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.")
