Tag Archives: SQL LIte Select

SQLite on the Pi

This weekend gone I was having a play with dSQLite for my Raspberry Pi2 and thanks to some helpful guys here in the comments section and at Node-Red Google forums – and a little inspiration of my own -  I manage to get everything up and running – so I’ve changed what was originally a question into short piece on using SQLLite on the Pi.

Why would you want to use SQLite?  Well, my Pi is running Node-Red to control my own little ESP8266 gadgets as some of you know (software for these under the Home Control 2015 page just took a turn for the better) – and I store info in MYSQL.  I’m aware from the various forums that MySQL can be quite heavy on writing and of course that’s not really good for solid state microSD memory  (HOW bad is debatable but we can all agree that microSD does not have unlimited WRITES). And so it was that one of the guys here suggested I look at SQLite.

Now, I don’t know if I was getting confused with a similarly named Microsoft product but I really didn’t know much about this until this weekend. Essentially this is a somewhat lightweight SQL – simple to install, simple to use, works with PHP etc… and works with Node-Red.

On my Raspberry Pi I installed SQLite (as root)

I created a database and put something in there – I tested it on the command line and in node-red – no problem.

I tried a simple PHP page to view the data – nothing. So, I enabled error messages in the page – and it said it did not know anything about the SQLite class.

So I installed that and then I got permissions errors – turns out that putting the database file in the /root directory isn’t too good an idea so I simply moved it to /var/www and Bob’s your uncle!

Here’s the sequence..

Install SQLite

apt-get install sqlite3

Create a database

sqlite3 test.db

Create a table

BEGIN;
CREATE TABLE temp1 (name TEXT, temperature NUMERIC);
COMMIT;

insert into temp1 values('first entry',45);
insert into temp1 values('second entry',56);

.exit

Install Node-Red Node

npm install node-red-node-sqlite

Add SQLite to PHP

apt-get install php5-sqlite

Write a page in PHP

<html>
<head></head>
<body>
<?php
error_reporting(E_ALL);
ini_set('display_errors', true);
$db = new SQLite3('/root/test.db');
var_dump($db->querySingle('SELECT name FROM temp1' ));
print_r($db->querySingle('SELECT name FROM temp1', true));
?>
</body>
</html>

I’ve no idea yet how that PHP is actually working – but that will come in time -  I’m thinking about all the MESS involved in installing MYSQL and the passwords etc, just to store a few vars away to draw some graphs – this might be a better solution… and there is an option to make RAM-based databases as well. Overall, worth a look I’d say.

BUT that still left me with a problem….

I have a logging table for my ESP8266 boards..

CREATE TABLE IF NOT EXISTS `device_list` (
`device_name` varchar(80) NOT NULL DEFAULT '',
`device_description` varchar(80) DEFAULT NULL,
`device_attribute` varchar(80) DEFAULT NULL,
`logins` int(11) DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
PRIMARY KEY (`device_name`)
);

Simple enough – and when a unit logs in, I either store the record OR update the counter (logins) if the record already exists.  In MYSQL this is easy and it is a fairly common requirement.

insert into device_list(device_name, device_description, device_attribute,logins,last_update,creation_date) values('998','empty','',1,NOW(),NOW()) on duplicate key update logins = logins+1, device_description='empty', device_attribute='', last_update=NOW()

In SQLite – ALMOST as easy… two statements…

insert or ignore into device_list(device_name, device_description, device_attribute,logins,last_update,creation_date) values('998','empty','',0,(datetime('now','localtime')),(datetime('now','localtime')));

update device_list set logins = logins+1, last_update=(datetime('now','localtime')) where device_name='998';

So you start with a count of zero IF a new record is created – and the second statement, which will always operate, updated the latest date and increments the counter (in the case of a new record to 1).

But therein lies the rub… the Node-Red node for SQLite3 will not let you enter more than one statement! I found a way to put this into a single statement but it wasn’t flexible, was VERY hieroglyphic and I figured 6 months later it would mean nothing to me… So the next best thing – a node-red function to split any multiple statements up and fire them out one at a time – easy.

splitter

and the code inside that function…. this will allow as many statements as needed… actually that really would make a good addition to the SQLite3 node…

var arr=msg.topic.split(';');
for(var i=0;i<arr.length;i++) {
msg.topic=arr[i];
node.send(msg);

}

Works a treat. NOW- that’s all very nice I hear you say but what about fancy graphical tools!!  MyPHPAdmin and others come to mind. So off I went looking – I tried 2 or 3, one of which used up loads of space on my PI but I never could find out where it was to complete the install.

phpLiteAdmin is the kind of program I like – one PHP page, one config page and Bob’s your uncle. Optionally you can have a themes directory full of theme .css files – but basically it’s a 2 file program – and yet…

phpliteadmin

As you can see, not at ALL shabby. Here’s a link – this is where I grabbed the THEMES directory from.. https://github.com/phpLiteAdmin – that is 1.97 but it says “dev” so I didn’t actually use that code, just the themes – my 1.9.6 came from here. http://www.phpliteadmin.org/download/

permissionsI put it into /var/www/phpliteadmin and renamed the main phpliteadmin.php to index.php because I’m lazy.

Oh and the database directory and file must have 0755 permissions… well, tha’ts the minimum I could get away with on experimenting.

Have fun – and if you know BETTER than the above – a better yet simple admin, something I’ve done wrong – DO write in here – improvement is the goal!

Facebooktwittergoogle_pluspinterestlinkedin