Shadowhelix:Datenbank/Umsetzung SQL

Datenbankstruktur

Skript für Tabellenerstellung:

CREATE TABLE source(
id_source      TEXT NOT NULL
,description   TEXT NOT NULL
,language_code TEXT NOT NULL
,type          TEXT
,id_reference  TEXT
);

CREATE TABLE source_catalog(
id_source     TEXT NOT NULL
,id_part      TEXT
,issue        TEXT
,id_version   TEXT
,catalog      TEXT
,key          TEXT
,id_reference TEXT
);

CREATE TABLE source_date(
id_source     TEXT NOT NULL
,id_part      TEXT
,issue        TEXT
,id_version   TEXT
,year         TEXT
,month        TEXT
,day          TEXT
,id_reference TEXT
);

CREATE TABLE source_date_ingame(
id_source          TEXT NOT NULL
,id_part           TEXT
,issue             TEXT
,id_version        TEXT
,range_start_year  TEXT
,range_start_month TEXT
,range_start_day   TEXT
,range_end_year    TEXT
,range_end_month   TEXT
,range_end_day     TEXT
,id_reference      TEXT
);

CREATE TABLE source_edition(
id_source     TEXT NOT NULL
,id_part      TEXT
,issue        TEXT
,id_version   TEXT
,edition      TEXT
,id_reference TEXT
);

CREATE TABLE source_page_number(
id_source     TEXT NOT NULL
,id_part      TEXT
,issue        TEXT
,id_version   TEXT
,page_number  TEXT
,id_reference TEXT
);

CREATE TABLE source_part(
id_source     TEXT NOT NULL
,id_part      TEXT
,issue        TEXT
,id_version   TEXT
,description  TEXT
,id_reference TEXT
);

CREATE TABLE source_title(
id_source     TEXT NOT NULL
,id_part      TEXT
,issue        TEXT
,id_version   TEXT
,prefix       TEXT
,pre_article  TEXT
,title        TEXT
,subtitle     TEXT
,id_reference TEXT
);

CREATE TABLE source_translated(
id_source            TEXT NOT NULL
,id_source_original  TEXT NOT NULL
,partial_translation TEXT
,id_reference        TEXT
);

CREATE TABLE contributor(
id_contributor   TEXT NOT NULL
,surname         TEXT
,surname_suffix  TEXT
,forename_first  TEXT
,forename_second TEXT
,forename_third  TEXT
,year_birth      TEXT
,year_death      TEXT
,sex             CHAR(1)
,id_reference    TEXT
);

CREATE TABLE contributor_catalog(
id_contributor          TEXT NOT NULL
,catalog                TEXT
,key                    TEXT
,archive_copy_timestamp INTEGER(0)
,id_reference           TEXT
);

CREATE TABLE contributor_referenced_name(
id_contributor   TEXT NOT NULL
,referenced_name TEXT NOT NULL
,id_reference    TEXT
);

CREATE TABLE contributor_weblink(
id_contributor          TEXT NOT NULL
,url                    TEXT NOT NULL
,archive_copy_timestamp INTEGER(0)
,id_reference           TEXT
);

CREATE TABLE contributor_weblink_hosting_service(
id_contributor          TEXT NOT NULL
,hosting_service        TEXT NOT NULL
,account_name           TEXT NOT NULL
,archive_copy_timestamp INTEGER(0)
,id_reference           TEXT
);

CREATE TABLE contribution(
id_contributor        TEXT NOT NULL
,id_source            TEXT NOT NULL
,id_part              TEXT
,issue                TEXT
,id_version           TEXT
,type_of_contribution TEXT NOT NULL
,uncredited           CHAR(1)
,id_reference         TEXT
);

CREATE TABLE contribution_illustration(
id_contributor   TEXT NOT NULL
,id_illustration TEXT
,id_reference    TEXT
);

CREATE TABLE contribution_text(
id_contributor TEXT NOT NULL
,id_text       TEXT NOT NULL
,id_reference  TEXT
);

