protected virtual int Update(DisconnectedMachineEntity machine, Table table, IDisconnectedStrategy strategy, DatabaseName newDatabaseName)
{
using (Transaction tr = new Transaction())
{
SqlPreCommandSimple command = UpdateTableScript(machine, table, newDatabaseName);
int result = Executor.ExecuteNonQuery(command);
foreach (var rt in table.TablesMList())
{
SqlPreCommandSimple delete = DeleteUpdatedRelationalTableScript(machine, table, rt, newDatabaseName);
Executor.ExecuteNonQuery(delete);
using (DisableIdentityIfNecessary(rt))
{
SqlPreCommandSimple insert = InsertUpdatedRelationalTableScript(machine, table, rt, newDatabaseName);
Executor.ExecuteNonQuery(insert);
}
}
return(tr.Commit(result));
}
}
public SqlPreCommand DeleteSqlSync <T>(T entity, Expression <Func <T, bool> >?where, string?comment = null)
where T : Entity
{
if (typeof(T) != Type && where != null)
{
throw new InvalidOperationException("Invalid table");
}
var declaration = where != null?DeclarePrimaryKeyVariable(entity, where) : null;
var variableOrId = entity.Id.VariableName ?? entity.Id.Object;
var isPostgres = Schema.Current.Settings.IsPostgres;
var pre = OnPreDeleteSqlSync(entity);
var collections = (from tml in this.TablesMList()
select new SqlPreCommandSimple("DELETE FROM {0} WHERE {1} = {2}; --{3}"
.FormatWith(tml.Name, tml.BackReference.Name.SqlEscape(isPostgres), variableOrId, comment ?? entity.ToString()))).Combine(Spacing.Simple);
var main = new SqlPreCommandSimple("DELETE FROM {0} WHERE {1} = {2}; --{3}"
.FormatWith(Name, this.PrimaryKey.Name.SqlEscape(isPostgres), variableOrId, comment ?? entity.ToString()));
if (isPostgres && declaration != null)
{
return(PostgresDoBlock(entity.Id.VariableName !, declaration, SqlPreCommand.Combine(Spacing.Simple, pre, collections, main) !));
}
return(SqlPreCommand.Combine(Spacing.Simple, declaration, pre, collections, main) !);
}
public SqlPreCommand CreateTableSql(ITable t, ObjectName?tableName = null, bool avoidSystemVersioning = false)
{
var primaryKeyConstraint = t.PrimaryKey == null || t.SystemVersioned != null && tableName != null && t.SystemVersioned.TableName.Equals(tableName) ? null :
isPostgres ?
"CONSTRAINT {0} PRIMARY KEY ({1})".FormatWith(PrimaryKeyIndex.GetPrimaryKeyName(t.Name).SqlEscape(isPostgres), t.PrimaryKey.Name.SqlEscape(isPostgres)) :
"CONSTRAINT {0} PRIMARY KEY CLUSTERED ({1} ASC)".FormatWith(PrimaryKeyIndex.GetPrimaryKeyName(t.Name).SqlEscape(isPostgres), t.PrimaryKey.Name.SqlEscape(isPostgres));
var systemPeriod = t.SystemVersioned == null || IsPostgres || avoidSystemVersioning ? null : Period(t.SystemVersioned);
var columns = t.Columns.Values.Select(c => this.ColumnLine(c, GetDefaultConstaint(t, c), isChange: false, forHistoryTable: avoidSystemVersioning))
.And(primaryKeyConstraint)
.And(systemPeriod)
.NotNull()
.ToString(",\r\n");
var systemVersioning = t.SystemVersioned == null || avoidSystemVersioning || IsPostgres ? null :
$"\r\nWITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {t.SystemVersioned.TableName.OnDatabase(null)}))";
var result = new SqlPreCommandSimple($"CREATE {(IsPostgres && t.Name.IsTemporal ? "TEMPORARY " : "")}TABLE {tableName ?? t.Name}(\r\n{columns}\r\n)" + systemVersioning + ";");
if (!(IsPostgres && t.SystemVersioned != null))
{
return(result);
}
return(new[]
protected virtual int Insert(DisconnectedMachineEntity machine, Table table, IDisconnectedStrategy strategy, SqlConnector newDatabase)
{
DatabaseName newDatabaseName = new DatabaseName(null, newDatabase.DatabaseName());
var count = (int)CountNewItems(table, newDatabaseName).ExecuteScalar();
if (count == 0)
{
return(0);
}
using (Transaction tr = new Transaction())
{
int result;
using (DisableIdentityIfNecessary(table))
{
SqlPreCommandSimple sql = InsertTableScript(table, newDatabaseName);
result = Executor.ExecuteNonQuery(sql);
}
foreach (var rt in table.TablesMList())
{
using (DisableIdentityIfNecessary(rt))
{
SqlPreCommandSimple rsql = InsertRelationalTableScript(table, newDatabaseName, rt);
Executor.ExecuteNonQuery(rsql);
}
}
return(tr.Commit(result));
}
}
static internal SqlPreCommandSimple Format(Expression expression)
{
QueryFormatter qf = new QueryFormatter();
qf.Visit(expression);
var parameters = qf.parameterExpressions.Values.Select(pi => pi.Parameter).ToList();
var sqlpc = new SqlPreCommandSimple(qf.sb.ToString(), parameters);
return(PostFormatter.Value == null ? sqlpc : PostFormatter.Value.Invoke(sqlpc));
}
private SqlPreCommandSimple PostgresDoBlock(string variableName, SqlPreCommandSimple declaration, SqlPreCommand block)
{
return(new SqlPreCommandSimple(@$ "DO $$
DECLARE
{declaration.PlainSql().Indent(4)}
BEGIN
IF {variableName} IS NULL THEN
RAISE EXCEPTION 'Not found';
END IF;
{block.PlainSql().Indent(4)}
END $$;"));
}
public SqlPreCommand DeleteSqlSync(Entity ident, string comment = null)
{
var pre = OnPreDeleteSqlSync(ident);
var collections = (from tml in this.TablesMList()
select new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}"
.FormatWith(tml.Name, tml.BackReference.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()))).Combine(Spacing.Simple);
var main = new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}"
.FormatWith(Name, this.PrimaryKey.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()));
return(SqlPreCommand.Combine(Spacing.Simple, pre, collections, main));
}
public TranslateResult(
List <IChildProjection> eagerProjections,
List <IChildProjection> lazyChildProjections,
SqlPreCommandSimple mainCommand,
Expression <Func <IProjectionRow, T> > projectorExpression,
UniqueFunction?unique)
{
EagerProjections = eagerProjections;
LazyChildProjections = lazyChildProjections;
MainCommand = mainCommand;
ProjectorExpression = projectorExpression;
Unique = unique;
}
private SqlPreCommand DeclarePrimaryKeyVariable <T>(T entity, Expression <Func <T, bool> > where) where T : Entity
{
var query = DbQueryProvider.Single.GetMainSqlCommand(Database.Query <T>().Where(where).Select(a => a.Id).Expression);
string variableName = SqlParameterBuilder.GetParameterName(this.Name.Name + "Id_" + (parameterIndex++));
entity.SetId(new Entities.PrimaryKey(entity.id.Value.Object, variableName));
string queryString = query.PlainSql().Lines().ToString(" ");
var result = new SqlPreCommandSimple($"DECLARE {variableName} {SqlBuilder.GetColumnType(this.PrimaryKey)}; SET {variableName} = COALESCE(({queryString}), 1 / 0)");
return(result);
}
static string Equals(Field field, object value, bool equals, bool isPostgres)
{
if (value == null)
{
return(IsNull(field, equals, isPostgres));
}
else
{
if (field is IColumn)
{
return(((IColumn)field).Name.SqlEscape(isPostgres) +
(equals ? " = " : " <> ") + SqlPreCommandSimple.Encode(value));
}
throw new NotSupportedException("Impossible to compare {0} to {1}".FormatWith(field, value));
}
}
public static void ExecuteDataReaderOptionalDependency(this Connector connector, SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action <FieldReader> forEach)
{
if (WithSqlDependency)
{
((SqlServerConnector)connector).ExecuteDataReaderDependency(preCommand, change, StartSqlDependencyAndEnableBrocker, forEach, CommandType.Text);
}
else
{
using (var p = preCommand.UnsafeExecuteDataReader())
{
FieldReader reader = new FieldReader(p.Reader);
while (p.Reader.Read())
{
forEach(reader);
}
}
}
}
protected virtual SqlPreCommandSimple DeleteUpdatedRelationalTableScript(DisconnectedMachineEntity machine, Table table, TableMList rt, DatabaseName newDatabaseName)
{
ParameterBuilder pb = Connector.Current.ParameterBuilder;
var delete = new SqlPreCommandSimple(@"DELETE {0}
FROM {0}
INNER JOIN {1} as [table] ON {0}.{2} = [table].{3}".FormatWith(
rt.Name,
table.Name.OnDatabase(newDatabaseName),
rt.BackReference.Name.SqlEscape(),
table.PrimaryKey.Name.SqlEscape()) +
GetUpdateWhere(table),
new List <DbParameter> {
pb.CreateParameter("@machineId", machine.Id.Object, machine.Id.Object.GetType())
});
return(delete);
}
protected static void Log(SqlPreCommandSimple pcs)
{
var log = logger.Value;
if (log != null)
{
log.WriteLine(pcs.Sql);
if (pcs.Parameters != null)
{
log.WriteLine(pcs.Parameters
.ToString(p => "{0} {1}: {2}".FormatWith(
p.ParameterName,
Connector.Current.GetSqlDbType(p),
p.Value?.Let(v => v.ToString())), "\r\n"));
}
log.WriteLine();
}
}
protected virtual SqlPreCommandSimple UpdateTableScript(DisconnectedMachineEntity machine, Table table, DatabaseName newDatabaseName)
{
ParameterBuilder pb = Connector.Current.ParameterBuilder;
var command = new SqlPreCommandSimple(@"UPDATE {0} SET
{2}
FROM {0}
INNER JOIN {1} as [table] ON {0}.{3} = [table].{3}".FormatWith(
table.Name,
table.Name.OnDatabase(newDatabaseName),
table.Columns.Values.Where(c => !c.PrimaryKey).ToString(c => " {0} = [table].{0}".FormatWith(c.Name.SqlEscape()), ",\r\n"),
table.PrimaryKey.Name.SqlEscape())
+ GetUpdateWhere(table),
new List <DbParameter> {
pb.CreateParameter("@machineId", machine.Id.Object, machine.Id.Object.GetType())
});
return(command);
}
protected virtual SqlPreCommandSimple InsertUpdatedRelationalTableScript(DisconnectedMachineEntity machine, Table table, TableMList rt, DatabaseName newDatabaseName)
{
ParameterBuilder pb = Connector.Current.ParameterBuilder;
var insert = new SqlPreCommandSimple(@"INSERT INTO {0} ({1})
SELECT {2}
FROM {3} as [relationalTable]
INNER JOIN {4} as [table] ON [relationalTable].{5} = [table].{6}".FormatWith(
rt.Name,
rt.Columns.Values.ToString(c => c.Name.SqlEscape(), ", "),
rt.Columns.Values.ToString(c => "[relationalTable]." + c.Name.SqlEscape(), ", "),
rt.Name.OnDatabase(newDatabaseName),
table.Name.OnDatabase(newDatabaseName),
rt.BackReference.Name.SqlEscape(),
table.PrimaryKey.Name.SqlEscape()) + GetUpdateWhere(table), new List <DbParameter> {
pb.CreateParameter("@machineId", machine.Id.Object, machine.Id.Object.GetType())
});
return(insert);
}
protected virtual SqlPreCommandSimple InsertRelationalTableScript(Table table, DatabaseName newDatabaseName, TableMList rt)
{
ParameterBuilder pb = Connector.Current.ParameterBuilder;
var created = table.Mixins[typeof(DisconnectedCreatedMixin)].Columns().Single();
string command = @"INSERT INTO {0} ({1})
SELECT {2}
FROM {3} as [relationalTable]
JOIN {4} [table] on [relationalTable].{5} = [table].{6}
WHERE [table].{7} = 1".FormatWith(
rt.Name,
rt.Columns.Values.ToString(c => c.Name.SqlEscape(), ", "),
rt.Columns.Values.ToString(c => "[relationalTable]." + c.Name.SqlEscape(), ", "),
rt.Name.OnDatabase(newDatabaseName),
table.Name.OnDatabase(newDatabaseName),
rt.BackReference.Name.SqlEscape(),
table.PrimaryKey.Name.SqlEscape(),
created.Name.SqlEscape());
var sql = new SqlPreCommandSimple(command);
return(sql);
}
public CachedTableMList(ICacheLogicController controller, TableMList table, AliasGenerator aliasGenerator, string lastPartialJoin, string remainingJoins)
: base(controller)
{
this.table = table;
CachedTableConstructor ctr = this.Constructor = new CachedTableConstructor(this, aliasGenerator);
//Query
using (ObjectName.OverrideOptions(new ObjectNameOptions {
AvoidDatabaseName = true
}))
{
string select = "SELECT\r\n{0}\r\nFROM {1} {2}\r\n".FormatWith(
ctr.table.Columns.Values.ToString(c => ctr.currentAlias + "." + c.Name.SqlEscape(), ",\r\n"),
table.Name.ToString(),
ctr.currentAlias.ToString());
ctr.remainingJoins = lastPartialJoin + ctr.currentAlias + "." + table.BackReference.Name.SqlEscape() + "\r\n" + remainingJoins;
query = new SqlPreCommandSimple(select);
}
//Reader
{
rowReader = ctr.GetRowReader();
}
//Completer
{
List <Expression> instructions = new List <Expression>
{
Expression.Assign(ctr.origin, Expression.Convert(CachedTableConstructor.originObject, ctr.tupleType)),
Expression.Assign(result, ctr.MaterializeField(table.Field))
};
var ci = typeof(MList <T> .RowIdElement).GetConstructor(new [] { typeof(T), typeof(PrimaryKey), typeof(int?) });
var order = table.Order == null?Expression.Constant(null, typeof(int?)) :
ctr.GetTupleProperty(table.Order).Nullify();
instructions.Add(Expression.New(ci, result, CachedTableConstructor.NewPrimaryKey(ctr.GetTupleProperty(table.PrimaryKey)), order));
var block = Expression.Block(typeof(MList <T> .RowIdElement), new[] { ctr.origin, result }, instructions);
activatorExpression = Expression.Lambda <Func <object, IRetriever, MList <T> .RowIdElement> >(block, CachedTableConstructor.originObject, CachedTableConstructor.retriever);
activator = activatorExpression.Compile();
parentIdGetter = ctr.GetPrimaryKeyGetter(table.BackReference);
rowIdGetter = ctr.GetPrimaryKeyGetter(table.PrimaryKey);
}
relationalRows = new ResetLazy <Dictionary <PrimaryKey, Dictionary <PrimaryKey, object> > >(() =>
{
CacheLogic.AssertSqlDependencyStarted();
var connector = (SqlConnector)Connector.Current;
var subConnector = connector.ForDatabase(table.Name.Schema?.Database);
Dictionary <PrimaryKey, Dictionary <PrimaryKey, object> > result = new Dictionary <PrimaryKey, Dictionary <PrimaryKey, object> >();
using (MeasureLoad())
using (Connector.Override(subConnector))
using (Transaction tr = Transaction.ForceNew(IsolationLevel.ReadCommitted))
{
if (CacheLogic.LogWriter != null)
{
CacheLogic.LogWriter.WriteLine("Load {0}".FormatWith(GetType().TypeName()));
}
((SqlConnector)Connector.Current).ExecuteDataReaderOptionalDependency(query, OnChange, fr =>
{
object obj = rowReader(fr);
PrimaryKey parentId = parentIdGetter(obj);
var dic = result.TryGetC(parentId);
if (dic == null)
{
result[parentId] = dic = new Dictionary <PrimaryKey, object>();
}
dic[rowIdGetter(obj)] = obj;
});
tr.Commit();
}
return(result);
}, mode: LazyThreadSafetyMode.ExecutionAndPublication);
}
public CachedTable(ICacheLogicController controller, AliasGenerator aliasGenerator, string lastPartialJoin, string remainingJoins)
: base(controller)
{
this.table = Schema.Current.Table(typeof(T));
CachedTableConstructor ctr = this.Constructor = new CachedTableConstructor(this, aliasGenerator);
//Query
using (ObjectName.OverrideOptions(new ObjectNameOptions {
AvoidDatabaseName = true
}))
{
string select = "SELECT\r\n{0}\r\nFROM {1} {2}\r\n".FormatWith(
Table.Columns.Values.ToString(c => ctr.currentAlias + "." + c.Name.SqlEscape(), ",\r\n"),
table.Name.ToString(),
ctr.currentAlias.ToString());
ctr.remainingJoins = lastPartialJoin == null ? null : lastPartialJoin + ctr.currentAlias + ".Id\r\n" + remainingJoins;
if (ctr.remainingJoins != null)
{
select += ctr.remainingJoins;
}
query = new SqlPreCommandSimple(select);
}
//Reader
{
rowReader = ctr.GetRowReader();
}
//Completer
{
ParameterExpression me = Expression.Parameter(typeof(T), "me");
var block = ctr.MaterializeEntity(me, table);
completerExpression = Expression.Lambda <Action <object, IRetriever, T> >(block, CachedTableConstructor.originObject, CachedTableConstructor.retriever, me);
completer = completerExpression.Compile();
idGetter = ctr.GetPrimaryKeyGetter((IColumn)table.PrimaryKey);
}
rows = new ResetLazy <Dictionary <PrimaryKey, object> >(() =>
{
CacheLogic.AssertSqlDependencyStarted();
var connector = (SqlConnector)Connector.Current;
Table table = connector.Schema.Table(typeof(T));
var subConnector = connector.ForDatabase(table.Name.Schema?.Database);
Dictionary <PrimaryKey, object> result = new Dictionary <PrimaryKey, object>();
using (MeasureLoad())
using (Connector.Override(subConnector))
using (Transaction tr = Transaction.ForceNew(IsolationLevel.ReadCommitted))
{
if (CacheLogic.LogWriter != null)
{
CacheLogic.LogWriter.WriteLine("Load {0}".FormatWith(GetType().TypeName()));
}
((SqlConnector)Connector.Current).ExecuteDataReaderOptionalDependency(query, OnChange, fr =>
{
object obj = rowReader(fr);
result[idGetter(obj)] = obj; //Could be repeated joins
});
tr.Commit();
}
return(result);
}, mode: LazyThreadSafetyMode.ExecutionAndPublication);
if (!CacheLogic.WithSqlDependency && lastPartialJoin.HasText()) //Is semi
{
semiCachedController = new SemiCachedController <T>(this);
}
}
public ColumnTableScript(ColumnTable columnTable, SqlPreCommandSimple updateScript)
{
ColumnTable = columnTable;
UpdateScript = updateScript;
}
static IChildProjection LazyChild <K, V>(Expression projector, Scope scope, LookupToken token, SqlPreCommandSimple command)
where K : notnull
{
var proj = ProjectionBuilder.Build <KeyValuePair <K, MList <V> .RowIdElement> >(projector, scope);
return(new LazyChildProjection <K, V>(token, command, proj));
}
static IChildProjection EagerChild <K, V>(Expression projector, Scope scope, LookupToken token, SqlPreCommandSimple command)
{
var proj = ProjectionBuilder.Build <KeyValuePair <K, V> >(projector, scope);
return(new EagerChildProjection <K, V>(token, command, proj));
}
