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' );
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->_db = 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 $_db = null;
76:
77:
78:
79: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
80: * Public methods
81: */
82:
83: /**
84: * Commit a database transaction.
85: *
86: * Use with {@link transaction} and {@link rollback}.
87: * @return self
88: */
89: public function commit ()
90: {
91: call_user_func($this->query_driver.'::commit', $this->_db );
92: return $this;
93: }
94:
95:
96: /**
97: * Perform a delete query on a table.
98: *
99: * This is a short cut method that creates an update query and then uses
100: * the query('delete'), table, where and exec methods of the query.
101: * @param string|string[] $table Table name(s) to act upon.
102: * @param array $where Where condition for what to delete - see {@link
103: * Query::where}.
104: * @return Result
105: */
106: public function delete ( $table, $where=null )
107: {
108: return $this->query( 'delete' )
109: ->table( $table )
110: ->where( $where )
111: ->exec();
112: }
113:
114:
115: /**
116: * Insert data into a table.
117: *
118: * This is a short cut method that creates an update query and then uses
119: * the query('insert'), table, set and exec methods of the query.
120: * @param string|string[] $table Table name(s) to act upon.
121: * @param array $set Field names and values to set - see {@link
122: * Query::set}.
123: * @return Result
124: */
125: public function insert ( $table, $set )
126: {
127: return $this->query( 'insert' )
128: ->table( $table )
129: ->set( $set )
130: ->exec();
131: }
132:
133:
134: /**
135: * Update or Insert data. When doing an insert, the where condition is
136: * added as a set field
137: * @param string|string[] $table Table name(s) to act upon.
138: * @param array $set Field names and values to set - see {@link
139: * Query::set}.
140: * @param array $where Where condition for what to update - see {@link
141: * Query::where}.
142: * @return Result
143: */
144: public function push ( $table, $set, $where=null )
145: {
146: // Update or insert
147: if ( $this->select( $table, "*", $where )->count() > 0 ) {
148: return $this->update( $table, $set, $where );
149: }
150:
151: // Add the where condition to the values to set
152: foreach ($where as $key => $value) {
153: if ( ! isset( $set[ $key ] ) ) {
154: $set[ $key ] = $value;
155: }
156: }
157:
158: return $this->insert( $table, $set );
159: }
160:
161:
162: /**
163: * Create a query object to build a database query.
164: * @param string $type Query type - select, insert, update or delete.
165: * @param string|string[] $table Table name(s) to act upon.
166: * @return Query
167: */
168: public function query ( $type, $table=null )
169: {
170: return new $this->query_driver( $this->_db, $type, $table );
171: }
172:
173:
174: /**
175: * Quote a string for a quote. Note you should generally use a bind!
176: * @param string $val Value to quote
177: * @param string $type Value type
178: * @return string
179: */
180: public function quote ( $val, $type=\PDO::PARAM_STR )
181: {
182: return $this->_db->quote( $val, $type );
183: }
184:
185:
186: /**
187: * Create a `Query` object that will execute a custom SQL query. This is
188: * similar to the `sql` method, but in this case you must call the `exec()`
189: * method of the returned `Query` object manually. This can be useful if you
190: * wish to bind parameters using the query `bind` method to ensure data is
191: * properly escaped.
192: *
193: * @return Result
194: *
195: * @example
196: * Safely escape user input
197: * <code>
198: * $db
199: * ->raw()
200: * ->bind( ':date', $_POST['date'] )
201: * ->exec( 'SELECT * FROM staff where date < :date' );
202: * </code>
203: */
204: public function raw ()
205: {
206: return $this->query( 'raw' );
207: }
208:
209:
210: /**
211: * Rollback the database state to the start of the transaction.
212: *
213: * Use with {@link transaction} and {@link commit}.
214: * @return self
215: */
216: public function rollback ()
217: {
218: call_user_func($this->query_driver.'::rollback', $this->_db );
219: return $this;
220: }
221:
222:
223: /**
224: * Select data from a table.
225: *
226: * This is a short cut method that creates an update query and then uses
227: * the query('select'), table, get, where and exec methods of the query.
228: * @param string|string[] $table Table name(s) to act upon.
229: * @param array $field Fields to get from the table(s) - see {@link
230: * Query::get}.
231: * @param array $where Where condition for what to select - see {@link
232: * Query::where}.
233: * @param array $orderBy Order condition - see {@link
234: * Query::order}.
235: * @return Result
236: */
237: public function select ( $table, $field="*", $where=null, $orderBy=null )
238: {
239: return $this->query( 'select' )
240: ->table( $table )
241: ->get( $field )
242: ->where( $where )
243: ->order( $orderBy )
244: ->exec();
245: }
246:
247:
248: /**
249: * Select distinct data from a table.
250: *
251: * This is a short cut method that creates an update query and then uses the
252: * query('select'), distinct ,table, get, where and exec methods of the
253: * query.
254: * @param string|string[] $table Table name(s) to act upon.
255: * @param array $field Fields to get from the table(s) - see {@link
256: * Query::get}.
257: * @param array $where Where condition for what to select - see {@link
258: * Query::where}.
259: * @param array $orderBy Order condition - see {@link
260: * Query::order}.
261: * @return Result
262: */
263: public function selectDistinct ( $table, $field="*", $where=null, $orderBy=null )
264: {
265: return $this->query( 'select' )
266: ->table( $table )
267: ->distinct( true )
268: ->get( $field )
269: ->where( $where )
270: ->order( $orderBy )
271: ->exec();
272: }
273:
274:
275: /**
276: * Execute an raw SQL query - i.e. give the method your own SQL, rather
277: * than having the Database classes building it for you.
278: *
279: * This method will execute the given SQL immediately. Use the `raw()`
280: * method if you need the ability to add bound parameters.
281: * @param string $sql SQL string to execute (only if _type is 'raw').
282: * @return Result
283: *
284: * @example
285: * Basic select
286: * <code>
287: * $result = $db->sql( 'SELECT * FROM myTable;' );
288: * </code>
289: *
290: * @example
291: * Set the character set of the connection
292: * <code>
293: * $db->sql("SET character_set_client=utf8");
294: * $db->sql("SET character_set_connection=utf8");
295: * $db->sql("SET character_set_results=utf8");
296: * </code>
297: */
298: public function sql ( $sql )
299: {
300: return $this->query( 'raw' )
301: ->exec( $sql );
302: }
303:
304:
305: /**
306: * Start a new database transaction.
307: *
308: * Use with {@link commit} and {@link rollback}.
309: * @return self
310: */
311: public function transaction ()
312: {
313: call_user_func($this->query_driver.'::transaction', $this->_db );
314: return $this;
315: }
316:
317:
318: /**
319: * Update data.
320: *
321: * This is a short cut method that creates an update query and then uses
322: * the query('update'), table, set, where and exec methods of the query.
323: * @param string|string[] $table Table name(s) to act upon.
324: * @param array $set Field names and values to set - see {@link
325: * Query::set}.
326: * @param array $where Where condition for what to update - see {@link
327: * Query::where}.
328: * @return Result
329: */
330: public function update ( $table, $set=null, $where=null )
331: {
332: return $this->query( 'update' )
333: ->table( $table )
334: ->set( $set )
335: ->where( $where )
336: ->exec();
337: }
338: };
339:
340: