def test_adv_select_values(self):
s = SQLGenerator()
self.assertEqual(s.adv_select(['column'], [('test', 't')], {}),
'SELECT column FROM test AS t')
self.assertEqual(
s.adv_select(['column'], [('test', 't')], {'nom': 'dupont'}),
'SELECT column FROM test AS t WHERE nom = %(nom)s')
def test_where_functions(self):
s = SQLGenerator()
params = {
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
self.assertEqual(
s.where(params),
'age = YEARS(%(date)s) AND nom = %(nom)s AND prenom = %(prenom)s')
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01'
})
params = {
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
self.assertEqual(
s.where(params, 'x.id = y.id'),
'x.id = y.id AND age = YEARS(%(date)s) AND nom = %(nom)s AND prenom = %(prenom)s'
)
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01'
})
def test_delete_many_values(self):
s = SQLGenerator()
params = {'nom': 'dupont', 'eid': '(1, 2, 3)'}
self.assertEqual(
s.delete_many('test', params),
'DELETE FROM test WHERE eid IN (1, 2, 3) AND nom = %(nom)s')
self.assertEqual(params, {'nom': 'dupont'})
def test_set_values(self):
s = SQLGenerator()
self.assertEqual(s.set(['nom']), 'nom = %(nom)s')
self.assertEqual(s.set(['nom', 'prenom']),
'nom = %(nom)s, prenom = %(prenom)s')
params = {'nom': 'dupont', 'prenom': 'jean'}
self.assertEqual(s.set(params), 'nom = %(nom)s, prenom = %(prenom)s')
self.assertEqual(params, {'nom': 'dupont', 'prenom': 'jean'})
def test_delete_values(self):
s = SQLGenerator()
self.assertEqual(s.delete('test', {'nom': 'dupont'}),
'DELETE FROM test WHERE nom = %(nom)s')
self.assertEqual(
s.delete('test', {
'nom': 'dupont',
'prenom': 'jean'
}), 'DELETE FROM test WHERE nom = %(nom)s AND prenom = %(prenom)s')
def test_select_values(self):
s = SQLGenerator()
self.assertEqual(s.select('test', {}), 'SELECT * FROM test')
self.assertEqual(s.select('test', {'nom': 'dupont'}),
'SELECT * FROM test WHERE nom = %(nom)s')
self.assertEqual(
s.select('test', {
'nom': 'dupont',
'prenom': 'jean'
}),
'SELECT * FROM test WHERE nom = %(nom)s AND prenom = %(prenom)s')
def test_set_functions(self):
s = SQLGenerator()
params = {
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
self.assertEqual(
s.set(params),
'age = YEARS(%(date)s), nom = %(nom)s, prenom = %(prenom)s')
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01'
})
def test_update_values(self):
s = SQLGenerator()
self.assertEqual(
s.update('test', {
'id': '001',
'nom': 'dupont'
}, ['id']), 'UPDATE test SET nom = %(nom)s WHERE id = %(id)s')
self.assertEqual(
s.update('test', {
'id': '001',
'nom': 'dupont',
'prenom': 'jean'
}, ['id']),
'UPDATE test SET nom = %(nom)s, prenom = %(prenom)s WHERE id = %(id)s'
)
def test_delete_functions(self):
s = SQLGenerator()
params = {
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
self.assertEqual(
s.delete('test', params),
'DELETE FROM test WHERE age = YEARS(%(date)s) '
'AND nom = %(nom)s AND prenom = %(prenom)s')
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01'
})
def test_adv_select_functions(self):
s = SQLGenerator()
params = {
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
self.assertEqual(
s.adv_select(['column'], [('test', 't')], params),
'SELECT column FROM test AS t WHERE age = YEARS(%(date)s) '
'AND nom = %(nom)s AND prenom = %(prenom)s')
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01'
})
def test_insert_functions(self):
s = SQLGenerator()
params = {
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
sqlstr = s.insert('test', params)
self.assertEqual(
sqlstr, 'INSERT INTO test ( age, nom, prenom ) VALUES '
'( YEARS(%(date)s), %(nom)s, %(prenom)s )')
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01'
})
def test_update_functions(self):
s = SQLGenerator()
params = {
'id': '001',
'nom': 'dupont',
'prenom': 'jean',
'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')
}
self.assertEqual(
s.update('test', params, ['id']),
'UPDATE test SET age = YEARS(%(date)s), nom = %(nom)s, '
'prenom = %(prenom)s WHERE id = %(id)s')
self.assertEqual(params, {
'nom': 'dupont',
'prenom': 'jean',
'date': '2013/01/01',
'id': '001'
})
def __init__(self, source_config, repairing=False):
try:
self.dbdriver = source_config['db-driver'].lower()
dbname = source_config['db-name']
except KeyError as e:
raise ConfigurationError(
'missing some expected entries in sources file (do you have '
'a db-driver and a db-name keys?), error: %s' % e)
dbhost = source_config.get('db-host')
port = source_config.get('db-port')
dbport = port and int(port) or None
dbuser = source_config.get('db-user')
dbpassword = source_config.get('db-password')
dbencoding = source_config.get('db-encoding', 'UTF-8')
dbextraargs = source_config.get('db-extra-arguments')
dbnamespace = source_config.get('db-namespace')
self.dbhelper = logilab_database.get_db_helper(self.dbdriver)
self.dbhelper.record_connection_info(dbname, dbhost, dbport, dbuser,
dbpassword, dbextraargs,
dbencoding, dbnamespace)
self.sqlgen = SQLGenerator()
# copy back some commonly accessed attributes
dbapi_module = self.dbhelper.dbapi_module
self.OperationalError = dbapi_module.OperationalError
self.InterfaceError = dbapi_module.InterfaceError
self.DbapiError = dbapi_module.Error
self._binary = self.dbhelper.binary_value
self._process_value = dbapi_module.process_value
self._dbencoding = dbencoding
if self.dbdriver == 'sqlite':
self.cnx_wrap = SqliteConnectionWrapper
self.dbhelper.dbname = abspath(self.dbhelper.dbname)
else:
self.cnx_wrap = ConnectionWrapper
if not repairing:
statement_timeout = int(
source_config.get('db-statement-timeout', 0))
if statement_timeout > 0:
def set_postgres_timeout(cnx):
cnx.cursor().execute('SET statement_timeout to %d' %
statement_timeout)
cnx.commit()
postgres_hooks = SQL_CONNECT_HOOKS['postgres']
postgres_hooks.append(set_postgres_timeout)
def test_where_values(self):
s = SQLGenerator()
self.assertEqual(s.where(['nom']), 'nom = %(nom)s')
self.assertEqual(s.where(['nom', 'prenom']),
'nom = %(nom)s AND prenom = %(prenom)s')
self.assertEqual(
s.where(['nom', 'prenom'], 'x.id = y.id'),
'x.id = y.id AND nom = %(nom)s AND prenom = %(prenom)s')
params = {'nom': 'dupont', 'prenom': 'jean'}
self.assertEqual(s.where(params),
'nom = %(nom)s AND prenom = %(prenom)s')
self.assertEqual(
s.where(params, 'x.id = y.id'),
'x.id = y.id AND nom = %(nom)s AND prenom = %(prenom)s')
def test_insert_values(self):
s = SQLGenerator()
params = {'nom': 'dupont'}
sqlstr = s.insert('test', params)
self.assertEqual(sqlstr, 'INSERT INTO test ( nom ) VALUES ( %(nom)s )')
self.assertEqual(params, {'nom': 'dupont'})