1: <?php
  2: /**
  3:  * PDO_SqLite3 database driver
  4:  * @package DatabaseAdapters
  5:  * @author Christophe Damour [sigeal at sigeal dot com dot fr]
  6:  * Adapted from PDO_PGSQL database driver by Marcos S�nchez [marcosdsanchez at thinkclear dot com dot ar]
  7:  */
  8: 
  9: class QSqLite3PdoDatabase extends QPdoDatabase {
 10:     const Adapter = 'SqLite3 PDO Database Adapter';
 11:     const PDO_SQLITE3_DSN_IDENTIFIER = 'sqlite';
 12: 
 13:     protected $strEscapeIdentifierBegin = '';
 14:     protected $strEscapeIdentifierEnd = '';
 15: 
 16:     protected $strEscapeIdentifierBeginInternal = '\'';
 17:     protected $strEscapeIdentifierEndInternal = '\'';   
 18: 
 19:         public function InsertOrUpdate($strTable, $mixColumnsAndValuesArray, $strPKNames = null) {
 20:             $strEscapedArray = $this->EscapeIdentifiersAndValues($mixColumnsAndValuesArray);
 21:             $strSql = sprintf('INSERT OR REPLACE INTO %s%s%s (%s) VALUES (%s)',
 22:                 $this->EscapeIdentifierBegin, $strTable, $this->EscapeIdentifierEnd,
 23:                 implode(', ', array_keys($strEscapedArray)),
 24:                 implode(', ', array_values($strEscapedArray))
 25:             );
 26:             $this->ExecuteNonQuery($strSql);
 27:         }
 28: 
 29:         public function Connect() {
 30:                 // Lookup Adapter-Specific Connection Properties
 31:                 $strDsn = sprintf('%s:%s', QSqLite3PdoDatabase::PDO_SQLITE3_DSN_IDENTIFIER, $this->Database);
 32:                 // Connect to the Database Server
 33:                 try {
 34:                         $this->objPdo = new PDO($strDsn);
 35:                 } catch (PDOException $expPgSql) {
 36:                         throw new QSqLite3PdoDatabaseException(sprintf('Unable to connect to Database: %s',$expPgSql->getMessage()), -1, null);
 37:                 }
 38:                 // Update Connected Flag
 39:                 $this->blnConnectedFlag = true;
 40:         }
 41: 
 42:         public function Close() {
 43:             parent::Close();
 44: 
 45:             // Update Connected Flag
 46:             $this->blnConnectedFlag = false;
 47:         }
 48: 
 49:         public function SqlVariable($mixData, $blnIncludeEquality = false, $blnReverseEquality = false) {
 50:                 // Are we SqlVariabling a BOOLEAN value?
 51:                 if (is_bool($mixData)) {
 52:                         // Yes
 53:                         if ($blnIncludeEquality) {
 54:                                 // We must include the inequality
 55: 
 56:                                 if ($blnReverseEquality) {
 57:                                         // Do a "Reverse Equality"
 58: 
 59:                                         // Check against NULL, True then False
 60:                                         if (is_null($mixData))
 61:                                                 return 'IS NOT NULL';
 62:                                         else if ($mixData)
 63:                                                 return "= '0'";
 64:                                         else
 65:                                                 return "!= '0'";
 66:                                 } else {
 67:                                         // Check against NULL, True then False
 68:                                         if (is_null($mixData))
 69:                                                 return 'IS NULL';
 70:                                         else if ($mixData)
 71:                                                 return "!= '0'";
 72:                                         else
 73:                                                 return "= '0'";
 74:                                 }
 75:                         } else {
 76:                                 // Check against NULL, True then False
 77:                                 if (is_null($mixData))
 78:                                         return 'NULL';
 79:                                 else if ($mixData)
 80:                                         return "'1'";
 81:                                 else
 82:                                         return "'0'";
 83:                         }
 84:                 }
 85: 
 86:                 // Check for Equality Inclusion
 87:                 if ($blnIncludeEquality) {
 88:                         if ($blnReverseEquality) {
 89:                                 if (is_null($mixData))
 90:                                         $strToReturn = 'IS NOT ';
 91:                                 else
 92:                                         $strToReturn = '!= ';
 93:                         } else {
 94:                                 if (is_null($mixData))
 95:                                         $strToReturn = 'IS ';
 96:                                 else
 97:                                         $strToReturn = '= ';
 98:                         }
 99:                 } else
100:                         $strToReturn = '';
101: 
102:                 // Check for NULL Value
103:                 if (is_null($mixData))
104:                         return $strToReturn . 'NULL';
105: 
106:                 // Check for NUMERIC Value
107:                 if (is_integer($mixData) || is_float($mixData))
108:                         return $strToReturn . sprintf('%s', $mixData);
109: 
110:                 // Check for DATE Value
111:                 if ($mixData instanceof QDateTime) {
112:                         if ($mixData->IsTimeNull())
113:                                 return $strToReturn . sprintf("'%s'", $mixData->__toString('YYYY-MM-DD'));
114:                         else
115:                                 return $strToReturn . sprintf("'%s'", $mixData->__toString(QDateTime::FormatIso));
116:                 }
117: 
118:                 // Assume it's some kind of string value
119:                 return $strToReturn . sprintf("'%s'", addslashes($mixData));
120:         }
121: 
122:         public function SqlLimitVariablePrefix($strLimitInfo) {
123:                 // SqLite3 uses Limit by Suffixes (via a LIMIT clause)
124:                 // Prefix is not used, therefore, return null
125:                 return null;
126:         }
127: 
128:         public function SqlLimitVariableSuffix($strLimitInfo) {
129:                 // Setup limit suffix (if applicable) via a LIMIT clause
130:                 if (strlen($strLimitInfo)) {
131:                         if (strpos($strLimitInfo, ';') !== false)
132:                                 throw new Exception('Invalid Semicolon in LIMIT Info');
133:                         if (strpos($strLimitInfo, '`') !== false)
134:                                 throw new Exception('Invalid Backtick in LIMIT Info');
135: 
136:                         // First figure out if we HAVE an offset
137:                         $strArray = explode(',', $strLimitInfo);
138: 
139:                         if (count($strArray) == 2) {
140:                                 // Yep -- there's an offset
141:                                 return sprintf('LIMIT %s OFFSET %s', $strArray[1], $strArray[0]);
142:                         } else if (count($strArray) == 1) {
143:                                 return sprintf('LIMIT %s', $strArray[0]);
144:                         } else {
145:                                 throw new QSqLite3DatabaseException('Invalid Limit Info: ' . $strLimitInfo, 0, null);
146:                         }
147:                 }
148:                 return null;
149:         }
150: 
151:         public function SqlSortByVariable($strSortByInfo) {
152:                 // Setup sorting information (if applicable) via a ORDER BY clause
153:                 if (strlen($strSortByInfo)) {
154:                         if (strpos($strSortByInfo, ';') !== false)
155:                                 throw new Exception('Invalid Semicolon in ORDER BY Info');
156:                         if (strpos($strSortByInfo, '`') !== false)
157:                                 throw new Exception('Invalid Backtick in ORDER BY Info');
158: 
159:                         return 'ORDER BY ' . $strSortByInfo;
160:                 }
161:                 return null;
162:         }
163: 
164:         public function GetTables() {
165:                 $objResult = $this->Query('SELECT name FROM sqlite_master WHERE type = "table"');
166:                 $strToReturn = array();
167:                 while ($strRowArray = $objResult->FetchRow())
168:             if (strpos($strRowArray[0], 'sqlite') === false) 
169:                 array_push($strToReturn, $strRowArray[0]);
170:                 return $strToReturn;
171:         }
172: 
173:         public function GetFieldsForTable($strTableName) {
174:         $strQuery = sprintf('PRAGMA table_info (%s%s%s)',
175:             $this->strEscapeIdentifierBeginInternal,
176:             $strTableName,
177:             $this->strEscapeIdentifierEndInternal);
178:         
179:         $objResult = $this->Query($strQuery);
180: 
181:                 $objFields = array();
182: 
183:                 while ($objRow = $objResult->GetNextRow()) {
184:                         array_push($objFields, new QSqLite3PdoDatabaseField($objRow, $this));
185:                 }
186:                 return $objFields;
187:         }
188: 
189:         public function InsertId($strTableName = null, $strColumnName = null) {
190:         return $this->objPdo->lastInsertId();
191:         }
192: 
193: 
194:         private function ParseColumnNameArrayFromKeyDefinition($strKeyDefinition) {
195:                 $strKeyDefinition = trim($strKeyDefinition);
196: 
197:                 // Get rid of the opening "(" and the closing ")"
198:                 $intPosition = strpos($strKeyDefinition, '(');
199:                 if ($intPosition === false)
200:                         throw new Exception('Invalid Key Definition: ' . $strKeyDefinition);
201:                 $strKeyDefinition = trim(substr($strKeyDefinition, $intPosition + 1));
202: 
203:                 $intPosition = strpos($strKeyDefinition, ')');
204:                 if ($intPosition === false)
205:                         throw new Exception('Invalid Key Definition: ' . $strKeyDefinition);
206:                 $strKeyDefinition = trim(substr($strKeyDefinition, 0, $intPosition));
207:                 $strKeyDefinition = str_replace(' ', '', $strKeyDefinition);
208: 
209:                 // Create the Array
210:                 // TODO: Current method doesn't support key names with commas or parenthesis in them!
211:                 $strToReturn = explode(',', $strKeyDefinition);
212: 
213:                 // Take out trailing and leading '"' character in each name (if applicable)
214:                 for ($intIndex = 0; $intIndex < count($strToReturn); $intIndex++) {
215:                         $strColumn = $strToReturn[$intIndex];
216: 
217:                         if (substr($strColumn, 0, 1) == '"')
218:                                 $strColumn = substr($strColumn, 1, strpos($strColumn, '"', 1) - 1);
219: 
220:                         $strToReturn[$intIndex] = $strColumn;
221:                 }
222:                 return $strToReturn;
223:         }
224: 
225:         public function GetIndexesForTable($strTableName) {
226:                 $objIndexArray = array();
227: 
228:         $objResult = $this->Query(sprintf(
229:             'PRAGMA index_list (%s%s%s)',
230:             $this->strEscapeIdentifierBeginInternal, $strTableName,
231:             $this->strEscapeIdentifierEndInternal));
232: 
233:         while ($objIndexList = $objResult->GetNextRow()) {
234:             $objResultIndex = $this->Query(sprintf('PRAGMA index_info (%s%s%s)', $this->strEscapeIdentifierBeginInternal, $objIndexList->GetColumn('name'), $this->strEscapeIdentifierEndInternal));
235:             $blnUnique = ($objIndexList->GetColumn('unique') == 1) ? true : false;
236:             
237:             $arrIndex = array();
238:             while($objIndex= $objResultIndex->GetNextRow()) {
239:                 $arrIndex[] = $objIndex->GetColumn('name');
240:             }
241:             
242:             if(count($arrIndex)>0) {
243:                 $objIndex = new QDatabaseIndex($objIndexList->GetColumn('name'), false, $blnUnique, $arrIndex); 
244:                 array_push($objIndexArray, $objIndex);  
245:             }           
246:         }
247: 
248:         //Get the PK-key
249:         $objPKList = $this->Query(sprintf('PRAGMA table_info (%s%s%s)', $this->strEscapeIdentifierBeginInternal, $strTableName, $this->strEscapeIdentifierEndInternal));
250:         while ($objPK = $objPKList->GetNextRow()) {
251:             if ($objPK->GetColumn('pk') == 1) {
252:                 unset($tmp);
253:                 $tmp[] = $objPK->GetColumn('name');
254:                 $objIndex = new QDatabaseIndex('(' . $strTableName . ' autoindex 1)', true, true, $tmp);    
255:                 array_push($objIndexArray, $objIndex);                      
256:             }
257:         }           
258:                 return $objIndexArray;
259:         }
260: 
261:         public function GetForeignKeysForTable($strTableName) {
262:         $objForeignKeyArray = array();
263:         $objForeignKeyArrayReturn = array();
264:         
265:         $strQuery = sprintf('PRAGMA foreign_key_list (%s%s%s)',
266:             $this->strEscapeIdentifierBeginInternal,
267:             $strTableName,
268:             $this->strEscapeIdentifierEndInternal);
269:         $objForeignKeyList = $this->Query($strQuery);
270:         //get an list of all foreignkeys
271:         while($objForeignKeyResult = $objForeignKeyList->GetNextRow()) {
272:             $objForeignKeyArray[$objForeignKeyResult->GetColumn('seq')][] = array(
273:                 $objForeignKeyResult->GetColumn('from') . '_' .
274:                 $objForeignKeyResult->GetColumn('table') . '_' .
275:                 $objForeignKeyResult->GetColumn('to'),
276:                 $objForeignKeyResult->GetColumn('from'),
277:                 $objForeignKeyResult->GetColumn('table'),
278:                 $objForeignKeyResult->GetColumn('to'));
279:         }
280:         
281:         //put the keys of the same seq together
282:         foreach($objForeignKeyArray as $objForeignKeySeq) {
283:             $arrFrom = array();
284:             $arrTo = array();
285:             foreach($objForeignKeySeq as $Key) {
286:                 $arrFrom[] = $Key[1];
287:                 $arrTo[] = $Key[3];
288:             }
289:             $objForeignKey = new QDatabaseForeignKey($Key[0], $arrFrom, $Key[2], $arrTo);
290:             array_push($objForeignKeyArrayReturn, $objForeignKey);
291:         }
292:                 return $objForeignKeyArrayReturn;
293:         }
294: 
295:         protected function ExecuteQuery($strQuery) {
296:                 // Perform the Query
297:                 $objResult = $this->objPdo->query($strQuery);
298:                 if ($objResult === false)
299:                     throw new QSqLite3PdoDatabaseException($this->objPdo->errorInfo(), $this->objPdo->errorCode(), $strQuery);
300: 
301:                 // Return the Result
302:                 $this->objMostRecentResult = $objResult;
303:                 $objPdoStatementDatabaseResult = new QSqLite3PdoDatabaseResult($objResult, $this);
304:                 return $objPdoStatementDatabaseResult;
305:         }
306: }
307: 
308: /**
309:  * QSqLite3PdoDatabaseResult: Class to handle results sent by database upon querying
310:  */
311: class QSqLite3PdoDatabaseResult extends QPdoDatabaseResult {
312: 
313:         public function GetNextRow() {
314:                 $strColumnArray = $this->FetchArray();
315: 
316:                 if ($strColumnArray)
317:                         return new QSqLite3PdoDatabaseRow($strColumnArray);
318:                 else
319:                         return null;
320:         }
321: 
322:         public function FetchFields() {
323:                 $objArrayToReturn = array();
324:                 while ($objField = $this->FetchColumn()) {
325:                         array_push($objArrayToReturn, new QSqLite3PdoDatabaseField($objField, $this->objDb));
326:                 }
327:                 return $objArrayToReturn;
328:         }
329: 
330:         public function FetchField() {
331:                 if ($objField = $this->FetchColumn())
332:                         return new QSqLite3PdoDatabaseField($objField, $this->objDb);
333:         }
334: }
335: 
336: /**
337:  * QSqLite3PdoDatabaseRow
338:  */
339: class QSqLite3PdoDatabaseRow extends QDatabaseRowBase {
340:     protected $strColumnArray;
341: 
342:     public function __construct($strColumnArray) {
343:         $this->strColumnArray = $strColumnArray;
344:     }
345: 
346:     /**
347:      * Gets the value of a column from a result row returned by the database
348:      *
349:      * @param string                  $strColumnName Name of te column
350:      * @param null|QDatabaseFieldType $strColumnType Data type
351:      *
352:      * @return mixed
353:      */
354:     public function GetColumn($strColumnName, $strColumnType = null) {
355:         if (!isset($this->strColumnArray[$strColumnName])) {
356:             return null;
357:         }
358:         $strColumnValue = $this->strColumnArray[$strColumnName];
359: 
360:         switch ($strColumnType) {
361:             case QDatabaseFieldType::Bit:
362:                 if (!$strColumnValue) {
363:                     return false;
364:                 } else {
365:                     return ($strColumnValue) ? true : false;
366:                 }
367: 
368:             case QDatabaseFieldType::Blob:
369:             case QDatabaseFieldType::Char:
370:             case QDatabaseFieldType::VarChar:
371:                 return QType::Cast($strColumnValue, QType::String);
372: 
373:             case QDatabaseFieldType::Date:
374:             case QDatabaseFieldType::DateTime:
375:             case QDatabaseFieldType::Time:
376:                 return new QDateTime($strColumnValue);
377: 
378:             case QDatabaseFieldType::Float:
379:                 return QType::Cast($strColumnValue, QType::Float);
380: 
381:             case QDatabaseFieldType::Integer:
382:                 return QType::Cast($strColumnValue, QType::Integer);
383: 
384:             default:
385:                 return $strColumnValue;
386:         }
387:     }
388: 
389:     /**
390:      * Tells whether a particular column exists in a returned database row
391:      *
392:      * @param string $strColumnName Name of te column
393:      *
394:      * @return bool
395:      */
396:     public function ColumnExists($strColumnName) {
397:         return array_key_exists($strColumnName, $this->strColumnArray);
398:     }
399:     
400:     public function GetColumnNameArray() {
401:             return $this->strColumnArray;
402:     }
403: }
404: 
405: /**
406:  * QSqLite3PdoDatabaseField
407:  */
408: class QSqLite3PdoDatabaseField extends QDatabaseFieldBase {
409:     //TODO: Tables and Unique
410:     public function __construct($mixFieldData, $objDb = null, $strTableName=null) {
411:         $this->strName = $mixFieldData->GetColumn('name');
412:         // Set strOriginalName to Name if it isn't set
413:         if (!$this->strOriginalName)
414:             $this->strOriginalName = $this->strName;
415:         else
416:             $this->strOriginalName = $mixFieldData->GetColumn('orgname');
417:         //TODO:table
418:         $this->strTable = $strTableName;
419:         $this->strOriginalTable = $strTableName;
420:         $this->strDefault = $mixFieldData->GetColumn('dflt_value');
421:         $this->intMaxLength = null;
422: 
423:         
424:         $strLengthArray = explode('(', $mixFieldData->GetColumn('type'));
425:         if (count($strLengthArray) > 1) {
426:             //$mixFieldData->type = $strLengthArray[0];
427:             $strLengthArray = explode(')', $strLengthArray[1]);
428:             $this->intMaxLength = $strLengthArray[0];
429: 
430:             // If the length is something like (7,2), then let's pull out just the "7"
431:             $intCommaPosition = strpos($this->intMaxLength, ',');
432:             if ($intCommaPosition !== false) {
433:                 $this->intMaxLength = substr($this->intMaxLength, 0, $intCommaPosition);
434:                 $this->intMaxLength++; // make room for decimal point
435:             }
436: 
437:             if (!is_numeric($this->intMaxLength))
438:                 throw new Exception('Not a valid Column Length: ' . $mixFieldData->GetColumn('type'));
439:         }
440: 
441:         $this->blnNotNull = $mixFieldData->GetColumn('notnull');
442:         $this->blnPrimaryKey = $mixFieldData->GetColumn('pk');
443:         
444:         //this is the way you define a auto_increment in Sqlite3
445:         if (($this->blnPrimaryKey == true) && ($mixFieldData->GetColumn('type') == 'INTEGER'))
446:             $this->blnIdentity = true;
447:         else
448:             $this->blnIdentity = false;
449:             
450:         //check if is unique
451:         if ($strTableName && $objDb) {
452:             $objResultList = $objDb->Query(sprintf('PRAGMA index_list (%s%s%s)', "'", $strTableName, "'"));
453:             $this->blnUnique = false;
454:             while ($objIndexList = $objResultList->FetchObject()) {
455:                 if ($objIndexList->unique == 1) {
456:                     $objResult = $objDb->Query(sprintf('PRAGMA index_info (%s%s%s)', "'", $objIndexList->name, "'"));
457:                     while ($objIndex = $objResult->FetchObject()) {
458:                         if ($objIndex->name == $this->strName)
459:                             $this->blnUnique = true;
460:                     }
461:                 }
462:             }
463:         }
464:         $strSqlite3FieldType = $mixFieldData->GetColumn('type');
465:         if (($intPos = strpos($strSqlite3FieldType, '(')) > 0)
466:             $strSqlite3FieldType = substr($strSqlite3FieldType, 0, $intPos);
467:         $strSqlite3FieldType = strtoupper($strSqlite3FieldType);
468:         $this->SetFieldType($strSqlite3FieldType);
469:     }
470: 
471:     protected function SetFieldType($strSqlite3FieldType) {
472:         switch ($strSqlite3FieldType) {
473:             case 'TINYINT':
474:             case 'BOOLEAN':
475:                 if ($this->intMaxLength == 1)
476:                     $this->strType = QDatabaseFieldType::Bit;
477:                 else
478:                     $this->strType = QDatabaseFieldType::Integer;
479:                 break;
480:             case 'INTEGER':
481:             case 'INT':
482:             case 'BIGINT':
483:             case 'SMALLINT':
484:             case 'MEDIUMINT':
485:                 $this->strType = QDatabaseFieldType::Integer;
486:                 break;
487:             
488:             case 'FLOAT':
489:                 $this->strType = QDatabaseFieldType::Float;
490:                 break;
491: 
492:             case 'DECIMAL':
493:                 // NOTE: PHP's best response to fixed point exact precision numbers is to use the bcmath library.
494:                 // bcmath requires string inputs. If you try to do math directly on these, PHP will convert to float,
495:                 // so for those who care, they will need to be careful. For those who do not care, then PHP will do
496:                 // the conversion anyway.
497:                 $this->strType = QDatabaseFieldType::VarChar;
498:                 break;
499: 
500:             case 'DOUBLE':
501:                 // NOTE: PHP does not offer full support of double-precision floats.
502:                 // Value will be set as a VarChar which will guarantee that the precision will be maintained.
503:                 //    However, you will not be able to support full typing control (e.g. you would
504:                 //    not be able to use a QFloatTextBox -- only a regular QTextBox)
505:                 $this->strType = QDatabaseFieldType::VarChar;
506:                 break;
507:             case 'DATE':
508:                 $this->strType = QDatabaseFieldType::Date;
509:                 break;
510:             case 'TIME':
511:                 $this->strType = QDatabaseFieldType::Time;
512:                 break;
513:             case 'TIMESTAMP':   // Timestamps do not exist
514:             case 'DATETIME':
515:                 $this->strType = QDatabaseFieldType::DateTime;
516:                 break;
517:             case 'TINYBLOB':
518:             case 'MEDIUMBLOB':
519:             case 'LONGBLOB':
520:             case 'BLOB':
521:                 $this->strType = QDatabaseFieldType::Blob;
522:                 break;
523:             case 'VARCHAR':
524:             case 'TEXT':
525:             case 'MEMO':
526:             case 'LONGTEXT':
527:             case 'MEDIUMTEXT':
528:                 $this->strType = QDatabaseFieldType::VarChar;
529:                 break;
530:             case 'CHAR':
531:                 $this->strType = QDatabaseFieldType::Char;
532:                 break;
533:             case 'YEAR':
534:                 $this->strType = QDatabaseFieldType::Integer;
535:                 break;
536:             default:
537:                 throw new Exception('Unable to determine Sqlite3 Database Field Type: ' . $strSqlite3FieldType);
538:                 break;
539:         }
540:     }
541: }
542: 
543: /**
544:  * QSqLite3PdoDatabaseException
545:  */
546: class QSqLite3PdoDatabaseException extends QPdoDatabaseException {
547: 
548: }