Пример #1
0
    def test_recursive(self):
        upto10 = With('n', recursive=True)
        upto10.query = Values([(1, )])
        upto10.query |= upto10.select(upto10.n + Literal(1),
                                      where=upto10.n < Literal(100))
        upto10.query.all_ = True

        q = upto10.select(with_=[upto10])
        self.assertEqual(
            str(q), 'WITH RECURSIVE "a" ("n") AS ('
            'VALUES (%s) '
            'UNION ALL '
            'SELECT ("a"."n" + %s) FROM "a" AS "a" WHERE ("a"."n" < %s)'
            ') SELECT * FROM "a" AS "a"')
        self.assertEqual(q.params, (1, 1, 100))
Пример #2
0
    def test_recursive(self):
        upto10 = With('n', recursive=True)
        upto10.query = Values([(1,)])
        upto10.query |= upto10.select(
            upto10.n + Literal(1),
            where=upto10.n < Literal(100))
        upto10.query.all_ = True

        q = upto10.select(with_=[upto10])
        self.assertEqual(str(q),
            'WITH RECURSIVE "a"("n") AS ('
            'VALUES (%s) '
            'UNION ALL '
            'SELECT ("a"."n" + %s) FROM "a" AS "a" WHERE ("a"."n" < %s)'
            ') SELECT * FROM "a" AS "a"')
        self.assertEqual(q.params, (1, 1, 100))
Пример #3
0
    def convert_domain_tree(self, domain, tables):
        Target = self.get_target()
        target = Target.__table__()
        table, _ = tables[None]
        name, operator, ids = domain
        red_sql = reduce_ids(target.id, (i for i in ids if i is not None))

        if operator.endswith('child_of'):
            tree = With('id', recursive=True)
            tree.query = target.select(target.id, where=red_sql)
            tree.query |= (target
                .join(tree, condition=Column(target, name) == tree.id)
                .select(target.id))
        else:
            tree = With('id', name, recursive=True)
            tree.query = target.select(
                target.id, Column(target, name), where=red_sql)
            tree.query |= (target
                .join(tree, condition=target.id == Column(tree, name))
                .select(target.id, Column(target, name)))

        expression = table.id.in_(tree.select(tree.id, with_=[tree]))

        if operator.startswith('not'):
            return ~expression
        return expression
Пример #4
0
    def test_with(self):
        w = With(query=self.table.select(self.table.c1))

        query = w.select(with_=[w])
        self.assertEqual(
            str(query), 'WITH "a" AS (SELECT "b"."c1" FROM "t" AS "b") '
            'SELECT * FROM "a" AS "a"')
        self.assertEqual(query.params, ())
Пример #5
0
    def test_with(self):
        w = With(query=self.table.select(self.table.c1))

        query = w.select(with_=[w])
        self.assertEqual(str(query),
            'WITH "a" AS (SELECT "b"."c1" FROM "t" AS "b") '
            'SELECT * FROM "a" AS "a"')
        self.assertEqual(query.params, ())
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select(t1.c1))

        query = self.table.delete(with_=[w],
            where=self.table.c2.in_(w.select(w.c3)))
        self.assertEqual(str(query),
            'WITH a AS (SELECT "b"."c1" FROM "t1" AS "b") '
            'DELETE FROM "t" WHERE ("c2" IN (SELECT "a"."c3" FROM a AS "a"))')
        self.assertEqual(query.params, ())
Пример #7
0
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select())

        query = self.table.insert([self.table.c1],
                                  with_=[w],
                                  values=w.select())
        self.assertEqual(
            str(query), 'WITH "a" AS (SELECT * FROM "t1" AS "b") '
            'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
        self.assertEqual(query.params, ())
Пример #8
0
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select(t1.c1))

        query = self.table.delete(with_=[w],
            where=self.table.c2.in_(w.select(w.c3)))
        self.assertEqual(str(query),
            'WITH "a" AS (SELECT "b"."c1" FROM "t1" AS "b") '
            'DELETE FROM "t" WHERE '
            '("c2" IN (SELECT "a"."c3" FROM "a" AS "a"))')
        self.assertEqual(query.params, ())
