====== SQLite ======
===== Introduction =====

SQLite Home page [[http://www.sqlite.org/]]

Wikipedia [[http://de.wikipedia.org/wiki/SQLite]]

Dokumentation [[http://www.sqlite.org/docs.html]]

Tutorials [[http://www.tutorialspoint.com/sqlite]]

==== Install ====
<code> sudo apt-get install sqlite3
</code>

===== Schema for Sensors =====
<code>
table Log (
  Id       integer primary key autoincrement,
  DateI    integer, # Insert timestamp unix time
  Msg      text,    # Log message text
  LogLevel text,    # level of importance critical, important, info
  value    real     # value if relevant for mlog message
          )
  
table Sensor (
  Id          integer primary key autoincrement,
  DateI       integer, # Insert timestamp unix time
  Sensor      text,    # Sensor ID
  Sonsortyp   text,    # Sensor type
  Room        text     # Room where sensor is installed, eg. Outside, Technik, Basteln, UGFlur
             )
  
 
table Measure (
  Id       integer primary key autoincrement,
  DateI    integer, # Insert timestamp unix time
  Sensor   text,    # Sensor ID
  Valuetyp text,    # Value type eg, temperature, humidity
  value    real     # measured value
              )
  
 ------------- Views ----------------

View LogV
    Select *,
           datetime(dateI, 'unixepoch') as STRDate, 
           strftime('%Y',datetime(dateI, 'unixepoch')) as Year,
           strftime('%d',datetime(dateI, 'unixepoch')) as day,
           strftime('%m',datetime(dateI, 'unixepoch'))as month,
           strftime('%H',datetime(dateI, 'unixepoch')) as hour,
           strftime('%M',datetime(dateI, 'unixepoch'))as minute, 
           strftime('%S',datetime(dateI, 'unixepoch')) as second,
    from Log
 
View SensorV
    Select *,
           datetime(dateI, 'unixepoch') as STRDate, 
           strftime('%Y',datetime(dateI, 'unixepoch')) as Year,
           strftime('%d',datetime(dateI, 'unixepoch')) as day,
           strftime('%m',datetime(dateI, 'unixepoch'))as month,
           strftime('%H',datetime(dateI, 'unixepoch')) as hour,
           strftime('%M',datetime(dateI, 'unixepoch'))as minute, 
           strftime('%S',datetime(dateI, 'unixepoch')) as second,
    from Sensor
 
View MeasureV 
    Select *, 
           datetime(dateI, 'unixepoch') as STRDate, 
           strftime('%Y',datetime(dateI, 'unixepoch')) as Year,
           strftime('%d',datetime(dateI, 'unixepoch')) as day,
           strftime('%m',datetime(dateI, 'unixepoch'))as month,
           strftime('%H',datetime(dateI, 'unixepoch')) as hour,
           strftime('%M',datetime(dateI, 'unixepoch'))as minute, 
           strftime('%S',datetime(dateI, 'unixepoch')) as second,
     from Measure
 </code>    
  
 ==== Auwertungs views ====

<code>
create view latestvaluelabor1 as 
     SELECT value 
     FROM Measure_V_A 
     WHERE sensor = 'Labor-1' 
     ORDER BY dateI DESC 
     LIMIT 1;
 </code>

===== Example code =====
==== convert unix timestamp to date, year, month, etc ====
<code>
SELECT datetime(dateI, 'unixepoch') as STRDate, strftime('%Y',datetime(dateI, 'unixepoch')) as Year
, strftime('%d',datetime(dateI, 'unixepoch')) as day
, strftime('%m',datetime(dateI, 'unixepoch'))as month
, strftime('%H',datetime(dateI, 'unixepoch')) as hour
, strftime('%M',datetime(dateI, 'unixepoch'))as minute
, strftime('%S',datetime(dateI, 'unixepoch')) as second from Daten
</code>