/*
 * ER/Studio 8.0 SQL Code Generation
 * Project :      IndorPower.dm1
 *
 * Date Created : Tuesday, September 07, 2010 18:44:51
 * Target DBMS : Microsoft SQL Server 2008
 */

/* 
 * TABLE: Attribute 
 */

CREATE TABLE Attribute(
    ID              int             IDENTITY(1,1),
    DataTypeID      int             NULL,
    IsNullable      int             NOT NULL,
    PhysicalName    varchar(100)    NOT NULL,
    LogicalName     varchar(100)    NOT NULL,
    ObjectTypeID    int             NOT NULL,
    CONSTRAINT PK6 PRIMARY KEY CLUSTERED (ID)
)
go



IF OBJECT_ID('Attribute') IS NOT NULL
    PRINT '<<< CREATED TABLE Attribute >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Attribute >>>'
go

/* 
 * TABLE: BaseObject 
 */

CREATE TABLE BaseObject(
    ID              int             IDENTITY(1,1),
    ParentID        int             NULL,
    ObjectName      varchar(100)    NOT NULL,
    ObjectTypeID    int             NOT NULL,
    CONSTRAINT PK5 PRIMARY KEY CLUSTERED (ID)
)
go



IF OBJECT_ID('BaseObject') IS NOT NULL
    PRINT '<<< CREATED TABLE BaseObject >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE BaseObject >>>'
go

/* 
 * TABLE: DictObjectTypes 
 */

CREATE TABLE DictObjectTypes(
    ID              int             IDENTITY(1,1),
    ParentTypeID    int             NULL,
    LogicalName     varchar(100)    NOT NULL,
    PhysicalName    varchar(100)    NOT NULL,
    CONSTRAINT PK3 PRIMARY KEY CLUSTERED (ID)
)
go



IF OBJECT_ID('DictObjectTypes') IS NOT NULL
    PRINT '<<< CREATED TABLE DictObjectTypes >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE DictObjectTypes >>>'
go

/* 
 * TABLE: Folder 
 */

CREATE TABLE Folder(
    ID    int    NOT NULL,
    CONSTRAINT PK2 PRIMARY KEY CLUSTERED (ID)
)
go



IF OBJECT_ID('Folder') IS NOT NULL
    PRINT '<<< CREATED TABLE Folder >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE Folder >>>'
go

/* 
 * INDEX: Ref315 
 */

CREATE INDEX Ref315 ON Attribute(ObjectTypeID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Attribute') AND name='Ref315')
    PRINT '<<< CREATED INDEX Attribute.Ref315 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Attribute.Ref315 >>>'
go

/* 
 * INDEX: ALTKEY_Name 
 */

CREATE UNIQUE INDEX ALTKEY_Name ON BaseObject(ObjectName)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('BaseObject') AND name='ALTKEY_Name')
    PRINT '<<< CREATED INDEX BaseObject.ALTKEY_Name >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX BaseObject.ALTKEY_Name >>>'
go

/* 
 * INDEX: Ref312 
 */

CREATE INDEX Ref312 ON BaseObject(ObjectTypeID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('BaseObject') AND name='Ref312')
    PRINT '<<< CREATED INDEX BaseObject.Ref312 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX BaseObject.Ref312 >>>'
go

/* 
 * INDEX: Ref514 
 */

CREATE INDEX Ref514 ON BaseObject(ParentID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('BaseObject') AND name='Ref514')
    PRINT '<<< CREATED INDEX BaseObject.Ref514 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX BaseObject.Ref514 >>>'
go

/* 
 * INDEX: Ref316 
 */

CREATE INDEX Ref316 ON DictObjectTypes(ParentTypeID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('DictObjectTypes') AND name='Ref316')
    PRINT '<<< CREATED INDEX DictObjectTypes.Ref316 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX DictObjectTypes.Ref316 >>>'
go

/* 
 * INDEX: Ref56 
 */

CREATE INDEX Ref56 ON Folder(ID)
go
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Folder') AND name='Ref56')
    PRINT '<<< CREATED INDEX Folder.Ref56 >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX Folder.Ref56 >>>'
go

/* 
 * TABLE: Attribute 
 */

ALTER TABLE Attribute ADD CONSTRAINT RefDictObjectTypes15 
    FOREIGN KEY (ObjectTypeID)
    REFERENCES DictObjectTypes(ID)
go


/* 
 * TABLE: BaseObject 
 */

ALTER TABLE BaseObject ADD CONSTRAINT RefDictObjectTypes12 
    FOREIGN KEY (ObjectTypeID)
    REFERENCES DictObjectTypes(ID)
go

ALTER TABLE BaseObject ADD CONSTRAINT RefBaseObject14 
    FOREIGN KEY (ParentID)
    REFERENCES BaseObject(ID)
go


/* 
 * TABLE: DictObjectTypes 
 */

ALTER TABLE DictObjectTypes ADD CONSTRAINT RefDictObjectTypes16 
    FOREIGN KEY (ParentTypeID)
    REFERENCES DictObjectTypes(ID)
go


/* 
 * TABLE: Folder 
 */

ALTER TABLE Folder ADD CONSTRAINT RefBaseObject6 
    FOREIGN KEY (ID)
    REFERENCES BaseObject(ID)
go


SET IDENTITY_INSERT DictObjectTypes ON

INSERT INTO DictObjectTypes(ID, ParentTypeID, LogicalName, PhysicalName) VALUES (1, NULL, 'Базовый объект', 'BaseObject')
INSERT INTO DictObjectTypes(ID, ParentTypeID, LogicalName, PhysicalName) VALUES (2, 1, 'Папка объектов', 'Folder')

SET IDENTITY_INSERT DictObjectTypes OFF

SET IDENTITY_INSERT Attribute ON

INSERT INTO Attribute(ID, ObjectTypeID, LogicalName, PhysicalName, IsNullable) VALUES (1, 1, 'Код объекта', 'ID', 0)
INSERT INTO Attribute(ID, ObjectTypeID, LogicalName, PhysicalName, IsNullable) VALUES (2, 1, 'Код объекта-владельца', 'ParentID', 1)
INSERT INTO Attribute(ID, ObjectTypeID, LogicalName, PhysicalName, IsNullable) VALUES (3, 1, 'Наименование', 'ObjectName', 0)
INSERT INTO Attribute(ID, ObjectTypeID, LogicalName, PhysicalName, IsNullable) VALUES (4, 1, 'Код типа объекта', 'ObjectTypeID', 0)

INSERT INTO Attribute(ID, ObjectTypeID, LogicalName, PhysicalName, IsNullable) VALUES (5, 2, 'Код папки', 'ID', 0)

SET IDENTITY_INSERT Attribute OFF
