private List<DataSetInstance> DoLoadRows(ICdlReader reader, NameWithSchema targetTable)
{
var loaded = new List<DataSetInstance>();
var ts = reader.Structure;
var cls = GetClass(targetTable);
var cols = cls.Structure.Columns.ToList();
int[] map = new int[cols.Count];
for (int i = 0; i < cols.Count; i++)
{
map[i] = ts.Columns.IndexOfIf(
c => System.String.Compare(c.Name, cols[i].Name, System.StringComparison.OrdinalIgnoreCase) == 0);
}
while (reader.Read())
{
if (_loadingStopped) return loaded;
object[] newValues = new object[map.Length];
for (int i = 0; i < map.Length; i++)
{
if (map[i] < 0) continue;
reader.ReadValue(map[i]);
newValues[i] = reader.GetValue();
}
var added = cls.AddRecord(newValues);
if (added != null) loaded.Add(added);
}
return loaded;
}
public override void SetDummyTable(NameWithSchema name)
{
var table = new TableInfo(null);
table.FullName = name;
table.Checks.Add(this);
}
public override void SetDummyTable(NameWithSchema name)
{
var table = new TableInfo(null);
table.FullName = name;
table.PrimaryKey = this;
}
public static int Compare(NameWithSchema a, NameWithSchema b)
{
int res = 0;
if (a.Schema == null && b.Schema != null)
{
return(-1);
}
if (a.Schema != null && b.Schema == null)
{
return(1);
}
if (a.Schema != null && b.Schema != null)
{
res = String.Compare(a.Schema, b.Schema, true);
}
if (res != 0)
{
return(res);
}
if (a.Name == null && b.Name != null)
{
return(-1);
}
if (a.Name != null && b.Name == null)
{
return(1);
}
if (a.Name != null && b.Name != null)
{
res = String.Compare(a.Name, b.Name, true);
}
return(res);
}
public ColumnInfo FindOriginalColumn(DatabaseInfo db, NameWithSchema baseName)
{
string name = IsAliased ? BaseColumnName : Name;
if (name == null)
{
return(null);
}
// determine original table name
TableInfo table = null;
if (!String.IsNullOrEmpty(BaseTableName))
{
table = db.FindTableLike(String.IsNullOrWhiteSpace(BaseSchemaName) ? null : BaseSchemaName, BaseTableName);
}
else
{
if (baseName != null)
{
table = db.FindTableLike(baseName.Schema, baseName.Name);
}
}
if (table == null)
{
return(null);
}
var tableCol = table.FindColumn(name);
return(tableCol);
}
public void AllowIdentityInsert(NameWithSchema tableName, bool allow)
{
Commands.Add(new DmlfAllowIdentityInsert
{
TableName = tableName,
AllowIdentityInsert = allow,
});
}
public bool MatchTableName(NameWithSchema tableName)
{
if (tableName == null) return false;
if (TableName == null) return false;
if (TableName.Schema == null) return System.String.Compare(TableName.Name ?? "", tableName.Name ?? "", System.StringComparison.OrdinalIgnoreCase) == 0;
return String.Compare(TableName.Schema ?? "", tableName.Schema ?? "", System.StringComparison.OrdinalIgnoreCase) == 0
&& System.String.Compare(TableName.Name ?? "", tableName.Name ?? "", System.StringComparison.OrdinalIgnoreCase) == 0;
}
public TableInfo AddTable(NameWithSchema name)
{
var res = new TableInfo(this);
res.FullName = name;
Tables.Add(res);
return(res);
}
public TargetEntitySqlModel(DataSyncSqlModel dataSyncSqlModel, Target dbsh, IShellContext context)
{
this._dataSyncSqlModel = dataSyncSqlModel;
this._dbsh = dbsh;
TargetTable = new NameWithSchema(context.Replace(dbsh.TableSchema), context.Replace(dbsh.TableName));
string findSchema = dbsh.TableSchema;
if (findSchema != null && findSchema.StartsWith(NameWithSchema.NoQuotePrefix)) findSchema = null;
Structure = dataSyncSqlModel.TargetStructure.FindTableLike(findSchema, TargetTable.Name);
SqlAlias = _dbsh.Alias ?? "dst_" + _dataSyncSqlModel.Entities.Count;
foreach (var col in dbsh.Columns)
{
var targetCol = new TargetNoRefColumnSqlModel(col, FindColumnInfo(col.Name));
TargetColumns.Add(targetCol);
foreach (string alias in ExtractColumnSources(col))
{
SourceColumnSqlModel source = null;
if (dataSyncSqlModel.SourceGraphModel == null)
{
// flat sync
if (!String.IsNullOrEmpty(dbsh.PrimarySource))
{
var tableSource = DataSync.FlatSources.FirstOrDefault(x => x.Match(Dbsh.PrimarySource));
if (tableSource != null)
{
source = tableSource.Columns.FirstOrDefault(x => x.Alias == alias);
}
}
}
else
{
source = dataSyncSqlModel.SourceGraphModel[alias];
//targetCol.Sources.Add(source);
}
RequiredSourceColumns.Add(source);
if (col.IsKey) KeySourceColumns.Add(source);
}
}
if (!String.IsNullOrEmpty(_dbsh.Connection))
{
var ctxConn = new NormalizedDatabaseConnectionInfo(new DatabaseConnectionInfoHolder { ProviderString = context.GetDefaultConnection() });
var tableConn = new NormalizedDatabaseConnectionInfo(new DatabaseConnectionInfoHolder { ProviderString = context.Replace(_dbsh.Connection), LinkedInfo = _dbsh.LinkedInfo });
if (ctxConn != tableConn)
{
if (ctxConn.ServerConnectionString == tableConn.ServerConnectionString)
{
TargetLinkedInfo = tableConn.GetLinkedInfo();
}
else
{
throw new IncorrectRdsDefinitionException($"DBSH-00000 RDS target must be reachable by database or linked server: ({TargetTable})");
}
}
}
}
public void DisableConstraint(NameWithSchema tableName, string constraintName, bool disable)
{
Commands.Add(new DmlfDisableConstraint
{
TableName = tableName,
ConstraintName = constraintName,
Disable = disable
});
}
public static string QuoteFullName(this ISqlDialect dialect, NameWithSchema name)
{
if (name.Schema != null)
{
if (name.Schema.ToUpper() == "INFORMATION_SCHEMA") return String.Format("{0}.{1}", name.Schema, name.Name);
return String.Format("{0}.{1}", dialect.QuoteIdentifier(name.Schema), dialect.QuoteIdentifier(name.Name));
}
return dialect.QuoteIdentifier(name.Name);
}
public int CompareTo(object obj)
{
var name2 = obj as NameWithSchema;
if (name2 != null)
{
return(NameWithSchema.Compare(this, name2));
}
return(0);
}
public TableInfo FindOrCreateTable(NameWithSchema name)
{
var res = FindTable(name);
if (res == null)
{
res = AddTable(name);
}
return(res);
}
private DmlfSource DoAddOrFindRelation(DmlfSource baseSource, NameWithSchema baseTable, StructuredIdentifier relationJoined, StructuredIdentifier relationToJoin, DatabaseInfo db, DmlfJoinType joinType)
{
if (relationToJoin.IsEmpty) return baseSource;
string relName = relationToJoin.First;
string alias = String.Format("_REF{0}_{1}", relationJoined.NameItems.Select(x => "_" + x).CreateDelimitedText(""), relName);
var source = FindSourceByAlias(alias);
if (source == null)
{
var baseTableInfo = db.FindTable(baseTable);
var fk = baseTableInfo.ForeignKeys.FirstOrDefault(x => System.String.Compare(x.ConstraintName, relName, StringComparison.OrdinalIgnoreCase) == 0);
if (fk == null)
{
var column = baseTableInfo.FindColumn(relName);
if (column != null) fk = column.GetForeignKeys().FirstOrDefault(x => x.Columns.Count == 1);
}
if (fk == null) return null;
source = new DmlfSource
{
TableOrView = fk.RefTableFullName,
Alias = alias,
};
var relation = new DmlfRelation
{
Reference = source,
JoinType = joinType,
};
for (int i = 0; i < fk.Columns.Count; i++)
{
relation.Conditions.Add(new DmlfEqualCondition
{
LeftExpr = new DmlfColumnRefExpression
{
Column = new DmlfColumnRef
{
ColumnName = fk.Columns[0].RefColumnName,
Source = baseSource,
}
},
RightExpr = new DmlfColumnRefExpression
{
Column = new DmlfColumnRef
{
ColumnName = fk.RefColumns[0].RefColumnName,
Source = source,
}
},
});
Relations.Add(relation);
}
}
if (relationToJoin.IsEmpty) return source;
return DoAddOrFindRelation(source, source.TableOrView, relationJoined/relationToJoin.First, relationToJoin.WithoutFirst, db, joinType);
}
public DataSetClass GetClass(NameWithSchema name)
{
//name = name.ToLower();
if (Classes.ContainsKey(name)) return Classes[name];
var tbl = _targetDatabase.FindTableLike(name.Schema, name.Name);
if (tbl == null) throw new Exception("DBSH-00120 Unknown target table in data set:" + name);
if (Classes.ContainsKey(tbl.FullName)) return Classes[tbl.FullName];
var cls = new DataSetClass(this, tbl);
Classes[tbl.FullName] = cls;
cls.InitializeClass();
return cls;
}
private void Where(NameWithSchema table, string[] cols, object[] vals)
{
Put(" ^where ");
bool was = false;
for (int i = 0; i < cols.Length; i++)
{
if (was) Put(" ^and ");
if (vals[i].IsNullOrDbNull()) Put("%i ^is ^null", cols[i]);
else Put("%i=%v", cols[i], vals[i]);
was = true;
}
}
public static int Compare(NameWithSchema a, NameWithSchema b)
{
int res = 0;
if (a.Schema == null && b.Schema != null) return -1;
if (a.Schema != null && b.Schema == null) return 1;
if (a.Schema != null && b.Schema != null) res = String.Compare(a.Schema, b.Schema, true);
if (res != 0) return res;
if (a.Name == null && b.Name != null) return -1;
if (a.Name != null && b.Name == null) return 1;
if (a.Name != null && b.Name != null) res = String.Compare(a.Name, b.Name, true);
return res;
}
private T FindObjectLike <T>(IEnumerable <T> objs, string name)
where T : NamedObjectInfo
{
if (name != null && name.Contains("."))
{
var fullName = NameWithSchema.Parse(name);
if (fullName == null)
{
return(null);
}
return(FindObjectLike(objs, fullName.Schema, fullName.Name));
}
return(objs.FirstOrDefault(t => String.Compare(t.Name, name, true) == 0));
}
public ObjectPath GetChild(NameWithSchema name)
{
ObjectPath res = new ObjectPath(DbName);
if (ObjectName == null)
{
res.ObjectName = name;
return res;
}
else
{
res.ObjectName = ObjectName;
res.SubNames.AddRange(SubNames);
res.SubNames.Add(name.Name);
return res;
}
}
public ObjectPath GetChild(NameWithSchema name)
{
ObjectPath res = new ObjectPath(DbName);
if (ObjectName == null)
{
res.ObjectName = name;
return(res);
}
else
{
res.ObjectName = ObjectName;
res.SubNames.AddRange(SubNames);
res.SubNames.Add(name.Name);
return(res);
}
}
public ColumnInfo FindOriginalColumn(DatabaseInfo db, NameWithSchema baseName)
{
string name = IsAliased ? BaseColumnName : Name;
if (name == null) return null;
// determine original table name
TableInfo table = null;
if (!String.IsNullOrEmpty(BaseTableName))
{
table = db.FindTableLike(String.IsNullOrWhiteSpace(BaseSchemaName) ? null : BaseSchemaName, BaseTableName);
}
else
{
if (baseName != null) table = db.FindTableLike(baseName.Schema, baseName.Name);
}
if (table == null) return null;
var tableCol = table.FindColumn(name);
return tableCol;
}
public TableWriter(IShellContext context, IConnectionProvider connection, NameWithSchema name, TableInfo inputRowFormat, CopyTableTargetOptions options, TableInfo destinationTableOverride = null, LinkedDatabaseInfo linkedInfo = null, DataFormatSettings sourceDataFormat = null)
{
_connectionProvider = connection;
_linkedInfo = linkedInfo;
_name = name;
_inputRowFormat = inputRowFormat;
_queue = new CdlDataQueue(inputRowFormat);
_context = context;
_inserter = connection.Factory.CreateBulkInserter();
_inserter.SourceDataFormat = sourceDataFormat;
_connection = _connectionProvider.Connect();
_inserter.Connection = _connection;
_inserter.Factory = connection.Factory;
_inserter.LinkedInfo = _linkedInfo;
var db = context.GetDatabaseStructure(connection.ProviderString);
_inserter.DestinationTable = destinationTableOverride ?? db.FindTableLike(_name.Schema, _name.Name);
_inserter.CopyOptions = options;
_inserter.Log += _inserter_Log;
_thread = new Thread(Run);
_thread.Start();
}
public ViewInfo FindView(NameWithSchema fullName)
{
return(_views.FirstOrDefault(v => v.FullName == fullName));
}
public TableInfo FindTable(NameWithSchema fullName)
{
return(Tables.FirstOrDefault(t => t.FullName == fullName));
}
public ViewInfo GetView(NameWithSchema table)
{
return(_views.FirstOrDefault(v => v.FullName == table));
}
public void MaterializeIfNeeded()
{
if (IsExternal) return;
if (!_dbsh.Materialize) return;
_materializedName = new NameWithSchema("#" + SqlAlias.Replace(".", "_"));
_materializeSelect = new DmlfSelect();
_materializeSelect.SingleFrom.Source = QuerySource;
_materializeSelect.SelectIntoTable = _materializedName;
_materializeSelect.SelectAll = true;
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = _materializedName,
};
}
public ObjectPath(string dbname, NameWithSchema objname, params string[] subnames)
{
DbName = dbname;
ObjectName = objname;
SubNames.AddRange(subnames);
}
public abstract void SetDummyTable(NameWithSchema name);
public TriggerInfo FindTrigger(NameWithSchema fullName)
{
return(Triggers.FirstOrDefault(x => x.FullName == fullName));
}
public void DefineConditionalInsert(NameWithSchema table, string[] lookupColumns)
{
CheckUnprepared("ConditionalInsert");
var cls = GetClass(table);
cls.ConditionalInsertFields = lookupColumns;
cls.ConditionalInsertFieldIndexes = lookupColumns.Select(x => cls.ColumnOrdinals.Get(x, -1)).ToArray();
if (cls.ConditionalInsertFieldIndexes.Any(x => x < 0))
{
throw new Exception($"DBSH-00000 Some of conditional insert columns in class {cls.TableName} not found: {lookupColumns.CreateDelimitedText(",")}");
}
}
public void DefineLookup(NameWithSchema table, string[] lookupColumns)
{
CheckUnprepared("Lookup");
var cls = GetClass(table);
cls.LookupFields = lookupColumns;
cls.LookupFieldIndexes = lookupColumns.Select(x => cls.ColumnOrdinals.Get(x, -1)).ToArray();
}
public void KeepKey(NameWithSchema table)
{
CheckUnprepared("KeepKey");
var cls = GetClass(table);
cls.KeepKey = true;
}
protected string QuoteFullName(NameWithSchema name)
{
return QuoteFullName(m_dialect, m_props, null, name);
}
public override void PutLogMessage(
ISqlDumper dmp,
string operationExpr,
string targetEntityExpr,
string messageExpr,
string durationExpr,
string procedureExpr,
string rowsExpr,
IShellContext context)
{
if (String.IsNullOrEmpty(TableName)) return;
var fullName = new NameWithSchema(context.Replace(TableSchema), context.Replace(TableName));
var insert = new DmlfInsert();
insert.InsertTarget = fullName;
if (!String.IsNullOrEmpty(OperationColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = OperationColumn,
Expr = new DmlfSqlValueExpression { Value = operationExpr },
});
}
if (!String.IsNullOrEmpty(MessageColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = MessageColumn,
Expr = new DmlfSqlValueExpression { Value = messageExpr },
});
}
if (!String.IsNullOrEmpty(DurationColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = DurationColumn,
Expr = new DmlfSqlValueExpression { Value = durationExpr },
});
}
if (!String.IsNullOrEmpty(TargetEntityColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = TargetEntityColumn,
Expr = new DmlfSqlValueExpression { Value = targetEntityExpr },
});
}
if (!String.IsNullOrEmpty(MessageDateColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = MessageDateColumn,
Expr = new DmlfSqlValueExpression { Value = "GETDATE()" },
});
}
if (!String.IsNullOrEmpty(ImportDateColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = ImportDateColumn,
Expr = SqlScriptCompiler.ImportDateTimeExpression,
});
}
if (!String.IsNullOrEmpty(ProcedureColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = ProcedureColumn,
Expr = new DmlfSqlValueExpression { Value = procedureExpr },
});
}
if (!String.IsNullOrEmpty(RowsColumn))
{
insert.Columns.Add(new DmlfUpdateField
{
TargetColumn = RowsColumn,
Expr = new DmlfSqlValueExpression { Value = rowsExpr },
});
}
if (insert.Columns.Any())
{
insert.GenSql(dmp);
dmp.Put("&n");
}
}
public StoredProcedureInfo FindStoredProcedure(NameWithSchema fullName)
{
return(StoredProcedures.FirstOrDefault(x => x.FullName == fullName));
}
public FunctionInfo FindFunction(NameWithSchema fullName)
{
return(Functions.FirstOrDefault(x => x.FullName == fullName));
}
public void ChangeColumn(NameWithSchema table, string column, string formula)
{
CheckUnprepared("ChangeColumn");
var cls = GetClass(table);
var formulaObj = Formulas[formula];
cls.ChangedColumns[column] = formulaObj;
}
public void DropReferencesTo(NameWithSchema fullName)
{
ForeignKeys.RemoveIf(cnt => cnt.RefTable.FullName == fullName);
}
public void PutProcedureHeader(NameWithSchema name, bool useTransaction, string createKeyword, List<ParameterModel> pars)
{
Put($"%k ^procedure %f ({ImportDateTimeVariableName} ^datetime = ^null", createKeyword, name);
if (useTransaction) Put(", @useTransaction bit = 1");
foreach(var par in pars)
{
Put($", @{par.Name} {par.DataType} = ^null");
}
Put($") ^as &n");
Put("^begin&>&n");
Put($"^if ({ImportDateTimeVariableName} ^is ^null) ^set {ImportDateTimeVariableName} = ^getdate();&n");
foreach (var par in pars)
{
if (String.IsNullOrEmpty(par.DefaultValue)) continue;
Put($"^if (@{par.Name} ^is ^null) ^set @{par.Name} = {par.DefaultValue}; &n");
}
}
public void CreateOrAlterProcedure(NameWithSchema name, string sqlCore)
{
Put("^declare @procedureText ^nvarchar(^max);&n");
Put("^set @procedureText = %v;&n", sqlCore);
if (name.Schema != null)
{
Put("^if (^exists (^select * from sys.objects inner join sys.schemas on schemas.schema_id = objects.schema_id where type='P' and schemas.name=%v and objects.name=%v))&n", name.Schema, name.Name);
}
else
{
Put("^if (^exists (^select * from sys.objects inner join sys.schemas on schemas.schema_id = objects.schema_id where type='P' and objects.name=%v))&n", name.Name);
}
Put("begin&nset @procedureText = 'alter' + @procedureText&nend&nelse&nbegin&nset @procedureText = 'create' + @procedureText&nend&n");
Put("exec sp_executesql @procedureText;&n");
}
public TableInfo GetTable(NameWithSchema table)
{
return(_tables.FirstOrDefault(t => t.FullName == table));
}
public void AddRowsByPk(NameWithSchema table, params string[] pks)
{
var cls = GetClass(table);
foreach (string pk in pks)
{
cls.RequiredPks.Add(pk);
}
}
public void AddRows(NameWithSchema table, string condition)
{
CheckUnprepared("AddRows");
var cls = GetClass(table);
cls.AddRowsRequests.Add(condition);
}
public void InitializeQuerySource(ITabularDataSource dataSource, IShellContext context, string sourceTableVariable, string sourceQueryVariable)
{
if (!_dbsh.ForceExternalSource)
{
// try to create non-external source
var tableOrView = dataSource as DbShell.Core.Utility.TableOrView;
if (!String.IsNullOrEmpty(sourceTableVariable))
{
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = new NameWithSchema($"###({sourceTableVariable})###"),
};
TableName = new NameWithSchema(sourceTableVariable);
return;
}
if (!String.IsNullOrEmpty(sourceQueryVariable))
{
QuerySource = new DmlfSource
{
Alias = SqlAlias,
SubQueryString = $"###({sourceQueryVariable})###",
};
return;
}
if (tableOrView != null)
{
bool canUseTable = true;
LinkedDatabaseInfo linked = null;
var ctxConn = new NormalizedDatabaseConnectionInfo(new DatabaseConnectionInfoHolder { ProviderString = context.GetDefaultConnection() });
var tableConn = tableOrView.GetNormalizedConnectionInfo(context);
if (ctxConn != tableConn)
{
if (ctxConn.ServerConnectionString == tableConn.ServerConnectionString)
{
linked = tableConn.GetLinkedInfo();
}
else
{
canUseTable = false;
}
}
if (canUseTable)
{
TableName = tableOrView.GetFullName(context);
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = TableName,
LinkedInfo = linked,
};
return;
}
}
var query = dataSource as DbShell.Core.Query;
if (query != null && query.GetProviderString(context) == context.GetDefaultConnection())
{
string sql = context.Replace(query.Text);
QuerySource = new DmlfSource
{
Alias = SqlAlias,
SubQueryString = sql,
};
return;
}
}
IsExternal = true;
_externalDataName = new NameWithSchema(null, $"##{SqlAlias}_{new Random().Next(10000, 100000)}");
QuerySource = new DmlfSource
{
Alias = SqlAlias,
TableOrView = _externalDataName,
};
_dataSync.AddExternalSource(this);
}
private void DoAddRows(DbConnection conn, NameWithSchema table, string condition)
{
using (var cmd = conn.CreateCommand())
{
var sb = new StringBuilder();
sb.Append("SELECT ");
var cls = GetClass(table);
sb.Append(cls.Columns.Select(x => "tmain.[" + x + "]").CreateDelimitedText(", "));
sb.Append($" FROM [{table.Schema}].[{table.Name}] tmain");
if (!String.IsNullOrEmpty(condition))
{
var rewr = new SqlRewriter(table, this);
string outcond = rewr.Rewrite(condition);
rewr.WriteJoins(sb);
sb.Append(" WHERE " + outcond);
}
cmd.CommandText = sb.ToString();
try
{
using (ICdlReader reader = _dda.AdaptReader(cmd.ExecuteReader()))
{
DoLoadRows(reader, table);
}
}
catch (Exception err)
{
throw new Exception(String.Format("Error loading, table={0}, condition={1}, error={2}", table, condition, err.Message));
}
}
}
private static string QuoteFullName(ISqlDialect dialect, SqlFormatProperties props, SqlFormatterState state, NameWithSchema name)
{
bool omitSchema = !props.UseSchema || name.Schema == null;
if (state != null && state.ForceFullName)
{
state.ForceFullName = false;
omitSchema = false;
}
if (omitSchema)
{
return QuoteIdentifier(dialect, props, name.Name);
}
return QuoteIdentifier(dialect, props, name.Schema) + "." + QuoteIdentifier(dialect, props, name.Name);
}
public void LoadMissing(NameWithSchema table)
{
CheckUnprepared("LoadMissing");
var cls = GetClass(table);
cls.LoadMissingInstances = true;
}
public override void SetDummyTable(NameWithSchema name)
{
var table = new TableInfo(null);
table.FullName = name;
table.Columns.Add(this);
}
public void LoadFromReader(ITabularDataSource source, NameWithSchema targetTable, IShellContext context)
{
using (var reader = source.CreateReader(context))
{
DoLoadRows(reader, targetTable);
}
}
