static private void UpdateToSource(uint articleId, ArticleInfo articleInfo)
{
var newDbElement = new ArticleTable
{
Id = articleInfo.Id,
Time = (ulong)articleInfo.Time,
Info = JsonConvert.SerializeObject(articleInfo)
};
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
try
{
db.BeginTransaction(IsolationLevel.Serializable);
var query = (from p in db.GetTable <ArticleTable>()
where p.Id == newDbElement.Id
select p);
var isExists = query.Count() != 0;
if (!isExists)
{
return;
}
db.InsertOrReplace(newDbElement);
}
finally
{
db.CommitTransaction();
}
}
}
public void TraceInfoStepsAreReportedForSqlInsert([IncludeDataSources(false, TestProvName.AllSQLiteNorthwind)] string context)
{
var events = GetEnumValues((TraceInfoStep s) => default(TraceInfo));
var counters = GetEnumValues((TraceInfoStep s) => 0);
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
db.OnTraceConnection = e =>
{
events[e.TraceInfoStep] = e;
counters[e.TraceInfoStep]++;
};
db.SetCommand(@"INSERT INTO Categories(CategoryID, CategoryName) VALUES(1024, '1024')").Execute();
// the same command is reported on each step
var command = events[TraceInfoStep.BeforeExecute] !.Command;
Assert.AreSame(command, events[TraceInfoStep.AfterExecute] !.Command);
Assert.AreSame(command, events[TraceInfoStep.Completed] !.Command);
Assert.NotNull(command);
// steps called once
Assert.AreEqual(1, counters[TraceInfoStep.BeforeExecute]);
Assert.AreEqual(1, counters[TraceInfoStep.AfterExecute]);
Assert.AreEqual(1, counters[TraceInfoStep.Completed]);
// steps never called
Assert.AreEqual(0, counters[TraceInfoStep.MapperCreated]);
Assert.AreEqual(0, counters[TraceInfoStep.Error]);
db.RollbackTransaction();
}
}
public void TraceInfoStepsAreReportedForSqlDelete([NorthwindDataContext] string context)
{
var events = GetEnumValues((TraceInfoStep s) => default(TraceInfo));
var counters = GetEnumValues((TraceInfoStep s) => 0);
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
db.OnTraceConnection = e =>
{
events[e.TraceInfoStep] = e;
counters[e.TraceInfoStep]++;
};
db.SetCommand(@"DELETE FROM Categories WHERE CategoryID = 1024").Execute();
// the same command is reported on each step
var command = events[TraceInfoStep.BeforeExecute] !.Command;
Assert.AreSame(command, events[TraceInfoStep.AfterExecute] !.Command);
Assert.AreSame(command, events[TraceInfoStep.Completed] !.Command);
Assert.NotNull(command);
// steps called once
Assert.AreEqual(1, counters[TraceInfoStep.BeforeExecute]);
Assert.AreEqual(1, counters[TraceInfoStep.AfterExecute]);
Assert.AreEqual(1, counters[TraceInfoStep.Completed]);
// steps never called
Assert.AreEqual(0, counters[TraceInfoStep.MapperCreated]);
Assert.AreEqual(0, counters[TraceInfoStep.Error]);
db.RollbackTransaction();
}
}
public Linq2DbSessionScope(DataConnection dataConnection)
{
Requires.NotNull(dataConnection, "dataConnection");
_dataConnection = dataConnection;
dataConnection.BeginTransaction();
}
private void WriteMemoryToDb()
{
using (DataConnection db = MySqlTools.CreateDataConnection(
ConfigLoadingManager.GetInstance()
.GetConfig().Database.GetConnectionString()))
{
try
{
db.BeginTransaction(IsolationLevel.Serializable);
foreach (var p in ArticleCache.Memory.Values)
{
db.InsertOrReplace(new ArticleTable
{
Id = p.Id,
Time = (ulong)p.Time,
Info = JsonConvert.SerializeObject(p)
});
}
}
finally
{
db.CommitTransaction();
}
}
}
public void TestWithTransaction([DataSources(false,
// those providers doesn't support stored procedures
ProviderName.SqlCe,
TestProvName.AllSQLite,
// those providers miss procedure schema load implementation for now
ProviderName.Informix,
// those providers cannot load schema when in transaction
ProviderName.DB2,
TestProvName.AllSybase,
TestProvName.AllMySql,
TestProvName.AllSqlServer)]
string context)
{
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
var recordsBefore = db.GetTable <AllTypes>().Count();
var sp = db.DataProvider.GetSchemaProvider();
var schema = sp.GetSchema(db, TestUtils.GetDefaultSchemaOptions(context, new GetSchemaOptions()
{
GetTables = false
}));
var recordsAfter = db.GetTable <AllTypes>().Count();
// schema request shouldn't execute procedure
Assert.AreEqual(recordsBefore, recordsAfter);
// schema provider should find our procedure for real
Assert.AreEqual(1, schema.Procedures.Count(p => p.ProcedureName.ToUpper() == "ADDISSUE792RECORD"));
}
}
private async Task <string> ProcessAsync(string s3BucketName, string s3ObjectKey)
{
var apiServerLogKeyword = Environment.GetEnvironmentVariable("apiServerLogKeyword");
var dedicatedServerLogKeyword = Environment.GetEnvironmentVariable("dedicatedServerLogKeyword");
if (!s3ObjectKey.ToLower().Contains(apiServerLogKeyword) &&
!s3ObjectKey.ToLower().Contains(dedicatedServerLogKeyword))
{
return(string.Empty);
}
var readCount = 0;
var processCount = 0;
var insertCount = 0;
var sw = Stopwatch.StartNew();
LinqToDBForEFTools.Initialize();
var optBuilder = new LinqToDbConnectionOptionsBuilder();
optBuilder.UseMySql(GetConnectionString());
using (var dataConnection = new DataConnection(optBuilder.Build()))
{
// todo: 1つのeventに2つ以上のファイルが含まれることがないか確認する
var request = new GetObjectRequest
{
BucketName = s3BucketName,
Key = s3ObjectKey,
};
using (var getResponse = await S3Client.GetObjectAsync(request))
using (var streamReader = new StreamReader(getResponse.ResponseStream))
using (var tran = dataConnection.BeginTransaction())
{
try
{
if (s3ObjectKey.ToLower().Contains(apiServerLogKeyword))
{
(readCount, processCount, insertCount) = await new ApiServerLogProcessor(dataConnection).ProcessAsync(streamReader);
}
else if (s3ObjectKey.ToLower().Contains(dedicatedServerLogKeyword))
{
(readCount, processCount, insertCount) = await new DedicatedServerLogProcessor(dataConnection).ProcessAsync(streamReader);
}
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
return($"{sw.Elapsed.TotalMilliseconds:N0} msec. read: {readCount:N0} lines. process: {processCount:N0} lines. insert: {insertCount:N0} rows.");
}
}
public DataConnection CreateDataConnection(string context)
{
var connection = new DataConnection(context);
connection.AddMappingSchema(_schema);
connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
return(connection);
}
protected override DataTable?GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters, GetSchemaOptions options)
{
//bug in drivers, SchemaOnly executes statement
using (dataConnection.BeginTransaction())
using (var rd = dataConnection.ExecuteReader(commandText, commandType, CommandBehavior.SchemaOnly, parameters))
{
return(rd.Reader !.GetSchemaTable());
}
}
public PersistentTableStoreFactory(LockManager lockManager, SchemaManager schemaManager)
{
using (Probe.Create("Get lock"))
{
_lockManager = lockManager;
_lock = _lockManager.GetLock();
_connection = new DataConnection("Messages").AddMappingSchema(schemaManager.GetSchema(_lock));
_connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
}
}
public void BeginTransaction()
{
using var fake = new FakeDbConnection();
using var conn = new DataConnection(fake, new DataSqlDialect());
var tx = conn.BeginTransaction();
assert.NotNull(tx);
assert.IsType(typeof(DataTransaction), tx);
assert.IsType(typeof(FakeDbTransaction), ((IUnwrap)tx).Unwrap());
}
public PersistentTableStoreFactory(IEqualityComparerFactory equalityComparerFactory,
MappingSchema webAppMappingSchema, IDataConnectionProvider connectionProvider)
{
_equalityComparerFactory = equalityComparerFactory;
_connection = connectionProvider.CreateVrConnection()
.AddMappingSchema(webAppMappingSchema);
// чтобы параллельные запуски single-проверок не накладывали блокировки на webapp-таблицы и не ждали друг друга
// запускаем single-проверки в транзакции с режимом snapshot, который не накладывает никаких блокировок
_connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
}
public void TestTimeSpan(string context)
{
using (var db = new DataConnection(context))
{
db.BeginTransaction();
long id = 1;
db.GetTable <Entity>().Insert(() => new Entity {
Id = id + 1, Duration = TimeSpan.FromHours(1)
});
}
}
protected override DataTable GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters)
{
//bug in drivers, SchemaOnly executes statement
dataConnection.BeginTransaction();
try
{
using (var rd = dataConnection.ExecuteReader(commandText, commandType, CommandBehavior.SchemaOnly, parameters))
{
return(rd.Reader.GetSchemaTable());
}
}
finally
{
dataConnection.RollbackTransaction();
}
}
void BulkCopyTest(string context, BulkCopyType bulkCopyType)
{
using (var conn = new DataConnection(context))
{
conn.BeginTransaction();
conn.BulkCopy(new BulkCopyOptions {
MaxBatchSize = 50000, BulkCopyType = bulkCopyType
},
Enumerable.Range(0, 100000).Select(n =>
new AllType
{
ID = 2000 + n,
bigintDataType = 3000 + n,
smallintDataType = (short)(4000 + n),
tinyintDataType = (sbyte)(5000 + n),
mediumintDataType = 6000 + n,
intDataType = 7000 + n,
numericDataType = 8000 + n,
decimalDataType = 9000 + n,
doubleDataType = 8800 + n,
floatDataType = 7700 + n,
dateDataType = DateTime.Now,
datetimeDataType = DateTime.Now,
timestampDataType = null,
timeDataType = null,
yearDataType = (1000 + n) % 100,
year2DataType = (1000 + n) % 100,
year4DataType = null,
charDataType = 'A',
varcharDataType = "",
textDataType = "",
binaryDataType = null,
varbinaryDataType = null,
blobDataType = new byte[] { 1, 2, 3 },
bitDataType = null,
enumDataType = "Green",
setDataType = "one",
intUnsignedDataType = (uint)(5000 + n),
}));
//var list = conn.GetTable<ALLTYPE>().ToList();
conn.GetTable <ALLTYPE>().Delete(p => p.SMALLINTDATATYPE >= 5000);
}
}
public void TestWithTransactionThrowsFromProvider(string context)
{
using (var db = new DataConnection(context))
using (var ts = db.BeginTransaction())
{
var recordsBefore = db.GetTable <AllTypes>().Count();
var sp = db.DataProvider.GetSchemaProvider();
var ex = Assert.Catch(() => sp.GetSchema(db, new GetSchemaOptions()
{
GetTables = false
}));
Assert.IsInstanceOf <InvalidOperationException>(ex);
Assert.IsTrue(ex.Message.Contains("requires the command to have a transaction"));
}
}
public void TestWithTransactionThrowsFromLinqToDB(string context)
{
using (var db = new DataConnection(context))
using (var ts = db.BeginTransaction())
{
var recordsBefore = db.GetTable <AllTypes>().Count();
var sp = db.DataProvider.GetSchemaProvider();
var ex = Assert.Catch(() => sp.GetSchema(db, new GetSchemaOptions()
{
GetTables = false
}));
Assert.IsInstanceOf <LinqToDBException>(ex);
Assert.AreEqual("Cannot read schema with GetSchemaOptions.GetProcedures = true from transaction. Remove transaction or set GetSchemaOptions.GetProcedures to false", ex.Message);
}
}
void BulkCopyTest(string context, BulkCopyType bulkCopyType)
{
using (var conn = new DataConnection(context))
{
conn.BeginTransaction();
conn.BulkCopy(new BulkCopyOptions {
MaxBatchSize = 50, BulkCopyType = bulkCopyType
},
Enumerable.Range(0, 100).Select(n =>
new AllType
{
ID = 2000 + n,
bigintDataType = 3000 + n,
smallintDataType = (short)(4000 + n),
decimalDataType = 900000 + n,
smalldecimalDataType = 90000 + n,
intDataType = 7000 + n,
tinyintDataType = (byte)(5000 + n),
floatDataType = 7700 + n,
realDataType = 7600 + n,
dateDataType = DateTime.Now,
timeDataType = DateTime.Now - DateTime.Today,
seconddateDataType = DateTime.Now,
timestampDataType = DateTime.Now,
charDataType = 'A',
varcharDataType = "AA",
textDataType = "text",
shorttextDataType = "shorttext",
ncharDataType = '\u00fc',
nvarcharDataType = "A\u00fcfsdf\u00fc",
alphanumDataType = "abcQWE654",
binaryDataType = new byte[] { 1 },
varbinaryDataType = new byte[] { 1, 2, 3 },
blobDataType = new byte[] { 1, 2, 3, 4, 5, 6 },
clobDataType = "clobclobclob",
nclobDataType = "nclob\u00fcnclob\u00fcnclob\u00fc"
}));
conn.GetTable <AllType>().Delete(p => p.ID >= 2000);
}
}
public void TraceInfoStepsAreReportedForBeginTransactionIlosationLevel([NorthwindDataContext(
#if NETFRAMEWORK
excludeSqlite: false, excludeSqliteMs: true
#endif
)] string context)
{
var events = GetEnumValues((TraceInfoStep s) => default(TraceInfo));
var counters = GetEnumValues((TraceInfoStep s) => 0);
using (var db = new DataConnection(context))
{
db.OnTraceConnection = e =>
{
events[e.TraceInfoStep] = e;
counters[e.TraceInfoStep]++;
};
using (db.BeginTransaction(IsolationLevel.ReadCommitted))
{
// Begin transaction command is reported on each step
Assert.AreEqual("BeginTransaction(ReadCommitted)", events[TraceInfoStep.BeforeExecute] !.CommandText);
Assert.AreEqual("BeginTransaction(ReadCommitted)", events[TraceInfoStep.AfterExecute] !.CommandText);
db.SetCommand(@"UPDATE Categories SET CategoryName = CategoryName WHERE 1=2").Execute();
db.CommitTransaction();
// Commit transaction command is reported on each step
Assert.AreEqual("CommitTransaction", events[TraceInfoStep.BeforeExecute] !.CommandText);
Assert.AreEqual("CommitTransaction", events[TraceInfoStep.AfterExecute] !.CommandText);
// steps called once for BeginTransaction once for Update and once for CommitTransaction
Assert.AreEqual(3, counters[TraceInfoStep.BeforeExecute]);
Assert.AreEqual(3, counters[TraceInfoStep.AfterExecute]);
// step called once for Update
Assert.AreEqual(1, counters[TraceInfoStep.Completed]);
// steps never called
Assert.AreEqual(0, counters[TraceInfoStep.MapperCreated]);
Assert.AreEqual(0, counters[TraceInfoStep.Error]);
}
}
}
public void Issue393Test([IncludeDataSources(ProviderName.SqlCe)] string context)
{
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
Random rnd = new Random();
var image = new byte[9000];
rnd.NextBytes(image);
var testItem = new ImageDataType {
imageDataType = image
};
var id = db.InsertWithInt32Identity(testItem);
var item = db.GetTable <ImageDataType>().FirstOrDefault(_ => _.ID == id);
Assert.That(testItem.imageDataType, Is.EqualTo(item.imageDataType));
}
}
public async Task DataConnectionDisposeAsyncTransaction([DataSources(false)] string context)
{
var tid = Thread.CurrentThread.ManagedThreadId;
using (var db = new DataConnection(context))
{
await using (db.BeginTransaction())
{
// perform synchonously to not mess with DisposeAsync testing
db.Insert(new Parent {
ParentID = 1010, Value1 = 1010
});
if (tid == Thread.CurrentThread.ManagedThreadId)
{
Assert.Inconclusive("Executed synchronously due to lack of async support or there were no underlying async operations");
}
}
}
}
public void TestTransaction1(string context)
{
using (var db = new DataConnection(context))
{
db.GetTable <Parent>().Update(p => p.ParentID == 1, p => new Parent {
Value1 = 1
});
db.BeginTransaction();
db.GetTable <Parent>().Update(p => p.ParentID == 1, p => new Parent {
Value1 = null
});
Assert.IsNull(db.GetTable <Parent>().First(p => p.ParentID == 1).Value1);
db.RollbackTransaction();
Assert.That(1, Is.EqualTo(db.GetTable <Parent>().First(p => p.ParentID == 1).Value1));
}
}
public void TestWithTransactionThrowsFromLinqToDB([IncludeDataSources(false,
ProviderName.Sybase, ProviderName.SybaseManaged,
ProviderName.MySql, TestProvName.MySql57, TestProvName.MariaDB)]
string context)
{
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
var recordsBefore = db.GetTable <AllTypes>().Count();
var sp = db.DataProvider.GetSchemaProvider();
var ex = Assert.Catch(() => sp.GetSchema(db, TestUtils.GetDefaultSchemaOptions(context, new GetSchemaOptions()
{
GetTables = false
})));
Assert.IsInstanceOf <LinqToDBException>(ex);
Assert.AreEqual("Cannot read schema with GetSchemaOptions.GetProcedures = true from transaction. Remove transaction or set GetSchemaOptions.GetProcedures to false", ex.Message);
}
}
public void TestTransaction2([IncludeDataSources(TestProvName.AllMySql)] string context)
{
using (var db = new DataConnection(context))
{
db.GetTable <Parent>().Update(p => p.ParentID == 1, p => new Parent {
Value1 = 1
});
using (var tran = db.BeginTransaction())
{
db.GetTable <Parent>().Update(p => p.ParentID == 1, p => new Parent {
Value1 = null
});
Assert.IsNull(db.GetTable <Parent>().First(p => p.ParentID == 1).Value1);
tran.Rollback();
Assert.That(1, Is.EqualTo(db.GetTable <Parent>().First(p => p.ParentID == 1).Value1));
}
}
}
public void TestWithTransactionThrowsFromProvider([IncludeDataSources(false,
ProviderName.DB2,
ProviderName.SqlServer2000, ProviderName.SqlServer2005, TestProvName.SqlAzure,
ProviderName.SqlServer2008, ProviderName.SqlServer2012, ProviderName.SqlServer2014)]
string context)
{
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
var recordsBefore = db.GetTable <AllTypes>().Count();
var sp = db.DataProvider.GetSchemaProvider();
var ex = Assert.Catch(() => sp.GetSchema(db, TestUtils.GetDefaultSchemaOptions(context, new GetSchemaOptions()
{
GetTables = false
})));
Assert.IsInstanceOf <InvalidOperationException>(ex);
Assert.IsTrue(ex.Message.Contains("requires the command to have a transaction"));
}
}
protected override DataTable GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters)
{
if (commandType == CommandType.StoredProcedure)
{
commandText = "{ CALL " + commandText + "(" + String.Join(",", parameters.Select(x => "?")) + ")}";
}
//bug SchemaOnly simply doesn't work
dataConnection.BeginTransaction();
try
{
using (var rd = dataConnection.ExecuteReader(commandText, CommandType.Text, CommandBehavior.Default, parameters))
{
return(rd.Reader.GetSchemaTable());
}
}
finally
{
dataConnection.RollbackTransaction();
}
}
public void TraceInfoStepsAreReportedForRolledbackTransaction([NorthwindDataContext] string context)
{
var events = GetEnumValues((TraceInfoStep s) => default(TraceInfo));
var counters = GetEnumValues((TraceInfoStep s) => 0);
using (var db = new DataConnection(context))
{
db.OnTraceConnection = e =>
{
events[e.TraceInfoStep] = e;
counters[e.TraceInfoStep]++;
};
using (db.BeginTransaction())
{
// Begin transaction command is reported on each step
Assert.AreEqual("BeginTransaction", events[TraceInfoStep.BeforeExecute] !.CommandText);
Assert.AreEqual("BeginTransaction", events[TraceInfoStep.AfterExecute] !.CommandText);
db.SetCommand(@"UPDATE Categories SET CategoryName = CategoryName WHERE 1=2").Execute();
db.RollbackTransaction();
// Commit transaction command is reported on each step
Assert.AreEqual("RollbackTransaction", events[TraceInfoStep.BeforeExecute] !.CommandText);
Assert.AreEqual("RollbackTransaction", events[TraceInfoStep.AfterExecute] !.CommandText);
// steps called once for BeginTransaction once for Update and once for CommitTransaction
Assert.AreEqual(3, counters[TraceInfoStep.BeforeExecute]);
Assert.AreEqual(3, counters[TraceInfoStep.AfterExecute]);
// step called once for Update
Assert.AreEqual(1, counters[TraceInfoStep.Completed]);
// steps never called
Assert.AreEqual(0, counters[TraceInfoStep.MapperCreated]);
Assert.AreEqual(0, counters[TraceInfoStep.Error]);
}
}
}
public void TestWithTransaction(string context)
{
using (var db = new DataConnection(context))
using (var ts = db.BeginTransaction())
{
var recordsBefore = db.GetTable <AllTypes>().Count();
var sp = db.DataProvider.GetSchemaProvider();
var schema = sp.GetSchema(db, new GetSchemaOptions()
{
GetTables = false
});
var recordsAfter = db.GetTable <AllTypes>().Count();
// schema request shouldn't execute procedure
Assert.AreEqual(recordsBefore, recordsAfter);
// schema provider should find our procedure for real
Assert.AreEqual(1, schema.Procedures.Count(p => p.ProcedureName.ToUpper() == "ADDISSUE792RECORD"));
}
}
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null)
{
if (options == null)
{
options = new GetSchemaOptions();
}
IncludedSchemas = GetHashSet(options.IncludedSchemas, options.StringComparer);
ExcludedSchemas = GetHashSet(options.ExcludedSchemas, options.StringComparer);
IncludedCatalogs = GetHashSet(options.IncludedCatalogs, options.StringComparer);
ExcludedCatalogs = GetHashSet(options.ExcludedCatalogs, options.StringComparer);
GenerateChar1AsString = options.GenerateChar1AsString;
var dbConnection = (DbConnection)dataConnection.Connection;
InitProvider(dataConnection);
DataTypes = GetDataTypes(dataConnection);
DataTypesDic = new Dictionary <string, DataTypeInfo>(DataTypes.Count, StringComparer.OrdinalIgnoreCase);
foreach (var dt in DataTypes)
{
if (!DataTypesDic.ContainsKey(dt.TypeName))
{
DataTypesDic.Add(dt.TypeName, dt);
}
}
List <TableSchema> tables;
List <ProcedureSchema> procedures;
if (options.GetTables)
{
tables =
(
from t in GetTables(dataConnection)
where
(IncludedSchemas.Count == 0 || IncludedSchemas.Contains(t.SchemaName)) &&
(ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(t.SchemaName)) &&
(IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(t.CatalogName)) &&
(ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(t.CatalogName))
select new TableSchema
{
ID = t.TableID,
CatalogName = t.CatalogName,
SchemaName = t.SchemaName,
TableName = t.TableName,
Description = t.Description,
IsDefaultSchema = t.IsDefaultSchema,
IsView = t.IsView,
TypeName = ToValidName(t.TableName),
Columns = new List <ColumnSchema>(),
ForeignKeys = new List <ForeignKeySchema>(),
IsProviderSpecific = t.IsProviderSpecific
}
).ToList();
var pks = GetPrimaryKeys(dataConnection);
#region Columns
var columns =
from c in GetColumns(dataConnection)
join pk in pks
on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2
from pk in g2.DefaultIfEmpty()
join t in tables on c.TableID equals t.ID
orderby c.Ordinal
select new { t, c, dt = GetDataType(c.DataType), pk };
foreach (var column in columns)
{
var dataType = column.c.DataType;
var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);
var isNullable = column.c.IsNullable;
var columnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);
column.t.Columns.Add(new ColumnSchema
{
Table = column.t,
ColumnName = column.c.Name,
ColumnType = columnType,
IsNullable = isNullable,
MemberName = ToValidName(column.c.Name),
MemberType = ToTypeName(systemType, isNullable),
SystemType = systemType ?? typeof(object),
DataType = GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale),
ProviderSpecificType = GetProviderSpecificType(dataType),
SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity,
SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity,
IsPrimaryKey = column.pk != null,
PrimaryKeyOrder = column.pk?.Ordinal ?? -1,
IsIdentity = column.c.IsIdentity,
Description = column.c.Description,
Length = column.c.Length,
Precision = column.c.Precision,
Scale = column.c.Scale,
});
}
#endregion
#region FK
var fks = GetForeignKeys(dataConnection);
foreach (var fk in fks.OrderBy(f => f.Ordinal))
{
var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault();
var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault();
if (thisTable == null || otherTable == null)
{
continue;
}
var thisColumn = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).Single();
var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single();
var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name);
if (key == null)
{
key = new ForeignKeySchema
{
KeyName = fk.Name,
MemberName = ToValidName(fk.Name),
ThisTable = thisTable,
OtherTable = otherTable,
ThisColumns = new List <ColumnSchema>(),
OtherColumns = new List <ColumnSchema>(),
CanBeNull = true,
};
thisTable.ForeignKeys.Add(key);
}
key.ThisColumns.Add(thisColumn);
key.OtherColumns.Add(otherColumn);
}
#endregion
var pst = GetProviderSpecificTables(dataConnection);
if (pst != null)
{
tables.AddRange(pst);
}
}
else
{
tables = new List <TableSchema>();
}
if (options.GetProcedures)
{
#region Procedures
var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder();
var procs = GetProcedures(dataConnection);
var procPparams = GetProcedureParameters(dataConnection);
var n = 0;
if (procs != null)
{
procedures =
(
from sp in procs
where
(IncludedSchemas.Count == 0 || IncludedSchemas.Contains(sp.SchemaName)) &&
(ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) &&
(IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(sp.CatalogName)) &&
(ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(sp.CatalogName))
join p in procPparams on sp.ProcedureID equals p.ProcedureID
into gr
select new ProcedureSchema
{
CatalogName = sp.CatalogName,
SchemaName = sp.SchemaName,
ProcedureName = sp.ProcedureName,
MemberName = ToValidName(sp.ProcedureName),
IsFunction = sp.IsFunction,
IsTableFunction = sp.IsTableFunction,
IsAggregateFunction = sp.IsAggregateFunction,
IsDefaultSchema = sp.IsDefaultSchema,
Parameters =
(
from pr in gr
join dt in DataTypes
on pr.DataType equals dt.TypeName into g1
from dt in g1.DefaultIfEmpty()
let systemType = GetSystemType(pr.DataType, null, dt, pr.Length, pr.Precision, pr.Scale)
orderby pr.Ordinal
select new ParameterSchema
{
SchemaName = pr.ParameterName,
SchemaType = GetDbType(pr.DataType, dt, pr.Length, pr.Precision, pr.Scale),
IsIn = pr.IsIn,
IsOut = pr.IsOut,
IsResult = pr.IsResult,
Size = pr.Length,
ParameterName = ToValidName(pr.ParameterName ?? "par" + ++n),
ParameterType = ToTypeName(systemType, true),
SystemType = systemType ?? typeof(object),
DataType = GetDataType(pr.DataType, null, pr.Length, pr.Precision, pr.Scale),
ProviderSpecificType = GetProviderSpecificType(pr.DataType),
}
).ToList()
} into ps
where ps.Parameters.All(p => p.SchemaType != "table type")
select ps
).ToList();
var current = 1;
var isActiveTransaction = dataConnection.Transaction != null;
if (GetProcedureSchemaExecutesProcedure && isActiveTransaction)
{
throw new LinqToDBException("Cannot read schema with GetSchemaOptions.GetProcedures = true from transaction. Remove transaction or set GetSchemaOptions.GetProcedures to false");
}
if (!isActiveTransaction)
{
dataConnection.BeginTransaction();
}
try
{
foreach (var procedure in procedures)
{
if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure))
{
var commandText = sqlProvider.ConvertTableName(new StringBuilder(),
procedure.CatalogName,
procedure.SchemaName,
procedure.ProcedureName).ToString();
LoadProcedureTableSchema(dataConnection, procedure, commandText, tables);
}
options.ProcedureLoadingProgress(procedures.Count, current++);
}
}
finally
{
if (!isActiveTransaction)
{
dataConnection.RollbackTransaction();
}
}
}
else
{
procedures = new List <ProcedureSchema>();
}
var psp = GetProviderSpecificProcedures(dataConnection);
if (psp != null)
{
procedures.AddRange(psp);
}
#endregion
}
else
{
procedures = new List <ProcedureSchema>();
}
return(ProcessSchema(new DatabaseSchema
{
DataSource = GetDataSourceName(dbConnection),
Database = GetDatabaseName(dbConnection),
ServerVersion = dbConnection.ServerVersion,
Tables = tables,
Procedures = procedures,
ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(),
DataTypesSchema = DataTypesSchema,
}, options));
}
protected override DataTable GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters)
{
//bug in drivers, SchemaOnly executes statement
dataConnection.BeginTransaction();
try
{
using (var rd = dataConnection.ExecuteReader(commandText, commandType, CommandBehavior.SchemaOnly, parameters))
{
return rd.Reader.GetSchemaTable();
}
}
finally
{
dataConnection.RollbackTransaction();
}
}
protected override DataTable GetProcedureSchema(DataConnection dataConnection, string commandText, CommandType commandType, DataParameter[] parameters)
{
if (commandType == CommandType.StoredProcedure)
{
commandText = "{ CALL " + commandText + "(" + String.Join(",", parameters.Select(x => "?")) + ")}";
}
//bug SchemaOnly simply doesn't work
dataConnection.BeginTransaction();
try
{
using (var rd = dataConnection.ExecuteReader(commandText, CommandType.Text, CommandBehavior.Default, parameters))
{
return rd.Reader.GetSchemaTable();
}
}
finally
{
dataConnection.RollbackTransaction();
}
}
public void Issue1298Test([IncludeDataSources(TestProvName.AllPostgreSQL)] string context)
{
using (var db = new DataConnection(context))
using (db.BeginTransaction())
{
db.CreateTable <mega_composites>();
db.CreateTable <qwerty>();
db.Insert(new qwerty()
{
Id = 1, asdfgh = "res1"
});
db.Insert(new qwerty()
{
Id = 100500, asdfgh = "res100500"
});
db.Insert(new mega_composites()
{
ref1 = 100500,
y1 = new mega_composites.mega_composites__y1()
{
q1 = new mega_composites.mega_composites__y1.mega_composites__y1__q1()
{
ref1 = 100500
}
}
});
db.Insert(new mega_composites()
{
ref1 = 1,
y1 = new mega_composites.mega_composites__y1()
{
q1 = new mega_composites.mega_composites__y1.mega_composites__y1__q1()
{
ref1 = 100500
}
}
});
db.Insert(new mega_composites()
{
ref1 = 100500,
y1 = new mega_composites.mega_composites__y1()
{
q1 = new mega_composites.mega_composites__y1.mega_composites__y1__q1()
{
ref1 = 1
}
}
});
var ref1 = db.GetTable <mega_composites>()
.Select(x => new __mega_composites_View
{
ref1 = x.ref1,
__face_ref1 = db.GetTable <qwerty>().Where(q => q.Id == x.ref1).Select(q => q.asdfgh).FirstOrDefault()
}).Take(2).ToArray();
Assert.NotNull(ref1);
}
}
