PostgreSQL Schema Management

/**
 * PostgreSQL schema implementation
 */
public class PostgreSQLSchema extends Schema<PostgreSQLDatabase, PostgreSQLTable> {
    /**
     * Creates a new PostgreSQL schema instance
     * @param jdbcTemplate JDBC template for database operations
     * @param database PostgreSQL database instance
     * @param name Schema name
     */
    protected PostgreSQLSchema(JdbcTemplate jdbcTemplate, 
                              PostgreSQLDatabase database, String name);
    
    /**
     * Checks if the schema exists in the database
     * @return true if schema exists
     * @throws SQLException if check fails
     */
    protected boolean doExists() throws SQLException;
    
    /**
     * Checks if the schema is empty (contains no objects)
     * @return false if schema contains tables, views, types, or functions
     * @throws SQLException if check fails
     */
    protected boolean doEmpty() throws SQLException;
    
    /**
     * Creates the schema in the database
     * @throws SQLException if creation fails
     */
    protected void doCreate() throws SQLException;
    
    /**
     * Drops the schema and all its objects using CASCADE
     * @throws SQLException if drop fails
     */
    protected void doDrop() throws SQLException;
    
    /**
     * Cleans all objects from the schema in dependency order
     * Removes materialized views, views, tables, types, functions, sequences, etc.
     * @throws SQLException if cleanup fails
     */
    protected void doClean() throws SQLException;
    
    /**
     * Returns all tables in the schema (excluding views and child tables)
     * @return Array of PostgreSQLTable instances
     * @throws SQLException if table enumeration fails
     */
    protected PostgreSQLTable[] doAllTables() throws SQLException;
    
    /**
     * Creates a PostgreSQL table instance for the given name
     * @param tableName Name of the table
     * @return PostgreSQLTable instance
     */
    public Table getTable(String tableName);
    
    /**
     * Creates a PostgreSQL type instance for the given name
     * @param typeName Name of the type
     * @return PostgreSQLType instance
     */
    protected Type getType(String typeName);
}

import org.flywaydb.database.postgresql.PostgreSQLSchema;

// Get schema instance (typically from connection)
Schema schema = connection.getSchema("my_schema");

// Check schema existence and state
if (schema.exists()) {
    System.out.println("Schema exists");
    if (schema.empty()) {
        System.out.println("Schema is empty");
    }
} else {
    // Create schema if it doesn't exist
    schema.create();
}

// Clean schema contents (preserves schema, removes all objects)
schema.clean();

// Get all tables in schema
Table[] tables = schema.allTables();
for (Table table : tables) {
    System.out.println("Found table: " + table.getName());
}

// Work with specific table
Table userTable = schema.getTable("users");
if (userTable.exists()) {
    userTable.drop();
}

/**
 * PostgreSQL table implementation
 */
public class PostgreSQLTable extends Table<PostgreSQLDatabase, PostgreSQLSchema> {
    /**
     * Creates a new PostgreSQL table instance
     * @param jdbcTemplate JDBC template for database operations
     * @param database PostgreSQL database instance  
     * @param schema PostgreSQL schema containing the table
     * @param name Table name
     */
    protected PostgreSQLTable(JdbcTemplate jdbcTemplate, 
                              PostgreSQLDatabase database, 
                              PostgreSQLSchema schema, String name);
    
    /**
     * Drops the table using CASCADE to handle dependencies
     * @throws SQLException if drop fails
     */
    protected void doDrop() throws SQLException;
    
    /**
     * Checks if the table exists in the database
     * @return true if table exists
     * @throws SQLException if check fails
     */
    protected boolean doExists() throws SQLException;
    
    /**
     * Locks the table for exclusive access using SELECT FOR UPDATE
     * @throws SQLException if locking fails
     */
    protected void doLock() throws SQLException;
}

import org.flywaydb.database.postgresql.PostgreSQLTable;

// Get table instance
PostgreSQLTable table = (PostgreSQLTable) schema.getTable("users");

// Check table existence
if (table.exists()) {
    // Lock table for exclusive access
    table.lock();
    
    // Perform operations while locked
    // ...
    
    // Drop table if needed
    table.drop();  // Uses CASCADE
}

/**
 * PostgreSQL type implementation
 */
