1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
# -*- coding: utf-8 -*-
import os
import json
import datetime
import time
import bottle
from sqlalchemy import text
app = bottle.Bottle("ebus")
def maketime(datetime):
"""rechnet zeit so um wie Javascript es braucht XXX"""
return (time.mktime(datetime.timetuple())+time.altzone*-1)*1000
def parsetime(timestamp):
"""macht aus javascript zeit systemzeit"""
return ((timestamp/1000) - time.altzone*-1)
@app.route('/')
def index_file():
return static_files("index.html")
@app.route('/static/:filename#.+#')
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(soup,name):
try:
sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
conn = soup.connection()
sql = text("""SELECT timestamp, COALESCE(value_int, value_float) as "value_real"
FROM value
WHERE sensor_id = :sensor_id
ORDER BY timestamp DESC
LIMIT 1""")
value = conn.execute(sql, 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(soup,name,startdate,enddate):
try:
interval = float(enddate) - float(startdate)
modulo = interval / 500 #500 values
startdate = datetime.datetime.fromtimestamp(float(startdate))
enddate = datetime.datetime.fromtimestamp(float(enddate))
if interval <= 0: raise Exception("Invalid interval")
if interval >= 14 * 24 * 60 * 60: raise Exception("interval too big")
sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
conn = soup.connection()
sql = 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(sql,
sensor_id=sensor_id,
startdate=startdate,
enddate=enddate,
modulo=modulo).fetchall()
values = map(lambda row: (maketime(row.round_timestamp), row.value_real),
values)
return {'sensor':name,'data':values, 'error':None}
except Exception,e:
return {'sensor':name, 'data':None, 'error':str(e)}
@app.route('/sensor_cached/:name/:timestamp_from')
def sensor_data_cached_fromto(soup, name, timestamp_from):
try:
timestamp_from = datetime.datetime.fromtimestamp(float(timestamp_from))
sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id
# select data from cache-view
conn = soup.connection()
sql = text("""SELECT timestamp, value_real AS "value_real"
FROM vi_value_cache
WHERE timestamp >= :timestamp_from
AND sensor_id = :sensor_id
ORDER BY timestamp""")
values = conn.execute(sql, timestamp_from=timestamp_from, sensor_id=sensor_id).fetchall()
values = map(lambda row: (maketime(row.timestamp), row.value_real.__float__()),
values)
return {'sensor':name,'data':values, 'error':None}
except Exception,e:
return {'sensor':name, 'data':None, 'error':str(e)}
import select
import psycopg2
@app.route('/stream')
@app.route('/stream/:startdate')
def stream(soup, startdate=None):
time_start = startdate != None and datetime.datetime.fromtimestamp(parsetime(float(startdate))) \
or datetime.datetime.now()
connection = soup.connection()
conn = connection.connection
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute("LISTEN evt_ebus_value_insert;")
print "Waiting for notifications on channel 'evt_ebus_value_insert'"
values = []
if select.select([conn],[],[],10) == ([],[],[]):
time_stop = datetime.datetime.now()
else:
time_stop = datetime.datetime.now()
conn.poll()
notify = conn.notifies.pop()
if notify:
sql = text("""SELECT sensor.name,
value.timestamp,
COALESCE(value.value_int,value.value_float) "value_real"
FROM value, sensor
WHERE value.sensor_id = sensor.id
AND timestamp >= :time_start
AND timestamp < :time_stop""")
values = connection.execute(sql, time_start=time_start, time_stop=time_stop)
values = map(lambda row: (row.name, maketime(row.timestamp), row.value_real != None and row.value_real.__float__() or None),
values)
cursor.close()
return {'time_start' : maketime(time_start), 'time_stop':maketime(time_stop),
'data':values}
# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python
|
