/**
* 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);
}
/**
* Creates a unique cache prefix to help prevent cache conflicts
*
* @return void
*/
private function makeCachePrefix()
{
$prefix = 'fSchema::' . $this->database->getType() . '::';
if ($this->database->getHost()) {
$prefix .= $this->database->getHost() . '::';
}
if ($this->database->getPort()) {
$prefix .= $this->database->getPort() . '::';
}
$prefix .= $this->database->getDatabase() . '::';
if ($this->database->getUsername()) {
$prefix .= $this->database->getUsername() . '::';
}
return $prefix;
}
/**
* Translates basic data types
*
* @param string $sql The SQL to translate
* @return string The translated SQL
*/
private function translateDataTypes($sql)
{
switch ($this->database->getType()) {
case 'db2':
$regex = array('#\\btext\\b#i' => 'CLOB(1 G)', '#\\bblob\\b(?!\\()#i' => 'BLOB(2 G)');
break;
case 'mssql':
$regex = array('#\\bblob\\b#i' => 'IMAGE', '#\\btimestamp\\b#i' => 'DATETIME', '#\\btime\\b#i' => 'DATETIME', '#\\bdate\\b#i' => 'DATETIME', '#\\bboolean\\b#i' => 'BIT', '#\\bvarchar\\b#i' => 'NVARCHAR', '#\\bchar\\b#i' => 'NCHAR', '#\\btext\\b#i' => 'NTEXT');
break;
case 'mysql':
$regex = array('#\\btext\\b#i' => 'LONGTEXT', '#\\bblob\\b#i' => 'LONGBLOB', '#\\btimestamp\\b#i' => 'DATETIME');
break;
case 'oracle':
$regex = array('#\\bbigint\\b#i' => 'INTEGER', '#\\bboolean\\b#i' => 'NUMBER(1)', '#\\btext\\b#i' => 'CLOB', '#\\bvarchar\\b#i' => 'VARCHAR2', '#\\btime\\b#i' => 'TIMESTAMP');
break;
case 'postgresql':
$regex = array('#\\bblob\\b#i' => 'BYTEA');
break;
case 'sqlite':
// SQLite doesn't have ALTER TABLE statements, so everything for data
// types is handled via ::translateCreateTableStatements()
$regex = array();
break;
}
return preg_replace(array_keys($regex), array_values($regex), $sql);
}
/**
* Translates `LIMIT x OFFSET x` to `ROW_NUMBER() OVER (ORDER BY)` syntax
*
* @param string $sql The SQL to translate
* @return string The translated SQL
*/
private function translateLimitOffsetToRowNumber($sql)
{
// Regex details:
// 1 - The SELECT clause
// 2 - () recursion handler
// 3 - FROM clause
// 4 - () recursion handler
// 5 - ORDER BY clause
// 6 - () recursion handler
// 7 - LIMIT number
// 8 - OFFSET number
preg_match_all('#select((?:(?:(?!\\sfrom\\s)[^()])+|\\(((?:[^()]+|\\((?2)\\))*)\\))*\\s)(from(?:(?:(?!\\slimit\\s|\\sorder by\\s)[^()])+|\\(((?:[^()]+|\\((?4)\\))*)\\))*\\s)(order by(?:(?:(?!\\slimit\\s)[^()])+|\\(((?:[^()]+|\\((?6)\\))*)\\))*\\s)?limit\\s+(\\d+)(?:\\s+offset\\s+(\\d+))?#i', $sql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
if ($this->database->getType() == 'mssql') {
// This means we have an offset clause
if (!empty($match[8])) {
if ($match[5] === '') {
$match[5] = "ORDER BY rand(1) ASC";
}
$select = $match[1] . ', ROW_NUMBER() OVER (';
$select .= $match[5];
$select .= ') AS flourish__row__num ';
$select .= $match[3];
$replacement = 'SELECT * FROM (SELECT ' . trim($match[1]) . ', ROW_NUMBER() OVER (' . $match[5] . ') AS flourish__row__num ' . $match[3] . ') AS original_query WHERE flourish__row__num > ' . $match[8] . ' AND flourish__row__num <= ' . ($match[7] + $match[8]) . ' ORDER BY flourish__row__num';
// Otherwise we just have a limit
} else {
$replacement = 'SELECT TOP ' . $match[7] . ' ' . trim($match[1] . $match[3] . $match[5]);
}
// While Oracle has the row_number() construct, the rownum pseudo-column is better
} elseif ($this->database->getType() == 'oracle') {
// This means we have an offset clause
if (!empty($match[8])) {
$replacement = 'SELECT * FROM (SELECT flourish__sq.*, rownum flourish__row__num FROM (SELECT' . $match[1] . $match[3] . $match[5] . ') flourish__sq WHERE rownum <= ' . ($match[7] + $match[8]) . ') WHERE flourish__row__num > ' . $match[8];
// Otherwise we just have a limit
} else {
$replacement = 'SELECT * FROM (SELECT' . $match[1] . $match[3] . $match[5] . ') WHERE rownum <= ' . $match[7];
}
} elseif ($this->database->getType() == 'db2') {
// This means we have an offset clause
if (!empty($match[8])) {
if ($match[5] === '') {
$match[5] = "ORDER BY rand(1) ASC";
}
$select = $match[1] . ', ROW_NUMBER() OVER (';
$select .= $match[5];
$select .= ') AS flourish__row__num ';
$select .= $match[3];
$replacement = 'SELECT * FROM (SELECT ' . trim($match[1]) . ', ROW_NUMBER() OVER (' . $match[5] . ') AS flourish__row__num ' . $match[3] . ') AS original_query WHERE flourish__row__num > ' . $match[8] . ' AND flourish__row__num <= ' . ($match[7] + $match[8]) . ' ORDER BY flourish__row__num';
// Otherwise we just have a limit
} else {
$replacement = 'SELECT ' . trim($match[1] . $match[3] . $match[5]) . ' FETCH FIRST ' . $match[7] . ' ROWS ONLY';
}
}
$sql = str_replace($match[0], $replacement, $sql);
}
return $sql;
}
/**
* Gets the next row from the result and assigns it to the current row
*
* @return void
*/
private function advanceCurrentRow()
{
$type = $this->database->getType();
switch ($this->extension) {
case 'ibm_db2':
$row = db2_fetch_assoc($this->result);
break;
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':
if (!$this->result instanceof stdClass) {
$row = mysqli_fetch_assoc($this->result);
} else {
$meta = $this->result->statement->result_metadata();
$row_references = array();
while ($field = $meta->fetch_field()) {
$row_references[] =& $fetched_row[$field->name];
}
call_user_func_array(array($this->result->statement, 'bind_result'), $row_references);
$this->result->statement->fetch();
$row = array();
foreach ($fetched_row as $key => $val) {
$row[$key] = $val;
}
unset($row_references);
$meta->free_result();
}
break;
case 'oci8':
$row = oci_fetch_assoc($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':
$resource = $this->result instanceof stdClass ? $this->result->statement : $this->result;
$row = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC);
break;
case 'pdo':
$row = $this->result->fetch(PDO::FETCH_ASSOC);
if (!empty($row) && $type == 'mssql') {
$row = $this->fixDblibMSSQLDriver($row);
}
break;
}
// Fix uppercase column names to lowercase
if ($row && ($type == 'oracle' || $type == 'db2' && $this->extension != 'ibm_db2')) {
$row = array_change_key_case($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 (isset($row['flourish__row__num'])) {
unset($row['flourish__row__num']);
}
// This decodes the data coming out of MSSQL into UTF-8
if ($row && $type == '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] = self::iconv($this->character_set, 'UTF-8', $value);
}
}
$row = $this->decodeMSSQLNationalColumns($row);
// This resets the array pointer so that
// current() will work as expected
reset($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;
}
/**
* Gets the next row from the result and assigns it to the current row
*
* @return void
*/
private function advanceCurrentRow()
{
$type = $this->database->getType();
$extension = $this->database->getExtension();
switch ($extension) {
case 'mssql':
$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':
if (is_object($this->result)) {
$row = mysqli_fetch_assoc($this->result);
} else {
$row = $this->result[$this->pointer];
}
break;
case 'pgsql':
$row = pg_fetch_assoc($this->result);
break;
case 'sqlite':
$row = sqlite_fetch_array($this->result, SQLITE_ASSOC);
break;
case 'ibm_db2':
case 'oci8':
case 'pdo':
case 'sqlsrv':
$row = $this->result[$this->pointer];
break;
}
// Fix uppercase column names to lowercase
if ($row && ($type == 'oracle' || $type == 'db2' && $extension != 'ibm_db2')) {
$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 && in_array($type, array('mssql', 'oracle', 'db2'))) {
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 && $type == 'mssql') {
if ($this->character_set) {
foreach ($row as $key => $value) {
if (!is_string($value) || strpos($key, 'fmssqln__') === 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;
}
/**
* Finds all of the table names in the SQL and creates the appropriate `FROM` and `GROUP BY` clauses with all necessary joins
*
* The SQL string should contain two placeholders, `:from_clause` and
* `:group_by_clause`. All columns should be qualified with their full table
* name. Here is an example SQL string to pass in presumming that the
* tables users and groups are in a relationship:
*
* {{{
* SELECT users.* FROM :from_clause WHERE groups.group_id = 5 :group_by_clause ORDER BY lower(users.first_name) ASC
* }}}
*
* @internal
*
* @param fDatabase $db The database the query is to be executed on
* @param fSchema $schema The schema for the database
* @param array $params The parameters for the fDatabase::query() call
* @param string $table The main table to be queried
* @return array The params with the SQL `FROM` and `GROUP BY` clauses injected
*/
public static function injectFromAndGroupByClauses($db, $schema, $params, $table)
{
$table = self::cleanTableName($schema, $table);
$joins = array();
if (strpos($params[0], ':from_clause') === FALSE) {
throw new fProgrammerException('No %1$s placeholder was found in:%2$s', ':from_clause', "\n" . $params[0]);
}
if (strpos($params[0], ':group_by_clause') === FALSE && !preg_match('#group\\s+by#i', $params[0])) {
throw new fProgrammerException('No %1$s placeholder was found in:%2$s', ':group_by_clause', "\n" . $params[0]);
}
$has_group_by_placeholder = strpos($params[0], ':group_by_clause') !== FALSE ? TRUE : FALSE;
// Separate the SQL from quoted values
preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\])*')|(?:[^']+)#", $params[0], $matches);
$table_alias = $table;
$used_aliases = array();
$table_map = array();
// If we are not passing in existing joins, start with the specified table
if (!$joins) {
$joins[] = array('join_type' => 'none', 'table_name' => $table, 'table_alias' => $table_alias);
}
$used_aliases[] = $table_alias;
foreach ($matches[0] as $match) {
if ($match[0] != "'") {
// This removes quotes from around . in the {route} specified of a shorthand column name
$match = preg_replace('#(\\{\\w+)"\\."(\\w+\\})#', '\\1.\\2', $match);
//fCore::expose($match);
preg_match_all('#(?<!\\w|"|=>)((?:"?((?:\\w+"?\\."?)?\\w+)(?:\\{([\\w.]+)\\})?"?=>)?("?(?:\\w+"?\\."?)?\\w+)(?:\\{([\\w.]+)\\})?"?)\\."?\\w+"?(?=[^\\w".{])#m', $match, $table_matches, PREG_SET_ORDER);
foreach ($table_matches as $table_match) {
if (!isset($table_match[5])) {
$table_match[5] = NULL;
}
if (!empty($table_match[2])) {
$table_match[2] = self::cleanTableName($schema, $table_match[2]);
}
$table_match[4] = self::cleanTableName($schema, $table_match[4]);
if (in_array($db->getType(), array('oracle', 'db2'))) {
foreach (array(2, 3, 4, 5) as $subpattern) {
if (isset($table_match[$subpattern])) {
$table_match[$subpattern] = strtolower($table_match[$subpattern]);
}
}
}
// This is a related table that is going to join to a once-removed table
if (!empty($table_match[2])) {
$related_table = $table_match[2];
$route = fORMSchema::getRouteName($schema, $table, $related_table, $table_match[3]);
$join_name = $table . '_' . $related_table . '{' . $route . '}';
$once_removed_table = $table_match[4];
// Add the once removed table to the aliases in case we also join directly to it
// which may cause the replacements later in this method to convert first to the
// real table name and then from the real table to the real table's alias
if (!in_array($once_removed_table, $used_aliases)) {
$used_aliases[] = $once_removed_table;
}
self::createJoin($schema, $table, $table_alias, $related_table, $route, $joins, $used_aliases);
$route = fORMSchema::getRouteName($schema, $related_table, $once_removed_table, $table_match[5]);
$join_name = self::createJoin($schema, $related_table, $joins[$join_name]['table_alias'], $once_removed_table, $route, $joins, $used_aliases);
$table_map[$table_match[1]] = $db->escape('%r', $joins[$join_name]['table_alias']);
// Remove the once removed table from the aliases so we also join directly to it without an alias
unset($used_aliases[array_search($once_removed_table, $used_aliases)]);
// This is a related table
} elseif (($table_match[4] != $table || fORMSchema::getRoutes($schema, $table, $table_match[4])) && self::cleanTableName($schema, $table_match[1]) != $table) {
$related_table = $table_match[4];
$route = fORMSchema::getRouteName($schema, $table, $related_table, $table_match[5]);
// If the related table is the current table and it is a one-to-many we don't want to join
if ($table_match[4] == $table) {
$one_to_many_routes = fORMSchema::getRoutes($schema, $table, $related_table, 'one-to-many');
if (isset($one_to_many_routes[$route])) {
$table_map[$table_match[1]] = $db->escape('%r', $table_alias);
continue;
}
}
$join_name = self::createJoin($schema, $table, $table_alias, $related_table, $route, $joins, $used_aliases);
$table_map[$table_match[1]] = $db->escape('%r', $joins[$join_name]['table_alias']);
}
}
}
}
// Determine if we joined a *-to-many relationship
$joined_to_many = FALSE;
foreach ($joins as $name => $join) {
if (is_numeric($name)) {
continue;
}
if (substr($name, -5) == '_join') {
$joined_to_many = TRUE;
break;
}
$main_table = preg_replace('#_' . $join['table_name'] . '{\\w+}$#iD', '', $name);
$second_table = $join['table_name'];
$route = preg_replace('#^[^{]+{([\\w.]+)}$#D', '\\1', $name);
$routes = fORMSchema::getRoutes($schema, $main_table, $second_table, '*-to-many');
if (isset($routes[$route])) {
$joined_to_many = TRUE;
break;
}
}
$found_order_by = FALSE;
$from_clause = self::createFromClauseFromJoins($db, $joins);
// If we are joining on a *-to-many relationship we need to group by the
// columns in the main table to prevent duplicate entries
if ($joined_to_many) {
$column_info = $schema->getColumnInfo($table);
$columns = array();
foreach ($column_info as $column => $info) {
$columns[] = $table . '.' . $column;
}
$group_by_columns = $db->escape('%r ', $columns);
$group_by_clause = ' GROUP BY ' . $group_by_columns;
} else {
$group_by_clause = ' ';
$group_by_columns = '';
}
// Put the SQL back together
$new_sql = '';
$preg_table_pattern = preg_quote($table, '#') . '\\.|' . preg_quote('"' . $table . '"', '#') . '\\.';
foreach ($matches[0] as $match) {
$temp_sql = $match;
// Get rid of the => notation and the :from_clause placeholder
if ($match[0] !== "'") {
// This removes quotes from around . in the {route} specified of a shorthand column name
$temp_sql = preg_replace('#(\\{\\w+)"\\."(\\w+\\})#', '\\1.\\2', $match);
foreach ($table_map as $arrow_table => $alias) {
$temp_sql = preg_replace('#(?<![\\w"])' . preg_quote($arrow_table, '#') . '(?!=[\\w"])#', $alias, $temp_sql);
}
// In the ORDER BY clause we need to wrap columns in
if ($found_order_by && $joined_to_many) {
$temp_sql = preg_replace('#(?<!avg\\(|count\\(|max\\(|min\\(|sum\\(|cast\\(|case |when |"|avg\\("|count\\("|max\\("|min\\("|sum\\("|cast\\("|case "|when "|\\{)\\b((?!' . $preg_table_pattern . ')("?\\w+"?\\.)?"?\\w+"?\\."?\\w+"?)(?![^\\w."])#i', 'max(\\1)', $temp_sql);
}
if ($joined_to_many && preg_match('#order\\s+by#i', $temp_sql)) {
$order_by_found = TRUE;
$parts = preg_split('#(order\\s+by)#i', $temp_sql, -1, PREG_SPLIT_DELIM_CAPTURE);
$parts[2] = $temp_sql = preg_replace('#(?<!avg\\(|count\\(|max\\(|min\\(|sum\\(|cast\\(|case |when |"|avg\\("|count\\("|max\\("|min\\("|sum\\("|cast\\("|case "|when "|\\{)\\b((?!' . $preg_table_pattern . ')("?\\w+"?\\.)?"?\\w+"?\\."?\\w+"?)(?![^\\w."])#i', 'max(\\1)', $parts[2]);
$temp_sql = join('', $parts);
}
$temp_sql = str_replace(':from_clause', $from_clause, $temp_sql);
if ($has_group_by_placeholder) {
$temp_sql = preg_replace('#\\s:group_by_clause(\\s|$)#', strtr($group_by_clause, array('\\' => '\\\\', '$' => '\\$')), $temp_sql);
} elseif ($group_by_columns) {
$temp_sql = preg_replace('#(\\sGROUP\\s+BY\\s((?!HAVING|ORDER\\s+BY).)*)\\s#i', '\\1, ' . strtr($group_by_columns, array('\\' => '\\\\', '$' => '\\$')), $temp_sql);
}
}
$new_sql .= $temp_sql;
}
$params[0] = $new_sql;
return $params;
}
/**
* 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;
}
