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