CREATE TABLE illustration_catalog(
id_illustration TEXT NOT NULL
,catalog        TEXT
,key            TEXT
,id_reference   TEXT
);

CREATE TABLE illustration_source(
id_illustration  TEXT NOT NULL
,id_source       TEXT NOT NULL
,id_part         TEXT
,issue           TEXT
,id_version      TEXT
,page_reference  TEXT
,entry_reference TEXT
,position        TEXT
,modification    TEXT
,id_reference    TEXT
);

CREATE TABLE illustration_type(
id_illustration TEXT NOT NULL
,type           TEXT NOT NULL
,id_reference   TEXT
);

CREATE TABLE illustration_weblink(
id_illustration TEXT NOT NULL
,web_link       TEXT
,direct_link    TEXT
,id_reference   TEXT
);

CREATE TABLE index_creature(
id_creature      TEXT NOT NULL
,id_source       TEXT
,id_part         TEXT
,issue           TEXT
,id_version      TEXT
,page_reference  TEXT
,entry_reference TEXT
,id_reference    TEXT
);

CREATE TABLE index_product(
id_product       TEXT NOT NULL
,id_source       TEXT
,id_part         TEXT
,issue           TEXT
,id_version      TEXT
,page_reference  TEXT
,entry_reference TEXT
,id_reference    TEXT
);

CREATE TABLE index_illustration_person(
id_person        TEXT NOT NULL
,id_illustration TEXT NOT NULL
,id_reference    TEXT
);

CREATE TABLE index_text_document(
id_document   TEXT NOT NULL
,id_text      TEXT NOT NULL
,id_reference TEXT
);

CREATE TABLE corporation_name(
id_corporation INTEGER(0) NOT NULL
,language_code TEXT
,short_name    TEXT
,full_name     TEXT
,abbreviation  TEXT
);

CREATE TABLE corporation_ownership(
id_corporation         INTEGER(0) NOT NULL
,id_corporation_owner  INTEGER(0)
,id_organisation_owner INTEGER(0)
,id_person_owner       INTEGER(0)
,fraction              TEXT
,from_year             TEXT
,from_month            TEXT
,to_year               TEXT
,to_month              TEXT
);

CREATE TABLE creature_name(
id_creature        TEXT NOT NULL
,language_code      TEXT
,sex                TEXT
,name               TEXT NOT NULL
,grammatical_number TEXT
,generic_form       INTEGER(0)
,id_reference       TEXT
);

CREATE TABLE document_producer_organisation(
id_document      TEXT NOT NULL
,id_organisation TEXT NOT NULL
,id_reference    TEXT
);

CREATE TABLE document_producer_person(
id_document      TEXT NOT NULL
,id_person       TEXT NOT NULL
,production_role TEXT
,id_reference    TEXT
);

CREATE TABLE document_title(
id_document    TEXT NOT NULL
,language_code TEXT
,title         TEXT
,original      INTEGER(0)
,id_reference  TEXT
);

CREATE TABLE product_name(
id_product     TEXT NOT NULL
,language_code TEXT
,name          TEXT
,id_reference   TEXT
);

CREATE TABLE product_type(
id_product   TEXT NOT NULL
,id_type     INTEGER(0)
,id_reference TEXT
);

CREATE TABLE type(
id_type        INTEGER(0) NOT NULL
,language_code TEXT
,name          TEXT
);

CREATE TABLE object_group(
id_object     TEXT NOT NULL
,group_name   TEXT NOT NULL
,id_reference TEXT
);

CREATE TABLE object_name(
id_object      TEXT NOT NULL
,language_code TEXT
,name          TEXT
,id_reference  TEXT
);

CREATE TABLE organisation(
id_organisation TEXT NOT NULL
,description    TEXT NOT NULL
,id_reference   TEXT
);

CREATE TABLE organisation_hierarchy(
id_organisation_below  TEXT NOT NULL
,id_organisation_above TEXT NOT NULL
,type                  TEXT
,start_year            TEXT
,start_month           TEXT
,start_day             TEXT
,end_year              TEXT
,end_month             TEXT
,end_day               TEXT
,id_reference          TEXT
);

