public static IDbConnection Create(Connection cn)
{
IDbConnection output;
switch (cn.Provider)
{
case "sqlserver":
output = new SqlServerConnectionFactory(cn).GetConnection();
break;
case "mysql":
output = new MySqlConnectionFactory(cn).GetConnection();
break;
case "postgresql":
output = new PostgreSqlConnectionFactory(cn).GetConnection();
break;
case "sqlite":
output = new SqLiteConnectionFactory(cn).GetConnection();
break;
case "sqlce":
output = new SqlCeConnectionFactory(cn).GetConnection();
break;
default:
throw new DataException($"Provider {cn.Provider} is not supported!");
}
output.Open();
return(output);
}
/// <summary>
/// Attempts to perform a backup, silently fails and logs exception if something
/// went wrong.
/// </summary>
private void Backup()
{
try
{
// compute the earliest non-existing filename like "backup_20131017_14_1.sqlite", for the 2nd backup past 14:00 on October 17th.
var destName = String.Format("{0:yyyyMMdd_HH}", DateTime.Now);
var filename = "";
int count = 0;
do
{
filename = String.Format("db\\backup\\{0}_{1}.sqlite", destName, count);
count++;
} while(File.Exists(filename));
var destFactory = new SqLiteConnectionFactory
{
ConnectionString = filename,
};
using (var dest = (OrmLiteConnection)destFactory.Connect())
{
var destSqlite = (SQLiteConnection)dest.DbConnection;
using (var source = (OrmLiteConnection)factory.Connect())
{
var sourceSqlite = (SQLiteConnection)source.DbConnection;
sourceSqlite.BackupDatabase(destSqlite, "main", "main", -1, null, 1000);
}
}
}
catch (Exception e)
{
logger.ErrorException("Could not backup database", e);
}
}
/// <summary>
/// Schedule the database that `factory` points to for backup every `period` amount of time.
/// Dispose this object to stop backupping.
/// </summary>
public BackupDaemon(SqLiteConnectionFactory factory, TimeSpan period)
{
this.factory = factory;
this.period = period;
// start loop task and forget about it
var task = Loop();
}
public AuctionServiceOptions(IConfiguration configuration)
{
ConnectionString = configuration.GetConnectionString("auctionsDB");
ConnectionFactory = new SqLiteConnectionFactory();
}
public void SqlLite_Integration()
{
var builder = new ContainerBuilder();
builder.RegisterModule(new RootModule(@"Files\Shorthand.xml"));
var container = builder.Build();
// CORRECT DATA AND INITIAL LOAD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2, cn.Execute(@"
UPDATE [Order Details] SET UnitPrice = 14.40, Quantity = 42 WHERE OrderId = 10253 AND ProductId = 39;
UPDATE Orders SET CustomerID = 'CHOPS', Freight = 22.98 WHERE OrderId = 10254;
"));
}
var root = ResolveRoot(container, TestFile, true);
var response = new PipelineAction(root).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Content);
using (var cn = new SqLiteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT Inserts FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 1 LIMIT 1;"));
}
// FIRST DELTA, NO CHANGES
root = ResolveRoot(container, TestFile, false);
response = new PipelineAction(root).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Content);
using (var cn = new SqLiteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(0, cn.ExecuteScalar <int>("SELECT Inserts+Updates+Deletes FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 9 LIMIT 1;"));
}
// CHANGE 2 FIELDS IN 1 RECORD IN MASTER TABLE THAT WILL CAUSE CALCULATED FIELD TO BE UPDATED TOO
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"UPDATE [Order Details] SET UnitPrice = 15, Quantity = 40 WHERE OrderId = 10253 AND ProductId = 39;";
Assert.AreEqual(1, cn.Execute(sql));
}
root = ResolveRoot(container, TestFile, false);
response = new PipelineAction(root).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Content);
using (var cn = new SqLiteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 17 LIMIT 1;"));
Assert.AreEqual(15.0, cn.ExecuteScalar <decimal>("SELECT OrderDetailsUnitPrice FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(40, cn.ExecuteScalar <int>("SELECT OrderDetailsQuantity FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(15.0 * 40, cn.ExecuteScalar <int>("SELECT OrderDetailsExtendedPrice FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
}
// CHANGE 1 RECORD'S CUSTOMERID AND FREIGHT ON ORDERS TABLE
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.Execute("UPDATE Orders SET CustomerID = 'VICTE', Freight = 20.11 WHERE OrderId = 10254;"));
}
root = ResolveRoot(container, TestFile, false);
response = new PipelineAction(root).Execute();
Assert.AreEqual(200, response.Code);
Assert.AreEqual(string.Empty, response.Content);
using (var cn = new SqLiteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Orders' AND BatchId = 26;"));
Assert.AreEqual("VICTE", cn.ExecuteScalar <string>("SELECT OrdersCustomerId FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
Assert.AreEqual(20.11, cn.ExecuteScalar <decimal>("SELECT OrdersFreight FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
}
}
public UsersRepository(SqLiteConnectionFactory factory)
{
ConnectionFactory = factory;
EnsureTablesExist();
}
public StatisticServiceOptions(IConfiguration configuration)
{
ConnectionString = configuration.GetConnectionString("statisticDB");
ConnectionFactory = new SqLiteConnectionFactory();
}
