TestSQLitePHP.ino

/*
 Project: TestSQLitePHP
 Author: Michael Gries
 Creation: 2014-08-23
 Modified: 2014-08-26
*/

/*
   get time and pin1 and pin2 state and run a linux process on the linino server to write to database table
*/

/*
 project-Reference        http://yunadventures.blogspot.de/
                          http://yunadventures.blogspot.de/2013/11/setting-up-yun-to-write-pin-data-to.htm
                     
 SQLite                   http://de.wikipedia.org/wiki/Sqlite
 PHP                      http://wiki.openwrt.org/doc/howto/php
 
 Yun specific:            http://playground.arduino.cc/Hardware/Yun
 Yun Package Manager      http://arduino.cc/en/Tutorial/YunPackageManager
 used Libraries:          http://arduino.cc/en/Guide/Libraries
 Bridge-Library           http://arduino.cc/en/Reference/YunBridgeLibrary

 GitHub source ref        https://github.com/alnitak1000/Arduino-Yun-pin-output-to-sqlite-db-project
*/


#include 
#include 

// how often to run the pin read (in milliseconds)
const unsigned long RUN_INTERVAL_MILLIS = 60000; 

// the last time we ran the pin read (initialized to 60 seconds ago,
// so the pins are read immediately when we start up)
unsigned long lastRun = (unsigned long)-60000;
// variables for setting up time 
Process date;                 // process used to get the date
String timeString;
int ledExtGreen = 6;

void setup() {
  initLED();
  Bridge.begin();
  Console.begin();
  // while(!Console); // waiting for console - use only for debug reason !!!
  Console.print("connected on ... : ");

  // run an initial date process. Should return:
  // hh:mm:ss :
  if (!date.running())  {
    date.begin("date");
    date.addParameter("+%D +%T");
    date.run();
    Console.println(date.readString());
  } // end if
} // end setup

void loop() {
  // put your main code here, to run repeatedly:
  // get the number of milliseconds this sketch has been running
  unsigned long now = millis();

  // run again if it's been RUN_INTERVAL_MILLIS milliseconds since we last ran
  if (now - lastRun >= RUN_INTERVAL_MILLIS) {

    // remember 'now' as the last time we checked the pins
    lastRun = now;
   analogWrite(ledExtGreen, 255);
   // get the date and time
    if (!date.running())  {
      date.begin("date");
      date.addParameter("+%Y-%m-%d %T");
      date.run();
    } // end if
    
    // put the date and time into a string variable
    while (date.available()>0) {
      timeString = date.readString(); 
    } // end while
    Console.print("Timestamp: ");
    Console.print(timeString);
    
    //get the pin states and add them to the database
    String pin1 = String(getSensorValue(1));
    String pin2 = String(getSensorValue(2));
    //String pin2 = getWifiInfo();
    runSqlQuery(timeString,pin1,pin2);
    analogWrite(ledExtGreen, 16);
  } // end if
} // end loop

// function to run the appending of the data to the database
unsigned int runSqlQuery(String time, String pin1, String pin2){
   Process p;
   String cmd = "sqlite3 ";
   String paramstring1 = "-line ";
   // set the path and name of your database here
   String paramstring2 ="/mnt/sda1/arduino/www/TestSQLitePHP/yun.db ";
   // insert a row with time and sensor data 
   String paramstring3 ="'insert into status (\"time\",\"pin1\",\"pin2\") Values (\""+time+"\","+pin1+","+pin2+");'";
   // get the error code
   String paramstring4 =" ; echo $?";
   p.runShellCommand(cmd + paramstring1 + paramstring2 + paramstring3+ paramstring4);
   Console.print("process run ... return code: ");
     // A process output can be read with the stream methods
   while (p.available()>0) {
    char c = p.read();
    Console.println(c);
   }
  Console.flush(); // Ensure the last bit of data is sent.
}
  
// function to get sensor values with debugging information
int getSensorValue(int pinNum) {
  Console.print("Reading pin "+ String(pinNum)+ ": value=");
  int value = analogRead(pinNum);
  Console.println(value);
  return value;
}

