Category Archives: SQL Lite

Android Phone as Server

Why am I showing you a picture of a cracked-screen phone?

Linux on Android PhoneWell because this particular phone is a bust Oukitel K10000, the phone with the world’s longest-lasting battery and an excellent workhorse. My friend Aidan gave me this (rather than chucking it away) some time ago and it has been sitting doing nothing. All that is wrong with it is a cracked (and exceedingly dangerous on the fingers) screen. I’ll bet I’m not the only one with such a phone lying around wasting space.

Well, as of yesterday, it is a Debian server with all my usual stuff going on quietly in the background – with the screen normally off – running freezing cold and hopefully being super reliable.

This is an experiment only – beware – if your phone or tablet dies it is your problem….  oh and your Android phone/tablet needs to be ROOTED. 

Imagine turning your old, dust-covered phone into a sleek, battery backed-up server with unfeasibly long backup time, immunity to any mains spikes, a silent, fast Debian base for all the stuff in my script – which includes Node-Red, Apache/PHP, SQLITE (+ PHPLiteAdmin), MQTT, MC, Ha-Bridge and more!  If you’ve been following this blog you’ll know about the script.

So this article applies to ROOTED Android phones and we’ve only tested around Android 5.0 onwards.  In my case I rooted the phone with KingRoot (note, NOT the one in the PlayStore which is a con – avoid it - but the one at kingroot.net ) – please note that rooting phones is beyond the scope of this article and if you are not confortable with this you should not do it. There are a lot of links out there on the subject and many of them are fraudulent.

tmpCD52There is an APP in the Play Store called Linux Deploy. It is also on GitHub. Beware that this is essentially undocumented unless you are Russian – so please don’t ask how you can use your phone’s GPS or Sound from Debian – not a clue!

You should have a modern-ish WiFi enabled (or hardwired if you like) Android phone or tablet with at least 16GB of internal FLASH (which should give you 10GB working space).   If you only have 8GB that will leave you only 2GB which - really – isn’t enough.

Getting Debian 8 on the phone:  After ensuring your phone/tablet is rooted, Install the App.

Linux on Android PhoneIn the app, on the top LEFT menu – find  REPOSITORIES and select Debian 8 for ARM.

On the bottom right is a drop down – you should go in there and select INSTALLATION TYPE – directory (OR FILE with a reasonable file size limit – say 5GB – the 2GB default will NOT work). Change the user to “pi” with suitable password in the same menu. TICK for allowing initialisation – and use of SSH server. Also where you see INIT SYSTEM change that to sysv. 

Then TOP RIGHT menu  - INSTALL – that might take some time – top right again CONFIGURE – then bottom menu START – and you should have a working Linux you can get into with for example WINSCP for that “pi” user. The IP address is clearly shown in the App.

I suggest not going further until you are comfortable with the above – took me several attempts because I didn’t follow the above exactly (well, and Antonio and I were learning at the same time).

Running the script: Via WinSCP or similar, put the script into the pi directory – changing permissions as normal - run the script – and ensure PHONE is selected – it isn’t by default. Come back after lunch. The script will no doubt suggest a reboot. Instead, hit the STOP button on the bottom of the phone screen – wait for it complete, hit the START button – wait – and you should now have everything in the script running!

Now – I’m using this to run my script – but I’m thinking you COULD use it to serve files etc. – I’ve not tried this but I’m guessing it would be easy to access an SD card…. and make that a folder…. hmmm.

Anyway, it is now the day after I installed all this – the phone is sitting there “off” and unlike my FriendlyArm board with it’s whirling fan, is sitting silently and freezing cold yet ran the script much faster than any of my SBCs – around 40 minutes.

K10000 running Debian 8No guarantees as there just isn’t enough info about Linux Deploy out there (unless you find/translate some) – but it does seem to work well now that we’ve made sufficient alterations to the script to take this particular setup into account. A fun project, to be sure. Now, I know this is a not a fair comparison and tomorrow we might come back and say … no good (for whatever reason).. but at £107 for that particular phone… compare – how much would it costs for a Raspberry Pi 3, uninterruptable power supply able to keep the phone going for something like a couple of days with no power, a high-def touch screen, a solid case for the whole thing.. indeed ,it might be possible to use a cheap tablet…  I was looking on Ebay – 16GB Android tablet – perfectly working except for missing buttons and cracked digitiser – around £10

One thing to note – I’ve turned rsyslog messages off – it was spitting out all sorts of unwanted helpful messages when you changed brilliance on the phone or disconnected the USB etc –REALLY annoying.. If you feel you need logging on -

sudo service rsyslog start

That will stay on until the next stop-start…

