Beispiel #1
0
        public static DbObject FromType(Type modelType, IDbConnection connection)
        {
            DbObject obj = FromType(modelType);

            obj.ObjectID = connection.QueryFirstOrDefault <int>("SELECT [object_id] FROM [sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = obj.Schema, name = obj.Name });
            return(obj);
        }
Beispiel #2
0
        public override IEnumerable <string> SqlCommands()
        {
            var    obj             = DbObject.FromType(_modelType, _connection);
            var    temp            = obj.GetTemp();
            string sourceTableName = obj.ToString();
            string tempTableName   = temp.ToString();

            yield return(SelectInto(obj, tempTableName));

            DropTable drop = new DropTable(obj, _connection);

            foreach (var cmd in drop.SqlCommands())
            {
                yield return(cmd);
            }

            CreateTable create = new CreateTable(_modelType);

            foreach (var cmd in create.SqlCommands())
            {
                yield return(cmd);
            }

            RestoreTempTable rt = new RestoreTempTable(temp, _modelType, _connection,
                                                       _columns.Where(cr => !cr.PropertyInfo.HasAttribute <CalculatedAttribute>()).ToDictionary(
                                                           item => item.PropertyInfo.SqlColumnName(),
                                                           item => item.PropertyInfo.SqlDefaultExpression(forCreateTable: false)));

            foreach (var cmd in rt.SqlCommands())
            {
                yield return(cmd);
            }
        }
Beispiel #3
0
        private IEnumerable <string> CreateTableUniqueConstraints()
        {
            List <string> results = new List <string>();

            if (PrimaryKeyColumns(markedOnly: true).Any())
            {
                results.Add($"CONSTRAINT [U_{DbObject.ConstraintName(_modelType)}_Id] UNIQUE ([Id])");
            }

            results.AddRange(_modelType.GetProperties().Where(pi =>
            {
                var unique = pi.GetCustomAttribute <UniqueKeyAttribute>();
                return(unique != null);
            }).Select(pi =>
            {
                return($"CONSTRAINT [U_{DbObject.ConstraintName(_modelType)}_{pi.SqlColumnName()}] UNIQUE ([{pi.SqlColumnName()}])");
            }));

            results.AddRange(_modelType.GetCustomAttributes <UniqueKeyAttribute>().Select((u, i) =>
            {
                string constrainName = (string.IsNullOrEmpty(u.ConstraintName)) ? $"U_{DbObject.ConstraintName(_modelType)}_{i}" : u.ConstraintName;
                return($"CONSTRAINT [{constrainName}] UNIQUE ({string.Join(", ", u.ColumnNames.Select(col => $"[{col}]"))})");
            }));

            return(results);
        }
Beispiel #4
0
 public override IEnumerable <string> SqlCommands()
 {
     yield return
         ($"CREATE TABLE {DbObject.SqlServerName(_modelType)} (\r\n\t" +
          string.Join(",\r\n\t", CreateTableMembers(false)) +
          "\r\n)");
 }
Beispiel #5
0
        public static string SqlServerName(Type modelType)
        {
            DbObject obj = FromType(modelType);

            obj.SquareBraces = true;
            return(obj.ToString());
        }
Beispiel #6
0
        private IEnumerable <string> CreateTableForeignKeys()
        {
            List <string> results = new List <string>();

            results.AddRange(_modelType.GetProperties().Where(pi => pi.HasAttribute <Attributes.ForeignKeyAttribute>())
                             .Select(pi =>
            {
                var fk = pi.GetCustomAttribute <Attributes.ForeignKeyAttribute>();
                return
                ($@"CONSTRAINT [{pi.ForeignKeyName()}] FOREIGN KEY (
							[{pi.SqlColumnName()}]
						) REFERENCES {DbObject.SqlServerName(fk.PrimaryTableType)} (
							[{nameof(DataRecord<int>.Id)}]
						)"                        );
            }));

            results.AddRange(_modelType.GetCustomAttributes <Attributes.ForeignKeyAttribute>()
                             .Where(attr => HasColumnName(_modelType, attr.ColumnName))
                             .Select(fk =>
            {
                return
                ($@"CONSTRAINT [FK_{DbObject.ConstraintName(_modelType)}_{fk.ColumnName}] FOREIGN KEY (
							[{fk.ColumnName}]
						) REFERENCES {DbObject.SqlServerName(fk.PrimaryTableType)} (
							[{nameof(DataRecord<int>.Id)}]
						)"                        );
            }));

            return(results);
        }
Beispiel #7
0
            public ColumnRef(PropertyInfo pi)
            {
                PropertyInfo = pi;
                DbObject obj = DbObject.FromType(pi.ReflectedType);

                Schema     = obj.Schema;
                TableName  = obj.Name;
                ColumnName = pi.SqlColumnName();
            }
Beispiel #8
0
 public RestoreTempTable(DbObject tempTable, Type modelType, IDbConnection connection, Dictionary <string, string> addColumns = null) : base(MergeObjectType.Table, MergeActionType.Load, $"Restore {tempTable.QualifiedName()}")
 {
     _tempTable  = tempTable;
     _modelTable = DbObject.FromTempName(tempTable);
     _modelType  = modelType;
     _addColumns = addColumns;
     if (addColumns == null)
     {
         _addColumns = NullMissingTempColumns(connection);
     }
 }
Beispiel #9
0
        public static string ConstraintName(Type modelType)
        {
            DbObject obj    = FromType(modelType);
            string   result = obj.Name;

            if (!obj.Schema.Equals("dbo"))
            {
                result = obj.Schema.Substring(0, 1).ToUpper() + obj.Schema.Substring(1).ToLower() + result;
            }
            return(result);
        }
Beispiel #10
0
        public override IEnumerable <string> SqlCommands()
        {
            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);
        }
