예제 #1
0
        internal void CreateFK(DBColumn ref_column)
        {
            if (this.Parent is DBTable)
            {
                var fk = new DBFKConstraint
                {
                    Action          = DB.DBAction.Add,
                    Column          = this,
                    ReferenceColumn = ref_column,
                    Connection      = this.Parent.Connection,
                    Schema          = this.Owner.Schema,
                    Parent          = this.Parent,
                    Name            = string.Format("FK_{0}_{1}_{2}_ref_{3}_{4}_{5}", this.Owner.Name, this.Parent.Name, this.Name, ref_column.Schema.Name, ref_column.Parent.Name, ref_column.Name),
                    State           = DBObject.DBObjectState.New
                };

                var command = new DBCommand {
                    Sql = fk.GetSQL(), Description = "Create FK", Owner = this
                };
                this.Connection.Project.Commands.Add(command);
            }
        }
        public void CreateLookupTable()
        {
            var owner = (Column.Parent as DBTable);
            var sql   = string.Format("if object_id('[{0}].[{1}]') is null select * into [{0}].[{1}] from ({2}) t ", Column.Owner.Schema.Name, LookupTableName, GetPreview());

            var cmd_create_table = new DBCommand {
                Owner = owner, Sql = sql, Description = "Create Lookup Table"
            };

            owner.Connection.Project.Commands.Add(cmd_create_table);
            DBProjectManager.Execute(cmd_create_table);

            //refresh so now the schema has the table..
            DBSchemaManager.Refresh(Column.Schema);

            var lookup_table = Column.Schema.Tables.FirstOrDefault(t => t.Name == LookupTableName);

            DBTableManager.Refresh(lookup_table);
            lookup_table.Action = DB.DBAction.Alter;

            var pk_col = lookup_table.Columns.FirstOrDefault(x => x.Name == "id");

            pk_col.Action   = DB.DBAction.Alter;
            pk_col.Nullable = false;
            var cmd_not_null = pk_col.Connection.Project.Commands.FirstOrDefault(c => c.Owner == pk_col);

            if (cmd_not_null != null)
            {
                DBProjectManager.Execute(cmd_not_null);
            }

            //create the PK
            lookup_table.PrimaryKey = new DBPKConstraint
            {
                Action     = DB.DBAction.Add,
                Parent     = lookup_table,
                Schema     = lookup_table.Schema,
                Connection = lookup_table.Connection,
                Name       = "PK_" + LookupTableName
            };

            lookup_table.PrimaryKey.Columns.Add(pk_col);


            var cmd = new DBCommand {
                Owner = lookup_table, Sql = lookup_table.PrimaryKey.GetSQL(), Description = "Create PK"
            };

            lookup_table.Connection.Project.Commands.Add(cmd);
            DBProjectManager.Execute(cmd);

            //table.Refresh();

            //reference by FK
            var fk = new DBFKConstraint
            {
                Name            = "FK_" + owner.Schema.Name + "_" + owner.Name + "_" + Column.Name + "_ref_" + lookup_table.Schema.Name + "_" + lookup_table.Name + "_id",
                Action          = DB.DBAction.Add,
                Parent          = Column.Parent,
                Schema          = Column.Owner.Schema,
                Column          = Column,
                Connection      = Column.Connection,
                OnUpdate        = DBFKConstraint.CascadeModes.Cascade,
                ReferenceColumn = pk_col
            };


            owner.ForeignKeys.Add(fk);

            var cmd_fk = new DBCommand {
                Owner = owner, Sql = fk.GetSQL(), Description = "Create FK"
            };

            lookup_table.Connection.Project.Commands.Add(cmd_fk);
            DBProjectManager.Execute(cmd_fk);
        }
