def start(self,
database_name,
user,
readonly=False,
context=None,
close=False,
autocommit=False):
'''
Start transaction
'''
Database = backend.get('Database')
assert self.user is None
assert self.database is None
assert self.cursor is None
assert self.close is None
assert self.context is None
if not database_name:
database = Database().connect()
else:
database = Database(database_name).connect()
Flavor.set(Database.flavor)
cursor = database.cursor(readonly=readonly, autocommit=autocommit)
self.user = user
self.database = database
self.cursor = cursor
self.close = close
self.context = context or {}
self.create_records = {}
self.delete_records = {}
self.delete = {}
self.timestamp = {}
self.counter = 0
return _TransactionManager()
def test(self, hostname=None):
Flavor.set(self.flavor)
tables = [
'ir_model', 'ir_model_field', 'ir_ui_view', 'ir_ui_menu',
'res_user', 'res_group', 'ir_module', 'ir_module_dependency',
'ir_translation', 'ir_lang', 'ir_configuration'
]
sqlite_master = Table('sqlite_master')
select = sqlite_master.select(sqlite_master.name)
select.where = sqlite_master.type == 'table'
select.where &= sqlite_master.name.in_(tables)
with self._conn as conn:
cursor = conn.cursor()
try:
cursor.execute(*select)
except Exception:
return False
if len(cursor.fetchall()) != len(tables):
return False
if hostname:
configuration = Table('ir_configuration')
try:
cursor.execute(
*configuration.select(configuration.hostname))
except Exception:
return False
hostnames = {h for h, in cursor if h}
if hostnames and hostname not in hostnames:
return False
return True
def test_select_limit_offset(self):
query = self.table.select(limit=50, offset=10)
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" LIMIT 50 OFFSET 10')
self.assertEqual(query.params, ())
query.limit = None
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" OFFSET 10')
self.assertEqual(query.params, ())
query.offset = 0
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
flavor = Flavor(max_limit=-1)
Flavor.set(flavor)
try:
query.offset = None
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
query.offset = 0
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
query.offset = 10
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" LIMIT -1 OFFSET 10')
self.assertEqual(query.params, ())
finally:
Flavor.set(Flavor())
def test_select_union_flavor(self):
flavor = Flavor.get()
prev = flavor.quote_character
flavor.quote_character = '`'
self._test_select_union_quote('`')
flavor.quote_character = prev
Flavor.set(flavor)
def init(self):
from trytond.modules import get_module_info
Flavor.set(self.flavor)
with self.get_connection() as conn:
cursor = conn.cursor()
sql_file = os.path.join(os.path.dirname(__file__), 'init.sql')
with open(sql_file) as fp:
for line in fp.read().split(';'):
if (len(line) > 0) and (not line.isspace()):
cursor.execute(line)
ir_module = Table('ir_module')
ir_module_dependency = Table('ir_module_dependency')
for module in ('ir', 'res'):
state = 'not activated'
if module in ('ir', 'res'):
state = 'to activate'
info = get_module_info(module)
insert = ir_module.insert([
ir_module.create_uid, ir_module.create_date,
ir_module.name, ir_module.state
], [[0, CurrentTimestamp(), module, state]])
cursor.execute(*insert)
cursor.execute('SELECT last_insert_rowid()')
module_id, = cursor.fetchone()
for dependency in info.get('depends', []):
insert = ir_module_dependency.insert([
ir_module_dependency.create_uid,
ir_module_dependency.create_date,
ir_module_dependency.module,
ir_module_dependency.name,
], [[0, CurrentTimestamp(), module_id, dependency]])
cursor.execute(*insert)
conn.commit()
def start(self, database_name, user, readonly=False, context=None,
close=False, autocommit=False):
'''
Start transaction
'''
Database = backend.get('Database')
assert self.user is None
assert self.database is None
assert self.close is None
assert self.context is None
if not database_name:
database = Database().connect()
else:
database = Database(database_name).connect()
Flavor.set(Database.flavor)
self.user = user
self.database = database
self.readonly = readonly
self.connection = database.get_connection(readonly=readonly,
autocommit=autocommit)
self.close = close
self.context = context or {}
self.create_records = {}
self.delete_records = {}
self.delete = {}
self.timestamp = {}
self.counter = 0
self._datamanagers = []
return self
def work(options):
Flavor.set(backend.Database.flavor)
if not config.getboolean('queue', 'worker', default=False):
return
try:
processes = options.processes or cpu_count()
except NotImplementedError:
processes = 1
logger.info("start %d workers", processes)
mpool = MPool(processes, initializer, (options, ),
options.maxtasksperchild)
queues = [Queue(pool, mpool) for pool in initializer(options, False)]
tasks = TaskList()
timeout = options.timeout
try:
while True:
while len(tasks.filter()) >= processes:
time.sleep(0.1)
for queue in queues:
task_id, next_ = queue.pull(options.name)
timeout = min(next_ or options.timeout, timeout,
options.timeout)
if task_id:
tasks.append(queue.run(task_id))
break
else:
connections = [q.connection for q in queues]
connections, _, _ = select.select(connections, [], [], timeout)
for connection in connections:
connection.poll()
while connection.notifies:
connection.notifies.pop(0)
except KeyboardInterrupt:
mpool.close()
def test_no_as(self):
query = self.table.select(self.column.as_('foo'))
try:
Flavor.set(Flavor(no_as=True))
self.assertEqual(str(query), 'SELECT "a"."c" "foo" FROM "t" "a"')
self.assertEqual(query.params, ())
finally:
Flavor.set(Flavor())
def test_select_limit_offset(self):
try:
Flavor.set(Flavor(limitstyle='limit'))
query = self.table.select(limit=50, offset=10)
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" LIMIT 50 OFFSET 10')
self.assertEqual(query.params, ())
query.limit = None
self.assertEqual(str(query), 'SELECT * FROM "t" AS "a" OFFSET 10')
self.assertEqual(query.params, ())
query.offset = 0
self.assertEqual(str(query), 'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
Flavor.set(Flavor(limitstyle='limit', max_limit=-1))
query.offset = None
self.assertEqual(str(query), 'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
query.offset = 0
self.assertEqual(str(query), 'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
query.offset = 10
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" LIMIT -1 OFFSET 10')
self.assertEqual(query.params, ())
finally:
Flavor.set(Flavor())
def start(self,
database_name,
user,
readonly=False,
context=None,
close=False,
autocommit=False):
'''
Start transaction
'''
assert self.user is None
assert self.database is None
assert self.close is None
assert self.context is None
# Compute started_at before connect to ensure
# it is strictly before all transactions started after
# but it may be also before transactions started before
self.started_at = self.monotonic_time()
if not database_name:
database = backend.Database().connect()
else:
database = backend.Database(database_name).connect()
Flavor.set(backend.Database.flavor)
self.connection = database.get_connection(readonly=readonly,
autocommit=autocommit)
self.user = user
self.database = database
self.readonly = readonly
self.close = close
self.context = context or {}
self.create_records = {}
self.delete_records = {}
self.delete = {}
self.trigger_records = defaultdict(set)
self.timestamp = {}
self.counter = 0
self._datamanagers = []
self._sub_transactions = []
self._sub_transactions_to_close = []
if database_name:
from trytond.cache import Cache
try:
Cache.sync(self)
except BaseException:
self.stop(False)
raise
from trytond import iwc
try:
iwc.start(database_name)
except BaseException:
self.stop(False)
raise
return self
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(*self.args))
param = Flavor.get().param
def format(arg):
if isinstance(arg, basestring):
return param
else:
return str(arg)
return self._function + '(' + ', '.join(map(format, self.args)) + ')'
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(self.string, self.position, self.characters))
param = Flavor.get().param
def format(arg):
if isinstance(arg, basestring):
return param
else:
return str(arg)
return self._function + "(%s %s FROM %s)" % (self.position, format(self.characters), format(self.string))
def test_at_time_zone_mapping(self):
class MyAtTimeZone(Function):
_function = 'MY_TIMEZONE'
time_zone = AtTimeZone(self.table.c1, 'UTC')
flavor = Flavor(function_mapping={
AtTimeZone: MyAtTimeZone,
})
Flavor.set(flavor)
try:
self.assertEqual(str(time_zone), 'MY_TIMEZONE("c1", %s)')
self.assertEqual(time_zone.params, ('UTC',))
finally:
Flavor.set(Flavor())
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test',)
abs_ = Abs(self.table.c1)
flavor = Flavor(function_mapping={
Abs: MyAbs,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test',))
finally:
Flavor.set(Flavor())
def test_no_boolean(self):
true = Literal(True)
false = Literal(False)
self.assertEqual(str(true), '%s')
self.assertEqual(true.params, (True,))
self.assertEqual(str(false), '%s')
self.assertEqual(false.params, (False,))
try:
Flavor.set(Flavor(no_boolean=True))
self.assertEqual(str(true), '(1 = 1)')
self.assertEqual(str(false), '(1 != 1)')
self.assertEqual(true.params, ())
self.assertEqual(false.params, ())
finally:
Flavor.set(Flavor())
def __str__(self):
flavor = Flavor.get()
Mapping = flavor.function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(self.field, self.zone))
param = flavor.param
return '%s AT TIME ZONE %s' % (str(self.field), param)
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(*self.args))
return (self._function + '(' + ' '.join(
chain(*zip(self._keywords, map(self._format, self.args))))[1:] +
')')
def __str__(self):
quantifier = 'DISTINCT ' if self.distinct else ''
has_filter = Flavor.get().filter_
expression = self.expression
if self.filter_ and not has_filter:
from sql.conditionals import Case
expression = Case((self.filter_, self._case_expression))
order_by = ''
if self.order_by:
order_by = ' ORDER BY %s' % ', '.join(map(str, self.order_by))
aggregate = '%s(%s%s%s)' % (self._sql, quantifier, expression,
order_by)
within = ''
if self.within:
within = (' WITHIN GROUP (ORDER BY %s)' %
', '.join(map(str, self.within)))
filter_ = ''
if self.filter_ and has_filter:
filter_ = ' FILTER (WHERE %s)' % self.filter_
window = ''
if self.window:
if self.window.has_alias:
window = ' OVER "%s"' % self.window.alias
else:
window = ' OVER (%s)' % self.window
return aggregate + within + filter_ + window
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(*self.args))
param = Flavor.get().param
def format(arg):
if isinstance(arg, basestring):
return param
else:
return str(arg)
return (self._function + '('
+ ' '.join(chain(*zip(
self._keywords,
map(format, self.args))))[1:]
+ ')')
def __str__(self):
flavor = Flavor.get()
Mapping = flavor.function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(self.field, self.zone))
param = flavor.param
return str(self.field) + " AT TIME ZONE " + param
def _format(self, operand, param=None):
if param is None:
param = Flavor.get().param
if isinstance(operand, Expression):
return str(operand)
elif isinstance(operand, (Select, CombiningQuery)):
return '(' + operand + ')'
# return '(%s)' % operand
elif isinstance(operand, (list, tuple)):
subStrings = []
for o in operand:
subStrings.append(self._format(o, param))
joinedString = ""
for joiningIndex in range(len(subStrings)):
joinedString = joinedString + subStrings[joiningIndex]
if joiningIndex != len(subStrings) - 1:
joinedString = joinedString + ", "
return '(' + joinedString + ')'
elif isinstance(operand, array):
subStrings = (param,) * len(operand)
joinedString = ""
for joiningIndex in range(len(subStrings)):
joinedString = joinedString + subStrings[joiningIndex]
if joiningIndex != len(subStrings) - 1:
joinedString = joinedString + ", "
return '(' + joinedString + ')'
else:
return param
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(self.field, self.zone).params
if isinstance(self.zone, Expression):
return self.field.params + self.zone.params
else:
return self.field.params + (self.zone,)
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(*self.args))
return (self._function + '('
+ ' '.join(chain(*zip(
self._keywords,
map(self._format, self.args))))[1:]
+ ')')
def start(self,
database_name,
user,
readonly=False,
context=None,
close=False,
autocommit=False,
_nocache=False):
'''
Start transaction
'''
Database = backend.get('Database')
assert self.user is None
assert self.database is None
assert self.close is None
assert self.context is None
if not database_name:
database = Database().connect()
else:
database = Database(database_name).connect()
Flavor.set(Database.flavor)
self.connection = database.get_connection(readonly=readonly,
autocommit=autocommit)
self.user = user
self.database = database
self.readonly = readonly
self.close = close
self.context = context or {}
self.create_records = {}
self.delete_records = {}
self.delete = {}
self.timestamp = {}
self.counter = 0
self._datamanagers = []
self._sub_transactions = []
self._nocache = _nocache
if not _nocache:
from trytond.cache import Cache
try:
Cache.clean(database.name)
except BaseException:
self.stop(False)
raise
return self
def __str__(self):
param = Flavor.get().param
def format(value):
if isinstance(value, basestring):
return param
else:
return str(value)
return (self._conditional
+ '(' + ', '.join(map(format, self.values)) + ')')
def __str__(self):
flavor = Flavor.get()
Mapping = flavor.function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(self.field, self.zone))
if isinstance(self.zone, Expression):
zone = str(self.zone)
else:
zone = flavor.param
return '%s AT TIME ZONE %s' % (str(self.field), zone)
def _format(self, operand):
param = Flavor.get().param
if isinstance(operand, Expression):
return str(operand)
elif isinstance(operand, (Select, CombiningQuery)):
return '(%s)' % operand
elif isinstance(operand, (list, tuple)):
return '(' + ', '.join(self._format(o) for o in operand) + ')'
else:
return param
def test_select_offset_fetch(self):
try:
Flavor.set(Flavor(limitstyle='fetch'))
query = self.table.select(limit=50, offset=10)
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" '
'OFFSET (10) ROWS FETCH FIRST (50) ROWS ONLY')
self.assertEqual(query.params, ())
query.limit = None
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" OFFSET (10) ROWS')
self.assertEqual(query.params, ())
query.offset = 0
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
finally:
Flavor.set(Flavor())
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(*self.args).params
p = []
for arg in self.args:
if isinstance(arg, Expression):
p.extend(arg.params)
else:
p.append(arg)
return tuple(p)
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(self.string, self.position, self.characters).params
p = []
for arg in (self.characters, self.string):
if isinstance(arg, basestring):
p.append(arg)
elif hasattr(arg, 'params'):
p.extend(arg.params)
return tuple(p)
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(*self.args).params
p = ()
for arg in (self.characters, self.string):
if isinstance(arg, basestring):
p += (arg,)
elif hasattr(arg, "params"):
p += arg.params
return p
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(self.string, self.position, self.characters).params
p = []
for arg in (self.characters, self.string):
if isinstance(arg, basestring):
p.append(arg)
elif hasattr(arg, 'params'):
p.extend(arg.params)
return tuple(p)
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(*self.args).params
p = []
for arg in self.args:
if isinstance(arg, Expression):
p.extend(arg.params)
else:
p.append(arg)
return tuple(p)
def test_no_null_ordering(self):
try:
Flavor.set(Flavor(null_ordering=False))
exp = NullsFirst(self.column)
self.assertEqual(str(exp),
'CASE WHEN ("c" IS NULL) THEN %s ELSE %s END ASC, "c"')
self.assertEqual(exp.params, (0, 1))
exp = NullsFirst(Desc(self.column))
self.assertEqual(str(exp),
'CASE WHEN ("c" IS NULL) THEN %s ELSE %s END ASC, "c" DESC')
self.assertEqual(exp.params, (0, 1))
exp = NullsLast(Literal(2))
self.assertEqual(str(exp),
'CASE WHEN (%s IS NULL) THEN %s ELSE %s END ASC, %s')
self.assertEqual(exp.params, (2, 1, 0, 2))
finally:
Flavor.set(Flavor())
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(*self.args))
subStrings = map(self._format, self.args)
joinedString = ""
for joiningIndex in range(len(subStrings)):
joinedString = joinedString + subStrings[joiningIndex]
if joiningIndex != len(subStrings) - 1:
joinedString = joinedString + ", "
return self._function + '(' + joinedString + ')'
def create_sql_sequence(self, number_next=None):
'Create the SQL sequence'
cursor = Transaction().connection.cursor()
param = Flavor.get().param
if self.type != 'incremental':
return
if number_next is None:
number_next = self.number_next
cursor.execute('CREATE SEQUENCE "' + self._sql_sequence_name
+ '" INCREMENT BY ' + param + ' START WITH ' + param,
(self.number_increment, number_next))
def params(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return Mapping(*self.args).params
p = ()
for arg in self.args:
if isinstance(arg, Expression):
p += arg.params
else:
p += (arg,)
return p
def create_sql_sequence(self, number_next=None):
'Create the SQL sequence'
cursor = Transaction().connection.cursor()
param = Flavor.get().param
if self.type != 'incremental':
return
if number_next is None:
number_next = self.number_next
cursor.execute('CREATE SEQUENCE "' + self._sql_sequence_name
+ '" INCREMENT BY ' + param + ' START WITH ' + param,
(self.number_increment, number_next))
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test',)
class MyOverlay(FunctionKeyword):
_function = 'MY_OVERLAY'
_keywords = ('', 'PLACING', 'FROM', 'FOR')
class MyCurrentTime(FunctionNotCallable):
_function = 'MY_CURRENT_TIME'
class MyTrim(Trim):
_function = 'MY_TRIM'
abs_ = Abs(self.table.c1)
overlay = Overlay(self.table.c1, 'test', 2)
current_time = CurrentTime()
trim = Trim(' test ')
flavor = Flavor(function_mapping={
Abs: MyAbs,
Overlay: MyOverlay,
CurrentTime: MyCurrentTime,
Trim: MyTrim,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test',))
self.assertEqual(str(overlay),
'MY_OVERLAY("c1" PLACING %s FROM %s)')
self.assertEqual(overlay.params, ('test', 2))
self.assertEqual(str(current_time), 'MY_CURRENT_TIME')
self.assertEqual(current_time.params, ())
self.assertEqual(str(trim), 'MY_TRIM(BOTH %s FROM %s)')
self.assertEqual(trim.params, (' ', ' test ',))
finally:
Flavor.set(Flavor())
def _format(operand):
param = Flavor.get().param
if isinstance(operand, Column):
return str(operand)
elif isinstance(operand, Select):
return '(%s)' % operand
elif isinstance(operand, (list, tuple)):
return '(' + ', '.join((param,) * len(operand)) + ')'
elif operand is None:
return ''
else:
return param
def work(options):
Flavor.set(backend.Database.flavor)
if not config.getboolean('queue', 'worker', default=False):
return
try:
processes = options.processes or cpu_count()
except NotImplementedError:
processes = 1
logger.info("start %d workers", processes)
mpool = MPool(
processes, initializer, (options.database_names,),
options.maxtasksperchild)
queues = [Queue(name, mpool) for name in options.database_names]
tasks = TaskList()
timeout = options.timeout
selector = selectors.DefaultSelector()
for queue in queues:
selector.register(queue.connection, selectors.EVENT_READ)
try:
while True:
while len(tasks.filter()) >= processes:
time.sleep(0.1)
for queue in queues:
task_id, next_ = queue.pull(options.name)
timeout = min(
next_ or options.timeout, timeout, options.timeout)
if task_id:
tasks.append(queue.run(task_id))
break
else:
for key, _ in selector.select(timeout=timeout):
connection = key.fileobj
connection.poll()
while connection.notifies:
connection.notifies.pop(0)
except KeyboardInterrupt:
mpool.close()
finally:
selector.close()
def __str__(self):
flavor = Flavor.get()
Mapping = flavor.function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(self.string, self.position, self.characters))
param = flavor.param
def format(arg):
if isinstance(arg, basestring):
return param
else:
return str(arg)
return self._function + "(" + self.position + " " + format(self.characters) + " FROM " + format(self.string) + ")"
def _format(self, operand, param=None):
if param is None:
param = Flavor.get().param
if isinstance(operand, Expression):
return str(operand)
elif isinstance(operand, (Select, CombiningQuery)):
return "(%s)" % operand
elif isinstance(operand, (list, tuple)):
return "(" + ", ".join(self._format(o, param) for o in operand) + ")"
elif isinstance(operand, array):
return "(" + ", ".join((param,) * len(operand)) + ")"
else:
return param
def test_no_as(self):
query = self.table.select(self.table.c)
try:
Flavor.set(Flavor(no_as=True))
self.assertEqual(str(query), 'SELECT "a"."c" FROM "t" "a"')
self.assertEqual(query.params, ())
finally:
Flavor.set(Flavor())
def __str__(self):
flavor = Flavor.get()
Mapping = flavor.function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(self.string, self.position, self.characters))
param = flavor.param
def format(arg):
if isinstance(arg, basestring):
return param
else:
return str(arg)
return self._function + '(%s %s FROM %s)' % (
self.position, format(self.characters), format(self.string))
def params(self):
has_filter = Flavor.get().filter_
p = []
if self.filter_ and not has_filter:
p.extend(self.filter_.params)
p.extend(self._case_expression.params)
else:
p.extend(self.expression.params)
if self.within:
for expression in self.within:
p.extend(expression.params)
if self.filter_ and has_filter:
p.extend(self.filter_.params)
return tuple(p)
def _format(self, operand, param=None):
if param is None:
param = Flavor.get().param
if isinstance(operand, Expression):
return str(operand)
elif isinstance(operand, (Select, CombiningQuery)):
return '(%s)' % operand
elif isinstance(operand, (list, tuple)):
return '(' + ', '.join(self._format(o, param)
for o in operand) + ')'
elif isinstance(operand, array):
return '(' + ', '.join((param, ) * len(operand)) + ')'
else:
return param
def __str__(self):
case = 'CASE '
param = Flavor.get().param
for cond, result in self.whens:
case += 'WHEN ' + str(cond)
if isinstance(result, basestring):
result = param
case += ' THEN ' + str(result) + ' '
if self.else_ is not None:
else_ = self.else_
if isinstance(self.else_, basestring):
else_ = param
case += 'ELSE ' + str(else_) + ' '
case += 'END'
return case
def __str__(self):
Mapping = Flavor.get().function_mapping.get(self.__class__)
if Mapping:
return str(Mapping(*self.args))
subStrings = list(chain(*zip(
self._keywords,
map(self._format, self.args))))
joinedString = ""
for joiningIndex in range(len(subStrings)):
joinedString = joinedString + subStrings[joiningIndex]
if joiningIndex != len(subStrings) - 1:
joinedString = joinedString + " "
return (self._function + '('
+ joinedString[1:]
+ ')')
def test_filter(self):
flavor = Flavor(filter_=True)
Flavor.set(flavor)
try:
avg = Avg(self.table.a + 1, filter_=self.table.a > 0)
self.assertEqual(str(avg),
'AVG(("a" + %s)) FILTER (WHERE ("a" > %s))')
self.assertEqual(avg.params, (1, 0))
finally:
Flavor.set(Flavor())
def test_mod_paramstyle(self):
flavor = Flavor(paramstyle='format')
Flavor.set(flavor)
try:
mod = Mod(self.table.c1, self.table.c2)
self.assertEqual(str(mod), '("c1" %% "c2")')
self.assertEqual(mod.params, ())
finally:
Flavor.set(Flavor())
flavor = Flavor(paramstyle='qmark')
Flavor.set(flavor)
try:
mod = Mod(self.table.c1, self.table.c2)
self.assertEqual(str(mod), '("c1" % "c2")')
self.assertEqual(mod.params, ())
finally:
Flavor.set(Flavor())
def __str__(self):
flavor = Flavor.get()
param = flavor.param
function = {
'BOTH': 'TRIM',
'LEADING': 'LTRIM',
'TRAILING': 'RTRIM',
}[self.position]
def format(arg):
if isinstance(arg, str):
return param
else:
return str(arg)
return function + '(%s, %s)' % (
format(self.string), format(self.characters))
def update_sql_sequence(self, number_next=None):
'Update the SQL sequence'
TableHandler = backend.get('TableHandler')
cursor = Transaction().connection.cursor()
param = Flavor.get().param
exist = TableHandler.sequence_exist(self._sql_sequence_name)
if self.type != 'incremental':
if exist:
self.delete_sql_sequence()
return
if not exist:
self.create_sql_sequence(number_next)
return
if number_next is None:
number_next = self.number_next
cursor.execute('ALTER SEQUENCE "' + self._sql_sequence_name
+ '" INCREMENT BY ' + param + ' RESTART WITH ' + param,
(self.number_increment, number_next))
def test_at_time_zone_mapping(self):
class MyAtTimeZone(Function):
_function = 'MY_TIMEZONE'
time_zone = AtTimeZone(self.table.c1, 'UTC')
flavor = Flavor(function_mapping={
AtTimeZone: MyAtTimeZone,
})
Flavor.set(flavor)
try:
self.assertEqual(str(time_zone), 'MY_TIMEZONE("c1", %s)')
self.assertEqual(time_zone.params, ('UTC', ))
finally:
Flavor.set(Flavor())
def test_no_boolean(self):
true = Literal(True)
false = Literal(False)
self.assertEqual(str(true), '%s')
self.assertEqual(true.params, (True, ))
self.assertEqual(str(false), '%s')
self.assertEqual(false.params, (False, ))
try:
Flavor.set(Flavor(no_boolean=True))
self.assertEqual(str(true), '(1 = 1)')
self.assertEqual(str(false), '(1 != 1)')
self.assertEqual(true.params, ())
self.assertEqual(false.params, ())
finally:
Flavor.set(Flavor())
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test', )
abs_ = Abs(self.table.c1)
flavor = Flavor(function_mapping={
Abs: MyAbs,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test', ))
finally:
Flavor.set(Flavor())
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test', )
class MyOverlay(FunctionKeyword):
_function = 'MY_OVERLAY'
_keywords = ('', 'PLACING', 'FROM', 'FOR')
class MyCurrentTime(FunctionNotCallable):
_function = 'MY_CURRENT_TIME'
class MyTrim(Trim):
_function = 'MY_TRIM'
abs_ = Abs(self.table.c1)
overlay = Overlay(self.table.c1, 'test', 2)
current_time = CurrentTime()
trim = Trim(' test ')
flavor = Flavor(
function_mapping={
Abs: MyAbs,
Overlay: MyOverlay,
CurrentTime: MyCurrentTime,
Trim: MyTrim,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test', ))
self.assertEqual(str(overlay),
'MY_OVERLAY("c1" PLACING %s FROM %s)')
self.assertEqual(overlay.params, ('test', 2))
self.assertEqual(str(current_time), 'MY_CURRENT_TIME')
self.assertEqual(current_time.params, ())
self.assertEqual(str(trim), 'MY_TRIM(BOTH %s FROM %s)')
self.assertEqual(trim.params, (
' ',
' test ',
))
finally:
Flavor.set(Flavor())
def test_select_offset_fetch(self):
try:
Flavor.set(Flavor(limitstyle='fetch'))
query = self.table.select(limit=50, offset=10)
self.assertEqual(
str(query), 'SELECT * FROM "t" AS "a" '
'OFFSET (10) ROWS FETCH FIRST (50) ROWS ONLY')
self.assertEqual(query.params, ())
query.limit = None
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a" OFFSET (10) ROWS')
self.assertEqual(query.params, ())
query.offset = 0
self.assertEqual(str(query), 'SELECT * FROM "t" AS "a"')
self.assertEqual(query.params, ())
finally:
Flavor.set(Flavor())
def test_no_null_ordering(self):
try:
Flavor.set(Flavor(null_ordering=False))
exp = NullsFirst(self.column)
self.assertEqual(
str(exp),
'CASE WHEN ("c" IS NULL) THEN %s ELSE %s END ASC, "c"')
self.assertEqual(exp.params, (0, 1))
exp = NullsFirst(Desc(self.column))
self.assertEqual(
str(exp),
'CASE WHEN ("c" IS NULL) THEN %s ELSE %s END ASC, "c" DESC')
self.assertEqual(exp.params, (0, 1))
exp = NullsLast(Literal(2))
self.assertEqual(
str(exp), 'CASE WHEN (%s IS NULL) THEN %s ELSE %s END ASC, %s')
self.assertEqual(exp.params, (2, 1, 0, 2))
finally:
Flavor.set(Flavor())
