install-diva.sql

PRAGMA foreign_keys = off;

DROP TABLE IF EXISTS user;
CREATE TABLE user (
    account_ident TEXT NOT NULL,
    domain_ident TEXT NOT NULL,
    passwordhash TEXT NOT NULL,
    nonce TEXT NOT NULL,
    publickey TEXT NOT NULL,
    privatekeyenc TEXT NOT NULL,

    PRIMARY KEY (account_ident)
) WITHOUT ROWID;

DROP TABLE IF EXISTS config;
CREATE TABLE config (
    key TEXT NOT NULL,
    value TEXT NOT NULL,

    PRIMARY KEY (key)
) WITHOUT ROWID;

DROP TABLE IF EXISTS language;
CREATE TABLE language (
    language_ident TEXT NOT NULL,

    PRIMARY KEY (language_ident),
    FOREIGN KEY (language_ident) REFERENCES culture(ident)
) WITHOUT ROWID;

INSERT INTO language (language_ident) VALUES
    ('en'),
    ('de'),
    ('ru'),
    ('zh'),
    ('ko'),
    ('es');

DROP TABLE IF EXISTS culture;
CREATE TABLE culture (
    language_ident TEXT NOT NULL,
    ident TEXT NOT NULL,
    text TEXT NOT NULL,

    PRIMARY KEY (language_ident, ident),
    FOREIGN KEY (language_ident) REFERENCES language(language_ident)
) WITHOUT ROWID;

