/// <summary>
/// Gets a SyncSchema object created by consulting the database, namely by filling the datatables from the SyncAdapters
/// </summary>
/// <param name="tableNames">the names of the tables to be included in the schema</param>
/// <param name="missingTables">the names of the missing tables not found in the set of the SyncAdapters</param>
/// <returns>the SyncSchema object</returns>
protected virtual SyncSchema GetSchemaFromDatabase(Collection <string> tableNames, out Collection <string> missingTables)
{
SyncSchema schema = new SyncSchema();
schema.SchemaDataSet = new DataSet();
schema.SchemaDataSet.Locale = CultureInfo.InvariantCulture;
missingTables = new Collection <string>();
Connection.Open();
foreach (string tableName in tableNames)
{
SpSyncAdapter adapter = null;
if (SyncAdapters.Contains(tableName))
{
adapter = SyncAdapters[tableName];
}
if (adapter != null)
{
DataTable dataTable = null;
try
{
dataTable = adapter.FillSchema(dataTable, Connection);
dataTable.TableName = tableName;
schema.SchemaDataSet.Tables.Add(dataTable);
}
catch (Exception e)
{
missingTables.Add(tableName);
if (SyncTracer.IsErrorEnabled())
{
SyncTracer.Error(e.ToString());
}
}
}
else
{
missingTables.Add(tableName);
}
}
Connection.Close();
if (missingTables.Count == 0)
{
missingTables = null;
}
return(schema);
}
private SyncGroupMetadata GenerateOrderGroupMetadata(SyncGroupMetadata groupMetadata)
{
if (groupMetadata.TablesMetadata.Count <= 1)
{
return(groupMetadata);
}
SyncTableMetadata[] tableMetadatas = new SyncTableMetadata[groupMetadata.TablesMetadata.Count];
for (int i = 0; i < groupMetadata.TablesMetadata.Count; ++i)
{
int index = SyncAdapters.IndexOf(groupMetadata.TablesMetadata[i].TableName);
if (index == -1)
{
throw new ArgumentException(String.Format(CultureInfo.CurrentCulture,
Messages.InvalidTableName, groupMetadata.TablesMetadata[i].TableName));
}
else
{
tableMetadatas[index] = groupMetadata.TablesMetadata[i];
}
}
SyncGroupMetadata newGroupMetadata = new SyncGroupMetadata(groupMetadata);
newGroupMetadata.TablesMetadata.Clear();
for (int i = 0; i < tableMetadatas.Length; i++)
{
if (tableMetadatas[i] != null)
{
newGroupMetadata.TablesMetadata.Add(tableMetadatas[i]);
}
}
return(newGroupMetadata);
}
public FirebirdDbSyncProvider()
{
// We need to modify this particular column name because the default is longer than Firebird allows.
this.ScopeForgottenKnowledgeColName = "scope_forgotten_knowledge";
for (int i = 0; i < SyncUtils.SyncAdapterTables.Length; i++)
{
//Add each table as a DbSyncAdapter to the provider
DbSyncAdapter adapter = new DbSyncAdapter(SyncUtils.SyncAdapterTables[i]);
adapter.RowIdColumns.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i]);
// select incremental changes command
FbCommand chgsOrdersCmd = new FbCommand();
chgsOrdersCmd.CommandType = CommandType.StoredProcedure;
chgsOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectchanges";
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, FbDbType.Integer); // TODO: timestamp?
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMetadataOnly, FbDbType.Integer);
chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
// chgsOrdersCmd.Parameters.Add("sync_changes", FbDbType.Cursor).Direction = ParameterDirection.Output;
adapter.SelectIncrementalChangesCommand = chgsOrdersCmd;
// insert row command
FbCommand insOrdersCmd = new FbCommand();
insOrdersCmd.CommandType = CommandType.StoredProcedure;
insOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applyinsert";
if (SyncUtils.SyncAdapterTables[i] == "order_details")
{
insOrdersCmd.Parameters.Add("order_id", FbDbType.Integer);
}
insOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
if (SyncUtils.SyncAdapterTables[i] == "orders")
{
insOrdersCmd.Parameters.Add("order_date", FbDbType.Date);
}
else
{
insOrdersCmd.Parameters.Add("product", FbDbType.VarChar, 100);
insOrdersCmd.Parameters.Add("quantity", FbDbType.Integer);
}
insOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.InsertCommand = insOrdersCmd;
// update row command
FbCommand updOrdersCmd = new FbCommand();
updOrdersCmd.CommandType = CommandType.StoredProcedure;
updOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applyupdate";
if (SyncUtils.SyncAdapterTables[i] == "order_details")
{
updOrdersCmd.Parameters.Add("order_id", FbDbType.Integer);
}
updOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
if (SyncUtils.SyncAdapterTables[i] == "orders")
{
updOrdersCmd.Parameters.Add("order_date", FbDbType.Date);
}
else
{
updOrdersCmd.Parameters.Add("quantity", FbDbType.Integer);
updOrdersCmd.Parameters.Add("product", FbDbType.VarChar, 100);
}
updOrdersCmd.Parameters.Add(DbSyncSession.SyncForceWrite, FbDbType.Integer);
updOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, FbDbType.Integer);
updOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.UpdateCommand = updOrdersCmd;
// delete row command
FbCommand delOrdersCmd = new FbCommand();
delOrdersCmd.CommandType = CommandType.StoredProcedure;
delOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_applydelete";
delOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, FbDbType.Integer);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncForceWrite, FbDbType.Integer);
delOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.DeleteCommand = delOrdersCmd;
// get row command
FbCommand selRowOrdersCmd = new FbCommand();
selRowOrdersCmd.CommandType = CommandType.StoredProcedure;
selRowOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectrow";
selRowOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
selRowOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
// selRowOrdersCmd.Parameters.Add("selectedRow", FbDbType.Cursor).Direction = ParameterDirection.Output;
adapter.SelectRowCommand = selRowOrdersCmd;
// insert row metadata command
FbCommand insMetadataOrdersCmd = new FbCommand();
insMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
insMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_insert_md";
insMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerKey, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerTimestamp, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerKey, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerTimestamp, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowIsTombstone, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
insMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.InsertMetadataCommand = insMetadataOrdersCmd;
// update row metadata command
FbCommand updMetadataOrdersCmd = new FbCommand();
updMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
updMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_update_md";
updMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowIsTombstone, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerKey, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCreatePeerTimestamp, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerKey, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncUpdatePeerTimestamp, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, FbDbType.Integer);
updMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.UpdateMetadataCommand = updMetadataOrdersCmd;
// delete row metadata command
FbCommand delMetadataOrdersCmd = new FbCommand();
delMetadataOrdersCmd.CommandType = CommandType.StoredProcedure;
delMetadataOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_delete_md";
delMetadataOrdersCmd.Parameters.Add(SyncUtils.SyncAdapterTablePrimaryKeys[i], FbDbType.Integer);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowTimestamp, FbDbType.Integer);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
delMetadataOrdersCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
adapter.DeleteMetadataCommand = delMetadataOrdersCmd;
// get tombstones for cleanup
FbCommand selTombstonesOrdersCmd = new FbCommand();
selTombstonesOrdersCmd.CommandType = CommandType.StoredProcedure;
selTombstonesOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_select_ts";
selTombstonesOrdersCmd.Parameters.Add("tombstone_aging_in_hours", FbDbType.Integer).Value = SyncUtils.TombstoneAgingInHours;
selTombstonesOrdersCmd.Parameters.Add("sync_scope_local_id", FbDbType.Integer);
adapter.SelectMetadataForCleanupCommand = selTombstonesOrdersCmd;
SyncAdapters.Add(adapter);
}
// 2. Setup provider wide commands
// There are few commands on the provider itself and not on a table sync adapter:
// SelectNewTimestampCommand: Returns the new high watermark for current sync
// SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge and scope version (timestamp)
// UpdateScopeInfoCommand: Sets the new values for sync knowledge and cleanup knowledge
//
FbCommand anchorCmd = new FbCommand();
anchorCmd.CommandType = CommandType.StoredProcedure;
anchorCmd.CommandText = "SP_GET_TIMESTAMP"; // for SQL Server 2005 SP2, use "min_active_rowversion() - 1"
anchorCmd.Parameters.Add(DbSyncSession.SyncNewTimestamp, FbDbType.Integer).Direction = ParameterDirection.Output; // TODO: Should this be BigInt
SelectNewTimestampCommand = anchorCmd;
//
// Select local replica info
//
RecreateSelectScope();
//
// Update local replica info
//
FbCommand updReplicaInfoCmd = new FbCommand();
updReplicaInfoCmd.CommandType = CommandType.StoredProcedure;
updReplicaInfoCmd.CommandText = "SP_UPDATE_SCOPE_INFO";
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeKnowledge, FbDbType.Binary, 10000);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeId, FbDbType.Guid);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupKnowledge, FbDbType.Binary, 10000);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeName, FbDbType.VarChar, 100);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncCheckConcurrency, FbDbType.Integer);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncScopeTimestamp, FbDbType.Integer);
updReplicaInfoCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
UpdateScopeInfoCommand = updReplicaInfoCmd;
//
// Select overlapping scopes
//
// get tombstones for cleanup
FbCommand overlappingScopesCmd = new FbCommand();
overlappingScopesCmd.CommandType = CommandType.StoredProcedure;
overlappingScopesCmd.CommandText = "sp_select_shared_scopes";
overlappingScopesCmd.Parameters.Add(DbSyncSession.SyncScopeName, FbDbType.VarChar, 100);
SelectOverlappingScopesCommand = overlappingScopesCmd;
//
// Update table cleanup info
//
FbCommand updScopeCleanupInfoCmd = new FbCommand();
updScopeCleanupInfoCmd.CommandType = CommandType.Text;
updScopeCleanupInfoCmd.CommandText = "update scope_info set " +
"scope_cleanup_timestamp = @" + DbSyncSession.SyncScopeCleanupTimestamp + " " +
"where scope_name = @" + DbSyncSession.SyncScopeName + " and " +
"(scope_cleanup_timestamp is null or scope_cleanup_timestamp < @" + DbSyncSession.SyncScopeCleanupTimestamp + "2);" +
"@" + DbSyncSession.SyncRowCount + " = ROW_COUNT;";
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupTimestamp, FbDbType.Integer);
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncScopeName, FbDbType.VarChar, 100);
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncScopeCleanupTimestamp + "2", FbDbType.Integer);
updScopeCleanupInfoCmd.Parameters.Add(DbSyncSession.SyncRowCount, FbDbType.Integer).Direction = ParameterDirection.Output;
UpdateScopeCleanupTimestampCommand = updScopeCleanupInfoCmd;
}
