def setUp(self):
# test will be skipped if mysql module or ensembldb server unavailable
logger.debug('accessing ensembldb.ensembl.org')
conn = DBServerInfo(host='ensembldb.ensembl.org',
user='******',
passwd='')
try:
translationDB = \
SQLTableCatcher('homo_sapiens_core_47_36i.translation',
serverInfo=conn)
translationDB.catchIter = True # should not iter in this test!
exonDB = SQLTable('homo_sapiens_core_47_36i.exon', serverInfo=conn)
except ImportError, e:
raise SkipTest(e)
def setup(self):
# test will be skipped if unavailable
import MySQLdb
createTable = """\
CREATE TABLE test.sqltable_test (primary_id INTEGER PRIMARY KEY AUTO_INCREMENT, seq_id TEXT, start INTEGER, stop INTEGER)
"""
try:
self.db = SQLTable('test.sqltable_test',
dropIfExists=True,
createTable=createTable)
except MySQLdb.MySQLError:
tempcurs = getNameCursor()[1]
try: # hmm, maybe need to create the test database?
tempcurs.execute('create database if not exists test')
self.db = SQLTable('test.sqltable_test',
dropIfExists=True,
createTable=createTable)
except MySQLdb.MySQLError: # no server, database or privileges?
print """\
The MySQL 'test' database doesn't exist and/or can't be
created or accessed on this account. This test will be skipped.
"""
raise ImportError # skip tests.
self.db.cursor.execute("""\
INSERT INTO test.sqltable_test (seq_id, start, stop)
VALUES ('seq1', 0, 10)
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_test (seq_id, start, stop)
VALUES ('seq2', 5, 15)
""")
self.sourceDB = SQLTable('test.sqltable_join1',
dropIfExists=True, createTable="""\
CREATE TABLE test.sqltable_join1 (my_id INTEGER PRIMARY KEY,
other_id VARCHAR(16))
""")
self.targetDB = SQLTable('test.sqltable_join2',
dropIfExists=True, createTable="""\
CREATE TABLE test.sqltable_join2 (third_id INTEGER PRIMARY KEY,
other_id VARCHAR(16))
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join1 VALUES (2,'seq2')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join1 VALUES (3,'seq3')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join1 VALUES (4,'seq4')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (7, 'seq2')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (99, 'seq3')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (6, 'seq4')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (8, 'seq4')
""")
class SQLTable_Test(object):
@skip_errors(ImportError)
def setup(self):
# test will be skipped if unavailable
import MySQLdb
createTable = """\
CREATE TABLE test.sqltable_test (primary_id INTEGER PRIMARY KEY AUTO_INCREMENT, seq_id TEXT, start INTEGER, stop INTEGER)
"""
try:
self.db = SQLTable('test.sqltable_test',
dropIfExists=True,
createTable=createTable)
except MySQLdb.MySQLError:
tempcurs = getNameCursor()[1]
try: # hmm, maybe need to create the test database?
tempcurs.execute('create database if not exists test')
self.db = SQLTable('test.sqltable_test',
dropIfExists=True,
createTable=createTable)
except MySQLdb.MySQLError: # no server, database or privileges?
print """\
The MySQL 'test' database doesn't exist and/or can't be
created or accessed on this account. This test will be skipped.
"""
raise ImportError # skip tests.
self.db.cursor.execute("""\
INSERT INTO test.sqltable_test (seq_id, start, stop)
VALUES ('seq1', 0, 10)
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_test (seq_id, start, stop)
VALUES ('seq2', 5, 15)
""")
self.sourceDB = SQLTable('test.sqltable_join1',
dropIfExists=True, createTable="""\
CREATE TABLE test.sqltable_join1 (my_id INTEGER PRIMARY KEY,
other_id VARCHAR(16))
""")
self.targetDB = SQLTable('test.sqltable_join2',
dropIfExists=True, createTable="""\
CREATE TABLE test.sqltable_join2 (third_id INTEGER PRIMARY KEY,
other_id VARCHAR(16))
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join1 VALUES (2,'seq2')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join1 VALUES (3,'seq3')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join1 VALUES (4,'seq4')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (7, 'seq2')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (99, 'seq3')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (6, 'seq4')
""")
self.db.cursor.execute("""\
INSERT INTO test.sqltable_join2 VALUES (8, 'seq4')
""")
def teardown(self):
self.db.cursor.execute('drop table if exists test.sqltable_test')
self.db.cursor.execute('drop table if exists test.sqltable_join1')
self.db.cursor.execute('drop table if exists test.sqltable_join2')
def keys_test(self):
k = self.db.keys()
k.sort()
assert k == [1, 2]
def contains_test(self):
assert 1 in self.db
assert 2 in self.db
assert 'foo' not in self.db
def has_key_test(self):
assert self.db.has_key(1)
assert self.db.has_key(2)
assert not self.db.has_key('foo')
def get_test(self):
assert self.db.get('foo') is None
assert self.db.get(1) == self.db[1]
assert self.db.get(2) == self.db[2]
def items_test(self):
i = [ k for (k,v) in self.db.items() ]
i.sort()
assert i == [1, 2]
def iterkeys_test(self):
kk = self.db.keys()
kk.sort()
ik = list(self.db.iterkeys())
ik.sort()
assert kk == ik
def itervalues_test(self):
kv = self.db.values()
kv.sort()
iv = list(self.db.itervalues())
iv.sort()
assert kv == iv
def iteritems_test(self):
ki = self.db.items()
ki.sort()
ii = list(self.db.iteritems())
ii.sort()
assert ki == ii
### @CTB need to test write access
def mapview_test(self):
m = MapView(self.sourceDB, self.targetDB,"""\
SELECT t2.third_id FROM test.sqltable_join1 t1, test.sqltable_join2 t2
WHERE t1.my_id=%s and t1.other_id=t2.other_id
""", cursor=self.db.cursor)
assert m[self.sourceDB[2]] == self.targetDB[7]
assert m[self.sourceDB[3]] == self.targetDB[99]
assert self.sourceDB[2] in m
def graphview_test(self):
m = GraphView(self.sourceDB, self.targetDB,"""\
SELECT t2.third_id FROM test.sqltable_join1 t1, test.sqltable_join2 t2
WHERE t1.my_id=%s and t1.other_id=t2.other_id
""", cursor=self.db.cursor)
d = m[self.sourceDB[4]]
assert len(d) == 2
assert self.targetDB[6] in d and self.targetDB[8] in d
assert self.sourceDB[2] in m
