public function testPreset()
{
$prefixDb = new Database('test', '\\selective\\ORM\\Tests\\Mocks\\Driver', ['prefix' => 'testprefix_']);
$this->assertEquals(count($prefixDb->getTables()), 1);
$this->assertFalse($prefixDb->hasTable('Books'));
$this->assertTrue($prefixDb->hasTable('Test'));
$table = $prefixDb->{'Test'};
$this->assertInstanceOf('selective\\ORM\\Table', $table);
}
/**
* Get a Table object for the given name
* TODO table/column properties should not be public
* @param Database $database
* @param String $name
* @throws \Exception
* @return Table
*/
public function buildTable(Database $database, $name)
{
$objectInfo = $this->fetchAll("SELECT object_id FROM sys.objects WHERE type IN ('U ', 'V ') AND name = ?", [$name]);
if (isset($objectInfo[0]['object_id'])) {
$objectId = $objectInfo[0]['object_id'];
$columns = $this->fetchAll(<<<SQL
SELECT
\tcolumns.name,
\ttypes.name AS type,
\tcolumns.max_length AS length,
\tcolumns.is_nullable AS allowNull,
\tdefault_constraints.definition AS [default],
\tcolumns.is_identity AS isAutoIncrement,
\tCOALESCE(indexes.is_primary_key, 0) AS isPrimaryKey
FROM
\tsys.columns
\tINNER JOIN sys.types ON columns.user_type_id = types.user_type_id
\tLEFT JOIN sys.default_constraints
\t\tON columns.default_object_id = default_constraints.object_id
\t\tAND columns.object_id = default_constraints.parent_object_id
\tLEFT JOIN sys.index_columns
\t\tON index_columns.column_id = columns.column_id
\t\tAND index_columns.object_id = columns.object_id
\tLEFT JOIN sys.indexes
\t\tON indexes.index_id = index_columns.index_id
\t\tAND indexes.object_id = columns.object_id
\t\tAND indexes.is_primary_key = 1
WHERE
\tcolumns.object_id = ?
SQL
, [$objectId]);
$constraints = $this->fetchAll(<<<SQL
SELECT
\tforeign_keys.name constraintName,
\tlocalColumns.name AS localColumnName,
\tforeignTables.name AS foreignTableName,
\tforeignColumns.name AS foreignColumnName
FROM
\tsys.foreign_keys
\tINNER JOIN sys.foreign_key_columns ON foreign_keys.object_id = foreign_key_columns.constraint_object_id
\tINNER JOIN sys.objects AS foreignTables ON foreignTables.object_id = foreign_key_columns.referenced_object_id
\tINNER JOIN sys.columns AS localColumns
\t\tON localColumns.column_id = foreign_key_columns.parent_column_id
\t\tAND localColumns.object_id = foreign_key_columns.parent_object_id
\tINNER JOIN sys.columns AS foreignColumns
\t\tON foreignColumns.column_id = foreign_key_columns.referenced_column_id
\t\tAND foreignColumns.object_id = foreign_key_columns.referenced_object_id
WHERE
\tforeign_keys.parent_object_id = ?
ORDER BY
\tforeign_keys.parent_object_id,
\tforeign_keys.object_id
SQL
, [$objectId], null, 'constraintName');
$tableClass = $database->getClassMapper()->getClassForTable($name);
/** @var Table $table */
$table = new $tableClass($name, $database);
foreach ($columns as $ordinal => $columnInfo) {
$column = new Column($table);
$default = null;
if ($columnInfo['default'] !== null) {
// we need to parse the SQL default value
$defaultExpression = $this->getSQLExpressionForType($columnInfo['default'], $columnInfo['type'], '[default]');
$defaultResult = $this->fetchAll('SELECT ' . $defaultExpression);
$default = current(current($defaultResult));
}
$column->setName($columnInfo['name'])->setOrdinal($ordinal)->setType($columnInfo['type'])->setDefault($default)->setAllowNull((bool) $columnInfo['allowNull'])->setPrimaryKey((bool) $columnInfo['isPrimaryKey'])->setAutoIncrement((bool) $columnInfo['isAutoIncrement']);
if ($columnInfo['isPrimaryKey']) {
$table->primaryKeys[] = $columnInfo['name'];
}
$length = null;
switch ($columnInfo['type']) {
case 'text':
$length = 2147483647;
break;
case 'ntext':
$length = 1073741823;
break;
case 'varchar':
case 'nvarchar':
case 'char':
case 'nchar':
$length = $columnInfo['length'];
break;
}
$column->setLength($length);
$table->columns[$column->getName()] = $column;
}
// enumerate relationships
$offset = strlen($database->getPrefix());
foreach ($constraints as $constraintName => $mappings) {
$localColumns = [];
$relatedColumns = [];
$mapping = null;
foreach ($mappings as $mapping) {
$localColumns[] = $mapping['localColumnName'];
$relatedColumns[] = $mapping['foreignColumnName'];
if (!isset($table->foreignKeys[$mapping['localColumnName']])) {
// columns can have multiple foreign keys; we can only use one of them
$table->foreignKeys[$mapping['localColumnName']] = $constraintName;
}
}
$foreignTableName = substr($mapping['foreignTableName'], $offset);
if (!isset($table->relatedTables[$foreignTableName])) {
// tables can be related to another table multiple times; we can only use one of them
$table->relatedTables[$foreignTableName] = $constraintName;
}
$table->constraints[$constraintName] = ['localColumns' => $localColumns, 'relatedTable' => $foreignTableName, 'relatedColumns' => $relatedColumns];
}
return $table;
} else {
throw new \Exception('Could not find table ' . $name);
}
}
/**
* @return Database
*/
protected function getDB()
{
$parameters = $this->getDriverParameters();
$pdo = new \PDO("sqlsrv:Server={$parameters['host']}", $parameters['username'], $parameters['password']);
$stmt = $pdo->prepare("IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = ?) CREATE DATABASE [{$GLOBALS['test_dbname']}]");
$stmt->bindParam(1, $GLOBALS['test_dbname']);
$stmt->execute();
$pdo = null;
$db = new Database($GLOBALS['test_dbname'], $this->getDriverClassName(), $parameters);
$db->getDriver()->executeUpdate("IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Books') DROP TABLE Books");
$db->getDriver()->executeUpdate("IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Authors') DROP TABLE Authors");
$db->getDriver()->executeUpdate("IF EXISTS (SELECT * FROM sys.objects WHERE name = 'testprefix_Test') DROP TABLE testprefix_Test");
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Authors (
idAuthor INTEGER PRIMARY KEY NOT NULL IDENTITY(1,1),
name NVARCHAR(100)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Books (
idBook INTEGER PRIMARY KEY NOT NULL IDENTITY(1,1),
title NVARCHAR(200) NOT NULL,
idAuthor INTEGER NOT NULL,
isbn NVARCHAR(50) NOT NULL,
description NVARCHAR(MAX),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
FOREIGN KEY(idAuthor) REFERENCES Authors(idAuthor)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE testprefix_Test (
test int DEFAULT NULL
)
SQL
);
$db->getDriver()->executeUpdate("DELETE FROM Books");
$db->getDriver()->executeUpdate("DELETE FROM Authors");
$db->getDriver()->executeUpdate("INSERT INTO Authors (name) VALUES ('Author 1')");
$db->getDriver()->executeUpdate("INSERT INTO Authors (name) VALUES ('Author 2')");
$db->getDriver()->executeUpdate("INSERT INTO Books (title, idAuthor, isbn, description) VALUES ('My First Book', 1, '12345-6789', 'It wasn''t very good')");
$db->getDriver()->executeUpdate("INSERT INTO Books (title, idAuthor, isbn, description) VALUES ('My Second Book', 1, '12345-6790', 'It wasn''t very good either')");
$db->getDriver()->executeUpdate("INSERT INTO Books (title, idAuthor, isbn, description) VALUES ('My First Book', 2, '12345-6790', 'It was OK')");
return $db;
}
/**
* Get a Table object for the given name
* TODO table/column properties should not be public
* @param Database $database
* @param string $name
* @throws \Exception
* @return Table
*/
public function buildTable(Database $database, $name)
{
$createTableInfo = $this->fetchAll("SHOW CREATE TABLE `{$database->getPrefix()}{$name}`");
if (!isset($createTableInfo[0]['Create Table'])) {
throw new \Exception('Could not find table ' . $name);
}
$createTableSql = $createTableInfo[0]['Create Table'];
$columns = [];
$primaryKeys = [];
$constraints = [];
$lowerToRealCaseTableNames = [];
$actualTableNames = [];
foreach ($this->getTables($database) as $tableName) {
$actualTableNames[$tableName] = true;
$lowerToRealCaseTableNames[strtolower($tableName)] = $tableName;
}
// parse columns
if (preg_match_all(self::CREATE_TABLE_SQL_COLUMNS_REGEX, $createTableSql, $columns, PREG_SET_ORDER)) {
$tableClass = $database->getClassMapper()->getClassForTable($name);
/** @var Table $table */
$table = new $tableClass($name, $database);
foreach ($columns as $ordinal => $columnInfo) {
$column = new Column($table);
$default = null;
if (isset($columnInfo['default']) && $columnInfo['default'] !== 'NULL') {
if ($columnInfo['default'] === '') {
$default = '';
} else {
if ($columnInfo['default'] !== 'CURRENT_TIMESTAMP' && $columnInfo['default'] !== 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP') {
// we need to parse the SQL default value
$defaultResult = $this->fetchAll('SELECT ' . $columnInfo['default']);
$default = current(current($defaultResult));
}
}
}
$column->setName($columnInfo['name'])->setOrdinal($ordinal)->setType(strtolower($columnInfo['type']))->setDefault($default)->setAllowNull(!isset($columnInfo['allowNull']) || $columnInfo['allowNull'] === 'NULL')->setAutoIncrement(!empty($columnInfo['autoIncrement']));
if ($column->getType() == 'set' || $column->getType() == 'enum') {
// we need to parse the SQL options
$optionsResult = $this->fetchAll('SELECT ' . $columnInfo['length']);
$options = [];
$i = 0;
foreach (current($optionsResult) as $option) {
$options[$column->getType() == 'set' ? pow(2, $i) : $i] = $option;
$i++;
}
$column->setOptions($options);
} else {
$column->setLength(isset($columnInfo['length']) && $columnInfo['length'] !== '' ? $columnInfo['length'] : null);
}
$table->columns[$column->getName()] = $column;
}
// parse primary keys
preg_match(self::CREATE_TABLE_SQL_PRIMARY_KEY_REGEX, $createTableSql, $primaryKeys);
$primaryKeys = explode('`,`', trim($primaryKeys[1], '`'));
foreach ($primaryKeys as $primaryKey) {
$table->primaryKeys[] = $primaryKey;
$table->columns[$primaryKey]->setPrimaryKey(true);
}
// parse relationships
preg_match_all(self::CREATE_TABLE_SQL_CONSTRAINT_REGEX, $createTableSql, $constraints, PREG_SET_ORDER);
$offset = strlen($database->getPrefix());
foreach ($constraints as $constraint) {
$localColumns = explode('`, `', trim($constraint['localColumns'], '`'));
$relatedColumns = explode('`, `', trim($constraint['relatedColumns'], '`'));
foreach ($localColumns as $localColumn) {
if (!isset($table->foreignKeys[$localColumn])) {
// columns can have multiple foreign keys; we can only use one of them
$table->foreignKeys[$localColumn] = $constraint['name'];
}
}
$foreignTableName = substr($constraint['relatedTable'], $offset);
// workaround for http://bugs.mysql.com/bug.php?id=6555
// map lower case table names to actual case table names
if (!isset($actualTableNames[$foreignTableName]) && isset($lowerToRealCaseTableNames[$foreignTableName])) {
$foreignTableName = $lowerToRealCaseTableNames[$foreignTableName];
}
if (!isset($table->relatedTables[$foreignTableName])) {
// tables can be related to another table multiple times; we can only use one of them
$table->relatedTables[$foreignTableName] = $constraint['name'];
}
$table->constraints[$constraint['name']] = ['localColumns' => $localColumns, 'relatedTable' => $foreignTableName, 'relatedColumns' => $relatedColumns];
}
return $table;
} else {
throw new \Exception('Could not parse table definition');
}
}
/**
* @return Database
*/
protected function getDB()
{
$db = new Database($GLOBALS['test_dbname'], $this->getDriverClassName(), $this->getDriverParameters());
$db->getDriver()->executeUpdate('DROP TABLE IF EXISTS Books');
$db->getDriver()->executeUpdate('DROP TABLE IF EXISTS Authors');
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE IF NOT EXISTS Authors (
idAuthor INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(100)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE Books (
idBook INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
idAuthor INTEGER NOT NULL,
isbn VARCHAR(50) NOT NULL,
description TEXT,
dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(idAuthor) REFERENCES Authors(idAuthor)
)
SQL
);
$db->getDriver()->executeUpdate(<<<SQL
CREATE TABLE IF NOT EXISTS testprefix_Test (
test int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL
);
$db->getDriver()->executeUpdate("DELETE FROM Books");
$db->getDriver()->executeUpdate("DELETE FROM Authors");
$db->getDriver()->executeUpdate("INSERT INTO Authors (idAuthor, name) VALUES (1, 'Author 1')");
$db->getDriver()->executeUpdate("INSERT INTO Authors (idAuthor, name) VALUES (2, 'Author 2')");
$db->getDriver()->executeUpdate("INSERT INTO Books (idBook, title, idAuthor, isbn, description) VALUES (1, 'My First Book', 1, '12345-6789', 'It wasn''t very good')");
$db->getDriver()->executeUpdate("INSERT INTO Books (idBook, title, idAuthor, isbn, description) VALUES (2, 'My Second Book', 1, '12345-6790', 'It wasn''t very good either')");
$db->getDriver()->executeUpdate("INSERT INTO Books (idBook, title, idAuthor, isbn, description) VALUES (3, 'My First Book', 2, '12345-6790', 'It was OK')");
return $db;
}
/**
* Get a Table object for the given name
* TODO table/column properties should not be public
* @param Database $database
* @param String $name
* @throws \Exception
* @return Table
*/
public function buildTable(Database $database, $name)
{
$columns = $this->fetchAll("PRAGMA table_info(`{$database->getPrefix()}{$name}`)");
if ($columns) {
$tableClass = $database->getClassMapper()->getClassForTable($name);
/** @var Table $table */
$table = new $tableClass($name, $database);
foreach ($columns as $ordinal => $columnInfo) {
$column = new Column($table);
$column->setName($columnInfo['name'])->setOrdinal($ordinal)->setType(strtolower($columnInfo['type']))->setDefault($columnInfo['dflt_value'])->setAllowNull($columnInfo['notnull'] === '0')->setPrimaryKey($columnInfo['pk'] === '1')->setAutoIncrement(strpos($columnInfo['type'], 'INT') === 0 && $columnInfo['pk']);
if ($columnInfo['pk']) {
$table->primaryKeys[] = $columnInfo['name'];
}
$table->columns[$column->getName()] = $column;
}
// enumerate relationships
$offset = strlen($database->getPrefix());
$foreignKeys = $this->fetchAll("PRAGMA foreign_key_list(`{$database->getPrefix()}{$name}`)", [], null, 'id');
foreach ($foreignKeys as $mappings) {
$localColumns = [];
$relatedColumns = [];
$constraintName = null;
$mapping = null;
foreach ($mappings as $mapping) {
$localColumns[] = $mapping['from'];
$relatedColumns[] = $mapping['to'];
$constraintName = 'fk_' . $mapping['id'];
if (!isset($table->foreignKeys[$mapping['from']])) {
// columns can have multiple foreign keys; we can only use one of them
$table->foreignKeys[$mapping['from']] = $constraintName;
}
}
$foreignTableName = substr($mapping['table'], $offset);
if (!isset($table->relatedTables[$foreignTableName])) {
// tables can be related to another table multiple times; we can only use one of them
$table->relatedTables[$foreignTableName] = $constraintName;
}
$table->constraints[$constraintName] = ['localColumns' => $localColumns, 'relatedTable' => $foreignTableName, 'relatedColumns' => $relatedColumns];
}
return $table;
} else {
throw new \Exception('Could not parse table definition');
}
}