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()
