<?php
//
//NULL. The value is a NULL value.
//INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
//REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
//TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
//BLOB. The value is a blob of data, stored exactly as it was input.


//return arrays and other associative arrays nothing else;
function &get_db($read_write_mode) {
	$ROOT=''; //save root of web directory TODO somewhere behind public www
	//$read_write_mode=SQLITE3_OPEN_READONLY | SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE
	$db = new SQLite3($ROOT.'tao.sqlite3',$read_write_mode);//pdo create sqlite table
	$db->busyTimeout(60000);//60 seconds
	$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html 
	$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry;
	$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier
	$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

	return $db;	
}


//* Object APIs

//Object APIs provides operations to allocate a new object and id, and to retrieve, update or delete the object associated with an id.

function db_create_object($object_type, $key_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$insert__sql = $db->prepare( 'INSERT INTO object_table (object_type, key_value) VALUES (?,?)');
	$insert__sql->bindValue(1, $object_type, SQLITE3_TEXT); 
	$insert__sql->bindValue(2, $key_value, SQLITE3_TEXT); 
	$insert__sql->execute();
	$last_insert_row_id = $db->lastInsertRowID();
	$db->close();
	return $last_insert_row_id;
}

function db_get_object($object_id) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM object_table WHERE object_id = ?;");
	$select_query->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$select_query_result = $select_query->execute();
	$array_return = array();
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			$array_return[]=$arrayrow;
		}
	}
	$db->close();
	return $array_return;
}

function db_delete_object($object_id) {
	//might need to cascade delete manually here!! or duplicate values TODO
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$delete__sql = $db->prepare( 'DELETE FROM object_table WHERE object_id = ?;');
	$delete__sql->bindValue(1, $object_id, SQLITE3_INTEGER); 
	$delete__sql->execute();
	$db->close();	
}

function db_update_object_value($object_id, $key_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$update__sql = $db->prepare( 'UPDATE object_table SET key_value=? WHERE object_id = ?');
	$update__sql->bindValue(1, $key_value, SQLITE3_TEXT); 
	$update__sql->bindValue(2, $object_id, SQLITE3_INTEGER); 
	$update__sql->execute();
}

function db_update_object_type_value($object_id, $object_type, $key_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$update__sql = $db->prepare( 'UPDATE object_table SET key_value=?, object_type=? WHERE object_id = ?');
	$update__sql->bindValue(1, $key_value, SQLITE3_TEXT); 
	$update__sql->bindValue(2, $object_type, SQLITE3_TEXT); 
	$update__sql->bindValue(3, $object_id, SQLITE3_INTEGER); 
	$update__sql->execute();
}

//* Association APIs

//Association APIs provides similar operations to add, modify and delete an association. However it provides a much richer set of APIs to query for associations. Some examples are


function db_create_association($source_object, $destination_object, $association_type, $key_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$insert__sql = $db->prepare( 'INSERT INTO association_table (source_object, destination_object, association_type, key_value) VALUES (?,?,?,?)');
	$insert__sql->bindValue(1, $source_object, SQLITE3_INTEGER); 
	$insert__sql->bindValue(2, $destination_object, SQLITE3_INTEGER); 
	$insert__sql->bindValue(3, $association_type, SQLITE3_TEXT); 
	$insert__sql->bindValue(4, $key_value, SQLITE3_TEXT); 
	$insert__sql->execute();
	$last_insert_row_id = $db->lastInsertRowID();
	$db->close();
	return $last_insert_row_id;
}

function db_get_association($source_object, $destination_object) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM association_table WHERE source_object=? AND destination_object = ?;");
	$select_query->bindValue(1, $source_object, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $destination_object, SQLITE3_INTEGER); 
	$select_query_result = $select_query->execute();
	$array_return = array();
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			$array_return[]=$arrayrow;
		}
	}
	$db->close();
	return $array_return;
}

function db_delete_association($source_object, $destination_object) {
	//might need to cascade delete manually here!! or duplicate values TODO
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$delete__sql = $db->prepare( 'DELETE FROM association_table WHERE source_object=? AND destination_object = ?;');
	$delete__sql->bindValue(1, $source_object, SQLITE3_INTEGER); 
	$delete__sql->bindValue(2, $destination_object, SQLITE3_INTEGER);
	$delete__sql->execute();
	$db->close();	
}

function db_update_association_value($source_object, $destination_object, $key_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$update__sql = $db->prepare( 'UPDATE association_table SET key_value=? WHERE source_object=? AND destination_object = ?;');
	$update__sql->bindValue(1, $key_value, SQLITE3_TEXT); 
	$update__sql->bindValue(2, $source_object, SQLITE3_INTEGER); 
	$update__sql->bindValue(3, $destination_object, SQLITE3_INTEGER);
	$update__sql->execute();
}

function db_update_association_type_value($source_object, $destination_object, $association_type, $key_value) {
	$db = &get_db(SQLITE3_OPEN_READWRITE);
	$update__sql = $db->prepare( 'UPDATE association_table SET key_value=?,association_type=? WHERE source_object=? AND destination_object = ?;');
	$update__sql->bindValue(1, $key_value, SQLITE3_TEXT); 
	$update__sql->bindValue(2, $association_type, SQLITE3_TEXT); 
	$update__sql->bindValue(3, $source_object, SQLITE3_INTEGER); 
	$update__sql->bindValue(4, $destination_object, SQLITE3_INTEGER);
	$update__sql->execute();
}

//utilities function
function db_assoc_get($source_object, $atype) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT * FROM association_table WHERE source_object=? AND association_type = ?;");
	$select_query->bindValue(1, $source_object, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $atype, SQLITE3_TEXT); 
	$select_query_result = $select_query->execute();
	$array_return = array();
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			$array_return[]=$arrayrow;
		}
	}
	$db->close();
	return $array_return;
}

function db_assoc_count($source_object, $atype) {
	$db = &get_db(SQLITE3_OPEN_READONLY);//get by reference, assign to a variable for re-use
	$select_query = $db->prepare("SELECT count(*) FROM association_table WHERE source_object=? AND association_type = ?;");
	$select_query->bindValue(1, $source_object, SQLITE3_INTEGER); 
	$select_query->bindValue(2, $atype, SQLITE3_TEXT); 
	$select_query_result = $select_query->execute();
	$array_return = array();
	if( is_a($select_query_result, 'SQLite3Result' )  )
	{
		while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){
			$array_return[]=$arrayrow;
		}
	}
	$db->close();
	return $array_return;
}

function reset_db() {
	//cannot just use open create : error bad parameter or other API misuse
	$db = &get_db(SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);//pdo create sqlite table
	

	//always HAVE SQL CASCADE DELETE ON otherwise primary keys will be re-used, leading to duplicates 

	//32 | “Post” | {“text”: “What a time to be alive”}  DATE_TIME created, modified?
	$db->exec( 'DROP table IF EXISTS object_table;');
	$db->exec( 'CREATE TABLE IF NOT EXISTS object_table ( object_id INTEGER PRIMARY KEY, object_type TEXT, key_value TEXT);');
	
	//32 | 31 | Comment | null
	$db->exec( 'DROP table IF EXISTS association_table;');
	$db->exec( 'CREATE TABLE IF NOT EXISTS association_table ( source_object INTEGER, destination_object INTEGER, association_type TEXT, key_value TEXT );');
	

	$db->close(); //always close
}	
?>