private List <SqlObjectName> LoadDependencies(DatabaseConfig config, SqlObjectName viewName)
{
List <SqlObjectName> result = new List <SqlObjectName>();
config.DatabaseConnectionString.WithReader(
$@"SELECT DISTINCT referenced_schema_name, referenced_entity_name, obj.type
FROM
sys.dm_sql_referenced_entities('{viewName}', 'OBJECT') e
LEFT JOIN
sys.objects obj
ON
obj.object_id = referenced_id",
reader =>
{
while (reader.Read())
{
var objectName = new SqlObjectName(reader[1] as string, reader[0] as string);
if (((string)reader[2]).Trim() == "V")
{
result.AddRange(LoadDependencies(config, objectName));
}
else if (result.Contains(objectName) == false)
{
result.Add(objectName);
}
}
});
return(result);
}
public RowSchema(string connectionString, SqlObjectName tableName, string[] primaryKeyColumns)
{
_columns = GetTableColumns(connectionString, tableName);
PrimaryKeyColumns = (primaryKeyColumns ?? connectionString.GetPrimaryKeyColumns(tableName)).ToDictionary(c => c, c => ColumnNamesLookup[c]);
RowEqualityComparer = new RowComparer();
RowKeyEqualityComparer = new RowKeyComparer();
}
private static string BuildFQN(ISqlBuilder sqlBuilder, SqlObjectName name)
{
// TODO: linq2db fix required
// currently we miss FQN-based mapping support at least for for stored procedures and non-table functions
// and they require raw SQL name instead
return(sqlBuilder.BuildObjectName(new (), name, ConvertType.NameToProcedure).ToString());
}
private IEnumerable <(string, string)> BuildReplacements(SqlObjectName cacheName, SqlObjectName viewName, string[] allTargetCollumns, string[] keyColumns, string[] notNullColumns)
{
yield return("cacheName", cacheName);
yield return("viewName", viewName);
yield return
(
"allTargetCollumns",
String.Join
(
", ",
allTargetCollumns.Select(c => $"[{c}]")
)
);
yield return("sourceAlias", "s");
yield return("targetAlias", "t");
yield return("keyColumnsMatchCondition", BuildKeyColumnsMatchCondition(keyColumns));
yield return("firstNotNullPrimaryKeyColumn", $"[{keyColumns.Intersect(notNullColumns).First()}]");
yield return("valuesUpdate", BuildValuesUpdate(allTargetCollumns, keyColumns));
yield return("notMatchedValuesWithNullCheck", NotMatchedValuesWithNullCheck(allTargetCollumns, keyColumns, notNullColumns));
}
internal SqlTable(
int id,
string?expression,
string alias,
SqlObjectName tableName,
Type objectType,
SequenceNameAttribute[]?sequenceAttributes,
IEnumerable <SqlField> fields,
SqlTableType sqlTableType,
ISqlExpression[]?tableArguments,
TableOptions tableOptions,
string?tableID)
: this(objectType, id, tableName)
{
Expression = expression;
Alias = alias;
SequenceAttributes = sequenceAttributes;
ID = tableID;
AddRange(fields);
SqlTableType = sqlTableType;
TableArguments = tableArguments;
TableOptions = tableOptions;
}
/// <summary>
/// Preprocess database object name according to current settings.
/// </summary>
/// <param name="originalName">Original (full) database object name.</param>
/// <param name="defaultSchemas">Collection of default schemas.</param>
/// <returns>New database object name for use in data model metadata and non-default schema flag.</returns>
private (SqlObjectName name, bool isNonDefaultSchema) ProcessObjectName(SqlObjectName originalName, ISet <string> defaultSchemas)
{
var isNonDefaultSchema = originalName.Schema != null && !defaultSchemas.Contains(originalName.Schema);
var result = originalName;
// remove default schema from name if disabled
if (!_options.DataModel.GenerateDefaultSchema && !isNonDefaultSchema && originalName.Schema != null)
{
result = result with {
Schema = null
}
}
;
// remove database name if disabled
if (!_options.DataModel.IncludeDatabaseName)
{
result = result with {
Database = null
}
}
;
// currently we don't load schema data from linked servers, so there is no need to process server name
return(result, isNonDefaultSchema);
}
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
var schemaName = name.Schema;
if (schemaName == null && tableOptions.IsTemporaryOptionSet())
{
schemaName = "SESSION";
}
// "db..table" syntax not supported
if (name.Database != null && schemaName == null)
{
throw new LinqToDBException("DB2 requires schema name if database name provided.");
}
if (name.Database != null)
{
(escape ? Convert(sb, name.Database, ConvertType.NameToDatabase) : sb.Append(name.Database))
.Append('.');
if (schemaName == null)
{
sb.Append('.');
}
}
if (schemaName != null)
{
(escape ? Convert(sb, schemaName, ConvertType.NameToSchema) : sb.Append(schemaName))
.Append('.');
}
return(escape ? Convert(sb, name.Name, objectType) : sb.Append(name.Name));
}
public static void CreateQueue(this SqlTransaction tran, SqlObjectName queueName)
{
var script = LoadScript("CreateQueue");
script = script.Replace("{queueName}", queueName);
tran.ExecuteNonQuery(script);
}
public TableFunctionModel(
SqlObjectName name,
MethodModel method,
TableFunctionMetadata metadata,
string methodInfoFieldName)
: base(name, method)
{
Metadata = metadata;
MethodInfoFieldName = methodInfoFieldName;
}
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
if (name.Database != null)
{
(escape ? Convert(sb, name.Database, ConvertType.NameToDatabase) : sb.Append(name.Database))
.Append('.');
}
return(escape ? Convert(sb, name.Name, objectType) : sb.Append(name.Name));
}
public MergeSelectViewChanges(SqlObjectName cacheName, SqlObjectName viewName, string[] allTargetCollumns, string[] keyColumns, string[] notNullColumns)
{
string sc = _script;
foreach (var replacement in BuildReplacements(cacheName, viewName, allTargetCollumns, keyColumns, notNullColumns))
{
sc = sc.Replace($"{{{replacement.Item1}}}", replacement.Item2);
}
_replacedScript = sc;
}
public void Build(ISqExtensionBuilder builder)
{
var tableExpr = builder.Arguments[0].EvaluateExpression();
var tableType = ((MethodInfo)builder.Member).GetGenericArguments()[0];
var helperType = typeof(TableHelper <>).MakeGenericType(tableType);
var tableHelper = (TableHelper)Activator.CreateInstance(helperType, tableExpr) !;
var qualified = builder.Arguments.Length <= 1 ? TableQualification.Full : builder.GetValue <TableQualification>(1);
var isExpression = builder.Member.Name == "TableExpr";
if (isExpression)
{
if (qualified == TableQualification.None)
{
builder.ResultExpression = new SqlExpression(typeof(string), tableHelper.TableName, Precedence.Primary);
}
else
{
var tableName = new SqlObjectName(
tableHelper.TableName,
Server: (qualified & TableQualification.ServerName) != 0 ? tableHelper.ServerName : null,
Database: (qualified & TableQualification.DatabaseName) != 0 ? tableHelper.DatabaseName : null,
Schema: (qualified & TableQualification.SchemaName) != 0 ? tableHelper.SchemaName : null);
var table = new SqlTable(tableType)
{
TableName = tableName,
TableOptions = (qualified & TableQualification.TableOptions) != 0 ? tableHelper.TableOptions : TableOptions.NotSet,
};
builder.ResultExpression = table;
}
}
else
{
var name = tableHelper.TableName;
if (qualified != TableQualification.None)
{
var sb = new StringBuilder();
builder.DataContext.CreateSqlProvider().BuildObjectName(
sb,
new SqlObjectName(
name,
Server: (qualified & TableQualification.ServerName) != 0 ? tableHelper.ServerName : null,
Database: (qualified & TableQualification.DatabaseName) != 0 ? tableHelper.DatabaseName : null,
Schema: (qualified & TableQualification.SchemaName) != 0 ? tableHelper.SchemaName : null),
ConvertType.NameToQueryTable,
true,
(qualified & TableQualification.TableOptions) != 0 ? tableHelper.TableOptions : TableOptions.NotSet);
name = sb.ToString();
}
builder.ResultExpression = new SqlValue(name);
}
}
public static void CreateMemoryViewCache(this string connectionString, SqlObjectName viewName, SqlObjectName cacheName, params string[] primaryColumns)
{
var script = LoadScript("CreateMemoryViewCache");
script = script.Replace("{viewName}", viewName);
script = script.Replace("{cacheName}", cacheName);
script = script.Replace("{cacheName.Schema}", cacheName.Schema);
script = script.Replace("{cacheName.Name}", cacheName.Name);
script = script.Replace("{primaryColumnList}", String.Join(", ", primaryColumns.Select(c => $"[{c}]")));
connectionString.WithConnection(con => { con.ExecuteNonQuery(script); });
}
public static void MergeViewChanges(this string sqlConnection, SqlObjectName cacheName, SqlObjectName viewName, string[] keyColumns, string[] valueColumns)
{
var script = LoadScript("MergeViewChanges");
script = script.Replace("{cacheName}", cacheName);
script = script.Replace("{viewName}", viewName);
script = script.Replace("{targetAlias}", "t");
script = script.Replace("{sourceAlias}", "s");
script = script.Replace
(
"{keyColumnsMatchCondition}",
String.Join(" AND ", keyColumns.Select(c => $"s.[{c}] = t.[{c}]"))
);
if (valueColumns.Length > 0)
{
script = script.Replace("{whenMatchedClause}", @" WHEN MATCHED AND ({valueColumnsChangedCondition})
THEN
UPDATE SET {valueColumnUpdates}" );
script = script.Replace
(
"{valueColumnsChangedCondition}",
String.Join(" OR ", valueColumns.Select(c => $"s.[{c}] <> t.[{c}]"))
);
script = script.Replace
(
"{valueColumnUpdates}",
String.Join(", ", valueColumns.Select(c => $"t.[{c}] = s.[{c}]"))
);
}
else
{
script = script.Replace("{whenMatchedClause}", String.Empty);
}
script = script.Replace
(
"{allTargetCollumns}",
String.Join(", ", keyColumns.Union(valueColumns).Select(c => $"[{c}]"))
);
script = script.Replace
(
"{allSourceColumns}",
String.Join(", ", keyColumns.Union(valueColumns).Select(c => $"s.[{c}]"))
);
sqlConnection.WithConnection(con =>
{
using (var command = con.CreateCommand())
{
command.CommandTimeout = 0;
command.CommandText = script;
command.ExecuteNonQuery();
}
});
}
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
if (name.Database != null && IsTemporary(name.Name, tableOptions))
{
name = name with {
Database = null
}
}
;
name = name with {
Name = GetTablePhysicalName(name.Name, tableOptions)
};
return(base.BuildObjectName(sb, name, objectType, escape, tableOptions));
}
// https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1652.htm
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
if (name.Server != null && name.Database == null)
{
throw new LinqToDBException("You must specify database for linked server query");
}
if (name.Database != null)
{
if (escape)
{
Convert(sb, name.Database, ConvertType.NameToDatabase);
}
else
{
sb.Append(name.Database);
}
}
if (name.Server != null)
{
sb.Append('@');
if (escape)
{
Convert(sb, name.Server, ConvertType.NameToServer);
}
else
{
sb.Append(name.Server);
}
}
if (name.Database != null)
{
sb.Append(':');
}
if (name.Schema != null)
{
(escape ? Convert(sb, name.Schema, ConvertType.NameToSchema) : sb.Append(name.Schema))
.Append('.');
}
return(escape ? Convert(sb, name.Name, objectType) : sb.Append(name.Name));
}
public QueryView(Database db, string query, string[] primaryKeyColumns)
{
_db = db;
Query = query;
PrimaryKeyColumns = primaryKeyColumns;
ViewName = string.Format(db.Config.QueryViewNameTemplate, Guid.ToString().Replace('-', '_'));
db.Config.DatabaseConnectionString.WithConnection
(
con =>
{
using (var command = con.CreateCommand())
{
command.CommandText = $"CREATE VIEW {ViewName} AS {Environment.NewLine}{query}";
command.ExecuteNonQuery();
}
}
);
}
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
var databaseName = name.Database;
// remove database name, which could be inherited from non-temporary table mapping
// except explicit use of tempdb, needed in some cases at least for sql server 2014
if ((name.Name.StartsWith("#") || tableOptions.IsTemporaryOptionSet()) && databaseName != "tempdb")
{
databaseName = "tempdb";
}
if (name.Server != null && (databaseName == null || name.Schema == null))
{
// all components required for linked-server syntax by SQL server
throw new LinqToDBException("You must specify both schema and database names explicitly for linked server query");
}
if (name.Server != null)
{
(escape ? Convert(sb, name.Server, ConvertType.NameToServer) : sb.Append(name.Server))
.Append('.');
}
if (databaseName != null)
{
(escape ? Convert(sb, databaseName, ConvertType.NameToDatabase) : sb.Append(databaseName))
.Append('.');
}
if (name.Schema != null)
{
(escape ? Convert(sb, name.Schema, ConvertType.NameToSchema) : sb.Append(name.Schema)).Append('.');
}
else if (databaseName != null)
{
sb.Append('.');
}
var tableName = GetTablePhysicalName(name.Name, tableOptions);
return(escape ? Convert(sb, tableName, objectType) : sb.Append(tableName));
}
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
var schemaName = tableOptions.HasIsTemporary() ? null : name.Schema;
// "db..table" syntax not supported and postgresql doesn't support database name, if it is not current database
// so we can ignore database name to avoid error from server
if (name.Database != null && schemaName != null)
{
(escape ? Convert(sb, name.Database, ConvertType.NameToDatabase) : sb.Append(name.Database))
.Append('.');
}
if (schemaName != null)
{
(escape ? Convert(sb, schemaName, ConvertType.NameToSchema) : sb.Append(schemaName))
.Append('.');
}
return(escape ? Convert(sb, name.Name, objectType) : sb.Append(name.Name));
}
public static IEnumerable <string> ReceiveMessages(this SqlTransaction tran, SqlObjectName queueName, TimeSpan timeoutMs)
{
var script = LoadScript("ReceiveMessages");
script = script.Replace("{queueName}", queueName);
script = script.Replace("{timeoutMs}", timeoutMs.TotalMilliseconds.ToString(CultureInfo.InvariantCulture));
using (var command = tran.Connection.CreateCommand())
{
command.CommandText = script;
command.Transaction = tran;
command.CommandTimeout = 0;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
}
}
}
public override ISqlExpression?GetIdentityExpression(SqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
{
var sequenceName = new SqlObjectName(attr.SequenceName, Server: table.TableName.Server, Database: table.TableName.Database, Schema: attr.Schema ?? table.TableName.Schema);
var sb = new StringBuilder();
sb.Append("nextval(");
MappingSchema.ConvertToSqlValue(sb, null, BuildObjectName(new StringBuilder(), sequenceName, ConvertType.SequenceName, true, TableOptions.NotSet).ToString());
sb.Append(')');
return(new SqlExpression(sb.ToString(), Precedence.Primary));
}
}
return(base.GetIdentityExpression(table));
}
private (Dictionary <int, string>, Dictionary <string, int>, Dictionary <int, Type>) GetTableColumns(string connectionString, SqlObjectName objectName)
{
string query = $"SELECT TOP(0) * FROM {objectName}";
var names = new Dictionary <int, string>();
var namesLookup = new Dictionary <string, int>();
var types = new Dictionary <int, Type>();
connectionString.WithReader(query, reader =>
{
for (int i = 0; i < reader.FieldCount; i++)
{
names.Add(i, reader.GetName(i));
namesLookup.Add(reader.GetName(i), i);
types.Add(i, reader.GetFieldType(i));
}
});
return(names, namesLookup, types);
}
private static void CreateTrigger(this SqlTransaction tran, string triggerResource, SqlObjectName triggerName, SqlObjectName tableName, string dialogHandle, SqlSchemalessObjectName messageType, bool withNative)
{
var script = LoadScript(triggerResource);
script = script.Replace("{triggerName}", triggerName);
script = script.Replace("{tableName.Schema}", tableName.Schema);
script = script.Replace("{tableName.Name}", tableName.Name);
script = script.Replace("{tableName}", tableName);
script = script.Replace("{dialogHandle}", dialogHandle);
script = script.Replace("{messageType}", messageType);
if (withNative)
{
script = script.Replace("{with}", "WITH NATIVE_COMPILATION, SCHEMABINDING");
script = script.Replace("{as}", "BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )");
}
else
{
script = script.Replace("{with}", String.Empty);
script = script.Replace("{as}", String.Empty);
}
tran.ExecuteNonQuery(script);
}
public RowSchema(string connectionString, SqlObjectName tableName) : this(connectionString, tableName, null)
{
}
public static void DropQueue(this SqlTransaction tran, SqlObjectName queueName)
{
tran.ExecuteNonQuery($"DROP QUEUE {queueName}");
}
public static IEnumerable <string> ReceiveMessages(this SqlTransaction tran, SqlObjectName queueName)
{
return(ReceiveMessages(tran, queueName, TimeSpan.Zero));
}
public static IEnumerable <string> ReceiveMessages(this string connectionString, SqlObjectName queueName, TimeSpan timeoutMs)
{
IEnumerable <string> result = Enumerable.Empty <string>();
connectionString.WithConnection(con =>
{
var transaction = con.BeginTransaction();
try
{
result = ReceiveMessages(transaction, queueName, timeoutMs).ToArray();
transaction.Commit();
}
finally
{
if (transaction.Connection == null || transaction.Connection.State != ConnectionState.Closed)
{
transaction.Dispose();
}
}
});
return(result);
}
public AggregateFunctionModel(SqlObjectName name, MethodModel method, FunctionMetadata metadata, IType returnType)
: base(name, method, metadata)
{
ReturnType = returnType;
}
public override StringBuilder BuildObjectName(StringBuilder sb, SqlObjectName name, ConvertType objectType, bool escape, TableOptions tableOptions)
{
return(escape ? Convert(sb, name.Name, objectType) : sb.Append(name.Name));
}
protected TableFunctionModelBase(SqlObjectName name, MethodModel method)
: base(name, method)
{
Name = name;
}
