/** * 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); }
/** * 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; }
public function setUpTable(Table $table) { $table->addField(new Field('name', new Type(Field::TYPE_VARCHAR, 255))); $table->addField(new Field('length', Field::TYPE_INT)); return $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()); } }