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: