public void SomeFieldsSelectTest()
{
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);
string query = "Select col2 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.AreNotEqual(null, result.Values, "There should be only one result set");
Assert.AreEqual(1, result.Values.Columns.Count(), "There should be only one column");
Assert.AreEqual("col2", result.Values.Columns.ElementAt(0).ColumnName, "The expected column was not on the result set");
Assert.AreEqual(1, result.Values.Records.Count(), "There should be only one row");
Assert.AreEqual(2, result.Values.Records.ElementAt(0).ItemArray[0], "The selected value was not present on the Table");
}
public void GroupByWithCount()
{
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,Count(*) 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(2, result.Values.Records.First().ItemArray[1], "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");
Assert.AreEqual(3, result.Values.Records.ElementAt(1).ItemArray[1], "The selected value was not present on the Table");
}
public void TopDistinctOrderSelectTest()
{
//The top clause should be applied AFTER the distinct
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(2));
table.AddRow(table.NewRow(2));
table.AddRow(table.NewRow(3));
table.AddRow(table.NewRow(3));
table.AddRow(table.NewRow(3));
string query = "Select distinct top 2 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(2, result.Values.Records.ElementAt(1).ItemArray[0], "The selected value was not present on the Table");
}
public void UpdateWithOutput()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.AddColumn(new Column("B", typeof(string)));
var r = table.NewRow(3, "asd");
table.AddRow(r);
string query = "Update [TBL] set A=2, B='qwe' output updated.*";
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.AreNotEqual(null, result.Values, "One result should have been returned");
var resultSet = result.Values;
Assert.AreEqual(2, resultSet.Columns.Count(), "The result should have two columns");
Assert.AreEqual("A", resultSet.Columns.ElementAt(0).ColumnName, "Failed to find the expected column");
Assert.AreEqual("B", resultSet.Columns.ElementAt(1).ColumnName, "Failed to find the expected column");
Assert.AreEqual(1, resultSet.Records.Count(), "There should be one row");
var row = resultSet.Records.ElementAt(0);
Assert.AreEqual(2, row["A"], "The expected result was not present in the row");
Assert.AreEqual("qwe", row["B"], "The expected result was not present in the row");
}
public void BitColumnShouldAllowComparisonWithStrings()
{
var db = new Database();
var interpreter = new SQLInterpreter(db);
interpreter.Execute(@"
create table Customer
(
Id int primary key identity,
[Name] nvarchar(50) not null,
[Timestamp] datetime,
[Enabled] bit default 1
)");
interpreter.Execute("insert into Customer ([Name]) values ('Richo'),('Diego'),('Sofía')");
Action <string, int> assert = (query, expected) =>
{
var result = interpreter.Execute(query)[0];
Assert.AreEqual(expected, result.RowsAffected);
Assert.AreEqual(expected, result.Values.Records.Count());
};
assert("select * from Customer where [Enabled] = '1'", 3);
assert("select * from Customer where [Enabled] > '1'", 0);
assert("select * from Customer where [Enabled] < '3'", 3);
assert("select * from Customer where [Enabled] >= '2'", 0);
assert("select * from Customer where [Enabled] <= '2'", 3);
assert("select * from Customer where '1' = [Enabled]", 3);
assert("select * from Customer where '1' < [Enabled]", 0);
assert("select * from Customer where '3' > [Enabled]", 3);
assert("select * from Customer where '2' <= [Enabled]", 0);
assert("select * from Customer where '2' >= [Enabled]", 3);
}
public void InsertWithSelectiveOutput()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.AddColumn(new Column("B", typeof(string)));
string query = "Insert into [TBL] output inserted.A values(3,'asd')";
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.AreNotEqual(null, result.Values, "One result should have been returned");
var resultSet = result.Values;
Assert.AreEqual(1, resultSet.Columns.Count(), "The result should have two columns");
Assert.AreEqual("A", resultSet.Columns.ElementAt(0).ColumnName, "Failed to find the expected column");
Assert.AreEqual(1, resultSet.Records.Count(), "There should be one row");
var row = resultSet.Records.ElementAt(0);
Assert.AreEqual(3, row["A"], "The expected result was not present in the row");
}
public void TopPercentUpdateTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
for (int i = 0; i < 200; i++)
{
var row = table.NewRow(i);
table.AddRow(row);
}
string query = "Update TOP(50) PERCENT [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 row affected");
int count = 0;
for (int i = 0; i < 200; i++)
{
if (table.GetRow(i)["ID"].Equals(2))
{
count++;
}
}
Assert.AreEqual(100, affected, "There was suposed to be 100 rows with the updated ID");
}
public void RecursiveFKAppliedBeforePKShouldWork()
{
string script = @"
CREATE TABLE [dbo].[LogException]
(
[id] INT IDENTITY (1, 1) NOT NULL,
[name] NVARCHAR (512) NOT NULL,
[message] NVARCHAR (512) NULL,
[stackTrace] NVARCHAR (4000) NULL,
[source] NVARCHAR (256) NULL,
[innerException] INT NULL,
CONSTRAINT [FK_LogException_ToLogException] FOREIGN KEY ([innerException]) REFERENCES [dbo].[LogException] ([id]),
PRIMARY KEY CLUSTERED ([id] ASC)
)";
var db = new Database();
var visitor = new SQLInterpreter(db);
visitor.Execute(script);
var table = db.GetTable("LogException");
Assert.IsNotNull(table, "The table should exist");
CollectionAssert.AreEqual(new[] { "id" },
table.PrimaryKey.Select(c => c.ColumnName).ToArray(),
"The PK should be configured correctly");
Assert.IsTrue(db.ContainsConstraint("FK_LogException_ToLogException"));
}
public void TableCreationTest()
{
string script = "CREATE TABLE CUSTOMERS("
+ "ID INT NOT NULL,"
+ "NAME VARCHAR (20) NOT NULL,"
+ "BIRTHDAY DATETIME NOT NULL,"
+ "ADDRESS CHAR (25) ,"
+ "SALARY DECIMAL (18, 2),"
+ "PRIMARY KEY (ID));";
var db = new Database();
var visitor = new SQLInterpreter(db);
var result = visitor.Execute(script)[0];
Assert.AreEqual(0, result.RowsAffected);
Assert.IsTrue(db.ContainsTable("CUSTOMERS"), "The table must be created");
var table = db.GetTable("CUSTOMERS");
Assert.IsTrue(table.ContainsColumn("ID"));
Assert.AreEqual(typeof(int), table.GetColumn("ID").DataType);
Assert.IsTrue(table.ContainsColumn("NAME"));
Assert.AreEqual(typeof(string), table.GetColumn("NAME").DataType);
Assert.IsTrue(table.ContainsColumn("BIRTHDAY"));
Assert.AreEqual(typeof(DateTime), table.GetColumn("BIRTHDAY").DataType);
Assert.IsTrue(table.ContainsColumn("ADDRESS"));
Assert.AreEqual(typeof(string), table.GetColumn("ADDRESS").DataType);
Assert.IsTrue(table.ContainsColumn("SALARY"));
Assert.AreEqual(typeof(decimal), table.GetColumn("SALARY").DataType);
Assert.IsTrue(table.PrimaryKey.Length == 1, "The Primary Key is missing!");
Assert.AreEqual(table.GetColumn("ID"), table.PrimaryKey[0]);
}
public void FKToNonExistentColumnShouldFail()
{
var db = new Database();
var visitor = new SQLInterpreter(db);
{
string script = "CREATE TABLE [Client] (ID2 int NOT NULL PRIMARY KEY)";
visitor.Execute(script);
}
{
var script = @"
CREATE TABLE [dbo].[TBL]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[ClientId] [int] NOT NULL,
CONSTRAINT [FK_TBL_CLIENT] FOREIGN KEY ([ClientId]) REFERENCES [Client]([Id])
)
";
Assert.ThrowsException <NullReferenceException>(() =>
{
visitor.Execute(script);
});
Assert.ThrowsException <InvalidOperationException>(
() => { db.GetTable("TBL"); },
"Table 'TBL' should not exist");
}
}
public void ComputedColumnWithCaseCreationTest()
{
string script = "CREATE TABLE [TBL]( " +
"[num] INT NOT NULL," +
" [calc] AS" +
" CASE WHEN num < 0" +
" THEN(num * 2)" +
" ELSE(num +2)" +
" END)";
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(3, dr1["calc"]);
dr1["num"] = -5;
Assert.AreEqual(-10, dr1["calc"]);
}
public void UpdateWhereEquals()
{
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]=5 where [ID] = 1";
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(1, affected, "There should be 1 row affected");
int count = 0;
for (int i = 0; i < 100; i++)
{
if (table.GetRow(i)["ID"].Equals(5))
{
count++;
}
}
Assert.AreEqual(2, count, "There was suposed to be 2 rows with the updated ID");
}
public void MultipleIdentityShouldFail()
{
string script = @"
CREATE TABLE [dbo].TBL
(
[id] INT IDENTITY (1, 1) ,
[foo] INT IDENTITY (1, 1),
CONSTRAINT [PK2] PRIMARY KEY (id)
)";
var db = new Database();
var visitor = new SQLInterpreter(db);
Assert.ThrowsException <ArgumentException>(() =>
{
/*
* INFO(Tera): This should fail. SQL Server 2014 throws the following error:
* Msg 2744, Level 16, State 2, Line 1
* Multiple identity columns specified for table 'TBL'. Only one identity column per table is allowed.
*/
visitor.Execute(script);
});
Assert.ThrowsException <InvalidOperationException>(
() => { db.GetTable("TBL"); },
"Table 'TBL' should not exist");
}
public void PKConstraintWithMultipleColumns()
{
string script = @"
CREATE TABLE TBL
(
[ID1] INT NOT NULL,
[ID2] INT NOT NULL
CONSTRAINT PK_TBL PRIMARY KEY ([ID1], [ID2])
)";
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("ID1"));
Assert.AreEqual(typeof(int), table.GetColumn("ID1").DataType);
Assert.IsTrue(table.ContainsColumn("ID2"));
Assert.AreEqual(typeof(int), table.GetColumn("ID2").DataType);
Assert.IsTrue(table.PrimaryKey.Length == 2, "The Primary Key is missing!");
Assert.AreEqual(table.GetColumn("ID1"), table.PrimaryKey[0]);
Assert.AreEqual(table.GetColumn("ID2"), table.PrimaryKey[1]);
}
public void DefaultValuesTableCreationTest()
{
string script = "Create table [TBL](" +
"data int," +
"col1 int DEFAULT 3," +
"col2 varchar(3) DEFAULT 'asd'," +
"col3 bit DEFAULT 1)";
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.ContainsColumn("col2"));
Assert.AreEqual(typeof(string), table.GetColumn("col2").DataType);
Assert.IsTrue(table.ContainsColumn("col3"));
Assert.AreEqual(typeof(bool), table.GetColumn("col3").DataType);
var dr = table.NewRow(("data", 0));
Assert.AreEqual(0, dr["data"], "The default value was not present on the row");
Assert.AreEqual(3, dr["col1"], "The default value was not present on the row");
Assert.AreEqual("asd", dr["col2"], "The default value was not present on the row");
Assert.AreEqual(true, dr["col3"], "The default value was not present on the row");
}
public void BatchInsertTest()
{
List <Tuple <int, int> > testData = new List <Tuple <int, int> >();
for (int i = 0; i < 5; i++)
{
for (int j = 5; j < 10; j++)
{
testData.Add(new Tuple <int, int>(i, j));
}
}
Func <Tuple <int, int>, string> printTuple = (t) => string.Format("( {0} , {1} )", t.Item1, t.Item2);
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 =
string.Format(
"Insert into [TBL] values {0}", string.Join(", ", testData.Select(t => printTuple(t))));
SQLInterpreter interpreter = new SQLInterpreter(db);
var result = interpreter.Execute(query)[0];
int affected = result.RowsAffected;
Assert.AreEqual(testData.Count, affected, "The amount of rows affected is wrong");
Assert.AreEqual(testData.Count, table.Rows.Count(), "All the test data should have been inserted");
for (int i = 0; i < testData.Count; i++)
{
Assert.AreEqual(testData[i].Item1, table.GetRow(i)["A"], "The inserted value was not present on the table");
Assert.AreEqual(testData[i].Item2, table.GetRow(i)["B"], "The inserted value was not present on the table");
}
}
public void AutomaticallyGeneratedConstraintNamesShouldNeverClash()
{
string script = @"
CREATE TABLE [dbo].[Client]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY,
[Name] NVARCHAR(50) NOT NULL,
[ExternalId] NVARCHAR(40) NOT NULL,
[InternalId] NVARCHAR(40) NOT NULL UNIQUE,
CONSTRAINT PK_Client UNIQUE(Name),
CONSTRAINT UC_Client_ExternalId UNIQUE(ExternalId)
)
";
var db = new Database();
var visitor = new SQLInterpreter(db);
visitor.Execute(script);
var table = db.GetTable("Client");
Assert.IsNotNull(table, "The table should be created");
CollectionAssert.AreEqual(new[] { "Id" },
table.PrimaryKey.Select(c => c.ColumnName).ToArray(),
"The PK should be configured correctly");
string[] cols = new[] { "Id", "Name", "ExternalId", "InternalId" };
for (int i = 0; i < cols.Length; i++)
{
var col = table.GetColumn(cols[i]);
Assert.IsNotNull(col, "The column should exist");
Assert.IsTrue(col.Unique, "Unique constraint should be set");
Assert.IsFalse(col.AllowDBNull, "The column should not allow null");
}
}
public void RightOuterJoinSelect()
{
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] right 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(3, 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(null, items.ElementAt(2)["col1"], "The selected value was not present on the Table");
Assert.AreEqual(null, items.ElementAt(2)["col2"], "The selected value was not present on the Table");
Assert.AreEqual(2, items.ElementAt(2)["col3"], "The selected value was not present on the Table");
Assert.AreEqual("B", items.ElementAt(2)["col4"], "The selected value was not present on the Table");
}
public void TestFKSetNullDeleteAndUpdateRules()
{
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 NULL,
CONSTRAINT [FK_T1_T2] FOREIGN KEY ([T1]) REFERENCES T1 ([Id])
ON DELETE SET NULL
ON UPDATE SET NULL
);
";
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) =>
{
var row = t2.NewRow(f1, f2);
t2.AddRow(row);
};
t2_insert("A", 1);
t2_insert("B", 2);
t2_insert("C", 3);
t2_insert("D", null);
Assert.ThrowsException <ConstraintException>(() =>
{
t2_insert("E", 4);
});
t1.FindRow(1).Delete();
Assert.IsNull(t1.FindRow(1), "The parent row should be removed");
Assert.IsNotNull(t2.FindRow(1), "The child row should not be removed");
Assert.AreEqual(null, t2.FindRow(1)["T1"], "The child row FK should be null");
{
var row = t1.FindRow(2);
row["Id"] = 4;
}
Assert.IsNotNull(t1.FindRow(4), "The parent row should be updated");
Assert.AreEqual(null, t2.FindRow(2)["T1"], "The child row FK should be null");
}
public void MultipartTableNamesShouldUseTheLastIdentifier()
{
var script = "CREATE TABLE [dbo].[Client] (ID int NOT NULL PRIMARY KEY)";
var db = new Database();
var visitor = new SQLInterpreter(db);
visitor.Execute(script);
Assert.IsTrue(db.ContainsTable("Client"));
}
public void CommaShouldEqualsCrossJoin()
{
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(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(3, "B");
table2.AddRow(row2);
string crossJoinQuery = "Select * from [TBL] cross join [TBL2]";
string commaQuery = "Select * from [TBL],[TBL2]";
SQLInterpreter interpreter = new SQLInterpreter(db);
var crossJoinResult = interpreter.Execute(crossJoinQuery)[0];
var commaResult = interpreter.Execute(commaQuery)[0];
Assert.AreEqual(crossJoinResult.RowsAffected, commaResult.RowsAffected, "The affected rows should be the same");
Assert.AreNotEqual(null, crossJoinResult.Values, "There should be one result set");
Assert.AreNotEqual(null, commaResult.Values, "There should be one result set");
Assert.AreEqual(crossJoinResult.Values.Columns.Count(), commaResult.Values.Columns.Count(), "There should be the same amount of columns");
for (int i = 0; i < crossJoinResult.Values.Columns.Count(); i++)
{
Assert.AreEqual(crossJoinResult.Values.Columns.ElementAt(i).ColumnName, commaResult.Values.Columns.ElementAt(i).ColumnName, "There was a column missmatch on the result set");
}
Assert.AreEqual(crossJoinResult.Values.Records.Count(), commaResult.Values.Records.Count(), "There should be the same amount of records ");
for (int i = 0; i < crossJoinResult.Values.Records.Count(); i++)
{
Assert.IsTrue(
crossJoinResult.Values.Records.ElementAt(i).ItemArray
.SequenceEqual(commaResult.Values.Records.ElementAt(i).ItemArray),
"There was a row missmatch on the set");
}
}
public void SelectOnSubquery()
{
var db = new Database();
var interpreter = new SQLInterpreter(db);
var result = interpreter.Execute("select * from (select 3 as A) t where t.A = 3");
Assert.AreEqual(1, result[0].Values.Records.Count(), "The result set should only contain one element");
Assert.AreEqual(3, result[0].Values.Records.ElementAt(0)["A"], "The result should be 3");
}
public void InvalidSyntaxShouldThrowAnException()
{
var script = "create table table (a int)";
var db = new Database();
var visitor = new SQLInterpreter(db);
Assert.ThrowsException <ParseException>(() =>
{
visitor.Execute(script);
});
Assert.ThrowsException <InvalidOperationException>(
() => { db.GetTable("table"); },
"Table 'table' should not exist");
}
public void InsertWithoutOutputShouldNotReturnValues()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("A", typeof(int)));
table.AddColumn(new Column("B", typeof(string)));
string query = "Insert into [TBL] values(3,'asd')";
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(null, result.Values, "No result should have been returned");
}
public void BasicInsertTest()
{
var db = new Database();
Table table = db.AddTable("TBL");
table.AddColumn(new Column("ID", typeof(int)));
string query = "Insert into [TBL] values(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, table.Rows.Count(), "There should be one row on the table");
Assert.AreEqual(3, table.GetRow(0)["ID"], "The inserted value was not present on the table");
}
public void InlinePKTableCreationTest()
{
string script = "Create table [TBL](ID int PRIMARY KEY)";
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]);
}
public void NullableTableCreationTest()
{
string script = "Create table [TBL](col1 int NOT NULL,col2 int NULL)";
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.IsFalse(table.GetColumn("col1").AllowDBNull, "This column should not allow nulls");
Assert.IsTrue(table.ContainsColumn("col2"));
Assert.AreEqual(typeof(int), table.GetColumn("col2").DataType);
Assert.IsTrue(table.GetColumn("col2").AllowDBNull, "This column should allow nulls");
}
public void BasicTableCreationTest()
{
string script = "Create table [TBL](col1 int,col2 varchar(3),col3 bit)";
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.ContainsColumn("col2"));
Assert.AreEqual(typeof(string), table.GetColumn("col2").DataType);
Assert.IsTrue(table.ContainsColumn("col3"));
Assert.AreEqual(typeof(bool), table.GetColumn("col3").DataType);
}
public void MultipleInlinedPKConstraintsShouldFail()
{
/*
* INFO(Richo): This should fail. SQL Server 2016 throws the following error:
* Cannot add multiple PRIMARY KEY constraints to table 'TBL'.
*/
string script = "Create table [TBL](ID int PRIMARY KEY,ID2 int PRIMARY KEY)";
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 IdentityPKWithoutArgsShouldUseDefaultValues()
{
string script = @"CREATE TABLE [dbo].[Client] ([Id] INT NOT NULL PRIMARY KEY IDENTITY)";
var db = new Database();
var visitor = new SQLInterpreter(db);
visitor.Execute(script);
var table = db.GetTable("Client");
Assert.IsNotNull(table, "The table should be created");
var col = table.GetColumn("Id");
Assert.IsNotNull(col, "The PK should be valid");
Assert.IsTrue(col.AutoIncrement, "Autoincrement should be set");
Assert.AreEqual(1, col.AutoIncrementSeed, "Autoincrement seed should be 1");
Assert.AreEqual(1, col.AutoIncrementStep, "Autoincrement step should be 1");
}
