public void TestCreateIndexUNIQUEAndMoreComplex()
{
const string createIndexStmt = "CREATE UNIQUE INDEX test1 ON test_table (column1 ASC, column2 COLLATE i, column3);";
var nodes = SQLiteParseVisitor.ParseString <CreateIndexNode>(createIndexStmt);
var expected = new CreateIndexNode
{
IndexName = "test1",
TableName = "test_table",
IsUnique = true,
IndexedColumnNodes = new[]
{
new IndexedColumnNode {
Id = "column1", Order = SortOrder.Asc
},
new IndexedColumnNode {
Id = "column2", CollationId = "i"
},
new IndexedColumnNode {
Id = "column3"
}
}
}.ToExpectedObject().AddTreeNode();
expected.ShouldMatch(nodes);
var treeStringBuilder = new TreeStringOutputVisitor();
var result = nodes.Accept(treeStringBuilder);
Assert.Equal(createIndexStmt, result.ToString());
}
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 ParseStringWithParseErrorsFails()
{
// missing one right parens at the end
var originalTable =
"Create Table TEST_TestTable (id INTEGER primary key autoincrement, name TEXT(40) NULL, last TEXT(256)";
Assert.Throws <ParseException>(() => SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable));
}
public void TestCreateIndex()
{
const string createIndexStmt = "CREATE INDEX test1 ON test_table (column1, column2, column3);";
var nodes = SQLiteParseVisitor.ParseString <CreateIndexNode>(createIndexStmt);
var treeStringBuilder = new TreeStringOutputVisitor();
var result = nodes.Accept(treeStringBuilder);
Assert.Equal(createIndexStmt, result.ToString());
}
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);
}
internal ColumnDefNode CreateColumnDefNode()
{
var ret = new ColumnDefNode {
ColumnName = this.ColumnName, ColumnConstraints = new List <ColumnConstraintNode>()
};
//dialect converts DbType.Int16-64 to "INT" not "INTEGER" and only INTEGER columns can be autoincremented. This fixes that.
string correctType = this.IsIdentity ? "INTEGER" : this.DbType;
ret.TypeNameNode = SQLiteParseVisitor.ParseString <TypeNameNode>(correctType, i => i.type_name());
//not quite right but should work
if (this.IsIdentity || this.IsPrimaryKey)
{
var primKey = new PrimaryKeyConstraintNode();
if (this.IsIdentity)
{
primKey.AutoIncrement = true;
}
ret.ColumnConstraints.Add(primKey);
}
if (this.Default != null)
{
ret.ColumnConstraints.Add(new DefaultConstraintNode {
Value = DbUtils.ConvertToSqlValue(Default)
});
}
if (this.IsNotNull)
{
ret.ColumnConstraints.Add(new NotNullConstraintNode());
}
else if (this.Default == null && !this.IsPrimaryKey && !this.IsUnique)
{
ret.ColumnConstraints.Add(new DefaultConstraintNode {
Value = "NULL"
});
}
if (this.IsUnique)
{
ret.ColumnConstraints.Add(new UniqueConstraintNode());
}
return(ret);
}
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 AlterMissingColumnShouldFail()
{
//var origin
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.AlterColumn("fake_column", i => i.WithType("fake"));
var adapter = new AlterTableAdapter(SQLiteParseVisitor.ParseString <CreateTableNode>(originalTable),
originalIndices.Select(SQLiteParseVisitor.ParseString <CreateIndexNode>));
Assert.Throws <InvalidColumnException <AlterColumnCommand> >(() => adapter.AlterTableStatements(input).ToArray());
}
public void ParsePartOfAString()
{
string parseOnlyAnArgument = "id INTEGER primary key autoincrement";
SQLiteParseTreeNode statementNodes = SQLiteParseVisitor.ParseString(parseOnlyAnArgument, i => i.column_def());
var expected = new ColumnDefNode()
{
ColumnName = "id",
ColumnConstraints = new[]
{
new PrimaryKeyConstraintNode {
AutoIncrement = true
}
},
TypeNameNode = new TypeNameNode()
{
TypeName = "INTEGER"
}
}.ToExpectedObject().AddTreeNode();
expected.ShouldMatch(statementNodes);
}
public AlterTableAdapter(string createTableStmt, IEnumerable <string> creatIndexStmts)
{
CreateTableNode = SQLiteParseVisitor.ParseString <CreateTableNode>(createTableStmt);
CreateIndexNodes = creatIndexStmts.Where(i => !String.IsNullOrEmpty(i)).Select(SQLiteParseVisitor.ParseString <CreateIndexNode>).ToList();
}
public SQLiteParseTreeNode RunParser(string parseString)
{
return(SQLiteParseVisitor.ParseString(parseString));
}
private CreateTableNode IncorporateAlterationsInCreateNode(AlterTableCommand command,
CreateTableNode createTableNode)
{
foreach (TableCommand alterCommand in command.TableCommands)
{
if (alterCommand is AddColumnCommand)
{
var addColumn = alterCommand as AddColumnCommand;
createTableNode.ColumnDefinitions.Add(addColumn.CreateColumnDefNode());
}
else if (alterCommand is DropColumnCommand)
{
var dropColumn = alterCommand as DropColumnCommand;
ColumnDefNode result =
createTableNode.ColumnDefinitions.FirstOrDefault(i => i.ColumnName == dropColumn.ColumnName);
if (result == null)
{
//bad!!
throw new InvalidColumnException <DropColumnCommand>(String.Format("Altering column {0} failed. No such column exists on table {1}.", dropColumn.ColumnName, dropColumn.TableName), dropColumn);
}
else
{
//remove our column
createTableNode.ColumnDefinitions.Remove(result);
}
}
else if (alterCommand is AlterColumnCommand)
{
var alterColumn = alterCommand as AlterColumnCommand;
ColumnDefNode columnDef =
createTableNode.ColumnDefinitions.FirstOrDefault(i => i.ColumnName == alterColumn.ColumnName);
if (columnDef == null)
{
//throw!!!!
throw new InvalidColumnException <AlterColumnCommand>(String.Format("Altering column {0} failed. No such column exists on table {1}.", alterColumn.ColumnName, alterColumn.TableName), alterColumn);
}
//modify the type name
if (!String.IsNullOrEmpty(alterColumn.DbType))
{
columnDef.TypeNameNode = SQLiteParseVisitor.ParseString <TypeNameNode>(alterColumn.DbType,
i => i.type_name());
}
if (alterColumn.Default != null)
{
//modify the default
DefaultConstraintNode defaultConstraint =
columnDef.ColumnConstraints.OfType <DefaultConstraintNode>().FirstOrDefault();
if (defaultConstraint == null)
{
//we'll create our own
defaultConstraint = new DefaultConstraintNode
{
Value = DbUtils.ConvertToSqlValue(alterColumn.Default)
};
//and add it!
columnDef.ColumnConstraints.Add(defaultConstraint);
}
else
{
//we modify the one that exists
defaultConstraint.Value = DbUtils.ConvertToSqlValue(alterColumn.Default);
}
}
}
else if (alterCommand is CreateForeignKeyCommand)
{
var foreignKeyCommand = alterCommand as CreateForeignKeyCommand;
var keyNode = new TableConstraintForeignKeyNode
{
FieldNames = foreignKeyCommand.SrcColumns,
ConstraintName = foreignKeyCommand.Name,
ForeignKeyClauseNode = new ForeignKeyClauseNode
{
TableName = foreignKeyCommand.DestTable,
FieldList = foreignKeyCommand.DestColumns,
ForeignDeferrable = new ForeignDeferrableNode().SetToTrulyDeferrable()
}
};
createTableNode.TableConstraints.Add(keyNode);
}
else if (alterCommand is DropForeignKeyCommand)
{
var foreignKeyCommand = alterCommand as DropForeignKeyCommand;
TableConstraintForeignKeyNode foreignKeyDrop = createTableNode.TableConstraints
.OfType <TableConstraintForeignKeyNode>()
.FirstOrDefault(n => n.ConstraintName == foreignKeyCommand.Name);
if (foreignKeyDrop == null)
{
throw new InvalidForeignKeyException(String.Format("No foreign key {0} exists.", foreignKeyCommand.Name), foreignKeyCommand);
}
createTableNode.TableConstraints.Remove(foreignKeyDrop);
}
}
return(createTableNode);
}
