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 +