public function testSQLiteDialect()
{
$dialect = new \Phalcon\Db\Dialect\Sqlite();
$columns = $dialect->getColumnList(array('column1', 'column2', 'column3'));
$this->assertEquals($columns, '"column1", "column2", "column3"');
$columns = $this->getColumns();
//Column definitions
$this->assertEquals($dialect->getColumnDefinition($columns['column1']), 'VARCHAR(10)');
$this->assertEquals($dialect->getColumnDefinition($columns['column2']), 'INT');
$this->assertEquals($dialect->getColumnDefinition($columns['column3']), 'NUMERIC(10,2)');
$this->assertEquals($dialect->getColumnDefinition($columns['column4']), 'CHARACTER(100)');
$this->assertEquals($dialect->getColumnDefinition($columns['column5']), 'DATE');
$this->assertEquals($dialect->getColumnDefinition($columns['column6']), 'TIMESTAMP');
$this->assertEquals($dialect->getColumnDefinition($columns['column7']), 'TEXT');
$this->assertEquals($dialect->getColumnDefinition($columns['column8']), 'FLOAT');
//Add Columns
$this->assertEquals($dialect->addColumn('table', null, $columns['column1']), 'ALTER TABLE "table" ADD COLUMN "column1" VARCHAR(10)');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column1']), 'ALTER TABLE "schema"."table" ADD COLUMN "column1" VARCHAR(10)');
$this->assertEquals($dialect->addColumn('table', null, $columns['column2']), 'ALTER TABLE "table" ADD COLUMN "column2" INT');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column2']), 'ALTER TABLE "schema"."table" ADD COLUMN "column2" INT');
$this->assertEquals($dialect->addColumn('table', null, $columns['column3']), 'ALTER TABLE "table" ADD COLUMN "column3" NUMERIC(10,2) NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column3']), 'ALTER TABLE "schema"."table" ADD COLUMN "column3" NUMERIC(10,2) NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column4']), 'ALTER TABLE "table" ADD COLUMN "column4" CHARACTER(100) NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column4']), 'ALTER TABLE "schema"."table" ADD COLUMN "column4" CHARACTER(100) NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column5']), 'ALTER TABLE "table" ADD COLUMN "column5" DATE NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column5']), 'ALTER TABLE "schema"."table" ADD COLUMN "column5" DATE NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column6']), 'ALTER TABLE "table" ADD COLUMN "column6" TIMESTAMP NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column6']), 'ALTER TABLE "schema"."table" ADD COLUMN "column6" TIMESTAMP NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column7']), 'ALTER TABLE "table" ADD COLUMN "column7" TEXT NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column7']), 'ALTER TABLE "schema"."table" ADD COLUMN "column7" TEXT NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column8']), 'ALTER TABLE "table" ADD COLUMN "column8" FLOAT NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column8']), 'ALTER TABLE "schema"."table" ADD COLUMN "column8" FLOAT NOT NULL');
//Modify Columns
try {
$dialect->modifyColumn('table', null, $columns['column1']);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
//Drop Columns
try {
$dialect->dropColumn('table', null, 'column1');
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
$indexes = $this->getIndexes();
//Add Index
$this->assertEquals($dialect->addIndex('table', null, $indexes['index1']), 'CREATE INDEX "index1" ON "table" ("column1")');
$this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index1']), 'CREATE INDEX "schema"."index1" ON "table" ("column1")');
$this->assertEquals($dialect->addIndex('table', null, $indexes['index2']), 'CREATE INDEX "index2" ON "table" ("column1", "column2")');
$this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index2']), 'CREATE INDEX "schema"."index2" ON "table" ("column1", "column2")');
//Drop Index
$this->assertEquals($dialect->dropIndex('table', null, 'index1'), 'DROP INDEX "index1"');
$this->assertEquals($dialect->dropIndex('table', 'schema', 'index1'), 'DROP INDEX "schema"."index1"');
//Add Primary Key
try {
$dialect->addPrimaryKey('table', null, $indexes['PRIMARY']);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
//Drop Primary Key
try {
$dialect->dropPrimaryKey('table', null);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
$references = $this->getReferences();
//Add Foreign Key
try {
$dialect->addForeignKey('table', null, $references['fk1']);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
try {
$dialect->dropForeignKey('table', null, 'fk1');
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
//Create tables
// Not implemented yet
}
public function testSQLiteDialect()
{
$dialect = new \Phalcon\Db\Dialect\Sqlite();
$columns = $dialect->getColumnList(array('column1', 'column2', 'column3', 'column13'));
$this->assertEquals($columns, '"column1", "column2", "column3", "column13"');
$columns = $this->getColumns();
//Column definitions
$this->assertEquals($dialect->getColumnDefinition($columns['column1']), 'VARCHAR(10)');
$this->assertEquals($dialect->getColumnDefinition($columns['column2']), 'INTEGER');
$this->assertEquals($dialect->getColumnDefinition($columns['column3']), 'NUMERIC(10,2)');
$this->assertEquals($dialect->getColumnDefinition($columns['column4']), 'CHARACTER(100)');
$this->assertEquals($dialect->getColumnDefinition($columns['column5']), 'DATE');
$this->assertEquals($dialect->getColumnDefinition($columns['column6']), 'DATETIME');
$this->assertEquals($dialect->getColumnDefinition($columns['column7']), 'TEXT');
$this->assertEquals($dialect->getColumnDefinition($columns['column8']), 'FLOAT');
$this->assertEquals($dialect->getColumnDefinition($columns['column9']), 'VARCHAR(10)');
$this->assertEquals($dialect->getColumnDefinition($columns['column10']), 'INTEGER');
$this->assertEquals($dialect->getColumnDefinition($columns['column13']), 'TIMESTAMP');
//Add Columns
$this->assertEquals($dialect->addColumn('table', null, $columns['column1']), 'ALTER TABLE "table" ADD COLUMN "column1" VARCHAR(10)');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column1']), 'ALTER TABLE "schema"."table" ADD COLUMN "column1" VARCHAR(10)');
$this->assertEquals($dialect->addColumn('table', null, $columns['column2']), 'ALTER TABLE "table" ADD COLUMN "column2" INTEGER');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column2']), 'ALTER TABLE "schema"."table" ADD COLUMN "column2" INTEGER');
$this->assertEquals($dialect->addColumn('table', null, $columns['column3']), 'ALTER TABLE "table" ADD COLUMN "column3" NUMERIC(10,2) NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column3']), 'ALTER TABLE "schema"."table" ADD COLUMN "column3" NUMERIC(10,2) NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column4']), 'ALTER TABLE "table" ADD COLUMN "column4" CHARACTER(100) NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column4']), 'ALTER TABLE "schema"."table" ADD COLUMN "column4" CHARACTER(100) NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column5']), 'ALTER TABLE "table" ADD COLUMN "column5" DATE NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column5']), 'ALTER TABLE "schema"."table" ADD COLUMN "column5" DATE NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column6']), 'ALTER TABLE "table" ADD COLUMN "column6" DATETIME NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column6']), 'ALTER TABLE "schema"."table" ADD COLUMN "column6" DATETIME NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column7']), 'ALTER TABLE "table" ADD COLUMN "column7" TEXT NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column7']), 'ALTER TABLE "schema"."table" ADD COLUMN "column7" TEXT NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column8']), 'ALTER TABLE "table" ADD COLUMN "column8" FLOAT NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column8']), 'ALTER TABLE "schema"."table" ADD COLUMN "column8" FLOAT NOT NULL');
$this->assertEquals($dialect->addColumn('table', null, $columns['column9']), 'ALTER TABLE "table" ADD COLUMN "column9" VARCHAR(10) DEFAULT "column9"');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column9']), 'ALTER TABLE "schema"."table" ADD COLUMN "column9" VARCHAR(10) DEFAULT "column9"');
$this->assertEquals($dialect->addColumn('table', null, $columns['column10']), 'ALTER TABLE "table" ADD COLUMN "column10" INTEGER DEFAULT "10"');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column10']), 'ALTER TABLE "schema"."table" ADD COLUMN "column10" INTEGER DEFAULT "10"');
$this->assertEquals($dialect->addColumn('table', null, $columns['column13']), 'ALTER TABLE "table" ADD COLUMN "column13" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL');
$this->assertEquals($dialect->addColumn('table', 'schema', $columns['column13']), 'ALTER TABLE "schema"."table" ADD COLUMN "column13" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL');
//Modify Columns
try {
$dialect->modifyColumn('table', null, $columns['column1']);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
//Drop Columns
try {
$dialect->dropColumn('table', null, 'column1');
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
$indexes = $this->getIndexes();
//Add Index
$this->assertEquals($dialect->addIndex('table', null, $indexes['index1']), 'CREATE INDEX "index1" ON "table" ("column1")');
$this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index1']), 'CREATE INDEX "schema"."index1" ON "table" ("column1")');
$this->assertEquals($dialect->addIndex('table', null, $indexes['index2']), 'CREATE INDEX "index2" ON "table" ("column1", "column2")');
$this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index2']), 'CREATE INDEX "schema"."index2" ON "table" ("column1", "column2")');
//Drop Index
$this->assertEquals($dialect->dropIndex('table', null, 'index1'), 'DROP INDEX "index1"');
$this->assertEquals($dialect->dropIndex('table', 'schema', 'index1'), 'DROP INDEX "schema"."index1"');
//Add Primary Key
try {
$dialect->addPrimaryKey('table', null, $indexes['PRIMARY']);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
//Drop Primary Key
try {
$dialect->dropPrimaryKey('table', null);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
$references = $this->getReferences();
//Add Foreign Key
try {
$dialect->addForeignKey('table', null, $references['fk1']);
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
try {
$dialect->dropForeignKey('table', null, 'fk1');
$this->assertTrue(false);
} catch (Phalcon\Db\Exception $ex) {
$this->assertTrue(true);
} catch (Exception $e) {
$this->assertTrue(false);
}
//Create tables
$definition = array('columns' => array($columns['column1'], $columns['column2']));
$expected = "CREATE TABLE \"table\" (\n";
$expected .= "\t`column1` VARCHAR(10),\n";
$expected .= "\t`column2` INTEGER\n";
$expected .= ")";
$this->assertEquals($dialect->createTable('table', null, $definition), $expected);
$definition = array('columns' => array($columns['column2'], $columns['column3'], $columns['column1']), 'indexes' => array($indexes['PRIMARY']));
$expected = "CREATE TABLE \"table\" (\n";
$expected .= "\t`column2` INTEGER,\n";
$expected .= "\t`column3` NUMERIC(10,2) NOT NULL,\n";
$expected .= "\t`column1` VARCHAR(10),\n";
$expected .= "\tPRIMARY KEY (\"column3\")\n";
$expected .= ")";
$this->assertEquals($dialect->createTable('table', null, $definition), $expected);
$definition['references'] = array($references['fk3']);
$expected = "CREATE TABLE \"table\" (\n";
$expected .= "\t`column2` INTEGER,\n";
$expected .= "\t`column3` NUMERIC(10,2) NOT NULL,\n";
$expected .= "\t`column1` VARCHAR(10),\n";
$expected .= "\tPRIMARY KEY (\"column3\"),\n";
$expected .= "\tCONSTRAINT `fk3` FOREIGN KEY (\"column1\") REFERENCES `ref_table`(\"column2\") ON DELETE CASCADE\n";
$expected .= ")";
$this->assertEquals($dialect->createTable('table', null, $definition), $expected);
$definition = array('columns' => array($columns['column9'], $columns['column10']));
$expected = "CREATE TABLE \"table\" (\n";
$expected .= "\t`column9` VARCHAR(10) DEFAULT \"column9\",\n";
$expected .= "\t`column10` INTEGER DEFAULT \"10\"\n";
$expected .= ")";
$this->assertEquals($dialect->createTable('table', null, $definition), $expected);
$definition = array('columns' => array($columns['column11'], $columns['column13']));
$expected = "CREATE TABLE \"table\" (\n";
$expected .= "\t`column11` BIGINT,\n";
$expected .= "\t`column13` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL\n";
$expected .= ")";
$this->assertEquals($dialect->createTable('table', null, $definition), $expected);
}