<?php 
//CREATE OR ACCESS SQL TABLE
$ROOT='/home/insertusername';
$db = new PDO('sqlite:'.$ROOT.'/texteditor.sqlite3');
//SETTING PRAGMA USING PDO NOT SQLITE3
$db->query('PRAGMA journal_mode = WAL;'); //https://sqlite.org/wal.html
$db->query('PRAGMA busy_timeout = 5000;');//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->exec( 'CREATE TABLE IF NOT EXISTS texteditor ( doc_id INTEGER PRIMARY KEY, doc_text TEXT NOT NULL );');

//INSERT OR ADD VALUES
$qry = $db->prepare( 'INSERT INTO texteditor (doc_text) VALUES (?)');
$textvalue='hi there'.date("Y-m-d",time());
$qry->bindValue(1, $textvalue, SQLITE3_TEXT);
$res = $qry->execute();
echo "INSERT records:".$res."<br />";

//RETRIEVE VALUES
$stmt = $db->prepare('SELECT * FROM texteditor WHERE doc_id = ?');
$stmt->bindValue(1, $db->lastInsertId(), SQLITE3_INTEGER);
$stmt->execute();
$result = $stmt->fetchAll();
echo "RETRIEVE last records:".print_r($result,true)."<br />";

foreach ($result as $cheek) {
  echo $cheek['doc_text'];
}

//UPDATE VALUES
$update_statement = $db->prepare('UPDATE texteditor SET doc_text = ? WHERE doc_id = ?;');
$update_statement->bindValue(2, $db->lastInsertId(), SQLITE3_INTEGER);
$update_statement->bindValue(1, "join_these_two".htmlspecialchars("<b>\"bold&</b>"), SQLITE3_TEXT);
$result = $update_statement->execute();
echo "UPDATE last records:".$result."<br />";


//RETRIEVE VALUES
$res = $db->query('SELECT * FROM texteditor', PDO::FETCH_ASSOC);
foreach ($res as $cheek) {
  echo $cheek['doc_id']." ".$cheek['doc_text'].'<br />';
}

//DELETE LAST VALUES INSERTED
$update = $db->prepare( 'INSERT INTO texteditor (doc_text) VALUES (?)');
$result = $update->execute(array($textvalue));
echo "INSERT".$result."<br />";

$delete_statement = $db->prepare('DELETE FROM texteditor WHERE doc_text = ?;');
$delete_statement->bindValue(1, $db->lastInsertId(), SQLITE3_INTEGER);
$result = $delete_statement->execute();
echo "DELETE".$result."<br />";


//SCALAR VALUES
$stmt = $db->prepare('SELECT count(*) FROM texteditor WHERE doc_id >= 2 ORDER BY doc_id LIMIT :limit');
$stmt->bindValue('limit', 7, SQLITE3_INTEGER);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); //https://www.php.net/manual/en/pdostatement.fetchall.php
echo "RETRIEVE count(*):".print_r($result,true)."=large<br />";
echo "RETRIEVE count(*):".$result[0]."=large<br />";
echo "RETRIEVE number of rows in result:".count($result)."=scalar 1<br />";

//DELETE new ORDERS, KEEPING LIMIT TO 25


if($result[0] >= 25) {
    $delete_statement = $db->prepare('DELETE FROM texteditor WHERE doc_id not in 
    (SELECT doc_id FROM texteditor ORDER BY doc_id DESC LIMIT 25);');
    $result = $delete_statement->execute();
    echo "DELETED many".print_r($result)."<br />";
}
//PAGINATE
//https://www2.sqlite.org/cvstrac/wiki?p=ScrollingCursor

$stmt = $db->prepare('SELECT * FROM texteditor WHERE doc_id >= :doc_id ORDER BY doc_id LIMIT 5');
$doc_id = 10;
$stmt->bindParam('doc_id', $doc_id, SQLITE3_INTEGER);
$stmt->execute();
$result = $stmt->fetchAll();
echo "PAGINATION using where:<br />";

foreach ($result as $cheek) {
  echo $cheek['doc_id']." ".$cheek['doc_text'].'<br />';
}


foreach ( ($db->query('PRAGMA journal_mode;') ) as $cheek) {
  echo print_r($cheek).'<br />';
}
foreach ( ($db->query('PRAGMA busy_timeout;') ) as $cheek) {
  echo print_r($cheek).'<br />';
}
foreach ( ($db->query('PRAGMA cell_size_check;') ) as $cheek) {
  echo print_r($cheek).'<br />';
}

//benchmark simulate multiple connections and multiple test using ab tool linux, 100 connections, 50 concurrently ;ApacheBench 100%   2seconds (longest request)
//sudo apt install apache2-utils 
//ab -n 100 -c 50 https://example.com/sqlite.php

//https://www.php.net/manual/en/pdo.lobs.php

//close db
$db = null;

//WORKING WITH BLOBS or BINARY data in SQLite
//STORAGE PNG IMAGE in sqlite database
//19000 in sql files versus 100000 inodes
//CREATE OR ACCESS SQL TABLE
//https://www.php.net/manual/en/pdo.lobs.php
$db = new PDO('sqlite:/home/steeldoo/photostorage.sqlite3');
$db->query('PRAGMA journal_mode = WAL;'); //write ahead logging
$db->query('PRAGMA busy_timeout = 5000;');
$db->query('PRAGMA cell_size_check = ON;'); 

//CREATE table using BLOB data type
$db->exec( 'CREATE TABLE IF NOT EXISTS photostorage ( doc_id INTEGER PRIMARY KEY, doc_text TEXT NOT NULL, doc_image BLOB );');


//INSERT using PARAM_LOB large objects.  

//CREATE Temporary file
$tmpfile = tempnam($ROOT."/tmp", "img"); //Create file with unique file name
echo 'Temporary image file intialized<br />';

//CREATE sample image using GD
$im = @imagecreate(110, 20)
    or die("Cannot Initialize new GD image stream");
$background_color = imagecolorallocate($im, 0, 0, 0);
$text_color = imagecolorallocate($im, 233, 14, 91);
imagestring($im, 1, 5, 5,  'Symphony installs gd correctly'.date("Y-m-d",time()), $text_color);
imagepng($im, $tmpfile);
imagedestroy($im); // This function has no effect <8.0. close the resource. 
echo 'Temporary image file saved<br />';


//CREATE INSERT STATEMENT
$stmt = $db->prepare("insert into photostorage ('doc_text', doc_image) values (?, ?)");
$file_pointer = fopen($tmpfile, 'rb');
$doc_text = 'Symphony installs gd correctly'.date("Y-m-d",time());
$stmt->bindParam(1, $doc_text);
$stmt->bindParam(2, $file_pointer, PDO::PARAM_LOB);
//Need transaction since may take long
$db->beginTransaction();
$stmt->execute();
$db->commit();
echo 'Image file uploaded to database<br />';

//DISPLAYING PNG IMAGE from sqlite DATABASE
$stmt = $db->prepare("select doc_id, doc_text, doc_image from photostorage where doc_id=?");
$stmt->execute(array (1) );
$stmt->bindColumn(2, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(3, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND); //returns true and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method 

//read chunks of data PARAM LOB as stream 

//$data = '';
//while (!feof($lob)) {
//    $data .= fread($lob, 8192);
//}

echo "<img src='data:image/png;base64," .  base64_encode($lob)  . "'/>" 

?>