commit 01893f74ccd519aa45e8a1ebcf7c3cbe3589db8f Author: Karsten Loesing <karsten.loesing@gmx.net> Date: Sun Sep 23 19:58:23 2012 +0200 Find a better hack for OFFSET without LIMIT. SQLite doesn't understand OFFSET without LIMIT, which is why the current code used a local counter to request TOTAL_ROWS-offset_value as the LIMIT. However, that code is not transaction-safe. A better solution is to pass -1 as the LIMIT, which has the same effect. --- pyonionoo/database.py | 12 ++---------- 1 files changed, 2 insertions(+), 10 deletions(-) diff --git a/pyonionoo/database.py b/pyonionoo/database.py index a7d909b..a553629 100644 --- a/pyonionoo/database.py +++ b/pyonionoo/database.py @@ -30,12 +30,6 @@ DB_UPDATE_INTERVAL = 60 # The timer object used for updating the database. FRESHEN_TIMER = None -# Total number of rows in summary db. This can also be calculated during -# run time by doing "SELECT COUNT(*) FROM summary" but this will take -# time to execute. It seems like a better idea to count the number of rows -# during insertion. -TOTAL_ROWS = 0 - # Database schemas. # Summary database: in conjunction with addresses and flags, holds the # information in the summary document. addresses and flags are lists of @@ -122,7 +116,7 @@ def update_databases(summary_file=None): @type summary_file: string @param summary_file: full path to the summary file """ - global DB_CREATION_TIME, TABLE_ROWS + global DB_CREATION_TIME if DB_CREATION_TIME >= os.stat(summary_file).st_mtime: return @@ -137,7 +131,6 @@ def update_databases(summary_file=None): logging.info("Deleting data from databases") CURSOR = conn.cursor() CURSOR.execute('DELETE FROM %s' % summary_tbl_name) - TABLE_ROWS = 0 # Create the summary database. We could accumulate all the router tuples # and then insert them with an executemany(...) in one go, except that @@ -176,7 +169,6 @@ def update_databases(summary_file=None): # or might allow users to optimize in this way. CURSOR.execute(summary_insert_stmt, router_tuple) id_num = CURSOR.lastrowid - TABLE_ROWS += 1 conn.commit() logging.info("Table updated") @@ -228,7 +220,7 @@ def query_summary_tbl(running_filter=None, type_filter=None, hex_fingerprint_fil if not limit_value: # sqlite doesn't support OFFSET without a LIMIT clause, this is # a hack to get around that. - limit_clause = 'LIMIT %s' % (TOTAL_ROWS-int(offset_value)) + limit_clause = 'LIMIT -1' offset_clause = 'OFFSET %s' % offset_value cursor.execute('SELECT %s FROM summary %s %s %s %s' % (','.join(fields), where_clause, order_clause, limit_clause,