Database Operations

/**
 * CREATE DATABASE statement for Hive dialect
 * Supports database properties, comments, and location specifications
 */
public class SqlCreateHiveDatabase extends SqlCreateDatabase {
    /**
     * Creates a new Hive database creation statement
     * @param pos Parser position information
     * @param databaseName Name of the database to create
     * @param propertyList Database properties (DBPROPERTIES)
     * @param comment Database comment
     * @param location Database location URI
     * @param ifNotExists Whether to use IF NOT EXISTS clause
     * @throws ParseException if validation fails
     */
    public SqlCreateHiveDatabase(SqlParserPos pos, SqlIdentifier databaseName, 
                                SqlNodeList propertyList, SqlCharStringLiteral comment, 
                                SqlCharStringLiteral location, boolean ifNotExists) throws ParseException;
}

public static final String DATABASE_LOCATION_URI = "hive.database.location-uri";

// Basic database creation
String createDbSql = "CREATE DATABASE sales_db";
SqlNode parsed = parser.parseStmt();

// Database with properties and location
String createDbWithPropsSql = """
    CREATE DATABASE IF NOT EXISTS analytics_db
    COMMENT 'Analytics database for reporting'
    LOCATION '/data/analytics'
    WITH DBPROPERTIES (
        'owner' = 'analytics_team',
        'department' = 'data_engineering'
    )
    """;

// Programmatic database creation
SqlIdentifier dbName = new SqlIdentifier("customer_db", SqlParserPos.ZERO);
SqlNodeList properties = new SqlNodeList(SqlParserPos.ZERO);
properties.add(new SqlTableOption("owner", "customer_team", SqlParserPos.ZERO));

SqlCreateHiveDatabase createDb = new SqlCreateHiveDatabase(
    SqlParserPos.ZERO,
    dbName,
    properties,
    SqlLiteral.createCharString("Customer data database", SqlParserPos.ZERO),
    SqlLiteral.createCharString("/data/customers", SqlParserPos.ZERO),
    true // IF NOT EXISTS
);

/**
 * Base class for ALTER DATABASE operations
 * Provides common functionality for all database alterations
 */
public abstract class SqlAlterHiveDatabase extends SqlAlterDatabase {
    /**
     * Creates base database alteration statement
     * @param pos Parser position information
     * @param databaseName Name of database to alter
     * @param propertyList Properties associated with the alteration
     */
    public SqlAlterHiveDatabase(SqlParserPos pos, SqlIdentifier databaseName, SqlNodeList propertyList);
    
    /**
     * Types of database alteration operations supported
     */
    public enum AlterHiveDatabaseOp { 
        CHANGE_PROPS,    // Change database properties
        CHANGE_LOCATION, // Change database location
        CHANGE_OWNER     // Change database owner
    }
}

public static final String ALTER_DATABASE_OP = "hive.alter.database.op";

/**
 * ALTER DATABASE SET LOCATION statement
 * Changes the location URI of an existing database
 */
public class SqlAlterHiveDatabaseLocation extends SqlAlterHiveDatabase {
    /**
     * Creates database location alteration statement
     * @param pos Parser position information
     * @param databaseName Name of database to alter
     * @param location New location URI for the database
     */
    public SqlAlterHiveDatabaseLocation(SqlParserPos pos, SqlIdentifier databaseName, 
                                       SqlCharStringLiteral location);
}

// Change database location
String alterLocationSql = "ALTER DATABASE sales_db SET LOCATION '/new/data/sales'";

// Programmatic location change
SqlIdentifier dbName = new SqlIdentifier("analytics_db", SqlParserPos.ZERO);
SqlCharStringLiteral newLocation = SqlLiteral.createCharString("/data/analytics_v2", SqlParserPos.ZERO);

SqlAlterHiveDatabaseLocation alterLocation = new SqlAlterHiveDatabaseLocation(
    SqlParserPos.ZERO,
    dbName,
    newLocation
);

/**
 * ALTER DATABASE SET OWNER statement
 * Changes the owner of an existing database (user or role)
 */
public class SqlAlterHiveDatabaseOwner extends SqlAlterHiveDatabase {
    /**
     * Creates database owner alteration statement
     * @param pos Parser position information
     * @param databaseName Name of database to alter
     * @param ownerType Type of owner ("user" or "role")
     * @param ownerName Name of the new owner
     */
    public SqlAlterHiveDatabaseOwner(SqlParserPos pos, SqlIdentifier databaseName, 
                                    String ownerType, SqlIdentifier ownerName);
}

