/usr/share/samizdat/database/create-pgsql.sql is in samizdat 0.7.0-2.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 | -- Samizdat Database Creation - PostgreSQL
--
-- Copyright (c) 2002-2011 Dmitry Borodaenko <angdraug@debian.org>
--
-- This program is free software.
-- You can distribute/modify this program under the terms of
-- the GNU General Public License version 3 or later.
--
-- RDF Data Model
CREATE TABLE resource (
id SERIAL PRIMARY KEY,
published_date TIMESTAMP WITH TIME ZONE -- received date with site tz
DEFAULT CURRENT_TIMESTAMP NOT NULL,
-- parent resource (subproperty of dct:isPartOf)
part_of INTEGER REFERENCES resource,
part_of_subproperty INTEGER REFERENCES resource,
part_sequence_number INTEGER,
-- resource type and value
literal BOOLEAN DEFAULT false,
uriref BOOLEAN DEFAULT false,
label TEXT); -- literal value | external uriref | internal class name
-- optimize: store external uriref hash in numeric field
CREATE INDEX resource_uriref_idx ON resource (uriref);
CREATE INDEX resource_label_idx ON resource (label);
CREATE INDEX resource_published_date_idx ON resource (published_date);
CREATE INDEX resource_part_of_idx ON resource (part_of);
CREATE TABLE statement (
id INTEGER PRIMARY KEY REFERENCES resource,
subject INTEGER NOT NULL REFERENCES resource,
predicate INTEGER NOT NULL REFERENCES resource,
object INTEGER NOT NULL REFERENCES resource,
rating NUMERIC(4,2)); -- computed from vote
CREATE INDEX statement_subject_idx ON statement (subject);
CREATE INDEX statement_object_idx ON statement (object);
-- Members and Sessions
CREATE TABLE member (
id INTEGER PRIMARY KEY REFERENCES resource,
login TEXT UNIQUE NOT NULL,
full_name TEXT,
email TEXT UNIQUE NOT NULL,
password TEXT,
prefs TEXT,
confirm TEXT UNIQUE,
session TEXT UNIQUE,
login_time TIMESTAMP WITH TIME ZONE,
last_time TIMESTAMP WITH TIME ZONE);
-- Messages and Threads
CREATE TABLE message (
id INTEGER PRIMARY KEY REFERENCES resource,
open BOOLEAN DEFAULT false, -- editing open for all members
hidden BOOLEAN DEFAULT false, -- hidden from public view
locked BOOLEAN,
creator INTEGER REFERENCES member, -- From:
language TEXT, -- language code
title TEXT, -- Subject:
format TEXT, -- MIME type
content TEXT,
html_full TEXT,
html_short TEXT);
CREATE INDEX message_parent_idx ON message (parent);
CREATE INDEX message_version_of_idx ON message (version_of);
-- Voting Data
CREATE TABLE vote (
id INTEGER PRIMARY KEY REFERENCES resource,
proposition INTEGER REFERENCES statement,
member INTEGER REFERENCES member,
rating NUMERIC(2),
UNIQUE (proposition, member));
CREATE INDEX vote_proposition_idx ON vote (proposition);
-- Moderation Actions Log
CREATE TABLE moderation (
action_date TIMESTAMP WITH TIME ZONE
DEFAULT CURRENT_TIMESTAMP PRIMARY KEY,
moderator INTEGER REFERENCES member,
action TEXT,
resource INTEGER REFERENCES resource);
CREATE INDEX moderation_resource_idx ON moderation (resource);
-- Role-based Access Control
CREATE TABLE role (
member INTEGER REFERENCES member,
role TEXT);
CREATE INDEX role_member_idx ON role (member);
-- Transitive Parts Lookup Table
CREATE TABLE part (
id INTEGER REFERENCES resource,
part_of INTEGER REFERENCES resource,
part_of_subproperty INTEGER REFERENCES resource,
distance INTEGER DEFAULT 0 NOT NULL);
CREATE INDEX part_resource_idx ON part (id);
CREATE INDEX part_part_of_idx ON part (part_of);
-- Tag Cache
CREATE TABLE tag (
id INTEGER PRIMARY KEY REFERENCES resource,
nrelated INTEGER,
nrelated_with_subtags INTEGER);
-- Pending Uploads Queue
CREATE TYPE pending_upload_status AS ENUM ('pending', 'confirmed', 'expired');
CREATE TABLE pending_upload (
id SERIAL PRIMARY KEY,
created_date TIMESTAMP WITH TIME ZONE
DEFAULT CURRENT_TIMESTAMP NOT NULL,
login TEXT NOT NULL,
status pending_upload_status DEFAULT 'pending' NOT NULL);
CREATE INDEX pending_upload_status_idx ON pending_upload (login, status);
CREATE TABLE pending_upload_file (
upload INTEGER NOT NULL REFERENCES pending_upload,
part INTEGER,
UNIQUE (upload, part),
format TEXT,
original_filename TEXT);
CREATE INDEX pending_upload_file_upload_idx ON pending_upload_file (upload);
|