private bool CheckExistingDb(DbConnection db)
{
try
{
// Check for correct structure
using (var cmd = new DbCommand(@"SELECT COUNT(*) from sqlite_master", db))
{
var result = (long)cmd.ExecuteScalar();
// We are expecting two tables and one additional index
if (result != 3)
{
_logger.LogWarning("Incorrect/incompatible existing cache db structure found!");
return(false);
}
}
// Check for correct version
using (var cmd = new DbCommand(@"SELECT value FROM meta WHERE key = ""version""", db))
{
var result = (long)cmd.ExecuteScalar();
if (result != SchemaVersion)
{
_logger.LogWarning("Existing cache db has unsupported schema version {SchemaVersion}",
result);
return(false);
}
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error while checking compatibilty of existing cache db!");
return(false);
}
return(true);
}
public static void AssemblyInit(TestContext context)
{
DataSource.GlobalExecutionCanceled += DefaultDispatcher_ExecutionCanceled;
DataSource.GlobalExecutionError += DefaultDispatcher_ExecutionError;
DataSource.GlobalExecutionFinished += DefaultDispatcher_ExecutionFinished;
DataSource.GlobalExecutionStarted += DefaultDispatcher_ExecutionStarted;
#if !Roslyn_Missing
CompiledMaterializers.MaterializerCompiled += CompiledMaterializers_MaterializerCompiled;
CompiledMaterializers.MaterializerCompilerFailed += CompiledMaterializers_MaterializerCompiled;
#endif
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabase.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
{
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}
}
public static void AssemblyInit()
{
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#else
void CreateFile(string databaseFileName)
{
FileStream fs = File.Create(databaseFileName);
fs.Close();
}
CreateFile(databaseFileName);
//SQLitePCL.Batteries.Init();
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabaseX.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
OfficePhone VARCHAR(15) NULL ,
CellPhone VARCHAR(15) NULL ,
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
string viewSql = @"CREATE VIEW EmployeeWithManager
AS
SELECT e.EmployeeKey ,
e.FirstName ,
e.MiddleName ,
e.LastName ,
e.Title ,
e.ManagerKey ,
e.OfficePhone ,
e.CellPhone ,
e.CreatedDate ,
e.UpdatedDate ,
m.EmployeeKey AS ManagerEmployeeKey ,
m.FirstName AS ManagerFirstName ,
m.MiddleName AS ManagerMiddleName ,
m.LastName AS ManagerLastName ,
m.Title AS ManagerTitle ,
m.ManagerKey AS ManagerManagerKey ,
m.OfficePhone AS ManagerOfficePhone ,
m.CellPhone AS ManagerCellPhone ,
m.CreatedDate AS ManagerCreatedDate ,
m.UpdatedDate AS ManagerUpdatedDate
FROM Employee e
LEFT JOIN Employee m ON m.EmployeeKey = e.ManagerKey;";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(viewSql, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
{
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}
}
/// <summary>
/// Tests the connection.
/// </summary>
public override void TestConnection()
{
using (var con = CreateConnection())
using (var cmd = new SQLiteCommand("SELECT 1", con))
cmd.ExecuteScalar();
}
/// <summary>
/// Tests the connection.
/// </summary>
public override void TestConnection()
{
using (var con = CreateConnection())
using (var cmd = new SQLiteCommand("SELECT 1", con))
cmd.ExecuteScalar();
}
public static void AssemblyInit(TestContext context)
{
DataSource.GlobalExecutionCanceled += DefaultDispatcher_ExecutionCanceled;
DataSource.GlobalExecutionError += DefaultDispatcher_ExecutionError;
DataSource.GlobalExecutionFinished += DefaultDispatcher_ExecutionFinished;
DataSource.GlobalExecutionStarted += DefaultDispatcher_ExecutionStarted;
#if !Roslyn_Missing
CompiledMaterializers.MaterializerCompiled += CompiledMaterializers_MaterializerCompiled;
CompiledMaterializers.MaterializerCompilerFailed += CompiledMaterializers_MaterializerCompiled;
#endif
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabase.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}