From 60b276d2f04c2b9b1d5f0c0dc1b5f0cc4176ef26 Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Sun, 23 Jul 2006 17:51:53 +0200 Subject: [PATCH 1/1] Add SQL schema. --- sql/pr0n.sql | 62 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 62 insertions(+) create mode 100644 sql/pr0n.sql diff --git a/sql/pr0n.sql b/sql/pr0n.sql new file mode 100644 index 0000000..5407ce8 --- /dev/null +++ b/sql/pr0n.sql @@ -0,0 +1,62 @@ +CREATE TABLE events ( + id character varying NOT NULL PRIMARY KEY, + date character varying NOT NULL, + name character varying NOT NULL, + vhost character varying NOT NULL, + last_picture timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL +); + +CREATE TABLE images ( + id integer serial NOT NULL PRIMARY KEY REFERENCES events(id), + event character varying NOT NULL, + filename character varying NOT NULL, + width integer DEFAULT -1 NOT NULL, + height integer DEFAULT -1 NOT NULL, + uploadedby character varying NOT NULL, + date timestamp without time zone, + takenby character varying NOT NULL, + selected boolean DEFAULT false +); +CREATE UNIQUE INDEX unique_filenames ON images USING btree (event, filename); + +CREATE TABLE deleted_images ( + id integer NOT NULL, + event character varying(32) NOT NULL, + filename character varying(255) NOT NULL, + width integer DEFAULT -1 NOT NULL, + height integer DEFAULT -1 NOT NULL, + uploadedby character varying(32), + date timestamp without time zone, + takenby character varying(32) NOT NULL, + selected boolean +); + +CREATE TABLE fake_files ( + event character varying(32) NOT NULL REFERENCES events(id), + filename character varying(255) NOT NULL, + expires_at timestamp without time zone NOT NULL, + + PRIMARY KEY ( event, filename ) +); + +CREATE TABLE shadow_files ( + event character varying(32) NOT NULL, + filename character varying(255) NOT NULL, + id integer NOT NULL, + expires_at timestamp without time zone NOT NULL +); + +CREATE TABLE users ( + username character varying(32) NOT NULL, + sha1password character(28) NOT NULL, + vhost character varying(32) NOT NULL +); + +GRANT INSERT ON TABLE deleted_images TO pr0n; +GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE events TO pr0n; +GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE fake_files TO pr0n; +GRANT INSERT,SELECT,UPDATE ON TABLE imageid_seq TO pr0n; +GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE images TO pr0n; +GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE shadow_files TO pr0n; +GRANT SELECT ON TABLE users TO pr0n; + -- 2.39.2