Databases stored on a read-only file system have very limited (short-term) use. Using the SD Card to store such databases would be very advantageous. Lets move the database location from from
/srv/www/cgi-bin/db to
/media/sd-mmcblk0p1/database.
steve@Desktop:~/projects/zedboard_linux$ sshpass -p root ssh -t root@192.168.2.87 'mkdir /media/sd-mmcblk0p1/database'
Update the SQLite test PHP to use the new location.
steve@Desktop:~/projects/zedboard_linux$ subl os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php
- <?php
- /*
- * File .......... sqlite_test.php
- * Author ........ Steve Haywood
- * Website ....... http://www.spacewire.co.uk
- * Project ....... SpaceWire UK Tutorial
- * Version ....... 1.1
- * Conception .... 8 August 2023
- * Standard ...... PHP 7
- * Description ...
- * Simple HTML, PHP & SQLite example code that checks the basic operation of
- * SQLite. SQL queries used are :-
- *
- * 1. Create/open database
- * 1. Create table
- * 3. Insert row
- * 4. Close database
- */
- ?>
-
- <!DOCTYPE html>
- <html lang="en">
- <head>
- <meta http-equiv="content-type" content="text/html; charset=UTF-8">
- <title>SQLite Test</title>
- </head>
-
- <?php
-
- class MyDB extends SQLite3 {
- function __construct() {
- $this->open('/media/sd-mmcblk0p1/database/test.db');
- }
- }
-
- echo 'Creating/opening database<br>';
- $db = new MyDB();
- if ($db) {
- echo 'Success';
- } else {
- echo 'Failure : ' . $db->lastErrorMsg();
- }
- echo '<br><br>';
-
- echo 'Creating table<br>';
- $return = $db->exec("CREATE TABLE fruit (item VARCHAR(30) NOT NULL PRIMARY KEY UNIQUE, quantity int unsigned NOT NULL)");
- if ($return) {
- echo 'Success';
- } else {
- echo 'Failure : ' . $db->lastErrorMsg();
- }
- echo '<br><br>';
-
- echo 'Inserting 1st row in table<br>';
- $result = $db->exec("INSERT INTO fruit (item, quantity) VALUES ('Apple', '5')");
- if ($result) {
- echo 'Success';
- } else {
- echo 'Failure : ' . $db->lastErrorMsg();
- }
- echo '<br><br>';
-
- echo 'Inserting 2nd row in table<br>';
- $result = $db->exec("INSERT INTO fruit (item, quantity) VALUES ('Orange', '12')");
- if ($result) {
- echo 'Success';
- } else {
- echo 'Failure : ' . $db->lastErrorMsg();
- }
- echo '<br><br>';
-
- echo 'Closing database<br>';
- $result = $db->close();
- if ($result) {
- echo 'Success';
- } else {
- echo 'Failure : ' . $db->lastErrorMsg();
- }
-
- ?>
-
- </body>
- </html>
Life too short? Grab the file and check out the changes :-
steve@Desktop:~/projects/zedboard_linux$ wget https://spacewire.co.uk/tutorial/petalinux_webserver_spruce/os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test_v1.1.php.txt -O os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php
steve@Desktop:~/projects/zedboard_linux$ git difftool v13.0:os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php
Recursively copy the updated and newly created files over to PetaLinux.
steve@Desktop:~/projects/zedboard_linux$ sshpass -p root scp -r os/petalinux/project-spec/meta-user/recipes-apps/website/files/* root@192.168.2.87:/srv/www
Access the webserver running on the Zedboard using a browser pointing at the Zedboard's IP address (
192.168.2.87). Select
Misc »
SQLite Basic Test from the menu bar. All being well the following page should be displayed in a new tab.
Examine the
database directory to check that everything is as expected.
root@petalinux:~# ls -la /media/sd-mmcblk0p1/database
total 20
drwxr-xr-x 2 root root 4096 Mar 2 08:24 .
drwxr-xr-x 5 root root 4096 Jan 1 1970 ..
-rwxr-xr-x 1 root root 12288 Mar 2 08:24 test.db
Since there is now a persistent database that will still exist after a reboot, an enhanced SQLite Test webpage is worth the investment. Let's enhance what we already have, something akin to the following should do the trick.
steve@Desktop:~/projects/zedboard_linux$ subl os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php
- <?php
- /*
- * File .......... sqlite_test.php
- * Author ........ Steve Haywood
- * Website ....... http://www.spacewire.co.uk
- * Project ....... SpaceWire UK Tutorial
- * Version ....... 1.2
- * Conception .... 8 August 2023
- * Standard ...... PHP 7
- * Description ...
- * Simple HTML, PHP & SQLite example code that offers some degree of error
- * checking on the form inputs. Reports SQL operations and success/failure
- * status. Demonstrates some of the common SQL queries :-
- *
- * 1. Create/open database
- * 2. Create table
- * 3. Drop table
- * 4. Insert row
- * 5. Delete row
- * 6. Update row
- * 7. Close database
- */
- ?>
-
- <!DOCTYPE html>
- <html lang="en">
- <head>
- <meta http-equiv="content-type" content="text/html; charset=UTF-8">
- <title>Simple PHP & SQLite Demonstration</title>
- </head>
-
- <style>
- table, th, td {
- border: 1px solid;
- }
-
- .success {
- color: green;
- }
-
- .fail {
- color: red;
- }
- </style>
-
- <body>
-
- <script>
- function row_delete(obj_submit) {
- if (obj_submit) {
- const row = obj_submit.id.substr(7);
- const obj_item = document.getElementById("item_" + row);
- if (obj_item) {
- location.replace("<?php echo $whoami; ?>?action=delete&item=" + obj_item.value);
- }
- }
- }
-
- function row_insert(obj_submit) {
- if (obj_submit) {
- const obj_item = document.getElementById("insert_item");
- if (obj_item) {
- const obj_quantity = document.getElementById("insert_quantity");
- if (obj_quantity) {
- location.replace("<?php echo $whoami; ?>?action=insert&item=" + obj_item.value + "&quantity=" + obj_quantity.value);
- }
- }
- }
- }
-
- function row_update(obj_submit) {
- if (obj_submit) {
- const row = obj_submit.id.substr(7);
- const obj_item = document.getElementById("item_" + row);
- if (obj_item) {
- const obj_quantity = document.getElementById("quantity_" + row);
- if (obj_quantity) {
- location.replace("<?php echo $whoami; ?>?action=update&item=" + obj_item.value + "&quantity=" + obj_quantity.value);
- }
- }
- }
- }
- </script>
-
- <?php
- $whoami = basename($_SERVER['PHP_SELF']);
- $db_name = 'test.db';
- $table = "fruit";
- $indent = " ... ";
-
- echo '<h3>Simple PHP & SQLite Demonstration</h3>';
-
- $db_exists = file_exists('/media/sd-mmcblk0p1/database/test.db');
-
- if ($db_exists) {
- echo "Attempting to open existing database<br>";
- } else {
- echo "Attempting to create new database<br>";
- }
-
- class MyDB extends SQLite3 {
- function __construct() {
- $this->open('/media/sd-mmcblk0p1/database/test.db');
- }
- }
-
- if ($db_exists) {
- echo $indent . 'Opening of existing';
- } else {
- echo $indent . 'Creation of new';
- }
-
- $db = new MyDB();
- if ($db) {
- echo ' database <span class="success">successful</span>';
- } else {
- echo ' database <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
-
- echo '<br><br>';
-
- echo 'Validating form submission (if any)<br>';
-
- $form_action_key = "action";
- $form_action_values = array("insert", "delete", "create", "drop", "update");
- if (isset($_GET[$form_action_key])) {
- $form_action_value = $_GET[$form_action_key];
- echo $indent . 'Form key (' . $form_action_key . ') <span class="success">detected</span> with value (' . $form_action_value . ') which ';
- if (in_array($form_action_value, $form_action_values)) {
- echo '<span class="success">exists</span>';
- $process_form = 1; // Good form submission
- } else {
- echo '<span class="fail">does not exist</span>';
- $process_form = 0; // Bad form submission
- }
- echo ' in expected set (';
- $last_value = end(array_values($form_action_values));
- foreach ($form_action_values as $value) {
- echo $value;
- if ($value != $last_value) {
- echo (', ');
- }
- }
- echo ')';
- } else {
- echo $indent . 'Form key (' . $form_action_key . ') <span class="success">not detected</span>';
- $process_form = 0; // No form submission
- }
-
- if (!$process_form) {
- echo ', treating page as a non-form/normal page';
- }
-
- if ($process_form) {
-
- echo '<br><br>';
-
- switch($form_action_value) {
- case "create" :
-
- echo 'Attempting to create table<br>';
- $query = "CREATE TABLE $table (
- item VARCHAR(30) NOT NULL PRIMARY KEY UNIQUE,
- quantity int unsigned NOT NULL
- )";
- $return = $db->exec($query);
- if ($return) {
- echo $indent . 'Table created <span class="success">successfully</span>';
- } else {
- echo $indent . 'Table creation <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
- break;
-
- case "drop" :
-
- echo 'Attempting to drop table<br>';
- $query = "DROP TABLE $table";
- $result = $db->exec($query);
- if ($result) {
- echo $indent . 'Table dropped <span class="success">successfully</span>';
- } else {
- echo $indent . 'Table drop <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
- break;
-
- case 'insert' :
- echo 'Attempting to insert into table<br>';
- $proceed = 1;
- $item = $_GET['item'];
- if ((isset($item) == 0) || ($item == "")) {
- echo $indent . 'Row insertion <span class="fail">failed</span> due to missing/empty item field<br>';
- $proceed = 0;
- }
- $quantity = $_GET['quantity'];
- if ((isset($quantity) == 0) || ($quantity == "")) {
- echo $indent . 'Row insertion <span class="fail">failed</span> due to missing/empty quantity field<br>';
- $proceed = 0;
- }
- if ($proceed) {
- $query = "INSERT INTO $table (item, quantity) VALUES ('$item', '$quantity')";
- $result = $db->exec($query);
- if ($result) {
- echo $indent . 'Table insertion <span class="success">successful</span>';
- } else {
- echo $indent . 'Table insertion <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
- }
- break;
-
- case 'delete' :
- $item = $_GET['item'];
- echo 'Attempting to delete row with Unique ID ' . $item . ' from table<br>';
- if ($item) {
- $query = "DELETE FROM $table WHERE item='$item'";
- $result = $db->exec($query);
- if ($result) {
- echo $indent . 'Row deletion <span class="success">successful</span>';
- } else {
- echo $indent . 'Row deletion <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
- } else {
- echo $indent . 'Row deletion <span class="fail">failed</span> due to missing/empty item field<br>';
- }
- break;
-
- case 'update' :
- echo 'Attempting to update row in table<br>';
- $proceed = 1;
- $item = $_GET['item'];
- if ((isset($item) == 0) || ($item == "")) {
- echo $indent . 'Row insertion <span class="fail">failed</span> due to missing/empty item field<br>';
- $proceed = 0;
- }
- $quantity = $_GET['quantity'];
- if ((isset($quantity) == 0) || ($quantity == "")) {
- echo $indent . 'Row insertion <span class="fail">failed</span> due to missing/empty quantity field<br>';
- $proceed = 0;
- }
- if ($proceed) {
- $query = "UPDATE $table SET quantity = $quantity WHERE item = '$item'";
- $result = $db->exec($query);
- if ($result) {
- echo $indent . 'Table row update <span class="success">successful</span>';
- } else {
- echo $indent . 'Table row update <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
- }
- break;
-
- }
-
- }
- ?>
-
- <br><br>
-
- <?php
- echo 'Attempting to select all data from table (if any)<br>';
- $query = "SELECT * FROM $table";
- $result = $db->query($query);
- if ($result) {
- echo $indent . 'Table data selection <span class="success">successful</span>';
- } else {
- echo $indent . 'Table data selection <span class="fail">failed</span> : ' . $db->lastErrorMsg();
- }
-
- if ($result) {
-
- echo '<br><br>';
- echo 'Attempting to fetch data from table (if any)';
- echo '<br><br>';
-
- echo '<table>';
- echo '<tr>';
- echo '<th>Item</th>';
- echo '<th>Quantity</th>';
- echo '<th>Action</th>';
- echo '</tr>';
-
- $pos = 0;
- while($row = $result->fetchArray(SQLITE3_ASSOC))
- {
- echo '<tr>';
-
- echo '<td>';
- echo '<input id="item_' . $pos . '" type="text" value="'.$row['item'].'" readonly>';
- echo '</td>';
-
- echo '<td>';
- echo '<input id="quantity_' . $pos . '" type="text" value="'.$row['quantity'].'">';
- echo '</td>';
-
- echo '<td>';
- echo '<input id="delete_' . $pos . '" type="submit" value="Delete" onclick="row_delete(this)">';
- echo '<input id="update_' . $pos . '" type="submit" value="Update" onclick="row_update(this)">';
- echo '</td>';
-
- echo '</tr>';
- $pos++;
- }
-
- echo '<tr>';
- echo ' <td>';
- echo ' <input id="insert_item" type="text">';
- echo ' </td>';
- echo ' <td>';
- echo ' <input id="insert_quantity" type="text">';
- echo ' </td>';
- echo ' <td>';
- echo ' <input id="insert" type="submit" value="Insert" onclick="row_insert(this)">';
- echo ' </td>';
- echo '</tr>';
- echo '</table>';
- }
-
- echo '<h3>Select option below :-</h3>';
-
- echo '<ul>';
- echo '<li><a href="'.$whoami.'?action=create">Create table</a></li>';
- echo '<li><a href="'.$whoami.'?action=drop">Drop table</a></li>';
- echo '</ul>';
-
- ?>
-
- <?php
- $db->close();
- ?>
-
- </body>
- </html>
Life too short? Grab the file and check out the changes :-
steve@Desktop:~/projects/zedboard_linux$ wget https://spacewire.co.uk/tutorial/petalinux_webserver_spruce/os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php.txt -O os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php
steve@Desktop:~/projects/zedboard_linux$ git difftool v13.0:os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/sqlite_test.php
Recursively copy the updated and newly created files over to PetaLinux.
steve@Desktop:~/projects/zedboard_linux$ sshpass -p root scp -r os/petalinux/project-spec/meta-user/recipes-apps/website/files/* root@192.168.2.87:/srv/www
Access the webserver running on the Zedboard using a browser pointing at the Zedboard's IP address (
192.168.2.87). Select
Misc »
SQLite Basic Test from the menu bar. All being well the following page should be displayed in a new tab.
Upon the reboot of PetaLinux all the fruity information will still be available in the database.
Change the PHP Lite Admin configuration to reflect the db location change.
steve@Desktop:~/projects/zedboard_linux$ subl os/petalinux/project-spec/meta-user/recipes-apps/website/files/cgi-bin/phpliteadmin.config.php
cgi-bin/phpliteadmin.config.php (partial)
- $directory = '/media/sd-mmcblk0p1/database';
Recursively copy the updated and newly created files over to PetaLinux.
steve@Desktop:~/projects/zedboard_linux$ sshpass -p root scp -r os/petalinux/project-spec/meta-user/recipes-apps/website/files/* root@192.168.2.87:/srv/www
Access the webserver running on the Zedboard using a browser pointing at the Zedboard's IP address (
192.168.2.87). Select
Misc »
PHP Lite Admin from the menu bar. All being well the following page should be displayed in a new tab.
Set the boot mode jumpers on the Zedboard for SD Card.
Power on the Zedboard.
Note the failures on Timestamp & Hash, this is because the recursive copy replaced the build generated project.txt with the default one that does not contain these two fields.
] & Off [
]
], Off [
] & None [
]
], Off [
] & None [
]
