private static void InitParameterRow(SyncRow parameter, string schema, string procName)
{
parameter["SPECIFIC_CATALOG"] = null;
parameter["SPECIFIC_SCHEMA"] = schema;
parameter["SPECIFIC_NAME"] = procName;
parameter["PARAMETER_MODE"] = "IN";
parameter["ORDINAL_POSITION"] = 0;
}
public void AfterDeserialized(SyncRow row)
{
// Convert all DateTime back from ticks
foreach (var col in row.Table.Columns.Where(c => c.GetDataType() == typeof(DateTime)))
{
if (row[col.ColumnName] != null)
{
row[col.ColumnName] = new DateTime(Convert.ToInt64(row[col.ColumnName]));
}
}
}
public void BeforeSerialize(SyncRow row)
{
// Convert all DateTime columns to ticks
foreach (var col in row.Table.Columns.Where(c => c.GetDataType() == typeof(DateTime)))
{
if (row[col.ColumnName] != null)
{
row[col.ColumnName] = ((DateTime)row[col.ColumnName]).Ticks;
}
}
}
public void SyncTable_EnsureSchema_Check_ColumnsAndRows()
{
SyncTable tCustomer = new SyncTable("Customer");
tCustomer.Columns.Add(new SyncColumn("ID", typeof(Guid)));
tCustomer.Columns.Add(new SyncColumn("Name", typeof(string)));
SyncRow tCustomerRow = new SyncRow(tCustomer);
tCustomerRow["ID"] = "A";
tCustomerRow["Name"] = "B";
tCustomer.Rows.Add(tCustomerRow);
tCustomer.EnsureTable(new SyncSet());
Assert.Equal(tCustomer, tCustomer.Columns.Table);
Assert.Equal(tCustomer, tCustomer.Rows.Table);
}
public void AfterDeserialized(SyncRow row)
{
// Only convert for table Product
if (row.Table.TableName != "Product")
{
return;
}
// Decode photo
row["ThumbNailPhoto"] = Convert.FromBase64String((string)row["ThumbNailPhoto"]);
// Convert all DateTime back from ticks
foreach (var col in row.Table.Columns.Where(c => c.GetDataType() == typeof(DateTime)))
{
if (row[col.ColumnName] != null)
{
row[col.ColumnName] = new DateTime(Convert.ToInt64(row[col.ColumnName]));
}
}
}
private static void ParseDataTypeSize(SyncRow row, string size)
{
var metadata = new MySqlDbMetadata();
size = size.Trim('(', ')');
string[] parts = size.Split(',');
if (!metadata.IsNumericType(row["DATA_TYPE"].ToString()))
{
row["CHARACTER_MAXIMUM_LENGTH"] = Int32.Parse(parts[0]);
// will set octet length in a minute
}
else
{
row["NUMERIC_PRECISION"] = Int32.Parse(parts[0]);
if (parts.Length == 2)
{
row["NUMERIC_SCALE"] = Int32.Parse(parts[1]);
}
}
}
private static string GetDataTypeDefaults(string type, SyncRow row)
{
var metadata = new MySqlDbMetadata();
string format = "({0},{1})";
object precision = row["NUMERIC_PRECISION"];
if (metadata.IsNumericType(type) && string.IsNullOrEmpty((string)row["NUMERIC_PRECISION"]))
{
row["NUMERIC_PRECISION"] = 10;
row["NUMERIC_SCALE"] = 0;
if (!metadata.SupportScale(type))
{
format = "({0})";
}
return(String.Format(format, row["NUMERIC_PRECISION"],
row["NUMERIC_SCALE"]));
}
return(String.Empty);
}
public void BeforeSerialize(SyncRow row)
{
// Each row belongs to a Table with its own Schema
// Easy to filter if needed
if (row.Table.TableName != "Product")
{
return;
}
// Encode a specific column, named "ThumbNailPhoto"
if (row["ThumbNailPhoto"] != null)
{
row["ThumbNailPhoto"] = Convert.ToBase64String((byte[])row["ThumbNailPhoto"]);
}
// Convert all DateTime columns to ticks
foreach (var col in row.Table.Columns.Where(c => c.GetDataType() == typeof(DateTime)))
{
if (row[col.ColumnName] != null)
{
row[col.ColumnName] = ((DateTime)row[col.ColumnName]).Ticks;
}
}
}
public async Task WriteRowToFileAsync(SyncRow row, SyncTable shemaTable)
{
writer.WriteStartArray();
var innerRow = row.ToArray();
if (this.writingRowAsync != null)
{
var str = await this.writingRowAsync(shemaTable, innerRow).ConfigureAwait(false);
writer.WriteValue(str);
}
else
{
for (var i = 0; i < innerRow.Length; i++)
{
writer.WriteValue(innerRow[i]);
}
}
writer.WriteEndArray();
writer.WriteWhitespace(Environment.NewLine);
writer.Flush();
}
private static string ParseDataType(SyncRow row, MySqlTokenizer tokenizer)
{
StringBuilder dtd = new StringBuilder(tokenizer.NextToken().ToUpperInvariant());
row["DATA_TYPE"] = dtd.ToString();
string type = row["DATA_TYPE"].ToString();
string token = tokenizer.NextToken();
if (token == "(")
{
token = tokenizer.ReadParenthesis();
dtd.AppendFormat(CultureInfo.InvariantCulture, "{0}", token);
if (type != "ENUM" && type != "SET")
{
ParseDataTypeSize(row, token);
}
token = tokenizer.NextToken();
}
else
{
dtd.Append(GetDataTypeDefaults(type, row));
}
while (token != ")" &&
token != "," &&
String.Compare(token, "begin", StringComparison.OrdinalIgnoreCase) != 0 &&
String.Compare(token, "return", StringComparison.OrdinalIgnoreCase) != 0)
{
if (String.Compare(token, "CHARACTER", StringComparison.OrdinalIgnoreCase) == 0 ||
String.Compare(token, "BINARY", StringComparison.OrdinalIgnoreCase) == 0)
{
} // we don't need to do anything with this
else if (String.Compare(token, "SET", StringComparison.OrdinalIgnoreCase) == 0 ||
String.Compare(token, "CHARSET", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = tokenizer.NextToken();
}
else if (String.Compare(token, "ASCII", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = "latin1";
}
else if (String.Compare(token, "UNICODE", StringComparison.OrdinalIgnoreCase) == 0)
{
row["CHARACTER_SET_NAME"] = "ucs2";
}
else if (String.Compare(token, "COLLATE", StringComparison.OrdinalIgnoreCase) == 0)
{
row["COLLATION_NAME"] = tokenizer.NextToken();
}
else
{
dtd.AppendFormat(CultureInfo.InvariantCulture, " {0}", token);
}
token = tokenizer.NextToken();
}
if (dtd.Length > 0)
{
row["DTD_IDENTIFIER"] = dtd.ToString();
}
// now default the collation if one wasn't given
//if (string.IsNullOrEmpty((string)row["COLLATION_NAME"]) &&
// !string.IsNullOrEmpty((string)row["CHARACTER_SET_NAME"]))
// row["COLLATION_NAME"] = CharSetMap.GetDefaultCollation(
// row["CHARACTER_SET_NAME"].ToString(), connection);
// now set the octet length
//if (row["CHARACTER_MAXIMUM_LENGTH"] != null)
//{
// if (row["CHARACTER_SET_NAME"] == null)
// row["CHARACTER_SET_NAME"] = "";
// row["CHARACTER_OCTET_LENGTH"] =
// CharSetMap.GetMaxLength((string)row["CHARACTER_SET_NAME"], connection) *
// (int)row["CHARACTER_MAXIMUM_LENGTH"];
//}
return(token);
}
static void DoWork()
{
switch (applicationConfig.Action.ToLower())
{
case "makewarehousesales":
log.Info("Calling makewarehousesales");
Init();
BiotrackEngine reporting = new BiotrackEngine(programConfig.username, programConfig.password, programConfig.host, programConfig.port, programConfig.database);
WarehouseEngine warehouse = new WarehouseEngine(programConfig.warehouse_username, programConfig.warehouse_password, programConfig.warehouse_host, programConfig.warehouse_port);
warehouse = WarehouseEngine.GetWarehouse(warehouse, true);
SyncFoundation();
var inputStartTime = applicationConfig.Options[0].Split('=')[1];
var inputEndTime = applicationConfig.Options[1].Split('=')[1];
log.Info(string.Format("starttime => {0}, endtime => {1}", inputStartTime, inputEndTime));
var data = reporting.MakeSalesWarehouse(inputStartTime, inputEndTime);
var syncedrows = warehouse.InsertSalesWarehouseData((List<SalesWarehouseRow>)data);
var syncrow = new SyncRow { Success = true, Tablename = "saleswarehouse", Timestamp = DateTime.UtcNow, RowsSynced = syncedrows };
warehouse.InsertSyncRow(syncrow);
break;
case "makewarehouseinventory":
log.Info("Calling makewarehouseinventory");
Init();
reporting = new BiotrackEngine(programConfig.username, programConfig.password, programConfig.host, programConfig.port, programConfig.database);
warehouse = new WarehouseEngine(programConfig.warehouse_username, programConfig.warehouse_password, programConfig.warehouse_host, programConfig.warehouse_port);
break;
case "deletedatabase":
log.Info("Calling deletedatabase");
Init();
warehouse = new WarehouseEngine(programConfig.warehouse_username, programConfig.warehouse_password, programConfig.warehouse_host, programConfig.warehouse_port);
warehouse.DropWarehouseDatabase();
break;
case "applyconfig":
log.Info("Calling config");
ApplyConfiguration(applicationConfig);
break;
default:
ShowHelp();
break;
}
}
/// <summary>
/// Executing a batch command
/// </summary>
public override async Task ExecuteBatchCommandAsync(DbCommand cmd, Guid senderScopeId, IEnumerable <SyncRow> applyRows, SyncTable schemaChangesTable,
SyncTable failedRows, long?lastTimestamp, DbConnection connection, DbTransaction transaction = null)
{
var applyRowsCount = applyRows.Count();
if (applyRowsCount <= 0)
{
return;
}
var dataRowState = DataRowState.Unchanged;
var records = new List <SqlDataRecord>(applyRowsCount);
SqlMetaData[] metadatas = new SqlMetaData[schemaChangesTable.Columns.Count];
for (int i = 0; i < schemaChangesTable.Columns.Count; i++)
{
metadatas[i] = GetSqlMetadaFromType(schemaChangesTable.Columns[i]);
}
try
{
foreach (var row in applyRows)
{
dataRowState = row.RowState;
var record = new SqlDataRecord(metadatas);
int sqlMetadataIndex = 0;
for (int i = 0; i < schemaChangesTable.Columns.Count; i++)
{
var schemaColumn = schemaChangesTable.Columns[i];
// Get the default value
//var columnType = schemaColumn.GetDataType();
dynamic defaultValue = schemaColumn.GetDefaultValue();
dynamic rowValue = row[i];
// metadatas don't have readonly values, so get from sqlMetadataIndex
var sqlMetadataType = metadatas[sqlMetadataIndex].SqlDbType;
if (rowValue != null)
{
var columnType = rowValue.GetType();
switch (sqlMetadataType)
{
case SqlDbType.BigInt:
if (columnType != typeof(long))
{
rowValue = SyncTypeConverter.TryConvertTo <long>(rowValue);
}
break;
case SqlDbType.Bit:
if (columnType != typeof(bool))
{
rowValue = SyncTypeConverter.TryConvertTo <bool>(rowValue);
}
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
if (columnType != typeof(DateTime))
{
rowValue = SyncTypeConverter.TryConvertTo <DateTime>(rowValue);
}
if (sqlMetadataType == SqlDbType.DateTime && rowValue < SqlDateMin)
{
rowValue = SqlDateMin;
}
if (sqlMetadataType == SqlDbType.SmallDateTime && rowValue < SqlSmallDateMin)
{
rowValue = SqlSmallDateMin;
}
break;
case SqlDbType.DateTimeOffset:
if (columnType != typeof(DateTimeOffset))
{
rowValue = SyncTypeConverter.TryConvertTo <DateTimeOffset>(rowValue);
}
break;
case SqlDbType.Decimal:
if (columnType != typeof(decimal))
{
rowValue = SyncTypeConverter.TryConvertTo <decimal>(rowValue);
}
break;
case SqlDbType.Float:
if (columnType != typeof(double))
{
rowValue = SyncTypeConverter.TryConvertTo <double>(rowValue);
}
break;
case SqlDbType.Real:
if (columnType != typeof(float))
{
rowValue = SyncTypeConverter.TryConvertTo <float>(rowValue);
}
break;
case SqlDbType.Image:
case SqlDbType.Binary:
case SqlDbType.VarBinary:
if (columnType != typeof(byte[]))
{
rowValue = SyncTypeConverter.TryConvertTo <byte[]>(rowValue);
}
break;
case SqlDbType.Variant:
break;
case SqlDbType.Int:
if (columnType != typeof(int))
{
rowValue = SyncTypeConverter.TryConvertTo <int>(rowValue);
}
break;
case SqlDbType.Money:
case SqlDbType.SmallMoney:
if (columnType != typeof(decimal))
{
rowValue = SyncTypeConverter.TryConvertTo <decimal>(rowValue);
}
break;
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Char:
if (columnType != typeof(string))
{
rowValue = SyncTypeConverter.TryConvertTo <string>(rowValue);
}
break;
case SqlDbType.SmallInt:
if (columnType != typeof(short))
{
rowValue = SyncTypeConverter.TryConvertTo <short>(rowValue);
}
break;
case SqlDbType.Time:
if (columnType != typeof(TimeSpan))
{
rowValue = SyncTypeConverter.TryConvertTo <TimeSpan>(rowValue);
}
break;
case SqlDbType.Timestamp:
break;
case SqlDbType.TinyInt:
if (columnType != typeof(byte))
{
rowValue = SyncTypeConverter.TryConvertTo <byte>(rowValue);
}
break;
case SqlDbType.Udt:
throw new ArgumentException($"Can't use UDT as SQL Type");
case SqlDbType.UniqueIdentifier:
if (columnType != typeof(Guid))
{
rowValue = SyncTypeConverter.TryConvertTo <Guid>(rowValue);
}
break;
}
}
if (rowValue == null)
{
rowValue = DBNull.Value;
}
record.SetValue(sqlMetadataIndex, rowValue);
sqlMetadataIndex++;
}
records.Add(record);
}
}
catch (Exception ex)
{
throw new InvalidOperationException($"Can't create a SqlRecord based on the rows we have: {ex.Message}");
}
var sqlParameters = cmd.Parameters as SqlParameterCollection;
sqlParameters["@changeTable"].TypeName = string.Empty;
sqlParameters["@changeTable"].Value = records;
if (sqlParameters.Contains("@sync_min_timestamp"))
{
sqlParameters["@sync_min_timestamp"].Value = lastTimestamp.HasValue ? (object)lastTimestamp.Value : DBNull.Value;
}
if (sqlParameters.Contains("@sync_scope_id"))
{
sqlParameters["@sync_scope_id"].Value = senderScopeId;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
cmd.Transaction = transaction;
using var dataReader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
while (dataReader.Read())
{
//var itemArray = new object[dataReader.FieldCount];
//var itemArray = new object[failedRows.Columns.Count];
var itemArray = new SyncRow(schemaChangesTable, dataRowState);
for (var i = 0; i < dataReader.FieldCount; i++)
{
var columnValueObject = dataReader.GetValue(i);
var columnName = dataReader.GetName(i);
var columnValue = columnValueObject == DBNull.Value ? null : columnValueObject;
var failedColumn = failedRows.Columns[columnName];
var failedIndexColumn = failedRows.Columns.IndexOf(failedColumn);
itemArray[failedIndexColumn] = columnValue;
}
// don't care about row state
// Since it will be requested by next request from GetConflict()
failedRows.Rows.Add(itemArray);
}
dataReader.Close();
}
catch (DbException ex)
{
Debug.WriteLine(ex.Message);
throw;
}
finally
{
records.Clear();
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}