Skip to content

Instantly share code, notes, and snippets.

@ChrisWeiss
Forked from brettkromkamp/create-database.txt
Created January 14, 2020 17:30
Show Gist options
  • Save ChrisWeiss/bd68df37424e016f5e64499a1a2d553a to your computer and use it in GitHub Desktop.
Save ChrisWeiss/bd68df37424e016f5e64499a1a2d553a to your computer and use it in GitHub Desktop.
Setting up the TopicDB database
sudo -i -u postgres
psql
CREATE USER databasuser WITH PASSWORD 'password';
CREATE DATABASE databasename OWNER databasuser;
\q
psql -h localhost -U databasuser -d databasename -a -f topicmap-definition.sql
exit
sudo -i -u postgres
psql
DROP DATABASE databasename;
CREATE DATABASE databasename OWNER databasuser;
\q
psql -h 127.0.0.1 -U databasuser -d databasename -a -f topicmap-definition.sql
exit
[DATABASE]
Username = databasuser
Password = password
Database = databasename
/* ========== TOPICDB ========== */
CREATE SCHEMA IF NOT EXISTS topicdb;
/* ========== MEMBER ========== */
CREATE TABLE IF NOT EXISTS topicdb.member (
topicmap_identifier INT NOT NULL,
identifier TEXT NOT NULL,
role_spec TEXT NOT NULL,
association_identifier TEXT NOT NULL,
PRIMARY KEY (topicmap_identifier, identifier)
);
CREATE INDEX member_1_index ON topicdb.member (topicmap_identifier, association_identifier);
/* ========== ATTRIBUTE ========== */
CREATE TABLE IF NOT EXISTS topicdb.attribute (
topicmap_identifier INT NOT NULL,
identifier TEXT NOT NULL,
parent_identifier TEXT NOT NULL,
name TEXT NOT NULL,
value TEXT NOT NULL,
data_type TEXT NOT NULL,
scope TEXT NOT NULL,
language TEXT NOT NULL,
PRIMARY KEY (topicmap_identifier, parent_identifier, name, scope, language)
);
CREATE INDEX attribute_1_index ON topicdb.attribute (topicmap_identifier, identifier);
CREATE INDEX attribute_2_index ON topicdb.attribute (topicmap_identifier, parent_identifier);
CREATE INDEX attribute_3_index ON topicdb.attribute (topicmap_identifier, parent_identifier, language);
CREATE INDEX attribute_4_index ON topicdb.attribute (topicmap_identifier, parent_identifier, scope);
CREATE INDEX attribute_5_index ON topicdb.attribute (topicmap_identifier, parent_identifier, scope, language);
/* ========== OCCURRENCE ========== */
CREATE TABLE IF NOT EXISTS topicdb.occurrence (
topicmap_identifier INT NOT NULL,
identifier TEXT NOT NULL,
instance_of TEXT NOT NULL,
scope TEXT NOT NULL,
resource_ref TEXT NOT NULL,
resource_data BYTEA,
topic_identifier TEXT NOT NULL,
language TEXT NOT NULL,
PRIMARY KEY (topicmap_identifier, identifier)
);
CREATE INDEX occurrence_1_index ON topicdb.occurrence (topicmap_identifier, topic_identifier);
CREATE INDEX occurrence_2_index ON topicdb.occurrence (topicmap_identifier, topic_identifier, scope, language);
CREATE INDEX occurrence_3_index ON topicdb.occurrence (topicmap_identifier, topic_identifier, instance_of, scope, language);
/* ========== TOPICREF ========== */
CREATE TABLE IF NOT EXISTS topicdb.topicref (
topicmap_identifier INT NOT NULL,
topic_ref TEXT NOT NULL,
member_identifier TEXT NOT NULL,
PRIMARY KEY (topicmap_identifier, topic_ref, member_identifier)
);
CREATE INDEX topicref_1_index ON topicdb.topicref (topicmap_identifier, member_identifier);
CREATE INDEX topicref_2_index ON topicdb.topicref (topicmap_identifier, topic_ref);
/* ========== TOPIC ========== */
CREATE TABLE IF NOT EXISTS topicdb.topic (
topicmap_identifier INT NOT NULL,
identifier TEXT NOT NULL,
instance_of TEXT NOT NULL,
scope TEXT,
PRIMARY KEY (topicmap_identifier, identifier)
);
CREATE INDEX topic_1_index ON topicdb.topic (topicmap_identifier, identifier, scope);
CREATE INDEX topic_2_index ON topicdb.topic (topicmap_identifier, instance_of, scope);
CREATE INDEX topic_3_index ON topicdb.topic (topicmap_identifier, scope);
/* ========== BASENAME ========== */
CREATE TABLE IF NOT EXISTS topicdb.basename (
topicmap_identifier INT NOT NULL,
identifier TEXT NOT NULL,
name TEXT NOT NULL,
topic_identifier TEXT NOT NULL,
language TEXT NOT NULL,
PRIMARY KEY (topicmap_identifier, identifier)
);
CREATE INDEX basename_1_index ON topicdb.basename (topicmap_identifier, topic_identifier);
/* ========== TOPICMAP ========== */
CREATE SEQUENCE topicdb.topic_map_id_sequence;
CREATE TABLE IF NOT EXISTS topicdb.topicmap (
user_identifier INT NOT NULL,
identifier INT NOT NULL DEFAULT nextval('topicdb.topic_map_id_sequence'),
name TEXT NOT NULL,
description TEXT,
image_path TEXT,
initialised BOOLEAN DEFAULT FALSE NOT NULL,
shared BOOLEAN DEFAULT FALSE NOT NULL,
promoted BOOLEAN DEFAULT FALSE NOT NULL,
PRIMARY KEY (user_identifier, identifier)
);
CREATE INDEX topicmap_1_index ON topicdb.topicmap (identifier);
CREATE INDEX topicmap_2_index ON topicdb.topicmap (shared);
CREATE INDEX topicmap_3_index ON topicdb.topicmap (promoted);
ALTER SEQUENCE topicdb.topic_map_id_sequence OWNED BY topicdb.topicmap.identifier;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment