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 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
