public async Task Table_Exists()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product", "SalesLT.ProductCategory" });
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
await localOrchestrator.CreateTableAsync(table);
var exists = await localOrchestrator.ExistTableAsync(setup.Tables[0]).ConfigureAwait(false);
Assert.True(exists);
exists = await localOrchestrator.ExistTableAsync(setup.Tables[1]).ConfigureAwait(false);
Assert.False(exists);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public override bool IsValid(SyncColumn columnDefinition)
{
var typeName = columnDefinition.OriginalTypeName.ToLowerInvariant();
if (typeName.Contains("("))
{
typeName = typeName.Substring(0, typeName.IndexOf("("));
}
switch (typeName)
{
case "integer":
case "decimal":
case "bit":
case "bigint":
case "numeric":
case "blob":
case "image":
case "datetime":
case "text":
case "real":
return(true);
}
return(false);
}
internal async Task <IEnumerable <SyncColumn> > GetColumnsAsync(DbConnection connection, DbTransaction transaction)
{
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var columns = new List <SyncColumn>();
// Get the columns definition
var syncTableColumnsList = await SqlManagementUtils.GetColumnsForTableAsync((SqlConnection)connection, (SqlTransaction)transaction, this.tableName.ToString(), schema).ConfigureAwait(false);
foreach (var c in syncTableColumnsList.Rows.OrderBy(r => (int)r["column_id"]))
{
var typeName = c["type"].ToString();
var name = c["name"].ToString();
var maxLengthLong = Convert.ToInt64(c["max_length"]);
//// Gets the datastore owner dbType
//var datastoreDbType = (SqlDbType)sqlDbMetadata.ValidateOwnerDbType(typeName, false, false, maxLengthLong);
//// once we have the datastore type, we can have the managed type
//var columnType = sqlDbMetadata.ValidateType(datastoreDbType);
var sColumn = new SyncColumn(name)
{
OriginalDbType = typeName,
Ordinal = (int)c["column_id"],
OriginalTypeName = c["type"].ToString(),
MaxLength = maxLengthLong > int.MaxValue ? int.MaxValue : (int)maxLengthLong,
Precision = (byte)c["precision"],
Scale = (byte)c["scale"],
AllowDBNull = (bool)c["is_nullable"],
IsAutoIncrement = (bool)c["is_identity"],
IsUnique = c["is_unique"] != DBNull.Value ? (bool)c["is_unique"] : false,
IsCompute = (bool)c["is_computed"],
DefaultValue = c["defaultvalue"] != DBNull.Value ? c["defaultvalue"].ToString() : null
};
if (sColumn.IsAutoIncrement)
{
sColumn.AutoIncrementSeed = Convert.ToInt32(c["seed"]);
sColumn.AutoIncrementStep = Convert.ToInt32(c["step"]);
}
switch (sColumn.OriginalTypeName.ToLowerInvariant())
{
case "nchar":
case "nvarchar":
sColumn.IsUnicode = true;
break;
default:
sColumn.IsUnicode = false;
break;
}
// No unsigned type in SQL Server
sColumn.IsUnsigned = false;
columns.Add(sColumn);
}
return(columns);
}
public async Task Table_Create_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
localOrchestrator.OnTableCreating(ttca =>
{
var addingID = Environment.NewLine + $"ALTER TABLE {ttca.TableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
localOrchestrator.OnTableCreated(ttca =>
{
onCreated = true;
});
var isCreated = await localOrchestrator.CreateTableAsync(table);
Assert.True(isCreated);
Assert.True(onCreating);
Assert.True(onCreated);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "Product", "SalesLT").ConfigureAwait(false);
Assert.Equal(4, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public override byte ValidatePrecision(SyncColumn columnDefinition)
{
if (IsNumericType(columnDefinition.OriginalTypeName) && columnDefinition.Precision == 0)
{
return(10);
}
return(columnDefinition.Precision);
}
public async Task BaseOrchestrator_Provision_ShouldCreate_TrackingTable()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup {
TrackingTablesSuffix = "sync", TrackingTablesPrefix = "trck"
};
var schema = new SyncSet();
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
//schema.TrackingTablesSuffix = "sync";
//schema.TrackingTablesPrefix = "trck";
schema.Tables.Add(table);
// trackign table name is composed with prefix and suffix from setup
var trackingTableName = $"{setup.TrackingTablesPrefix}{table.TableName}{setup.TrackingTablesSuffix}";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.TrackingTable;
await localOrchestrator.ProvisionAsync(schema, provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var tbl = await SqlManagementUtils.GetTableAsync(c, null, trackingTableName, "SalesLT");
var tblName = tbl.Rows[0]["TableName"].ToString();
var schName = tbl.Rows[0]["SchemaName"].ToString();
Assert.Equal(trackingTableName, tblName);
Assert.Equal(table.SchemaName, schName);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public override bool IsValid(SyncColumn columnDefinition)
{
switch (columnDefinition.OriginalTypeName.ToLowerInvariant())
{
case "int":
case "int16":
case "int24":
case "int32":
case "int64":
case "uint16":
case "uint24":
case "uint32":
case "uint64":
case "bit":
case "integer":
case "datetime":
case "date":
case "newdate":
case "numeric":
case "decimal":
case "dec":
case "fixed":
case "tinyint":
case "mediumint":
case "bigint":
case "real":
case "double":
case "float":
case "serial":
case "smallint":
case "varchar":
case "char":
case "text":
case "longtext":
case "tinytext":
case "mediumtext":
case "nchar":
case "nvarchar":
case "enum":
case "set":
case "blob":
case "longblob":
case "tinyblob":
case "mediumblob":
case "binary":
case "varbinary":
case "year":
case "time":
case "timestamp":
return(true);
}
return(false);
}
public async Task BaseOrchestrator_Provision_SchemaCreated_If_SchemaHasColumnsDefinition()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup();
var schema = new SyncSet();
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
schema.Tables.Add(table);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
await localOrchestrator.ProvisionAsync(schema, provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var tbl = await SqlManagementUtils.GetTableAsync(c, null, "Product", "SalesLT");
var tblName = tbl.Rows[0]["TableName"].ToString();
var schName = tbl.Rows[0]["SchemaName"].ToString();
Assert.Equal(table.TableName, tblName);
Assert.Equal(table.SchemaName, schName);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "Product", "SalesLT");
Assert.Equal(3, cols.Rows.Count);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public override (byte precision, byte scale) ValidatePrecisionAndScale(SyncColumn columnDefinition)
{
var precision = columnDefinition.Precision;
var scale = columnDefinition.Scale;
if (IsNumericType(columnDefinition.OriginalTypeName) && precision == 0)
{
precision = 10;
scale = 0;
}
return(precision, scale);
}
internal async Task <IEnumerable <SyncColumn> > GetPrimaryKeysAsync(DbConnection connection, DbTransaction transaction)
{
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var syncTableKeys = await SqlManagementUtils.GetPrimaryKeysForTableAsync((SqlConnection)connection, (SqlTransaction)transaction, this.tableName.ToString(), schema).ConfigureAwait(false);
var lstKeys = new List <SyncColumn>();
foreach (var dmKey in syncTableKeys.Rows)
{
var keyColumn = SyncColumn.Create <string>((string)dmKey["columnName"]);
keyColumn.Ordinal = Convert.ToInt32(dmKey["column_id"]);
lstKeys.Add(keyColumn);
}
return(lstKeys);
}
public async Task <IEnumerable <SyncColumn> > GetPrimaryKeysAsync(DbConnection connection, DbTransaction transaction)
{
var commandColumn = @"select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName and column_key='PRI'";
var keys = new SyncTable(tableName.Unquoted().ToString());
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandColumn;
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tableName.Unquoted().ToString();
command.Parameters.Add(parameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
keys.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
var lstKeys = new List <SyncColumn>();
foreach (var key in keys.Rows)
{
var keyColumn = new SyncColumn((string)key["COLUMN_NAME"], typeof(string));
keyColumn.Ordinal = Convert.ToInt32(key["ORDINAL_POSITION"]);
lstKeys.Add(keyColumn);
}
return(lstKeys);
}
public async Task RemoteOrchestrator_Provision_SchemaFail_If_SchemaHasColumnsDefinitionButNoPrimaryKey()
{
var dbName = HelperDatabase.GetRandomName("tcp_ro_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var schema = new SyncSet();
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
schema.Tables.Add(table);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
// Overriding scope info to introduce a bad table with no primary key
scopeInfo.Schema = schema;
scopeInfo.Setup = setup;
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var se = await Assert.ThrowsAsync <SyncException>(
async() => await remoteOrchestrator.ProvisionAsync(scopeInfo, provision));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("MissingPrimaryKeyException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
internal MySqlParameter GetMySqlParameter(SyncColumn column)
{
var mySqlDbMetadata = new MySqlDbMetadata();
var parameterName = ParserName.Parse(column, "`").Unquoted().Normalized().ToString();
var sqlParameter = new MySqlParameter
{
ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{parameterName}",
DbType = column.GetDbType(),
IsNullable = column.AllowDBNull
};
#if MARIADB
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, MariaDB.MariaDBSyncProvider.ProviderType);
#elif MYSQL
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
#endif
if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double ||
sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0)
{
sqlParameter.Precision = precision;
if (scale > 0)
{
sqlParameter.Scale = scale;
}
}
else if (column.MaxLength > 0)
{
sqlParameter.Size = (int)column.MaxLength;
}
else if (sqlParameter.DbType == DbType.Guid)
{
sqlParameter.Size = 36;
}
else
{
sqlParameter.Size = -1;
}
return(sqlParameter);
}
public override DbType GetDbType(SyncColumn columnDefinition)
{
DbType stringDbType;
if (columnDefinition.IsUnicode)
{
stringDbType = columnDefinition.MaxLength <= 0 ? DbType.String : DbType.StringFixedLength;
}
else
{
stringDbType = columnDefinition.MaxLength <= 0 ? DbType.AnsiString : DbType.AnsiStringFixedLength;
}
return(columnDefinition.OriginalTypeName.ToLowerInvariant() switch
{
"int" or "integer" or "mediumint" => columnDefinition.IsUnsigned ? DbType.UInt32 : DbType.Int32,
"int16" => DbType.Int16,
"int24" or "int32" => DbType.Int32,
"int64" => DbType.Int64,
"uint16" => DbType.UInt16,
"uint24" or "uint32" => DbType.UInt32,
"uint64" => DbType.UInt64,
"bit" => DbType.Boolean,
"numeric" => DbType.VarNumeric,
"decimal" or "dec" or "fixed" or "real" or "double" or "float" => DbType.Decimal,
"tinyint" => columnDefinition.IsUnsigned ? DbType.Byte : DbType.SByte,
"bigint" => columnDefinition.IsUnsigned ? DbType.UInt64 : DbType.Int64,
"serial" => DbType.UInt64,
"smallint" => columnDefinition.IsUnsigned ? DbType.UInt16 : DbType.Int16,
"mediumtext" or "longtext" or "json" or "tinytext" => columnDefinition.IsUnicode ? DbType.String : DbType.AnsiString,
"date" => DbType.Date,
"datetime" or "newdate" => DbType.DateTime,
"blob" or "longblob" or "tinyblob" or "mediumblob" or "binary" or "varbinary" => DbType.Binary,
"year" => DbType.Int32,
"time" => DbType.Time,
"timestamp" => DbType.DateTime,
"text" or "set" or "enum" or "nchar" or "nvarchar" or "varchar" or "json" => stringDbType,
"char" => columnDefinition.MaxLength == 36 ? DbType.Guid : stringDbType,
_ => throw new Exception($"this db type {columnDefinition.GetDbType()} for column {columnDefinition.ColumnName} is not supported"),
});
public override DbType GetDbType(SyncColumn columnDefinition)
{
var typeName = columnDefinition.OriginalTypeName.ToLowerInvariant();
if (typeName.Contains("("))
{
typeName = typeName.Substring(0, typeName.IndexOf("("));
}
return(typeName.ToLowerInvariant() switch
{
"bit" => DbType.Boolean,
"integer" or "bigint" => DbType.Int64,
"numeric" or "real" or "float" => DbType.Double,
"decimal" => DbType.Decimal,
"blob" or "image" => DbType.Binary,
"datetime" => DbType.DateTime,
"time" => DbType.Time,
"text" or "varchar" => DbType.String,
_ => throw new Exception($"this type {columnDefinition.OriginalTypeName} for column {columnDefinition.ColumnName} is not supported")
});
public async Task BaseOrchestrator_Provision_SchemaFail_If_SchemaHasColumnsDefinitionButNoPrimaryKey()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup();
var schema = new SyncSet();
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
schema.Tables.Add(table);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var se = await Assert.ThrowsAsync <SyncException>(async() => await localOrchestrator.ProvisionAsync(schema, provision));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ClientSide, se.Side);
Assert.Equal("MissingPrimaryKeyException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public override bool IsValid(SyncColumn columnDefinition)
{
switch (columnDefinition.OriginalTypeName.ToLowerInvariant())
{
case "bigint":
case "binary":
case "bit":
case "char":
case "date":
case "datetime":
case "datetime2":
case "datetimeoffset":
case "decimal":
case "float":
case "int":
case "money":
case "nchar":
case "numeric":
case "nvarchar":
case "real":
case "smalldatetime":
case "smallint":
case "smallmoney":
case "sql_variant":
case "variant":
case "time":
case "timestamp":
case "tinyint":
case "uniqueidentifier":
case "varbinary":
case "varchar":
case "xml":
return(true);
}
return(false);
}
/// <summary> /// Gets and validate a max length issued from the database definition /// </summary> public abstract int GetMaxLength(SyncColumn columnDefinition);
/// <summary> /// Get precision and scale from a SchemaColumn /// </summary> public abstract (byte precision, byte scale) ValidatePrecisionAndScale(SyncColumn columnDefinition);
/// <summary> /// Validate if a column is readonly or not /// </summary> /// <param name="columnDefinition"></param> /// <returns></returns> public abstract bool ValidateIsReadonly(SyncColumn columnDefinition);
/// <summary> /// Validate if a column definition is actualy supported by the provider /// </summary> public abstract bool IsValid(SyncColumn columnDefinition);
/// <summary> /// Get a managed type from a datastore dbType /// </summary> public abstract Type GetType(SyncColumn columnDefinition);
/// <summary> /// Get precision if supported (MySql supports int(10)) /// </summary> public abstract byte ValidatePrecision(SyncColumn columnDefinition);
/// <summary> /// Check if a type name support scale /// </summary> public abstract bool IsSupportingScale(SyncColumn columnDefinition);
/// <summary> /// Get precision if supported (MySql supports int(10)) /// </summary> public abstract byte GetPrecision(SyncColumn columnDefinition);
/// <summary> /// Check if a type name is a numeric type /// </summary> public abstract bool IsNumericType(SyncColumn columnDefinition);
/// <summary> /// Get the native datastore DbType (that's why we return object instead of SqlDbType or SqliteDbType or MySqlDbType) /// </summary> public abstract object GetOwnerDbType(SyncColumn columnDefinition);
private SqlMetaData GetSqlMetadaFromType(SyncColumn column)
{
long maxLength = column.MaxLength;
var dataType = column.GetDataType();
var dbType = column.GetDbType();
var sqlDbType = (SqlDbType)this.sqlMetadata.TryGetOwnerDbType(column.OriginalDbType, dbType, false, false, column.MaxLength, this.TableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
// Since we validate length before, it's not mandatory here.
// let's say.. just in case..
if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar)
{
// set value for (MAX)
maxLength = maxLength < 0 ? SqlMetaData.Max : maxLength;
// If max length is specified (not (MAX) )
if (maxLength > 0)
{
maxLength = sqlDbType == SqlDbType.NVarChar ? Math.Min(maxLength, 4000) : Math.Min(maxLength, 8000);
}
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (dataType == typeof(char))
{
return(new SqlMetaData(column.ColumnName, sqlDbType, 1));
}
if (sqlDbType == SqlDbType.Char || sqlDbType == SqlDbType.NChar)
{
maxLength = maxLength <= 0 ? (sqlDbType == SqlDbType.NChar ? 4000 : 8000) : maxLength;
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (sqlDbType == SqlDbType.Binary)
{
maxLength = maxLength <= 0 ? 8000 : maxLength;
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (sqlDbType == SqlDbType.VarBinary)
{
// set value for (MAX)
maxLength = maxLength <= 0 ? SqlMetaData.Max : maxLength;
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (sqlDbType == SqlDbType.Decimal)
{
if (column.PrecisionSpecified && column.ScaleSpecified)
{
var(p, s) = this.sqlMetadata.ValidatePrecisionAndScale(column);
return(new SqlMetaData(column.ColumnName, sqlDbType, p, s));
}
if (column.PrecisionSpecified)
{
var p = this.sqlMetadata.ValidatePrecision(column);
return(new SqlMetaData(column.ColumnName, sqlDbType, p));
}
}
return(new SqlMetaData(column.ColumnName, sqlDbType));
}
private static SyncSet CreateSchema()
{
var set = new SyncSet();
set.StoredProceduresPrefix = "spp";
set.StoredProceduresSuffix = "sps";
set.TrackingTablesPrefix = "ttp";
set.TrackingTablesSuffix = "tts";
set.TriggersPrefix = "tp";
set.TriggersSuffix = "ts";
var tbl = new SyncTable("ServiceTickets", null);
tbl.OriginalProvider = "SqlServerProvider";
tbl.SyncDirection = Enumerations.SyncDirection.Bidirectional;
set.Tables.Add(tbl);
var c = SyncColumn.Create <int>("ServiceTicketID");
c.DbType = 8;
c.AllowDBNull = true;
c.IsAutoIncrement = true;
c.AutoIncrementStep = 1;
c.AutoIncrementSeed = 10;
c.IsCompute = false;
c.IsReadOnly = true;
tbl.Columns.Add(c);
tbl.Columns.Add(SyncColumn.Create <string>("Title"));
tbl.Columns.Add(SyncColumn.Create <string>("Description"));
tbl.Columns.Add(SyncColumn.Create <int>("StatusValue"));
tbl.Columns.Add(SyncColumn.Create <int>("EscalationLevel"));
tbl.Columns.Add(SyncColumn.Create <DateTime>("Opened"));
tbl.Columns.Add(SyncColumn.Create <DateTime>("Closed"));
tbl.Columns.Add(SyncColumn.Create <int>("CustomerID"));
tbl.PrimaryKeys.Add("ServiceTicketID");
// Add Second tables
var tbl2 = new SyncTable("Product", "SalesLT");
tbl2.SyncDirection = SyncDirection.UploadOnly;
tbl2.Columns.Add(SyncColumn.Create <int>("Id"));
tbl2.Columns.Add(SyncColumn.Create <string>("Title"));
tbl2.PrimaryKeys.Add("Id");
set.Tables.Add(tbl2);
// Add Filters
var sf = new SyncFilter("Product", "SalesLT");
sf.Parameters.Add(new SyncFilterParameter {
Name = "Title", DbType = DbType.String, MaxLength = 20, DefaultValue = "'Bikes'"
});
sf.Parameters.Add(new SyncFilterParameter {
Name = "LastName", TableName = "Customer", SchemaName = "SalesLT", AllowNull = true
});
sf.Wheres.Add(new SyncFilterWhereSideItem {
ColumnName = "Title", ParameterName = "Title", SchemaName = "SalesLT", TableName = "Product"
});
sf.Joins.Add(new SyncFilterJoin {
JoinEnum = Join.Right, TableName = "SalesLT.ProductCategory", LeftColumnName = "LCN", LeftTableName = "SalesLT.Product", RightColumnName = "RCN", RightTableName = "SalesLT.ProductCategory"
});
sf.CustomWheres.Add("1 = 1");
set.Filters.Add(sf);
// Add Relations
var keys = new[] { new SyncColumnIdentifier("ProductId", "ServiceTickets") };
var parentKeys = new[] { new SyncColumnIdentifier("ProductId", "Product", "SalesLT") };
var rel = new SyncRelation("AdventureWorks_Product_ServiceTickets", keys, parentKeys);
set.Relations.Add(rel);
return(set);
}
public override bool ValidateIsReadonly(SyncColumn columnDefinition)
{
return(columnDefinition.OriginalTypeName.ToLowerInvariant() == "timestamp");
}
