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: