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
|