/com/db.php
PHP | 2641 lines | 1566 code | 196 blank | 879 comment | 266 complexity | bac9ee45ef4368a2896f41023adeb8cb MD5 | raw file
Possible License(s): LGPL-2.1, BSD-3-Clause
Large files files are truncated, but you can click here to view the full file
- <?php
- /**
- * Copyright 2011 Unirgy LLC
- *
- * Licensed under the Apache License, Version 2.0 (the "License");
- * you may not use this file except in compliance with the License.
- * You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- *
- * @package BuckyBall
- * @link http://github.com/unirgy/buckyball
- * @author Boris Gurvich <boris@unirgy.com>
- * @copyright (c) 2010-2012 Boris Gurvich
- * @license http://www.apache.org/licenses/LICENSE-2.0.html
- */
- /**
- * Wrapper for idiorm/paris
- *
- * @see http://j4mie.github.com/idiormandparis/
- */
- class BDb
- {
- /**
- * Collection of cached named DB connections
- *
- * @var array
- */
- protected static $_namedConnections = array();
- /**
- * Necessary configuration for each DB connection name
- *
- * @var array
- */
- protected static $_namedConnectionConfig = array();
- /**
- * Default DB connection name
- *
- * @var string
- */
- protected static $_defaultConnectionName = 'DEFAULT';
- /**
- * DB name which is currently referenced in BORM::$_db
- *
- * @var string
- */
- protected static $_currentConnectionName;
- /**
- * Current DB configuration
- *
- * @var array
- */
- protected static $_config = array('table_prefix'=>'');
- /**
- * List of tables per connection
- *
- * @var array
- */
- protected static $_tables = array();
- /**
- * Shortcut to help with IDE autocompletion
- * @param bool $new
- * @param array $args
- * @return BDb
- */
- public static function i($new=false, array $args=array())
- {
- return BClassRegistry::i()->instance(__CLASS__, $args, !$new);
- }
- /**
- * Connect to DB using default or a named connection from global configuration
- *
- * Connections are cached for reuse when switching.
- *
- * Structure in configuration:
- *
- * {
- * db: {
- * dsn: 'mysql:host=127.0.0.1;dbname=buckyball', - optional: replaces engine, host, dbname
- * engine: 'mysql', - optional if dsn exists, default: mysql
- * host: '127.0.0.1', - optional if dsn exists, default: 127.0.0.1
- * dbname: 'buckyball', - optional if dsn exists, required otherwise
- * username: 'dbuser', - default: root
- * password: 'password', - default: (empty)
- * logging: false, - default: false
- * named: {
- * read: {<db-connection-structure>}, - same structure as default connection
- * write: {
- * use: 'read' - optional, reuse another connection
- * }
- * }
- * }
- *
- * @param string $name
- * @throws BException
- * @return PDO
- */
- public static function connect($name=null)
- {
- if (!$name && static::$_currentConnectionName) { // continue connection to current db, if no value
- return BORM::get_db();
- }
- if (is_null($name)) { // if first time connection, connect to default db
- $name = static::$_defaultConnectionName;
- }
- if ($name===static::$_currentConnectionName) { // if currently connected to requested db, return
- return BORM::get_db();
- }
- if (!empty(static::$_namedConnections[$name])) { // if connection already exists, switch to it
- BDebug::debug('DB.SWITCH '.$name);
- static::$_currentConnectionName = $name;
- static::$_config = static::$_namedConnectionConfig[$name];
- BORM::set_db(static::$_namedConnections[$name], static::$_config);
- return BORM::get_db();
- }
- $config = BConfig::i()->get($name===static::$_defaultConnectionName ? 'db' : 'db/named/'.$name);
- if (!$config) {
- throw new BException(BLocale::_('Invalid or missing DB configuration: %s', $name));
- }
- if (!empty($config['use'])) { //TODO: Prevent circular reference
- static::connect($config['use']);
- return BORM::get_db();
- }
- if (!empty($config['dsn'])) {
- $dsn = $config['dsn'];
- if (empty($config['dbname']) && preg_match('#dbname=(.*?)(;|$)#', $dsn, $m)) {
- $config['dbname'] = $m[1];
- }
- } else {
- if (empty($config['dbname'])) {
- throw new BException(BLocale::_("dbname configuration value is required for '%s'", $name));
- }
- $engine = !empty($config['engine']) ? $config['engine'] : 'mysql';
- $host = !empty($config['host']) ? $config['host'] : '127.0.0.1';
- switch ($engine) {
- case "mysql":
- $dsn = "mysql:host={$host};dbname={$config['dbname']};charset=UTF8";
- break;
- default:
- throw new BException(BLocale::_('Invalid DB engine: %s', $engine));
- }
- }
- $profile = BDebug::debug('DB.CONNECT '.$name);
- static::$_currentConnectionName = $name;
- BORM::configure($dsn);
- BORM::configure('username', !empty($config['username']) ? $config['username'] : 'root');
- BORM::configure('password', !empty($config['password']) ? $config['password'] : '');
- BORM::configure('logging', !empty($config['logging']));
- BORM::set_db(null);
- BORM::setup_db();
- static::$_namedConnections[$name] = BORM::get_db();
- static::$_config = static::$_namedConnectionConfig[$name] = array(
- 'dbname' => !empty($config['dbname']) ? $config['dbname'] : null,
- 'table_prefix' => !empty($config['table_prefix']) ? $config['table_prefix'] : '',
- );
- $db = BORM::get_db();
- BDebug::profile($profile);
- return $db;
- }
- /**
- * DB friendly current date/time
- *
- * @return string
- */
- public static function now()
- {
- return gmstrftime('%Y-%m-%d %H:%M:%S');
- }
- /**
- * Shortcut to run multiple queries from migrate scripts
- *
- * It doesn't make sense to run multiple queries in the same call and use $params
- *
- * @param string $sql
- * @param array $params
- * @param array $options
- * - echo - echo all queries as they run
- * @throws Exception
- * @return array
- */
- public static function run($sql, $params=null, $options=array())
- {
- BDb::connect();
- $queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
- $results = array();
- foreach ($queries as $i=>$query){
- if (strlen(trim($query)) > 0) {
- try {
- BDebug::debug('DB.RUN: '.$query);
- if (!empty($options['echo'])) {
- echo '<hr><pre>'.$query.'<pre>';
- }
- if (is_null($params)) {
- $results[] = BORM::get_db()->exec($query);
- } else {
- $results[] = BORM::get_db()->prepare($query)->execute($params);
- }
- } catch (Exception $e) {
- echo "<hr>{$e->getMessage()}: <pre>{$query}</pre>";
- if (empty($options['try'])) {
- throw $e;
- }
- }
- }
- }
- return $results;
- }
- /**
- * Start transaction
- *
- * @param string $connectionName
- */
- public static function transaction($connectionName=null)
- {
- if (!is_null($connectionName)) {
- BDb::connect($connectionName);
- }
- BORM::get_db()->beginTransaction();
- }
- /**
- * Commit transaction
- *
- * @param string $connectionName
- */
- public static function commit($connectionName=null)
- {
- if (!is_null($connectionName)) {
- BDb::connect($connectionName);
- }
- BORM::get_db()->commit();
- }
- /**
- * Rollback transaction
- *
- * @param string $connectionName
- */
- public static function rollback($connectionName=null)
- {
- if (!is_null($connectionName)) {
- BDb::connect($connectionName);
- }
- BORM::get_db()->rollback();
- }
- /**
- * Get db specific table name with pre-configured prefix for current connection
- *
- * Can be used as both BDb::t() and $this->t() within migration script
- * Convenient within strings and heredocs as {$this->t(...)}
- *
- * @param string $tableName
- * @return string
- */
- public static function t($tableName)
- {
- $a = explode('.', $tableName);
- $p = static::$_config['table_prefix'];
- return !empty($a[1]) ? $a[0].'.'.$p.$a[1] : $p.$a[0];
- }
- /**
- * Convert array collection of objects from find_many result to arrays
- *
- * @param array $rows result of ORM::find_many()
- * @param string $method default 'as_array'
- * @param array|string $fields if specified, return only these fields
- * @param boolean $maskInverse if true, do not return specified fields
- * @return array
- */
- public static function many_as_array($rows, $method='as_array', $fields=null, $maskInverse=false)
- {
- $res = array();
- foreach ((array)$rows as $i=>$r) {
- if (!$r instanceof BModel) {
- echo "Rows are not models: <pre>"; print_r($r);
- debug_print_backtrace();
- exit;
- }
- $row = $r->$method();
- if (!is_null($fields)) $row = BUtil::arrayMask($row, $fields, $maskInverse);
- $res[$i] = $row;
- }
- return $res;
- }
- /**
- * Construct where statement (for delete or update)
- *
- * Examples:
- * $w = BDb::where("f1 is null");
- *
- * // (f1='V1') AND (f2='V2')
- * $w = BDb::where(array('f1'=>'V1', 'f2'=>'V2'));
- *
- * // (f1=5) AND (f2 LIKE '%text%'):
- * $w = BDb::where(array('f1'=>5, array('f2 LIKE ?', '%text%')));
- *
- * // ((f1!=5) OR (f2 BETWEEN 10 AND 20)):
- * $w = BDb::where(array('OR'=>array(array('f1!=?', 5), array('f2 BETWEEN ? AND ?', 10, 20))));
- *
- * // (f1 IN (1,2,3)) AND NOT ((f2 IS NULL) OR (f2=10))
- * $w = BDb::where(array('f1'=>array(1,2,3)), 'NOT'=>array('OR'=>array("f2 IS NULL", 'f2'=>10)));
- *
- * // ((A OR B) AND (C OR D))
- * $w = BDb::where(array('AND', array('OR', 'A', 'B'), array('OR', 'C', 'D')));
- *
- * @param array $conds
- * @param boolean $or
- * @throws BException
- * @return array (query, params)
- */
- public static function where($conds, $or=false)
- {
- if (is_string($conds)) {
- return array($conds, array());
- }
- if (!is_array($conds)) {
- throw new BException("Invalid where parameter");
- }
- $where = array();
- $params = array();
- foreach ($conds as $f=>$v) {
- if (is_int($f)) {
- if (is_string($v)) { // freeform
- $where[] = '('.$v.')';
- continue;
- }
- if (is_array($v)) { // [freeform|arguments]
- $sql = array_shift($v);
- if ('AND'===$sql || 'OR'===$sql || 'NOT'===$sql) {
- $f = $sql;
- } else {
- if (isset($v[0]) && is_array($v[0])) { // `field` IN (?)
- $v = $v[0];
- $sql = str_replace('(?)', '('.str_pad('', sizeof($v)*2-1, '?,').')', $sql);
- }
- $where[] = '('.$sql.')';
- $params = array_merge($params, $v);
- continue;
- }
- } else {
- throw new BException('Invalid token: '.print_r($v,1));
- }
- }
- if ('AND'===$f) {
- list($w, $p) = static::where($v);
- $where[] = '('.$w.')';
- $params = array_merge($params, $p);
- } elseif ('OR'===$f) {
- list($w, $p) = static::where($v, true);
- $where[] = '('.$w.')';
- $params = array_merge($params, $p);
- } elseif ('NOT'===$f) {
- list($w, $p) = static::where($v);
- $where[] = 'NOT ('.$w.')';
- $params = array_merge($params, $p);
- } elseif (is_array($v)) {
- $where[] = "({$f} IN (".str_pad('', sizeof($v)*2-1, '?,')."))";
- $params = array_merge($params, $v);
- } elseif (is_null($v)) {
- $where[] = "({$f} IS NULL)";
- } else {
- $where[] = "({$f}=?)";
- $params[] = $v;
- }
- }
- #print_r($where); print_r($params);
- return array(join($or ? " OR " : " AND ", $where), $params);
- }
- /**
- * Get database name for current connection
- *
- */
- public static function dbName()
- {
- if (!static::$_config) {
- throw new BException('No connection selected');
- }
- return !empty(static::$_config['dbname']) ? static::$_config['dbname'] : null;
- }
- public static function ddlStart()
- {
- BDb::run(<<<EOT
- /*!40101 SET SQL_MODE=''*/;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
- EOT
- );
- }
- public static function ddlFinish()
- {
- BDb::run(<<<EOT
- /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
- /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
- /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
- /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
- EOT
- );
- }
- /**
- * Clear DDL cache
- *
- */
- public static function ddlClearCache($fullTableName=null)
- {
- if ($fullTableName) {
- if (!static::dbName()) {
- static::connect(static::$_defaultConnectionName);
- }
- $a = explode('.', $fullTableName);
- $dbName = empty($a[1]) ? static::dbName() : $a[0];
- $tableName = empty($a[1]) ? $fullTableName : $a[1];
- static::$_tables[$dbName][$tableName] = null;
- } else {
- static::$_tables = array();
- }
- }
- /**
- * Check whether table exists
- *
- * @param string $fullTableName
- * @return bool
- */
- public static function ddlTableExists($fullTableName)
- {
- if (!static::dbName()) {
- static::connect(static::$_defaultConnectionName);
- }
- $a = explode('.', $fullTableName);
- $dbName = empty($a[1]) ? static::dbName() : $a[0];
- $tableName = empty($a[1]) ? $fullTableName : $a[1];
- if (!isset(static::$_tables[$dbName])) {
- $tables = BORM::i()->raw_query("SHOW TABLES FROM `{$dbName}`", array())->find_many();
- $field = "Tables_in_{$dbName}";
- foreach ($tables as $t) {
- static::$_tables[$dbName][$t->get($field)] = array();
- }
- } elseif (!isset(static::$_tables[$dbName][$tableName])) {
- $table = BORM::i()->raw_query("SHOW TABLES FROM `{$dbName}` LIKE ?", array($tableName))->find_one();
- if ($table) {
- static::$_tables[$dbName][$tableName] = array();
- }
- }
- return isset(static::$_tables[$dbName][$tableName]);
- }
- /**
- * Get table field info
- *
- * @param string $fullTableName
- * @param string $fieldName if null return all fields
- * @throws BException
- * @return mixed
- */
- public static function ddlFieldInfo($fullTableName, $fieldName=null)
- {
- self::checkTable($fullTableName);
- $a = explode('.', $fullTableName);
- $dbName = empty($a[1]) ? static::dbName() : $a[0];
- $tableName = empty($a[1]) ? $fullTableName : $a[1];
- if (!isset(static::$_tables[$dbName][$tableName]['fields'])) {
- static::$_tables[$dbName][$tableName]['fields'] = BORM::i()
- ->raw_query("SHOW FIELDS FROM `{$dbName}`.`{$tableName}`", array())->find_many_assoc('Field');
- }
- $res = static::$_tables[$dbName][$tableName]['fields'];
- return is_null($fieldName) ? $res : (isset($res[$fieldName]) ? $res[$fieldName] : null);
- }
- /**
- * @param string $fullTableName
- * @throws BException
- */
- protected static function checkTable($fullTableName)
- {
- if (!static::ddlTableExists($fullTableName)) {
- throw new BException(BLocale::_('Invalid table name: %s', $fullTableName));
- }
- }
- /**
- * Retrieve table index(es) info, if exist
- *
- * @param string $fullTableName
- * @param string $indexName
- * @throws BException
- * @return array|null
- */
- public static function ddlIndexInfo($fullTableName, $indexName=null)
- {
- if (!static::ddlTableExists($fullTableName)) {
- throw new BException(BLocale::_('Invalid table name: %s', $fullTableName));
- }
- $a = explode('.', $fullTableName);
- $dbName = empty($a[1]) ? static::dbName() : $a[0];
- $tableName = empty($a[1]) ? $fullTableName : $a[1];
- if (!isset(static::$_tables[$dbName][$tableName]['indexes'])) {
- static::$_tables[$dbName][$tableName]['indexes'] = BORM::i()
- ->raw_query("SHOW KEYS FROM `{$dbName}`.`{$tableName}`", array())->find_many_assoc('Key_name');
- }
- $res = static::$_tables[$dbName][$tableName]['indexes'];
- return is_null($indexName) ? $res : (isset($res[$indexName]) ? $res[$indexName] : null);
- }
- /**
- * Retrieve table foreign key(s) info, if exist
- *
- * Mysql/InnoDB specific
- *
- * @param string $fullTableName
- * @param string $fkName
- * @throws BException
- * @return array|null
- */
- public static function ddlForeignKeyInfo($fullTableName, $fkName=null)
- {
- if (!static::ddlTableExists($fullTableName)) {
- throw new BException(BLocale::_('Invalid table name: %s', $fullTableName));
- }
- $a = explode('.', $fullTableName);
- $dbName = empty($a[1]) ? static::dbName() : $a[0];
- $tableName = empty($a[1]) ? $fullTableName : $a[1];
- if (!isset(static::$_tables[$dbName][$tableName]['fks'])) {
- static::$_tables[$dbName][$tableName]['fks'] = BORM::i()
- ->raw_query("SELECT * FROM information_schema.TABLE_CONSTRAINTS
- WHERE TABLE_SCHEMA='{$dbName}' AND TABLE_NAME='{$tableName}'
- AND CONSTRAINT_TYPE='FOREIGN KEY'", array())->find_many_assoc('CONSTRAINT_NAME');
- }
- $res = static::$_tables[$dbName][$tableName]['fks'];
- return is_null($fkName) ? $res : (isset($res[$fkName]) ? $res[$fkName] : null);
- }
- /**
- * Create or update table
- *
- * @deprecates ddlTable and ddlTableColumns
- * @param string $fullTableName
- * @param array $def
- * @throws BException
- * @return array
- */
- public static function ddlTableDef($fullTableName, $def)
- {
- $fields = !empty($def['COLUMNS']) ? $def['COLUMNS'] : null;
- $primary = !empty($def['PRIMARY']) ? $def['PRIMARY'] : null;
- $indexes = !empty($def['KEYS']) ? $def['KEYS'] : null;
- $fks = !empty($def['CONSTRAINTS']) ? $def['CONSTRAINTS'] : null;
- $options = !empty($def['OPTIONS']) ? $def['OPTIONS'] : null;
- if (!static::ddlTableExists($fullTableName)) {
- if (!$fields) {
- throw new BException('Missing fields definition for new table');
- }
- // temporary code duplication with ddlTable, until the other one is removed
- $fieldsArr = array();
- foreach ($fields as $f=>$def) {
- $fieldsArr[] = '`'.$f.'` '.$def;
- }
- $fields = null; // reset before update step
- if ($primary) {
- $fieldsArr[] = "PRIMARY KEY ".$primary;
- $primary = null; // reset before update step
- }
- $engine = !empty($options['engine']) ? $options['engine'] : 'InnoDB';
- $charset = !empty($options['charset']) ? $options['charset'] : 'utf8';
- $collate = !empty($options['collate']) ? $options['collate'] : 'utf8_general_ci';
- BORM::i()->raw_query("CREATE TABLE {$fullTableName} (".join(', ', $fieldsArr).")
- ENGINE={$engine} DEFAULT CHARSET={$charset} COLLATE={$collate}", array())->execute();
- }
- static::ddlTableColumns($fullTableName, $fields, $indexes, $fks, $options);
- static::ddlClearCache();
- }
- /**
- * Create or update table
- *
- * @param string $fullTableName
- * @param array $fields
- * @param array $options
- * - engine (default InnoDB)
- * - charset (default utf8)
- * - collate (default utf8_general_ci)
- * @return bool
- */
- public static function ddlTable($fullTableName, $fields, $options=null)
- {
- if (static::ddlTableExists($fullTableName)) {
- static::ddlTableColumns($fullTableName, $fields, null, null, $options); // altering options is not implemented
- } else {
- $fieldsArr = array();
- foreach ($fields as $f=>$def) {
- $fieldsArr[] = '`'.$f.'` '.$def;
- }
- if (!empty($options['primary'])) {
- $fieldsArr[] = "PRIMARY KEY ".$options['primary'];
- }
- $engine = !empty($options['engine']) ? $options['engine'] : 'InnoDB';
- $charset = !empty($options['charset']) ? $options['charset'] : 'utf8';
- $collate = !empty($options['collate']) ? $options['collate'] : 'utf8_general_ci';
- BORM::i()->raw_query("CREATE TABLE {$fullTableName} (".join(', ', $fieldsArr).")
- ENGINE={$engine} DEFAULT CHARSET={$charset} COLLATE={$collate}", array())->execute();
- static::ddlClearCache();
- }
- return true;
- }
- /**
- * Add or change table columns
- *
- * BDb::ddlTableColumns('my_table', array(
- * 'field_to_create' => 'varchar(255) not null',
- * 'field_to_update' => 'decimal(12,2) null',
- * 'field_to_drop' => 'DROP',
- * ));
- *
- * @param string $fullTableName
- * @param array $fields
- * @param array $indexes
- * @param array $fks
- * @return array
- */
- public static function ddlTableColumns($fullTableName, $fields, $indexes=null, $fks=null)
- {
- $tableFields = static::ddlFieldInfo($fullTableName, null);
- $tableFields = array_change_key_case($tableFields, CASE_LOWER);
- $alterArr = array();
- if ($fields) {
- foreach ($fields as $f=>$def) {
- $fLower = strtolower($f);
- if ($def==='DROP') {
- if (!empty($tableFields[$fLower])) {
- $alterArr[] = "DROP `{$f}`";
- }
- } elseif (strpos($def, 'RENAME')===0) {
- $a = explode(' ', $def, 3); //TODO: smarter parser, allow spaces in column name??
- // Why not use a sprintf($def, $f) to fill in column name from $f?
- $colName = $a[1];
- $def = $a[2];
- if (empty($tableFields[$fLower])) {
- $f = $colName;
- }
- $alterArr[] = "CHANGE `{$f}` `{$colName}` {$def}";
- } elseif (empty($tableFields[$fLower])) {
- $alterArr[] = "ADD `{$f}` {$def}";
- } else {
- $alterArr[] = "CHANGE `{$f}` `{$f}` {$def}";
- }
- }
- }
- if ($indexes) {
- $tableIndexes = static::ddlIndexInfo($fullTableName);
- $tableIndexes = array_change_key_case($tableIndexes, CASE_LOWER);
- foreach ($indexes as $idx=>$def) {
- $idxLower = strtolower($idx);
- if ($def==='DROP') {
- if (!empty($tableIndexes[$idxLower])) {
- $alterArr[] = "DROP KEY `{$idx}`";
- }
- } else {
- if (!empty($tableIndexes[$idxLower])) {
- $alterArr[] = "DROP KEY `{$idx}`";
- }
- if (strpos($def, 'PRIMARY')===0) {
- $alterArr[] = "DROP PRIMARY KEY";
- $def = substr($def, 7);
- $alterArr[] = "ADD PRIMARY KEY `{$idx}` {$def}";
- } elseif (strpos($def, 'UNIQUE')===0) {
- $def = substr($def, 6);
- $alterArr[] = "ADD UNIQUE KEY `{$idx}` {$def}";
- } else {
- $alterArr[] = "ADD KEY `{$idx}` {$def}";
- }
- }
- }
- }
- if ($fks) {
- $tableFKs = static::ddlForeignKeyInfo($fullTableName);
- $tableFKs = array_change_key_case($tableFKs, CASE_LOWER);
- // @see http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
- // You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement.
- // Separate statements are required.
- $dropArr = array();
- foreach ($fks as $idx=>$def) {
- $idxLower = strtolower($idx);
- if ($def==='DROP') {
- if (!empty($tableFKs[$idxLower])) {
- $dropArr[] = "DROP FOREIGN KEY `{$idx}`";
- }
- } else {
- if (!empty($tableFKs[$idxLower])) {
- // what if it is not foreign key constraint we do not doe anything to check for UNIQUE and PRIMARY constraint
- $dropArr[] = "DROP FOREIGN KEY `{$idx}`";
- }
- $alterArr[] = "ADD CONSTRAINT `{$idx}` {$def}";
- }
- }
- if (!empty($dropArr)) {
- BORM::i()->raw_query("ALTER TABLE {$fullTableName} ".join(", ", $dropArr), array())->execute();
- static::ddlClearCache();
- }
- }
- $result = null;
- if ($alterArr) {
- $result = BORM::i()->raw_query("ALTER TABLE {$fullTableName} ".join(", ", $alterArr), array())->execute();
- static::ddlClearCache();
- }
- return $result;
- }
- /**
- * A convenience method to add columns to table
- * It should check if columns exist before passing to self::ddlTableColumns
- * $columns array should be in same format as for ddlTableColumns:
- *
- * array(
- * 'field_name' => 'column definition',
- * 'field_two' => 'column definition',
- * 'field_three' => 'column definition',
- * )
- *
- * @param string $table
- * @param array $columns
- * @return array|null
- */
- public static function ddlAddColumns($table, $columns = array())
- {
- if (empty($columns)) {
- BDebug::log(__METHOD__ . ": columns array is empty.");
- return null;
- }
- $pass = array();
- $tableFields = array_keys(static::ddlFieldInfo($table));
- foreach ($columns as $field => $def) {
- if( in_array($field, $tableFields)) {
- continue;
- }
- $pass[$field] = $def;
- }
- return static::ddlTableColumns($table, $pass);
- }
- /**
- * Clean array or object fields based on table columns and return an array
- *
- * @param string $table
- * @param array|object $data
- * @return array
- */
- public static function cleanForTable($table, $data)
- {
- $isObject = is_object($data);
- $result = array();
- foreach ($data as $k=>$v) {
- if (BDb::ddlFieldInfo($table, $k)) {
- $result[$k] = $isObject ? $data->get($k) : $data[$k];
- }
- }
- return $result;
- }
- }
- /**
- * Enhanced PDO class to allow for transaction nesting for mysql and postgresql
- *
- * @see http://us.php.net/manual/en/pdo.connections.php#94100
- * @see http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/
- */
- class BPDO extends PDO
- {
- // Database drivers that support SAVEPOINTs.
- protected static $_savepointTransactions = array("pgsql", "mysql");
- // The current transaction level.
- protected $_transLevel = 0;
- /*
- public static function exception_handler($exception)
- {
- // Output the exception details
- die('Uncaught exception: '. $exception->getMessage());
- }
- public function __construct($dsn, $username='', $password='', $driver_options=array())
- {
- // Temporarily change the PHP exception handler while we . . .
- set_exception_handler(array(__CLASS__, 'exception_handler'));
- // . . . create a PDO object
- parent::__construct($dsn, $username, $password, $driver_options);
- // Change the exception handler back to whatever it was before
- restore_exception_handler();
- }
- */
- protected function _nestable() {
- return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
- static::$_savepointTransactions);
- }
- public function beginTransaction() {
- if (!$this->_nestable() || $this->_transLevel == 0) {
- parent::beginTransaction();
- } else {
- $this->exec("SAVEPOINT LEVEL{$this->_transLevel}");
- }
- $this->_transLevel++;
- }
- public function commit() {
- $this->_transLevel--;
- if (!$this->_nestable() || $this->_transLevel == 0) {
- parent::commit();
- } else {
- $this->exec("RELEASE SAVEPOINT LEVEL{$this->_transLevel}");
- }
- }
- public function rollBack() {
- $this->_transLevel--;
- if (!$this->_nestable() || $this->_transLevel == 0) {
- parent::rollBack();
- } else {
- $this->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->_transLevel}");
- }
- }
- }
- /**
- * Enhanced ORMWrapper to support multiple database connections and many other goodies
- */
- class BORM extends ORMWrapper
- {
- /**
- * Singleton instance
- *
- * @var BORM
- */
- protected static $_instance;
- /**
- * ID for profiling of the last run query
- *
- * @var int
- */
- protected static $_last_profile;
- /**
- * Default class name for direct ORM calls
- *
- * @var string
- */
- protected $_class_name = 'BModel';
- /**
- * Read DB connection for selects (replication slave)
- *
- * @var string|null
- */
- protected $_readConnectionName;
- /**
- * Write DB connection for updates (master)
- *
- * @var string|null
- */
- protected $_writeConnectionName;
- /**
- * Read DB name
- *
- * @var string
- */
- protected $_readDbName;
- /**
- * Write DB name
- *
- * @var string
- */
- protected $_writeDbName;
- /**
- * Old values in the object before ->set()
- *
- * @var array
- */
- protected $_old_values = array();
- /**
- * Shortcut factory for generic instance
- *
- * @param bool $new
- * @return BORM
- */
- public static function i($new=false)
- {
- if ($new) {
- return new static('');
- }
- if (!static::$_instance) {
- static::$_instance = new static('');
- }
- return static::$_instance;
- }
- protected function _quote_identifier($identifier) {
- if ($identifier[0]=='(') {
- return $identifier;
- }
- return parent::_quote_identifier($identifier);
- }
- public static function get_config($key)
- {
- return !empty(static::$_config[$key]) ? static::$_config[$key] : null;
- }
- /**
- * Public alias for _setup_db
- */
- public static function setup_db()
- {
- static::_setup_db();
- }
- /**
- * Set up the database connection used by the class.
- * Use BPDO for nested transactions
- */
- protected static function _setup_db()
- {
- if (!is_object(static::$_db)) {
- $connection_string = static::$_config['connection_string'];
- $username = static::$_config['username'];
- $password = static::$_config['password'];
- $driver_options = static::$_config['driver_options'];
- if (empty($driver_options[PDO::MYSQL_ATTR_INIT_COMMAND])) { //ADDED
- $driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES utf8";
- }
- try { //ADDED: hide connection details from the error if not in DEBUG mode
- $db = new BPDO($connection_string, $username, $password, $driver_options); //UPDATED
- } catch (PDOException $e) {
- if (BDebug::is('DEBUG')) {
- throw $e;
- } else {
- throw new PDOException('Could not connect to database');
- }
- }
- $db->setAttribute(PDO::ATTR_ERRMODE, static::$_config['error_mode']);
- static::set_db($db);
- }
- }
- /**
- * Set the PDO object used by Idiorm to communicate with the database.
- * This is public in case the ORM should use a ready-instantiated
- * PDO object as its database connection.
- */
- public static function set_db($db, $config=null)
- {
- if (!is_null($config)) {
- static::$_config = array_merge(static::$_config, $config);
- }
- static::$_db = $db;
- if (!is_null($db)) {
- static::_setup_identifier_quote_character();
- }
- }
- /**
- * Set read/write DB connection names from model
- *
- * @param string $read
- * @param string $write
- * @return BORMWrapper
- */
- public function set_rw_db_names($read, $write)
- {
- $this->_readDbName = $read;
- $this->_writeDbName = $write;
- return $this;
- }
- protected static function _log_query($query, $parameters)
- {
- $result = parent::_log_query($query, $parameters);
- static::$_last_profile = BDebug::debug('DB.RUN: '.(static::$_last_query ? static::$_last_query : 'LOGGING NOT ENABLED'));
- return $result;
- }
- /**
- * Execute the SELECT query that has been built up by chaining methods
- * on this class. Return an array of rows as associative arrays.
- *
- * Connection will be switched to read, if set
- *
- * @return array
- */
- protected function _run()
- {
- BDb::connect($this->_readConnectionName);
- #$timer = microtime(true); // file log
- $result = parent::_run();
- #BDebug::log((microtime(true)-$timer).' '.static::$_last_query); // file log
- BDebug::profile(static::$_last_profile);
- static::$_last_profile = null;
- return $result;
- }
- /**
- * Set or return table alias for the main table
- *
- * @param string|null $alias
- * @return BORM|string
- */
- public function table_alias($alias=null)
- {
- if (is_null($alias)) {
- return $this->_table_alias;
- }
- $this->_table_alias = $alias;
- return $this;
- }
- /**
- * Add a column to the list of columns returned by the SELECT
- * query. This defaults to '*'. The second optional argument is
- * the alias to return the column as.
- *
- * @param string|array $column if array, select multiple columns
- * @param string $alias optional alias, if $column is array, used as table name
- * @return BORM
- */
- public function select($column, $alias=null)
- {
- if (is_array($column)) {
- foreach ($column as $k=>$v) {
- $col = (!is_null($alias) ? $alias.'.' : '').$v;
- if (is_int($k)) {
- $this->select($col);
- } else {
- $this->select($col, $k);
- }
- }
- return $this;
- }
- return parent::select($column, $alias);
- }
- protected $_use_index = array();
- public function use_index($index, $type='USE', $table='_')
- {
- $this->_use_index[$table] = compact('index', 'type');
- return $this;
- }
- protected function _build_select_start() {
- $fragment = parent::_build_select_start();
- if (!empty($this->_use_index['_'])) {
- $idx = $this->_use_index['_'];
- $fragment .= ' '.$idx['type'].' INDEX ('.$idx['index'].') ';
- }
- return $fragment;
- }
- protected function _add_result_column($expr, $alias=null) {
- if (!is_null($alias)) {
- $expr .= " AS " . $this->_quote_identifier($alias);
- }
- // ADDED TO AVOID DUPLICATE FIELDS
- if (in_array($expr, $this->_result_columns)) {
- return $this;
- }
- if ($this->_using_default_result_columns) {
- $this->_result_columns = array($expr);
- $this->_using_default_result_columns = false;
- } else {
- $this->_result_columns[] = $expr;
- }
- return $this;
- }
- public function clear_columns()
- {
- $this->_result_columns = array();
- return $this;
- }
- /**
- * Return select sql statement built from the ORM object
- *
- * @return string
- */
- public function as_sql()
- {
- return $this->_build_select();
- }
- /**
- * Execute the query and return PDO statement object
- *
- * Usage:
- * $sth = $orm->execute();
- * while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { ... }
- *
- * @return PDOStatement
- */
- public function execute()
- {
- BDb::connect($this->_readConnectionName);
- $query = $this->_build_select();
- static::_log_query($query, $this->_values);
- $statement = static::$_db->prepare($query);
- try {
- $statement->execute($this->_values);
- } catch (Exception $e) {
- echo $query;
- print_r($e);
- exit;
- }
- return $statement;
- }
- public function row_to_model($row)
- {
- return $this->_create_model_instance($this->_create_instance_from_row($row));
- }
- /**
- * Iterate over select result with callback on each row
- *
- * @param mixed $callback
- * @param string $type
- * @return BORM
- */
- public function iterate($callback, $type='callback')
- {
- $statement = $this->execute();
- while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
- $model = $this->row_to_model($row);
- switch ($type) {
- case 'callback': call_user_func($callback, $model); break;
- case 'method': $model->$callback(); break;
- }
- }
- return $this;
- }
- /**
- * Extended where condition
- *
- * @param string|array $column_name if array - use where_complex() syntax
- * @param mixed $value
- */
- public function where($column_name, $value=null)
- {
- if (is_array($column_name)) {
- return $this->where_complex($column_name, !!$value);
- }
- return parent::where($column_name, $value);
- }
- /**
- * Add a complex where condition
- *
- * @see BDb::where
- * @param array $conds
- * @param boolean $or
- * @return BORM
- */
- public function where_complex($conds, $or=false)
- {
- list($where, $params) = BDb::where($conds, $or);
- if (!$where) {
- return $this;
- }
- return $this->where_raw($where, $params);
- }
- /**
- * Find one row
- *
- * @param int|null $id
- * @return BModel
- */
- public function find_one($id=null)
- {
- $class = $this->_class_name;
- if ($class::origClass()) {
- $class = $class::origClass();
- }
- BEvents::i()->fire($class.'::find_one:orm', array('orm'=>$this, 'class'=>$class, 'id'=>$id));
- $result = parent::find_one($id);
- BEvents::i()->fire($class.'::find_one:after', array('result'=>&$result, 'class'=>$class, 'id'=>$id));
- return $result;
- }
- /**
- * Find many rows (SELECT)
- *
- * @return array
- */
- public function find_many()
- {
- $class = $this->_class_name;
- if ($class::origClass()) {
- $class = $class::origClass();
- }
- BEvents::i()->fire($class.'::find_many:orm', array('orm'=>$this, 'class'=>$class));
- $result = parent::find_many();
- BEvents::i()->fire($class.'::find_many:after', array('result'=>&$result, 'class'=>$class));
- return $result;
- }
- /**
- * Find many records and return as associated array
- *
- * @param string|array $key if array, will create multi-dimensional array (currently 2D)
- * @param string|null $labelColumn
- * @param array $options (key_lower, key_trim)
- * @return array
- */
- public function find_many_assoc($key=null, $labelColumn=null, $options=array())
- {
- $objects = $this->find_many();
- $array = array();
- if (empty($key)) {
- $key = $this->_get_id_column_name();
- }
- foreach ($objects as $r) {
- $value = is_null($labelColumn) ? $r : (is_array($labelColumn) ? BUtil::arrayMask($r, $labelColumn) : $r->get($labelColumn));
- if (!is_array($key)) { // save on performance for 1D keys
- $v = $r->get($key);
- if (!empty($options['key_lower'])) $v = strtolower($v);
- if (!empty($options['key_trim'])) $v = trim($v);
- $array[$v] = $value;
- } else {
- $v1 = $r->get($key[0]);
- if (!empty($options['key_lower'])) $v1 = strtolower($v1);
- if (!empty($options['key_trim'])) $v1 = trim($v1);
- $v2 = $r->get($key[1]);
- if (!empty($options['key_lower'])) $v2 = strtolower($v2);
- if (!empty($options['key_trim'])) $v1 = trim($v2);
- $array[$v1][$v2] = $value;
- }
- }
- return $array;
- }
- /**
- * Check whether the given field (or object itself) has been changed since this
- * object was saved.
- */
- public function is_dirty($key=null) {
- return is_null($key) ? !empty($this->_dirty_fields) : isset($this->_dirty_fields[$key]);
- }
- /**
- * Set a property to a particular value on this object.
- * Flags that property as 'dirty' so it will be saved to the
- * database when save() is called.
- */
- public function set($key, $value) {
- if (!is_scalar($key)) {
- throw new BException('Key not scalar');
- }
- if (!array_key_exists($key, $this->_data)
- || is_null($this->_data[$key]) && !is_null($value)
- || !is_null($this->_data[$key]) && is_null($value)
- || is_scalar($this->_data[$key]) && is_scalar($value)
- && ((string)$this->_data[$key] !== (string)$value)
- ) {
- #echo "DIRTY: "; var_dump($this->_data[$key], $value); echo "\n";
- if (!array_key_exists($key, $this->_old_values)) {
- $this->_old_values[$key] = array_key_exists($key, $this->_data) ? $this->_data[$key] : BNULL;
- }
- $this->_dirty_fields[$key] = $value;
- }
- $this->_data[$key] = $value;
- }
- /**
- * Class to table map cache
- *
- * @var array
- */
- protected static $_classTableMap = array();
- /**
- * Add a simple JOIN source to the query
- */
- public function _add_join_source($join_operator, $table, $constraint, $table_alias=null) {
- if (!isset(self::$_classTableMap[$table])) {
- if (class_exists($table) && is_subclass_of($table, 'BModel')) {
- $class = BClassRegistry::i()->className($table);
- self::$_classTableMap[$table] = $class::table();
- } else {
- self::$_classTableMap[$table] = false;
- }
- }
- if (self::$_classTableMap[$table]) {
- $table = self::$_classTableMap[$table];
- }
- return parent::_add_join_source($join_operator, $table, $constraint, $table_alias);
- }
- /**
- * Save any fields which have been modified on this object
- * to the database.
- *
- * Connection will be switched to write, if set
- *
- * @return boolean
- */
- public function save()
- {
- BDb::connect($this->_writeConnectionName);
- $this->_dirty_fields = BDb::cleanForTable($this->_table_name, $this->_dirty_fields);
- if (true) {
- #if (array_diff_assoc($this->_old_values, $this->_dirty_fields)) {
- $result = parent::save();
- #}
- } else {
- echo $this->_class_name.'['.$this->id.']: ';
- print_r($this->_data);
- echo 'FROM: '; print_r($this->_old_values);
- echo 'TO: '; print_r($this->_dirty_fields); echo "\n\n";
- $result = true;
- }
- //$this->_old_values = array(); // commented out to make original loaded object old values available after save
- return $result;
- }
- /**
- * Return dirty fields for debugging
- *
- * @return array
- */
- public function dirty_fields()
- {
- return $this->_dirty_fields;
- }
- public function old_values($property='')
- {
- if ($property && isset($this->_old_values[$property])) {
- return $this->_old_values[$property];
- }
- return $this->_old_values;
- }
- /**
- * Delete this record from the database
- *
- * Connection will be switched to write, if set
- *
- * @return boolean
- */
- public function delete()
- {
- BDb::connect($this->_writeConnectionName);
- return parent::delete();
- }
- /**
- * Add an ORDER BY expression DESC clause
- */
- public function order_by_expr($expression) {
- $this->_order_by[] = "{$expression}";
- return $this;
- }
- /**
- * Perform a raw query. The query should contain placeholders,
- * in either named or question mark style, and the parameters
- * should be an array of values which will be bound to the
- * placeholders in the query. If this method is called, all
- * other query building methods will be ignored.
- *
- * Connection will be set to write, if query is not SELECT or SHOW
- *
- * @param $query
- * @param array $parameters
- * @return BORM
- */
- public function raw_query($query, $parameters=array())
- {
- if (preg_match('#^\s*(SELECT|SHOW)#i', $query)) {
- BDb::connect($this->_readConnectionName);
- } else {
- BDb::connect($this->_writeConnectionName);
- }
- return parent::raw_query($query, $parameters);
- }
- /**
- * Get table name with prefix, if configured
- *
- * @param string $class_name
- * @return string
- */
- protected static function _get_table_name($class_name) {
- return BDb::t(parent::_get_table_name($class_name));
- }
- /**
- * Set page constraints on collection for use in grids
- *
- * Request and result vars:
- * - p: page number
- * - ps: page size
- * - s: sort order by (if default is array - only these values are allowed) (alt: sort|dir)
- * - sd: sort direction (asc/desc)
- * - sc: sort combined (s|sd)
- * - rs: requested row start (optional in request, not dependent on page size)
- * - rc: requested row count (optional in request, not dependent on page size)
- * - c: total row count (return only)
- * - mp: max page (return only)
- *
- * Options (all optional):
- * - format: 0..2
- * - as_array: true or method name
- *
- * @param array $r pagination request, if null - take from request query string
- * @param array $d default values and options
- * @return array
- */
- public function paginate($r=null, $d=array())
- {
- if (is_null($r)) {
- $r = BRequest::i()->request(); // GET request
- }
- $d = (array)$d; // make sure it's array
- if (!empty($r['sc']) && empty($r['s']) && empty($r['sd'])) { // sort and dir combined
- list($r['s'], $r['sd']) = preg_split('#[| ]#', trim($r['sc']));
- }
- if (!empty($r['s']) && !empty($d['s']) && is_array($d['s'])) { // limit by these values only
- if (!in_array($r['s'], $d['s'])) $r['s'] = null;
- $d['s'] = null;
- }
- if (!empty($r['sd']) && $r['sd']!='asc' && $r['sd']!='desc') { // only asc and desc are allowed
- $r['sd'] = null;
- }
- $s = array( // state
- 'p' => !empty($r['p']) && is_numeric($r['p']) ? $r['p'] : (isset($d['p']) ? $d['p'] : 1), // page
- 'ps' => !empty($r['ps']) && is_numeric($r['ps']) ? $r['ps'] : (isset($d['ps']) ? $d['ps'] : 100), // page size
- 's' => !empty($r['s']) ? $r['s'] : (isset($d['s']) ? $d['s'] : ''), // sort by
- 'sd' => !empty($r['sd']) ? $r['sd'] : (isset($d['sd']) ? $d['sd'] : 'asc'), // sort dir
- 'rs' => !empty($r['rs']) ? $r['rs'] : null, // starting row
- 'rc' => !empty($r['rc']) ? $r['rc'] : null, // total rows on page
- 'q' => !empty($r['q']) ? $r['q'] : null, // query string
- 'c' => !empty($d['c']) ? $d['c'] : null, //total found
- );
- #print_r($r); print_r($d); print_r($s); exit;
- $s['sc'] = $s['s'].' '.$s['sd']; // sort combined for state
- #$s['c'] = 600000;
- if (empty($s['c'])){
- $cntOrm = clone $this; // clone ORM to count
- $s['c'] = $cntOrm->count(); // total row count
- unset($cntOrm); // free mem
- }
- $s['mp'] = ceil($s['c']/$s['ps']…
Large files files are truncated, but you can click here to view the full file