diff.sql

alter table applications_phases_rewrite_rules_actions add "redirect_url_custom" text;
alter table applications_phases_rewrite_rules_actions add "redirect_args" varchar(16);
alter table applications_phases_rewrite_rules_actions add "redirect_args_custom" text;
alter table applications_phases_rewrite_rules_actions add "redirect_domain" text;
alter table applications_phases_rewrite_rules_actions add "redirect_port" integer;
alter table applications_phases_rewrite_rules_actions add "limit_req_rate_key_arg" text;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_name" text;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_value" text;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_domain" text;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_path" text;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_http_only" boolean;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_max_age" numeric;
alter table applications_phases_resp_rewrite_rules_actions add "set_resp_cookie_max_age_unit" varchar(8);

alter table applications_users add created timestamp default current_timestamp;
alter table applications_users add modified timestamp default current_timestamp;
CREATE TRIGGER update_sync_modtime BEFORE UPDATE ON applications_users FOR EACH ROW EXECUTE PROCEDURE update_modified_column();


alter table applications_phases_proxy_rules drop "config_retry_condition";
alter table applications_phases_proxy_rules add "config_retry_condition" varchar(32) ARRAY;

alter table applications_phases_rewrite_rules_actions add "limit_req_count_limit_key" varchar(32);
alter table applications_phases_rewrite_rules_actions add "limit_req_count_key_arg" text;
alter table applications_phases_rewrite_rules_actions add "limit_req_count_count_reject" numeric;
alter table applications_phases_rewrite_rules_actions add "limit_req_count_count_time_window" numeric;

alter table applications_clusters add "checker_concurrency" integer;
alter table applications_clusters add "checker_fall" integer;
alter table applications_clusters add "checker_http_req_host" text;
alter table applications_clusters add "checker_http_req_uri" text;
alter table applications_clusters add "checker_interval" integer;
alter table applications_clusters add "checker_interval_unit" varchar(8);
alter table applications_clusters add "checker_report_interval" integer;
alter table applications_clusters add "checker_report_interval_unit" varchar(8);
alter table applications_clusters add "checker_rise" integer;
alter table applications_clusters add "checker_timeout" integer;

alter table applications rename column use_all_ip_enable to allow_access_by_ip;
alter table applications rename column server_ip_list to allow_access_by_ip_list;

alter table applications rename phases_ssl_cert_limit_connEnabled to phases_ssl_cert_limit_enable_conn_limit;
alter table applications rename phases_ssl_cert_limit_rateEnabled to phases_ssl_cert_limit_enable_rate_limit;
alter table applications_clusters rename checker_enable to enable_checker;
alter table gateway_nodes rename ip to external_ip;
alter table gateway_nodes rename inner_ip to internal_ip;
alter table global rename ngx_access_log_enable to ngx_enable_access_log;
alter table global rename ngx_dns_enable to ngx_enable_dns;
alter table global rename ngx_http2_enable to ngx_enable_http2;
alter table global rename ngx_ignore_invalid_headers_enable to ngx_ignore_invalid_headers;
alter table global rename ngx_open_file_cache_enable to ngx_enable_open_file_cache;
alter table global rename ngx_proxy_ignore_client_abort_enable to ngx_proxy_ignore_client_abort;
alter table global rename ngx_proxy_intercept_errors_enable to ngx_proxy_intercept_errors;
alter table global rename ngx_ssl_prefer_server_ciphers_enable to ngx_ssl_prefer_server_ciphers;

alter table applications_clusters_nodes alter ip TYPE inet USING ip::inet;
alter table applications_dns_config_authority alter address TYPE inet USING address::inet;
alter table gateway_nodes alter external_ip TYPE inet USING external_ip::inet;
alter table gateway_nodes alter internal_ip TYPE inet USING internal_ip::inet;


alter table applications alter "name" TYPE varchar(128);
alter table applications_clusters alter "name" TYPE varchar(128);
alter table applications_variable alter "name" TYPE varchar(128);
alter table gateway alter "name" TYPE varchar(128);
alter table gateway_nodes alter "name" TYPE varchar(128);

alter table applications add "phases_waf_action" varchar(64);

create table license (
    id serial,
    "total" integer not null,
    "expired" integer not null,
    "balance" integer not null,
    "user_id" text not null,
    "currency" varchar(8) not null,
    "hourly_price" integer not null,
    "updater" varchar(16) not null,
    "used_licenses" integer not null default '0',
    created timestamp default current_timestamp,
    modified timestamp default current_timestamp,
    UNIQUE (id)
);
CREATE TRIGGER update_sync_modtime BEFORE UPDATE ON license FOR EACH ROW EXECUTE PROCEDURE update_modified_column();


-- liscense start
create table license_sync_logs (
    id serial,
    "total" integer not null,
    "expired" integer not null,
    "balance" integer not null,
    "user_id" text not null,
    "currency" varchar(8) not null,
    "hourly_price" integer not null,
    "updater" varchar(16),
    "used_licenses" integer not null default '0',
    created timestamp default current_timestamp,
    modified timestamp default current_timestamp,
    UNIQUE(id)
);

CREATE OR REPLACE FUNCTION audit_license_log_func() RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO license_sync_logs(total, expired, balance, user_id, currency, hourly_price, updater, used_licenses, created, modified)
        VALUES (NEW.total, NEW.expired, NEW.balance, NEW.user_id, NEW.currency, NEW.hourly_price, NEW.updater, NEW.used_licenses, NEW.created, NEW.modified);
        RETURN NEW;
    END;$$ language 'plpgsql';

CREATE TRIGGER audit_license_sync BEFORE UPDATE ON license
FOR EACH ROW EXECUTE PROCEDURE audit_license_log_func();

insert into license(user_id, total, expired, balance, currency,
    hourly_price, updater, used_licenses)
values(1, 0, extract(EPOCH FROM now()), 0, 'CNY', 0, 'local', 0);

-- liscense end


alter table applications_phases_rewrite_rules add column _placeholder char(1) not null default '';
alter table applications_phases_resp_rewrite_rules add column _placeholder char(1) not null default '';
alter table applications_dns_rules add column _placeholder char(1) not null default '';
alter table global add column _placeholder char(1) not null default '';

ALTER TABLE ONLY applications_clusters
    ADD CONSTRAINT applications_clusters__applications_id_name_key UNIQUE (_applications_id, name);
ALTER TABLE ONLY applications_phases_proxy_rules_config_upstream
    ADD CONSTRAINT applications_phases_proxy_rul__applications_phases_proxy_ru_key UNIQUE (_applications_phases_proxy_rules_id, _applications_id, cluster);
ALTER TABLE ONLY applications_variable
    ADD CONSTRAINT applications_variable__applications_id_name_key UNIQUE (_applications_id, name);
ALTER TABLE ONLY gateway
    ADD CONSTRAINT gateway_name_key UNIQUE (name);
create table applications_phases_proxy_rules_config_backup_upstream (
    id serial,
    _applications_phases_proxy_rules_id integer not null REFERENCES applications_phases_proxy_rules (id),
    _applications_id integer not null REFERENCES applications (id),
    "item" integer not null REFERENCES applications_clusters (id),
    UNIQUE (id)
);

-- XX: after check all is null
alter table applications_phases_proxy_rules DROP COLUMN "config_backup_upstream";


-- privilege after create new table.
REVOKE ALL ON schema public FROM public;
GRANT CONNECT ON DATABASE or_edge_admin TO or_edge_admin;
GRANT USAGE ON SCHEMA public TO or_edge_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON  ALL TABLES IN SCHEMA public to or_edge_admin;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO or_edge_admin;