CREATE TABLE person(
id_person     TEXT NOT NULL
,description  TEXT NOT NULL
,sex          TEXT
,id_reference TEXT
);

CREATE TABLE person_creature(
id_person     TEXT NOT NULL
,id_creature  TEXT NOT NULL
,id_reference TEXT
);

CREATE TABLE virtual_entity_name(
id_virtual_entity TEXT NOT NULL
,name             TEXT
,id_reference     TEXT
);

CREATE TABLE virtual_entity_shadowtalker(
id_virtual_entity TEXT NOT NULL
,id_text          TEXT NOT NULL
,id_reference     TEXT
);

Beispielimplementierung - SQLite

Anleitung - Erstellung der Datenbank

Voraussetzung: SQLite ist installiert.

• Datenbank-Dateien herunterladen.
• Obenstehendes Skript in die Datei create_tables.sql kopieren.
• Import-Skript in die Datei import.sql schreiben. Inhalt (kürzen, falls nicht alle Daten eingefügt werden müssen):

.separator "\t"
.import ShxDB_contributor.txt contributor
.import ShxDB_contributor_contributors_catalog.txt contributor_catalog
.import ShxDB_contributor_referenced_name contributor_referenced_name
.import ShxDB_contributor_weblink.txt contributor_weblink
.import ShxDB_contributor_weblink_hosting_service.txt contributor_weblink_hosting_service
.import ShxDB_source.txt source
.import ShxDB_source_catalog.txt source_catalog
.import ShxDB_source_date.txt source_date
.import ShxDB_source_date_ingame.txt source_date_ingame
.import ShxDB_source_edition.txt source_edition
.import ShxDB_source_page_number.txt source_page_number
.import ShxDB_source_part.txt source_part
.import ShxDB_source_title.txt source_title
.import ShxDB_source_translated.txt source_translated
.import ShxDB_contribution.txt contribution
.import ShxDB_contribution_illustration.txt contribution_illustration
.import ShxDB_contribution_text.txt contribution_text
.import ShxDB_illustration_catalog.txt illustration_catalog
.import ShxDB_illustration_source.txt illustration_source
.import ShxDB_illustration_type.txt illustration_type
.import ShxDB_illustration_weblink.txt illustration_weblink
.import ShxDB_index_creature.txt index_creature
.import ShxDB_index_product.txt index_product
.import ShxDB_index_illustration_person.txt index_illustration_person
.import ShxDB_index_text_document.txt index_text_document

.import ShxDB_corporation_name.txt corporation_name
.import ShxDB_corporation_ownership.txt corporation_ownership
.import ShxDB_creature_name.txt creature_name
.import ShxDB_document_producer_organisation document_producer_organisation
.import ShxDB_document_producer_person document_producer_person
.import ShxDB_document_title document_title
.import ShxDB_object_group.txt object_group
.import ShxDB_object_name.txt object_name
.import ShxDB_organisation organisation
.import ShxDB_organisation_hierarchy organisation_hierarchy
.import ShxDB_person.txt person
.import ShxDB_person_creature.txt person_creature
.import ShxDB_product_name.txt product_name
.import ShxDB_product_type.txt product_type
.import ShxDB_type.txt type
.import ShxDB_virtual_entity_name.txt virtual_entity_name
.import ShxDB_virtual_entity_shadowtalk.txt virtual_entity_shadowtalk

• Alle Dateien in ein Verzeichnis zusammenführen.
• Neue Datenbank anlegen ggf. systemabhängig aber normalerweise durch Aufruf des SQLite-Kommandos mit Datenbankname, z.B.

> sqlite shx.db

• In SQlite die Tabellen durch Ausführen von create_tables.sql erstellen.

sqlite> .read create_tables.sql

• In SQlite den Inhalt der Datenbankdateien durch Ausführen von import.sql in die Tabellen importieren.

sqlite> .read import.sql