There is one table test that you can query. That table has the following fields:
You can do things like: select * from test, or insert into test (name) value ('something') or delete from test where id=10
You can add only 20 records and then the earliest records are deleted.
worker.main.php
<?php // Demonstrates the use of a worker using AJAX calls. // This is the main program for worker.main.php it uses // worker.worker.js and worker.ajax.php // See worker.ajax.php for a description of the 'test' table in the database 'test'. // Load info from mysitemap.json for use by my framework SiteClass. // Check SiteClass out at https://github.com/bartonlp/site-class. // It has full documentation at that site. $_site = require_once(getenv("SITELOADNAME")); $S = new $_site->className($_site); // $S gives access to my framework. // escapeltgt() is a little utility that change < and > to < > $main = escapeltgt(file_get_contents("worker.main.php")); $worker = escapeltgt(file_get_contents("worker.worker.js")); $ajax = escapeltgt(file_get_contents("worker.ajax.php")); $h->title = "Workers"; $h->banner = "<h1>Worker Demo</h1>"; $h->extra =<<<EOF <script src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.js"></script> <script src="https://bartonphillips.net/js/syntaxhighlighter.js"></script> <link rel='stylesheet' href="https://bartonphillips.net/css/theme.css"> <script> jQuery(document).ready(function($) { var w1 = new Worker("worker.worker.js"); w1.addEventListener("message", function(evt) { var string = String.fromCharCode.apply(null, evt.data) //var string = new TextDecoder("utf-8").decode(evt.data); console.log("Main string: ", string); $("pre").html(string); }); // now transfer array buffer const send = function(txt) { // use a map to create ascii to int. bufView = Uint8Array.from(txt, x => x.charCodeAt()); console.log("Main bufView: ", bufView); w1.postMessage(bufView, [bufView.buffer]); } $("#click").click(function() { var sql = $("input").val(); send(sql); return false; }); $("#clear").click(function() { $("pre").html(""); return false; }); $("#showfiles").click(function() { $("#files").show(); $(this).hide(); return false; }); }); </script> EOF; $h->css =<<<EOF <style> input { width: 100%; font-size: 1rem; } button { cursor: pointer; font-size: 1rem; } #files { display: none; } </style> EOF; // Use my framework to get the $top of the page which includes the <head> section // the <body> tag and my banner which is in <header>. list($top, $footer) = $S->getPageTopBottom($h); // Render the page echo <<<EOF $top <p>There is one table <b>test</b> that you can query. That table has the following fields:</p> <ul> <li><i>id</i> which is an auto incrementing value.</li> <li><i>name</i> which is an ascii field.</li> <li><i>lasttime</i> which is an automatic time stamp.</li> </ul> <p>You can do things like: <i>select * from test</i>, or <i>insert into test (name) value ('something')</i> or <i>delete from test where id=10</i></p> <p>You can add only 20 records and then the earliest records are deleted.</p> <form> Enter a SQL statement: <input type="text" autofocus ><br> <button id="click">Click Me</button> <button id="clear">Clear</button> </form> <pre> </pre> <hr> <button id="showfiles">View the file <b>worker.main.php</b>,<b>worker.worker.js</b> and <b>worker.ajax.php</b></button> <div id="files"> <p>worker.main.php</p> <pre class='brush: php'> $main </pre> <p>worker.worker.js</p> <pre class='brush: js'> $worker </pre> <p>worker.ajax.php</p> <pre class='brush: php'> $ajax </pre> </div> $footer EOF;
worker.worker.js
// worker.worker.js This is javascript. // This is the worker side of worker.main.php and it calls // worker.ajax.php for the info from tables. // See worker.ajax.php for description of the 'test' table in database // 'test'. // Add an event listener for 'message'. The data is in evt.data and we // make it into a string and then pass the string to sendText() addEventListener("message", function(evt) { var string = new TextDecoder("utf-8").decode(evt.data); console.log("Worker string: ", string); sendText(string); }); // SendText() does the usual XMLHttpRequest() stuff to post to // worker.ajax.php. function sendText(txt) { var xhr = new XMLHttpRequest(); xhr.open('POST', 'worker.ajax.php', true); xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded'); // Send the text to the worker.ajax.php xhr.send("sql="+txt); // Get the information from our xhr.send(). xhr.onload = function(e) { if(this.status == 200) { // We can get two non json items back ERROR or DONE if(this.responseText.match(/ERROR|DONE/)) { var str = this.responseText; // Make a bufView using Uint8Array.from(). // This takes the string value and make a uint array of the // ascii code values. It uses the new => operator to indicate a // function(x) { return x.charCodeAt() }. This is a MAP that // converts each value from the string into an code. bufView = Uint8Array.from(str, x => x.charCodeAt()); console.log("Error Worker bufView: ", bufView); // Post the Transfer buffer postMessage(bufView, [bufView.buffer]); return; } // If it isn't the two possible ascii text values then this is a // JSON packet so decode it. console.log("Worker response", this.responseText); var newtxt = JSON.parse(this.responseText, true); // Take the items out of newtxt which is an array. var rows = ''; for(item of newtxt) { // Now the stuff in the array is an object so get the key and // value and put them into the rows variable. for([key, value] of Object.entries(item)) { rows += key + ": " + value + "\n"; } rows += "\n"; } // Now we do the same thing we did above to make the Transfer // buffer bufView = Uint8Array.from(rows, x => x.charCodeAt()); console.log("Worker bufView: ", bufView); postMessage(bufView, [bufView.buffer]); } }; };
worker.ajax.php
<?php // This is part of the worker group. // This is the AJAX server that is called from worker.worker.js // The trio is worker.main.php, worker.worker.js and worker.ajax.php /* CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(254) DEFAULT NULL, `lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; This table is in database "test" on bartonlp.com. The database allows only 'select, update, insert and delete' and the code below maintains a max of 20 entries. */ $_site = require_once(getenv("SITELOADNAME")); ErrorClass::setDevelopment(true); $S = new Database($_site); // allow both POST and GET // If $_REQUEST['sql'] is '' it will look like zero // so we will fail and fall through to GO AWAY. // So check to see if $_REQUEST['sql'] is set which // it will be and then get $sql once we know that // the request has sql set (even if it is ''). if(isset($_REQUEST['sql'])) { $sql = $_REQUEST['sql']; if(!$sql) { echo "ERROR: No sql statment<br>"; exit(); } // We could be passed something is will not work try { $S->query($sql); if(preg_match("/insert/i", $sql)) { // We want to restrict the size of this table so check the TABLE_ROWS $S->query("select TABLE_ROWS from information_schema.TABLES where TABLE_NAME='test'"); list($cnt) = $S->fetchrow('num'); $nn = $cnt - 20; // This is the number to delete //error_log("worker.ajax.php, cnt: $cnt nn: $nn"); if($cnt > 20) { $n = $S->query("delete from test order by id asc limit $nn"); // leave most resent 20 echo "DONE $n<br>"; exit(); } } if(preg_match("/update|insert|delete/", $sql)) { echo "DONE<br>"; exit(); } $rows = array(); while($row = $S->fetchrow('assoc')) { $rows[] = $row; } if(!count($rows)) { echo "ERROR: NO DATA<br>"; exit(); } //error_log("worker.ajax.php, rows:" . print_r($rows, true)); echo json_encode($rows); exit(); } catch(Exception $e) { echo "ERROR: " . $e->getMessage() . "<br>"; exit(); } } echo "ERROR: GO AWAY<br>";