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