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"); }