public function testDropMultipleTable()
{
$q = new CreateTableQuery('points');
$q->column('id')->int();
$this->assertQuery($q);
$q = new CreateTableQuery('users');
$q->column('id')->int();
$this->assertQuery($q);
$q = new DropTableQuery(['points', 'users']);
$this->assertQuery($q);
}
public function testCreateTables()
{
$this->cleanUpTables();
$createProductTable = new CreateTableQuery('products');
$createProductTable->column('id')->integer()->primary()->autoIncrement();
$createProductTable->column('name')->varchar(32);
$createProductTable->column('description')->text();
$createProductTable->column('created_by')->int();
$createProductTable->column('updated_by')->int();
$this->assertDriverQuery(new MySQLDriver(), $createProductTable);
$this->assertDriverQuery(new PgSQLDriver(), $createProductTable);
$createUserTable = new CreateTableQuery('users');
$createUserTable->column('id')->integer()->primary()->autoIncrement();
$this->assertDriverQuery(new MySQLDriver(), $createUserTable);
$this->assertDriverQuery(new PgSQLDriver(), $createUserTable);
$this->assertDriverQuery(new SQLiteDriver(), $createUserTable);
}
public function setUp()
{
parent::setUp();
$q = new CreateTableQuery('products');
$q->column('id')->integer()->primary()->autoIncrement();
$q->column('name')->varchar(32);
$q->column('sn')->varchar(16);
$q->column('price')->int(4)->unsigned();
$q->column('content')->text();
$this->assertQuery($q);
$q = new CreateTableQuery('users');
$q->column('id')->integer()->primary()->autoIncrement();
$q->column('name')->varchar(32);
$q->column('phone')->varchar(16);
$q->column('address')->varchar(128);
$this->assertQuery($q);
}
public function testCreateTableQuery()
{
$q = new CreateTableQuery('groups');
$q->column('id')->integer()->primary()->autoIncrement();
$q->engine('InnoDB');
$this->assertQuery($q);
$q = new CreateTableQuery('users');
$q->table('authors');
$q->column('id')->integer()->primary()->autoIncrement();
$q->column('first_name')->varchar(32);
$q->column('last_name')->varchar(16);
$q->column('age')->tinyint(3)->unsigned()->null();
$q->column('phone')->varchar(24)->null();
$q->column('email')->varchar(128)->notNull();
$q->column('confirmed')->boolean()->default(false);
$q->column('types')->set('student', 'teacher');
$q->column('remark')->text();
$q->column('group_id')->integer();
// create table t1 (
// id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
// product_id integer unsigned, constraint `fk_product_id`
// FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) );
$q->constraint('fk_group_id')->foreignKey('group_id')->references('groups', 'id')->onDelete('CASCADE')->onUpdate('CASCADE');
$q->uniqueKey('email');
$q->engine('InnoDB');
$dropQuery = new DropTableQuery('authors');
$dropQuery->IfExists();
$this->assertSql('DROP TABLE IF EXISTS `authors`', $dropQuery);
$this->assertQuery($dropQuery);
$this->assertSql('CREATE TABLE `authors`(
`id` int PRIMARY KEY AUTO_INCREMENT,
`first_name` varchar(32),
`last_name` varchar(16),
`age` tinyint(3) UNSIGNED NULL,
`phone` varchar(24) NULL,
`email` varchar(128) NOT NULL,
`confirmed` boolean DEFAULT FALSE,
`types` set(\'student\', \'teacher\'),
`remark` text,
`group_id` int,
CONSTRAINT `fk_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
UNIQUE KEY (`email`)
) ENGINE=InnoDB', $q);
$this->assertQuery($q);
$this->assertQuery($dropQuery);
// drop again to test the if exists.
}
public function setUp()
{
parent::setUp();
$q = new DropTableQuery('users');
$q->ifExists();
$this->assertQuery($q);
$q = new CreateTableQuery('users');
$q->column('id')->integer()->primary()->autoIncrement();
$q->column('first_name')->varchar(32);
$q->column('last_name')->varchar(16);
$q->column('age')->tinyint(3)->unsigned()->null();
$q->column('phone')->varchar(24)->null();
$q->column('email')->varchar(128)->notNull();
$q->column('confirmed')->boolean()->default(false);
$q->column('types')->set('student', 'teacher');
$q->column('remark')->text();
$q->index(['first_name', 'last_name'])->name('username_idx');
$this->assertQuery($q);
}
