 
 SET IMPLICIT_TRANSACTIONS ON
 
-IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CSM_APPLICATION') DROP TABLE CSM_APPLICATION;
+--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CSM_APPLICATION') DROP TABLE CSM_APPLICATION;
 CREATE TABLE  CSM_APPLICATION  (
    APPLICATION_ID  numeric(20) NOT NULL  identity,
    APPLICATION_NAME  varchar(255) NOT NULL default '',

INSERT INTO CSM_ROLE_PRIVILEGE (ROLE_PRIVILEGE_ID,ROLE_ID,PRIVILEGE_ID,UPDATE_DATE) VALUES (27,10,12,0000-00-00);
INSERT INTO CSM_ROLE_PRIVILEGE (ROLE_PRIVILEGE_ID,ROLE_ID,PRIVILEGE_ID,UPDATE_DATE) VALUES (28,11,3,0000-00-00);
INSERT INTO CSM_ROLE_PRIVILEGE (ROLE_PRIVILEGE_ID,ROLE_ID,PRIVILEGE_ID,UPDATE_DATE) VALUES (29,11,11,0000-00-00);
SET IDENTITY_INSERT dbo.CSM_ROLE_PRIVILEGE OFF

/* Table Objects for table csm_user*/

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CSM_USER ') DROP TABLE CSM_USER ;
CREATE TABLE  CSM_USER  (
   USER_ID  numeric(20) NOT NULL  identity,
   LOGIN_NAME  varchar(100) NOT NULL default '',

   PROTECTION_ELEMENT_ID  numeric(20) NOT NULL default '0',
   USER_ID  numeric(20) NOT NULL default '0',
   UPDATE_DATE  smalldatetime NOT NULL default '0000-00-00',
CONSTRAINT UQ_USER_PROTECTION_ELEMENT_PROTECTION_ELEMENT_ID UNIQUE (USER_ID , PROTECTION_ELEMENT_ID)
  
)  
alter table CSM_USER_PE add constraint PK_CSM_USR_PE_USR_PRO_ELE_ID primary key (USER_PROTECTION_ELEMENT_ID);


/*  Foreign keys for table csm_group*/
alter table CSM_GROUP  add constraint FK_CSM_GROUP_APPLICATION_ID foreign key (APPLICATION_ID) REFERENCES CSM_APPLICATION (APPLICATION_ID) ON DELETE CASCADE;
/*  Foreign keys for table csm_pg_pe*/

alter table CSM_PG_PE  add constraint FK_CSM_PG_PE_PROT_ELEMENT_ID foreign key (PROTECTION_ELEMENT_ID) REFERENCES CSM_PROTECTION_ELEMENT (PROTECTION_ELEMENT_ID) ON DELETE CASCADE 
alter table CSM_PG_PE  add constraint FK_CSM_PG_PE_PROT_GROUP_ID foreign key (PROTECTION_GROUP_ID) REFERENCES CSM_PROTECTION_GROUP (PROTECTION_GROUP_ID) ON DELETE CASCADE 

/*  Foreign keys for table csm_protection_element*/
alter table CSM_PROTECTION_ELEMENT  add constraint FK_CSM_PROT_ELEMENT_APP_ID foreign key (APPLICATION_ID)  REFERENCES CSM_APPLICATION (APPLICATION_ID) ON DELETE CASCADE;

/*  Foreign keys for table csm_protection_group*/
alter table CSM_PROTECTION_GROUP  add constraint FK_CSM_PROT_GROUP_APP_ID foreign key (APPLICATION_ID)  REFERENCES CSM_APPLICATION (APPLICATION_ID) ON DELETE NO ACTION 
alter table CSM_PROTECTION_GROUP  add constraint FK_CSM_PARENT_PROT_GR_ID foreign key (PARENT_PROTECTION_GROUP_ID)  REFERENCES CSM_PROTECTION_GROUP (PROTECTION_GROUP_ID) ON DELETE NO ACTION 

/*  Foreign keys for table csm_role*/
alter table CSM_ROLE  add constraint FK_CSM_ROLE_APPLICATION_ID foreign key (APPLICATION_ID)  REFERENCES CSM_APPLICATION (APPLICATION_ID) ON DELETE CASCADE;

/*  Foreign keys for table csm_role_privilege */
alter table CSM_ROLE_PRIVILEGE  add constraint FK_CSM_ROLE_PRIVILEGE_ID foreign key (PRIVILEGE_ID)  REFERENCES CSM_PRIVILEGE (PRIVILEGE_ID) ON DELETE CASCADE 
alter table CSM_ROLE_PRIVILEGE  add constraint FK_CSM_ROLE_PRIVILEG_ROLE_ID foreign key (ROLE_ID)  REFERENCES CSM_ROLE (ROLE_ID) ON DELETE CASCADE 

/*  Foreign keys for table csm_user_group */
alter table CSM_USER_GROUP  add constraint FK_CSM_USER_GROUP_GROUP_ID foreign key (GROUP_ID)  REFERENCES CSM_GROUP (GROUP_ID) ON DELETE CASCADE
alter table CSM_USER_GROUP  add constraint FK_CSM_USER_GROUP_USER_ID foreign key (USER_ID)  REFERENCES CSM_USER (USER_ID) ON DELETE CASCADE 

/*  Foreign keys for table csm_user_group_role_pg */
alter table CSM_USER_GROUP_ROLE_PG  add constraint FK_CSM_USER_GR_ROLE_PG_GR_ID foreign key (GROUP_ID)  REFERENCES CSM_GROUP (GROUP_ID) ON DELETE CASCADE
alter table CSM_USER_GROUP_ROLE_PG  add constraint FK_CSM_USR_GR_ID_ROLE_PG_PROT foreign key (PROTECTION_GROUP_ID)  REFERENCES CSM_PROTECTION_GROUP (PROTECTION_GROUP_ID) ON DELETE CASCADE 
alter table CSM_USER_GROUP_ROLE_PG  add constraint FK_CSM_USR_GR_ROLE_PG_ROLE_ID foreign key (ROLE_ID)  REFERENCES CSM_ROLE (ROLE_ID) ON DELETE NO ACTION 
alter table CSM_USER_GROUP_ROLE_PG  add constraint FK_CSM_USR_GR_ROLE_PG_USR_ID foreign key (USER_ID)  REFERENCES CSM_USER (USER_ID) ON DELETE CASCADE 

/*  Foreign keys for table csm_user_pe */
alter table CSM_USER_PE  add constraint FK_CSM_USER_PE_USER_ID foreign key (USER_ID)  REFERENCES CSM_USER (USER_ID) ON DELETE CASCADE
alter table CSM_USER_PE  add constraint FK_CSM_USR_PE_PROT_ELEMENT_ID foreign key (PROTECTION_ELEMENT_ID)  REFERENCES CSM_PROTECTION_ELEMENT (PROTECTION_ELEMENT_ID) ON DELETE CASCADE 

/* Aarti Sharma : updating all dates with value '0000-00-00' to '2005-01-01'*/

