Reviewers: ,
Please review this at http://codereview.tryton.org/784002/
Affected files:
M trytond/backend/sqlite/database.py
M trytond/ir/translation.py
M trytond/model/fields/property.py
M trytond/model/modelsql.py
Index: trytond/backend/sqlite/database.py
===================================================================
--- a/trytond/backend/sqlite/database.py
+++ b/trytond/backend/sqlite/database.py
@@ -23,6 +23,7 @@
from sqlite3 import OperationalError as DatabaseOperationalError
QUOTE_SEPARATION = re.compile(r"(.*?)('.*?')", re.DOTALL)
EXTRACT_PATTERN = re.compile(r'EXTRACT\s*\(\s*(\S*)\s+FROM', re.I)
+POSITION_PATTERN = re.compile(r'POSITION\s*\(([^\)]*)\s+IN', re.I)
def extract(lookup_type, date):
@@ -87,6 +88,15 @@
return (text.split(delimiter) + [''] * (count - 1))[count - 1]
+def position(substring, string):
+ if string is None:
+ return
+ try:
+ return string.index(substring) + 1
+ except ValueError:
+ return 0
+
+
def replace(text, pattern, replacement):
return str(text).replace(pattern, replacement)
@@ -122,6 +132,7 @@
self._conn.create_function('extract', 2, extract)
self._conn.create_function('date_trunc', 2, date_trunc)
self._conn.create_function('split_part', 3, split_part)
+ self._conn.create_function('position', 2, position)
if sqlite.sqlite_version_info < (3, 3, 14):
self._conn.create_function('replace', 3, replace)
return self
@@ -278,6 +289,7 @@
def execute(self, sql, params=None):
buf = ""
+ sql = re.sub(POSITION_PATTERN, r'POSITION(\1,', sql)
for nquote, quote in QUOTE_SEPARATION.findall(sql + "''"):
nquote = nquote.replace('?', '??')
nquote = nquote.replace('%s', '?')
Index: trytond/ir/translation.py
===================================================================
--- a/trytond/ir/translation.py
+++ b/trytond/ir/translation.py
@@ -144,7 +144,7 @@
def search_model(cls, name, clause):
cursor = Transaction().cursor
cursor.execute('SELECT id FROM "%s" '
- 'WHERE split_part(name, \',\', 1) %s %%s' %
+ 'WHERE SUBSTR(name, 1, POSITION(\',\' IN name) - 1) %s %%s' %
(cls._table, clause[1]), (clause[2],))
return [('id', 'in', [x[0] for x in cursor.fetchall()])]
Index: trytond/model/fields/property.py
===================================================================
--- a/trytond/model/fields/property.py
+++ b/trytond/model/fields/property.py
@@ -90,7 +90,8 @@
#Fetch res ids that comply with the domain
cursor.execute(
'SELECT CAST('
- 'SPLIT_PART("' + Property._table + '".res,\',\',2) '
+ 'SUBSTR("' + Property._table + '".res, '
+ 'POSITION(\',\' IN "' + Property._table + '".res) +
1) '
'AS INTEGER), '
'"' + Property._table + '".id '
'FROM "' + Property._table + '" '
@@ -135,7 +136,7 @@
#Fetch the res ids that doesn't use the default value
cursor.execute(
- "SELECT cast(split_part(res,',',2) as integer) "
+ "SELECT CAST(SUBSTR(res, POSITION(',' IN res) + 1) AS
integer) "
'FROM "' + Property._table + '"'
'WHERE ' + property_query + ' AND res is not null',
property_val)
@@ -160,23 +161,22 @@
if sql_type == 'NUMERIC':
operator = 'CAST(%s AS NUMERIC)'
+ value = "SUBSTR(value, POSITION(',' IN value) + 1)"
# All negative clauses will be negated later
if clause[1] in ('in', 'not in'):
operator = operator % '%%s'
- return ("(CAST(SPLIT_PART(value,',',2) AS %s) IN ("
+ return ("(CAST(" + value + " AS %s) IN ("
+ ",".join((operator,) * len(clause[2])) + ")) ") %
sql_type
elif ((clause[2] is False or clause[2] is None)
and clause[1] in ['=', '!=']):
- return "((cast(split_part(value,',',2) as %s) IS NULL " \
- ") = %%s) " % sql_type
+ return "((CAST(" + value + " AS %s) IS NULL) = %%s) " %
sql_type
elif clause[1] in ['not like', 'not ilike']:
- return "(cast(split_part(value,',',2) as %s) %s %s) " % \
+ return "(CAST(" + value + " AS %s) %s %s) " % \
(sql_type, clause[1].split()[1], operator)
elif clause[1] == '!=':
- return "(cast(split_part(value,',',2) as %s) = %s) " % \
- (sql_type, operator)
+ return "(CAST(" + value + " AS %s) = %s) " % (sql_type,
operator)
else:
- return "(cast(split_part(value,',',2) as %s) %s %s) " % \
+ return "(CAST(" + value + " AS %s) %s %s) " % \
(sql_type, clause[1], operator)
@staticmethod
Index: trytond/model/modelsql.py
===================================================================
--- a/trytond/model/modelsql.py
+++ b/trytond/model/modelsql.py
@@ -1269,8 +1269,9 @@
sql_type = FIELDS[
cls.id._type].sql_type(cls.id)[0]
query = ('SELECT id FROM "' + cls._table + '" '
- 'WHERE CAST(SPLIT_PART('
- '"' + fargs[0] + '", \',\', 2) '
+ 'WHERE CAST(SUBSTR("' + fargs[0] + '", '
+ 'POSITION(\',\' IN "' + fargs[0]
+ '")'
+ + '+ 1) '
'AS ' + sql_type + ') '
'IN (' + in_query[0] + ') '
'AND "' + fargs[0] + '" ilike %s')
@@ -1295,8 +1296,9 @@
cls.id._type].sql_type(cls.id)[0]
where = ' AND "' + origin + '" LIKE %s'
where_args = [cls.__name__ + ',%']
- origin = ('CAST(SPLIT_PART("%s", \',\', 2) AS %s)'
- % (origin, sql_type))
+ origin = ('CAST(SUBSTR("%s", '
+ 'POSITION(\',\' IN "%s") + 1) AS %s)'
+ % (origin, origin, sql_type))
else:
origin = '"%s"' % origin
where = ''
@@ -1336,8 +1338,9 @@
rev_field = Field._fields[field.field]
if rev_field._type == 'reference':
sql_type = FIELDS[cls.id._type].sql_type(cls.id)[0]
- select = ('CAST(SPLIT_PART("%s", \',\', 2) AS %s)'
- % (field.field, sql_type))
+ select = ('CAST(SUBSTR("%s", '
+ 'POSITION(\',\' IN "%s") + 1) AS %s)'
+ % (field.field, field.field, sql_type))
where = ' AND "' + field.field + '" LIKE %s'
where_args = [cls.__name__ + ',%']
else:
@@ -1431,8 +1434,9 @@
origin_field = Relation._fields[field.origin]
if origin_field._type == 'reference':
sql_type = FIELDS[cls.id._type].sql_type(cls.id)[0]
- select = ('CAST(SPLIT_PART("%s", \',\', 2) AS %s)'
- % (field.origin, sql_type))
+ select = ('CAST(SUBSTR("%s", '
+ 'POSITION(\',\' IN "%s") + 1) AS %s)'
+ % (field.origin, field.origin, sql_type))
where = ' AND "' + field.origin + '" LIKE %s'
where_args = [cls.__name__ + ',%']
else:
