def main(argv=sys.argv):
import logging
logging.basicConfig(level=logging.DEBUG)
# if len(argv) != 2:
# usage(argv)
# config_uri = argv[1]
# setup_logging(config_uri)
# settings = get_appsettings(config_uri)
# engine = engine_from_config(settings, 'sqlalchemy.')
logging.debug("Initialise Engine")
engine = sqlalchemy.create_engine(DBFILE, echo=False)
meta.Session.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
DBSession = meta.Session()
#DBSession.configure(bind=engine)
logging.debug("Populating Data")
populateData.init_data(DBSession)
#populateUser()
logging.debug("Populated")
DBSession.flush()
#DBSession.commit()
#We also want any alembic scripts to be executed (or not if we build the DB
#properly)
alembic_cfg = Config("cogent/alembic.ini") #TODO: WARNING RELATIVE PATH
command.stamp(alembic_cfg, "head")
DBSession.close()
def populateUser():
"""Helper method to populate the User table with our initial root user"""
session = meta.Session()
hasUser = session.query(user.User).first()
if hasUser is None:
print "Warning: No users setup on the system"
print " Creating Root User"
newUser = raw_input("Login Name: ")
userEmail = raw_input("User Email: ")
passOne = "FOO"
passTwo = "BAR"
while passOne != passTwo:
passOne = getpass.getpass()
passTwo = getpass.getpass("Repeat Password: "******"Passwords do not match"
#Setup a new User
thisUser = user.User(username=newUser,
email=userEmail,
password=meta.pwdContext.encrypt(passOne),
level="root")
session.add(thisUser)
session.flush()
transaction.commit()
def create_tables(self):
""" create any missing tables using sqlalchemy
"""
#self.metadata.create_all(self.engine)
# TODO: follow the instructions at url:
# https://alembic.readthedocs.org/en/latest/tutorial.html#building-an-up-to-date-database-from-scratch
# to write an alembic version string
session = meta.Session()
models.populateData.init_data(session)
if session.query(SensorType).get(0) is None:
raise Exception("SensorType must be populated by alembic " +
"before starting BaseLogger")
session.close()
def __init__(self):
log = logging.getLogger(__name__)
self.log = log
log.debug("Starting Serial connection")
self.con = serial.Serial("/dev/ttyUSB0", 115200, timeout=10)
log.debug("Starting Database Connection")
engine = sqlalchemy.create_engine(DBFILE, echo=False)
#Actually not that fussed in this test code about creating stuff propery
meta.Base.metadata.bind = engine
meta.Base.metadata.create_all(engine)
meta.Session.configure(bind=engine)
log.debug("Populating Data")
#Populate with our intial dataset
models.populateData.init_data()
#And add our local sensor types
session = meta.Session()
sensortype = models.SensorType(id=HOPS, name="CTP hops")
session.merge(sensortype)
sensortype = models.SensorType(id=TX_PWR, name="Tx Power")
session.merge(sensortype)
sensortype = models.SensorType(id=N_COUNT, name="Neighbor Count")
session.merge(sensortype)
sensortype = models.SensorType(id=CTP_SEQ, name="CTP Sequence")
session.merge(sensortype)
sensortype = models.SensorType(id=2000, name="Neigh0")
session.merge(sensortype)
sensortype = models.SensorType(id=2001, name="Neigh1")
session.merge(sensortype)
sensortype = models.SensorType(id=2002, name="Neigh2")
session.merge(sensortype)
sensortype = models.SensorType(id=2003, name="Neigh3")
session.merge(sensortype)
session.flush()
session.commit()
def saveData(self, nodeId, values):
"""Save a reading in the Database
:var nodeId: String Containing the Current Cost Node Id
:var values: Tuple containing sensor values as returned by ploggParseValue
"""
log = self.log
log.debug("Saving data for {0} {1}".format(nodeId, values))
session = meta.Session()
mappedId = NODEMAP[nodeId]
theNode = session.query(models.Node).filter_by(id=mappedId).first()
#Fetch Sensor Types
wattSensor = session.query(
models.SensorType).filter_by(name="Plogg Watts").first()
log.debug("Watt Sensor {0}".format(wattSensor))
kWhSensor = session.query(
models.SensorType).filter_by(name="Plogg kWh").first()
log.debug("kW Sensor {0}".format(kWhSensor))
currentSensor = session.query(
models.SensorType).filter_by(name="Plogg Current").first()
log.debug("A Sensor {0}".format(currentSensor))
#Create if it doesnt Exist
if not theNode:
log.info("Node {0} / {1} does not exist, Creating".format(
nodeId, mappedId))
theNode = models.Node(id=mappedId, locationId=None)
session.add(theNode)
session.flush()
log.debug("Node is {0}".format(theNode))
#And we need to add a set of sensors
for item in [wattSensor, kWhSensor, currentSensor]:
theSensor = models.Sensor(sensorTypeId=item.id,
nodeId=theNode.id,
calibrationSlope=1.0,
calibrationOffset=0.0)
session.add(theSensor)
session.flush()
sampleTime, sampleWatts, samplekWh, sampleCurrent = values
#Then Add the Readings
theReading = models.Reading(time=sampleTime,
nodeId=theNode.id,
location=theNode.locationId,
typeId=wattSensor.id,
value=sampleWatts)
session.add(theReading)
theReading = models.Reading(time=sampleTime,
nodeId=theNode.id,
location=theNode.locationId,
typeId=kWhSensor.id,
value=samplekWh)
session.add(theReading)
theReading = models.Reading(time=sampleTime,
nodeId=theNode.id,
location=theNode.locationId,
typeId=currentSensor.id,
value=sampleCurrent)
session.add(theReading)
#And add a nodeState
theNodeState = models.NodeState(time=sampleTime,
nodeId=theNode.id,
parent=theNode.id,
localtime=sampleTime)
session.add(theNodeState)
session.flush()
session.commit()
session.close()
def __init__(self):
log.debug("Initialising Push Script")
engine = sqlalchemy.create_engine(LOCAL_URL)
#Intialise the databse
models.initialise_sql(engine)
models.populate_data()
#Get relevant models etc
session = meta.Session()
#Check if our data exists
theDeployment = session.query(
models.Deployment).filter_by(name="PushTest").first()
if theDeployment is None:
log.debug("--> Create New Deployment")
#Create a new Deployment
theDeployment = models.Deployment(name="PushTest")
session.add(theDeployment)
session.flush()
log.debug("Deployment is {0}".format(theDeployment))
theHouse = session.query(
models.House).filter_by(address="Push Address").first()
if theHouse is None:
log.debug("--> Create new House")
theHouse = models.House(address="Push Address",
deploymentId=theDeployment.id)
session.add(theHouse)
session.flush()
log.debug("House is {0}".format(theHouse))
#Two Rooms
roomType = session.query(
models.RoomType).filter_by(name="Bedroom").first()
masterBed = session.query(
models.Room).filter_by(name="Master Bedroom").first()
if masterBed is None:
masterBed = models.Room(name="Master Bedroom",
roomTypeId=roomType.id)
session.add(masterBed)
secondBed = session.query(
models.Room).filter_by(name="Second Bedroom").first()
if secondBed is None:
secondBed = models.Room(name="Second Bedroom",
roomTypeId=roomType.id)
session.add(secondBed)
session.flush()
#Locations
masterLoc = session.query(models.Location).filter_by(
houseId=theHouse.id, roomId=masterBed.id).first()
if masterLoc is None:
log.debug("Create New Master Location")
masterLoc = models.Location(houseId=theHouse.id,
roomId=masterBed.id)
session.add(masterLoc)
secondLoc = session.query(models.Location).filter_by(
houseId=theHouse.id, roomId=secondBed.id).first()
if secondLoc is None:
log.debug("Create New Second Location")
secondLoc = models.Location(houseId=theHouse.id,
roomId=secondBed.id)
session.add(secondLoc)
session.flush()
log.debug("Master Location {0}".format(masterLoc))
log.debug("Second Location {0}".format(secondLoc))
#Add Nodes to each Location
node37 = session.query(models.Node).filter_by(id=37).first()
if not node37:
node37 = models.Node(id=37)
session.add(node37)
node38 = session.query(models.Node).filter_by(id=38).first()
if node38 is None:
node38 = models.Node(id=38)
session.add(node38)
node37.location = masterLoc
node38.location = secondLoc
session.flush()
#All Our Data adding script needs to worry about is the Nodes
self.node37 = node37
self.node38 = node38
#Finally add an Upload URL if required
# theUrl = session.query(models.UploadURL).filter_by(url="[email protected]").first()
# if not theUrl:
# theUrl = models.UploadURL(url="[email protected]",
# dburl="mysql://*****:*****@localhost:3307/pushTest")
# session.add(theUrl)
# theUrl = session.query(models.UploadURL).filter_by(url="*****@*****.**").first()
# if not theUrl:
# theUrl = models.UploadURL(url="*****@*****.**",
# dburl="mysql://*****:*****@localhost:3307/chtest")
# session.add(theUrl)
session.commit()
def run(self):
session = meta.Session()
localCount = 0
stateOne = True
node37 = self.node37
node38 = self.node38
try:
while True:
#Add a reading every N seconds
log.debug("Adding New Reading {0}".format(datetime.utcnow()))
theReading = models.Reading(time=datetime.utcnow(),
nodeId=node37.id,
locationId=node37.locationId,
value=localCount,
typeId=0)
session.add(theReading)
theReading = models.Reading(time=datetime.utcnow(),
nodeId=node38.id,
locationId=node38.locationId,
value=100 - localCount,
typeId=0)
session.add(theReading)
session.flush()
if localCount == STATE_SWITCH:
log.debug("Switching States")
localCount = 0
#Add a node state
if stateOne:
theState = models.NodeState(time=datetime.utcnow(),
nodeId=node37.id,
parent=1024,
localtime=0)
session.add(theState)
theState = models.NodeState(time=datetime.utcnow(),
nodeId=node38.id,
parent=1024,
localtime=0)
session.add(theState)
else:
theState = models.NodeState(time=datetime.utcnow(),
nodeId=node37.id,
parent=node38.id,
localtime=0)
session.add(theState)
theState = models.NodeState(time=datetime.utcnow(),
nodeId=node38.id,
parent=1024,
localtime=0)
session.add(theState)
stateOne = not stateOne
session.flush()
else:
localCount += 1
time.sleep(READING_GAP)
session.commit()
except KeyboardInterrupt:
log.debug("Closing Everything down")
session.flush()
session.commit()
def addMany(self):
"""Add around 1 million records to the database"""
"""Add about 2000 Records to the Database"""
session = meta.Session()
localCount = 0
stateOne = True
fakeTime = datetime.utcnow()
node37 = self.node37
node38 = self.node38
#Work out a better start time
currentTime = datetime.utcnow()
#Calculate total seconds for samples
deploymentSeconds = (BULK_SAMPLES * READING_GAP) * BULK_OFFSET
fakeTime = currentTime - timedelta(seconds=deploymentSeconds)
log.debug("Current Time is {0} -> Start time is {1}".format(
currentTime, fakeTime))
totalCount = 0
try:
#while totalCount < 500000:
while totalCount < BULK_SAMPLES:
#Add a reading every N seconds
#log.debug("Adding New Reading {0}".format(fakeTime))
thisRRD = RRDLIST.get((node37.id, 0, node37.locationId), None)
if thisRRD is None:
thisRRD = rrdstore.RRDStore(node37.id,
0,
node37.locationId,
startTime=fakeTime)
RRDLIST[(node37.id, 0, node37.locationId)] = thisRRD
theReading = models.Reading(time=fakeTime,
nodeId=node37.id,
locationId=node37.locationId,
value=localCount,
typeId=0)
session.add(theReading)
thisRRD.update(fakeTime, localCount)
# ---- AND Node 38
thisRRD = RRDLIST.get((node38.id, 0, node38.locationId), None)
if thisRRD is None:
thisRRD = rrdstore.RRDStore(node38.id,
0,
node38.locationId,
startTime=fakeTime)
RRDLIST[(node38.id, 0, node38.locationId)] = thisRRD
thisReading = models.Reading(time=fakeTime,
nodeId=node38.id,
locationId=node38.locationId,
value=100 - localCount,
typeId=0)
session.add(theReading)
thisRRD.update(fakeTime, localCount)
session.flush()
if localCount == STATE_SWITCH:
log.debug("Switching States")
localCount = 0
#Add a node state
if stateOne:
theState = models.NodeState(time=fakeTime,
nodeId=node37.id,
parent=1024,
localtime=0)
session.add(theState)
theState = models.NodeState(time=fakeTime,
nodeId=node38.id,
parent=1024,
localtime=0)
session.add(theState)
else:
theState = models.NodeState(time=fakeTime,
nodeId=node37.id,
parent=node38.id,
localtime=0)
session.add(theState)
theState = models.NodeState(time=fakeTime,
nodeId=node38.id,
parent=1024,
localtime=0)
session.add(theState)
stateOne = not stateOne
session.flush()
session.commit
log.debug("Commiting Samples {0}".format(totalCount))
else:
localCount += 1
#time.sleep(READING_GAP)
totalCount += 1
fakeTime = fakeTime + timedelta(seconds=READING_GAP)
#session.commit()
except KeyboardInterrupt:
log.debug("Closing Everything down")
session.flush()
session.commit()
session.flush()
session.commit()
def populatedata(session = None):
"""Populate our testing database with an example deployment etc"""
#The Deployment
if not session:
print "Creating a new Session"
session = meta.Session()
#Remove Existing nodes as they just confuse things
qry = session.query(models.Node)
qry.delete()
session.flush()
session.commit()
transaction.commit()
#now = datetime.datetime.now()
now = datetime.datetime(2013,01,01,00,00,00)
thedeployment = models.Deployment(id=1,
name="testing",
description="a testing deployment",
startDate = now)
session.merge(thedeployment)
session.flush()
#We want a server
theserver = models.Server(id=1,
hostname = "Testing Server")
session.merge(theserver)
session.flush()
#We also add a testing house
thehouse = models.House(id=1,
address="testing house",
deploymentId = thedeployment.id,
startDate = now,
serverid = 1)
session.merge(thehouse)
session.flush()
#Lets add two locations
thebedroom = models.Location(id=1,
houseId = thehouse.id,
roomId = 1)
thebathroom = models.Location(id=2,
houseId = thehouse.id,
roomId = 6)
session.merge(thebedroom)
session.merge(thebathroom)
session.flush()
session.commit()
#update the nodes so they have the correct locations
thenode = session.query(models.Node).filter_by(id=837).first()
if thenode is None:
print "Create Node 837"
thenode = models.Node(id=837,
locationId=1)
session.add(thenode)
thenode = session.query(models.Node).filter_by(id=838).first()
if thenode is None:
thenode = models.Node(id=838,
locationId=2)
print "Create Node 838"
session.add(thenode)
session.flush()
transaction.commit()
session.commit()
#transaction.commit()
#To test the Yields I also want an incomplte database
thehouse = models.House(id=2,
address="Poor House",
deploymentId = thedeployment.id,
startDate = now,
serverid=1)
session.merge(thehouse)
#Add a couple of locations
#Lets add two locations
thebedroom = models.Location(id=3,
houseId = thehouse.id,
roomId = 1)
session.merge(thebedroom)
thebathroom = models.Location(id=4,
houseId = thehouse.id,
roomId = 6)
session.merge(thebathroom)
session.flush()
session.commit()
transaction.commit()
thenode = session.query(models.Node).filter_by(id=1061).first()
if thenode is None:
thenode = models.Node(id=1061,
locationId=3)
print "Create Node 1061"
session.add(thenode)
thenode.locationId = 3
thenode = session.query(models.Node).filter_by(id=1063).first()
if thenode is None:
thenode = models.Node(id=1063,
locationId=4)
print "Create Node 1063"
session.add(thenode)
session.flush()
session.commit()
def populate_readings(session = None):
#The Deployment
if not session:
print "Creating a new Session"
session = meta.Session()
now = datetime.datetime(2013, 01, 01, 00, 00, 00)
#Now we want to add a load of readings / Nodestates
thetime = now# - datetime.timedelta(days = 10)
endtime = now + datetime.timedelta(days=10)
#print "START TIME {0}".format(starttime)
thecount = 0.0
seqnum = -1
while thetime < endtime:
#Increment and roll over the sequence number
seqnum += 1
if seqnum > 255:
seqnum = seqnum - 255
for nid in [837, 838, 1061, 1063]:
locationid = 1
if nid == 838:
locationid = 2
elif nid == 1061:
locationid = 3
#Sample very 10 minutes (50% Yield)
if thetime.minute % 10 == 0:
continue
elif nid == 1063:
locationid = 4
#And remove every 3rd sample
if thetime.minute % 15 == 0:
continue
ns = models.NodeState(nodeId = nid,
parent = 1,
time = thetime,
seq_num = seqnum)
session.add(ns)
reading = models.Reading(nodeId = nid,
typeId = 0,
time = thetime,
locationId = locationid,
value = 18.0+(2.0*math.sin(thecount)),
)
session.add(reading)
#Increment the time
thetime = thetime + datetime.timedelta(minutes=5)
thecount = thecount + (3.14 / 144)
session.commit()
transaction.commit()
session.commit()
session.close()
def run(self):
"""Single iteration of the mainloop"""
#Wait for
data = self.con.readline()
log = self.log
if data:
session = meta.Session()
log.debug("> {0}".format(data.strip()))
if "PKT:" in data:
now = datetime.datetime.now()
pktdata = data.strip().split(":") #Get the main packet data
pktitems = [int(x) for x in pktdata[1].split(",")]
log.debug(">>PKT. {0}".format(pktitems))
(nodeid, time, ctp_seq, hops, tx_pwr, msg_seq, parent, n_count,
temp, hum) = pktitems
#Temperature / Humidity conversion
temp = float(temp)
temp = -39.6 + 0.01 * temp
hum = float(hum)
hum = -4 + 0.0405 * hum - 0.0000028 * (hum * hum)
qry = session.query(models.Node).filter_by(id=nodeid)
thenode = qry.first()
if thenode is None:
log.info("No such node {0}".format(nodeid))
thenode = models.Node(id=nodeid)
session.add(thenode)
session.flush()
#Then we can create a nodestate
ns = models.NodeState(time=now,
nodeId=nodeid,
localtime=time,
seq_num=msg_seq,
parent=parent)
session.add(ns)
#And Readings
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=HOPS,
locationId=thenode.locationId,
value=hops)
session.add(rdg)
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=TX_PWR,
locationId=thenode.locationId,
value=tx_pwr)
session.add(rdg)
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=N_COUNT,
locationId=thenode.locationId,
value=n_count)
session.add(rdg)
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=CTP_SEQ,
locationId=thenode.locationId,
value=ctp_seq)
session.add(rdg)
#Temperature
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=0,
locationId=thenode.locationId,
value=temp)
session.add(rdg)
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=2,
locationId=thenode.locationId,
value=hum)
session.add(rdg)
session.commit()
#Now neighbor table info
print pktdata
if len(pktdata) > 2:
neighinfo = pktdata[2:]
log.info("Neighbor Table is {0}".format(neighinfo))
for idx, item in enumerate(neighinfo):
print item, idx
vals = item.split(",")
rdg = models.Reading(time=now,
nodeId=nodeid,
typeId=2000 + idx,
locationId=thenode.locationId,
value=vals[0])
session.add(rdg)
session.commit()