Link.FYI

Pastebin

Create New My Pastes

Code (PHP) pasted on 2019-01-13, 13:21 Raw Source

  1. <?php
  2.  
  3. //$securePath = '../00_private';
  4. $securePath = getcwd();
  5.  
  6. $db = new PDO('sqlite:' . $securePath . '/finances.sqlite3');
  7. $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  8.  
  9. try {
  10.     $db->exec("
  11.      PRAGMA journal_mode = wal;
  12.      PRAGMA foreign_keys = ON;
  13.    ");
  14.  
  15.     $db->exec("
  16.      CREATE TABLE IF NOT EXISTS account (
  17.        id INTEGER PRIMARY KEY,
  18.        name TEXT
  19.      );
  20.      CREATE UNIQUE INDEX idx_account_name ON account(name);
  21.    ");
  22.  
  23.     $db->exec("
  24.      CREATE TABLE IF NOT EXISTS payer (
  25.        id INTEGER PRIMARY KEY,
  26.        name TEXT
  27.      );
  28.      CREATE UNIQUE INDEX idx_payer_name ON payer(name);
  29.    ");
  30.  
  31.     $db->exec("
  32.      CREATE TABLE IF NOT EXISTS payment (
  33.        id INTEGER PRIMARY KEY,
  34.        account INTEGER,
  35.        payer INTEGER,
  36.        subject TEXT,
  37.        date NUMERIC,
  38.        amount NUMERIC,
  39.        type TEXT,
  40.        FOREIGN KEY(account) REFERENCES account(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  41.        FOREIGN KEY(payer) REFERENCES payer(id) ON UPDATE CASCADE ON DELETE RESTRICT
  42.      );
  43.      CREATE INDEX idx_payment_amount ON payment(date, amount);
  44.      CREATE INDEX idx_payment_subject ON payment(date, subject);
  45.    ");
  46.  
  47.     $db->exec("
  48.      CREATE TABLE IF NOT EXISTS category (
  49.        id INTEGER PRIMARY KEY,
  50.        pid INTEGER,
  51.        name TEXT
  52.      );
  53.      CREATE UNIQUE INDEX idx_category_name ON category(name);
  54.    ");
  55.  
  56.     $db->exec("
  57.      CREATE TABLE IF NOT EXISTS payment2category (
  58.        id INTEGER PRIMARY KEY,
  59.        paymentID INTEGER,
  60.        categoryID INTEGER,
  61.        FOREIGN KEY(paymentID) REFERENCES payment(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  62.        FOREIGN KEY(categoryID) REFERENCES category(id) ON UPDATE CASCADE ON DELETE RESTRICT
  63.      );
  64.      CREATE INDEX idx_p2c_pc ON payment2category(paymentID, categoryID);
  65.      CREATE INDEX idx_p2c_cp ON payment2category(categoryID, paymentID);
  66.    ");
  67. } catch (PDOException $e) {
  68.     echo $e->getMessage();
  69. }