1: <?php
2:
3: /**
4: * SQLite3 database server access classes
5: *
6: * @package objSQL
7: * @version 3.2.0
8: * @author MT Jordan <mtjo62@gmail.com>
9: * @copyright 2013
10: * @license zlib/libpng
11: * @link http://objsql.sourceforge.net
12: */
13:
14:
15: /*************************************************************************************************************
16: * Begin database connection/utility class
17: ************************************************************************************************************/
18:
19:
20: class sqlite3_connection extends obj_access
21: {
22: /**********************************************
23: * Internal variables
24: *********************************************/
25:
26: /**
27: * Database connection instance
28: *
29: * @access private
30: * @var mixed
31: */
32: private $obj_connection;
33:
34: /**
35: * Database connection information
36: *
37: * @access private
38: * @var array
39: */
40: private $obj_datasource;
41:
42: /**********************************************
43: * Class methods
44: *********************************************/
45:
46: /**
47: * Constructor
48: *
49: * @access public
50: * @param array $datasource
51: */
52: public function __construct( $datasource )
53: {
54: $this->obj_datasource = $datasource[4];
55: }
56:
57: /**
58: * Returns database connection object
59: *
60: * @access protected
61: * @return mixed
62: */
63: protected function obj_db_connection()
64: {
65: $db_connection = new SQLite3( $this->obj_datasource );
66:
67: return ( is_object( $db_connection ) ) ? $this->obj_connection = $db_connection : false;
68: }
69:
70: /**
71: * Closes connection to database server
72: *
73: * @access protected
74: * @return bool
75: */
76: protected function obj_db_close()
77: {
78: return $this->obj_connection->close();
79: }
80:
81: /**
82: * Returns error flag for current connection object
83: *
84: * @access protected
85: * @return bool
86: */
87: protected function obj_db_error()
88: {
89: return ( $this->obj_connection->lastErrorCode() ) ? true : false;
90: }
91:
92: /**
93: * Escapes string data for database insertion
94: *
95: * @access protected
96: * @param mixed $data
97: * @return mixed
98: */
99: protected function obj_db_escape_data( $data )
100: {
101: return $this->obj_connection->escapeString( $data );
102: }
103:
104: /**
105: * Returns error message for current connection object
106: *
107: * @access protected
108: * @return str
109: */
110: protected function obj_db_message()
111: {
112: return ( $this->obj_db_error() ) ? $this->obj_connection->lastErrorMsg() : null;
113: }
114:
115: /**
116: * Returns database server information
117: *
118: * @access protected
119: * @return array
120: */
121: protected function obj_db_info()
122: {
123: return array( $this->obj_connection->version()['versionString'], 'UTF8', $this->obj_datasource );
124: }
125:
126: /**
127: * Returns row count for named table with arguments
128: * Returns -1 if undetermined or failure
129: *
130: * @access protected
131: * @param str $table
132: * @param str $cols
133: * @param str $where
134: * @return int
135: */
136: protected function obj_db_rowcount( $table, $cols=false, $where=false )
137: {
138: $query_cols = ( !trim( $cols ) ) ? '*' : $cols;
139: $query_where = ( !trim( $where ) ) ? '' : "WHERE $where";
140:
141: $query_stmt = $this->obj_connection->query( "SELECT COUNT($query_cols) FROM $table $query_where" );
142:
143: if ( !$query_stmt || !is_object( $query_stmt ) )
144: return -1;
145:
146: while ( $num_rows = $query_stmt->fetchArray( SQLITE3_NUM ) )
147: $rowcount = ( $num_rows[0] >= 0 ) ? $num_rows[0] : -1;
148:
149: $query_stmt->finalize();
150:
151: return $rowcount;
152: }
153: }
154:
155:
156: /*************************************************************************************************************
157: * End database connection class
158: ************************************************************************************************************/
159:
160:
161: /*************************************************************************************************************
162: * Begin database statement class
163: ************************************************************************************************************/
164:
165:
166: class sqlite3_statement
167: {
168: /**********************************************
169: * Internal variables
170: *********************************************/
171:
172: /**
173: * Database connection object
174: *
175: * @access private
176: * @var mixed
177: */
178: private $obj_connection;
179:
180: /**
181: * Query string
182: *
183: * @access private
184: * @var str
185: */
186: private $obj_query;
187:
188: /**********************************************
189: * Class methods
190: *********************************************/
191:
192: /**
193: * Constructor
194: *
195: * @access public
196: * @param str $query
197: * @param mixed $connection
198: */
199: function __construct( $query, $connection )
200: {
201: $this->obj_connection = $connection->obj_connection;
202: $this->obj_query = $query;
203: }
204:
205: /**
206: * Executes general query and returns resultset object
207: *
208: * @access public
209: * @return mixed
210: */
211: public function obj_query_execute()
212: {
213: $query_stmt = $this->obj_connection->query( $this->obj_query );
214:
215: return ( $query_stmt || is_object( $query_stmt ) ) ? new sqlite3_resultset( $query_stmt, $this->obj_connection ) : false;
216: }
217: }
218:
219:
220: /*************************************************************************************************************
221: * End database statement class
222: ************************************************************************************************************/
223:
224:
225: /*************************************************************************************************************
226: * Begin database prepared statement class
227: ************************************************************************************************************/
228:
229:
230: class sqlite3_prepare
231: {
232: /**********************************************
233: * Internal variables
234: *********************************************/
235:
236: /**
237: * Set obj_bind parameter counter
238: *
239: * @access private
240: * @var int
241: */
242: private $obj_parameter_cnt = 0;
243:
244: /**
245: * Database connection object
246: *
247: * @access private
248: * @var mixed
249: */
250: private $obj_connection;
251:
252: /**
253: * Prepared statement instance
254: *
255: * @access public
256: * @var bool
257: */
258: private $obj_prepare_instance;
259:
260: /**********************************************
261: * Class methods
262: *********************************************/
263:
264: /**
265: * Constructor
266: *
267: * @access public
268: * @param str $query
269: * @param mixed $connection
270: */
271: function __construct( $query, $connection )
272: {
273: $this->obj_connection = $connection->obj_connection;
274: $this->obj_prepare_init( $query );
275: }
276:
277: /**
278: * Set parameters for prepared statement
279: *
280: * @access public
281: * @param mixed $param
282: * @return bool
283: */
284: public function obj_bind( $param )
285: {
286: $this->obj_parameter_cnt++;
287:
288: return $this->obj_prepare_instance->bindValue( $this->obj_parameter_cnt, $param );
289: }
290:
291: /**
292: * Destroys prepared statement object
293: *
294: * @access public
295: * @return bool
296: */
297: public function obj_close_statement()
298: {
299: return ( $this->obj_prepare_instance->close() ) ? true : false;
300: }
301:
302: /**
303: * Executes prepared statement and returns resultset object
304: *
305: * @access public
306: * @return mixed
307: */
308: public function obj_execute()
309: {
310: $query_stmt = $this->obj_prepare_instance->execute();
311:
312: return ( is_object( $query_stmt ) ) ? new sqlite3_resultset( $query_stmt, $this->obj_connection ) : false;
313: }
314:
315: /**
316: * Resets prepared statement object and binding parameters
317: *
318: * @access public
319: * @return bool
320: */
321: public function obj_free_statement()
322: {
323: $this->obj_parameter_cnt = 0;
324: $this->obj_prepare_instance->clear();
325:
326: return $this->obj_prepare_instance->reset();
327: }
328:
329: /**
330: * Returns prepared statement object
331: *
332: * @access private
333: * @param str $query
334: * @return mixed
335: */
336: private function obj_prepare_init( $query )
337: {
338: $prepare_instance = $this->obj_connection->prepare( $query );
339:
340: return ( is_object( $prepare_instance ) ) ? $this->obj_prepare_instance = $prepare_instance : false;
341: }
342: }
343:
344:
345: /*************************************************************************************************************
346: * End database prepared statement class
347: ************************************************************************************************************/
348:
349:
350: /*************************************************************************************************************
351: * Begin database resultset class
352: ************************************************************************************************************/
353:
354:
355: class sqlite3_resultset
356: {
357: /**********************************************
358: * Internal variables
359: *********************************************/
360:
361: /**
362: * Database connection object
363: *
364: * @access private
365: * @var mixed
366: */
367: private $obj_connection;
368:
369: /**
370: * Query record
371: *
372: * @access private
373: * @var array
374: */
375: private $obj_record = array();
376:
377: /**
378: * Query resultset object
379: *
380: * @access private
381: * @var mixed
382: */
383: private $obj_result;
384:
385: /**********************************************
386: * Class methods
387: *********************************************/
388:
389: /**
390: * Constructor
391: *
392: * @access public
393: * @param mixed $result
394: * @param mixed $connection
395: */
396: public function __construct( $result, $connection )
397: {
398: $this->obj_connection = $connection;
399: $this->obj_result = $result;
400: }
401:
402: /**
403: * Return number of affected rows from insert/delete/update query
404: * Returns -1 if undetermined or failure
405: *
406: * @access public
407: * @return int
408: */
409: public function obj_affected_rows()
410: {
411: $affected_rows = $this->obj_connection->changes();
412:
413: return ( $affected_rows >= 0 ) ? $affected_rows : -1;
414: }
415:
416: /**
417: * Returns resultset object as associative array
418: *
419: * @access public
420: * @return mixed
421: */
422: public function obj_fetch_assoc()
423: {
424: $result = $this->obj_result->fetchArray( SQLITE3_ASSOC );
425:
426: return ( is_array( $result ) && $result !== false ) ? $this->obj_record = $result : null;
427: }
428:
429: /**
430: * Returns resultset object as numeric array
431: *
432: * @access public
433: * @return mixed
434: */
435: public function obj_fetch_num()
436: {
437: $result = $this->obj_result->fetchArray( SQLITE3_NUM );
438:
439: return ( is_array( $result ) && $result !== false ) ? $this->obj_record = $result : null;
440: }
441:
442: /**
443: * Return resultset object as object
444: *
445: * @access public
446: * @return mixed
447: */
448: public function obj_fetch_object()
449: {
450: $result = $this->obj_result->fetchArray( SQLITE3_ASSOC );
451:
452: return ( is_array( $result ) && $result !== false ) ? $this->obj_record = ( object )$result : null;
453: }
454:
455: /**
456: * Returns resultset record
457: *
458: * @access public
459: * @param mixed $field
460: * @return mixed
461: */
462: public function obj_field( $field )
463: {
464: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
465: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
466: }
467:
468: /**
469: * Frees resultset memory and destroys resultset object
470: *
471: * @access public
472: * @return bool
473: */
474: public function obj_free_result()
475: {
476: $this->obj_record = array();
477:
478: return ( $this->obj_result->finalize() ) ? true : false;
479: }
480:
481: /**
482: * Returns number of fields from query
483: * Returns -1 if undetermined or failure
484: *
485: * @access public
486: * @return int
487: */
488: public function obj_num_fields()
489: {
490: $num_cols = $this->obj_result->numColumns();
491:
492: return ( $num_cols >= 0 ) ? $num_cols : -1;
493: }
494:
495: /**
496: * Return number of rows from query
497: * Returns -1 if undetermined or failure
498: *
499: * @access public
500: * @return int
501: */
502: public function obj_num_rows()
503: {
504: //Very inefficent - will timeout on larger recordsets
505: //Use the obj_row_count() method
506: $num_rows = 0;
507:
508: while ( $this->obj_result->fetchArray( SQLITE3_NUM ) )
509: $num_rows++;
510:
511: return ( $num_rows >= 0 ) ? $num_rows : -1;
512: }
513: }
514:
515:
516: /*************************************************************************************************************
517: * End database resultset class
518: ************************************************************************************************************/
519:
520:
521: /*************************************************************************************************************
522: * Begin database transaction class
523: ************************************************************************************************************/
524:
525:
526: class sqlite3_transaction
527: {
528: /**********************************************
529: * Internal variables
530: *********************************************/
531:
532: /**
533: * Database connection instance
534: *
535: * @access private
536: * @var mixed
537: */
538: private $obj_connection;
539:
540: /**********************************************
541: * Class methods
542: *********************************************/
543:
544: /**
545: * Constructor
546: *
547: * @access public
548: * @param mixed $connection
549: */
550: public function __construct( $connection )
551: {
552: $this->obj_connection = $connection;
553:
554: //turn off autocommit
555: $this->obj_connection->exec( 'BEGIN' );
556: }
557:
558: /**
559: * Commits transaction for current transaction instance
560: *
561: * @access public
562: * @return bool
563: */
564: public function obj_commit()
565: {
566: return $this->obj_connection->exec( 'COMMIT' );
567: }
568:
569: /**
570: * Rollbacks transaction for current transaction instance
571: *
572: * @access public
573: * @param str $savepoint
574: * @return bool
575: */
576: public function obj_rollback( $savepoint=false )
577: {
578: $rollback = ( !$savepoint ) ? 'ROLLBACK' : "ROLLBACK TO $savepoint";
579:
580: return $this->obj_connection->exec( $rollback );
581: }
582:
583: /**
584: * Creates transaction savepoint for current transaction instance
585: *
586: * @access public
587: * @param str $savepoint
588: * @return bool
589: */
590: public function obj_savepoint( $savepoint )
591: {
592: return $this->obj_connection->exec( "SAVEPOINT $savepoint" );
593: }
594: }
595:
596:
597: /*************************************************************************************************************
598: * End database transaction class
599: ************************************************************************************************************/
600:
601:
602: ?>