SQLite on the Pi

Peter ScargillSQL Lite23 Comments on 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!

23 thoughts on “SQLite on the Pi”

  1. Hi Peter,

    i cant get the phpliteadmin to work.
    Apache2 works, i can open the test-html-page.
    but when i try to open phpliteadmin (192.168.2.107/phpliteadmin) it says: the requested url was not found on this server.
    i changed permission to 775 to all files and the folder of phpliteadmin.
    files and folder are owned by www-data group and www-data user.
    can you help me? do you know what i am doing wrong?

    thanks
    jan

    1. Hi

      No idea. As user Pi running my script you should need to change no permissions or anything to run PHPLiteAdmin at all – it usually just works after the script is done. If you’ve messed with permissions and made any other changes it is impossible to know what to do other than start again. You could extract the bits to install phpliteadmin and watch carefully the output when you run the lines.

      1. Thanks Peter for your answer.

        I solved the problem this way:

        I had to copy the phpliteadmin folder to : /var/www/html/….
        After that I was able to open the index.php file from my browser
        Problem was the page didnt load in the correct way, I was able to see the source code of the php file.
        After installing “sudo apt-get install libapache2-mod-php5” i can finally enter phpliteadmin 🙂

  2. Peter, what about Mosquitto – it also writes to S a lot, the log file – in my case at /var/log/mosquitto/mosquitto.log, and if you have “persistence true” then it rights a file var/lib/mosquitto/mosquitto.db as well ? What do you think – should we also look at this – to save SD writes ?

    1. Could be worth looking at http://mosquitto.org/man/mosquitto-conf-5.html to see if it is worth changing the autosave interval etc.. Also could be worth looking to see if the log file can simply be turned off. I THINK Mosquitto by default simply writes the log to the console?? Logging defaults to stderr according to the file.

  3. Sadly I’m having an issue installing the node-red-node-sqlite like Scott is. I’m using the latest OS “jessie” on a Pi2 that comes with Node Red pre-installed.

    1. I will share my script as I just installed the lot on a brand new Pi3 without a hitch. Bear with me… packing for trip right now

      1. Ok I’ve documented what I did yesterday on my new Raspberry Pi 3 – should be fine on a 2. That might help anyone having issues installing Node-Red etc on a Pi2 or Pi3.

        Installing the Works – The Script

        Now, you might ask why did I use BerryBoot? You don’t have to but it’s great for doing small backups on powerup instead of backing up an entire 16GB card. That’s all.

  4. How did you get npm to install node-red-node-sqlite? It is failing on compilation for me…

    1. Just as per the script! Seems to work every time. Did you start off with EXACTLY the same base version operating system? And on Pi2?

    1. Hi John

      Thanks for writing in – I’m going to guess – and DO tell me if I am talking rubbish. It seems that because there are no PORTS to write to, any SQLite Manager has to have access to the actual file. So – a Firefox add-in on my PC would not be able to access a database on my Raspberry Pi… and that’s no good because browser use on the Pi is awful not least because of lack of speed and the awful browsers on the Pi.. Hence I tend to manage everything from my PC.. I assume therefore that any admin for SQLite needs to be on the Pi and web-based… am I right? Or completely off-base?

      1. Hi Pete, sorry about the delay.

        Yes, what you wrote is exactly right, you do need access to the file.

        If you run a samba server on your pi, maybe you could make www-data (or whichever folder) a share. Or maybe you could try http://igikorn.com/sshfs-windows-8/ to mount your Pi folder via ssh (in which case you will need a user which has write access to where your sqlite files are).

        For me, sqlite manager is more of a “post mortem” type tool. I download the sqlite database file to my windows machine and then check its content with sqlite manager.

        Cheers,
        John

        1. Do you know, for reasons that I can’t explain, using a Samba server never occurred to me – though I prefer general web access as I move around a lot. I have bases in England and Spain and I need to access all things at all time so web interfaces tend to appeal… but anyway, it’s looking like solutions are emerging and there is no doubt that SQLite is way easier to set up than MYSQL and no doubt less disk intensive though how much I’ve yet to clarify. Thoughts anyone – is it really worth moving in your opinion, from MYSQL to SQLite for relatively simple queries?

          1. Samba works really well, no worries it would’ve occurred to you eventually!

            Just a suggestion if you haven’t explored it yet, VPN to your remote location! I use an Asus router (RT-N66U) + ADSL modem (Vigor 120) + TomatoUSB Shibby.

            Unfortunately, the AC routers with integrated ADSL don’t work with TomatoUSB, so that’s the reason for using an Ethernet router + separate modem.

            Anyway, the point of this setup is to allow (among other things) yourself VPN access via OpenVPN. You can actually set a router-to-router VPN “bridge” and see the remote machines locally without faffing about. For example, if remote is 192.168.1.X and local is 192.168.2.X, with the VPN bridge set-up on the router, you can ping any 192.168.1.X remote machines locally from any machine on your 192.168.2.X network. SMB file transfer, RDP, printing, SSH, http(s) traffic… anything goes. The only open port on the firewall is for OpenVPN.

            No opinion regarding SQLite, sorry. For simple queries, it works really well. Firebird embedded might be another solution to consider.

            End of rant 🙂

            Cheers,
            John

  6. Pimatic also uses SQLite, they keep the database in RAM and now en then flush/save it (interval is configurable). Runs great on Raspberry’s and BananaPi. I’m using the latter one and have been running it for almost 4 weeks without any trouble.

    1. Hi Rene – as I’m not familiar with this – want to point me to the bit where the running in RAM is done and the flushing? I’d like to implement that on my setup…. I’m sure others would also.

      1. Ok I went and had a look. Someday that might be a good system.. looks a bit BETA right now, could not find anything about their use of SQLITE sadly…

  7. Hi Peter,

    The problem is probably in having your test.db in /root. Even though the permissions on the file itself make it readable to the www-data user, the container directory (/root) is almost certainly still out of bounds to it.

    Perhaps try moving the db into its own data directory in /var/www and see if that helps.

    1. You’re right Kevin – Thanks Jim as well…it was indeed the location… I simply moved it to /var/www and all is well – well other than having now to learn how to use it properly but at least it is pulling in the data and not throwing errors out.

      Thanks Guys.

      1. FWIW, when I’m debugging stuff like that, I try to do it logged in as the web user. I’ve been caught out like yourself before where it all *looks* like it should work, but subtle issues can work their way in, especially if a var/www folder has been copied over from another system with a different user database; Suddenly, files with permissions that worked fine on the other system are no longer accessible. When logged in as www-data, doing a recursive find or grep can flag potential trouble spots.
        One caveat with that is that the shell environment for www-data will be a little bit spartan. You may want to set up a little script that can be manually run after logging in to get back all the nice stuff for interactive sessions!

Comments are closed.