[r58]: tags / 1.2.1 / dspace-vireo / etc / database_schema.sql  Maximize  Restore  History

Download this file

995 lines (860 with data), 36.9 kB

  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
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
--
-- database_schema.sql
--
-- Version: $Revision: 2793 $
--
-- Date: $Date: 2008-03-07 11:33:32 -0600 (Fri, 07 Mar 2008) $
--
-- Copyright (c) 2002, Hewlett-Packard Company and Massachusetts
-- Institute of Technology. All rights reserved.
--
-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions are
-- met:
--
-- - Redistributions of source code must retain the above copyright
-- notice, this list of conditions and the following disclaimer.
--
-- - Redistributions in binary form must reproduce the above copyright
-- notice, this list of conditions and the following disclaimer in the
-- documentation and/or other materials provided with the distribution.
--
-- - Neither the name of the Hewlett-Packard Company nor the name of the
-- Massachusetts Institute of Technology nor the names of their
-- contributors may be used to endorse or promote products derived from
-- this software without specific prior written permission.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
-- HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
-- OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
-- ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
-- TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
-- USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
-- DAMAGE.
--
--
--
--
-- DSpace SQL schema
--
-- Authors: Peter Breton, Robert Tansley, David Stuve, Daniel Chudnov,
-- Richard Jones
--
-- This file is used as-is to initialize a database. Therefore,
-- table and view definitions must be ordered correctly.
--
-- Caution: THIS IS POSTGRESQL-SPECIFIC:
--
-- * SEQUENCES are used for automatic ID generation
-- * FUNCTION getnextid used for automatic ID generation
--
--
-- To convert to work with another database, you need to ensure
-- an SQL function 'getnextid', which takes a table name as an
-- argument, will return a safe new ID to use to create a new
-- row in that table.
-------------------------------------------------------
-- Function for obtaining new IDs.
--
-- * The argument is a table name
-- * It returns a new ID safe to use for that table
--
-- The function reads the next value from the sequence
-- 'tablename_seq'
-------------------------------------------------------
CREATE FUNCTION getnextid(VARCHAR(40)) RETURNS INTEGER AS
'SELECT CAST (nextval($1 || ''_seq'') AS INTEGER) AS RESULT;' LANGUAGE SQL;
-------------------------------------------------------
-- Sequences for creating new IDs (primary keys) for
-- tables. Each table must have a corresponding
-- sequence called 'tablename_seq'.
-------------------------------------------------------
CREATE SEQUENCE bitstreamformatregistry_seq;
CREATE SEQUENCE fileextension_seq;
CREATE SEQUENCE bitstream_seq;
CREATE SEQUENCE eperson_seq;
CREATE SEQUENCE epersongroup_seq;
CREATE SEQUENCE item_seq;
CREATE SEQUENCE bundle_seq;
CREATE SEQUENCE item2bundle_seq;
CREATE SEQUENCE bundle2bitstream_seq;
CREATE SEQUENCE dctyperegistry_seq;
CREATE SEQUENCE dcvalue_seq;
CREATE SEQUENCE community_seq;
CREATE SEQUENCE collection_seq;
CREATE SEQUENCE community2community_seq;
CREATE SEQUENCE community2collection_seq;
CREATE SEQUENCE collection2item_seq;
CREATE SEQUENCE resourcepolicy_seq;
CREATE SEQUENCE epersongroup2eperson_seq;
CREATE SEQUENCE handle_seq;
CREATE SEQUENCE workspaceitem_seq;
CREATE SEQUENCE workflowitem_seq;
CREATE SEQUENCE tasklistitem_seq;
CREATE SEQUENCE registrationdata_seq;
CREATE SEQUENCE subscription_seq;
CREATE SEQUENCE history_seq;
CREATE SEQUENCE historystate_seq;
CREATE SEQUENCE communities2item_seq;
CREATE SEQUENCE epersongroup2workspaceitem_seq;
CREATE SEQUENCE metadataschemaregistry_seq;
CREATE SEQUENCE metadatafieldregistry_seq;
CREATE SEQUENCE metadatavalue_seq;
CREATE SEQUENCE group2group_seq;
CREATE SEQUENCE group2groupcache_seq;
CREATE SEQUENCE harvestsettings_seq;
-------------------------------------------------------
-- BitstreamFormatRegistry table
-------------------------------------------------------
CREATE TABLE BitstreamFormatRegistry
(
bitstream_format_id INTEGER PRIMARY KEY,
mimetype VARCHAR(256),
short_description VARCHAR(128) UNIQUE,
description TEXT,
support_level INTEGER,
-- Identifies internal types
internal BOOL
);
-------------------------------------------------------
-- FileExtension table
-------------------------------------------------------
CREATE TABLE FileExtension
(
file_extension_id INTEGER PRIMARY KEY,
bitstream_format_id INTEGER REFERENCES BitstreamFormatRegistry(bitstream_format_id),
extension VARCHAR(16)
);
CREATE INDEX fe_bitstream_fk_idx ON FileExtension(bitstream_format_id);
-------------------------------------------------------
-- Bitstream table
-------------------------------------------------------
CREATE TABLE Bitstream
(
bitstream_id INTEGER PRIMARY KEY,
bitstream_format_id INTEGER REFERENCES BitstreamFormatRegistry(bitstream_format_id),
name VARCHAR(256),
size_bytes BIGINT,
checksum VARCHAR(64),
checksum_algorithm VARCHAR(32),
description TEXT,
user_format_description TEXT,
source VARCHAR(256),
internal_id VARCHAR(256),
deleted BOOL,
store_number INTEGER,
sequence_id INTEGER
);
CREATE INDEX bit_bitstream_fk_idx ON Bitstream(bitstream_format_id);
-------------------------------------------------------
-- EPerson table
-------------------------------------------------------
CREATE TABLE EPerson
(
eperson_id INTEGER PRIMARY KEY,
email VARCHAR(64) UNIQUE,
password VARCHAR(64),
firstname VARCHAR(64),
lastname VARCHAR(64),
can_log_in BOOL,
require_certificate BOOL,
self_registered BOOL,
last_active TIMESTAMP,
sub_frequency INTEGER,
phone VARCHAR(32),
netid VARCHAR(64),
language VARCHAR(64),
cn VARCHAR(1024),
edupersonorgdn VARCHAR(1024),
edupersonorgunitdn VARCHAR(1024),
edupersonaffiliation VARCHAR(1024),
tdlhomepostaladdress VARCHAR(1024),
initials VARCHAR(1024),
tdledupersongraduationsem VARCHAR(1024),
tdledupersonmajor VARCHAR(1024),
tdledupersonmajorcode VARCHAR(1024),
tdldepartmentname VARCHAR(1024)
);
-- index by email
CREATE INDEX eperson_email_idx ON EPerson(email);
-- index by netid
CREATE INDEX eperson_netid_idx ON EPerson(netid);
-------------------------------------------------------
-- EPersonGroup table
-------------------------------------------------------
CREATE TABLE EPersonGroup
(
eperson_group_id INTEGER PRIMARY KEY,
name VARCHAR(256) UNIQUE
);
------------------------------------------------------
-- Group2Group table, records group membership in other groups
------------------------------------------------------
CREATE TABLE Group2Group
(
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES EPersonGroup(eperson_group_id),
child_id INTEGER REFERENCES EPersonGroup(eperson_group_id)
);
CREATE INDEX g2g_parent_fk_idx ON Group2Group(parent_id);
CREATE INDEX g2g_child_fk_idx ON Group2Group(child_id);
------------------------------------------------------
-- Group2GroupCache table, is the 'unwound' hierarchy in
-- Group2Group. It explicitly names every parent child
-- relationship, even with nested groups. For example,
-- If Group2Group lists B is a child of A and C is a child of B,
-- this table will have entries for parent(A,B), and parent(B,C)
-- AND parent(A,C) so that all of the child groups of A can be
-- looked up in a single simple query
------------------------------------------------------
CREATE TABLE Group2GroupCache
(
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES EPersonGroup(eperson_group_id),
child_id INTEGER REFERENCES EPersonGroup(eperson_group_id)
);
CREATE INDEX g2gc_parent_fk_idx ON Group2Group(parent_id);
CREATE INDEX g2gc_child_fk_idx ON Group2Group(child_id);
-------------------------------------------------------
-- Item table
-------------------------------------------------------
CREATE TABLE Item
(
item_id INTEGER PRIMARY KEY,
submitter_id INTEGER REFERENCES EPerson(eperson_id),
in_archive BOOL,
withdrawn BOOL,
last_modified TIMESTAMP WITH TIME ZONE,
owning_collection INTEGER,
oai_id VARCHAR(256),
last_harvested TIMESTAMP WITH TIME ZONE
);
CREATE INDEX item_submitter_fk_idx ON Item(submitter_id);
-------------------------------------------------------
-- Bundle table
-------------------------------------------------------
CREATE TABLE Bundle
(
bundle_id INTEGER PRIMARY KEY,
name VARCHAR(16), -- ORIGINAL | THUMBNAIL | TEXT
primary_bitstream_id INTEGER REFERENCES Bitstream(bitstream_id)
);
CREATE INDEX bundle_primary_fk_idx ON Bundle(primary_bitstream_id);
-------------------------------------------------------
-- Item2Bundle table
-------------------------------------------------------
CREATE TABLE Item2Bundle
(
id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
bundle_id INTEGER REFERENCES Bundle(bundle_id)
);
-- index by item_id
CREATE INDEX item2bundle_item_idx on Item2Bundle(item_id);
CREATE INDEX item2bundle_bundle_fk_idx ON Item2Bundle(bundle_id);
-------------------------------------------------------
-- Bundle2Bitstream table
-------------------------------------------------------
CREATE TABLE Bundle2Bitstream
(
id INTEGER PRIMARY KEY,
bundle_id INTEGER REFERENCES Bundle(bundle_id),
bitstream_id INTEGER REFERENCES Bitstream(bitstream_id)
);
-- index by bundle_id
CREATE INDEX bundle2bitstream_bundle_idx ON Bundle2Bitstream(bundle_id);
CREATE INDEX bundle2bitstream_bitstream_fk_idx ON Bundle2Bitstream(bitstream_id);
-------------------------------------------------------
-- Metadata Tables and Sequences
-------------------------------------------------------
CREATE TABLE MetadataSchemaRegistry
(
metadata_schema_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('metadataschemaregistry_seq'),
namespace VARCHAR(256) UNIQUE,
short_id VARCHAR(32) UNIQUE
);
CREATE TABLE MetadataFieldRegistry
(
metadata_field_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('metadatafieldregistry_seq'),
metadata_schema_id INTEGER NOT NULL REFERENCES MetadataSchemaRegistry(metadata_schema_id),
element VARCHAR(64),
qualifier VARCHAR(64),
scope_note TEXT
);
CREATE TABLE MetadataValue
(
metadata_value_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('metadatavalue_seq'),
item_id INTEGER REFERENCES Item(item_id),
metadata_field_id INTEGER REFERENCES MetadataFieldRegistry(metadata_field_id),
text_value TEXT,
text_lang VARCHAR(24),
place INTEGER
);
-- Create a dcvalue view for backwards compatibilty
CREATE VIEW dcvalue AS
SELECT MetadataValue.metadata_value_id AS "dc_value_id", MetadataValue.item_id,
MetadataValue.metadata_field_id AS "dc_type_id", MetadataValue.text_value,
MetadataValue.text_lang, MetadataValue.place
FROM MetadataValue, MetadataFieldRegistry
WHERE MetadataValue.metadata_field_id = MetadataFieldRegistry.metadata_field_id
AND MetadataFieldRegistry.metadata_schema_id = 1;
-- An index for item_id - almost all access is based on
-- instantiating the item object, which grabs all values
-- related to that item
CREATE INDEX metadatavalue_item_idx ON MetadataValue(item_id);
CREATE INDEX metadatavalue_item_idx2 ON MetadataValue(item_id,metadata_field_id);
CREATE INDEX metadatavalue_field_fk_idx ON MetadataValue(metadata_field_id);
CREATE INDEX metadatafield_schema_idx ON MetadataFieldRegistry(metadata_schema_id);
-------------------------------------------------------
-- Community table
-------------------------------------------------------
CREATE TABLE Community
(
community_id INTEGER PRIMARY KEY,
name VARCHAR(128),
short_description VARCHAR(512),
introductory_text TEXT,
logo_bitstream_id INTEGER REFERENCES Bitstream(bitstream_id),
copyright_text TEXT,
side_bar_text TEXT
);
CREATE INDEX community_logo_fk_idx ON Community(logo_bitstream_id);
-------------------------------------------------------
-- Collection table
-------------------------------------------------------
CREATE TABLE Collection
(
collection_id INTEGER PRIMARY KEY,
name VARCHAR(128),
short_description VARCHAR(512),
introductory_text TEXT,
logo_bitstream_id INTEGER REFERENCES Bitstream(bitstream_id),
template_item_id INTEGER REFERENCES Item(item_id),
provenance_description TEXT,
license TEXT,
copyright_text TEXT,
side_bar_text TEXT,
workflow_step_1 INTEGER REFERENCES EPersonGroup( eperson_group_id ),
workflow_step_2 INTEGER REFERENCES EPersonGroup( eperson_group_id ),
workflow_step_3 INTEGER REFERENCES EPersonGroup( eperson_group_id ),
submitter INTEGER REFERENCES EPersonGroup( eperson_group_id ),
admin INTEGER REFERENCES EPersonGroup( eperson_group_id)
);
CREATE INDEX collection_logo_fk_idx ON Collection(logo_bitstream_id);
CREATE INDEX collection_template_fk_idx ON Collection(template_item_id);
CREATE INDEX collection_workflow1_fk_idx ON Collection(workflow_step_1);
CREATE INDEX collection_workflow2_fk_idx ON Collection(workflow_step_2);
CREATE INDEX collection_workflow3_fk_idx ON Collection(workflow_step_3);
CREATE INDEX collection_submitter_fk_idx ON Collection(submitter);
CREATE INDEX collection_admin_fk_idx ON Collection(admin);
-------------------------------------------------------
-- Community2Community table
-------------------------------------------------------
CREATE TABLE Community2Community
(
id INTEGER PRIMARY KEY,
parent_comm_id INTEGER REFERENCES Community(community_id),
child_comm_id INTEGER REFERENCES Community(community_id)
);
CREATE INDEX com2com_parent_fk_idx ON Community2Community(parent_comm_id);
CREATE INDEX com2com_child_fk_idx ON Community2Community(child_comm_id);
-------------------------------------------------------
-- Community2Collection table
-------------------------------------------------------
CREATE TABLE Community2Collection
(
id INTEGER PRIMARY KEY,
community_id INTEGER REFERENCES Community(community_id),
collection_id INTEGER REFERENCES Collection(collection_id)
);
-- Index on community ID
CREATE INDEX Community2Collection_community_id_idx ON Community2Collection(community_id);
-- Index on collection ID
CREATE INDEX Community2Collection_collection_id_idx ON Community2Collection(collection_id);
-------------------------------------------------------
-- Collection2Item table
-------------------------------------------------------
CREATE TABLE Collection2Item
(
id INTEGER PRIMARY KEY,
collection_id INTEGER REFERENCES Collection(collection_id),
item_id INTEGER REFERENCES Item(item_id)
);
-- index by collection_id
CREATE INDEX collection2item_collection_idx ON Collection2Item(collection_id);
-- and item_id
CREATE INDEX Collection2Item_item_id_idx ON Collection2Item( item_id );
-------------------------------------------------------
-- ResourcePolicy table
-------------------------------------------------------
CREATE TABLE ResourcePolicy
(
policy_id INTEGER PRIMARY KEY,
resource_type_id INTEGER,
resource_id INTEGER,
action_id INTEGER,
eperson_id INTEGER REFERENCES EPerson(eperson_id),
epersongroup_id INTEGER REFERENCES EPersonGroup(eperson_group_id),
start_date DATE,
end_date DATE
);
-- index by resource_type,resource_id - all queries by
-- authorization manager are select type=x, id=y, action=z
CREATE INDEX resourcepolicy_type_id_idx ON ResourcePolicy(resource_type_id,resource_id);
CREATE INDEX rp_eperson_fk_idx ON ResourcePolicy(eperson_id);
CREATE INDEX rp_epersongroup_fk_idx ON ResourcePolicy(epersongroup_id);
-------------------------------------------------------
-- EPersonGroup2EPerson table
-------------------------------------------------------
CREATE TABLE EPersonGroup2EPerson
(
id INTEGER PRIMARY KEY,
eperson_group_id INTEGER REFERENCES EPersonGroup(eperson_group_id),
eperson_id INTEGER REFERENCES EPerson(eperson_id)
);
-- Index by group ID (used heavily by AuthorizeManager)
CREATE INDEX epersongroup2eperson_group_idx on EPersonGroup2EPerson(eperson_group_id);
CREATE INDEX epg2ep_eperson_fk_idx ON EPersonGroup2EPerson(eperson_id);
-------------------------------------------------------
-- Handle table
-------------------------------------------------------
CREATE TABLE Handle
(
handle_id INTEGER PRIMARY KEY,
handle VARCHAR(256) UNIQUE,
resource_type_id INTEGER,
resource_id INTEGER
);
-- index by handle, commonly looked up
CREATE INDEX handle_handle_idx ON Handle(handle);
-- index by resource id and resource type id
CREATE INDEX handle_resource_id_and_type_idx ON handle(resource_id, resource_type_id);
-------------------------------------------------------
-- WorkspaceItem table
-------------------------------------------------------
CREATE TABLE WorkspaceItem
(
workspace_item_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
collection_id INTEGER REFERENCES Collection(collection_id),
-- Answers to questions on first page of submit UI
multiple_titles BOOL,
published_before BOOL,
multiple_files BOOL,
-- How for the user has got in the submit process
stage_reached INTEGER,
page_reached INTEGER
);
CREATE INDEX workspace_item_fk_idx ON WorkspaceItem(item_id);
CREATE INDEX workspace_coll_fk_idx ON WorkspaceItem(collection_id);
-------------------------------------------------------
-- WorkflowItem table
-------------------------------------------------------
CREATE TABLE WorkflowItem
(
workflow_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id) UNIQUE,
collection_id INTEGER REFERENCES Collection(collection_id),
state INTEGER,
owner INTEGER REFERENCES EPerson(eperson_id),
-- Answers to questions on first page of submit UI
multiple_titles BOOL,
published_before BOOL,
multiple_files BOOL
-- Note: stage reached not applicable here - people involved in workflow
-- can always jump around submission UI
);
CREATE INDEX workflow_item_fk_idx ON WorkflowItem(item_id);
CREATE INDEX workflow_coll_fk_idx ON WorkflowItem(collection_id);
CREATE INDEX workflow_owner_fk_idx ON WorkflowItem(owner);
-------------------------------------------------------
-- TasklistItem table
-------------------------------------------------------
CREATE TABLE TasklistItem
(
tasklist_id INTEGER PRIMARY KEY,
eperson_id INTEGER REFERENCES EPerson(eperson_id),
workflow_id INTEGER REFERENCES WorkflowItem(workflow_id)
);
CREATE INDEX tasklist_eperson_fk_idx ON TasklistItem(eperson_id);
CREATE INDEX tasklist_workflow_fk_idx ON TasklistItem(workflow_id);
-------------------------------------------------------
-- RegistrationData table
-------------------------------------------------------
CREATE TABLE RegistrationData
(
registrationdata_id INTEGER PRIMARY KEY,
email VARCHAR(64) UNIQUE,
token VARCHAR(48),
expires TIMESTAMP
);
-------------------------------------------------------
-- Subscription table
-------------------------------------------------------
CREATE TABLE Subscription
(
subscription_id INTEGER PRIMARY KEY,
eperson_id INTEGER REFERENCES EPerson(eperson_id),
collection_id INTEGER REFERENCES Collection(collection_id)
);
CREATE INDEX subs_eperson_fk_idx ON Subscription(eperson_id);
CREATE INDEX subs_collection_fk_idx ON Subscription(collection_id);
-------------------------------------------------------------------------------
-- EPersonGroup2WorkspaceItem table
-------------------------------------------------------------------------------
CREATE TABLE epersongroup2workspaceitem
(
id integer DEFAULT nextval('epersongroup2workspaceitem_seq'),
eperson_group_id integer REFERENCES EPersonGroup(eperson_group_id),
workspace_item_id integer REFERENCES WorkspaceItem(workspace_item_id),
CONSTRAINT epersongroup2item_pkey PRIMARY KEY (id)
);
CREATE INDEX epg2wi_group_fk_idx ON epersongroup2workspaceitem(eperson_group_id);
CREATE INDEX epg2wi_workspace_fk_idx ON epersongroup2workspaceitem(workspace_item_id);
------------------------------------------------------------
-- Browse subsystem tables and views
------------------------------------------------------------
-------------------------------------------------------
-- Communities2Item table
-------------------------------------------------------
CREATE TABLE Communities2Item
(
id INTEGER PRIMARY KEY,
community_id INTEGER REFERENCES Community(community_id),
item_id INTEGER REFERENCES Item(item_id)
);
-- Index by item_id for update/re-index
CREATE INDEX Communities2Item_item_id_idx ON Communities2Item( item_id );
CREATE INDEX Comm2Item_community_fk_idx ON Communities2Item( community_id );
-------------------------------------------------------
-- Community2Item view
------------------------------------------------------
CREATE VIEW Community2Item as
SELECT Community2Collection.community_id, Collection2Item.item_id
FROM Community2Collection, Collection2Item
WHERE Collection2Item.collection_id = Community2Collection.collection_id
;
-------------------------------------------------------------------------
-- Tables to manage cache of item counts for communities and collections
-------------------------------------------------------------------------
CREATE TABLE collection_item_count (
collection_id INTEGER PRIMARY KEY REFERENCES collection(collection_id),
count INTEGER
);
CREATE TABLE community_item_count (
community_id INTEGER PRIMARY KEY REFERENCES community(community_id),
count INTEGER
);
-------------------------------------------------------
-- Create 'special' groups, for anonymous access
-- and administrators
-------------------------------------------------------
-- We don't use getnextid() for 'anonymous' since the sequences start at '1'
INSERT INTO epersongroup VALUES(0, 'Anonymous');
INSERT INTO epersongroup VALUES(getnextid('epersongroup'), 'Administrator');
-------------------------------------------------------
-- Create the checksum checker tables
-------------------------------------------------------
-- list of the possible results as determined
-- by the system or an administrator
CREATE TABLE checksum_results
(
result_code VARCHAR PRIMARY KEY,
result_description VARCHAR
);
-- This table has a one-to-one relationship
-- with the bitstream table. A row will be inserted
-- every time a row is inserted into the bitstream table, and
-- that row will be updated every time the checksum is
-- re-calculated.
CREATE TABLE most_recent_checksum
(
bitstream_id INTEGER PRIMARY KEY REFERENCES bitstream(bitstream_id),
to_be_processed BOOLEAN NOT NULL,
expected_checksum VARCHAR NOT NULL,
current_checksum VARCHAR NOT NULL,
last_process_start_date TIMESTAMP NOT NULL,
last_process_end_date TIMESTAMP NOT NULL,
checksum_algorithm VARCHAR NOT NULL,
matched_prev_checksum BOOLEAN NOT NULL,
result VARCHAR REFERENCES checksum_results(result_code)
);
CREATE INDEX mrc_result_fk_idx ON most_recent_checksum( result );
-- A row will be inserted into this table every
-- time a checksum is re-calculated.
CREATE TABLE checksum_history
(
check_id BIGSERIAL PRIMARY KEY,
bitstream_id INTEGER,
process_start_date TIMESTAMP,
process_end_date TIMESTAMP,
checksum_expected VARCHAR,
checksum_calculated VARCHAR,
result VARCHAR REFERENCES checksum_results(result_code)
);
CREATE INDEX ch_result_fk_idx ON checksum_history( result );
-- this will insert into the result code
-- the initial results that should be
-- possible
insert into checksum_results
values
(
'INVALID_HISTORY',
'Install of the cheksum checking code do not consider this history as valid'
);
insert into checksum_results
values
(
'BITSTREAM_NOT_FOUND',
'The bitstream could not be found'
);
insert into checksum_results
values
(
'CHECKSUM_MATCH',
'Current checksum matched previous checksum'
);
insert into checksum_results
values
(
'CHECKSUM_NO_MATCH',
'Current checksum does not match previous checksum'
);
insert into checksum_results
values
(
'CHECKSUM_PREV_NOT_FOUND',
'Previous checksum was not found: no comparison possible'
);
insert into checksum_results
values
(
'BITSTREAM_INFO_NOT_FOUND',
'Bitstream info not found'
);
insert into checksum_results
values
(
'CHECKSUM_ALGORITHM_INVALID',
'Invalid checksum algorithm'
);
insert into checksum_results
values
(
'BITSTREAM_NOT_PROCESSED',
'Bitstream marked to_be_processed=false'
);
insert into checksum_results
values
(
'BITSTREAM_MARKED_DELETED',
'Bitstream marked deleted in bitstream table'
);
-------------------------------------------------------
-- Create the harvest settings table
-------------------------------------------------------
CREATE TABLE harvestsettings
(
collection_id INTEGER REFERENCES collection(collection_id) ON DELETE CASCADE,
harvest_type INTEGER,
oai_source VARCHAR,
oai_set_id VARCHAR,
harvest_message VARCHAR,
metadata_config_id VARCHAR,
harvest_status INTEGER,
harvest_start_time TIMESTAMP WITH TIME ZONE,
last_harvested TIMESTAMP WITH TIME ZONE,
id INTEGER PRIMARY KEY
);
CREATE INDEX harvestsettings_fk_idx ON harvestsettings(collection_id);
-------------------------------------------------------
-- Vireo submission table
-------------------------------------------------------
CREATE TABLE vireosubmission
(
submission_id integer NOT NULL PRIMARY KEY,
applicant_id integer,
item_id integer,
current_phone character varying(32),
permanent_phone character varying(32),
citizenship_info character varying(64),
status integer DEFAULT -1,
assigned_to integer DEFAULT -1,
current_address character varying(1024),
permanent_address character varying(1024),
embargo_requested int4, -- deprecated
embargo_name text, -- new in 1.1.0
embargo_duration int4, -- new in 1.1.0
embargo_description text, -- new in 1.1.0
email_address character varying(64),
submission_date timestamp without time zone,
approval_date timestamp without time zone,
collection_id integer,
license_agreement_date timestamp without time zone,
degree_type text,
custom_actions_completed varchar(1024),
committee_email_address varchar(64),
email_hash varchar(32),
committee_approval text,
committee_embargo_approval text,
year_of_birth int4,
permanent_email varchar(64),
college text,
umi int4
);
CREATE SEQUENCE vireosubmission_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
CREATE INDEX submission_all ON vireosubmission USING btree
(
submission_id,
applicant_id,
item_id,
current_phone,
permanent_phone,
citizenship_info,
status,
assigned_to,
current_address,
permanent_address,
embargo_requested,
email_address,
submission_date,
approval_date,
collection_id,
license_agreement_date,
degree_type,
custom_actions_completed
);
-------------------------------------------------------
-- The big vireo view.... be afraid, very afraid...
-------------------------------------------------------
CREATE OR REPLACE VIEW vireo_search AS SELECT app.submission_id AS application_id, app.item_id,
(SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'creator'::text AND mr.qualifier::text IS NULL)) LIMIT 1) AS author,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'title'::text AND mr.qualifier IS NULL)) LIMIT 1) AS title,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'discipline'::text)) LIMIT 1) AS discipline,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'level'::text)) LIMIT 1) AS degree_level,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'name'::text)) LIMIT 1) AS degree_name,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'date'::text AND mr.qualifier::text = 'submitted'::text)) LIMIT 1) AS graduation_semester,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'subject'::text AND mr.qualifier IS NULL)) LIMIT 1) AS keywords,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'description'::text AND mr.qualifier::text = 'abstract'::text)) LIMIT 1) AS abstract,
((((((((((((((( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'creator'::text AND mr.qualifier::text IS NULL)) LIMIT 1)) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'title'::text AND mr.qualifier IS NULL)) LIMIT 1))) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'discipline'::text)) LIMIT 1))) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'level'::text)) LIMIT 1))) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'name'::text)) LIMIT 1))) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'date'::text AND mr.qualifier::text = 'submitted'::text)) LIMIT 1))) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'subject'::text AND mr.qualifier IS NULL)) LIMIT 1))) || ' '::text) ||
(( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'dc'::text))
AND mr.element::text = 'description'::text AND mr.qualifier::text = 'abstract'::text)) LIMIT 1)) AS search,
( SELECT m.text_value FROM metadatavalue m WHERE m.item_id = app.item_id AND m.metadata_field_id =
(( SELECT mr.metadata_field_id FROM metadatafieldregistry mr WHERE mr.metadata_schema_id =
(( SELECT ms.metadata_schema_id FROM metadataschemaregistry ms WHERE ms.short_id::text = 'thesis'::text))
AND mr.element::text = 'degree'::text AND mr.qualifier::text = 'department'::text)) LIMIT 1) AS department
FROM vireosubmission app;
-------------------------------------------------------
-- Vireo preference table
-------------------------------------------------------
CREATE TABLE vireoadminpreference
(
entity_id character varying(250),
preference_type character varying(50),
preference text,
pref_id integer NOT NULL PRIMARY KEY,
preference_order integer DEFAULT 0
);
CREATE SEQUENCE vireoadminpreference_seq INCREMENT 1 MINVALUE 1 MAXVALUE 999999999999999999 START 1 CACHE 1;
CREATE INDEX vireopreference_all ON vireoadminpreference USING btree
(
entity_id,
preference_type,
preference,
pref_id,
preference_order
);
CREATE INDEX vireopreference_order ON vireoadminpreference USING btree
(
preference_order
);
CREATE INDEX vireopreference_type ON vireoadminpreference USING btree
(
preference_type
);
-------------------------------------------------------
-- Vireo log table
-------------------------------------------------------
CREATE TABLE vireolog
(
submission_id integer,
submission_status integer,
eperson_id integer,
date timestamp without time zone,
log_id integer NOT NULL PRIMARY KEY,
log_entry text,
private boolean DEFAULT false,
event_type integer,
bitstream_id integer
);
CREATE SEQUENCE vireolog_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
CREATE INDEX "all" ON vireolog USING btree
(
submission_id,
submission_status,
eperson_id,
date,
log_id,
log_entry,
private,
event_type,
bitstream_id
);

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks