def test_function_random(self):
"Test RANDOM function"
cursor = Transaction().connection.cursor()
cursor.execute(*Select([functions.Random()]))
value, = cursor.fetchone()
self.assertGreaterEqual(value, 0)
self.assertLessEqual(value, 1)
def table_query(cls):
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
today = Date.today()
if context.get('product_template') is not None:
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = (product.template == context['product_template'])
else:
product_clause = move.product == context.get('product', -1)
if 'warehouse' in context:
warehouse = Location(context.get('warehouse'))
if context.get('stock_skip_warehouse'):
location_id = warehouse.storage_location.id
else:
location_id = warehouse.id
else:
location_id = -1
warehouse = With('id',
query=Location.search([
('parent', 'child_of', [location_id]),
],
query=True,
order=[]))
date_column = Coalesce(move.effective_date, move.planned_date)
return (from_.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column.as_('date'),
move.company.as_('company'),
where=product_clause
& ((move.from_location.in_(warehouse.select(warehouse.id))
& ~move.to_location.in_(warehouse.select(warehouse.id)))
| (~move.from_location.in_(warehouse.select(warehouse.id))
& move.to_location.in_(warehouse.select(warehouse.id))))
& ((date_column < today) & (move.state == 'done')
| (date_column >= today)),
group_by=(date_column, move.product, move.company),
with_=warehouse)
| Select([
Literal(0).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal(today).as_('date'),
Literal(context.get('company', -1)).as_('company'),
]))
def test_to_char_date(self):
"Test TO_CHAR with date"
today = datetime.date.today()
query = Select([ToChar(today, 'YYYY-MM-DD')])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
text, = cursor.fetchone()
self.assertEqual(text, today.strftime('%Y-%m-%d'))
def test_to_char_datetime(self):
"Test TO_CHAR with datetime"
now = datetime.datetime.now()
query = Select([ToChar(now, 'YYYYMMDD HH24:MI:SS.US')])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
text, = cursor.fetchone()
self.assertEqual(text, now.strftime('%Y%m%d %H:%M:%S.%f'))
def test_current_timestamp_static_transaction(self):
"Test CURRENT_TIMESTAMP is static during transaction"
query = Select([CurrentTimestamp()])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
current, = cursor.fetchone()
cursor.execute(*query)
second, = cursor.fetchone()
self.assertEqual(current, second)
def test_current_timestamp_reset_after_commit(self):
"Test CURRENT_TIMESTAMP is reset after commit"
query = Select([CurrentTimestamp()])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
current, = cursor.fetchone()
Transaction().commit()
cursor.execute(*query)
second, = cursor.fetchone()
self.assertNotEqual(current, second)
def test_order_params(self):
with_ = With(
query=self.table.select(self.table.c, where=(self.table.c > 1)))
w = Window([Literal(8)])
query = Select([Literal(2), Min(self.table.c, window=w)],
from_=self.table.select(where=self.table.c > 3),
with_=with_,
where=self.table.c > 4,
group_by=[Literal(5)],
order_by=[Literal(6)],
having=Literal(7))
self.assertEqual(query.params, (1, 2, 3, 4, 5, 6, 7, 8))
def test_sql_cast_timezone(self):
"Cast datetime to date with timezone"
Date = Pool().get('test.date')
expression = Date.date.sql_cast(Literal(
datetime.datetime(2021, 10, 14, 22, 00)),
timezone='Europe/Brussels')
cursor = Transaction().connection.cursor()
cursor.execute(*Select([expression]))
result, = cursor.fetchone()
self.assertEqual(result, datetime.date(2021, 10, 15))
def test_functions(self):
"Test functions"
cursor = Transaction().connection.cursor()
tests = [
(functions.Abs(-1), 1),
(functions.Cbrt(27), 3),
(functions.Ceil(-42.8), -42),
(functions.Degrees(0.5), 28.6478897565412),
(functions.Div(9, 4), 2),
(functions.Exp(1.), math.e),
(functions.Floor(-42.8), -43),
(functions.Ln(2.), 0.693147180559945),
(functions.Log(100.0), 2),
(functions.Mod(9, 4), 1),
(functions.Pi(), math.pi),
(functions.Power(9, 3), 729),
(functions.Radians(45.), math.pi / 4),
(functions.Round(42.4), 42),
(functions.Round(42.4382, 2), 42.44),
(functions.Sign(-8.4), -1),
(functions.Sqrt(2.), 1.4142135623731),
(functions.Trunc(42.8), 42),
(functions.Trunc(42.4348, 2), 42.43),
(functions.Acos(0.5), 1.0471975511965979),
(functions.Asin(0.5), 0.5235987755982989),
(functions.Atan(0.5), 0.4636476090008061),
(functions.Atan2(0.5, 0.5), 0.7853981633974483),
(functions.Cos(1), 0.5403023058681398),
(functions.Cot(0), math.inf),
(functions.Cot(1), 0.6420926159343306),
(functions.Sin(1), 0.8414709848078965),
(functions.Tan(1), 1.5574077246549023),
(functions.CharLength('jose'), 4),
(functions.Lower('TOM'), 'tom'),
(functions.Overlay('Txxxxas', 'hom', 2, 4), 'Thomas'),
(functions.Position('om', 'Thomas'), 3),
(functions.Substring('Thomas', 2, 3), 'hom'),
# (functions.Substring('Thomas', '...$'), 'mas'),
# (functions.Substring('Thomas', '%#"o_a#"_', '#'), 'oma'),
(functions.Trim('yxTomxx', 'BOTH', 'xyz'), 'Tom'),
(functions.Trim(Literal('yxTomxxx'), 'BOTH', 'xyz'), "Tom"),
(functions.Upper('tom'), 'TOM'),
]
for func, result in tests:
with self.subTest(func=str(func)):
cursor.execute(*Select([func]))
value, = cursor.fetchone()
if isinstance(result, str):
self.assertEqual(value, result)
else:
self.assertAlmostEqual(float(value), float(result))
def test_current_timestamp_different_transaction(self):
"Test CURRENT_TIMESTAMP is different per transaction"
query = Select([CurrentTimestamp()])
cursor = Transaction().connection.cursor()
cursor.execute(*query)
current, = cursor.fetchone()
with Transaction().new_transaction() as transaction:
cursor = transaction.connection.cursor()
cursor.execute(*query)
second, = cursor.fetchone()
self.assertNotEqual(current, second)
def test_function_date_trunc(self):
"Test DateTrunc function"
cursor = Transaction().connection.cursor()
date = dt.datetime(2001, 2, 16, 20, 38, 40, 100)
for type_, result in [
('microsecond', dt.datetime(2001, 2, 16, 20, 38, 40, 100)),
('second', dt.datetime(2001, 2, 16, 20, 38, 40)),
('minute', dt.datetime(2001, 2, 16, 20, 38)),
('hour', dt.datetime(2001, 2, 16, 20)),
('day', dt.datetime(2001, 2, 16)),
('month', dt.datetime(2001, 2, 1)),
]:
for type_ in [type_.lower(), type_.upper()]:
with self.subTest(type_=type_):
cursor.execute(*Select([DateTrunc(type_, date)]))
value, = cursor.fetchone()
self.assertEqual(str(value), str(result))
def get(cls, user_name=None, namespace=None, time_interval=None):
search = Search("sthpw/transaction_log")
if user_name:
search.add_filter("login", user_name)
if namespace:
search.add_filter("namespace", namespace)
if time_interval and time_interval != 'NONE':
from sql import Select
search.add_where(Select.get_interval_where(time_interval))
search.add_order_by("timestamp desc")
search.set_limit(100)
sobjects = search.do_search()
return sobjects
def get(cls, user_name=None, namespace=None, time_interval=None):
search = Search("sthpw/transaction_log")
if user_name:
search.add_filter("login", user_name)
if namespace:
search.add_filter("namespace", namespace)
if time_interval and time_interval != 'NONE':
from sql import Select
search.add_where(Select.get_interval_where(time_interval))
search.add_order_by("timestamp desc")
search.set_limit(100)
sobjects = search.do_search()
return sobjects
def table_query():
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
today = Date.today()
if context.get('product_template') is not None:
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = (product.template == context['product_template'])
else:
product_clause = move.product == context.get('product', -1)
warehouse_id = context.get('warehouse', -1)
warehouse_query = Location.search([
('parent', 'child_of', [warehouse_id]),
],
query=True,
order=[])
date_column = Coalesce(move.effective_date, move.planned_date)
return (from_.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column.as_('date'),
where=product_clause
& (move.from_location.in_(warehouse_query)
| move.to_location.in_(warehouse_query))
& (Coalesce(move.effective_date, move.planned_date) != Null)
& (date_column != today),
group_by=(date_column, move.product))
| Select([
Literal(0).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal(today).as_('date'),
]))
def test_functions(self):
"Test functions"
cursor = Transaction().connection.cursor()
tests = [
(functions.Abs(-1), 1),
(functions.Cbrt(27), 3),
(functions.Ceil(-42.8), -42),
(functions.Degrees(0.5), 28.6478897565412),
(functions.Div(9, 4), 2),
(functions.Exp(1.), math.e),
(functions.Floor(-42.8), -43),
(functions.Ln(2.), 0.693147180559945),
(functions.Log(100.0), 2),
(functions.Mod(9, 4), 1),
(functions.Pi(), math.pi),
(functions.Power(9, 3), 729),
(functions.Radians(45.), math.pi / 4),
(functions.Round(42.4), 42),
(functions.Round(42.4382, 2), 42.44),
(functions.Sign(-8.4), -1),
(functions.Sqrt(2.), 1.4142135623731),
(functions.Trunc(42.8), 42),
(functions.Trunc(42.4348, 2), 42.43),
(functions.CharLength('jose'), 4),
(functions.Lower('TOM'), 'tom'),
(functions.Overlay('Txxxxas', 'hom', 2, 4), 'Thomas'),
(functions.Position('om', 'Thomas'), 3),
(functions.Substring('Thomas', 2, 3), 'hom'),
# (functions.Substring('Thomas', '...$'), 'mas'),
# (functions.Substring('Thomas', '%#"o_a#"_', '#'), 'oma'),
(functions.Trim('yxTomxx', 'BOTH', 'xyz'), 'Tom'),
(functions.Upper('tom'), 'TOM'),
]
for func, result in tests:
with self.subTest(func=str(func)):
cursor.execute(*Select([func]))
value, = cursor.fetchone()
if isinstance(result, str):
self.assertEqual(value, result)
else:
self.assertAlmostEqual(float(value), float(result))
OS_FILES = [
("Novell SuSE", ["/etc/SuSE-release"]),
("Red Hat", ["/etc/redhat-release", "/etc/redhat_version"]),
("Fedora", ["/etc/fedora-release"]),
("Slackware", ["/etc/slackware-release", "/etc/slackware-version"]),
("Debian", ["/etc/debian_release", "/etc/debian_version"]),
("Mandrake", ["/etc/mandrake-release"]),
("Yellow dog", ["/etc/yellowdog-release"]),
("Sun JDS", ["/etc/sun-release "]),
("Solaris/Sparc", ["/etc/release "]),
("Gentoo", ["/etc/gentoo-release"]),
]
PROBES = [
("MySQL Version", Select.parse("@@VERSION")),
("MySQL database", Select.parse("database()")),
("MySQL user", Select.parse("current_user()")),
("MySQL is admin", Select.parse("super_priv FROM mysql.user WHERE user=(SUBSTRING_INDEX(CURRENT_USER(), 0x40, 1)) LIMIT 0, 1")),
#("MySQL first user", Select.parse("user from mysql.user limit 1")),
]
for name, files in OS_FILES:
for i, filename in enumerate(files):
if len(files)>1:
n = "%s %d (%s)" % (name, i+1, filename)
else:
n = "%s (%s)" % (name, filename)
PROBES.append((n, Select.parse(Loadfile(filename))))
def trigger_action(self, ids):
"""
Trigger the action define on trigger for the records
"""
pool = Pool()
TriggerLog = pool.get('ir.trigger.log')
Model = pool.get(self.model.model)
model, method = self.action.split('|')
ActionModel = pool.get(model)
cursor = Transaction().connection.cursor()
trigger_log = TriggerLog.__table__()
ids = [r.id for r in Model.browse(ids) if self.eval(r)]
# Filter on limit_number
if self.limit_number:
new_ids = []
for sub_ids in grouped_slice(ids):
sub_ids = list(sub_ids)
red_sql = reduce_ids(trigger_log.record_id, sub_ids)
cursor.execute(*trigger_log.select(
trigger_log.record_id, Count(Literal(1)),
where=red_sql & (trigger_log.trigger == self.id),
group_by=trigger_log.record_id))
number = dict(cursor)
for record_id in sub_ids:
if record_id not in number:
new_ids.append(record_id)
continue
if number[record_id] < self.limit_number:
new_ids.append(record_id)
ids = new_ids
def cast_datetime(value):
datepart, timepart = value.split(" ")
year, month, day = map(int, datepart.split("-"))
timepart_full = timepart.split(".")
hours, minutes, seconds = map(
int, timepart_full[0].split(":"))
if len(timepart_full) == 2:
microseconds = int(timepart_full[1])
else:
microseconds = 0
return datetime.datetime(
year, month, day, hours, minutes, seconds, microseconds)
# Filter on minimum_time_delay
if self.minimum_time_delay:
new_ids = []
# Use now from the transaction to compare with create_date
timestamp_cast = self.__class__.create_date.sql_cast
cursor.execute(*Select([timestamp_cast(CurrentTimestamp())]))
now, = cursor.fetchone()
if isinstance(now, str):
now = cast_datetime(now)
for sub_ids in grouped_slice(ids):
sub_ids = list(sub_ids)
red_sql = reduce_ids(trigger_log.record_id, sub_ids)
cursor.execute(*trigger_log.select(
trigger_log.record_id, Max(trigger_log.create_date),
where=(red_sql & (trigger_log.trigger == self.id)),
group_by=trigger_log.record_id))
delay = dict(cursor)
for record_id in sub_ids:
if record_id not in delay:
new_ids.append(record_id)
continue
# SQLite return string for MAX
if isinstance(delay[record_id], str):
delay[record_id] = cast_datetime(delay[record_id])
if now - delay[record_id] >= self.minimum_time_delay:
new_ids.append(record_id)
ids = new_ids
records = Model.browse(ids)
if records:
getattr(ActionModel, method)(records, self)
if self.limit_number or self.minimum_time_delay:
to_create = []
for record in records:
to_create.append({
'trigger': self.id,
'record_id': record.id,
})
if to_create:
TriggerLog.create(to_create)
def test_select_without_from(self):
query = Select([Literal(1)])
self.assertEqual(str(query), 'SELECT %s')
self.assertEqual(query.params, (1,))
def table_query(cls):
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
today = Date.today()
if context.get('product_template') is not None:
product_template = context['product_template']
if isinstance(product_template, int):
product_template = [product_template]
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = product.template.in_(product_template or [-1])
product_column = Concat('product.template,', product.template)
products = [('product.template', i) for i in product_template]
else:
product = context.get('product')
if product is None:
product = []
if isinstance(product, int):
product = [product]
product_clause = move.product.in_(product or [-1])
product_column = Concat('product.product,', move.product)
products = [('product.product', i) for i in product]
if 'warehouse' in context:
warehouse = Location(context.get('warehouse'))
if context.get('stock_skip_warehouse'):
location_id = warehouse.storage_location.id
else:
location_id = warehouse.id
else:
location_id = -1
warehouse = With('id',
query=Location.search([
('parent', 'child_of', [location_id]),
],
query=True,
order=[]))
date_column = Coalesce(move.effective_date, move.planned_date)
query = (from_.select(
Max(move.id * 3).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
product_column.as_('product'),
date_column.as_('date'),
move.company.as_('company'),
where=product_clause
& ((move.from_location.in_(warehouse.select(warehouse.id))
& ~move.to_location.in_(warehouse.select(warehouse.id)))
| (~move.from_location.in_(warehouse.select(warehouse.id))
& move.to_location.in_(warehouse.select(warehouse.id))))
& ((date_column < today) & (move.state == 'done')
| (date_column > today)),
group_by=(date_column, product_column, move.company),
with_=warehouse))
for model, id_ in products:
gap = ['product.template', 'product.product'].index(model) + 1
query |= Select([
Literal(id_ * 3 + gap).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal('%s,%s' % (model, id_)).as_('product'),
Literal(today).as_('date'),
Literal(context.get('company', -1)).as_('company'),
])
return query
def test_select_select(self):
query = Select([Select([Literal(1)])])
self.assertEqual(str(query), 'SELECT (SELECT %s)')
self.assertEqual(query.params, (1,))
def test_select_select_as(self):
query = Select([Select([Literal(1)]).as_('foo')])
self.assertEqual(str(query), 'SELECT (SELECT %s) AS "foo"')
self.assertEqual(query.params, (1,))
def test_function_setseed(self):
"Test SETSEED function"
cursor = Transaction().connection.cursor()
cursor.execute(*Select([functions.SetSeed(1)]))