webmcp
annotate demo-app/db/schema.sql @ 28:ea2e8f3a2776
allow webmcp path to be set in cgi script
this allows another script to include the webmcp script when the cwd is not the cgi-bin. the script needs to set the WEBMCP_PATH variable.
this allows another script to include the webmcp script when the cwd is not the cgi-bin. the script needs to set the WEBMCP_PATH variable.
author | Daniel Poelzleithner <poelzi@poelzi.org> |
---|---|
date | Sun Sep 19 01:36:08 2010 +0200 (2010-09-19) |
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 |