/**
* Inserts row in a table.
* @param mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
* @return NTableRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
*/
public function insert($data)
{
if ($data instanceof NTableSelection) {
$data = $data->getSql();
} elseif ($data instanceof Traversable) {
$data = iterator_to_array($data);
}
$return = $this->connection->query("INSERT INTO $this->delimitedName", $data);
if (!is_array($data)) {
return $return->rowCount();
}
$this->checkReferenceNewKeys = TRUE;
if (!isset($data[$this->primary]) && ($id = $this->connection->lastInsertId())) {
$data[$this->primary] = $id;
return $this->rows[$id] = new NTableRow($data, $this);
} else {
return new NTableRow($data, $this);
}
}
/**
* 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);
}
/**
* Inserts row in a table.
* @param mixed array($column => $value)|Traversable for single row insert or Selection|string for INSERT ... SELECT
* @return NTableRow or FALSE in case of an error or number of affected rows for INSERT ... SELECT
*/
public function insert($data)
{
if ($data instanceof NTableSelection) {
$data = $data->getSql();
} elseif ($data instanceof Traversable) {
$data = iterator_to_array($data);
}
$return = $this->connection->query($this->sqlBuilder->buildInsertQuery(), $data);
$this->checkReferenced = TRUE;
if (!is_array($data)) {
return $return->rowCount();
}
if (!is_array($this->primary) && !isset($data[$this->primary]) && ($id = $this->connection->lastInsertId($this->getPrimarySequence()))) {
$data[$this->primary] = $id;
}
$row = $this->createRow($data);
if ($signature = $row->getSignature(FALSE)) {
$this->rows[$signature] = $row;
}
return $row;
}
/**
* Returns list of tables.
*/
public function getTables()
{
$tables = array();
foreach ($this->connection->query('SELECT * FROM cat', PDO::FETCH_NUM) as $row) {
if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
$tables[] = array(
'name' => $row[0],
'view' => $row[1] === 'VIEW',
);
}
}
return $tables;
}
/**
* 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)
{
$keys = array();
foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
$keys[$row['id']]['name'] = $row['id']; // foreign key name
$keys[$row['id']]['local'][$row['seq']] = $row['from']; // local columns
$keys[$row['id']]['table'] = $row['table']; // referenced table
$keys[$row['id']]['foreign'][$row['seq']] = $row['to']; // referenced columns
$keys[$row['id']]['onDelete'] = $row['on_delete'];
$keys[$row['id']]['onUpdate'] = $row['on_update'];
if ($keys[$row['id']]['foreign'][0] == NULL) {
$keys[$row['id']]['foreign'] = NULL;
}
}
return array_values($keys);
}
/**
* Returns metadata for all indexes in a table.
*/
public function getIndexes($table)
{
/*$this->connection->query("
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = {$this->connection->quote($table)} AND TABLE_SCHEMA = DATABASE()
AND REFERENCED_COLUMN_NAME IS NULL
");*/
$indexes = array();
foreach ($this->connection->query('SHOW INDEX FROM ' . $this->delimite($table)) as $row) {
$indexes[$row['Key_name']]['name'] = $row['Key_name'];
$indexes[$row['Key_name']]['unique'] = !$row['Non_unique'];
$indexes[$row['Key_name']]['primary'] = $row['Key_name'] === 'PRIMARY';
$indexes[$row['Key_name']]['columns'][$row['Seq_in_index'] - 1] = $row['Column_name'];
}
return array_values($indexes);
}
/**
* 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();
}
/**
* Import SQL dump from file - extreme fast.
* @return int count of commands
*/
public static function loadFromFile(NConnection $connection, $file)
{
@set_time_limit(0); // intentionally @
$handle = @fopen($file, 'r'); // intentionally @
if (!$handle) {
throw new FileNotFoundException("Cannot open file '$file'.");
}
$count = 0;
$sql = '';
while (!feof($handle)) {
$s = fgets($handle);
$sql .= $s;
if (substr(rtrim($s), -1) === ';') {
$connection->query($sql); // native query without logging
$sql = '';
$count++;
}
}
if (trim($sql) !== '') {
$connection->query($sql);
$count++;
}
fclose($handle);
return $count;
}
