app/DB/db_postgres.sql

   1 DROP TABLE IF EXISTS public."user" CASCADE;
   2 DROP SEQUENCE IF EXISTS public.user_id_seq;
   3 CREATE SEQUENCE public.user_id_seq
   4   INCREMENT 1
   5   MINVALUE 1
   6   MAXVALUE 9223372036854775807
   7   START 1
   8   CACHE 1;
   9 CREATE TABLE public."user"
  10 (
  11   id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
  12   name character varying(255) NOT NULL,
  13   email character varying(255) NOT NULL,
  14   username character varying(255) NOT NULL,
  15   password character varying(255) NOT NULL,
  16   CONSTRAINT user_pkey PRIMARY KEY (id)
  17 );
  18
  19 DROP INDEX IF EXISTS ix_user_email;
  20 CREATE UNIQUE INDEX ix_user_email
  21   ON public."user"
  22   USING btree
  23   (email COLLATE pg_catalog."default");
  24
  25 DROP INDEX IF EXISTS ix_user_username;
  26 CREATE UNIQUE INDEX ix_user_username
  27   ON public."user"
  28   USING btree
  29   (username COLLATE pg_catalog."default");
  30
  31 DROP TABLE IF EXISTS public.role CASCADE;
  32 DROP SEQUENCE IF EXISTS public.role_id_seq;
  33 CREATE SEQUENCE public.role_id_seq
  34   INCREMENT 1
  35   MINVALUE 1
  36   MAXVALUE 9223372036854775807
  37   START 1
  38   CACHE 1;
  39
  40 CREATE TABLE public.role
  41 (
  42   id integer NOT NULL DEFAULT nextval('role_id_seq'::regclass),
  43   role character varying(255),
  44   CONSTRAINT role_pkey PRIMARY KEY (id)
  45 );
  46 insert into role (role) values ('read');
  47 insert into role (role) values ('write');
  48 insert into role (role) values ('admin');
  49
  50 DROP TABLE IF EXISTS public.accessright CASCADE;
  51 DROP SEQUENCE IF EXISTS public.accessright_id_seq;
  52 CREATE SEQUENCE public.accessright_id_seq
  53   INCREMENT 1
  54   MINVALUE 1
  55   MAXVALUE 9223372036854775807
  56   START 1
  57   CACHE 1;
  58
  59 CREATE TABLE public.accessright
  60 (
  61   id integer NOT NULL DEFAULT nextval('accessright_id_seq'::regclass),
  62   perm integer NOT NULL,
  63   "user" integer NOT NULL,
  64   CONSTRAINT accessright_pkey PRIMARY KEY (id),
  65   CONSTRAINT accessright_userid_fkey FOREIGN KEY ("user")
  66       REFERENCES public."user" (id) MATCH SIMPLE
  67       ON UPDATE CASCADE ON DELETE CASCADE,
  68   CONSTRAINT accessright_perm_fkey FOREIGN KEY (perm)
  69       REFERENCES public.role (id) MATCH SIMPLE
  70       ON UPDATE CASCADE ON DELETE CASCADE
  71 );
  72
  73 DROP TABLE IF EXISTS public.portfolio CASCADE;
  74 DROP SEQUENCE IF EXISTS public.portfolio_id_seq;
  75 CREATE SEQUENCE public.portfolio_id_seq
  76   INCREMENT 1
  77   MINVALUE 1
  78   MAXVALUE 9223372036854775807
  79   START 1
  80   CACHE 1;
  81
  82 CREATE TABLE public.portfolio
  83 (
  84   id integer NOT NULL DEFAULT nextval('portfolio_id_seq'::regclass),
  85   name character varying(255),
  86   "user" integer NOT NULL,
  87   public boolean NOT NULL DEFAULT true,
  88   visible boolean NOT NULL DEFAULT true,
  89   CONSTRAINT portfolio_pkey PRIMARY KEY (id),
  90   CONSTRAINT portfolio_userid_fkey FOREIGN KEY ("user")
  91       REFERENCES public."user" (id) MATCH SIMPLE
  92       ON UPDATE CASCADE ON DELETE CASCADE
  93 );
  94
  95 DROP TABLE IF EXISTS public.album CASCADE;
  96 DROP SEQUENCE IF EXISTS public.album_id_seq;
  97 CREATE SEQUENCE public.album_id_seq
  98   INCREMENT 1
  99   MINVALUE 1
 100   MAXVALUE 9223372036854775807
 101   START 1
 102   CACHE 1;
 103
 104 CREATE TABLE public.album
 105 (
 106   id integer NOT NULL DEFAULT nextval('album_id_seq'::regclass),
 107   name character varying(255),
 108   visible boolean NOT NULL,
 109   public boolean NOT NULL,
 110   portfolioid integer NOT NULL,
 111   CONSTRAINT album_pkey PRIMARY KEY (id),
 112   CONSTRAINT album_portfolioid_fkey FOREIGN KEY (portfolioid)
 113       REFERENCES public.portfolio (id) MATCH SIMPLE
 114       ON UPDATE CASCADE ON DELETE CASCADE
 115 );
 116
 117 DROP TABLE IF EXISTS public.format CASCADE;
 118 DROP SEQUENCE IF EXISTS public.format_id_seq;
 119 CREATE SEQUENCE public.format_id_seq
 120   INCREMENT 1
 121   MINVALUE 1
 122   MAXVALUE 9223372036854775807
 123   START 1
 124   CACHE 1;
 125
 126 CREATE TABLE public.format
 127 (
 128   id integer NOT NULL DEFAULT nextval('format_id_seq'::regclass),
 129   format character varying(255),
 130   CONSTRAINT format_pkey PRIMARY KEY (id)
 131 );
 132 insert into format (format) values ('jpg');
 133 insert into format (format) values ('raw');
 134 insert into format (format) values ('tiff');
 135
 136 DROP TABLE IF EXISTS public.photo CASCADE;
 137 DROP SEQUENCE IF EXISTS public.photo_id_seq;
 138 CREATE SEQUENCE public.photo_id_seq
 139   INCREMENT 1
 140   MINVALUE 1
 141   MAXVALUE 9223372036854775807
 142   START 1
 143   CACHE 1;
 144
 145 CREATE TABLE public.photo
 146 (
 147   id integer NOT NULL DEFAULT nextval('photo_id_seq'::regclass),
 148   file character varying(255),
 149   name character varying(255),
 150   exif text,
 151   format integer NOT NULL,
 152   albumid integer NOT NULL,
 153   visible boolean NOT NULL,
 154   public boolean NOT NULL,
 155   CONSTRAINT photo_pkey PRIMARY KEY (id),
 156   CONSTRAINT photo_albumid_fkey FOREIGN KEY (albumid)
 157       REFERENCES public.album (id) MATCH SIMPLE
 158       ON UPDATE CASCADE ON DELETE CASCADE,
 159   CONSTRAINT photo_format_fkey FOREIGN KEY (format)
 160       REFERENCES public.format (id) MATCH SIMPLE
 161       ON UPDATE CASCADE ON DELETE CASCADE
 162 );
 163