Link.FYI

Pastebin

Create New My Pastes

Code (PHP) pasted on 2019-01-13, 13:28 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.        name TEXT PRIMARY KEY
  18.      );
  19.    ");
  20.  
  21.     $db->exec("
  22.      CREATE TABLE IF NOT EXISTS payer (
  23.        name TEXT PRIMARY KEY
  24.      );
  25.    ");
  26.  
  27.     $db->exec("
  28.      CREATE TABLE IF NOT EXISTS payment (
  29.        account INTEGER,
  30.        payer INTEGER,
  31.        subject TEXT,
  32.        date NUMERIC,
  33.        amount NUMERIC,
  34.        type TEXT,
  35.        FOREIGN KEY(account) REFERENCES account(rowid) ON UPDATE CASCADE ON DELETE RESTRICT,
  36.        FOREIGN KEY(payer) REFERENCES payer(rowid) ON UPDATE CASCADE ON DELETE RESTRICT
  37.      );
  38.      CREATE INDEX idx_payment_amount ON payment(date, amount);
  39.      CREATE INDEX idx_payment_subject ON payment(date, subject);
  40.    ");
  41.  
  42.     $db->exec("
  43.      CREATE TABLE IF NOT EXISTS category (
  44.        pid INTEGER,
  45.        name TEXT PRIMARY KEY
  46.      );
  47.    ");
  48.  
  49.     $db->exec("
  50.      CREATE TABLE IF NOT EXISTS payment2category (
  51.        paymentID INTEGER,
  52.        categoryID INTEGER,
  53.        FOREIGN KEY(paymentID) REFERENCES payment(rowid) ON UPDATE CASCADE ON DELETE RESTRICT,
  54.        FOREIGN KEY(categoryID) REFERENCES category(rowid) ON UPDATE CASCADE ON DELETE RESTRICT
  55.      );
  56.      CREATE INDEX idx_p2c_pc ON payment2category(paymentID, categoryID);
  57.      CREATE INDEX idx_p2c_cp ON payment2category(categoryID, paymentID);
  58.    ");
  59. } catch (PDOException $e) {
  60.     echo $e->getMessage();
  61. }