Beispiel #11
0
 internal static IEnumerable <ForeignKeyRef> GetReferencingForeignKeys(Type modelType, IEnumerable <Type> allTypes)
 {
     return(allTypes.SelectMany(t => GetForeignKeys(t).Where(pi =>
     {
         ForeignKeyAttribute fk = pi.GetForeignKeyAttribute();
         return (fk.PrimaryTableType.Equals(modelType));
     }).Select(pi =>
               new ForeignKeyRef()
     {
         ConstraintName = pi.ForeignKeyName(), ReferencingTable = DbObject.FromType(pi.DeclaringType)
     }
               )));
 }
Beispiel #12
0
        public override IEnumerable <string> SqlCommands()
        {
            Type     modelType = _modelColumn.ReflectedType;
            DbObject obj       = DbObject.FromType(modelType);

            if (_modelColumn.HasAttribute <PrimaryKeyAttribute>())
            {
                // issue #7, table must be dropped and rebuilt
            }

            yield return($"ALTER TABLE [{obj.Schema}].[{obj.Name}] ALTER COLUMN [{_modelColumn.SqlColumnName()}] {_modelColumn.SqlColumnType()}");

            // restore PK
        }
Beispiel #13
0
 private static IEnumerable <PrimaryKeyRef> GetModelPrimaryKeys(IEnumerable <Type> types, IDbConnection connection)
 {
     return(types.Select(t =>
     {
         DbObject obj = DbObject.FromType(t, connection);
         return new PrimaryKeyRef()
         {
             Schema = obj.Schema,
             TableName = obj.Name,
             ColumnList = string.Join(",", CreateTable.PrimaryKeyColumns(t)),
             ObjectId = obj.ObjectID,
             ModelType = t
         };
     }));
 }
Beispiel #14
0
        public override bool Equals(object obj)
        {
            DbObject test = obj as DbObject;

            if (test != null)
            {
                return(test.Schema.ToLower().Equals(this.Schema.ToLower()) && test.Name.ToLower().Equals(this.Name.ToLower()));
            }

            Type testType = obj as Type;

            if (testType != null)
            {
                return(Equals(FromType(testType)));
            }

            return(false);
        }
Beispiel #15
0
        private IEnumerable <Action> GetNewTables(IEnumerable <Type> modelTypes, IDbConnection connection)
        {
            List <Action> actions = new List <Action>();

            foreach (var type in modelTypes)
            {
                DbObject obj = DbObject.FromType(type);
                if (!connection.Exists("[sys].[tables] WHERE SCHEMA_NAME([schema_id])=@schema AND [name]=@name", new { schema = obj.Schema, name = obj.Name }))
                {
                    if (!_createdTables.Contains(obj))
                    {
                        _createdTables.Add(obj);
                        actions.Add(new CreateTable(type));
                    }
                }
            }

            return(actions);
        }
Beispiel #16
0
 public static DbObject FromTempName(DbObject obj)
 {
     return(new DbObject(obj.Schema, obj.Name.Substring(0, obj.Name.IndexOf(_tempSuffix))));
 }
