search

glidernet / ogn-python

A gateway with built-in database for the Open Glider Network (OGN)
http://glidernet.org
GNU Affero General Public License v3.0
17 stars 4 forks source link

Add sqlite-backend support for tasks update_receivers and compute_takeoff_and_landing #28

Closed kerel-fs closed 8 years ago

kerel-fs commented 8 years ago 
./manage.py logbook.show test 2.0 20.0 100

--- Logbook (test) ---
Traceback (most recent call last):
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near "(": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 13, in <module>
    manager.main()
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/manager/__init__.py", line 333, in main
    command.parse(args.all[1:])
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/manager/__init__.py", line 130, in parse
    r = self(*args, **kwargs)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/manager/__init__.py", line 62, in __call__
    return self.run(*args, **kwargs)
  File "/home/kerel/ogn/ogn-python/ogn/commands/logbook.py", line 151, in show
    for [reftime, address, takeoff, takeoff_track, landing, landing_track, duration, registration, aircraft] in logbook_query.all():
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2399, in all
    return list(self)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2516, in __iter__
    return self._execute_and_instances(context)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2531, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/kerel/ogn/ogn-python/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "(": syntax error [SQL: 'SELECT anon_1.reftime AS anon_1_reftime, anon_1.address AS anon_1_address, anon_1.takeoff AS anon_1_takeoff, anon_1.takeoff_track AS anon_1_takeoff_track, anon_1.landing AS anon_1_landing, anon_1.landing_track AS anon_1_landing_track, anon_1.duration AS anon_1_duration, device.registration AS device_registration, device.aircraft AS device_aircraft \nFROM (SELECT anon_2.reftime AS reftime, anon_2.address AS address, anon_2.takeoff AS takeoff, anon_2.takeoff_track AS takeoff_track, anon_2.landing AS landing, anon_2.landing_track AS landing_track, anon_2.duration AS duration \nFROM (SELECT anon_3.timestamp AS reftime, anon_3.address AS address, anon_3.timestamp AS takeoff, anon_3.track AS takeoff_track, anon_3.timestamp_next AS landing, anon_3.track_next AS landing_track, anon_3.timestamp_next - anon_3.timestamp AS duration \nFROM (SELECT takeoff_landing.address AS address, lag(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_prev, lead(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_next, takeoff_landing.timestamp AS timestamp, lag(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_prev, lead(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_next, takeoff_landing.track AS track, lag(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_prev, lead(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_next, takeoff_landing.is_takeoff AS is_takeoff, lag(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_prev, lead(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_next \nFROM takeoff_landing \nWHERE takeoff_landing.latitude BETWEEN ? AND ? AND takeoff_landing.longitude BETWEEN ? AND ? AND takeoff_landing.altitude < ?) AS anon_3 \nWHERE anon_3.is_takeoff = 1 AND anon_3.is_takeoff_next = 0 AND anon_3.address = anon_3.address_next AND anon_3.timestamp_next - anon_3.timestamp < ? UNION SELECT anon_3.timestamp AS reftime, anon_3.address AS address, anon_3.timestamp AS takeoff, anon_3.track AS takeoff_track, NULL AS landing, NULL AS landing_track, NULL AS duration \nFROM (SELECT takeoff_landing.address AS address, lag(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_prev, lead(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_next, takeoff_landing.timestamp AS timestamp, lag(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_prev, lead(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_next, takeoff_landing.track AS track, lag(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_prev, lead(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_next, takeoff_landing.is_takeoff AS is_takeoff, lag(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_prev, lead(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_next \nFROM takeoff_landing \nWHERE takeoff_landing.latitude BETWEEN ? AND ? AND takeoff_landing.longitude BETWEEN ? AND ? AND takeoff_landing.altitude < ?) AS anon_3 \nWHERE anon_3.is_takeoff = 1 AND anon_3.is_takeoff_next = 0 AND anon_3.address = anon_3.address_next AND anon_3.timestamp_next - anon_3.timestamp >= ? UNION SELECT anon_3.timestamp_next AS reftime, anon_3.address AS address, NULL AS takeoff, NULL AS takeoff_track, anon_3.timestamp_next AS landing, anon_3.track_next AS landing_track, NULL AS duration \nFROM (SELECT takeoff_landing.address AS address, lag(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_prev, lead(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_next, takeoff_landing.timestamp AS timestamp, lag(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_prev, lead(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_next, takeoff_landing.track AS track, lag(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_prev, lead(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_next, takeoff_landing.is_takeoff AS is_takeoff, lag(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_prev, lead(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_next \nFROM takeoff_landing \nWHERE takeoff_landing.latitude BETWEEN ? AND ? AND takeoff_landing.longitude BETWEEN ? AND ? AND takeoff_landing.altitude < ?) AS anon_3 \nWHERE anon_3.is_takeoff = 1 AND anon_3.is_takeoff_next = 0 AND anon_3.address = anon_3.address_next AND anon_3.timestamp_next - anon_3.timestamp >= ? UNION SELECT anon_3.timestamp AS reftime, anon_3.address AS address, NULL AS takeoff, NULL AS takeoff_track, anon_3.timestamp AS landing, anon_3.track_next AS landing_track, NULL AS duration \nFROM (SELECT takeoff_landing.address AS address, lag(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_prev, lead(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_next, takeoff_landing.timestamp AS timestamp, lag(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_prev, lead(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_next, takeoff_landing.track AS track, lag(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_prev, lead(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_next, takeoff_landing.is_takeoff AS is_takeoff, lag(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_prev, lead(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_next \nFROM takeoff_landing \nWHERE takeoff_landing.latitude BETWEEN ? AND ? AND takeoff_landing.longitude BETWEEN ? AND ? AND takeoff_landing.altitude < ?) AS anon_3 \nWHERE anon_3.is_takeoff = 0 AND (anon_3.address != anon_3.address_prev OR anon_3.is_takeoff_prev = 0) UNION SELECT anon_3.timestamp AS reftime, anon_3.address AS address, anon_3.timestamp AS takeoff, anon_3.track AS takeoff_track, NULL AS landing, NULL AS landing_track, NULL AS duration \nFROM (SELECT takeoff_landing.address AS address, lag(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_prev, lead(takeoff_landing.address) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS address_next, takeoff_landing.timestamp AS timestamp, lag(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_prev, lead(takeoff_landing.timestamp) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS timestamp_next, takeoff_landing.track AS track, lag(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_prev, lead(takeoff_landing.track) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS track_next, takeoff_landing.is_takeoff AS is_takeoff, lag(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_prev, lead(takeoff_landing.is_takeoff) OVER (ORDER BY date(takeoff_landing.timestamp), takeoff_landing.address, takeoff_landing.timestamp) AS is_takeoff_next \nFROM takeoff_landing \nWHERE takeoff_landing.latitude BETWEEN ? AND ? AND takeoff_landing.longitude BETWEEN ? AND ? AND takeoff_landing.altitude < ?) AS anon_3 \nWHERE anon_3.is_takeoff = 1 AND (anon_3.address != anon_3.address_next OR anon_3.is_takeoff_next = 1)) AS anon_2) AS anon_1 LEFT OUTER JOIN device ON anon_1.address = device.address ORDER BY anon_1.reftime'] [parameters: (1.85, 2.15, 19.85, 20.15, 300.0, '1970-01-02 00:00:00.000000', 1.85, 2.15, 19.85, 20.15, 300.0, '1970-01-02 00:00:00.000000', 1.85, 2.15, 19.85, 20.15, 300.0, '1970-01-02 00:00:00.000000', 1.85, 2.15, 19.85, 20.15, 300.0, 1.85, 2.15, 19.85, 20.15, 300.0)]

