def collect(): entries = json.loads(local.request.data) for entry in entries: dsnames = entry["dsnames"] dstypes = entry["dstypes"] host = entry["host"] plugin = entry["plugin"] plugin_instance = entry["plugin_instance"] type_ = entry["type"] type_instance = entry["type_instance"] time = entry["time"] values = entry["values"] if len(dsnames) != len(dstypes) or len(dstypes) != len(values): error.log("Length mismatch (dsname, dstype, value): %s", repr(data)) for dstype, dsname, value in zip(dstypes,dsnames,values): metadata_id = None with sqlitedb() as db: for row in db.execute(""" SELECT "id" FROM "metadata" WHERE "dsname" = ? and "dstype" = ? and "host" = ? and "plugin" = ? and "plugin_instance" = ? and "type" = ? and "type_instance" = ? LIMIT 1 """, ( dsname, dstype, host, plugin, plugin_instance, type_, type_instance )): metadata_id = row[0] if metadata_id == None: metadata_id = db.execute(""" INSERT INTO "metadata" ( "dsname", "dstype", "host", "plugin", "plugin_instance", "type", "type_instance" ) VALUES(?,?,?,?,?,?,?) """, ( dsname, dstype, host, plugin, plugin_instance, type_, type_instance )).lastrowid db.execute(""" INSERT INTO "data" ( "metadata", "time", "value" ) VALUES(?,?,?) """, ( metadata_id, time, value ))
def iterdata(metadata_id, resolution=200, start=None, end=None): timesql = '' timeparams = [] if start != None: timeparams.append(start) timesql += 'and "time" >= ?' if end != None: timeparams.append(end) timesql += 'and "time" <= ?' timeparams = tuple(timeparams) if resolution != None: sql = 'SELECT max("time")-min("time")' sql += 'FROM "data" where "metadata" = ?' sql += timesql for row in sqlitedb().execute(sql, (metadata_id,)+timeparams): timediff = row[0] timediff = timediff/resolution sql = 'SELECT avg("time")*1000, avg("value")' sql += 'FROM "data"' sql += 'WHERE "metadata" = ?' sql += timesql sql += 'GROUP BY round("time"/?)' sql += 'ORDER BY "time"' for row in sqlitedb().execute(sql, (metadata_id,)+timeparams+(timediff,)): yield row[0],row[1] else: sql = 'SELECT "time"*1000, "value" FROM "data" WHERE "metadata" = ?' sql += timesql sql += 'ORDER BY "time"' for row in sqlitedb().execute(sql, (metadata_id,)+timeparams): yield row[0],row[1]
def itermetadata(): for x in sqlitedb().execute(""" SELECT "host", "plugin", "plugin_instance", "type", "type_instance", "dsname", "dstype", "id" FROM "metadata" ORDER BY "host", "plugin", "plugin_instance", "type", "type_instance", "dsname", "dstype" """ ): doc = { "host" : x[0], "plugin" : x[1], "plugin_instance": x[2], "type" : x[3], "type_instance" : x[4], "dsname" : x[5], "dstype" : x[6], "id" : x[7] } yield doc