webapp: 500 werte, native sqL

2 files changed, 56 insertions, 34 deletions

diff --git a/ebus/webapp/__init__.py b/ebus/webapp/__init__.py
index d65e2d1..cd5de8c 100644
--- a/ebus/webapp/__init__.py
+++ b/ebus/webapp/__init__.py
@@ -1,4 +1,5 @@
 # -*- coding: utf-8 -*-
+# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python
 
 import bottle 
 import sqlalchemy
@@ -17,7 +18,7 @@ import time
 app = bottle.Bottle("ebus")
 bottle.debug()
 
-engine = create_engine("postgresql://ebus:ebus@10.2.2.26/ebus", echo=True)
+engine = create_engine("postgresql:///ebus", echo=True)
 Session = sessionmaker()
 Session.configure(bind=engine)
 
@@ -32,40 +33,60 @@ def static_files(filename):
     return bottle.static_file(filename, root=os.path.join(os.path.dirname(__file__),"static"))
 
 @app.route('/sensor/:name')
-def sensor_data_single(name):
-    #s = Session()
-    #sensorid = s.query(model.Sensor).filter(model.Sensor.name == name).first()
-    #values = Session().query(model.Value).add_columns("timestamp","value_float","value_string","value_int")
-    #values = values.filter(model.Value.sensor == sensorid)
-    #return json.dumps(values.first().value_int)
-    return sensor_data_fromto(name,None,None)
+def sensor_data(name):
+    try:
+        # get sensor_id for name
+        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
+
+        # select data from cache-view
+        conn = soup.connection()
+        s = text("""SELECT timestamp, COALESCE(value_int, value_float) as "value_real"
+                      FROM value
+                     WHERE sensor_id = :sensor_id
+                  ORDER BY timestamp
+                  LIMIT 1""")
+        value = conn.execute(s, sensor_id=sensor_id).first()
+
+        return {'sensor':name,'data':[maketime(value.timestamp), float(value.value_real)], 'error':None}
+    except Exception,e:
+        return {'sensor':name,'data':None, 'error':str(e)}
 
 @app.route('/sensor/:name/:startdate/:enddate')
 def sensor_data_fromto(name,startdate,enddate):
-    s = Session()
-    sensorid = s.query(model.Sensor).filter(model.Sensor.name == name).first()
-    values = Session().query(model.Value).add_columns("timestamp","value_float","value_string","value_int")
-    values = values.filter(model.Value.sensor == sensorid)
-    
-    if startdate == None or enddate == None:
-        queried_values = []
-        queried_values.append(values.first())
-    else:
-        now = datetime.datetime.now()
-        f = datetime.datetime.fromtimestamp(float(startdate))
-        t = datetime.datetime.fromtimestamp(float(enddate))
-        values = values.filter(model.Value.timestamp.between(f,t))
-        queried_values = values.all()
-    v = []
-    for value in queried_values:
-        if value.value_float != None:
-            v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_float]) # time.altzone ist negativ
-        elif value.value_int != None:
-            v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_int])
-        elif value.value_string != None:
-            v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_string])
-    return {'data':v, 'error':None}
+    try:
+        interval = float(enddate) - float(startdate)
+        if interval <= 0: raise Exception("Invalid interval")
+        if interval >= 14 * 24 * 60 * 60: raise Exception("interval too big")
+
+        modulo = interval / 500 #500 values
 
+        startdate = datetime.datetime.fromtimestamp(float(startdate))
+        enddate = datetime.datetime.fromtimestamp(float(enddate))
+        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
+
+
+
+        conn = soup.connection()
+        s = text("""
+        SELECT to_timestamp( extract(epoch from timestamp)::int - extract(epoch from timestamp)::int % :modulo ) "round_timestamp", 
+               AVG(COALESCE(value_int,value_float)) "value_real" 
+         FROM value
+        WHERE timestamp > :startdate
+          AND timestamp < :enddate
+          AND sensor_id = :sensor_id
+        GROUP BY "round_timestamp"
+        ORDER BY "round_timestamp"
+        """)
+
+        values = conn.execute(s, sensor_id=sensor_id, startdate=startdate, enddate=enddate, modulo=modulo).fetchall()
+        values = map(lambda row: (maketime(row.round_timestamp),
+                                 row.value_real),
+                     values)
+
+        print "Sent", len(values)
+        return {'sensor':name,'data':values, 'error':None}
+    except Exception,e:
+        return {'sensor':name, 'data':None, 'error':str(e)}
 
 # rechnet zeit so um wie Javascript
 # teil es braucht XXX
@@ -92,7 +113,7 @@ def sensor_data_cached_fromto(name, timestamp_from):
                                  row.value_real.__float__()),
                      values)
 
-        return {'data':values, 'error':None}
+        return {'sensor':name,'data':values, 'error':None}
     except Exception,e:
-        return {'data':None, 'error':str(e)}
+        return {'sensor':name, 'data':None, 'error':str(e)}
 
diff --git a/ebus/webapp/static/ebus.js b/ebus/webapp/static/ebus.js
index 15fe692..32e9f3a 100644
--- a/ebus/webapp/static/ebus.js
+++ b/ebus/webapp/static/ebus.js
@@ -99,7 +99,8 @@ $(document).ready(function(){
     $("#overview").bind("plotselected", function (event, ranges) {
         range_from = Math.round(ranges.xaxis.from / 1000);
 	range_to = Math.round(ranges.xaxis.to / 1000);
-	if (range_to - range_from > 4 * 24 * 60 * 60) {
+	// max selection range 14 days
+	if (range_to - range_from > 14 * 24 * 60 * 60) {
 	    plotOverview.setSelection({xaxis: {'from': from*1000, 'to': to*1000}}, true);
 	    return;
 	} else {