public void Setup()
{
var schema = new DataTable();
schema.Columns.Add("AllowDBNull", typeof(bool));
schema.Rows.Add(false);
schema.Rows.Add(true);
schema.Rows.Add(true);
var result = new QueryResult()
{
Schema = schema,
Names = new[] { "id", "name", "login_count" },
FieldTypes = new[] { typeof(long), typeof(string), typeof(int) },
DbTypes = new[] { "int8", "varchar", "int4" },
Data = new object?[][] { new object?[] { 100500L, "Vasily Lohankin", 123 } },
};
_cn = new MockDbConnection(result, ConnectionState.Open);
_db = new DataConnection(PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95), _cn);
_compiled = CompiledQuery.Compile <DataConnection, long, IQueryable <User> >(
(db, userId) => from c in db.GetTable <User>()
where c.Id == userId
select c);
}
public void SpecificTest([IncludeDataSources(TestProvName.AllPostgreSQL)] string context, [Values] bool avoidProviderSpecificApi)
{
var provider = PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95);
var cs = DataConnection.GetConnectionString(context);
using (var cn = new NpgsqlConnection(cs))
using (var db = new DataConnection(provider, cn))
{
db.MappingSchema.GetFluentMappingBuilder()
.Entity <Client>()
.HasTableName("Issue1438")
.Property(x => x.Id)
.IsPrimaryKey()
.IsIdentity();
using (var tbl = db.CreateLocalTable <Client>())
{
var id = db.InsertWithInt32Identity(new Client()
{
Has = true
});
var record = tbl.Where(_ => _.Id == id).Single();
Assert.AreEqual(id, record.Id);
Assert.True(record.Has);
}
}
}
static DataConnection()
{
_configurationIDs = new ConcurrentDictionary <string, int>();
OracleTools.GetDataProvider();
PostgreSQLTools.GetDataProvider();
var section = LinqToDBSection.Instance;
if (section != null)
{
DefaultConfiguration = section.DefaultConfiguration;
DefaultDataProvider = section.DefaultDataProvider;
foreach (DataProviderElement provider in section.DataProviders)
{
var dataProviderType = Type.GetType(provider.TypeName, true);
var providerInstance = (IDataProviderFactory)Activator.CreateInstance(dataProviderType);
if (!string.IsNullOrEmpty(provider.Name))
{
AddDataProvider(provider.Name, providerInstance.GetDataProvider(provider.Attributes));
}
}
}
}
protected override IDbConnection CreateConnectionInternal(string connectionString)
{
// fill base mapping schema
using (PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95).CreateConnection(connectionString))
{
}
return(base.CreateConnectionInternal(connectionString));
}
public void NpgsqlDateTimeTest(string context)
{
PostgreSQLTools.GetDataProvider().CreateConnection(DataConnection.GetConnectionString(context));
var d = new NpgsqlDateTime(DateTime.Today);
var o = new DateTimeOffset(DateTime.Today);
var c1 = PostgreSQLTools.GetDataProvider().MappingSchema.GetConvertExpression <NpgsqlDateTime, DateTimeOffset>();
var c2 = PostgreSQLTools.GetDataProvider().MappingSchema.GetConvertExpression <NpgsqlDateTime, DateTimeOffset?>();
Assert.IsNotNull(c1);
Assert.IsNotNull(c2);
Assert.AreEqual(o, c1.Compile()(d));
Assert.AreEqual(o, c2.Compile()(d).Value);
}
protected override void OnConnectionTypeCreated(Type connectionType)
{
base.OnConnectionTypeCreated(connectionType);
var baseProvider = (PostgreSQLDataProvider)PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95);
// configure mapping from npgsql3 type to npgsql4 type though DataParameter.Value
SetConverterToParameter(baseProvider.NpgsqlDateTimeType, NpgsqlDateTimeType);
SetConverterToParameter(baseProvider.NpgsqlDateType, NpgsqlDateType);
SetConverterToParameter(baseProvider.NpgsqlIntervalType, NpgsqlIntervalType);
SetConverterToParameter(baseProvider.NpgsqlPointType, NpgsqlPointType);
SetConverterToParameter(baseProvider.NpgsqlLSegType, NpgsqlLSegType);
SetConverterToParameter(baseProvider.NpgsqlBoxType, NpgsqlBoxType);
SetConverterToParameter(baseProvider.NpgsqlPathType, NpgsqlPathType);
SetConverterToParameter(baseProvider.NpgsqlPolygonType, NpgsqlPolygonType);
SetConverterToParameter(baseProvider.NpgsqlCircleType, NpgsqlCircleType);
SetConverterToParameter(baseProvider.NpgsqlLineType, NpgsqlLineType);
SetConverterToParameterNpgsqlInet(baseProvider.NpgsqlInetType, NpgsqlInetType);
// configure mapping from npgsql4 type to npgsql3 type
SetConverterToV3(baseProvider.NpgsqlDateTimeType, NpgsqlDateTimeType);
SetConverterToV3(baseProvider.NpgsqlDateType, NpgsqlDateType);
SetConverterToV3(baseProvider.NpgsqlIntervalType, NpgsqlIntervalType);
SetConverterToV3(baseProvider.NpgsqlPointType, NpgsqlPointType);
SetConverterToV3(baseProvider.NpgsqlLSegType, NpgsqlLSegType);
SetConverterToV3(baseProvider.NpgsqlBoxType, NpgsqlBoxType);
SetConverterToV3(baseProvider.NpgsqlPathType, NpgsqlPathType);
SetConverterToV3(baseProvider.NpgsqlPolygonType, NpgsqlPolygonType);
SetConverterToV3(baseProvider.NpgsqlCircleType, NpgsqlCircleType);
SetConverterToV3(baseProvider.NpgsqlLineType, NpgsqlLineType);
SetConverterToV3NpgsqlInet(baseProvider.NpgsqlInetType, NpgsqlInetType);
_setMoney = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Money");
_setVarBinary = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Bytea");
_setBoolean = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Boolean");
_setXml = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Xml");
_setText = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Text");
_setBit = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Bit");
_setHstore = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Hstore");
_setJson = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Json");
_setJsonb = GetSetParameter(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType, "Jsonb");
_setNativeParameterType = GetSetParameter <object>(connectionType, "NpgsqlParameter", "NpgsqlDbType", NpgsqlDbType);
}
public void Setup()
{
var schema = new DataTable();
schema.Columns.Add("AllowDBNull", typeof(bool));
schema.Rows.Add(false);
schema.Rows.Add(true);
schema.Rows.Add(true);
var result = new QueryResult()
{
Schema = schema,
Names = new[] { "id", "name", "login_count" },
FieldTypes = new[] { typeof(long), typeof(string), typeof(int) },
DbTypes = new[] { "int8", "varchar", "int4" },
Data = new object?[][] { new object?[] { 100500L, "Vasily Lohankin", 123 } },
};
_cn = new MockDbConnection(result, ConnectionState.Open);
_compiled = CompiledQuery.Compile <DataConnection, long?, IQueryable <User> >(
(db, userId) => from c in db.GetTable <User>()
where userId == null || c.Id == userId
select c);
var threadCount = ThreadCount;
_threads = new Thread[threadCount];
_db = new DataConnection[threadCount];
for (var i = 0; i < _threads.Length; i++)
{
_db[i] = new DataConnection(PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95), _cn);
_threads[i] = new Thread(ThreadWorker);
_threads[i].IsBackground = true; // we don't stop threads explicitly
_threads[i].Start(i);
}
}
public void Setup()
{
_cn = new MockDbConnection(new QueryResult()
{
Return = 1
}, ConnectionState.Open);
_db = new DataConnection(PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95), _cn);
_compiledLinqSet = CompiledQuery.Compile <DataConnection, Workflow, int>(
(db, record) => db.GetTable <Workflow>()
.Where(x => x.Id == record.Id && x.RowVersion == record.RowVersion)
.Set(x => x.Status, record.Status)
.Set(x => x.Result, record.Result)
.Set(x => x.Error, record.Error)
.Set(x => x.Steps, record.Steps)
.Set(x => x.UpdateTime, record.UpdateTime)
.Set(x => x.RowVersion, x => x.RowVersion + 1)
.Set(x => x.StartTime, record.StartTime)
.Set(x => x.ProcessedTime, record.ProcessedTime)
.Set(x => x.CompleteTime, record.CompleteTime)
.Update());
_compiledLinqObject = CompiledQuery.Compile <DataConnection, Workflow, int>(
(db, record) => db.GetTable <Workflow>()
.Where(x => x.Id == _record.Id && x.RowVersion == _record.RowVersion)
.Update(x => new()
{
Status = record.Status,
Result = record.Result,
Error = record.Error,
Steps = record.Steps,
UpdateTime = record.UpdateTime,
RowVersion = x.RowVersion + 1,
StartTime = record.StartTime,
ProcessedTime = record.ProcessedTime,
CompleteTime = record.CompleteTime
}));
}
public Npgsql4PostgreSQLMappingSchema() : base(PostgreSQLTools.GetDataProvider(PostgreSQLVersion.v95).MappingSchema)
{
}
public bool OpenConnection(string connectionString)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
Logger.Log(this, "Unable to connect to the SQL database: No connection specified.", LogLevel.Error);
return(false);
}
internalState = ConnectionState.Connecting;
connection = PostgreSQLTools.CreateDataConnection(connectionString);
internalState = ConnectionState.Open;
connection.OnClosing += (sender, args) => internalState = ConnectionState.Closed;
connection.OnClosed += (sender, args) => internalState = ConnectionState.Closed;
metadata = connection.GetTable <Metadata>();
try
{
var version = from entry in metadata
where entry.Key == "version"
select entry.Value;
var count = version.Count();
if (count != 1)
{
Logger.Log(this, "Zero or multiple 'version' entries found in the Metadata table. The database connection will be dropped.", LogLevel.Error);
HandleConnectionFailure();
return(false);
}
if (version.First() != Bot.DatabaseVersion)
{
Logger.Log(this, $"Bot and database version do not match (bot is at {Bot.DatabaseVersion}, database is at {version.First()}). The database will now be upgraded.", LogLevel.Info);
if (!UpgradeDatabase(version.First()))
{
Logger.Log(this, "Upgrade failed. The database connection will be dropped.", LogLevel.Warning);
HandleConnectionFailure();
return(false);
}
}
}
catch (NpgsqlException e)
{
if (e.Message.ToLower().Contains("metadata") && e.Message.ToLower().Contains("does not exist"))
{
Logger.Log(this, "Metadata table not found. A new tableset will be created.", LogLevel.Warning);
try
{
DropTables();
CreateTables();
}
catch (NpgsqlException f)
{
Logger.Log(this, $"Unable to create a new tableset: An exception occurred({f.GetType()}: {f.Message}). The database connection will be dropped.", LogLevel.Error);
HandleConnectionFailure();
return(false);
}
AddMetadata();
Logger.Log(this, "Writing metadata...", LogLevel.Info);
}
else
{
Logger.Log(this, $"Unable to retrieve the database version: An exception occurred ({e.GetType()}: {e.Message}). The database connection will be dropped.", LogLevel.Error);
HandleConnectionFailure();
return(false);
}
}
//UserCredentials = connection.GetTable<UserCredential>();
Quotes = connection.GetTable <Quote>();
UserStatistics = connection.GetTable <UserStatistic>();
Emoticons = connection.GetTable <UsedEmoticon>();
KeyValuePairs = connection.GetTable <KeyValuePair>();
LinkedUrls = connection.GetTable <LinkedUrl>();
Users = connection.GetTable <User>();
Words = connection.GetTable <UsedWord>();
ChatLog = connection.GetTable <ChatLog>();
MiscData = connection.GetTable <MiscData>();
try
{
// These methods are actually not pure, because they'll throw an exception
// if their backing database tables are not found. It's a bit of a hack,
// but this is the easiest way to check whether those tables exist.
// ReSharper disable ReturnValueOfPureMethodIsNotUsed
//UserCredentials.FirstOrDefault();
Quotes.FirstOrDefault();
UserStatistics.FirstOrDefault();
Emoticons.FirstOrDefault();
KeyValuePairs.FirstOrDefault();
LinkedUrls.FirstOrDefault();
Users.FirstOrDefault();
Words.FirstOrDefault();
ChatLog.FirstOrDefault();
MiscData.FirstOrDefault();
// ReSharper restore ReturnValueOfPureMethodIsNotUsed
}
catch (Exception e)
{
Logger.Log(this, $"Database integrity check failed ({e.GetType().Name}: {e.Message})", LogLevel.Error);
HandleConnectionFailure();
return(false);
}
return(true);
}
/// <summary>
/// Configure connection to use PostgreSQL Npgsql provider, specific dialect and connection string.
/// </summary>
/// <param name="builder">Instance of <see cref="LinqToDbConnectionOptionsBuilder"/>.</param>
/// <param name="connectionString">PostgreSQL connection string.</param>
/// <param name="dialect">POstgreSQL dialect support level.</param>
/// <returns>The builder instance so calls can be chained.</returns>
public static LinqToDbConnectionOptionsBuilder UsePostgreSQL(this LinqToDbConnectionOptionsBuilder builder, string connectionString, PostgreSQLVersion dialect)
{
return(builder.UseConnectionString(PostgreSQLTools.GetDataProvider(dialect), connectionString));
}
