def createTables(self):
self.createdTables = True
try:
try:
os.remove(self.db_filename)
except OSError, e:
pass
self.factory = factory.Sqlite3ConnectionFactory(self.db_filename)
dt = DatabaseTemplate(self.factory)
dt.execute("DROP TABLE IF EXISTS animal")
dt.execute("DROP TABLE IF EXISTS account")
dt.execute("""
CREATE TABLE animal (
id serial PRIMARY KEY,
name VARCHAR(11)
)
""")
dt.execute("""
CREATE TABLE account (
id serial PRIMARY KEY,
account_num VARCHAR(11),
balance FLOAT(10)
)
""")
self.factory.commit()
def createTables(self):
self.createdTables = True
try:
self.factory = factory.MySQLConnectionFactory("springpython", "springpython", "localhost", "springpython")
dt = DatabaseTemplate(self.factory)
dt.execute("DROP TABLE IF EXISTS animal")
dt.execute("""
CREATE TABLE animal (
id serial PRIMARY KEY,
name VARCHAR(11),
category VARCHAR(20),
population SMALLINT
) ENGINE=innodb
""")
self.factory.commit()
except Exception, e:
print("""
!!! Can't run MySQLDatabaseTemplateTestCase !!!
This assumes you have executed some step like:
% sudo apt-get install mysql (Ubuntu)
% apt-get install mysql (Debian)
And then created a database for the spring python user:
% mysql -uroot
mysql> DROP DATABASE IF EXISTS springpython;
mysql> CREATE DATABASE springpython;
mysql> GRANT ALL ON springpython.* TO springpython@localhost IDENTIFIED BY 'springpython';
That should setup the springpython user to be able to create tables as needed for these test cases.
""")
raise e
def testQueryingOracleWithValidlyFormattedArguments(self):
cursor = self.mock()
cursor.expects(once()).method("execute")
cursor.expects(once()).method("fetchall").will(return_value([("workDir", "reloadDir", "archiveDir", "oid1")]))
conn = self.mock()
conn.expects(once()).method("cursor").will(return_value(cursor))
conn.expects(once()).method("close")
sys.modules["cx_Oracle"] = self.mock()
sys.modules["cx_Oracle"].expects(once()).method("connect").will(return_value(conn))
connection_factory = factory.cxoraConnectionFactory(username="******", password="******", hostname="localhost", db="mock")
dt = DatabaseTemplate(connection_factory)
dt.query("""
SELECT
impcarrcfg.paystat_work_dir,
impcarrcfg.paystat_reload_dir,
impcarrcfg.paystat_archive_dir,
impcarrcfg.oid
FROM impcarrcfg, carr, lklabelsys
WHERE (lklabelsys.oid = impcarrcfg.lklabelsys_oid)
and (carr.oid = impcarrcfg.carr_oid )
and (carr.oid = :carr_oid and lklabelsys.oid = :lklabelsys_oid)
""",
{'carr_oid':5, 'lklabelsys_oid':5},
testSupportClasses.ImpFilePropsRowMapper())
del(sys.modules["cx_Oracle"])
def createTables(self):
self.createdTables = True
try:
try:
os.remove(self.db_filename)
except OSError:
pass
self.factory = factory.Sqlite3ConnectionFactory(self.db_filename)
dt = DatabaseTemplate(self.factory)
dt.execute("DROP TABLE IF EXISTS animal")
dt.execute("""
CREATE TABLE animal (
id serial PRIMARY KEY,
name VARCHAR(11),
category VARCHAR(20),
population integer
)
""")
self.factory.commit()
except Exception, e:
print("""
!!! Can't run SqliteDatabaseTemplateTestCase !!!
""")
raise e
def testIoCGeneralQueryWithDictionaryRowMapper(self):
appContext = ApplicationContext(XMLConfig("support/databaseTestSqliteApplicationContext.xml"))
factory = appContext.get_object("connection_factory")
databaseTemplate = DatabaseTemplate(factory)
databaseTemplate.execute("DROP TABLE IF EXISTS animal")
databaseTemplate.execute("""
CREATE TABLE animal (
id serial PRIMARY KEY,
name VARCHAR(11),
category VARCHAR(20),
population integer
)
""")
factory.commit()
databaseTemplate.execute("DELETE FROM animal")
factory.commit()
self.assertEquals(len(databaseTemplate.query_for_list("SELECT * FROM animal")), 0)
databaseTemplate.execute("INSERT INTO animal (name, category, population) VALUES ('snake', 'reptile', 1)")
databaseTemplate.execute("INSERT INTO animal (name, category, population) VALUES ('racoon', 'mammal', 0)")
databaseTemplate.execute ("INSERT INTO animal (name, category, population) VALUES ('black mamba', 'kill_bill_viper', 1)")
databaseTemplate.execute ("INSERT INTO animal (name, category, population) VALUES ('cottonmouth', 'kill_bill_viper', 1)")
factory.commit()
self.assertEquals(len(databaseTemplate.query_for_list("SELECT * FROM animal")), 4)
results = databaseTemplate.query("select * from animal", rowhandler=DictionaryRowMapper())
def load_user(self, username):
dt = DatabaseTemplate(self.dataSource)
users = dt.query(self.users_by_username_query, (username, ),
self.UsersByUsernameMapping())
if len(users) == 0:
raise UsernameNotFoundException("User not found")
user = users[
0] # First item in list, first column of tuple, containing no GrantedAuthority[]
dbAuths = dt.query(self.auth_by_username_query, (user.username, ),
self.AuthoritiesByUsernameMapping(self.role_prefix))
self.add_custom_authorities(user.username, dbAuths)
if len(dbAuths) == 0:
raise UsernameNotFoundException("User has no GrantedAuthority")
auths = [dbAuth for dbAuth in dbAuths]
return_username = user.username
if not self.username_based_pk:
return_username = username
self.logger.debug("Just fetched %s from the database" % user)
return User(return_username, user.password, user.enabled, True, True,
True, auths)
def testInsertingIntoOracleWithInvalidlyFormattedArgumentsWithInsertApi(self):
sys.modules["cx_Oracle"] = self.mock()
connection_factory = factory.cxoraConnectionFactory(username="******", password="******", hostname="localhost", db="mock")
dt = DatabaseTemplate(connection_factory)
self.assertRaises(InvalidArgumentType, dt.insert_and_return_id,
"INSERT INTO T_UNIT (F_UNIT_PK, F_UNIT_ID, F_NAME) VALUES (?, ?, ?)",
(1,1,1))
del(sys.modules["cx_Oracle"])
def setUp(self):
if not self.createdTables:
self.createTables()
self.createTables()
self.dt = DatabaseTemplate(self.factory)
self.dt.execute("DELETE FROM animal")
self.dt.execute("DELETE FROM account")
self.factory.commit()
self.assertEquals(len(self.dt.query_for_list("SELECT * FROM animal")), 0)
self.transactionManager = ConnectionFactoryTransactionManager(self.factory)
self.transactionTemplate = TransactionTemplate(self.transactionManager)
def testIoCGeneralQuery(self):
appContext = ApplicationContext(XMLConfig("support/databaseTestApplicationContext.xml"))
mockConnectionFactory = appContext.get_object("mockConnectionFactory")
mockConnectionFactory.stubConnection.mockCursor = self.mock
self.mock.expects(once()).method("execute")
self.mock.expects(once()).method("fetchall").will(return_value([("me", "myphone")]))
databaseTemplate = DatabaseTemplate(connection_factory = mockConnectionFactory)
results = databaseTemplate.query("select * from foobar", rowhandler=testSupportClasses.SampleRowMapper())
def setUp(self):
if not self.createdTables:
self.createTables()
self.databaseTemplate = DatabaseTemplate(self.factory)
self.databaseTemplate.execute("DELETE FROM animal")
self.factory.commit()
self.assertEquals(len(self.databaseTemplate.query_for_list("SELECT * FROM animal")), 0)
self.databaseTemplate.execute("INSERT INTO animal (name, category, population) VALUES ('snake', 'reptile', 1)")
self.databaseTemplate.execute("INSERT INTO animal (name, category, population) VALUES ('racoon', 'mammal', 0)")
self.databaseTemplate.execute ("INSERT INTO animal (name, category, population) VALUES ('black mamba', 'kill_bill_viper', 1)")
self.databaseTemplate.execute ("INSERT INTO animal (name, category, population) VALUES ('cottonmouth', 'kill_bill_viper', 1)")
self.factory.commit()
self.assertEquals(len(self.databaseTemplate.query_for_list("SELECT * FROM animal")), 4)
def createTables(self):
self.createdTables = True
try:
self.factory = factory.SQLServerConnectionFactory(DRIVER="{SQL Server}",
SERVER="localhost", DATABASE="springpython", UID="springpython", PWD="cdZS*RQRBdc9a")
dt = DatabaseTemplate(self.factory)
dt.execute("""IF EXISTS(SELECT 1 FROM sys.tables WHERE name='animal')
DROP TABLE animal""")
dt.execute("""
CREATE TABLE animal (
id INTEGER IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(11),
category VARCHAR(20),
population INTEGER
)
""")
self.factory.commit()
except Exception, e:
print("""
!!! Can't run SQLServerDatabaseTemplateTestCase !!!
This assumes you have installed pyodbc (http://code.google.com/p/pyodbc/).
And then created an SQL Server database for the 'springpython'
login and user.
USE master;
IF EXISTS(SELECT 1 FROM sys.databases WHERE name='springpython')
DROP DATABASE springpython;
IF EXISTS(SELECT 1 FROM sys.syslogins WHERE name='springpython')
DROP LOGIN springpython;
IF EXISTS(SELECT 1 FROM sys.sysusers WHERE name='springpython')
DROP USER springpython;
CREATE DATABASE springpython;
CREATE LOGIN springpython WITH PASSWORD='******', DEFAULT_DATABASE=springpython;
USE springpython;
CREATE USER springpython FOR LOGIN springpython;
EXEC sp_addrolemember 'db_owner', 'springpython';
From here on, you should be able to connect into SQL Server and run SQL scripts.
""")
raise e
def setupDatabase():
"""Figure out what type of database exists, and then set it up."""
connectionFactory = tryMySQL()
if connectionFactory is None:
raise Exception(
"+++ Could not setup MySQL. We don't support any others yet.")
databaseTemplate = DatabaseTemplate(connectionFactory)
for sqlStatement in [
line.strip() for line in open("db/populateDB.txt").readlines()
if line.strip() != ""
]:
databaseTemplate.execute(sqlStatement)
print "+++ Database is setup."
def createTables(self):
self.createdTables = True
try:
self.factory = factory.PgdbConnectionFactory("springpython", "springpython", "localhost", "springpython")
dt = DatabaseTemplate(self.factory)
dt.execute("DROP TABLE IF EXISTS animal")
dt.execute("""
CREATE TABLE animal (
id serial PRIMARY KEY,
name VARCHAR(11)
)
""")
dt.execute("DROP TABLE IF EXISTS account")
dt.execute("""
CREATE TABLE account (
id serial PRIMARY KEY,
account_num VARCHAR(11),
balance FLOAT(10)
)
""")
self.factory.commit()
except Exception, e:
print("""
!!! Can't run PostGreSQLTransactionTestCase !!!
This assumes you have executed some step like:
% sudo apt-get install postgresql (Ubuntu)
% apt-get install postgresql (Debian)
Next, you need to let PostGreSQL's accounts be decoupled from the system accounts.
Find pg_hba.conf underneath /etc and add something like this:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all <your network> <yournetworkmask> md5
Then, restart it.
% sudo /etc/init.d/postgresql restart (Ubuntu)
Then create a user database to match this account.
% sudo -u postgres psql -f support/setupPostGreSQLSpringPython.sql
From here on, you should be able to connect into PSQL and run SQL scripts.
""")
raise e
def testQueryingOracleWithInvalidlyFormattedArguments(self):
sys.modules["cx_Oracle"] = self.mock()
connection_factory = factory.cxoraConnectionFactory(username="******", password="******", hostname="localhost", db="mock")
dt = DatabaseTemplate(connection_factory)
self.assertRaises(InvalidArgumentType, dt.query, """
SELECT
impcarrcfg.paystat_work_dir,
impcarrcfg.paystat_reload_dir,
impcarrcfg.paystat_archive_dir,
impcarrcfg.oid
FROM impcarrcfg, carr, lklabelsys
WHERE (lklabelsys.oid = impcarrcfg.lklabelsys_oid)
and (carr.oid = impcarrcfg.carr_oid )
and (carr.oid = ? and lklabelsys.oid = ?)
""", (5, 5), testSupportClasses.ImpFilePropsRowMapper())
del(sys.modules["cx_Oracle"])
def __init__(self, factory):
self.logger = logging.getLogger(
"springpythontest.testSupportClasses.Bank")
self.dt = DatabaseTemplate(factory)
def testIoCGeneralQuery(self):
appContext = ApplicationContext(XMLConfig("support/databaseTestSQLServerApplicationContext.xml"))
factory = appContext.get_object("connection_factory")
databaseTemplate = DatabaseTemplate(factory)
results = databaseTemplate.query("select * from animal", rowhandler=testSupportClasses.SampleRowMapper())
def testIoCGeneralQueryWithDictionaryRowMapper(self):
appContext = ApplicationContext(XMLConfig("support/databaseTestMySQLApplicationContext.xml"))
factory = appContext.get_object("connection_factory")
databaseTemplate = DatabaseTemplate(factory)
results = databaseTemplate.query("select * from animal", rowhandler=DictionaryRowMapper())
def setUp(self):
self.mock = self.mock()
connection_factory = testSupportClasses.StubDBFactory()
connection_factory.stubConnection.mockCursor = self.mock
self.databaseTemplate = DatabaseTemplate(connection_factory)
def testProgrammaticallyInstantiatingAnAbstractDatabaseTemplate(self):
emptyTemplate = DatabaseTemplate()
self.assertRaises(AttributeError, emptyTemplate.query, "sql query shouldn't work", None)
def __init__(self, factory):
self.logger = logging.getLogger(
"springpythontest.testSupportClasses.TransactionalBankWithLotsOfTransactionalArguments"
)
self.dt = DatabaseTemplate(factory)