예제 #3
0
        public DB get_db_basic_data(DBConnection conn)
        {
            #region sql
            var sql = @"
declare @schema_folders table (id nvarchar(200), object_name nvarchar(200), object_type nvarchar(50), parent_id nvarchar(200))
insert @schema_folders
	select distinct 
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS
		, [object_name] = o.type_desc collate SQL_Latin1_General_CP1_CI_AS
		, [object_type] = 'FOLDER'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name
	from 
		sys.objects o 
		join sys.schemas s on o.schema_id = s.schema_id and o.parent_object_id = 0
	where
		s.schema_id between 5 and 16383

-------------------------------------------------------------------------------------------------------------------------------

declare @object_folders table (id nvarchar(200), object_name nvarchar(200), object_type nvarchar(50), parent_id nvarchar(200))
insert @object_folders
	select distinct --constraints etc
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS
		, [object_name] = o.type_desc collate SQL_Latin1_General_CP1_CI_AS
		, [object_type] = 'FOLDER'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name
	from 
		sys.schemas s
		join sys.objects o on o.schema_id = s.schema_id
		join sys.objects po on o.parent_object_id = po.object_id
	where
		s.schema_id between 5 and 16383
		and o.parent_object_id <> 0

-------------------------------------------------------------------------------------------------------------------------------

declare @object_property_folders table (id nvarchar(200), object_name nvarchar(200), object_type nvarchar(50), parent_id nvarchar(200))
insert @object_property_folders
	select distinct 
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/PROPERTY'
		, [object_name] = 'PROPERTY'
		, [object_type] = 'FOLDER'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name
	from 
		sys.objects o 
		join sys.schemas s on o.schema_id = s.schema_id and o.parent_object_id = 0
		join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = 0
	where
		s.schema_id between 5 and 16383
-------------------------------------------------------------------------------------------------------------------------------

declare @sub_object_property_folders table (id nvarchar(200), object_name nvarchar(200), object_type nvarchar(50), parent_id nvarchar(200))
insert @sub_object_property_folders
	select distinct --constraints etc
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/PROPERTY'
		, [object_name] = 'PROPERTY'
		, [object_type] = 'FOLDER'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name  + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name
	from 
		sys.schemas s
		join sys.objects o on o.schema_id = s.schema_id
		join sys.objects po on o.parent_object_id = po.object_id
		join sys.extended_properties ep on ep.major_id = o.object_id and ep.class = 1
	where
		s.schema_id between 5 and 16383
		and o.parent_object_id <> 0

-------------------------------------------------------------------------------------------------------------------------------

declare @object_column_folders table (id nvarchar(200), object_name nvarchar(200), object_type nvarchar(50), parent_id nvarchar(200))
insert @object_column_folders
	select distinct --columns
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/COLUMN'
		, [object_name] = 'COLUMN'
		, [object_type] = 'FOLDER'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name
	from 
		sys.schemas s
		join sys.objects o on o.schema_id = s.schema_id
		join sys.columns c on c.object_id = o.object_id
	where
		s.schema_id between 5 and 16383

-------------------------------------------------------------------------------------------------------------------------------

declare @object_column_property_folders table (id nvarchar(200), object_name nvarchar(200), object_type nvarchar(50), parent_id nvarchar(200))
insert @object_column_property_folders
	select distinct --columns
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/COLUMN/' + c.name + '/PROPERTY'
		, [object_name] = 'PROPERTY'
		, [object_type] = 'FOLDER'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/COLUMN/' + c.name
	from 
		sys.schemas s
		join sys.objects o on o.schema_id = s.schema_id
		join sys.columns c on c.object_id = o.object_id
		join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = c.column_id
	where
		s.schema_id between 5 and 16383

-------------------------------------------------------------------------------------------------------------------------------

select 
	[id] = cast([id] as nvarchar(200))
	, [object_name] = cast([object_name] as nvarchar(200))
	, [object_type] = cast([object_type] as nvarchar(200))
	, [parent_id] = cast([parent_id] as nvarchar(200))
	, [definition] = cast([definition] as nvarchar(max))
from
(
select	--database
	[id] = db_name()
	, [object_name] = db_name()
	, [object_type] = 'DATABASE' collate SQL_Latin1_General_CP1_CI_AS
	, [parent_id] = null
	, [definition] = null

union all

select --database folders
	[id] = db_name() + '/SCHEMA'
	, [object_name] = 'SCHEMA'
	, [object_type] = 'FOLDER' collate SQL_Latin1_General_CP1_CI_AS
	, [parent_id] = db_name()
	, [definition] = null

union all

select --schemas
	[id] = db_name() + '/SCHEMA/' + s.name
	, [object_name] = s.name
	, [object_type] = 'SCHEMA' collate SQL_Latin1_General_CP1_CI_AS
	, [parent_id] = db_name() + '/SCHEMA' collate SQL_Latin1_General_CP1_CI_AS	
	, [definition] = null
from 
	sys.schemas s
where
	s.schema_id between 5 and 16383

union all

	select --schema folders
		*, [definition] = null 
	from @schema_folders

union all

select --tables etc
	[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name
	, [object_name] = o.name
	, [object_type] = o.type_desc
	, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS
	, [definition] = OBJECT_DEFINITION(o.object_id)
from 
	sys.schemas s
	join sys.objects o on o.schema_id = s.schema_id
where
	s.schema_id between 5 and 16383
	and o.parent_object_id = 0

union all

	select 
		*, [definition] = null 
	from @object_folders

union all

	select 
		*, [definition] = null 
	from @object_column_folders

union all

	select 
		*, [definition] = null 
	from @object_property_folders

union all

	select --table properties etc 
		[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/PROPERTY/' + ep.name
		, [object_name] = ep.name
		, [object_type] = 'PROPERTY'
		, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/PROPERTY'
		, [definition] = cast(ep.value as nvarchar(max))
	from 
		sys.objects o 
		join sys.schemas s on o.schema_id = s.schema_id and o.parent_object_id = 0
		join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = 0
	where
		s.schema_id between 5 and 16383

union all

select --special case for history
	[id] = db_name() + '/SCHEMA/history/USER_TABLE/EventLog/FOREIGN_KEY_CONSTRAINT'
	, [object_name] = o.name
	, [object_type] = 'FOLDER'
	, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name
	, [definition] = OBJECT_DEFINITION(o.object_id)
from
	sys.objects o 
	join sys.schemas s on o.schema_id = s.schema_id and o.parent_object_id = 0
where
	s.name = 'history' and o.name = 'EventLog'

union all

select --special case for history fk
	[id] = db_name() + '/SCHEMA/history/USER_TABLE/EventLog/FOREIGN_KEY_CONSTRAINT/' + 'FK_history_eventlog_' + s.name + '_' + o.name
	, [object_name] = 'FK_history_eventlog_' + s.name + '_' + o.name
	, [object_type] = 'FOREIGN_KEY_CONSTRAINT'
	, [parent_id] = db_name() + '/SCHEMA/history/USER_TABLE/EventLog/FOREIGN_KEY_CONSTRAINT'
	, [definition] = null
from
	sys.objects o 
	join sys.schemas s on o.schema_id = s.schema_id and o.parent_object_id = 0
where
	object_id('history.EventLog') is not null
	and exists(select 1 from sys.extended_properties ep where ep.major_id = o.object_id and ep.minor_id = 0 and ep.name = 'use_history' and ep.value = 'true')

union all

select --constraints etc
	[id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name
	, [object_name] = o.name
	, [object_type] = o.type_desc
	, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS
	, [definition] = OBJECT_DEFINITION(o.object_id)
from 
	sys.schemas s
	join sys.objects o on o.schema_id = s.schema_id
	join sys.objects po on o.parent_object_id = po.object_id
where
	s.schema_id between 5 and 16383
	and o.parent_object_id <> 0

union all

select --properties of constraints etc
	[id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/PROPERTY/' + ep.name
	, [object_name] = ep.name
	, [object_type] = 'PROPERTY'
	, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + po.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + po.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/PROPERTY'
	, [definition] = cast(ep.value as nvarchar(max))
from 
	sys.schemas s
	join sys.objects o on o.schema_id = s.schema_id
	join sys.objects po on o.parent_object_id = po.object_id
	join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = 0
where
	s.schema_id between 5 and 16383
	and o.parent_object_id <> 0

union all

	select 
		*, [definition] = null 
	from @sub_object_property_folders

union all

select --columns
	[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/COLUMN/'  + c.name
	, [object_name] = c.name
	, [object_type] = 'COLUMN' collate SQL_Latin1_General_CP1_CI_AS
	, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name  + '/COLUMN'
	, [definition] = null
from 
	sys.schemas s
	join sys.objects o on o.schema_id = s.schema_id
	join sys.columns c on c.object_id = o.object_id
where
	s.schema_id between 5 and 16383

union all

	select --property folders
		*, [definition] = null
	from @object_column_property_folders

union all

select --columns properties
	[id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name + '/COLUMN/'  + c.name + '/PROPERTY/' + ep.name
	, [object_name] = ep.name
	, [object_type] = 'PROPERTY' collate SQL_Latin1_General_CP1_CI_AS
	, [parent_id] = db_name() + '/SCHEMA/' + s.name + '/' + o.type_desc collate SQL_Latin1_General_CP1_CI_AS + '/' + o.name  + '/COLUMN/' + c.name + '/PROPERTY'
	, [definition] = cast(ep.value as nvarchar(max))
from 
	sys.schemas s
	join sys.objects o on o.schema_id = s.schema_id
	join sys.columns c on c.object_id = o.object_id
	join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = c.column_id
where
	s.schema_id between 5 and 16383

) t
order by id
                ";

            #endregion

            db_objects.Clear();

            var db = new DB();

            var data = conn.Connection.GetDataTable(sql); //all objects

            foreach (DataRow r in data.Rows)
            {
                var id          = r["id"].ToString();
                var object_name = r["object_name"].ToString();
                var object_type = r["object_type"].ToString();
                var parent_id   = r["parent_id"].ToString();
                var definition  = r["definition"].ToString();

                var db_obj = new DBObject();


                switch (r["object_type"].ToString())
                {
                case "DATABASE": db_obj = new DB(); break;

                case "FOLDER": db_obj = new DBFolder(); break;

                case "SCHEMA": db_obj = new DBSchema(); break;

                case "USER_TABLE": db_obj = new DBTable(); break;

                case "VIEW": db_obj = new DBView(); break;

                case "SQL_STORED_PROCEDURE": db_obj = new DBStoredProcedure(); break;

                case "SQL_TRIGGER": db_obj = new DBTrigger(); break;

                case "FOREIGN_KEY_CONSTRAINT": db_obj = new DBFKConstraint(); break;

                case "PRIMARY_KEY_CONSTRAINT": db_obj = new DBPKConstraint(); break;

                case "SQL_SCALAR_FUNCTION": db_obj = new DBFunction(); break;              //

                case "SQL_INLINE_TABLE_VALUED_FUNCTION": db_obj = new DBFunction(); break; //

                case "SQL_TABLE_VALUED_FUNCTION": db_obj = new DBFunction(); break;        //

                case "CHECK_CONSTRAINT": db_obj = new DBCheckConstraint(); break;

                case "UNIQUE_CONSTRAINT": db_obj = new DBUniqueConstraint(); break;

                case "DEFAULT_CONSTRAINT": db_obj = new DBDefaultConstraint(); break;

                case "COLUMN": db_obj = new DBColumn(); break;

                case "PROPERTY": db_obj = new DBExtendedProperty(); break;

                default: db_obj = null; break;
                }

                if (db_obj != null)
                {
                    db_obj.PathID        = id;
                    db_obj.Name          = object_name;
                    db_obj.Connection    = conn;
                    db_obj.DefinitionSQL = definition;

                    db_objects.Add(id, db_obj); //flat list

                    if (parent_id != "")
                    {
                        //update parent reference in this object
                        var parent_obj = db_objects[parent_id];
                        if (db_obj is DBFolder)
                        {
                            db_obj.Parent = parent_obj; //an object owns a folder
                        }
                        else
                        {
                            db_obj.Parent = parent_obj.Parent; //parent of the folder owns the object
                        }
                        //add this object as a child
                        if (parent_obj is DBFolder)
                        {
                            parent_obj.Parent.DBObjects.Add(db_obj); //add it as direct child of folder owner
                        }
                        parent_obj.DBObjects.Add(db_obj);            //folder owns automatically

                        //set schema reference if applicable
                        var schema_obj = db_obj as DBSchemaObject;
                        if (parent_obj.Parent != null && parent_obj.Parent is DBSchema)
                        {
                            schema_obj.Schema = parent_obj.Parent as DBSchema;
                        }

                        //set database for schemas
                        if (db_obj is DBSchema)
                        {
                            (db_obj as DBSchema).Database = db;
                        }
                    }
                    else
                    {
                        db = db_obj as DB;
                    }
                }
            }

            //db_objects list populated.

            var fks = this.get_foreign_keys(conn); //fks
            //wire the foreign keys to columns
            foreach (var fk in fks)
            {
                var o = (db_objects[fk.Key] as DBFKConstraint);
                var v = fk.Value;

                o.CascadeLookup   = v.CascadeLookup;
                o.Column          = v.Column;
                o.OnDelete        = v.OnDelete;
                o.OnUpdate        = v.OnUpdate;
                o.ReferenceColumn = v.ReferenceColumn;
                o.RefreshParent   = v.RefreshParent;
                o.RespectCreate   = v.RespectCreate;
                o.RespectDelete   = v.RespectDelete;
                o.RespectRead     = v.RespectRead;
                o.RespectUpdate   = v.RespectUpdate;

                //update sub tables collection
                if (!v.ReferenceColumn.Parent.DBObjects.Contains(o))
                {
                    v.ReferenceColumn.Parent.DBObjects.Add(o);
                }
            }

            //distribute db_objects into owner collections (properties of type list and same dbobject type)
            foreach (var ob in db_objects.Select(x => x.Value).ToList())
            {
                var type  = ob.GetType();
                var infos = type.GetProperties();
                foreach (var info in infos)
                {
                    //check the type
                    Type proptype = info.PropertyType;
                    if (proptype.IsGenericType &&
                        (proptype.GetGenericTypeDefinition() == typeof(List <>) ||
                         proptype.GetGenericTypeDefinition() == typeof(BindingList <>) ||
                         proptype.GetGenericTypeDefinition() == typeof(MyBindingList <>)))
                    {
                        Type itemType = proptype.GetGenericArguments()[0];
                        if (typeof(DBObject).IsAssignableFrom(itemType)) //if inherits DBObject
                        {
                            var obj        = info.GetValue(ob);
                            var collection = new List <DBObject>();
                            collection.AddRange(ob.DBObjects.Where(o => o.GetType() == itemType).ToList());
                            var method = proptype.GetMethod("Add");
                            foreach (var i in collection)
                            {
                                if (obj != null)
                                {
                                    method.Invoke(obj, new object[] { i });
                                }
                            }
                        }
                    }
                }
            }

            return(db);
        }