public void SubAssignmentUpdateTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
var row = table.NewRow(3);
table.AddRow(row);
string query = "Update [TBL] set ID -=2";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be one row affected");
Assert.AreEqual(1, table.GetRow(0)["ID"], "The updated value was not present on the Table");
}
public void TestFKSetDefaultDeleteAndUpdateRules()
{
var db = new Database();
var visitor = new SQLInterpreter(db);
string script = @"
CREATE TABLE T1 (Id INT PRIMARY KEY NOT NULL);
CREATE TABLE T2
(
Id INT PRIMARY KEY IDENTITY NOT NULL,
[Name] NVARCHAR(50) NULL,
T1 INT NOT NULL DEFAULT 3,
CONSTRAINT [FK_T1_T2] FOREIGN KEY ([T1]) REFERENCES T1 ([Id])
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT
);
";
visitor.Execute(script);
var t1 = db.GetTable("T1");
Assert.IsNotNull(t1, "Table T1 should exist");
var t2 = db.GetTable("T2");
Assert.IsNotNull(t2, "Table T2 should exist");
// Initialize T1
t1.AddRow(1); t1.AddRow(2); t1.AddRow(3);
// Initialize T2
Action <string, int?> t2_insert = (f1, f2) =>
{
Row row;
if (f2.HasValue)
{
row = t2.NewRow(f1, f2);
}
else
{
row = t2.NewRow(("Name", f1));
}
public void FKCreationTest()
{
string script = "Create table [TBL](col1 int NOT NULL,PRIMARY KEY (col1))";
var db = new Database();
var visitor = new SQLInterpreter(db);
var result = visitor.Execute(script)[0];
Assert.AreEqual(0, result.RowsAffected);
Assert.IsTrue(db.ContainsTable("TBL"), "The table must be created");
var table = db.GetTable("TBL");
Assert.IsTrue(table.ContainsColumn("col1"));
Assert.AreEqual(typeof(int), table.GetColumn("col1").DataType);
Assert.IsTrue(table.PrimaryKey.Length == 1, "The Primary Key is missing!");
Assert.AreEqual(table.GetColumn("col1"), table.PrimaryKey[0]);
visitor = new SQLInterpreter(db);
string script2 = "Create table [TBL2](col2 int NOT NULL,PRIMARY KEY (col2), " +
"CONSTRAINT FK_tbl FOREIGN KEY (col2) REFERENCES TBL(col1))";
result = visitor.Execute(script2)[0];
Assert.IsTrue(db.ContainsTable("TBL2"), "The table must be created");
var table2 = db.GetTable("TBL2");
Assert.IsTrue(table2.ContainsColumn("col2"));
Assert.AreEqual(typeof(int), table2.GetColumn("col2").DataType);
Assert.IsTrue(table2.PrimaryKey.Length == 1, "The Primary Key is missing!");
Assert.AreEqual(table2.GetColumn("col2"), table2.PrimaryKey[0]);
Assert.AreEqual(2, table2.Constraints.Count(), "Either the PK or the FK are missing");
Assert.IsTrue(db.ContainsConstraint("FK_tbl"), "The FK was not found by name");
var fk = db.GetConstraint("FK_tbl") as ForeignKeyConstraint;
Assert.IsTrue(fk.Columns.Length == 1);
Assert.AreEqual("col2", fk.Columns[0].ColumnName);
Assert.AreEqual(table2, fk.Table, "The child table is not the correct one");
Assert.IsTrue(fk.RelatedColumns.Length == 1);
Assert.AreEqual("col1", fk.RelatedColumns[0].ColumnName);
Assert.AreEqual(table, fk.RelatedTable, "The parent table is not the correct one");
}
public void BasicDeleteTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
var row = table.NewRow(3);
table.AddRow(row);
string query = "Delete from [TBL]";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be one row affected");
Assert.AreEqual(0, table.Rows.Count(), "There should be no rows on the table");
Assert.IsTrue(row.RowState == DataRowState.Detached, "The created row should have been detached because it is no longer in the table");
}
public void SelectedValuesTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.AddColumn(new Column("B", typeof(int)));
table.AddColumn(new Column("C", typeof(int)));
table.GetColumn("C").AllowDBNull = true;
string query = "Insert into [TBL](B,A) values(2,1)";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be one row affected");
Assert.AreEqual(1, table.Rows.Count(), "There should be one row on the table");
Assert.AreEqual(1, table.GetRow(0)["A"], "The inserted value was not present on the table");
Assert.AreEqual(2, table.GetRow(0)["B"], "The inserted value was not present on the table");
Assert.AreEqual(null, table.GetRow(0)["C"], "The default value was not present on the table");
}
public void InsufficientParametersWithoutFieldNameShouldFail()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.AddColumn(new Column("B", typeof(int)));
string query = "Insert into [TBL] values(3)";
SQLInterpreter interpreter = new SQLInterpreter(db);
Assert.ThrowsException <ArgumentException>(() =>
{
/*
* INFO(Tera): This should fail. SQL Server 2014 throws the following error:
* Msg 213, Level 16, State 1, Line 4
* Column name or number of supplied values does not match table definition.
*/
interpreter.Execute(query);
});
}
public void InsufficientParametersWithoutNameShouldNotConsiderIdentityColumns()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.AddColumn(new Column("B", typeof(string)));
table.GetColumn("A").AutoIncrement = true;
table.GetColumn("A").AutoIncrementSeed = 1;
table.GetColumn("A").AutoIncrementStep = 1;
string query = "Insert into [TBL] values('asd')";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
Assert.AreEqual(1, result.RowsAffected, "There should be one row affected");
Assert.AreEqual(1, table.Rows.Count(), "There should be one row on the table");
Assert.AreEqual(1, table.GetRow(0)["A"], "The inserted value was not present on the table");
Assert.AreEqual("asd", table.GetRow(0)["B"], "The inserted value was not present on the table");
}
public void AutoincrementPKTableCreationTest()
{
string script = "Create table [TBL](ID int IDENTITY(3,3) PRIMARY KEY, DATA int)";
var db = new Database();
var visitor = new SQLInterpreter(db);
var result = visitor.Execute(script)[0];
Assert.AreEqual(0, result.RowsAffected);
Assert.IsTrue(db.ContainsTable("TBL"), "The table must be created");
var table = db.GetTable("TBL");
Assert.IsTrue(table.ContainsColumn("ID"));
Assert.AreEqual(typeof(int), table.GetColumn("ID").DataType);
Assert.IsTrue(table.PrimaryKey.Length == 1, "The Primary Key is missing!");
Assert.AreEqual(table.GetColumn("ID"), table.PrimaryKey[0]);
for (int i = 1; i < 10; i++)
{
var dr = table.NewRow(i);
Assert.AreEqual(i * 3, dr["ID"], "The autonumeric field did not increment correctly");
}
}
public void SelectWithAliasAndWhere()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
var row = table.NewRow(3);
table.AddRow(row);
string query = "Select ID as A from [TBL] where TBL.ID=3";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be one row affected");
Assert.AreEqual(1, result.Values.Columns.Count(), "There should be only one column");
Assert.AreEqual("A", result.Values.Columns.ElementAt(0).ColumnName, "The expected column was not on the result set");
Assert.AreEqual(3, result.Values.Records.First()["A"], "The selected value was not present on the Table");
}
public void CountDistinctSelectTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
table.AddRow(table.NewRow(1));
table.AddRow(table.NewRow(1));
table.AddRow(table.NewRow(3));
table.AddRow(table.NewRow(3));
table.AddRow(table.NewRow(3));
string query = "Select count(distinct ID) from [TBL]";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be one row affected");
Assert.AreEqual(2, result.Values.Records.First().ItemArray[0], "The selected value was not present on the Table");
}
public void MultiplePKShouldFail()
{
string script = @"
CREATE TABLE [dbo].TBL
(
[id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[foo] INT NULL,
CONSTRAINT [UC0] UNIQUE (id),
CONSTRAINT [UC1] UNIQUE (id, foo),
CONSTRAINT [PK2] PRIMARY KEY (id)
)";
var db = new Database();
var visitor = new SQLInterpreter(db);
Assert.ThrowsException <ArgumentException>(() =>
{
visitor.Execute(script);
});
Assert.ThrowsException <InvalidOperationException>(
() => { db.GetTable("TBL"); },
"Table 'TBL' should not exist");
}
public void BatchUpdateTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
for (int i = 0; i < 100; i++)
{
var row = table.NewRow(i);
table.AddRow(row);
}
string query = "Update [TBL] set ID=2";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(100, affected, "There should be 100 rows affected");
for (int i = 0; i < 100; i++)
{
Assert.AreEqual(2, table.GetRow(i)["ID"], "The updated value was not present on All the rows of the Table");
}
}
public void SelectWithoutTable()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
var row = table.NewRow(3);
table.AddRow(row);
string query = "Select 7";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be one row affected");
Assert.AreEqual(1, result.Values.Columns.Count(), "There should be only one column");
var data = result.Values.Records.First().ItemArray;
Assert.AreEqual(7, data[0], "The selected value was not present on the Table");
}
public void ComputedColumnCreationTest()
{
string script = "CREATE TABLE [TBL]( " +
"[num] INT NOT NULL," +
" [calc] AS ( num ))";
var db = new Database();
var visitor = new SQLInterpreter(db);
var result = visitor.Execute(script)[0];
Assert.AreEqual(0, result.RowsAffected);
Assert.IsTrue(db.ContainsTable("TBL"), "The table must be created");
var table = db.GetTable("TBL");
Assert.IsTrue(table.ContainsColumn("num"));
Assert.AreEqual(typeof(int), table.GetColumn("num").DataType);
Assert.IsTrue(table.ContainsColumn("calc"));
Assert.AreEqual(typeof(int), table.GetColumn("calc").DataType);
var dr1 = table.NewRow(1);
Assert.AreEqual(1, dr1["calc"]);
dr1["num"] = -5;
Assert.AreEqual(-5, dr1["calc"]);
}
public void EnumeratingResultsShouldNotExecuteInsertStatementTwice()
{
var db = new Database();
{
Table table = db.AddTable("TBL");
table.AddColumn(new Column("Id", typeof(int))
{
AutoIncrement = true,
AutoIncrementSeed = 1,
AutoIncrementStep = 1,
});
table.AddColumn(new Column("Name", typeof(string)));
db.AddConstraint(new UniqueConstraint("TBL_PK", new[] { table.GetColumn("Id") }, true));
}
var interpreter = new SQLInterpreter(db);
string query = "insert into TBL ([Name]) output inserted.* values ('Richo')";
var result = interpreter.Execute(query)[0];
// Enumerating all records should have no effect
foreach (var row in result.Values.Records)
{
foreach (var col in result.Values.Columns)
{
Console.WriteLine(row[col.ColumnName]);
}
}
// Sending ToString() also enumerates the records
Console.WriteLine(result.ToString());
Assert.AreEqual(1, result.RowsAffected, "There should be one row affected");
Assert.AreEqual(1, db.GetTable("TBL").Rows.Count(), "There should be one row on the table");
Assert.AreEqual(1, db.GetTable("TBL").GetRow(0)["Id"], "The Id should be set correctly");
}
public void InsertIdentityShouldFail()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.GetColumn("A").AutoIncrement = true;
table.GetColumn("A").AutoIncrementSeed = 1;
table.GetColumn("A").AutoIncrementStep = 1;
string query = "Insert into [TBL](A) values(3)";
SQLInterpreter interpreter = new SQLInterpreter(db);
Assert.ThrowsException <InvalidOperationException>(() =>
{
/*
* INFO(Tera): This should fail. SQL Server 2014 throws the following error:
* Msg 544, Level 16, State 1, Line 4
* Cannot insert explicit value for identity column in table 'TBL' when IDENTITY_INSERT is set to OFF.
*/
interpreter.Execute(query);
});
}
public void DistinctSelectTest()
{
//TODO: SQL Server seems to order the results! we need to check the specs
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
table.AddRow(table.NewRow(1));
table.AddRow(table.NewRow(1));
table.AddRow(table.NewRow(3));
table.AddRow(table.NewRow(3));
table.AddRow(table.NewRow(3));
string query = "Select distinct ID from [TBL]";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(2, affected, "There should be one row affected");
Assert.AreEqual(1, result.Values.Records.First().ItemArray[0], "The selected value was not present on the Table");
Assert.AreEqual(3, result.Values.Records.ElementAt(1).ItemArray[0], "The selected value was not present on the Table");
}
public void GroupByWithoutAggregate()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
table.AddColumn(new Column("Valor", typeof(string)));
table.AddRow(table.NewRow(1, "A"));
table.AddRow(table.NewRow(1, "B"));
table.AddRow(table.NewRow(3, "C"));
table.AddRow(table.NewRow(3, "A"));
table.AddRow(table.NewRow(3, "A"));
string query = "Select ID from [TBL] group by ID";
SQLInterpreter interpreter = new SQLInterpreter(db);
//this should work as a distinct.
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(2, affected, "There should be two rows affected");
Assert.AreEqual(1, result.Values.Records.First().ItemArray[0], "The selected value was not present on the Table");
Assert.AreEqual(3, result.Values.Records.ElementAt(1).ItemArray[0], "The selected value was not present on the Table");
}
public void MultipleUniqueConstraintsOnTheSameColumnShouldWork()
{
string script = @"
CREATE TABLE [dbo].[TBL]
(
[id] INT IDENTITY (1, 1) NOT NULL,
[foo] NVARCHAR (256) NULL,
[bar] INT NULL UNIQUE,
CONSTRAINT [UC0] UNIQUE (bar),
CONSTRAINT [UC1] UNIQUE (bar, foo),
CONSTRAINT [UC2] UNIQUE (bar)
)";
var db = new Database();
var visitor = new SQLInterpreter(db);
visitor.Execute(script);
var table = db.GetTable("TBL");
Assert.IsTrue(table.GetColumn("bar").Unique, "Column should be unique");
{
var row = table.NewRow("1", 1);
table.AddRow(row);
}
{
var row = table.NewRow("1", 2);
table.AddRow(row);
}
Assert.ThrowsException <ConstraintException>(() =>
{
var row = table.NewRow("2", 1);
table.AddRow(row);
});
}
public void MultipleUniqueConstraintsOnTheSameColumnAsPKShouldWork()
{
string script = @"
CREATE TABLE [dbo].TBL
(
[id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[foo] INT NULL,
CONSTRAINT [UC0] UNIQUE (id),
CONSTRAINT [UC1] UNIQUE (id, foo)
)";
var db = new Database();
var visitor = new SQLInterpreter(db);
visitor.Execute(script);
var table = db.GetTable("TBL");
Assert.IsTrue(table.GetColumn("id").Unique, "Column should be unique");
CollectionAssert.AreEqual(new[] { table.GetColumn("id") }, table.PrimaryKey,
"PK should be valid");
Assert.IsTrue(db.Constraints.OfType <UniqueConstraint>()
.Where(c => c.Columns.SequenceEqual(new[] { table.GetColumn("id"), table.GetColumn("foo") })).Any(),
"Composite unique key should exist");
}
public void FullOuterJoinSelect()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("col1", typeof(int)));
table.AddColumn(new Column("col2", typeof(int)));
var row = table.NewRow(1, 2);
table.AddRow(row);
row = table.NewRow(1, 5);
table.AddRow(row);
row = table.NewRow(3, 4);
table.AddRow(row);
Table table2 = db.AddTable("TBL2");
table2.AddColumn(new Column("col3", typeof(int)));
table2.AddColumn(new Column("col4", typeof(string)));
var row2 = table2.NewRow(1, "A");
table2.AddRow(row2);
row2 = table2.NewRow(2, "B");
table2.AddRow(row2);
string query = "Select * from [TBL] full outer join [TBL2] on [TBL].[col1]=[TBL2].[col3]";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreNotEqual(null, result.Values, "There should be only one result set");
Assert.AreEqual(4, result.Values.Columns.Count(), "There should be only one column");
Assert.AreEqual("col1", result.Values.Columns.ElementAt(0).ColumnName, "The expected column was not on the result set");
Assert.AreEqual("col2", result.Values.Columns.ElementAt(1).ColumnName, "The expected column was not on the result set");
Assert.AreEqual("col3", result.Values.Columns.ElementAt(2).ColumnName, "The expected column was not on the result set");
Assert.AreEqual("col4", result.Values.Columns.ElementAt(3).ColumnName, "The expected column was not on the result set");
Assert.AreEqual(4, affected, "There should be two row affected");
var items = result.Values.Records;
Assert.AreEqual(1, items.ElementAt(0)["col1"], "The selected value was not present on the Table");
Assert.AreEqual(2, items.ElementAt(0)["col2"], "The selected value was not present on the Table");
Assert.AreEqual(1, items.ElementAt(0)["col3"], "The selected value was not present on the Table");
Assert.AreEqual("A", items.ElementAt(0)["col4"], "The selected value was not present on the Table");
Assert.AreEqual(1, items.ElementAt(1)["col1"], "The selected value was not present on the Table");
Assert.AreEqual(5, items.ElementAt(1)["col2"], "The selected value was not present on the Table");
Assert.AreEqual(1, items.ElementAt(1)["col3"], "The selected value was not present on the Table");
Assert.AreEqual("A", items.ElementAt(1)["col4"], "The selected value was not present on the Table");
Assert.AreEqual(3, items.ElementAt(2)["col1"], "The selected value was not present on the Table");
Assert.AreEqual(4, items.ElementAt(2)["col2"], "The selected value was not present on the Table");
Assert.AreEqual(null, items.ElementAt(2)["col3"], "The selected value was not present on the Table");
Assert.AreEqual(null, items.ElementAt(2)["col4"], "The selected value was not present on the Table");
Assert.AreEqual(null, items.ElementAt(3)["col1"], "The selected value was not present on the Table");
Assert.AreEqual(null, items.ElementAt(3)["col2"], "The selected value was not present on the Table");
Assert.AreEqual(2, items.ElementAt(3)["col3"], "The selected value was not present on the Table");
Assert.AreEqual("B", items.ElementAt(3)["col4"], "The selected value was not present on the Table");
}
