Adding Database Support¶

package io.queryaudit.postgresql;

import io.queryaudit.core.analyzer.IndexMetadataProvider;
import io.queryaudit.core.model.IndexInfo;
import io.queryaudit.core.model.IndexMetadata;

import java.sql.*;
import java.util.*;

public class PostgreSqlIndexMetadataProvider implements IndexMetadataProvider {

    private static final String INDEX_QUERY = """
            SELECT
                t.relname   AS table_name,
                i.relname   AS index_name,
                a.attname   AS column_name,
                array_position(ix.indkey, a.attnum) AS seq_in_index,
                NOT ix.indisunique AS non_unique,
                COALESCE(s.n_distinct, 0) AS cardinality
            FROM pg_class t
            JOIN pg_index ix ON t.oid = ix.indrelid
            JOIN pg_class i  ON i.oid = ix.indexrelid
            JOIN pg_attribute a ON a.attrelid = t.oid
                AND a.attnum = ANY(ix.indkey)
            LEFT JOIN pg_stats s ON s.tablename = t.relname
                AND s.attname = a.attname
            JOIN pg_namespace n ON n.oid = t.relnamespace
            WHERE n.nspname = 'public'
              AND t.relkind = 'r'
            ORDER BY t.relname, i.relname, array_position(ix.indkey, a.attnum)
            """;

    @Override
    public String supportedDatabase() {
        return "postgresql";  // Matched against DatabaseProductName
    }

    @Override
    public IndexMetadata getIndexMetadata(Connection connection) throws SQLException {
        Map<String, List<IndexInfo>> indexesByTable = new HashMap<>();

        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(INDEX_QUERY)) {

            while (rs.next()) {
                String tableName  = rs.getString("table_name");
                String indexName  = rs.getString("index_name");
                String columnName = rs.getString("column_name");
                int seqInIndex    = rs.getInt("seq_in_index");
                boolean nonUnique = rs.getBoolean("non_unique");
                long cardinality  = rs.getLong("cardinality");

                IndexInfo info = new IndexInfo(
                    tableName, indexName, columnName,
                    seqInIndex, nonUnique, cardinality
                );

                indexesByTable
                    .computeIfAbsent(tableName, k -> new ArrayList<>())
                    .add(info);
            }
        }

        return new IndexMetadata(indexesByTable);
    }
}

package io.queryaudit.postgresql;

import io.queryaudit.core.analyzer.ExplainAnalyzer;
import io.queryaudit.core.model.Issue;
import io.queryaudit.core.model.QueryRecord;

import java.util.List;

public class PostgreSqlExplainAnalyzer implements ExplainAnalyzer {

    @Override
    public String supportedDatabase() {
        return "postgresql";
    }

    @Override
    public List<Issue> analyze(String explainOutput, QueryRecord query) {
        // Parse PostgreSQL EXPLAIN (FORMAT JSON) output
        // Look for:
        //   - Seq Scan nodes (full table scan)
        //   - Sort nodes without index (filesort equivalent)
        //   - Materialize nodes (temporary table equivalent)
        //   - Nested Loop with inner Seq Scan (inefficient join)

        // Return a list of Issue objects for each detected problem.
        return List.of();
    }
}

package io.queryaudit.postgresql;

import io.queryaudit.core.model.IndexMetadata;
import org.junit.jupiter.api.*;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.sql.*;

import static org.junit.jupiter.api.Assertions.*;

@Testcontainers
class PostgreSqlIndexMetadataProviderTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    private Connection connection;

    @BeforeEach
    void setUp() throws SQLException {
        connection = DriverManager.getConnection(
            postgres.getJdbcUrl(),
            postgres.getUsername(),
            postgres.getPassword()
        );

        try (Statement stmt = connection.createStatement()) {
            stmt.execute("""
                CREATE TABLE IF NOT EXISTS orders (
                    id SERIAL PRIMARY KEY,
                    user_id INT NOT NULL,
                    status VARCHAR(50),
                    created_at TIMESTAMP DEFAULT NOW()
                )
                """);
            stmt.execute("CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders (user_id)");
        }
    }

    @AfterEach
    void tearDown() throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    @Test
    void shouldCollectIndexMetadata() throws SQLException {
        PostgreSqlIndexMetadataProvider provider = new PostgreSqlIndexMetadataProvider();

        assertEquals("postgresql", provider.supportedDatabase());

        IndexMetadata metadata = provider.getIndexMetadata(connection);
        assertNotNull(metadata);
        assertNotNull(metadata.getIndexesForTable("orders"));
        assertFalse(metadata.getIndexesForTable("orders").isEmpty());
    }

    @Test
    void shouldDetectUserIdIndex() throws SQLException {
        PostgreSqlIndexMetadataProvider provider = new PostgreSqlIndexMetadataProvider();
        IndexMetadata metadata = provider.getIndexMetadata(connection);

        boolean hasUserIdIndex = metadata.getIndexesForTable("orders").stream()
            .anyMatch(idx -> "user_id".equals(idx.columnName()));
        assertTrue(hasUserIdIndex, "Should detect index on orders.user_id");
    }
}