SHA256
Перевести deploy и PostgreSQL схемы на актуальное состояние
This commit is contained in:
AidarKC
@@ -18,7 +18,9 @@ public final class DatabaseInitializer {
|
||||
|
||||
public static final String DB_SCHEMA_VERSION_TABLE = "db_schema_version";
|
||||
public static final int SCHEMA_VERSION_1 = 1;
|
||||
public static final int SCHEMA_VERSION_2 = 2;
|
||||
public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
|
||||
public static final String POSTGRES_MIGRATION_V2_RESOURCE = "postgres/migration_v2.sql";
|
||||
|
||||
private DatabaseInitializer() {}
|
||||
|
||||
@@ -73,10 +75,14 @@ public final class DatabaseInitializer {
|
||||
}
|
||||
|
||||
try (Connection conn = openConnection(jdbcUrl, user, password)) {
|
||||
if (postgresSchemaVersionTableExists(conn)) {
|
||||
if (!postgresSchemaVersionTableExists(conn)) {
|
||||
runSqlScript(conn, POSTGRES_SCHEMA_RESOURCE);
|
||||
return;
|
||||
}
|
||||
runPostgresSchemaScript(conn);
|
||||
int currentVersion = readCurrentSchemaVersion(conn);
|
||||
if (currentVersion < SCHEMA_VERSION_2) {
|
||||
runSqlScript(conn, POSTGRES_MIGRATION_V2_RESOURCE);
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
@@ -104,8 +110,23 @@ public final class DatabaseInitializer {
|
||||
}
|
||||
}
|
||||
|
||||
private static void runPostgresSchemaScript(Connection conn) throws SQLException {
|
||||
String sqlScript = loadClasspathResource(POSTGRES_SCHEMA_RESOURCE);
|
||||
private static int readCurrentSchemaVersion(Connection conn) throws SQLException {
|
||||
String sql = """
|
||||
SELECT schema_version
|
||||
FROM db_schema_version
|
||||
WHERE id = 1
|
||||
""";
|
||||
try (Statement st = conn.createStatement();
|
||||
ResultSet rs = st.executeQuery(sql)) {
|
||||
if (!rs.next()) {
|
||||
return SCHEMA_VERSION_1;
|
||||
}
|
||||
return rs.getInt(1);
|
||||
}
|
||||
}
|
||||
|
||||
private static void runSqlScript(Connection conn, String resourcePath) throws SQLException {
|
||||
String sqlScript = loadClasspathResource(resourcePath);
|
||||
List<String> statements = splitSqlStatements(sqlScript);
|
||||
|
||||
boolean previousAutoCommit = conn.getAutoCommit();
|
||||
|
||||
@@ -0,0 +1,178 @@
|
||||
BEGIN;
|
||||
|
||||
CREATE TABLE IF NOT EXISTS user_access_servers_current (
|
||||
user_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
|
||||
server_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
|
||||
server_url TEXT NOT NULL,
|
||||
server_client_key TEXT NOT NULL,
|
||||
user_record_number INTEGER NOT NULL,
|
||||
user_updated_at_ms BIGINT NOT NULL,
|
||||
server_record_number INTEGER NOT NULL,
|
||||
server_updated_at_ms BIGINT NOT NULL,
|
||||
refreshed_at_ms BIGINT NOT NULL,
|
||||
PRIMARY KEY (user_login, server_login)
|
||||
);
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_user_access_servers_user
|
||||
ON user_access_servers_current(user_login);
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_user_access_servers_server
|
||||
ON user_access_servers_current(server_login);
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_user(p_user_login TEXT)
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF p_user_login IS NULL OR btrim(p_user_login) = '' THEN
|
||||
RETURN;
|
||||
END IF;
|
||||
|
||||
DELETE FROM user_access_servers_current
|
||||
WHERE LOWER(user_login) = LOWER(p_user_login);
|
||||
|
||||
INSERT INTO user_access_servers_current (
|
||||
user_login,
|
||||
server_login,
|
||||
server_url,
|
||||
server_client_key,
|
||||
user_record_number,
|
||||
user_updated_at_ms,
|
||||
server_record_number,
|
||||
server_updated_at_ms,
|
||||
refreshed_at_ms
|
||||
)
|
||||
SELECT
|
||||
u.login,
|
||||
s.login,
|
||||
s.server_address,
|
||||
s.client_key,
|
||||
u.record_number,
|
||||
u.updated_at_ms,
|
||||
s.record_number,
|
||||
s.updated_at_ms,
|
||||
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
|
||||
FROM solana_user_pda_current u
|
||||
CROSS JOIN LATERAL jsonb_array_elements_text(
|
||||
CASE
|
||||
WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
|
||||
ELSE u.access_servers_json::jsonb
|
||||
END
|
||||
) AS access_server(login_value)
|
||||
JOIN solana_user_pda_current s
|
||||
ON LOWER(s.login) = LOWER(btrim(access_server.login_value))
|
||||
AND s.is_server = TRUE
|
||||
AND btrim(COALESCE(s.server_address, '')) <> ''
|
||||
WHERE LOWER(u.login) = LOWER(p_user_login)
|
||||
ON CONFLICT (user_login, server_login) DO UPDATE SET
|
||||
server_url = EXCLUDED.server_url,
|
||||
server_client_key = EXCLUDED.server_client_key,
|
||||
user_record_number = EXCLUDED.user_record_number,
|
||||
user_updated_at_ms = EXCLUDED.user_updated_at_ms,
|
||||
server_record_number = EXCLUDED.server_record_number,
|
||||
server_updated_at_ms = EXCLUDED.server_updated_at_ms,
|
||||
refreshed_at_ms = EXCLUDED.refreshed_at_ms;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_server(p_server_login TEXT)
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
DECLARE
|
||||
affected_user RECORD;
|
||||
BEGIN
|
||||
IF p_server_login IS NULL OR btrim(p_server_login) = '' THEN
|
||||
RETURN;
|
||||
END IF;
|
||||
|
||||
DELETE FROM user_access_servers_current
|
||||
WHERE LOWER(server_login) = LOWER(p_server_login);
|
||||
|
||||
FOR affected_user IN
|
||||
SELECT u.login
|
||||
FROM solana_user_pda_current u
|
||||
CROSS JOIN LATERAL jsonb_array_elements_text(
|
||||
CASE
|
||||
WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
|
||||
ELSE u.access_servers_json::jsonb
|
||||
END
|
||||
) AS access_server(login_value)
|
||||
WHERE LOWER(btrim(access_server.login_value)) = LOWER(p_server_login)
|
||||
LOOP
|
||||
PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
|
||||
END LOOP;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_all()
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
DECLARE
|
||||
affected_user RECORD;
|
||||
BEGIN
|
||||
TRUNCATE TABLE user_access_servers_current;
|
||||
|
||||
FOR affected_user IN
|
||||
SELECT login
|
||||
FROM solana_user_pda_current
|
||||
LOOP
|
||||
PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
|
||||
END LOOP;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_row()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF TG_OP = 'DELETE' THEN
|
||||
PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
|
||||
RETURN OLD;
|
||||
END IF;
|
||||
|
||||
IF TG_OP = 'UPDATE' AND LOWER(OLD.login) <> LOWER(NEW.login) THEN
|
||||
PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
|
||||
END IF;
|
||||
|
||||
PERFORM shine_refresh_user_access_servers_for_user(NEW.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(NEW.login);
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
TRUNCATE TABLE user_access_servers_current;
|
||||
RETURN NULL;
|
||||
END;
|
||||
$$;
|
||||
|
||||
DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_row ON solana_user_pda_current;
|
||||
CREATE TRIGGER trg_refresh_user_access_servers_row
|
||||
AFTER INSERT OR UPDATE OR DELETE ON solana_user_pda_current
|
||||
FOR EACH ROW
|
||||
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_row();
|
||||
|
||||
DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_truncate ON solana_user_pda_current;
|
||||
CREATE TRIGGER trg_refresh_user_access_servers_truncate
|
||||
AFTER TRUNCATE ON solana_user_pda_current
|
||||
FOR EACH STATEMENT
|
||||
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate();
|
||||
|
||||
SELECT shine_refresh_user_access_servers_all();
|
||||
|
||||
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
|
||||
VALUES (1, 2, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
|
||||
ON CONFLICT (id) DO UPDATE SET
|
||||
schema_version = EXCLUDED.schema_version,
|
||||
updated_at_ms = EXCLUDED.updated_at_ms;
|
||||
|
||||
COMMIT;
|
||||
@@ -1,5 +1,5 @@
|
||||
-- SHiNE PostgreSQL runtime schema v1
|
||||
-- Дата: 2026-07-24
|
||||
-- Дата: 2026-07-28
|
||||
--
|
||||
-- Назначение:
|
||||
-- - поднять пустую PostgreSQL БД сервера SHiNE с нуля;
|
||||
@@ -22,7 +22,7 @@ CREATE TABLE IF NOT EXISTS db_schema_version (
|
||||
);
|
||||
|
||||
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
|
||||
VALUES (1, 1, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
|
||||
VALUES (1, 2, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
|
||||
ON CONFLICT (id) DO UPDATE SET
|
||||
schema_version = EXCLUDED.schema_version,
|
||||
updated_at_ms = EXCLUDED.updated_at_ms;
|
||||
@@ -109,6 +109,173 @@ CREATE TABLE IF NOT EXISTS solana_user_pda_current (
|
||||
CREATE INDEX IF NOT EXISTS idx_user_pda_current_slot
|
||||
ON solana_user_pda_current(slot);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS user_access_servers_current (
|
||||
user_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
|
||||
server_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
|
||||
server_url TEXT NOT NULL,
|
||||
server_client_key TEXT NOT NULL,
|
||||
user_record_number INTEGER NOT NULL,
|
||||
user_updated_at_ms BIGINT NOT NULL,
|
||||
server_record_number INTEGER NOT NULL,
|
||||
server_updated_at_ms BIGINT NOT NULL,
|
||||
refreshed_at_ms BIGINT NOT NULL,
|
||||
PRIMARY KEY (user_login, server_login)
|
||||
);
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_user_access_servers_user
|
||||
ON user_access_servers_current(user_login);
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_user_access_servers_server
|
||||
ON user_access_servers_current(server_login);
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_user(p_user_login TEXT)
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF p_user_login IS NULL OR btrim(p_user_login) = '' THEN
|
||||
RETURN;
|
||||
END IF;
|
||||
|
||||
DELETE FROM user_access_servers_current
|
||||
WHERE LOWER(user_login) = LOWER(p_user_login);
|
||||
|
||||
INSERT INTO user_access_servers_current (
|
||||
user_login,
|
||||
server_login,
|
||||
server_url,
|
||||
server_client_key,
|
||||
user_record_number,
|
||||
user_updated_at_ms,
|
||||
server_record_number,
|
||||
server_updated_at_ms,
|
||||
refreshed_at_ms
|
||||
)
|
||||
SELECT
|
||||
u.login,
|
||||
s.login,
|
||||
s.server_address,
|
||||
s.client_key,
|
||||
u.record_number,
|
||||
u.updated_at_ms,
|
||||
s.record_number,
|
||||
s.updated_at_ms,
|
||||
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
|
||||
FROM solana_user_pda_current u
|
||||
CROSS JOIN LATERAL jsonb_array_elements_text(
|
||||
CASE
|
||||
WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
|
||||
ELSE u.access_servers_json::jsonb
|
||||
END
|
||||
) AS access_server(login_value)
|
||||
JOIN solana_user_pda_current s
|
||||
ON LOWER(s.login) = LOWER(btrim(access_server.login_value))
|
||||
AND s.is_server = TRUE
|
||||
AND btrim(COALESCE(s.server_address, '')) <> ''
|
||||
WHERE LOWER(u.login) = LOWER(p_user_login)
|
||||
ON CONFLICT (user_login, server_login) DO UPDATE SET
|
||||
server_url = EXCLUDED.server_url,
|
||||
server_client_key = EXCLUDED.server_client_key,
|
||||
user_record_number = EXCLUDED.user_record_number,
|
||||
user_updated_at_ms = EXCLUDED.user_updated_at_ms,
|
||||
server_record_number = EXCLUDED.server_record_number,
|
||||
server_updated_at_ms = EXCLUDED.server_updated_at_ms,
|
||||
refreshed_at_ms = EXCLUDED.refreshed_at_ms;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_server(p_server_login TEXT)
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
DECLARE
|
||||
affected_user RECORD;
|
||||
BEGIN
|
||||
IF p_server_login IS NULL OR btrim(p_server_login) = '' THEN
|
||||
RETURN;
|
||||
END IF;
|
||||
|
||||
DELETE FROM user_access_servers_current
|
||||
WHERE LOWER(server_login) = LOWER(p_server_login);
|
||||
|
||||
FOR affected_user IN
|
||||
SELECT u.login
|
||||
FROM solana_user_pda_current u
|
||||
CROSS JOIN LATERAL jsonb_array_elements_text(
|
||||
CASE
|
||||
WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
|
||||
ELSE u.access_servers_json::jsonb
|
||||
END
|
||||
) AS access_server(login_value)
|
||||
WHERE LOWER(btrim(access_server.login_value)) = LOWER(p_server_login)
|
||||
LOOP
|
||||
PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
|
||||
END LOOP;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_all()
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
DECLARE
|
||||
affected_user RECORD;
|
||||
BEGIN
|
||||
TRUNCATE TABLE user_access_servers_current;
|
||||
|
||||
FOR affected_user IN
|
||||
SELECT login
|
||||
FROM solana_user_pda_current
|
||||
LOOP
|
||||
PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
|
||||
END LOOP;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_row()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF TG_OP = 'DELETE' THEN
|
||||
PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
|
||||
RETURN OLD;
|
||||
END IF;
|
||||
|
||||
IF TG_OP = 'UPDATE' AND LOWER(OLD.login) <> LOWER(NEW.login) THEN
|
||||
PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
|
||||
END IF;
|
||||
|
||||
PERFORM shine_refresh_user_access_servers_for_user(NEW.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(NEW.login);
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
TRUNCATE TABLE user_access_servers_current;
|
||||
RETURN NULL;
|
||||
END;
|
||||
$$;
|
||||
|
||||
DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_row ON solana_user_pda_current;
|
||||
CREATE TRIGGER trg_refresh_user_access_servers_row
|
||||
AFTER INSERT OR UPDATE OR DELETE ON solana_user_pda_current
|
||||
FOR EACH ROW
|
||||
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_row();
|
||||
|
||||
DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_truncate ON solana_user_pda_current;
|
||||
CREATE TRIGGER trg_refresh_user_access_servers_truncate
|
||||
AFTER TRUNCATE ON solana_user_pda_current
|
||||
FOR EACH STATEMENT
|
||||
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate();
|
||||
|
||||
CREATE TABLE IF NOT EXISTS solana_user_pda_history (
|
||||
id BIGSERIAL PRIMARY KEY,
|
||||
tx_signature TEXT NOT NULL,
|
||||
|
||||
+202
@@ -996,6 +996,208 @@ public final class PostgresStorageRepository
|
||||
"CREATE INDEX IF NOT EXISTS idx_user_pda_history_slot " +
|
||||
"ON solana_user_pda_history(slot)"
|
||||
);
|
||||
|
||||
statement.executeUpdate(
|
||||
"CREATE TABLE IF NOT EXISTS user_access_servers_current (" +
|
||||
"user_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE, " +
|
||||
"server_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE, " +
|
||||
"server_url TEXT NOT NULL, " +
|
||||
"server_client_key TEXT NOT NULL, " +
|
||||
"user_record_number INTEGER NOT NULL, " +
|
||||
"user_updated_at_ms BIGINT NOT NULL, " +
|
||||
"server_record_number INTEGER NOT NULL, " +
|
||||
"server_updated_at_ms BIGINT NOT NULL, " +
|
||||
"refreshed_at_ms BIGINT NOT NULL, " +
|
||||
"PRIMARY KEY (user_login, server_login)" +
|
||||
")"
|
||||
);
|
||||
|
||||
statement.executeUpdate(
|
||||
"CREATE INDEX IF NOT EXISTS idx_user_access_servers_user " +
|
||||
"ON user_access_servers_current(user_login)"
|
||||
);
|
||||
statement.executeUpdate(
|
||||
"CREATE INDEX IF NOT EXISTS idx_user_access_servers_server " +
|
||||
"ON user_access_servers_current(server_login)"
|
||||
);
|
||||
|
||||
statement.execute(
|
||||
"""
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_user(p_user_login TEXT)
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF p_user_login IS NULL OR btrim(p_user_login) = '' THEN
|
||||
RETURN;
|
||||
END IF;
|
||||
|
||||
DELETE FROM user_access_servers_current
|
||||
WHERE LOWER(user_login) = LOWER(p_user_login);
|
||||
|
||||
INSERT INTO user_access_servers_current (
|
||||
user_login,
|
||||
server_login,
|
||||
server_url,
|
||||
server_client_key,
|
||||
user_record_number,
|
||||
user_updated_at_ms,
|
||||
server_record_number,
|
||||
server_updated_at_ms,
|
||||
refreshed_at_ms
|
||||
)
|
||||
SELECT
|
||||
u.login,
|
||||
s.login,
|
||||
s.server_address,
|
||||
s.client_key,
|
||||
u.record_number,
|
||||
u.updated_at_ms,
|
||||
s.record_number,
|
||||
s.updated_at_ms,
|
||||
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
|
||||
FROM solana_user_pda_current u
|
||||
CROSS JOIN LATERAL jsonb_array_elements_text(
|
||||
CASE
|
||||
WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
|
||||
ELSE u.access_servers_json::jsonb
|
||||
END
|
||||
) AS access_server(login_value)
|
||||
JOIN solana_user_pda_current s
|
||||
ON LOWER(s.login) = LOWER(btrim(access_server.login_value))
|
||||
AND s.is_server = TRUE
|
||||
AND btrim(COALESCE(s.server_address, '')) <> ''
|
||||
WHERE LOWER(u.login) = LOWER(p_user_login)
|
||||
ON CONFLICT (user_login, server_login) DO UPDATE SET
|
||||
server_url = EXCLUDED.server_url,
|
||||
server_client_key = EXCLUDED.server_client_key,
|
||||
user_record_number = EXCLUDED.user_record_number,
|
||||
user_updated_at_ms = EXCLUDED.user_updated_at_ms,
|
||||
server_record_number = EXCLUDED.server_record_number,
|
||||
server_updated_at_ms = EXCLUDED.server_updated_at_ms,
|
||||
refreshed_at_ms = EXCLUDED.refreshed_at_ms;
|
||||
END;
|
||||
$$;
|
||||
"""
|
||||
);
|
||||
|
||||
statement.execute(
|
||||
"""
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_server(p_server_login TEXT)
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
DECLARE
|
||||
affected_user RECORD;
|
||||
BEGIN
|
||||
IF p_server_login IS NULL OR btrim(p_server_login) = '' THEN
|
||||
RETURN;
|
||||
END IF;
|
||||
|
||||
DELETE FROM user_access_servers_current
|
||||
WHERE LOWER(server_login) = LOWER(p_server_login);
|
||||
|
||||
FOR affected_user IN
|
||||
SELECT u.login
|
||||
FROM solana_user_pda_current u
|
||||
CROSS JOIN LATERAL jsonb_array_elements_text(
|
||||
CASE
|
||||
WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
|
||||
ELSE u.access_servers_json::jsonb
|
||||
END
|
||||
) AS access_server(login_value)
|
||||
WHERE LOWER(btrim(access_server.login_value)) = LOWER(p_server_login)
|
||||
LOOP
|
||||
PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
|
||||
END LOOP;
|
||||
END;
|
||||
$$;
|
||||
"""
|
||||
);
|
||||
|
||||
statement.execute(
|
||||
"""
|
||||
CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_all()
|
||||
RETURNS VOID
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
DECLARE
|
||||
affected_user RECORD;
|
||||
BEGIN
|
||||
TRUNCATE TABLE user_access_servers_current;
|
||||
|
||||
FOR affected_user IN
|
||||
SELECT login
|
||||
FROM solana_user_pda_current
|
||||
LOOP
|
||||
PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
|
||||
END LOOP;
|
||||
END;
|
||||
$$;
|
||||
"""
|
||||
);
|
||||
|
||||
statement.execute(
|
||||
"""
|
||||
CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_row()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF TG_OP = 'DELETE' THEN
|
||||
PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
|
||||
RETURN OLD;
|
||||
END IF;
|
||||
|
||||
IF TG_OP = 'UPDATE' AND LOWER(OLD.login) <> LOWER(NEW.login) THEN
|
||||
PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
|
||||
END IF;
|
||||
|
||||
PERFORM shine_refresh_user_access_servers_for_user(NEW.login);
|
||||
PERFORM shine_refresh_user_access_servers_for_server(NEW.login);
|
||||
RETURN NEW;
|
||||
END;
|
||||
$$;
|
||||
"""
|
||||
);
|
||||
|
||||
statement.execute(
|
||||
"""
|
||||
CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
TRUNCATE TABLE user_access_servers_current;
|
||||
RETURN NULL;
|
||||
END;
|
||||
$$;
|
||||
"""
|
||||
);
|
||||
|
||||
statement.executeUpdate(
|
||||
"DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_row ON solana_user_pda_current"
|
||||
);
|
||||
statement.executeUpdate(
|
||||
"CREATE TRIGGER trg_refresh_user_access_servers_row " +
|
||||
"AFTER INSERT OR UPDATE OR DELETE ON solana_user_pda_current " +
|
||||
"FOR EACH ROW " +
|
||||
"EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_row()"
|
||||
);
|
||||
|
||||
statement.executeUpdate(
|
||||
"DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_truncate ON solana_user_pda_current"
|
||||
);
|
||||
statement.executeUpdate(
|
||||
"CREATE TRIGGER trg_refresh_user_access_servers_truncate " +
|
||||
"AFTER TRUNCATE ON solana_user_pda_current " +
|
||||
"FOR EACH STATEMENT " +
|
||||
"EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate()"
|
||||
);
|
||||
|
||||
statement.execute("SELECT shine_refresh_user_access_servers_all()");
|
||||
}
|
||||
}
|
||||
|
||||
|
||||
Reference in New Issue
Block a user