def _process_command_item(self, command_item):
query, result, signal, trace, transaction = command_item
with Timer("SQL Timing", verbose=self.debug):
if transaction is None:
# THIS IS A TRANSACTIONLESS QUERY, DELAY IT IF THERE IS A CURRENT TRANSACTION
if self.transaction_stack:
with self.locker:
if self.too_long is None:
self.too_long = Till(
seconds=TOO_LONG_TO_HOLD_TRANSACTION)
self.too_long.then(
self.show_transactions_blocked_warning)
self.delayed_queries.append(command_item)
return
elif self.transaction_stack and self.transaction_stack[-1] not in [
transaction,
transaction.parent,
]:
# THIS TRANSACTION IS NOT THE CURRENT TRANSACTION, DELAY IT
with self.locker:
if self.too_long is None:
self.too_long = Till(
seconds=TOO_LONG_TO_HOLD_TRANSACTION)
self.too_long.then(
self.show_transactions_blocked_warning)
self.delayed_transactions.append(command_item)
return
else:
# ENSURE THE CURRENT TRANSACTION IS UP TO DATE FOR THIS query
if not self.transaction_stack:
# sqlite3 ALLOWS ONLY ONE TRANSACTION AT A TIME
self.debug and Log.note(FORMAT_COMMAND, command=BEGIN)
self.db.execute(BEGIN)
self.transaction_stack.append(transaction)
elif transaction is not self.transaction_stack[-1]:
self.transaction_stack.append(transaction)
elif transaction.exception and query is not ROLLBACK:
result.exception = Except(
context=ERROR,
template=
"Not allowed to continue using a transaction that failed",
cause=transaction.exception,
trace=trace,
)
signal.release()
return
try:
transaction.do_all()
except Exception as e:
# DEAL WITH ERRORS IN QUEUED COMMANDS
# WE WILL UNWRAP THE OUTER EXCEPTION TO GET THE CAUSE
err = Except(
context=ERROR,
template="Bad call to Sqlite3 while " + FORMAT_COMMAND,
params={"command": e.params.current.command},
cause=e.cause,
trace=e.params.current.trace,
)
transaction.exception = result.exception = err
if query in [COMMIT, ROLLBACK]:
self._close_transaction(
CommandItem(ROLLBACK, result, signal, trace,
transaction))
signal.release()
return
try:
# DEAL WITH END-OF-TRANSACTION MESSAGES
if query in [COMMIT, ROLLBACK]:
self._close_transaction(command_item)
return
# EXECUTE QUERY
self.last_command_item = command_item
self.debug and Log.note(FORMAT_COMMAND, command=query)
curr = self.db.execute(text(query))
result.meta.format = "table"
result.header = ([d[0] for d in curr.description]
if curr.description else None)
result.data = curr.fetchall()
if self.debug and result.data:
csv = convert.table2csv(list(result.data))
Log.note("Result:\n{{data|limit(100)|indent}}", data=csv)
except Exception as e:
e = Except.wrap(e)
err = Except(
context=ERROR,
template="Bad call to Sqlite while " + FORMAT_COMMAND,
params={"command": query},
trace=trace,
cause=e,
)
result.exception = err
if transaction:
transaction.exception = err
finally:
signal.release()
def _worker(self, please_stop):
global _load_extension_warning_sent
if DEBUG:
Log.note("Sqlite version {{version}}",
version=sqlite3.sqlite_version)
if Sqlite.canonical:
self.db = Sqlite.canonical
else:
self.db = sqlite3.connect(coalesce(self.filename, ':memory:'))
library_loc = File.new_instance(sys.modules[__name__].__file__,
"../..")
full_path = File.new_instance(
library_loc, "vendor/sqlite/libsqlitefunctions.so").abspath
try:
trace = extract_stack(0)[0]
if self.upgrade:
if os.name == 'nt':
file = File.new_instance(
trace["file"],
"../../vendor/sqlite/libsqlitefunctions.so")
else:
file = File.new_instance(
trace["file"],
"../../vendor/sqlite/libsqlitefunctions")
full_path = file.abspath
self.db.enable_load_extension(True)
self.db.execute("SELECT load_extension(" +
self.quote_value(full_path) + ")")
except Exception as e:
if not _load_extension_warning_sent:
_load_extension_warning_sent = True
Log.warning(
"Could not load {{file}}}, doing without. (no SQRT for you!)",
file=full_path,
cause=e)
try:
while not please_stop:
command, result, signal, trace = self.queue.pop(
till=please_stop)
if DEBUG_INSERT and command.strip().lower().startswith(
"insert"):
Log.note("Running command\n{{command|indent}}",
command=command)
if DEBUG and not command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|indent}}",
command=command)
with Timer("Run command", debug=DEBUG):
if signal is not None:
try:
curr = self.db.execute(command)
self.db.commit()
result.meta.format = "table"
result.header = [d[0] for d in curr.description
] if curr.description else None
result.data = curr.fetchall()
if DEBUG and result.data:
text = convert.table2csv(list(result.data))
Log.note("Result:\n{{data}}", data=text)
except Exception as e:
e = Except.wrap(e)
result.exception = Except(
ERROR,
"Problem with\n{{command|indent}}",
command=command,
cause=e)
finally:
signal.go()
else:
try:
self.db.execute(command)
self.db.commit()
except Exception as e:
e = Except.wrap(e)
e.cause = Except(type=ERROR,
template="Bad call to Sqlite",
trace=trace)
Log.warning("Failure to execute", cause=e)
except Exception as e:
if not please_stop:
Log.error("Problem with sql thread", e)
finally:
if DEBUG:
Log.note("Database is closed")
self.db.commit()
self.db.close()
def _worker(self, please_stop):
global _load_extension_warning_sent
if DEBUG:
Log.note("Sqlite version {{version}}", version=sqlite3.sqlite_version)
if Sqlite.canonical:
self.db = Sqlite.canonical
else:
self.db = sqlite3.connect(coalesce(self.filename, ':memory:'))
library_loc = File.new_instance(sys.modules[__name__].__file__, "../..")
full_path = File.new_instance(library_loc, "vendor/sqlite/libsqlitefunctions.so").abspath
try:
trace = extract_stack(0)[0]
if self.upgrade:
if os.name == 'nt':
file = File.new_instance(trace["file"], "../../vendor/sqlite/libsqlitefunctions.so")
else:
file = File.new_instance(trace["file"], "../../vendor/sqlite/libsqlitefunctions")
full_path = file.abspath
self.db.enable_load_extension(True)
self.db.execute("SELECT load_extension(" + self.quote_value(full_path) + ")")
except Exception as e:
if not _load_extension_warning_sent:
_load_extension_warning_sent = True
Log.warning("Could not load {{file}}}, doing without. (no SQRT for you!)", file=full_path, cause=e)
try:
while not please_stop:
command, result, signal, trace = self.queue.pop(till=please_stop)
if DEBUG_INSERT and command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|indent}}", command=command)
if DEBUG and not command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|indent}}", command=command)
with Timer("Run command", debug=DEBUG):
if signal is not None:
try:
curr = self.db.execute(command)
self.db.commit()
result.meta.format = "table"
result.header = [d[0] for d in curr.description] if curr.description else None
result.data = curr.fetchall()
if DEBUG and result.data:
text = convert.table2csv(list(result.data))
Log.note("Result:\n{{data}}", data=text)
except Exception as e:
e = Except.wrap(e)
result.exception = Except(ERROR, "Problem with\n{{command|indent}}", command=command, cause=e)
finally:
signal.go()
else:
try:
self.db.execute(command)
self.db.commit()
except Exception as e:
e = Except.wrap(e)
e.cause = Except(
type=ERROR,
template="Bad call to Sqlite",
trace=trace
)
Log.warning("Failure to execute", cause=e)
except Exception as e:
if not please_stop:
Log.error("Problem with sql thread", e)
finally:
if DEBUG:
Log.note("Database is closed")
self.db.commit()
self.db.close()
def _worker(self, please_stop):
global _load_extension_warning_sent
try:
if DEBUG:
Log.note("Sqlite version {{version}}", version=sqlite3.sqlite_version)
try:
if Sqlite.canonical:
self.db = Sqlite.canonical
else:
self.db = sqlite3.connect(coalesce(self.filename, ':memory:'), check_same_thread=False)
except Exception as e:
Log.error("could not open file {{filename}}", filename=self.filename)
if self.settings.load_functions:
self._load_functions()
while not please_stop:
quad = self.queue.pop(till=please_stop)
if quad is None:
break
command, result, signal, trace = quad
show_timing = False
if DEBUG_INSERT and command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|limit(100)|indent}}", command=command)
show_timing = True
if DEBUG and not command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|limit(100)|indent}}", command=command)
show_timing = True
with Timer("SQL Timing", silent=not show_timing):
if command is COMMIT:
self.db.commit()
signal.release()
elif signal is not None:
try:
curr = self.db.execute(command)
if result is not None:
result.meta.format = "table"
result.header = [d[0] for d in curr.description] if curr.description else None
result.data = curr.fetchall()
if DEBUG and result.data:
text = convert.table2csv(list(result.data))
Log.note("Result:\n{{data}}", data=text)
except Exception as e:
e = Except.wrap(e)
e.cause = Except(
type=ERROR,
template="Bad call to Sqlite",
trace=trace
)
if result is None:
Log.error("Problem with\n{{command|indent}}", command=command, cause=e)
else:
result.exception = Except(ERROR, "Problem with\n{{command|indent}}", command=command, cause=e)
finally:
if isinstance(signal, Signal):
signal.go()
else:
signal.release()
else:
try:
self.db.execute(command)
except Exception as e:
e = Except.wrap(e)
e.cause = Except(
type=ERROR,
template="Bad call to Sqlite",
trace=trace
)
Log.warning("Failure to execute", cause=e)
except Exception as e:
if not please_stop:
Log.warning("Problem with sql thread", cause=e)
finally:
self.closed = True
if DEBUG:
Log.note("Database is closed")
self.db.close()
class Sqlite(DB):
"""
Allows multi-threaded access
Loads extension functions (like SQRT)
"""
canonical = None
def __init__(self, filename=None, db=None):
"""
:param db: Optional, wrap a sqlite db in a thread
:return: Multithread save database
"""
if not _upgraded:
_upgrade()
self.filename = filename
self.db = db
self.queue = Queue("sql commands") # HOLD (command, result, signal) PAIRS
self.worker = Thread.run("sqlite db thread", self._worker)
self.get_trace = DEBUG
def _enhancements(self):
def regex(pattern, value):
return 1 if re.match(pattern+"$", value) else 0
con = self.db.create_function("regex", 2, regex)
class Percentile(object):
def __init__(self, percentile):
self.percentile=percentile
self.acc=[]
def step(self, value):
self.acc.append(value)
def finalize(self):
return percentile(self.acc, self.percentile)
con.create_aggregate("percentile", 2, Percentile)
def execute(self, command):
"""
COMMANDS WILL BE EXECUTED IN THE ORDER THEY ARE GIVEN
BUT CAN INTERLEAVE WITH OTHER TREAD COMMANDS
:param command: COMMAND FOR SQLITE
:return: None
"""
if DEBUG: # EXECUTE IMMEDIATELY FOR BETTER STACK TRACE
return self.query(command)
if self.get_trace:
trace = extract_stack(1)
else:
trace = None
self.queue.add((command, None, None, trace))
def query(self, command):
"""
WILL BLOCK CALLING THREAD UNTIL THE command IS COMPLETED
:param command: COMMAND FOR SQLITE
:return: list OF RESULTS
"""
if not self.worker:
self.worker = Thread.run("sqlite db thread", self._worker)
signal = Signal()
result = Data()
self.queue.add((command, result, signal, None))
signal.wait()
if result.exception:
Log.error("Problem with Sqlite call", cause=result.exception)
return result
def _worker(self, please_stop):
global _load_extension_warning_sent
if DEBUG:
Log.note("Sqlite version {{version}}", version=sqlite3.sqlite_version)
if Sqlite.canonical:
self.db = Sqlite.canonical
else:
self.db = sqlite3.connect(coalesce(self.filename, ':memory:'))
library_loc = File.new_instance(sys.modules[__name__].__file__, "../..")
full_path = File.new_instance(library_loc, "vendor/sqlite/libsqlitefunctions.so").abspath
try:
trace = extract_stack(0)[0]
file = File.new_instance(trace["file"], "../../vendor/sqlite/libsqlitefunctions.so")
full_path = file.abspath
self.db.enable_load_extension(True)
self.db.execute("SELECT load_extension(" + self.quote_value(full_path) + ")")
except Exception, e:
if not _load_extension_warning_sent:
_load_extension_warning_sent = True
Log.warning("Could not load {{file}}}, doing without. (no SQRT for you!)", file=full_path, cause=e)
try:
while not please_stop:
if DEBUG:
Log.note("begin pop")
command, result, signal, trace = self.queue.pop(till=please_stop)
if DEBUG:
Log.note("done pop")
if DEBUG_INSERT and command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|indent}}", command=command)
if DEBUG and not command.strip().lower().startswith("insert"):
Log.note("Running command\n{{command|indent}}", command=command)
with Timer("Run command", debug=DEBUG):
if signal is not None:
try:
curr = self.db.execute(command)
self.db.commit()
result.meta.format = "table"
result.header = [d[0] for d in curr.description] if curr.description else None
result.data = curr.fetchall()
if DEBUG and result.data:
text = convert.table2csv(list(result.data))
Log.note("Result:\n{{data}}", data=text)
except Exception, e:
e = Except.wrap(e)
result.exception = Except(ERROR, "Problem with\n{{command|indent}}", command=command, cause=e)
finally:
