/** * 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)); }