PageRenderTime 207ms CodeModel.GetById 124ms app.highlight 71ms RepoModel.GetById 0ms app.codeStats 1ms

/com/db.php

http://github.com/unirgy/buckyball
PHP | 2641 lines | 1566 code | 196 blank | 879 comment | 266 complexity | bac9ee45ef4368a2896f41023adeb8cb MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1<?php
   2/**
   3* Copyright 2011 Unirgy LLC
   4*
   5* Licensed under the Apache License, Version 2.0 (the "License");
   6* you may not use this file except in compliance with the License.
   7* You may obtain a copy of the License at
   8*
   9* http://www.apache.org/licenses/LICENSE-2.0
  10*
  11* Unless required by applicable law or agreed to in writing, software
  12* distributed under the License is distributed on an "AS IS" BASIS,
  13* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14* See the License for the specific language governing permissions and
  15* limitations under the License.
  16*
  17* @package BuckyBall
  18* @link http://github.com/unirgy/buckyball
  19* @author Boris Gurvich <boris@unirgy.com>
  20* @copyright (c) 2010-2012 Boris Gurvich
  21* @license http://www.apache.org/licenses/LICENSE-2.0.html
  22*/
  23
  24/**
  25* Wrapper for idiorm/paris
  26*
  27* @see http://j4mie.github.com/idiormandparis/
  28*/
  29class BDb
  30{
  31    /**
  32    * Collection of cached named DB connections
  33    *
  34    * @var array
  35    */
  36    protected static $_namedConnections = array();
  37
  38    /**
  39    * Necessary configuration for each DB connection name
  40    *
  41    * @var array
  42    */
  43    protected static $_namedConnectionConfig = array();
  44
  45    /**
  46    * Default DB connection name
  47    *
  48    * @var string
  49    */
  50    protected static $_defaultConnectionName = 'DEFAULT';
  51
  52    /**
  53    * DB name which is currently referenced in BORM::$_db
  54    *
  55    * @var string
  56    */
  57    protected static $_currentConnectionName;
  58
  59    /**
  60    * Current DB configuration
  61    *
  62    * @var array
  63    */
  64    protected static $_config = array('table_prefix'=>'');
  65
  66    /**
  67    * List of tables per connection
  68    *
  69    * @var array
  70    */
  71    protected static $_tables = array();
  72
  73    /**
  74    * Shortcut to help with IDE autocompletion
  75    * @param bool  $new
  76    * @param array $args
  77    * @return BDb
  78    */
  79    public static function i($new=false, array $args=array())
  80    {
  81        return BClassRegistry::i()->instance(__CLASS__, $args, !$new);
  82    }
  83
  84    /**
  85    * Connect to DB using default or a named connection from global configuration
  86    *
  87    * Connections are cached for reuse when switching.
  88    *
  89    * Structure in configuration:
  90    *
  91    * {
  92    *   db: {
  93    *     dsn: 'mysql:host=127.0.0.1;dbname=buckyball',  - optional: replaces engine, host, dbname
  94    *     engine: 'mysql',                               - optional if dsn exists, default: mysql
  95    *     host: '127.0.0.1',                             - optional if dsn exists, default: 127.0.0.1
  96    *     dbname: 'buckyball',                           - optional if dsn exists, required otherwise
  97    *     username: 'dbuser',                            - default: root
  98    *     password: 'password',                          - default: (empty)
  99    *     logging: false,                                - default: false
 100    *     named: {
 101    *       read: {<db-connection-structure>},           - same structure as default connection
 102    *       write: {
 103    *         use: 'read'                                - optional, reuse another connection
 104    *       }
 105    *     }
 106    *  }
 107    *
 108    * @param string $name
 109    * @throws BException
 110    * @return PDO
 111    */
 112    public static function connect($name=null)
 113    {
 114        if (!$name && static::$_currentConnectionName) { // continue connection to current db, if no value
 115            return BORM::get_db();
 116        }
 117        if (is_null($name)) { // if first time connection, connect to default db
 118            $name = static::$_defaultConnectionName;
 119        }
 120        if ($name===static::$_currentConnectionName) { // if currently connected to requested db, return
 121            return BORM::get_db();
 122        }
 123        if (!empty(static::$_namedConnections[$name])) { // if connection already exists, switch to it
 124            BDebug::debug('DB.SWITCH '.$name);
 125            static::$_currentConnectionName = $name;
 126            static::$_config = static::$_namedConnectionConfig[$name];
 127            BORM::set_db(static::$_namedConnections[$name], static::$_config);
 128            return BORM::get_db();
 129        }
 130        $config = BConfig::i()->get($name===static::$_defaultConnectionName ? 'db' : 'db/named/'.$name);
 131        if (!$config) {
 132            throw new BException(BLocale::_('Invalid or missing DB configuration: %s', $name));
 133        }
 134        if (!empty($config['use'])) { //TODO: Prevent circular reference
 135            static::connect($config['use']);
 136            return BORM::get_db();
 137        }
 138        if (!empty($config['dsn'])) {
 139            $dsn = $config['dsn'];
 140            if (empty($config['dbname']) && preg_match('#dbname=(.*?)(;|$)#', $dsn, $m)) {
 141                $config['dbname'] = $m[1];
 142            }
 143        } else {
 144            if (empty($config['dbname'])) {
 145                throw new BException(BLocale::_("dbname configuration value is required for '%s'", $name));
 146            }
 147            $engine = !empty($config['engine']) ? $config['engine'] : 'mysql';
 148            $host = !empty($config['host']) ? $config['host'] : '127.0.0.1';
 149            switch ($engine) {
 150                case "mysql":
 151                    $dsn = "mysql:host={$host};dbname={$config['dbname']};charset=UTF8";
 152                    break;
 153
 154                default:
 155                    throw new BException(BLocale::_('Invalid DB engine: %s', $engine));
 156            }
 157        }
 158        $profile = BDebug::debug('DB.CONNECT '.$name);
 159        static::$_currentConnectionName = $name;
 160
 161        BORM::configure($dsn);
 162        BORM::configure('username', !empty($config['username']) ? $config['username'] : 'root');
 163        BORM::configure('password', !empty($config['password']) ? $config['password'] : '');
 164        BORM::configure('logging', !empty($config['logging']));
 165        BORM::set_db(null);
 166        BORM::setup_db();
 167        static::$_namedConnections[$name] = BORM::get_db();
 168        static::$_config = static::$_namedConnectionConfig[$name] = array(
 169            'dbname' => !empty($config['dbname']) ? $config['dbname'] : null,
 170            'table_prefix' => !empty($config['table_prefix']) ? $config['table_prefix'] : '',
 171        );
 172
 173        $db = BORM::get_db();
 174        BDebug::profile($profile);
 175        return $db;
 176    }
 177
 178    /**
 179    * DB friendly current date/time
 180    *
 181    * @return string
 182    */
 183    public static function now()
 184    {
 185        return gmstrftime('%Y-%m-%d %H:%M:%S');
 186    }
 187
 188    /**
 189    * Shortcut to run multiple queries from migrate scripts
 190    *
 191    * It doesn't make sense to run multiple queries in the same call and use $params
 192    *
 193    * @param string $sql
 194    * @param array $params
 195    * @param array $options
 196    *   - echo - echo all queries as they run
 197    * @throws Exception
 198    * @return array
 199    */
 200    public static function run($sql, $params=null, $options=array())
 201    {
 202        BDb::connect();
 203        $queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
 204        $results = array();
 205        foreach ($queries as $i=>$query){
 206           if (strlen(trim($query)) > 0) {
 207                try {
 208                    BDebug::debug('DB.RUN: '.$query);
 209                    if (!empty($options['echo'])) {
 210                        echo '<hr><pre>'.$query.'<pre>';
 211                    }
 212                    if (is_null($params)) {
 213                        $results[] = BORM::get_db()->exec($query);
 214                    } else {
 215                        $results[] = BORM::get_db()->prepare($query)->execute($params);
 216                    }
 217                } catch (Exception $e) {
 218                    echo "<hr>{$e->getMessage()}: <pre>{$query}</pre>";
 219                    if (empty($options['try'])) {
 220                        throw $e;
 221                    }
 222                }
 223           }
 224        }
 225        return $results;
 226    }
 227
 228    /**
 229    * Start transaction
 230    *
 231    * @param string $connectionName
 232    */
 233    public static function transaction($connectionName=null)
 234    {
 235        if (!is_null($connectionName)) {
 236            BDb::connect($connectionName);
 237        }
 238        BORM::get_db()->beginTransaction();
 239    }
 240
 241    /**
 242    * Commit transaction
 243    *
 244    * @param string $connectionName
 245    */
 246    public static function commit($connectionName=null)
 247    {
 248        if (!is_null($connectionName)) {
 249            BDb::connect($connectionName);
 250        }
 251        BORM::get_db()->commit();
 252    }
 253
 254    /**
 255    * Rollback transaction
 256    *
 257    * @param string $connectionName
 258    */
 259    public static function rollback($connectionName=null)
 260    {
 261        if (!is_null($connectionName)) {
 262            BDb::connect($connectionName);
 263        }
 264        BORM::get_db()->rollback();
 265    }
 266
 267    /**
 268    * Get db specific table name with pre-configured prefix for current connection
 269    *
 270    * Can be used as both BDb::t() and $this->t() within migration script
 271    * Convenient within strings and heredocs as {$this->t(...)}
 272    *
 273    * @param string $tableName
 274    * @return string
 275    */
 276    public static function t($tableName)
 277    {
 278        $a = explode('.', $tableName);
 279        $p = static::$_config['table_prefix'];
 280        return !empty($a[1]) ? $a[0].'.'.$p.$a[1] : $p.$a[0];
 281    }
 282
 283    /**
 284    * Convert array collection of objects from find_many result to arrays
 285    *
 286    * @param array $rows result of ORM::find_many()
 287    * @param string $method default 'as_array'
 288    * @param array|string $fields if specified, return only these fields
 289    * @param boolean $maskInverse if true, do not return specified fields
 290    * @return array
 291    */
 292    public static function many_as_array($rows, $method='as_array', $fields=null, $maskInverse=false)
 293    {
 294        $res = array();
 295        foreach ((array)$rows as $i=>$r) {
 296            if (!$r instanceof BModel) {
 297                echo "Rows are not models: <pre>"; print_r($r);
 298                debug_print_backtrace();
 299                exit;
 300            }
 301            $row = $r->$method();
 302            if (!is_null($fields)) $row = BUtil::arrayMask($row, $fields, $maskInverse);
 303            $res[$i] = $row;
 304        }
 305        return $res;
 306    }
 307
 308    /**
 309    * Construct where statement (for delete or update)
 310    *
 311    * Examples:
 312    * $w = BDb::where("f1 is null");
 313    *
 314    * // (f1='V1') AND (f2='V2')
 315    * $w = BDb::where(array('f1'=>'V1', 'f2'=>'V2'));
 316    *
 317    * // (f1=5) AND (f2 LIKE '%text%'):
 318    * $w = BDb::where(array('f1'=>5, array('f2 LIKE ?', '%text%')));
 319    *
 320    * // ((f1!=5) OR (f2 BETWEEN 10 AND 20)):
 321    * $w = BDb::where(array('OR'=>array(array('f1!=?', 5), array('f2 BETWEEN ? AND ?', 10, 20))));
 322    *
 323    * // (f1 IN (1,2,3)) AND NOT ((f2 IS NULL) OR (f2=10))
 324    * $w = BDb::where(array('f1'=>array(1,2,3)), 'NOT'=>array('OR'=>array("f2 IS NULL", 'f2'=>10)));
 325    *
 326    * // ((A OR B) AND (C OR D))
 327    * $w = BDb::where(array('AND', array('OR', 'A', 'B'), array('OR', 'C', 'D')));
 328    *
 329    * @param array $conds
 330    * @param boolean $or
 331    * @throws BException
 332    * @return array (query, params)
 333    */
 334    public static function where($conds, $or=false)
 335    {
 336        if (is_string($conds)) {
 337            return array($conds, array());
 338        }
 339        if (!is_array($conds)) {
 340            throw new BException("Invalid where parameter");
 341        }
 342        $where = array();
 343        $params = array();
 344        foreach ($conds as $f=>$v) {
 345            if (is_int($f)) {
 346                if (is_string($v)) { // freeform
 347                    $where[] = '('.$v.')';
 348                    continue;
 349                }
 350                if (is_array($v)) { // [freeform|arguments]
 351                    $sql = array_shift($v);
 352                    if ('AND'===$sql || 'OR'===$sql || 'NOT'===$sql) {
 353                        $f = $sql;
 354                    } else {
 355                        if (isset($v[0]) && is_array($v[0])) { // `field` IN (?)
 356                            $v = $v[0];
 357                            $sql = str_replace('(?)', '('.str_pad('', sizeof($v)*2-1, '?,').')', $sql);
 358                        }
 359                        $where[] = '('.$sql.')';
 360                        $params = array_merge($params, $v);
 361                        continue;
 362                    }
 363                } else {
 364                    throw new BException('Invalid token: '.print_r($v,1));
 365                }
 366            }
 367            if ('AND'===$f) {
 368                list($w, $p) = static::where($v);
 369                $where[] = '('.$w.')';
 370                $params = array_merge($params, $p);
 371            } elseif ('OR'===$f) {
 372                list($w, $p) = static::where($v, true);
 373                $where[] = '('.$w.')';
 374                $params = array_merge($params, $p);
 375            } elseif ('NOT'===$f) {
 376                list($w, $p) = static::where($v);
 377                $where[] = 'NOT ('.$w.')';
 378                $params = array_merge($params, $p);
 379            } elseif (is_array($v)) {
 380                $where[] = "({$f} IN (".str_pad('', sizeof($v)*2-1, '?,')."))";
 381                $params = array_merge($params, $v);
 382            } elseif (is_null($v)) {
 383                $where[] = "({$f} IS NULL)";
 384            } else {
 385                $where[] = "({$f}=?)";
 386                $params[] = $v;
 387            }
 388        }
 389#print_r($where); print_r($params);
 390        return array(join($or ? " OR " : " AND ", $where), $params);
 391    }
 392
 393    /**
 394    * Get database name for current connection
 395    *
 396    */
 397    public static function dbName()
 398    {
 399        if (!static::$_config) {
 400            throw new BException('No connection selected');
 401        }
 402        return !empty(static::$_config['dbname']) ? static::$_config['dbname'] : null;
 403    }
 404
 405    public static function ddlStart()
 406    {
 407        BDb::run(<<<EOT
 408/*!40101 SET SQL_MODE=''*/;
 409
 410/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 411/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 412/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 413/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 414EOT
 415        );
 416    }
 417
 418    public static function ddlFinish()
 419    {
 420        BDb::run(<<<EOT
 421/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 422/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 423/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 424/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 425EOT
 426        );
 427    }
 428
 429    /**
 430    * Clear DDL cache
 431    *
 432    */
 433    public static function ddlClearCache($fullTableName=null)
 434    {
 435        if ($fullTableName) {
 436            if (!static::dbName()) {
 437                static::connect(static::$_defaultConnectionName);
 438            }
 439            $a = explode('.', $fullTableName);
 440            $dbName = empty($a[1]) ? static::dbName() : $a[0];
 441            $tableName = empty($a[1]) ? $fullTableName : $a[1];
 442            static::$_tables[$dbName][$tableName] = null;
 443        } else {
 444            static::$_tables = array();
 445        }
 446    }
 447
 448    /**
 449    * Check whether table exists
 450    *
 451    * @param string $fullTableName
 452    * @return bool
 453    */
 454    public static function ddlTableExists($fullTableName)
 455    {
 456        if (!static::dbName()) {
 457            static::connect(static::$_defaultConnectionName);
 458        }
 459        $a = explode('.', $fullTableName);
 460        $dbName = empty($a[1]) ? static::dbName() : $a[0];
 461        $tableName = empty($a[1]) ? $fullTableName : $a[1];
 462        if (!isset(static::$_tables[$dbName])) {
 463            $tables = BORM::i()->raw_query("SHOW TABLES FROM `{$dbName}`", array())->find_many();
 464            $field = "Tables_in_{$dbName}";
 465            foreach ($tables as $t) {
 466                 static::$_tables[$dbName][$t->get($field)] = array();
 467            }
 468        } elseif (!isset(static::$_tables[$dbName][$tableName])) {
 469            $table = BORM::i()->raw_query("SHOW TABLES FROM `{$dbName}` LIKE ?", array($tableName))->find_one();
 470            if ($table) {
 471                static::$_tables[$dbName][$tableName] = array();
 472            }
 473        }
 474        return isset(static::$_tables[$dbName][$tableName]);
 475    }
 476
 477    /**
 478    * Get table field info
 479    *
 480    * @param string $fullTableName
 481    * @param string $fieldName if null return all fields
 482    * @throws BException
 483    * @return mixed
 484    */
 485    public static function ddlFieldInfo($fullTableName, $fieldName=null)
 486    {
 487        self::checkTable($fullTableName);
 488        $a = explode('.', $fullTableName);
 489        $dbName = empty($a[1]) ? static::dbName() : $a[0];
 490        $tableName = empty($a[1]) ? $fullTableName : $a[1];
 491        if (!isset(static::$_tables[$dbName][$tableName]['fields'])) {
 492            static::$_tables[$dbName][$tableName]['fields'] = BORM::i()
 493                ->raw_query("SHOW FIELDS FROM `{$dbName}`.`{$tableName}`", array())->find_many_assoc('Field');
 494
 495        }
 496        $res = static::$_tables[$dbName][$tableName]['fields'];
 497        return is_null($fieldName) ? $res : (isset($res[$fieldName]) ? $res[$fieldName] : null);
 498    }
 499
 500    /**
 501     * @param string $fullTableName
 502     * @throws BException
 503     */
 504    protected static function checkTable($fullTableName)
 505    {
 506        if (!static::ddlTableExists($fullTableName)) {
 507            throw new BException(BLocale::_('Invalid table name: %s', $fullTableName));
 508        }
 509    }
 510
 511    /**
 512    * Retrieve table index(es) info, if exist
 513    *
 514    * @param string $fullTableName
 515    * @param string $indexName
 516    * @throws BException
 517    * @return array|null
 518    */
 519    public static function ddlIndexInfo($fullTableName, $indexName=null)
 520    {
 521        if (!static::ddlTableExists($fullTableName)) {
 522            throw new BException(BLocale::_('Invalid table name: %s', $fullTableName));
 523        }
 524        $a = explode('.', $fullTableName);
 525        $dbName = empty($a[1]) ? static::dbName() : $a[0];
 526        $tableName = empty($a[1]) ? $fullTableName : $a[1];
 527        if (!isset(static::$_tables[$dbName][$tableName]['indexes'])) {
 528            static::$_tables[$dbName][$tableName]['indexes'] = BORM::i()
 529                ->raw_query("SHOW KEYS FROM `{$dbName}`.`{$tableName}`", array())->find_many_assoc('Key_name');
 530        }
 531        $res = static::$_tables[$dbName][$tableName]['indexes'];
 532        return is_null($indexName) ? $res : (isset($res[$indexName]) ? $res[$indexName] : null);
 533    }
 534
 535    /**
 536    * Retrieve table foreign key(s) info, if exist
 537    *
 538    * Mysql/InnoDB specific
 539    *
 540    * @param string $fullTableName
 541    * @param string $fkName
 542    * @throws BException
 543    * @return array|null
 544    */
 545    public static function ddlForeignKeyInfo($fullTableName, $fkName=null)
 546    {
 547        if (!static::ddlTableExists($fullTableName)) {
 548            throw new BException(BLocale::_('Invalid table name: %s', $fullTableName));
 549        }
 550        $a = explode('.', $fullTableName);
 551        $dbName = empty($a[1]) ? static::dbName() : $a[0];
 552        $tableName = empty($a[1]) ? $fullTableName : $a[1];
 553        if (!isset(static::$_tables[$dbName][$tableName]['fks'])) {
 554            static::$_tables[$dbName][$tableName]['fks'] = BORM::i()
 555                ->raw_query("SELECT * FROM information_schema.TABLE_CONSTRAINTS
 556                    WHERE TABLE_SCHEMA='{$dbName}' AND TABLE_NAME='{$tableName}'
 557                        AND CONSTRAINT_TYPE='FOREIGN KEY'", array())->find_many_assoc('CONSTRAINT_NAME');
 558        }
 559        $res = static::$_tables[$dbName][$tableName]['fks'];
 560        return is_null($fkName) ? $res : (isset($res[$fkName]) ? $res[$fkName] : null);
 561    }
 562
 563    /**
 564    * Create or update table
 565    *
 566    * @deprecates ddlTable and ddlTableColumns
 567    * @param string $fullTableName
 568    * @param array $def
 569    * @throws BException
 570    * @return array
 571    */
 572    public static function ddlTableDef($fullTableName, $def)
 573    {
 574        $fields = !empty($def['COLUMNS']) ? $def['COLUMNS'] : null;
 575        $primary = !empty($def['PRIMARY']) ? $def['PRIMARY'] : null;
 576        $indexes = !empty($def['KEYS']) ? $def['KEYS'] : null;
 577        $fks = !empty($def['CONSTRAINTS']) ? $def['CONSTRAINTS'] : null;
 578        $options = !empty($def['OPTIONS']) ? $def['OPTIONS'] : null;
 579
 580        if (!static::ddlTableExists($fullTableName)) {
 581            if (!$fields) {
 582                throw new BException('Missing fields definition for new table');
 583            }
 584            // temporary code duplication with ddlTable, until the other one is removed
 585            $fieldsArr = array();
 586            foreach ($fields as $f=>$def) {
 587                $fieldsArr[] = '`'.$f.'` '.$def;
 588            }
 589            $fields = null; // reset before update step
 590            if ($primary) {
 591                $fieldsArr[] = "PRIMARY KEY ".$primary;
 592                $primary = null; // reset before update step
 593            }
 594            $engine = !empty($options['engine']) ? $options['engine'] : 'InnoDB';
 595            $charset = !empty($options['charset']) ? $options['charset'] : 'utf8';
 596            $collate = !empty($options['collate']) ? $options['collate'] : 'utf8_general_ci';
 597            BORM::i()->raw_query("CREATE TABLE {$fullTableName} (".join(', ', $fieldsArr).")
 598                ENGINE={$engine} DEFAULT CHARSET={$charset} COLLATE={$collate}", array())->execute();
 599        }
 600        static::ddlTableColumns($fullTableName, $fields, $indexes, $fks, $options);
 601        static::ddlClearCache();
 602    }
 603
 604    /**
 605    * Create or update table
 606    *
 607    * @param string $fullTableName
 608    * @param array $fields
 609    * @param array $options
 610    *   - engine (default InnoDB)
 611    *   - charset (default utf8)
 612    *   - collate (default utf8_general_ci)
 613    * @return bool
 614    */
 615    public static function ddlTable($fullTableName, $fields, $options=null)
 616    {
 617        if (static::ddlTableExists($fullTableName)) {
 618            static::ddlTableColumns($fullTableName, $fields, null, null, $options); // altering options is not implemented
 619        } else {
 620            $fieldsArr = array();
 621            foreach ($fields as $f=>$def) {
 622                $fieldsArr[] = '`'.$f.'` '.$def;
 623            }
 624            if (!empty($options['primary'])) {
 625                $fieldsArr[] = "PRIMARY KEY ".$options['primary'];
 626            }
 627            $engine = !empty($options['engine']) ? $options['engine'] : 'InnoDB';
 628            $charset = !empty($options['charset']) ? $options['charset'] : 'utf8';
 629            $collate = !empty($options['collate']) ? $options['collate'] : 'utf8_general_ci';
 630            BORM::i()->raw_query("CREATE TABLE {$fullTableName} (".join(', ', $fieldsArr).")
 631                ENGINE={$engine} DEFAULT CHARSET={$charset} COLLATE={$collate}", array())->execute();
 632            static::ddlClearCache();
 633        }
 634        return true;
 635    }
 636
 637    /**
 638    * Add or change table columns
 639    *
 640    * BDb::ddlTableColumns('my_table', array(
 641    *   'field_to_create' => 'varchar(255) not null',
 642    *   'field_to_update' => 'decimal(12,2) null',
 643    *   'field_to_drop'   => 'DROP',
 644    * ));
 645    *
 646    * @param string $fullTableName
 647    * @param array $fields
 648    * @param array $indexes
 649    * @param array $fks
 650    * @return array
 651    */
 652    public static function ddlTableColumns($fullTableName, $fields, $indexes=null, $fks=null)
 653    {
 654        $tableFields = static::ddlFieldInfo($fullTableName, null);
 655        $tableFields = array_change_key_case($tableFields, CASE_LOWER);
 656        $alterArr = array();
 657        if ($fields) {
 658            foreach ($fields as $f=>$def) {
 659                $fLower = strtolower($f);
 660                if ($def==='DROP') {
 661                    if (!empty($tableFields[$fLower])) {
 662                        $alterArr[] = "DROP `{$f}`";
 663                    }
 664                } elseif (strpos($def, 'RENAME')===0) {
 665                    $a = explode(' ', $def, 3); //TODO: smarter parser, allow spaces in column name??
 666                    // Why not use a sprintf($def, $f) to fill in column name from $f?
 667                    $colName = $a[1];
 668                    $def = $a[2];
 669                    if (empty($tableFields[$fLower])) {
 670                        $f = $colName;
 671                    }
 672                    $alterArr[] = "CHANGE `{$f}` `{$colName}` {$def}";
 673                } elseif (empty($tableFields[$fLower])) {
 674                    $alterArr[] = "ADD `{$f}` {$def}";
 675                } else {
 676                    $alterArr[] = "CHANGE `{$f}` `{$f}` {$def}";
 677                }
 678            }
 679        }
 680        if ($indexes) {
 681            $tableIndexes = static::ddlIndexInfo($fullTableName);
 682            $tableIndexes = array_change_key_case($tableIndexes, CASE_LOWER);
 683            foreach ($indexes as $idx=>$def) {
 684                $idxLower = strtolower($idx);
 685                if ($def==='DROP') {
 686                    if (!empty($tableIndexes[$idxLower])) {
 687                        $alterArr[] = "DROP KEY `{$idx}`";
 688                    }
 689                } else {
 690                    if (!empty($tableIndexes[$idxLower])) {
 691                        $alterArr[] = "DROP KEY `{$idx}`";
 692                    }
 693                    if (strpos($def, 'PRIMARY')===0) {
 694                        $alterArr[] = "DROP PRIMARY KEY";
 695                        $def = substr($def, 7);
 696                        $alterArr[] = "ADD PRIMARY KEY `{$idx}` {$def}";
 697                    } elseif (strpos($def, 'UNIQUE')===0) {
 698                        $def = substr($def, 6);
 699                        $alterArr[] = "ADD UNIQUE KEY `{$idx}` {$def}";
 700                    } else {
 701                        $alterArr[] = "ADD KEY `{$idx}` {$def}";
 702                    }
 703                }
 704            }
 705        }
 706        if ($fks) {
 707            $tableFKs = static::ddlForeignKeyInfo($fullTableName);
 708            $tableFKs = array_change_key_case($tableFKs, CASE_LOWER);
 709            // @see http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
 710            // You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement.
 711            // Separate statements are required.
 712            $dropArr = array();
 713            foreach ($fks as $idx=>$def) {
 714                $idxLower = strtolower($idx);
 715                if ($def==='DROP') {
 716                    if (!empty($tableFKs[$idxLower])) {
 717                        $dropArr[] = "DROP FOREIGN KEY `{$idx}`";
 718                    }
 719                } else {
 720                    if (!empty($tableFKs[$idxLower])) {
 721                    // what if it is not foreign key constraint we do not doe anything to check for UNIQUE and PRIMARY constraint
 722                        $dropArr[] = "DROP FOREIGN KEY `{$idx}`";
 723                    }
 724                    $alterArr[] = "ADD CONSTRAINT `{$idx}` {$def}";
 725                }
 726            }
 727            if (!empty($dropArr)) {
 728                BORM::i()->raw_query("ALTER TABLE {$fullTableName} ".join(", ", $dropArr), array())->execute();
 729                static::ddlClearCache();
 730            }
 731        }
 732        $result = null;
 733        if ($alterArr) {
 734            $result = BORM::i()->raw_query("ALTER TABLE {$fullTableName} ".join(", ", $alterArr), array())->execute();
 735            static::ddlClearCache();
 736        }
 737        return $result;
 738    }
 739
 740    /**
 741     * A convenience method to add columns to table
 742     * It should check if columns exist before passing to self::ddlTableColumns
 743     * $columns array should be in same format as for ddlTableColumns:
 744     *
 745     * array(
 746     *      'field_name' => 'column definition',
 747     *      'field_two' => 'column definition',
 748     *      'field_three' => 'column definition',
 749     * )
 750     *
 751     * @param string $table
 752     * @param array $columns
 753     * @return array|null
 754     */
 755    public static function ddlAddColumns($table, $columns = array())
 756    {
 757       if (empty($columns)) {
 758           BDebug::log(__METHOD__ . ": columns array is empty.");
 759           return null;
 760       }
 761        $pass = array();
 762        $tableFields = array_keys(static::ddlFieldInfo($table));
 763        foreach ($columns as $field => $def) {
 764            if( in_array($field, $tableFields)) {
 765                continue;
 766            }
 767            $pass[$field] = $def;
 768        }
 769        return static::ddlTableColumns($table, $pass);
 770    }
 771
 772    /**
 773    * Clean array or object fields based on table columns and return an array
 774    *
 775    * @param string $table
 776    * @param array|object $data
 777    * @return array
 778    */
 779    public static function cleanForTable($table, $data)
 780    {
 781        $isObject = is_object($data);
 782        $result = array();
 783        foreach ($data as $k=>$v) {
 784            if (BDb::ddlFieldInfo($table, $k)) {
 785                $result[$k] = $isObject ? $data->get($k) : $data[$k];
 786            }
 787        }
 788        return $result;
 789    }
 790}
 791
 792/**
 793* Enhanced PDO class to allow for transaction nesting for mysql and postgresql
 794*
 795* @see http://us.php.net/manual/en/pdo.connections.php#94100
 796* @see http://www.kennynet.co.uk/2008/12/02/php-pdo-nested-transactions/
 797*/
 798class BPDO extends PDO
 799{
 800    // Database drivers that support SAVEPOINTs.
 801    protected static $_savepointTransactions = array("pgsql", "mysql");
 802
 803    // The current transaction level.
 804    protected $_transLevel = 0;
 805/*
 806    public static function exception_handler($exception)
 807    {
 808        // Output the exception details
 809        die('Uncaught exception: '. $exception->getMessage());
 810    }
 811
 812    public function __construct($dsn, $username='', $password='', $driver_options=array())
 813    {
 814        // Temporarily change the PHP exception handler while we . . .
 815        set_exception_handler(array(__CLASS__, 'exception_handler'));
 816
 817        // . . . create a PDO object
 818        parent::__construct($dsn, $username, $password, $driver_options);
 819
 820        // Change the exception handler back to whatever it was before
 821        restore_exception_handler();
 822    }
 823*/
 824    protected function _nestable() {
 825        return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
 826                        static::$_savepointTransactions);
 827    }
 828
 829    public function beginTransaction() {
 830        if (!$this->_nestable() || $this->_transLevel == 0) {
 831            parent::beginTransaction();
 832        } else {
 833            $this->exec("SAVEPOINT LEVEL{$this->_transLevel}");
 834        }
 835
 836        $this->_transLevel++;
 837    }
 838
 839    public function commit() {
 840        $this->_transLevel--;
 841
 842        if (!$this->_nestable() || $this->_transLevel == 0) {
 843            parent::commit();
 844        } else {
 845            $this->exec("RELEASE SAVEPOINT LEVEL{$this->_transLevel}");
 846        }
 847    }
 848
 849    public function rollBack() {
 850        $this->_transLevel--;
 851
 852        if (!$this->_nestable() || $this->_transLevel == 0) {
 853            parent::rollBack();
 854        } else {
 855            $this->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->_transLevel}");
 856        }
 857    }
 858}
 859
 860/**
 861* Enhanced ORMWrapper to support multiple database connections and many other goodies
 862*/
 863class BORM extends ORMWrapper
 864{
 865    /**
 866    * Singleton instance
 867    *
 868    * @var BORM
 869    */
 870    protected static $_instance;
 871
 872    /**
 873    * ID for profiling of the last run query
 874    *
 875    * @var int
 876    */
 877    protected static $_last_profile;
 878
 879    /**
 880    * Default class name for direct ORM calls
 881    *
 882    * @var string
 883    */
 884    protected $_class_name = 'BModel';
 885
 886    /**
 887    * Read DB connection for selects (replication slave)
 888    *
 889    * @var string|null
 890    */
 891    protected $_readConnectionName;
 892
 893    /**
 894    * Write DB connection for updates (master)
 895    *
 896    * @var string|null
 897    */
 898    protected $_writeConnectionName;
 899
 900    /**
 901    * Read DB name
 902    *
 903    * @var string
 904    */
 905    protected $_readDbName;
 906
 907    /**
 908    * Write DB name
 909    *
 910    * @var string
 911    */
 912    protected $_writeDbName;
 913
 914    /**
 915    * Old values in the object before ->set()
 916    *
 917    * @var array
 918    */
 919    protected $_old_values = array();
 920
 921    /**
 922    * Shortcut factory for generic instance
 923    *
 924    * @param bool $new
 925    * @return BORM
 926    */
 927    public static function i($new=false)
 928    {
 929        if ($new) {
 930            return new static('');
 931        }
 932        if (!static::$_instance) {
 933            static::$_instance = new static('');
 934        }
 935        return static::$_instance;
 936    }
 937
 938    protected function _quote_identifier($identifier) {
 939        if ($identifier[0]=='(') {
 940            return $identifier;
 941        }
 942        return parent::_quote_identifier($identifier);
 943    }
 944
 945    public static function get_config($key)
 946    {
 947        return !empty(static::$_config[$key]) ? static::$_config[$key] : null;
 948    }
 949
 950    /**
 951    * Public alias for _setup_db
 952    */
 953    public static function setup_db()
 954    {
 955        static::_setup_db();
 956    }
 957
 958    /**
 959     * Set up the database connection used by the class.
 960     * Use BPDO for nested transactions
 961     */
 962    protected static function _setup_db()
 963    {
 964        if (!is_object(static::$_db)) {
 965            $connection_string = static::$_config['connection_string'];
 966            $username = static::$_config['username'];
 967            $password = static::$_config['password'];
 968            $driver_options = static::$_config['driver_options'];
 969            if (empty($driver_options[PDO::MYSQL_ATTR_INIT_COMMAND])) { //ADDED
 970                $driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES utf8";
 971            }
 972            try { //ADDED: hide connection details from the error if not in DEBUG mode
 973                $db = new BPDO($connection_string, $username, $password, $driver_options); //UPDATED
 974            } catch (PDOException $e) {
 975                if (BDebug::is('DEBUG')) {
 976                    throw $e;
 977                } else {
 978                    throw new PDOException('Could not connect to database');
 979                }
 980            }
 981            $db->setAttribute(PDO::ATTR_ERRMODE, static::$_config['error_mode']);
 982            static::set_db($db);
 983        }
 984    }
 985
 986    /**
 987     * Set the PDO object used by Idiorm to communicate with the database.
 988     * This is public in case the ORM should use a ready-instantiated
 989     * PDO object as its database connection.
 990     */
 991    public static function set_db($db, $config=null)
 992    {
 993        if (!is_null($config)) {
 994            static::$_config = array_merge(static::$_config, $config);
 995        }
 996        static::$_db = $db;
 997        if (!is_null($db)) {
 998            static::_setup_identifier_quote_character();
 999        }
1000    }
1001
1002    /**
1003    * Set read/write DB connection names from model
1004    *
1005    * @param string $read
1006    * @param string $write
1007    * @return BORMWrapper
1008    */
1009    public function set_rw_db_names($read, $write)
1010    {
1011        $this->_readDbName = $read;
1012        $this->_writeDbName = $write;
1013        return $this;
1014    }
1015
1016    protected static function _log_query($query, $parameters)
1017    {
1018        $result = parent::_log_query($query, $parameters);
1019        static::$_last_profile = BDebug::debug('DB.RUN: '.(static::$_last_query ? static::$_last_query : 'LOGGING NOT ENABLED'));
1020        return $result;
1021    }
1022
1023    /**
1024    * Execute the SELECT query that has been built up by chaining methods
1025    * on this class. Return an array of rows as associative arrays.
1026    *
1027    * Connection will be switched to read, if set
1028    *
1029    * @return array
1030    */
1031    protected function _run()
1032    {
1033        BDb::connect($this->_readConnectionName);
1034        #$timer = microtime(true); // file log
1035        $result = parent::_run();
1036        #BDebug::log((microtime(true)-$timer).' '.static::$_last_query); // file log
1037        BDebug::profile(static::$_last_profile);
1038        static::$_last_profile = null;
1039        return $result;
1040    }
1041
1042    /**
1043    * Set or return table alias for the main table
1044    *
1045    * @param string|null $alias
1046    * @return BORM|string
1047    */
1048    public function table_alias($alias=null)
1049    {
1050        if (is_null($alias)) {
1051            return $this->_table_alias;
1052        }
1053        $this->_table_alias = $alias;
1054        return $this;
1055    }
1056
1057    /**
1058    * Add a column to the list of columns returned by the SELECT
1059    * query. This defaults to '*'. The second optional argument is
1060    * the alias to return the column as.
1061    *
1062    * @param string|array $column if array, select multiple columns
1063    * @param string $alias optional alias, if $column is array, used as table name
1064    * @return BORM
1065    */
1066    public function select($column, $alias=null)
1067    {
1068        if (is_array($column)) {
1069            foreach ($column as $k=>$v) {
1070                $col = (!is_null($alias) ? $alias.'.' : '').$v;
1071                if (is_int($k)) {
1072                    $this->select($col);
1073                } else {
1074                    $this->select($col, $k);
1075                }
1076            }
1077            return $this;
1078        }
1079        return parent::select($column, $alias);
1080    }
1081
1082    protected $_use_index = array();
1083
1084    public function use_index($index, $type='USE', $table='_')
1085    {
1086        $this->_use_index[$table] = compact('index', 'type');
1087        return $this;
1088    }
1089
1090    protected function _build_select_start() {
1091        $fragment = parent::_build_select_start();
1092        if (!empty($this->_use_index['_'])) {
1093            $idx = $this->_use_index['_'];
1094            $fragment .= ' '.$idx['type'].' INDEX ('.$idx['index'].') ';
1095        }
1096        return $fragment;
1097    }
1098
1099    protected function _add_result_column($expr, $alias=null) {
1100        if (!is_null($alias)) {
1101            $expr .= " AS " . $this->_quote_identifier($alias);
1102        }
1103        // ADDED TO AVOID DUPLICATE FIELDS
1104        if (in_array($expr, $this->_result_columns)) {
1105            return $this;
1106        }
1107
1108        if ($this->_using_default_result_columns) {
1109            $this->_result_columns = array($expr);
1110            $this->_using_default_result_columns = false;
1111        } else {
1112            $this->_result_columns[] = $expr;
1113        }
1114        return $this;
1115    }
1116
1117    public function clear_columns()
1118    {
1119        $this->_result_columns = array();
1120        return $this;
1121    }
1122
1123    /**
1124    * Return select sql statement built from the ORM object
1125    *
1126    * @return string
1127    */
1128    public function as_sql()
1129    {
1130        return $this->_build_select();
1131    }
1132
1133    /**
1134    * Execute the query and return PDO statement object
1135    *
1136    * Usage:
1137    *   $sth = $orm->execute();
1138    *   while ($row = $sth->fetch(PDO::FETCH_ASSOC)) { ... }
1139    *
1140    * @return PDOStatement
1141    */
1142    public function execute()
1143    {
1144        BDb::connect($this->_readConnectionName);
1145        $query = $this->_build_select();
1146        static::_log_query($query, $this->_values);
1147        $statement = static::$_db->prepare($query);
1148try {
1149        $statement->execute($this->_values);
1150} catch (Exception $e) {
1151echo $query;
1152print_r($e);
1153exit;
1154}
1155        return $statement;
1156    }
1157
1158    public function row_to_model($row)
1159    {
1160        return $this->_create_model_instance($this->_create_instance_from_row($row));
1161    }
1162
1163    /**
1164    * Iterate over select result with callback on each row
1165    *
1166    * @param mixed $callback
1167    * @param string $type
1168    * @return BORM
1169    */
1170    public function iterate($callback, $type='callback')
1171    {
1172        $statement = $this->execute();
1173        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
1174            $model = $this->row_to_model($row);
1175            switch ($type) {
1176                case 'callback': call_user_func($callback, $model); break;
1177                case 'method': $model->$callback(); break;
1178            }
1179        }
1180        return $this;
1181    }
1182
1183    /**
1184    * Extended where condition
1185    *
1186    * @param string|array $column_name if array - use where_complex() syntax
1187    * @param mixed $value
1188    */
1189    public function where($column_name, $value=null)
1190    {
1191        if (is_array($column_name)) {
1192            return $this->where_complex($column_name, !!$value);
1193        }
1194        return parent::where($column_name, $value);
1195    }
1196
1197    /**
1198    * Add a complex where condition
1199    *
1200    * @see BDb::where
1201    * @param array $conds
1202    * @param boolean $or
1203    * @return BORM
1204    */
1205    public function where_complex($conds, $or=false)
1206    {
1207        list($where, $params) = BDb::where($conds, $or);
1208        if (!$where) {
1209            return $this;
1210        }
1211        return $this->where_raw($where, $params);
1212    }
1213
1214    /**
1215     * Find one row
1216     *
1217     * @param int|null $id
1218     * @return BModel
1219     */
1220    public function find_one($id=null)
1221    {
1222        $class = $this->_class_name;
1223        if ($class::origClass()) {
1224            $class = $class::origClass();
1225        }
1226        BEvents::i()->fire($class.'::find_one:orm', array('orm'=>$this, 'class'=>$class, 'id'=>$id));
1227        $result = parent::find_one($id);
1228        BEvents::i()->fire($class.'::find_one:after', array('result'=>&$result, 'class'=>$class, 'id'=>$id));
1229        return $result;
1230    }
1231
1232    /**
1233    * Find many rows (SELECT)
1234    *
1235    * @return array
1236    */
1237    public function find_many()
1238    {
1239        $class = $this->_class_name;
1240        if ($class::origClass()) {
1241            $class = $class::origClass();
1242        }
1243        BEvents::i()->fire($class.'::find_many:orm', array('orm'=>$this, 'class'=>$class));
1244        $result = parent::find_many();
1245        BEvents::i()->fire($class.'::find_many:after', array('result'=>&$result, 'class'=>$class));
1246        return $result;
1247    }
1248
1249    /**
1250    * Find many records and return as associated array
1251    *
1252    * @param string|array $key if array, will create multi-dimensional array (currently 2D)
1253    * @param string|null $labelColumn
1254    * @param array $options (key_lower, key_trim)
1255    * @return array
1256    */
1257    public function find_many_assoc($key=null, $labelColumn=null, $options=array())
1258    {
1259        $objects = $this->find_many();
1260        $array = array();
1261        if (empty($key)) {
1262            $key = $this->_get_id_column_name();
1263        }
1264        foreach ($objects as $r) {
1265            $value = is_null($labelColumn) ? $r : (is_array($labelColumn) ? BUtil::arrayMask($r, $labelColumn) : $r->get($labelColumn));
1266            if (!is_array($key)) { // save on performance for 1D keys
1267                $v = $r->get($key);
1268                if (!empty($options['key_lower'])) $v = strtolower($v);
1269                if (!empty($options['key_trim'])) $v = trim($v);
1270                $array[$v] = $value;
1271            } else {
1272                $v1 = $r->get($key[0]);
1273                if (!empty($options['key_lower'])) $v1 = strtolower($v1);
1274                if (!empty($options['key_trim'])) $v1 = trim($v1);
1275                $v2 = $r->get($key[1]);
1276                if (!empty($options['key_lower'])) $v2 = strtolower($v2);
1277                if (!empty($options['key_trim'])) $v1 = trim($v2);
1278                $array[$v1][$v2] = $value;
1279            }
1280        }
1281        return $array;
1282    }
1283
1284    /**
1285     * Check whether the given field (or object itself) has been changed since this
1286     * object was saved.
1287     */
1288    public function is_dirty($key=null) {
1289        return is_null($key) ? !empty($this->_dirty_fields) : isset($this->_dirty_fields[$key]);
1290    }
1291
1292    /**
1293     * Set a property to a particular value on this object.
1294     * Flags that property as 'dirty' so it will be saved to the
1295     * database when save() is called.
1296     */
1297    public function set($key, $value) {
1298        if (!is_scalar($key)) {
1299            throw new BException('Key not scalar');
1300        }
1301        if (!array_key_exists($key, $this->_data)
1302            || is_null($this->_data[$key]) && !is_null($value)
1303            || !is_null($this->_data[$key]) && is_null($value)
1304            || is_scalar($this->_data[$key]) && is_scalar($value)
1305                && ((string)$this->_data[$key] !== (string)$value)
1306        ) {
1307#echo "DIRTY: "; var_dump($this->_data[$key], $value); echo "\n";
1308            if (!array_key_exists($key, $this->_old_values)) {
1309                $this->_old_values[$key] = array_key_exists($key, $this->_data) ? $this->_data[$key] : BNULL;
1310            }
1311            $this->_dirty_fields[$key] = $value;
1312        }
1313        $this->_data[$key] = $value;
1314    }
1315
1316    /**
1317    * Class to table map cache
1318    *
1319    * @var array
1320    */
1321    protected static $_classTableMap = array();
1322
1323    /**
1324     * Add a simple JOIN source to the query
1325     */
1326    public function _add_join_source($join_operator, $table, $constraint, $table_alias=null) {
1327        if (!isset(self::$_classTableMap[$table])) {
1328            if (class_exists($table) && is_subclass_of($table, 'BModel')) {
1329                $class = BClassRegistry::i()->className($table);
1330                self::$_classTableMap[$table] = $class::table();
1331            } else {
1332                self::$_classTableMap[$table] = false;
1333            }
1334        }
1335        if (self::$_classTableMap[$table]) {
1336            $table = self::$_classTableMap[$table];
1337        }
1338        return parent::_add_join_source($join_operator, $table, $constraint, $table_alias);
1339    }
1340
1341    /**
1342     * Save any fields which have been modified on this object
1343     * to the database.
1344     *
1345     * Connection will be switched to write, if set
1346     *
1347     * @return boolean
1348     */
1349    public function save()
1350    {
1351        BDb::connect($this->_writeConnectionName);
1352        $this->_dirty_fields = BDb::cleanForTable($this->_table_name, $this->_dirty_fields);
1353        if (true) {
1354            #if (array_diff_assoc($this->_old_values, $this->_dirty_fields)) {
1355                $result = parent::save();
1356            #}
1357        } else {
1358            echo $this->_class_name.'['.$this->id.']: ';
1359            print_r($this->_data);
1360            echo 'FROM: '; print_r($this->_old_values);
1361            echo 'TO: '; print_r($this->_dirty_fields); echo "\n\n";
1362            $result = true;
1363        }
1364        //$this->_old_values = array(); // commented out to make original loaded object old values available after save
1365        return $result;
1366    }
1367
1368    /**
1369    * Return dirty fields for debugging
1370    *
1371    * @return array
1372    */
1373    public function dirty_fields()
1374    {
1375        return $this->_dirty_fields;
1376    }
1377
1378    public function old_values($property='')
1379    {
1380        if ($property && isset($this->_old_values[$property])) {
1381            return $this->_old_values[$property];
1382        }
1383        return $this->_old_values;
1384    }
1385
1386    /**
1387     * Delete this record from the database
1388     *
1389     * Connection will be switched to write, if set
1390     *
1391     * @return boolean
1392     */
1393    public function delete()
1394    {
1395        BDb::connect($this->_writeConnectionName);
1396        return parent::delete();
1397    }
1398
1399     /**
1400     * Add an ORDER BY expression DESC clause
1401     */
1402     public function order_by_expr($expression) {
1403        $this->_order_by[] = "{$expression}";
1404        return $this;
1405     }
1406
1407    /**
1408    * Perform a raw query. The query should contain placeholders,
1409    * in either named or question mark style, and the parameters
1410    * should be an array of values which will be bound to the
1411    * placeholders in the query. If this method is called, all
1412    * other query building methods will be ignored.
1413    *
1414    * Connection will be set to write, if query is not SELECT or SHOW
1415    *
1416    * @param       $query
1417    * @param array $parameters
1418    * @return BORM
1419    */
1420    public function raw_query($query, $parameters=array())
1421    {
1422        if (preg_match('#^\s*(SELECT|SHOW)#i', $query)) {
1423            BDb::connect($this->_readConnectionName);
1424        } else {
1425            BDb::connect($this->_writeConnectionName);
1426        }
1427        return parent::raw_query($query, $parameters);
1428    }
1429
1430    /**
1431    * Get table name with prefix, if configured
1432    *
1433    * @param string $class_name
1434    * @return string
1435    */
1436    protected static function _get_table_name($class_name) {
1437        return BDb::t(parent::_get_table_name($class_name));
1438    }
1439
1440    /**
1441    * Set page constraints on collection for use in grids
1442    *
1443    * Request and result vars:
1444    * - p: page number
1445    * - ps: page size
1446    * - s: sort order by (if default is array - only these values are allowed) (alt: sort|dir)
1447    * - sd: sort direction (asc/desc)
1448    * - sc: sort combined (s|sd)
1449    * - rs: requested row start (optional in request, not dependent on page size)
1450    * - rc: requested row count (optional in request, not dependent on page size)
1451    * - c: total row count (return only)
1452    * - mp: max page (return only)
1453    *
1454    * Options (all optional):
1455    * - format: 0..2
1456    * - as_array: true or method name
1457    *
1458    * @param array $r pagination request, if null - take from request query string
1459    * @param array $d default values and options
1460    * @return array
1461    */
1462    public function paginate($r=null, $d=array())
1463    {
1464        if (is_null($r)) {
1465            $r = BRequest::i()->request(); // GET request
1466        }
1467        $d = (array)$d; // make sure it's array
1468        if (!empty($r['sc']) && empty($r['s']) && empty($r['sd'])) { // sort and dir combined
1469            list($r['s'], $r['sd']) = preg_split('#[| ]#', trim($r['sc']));
1470        }
1471        if (!empty($r['s']) && !empty($d['s']) && is_array($d['s'])) { // limit by these values only
1472            if (!in_array($r['s'], $d['s'])) $r['s'] = null;
1473            $d['s'] = null;
1474        }
1475        if (!empty($r['sd']) && $r['sd']!='asc' && $r['sd']!='desc') { // only asc and desc are allowed
1476            $r['sd'] = null;
1477        }
1478        $s = array( // state
1479            'p'  => !empty($r['p'])  && is_numeric($r['p']) ? $r['p']  : (isset($d['p'])  ? $d['p']  : 1), // page
1480            'ps' => !empty($r['ps']) && is_numeric($r['ps']) ? $r['ps'] : (isset($d['ps']) ? $d['ps'] : 100), // page size
1481            's'  => !empty($r['s'])  ? $r['s']  : (isset($d['s'])  ? $d['s']  : ''), // sort by
1482            'sd' => !empty($r['sd']) ? $r['sd'] : (isset($d['sd']) ? $d['sd'] : 'asc'), // sort dir
1483            'rs' => !empty($r['rs']) ? $r['rs'] : null, // starting row
1484            'rc' => !empty($r['rc']) ? $r['rc'] : null, // total rows on page
1485            'q'  => !empty($r['q'])  ? $r['q'] : null, // query string
1486            'c'  => !empty($d['c'])  ? $d['c'] : null, //total found
1487        );
1488#print_r($r); print_r($d); print_r($s); exit;
1489        $s['sc'] = $s['s'].' '.$s['sd']; // sort combined for state
1490
1491        #$s['c'] = 600000;
1492        if (empty($s['c'])){
1493            $cntOrm = clone $this; // clone ORM to count
1494            $s['c'] = $cntOrm->count(); // total row count
1495            unset($cntOrm); // free mem
1496        }
1497
1498        $s['mp'] = ceil($s['c']/$s['ps']

Large files files are truncated, but you can click here to view the full file