示例#1
0
 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)
示例#2
0
    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'),
                ]))
示例#3
0
    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'))
示例#4
0
    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'))
示例#5
0
    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)
示例#6
0
    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)
示例#7
0
    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))
示例#8
0
 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))
示例#9
0
 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))
示例#10
0
    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)
示例#11
0
 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))
示例#12
0
    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'),
                ]))
示例#13
0
 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))
示例#14
0
文件: trigger.py 项目: tryton/trytond
    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)
示例#15
0
 def test_select_without_from(self):
     query = Select([Literal(1)])
     self.assertEqual(str(query), 'SELECT %s')
     self.assertEqual(query.params, (1,))
示例#16
0
 def test_select_select(self):
     query = Select([Select([Literal(1)])])
     self.assertEqual(str(query), 'SELECT (SELECT %s)')
     self.assertEqual(query.params, (1,))
示例#17
0
    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
示例#18
0
 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,))
示例#19
0
 def test_function_setseed(self):
     "Test SETSEED function"
     cursor = Transaction().connection.cursor()
     cursor.execute(*Select([functions.SetSeed(1)]))