private DataTableSchema getSchemaFromDirectQuery()

in baremaps-calcite/src/main/java/org/apache/baremaps/calcite/postgres/PostgresModifiableTable.java [193:303]
83 lines of code
10 McCabe index (conditional complexity)

  private DataTableSchema getSchemaFromDirectQuery() throws SQLException {
    List<DataColumn> columns = new ArrayList<>();

    try (Connection connection = dataSource.getConnection()) {
      // First try with pg_catalog, which works for both tables and materialized views
      String sql =
          "SELECT a.attname AS column_name, " +
              "       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type, " +
              "       NOT a.attnotnull AS is_nullable " +
              "FROM pg_catalog.pg_attribute a " +
              "JOIN pg_catalog.pg_class c ON a.attrelid = c.oid " +
              "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " +
              "WHERE n.nspname = ? " +
              "  AND c.relname = ? " +
              "  AND a.attnum > 0 " +
              "  AND NOT a.attisdropped " +
              "ORDER BY a.attnum";

      try (PreparedStatement stmt = connection.prepareStatement(sql)) {
        stmt.setString(1, schema);
        stmt.setString(2, tableName);

        try (ResultSet rs = stmt.executeQuery()) {
          while (rs.next()) {
            String columnName = rs.getString("column_name");
            String dataType = rs.getString("data_type");
            boolean isNullable = rs.getBoolean("is_nullable");

            // Determine column cardinality
            DataColumn.Cardinality cardinality =
                isNullable ? DataColumn.Cardinality.OPTIONAL : DataColumn.Cardinality.REQUIRED;

            // Create a data column based on the type
            RelDataTypeFactory typeFactory = new org.apache.calcite.jdbc.JavaTypeFactoryImpl();
            RelDataType relDataType;

            // Check if it's a geometry column by looking at the data type
            if (dataType.contains("geometry")) {
              relDataType = typeFactory.createSqlType(SqlTypeName.GEOMETRY);
            } else {
              // Map PostgreSQL type to Calcite type
              relDataType = PostgresTypeConversion.postgresTypeToRelDataType(
                  typeFactory, mapPostgresTypeName(dataType));
            }

            columns.add(new DataColumnFixed(columnName, cardinality, relDataType));
          }
        }
      }

      // If we didn't find any columns, try falling back to information_schema.columns
      if (columns.isEmpty()) {
        try (PreparedStatement stmt = connection.prepareStatement(
            "SELECT column_name, data_type, is_nullable " +
                "FROM information_schema.columns " +
                "WHERE table_name = ? " +
                "ORDER BY ordinal_position")) {

          stmt.setString(1, tableName);

          try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
              String columnName = rs.getString("column_name");
              String dataType = rs.getString("data_type");
              boolean isNullable = "YES".equalsIgnoreCase(rs.getString("is_nullable"));

              // Determine column cardinality
              DataColumn.Cardinality cardinality =
                  isNullable ? DataColumn.Cardinality.OPTIONAL : DataColumn.Cardinality.REQUIRED;

              // Create a data column based on the type
              RelDataTypeFactory typeFactory = new org.apache.calcite.jdbc.JavaTypeFactoryImpl();
              RelDataType relDataType;

              // Check if it's a geometry column
              if ("USER-DEFINED".equals(dataType)) {
                // Check if this is a geometry column by querying for spatial_ref_sys
                try (PreparedStatement geometryCheck = connection.prepareStatement(
                    "SELECT type FROM geometry_columns " +
                        "WHERE f_table_name = ? AND f_geometry_column = ?")) {
                  geometryCheck.setString(1, tableName);
                  geometryCheck.setString(2, columnName);
                  try (ResultSet geomRs = geometryCheck.executeQuery()) {
                    if (geomRs.next()) {
                      relDataType = typeFactory.createSqlType(SqlTypeName.GEOMETRY);
                    } else {
                      // Not a geometry, handle as regular type
                      relDataType = PostgresTypeConversion.postgresTypeToRelDataType(
                          typeFactory, dataType);
                    }
                  }
                }
              } else {
                // Regular PostgreSQL type
                relDataType = PostgresTypeConversion.postgresTypeToRelDataType(
                    typeFactory, dataType);
              }

              columns.add(new DataColumnFixed(columnName, cardinality, relDataType));
            }
          }
        }
      }
    }

    if (columns.isEmpty()) {
      throw new SQLException("No columns found for table: " + schema + "." + tableName);
    }

    return new DataTableSchema(tableName, columns);
  }