SHARE
TWEET

Untitled

a guest Aug 23rd, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE resources
  2.   (
  3.      resource_id BIGINT IDENTITY,
  4.      module_name NVARCHAR(255) NOT NULL,
  5.      page_name   NVARCHAR(255) NOT NULL
  6.   )
  7.  
  8. go
  9.  
  10. EXEC Sp_addextendedproperty
  11.   'MS_Description',
  12.   'Creation Of Resources Table',
  13.   'SCHEMA',
  14.   'dbo',
  15.   'TABLE',
  16.   'resources'
  17.  
  18. go
  19.  
  20. CREATE UNIQUE INDEX resources_resource_id_uindex
  21.   ON resources (resource_id)
  22.  
  23. go
  24.  
  25. ALTER TABLE resources
  26.   ADD CONSTRAINT resources_pk PRIMARY KEY NONCLUSTERED (resource_id)
  27.  
  28. go
  29.  
  30. CREATE TABLE role_group
  31.   (
  32.      role_group_id BIGINT IDENTITY,
  33.      group_name    NVARCHAR(255) NOT NULL,
  34.      user_id       BIGINT CONSTRAINT role_group_patients_patient_id_fk
  35.      REFERENCES patients
  36.   )
  37.  
  38. go
  39.  
  40. EXEC Sp_addextendedproperty
  41.   'MS_Description',
  42.   'Roles Group for Users',
  43.   'SCHEMA',
  44.   'dbo',
  45.   'TABLE',
  46.   'role_group'
  47.  
  48. go
  49.  
  50. CREATE UNIQUE INDEX role_group_role_group_id_uindex
  51.   ON role_group (role_group_id)
  52.  
  53. go
  54.  
  55. CREATE UNIQUE INDEX role_group_group_name_uindex
  56.   ON role_group (group_name)
  57.  
  58. go
  59.  
  60. ALTER TABLE role_group
  61.   ADD CONSTRAINT role_group_pk PRIMARY KEY NONCLUSTERED (role_group_id)
  62.  
  63. go
  64.  
  65. EXEC Sp_rename
  66.   'role_group',
  67.   role_groups,
  68.   'OBJECT'
  69.  
  70. go
  71.  
  72. ALTER TABLE patients
  73.   ADD role_group_id BIGINT
  74.  
  75. go
  76.  
  77. ALTER TABLE patients
  78.   ADD CONSTRAINT patients_role_groups_role_group_id_fk FOREIGN KEY (
  79.   role_group_id) REFERENCES role_groups
  80.  
  81. go
  82.  
  83. create table roles
  84. (
  85.     role_id bigint identity
  86.         constraint roles_pk
  87.             primary key nonclustered,
  88.     role_group_id bigint
  89.         constraint roles_role_groups_role_group_id_fk
  90.             references role_groups,
  91.     [create] bit,
  92.     [read] bit,
  93.     [update] bit,
  94.     [delete] bit
  95. )
  96. go
  97.  
  98. exec sp_addextendedproperty 'MS_Description', 'Creation of Roles Table', 'SCHEMA', 'dbo', 'TABLE', 'roles'
  99. go
  100.  
  101. create unique index roles_role_id_uindex
  102.     on roles (role_id)
  103. go
  104.  
  105. alter table roles add default 0 for [create]
  106. go
  107.  
  108. alter table roles add default 0 for [read]
  109. go
  110.  
  111. alter table roles add default 0 for [update]
  112. go
  113.  
  114. alter table roles add default 0 for [delete]
  115. go
  116.  
  117. alter table resources
  118.     add role_id BIGINT
  119. go
  120.  
  121. alter table resources
  122.     add constraint resources_roles_role_id_fk
  123.         foreign key (role_id) references roles
  124. go
  125.  
  126. alter table resources drop column role_id
  127. go
  128.  
  129. alter table resources drop constraint resources_roles_role_id_fk
  130. go
  131.  
  132. create table roles_and_resources_mappings
  133. (
  134.     roles_and_resources_mapping_id BIGINT identity,
  135.     role_id BIGINT not null
  136.         constraint roles_and_resources_mappings_roles_role_id_fk
  137.             references roles,
  138.     resource_id BIGINT not null
  139.         constraint roles_and_resources_mappings_resources_resource_id_fk
  140.             references resources
  141. )
  142. go
  143.  
  144. create unique index roles_and_resources_mappings_roles_and_resources_mapping_id_uindex
  145.     on roles_and_resources_mappings (roles_and_resources_mapping_id)
  146. go
  147.  
  148. alter table roles_and_resources_mappings
  149.     add constraint roles_and_resources_mappings_pk
  150.         primary key nonclustered (roles_and_resources_mapping_id)
  151. go
  152.  
  153. alter table roles_and_resources_mappings drop constraint roles_and_resources_mappings_resources_resource_id_fk
  154. go
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top