private KeyValuePair <ISchema, List <Expression> > GetSchemaAndFieldExpressionsFromJoinTables(ITable innerTable, ITable outerTable, Expression innerRowExpression, Expression outerRowExpression) { ISchema schema = Memory.Database.NewSchema(); List <Expression> listOfFieldExpressions = new List <Expression>(); int numberOfOuterFields = outerTable.Schema.Fields.Count; int numberOfInnerFields = innerTable.Schema.Fields.Count; // add all outer fields to the schema for (int i = 0; i < numberOfOuterFields; i++) { var field = outerTable.Schema.Fields[i]; schema.AddField(field.Name, field.Type); Expression arraySelectorExpression = Expression.ArrayIndex(outerRowExpression, Expression.Constant(i)); listOfFieldExpressions.Add(arraySelectorExpression); } // add inner fields to the schema for (int i = 0; i < numberOfInnerFields; i++) { var field = innerTable.Schema.Fields[i]; schema.AddField(field.Name, field.Type); Expression arraySelectorExpression = Expression.ArrayIndex(innerRowExpression, Expression.Constant(i)); listOfFieldExpressions.Add(arraySelectorExpression); } return(new KeyValuePair <ISchema, List <Expression> >(schema, listOfFieldExpressions)); }
public void SetupTestWithDummyPlugin() { SetupDummyPlugin(); SetupProviderPluginDummyConnection(); // setup article table for export ISchema schema = _Database.NewSchema(); schema.AddField <string>("ArticleNumber"); schema.AddField <string>("Name1"); schema.AddField <decimal>("Price1"); schema.AddField <string>("UnitInternal"); schema.AddField <string>("UnitInvoice"); List <object[]> data = new List <object[]> { new object[] { "SaveTest01", "SaveTest01-Name1", 15.2M, "PCS", "PCS" }, new object[] { "SaveTest02", "SaveTest02-Name1", 17.95M, "PCS", "PCS" }, new object[] { "SaveTest03", "SaveTest03-Name1", 9.90M, "PCS", "PCS" }, }; ITable table = _Database.NewTable(schema, data); _Database.CreateTable(@"\ExportTests\Articles", table); }
public static void CreateRegistrationsTable(IDatabase db) { ISchema schema = db.NewSchema(); schema.AddField <int>("Id"); schema.AddField <int>("IdPerson"); schema.AddField <int>("IdCourse"); schema.AddField <DateTime>("DateOfRegistration"); List <object[]> data = new List <object[]> { new object[] { 5, 1, 2, new DateTime(2015, 2, 3) }, new object[] { 10, 1, 4, new DateTime(2015, 2, 3) }, new object[] { 15, 1, 6, new DateTime(2015, 2, 3) }, new object[] { 20, 3, 2, new DateTime(2015, 2, 3) }, new object[] { 25, 3, 6, new DateTime(2015, 2, 3) }, new object[] { 30, 5, 6, new DateTime(2015, 2, 3) }, new object[] { 55, 7, 6, new DateTime(2015, 2, 3) }, new object[] { 35, 7, 8, new DateTime(2015, 2, 3) }, new object[] { 40, 7, 10, new DateTime(2015, 2, 3) }, new object[] { 50, 9, 6, new DateTime(2015, 2, 3) }, new object[] { 45, 9, 10, new DateTime(2015, 2, 3) }, }; ITable table = db.NewTable(schema, data); db.CreateTable(@"\QueryLanguageTests\Registrations", table); }
public void JOIN_Integer_Field_Pair_With_Same_Hash_Values() { // create outer table (First) ISchema schema = _Database.NewSchema(); schema.AddField <int>("KeyOne"); schema.AddField <int>("KeyTwo"); schema.AddField <string>("Value"); List <object[]> data = new List <object[]> { new object[] { 1212369, 10, "First Bla" }, new object[] { 1212700, 10, "First Aha" }, new object[] { 1212700, 20, "First Soso" }, }; ITable table = _Database.NewTable(schema, data); _Database.CreateTable(@"\SameHashCheck\First", table); // create inner table (Second) ISchema schema2 = _Database.NewSchema(); schema2.AddField <int>("KeyOne"); schema2.AddField <int>("KeyTwo"); schema2.AddField <string>("Value"); List <object[]> data2 = new List <object[]> { new object[] { 55999, 10, "Second Huhu" }, new object[] { 66999, 20, "Second Haha" }, new object[] { 1212369, 10, "Second Bla" }, new object[] { 1212359, 240, "Second NOT OK!" }, }; ITable table2 = _Database.NewTable(schema2, data2); _Database.CreateTable(@"\SameHashCheck\Second", table2); // run the test string code = @" \SameHashCheck\Test = FROM \SameHashCheck\First AS f JOIN \SameHashCheck\Second AS s COMPARE s.KeyOne, s.KeyTwo TO f.KeyOne, f.KeyTwo SELECT ValueFirst = f.Value, ValueSecond = f.Value; "; _SyneryClient.Run(code); ITable destinationTable = _Database.LoadTable(@"\SameHashCheck\Test"); Assert.AreEqual(1, destinationTable.Count); }
public void SetupSpecificTest() { // prepare a table to have an existing table ISchema schema = _Database.NewSchema(); schema.AddField <int>("Id"); schema.AddField <string>("Firstname"); schema.AddField <string>("Secondname"); schema.AddField <string>("Lastname"); schema.AddField <bool>("IsMarried"); ITable table = _Database.NewTable(schema); table.Add(new object[] { 3, "Steve", "Patrik", "Lawrence", false }); table.Add(new object[] { 9, "Sivlia", null, "Dagustina", true }); _Database.CreateTable(@"\StatementTest\TableAddToExisting", table); }
public void DISTINCT_Integer_Field_Pair_With_Same_Hash_Values() { // create table with values ISchema schema = _Database.NewSchema(); schema.AddField <int>("KeyOne"); schema.AddField <int>("KeyTwo"); List <object[]> data = new List <object[]> { // these value-pairs get the same GetHashCode()-Value new object[] { 1212369, 10, }, new object[] { 1212359, 240, }, // add some more doublettes new object[] { 1212369, 10, }, new object[] { 1212369, 10, }, new object[] { 1212359, 240, }, new object[] { 1212359, 240, }, new object[] { 1212369, 10, }, new object[] { 1212359, 240, }, }; ITable table = _Database.NewTable(schema, data); _Database.CreateTable(@"\SameHashCheck\First", table); // run the test string code = @" \SameHashCheck\Test = FROM \SameHashCheck\First AS f DISTINCT; "; _SyneryClient.Run(code); ITable destinationTable = _Database.LoadTable(@"\SameHashCheck\Test"); Assert.AreEqual(2, destinationTable.Count); }
/// <summary> /// creates a large test table in the given database /// the IdJoin can be used to run some join tests with two larg tables /// </summary> /// <param name="db"></param> /// <param name="numberOfRows"></param> /// <param name="tableName"></param> public static void CreateLargeTestTable(IDatabase db, int numberOfRows = 50000, string tableName = @"\QueryLanguageTests\Logs") { Random random = new Random(); ISchema schema = db.NewSchema(); schema.AddField <int>("Id"); schema.AddField <int>("IdPerson"); schema.AddField <int>("IdCourse"); schema.AddField <int>("IdJoin"); schema.AddField <string>("First"); schema.AddField <string>("Second"); schema.AddField <string>("Third"); schema.AddField <string>("Fourth"); schema.AddField <string>("Fifth"); schema.AddField <bool>("IsActive"); List <object[]> data = new List <object[]>(); for (int i = 0; i < numberOfRows; i++) { data.Add( new object[] { random.Next(), //Id random.Next(5) * 2 + 1, // IdPerson random.Next(5) * 2, // IdCourse random.Next(numberOfRows), // IdJoin GetRandomSentence(random), // First GetRandomSentence(random), // Second GetRandomSentence(random), // Third GetRandomSentence(random), // Fourth GetRandomSentence(random), // Fifth random.Next(1) == 1 ? true : false // IsActive }); } ITable table = db.NewTable(schema, data); db.CreateTable(tableName, table); }
private ITable CreateNewTableFromRecord(IRecord record) { ISchema schema = Memory.Database.NewSchema(); ITable table = Memory.Database.NewTable(schema); foreach (var field in record.RecordType.Fields) { if (TypeHelper.IsPrimitiveType(field.Type)) { schema.AddField(field.Name, field.Type.UnterlyingDotNetType); } } return(table); }
public void Basic_DELETE_Statement_Works() { // setup article table that contains the articles that should be deleted ISchema schema = _Database.NewSchema(); schema.AddField <string>("ArticleNumber"); List <object[]> data = new List <object[]> { new object[] { "Test01", }, new object[] { "Test03", }, }; ITable table = _Database.NewTable(schema, data); // load the record set from before the deletion RecordSet articlesRecordSetBeforeDeletion = LoadArticlesRecordSetFromDummyPlugin(); // run synery command _Database.CreateTable(@"\ExportTests\Articles", table); string code = @" DELETE \\Connections\DummyConnection\Tables\LAG\Articles FROM \ExportTests\Articles END"; _SyneryClient.Run(code); // load the record set after the deletion RecordSet articlesRecordSetAfterDeletion = LoadArticlesRecordSetFromDummyPlugin(); // check wheter the articles from the table \ExportTests\Articles have been deleted Assert.AreEqual(articlesRecordSetBeforeDeletion.Count - data.Count, articlesRecordSetAfterDeletion.Count); }
public static void CreateCoursesTable(IDatabase db) { ISchema schema = db.NewSchema(); schema.AddField <int>("Id"); schema.AddField <string>("Name"); schema.AddField <string>("Description"); schema.AddField <decimal>("Price"); schema.AddField <DateTime>("DateOfStart"); schema.AddField <DateTime>("DateOfEnd"); List <object[]> data = new List <object[]> { new object[] { 2, "How to eat lobster", "You learn how to eas a lobster without getting dirty.", 199.90M, new DateTime(2015, 5, 1), new DateTime(2015, 5, 8) }, new object[] { 4, "Lobster cooking for dummies", "Don't be anxious to cook a lobster. In this course you learn how to do it right.", 279.50M, new DateTime(2015, 9, 5), new DateTime(2015, 9, 13) }, new object[] { 6, "Rhubarb cakes for beginners", "Learn how to bake a rhubarb cake without getting poisoned.", 99.95M, new DateTime(2015, 7, 23), new DateTime(2015, 7, 30) }, new object[] { 8, "Rhubarb cakes for professionals", "Learn how to bake like your grandmother.", 529.10M, new DateTime(2015, 11, 12), new DateTime(2015, 11, 17) }, new object[] { 10, "Blowfish", "Learn how to poison your guests with a blowfish.", 789.25M, new DateTime(2015, 12, 3), new DateTime(2015, 12, 10) }, }; ITable table = db.NewTable(schema, data); db.CreateTable(@"\QueryLanguageTests\Courses", table); }
public void UPDATE_Statement_From_Table_With_Different_Structure_Works() { // setup article table with the same structure for export ISchema schema = _Database.NewSchema(); schema.AddField <string>("ArticleNumber"); schema.AddField <string>("Name1"); schema.AddField <decimal>("Price1"); schema.AddField <string>("UnitInternal"); schema.AddField <string>("UnitInvoice"); List <object[]> data = new List <object[]> { new object[] { "Test01", "UpdateTest01-Name1", 1.1M, "PCS", "PCS" }, new object[] { "Test03", "UpdateTest03-Name1", 3.1M, "PCS", "PCS" }, }; ITable table = _Database.NewTable(schema, data); _Database.CreateTable(@"\ExportTests\Articles", table); // run synery command string code = @" UPDATE \\Connections\DummyConnection\Tables\LAG\Articles FROM \ExportTests\Articles END"; _SyneryClient.Run(code); // check result // load the articles record set after the update of the changed articles RecordSet articlesRecordSetAfterExecutionOfSyneryCode = LoadArticlesRecordSetFromDummyPlugin(); // create a query function that looks for the first record updated in the UPDATE statement Func <Record, bool> firstRecordQuery = (Record r) => { return((string)r["ArticleNumber"] == "Test01" && (string)r["Name1"] == "UpdateTest01-Name1" && (decimal)r["Price1"] == 1.1M && (string)r["UnitInternal"] == "PCS" && (string)r["UnitInvoice"] == "PCS"); }; // create a query function that looks for the second record that is expected to be untouched Func <Record, bool> secondRecordQuery = (Record r) => { return((string)r["ArticleNumber"] == "Test02" && (string)r["Name1"] == "Test02-Name1" && (decimal)r["Price1"] == 2M && (string)r["UnitInternal"] == "PCS" && (string)r["UnitInvoice"] == "PCS"); }; // create a query function that looks for the third record updated in the UPDATE statement Func <Record, bool> thirdRecordQuery = (Record r) => { return((string)r["ArticleNumber"] == "Test03" && (string)r["Name1"] == "UpdateTest03-Name1" && (decimal)r["Price1"] == 3.1M && (string)r["UnitInternal"] == "PCS" && (string)r["UnitInvoice"] == "PCS"); }; // check wheter one record exists that matches the query conditions Assert.AreEqual(1, articlesRecordSetAfterExecutionOfSyneryCode.Count(firstRecordQuery)); Assert.AreEqual(1, articlesRecordSetAfterExecutionOfSyneryCode.Count(secondRecordQuery)); Assert.AreEqual(1, articlesRecordSetAfterExecutionOfSyneryCode.Count(thirdRecordQuery)); }
// These methods can be called from a test (or SetUp) to get a set of dummy data public static void CreatePeopleTable(IDatabase db) { Random random = new Random(); ISchema schema = db.NewSchema(); schema.AddField <int>("Id"); schema.AddField <string>("Firstname"); schema.AddField <string>("Lastname"); schema.AddField <string>("Title"); schema.AddField <bool>("IsMale"); schema.AddField <int>("NumberOfChildren"); schema.AddField <double>("Size"); schema.AddField <decimal>("Weight"); schema.AddField <DateTime>("DateOfBirth"); schema.AddField <char>("FirstLetter"); schema.AddField <DateTime>("DateOfDeath"); schema.AddField <string>("Notes"); List <object[]> data = new List <object[]> { new object[] { 1, "Roger", "Guillet", "Mr.", true, 2, 1.70, 81.3M, new DateTime(1987, 12, 15), 'R', null, "Some text..." }, new object[] { 3, "Sandra", "Bloch", "Mrs.", false, 1, 1.67, 68.9M, new DateTime(1989, 6, 2), 'S', null, "Some notes..." }, new object[] { 5, "Pete", "Rhubarb", "Mr.", true, 0, 1.75, 85.1M, new DateTime(1981, 3, 17), 'P', null, null }, new object[] { 7, "Kate", "Price", "Mrs.", false, 3, 1.70, 73.8M, new DateTime(1976, 9, 13), 'K', new DateTime(2014, 6, 22), null }, new object[] { 9, "Silvia", "Meyer", "Mrs.", false, 1, 1.63, 62.7M, new DateTime(1992, 1, 22), 'S', null, "Simething important..." }, new object[] { 11, "Petra", "Stalone", "Mrs.", false, 2, 1.7, 73.5M, new DateTime(1984, 6, 9), 'P', null, null }, }; ITable table = db.NewTable(schema, data); db.CreateTable(@"\QueryLanguageTests\People", table); }
public void JOIN_With_Null_Values_Works() { // create outer table (First) ISchema schema = _Database.NewSchema(); schema.AddField <int>("Id"); schema.AddField <int>("IdSecond"); schema.AddField <string>("NullValue"); List <object[]> data = new List <object[]> { new object[] { 5, 2, null }, new object[] { 10, 4, null }, new object[] { 15, 4, null }, }; ITable table = _Database.NewTable(schema, data); _Database.CreateTable(@"\NullJoinTest\First", table); // create inner table (Second) ISchema schema2 = _Database.NewSchema(); schema2.AddField <int>("Id"); schema2.AddField <string>("NullValue"); List <object[]> data2 = new List <object[]> { new object[] { 2, null }, new object[] { 4, null }, }; ITable table2 = _Database.NewTable(schema2, data2); _Database.CreateTable(@"\NullJoinTest\Second", table2); // run the test string code = @" \QueryLanguageTests\Test = FROM \NullJoinTest\First AS f JOIN \NullJoinTest\Second AS s COMPARE s.Id, s.NullValue TO f.IdSecond, f.NullValue SELECT IdFirst = f.Id, IdSecond = f.Id; "; _SyneryClient.Run(code); // get expected result for validating the test result ITable outerTable = _Database.LoadTable(@"\NullJoinTest\First"); ITable innerTable = _Database.LoadTable(@"\NullJoinTest\Second"); IEnumerable <object[]> result = outerTable.Join(innerTable, o => { return(new object[] { o[1], o[2] }); }, i => { return(new object[] { i[0], i[1] }); }, (o, i) => { return(new object[] { o[0], i[0], }); }, new ObjectArrayEqualityComparer()); ITable destinationTable = _Database.LoadTable(@"\QueryLanguageTests\Test"); Assert.AreEqual(result.Count(), destinationTable.Count); }