INSERT INTO culture (language_ident, ident, text) VALUES
    ('en', 'en', 'English'),
    ('de', 'en', 'Englisch'),
    ('en', 'de', 'German'),
    ('de', 'de', 'Deutsch'),
    ('en', 'ru', 'Russian'),
    ('de', 'ru', 'Russisch'),
    ('en', 'zh', 'Chinese'),
    ('de', 'zh', 'Chinesisch'),
    ('en', 'ko', 'Korean'),
    ('de', 'ko', 'Koreanisch'),
    ('en', 'es', 'Spanish'),
    ('de', 'es', 'Spanisch'),
    ('en', 'jsPENDING', 'pending'),
    ('en', 'jsOK', 'ok'),
    ('en', 'jsERROR', 'error'),
    ('en', 'jiUI', 'User Interface'),
    ('en', 'jiAPI', 'Application Programming Interface'),
    ('en', 'BTC_XMR', 'Bitcoin/Monero'),
    ('en', 'BTC_ZEC', 'Bitcoin/ZCash'),
    ('en', 'BTC_ETH', 'Bitcoin/Ethereum'),
    ('en', 'BTC', 'Bitcoin'),
    ('en', 'XMR', 'Monero'),
    ('en', 'ZEC', 'ZCash'),
    ('en', 'ETH', 'Ethereum'),

    ('en', 'Dashboard', 'Dashboard'),
    ('de', 'Dashboard', 'Übersicht'),
    ('en', 'Trade', 'Trade'),
    ('de', 'Trade', 'Handel'),
    ('en', 'Social', 'Social'),
    ('de', 'Social', 'Freunde'),
    ('en', 'Network', 'Network'),
    ('de', 'Network', 'Netzwerk'),
    ('en', 'About', 'About'),
    ('de', 'About', 'Über diva'),
    ('en', 'Config', 'Config'),
    ('de', 'Config', 'Konfiguration'),
    ('en', 'Logout', 'Logout'),
    ('de', 'Logout', 'Abmelden'),

    ('en', 'Nostro', 'My Orders & Trades'),
    ('de', 'Nostro', 'Meine Aufträge & Abschlüsse'),
    ('en', 'NostroOrder', 'Orders'),
    ('de', 'NostroOrder', 'Aufträge'),
    ('en', 'NostroTrade', 'Trades'),
    ('de', 'NostroTrade', 'Abschlüsse'),
    ('en', 'NostroAsset', 'My Assets'),
    ('de', 'NostroAsset', 'Meine Werte'),
    ('en', 'Market', 'Market'),
    ('de', 'Market', 'Markt'),
    ('en', 'AuctionPrices', 'Paid Prices'),
    ('de', 'AuctionPrices', 'Bezahlte Kurse'),

    ('en', 'Buy', 'Buy'),
    ('de', 'Buy', 'Kaufen'),
    ('en', 'Sell', 'Sell'),
    ('de', 'Sell', 'Verkaufen'),
    ('en', 'Buyer', 'Buyer'),
    ('de', 'Buyer', 'Käufer'),
    ('en', 'Seller', 'Seller'),
    ('de', 'Seller', 'Verkäufer'),
    ('en', 'Amount', 'Amount'),
    ('de', 'Amount', 'Menge'),
    ('en', 'Price', 'Price'),
    ('de', 'Price', 'Preis'),

    ('en', 'PriceBuy', 'Price (Buy)'),
    ('de', 'PriceBuy', 'Preis (Kaufen)'),
    ('es', 'PriceBuy', 'Precio (Compra)'),
    ('en', 'PriceSell', 'Price (Sell)'),
    ('de', 'PriceSell', 'Preis (Verkaufen)'),
    ('es', 'PriceSell', 'Precio (Venta)'),

    ('en', 'Hide', 'Hide'),
    ('de', 'Hide', 'Einklappen'),
    ('en', 'Create', 'Create'),
    ('de', 'Create', 'Erstellen'),
    ('en', 'Login', 'Login'),
    ('de', 'Login', 'Anmelden'),
    ('en', 'SignUp', 'Sign Up'),
    ('de', 'SignUp', 'User erstellen'),

    ('en', 'Account', 'Account'),
    ('de', 'Account', 'Benutzer'),
    ('en', 'Role', 'Role'),
    ('de', 'Role', 'Rolle'),
    ('en', 'Karma', 'Karma'),
    ('de', 'Karma', 'Karma'),
    ('en', 'Connection', 'Connection'),
    ('de', 'Connection', 'Verbindung'),

    ('en', 'auth.Error', 'Authentication failed'),
    ('de', 'auth.Error', 'Anmeldung fehlgeschlagen'),

    ('en', 'Password', 'Password'),
    ('de', 'Password', 'Passwort'),
    ('en', 'NewPassword', 'New Password'),
    ('de', 'NewPassword', 'Neues Passwort'),

    ('en', 'newuser.PasswordHelp', 'Between 10 and 32 characters: at least a letter, at least a number and at least a special char'),
    ('de', 'newuser.PasswordHelp', 'Zwischen 10 und 32 Zeichen: davon mindestens ein Buchstabe, eine Zahl und ein Sonderzeichen'),
    ('en', 'newuser.CreatingUser', 'Creating User...'),
    ('de', 'newuser.CreatingUser', 'Erstelle User...'),
    ('en', 'newuser.UserCreated', 'User created, login as'),
    ('de', 'newuser.UserCreated', 'User erstellt, anmelden als'),

    ('en', 'main.DivaTitle', 'diva - Personal Distributed Value Exchange'),
    ('de', 'main.SuperHeroTitle', 'Du bist superheldenmässig unterwegs'),
    ('en', 'main.SuperHeroTitle', 'You Are a Bold Super Hero'),

    ('en', 'Date/Time', 'Date/Time'),
    ('de', 'Date/Time', 'Datum/Zeit'),
    ('es', 'Date/Time', 'Fecha/Hora'),

    ('en', 'abbr_seconds', 's'),
    ('de', 'abbr_seconds', 's'),
    ('es', 'abbr_seconds', 's'),

    ('en', 'abbr_minutes', 'm'),
    ('de', 'abbr_minutes', 'm'),
    ('es', 'abbr_minutes', 'm'),

    ('en', 'abbr_hours', 'h'),
    ('de', 'abbr_hours', 'h'),
    ('es', 'abbr_hours', 'h'),

    ('en', 'abbr_days', 'd'),
    ('de', 'abbr_days', 'd'),
    ('es', 'abbr_days', 'd'),

    ('es', 'en', 'Inglés'),
    ('es', 'de', 'Aleman'),
    ('es', 'ru', 'Ruso'),
    ('es', 'zh', 'Chino'),
    ('es', 'ko', 'Coreano'),
    ('es', 'es', 'Español'),
    ('es', 'jsPENDING', 'pendiente'),
    ('es', 'jsOK', 'ok'),
    ('es', 'jsERROR', 'error'),
    ('es', 'jiUI', 'Interfaz de usuario'),
    ('es', 'jiAPI', 'Interfaz de programación de aplicaciones'),
    ('es', 'BTC_XMR', 'Bitcoin/Monero'),
    ('es', 'BTC_ZEC', 'Bitcoin/ZCash'),
    ('es', 'BTC_ETH', 'Bitcoin/Ethereum'),
    ('es', 'BTC', 'Bitcoin'),
    ('es', 'XMR', 'Monero'),
    ('es', 'ZEC', 'ZCash'),
    ('es', 'ETH', 'Ethereum'),

    ('es', 'Dashboard', 'Tablero'),
    ('es', 'Trade', 'Comercio'),
    ('es', 'Social', 'Social'),
    ('es', 'Network', 'Red'),
    ('es', 'About', 'Acerca de'),
    ('es', 'Config', 'Configuración'),
    ('es', 'Logout', 'Cerrar sesión'),

    ('es', 'Nostro', 'Mis Órdenes y Transacciones'),
    ('es', 'NostroOrder', 'Órdenes'),
    ('es', 'NostroTrade', 'Transacciones'),
    ('es', 'NostroAsset', 'Mis Activos'),
    ('es', 'Market', 'Mercado'),
    ('es', 'AuctionPrices', 'Precios Pagados'),

    ('es', 'Buy', 'Comprar'),
    ('es', 'Sell', 'Vender'),
    ('es', 'Buyer', 'Comprador'),
    ('es', 'Seller', 'Vendedor'),
    ('es', 'Amount', 'Cantidad'),
    ('es', 'Price', 'Precio'),

    ('es', 'Hide', 'Ocultar'),
    ('es', 'Create', 'Crear'),
    ('es', 'Login', 'Iniciar sesión'),
    ('es', 'SignUp', 'Registrarse'),

    ('es', 'Account', 'Cuenta'),
    ('es', 'Role', 'Rol'),
    ('es', 'Karma', 'Karma'),
    ('es', 'Connection', 'Conexión'),

    ('es', 'auth.Error', 'Autenticación fallida'),

    ('es', 'Password', 'Contraseña'),
    ('es', 'NewPassword', 'Nueva Contraseña'),

    ('es', 'newuser.PasswordHelp', 'Entre 10 y 32 caracteres: al menos una letra, al menos un número y al menos un carácter especial'),
    ('es', 'newuser.CreatingUser', 'Creando Usuario...'),
    ('es', 'newuser.UserCreated', 'Usuario creado, iniciar sesión como'),

    ('es', 'main.DivaTitle', 'diva - Intercambio de valor distribuido personal'),
    ('es', 'main.SuperHeroTitle', 'Eres una superheroína audaz');