public class PostgreSQLType extends Type<PostgreSQLDatabase, PostgreSQLSchema> {
    /**
     * Creates a new PostgreSQL type instance
     * @param jdbcTemplate JDBC template for database operations
     * @param database PostgreSQL database instance
     * @param schema PostgreSQL schema containing the type
     * @param name Type name
     */
    public PostgreSQLType(JdbcTemplate jdbcTemplate, 
                         PostgreSQLDatabase database, 
                         PostgreSQLSchema schema, String name);
    
    /**
     * Drops the type from the database
     * @throws SQLException if drop fails
     */
    protected void doDrop() throws SQLException;
}

// Full schema cleanup
schema.clean();

// Manual cleanup of specific object types
PostgreSQLSchema pgSchema = (PostgreSQLSchema) schema;

// Clean views
List<String> viewNames = jdbcTemplate.queryForStringList(
    "SELECT relname FROM pg_catalog.pg_class c " +
    "JOIN pg_namespace n ON n.oid = c.relnamespace " +
    "WHERE c.relkind = 'v' AND n.nspname = ?", schema.getName());
    
for (String viewName : viewNames) {
    jdbcTemplate.execute("DROP VIEW IF EXISTS " + 
                        database.quote(schema.getName(), viewName) + " CASCADE");
}

// Get all tables (excludes views and child tables)
PostgreSQLTable[] tables = schema.doAllTables();

// Tables are filtered to exclude:
// - Views (relkind = 'v')  
// - Child tables (those with inheritance)
// - Extension-dependent objects

// Work with custom types
Type statusType = schema.getType("status_type");
if (statusType.exists()) {
    statusType.drop();
}

// Enum types
jdbcTemplate.execute("CREATE TYPE status_type AS ENUM ('active', 'inactive')");

// Domain types  
jdbcTemplate.execute("CREATE DOMAIN email_domain AS text CHECK (VALUE ~ '^[^@]+@[^@]+$')");

// Functions are cleaned up automatically during schema.clean()
// Supports PostgreSQL 11+ procedures vs functions distinction

// Manual function cleanup
jdbcTemplate.execute("DROP FUNCTION IF EXISTS my_function(int, text) CASCADE");
jdbcTemplate.execute("DROP PROCEDURE IF EXISTS my_procedure(int) CASCADE");  // PostgreSQL 11+
jdbcTemplate.execute("DROP AGGREGATE IF EXISTS my_aggregate(int) CASCADE");

// Extensions are cleaned up if owned by current user
// Check for extensions in schema
List<String> extensions = jdbcTemplate.queryForStringList(
    "SELECT e.extname FROM pg_extension e " +
    "LEFT JOIN pg_namespace n ON n.oid = e.extnamespace " + 
    "LEFT JOIN pg_roles r ON r.oid = e.extowner " +
    "WHERE n.nspname=? AND r.rolname=?", 
    schema.getName(), database.getCurrentUser());

for (String extension : extensions) {
    jdbcTemplate.execute("DROP EXTENSION IF EXISTS " + 
                        database.quote(extension) + " CASCADE");
}

// Sequences are cleaned up automatically
// Manual sequence operations
jdbcTemplate.execute("CREATE SEQUENCE user_id_seq");
jdbcTemplate.execute("DROP SEQUENCE IF EXISTS user_id_seq");

try {
    schema.clean();
} catch (SQLException e) {
    System.err.println("Schema cleanup failed: " + e.getMessage());
    // Handle specific error cases
    if (e.getMessage().contains("permission denied")) {
        System.err.println("Insufficient privileges for cleanup");
    }
}

try {
    schema.create();
} catch (SQLException e) {
    System.err.println("Schema creation failed: " + e.getMessage());
    // Schema might already exist
}

// Materialized views (9.3+)
if (database.getVersion().isAtLeast("9.3")) {
    // Materialized views are included in cleanup
    List<String> matViews = jdbcTemplate.queryForStringList(
        "SELECT relname FROM pg_catalog.pg_class c " +
        "JOIN pg_namespace n ON n.oid = c.relnamespace " +
        "WHERE c.relkind = 'm' AND n.nspname = ?", schema.getName());
}

// Procedures vs functions distinction (11+)
if (database.getVersion().isAtLeast("11")) {
    // Uses 'prokind' column instead of deprecated 'proisagg'
    String isProcedure = "pg_proc.prokind = 'p'";
    String isAggregate = "pg_proc.prokind = 'a'";
} else {
    // Legacy detection for older versions
    String isProcedure = "FALSE";  
    String isAggregate = "pg_proc.proisagg";
}