Database Structure

CREATE TABLE mus_mobile_app (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    sign_private_key VARCHAR(255) NOT NULL,
    sign_public_key VARCHAR(255) NOT NULL
);

CREATE TABLE mus_mobile_domain (
    id INTEGER PRIMARY KEY,
    app_id INTEGER NOT NULL,
    domain VARCHAR(255) NOT NULL
);

CREATE TABLE mus_certificate (
    id INTEGER PRIMARY KEY,
    pem TEXT NOT NULL,
    fingerprint VARCHAR(255) NOT NULL,
    expires INTEGER NOT NULL,
    mobile_domain_id INTEGER NOT NULL
);

CREATE TABLE mus_user (
    id INTEGER PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    enabled BOOLEAN NOT NULL
);

CREATE TABLE mus_user_authority (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE,
    authority VARCHAR(255) NOT NULL
);

create table mus_mobile_app_version
(
    id                integer      not null primary key,
    app_id            integer      not null,
    platform          varchar(10)  not null,
    major_os_version  integer,
    suggested_version varchar(24),
    required_version  varchar(24),
    message_key       varchar(255)
);

create table mus_localized_text
(
    message_key varchar(255) not null,
    language    varchar(2)   not null,
    text        text         not null,
    primary key (message_key, language)
);

CREATE SEQUENCE IF NOT EXISTS mus_mobile_app_seq INCREMENT BY 1 START WITH 1 CACHE 20;

CREATE SEQUENCE IF NOT EXISTS mus_mobile_domain_seq INCREMENT BY 1 START WITH 1 CACHE 20;

CREATE SEQUENCE IF NOT EXISTS mus_certificates_seq INCREMENT BY 1 START WITH 1 CACHE 20;

CREATE SEQUENCE IF NOT EXISTS mus_mobile_app_version_seq INCREMENT BY 1 START WITH 1 CACHE 20;

ALTER TABLE mus_mobile_fingerprint
    ADD CONSTRAINT mobile_ssl_pinning_app_fk FOREIGN KEY (mobile_domain_id)
        REFERENCES mus_mobile_domain ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE mus_mobile_domain
    ADD CONSTRAINT mus_mobile_domain_fk FOREIGN KEY (app_id)
        REFERENCES mus_mobile_app ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE mus_user_authority
    ADD CONSTRAINT mus_user_fk FOREIGN KEY (app_id)
        REFERENCES mus_user ON UPDATE CASCADE ON DELETE CASCADE;