InigoValentin/sql/01_structure.sql

DROP DATABASE IF EXISTS web;
CREATE DATABASE web CHARACTER SET utf8 COLLATE utf8_bin;

SET NAMES utf8;

CONNECT web;

CREATE TABLE lang (
    code   VARCHAR(2)  PRIMARY KEY,
    name   VARCHAR(16) NOT NULL,
    active BOOLEAN     NOT NULL DEFAULT 1
);

CREATE TABLE text (
    id      VARCHAR(32)  NOT NULL,
    lang    VARCHAR(2)   NOT NULL REFERENCES lang(code),
    section VARCHAR(32),
    text    VARCHAR(5000),
    file    VARCHAR(32),
    PRIMARY KEY(id, lang)
);
CREATE INDEX i_text ON text(id, lang);

CREATE TABLE user (
    id         INT         AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(32) NOT NULL UNIQUE,
    first_name VARCHAR(64),
    last_name  VARCHAR(64),
    password   VARCHAR(256),
    salt       VARCHAR(256),
    name       VARCHAR(256),
    image      VARCHAR(128),
    admin      BOOLEAN NOT NULL DEFAULT 0
);

CREATE TABLE user_lang (
    user     INT        NOT NULL REFERENCES user(id),
    lang     VARCHAR(2) NOT NULL REFERENCES lang(code),
    priority INT        NOT NULL DEFAULT 0,
    PRIMARY KEY(user, lang)
);

CREATE TABLE user_email (
    user    INT          NOT NULL REFERENCES user(id),
    email   VARCHAR(256) PRIMARY KEY,
    visible BOOLEAN      NOT NULL DEFAULT 0
);

CREATE TABLE user_text (
    user INT         NOT NULL REFERENCES user(id),
    name VARCHAR(32) NOT NULL,
    text VARCHAR(32) NOT NULL,
    PRIMARY KEY(user, name)
);

CREATE TABLE social (
    id        INT          AUTO_INCREMENT PRIMARY KEY,
    site_name VARCHAR(128) NOT NULL UNIQUE,
    site_url  VARCHAR(256),
    logo      VARCHAR(128) NOT NULL,
    text      VARCHAR(32)  NOT NULL REFERENCES text(id)
);

CREATE TABLE user_social (
    id       INT          AUTO_INCREMENT PRIMARY KEY,
    priority INT          NOT NULL DEFAULT 0,
    user     INT          NOT NULL REFERENCES user(id),
    social   INT          NOT NULL REFERENCES social(id),
    url      VARCHAR(256) NOT NULL
);


CREATE TABLE share(
    id      INT          AUTO_INCREMENT PRIMARY KEY,
    idx     INT          NOT NULL UNIQUE,
    visible BOOLEAN      NOT NULL DEFAULT 1,
    title   VARCHAR(32)  NOT NULL UNIQUE REFERENCES text(id),
    text    VARCHAR(32)  REFERENCES text(id),
    icon    VARCHAR(16)  NOT NULL,
    url     VARCHAR(256) NOT NULL
);

CREATE TABLE license (
    id      VARCHAR(32) PRIMARY KEY,
    summary VARCHAR(32) NOT NULL REFERENCES text(id),
    legal   VARCHAR(32) NOT NULL REFERENCES text(id),
    logo    VARCHAR(32),
    icon    VARCHAR(32)
);

CREATE TABLE project_type (
    id      VARCHAR(1)  NOT NULL PRIMARY KEY,
    title   VARCHAR(32) NOT NULL REFERENCES text(id),
    summary VARCHAR(32) NOT NULL REFERENCES text(id)
);

CREATE TABLE project (
    id        INT           AUTO_INCREMENT PRIMARY KEY,
    permalink VARCHAR(300)  NOT NULL,
    user      INT           NOT NULL REFERENCES user(id),
    idx       INT           NOT NULL UNIQUE,
    type      VARCHAR(1)    NOT NULL REFERENCES project_type(id),
    title     VARCHAR(32)   NOT NULL REFERENCES text(id),
    logo      VARCHAR(128),
    header    VARCHAR(32)   NOT NULL REFERENCES text(id),
    text      VARCHAR(32)   REFERENCES text(id),
    comment   VARCHAR(32)   REFERENCES text(id),
    license   VARCHAR(32)   NOT NULL REFERENCES license(id),
    visible   BOOLEAN       NOT NULL DEFAULT 1
);

CREATE TABLE project_url_type (
    id      VARCHAR(1)  PRIMARY KEY,
    title   VARCHAR(32) REFERENCES text(id),
    summary VARCHAR(32) REFERENCES text(id),
    logo    VARCHAR(32)
);

CREATE TABLE project_url (
    id      INT           AUTO_INCREMENT PRIMARY KEY,
    project INT           REFERENCES project(id),
    type    VARCHAR(1)    REFERENCES project_url_type(id),
    url     VARCHAR(1024) NOT NULL
);

CREATE TABLE project_image (
    id      INT          AUTO_INCREMENT PRIMARY KEY,
    project INT          REFERENCES project(id),
    idx     INT          NOT NULL,
    image   VARCHAR(200) NOT NULL,
    alt     VARCHAR(32)
);

CREATE TABLE project_tag (
    project INT          REFERENCES project(id),
    tag     VARCHAR(32)  REFERENCES text(id),
    PRIMARY KEY (project, tag)
);

CREATE TABLE cv (
    id      INT          AUTO_INCREMENT PRIMARY KEY,
    user    INT          NOT NULL REFERENCES user(id),
    lang    VARCHAR(2)   NOT NULL REFERENCES lang(code),
    file    VARCHAR(300) NOT NULL,
    visible BOOLEAN      NOT NULL DEFAULT 1
);

CREATE TABLE settings (
    name    VARCHAR(64)  PRIMARY KEY,
    value   VARCHAR(64)  NOT NULL,
    changed TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE TABLE stat_visit (
    id      INT           AUTO_INCREMENT PRIMARY KEY,
    ip      VARCHAR(50),
    uagent  VARCHAR(400),
    os      VARCHAR(150),
    browser VARCHAR(150),
    dtime   TIMESTAMP     NOT NULL DEFAULT now()
);

CREATE TABLE stat_view (
    id      INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    visit   INT          NOT NULL REFERENCES stat_visit(id),
    section VARCHAR(80)  NOT NULL,
    entry   VARCHAR(100) NOT NULL,
    dtime   TIMESTAMP    NOT NULL DEFAULT now()
);
CREATE INDEX i_visit ON stat_view(visit, dtime);