<?php
$ROOT=''; //save root of web directory
//NOTES WAL NOT the problem; problem is primary key goes back to 0 does not globally increment
//thus need cascade delete otherwise will have images in blog_image that have no doc id until create
//orphan files in image table also!!

//need for transactions also, what if cascade delete fails? 
//need for transactions also, imagine a primary key table, multiple readers will read same value and increment; let db handle primary key; or mark items immutable; never delete but cancel
$db = new PDO('sqlite:'.$ROOT.'todo_v14.sqlite3');//pdo create sqlite table watch php is iso but sqllite utf-8 TEXT collate 
$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html synchro problems?? two commits?? locks??
$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry; will it work on PDO vs SQLITE3?
$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier and is less likely to "spread"
$db->query("PRAGMA encoding = 'UTF-8';"); //watch php WHERE clause will not work since ISO encoding vs UTF 8,
$db->query("PRAGMA foreign_keys = ON;");  //off by default , each connection must call this

//https://www.sqlite.org/foreignkeys.html  FOREIGN KEY(thistablename) REFERENCES othertable(unique_or_primary_key)
//not no need to declare type INTEGER if using short hand notation REFERENCES
//create index always of foreign key
//Foreign key actions are similar to triggers in many ways. CAN USE TRIGGER TO CASCADE DELETE
//ALL Foreign key references last items in table  otherwise syntax error
$db->exec( 'CREATE TABLE IF NOT EXISTS accounts ( user_id INTEGER PRIMARY KEY, username TEXT NOT NULL UNIQUE COLLATE NOCASE, userpassword TEXT );');$db->exec( 'CREATE TABLE IF NOT EXISTS blog ( doc_id INTEGER PRIMARY KEY, doc_title TEXT, doc_text TEXT, doc_created_time INTEGER, user_id INTEGER );');
$db->exec( 'CREATE TABLE IF NOT EXISTS session (session_id INTEGER PRIMARY KEY, session_hashid TEXT, user_id INTEGER, time_created INTEGER, FOREIGN KEY(user_id) REFERENCES accounts(user_id) ON DELETE CASCADE );');
$db->exec( 'CREATE TABLE IF NOT EXISTS image ( image_id INTEGER PRIMARY KEY, image_base64 TEXT, image_mimetype TEXT );');
$db->exec( 'CREATE TABLE IF NOT EXISTS blog_image ( blog_image INTEGER PRIMARY KEY, doc_id INTEGER, image_id INTEGER, FOREIGN KEY(doc_id) REFERENCES blog(doc_id) ON DELETE CASCADE, FOREIGN KEY(image_id) REFERENCES image(image_id) ON DELETE CASCADE );');

$db->exec( 'CREATE TABLE IF NOT EXISTS user_preference ( user_preference_id INTEGER PRIMARY KEY, user_id INTEGER, option TEXT, value TEXT, FOREIGN KEY(user_id) REFERENCES accounts(user_id) ON DELETE CASCADE );');

$db->exec( 'CREATE INDEX IF NOT EXISTS session_user_id ON session(user_id);');
$db->exec( 'CREATE INDEX IF NOT EXISTS blog_image_doc_id ON blog_image(doc_id);');
$db->exec( 'CREATE INDEX IF NOT EXISTS blog_image_image_id ON image(image_id);');
$db->exec( 'CREATE INDEX IF NOT EXISTS user_preference_user_id ON accounts(user_id);');

//image is orphanned if no reference, ggood to prevent multiple uploads IF CHECKED, but since not checked waste of database space
//https://www.sqlite.org/lang_createtrigger.html
//INSERT 	NEW. references are valid
//UPDATE 	NEW. and OLD. references are valid
//DELETE 	OLD. references are valid
//NEED TO DO this or will create orphans
$db->exec( 'CREATE TRIGGER IF NOT EXISTS on_delete_blog_image AFTER DELETE ON blog_image BEGIN DELETE FROM image WHERE image.image_id = old.image_id;END;');

?>