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); }