/**
* The MySQL PDO driver has issues if you try to reuse a prepared statement
* without any placeholders.
*
* @return void
*/
private function regenerateStatement()
{
$is_pdo = $this->database->getExtension() == 'pdo';
$is_mysql = $this->database->getType() == 'mysql';
if ($this->placeholders || !$is_pdo || !$is_mysql) {
return;
}
$this->statement = $this->database->getConnection()->prepare($this->sql);
}
/**
* Configures the result set
*
* @internal
*
* @param fDatabase $database The database object this result was created from
* @param string $character_set MSSQL only: the character set to transcode from since MSSQL doesn't do UTF-8
* @return fUnbufferedResult
*/
public function __construct($database, $character_set = NULL)
{
if (!$database instanceof fDatabase) {
throw new fProgrammerException('The database object provided does not appear to be a descendant of fDatabase');
}
$this->database = $database;
$this->extension = $this->database->getExtension();
$this->character_set = $character_set;
}
/**
* Seeks to the specified zero-based row for the specified SQL query
*
* @throws fNoRowsException When the query did not return any rows
*
* @param integer $row The row number to seek to (zero-based)
* @return void
*/
public function seek($row)
{
if (!$this->returned_rows) {
throw new fNoRowsException('The query did not return any rows');
}
if ($row >= $this->returned_rows || $row < 0) {
throw new fProgrammerException('The row requested does not exist');
}
$this->pointer = $row;
switch ($this->database->getExtension()) {
case 'mssql':
$success = mssql_data_seek($this->result, $row);
break;
case 'mysql':
$success = mysql_data_seek($this->result, $row);
break;
case 'mysqli':
if (is_object($this->result)) {
$success = mysqli_data_seek($this->result, $row);
} else {
$success = TRUE;
}
break;
case 'pgsql':
$success = pg_result_seek($this->result, $row);
break;
case 'sqlite':
$success = sqlite_seek($this->result, $row);
break;
case 'ibm_db2':
case 'oci8':
case 'odbc':
case 'pdo':
case 'sqlsrv':
// Do nothing since we already changed the pointer
$success = TRUE;
break;
}
if (!$success) {
throw new fSQLException('There was an error seeking to row %s', $row);
}
$this->advanceCurrentRow();
}
/**
* Translates the structure of `CREATE TABLE` statements to the database specific syntax
*
* @param string $sql The SQL to translate
* @param array &$extra_statements Any extra SQL statements that need to be added
* @return string The translated SQL
*/
private function translateCreateTableStatements($sql, &$extra_statements)
{
if (!preg_match('#^\\s*CREATE\\s+TABLE\\s+(["`\\[]?\\w+["`\\]]?)#i', $sql, $table_matches)) {
return $sql;
}
$table = $table_matches[1];
if ($this->database->getType() == 'db2') {
// Data type translation
$regex = array('#\\btext\\b#i' => 'CLOB', '#("[^"]+"|\\w+)\\s+boolean(.*?)(,|\\)|$)#im' => '\\1 CHAR(1)\\2 CHECK(\\1 IN (\'0\', \'1\'))\\3', '#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\b#i' => 'INTEGER GENERATED BY DEFAULT AS IDENTITY');
$sql = preg_replace(array_keys($regex), array_values($regex), $sql);
} elseif ($this->database->getType() == 'mssql') {
// Data type translation
$regex = array('#\\bblob\\b#i' => 'IMAGE', '#\\btimestamp\\b#i' => 'DATETIME', '#\\btime\\b#i' => 'DATETIME', '#\\bdate\\b#i' => 'DATETIME', '#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\b#i' => 'INTEGER IDENTITY(1,1)', '#\\bboolean\\b#i' => 'BIT', '#\\bvarchar\\b#i' => 'NVARCHAR', '#\\bchar\\b#i' => 'NCHAR', '#\\btext\\b#i' => 'NTEXT');
$sql = preg_replace(array_keys($regex), array_values($regex), $sql);
} elseif ($this->database->getType() == 'mysql') {
// Data type translation
$regex = array('#\\btext\\b#i' => 'MEDIUMTEXT', '#\\bblob\\b#i' => 'LONGBLOB', '#\\btimestamp\\b#i' => 'DATETIME', '#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\b#i' => 'INTEGER AUTO_INCREMENT');
$sql = preg_replace(array_keys($regex), array_values($regex), $sql);
// Make sure MySQL uses InnoDB tables, translate check constraints to enums and fix column-level foreign key definitions
preg_match_all('#(?<=,|\\()\\s*(["`]?\\w+["`]?)\\s+(?:[a-z]+)(?:\\(\\d+\\))?(?:\\s+unsigned|\\s+zerofill|\\s+character\\s+set\\s+[^ ]+|\\s+collate\\s+[^ ]+|\\s+NULL|\\s+NOT\\s+NULL|(\\s+DEFAULT\\s+(?:[^, \']*|\'(?:\'\'|[^\']+)*\'))|\\s+UNIQUE|\\s+PRIMARY\\s+KEY|(\\s+CHECK\\s*\\(\\w+\\s+IN\\s+(\\(\\s*(?:(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\\s*,\\s*)*\\s*(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\\))\\)))*(\\s+REFERENCES\\s+["`]?\\w+["`]?\\s*\\(\\s*["`]?\\w+["`]?\\s*\\)\\s*(?:\\s+(?:ON\\s+DELETE|ON\\s+UPDATE)\\s+(?:CASCADE|NO\\s+ACTION|RESTRICT|SET\\s+NULL|SET\\s+DEFAULT))*(?:\\s+(?:DEFERRABLE|NOT\\s+DEFERRABLE))?)?\\s*(,|\\s*(?=\\)))#mi', $sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
// MySQL has the enum data type, so we switch check constraints to that
if (!empty($match[3])) {
$replacement = "\n " . $match[1] . ' enum' . $match[4] . $match[2] . $match[5] . $match[6];
$sql = str_replace($match[0], $replacement, $sql);
// This allows us to do a str_replace below for converting foreign key syntax
$match[0] = $replacement;
}
// Even InnoDB table types don't allow specify foreign key constraints in the column
// definition, so we move it to its own definition on the next line
if (!empty($match[5])) {
$updated_match_0 = str_replace($match[5], ",\nFOREIGN KEY (" . $match[1] . ') ' . $match[5], $match[0]);
$sql = str_replace($match[0], $updated_match_0, $sql);
}
}
$sql = preg_replace('#\\)\\s*;?\\s*$#D', ')ENGINE=InnoDB', $sql);
} elseif ($this->database->getType() == 'oracle') {
// Data type translation
$regex = array('#\\bbigint\\b#i' => 'INTEGER', '#\\bboolean\\b#i' => 'NUMBER(1)', '#\\btext\\b#i' => 'CLOB', '#\\bvarchar\\b#i' => 'VARCHAR2', '#\\btime\\b#i' => 'TIMESTAMP');
$sql = preg_replace(array_keys($regex), array_values($regex), $sql);
// Create sequences and triggers for Oracle
if (stripos($sql, 'autoincrement') !== FALSE && preg_match('#(?<=,|\\()\\s*("?\\w+"?)\\s+(?:[a-z]+)(?:\\((?:\\d+)\\))?.*?\\bAUTOINCREMENT\\b[^,\\)]*(?:,|\\s*(?=\\)))#mi', $sql, $matches)) {
$column = $matches[1];
$table_column = substr(str_replace('"', '', $table) . '_' . str_replace('"', '', $column), 0, 26);
$sequence_name = $table_column . '_seq';
$trigger_name = $table_column . '_trg';
$sequence = 'CREATE SEQUENCE ' . $sequence_name;
$trigger = 'CREATE OR REPLACE TRIGGER ' . $trigger_name . "\n";
$trigger .= "BEFORE INSERT ON " . $table . "\n";
$trigger .= "FOR EACH ROW\n";
$trigger .= "BEGIN\n";
$trigger .= " IF :new." . $column . " IS NULL THEN\n";
$trigger .= "\tSELECT " . $sequence_name . ".nextval INTO :new." . $column . " FROM dual;\n";
$trigger .= " END IF;\n";
$trigger .= "END;";
$extra_statements[] = $sequence;
$extra_statements[] = $trigger;
$sql = preg_replace('#\\s+autoincrement\\b#i', '', $sql);
}
} elseif ($this->database->getType() == 'postgresql') {
// Data type translation
$regex = array('#\\bblob\\b#i' => 'BYTEA', '#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\b#i' => 'SERIAL');
$sql = preg_replace(array_keys($regex), array_values($regex), $sql);
} elseif ($this->database->getType() == 'sqlite') {
// Data type translation
if ($this->database->getExtension() == 'pdo') {
$sql = preg_replace('#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\s+primary\\s+key\\b#i', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql);
} else {
$sql = preg_replace('#\\binteger(?:\\(\\d+\\))?\\s+autoincrement\\s+primary\\s+key\\b#i', 'INTEGER PRIMARY KEY', $sql);
}
// Create foreign key triggers for SQLite
if (stripos($sql, 'REFERENCES') !== FALSE) {
preg_match_all('#(?:(?<=,|\\()\\s*(["`\\[]?\\w+["`\\]]?)\\s+(?:[a-z]+)(?:\\((?:\\d+)\\))?(?:(\\s+NOT\\s+NULL)|(?:\\s+DEFAULT\\s+(?:[^, \']*|\'(?:\'\'|[^\']+)*\'))|(?:\\s+UNIQUE)|(?:\\s+PRIMARY\\s+KEY(?:\\s+AUTOINCREMENT)?)|(?:\\s+CHECK\\s*\\(\\w+\\s+IN\\s+\\(\\s*(?:(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\\s*,\\s*)*\\s*(?:[^, \']+|\'(?:\'\'|[^\']+)*\')\\)\\)))*(\\s+REFERENCES\\s+(["`\\[]?\\w+["`\\]]?)\\s*\\(\\s*(["`\\[]?\\w+["`\\]]?)\\s*\\)\\s*(?:\\s+(?:ON\\s+DELETE\\s+(CASCADE|NO\\s+ACTION|RESTRICT|SET\\s+NULL)))?(?:\\s+(?:ON\\s+UPDATE\\s+(CASCADE|NO\\s+ACTION|RESTRICT|SET\\s+NULL)))?)?\\s*(?:,|\\s*(?=\\)))|(?<=,|\\()\\s*FOREIGN\\s+KEY\\s*(?:(["`\\[]?\\w+["`\\]]?)|\\((["`\\[]?\\w+["`\\]]?)\\))\\s+REFERENCES\\s+(["`\\[]?\\w+["`\\]]?)\\s*\\(\\s*(["`\\[]?\\w+["`\\]]?)\\s*\\)\\s*(?:\\s+(?:ON\\s+DELETE\\s+(CASCADE|NO\\s+ACTION|RESTRICT|SET\\s+NULL)))?(?:\\s+(?:ON\\s+UPDATE\\s+(CASCADE|NO\\s+ACTION|RESTRICT|SET\\s+NULL)))?\\s*(?:,|\\s*(?=\\))))#mi', $sql, $matches, PREG_SET_ORDER);
$not_null_columns = array();
foreach ($matches as $match) {
$fields_to_unquote = array(1, 4, 5, 9, 10, 11);
foreach ($fields_to_unquote as $field) {
if (isset($match[$field])) {
$match[$field] = str_replace(array('[', '"', '`', ']'), '', $match[$field]);
}
}
// Find all of the not null columns
if (!empty($match[2])) {
$not_null_columns[] = $match[1];
}
// If neither of these fields is matched, we don't have a foreign key
if (empty($match[3]) && empty($match[10])) {
continue;
}
// 8 and 9 will be an either/or set, so homogenize
if (empty($match[9]) && !empty($match[8])) {
$match[9] = $match[8];
}
// Handle column level foreign key inserts/updates
if ($match[1]) {
$this->createSQLiteForeignKeyTriggerValidInsertUpdate($extra_statements, $table, $match[1], $match[4], $match[5], in_array($match[1], $not_null_columns));
// Handle table level foreign key inserts/update
} elseif ($match[9]) {
$this->createSQLiteForeignKeyTriggerValidInsertUpdate($extra_statements, $table, $match[9], $match[10], $match[11], in_array($match[9], $not_null_columns));
}
// If none of these fields is matched, we don't have on delete or on update clauses
if (empty($match[6]) && empty($match[7]) && empty($match[12]) && empty($match[13])) {
continue;
}
// Handle column level foreign key delete/update clauses
if (!empty($match[3])) {
if ($match[6]) {
$this->createSQLiteForeignKeyTriggerOnDelete($extra_statements, $table, $match[1], $match[4], $match[5], $match[6]);
}
if (!empty($match[7])) {
$this->createSQLiteForeignKeyTriggerOnUpdate($extra_statements, $table, $match[1], $match[4], $match[5], $match[7]);
}
continue;
}
// Handle table level foreign key delete/update clauses
if ($match[12]) {
$this->createSQLiteForeignKeyTriggerOnDelete($extra_statements, $table, $match[9], $match[10], $match[11], $match[12]);
}
if ($match[13]) {
$this->createSQLiteForeignKeyTriggerOnUpdate($extra_statements, $table, $match[9], $match[10], $match[11], $match[13]);
}
}
}
}
return $sql;
}
/**
* Adds a number of math functions to SQLite that MSSQL, MySQL and PostgreSQL have by default
*
* @return void
*/
private function createSQLiteFunctions()
{
$function = array();
$functions[] = array('acos', 'acos', 1);
$functions[] = array('asin', 'asin', 1);
$functions[] = array('atan', 'atan', 1);
$functions[] = array('atan2', 'atan2', 2);
$functions[] = array('ceil', 'ceil', 1);
$functions[] = array('ceiling', 'ceil', 1);
$functions[] = array('cos', 'cos', 1);
$functions[] = array('cot', array('fSQLTranslation', 'sqliteCotangent'), 1);
$functions[] = array('degrees', 'rad2deg', 1);
$functions[] = array('exp', 'exp', 1);
$functions[] = array('floor', 'floor', 1);
$functions[] = array('ln', 'log', 1);
$functions[] = array('log', array('fSQLTranslation', 'sqliteLogBaseFirst'), 2);
$functions[] = array('ltrim', 'ltrim', 1);
$functions[] = array('pi', 'pi', 0);
$functions[] = array('power', 'pow', 2);
$functions[] = array('radians', 'deg2rad', 1);
$functions[] = array('rtrim', 'rtrim', 1);
$functions[] = array('sign', array('fSQLTranslation', 'sqliteSign'), 1);
$functions[] = array('sqrt', 'sqrt', 1);
$functions[] = array('sin', 'sin', 1);
$functions[] = array('tan', 'tan', 1);
$functions[] = array('trim', 'trim', 1);
if ($this->database->getExtension() == 'sqlite') {
$functions[] = array('current_date', array('fSQLTranslation', 'sqliteDate'), 0);
$functions[] = array('current_time', array('fSQLTranslation', 'sqliteTime'), 0);
$functions[] = array('current_timestamp', array('fSQLTranslation', 'sqliteTimestamp'), 0);
// If SQLite was compiled with ISO-8859-* string handling, we override as best we can
// with custom functions that return the correct values. We can't fix LIKE and GLOB
// but they don't matter as much since the encoding only affects case transformations.
if (strtolower(sqlite_libencoding()) != 'utf-8') {
$functions[] = array('length', array('fSQLTranslation', 'sqliteLength'), 1);
$functions[] = array('substr', array('fSQLTranslation', 'sqliteSubstr'), 3);
}
}
foreach ($functions as $function) {
if ($this->database->getExtension() == 'pdo') {
$this->database->getConnection()->sqliteCreateFunction($function[0], $function[1], $function[2]);
} else {
sqlite_create_function($this->database->getConnection(), $function[0], $function[1], $function[2]);
}
}
}
/**
* Gets the next row from the result and assigns it to the current row
*
* @return void
*/
private function advanceCurrentRow()
{
switch ($this->database->getExtension()) {
case 'mssql':
// For some reason the mssql extension will return an empty row even
// when now rows were returned, so we have to explicitly check for this
if ($this->pointer == 0 && !mssql_num_rows($this->result)) {
$row = FALSE;
} else {
$row = mssql_fetch_assoc($this->result);
if (empty($row)) {
mssql_fetch_batch($this->result);
$row = mssql_fetch_assoc($this->result);
}
if (!empty($row)) {
$row = $this->fixDblibMSSQLDriver($row);
}
}
break;
case 'mysql':
$row = mysql_fetch_assoc($this->result);
break;
case 'mysqli':
$row = mysqli_fetch_assoc($this->result);
break;
case 'oci8':
$row = oci_fetch_assoc($this->result);
break;
case 'odbc':
$row = odbc_fetch_array($this->result);
break;
case 'pgsql':
$row = pg_fetch_assoc($this->result);
break;
case 'sqlite':
$row = sqlite_fetch_array($this->result, SQLITE_ASSOC);
break;
case 'sqlsrv':
$row = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC);
break;
case 'pdo':
$row = $this->result->fetch(PDO::FETCH_ASSOC);
break;
}
// Fix uppercase column names to lowercase
if ($row && $this->database->getType() == 'oracle') {
$new_row = array();
foreach ($row as $column => $value) {
$new_row[strtolower($column)] = $value;
}
$row = $new_row;
}
// This is an unfortunate fix that required for databases that don't support limit
// clauses with an offset. It prevents unrequested columns from being returned.
if ($row && ($this->database->getType() == 'mssql' || $this->database->getType() == 'oracle')) {
if ($this->untranslated_sql !== NULL && isset($row['flourish__row__num'])) {
unset($row['flourish__row__num']);
}
}
// This decodes the data coming out of MSSQL into UTF-8
if ($row && $this->database->getType() == 'mssql') {
if ($this->character_set) {
foreach ($row as $key => $value) {
if (!is_string($value) || strpos($key, '__flourish_mssqln_') === 0 || isset($row['fmssqln__' . $key]) || preg_match('#[\\x0-\\x8\\xB\\xC\\xE-\\x1F]#', $value)) {
continue;
}
$row[$key] = iconv($this->character_set, 'UTF-8', $value);
}
}
$row = $this->decodeMSSQLNationalColumns($row);
}
if ($this->unescape_map) {
foreach ($this->unescape_map as $column => $type) {
if (!isset($row[$column])) {
continue;
}
$row[$column] = $this->database->unescape($type, $row[$column]);
}
}
$this->current_row = $row;
}
