def getColumnMetadata(self, tableName, columnExpr, dataType):
"""
Returns the meta data for a given column. See DataSourceConnection.getColumnMetadata
for more information.
Raises:
ValueError: Thrown when columnName is not found in table.
"""
columns = []
for col in self._getColumnsInTable(tableName):
columns += ["{0}.{1}".format(tableName, col[0])]
validate = getExprValidator(columns)
validate(columnExpr)
expr = columnExpr['expr']
result = {}
if dataType == 'cat':
columns = self._query(
'SELECT distinct({col}) FROM {table} LIMIT 100'.format(
col = self._translate(expr)
, table = tableName)
)
def sanitize(v):
if v is None:
return "Null"
return v
result = { "values" : [sanitize(v[0]) for v in columns if len(v) > 0] }
else:
min = exprCallFnc('min', [expr])
max = exprCallFnc('max', [expr])
if dataType == 'date':
q = "SELECT {colMin},{colMax} FROM {table}".format(
colMin = self._translate(exprCallFnc('unix', [min]))
, colMax = self._translate(exprCallFnc('unix', [max]))
, table = tableName)
elif dataType == 'num':
q = "SELECT {colMin},{colMax} FROM {table}".format(
colMin = self._translate(min)
, colMax = self._translate(max)
, table = tableName)
columns = self._query(q)
if len(columns) > 0:
minValue, maxValue = columns[0]
else:
minValue, maxValue = (0, 0)
result = { "min" : minValue
, "max" : maxValue }
return result
def _combinePieces(self, queryFields, joins, groups, filters):
select = ",".join(queryFields)
query = 'SELECT {col} FROM {table} '.format(col=select,
table=self.tableName)
params = []
# Join all tables and apply predicates later. This algorithm only works
# for inner joins - more work will need to be done here in order to support
# left/right/outer joins.
joinedTables = [self.tableName]
for (table1, table2), _ in joins:
if table1 not in joinedTables:
query += "INNER JOIN {0} ".format(table1)
joinedTables.append(table1)
if table2 not in joinedTables:
query += "INNER JOIN {0} ".format(table2)
joinedTables.append(table2)
isFirst = True
for _, predicates in joins:
for pred in predicates:
if isFirst:
query += "ON {0} ".format(pred)
isFirst = False
else:
query += "AND {0} ".format(pred)
idx = 0
for obj in filters:
# To keep unix time stamp consistent
metaKey = obj['name']
if not metaKey in self.jsSpec['meta']:
raise ValueError(
"dataSources.sql.query._combinePieces",
"No meta info for {field}.".format(field=metaKey))
if self.jsSpec['meta'][metaKey]['type'] == 'date':
# redo the translation?
expr = obj['expr']['expr']
obj['translated'] = self._translate(exprCallFnc(
'unix', [expr]))
field = obj['translated']
querytmp = ''
if 'in' in obj:
val = obj['in']
querytmp += field + ' IN (' + ','.join('%s'
for i in val) + ') '
params += val
for op in ['ge', 'le', 'lt', 'gt', 'eq']:
if op in obj:
val = obj[op]
_infixop = {
'ge': '>=',
'gt': '>',
'le': '<=',
'lt': '<',
'eq': '=='
}
if querytmp != '':
querytmp += ' AND '
querytmp += field + _infixop[op] + ' %s '
params.append(val)
if 'notnull' in obj:
if obj['notnull']:
if querytmp != '':
querytmp = '( ' + querytmp + ' ) AND ' + field + ' IS NOT NULL'
else:
querytmp = field + ' IS NOT NULL'
else:
if querytmp != '':
querytmp = '( ' + querytmp + ' ) OR ' + field + ' IS NULL'
if idx == 0:
query += 'WHERE %s ' % querytmp
else:
query += 'AND %s ' % querytmp
idx += 1
if len(groups) > 0:
query += 'GROUP BY {groups} '.format(groups=','.join(groups))
if len(self.sort) > 0:
fields = []
for key, asc in self.sort.iteritems():
if asc:
dir = " ASC "
else:
dir = " DESC "
fields.append(key + dir)
query += 'ORDER BY {orders} '.format(orders=','.join(fields))
if self.limit: query += 'LIMIT {lim}'.format(lim=str(self.limit))
return (query, params)
def _combinePieces(self, queryFields, joins, groups, filters):
select = ",".join(queryFields)
query = 'SELECT {col} FROM {table} '.format(col = select, table = self.tableName)
params = []
# Join all tables and apply predicates later. This algorithm only works
# for inner joins - more work will need to be done here in order to support
# left/right/outer joins.
joinedTables = [self.tableName]
for (table1, table2), _ in joins:
if table1 not in joinedTables:
query += "INNER JOIN {0} ".format(table1)
joinedTables.append(table1)
if table2 not in joinedTables:
query += "INNER JOIN {0} ".format(table2)
joinedTables.append(table2)
isFirst = True
for _, predicates in joins:
for pred in predicates:
if isFirst:
query += "ON {0} ".format(pred)
isFirst = False
else:
query += "AND {0} ".format(pred)
idx = 0
for obj in filters:
# To keep unix time stamp consistent
metaKey = obj['name']
if not metaKey in self.jsSpec['meta']:
raise ValueError( "dataSources.sql.query._combinePieces"
, "No meta info for {field}.".format(field=metaKey))
if self.jsSpec['meta'][metaKey]['type'] == 'date':
# redo the translation?
expr = obj['expr']['expr']
obj['translated'] = self._translate(exprCallFnc('unix', [expr]))
field = obj['translated']
querytmp = ''
if 'in' in obj:
val = obj['in']
querytmp += field + ' IN (' + ','.join('%s' for i in val) + ') '
params += val
for op in ['ge', 'le', 'lt', 'gt', 'eq']:
if op in obj:
val = obj[op]
_infixop = {
'ge' : '>=',
'gt' : '>',
'le' : '<=',
'lt' : '<',
'eq' : '=='
}
if querytmp != '':
querytmp += ' AND '
querytmp += field + _infixop[op] + ' %s '
params.append(val)
if 'notnull' in obj:
if obj['notnull']:
if querytmp != '':
querytmp = '( ' + querytmp + ' ) AND ' + field + ' IS NOT NULL'
else:
querytmp = field + ' IS NOT NULL'
else:
if querytmp != '':
querytmp = '( ' + querytmp + ' ) OR ' + field + ' IS NULL'
if idx == 0:
query += 'WHERE %s ' % querytmp
else:
query += 'AND %s ' % querytmp
idx += 1
if len(groups) > 0:
query += 'GROUP BY {groups} '.format(groups = ','.join(groups))
if len(self.sort) > 0:
fields = []
for key, asc in self.sort.iteritems():
if asc:
dir = " ASC "
else:
dir = " DESC "
fields.append(key + dir)
query += 'ORDER BY {orders} '.format(orders = ','.join(fields))
if self.limit: query += 'LIMIT {lim}'.format(lim = str(self.limit))
return (query, params)
