1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2010 OpenERP S.A. http://www.openerp.com
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as
9 # published by the Free Software Foundation, either version 3 of the
10 # License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
20 ##############################################################################
22 #.apidoc title: Query object
26 if '"' not in to_quote:
27 return '"%s"' % to_quote
33 Dumb implementation of a Query object, using 3 string lists so far
34 for backwards compatibility with the (table, where_clause, where_params) previously used.
36 TODO: To be improved after v6.0 to rewrite part of the ORM and add support for:
37 - auto-generated multiple table aliases
38 - multiple joins to the same table with different conditions
39 - dynamic right-hand-side values in domains (e.g. a.name = a.description)
43 def __init__(self, tables=None, where_clause=None, where_clause_params=None, joins=None):
45 # holds the list of tables joined using default JOIN.
46 # the table names are stored double-quoted (backwards compatibility)
47 self.tables = tables or []
49 # holds the list of WHERE clause elements, to be joined with
50 # 'AND' when generating the final query
51 self.where_clause = where_clause or []
53 # holds the parameters for the formatting of `where_clause`, to be
54 # passed to psycopg's execute method.
55 self.where_clause_params = where_clause_params or []
57 # holds table joins done explicitly, supporting outer joins. The JOIN
58 # condition should not be in `where_clause`. The dict is used as follows:
61 # ('table_b', 'table_a_col1', 'table_b_col', 'LEFT JOIN'),
62 # ('table_c', 'table_a_col2', 'table_c_col', 'LEFT JOIN'),
63 # ('table_d', 'table_a_col3', 'table_d_col', 'JOIN'),
66 # which should lead to the following SQL:
67 # SELECT ... FROM "table_a" LEFT JOIN "table_b" ON ("table_a"."table_a_col1" = "table_b"."table_b_col")
68 # LEFT JOIN "table_c" ON ("table_a"."table_a_col2" = "table_c"."table_c_col")
69 self.joins = joins or {}
71 def _get_table_aliases(self):
72 from openerp.osv.expression import get_alias_from_query
73 return [get_alias_from_query(from_statement)[1] for from_statement in self.tables]
75 def _get_alias_mapping(self):
76 from openerp.osv.expression import get_alias_from_query
78 for table in self.tables:
79 alias, statement = get_alias_from_query(table)
80 mapping[statement] = table
83 def add_join(self, connection, implicit=True, outer=False):
84 """ Join a destination table to the current table.
86 :param implicit: False if the join is an explicit join. This allows
87 to fall back on the previous implementation of ``join`` before
88 OpenERP 7.0. It therefore adds the JOIN specified in ``connection``
89 If True, the join is done implicitely, by adding the table alias
90 in the from clause and the join condition in the where clause
92 :param connection: a tuple ``(lhs, table, lhs_col, col, link)``.
93 The join corresponds to the SQL equivalent of::
95 (lhs.lhs_col = table.col)
97 Note that all connection elements are strings. Please refer to expression.py for more details about joins.
99 :param outer: True if a LEFT OUTER JOIN should be used, if possible
100 (no promotion to OUTER JOIN is supported in case the JOIN
101 was already present in the query, as for the moment
102 implicit INNER JOINs are only connected from NON-NULL
103 columns so it would not be correct (e.g. for
104 ``_inherits`` or when a domain criterion explicitly
107 from openerp.osv.expression import generate_table_alias
108 (lhs, table, lhs_col, col, link) = connection
109 alias, alias_statement = generate_table_alias(lhs, [(table, link)])
112 if alias_statement not in self.tables:
113 self.tables.append(alias_statement)
114 condition = '("%s"."%s" = "%s"."%s")' % (lhs, lhs_col, alias, col)
115 # print '\t\t... Query: added %s in %s (received %s)' % (alias_statement, self.tables, connection)
116 self.where_clause.append(condition)
118 # print '\t\t... Query: not added %s in %s (received %s)' % (alias_statement, self.tables, connection)
121 return alias, alias_statement
123 aliases = self._get_table_aliases()
124 assert lhs in aliases, "Left-hand-side table %s must already be part of the query tables %s!" % (lhs, str(self.tables))
125 if alias_statement in self.tables:
126 # already joined, must ignore (promotion to outer and multiple joins not supported yet)
127 # print '\t\t... Query: not added %s in %s (received %s)' % (alias_statement, self.tables, connection)
131 self.tables.append(alias_statement)
132 self.joins.setdefault(lhs, []).append((alias, lhs_col, col, outer and 'LEFT JOIN' or 'JOIN'))
133 return alias, alias_statement
136 """ Returns (query_from, query_where, query_params). """
137 from openerp.osv.expression import get_alias_from_query
139 tables_to_process = list(self.tables)
140 alias_mapping = self._get_alias_mapping()
142 def add_joins_for_table(table, query_from):
143 for (dest_table, lhs_col, col, join) in self.joins.get(table, []):
144 tables_to_process.remove(alias_mapping[dest_table])
145 query_from += ' %s %s ON ("%s"."%s" = "%s"."%s")' % \
146 (join, alias_mapping[dest_table], table, lhs_col, dest_table, col)
147 query_from = add_joins_for_table(dest_table, query_from)
150 for table in tables_to_process:
152 table_alias = get_alias_from_query(table)[1]
153 if table_alias in self.joins:
154 query_from = add_joins_for_table(table_alias, query_from)
156 query_from = query_from[:-1] # drop last comma
157 return (query_from, " AND ".join(self.where_clause), self.where_clause_params)
160 return '<osv.Query: "SELECT ... FROM %s WHERE %s" with params: %r>' % self.get_sql()
162 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: