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 TestTreeStringOutputVisitorWithNullTableConstraints()
{
var sampleWithoutTableConstraint = new CreateTableNode
{
TableName = "PROJECTS",
ColumnDefinitions = new[]
{
new ColumnDefNode
{
ColumnName = "CLASSID",
TypeNameNode = new TypeNameNode {
TypeName = "int"
},
ColumnConstraints = new[] { new DefaultConstraintNode()
{
Value = "NULL"
} }
}
}
};
var visitor = new TreeStringOutputVisitor();
Assert.DoesNotThrow(() => sampleWithoutTableConstraint.Accept(visitor).ToString());
}
private IEnumerable <string> CreateNewTableCopyInDataAndCreateIndices(AlterTableCommand command, string tempTable)
{
string fullTableName = command.Name;
//we'll use this to create the insert into later
string[] originalColumns = CreateTableNode.ColumnDefinitions.Select(i => i.ColumnName).ToArray();
CreateTableNode = IncorporateAlterationsInCreateNode(command, CreateTableNode);
var visitor = new TreeStringOutputVisitor();
yield return(CreateTableNode.Accept(visitor).ToString());
string[] finalSetOfColumns =
originalColumns.Where(i => command.TableCommands.OfType <DropColumnCommand>().All(j => j.ColumnName != i))
.ToArray();
// this can only be the ones in the ORIGINAL table that we want to copy, i.e. don't copy in deleted columns
yield return(StatementUtil.InsertInto(fullTableName, finalSetOfColumns, tempTable));
//let's figure out our final indexes
IncorporateAlterationsInIndexNodes(CreateIndexNodes, fullTableName, command.TableCommands, finalSetOfColumns);
var indexNodeVisitor = new TreeStringOutputVisitor();
foreach (CreateIndexNode indexNode in CreateIndexNodes)
{
yield return(indexNode.Accept(indexNodeVisitor).ToString());
}
}
/// <summary>
/// returns null if something if we don't have ones we can do natively
/// </summary>
/// <param name="command"></param>
/// <returns></returns>
public IEnumerable <string> CreateSqlStatements(AlterTableCommand command)
{
if (!command.TableCommands.All(i => i is AddColumnCommand || i is AddIndexCommand || i is DropIndexCommand))
{
// we have non Native Alterations to make
yield break;
}
var visitor = new TreeStringOutputVisitor();
foreach (var alterCommand in command.TableCommands)
{
if (alterCommand is AddColumnCommand)
{
var addColumn = alterCommand as AddColumnCommand;
yield return
(StatementUtil.AddColumn(command.Name,
addColumn.CreateColumnDefNode().Accept(visitor).ToString()));
}
else if (alterCommand is AddIndexCommand)
{
var addIndexCommand = alterCommand as AddIndexCommand;
yield return(StatementUtil.AddIndex(addIndexCommand.IndexName, addIndexCommand.TableName, addIndexCommand.ColumnNames));
}
else if (alterCommand is DropIndexCommand)
{
var dropIndexCommand = alterCommand as DropIndexCommand;
yield return(StatementUtil.DropIndex(dropIndexCommand.IndexName));
}
}
}
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 TestWithTableConstraintsRoundTrip()
{
var parser = RunParser(
"CREATE TABLE PROJECTS(CLASSID int null, SEQNO int not null, LASTMODONNODEID text(50) not null, PREVMODONNODEID text(50) null, ISSUEID text(50) not null, OBJECTID text(50) not null, REVISIONNUM int not null, CONTAINERID text(50) not null, AUTHORID text(50) not null, CREATIONDATE text(25) null, LASTMODIFIEDDATE text(25) null, UPDATENUMBER int null, PREVREVISIONNUM int null, LASTCMD int null, LASTCMDACLVERSION int null, USERDEFINEDFIELD text(300) null, LASTMODIFIEDBYID text(50) null, NAME text(100) not null, ID text(100) null, constraint PK_PROJECTS primary key (ISSUEID, OBJECTID))");
var expected = parser.ToExpectedObject().AddTreeNode();
var visitor = new TreeStringOutputVisitor();
var output = parser.Accept(visitor).ToString();
var finalTree = RunParser(output);
expected.ShouldMatch(finalTree);
}
public void TestBigThing()
{
var parser =
RunParser(
"CREATE TABLE operationbalance(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,f_balance FLOAT NOT NULL DEFAULT 0,r_operation_id INTEGER NOT NULL);");
var expected = parser.ToExpectedObject().AddTreeNode();
var visitor = new TreeStringOutputVisitor();
var output = parser.Accept(visitor).ToString();
//lets parse it again!
var finalTree = RunParser(output);
expected.ShouldMatch(finalTree);
}
