public void AlterTableAdapterIgnoresEmptyOrNullIndices()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256));";
var originalIndices = new string[0];
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
var input = new AlterTableCommand("TEST_TestTable");
input.DropColumn("name");
input.DropColumn("last");
input.CreateIndex("fun", "id");
var result = adapter.AlterTableStatements(input);
var expectedFinal = new[]
{
//this line expects the guid to be added
"CREATE TEMPORARY TABLE TEST_TestTable_ AS SELECT * FROM TEST_TestTable;",
"DROP TABLE TEST_TestTable;",
"CREATE TABLE TEST_TestTable (id INTEGER primary key autoincrement);",
//this line expects the guid to be added
"INSERT INTO TEST_TestTable (id) SELECT id FROM TEST_TestTable_;",
"CREATE INDEX fun ON TEST_TestTable (id);",
//this line expects the guid to be added
"DROP TABLE TEST_TestTable_;",
};
VerifyYourStatementsAreValid(expectedFinal, result);
}
public void HandleAlterTables()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256), "
+ "CONSTRAINT TEST_FK FOREIGN KEY (id) REFERENCES FAKE_TABLE (id) DEFERRABLE INITIALLY DEFERRED,"
+ "CONSTRAINT KEEP_FK FOREIGN KEY (temp, id) REFERENCES FAKE_TABLE (temp, id) DEFERRABLE INITIALLY DEFERRED);";
var originalIndices = new[]
{
"CREATE INDEX some_index ON TEST_TestTable (id)",
"CREATE INDEX name_index ON TEST_TestTable (name)",
"CREATE INDEX this_should_be_gone ON TEST_TestTable (name, last)",
};
var input = new AlterTableCommand("TEST_TestTable");
input.CreateIndex("funny_index", "id", "name");
input.DropIndex("some_index");
input.AddColumn("add_column", "TINYINT");
input.DropColumn("last");
//no, this doesn't make sense; no, I don't care
input.AlterColumn("name", c => c.WithDefault(0).WithType("INTEGER"));
input.DropForeignKey("TEST_FK");
input.CreateForeignKey("TEST_FK2", new [] { "id", "last" }, "SOME_OTHERTABLE", new [] { "something", "else" });
var expectedFinal = new[]
{
//this line expects the guid to be added
"CREATE TEMPORARY TABLE TEST_TestTable_ AS SELECT * FROM TEST_TestTable;",
"DROP TABLE TEST_TestTable;",
"CREATE TABLE TEST_TestTable (id INTEGER primary key autoincrement, name INTEGER default 0, add_column TINYINT default NULL, " +
"CONSTRAINT KEEP_FK FOREIGN KEY (temp, id) REFERENCES FAKE_TABLE (temp, id) DEFERRABLE INITIALLY DEFERRED, " +
"CONSTRAINT TEST_FK2 FOREIGN KEY (id, last) REFERENCES SOME_OTHERTABLE (something, else) DEFERRABLE INITIALLY DEFERRED);",
//this line expects the guid to be added
"INSERT INTO TEST_TestTable (id, name) SELECT id, name FROM TEST_TestTable_;",
"CREATE INDEX name_index ON TEST_TestTable (name);",
"CREATE INDEX funny_index ON TEST_TestTable (id, name);",
//this line expects the guid to be added
"DROP TABLE TEST_TestTable_;",
}.Select(LowerAndWhitespaceFreeString).ToArray();
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
var output = adapter.AlterTableStatements(input).Select(LowerAndWhitespaceFreeString).ToArray();
VerifyYourStatementsAreValid(expectedFinal, output);
}
public void DropMissingIndexShouldFail()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256));";
var originalIndices = new string[0];
var input = new AlterTableCommand("TEST_TestTable");
input.DropIndex("fake_index");
input.DropColumn("name");
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
Assert.Throws <InvalidIndexException>(() => adapter.AlterTableStatements(input).ToArray());
}
public void FirstAlterTableTest()
{
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256))";
var originalIndices = new[]
{
"CREATE INDEX some_index ON TEST_TestTable (id)",
"CREATE INDEX name_index ON TEST_TestTable (name)",
"CREATE INDEX this_should_be_gone ON TEST_TestTable (name, last)",
};
var input = new AlterTableCommand("TEST_TestTable");
input.CreateIndex("funny_index", "id", "name");
input.DropIndex("some_index");
input.AddColumn("add_column", "TINYINT", command => command.WithType("TINYINT"));
input.DropColumn("last");
//no, this doesn't make sense; no, I don't care
input.AlterColumn("name", c => c.WithDefault(0).WithType("INTEGER"));
var expectedFinal = new[]
{
//this line expects the guid to be added
"CREATE TEMPORARY TABLE TEST_TestTable_ AS SELECT * FROM TEST_TestTable;",
"DROP TABLE TEST_TestTable;",
"CREATE TABLE TEST_TestTable (id INTEGER primary key autoincrement, name INTEGER default 0, add_column TINYINT default NULL);",
//this line expects the guid to be added
"INSERT INTO TEST_TestTable (id, name) SELECT id, name FROM TEST_TestTable_;",
"CREATE INDEX name_index ON TEST_TestTable (name);",
"CREATE INDEX funny_index ON TEST_TestTable (id, name);",
//this line expects the guid to be added
"DROP TABLE TEST_TestTable_;",
};
var adapter = new AlterTableAdapter(originalTable, originalIndices);
var output = adapter.AlterTableStatements(input).Select(LowerAndWhitespaceFreeString).ToArray();
VerifyYourStatementsAreValid(expectedFinal, output);
}