initial commit

[pwp.git] / app / DB / db_postgres.sql

DROP TABLE IF EXISTS public."user" CASCADE;
DROP SEQUENCE IF EXISTS public.user_id_seq;
CREATE SEQUENCE public.user_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
CREATE TABLE public."user"
(
  id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
  name character varying(255) NOT NULL,
  email character varying(255) NOT NULL,
  username character varying(255) NOT NULL,
  password character varying(255) NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (id)
);

DROP INDEX IF EXISTS ix_user_email;
CREATE UNIQUE INDEX ix_user_email
  ON public."user"
  USING btree
  (email COLLATE pg_catalog."default");

DROP INDEX IF EXISTS ix_user_username;
CREATE UNIQUE INDEX ix_user_username
  ON public."user"
  USING btree
  (username COLLATE pg_catalog."default");

DROP TABLE IF EXISTS public.role CASCADE;
DROP SEQUENCE IF EXISTS public.role_id_seq;
CREATE SEQUENCE public.role_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE public.role
(
  id integer NOT NULL DEFAULT nextval('role_id_seq'::regclass),
  role character varying(255),
  CONSTRAINT role_pkey PRIMARY KEY (id)
);
insert into role (role) values ('read');
insert into role (role) values ('write');
insert into role (role) values ('admin');

DROP TABLE IF EXISTS public.accessright CASCADE;
DROP SEQUENCE IF EXISTS public.accessright_id_seq;
CREATE SEQUENCE public.accessright_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE public.accessright
(
  id integer NOT NULL DEFAULT nextval('accessright_id_seq'::regclass),
  perm integer NOT NULL,
  "user" integer NOT NULL,
  CONSTRAINT accessright_pkey PRIMARY KEY (id),
  CONSTRAINT accessright_userid_fkey FOREIGN KEY ("user")
      REFERENCES public."user" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT accessright_perm_fkey FOREIGN KEY (perm)
      REFERENCES public.role (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

DROP TABLE IF EXISTS public.portfolio CASCADE;
DROP SEQUENCE IF EXISTS public.portfolio_id_seq;
CREATE SEQUENCE public.portfolio_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE public.portfolio
(
  id integer NOT NULL DEFAULT nextval('portfolio_id_seq'::regclass),
  name character varying(255),
  "user" integer NOT NULL,
  public boolean NOT NULL DEFAULT true,
  visible boolean NOT NULL DEFAULT true,
  CONSTRAINT portfolio_pkey PRIMARY KEY (id),
  CONSTRAINT portfolio_userid_fkey FOREIGN KEY ("user")
      REFERENCES public."user" (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

DROP TABLE IF EXISTS public.album CASCADE;
DROP SEQUENCE IF EXISTS public.album_id_seq;
CREATE SEQUENCE public.album_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE public.album
(
  id integer NOT NULL DEFAULT nextval('album_id_seq'::regclass),
  name character varying(255),
  visible boolean NOT NULL,
  public boolean NOT NULL,
  portfolioid integer NOT NULL,
  CONSTRAINT album_pkey PRIMARY KEY (id),
  CONSTRAINT album_portfolioid_fkey FOREIGN KEY (portfolioid)
      REFERENCES public.portfolio (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

DROP TABLE IF EXISTS public.format CASCADE;
DROP SEQUENCE IF EXISTS public.format_id_seq;
CREATE SEQUENCE public.format_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE public.format
(
  id integer NOT NULL DEFAULT nextval('format_id_seq'::regclass),
  format character varying(255),
  CONSTRAINT format_pkey PRIMARY KEY (id)
);
insert into format (format) values ('jpg');
insert into format (format) values ('raw');
insert into format (format) values ('tiff');

DROP TABLE IF EXISTS public.photo CASCADE;
DROP SEQUENCE IF EXISTS public.photo_id_seq;
CREATE SEQUENCE public.photo_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE public.photo
(
  id integer NOT NULL DEFAULT nextval('photo_id_seq'::regclass),
  file character varying(255),
  name character varying(255),
  exif text,
  format integer NOT NULL,
  albumid integer NOT NULL,
  visible boolean NOT NULL,
  public boolean NOT NULL,
  CONSTRAINT photo_pkey PRIMARY KEY (id),
  CONSTRAINT photo_albumid_fkey FOREIGN KEY (albumid)
      REFERENCES public.album (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT photo_format_fkey FOREIGN KEY (format)
      REFERENCES public.format (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);