<?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 SQLite3('sqlite:'.$ROOT.'door_ca.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 doors ( door_id INTEGER PRIMARY KEY, nominal_width INTEGER, nominal_height INTEGER, ga INTEGER, swing TEXT, door_front_dwg TEXT, door_back_dwg TEXT );');


?>