Issues (38)

annotation/uploads.py (15 issues)

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__)
0 ignored issues
show
Coding Style Naming introduced by Stefan Verhoeven
The name logger does not conform to the constant naming conventions ((([A-Z_][A-Z0-9_]*)|(__.*__))$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
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)
0 ignored issues
show
This line is too long as per the coding-style (106/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
40
        );
41
    """
42
43 1
    def __init__(self, request):
44 1
        self.request = request
45 1
        self.db = request.db
0 ignored issues
show
Coding Style Naming introduced by Stefan Verhoeven
The name db does not conform to the attribute naming conventions ([a-z_][a-z0-9_]{2,30}$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
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"
0 ignored issues
show
This line is too long as per the coding-style (219/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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'])
0 ignored issues
show
This line is too long as per the coding-style (94/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
71 1
            tracker['page_size'] = 500
72 1
            tracker['first_page'] = self.ts_track_after(tracker['id'], tracker['start'], tracker['end'], tracker['page_size'])
0 ignored issues
show
This line is too long as per the coding-style (126/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
73 1
            tracker['last_page'] = self.ts_track_before(tracker['id'], tracker['start'], tracker['end'], tracker['page_size'])
0 ignored issues
show
This line is too long as per the coding-style (126/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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
This line is too long as per the coding-style (106/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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():
0 ignored issues
show
Coding Style Naming introduced by Stefan Verhoeven
The name a does not conform to the variable naming conventions ([a-z_][a-z0-9_]{2,30}$).

This check looks for invalid names for a range of different identifiers.

You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.

If your project includes a Pylint configuration file, the settings contained in that file take precedence.

To find out more about Pylint, please refer to their site.

Loading history...
98 1
            annotations.append(str(a['device_info_serial']) + ',' + a['date_time'].isoformat() + 'Z,' + str(a['class_id']))
0 ignored issues
show
This line is too long as per the coding-style (123/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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
0 ignored issues
show
This line is too long as per the coding-style (84/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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.
0 ignored issues
show
This line is too long as per the coding-style (80/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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()):
0 ignored issues
show
This line is too long as per the coding-style (80/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
196 1
            return HTTPFound(self.request.route_path('uploads.html', _query={'table': self.table}))
0 ignored issues
show
This line is too long as per the coding-style (99/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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.
0 ignored issues
show
This line is too long as per the coding-style (81/79).

This check looks for lines that are too long. You can specify the maximum line length.

Loading history...
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