database_schema.sql from DSpace at Krugle
Show database_schema.sql syntax highlighted
--
-- database_schema.sql
--
-- Version: $Revision: 2123 $
--
-- Date: $Date: 2007-08-07 15:54:53 -0700 (Tue, 07 Aug 2007) $
--
-- 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 itemsbyauthor_seq;
CREATE SEQUENCE itemsbytitle_seq;
CREATE SEQUENCE itemsbydate_seq;
CREATE SEQUENCE itemsbydateaccessioned_seq;
CREATE SEQUENCE itemsbysubject_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;
-------------------------------------------------------
-- BitstreamFormatRegistry table
-------------------------------------------------------
CREATE TABLE BitstreamFormatRegistry
(
bitstream_format_id INTEGER PRIMARY KEY,
mimetype VARCHAR(48),
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)
);
-------------------------------------------------------
-- 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
);
-------------------------------------------------------
-- 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)
);
-- 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)
);
------------------------------------------------------
-- 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)
);
-------------------------------------------------------
-- 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
);
-------------------------------------------------------
-- Bundle table
-------------------------------------------------------
CREATE TABLE Bundle
(
bundle_id INTEGER PRIMARY KEY,
name VARCHAR(16), -- ORIGINAL | THUMBNAIL | TEXT
primary_bitstream_id INTEGER REFERENCES Bitstream(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);
-------------------------------------------------------
-- 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);
-------------------------------------------------------
-- 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 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
);
-------------------------------------------------------
-- 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)
);
-------------------------------------------------------
-- 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)
);
-------------------------------------------------------
-- 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);
-------------------------------------------------------
-- 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);
-------------------------------------------------------
-- 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
);
-------------------------------------------------------
-- 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
);
-------------------------------------------------------
-- TasklistItem table
-------------------------------------------------------
CREATE TABLE TasklistItem
(
tasklist_id INTEGER PRIMARY KEY,
eperson_id INTEGER REFERENCES EPerson(eperson_id),
workflow_id INTEGER REFERENCES WorkflowItem(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)
);
-------------------------------------------------------
-- History table
-------------------------------------------------------
CREATE TABLE History
(
history_id INTEGER PRIMARY KEY,
-- When it was stored
creation_date TIMESTAMP,
-- A checksum to keep INTEGERizations from being stored more than once
checksum VARCHAR(32) UNIQUE
);
-------------------------------------------------------
-- HistoryState table
-------------------------------------------------------
CREATE TABLE HistoryState
(
history_state_id INTEGER PRIMARY KEY,
object_id VARCHAR(64)
);
-------------------------------------------------------------------------------
-- 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)
);
------------------------------------------------------------
-- 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 );
-------------------------------------------------------
-- Community2Item view
------------------------------------------------------
CREATE VIEW Community2Item as
SELECT Community2Collection.community_id, Collection2Item.item_id
FROM Community2Collection, Collection2Item
WHERE Collection2Item.collection_id = Community2Collection.collection_id
;
-------------------------------------------------------
-- ItemsByAuthor table
-------------------------------------------------------
CREATE TABLE ItemsByAuthor
(
items_by_author_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
author TEXT,
sort_author TEXT
);
-- index by sort_author, of course!
CREATE INDEX sort_author_idx on ItemsByAuthor(sort_author);
-- And by item ID for updates/re-indexing
CREATE INDEX ItemsByAuthor_item_id_idx ON ItemsByAuthor(item_id);
-------------------------------------------------------
-- CollectionItemsByAuthor view
-------------------------------------------------------
CREATE VIEW CollectionItemsByAuthor as
SELECT Collection2Item.collection_id, ItemsByAuthor.*
FROM ItemsByAuthor, Collection2Item
WHERE ItemsByAuthor.item_id = Collection2Item.item_id
;
-------------------------------------------------------
-- CommunityItemsByAuthor view
-------------------------------------------------------
CREATE VIEW CommunityItemsByAuthor as
SELECT Communities2Item.community_id, ItemsByAuthor.*
FROM ItemsByAuthor, Communities2Item
WHERE ItemsByAuthor.item_id = Communities2Item.item_id
;
----------------------------------------
-- ItemsByTitle table
----------------------------------------
CREATE TABLE ItemsByTitle
(
items_by_title_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
title TEXT,
sort_title TEXT
);
-- index by the sort_title
CREATE INDEX sort_title_idx on ItemsByTitle(sort_title);
-- And by item ID for updates/re-indexing
CREATE INDEX ItemsByTitle_item_id_idx ON ItemsByTitle(item_id);
-------------------------------------------------------
-- CollectionItemsByTitle view
-------------------------------------------------------
CREATE VIEW CollectionItemsByTitle as
SELECT Collection2Item.collection_id, ItemsByTitle.*
FROM ItemsByTitle, Collection2Item
WHERE ItemsByTitle.item_id = Collection2Item.item_id
;
-------------------------------------------------------
-- CommunityItemsByTitle view
-------------------------------------------------------
CREATE VIEW CommunityItemsByTitle as
SELECT Communities2Item.community_id, ItemsByTitle.*
FROM ItemsByTitle, Communities2Item
WHERE ItemsByTitle.item_id = Communities2Item.item_id
;
-------------------------------------------------------
-- ItemsByDate table
-------------------------------------------------------
CREATE TABLE ItemsByDate
(
items_by_date_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
date_issued TEXT
);
-- sort by date
CREATE INDEX date_issued_idx on ItemsByDate(date_issued);
-- And by item ID for updates/re-indexing
CREATE INDEX ItemsByDate_item_id_idx ON ItemsByDate(item_id);
-------------------------------------------------------
-- CollectionItemsByDate view
-------------------------------------------------------
CREATE VIEW CollectionItemsByDate as
SELECT Collection2Item.collection_id, ItemsByDate.*
FROM ItemsByDate, Collection2Item
WHERE ItemsByDate.item_id = Collection2Item.item_id
;
-------------------------------------------------------
-- CommunityItemsByDate view
-------------------------------------------------------
CREATE VIEW CommunityItemsByDate as
SELECT Communities2Item.community_id, ItemsByDate.*
FROM ItemsByDate, Communities2Item
WHERE ItemsByDate.item_id = Communities2Item.item_id
;
-------------------------------------------------------
-- ItemsByDateAccessioned table
-------------------------------------------------------
CREATE TABLE ItemsByDateAccessioned
(
items_by_date_accessioned_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
date_accessioned TEXT
);
-- Index on item_id for update/re-indexing
CREATE INDEX ItemsByDateAccessioned_item_id_idx ON ItemsByDateAccessioned(item_id);
-------------------------------------------------------
-- CollectionItemsByDateAccession view
-------------------------------------------------------
CREATE VIEW CollectionItemsByDateAccession as
SELECT Collection2Item.collection_id, ItemsByDateAccessioned.*
FROM ItemsByDateAccessioned, Collection2Item
WHERE ItemsByDateAccessioned.item_id = Collection2Item.item_id
;
-------------------------------------------------------
-- CommunityItemsByDateAccession view
-------------------------------------------------------
CREATE VIEW CommunityItemsByDateAccession as
SELECT Communities2Item.community_id, ItemsByDateAccessioned.*
FROM ItemsByDateAccessioned, Communities2Item
WHERE ItemsByDateAccessioned.item_id = Communities2Item.item_id
;
-------------------------------------------------------
-- ItemsBySubject table
-------------------------------------------------------
CREATE TABLE ItemsBySubject
(
items_by_subject_id INTEGER PRIMARY KEY,
item_id INTEGER REFERENCES Item(item_id),
subject TEXT,
sort_subject TEXT
);
-- index by sort_subject
CREATE INDEX sort_subject_idx on ItemsBySubject(sort_subject);
-------------------------------------------------------
-- CollectionItemsBySubject view
-------------------------------------------------------
CREATE VIEW CollectionItemsBySubject as
SELECT Collection2Item.collection_id, ItemsBySubject.*
FROM ItemsBySubject, Collection2Item
WHERE ItemsBySubject.item_id = Collection2Item.item_id
;
-------------------------------------------------------
-- CommunityItemsBySubject view
-------------------------------------------------------
CREATE VIEW CommunityItemsBySubject as
SELECT Communities2Item.community_id, ItemsBySubject.*
FROM ItemsBySubject, Communities2Item
WHERE ItemsBySubject.item_id = Communities2Item.item_id
;
-------------------------------------------------------
-- 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)
);
-- 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)
);
-- 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'
);
See more files for this project here