Lib.jyjdbc.mydbapi.py Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of jython Show documentation
Show all versions of jython Show documentation
Jython is an implementation of the high-level, dynamic, object-oriented
language Python written in 100% Pure Java, and seamlessly integrated with
the Java platform. It thus allows you to run Python on any Java platform.
#!/usr/bin/env python
# -*- coding: ascii -*-
# vim:ts=4:sw=4:softtabstop=4:smarttab:expandtab
"""Test suite for jyjdbc and SQLite.
Uses/requires old/original http://stuartbishop.net/Software/DBAPI20TestSuite/
(also see https://launchpad.net/dbapi-compliance).
Does not use the newer (http://code.google.com/p/acute-dbapi/)
"""
from __future__ import generators
import os
import sys
import string
try:
if sys.version_info < (2, 3):
raise ImportError
import unittest2
unittest = unittest2
except ImportError:
import unittest
unittest2 = None
import dbapi20
class GenericDatabaseAPITest(dbapi20.DatabaseAPI20Test):
"""Generic additions to dbapi20.
NOTE these should all be contributed upstream
TODO move generic tests into here
"""
sql_types = {} # Each DBMS test subclass should define types here
"""
BLOB -> the DBMS SQL type to use in DDL for BLOB, also known as, Binary Large OBject
CLOB -> the DBMS SQL type to use in DDL for CLOB/NCLOB, also known as, Character Large OBject
"""
def test_simple_blob(self):
table_name = 'blob_test'
ddl_sql = 'create table %s (col1 integer, col2 %s)' % (table_name, self.sql_types['BLOB'])
xddl_sql = 'drop table %s' % table_name
"""Sadly this test performs BOTH insert and select.
A failure means 1 or more of:
* insert failed
* select failed
* both of the above
* description has wrong type
It is difficult to write portable insert only and portable
select only tests. INSERT/SELECT only tests should be implemented
for each tested DBMS/driver.
For simplicity only "small" length BLOB data tested, it simply
checks all possible byte values are handled.
Additional tests for large length data are required
"""
con = self._connect()
try:
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bin_data = []
for x in range(0xff + 1):
bin_data.append(chr(x))
bin_data = ''.join(bin_data)
bind_params = (self.driver.Binary(bin_data),)
cur.execute("insert into %s (col1, col2) values (1, ?)" % (table_name,), bind_params)
self.failUnless(cur.rowcount in (-1, 1))
expected_result = [(1, bin_data), ]
cur.execute('select * from %s' % table_name)
col_description = cur.description
# ensure correct data returned
rows = cur.fetchall()
self.assertEqual(rows, expected_result)
# ensure correct type meta data returned
# NOTE "typeless" systems may fail here. For example, sqlite3 type affinity causes failures here.
self.assertEqual(col_description[1][1], self.driver.BINARY)
cur.execute(xddl_sql)
con.commit()
finally:
con.close()
def test_commit_works(self):
# Test that commit really does commit work
# Similar to test_rollback_works
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
row1 = 'Victoria Bitter'
cur.execute("insert into %sbooze values ('%s')" % (
self.table_prefix, row1))
cur.execute("select name from %sbooze" % self.table_prefix)
row = cur.fetchone()
self.assertEqual(row, (row1,), 'First insert pre commit')
con.commit()
cur.execute("select name from %sbooze" % self.table_prefix)
row = cur.fetchone()
self.assertEqual(row, (row1,), 'First insert after commit')
finally:
con.close()
def test_rollback_works_explict_cursor_close(self):
# Test that commit really does commit work
# Similar to test_commit_works
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
con.commit() # some databases allow recovery of DML
row1 = 'Victoria Bitter'
cur.execute("insert into %sbooze values ('%s')" % (
self.table_prefix, row1))
#con.rollback()
cur.execute("select name from %sbooze" % self.table_prefix)
row = cur.fetchone()
self.assertEqual(row, (row1,), 'First insert pre rollback')
cur.close() # close cursor before rollback
con.rollback()
cur = con.cursor()
cur.execute("select name from %sbooze" % self.table_prefix)
row = cur.fetchone()
self.assertEqual(row, None, 'First insert after rollback')
finally:
con.close()
def test_rollback_works(self):
# Test that commit really does commit work
# Similar to test_commit_works/test_rollback_works_explict_cursor_close
# NOTE expects driver to deal with cursor close
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
con.commit() # some databases allow recovery of DML
row1 = 'Victoria Bitter'
cur.execute("insert into %sbooze values ('%s')" % (
self.table_prefix, row1))
#con.rollback()
cur.execute("select name from %sbooze" % self.table_prefix)
row = cur.fetchone()
self.assertEqual(row, (row1,), 'First insert pre rollback')
con.rollback()
cur.execute("select name from %sbooze" % self.table_prefix)
row = cur.fetchone()
self.assertEqual(row, None, 'First insert after rollback')
finally:
con.close()
def test_simple_clob(self):
table_name = 'clob_test'
ddl_sql = 'create table %s (col1 integer, col2 %s)' % (table_name, self.sql_types['CLOB'])
xddl_sql = 'drop table %s' % table_name
"""Sadly this test performs BOTH insert and select.
A failure means 1 or more of:
* insert failed
* select failed
* both of the above
* description has wrong type
It is difficult to write portable insert only and portable
select only tests. INSERT/SELECT only tests should be implemented
for each tested DBMS/driver.
For simplicity only "small" length CLOB data tested.
Additional tests for large length data are required.
"""
con = self._connect()
try:
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
clob_data = string.ascii_letters + string.digits
bind_params = (clob_data,)
cur.execute("insert into %s (col1, col2) values (1, ?)" % (table_name,), bind_params)
self.failUnless(cur.rowcount in (-1, 1))
expected_result = [(1, clob_data), ]
cur.execute('select * from %s' % table_name)
col_description = cur.description
# ensure correct data returned
rows = cur.fetchall()
self.assertEqual(rows, expected_result)
# ensure correct type meta data returned
# NOTE "typeless" systems may fail here. For example, sqlite3 type affinity causes failures here.
self.assertEqual(col_description[1][1], self.driver.STRING)
cur.execute(xddl_sql)
con.commit()
finally:
con.close()
def test_simple_nclob(self):
table_name = 'nclob_test'
ddl_sql = 'create table %s (col1 integer, col2 %s)' % (table_name, self.sql_types['CLOB'])
xddl_sql = 'drop table %s' % table_name
"""Sadly this test performs BOTH insert and select.
A failure means 1 or more of:
* insert failed
* select failed
* both of the above
* description has wrong type
It is difficult to write portable insert only and portable
select only tests. INSERT/SELECT only tests should be implemented
for each tested DBMS/driver.
For simplicity only "small" length CLOB data tested.
Additional tests for large length data are required.
"""
con = self._connect()
try:
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
nclob_data = unicode(string.ascii_letters + string.digits)
bind_params = (nclob_data,)
cur.execute("insert into %s (col1, col2) values (1, ?)" % (table_name,), bind_params)
self.failUnless(cur.rowcount in (-1, 1))
expected_result = [(1, nclob_data), ]
cur.execute('select * from %s' % table_name)
col_description = cur.description
# ensure correct data returned
rows = cur.fetchall()
self.assertEqual(rows, expected_result)
# ensure correct type meta data returned
# NOTE "typeless" systems may fail here. For example, sqlite3 type affinity causes failures here.
self.assertEqual(col_description[1][1], self.driver.STRING)
cur.execute(xddl_sql)
con.commit()
finally:
con.close()
"""Test ideas
# TODO add test that issues select with fetch of _some_ rows (not all) and then issues rollback/commit
# and attempts to fetch the rest of the rows
# NOTE not all DBMS support fetch after commit so this would NOT be a dbi test. It would be a behavior test
def test_optional_extension_autocommit(self):
# EXTENSION has been discussed in db-sig
# pep-249 documents an optional menthod but does not give
# a name suggestion
# Test for the optional DB API 2.0 extension;
# autocommit on/off - does not test what happens with open transactions
pass
"""
class zxJDBCCompatTest(GenericDatabaseAPITest):
"""Test zxJDBC additions to dbapi that existing zxJDBC applications
maybe using.
CPython implementations of drivers may be missing these, for example
sqlite3 part of CPython 2.5 stdlib does not include dbversion attribute.
"""
def test_connect_dbversion(self):
con = self._connect()
try:
#TODO add assert con.dbversion attr is instance string/basestring
self.failUnless(hasattr(con, 'dbversion'),
'zxJDBC has extension dbversion')
finally:
con.close()
class MyDatabaseAPITest(zxJDBCCompatTest):
# TODO add stop for failed connects, avoids long running, 100% all failing results
def skip(self, reason):
"""Skip current test because of `reason`.
NOTE currently expects unittest2, and defaults to "pass" if not available.
unittest2 does NOT work under Python 2.2.
Could potentially use nose or py.test which has (previously) supported Python 2.2
* nose http://python-nose.googlecode.com/svn/wiki/NoseWithPython2_2.wiki
* py.test http://codespeak.net/pipermail/py-dev/2005-February/000203.html
"""
if unittest2:
raise unittest2.SkipTest(reason)
def test_nextset(self):
self.skip('assume nextset not implemented')
def test_setoutputsize(self):
self.skip('assume setoutputsize not implemented')
# TODO contribute this test back to dbapi20
def test_fetch_partial_ddl(self):
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
# insert literals (avoid bind param code)
cur.execute("insert into %sbooze values ('Victoria Bitter')" % (
self.table_prefix,
) )
cur.execute("insert into %sbooze values ('Cooper')" % (
self.table_prefix,
))
cur.execute('select * from %sbooze' % (self.table_prefix))
row1 = cur.fetchone()
"""At this point there is at least one more row to retrieve
before end of row, issue DDL (on same table)
"""
cur.execute(self.xddl1)
finally:
con.close()
def test_fetch_partial_select(self):
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
# insert literals (avoid bind param code)
cur.execute("insert into %sbooze values ('Victoria Bitter')" % (
self.table_prefix,
))
cur.execute("insert into %sbooze values ('Cooper')" % (
self.table_prefix,
))
cur.execute('select * from %sbooze' % (self.table_prefix))
row1 = cur.fetchone()
"""At this point there is at least one more row to retrieve
before end of row, issue another query (on same table)
"""
cur.execute('select * from %sbooze' % (self.table_prefix))
row1 = cur.fetchone() # TODO? Confirm row contents?
finally:
con.close()
# TODO contribute this test back to dbapi20
def test_close_rollback(self):
table_name = 'rollback_close'
# NOTE sqlite tests MUST use a persistent (not :memory:) database for this test
# Add sqlite check? Currently this will just fail with memory.
ddl_sql = 'create table %s (name varchar(20))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
try:
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
cur.execute("insert into %s values ('Victoria Bitter')" % (table_name,))
self.failUnless(cur.rowcount in (-1, 1))
finally:
# NOTE no explict commit, so rollback is expected at close
con.close()
con = self._connect()
try:
cur = con.cursor()
cur.execute("insert into %s values ('Cooper')" % (table_name,))
self.failUnless(cur.rowcount in (-1, 1))
# check row count, should only be 1, due to (auto) rollback in connection 1
cur.execute('select count(*) from %s' % (table_name,))
row = cur.fetchone()
self.failUnless(row[0] == 1, '%r != 1' % (row[0],))
cur.execute(xddl_sql)
con.commit()
finally:
con.close()
def test_open_close(self):
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
cur.execute(self.xddl1)
cur.close()
finally:
con.close()
# TODO implement decimal test with plugable decimal type (pick up from jdbc metadata?) in this module for generic test
# basically a mini version of http://bugs.jython.org/issue1421
# JDBC does NOT have an interval type but most JDBC drivers indicate the type correctly.
def test_interval_type_day_to_second(self):
table_name = 't_issue1421'
ddl_sql = 'create table %s (col1 INTERVAL DAY TO SECOND)' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
try:
cur = con.cursor()
cur.execute(ddl_sql)
cur.execute("insert into %s (col1) values (INTERVAL '1:30' HOUR TO MINUTE)" % table_name)
cur.execute('select col1 from %s' % (table_name,))
row = cur.fetchone()
# TODO define behavior - JDBC does not support intervals so
# an extension is needed currently intervals are returned as
# strings for most JDBC drivers.
# Consider returning datetime.timedelta()
# for now this is a good smoke test to see which backends
# support the datatype
#
# FIXME add description/row check
#print cur.description
#print row
cur.execute(xddl_sql)
cur.close()
finally:
con.close()
# TODO contribute this test back to dbapi20 - bind param version of test_None()
def test_none_bind_null(self):
con = self._connect()
try:
cur = con.cursor()
self.executeDDL1(cur)
# FIXME hard coded qmark
cur.execute('insert into %sbooze values (?)' % self.table_prefix, (None,))
cur.execute('select name from %sbooze' % self.table_prefix)
r = cur.fetchall()
self.assertEqual(len(r), 1)
self.assertEqual(len(r[0]), 1)
self.assertEqual(r[0][0], None, 'NULL value not sent/returned as None')
finally:
con.close()
# TODO contribute this test back to dbapi20
def test_executemany_simplelist(self):
table_name = 'executemany_simplelist'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
cur.executemany("insert into %s (col1, col2) values (?, ?)" % (table_name,), bind_data)
cur.execute('select * from %s' % (table_name,))
result = cur.fetchall()
cur.execute(xddl_sql)
bind_data.sort()
result.sort()
# Assume we have a list of tuples
# if types are not the same check below will fail
self.assertEqual(bind_data, result)
con.close()
# TODO contribute this test back to dbapi20
def test_executemany_simpleiterator(self):
table_name = 'executemany_simplelist'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
canon_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
def simple_generator():
for x in canon_data:
yield x
bind_data = simple_generator()
cur.executemany("insert into %s (col1, col2) values (?, ?)" % (table_name,), bind_data)
cur.execute('select * from %s' % (table_name,))
result = cur.fetchall()
cur.execute(xddl_sql)
canon_data.sort()
result.sort()
# Assume we have a list of tuples
# if types are not the same check below will fail
self.assertEqual(canon_data, result)
con.close()
# TODO contribute this test back to dbapi20
def test_executesame_insert_simplelist(self):
pass # do not use docstring for name if there are errors
"""perform the same insert query text, multiple times.
Used to see if prepared statements are re-used to gain performance.
"""
table_name = 'executesame_insert_simplelist'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(1, 's 1'),
(1, 's 1'),
(1, 's 1'),
]
for b in bind_data:
cur.execute("insert into %s (col1, col2) values (1, 's 1')" % (table_name,))
cur.execute('select * from %s' % (table_name,))
result = cur.fetchall()
cur.execute(xddl_sql)
bind_data.sort()
result.sort()
# Assume we have a list of tuples
# if types are not the same check below will fail
self.assertEqual(bind_data, result)
con.close()
# TODO contribute this test back to dbapi20
def test_executesame_insert_simplelist_withbind(self):
pass # do not use docstring for name if there are errors
"""perform the same insert query text, multiple times.
Used to see if prepared statements are re-used to gain performance.
"""
table_name = 'executesame_insert_simplelist'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
for b in bind_data:
cur.execute("insert into %s (col1, col2) values (?, ?)" % (table_name,), b)
cur.execute('select * from %s' % (table_name,))
result = cur.fetchall()
cur.execute(xddl_sql)
bind_data.sort()
result.sort()
# Assume we have a list of tuples
# if types are not the same check below will fail
self.assertEqual(bind_data, result)
con.close()
# TODO contribute this test back to dbapi20
def test_executesame_select(self):
pass # do not use docstring for name if there are errors
"""perform the same select query text, multiple times.
Used to see if prepared statements are re-used to gain performance.
"""
table_name = 'executesame_select'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
for b in bind_data:
cur.execute("insert into %s (col1, col2) values (?, ?)" % (table_name,), b)
repeat_count = 5
for dummy in xrange(repeat_count):
"""
if dummy == 1:
import pdb
pdb.set_trace()
"""
cur.execute('select * from %s' % (table_name,))
result = cur.fetchall()
bind_data.sort()
result.sort()
# Assume we have a list of tuples
# if types are not the same check below will fail
self.assertEqual(bind_data, result)
cur.execute(xddl_sql)
con.close()
def test_executesame_select_withbind(self):
pass # do not use docstring for name if there are errors
"""perform the same select query text, multiple times.
Used to see if prepared statements are re-used to gain performance.
"""
table_name = 'executesame_select'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
for b in bind_data:
cur.execute("insert into %s (col1, col2) values (?, ?)" % (table_name,), b)
repeat_count = 5
for dummy in xrange(repeat_count):
"""
if dummy == 1:
import pdb
pdb.set_trace()
"""
cur.execute('select * from %s where ?=?' % (table_name,), (1, 1))
result = cur.fetchall()
bind_data.sort()
result.sort()
# Assume we have a list of tuples
# if types are not the same check below will fail
self.assertEqual(bind_data, result)
cur.execute(xddl_sql)
con.close()
def test_executesame_partialselect_withbind(self):
pass # do not use docstring for name if there are errors
"""perform the same select query text, multiple times BUT do not fetch all rows back before re-issuing query
Used to see if prepared statements are re-used to gain performance.
"""
table_name = 'executesame_select'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
for b in bind_data:
cur.execute("insert into %s (col1, col2) values (?, ?)" % (table_name,), b)
repeat_count = 5
for dummy in xrange(repeat_count):
"""
if dummy == 1:
import pdb
pdb.set_trace()
"""
cur.execute('select * from %s where ?=?' % (table_name,), (1, 1))
result = cur.fetchone()
bind_data.sort()
self.assertEqual((1, 's 1'), result)
cur.execute(xddl_sql)
con.close()
def test_executesame_partialselect(self):
pass # do not use docstring for name if there are errors
"""perform the same select query text, multiple times BUT do not fetch all rows back before re-issuing query
Used to see if prepared statements are re-used to gain performance.
"""
table_name = 'executesame_select'
ddl_sql = 'create table %s (col1 integer, col2 varchar(10))' % table_name
xddl_sql = 'drop table %s' % table_name
con = self._connect()
cur = con.cursor()
try:
cur.execute(xddl_sql)
except self.driver.Error:
pass # assume does not exist
cur.execute(ddl_sql)
con.commit()
bind_data = [
(1, 's 1'),
(2, 's 2'),
(3, 's 3'),
(4, 's 4'),
]
for b in bind_data:
cur.execute("insert into %s (col1, col2) values (?, ?)" % (table_name,), b)
repeat_count = 5
for dummy in xrange(repeat_count):
"""
if dummy == 1:
import pdb
pdb.set_trace()
"""
cur.execute('select * from %s' % (table_name,))
result = cur.fetchone()
bind_data.sort()
self.assertEqual((1, 's 1'), result)
cur.execute(xddl_sql)
con.close()
def main():
# Some tests may use data files (without a full pathname)
# set current working directory to test directory if
# test is not being run from the same directory
testpath = os.path.dirname(__file__)
if testpath:
try:
os.chdir(testpath)
except OSError:
# this may be Jython 2.2 under OpenJDK...
if sys.version_info <= (2, 3):
pass
else:
raise
unittest.main()