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