source: cats/ISOcat/trunk/mod-ISOcat-access-data/dbms/ISOcat-schema-init.sql @ 2326

-- clean the tables in the right order:

DELETE FROM
        generic_lock
;
DELETE FROM
        generic_name
;
DELETE FROM
        generic_text
;

DELETE FROM
        link_open_data_category_conceptual_domain
;
DELETE FROM
        link_closed_data_category_conceptual_domains
;
DELETE FROM
        link_constrained_data_category_conceptual_domains
;
DELETE FROM
        link_constrained_linguistic_section_conceptual_domains
;
DELETE FROM
        link_data_category_selection_users
;
DELETE FROM
        link_group_members
;
DELETE FROM
        link_profile_members
;
DELETE FROM
        link_selected_data_category_picklist
;
DELETE FROM
        link_selected_data_category_profiles
;
DELETE FROM
        link_simple_data_category_super
;
DELETE FROM
        link_value_domain_values
;

DELETE FROM
        core_selected_container_data_category
;
DELETE FROM
        core_selected_complex_data_category
;
DELETE FROM
        core_selected_simple_data_category
;
DELETE FROM
        core_selected_data_category
;

DELETE FROM
        core_data_category_selection
;

DELETE FROM
        core_registration_group
;
DELETE FROM
        core_submission_group
;
DELETE FROM
        core_decision_group
;

DELETE FROM
        core_language_section
;

DELETE FROM
        core_constrained_linguistic_section
;
DELETE FROM
        core_closed_linguistic_section
;
DELETE FROM
        core_linguistic_section
;

DELETE FROM
        core_rule
;

DELETE FROM
        core_profile_value_domain
;
DELETE FROM
        core_value_domain
;
DELETE FROM
        core_conceptual_domain_rule
;
DELETE FROM
        core_open_conceptual_domain
;
DELETE FROM
        core_conceptual_domain
;

DELETE FROM
        core_closed_data_category
;
DELETE FROM
        core_constrained_data_category
;
DELETE FROM
        core_open_data_category
;
DELETE FROM
        core_complex_data_category
;
DELETE FROM
        core_simple_data_category
;
DELETE FROM
        core_container_data_category
;
DELETE FROM
        core_data_category
;

DELETE FROM
        core_profile
;

DELETE FROM
        core_session
;

DELETE FROM
        core_message
;

DELETE FROM
        core_thematic_domain_group
;
DELETE FROM
        core_dcr_board
;
DELETE FROM
        core_group
;
DELETE FROM
        core_user
;
DELETE FROM
        core_owner
;
DELETE FROM
        core_account
;

DELETE FROM
        core_registration_authority
;

DELETE FROM
        core_language
;
DELETE FROM
        core_data_type
;
DELETE FROM
        core_rule_type
;

-- reset sequences

ALTER SEQUENCE core_account_id_seq RESTART WITH 1;

ALTER SEQUENCE core_conceptual_domain_id_seq RESTART WITH 1;

ALTER SEQUENCE core_data_category_id_seq RESTART WITH 1;

ALTER SEQUENCE core_data_category_selection_id_seq RESTART WITH 1;

ALTER SEQUENCE core_data_type_id_seq RESTART WITH 1;

ALTER SEQUENCE core_language_id_seq RESTART WITH 1;

ALTER SEQUENCE core_language_section_id_seq RESTART WITH 1;

ALTER SEQUENCE core_linguistic_section_id_seq RESTART WITH 1;

ALTER SEQUENCE core_message_id_seq RESTART WITH 1;

ALTER SEQUENCE core_profile_id_seq RESTART WITH 1;

ALTER SEQUENCE core_registration_authority_id_seq RESTART WITH 1;

ALTER SEQUENCE core_rule_id_seq RESTART WITH 1;

ALTER SEQUENCE core_rule_type_id_seq RESTART WITH 1;

ALTER SEQUENCE core_selected_data_category_id_seq RESTART WITH 1;

ALTER SEQUENCE core_session_id_seq RESTART WITH 1;

ALTER SEQUENCE generic_lock_id_seq RESTART WITH 1;

