jbe/bsw@0: -- only needed for database driven tempstore (see application config)
jbe/bsw@0: CREATE TABLE "tempstore" (
jbe/bsw@0:         "key"           TEXT            PRIMARY KEY,
jbe/bsw@0:         "data"          BYTEA           NOT NULL );
jbe/bsw@0: 
jbe/bsw@0: -- Attention: USER is a reserved word in PostgreSQL. We use it anyway in
jbe/bsw@0: -- this example. Don't forget the double quotes where neccessary.
jbe/bsw@0: CREATE TABLE "user" (
jbe/bsw@0:         "id"            SERIAL8         PRIMARY KEY,
jbe/bsw@0:         "ident"         TEXT            NOT NULL,
jbe/bsw@0:         "password"      TEXT,
jbe/bsw@0:         "name"          TEXT,
jbe/bsw@0:         "lang"          TEXT,
jbe/bsw@0:         "write_priv"    BOOLEAN         NOT NULL DEFAULT FALSE,
jbe/bsw@0:         "admin"         BOOLEAN         NOT NULL DEFAULT FALSE );
jbe/bsw@0: 
jbe/bsw@0: CREATE TABLE "session" (
jbe/bsw@0:         "ident"         TEXT            PRIMARY KEY,
jbe/bsw@0:         "csrf_secret"   TEXT            NOT NULL,
jbe/bsw@0:         "expiry"        TIMESTAMPTZ     NOT NULL DEFAULT NOW() + '24 hours',
jbe/bsw@0:         "user_id"       INT8            REFERENCES "user" ("id") ON DELETE SET NULL ON UPDATE CASCADE );
jbe/bsw@0: 
jbe/bsw@0: CREATE TABLE "media_type" (
jbe/bsw@0:         "id"            SERIAL8         PRIMARY KEY,
jbe/bsw@0:         "name"          TEXT            NOT NULL,
jbe/bsw@0:         "description"   TEXT );
jbe/bsw@0: 
jbe/bsw@0: CREATE TABLE "genre" (
jbe/bsw@0:         "id"            SERIAL8         PRIMARY KEY,
jbe/bsw@0:         "name"          TEXT            NOT NULL,
jbe/bsw@0:         "description"   TEXT );
jbe/bsw@0: 
jbe/bsw@0: CREATE TABLE "medium" (
jbe/bsw@0:         "id"            SERIAL8         PRIMARY KEY,
jbe/bsw@0:         "media_type_id" INT8            NOT NULL REFERENCES "media_type" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe/bsw@0:         "name"          TEXT            NOT NULL,
jbe/bsw@0:         "copyprotected" BOOLEAN         NOT NULL );
jbe/bsw@0: 
jbe/bsw@0: CREATE TABLE "classification" (
jbe/bsw@0:         PRIMARY KEY ("medium_id", "genre_id"),
jbe/bsw@0:         "medium_id"     INT8            REFERENCES "medium" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe/bsw@0:         "genre_id"      INT8            REFERENCES "genre" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe/bsw@0: 
jbe/bsw@0: CREATE TABLE "track" (
jbe/bsw@0:         "id"            SERIAL8         PRIMARY KEY,
jbe/bsw@0:         "medium_id"     INT8            NOT NULL REFERENCES "medium" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe/bsw@0:         "position"      INT8            NOT NULL,
jbe/bsw@0:         "name"          TEXT            NOT NULL,
jbe/bsw@0:         "description"   TEXT,
jbe/bsw@0:         "duration"      INTERVAL,
jbe/bsw@0:         UNIQUE ("medium_id", "position") );
jbe/bsw@0: 
jbe/bsw@0: INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin")
jbe/bsw@0:   VALUES ('admin', 'admin', 'Administrator', true, true);
jbe/bsw@0: 
jbe/bsw@0: INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin")
jbe/bsw@0:   VALUES ('user', 'User', 'User', true, false);
jbe/bsw@0: 
jbe/bsw@0: INSERT INTO "user" ("ident", "password", "name", "write_priv", "admin")
jbe/bsw@0:   VALUES ('anon', 'anon', 'Anonymous', false, false);
jbe/bsw@0: 
jbe/bsw@0: INSERT INTO "media_type" ("name", "description") VALUES ('CD', '');
jbe/bsw@0: INSERT INTO "media_type" ("name", "description") VALUES ('Tape', '');
jbe/bsw@0: 
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Klassik', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Gospel', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Jazz', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Traditional', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Latin', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Blues', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Rhythm & blues', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Funk', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Rock', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Pop', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Country', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Electronic', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Ska / Reggea', '');
jbe/bsw@0: INSERT INTO "genre" ("name", "description") VALUES ('Hip hop / Rap', '');
jbe/bsw@0: