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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
# -*- coding: utf-8 -*-
import os
import json
import datetime
import time
import bottle
from sqlalchemy import text
app = bottle.Bottle("ebus")
def maketime(dt):
"""Rechnet CET DateTime in CET timestamp"""
return time.mktime(dt.timetuple())*1000 # - time.altzone)*1000
def parsetime(timestamp):
"""Machtaus CET timestamp Local DateTime"""
return datetime.datetime.fromtimestamp((timestamp/1000)) # + time.altzone)
def now(conn):
return conn.execute(text("SELECT CURRENT_TIMESTAMP t")).first().t
@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):
connection = soup.connection()
conn = connection.connection
time_start = startdate != None and parsetime(float(startdate)) \
or now(connection)
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute("LISTEN evt_ebus_value_insert;")
values = []
fails = 0
while fails < 5:
if select.select([conn],[],[],10) == ([],[],[]):
time_stop = now(connection)
fails += 1
else:
conn.poll()
notify = conn.notifies.pop()
if not notify:
continue
time_stop = now(connection)
print "time_stop %s"%time_stop
sql = text("""SELECT sensor.name,
value.timestamp,
COALESCE(value.value_int,value.value_float) "value_real",
value_string
FROM value, sensor
WHERE value.sensor_id = sensor.id
AND timestamp >= :time_start
AND timestamp < :time_stop""")
values = map(lambda row: {
"name":row.name,
"timestamp":maketime(row.timestamp),
"value_real":row.value_real != None and row.value_real.__float__() or None,
"value_string":row.value_string
},
connection.execute(sql, time_start=time_start, time_stop=time_stop))
break
cursor.close()
return {'time_start' : maketime(time_start), 'time_stop':maketime(time_stop),
'data':values}
@app.route("/all_values")
def all_values(soup):
conn = soup.connection()
sql = text("""
SELECT sensor.name,
value.timestamp,
COALESCE(value.value_int,value.value_float) "value_real",
value_string
FROM value, sensor, (SELECT MAX(timestamp) as timestamp,
sensor_id
FROM value
WHERE timestamp > CURRENT_TIMESTAMP - '15 minutes'::interval
GROUP BY sensor_id) last_value
WHERE value.timestamp = last_value.timestamp
AND value.sensor_id = last_value.sensor_id
AND value.sensor_id = sensor.id""")
time_stop = now(conn)
values = map(lambda row: {
"name":row.name,
"timestamp":maketime(row.timestamp),
"value_real":row.value_real != None and row.value_real.__float__() or None,
"value_string":row.value_string},
conn.execute(sql))
return {'data':values, 'time_stop':maketime(time_stop)}
# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python
|
