def get_connection(read_only=True, foreign_keys=True, integrity_check=True): """Connects to the SQLite database, returning a db `Connection` object""" logger.debug('Creating connection to `{}`.'.format(config.DATABASE)) if read_only: db = apsw.Connection(config.DATABASE, flags=apsw.SQLITE_OPEN_READONLY) else: db = apsw.Connection(config.DATABASE) cursor = db.cursor() # For backward compatibility. if not read_only: cursor.execute('''PRAGMA legacy_alter_table=ON''') # For integrity, security. if foreign_keys and not read_only: # logger.debug('Checking database foreign keys.') cursor.execute('''PRAGMA foreign_keys = ON''') cursor.execute('''PRAGMA defer_foreign_keys = ON''') rows = list(cursor.execute('''PRAGMA foreign_key_check''')) if rows: for row in rows: logger.debug('Foreign Key Error: {}'.format(row)) raise exceptions.DatabaseError('Foreign key check failed.') # So that writers don’t block readers. cursor.execute('''PRAGMA journal_mode = WAL''') # logger.debug('Foreign key check completed.') # Make case sensitive the `LIKE` operator. # For insensitive queries use 'UPPER(fieldname) LIKE value.upper()'' cursor.execute('''PRAGMA case_sensitive_like = ON''') if integrity_check: logger.debug('Checking database integrity.') integral = False for _ in range(10): # DUPE try: cursor.execute('''PRAGMA integrity_check''') rows = cursor.fetchall() if not (len(rows) == 1 and rows[0][0] == 'ok'): raise exceptions.DatabaseError('Integrity check failed.') integral = True break except DatabaseIntegrityError: time.sleep(1) continue if not integral: raise exceptions.DatabaseError( 'Could not perform integrity check.') # logger.debug('Integrity check completed.') db.setrowtrace(rowtracer) db.setexectrace(exectracer) return db
def last_message(db): """Return latest message from the db.""" cursor = db.cursor() messages = list(cursor.execute('''SELECT * FROM messages WHERE message_index = (SELECT MAX(message_index) from messages)''')) if messages: assert len(messages) == 1 last_message = messages[0] else: raise exceptions.DatabaseError('No messages found.') cursor.close() return last_message
def get_block_info(block_index): assert isinstance(block_index, int) cursor = self.db.cursor() cursor.execute('''SELECT * FROM blocks WHERE block_index = ?''', (block_index,)) blocks = list(cursor) if len(blocks) == 1: block = blocks[0] elif len(blocks) == 0: raise exceptions.DatabaseError('No blocks found.') else: assert False return block
def initialise(db): """Initialise data, create and populate the database.""" cursor = db.cursor() # Blocks cursor.execute('''CREATE TABLE IF NOT EXISTS blocks( block_index INTEGER UNIQUE, block_hash TEXT UNIQUE, block_time INTEGER, previous_block_hash TEXT UNIQUE, difficulty INTEGER, PRIMARY KEY (block_index, block_hash)) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS block_index_idx ON blocks (block_index) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS index_hash_idx ON blocks (block_index, block_hash) ''') # SQLite can’t do `ALTER TABLE IF COLUMN NOT EXISTS`. columns = [ column['name'] for column in cursor.execute('''PRAGMA table_info(blocks)''') ] if 'ledger_hash' not in columns: cursor.execute('''ALTER TABLE blocks ADD COLUMN ledger_hash TEXT''') if 'txlist_hash' not in columns: cursor.execute('''ALTER TABLE blocks ADD COLUMN txlist_hash TEXT''') if 'previous_block_hash' not in columns: cursor.execute( '''ALTER TABLE blocks ADD COLUMN previous_block_hash TEXT''') if 'difficulty' not in columns: cursor.execute('''ALTER TABLE blocks ADD COLUMN difficulty TEXT''') # Check that first block in DB is BLOCK_FIRST. cursor.execute('''SELECT * from blocks ORDER BY block_index''') blocks = list(cursor) if len(blocks): if blocks[0]['block_index'] != config.BLOCK_FIRST: raise exceptions.DatabaseError( 'First block in database is not block {}.'.format( config.BLOCK_FIRST)) # Transactions cursor.execute('''CREATE TABLE IF NOT EXISTS transactions( tx_index INTEGER UNIQUE, tx_hash TEXT UNIQUE, block_index INTEGER, block_hash TEXT, block_time INTEGER, source TEXT, destination TEXT, btc_amount INTEGER, fee INTEGER, data BLOB, supported BOOL DEFAULT 1, FOREIGN KEY (block_index, block_hash) REFERENCES blocks(block_index, block_hash), PRIMARY KEY (tx_index, tx_hash, block_index)) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS block_index_idx ON transactions (block_index) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS tx_index_idx ON transactions (tx_index) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS tx_hash_idx ON transactions (tx_hash) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS index_index_idx ON transactions (block_index, tx_index) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS index_hash_index_idx ON transactions (tx_index, tx_hash, block_index) ''') # Purge database of blocks, transactions from before BLOCK_FIRST. cursor.execute('''DELETE FROM blocks WHERE block_index < ?''', (config.BLOCK_FIRST, )) cursor.execute('''DELETE FROM transactions WHERE block_index < ?''', (config.BLOCK_FIRST, )) # (Valid) debits cursor.execute('''CREATE TABLE IF NOT EXISTS debits( block_index INTEGER, address TEXT, asset TEXT, quantity INTEGER, action TEXT, event TEXT, FOREIGN KEY (block_index) REFERENCES blocks(block_index)) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS address_idx ON debits (address) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS asset_idx ON debits (asset) ''') # (Valid) credits cursor.execute('''CREATE TABLE IF NOT EXISTS credits( block_index INTEGER, address TEXT, asset TEXT, quantity INTEGER, calling_function TEXT, event TEXT, FOREIGN KEY (block_index) REFERENCES blocks(block_index)) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS address_idx ON credits (address) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS asset_idx ON credits (asset) ''') # Balances cursor.execute('''CREATE TABLE IF NOT EXISTS balances( address TEXT, asset TEXT, quantity INTEGER) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS address_asset_idx ON balances (address, asset) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS address_idx ON balances (address) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS asset_idx ON balances (asset) ''') # Assets # TODO: Store more asset info here?! cursor.execute('''CREATE TABLE IF NOT EXISTS assets( asset_id TEXT UNIQUE, asset_name TEXT UNIQUE, block_index INTEGER) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS name_idx ON assets (asset_name) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS id_idx ON assets (asset_id) ''') cursor.execute('''SELECT * FROM assets WHERE asset_name = ?''', ('BTC', )) if not list(cursor): cursor.execute('''INSERT INTO assets VALUES (?,?,?)''', ('0', 'BTC', None)) cursor.execute('''INSERT INTO assets VALUES (?,?,?)''', ('1', 'XCP', None)) # Consolidated send.initialise(db) destroy.initialise(db) order.initialise(db) btcpay.initialise(db) issuance.initialise(db) broadcast.initialise(db) bet.initialise(db) publish.initialise(db) execute.initialise(db) dividend.initialise(db) burn.initialise(db) cancel.initialise(db) rps.initialise(db) rpsresolve.initialise(db) # Messages cursor.execute('''CREATE TABLE IF NOT EXISTS messages( message_index INTEGER PRIMARY KEY, block_index INTEGER, command TEXT, category TEXT, bindings TEXT, timestamp INTEGER) ''') # TODO: FOREIGN KEY (block_index) REFERENCES blocks(block_index) DEFERRABLE INITIALLY DEFERRED) cursor.execute('''CREATE INDEX IF NOT EXISTS block_index_idx ON messages (block_index) ''') cursor.execute('''CREATE INDEX IF NOT EXISTS block_index_message_index_idx ON messages (block_index, message_index) ''') # Mempool messages # NOTE: `status`, 'block_index` are removed from bindings. cursor.execute('''DROP TABLE IF EXISTS mempool''') cursor.execute('''CREATE TABLE mempool( tx_hash TEXT, command TEXT, category TEXT, bindings TEXT, timestamp INTEGER) ''') cursor.close()