def queryTable(self, tableName, querySpec, limit):
"""
Queries the SQL database and returns the result. Overrides SqlConn.queryTable
due to differences in how PostgreSQL handles schemas.
Raises:
ValueError: Thrown when the querySpec is not a dictionary.
"""
if not isinstance(querySpec, dict):
raise ValueError( "sql.connSql.PostgreSQL.queryTable"
, "Invalid parameter type: {0}".format(type(querySpec)))
# IMPORTANT: This prevents SQL injections by validating table name
columns = [("{0}.{1}".format(tableName, col[0]), col[1])
for col in self._getColumnsInTable(tableName)]
if not columns:
raise ValueError( "sql.connSql.PostgreSQL.queryTable"
, "Unknown table name: {0}".format(tableName))
schema = self._getTableSchema(tableName)
if schema != [] and schema[0][0] != 'public':
tableName = schema[0][0] + '.' + tableName
if 'meta' not in querySpec or querySpec['meta'] == {}:
meta = {colName: {'type': getType(colType)} for colName, colType in columns}
querySpec['meta'] = meta
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 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 dsCallback(request):
"""View Handler for OAuth2.0 callback."""
if 'code' not in request.GET or 'state' not in request.GET:
raise ValueError("Invalid OAuth Response")
code = request.GET['code']
state = request.GET['state']
reqArgs = state.split('-')
reqType = reqArgs[0]
reqName = '-'.join(reqArgs[1:])
response = oauthCallback(code, reqType, session = request.session)
if reqType == 'ga':
split = reqName.rfind("||")
response['name'] = reqName[0:split]
response['gaId'] = reqName[split+2:]
response['type'] = 'googleAnalytics'
return render( request
, 'callback.tmpl'
, dictionary = { 'response': saneEncode(response)}
)
def tableMeta(request, dsKey):
"""View Handler for returning table meta data."""
tableName = request.POST.get('tableName', None)
columnExpr = json.loads(urllib.unquote(request.POST.get('columnExpr', None)))
dataType = request.POST.get('type', None)
if None in [tableName, columnExpr, dataType]:
raise ValueError("views.dataSource.tableMeta",
"Invalid request; missing data.")
try:
connection = getConnection(request, dsKey)
result = runConnectionMethod(
connection,
'getColumnMetadata',
tableName,
columnExpr,
dataType
)
except ValueError as err:
if len(err.args) > 0:
return jsonResponse({'message': str(err.args[0])}, status=500)
return jsonResponse({'message': None}, status=500)
return jsonResponse(saneEncode(result))
def tableList(request, dsKey): """View Handler for listing tables in a dashboard.""" connection = getConnection(request, dsKey) tables = runConnectionMethod(connection, 'listTables') return jsonResponse(saneEncode(tables))
