commit 01893f74ccd519aa45e8a1ebcf7c3cbe3589db8f
Author: Karsten Loesing <karsten.loesing(a)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,