schema.sql

DROP DATABASE IF EXISTS nodejs;

CREATE DATABASE nodejs
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci;

USE nodejs;

CREATE TABLE Albums
(
  name VARCHAR(50) UNIQUE PRIMARY KEY,
  title VARCHAR(100),
  date DATETIME,
  description VARCHAR(500),

  -- allow for sorting on date.
  INDEX(date)
)
ENGINE = InnoDB;

CREATE TABLE Photos
(
  album_name VARCHAR(50),
  filename VARCHAR(50),
  description VARCHAR(500),
  date DATETIME,
  path VARCHAR(100),

  FOREIGN KEY (album_name) REFERENCES Albums (name),
  INDEX (album_name, date)
)
ENGINE = InnoDB;


CREATE TABLE Users
(
  user_uuid VARCHAR(50) UNIQUE PRIMARY KEY,
  email VARCHAR(150) UNIQUE,

  display_name VARCHAR(100) UNIQUE,
  password VARCHAR(128),

  first_seen_date BIGINT,
  last_modified_date BIGINT,
  deleted BOOL DEFAULT false,

  INDEX(email, deleted),
  INDEX(user_uuid, deleted)
)
ENGINE = InnoDB;