#!/usr/bin/env python
import os, shutil, apsw, distutils.dir_util
def extract(case, userdata):
print("--> Extracting SMS/MMS messages\n\n")
extractdir = os.path.join(case, "extracted data")
if not os.path.exists(extractdir):
os.makedirs(extractdir)
extractdir = os.path.join(extractdir, "mms-sms")
if not os.path.exists(extractdir):
os.makedirs(extractdir)
dbdir = os.path.join(extractdir, "db")
if not os.path.exists(dbdir):
os.makedirs(dbdir)
db_src = os.path.join(userdata, "data", "com.android.providers.telephony",
"databases", "mmssms.db")
db_dest = os.path.join(dbdir, "mmssms.db")
shutil.copyfile(db_src, db_dest)
dbconnection = apsw.Connection(db_dest)
filepath = os.path.join(extractdir, "Messages.txt")
fileopen = open(filepath, "w", encoding='utf8')
dbshell = apsw.Shell(stdout=fileopen, db=dbconnection)
dbshell.process_command(".header on")
dbshell.process_sql("select * from sms")
fileopen.close()
def rmlid(lid=0):
'''Remove a record from the anime cache.'''
handle = apsw.Connection(db)
handle.cursor().execute("delete from mylist where lid=?", (lid, ))
doclose(handle)
import apsw
c = apsw.Connection("../data/imdb.sqlite")
movie_data = c.cursor().execute("select * from movie_data").fetchall()
c.close()
del c
del apsw
X = [x.split(',') for (x, y) in movie_data]
y = [y for (x, y) in movie_data]
del movie_data
from sklearn.pipeline import Pipeline
from xgboost import XGBRegressor
from sklearn.feature_extraction import FeatureHasher
from sklearn.neural_network import BernoulliRBM
thePipe = Pipeline([("hash", FeatureHasher(input_type="string")),
('RBM', BernoulliRBM()), ('XGB', XGBRegressor())])
from sklearn.grid_search import GridSearchCV
from sklearn.metrics import mean_squared_error, make_scorer
paramGrid = {
'XGB__max_depth': [3],
'XGB__n_estimators': [100],
'RBM__n_components': [20],
"hash__n_features": [100000]
}
theScorer = make_scorer(mean_squared_error, greater_is_better=False)
clf = GridSearchCV(thePipe,
from CustomSQLFeatureExtractor import *
import numpy as np
import apsw
import datetime
from sklearn.pipeline import Pipeline
from sklearn.linear_model import ElasticNetCV
from sklearn.metrics import mean_squared_error, make_scorer, mean_absolute_error, median_absolute_error
from sklearn.cross_validation import train_test_split
# get and split the data
homefolder = '../data'
# read text of query from disk into memory
query = open(homefolder + '/transformData.sql', "r").read()
c = apsw.Connection(homefolder + '/imdb.sqlite').cursor()
all_data = c.execute(
'SELECT movie_id, rating FROM movies ORDER BY movie_id').fetchall()
train_data, test_data = train_test_split(all_data,
train_size=0.8,
random_state=17273)
train_data = np.asarray(sorted(train_data))
test_data = np.asarray(sorted(test_data))
X_train, y_train = train_data[:, 0], train_data[:, 1]
X_test, y_test = test_data[:, 0], test_data[:, 1]
# construct pipeline
featureExtractor = CustomSQLFeatureExtractor(query)
elastic = ElasticNetCV(n_jobs=1,
cv=10,
l1_ratio=[.5, 1],
n_alphas=100,
eps=0.001)
def rmeid(eid=0):
'''Remove a record from the anime cache.'''
handle = apsw.Connection(db)
handle.cursor().execute("delete from episodes where eid=?", (eid, ))
doclose(handle)
import os
import re
import json
import random
import apsw
import time
# import flask web microframework
from flask import Flask
from flask import request
# import from the 21 Developer Library
from two1.lib.wallet import Wallet
from two1.lib.bitserv.flask import Payment
connection = apsw.Connection("apibb.db")
name_re = re.compile(r"^[a-zA-Z0-9][a-zA-Z0-9-\.]*$")
app = Flask(__name__)
wallet = Wallet()
payment = Payment(app, wallet)
def expire_ads():
cursor = connection.cursor()
cursor.execute("DELETE FROM ads WHERE expires < datetime('now')")
def expire_names():
cursor = connection.cursor()
def __init__(self, name):
#------------------------
self._db = apsw.Connection(name)
self._cursor = self._db.cursor()
import logging
import logging.config
import os
import sys
import requests
requests.packages.urllib3.disable_warnings()
import xmltodict
from six.moves import queue as Queue
from threading import Thread
import re
import json
import apsw
import time
conn=apsw.Connection("tumblr.db")
cu=conn.cursor()
logging.config.fileConfig("logger.conf")
logger = logging.getLogger("example01")
logger03 = logging.getLogger("example03")
ss = requests.Session()
# Setting timeout
TIMEOUT = 15
# Retry times
RETRY = 5
# Medium Index Number that Starts from
START = 0
op = []
op.append(".. Automatically generated by code2rst.py")
op.append(" code2rst.py %s %s" % (sys.argv[2], sys.argv[3]))
op.append(" Edit %s not this file!" % (sys.argv[2], ))
op.append("")
if sys.argv[2] != "src/apsw.c":
op.append(".. currentmodule:: apsw")
op.append("")
import apsw
with tempfile.NamedTemporaryFile() as f:
f.write(urllib.request.urlopen(basesqurl + "toc.db").read())
f.flush()
db = apsw.Connection(f.name)
funclist = {}
consts = collections.defaultdict(lambda: copy.deepcopy({"vars": []}))
const2page = {}
for name, type, title, uri in db.cursor().execute(
"select name, type, title, uri from toc"):
if type == "function":
funclist[name] = basesqurl + uri
elif type == "constant":
const2page[name] = basesqurl + uri
consts[title]["vars"].append(name)
consts[title]["page"] = basesqurl + uri.split("#")[0]
def __init__(self):
self.dbConn = apsw.Connection("o.db")
self.cursor = self.dbConn.cursor()
def run(self):
logger = logging.getLogger('werkzeug')
logger.setLevel(logging.WARNING)
db = apsw.Connection(config.DATABASE)
db.setrowtrace(util.rowtracer)
@dispatcher.add_method
def get_address(address):
try:
return util.get_address(db, address=address)
except exceptions.InvalidAddressError:
return None
@dispatcher.add_method
def get_debits(address=None,
asset=None,
order_by=None,
order_dir=None):
return util.get_debits(db,
address=address,
asset=asset,
order_by=order_by,
order_dir=order_dir)
@dispatcher.add_method
def get_credits(address=None,
asset=None,
order_by=None,
order_dir=None):
return util.get_credits(db,
address=address,
asset=asset,
order_by=order_by,
order_dir=order_dir)
@dispatcher.add_method
def get_balances(address=None,
asset=None,
order_by=None,
order_dir=None):
return util.get_balances(db,
address=address,
asset=asset,
order_by=order_by,
order_dir=order_dir)
@dispatcher.add_method
def get_sends(source=None,
destination=None,
is_valid=None,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_sends(db,
source=source,
destination=destination,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_orders(address=None,
is_valid=True,
show_empty=True,
show_expired=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_orders(
db,
address=address,
show_empty=show_empty,
show_expired=show_expired,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_order_matches(address=None,
is_valid=True,
is_mine=False,
tx0_hash=None,
tx1_hash=None,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_order_matches(
db,
is_mine=is_mine,
address=address,
tx0_hash=tx0_hash,
tx1_hash=tx1_hash,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_btcpays(is_valid=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_btcpays(
db,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_issuances(asset=None,
issuer=None,
is_valid=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_issuances(
db,
asset=asset,
issuer=issuer,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_broadcasts(source=None,
is_valid=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_broadcasts(
db,
source=source,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_bets(address=None,
show_empty=False,
is_valid=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_bets(db,
address=address,
show_empty=show_empty,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_bet_matches(address=None,
is_valid=True,
tx0_hash=None,
tx1_hash=None,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_bet_matches(
db,
address=address,
tx0_hash=tx0_hash,
tx1_hash=tx1_hash,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_dividends(address=None,
asset=None,
is_valid=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_dividends(
db,
address=address,
asset=asset,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def get_burns(address=None,
is_valid=True,
order_by=None,
order_dir=None,
start_block=None,
end_block=None):
return util.get_burns(db,
address=address,
validity='Valid' if bool(is_valid) else None,
order_by=order_by,
order_dir=order_dir,
start_block=start_block,
end_block=end_block)
@dispatcher.add_method
def do_send(source, destination, quantity, asset, unsigned=False):
unsigned_tx_hex = send.create(db, source, destination, quantity,
asset)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_order(source,
give_quantity,
give_asset,
get_quantity,
get_asset,
expiration,
fee_required=0,
fee_provided=config.MIN_FEE / config.UNIT,
unsigned=False):
unsigned_tx_hex = order.create(db, source, give_asset,
give_quantity, get_asset,
get_quantity, expiration,
fee_required, fee_provided)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_btcpay(order_match_id, unsigned=False):
unsigned_tx_hex = btcpay.create(db, order_match_id)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_issuance(source,
quantity,
asset,
divisible,
transfer_destination=None,
unsigned=False):
unsigned_tx_hex = issuance.create(db, source, transfer_destination,
asset, quantity, divisible)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_broadcast(source,
fee_multiplier,
text,
value=0,
unsigned=False):
unsigned_tx_hex = broadcast.create(db, source, int(time.time()),
value, fee_multiplier, text)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_bet(source,
feed_address,
bet_type,
deadline,
wager,
counterwager,
target_value=0.0,
leverage=5040,
unsigned=False):
bet_type_id = util.BET_TYPE_ID[bet_type]
unsigned_tx_hex = bet.create(db, source, feed_address, bet_type_id,
deadline, wager, counterwager,
target_value, leverage, expiration)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_dividend(source,
quantity_per_share,
share_asset,
unsigned=False):
unsigned_tx_hex = dividend.create(db, source, quantity_per_share,
share_asset)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_burn(source, quantity, unsigned=False):
unsigned_tx_hex = burn.create(db, source, quantity)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@dispatcher.add_method
def do_cancel(offer_hash, unsigned=False):
unsigned_tx_hex = cancel.create(db, offer_hash)
return bitcoin.transmit(unsigned_tx_hex,
unsigned=unsigned,
ask=False)
@Request.application
def application(request):
response = JSONRPCResponseManager.handle(
request.get_data(cache=False, as_text=True), dispatcher)
return Response(response.json, mimetype='application/json')
# util.database_check(db) # TODO Have this run regularly.
run_simple(config.RPC_HOST, config.RPC_PORT, application)
if len(line)>=2:
if line[0]==".." and line[1] in ("method::", "automethod::", "attribute::"):
funcname=line[2].split("(")[0].strip()
if "." in funcname:
klass, funcname=funcname.split(".",1)
else:
klass="apsw"
if klass not in classes:
classes[klass]=[]
classes[klass].append(funcname)
# ok, so we know what was documented. Now lets see what exists
con=apsw.Connection(":memory:")
cur=con.cursor()
cur.execute("create table x(y); insert into x values(x'abcdef1012');select * from x")
blob=con.blobopen("main", "x", "y", con.last_insert_rowid(), 0)
vfs=apsw.VFS("aname", "")
vfsfile=apsw.VFSFile("", ":memory:", [apsw.SQLITE_OPEN_MAIN_DB|apsw.SQLITE_OPEN_CREATE|apsw.SQLITE_OPEN_READWRITE, 0])
# virtual tables aren't real - just check their size hasn't changed
assert len(classes['VTModule'])==2
del classes['VTModule']
assert len(classes['VTTable'])==13
del classes['VTTable']
assert len(classes['VTCursor'])==6
del classes['VTCursor']
for name, obj in ( ('Connection', con),
def _resume_db_factory() -> apsw.Connection:
conn = apsw.Connection(str(RESUME_DB_PATH))
conn.setbusytimeout(120_000)
return conn
def maketable(reportfile, case):
reportfiledb = os.path.join(case, "extracted data", "mms-sms", "db",
"mmssms.db")
reportfile_connection = apsw.Connection(reportfiledb)
reportfile_cursor1 = reportfile_connection.cursor()
reportfile_cursor2 = reportfile_connection.cursor()
reportfile_cursor3 = reportfile_connection.cursor()
contactfiledb = os.path.join(case, "extracted data", "contacts", "db",
"contacts2.db")
contactfile_connection = apsw.Connection(contactfiledb)
contactfile_cursor1 = contactfile_connection.cursor()
contactfile_cursor2 = contactfile_connection.cursor()
phone1 = re.compile("0")
phone2 = re.compile("\+")
reportfile.write("<table CELLPADDING=8 CELLSPACING=0 VALIGN=TOP>\n")
reportfile.write("</table>\n")
reportfile.write("<div class=\"ResultsTable\">\n")
reportfile.write("<table>\n")
reportfile.write(
"<tr class=\"title\"><td><b>Status</b></td><td><b>Name</b></td><td><b>Number</b></td><td><b>Content</b></td><td><b>Date/Time Recieved</b></td><td><b>Date/Time Sent</b></td></tr>\n"
)
for row1 in reportfile_cursor1.execute(
"SELECT _id FROM sms ORDER BY date DESC"):
for entry in row1:
for row2 in reportfile_cursor2.execute(
"SELECT type FROM sms where _id = " + str(entry)):
for status in row2:
if str(status) == '1':
typename = 'Recieved'
reportfile.write("<TR class=\"Recieved\">")
elif str(status) == '2':
typename = 'Sent'
reportfile.write("<TR class=\"Sent\">")
elif str(status) == '3':
typename = 'Draft'
reportfile.write("<TR class=\"Draft\">")
elif str(status) == '5':
typename = 'Error'
reportfile.write("<TR class=\"Error\">")
else:
typename = 'Unknown (' + str(status) + ')'
reportfile.write("<TR>")
reportfile.write("<TD>" + typename + "</TD>")
for row2 in reportfile_cursor2.execute(
"SELECT address FROM sms where _id = " + str(entry)):
for number in row2:
namestr = 'Unknown'
address = str(number)
address = address.replace(" ", "")
if phone1.match(address) or phone2.match(address):
for row3 in contactfile_cursor1.execute(
"SELECT raw_contact_id FROM phone_lookup where normalized_number = '"
+ address + "'"):
for contactid in row3:
if str(contactid) == 'None':
namestr = 'None'
else:
for row4 in contactfile_cursor2.execute(
"SELECT display_name FROM raw_contacts where _id = '"
+ str(contactid) + "'"):
for name in row4:
namestr = str(name)
reportfile.write("<TD>" + namestr + "</TD>")
for row2 in reportfile_cursor2.execute(
"SELECT address FROM sms where _id = " + str(entry)):
for number in row2:
reportfile.write("<TD>" + str(number) + "</TD>")
for row2 in reportfile_cursor2.execute(
"SELECT body FROM sms where _id = " + str(entry)):
for body in row2:
reportfile.write("<TD>" + str(body) + "</TD>")
for row2 in reportfile_cursor2.execute(
"SELECT datetime(date/1000,'unixepoch','localtime') as date FROM sms where _id = "
+ str(entry)):
for date in row2:
reportfile.write("<TD>" + str(date) + "</TD>")
for row2 in reportfile_cursor2.execute(
"SELECT datetime(date/1000,'unixepoch','localtime') as date_sent FROM sms where _id = "
+ str(entry)):
for sent in row2:
reportfile.write("<TD>" + str(sent) + "</TD>")
reportfile.write("</TR>")
def db_connect():
return apsw.Connection(DB)
def books(self, oncard=None, end_session=True):
import apsw
dummy_bl = BookList(None, None, None)
if ((oncard == 'carda' and not self._card_a_prefix)
or (oncard and oncard != 'carda')):
self.report_progress(1.0, _('Getting list of books on device...'))
return dummy_bl
prefix = self._card_a_prefix if oncard == 'carda' else self._main_prefix
# Let parent driver get the books
self.booklist_class.rebuild_collections = self.rebuild_collections
bl = USBMS.books(self, oncard=oncard, end_session=end_session)
dbpath = self.normalize_path(prefix + DBPATH)
debug_print("SQLite DB Path: " + dbpath)
with closing(apsw.Connection(dbpath)) as connection:
cursor = connection.cursor()
# Query collections
query = '''
SELECT books._id, tags.tagname
FROM booktags
LEFT OUTER JOIN books
LEFT OUTER JOIN tags
WHERE booktags.book_id = books._id AND
booktags.tag_id = tags._id
'''
cursor.execute(query)
bl_collections = {}
for i, row in enumerate(cursor):
bl_collections.setdefault(row[0], [])
bl_collections[row[0]].append(row[1])
# collect information on offsets, but assume any
# offset we already calculated is correct
if self.device_offset is None:
query = 'SELECT filename, addeddate FROM books'
cursor.execute(query)
time_offsets = {}
for i, row in enumerate(cursor):
try:
comp_date = int(
os.path.getmtime(
self.normalize_path(prefix + row[0])) * 1000)
except (OSError, IOError, TypeError):
# In case the db has incorrect path info
continue
device_date = int(row[1])
offset = device_date - comp_date
time_offsets.setdefault(offset, 0)
time_offsets[offset] = time_offsets[offset] + 1
try:
device_offset = max(time_offsets,
key=lambda a: time_offsets.get(a))
debug_print("Device Offset: %d ms" % device_offset)
self.device_offset = device_offset
except ValueError:
debug_print("No Books To Detect Device Offset.")
for idx, book in enumerate(bl):
query = 'SELECT _id, thumbnail FROM books WHERE filename = ?'
t = (book.lpath, )
cursor.execute(query, t)
for i, row in enumerate(cursor):
book.device_collections = bl_collections.get(row[0], None)
thumbnail = row[1]
if thumbnail is not None:
thumbnail = self.normalize_path(prefix + thumbnail)
book.thumbnail = ImageWrapper(thumbnail)
cursor.close()
return bl
def _database(self, dbname):
# return a connection if exists
return lite.Connection(self._resource("databases",
str(dbname) + ".db"))
def reparse(testnet=True):
"""
Reparse all transaction from the database.
- Create a new in-memory DB, copy the DB that is on-disk
- Reparse DB, automatically compares consensus hashes to the original ones from the on-disk DB
"""
options = dict(COUNTERPARTYD_OPTIONS)
server.initialise(database_file=':memory:', testnet=testnet, **options)
logger = logging.getLogger()
if testnet:
config.PREFIX = b'TESTXXXX'
memory_db = database.get_connection(read_only=False)
data_dir = appdirs.user_data_dir(appauthor=config.XCP_NAME,
appname=config.APP_NAME,
roaming=True)
prod_db_path = os.path.join(
data_dir, '{}{}.db'.format(config.APP_NAME,
'.testnet' if testnet else ''))
assert os.path.exists(
prod_db_path), "database path {} does not exist".format(prod_db_path)
prod_db = apsw.Connection(prod_db_path)
prod_db.setrowtrace(database.rowtracer)
# Copy DB from file on disk (should be a DB file with at least all the checkpoints)
# in-memory DB shouldn't have been written to yet up until this point
with memory_db.backup("main", prod_db, "main") as backup:
while not backup.done:
backup.step(100)
# Drop most tables (except blocks, transactions, undolog)
memory_cursor = memory_db.cursor()
for table in blocks.TABLES + ['balances']:
memory_cursor.execute('''DROP TABLE IF EXISTS {}'''.format(table))
# Check that all checkpoint blocks are in the database to be tested.
if testnet:
CHECKPOINTS = check.CHECKPOINTS_TESTNET
else:
CHECKPOINTS = check.CHECKPOINTS_MAINNET
for block_index in CHECKPOINTS.keys():
block_exists = bool(
list(
memory_cursor.execute(
'''SELECT * FROM blocks WHERE block_index = ?''',
(block_index, ))))
assert block_exists, "block #%d does not exist" % block_index
# Clean consensus hashes if first block hash don’t match with checkpoint.
checkpoints = check.CHECKPOINTS_TESTNET if config.TESTNET else check.CHECKPOINTS_MAINNET
columns = [
column['name']
for column in memory_cursor.execute('''PRAGMA table_info(blocks)''')
]
for field in ['ledger_hash', 'txlist_hash']:
if field in columns:
sql = '''SELECT {} FROM blocks WHERE block_index = ?'''.format(
field)
first_hash = list(
memory_cursor.execute(sql, (config.BLOCK_FIRST, )))[0][field]
if first_hash != checkpoints[config.BLOCK_FIRST][field]:
logger.info('First hash changed. Cleaning {}.'.format(field))
memory_cursor.execute(
'''UPDATE blocks SET {} = NULL'''.format(field))
# Initialise missing tables
blocks.initialise(memory_db)
previous_ledger_hash = None
previous_txlist_hash = None
previous_messages_hash = None
# Reparse each block, if ConsensusError is thrown then the difference
memory_cursor.execute('''SELECT * FROM blocks ORDER BY block_index''')
for block in memory_cursor.fetchall():
try:
util.CURRENT_BLOCK_INDEX = block['block_index']
previous_ledger_hash, previous_txlist_hash, previous_messages_hash, previous_found_messages_hash = blocks.parse_block(
memory_db,
block['block_index'],
block['block_time'],
previous_ledger_hash=previous_ledger_hash,
ledger_hash=block['ledger_hash'],
previous_txlist_hash=previous_txlist_hash,
txlist_hash=block['txlist_hash'],
previous_messages_hash=previous_messages_hash)
logger.info(
'Block (re-parse): %s (hashes: L:%s / TX:%s / M:%s%s)' %
(block['block_index'], previous_ledger_hash[-5:],
previous_txlist_hash[-5:], previous_messages_hash[-5:],
(' [overwrote %s]' %
previous_found_messages_hash) if previous_found_messages_hash
and previous_found_messages_hash != previous_messages_hash
else ''))
except check.ConsensusError as e:
message = str(e)
if message.find('ledger_hash') != -1:
new_ledger = get_block_ledger(memory_db, block['block_index'])
old_ledger = get_block_ledger(prod_db, block['block_index'])
compare_strings(old_ledger, new_ledger)
elif message.find('txlist_hash') != -1:
new_txlist = get_block_txlist(memory_db, block['block_index'])
old_txlist = get_block_txlist(prod_db, block['block_index'])
compare_strings(old_txlist, new_txlist)
raise e
def fromDatabaseName(cls, dbFilename, timeout=None):
return cls(apsw.Connection(dbfname), timeout)
tmpfile.write(chunk)
keyfile.close()
tmpfile.close()
dbdestfile.close()
tmpfile = open(dbtmp, "rb")
tmptogzip = tmpfile.read()
finaldb = open(dbdecrypt, "wb")
d = zlib.decompressobj(16 + zlib.MAX_WBITS)
decompressdata = d.decompress(tmptogzip)
finaldb.write(decompressdata)
tmpfile.close()
finaldb.close()
#os.system('bin\\gzip.exe -d < "' + dbtmp + '" > "' + dbdecrypt + '"' if os.name == 'nt' else 'gzip -d < "' + dbtmp + '" > "' + dbdecrypt + '" 2>&1' )
print("--> Extracting WhatsApp data\n\n")
txtoutput = os.path.join(case, "extracted data", "whatsapp",
"messages.txt")
txtoutfile = open(txtoutput, 'w', encoding='utf8')
sqlconnection = apsw.Connection(dbdecrypt)
sqlshell = apsw.Shell(stdout=txtoutfile, db=sqlconnection)
sqlshell.process_command('.header on')
sqlshell.process_sql('select * from messages')
txtoutfile.close()
os.remove(dbtmp)
os.remove(dbnohead)
else:
print("--> Not extracting WhatsApp data. Reason: Not found\n\n")
def rawtransactions_db(request):
"""Return a database object."""
db = apsw.Connection(util_test.CURR_DIR + '/fixtures/rawtransactions.db')
if (request.module.__name__ == 'integration_test'):
util_test.initialise_rawtransactions_db(db)
return db
def get_apsw_connection():
if constants.dev_mode == True:
pref_file = os.path.join(os.getcwd(), 'local_settings')
else:
pref_file = os.path.join(constants.preferences_path, 'local_settings')
return apsw.Connection(pref_file)
def reparse(testnet=True):
"""Reparse all transaction from the database, create a new blockchain and compare it to the old one."""
options = dict(COUNTERPARTYD_OPTIONS)
server.initialise(database_file=':memory:', testnet=testnet, **options)
logger = logging.getLogger()
if testnet:
config.PREFIX = b'TESTXXXX'
memory_db = database.get_connection(read_only=False)
initialise_db(memory_db)
data_dir = appdirs.user_data_dir(appauthor=config.XCP_NAME,
appname=config.APP_NAME,
roaming=True)
prod_db_path = os.path.join(
data_dir, '{}{}.db'.format(config.APP_NAME,
'.testnet' if testnet else ''))
assert os.path.exists(
prod_db_path), "database path {} does not exist".format(prod_db_path)
prod_db = apsw.Connection(prod_db_path)
prod_db.setrowtrace(database.rowtracer)
with memory_db.backup("main", prod_db, "main") as backup:
backup.step()
# Here we don’t use block.reparse() because it reparse db in transaction (`with db`).
memory_cursor = memory_db.cursor()
for table in blocks.TABLES + ['balances']:
memory_cursor.execute('''DROP TABLE IF EXISTS {}'''.format(table))
# Check that all checkpoint blocks are in the database to be tested.
if testnet:
CHECKPOINTS = check.CHECKPOINTS_TESTNET
else:
CHECKPOINTS = check.CHECKPOINTS_MAINNET
for block_index in CHECKPOINTS.keys():
block_exists = bool(
list(
memory_cursor.execute(
'''SELECT * FROM blocks WHERE block_index = ?''',
(block_index, ))))
assert block_exists, "block #%d does not exist" % block_index
# Clean consensus hashes if first block hash don’t match with checkpoint.
checkpoints = check.CHECKPOINTS_TESTNET if config.TESTNET else check.CHECKPOINTS_MAINNET
columns = [
column['name']
for column in memory_cursor.execute('''PRAGMA table_info(blocks)''')
]
for field in ['ledger_hash', 'txlist_hash']:
if field in columns:
sql = '''SELECT {} FROM blocks WHERE block_index = ?'''.format(
field)
first_hash = list(
memory_cursor.execute(sql, (config.BLOCK_FIRST, )))[0][field]
if first_hash != checkpoints[config.BLOCK_FIRST][field]:
logger.info('First hash changed. Cleaning {}.'.format(field))
memory_cursor.execute(
'''UPDATE blocks SET {} = NULL'''.format(field))
blocks.initialise(memory_db)
previous_ledger_hash = None
previous_txlist_hash = None
previous_messages_hash = None
memory_cursor.execute('''SELECT * FROM blocks ORDER BY block_index''')
for block in memory_cursor.fetchall():
try:
util.CURRENT_BLOCK_INDEX = block['block_index']
previous_ledger_hash, previous_txlist_hash, previous_messages_hash, previous_found_messages_hash = blocks.parse_block(
memory_db,
block['block_index'],
block['block_time'],
previous_ledger_hash=previous_ledger_hash,
ledger_hash=block['ledger_hash'],
previous_txlist_hash=previous_txlist_hash,
txlist_hash=block['txlist_hash'],
previous_messages_hash=previous_messages_hash)
logger.info(
'Block (re-parse): %s (hashes: L:%s / TX:%s / M:%s%s)' %
(block['block_index'], previous_ledger_hash[-5:],
previous_txlist_hash[-5:], previous_messages_hash[-5:],
(' [overwrote %s]' %
previous_found_messages_hash) if previous_found_messages_hash
and previous_found_messages_hash != previous_messages_hash
else ''))
except check.ConsensusError as e:
message = str(e)
if message.find('ledger_hash') != -1:
new_ledger = get_block_ledger(memory_db, block['block_index'])
old_ledger = get_block_ledger(prod_db, block['block_index'])
compare_strings(old_ledger, new_ledger)
elif message.find('txlist_hash') != -1:
new_txlist = get_block_txlist(memory_db, block['block_index'])
old_txlist = get_block_txlist(prod_db, block['block_index'])
compare_strings(old_txlist, new_txlist)
raise (e)
def _build_connection(self):
return apsw.Connection(self._build_sqlite_s3_uri(),
flags=apsw.SQLITE_OPEN_READONLY
| apsw.SQLITE_OPEN_URI,
vfs=S3FS.vfsname)
def rmgid(gid=0):
'''Remove a record from the anime cache.'''
handle = apsw.Connection(db)
handle.cursor().execute("delete from groups where gid=?", (gid, ))
doclose(handle)
#!/usr/bin/env python
import apsw
import collections
import sys
db = apsw.Connection(sys.argv[1])
cursor = db.cursor()
# load namespace definitions
nsName = dict()
nsID = dict()
for row in cursor.execute("select namespace_id,namespace from namespace;"):
nsName[row[0]] = row[1]
nsID[row[1]] = row[0]
# load namespace aliases
alias = dict()
for row in cursor.execute(
"select namespace_id1,name1,namespace_id2,name2 from unit_name_name where namespace_id1 = namespace_id2;"
):
n1 = (row[0], row[1])
n2 = (row[2], row[3])
if n1 < n2:
alias[n2] = min(n1, alias.get(n2, n1))
elif n1 > n2:
alias[n1] = min(n2, alias.get(n1, n2))
print "%d namespace aliases" % (len(alias), )
# load name graph
graph = collections.defaultdict(set)
def test_apsw(self):
import apsw
conn = apsw.Connection(':memory:')
conn.close()
def _open_connection(self):
""" Opens a connection to the database. If the database doesn't exist, we create a new one and run the
initialization SQL scripts. If the database doesn't exist, we simply connect to it.
And finally, we read the database version.
"""
# check if it is in memory
is_in_memory = self.sqlite_db_path == u":memory:"
is_new_db = is_in_memory
# check if database file exists
if not is_in_memory:
if not os.path.exists(self.sqlite_db_path):
# create a new one
is_new_db = True
elif not os.path.isfile(self.sqlite_db_path):
msg = u"Not a file: %s" % self.sqlite_db_path
raise OSError(msg)
# create connection
try:
self._connection = apsw.Connection(self.sqlite_db_path)
self._connection.setbusytimeout(self._busytimeout)
except CantOpenError as e:
msg = u"Failed to open connection to %s: %s" % (
self.sqlite_db_path, e)
raise CantOpenError(msg)
cursor = self.get_cursor()
# Check integrity of the database
check_response, = self.execute(u"PRAGMA quick_check").next()
if check_response != 'ok':
msg = u"Quick integrity check of database failed"
self._logger.error(msg)
raise CorruptedDatabaseError(msg)
# apply pragma
page_size, = next(cursor.execute(u"PRAGMA page_size"))
if page_size < 8192:
# journal_mode and page_size only need to be set once. because of the VACUUM this
# is very expensive
self._logger.info(u"begin page_size upgrade...")
cursor.execute(u"PRAGMA journal_mode = DELETE;")
cursor.execute(u"PRAGMA page_size = 8192;")
cursor.execute(u"VACUUM;")
self._logger.info(u"...end page_size upgrade")
# http://www.sqlite.org/pragma.html
# When synchronous is NORMAL, the SQLite database engine will still
# pause at the most critical moments, but less often than in FULL
# mode. There is a very small (though non-zero) chance that a power
# failure at just the wrong time could corrupt the database in
# NORMAL mode. But in practice, you are more likely to suffer a
# catastrophic disk failure or some other unrecoverable hardware
# fault.
#
cursor.execute(u"PRAGMA synchronous = NORMAL;")
cursor.execute(u"PRAGMA cache_size = 10000;")
# Niels 19-09-2012: even though my database upgraded to increase the pagesize it did not keep wal mode?
# Enabling WAL on every starup
cursor.execute(u"PRAGMA journal_mode = WAL;")
# create tables if this is a new database
if is_new_db and self.db_script_path is not None:
self._logger.info(u"Initializing new database...")
# check if the SQL script exists
if not os.path.exists(self.db_script_path):
msg = u"SQL script doesn't exist: %s" % self.db_script_path
raise OSError(msg)
if not os.path.isfile(self.db_script_path):
msg = u"SQL script is not a file: %s" % self.db_script_path
raise OSError(msg)
try:
f = open(self.db_script_path, "r")
sql_script = f.read()
f.close()
except IOError as e:
msg = u"Failed to load SQL script %s: %s" % (
self.db_script_path, e)
raise IOError(msg)
cursor.execute(sql_script)
if self.db_script_path is not None:
# read database version
self._logger.info(u"Reading database version...")
try:
version_str, = cursor.execute(
u"SELECT value FROM MyInfo WHERE entry == 'version'").next(
)
self._version = int(version_str)
self._logger.info(u"Current database version is %s",
self._version)
except (StopIteration, SQLError) as e:
msg = u"Failed to load database version: %s" % e
raise CorruptedDatabaseError(msg)
else:
self._version = 1
def start(self, args):
self.node = args["file"].value()
avfs = apswvfs.apswVFS()
self.db = apsw.Connection(self.node.absolute(), vfs=avfs.vfsname)
def test_apsw():
import apsw
conn = apsw.Connection(':memory:')
conn.close()
print ('apsw OK!')