and

[2016-01-12 18:05:06,036: ERROR/MainProcess] Task ogn.collect.logbook.compute_takeoff_and_landing[d8bc8ff9-08bc-4f21-b81e-f8a0cbf0594a] raised unexpected: OperationalError('(sqlite3.OperationalError) near "(": syntax error',)
Traceback (most recent call last):
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/celery/app/trace.py", line 240, in trace_task
    R = retval = fun(*args, **kwargs)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/celery/app/trace.py", line 438, in __protected_call__
    return self.run(*args, **kwargs)
  File "/home/kerel/tmp/test2/ogn-python/ogn/collect/logbook.py", line 86, in compute_takeoff_and_landing
    result = app.session.execute(ins)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 1023, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/kerel/tmp/test/env/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "(": syntax error [SQL: 'INSERT INTO takeoff_landing (address, timestamp, latitude, longitude, track, ground_speed, altitude, is_takeoff) SELECT anon_1.address AS anon_1_address, anon_1.timestamp AS anon_1_timestamp, anon_1.latitude AS anon_1_latitude, anon_1.longitude AS anon_1_longitude, anon_1.track AS anon_1_track, anon_1.ground_speed AS anon_1_ground_speed, anon_1.altitude AS anon_1_altitude, CASE WHEN (anon_1.ground_speed > ?) THEN ? WHEN (anon_1.ground_speed < ?) THEN ? END AS is_takeoff \nFROM (SELECT aircraft_beacon.address AS address, lag(aircraft_beacon.address) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS address_prev, lead(aircraft_beacon.address) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS address_next, aircraft_beacon.timestamp AS timestamp, lag(aircraft_beacon.timestamp) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS timestamp_prev, lead(aircraft_beacon.timestamp) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS timestamp_next, aircraft_beacon.latitude AS latitude, lag(aircraft_beacon.latitude) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS latitude_prev, lead(aircraft_beacon.latitude) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS latitude_next, aircraft_beacon.longitude AS longitude, lag(aircraft_beacon.longitude) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS longitude_prev, lead(aircraft_beacon.longitude) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS longitude_next, aircraft_beacon.ground_speed AS ground_speed, aircraft_beacon.track AS track, lag(aircraft_beacon.track) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS track_prev, lead(aircraft_beacon.track) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS track_next, lag(aircraft_beacon.ground_speed) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS ground_speed_prev, lead(aircraft_beacon.ground_speed) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS ground_speed_next, aircraft_beacon.altitude AS altitude, lag(aircraft_beacon.altitude) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS altitude_prev, lead(aircraft_beacon.altitude) OVER (ORDER BY aircraft_beacon.address, aircraft_beacon.timestamp) AS altitude_next \nFROM aircraft_beacon \nWHERE aircraft_beacon.timestamp > ? ORDER BY date(aircraft_beacon.timestamp), aircraft_beacon.address, aircraft_beacon.timestamp) AS anon_1 \nWHERE anon_1.address = anon_1.address_prev AND (anon_1.ground_speed_prev < ? AND anon_1.ground_speed > ? AND anon_1.ground_speed_next > ? OR anon_1.ground_speed_prev > ? AND anon_1.ground_speed < ? AND anon_1.ground_speed_next < ?) ORDER BY date(anon_1.timestamp), anon_1.timestamp'] [parameters: (30, 1, 30, 0, '2015-01-01 00:00:00.000000', 30, 30, 30, 30, 30, 30)

@Meisterschueler any ideas?

Meisterschueler commented 8 years ago

I cannot reproduce it, but I am using postgresql. Where does the '\n' in the sql statement comes from? I will have a closer look.

kerel-fs commented 8 years ago

I can confirm that there are no issues with a postresql- backend and found the problem: SQLite lacks support of analytic functions (LAG here), thus both commands don't work with a sqlite-backend.

We should state that logbook requires a postgresql- backend in the README but continue to use sqlite preconfigured because of its simplicity.

Or is it easily possible to rewrite those queries to support sqlite?

Meisterschueler commented 8 years ago

Lag and lead make the calculation very fast. But I will look after a solution so we keep SQLite compatibility.

kerel-fs commented 8 years ago

related: ae3b08722f0acd761148138ebc0c7ba773a06d07 README: Add note about special backend requirements for some tasks

kerel-fs commented 8 years ago

Added a recommendation for postgresql-backend to README.

Meisterschueler commented 8 years ago

Since we need PostGIS there is no chance anymore for SQLite