ALTER SEQUENCE generic_name_id_seq RESTART WITH 1;

ALTER SEQUENCE generic_text_id_seq RESTART WITH 1;

-- give some tables an initial filling

-- the default languages

INSERT INTO core_language (name, tag, search, tags) VALUES ('English',  'en', 'english', 'eng');
INSERT INTO core_language (name, tag, search) VALUES ('French',   'fr', 'french');
INSERT INTO core_language (name, tag, search) VALUES ('Japanese', 'ja', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('German',   'de', 'german');
INSERT INTO core_language (name, tag, search) VALUES ('Dutch',    'nl', 'dutch');
INSERT INTO core_language (name, tag, search) VALUES ('Czech',    'cs', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Estonian', 'et', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Finnish',  'fi', 'finnish');
INSERT INTO core_language (name, tag, search) VALUES ('Hungarian','hu', 'hungarian');
INSERT INTO core_language (name, tag, search) VALUES ('Italian',  'it', 'italian');
INSERT INTO core_language (name, tag, search) VALUES ('Korean',   'ko', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Romanian', 'ro', 'romanian');
INSERT INTO core_language (name, tag, search) VALUES ('Russian',  'ru', 'russian');
INSERT INTO core_language (name, tag, search) VALUES ('Swedish',  'sv', 'swedish');

INSERT INTO core_language (name, tag, search) VALUES ('Bulgarian',     'bg', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Croatian',      'hr', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Danish',        'da', 'danish');
INSERT INTO core_language (name, tag, search) VALUES ('Greek',         'el', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Icelandic',     'is', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Irish',         'ga', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Hebrew',        'he', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Latvian',       'lv', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Lithuanian',    'lt', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Luxembourgish', 'lb', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Maltese',       'mt', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Norwegian',     'no', 'norwegian');
INSERT INTO core_language (name, tag, search) VALUES ('Polish',        'pl', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Portuguese',    'pt', 'portuguese');
INSERT INTO core_language (name, tag, search) VALUES ('Serbian',       'sr', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Slovene',       'sl', 'simple');
INSERT INTO core_language (name, tag, search) VALUES ('Spanish',       'es', 'spanish');
INSERT INTO core_language (name, tag, search) VALUES ('Turkish',       'tr', 'turkish');
INSERT INTO core_language (name, tag, search) VALUES ('Slovak',        'sk', 'simple');

INSERT INTO core_language (name, tag, search, tags) VALUES ('Welsh', 'cy', 'simple', 'cym');

INSERT INTO core_language (name, tag, search, direction) VALUES ('Arabic',  'ar', 'simple', 'rtl');
INSERT INTO core_language (name, tag, search, direction) VALUES ('Chinese', 'zh', 'simple', 'ltr');

-- the default data types

INSERT INTO core_data_type (id, data_type, super) VALUES ( 2,'anyType',NULL);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 3,'anySimpleType',2);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 4,'duration',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 5,'dateTime',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 6,'time',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 7,'date',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 8,'gYearMonth',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 9,'gYear',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (10,'gMonthDay',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (11,'gDay',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (12,'gMonth',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (13,'boolean',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (14,'base64Binary',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (15,'hexBinary',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (16,'float',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (17,'double',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (18,'anyURI',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (19,'QName',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (20,'NOTATION',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 1,'string',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (21,'normalizedString',1);
INSERT INTO core_data_type (id, data_type, super) VALUES (22,'token',21);
INSERT INTO core_data_type (id, data_type, super) VALUES (23,'language',22);
INSERT INTO core_data_type (id, data_type, super) VALUES (24,'Name',22);
INSERT INTO core_data_type (id, data_type, super) VALUES (25,'NMTOKEN',22);
INSERT INTO core_data_type (id, data_type, super) VALUES (26,'NCName',24);
INSERT INTO core_data_type (id, data_type, super) VALUES (27,'ID',26);
INSERT INTO core_data_type (id, data_type, super) VALUES (28,'IDREF',26);
INSERT INTO core_data_type (id, data_type, super) VALUES (29,'ENTITY',26);
INSERT INTO core_data_type (id, data_type, super) VALUES (30,'decimal',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (31,'integer',30);
INSERT INTO core_data_type (id, data_type, super) VALUES (32,'nonPositiveInteger',31);
INSERT INTO core_data_type (id, data_type, super) VALUES (33,'long',31);
INSERT INTO core_data_type (id, data_type, super) VALUES (34,'nonNegativeInteger',31);
INSERT INTO core_data_type (id, data_type, super) VALUES (35,'negativeInteger',32);
INSERT INTO core_data_type (id, data_type, super) VALUES (36,'int',33);
INSERT INTO core_data_type (id, data_type, super) VALUES (37,'short',36);
INSERT INTO core_data_type (id, data_type, super) VALUES (38,'byte',37);
INSERT INTO core_data_type (id, data_type, super) VALUES (39,'unsignedLong',34);
INSERT INTO core_data_type (id, data_type, super) VALUES (40,'positiveInteger',34);
INSERT INTO core_data_type (id, data_type, super) VALUES (41,'unsignedInt',39);
INSERT INTO core_data_type (id, data_type, super) VALUES (42,'unsignedShort',41);
INSERT INTO core_data_type (id, data_type, super) VALUES (43,'unsignedByte',42);

SELECT pg_catalog.setval('core_data_type_id_seq', 43, true);

-- the default rule languages

INSERT INTO core_rule_type (rule_type, xml) VALUES('XML Schema facet',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('XML Schema regular expression',false);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Relax NG datatype parameter',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Schematron rule',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Object Constraint Language',false);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Semantic Web Rule Language',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('SCHEMAcat reference',true);

-- the default registration authority
-- CHANGE: rename to the proper name of the Registration Authority

INSERT INTO core_registration_authority (id, registration_authority) VALUES (0, 'Private');
INSERT INTO core_registration_authority (    registration_authority) VALUES (   'Example Registration Authority');

-- the default guest user (password: guest)

INSERT INTO core_account(id) VALUES (1);
INSERT INTO core_owner(id) VALUES (1);
INSERT INTO core_user(id, login, last_name, password, email, disclaimer, sync) VALUES (1, 'guest', 'Guest', '084e0343a0486ff05530df6c705c8bb4', '', true, false);

-- the default isocat admin (no password, internal use only)

INSERT INTO core_account(id) VALUES (2);
INSERT INTO core_owner(id) VALUES (2);
INSERT INTO core_user(id, login, last_name, password, email, type, disclaimer, sync) VALUES (2, 'isocat', 'ISOcat', '', '', 'admin', true, false);

-- the default DCR Board chair (password: password)
-- CHANGE: replace by the proper details of the DCR Board chair

INSERT INTO core_account(id) VALUES (3);
INSERT INTO core_owner(id) VALUES (3);
INSERT INTO core_user(id, login, last_name, password, email, type, disclaimer, sync) VALUES (3, 'chair', 'DCR Board chair', '5f4dcc3b5aa765d61d8327deb882cf99', 'chair@example.com', 'expert', true, false);

-- the DCR Board

INSERT INTO core_account(id) VALUES (4);
INSERT INTO core_group (id) VALUES (4);
INSERT INTO core_dcr_board (id) VALUES (4);
INSERT INTO link_group_members("group", member, "role", status) VALUES (4,3,'chair','accepted');

-- the default profile

INSERT INTO core_profile (id, name) VALUES (0, 'Private');

-- an example TDG
INSERT INTO core_account(id) VALUES (5);
INSERT INTO core_owner(id) VALUES (5);
INSERT INTO core_group (id) VALUES (5);
INSERT INTO core_thematic_domain_group (id,"name",description,"order",status,activity) VALUES (5,'Example','This is an example TDG and profile. To rename them the PostgreSQL database needs to be updated.',1,'example','example');
INSERT INTO link_group_members("group", member, "role", status) VALUES (5,3,'chair','accepted');

-- an example TDG profile

INSERT INTO core_profile (name,tdg) VALUES ('Example',5);

SELECT pg_catalog.setval('core_account_id_seq', 5, true);