public override DbCommand GetCreateScopeInfoTableCommand(DbScopeType scopeType, DbConnection connection, DbTransaction transaction)
{
if (scopeType != DbScopeType.Client)
{
return(null);
}
var commandText =
$@"CREATE TABLE {ScopeInfoTableName.Quoted().ToString()}(
sync_scope_id blob NOT NULL PRIMARY KEY,
sync_scope_name text NOT NULL,
sync_scope_schema text NULL,
sync_scope_setup text NULL,
sync_scope_version text NULL,
scope_last_server_sync_timestamp integer NULL,
scope_last_sync_timestamp integer NULL,
scope_last_sync_duration integer NULL,
scope_last_sync datetime NULL
)";
var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = commandText;
return(command);
}
// Drop Scope table
// -------------------------------
public override DbCommand GetDropClientScopeInfoTableCommand(DbConnection connection, DbTransaction transaction)
{
var commandText = $"DROP Table {ScopeInfoTableName.Unquoted().ToString()}";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
return(command);
}
// Get all scopes
// ------------------------------
public override DbCommand GetAllClientScopesInfoCommand(DbConnection connection, DbTransaction transaction)
{
var commandText = $@"SELECT sync_scope_id, sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version,
scope_last_sync, scope_last_server_sync_timestamp, scope_last_sync_timestamp, scope_last_sync_duration
FROM {ScopeInfoTableName.Unquoted().ToString()}";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
return(command);
}
//Exists Scope Info
// ------------------------------
public override DbCommand GetExistsClientScopeInfoCommand(DbConnection connection, DbTransaction transaction)
{
var commandText = $@"Select count(*) from {ScopeInfoTableName.Unquoted().ToString()} where sync_scope_name = @sync_scope_name";
var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = commandText;
var p0 = command.CreateParameter();
p0.ParameterName = "@sync_scope_name";
p0.DbType = DbType.String;
p0.Size = 100;
command.Parameters.Add(p0);
return(command);
}
public override DbCommand GetDropScopeInfoTableCommand(DbScopeType scopeType, DbConnection connection, DbTransaction transaction)
{
if (scopeType != DbScopeType.Client)
{
return(null);
}
var commandText = $"DROP Table {ScopeInfoTableName.Unquoted().ToString()}";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
return(command);
}
public override DbCommand GetExistsScopeInfoCommand(DbScopeType scopeType, DbConnection connection, DbTransaction transaction)
{
if (scopeType != DbScopeType.Client)
{
return(null);
}
var commandText = $@"Select count(*) from {ScopeInfoTableName.Unquoted().ToString()} where sync_scope_id = @sync_scope_id";
var scommand = new SqliteCommand(commandText, (SqliteConnection)connection, (SqliteTransaction)transaction);
var p0 = scommand.CreateParameter();
p0.ParameterName = "@sync_scope_id";
p0.DbType = DbType.String;
scommand.Parameters.Add(p0);
return(scommand);
}
// Delete scope
// ------------------------------
public override DbCommand GetDeleteClientScopeInfoCommand(DbConnection connection, DbTransaction transaction)
{
var commandText = $@"DELETE FROM {ScopeInfoTableName.Unquoted().ToString()}
WHERE [sync_scope_name] = @sync_scope_name";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
var p0 = command.CreateParameter();
p0.ParameterName = "@sync_scope_name";
p0.DbType = DbType.String;
p0.Size = 100;
command.Parameters.Add(p0);
return(command);
}
// Get scope
// ------------------------------
public override DbCommand GetClientScopeInfoCommand(DbConnection connection, DbTransaction transaction)
{
var commandText = $@"SELECT sync_scope_id, sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version,
scope_last_sync, scope_last_server_sync_timestamp, scope_last_sync_timestamp, scope_last_sync_duration
FROM {ScopeInfoTableName.Unquoted().ToString()}
WHERE [sync_scope_name] = @sync_scope_name";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
var p0 = command.CreateParameter();
p0.ParameterName = "@sync_scope_name";
p0.DbType = DbType.String;
p0.Size = 100;
command.Parameters.Add(p0);
return(command);
}
public override DbCommand GetAllScopesCommand(DbScopeType scopeType, DbConnection connection, DbTransaction transaction)
{
if (scopeType != DbScopeType.Client)
{
return(null);
}
var commandText =
$@"SELECT sync_scope_id
, sync_scope_name
, sync_scope_schema
, sync_scope_setup
, sync_scope_version
, scope_last_sync
, scope_last_server_sync_timestamp
, scope_last_sync_timestamp
, scope_last_sync_duration
FROM {ScopeInfoTableName.Unquoted().ToString()}
WHERE sync_scope_name = @sync_scope_name";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.DbType = DbType.String;
p.Size = 100;
command.Parameters.Add(p);
return(command);
}
// Create Table
// ------------------------------
public override DbCommand GetCreateClientScopeInfoTableCommand(DbConnection connection, DbTransaction transaction)
{
var tableName = $"{ScopeInfoTableName.Unquoted().Normalized().ToString()}";
var commandText =
$@"CREATE TABLE [{tableName}](
sync_scope_id blob NOT NULL,
sync_scope_name text NOT NULL,
sync_scope_schema text NULL,
sync_scope_setup text NULL,
sync_scope_version text NULL,
scope_last_server_sync_timestamp integer NULL,
scope_last_sync_timestamp integer NULL,
scope_last_sync_duration integer NULL,
scope_last_sync datetime NULL,
CONSTRAINT PK_{tableName} PRIMARY KEY(sync_scope_id, sync_scope_name))";
var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = commandText;
return(command);
}
public DbCommand GetSaveScopeInfoCommand(bool exist, DbConnection connection, DbTransaction transaction)
{
var stmtText = new StringBuilder();
stmtText.AppendLine(exist
? $"Update {ScopeInfoTableName.Unquoted().ToString()} set sync_scope_name=@sync_scope_name, sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, scope_last_sync=@scope_last_sync, scope_last_server_sync_timestamp=@scope_last_server_sync_timestamp, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id;"
: $"Insert into {ScopeInfoTableName.Unquoted().ToString()} (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, scope_last_sync, scope_last_sync_duration, scope_last_server_sync_timestamp, scope_last_sync_timestamp, sync_scope_id) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @scope_last_sync, @scope_last_sync_duration, @scope_last_server_sync_timestamp, @scope_last_sync_timestamp, @sync_scope_id);");
stmtText.AppendLine(@$ "SELECT sync_scope_id
, sync_scope_name
, sync_scope_schema
, sync_scope_setup
, sync_scope_version
, scope_last_sync
, scope_last_server_sync_timestamp
, scope_last_sync_timestamp
, scope_last_sync_duration
FROM {ScopeInfoTableName.Unquoted().ToString()}
WHERE sync_scope_name = @sync_scope_name");
var command = new SqliteCommand(stmtText.ToString(), (SqliteConnection)connection, (SqliteTransaction)transaction);
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.DbType = DbType.String;
p.Size = 100;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_schema";
p.DbType = DbType.String;
p.Size = -1;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_setup";
p.DbType = DbType.String;
p.Size = -1;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_version";
p.DbType = DbType.String;
p.Size = 10;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_server_sync_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.DbType = DbType.String;
p.Size = -1;
command.Parameters.Add(p);
return(command);
}
public override DbCommand GetExistsScopeInfoTableCommand(DbScopeType scopeType, DbConnection connection, DbTransaction transaction)
{
if (scopeType != DbScopeType.Client)
{
return(null);
}
var commandText = $@"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{ScopeInfoTableName.Unquoted().ToString()}'";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
command.Transaction = transaction;
return(command);
}
