/**
* @throws WrongArgumentException
* @return DBSchema
**/
public function addTable(DBTable $table)
{
$name = $table->getName();
Assert::isFalse(isset($this->tables[$name]), "table '{$name}' already exist");
$this->tables[$table->getName()] = $table;
$this->order[] = $name;
return $this;
}
/**
* Adds the DBTable object to the schema
*
* @param DBTable $table table to add
* @throws DuplicationException thrown when another DBTable with the same name already added
* @return DBSchema itself
*/
function addTable(DBTable $table)
{
$name = $table->getName();
if (isset($this->tables[$name])) {
throw new DuplicationException('table', $name);
}
$this->tables[$name] = $table;
return $this;
}
public static function call($name, $clear = true)
{
$tab = new $name();
$tabl = $tab->table;
//
if ($clear) {
$tabs = new DBTable($tabl);
$tabs->clear();
}
//
$tab->run();
}
public function getTable($name)
{
$this->db = new Database(DB_SERVER, DB_USER, DB_PASS, $this->data_base_name);
$this->db->connect();
$sql = 'DESC ' . $name;
$rows = $this->db->fetch_all_array($sql);
$this->db->close();
$table = new DBTable($name);
foreach ($rows as $rowData) {
$table->AddColumn(new DBColumn($rowData));
}
return $table;
}
/**
* @param array $fields fields of referencing table that reference primary another table
* @param DBTable $referencedTable object that represents referenced table
* @param AssociationBreakAction $associationBreakAction action which is performed on reference break
*/
function __construct(array $fields, DBTable $referencedTable, AssociationBreakAction $associationBreakAction)
{
foreach ($referencedTable->getConstraints() as $constraint) {
if ($constraint instanceof DBPrimaryKeyConstraint) {
$pkFields = $constraint->getFields();
Assert::isTrue(sizeof($pkFields) == sizeof($fields), 'foreign key (%s) should have the same number of columns as %s`s table primary key (%s)', join(', ', $fields), $referencedTable->getName(), join(', ', $pkFields));
$this->fields = new SqlFieldArray($fields);
$this->pkFields = new SqlFieldArray($pkFields);
$this->referencedTable = $referencedTable;
$this->associationBreakAction = $associationBreakAction;
return;
}
}
Assert::isUnreachable('referenced table `%s` MUST contain DBPrimaryKeyConstraint', $referencedTable->getName());
}
public function loadRelatedRow($table, $field = '')
{
if (!$field) {
$field = $table . '_id';
}
return DBTable::get($table)->loadRow($this->get($field));
}
private function importConstraints(OrmProperty $property)
{
$name = $this->dbTable->getName() . '_' . $property->getName();
$fields = $property->getFields();
if ($property->isIdentifier()) {
$this->dbTable->addConstraint(new DBPrimaryKeyConstraint($name . '_pk', $this->dbTable, $fields));
} else {
if ($property->isUnique()) {
$this->dbTable->addConstraint(new DBUniqueConstraint($name . '_uq', $this->dbTable, $fields));
}
}
$type = $property->getType();
if ($type instanceof AssociationPropertyType) {
$this->dbTable->addConstraint(new DBOneToOneConstraint($name . '_fk', $this->dbTable, $fields, $this->dbSchema->getTable($property->getType()->getContainer()->getTable()), $property->getType()->getAssociationBreakAction()));
} else {
if ($type instanceof CompositePropertyType) {
foreach ($type->getProperties($property) as $_property) {
$this->importConstraints($_property);
}
}
}
if ($property->isQueryable()) {
$this->dbTable->addIndex(new DBIndex($name . '_idx', $this->dbTable, $fields));
}
}
function User()
{
global $sql;
$this->cookie_expire = time() + 60 * 60 * 24 * 30;
//Will expire in 30 days
parent::__construct("User");
if (isset($_SESSION['user_id']) and isset($_SESSION['user_name'])) {
$this->setCurrentUser($_SESSION['user_id'], $_SESSION['user_name']);
return;
//User logged in already.
}
//This is a User who have enabled the 'Remember me' Option - so there is a cookie in the users system
if (isset($_COOKIE['username']) and $_COOKIE['username'] and isset($_COOKIE['password_hash'])) {
$user_details = $sql->getAssoc("SELECT id,name FROM User WHERE email='{$_COOKIE['username']}' AND MD5(CONCAT(password,'#c*2u!'))='{$_COOKIE['password_hash']}'");
if ($user_details) {
//If it is valid, store it in session
$this->setCurrentUser($user_details['id'], $_COOKIE['username'], $user_details['name']);
} else {
//The user details in the cookie is invalid - force a logout to clear cookie
$this->logout();
}
} else {
unset($_SESSION['user_id']);
unset($_SESSION['user_name']);
}
}
/**
* @return void
*/
private function makeConstraints(IDialect $dialect)
{
foreach ($this->table->getConstraints() as $constraint) {
$queryParts = array(StringUtils::DELIM_STANDART, "\t");
$queryParts[] = $constraint->toDialectString($dialect);
$this->commaSeparatedQueryParts[] = join('', $queryParts);
}
}
function getTableQuerySet(DBTable $table, $includeCreateTable = true)
{
$preQueries = array();
$postQueries = array();
foreach ($table->getColumns() as $column) {
$type = $column->getType();
if ($type instanceof DBType && $type->isGenerated()) {
$sqName = $this->getSequenceName($table->getName(), $column->getName());
$preQueries[] = new RawSqlQuery('CREATE SEQUENCE %s;', array(new SqlIdentifier($sqName)));
$postQueries[] = new RawSqlQuery('ALTER SEQUENCE %s OWNED BY %s;', array(new SqlIdentifier($sqName), new SqlPath($table->getName(), $column->getName())));
$postQueries[] = new RawSqlQuery('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;', array(new SqlIdentifier($table->getName()), new SqlIdentifier($column->getName()), new SqlFunction('nextval', new SqlValue($sqName))));
}
}
foreach ($table->getConstraints() as $constraint) {
$postQueries[] = new CreateConstraintQuery($table, $constraint);
$columns = array();
// create indexes
foreach ($constraint->getIndexableFields() as $field) {
$columns[] = $this->quoteIdentifier($field);
}
if (!empty($columns)) {
$postQueries[] = new RawSqlQuery('CREATE INDEX %s ON %s (' . join($columns) . ');', array(new SqlIdentifier($constraint->getName() . '_idx'), new SqlIdentifier($table->getName())));
}
}
if ($includeCreateTable) {
$preQueries[] = new CreateTableQuery($table);
}
return array_merge($preQueries, $postQueries);
}
/**
* Create XML map
*
* @param rdbms.DBAdapter and adapter
* @param string database
* @return rdbms.util.DBConstraintXmlGenerator object
*/
public static function createFromDatabase($adapter, $database)
{
$g = new self();
$g->doc->root()->setAttribute('created_at', date('r'));
$g->doc->root()->setAttribute('created_by', System::getProperty('user.name'));
$g->doc->root()->addChild(new Node('database', NULL, array('database' => $database)));
$g->tables = DBTable::getByDatabase($adapter, $database);
return $g;
}
/**
* Get table by name
*
* @param string table
* @param string database default NULL if omitted, uses current database
* @return rdbms.DBTable a DBTable object
*/
public function getTable($table, $database = NULL)
{
$t = new DBTable($table);
$q = $this->conn->query("Select\n column_name,\n udt_name,\n column_default,\n data_type,\n numeric_precision,\n numeric_scale,\n datetime_precision,\n character_maximum_length,\n is_nullable\n from\n information_schema.columns\n where\n table_schema='public'\n and table_catalog=%s\n and table_name=%s", $database, $table);
while ($record = $q->next()) {
$t->addAttribute(new DBTableAttribute($record['column_name'], $this->map[$record['udt_name']], strpos($record['column_default'], 'nextval(') === 0, $record['is_nullable'] != 'NO', 0, $record['numeric_precision'], $record['numeric_scale']));
}
$q = $this->conn->query("Select\n t.constraint_name as name,\n k.column_name as column\n from\n information_schema.table_constraints as t JOIN\n information_schema.key_column_usage as k on (k.constraint_name = t.constraint_name)\n where\n 'PRIMARY KEY' = t.constraint_type\n and t.table_catalog = %s\n and t.table_name = %s", $database, $table);
$key = NULL;
while ($record = $q->next()) {
if ($record['name'] != $key) {
$index = $t->addIndex(new DBIndex($record['name'], array()));
$key = $record['name'];
}
$index->unique = TRUE;
$index->primary = TRUE;
$index->keys[] = $record['column'];
}
$q = $this->conn->query("Select\n t.constraint_name as name,\n k.column_name as column\n from\n information_schema.table_constraints as t JOIN\n information_schema.key_column_usage as k on (k.constraint_name = t.constraint_name)\n where\n 'UNIQUE' = t.constraint_type\n and t.table_catalog = %s\n and t.table_name = %s", $database, $table);
$key = NULL;
while ($record = $q->next()) {
if ($record['name'] != $key) {
$index = $t->addIndex(new DBIndex($record['name'], array()));
$key = $record['name'];
}
$index->unique = TRUE;
$index->primary = FALSE;
$index->keys[] = $record['column'];
}
$q = $this->conn->query("Select\n t.constraint_name as name,\n t.table_catalog as db,\n t.table_name as tbl,\n k.column_name as col,\n r.unique_constraint_catalog as source_db,\n tt.table_name as source_tbl,\n tk.column_name as source_col\n from\n information_schema.table_constraints as t\n JOIN information_schema.referential_constraints as r on (t.constraint_name = r.constraint_name)\n JOIN information_schema.key_column_usage as k on (k.constraint_name = t.constraint_name)\n JOIN information_schema.table_constraints as tt on (r.unique_constraint_name = tt.constraint_name)\n JOIN information_schema.key_column_usage as tk on (\n r.unique_constraint_name = tk.constraint_name\n and k.ordinal_position = tk.ordinal_position\n )\n where\n t.constraint_type = 'FOREIGN KEY'\n and t.table_catalog = %s\n and t.table_name = %s", $database, $table);
$key = NULL;
while ($record = $q->next()) {
if ($record['name'] != $key) {
$constraint = new DBForeignKeyConstraint();
$t->addForeignKeyConstraint($constraint);
$key = $record['name'];
}
$constraint->addKey($record['col'], $record['source_col']);
$constraint->setName($record['name']);
$constraint->setSource($record['source_tbl']);
}
return $t;
}
function getExtraTableQueries(DBTable $table)
{
$queries = array();
foreach ($table->getColumns() as $column) {
$type = $column->getType();
if ($type instanceof DBType && $type->isGenerated()) {
$sqName = $this->getSequenceName($table->getName(), $column->getName());
$queries[] = new RawSqlQuery('CREATE SEQUENCE %s;', array(new SqlIdentifier($sqName)));
$queries[] = new RawSqlQuery('ALTER SEQUENCE %s OWNED BY %s;', array(new SqlIdentifier($sqName), new SqlPath($table->getName(), $column->getName())));
$queries[] = new RawSqlQuery('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;', array(new SqlIdentifier($table->getName()), new SqlIdentifier($column->getName()), new SqlFunction('nextval', new SqlValue($sqName))));
}
}
foreach ($table->getConstraints() as $constraint) {
if ($constraint instanceof DBOneToOneConstraint) {
// create an explicit index for that
$queries[] = new CreateIndexQuery($table, new DBIndex($constraint->getFields()));
}
}
return $queries;
}
public function delete($args)
{
$users_id = get($args, 'users_id');
if ($users_id) {
if ($user = DBTable::get('users')->loadRow($users_id)) {
DBTable::deleteRow($user);
return data::success($user->export());
}
return data::error('invalid user id');
}
return data::error('user id required');
}
function __construct($user_id = 0)
{
global $sql;
parent::__construct('users');
if ($user_id) {
$this->user = $this->find($user_id);
if ($this->user) {
$this->user['name'] = $this->user['first_name'] . ' ' . $this->user['last_name'];
} else {
$this->_error("Can't find user with ID {$user_id}.");
}
}
}
/**
* Get table by name
*
* @param string table
* @param string database default NULL if omitted, uses current database
* @return rdbms.DBTable a DBTable object
*/
public function getTable($table, $database = NULL)
{
$t = new DBTable($table);
$q = $this->conn->query('describe %c', $this->qualifiedTablename($table, $database));
while ($record = $q->next()) {
$t->addAttribute(self::tableAttributeFrom($record));
}
// Get keys
// +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
// | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
// +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
// | contract | 0 | PRIMARY | 1 | contract_id | A | 6 | NULL | NULL | |
// | contract | 0 | contract_id_2 | 1 | contract_id | A | 6 | NULL | NULL | |
// | contract | 1 | contract_id | 1 | contract_id | A | 6 | NULL | NULL | |
// | contract | 1 | contract_id | 2 | user_id | A | 6 | NULL | NULL | |
// +----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
$q = $this->conn->query('show keys from %c', $this->qualifiedTablename($table, $database));
$key = NULL;
while ($record = $q->next()) {
if ($record['Key_name'] != $key) {
$index = $t->addIndex(new DBIndex($record['Key_name'], array()));
$key = $record['Key_name'];
}
$index->unique = '0' == $record['Non_unique'];
$index->primary = 'PRIMARY' == $record['Key_name'];
$index->keys[] = $record['Column_name'];
}
// Get foreign key constraints
// in mysql the only way is to parse the creat statement
$createTableString = $this->conn->query('show create table %c', $this->qualifiedTablename($table, $database))->next('Create Table');
for ($i = 0; $i < strlen($createTableString); $i++) {
switch ($createTableString[$i]) {
case '`':
$this->parseQuoteString($createTableString, $i, '`');
break;
case '"':
$this->parseQuoteString($createTableString, $i, '"');
break;
case '(':
$tableConstraints = $this->filterConstraints($this->extractParams($this->parseBracerString($createTableString, $i)));
foreach ($tableConstraints as $tableConstraint) {
if (strstr($tableConstraint, 'FOREIGN KEY') === FALSE) {
continue;
}
$t->addForeignKeyConstraint($this->parseForeignKeyString($tableConstraint));
}
break;
}
}
return $t;
}
public function testDiff()
{
$table1 = DBTable::fromSQL('CREATE TABLE test (id int,name varchar(255))');
$table2 = DBTable::fromSQL('CREATE TABLE test (id int,name varchar(255))');
$table3 = DBTable::fromSQL('CREATE TABLE test2 (id int,name varchar(255))');
$table4 = DBTable::fromSQL('CREATE TABLE test3 (id int,name varchar(255))');
$table5 = DBTable::fromSQL('CREATE TABLE test (id char(4),name varchar(255))');
$table6 = DBTable::fromSQL('CREATE TABLE test (no int,name varchar(255))');
$this->assertEquals('', $table1->diff($table2));
$this->assertEquals('ALTER TABLE test RENAME test2', $table1->diff($table3));
$this->assertEquals('ALTER TABLE test RENAME test3', $table1->diff($table4));
$this->assertEquals('ALTER TABLE test CHANGE id id char(4)', $table1->diff($table5));
$this->assertEquals('ALTER TABLE test ADD no int,DROP id', $table1->diff($table6));
}
public function logout($args)
{
extract(extractable(array('users_id', 'key'), $args));
$users_id = intval($users_id);
if ($users_id) {
if ($session = DBTable::get('session')->loadRowsWhere(array('key' => $key))) {
$session = $session[0];
$session->logged_in = 0;
$session->save();
return data::success($session->export());
}
return data::error('unable to locate session');
}
return data::error('user_id required');
}
/**
* @return void
*/
private function importProperty()
{
if (!sizeof($this->ormProperty->getFields())) {
// columnless properties are skipped
return;
}
$columns = array_combine($this->ormProperty->getFields(), $this->ormProperty->getType()->getSqlTypes());
$dbColumns = array();
foreach ($columns as $name => $dbType) {
$dbColumns[$name] = new DBColumn($name, $dbType);
}
$fields = array_keys($dbColumns);
$this->dbTable->addColumns($dbColumns);
if ($this->ormProperty->getType() instanceof AssociationPropertyType) {
$this->dbTable->addConstraint(new DBOneToOneConstraint($fields, $this->dbSchema->getTable($this->ormProperty->getType()->getContainer()->getTable()), $this->ormProperty->getType()->getAssociationBreakAction()));
}
if ($this->ormProperty->isIdentifier()) {
$this->dbTable->addConstraint(new DBPrimaryKeyConstraint($fields));
} else {
if ($this->ormProperty->isUnique()) {
$this->dbTable->addConstraint(new DBUniqueConstraint($fields));
}
}
}
private function getDBTable()
{
global $GLOBAL_DATATABLES;
return DBTable::getTable($GLOBAL_DATATABLES[get_class($this)]);
}
/**
* @return MetaConfiguration
**/
public function buildSchemaChanges()
{
$out = $this->getOutput();
$out->newLine()->infoLine('Suggested DB-schema changes: ');
require ONPHP_META_AUTO_DIR . 'schema.php';
foreach ($this->classes as $class) {
if ($class->getTypeId() == MetaClassType::CLASS_ABSTRACT || $class->getPattern() instanceof EnumerationClassPattern) {
continue;
}
try {
$target = $schema->getTableByName($class->getTableName());
} catch (MissingElementException $e) {
// dropped or tableless
continue;
}
try {
$db = DBPool::me()->getLink($class->getSourceLink());
} catch (BaseException $e) {
$out->errorLine('Can not connect using source link in \'' . $class->getName() . '\' class, skipping this step.');
break;
}
try {
$source = $db->getTableInfo($class->getTableName());
} catch (UnsupportedMethodException $e) {
$out->errorLine(get_class($db) . ' does not support tables introspection yet.', true);
break;
} catch (ObjectNotFoundException $e) {
$out->errorLine("table '{$class->getTableName()}' not found, skipping.");
continue;
}
$diff = DBTable::findDifferences($db->getDialect(), $source, $target);
if ($diff) {
foreach ($diff as $line) {
$out->warningLine($line);
}
$out->newLine();
}
}
return $this;
}
/**
* @return void
*/
private function makeColumns(IDialect $dialect)
{
foreach ($this->table->getColumns() as $column) {
$this->makeColumn($column, $dialect);
}
}
<?php
/*****************************************************************************
* Copyright (C) 2006-2007, onPHP's MetaConfiguration Builder. *
* Generated by onPHP-0.9.128 at 2007-03-31 16:25:48 *
* This file is autogenerated - do not edit. *
*****************************************************************************/
$schema = new DBSchema();
$schema->addTable(DBTable::create('administrator')->addColumn(DBColumn::create(DataType::create(DataType::BIGINT)->setNull(false), 'id')->setPrimaryKey(true)->setAutoincrement(true))->addColumn(DBColumn::create(DataType::create(DataType::VARCHAR)->setNull(false)->setSize(64), 'username'))->addColumn(DBColumn::create(DataType::create(DataType::VARCHAR)->setNull(false)->setSize(40), 'password')));
function __construct()
{
parent::__construct();
$this->_tablename = 'post_reply';
}
public function getTableInfo($table)
{
static $types = array('tinyint' => DataType::SMALLINT, 'smallint' => DataType::SMALLINT, 'int' => DataType::INTEGER, 'mediumint' => DataType::INTEGER, 'bigint' => DataType::BIGINT, 'double' => DataType::DOUBLE, 'decimal' => DataType::NUMERIC, 'char' => DataType::CHAR, 'varchar' => DataType::VARCHAR, 'text' => DataType::TEXT, 'tinytext' => DataType::TEXT, 'mediumtext' => DataType::TEXT, 'date' => DataType::DATE, 'time' => DataType::TIME, 'timestamp' => DataType::TIMESTAMP, 'datetime' => DataType::TIMESTAMP, 'set' => null, 'enum' => null, 'year' => null);
try {
$result = $this->queryRaw('SHOW COLUMNS FROM ' . $table);
} catch (BaseException $e) {
throw new ObjectNotFoundException("unknown table '{$table}'");
}
$table = new DBTable($table);
while ($row = mysql_fetch_assoc($result)) {
$name = strtolower($row['Field']);
$matches = array();
$info = array('type' => null, 'extra' => null);
if (preg_match('~(\\w+)(\\((\\d+?)\\)){0,1}\\s*(\\w*)~', strtolower($row['Type']), $matches)) {
$info['type'] = $matches[1];
$info['size'] = $matches[3];
$info['extra'] = $matches[4];
}
Assert::isTrue(array_key_exists($info['type'], $types), 'unknown type "' . $types[$info['type']] . '" found in column "' . $name . '"');
if (empty($types[$info['type']])) {
continue;
}
$column = DBColumn::create(DataType::create($types[$info['type']])->setUnsigned(strtolower($info['extra']) == 'unsigned')->setNull(strtolower($row['Null']) == 'yes'), $name)->setAutoincrement(strtolower($row['Extra']) == 'auto_increment')->setPrimaryKey(strtolower($row['Key']) == 'pri');
if ($row['Default']) {
$column->setDefault($row['Default']);
}
$table->addColumn($column);
}
return $table;
}
function __construct()
{
parent::__construct();
$this->_tablename = 'file';
}
public function pagination($RowsPerPage)
{
$table = new DBTable($this->table);
//
$dbtable = $table->paginate($RowsPerPage);
//
//Table::show($dbtable);
$data = array();
foreach ($dbtable->data as $key => $value) {
$row = new self($value[0], $this->table);
array_push($data, $row);
}
//params
$nbRows = $dbtable->nbRows;
//
$collection = new DataCollection($data, true, $dbtable->nbRows, $RowsPerPage, $dbtable->CurrentPage);
//
return $collection;
}
function __construct()
{
parent::__construct();
$this->_tablename = 'teacher';
}
/**
* @throws ObjectNotFoundException
* @return DBTable
**/
public function getTableInfo($table)
{
static $types = array('time' => DataType::TIME, 'date' => DataType::DATE, 'timestamp' => DataType::TIMESTAMP, 'bool' => DataType::BOOLEAN, 'int2' => DataType::SMALLINT, 'int4' => DataType::INTEGER, 'int8' => DataType::BIGINT, 'numeric' => DataType::NUMERIC, 'float4' => DataType::REAL, 'float8' => DataType::DOUBLE, 'varchar' => DataType::VARCHAR, 'bpchar' => DataType::CHAR, 'text' => DataType::TEXT, 'bytea' => DataType::BINARY, 'ip4' => DataType::IP, 'inet' => DataType::IP, 'ip4r' => DataType::IP_RANGE, 'tsvector' => null, 'ltree' => null, 'hstore' => null);
try {
$res = pg_meta_data($this->link, $table);
} catch (BaseException $e) {
throw new ObjectNotFoundException("unknown table '{$table}'");
}
$table = new DBTable($table);
foreach ($res as $name => $info) {
Assert::isTrue(array_key_exists($info['type'], $types), 'unknown type "' . $types[$info['type']] . '" found in column "' . $name . '"');
if (empty($types[$info['type']])) {
continue;
}
$column = new DBColumn(DataType::create($types[$info['type']])->setNull(!$info['not null']), $name);
$table->addColumn($column);
}
return $table;
}
public function delete($id)
{
parent::delete($id);
}