app/DB/db_mysql.sql

   1 DROP TABLE IF EXISTS photo,format,album,portfolio,accessright,role,user;
   2 CREATE TABLE user
   3 (
   4   id integer NOT NULL AUTO_INCREMENT,
   5   name varchar(255) NOT NULL,
   6   email varchar(150) NOT NULL,
   7   username varchar(150) NOT NULL,
   8   password varchar(255) NOT NULL,
   9   CONSTRAINT user_pkey PRIMARY KEY (id)
  10 ) ENGINE = InnoDB;
  11
  12 DROP INDEX IF EXISTS ix_user_email ON user;
  13 CREATE UNIQUE INDEX ix_user_email ON user (email);
  14
  15 DROP INDEX IF EXISTS ix_user_username ON user;
  16 CREATE UNIQUE INDEX ix_user_username ON user (username);
  17
  18 CREATE TABLE role
  19 (
  20   id integer NOT NULL AUTO_INCREMENT,
  21   role varchar(255),
  22   CONSTRAINT role_pkey PRIMARY KEY (id)
  23 ) ENGINE = InnoDB;
  24 insert into role (role) values ('read');
  25 insert into role (role) values ('write');
  26 insert into role (role) values ('admin');
  27
  28 CREATE TABLE accessright
  29 (
  30   id integer NOT NULL AUTO_INCREMENT,
  31   perm integer NOT NULL,
  32   user integer NOT NULL,
  33   CONSTRAINT accessright_pkey PRIMARY KEY (id),
  34   CONSTRAINT accessright_userid_fkey FOREIGN KEY (user)
  35       REFERENCES user (id) MATCH SIMPLE
  36       ON UPDATE CASCADE ON DELETE CASCADE,
  37   CONSTRAINT accessright_perm_fkey FOREIGN KEY (perm)
  38       REFERENCES role (id) MATCH SIMPLE
  39       ON UPDATE CASCADE ON DELETE CASCADE
  40 ) ENGINE = InnoDB;
  41
  42 CREATE TABLE portfolio
  43 (
  44   id integer NOT NULL AUTO_INCREMENT,
  45   name varchar(255),
  46   user integer NOT NULL,
  47   public boolean NOT NULL DEFAULT true,
  48   visible boolean NOT NULL DEFAULT true,
  49   CONSTRAINT portfolio_pkey PRIMARY KEY (id),
  50   CONSTRAINT portfolio_userid_fkey FOREIGN KEY (user)
  51       REFERENCES user (id) MATCH SIMPLE
  52       ON UPDATE CASCADE ON DELETE CASCADE
  53 ) ENGINE = InnoDB;
  54
  55 CREATE TABLE album
  56 (
  57   id integer NOT NULL AUTO_INCREMENT,
  58   name varchar(255),
  59   visible boolean NOT NULL,
  60   public boolean NOT NULL,
  61   portfolioid integer NOT NULL,
  62   CONSTRAINT album_pkey PRIMARY KEY (id),
  63   CONSTRAINT album_portfolioid_fkey FOREIGN KEY (portfolioid)
  64       REFERENCES portfolio (id) MATCH SIMPLE
  65       ON UPDATE CASCADE ON DELETE CASCADE
  66 ) ENGINE = InnoDB;
  67
  68 CREATE TABLE format
  69 (
  70   id integer NOT NULL AUTO_INCREMENT,
  71   format varchar(255),
  72   CONSTRAINT format_pkey PRIMARY KEY (id)
  73 ) ENGINE = InnoDB;
  74 insert into format (format) values ('jpg');
  75 insert into format (format) values ('raw');
  76 insert into format (format) values ('tiff');
  77
  78 CREATE TABLE photo
  79 (
  80   id integer NOT NULL AUTO_INCREMENT,
  81   file varchar(255),
  82   name varchar(255),
  83   exif varchar(255),
  84   format integer NOT NULL,
  85   albumid integer NOT NULL,
  86   visible boolean NOT NULL,
  87   public boolean NOT NULL,
  88   CONSTRAINT photo_pkey PRIMARY KEY (id),
  89   CONSTRAINT photo_albumid_fkey FOREIGN KEY (albumid)
  90       REFERENCES album (id) MATCH SIMPLE
  91       ON UPDATE CASCADE ON DELETE CASCADE,
  92   CONSTRAINT photo_format_fkey FOREIGN KEY (format)
  93       REFERENCES format (id) MATCH SIMPLE
  94       ON UPDATE CASCADE ON DELETE CASCADE
  95 ) ENGINE = InnoDB;
  96