示例#1
0
        public static string DbObjectName(this Type type, bool squareBraces = false)
        {
            var obj = DbObject.FromType(type);

            obj.SquareBraces = squareBraces;
            return(obj.ToString());
        }
示例#2
0
        public override IEnumerable <string> SqlCommands(IDbConnection connection)
        {
            foreach (var cmd in base.SqlCommands(connection))
            {
                yield return(cmd);
            }

            DbObject obj = DbObject.FromType(_propertyInfo.DeclaringType);

            obj.SquareBraces = false;
            yield return($"EXEC sp_rename '{obj}.{_attr.OldName}', '{_propertyInfo.SqlColumnName()}', 'COLUMN'");

            ForeignKeyAttribute fkAttr = _propertyInfo.GetAttribute <ForeignKeyAttribute>();

            if (fkAttr != null)
            {
                yield return($"ALTER TABLE [{obj.Schema}].[{obj.Name}] DROP CONSTRAINT [FK_{obj.ConstraintName()}_{_attr.OldName}]");

                if (fkAttr.CreateIndex)
                {
                    yield return($"DROP INDEX [IX_{DbObject.ConstraintName(_propertyInfo.DeclaringType)}_{_attr.OldName}] ON [{obj.Schema}].[{obj.Name}]");
                }

                CreateForeignKey fk = new CreateForeignKey(_propertyInfo);
                foreach (var cmd in fk.SqlCommands(connection))
                {
                    yield return(cmd);
                }
            }
        }
示例#3
0
        public override IEnumerable <ChangeHistory <TKey> > QueryChangeHistory(IDbConnection connection, TKey id, int timeZoneOffset = 0)
        {
            DbObject obj       = DbObject.FromType(typeof(TRecord));
            string   tableName = $"{obj.Schema}_{obj.Name}";

            var results = connection.Query <ChangeHistoryRecord <TKey> >(
                $@"SELECT * FROM [{_changesSchema}].[{tableName}] WHERE [RecordId]=@id ORDER BY [DateTime] DESC", new { id = id });

            return(results.GroupBy(item => new
            {
                RecordId = item.RecordId,
                Version = item.Version
            }).Select(ch =>
            {
                return new ChangeHistory <TKey>()
                {
                    RecordId = ch.Key.RecordId,
                    DateTime = ch.First().DateTime.AddHours(timeZoneOffset),
                    Version = ch.Key.Version,
                    Properties = ch.Select(chr => new PropertyChange()
                    {
                        PropertyName = chr.ColumnName,
                        OldValue = chr.OldValue,
                        NewValue = chr.NewValue
                    })
                };
            }));
        }
示例#4
0
        private static string RenameInfo(PropertyInfo propertyInfo)
        {
            RenameFromAttribute attr = propertyInfo.GetAttribute <RenameFromAttribute>();
            DbObject            obj  = DbObject.FromType(propertyInfo.DeclaringType);

            obj.SquareBraces = false;
            return($"{obj}.{attr.OldName} -> {propertyInfo.SqlColumnName()}");
        }
示例#5
0
        public static string RenameDescription(Type modelType)
        {
            RenameFromAttribute attr = modelType.GetAttribute <RenameFromAttribute>();
            DbObject            obj  = DbObject.FromType(modelType);

            obj.SquareBraces = false;
            return($"{attr.OldName} -> {obj}");
        }
示例#6
0
        public string TableName(Type tableType = null)
        {
            Type t = (tableType == null) ? typeof(TRecord) : tableType;

            string result = DbObject.FromType(t).ToString();

            //if (_squareBraces) result = string.Join(".", result.Split('.').Select(s => $"[{s}]"));

            return(result);
        }
示例#7
0
        public override IEnumerable <string> ValidationErrors(IDbConnection connection)
        {
            var tbl = DbObject.FromType(_propertyInfo.DeclaringType);

            tbl.SquareBraces = false;
            if (!connection.ColumnExists(tbl.Schema, tbl.Name, _attr.OldName))
            {
                yield return($"Can't rename from {tbl}.{_attr.OldName} -- column doesn't exist.");
            }

            if (_propertyInfo.SqlColumnName().Equals(_attr.OldName))
            {
                yield return($"Can't rename column to the same name.");
            }
        }