Beispiel #17
0
 public CreateTable(Type modelType) : base(MergeObjectType.Table, MergeActionType.Create, DbObject.FromType(modelType).QualifiedName())
 {
     _modelType = modelType;
 }
Beispiel #18
0
 private string CreateTablePrimaryKey()
 {
     return($"CONSTRAINT [PK_{DbObject.ConstraintName(_modelType)}] PRIMARY KEY ({string.Join(", ", PrimaryKeyColumns().Select(col => $"[{col}]"))})");
 }
Beispiel #19
0
 public DropTable(DbObject @object, IDbConnection connection) : base(MergeObjectType.Table, MergeActionType.Delete, @object.QualifiedName())
 {
     _cn          = connection;
     _object      = @object;
     _foreignKeys = GetReferencingForeignKeys(connection, @object.ObjectID);
 }
Beispiel #20
0
 private string SelectInto(DbObject sourceObject, string intoTable)
 {
     return($"SELECT * INTO {intoTable} FROM {sourceObject.ToString()}");
 }
Beispiel #21
0
        private IEnumerable <Action> GetRestorableTempTables(IEnumerable <Type> modelTypes, IDbConnection connection)
        {
            List <Action> results = new List <Action>();

            var tempTables = connection.Query <TempTableRef>(
                @"SELECT 
					SCHEMA_NAME([schema_id]) AS [Schema], [name] AS [TempName], 
					SUBSTRING([name], 0, LEN([name]) - LEN('_temp') + 1) AS [ModelName],
					[object_id] AS [TempObjectId]
				FROM 
					[sys].[tables] WHERE [name] LIKE '%_temp'"                    );

            var restoreTables = from tt in tempTables
                                join mt in modelTypes on new DbObject(tt.Schema, tt.ModelName) equals DbObject.FromType(mt)
                                select new { TempObject = new DbObject(tt.Schema, tt.TempName)
                                             {
                                                 ObjectID = tt.TempObjectId
                                             }, ModelType = mt };

            results.AddRange(restoreTables.Select(rt => new RestoreTempTable(rt.TempObject, rt.ModelType, connection)));

            return(results);
        }
Beispiel #22
0
        private IEnumerable <Action> GetNewColumns(IEnumerable <Type> modelTypes, IDbConnection connection)
        {
            List <Action> results = new List <Action>();

            var schemaColumns = GetSchemaColumns(connection);

            var dbObjects = schemaColumns.GroupBy(item => new DbObject(item.Schema, item.TableName)
            {
                ObjectID = item.ObjectID
            });
            var dcObjectIDs = dbObjects.ToDictionary(obj => obj.Key, obj => obj.Key.ObjectID);

            Dictionary <DbObject, Type> dcModelTypes = new Dictionary <DbObject, Type>();

            var modelColumns = modelTypes.SelectMany(mt => mt.GetProperties()
                                                     .Where(pi => CreateTable.IsSupportedType(pi.PropertyType) && !pi.HasAttribute <NotMappedAttribute>())
                                                     .Select(pi =>
            {
                DbObject obj = DbObject.FromType(mt);
                if (!dcModelTypes.ContainsKey(obj))
                {
                    dcModelTypes.Add(obj, mt);
                }
                return(new ColumnRef()
                {
                    Schema = obj.Schema,
                    TableName = obj.Name,
                    ColumnName = pi.SqlColumnName(),
                    PropertyInfo = pi
                });
            }));

            var newColumns = modelColumns.Where(mcol =>
                                                !_createdTables.Contains(new DbObject(mcol.Schema, mcol.TableName)) &&
                                                !schemaColumns.Any(scol => mcol.Equals(scol)));

            foreach (var colGroup in newColumns.GroupBy(item => new DbObject(item.Schema, item.TableName)))
            {
                if (!_deletedTables.Contains(colGroup.Key))
                {
                    _deletedTables.Add(colGroup.Key);
                }
                if (!_createdTables.Contains(colGroup.Key))
                {
                    _createdTables.Add(colGroup.Key);
                }

                if (IsTableEmpty(connection, colGroup.Key.Schema, colGroup.Key.Name) || dcModelTypes[colGroup.Key].HasAttribute <AllowDropAttribute>())
                {
                    colGroup.Key.ObjectID = dcObjectIDs[colGroup.Key];
                    results.Add(new DropTable(colGroup.Key, connection));
                    results.Add(new CreateTable(dcModelTypes[colGroup.Key]));
                }
                else
                {
                    results.Add(new AddColumns(dcModelTypes[colGroup.Key], colGroup, connection));
                }
            }

            return(results);
        }