def build_sql_query(self, query_desc):
"""Modify a raw SQL query with some others rules."""
query = query_desc['sql']
params = []
for name, value in query_desc.iteritems():
#skip system fields
if name.startswith('_'):
#FIXME: put this in a central place!
if name not in ('_limit', '_limit_start',
'_resolve_phonenumber',
'_retrieve_full_contact'):
raise InvalidField("Query rule '%s' does not exist." %
(name, ))
else:
continue
elif name.startswith('@'):
if name[1:] not in DomainManager.get_domains():
raise InvalidField("Domain '%s' does not exist." %
(name[1:], ))
else:
continue
limit_start = 0
if '_limit_start' in query_desc:
try:
limit_start = int(query_desc['_limit_start'])
except:
raise InvalidField("_limit_start should be an integer value")
limit_end = -1
if '_limit' in query_desc:
try:
limit_end = int(query_desc['_limit'])
except:
raise InvalidField("_limit should be an integer value")
if (limit_start != 0 or limit_end != -1):
query = "SELECT * FROM (" + query + ") LIMIT ?,?"
params.extend([limit_start, limit_end])
return {'Query': query, 'Parameters': params}
def build_search_query(self, query_desc):
"""Recieves a dictionary and makes an sql query that returns all the
id's of those who meet the dictionaries restrictions"""
params = []
not_first = False
if '_at_least_one' in query_desc:
table_join_operator = " UNION "
else:
table_join_operator = " INTERSECT "
query = ""
for name, value in query_desc.iteritems():
#skip system fields
if name.startswith('_'):
#FIXME: put this in a central place!
if name not in ('_at_least_one', '_sortdesc', '_sortby',
'_limit', '_limit_start',
'_resolve_phonenumber',
'_retrieve_full_contact'):
raise InvalidField("Query rule '%s' does not exist." %
(name, ))
else:
continue
elif name.startswith('@'):
if name[1:] not in DomainManager.get_domains():
raise InvalidField("Domain '%s' does not exist." %
(name[1:], ))
else:
continue
if not_first:
query = query + table_join_operator
not_first = True
#handle type searching
if name.startswith('<') or name.startswith('>'):
pos = 1
if (name[1] == '='):
pos = 2
operator = name[:pos]
name = name[pos:]
elif name.startswith('!'):
operator = '!='
name = name[1:]
else:
operator = '='
if name.startswith('$'):
field_type = name[1:]
table = self.get_table_name_from_type(field_type)
if not table:
raise InvalidField("Type '%s' does not exist." %
(field_type, ))
query = query + "SELECT DISTINCT " + self.db_prefix + "_id FROM " + \
table + " WHERE ("
else:
field_type = self.domain.field_type_from_name(name)
table = self.get_table_name(name)
if not table:
raise InvalidField(
"Field '%s' is reserved for internal use." % (name, ))
query = query + "SELECT DISTINCT " + self.db_prefix + "_id FROM " + \
table + " WHERE field_name = ? AND ("
params.append(str(name))
#If multi values, make OR connections
comp_string = self.get_value_compare_string(
field_type, name, operator)
if type(value) == Array or type(value) == list:
first_val = True
for val in value:
if first_val:
first_val = False
else:
query = query + " OR "
query = query + comp_string
params.append(
self.get_value_compare_object(field_type, name, val))
else:
query = query + comp_string
params.append(
self.get_value_compare_object(field_type, name, value))
query = query + ")"
#If there are no restrictions get everything
if query == "":
query = "SELECT " + self.db_prefix + "_id FROM " + self.db_prefix
if '_sortby' in query_desc:
sortby = query_desc['_sortby']
query = "SELECT DISTINCT " + self.db_prefix + "_id FROM (" + query + \
") JOIN " + self.get_table_name(sortby) + " USING (" + \
self.db_prefix + "_id) WHERE field_name = ? ORDER BY value"
params.append(sortby)
if '_sortdesc' in query_desc:
query = query + " DESC"
limit_start = 0
if '_limit_start' in query_desc:
try:
limit_start = int(query_desc['_limit_start'])
except:
raise InvalidField("_limit_start should be an integer value")
limit_end = -1
if '_limit' in query_desc:
try:
limit_end = int(query_desc['_limit'])
except:
raise InvalidField("_limit should be an integer value")
if (limit_start != 0 or limit_end != -1):
query = query + " LIMIT ?,?"
params.extend([limit_start, limit_end])
return {'Query': query, 'Parameters': params}
