A rainy saturday spent with SQLite

August 25, 2018
By: TheCra1gster

I’d been meaning to get my mits on SQLite for some time and finally found a good project to do so.

Installing SQLite

I’m on ubuntu so the following got things sorted for me:

 sudo apt-get install sqlite3 libsqlite3-dev

1	sudo apt-get install sqlite3 libsqlite3-dev

I then noticed I was getting the following

 PHP Fatal error:  Class 'SQLite3' not found in /var/www/html/wordpress/testdb.php on line 2

1	PHP Fatal error: Class 'SQLite3' not found in /var/www/html/wordpress/testdb.php on line 2

I needed to install the following as per this post and restart my web server

sudo apt-get install php5-sqlite

1	sudo apt-get install php5-sqlite

Check SQLite is installed

php -i|grep sqlite
/etc/php5/cli/conf.d/20-pdo_sqlite.ini,
/etc/php5/cli/conf.d/20-sqlite3.ini,
PDO drivers => mysql, sqlite
pdo_sqlite
sqlite3
sqlite3.extension_dir => no value => no value

php -i|grep sqlite

/etc/php5/cli/conf.d/20-pdo_sqlite.ini,

/etc/php5/cli/conf.d/20-sqlite3.ini,

PDO drivers => mysql, sqlite

pdo_sqlite

sqlite3

sqlite3.extension_dir => no value => no value

Finally, some working php code

While I won’t share my production code with you, here’s what I can share

References: https://www.tutorialspoint.com/sqlite/sqlite_php.htm https://www.digitalocean.com/community/tutorials/how-and-when-to-use-sqlite As per the tutorial above, I created the WINES table and populated it:

CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20)); 
INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");
INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");
INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");

CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20));

INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");

INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");

INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");

The following code reads my SQLite db ‘test.db’ and outputs the rows from the WINES table to an HTML table.

<?php
   class MyDB extends SQLite3{
      function __construct() {
         $this->open('test.db');
      }
   }
   
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo '<span  class="sdata" style="font-family:Verdana;font-size:12px;font-weight:400;">Opened database successfully</span><br>';
   }

   $sql =<<<EOF
      SELECT * from WINES;
EOF;

   $query = $db->query($sql);
        
// output to table
// css
$css=<<<EOF
 <!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head> 
<style>
table.redTable {
  border: 2px solid #A40808;
  background-color: #EEE7DB;
  width: 100%;
  text-align: center;
  border-collapse: collapse;
}
table.redTable td, table.redTable th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
table.redTable tbody td {
  font-size: 13px;
}
table.redTable tr:nth-child(even) {
  background: #F5C8BF;
}
table.redTable thead {
  background: #A40808;
}
table.redTable thead th {
  font-size: 19px;
  font-weight: bold;
  color: #FFFFFF;
  text-align: center;
  border-left: 2px solid #A40808;
}
table.redTable thead th:first-child {
  border-left: none;
}

table.redTable tfoot {
  font-size: 13px;
  font-weight: bold;
  color: #FFFFFF;
  background: #A40808;
}
table.redTable tfoot td {
  font-size: 13px;
}
table.redTable tfoot .links {
  text-align: right;
}
table.redTable tfoot .links a{
  display: inline-block;
  background: #FFFFFF;
  color: #A40808;
  padding: 2px 8px;
  border-radius: 5px;
}
</style>
EOF;

echo $css;

$firstRow = true;
echo '<div class="table-responsive"><table id="wines_tbl" class="redtable">';
while ($row = $query->fetchArray(SQLITE3_ASSOC)) {
    if ($firstRow) {
        echo '<thead><tr>';
        foreach ($row as $key => $value) {
            echo '<th>'.$key.'</th>';
        }
        echo '</tr></thead>';
        echo '<tbody>';
        $firstRow = false;
    }

    echo '<tr>';
    foreach ($row as $value) {
        echo '<td>'.$value.'</td>';
    }
    echo '</tr>';
}
echo '</tbody>';
echo '</table></div>';

   echo '<span  class="sdata" style="font-family:Verdana;font-size:12px;font-weight:400;">Operation completed successfully</span><br>';
   $db->close();
   
$js=<<<EOF
<script type="text/javascript">
   // jQuery - load jsontable
	  jQuery.getScript("/wordpress/js/jquery.jsontable.js", function() {
	    // script is now loaded and executed ; put your dependent JS here.
	    var jsonData = jQuery("#wines_tbl").tableToJSON({
	    ignoreColumns: [0]
	    }); 
	    
	     console.log(jsonData);
	  });
</script>
EOF;
echo $js;
   
