Workers

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

worker.main.php

<?php
// BLP 2023-02-26 - use new approach
// Demonstrates the use of a worker using AJAX calls (worker.ajax.php).
// This is the main program 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 my framework SiteClass into $_site.
// Check SiteClass out at https://github.com/bartonlp/site-class.
// It has some documentation at that site.
// The worker.ajax.php uses the 'test' user and database while this program uses what is in
// mysitemap.json (which is usualy user=barton, database=barton).

$_site = require_once(getenv("SITELOADNAME"));
$S = new SiteClass($_site); // $S gives access to my framework.

// escapeltgt() is a little utility that change < and > to < > from helper-functions.php.
// These three, $main, $worker, $ajax are displayed when id 'showfiles' is clicked.

$main = escapeltgt(file_get_contents("worker.main.php"));
$worker = escapeltgt(file_get_contents("worker.worker.js"));
$ajax = escapeltgt(file_get_contents("worker.ajax.php"));

$S->title = "Workers";
$S->banner = "<h1>Worker Demo</h1>";

$S->h_script =<<<EOF
<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");

  // Listen from messages frrom worker.worker.js
  
  w1.addEventListener("message", function(evt) {
    console.log("data: ", evt.data);
    if(Object.keys(evt.data)[0] == "ERROR" || Object.keys(evt.data)[0] == "DONE") {
      $("pre").html(Object.values(evt.data)[0]);
    } else {
      //let string = String.fromCharCode.apply(null, evt.data)
      let 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());
    let bufView = new TextEncoder("utf-8").encode(txt);
    console.log("Main bufView: ", bufView);
    w1.postMessage(bufView, [bufView.buffer]);
  }

  $("#click").on("click", function() {
    var sql = $("input").val();
    send(sql);
    return false;
  });
  $("#clear").on("click", function() {
    $("pre").html("");
    return false;
  });
  $("#showfiles").on("click", function() {
    $("#files").show();
    $(this).hide();
    return false;
  });
});
</script>
EOF;
$S->css =<<<EOF
input {
  width: 100%;
  font-size: 1rem;
}
button {
  cursor: pointer;
  font-size: 1rem;
}
#files {
  display: none;
}
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>.

[$top, $footer] = $S->getPageTopBottom();

// 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 the 'test' table.
// 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) {
  // Use fetch() to send and receive the data.
  
  let ret = fetch("worker.ajax.php", {
    body: "sql=" +txt, // This is just plain sql
    method: "POST",
    headers: {
      'content-type': 'application/x-www-form-urlencoded'
    }
  }).then(res => res.text());

  ret.then(newtxt => {
    newtxt = newtxt.toString();
    $reg = /(\[?{.*}\]?).*/ms;
    newtxt = newtxt.replace($reg, '$1');
    console.log("Worker response" + newtxt);

    newtxt = JSON.parse(newtxt);
    
    if(Object.keys(newtxt) == "ERROR" || Object.keys(newtxt) == "DONE") {
      postMessage(newtxt);
    } else {
      // 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 bartonphillips.com.
The database allows only 'select, update, insert and delete' and the code below maintains a max of 20 entries.
The standard mysitemap.json has my normal database stuff. We change it here to ues the 'test'
user and the 'test' database which has the 'test' table.
*/

$_site = require_once(getenv("SITELOADNAME"));

//ErrorClass::setNobacktrace(true);
//ErrorClass::setErrlast(true);

$_site->dbinfo->user = "test"; // use test user
$_site->dbinfo->database = "test"; // and test database
$_site->noTrack = true; // needed because user is test not barton.
$S = new Database($_site); // Database does not do any counting and sets noTrack true by default.

$sql = $_POST['sql'];

// We are using fetch() in worker.worker.js so we need to get the data from 'php://input'

//$sql = file_get_contents("php://input");
//error_log("worker.ajax.php, php://input=$sql");
//exit();

if(empty($sql)) {
  echo json_encode(["ERROR"=>"No sql statment"]);
  exit();
}

// We could be passed something that will not work

try {
  if(preg_match("/insert/i", $sql)) {
    // We want to restrict the size of this table so check the TABLE_ROWS

    $S->query("select count(*) from test");

    $cnt = $S->fetchrow('num')[0];
    $nn = $cnt - 19; // This is the number to delete

    if($cnt > 20) {
      $n = $S->query("delete from test order by id asc limit $nn"); // leave most resent 20
      $del = "Deleted $n items";
    }
  }

  $n = $S->query($sql);

  if(preg_match("/update|insert|delete/", $sql)) {
    echo json_encode(["DONE"=>"$del Rows Affected: $n"]);
    exit();
  }

  $rows = [];

  while($row = $S->fetchrow('assoc')) {
    $rows[] = $row;
  }

  if(!count($rows)) {
    echo json_encode(["ERROR"=>"NO DATA"]);
    exit();
  }

  echo json_encode($rows); // encode the data and send it.
  exit();
} catch(Exception $e) {
  $tmp = json_encode(["ERROR"=> $e->getMessage()]);
  echo $tmp;
  throw(new Exception($e));
}

echo "ERROR: This program should not be run directly. Run 'worker.main.php' instead.<br>";
error_log("worker.ajax.php, ERROR: This program should not be run directly. Run 'worker.main.php' instead.");