示例#8
0
        protected override object OnGetChangesPropertyValue(PropertyInfo propertyInfo, object record, IDbConnection connection)
        {
            object result = base.OnGetChangesPropertyValue(propertyInfo, record, connection);

            ForeignKeyAttribute   fk;
            DereferenceExpression dr;

            if (result != null && propertyInfo.HasAttribute(out fk) && fk.PrimaryTableType.HasAttribute(out dr))
            {
                DbObject obj = DbObject.FromType(fk.PrimaryTableType);
                result = connection.QueryFirst <string>(
                    $@"SELECT {dr.Expression} FROM [{obj.Schema}].[{obj.Name}] 
					WHERE [{fk.PrimaryTableType.IdentityColumnName()}]=@id"                    , new { id = result });
            }

            return(result);
        }
示例#9
0
        public override IEnumerable <string> SqlCommands(IDbConnection connection)
        {
            foreach (var cmd in base.SqlCommands(connection))
            {
                yield return(cmd);
            }

            DbObject newTable = DbObject.FromType(_modelType);

            CreateTable ct = new CreateTable(_modelType);

            if (!connection.TableExists(newTable.Schema, newTable.Name))
            {
                foreach (var cmd in ct.SqlCommands(connection))
                {
                    yield return(cmd);
                }
            }

            DbObject oldTable = DbObject.Parse(_attr.OldName);

            if (connection.TableExists(oldTable.Schema, oldTable.Name))
            {
                if (!connection.IsTableEmpty(oldTable.Schema, oldTable.Name))
                {
                    yield return($"SET IDENTITY_INSERT [{newTable.Schema}].[{newTable.Name}] ON");

                    string columnNames = string.Join(", ", ct.ColumnProperties().Select(pi => $"[{pi.SqlColumnName()}]").Concat(new string[] { $"[{SqlDb<int>.IdentityColumnName}]" }));
                    yield return($"INSERT INTO [{newTable.Schema}].[{newTable.Name}] ({columnNames}) SELECT {columnNames} FROM [{oldTable.Schema}].[{oldTable.Name}]");

                    yield return($"SET IDENTITY_INSERT [{newTable.Schema}].[{newTable.Name}] OFF");
                }

                DbObject.SetObjectId(connection, oldTable);
                foreach (var cmd in connection.GetFKDropStatements(oldTable.ObjectId))
                {
                    yield return(cmd);
                }

                yield return($"DROP TABLE [{oldTable.Schema}].[{oldTable.Name}]");
            }
        }
示例#10
0
        public override IEnumerable <string> ValidationErrors(IDbConnection connection)
        {
            var oldTable = DbObject.Parse(_attr.OldName);

            oldTable.SquareBraces = false;
            if (!connection.TableExists(oldTable.Schema, oldTable.Name))
            {
                yield return($"Can't rename from {oldTable} -- table doesn't exist.");
            }

            var newTable = DbObject.FromType(_modelType);

            if (oldTable.Equals(newTable))
            {
                yield return($"Can't rename table to the same name.");
            }

            if (_modelType.GetProperties().Any(pi => pi.HasAttribute <RenameFromAttribute>()))
            {
                yield return($"Can't rename columns while containing table is being renamed. Please do these changes one after the other.");
            }
        }
        public override IEnumerable <string> SqlCommands(IDbConnection connection)
        {
            foreach (var cmd in base.SqlCommands(connection))
            {
                yield return(cmd);
            }

            yield return($"ALTER TABLE {DbObject.SqlServerName(_pi.DeclaringType)} DROP CONSTRAINT [{_pi.ForeignKeyName()}]");

            ForeignKeyAttribute fk = _pi.GetForeignKeyAttribute();
            var obj = DbObject.FromType(_pi.DeclaringType);

            if (!fk.CreateIndex)
            {
                yield return($"DROP INDEX [{_pi.IndexName()}] ON {obj}");
            }

            foreach (var cmd in base.SqlCommands(connection))
            {
                yield return(cmd);
            }
        }
示例#12
0
        public override IEnumerable <string> SqlCommands(IDbConnection connection)
        {
            foreach (var cmd in base.SqlCommands(connection))
            {
                yield return(cmd);
            }

            ForeignKeyAttribute fk = _pi.GetForeignKeyAttribute();
            string cascadeDelete   = (fk.CascadeDelete) ? " ON DELETE CASCADE" : string.Empty;

            yield return
                ($"ALTER TABLE {DbObject.SqlServerName(_pi.DeclaringType)} ADD CONSTRAINT [{_pi.ForeignKeyName()}] FOREIGN KEY (\r\n" +
                 $"\t[{_pi.SqlColumnName()}]\r\n" +
                 $") REFERENCES {DbObject.SqlServerName(fk.PrimaryTableType)} (\r\n" +
                 $"\t[{fk.PrimaryTableType.IdentityColumnName()}]\r\n" +
                 ")" + cascadeDelete);

            if (fk.CreateIndex && !connection.Exists("[sys].[indexes] WHERE [name]=@name", new { name = _pi.IndexName() }))
            {
                var obj = DbObject.FromType(_pi.DeclaringType);
                yield return($"CREATE INDEX [{_pi.IndexName()}] ON {obj} ([{_pi.SqlColumnName()}])");
            }
        }
示例#13
0
 public AddColumn(PropertyInfo propertyInfo) : base(MergeObjectType.Column, MergeActionType.Create, $"Add column {propertyInfo.DeclaringType.Name}.{propertyInfo.Name}", nameof(AddColumn))
 {
     _propertyInfo = propertyInfo;
     _object       = DbObject.FromType(_propertyInfo.ReflectedType);
 }
示例#14
0
        protected override void OnCaptureChanges(IDbConnection connection, TKey id, IEnumerable <PropertyChange> changes, string userName = null)
        {
            SqlConnection cn = connection as SqlConnection;

            if (!cn.Exists("[sys].[schemas] WHERE [name]=@name", new { name = _changesSchema }))
            {
                cn.Execute($"CREATE SCHEMA [{_changesSchema}]");
            }

            var typeMap = new Dictionary <Type, string>()
            {
                { typeof(int), "int" },
                { typeof(long), "bigint" },
                { typeof(Guid), "uniqueidentifier" }
            };

            DbObject obj       = DbObject.FromType(typeof(TRecord));
            string   tableName = $"{obj.Schema}_{obj.Name}";

            if (!cn.Exists("[sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = _changesSchema, name = $"{tableName}_Versions" }))
            {
                cn.Execute($@"CREATE TABLE [{_changesSchema}].[{tableName}_Versions] (
					[RecordId] {typeMap[typeof(TKey)]} NOT NULL,
					[NextVersion] int NOT NULL DEFAULT (1),
					CONSTRAINT [PK_{_changesSchema}_{tableName}_Versions] PRIMARY KEY ([RecordId])
				)"                );
            }

            if (!cn.Exists("[sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = _changesSchema, name = tableName }))
            {
                cn.Execute($@"CREATE TABLE [{_changesSchema}].[{tableName}] (
					[RecordId] {typeMap[typeof(TKey)]} NOT NULL,
					[Version] int NOT NULL,
					[UserName] nvarchar(256) NOT NULL,
					[ColumnName] nvarchar(100) NOT NULL,
					[OldValue] nvarchar(max) NULL,
					[NewValue] nvarchar(max) NULL,
					[DateTime] datetime NOT NULL DEFAULT (getutcdate()),
					CONSTRAINT [PK_{_changesSchema}_{obj.Name}] PRIMARY KEY ([RecordId], [Version], [ColumnName])
				)"                );
            }

            int version = 0;

            while (version == 0)
            {
                version = cn.QueryFirstOrDefault <int>($"SELECT [NextVersion] FROM [{_changesSchema}].[{tableName}_Versions] WHERE [RecordId]=@id", new { id = id });
                if (version == 0)
                {
                    cn.Execute($"INSERT INTO [{_changesSchema}].[{tableName}_Versions] ([RecordId]) VALUES (@id)", new { id = id });
                }
            }
            cn.Execute($"UPDATE [{_changesSchema}].[{tableName}_Versions] SET [NextVersion]=[NextVersion]+1 WHERE [RecordId]=@id", new { id = id });

            foreach (var change in changes)
            {
                cn.Execute(
                    $@"INSERT INTO [{_changesSchema}].[{tableName}] ([RecordId], [Version], [UserName], [ColumnName], [OldValue], [NewValue])
					VALUES (@id, @version, @userName, @columnName, @oldValue, @newValue)"                    ,
                    new
                {
                    id         = id, version = version,
                    columnName = change.PropertyName,
                    userName   = userName ?? "<unknown>",
                    oldValue   = CleanMinDate(change.OldValue) ?? "<null>",
                    newValue   = CleanMinDate(change.NewValue) ?? "<null>"
                });
            }
        }