Пример #9
0
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select(t1.c1))

        query = self.table.update([self.table.c2],
                                  with_=[w],
                                  values=[w.select(w.c3, where=w.c4 == 2)])
        self.assertEqual(
            str(query), 'WITH "b" AS (SELECT "c"."c1" FROM "t1" AS "c") '
            'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM "b" AS "b" '
            'WHERE ("b"."c4" = %s))')
        self.assertEqual(query.params, (2, ))
Пример #10
0
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select())

        query = self.table.insert(
            [self.table.c1],
            with_=[w],
            values=w.select())
        self.assertEqual(str(query),
            'WITH "a" AS (SELECT * FROM "t1" AS "b") '
            'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
        self.assertEqual(query.params, ())
Пример #11
0
    def pull(cls, database, connection, name=None):
        cursor = connection.cursor()
        queue = cls.__table__()

        candidates = With('id', 'scheduled_at', 'expected_at',
            query=queue.select(
                queue.id,
                queue.scheduled_at,
                queue.expected_at,
                where=((queue.name == name) if name else Literal(True))
                & (queue.dequeued_at == Null),
                order_by=[
                    queue.scheduled_at.nulls_first,
                    queue.expected_at.nulls_first]))
        selected = With('id', query=candidates.select(
                candidates.id,
                where=((candidates.scheduled_at <= CurrentTimestamp())
                    | (candidates.scheduled_at == Null))
                & database.lock_id(candidates.id),
                order_by=[
                    candidates.scheduled_at.nulls_first,
                    candidates.expected_at.nulls_first],
                limit=1))
        next_timeout = With('seconds', query=candidates.select(
                Min(Extract('second',
                        candidates.scheduled_at - CurrentTimestamp())
                    ),
                where=candidates.scheduled_at >= CurrentTimestamp()))

        task_id, seconds = None, None
        if database.has_returning():
            query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
                where=queue.id == selected.select(selected.id),
                with_=[candidates, selected, next_timeout],
                returning=[
                    queue.id, next_timeout.select(next_timeout.seconds)])
            cursor.execute(*query)
            row = cursor.fetchone()
            if row:
                task_id, seconds = row
        else:
            query = queue.select(queue.id,
                where=queue.id == selected.select(selected.id),
                with_=[candidates, selected])
            cursor.execute(*query)
            row = cursor.fetchone()
            if row:
                task_id, = row
                query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
                    where=queue.id == task_id)
                cursor.execute(*query)
            query = next_timeout.select(next_timeout.seconds)
            cursor.execute(*query)
            row = cursor.fetchone()
            if row:
                seconds, = row

        if not task_id and database.has_channel():
            cursor.execute('LISTEN "%s"', (cls.__name__,))
        return task_id, seconds
    def test_with(self):
        t1 = Table('t1')
        w = With(query=t1.select(t1.c1))

        query = self.table.update(
            [self.table.c2],
            with_=[w],
            values=[w.select(w.c3, where=w.c4 == 2)])
        self.assertEqual(str(query),
            'WITH b AS (SELECT "c"."c1" FROM "t1" AS "c") '
            'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM b AS "b" '
            'WHERE ("b"."c4" = %s))')
        self.assertEqual(query.params, (2,))
Пример #13
0
    def test_with_query(self):
        with AliasManager():
            simple = With()
            simple.query = self.table.select(self.table.id,
                where=self.table.id == 1)
            second = With()
            second.query = simple.select()

            wq = WithQuery(with_=[simple, second])
            self.assertEqual(wq._with_str(),
                'WITH "a" AS ('
                'SELECT "b"."id" FROM "t" AS "b" WHERE ("b"."id" = %s)'
                '), "c" AS ('
                'SELECT * FROM "a" AS "a"'
                ') ')
            self.assertEqual(wq._with_params(), (1,))
Пример #14
0
    def test_with_query(self):
        with AliasManager():
            simple = With()
            simple.query = self.table.select(self.table.id,
                                             where=self.table.id == 1)
            second = With()
            second.query = simple.select()

            wq = WithQuery(with_=[simple, second])
            self.assertEqual(
                wq._with_str(), 'WITH "a" AS ('
                'SELECT "b"."id" FROM "t" AS "b" WHERE ("b"."id" = %s)'
                '), "c" AS ('
                'SELECT * FROM "a" AS "a"'
                ') ')
            self.assertEqual(wq._with_params(), (1, ))