/** * Fetches the keys for a MySQL database * * @return array The keys arrays for every table in the database - see ::getKeys() for details */ private function fetchMySQLKeys() { $tables = $this->getTables(); $keys = array(); foreach ($tables as $table) { $keys[$table] = array(); $keys[$table]['primary'] = array(); $keys[$table]['foreign'] = array(); $keys[$table]['unique'] = array(); $result = $this->database->query('SHOW CREATE TABLE `' . substr($this->database->escape('string', $table), 1, -1) . '`'); $row = $result->fetchRow(); // Primary keys preg_match_all('/PRIMARY KEY\\s+\\("(.*?)"\\),?\\n/U', $row['Create Table'], $matches, PREG_SET_ORDER); if (!empty($matches)) { $keys[$table]['primary'] = explode('","', $matches[0][1]); } // Unique keys preg_match_all('/UNIQUE KEY\\s+"([^"]+)"\\s+\\("(.*?)"\\),?\\n/U', $row['Create Table'], $matches, PREG_SET_ORDER); foreach ($matches as $match) { $keys[$table]['unique'][] = explode('","', $match[2]); } // Foreign keys preg_match_all('#FOREIGN KEY \\("([^"]+)"\\) REFERENCES "([^"]+)" \\("([^"]+)"\\)(?:\\sON\\sDELETE\\s(SET\\sNULL|SET\\sDEFAULT|CASCADE|NO\\sACTION|RESTRICT))?(?:\\sON\\sUPDATE\\s(SET\\sNULL|SET\\sDEFAULT|CASCADE|NO\\sACTION|RESTRICT))?#', $row['Create Table'], $matches, PREG_SET_ORDER); foreach ($matches as $match) { $temp = array('column' => $match[1], 'foreign_table' => $match[2], 'foreign_column' => $match[3], 'on_delete' => 'no_action', 'on_update' => 'no_action'); if (isset($match[4])) { $temp['on_delete'] = strtolower(str_replace(' ', '_', $match[4])); } if (isset($match[5])) { $temp['on_update'] = strtolower(str_replace(' ', '_', $match[5])); } $keys[$table]['foreign'][] = $temp; } } return $keys; }
/** * Translates Flourish SQL `ALTER TABLE * RENAME TO` statements to the appropriate * statements for SQLite * * @param string $sql The SQL statements that will be executed against the database * @param array &$extra_statements Any extra SQL statements required for SQLite * @param array $data Data parsed from the `ALTER TABLE` statement * @return string The modified SQL statement */ private function translateSQLiteRenameTableStatements($sql, &$extra_statements, $data) { $tables = $this->getSQLiteTables(); if (in_array($data['new_table_name'], $tables)) { $this->throwException(self::compose('A table with the name "%1$s" already exists', $data['new_table_name']), $sql); } if (!in_array($data['table'], $tables)) { $this->throwException(self::compose('The table specified, "%1$s", does not exist', $data['table']), $sql); } // We start by dropping all references to this table $foreign_keys = $this->getSQLiteForeignKeys($data['table']); foreach ($foreign_keys as $foreign_key) { $extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r DROP FOREIGN KEY (%r)", array($foreign_key['table'], $foreign_key['column']), TRUE)); } // SQLite 2 does not natively support renaming tables, so we have to do // it by creating a new table name and copying all data and indexes if (version_compare($this->database->getVersion(), 3, '<')) { $renamed_create_sql = preg_replace('#^\\s*CREATE\\s+TABLE\\s+["\\[`\']?\\w+["\\]`\']?\\s+#i', 'CREATE TABLE "' . $data['new_table_name'] . '" ', $this->getSQLiteCreateTable($data['table'])); $this->addSQLiteTable($data['new_table_name'], $renamed_create_sql); // We rename string placeholders to prevent confusion with // string placeholders that are added by call to fDatabase $renamed_create_sql = str_replace(':string_', ':sub_string_', $renamed_create_sql); $create_statements = str_replace(':sub_string_', ':string_', $this->database->preprocess($renamed_create_sql, array(), TRUE)); $extra_statements[] = array_shift($create_statements); // Recreate the indexes on the new table $indexes = $this->getSQLiteIndexes($data['table']); foreach ($indexes as $name => $index) { $create_sql = $index['sql']; preg_match('#^\\s*CREATE\\s+(?:UNIQUE\\s+)?INDEX\\s+(?:[\'"`\\[]?\\w+[\'"`\\]]?\\.)?[\'"`\\[]?\\w+[\'"`\\]]?\\s+(ON\\s+[\'"`\\[]?\\w+[\'"`\\]]?)\\s*(\\((\\s*(?:\\s*[\'"`\\[]?\\w+[\'"`\\]]?\\s*,\\s*)*[\'"`\\[]?\\w+[\'"`\\]]?\\s*)\\))\\s*$#Di', $create_sql, $match); // Fix the table name to the new table $create_sql = str_replace($match[1], preg_replace('#(?:`|\'|"|\\[|\\b)?' . preg_quote($data['table'], '#') . '(?:`|\'|"|\\]|\\b)?#i', '"' . $data['new_table_name'] . '"', $match[1]), $create_sql); // We change the name of the index to keep it in sync // with the new table name $new_name = preg_replace('#^' . preg_quote($data['table'], '#') . '_#i', $data['new_table_name'] . '_', $name); $create_sql = preg_replace('#[\'"`\\[]?' . preg_quote($name, '#') . '[\'"`\\]]?(\\s+ON\\s+)#i', '"' . $new_name . '"\\1', $create_sql); $extra_statements[] = $create_sql; $this->addSQLiteIndex($new_name, $data['new_table_name'], $create_sql); } $column_names = $this->getSQLiteColumns($data['table']); $extra_statements[] = $this->database->escape("INSERT INTO %r (%r) SELECT %r FROM %r", $data['new_table_name'], $column_names, $column_names, $data['table']); $extra_statements = array_merge($extra_statements, $create_statements); $extra_statements = array_merge($extra_statements, $this->database->preprocess("DROP TABLE %r", array($data['table']), TRUE)); // SQLite 3 natively supports renaming tables, but it does not fix // references to the old table name inside of trigger bodies } else { // We add the rename SQL in the middle so it happens after we drop the // foreign key constraints and before we re-add them $extra_statements[] = $sql; $this->addSQLiteTable($data['new_table_name'], preg_replace('#^\\s*CREATE\\s+TABLE\\s+[\'"\\[`]?\\w+[\'"\\]`]?\\s+#i', 'CREATE TABLE "' . $data['new_table_name'] . '" ', $this->getSQLiteCreateTable($data['table']))); $this->removeSQLiteTable($data['table']); // Copy the trigger definitions to the new table name foreach ($this->getSQLiteTriggers() as $name => $trigger) { if ($trigger['table'] == $data['table']) { $this->addSQLiteTrigger($name, $data['new_table_name'], $trigger['sql']); } } // Move the index definitions to the new table name foreach ($this->getSQLiteIndexes($data['table']) as $name => $index) { $this->addSQLiteIndex($name, $data['new_table_name'], preg_replace('#(\\s+ON\\s+)["\'`\\[]?\\w+["\'`\\]]?#', '\\1"' . preg_quote($data['new_table_name'], '#') . '"', $index['sql'])); } foreach ($this->getSQLiteTriggers() as $name => $trigger) { $create_sql = $trigger['sql']; $create_sql = preg_replace('#( on table )"' . $data['table'] . '"#i', '\\1"' . $data['new_table_name'] . '"', $create_sql); $create_sql = preg_replace('#(\\s+FROM\\s+)(`' . $data['table'] . '`|"' . $data['table'] . '"|\'' . $data['table'] . '\'|' . $data['table'] . '|\\[' . $data['table'] . '\\])#i', '\\1"' . $data['new_table_name'] . '"', $create_sql); if ($create_sql != $trigger['sql']) { $extra_statements[] = $this->database->escape("DROP TRIGGER %r", $name); $this->removeSQLiteTrigger($name); $this->addSQLiteTrigger($name, $data['new_table_name'], $create_sql); $extra_statements[] = $create_sql; } } } // Here we recreate the references that we dropped at the beginning foreach ($foreign_keys as $foreign_key) { $extra_statements = array_merge($extra_statements, $this->database->preprocess("ALTER TABLE %r ADD FOREIGN KEY (%r) REFERENCES %r(%r) ON UPDATE " . $foreign_key['on_update'] . " ON DELETE " . $foreign_key['on_delete'], array($foreign_key['table'], $foreign_key['column'], $data['new_table_name'], $foreign_key['foreign_column']), TRUE)); } // Remove any nested transactions $extra_statements = array_diff($extra_statements, array("BEGIN", "COMMIT")); // Since the actual rename or create/drop has to happen after adjusting // foreign keys, we previously added it in the appropriate place and // now need to provide the first statement to be run return array_shift($extra_statements); }
/** * Adds `WHERE` params to the SQL for the primary keys of this record set * * @param fDatabase $db The database the query will be executed on * @param fSchema $schema The schema for the database * @param array $params The parameters for the fDatabase::query() call * @param string $route The route to this table from another table * @return array The params with the `WHERE` clause added */ private function addWhereParams($db, $schema, $params, $route = NULL) { $table = fORM::tablize($this->class); $table_with_route = $route ? $table . '{' . $route . '}' : $table; $pk_columns = $schema->getKeys($table, 'primary'); // We have a multi-field primary key, making things kinda ugly if (sizeof($pk_columns) > 1) { $escape_pk_columns = array(); foreach ($pk_columns as $pk_column) { $escaped_pk_columns[$pk_column] = $db->escape('%r', $table_with_route . '.' . $pk_column); } $column_info = $schema->getColumnInfo($table); $conditions = array(); foreach ($this->getPrimaryKeys() as $primary_key) { $sub_conditions = array(); foreach ($pk_columns as $pk_column) { $value = $primary_key[$pk_column]; // This makes sure the query performs the way an insert will if ($value === NULL && $column_info[$pk_column]['not_null'] && $column_info[$pk_column]['default'] !== NULL) { $value = $column_info[$pk_column]['default']; } $sub_conditions[] = str_replace('%r', $escaped_pk_columns[$pk_column], fORMDatabase::makeCondition($schema, $table, $pk_column, '=', $value)); $params[] = $value; } $conditions[] = join(' AND ', $sub_conditions); } $params[0] .= '(' . join(') OR (', $conditions) . ')'; // We have a single primary key field, making things nice and easy } else { $first_pk_column = $pk_columns[0]; $params[0] .= $db->escape('%r IN ', $table_with_route . '.' . $first_pk_column); $params[0] .= '(' . $schema->getColumnInfo($table, $first_pk_column, 'placeholder') . ')'; $params[] = $this->getPrimaryKeys(); } return $params; }
/** * 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; }
/** * Takes an array of parameters and prepares them for use in a prepared statement * * @param array $params The parameters * @return array The prepared parameters */ private function prepareParams($params) { $type = $this->database->getType(); $extension = $this->database->getExtension(); $statement = $this->statement; $new_params = array(); // The mysqli extension requires all params be set in one call $params_reference = array(); $params_type = ''; foreach ($params as $i => $param) { // Prepared statements don't support multi-value params if (is_array($param)) { throw new fProgrammerException('The value passed for placeholder #%s is an array, however multiple values are not supported with prepared statements', $i + 1); } // A few of the database extensions don't have prepared statement support // so instead we don't bother preparing the params, we just do a normal escape if (in_array($extension, array('mssql', 'mysql', 'sqlite'))) { $new_params[] = $params[$i]; continue; } $placeholder = $this->placeholders[$i]; // We want to normally escape all values except strings // since that actually changed the string if (!in_array($placeholder, array('%l', '%s'))) { $params[$i] = $this->database->escape($placeholder, $params[$i]); // Dates, times, timestamps and some booleans need to be unquoted if (in_array($placeholder, array('%d', '%t', '%p')) || ($type == 'mssql' || $type == 'sqlite' || $type == 'db2') && $placeholder == '%b') { $params[$i] = substr($params[$i], 1, -1); // Some booleans need to be converted to integers } elseif ($placeholder == '%b' && ($type == 'postgresql' || $type == 'mysql')) { $params[$i] = $params[$i] == 'TRUE' ? 1 : 0; } // For strings and blobs we need to manually cast objects // This is done in fDatabase::escape() for all other types } else { if (is_object($params[$i]) && is_callable(array($params[$i], '__toString'))) { $params[$i] = $params[$i]->__toString(); } elseif (is_object($params[$i])) { $params[$i] = (string) $params[$i]; } } // For the database extensions that require is, here we bind params // to the actual statements using the appropriate data types switch ($extension) { case 'mysqli': switch ($placeholder) { case '%l': // Blobs that are larger than the packet size have to have NULL // bound and then the data sent via the long data function $n = NULL; $params_type .= 'b'; $params_reference[$i] =& $params[$i]; $statement->send_long_data($i, $params[$i]); break; case '%b': $params_type .= 'i'; $params_reference[$i] =& $params[$i]; break; case '%f': $params_type .= 'd'; $params_reference[$i] =& $params[$i]; break; case '%d': case '%i': // Ints are sent as strings to get past 32 bit int limit, allowing unsigned ints // Ints are sent as strings to get past 32 bit int limit, allowing unsigned ints case '%s': case '%t': case '%p': $params_type .= 's'; $params_reference[$i] =& $params[$i]; break; } break; case 'oci8': switch ($placeholder) { case '%l': oci_bind_by_name($statement, ':p' . ($i + 1), $params[$i], -1, SQLT_BLOB); break; case '%b': case '%i': oci_bind_by_name($statement, ':p' . ($i + 1), $params[$i], -1, SQLT_INT); break; case '%d': case '%f': // There is no constant for floats, so we treat them as strings // There is no constant for floats, so we treat them as strings case '%s': case '%t': case '%p': oci_bind_by_name($statement, ':p' . ($i + 1), $params[$i], -1, SQLT_CHR); break; } break; case 'odbc': // ODBC does not allow strings that start and end with single quotes, so a space must be added at the end if (is_string($params[$i]) && strlen($params[$i]) >= 2 && $params[$i][0] == "'" && $params[$i][strlen($params[$i]) - 1] == "'") { $params[$i] .= ' '; } break; case 'pdo': switch ($placeholder) { case '%l': $statement->bindParam($i + 1, $params[$i], $params[$i] === NULL ? PDO::PARAM_NULL : PDO::PARAM_LOB); break; case '%b': $statement->bindParam($i + 1, $params[$i], $params[$i] === NULL ? PDO::PARAM_NULL : PDO::PARAM_BOOL); break; case '%i': $statement->bindParam($i + 1, $params[$i], $params[$i] === NULL ? PDO::PARAM_NULL : PDO::PARAM_INT); break; case '%d': case '%f': // For some reason float are supposed to be bound as strings // For some reason float are supposed to be bound as strings case '%s': case '%t': case '%p': $statement->bindParam($i + 1, $params[$i], $params[$i] === NULL ? PDO::PARAM_NULL : PDO::PARAM_STR); break; } break; case 'sqlsrv': $this->bound_params[$i] = $params[$i]; break; } $new_params[] = $params[$i]; } if ($extension == 'mysqli') { array_unshift($params_reference, $params_type); call_user_func_array(array($statement, 'bind_param'), $params_reference); } return $new_params; }