initial commit

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

DROP TABLE IF EXISTS photo,format,album,portfolio,accessright,role,user;
CREATE TABLE user
(
  id integer NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  email varchar(150) NOT NULL,
  username varchar(150) NOT NULL,
  password varchar(255) NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY (id)
) ENGINE = InnoDB;

DROP INDEX IF EXISTS ix_user_email ON user;
CREATE UNIQUE INDEX ix_user_email ON user (email);

DROP INDEX IF EXISTS ix_user_username ON user;
CREATE UNIQUE INDEX ix_user_username ON user (username);

CREATE TABLE role
(
  id integer NOT NULL AUTO_INCREMENT,
  role varchar(255),
  CONSTRAINT role_pkey PRIMARY KEY (id)
) ENGINE = InnoDB;
insert into role (role) values ('read');
insert into role (role) values ('write');
insert into role (role) values ('admin');

CREATE TABLE accessright
(
  id integer NOT NULL AUTO_INCREMENT,
  perm integer NOT NULL,
  user integer NOT NULL,
  CONSTRAINT accessright_pkey PRIMARY KEY (id),
  CONSTRAINT accessright_userid_fkey FOREIGN KEY (user)
      REFERENCES user (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT accessright_perm_fkey FOREIGN KEY (perm)
      REFERENCES role (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE portfolio
(
  id integer NOT NULL AUTO_INCREMENT,
  name varchar(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 user (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE album
(
  id integer NOT NULL AUTO_INCREMENT,
  name varchar(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 portfolio (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE format
(
  id integer NOT NULL AUTO_INCREMENT,
  format varchar(255),
  CONSTRAINT format_pkey PRIMARY KEY (id)
) ENGINE = InnoDB;
insert into format (format) values ('jpg');
insert into format (format) values ('raw');
insert into format (format) values ('tiff');

CREATE TABLE photo
(
  id integer NOT NULL AUTO_INCREMENT,
  file varchar(255),
  name varchar(255),
  exif varchar(255),
  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 album (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT photo_format_fkey FOREIGN KEY (format)
      REFERENCES format (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;