DROP TABLE IF EXISTS contract;
CREATE TABLE contract (
    contract_ident TEXT NOT NULL,
    precision INTEGER NOT NULL DEFAULT 8,

    PRIMARY KEY (contract_ident),
    FOREIGN KEY (contract_ident) REFERENCES culture(ident)
) WITHOUT ROWID;

INSERT INTO contract (contract_ident, precision) VALUES
    ('BTC_XMR', 8),
    ('BTC_ZEC', 8),
    ('BTC_ETH', 8);

DROP TABLE IF EXISTS orderbook;
CREATE TABLE orderbook (
    account_ident TEXT NOT NULL,
    contract_ident TEXT NOT NULL,
    type TEXT CHECK(type IN ('B', 'A')) NOT NULL,
    timestamp_ms INTEGER NOT NULL,
    price TEXT NOT NULL,
    amount TEXT NOT NULL,
    status TEXT CHECK(status IN ('PA', 'PD', 'C')) NOT NULL,

    PRIMARY KEY (account_ident, contract_ident, type, timestamp_ms),
    FOREIGN KEY (account_ident) REFERENCES user(account_ident),
    FOREIGN KEY (contract_ident) REFERENCES contract(contract_ident)
) WITHOUT ROWID;

DROP TABLE IF EXISTS market;
CREATE TABLE market (
    contract_ident TEXT NOT NULL,
    type TEXT CHECK(type IN ('B', 'A')) NOT NULL,
    price TEXT NOT NULL,
    amount TEXT NOT NULL,

    PRIMARY KEY (contract_ident, type)
) WITHOUT ROWID;

DROP TABLE IF EXISTS asset;
CREATE TABLE asset (
    asset_ident TEXT NOT NULL,

    PRIMARY KEY (asset_ident),
    FOREIGN KEY (asset_ident) REFERENCES culture(ident)
) WITHOUT ROWID;

INSERT INTO asset (asset_ident) VALUES
    ('BTC'),
    ('XMR'),
    ('ETH');

DROP TABLE IF EXISTS diva_chat;

CREATE TABLE diva_chat_messages (
    account_ident TEXT NOT NULL,
    message TEXT NOT NULL,
    timestamp_ms INTEGER NOT NULL,
    sent_received TINYINT NOT NULL,

    PRIMARY KEY (timestamp_ms)
) WITHOUT ROWID;

CREATE TABLE diva_chat_profiles (
    account_ident TEXT NOT NULL,
    avatar TEXT NOT NULL,
    b32_address TEXT NOT NULL,
    pub_key TEXT NOT NULL,
    timestamp_ms INTEGER NOT NULL,
    active TINYINT NOT NULL DEFAULT 0,

    PRIMARY KEY (account_ident)
) WITHOUT ROWID;

PRAGMA foreign_keys = on