/**
* Returns metadata for all indexes in a table.
*/
public function getIndexes($table)
{
$columns = array();
foreach ($this->connection->query("
SELECT ordinal_position, column_name
FROM information_schema.columns
WHERE table_name = {$this->connection->quote($table)} AND table_schema = current_schema()
ORDER BY ordinal_position
") as $row) {
$columns[$row['ordinal_position']] = $row['column_name'];
}
$indexes = array();
foreach ($this->connection->query("
SELECT pg_class2.relname, indisunique, indisprimary, indkey
FROM pg_class
LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid
INNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid
WHERE pg_class.relname = {$this->connection->quote($table)}
") as $row) {
$indexes[$row['relname']]['name'] = $row['relname'];
$indexes[$row['relname']]['unique'] = $row['indisunique'] === 't';
$indexes[$row['relname']]['primary'] = $row['indisprimary'] === 't';
foreach (explode(' ', $row['indkey']) as $index) {
$indexes[$row['relname']]['columns'][] = $columns[$index];
}
}
return array_values($indexes);
}
/**
* Returns metadata for all columns in a table.
*/
public function getColumns($table)
{
$meta = $this->connection->query("
SELECT sql FROM sqlite_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
UNION ALL
SELECT sql FROM sqlite_temp_master WHERE type = 'table' AND name = {$this->connection->quote($table)}
")->fetch();
$columns = array();
foreach ($this->connection->query("PRAGMA table_info({$this->delimite($table)})") as $row) {
$column = $row['name'];
$pattern = "/(\"$column\"|\[$column\]|$column)\s+[^,]+\s+PRIMARY\s+KEY\s+AUTOINCREMENT/Ui";
$type = explode('(', $row['type']);
$columns[] = array(
'name' => $column,
'table' => $table,
'fullname' => "$table.$column",
'nativetype' => strtoupper($type[0]),
'size' => isset($type[1]) ? (int) $type[1] : NULL,
'nullable' => $row['notnull'] == '0',
'default' => $row['dflt_value'],
'autoincrement' => (bool) preg_match($pattern, $meta['sql']),
'vendor' => (array) $row,
);
}
return $columns;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
$keys = array();
$query = 'SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE '
. 'WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = ' . $this->connection->quote($table);
foreach ($this->connection->query($query) as $id => $row) {
$keys[$id]['name'] = $row['CONSTRAINT_NAME']; // foreign key name
$keys[$id]['local'] = $row['COLUMN_NAME']; // local columns
$keys[$id]['table'] = $row['REFERENCED_TABLE_NAME']; // referenced table
$keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME']; // referenced columns
}
return array_values($keys);
}
protected function reloadTableReference($table)
{
$tables = array();
$query = 'SELECT COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ' . $this->connection->quote($table);
foreach ($this->connection->query($query) as $row) {
$tables[strtolower($row[0])] = $row[1];
}
uksort($tables, create_function('$a, $b', '
return strlen($a) - strlen($b);
'));
$this->structure['belongsTo'][$table] = $tables;
}
/**
* Returns metadata for all foreign keys in a table.
*/
public function getForeignKeys($table)
{
/* Does't work with multicolumn foreign keys */
return $this->connection->query("
SELECT
co.conname::varchar AS name,
al.attname::varchar AS local,
cf.relname::varchar AS table,
af.attname::varchar AS foreign
FROM
pg_catalog.pg_constraint AS co
JOIN pg_catalog.pg_namespace AS n ON co.connamespace = n.oid
JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]
JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]
WHERE
n.nspname = current_schema()
AND co.contype = 'f'
AND cl.relname = {$this->connection->quote($table)}
")->fetchAll();
}
private function formatValue($value)
{
if (is_string($value)) {
if (strlen($value) > 20) {
$this->remaining[] = $value;
return '?';
} else {
return $this->connection->quote($value);
}
} elseif (is_int($value)) {
return (string) $value;
} elseif (is_float($value)) {
return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
} elseif (is_bool($value)) {
$this->remaining[] = $value;
return '?';
} elseif ($value === NULL) {
return 'NULL';
} elseif ($value instanceof NTableRow) {
return $value->getPrimary();
} elseif (is_array($value) || $value instanceof Traversable) {
$vx = $kx = array();
if (isset($value[0])) { // non-associative; value, value, value
foreach ($value as $v) {
$vx[] = $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'values') { // (key, key, ...) VALUES (value, value, ...)
$this->arrayMode = 'multi';
foreach ($value as $k => $v) {
$kx[] = $this->driver->delimite($k);
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
} elseif ($this->arrayMode === 'assoc') { // key=value, key=value, ...
foreach ($value as $k => $v) {
$vx[] = $this->driver->delimite($k) . '=' . $this->formatValue($v);
}
return implode(', ', $vx);
} elseif ($this->arrayMode === 'multi') { // multiple insert (value, value, ...), ...
foreach ($value as $k => $v) {
$vx[] = $this->formatValue($v);
}
return '(' . implode(', ', $vx) . ')';
}
} elseif ($value instanceof DateTime) {
return $this->driver->formatDateTime($value);
} elseif ($value instanceof NSqlLiteral) {
return $value->value;
} else {
$this->remaining[] = $value;
return '?';
}
}
