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