public void DmTable_Clone_Copy()
{
var tbl = new DmTable("ServiceTickets");
var id = new DmColumn <Guid>("ServiceTicketID");
tbl.Columns.Add(id);
var key = new DmKey(new DmColumn[] { id });
tbl.PrimaryKey = key;
tbl.Columns.Add(new DmColumn <string>("Title"));
tbl.Columns.Add(new DmColumn <string>("Description"));
tbl.Columns.Add(new DmColumn <int>("StatusValue"));
tbl.Columns.Add(new DmColumn <int>("EscalationLevel"));
tbl.Columns.Add(new DmColumn <DateTime>("Opened"));
tbl.Columns.Add(new DmColumn <DateTime>("Closed"));
tbl.Columns.Add(new DmColumn <int>("CustomerID"));
var st = tbl.NewRow();
st["ServiceTicketID"] = Guid.NewGuid();
st["Title"] = "Titre AER";
st["Description"] = "Description 2";
st["EscalationLevel"] = 1;
st["StatusValue"] = 2;
st["Opened"] = DateTime.Now;
st["Closed"] = null;
st["CustomerID"] = 1;
tbl.Rows.Add(st);
var tbl2 = tbl.Clone();
Assert.NotSame(tbl2, tbl);
Assert.Empty(tbl2.Rows);
Assert.Equal(8, tbl2.Columns.Count);
var tbl3 = tbl.Copy();
Assert.NotSame(tbl3, tbl2);
Assert.NotSame(tbl3, tbl);
Assert.Equal(8, tbl3.Columns.Count);
Assert.NotEmpty(tbl3.Rows);
}
internal void ReadSchemaIntoDmSet(DmSet ds)
{
var dmTableSurrogateArray = this.Tables;
for (int i = 0; i < dmTableSurrogateArray.Count; i++)
{
DmTableSurrogate dmTableSurrogate = dmTableSurrogateArray[i];
DmTable dmTable = new DmTable();
dmTableSurrogate.ReadSchemaIntoDmTable(dmTable);
dmTable.Culture = new CultureInfo(dmTableSurrogate.CultureInfoName);
dmTable.CaseSensitive = dmTableSurrogate.CaseSensitive;
dmTable.TableName = dmTableSurrogate.TableName;
ds.Tables.Add(dmTable);
}
if (this.Relations != null && this.Relations.Count > 0)
{
foreach (var dmRelationSurrogate in this.Relations)
{
DmColumn[] parentColumns = new DmColumn[dmRelationSurrogate.ParentKeySurrogates.Length];
DmColumn[] childColumns = new DmColumn[dmRelationSurrogate.ChildKeySurrogates.Length];
for (int i = 0; i < parentColumns.Length; i++)
{
var columnName = dmRelationSurrogate.ParentKeySurrogates[i].ColumnName;
var tableName = dmRelationSurrogate.ParentKeySurrogates[i].TableName;
parentColumns[i] = ds.Tables[tableName].Columns[columnName];
columnName = dmRelationSurrogate.ChildKeySurrogates[i].ColumnName;
tableName = dmRelationSurrogate.ChildKeySurrogates[i].TableName;
childColumns[i] = ds.Tables[tableName].Columns[columnName];
}
DmRelation relation = new DmRelation(dmRelationSurrogate.RelationName, parentColumns, childColumns);
ds.Relations.Add(relation);
}
}
}
internal static (byte length, byte scale) GetSqlTypePrecision(this DmColumn column)
{
string sizeString = string.Empty;
switch (column.DbType)
{
case DbType.Decimal:
case DbType.Double:
case DbType.Single:
case DbType.VarNumeric:
if (!column.PrecisionSpecified || !column.ScaleSpecified)
{
break;
}
return(column.Precision, column.Scale);
}
return(0, 0);
}
public void DmTable_ImportRow()
{
var tbl2 = new DmTable();
var id = new DmColumn <Guid>("ServiceTicketID");
tbl2.Columns.Add(id);
var key = new DmKey(new DmColumn[] { id });
tbl2.PrimaryKey = key;
tbl2.Columns.Add(new DmColumn <string>("Title"));
tbl2.Columns.Add(new DmColumn <string>("Description"));
tbl2.Columns.Add(new DmColumn <int>("StatusValue"));
tbl2.Columns.Add(new DmColumn <int>("EscalationLevel"));
tbl2.Columns.Add(new DmColumn <DateTime>("Opened"));
tbl2.Columns.Add(new DmColumn <DateTime>("Closed"));
tbl2.Columns.Add(new DmColumn <int>("CustomerID"));
var st = tbl2.NewRow();
st["ServiceTicketID"] = Guid.NewGuid();
st["Title"] = "Titre AER";
st["Description"] = "Description 2";
st["EscalationLevel"] = 1;
st["StatusValue"] = 2;
st["Opened"] = DateTime.Now;
st["Closed"] = null;
st["CustomerID"] = 1;
tbl2.Rows.Add(st);
// Importing into tbl
var st2 = this.tbl.ImportRow(st);
// acceptchanges to change the row state
this.tbl.AcceptChanges();
Assert.NotEqual(st.RowState, st2.RowState);
// making change to be sure
st2["CustomerID"] = 2;
Assert.Equal(2, st2["CustomerID"]);
Assert.Equal(1, st["CustomerID"]);
}
/// <summary>
/// Copies the table schema from a DmTableSurrogate object into a DmTable object.
/// </summary>
public void ReadSchemaIntoDmTable(DmTable dt)
{
if (dt == null)
{
throw new ArgumentNullException("dt", "DmTable");
}
dt.TableName = this.TableName;
dt.Culture = new CultureInfo(this.CultureInfoName);
dt.Schema = this.Schema;
dt.CaseSensitive = this.CaseSensitive;
dt.OriginalProvider = this.OriginalProvider;
dt.SyncDirection = this.SyncDirection;
dt.TrackingTablesPrefix = this.TrackingTablesPrefix;
dt.TrackingTablesSuffix = this.TrackingTablesSuffix;
dt.StoredProceduresPrefix = this.StoredProceduresPrefix;
dt.StoredProceduresSuffix = this.StoredProceduresSuffix;
dt.TriggersPrefix = this.TriggersPrefix;
dt.TriggersSuffix = this.TriggersSuffix;
for (int i = 0; i < this.Columns.Count; i++)
{
DmColumn dmColumn = this.Columns[i].ConvertToDmColumn();
dt.Columns.Add(dmColumn);
}
if (this.PrimaryKeys != null && this.PrimaryKeys.Count > 0)
{
DmColumn[] keyColumns = new DmColumn[this.PrimaryKeys.Count];
for (int i = 0; i < this.PrimaryKeys.Count; i++)
{
string columnName = this.PrimaryKeys[i];
keyColumns[i] = dt.Columns.First(c => dt.IsEqual(c.ColumnName, columnName));
}
DmKey key = new DmKey(keyColumns);
dt.PrimaryKey = key;
}
}
public string CreatePkCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{trackingName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY (");
for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
{
DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
public override bool IsValid(DmColumn columnDefinition)
{
switch (columnDefinition.OriginalTypeName.ToLowerInvariant())
{
case "number":
case "date":
case "datetime":
case "char":
case "datetime2":
case "nchar":
case "nvarchar":
case "nvarchar2":
case "varchar2":
case "timestamp":
case "blob":
case "clob":
case "varchar":
return(true);
}
return(false);
}
private SqlCommand BuildPkCommand()
{
string[] localName = new string[] { };
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"ALTER TABLE {tableName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{tableName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
{
DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(new SqlCommand(stringBuilder.ToString()));
}
public void DmRow_Add_Rows()
{
var tbl = new DmTable("ServiceTickets");
var idColumn = new DmColumn <Guid>("ServiceTicketID");
tbl.Columns.Add(idColumn);
var key = new DmKey(new DmColumn[] { idColumn });
tbl.PrimaryKey = key;
var titleColumn = new DmColumn <string>("Title");
tbl.Columns.Add(titleColumn);
var statusValueColumn = new DmColumn <int>("StatusValue");
tbl.Columns.Add(statusValueColumn);
var openedColumn = new DmColumn <DateTime>("Opened");
tbl.Columns.Add(openedColumn);
var st = tbl.NewRow();
Assert.Null(st[0]);
Assert.Null(st[1]);
var id = Guid.NewGuid();
var dateNow = DateTime.Now;
st["ServiceTicketID"] = id;
st["Title"] = "Titre AER";
st["StatusValue"] = 2;
st["Opened"] = dateNow;
tbl.Rows.Add(st);
Assert.Equal(id, st["ServiceTicketID"]);
Assert.Equal("Titre AER", st["Title"]);
Assert.Equal(2, st["StatusValue"]);
Assert.Equal(dateNow, st["Opened"]);
}
/// <summary>
/// Converts to unicode Sql server char (nchar)
/// </summary>
private static DmColumn MySql_Char_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
// a char(0) is possible in mysql
if (mySqlColumn.MaxLength == 0)
{
sqlColumn.MaxLength = 1;
}
else
{
sqlColumn.MaxLength = mySqlColumn.MaxLength >= 1 ? mySqlColumn.MaxLength : -1;
}
// set if it's a fixed size string
sqlColumn.DbType = sqlColumn.MaxLength > 0 ? DbType.StringFixedLength : DbType.String;
sqlColumn.OriginalDbType = "SqlDbType.NChar";
sqlColumn.OriginalTypeName = "nchar";
return(sqlColumn);
}
private SqlCommand BuildPkCommand()
{
string[] localName = new string[] { };
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"ALTER TABLE {tableName.QuotedString} ADD CONSTRAINT [PK_{tableName.ObjectName}] PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
{
DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "[", "]").QuotedString;
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(new SqlCommand(stringBuilder.ToString()));
}
internal static MySqlParameter GetMySqlParameter(this DmColumn column)
{
MySqlDbMetadata mySqlDbMetadata = new MySqlDbMetadata();
var parameterName = ParserName.Parse(column).Unquoted().Normalized().ToString();
MySqlParameter sqlParameter = new MySqlParameter
{
ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{parameterName}",
DbType = column.DbType,
IsNullable = column.AllowDBNull
};
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.MaxLength, column.Precision, column.Scale, column.Table.OriginalProvider, MySqlSyncProvider.ProviderType);
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);
}
private void AssertIsEqual(DmColumn c, DmColumn d)
{
Assert.Equal(c.AllowDBNull, d.AllowDBNull);
Assert.Equal(c.IsAutoIncrement, d.IsAutoIncrement);
Assert.Equal(c.ColumnName, d.ColumnName);
Assert.Equal(c.DataType, d.DataType);
Assert.Equal(c.DbType, d.DbType);
Assert.Equal(c.DefaultValue, d.DefaultValue);
Assert.Equal(c.IsValueType, d.IsValueType);
Assert.Equal(c.MaxLength, d.MaxLength);
Assert.Equal(c.Ordinal, d.Ordinal);
Assert.Equal(c.Precision, d.Precision);
Assert.Equal(c.PrecisionSpecified, d.PrecisionSpecified);
Assert.Equal(c.IsReadOnly, d.IsReadOnly);
Assert.Equal(c.Scale, d.Scale);
Assert.Equal(c.ScaleSpecified, d.ScaleSpecified);
Assert.Equal(c.IsUnique, d.IsUnique);
Assert.Equal(c.IsUnsigned, d.IsUnsigned);
Assert.Equal(c.IsUnicode, d.IsUnicode);
Assert.Equal(c.IsCompute, d.IsCompute);
Assert.Equal(c.Table.TableName, d.Table.TableName);
}
public string CreatePkCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append($"ALTER TABLE {trackingName.QuotedString} ADD CONSTRAINT [PK_{trackingName.UnquotedStringWithUnderScore}] PRIMARY KEY (");
for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
{
DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "[", "]").QuotedString;
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
/// <summary>
/// Constructs a DmColumn object based on a DmColumnSurrogate object.
/// </summary>
public DmColumn ConvertToDmColumn()
{
DmColumn dmColumn = DmColumn.CreateColumn(this.ColumnName, DmUtils.GetTypeFromAssemblyQualifiedName(this.DataType));
dmColumn.dbTypeAllowed = this.dbTypeAllowed;
if (dmColumn.dbTypeAllowed)
{
dmColumn.DbType = (DbType)this.DbType;
}
dmColumn.AllowDBNull = this.AllowDBNull;
dmColumn.ReadOnly = this.ReadOnly;
dmColumn.MaxLength = this.MaxLength;
dmColumn.AutoIncrement = this.AutoIncrement;
dmColumn.Precision = this.Precision;
dmColumn.PrecisionSpecified = this.PrecisionSpecified;
dmColumn.Scale = this.Scale;
dmColumn.ScaleSpecified = this.ScaleSpecified;
dmColumn.Unique = this.Unique;
dmColumn.SetOrdinal(this.Ordinal);
return(dmColumn);
}
/// <summary>
/// if scale is sup to 0 then return numeric otherwise, return a float
/// </summary>
private static DmColumn MySql_Double_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = -1;
if (mySqlColumn.Scale > 0)
{
sqlColumn.DbType = DbType.VarNumeric;
sqlColumn.OriginalDbType = "SqlDbType.Numeric";
sqlColumn.OriginalTypeName = "numeric";
}
else
{
sqlColumn.DbType = DbType.Double;
// if we convert from double, the float will be 53 or if we convert from float it will be 24
sqlColumn.Precision = mySqlColumn.OriginalTypeName.ToLowerInvariant() == "double" ? (byte)53 : (byte)24;
sqlColumn.Scale = 0;
sqlColumn.OriginalDbType = "SqlDbType.Float";
sqlColumn.OriginalTypeName = "float";
}
return(sqlColumn);
}
/// <summary>
/// Converts to Varbinary(N)
/// </summary>
private static DmColumn MySql_VarBinary_To_SqlServer(DmColumn mySqlColumn)
{
var maxlen = mySqlColumn.MaxLength;
// check length.
// Exception on 0 and go to Varbinary(max) if > 8000
if (maxlen == 0)
{
maxlen = 1;
}
else if (maxlen > 8000)
{
maxlen = -1;
}
var sqlColumn = mySqlColumn.Clone();
sqlColumn.DbType = DbType.Binary;
sqlColumn.MaxLength = maxlen;
sqlColumn.OriginalDbType = "SqlDbType.VarBinary";
sqlColumn.OriginalTypeName = "varbinary";
return(sqlColumn);
}
internal static MySqlParameter GetMySqlParameter(this DmColumn column)
{
MySqlDbMetadata mySqlDbMetadata = new MySqlDbMetadata();
MySqlParameter sqlParameter = new MySqlParameter
{
ParameterName = $"in{column.ColumnName}",
DbType = column.DbType,
IsNullable = column.AllowDBNull
};
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.Precision, column.Scale, column.Table.OriginalProvider, MySqlSyncProvider.ProviderType);
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);
}
internal static NpgsqlParameter GetPostgreSqlParameter(this DmColumn column)
{
var mySqlDbMetadata = new PostgreSqlDbMetadata();
var sqlParameter = new NpgsqlParameter
{
ParameterName = $"{PostgreSqlBuilderProcedure.PgsqlPrefixParameter}{column.ColumnName}",
DbType = column.DbType,
IsNullable = column.AllowDBNull
};
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.Precision, column.Scale, column.Table.OriginalProvider, PostgreSqlSyncProvider.ProviderType);
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 = column.MaxLength;
}
else if (sqlParameter.DbType == DbType.Guid)
{
//sqlParameter.Size = 36;
}
else
{
sqlParameter.Size = -1;
}
return(sqlParameter);
}
public override bool IsValid(DmColumn 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);
}
public DmRelationTests()
{
_set = new DmSet();
clientTable = new DmTable("Client");
clientTypeTable = new DmTable("TypeClient");
_set.Tables.Add(clientTable);
_set.Tables.Add(clientTypeTable);
DmColumn Col0 = new DmColumn <int>("ClientId");
DmColumn Col1 = new DmColumn <int>("ClientType");
DmColumn Col2 = new DmColumn <String>("ClientName");
clientTable.Columns.Add(Col0);
clientTable.Columns.Add(Col1);
clientTable.Columns.Add(Col2);
clientTable.PrimaryKey = new DmKey(Col0);
DmColumn Col3 = new DmColumn <int>("TypeId");
DmColumn Col4 = new DmColumn <string>("TypeName");
clientTypeTable.Columns.Add(Col3);
clientTypeTable.Columns.Add(Col4);
clientTypeTable.PrimaryKey = new DmKey(Col3);
}
internal static string GetSqlTypePrecisionString(this DmColumn column)
{
string sizeString = string.Empty;
switch (column.DbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.Binary:
case DbType.String:
case DbType.StringFixedLength:
if (column.MaxLength > 0)
{
sizeString = $"({column.MaxLength})";
}
else
{
sizeString = $"(MAX)";
}
break;
case DbType.Decimal:
case DbType.Double:
case DbType.Single:
case DbType.VarNumeric:
if (!column.PrecisionSpecified || !column.ScaleSpecified)
{
break;
}
sizeString = $"({ column.Precision}, {column.Scale})";
break;
}
return(sizeString);
}
public override bool IsValid(DmColumn columnDefinition)
{
switch (columnDefinition.OriginalTypeName.ToLowerInvariant())
{
case "int":
case "int4":
case "integer":
case "int8":
case "int2":
case "bigint":
case "smallint":
case "serial":
case "bigserial":
case "float8":
case "float4":
case "real":
case "smallserial":
case "serial2":
case "serial4":
case "varchar":
case "char":
case "text":
case "bit":
case "bytea":
case "xml":
case "timestamp":
case "date":
case "time":
case "uuid":
case "character varying":
case "timestamp without time zone":
case "time without time zone":
return(true);
}
return(false);
}
private SqlMetaData GetSqlMetadaFromType(DmColumn column)
{
SqlMetaData smd = null;
var sqlDbType = column.GetSqlDbType();
var dbType = column.DbType;
var precision = column.GetSqlTypePrecision();
int maxLength = column.MaxLength;
if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar)
{
maxLength = column.MaxLength <= 0 ? ((sqlDbType == SqlDbType.NVarChar) ? 4000 : 8000) : column.MaxLength;
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (column.DataType == typeof(char))
{
return(new SqlMetaData(column.ColumnName, sqlDbType, 1));
}
smd = new SqlMetaData(column.ColumnName, sqlDbType);
return(smd);
}
public List <DmColumn> GetTableDefinition()
{
List <DmColumn> columns = new List <DmColumn>();
// Get the columns definition
var dmColumnsList = PostgreSqlManagementUtils.ColumnsForTable(_sqlConnection, _sqlTransaction, TableName);
var postgreSqlDbMetadata = new PostgreSqlDbMetadata();
foreach (var c in dmColumnsList.Rows.OrderBy(r => (int)r["ordinal_position"]))
{
var typeName = c["data_type"].ToString();
var name = c["column_name"].ToString();
// Gets the datastore owner dbType
var datastoreDbType = (NpgsqlDbType)postgreSqlDbMetadata.ValidateOwnerDbType(typeName, false, false);
// once we have the datastore type, we can have the managed type
Type columnType = postgreSqlDbMetadata.ValidateType(datastoreDbType);
var dbColumn = DmColumn.CreateColumn(name, columnType);
dbColumn.OriginalTypeName = typeName;
dbColumn.SetOrdinal(Convert.ToInt32(c["ordinal_position"]));
var maxLengthLong = c["character_octet_length"] != DBNull.Value ? Convert.ToInt64(c["character_octet_length"]) : 0;
dbColumn.MaxLength = maxLengthLong > Int32.MaxValue ? Int32.MaxValue : (Int32)maxLengthLong;
dbColumn.Precision = c["numeric_precision"] != DBNull.Value ? Convert.ToByte(c["numeric_precision"]) : (byte)0;
dbColumn.Scale = c["numeric_scale"] != DBNull.Value ? Convert.ToByte(c["numeric_scale"]) : (byte)0;
dbColumn.AllowDBNull = (String)c["is_nullable"] != "NO";
dbColumn.AutoIncrement = typeName.Contains("serial");
dbColumn.IsUnsigned = false;
columns.Add(dbColumn);
}
return(columns);
}
public void AddFilterColumn(DmColumn filterColumn)
{
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
using (var command = new SqlCommand())
{
if (!alreadyOpened)
{
this.connection.Open();
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
command.CommandText = this.AddFilterColumnCommandText(filterColumn);
command.Connection = this.connection;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateIndex : {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
private SqlMetaData GetSqlMetadaFromType(DmColumn column)
{
var dbType = column.DbType;
var precision = column.Precision;
int maxLength = column.MaxLength;
SqlDbType sqlDbType = (SqlDbType)this.sqlMetadata.TryGetOwnerDbType(column.OriginalDbType, column.DbType, false, false, this.TableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
// TODO : Since we validate length before, is it mandatory here ?
if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.NVarChar)
{
maxLength = Convert.ToInt32(column.MaxLength) <= 0 ? 8000 : Convert.ToInt32(column.MaxLength);
maxLength = sqlDbType == SqlDbType.NVarChar ? Math.Min(maxLength, 4000) : Math.Min(maxLength, 8000);
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (column.DataType == typeof(char))
{
return(new SqlMetaData(column.ColumnName, sqlDbType, 1));
}
if (sqlDbType == SqlDbType.Char || sqlDbType == SqlDbType.NChar)
{
maxLength = Convert.ToInt32(column.MaxLength) <= 0 ? (sqlDbType == SqlDbType.NChar ? 4000 : 8000) : Convert.ToInt32(column.MaxLength);
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
if (sqlDbType == SqlDbType.Binary || sqlDbType == SqlDbType.VarBinary)
{
maxLength = Convert.ToInt32(column.MaxLength) <= 0 ? 8000 : Convert.ToInt32(column.MaxLength);
return(new SqlMetaData(column.ColumnName, sqlDbType, maxLength));
}
return(new SqlMetaData(column.ColumnName, sqlDbType));
}
public string CreateTableCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE {trackingName.FullQuotedString} (");
// Adding the primary key
foreach (DmColumn pkColumn in this.tableDescription.PrimaryKey.Columns)
{
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`").FullQuotedString;
var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.DbType, false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var unQuotedColumnType = new ObjectNameParser(columnTypeString, "`", "`").FullUnquotedString;
var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.DbType, false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var columnType = $"{unQuotedColumnType} {columnPrecisionString}";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} NOT NULL, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"`create_scope_id` VARCHAR(36) NULL, ");
stringBuilder.AppendLine($"`update_scope_id` VARCHAR(36) NULL, ");
stringBuilder.AppendLine($"`create_timestamp` BIGINT NULL, ");
stringBuilder.AppendLine($"`update_timestamp` BIGINT NULL, ");
stringBuilder.AppendLine($"`timestamp` BIGINT NULL, ");
stringBuilder.AppendLine($"`sync_row_is_tombstone` BIT NOT NULL default 0 , ");
stringBuilder.AppendLine($"`last_change_datetime` DATETIME NULL, ");
if (this.Filters != null && this.Filters.Count > 0)
{
foreach (var filter in this.Filters)
{
var columnFilter = this.tableDescription.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}");
}
var isPk = this.tableDescription.PrimaryKey.Columns.Any(dm => this.tableDescription.IsEqual(dm.ColumnName, filter.ColumnName));
if (isPk)
{
continue;
}
var quotedColumnName = new ObjectNameParser(columnFilter.ColumnName, "`", "`").FullQuotedString;
var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(columnFilter.OriginalDbType, columnFilter.DbType, false, false, columnFilter.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var unQuotedColumnType = new ObjectNameParser(columnTypeString, "`", "`").FullUnquotedString;
var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(columnFilter.OriginalDbType, columnFilter.DbType, false, false, columnFilter.MaxLength, columnFilter.Precision, columnFilter.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var columnType = $"{unQuotedColumnType} {columnPrecisionString}";
var nullableColumn = columnFilter.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, ");
}
}
stringBuilder.Append(" PRIMARY KEY (");
for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
{
DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "`", "`").QuotedObjectName;
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append("))");
return(stringBuilder.ToString());
}
public string ScriptPopulateNewFilterColumnFromBaseTable(DmColumn filterColumn)
{
throw new NotImplementedException();
}
public override bool ValidateIsReadonly(DmColumn columnDefinition)
{
return(columnDefinition.OriginalTypeName.ToLowerInvariant() == "timestamp");
}
