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)