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));
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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);
        }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        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);
        }
Esempio n. 10
0
        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);
        }
Esempio n. 11
0
        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));
        }
Esempio n. 12
0
        // 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);
        }
Esempio n. 13
0
        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);
        }