[tryton-debian-vcs] python-sql branch debian created. 0729a2d59a397117d1445fbe7319cc6ff8689bf1
Mathias Behrle
tryton-debian-vcs at alioth.debian.org
Wed Nov 27 16:48:16 UTC 2013
The following commit has been merged in the debian branch:
https://alioth.debian.org/plugins/scmgit/cgi-bin/gitweb.cgi/?p=tryton/python-sql.git;a=commitdiff;h=0729a2d59a397117d1445fbe7319cc6ff8689bf1
commit 0729a2d59a397117d1445fbe7319cc6ff8689bf1
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Sun Nov 24 19:15:23 2013 +0100
Updating to standards version 3.9.5, no changes needed.
diff --git a/debian/control b/debian/control
index 751a23d..7bafd08 100644
--- a/debian/control
+++ b/debian/control
@@ -6,7 +6,7 @@ Uploaders: Mathias Behrle <mathiasb at m9s.biz>
Build-Depends:
debhelper (>= 9), dh-python (>= 1.20130901-1~), python (>= 2.6.6-3~),
python3, python-setuptools, python3-setuptools
-Standards-Version: 3.9.4
+Standards-Version: 3.9.5
Homepage: http://code.google.com/p/python-sql/
Vcs-Browser: http://debian.tryton.org/gitweb/?p=packages/python-sql.git
Vcs-Git: git://debian.tryton.org/packages/python-sql.git
commit b3331c2de8ac9ba51283947280bb796e469912d2
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Wed Oct 16 20:22:13 2013 +0200
Removing version constraint for build dependency python3.
Since we are building with dh-python/pybuild, this version constraint is
no more needed.
diff --git a/debian/control b/debian/control
index 8e308c8..751a23d 100644
--- a/debian/control
+++ b/debian/control
@@ -5,7 +5,7 @@ Maintainer: Debian Tryton Maintainers <maintainers at debian.tryton.org>
Uploaders: Mathias Behrle <mathiasb at m9s.biz>
Build-Depends:
debhelper (>= 9), dh-python (>= 1.20130901-1~), python (>= 2.6.6-3~),
- python3 (>= 3.3.0-2~), python-setuptools, python3-setuptools
+ python3, python-setuptools, python3-setuptools
Standards-Version: 3.9.4
Homepage: http://code.google.com/p/python-sql/
Vcs-Browser: http://debian.tryton.org/gitweb/?p=packages/python-sql.git
commit d6b3ee7f0bcb7b3e81d7c095f67ca7b2aeee249f
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Mon Oct 14 19:50:38 2013 +0200
Releasing debian version 0.2-1.
diff --git a/debian/changelog b/debian/changelog
index f5726c0..969f6a7 100644
--- a/debian/changelog
+++ b/debian/changelog
@@ -2,4 +2,4 @@ python-sql (0.2-1) unstable; urgency=low
* Initial packaging (Closes: #721813).
- -- Mathias Behrle <mathiasb at m9s.biz> Fri, 11 Oct 2013 13:28:50 +0200
+ -- Mathias Behrle <mathiasb at m9s.biz> Sat, 12 Oct 2013 22:53:27 +0200
commit cd799b08eeaa266f5ae98e7670e4c0b058f4513f
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Mon Oct 7 14:43:55 2013 +0200
Adding debian version 0.2-1.
diff --git a/CHANGELOG b/CHANGELOG
new file mode 100644
index 0000000..2780393
--- /dev/null
+++ b/CHANGELOG
@@ -0,0 +1,6 @@
+Version 0.2 - 2013-09-18
+* Fix usage mixture between Div operator and function
+* Add array support in operators
+
+Version 0.1 - 2013-08-28
+* Initial release
diff --git a/MANIFEST.in b/MANIFEST.in
new file mode 100644
index 0000000..f188446
--- /dev/null
+++ b/MANIFEST.in
@@ -0,0 +1,2 @@
+include README
+include CHANGELOG
diff --git a/PKG-INFO b/PKG-INFO
new file mode 100644
index 0000000..2a7162e
--- /dev/null
+++ b/PKG-INFO
@@ -0,0 +1,189 @@
+Metadata-Version: 1.1
+Name: python-sql
+Version: 0.2
+Summary: Library to write SQL queries
+Home-page: http://code.google.com/p/python-sql/
+Author: B2CK
+Author-email: info at b2ck.com
+License: BSD
+Description: python-sql
+ ==========
+
+ python-sql is a library to write SQL queries in a pythonic way.
+
+ Nutshell
+ --------
+
+ Import::
+
+ >>> from sql import *
+ >>> from sql.aggregate import *
+ >>> from sql.conditionals import *
+
+ Simple selects::
+
+ >>> user = Table('user')
+ >>> select = user.select()
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a"', ())
+
+ >>> select = user.select(user.name)
+ >>> tuple(select)
+ ('SELECT "a"."name" FROM "user" AS "a"', ())
+
+ >>> select = user.select(Count(Literal(1)))
+ >>> tuple(select)
+ ('SELECT COUNT(%s) FROM "user" AS "a"', (1,))
+
+ >>> select = user.select(user.id, user.name)
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a"', ())
+
+ Select with where condition::
+
+ >>> select.where = user.name == 'foo'
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = %s)', ('foo',))
+
+ >>> select.where = (user.name == 'foo') & (user.active == True)
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE (("a"."name" = %s) AND ("a"."active" = %s))', ('foo', True))
+ >>> select.where = user.name == user.login
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = "a"."login")', ())
+
+ Select with join::
+
+ >>> join = user.join(Table('user_group'))
+ >>> join.condition = join.right.user == user.id
+ >>> select = join.select(user.name, join.right.group)
+ >>> tuple(select)
+ ('SELECT "a"."name", "b"."group" FROM "user" AS "a" INNER JOIN "user_group" AS "b" ON ("b"."user" = "a"."id")', ())
+
+ Select with multiple joins::
+
+ >>> join1 = user.join(Table('user'))
+ >>> join2 = join1.join(Table('user'))
+ >>> select = join2.select(user.id, join1.right.id, join2.right.id)
+ >>> tuple(select)
+ ('SELECT "a"."id", "b"."id", "c"."id" FROM "user" AS "a" INNER JOIN "user" AS "b" INNER JOIN "user" AS "c"', ())
+
+ Select with group_by::
+
+ >>> invoice = Table('invoice')
+ >>> select = invoice.select(Sum(invoice.amount), invoice.currency,
+ ... group_by=invoice.currency)
+ >>> tuple(select)
+ ('SELECT SUM("a"."amount"), "a"."currency" FROM "invoice" AS "a" GROUP BY "a"."currency"', ())
+
+ Select with output name::
+
+ >>> tuple(user.select(user.name.as_('First Name')))
+ ('SELECT "a"."name" AS "First Name" FROM "user" AS "a"', ())
+
+ Select with order_by::
+
+ >>> tuple(user.select(order_by=user.date))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date"', ())
+ >>> tuple(user.select(order_by=Asc(user.date)))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC', ())
+ >>> tuple(user.select(order_by=(user.date.asc, user.id.desc)))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC, "a"."id" DESC', ())
+
+ Select with sub-select::
+
+ >>> user_group = Table('user_group')
+ >>> subselect = user_group.select(user_group.user,
+ ... where=(user_group.active == True))
+ >>> user = Table('user')
+ >>> tuple(user.select(user.id, where=(user.id.in_(subselect))))
+ ('SELECT "a"."id" FROM "user" AS "a" WHERE ("a"."id" IN (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)))', (True,))
+ >>> tuple(subselect.select(subselect.user))
+ ('SELECT "a"."user" FROM (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)) AS "a"', (True,))
+
+ Insert query with default values::
+
+ >>> tuple(user.insert())
+ ('INSERT INTO "user" DEFAULT VALUES', ())
+
+ Insert query with values::
+
+ >>> tuple(user.insert(columns=[user.name, user.login],
+ ... values=[['Foo', 'foo']]))
+ ('INSERT INTO "user" ("name", "login") VALUES (%s, %s)', ('Foo', 'foo'))
+ >>> tuple(user.insert(columns=[user.name, user.login],
+ ... values=[['Foo', 'foo'], ['Bar', 'bar']]))
+ ('INSERT INTO "user" ("name", "login") VALUES (%s, %s), (%s, %s)', ('Foo', 'foo', 'Bar', 'bar'))
+
+ Insert query with query::
+
+ >>> passwd = Table('passwd')
+ >>> select = passwd.select(passwd.login, passwd.passwd)
+ >>> tuple(user.insert(values=select))
+ ('INSERT INTO "user" SELECT "a"."login", "a"."passwd" FROM "passwd" AS "a"', ())
+
+ Update query with values::
+
+ >>> tuple(user.update(columns=[user.active], values=[True]))
+ ('UPDATE "user" SET "active" = %s', (True,))
+ >>> tuple(invoice.update(columns=[invoice.total], values=[invoice.amount + invoice.tax]))
+ ('UPDATE "invoice" SET "total" = ("invoice"."amount" + "invoice"."tax")', ())
+
+ Update query with where condition::
+
+ >>> tuple(user.update(columns=[user.active], values=[True], where=(
+ ... user.active == False)))
+ ('UPDATE "user" SET "active" = %s WHERE ("user"."active" = %s)', (True, False))
+
+ Update query with from list::
+
+ >>> group = Table('user_group')
+ >>> tuple(user.update(columns=[user.active], values=[group.active],
+ ... from_=[group], where=(user.id == group.user)))
+ ('UPDATE "user" AS "b" SET "active" = "a"."active" FROM "user_group" AS "a" WHERE ("b"."id" = "a"."user")', ())
+
+ Delete query::
+
+ >>> tuple(user.delete())
+ ('DELETE FROM "user"', ())
+
+ Delete query with where condition::
+
+ >>> tuple(user.delete(where=(user.name == 'foo')))
+ ('DELETE FROM "user" WHERE ("name" = %s)', ('foo',))
+
+ Delete query with sub-query::
+
+ >>> tuple(user.delete(where=(
+ ... user.id.in_(user_group.select(user_group.user)))))
+ ('DELETE FROM "user" WHERE ("id" IN (SELECT "a"."user" FROM "user_group" AS "a"))', ())
+
+ Flavors::
+
+ >>> select = user.select()
+ >>> select.offset = 10
+ >>> Flavor.set(Flavor())
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" OFFSET 10', ())
+ >>> Flavor.set(Flavor(max_limit=18446744073709551615))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT 18446744073709551615 OFFSET 10', ())
+ >>> Flavor.set(Flavor(max_limit=-1))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
+ >>> Flavor.set(Flavor(paramstyle='qmark'))
+ >>> select = user.select()
+ >>> select.where = (user.name == 'foo')
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
+
+Platform: UNKNOWN
+Classifier: Development Status :: 5 - Production/Stable
+Classifier: Intended Audience :: Developers
+Classifier: License :: OSI Approved :: BSD License
+Classifier: Operating System :: OS Independent
+Classifier: Programming Language :: Python :: 2.6
+Classifier: Programming Language :: Python :: 2.7
+Classifier: Programming Language :: Python :: 3
+Classifier: Topic :: Database
+Classifier: Topic :: Software Development :: Libraries :: Python Modules
diff --git a/README b/README
new file mode 100644
index 0000000..57f08ee
--- /dev/null
+++ b/README
@@ -0,0 +1,170 @@
+python-sql
+==========
+
+python-sql is a library to write SQL queries in a pythonic way.
+
+Nutshell
+--------
+
+Import::
+
+ >>> from sql import *
+ >>> from sql.aggregate import *
+ >>> from sql.conditionals import *
+
+Simple selects::
+
+ >>> user = Table('user')
+ >>> select = user.select()
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a"', ())
+
+ >>> select = user.select(user.name)
+ >>> tuple(select)
+ ('SELECT "a"."name" FROM "user" AS "a"', ())
+
+ >>> select = user.select(Count(Literal(1)))
+ >>> tuple(select)
+ ('SELECT COUNT(%s) FROM "user" AS "a"', (1,))
+
+ >>> select = user.select(user.id, user.name)
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a"', ())
+
+Select with where condition::
+
+ >>> select.where = user.name == 'foo'
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = %s)', ('foo',))
+
+ >>> select.where = (user.name == 'foo') & (user.active == True)
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE (("a"."name" = %s) AND ("a"."active" = %s))', ('foo', True))
+ >>> select.where = user.name == user.login
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = "a"."login")', ())
+
+Select with join::
+
+ >>> join = user.join(Table('user_group'))
+ >>> join.condition = join.right.user == user.id
+ >>> select = join.select(user.name, join.right.group)
+ >>> tuple(select)
+ ('SELECT "a"."name", "b"."group" FROM "user" AS "a" INNER JOIN "user_group" AS "b" ON ("b"."user" = "a"."id")', ())
+
+Select with multiple joins::
+
+ >>> join1 = user.join(Table('user'))
+ >>> join2 = join1.join(Table('user'))
+ >>> select = join2.select(user.id, join1.right.id, join2.right.id)
+ >>> tuple(select)
+ ('SELECT "a"."id", "b"."id", "c"."id" FROM "user" AS "a" INNER JOIN "user" AS "b" INNER JOIN "user" AS "c"', ())
+
+Select with group_by::
+
+ >>> invoice = Table('invoice')
+ >>> select = invoice.select(Sum(invoice.amount), invoice.currency,
+ ... group_by=invoice.currency)
+ >>> tuple(select)
+ ('SELECT SUM("a"."amount"), "a"."currency" FROM "invoice" AS "a" GROUP BY "a"."currency"', ())
+
+Select with output name::
+
+ >>> tuple(user.select(user.name.as_('First Name')))
+ ('SELECT "a"."name" AS "First Name" FROM "user" AS "a"', ())
+
+Select with order_by::
+
+ >>> tuple(user.select(order_by=user.date))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date"', ())
+ >>> tuple(user.select(order_by=Asc(user.date)))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC', ())
+ >>> tuple(user.select(order_by=(user.date.asc, user.id.desc)))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC, "a"."id" DESC', ())
+
+Select with sub-select::
+
+ >>> user_group = Table('user_group')
+ >>> subselect = user_group.select(user_group.user,
+ ... where=(user_group.active == True))
+ >>> user = Table('user')
+ >>> tuple(user.select(user.id, where=(user.id.in_(subselect))))
+ ('SELECT "a"."id" FROM "user" AS "a" WHERE ("a"."id" IN (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)))', (True,))
+ >>> tuple(subselect.select(subselect.user))
+ ('SELECT "a"."user" FROM (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)) AS "a"', (True,))
+
+Insert query with default values::
+
+ >>> tuple(user.insert())
+ ('INSERT INTO "user" DEFAULT VALUES', ())
+
+Insert query with values::
+
+ >>> tuple(user.insert(columns=[user.name, user.login],
+ ... values=[['Foo', 'foo']]))
+ ('INSERT INTO "user" ("name", "login") VALUES (%s, %s)', ('Foo', 'foo'))
+ >>> tuple(user.insert(columns=[user.name, user.login],
+ ... values=[['Foo', 'foo'], ['Bar', 'bar']]))
+ ('INSERT INTO "user" ("name", "login") VALUES (%s, %s), (%s, %s)', ('Foo', 'foo', 'Bar', 'bar'))
+
+Insert query with query::
+
+ >>> passwd = Table('passwd')
+ >>> select = passwd.select(passwd.login, passwd.passwd)
+ >>> tuple(user.insert(values=select))
+ ('INSERT INTO "user" SELECT "a"."login", "a"."passwd" FROM "passwd" AS "a"', ())
+
+Update query with values::
+
+ >>> tuple(user.update(columns=[user.active], values=[True]))
+ ('UPDATE "user" SET "active" = %s', (True,))
+ >>> tuple(invoice.update(columns=[invoice.total], values=[invoice.amount + invoice.tax]))
+ ('UPDATE "invoice" SET "total" = ("invoice"."amount" + "invoice"."tax")', ())
+
+Update query with where condition::
+
+ >>> tuple(user.update(columns=[user.active], values=[True], where=(
+ ... user.active == False)))
+ ('UPDATE "user" SET "active" = %s WHERE ("user"."active" = %s)', (True, False))
+
+Update query with from list::
+
+ >>> group = Table('user_group')
+ >>> tuple(user.update(columns=[user.active], values=[group.active],
+ ... from_=[group], where=(user.id == group.user)))
+ ('UPDATE "user" AS "b" SET "active" = "a"."active" FROM "user_group" AS "a" WHERE ("b"."id" = "a"."user")', ())
+
+Delete query::
+
+ >>> tuple(user.delete())
+ ('DELETE FROM "user"', ())
+
+Delete query with where condition::
+
+ >>> tuple(user.delete(where=(user.name == 'foo')))
+ ('DELETE FROM "user" WHERE ("name" = %s)', ('foo',))
+
+Delete query with sub-query::
+
+ >>> tuple(user.delete(where=(
+ ... user.id.in_(user_group.select(user_group.user)))))
+ ('DELETE FROM "user" WHERE ("id" IN (SELECT "a"."user" FROM "user_group" AS "a"))', ())
+
+Flavors::
+
+ >>> select = user.select()
+ >>> select.offset = 10
+ >>> Flavor.set(Flavor())
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" OFFSET 10', ())
+ >>> Flavor.set(Flavor(max_limit=18446744073709551615))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT 18446744073709551615 OFFSET 10', ())
+ >>> Flavor.set(Flavor(max_limit=-1))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
+ >>> Flavor.set(Flavor(paramstyle='qmark'))
+ >>> select = user.select()
+ >>> select.where = (user.name == 'foo')
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
diff --git a/debian/changelog b/debian/changelog
new file mode 100644
index 0000000..f5726c0
--- /dev/null
+++ b/debian/changelog
@@ -0,0 +1,5 @@
+python-sql (0.2-1) unstable; urgency=low
+
+ * Initial packaging (Closes: #721813).
+
+ -- Mathias Behrle <mathiasb at m9s.biz> Fri, 11 Oct 2013 13:28:50 +0200
diff --git a/debian/compat b/debian/compat
new file mode 100644
index 0000000..ec63514
--- /dev/null
+++ b/debian/compat
@@ -0,0 +1 @@
+9
diff --git a/debian/control b/debian/control
new file mode 100644
index 0000000..8e308c8
--- /dev/null
+++ b/debian/control
@@ -0,0 +1,43 @@
+Source: python-sql
+Section: python
+Priority: optional
+Maintainer: Debian Tryton Maintainers <maintainers at debian.tryton.org>
+Uploaders: Mathias Behrle <mathiasb at m9s.biz>
+Build-Depends:
+ debhelper (>= 9), dh-python (>= 1.20130901-1~), python (>= 2.6.6-3~),
+ python3 (>= 3.3.0-2~), python-setuptools, python3-setuptools
+Standards-Version: 3.9.4
+Homepage: http://code.google.com/p/python-sql/
+Vcs-Browser: http://debian.tryton.org/gitweb/?p=packages/python-sql.git
+Vcs-Git: git://debian.tryton.org/packages/python-sql.git
+X-Python-Version: >= 2.6
+
+Package: python-sql
+Architecture: all
+Depends: ${misc:Depends}, ${python:Depends}, python-pkg-resources
+Suggests: python-psycopg2, python-mysqldb, python-pysqlite2 | python-apsw
+Description: Library to write SQL queries (implemented in Python 2)
+ python-sql is a library to write SQL queries in a pythonic way. It relies
+ exclusively on the Python standard library. It is database independent,
+ doesn't require the declaration of tables and allows one to manipulate the
+ generated queries.
+ .
+ To make real use of this package you will have to install a database
+ connector.
+ .
+ This package is targeting Python version 2.
+
+Package: python3-sql
+Architecture: all
+Depends: ${misc:Depends}, ${python3:Depends}, python3-pkg-resources
+Suggests: python3-psycopg2, python3-mysql.connector, python3-apsw
+Description: Library to write SQL queries (implemented in Python 3)
+ python-sql is a library to write SQL queries in a pythonic way. It relies
+ exclusively on the Python standard library. It is database independent,
+ doesn't require the declaration of tables and allows one to manipulate the
+ generated queries.
+ .
+ To make real use of this package you will have to install a database
+ connector.
+ .
+ This package is targeting Python version 3.
diff --git a/debian/copyright b/debian/copyright
new file mode 100644
index 0000000..50ad261
--- /dev/null
+++ b/debian/copyright
@@ -0,0 +1,35 @@
+Format: http://www.debian.org/doc/packaging-manuals/copyright-format/1.0/
+
+Files: *
+Copyright: 2011-2013 Cedric Krier
+ 2011-2013 B2CK
+License: BSD
+
+Files: debian/*
+Copyright: 2013 Mathias Behrle <mathiasb at m9s.biz>
+License: BSD
+
+License: BSD
+ All rights reserved.
+ .
+ Redistribution and use in source and binary forms, with or without
+ modification, are permitted provided that the following conditions are met:
+ * Redistributions of source code must retain the above copyright
+ notice, this list of conditions and the following disclaimer.
+ * Redistributions in binary form must reproduce the above copyright
+ notice, this list of conditions and the following disclaimer in the
+ documentation and/or other materials provided with the distribution.
+ * Neither the name of the <organization> nor the
+ names of its contributors may be used to endorse or promote products
+ derived from this software without specific prior written permission.
+ .
+ THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+ AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+ IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+ ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+ DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+ (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+ LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+ ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+ SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
diff --git a/debian/patches/fix_slots_shadows_class_variable.patch b/debian/patches/fix_slots_shadows_class_variable.patch
new file mode 100644
index 0000000..00cb85f
--- /dev/null
+++ b/debian/patches/fix_slots_shadows_class_variable.patch
@@ -0,0 +1,27 @@
+Description: Fix slots shadows class variable in AliasManager
+This patch already went upstream and is subject for removal with version > 0.2
+Origin: http://code.google.com/p/python-sql/source/detail?r=217fc0c811dac6883f69e30c6943b95c97e41e77
+Bug: http://code.google.com/p/python-sql/issues/detail?id=12
+Reviewed-By: Mathias Behrle<mathiasb at m9s.biz>
+Last-Update: 2013-10-10
+
+--- python-sql-0.2.orig/sql/__init__.py
++++ python-sql-0.2/sql/__init__.py
+@@ -29,7 +29,7 @@
+
+ from __future__ import division
+
+-__version__ = '0.2'
++__version__ = '0.3'
+ __all__ = ['Flavor', 'Table', 'Literal', 'Column', 'Join', 'Asc', 'Desc']
+
+ import string
+@@ -110,7 +110,7 @@ class AliasManager(object):
+ '''
+ Context Manager for unique alias generation
+ '''
+- __slots__ = 'local'
++ __slots__ = ()
+
+ local = local()
+ local.alias = None
diff --git a/debian/patches/series b/debian/patches/series
new file mode 100644
index 0000000..951b4e2
--- /dev/null
+++ b/debian/patches/series
@@ -0,0 +1 @@
+fix_slots_shadows_class_variable.patch
diff --git a/debian/rules b/debian/rules
new file mode 100755
index 0000000..d5d1fac
--- /dev/null
+++ b/debian/rules
@@ -0,0 +1,19 @@
+#!/usr/bin/make -f
+
+# needed for pbuilder
+export LC_ALL=C.UTF-8
+
+PACKAGE_NAME := python_sql
+export PYBUILD_DESTDIR_python2=debian/python-sql/
+export PYBUILD_DESTDIR_python3=debian/python3-sql/
+
+%:
+ dh ${@} --with python2,python3 --buildsystem=pybuild
+
+override_dh_auto_clean:
+ rm -rf $(PACKAGE_NAME).egg-info
+ rm -rf PKG-INFO
+ dh_auto_clean
+
+override_dh_builddeb:
+ dh_builddeb -- -Zxz -z9
diff --git a/debian/source/format b/debian/source/format
new file mode 100644
index 0000000..163aaf8
--- /dev/null
+++ b/debian/source/format
@@ -0,0 +1 @@
+3.0 (quilt)
diff --git a/debian/source/options b/debian/source/options
new file mode 100644
index 0000000..22a4de9
--- /dev/null
+++ b/debian/source/options
@@ -0,0 +1,2 @@
+compression = xz
+compression-level = 9
diff --git a/debian/watch b/debian/watch
new file mode 100644
index 0000000..4e32ade
--- /dev/null
+++ b/debian/watch
@@ -0,0 +1,2 @@
+version=3
+https://pypi.python.org/packages/source/p/python-sql/python-sql-(\d+.*)\.(?:tgz|tbz2|txz|tar\.(?:gz|bz2|xz))
diff --git a/python_sql.egg-info/PKG-INFO b/python_sql.egg-info/PKG-INFO
new file mode 100644
index 0000000..2a7162e
--- /dev/null
+++ b/python_sql.egg-info/PKG-INFO
@@ -0,0 +1,189 @@
+Metadata-Version: 1.1
+Name: python-sql
+Version: 0.2
+Summary: Library to write SQL queries
+Home-page: http://code.google.com/p/python-sql/
+Author: B2CK
+Author-email: info at b2ck.com
+License: BSD
+Description: python-sql
+ ==========
+
+ python-sql is a library to write SQL queries in a pythonic way.
+
+ Nutshell
+ --------
+
+ Import::
+
+ >>> from sql import *
+ >>> from sql.aggregate import *
+ >>> from sql.conditionals import *
+
+ Simple selects::
+
+ >>> user = Table('user')
+ >>> select = user.select()
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a"', ())
+
+ >>> select = user.select(user.name)
+ >>> tuple(select)
+ ('SELECT "a"."name" FROM "user" AS "a"', ())
+
+ >>> select = user.select(Count(Literal(1)))
+ >>> tuple(select)
+ ('SELECT COUNT(%s) FROM "user" AS "a"', (1,))
+
+ >>> select = user.select(user.id, user.name)
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a"', ())
+
+ Select with where condition::
+
+ >>> select.where = user.name == 'foo'
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = %s)', ('foo',))
+
+ >>> select.where = (user.name == 'foo') & (user.active == True)
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE (("a"."name" = %s) AND ("a"."active" = %s))', ('foo', True))
+ >>> select.where = user.name == user.login
+ >>> tuple(select)
+ ('SELECT "a"."id", "a"."name" FROM "user" AS "a" WHERE ("a"."name" = "a"."login")', ())
+
+ Select with join::
+
+ >>> join = user.join(Table('user_group'))
+ >>> join.condition = join.right.user == user.id
+ >>> select = join.select(user.name, join.right.group)
+ >>> tuple(select)
+ ('SELECT "a"."name", "b"."group" FROM "user" AS "a" INNER JOIN "user_group" AS "b" ON ("b"."user" = "a"."id")', ())
+
+ Select with multiple joins::
+
+ >>> join1 = user.join(Table('user'))
+ >>> join2 = join1.join(Table('user'))
+ >>> select = join2.select(user.id, join1.right.id, join2.right.id)
+ >>> tuple(select)
+ ('SELECT "a"."id", "b"."id", "c"."id" FROM "user" AS "a" INNER JOIN "user" AS "b" INNER JOIN "user" AS "c"', ())
+
+ Select with group_by::
+
+ >>> invoice = Table('invoice')
+ >>> select = invoice.select(Sum(invoice.amount), invoice.currency,
+ ... group_by=invoice.currency)
+ >>> tuple(select)
+ ('SELECT SUM("a"."amount"), "a"."currency" FROM "invoice" AS "a" GROUP BY "a"."currency"', ())
+
+ Select with output name::
+
+ >>> tuple(user.select(user.name.as_('First Name')))
+ ('SELECT "a"."name" AS "First Name" FROM "user" AS "a"', ())
+
+ Select with order_by::
+
+ >>> tuple(user.select(order_by=user.date))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date"', ())
+ >>> tuple(user.select(order_by=Asc(user.date)))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC', ())
+ >>> tuple(user.select(order_by=(user.date.asc, user.id.desc)))
+ ('SELECT * FROM "user" AS "a" ORDER BY "a"."date" ASC, "a"."id" DESC', ())
+
+ Select with sub-select::
+
+ >>> user_group = Table('user_group')
+ >>> subselect = user_group.select(user_group.user,
+ ... where=(user_group.active == True))
+ >>> user = Table('user')
+ >>> tuple(user.select(user.id, where=(user.id.in_(subselect))))
+ ('SELECT "a"."id" FROM "user" AS "a" WHERE ("a"."id" IN (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)))', (True,))
+ >>> tuple(subselect.select(subselect.user))
+ ('SELECT "a"."user" FROM (SELECT "b"."user" FROM "user_group" AS "b" WHERE ("b"."active" = %s)) AS "a"', (True,))
+
+ Insert query with default values::
+
+ >>> tuple(user.insert())
+ ('INSERT INTO "user" DEFAULT VALUES', ())
+
+ Insert query with values::
+
+ >>> tuple(user.insert(columns=[user.name, user.login],
+ ... values=[['Foo', 'foo']]))
+ ('INSERT INTO "user" ("name", "login") VALUES (%s, %s)', ('Foo', 'foo'))
+ >>> tuple(user.insert(columns=[user.name, user.login],
+ ... values=[['Foo', 'foo'], ['Bar', 'bar']]))
+ ('INSERT INTO "user" ("name", "login") VALUES (%s, %s), (%s, %s)', ('Foo', 'foo', 'Bar', 'bar'))
+
+ Insert query with query::
+
+ >>> passwd = Table('passwd')
+ >>> select = passwd.select(passwd.login, passwd.passwd)
+ >>> tuple(user.insert(values=select))
+ ('INSERT INTO "user" SELECT "a"."login", "a"."passwd" FROM "passwd" AS "a"', ())
+
+ Update query with values::
+
+ >>> tuple(user.update(columns=[user.active], values=[True]))
+ ('UPDATE "user" SET "active" = %s', (True,))
+ >>> tuple(invoice.update(columns=[invoice.total], values=[invoice.amount + invoice.tax]))
+ ('UPDATE "invoice" SET "total" = ("invoice"."amount" + "invoice"."tax")', ())
+
+ Update query with where condition::
+
+ >>> tuple(user.update(columns=[user.active], values=[True], where=(
+ ... user.active == False)))
+ ('UPDATE "user" SET "active" = %s WHERE ("user"."active" = %s)', (True, False))
+
+ Update query with from list::
+
+ >>> group = Table('user_group')
+ >>> tuple(user.update(columns=[user.active], values=[group.active],
+ ... from_=[group], where=(user.id == group.user)))
+ ('UPDATE "user" AS "b" SET "active" = "a"."active" FROM "user_group" AS "a" WHERE ("b"."id" = "a"."user")', ())
+
+ Delete query::
+
+ >>> tuple(user.delete())
+ ('DELETE FROM "user"', ())
+
+ Delete query with where condition::
+
+ >>> tuple(user.delete(where=(user.name == 'foo')))
+ ('DELETE FROM "user" WHERE ("name" = %s)', ('foo',))
+
+ Delete query with sub-query::
+
+ >>> tuple(user.delete(where=(
+ ... user.id.in_(user_group.select(user_group.user)))))
+ ('DELETE FROM "user" WHERE ("id" IN (SELECT "a"."user" FROM "user_group" AS "a"))', ())
+
+ Flavors::
+
+ >>> select = user.select()
+ >>> select.offset = 10
+ >>> Flavor.set(Flavor())
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" OFFSET 10', ())
+ >>> Flavor.set(Flavor(max_limit=18446744073709551615))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT 18446744073709551615 OFFSET 10', ())
+ >>> Flavor.set(Flavor(max_limit=-1))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
+ >>> Flavor.set(Flavor(paramstyle='qmark'))
+ >>> select = user.select()
+ >>> select.where = (user.name == 'foo')
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
+
+Platform: UNKNOWN
+Classifier: Development Status :: 5 - Production/Stable
+Classifier: Intended Audience :: Developers
+Classifier: License :: OSI Approved :: BSD License
+Classifier: Operating System :: OS Independent
+Classifier: Programming Language :: Python :: 2.6
+Classifier: Programming Language :: Python :: 2.7
+Classifier: Programming Language :: Python :: 3
+Classifier: Topic :: Database
+Classifier: Topic :: Software Development :: Libraries :: Python Modules
diff --git a/python_sql.egg-info/SOURCES.txt b/python_sql.egg-info/SOURCES.txt
new file mode 100644
index 0000000..7c24f7a
--- /dev/null
+++ b/python_sql.egg-info/SOURCES.txt
@@ -0,0 +1,30 @@
+CHANGELOG
+MANIFEST.in
+README
+setup.py
+python_sql.egg-info/PKG-INFO
+python_sql.egg-info/SOURCES.txt
+python_sql.egg-info/dependency_links.txt
+python_sql.egg-info/top_level.txt
+sql/__init__.py
+sql/aggregate.py
+sql/conditionals.py
+sql/functions.py
+sql/operators.py
+sql/tests/__init__.py
+sql/tests/test_aggregate.py
+sql/tests/test_alias.py
+sql/tests/test_as.py
+sql/tests/test_cast.py
+sql/tests/test_column.py
+sql/tests/test_conditionals.py
+sql/tests/test_delete.py
+sql/tests/test_for.py
+sql/tests/test_functions.py
+sql/tests/test_insert.py
+sql/tests/test_join.py
+sql/tests/test_literal.py
+sql/tests/test_operators.py
+sql/tests/test_order.py
+sql/tests/test_select.py
+sql/tests/test_update.py
\ No newline at end of file
diff --git a/python_sql.egg-info/dependency_links.txt b/python_sql.egg-info/dependency_links.txt
new file mode 100644
index 0000000..8b13789
--- /dev/null
+++ b/python_sql.egg-info/dependency_links.txt
@@ -0,0 +1 @@
+
diff --git a/python_sql.egg-info/top_level.txt b/python_sql.egg-info/top_level.txt
new file mode 100644
index 0000000..d5ce2f2
--- /dev/null
+++ b/python_sql.egg-info/top_level.txt
@@ -0,0 +1 @@
+sql
diff --git a/setup.cfg b/setup.cfg
new file mode 100644
index 0000000..861a9f5
--- /dev/null
+++ b/setup.cfg
@@ -0,0 +1,5 @@
+[egg_info]
+tag_build =
+tag_date = 0
+tag_svn_revision = 0
+
diff --git a/setup.py b/setup.py
new file mode 100644
index 0000000..28fe987
--- /dev/null
+++ b/setup.py
@@ -0,0 +1,65 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+from setuptools import setup, find_packages
+import os
+import re
+
+
+def read(fname):
+ return open(os.path.join(os.path.dirname(__file__), fname)).read()
+
+
+def get_version():
+ init = read(os.path.join('sql', '__init__.py'))
+ return re.search("__version__ = '([0-9.]*)'", init).group(1)
+
+setup(name='python-sql',
+ version=get_version(),
+ description='Library to write SQL queries',
+ long_description=read('README'),
+ author='B2CK',
+ author_email='info at b2ck.com',
+ url='http://code.google.com/p/python-sql/',
+ packages=find_packages(),
+ classifiers=[
+ 'Development Status :: 5 - Production/Stable',
+ 'Intended Audience :: Developers',
+ 'License :: OSI Approved :: BSD License',
+ 'Operating System :: OS Independent',
+ 'Programming Language :: Python :: 2.6',
+ 'Programming Language :: Python :: 2.7',
+ 'Programming Language :: Python :: 3',
+ 'Topic :: Database',
+ 'Topic :: Software Development :: Libraries :: Python Modules',
+ ],
+ license='BSD',
+ test_suite='sql.tests',
+ use_2to3=True,
+ )
diff --git a/sql/__init__.py b/sql/__init__.py
new file mode 100644
index 0000000..2c65f6b
--- /dev/null
+++ b/sql/__init__.py
@@ -0,0 +1,1075 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2013, Nicolas Évrard
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+from __future__ import division
+
+__version__ = '0.2'
+__all__ = ['Flavor', 'Table', 'Literal', 'Column', 'Join', 'Asc', 'Desc']
+
+import string
+from functools import partial
+from threading import local, currentThread
+from collections import defaultdict
+
+
+def alias(i, letters=string.ascii_lowercase):
+ '''
+ Generate a unique alias based on integer
+
+ >>> [alias(n) for n in range(6)]
+ ['a', 'b', 'c', 'd', 'e', 'f']
+ >>> [alias(n) for n in range(26, 30)]
+ ['ba', 'bb', 'bc', 'bd']
+ >>> [alias(26**n) for n in range(5)]
+ ['b', 'ba', 'baa', 'baaa', 'baaaa']
+ '''
+ s = ''
+ length = len(letters)
+ while True:
+ r = i % length
+ s = letters[r] + s
+ i //= length
+ if i == 0:
+ break
+ return s
+
+
+class Flavor(object):
+ '''
+ Contains the flavor of SQL
+
+ Contains:
+ max_limit - limit to use if there is no limit but an offset
+ paramstyle - state the type of parameter marker formatting
+ ilike - support ilike extension
+ function_mapping - dictionary with Function to replace
+ '''
+
+ def __init__(self, max_limit=None, paramstyle='format', ilike=False,
+ function_mapping=None):
+ self.max_limit = max_limit
+ self.paramstyle = paramstyle
+ self.ilike = ilike
+ self.function_mapping = function_mapping or {}
+
+ @property
+ def param(self):
+ if self.paramstyle == 'format':
+ return '%s'
+ elif self.paramstyle == 'qmark':
+ return '?'
+
+ @staticmethod
+ def set(flavor):
+ '''Set this thread's flavor to flavor.'''
+ currentThread().__sql_flavor__ = flavor
+
+ @staticmethod
+ def get():
+ '''
+ Return this thread's flavor.
+
+ If this thread does not yet have a flavor, returns a new flavor and
+ sets this thread's flavor.
+ '''
+ try:
+ return currentThread().__sql_flavor__
+ except AttributeError:
+ flavor = Flavor()
+ currentThread().__sql_flavor__ = flavor
+ return flavor
+
+
+class AliasManager(object):
+ '''
+ Context Manager for unique alias generation
+ '''
+ __slots__ = 'local'
+
+ local = local()
+ local.alias = None
+ local.nested = 0
+
+ @classmethod
+ def __enter__(cls):
+ if getattr(cls.local, 'alias', None) is None:
+ cls.local.alias = defaultdict(cls.alias_factory)
+ cls.local.nested = 0
+ cls.local.nested += 1
+
+ @classmethod
+ def __exit__(cls, type, value, traceback):
+ cls.local.nested -= 1
+ if not cls.local.nested:
+ cls.local.alias = None
+
+ @classmethod
+ def get(cls, from_):
+ if getattr(cls.local, 'alias', None) is None:
+ return ''
+ return cls.local.alias[from_]
+
+ @classmethod
+ def set(cls, from_, alias):
+ assert cls.local.alias.get(from_) is None
+ cls.local.alias[from_] = alias
+
+ @classmethod
+ def alias_factory(cls):
+ i = len(cls.local.alias)
+ return alias(i)
+
+
+class Query(object):
+ __slots__ = ()
+
+ @property
+ def params(self):
+ return ()
+
+ def __iter__(self):
+ yield str(self)
+ yield self.params
+
+
+class FromItem(object):
+ __slots__ = ()
+
+ @property
+ def alias(self):
+ return AliasManager.get(self)
+
+ def __getattr__(self, name):
+ return Column(self, name)
+
+ def __add__(self, other):
+ assert isinstance(other, FromItem)
+ return From((self, other))
+
+ def select(self, *args, **kwargs):
+ return From((self,)).select(*args, **kwargs)
+
+ def join(self, right, type_='INNER', condition=None):
+ return Join(self, right, type_=type_, condition=condition)
+
+
+class _SelectQueryMixin(object):
+ __slots__ = ('__order_by', '__limit', '__offset')
+
+ def __init__(self, *args, **kwargs):
+ self.__order_by = None
+ self.__limit = None
+ self.__offset = None
+ self.order_by = kwargs.pop('order_by', None)
+ self.limit = kwargs.pop('limit', None)
+ self.offset = kwargs.pop('offset', None)
+ super(_SelectQueryMixin, self).__init__(*args, **kwargs)
+
+ @property
+ def order_by(self):
+ return self.__order_by
+
+ @order_by.setter
+ def order_by(self, value):
+ if value is not None:
+ if isinstance(value, Expression):
+ value = [value]
+ assert all(isinstance(col, Expression) for col in value)
+ self.__order_by = value
+
+ @property
+ def _order_by_str(self):
+ order_by = ''
+ if self.order_by:
+ order_by = ' ORDER BY ' + ', '.join(map(str, self.order_by))
+ return order_by
+
+ @property
+ def limit(self):
+ return self.__limit
+
+ @limit.setter
+ def limit(self, value):
+ if value is not None:
+ assert isinstance(value, (int, long))
+ self.__limit = value
+
+ @property
+ def _limit_str(self):
+ limit = ''
+ if self.limit is not None:
+ limit = ' LIMIT %s' % self.limit
+ elif self.offset:
+ max_limit = Flavor.get().max_limit
+ if max_limit:
+ limit = ' LIMIT %s' % max_limit
+ return limit
+
+ @property
+ def offset(self):
+ return self.__offset
+
+ @offset.setter
+ def offset(self, value):
+ if value is not None:
+ assert isinstance(value, (int, long))
+ self.__offset = value
+
+ @property
+ def _offset_str(self):
+ offset = ''
+ if self.offset:
+ offset = ' OFFSET %s' % self.offset
+ return offset
+
+
+class Select(Query, FromItem, _SelectQueryMixin):
+ __slots__ = ('__columns', '__where', '__group_by', '__having', '__for_',
+ 'from_')
+
+ def __init__(self, columns, from_=None, where=None, group_by=None,
+ having=None, for_=None, **kwargs):
+ self.__columns = None
+ self.__where = None
+ self.__group_by = None
+ self.__having = None
+ self.__for_ = None
+ super(Select, self).__init__(**kwargs)
+ # TODO ALL|DISTINCT
+ self.columns = columns
+ self.from_ = from_
+ self.where = where
+ self.group_by = group_by
+ self.having = having
+ self.for_ = for_
+
+ @property
+ def columns(self):
+ return self.__columns
+
+ @columns.setter
+ def columns(self, value):
+ assert all(isinstance(col, Expression) for col in value)
+ self.__columns = tuple(value)
+
+ @property
+ def where(self):
+ return self.__where
+
+ @where.setter
+ def where(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self.__where = value
+
+ @property
+ def group_by(self):
+ return self.__group_by
+
+ @group_by.setter
+ def group_by(self, value):
+ if value is not None:
+ if isinstance(value, Expression):
+ value = [value]
+ assert all(isinstance(col, Expression) for col in value)
+ self.__group_by = value
+
+ @property
+ def having(self):
+ return self.__having
+
+ @having.setter
+ def having(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self.__having = value
+
+ @property
+ def for_(self):
+ return self.__for_
+
+ @for_.setter
+ def for_(self, value):
+ if value is not None:
+ if isinstance(value, For):
+ value = [value]
+ assert isinstance(all(isinstance(f, For) for f in value))
+ self.__for_ = value
+
+ @staticmethod
+ def _format_column(column):
+ if isinstance(column, As):
+ return '%s AS %s' % (column.expression, column)
+ else:
+ return str(column)
+
+ def __str__(self):
+ with AliasManager():
+ from_ = str(self.from_)
+ if self.columns:
+ columns = ', '.join(map(self._format_column, self.columns))
+ else:
+ columns = '*'
+ where = ''
+ if self.where:
+ where = ' WHERE ' + str(self.where)
+ group_by = ''
+ if self.group_by:
+ group_by = ' GROUP BY ' + ', '.join(map(str, self.group_by))
+ having = ''
+ if self.having:
+ having = ' HAVING ' + str(self.having)
+ for_ = ''
+ if self.for_ is not None:
+ for_ = ' '.join(self.for_)
+ return ('SELECT %s FROM %s' % (columns, from_) + where + group_by
+ + having + self._order_by_str + self._limit_str +
+ self._offset_str + for_)
+
+ @property
+ def params(self):
+ p = []
+ for column in self.columns:
+ if isinstance(column, As):
+ p.extend(column.expression.params)
+ p.extend(column.params)
+ p.extend(self.from_.params)
+ if self.where:
+ p.extend(self.where.params)
+ if self.group_by:
+ for expression in self.group_by:
+ p.extend(expression.params)
+ if self.having:
+ p.extend(self.having.params)
+ if self.order_by:
+ for expression in self.order_by:
+ p.extend(expression.params)
+ return tuple(p)
+
+ def __or__(self, other):
+ return Union(self, other)
+
+ def __and__(self, other):
+ return Interesect(self, other)
+
+ def __sub__(self, other):
+ return Except(self, other)
+
+
+class Insert(Query):
+ __slots__ = ('__table', '__columns', '__values', '__returning')
+
+ def __init__(self, table, columns=None, values=None, returning=None):
+ self.__table = None
+ self.__columns = None
+ self.__values = None
+ self.__returning = None
+ self.table = table
+ self.columns = columns
+ self.values = values
+ self.returning = returning
+
+ @property
+ def table(self):
+ return self.__table
+
+ @table.setter
+ def table(self, value):
+ assert isinstance(value, Table)
+ self.__table = value
+
+ @property
+ def columns(self):
+ return self.__columns
+
+ @columns.setter
+ def columns(self, value):
+ if value is not None:
+ assert all(isinstance(col, Column) for col in value)
+ assert all(col.table == self.table for col in value)
+ self.__columns = value
+
+ @property
+ def values(self):
+ return self.__values
+
+ @values.setter
+ def values(self, value):
+ if value is not None:
+ assert isinstance(value, (list, Select))
+ self.__values = value
+
+ @property
+ def returning(self):
+ return self.__returning
+
+ @returning.setter
+ def returning(self, value):
+ if value is not None:
+ assert isinstance(value, list)
+ self.__returning = value
+
+ @staticmethod
+ def _format(value, param=None):
+ if param is None:
+ param = Flavor.get().param
+ if isinstance(value, Expression):
+ return str(value)
+ elif isinstance(value, Select):
+ return '(%s)' % value
+ else:
+ return param
+
+ def __str__(self):
+ columns = ''
+ if self.columns:
+ assert all(col.table == self.table for col in self.columns)
+ columns = ' (' + ', '.join(map(str, self.columns)) + ')'
+ if isinstance(self.values, list):
+ format_ = partial(self._format, param=Flavor.get().param)
+ values = ' VALUES ' + ', '.join(
+ '(' + ', '.join(map(format_, v)) + ')'
+ for v in self.values)
+ # TODO manage DEFAULT
+ elif isinstance(self.values, Select):
+ values = ' %s' % str(self.values)
+ elif self.values is None:
+ values = ' DEFAULT VALUES'
+ returning = ''
+ if self.returning:
+ returning = ' RETURNING ' + ', '.join(map(str, self.returning))
+ return 'INSERT INTO %s' % self.table + columns + values + returning
+
+ @property
+ def params(self):
+ if isinstance(self.values, list):
+ p = []
+ for values in self.values:
+ for value in values:
+ if isinstance(value, (Expression, Select)):
+ p.extend(value.params)
+ else:
+ p.append(value)
+ return tuple(p)
+ elif isinstance(self.values, Select):
+ return self.values.params
+ else:
+ return ()
+
+
+class Update(Insert):
+ __slots__ = ('__where', '__values', 'from_')
+
+ def __init__(self, table, columns, values, from_=None, where=None):
+ super(Update, self).__init__(table, columns=columns, values=values)
+ self.__where = None
+ self.from_ = From(from_) if from_ else None
+ self.where = where
+
+ @property
+ def values(self):
+ return self.__values
+
+ @values.setter
+ def values(self, value):
+ if isinstance(value, Select):
+ value = [value]
+ assert isinstance(value, list)
+ self.__values = value
+
+ @property
+ def where(self):
+ return self.__where
+
+ @where.setter
+ def where(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self.__where = value
+
+ def __str__(self):
+ assert all(col.table == self.table for col in self.columns)
+ # Get columns without alias
+ columns = map(str, self.columns)
+
+ with AliasManager():
+ from_ = ''
+ if self.from_:
+ table = From([self.table])
+ from_ = ' FROM %s' % str(self.from_)
+ else:
+ table = self.table
+ AliasManager.set(table, str(table)[1:-1])
+ values = ', '.join('%s = %s' % (c, self._format(v))
+ for c, v in zip(columns, self.values))
+ where = ''
+ if self.where:
+ where = ' WHERE ' + str(self.where)
+ return 'UPDATE %s SET ' % table + values + from_ + where
+
+ @property
+ def params(self):
+ p = []
+ for value in self.values:
+ if isinstance(value, (Expression, Select)):
+ p.extend(value.params)
+ else:
+ p.append(value)
+ if self.from_:
+ p.extend(self.from_.params)
+ if self.where:
+ p.extend(self.where.params)
+ return tuple(p)
+
+
+class Delete(Query):
+ __slots__ = ('__table', '__where', '__returning', 'only')
+
+ def __init__(self, table, only=False, using=None, where=None,
+ returning=None):
+ self.__table = None
+ self.__where = None
+ self.__returning = None
+ self.table = table
+ self.only = only
+ # TODO using (not standard)
+ self.where = where
+ self.returning = returning
+
+ @property
+ def table(self):
+ return self.__table
+
+ @table.setter
+ def table(self, value):
+ assert isinstance(value, Table)
+ self.__table = value
+
+ @property
+ def where(self):
+ return self.__where
+
+ @where.setter
+ def where(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self.__where = value
+
+ @property
+ def returning(self):
+ return self.__returning
+
+ @returning.setter
+ def returning(self, value):
+ if value is not None:
+ assert isinstance(value, list)
+ self.__returning = value
+
+ def __str__(self):
+ only = ' ONLY' if self.only else ''
+ where = ''
+ if self.where:
+ where = ' WHERE ' + str(self.where)
+ returning = ''
+ if self.returning:
+ returning = ' RETURNING ' + ', '.join(map(str, self.returning))
+ return 'DELETE FROM%s %s' % (only, self.table) + where + returning
+
+ @property
+ def params(self):
+ return self.where.params if self.where else ()
+
+
+class CombiningQuery(Query, FromItem, _SelectQueryMixin):
+ __slots__ = ('queries', 'all_')
+ _operator = ''
+
+ def __init__(self, *queries, **kwargs):
+ assert all(isinstance(q, _SelectQueryMixin) for q in queries)
+ self.queries = queries
+ self.all_ = kwargs.pop('all_', False)
+ super(CombiningQuery, self).__init__(**kwargs)
+
+ def __str__(self):
+ with AliasManager():
+ operator = ' %s %s' % (self._operator, 'ALL ' if self.all_ else '')
+ return (operator.join(map(str, self.queries)) + self._order_by_str
+ + self._limit_str + self._offset_str)
+
+ @property
+ def params(self):
+ p = []
+ for q in self.queries:
+ p.extend(q.params)
+ if self.order_by:
+ for expression in self.order_by:
+ p.extend(expression.params)
+ return tuple(p)
+
+
+class Union(CombiningQuery):
+ __slots__ = ()
+ _operator = 'UNION'
+
+
+class Interesect(CombiningQuery):
+ __slots__ = ()
+ _operator = 'INTERSECT'
+
+
+class Except(CombiningQuery):
+ __slots__ = ()
+ _operator = 'EXCEPT'
+
+
+class Table(FromItem):
+ __slots__ = '__name'
+
+ def __init__(self, name):
+ super(Table, self).__init__()
+ self.__name = name
+
+ def __str__(self):
+ return '"%s"' % self.__name
+
+ @property
+ def params(self):
+ return ()
+
+ def insert(self, columns=None, values=None, returning=None):
+ return Insert(self, columns=columns, values=values,
+ returning=returning)
+
+ def update(self, columns, values, from_=None, where=None):
+ return Update(self, columns=columns, values=values, from_=from_,
+ where=where)
+
+ def delete(self, only=False, using=None, where=None, returning=None):
+ return Delete(self, only=only, using=using, where=where,
+ returning=returning)
+
+
+class Join(FromItem):
+ __slots__ = ('__left', '__right', '__condition', '__type_')
+
+ def __init__(self, left, right, type_='INNER', condition=None):
+ super(Join, self).__init__()
+ self.__left, self.__right = None, None
+ self.__condition = None
+ self.__type_ = None
+ self.left = left
+ self.right = right
+ self.condition = condition
+ self.type_ = type_
+
+ @property
+ def left(self):
+ return self.__left
+
+ @left.setter
+ def left(self, value):
+ assert isinstance(value, FromItem)
+ self.__left = value
+
+ @property
+ def right(self):
+ return self.__right
+
+ @right.setter
+ def right(self, value):
+ assert isinstance(value, FromItem)
+ self.__right = value
+
+ @property
+ def condition(self):
+ return self.__condition
+
+ @condition.setter
+ def condition(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self.__condition = value
+
+ @property
+ def type_(self):
+ return self.__type_
+
+ @type_.setter
+ def type_(self, value):
+ value = value.upper()
+ assert value in ('INNER', 'LEFT', 'LEFT OUTER',
+ 'RIGHT', 'RIGHT OUTER', 'FULL', 'FULL OUTER', 'CROSS')
+ self.__type_ = value
+
+ def __str__(self):
+ join = '%s %s JOIN %s' % (From([self.left]), self.type_,
+ From([self.right]))
+ if self.condition:
+ condition = ' ON %s' % self.condition
+ else:
+ condition = ''
+ return join + condition
+
+ @property
+ def params(self):
+ p = []
+ for item in (self.left, self.right):
+ if hasattr(item, 'params'):
+ p.extend(item.params)
+ if hasattr(self.condition, 'params'):
+ p.extend(self.condition.params)
+ return tuple(p)
+
+ @property
+ def alias(self):
+ raise AttributeError
+
+ def __getattr__(self, name):
+ raise AttributeError
+
+ def select(self, *args, **kwargs):
+ return super(Join, self).select(*args, **kwargs)
+
+
+class From(list):
+ __slots__ = ()
+
+ def select(self, *args, **kwargs):
+ return Select(args, from_=self, **kwargs)
+
+ def __str__(self):
+ def format(from_):
+ if isinstance(from_, Query):
+ return '(%s) AS "%s"' % (from_, from_.alias)
+ else:
+ alias = getattr(from_, 'alias', None)
+ if alias:
+ return '%s AS "%s"' % (from_, alias)
+ else:
+ return str(from_)
+ return ', '.join(map(format, self))
+
+ @property
+ def params(self):
+ p = []
+ for from_ in self:
+ p.extend(from_.params)
+ return tuple(p)
+
+ def __add__(self, other):
+ assert isinstance(other, (Join, Select))
+ return From(super(From, self).__add__([other]))
+
+
+class Expression(object):
+ __slots__ = ()
+
+ def __str__(self):
+ raise NotImplementedError
+
+ @property
+ def params(self):
+ raise NotImplementedError
+
+ def __and__(self, other):
+ from sql.operators import And
+ return And((self, other))
+
+ def __or__(self, other):
+ from sql.operators import Or
+ return Or((self, other))
+
+ def __invert__(self):
+ from sql.operators import Not
+ return Not(self)
+
+ def __add__(self, other):
+ from sql.operators import Add
+ return Add(self, other)
+
+ def __sub__(self, other):
+ from sql.operators import Sub
+ return Sub(self, other)
+
+ def __mul__(self, other):
+ from sql.operators import Mul
+ return Mul(self, other)
+
+ def __div__(self, other):
+ from sql.operators import Div
+ return Div(self, other)
+
+ def __floordiv__(self, other):
+ from sql.functions import Div
+ return Div(self, other)
+
+ def __mod__(self, other):
+ from sql.operators import Mod
+ return Mod(self, other)
+
+ def __pow__(self, other):
+ from sql.operators import Pow
+ return Pow(self, other)
+
+ def __neg__(self):
+ from sql.operators import Neg
+ return Neg(self)
+
+ def __pos__(self):
+ from sql.operators import Pos
+ return Pos(self)
+
+ def __abs__(self):
+ from sql.operators import Abs
+ return Abs(self)
+
+ def __lshift__(self, other):
+ from sql.operators import LShift
+ return LShift(self, other)
+
+ def __rshift__(self, other):
+ from sql.operators import RShift
+ return RShift(self, other)
+
+ def __lt__(self, other):
+ from sql.operators import Less
+ return Less(self, other)
+
+ def __le__(self, other):
+ from sql.operators import LessEqual
+ return LessEqual(self, other)
+
+ def __eq__(self, other):
+ from sql.operators import Equal
+ return Equal(self, other)
+
+ # When overriding __eq__, __hash__ is implicitly set to None
+ __hash__ = object.__hash__
+
+ def __ne__(self, other):
+ from sql.operators import NotEqual
+ return NotEqual(self, other)
+
+ def __gt__(self, other):
+ from sql.operators import Greater
+ return Greater(self, other)
+
+ def __ge__(self, other):
+ from sql.operators import GreaterEqual
+ return GreaterEqual(self, other)
+
+ def in_(self, values):
+ from sql.operators import In
+ return In(self, values)
+
+ def like(self, test):
+ from sql.operators import Like
+ return Like(self, test)
+
+ def ilike(self, test):
+ from sql.operators import ILike
+ return ILike(self, test)
+
+ def as_(self, output_name):
+ return As(self, output_name)
+
+ def cast(self, typename):
+ return Cast(self, typename)
+
+ @property
+ def asc(self):
+ return Asc(self)
+
+ @property
+ def desc(self):
+ return Desc(self)
+
+
+class Literal(Expression):
+ __slots__ = ('__value')
+
+ def __init__(self, value):
+ super(Literal, self).__init__()
+ self.__value = value
+
+ @property
+ def value(self):
+ return self.__value
+
+ def __str__(self):
+ return Flavor.get().param
+
+ @property
+ def params(self):
+ return (self.__value,)
+
+
+class Column(Expression):
+ __slots__ = ('__from', '__name')
+
+ def __init__(self, from_, name):
+ super(Column, self).__init__()
+ self.__from = from_
+ self.__name = name
+
+ @property
+ def table(self):
+ return self.__from
+
+ @property
+ def name(self):
+ return self.__name
+
+ def __str__(self):
+ if self.__name == '*':
+ t = '%s'
+ else:
+ t = '"%s"'
+ alias = self.__from.alias
+ if alias:
+ t = '"%s".' + t
+ return t % (alias, self.__name)
+ else:
+ return t % self.__name
+
+ @property
+ def params(self):
+ return ()
+
+
+class As(Expression):
+ __slots__ = ('expression', 'output_name')
+
+ def __init__(self, expression, output_name):
+ super(As, self).__init__()
+ self.expression = expression
+ self.output_name = output_name
+
+ def __str__(self):
+ return '"%s"' % self.output_name
+
+ @property
+ def params(self):
+ return ()
+
+
+class Cast(Expression):
+ __slots__ = ('expression', 'typename')
+
+ def __init__(self, expression, typename):
+ super(Expression, self).__init__()
+ self.expression = expression
+ self.typename = typename
+
+ def __str__(self):
+ if isinstance(self.expression, Expression):
+ value = self.expression
+ else:
+ value = Flavor.get().param
+ return 'CAST(%s AS %s)' % (value, self.typename)
+
+ @property
+ def params(self):
+ if isinstance(self.expression, Expression):
+ return self.expression.params
+ else:
+ return (self.expression,)
+
+
+class Order(Expression):
+ __slots__ = ('expression')
+ _sql = ''
+
+ def __init__(self, expression):
+ super(Order, self).__init__()
+ self.expression = expression
+ # TODO USING
+
+ def __str__(self):
+ return '%s %s' % (self.expression, self._sql)
+
+ @property
+ def params(self):
+ return self.expression.params
+
+
+class Asc(Order):
+ __slots__ = ()
+ _sql = 'ASC'
+
+
+class Desc(Order):
+ __slots__ = ()
+ _sql = 'DESC'
+
+
+class For(object):
+ __slots__ = ('__tables', '__type_', 'nowait')
+
+ def __init__(self, type_, *tables, **kwargs):
+ self.__tables = None
+ self.__type_ = None
+ self.tables = list(tables)
+ self.type_ = type_
+ self.nowait = kwargs.get('nowait')
+
+ @property
+ def tables(self):
+ return self.__tables
+
+ @tables.setter
+ def tables(self, value):
+ if not isinstance(value, list):
+ value = list(value)
+ all(isinstance(table, Table) for table in value)
+ self.__tables = value
+
+ @property
+ def type_(self):
+ return self.__type_
+
+ @type_.setter
+ def type_(self, value):
+ value = value.upper()
+ assert value in ('UPDATE', 'SHARE')
+ self.__type_ = value
+
+ def __str__(self):
+ tables = ''
+ if self.tables:
+ tables = ' OF ' + ', '.join(map(str, self.tables))
+ nowait = ''
+ if self.nowait:
+ nowait = ' NOWAIT'
+ return ('FOR %s' % self.type_) + tables + nowait
diff --git a/sql/aggregate.py b/sql/aggregate.py
new file mode 100644
index 0000000..0835031
--- /dev/null
+++ b/sql/aggregate.py
@@ -0,0 +1,110 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+
+from sql import Expression
+
+__all__ = ['Avg', 'BitAnd', 'BitOr', 'BoolAnd', 'BoolOr', 'Count', 'Every',
+ 'Max', 'Min', 'Stddev', 'Sum', 'Variance']
+
+
+class Aggregate(Expression):
+ __slots__ = ('expression')
+ _sql = ''
+
+ def __init__(self, expression):
+ super(Aggregate, self).__init__()
+ self.expression = expression
+
+ def __str__(self):
+ return '%s(%s)' % (self._sql, self.expression)
+
+ @property
+ def params(self):
+ return self.expression.params
+
+
+class Avg(Aggregate):
+ __slots__ = ()
+ _sql = 'AVG'
+
+
+class BitAnd(Aggregate):
+ __slots__ = ()
+ _sql = 'BIT_AND'
+
+
+class BitOr(Aggregate):
+ __slots__ = ()
+ _sql = 'BIT_OR'
+
+
+class BoolAnd(Aggregate):
+ __slots__ = ()
+ _sql = 'BOOL_AND'
+
+
+class BoolOr(Aggregate):
+ __slots__ = ()
+ _sql = 'BOOL_OR'
+
+
+class Count(Aggregate):
+ __slots__ = ()
+ _sql = 'COUNT'
+
+
+class Every(Aggregate):
+ __slots__ = ()
+ _sql = 'EVERY'
+
+
+class Max(Aggregate):
+ __slots__ = ()
+ _sql = 'MAX'
+
+
+class Min(Aggregate):
+ __slots__ = ()
+ _sql = 'MIN'
+
+
+class Stddev(Aggregate):
+ __slots__ = ()
+ _sql = 'Stddev'
+
+
+class Sum(Aggregate):
+ __slots__ = ()
+ _sql = 'SUM'
+
+
+class Variance(Aggregate):
+ __slots__ = ()
+ _sql = 'VARIANCE'
diff --git a/sql/conditionals.py b/sql/conditionals.py
new file mode 100644
index 0000000..766a49e
--- /dev/null
+++ b/sql/conditionals.py
@@ -0,0 +1,119 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+from sql import Expression, Flavor
+
+__all__ = ['Case', 'Coalesce', 'NullIf', 'Greatest', 'Least']
+
+
+class Conditional(Expression):
+ __slots__ = ()
+ _sql = ''
+ table = ''
+ name = ''
+
+ @staticmethod
+ def _format(value):
+ if isinstance(value, Expression):
+ return str(value)
+ else:
+ return Flavor().get().param
+
+
+class Case(Conditional):
+ __slots__ = ('whens', 'else_')
+
+ def __init__(self, *whens, **kwargs):
+ self.whens = whens
+ self.else_ = kwargs.get('else_')
+
+ def __str__(self):
+ case = 'CASE '
+ for cond, result in self.whens:
+ case += 'WHEN %s THEN %s ' % (
+ self._format(cond), self._format(result))
+ if self.else_ is not None:
+ case += 'ELSE %s ' % self._format(self.else_)
+ case += 'END'
+ return case
+
+ @property
+ def params(self):
+ p = []
+ for cond, result in self.whens:
+ if isinstance(cond, Expression):
+ p.extend(cond.params)
+ else:
+ p.append(cond)
+ if isinstance(result, Expression):
+ p.extend(result.params)
+ else:
+ p.append(result)
+ if self.else_ is not None:
+ if isinstance(self.else_, Expression):
+ p.extend(self.else_.params)
+ else:
+ p.append(self.else_)
+ return tuple(p)
+
+
+class Coalesce(Conditional):
+ __slots__ = ('values')
+ _conditional = 'COALESCE'
+
+ def __init__(self, *values):
+ self.values = values
+
+ def __str__(self):
+ return (self._conditional
+ + '(' + ', '.join(map(self._format, self.values)) + ')')
+
+ @property
+ def params(self):
+ p = []
+ for value in self.values:
+ if isinstance(value, Expression):
+ p.extend(value.params)
+ else:
+ p.append(value)
+ return tuple(p)
+
+
+class NullIf(Coalesce):
+ __slots__ = ()
+ _conditional = 'NULLIF'
+
+
+class Greatest(Coalesce):
+ __slots__ = ()
+ _conditional = 'GREATEST'
+
+
+class Least(Coalesce):
+ __slots__ = ()
+ _conditional = 'LEAST'
diff --git a/sql/functions.py b/sql/functions.py
new file mode 100644
index 0000000..58fb127
--- /dev/null
+++ b/sql/functions.py
@@ -0,0 +1,465 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+from itertools import chain
+
+from sql import Expression, Flavor, FromItem
+
+__all__ = ['Abs', 'Cbrt', 'Ceil', 'Degrees', 'Div', 'Exp', 'Floor', 'Ln',
+ 'Log', 'Mod', 'Pi', 'Power', 'Radians', 'Random', 'Round', 'SetSeed',
+ 'Sign', 'Sqrt', 'Trunc', 'WidthBucket',
+ 'Acos', 'Asin', 'Atan', 'Atan2', 'Cos', 'Cot', 'Sin', 'Tan',
+ 'BitLength', 'CharLength', 'Overlay', 'Position', 'Substring', 'Trim',
+ 'Upper',
+ 'ToChar', 'ToDate', 'ToNumber', 'ToTimestamp',
+ 'Age', 'ClockTimestamp', 'CurrentTime', 'CurrentTimestamp', 'DatePart',
+ 'DateTrunc', 'Extract', 'Isfinite', 'JustifyDays', 'JustifyHours',
+ 'JustifyInterval', 'Localtime', 'Localtimestamp', 'Now',
+ 'StatementTimestamp', 'Timeofday', 'TransactionTimestamp',
+ 'AtTimeZone']
+
+# Mathematical
+
+
+class Function(Expression, FromItem):
+ __slots__ = ('args',)
+ table = ''
+ name = ''
+ _function = ''
+
+ def __init__(self, *args):
+ self.args = args
+
+ @staticmethod
+ def _format(value):
+ if isinstance(value, Expression):
+ return str(value)
+ else:
+ return Flavor().get().param
+
+ def __str__(self):
+ Mapping = Flavor.get().function_mapping.get(self.__class__)
+ if Mapping:
+ return str(Mapping(*self.args))
+ return self._function + '(' + ', '.join(
+ map(self._format, self.args)) + ')'
+
+ @property
+ 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)
+
+
+class FunctionKeyword(Function):
+ __slots__ = ()
+ _function = ''
+ _keywords = ()
+
+ 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:]
+ + ')')
+
+
+class FunctionNotCallable(Function):
+ __slots__ = ()
+ _function = ''
+
+ def __str__(self):
+ Mapping = Flavor.get().function_mapping.get(self.__class__)
+ if Mapping:
+ return str(Mapping(*self.args))
+ return self._function
+
+
+class Abs(Function):
+ __slots__ = ()
+ _function = 'ABS'
+
+
+class Cbrt(Function):
+ __slots__ = ()
+ _function = 'CBRT'
+
+
+class Ceil(Function):
+ __slots__ = ()
+ _function = 'CEIL'
+
+
+class Degrees(Function):
+ __slots__ = ()
+ _function = 'DEGREES'
+
+
+class Div(Function):
+ __slots__ = ()
+ _function = 'DIV'
+
+
+class Exp(Function):
+ __slots__ = ()
+ _function = 'EXP'
+
+
+class Floor(Function):
+ __slots__ = ()
+ _function = 'FLOOR'
+
+
+class Ln(Function):
+ __slots__ = ()
+ _function = 'LN'
+
+
+class Log(Function):
+ __slots__ = ()
+ _function = 'LOG'
+
+
+class Mod(Function):
+ __slots__ = ()
+ _function = 'MOD'
+
+
+class Pi(Function):
+ __slots__ = ()
+ _function = 'PI'
+
+
+class Power(Function):
+ __slots__ = ()
+ _function = 'POWER'
+
+
+class Radians(Function):
+ __slots__ = ()
+ _function = 'RADIANS'
+
+
+class Random(Function):
+ __slots__ = ()
+ _function = 'RANDOM'
+
+
+class Round(Function):
+ __slots__ = ()
+ _function = 'ROUND'
+
+
+class SetSeed(Function):
+ __slots__ = ()
+ _function = 'SETSEED'
+
+
+class Sign(Function):
+ __slots__ = ()
+ _function = 'SIGN'
+
+
+class Sqrt(Function):
+ __slots__ = ()
+ _function = 'SQRT'
+
+
+class Trunc(Function):
+ __slots__ = ()
+ _function = 'TRUNC'
+
+
+class WidthBucket(Function):
+ __slots__ = ()
+ _function = 'WIDTH_BUCKET'
+
+# Trigonometric
+
+
+class Acos(Function):
+ __slots__ = ()
+ _function = 'ACOS'
+
+
+class Asin(Function):
+ __slots__ = ()
+ _function = 'ASIN'
+
+
+class Atan(Function):
+ __slots__ = ()
+ _function = 'ATAN'
+
+
+class Atan2(Function):
+ __slots__ = ()
+ _function = 'ATAN2'
+
+
+class Cos(Function):
+ __slots__ = ()
+ _function = 'Cos'
+
+
+class Cot(Function):
+ __slots__ = ()
+ _function = 'COT'
+
+
+class Sin(Function):
+ __slots__ = ()
+ _function = 'SIN'
+
+
+class Tan(Function):
+ __slots__ = ()
+ _function = 'TAN'
+
+# String
+
+
+class BitLength(Function):
+ __slots__ = ()
+ _function = 'BIT_LENGTH'
+
+
+class CharLength(Function):
+ __slots__ = ()
+ _function = 'CHAR_LENGTH'
+
+
+class Lower(Function):
+ __slots__ = ()
+ _function = 'LOWER'
+
+
+class OctetLength(Function):
+ __slots__ = ()
+ _function = 'OCTET_LENGTH'
+
+
+class Overlay(FunctionKeyword):
+ __slots__ = ()
+ _function = 'OVERLAY'
+ _keywords = ('', 'PLACING', 'FROM', 'FOR')
+
+
+class Position(FunctionKeyword):
+ __slots__ = ()
+ _function = 'POSITION'
+ _keywords = ('', 'IN')
+
+
+class Substring(FunctionKeyword):
+ __slots__ = ()
+ _function = 'SUBSTRING'
+ _keywords = ('', 'FROM', 'FOR')
+
+
+class Trim(Function):
+ __slots__ = ('position', 'characters', 'string')
+ _function = 'TRIM'
+
+ def __init__(self, string, position='BOTH', characters=' '):
+ assert position.upper() in ('LEADING', 'TRAILING', 'BOTH')
+ self.position = position.upper()
+ self.characters = characters
+ self.string = string
+
+ 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))
+
+ @property
+ 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.append(arg)
+ elif hasattr(arg, 'params'):
+ p.extend(arg.params)
+ return tuple(p)
+
+
+class Upper(Function):
+ __slots__ = ()
+ _function = 'UPPER'
+
+
+class ToChar(Function):
+ __slots__ = ()
+ _function = 'TO_CHAR'
+
+
+class ToDate(Function):
+ __slots__ = ()
+ _function = 'TO_DATE'
+
+
+class ToNumber(Function):
+ __slots__ = ()
+ _function = 'TO_NUMBER'
+
+
+class ToTimestamp(Function):
+ __slots__ = ()
+ _function = 'TO_TIMESTAMP'
+
+
+class Age(Function):
+ __slots__ = ()
+ _function = 'AGE'
+
+
+class ClockTimestamp(Function):
+ __slots__ = ()
+ _function = 'CLOCK_TIMESTAMP'
+
+
+class CurrentTime(FunctionNotCallable):
+ __slots__ = ()
+ _function = 'CURRENT_TIME'
+
+
+class CurrentTimestamp(FunctionNotCallable):
+ __slots__ = ()
+ _function = 'CURRENT_TIMESTAMP'
+
+
+class DatePart(Function):
+ __slots__ = ()
+ _function = 'DATE_PART'
+
+
+class DateTrunc(Function):
+ __slots__ = ()
+ _function = 'DateTrunc'
+
+
+class Extract(FunctionKeyword):
+ __slots__ = ()
+ _function = 'EXTRACT'
+ _keywords = ('', 'FROM')
+
+
+class Isfinite(Function):
+ __slots__ = ()
+ _function = 'ISFINITE'
+
+
+class JustifyDays(Function):
+ __slots__ = ()
+ _function = 'JUSTIFY_DAYS'
+
+
+class JustifyHours(Function):
+ __slots__ = ()
+ _function = 'JUSTIFY_HOURS'
+
+
+class JustifyInterval(Function):
+ __slots__ = ()
+ _function = 'JUSTIFY_INTERVAL'
+
+
+class Localtime(FunctionNotCallable):
+ __slots__ = ()
+ _function = 'LOCALTIME'
+
+
+class Localtimestamp(FunctionNotCallable):
+ __slots__ = ()
+ _function = 'LOCALTIMESTAMP'
+
+
+class Now(Function):
+ __slots__ = ()
+ _function = 'NOW'
+
+
+class StatementTimestamp(Function):
+ __slots__ = ()
+ _function = 'STATEMENT_TIMESTAMP'
+
+
+class Timeofday(Function):
+ __slots__ = ()
+ _function = 'TIMEOFDAY'
+
+
+class TransactionTimestamp(Function):
+ __slots__ = ()
+ _function = 'TRANSACTION_TIMESTAMP'
+
+
+class AtTimeZone(Function):
+ __slots__ = ('field', 'zone')
+
+ def __init__(self, field, zone):
+ self.field = field
+ self.zone = zone
+
+ def __str__(self):
+ flavor = Flavor.get()
+ Mapping = flavor.function_mapping.get(self.__class__)
+ if Mapping:
+ return str(Mapping(*self.args))
+ param = flavor.param
+ return '%s AT TIME ZONE %s' % (str(self.field), param)
+
+ @property
+ def params(self):
+ Mapping = Flavor.get().function_mapping.get(self.__class__)
+ if Mapping:
+ return Mapping(self.field, self.zone).params
+ return self.field.params + (self.zone,)
diff --git a/sql/operators.py b/sql/operators.py
new file mode 100644
index 0000000..87587b3
--- /dev/null
+++ b/sql/operators.py
@@ -0,0 +1,397 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+import warnings
+from array import array
+
+from sql import Expression, Select, CombiningQuery, Flavor
+
+__all__ = ['And', 'Or', 'Not', 'Less', 'Greater', 'LessEqual', 'GreaterEqual',
+ 'Equal', 'NotEqual', 'Add', 'Sub', 'Mul', 'Div', 'FloorDiv', 'Mod', 'Pow',
+ 'SquareRoot', 'CubeRoot', 'Factorial', 'Abs', 'BAnd', 'BOr', 'BXor',
+ 'BNot', 'LShift', 'RShift', 'Concat', 'Like', 'NotLike', 'ILike',
+ 'NotILike', 'In', 'NotIn', 'Exists', 'Any', 'Some', 'All']
+
+
+class Operator(Expression):
+ __slots__ = ()
+
+ @property
+ def table(self):
+ return ''
+
+ @property
+ def name(self):
+ return ''
+
+ @property
+ def _operands(self):
+ return ()
+
+ @property
+ def params(self):
+
+ def convert(operands):
+ params = []
+ for operand in operands:
+ if isinstance(operand, (Expression, Select, CombiningQuery)):
+ params.extend(operand.params)
+ elif isinstance(operand, (list, tuple)):
+ params.extend(convert(operand))
+ elif isinstance(operand, array):
+ params.extend(operand)
+ else:
+ params.append(operand)
+ return params
+ return tuple(convert(self._operands))
+
+ 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):
+ raise NotImplemented
+
+ def __and__(self, other):
+ if isinstance(other, And):
+ return And([self] + other)
+ else:
+ return And((self, other))
+
+ def __or__(self, other):
+ if isinstance(other, Or):
+ return Or([self] + other)
+ else:
+ return Or((self, other))
+
+
+class UnaryOperator(Operator):
+ __slots__ = 'operand'
+ _operator = ''
+
+ def __init__(self, operand):
+ self.operand = operand
+
+ @property
+ def _operands(self):
+ return (self.operand,)
+
+ def __str__(self):
+ return '(%s %s)' % (self._operator, self.operand)
+
+
+class BinaryOperator(Operator):
+ __slots__ = ('left', 'right')
+ _operator = ''
+
+ def __init__(self, left, right):
+ self.left = left
+ self.right = right
+
+ @property
+ def _operands(self):
+ return (self.left, self.right)
+
+ def __str__(self):
+ return '(%s %s %s)' % (self._format(self.left), self._operator,
+ self._format(self.right))
+
+ def __invert__(self):
+ return _INVERT[self.__class__](self.left, self.right)
+
+
+class NaryOperator(list, Operator):
+ __slots__ = ()
+ _operator = ''
+
+ @property
+ def _operands(self):
+ return self
+
+ def __str__(self):
+ return '(' + (' %s ' % self._operator).join(map(str, self)) + ')'
+
+
+class And(NaryOperator):
+ __slots__ = ()
+ _operator = 'AND'
+
+
+class Or(NaryOperator):
+ __slots__ = ()
+ _operator = 'OR'
+
+
+class Not(UnaryOperator):
+ __slots__ = ()
+ _operator = 'NOT'
+
+
+class Neg(UnaryOperator):
+ __slots__ = ()
+ _operator = '-'
+
+
+class Pos(UnaryOperator):
+ __slots__ = ()
+ _operator = '+'
+
+
+class Less(BinaryOperator):
+ __slots__ = ()
+ _operator = '<'
+
+
+class Greater(BinaryOperator):
+ __slots__ = ()
+ _operator = '>'
+
+
+class LessEqual(BinaryOperator):
+ __slots__ = ()
+ _operator = '<='
+
+
+class GreaterEqual(BinaryOperator):
+ __slots__ = ()
+ _operator = '>='
+
+
+class Equal(BinaryOperator):
+ __slots__ = ()
+ _operator = '='
+
+ @property
+ def _operands(self):
+ if self.left is None:
+ return (self.right,)
+ elif self.right is None:
+ return (self.left,)
+ return super(Equal, self)._operands
+
+ def __str__(self):
+ if self.left is None:
+ return '(%s IS NULL)' % self.right
+ elif self.right is None:
+ return '(%s IS NULL)' % self.left
+ return super(Equal, self).__str__()
+
+
+class NotEqual(Equal):
+ __slots__ = ()
+ _operator = '!='
+
+ def __str__(self):
+ if self.left is None:
+ return '(%s IS NOT NULL)' % self.right
+ elif self.right is None:
+ return '(%s IS NOT NULL)' % self.left
+ return super(Equal, self).__str__()
+
+
+class Add(BinaryOperator):
+ __slots__ = ()
+ _operator = '+'
+
+
+class Sub(BinaryOperator):
+ __slots__ = ()
+ _operator = '-'
+
+
+class Mul(BinaryOperator):
+ __slots__ = ()
+ _operator = '*'
+
+
+class Div(BinaryOperator):
+ __slots__ = ()
+ _operator = '/'
+
+
+# For backward compatibility
+class FloorDiv(BinaryOperator):
+ __slots__ = ()
+ _operator = '/'
+
+ def __init__(self, left, right):
+ warnings.warn('FloorDiv operator is deprecated, use Div function',
+ DeprecationWarning, stacklevel=2)
+ super(FloorDiv, self).__init__(left, right)
+
+
+class Mod(BinaryOperator):
+ __slots__ = ()
+ _operator = '%'
+
+
+class Pow(BinaryOperator):
+ __slots__ = ()
+ _operator = '^'
+
+
+class SquareRoot(UnaryOperator):
+ __slots__ = ()
+ _operator = '|/'
+
+
+class CubeRoot(UnaryOperator):
+ __slots__ = ()
+ _operator = '||/'
+
+
+class Factorial(UnaryOperator):
+ __slots__ = ()
+ _operator = '!!'
+
+
+class Abs(UnaryOperator):
+ __slots__ = ()
+ _operator = '@'
+
+
+class BAnd(BinaryOperator):
+ __slots__ = ()
+ _operator = '&'
+
+
+class BOr(BinaryOperator):
+ __slots__ = ()
+ _operator = '|'
+
+
+class BXor(BinaryOperator):
+ __slots__ = ()
+ _operator = '#'
+
+
+class BNot(UnaryOperator):
+ __slots__ = ()
+ _operator = '~'
+
+
+class LShift(BinaryOperator):
+ __slots__ = ()
+ _operator = '<<'
+
+
+class RShift(BinaryOperator):
+ __slots__ = ()
+ _operator = '>>'
+
+
+class Concat(BinaryOperator):
+ __slots__ = ()
+ _operator = '||'
+
+
+class Like(BinaryOperator):
+ __slots__ = ()
+ _operator = 'LIKE'
+
+
+class NotLike(BinaryOperator):
+ __slots__ = ()
+ _operator = 'NOT LIKE'
+
+
+class ILike(BinaryOperator):
+ __slots__ = ()
+
+ @property
+ def _operator(self):
+ if Flavor.get().ilike:
+ return 'ILIKE'
+ else:
+ return 'LIKE'
+
+
+class NotILike(BinaryOperator):
+ __slots__ = ()
+
+ @property
+ def _operator(self):
+ if Flavor.get().ilike:
+ return 'NOT ILIKE'
+ else:
+ return 'NOT LIKE'
+
+# TODO SIMILAR
+
+
+class In(BinaryOperator):
+ __slots__ = ()
+ _operator = 'IN'
+
+
+class NotIn(BinaryOperator):
+ __slots__ = ()
+ _operator = 'NOT IN'
+
+
+class Exists(UnaryOperator):
+ __slots__ = ()
+ _operator = 'EXISTS'
+
+
+class Any(UnaryOperator):
+ __slots__ = ()
+ _operator = 'ANY'
+
+Some = Any
+
+
+class All(UnaryOperator):
+ __slots__ = ()
+ _operator = 'ALL'
+
+
+_INVERT = {
+ Less: GreaterEqual,
+ Greater: LessEqual,
+ LessEqual: Greater,
+ GreaterEqual: Less,
+ Equal: NotEqual,
+ NotEqual: Equal,
+ Like: NotLike,
+ NotLike: Like,
+ ILike: NotILike,
+ NotILike: ILike,
+ In: NotIn,
+ NotIn: In,
+ }
diff --git a/sql/tests/__init__.py b/sql/tests/__init__.py
new file mode 100644
index 0000000..c262fba
--- /dev/null
+++ b/sql/tests/__init__.py
@@ -0,0 +1,70 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import sys
+import os
+import unittest
+import doctest
+
+import sql
+
+here = os.path.dirname(__file__)
+readme = os.path.normpath(os.path.join(here, '..', '..', 'README'))
+
+
+def test_suite():
+ suite = additional_tests()
+ loader = unittest.TestLoader()
+ for fn in os.listdir(here):
+ if fn.startswith('test') and fn.endswith('.py'):
+ modname = 'sql.tests.' + fn[:-3]
+ __import__(modname)
+ module = sys.modules[modname]
+ suite.addTests(loader.loadTestsFromModule(module))
+ return suite
+
+
+def additional_tests():
+ suite = unittest.TestSuite()
+ for mod in (sql,):
+ suite.addTest(doctest.DocTestSuite(mod))
+ if os.path.isfile(readme):
+ suite.addTest(doctest.DocFileSuite(readme, module_relative=False,
+ tearDown=lambda t: sql.Flavor.set(sql.Flavor())))
+ return suite
+
+
+def main():
+ suite = test_suite()
+ runner = unittest.TextTestRunner()
+ runner.run(suite)
+
+if __name__ == '__main__':
+ sys.path.insert(0, os.path.dirname(os.path.dirname(
+ os.path.dirname(os.path.abspath(__file__)))))
+ main()
diff --git a/sql/tests/test_aggregate.py b/sql/tests/test_aggregate.py
new file mode 100644
index 0000000..db59c67
--- /dev/null
+++ b/sql/tests/test_aggregate.py
@@ -0,0 +1,43 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table
+from sql.aggregate import Avg
+
+
+class TestAggregate(unittest.TestCase):
+ table = Table('t')
+
+ def test_avg(self):
+ avg = Avg(self.table.c)
+ self.assertEqual(str(avg), 'AVG("c")')
+
+ avg = Avg(self.table.a + self.table.b)
+ self.assertEqual(str(avg), 'AVG(("a" + "b"))')
diff --git a/sql/tests/test_alias.py b/sql/tests/test_alias.py
new file mode 100644
index 0000000..fcfed30
--- /dev/null
+++ b/sql/tests/test_alias.py
@@ -0,0 +1,89 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+import threading
+
+from sql import AliasManager, Table
+
+
+class TestAliasManager(unittest.TestCase):
+
+ def setUp(self):
+ self.synchro = threading.Event()
+ self.succeed1 = threading.Event()
+ self.succeed2 = threading.Event()
+ self.finish1 = threading.Event()
+ self.finish2 = threading.Event()
+
+ self.t1 = Table('t1')
+ self.t2 = Table('t2')
+
+ def func1(self):
+ try:
+ with AliasManager():
+ a1 = AliasManager.get(self.t1)
+ a2 = AliasManager.get(self.t2)
+ self.synchro.wait()
+ self.assertEqual(a1, AliasManager.get(self.t1))
+ self.assertEqual(a2, AliasManager.get(self.t2))
+ self.succeed1.set()
+ return
+ except Exception:
+ pass
+ finally:
+ self.finish1.set()
+
+ def func2(self):
+ try:
+ with AliasManager():
+ a2 = AliasManager.get(self.t2)
+ a1 = AliasManager.get(self.t1)
+ self.synchro.set()
+ self.assertEqual(a1, AliasManager.get(self.t1))
+ self.assertEqual(a2, AliasManager.get(self.t2))
+ self.succeed2.set()
+ return
+ except Exception:
+ pass
+ finally:
+ self.synchro.set()
+ self.finish2.set()
+
+ def test_threading(self):
+
+ th1 = threading.Thread(target=self.func1)
+ th2 = threading.Thread(target=self.func2)
+
+ th1.start()
+ th2.start()
+
+ self.finish1.wait()
+ self.finish2.wait()
+ if not self.succeed1.is_set() or not self.succeed2.is_set():
+ self.fail()
diff --git a/sql/tests/test_as.py b/sql/tests/test_as.py
new file mode 100644
index 0000000..8e18df9
--- /dev/null
+++ b/sql/tests/test_as.py
@@ -0,0 +1,44 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import As, Column, Table
+
+
+class TestOrder(unittest.TestCase):
+ table = Table('t')
+ column = Column(table, 'c')
+
+ def test_as(self):
+ self.assertEqual(str(As(self.column, 'foo')), '"foo"')
+
+ def test_as_select(self):
+ query = self.table.select(self.column.as_('foo'))
+ self.assertEqual(str(query), 'SELECT "a"."c" AS "foo" FROM "t" AS "a"')
+ self.assertEqual(query.params, ())
diff --git a/sql/tests/test_cast.py b/sql/tests/test_cast.py
new file mode 100644
index 0000000..a76b648
--- /dev/null
+++ b/sql/tests/test_cast.py
@@ -0,0 +1,44 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Cast, Column, Table
+
+
+class TestCast(unittest.TestCase):
+ column = Column(Table('t'), 'c')
+
+ def test_cast(self):
+ self.assertEqual(str(Cast(self.column, 'int')),
+ 'CAST("c" AS int)')
+
+ def test_cast_no_expression(self):
+ cast = Cast(1.1, 'int')
+ self.assertEqual(str(cast), 'CAST(%s AS int)')
+ self.assertEqual(cast.params, (1.1,))
diff --git a/sql/tests/test_column.py b/sql/tests/test_column.py
new file mode 100644
index 0000000..6be3168
--- /dev/null
+++ b/sql/tests/test_column.py
@@ -0,0 +1,40 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Column, Table, AliasManager
+
+
+class TestColumn(unittest.TestCase):
+ def test_column(self):
+ column = Column(Table('t'), 'c')
+ self.assertEqual(str(column), '"c"')
+
+ with AliasManager():
+ self.assertEqual(str(column), '"a"."c"')
diff --git a/sql/tests/test_conditionals.py b/sql/tests/test_conditionals.py
new file mode 100644
index 0000000..501d99d
--- /dev/null
+++ b/sql/tests/test_conditionals.py
@@ -0,0 +1,75 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table
+from sql.conditionals import Case, Coalesce, NullIf, Greatest, Least
+
+
+class TestConditionals(unittest.TestCase):
+ table = Table('t')
+
+ def test_case(self):
+ case = Case((self.table.c1, 'foo'),
+ (self.table.c2, 'bar'),
+ else_=self.table.c3)
+ self.assertEqual(str(case),
+ 'CASE WHEN "c1" THEN %s '
+ 'WHEN "c2" THEN %s '
+ 'ELSE "c3" END')
+ self.assertEqual(case.params, ('foo', 'bar'))
+
+ def test_case_no_expression(self):
+ case = Case((True, self.table.c1), (self.table.c2, False),
+ else_=False)
+ self.assertEqual(str(case),
+ 'CASE WHEN %s THEN "c1" '
+ 'WHEN "c2" THEN %s '
+ 'ELSE %s END')
+ self.assertEqual(case.params, (True, False, False))
+
+ def test_coalesce(self):
+ coalesce = Coalesce(self.table.c1, self.table.c2, 'foo')
+ self.assertEqual(str(coalesce), 'COALESCE("c1", "c2", %s)')
+ self.assertEqual(coalesce.params, ('foo',))
+
+ def test_nullif(self):
+ nullif = NullIf(self.table.c1, 'foo')
+ self.assertEqual(str(nullif), 'NULLIF("c1", %s)')
+ self.assertEqual(nullif.params, ('foo',))
+
+ def test_greatest(self):
+ greatest = Greatest(self.table.c1, self.table.c2, 'foo')
+ self.assertEqual(str(greatest), 'GREATEST("c1", "c2", %s)')
+ self.assertEqual(greatest.params, ('foo',))
+
+ def test_least(self):
+ least = Least(self.table.c1, self.table.c2, 'foo')
+ self.assertEqual(str(least), 'LEAST("c1", "c2", %s)')
+ self.assertEqual(least.params, ('foo',))
diff --git a/sql/tests/test_delete.py b/sql/tests/test_delete.py
new file mode 100644
index 0000000..2ba1474
--- /dev/null
+++ b/sql/tests/test_delete.py
@@ -0,0 +1,54 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table
+
+
+class TestDelete(unittest.TestCase):
+ table = Table('t')
+
+ def test_delete1(self):
+ query = self.table.delete()
+ self.assertEqual(str(query), 'DELETE FROM "t"')
+ self.assertEqual(query.params, ())
+
+ def test_delete2(self):
+ query = self.table.delete(where=(self.table.c == 'foo'))
+ self.assertEqual(str(query), 'DELETE FROM "t" WHERE ("c" = %s)')
+ self.assertEqual(query.params, ('foo',))
+
+ def test_delete3(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ query = t1.delete(where=(t1.c.in_(t2.select(t2.c))))
+ self.assertEqual(str(query),
+ 'DELETE FROM "t1" WHERE ("c" IN ('
+ 'SELECT "a"."c" FROM "t2" AS "a"))')
+ self.assertEqual(query.params, ())
diff --git a/sql/tests/test_for.py b/sql/tests/test_for.py
new file mode 100644
index 0000000..cf0b940
--- /dev/null
+++ b/sql/tests/test_for.py
@@ -0,0 +1,37 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import For, Table
+
+
+class TestFor(unittest.TestCase):
+ def test_for(self):
+ for_ = For('UPDATE', Table('t1'), Table('t2'), nowait=True)
+ self.assertEqual(str(for_), 'FOR UPDATE OF "t1", "t2" NOWAIT')
diff --git a/sql/tests/test_functions.py b/sql/tests/test_functions.py
new file mode 100644
index 0000000..3872187
--- /dev/null
+++ b/sql/tests/test_functions.py
@@ -0,0 +1,80 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table, Flavor
+from sql.functions import Function, Abs, Overlay, Trim, AtTimeZone
+
+
+class TestFunctions(unittest.TestCase):
+ table = Table('t')
+
+ def test_abs(self):
+ abs_ = Abs(self.table.c1)
+ self.assertEqual(str(abs_), 'ABS("c1")')
+ self.assertEqual(abs_.params, ())
+
+ abs_ = Abs(-12)
+ self.assertEqual(str(abs_), 'ABS(%s)')
+ self.assertEqual(abs_.params, (-12,))
+
+ 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_overlay(self):
+ overlay = Overlay(self.table.c1, 'test', 3)
+ self.assertEqual(str(overlay), 'OVERLAY("c1" PLACING %s FROM %s)')
+ self.assertEqual(overlay.params, ('test', 3))
+ overlay = Overlay(self.table.c1, 'test', 3, 7)
+ self.assertEqual(str(overlay),
+ 'OVERLAY("c1" PLACING %s FROM %s FOR %s)')
+ self.assertEqual(overlay.params, ('test', 3, 7))
+
+ def test_trim(self):
+ trim = Trim(' test ')
+ self.assertEqual(str(trim), 'TRIM(BOTH %s FROM %s)')
+ self.assertEqual(trim.params, (' ', ' test ',))
+
+ def test_at_time_zone(self):
+ time_zone = AtTimeZone(self.table.c1, 'UTC')
+ self.assertEqual(str(time_zone), '"c1" AT TIME ZONE %s')
+ self.assertEqual(time_zone.params, ('UTC',))
diff --git a/sql/tests/test_insert.py b/sql/tests/test_insert.py
new file mode 100644
index 0000000..92954a2
--- /dev/null
+++ b/sql/tests/test_insert.py
@@ -0,0 +1,72 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2013, Nicolas Évrard
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table
+from sql.functions import Abs
+
+
+class TestInsert(unittest.TestCase):
+ table = Table('t')
+
+ def test_insert_default(self):
+ query = self.table.insert()
+ self.assertEqual(str(query), 'INSERT INTO "t" DEFAULT VALUES')
+ self.assertEqual(query.params, ())
+
+ def test_insert_values(self):
+ query = self.table.insert([self.table.c1, self.table.c2],
+ [['foo', 'bar']])
+ self.assertEqual(str(query),
+ 'INSERT INTO "t" ("c1", "c2") VALUES (%s, %s)')
+ self.assertEqual(query.params, ('foo', 'bar'))
+
+ def test_insert_many_values(self):
+ query = self.table.insert([self.table.c1, self.table.c2],
+ [['foo', 'bar'], ['spam', 'eggs']])
+ self.assertEqual(str(query),
+ 'INSERT INTO "t" ("c1", "c2") VALUES (%s, %s), (%s, %s)')
+ self.assertEqual(query.params, ('foo', 'bar', 'spam', 'eggs'))
+
+ def test_insert_subselect(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ subquery = t2.select(t2.c1, t2.c2)
+ query = t1.insert([t1.c1, t1.c2], subquery)
+ self.assertEqual(str(query),
+ 'INSERT INTO "t1" ("c1", "c2") '
+ 'SELECT "a"."c1", "a"."c2" FROM "t2" AS "a"')
+ self.assertEqual(query.params, ())
+
+ def test_insert_function(self):
+ query = self.table.insert([self.table.c], [[Abs(-1)]])
+ self.assertEqual(str(query),
+ 'INSERT INTO "t" ("c") VALUES (ABS(%s))')
+ self.assertEqual(query.params, (-1,))
diff --git a/sql/tests/test_join.py b/sql/tests/test_join.py
new file mode 100644
index 0000000..76fe6dd
--- /dev/null
+++ b/sql/tests/test_join.py
@@ -0,0 +1,66 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Join, Table, AliasManager
+from sql.functions import Now
+
+
+class TestJoin(unittest.TestCase):
+ def test_join(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ join = Join(t1, t2)
+ with AliasManager():
+ self.assertEqual(str(join), '"t1" AS "a" INNER JOIN "t2" AS "b"')
+ self.assertEqual(join.params, ())
+
+ join.condition = t1.c == t2.c
+ with AliasManager():
+ self.assertEqual(str(join),
+ '"t1" AS "a" INNER JOIN "t2" AS "b" ON ("a"."c" = "b"."c")')
+
+ def test_join_subselect(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ select = t2.select()
+ join = Join(t1, select)
+ join.condition = t1.c == select.c
+ with AliasManager():
+ self.assertEqual(str(join),
+ '"t1" AS "a" INNER JOIN (SELECT * FROM "t2" AS "c") AS "b" '
+ 'ON ("a"."c" = "b"."c")')
+ self.assertEqual(join.params, ())
+
+ def test_join_function(self):
+ t1 = Table('t1')
+ join = Join(t1, Now())
+ with AliasManager():
+ self.assertEqual(str(join), '"t1" AS "a" INNER JOIN NOW() AS "b"')
+ self.assertEqual(join.params, ())
diff --git a/sql/tests/test_literal.py b/sql/tests/test_literal.py
new file mode 100644
index 0000000..7c8f3b4
--- /dev/null
+++ b/sql/tests/test_literal.py
@@ -0,0 +1,38 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Literal
+
+
+class TestLiteral(unittest.TestCase):
+ def test_literal(self):
+ literal = Literal(1)
+ self.assertEqual(str(literal), '%s')
+ self.assertEqual(literal.params, (1,))
diff --git a/sql/tests/test_operators.py b/sql/tests/test_operators.py
new file mode 100644
index 0000000..d726655
--- /dev/null
+++ b/sql/tests/test_operators.py
@@ -0,0 +1,134 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+import warnings
+from array import array
+
+from sql import Table, Literal
+from sql.operators import And, Not, Less, Equal, NotEqual, In, FloorDiv
+
+
+class TestOperators(unittest.TestCase):
+ table = Table('t')
+
+ def test_and(self):
+ and_ = And((self.table.c1, self.table.c2))
+ self.assertEqual(str(and_), '("c1" AND "c2")')
+ self.assertEqual(and_.params, ())
+
+ and_ = And((Literal(True), self.table.c2))
+ self.assertEqual(str(and_), '(%s AND "c2")')
+ self.assertEqual(and_.params, (True,))
+
+ def test_not(self):
+ not_ = Not(self.table.c)
+ self.assertEqual(str(not_), '(NOT "c")')
+ self.assertEqual(not_.params, ())
+
+ not_ = Not(Literal(False))
+ self.assertEqual(str(not_), '(NOT %s)')
+ self.assertEqual(not_.params, (False,))
+
+ def test_less(self):
+ less = Less(self.table.c1, self.table.c2)
+ self.assertEqual(str(less), '("c1" < "c2")')
+ self.assertEqual(less.params, ())
+
+ less = Less(Literal(0), self.table.c2)
+ self.assertEqual(str(less), '(%s < "c2")')
+ self.assertEqual(less.params, (0,))
+
+ def test_equal(self):
+ equal = Equal(self.table.c1, self.table.c2)
+ self.assertEqual(str(equal), '("c1" = "c2")')
+ self.assertEqual(equal.params, ())
+
+ equal = Equal(Literal('foo'), Literal('bar'))
+ self.assertEqual(str(equal), '(%s = %s)')
+ self.assertEqual(equal.params, ('foo', 'bar'))
+
+ equal = Equal(self.table.c1, None)
+ self.assertEqual(str(equal), '("c1" IS NULL)')
+ self.assertEqual(equal.params, ())
+
+ equal = Equal(Literal('test'), None)
+ self.assertEqual(str(equal), '(%s IS NULL)')
+ self.assertEqual(equal.params, ('test',))
+
+ equal = Equal(None, self.table.c1)
+ self.assertEqual(str(equal), '("c1" IS NULL)')
+ self.assertEqual(equal.params, ())
+
+ equal = Equal(None, Literal('test'))
+ self.assertEqual(str(equal), '(%s IS NULL)')
+ self.assertEqual(equal.params, ('test',))
+
+ def test_not_equal(self):
+ equal = NotEqual(self.table.c1, self.table.c2)
+ self.assertEqual(str(equal), '("c1" != "c2")')
+ self.assertEqual(equal.params, ())
+
+ equal = NotEqual(self.table.c1, None)
+ self.assertEqual(str(equal), '("c1" IS NOT NULL)')
+ self.assertEqual(equal.params, ())
+
+ equal = NotEqual(None, self.table.c1)
+ self.assertEqual(str(equal), '("c1" IS NOT NULL)')
+ self.assertEqual(equal.params, ())
+
+ def test_in(self):
+ in_ = In(self.table.c1, [self.table.c2, 1, None])
+ self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))')
+ self.assertEqual(in_.params, (1, None))
+
+ t2 = Table('t2')
+ in_ = In(self.table.c1, t2.select(t2.c2))
+ self.assertEqual(str(in_),
+ '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))')
+ self.assertEqual(in_.params, ())
+
+ in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3))
+ self.assertEqual(str(in_),
+ '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" '
+ 'UNION SELECT "a"."c3" FROM "t2" AS "a"))')
+ self.assertEqual(in_.params, ())
+
+ in_ = In(self.table.c1, array('l', range(10)))
+ self.assertEqual(str(in_),
+ '("c1" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s))')
+ self.assertEqual(in_.params, tuple(range(10)))
+
+ def test_floordiv(self):
+ with warnings.catch_warnings(record=True) as w:
+ warnings.simplefilter("always")
+ FloorDiv(4, 2)
+ self.assertEqual(len(w), 1)
+ self.assertTrue(issubclass(w[-1].category, DeprecationWarning))
+ self.assertIn('FloorDiv operator is deprecated, use Div function',
+ str(w[-1].message))
diff --git a/sql/tests/test_order.py b/sql/tests/test_order.py
new file mode 100644
index 0000000..6d859cd
--- /dev/null
+++ b/sql/tests/test_order.py
@@ -0,0 +1,41 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Asc, Desc, Column, Table
+
+
+class TestOrder(unittest.TestCase):
+ column = Column(Table('t'), 'c')
+
+ def test_asc(self):
+ self.assertEqual(str(Asc(self.column)), '"c" ASC')
+
+ def test_desc(self):
+ self.assertEqual(str(Desc(self.column)), '"c" DESC')
diff --git a/sql/tests/test_select.py b/sql/tests/test_select.py
new file mode 100644
index 0000000..b17142f
--- /dev/null
+++ b/sql/tests/test_select.py
@@ -0,0 +1,186 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2013, Nicolas Évrard
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table, Join, Union, Literal, Flavor
+from sql.functions import Now
+from sql.aggregate import Min
+
+
+class TestSelect(unittest.TestCase):
+ table = Table('t')
+
+ def test_select1(self):
+ query = self.table.select()
+ self.assertEqual(str(query), 'SELECT * FROM "t" AS "a"')
+ self.assertEqual(query.params, ())
+
+ def test_select2(self):
+ query = self.table.select(self.table.c)
+ self.assertEqual(str(query), 'SELECT "a"."c" FROM "t" AS "a"')
+ self.assertEqual(query.params, ())
+
+ query.columns += (self.table.c2,)
+ self.assertEqual(str(query),
+ 'SELECT "a"."c", "a"."c2" FROM "t" AS "a"')
+
+ def test_select3(self):
+ query = self.table.select(where=(self.table.c == 'foo'))
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a" WHERE ("a"."c" = %s)')
+ self.assertEqual(query.params, ('foo',))
+
+ def test_select_union(self):
+ query1 = self.table.select()
+ query2 = Table('t2').select()
+ union = query1 | query2
+ self.assertEqual(str(union),
+ 'SELECT * FROM "t" AS "a" UNION SELECT * FROM "t2" AS "b"')
+ union.all_ = True
+ self.assertEqual(str(union),
+ 'SELECT * FROM "t" AS "a" UNION ALL '
+ 'SELECT * FROM "t2" AS "b"')
+ self.assertEqual(str(union.select()),
+ 'SELECT * FROM ('
+ 'SELECT * FROM "t" AS "b" UNION ALL '
+ 'SELECT * FROM "t2" AS "c") AS "a"')
+ query1.where = self.table.c == 'foo'
+ self.assertEqual(str(union),
+ 'SELECT * FROM "t" AS "a" WHERE ("a"."c" = %s) UNION ALL '
+ 'SELECT * FROM "t2" AS "b"')
+ self.assertEqual(union.params, ('foo',))
+
+ union = Union(query1)
+ self.assertEqual(str(union), str(query1))
+ self.assertEqual(union.params, query1.params)
+
+ def test_select_union_order(self):
+ query1 = self.table.select()
+ query2 = Table('t2').select()
+ union = query1 | query2
+ union.order_by = Literal(1)
+ self.assertEqual(str(union),
+ 'SELECT * FROM "t" AS "a" UNION '
+ 'SELECT * FROM "t2" AS "b" '
+ 'ORDER BY %s')
+ self.assertEqual(union.params, (1,))
+
+ def test_select_join(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ join = Join(t1, t2)
+
+ self.assertEqual(str(join.select()),
+ 'SELECT * FROM "t1" AS "a" INNER JOIN "t2" AS "b"')
+ self.assertEqual(str(join.select(getattr(t1, '*'))),
+ 'SELECT "a".* FROM "t1" AS "a" INNER JOIN "t2" AS "b"')
+
+ def test_select_subselect(self):
+ t1 = Table('t1')
+ select = t1.select()
+ self.assertEqual(str(select.select()),
+ 'SELECT * FROM (SELECT * FROM "t1" AS "b") AS "a"')
+ self.assertEqual(select.params, ())
+
+ def test_select_function(self):
+ query = Now().select()
+ self.assertEqual(str(query), 'SELECT * FROM NOW() AS "a"')
+ self.assertEqual(query.params, ())
+
+ def test_select_group_by(self):
+ column = self.table.c
+ query = self.table.select(column, group_by=column)
+ self.assertEqual(str(query),
+ 'SELECT "a"."c" FROM "t" AS "a" GROUP BY "a"."c"')
+ self.assertEqual(query.params, ())
+
+ output = column.as_('c1')
+ query = self.table.select(output, group_by=output)
+ self.assertEqual(str(query),
+ 'SELECT "a"."c" AS "c1" FROM "t" AS "a" GROUP BY "c1"')
+ self.assertEqual(query.params, ())
+
+ query = self.table.select(Literal('foo'), group_by=Literal('foo'))
+ self.assertEqual(str(query),
+ 'SELECT %s FROM "t" AS "a" GROUP BY %s')
+ self.assertEqual(query.params, ('foo', 'foo'))
+
+ def test_select_having(self):
+ col1 = self.table.col1
+ col2 = self.table.col2
+ query = self.table.select(col1, Min(col2),
+ having=(Min(col2) > 3))
+ self.assertEqual(str(query),
+ 'SELECT "a"."col1", MIN("a"."col2") FROM "t" AS "a" '
+ 'HAVING (MIN("a"."col2") > %s)')
+ self.assertEqual(query.params, (3,))
+
+ def test_select_order(self):
+ c = self.table.c
+ query = self.table.select(c, order_by=Literal(1))
+ self.assertEqual(str(query),
+ 'SELECT "a"."c" FROM "t" AS "a" ORDER BY %s')
+ self.assertEqual(query.params, (1,))
+
+ 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())
diff --git a/sql/tests/test_update.py b/sql/tests/test_update.py
new file mode 100644
index 0000000..c0e0219
--- /dev/null
+++ b/sql/tests/test_update.py
@@ -0,0 +1,63 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2013, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Table
+
+
+class TestUpdate(unittest.TestCase):
+ table = Table('t')
+
+ def test_update1(self):
+ query = self.table.update([self.table.c], ['foo'])
+ self.assertEqual(str(query), 'UPDATE "t" SET "c" = %s')
+ self.assertEqual(query.params, ('foo',))
+
+ query.where = (self.table.b == True)
+ self.assertEqual(str(query),
+ 'UPDATE "t" SET "c" = %s WHERE ("t"."b" = %s)')
+ self.assertEqual(query.params, ('foo', True))
+
+ def test_update2(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ query = t1.update([t1.c], ['foo'], from_=[t2], where=(t1.c == t2.c))
+ self.assertEqual(str(query),
+ 'UPDATE "t1" AS "b" SET "c" = %s FROM "t2" AS "a" '
+ 'WHERE ("b"."c" = "a"."c")')
+ self.assertEqual(query.params, ('foo',))
+
+ def test_update_subselect(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ query = t1.update([t1.c], [t2.select(t2.c, where=t2.i == t1.i)])
+ self.assertEqual(str(query),
+ 'UPDATE "t1" SET "c" = ('
+ 'SELECT "b"."c" FROM "t2" AS "b" WHERE ("b"."i" = "t1"."i"))')
+ self.assertEqual(query.params, ())
--
python-sql
More information about the tryton-debian-vcs
mailing list