Category Archives: SQL

Android Phone as Server

Android, Apache, Debian, MQTT, Node Red, SQL, SQL Lite, The Script

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

broadband, Node Red, SQL, SQL Lite

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

Graphing Wonders

MQTT, mysql, Raspberry Pi 2, SQL

Tonight I’ve been building on my earlier work on graphing, if you read a few blogs back you’ll know I got some basic graphing running on the Raspberry Pi 2, taking MQTT data from a sensor and storing it in MYSQL.

Well that was fine apart from the graphs having no anti-aliasing and hence looking a bit naff. The big problem was I could not get my head around how to handle missing items- if you have a sensor sending MQTT messages on a regular basis, well, you might have any number of reasons why some might be missed. If relying on waking up for a solar installation or just you’ve been in there and been messing with the unit or upgrading it – you could end up instead of a nice set of regular readings, some chunks missing!

Well, as of this evening the whole graphics library is in the bin, saving me some room in the PI. I discovered Highcharts.com – which for all except proper commercial use can be had for free. They use JQUERY and 2 highcharts libraries all of which are on CDNs and hence you only need 3 links to access them.

I suggest you go look at DEMOS, BASIC LINE, TIME DATA WITH IRREGULAR INTERVALS. It’s wonderful. ONE PHP/Javascript page does this.

Not only do you get a relatively simple page but it’s HTML5, the imagery is really nice etc. AND you’re looking at filling in with data including the time-date and your reading…. either one graph – or many lines superimposed and if the latter, colouring the different lines and matching them up is all done automatically, you don’t have to lift a finger. Took me less than an hour to take the Javascript code they supply, bang some PHP in there to populate the table with data from mysql and produce nice graphs on demand all done inside the Raspberry Pi. Pretty much all modern browsers will support the output…including the naff browser that comes with the Pi itself.

Here’s a sample – remembering that the data from the two lines were at completely different intervals and one had more samples than the other! All scaling, smoothing etc. is done completely automatically.

graphing

And if you don’t like the idea of relying on external libraries you can of course download the lot onto your Pi. Personally, I look at it this way, if the Internet isn’t working, I can’t access the graphs anyway! Hopefully now I’m using my un-interruptible power supply idea that will happen less and less (the bottom of the range Juicebar was involuntarily tested this evening – I forgot to plug it in – it powered the Pi for nearly 2 hours).

I ended up bringing everything into a PHP function with 2 arguments – the title of the line – and the SQL statement needed to get the results. I store all of my temperature readings with 3 fields – the value, a timedate stamp and the location – so I can easily select for example all front room readings from X date to Y  date. The titles and data above are just dummy data I entered into the database. I have 5 months worth of internal, external temperature and internal humidity data available to import from Grovestreams as soon as I figure out how to do it.

If you look at their examples here you’ll see the source code is simply not that hard and dropping in some PHP to replace the static figures takes no time. This has saved me so much time and effort.

After some messing about, automating commas in the right place and struggling with the ridiculous fact that PHP doesn’t by default recognise global variables in a function (EH!!!?!!?!?!) I finally reduced the effort to adding lines to the graph to this..

drawgraph("Autumn","select value,UNIX_TIMESTAMP(logged) as logging from readings where location='office'");
drawgraph("Spring","select value,UNIX_TIMESTAMP(logged) as logging from readings where location='kitchen'");

That’s it… as many lines as needed.  I’m sure once you look at this if it’s of use you’ll come up with your own scheme using your own preferred language.

I really do need to back up this Pi. There has to be a better way than turning it off for several hours to copy the SD…

Facebooktwittergoogle_pluspinterestlinkedin

Flashing Lights on Raspberry Pi 2

mysql, Raspberry Pi 2, SQL, Wordpress

So – having managed to get WordPress up and running on the P2 I thought it might be nice to step back toward the original plan and get some home control running. I like PHP so I thought that it might be nice to have a PHP page with some security running – with buttons. I stumbled on an article that suggested the first thing to do is get phpMyAdmin running – so that you can mess around with a database on the Pi.

phpMyAdminSure enough, 10 minutes after discovering this article I was up and running with phpMyAdmin in a browser. Amazing.

Then this link – which takes a little longer than 10 minutes…

So that gets you a nice web page with which you can control pins – once you have logged in of course.

And all of that is great, but I don’t like the constant polling of the database every 5 seconds – from my brief exposure to this, the PHP page accepts buttons and returns the status – a background script looks at the database and sets GPIO accordingly.  Put it this way it’s never going to replace OpenHab but at least out of the process you get a little experience of all of this, not to mention phpMyAdmin – and in my case a little better understanding of moving files and privileges around in the Pi… and it’s nice to know this older stuff works in the new model! I did have issues with the higher pins not doing anything but I’m far to new to this device to claim it’s anything other than potential finger trouble or some difference with Pi 2 perhaps…

gpio

Facebooktwittergoogle_pluspinterestlinkedin