/**
* Returns the table skeleton.
*
* This is the basic table with ID, GID and parent fields.
*
* @return Table the table skeleton
*/
protected function getTableSkeleton()
{
$table = new Table($this->tableName, '');
$table->addField(new Field('id', new Type(Field::TYPE_INT, 20), '', false, true, 'The local ID'));
$table->addField(new Field('gid', new Type(Field::TYPE_VARCHAR, 32), '-1', false, false, 'The global ID (GID)'));
$table->addField(new Field('parent', new Type(Field::TYPE_VARCHAR, 32), '-1', false, false, 'The parent GID'));
$table->setPrimaryKey(new PrimaryKey($this->tableName, array('id')));
$table->addIndex(new Index('gid', $this->tableName, true, array('gid')));
$table->addIndex(new Index('parent', $this->tableName, false, array('parent')));
return $table;
}
/**
* Creates the table.
*
* @param string $name the table name
*/
private function createTable($name)
{
$table = new Table($name);
$table->addField(new Field('id', new Type(Field::TYPE_VARCHAR, 32), '', false, false));
$table->addField(new Field('username', new Type(Field::TYPE_VARCHAR, 255), null, true, false));
$table->addField(new Field('fullName', Field::TYPE_TEXT, null, true, false));
$table->addField(new Field('email', new Type(Field::TYPE_VARCHAR, 255), null, true, false));
$table->addField(new Field('birthdate', Field::TYPE_DATE, null, true, false));
$table->addField(new Field('gender', new Type(Field::TYPE_VARCHAR, 255), '', true, false));
$table->addField(new Field('timezoneOffset', new Type(Field::TYPE_SMALLINT, 4), 0, false, false));
$table->addField(new Field('locale', new Type(Field::TYPE_VARCHAR, 10), 'en-US', false, false));
$table->addField(new Field('lastLogin', new Type(Field::TYPE_INT, 12), -1, false, false));
$table->addField(new Field('secret', Field::TYPE_TEXT, '', false, false));
$table->setPrimaryKey(new PrimaryKey($name, array('id')));
$table->addIndex(new Index('username', $name, true, array('username')));
$table->addIndex(new Index('email', $name, true, array('email')));
$this->db->createTable($table);
}
/**
* Creates the table.
*/
private function createTable()
{
$table = new Table($this->table);
$table->addField(new Field('role', new Type(Field::TYPE_VARCHAR, 255), '', false, false));
$table->addField(new Field('page', new Type(Field::TYPE_VARCHAR, 255), '', false, false));
$table->addField(new Field('task', new Type(Field::TYPE_VARCHAR, 255), '', false, false));
$table->setPrimaryKey(new PrimaryKey($this->table, array('role', 'page', 'task')));
$this->db->createTable($table);
}
/**
* Creates the table.
*
* @param string $name the table name
*/
private function createTable($name)
{
$table = new Table($name);
$table->addField(new Field('idx', Field::TYPE_INT, '', false, true));
$table->addField(new Field('id', new Type(Field::TYPE_VARCHAR, 32), '', false, false));
$table->addField(new Field('role', Field::TYPE_TEXT, '', false, false));
$table->setPrimaryKey(new PrimaryKey($name, array('idx')));
$this->db->createTable($table);
}
/**
* Creates the table.
*
* @param string $name the table name
*/
private function createTable()
{
$name = $this->table;
$table = new Table($name, '');
$table->addField(new Field('idx', new Type(Field::TYPE_VARCHAR, 255), '', false, false));
$table->addField(new Field('data', new Type(Field::TYPE_BLOB), '', false, false));
if ($this->useTimestamps) {
$table->addField(new Field('ts', new Type(Field::TYPE_INT, 10), '', false, false));
}
$table->setPrimaryKey(new PrimaryKey($name, array('idx')));
$this->db->createTable($table);
}
public function getTable($table)
{
$query = 'SELECT
t.TABLE_COMMENT AS tableComment,
c.COLUMN_NAME AS columnName,
c.COLUMN_TYPE AS type,
c.COLUMN_DEFAULT AS columnDefault,
c.IS_NULLABLE AS columnNullable,
c.EXTRA AS extra,
c.COLUMN_COMMENT AS columnComment,
k.CONSTRAINT_NAME AS constraintName,
k.REFERENCED_TABLE_NAME AS referencedTable,
k.REFERENCED_COLUMN_NAME AS referencedColumn,
k.UPDATE_RULE AS onUpdate,
k.DELETE_RULE AS onDelete,
NULL AS indexName,
NULL AS indexColumn,
NULL indexNonUnique
FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
(
SELECT
ku.COLUMN_NAME,
ku.CONSTRAINT_NAME,
ku.REFERENCED_TABLE_NAME,
ku.REFERENCED_COLUMN_NAME,
r.UPDATE_RULE,
r.DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS AS r JOIN information_schema.KEY_COLUMN_USAGE AS ku
ON ku.CONSTRAINT_NAME = r.CONSTRAINT_NAME AND ku.TABLE_NAME = r.TABLE_NAME
WHERE ku.TABLE_NAME=:table AND r.CONSTRAINT_SCHEMA = :id AND ku.CONSTRAINT_SCHEMA = :id
) AS k
ON c.COLUMN_NAME = k.COLUMN_NAME
WHERE t.TABLE_SCHEMA = :id AND c.TABLE_SCHEMA = :id AND t.TABLE_NAME = :table AND c.TABLE_NAME = :table
UNION
SELECT
t.TABLE_COMMENT AS tableComment,
NULL AS columnName,
NULL AS type,
NULL AS columnDefault,
NULL AS columnNullable,
NULL AS extra,
NULL AS columnComment,
NULL AS constraintName,
NULL AS referencedTable,
NULL AS referencedColumn,
NULL AS onUpdate,
NULL AS onDelete,
st.INDEX_NAME AS indexName,
st.COLUMN_NAME AS indexColumn,
st.NON_UNIQUE AS indexNonUnique
FROM information_schema.TABLES AS t JOIN information_schema.STATISTICS AS st ON t.TABLE_SCHEMA= st.TABLE_SCHEMA AND t.TABLE_NAME = st.TABLE_NAME
WHERE t.TABLE_SCHEMA = :id AND st.TABLE_SCHEMA = :id AND t.TABLE_NAME = :table AND st.TABLE_NAME = :table';
$fields = $this->pdoDriver->executeQuery($query, array('id' => $this->pdoDriver->getID(), 'table' => $this->addPrefix($table)), true);
if (count($fields) == 0) {
return null;
}
$res = new Table($table, $fields[0]['tableComment']);
// initialize constraints
$currentForeignKeyName = null;
$currentForeignKey = null;
$currentIndexName = null;
$currentIndex = null;
foreach ($fields as $field) {
$name = $field['columnName'];
// new index
if ($field['indexName'] !== $currentIndexName) {
// add old index to table
if ($currentIndex != null) {
if ($currentIndexName === 'PRIMARY') {
$res->setPrimaryKey($currentIndex);
} else {
$res->addConstraint($currentIndex);
}
}
// create a new index
$currentIndexName = $field['indexName'];
if ($currentIndexName === 'PRIMARY') {
$currentIndex = new PrimaryKey($table);
} elseif ($currentIndexName !== null) {
if ($field['indexNonUnique'] === '0') {
$currentIndex = new Index($currentIndexName, $table, true);
} else {
$currentIndex = new Index($currentIndexName, $table, false);
}
}
}
// Index definition
if ($name === null) {
// Add field to current index
assert($currentIndex !== null);
$currentIndex->addField($field['indexColumn']);
} else {
// New foreign key
if ($field['constraintName'] !== $currentForeignKeyName) {
// Add old foreign key to table
if ($currentForeignKey !== null) {
$res->addForeignKey($currentForeignKey);
}
// Create a new foreign key
$currentForeignKeyName = $field['constraintName'];
if ($currentForeignKeyName === null) {
$currentForeignKey = null;
} else {
assert(array_key_exists($field['onDelete'], $this->onDeleteActionMapping));
$currentForeignKey = new ForeignKey($currentForeignKeyName, $table, $this->removePrefix($field['referencedTable']), array(), $this->onDeleteActionMapping[$field['onDelete']]);
}
}
// Add foreign key fields
if ($currentForeignKeyName !== null) {
assert($currentForeignKey !== null);
$currentForeignKey->addReference($name, $field['referencedColumn']);
}
$res->addField($this->buildField($name, $field['type'], $field['columnDefault'], $field['columnNullable'], $field['extra'], $field['columnComment']));
}
// Save last indices
if ($currentIndex !== null) {
$res->addConstraint($currentIndex);
}
if ($currentForeignKey !== null) {
$res->addConstraint($currentForeignKey);
}
}
return $res;
}
/**
* Tests getting the last insert ID.
*
* @depends testCreateTable
* @covers empire\framework\db\DB::lastInsertID
*
* @param DB[] $dbs the database objects to work on
*/
public function testLastInsertID($dbs)
{
foreach ($dbs as $db) {
/* @var $db DB */
$table = new Table('tlastinsert');
$table->addField(new Field('flastinsert', Field::TYPE_INT, '', false, true));
$table->addField(new Field('ftest', new Type(Field::TYPE_VARCHAR, 10)));
$table->setPrimaryKey(new PrimaryKey('flastinsert', array('flastinsert')));
if ($db->tableExists($table->getName())) {
$db->dropTable($table->getName());
}
$db->createTable($table);
$bindings = array(':::table' => 'tlastinsert', ':value' => 'hello');
for ($i = 1; $i <= 100; $i++) {
$db->execute('INSERT INTO :::table (ftest) VALUES(:value)', $bindings);
$this->assertSame($i, $db->lastInsertID());
}
$db->dropTable($table->getName());
}
}