// function WIFI info
String getWifiInfo(){
  String wifiInfo = "";
  Process wifiCheck;  // initialize a new process
  wifiCheck.runShellCommand("/usr/bin/pretty-wifi-info.lua |grep Active");  // preconfigured script out of the box
  while (wifiCheck.available() > 0) {
    char c = wifiCheck.read();
    wifiInfo += c;
  }
  Console.print(wifiInfo);
  Console.flush(); // Ensure the last bit of data is sent.
  return wifiInfo;
}

// function LED setup
void initLED(){
  pinMode(ledExtGreen, OUTPUT);
  analogWrite(ledExtGreen, 255);
  delay(1000);
  analogWrite(ledExtGreen, 16);
}

    

www.h

/*
 Project: TestSQLitePHP - www folder content
 Author: Michael Gries
 Creation: 2014-08-23
 Modified: 2014-08-26
*/

/*
 www folder content:
 table.php - PHP script to visualize content of SQLite database on webbrowser
 sqlite3-version.py - support Python script to get version of installed SQLite package
 read_yun_status.py - support Python script to get first record of SQLite database (yun.db)
                                                                in table (status)
*/

/*
 open LuCI by using url http://gries.spdns.de/
 access table.php by using url http://gries.spdns.de/sd/TestSQLitePHP/table.php
*/

/*
  PuTTY ssh service:
  cd /mnt/sda1/arduino/www/TestSQLitePHP
  ls -l
  sqlite3 yun.db
  
  SQLite version 3.7.12.1 2012-05-22 02:45:53
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite>
*/

/*
 before running PHP script table.php a new table 'status' must be defined in yun.db
 CREATE TABLE status (time TEXT, pin1 TEXT, pin2 TEXT);
 INSERT INTO status (time, pin1, pin2) VALUES ( '2014-08-23, '100', '200'); 
*/

/*
 common commands in sqlite-cli:
  .tables  // show all available tables
  select * from status; // show all records in table 'status'
  SELECT DISTINCT pin1 FROM status // shows each different record value once
  .exit // ends sqlite-cli interface
  CTRL+D quids all immediately
*/

/*
 preconfigured script out of the box: /usr/bin/pretty-wifi-info.lua
  Current WiFi configuration
  SSID: Rauchmelder RA-350F
  Mode: Client
  Signal: 57%
  Encryption method: WPA2 PSK (NONE)
  Interface name: wlan0
  Active for: 2861 minutes
  IP address: 192.168.178.10/255.255.255.0
  MAC address: 90:A2:DA:F2:02:76
  RX/TX: 45212/10592 KBs
*/

/*
 examples using /usr/bin/pretty-wifi-info.lua:
  /usr/bin/pretty-wifi-info.lua | grep Signal   // getting content of line containing 'Signal'
  /usr/bin/pretty-wifi-info.lua | grep Active   // getting content of line containing 'Active'
  /usr/bin/pretty-wifi-info.lua | grep RX/TX    // getting content of line containing 'RX/TX'
  
  first use e.g. PuTTY to check examples results
*/

    

www/table.php

  
	
	
	
  
  
	
Arduino Yun pin outputs
  	query('SELECT * FROM status');
	 	$cols = $result->numColumns();
		print "	  \n";
		print "		  \n";
		for ($i = 0; $i < $cols; $i++) { 
				print "		   	 \n"; 
 		}//end for
		print "		  \n";

		while ($row = $result->fetchArray()){
			print "		  \n";
			for ($i = 0; $i < $cols; $i++) { 
			   print "		   \n";
			} //end for 
			print "		  \n";
		} // end while loop

		print "	   
".$result->columnName($i)."
".$row[$i] . "
 \n";	
  	?>
  


    

www/sqlite3-version.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = None

try:
    con = lite.connect('test.db')
    
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    print "SQLite version: %s" % data                
    
except lite.Error, e:
    
    print "Error %s:" % e.args[0]
    sys.exit(1)
    
finally:
    
    if con:
        con.close()

    

www/read_yun_status.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = None

try:
   con = lite.connect('yun.db') 
   cur = con.cursor()
   cur.execute('SELECT * from status;') 
   data = cur.fetchone() 
   print data

except lite.Error, e: 
   print "Error %s:" % e.args[0]
   sys.exit(1)

finally: 
   if con:
      con.close()