/**
* Get the highest revision number applied for the given changeset name.
*
* @param string $changesetName
* @return int
*/
public function getCurrentRevisionForChangeset($changesetName)
{
if (!$this->tableExists()) {
$this->createTable();
}
return (int) $this->dbAdapter->fetchOne(sprintf('SELECT MAX(change_number) FROM %s WHERE delta_set = ?', $this->dbAdapter->quoteIdentifier($this->tableName)), array($changesetName));
}
/**
* Returns the column descriptions for a table.
*
* The return value is an associative array keyed by the column name,
* as returned by the RDBMS.
*
* The value of each array element is an associative array
* with the following keys:
*
* SCHEMA_NAME => string; name of database or schema
* TABLE_NAME => string;
* COLUMN_NAME => string; column name
* COLUMN_POSITION => number; ordinal position of column in table
* DATA_TYPE => string; SQL datatype name of column
* DEFAULT => string; default expression of column, null if none
* NULLABLE => boolean; true if column can have nulls
* LENGTH => number; length of CHAR/VARCHAR
* SCALE => number; scale of NUMERIC/DECIMAL
* PRECISION => number; precision of NUMERIC/DECIMAL
* UNSIGNED => boolean; unsigned property of an integer type
* PRIMARY => boolean; true if column is part of the primary key
* PRIMARY_POSITION => integer; position of column in primary key
* IDENTITY => integer; true if column is auto-generated with unique values
*
* @param string $tableName
* @return array
*/
public function describeTable($tableName)
{
$sql = 'DESCRIBE ' . $this->adapter->quoteIdentifier($tableName, true);
$result = $this->fetchAll($sql, array(), ARRAY_A);
$desc = array();
$row_defaults = array('Length' => null, 'Scale' => null, 'Precision' => null, 'Unsigned' => null, 'Primary' => false, 'PrimaryPosition' => null, 'Identity' => false);
$i = 1;
$p = 1;
foreach ($result as $key => $row) {
$row = array_merge($row_defaults, $row);
if (preg_match('/unsigned/', $row['Type'])) {
$row['Unsigned'] = true;
}
if (preg_match('/^((?:var)?char)\\((\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = $matches[1];
$row['Length'] = $matches[2];
}
if (preg_match('/^((?:var)?binary)\\((\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = $matches[1];
$row['Length'] = $matches[2];
} elseif (preg_match('/^decimal\\((\\d+),(\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = 'decimal';
$row['Precision'] = $matches[1];
$row['Scale'] = $matches[2];
} elseif (preg_match('/^float\\((\\d+),(\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = 'float';
$row['Precision'] = $matches[1];
$row['Scale'] = $matches[2];
} elseif (preg_match('/^double\\((\\d+),(\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = 'double';
$row['Precision'] = $matches[1];
$row['Scale'] = $matches[2];
} elseif (preg_match('/^((?:big|medium|small|tiny)?int)\\((\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = $matches[1];
/**
* The optional argument of a MySQL int type is not precision
* or length; it is only a hint for display width.
*/
}
if (strtoupper($row['Key']) == 'PRI') {
$row['Primary'] = true;
$row['PrimaryPosition'] = $p;
if ($row['Extra'] == 'auto_increment') {
$row['Identity'] = true;
} else {
$row['Identity'] = false;
}
++$p;
}
$desc[$this->adapter->foldCase($row['Field'])] = array('SCHEMA_NAME' => null, 'TABLE_NAME' => $this->adapter->foldCase($tableName), 'COLUMN_NAME' => $this->adapter->foldCase($row['Field']), 'COLUMN_POSITION' => $i, 'DATA_TYPE' => $row['Type'], 'DEFAULT' => $row['Default'], 'NULLABLE' => (bool) ($row['Null'] == 'YES'), 'LENGTH' => $row['Length'], 'SCALE' => $row['Scale'], 'GENERIC_TYPE' => $this->mapNativeTypeToGenericType($row['Type'], $row['Length']), 'PRECISION' => $row['Precision'], 'UNSIGNED' => $row['Unsigned'], 'PRIMARY' => $row['Primary'], 'PRIMARY_POSITION' => $row['PrimaryPosition'], 'IDENTITY' => $row['Identity']);
++$i;
}
return $desc;
}
/**
* Attempt to apply some default filters to a generated options
* statement. Dewdrop supported two similar conventions here: "active"
* columns and "deleted" columns. If your options table has an "active"
* column, only options for which "active" is true will be included.
* If your options table has a "deleted" column, options with that
* column set as true will be excluded.
*
* @param array $columns The columns portion of the table metadata.
* @param Select $stmt
* @return \Dewdrop\Db\Select
*/
protected function filterStmt($columns, Select $stmt)
{
if (array_key_exists('active', $columns)) {
$column = $this->dbAdapter->quoteIdentifier("{$this->tableName}.active");
$stmt->where("{$column} = true");
}
if (array_key_exists('deleted', $columns)) {
$column = $this->dbAdapter->quoteIdentifier("{$this->tableName}.deleted");
$stmt->where("{$column} = false");
}
return $stmt;
}
/**
* Load the current selections from the database. Will return an array
* of field IDs.
*
* @return array
*/
public function load()
{
$userRefs = $this->getUserReferenceValues();
$select = $this->selectForLoad();
foreach ($userRefs as $column => $value) {
$select->where(sprintf('%s = ?', $this->dbAdapter->quoteIdentifier($column)), $value);
}
$selections = $this->dbAdapter->fetchCol($select);
// Fall back to global selections, if no per-user selections are available
if (count($userRefs) && !count($selections)) {
$select = $this->selectForLoad();
foreach ($userRefs as $column => $value) {
$select->where(sprintf('%s IS NULL', $this->dbAdapter->quoteIdentifier($column)));
}
$selections = $this->dbAdapter->fetchCol($select);
}
return is_array($selections) ? $selections : [];
}
/**
* Render ORDER clause
*
* @param string $sql SQL query
* @return string
*/
protected function renderOrder($sql)
{
if ($this->parts[self::ORDER]) {
$order = array();
foreach ($this->parts[self::ORDER] as $term) {
if (is_array($term)) {
if (is_numeric($term[0]) && strval(intval($term[0])) == $term[0]) {
$order[] = (int) trim($term[0]) . ' ' . $term[1];
} else {
$order[] = $this->adapter->quoteIdentifier($term[0], true) . ' ' . $term[1];
}
} elseif (is_numeric($term) && strval(intval($term)) == $term) {
$order[] = (int) trim($term);
} else {
$order[] = $this->adapter->quoteIdentifier($term, true);
}
}
$sql .= ' ' . self::SQL_ORDER_BY . ' ' . implode(', ', $order);
}
return $sql;
}
/**
* Join against any tables referenced by foreign keys in order to get a
* reasonable value to display for them. If the foreign key in the listing
* table is nullable, we'll use a LEFT JOIN so that a missing foreign key
* value does not exclude the record from the result set.
*
* We follow a naming convention for these values in result sets: the
* foreign key ends with "_id" and the value in the result set is aliased to
* that foreign key column name minus "_id". For example, let's say the
* foreign key column was "favorite_book_id". In the result set for the
* listing query, we'd include an alias of "favorite_book" that pointed
* to the "title" column of the referenced "books" table. That way, both
* the integer favorite_book_id and the title favorite_book are available
* when we render the listing.
*
* @param Select $select
* @return Select
*/
private function selectForeignKeyValues(Select $select)
{
$tableAlias = $this->getAlias($this->table->getTableName());
$tableInstances = [];
foreach ($this->table->getMetadata('references') as $column => $reference) {
$metadata = $this->table->getMetadata('columns', $column);
if ($metadata['NULLABLE']) {
$join = 'joinLeft';
} else {
$join = 'join';
}
$refTable = $reference['table'];
if (!array_key_exists($refTable, $tableInstances)) {
$tableInstances[$refTable] = 0;
}
$refAlias = $this->getAlias($refTable, $tableInstances[$refTable], $column);
$tableInstances[$refTable] += 1;
$columnAlias = preg_replace('/_id$/i', '', $column);
$titleColumn = $this->findReferenceTitleColumn($column, $reference, $refAlias);
$select->{$join}([$refAlias => $refTable], sprintf('%s = %s', $this->db->quoteIdentifier("{$tableAlias}.{$column}"), $this->db->quoteIdentifier("{$refAlias}.{$reference['column']}")), [$columnAlias => $titleColumn]);
}
return $select;
}
/**
* Build a WHERE clause for use in deleting cross-reference values while
* saving. If we have no cross-reference values, we delete all cross-reference
* rows with the given anchor value. Otherwise, we only delete those rows
* that are not related to the newly supplied $xrefValues.
*
* @param DbAdapter $db
* @param array $xrefValues
* @param mixed $anchorValue
* @return string
*/
private function buildDeleteWhereClause(DbAdapter $db, array $xrefValues, $anchorValue)
{
$anchorName = $db->quoteIdentifier("{$this->xrefTableName}.{$this->getXrefAnchorColumnName()}");
if (!count($xrefValues) || $this->allowDuplicateValues) {
$where = "{$anchorName} = ?";
$where = $db->quoteInto($where, $anchorValue);
} else {
$refName = $db->quoteIdentifier("{$this->xrefTableName}.{$this->getXrefReferenceColumnName()}");
$where = "{$anchorName} = ? AND {$refName} NOT IN (?)";
$where = $db->quoteInto($where, $anchorValue, null, 1);
$where = $db->quoteInto($where, $xrefValues, null, 1);
}
return $where;
}
/**
* Assemble SQL for finding a row by its primary key.
*
* @param array $args The primary key values
* @return string
*/
private function assembleFindSql(array $args)
{
$sql = sprintf('SELECT * FROM %s WHERE %s', $this->db->quoteIdentifier($this->tableName), $this->assembleFindWhere($args));
return $sql;
}
public function generateAnalyzeTableStatement($tableName)
{
return sprintf('ANALYZE %s;', $this->adapter->quoteIdentifier($tableName));
}
