예제 #1
0
        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));
            }
        }
예제 #2
0
    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) !);
    }
예제 #3
0
    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[]
예제 #4
0
        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));
            }
        }
예제 #5
0
        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));
        }
예제 #6
0
    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 $$;"));
    }
예제 #7
0
        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));
        }
예제 #8
0
 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;
 }
예제 #9
0
        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);
        }
예제 #10
0
        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));
            }
        }
예제 #11
0
 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);
             }
         }
     }
 }
예제 #12
0
        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);
        }
예제 #13
0
    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();
        }
    }
예제 #14
0
        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);
        }
예제 #15
0
        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);
        }
예제 #16
0
        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);
        }
예제 #17
0
        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);
        }
예제 #18
0
        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);
            }
        }
예제 #19
0
 public ColumnTableScript(ColumnTable columnTable, SqlPreCommandSimple updateScript)
 {
     ColumnTable  = columnTable;
     UpdateScript = updateScript;
 }
예제 #20
0
        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));
        }
예제 #21
0
        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));
        }