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