webmcp

annotate demo-app/db/schema.sql @ 354:e7e51bcf47d1

find changesets by author, revision, files, or words in the commit message
Fixed comment
author jbe
date Thu Mar 26 19:02:19 2015 +0100 (2015-03-26)
parents 9fdfb27f8e67
children
rev   line source
jbe/bsw@0 1 -- only needed for database driven tempstore (see application config)
jbe/bsw@0 2 CREATE TABLE "tempstore" (
jbe/bsw@0 3 "key" TEXT PRIMARY KEY,
jbe/bsw@0 4 "data" BYTEA NOT NULL );
jbe/bsw@0 5
jbe/bsw@0 6 -- Attention: USER is a reserved word in PostgreSQL. We use it anyway in
jbe/bsw@0 7 -- this example. Don't forget the double quotes where neccessary.
jbe/bsw@0 8 CREATE TABLE "user" (
jbe/bsw@0 9 "id" SERIAL8 PRIMARY KEY,
jbe/bsw@0 10 "ident" TEXT NOT NULL,
jbe/bsw@0 11 "password" TEXT,
jbe/bsw@0 12 "name" TEXT,
jbe/bsw@0 13 "lang" TEXT,
jbe/bsw@0 14 "write_priv" BOOLEAN NOT NULL DEFAULT FALSE,
jbe/bsw@0 15 "admin" BOOLEAN NOT NULL DEFAULT FALSE );
jbe/bsw@0 16
jbe/bsw@0 17 CREATE TABLE "session" (
jbe/bsw@0 18 "ident" TEXT PRIMARY KEY,
jbe/bsw@0 19 "csrf_secret" TEXT NOT NULL,
jbe/bsw@0 20 "expiry" TIMESTAMPTZ NOT NULL DEFAULT NOW() + '24 hours',
jbe/bsw@0 21 "user_id" INT8 REFERENCES "user" ("id") ON DELETE SET NULL ON UPDATE CASCADE );
jbe/bsw@0 22
jbe/bsw@0 23 CREATE TABLE "media_type" (
jbe/bsw@0 24 "id" SERIAL8 PRIMARY KEY,
jbe/bsw@0 25 "name" TEXT NOT NULL,
jbe/bsw@0 26 "description" TEXT );
jbe/bsw@0 27
jbe/bsw@0 28 CREATE TABLE "genre" (
jbe/bsw@0 29 "id" SERIAL8 PRIMARY KEY,
jbe/bsw@0 30 "name" TEXT NOT NULL,
jbe/bsw@0 31 "description" TEXT );
jbe/bsw@0 32
jbe/bsw@0 33 CREATE TABLE "medium" (
jbe/bsw@0 34 "id" SERIAL8 PRIMARY KEY,
jbe/bsw@0 35 "media_type_id" INT8 NOT NULL REFERENCES "media_type" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe/bsw@0 36 "name" TEXT NOT NULL,
jbe/bsw@0 37 "copyprotected" BOOLEAN NOT NULL );
jbe/bsw@0 38
jbe/bsw@0 39 CREATE TABLE "classification" (
jbe/bsw@0 40 PRIMARY KEY ("medium_id", "genre_id"),
jbe/bsw@0 41 "medium_id" INT8 REFERENCES "medium" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe/bsw@0 42 "genre_id" INT8 REFERENCES "genre" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe/bsw@0 43
jbe/bsw@0 44 CREATE TABLE "track" (
jbe/bsw@0 45 "id" SERIAL8 PRIMARY KEY,
jbe/bsw@0 46 "medium_id" INT8 NOT NULL REFERENCES "medium" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe/bsw@0 47 "position" INT8 NOT NULL,
jbe/bsw@0 48 "name" TEXT NOT NULL,
jbe/bsw@0 49 "description" TEXT,
jbe/bsw@0 50 "duration" INTERVAL,
jbe/bsw@0 51 UNIQUE ("medium_id", "position") );
jbe/bsw@0 52
jbe/bsw@0 53 INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin")
jbe/bsw@0 54 VALUES ('admin', 'admin', 'Administrator', true, true);
jbe/bsw@0 55
jbe/bsw@0 56 INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin")
jbe/bsw@0 57 VALUES ('user', 'User', 'User', true, false);
jbe/bsw@0 58
jbe/bsw@0 59 INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin")
jbe/bsw@0 60 VALUES ('anon', 'anon', 'Anonymous', false, false);
jbe/bsw@0 61
jbe/bsw@0 62 INSERT INTO "media_type" ("name", "description") VALUES ('CD', '');
jbe/bsw@0 63 INSERT INTO "media_type" ("name", "description") VALUES ('Tape', '');
jbe/bsw@0 64
jbe/bsw@0 65 INSERT INTO "genre" ("name", "description") VALUES ('Klassik', '');
jbe/bsw@0 66 INSERT INTO "genre" ("name", "description") VALUES ('Gospel', '');
jbe/bsw@0 67 INSERT INTO "genre" ("name", "description") VALUES ('Jazz', '');
jbe/bsw@0 68 INSERT INTO "genre" ("name", "description") VALUES ('Traditional', '');
jbe/bsw@0 69 INSERT INTO "genre" ("name", "description") VALUES ('Latin', '');
jbe/bsw@0 70 INSERT INTO "genre" ("name", "description") VALUES ('Blues', '');
jbe/bsw@0 71 INSERT INTO "genre" ("name", "description") VALUES ('Rhythm & blues', '');
jbe/bsw@0 72 INSERT INTO "genre" ("name", "description") VALUES ('Funk', '');
jbe/bsw@0 73 INSERT INTO "genre" ("name", "description") VALUES ('Rock', '');
jbe/bsw@0 74 INSERT INTO "genre" ("name", "description") VALUES ('Pop', '');
jbe/bsw@0 75 INSERT INTO "genre" ("name", "description") VALUES ('Country', '');
jbe/bsw@0 76 INSERT INTO "genre" ("name", "description") VALUES ('Electronic', '');
jbe/bsw@0 77 INSERT INTO "genre" ("name", "description") VALUES ('Ska / Reggea', '');
jbe/bsw@0 78 INSERT INTO "genre" ("name", "description") VALUES ('Hip hop / Rap', '');
jbe/bsw@0 79

Impressum / About Us