Node-Red running on a K10000 phoneSuch a shame it isn’t clear how to access some of the peripherals like audio. But who knows – someone reading this article may already have solved such challenges.

Please note: the pretty colours on the right do not come as standard. Click images to see larger versions.

This is really starting to look GOOD!!!!

Revelation: I’ve now taken on-board ideas from others and thanks for that – both people in here and on Google+ – most of the other solutions are longwinded and need other packaged  so up to now Linux Deploy – I’m now installing on my old Nexus 7 2012 UTTERLY SUCCESSFULLY (though not the fastest kid on the block) ( after rooting it with the Nexus toolkit ) - using Linux Deploy (which is completely self-contained and offers full root access – is looking the best). The ONLY thing you can’t do is use the Android peripherals – because of lack of UK info but this morning I figured it all out. 

We’ve also tested this one OnePlus One (model BACON) and a Xiaomi Redmi 3 model IDO). The K10000 has now been up for several days.

Ok, bear with me – you have Node-Red on Linux – and MQTT. So, you run Tasker on the phone (in the Android section) with MQTT – and now you have access to and control of all of the Android facilities that TASKER can handle (i.e. just about the lot) from within the Debian environment. Doddle.. now all I need is some time!!

Facebooktwittergoogle_pluspinterestlinkedin

Track Broadband Outage in Node Red

connectionActually I could have given this a range of titles – as the following is useful in a range of situations. But first let me explain my problem:

The problem: I’ve been having broadband issues. The broadband cuts out for a few minutes without warning and then reconnects.  I just wasted a lot of time getting onto my router people (TP-Link – they are quite helpful) only to discover the same thing happens with the original rubbish Plusnet router (we’re using high speed fibre-to-cabinet broadband, around 70 meg or so – but it still looks like a normal phone line connection coming into the house so not TOO many modems support this – TP-Link does).

Of course Plusnet and BT say “nothing wrong here mate” and the problem really is intermittent – not had a failure in 2 days now but last week had several in one day. 

The solution: Well, not so much a solution but at least a means to easily monitor any future issues so I can act on them if things get out of hand. The answer for now is to log these events. I set up a routine to log failures by pinging Google every 30 seconds – of course it takes the modem 2 or 3 minutes to recover from a loss of connection so that log was getting large. By version 2 I was only logging CHANGE of state.

Node-Red SQL inject

Above is how I store the info in SQLITE. I use a PING node and the SQLITE node…  and here’s that function in the middle.

context.global.shot=context.global.shot|0;

if (msg.payload===false)
{
if (context.global.shot===0)
    {
        context.global.shot=1;
        msg.topic="insert into pings (ping) VALUES(" + 0 + ")";
        return msg;
    }
}
else
{
if (context.global.shot==1)
    {
        context.global.shot=0;
        msg.topic="insert into pings (ping) VALUES(" + 1 + ")";
        return msg;
    }
}

The actual table is in a database called IOT.DB and here’s the table.

CREATE TABLE 'pings' ('thetime' DATETIME PRIMARY KEY NOT NULL DEFAULT CURRENT_TIMESTAMP, 'ping' INTEGER)

So that’s simply storing away the date and time – and 0 for disconnect, 1 for reconnect.

To make that useful what I needed was for the computer – at one minute to midnight – to send me a report FOR THAT DAY but only if there was actually something to report. That I’ve done below.

image

The first (blue) node is simply a Node-Red inject that sends an empty message at one minute to midnight every night. The second is a function to build up a SQLITE query to get all the events for that day. The last one is an email node set up to send me emails on Gmail.

var now = new Date();
function pad(n) {
    return (n < 10) ? ("0" + n) : n;
}
var today=now.getFullYear() + "-"+pad((now.getMonth()+1))+"-"+ pad(now.getDate());

msg.topic="select * from pings where thetime >= Datetime('"+ today +" 00:00:00') and thetime <= Datetime('"+ today+" 23:59:59')";
return msg;

And finally having sent that off to the SQLITE node, I get an array back of the dates and states… which I send off to myself in email.

msg.topic="Hollyberry Connection Notice";
var a=msg.payload;
msg.payload="";

if (a.length!==0)
{
    for (var i in a)
    {
    msg.payload+="\r\n"+ ((a[i].ping !== 0) ? "Regained" : "Lost") +" connection " + a[i].thetime;
    }
return(msg);  
}

And that, hopefully will keep me informed of any issues with the broadband while we’re away enjoying the sun next week. Hope you find that useful – I’m sure there are many variations of this which might be useful for general monitoring.

Node-Red constantly surprises me with how simple it is to do just about anything I want.

Facebooktwittergoogle_pluspinterestlinkedin

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