Example #1
0
  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)
Example #2
0
    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)
Example #3
0
  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)
Example #4
0
  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