includes/database/QSqLite3PdoDatabase.class.php
<?php
/**
* PDO_SqLite3 database driver
* @package DatabaseAdapters
* @author Christophe Damour [sigeal at sigeal dot com dot fr]
* Adapted from PDO_PGSQL database driver by Marcos S�nchez [marcosdsanchez at thinkclear dot com dot ar]
*/
class QSqLite3PdoDatabase extends QPdoDatabase {
const Adapter = 'SqLite3 PDO Database Adapter';
const PDO_SQLITE3_DSN_IDENTIFIER = 'sqlite';
protected $strEscapeIdentifierBegin = '';
protected $strEscapeIdentifierEnd = '';
protected $strEscapeIdentifierBeginInternal = '\'';
protected $strEscapeIdentifierEndInternal = '\'';
public function InsertOrUpdate($strTable, $mixColumnsAndValuesArray, $strPKNames = null) {
$strEscapedArray = $this->EscapeIdentifiersAndValues($mixColumnsAndValuesArray);
$strSql = sprintf('INSERT OR REPLACE INTO %s%s%s (%s) VALUES (%s)',
$this->EscapeIdentifierBegin, $strTable, $this->EscapeIdentifierEnd,
implode(', ', array_keys($strEscapedArray)),
implode(', ', array_values($strEscapedArray))
);
$this->ExecuteNonQuery($strSql);
}
public function Connect() {
// Lookup Adapter-Specific Connection Properties
$strDsn = sprintf('%s:%s', QSqLite3PdoDatabase::PDO_SQLITE3_DSN_IDENTIFIER, $this->Database);
// Connect to the Database Server
try {
$this->objPdo = new PDO($strDsn);
} catch (PDOException $expPgSql) {
throw new QSqLite3PdoDatabaseException(sprintf('Unable to connect to Database: %s',$expPgSql->getMessage()), -1, null);
}
// Update Connected Flag
$this->blnConnectedFlag = true;
}
public function Close() {
parent::Close();
// Update Connected Flag
$this->blnConnectedFlag = false;
}
public function SqlVariable($mixData, $blnIncludeEquality = false, $blnReverseEquality = false) {
// Are we SqlVariabling a BOOLEAN value?
if (is_bool($mixData)) {
// Yes
if ($blnIncludeEquality) {
// We must include the inequality
if ($blnReverseEquality) {
// Do a "Reverse Equality"
// Check against NULL, True then False
if (is_null($mixData))
return 'IS NOT NULL';
else if ($mixData)
return "= '0'";
else
return "!= '0'";
} else {
// Check against NULL, True then False
if (is_null($mixData))
return 'IS NULL';
else if ($mixData)
return "!= '0'";
else
return "= '0'";
}
} else {
// Check against NULL, True then False
if (is_null($mixData))
return 'NULL';
else if ($mixData)
return "'1'";
else
return "'0'";
}
}
// Check for Equality Inclusion
if ($blnIncludeEquality) {
if ($blnReverseEquality) {
if (is_null($mixData))
$strToReturn = 'IS NOT ';
else
$strToReturn = '!= ';
} else {
if (is_null($mixData))
$strToReturn = 'IS ';
else
$strToReturn = '= ';
}
} else
$strToReturn = '';
// Check for NULL Value
if (is_null($mixData))
return $strToReturn . 'NULL';
// Check for NUMERIC Value
if (is_integer($mixData) || is_float($mixData))
return $strToReturn . sprintf('%s', $mixData);
// Check for DATE Value
if ($mixData instanceof QDateTime) {
if ($mixData->IsTimeNull())
return $strToReturn . sprintf("'%s'", $mixData->__toString('YYYY-MM-DD'));
else
return $strToReturn . sprintf("'%s'", $mixData->__toString(QDateTime::FormatIso));
}
// Assume it's some kind of string value
return $strToReturn . sprintf("'%s'", addslashes($mixData));
}
public function SqlLimitVariablePrefix($strLimitInfo) {
// SqLite3 uses Limit by Suffixes (via a LIMIT clause)
// Prefix is not used, therefore, return null
return null;
}
public function SqlLimitVariableSuffix($strLimitInfo) {
// Setup limit suffix (if applicable) via a LIMIT clause
if (strlen($strLimitInfo)) {
if (strpos($strLimitInfo, ';') !== false)
throw new Exception('Invalid Semicolon in LIMIT Info');
if (strpos($strLimitInfo, '`') !== false)
throw new Exception('Invalid Backtick in LIMIT Info');
// First figure out if we HAVE an offset
$strArray = explode(',', $strLimitInfo);
if (count($strArray) == 2) {
// Yep -- there's an offset
return sprintf('LIMIT %s OFFSET %s', $strArray[1], $strArray[0]);
} else if (count($strArray) == 1) {
return sprintf('LIMIT %s', $strArray[0]);
} else {
throw new QSqLite3DatabaseException('Invalid Limit Info: ' . $strLimitInfo, 0, null);
}
}
return null;
}
public function SqlSortByVariable($strSortByInfo) {
// Setup sorting information (if applicable) via a ORDER BY clause
if (strlen($strSortByInfo)) {
if (strpos($strSortByInfo, ';') !== false)
throw new Exception('Invalid Semicolon in ORDER BY Info');
if (strpos($strSortByInfo, '`') !== false)
throw new Exception('Invalid Backtick in ORDER BY Info');
return 'ORDER BY ' . $strSortByInfo;
}
return null;
}
public function GetTables() {
$objResult = $this->Query('SELECT name FROM sqlite_master WHERE type = "table"');
$strToReturn = array();
while ($strRowArray = $objResult->FetchRow())
if (strpos($strRowArray[0], 'sqlite') === false)
array_push($strToReturn, $strRowArray[0]);
return $strToReturn;
}
public function GetFieldsForTable($strTableName) {
$strQuery = sprintf('PRAGMA table_info (%s%s%s)',
$this->strEscapeIdentifierBeginInternal,
$strTableName,
$this->strEscapeIdentifierEndInternal);
$objResult = $this->Query($strQuery);
$objFields = array();
while ($objRow = $objResult->GetNextRow()) {
array_push($objFields, new QSqLite3PdoDatabaseField($objRow, $this));
}
return $objFields;
}
public function InsertId($strTableName = null, $strColumnName = null) {
return $this->objPdo->lastInsertId();
}
private function ParseColumnNameArrayFromKeyDefinition($strKeyDefinition) {
$strKeyDefinition = trim($strKeyDefinition);
// Get rid of the opening "(" and the closing ")"
$intPosition = strpos($strKeyDefinition, '(');
if ($intPosition === false)
throw new Exception('Invalid Key Definition: ' . $strKeyDefinition);
$strKeyDefinition = trim(substr($strKeyDefinition, $intPosition + 1));
$intPosition = strpos($strKeyDefinition, ')');
if ($intPosition === false)
throw new Exception('Invalid Key Definition: ' . $strKeyDefinition);
$strKeyDefinition = trim(substr($strKeyDefinition, 0, $intPosition));
$strKeyDefinition = str_replace(' ', '', $strKeyDefinition);
// Create the Array
// TODO: Current method doesn't support key names with commas or parenthesis in them!
$strToReturn = explode(',', $strKeyDefinition);
// Take out trailing and leading '"' character in each name (if applicable)
for ($intIndex = 0; $intIndex < count($strToReturn); $intIndex++) {
$strColumn = $strToReturn[$intIndex];
if (substr($strColumn, 0, 1) == '"')
$strColumn = substr($strColumn, 1, strpos($strColumn, '"', 1) - 1);
$strToReturn[$intIndex] = $strColumn;
}
return $strToReturn;
}
public function GetIndexesForTable($strTableName) {
$objIndexArray = array();
$objResult = $this->Query(sprintf(
'PRAGMA index_list (%s%s%s)',
$this->strEscapeIdentifierBeginInternal, $strTableName,
$this->strEscapeIdentifierEndInternal));
while ($objIndexList = $objResult->GetNextRow()) {
$objResultIndex = $this->Query(sprintf('PRAGMA index_info (%s%s%s)', $this->strEscapeIdentifierBeginInternal, $objIndexList->GetColumn('name'), $this->strEscapeIdentifierEndInternal));
$blnUnique = ($objIndexList->GetColumn('unique') == 1) ? true : false;
$arrIndex = array();
while($objIndex= $objResultIndex->GetNextRow()) {
$arrIndex[] = $objIndex->GetColumn('name');
}
if(count($arrIndex)>0) {
$objIndex = new QDatabaseIndex($objIndexList->GetColumn('name'), false, $blnUnique, $arrIndex);
array_push($objIndexArray, $objIndex);
}
}
//Get the PK-key
$objPKList = $this->Query(sprintf('PRAGMA table_info (%s%s%s)', $this->strEscapeIdentifierBeginInternal, $strTableName, $this->strEscapeIdentifierEndInternal));
while ($objPK = $objPKList->GetNextRow()) {
if ($objPK->GetColumn('pk') == 1) {
unset($tmp);
$tmp[] = $objPK->GetColumn('name');
$objIndex = new QDatabaseIndex('(' . $strTableName . ' autoindex 1)', true, true, $tmp);
array_push($objIndexArray, $objIndex);
}
}
return $objIndexArray;
}
public function GetForeignKeysForTable($strTableName) {
$objForeignKeyArray = array();
$objForeignKeyArrayReturn = array();
$strQuery = sprintf('PRAGMA foreign_key_list (%s%s%s)',
$this->strEscapeIdentifierBeginInternal,
$strTableName,
$this->strEscapeIdentifierEndInternal);
$objForeignKeyList = $this->Query($strQuery);
//get an list of all foreignkeys
while($objForeignKeyResult = $objForeignKeyList->GetNextRow()) {
$objForeignKeyArray[$objForeignKeyResult->GetColumn('seq')][] = array(
$objForeignKeyResult->GetColumn('from') . '_' .
$objForeignKeyResult->GetColumn('table') . '_' .
$objForeignKeyResult->GetColumn('to'),
$objForeignKeyResult->GetColumn('from'),
$objForeignKeyResult->GetColumn('table'),
$objForeignKeyResult->GetColumn('to'));
}
//put the keys of the same seq together
foreach($objForeignKeyArray as $objForeignKeySeq) {
$arrFrom = array();
$arrTo = array();
foreach($objForeignKeySeq as $Key) {
$arrFrom[] = $Key[1];
$arrTo[] = $Key[3];
}
$objForeignKey = new QDatabaseForeignKey($Key[0], $arrFrom, $Key[2], $arrTo);
array_push($objForeignKeyArrayReturn, $objForeignKey);
}
return $objForeignKeyArrayReturn;
}
protected function ExecuteQuery($strQuery) {
// Perform the Query
$objResult = $this->objPdo->query($strQuery);
if ($objResult === false)
throw new QSqLite3PdoDatabaseException($this->objPdo->errorInfo(), $this->objPdo->errorCode(), $strQuery);
// Return the Result
$this->objMostRecentResult = $objResult;
$objPdoStatementDatabaseResult = new QSqLite3PdoDatabaseResult($objResult, $this);
return $objPdoStatementDatabaseResult;
}
}
/**
* QSqLite3PdoDatabaseResult: Class to handle results sent by database upon querying
*/
class QSqLite3PdoDatabaseResult extends QPdoDatabaseResult {
public function GetNextRow() {
$strColumnArray = $this->FetchArray();
if ($strColumnArray)
return new QSqLite3PdoDatabaseRow($strColumnArray);
else
return null;
}
public function FetchFields() {
$objArrayToReturn = array();
while ($objField = $this->FetchColumn()) {
array_push($objArrayToReturn, new QSqLite3PdoDatabaseField($objField, $this->objDb));
}
return $objArrayToReturn;
}
public function FetchField() {
if ($objField = $this->FetchColumn())
return new QSqLite3PdoDatabaseField($objField, $this->objDb);
}
}
/**
* QSqLite3PdoDatabaseRow
*/
class QSqLite3PdoDatabaseRow extends QDatabaseRowBase {
protected $strColumnArray;
public function __construct($strColumnArray) {
$this->strColumnArray = $strColumnArray;
}
/**
* Gets the value of a column from a result row returned by the database
*
* @param string $strColumnName Name of te column
* @param null|QDatabaseFieldType $strColumnType Data type
*
* @return mixed
*/
public function GetColumn($strColumnName, $strColumnType = null) {
if (!isset($this->strColumnArray[$strColumnName])) {
return null;
}
$strColumnValue = $this->strColumnArray[$strColumnName];
switch ($strColumnType) {
case QDatabaseFieldType::Bit:
if (!$strColumnValue) {
return false;
} else {
return ($strColumnValue) ? true : false;
}
case QDatabaseFieldType::Blob:
case QDatabaseFieldType::Char:
case QDatabaseFieldType::VarChar:
return QType::Cast($strColumnValue, QType::String);
case QDatabaseFieldType::Date:
case QDatabaseFieldType::DateTime:
case QDatabaseFieldType::Time:
return new QDateTime($strColumnValue);
case QDatabaseFieldType::Float:
return QType::Cast($strColumnValue, QType::Float);
case QDatabaseFieldType::Integer:
return QType::Cast($strColumnValue, QType::Integer);
default:
return $strColumnValue;
}
}
/**
* Tells whether a particular column exists in a returned database row
*
* @param string $strColumnName Name of te column
*
* @return bool
*/
public function ColumnExists($strColumnName) {
return array_key_exists($strColumnName, $this->strColumnArray);
}
public function GetColumnNameArray() {
return $this->strColumnArray;
}
}
/**
* QSqLite3PdoDatabaseField
*/
class QSqLite3PdoDatabaseField extends QDatabaseFieldBase {
//TODO: Tables and Unique
public function __construct($mixFieldData, $objDb = null, $strTableName=null) {
$this->strName = $mixFieldData->GetColumn('name');
// Set strOriginalName to Name if it isn't set
if (!$this->strOriginalName)
$this->strOriginalName = $this->strName;
else
$this->strOriginalName = $mixFieldData->GetColumn('orgname');
//TODO:table
$this->strTable = $strTableName;
$this->strOriginalTable = $strTableName;
$this->strDefault = $mixFieldData->GetColumn('dflt_value');
$this->intMaxLength = null;
$strLengthArray = explode('(', $mixFieldData->GetColumn('type'));
if (count($strLengthArray) > 1) {
//$mixFieldData->type = $strLengthArray[0];
$strLengthArray = explode(')', $strLengthArray[1]);
$this->intMaxLength = $strLengthArray[0];
// If the length is something like (7,2), then let's pull out just the "7"
$intCommaPosition = strpos($this->intMaxLength, ',');
if ($intCommaPosition !== false) {
$this->intMaxLength = substr($this->intMaxLength, 0, $intCommaPosition);
$this->intMaxLength++; // make room for decimal point
}
if (!is_numeric($this->intMaxLength))
throw new Exception('Not a valid Column Length: ' . $mixFieldData->GetColumn('type'));
}
$this->blnNotNull = $mixFieldData->GetColumn('notnull');
$this->blnPrimaryKey = $mixFieldData->GetColumn('pk');
//this is the way you define a auto_increment in Sqlite3
if (($this->blnPrimaryKey == true) && ($mixFieldData->GetColumn('type') == 'INTEGER'))
$this->blnIdentity = true;
else
$this->blnIdentity = false;
//check if is unique
if ($strTableName && $objDb) {
$objResultList = $objDb->Query(sprintf('PRAGMA index_list (%s%s%s)', "'", $strTableName, "'"));
$this->blnUnique = false;
while ($objIndexList = $objResultList->FetchObject()) {
if ($objIndexList->unique == 1) {
$objResult = $objDb->Query(sprintf('PRAGMA index_info (%s%s%s)', "'", $objIndexList->name, "'"));
while ($objIndex = $objResult->FetchObject()) {
if ($objIndex->name == $this->strName)
$this->blnUnique = true;
}
}
}
}
$strSqlite3FieldType = $mixFieldData->GetColumn('type');
if (($intPos = strpos($strSqlite3FieldType, '(')) > 0)
$strSqlite3FieldType = substr($strSqlite3FieldType, 0, $intPos);
$strSqlite3FieldType = strtoupper($strSqlite3FieldType);
$this->SetFieldType($strSqlite3FieldType);
}
protected function SetFieldType($strSqlite3FieldType) {
switch ($strSqlite3FieldType) {
case 'TINYINT':
case 'BOOLEAN':
if ($this->intMaxLength == 1)
$this->strType = QDatabaseFieldType::Bit;
else
$this->strType = QDatabaseFieldType::Integer;
break;
case 'INTEGER':
case 'INT':
case 'BIGINT':
case 'SMALLINT':
case 'MEDIUMINT':
$this->strType = QDatabaseFieldType::Integer;
break;
case 'FLOAT':
$this->strType = QDatabaseFieldType::Float;
break;
case 'DECIMAL':
// NOTE: PHP's best response to fixed point exact precision numbers is to use the bcmath library.
// bcmath requires string inputs. If you try to do math directly on these, PHP will convert to float,
// so for those who care, they will need to be careful. For those who do not care, then PHP will do
// the conversion anyway.
$this->strType = QDatabaseFieldType::VarChar;
break;
case 'DOUBLE':
// NOTE: PHP does not offer full support of double-precision floats.
// Value will be set as a VarChar which will guarantee that the precision will be maintained.
// However, you will not be able to support full typing control (e.g. you would
// not be able to use a QFloatTextBox -- only a regular QTextBox)
$this->strType = QDatabaseFieldType::VarChar;
break;
case 'DATE':
$this->strType = QDatabaseFieldType::Date;
break;
case 'TIME':
$this->strType = QDatabaseFieldType::Time;
break;
case 'TIMESTAMP': // Timestamps do not exist
case 'DATETIME':
$this->strType = QDatabaseFieldType::DateTime;
break;
case 'TINYBLOB':
case 'MEDIUMBLOB':
case 'LONGBLOB':
case 'BLOB':
$this->strType = QDatabaseFieldType::Blob;
break;
case 'VARCHAR':
case 'TEXT':
case 'MEMO':
case 'LONGTEXT':
case 'MEDIUMTEXT':
$this->strType = QDatabaseFieldType::VarChar;
break;
case 'CHAR':
$this->strType = QDatabaseFieldType::Char;
break;
case 'YEAR':
$this->strType = QDatabaseFieldType::Integer;
break;
default:
throw new Exception('Unable to determine Sqlite3 Database Field Type: ' . $strSqlite3FieldType);
break;
}
}
}
/**
* QSqLite3PdoDatabaseException
*/
class QSqLite3PdoDatabaseException extends QPdoDatabaseException {
}