1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363
| -- Argument.tArgument
CREATE TABLE Argument.tArgument (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tArgument PRIMARY KEY (ID)
, typeID INT NOT NULL CONSTRAINT DF_tArgument_typeID DEFAULT (0)
, CONSTRAINT FK_tArgument_typeID FOREIGN KEY (typeID) REFERENCES Inventory.tType(ID) ON DELETE SET DEFAULT
, [name] NVARCHAR(200) NOT NULL
, dataTypeID INT NOT NULL
-- deletion of a data type should never occur => NO ACTION => error
, CONSTRAINT FK_tArgument_dataTypeIDD FOREIGN KEY (dataTypeID) REFERENCES Argument.tDataType(ID) ON DELETE NO ACTION
, accountRelated BIT NOT NULL CONSTRAINT DF_tArgument_accountRelated DEFAULT (0)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgument_01 ON Argument.tArgument(typeID, [name]) WHERE typeID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgument_typeID ON Argument.tArgument(ID) WHERE typeID = 0
GO
ALTER INDEX IDXM_tArgument_typeID ON Argument.tArgument DISABLE
GO
-- Special Row(s)
SET IDENTITY_INSERT Argument.tArgument ON
GO
INSERT Argument.tArgument (
ID
, typeID
, [name]
, dataTypeID
)
VALUES (
0
, 0
, 'NULL'
, 0
)
GO
SET IDENTITY_INSERT Argument.tArgument OFF
GO
-- Prevent deletion of special rows
CREATE TABLE Argument.tArgument_d (
ID INT NOT NULL
, CONSTRAINT PK_tArgument_d PRIMARY KEY (ID)
, CONSTRAINT FK_tArgument_d FOREIGN KEY (ID) REFERENCES Argument.tArgument(ID) ON DELETE NO ACTION
)
GO
INSERT Argument.tArgument_d VALUES (0)
GO
-- View
CREATE VIEW Argument.vArgument
AS
SELECT
ID
, typeID
, [name]
, dataTypeID
, accountRelated
FROM Argument.tArgument
WHERE ID > 0
AND typeID > 0
GO
-- Argument.tArgumentVersion
CREATE TABLE Argument.tArgumentVersion (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tArgumentVersion PRIMARY KEY (ID)
, argID INT NOT NULL CONSTRAINT DF_tArgumentVersion_argID DEFAULT (0)
, CONSTRAINT FK_tArgumentVersion_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
, [version] INT NOT NULL CONSTRAINT DF_tArgumentVersion_version DEFAULT(1)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentVersion_argID ON Argument.tArgumentVersion(argID) WHERE argID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentVersion_argID ON Argument.tArgumentVersion(argID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentVersion_argID ON Argument.tArgumentVersion DISABLE
GO
-- View
CREATE VIEW Argument.vArgumentVersion
AS
SELECT
ID
, argID
, [version]
FROM Argument.tArgumentVersion
WHERE argID > 0
GO
-- Argument.tEntryArg
CREATE TABLE Argument.tEntryArg (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tEntryArg PRIMARY KEY (ID)
, argID INT NOT NULL CONSTRAINT DF_tEntryArg_argID DEFAULT(0)
, CONSTRAINT FK_tEntryArg_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
, entryID INT NOT NULL CONSTRAINT DF_tEntryArg_entryID DEFAULT(0)
, CONSTRAINT FK_tEntryArg_entryID FOREIGN KEY (entryID) REFERENCES Inventory.tEntry (ID) ON DELETE SET DEFAULT
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tEntryArg_01 ON Argument.tEntryArg(argID, entryID) WHERE argID > 0 AND entryID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tEntryArg_argID ON Argument.tEntryArg(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tEntryArg_argID ON Argument.tEntryArg DISABLE
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tEntryArg_entryID ON Argument.tEntryArg(ID) WHERE entryID = 0
GO
ALTER INDEX IDXM_tEntryArg_entryID ON Argument.tEntryArg DISABLE
GO
-- Special Row(s)
SET IDENTITY_INSERT Argument.tEntryArg ON
GO
INSERT Argument.tEntryArg (
ID
, argID
, entryID
)
VALUES (
0
, 0
, 0
)
GO
SET IDENTITY_INSERT Argument.tEntryArg OFF
GO
-- Prevent deletion of special rows
CREATE TABLE Argument.tEntryArg_d (
ID INT NOT NULL
, CONSTRAINT PK_tEntryArg_d PRIMARY KEY (ID)
, CONSTRAINT FK_tEntryArg_d FOREIGN KEY (ID) REFERENCES Argument.tEntryArg(ID) ON DELETE NO ACTION
)
GO
INSERT Argument.tEntryArg_d VALUES (0)
GO
-- View
CREATE VIEW Argument.vEntryArg
AS
SELECT
ID
, argID
, entryID
FROM Argument.tEntryArg
WHERE ID > 0
AND argID > 0
AND entryID > 0
GO
-- Account.tAccount
CREATE TABLE Account.tAccount (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tAccount PRIMARY KEY (ID)
, [name] NVARCHAR(200) NOT NULL
, passHash BINARY(32) NOT NULL
, salt BINARY(32) NOT NULL
, isDeleted BIT NOT NULL CONSTRAINT DF_tAccount_isDeleted DEFAULT(0)
)
GO
CREATE UNIQUE INDEX UQ_tAccount_name ON Account.tAccount ([name]) WHERE isDeleted = 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tAccount_isDeleted ON Account.tAccount(ID) WHERE isDeleted = 0
GO
ALTER INDEX IDXM_tAccount_isDeleted ON Account.tAccount DISABLE
GO
-- Argument.tArgumentView
CREATE TABLE Argument.tArgumentView (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tArgumentView PRIMARY KEY (ID)
, argID INT NOT NULL CONSTRAINT DF_tArgumentView_argID DEFAULT (0)
, CONSTRAINT FK_tArgumentView_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
, roleID INT NOT NULL CONSTRAINT DF_tArgumentView_roleID DEFAULT (0)
, CONSTRAINT FK_tArgumentView_roleID FOREIGN KEY (roleID) REFERENCES Account.tRole(ID) ON DELETE SET DEFAULT
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentView_01 ON Argument.tArgumentView(argID, roleID) WHERE argID > 0 ANd roleID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentView_argID ON Argument.tArgumentView(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentView_argID ON Argument.tArgumentView DISABLE
GO
CREATE NONCLUSTERED INDEX IDXM_tArgumentView_roleID ON Argument.tArgumentView(ID) WHERE roleID = 0
GO
ALTER INDEX IDXM_tArgumentView_roleID ON Argument.tArgumentView DISABLE
GO
-- View
CREATE VIEW Argument.vArgumentView
AS
SELECT
ID
, argID
, roleID
FROM Argument.tArgumentView
WHERE ID > 0
AND argID > 0
ANd roleID > 0
GO
-- Argument.tArgumentManage
CREATE TABLE Argument.tArgumentManage (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tArgumentManage PRIMARY KEY (ID)
, argID INT NOT NULL CONSTRAINT DF_tArgumentManage_argID DEFAULT (0)
, CONSTRAINT FK_tArgumentManage_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
, roleID INT NOT NULL CONSTRAINT DF_tArgumentManage_roleID DEFAULT (0)
, CONSTRAINT FK_tArgumentManage_roleID FOREIGN KEY (roleID) REFERENCES Account.tRole(ID) ON DELETE SET DEFAULT
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentManage_01 ON Argument.tArgumentManage(argID, roleID) WHERE argID > 0 ANd roleID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentManage_argID ON Argument.tArgumentManage(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentManage_argID ON Argument.tArgumentManage DISABLE
GO
CREATE NONCLUSTERED INDEX IDXM_tArgumentManage_roleID ON Argument.tArgumentManage(ID) WHERE roleID = 0
GO
ALTER INDEX IDXM_tArgumentManage_roleID ON Argument.tArgumentManage DISABLE
GO
-- View
CREATE VIEW Argument.vArgumentManage
AS
SELECT
ID
, argID
, roleID
FROM Argument.tArgumentManage
WHERE ID > 0
AND argID > 0
ANd roleID > 0
GO
-- Argument.tArgumentRefType
CREATE TABLE Argument.tArgumentRefType (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tArgumentRefType PRIMARY KEY (ID)
, argID INT NOT NULL CONSTRAINT DF_tArgumentRefType_argID DEFAULT (0)
, CONSTRAINT FK_tArgumentRefType_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
, typeID INT NOT NULL CONSTRAINT DF_tArgumentRefType_typeID DEFAULT (0)
, CONSTRAINT FK_tArgumentRefType_typeID FOREIGN KEY (typeID) REFERENCES Inventory.tType(ID) ON DELETE SET DEFAULT
-- , [version] INT NOT NULL CONSTRAINT DF_tArgumentRefType_version DEFAULT (0)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentRefType_argID ON Argument.tArgumentRefType(argID) WHERE argID > 0 AND typeID > 0
GO
CREATE NONCLUSTERED INDEX IDX_tArgumentRefType_typeID ON Argument.tArgumentRefType(typeID) WHERE argID > 0 AND typeID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentRefType_argID ON Argument.tArgumentRefType(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentRefType_argID ON Argument.tArgumentRefType DISABLE
GO
CREATE NONCLUSTERED INDEX IDXM_tArgumentRefType_typeID ON Argument.tArgumentRefType(ID) WHERE typeID = 0
GO
ALTER INDEX IDXM_tArgumentRefType_typeID ON Argument.tArgumentRefType DISABLE
GO
-- View
CREATE VIEW Argument.vArgumentRefType
AS
SELECT
ID
, argID
, typeID
FROM Argument.tArgumentRefType
WHERE ID > 0
AND argID > 0
AND typeID > 0
GO
-- 2014-09-02
-- Argument.tArgumentOption
CREATE TABLE Argument.tArgumentOption (
ID INT NOT NULL IDENTITY
, CONSTRAINT PK_tArgumentOption PRIMARY KEY (ID)
, argID INT NOT NULL CONSTRAINT DF_tArgumentOption_argID DEFAULT (0)
, CONSTRAINT FK_tArgumentOption_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
, [option] NVARCHAR(200) NOT NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentOption_01 ON Argument.tArgumentOption(argID, [option]) WHERE argID > 0
GO
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentOption_argID ON Argument.tArgumentOption(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentOption_argID ON Argument.tArgumentOption DISABLE
GO
-- View
CREATE VIEW Argument.vArgumentOption
AS
SELECT
ID
, argID
, [option]
FROM Argument.tArgumentOption
WHERE ID > 0
AND argID > 0
GO
CREATE NONCLUSTERED INDEX IDX_tArgumentView_roleID ON Argument.tArgumentView(roleID) WHERE argID > 0 ANd roleID > 0
GO
CREATE NONCLUSTERED INDEX IDX_tArgumentManage_roleID ON Argument.tArgumentManage(roleID) WHERE argID > 0 ANd roleID > 0
GO |
Partager