1 | # Copyright 2013 Netherlands eScience Center |
||
2 | # |
||
3 | # Licensed under the Apache License, Version 2.0 (the "License"); |
||
4 | # you may not use this file except in compliance with the License. |
||
5 | # You may obtain a copy of the License at |
||
6 | # |
||
7 | # http://www.apache.org/licenses/LICENSE-2.0 |
||
8 | # |
||
9 | # Unless required by applicable law or agreed to in writing, software |
||
10 | # distributed under the License is distributed on an "AS IS" BASIS, |
||
11 | # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
||
12 | # See the License for the specific language governing permissions and |
||
13 | # limitations under the License. |
||
14 | |||
15 | 1 | import logging |
|
16 | 1 | from iso8601 import parse_date |
|
17 | 1 | from pyramid.httpexceptions import HTTPFound |
|
18 | 1 | from pyramid.response import Response |
|
19 | 1 | from pyramid.view import view_config |
|
20 | 1 | import simplejson |
|
21 | |||
22 | 1 | logger = logging.getLogger(__package__) |
|
23 | |||
24 | |||
25 | 1 | class UploadViews(object): |
|
26 | """ |
||
27 | Serves annotations from a table with following structure:: |
||
28 | |||
29 | CREATE TABLE classification.annotation_538_movement |
||
30 | ( |
||
31 | device_info_serial integer NOT NULL, |
||
32 | date_time timestamp without time zone NOT NULL, |
||
33 | first_index integer NOT NULL, |
||
34 | class_id integer, |
||
35 | class_name character varying(255), |
||
36 | class_red double precision, |
||
37 | class_green double precision, |
||
38 | class_blue double precision, |
||
39 | CONSTRAINT annotation_538_movement_pkey PRIMARY KEY (device_info_serial, date_time, first_index) |
||
40 | ); |
||
41 | """ |
||
42 | |||
43 | 1 | def __init__(self, request): |
|
44 | 1 | self.request = request |
|
45 | 1 | self.db = request.db |
|
46 | 1 | self.table = request.matchdict.get('table', '') |
|
47 | |||
48 | 1 | def fetch_classes(self): |
|
49 | 1 | cursor = self.db.cursor() |
|
50 | 1 | sql_template = "SELECT DISTINCT class_id AS id, class_name AS label, 'rgb(' || floor(class_red*255) ||',' || floor(class_green*255) || ',' || floor(class_blue*255) || ')' AS color FROM {table} ORDER BY class_id" |
|
51 | 1 | sql = sql_template.format(table=self.table) |
|
52 | 1 | cursor.execute(sql) |
|
53 | 1 | return cursor.fetchall() |
|
54 | |||
55 | 1 | def fetch_trackers(self): |
|
56 | 1 | cursor = self.db.cursor() |
|
57 | 1 | sql_template = """SELECT |
|
58 | device_info_serial AS id, |
||
59 | TIMEZONE('zulu', MIN(date_time)) AS start, |
||
60 | TIMEZONE('zulu', MAX(date_time)) AS end, |
||
61 | COUNT(*) AS count |
||
62 | FROM "{table}" |
||
63 | GROUP BY device_info_serial |
||
64 | ORDER BY device_info_serial |
||
65 | """ |
||
66 | 1 | sql = sql_template.format(table=self.table.replace('.', '"."')) |
|
67 | 1 | cursor.execute(sql) |
|
68 | 1 | trackers = cursor.fetchall() |
|
69 | 1 | for tracker in trackers: |
|
70 | 1 | tracker['size'] = self.track_size(tracker['id'], tracker['start'], tracker['end']) |
|
71 | 1 | tracker['page_size'] = 500 |
|
72 | 1 | tracker['first_page'] = self.ts_track_after(tracker['id'], tracker['start'], tracker['end'], tracker['page_size']) |
|
73 | 1 | tracker['last_page'] = self.ts_track_before(tracker['id'], tracker['start'], tracker['end'], tracker['page_size']) |
|
74 | 1 | return trackers |
|
75 | |||
76 | 1 | def fetch_annotations_as_csv(self, tracker_id, start, end): |
|
77 | 1 | cursor = self.db.cursor() |
|
78 | 1 | sql_template = '''SELECT |
|
79 | device_info_serial, |
||
80 | date_time, |
||
81 | class_id |
||
82 | FROM "{table}" |
||
83 | WHERE |
||
84 | device_info_serial=%(tracker)s |
||
85 | AND |
||
86 | date_time BETWEEN %(start)s AND %(end)s |
||
87 | ORDER BY |
||
88 | device_info_serial, date_time |
||
89 | ''' |
||
90 | 1 | logger.debug('Fetching annotations for id:{0}, start:{1}, end:{2}'.format(tracker_id, start, end)) |
|
0 ignored issues
–
show
|
|||
91 | 1 | sql = sql_template.format(table=self.table.replace('.', '"."')) |
|
92 | 1 | cursor.execute(sql, {'tracker': tracker_id, |
|
93 | 'start': start, |
||
94 | 'end': end, |
||
95 | }) |
||
96 | 1 | annotations = ['device_info_serial,date_time,class_id'] |
|
97 | 1 | for a in cursor.fetchall(): |
|
98 | 1 | annotations.append(str(a['device_info_serial']) + ',' + a['date_time'].isoformat() + 'Z,' + str(a['class_id'])) |
|
99 | 1 | return "\n".join(annotations) + "\n" |
|
100 | |||
101 | 1 | def track_size(self, tracker_id, start, end): |
|
102 | 1 | cursor = self.db.cursor() |
|
103 | 1 | sql = '''SELECT |
|
104 | COUNT(*) AS count |
||
105 | FROM gps.ee_tracking_speed_limited |
||
106 | WHERE |
||
107 | device_info_serial=%(tracker)s |
||
108 | AND |
||
109 | date_time BETWEEN %(start)s AND %(end)s |
||
110 | ''' |
||
111 | 1 | cursor.execute(sql, {'tracker': tracker_id, |
|
112 | 'start': start, |
||
113 | 'end': end, |
||
114 | }) |
||
115 | 1 | result = cursor.fetchone() |
|
116 | 1 | return result['count'] |
|
117 | |||
118 | 1 | def ts_track_after(self, tracker_id, start, end, count): |
|
119 | 1 | cursor = self.db.cursor() |
|
120 | 1 | sql = '''SELECT |
|
121 | date_time |
||
122 | FROM gps.ee_tracking_speed_limited |
||
123 | WHERE |
||
124 | device_info_serial=%(tracker)s |
||
125 | AND |
||
126 | date_time BETWEEN %(start)s AND %(end)s |
||
127 | ORDER BY date_time |
||
128 | LIMIT 1 |
||
129 | OFFSET %(count)s |
||
130 | ''' |
||
131 | 1 | cursor.execute(sql, {'tracker': tracker_id, |
|
132 | 'start': start, |
||
133 | 'end': end, |
||
134 | 'count': count, |
||
135 | }) |
||
136 | |||
137 | 1 | result = cursor.fetchone() |
|
138 | 1 | if result: |
|
139 | 1 | return result['date_time'] |
|
140 | else: |
||
141 | 1 | return end |
|
142 | |||
143 | 1 | def ts_track_before(self, tracker_id, start, end, count): |
|
144 | 1 | cursor = self.db.cursor() |
|
145 | 1 | sql = '''SELECT |
|
146 | date_time |
||
147 | FROM gps.ee_tracking_speed_limited |
||
148 | WHERE |
||
149 | device_info_serial=%(tracker)s |
||
150 | AND |
||
151 | date_time BETWEEN %(start)s AND %(end)s |
||
152 | ORDER BY date_time DESC |
||
153 | LIMIT 1 |
||
154 | OFFSET %(count)s |
||
155 | ''' |
||
156 | 1 | cursor.execute(sql, {'tracker': tracker_id, |
|
157 | 'start': start, |
||
158 | 'end': end, |
||
159 | 'count': count, |
||
160 | }) |
||
161 | 1 | result = cursor.fetchone() |
|
162 | 1 | if result: |
|
163 | 1 | return result['date_time'] |
|
164 | else: |
||
165 | 1 | return start |
|
166 | |||
167 | 1 | @view_config(route_name='uploads.html', renderer='uploads.mako') |
|
168 | def uploads(self): |
||
169 | ''' |
||
170 | Page with form to select table with annotations. |
||
171 | |||
172 | Query parameters: |
||
173 | - table: Postgresql schema and table seperated by a dot, eg myschema.mytable |
||
174 | ''' |
||
175 | 1 | table = self.request.params.get('table', '') |
|
176 | 1 | if table == '': |
|
177 | 1 | return {'trackers': [], 'table': ''} |
|
178 | else: |
||
179 | 1 | self.table = table |
|
180 | |||
181 | 1 | trackers = self.fetch_trackers() |
|
182 | |||
183 | 1 | return {'trackers': trackers, 'table': self.table} |
|
184 | |||
185 | 1 | @view_config(route_name='annotations.html', renderer='upload.mako') |
|
186 | def upload(self): |
||
187 | ''' |
||
188 | Page with eEcology Annotation UI with annotations loaded from databases. |
||
189 | |||
190 | Query parameters: |
||
191 | - id: tracker id, |
||
192 | - start: start of time range in ISO8601 format |
||
193 | - end: end of time range in ISO8601 format |
||
194 | ''' |
||
195 | 1 | if not set(['id', 'start', 'end']).issubset(self.request.params.keys()): |
|
196 | 1 | return HTTPFound(self.request.route_path('uploads.html', _query={'table': self.table})) |
|
197 | 1 | tracker_id = int(self.request.params.get('id', 0)) |
|
198 | 1 | start = parse_date(self.request.params['start']).isoformat() |
|
199 | 1 | end = parse_date(self.request.params['end']).isoformat() |
|
200 | 1 | classes = self.fetch_classes() |
|
201 | 1 | annotations_url = self.request.route_path('annotations.csv', |
|
202 | table=self.table, |
||
203 | ) |
||
204 | 1 | return {'tracker_id': tracker_id, |
|
205 | 'start': start, |
||
206 | 'end': end, |
||
207 | 'classes': simplejson.dumps(classes), |
||
208 | 'annotations_url': annotations_url, |
||
209 | } |
||
210 | |||
211 | 1 | @view_config(route_name='annotations.csv') |
|
212 | def annotations_as_csv(self): |
||
213 | ''' |
||
214 | Returns annotations from a table with a tracker and time range selection. |
||
215 | |||
216 | Query parameters: |
||
217 | - id: tracker id, |
||
218 | - start: start of time range in ISO8601 format |
||
219 | - end: end of time range in ISO8601 format |
||
220 | ''' |
||
221 | 1 | tracker_id = self.request.params.get('id', 0) |
|
222 | 1 | start = parse_date(self.request.params['start']).isoformat() |
|
223 | 1 | end = parse_date(self.request.params['end']).isoformat() |
|
224 | 1 | csv = self.fetch_annotations_as_csv(tracker_id, start, end) |
|
225 | return Response(csv, content_type='text/csv') |
||
226 |
This check looks for lines that are too long. You can specify the maximum line length.