public void SqlPropertyNoStoreNonIdentifierGrouping([DataSources] string context)
{
using (new FirebirdQuoteMode(FirebirdIdentifierQuoteMode.Auto))
using (var db = GetDataContext(context))
using (db.CreateLocalTable(new []
{
new DynamicTablePrototype {
NotIdentifier = 77, Value = 5
},
new DynamicTablePrototype {
NotIdentifier = 77, Value = 5
}
}))
{
var query =
from d in db.GetTable <DynamicTable>()
group d by new { NI = Sql.Property <int>(d, TestProvName.IsiSeries(context) ? "NotIdentifier" : "Not Identifier") }
into g
select new
{
g.Key.NI,
Count = g.Count(),
Sum = g.Sum(i => Sql.Property <int>(i, TestProvName.IsiSeries(context) ? "SomeValue" : "Some Value"))
};
var result = query.ToArray();
Assert.AreEqual(77, result[0].NI);
Assert.AreEqual(2, result[0].Count);
Assert.AreEqual(10, result[0].Sum);
}
}
private StringTestTable[] GetStringData([DataSources] string context)
{
var provider = GetProviderName(context, out var _);
// filter out null-character test cases for servers/providers without support
if (context.Contains(ProviderName.PostgreSQL) ||
provider == ProviderName.DB2 ||
context == ProviderName.DB2 + ".LinqService" ||
TestProvName.IsiSeries(provider) ||
TestProvName.IsiSeries(context) ||
context.Contains("SQLite") ||
provider == ProviderName.SqlCe ||
context.Contains(ProviderName.SapHana))
{
return(StringTestData.Where(_ => !(_.NString ?? string.Empty).Contains("\0")).ToArray());
}
// I wonder why
if (context.Contains(ProviderName.Firebird))
{
return(StringTestData.Where(_ => !(_.NString ?? string.Empty).Contains("\xA0")).ToArray());
}
// also strange
if (context.Contains(TestProvName.AllInformix))
{
return(StringTestData.Where(_ => !(_.NString ?? string.Empty).Contains("\0") &&
!(_.NString ?? string.Empty).Any(c => (int)c > byte.MaxValue)).ToArray());
}
return(StringTestData);
}
private static void AssertString(string?expected, string?actual, string provider, bool isIDS)
{
if (expected != null)
{
if (TestProvName.IsiSeriesOleDb(provider))
{
expected = expected.TrimEnd(' ');
}
switch (provider)
{
case ProviderName.Sybase:
case ProviderName.SybaseManaged:
case ProviderName.SqlCe:
expected = expected.TrimEnd(' ');
break;
case ProviderName.Informix:
expected = isIDS ? expected : expected.TrimEnd('\t', ' ');
break;
}
}
Assert.AreEqual(expected, actual);
}
private static void AssertDateTimeOffset(DateTimeOffset?expected, DateTimeOffset?actual, string provider)
{
if (expected != null)
{
if (provider.Contains(ProviderName.PostgreSQL))
{
expected = expected.Value.AddTicks(-expected.Value.Ticks % 10);
}
}
if (provider != ProviderName.SqlServer2000 &&
provider != ProviderName.SqlServer2005 &&
provider != ProviderName.SqlCe &&
!provider.Contains(ProviderName.Informix) &&
provider != ProviderName.Firebird &&
provider != TestProvName.Firebird3 &&
provider != ProviderName.MySql &&
provider != ProviderName.MySqlConnector &&
provider != TestProvName.MySql55 &&
provider != TestProvName.MariaDB &&
!provider.StartsWith("Access") &&
provider != ProviderName.SQLiteClassic &&
provider != TestProvName.SQLiteClassicMiniProfilerMapped &&
provider != TestProvName.SQLiteClassicMiniProfilerUnmapped &&
provider != ProviderName.SQLiteMS &&
provider != ProviderName.Sybase &&
provider != ProviderName.SybaseManaged &&
provider != ProviderName.DB2 &&
!TestProvName.IsiSeries(provider) &&
!provider.StartsWith(ProviderName.SapHana))
{
Assert.AreEqual(expected, actual);
}
}
public void CreateDatabase([CreateDatabaseSources] string context)
{
if (TestProvName.IsiSeries(context))
{
var script = context.Contains("GAS") ? "DB2iSeriesGAS" : "DB2iSeries";
RunScript(context, "\nGO\n", script);
}
}
private static bool SkipChar([DataSources] string context)
{
return(context == ProviderName.SqlCe ||
context == ProviderName.SqlCe + ".LinqService" ||
context == ProviderName.DB2 ||
context == ProviderName.DB2 + ".LinqService" ||
TestProvName.IsiSeries(context) ||
context.Contains(ProviderName.PostgreSQL) ||
context == ProviderName.MySql ||
context == ProviderName.MySql + ".LinqService" ||
context == ProviderName.MySqlConnector ||
context == ProviderName.MySqlConnector + ".LinqService" ||
context == TestProvName.MySql55 ||
context == TestProvName.MySql55 + ".LinqService" ||
context == TestProvName.MariaDB ||
context == TestProvName.MariaDB + ".LinqService");
}
public void ProviderConnectionStringConstructorTest2([DataSources(false)] string context)
{
using (var db = (TestDataConnection)GetDataContext(context))
using (var db1 = new DataContext(db.DataProvider.Name, "BAD"))
{
if (!TestProvName.IsiSeriesAccessClient(context))
{
Assert.Throws(typeof(ArgumentException), () => db1.GetTable <Child>().ToList());
}
#if NETFRAMEWORK
else
{
Assert.Throws(typeof(IBM.Data.DB2.iSeries.iDB2InvalidConnectionStringException), () => db1.GetTable <Child>().ToList());
}
#endif
}
}
private static void AssertNChar(char?expected, char?actual, string provider)
{
if (expected != null)
{
if (expected == ' ' &&
(provider == ProviderName.MySql ||
provider == ProviderName.MySqlConnector ||
provider == TestProvName.MariaDB ||
provider == TestProvName.MySql55 ||
TestProvName.IsiSeries(provider) && !TestProvName.IsiSeriesDB2Connect(provider)
// after migration to 2.4.126 provider + SPS4, hana or provider started to trim spaces on insert for some reason
|| provider.StartsWith(ProviderName.SapHana)))
{
expected = '\0';
}
}
Assert.AreEqual(expected, actual);
}
private static void AssertNString(string?expected, string?actual, string provider)
{
if (expected != null)
{
if (TestProvName.IsiSeriesOleDb(provider))
{
expected = expected.TrimEnd(' ');
}
if (provider == ProviderName.Sybase ||
provider == ProviderName.SybaseManaged ||
provider == ProviderName.SqlCe)
{
expected = expected.TrimEnd(' ');
}
}
if (!provider.Contains(ProviderName.Informix))
{
Assert.AreEqual(expected, actual);
}
}
private static char GetParameterToken([MergeDataContextSource] string context)
{
if (TestProvName.IsiSeriesODBC(context) || TestProvName.IsiSeriesOleDb(context))
{
return('?');
}
switch (context)
{
case ProviderName.SapHanaOdbc:
case ProviderName.Informix:
return('?');
case ProviderName.SapHanaNative:
case TestProvName.Oracle11Managed:
case TestProvName.Oracle11Native:
case ProviderName.OracleManaged:
case ProviderName.OracleNative:
return(':');
}
return('@');
}
public void SqlPropertyNoStoreNonIdentifier([DataSources] string context)
{
using (new FirebirdQuoteMode(FirebirdIdentifierQuoteMode.Auto))
using (var db = GetDataContext(context))
using (db.CreateLocalTable(new []
{
new DynamicTablePrototype {
NotIdentifier = 77
}
}))
{
var query =
from d in db.GetTable <DynamicTable>()
select new
{
NI = Sql.Property <int>(d, TestProvName.IsiSeries(context) ? "NotIdentifier" : "Not Identifier")
};
var result = query.ToArray();
Assert.AreEqual(77, result[0].NI);
}
}
public void DB2Test([IncludeDataSources(ProviderName.DB2)] string context)
{
using (var conn = new DataConnection(context))
{
var sp = conn.DataProvider.GetSchemaProvider();
var dbSchema = sp.GetSchema(conn);
var table = dbSchema.Tables.Single(t => t.IsDefaultSchema && t.TableName == "ALLTYPES");
if (TestProvName.IsiSeries(context))
{
var binaryType = TestProvName.IsiSeriesAccessClient(context) ? "BINARY(20)" : "BINARY";
Assert.That(table.Columns.Single(c => c.ColumnName == "BINARYDATATYPE").ColumnType, Is.EqualTo(binaryType));
Assert.That(table.Columns.Single(c => c.ColumnName == "VARBINARYDATATYPE").ColumnType, Is.EqualTo("VARBIN"));
}
else
{
Assert.That(table.Columns.Single(c => c.ColumnName == "BINARYDATATYPE").ColumnType, Is.EqualTo("CHAR (5) FOR BIT DATA"));
Assert.That(table.Columns.Single(c => c.ColumnName == "VARBINARYDATATYPE").ColumnType, Is.EqualTo("VARCHAR (5) FOR BIT DATA"));
}
}
}
public void CharTrimming([DataSources(TestProvName.AllInformix)] string context)
{
using (var db = GetDataContext(context))
{
var lastId = db.GetTable <CharTestTable>().Select(_ => _.Id).Max();
try
{
var testData = GetCharData(context);
foreach (var record in testData)
{
var query = db.GetTable <CharTestTable>().Value(_ => _.NChar, record.NChar);
if (!SkipChar(context))
{
query = query.Value(_ => _.Char, record.Char);
}
if (context.Contains(ProviderName.Firebird))
{
query = db.GetTable <CharTestTable>().Value(_ => _.Char, record.Char);
}
query.Insert();
}
var records = db.GetTable <CharTestTable>().Where(_ => _.Id > lastId).OrderBy(_ => _.Id).ToArray();
Assert.AreEqual(testData.Length, records.Length);
for (var i = 0; i < records.Length; i++)
{
if (context.StartsWith(ProviderName.SapHana))
{
// SAP or provider trims space and we return default value, which is \0 for char
// or we insert it incorrectly?
if (testData[i].Char == ' ')
{
Assert.AreEqual('\0', records[i].Char);
}
else
{
Assert.AreEqual(testData[i].Char, records[i].Char);
}
if (testData[i].NChar == ' ')
{
Assert.AreEqual('\0', records[i].NChar);
}
else
{
Assert.AreEqual(testData[i].NChar, records[i].NChar);
}
continue;
}
if (!SkipChar(context))
{
if (context.Contains("Sybase"))
{
Assert.AreEqual(testData[i].Char == '\0' ? ' ' : testData[i].Char, records[i].Char);
}
else
{
Assert.AreEqual(testData[i].Char, records[i].Char);
}
}
if (TestProvName.IsiSeriesOleDb(context) && i == 18)
{
Assert.AreEqual('\0', records[i].NChar);
}
else if (context == ProviderName.MySql ||
context == ProviderName.MySql + ".LinqService" ||
context == ProviderName.MySqlConnector ||
context == ProviderName.MySqlConnector + ".LinqService" ||
context == TestProvName.MySql55 ||
context == TestProvName.MySql55 + ".LinqService" ||
context == TestProvName.MariaDB ||
context == TestProvName.MariaDB + ".LinqService" ||
TestProvName.IsiSeries(context)
)
{
// for some reason mysql doesn't insert space
Assert.AreEqual(testData[i].NChar == ' ' ? '\0' : testData[i].NChar, records[i].NChar);
}
else if (!context.Contains(ProviderName.Firebird))
{
if (context.Contains("Sybase") || TestProvName.IsiSeriesAccessClient(context))
{
Assert.AreEqual(testData[i].NChar == '\0' ? ' ' : testData[i].NChar, records[i].NChar);
}
else
{
Assert.AreEqual(testData[i].NChar, records[i].NChar);
}
}
}
}
finally
{
db.GetTable <CharTestTable>().Where(_ => _.Id > lastId).Delete();
}
}
}
public void StringTrimming([DataSources(TestProvName.AllInformix)] string context)
{
using (var db = GetDataContext(context))
{
var lastId = db.GetTable <StringTestTable>().Select(_ => _.Id).Max();
try
{
var testData = GetStringData(context);
foreach (var record in testData)
{
var query = db.GetTable <StringTestTable>().Value(_ => _.NString, record.NString);
if (!SkipChar(context))
{
query = query.Value(_ => _.String, record.String);
}
if (context == ProviderName.Firebird ||
context == ProviderName.Firebird + ".LinqService" ||
context == TestProvName.Firebird3 ||
context == TestProvName.Firebird3 + ".LinqService")
{
query = db.GetTable <StringTestTable>().Value(_ => _.String, record.String);
}
query.Insert();
}
var records = db.GetTable <StringTestTable>().Where(_ => _.Id > lastId).OrderBy(_ => _.Id).ToArray();
Assert.AreEqual(testData.Length, records.Length);
for (var i = 0; i < records.Length; i++)
{
if (!SkipChar(context))
{
if (context.Contains("Sybase"))
{
Assert.AreEqual(testData[i].String?.TrimEnd(' ')?.TrimEnd('\0'), records[i].String);
}
else
{
Assert.AreEqual(testData[i].String?.TrimEnd(' '), records[i].String);
}
}
if (context != ProviderName.Firebird &&
context != ProviderName.Firebird + ".LinqService" &&
context != TestProvName.Firebird3 &&
context != TestProvName.Firebird3 + ".LinqService")
{
if (context.Contains("Sybase"))
{
Assert.AreEqual(testData[i].NString?.TrimEnd(' ')?.TrimEnd('\0'), records[i].NString);
}
else if (TestProvName.IsiSeriesOleDb(context) && i == 19)
{
Assert.AreEqual(testData[i].NString?.TrimEnd(), records[i].NString);
}
else
{
Assert.AreEqual(testData[i].NString?.TrimEnd(' '), records[i].NString);
}
}
}
}
finally
{
db.GetTable <StringTestTable>().Where(_ => _.Id > lastId).Delete();
}
}
}
private static void AssertTime(TimeSpan?expected, TimeSpan?actual, string provider)
{
if (provider == ProviderName.SqlServer2000 ||
provider == ProviderName.SqlServer2005 ||
provider.Contains("Oracle") ||
provider == ProviderName.SqlCe ||
provider == ProviderName.SQLiteClassic ||
provider == TestProvName.SQLiteClassicMiniProfilerMapped ||
provider == TestProvName.SQLiteClassicMiniProfilerUnmapped ||
provider == ProviderName.SQLiteMS ||
provider == TestProvName.MySql55 ||
provider == ProviderName.Firebird ||
provider == TestProvName.Firebird3)
{
return;
}
if (expected != null)
{
switch (TestProvName.GetFamily(provider))
{
case ProviderName.Sybase:
case ProviderName.SybaseManaged:
expected = TimeSpan.FromTicks((expected.Value.Ticks / 10000) * 10000);
switch (expected.Value.Milliseconds % 10)
{
case 1:
case 4:
case 7:
expected = expected.Value.Add(TimeSpan.FromMilliseconds(-1));
break;
case 2:
case 5:
case 9:
expected = expected.Value.Add(TimeSpan.FromMilliseconds(1));
break;
case 8:
expected = expected.Value.Add(TimeSpan.FromMilliseconds(2));
break;
}
if (expected == TimeSpan.FromDays(1))
{
expected = expected.Value.Add(TimeSpan.FromMilliseconds(-4));
}
break;
case ProviderName.Firebird:
case TestProvName.Firebird3:
expected = TimeSpan.FromTicks((expected.Value.Ticks / 1000) * 1000);
break;
case ProviderName.InformixDB2:
case ProviderName.Informix:
expected = TimeSpan.FromTicks((expected.Value.Ticks / 100) * 100);
break;
case ProviderName.PostgreSQL:
case ProviderName.PostgreSQL92:
case ProviderName.PostgreSQL93:
case ProviderName.PostgreSQL95:
case TestProvName.PostgreSQL10:
case TestProvName.PostgreSQL11:
expected = TimeSpan.FromTicks((expected.Value.Ticks / 10) * 10);
break;
case ProviderName.DB2:
case ProviderName.Access:
case ProviderName.AccessOdbc:
case ProviderName.SapHanaNative:
case ProviderName.SapHanaOdbc:
case TestProvName.MariaDB:
expected = TimeSpan.FromTicks((expected.Value.Ticks / 10000000) * 10000000);
break;
case ProviderName.MySqlConnector:
case ProviderName.MySql:
var msecs = expected.Value.Milliseconds;
if (msecs > 500)
{
expected = expected.Value.Add(TimeSpan.FromSeconds(1));
}
expected = TimeSpan.FromTicks((expected.Value.Ticks / 10000000) * 10000000);
break;
}
}
Assert.AreEqual(expected, actual);
}