def response(returndata):
tableId = returndata['table']
query = returndata['query']
columns = DQXDbTools.ParseColumnEncoding(
LZString.decompressFromEncodedURIComponent(returndata['columns']))
database = None
orderBy = None
if 'database' in returndata:
database = returndata['database']
if 'orderBy' in returndata:
orderBy = json.loads(returndata['orderBy'])
auth_query = DQXDbTools.CredentialInformation(returndata).get_auth_query(
database, [tableId])
#Auth is checked when this context is entered
with DQXDbTools.DBCursor(returndata, database,
read_timeout=config.TIMEOUT) as cur:
whc = DQXDbTools.WhereClause()
whc.ParameterPlaceHolder = '%s' # NOTE: MySQL PyODDBC seems to require this nonstardard coding
whc.Decode(query)
if auth_query:
whc.query = {
"whcClass": "compound",
"isCompound": True,
"isRoot": True,
"Components": [whc.query, auth_query],
"Tpe": "AND"
}
whc.CreateSelectStatement()
sqlquery = "SELECT {0} FROM {1}".format(
','.join([DBCOLESC(x['Name']) for x in columns]), DBTBESC(tableId))
if len(whc.querystring_params) > 0:
sqlquery += " WHERE {0}".format(whc.querystring_params)
if orderBy is not None:
sqlquery += " ORDER BY {0}".format(','.join([
DBCOLESC(col) + ' ' + direction for direction, col in orderBy
]))
print(sqlquery)
cur.execute(sqlquery, whc.queryparams)
yield b'\t'.join(col[0].encode('ascii', 'replace')
for col in cur.description) + b'\n'
for row in cur.fetchall():
line = b'\t'.join([str(x).encode('ascii', 'replace')
for x in row]) + b'\n'
yield line
if DQXDbTools.LogRequests:
DQXUtils.LogServer('###QRY:' + sqlquery)
DQXUtils.LogServer('###PARAMS:' + str(whc.queryparams))
def _decode(column):
if isinstance(column, (str, str)):
return DBCOLESC(column)
elif isinstance(column, (int, float)):
return str(column)
elif isinstance(column, (list, tuple)):
return decoders[column[0]](*column[1])
def dropColumns(self, table, columns):
sql = "ALTER TABLE {0} ".format(DBTBESC(table))
for prop in columns:
if prop != columns[0]:
sql += ", "
sql += "DROP COLUMN {0}".format(DBCOLESC(prop))
self._execSql(sql)
def createIndex(self, indexName, tableid, columns, unique=False):
cols = columns.split(",")
modifier = ''
if unique:
modifier = 'UNIQUE'
self._execSql(
'create ' + modifier +
' index {} ON {}({})'.format(DBCOLESC(indexName), DBTBESC(tableid),
",".join(map(DBCOLESC, cols))))
def _loadTable(self, inputFile):
sql = "COPY OFFSET 2 INTO %s from '%s' USING DELIMITERS '%s','%s','%s' NULL AS ''" % (
DBTBESC(self._tableId), inputFile, self._separator,
self._lineSeparator, self._quote)
self._dao._execSql(sql)
if self._loadSettings["primKey"] == "AutoKey":
self._dao._execSql(
"ALTER TABLE %s ADD COLUMN %s int AUTO_INCREMENT PRIMARY KEY" %
(DBTBESC(self._tableId), DBCOLESC(
self._loadSettings["primKey"])))
def _createTable(self):
sql = 'CREATE TABLE {0} (\n'.format(DBTBESC(self._tableId))
colTokens = []
#Get names in header file
with open(self._sourceFileName, 'r', encoding='utf-8') as sourceFile:
header_names = sourceFile.readline().strip().replace(
' ', '_').split(self._separator)
for col in self._loadSettings.getPropertyNames():
if '.' in col:
raise Exception("Column IDs cannot include '.' (%s - %s)" %
(col, self._tableId))
if col not in header_names and col != 'AutoKey':
raise Exception("Can't find column %s in data file for %s" %
(col, self._tableId))
additional_cols = []
for i, col in enumerate(header_names):
if col not in self._loadSettings.getPropertyNames():
additional_cols.append((i, col))
if col == "AutoKey":
continue
if len(additional_cols) > 0:
cols = ','.join(col for (i, col) in additional_cols)
positions = ','.join(str(i + 1) for (i, col) in additional_cols)
raise Exception(
"Data file for %s contains column(s) %s at position(s) %s not seen in settings. You can "
"cut out this column with 'cut -f %s data --complement'" %
(self._tableId, cols, positions, positions))
#Table order has to be file order
for col in header_names:
st = DBCOLESC(self._loadSettings.getPropertyValue(col, 'id'))
st += ' ' + ImpUtils.GetSQLDataType(
self._loadSettings.getPropertyValue(col, 'dataType'))
colTokens.append(st)
sql += ', '.join(colTokens)
sql += ')'
return sql
def handler(start_response, requestData):
try:
length = int(requestData['environ'].get('CONTENT_LENGTH', '0'))
except ValueError:
length = 0
content = requestData['environ']['wsgi.input'].read(length).decode("utf-8")
content = json.loads(content) if len(content) > 0 else None
if not content:
raise SyntaxError('No query parameters supplied')
database = content['database']
# Due to caching we check for auth here, as otherwise auth is only checked on DB read.
credentials = DQXDbTools.CredentialInformation(requestData)
credentials.VerifyCanDo(DQXDbTools.DbOperationRead(database))
tableId = content['table']
query = content['query']
orderBy = json.loads(content.get('orderBy', '[]'))
distinct = content.get('distinct', 'false') == 'true'
rawColumns = json.loads(content['columns'])
columns = list(map(decode, rawColumns))
groupBy = content.get('groupBy', None)
startRow, endRow = None, None
if content.get('limit', False):
startRow, endRow = content['limit'].split('~')
startRow = int(startRow)
endRow = int(endRow)
if startRow < 0:
startRow = 0
if endRow <= startRow:
endRow = startRow + 1
randomSample = None
if content.get('randomSample', False):
randomSample = int(content['randomSample'])
cacheData = content.get('cache', True)
joins = json.loads(content.get('joins', '[]'))
auth_query = credentials.get_auth_query(
database, [join['foreignTable'] for join in joins] + [tableId])
cache = getCache()
cacheKey = json.dumps([
tableId, query, orderBy, distinct, columns, groupBy, database,
startRow, endRow, joins, auth_query
])
data = None
if cacheData and randomSample is None: # Don't serve cache on random sample!!
try:
data = cache[cacheKey]
except KeyError:
pass
if data is None:
with DQXDbTools.DBCursor(requestData,
database,
read_timeout=config.TIMEOUT) as cur:
whereClause = DQXDbTools.WhereClause()
whereClause.ParameterPlaceHolder = '%s'
whereClause.Decode(query, True)
if auth_query:
whereClause.query = {
"whcClass": "compound",
"isCompound": True,
"isRoot": True,
"Components": [whereClause.query, auth_query],
"Tpe": "AND"
}
whereClause.CreateSelectStatement()
sqlQuery = "SELECT "
if distinct:
sqlQuery += " DISTINCT "
sqlQuery += "{0} FROM {1}".format(','.join(columns),
DBTBESC(tableId))
for join in joins:
if 'type' in join and join['type'] in [
'', 'INNER', 'LEFT', 'RIGHT', 'FULL'
]:
sqlQuery += " {0} JOIN {1} ON {2} = {3}".format(
join['type'].upper(), DBTBESC(join['foreignTable']),
DBCOLESC(join['foreignColumn']),
DBCOLESC(join['column']))
else:
raise SyntaxError('Join type not valid')
if len(whereClause.querystring_params) > 0:
sqlQuery += " WHERE {0}".format(whereClause.querystring_params)
if groupBy and len(groupBy) > 0:
sqlQuery += " GROUP BY " + ','.join(
map(DBCOLESC, groupBy.split('~')))
if len(orderBy) > 0:
sqlQuery += " ORDER BY {0}".format(','.join([
DBCOLESC(col) + ' ' + direction
for direction, col in orderBy
]))
if startRow is not None and endRow is not None:
sqlQuery += " LIMIT {0} OFFSET {1}".format(
endRow - startRow + 1, startRow)
if randomSample is not None:
sqlQuery += " SAMPLE {0}".format(randomSample)
if DQXDbTools.LogRequests:
DQXUtils.LogServer('###QRY:' + sqlQuery)
DQXUtils.LogServer('###PARAMS:' + str(whereClause.queryparams))
cur.execute(sqlQuery, whereClause.queryparams)
rows = cur.fetchall()
result = {}
for rawCol, (i, desc) in zip(rawColumns,
enumerate(cur.description)):
# Figure out the name we should return for the column - by deafult monet doesn't qualify names
col_name = name(rawCol, desc[0])
dtype = desciptionToDType(desc[1])
if dtype in ['i1', 'i2', 'i4', 'S']:
null_value = NULL_VALUES[dtype]
result[col_name] = np.array(
[(str(row[i]).encode('utf-8') if dtype == 'S' else
row[i]) if row[i] is not None else null_value
for row in rows],
dtype=dtype)
else:
result[col_name] = np.array([row[i] for row in rows],
dtype=dtype)
data = gzip(data=b''.join(
arraybuffer.encode_array_set(list(result.items()))))
if cacheData:
cache[cacheKey] = data
status = '200 OK'
response_headers = [('Content-type', 'text/plain'),
('Content-Length', str(len(data))),
('Content-Encoding', 'gzip'),
('Access-Control-Allow-Origin', '*')]
start_response(status, response_headers)
yield data
def decode(column):
if isinstance(column, (str, str, int, float, list, tuple)):
return _decode(column)
elif isinstance(column, object):
return _decode(column['expr']) + ' AS ' + DBCOLESC(column['as'])