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