Setup Database

Setting up the SQL tables

If you have a running MySQL or MariaDB Server instance you just need to create a new database to store all the data.

Last step to do is running the following SQL query to create all the required tables.

CREATE TABLE IF NOT EXISTS users (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username     VARCHAR(255)  COLLATE ascii_general_ci    NOT NULL,
    password     VARCHAR(255)  COLLATE utf8_general_ci     NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
    session_id   CHAR(8)       COLLATE ascii_bin           NOT NULL PRIMARY KEY,
    user_id      BIGINT        UNSIGNED                    NOT NULL,
    created      DATETIME                                  NOT NULL,
    last_sync    DATETIME                                  NOT NULL,

    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_databases (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(255)  COLLATE utf8_general_ci     NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS locations (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(255)  COLLATE utf8mb4_unicode_ci  NOT NULL UNIQUE,
    database_id  BIGINT        UNSIGNED                    NOT NULL,

    FOREIGN KEY(database_id) REFERENCES item_databases(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS tags (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(255)  COLLATE utf8mb4_unicode_ci  NOT NULL UNIQUE,
    color        INTEGER       UNSIGNED                    NOT NULL,
    icon         INTEGER       UNSIGNED                        NULL DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS items (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(255)  COLLATE utf8mb4_unicode_ci  NOT NULL UNIQUE,
    description  TEXT          COLLATE utf8mb4_unicode_ci  NOT NULL,
    image        VARCHAR(255)  COLLATE utf8mb4_unicode_ci      NULL DEFAULT NULL,
    location_id  BIGINT        UNSIGNED                    NOT NULL,
    amount       BIGINT        UNSIGNED                    NOT NULL,
    last_edited  DATETIME                                  NOT NULL,
    created      DATETIME                                  NOT NULL,

    FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_tags (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    item_id      BIGINT        UNSIGNED                    NOT NULL,
    tag_id       BIGINT        UNSIGNED                    NOT NULL,

    FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY(tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_properties (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    item_id      BIGINT        UNSIGNED                    NOT NULL,
    is_custom    BOOLEAN                                   NOT NULL,
    name         VARCHAR(255)  COLLATE utf8mb4_unicode_ci  NOT NULL,
    value        TEXT          COLLATE utf8mb4_unicode_ci  NOT NULL,

    FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_attachments (
    id           BIGINT        UNSIGNED                    NOT NULL PRIMARY KEY AUTO_INCREMENT,
    item_id      BIGINT        UNSIGNED                    NOT NULL,
    name         VARCHAR(255)  COLLATE utf8mb4_unicode_ci  NOT NULL,
    url          TEXT          COLLATE utf8mb4_unicode_ci  NOT NULL,

    FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_deleted (
    item_id      BIGINT        UNSIGNED                    NOT NULL,
    deleted      DATETIME                                  NOT NULL
);