· 7 years ago · Jan 17, 2019, 11:50 AM
1-- --------------------------------------------------------
2-- Host: 172.17.0.188
3-- Server Version: Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)
4-- Server Betriebssystem: Linux (Ubuntu 16.04.3 LTS)
5-- HeidiSQL Version: 8.0.0.4396
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES */;
10/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
11/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
12
13-- Exportiere Datenbank Struktur für Inventarisierung
14CREATE DATABASE IF NOT EXISTS "Inventarisierung";
15USE "Inventarisierung";
16
17
18-- Exportiere Struktur von Funktion Inventarisierung.fn_diagramobjects
19DELIMITER //
20
21 CREATE FUNCTION dbo.fn_diagramobjects()
22 RETURNS int
23 WITH EXECUTE AS N'dbo'
24 AS
25 BEGIN
26 declare @id_upgraddiagrams int
27 declare @id_sysdiagrams int
28 declare @id_helpdiagrams int
29 declare @id_helpdiagramdefinition int
30 declare @id_creatediagram int
31 declare @id_renamediagram int
32 declare @id_alterdiagram int
33 declare @id_dropdiagram int
34 declare @InstalledObjects int
35
36 select @InstalledObjects = 0
37
38 select @id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),
39 @id_sysdiagrams = object_id(N'dbo.sysdiagrams'),
40 @id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),
41 @id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),
42 @id_creatediagram = object_id(N'dbo.sp_creatediagram'),
43 @id_renamediagram = object_id(N'dbo.sp_renamediagram'),
44 @id_alterdiagram = object_id(N'dbo.sp_alterdiagram'),
45 @id_dropdiagram = object_id(N'dbo.sp_dropdiagram')
46
47 if @id_upgraddiagrams is not null
48 select @InstalledObjects = @InstalledObjects + 1
49 if @id_sysdiagrams is not null
50 select @InstalledObjects = @InstalledObjects + 2
51 if @id_helpdiagrams is not null
52 select @InstalledObjects = @InstalledObjects + 4
53 if @id_helpdiagramdefinition is not null
54 select @InstalledObjects = @InstalledObjects + 8
55 if @id_creatediagram is not null
56 select @InstalledObjects = @InstalledObjects + 16
57 if @id_renamediagram is not null
58 select @InstalledObjects = @InstalledObjects + 32
59 if @id_alterdiagram is not null
60 select @InstalledObjects = @InstalledObjects + 64
61 if @id_dropdiagram is not null
62 select @InstalledObjects = @InstalledObjects + 128
63
64 return @InstalledObjects
65 END
66 //
67DELIMITER ;
68
69
70-- Exportiere Struktur von Tabelle Inventarisierung.Geräte
71CREATE TABLE IF NOT EXISTS "Geräte" (
72 "id" INT NOT NULL,
73 "datumAnschaffung" DATE NOT NULL,
74 "preis" FLOAT NOT NULL,
75 "inventarNr" INT NOT NULL,
76 "verkäufterId" INT NOT NULL,
77 "kategorieId" INT NOT NULL,
78 "mangelId" INT NOT NULL,
79 "raumId" INT NOT NULL,
80 PRIMARY KEY ("id")
81);
82
83-- Daten Export vom Benutzer nicht ausgewählt
84
85
86-- Exportiere Struktur von Tabelle Inventarisierung.GeräteVerkäufter
87CREATE TABLE IF NOT EXISTS "GeräteVerkäufter" (
88 "id" INT NOT NULL,
89 "standort" NVARCHAR(255) NOT NULL,
90 "name" NVARCHAR(255) NOT NULL,
91 PRIMARY KEY ("id")
92);
93
94-- Daten Export vom Benutzer nicht ausgewählt
95
96
97-- Exportiere Struktur von Tabelle Inventarisierung.Hardware
98CREATE TABLE IF NOT EXISTS "Hardware" (
99 "id" INT NOT NULL,
100 "kategorieId" INT NOT NULL,
101 "austausch" TINYINT NOT NULL,
102 "name" NVARCHAR(255) NOT NULL,
103 PRIMARY KEY ("id")
104);
105
106-- Daten Export vom Benutzer nicht ausgewählt
107
108
109-- Exportiere Struktur von Tabelle Inventarisierung.HardwareGeräte
110CREATE TABLE IF NOT EXISTS "HardwareGeräte" (
111 "hardwareId" INT NOT NULL,
112 "geräteId" INT NOT NULL,
113 "austauschDatum" DATE NULL
114);
115
116-- Daten Export vom Benutzer nicht ausgewählt
117
118
119-- Exportiere Struktur von Tabelle Inventarisierung.Kategorie
120CREATE TABLE IF NOT EXISTS "Kategorie" (
121 "id" INT NOT NULL,
122 "name" NVARCHAR(255) NOT NULL,
123 "beschreibung" NVARCHAR(255) NOT NULL,
124 PRIMARY KEY ("id")
125);
126
127-- Daten Export vom Benutzer nicht ausgewählt
128
129
130-- Exportiere Struktur von Tabelle Inventarisierung.Lehrer
131CREATE TABLE IF NOT EXISTS "Lehrer" (
132 "id" INT NOT NULL,
133 "vorname" NVARCHAR(255) NOT NULL,
134 "name" NVARCHAR(255) NOT NULL,
135 "email" NVARCHAR(255) NOT NULL,
136 "raumId" INT NOT NULL,
137 "raumbetreuer" TINYINT NOT NULL,
138 PRIMARY KEY ("id")
139);
140
141-- Daten Export vom Benutzer nicht ausgewählt
142
143
144-- Exportiere Struktur von Tabelle Inventarisierung.Mangel
145CREATE TABLE IF NOT EXISTS "Mangel" (
146 "id" INT NOT NULL,
147 "datum" DATE NOT NULL,
148 "beschreibung" NVARCHAR(255) NOT NULL,
149 "geräteId" INT NOT NULL,
150 PRIMARY KEY ("id")
151);
152
153-- Daten Export vom Benutzer nicht ausgewählt
154
155
156-- Exportiere Struktur von Tabelle Inventarisierung.Räume
157CREATE TABLE IF NOT EXISTS "Räume" (
158 "id" INT NOT NULL,
159 "raumNr" INT NOT NULL,
160 PRIMARY KEY ("id")
161);
162
163-- Daten Export vom Benutzer nicht ausgewählt
164
165
166-- Exportiere Struktur von Tabelle Inventarisierung.RäumeLehrer
167CREATE TABLE IF NOT EXISTS "RäumeLehrer" (
168 "lehrerId" INT NOT NULL,
169 "raumId" INT NOT NULL
170);
171
172-- Daten Export vom Benutzer nicht ausgewählt
173
174
175-- Exportiere Struktur von Prozedur Inventarisierung.sp_alterdiagram
176DELIMITER //
177
178 CREATE PROCEDURE dbo.sp_alterdiagram
179 (
180 @diagramname sysname,
181 @owner_id int = null,
182 @version int,
183 @definition varbinary(max)
184 )
185 WITH EXECUTE AS 'dbo'
186 AS
187 BEGIN
188 set nocount on
189
190 declare @theId int
191 declare @retval int
192 declare @IsDbo int
193
194 declare @UIDFound int
195 declare @DiagId int
196 declare @ShouldChangeUID int
197
198 if(@diagramname is null)
199 begin
200 RAISERROR ('Invalid ARG', 16, 1)
201 return -1
202 end
203
204 execute as caller;
205 select @theId = DATABASE_PRINCIPAL_ID();
206 select @IsDbo = IS_MEMBER(N'db_owner');
207 if(@owner_id is null)
208 select @owner_id = @theId;
209 revert;
210
211 select @ShouldChangeUID = 0
212 select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
213
214 if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
215 begin
216 RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
217 return -3
218 end
219
220 if(@IsDbo <> 0)
221 begin
222 if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
223 begin
224 select @ShouldChangeUID = 1 ;
225 end
226 end
227
228 -- update dds data
229 update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;
230
231 -- change owner
232 if(@ShouldChangeUID = 1)
233 update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;
234
235 -- update dds version
236 if(@version is not null)
237 update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;
238
239 return 0
240 END
241 //
242DELIMITER ;
243
244
245-- Exportiere Struktur von Prozedur Inventarisierung.sp_creatediagram
246DELIMITER //
247
248 CREATE PROCEDURE dbo.sp_creatediagram
249 (
250 @diagramname sysname,
251 @owner_id int = null,
252 @version int,
253 @definition varbinary(max)
254 )
255 WITH EXECUTE AS 'dbo'
256 AS
257 BEGIN
258 set nocount on
259
260 declare @theId int
261 declare @retval int
262 declare @IsDbo int
263 declare @userName sysname
264 if(@version is null or @diagramname is null)
265 begin
266 RAISERROR (N'E_INVALIDARG', 16, 1);
267 return -1
268 end
269
270 execute as caller;
271 select @theId = DATABASE_PRINCIPAL_ID();
272 select @IsDbo = IS_MEMBER(N'db_owner');
273 revert;
274
275 if @owner_id is null
276 begin
277 select @owner_id = @theId;
278 end
279 else
280 begin
281 if @theId <> @owner_id
282 begin
283 if @IsDbo = 0
284 begin
285 RAISERROR (N'E_INVALIDARG', 16, 1);
286 return -1
287 end
288 select @theId = @owner_id
289 end
290 end
291 -- next 2 line only for test, will be removed after define name unique
292 if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
293 begin
294 RAISERROR ('The name is already used.', 16, 1);
295 return -2
296 end
297
298 insert into dbo.sysdiagrams(name, principal_id , version, definition)
299 VALUES(@diagramname, @theId, @version, @definition) ;
300
301 select @retval = @@IDENTITY
302 return @retval
303 END
304 //
305DELIMITER ;
306
307
308-- Exportiere Struktur von Prozedur Inventarisierung.sp_dropdiagram
309DELIMITER //
310
311 CREATE PROCEDURE dbo.sp_dropdiagram
312 (
313 @diagramname sysname,
314 @owner_id int = null
315 )
316 WITH EXECUTE AS 'dbo'
317 AS
318 BEGIN
319 set nocount on
320 declare @theId int
321 declare @IsDbo int
322
323 declare @UIDFound int
324 declare @DiagId int
325
326 if(@diagramname is null)
327 begin
328 RAISERROR ('Invalid value', 16, 1);
329 return -1
330 end
331
332 EXECUTE AS CALLER;
333 select @theId = DATABASE_PRINCIPAL_ID();
334 select @IsDbo = IS_MEMBER(N'db_owner');
335 if(@owner_id is null)
336 select @owner_id = @theId;
337 REVERT;
338
339 select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
340 if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
341 begin
342 RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
343 return -3
344 end
345
346 delete from dbo.sysdiagrams where diagram_id = @DiagId;
347
348 return 0;
349 END
350 //
351DELIMITER ;
352
353
354-- Exportiere Struktur von Prozedur Inventarisierung.sp_helpdiagramdefinition
355DELIMITER //
356
357 CREATE PROCEDURE dbo.sp_helpdiagramdefinition
358 (
359 @diagramname sysname,
360 @owner_id int = null
361 )
362 WITH EXECUTE AS N'dbo'
363 AS
364 BEGIN
365 set nocount on
366
367 declare @theId int
368 declare @IsDbo int
369 declare @DiagId int
370 declare @UIDFound int
371
372 if(@diagramname is null)
373 begin
374 RAISERROR (N'E_INVALIDARG', 16, 1);
375 return -1
376 end
377
378 execute as caller;
379 select @theId = DATABASE_PRINCIPAL_ID();
380 select @IsDbo = IS_MEMBER(N'db_owner');
381 if(@owner_id is null)
382 select @owner_id = @theId;
383 revert;
384
385 select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
386 if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
387 begin
388 RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
389 return -3
390 end
391
392 select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ;
393 return 0
394 END
395 //
396DELIMITER ;
397
398
399-- Exportiere Struktur von Prozedur Inventarisierung.sp_helpdiagrams
400DELIMITER //
401
402 CREATE PROCEDURE dbo.sp_helpdiagrams
403 (
404 @diagramname sysname = NULL,
405 @owner_id int = NULL
406 )
407 WITH EXECUTE AS N'dbo'
408 AS
409 BEGIN
410 DECLARE @user sysname
411 DECLARE @dboLogin bit
412 EXECUTE AS CALLER;
413 SET @user = USER_NAME();
414 SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
415 REVERT;
416 SELECT
417 [Database] = DB_NAME(),
418 [Name] = name,
419 [ID] = diagram_id,
420 [Owner] = USER_NAME(principal_id),
421 [OwnerID] = principal_id
422 FROM
423 sysdiagrams
424 WHERE
425 (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
426 (@diagramname IS NULL OR name = @diagramname) AND
427 (@owner_id IS NULL OR principal_id = @owner_id)
428 ORDER BY
429 4, 5, 1
430 END
431 //
432DELIMITER ;
433
434
435-- Exportiere Struktur von Prozedur Inventarisierung.sp_renamediagram
436DELIMITER //
437
438 CREATE PROCEDURE dbo.sp_renamediagram
439 (
440 @diagramname sysname,
441 @owner_id int = null,
442 @new_diagramname sysname
443
444 )
445 WITH EXECUTE AS 'dbo'
446 AS
447 BEGIN
448 set nocount on
449 declare @theId int
450 declare @IsDbo int
451
452 declare @UIDFound int
453 declare @DiagId int
454 declare @DiagIdTarg int
455 declare @u_name sysname
456 if((@diagramname is null) or (@new_diagramname is null))
457 begin
458 RAISERROR ('Invalid value', 16, 1);
459 return -1
460 end
461
462 EXECUTE AS CALLER;
463 select @theId = DATABASE_PRINCIPAL_ID();
464 select @IsDbo = IS_MEMBER(N'db_owner');
465 if(@owner_id is null)
466 select @owner_id = @theId;
467 REVERT;
468
469 select @u_name = USER_NAME(@owner_id)
470
471 select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
472 if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
473 begin
474 RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
475 return -3
476 end
477
478 -- if((@u_name is not null) and (@new_diagramname = @diagramname)) -- nothing will change
479 -- return 0;
480
481 if(@u_name is null)
482 select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
483 else
484 select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
485
486 if((@DiagIdTarg is not null) and @DiagId <> @DiagIdTarg)
487 begin
488 RAISERROR ('The name is already used.', 16, 1);
489 return -2
490 end
491
492 if(@u_name is null)
493 update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
494 else
495 update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
496 return 0
497 END
498 //
499DELIMITER ;
500
501
502-- Exportiere Struktur von Prozedur Inventarisierung.sp_upgraddiagrams
503DELIMITER //
504
505 CREATE PROCEDURE dbo.sp_upgraddiagrams
506 AS
507 BEGIN
508 IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL
509 return 0;
510
511 CREATE TABLE dbo.sysdiagrams
512 (
513 name sysname NOT NULL,
514 principal_id int NOT NULL, -- we may change it to varbinary(85)
515 diagram_id int PRIMARY KEY IDENTITY,
516 version int,
517
518 definition varbinary(max)
519 CONSTRAINT UK_principal_name UNIQUE
520 (
521 principal_id,
522 name
523 )
524 );
525
526
527 /* Add this if we need to have some form of extended properties for diagrams */
528 /*
529 IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL
530 BEGIN
531 CREATE TABLE dbo.sysdiagram_properties
532 (
533 diagram_id int,
534 name sysname,
535 value varbinary(max) NOT NULL
536 )
537 END
538 */
539
540 IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL
541 begin
542 insert into dbo.sysdiagrams
543 (
544 [name],
545 [principal_id],
546 [version],
547 [definition]
548 )
549 select
550 convert(sysname, dgnm.[uvalue]),
551 DATABASE_PRINCIPAL_ID(N'dbo'), -- will change to the sid of sa
552 0, -- zero for old format, dgdef.[version],
553 dgdef.[lvalue]
554 from dbo.[dtproperties] dgnm
555 inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid]
556 inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid]
557
558 where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_'
559 return 2;
560 end
561 return 1;
562 END
563 //
564DELIMITER ;
565
566
567-- Exportiere Struktur von Tabelle Inventarisierung.sysdiagrams
568CREATE TABLE IF NOT EXISTS "sysdiagrams" (
569 "name" NVARCHAR(128) NOT NULL,
570 "principal_id" INT NOT NULL,
571 "diagram_id" INT NOT NULL,
572 "version" INT NULL,
573 "definition" VARBINARY(-1) NULL,
574 PRIMARY KEY ("diagram_id"),
575 UNIQUE KEY ("principal_id","name")
576);
577
578-- Daten Export vom Benutzer nicht ausgewählt
579/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
580/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
581/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;