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: