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