def rebuildsystemdatadb(tabledict): runquery = "False" querylist = [] if 'netstatus' in tabledict: runquery = True table = 'netstatus' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( address text, connected boolean, WANaccess text, latency real, SSID text, dhcpstatus boolean default 0, mode text , onlinetime text, offlinetime text, statusmsg text)" ) querylist.append("insert into " + table + " values ('',0,'','','','','','','','')") if 'netconfig' in tabledict: runquery = True table = 'netconfig' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (enabled boolean, SSID text, mode text, aprevert text default 'temprevert', addtype text, address text, gateway text, dhcpstart text default '192.168.0.70', dhcpend text default '192.168.1.99', apreverttime integer default 60, stationretrytime integer default 300, laststationretry text, pingthreshold integer default 200)" ) querylist.append( "insert into " + table + " values ('1','OurHouse','station','temprevert','static','192.168.1.30','192.168.1.1','','',60,300,0,200)" ) if 'systemflags' in tabledict: runquery = True table = 'systemflags' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (name text, value boolean default 0)") querylist.append("insert into " + table + " values ('reboot', 0)") querylist.append("insert into " + table + " values ('netconfig', 0)") querylist.append("insert into " + table + " values ('updateiicontrollibs', 0)") querylist.append("insert into " + table + " values ('updatecupidweblib', 0)") if 'metadata' in tabledict: runquery = True table = 'metadata' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( devicename text, groupname text)") querylist.append("insert into " + table + " values ( 'My CuPID', 'None' )") if 'versions' in tabledict: runquery = True table = 'versions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( item text primary key, version text, versiontime text, updatetime text)" ) if runquery: print(querylist) sqlitemultquery(systemdatadatabase, querylist)
def rebuildsessiondb(): querylist = [] ### Session limits table = 'sessionlimits' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (username text primary key, sessionsallowed real default 5 )") querylist.append("insert into " + table + " values ('viewer', 5)") querylist.append("insert into " + table + " values ('controller', 5)") querylist.append("insert into " + table + " values ('administrator', 5)") querylist.append("insert into " + table + " values ('owner', 3)") querylist.append("insert into " + table + " values ('admin', 3)") querylist.append("insert into " + table + " values ('colin', 5)") ### Settings table table = 'settings' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (sessionlength real default 600, sessionlimitsenabled real default 1, updatefrequency real)" ) querylist.append("insert into " + table + " values (600,1,30)") ### Session table table = 'sessions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (username text, sessionid text, sessionlength real, timecreated text, apparentIP text , realIP text)" ) ### Sessions summary table = 'sessionsummary' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (username text, sessionsactive real)") querylist.append("insert into " + table + " values ('viewer', 0)") querylist.append("insert into " + table + " values ('controller', 0)") querylist.append("insert into " + table + " values ('administrator', 0)") ### Session log table = 'sessionlog' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (username text, sessionid text, time text, action text, apparentIP text, realIP text)" ) #print(querylist) sqlitemultquery(sessiondatabase, querylist)
def rebuildsafedata(): runquery = False querylist = [] querylist.append('drop table if exists wireless') querylist.append('create table wireless (SSID text, password text)') sqlitemultquery(safedatabase, querylist)
def rebuildrecipesdb(tabledict): runquery = False querylist = [] addentries = True if 'recipes' in tabledict: runquery = True table = 'stdreflow' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( stagenumber integer default 1, stagelength real default 0, setpointvalue real default 0, lengthmode text default 'setpoint', controlalgorithm text default 'on/off 1')" ) if addentries: querylist.append("insert into " + table + " values ( 1, 300, 40, 'setpoint','on/off 1')") querylist.append("insert into " + table + " values ( 2, 600, 60, 'setpoint','on/off 1')") querylist.append("insert into " + table + " values ( 3, 600, 100, 'setpoint','on/off 1')") querylist.append("insert into " + table + " values ( 4, 300, 40, 'setpoint','on/off 1')") if runquery: print(querylist) sqlitemultquery(recipedatabase, querylist)
def rebuildsystemdatadb(tabledict): runquery = "False" querylist = [] if 'netstatus' in tabledict: runquery = "True" table = 'netstatus' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( IPAddress text, gateway text, WANaccess text, networkSSID text, dhcpstatus boolean default 0, hostapdstatus boolean default 0)") querylist.append("insert into " + table + " values ('','','','','','')") if 'netconfig' in tabledict: runquery = "True" table = 'netconfig' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (nettype text, addtype text, address text, gateway text, networkSSID text, networkpassword text,dhcpstart text default '192.168.0.70', dhcpend text default '192.168.1.99')") querylist.append("insert into " + table + " values ('station','static','192.168.1.40','192.168.1.1','','')") if 'metadata' in tabledict: runquery = "True" table = 'metadata' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( devicename text, groupname text)") querylist.append("insert into " + table + " values ( 'My CuPID', 'None' )") if 'versions' in tabledict: runquery = True table = 'versions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( item text primary key, version text, versiontime text, updatetime text)") if runquery: print(querylist) sqlitemultquery(systemdatadatabase, querylist)
def rebuildmotesdb(tabledict): runquery = False querylist = [] addentries = True if 'readmessages' in tabledict: runquery = True table = 'readmessages' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( time text default '', message text default '' )") if 'queuedmessages' in tabledict: runquery = True table = 'queuedmessages' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( queuedtime text default '', message text default '' )") if 'sentmessages' in tabledict: runquery = True table = 'sentmessages' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( queuedtime text default '', senttime text default '', message text default '' )") if runquery: print(querylist) sqlitemultquery(motesdatabase, querylist)
def rebuildusersdata(argument=None): from pilib import gethashedentry querylist = [] runquery = True querylist.append('drop table if exists users') enteringusers = True runquery = False index = 1 querylist.append( 'create table users (id integer primary key not null, name text not null, password text not null, email text not null, temp text not null, authlevel integer default 0)') if argument == 'defaults': runquery = True entries = [{'user': '******', 'password': '******', 'email': '*****@*****.**', 'authlevel': 1}, {'user': '******', 'password': '******', 'email': '*****@*****.**', 'authlevel': 4}, {'user': '******', 'password': '******', 'email': '*****@*****.**', 'authlevel': 3}] index = 1 for entry in entries: hashedentry = gethashedentry(entry['user'], entry['password']) querylist.append( "insert into users values(" + str(index) + ",'" + entry['user'] + "','" + hashedentry + "','" + entry[ 'email'] + "',''," + str(entry['authlevel']) + ")") index += 1 else: while enteringusers: validentry = True userinput = raw_input("Enter username or Q to stop: ") if userinput == 'Q': print('exiting ...') break passone = raw_input("Enter password: "******"Confirm password: "******"Enter user email") authlevelentry = raw_input("Enter authorization level (0-5)") if passone != passtwo: validentry = False print('passwords do not match') if not len(passone) >= 6: validentry = False print('passwords must be at least six characters') if not emailentry.find('@') > 0: validentry = False print('Email does not appear to be valid') if validentry: hashedentry = gethashedentry(userinput, passone) querylist.append("insert into users values(" + str( index) + ",'" + userinput + "','" + hashedentry + "','" + emailentry + "',''," + authlevelentry + ")") index += 1 runquery = True if runquery: print(querylist) sqlitemultquery(usersdatabase, querylist)
def rebuildapdata(SSID='cupidwifi', password='******'): querylist = [] querylist.append('drop table if exists apsettings') querylist.append("create table apsettings (SSID text default 'cupidwifi', password text default 'cupidpassword')") querylist.append( "insert into apsettings values('" + SSID + "','" + password + "')") sqlitemultquery(safedatabase, querylist)
def recordspidata(database, valuedict): # This is incomplete and hardcoded partially querylist = [] for key, value in valuedict.iteritems(): querylist.append(pilib.makesqliteinsert('inputsdata', valuelist=[key, 'SPI1', 'TC', '1', value, 'F', pilib.gettimestring(), 1, key])) querylist.append(pilib.makesqliteinsert('ioinfo', valuelist=[key, key])) pilib.sqlitemultquery(database, querylist)
def updateowfstable(database, tablename, busdevices): from pilib import makesqliteinsert, sqlitemultquery querylist = [] for device in busdevices: # print(device.id) # print([device.address, device.family, device.id, device.type, device.crc8]) querylist.append( makesqliteinsert(tablename, [device.address, device.family, device.id, device.type, device.crc8])) # print(querylist) sqlitemultquery(database, querylist)
def updateioinfo(database, table): from pilib import readalldbrows, sqlitedatumquery, sqlitemultquery tabledata = readalldbrows(database, table) querylist = [] for item in tabledata: itemid = item['id'] name = sqlitedatumquery(database, 'select name from ioinfo where id=\'' + itemid + '\'') querylist.append(database, 'update ' + table + ' set name=\'' + name + '\' where id = \'' + itemid + '\'') if querylist: sqlitemultquery(querylist)
def rebuildsystemdatadb(tabledict): runquery = "False" querylist = [] if 'netstatus' in tabledict: runquery = True table = 'netstatus' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( WANaccess boolean default 0, WANaccessrestarts integer default 0, latency real default 0, mode text default 'eth0wlan0bridge', onlinetime text, offlinetime text default '', netstate integer default 0, netstateoktime text default '', updatetime text '', statusmsg text default '', netrebootcounter integer default 0)") querylist.append("insert into " + table + " default values") if 'netconfig' in tabledict: runquery = True table = 'netconfig' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (requireWANaccess integer default 1, WANretrytime integer default 60, mode text default eth0wlan0bridge, SSID text default '', aprevert text default '', addtype text default 'dhcp', address text default '192.168.1.30', gateway text default '192.168.0.1', dhcpstart text default '192.168.0.70', dhcpend text default '192.168.0.99', apreverttime integer default 60, stationretrytime integer default 300, laststationretry text default '', pingthreshold integer default 2000, netstatslogenabled boolean default 0, netstatslogfreq integer default 60, apoverride boolean default 0, apoverridepin integer default 21, rebootonfail integer default 0, rebootonfailperiod default 900)") querylist.append("insert into " + table + " default values") if 'systemflags' in tabledict: runquery = True table = 'systemflags' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (name text, value boolean default 0)") querylist.append("insert into " + table + " values ('reboot', 0)") querylist.append("insert into " + table + " values ('netconfig', 0)") querylist.append("insert into " + table + " values ('updateiicontrollibs', 0)") querylist.append("insert into " + table + " values ('updatecupidweblib', 0)") if 'metadata' in tabledict: runquery = True table = 'metadata' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( devicename text, groupname text)") querylist.append("insert into " + table + " values ( 'My CuPID', 'None' )") if 'uisettings' in tabledict: runquery = True table = 'uisettings' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( 'setting' text, 'group' text, 'value' text)") querylist.append("insert into " + table + " values ( 'showinputgpiologs', 'dataviewer', '1' )") querylist.append("insert into " + table + " values ( 'showinput1wirelogs', 'dataviewer', '1' )") querylist.append("insert into " + table + " values ( 'showchannellogs', 'dataviewer', '1' )") if 'versions' in tabledict: runquery = True table = 'versions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( item text primary key, version text, versiontime text, updatetime text)") if runquery: print(querylist) sqlitemultquery(systemdatadatabase, querylist)
def recordspidata(database, valuedict, execute=False): # This is incomplete and hardcoded partially querylist = [] for key, value in valuedict.iteritems(): querylist.append(pilib.makesqliteinsert('inputs', valuelist=[key, 'SPI1', 'TC', '1', 'SPITC1', value, 'F', pilib.gettimestring(), 1, '',''])) querylist.append(pilib.makesqliteinsert('ioinfo', valuelist=[key, key, ''])) if execute: pilib.sqlitemultquery(database, querylist) return querylist
def rebuildsessiondb(): querylist = [] ### Session limits table = 'sessionlimits' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (username text primary key, sessionsallowed real default 5 )") querylist.append("insert into " + table + " values ('viewer', 5)") querylist.append("insert into " + table + " values ('controller', 5)") querylist.append("insert into " + table + " values ('administrator', 5)") querylist.append("insert into " + table + " values ('owner', 3)") querylist.append("insert into " + table + " values ('admin', 3)") querylist.append("insert into " + table + " values ('colin', 5)") ### Settings table table = 'settings' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (sessionlength real default 600, sessionlimitsenabled real default 1, updatefrequency real)") querylist.append("insert into " + table + " values (600,1,30)") ### Session table table = 'sessions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (username text, sessionid text, sessionlength real, timecreated text, apparentIP text , realIP text)") ### Sessions summary table = 'sessionsummary' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (username text, sessionsactive real)") querylist.append("insert into " + table + " values ('viewer', 0)") querylist.append("insert into " + table + " values ('controller', 0)") querylist.append("insert into " + table + " values ('administrator', 0)") ### Session log table = 'sessionlog' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (username text, sessionid text, time text, action text, apparentIP text, realIP text)") # print(querylist) sqlitemultquery(sessiondatabase, querylist)
def updateowfsinputentries(database, tablename, devices, execute=True): from pilib import readalldbrows, controldatabase, sqlitemultquery querylist = [] querylist.append("delete from '" + tablename + "' where interface='1wire'") for device in devices: querylist.append("insert into inputs values ('" + device.sensorid +"','" + '1wire' + "','" + str(device.type) + "','" + str(device.id) + "','" + str(device.name) + "','" + str(device.value) + "','" + str(device.unit)+ "','" + str(device.polltime) + "'," + str(device.pollfreq) + ",'" + device.ontime + "','" + device.offtime + "')") print(querylist) if execute: sqlitemultquery(controldatabase, querylist) return querylist
def updateowfsentries(database, tablename, busdevices, myProxy=None): import pilib querylist = [] querylist.append('delete from ' + tablename + ' where interface = \'i2c1wire\'') # We're going to set a name because calling things by their ids is getting # a bit ridiculous, but we can't have empty name fields if we rely on them # being there. They need to be unique, so we'll name them by type and increment them for device in busdevices: # print(device.id) if device.type == 'DS18B20': sensorid = 'i2c1wire' + '_' + device.address # Get name if one exists name = pilib.sqlitedatumquery(database, 'select name from ioinfo where id=\'' + sensorid + '\'') # If doesn't exist, check to see if proposed name exists. If it doesn't, add it. # If it does, keep trying. if name == '': for index in range(100): # check to see if name exists name = device.type + '-' + str(int(index + 1)) print(name) foundid = pilib.sqlitedatumquery(database, 'select id from ioinfo where name=\'' + name + '\'') print('foundid' + foundid) if foundid: pass else: pilib.sqlitequery(database, pilib.makesqliteinsert('ioinfo', valuelist=[sensorid, name], valuenames=['id', 'name'])) break # Is it time to read temperature? # At the moment, we assume yes. device.readprop('temperature', myProxy) # print('temperature:') # print(device.temperature) querylist.append(pilib.makesqliteinsert(tablename, [sensorid, 'i2c1wire', device.type, device.address, name, float(device.temperature), 'F', pilib.gettimestring(), ''])) # print(querylist) pilib.sqlitemultquery(database, querylist)
def updateowfstable(database, tablename, busdevices, execute=True): from pilib import makesqliteinsert, sqlitemultquery querylist = [] for device in busdevices: # print(device.id) # print([device.address, device.family, device.id, device.type, device.crc8]) querylist.append( makesqliteinsert(tablename, [ device.address, device.family, device.id, device.type, device.crc8 ])) # print(querylist) if execute: sqlitemultquery(database, querylist) return querylist
def recordspidata(database, valuedict, execute=False): # This is incomplete and hardcoded partially querylist = [] for key, value in valuedict.iteritems(): querylist.append( pilib.makesqliteinsert('inputs', valuelist=[ key, 'SPI1', 'TC', '1', 'SPITC1', value, 'F', pilib.gettimestring(), 1, '', '' ])) querylist.append(pilib.makesqliteinsert('ioinfo', valuelist=[key, key])) if execute: pilib.sqlitemultquery(database, querylist) return querylist
def updateowfsinputentries(database, tablename, devices, execute=True): from pilib import readalldbrows, controldatabase, sqlitemultquery querylist = [] querylist.append("delete from '" + tablename + "' where interface='1wire'") for device in devices: querylist.append("insert into inputs values ('" + device.sensorid + "','" + '1wire' + "','" + str(device.type) + "','" + str(device.id) + "','" + str(device.name) + "','" + str(device.value) + "','" + str(device.unit) + "','" + str(device.polltime) + "'," + str(device.pollfreq) + ",'" + device.ontime + "','" + device.offtime + "')") print(querylist) if execute: sqlitemultquery(controldatabase, querylist) return querylist
def runowfsupdate(debug=False, execute=True): import time queries = [] from pilib import onewiredir, controldatabase, sqlitemultquery if debug: print('getting buses') starttime = time.time() busdevices = owfsgetbusdevices(onewiredir) if debug: print('done getting devices, took ' + str(time.time() - starttime)) print('updating device data') starttime = time.time() updateddevices = updateowfsdevices(busdevices) if debug: print('done reading devices, took ' + str(time.time() - starttime)) print('your devices: ') for device in busdevices: print(device.id) print('updating entries in owfstable') starttime = time.time() owfstableentries = updateowfstable(controldatabase, 'owfs', updateddevices, execute=execute) if debug: print('done updating owfstable, took ' + str(time.time() - starttime)) owfsinputentries = updateowfsinputentries(controldatabase, 'inputs', updateddevices, execute=execute) queries.extend(owfstableentries) queries.extend(owfsinputentries) if execute: sqlitemultquery(controldatabase, queries) return busdevices, queries
def rebuildrecipesdb(tabledict): runquery = False querylist = [] addentries = True if 'recipes' in tabledict: runquery = "True" table = 'stdreflow' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( stagenumber integer default 1, stagelength real default 0, setpointvalue real default 0, lengthmode text default 'setpoint', controlalgorithm text default 'on/off 1')") if addentries: querylist.append("insert into " + table + " values ( 1, 300, 40, 'setpoint','on/off 1')") querylist.append("insert into " + table + " values ( 2, 600, 60, 'setpoint','on/off 1')") querylist.append("insert into " + table + " values ( 3, 600, 100, 'setpoint','on/off 1')") querylist.append("insert into " + table + " values ( 4, 300, 40, 'setpoint','on/off 1')") if runquery: print(querylist) sqlitemultquery(recipedatabase, querylist)
ROM=tuple[0] temp=tuple[1] queryresult=pilib.sqlitequery(database,'select name from sensorinfo where sensorID = ' + "'" + ROM + "'") if not queryresult: name='' else: name = queryresult[0][0] valuelist = [tuple[0],tuple[1],'F',pilib.gettimestring(),1,name] query = pilib.makesqliteinsert('sensordata',valuelist) # print(query) querylist.append(query) # execute combined query #print(querylist) ######################################## # Do thermocouple stuff import subprocess tctemp=subprocess.check_output(['python3','/usr/lib/modwsgi/max31855-1.0/getmaxtemp.py']) print('temperature is ') print(tctemp) print(tctemp) querylist.append(pilib.makesqliteinsert('sensordata',['TC1','SPITC','TC',tctemp,'F',pilib.gettimestring(),1,''])) if querylist: print(querylist) pilib.sqlitemultquery(database,querylist) print('doing querylist')
while enabled: #print('enabled') polltime = pilib.sqlitedatumquery(pilib.sessiondatabase, 'select updatefrequency from \'settings\'') # Go through sessions and delete expired ones sessions = pilib.readalldbrows(pilib.sessiondatabase, 'sessions') sessions = pilib.readalldbrows(pilib.sessiondatabase, 'sessions') arrayquery = [] for session in sessions: sessionstart = pilib.timestringtoseconds(session['timecreated']) sessionlength = session['sessionlength'] if time.time() - sessionstart > sessionlength: arrayquery.append('delete from sessions where sessionid=\'' + session['sessionid'] + '\'') # Delete offensive sessions pilib.sqlitemultquery(pilib.sessiondatabase, arrayquery) # Reload surviving sessions and summarize sessions = pilib.readalldbrows(pilib.sessiondatabase, 'sessions') sessiondictarray = [] for session in sessions: found = 0 for dict in sessiondictarray: if dict['username'] == session['username']: found = 1 index = sessiondictarray.index(dict) dict['sessions'] += 1 sessiondictarray[index] = dict if not found: sessiondictarray.append({'username': session['username'], 'sessions': 1})
def rebuildcontroldb(tabledict): # Create databases entries or leave them empty? addentries = True querylist = [] runquery = False ### SystemStatus table if 'actions' in tabledict: runquery = True table = 'actions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (channelindex integer primary key, name text unique default 'myaction', enabled boolean default 0, actiontype text default 'email', actiondetail text default '*****@*****.**', conditiontype text default 'dbvalue',database text default 'controldata',tablename text default 'channels', variablename text default 'controlvalue', variablevalue text default '', operator text default '=',criterion text default '25',offdelay real default 0,ondelay real default 0,active boolean default 0, activereset boolean default 1, status boolean default 0,ontime text,offtime text,actionfrequency real default 60, lastactiontime text default '', statusmsg text default 'default msg')") if addentries: querylist.append("insert into " + table + " default values") ### SystemStatus table if 'systemstatus' in tabledict: runquery = True table = 'systemstatus' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (picontrolenabled boolean default 0, picontrolstatus boolean default 0, picontrolfreq real default 15 , lastpicontrolpoll text, inputsreadenabled boolean default 1, inputsreadstatus boolean default 0, inputsreadfreq real default 15, lastinputspoll text, enableoutputs boolean default 0, sessioncontrolenabled boolean, sessioncontrolstatus boolean, systemstatusenabled boolean, systemstatusstatus boolean, systemmessage text)") if addentries: querylist.append("insert into " + table + " values (0,0,15,'',1,0,15,'',0,1,0,1,0,'')") ### Indicators table if 'indicators' in tabledict: runquery = True table = 'indicators' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( name text primary key, type text, status boolean default 0, detail text)") addentries = True if addentries: querylist.append("insert into " + table + " values ('SPI_RGB1_R', 'SPI', 0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB1_G', 'SPI', 0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB1_B', 'SPI', 0,'blue')") querylist.append("insert into " + table + " values ('SPI_RGB2_R', 'SPI', 0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB2_G', 'SPI', 0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB2_B', 'SPI', 0,'blue')") querylist.append("insert into " + table + " values ('SPI_RGB3_R', 'SPI', 0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB3_G', 'SPI', 0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB3_B', 'SPI', 0,'blue')") querylist.append("insert into " + table + " values ('SPI_RGB4_R', 'SPI', 0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB4_G', 'SPI', 0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB4_B', 'SPI', 0,'blue')") querylist.append("insert into " + table + " values ('SPI_SC_R', 'SPI', 0,'red')") querylist.append("insert into " + table + " values ('SPI_SC_G', 'SPI', 0,'green')") querylist.append("insert into " + table + " values ('SPI_SC_B', 'SPI', 0,'blue')") querylist.append("insert into " + table + " values ('SPI_SC_Y', 'SPI', 0,'yellow')") ### Defaults table if 'defaults' in tabledict: runquery = True table = 'defaults' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( inputpollfreq real, outputpollfreq real)") addentries = True if addentries: querylist.append("insert into " + table + " values (60, 60)") ### Outputs table if 'outputs' in tabledict: runquery = True table = 'outputs' querylist.append('drop table if exists ' + table) querylist.append( 'create table ' + table + ' (id text primary key, interface text, type text, address text, name text, ' + 'value real, unit text, polltime text, pollfreq real)') ### Outputs table if 'inputs' in tabledict: runquery = True table = 'outputs' querylist.append('drop table if exists ' + table) querylist.append( 'create table ' + table + ' (id text primary key, interface text, type text, address text, name text, ' + 'value real, unit text, polltime text, pollfreq real)') ### OWFS Table if 'owfs' in tabledict: runquery = True table = 'owfs' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (address text primary key, family text, id text, type text, crc8 text)") ### Inputs Info Table if 'ioinfo' in tabledict: runquery = True table = 'ioinfo' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (id text primary key, name text)") if addentries: querylist.append( "insert into " + table + " values ('GPIO18', 'GPIO1')") querylist.append( "insert into " + table + " values ('GPIO23', 'GPIO2')") querylist.append( "insert into " + table + " values ('GPIO24', 'GPIO3')") querylist.append( "insert into " + table + " values ('GPIO25', 'GPIO4')") querylist.append( "insert into " + table + " values ('GPIO4', 'GPIO5')") querylist.append( "insert into " + table + " values ('GPIO17', 'GPIO6')") querylist.append( "insert into " + table + " values ('GPIO21', 'GPIO7')") querylist.append( "insert into " + table + " values ('GPIO22', 'GPIO8')") ### Interfaces Table if 'interfaces' in tabledict: runquery = True table = 'interfaces' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (interface text, type text, address text, id text primary key, name text unique, options text, enabled integer default 0, status integer default 0)") if addentries: querylist.append( "insert into " + table + " values ('SPI1','CuPIDlights','','SPIout1','myCuPIDlightboard','',1,0)") querylist.append( "insert into " + table + " values ('I2C','DS2483','','I2CDS2483','I2C 1Wire','tempunit:F',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','18','GPIO18','GPIO 1','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','23','GPIO23','GPIO 2','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','24','GPIO24','GPIO 3','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','25','GPIO25','GPIO 4','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','4','GPIO4','GPIO 5','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','17','GPIO17','GPIO6','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','21','GPIO21','GPIO 7','mode:output,pullupdown:pulldown',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','22','GPIO22','GPIO 8','mode:output,pullupdown:pulldown',1,0)") ### Controlalgorithms table if 'algorithms' in tabledict: runquery = True table = 'controlalgorithms' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (name text primary key, type text, maxposrate real default 0, maxnegrate real default 0, derivativemode text default time, derivativeperiod real default 0, integralmode text default time, integralperiod real default 0, proportional real default 1, integral real default 0, derivative real default 0, deadbandhigh real default 0, deadbandlow real default 0, dutypercent real default 0, dutyperiod real default 1)") if addentries: querylist.append( "insert into " + table + " values ('on/off 1', 'on/off with deadband',1,1,0,0,0,0,0,0,0,0,0,0,1)") ### Algorithmtypes if 'algorithmtypes' in tabledict: runquery = True table = 'algorithmtypes' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( name text )") if addentries: querylist.append("insert into " + table + " values ( 'on/off with deadband')") ### Channels table if 'channels' in tabledict: runquery = True table = 'channels' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (channelindex integer primary key, name text unique, controlinput text , enabled boolean default 0, outputsenabled boolean default 0, controlupdatetime text, controlalgorithm text default 'on/off 1', controlrecipe text default 'none', recipestage integer default 0, recipestarttime real default 0, recipestagestarttime real default 0, setpointvalue real, controlvalue real, controlvaluetime text, positiveoutput text, negativeoutput text, action real default 0, mode text default manual, statusmessage text, logpoints real default 100)") if addentries: querylist.append( "insert into " + table + " values (1, 'channel 1', 'none', 0, 0, '', 'on/off 1', 'none',0,0,0,65, '', '', 'output1', 'output2', 0, 'auto', '', 1000)") if runquery: print(querylist) sqlitemultquery(controldatabase, querylist)
def updateiodata(database): # This recreates all input and output tables based on the interfaces table. # Thus way we don't keep around stale data values. We could at some point incorporate # a retention feature that keeps them around in case they disappear temporarily. # It also reads the elements if they are enabled and it's time to read them import pilib import RPi.GPIO as GPIO allowedGPIOaddresses = [18, 23, 24, 25, 4, 17, 21, 22] dontrun = False GPIO.setmode(GPIO.BCM) GPIO.setwarnings(False) tables = pilib.gettablenames(pilib.controldatabase) if 'interfaces' in tables: interfaces = pilib.readalldbrows(pilib.controldatabase, 'interfaces') else: print('interfaces table not found. Exiting') return if 'inputs' in tables: previnputs = pilib.readalldbrows(pilib.controldatabase, 'inputs') # Make list of IDs for easy indexing previnputids = [] for input in previnputs: previnputids.append(input['id']) else: previnputs = [] previnputids = [] if 'outputs' in tables: prevoutputs = pilib.readalldbrows(pilib.controldatabase, 'outputs') # Make list of IDs for easy indexing prevoutputids = [] prevoutputvalues = [] for output in prevoutputs: prevoutputids.append(output['id']) prevoutputvalues.append(output['value']) else: prevoutputs = {} prevoutputids = [] if 'defaults' in tables: defaults = pilib.readalldbrows(pilib.controldatabase, 'defaults')[0] defaultinputpollfreq = defaults['inputpollfreq'] defaultoutputpollfreq = defaults['outputpollfreq'] else: defaults = [] defaultinputpollfreq = 60 defaultoutputpollfreq = 60 # We drop all inputs and outputs and recreate # Add all into one query so there is no time when the IO don't exist. querylist = [] querylist.append('delete from inputs') querylist.append('delete from outputs') pilib.sqlitemultquery(pilib.controldatabase, querylist) querylist = [] for interface in interfaces: if interface['interface'] == 'I2C': if interface['enabled']: # print('processing enabled I2C') if interface['type'] == 'DS2483': from owfslib import runowfsupdate runowfsupdate() elif interface['interface'] == 'GPIO': options = pilib.parseoptions(interface['options']) # TODO : respond to more option, like pullup and pulldown address = int(interface['address']) if address in allowedGPIOaddresses: # Check if interface is enabled if interface['enabled']: # Get name from ioinfo table to give it a colloquial name gpioname = pilib.sqlitedatumquery(database, 'select name from ioinfo where id=\'' + interface['id'] + '\'') polltime = pilib.gettimestring() # Append to inputs and update name, even if it's an output (can read status as input) if options['mode'] == 'output': GPIO.setup(address, GPIO.OUT) # Set the value of the gpio. # Get previous value if exists if interface['id'] in prevoutputids: value = prevoutputvalues[prevoutputids.index(interface['id'])] else: value = 0 if value == 1: GPIO.output(address, True) else: GPIO.output(address, False) # Get output settings and keep them if the GPIO previously existed if interface['id'] in prevoutputids: pollfreq = prevoutputs[prevoutputids.index(interface['id'])]['pollfreq'] else: pollfreq = defaultoutputpollfreq # Add entry to outputs tables querylist.append('insert into outputs values (\'' + interface['id'] + '\',\'' + interface['interface'] + '\',\'' + interface['type'] + '\',\'' + str(address) + '\',\'' + gpioname + '\',\'' + str(value) + '\',\'\',\'' + str(polltime) + '\',\'' + str(pollfreq) + '\')') else: GPIO.setup(address, GPIO.IN) value = GPIO.input(address) polltime = pilib.gettimestring() # Get input settings and keep them if the GPIO previously existed if interface['id'] in prevoutputids: pollfreq = previnputs[prevoutputids.index(interface['id'])]['pollfreq'] polltime = previnputs[prevoutputids.index(interface['id'])]['polltime'] else: pollfreq = defaultinputpollfreq # Add entry to inputs tables # Get output settings and keep them if the GPIO previously existed if interface['id'] in prevoutputids: pollfreq = prevoutputs[prevoutputids.index(interface['id'])]['pollfreq'] else: pollfreq = defaultoutputpollfreq querylist.append( 'insert into inputs values (\'' + interface['id'] + '\',\'' + interface['interface'] + '\',\'' + interface['type'] + '\',\'' + str(address) + '\',\'' + gpioname + '\',\'' + str(value) + '\',\'\',\'' + str(polltime) + '\',\'' + str(pollfreq) + '\')') else: GPIO.setup(address, GPIO.IN) else: print('GPIO address ' + address + 'not allowed. BAD THINGS CAN HAPPEN.') elif interface['interface'] == 'SPI': # print('processing SPI') if interface['type'] == 'SPITC': import readspi spidata = readspi.readspitc() readspi.recordspidata(database, spidata) elif interface['type'] == 'CuPIDLights': import spilights spilights.updatelightsfromdb(pilib.controldatabase, 'indicators') # Set tables # print(querylist) pilib.sqlitemultquery(pilib.controldatabase, querylist)
def rebuildusersdata(argument=None): from pilib import gethashedentry querylist = [] runquery = True querylist.append('drop table if exists users') enteringusers = True runquery = False index = 1 querylist.append( 'create table users (id integer primary key not null, name text not null, password text not null, email text not null, temp text not null, authlevel integer default 0)' ) if argument == 'defaults': runquery = True entries = [{ 'user': '******', 'password': '******', 'email': '*****@*****.**', 'authlevel': 1 }, { 'user': '******', 'password': '******', 'email': '*****@*****.**', 'authlevel': 4 }, { 'user': '******', 'password': '******', 'email': '*****@*****.**', 'authlevel': 3 }] index = 1 for entry in entries: hashedentry = gethashedentry(entry['user'], entry['password']) querylist.append("insert into users values(" + str(index) + ",'" + entry['user'] + "','" + hashedentry + "','" + entry['email'] + "',''," + str(entry['authlevel']) + ")") index += 1 else: while enteringusers: validentry = True input = raw_input("Enter username or Q to stop: ") passone = raw_input("Enter password: "******"Confirm password: "******"Enter user email") authlevelentry = raw_input("Enter authorization level (0-5)") if input == 'Q': enteringusers = False validentry = False print('exiting ...') if passone != passtwo: validentry = False print('passwords do not match') if not len(passone) >= 6: validentry = False print('passwords must be at least six characters') if not emailentry.find('@') > 0: validentry = False print('Email does not appear to be valid') if validentry: hashedentry = gethashedentry(passone) querylist.append("insert into users values(" + str(index) + ",'" + input + "','" + hashedentry + "','" + emailentry + "',''," + authlevelentry + ")") index += 1 runquery = True if runquery: print(querylist) sqlitemultquery(usersdatabase, querylist)
for output in outputs: if output['name'] in [channel['positiveoutput'], channel['negativeoutput']]: # set value querylist.append("update outputs set value = 0 where id='" + output['id'] + '\'') statusmsg += 'Output ' + output['name'] + ' disabled. ' # Set status message for channel print(statusmsg) querylist.append('update channels set statusmessage=\'' + statusmsg + '\'' + 'where channelindex=' + channelindex) # Set update time for channel querylist.append('update channels set controlupdatetime=\'' + time + '\'' + 'where channelindex=' + channelindex) # Execute query pilib.sqlitemultquery(pilib.controldatabase, querylist) # We do this system status again to refresh settings systemstatus = pilib.readalldbrows(pilib.controldatabase, 'systemstatus')[0] from processactions import processactions processactions() # Wait for delay time #print('sleeping') # spilights.updatelightsfromdb(pilib.controldatabase, 'indicators') pilib.writedatedlogmsg(pilib.systemstatuslog, 'Sleeping for .' + str(systemstatus['systemstatusfreq']), 2, pilib.systemstatusloglevel) sleep(systemstatus['systemstatusfreq']) pilib.writedatedlogmsg(pilib.systemstatuslog, 'picontrol not enabled. exiting.', 1, pilib.systemstatusloglevel)
def rebuildcontroldb(tabledict): # Create databases entries or leave them empty? addentries = True querylist = [] runquery = False ### Remotes table if 'remotes' in tabledict: runquery = True table = 'remotes' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (nodeid integer, msgtype text, keyvalue text, keyvaluename text, data text, time text)") ### Actions table if 'actions' in tabledict: runquery = True table = 'actions' querylist.append('drop table if exists ' + table) # actionindex, actiontype, querylist.append( # "create table " + table + " (actionindex integer primary key, valuerowid integer default 1, name text unique default 'myaction', enabled boolean default 0, actiontype text default 'email', actiondetail text default '*****@*****.**', conditiontype text default 'dbvalue',database text default 'controldata',tablename text default 'channels', variablename text default 'controlvalue', variablevalue text default '', operator text default 'equal',criterion text default '25',offdelay real default 0,ondelay real default 0,active boolean default 0, activereset boolean default 1, status boolean default 0,ontime text,offtime text,actionfrequency real default 60, lastactiontime text default '', statusmsg text default 'default msg')") "create table " + table + " (actionindex integer primary key, name text unique default 'myaction', enabled boolean default 0, actiontype text default 'email', actiondetail text default '*****@*****.**', conditiontype text default 'logical', actiondata text default '', value text default '', offdelay real default 0, ondelay real default 0, active boolean default 0, activereset boolean default 1, status boolean default 0,ontime text,offtime text,actionfrequency real default 60, lastactiontime text default '', statusmsg text default 'default msg')") # querylist.append("create table " + table + " (actionindex integer primary key, actiondata text default '')") ### SystemStatus table if 'systemstatus' in tabledict: runquery = True table = 'systemstatus' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (picontrolenabled boolean default 0, picontrolstatus boolean default 0, picontrolfreq real default 15 , lastpicontrolpoll text default '', updateioenabled boolean default 1, updateiostatus boolean default 0, updateiofreq real default 15, lastiopoll text default '', enableoutputs boolean default 0, sessioncontrolenabled boolean default 0, sessioncontrolstatus boolean default 0, systemstatusenabled boolean default 1, netstatusenabled boolean default 1, netconfigenabled default 0, checkhamachistatus boolean default 0, hamachistatus boolean default 0, systemstatusstatus boolean default 0, systemstatusfreq real default 15, lastsystemstatuspoll text default '', systemmessage text default '', serialhandlerenabled boolean default 0, serialhandlerstatus boolean default 0, webserver text default 'nginx')") if addentries: querylist.append("insert into " + table + " default values") ### logconfig Table if 'logconfig' in tabledict: runquery = True table = 'logconfig' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (networkloglevel integer, iologlevel integer, systemstatusloglevel integer, controlloglevel integer, daemonloglevel integer)") if addentries: querylist.append( "insert into " + table + " values (4,4,4,4,4)") ### Indicators table if 'indicators' in tabledict: runquery = True table = 'indicators' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( name text primary key, interface text, type text, status boolean default 0, detail text)") addentries = True if addentries: querylist.append("insert into " + table + " values ('SPI_RGB1_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB1_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB1_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append("insert into " + table + " values ('SPI_RGB2_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB2_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB2_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append("insert into " + table + " values ('SPI_RGB3_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB3_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB3_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append("insert into " + table + " values ('SPI_RGB4_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append("insert into " + table + " values ('SPI_RGB4_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append("insert into " + table + " values ('SPI_RGB4_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append("insert into " + table + " values ('SPI_SC_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append("insert into " + table + " values ('SPI_SC_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append("insert into " + table + " values ('SPI_SC_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append("insert into " + table + " values ('SPI_SC_Y', 'SPI1', 'CuPIDlights',0,'yellow')") ### Defaults table if 'defaults' in tabledict: runquery = True table = 'defaults' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( inputpollfreq real, outputpollfreq real)") addentries = True if addentries: querylist.append("insert into " + table + " values (60, 60)") ### Outputs table if 'outputs' in tabledict: runquery = True table = 'outputs' querylist.append('drop table if exists ' + table) querylist.append( 'create table ' + table + ' (id text primary key, interface text, type text, address text, name text, ' + 'value real, unit text, polltime text, pollfreq real, ontime text, offtime text)') ### Inputs table if 'inputs' in tabledict: runquery = True table = 'inputs' querylist.append('drop table if exists ' + table) querylist.append( 'create table ' + table + ' (id text primary key, interface text, type text, address text, name text, ' + 'value real, unit text, polltime text, pollfreq real, ontime text, offtime text)') ### OWFS Table if 'owfs' in tabledict: runquery = True table = 'owfs' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (address text primary key, family text, id text, type text, crc8 text)") ### Inputs Info Table if 'ioinfo' in tabledict: runquery = True table = 'ioinfo' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (id text primary key, name text, options text)") if addentries: querylist.append( "insert into " + table + " values ('GPIO18', 'GPIO18', '')") querylist.append( "insert into " + table + " values ('GPIO23', 'GPIO23', '')") querylist.append( "insert into " + table + " values ('GPIO24', 'GPIO24', '')") querylist.append( "insert into " + table + " values ('GPIO25', 'GPIO25(BootOk)', '')") querylist.append( "insert into " + table + " values ('GPIO4', 'GPIO4(MotePower)', '')") querylist.append( "insert into " + table + " values ('GPIO17', 'GPIO17', '')") querylist.append( "insert into " + table + " values ('GPIO27', 'GPIO27', '')") querylist.append( "insert into " + table + " values ('GPIO22', 'GPIO22', '')") querylist.append( "insert into " + table + " values ('GPIO5', 'GPIO5', '')") querylist.append( "insert into " + table + " values ('GPIO6', 'GPIO6', '')") querylist.append( "insert into " + table + " values ('GPIO13', 'GPIO13', '')") querylist.append( "insert into " + table + " values ('GPIO19', 'GPIO19', '')") querylist.append( "insert into " + table + " values ('GPIO26', 'GPIO26', '')") querylist.append( "insert into " + table + " values ('GPIO16', 'GPIO16', '')") querylist.append( "insert into " + table + " values ('GPIO20', 'GPIO20', '')") querylist.append( "insert into " + table + " values ('GPIO21', 'GPIO21(WiFi)', '')") ### Interfaces Table if 'interfaces' in tabledict: runquery = True table = 'interfaces' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (interface text, type text, address text, id text primary key, name text unique, options text, enabled integer default 0, status integer default 0)") if addentries: querylist.append( "insert into " + table + " values ('SPI1','CuPIDlights','','SPIout1','myCuPIDlightboard','',0,0)") querylist.append( "insert into " + table + " values ('SPI0','SPITC','','SPITC0','mySPITC','',0,0)") querylist.append( "insert into " + table + " values ('I2C','DS2483','','I2CDS2483','I2C 1Wire','tempunit:F',1,0)") querylist.append( "insert into " + table + " values ('LAN','MBTCP','192.168.1.18','MBTCP1','Modbus TCPIP','wordblocksize:24,bitblocksize:96',0,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','18','GPIO18','GPIO 18','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','23','GPIO23','GPIO 23','mode:input,pullupdown:pullu',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','24','GPIO24','GPIO 24','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','25','GPIO25','GPIO 25','mode:output',0,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','4','GPIO4','GPIO 4','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','17','GPIO17','GPIO 17','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','27','GPIO27','GPIO 27','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','22','GPIO22','GPIO 22','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','5','GPIO5','GPIO 5','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','6','GPIO6','GPIO 6','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','13','GPIO13','GPIO 13','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','19','GPIO19','GPIO 19','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','26','GPIO26','GPIO 26','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','16','GPIO16','GPIO 16','mode:input,pullupdown:pullup',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','20','GPIO20','GPIO 20','mode:input,pullupdown:pulldown,function:shutdown,functionstate:true',1,0)") querylist.append( "insert into " + table + " values ('GPIO','GPIO','21','GPIO21','GPIO 21','mode:input,pullupdown:pullup',1,0)") ### modbustcp Table if 'modbustcp' in tabledict: runquery = True table = 'modbustcp' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (interfaceid text, register integer, mode text default 'read', length integer default 1, bigendian boolean default 1, reversebyte boolean default 0, format text, options text)") if addentries: querylist.append("insert into " + table + " values ('MBTCP1', '400001', 'read', 2, 1, 0, 'float32','')") querylist.append("insert into " + table + " values ('MBTCP1', '400003', 'read', 2, 1, 0, 'float32','')") querylist.append("insert into " + table + " values ('MBTCP1', '400005', 'read', 2, 1, 0, 'float32','')") querylist.append("insert into " + table + " values ('MBTCP1', '400007', 'read', 2, 1, 0, 'float32','')") ### Controlalgorithms table if 'algorithms' in tabledict: runquery = True table = 'controlalgorithms' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (name text primary key, type text, maxposrate real default 0, maxnegrate real default 0, derivativemode text default time, derivativeperiod real default 0, integralmode text default time, integralperiod real default 0, proportional real default 1, integral real default 0, derivative real default 0, deadbandhigh real default 0, deadbandlow real default 0, dutypercent real default 0, dutyperiod real default 1, minontime real, minofftime real)") if addentries: querylist.append( "insert into " + table + " values ('on/off 1', 'on/off with deadband',1,1,0,0,0,0,0,0,0,0,0,0,1,0,0)") ### Algorithmtypes if 'algorithmtypes' in tabledict: runquery = True table = 'algorithmtypes' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( name text )") if addentries: querylist.append("insert into " + table + " values ( 'on/off with deadband')") ### Channels table if 'channels' in tabledict: runquery = True table = 'channels' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (channelindex integer primary key, type text default local, name text unique, controlinput text default 'none', enabled boolean default 0, outputsenabled boolean default 0, controlupdatetime text, controlalgorithm text default 'on/off 1', controlrecipe text default 'none', recipestage integer default 0, recipestarttime real default 0, recipestagestarttime real default 0, setpointvalue real, controlvalue real, controlvaluetime text, positiveoutput text default none, negativeoutput text default none, action real default 0, mode text default manual, statusmessage text, logpoints real default 100, data text, dataclasses text default '', pending text default '')") if addentries: # querylist.append("insert into " + table + " values (1, 'local', 'channel 1', '', 'none', 0, 0, '', 'on/off 1', 'none',0,0,0,65, '', '', 'none', 'none', 0, 'auto', '', 1000,'', '', '')") querylist.append("insert into " + table + " values (1, 'remote', 'Kettle', 'none', 0, 0, '', 'on/off 1', 'none',1,0,0,65, '', '', 'none', 'none', 0, 'auto', '', 1000,'', '', '')") querylist.append("insert into " + table + " values (2, 'remote', 'MLT', 'none', 0, 0, '', 'on/off 1', 'none',1,0,0,65, '', '', 'none', 'none', 0, 'auto', '', 1000,'', '', '')") querylist.append("insert into " + table + " values (3, 'remote', 'HLT', 'none', 0, 0, '', 'on/off 1', 'none',1,0,0,65, '', '', 'none', 'none', 0, 'auto', '', 1000,'', '', '')") if runquery: print(querylist) sqlitemultquery(controldatabase, querylist)
def rebuildwirelessdata(): querylist = [] querylist.append('drop table if exists wireless') querylist.append('create table wireless (SSID text, password text)') sqlitemultquery(safedatabase, querylist)
def rebuildcontroldb(tabledict): # Create databases entries or leave them empty? addentries = True querylist = [] runquery = False ### Remotes table if 'remotes' in tabledict: runquery = True table = 'remotes' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (nodeid integer, msgtype text, keyvalue text, keyvaluename text, data text, time text)" ) ### Actions table if 'actions' in tabledict: runquery = True table = 'actions' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (channelindex integer primary key, name text unique default 'myaction', enabled boolean default 0, actiontype text default 'email', actiondetail text default '*****@*****.**', conditiontype text default 'dbvalue',database text default 'controldata',tablename text default 'channels', variablename text default 'controlvalue', variablevalue text default '', operator text default '=',criterion text default '25',offdelay real default 0,ondelay real default 0,active boolean default 0, activereset boolean default 1, status boolean default 0,ontime text,offtime text,actionfrequency real default 60, lastactiontime text default '', statusmsg text default 'default msg')" ) if addentries: querylist.append("insert into " + table + " default values") ### SystemStatus table if 'systemstatus' in tabledict: runquery = True table = 'systemstatus' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (picontrolenabled boolean default 0, picontrolstatus boolean default 0, picontrolfreq real default 15 , lastpicontrolpoll text, updateioenabled boolean default 1, updateiostatus boolean default 0, updateiofreq real default 5, lastiopoll text, enableoutputs boolean default 0, sessioncontrolenabled boolean, sessioncontrolstatus boolean, systemstatusenabled boolean default 0, netconfigenabled boolean default 0, systemstatusstatus boolean, systemstatusfreq real default 15, lastsystemstatuspoll text, systemmessage text, serialhandlerenabled boolean default 0, serialhandlerstatus boolean default 0)" ) if addentries: querylist.append( "insert into " + table + " values (0,0,15,'',1,0,15,'',0,1,0,1,0,0,15,'','',0,0)") ### logconfig Table if 'logconfig' in tabledict: runquery = True table = 'logconfig' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (networkloglevel integer, iologlevel integer, systemstatusloglevel integer, controlloglevel integer, daemonloglevel integer)" ) if addentries: querylist.append("insert into " + table + " values (4,4,4,4,4)") ### Indicators table if 'indicators' in tabledict: runquery = True table = 'indicators' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " ( name text primary key, interface text, type text, status boolean default 0, detail text)" ) addentries = True if addentries: querylist.append( "insert into " + table + " values ('SPI_RGB1_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append( "insert into " + table + " values ('SPI_RGB1_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append( "insert into " + table + " values ('SPI_RGB1_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append( "insert into " + table + " values ('SPI_RGB2_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append( "insert into " + table + " values ('SPI_RGB2_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append( "insert into " + table + " values ('SPI_RGB2_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append( "insert into " + table + " values ('SPI_RGB3_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append( "insert into " + table + " values ('SPI_RGB3_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append( "insert into " + table + " values ('SPI_RGB3_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append( "insert into " + table + " values ('SPI_RGB4_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append( "insert into " + table + " values ('SPI_RGB4_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append( "insert into " + table + " values ('SPI_RGB4_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append( "insert into " + table + " values ('SPI_SC_R', 'SPI1', 'CuPIDlights',0,'red')") querylist.append( "insert into " + table + " values ('SPI_SC_G', 'SPI1', 'CuPIDlights',0,'green')") querylist.append( "insert into " + table + " values ('SPI_SC_B', 'SPI1', 'CuPIDlights',0,'blue')") querylist.append( "insert into " + table + " values ('SPI_SC_Y', 'SPI1', 'CuPIDlights',0,'yellow')") ### Defaults table if 'defaults' in tabledict: runquery = True table = 'defaults' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( inputpollfreq real, outputpollfreq real)") addentries = True if addentries: querylist.append("insert into " + table + " values (60, 60)") ### Outputs table if 'outputs' in tabledict: runquery = True table = 'outputs' querylist.append('drop table if exists ' + table) querylist.append( 'create table ' + table + ' (id text primary key, interface text, type text, address text, name text, ' + 'value real, unit text, polltime text, pollfreq real, ontime text, offtime text)' ) ### Inputs table if 'inputs' in tabledict: runquery = True table = 'inputs' querylist.append('drop table if exists ' + table) querylist.append( 'create table ' + table + ' (id text primary key, interface text, type text, address text, name text, ' + 'value real, unit text, polltime text, pollfreq real, ontime text, offtime text)' ) ### OWFS Table if 'owfs' in tabledict: runquery = True table = 'owfs' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (address text primary key, family text, id text, type text, crc8 text)" ) ### Inputs Info Table if 'ioinfo' in tabledict: runquery = True table = 'ioinfo' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " (id text primary key, name text, options text)") if addentries: querylist.append("insert into " + table + " values ('GPIO18', 'GPIO1', '')") querylist.append("insert into " + table + " values ('GPIO23', 'GPIO2', '')") querylist.append("insert into " + table + " values ('GPIO24', 'GPIO3', '')") querylist.append("insert into " + table + " values ('GPIO25', 'GPIO4', '')") querylist.append("insert into " + table + " values ('GPIO4', 'GPIO5', '')") querylist.append("insert into " + table + " values ('GPIO17', 'GPIO6', '')") querylist.append("insert into " + table + " values ('GPIO21', 'GPIO7', '')") querylist.append("insert into " + table + " values ('GPIO22', 'GPIO8', '')") ### Interfaces Table if 'interfaces' in tabledict: runquery = True table = 'interfaces' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (interface text, type text, address text, id text primary key, name text unique, options text, enabled integer default 0, status integer default 0)" ) if addentries: querylist.append( "insert into " + table + " values ('SPI1','CuPIDlights','','SPIout1','myCuPIDlightboard','',1,0)" ) querylist.append( "insert into " + table + " values ('I2C','DS2483','192.168.1.18','I2CDS2483','I2C 1Wire','tempunit:F',1,0)" ) querylist.append( "insert into " + table + " values ('LAN','MBTCP','','MBTCP1','Modbus TCPIP','wordblocksize:24,bitblocksize:96',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','18','GPIO18','GPIO 1','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','23','GPIO23','GPIO 2','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','24','GPIO24','GPIO 3','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','25','GPIO25','GPIO 4','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','4','GPIO4','GPIO 5','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','17','GPIO17','GPIO6','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','21','GPIO21','GPIO 7','mode:output,pullupdown:pulldown',1,0)" ) querylist.append( "insert into " + table + " values ('GPIO','GPIO','22','GPIO22','GPIO 8','mode:output,pullupdown:pulldown',1,0)" ) ### modbustcp Table if 'modbustcp' in tabledict: runquery = True table = 'modbustcp' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (interfaceid text, register integer, mode text default 'read', length integer default 1, bigendian boolean default 1, reversebyte boolean default 0, format text, options text)" ) if addentries: querylist.append( "insert into " + table + " values ('MBTCP1', '400001', 'read', 2, 1, 0, 'float32','')") querylist.append( "insert into " + table + " values ('MBTCP1', '400003', 'read', 2, 1, 0, 'float32','')") querylist.append( "insert into " + table + " values ('MBTCP1', '400005', 'read', 2, 1, 0, 'float32','')") querylist.append( "insert into " + table + " values ('MBTCP1', '400007', 'read', 2, 1, 0, 'float32','')") ### Controlalgorithms table if 'algorithms' in tabledict: runquery = True table = 'controlalgorithms' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (name text primary key, type text, maxposrate real default 0, maxnegrate real default 0, derivativemode text default time, derivativeperiod real default 0, integralmode text default time, integralperiod real default 0, proportional real default 1, integral real default 0, derivative real default 0, deadbandhigh real default 0, deadbandlow real default 0, dutypercent real default 0, dutyperiod real default 1, minontime real, minofftime real)" ) if addentries: querylist.append( "insert into " + table + " values ('on/off 1', 'on/off with deadband',1,1,0,0,0,0,0,0,0,0,0,0,1,0,0)" ) ### Algorithmtypes if 'algorithmtypes' in tabledict: runquery = True table = 'algorithmtypes' querylist.append('drop table if exists ' + table) querylist.append("create table " + table + " ( name text )") if addentries: querylist.append("insert into " + table + " values ( 'on/off with deadband')") ### Channels table if 'channels' in tabledict: runquery = True table = 'channels' querylist.append('drop table if exists ' + table) querylist.append( "create table " + table + " (channelindex integer primary key, name text unique, controlinput text , enabled boolean default 0, outputsenabled boolean default 0, controlupdatetime text, controlalgorithm text default 'on/off 1', controlrecipe text default 'none', recipestage integer default 0, recipestarttime real default 0, recipestagestarttime real default 0, setpointvalue real, controlvalue real, controlvaluetime text, positiveoutput text, negativeoutput text, action real default 0, mode text default manual, statusmessage text, logpoints real default 100)" ) if addentries: querylist.append( "insert into " + table + " values (1, 'channel 1', 'none', 0, 0, '', 'on/off 1', 'none',0,0,0,65, '', '', 'output1', 'output2', 0, 'auto', '', 1000)" ) if runquery: print(querylist) sqlitemultquery(controldatabase, querylist)
def updateiodata(database): # This recreates all input and output tables based on the interfaces table. # Thus way we don't keep around stale data values. We could at some point incorporate # a retention feature that keeps them around in case they disappear temporarily. # It also reads the elements if they are enabled and it's time to read them import pilib import traceback import RPi.GPIO as GPIO allowedGPIOaddresses = [18, 23, 24, 25, 4, 17, 21, 22] logconfig = pilib.getlogconfig() GPIO.setmode(GPIO.BCM) GPIO.setwarnings(False) tables = pilib.gettablenames(pilib.controldatabase) if 'interfaces' in tables: interfaces = pilib.readalldbrows(pilib.controldatabase, 'interfaces') else: pilib.writedatedlogmsg(pilib.iolog, 'interfaces table not found. Exiting', 1, logconfig['iologlevel']) return if 'inputs' in tables: previnputs = pilib.readalldbrows(pilib.controldatabase, 'inputs') # Make list of IDs for easy indexing previnputids = [] for input in previnputs: previnputids.append(input['id']) else: previnputs = [] previnputids = [] if 'outputs' in tables: prevoutputs = pilib.readalldbrows(pilib.controldatabase, 'outputs') # Make list of IDs for easy indexing prevoutputids = [] prevoutputvalues = [] for output in prevoutputs: prevoutputids.append(output['id']) prevoutputvalues.append(output['value']) else: prevoutputs = {} prevoutputids = [] if 'defaults' in tables: defaults = pilib.readalldbrows(pilib.controldatabase, 'defaults')[0] defaultinputpollfreq = defaults['inputpollfreq'] defaultoutputpollfreq = defaults['outputpollfreq'] else: defaults = [] defaultinputpollfreq = 60 defaultoutputpollfreq = 60 if 'indicators' in tables: indicatornames = [] previndicators = pilib.readalldbrows(pilib.controldatabase, 'indicators') for indicator in previndicators: indicatornames.append(indicator['name']) else: previndicators = [] indicatornames = [] # We drop all inputs and outputs and recreate # Add all into one query so there is no time when the IO don't exist. querylist = [] querylist.append('delete from inputs') querylist.append('delete from outputs') # This is temporary. Clearing the table here and adding entries below can result in a gap in time # where there are no database indicator entries. This is not too much of a problem with indicators, as we # update the hardware explicitly after we add the entries. If the interface queries the table during # this period, however, we could end up with an apparently empty table. # TODO: FIX update on indicators in updateio pilib.sqlitequery(pilib.controldatabase, 'delete from indicators') owfsupdate = False for interface in interfaces: if interface['interface'] == 'I2C': pilib.writedatedlogmsg(pilib.iolog, 'Processing I2C interface' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.writedatedlogmsg(pilib.iolog, 'I2C Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) if interface['type'] == 'DS2483': pilib.writedatedlogmsg(pilib.iolog, 'Interface type is DS2483', 3, logconfig['iologlevel']) owfsupdate = True elif interface['interface'] == 'USB': pilib.writedatedlogmsg(pilib.iolog, 'Processing USB interface' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.writedatedlogmsg(pilib.iolog, 'USB Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) if interface['type'] == 'DS9490': pilib.writedatedlogmsg(pilib.iolog, 'Interface type is DS9490', 3, logconfig['iologlevel']) owfsupdate = True elif interface['interface'] == 'MOTE': pilib.writedatedlogmsg(pilib.iolog, 'Processing Mote interface' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.writedatedlogmsg(pilib.iolog, 'Mote Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) # Grab mote entries from remotes table nodeaddress = int(interface['address']) nodeentries = pilib.dynamicsqliteread(pilib.controldatabase, 'remotes', condition="\"nodeid\"='" + str(nodeaddress) + "'") # Create queries for table insertion # TODO: process mote pollfreq, ontime, offtime moteentries = [] for nodeentry in nodeentries: datadict = pilib.parseoptions(nodeentry['data']) try: entrytype = nodeentry['msgtype'] entryid = 'MOTE' + str(nodeentry['nodeid']) + '_' + nodeentry['keyvaluename'] + '_' + nodeentry['keyvalue'] entrymetareturn = pilib.dynamicsqliteread(pilib.controldatabase, 'ioinfo', condition="\"id\"='" + entryid + "'") try: entrymeta = entrymetareturn[0] except: entrymeta = [] # print(entrymeta) entryoptions={} if entrymeta: entryname = entrymeta['name'] if entrymeta['options']: entryoptions = pilib.parseoptions(entrymeta['options']) else: entryname = '[MOTE' + str(nodeentry['nodeid']) + '] ' + nodeentry['keyvaluename'] + ':' + nodeentry['keyvalue'] except KeyError: print('OOPS KEY ERROR') else: if entrytype == 'iovalue': if 'scale' in entryoptions: entryvalue = str(float(entryoptions['scale']) * float(datadict['iovalue'])) else: entryvalue = datadict['iovalue'] elif entrytype == 'owdev': if 'owtmpasc' in datadict: if 'scale' in entryoptions: entryvalue = str(float(entryoptions['scale']) * float(datadict['owtmpasc'])) else: entryvalue = datadict['owtmpasc'] else: entryvalue = -1 else: entryvalue = -1 moteentries.append('insert into inputs values (\'' + entryid + '\',\'' + interface['interface'] + '\',\'' + interface['type'] + '\',\'' + str(address) + '\',\'' + entryname + '\',\'' + str(entryvalue) + "','','" + nodeentry['time'] + '\',\'' + str(15) + "','" + '' + "','" + '' + "')") # print('querylist') # print(moteentries) querylist.extend(moteentries) elif interface['interface'] == 'LAN': pilib.writedatedlogmsg(pilib.iolog, 'Processing LAN interface' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.writedatedlogmsg(pilib.iolog, 'LAN Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) if interface['type'] == 'MBTCP': pilib.writedatedlogmsg(pilib.iolog, 'Interface ' + interface['name'] + ' type is MBTCP', 3, logconfig['iologlevel']) try: mbentries = processMBinterface(interface, prevoutputs, prevoutputids, previnputs, previnputids, defaults, logconfig) except: pilib.writedatedlogmsg(pilib.iolog, 'Error processing MBTCP interface ' + interface['name'], 0, logconfig['iologlevel']) errorstring = traceback.format_exc() pilib.writedatedlogmsg(pilib.iolog, 'Error of kind: ' + errorstring, 0, logconfig['iologlevel']) else: pilib.writedatedlogmsg(pilib.iolog, 'Done processing MBTCP interface ' + interface['name'], 3, logconfig['iologlevel']) querylist.extend(mbentries) elif interface['interface'] == 'GPIO': try: address = int(interface['address']) except KeyError: pilib.writedatedlogmsg(pilib.iolog, 'GPIO address key not found for ' + interface['name'], 1, logconfig['iologlevel']) continue pilib.writedatedlogmsg(pilib.iolog, 'Processing GPIO interface ' + str(interface['address']), 3, logconfig['iologlevel']) if address in allowedGPIOaddresses: pilib.writedatedlogmsg(pilib.iolog, 'GPIO address' + str(address) + ' allowed', 4, logconfig['iologlevel']) # Check if interface is enabled if interface['enabled']: GPIOentries = processGPIOinterface(interface, prevoutputs, prevoutputvalues, prevoutputids, previnputs, previnputids, defaults, logconfig) querylist.extend(GPIOentries) else: pilib.writedatedlogmsg(pilib.iolog, 'GPIO address' + str(address) + ' disabled', 4, logconfig['iologlevel']) GPIO.setup(address, GPIO.IN) else: pilib.writedatedlogmsg(pilib.iolog, 'GPIO address' + str(address) + ' not allowed. Bad things can happen. ', 4, logconfig['iologlevel']) elif interface['interface'] == 'SPI0': pilib.writedatedlogmsg(pilib.iolog, 'Processing SPI0', 1, logconfig['iologlevel']) if interface['type'] == 'SPITC': import readspi spidata = readspi.readspitc(0) spitcentries = readspi.recordspidata(database, spidata) querylist.extend(spitcentries) if interface['type'] == 'CuPIDlights': import spilights spilightsentries, setlist = spilights.getCuPIDlightsentries('indicators', 0, previndicators) querylist.extend(spilightsentries) spilights.updatelightsfromdb(pilib.controldatabase, 'indicators', 0) spilights.setspilights(setlist, 0) elif interface['interface'] == 'SPI1': pilib.writedatedlogmsg(pilib.iolog, 'Processing SPI1', 1, logconfig['iologlevel']) if interface['type'] == 'CuPIDlights': import spilights spilightsentries, setlist = spilights.getCuPIDlightsentries('indicators', 1, previndicators) querylist.extend(spilightsentries) spilights.setspilights(setlist, 1) # Set tables querylist.append(pilib.makesinglevaluequery('systemstatus', 'lastiopoll', pilib.gettimestring())) if owfsupdate: from owfslib import runowfsupdate devices, owfsentries = runowfsupdate(execute=False) querylist.extend(owfsentries) pilib.writedatedlogmsg(pilib.iolog, 'Executing query: ' + str(querylist), 5, logconfig['iologlevel']) try: pilib.sqlitemultquery(pilib.controldatabase, querylist) except: errorstring = traceback.format_exc() pilib.writedatedlogmsg(pilib.iolog, 'Error executing query, message: ' + errorstring, 0, logconfig['iologlevel']) pilib.writedatedlogmsg(pilib.errorlog, 'Error executing updateio query, message: ' + errorstring) pilib.writedatedlogmsg(pilib.errorlog, 'Query: ' + str(querylist))
def updateiodata(database, **kwargs): # This recreates all input and output tables based on the interfaces table. # Thus way we don't keep around stale data values. We could at some point incorporate # a retention feature that keeps them around in case they disappear temporarily. # It also reads the elements if they are enabled and it's time to read them import pilib import traceback if 'piobject' in kwargs: pi = kwargs['piobject'] else: import pigpio pi = pigpio.pi() allowedGPIOaddresses = [18, 23, 24, 25, 4, 17, 27, 22, 5, 6, 13, 19, 26, 16, 20, 21] logconfig = pilib.getlogconfig() tables = pilib.gettablenames(pilib.controldatabase) if 'interfaces' in tables: interfaces = pilib.readalldbrows(pilib.controldatabase, 'interfaces') else: pilib.log(pilib.iolog, 'interfaces table not found. Exiting', 1, logconfig['iologlevel']) return if 'inputs' in tables: previnputs = pilib.readalldbrows(pilib.controldatabase, 'inputs') # Make list of IDs for easy indexing previnputids = [] for input in previnputs: previnputids.append(input['id']) else: previnputs = [] previnputids = [] if 'outputs' in tables: prevoutputs = pilib.readalldbrows(pilib.controldatabase, 'outputs') # Make list of IDs for easy indexing prevoutputids = [] prevoutputvalues = [] for output in prevoutputs: prevoutputids.append(output['id']) prevoutputvalues.append(output['value']) else: prevoutputs = {} prevoutputids = [] if 'defaults' in tables: defaults = pilib.readalldbrows(pilib.controldatabase, 'defaults')[0] defaultinputpollfreq = defaults['inputpollfreq'] defaultoutputpollfreq = defaults['outputpollfreq'] else: defaults = [] defaultinputpollfreq = 60 defaultoutputpollfreq = 60 if 'indicators' in tables: indicatornames = [] previndicators = pilib.readalldbrows(pilib.controldatabase, 'indicators') for indicator in previndicators: indicatornames.append(indicator['name']) else: previndicators = [] indicatornames = [] # We drop all inputs and outputs and recreate # Add all into one query so there is no time when the IO don't exist. querylist = [] querylist.append('delete from inputs') querylist.append('delete from outputs') # This is temporary. Clearing the table here and adding entries below can result in a gap in time # where there are no database indicator entries. This is not too much of a problem with indicators, as we # update the hardware explicitly after we add the entries. If the interface queries the table during # this period, however, we could end up with an apparently empty table. # TODO: FIX update on indicators in updateio # We drop this table, so that if SP1 has been disabled, the entries do not appear as valid indicators pilib.sqlitequery(pilib.controldatabase, 'delete from indicators') owfsupdate = False for interface in interfaces: if interface['interface'] == 'I2C': pilib.log(pilib.iolog, 'Processing I2C interface ' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.log(pilib.iolog, 'I2C Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) if interface['type'] == 'DS2483': pilib.log(pilib.iolog, 'Interface type is DS2483', 3, logconfig['iologlevel']) owfsupdate = True else: pilib.log(pilib.iolog, 'I2C Interface ' + interface['name'] + ' disabled', 3, logconfig['iologlevel']) elif interface['interface'] == 'USB': pilib.log(pilib.iolog, 'Processing USB interface ' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.log(pilib.iolog, 'USB Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) if interface['type'] == 'DS9490': pilib.log(pilib.iolog, 'Interface type is DS9490', 3, logconfig['iologlevel']) owfsupdate = True else: pilib.log(pilib.iolog, 'USB Interface ' + interface['name'] + ' disabled', 3, logconfig['iologlevel']) elif interface['interface'] == 'MOTE': #determine and then update id based on fields entryid = interface['interface'] + '_' + interface['type'] + '_' + interface['address'] condition = '"interface"=\'' + interface['interface'] + '\' and "type"=\'' + interface['type'] + '\' and "address"=\'' + interface['address'] + '\'' print(condition) pilib.setsinglevalue(pilib.controldatabase, 'interfaces', 'id', entryid, condition) pilib.log(pilib.iolog, 'Processing Mote interface' + interface['name'] + ', id:' + entryid, 3, logconfig['iologlevel']) if interface['enabled']: pilib.log(pilib.iolog, 'Mote Interface ' + interface['name'] + ', id:' + entryid + ' enabled', 3, logconfig['iologlevel']) # Grab mote entries from remotes table # nodeid and keyvalue are keyed into address in remotes table # keyvalues are : # channel: channel number # iovalue: ionumber # owdev: ROM # This would look like, for example # 1:1 for a nodeid:channel scenario for a controller split = interface['address'].split(':') nodeid = split[0] keyvalue = split[1] # so we used to enable an interface and then take all entries from a node # Now, we have to explicitly add an interface for each device, unless the keychar * is used as the # keyvalue. This will allow us to insert all automatically, for example for owdevs or iovals from a # node. # This pulls out all mote entries that have nodeid and keyvalue that match the interface address # We should just find one, ideally if keyvalue == '*': pass else: condition = "\"nodeid\"='" + nodeid + "' and \"keyvalue\"='" + keyvalue + "'" nodeentries = pilib.dynamicsqliteread(pilib.controldatabase, 'remotes', condition=condition) print("WE FOUND MOTE") print(condition) print(len(nodeentries)) if interface['type'] == 'channel': print('channel') if len(nodeentries) == 1: print('one entry found') nodeentry = nodeentries[0] nodedata = pilib.parseoptions(nodeentry['data']) # Find existing channel so we can get existing data, settings, etc., and retain channel ordering newchanneldata = {'name':interface['name'], 'controlvalue':nodedata['pv'], 'setpointvalue':nodedata['sv'],'controlvaluetime':pilib.gettimestring(), 'data':nodeentry['data'], 'type':'remote'} newchannel = {} existingchannels = pilib.readalldbrows(pilib.controldatabase, 'channels') for channel in existingchannels: if channel['name'] == interface['name']: print('updating') print(channel) newchannel.update(channel) print(newchannel) newchannel.update(newchanneldata) print(newchannel) keys = [] values = [] for key, value in newchannel.iteritems(): keys.append(key) values.append(value) query = pilib.makesqliteinsert('channels',values, keys) # print(query) pilib.sqlitequery(pilib.controldatabase,query) else: print('multiple entries found for channel. not appropriate') else: pass # Create queries for table insertion # TODO: process mote pollfreq, ontime, offtime moteentries = [] for nodeentry in nodeentries: # THis breaks out all of the strictly json-encoded data. datadict = pilib.parseoptions(nodeentry['data']) try: entrytype = nodeentry['msgtype'] # now treat each mote type entry specially # if entrytype == 'channel': entryid = 'MOTE' + str(nodeentry['nodeid']) + '_' + nodeentry['keyvaluename'] + '_' + nodeentry['keyvalue'] entrymetareturn = pilib.dynamicsqliteread(pilib.controldatabase, 'ioinfo', condition="\"id\"='" + entryid + "'") try: entrymeta = entrymetareturn[0] except: entrymeta = [] # print(entrymeta) entryoptions={} if entrymeta: entryname = entrymeta['name'] if entrymeta['options']: entryoptions = pilib.parseoptions(entrymeta['options']) else: entryname = '[MOTE' + str(nodeentry['nodeid']) + '] ' + nodeentry['keyvaluename'] + ':' + nodeentry['keyvalue'] except KeyError: print('OOPS KEY ERROR') else: if entrytype == 'iovalue': if 'scale' in entryoptions: entryvalue = str(float(entryoptions['scale']) * float(datadict['ioval'])) elif 'formula' in entryoptions: x = float(datadict['ioval']) try: entryvalue = eval(entryoptions['formula']) except: entryvalue = float(datadict['ioval']) else: entryvalue = float(datadict['ioval']) elif entrytype == 'owdev': if 'owtmpasc' in datadict: if 'scale' in entryoptions: entryvalue = str(float(entryoptions['scale']) * float(datadict['owtmpasc'])) elif 'formula' in entryoptions: x = float(datadict['owtmpasc']) try: entryvalue = eval(entryoptions['formula']) except: entryvalue = float(datadict['owtmpasc']) else: entryvalue = datadict['owtmpasc'] else: entryvalue = -1 else: entryvalue = -1 moteentries.append('insert into inputs values (\'' + entryid + '\',\'' + interface['interface'] + '\',\'' + interface['type'] + '\',\'' + str(address) + '\',\'' + entryname + '\',\'' + str(entryvalue) + "','','" + nodeentry['time'] + '\',\'' + str(15) + "','" + '' + "','" + '' + "')") # print('querylist') # print(moteentries) querylist.extend(moteentries) else: pilib.log(pilib.iolog, 'Mote Interface ' + interface['name'] + ' disnabled', 3, logconfig['iologlevel']) elif interface['interface'] == 'LAN': pilib.log(pilib.iolog, 'Processing LAN interface' + interface['name'], 3, logconfig['iologlevel']) if interface['enabled']: pilib.log(pilib.iolog, 'LAN Interface ' + interface['name'] + ' enabled', 3, logconfig['iologlevel']) if interface['type'] == 'MBTCP': pilib.log(pilib.iolog, 'Interface ' + interface['name'] + ' type is MBTCP', 3, logconfig['iologlevel']) try: mbentries = processMBinterface(interface, prevoutputs, prevoutputids, previnputs, previnputids, defaults, logconfig) except: pilib.log(pilib.iolog, 'Error processing MBTCP interface ' + interface['name'], 0, logconfig['iologlevel']) errorstring = traceback.format_exc() pilib.log(pilib.iolog, 'Error of kind: ' + errorstring, 0, logconfig['iologlevel']) else: pilib.log(pilib.iolog, 'Done processing MBTCP interface ' + interface['name'], 3, logconfig['iologlevel']) querylist.extend(mbentries) else: pilib.log(pilib.iolog, 'LAN Interface ' + interface['name'] + ' disabled', 3, logconfig['iologlevel']) elif interface['interface'] == 'GPIO': try: address = int(interface['address']) except KeyError: pilib.log(pilib.iolog, 'GPIO address key not found for ' + interface['name'], 1, logconfig['iologlevel']) continue if interface['enabled']: pilib.log(pilib.iolog, 'Processing GPIO interface ' + str(interface['address']), 3, logconfig['iologlevel']) if address in allowedGPIOaddresses: pilib.log(pilib.iolog, 'GPIO address' + str(address) + ' allowed. Processing.', 4, logconfig['iologlevel']) GPIOentries = processGPIOinterface(interface, prevoutputs, prevoutputvalues, prevoutputids, previnputs, previnputids, defaults, logconfig, piobject=pi) if GPIOentries: querylist.extend(GPIOentries) else: pilib.log(pilib.iolog, 'GPIO address' + str(address) + ' not allowed. Bad things can happen. ', 4, logconfig['iologlevel']) else: pilib.log(pilib.iolog, 'GPIO address' + str(address) + ' disabled. Doing nothing.', 4, logconfig['iologlevel']) elif interface['interface'] == 'SPI0': pilib.log(pilib.iolog, 'Processing SPI0', 1, logconfig['iologlevel']) if interface['enabled']: pilib.log(pilib.iolog, 'SPI0 enabled', 1, logconfig['iologlevel']) if interface['type'] == 'SPITC': pilib.log(pilib.iolog, 'Processing SPITC on SPI0', 3, logconfig['iologlevel']) import readspi tcdict = readspi.getpigpioMAX31855temp(0,0) # Convert to F for now spitcentries = readspi.recordspidata(database, {'SPITC1' :tcdict['tctemp']*1.8+32}) querylist.extend(spitcentries) if interface['type'] == 'CuPIDlights': import spilights spilightsentries, setlist = spilights.getCuPIDlightsentries('indicators', 0, previndicators) querylist.extend(spilightsentries) spilights.updatelightsfromdb(pilib.controldatabase, 'indicators', 0) spilights.setspilights(setlist, 0) else: pilib.log(pilib.iolog, 'SPI0 not enabled', 1, logconfig['iologlevel']) elif interface['interface'] == 'SPI1': pilib.log(pilib.iolog, 'Processing SPI1', 1, logconfig['iologlevel']) if interface['enabled']: pilib.log(pilib.iolog, 'SPI1 enabled', 1, logconfig['iologlevel']) if interface['type'] == 'CuPIDlights': pilib.log(pilib.iolog, 'Processing CuPID Lights on SPI1', 1, logconfig['iologlevel']) import spilights spilightsentries, setlist = spilights.getCuPIDlightsentries('indicators', 1, previndicators) querylist.extend(spilightsentries) spilights.setspilights(setlist, 1) else: pilib.log(pilib.iolog, 'SPI1 disaabled', 1, logconfig['iologlevel']) # Set tables querylist.append(pilib.makesinglevaluequery('systemstatus', 'lastiopoll', pilib.gettimestring())) if owfsupdate: from owfslib import runowfsupdate pilib.log(pilib.iolog, 'Running owfsupdate', 1, logconfig['iologlevel']) devices, owfsentries = runowfsupdate(execute=False) querylist.extend(owfsentries) else: pilib.log(pilib.iolog, 'owfsupdate disabled', 3, logconfig['iologlevel']) pilib.log(pilib.iolog, 'Executing query: ' + str(querylist), 5, logconfig['iologlevel']) try: # print(querylist) pilib.sqlitemultquery(pilib.controldatabase, querylist) except: errorstring = traceback.format_exc() pilib.log(pilib.iolog, 'Error executing query, message: ' + errorstring, 0, logconfig['iologlevel']) pilib.log(pilib.errorlog, 'Error executing updateio query, message: ' + errorstring) pilib.log(pilib.errorlog, 'Query: ' + str(querylist))
def runpicontrol(runonce=False): systemstatus = pilib.readalldbrows(pilib.controldatabase, 'systemstatus')[0] while systemstatus['picontrolenabled']: pilib.log(pilib.syslog, 'Running picontrol', 3, pilib.sysloglevel) pilib.log(pilib.controllog, 'Running picontrol', 3, pilib.controlloglevel) # Set poll date. While intuitively we might want to set this # after the poll is complete, if we error below, we will know # from this stamp when it barfed. This is arguably more valuable # then 'last time we didn't barf' pilib.sqlitequery(pilib.controldatabase, "update systemstatus set lastpicontrolpoll='" + pilib.gettimestring() + "'") channels = pilib.readalldbrows(pilib.controldatabase, 'channels') outputs = pilib.readalldbrows(pilib.controldatabase, 'outputs') controlalgorithms = pilib.readalldbrows(pilib.controldatabase, 'controlalgorithms') algorithmnames=[] for algorithm in controlalgorithms: algorithmnames.append(algorithm['name']) # Cycle through channels and set action based on setpoint # and algorithm if set to auto mode for channel in channels: statusmsg = '' querylist = [] channelindex = str(int(channel['channelindex'])) channelname = channel['name'] logtablename = 'channel' + '_' + channel['name'] + '_log' time = pilib.gettimestring() disableoutputs = True # Make sure channel is enabled if channel['enabled']: # Create log if it doesn't exist query = 'create table if not exists \'' + logtablename + '\' (time text, controlinput text, controlvalue real, setpointvalue real, action real, algorithm text, enabled real, statusmsg text)' pilib.sqlitequery(pilib.logdatabase, query) statusmsg = '' if 'setpointvalue' in channel: setpointvalue = float(channel['setpointvalue']) else: statusmsg += 'No setpoint. ' # Need to test for age of data. If stale or disconnected, invalidate if 'controlvalue' in channel: try: controlvalue = float(channel['controlvalue']) except (ValueError, TypeError) as e: statusmsg += 'Invalid control value. ' controllib.setcontrolvalue(pilib.controldatabase, channelname, 0) else: statusmsg += 'No controlvalue. ' # Test to see if key exists and is true if 'enabled' in channel: if channel['enabled']: pass else: statusmsg += 'Channel disabled. ' else: statusmsg += 'Error. Enabled key does not exist' mode = channel['mode'] channelalgorithmname = channel['controlalgorithm'] controlinput = channel['controlinput'] logpoints = channel['logpoints'] # Move forward if everything is defined for control if channel['enabled'] and 'controlvalue' in locals() and 'setpointvalue' in locals(): statusmsg += 'Channel Enabled. ' if mode == 'auto': statusmsg += 'Mode:Auto. ' #print('running auto sequence') # run algorithm on channel response = controllib.runalgorithm(pilib.controldatabase, pilib.recipedatabase, channelname) action = response[0] message = response[1] statusmsg += ' ' + response[1] + ' ' statusmsg += 'Action: ' + str(action) + '. ' # Set action in channel controllib.setaction(pilib.controldatabase, channelname, action) elif mode == 'manual': #print('manual mode') statusmsg += 'Mode:Manual. ' # action = controllib.getaction(pilib.controldatabase, channelname) else: #print('error, mode= ' + mode) statusmsg += 'Mode:Error. ' if systemstatus['enableoutputs']: statusmsg += 'System outputs enabled. ' if channel['outputsenabled']: statusmsg += 'Channel outputs enabled. ' disableoutputs = False # find out whether action is positive or negative or # not at all. # and act. for now, this is binary, but in the future # this will be a duty cycle daemon outputsetnames = [] outputresetnames = [] if action > 0: print("set positive output on") outputsetnames.append(channel['positiveoutput']) outputresetnames.append(channel['negativeoutput']) elif action < 0: print("set negative output on") outputsetnames.append(channel['negativeoutput']) outputresetnames.append(channel['positiveoutput']) elif action == 0: statusmsg += 'No action. ' outputresetnames.append(channel['positiveoutput']) outputresetnames.append(channel['negativeoutput']) else: statusmsg += 'Algorithm error. Doing nothing.' outputsetname = None # Check to see if outputs are ready to enable/disable # If not, pull them from list of set/reset outputstoset=[] for outputname in outputsetnames: if channelalgorithmname in algorithmnames: offtime = pilib.sqlitedatumquery(pilib.controldatabase, "select offtime from outputs where name='" + outputname + "'" ) if pilib.timestringtoseconds(pilib.gettimestring()) - pilib.timestringtoseconds(offtime) > controlalgorithms[algorithmnames.index(channelalgorithmname)]['minofftime']: outputstoset.append(outputname) else: statusmsg += 'Output ' + outputname + ' not ready to enable. ' else: statusmsg += 'Algorithm Error: Not found. ' outputstoreset=[] for outputname in outputresetnames: if channelalgorithmname in algorithmnames: ontime = pilib.sqlitedatumquery(pilib.controldatabase, "select ontime from outputs where name='" + outputname +"'") if pilib.timestringtoseconds(pilib.gettimestring()) - pilib.timestringtoseconds(ontime) > controlalgorithms[algorithmnames.index(channelalgorithmname)]['minontime']: outputstoreset.append(outputname) else: statusmsg += 'Output ' + outputname + ' not ready to disable. ' else: statusmsg += 'Algorithm Error: Not found. ' """ TODO: Change reference to controlinputs to name rather than id. Need to double-check enforcement of no duplicates.""" # Find output in list of outputs if we have one to set time = pilib.gettimestring() if len(outputstoset) > 0 or len(outputstoreset) > 0: for output in outputs: if output['name'] in outputstoset: # check current status currvalue = output['value'] if currvalue == 0: # No need to set if otherwise. Will be different for analog out # set ontime querylist.append('update outputs set ontime=\'' + time + '\' ' + 'where id=\'' + output['id'] + '\'') # set value querylist.append("update outputs set value = 1 where id='" + output['id'] + '\'') statusmsg += 'Output ' + output['name'] + ' enabled. ' else: statusmsg += 'Output ' + output['name'] + ' already enabled. ' if output['name'] in outputstoreset: # check current status currvalue = output['value'] if currvalue == 1: # No need to set if otherwise. Will be different for analog out # set ontime querylist.append('update outputs set offtime=\'' + time + '\' ' + 'where id=\'' + output['id'] + '\'') # set value querylist.append('update outputs set value = 0 where id=\'' + output['id'] + '\'') statusmsg += 'Output ' + output['name'] + ' disabled. ' else: statusmsg += 'Output ' + output['name'] + ' already disabled. ' else: statusmsg += 'Channel outputs disabled. ' action = 0 else: statusmsg += 'System outputs disabled. ' action = 0 # Insert entry into control log pilib.makesqliteinsert(pilib.logdatabase, logtablename, [time, controlinput, controlvalue, setpointvalue, action, channelalgorithmname, channel['enabled'], statusmsg]) pilib.sqliteinsertsingle(pilib.logdatabase, logtablename, [time, controlinput, controlvalue, setpointvalue, action, channelalgorithmname, channel['enabled'], statusmsg]) # Size log pilib.sizesqlitetable(pilib.logdatabase, logtablename, logpoints) # print(statusmsg) else: # print('channel not enabled') statusmsg += 'Channel not enabled. ' # If active reset and we didn't set channel modes, disable outputs # Active reset is not yet explicitly declared, but implied if disableoutputs: statusmsg += 'Disabling Outputs. ' for output in outputs: if output['name'] in [channel['positiveoutput'], channel['negativeoutput']]: # set value querylist.append("update outputs set value = 0 where id='" + output['id'] + '\'') statusmsg += 'Output ' + output['name'] + ' disabled. ' # Set status message for channel # print(statusmsg) querylist.append('update channels set statusmessage=\'' + statusmsg + '\'' + 'where channelindex=' + channelindex) # Set update time for channel querylist.append('update channels set controlupdatetime=\'' + time + '\'' + 'where channelindex=' + channelindex) # Execute query pilib.sqlitemultquery(pilib.controldatabase, querylist) # We do this system status again to refresh settings systemstatus = pilib.readalldbrows(pilib.controldatabase, 'systemstatus')[0] from actions import processactions processactions() # Wait for delay time #print('sleeping') # spilights.updatelightsfromdb(pilib.controldatabase, 'indicators') if runonce: break pilib.log(pilib.syslog, 'Picontrol Sleeping for ' + str(systemstatus['picontrolfreq']), 2, pilib.sysloglevel) pilib.log(pilib.controllog, 'Picontrol Sleeping for ' + str(systemstatus['picontrolfreq']), 2, pilib.sysloglevel) sleep(systemstatus['picontrolfreq']) pilib.log(pilib.syslog, 'picontrol not enabled. exiting.', 1, pilib.sysloglevel)
else: name = queryresult[0][0] valuelist = [tuple[0], tuple[1], 'F', pilib.gettimestring(), 1, name] query = pilib.makesqliteinsert('sensordata', valuelist) # print(query) querylist.append(query) # execute combined query #print(querylist) ######################################## # Do thermocouple stuff import subprocess tctemp = subprocess.check_output( ['python3', '/usr/lib/modwsgi/max31855-1.0/getmaxtemp.py']) print('temperature is ') print(tctemp) print(tctemp) querylist.append( pilib.makesqliteinsert( 'sensordata', ['TC1', 'SPITC', 'TC', tctemp, 'F', pilib.gettimestring(), 1, ''])) if querylist: print(querylist) pilib.sqlitemultquery(database, querylist) print('doing querylist')
querylist.append( "update outputs set value = 0 where id='" + output['id'] + '\'') statusmsg += 'Output ' + output['name'] + ' disabled. ' # Set status message for channel print(statusmsg) querylist.append('update channels set statusmessage=\'' + statusmsg + '\'' + 'where channelindex=' + channelindex) # Set update time for channel querylist.append('update channels set controlupdatetime=\'' + time + '\'' + 'where channelindex=' + channelindex) # Execute query pilib.sqlitemultquery(pilib.controldatabase, querylist) # We do this system status again to refresh settings systemstatus = pilib.readalldbrows(pilib.controldatabase, 'systemstatus')[0] from processactions import processactions processactions() # Wait for delay time #print('sleeping') # spilights.updatelightsfromdb(pilib.controldatabase, 'indicators') pilib.writedatedlogmsg( pilib.systemstatuslog, 'Sleeping for .' + str(systemstatus['systemstatusfreq']), 2, pilib.systemstatusloglevel)
polltime = pilib.sqlitedatumquery( pilib.sessiondatabase, 'select updatefrequency from \'settings\'') # Go through sessions and delete expired ones sessions = pilib.readalldbrows(pilib.sessiondatabase, 'sessions') sessions = pilib.readalldbrows(pilib.sessiondatabase, 'sessions') arrayquery = [] for session in sessions: sessionstart = pilib.timestringtoseconds(session['timecreated']) sessionlength = session['sessionlength'] if time.time() - sessionstart > sessionlength: arrayquery.append('delete from sessions where sessionid=\'' + session['sessionid'] + '\'') # Delete offensive sessions pilib.sqlitemultquery(pilib.sessiondatabase, arrayquery) # Reload surviving sessions and summarize sessions = pilib.readalldbrows(pilib.sessiondatabase, 'sessions') sessiondictarray = [] for session in sessions: found = 0 for dict in sessiondictarray: if dict['username'] == session['username']: found = 1 index = sessiondictarray.index(dict) dict['sessions'] += 1 sessiondictarray[index] = dict if not found: sessiondictarray.append({ 'username': session['username'],