changeset f9917483447a in trytond:default
details: https://hg.tryton.org/trytond?cmd=changeset;node=f9917483447a
description:
Ensure all SQL functions are supported by the backend's
issue8942
review270751002
diffstat:
trytond/backend/sqlite/database.py | 50 +++++++++++++++++++++++++++++--
trytond/tests/test_backend.py | 60 ++++++++++++++++++++++++++++++++++++++
2 files changed, 106 insertions(+), 4 deletions(-)
diffs (168 lines):
diff -r ef2eba99f59b -r f9917483447a trytond/backend/sqlite/database.py
--- a/trytond/backend/sqlite/database.py Mon Dec 30 14:26:14 2019 +0100
+++ b/trytond/backend/sqlite/database.py Mon Jan 13 23:47:17 2020 +0100
@@ -2,7 +2,9 @@
# this repository contains the full copyright notices and license terms.
import datetime
import logging
+import math
import os
+import random
import threading
import time
from decimal import Decimal
@@ -230,6 +232,10 @@
return function + '(%s, %s)' % (
format(self.string), format(self.characters))
+ @property
+ def params(self):
+ return [self.string, self.characters]
+
def sign(value):
if value > 0:
@@ -256,6 +262,18 @@
return None
+def cbrt(value):
+ return math.pow(value, 1 / 3)
+
+
+def div(a, b):
+ return a // b
+
+
+def trunc(value, digits):
+ return math.trunc(value * 10 ** digits) / 10 ** digits
+
+
MAPPING = {
Extract: SQLiteExtract,
Position: SQLitePosition,
@@ -333,16 +351,40 @@
self._conn.create_function('extract', 2, SQLiteExtract.extract)
self._conn.create_function('date_trunc', 2, date_trunc)
self._conn.create_function('split_part', 3, split_part)
- self._conn.create_function('position', 2, SQLitePosition.position)
self._conn.create_function('to_char', 2, to_char)
- self._conn.create_function('overlay', 3, SQLiteOverlay.overlay)
- self._conn.create_function('overlay', 4, SQLiteOverlay.overlay)
if sqlite.sqlite_version_info < (3, 3, 14):
self._conn.create_function('replace', 3, replace)
self._conn.create_function('now', 0, now)
- self._conn.create_function('sign', 1, sign)
self._conn.create_function('greatest', -1, greatest)
self._conn.create_function('least', -1, least)
+
+ # Mathematical functions
+ self._conn.create_function('cbrt', 1, cbrt)
+ self._conn.create_function('ceil', 1, math.ceil)
+ self._conn.create_function('degrees', 1, math.degrees)
+ self._conn.create_function('div', 2, div)
+ self._conn.create_function('exp', 1, math.exp)
+ self._conn.create_function('floor', 1, math.floor)
+ self._conn.create_function('ln', 1, math.log)
+ self._conn.create_function('log', 1, math.log10)
+ self._conn.create_function('mod', 2, math.fmod)
+ self._conn.create_function('pi', 0, lambda: math.pi)
+ self._conn.create_function('power', 2, math.pow)
+ self._conn.create_function('radians', 1, math.radians)
+ self._conn.create_function('sign', 1, sign)
+ self._conn.create_function('sqrt', 1, math.sqrt)
+ self._conn.create_function('trunc', 1, math.trunc)
+ self._conn.create_function('trunc', 2, trunc)
+
+ # Random functions
+ self._conn.create_function('random', 0, random.random)
+ self._conn.create_function('setseed', 1, random.seed)
+
+ # String functions
+ self._conn.create_function('overlay', 3, SQLiteOverlay.overlay)
+ self._conn.create_function('overlay', 4, SQLiteOverlay.overlay)
+ self._conn.create_function('position', 2, SQLitePosition.position)
+
if (hasattr(self._conn, 'set_trace_callback')
and logger.isEnabledFor(logging.DEBUG)):
self._conn.set_trace_callback(logger.debug)
diff -r ef2eba99f59b -r f9917483447a trytond/tests/test_backend.py
--- a/trytond/tests/test_backend.py Mon Dec 30 14:26:14 2019 +0100
+++ b/trytond/tests/test_backend.py Mon Jan 13 23:47:17 2020 +0100
@@ -1,9 +1,11 @@
# This file is part of Tryton. The COPYRIGHT file at the top level of
# this repository contains the full copyright notices and license terms.
import datetime
+import math
import unittest
from sql import Select
+from sql import functions
from sql.functions import CurrentTimestamp, ToChar
from trytond.tests.test_tryton import activate_module, with_transaction
@@ -84,6 +86,64 @@
self.assertEqual(text, today.strftime('%Y-%m-%d'))
+ @with_transaction()
+ 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))
+
+ @with_transaction()
+ 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)
+
+ @with_transaction()
+ def test_function_setseed(self):
+ "Test SETSEED function"
+ cursor = Transaction().connection.cursor()
+ cursor.execute(*Select([functions.SetSeed(1)]))
+
def suite():
return unittest.TestLoader().loadTestsFromTestCase(BackendTestCase)
