· 9 years ago · Dec 23, 2016, 12:54 PM
1/*==============================================================*/
2/* DBMS name: SAP SQL Anywhere 16 */
3/* Created on: 21-12-2016 15:30:14 */
4/*==============================================================*/
5use master
6go
7
8drop database db_handleiding
9go
10
11create database db_handleiding
12go
13
14use db_handleiding
15go
16
17if exists (select 1
18 from sysobjects
19 where id = object_id('APPLICATION_ELEMENTS')
20 and type = 'U')
21 drop table APPLICATION_ELEMENTS
22go
23
24if exists (select 1
25 from sysindexes
26 where id = object_id('APPLICATION_TEXT')
27 and name = 'APPLICATION_TEXT_ON_PAGE_FK'
28 and indid > 0
29 and indid < 255)
30 drop index APPLICATION_TEXT.APPLICATION_TEXT_ON_PAGE_FK
31go
32
33if exists (select 1
34 from sysindexes
35 where id = object_id('APPLICATION_TEXT')
36 and name = 'ELEMENTS_CONTAINS_TEXT_FK'
37 and indid > 0
38 and indid < 255)
39 drop index APPLICATION_TEXT.ELEMENTS_CONTAINS_TEXT_FK
40go
41
42if exists (select 1
43 from sysindexes
44 where id = object_id('APPLICATION_TEXT')
45 and name = 'APPLICATION_TEXT_IN_LANGUAGE_FK'
46 and indid > 0
47 and indid < 255)
48 drop index APPLICATION_TEXT.APPLICATION_TEXT_IN_LANGUAGE_FK
49go
50
51if exists (select 1
52 from sysobjects
53 where id = object_id('APPLICATION_TEXT')
54 and type = 'U')
55 drop table APPLICATION_TEXT
56go
57
58if exists (select 1
59 from sysobjects
60 where id = object_id('AUTHOR')
61 and type = 'U')
62 drop table AUTHOR
63go
64
65if exists (select 1
66 from sysindexes
67 where id = object_id('AUTHOR_WORKS_FOR_COMPANY')
68 and name = 'AUTHOR_WORKS_FOR_COMPANY_FK'
69 and indid > 0
70 and indid < 255)
71 drop index AUTHOR_WORKS_FOR_COMPANY.AUTHOR_WORKS_FOR_COMPANY_FK
72go
73
74if exists (select 1
75 from sysindexes
76 where id = object_id('AUTHOR_WORKS_FOR_COMPANY')
77 and name = 'WORKS_FOR_FK'
78 and indid > 0
79 and indid < 255)
80 drop index AUTHOR_WORKS_FOR_COMPANY.WORKS_FOR_FK
81go
82
83if exists (select 1
84 from sysobjects
85 where id = object_id('AUTHOR_WORKS_FOR_COMPANY')
86 and type = 'U')
87 drop table AUTHOR_WORKS_FOR_COMPANY
88go
89
90if exists (select 1
91 from sysobjects
92 where id = object_id('CATEGORY')
93 and type = 'U')
94 drop table CATEGORY
95go
96
97if exists (select 1
98 from sysobjects
99 where id = object_id('CHAPTER')
100 and type = 'U')
101 drop table CHAPTER
102go
103
104if exists (select 1
105 from sysindexes
106 where id = object_id('CHAPTER_IN_CHAPTER')
107 and name = 'CHAPTER_IN_CHAPTER_FK'
108 and indid > 0
109 and indid < 255)
110 drop index CHAPTER_IN_CHAPTER.CHAPTER_IN_CHAPTER_FK
111go
112
113if exists (select 1
114 from sysindexes
115 where id = object_id('CHAPTER_IN_CHAPTER')
116 and name = 'CHAPTER_CONTAINS_CHAPTER_FK'
117 and indid > 0
118 and indid < 255)
119 drop index CHAPTER_IN_CHAPTER.CHAPTER_CONTAINS_CHAPTER_FK
120go
121
122if exists (select 1
123 from sysobjects
124 where id = object_id('CHAPTER_IN_CHAPTER')
125 and type = 'U')
126 drop table CHAPTER_IN_CHAPTER
127go
128
129if exists (select 1
130 from sysindexes
131 where id = object_id('CHAPTER_IN_EDITION')
132 and name = 'EDITION_CONTAINS_CHAPTER_FK'
133 and indid > 0
134 and indid < 255)
135 drop index CHAPTER_IN_EDITION.EDITION_CONTAINS_CHAPTER_FK
136go
137
138if exists (select 1
139 from sysindexes
140 where id = object_id('CHAPTER_IN_EDITION')
141 and name = 'CHAPTER_IN_EDITION_FK'
142 and indid > 0
143 and indid < 255)
144 drop index CHAPTER_IN_EDITION.CHAPTER_IN_EDITION_FK
145go
146
147if exists (select 1
148 from sysobjects
149 where id = object_id('CHAPTER_IN_EDITION')
150 and type = 'U')
151 drop table CHAPTER_IN_EDITION
152go
153
154if exists (select 1
155 from sysobjects
156 where id = object_id('COMPANY')
157 and type = 'U')
158 drop table COMPANY
159go
160
161if exists (select 1
162 from sysobjects
163 where id = object_id('DOCUMENT')
164 and type = 'U')
165 drop table DOCUMENT
166go
167
168if exists (select 1
169 from sysindexes
170 where id = object_id('DOCUMENT_IN_CATEGORY')
171 and name = 'DOCUMENT_IN_CATEGORY_FK'
172 and indid > 0
173 and indid < 255)
174 drop index DOCUMENT_IN_CATEGORY.DOCUMENT_IN_CATEGORY_FK
175go
176
177if exists (select 1
178 from sysindexes
179 where id = object_id('DOCUMENT_IN_CATEGORY')
180 and name = 'IN_FK'
181 and indid > 0
182 and indid < 255)
183 drop index DOCUMENT_IN_CATEGORY.IN_FK
184go
185
186if exists (select 1
187 from sysobjects
188 where id = object_id('DOCUMENT_IN_CATEGORY')
189 and type = 'U')
190 drop table DOCUMENT_IN_CATEGORY
191go
192
193if exists (select 1
194 from sysindexes
195 where id = object_id('EDITION')
196 and name = 'EDITION_MADE_BY_COMPANY_FK'
197 and indid > 0
198 and indid < 255)
199 drop index EDITION.EDITION_MADE_BY_COMPANY_FK
200go
201
202if exists (select 1
203 from sysindexes
204 where id = object_id('EDITION')
205 and name = 'IS_WRITTEN_IN_LANGUAGE_FK'
206 and indid > 0
207 and indid < 255)
208 drop index EDITION.IS_WRITTEN_IN_LANGUAGE_FK
209go
210
211if exists (select 1
212 from sysindexes
213 where id = object_id('EDITION')
214 and name = 'EDITION_FROM_DOCUMENT_FK'
215 and indid > 0
216 and indid < 255)
217 drop index EDITION.EDITION_FROM_DOCUMENT_FK
218go
219
220if exists (select 1
221 from sysobjects
222 where id = object_id('EDITION')
223 and type = 'U')
224 drop table EDITION
225go
226
227if exists (select 1
228 from sysindexes
229 where id = object_id('EDITION_MADE_BY_AUTHOR')
230 and name = 'EDITION_MADE_BY_AUTHOR_FK'
231 and indid > 0
232 and indid < 255)
233 drop index EDITION_MADE_BY_AUTHOR.EDITION_MADE_BY_AUTHOR_FK
234go
235
236if exists (select 1
237 from sysindexes
238 where id = object_id('EDITION_MADE_BY_AUTHOR')
239 and name = 'MADE_BY_FK'
240 and indid > 0
241 and indid < 255)
242 drop index EDITION_MADE_BY_AUTHOR.MADE_BY_FK
243go
244
245if exists (select 1
246 from sysobjects
247 where id = object_id('EDITION_MADE_BY_AUTHOR')
248 and type = 'U')
249 drop table EDITION_MADE_BY_AUTHOR
250go
251
252if exists (select 1
253 from sysindexes
254 where id = object_id('ELEMENT')
255 and name = 'ELEMENT_IS_ELEMENT_TYPE_FK'
256 and indid > 0
257 and indid < 255)
258 drop index ELEMENT.ELEMENT_IS_ELEMENT_TYPE_FK
259go
260
261if exists (select 1
262 from sysobjects
263 where id = object_id('ELEMENT')
264 and type = 'U')
265 drop table ELEMENT
266go
267
268if exists (select 1
269 from sysindexes
270 where id = object_id('ELEMENT_IN_CHAPTER')
271 and name = 'CHAPTER_CONTAINS_ELEMENT_FK'
272 and indid > 0
273 and indid < 255)
274 drop index ELEMENT_IN_CHAPTER.CHAPTER_CONTAINS_ELEMENT_FK
275go
276
277if exists (select 1
278 from sysindexes
279 where id = object_id('ELEMENT_IN_CHAPTER')
280 and name = 'ELEMENT_IN_CHAPTER_FK'
281 and indid > 0
282 and indid < 255)
283 drop index ELEMENT_IN_CHAPTER.ELEMENT_IN_CHAPTER_FK
284go
285
286if exists (select 1
287 from sysobjects
288 where id = object_id('ELEMENT_IN_CHAPTER')
289 and type = 'U')
290 drop table ELEMENT_IN_CHAPTER
291go
292
293if exists (select 1
294 from sysobjects
295 where id = object_id('ELEMENT_TYPE')
296 and type = 'U')
297 drop table ELEMENT_TYPE
298go
299
300if exists (select 1
301 from sysobjects
302 where id = object_id('LANGUAGE')
303 and type = 'U')
304 drop table LANGUAGE
305go
306
307if exists (select 1
308 from sysobjects
309 where id = object_id('PAGE')
310 and type = 'U')
311 drop table PAGE
312go
313
314if exists (select 1
315 from sysobjects
316 where id = object_id('SOURCE')
317 and type = 'U')
318 drop table SOURCE
319go
320
321if exists (select 1
322 from sysindexes
323 where id = object_id('SOURCE_FROM_ELEMENT')
324 and name = 'SOURCE_FROM_ELEMENT_FK'
325 and indid > 0
326 and indid < 255)
327 drop index SOURCE_FROM_ELEMENT.SOURCE_FROM_ELEMENT_FK
328go
329
330if exists (select 1
331 from sysindexes
332 where id = object_id('SOURCE_FROM_ELEMENT')
333 and name = 'HAS_FK'
334 and indid > 0
335 and indid < 255)
336 drop index SOURCE_FROM_ELEMENT.HAS_FK
337go
338
339if exists (select 1
340 from sysobjects
341 where id = object_id('SOURCE_FROM_ELEMENT')
342 and type = 'U')
343 drop table SOURCE_FROM_ELEMENT
344go
345
346if exists(select 1 from systypes where name='APPLICATION_TEXT')
347 drop type APPLICATION_TEXT
348go
349
350if exists(select 1 from systypes where name='AUTHOR_NAME')
351 drop type AUTHOR_NAME
352go
353
354if exists(select 1 from systypes where name='CHAPTER_KEY')
355 drop type CHAPTER_KEY
356go
357
358if exists(select 1 from systypes where name='COMPANY_NAME')
359 drop type COMPANY_NAME
360go
361
362if exists(select 1 from systypes where name='CONTENT_TEXT')
363 drop type CONTENT_TEXT
364go
365
366if exists(select 1 from systypes where name='DESCRIPTION')
367 drop type DESCRIPTION
368go
369
370if exists(select 1 from systypes where name='DOCUMENT_NR')
371 drop type DOCUMENT_NR
372go
373
374if exists(select 1 from systypes where name='EDITION_KEY')
375 drop type EDITION_KEY
376go
377
378if exists(select 1 from systypes where name='ELEMENT_KEY')
379 drop type ELEMENT_KEY
380go
381
382if exists(select 1 from systypes where name='ELEMENT_TAG')
383 drop type ELEMENT_TAG
384go
385
386if exists(select 1 from systypes where name='ELEMENT_TYPE')
387 drop type ELEMENT_TYPE
388go
389
390if exists(select 1 from systypes where name='IMAGE_LINK')
391 drop type IMAGE_LINK
392go
393
394if exists(select 1 from systypes where name='LANGUAGE_NAME')
395 drop type LANGUAGE_NAME
396go
397
398if exists(select 1 from systypes where name='LANGUAGE_SHORT')
399 drop type LANGUAGE_SHORT
400go
401
402if exists(select 1 from systypes where name='PAGE')
403 drop type PAGE
404go
405
406if exists(select 1 from systypes where name='REFERENCE_TEXT')
407 drop type REFERENCE_TEXT
408go
409
410if exists(select 1 from systypes where name='REFERENCE_TEXT_SHORT')
411 drop type REFERENCE_TEXT_SHORT
412go
413
414if exists(select 1 from systypes where name='RELEASE_DATE')
415 drop type RELEASE_DATE
416go
417
418if exists(select 1 from systypes where name='SEQ_NR')
419 drop type SEQ_NR
420go
421
422if exists(select 1 from systypes where name='STYLE_TEXT')
423 drop type STYLE_TEXT
424go
425
426if exists(select 1 from systypes where name='TITLE')
427 drop type TITLE
428go
429
430if exists(select 1 from systypes where name='UPDATE_DATE')
431 drop type UPDATE_DATE
432go
433
434if exists(select 1 from systypes where name='VERSION_NR')
435 drop type VERSION_NR
436go
437
438if exists(select 1 from systypes where name='VISIBILITY')
439 drop type VISIBILITY
440go
441
442/*==============================================================*/
443/* Domain: APPLICATION_TEXT */
444/*==============================================================*/
445create type APPLICATION_TEXT
446 from varchar(1024)
447go
448
449/*==============================================================*/
450/* Domain: AUTHOR_NAME */
451/*==============================================================*/
452create type AUTHOR_NAME
453 from varchar(128)
454go
455
456/*==============================================================*/
457/* Domain: CHAPTER_KEY */
458/*==============================================================*/
459create type CHAPTER_KEY
460 from bigint
461go
462
463/*==============================================================*/
464/* Domain: COMPANY_NAME */
465/*==============================================================*/
466create type COMPANY_NAME
467 from varchar(100)
468go
469
470/*==============================================================*/
471/* Domain: CONTENT_TEXT */
472/*==============================================================*/
473create type CONTENT_TEXT
474 from varchar(4096)
475go
476
477/*==============================================================*/
478/* Domain: DESCRIPTION */
479/*==============================================================*/
480create type DESCRIPTION
481 from varchar(256)
482go
483
484/*==============================================================*/
485/* Domain: DOCUMENT_NR */
486/*==============================================================*/
487create type DOCUMENT_NR
488 from int
489go
490
491/*==============================================================*/
492/* Domain: EDITION_KEY */
493/*==============================================================*/
494create type EDITION_KEY
495 from bigint
496go
497
498/*==============================================================*/
499/* Domain: ELEMENT_KEY */
500/*==============================================================*/
501create type ELEMENT_KEY
502 from bigint
503go
504
505/*==============================================================*/
506/* Domain: ELEMENT_TAG */
507/*==============================================================*/
508create type ELEMENT_TAG
509 from varchar(80)
510go
511
512/*==============================================================*/
513/* Domain: ELEMENT_TYPE */
514/*==============================================================*/
515create type ELEMENT_TYPE
516 from varchar(5)
517go
518
519/*==============================================================*/
520/* Domain: IMAGE_LINK */
521/*==============================================================*/
522create type IMAGE_LINK
523 from varchar(256)
524go
525
526/*==============================================================*/
527/* Domain: LANGUAGE_NAME */
528/*==============================================================*/
529create type LANGUAGE_NAME
530 from varchar(100)
531go
532
533/*==============================================================*/
534/* Domain: LANGUAGE_SHORT */
535/*==============================================================*/
536create type LANGUAGE_SHORT
537 from varchar(10)
538go
539
540/*==============================================================*/
541/* Domain: PAGE */
542/*==============================================================*/
543create type PAGE
544 from varchar(64)
545go
546
547/*==============================================================*/
548/* Domain: REFERENCE_TEXT */
549/*==============================================================*/
550create type REFERENCE_TEXT
551 from varchar(300)
552go
553
554/*==============================================================*/
555/* Domain: REFERENCE_TEXT_SHORT */
556/*==============================================================*/
557create type REFERENCE_TEXT_SHORT
558 from varchar(100)
559go
560
561/*==============================================================*/
562/* Domain: RELEASE_DATE */
563/*==============================================================*/
564create type RELEASE_DATE
565 from datetime
566go
567
568/*==============================================================*/
569/* Domain: SEQ_NR */
570/*==============================================================*/
571create type SEQ_NR
572 from int
573go
574
575/*==============================================================*/
576/* Domain: STYLE_TEXT */
577/*==============================================================*/
578create type STYLE_TEXT
579 from varchar(4096)
580go
581
582/*==============================================================*/
583/* Domain: TITLE */
584/*==============================================================*/
585create type TITLE
586 from varchar(128)
587go
588
589/*==============================================================*/
590/* Domain: UPDATE_DATE */
591/*==============================================================*/
592create type UPDATE_DATE
593 from datetime
594go
595
596/*==============================================================*/
597/* Domain: VERSION_NR */
598/*==============================================================*/
599create type VERSION_NR
600 from int
601go
602
603/*==============================================================*/
604/* Domain: VISIBILITY */
605/*==============================================================*/
606create type VISIBILITY
607 from bit
608go
609
610/*==============================================================*/
611/* Table: APPLICATION_ELEMENTS */
612/*==============================================================*/
613create table APPLICATION_ELEMENTS
614(
615 ELEMENT_TAG ELEMENT_TAG not null,
616 constraint PK_APPLICATION_ELEMENTS primary key clustered (ELEMENT_TAG)
617);
618
619/*==============================================================*/
620/* Table: APPLICATION_TEXT */
621/*==============================================================*/
622create table APPLICATION_TEXT
623(
624 LANGUAGE_ABBRIVIATION LANGUAGE_SHORT not null,
625 ELEMENT_TAG ELEMENT_TAG not null,
626 PAGE_NAME PAGE not null,
627 VALUE APPLICATION_TEXT not null,
628 constraint PK_APPLICATION_TEXT primary key clustered (LANGUAGE_ABBRIVIATION, ELEMENT_TAG, PAGE_NAME)
629);
630
631
632/*==============================================================*/
633/* Index: APPLICATION_TEXT_IN_LANGUAGE_FK */
634/*==============================================================*/
635create index APPLICATION_TEXT_IN_LANGUAGE_FK on APPLICATION_TEXT (
636LANGUAGE_ABBRIVIATION ASC
637);
638
639/*==============================================================*/
640/* Index: ELEMENTS_CONTAINS_TEXT_FK */
641/*==============================================================*/
642create index ELEMENTS_CONTAINS_TEXT_FK on APPLICATION_TEXT (
643ELEMENT_TAG ASC
644);
645
646/*==============================================================*/
647/* Index: APPLICATION_TEXT_ON_PAGE_FK */
648/*==============================================================*/
649create index APPLICATION_TEXT_ON_PAGE_FK on APPLICATION_TEXT (
650PAGE_NAME ASC
651);
652
653/*==============================================================*/
654/* Table: AUTHOR */
655/*==============================================================*/
656create table AUTHOR
657(
658 AUTHOR AUTHOR_NAME not null,
659 constraint PK_AUTHOR primary key clustered (AUTHOR)
660);
661
662
663/*==============================================================*/
664/* Table: AUTHOR_WORKS_FOR_COMPANY */
665/*==============================================================*/
666create table AUTHOR_WORKS_FOR_COMPANY
667(
668 COMPANY COMPANY_NAME not null,
669 AUTHOR AUTHOR_NAME not null,
670 constraint PK_AUTHOR_WORKS_FOR_COMPANY primary key clustered (COMPANY, AUTHOR)
671);
672
673
674/*==============================================================*/
675/* Index: WORKS_FOR_FK */
676/*==============================================================*/
677create index WORKS_FOR_FK on AUTHOR_WORKS_FOR_COMPANY (
678COMPANY ASC
679);
680
681/*==============================================================*/
682/* Index: AUTHOR_WORKS_FOR_COMPANY_FK */
683/*==============================================================*/
684create index AUTHOR_WORKS_FOR_COMPANY_FK on AUTHOR_WORKS_FOR_COMPANY (
685AUTHOR ASC
686);
687
688/*==============================================================*/
689/* Table: CATEGORY */
690/*==============================================================*/
691create table CATEGORY
692(
693 CATEGORY_TITLE TITLE not null,
694 constraint PK_CATEGORY primary key clustered (CATEGORY_TITLE)
695);
696
697/*==============================================================*/
698/* Index: CATEGORY_PK */
699/*==============================================================*/
700
701/*==============================================================*/
702/* Table: CHAPTER */
703/*==============================================================*/
704create table CHAPTER
705(
706 CHAPTER_TITLE TITLE not null,
707 CHAPTER_KEY CHAPTER_KEY not null IDENTITY,
708 constraint PK_CHAPTER primary key clustered (CHAPTER_KEY)
709);
710
711/*==============================================================*/
712/* Index: CHAPTER_PK */
713/*==============================================================*/
714
715
716/*==============================================================*/
717/* Table: CHAPTER_IN_CHAPTER */
718/*==============================================================*/
719create table CHAPTER_IN_CHAPTER
720(
721 CHAPTER_KEY CHAPTER_KEY not null,
722 CHA_CHAPTER_KEY CHAPTER_KEY not null,
723 CHAPTER_NR_IN_CHAPTER SEQ_NR not null,
724 constraint PK_CHAPTER_IN_CHAPTER primary key clustered (CHAPTER_KEY, CHA_CHAPTER_KEY)
725);
726
727
728/*==============================================================*/
729/* Index: CHAPTER_CONTAINS_CHAPTER_FK */
730/*==============================================================*/
731create index CHAPTER_CONTAINS_CHAPTER_FK on CHAPTER_IN_CHAPTER (
732CHAPTER_KEY ASC
733);
734
735/*==============================================================*/
736/* Index: CHAPTER_IN_CHAPTER_FK */
737/*==============================================================*/
738create index CHAPTER_IN_CHAPTER_FK on CHAPTER_IN_CHAPTER (
739CHA_CHAPTER_KEY ASC
740);
741
742/*==============================================================*/
743/* Table: CHAPTER_IN_EDITION */
744/*==============================================================*/
745create table CHAPTER_IN_EDITION
746(
747 CHAPTER_KEY CHAPTER_KEY not null,
748 EDITION_KEY ELEMENT_KEY not null,
749 CHAPTER_NR_IN_EDITION SEQ_NR not null,
750 constraint PK_CHAPTER_IN_EDITION primary key clustered (CHAPTER_KEY, EDITION_KEY),
751 constraint UC_CHAPTER_KEY_EDITION_KEY_CHAPTER_NR UNIQUE (CHAPTER_KEY, EDITION_KEY, CHAPTER_NR_IN_EDITION)
752);
753
754
755
756/*==============================================================*/
757/* Index: CHAPTER_IN_EDITION_FK */
758/*==============================================================*/
759create index CHAPTER_IN_EDITION_FK on CHAPTER_IN_EDITION (
760CHAPTER_KEY ASC
761);
762
763/*==============================================================*/
764/* Index: EDITION_CONTAINS_CHAPTER_FK */
765/*==============================================================*/
766create index EDITION_CONTAINS_CHAPTER_FK on CHAPTER_IN_EDITION (
767EDITION_KEY ASC
768);
769
770/*==============================================================*/
771/* Table: COMPANY */
772/*==============================================================*/
773create table COMPANY
774(
775 COMPANY COMPANY_NAME not null,
776 constraint PK_COMPANY primary key clustered (COMPANY)
777);
778
779
780
781/*==============================================================*/
782/* Table: DOCUMENT */
783/*==============================================================*/
784create table DOCUMENT
785(
786 DOCUMENT_NR DOCUMENT_NR not null IDENTITY,
787 MAIN_TITLE TITLE not null,
788 STYLE STYLE_TEXT null,
789 constraint PK_DOCUMENT primary key clustered (DOCUMENT_NR),
790 constraint UC_DOCUMENT_NR_MAIN_TITLE UNIQUE (DOCUMENT_NR, MAIN_TITLE)
791);
792
793
794/*==============================================================*/
795/* Table: DOCUMENT_IN_CATEGORY */
796/*==============================================================*/
797create table DOCUMENT_IN_CATEGORY
798(
799 CATEGORY_TITLE TITLE not null,
800 DOCUMENT_NR DOCUMENT_NR not null,
801 constraint PK_DOCUMENT_IN_CATEGORY primary key clustered (CATEGORY_TITLE, DOCUMENT_NR)
802);
803
804
805
806/*==============================================================*/
807/* Index: IN_FK */
808/*==============================================================*/
809create index IN_FK on DOCUMENT_IN_CATEGORY (
810CATEGORY_TITLE ASC
811);
812
813/*==============================================================*/
814/* Index: DOCUMENT_IN_CATEGORY_FK */
815/*==============================================================*/
816create index DOCUMENT_IN_CATEGORY_FK on DOCUMENT_IN_CATEGORY (
817DOCUMENT_NR ASC
818);
819
820/*==============================================================*/
821/* Table: EDITION */
822/*==============================================================*/
823create table EDITION
824(
825 DOCUMENT_NR DOCUMENT_NR not null,
826 LANGUAGE_ABBRIVIATION LANGUAGE_SHORT not null,
827 RELEASE_DATE RELEASE_DATE not null,
828 EDITION_TITLE TITLE not null,
829 VERSION_NR VERSION_NR null,
830 INVISIBLE VISIBILITY not null,
831 COMPANY COMPANY_NAME null,
832 LAST_UPDATE_DATE UPDATE_DATE not null,
833 EDITION_KEY EDITION_KEY not null IDENTITY,
834 constraint PK_EDITION primary key clustered (EDITION_KEY),
835 constraint UC_DOCUMENT_NR_LANGUAGE_RELEASE_DATE UNIQUE (DOCUMENT_NR, LANGUAGE_ABBRIVIATION, RELEASE_DATE)
836);
837
838/*==============================================================*/
839/* Index: EDITION_FROM_DOCUMENT_FK */
840/*==============================================================*/
841create index EDITION_FROM_DOCUMENT_FK on EDITION (
842DOCUMENT_NR ASC
843);
844
845/*==============================================================*/
846/* Index: IS_WRITTEN_IN_LANGUAGE_FK */
847/*==============================================================*/
848create index IS_WRITTEN_IN_LANGUAGE_FK on EDITION (
849LANGUAGE_ABBRIVIATION ASC
850);
851
852/*==============================================================*/
853/* Index: EDITION_MADE_BY_COMPANY_FK */
854/*==============================================================*/
855create index EDITION_MADE_BY_COMPANY_FK on EDITION (
856COMPANY ASC
857);
858
859/*==============================================================*/
860/* Table: EDITION_MADE_BY_AUTHOR */
861/*==============================================================*/
862create table EDITION_MADE_BY_AUTHOR
863(
864 EDITION_KEY ELEMENT_KEY not null,
865 AUTHOR AUTHOR_NAME not null,
866 constraint PK_EDITION_MADE_BY_AUTHOR primary key clustered (EDITION_KEY, AUTHOR)
867);
868
869
870/*==============================================================*/
871/* Index: MADE_BY_FK */
872/*==============================================================*/
873create index MADE_BY_FK on EDITION_MADE_BY_AUTHOR (
874EDITION_KEY ASC
875);
876
877/*==============================================================*/
878/* Index: EDITION_MADE_BY_AUTHOR_FK */
879/*==============================================================*/
880create index EDITION_MADE_BY_AUTHOR_FK on EDITION_MADE_BY_AUTHOR (
881AUTHOR ASC
882);
883
884/*==============================================================*/
885/* Table: ELEMENT */
886/*==============================================================*/
887create table ELEMENT
888(
889 ELEMENT_KEY ELEMENT_KEY not null IDENTITY,
890 TYPE ELEMENT_TYPE not null,
891 IMAGE_DESCRIPTION DESCRIPTION null,
892 IMAGE_LINK IMAGE_LINK null,
893 ALT_TEXT_IMAGE DESCRIPTION null,
894 CONTENT CONTENT_TEXT null,
895 ALT_TEXT_TABLE DESCRIPTION null,
896 TABLE_DESCRIPTION DESCRIPTION null,
897 TABLE_CONTENT CONTENT_TEXT null,
898 constraint PK_ELEMENT primary key clustered (ELEMENT_KEY)
899);
900
901
902/*==============================================================*/
903/* Index: ELEMENT_IS_ELEMENT_TYPE_FK */
904/*==============================================================*/
905create index ELEMENT_IS_ELEMENT_TYPE_FK on ELEMENT (
906TYPE ASC
907);
908
909/*==============================================================*/
910/* Table: ELEMENT_IN_CHAPTER */
911/*==============================================================*/
912create table ELEMENT_IN_CHAPTER
913(
914 ELEMENT_KEY ELEMENT_KEY not null,
915 CHAPTER_KEY CHAPTER_KEY not null,
916 ELEMENT_NR SEQ_NR not null,
917 constraint PK_ELEMENT_IN_CHAPTER primary key clustered (ELEMENT_KEY, CHAPTER_KEY)
918);
919
920
921/*==============================================================*/
922/* Index: ELEMENT_IN_CHAPTER_FK */
923/*==============================================================*/
924create index ELEMENT_IN_CHAPTER_FK on ELEMENT_IN_CHAPTER (
925ELEMENT_KEY ASC
926);
927
928/*==============================================================*/
929/* Index: CHAPTER_CONTAINS_ELEMENT_FK */
930/*==============================================================*/
931create index CHAPTER_CONTAINS_ELEMENT_FK on ELEMENT_IN_CHAPTER (
932CHAPTER_KEY ASC
933);
934
935/*==============================================================*/
936/* Table: ELEMENT_TYPE */
937/*==============================================================*/
938create table ELEMENT_TYPE
939(
940 TYPE ELEMENT_TYPE not null,
941 constraint PK_ELEMENT_TYPE primary key clustered (TYPE)
942);
943
944
945/*==============================================================*/
946/* Table: LANGUAGE */
947/*==============================================================*/
948create table LANGUAGE
949(
950 LANGUAGE_NAME LANGUAGE_NAME not null,
951 LANGUAGE_ABBRIVIATION LANGUAGE_SHORT not null,
952 constraint PK_LANGUAGE primary key clustered (LANGUAGE_ABBRIVIATION)
953);
954
955
956/*==============================================================*/
957/* Table: PAGE */
958/*==============================================================*/
959create table PAGE
960(
961 PAGE_NAME PAGE not null,
962 constraint PK_PAGE primary key clustered (PAGE_NAME)
963);
964
965
966/*==============================================================*/
967/* Table: SOURCE */
968/*==============================================================*/
969create table SOURCE
970(
971 TEXT_REFERENCE REFERENCE_TEXT_SHORT not null,
972 REFERENCE_LIST_TEXT REFERENCE_TEXT not null,
973 constraint PK_SOURCE primary key clustered (REFERENCE_LIST_TEXT)
974);
975
976/*==============================================================*/
977/* Table: SOURCE_FROM_ELEMENT */
978/*==============================================================*/
979create table SOURCE_FROM_ELEMENT
980(
981 REFERENCE_LIST_TEXT REFERENCE_TEXT not null,
982 ELEMENT_KEY ELEMENT_KEY not null,
983 constraint PK_SOURCE_FROM_ELEMENT primary key clustered (REFERENCE_LIST_TEXT, ELEMENT_KEY)
984);
985
986
987/*==============================================================*/
988/* Index: HAS_FK */
989/*==============================================================*/
990create index HAS_FK on SOURCE_FROM_ELEMENT (
991REFERENCE_LIST_TEXT ASC
992);
993
994/*==============================================================*/
995/* Index: SOURCE_FROM_ELEMENT_FK */
996/*==============================================================*/
997create index SOURCE_FROM_ELEMENT_FK on SOURCE_FROM_ELEMENT (
998ELEMENT_KEY ASC
999);
1000
1001alter table APPLICATION_TEXT
1002 add constraint FK_APPLICAT_APPLICATI_LANGUAGE foreign key (LANGUAGE_ABBRIVIATION)
1003 references LANGUAGE (LANGUAGE_ABBRIVIATION)
1004 on update cascade
1005 on delete cascade;
1006
1007alter table APPLICATION_TEXT
1008 add constraint FK_APPLICAT_APPLICATI_PAGE foreign key (PAGE_NAME)
1009 references PAGE (PAGE_NAME)
1010 on update cascade
1011 on delete cascade;
1012
1013alter table APPLICATION_TEXT
1014 add constraint FK_APPLICAT_ELEMENTS__APPLICAT foreign key (ELEMENT_TAG)
1015 references APPLICATION_ELEMENTS (ELEMENT_TAG)
1016 on update cascade
1017 on delete cascade;
1018
1019alter table AUTHOR_WORKS_FOR_COMPANY
1020 add constraint FK_AUTHOR_W_AUTHOR_WO_AUTHOR foreign key (AUTHOR)
1021 references AUTHOR (AUTHOR)
1022 on update cascade
1023 on delete cascade;
1024
1025alter table AUTHOR_WORKS_FOR_COMPANY
1026 add constraint FK_AUTHOR_W_WORKS_FOR_COMPANY foreign key (COMPANY)
1027 references COMPANY (COMPANY)
1028 on update cascade
1029 on delete cascade;
1030
1031alter table CHAPTER_IN_CHAPTER
1032 add constraint FK_CHAPTER__CHAPTER_C_CHAPTER foreign key (CHAPTER_KEY)
1033 references CHAPTER (CHAPTER_KEY)
1034 on update cascade
1035 on delete cascade;
1036
1037alter table CHAPTER_IN_EDITION
1038 add constraint FK_CHAPTER__CHAPTER_I_CHAPTER foreign key (CHAPTER_KEY)
1039 references CHAPTER (CHAPTER_KEY)
1040 on update cascade
1041 on delete cascade;
1042
1043alter table CHAPTER_IN_EDITION
1044 add constraint FK_CHAPTER__EDITION_C_EDITION foreign key (EDITION_KEY)
1045 references EDITION (EDITION_KEY)
1046 on update cascade
1047 on delete cascade;
1048
1049alter table DOCUMENT_IN_CATEGORY
1050 add constraint FK_DOCUMENT_DOCUMENT__DOCUMENT foreign key (DOCUMENT_NR)
1051 references DOCUMENT (DOCUMENT_NR)
1052 on update cascade
1053 on delete cascade;
1054
1055alter table DOCUMENT_IN_CATEGORY
1056 add constraint FK_DOCUMENT_IN_CATEGORY foreign key (CATEGORY_TITLE)
1057 references CATEGORY (CATEGORY_TITLE)
1058 on update cascade
1059 on delete cascade;
1060
1061alter table EDITION
1062 add constraint FK_EDITION_EDITION_F_DOCUMENT foreign key (DOCUMENT_NR)
1063 references DOCUMENT (DOCUMENT_NR)
1064 on update cascade
1065 on delete cascade;
1066
1067alter table EDITION
1068 add constraint FK_EDITION_EDITION_M_COMPANY foreign key (COMPANY)
1069 references COMPANY (COMPANY)
1070 on update cascade
1071 on delete cascade;
1072
1073alter table EDITION
1074 add constraint FK_EDITION_IS_WRITTE_LANGUAGE foreign key (LANGUAGE_ABBRIVIATION)
1075 references LANGUAGE (LANGUAGE_ABBRIVIATION)
1076 on update cascade
1077 on delete cascade;
1078
1079alter table EDITION_MADE_BY_AUTHOR
1080 add constraint FK_EDITION__EDITION_M_AUTHOR foreign key (AUTHOR)
1081 references AUTHOR (AUTHOR)
1082 on update cascade
1083 on delete cascade;
1084
1085alter table EDITION_MADE_BY_AUTHOR
1086 add constraint FK_EDITION__MADE_BY_EDITION foreign key (EDITION_KEY)
1087 references EDITION (EDITION_KEY)
1088 on update cascade
1089 on delete cascade;
1090
1091alter table ELEMENT
1092 add constraint FK_ELEMENT_ELEMENT_I_ELEMENT_ foreign key (TYPE)
1093 references ELEMENT_TYPE (TYPE)
1094 on update cascade
1095 on delete cascade;
1096
1097alter table ELEMENT_IN_CHAPTER
1098 add constraint FK_ELEMENT__CHAPTER_C_CHAPTER foreign key (CHAPTER_KEY)
1099 references CHAPTER (CHAPTER_KEY)
1100 on update cascade
1101 on delete cascade;
1102
1103alter table ELEMENT_IN_CHAPTER
1104 add constraint FK_ELEMENT__ELEMENT_I_ELEMENT foreign key (ELEMENT_KEY)
1105 references ELEMENT (ELEMENT_KEY)
1106 on update cascade
1107 on delete cascade;
1108
1109alter table SOURCE_FROM_ELEMENT
1110 add constraint FK_SOURCE_F_HAS_SOURCE foreign key (REFERENCE_LIST_TEXT)
1111 references SOURCE (REFERENCE_LIST_TEXT)
1112 on update cascade
1113 on delete cascade;
1114
1115alter table SOURCE_FROM_ELEMENT
1116 add constraint FK_SOURCE_F_SOURCE_FR_ELEMENT foreign key (ELEMENT_KEY)
1117 references ELEMENT (ELEMENT_KEY)
1118 on update cascade
1119 on delete cascade;
1120
1121use master
1122go
1123
1124
1125
1126USE db_handleiding
1127GO
1128
1129
1130
1131ALTER DATABASE db_handleiding
1132SET RECURSIVE_TRIGGERS ON
1133GO
1134
1135/*
1136 1 Hierop volgende trigger die er voor zorgt dat als er een application text wordt verwijderd of geupdate naar een ander element, dat het oude application element wordt verwijderd als deze niet meer wordt gebruikt in application text.
1137*/
1138
1139CREATE trigger ELEMENT_TAG_CHECK on APPLICATION_TEXT
1140for update,delete
1141AS
1142if (@@ROWCOUNT=0 ) return --or not update(tablerow)
1143 SET NOCOUNT, XACT_ABORT ON
1144
1145 DECLARE @TranCounter INT;
1146 SET @TranCounter = @@TRANCOUNT;
1147 IF @TranCounter > 0 -- alternatief: IF (XACT_STATE()) = 1
1148 SAVE TRANSACTION ProcedureSave;
1149 ELSE -- alternatief: IF (XACT_STATE()) = 0
1150 BEGIN TRANSACTION;
1151
1152 BEGIN TRY
1153 IF NOT EXISTS(SELECT 1 FROM Application_text WHERE element_tag = (SELECT d.element_tag from deleted d))
1154 --IF ((SELECT COUNT(*) FROM Application_text WHERE element_tag = (SELECT d.element_tag from deleted d)) = 0)
1155 BEGIN
1156 DELETE FROM Application_elements WHERE element_tag = (SELECT d.element_tag from deleted d)
1157 END
1158
1159 IF @TranCounter = 0 AND XACT_STATE() = 1
1160 COMMIT TRANSACTION;
1161 END TRY
1162 BEGIN CATCH
1163 IF @TranCounter = 0
1164 BEGIN
1165 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1166 END;
1167 ELSE
1168 BEGIN
1169 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1170 END;
1171 THROW
1172 END CATCH
1173GO
1174
1175
1176/*
1177 2 Hierop volgende trigger die er voor zorgt dat als er een Chapter in Edition text wordt verwijderd of ge update naar een ander element, dat de oude chapter wordt verwijderd als deze niet meer wordt gebruikt in chapterinedition.
1178*/
1179
1180
1181CREATE trigger CHAPTER_BEING_USED on CHAPTER_IN_EDITION
1182for update, delete
1183AS
1184if (@@ROWCOUNT=0 ) return --or not update(tablerow)
1185 SET NOCOUNT, XACT_ABORT ON
1186
1187 DECLARE @TranCounter INT;
1188 SET @TranCounter = @@TRANCOUNT;
1189 IF @TranCounter > 0 -- alternatief: IF (XACT_STATE()) = 1
1190 SAVE TRANSACTION ProcedureSave;
1191 ELSE -- alternatief: IF (XACT_STATE()) = 0
1192 BEGIN TRANSACTION;
1193
1194 BEGIN TRY
1195 --DECLARE @deleted_chapter_key INT = (SELECT d.chapter_key from deleted d)
1196
1197 -- check of het chapter dat is gedelete uit chapter_in_edition de laatste was in chapter_in_edition
1198 IF NOT EXISTS(SELECT 1 FROM CHAPTER_IN_EDITION WHERE chapter_key = (SELECT d.chapter_key from deleted d))
1199 --IF ((SELECT COUNT(*) FROM CHAPTER_IN_EDITION WHERE chapter_key = @deleted_chapter_key) = 0)
1200 BEGIN
1201 -- delete het chapter
1202 DELETE FROM CHAPTER WHERE chapter_key = (SELECT d.chapter_key from deleted d)
1203 END
1204
1205 IF @TranCounter = 0 AND XACT_STATE() = 1
1206 COMMIT TRANSACTION;
1207 END TRY
1208 BEGIN CATCH
1209 IF @TranCounter = 0
1210 BEGIN
1211 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1212 END;
1213 ELSE
1214 BEGIN
1215 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1216 END;
1217 THROW
1218 END CATCH
1219GO
1220
1221
1222
1223/*
1224 3 Trigger als er niet meer wordt verwezen naar het element in elementinchapter, verwijder het element.
1225*/
1226
1227
1228CREATE trigger ELEMENT_BEING_USED on ELEMENT_IN_CHAPTER
1229for update, delete
1230AS
1231if (@@ROWCOUNT=0 ) return --or not update(tablerow)
1232 SET NOCOUNT, XACT_ABORT ON
1233
1234 DECLARE @TranCounter INT;
1235 SET @TranCounter = @@TRANCOUNT;
1236 IF @TranCounter > 0 -- alternatief: IF (XACT_STATE()) = 1
1237 SAVE TRANSACTION ProcedureSave;
1238 ELSE -- alternatief: IF (XACT_STATE()) = 0
1239 BEGIN TRANSACTION;
1240
1241 BEGIN TRY
1242
1243 --DECLARE @deleted_element_key INT = (SELECT d.element_key from deleted d)
1244
1245 IF NOT EXISTS(SELECT 1 FROM ELEMENT_IN_CHAPTER WHERE element_key = (SELECT d.element_key from deleted d))
1246 --IF ((SELECT COUNT(*) FROM ELEMENT_IN_CHAPTER WHERE element_key = @deleted_element_key) = 0)
1247 BEGIN
1248 DELETE FROM ELEMENT WHERE element_key = (SELECT d.element_key from deleted d)
1249 END
1250
1251 IF @TranCounter = 0 AND XACT_STATE() = 1
1252 COMMIT TRANSACTION;
1253 END TRY
1254 BEGIN CATCH
1255 IF @TranCounter = 0
1256 BEGIN
1257 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1258 END;
1259 ELSE
1260 BEGIN
1261 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1262 END;
1263 THROW
1264 END CATCH
1265GO
1266
1267/*
1268 4 Trigger die hieruit voorkomt, als er een chapter wordt verwijderd uit chapterinchapter of chapterinedition, chapter.
1269*/
1270
1271CREATE trigger CHAPTER_OF_SUBCHAPTER on CHAPTER_IN_CHAPTER
1272for delete
1273AS
1274if (@@ROWCOUNT=0 ) return --or not update(tablerow)
1275 SET NOCOUNT, XACT_ABORT ON
1276
1277 DECLARE @TranCounter INT;
1278 SET @TranCounter = @@TRANCOUNT;
1279 IF @TranCounter > 0 -- alternatief: IF (XACT_STATE()) = 1
1280 SAVE TRANSACTION ProcedureSave;
1281 ELSE -- alternatief: IF (XACT_STATE()) = 0
1282 BEGIN TRANSACTION;
1283
1284 BEGIN TRY
1285
1286 --IF EXISTS (SELECT 1 FROM CHAPTER_IN_CHAPTER WHERE CHA_CHAPTER_KEY = (SELECT d.chapter_key from deleted d))
1287 --BEGIN
1288 -- DELETE FROM CHAPTER_IN_CHAPTER WHERE CHA_CHAPTER_KEY = (SELECT d.chapter_key from deleted d)
1289 IF NOT ((SELECT COUNT(*) FROM CHAPTER_IN_CHAPTER WHERE chapter_key = (SELECT d.chapter_key from deleted d)) > 1)
1290 BEGIN
1291 IF ((SELECT COUNT(*) FROM CHAPTER_IN_EDITION WHERE chapter_key = (SELECT d.chapter_key from deleted d)) < 2)
1292 BEGIN
1293 DELETE FROM CHAPTER WHERE chapter_key = (SELECT d.chapter_key from deleted d)
1294 END
1295 END
1296 --END
1297 IF @TranCounter = 0 AND XACT_STATE() = 1
1298 COMMIT TRANSACTION;
1299 END TRY
1300 BEGIN CATCH
1301 IF @TranCounter = 0
1302 BEGIN
1303 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1304 END;
1305 ELSE
1306 BEGIN
1307 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1308 END;
1309 THROW
1310 END CATCH
1311GO
1312
1313-- SP Application element - application text. Er moet een text worden meegegeven als er een element wordt aangemaakt. (Farid)
1314
1315CREATE PROCEDURE sph_addTextToElement
1316 @element_tag ELEMENT_TAG,
1317 @value APPLICATION_TEXT,
1318 @language_abbriviation LANGUAGE_SHORT
1319AS
1320 SET NOCOUNT, XACT_ABORT ON
1321
1322 DECLARE @TranCounter INT;
1323 SET @TranCounter = @@TRANCOUNT;
1324 IF @TranCounter > 0 -- alternatief: IF (XACT_STATE()) = 1
1325 SAVE TRANSACTION ProcedureSave;
1326 ELSE -- alternatief: IF (XACT_STATE()) = 0
1327 BEGIN TRANSACTION;
1328
1329 BEGIN TRY
1330
1331 INSERT INTO APPLICATION_ELEMENTS (ELEMENT_TAG)
1332 VALUES (
1333 @element_tag
1334 )
1335
1336 INSERT INTO APPLICATION_TEXT (ELEMENT_TAG, LANGUAGE_ABBRIVIATION, VALUE)
1337 VALUES (
1338 @element_tag,
1339 @language_abbriviation,
1340 @value
1341 )
1342
1343 IF @TranCounter = 0 AND XACT_STATE() = 1
1344 COMMIT TRANSACTION;
1345 END TRY
1346 BEGIN CATCH
1347 IF @TranCounter = 0
1348 BEGIN
1349 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1350 END;
1351 ELSE
1352 BEGIN
1353 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1354 END;
1355 THROW
1356 END CATCH
1357GO
1358
1359
1360-- Als het type image, table of text wordt gegeven moeten de bijbehorende velden ingevuld worden.
1361
1362ALTER TABLE ELEMENT
1363ADD CONSTRAINT CHK_TYPE_ADD_FIELD CHECK (
1364 (
1365 [TYPE] = 'table' AND [ALT_TEXT_TABLE] IS NOT NULL AND [TABLE_DESCRIPTION] IS NOT NULL AND [TABLE_CONTENT] IS NOT NULL
1366 AND [IMAGE_LINK] IS NULL AND [ALT_TEXT_IMAGE] IS NULL AND [IMAGE_DESCRIPTION] IS NULL AND [CONTENT] IS NULL
1367 )
1368 OR (
1369 [TYPE] = 'image' AND [IMAGE_LINK] IS NOT NULL AND [ALT_TEXT_IMAGE] IS NOT NULL AND [IMAGE_DESCRIPTION] IS NOT NULL
1370 AND [ALT_TEXT_TABLE] IS NULL AND [TABLE_DESCRIPTION] IS NULL AND [TABLE_CONTENT] IS NULL AND [CONTENT] IS NULL
1371 )
1372 OR (
1373 [TYPE] = 'text' AND [CONTENT] IS NOT NULL
1374 AND [IMAGE_LINK] IS NULL AND [ALT_TEXT_IMAGE] IS NULL AND [IMAGE_DESCRIPTION] IS NULL
1375 AND [ALT_TEXT_TABLE] IS NULL AND [TABLE_DESCRIPTION] IS NULL AND [TABLE_CONTENT] IS NULL
1376 )
1377)
1378
1379-- SP Chapter - Chapter in Edition. Er moet een chapterinedition worden meegegeven als er een chapter wordt aangemaakt of in chapterinchapter.
1380
1381IF OBJECT_ID('sp_chapter_used_in_chapterinedition') IS NOT NULL
1382 DROP PROCEDURE sp_chapter_used_in_chapterinedition
1383GO
1384
1385CREATE PROCEDURE sp_chapter_used_in_chapterinedition
1386@chapter_title VARCHAR(128),
1387@edition_key BIGINT,
1388@chapter_nr_in_edition INT
1389
1390AS
1391 SET NOCOUNT, XACT_ABORT ON
1392
1393 DECLARE @TranCounter INT;
1394 SET @TranCounter = @@TRANCOUNT;
1395
1396 IF @TranCounter > 0
1397 SAVE TRANSACTION ProcedureSave;
1398 ELSE
1399 BEGIN TRANSACTION;
1400
1401 BEGIN TRY
1402
1403 BEGIN
1404 INSERT INTO CHAPTER (chapter_title) VALUES (@chapter_title)
1405 INSERT INTO CHAPTER_IN_EDITION (chapter_key,edition_key,chapter_nr_in_edition) VALUES (SCOPE_IDENTITY(),@edition_key,@chapter_nr_in_edition)
1406 END
1407
1408 IF @TranCounter = 0 AND XACT_STATE() = 1
1409 COMMIT TRANSACTION;
1410 END TRY
1411 BEGIN CATCH
1412 IF @TranCounter = 0
1413 BEGIN
1414 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1415 END;
1416 ELSE
1417 BEGIN
1418 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1419 END;
1420 THROW
1421 END CATCH
1422GO
1423
1424
1425/* Stored Procedure Betreft tabellen: Chapter en ChapterinChapter
1426Als er een chapter wordt aangemaakt, moet deze chapter worden gebruikt in een ChapterinChapter.
1427Dus: ChapterinChapter moet ook worden aangemaakt */
1428
1429IF OBJECT_ID('sp_chapterinchapter_in_chapterinedition') IS NOT NULL
1430 DROP PROCEDURE sp_chapterinchapter_in_chapterinedition
1431GO
1432
1433CREATE PROCEDURE sp_chapterinchapter_in_chapterinedition
1434@chapter_title VARCHAR(128),
1435@cha_chapter_key BIGINT,
1436@chapter_nr_in_chapter INT
1437AS
1438 SET NOCOUNT, XACT_ABORT ON
1439
1440 DECLARE @TranCounter INT;
1441 SET @TranCounter = @@TRANCOUNT;
1442
1443 IF @TranCounter > 0
1444 SAVE TRANSACTION ProcedureSave;
1445 ELSE
1446 BEGIN TRANSACTION;
1447
1448 BEGIN TRY
1449
1450 BEGIN
1451 INSERT INTO CHAPTER (chapter_title) VALUES (@chapter_title)
1452 INSERT INTO CHAPTER_IN_CHAPTER(chapter_key,cha_chapter_key,chapter_nr_in_chapter) VALUES (SCOPE_IDENTITY(),@cha_chapter_key,@chapter_nr_in_chapter)
1453 END
1454
1455 IF @TranCounter = 0 AND XACT_STATE() = 1
1456 COMMIT TRANSACTION;
1457 END TRY
1458 BEGIN CATCH
1459 IF @TranCounter = 0
1460 BEGIN
1461 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1462 END;
1463 ELSE
1464 BEGIN
1465 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1466 END;
1467 THROW
1468 END CATCH
1469GO
1470
1471
1472/*
1473Stored Procedure Betreft tabellen: Element en ElementinChapter
1474Als er een Element wordt aangemaakt, moet deze element worden gebruikt in een ElementinChapter.
1475Dus: Element in Chapter moet ook worden aangemaakt
1476*/
1477IF OBJECT_ID('sp_element_in_elementinchapter') IS NOT NULL
1478 DROP PROCEDURE sp_element_in_elementinchapter
1479GO
1480
1481CREATE PROCEDURE sp_element_in_elementinchapter
1482@type VARCHAR(5),
1483@image_description VARCHAR(256),
1484@image_link VARCHAR(256),
1485@alt_text_image VARCHAR(256),
1486@content VARCHAR(4096),
1487@alt_text_table VARCHAR(256),
1488@table_description VARCHAR(256),
1489@table_content VARCHAR(4096),
1490@element_nr INT,
1491@chapter_key BIGINT
1492AS
1493 SET NOCOUNT, XACT_ABORT ON
1494
1495 DECLARE @TranCounter INT;
1496 SET @TranCounter = @@TRANCOUNT;
1497
1498 IF @TranCounter > 0
1499 SAVE TRANSACTION ProcedureSave;
1500 ELSE
1501 BEGIN TRANSACTION;
1502
1503 BEGIN TRY
1504
1505 BEGIN
1506 INSERT INTO ELEMENT ([type],image_description,image_link,alt_text_image,content,alt_text_table,table_description,table_content)
1507 VALUES (@type,@image_description,@image_link,@alt_text_image,@content,@alt_text_table,@table_description,@table_content)
1508 INSERT INTO ELEMENT_IN_CHAPTER(element_key,chapter_key,element_nr) VALUES (SCOPE_IDENTITY(),@chapter_key,@element_nr)
1509 END
1510
1511 IF @TranCounter = 0 AND XACT_STATE() = 1
1512 COMMIT TRANSACTION;
1513 END TRY
1514 BEGIN CATCH
1515 IF @TranCounter = 0
1516 BEGIN
1517 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1518 END;
1519 ELSE
1520 BEGIN
1521 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1522 END;
1523 THROW
1524 END CATCH
1525GO
1526
1527
1528/*
1529Stored Procedure Betreft tabellen: Document en DocumentInCategory
1530Als er een Document wordt aangemaakt, moet deze document een categorie bevatten in Category.
1531Dus:Document in Gategory moet ook worden aangemaakt
1532*/
1533
1534IF OBJECT_ID('sp_document_in_documentincategory') IS NOT NULL
1535 DROP PROCEDURE sp_document_in_documentincategory
1536GO
1537
1538CREATE PROCEDURE sp_document_in_documentincategory
1539@main_title VARCHAR(100),
1540@style VARCHAR(4096),
1541@category_title VARCHAR(128)
1542
1543AS
1544 SET NOCOUNT, XACT_ABORT ON
1545
1546 DECLARE @TranCounter INT;
1547 SET @TranCounter = @@TRANCOUNT;
1548
1549 IF @TranCounter > 0
1550 SAVE TRANSACTION ProcedureSave;
1551 ELSE
1552 BEGIN TRANSACTION;
1553
1554 BEGIN TRY
1555
1556 BEGIN
1557 INSERT INTO DOCUMENT (main_title,style) VALUES (@main_title,@style)
1558 INSERT INTO DOCUMENT_IN_CATEGORY (document_nr,category_title) VALUES (SCOPE_IDENTITY(),@category_title)
1559 END
1560
1561 IF @TranCounter = 0 AND XACT_STATE() = 1
1562 COMMIT TRANSACTION;
1563 END TRY
1564 BEGIN CATCH
1565 IF @TranCounter = 0
1566 BEGIN
1567 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1568 END;
1569 ELSE
1570 BEGIN
1571 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1572 END;
1573 THROW
1574 END CATCH
1575GO
1576
1577
1578/*
1579Trigger Betreft tabellen: Document en DocumentInCategory
1580Als er een DocumentinCategory wordt verwijderd en het is de laatste document in die category, geef foutmelding.
1581*/
1582
1583IF OBJECT_ID('trg_DocumentLastCategoryDeleted') IS NOT NULL
1584 DROP TRIGGER trg_DocumentLastCategoryDeleted
1585GO
1586
1587CREATE TRIGGER trg_DocumentLastCategoryDeleted
1588ON DOCUMENT_IN_CATEGORY
1589FOR UPDATE,DELETE
1590AS
1591if (@@ROWCOUNT=0 ) return --or not update(category_title)
1592 SET NOCOUNT, XACT_ABORT ON
1593
1594 DECLARE @TranCounter INT;
1595 SET @TranCounter = @@TRANCOUNT;
1596 IF @TranCounter > 0
1597 SAVE TRANSACTION ProcedureSave;
1598 ELSE
1599 BEGIN TRANSACTION;
1600
1601 BEGIN TRY
1602 IF EXISTS( SELECT 'failure'
1603 FROM deleted d
1604 WHERE NOT EXISTS(
1605 SELECT 1
1606 FROM document_in_category c
1607 WHERE c.document_nr=d.document_nr
1608 )
1609 )
1610 BEGIN
1611 ;THROW 50000, 'Dit document is de laatste document van deze categorie. Het is niet mogelijk om dit document te verwijderen.', 1
1612
1613 END
1614 IF @TranCounter = 0 AND XACT_STATE() = 1
1615 COMMIT TRANSACTION;
1616 END TRY
1617 BEGIN CATCH
1618 IF @TranCounter = 0
1619 BEGIN
1620 IF XACT_STATE() = 1 ROLLBACK TRANSACTION;
1621 END;
1622 ELSE
1623 BEGIN
1624 IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;
1625 END;
1626 THROW
1627 END CATCH
1628GO
1629
1630create function getNumberOfEditionsForChapter (@chapterNumber INT)
1631returns int
1632as
1633begin
1634declare @result INT
1635;WITH parents AS (
1636 SELECT CHAPTER_KEY, CHA_CHAPTER_KEY
1637 FROM CHAPTER_IN_CHAPTER
1638 WHERE CHAPTER_KEY = @chapterNumber
1639 UNION ALL
1640 SELECT c.CHAPTER_KEY,c.CHA_CHAPTER_KEY
1641 FROM CHAPTER_IN_CHAPTER c INNER JOIN
1642 parents p ON p.CHAPTER_KEY = c.CHA_CHAPTER_KEY
1643)
1644SELECT @result = count(distinct c.EDITION_KEY)
1645FROM parents p INNER JOIN CHAPTER_IN_EDITION c ON p.CHAPTER_KEY = c.CHAPTER_KEY
1646option(maxrecursion 10)
1647return @result
1648end
1649go
1650
1651create function getNumberOfEditionsForElement (@elementNumber INT)
1652returns int
1653as
1654begin
1655declare @result INT = (
1656SELECT sum(dbo.getNumberOfEditionsForChapter(e.CHAPTER_KEY))
1657FROM ELEMENT_IN_CHAPTER e
1658WHERE e.ELEMENT_KEY = @elementNumber
1659)
1660return @result
1661end
1662go
1663
1664create function getSequenceNumber(@chapterNumber int)
1665returns int
1666as
1667begin
1668declare @maxChapter int = (select max(chapter_nr_in_chapter) from CHAPTER_IN_CHAPTER WHERE chapter_key = @chapterNumber)
1669declare @maxElement int = (select max(element_nr) from ELEMENT_IN_CHAPTER WHERE chapter_key = @chapterNumber)
1670if @maxChapter > @maxElement
1671 return @maxChapter + 1
1672else if @maxChapter < @maxElement
1673 return @maxElement + 1
1674return @maxChapter + 1
1675end
1676go
1677
1678
1679use master
1680go