public static final String DATABASE_OWNER_NAME = "hive.database.owner.name";
public static final String DATABASE_OWNER_TYPE = "hive.database.owner.type";
public static final String USER_OWNER = "user";
public static final String ROLE_OWNER = "role";

// Change database owner to user
String alterOwnerSql = "ALTER DATABASE sales_db SET OWNER USER john_doe";

// Change database owner to role
String alterRoleSql = "ALTER DATABASE analytics_db SET OWNER ROLE data_engineers";

// Programmatic owner change
SqlIdentifier dbName = new SqlIdentifier("customer_db", SqlParserPos.ZERO);
SqlIdentifier ownerName = new SqlIdentifier("analytics_team", SqlParserPos.ZERO);

SqlAlterHiveDatabaseOwner alterOwner = new SqlAlterHiveDatabaseOwner(
    SqlParserPos.ZERO,
    dbName,
    SqlAlterHiveDatabaseOwner.ROLE_OWNER,
    ownerName
);

/**
 * ALTER DATABASE SET DBPROPERTIES statement
 * Changes the properties of an existing database
 */
public class SqlAlterHiveDatabaseProps extends SqlAlterHiveDatabase {
    /**
     * Creates database properties alteration statement
     * @param pos Parser position information
     * @param databaseName Name of database to alter
     * @param propertyList New properties to set
     * @throws ParseException if properties validation fails
     */
    public SqlAlterHiveDatabaseProps(SqlParserPos pos, SqlIdentifier databaseName, 
                                    SqlNodeList propertyList) throws ParseException;
}

// Change database properties
String alterPropsSql = """
    ALTER DATABASE sales_db SET DBPROPERTIES (
        'last_modified' = '2023-12-01',
        'department' = 'sales_operations',
        'retention_days' = '365'
    )
    """;

// Programmatic properties change
SqlIdentifier dbName = new SqlIdentifier("analytics_db", SqlParserPos.ZERO);
SqlNodeList properties = new SqlNodeList(SqlParserPos.ZERO);

properties.add(new SqlTableOption("owner", "new_owner", SqlParserPos.ZERO));
properties.add(new SqlTableOption("environment", "production", SqlParserPos.ZERO));
properties.add(new SqlTableOption("backup_enabled", "true", SqlParserPos.ZERO));

SqlAlterHiveDatabaseProps alterProps = new SqlAlterHiveDatabaseProps(
    SqlParserPos.ZERO,
    dbName,
    properties
);

// Reserved database properties that cannot be set by users
Set<String> reservedProps = Set.of(
    "hive.alter.database.op",
    "hive.database.location-uri"
);

// Properties validation is performed automatically
try {
    SqlAlterHiveDatabaseProps alterProps = new SqlAlterHiveDatabaseProps(pos, dbName, props);
} catch (ParseException e) {
    // Thrown if trying to set reserved properties
    System.err.println("Cannot set reserved property: " + e.getMessage());
}

// 1. Create database
String createSql = """
    CREATE DATABASE IF NOT EXISTS company_data
    COMMENT 'Main company data warehouse'
    LOCATION '/data/warehouse/company'
    WITH DBPROPERTIES (
        'owner' = 'data_team',
        'environment' = 'production',
        'created_date' = '2023-01-01'
    )
    """;

// 2. Change database location
String relocateSql = "ALTER DATABASE company_data SET LOCATION '/data/warehouse/v2/company'";

// 3. Change database owner
String changeOwnerSql = "ALTER DATABASE company_data SET OWNER ROLE data_engineers";

// 4. Update database properties
String updatePropsSql = """
    ALTER DATABASE company_data SET DBPROPERTIES (
        'last_backup' = '2023-12-01',
        'backup_retention' = '90',
        'compliance_level' = 'high'
    )
    """;

// Parse and execute each statement
for (String sql : List.of(createSql, relocateSql, changeOwnerSql, updatePropsSql)) {
    try {
        SqlNode parsed = parser.create(sql).parseStmt();
        // Execute with Flink TableEnvironment
        tableEnv.executeSql(sql);
    } catch (SqlParseException e) {
        System.err.println("Failed to parse: " + sql + " - " + e.getMessage());
    }
}