Files
SHiNE-server/SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v23.sql
T

33 lines
1.3 KiB
SQL

-- v23: trusted SHINE-ARCHIVE import state + last chunk location index.
-- Solana archive head itself is already copied by the normal Solana users sync (v22).
ALTER TABLE solana_user_pda_current
ADD COLUMN IF NOT EXISTS archive_imported BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE solana_user_pda_current
ADD COLUMN IF NOT EXISTS archive_last_imported_tx_id TEXT NOT NULL DEFAULT '';
CREATE INDEX IF NOT EXISTS idx_solana_user_pda_current_archive_pending
ON solana_user_pda_current(is_server, archive_imported)
WHERE archive_head_tx_id <> '';
CREATE TABLE IF NOT EXISTS archive_blockchain_location (
blockchain_name TEXT PRIMARY KEY,
publisher_login TEXT NOT NULL,
arweave_tx_id TEXT NOT NULL,
archive_hash BYTEA NOT NULL,
big_block_number BIGINT NOT NULL,
chunk_offset BIGINT NOT NULL,
chunk_size BIGINT NOT NULL,
source_last_block_number BIGINT NOT NULL,
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_archive_blockchain_location_publisher
ON archive_blockchain_location(publisher_login);
UPDATE db_schema_version
SET schema_version = 23,
updated_at_ms = CAST(EXTRACT(EPOCH FROM clock_timestamp())*1000 AS BIGINT)
WHERE id = 1;