      1: <?php
      2: /**
      3:  * DataTables PHP libraries.
      4:  *
      5:  * PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
      6:  *
      7:  *  @author    SpryMedia
      8:  *  @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
      9:  *  @license   http://editor.datatables.net/license DataTables Editor
     10:  *  @link      http://editor.datatables.net
     11:  */
     12:
     13: namespace DataTables;
     14: if (!defined('DATATABLES')) exit();
     15:
     16: use
     17:     DataTables\Database\Query,
     18:     DataTables\Database\Result;
     19:
     20:
     21: /**
     22:  * DataTables Database connection object.
     23:  *
     24:  * Create a database connection which may then have queries performed upon it.
     25:  *
     26:  * This is a database abstraction class that can be used on multiple different
     27:  * databases. As a result of this, it might not be suitable to perform complex
     28:  * queries through this interface or vendor specific queries, but everything
     29:  * required for basic database interaction is provided through the abstracted
     30:  * methods.
     31:  */
     32: class Database {
     33:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     34:      * Constructor
     35:      */
     36:
     37:     /**
     38:      * Database instance constructor.
     39:      *  @param string[] $opts Array of connection parameters for the database:
     40:      *    <code>
     41:      *      array(
     42:      *          "user" => "", // User name
     43:      *          "pass" => "", // Password
     44:      *          "host" => "", // Host name
     45:      *          "port" => "", // Port
     46:      *          "db"   => "", // Database name
     47:      *          "type" => ""  // Datable type: "Mysql", "Postgres" or "Sqlite"
     48:      *      )
     49:      *    </code>
     50:      */
     51:     function __construct( $opts )
     52:     {
     53:         $types = array( 'Mysql', 'Oracle', 'Postgres', 'Sqlite', 'Sqlserver', 'Db2', 'Firebird' );
     54:
     55:         if ( ! in_array( $opts['type'], $types ) ) {
     56:             throw new \Exception(
     57:                 "Unknown database driver type. Must be one of ".implode(', ', $types),
     58:                 1
     59:             );
     60:         }
     61:
     62:         $this->type = $opts['type'];
     63:         $this->query_driver = "DataTables\\Database\\Driver\\".$opts['type'].'Query';
     64:         $this->_dbResource = isset( $opts['pdo'] ) ?
     65:             $opts['pdo'] :
     66:             call_user_func($this->query_driver.'::connect', $opts );
     67:     }
     68:
     69:
     70:
     71:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     72:      * Private properties
     73:      */
     74:
     75:     /** @var resource */
     76:     private $_dbResource = null;
     77:
     78:     /** @var callable */
     79:     private $_debugCallback = null;
     80:
     81:
     82:
     83:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     84:      * Public methods
     85:      */
     86:
     87:     /**
     88:      * Determine if there is any data in the table that matches the query
     89:      * condition
     90:      *
     91:      * @param string|string[] $table Table name(s) to act upon.
     92:      * @param array $where Where condition for what to select - see {@link
     93:      *   Query::where}.
     94:      * @return boolean Boolean flag - true if there were rows
     95:      */
     96:     public function any( $table, $where=null )
     97:     {
     98:         $res = $this->query( 'select' )
     99:             ->table( $table )
    100:             ->get( '*' )
    101:             ->where( $where )
    102:             ->exec();
    103:
    104:         return $res->count() > 0;
    105:     }
    106:
    107:
    108:     /**
    109:      * Commit a database transaction.
    110:      *
    111:      * Use with {@link transaction} and {@link rollback}.
    112:      *  @return self
    113:      */
    114:     public function commit ()
    115:     {
    116:         call_user_func($this->query_driver.'::commit', $this->_dbResource );
    117:         return $this;
    118:     }
    119:
    120:
    121:     /**
    122:      * Get a count from a table.
    123:      *  @param string|string[] $table Table name(s) to act upon.
    124:      *  @param string $field Primary key field name
    125:      *  @param array $where Where condition for what to select - see {@link
    126:      *    Query::where}.
    127:      *  @return Number
    128:      */
    129:     public function count ( $table, $field="id", $where=null )
    130:     {
    131:         $res = $this->query( 'count' )
    132:             ->table( $table )
    133:             ->get( $field )
    134:             ->where( $where )
    135:             ->exec();
    136:
    137:         $cnt = $res->fetch();
    138:         return $cnt['cnt'];
    139:     }
    140:
    141:
    142:     /**
    143:      * Get / set debug mode.
    144:      *
    145:      *  @param boolean $_ Debug mode state. If not given, then used as a getter.
    146:      *  @return boolean|self Debug mode state if no parameter is given, or
    147:      *    self if used as a setter.
    148:      */
    149:     public function debug ( $set=null )
    150:     {
    151:         if ( $set === null ) {
    152:             return $this->_debugCallback ? true : false;
    153:         }
    154:         else if ( $set === false ) {
    155:             $this->_debugCallback = null;
    156:         }
    157:         else {
    158:             $this->_debugCallback = $set;
    159:         }
    160:
    161:         return $this;
    162:     }
    163:
    164:
    165:     /**
    166:      * Perform a delete query on a table.
    167:      *
    168:      * This is a short cut method that creates an update query and then uses
    169:      * the query('delete'), table, where and exec methods of the query.
    170:      *  @param string|string[] $table Table name(s) to act upon.
    171:      *  @param array $where Where condition for what to delete - see {@link
    172:      *    Query::where}.
    173:      *  @return Result
    174:      */
    175:     public function delete ( $table, $where=null )
    176:     {
    177:         return $this->query( 'delete' )
    178:             ->table( $table )
    179:             ->where( $where )
    180:             ->exec();
    181:     }
    182:
    183:
    184:     /**
    185:      * Insert data into a table.
    186:      *
    187:      * This is a short cut method that creates an update query and then uses
    188:      * the query('insert'), table, set and exec methods of the query.
    189:      *  @param string|string[] $table Table name(s) to act upon.
    190:      *  @param array $set Field names and values to set - see {@link
    191:      *    Query::set}.
    192:      *  @param  array $pkey Primary key column names (this is an array for
    193:      *    forwards compt, although only the first item in the array is actually
    194:      *    used). This doesn't need to be set, but it must be if you want to use
    195:      *    the `Result->insertId()` method.
    196:      *  @return Result
    197:      */
    198:     public function insert ( $table, $set, $pkey='' )
    199:     {
    200:         return $this->query( 'insert' )
    201:             ->pkey( $pkey )
    202:             ->table( $table )
    203:             ->set( $set )
    204:             ->exec();
    205:     }
    206:
    207:
    208:     /**
    209:      * Update or Insert data. When doing an insert, the where condition is
    210:      * added as a set field
    211:      *  @param string|string[] $table Table name(s) to act upon.
    212:      *  @param array $set Field names and values to set - see {@link
    213:      *    Query::set}.
    214:      *  @param array $where Where condition for what to update - see {@link
    215:      *    Query::where}.
    216:      *  @param  array $pkey Primary key column names (this is an array for
    217:      *    forwards compt, although only the first item in the array is actually
    218:      *    used). This doesn't need to be set, but it must be if you want to use
    219:      *    the `Result->insertId()` method. Only used if an insert is performed.
    220:      *  @return Result
    221:      */
    222:     public function push ( $table, $set, $where=null, $pkey='' )
    223:     {
    224:         $selectColumn = '*';
    225:
    226:         if ( $pkey ) {
    227:             $selectColumn = is_array($pkey) ?
    228:                 $pkey[0] :
    229:                 $pkey;
    230:         }
    231:
    232:         // Update or insert
    233:         if ( $this->select( $table, $selectColumn, $where )->count() > 0 ) {
    234:             return $this->update( $table, $set, $where );
    235:         }
    236:
    237:         // Add the where condition to the values to set
    238:         foreach ($where as $key => $value) {
    239:             if ( ! isset( $set[ $key ] ) ) {
    240:                 $set[ $key ] = $value;
    241:             }
    242:         }
    243:
    244:         return $this->insert( $table, $set, $pkey );
    245:     }
    246:
    247:
    248:     /**
    249:      * Create a query object to build a database query.
    250:      *  @param string $type Query type - select, insert, update or delete.
    251:      *  @param string|string[] $table Table name(s) to act upon.
    252:      *  @return Query
    253:      */
    254:     public function query ( $type, $table=null )
    255:     {
    256:         return new $this->query_driver( $this, $type, $table );
    257:     }
    258:
    259:
    260:     /**
    261:      * Quote a string for a quote. Note you should generally use a bind!
    262:      *  @param string $val Value to quote
    263:      *  @param string $type Value type
    264:      *  @return string
    265:      */
    266:     public function quote ( $val, $type=\PDO::PARAM_STR )
    267:     {
    268:         return $this->_dbResource->quote( $val, $type );
    269:     }
    270:
    271:
    272:     /**
    273:      * Create a `Query` object that will execute a custom SQL query. This is
    274:      * similar to the `sql` method, but in this case you must call the `exec()`
    275:      * method of the returned `Query` object manually. This can be useful if you
    276:      * wish to bind parameters using the query `bind` method to ensure data is
    277:      * properly escaped.
    278:      *
    279:      *  @return Result
    280:      *
    281:      *  @example
    282:      *    Safely escape user input
    283:      *    <code>
    284:      *    $db
    285:      *      ->raw()
    286:      *      ->bind( ':date', $_POST['date'] )
    287:      *      ->exec( 'SELECT * FROM staff where date < :date' );
    288:      *    </code>
    289:      */
    290:     public function raw ()
    291:     {
    292:         return $this->query( 'raw' );
    293:     }
    294:
    295:
    296:     /**
    297:      * Get the database resource connector. This is typically a PDO object.
    298:      * @return resource PDO connection resource (driver dependent)
    299:      */
    300:     public function resource ()
    301:     {
    302:         return $this->_dbResource;
    303:     }
    304:
    305:
    306:     /**
    307:      * Rollback the database state to the start of the transaction.
    308:      *
    309:      * Use with {@link transaction} and {@link commit}.
    310:      *  @return self
    311:      */
    312:     public function rollback ()
    313:     {
    314:         call_user_func($this->query_driver.'::rollback', $this->_dbResource );
    315:         return $this;
    316:     }
    317:
    318:
    319:     /**
    320:      * Select data from a table.
    321:      *
    322:      * This is a short cut method that creates an update query and then uses
    323:      * the query('select'), table, get, where and exec methods of the query.
    324:      *  @param string|string[] $table Table name(s) to act upon.
    325:      *  @param array $field Fields to get from the table(s) - see {@link
    326:      *    Query::get}.
    327:      *  @param array $where Where condition for what to select - see {@link
    328:      *    Query::where}.
    329:      *  @param array $orderBy Order condition - see {@link
    330:      *    Query::order}.
    331:      *  @return Result
    332:      */
    333:     public function select ( $table, $field="*", $where=null, $orderBy=null )
    334:     {
    335:         return $this->query( 'select' )
    336:             ->table( $table )
    337:             ->get( $field )
    338:             ->where( $where )
    339:             ->order( $orderBy )
    340:             ->exec();
    341:     }
    342:
    343:
    344:     /**
    345:      * Select distinct data from a table.
    346:      *
    347:      * This is a short cut method that creates an update query and then uses the
    348:      * query('select'), distinct ,table, get, where and exec methods of the
    349:      * query.
    350:      *  @param string|string[] $table Table name(s) to act upon.
    351:      *  @param array $field Fields to get from the table(s) - see {@link
    352:      *    Query::get}.
    353:      *  @param array $where Where condition for what to select - see {@link
    354:      *    Query::where}.
    355:      *  @param array $orderBy Order condition - see {@link
    356:      *    Query::order}.
    357:      *  @return Result
    358:      */
    359:     public function selectDistinct ( $table, $field="*", $where=null, $orderBy=null )
    360:     {
    361:         return $this->query( 'select' )
    362:             ->table( $table )
    363:             ->distinct( true )
    364:             ->get( $field )
    365:             ->where( $where )
    366:             ->order( $orderBy )
    367:             ->exec();
    368:     }
    369:
    370:
    371:     /**
    372:      * Execute an raw SQL query - i.e. give the method your own SQL, rather
    373:      * than having the Database classes building it for you.
    374:      *
    375:      * This method will execute the given SQL immediately. Use the `raw()`
    376:      * method if you need the ability to add bound parameters.
    377:      *  @param string $sql SQL string to execute (only if _type is 'raw').
    378:      *  @return Result
    379:      *
    380:      *  @example
    381:      *    Basic select
    382:      *    <code>
    383:      *    $result = $db->sql( 'SELECT * FROM myTable;' );
    384:      *    </code>
    385:      *
    386:      *  @example
    387:      *    Set the character set of the connection
    388:      *    <code>
    389:      *    $db->sql("SET character_set_client=utf8");
    390:      *    $db->sql("SET character_set_connection=utf8");
    391:      *    $db->sql("SET character_set_results=utf8");
    392:      *    </code>
    393:      */
    394:     public function sql ( $sql )
    395:     {
    396:         return $this->query( 'raw' )
    397:             ->exec( $sql );
    398:     }
    399:
    400:
    401:     /**
    402:      * Start a new database transaction.
    403:      *
    404:      * Use with {@link commit} and {@link rollback}.
    405:      *  @return self
    406:      */
    407:     public function transaction ()
    408:     {
    409:         call_user_func($this->query_driver.'::transaction', $this->_dbResource );
    410:         return $this;
    411:     }
    412:
    413:
    414:     /**
    415:      * Update data.
    416:      *
    417:      * This is a short cut method that creates an update query and then uses
    418:      * the query('update'), table, set, where and exec methods of the query.
    419:      *  @param string|string[] $table Table name(s) to act upon.
    420:      *  @param array $set Field names and values to set - see {@link
    421:      *    Query::set}.
    422:      *  @param array $where Where condition for what to update - see {@link
    423:      *    Query::where}.
    424:      *  @return Result
    425:      */
    426:     public function update ( $table, $set=null, $where=null )
    427:     {
    428:         return $this->query( 'update' )
    429:             ->table( $table )
    430:             ->set( $set )
    431:             ->where( $where )
    432:             ->exec();
    433:     }
    434:
    435:
    436:     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    437:      * Internal functions
    438:      */
    439:
    440:     /**
    441:      * Get debug query information.
    442:      *
    443:      *  @return array Information about the queries used. When this method is
    444:      *    called it will reset the query cache.
    445:      *  @internal
    446:      */
    447:     public function debugInfo ( $query=null, $bindings=null )
    448:     {
    449:         $callback = $this->_debugCallback;
    450:
    451:         if ( $callback ) {
    452:             $callback( array(
    453:                 "query"    => $query,
    454:                 "bindings" => $bindings
    455:             ) );
    456:         }
    457:
    458:         return $this;
    459:     }
    460: };
    461:
    462: