webmcp

annotate demo-app/db/schema.sql @ 3:795b764629ca

find changesets by author, revision, files, or words in the commit message
Version 1.0.3

Important bugfix related to internal forwards (Bug was introduced by the restriction of views with underscore prefix in Version 1.0.2)
author jbe
date Thu Dec 10 12:00:00 2009 +0100 (2009-12-10)
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