Reset (Wipe)

This SQL script provides functionality to reset the Vircadia World database by dropping schemas, roles, and publications.

Environment Variables

You can customize the reset process by setting these environment variables:

• VRCA_CLI_SERVICE_POSTGRES_SYSTEM_RESET_DIR: Overrides the default system reset files (as seen below) with a custom set of files
• VRCA_CLI_SERVICE_POSTGRES_USER_RESET_DIR: Optional: sets another directory for user-defined reset SQL scripts

The reset process will execute all .sql files from both directories in alphanumeric order.

Reset Script

reset.sql

DO $$ 
DECLARE
    pubname RECORD;
BEGIN
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'vircadia_agent_proxy') THEN
        DROP OWNED BY vircadia_agent_proxy;

        DROP ROLE vircadia_agent_proxy;
    END IF;

    -- Drop publications if they exist
    FOR pubname IN (SELECT p.pubname AS publication_name FROM pg_publication p)
    LOOP
        EXECUTE 'DROP PUBLICATION ' || quote_ident(pubname.publication_name);
    END LOOP;

    -- Drop specific schemas and all their contents
    DROP SCHEMA IF EXISTS public CASCADE;
    DROP SCHEMA IF EXISTS auth CASCADE;
    DROP SCHEMA IF EXISTS entity CASCADE;
    DROP SCHEMA IF EXISTS tick CASCADE;
    DROP SCHEMA IF EXISTS config CASCADE;
    -- Recreate the public schema (this is required for PostgreSQL)
    CREATE SCHEMA public;
    GRANT ALL ON SCHEMA public TO PUBLIC;
END $$;