/**
* Write metadata files for all the listed tables.
*
* @param string $path
* @param DbAdapter $db
* @param array $tables
* @return void
*/
protected function writeMetadataFiles($path, DbAdapter $db, array $tables)
{
$inflector = new Inflector();
foreach ($tables as $table) {
$columns = $db->describeTable($table);
$references = $db->listForeignKeyReferences($table);
$uniqueConstraints = $db->listUniqueConstraints($table);
$title = $inflector->titleize($table);
$replacements = array('{{singular}}' => $inflector->singularize($title), '{{plural}}' => $inflector->pluralize($title), '{{columns}}' => var_export($columns, true), '{{references}}' => var_export($references, true), '{{uniqueConstraints}}' => var_export($uniqueConstraints, true));
file_put_contents("{$path}/{$table}.php", str_replace(array_keys($replacements), $replacements, file_get_contents(__DIR__ . '/db-metadata/template.tpl')));
}
}
/**
* Load the metadata for the options table.
*
* @throws \Dewdrop\Exception
* @return array
*/
protected function loadTableMetadata()
{
if (!$this->tableName) {
throw new DewdropException('Table name must be set prior to loading metadata.');
}
return $this->dbAdapter->getTableMetadata($this->tableName);
}
/**
* Delete any existing settings from the database. Done inside a transaction
* while saving new settings.
*
* @return void
*/
public function deleteCurrentSettings()
{
$this->dbAdapter->delete('dewdrop_field_groups', $this->dbAdapter->quoteInto('dewdrop_field_group_id IN (
SELECT dewdrop_field_group_id
FROM dewdrop_sorted_fields
WHERE component = ?
)', $this->componentName));
$this->dbAdapter->delete('dewdrop_sorted_fields', $this->dbAdapter->quoteInto('component = ?', $this->componentName));
}
private function getMissingIndexesByTable()
{
$missingIndexesByTable = [];
foreach ($this->db->listTables() as $tableName) {
$missingIndexes = $this->db->listMissingForeignKeyIndexes($tableName);
if (count($missingIndexes)) {
$missingIndexesByTable[$tableName] = $missingIndexes;
}
}
return $missingIndexesByTable;
}
/**
* Get an array representing the columns in the root table's primary
* key. The returned array's keys will be the column names and the values
* will be the metadata for that column as returned by
* \Dewdrop\Db\Adapter::describeTable().
*
* @return array
*/
protected function getEntityPrimaryKey()
{
if (!$this->entityPrimaryKey) {
$meta = $this->db->getTableMetadata($this->tableName);
$pkey = array();
foreach ($meta['columns'] as $name => $column) {
if ($column['PRIMARY']) {
$pkey[$name] = $column;
}
}
$this->entityPrimaryKey = $pkey;
}
return $this->entityPrimaryKey;
}
/**
* 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 = "SELECT\n a.attnum,\n n.nspname,\n c.relname,\n a.attname AS colname,\n t.typname AS type,\n a.atttypmod,\n FORMAT_TYPE(a.atttypid, a.atttypmod) AS complete_type,\n d.adsrc AS default_value,\n a.attnotnull AS notnull,\n a.attlen AS length,\n co.contype,\n ARRAY_TO_STRING(co.conkey, ',') AS conkey\n FROM pg_attribute AS a\n JOIN pg_class AS c ON a.attrelid = c.oid\n JOIN pg_namespace AS n ON c.relnamespace = n.oid\n JOIN pg_type AS t ON a.atttypid = t.oid\n LEFT OUTER JOIN pg_constraint AS co ON (co.conrelid = c.oid\n AND a.attnum = ANY(co.conkey) AND co.contype = 'p')\n LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum\n WHERE a.attnum > 0 AND c.relname = " . $this->adapter->quote($tableName) . ' ORDER BY a.attnum';
$result = $this->fetchAll($sql, array(), Adapter::ARRAY_N);
$attnum = 0;
$nspname = 1;
$relname = 2;
$colname = 3;
$type = 4;
$atttypemod = 5;
$complete_type = 6;
$default_value = 7;
$notnull = 8;
$length = 9;
$contype = 10;
$conkey = 11;
$desc = array();
foreach ($result as $key => $row) {
$defaultValue = $row[$default_value];
if ($row[$type] == 'varchar' || $row[$type] == 'bpchar') {
if (preg_match('/character(?: varying)?(?:\\((\\d+)\\))?/', $row[$complete_type], $matches)) {
if (isset($matches[1])) {
$row[$length] = $matches[1];
} else {
$row[$length] = null;
// unlimited
}
}
if (preg_match("/^'(.*?)'::(?:character varying|bpchar)\$/", $defaultValue, $matches)) {
$defaultValue = $matches[1];
}
}
if ($row[$type] === 'bool' && $row[$default_value]) {
$defaultValue = 'true' === $row[$default_value] ? 1 : 0;
}
if (false !== strpos($defaultValue, '(')) {
$defaultValue = null;
}
list($primary, $primaryPosition, $identity) = array(false, null, false);
if ($row[$contype] == 'p') {
$primary = true;
$primaryPosition = array_search($row[$attnum], explode(',', $row[$conkey])) + 1;
$identity = (bool) preg_match('/^nextval/', $row[$default_value]);
}
$desc[$this->adapter->foldCase($row[$colname])] = array('SCHEMA_NAME' => $this->adapter->foldCase($row[$nspname]), 'TABLE_NAME' => $this->adapter->foldCase($row[$relname]), 'COLUMN_NAME' => $this->adapter->foldCase($row[$colname]), 'COLUMN_POSITION' => $row[$attnum], 'DATA_TYPE' => $row[$type], 'GENERIC_TYPE' => $this->mapNativeTypeToGenericType($row[$type], $length), 'DEFAULT' => $defaultValue, 'NULLABLE' => (bool) ($row[$notnull] != 't'), 'LENGTH' => $row[$length], 'SCALE' => null, 'PRECISION' => null, 'UNSIGNED' => null, 'PRIMARY' => $primary, 'PRIMARY_POSITION' => $primaryPosition, 'IDENTITY' => $identity);
}
return $desc;
}
/**
* Get the values for any foreign keys in the DB table that reference the user
* object's table. Allows the filter to be applied on a per-user basis.
*
* @return array
*/
protected function getUserReferenceValues()
{
$user = $this->getUser();
if (!$user) {
return [];
}
$out = [];
$userTable = $user->getTable();
$metadata = $this->dbAdapter->getTableMetadata($this->dbTableName);
foreach ($metadata['references'] as $foreignKey => $reference) {
if ($reference['table'] === $userTable->getTableName()) {
$out[$foreignKey] = $user->get($reference['column']);
}
}
return $out;
}
public function testDelete()
{
$table = 'dewdrop_test_fruits';
$idField = 'dewdrop_test_fruit_id';
$fetchAllSql = "SELECT * FROM {$table} ORDER BY {$idField}";
$fruits = $this->db->fetchAll($fetchAllSql);
$this->assertSame(5, count($fruits));
$this->assertEquals(1, $fruits[0][$idField]);
$fruit = $this->table->find(1);
$this->assertSame(1, $fruit->delete());
$this->assertNull($fruit->get('dewdrop_test_fruit_id'));
$this->assertNull($fruit->get('name'));
$this->assertNull($fruit->get('is_delicious'));
$this->assertNull($fruit->get('level_of_deliciousness'));
$fruits = $this->db->fetchAll($fetchAllSql);
$this->assertSame(4, count($fruits));
$this->assertEquals(2, $fruits[0][$idField]);
}
/**
* Render LIMIT OFFSET clause
*
* @param string $sql SQL query
* @return string
*/
protected function renderLimitoffset($sql)
{
$count = 0;
$offset = 0;
if (!empty($this->parts[self::LIMIT_OFFSET])) {
$offset = (int) $this->parts[self::LIMIT_OFFSET];
$count = PHP_INT_MAX;
}
if (!empty($this->parts[self::LIMIT_COUNT])) {
$count = (int) $this->parts[self::LIMIT_COUNT];
}
/*
* Add limits clause
*/
if ($count > 0) {
$sql = trim($this->adapter->limit($sql, $count, $offset));
}
return $sql;
}
public function testDelete()
{
$table = 'dewdrop_test_fruits';
$idField = 'dewdrop_test_fruit_id';
$fetchAllSql = "SELECT * FROM {$table} ORDER BY {$idField}";
$fruits = $this->db->fetchAll($fetchAllSql);
$this->assertSame(5, count($fruits));
$this->assertEquals(1, $fruits[0][$idField]);
$this->assertSame(1, $this->table->delete("{$idField} = 1"));
$fruits = $this->db->fetchAll($fetchAllSql);
$this->assertSame(4, count($fruits));
$this->assertEquals(2, $fruits[0][$idField]);
$this->assertSame(0, $this->table->delete("{$idField} = 7"));
$fruits = $this->db->fetchAll($fetchAllSql);
$this->assertSame(4, count($fruits));
$this->assertEquals(2, $fruits[0][$idField]);
$this->assertSame(1, $this->table->delete(array("{$idField} = 2", 'is_delicious')));
$fruits = $this->db->fetchAll($fetchAllSql);
$this->assertSame(3, count($fruits));
$this->assertEquals(3, $fruits[0][$idField]);
}
/**
* Determine a reasonable value to use as a title for a foreign key
* reference. We'll look for a name or title in the referenced table. If
* neither is present, we just grab the first column, which is probably the
* ID itself.
*
* However, you can call setReferenceTitleColumn() to supply a different
* column to use or an Expr object.
*
* @param $localColumn
* @param array $reference
* @param string $alias
* @return string|Expr
*/
private function findReferenceTitleColumn($localColumn, array $reference, $alias)
{
if (array_key_exists($localColumn, $this->referenceTitleColumns)) {
$value = $this->referenceTitleColumns[$localColumn];
if (is_callable($value)) {
$value = call_user_func($value, $alias);
if (!is_string($value) && !$value instanceof Expr) {
throw new Exception('Title column callbacks should return a string or Expr.');
}
}
return $value;
}
$metadata = $this->db->getTableMetadata($reference['table']);
$columns = array_keys($metadata['columns']);
if (in_array('name', $columns)) {
return 'name';
} elseif (in_array('title', $columns)) {
return 'title';
} else {
return array_shift($columns);
}
}
public function testGetDriver()
{
$this->assertInstanceOf('\\Dewdrop\\Db\\Driver\\DriverInterface', $this->db->getDriver());
}
/**
* 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;
}
/**
* Check to see if the changelog table exists in the database already.
*
* @return bool
*/
protected function tableExists()
{
return in_array($this->tableName, $this->dbAdapter->listTables());
}
/**
* 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;
}
/**
* Define the DB resource for Pimple.
*
* @return Wp
*/
public function defineDb()
{
$this->pimple['db'] = $this->pimple->share(function () {
global $wpdb;
$adapter = new DbAdapter();
$driver = new WpdbDriver($adapter, $wpdb);
$adapter->setDriver($driver);
return $adapter;
});
return $this;
}
/**
* Use MySQL's FOUND_ROWS() facility to retrieve the total number of rows
* that would have been fetched on the previous statement if no LIMIT was
* applied.
*
* @param array $resultSet
* @return integer
*/
public function fetchTotalRowCount(array $resultSet)
{
return $this->adapter->fetchOne('SELECT FOUND_ROWS()');
}
public function generateAnalyzeTableStatement($tableName)
{
return sprintf('ANALYZE %s;', $this->adapter->quoteIdentifier($tableName));
}