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>";