webmcp
diff demo-app/db/schema.sql @ 0:9fdfb27f8e67
Version 1.0.0
author | jbe/bsw |
---|---|
date | Sun Oct 25 12:00:00 2009 +0100 (2009-10-25) |
parents | |
children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/demo-app/db/schema.sql Sun Oct 25 12:00:00 2009 +0100 1.3 @@ -0,0 +1,79 @@ 1.4 +-- only needed for database driven tempstore (see application config) 1.5 +CREATE TABLE "tempstore" ( 1.6 + "key" TEXT PRIMARY KEY, 1.7 + "data" BYTEA NOT NULL ); 1.8 + 1.9 +-- Attention: USER is a reserved word in PostgreSQL. We use it anyway in 1.10 +-- this example. Don't forget the double quotes where neccessary. 1.11 +CREATE TABLE "user" ( 1.12 + "id" SERIAL8 PRIMARY KEY, 1.13 + "ident" TEXT NOT NULL, 1.14 + "password" TEXT, 1.15 + "name" TEXT, 1.16 + "lang" TEXT, 1.17 + "write_priv" BOOLEAN NOT NULL DEFAULT FALSE, 1.18 + "admin" BOOLEAN NOT NULL DEFAULT FALSE ); 1.19 + 1.20 +CREATE TABLE "session" ( 1.21 + "ident" TEXT PRIMARY KEY, 1.22 + "csrf_secret" TEXT NOT NULL, 1.23 + "expiry" TIMESTAMPTZ NOT NULL DEFAULT NOW() + '24 hours', 1.24 + "user_id" INT8 REFERENCES "user" ("id") ON DELETE SET NULL ON UPDATE CASCADE ); 1.25 + 1.26 +CREATE TABLE "media_type" ( 1.27 + "id" SERIAL8 PRIMARY KEY, 1.28 + "name" TEXT NOT NULL, 1.29 + "description" TEXT ); 1.30 + 1.31 +CREATE TABLE "genre" ( 1.32 + "id" SERIAL8 PRIMARY KEY, 1.33 + "name" TEXT NOT NULL, 1.34 + "description" TEXT ); 1.35 + 1.36 +CREATE TABLE "medium" ( 1.37 + "id" SERIAL8 PRIMARY KEY, 1.38 + "media_type_id" INT8 NOT NULL REFERENCES "media_type" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.39 + "name" TEXT NOT NULL, 1.40 + "copyprotected" BOOLEAN NOT NULL ); 1.41 + 1.42 +CREATE TABLE "classification" ( 1.43 + PRIMARY KEY ("medium_id", "genre_id"), 1.44 + "medium_id" INT8 REFERENCES "medium" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.45 + "genre_id" INT8 REFERENCES "genre" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.46 + 1.47 +CREATE TABLE "track" ( 1.48 + "id" SERIAL8 PRIMARY KEY, 1.49 + "medium_id" INT8 NOT NULL REFERENCES "medium" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.50 + "position" INT8 NOT NULL, 1.51 + "name" TEXT NOT NULL, 1.52 + "description" TEXT, 1.53 + "duration" INTERVAL, 1.54 + UNIQUE ("medium_id", "position") ); 1.55 + 1.56 +INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin") 1.57 + VALUES ('admin', 'admin', 'Administrator', true, true); 1.58 + 1.59 +INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin") 1.60 + VALUES ('user', 'User', 'User', true, false); 1.61 + 1.62 +INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin") 1.63 + VALUES ('anon', 'anon', 'Anonymous', false, false); 1.64 + 1.65 +INSERT INTO "media_type" ("name", "description") VALUES ('CD', ''); 1.66 +INSERT INTO "media_type" ("name", "description") VALUES ('Tape', ''); 1.67 + 1.68 +INSERT INTO "genre" ("name", "description") VALUES ('Klassik', ''); 1.69 +INSERT INTO "genre" ("name", "description") VALUES ('Gospel', ''); 1.70 +INSERT INTO "genre" ("name", "description") VALUES ('Jazz', ''); 1.71 +INSERT INTO "genre" ("name", "description") VALUES ('Traditional', ''); 1.72 +INSERT INTO "genre" ("name", "description") VALUES ('Latin', ''); 1.73 +INSERT INTO "genre" ("name", "description") VALUES ('Blues', ''); 1.74 +INSERT INTO "genre" ("name", "description") VALUES ('Rhythm & blues', ''); 1.75 +INSERT INTO "genre" ("name", "description") VALUES ('Funk', ''); 1.76 +INSERT INTO "genre" ("name", "description") VALUES ('Rock', ''); 1.77 +INSERT INTO "genre" ("name", "description") VALUES ('Pop', ''); 1.78 +INSERT INTO "genre" ("name", "description") VALUES ('Country', ''); 1.79 +INSERT INTO "genre" ("name", "description") VALUES ('Electronic', ''); 1.80 +INSERT INTO "genre" ("name", "description") VALUES ('Ska / Reggea', ''); 1.81 +INSERT INTO "genre" ("name", "description") VALUES ('Hip hop / Rap', ''); 1.82 +