def read_data(self):
db = SQLiteCacheDB.getInstance()
one = db.fetchone('select * from person')
assert one == ('a','b'), str(one)
one = db.fetchone("select lastname from person where firstname == 'b'")
assert one == 'a'
one = db.fetchone("select lastname from person where firstname == 'c'")
assert one == None
all = db.fetchall('select * from person')
assert len(all) == 101, len(all)
num = db.size('person')
assert num == 101
db.insert('person', lastname='1', firstname='abc')
one = db.fetchone("select firstname from person where lastname == '1'")
assert one == '1' or one == 'abc'
all = db.fetchall("select firstname from person where lastname == '1'")
assert len(all) == 2
db.update('person', "lastname == '2'", firstname='56')
one = db.fetchone("select firstname from person where lastname == '2'")
assert one == '56', one
db.update('person', "lastname == '3'", firstname=65)
one = db.fetchone("select firstname from person where lastname == '3'")
assert one == 65, one
db.update('person', "lastname == '4'", firstname=654, lastname=44)
one = db.fetchone("select firstname from person where lastname == 44")
assert one == 654, one
db.close()
def read_data(self):
db = SQLiteCacheDB.getInstance()
one = db.fetchone('select * from person')
assert one == ('a', 'b'), str(one)
one = db.fetchone("select lastname from person where firstname == 'b'")
assert one == 'a'
one = db.fetchone("select lastname from person where firstname == 'c'")
assert one == None
all = db.fetchall('select * from person')
assert len(all) == 101, len(all)
num = db.size('person')
assert num == 101
db.insert('person', lastname='1', firstname='abc')
one = db.fetchone("select firstname from person where lastname == '1'")
assert one == '1' or one == 'abc'
all = db.fetchall("select firstname from person where lastname == '1'")
assert len(all) == 2
db.update('person', "lastname == '2'", firstname='56')
one = db.fetchone("select firstname from person where lastname == '2'")
assert one == '56', one
db.update('person', "lastname == '3'", firstname=65)
one = db.fetchone("select firstname from person where lastname == '3'")
assert one == 65, one
db.update('person', "lastname == '4'", firstname=654, lastname=44)
one = db.fetchone("select firstname from person where lastname == 44")
assert one == 654, one
db.close()
def test_open_close_db(self):
sqlite_test = SQLiteCacheDB.getInstance()
sqlite_test.openDB(self.db_path, 1250)
sqlite_test.close()
sqlite_test.close()
sqlite_test.openDB(self.db_path, 0)
sqlite_test.close()
def keep_reading_data(self, period):
db = SQLiteCacheDB.getInstance()
st = time()
oldnum = 0
self.all = []
self.read_times = 0
if DEBUG_R:
print "begin read", self.getName(), period, time()
while True:
et = time()
if et - st > period:
break
if DEBUG_R:
print "...start read", self.getName(), time()
sys.stdout.flush()
try:
self.all = db.fetchall("select * from person")
self.last_read = time() - st
self.read_times += 1
except Exception, msg:
print_exc()
print "*-*", Exception, msg
self.read_locks += 1
if DEBUG:
print >> sys.stdout, "Locked while reading!", self.read_locks
sys.stdout.flush()
else:
if DEBUG_R:
print "...end read", self.getName(), time(), len(
self.all)
sys.stdout.flush()
def setUp(self):
db_path = TRIBLER_DB_PATH
db = SQLiteCacheDB.getInstance()
db.initDB(db_path, busytimeout=BUSYTIMEOUT)
launchmany = FakeLaunchmany(db)
overlay_bridge = FakeOverlayBridge()
self.datahandler = DataHandler(launchmany, overlay_bridge)
def setUp(self):
db_path = TRIBLER_DB_PATH
db = SQLiteCacheDB.getInstance()
db.initDB(db_path, busytimeout=BUSYTIMEOUT)
launchmany = FakeLaunchmany(db)
overlay_bridge = FakeOverlayBridge()
self.datahandler = DataHandler(launchmany,overlay_bridge)
def keep_reading_data(self, period):
db = SQLiteCacheDB.getInstance()
st = time()
oldnum = 0
self.all = []
self.read_times = 0
if DEBUG_R:
print "begin read", self.getName(), period, time()
while True:
et = time()
if et-st > period:
break
if DEBUG_R:
print "...start read", self.getName(), time()
sys.stdout.flush()
try:
self.all = db.fetchall("select * from person")
self.last_read = time()-st
self.read_times += 1
except Exception, msg:
print_exc()
print "*-*", Exception, msg
self.read_locks += 1
if DEBUG:
print >> sys.stdout, "Locked while reading!", self.read_locks
sys.stdout.flush()
else:
if DEBUG_R:
print "...end read", self.getName(), time(), len(self.all)
sys.stdout.flush()
def test_open_close_db(self):
sqlite_test = SQLiteCacheDB.getInstance()
sqlite_test.openDB(self.db_path, 1250)
sqlite_test.close()
sqlite_test.close()
sqlite_test.openDB(self.db_path, 0)
sqlite_test.close()
def keep_writing_data(self, period, num_write, commit=False):
db = SQLiteCacheDB.getInstance()
st = time()
if DEBUG:
print "begin write", self.getName(), period, time()
sys.stdout.flush()
begin_time = time()
w_times = []
c_times = []
self.last_write = 0
try:
while True:
st = time()
if st-begin_time > period:
break
#db.begin()
values = []
for i in range(num_write):
value = (str(i)+'"'+"'", str(i**2)+'"'+"'")
values.append(value)
try:
st = time()
if DEBUG:
print '-'+self.num + "start write", self.getName(), self.writes, time()-begin_time
sys.stdout.flush()
sql = 'INSERT INTO person VALUES (?, ?)'
db.executemany(sql, values, commit=commit)
self.last_write = time()-begin_time
write_time = time()-st
w_times.append(write_time)
if DEBUG:
print '-'+self.num + "end write", self.getName(), '+', write_time
sys.stdout.flush()
self.writes += 1
except apsw.BusyError:
self.write_locks += 1
if DEBUG:
if commit:
s = "Writing/Commiting"
else:
s = "Writing"
print >> sys.stdout, '>'+self.num + "Locked while ", s, self.getName(), self.write_locks, time()-st
sys.stdout.flush()
continue
if SLEEP_W >= 0:
sleep(SLEEP_W/1000.0)
if DO_STH > 0:
do_sth(DO_STH)
except Exception, msg:
print_exc()
print >> sys.stderr, "On Error", time(), begin_time, time()-begin_time, Exception, msg, self.getName()
def create_db(self, db_path, busytimeout=DEFAULT_BUSY_TIMEOUT):
create_sql = "create table person(lastname, firstname);"
db = SQLiteCacheDB.getInstance()
tmp_sql_path = 'tmp.sql'
f = open(tmp_sql_path, 'w')
f.write(create_sql)
f.close()
#print "initDB", db_path
db.initDB(db_path, tmp_sql_path, busytimeout=busytimeout, check_version=False)
os.remove(tmp_sql_path)
def __init__(self):
self._sqlite_cache_db = SQLiteCacheDB.getInstance()
crawler = Crawler.get_instance()
if crawler.am_crawler():
self._file = open("databasecrawler.txt", "a")
self._file.write("".join(("# ", "*" * 80, "\n# ", strftime("%Y/%m/%d %H:%M:%S"), " Crawler started\n")))
self._file.flush()
else:
self._file = None
def __init__(self):
self._sqlite_cache_db = SQLiteCacheDB.getInstance()
crawler = Crawler.get_instance()
if crawler.am_crawler():
self._file = open("databasecrawler.txt", "a")
self._file.write("".join(
("# ", "*" * 80, "\n# ", strftime("%Y/%m/%d %H:%M:%S"),
" Crawler started\n")))
self._file.flush()
else:
self._file = None
def setUp(self):
self.db = SQLiteCacheDB.getInstance()
self.db.initDB(TRIBLER_DB_PATH_BACKUP)
permid = {}
permid[3127] = 'MFIwEAYHKoZIzj0CAQYFK4EEABoDPgAEAcPezgQ13k1MSOaUrCPisWRhYuNT7Tm+q5rUgHFvAWd9b+BcSut6TCniEgHYHDnQ6TH/vxQBqtY8Loag'
permid[994] = 'MFIwEAYHKoZIzj0CAQYFK4EEABoDPgAEAJUNmwvDaigRaM4cj7cE2O7lessqnnFEQsan7df9AZS8xeNmVsP/XXVrEt4t7e2TNicYmjn34st/sx2P'
permid[19] = 'MFIwEAYHKoZIzj0CAQYFK4EEABoDPgAEAAJv2YLuIWa4QEdOEs4CPRxQZDwZphKd/xK/tgbcALG198nNdT10znJ2sZYl+OJIvj7YfYp75PrrnWNX'
permid[5] = 'MFIwEAYHKoZIzj0CAQYFK4EEABoDPgAEAAB0XbUrw5b8CrTrMZST1SPyrzjgSzIE6ynALtlZASGAb+figVXRRGpKW6MSal3KnEm1/q0P3JPWrhCE'
self.permid = permid
db = MyPreferenceDBHandler.getInstance()
db.loadData()
def create_db(self, db_path, busytimeout=DEFAULT_BUSY_TIMEOUT):
create_sql = "create table person(lastname, firstname);"
db = SQLiteCacheDB.getInstance()
tmp_sql_path = 'tmp.sql'
f = open(tmp_sql_path, 'w')
f.write(create_sql)
f.close()
#print "initDB", db_path
db.initDB(db_path,
tmp_sql_path,
busytimeout=busytimeout,
check_version=False)
os.remove(tmp_sql_path)
def setUp(self):
self.file_path = tempfile.mktemp()
self.db_path = tempfile.mktemp()
#print >>sys.stderr,"test: file_path",self.file_path
#print >>sys.stderr,"test: db_path",self.db_path
self.writeSuperPeers()
head,tail = os.path.split(self.file_path)
self.config = {'install_dir':head, 'superpeer_file':tail}
self.db = SQLiteCacheDB.getInstance()
self.db.initDB(self.db_path, CREATE_SQL_FILE, check_version=False)
self.splist = SuperPeerDBHandler.getInstance()
def write_data(self):
db = SQLiteCacheDB.getInstance()
#db.begin()
db.insert('person', lastname='a', firstname='b')
values = []
for i in range(100):
value = (str(i), str(i**2))
values.append(value)
db.insertMany('person', values)
db.commit()
#db.begin()
db.commit()
db.commit()
db.close()
def write_data(self):
db = SQLiteCacheDB.getInstance()
#db.begin()
db.insert('person', lastname='a', firstname='b')
values = []
for i in range(100):
value = (str(i), str(i**2))
values.append(value)
db.insertMany('person', values)
db.commit()
#db.begin()
db.commit()
db.commit()
db.close()
def basic_funcs(self):
db = SQLiteCacheDB.getInstance()
create_sql = "create table person(lastname, firstname);"
db.createDBTable(create_sql, self.db_path)
db.insert('person', lastname='a', firstname='b')
one = db.fetchone('select * from person')
assert one == ('a','b')
one = db.fetchone("select lastname from person where firstname == 'b'")
assert one == 'a'
one = db.fetchone("select lastname from person where firstname == 'c'")
assert one == None
values = []
for i in range(100):
value = (str(i), str(i**2))
values.append(value)
db.insertMany('person', values)
all = db.fetchall('select * from person')
assert len(all) == 101
all = db.fetchall("select * from person where lastname=='101'")
assert all == []
num = db.size('person')
assert num == 101
db.insert('person', lastname='1', firstname='abc')
one = db.fetchone("select firstname from person where lastname == '1'")
assert one == '1' or one == 'abc'
all = db.fetchall("select firstname from person where lastname == '1'")
assert len(all) == 2
db.update('person', "lastname == '2'", firstname='56')
one = db.fetchone("select firstname from person where lastname == '2'")
assert one == '56', one
db.update('person', "lastname == '3'", firstname=65)
one = db.fetchone("select firstname from person where lastname == '3'")
assert one == 65, one
db.update('person', "lastname == '4'", firstname=654, lastname=44)
one = db.fetchone("select firstname from person where lastname == 44")
assert one == 654, one
db.close()
def basic_funcs(self):
db = SQLiteCacheDB.getInstance()
create_sql = "create table person(lastname, firstname);"
db.createDBTable(create_sql, self.db_path)
db.insert('person', lastname='a', firstname='b')
one = db.fetchone('select * from person')
assert one == ('a', 'b')
one = db.fetchone("select lastname from person where firstname == 'b'")
assert one == 'a'
one = db.fetchone("select lastname from person where firstname == 'c'")
assert one == None
values = []
for i in range(100):
value = (str(i), str(i**2))
values.append(value)
db.insertMany('person', values)
all = db.fetchall('select * from person')
assert len(all) == 101
all = db.fetchall("select * from person where lastname=='101'")
assert all == []
num = db.size('person')
assert num == 101
db.insert('person', lastname='1', firstname='abc')
one = db.fetchone("select firstname from person where lastname == '1'")
assert one == '1' or one == 'abc'
all = db.fetchall("select firstname from person where lastname == '1'")
assert len(all) == 2
db.update('person', "lastname == '2'", firstname='56')
one = db.fetchone("select firstname from person where lastname == '2'")
assert one == '56', one
db.update('person', "lastname == '3'", firstname=65)
one = db.fetchone("select firstname from person where lastname == '3'")
assert one == 65, one
db.update('person', "lastname == '4'", firstname=654, lastname=44)
one = db.fetchone("select firstname from person where lastname == 44")
assert one == 654, one
db.close()
def test_insertPeer(self):
create_sql = """
CREATE TABLE Peer (
peer_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
permid text NOT NULL,
name text,
ip text,
port integer,
thumbnail text,
oversion integer,
similarity numeric,
friend integer,
superpeer integer,
last_seen numeric,
last_connected numeric,
last_buddycast numeric,
connected_times integer,
buddycast_times integer,
num_peers integer,
num_torrents integer,
num_prefs integer,
num_queries integer
);
"""
db = SQLiteCacheDB.getInstance()
db.createDBTable(create_sql, self.db_path)
assert db.size('Peer') == 0
fake_permid_x = 'fake_permid_x' + '0R0\x10\x06\x07*\x86H\xce=\x02\x01\x06\x05+\x81\x04\x00\x1a\x03>\x00\x04'
peer_x = {
'permid': fake_permid_x,
'ip': '1.2.3.4',
'port': 234,
'name': 'fake peer x'
}
permid = peer_x.pop('permid')
db.insertPeer(permid, update=False, **peer_x)
assert db.size('Peer') == 1
assert db.getOne('Peer', 'name', peer_id=1) == peer_x['name']
peer_x['port'] = 456
db.insertPeer(permid, update=False, **peer_x)
assert db.getOne('Peer', 'port', peer_id=1) == 234
db.insertPeer(permid, update=True, **peer_x)
assert db.getOne('Peer', 'port', peer_id=1) == 456
def test_insertPeer(self):
create_sql = """
CREATE TABLE Peer (
peer_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
permid text NOT NULL,
name text,
ip text,
port integer,
thumbnail text,
oversion integer,
similarity numeric,
friend integer,
superpeer integer,
last_seen numeric,
last_connected numeric,
last_buddycast numeric,
connected_times integer,
buddycast_times integer,
num_peers integer,
num_torrents integer,
num_prefs integer,
num_queries integer
);
"""
db = SQLiteCacheDB.getInstance()
db.createDBTable(create_sql, self.db_path)
assert db.size('Peer') == 0
fake_permid_x = 'fake_permid_x'+'0R0\x10\x06\x07*\x86H\xce=\x02\x01\x06\x05+\x81\x04\x00\x1a\x03>\x00\x04'
peer_x = {'permid':fake_permid_x, 'ip':'1.2.3.4', 'port':234, 'name':'fake peer x'}
permid = peer_x.pop('permid')
db.insertPeer(permid, update=False, **peer_x)
assert db.size('Peer') == 1
assert db.getOne('Peer', 'name', peer_id=1) == peer_x['name']
peer_x['port']=456
db.insertPeer(permid, update=False, **peer_x)
assert db.getOne('Peer', 'port', peer_id=1) == 234
db.insertPeer(permid, update=True, **peer_x)
assert db.getOne('Peer', 'port', peer_id=1) == 456
def __init__(self, db=SQLiteCacheDB.getInstance()):
# notice that singleton pattern is not enforced.
# This way the code is more easy
# to test.
try:
MetadataDBHandler._lock.acquire()
MetadataDBHandler.__single = self
finally:
MetadataDBHandler._lock.release()
try:
self._db = db
# Don't know what those life should know. Assuming I don't need
# them 'till a countrary proof! (Ask Nitin)
# BasicDBHandler.__init__(self,db,METADATA_TABLE)
# BasicDBHandler.__init__(self,db,SUBTITLES_TABLE)
print >> sys.stderr, time.asctime(),'-', "Metadata: DB made"
except:
print >> sys.stderr, time.asctime(),'-', "Metadata: couldn't make the tables"
print >> sys.stderr, time.asctime(),'-', "Metadata DB Handler initialized"
def tearDown(self):
SQLiteCacheDB.getInstance().close()
self.datahandler.peers = None
del self.datahandler
def keep_writing_data(self, period, num_write, commit=False):
db = SQLiteCacheDB.getInstance()
st = time()
if DEBUG:
print "begin write", self.getName(), period, time()
sys.stdout.flush()
begin_time = time()
w_times = []
c_times = []
self.last_write = 0
try:
while True:
st = time()
if st - begin_time > period:
break
#db.begin()
values = []
for i in range(num_write):
value = (str(i) + '"' + "'", str(i**2) + '"' + "'")
values.append(value)
try:
st = time()
if DEBUG:
print '-' + self.num + "start write", self.getName(
), self.writes, time() - begin_time
sys.stdout.flush()
sql = 'INSERT INTO person VALUES (?, ?)'
db.executemany(sql, values, commit=commit)
self.last_write = time() - begin_time
write_time = time() - st
w_times.append(write_time)
if DEBUG:
print '-' + self.num + "end write", self.getName(
), '+', write_time
sys.stdout.flush()
self.writes += 1
except apsw.BusyError:
self.write_locks += 1
if DEBUG:
if commit:
s = "Writing/Commiting"
else:
s = "Writing"
print >> sys.stdout, '>' + self.num + "Locked while ", s, self.getName(
), self.write_locks, time() - st
sys.stdout.flush()
continue
if SLEEP_W >= 0:
sleep(SLEEP_W / 1000.0)
if DO_STH > 0:
do_sth(DO_STH)
except Exception, msg:
print_exc()
print >> sys.stderr, "On Error", time(), begin_time, time(
) - begin_time, Exception, msg, self.getName()
NLOOPS = 1
total_rlock = total_wlock = 0
for i in range(NLOOPS):
rlock, wlock = start_testing(nwriters=1,
nreaders=0,
num_write=100,
write_period=5,
read_period=5,
db_path=self.db_path,
busytimeout=5000,
commit=True)
total_rlock += rlock
total_wlock += wlock
db = SQLiteCacheDB.getInstance()
all = db.fetchall("select * from person")
if INFO:
print "Finally inserted", len(all)
assert total_rlock == 0 and total_wlock == 0, (total_rlock,
total_wlock)
assert len(all) > 0, len(all)
def test_suite():
suite = unittest.TestSuite()
suite.addTest(unittest.makeSuite(TestSqliteCacheDB))
suite.addTest(unittest.makeSuite(TestThreadedSqliteCacheDB))
suite.addTest(unittest.makeSuite(TestSQLitePerformance))
def tearDown(self):
SQLiteCacheDB.getInstance().close()
self.datahandler.peers = None
del self.datahandler
def test_create_temp_db(self):
sqlite_test = SQLiteCacheDB.getInstance()
sql = "create table person(lastname, firstname);"
sqlite_test.createDBTable(sql, self.db_path)
sqlite_test.close(clean=True)
def __init__(self):
self.db = SQLiteCacheDB.getInstance()
def tearDown(self):
db = SQLiteCacheDB.getInstance()
db.close(clean=True)
del db
if os.path.exists(self.db_path):
os.remove(self.db_path)
def test_create_temp_db(self):
sqlite_test = SQLiteCacheDB.getInstance()
sql = "create table person(lastname, firstname);"
sqlite_test.createDBTable(sql, self.db_path)
sqlite_test.close(clean=True)
#sys.setcheckinterval(1)
DEBUG_R = False
DEBUG = False
INFO = False
SLEEP_W = -10 # millisecond. -1 to disable, otherwise indicate how long to sleep
DO_STH = 0
NLOOPS = 1
total_rlock = total_wlock = 0
for i in range(NLOOPS):
rlock, wlock = start_testing(nwriters=1, nreaders=0, num_write=100, write_period=5, read_period=5,
db_path=self.db_path, busytimeout=5000, commit=True)
total_rlock += rlock
total_wlock += wlock
db = SQLiteCacheDB.getInstance()
all = db.fetchall("select * from person")
if INFO:
print "Finally inserted", len(all)
assert total_rlock == 0 and total_wlock == 0, (total_rlock, total_wlock)
assert len(all) > 0, len(all)
def test_suite():
suite = unittest.TestSuite()
suite.addTest(unittest.makeSuite(TestSqliteCacheDB))
suite.addTest(unittest.makeSuite(TestThreadedSqliteCacheDB))
suite.addTest(unittest.makeSuite(TestSQLitePerformance))
return suite
def __init__(self):
self.db = SQLiteCacheDB.getInstance()
def tearDown(self):
db = SQLiteCacheDB.getInstance()
db.close(clean=True)
del db
if os.path.exists(self.db_path):
os.remove(self.db_path)