$html.='<hr style="width:100%;">';
$date = time();
$html.='<span  class="sdata" style="font-family:Verdana;font-size:12px;font-weight:600;">Query execution time:</span><br>';
$html.='<span  class="sdata" style="font-family:Verdana;font-size:12px;">'.date("Y-m-d",$date).' '.date( "H:i:s e",$date).'</span><br>';
$date = new DateTime('now');
$tz = new DateTimeZone('Asia/Bangkok');
$date->setTimezone($tz);
$html.= '<span  class="sdata" style="font-family:Verdana;font-size:12px;">Bangkok: '.$date->format('l F j Y g:i:s A')."</span><br>";
$tz = new DateTimeZone("America/Vancouver"); 
$date->setTimezone($tz);
$html.= '<span  class="sdata" style="font-family:Verdana;font-size:12px;">Vancouver: '.$date->format('l F j Y g:i:s A')."</span><br>";
$html .='</body></html>';

echo $html;
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

<?php

class MyDB extends SQLite3{

function __construct() {

$this->open('test.db');

}

$db = new MyDB();

if(!$db) {

echo $db->lastErrorMsg();

} else {

echo '<span class="sdata" style="font-family:Verdana;font-size:12px;font-weight:400;">Opened database successfully</span><br>';

}

$sql =<<<EOF

SELECT * from WINES;

EOF;

$query = $db->query($sql);

// output to table

// css

$css=<<<EOF

<!DOCTYPE html>

<head>

</head>

<style>

table.redTable {

border: 2px solid #A40808;

background-color: #EEE7DB;

width: 100%;

text-align: center;

border-collapse: collapse;

}

table.redTable td, table.redTable th {

border: 1px solid #AAAAAA;

padding: 3px 2px;

}

table.redTable tbody td {

font-size: 13px;

}

table.redTable tr:nth-child(even) {

background: #F5C8BF;

}

table.redTable thead {

background: #A40808;

}

table.redTable thead th {

font-size: 19px;

font-weight: bold;

color: #FFFFFF;

text-align: center;

border-left: 2px solid #A40808;

}

table.redTable thead th:first-child {

border-left: none;

}

table.redTable tfoot {

font-size: 13px;

font-weight: bold;

color: #FFFFFF;

background: #A40808;

}

table.redTable tfoot td {

font-size: 13px;

}

table.redTable tfoot .links {

text-align: right;

}

table.redTable tfoot .links a{

display: inline-block;

background: #FFFFFF;

color: #A40808;

padding: 2px 8px;

border-radius: 5px;

}

</style>

EOF;

echo $css;

$firstRow = true;

echo '<div class="table-responsive"><table id="wines_tbl" class="redtable">';

while ($row = $query->fetchArray(SQLITE3_ASSOC)) {

if ($firstRow) {

echo '<thead><tr>';

foreach ($row as $key => $value) {

echo '<th>'.$key.'</th>';

}

echo '</tr></thead>';

echo '<tbody>';

$firstRow = false;

}

echo '<tr>';

foreach ($row as $value) {

echo '<td>'.$value.'</td>';

}

echo '</tr>';

}

echo '</tbody>';

echo '</table></div>';

echo '<span class="sdata" style="font-family:Verdana;font-size:12px;font-weight:400;">Operation completed successfully</span><br>';

$db->close();

$js=<<<EOF

// jQuery - load jsontable

jQuery.getScript("/wordpress/js/jquery.jsontable.js", function() {

// script is now loaded and executed ; put your dependent JS here.

var jsonData = jQuery("#wines_tbl").tableToJSON({

ignoreColumns: [0]

});

console.log(jsonData);

});

</script>

EOF;

echo $js;

$html.='<hr style="width:100%;">';

$date = time();

$html.='<span class="sdata" style="font-family:Verdana;font-size:12px;font-weight:600;">Query execution time:</span><br>';

$html.='<span class="sdata" style="font-family:Verdana;font-size:12px;">'.date("Y-m-d",$date).' '.date( "H:i:s e",$date).'</span><br>';

$date = new DateTime('now');

$tz = new DateTimeZone('Asia/Bangkok');

$date->setTimezone($tz);

$html.= '<span class="sdata" style="font-family:Verdana;font-size:12px;">Bangkok: '.$date->format('l F j Y g:i:s A')."</span><br>";

$tz = new DateTimeZone("America/Vancouver");

$date->setTimezone($tz);

$html.= '<span class="sdata" style="font-family:Verdana;font-size:12px;">Vancouver: '.$date->format('l F j Y g:i:s A')."</span><br>";

$html .='</body></html>';

echo $html;

Here;s what my output looks like:

I grabbed the CSS for the table styling from here.

Backing up your SQLite database

sqlite3 test.db ".backup mysq3db.bak"

1	sqlite3 test.db ".backup mysq3db.bak"

JSON REST API Issues with WordPress Gutenberg

While writing up this post I ran into JSON REST API issues. This post from StackOverflow eventually saved me 🙂

here’s my .htaccess file:

# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
SetEnvIf Authorization "(.*)" HTTP_AUTHORIZATION=$1
</IfModule>
# END WordPress

<Directory "/home/*/Sites">
    AllowOverride All FileInfo AuthConfig Limit Indexes
    Options MultiViews Indexes SymLinksIfOwnerMatch IncludesNoExec
    Require method GET POST PUT DELETE OPTIONS
</Directory>