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