def getColumnMetadata(self, tableName, columnExpr, dataType): """ Returns the meta data for a given column. See DataSourceConnection.getColumnMetadata for more information. The implementation here runs by constructing a dummy Google Analytics query, from which meta data is computed. Raises: ValueError: Thrown when the connection is not available. """ # TODO: disallow formulas columnName = getColumnName(columnExpr['name'], columnExpr['expr']) nakedName = columnName[len(tableName)+1:] ready = self._checkConnection() if ready: if nakedName == 'time': return { 'min': mktime(strptime(self.startDate, '%Y-%m-%d')) , 'max': mktime(strptime(self.endDate, '%Y-%m-%d')) } else: select = [columnExpr] meta = {columnName: {'type': dataType, 'ga': 'metric'}} meta['tableName'] = tableName #match = re.match(r'ga-(?:dimension|metric)-(.*)', tableName) #if match is not None: # tableName = match.group(1) #else: # raise ValueError( "dataSources.googleAnalytics.connection.getColumnMetadata: "\ # + "Invalid table name, %s" % tableName) t = listDictWithPair(GAParams.getTables(), 'name', tableName) gaType = t['meta'][nakedName]['ga'] if gaType == 'dimension': meta[columnName]['ga'] = 'dimension' select.append( {'name': '[Visitor.visitors]', 'expr': ['ident', {'name': 'Visitor.visitors'}]} ) meta.update({ 'Visitor.visitors': { 'type': 'num' , 'ga': 'metric'}}) query = GAQuery( tableName , { 'select' : select , 'meta' : meta , 'stats' : {'stats': [], 'groups': []} , 'trans' : [] , 'filter' : {} } , queryFunc = self._queryFunc , startDate = self.startDate , endDate = self.endDate , gaId = self.gaId ) result = query.getData() vals = [datum[columnName] for datum in result['data']] if dataType == 'cat': return { 'values': vals } else: return { 'min': min(vals) , 'max': max(vals)} else: raise ValueError( "googleAnalytics.connection.getColumnMetadata" , "Unable to run because not ready.")
def queryTable(self, tableName, querySpec, limit): """ Queries the SQL database and returns the result. See DataSourceConnection.queryTable for more information. Raises: ValueError: Thrown when the querySpec is not a dictionary. """ if not isinstance(querySpec, dict): raise ValueError( "sql.connSql.SqlConn.queryTable", "Invalid parameter type: {0}".format(type(querySpec))) # IMPORTANT: This prevents SQL injections by validating table name tableNames = [] if '_additionalInfo' in querySpec['meta']: tableNames = querySpec['meta']['_additionalInfo']['joins'][ 'tables'] if len(tableNames) == 0 and tableName: tableNames = [tableName] columns = [] for table in tableNames: for col in self._getColumnsInTable(table): columns += [("{0}.{1}".format(table, col[0]), col[1])] if not columns: raise ValueError( "sql.connSql.SqlConn.queryTable", "Unknown table name: {table}".format(table=tableName)) for colName, colType in columns: name = colName trans = listDictWithPair(querySpec['trans'], 'key', colName) transKey = None if trans is not None: transKey = trans['key'] name = trans['name'] if colName in querySpec['select'] or\ colName in querySpec['filter'] or\ colName == transKey: if name not in querySpec['meta']: querySpec['meta'][name] = {} querySpec['meta'][name]['type'] = getType(colType) # Perform table query using translator query = self.queryType(tableName, querySpec, limit, self._query, columns) result = query.getData() return saneEncode(result)
def queryTable(self, tableName, querySpec, limit): """ Queries the SQL database and returns the result. See DataSourceConnection.queryTable for more information. Raises: ValueError: Thrown when the querySpec is not a dictionary. """ if not isinstance(querySpec, dict): raise ValueError( "sql.connSql.SqlConn.queryTable" , "Invalid parameter type: {0}".format(type(querySpec))) # IMPORTANT: This prevents SQL injections by validating table name tableNames = [] if '_additionalInfo' in querySpec['meta']: tableNames = querySpec['meta']['_additionalInfo']['joins']['tables'] if len(tableNames) == 0 and tableName: tableNames = [tableName] columns = [] for table in tableNames: for col in self._getColumnsInTable(table): columns += [("{0}.{1}".format(table, col[0]), col[1])] if not columns: raise ValueError( "sql.connSql.SqlConn.queryTable" , "Unknown table name: {table}".format(table=tableName)) for colName, colType in columns: name = colName trans = listDictWithPair(querySpec['trans'], 'key', colName) transKey = None if trans is not None: transKey = trans['key'] name = trans['name'] if colName in querySpec['select'] or\ colName in querySpec['filter'] or\ colName == transKey: if name not in querySpec['meta']: querySpec['meta'][name] = {} querySpec['meta'][name]['type'] = getType(colType) # Perform table query using translator query = self.queryType(tableName, querySpec, limit, self._query, columns) result = query.getData() return saneEncode(result)
def listTables(self): """ Lists tables for general SQL data sources. See DataSourceConnection.listTables for more information. """ cur_result = self._getAllColumns() result = [] for tableName, columnName, dataType in cur_result: table = listDictWithPair(result, 'name', tableName) if table is None: table = { 'name': tableName, 'meta': {} } result.append(table) polyType = getType(dataType) table['meta'][columnName] = { 'type': polyType } if polyType == 'date' and dataType in ['day', 'month', 'year']: table['meta'][columnName]['timerange'] = dataType return result
def getColumnMetadata(self, tableName, columnExpr, dataType): """ Returns the meta data for a given column. See DataSourceConnection.getColumnMetadata for more information. The implementation here runs by constructing a dummy Google Analytics query, from which meta data is computed. Raises: ValueError: Thrown when the connection is not available. """ # TODO: disallow formulas columnName = getColumnName(columnExpr['name'], columnExpr['expr']) nakedName = columnName[len(tableName) + 1:] ready = self._checkConnection() if ready: if nakedName == 'time': return { 'min': mktime(strptime(self.startDate, '%Y-%m-%d')), 'max': mktime(strptime(self.endDate, '%Y-%m-%d')) } else: select = [columnExpr] meta = {columnName: {'type': dataType, 'ga': 'metric'}} meta['tableName'] = tableName #match = re.match(r'ga-(?:dimension|metric)-(.*)', tableName) #if match is not None: # tableName = match.group(1) #else: # raise ValueError( "dataSources.googleAnalytics.connection.getColumnMetadata: "\ # + "Invalid table name, %s" % tableName) t = listDictWithPair(GAParams.getTables(), 'name', tableName) gaType = t['meta'][nakedName]['ga'] if gaType == 'dimension': meta[columnName]['ga'] = 'dimension' select.append({ 'name': '[Visitor.visitors]', 'expr': ['ident', { 'name': 'Visitor.visitors' }] }) meta.update( {'Visitor.visitors': { 'type': 'num', 'ga': 'metric' }}) query = GAQuery(tableName, { 'select': select, 'meta': meta, 'stats': { 'stats': [], 'groups': [] }, 'trans': [], 'filter': {} }, queryFunc=self._queryFunc, startDate=self.startDate, endDate=self.endDate, gaId=self.gaId) result = query.getData() vals = [datum[columnName] for datum in result['data']] if dataType == 'cat': return {'values': vals} else: return {'min': min(vals), 'max': max(vals)} else: raise ValueError("googleAnalytics.connection.getColumnMetadata", "Unable to run because not ready.")