/* for historical amm pools */ CREATE TABLE `amm_pools` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `asset1_currency` VARCHAR(40) NOT NULL, `asset1_issuer` VARCHAR(35) DEFAULT NULL, -- NULL for XRP `asset2_currency` VARCHAR(40) NOT NULL, `asset2_issuer` VARCHAR(35) DEFAULT NULL, -- NULL for XRP `ledger_index_created` INT UNSIGNED NOT NULL, `created_timestamp` TIMESTAMP NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_asset_pair` (`asset1_currency`, `asset1_issuer`, `asset2_currency`, `asset2_issuer`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* for histoerical amm pools part 2 */ CREATE TABLE `amm_pool_daily_snapshots` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `pool_id` INT UNSIGNED NOT NULL, `snapshot_date` DATE NOT NULL, `asset1_balance` DECIMAL(40,15) NOT NULL, `asset2_balance` DECIMAL(40,15) NOT NULL, `lp_token_balance` DECIMAL(40,15) NOT NULL, `daily_swap_volume_usd` DECIMAL(30,6) DEFAULT NULL, -- In a normalized currency like USD `daily_fees_usd` DECIMAL(30,6) DEFAULT NULL, -- In a normalized currency like USD `total_liquidity_usd` DECIMAL(30,6) DEFAULT NULL, -- TVL `calculated_apy` DECIMAL(10,4) DEFAULT NULL, -- The final APY percentage PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_date` (`pool_id`, `snapshot_date`), FOREIGN KEY (`pool_id`) REFERENCES `amm_pools`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* for tracking visits to my websites, referenced in header.php */ CREATE TABLE `website_visits` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `site_identifier` VARCHAR(255) DEFAULT NULL, `page_url` VARCHAR(2083) NOT NULL, `visit_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `ip_address` VARCHAR(45) DEFAULT NULL, `user_agent` TEXT DEFAULT NULL, `referrer_url` VARCHAR(2083) DEFAULT NULL, PRIMARY KEY (`id`), INDEX `idx_visit_timestamp` (`visit_timestamp`), INDEX `idx_site_page` (`site_identifier`, `page_url`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* Table: ledgers Purpose: This table stores information about each ledger in the XRP Ledger (XRPL). Each ledger represents a snapshot of the XRPL's state at a specific point in time, including all transactions and account states up to the ledger's close time. Structure: - id: Unique identifier for each ledger record in the database. - ledger_index: The unique sequence number of the ledger in the XRPL. - ledger_hash: The hash of the ledger, uniquely identifying its contents. - account_hash: The hash of the account state data in the ledger. - close_flags: Flags indicating special conditions about the ledger's close. - close_time: The time the ledger was closed, in Ripple's epoch time (seconds since January 1, 2000). - close_time_human: A human-readable version of the close time. - close_time_resolution: The resolution (in seconds) of the close time. - close_time_iso: The close time in ISO 8601 format. - parent_close_time: The close time of the parent ledger. - parent_hash: The hash of the parent ledger. - total_coins: The total number of XRP drops in existence at the time of the ledger. - transaction_hash: The hash of the transactions in the ledger. - closed: Indicates whether the ledger is closed (1 for true, 0 for false). - validated: Indicates whether the ledger has been validated by the network (1 for true, 0 for false). Key Fields: - id: Primary key for this table. - ledger_index: Unique ledger sequence number. - ledger_hash: Unique hash of the ledger. - close_time: Timestamp of when the ledger was closed. - parent_hash: Hash of the parent ledger. - validated: Validation status of the ledger. */ CREATE TABLE ledgers ( id INT AUTO_INCREMENT, ledger_index INT NOT NULL, ledger_hash VARCHAR(64) NOT NULL, account_hash VARCHAR(64) COMMENT 'Hash of the account state data', close_flags INT COMMENT 'Flags indicating special close conditions', close_time INT UNSIGNED COMMENT 'Ledger close time in Ripple epoch (seconds since 2000-01-01)', close_time_human VARCHAR(34) COMMENT 'Human-readable close time', close_time_resolution INT COMMENT 'Resolution of close time in seconds', close_time_iso VARCHAR(20) COMMENT 'Close time in ISO 8601 format', parent_close_time INT COMMENT 'Close time of the parent ledger', parent_hash VARCHAR(64) COMMENT 'Hash of the parent ledger', total_coins VARCHAR(20) COMMENT 'Total XRP drops in existence', transaction_hash VARCHAR(64) COMMENT 'Hash of the transactions in the ledger', closed TINYINT COMMENT 'Indicates if the ledger is closed (1 for true, 0 for false)', validated TINYINT COMMENT 'Indicates if the ledger is validated (1 for true, 0 for false)', PRIMARY KEY (id, close_time), UNIQUE KEY uk_ledger_index_close_time (ledger_index, close_time), UNIQUE KEY uk_ledger_hash_close_time (ledger_hash, close_time), INDEX idx_ledgers_parent_hash (parent_hash) ) COMMENT = 'Stores information about each ledger in the XRP Ledger' ENGINE=InnoDB; /* Table: transactions Purpose: This table serves as the base table for all XRPL transactions, storing common attributes shared across all transaction types. It acts as a central reference point linked to specific transaction detail tables and the ledgers table. Structure: - id: Unique identifier for each transaction. - ledger_id: Foreign key referencing the ledgers table (id). - hash: Unique transaction hash. - Account: Initiating account address. - TransactionType: Type of transaction (e.g., Payment, OfferCreate). - Fee: Transaction fee in drops. - Sequence: Transaction sequence number. - SigningPubKey: Public key used to sign the transaction. - TxnSignature: Transaction signature. - TransactionIndex: Position of the transaction within its ledger. - TransactionResult: Result code indicating success or failure (e.g., tesSUCCESS). - date: Ledger close time (Ripple timestamp). - validated: Indicates if the transaction is in a validated ledger (0 or 1). Key Fields: - id: Primary key for this table. - ledger_id: Foreign key linking to the ledgers table. - hash: Unique identifier for the transaction, also a foreign key target. - Account: Account initiating the transaction. - TransactionType: Defines the transaction’s purpose. - Fee: Cost of the transaction. - Sequence: Ensures transaction order. - SigningPubKey: Verifies the signer. - TxnSignature: Validates the transaction. - TransactionIndex: Order within the ledger. - TransactionResult: Outcome of the transaction. - date: Timestamp of the transaction. - validated: Validation status. */ CREATE TABLE transactions ( id BIGINT AUTO_INCREMENT, ledger_id INT NOT NULL COMMENT 'Foreign key referencing the ledgers table', hash CHAR(64) NOT NULL COMMENT 'Unique transaction hash', Account VARCHAR(35) NOT NULL COMMENT 'Initiating account address', TransactionType VARCHAR(20) NOT NULL COMMENT 'Type of transaction (e.g., Payment, OfferCreate)', Fee BIGINT NOT NULL COMMENT 'Transaction fee in drops', Sequence INT UNSIGNED NOT NULL COMMENT 'Transaction sequence number', SigningPubKey VARCHAR(66) NOT NULL COMMENT 'Public key used to sign the transaction', TxnSignature VARCHAR(128) NOT NULL COMMENT 'Transaction signature', TransactionIndex INT UNSIGNED COMMENT 'Position of the transaction within its ledger', TransactionResult VARCHAR(20) COMMENT 'Result code indicating success or failure (e.g., tesSUCCESS)', date INT UNSIGNED COMMENT 'Ledger close time (Ripple timestamp)', -- Partitioning column validated TINYINT COMMENT 'Indicates if the transaction is in a validated ledger (0 or 1)', LastLedgerSequence BIGINT UNSIGNED DEFAULT NULL COMMENT 'Last ledger sequence for transaction validity', PRIMARY KEY (id, date), UNIQUE KEY uk_hash_date (hash, date), FOREIGN KEY (ledger_id, date) REFERENCES ledgers(id, close_time) ON DELETE CASCADE, INDEX idx_transactions_ledger_id (ledger_id), INDEX idx_transactions_transaction_type (TransactionType), INDEX idx_transactions_date_type (date, TransactionType), INDEX idx_transactions_account (Account), INDEX idx_t_result (TransactionResult) ) COMMENT = 'Base table for all XRPL transactions, storing common attributes' ENGINE=InnoDB; /* Table: affected_nodes_base Purpose: This table serves as the foundational table for tracking metadata about nodes affected by transactions in the XRP Ledger (XRPL). It stores common attributes applicable to all types of ledger entries impacted by a transaction, acting as a central hub that links to more specific tables based on the ledger entry type. Structure: - base_id: A unique identifier for each affected node entry, auto-incremented for simplicity. - transaction_hash: The hash of the transaction that affected this node, serving as a link to the transactions table. - node_index: The position of the node within the transaction's AffectedNodes array. - action: Specifies the type of change made to the node (e.g., creation, modification, or deletion). - LedgerIndex: The unique identifier of the ledger entry affected by the transaction. - LedgerEntryType: Indicates the type of ledger entry (e.g., AccountRoot, Offer), guiding which specific table contains additional details. Key Fields: - base_id: Primary key, uniquely identifying each record. - transaction_hash: Foreign key linking to the transactions table, ensuring referential integrity. - node_index: Identifies the node's position in the transaction's AffectedNodes array. - action: Enum value ('CreatedNode', 'ModifiedNode', 'DeletedNode') indicating the node's change type. - LedgerIndex: Unique identifier for the ledger entry, critical for tracking. - LedgerEntryType: Determines the specific table for detailed data retrieval. */ CREATE TABLE affected_nodes_base ( base_id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key linking to the transactions table', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', node_index TINYINT UNSIGNED NOT NULL COMMENT 'Position of the node within the transaction''s AffectedNodes array', action ENUM('CreatedNode', 'ModifiedNode', 'DeletedNode') NOT NULL COMMENT 'Type of change made to the node', LedgerIndex CHAR(64) NOT NULL COMMENT 'Unique identifier of the ledger entry affected', LedgerEntryType VARCHAR(20) NOT NULL COMMENT 'Type of ledger entry (e.g., AccountRoot, Offer)', PRIMARY KEY (base_id, transaction_date), UNIQUE KEY uk_transaction_node_date (transaction_id, node_index, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_affected_nodes_base_transaction_id (transaction_id), INDEX idx_affected_nodes_base_ledger_entry_type (LedgerEntryType) ) COMMENT = 'Tracks metadata about nodes affected by transactions' ENGINE=InnoDB; /* Table: top_wallets_history Purpose: This table stores historical balance data for XRP wallets holding over 5 million XRP, updated each time transactions.php is run, to track changes in holdings, wallet types (Exchange, Ripple, or Other), total XRP (all wallets and exchanges), and cached metadata (owner count, flags, transaction count) for graphing purposes and to reduce XRPSCAN API calls. Structure: - id: Unique identifier for each entry. - wallet_address: The XRP account address of the wallet, or 'TOTALS' for aggregate records. - balance_drops: The wallet's balance in drops (1 XRP = 1,000,000 drops), or 0 for totals. - name: The associated name of the wallet (e.g., from XRPSCAN API), or 'Totals' for aggregate records. - wallet_type: The type of wallet (Exchange, Ripple, Other), or 'Totals' for aggregate records. - owner_count: Number of ledger objects owned (trust lines, offers, etc.), cached from XRPSCAN API. - flags: Account flags, cached from XRPSCAN API. - tx_count: Number of recent transactions, cached from XRPSCAN API. - total_xrp_all: Total XRP (in drops) held by all wallets with over 5 million XRP. - total_xrp_exchanges: Total XRP (in drops) held by exchange wallets with over 5 million XRP. - recorded_at: Timestamp when the data was recorded. Key Fields: - id: Primary key for this table. - wallet_address: The XRP account address or 'TOTALS' for aggregates. - balance_drops: Balance in drops for individual wallets, 0 for totals. - name: Name associated with the wallet, or 'Totals' for aggregate records. - wallet_type: Indicates whether the wallet is an Exchange, Ripple, Other, or Totals. - owner_count: Cached owner count for exchange detection. - flags: Cached flags for exchange detection. - tx_count: Cached transaction count for exchange detection. - total_xrp_all: Total balance in drops for all wallets over 5M XRP. - total_xrp_exchanges: Total balance in drops for exchange wallets over 5M XRP. - recorded_at: When the data was captured. */ CREATE TABLE top_wallets_history ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each entry', wallet_address VARCHAR(35) NOT NULL COMMENT 'XRP account address of the wallet or "TOTALS" for aggregates', balance_drops BIGINT NOT NULL COMMENT 'Wallet balance in drops (1 XRP = 1,000,000 drops), 0 for totals', name VARCHAR(100) DEFAULT 'Unknown' COMMENT 'Associated name of the wallet, or "Totals" for aggregates', wallet_type ENUM('Exchange', 'Ripple', 'Other', 'Totals') NOT NULL DEFAULT 'Other' COMMENT 'Type of wallet: Exchange, Ripple, Other, or Totals', owner_count BIGINT DEFAULT NULL COMMENT 'Number of ledger objects owned, cached from XRPSCAN API', flags BIGINT DEFAULT NULL COMMENT 'Account flags, cached from XRPSCAN API', tx_count INT DEFAULT NULL COMMENT 'Number of recent transactions, cached from XRPSCAN API', total_xrp_all BIGINT NOT NULL DEFAULT 0 COMMENT 'Total XRP in drops for all wallets over 5M XRP', total_xrp_exchanges BIGINT NOT NULL DEFAULT 0 COMMENT 'Total XRP in drops for exchange wallets over 5M XRP', recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp when the balance was recorded', CONSTRAINT uk_wallet_address_recorded_at UNIQUE (wallet_address, recorded_at), INDEX idx_recorded_at (recorded_at), INDEX idx_wallet_address (wallet_address), INDEX idx_wallet_type (wallet_type) ) COMMENT = 'Stores historical balance data, wallet types, totals, and cached metadata for XRP wallets holding over 5 million XRP' ENGINE=InnoDB; CREATE TABLE `issuer_info_cache` ( `issuer_address` VARCHAR(40) NOT NULL COMMENT 'The XRPL issuer address', `domain_hex` VARCHAR(512) DEFAULT NULL COMMENT 'Raw Domain field from account_info (hex)', `domain_decoded` VARCHAR(255) DEFAULT NULL COMMENT 'Decoded domain name', `toml_url` VARCHAR(2083) DEFAULT NULL COMMENT 'Full URL to the xrpl.toml file', `toml_last_fetched` TIMESTAMP NULL DEFAULT NULL COMMENT 'When the TOML file was last successfully fetched', `toml_fetch_error` TEXT DEFAULT NULL COMMENT 'Last error message if TOML fetch failed', `failed_toml_fetch_attempts` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of consecutive failed attempts to fetch TOML', `account_info_last_fetched` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When account_info was last fetched for this issuer', `currency_info_json` TEXT DEFAULT NULL COMMENT 'JSON string of currency/token info from TOML for this issuer (e.g., [[TOKENS]])', PRIMARY KEY (`issuer_address`), INDEX `idx_domain_decoded` (`domain_decoded`(191)), -- Index prefix of 191 chars INDEX `idx_toml_url` (`toml_url`(191)), -- Index prefix of 191 chars INDEX `idx_toml_last_fetched` (`toml_last_fetched`), INDEX `idx_account_info_last_fetched` (`account_info_last_fetched`) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ENGINE=InnoDB; /* Table: ripple_escrow_accounts Purpose: This table stores information about Ripple-controlled accounts that hold XRP in escrow on the XRP Ledger (XRPL). It tracks the total number of escrow objects, the total amount of XRP (in drops), and detailed escrow data for each account identified as belonging to Ripple via the XRPSCAN API. Structure: - id: Unique identifier for each record in the database. - account_address: The XRPL account address holding the escrows. - total_escrows: The number of active escrow objects for the account. - total_drops: The total amount of XRP held in escrow (in drops). - escrow_details: A JSON string containing detailed information about each escrow object (amount, destination, conditions, etc.). - updated_at: Timestamp of when the record was last updated. Key Fields: - id: Primary key for this table. - account_address: Unique XRPL account address (used as a unique key). - total_escrows: Count of escrow objects. - total_drops: Total XRP in escrow (in drops). - escrow_details: JSON-encoded array of escrow objects with their properties. - updated_at: Last update timestamp for freshness tracking. */ CREATE TABLE ripple_escrow_accounts ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each record', account_address VARCHAR(35) UNIQUE NOT NULL COMMENT 'XRPL account address holding escrows', total_escrows INT NOT NULL COMMENT 'Number of active escrow objects', total_drops BIGINT NOT NULL COMMENT 'Total XRP held in escrow (in drops)', escrow_details TEXT COMMENT 'JSON-encoded details of each escrow object', updated_at DATETIME NOT NULL COMMENT 'Timestamp of last update' ) COMMENT 'Stores data about Ripple-controlled escrow accounts on the XRP Ledger' ENGINE=InnoDB; -- Additional indexes for performance CREATE INDEX idx_ripple_escrow_accounts_updated_at ON ripple_escrow_accounts (updated_at); /* Table: total_escrowed Purpose: This table stores the aggregate total of XRP held in escrow across all Ripple-controlled accounts, providing a quick reference for the grand total and the number of accounts involved. Structure: - id: Unique identifier for the record (single row expected, updated via ON DUPLICATE KEY UPDATE). - total_drops: The total amount of XRP held in escrow across all Ripple accounts (in drops). - account_count: The number of Ripple accounts with active escrows. - updated_at: Timestamp of when the record was last updated. Key Fields: - id: Primary key (single record). - total_drops: Total XRP in escrow (in drops). - account_count: Number of accounts with escrows. - updated_at: Last update timestamp. */ CREATE TABLE total_escrowed ( id INT PRIMARY KEY DEFAULT 1 COMMENT 'Unique identifier (single record)', total_drops BIGINT NOT NULL COMMENT 'Total XRP held in escrow across all Ripple accounts (in drops)', account_count INT NOT NULL COMMENT 'Number of Ripple accounts with active escrows', updated_at DATETIME NOT NULL COMMENT 'Timestamp of last update' ) COMMENT 'Stores the total XRP escrowed by Ripple accounts' ENGINE=InnoDB; -- Initialize with a single row if needed INSERT INTO total_escrowed (id, total_drops, account_count, updated_at) VALUES (1, 0, 0, NOW()) ON DUPLICATE KEY UPDATE total_drops = 0, account_count = 0, updated_at = NOW(); CREATE TABLE access_records ( id INT AUTO_INCREMENT PRIMARY KEY, tx_hash VARCHAR(64) UNIQUE NOT NULL, wallet_address VARCHAR(35), amount DECIMAL(10,2), access_start DATETIME NOT NULL, access_end DATETIME NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS toml_cache ( domain VARCHAR(255) CHARACTER SET latin1 PRIMARY KEY, parsed_data TEXT, fetched_at DATETIME, expires_at DATETIME ) ENGINE=InnoDB; CREATE TABLE xrpl_tokens ( id INT AUTO_INCREMENT PRIMARY KEY, currency_code VARCHAR(40) NOT NULL COMMENT 'Token currency code (standard or hex)', issuer_address VARCHAR(35) NOT NULL COMMENT 'Issuing account address', issuer_domain VARCHAR(256) DEFAULT NULL COMMENT 'Domain associated with issuer account (first from issuers array)', token_name VARCHAR(100) DEFAULT NULL COMMENT 'Token name from tokens array', description TEXT DEFAULT NULL COMMENT 'Token description from tokens array', icon VARCHAR(256) DEFAULT NULL COMMENT 'Token icon URL from tokens array', weblinks JSON DEFAULT NULL COMMENT 'Array of weblinks from TOKENS.WEBLINKS in JSON format', toml_file_url VARCHAR(256) DEFAULT NULL COMMENT 'URL link to the toml file', fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the data was fetched', UNIQUE KEY unique_token (currency_code, issuer_address) COMMENT 'Prevent duplicate tokens' ) COMMENT='Stores discovered XRPL tokens and their details from TOML files' ENGINE=InnoDB; CREATE TABLE large_transactions ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL, hash CHAR(64) NOT NULL, date INT UNSIGNED NOT NULL, -- Partitioning column (Ripple Epoch from transactions.date) ledger_index INT NOT NULL, amount_value DECIMAL(30,10) NOT NULL, amount_currency VARCHAR(40) NOT NULL, amount_issuer VARCHAR(35), xrp_equivalent DECIMAL(20,6) NOT NULL, PRIMARY KEY (id, date), UNIQUE KEY uk_transaction_id_date (transaction_id, date), FOREIGN KEY (transaction_id, date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_xrp_equivalent (xrp_equivalent) ) COMMENT = 'Stores large transactions identified from the transactions table' ENGINE=InnoDB; CREATE TABLE processing_state ( id INT PRIMARY KEY DEFAULT 1, last_ledger_index INT NOT NULL ) COMMENT 'Stores the last processed ledger index for transaction processing' ENGINE=InnoDB; -- Insert the initial single row INSERT INTO processing_state (id, last_ledger_index) VALUES (1, 0) ON DUPLICATE KEY UPDATE last_ledger_index = last_ledger_index; /* Table: doppler_wallet_balances Purpose: This table stores general staking information from Doppler.finance's XRPfi protocol and the user's current XRP balance deposited on the platform. It captures a snapshot of staking metrics (e.g., total staked, APR) and user-specific deposit data for tracking yield activities over time. Structure: - id: Unique identifier for each record. - wallet_address: The XRPL address of the wallet (null for general staking records). - currency: The currency code (e.g., 'XRP', null for general staking records). - deposit_balance: The user's deposited XRP balance on Doppler.finance (null for general staking records). - total_staked: Total XRP staked across all users in the XRPfi protocol. - staking_cap: Maximum staking limit for the protocol. - boost_stage: Name of the current boost stage (e.g., 'x2'). - boost_point_ratio: Reward multiplier for the boost stage. - token_price: Price of XRP in USD. - fireblocks_treasury_balance: XRP balance in the Fireblocks treasury wallet. - apr: Annual percentage rate for staking. - pending_withdrawal_amount: Total XRP pending withdrawal across users. - timestamp: Time when the data was recorded (in UTC). - record_date: Generated date of the record (in UTC). Key Fields: - id: Primary key for this table. - wallet_address: Identifies user-specific records (null for general staking data). - currency: Specifies the currency for user balances (null for general staking). - deposit_balance: User’s deposited balance on Doppler.finance. - total_staked: Key metric for platform-wide staking activity. - UNIQUE KEY: Ensures one user balance record per wallet, currency, and timestamp. */ CREATE TABLE doppler_wallet_balances ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each record', wallet_address VARCHAR(35) COMMENT 'XRPL address of the wallet (null for general staking records)', currency VARCHAR(40) COMMENT 'Currency code of the balance (null for general staking records)', deposit_balance DECIMAL(20, 6) COMMENT 'User deposited XRP balance on Doppler.finance', total_staked DECIMAL(20, 6) COMMENT 'Total XRP staked across all users', staking_cap DECIMAL(20, 6) COMMENT 'Maximum staking limit for the protocol', boost_stage VARCHAR(50) COMMENT 'Name of the current boost stage', boost_point_ratio DECIMAL(10, 6) COMMENT 'Reward multiplier for the boost stage', token_price DECIMAL(20, 6) COMMENT 'Price of XRP in USD', fireblocks_treasury_balance DECIMAL(20, 6) COMMENT 'XRP balance in Fireblocks treasury wallet', apr DECIMAL(10, 6) COMMENT 'Annual percentage rate for staking', pending_withdrawal_amount DECIMAL(20, 6) COMMENT 'Total XRP pending withdrawal', timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when the data was recorded (in UTC)', record_date DATE GENERATED ALWAYS AS (DATE(timestamp)) STORED COMMENT 'Date of the record (in UTC)', UNIQUE KEY unique_user_balance (wallet_address, currency, timestamp) ) COMMENT 'Stores general staking info and user balances from Doppler.finance XRPfi protocol' ENGINE=InnoDB; -- Additional indexes for performance CREATE INDEX idx_wallet_address ON doppler_wallet_balances (wallet_address); CREATE INDEX idx_record_date ON doppler_wallet_balances (record_date); /* Table: affected_nodes_oracle Purpose: This table stores detailed information about Oracle ledger entries affected by transactions in the XRP Ledger (XRPL). Oracles provide external data, such as price feeds, to the ledger, and this table tracks their state for CreatedNode, ModifiedNode, and DeletedNode actions. Structure: - id: Unique identifier for each entry in this table. - base_id: Foreign key linking to the affected_nodes_base table for general metadata. - Node_PreviousTxnID: Transaction ID of the last modification to this node (from node metadata). - Node_PreviousTxnLgrSeq: Ledger sequence number of the last modification (from node metadata). - New_*: Fields capturing the initial state of the Oracle for CreatedNode actions. - Final_*: Fields capturing the Oracle's state after the transaction (for ModifiedNode and DeletedNode). - Previous_*: Fields capturing the Oracle's state before the transaction (for ModifiedNode and DeletedNode). */ CREATE TABLE affected_nodes_oracle ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key linking to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Transaction ID of the last modification to this node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Flags INT UNSIGNED COMMENT 'Initial flags for new Oracle nodes', New_Owner VARCHAR(35) COMMENT 'Initial owner account for new Oracle nodes', New_OracleDocumentID INT UNSIGNED COMMENT 'Initial Oracle document ID for new Oracle nodes', New_Provider VARCHAR(256) COMMENT 'Initial provider for new Oracle nodes', New_URI VARCHAR(256) COMMENT 'Initial URI for new Oracle nodes', New_AssetClass VARCHAR(16) COMMENT 'Initial asset class for new Oracle nodes', New_PriceData JSON COMMENT 'Initial price data for new Oracle nodes', New_LastUpdateTime INT UNSIGNED COMMENT 'Initial last update time for new Oracle nodes', New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes', Final_Flags INT UNSIGNED COMMENT 'Flags after the transaction', Final_Owner VARCHAR(35) COMMENT 'Account that owns the Oracle after the transaction', Final_OracleDocumentID VARCHAR(64) COMMENT 'Unique identifier for the Oracle document after the transaction', Final_Provider VARCHAR(256) COMMENT 'Provider of the oracle data after the transaction', Final_URI VARCHAR(256) COMMENT 'URI pointing to additional data after the transaction', Final_PriceData JSON COMMENT 'JSON array of price data points after the transaction', Final_LastUpdateTime INT UNSIGNED COMMENT 'Timestamp of the last update after the transaction', Final_AssetClass VARCHAR(64) COMMENT 'Asset class after the transaction', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_Owner VARCHAR(35) COMMENT 'Account that owned the Oracle before the transaction', Previous_OracleDocumentID VARCHAR(64) COMMENT 'Oracle document ID before the transaction', Previous_Provider VARCHAR(256) COMMENT 'Provider of the oracle data before the transaction', Previous_URI VARCHAR(256) COMMENT 'URI pointing to additional data before the transaction', Previous_PriceData JSON COMMENT 'JSON array of price data before the transaction', Previous_LastUpdateTime INT UNSIGNED COMMENT 'Timestamp of the last update before the transaction', Previous_AssetClass VARCHAR(64) COMMENT 'Asset class before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores detailed information about Oracle ledger entries affected by transactions' ENGINE=InnoDB; CREATE TABLE affected_nodes_accountroot ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key linking to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Transaction ID of the last modification to this node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account address for new AccountRoot nodes', New_Balance BIGINT COMMENT 'Initial account balance in drops for new AccountRoot nodes', New_Sequence INT UNSIGNED COMMENT 'Initial account sequence number for new AccountRoot nodes', New_AMMID CHAR(64) COMMENT 'AMM ID for new AccountRoot nodes', New_Flags INT UNSIGNED COMMENT 'Flags for new AccountRoot nodes', New_OwnerCount INT UNSIGNED COMMENT 'Owner count for new AccountRoot nodes', Final_Account VARCHAR(35) COMMENT 'Account address after the transaction', Final_Balance BIGINT COMMENT 'Account balance in drops after the transaction', Final_Flags INT UNSIGNED COMMENT 'Account flags after the transaction', Final_OwnerCount INT UNSIGNED COMMENT 'Number of owned objects after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_Sequence INT UNSIGNED COMMENT 'Account sequence number after the transaction', Final_TicketCount INT UNSIGNED COMMENT 'Number of tickets after the transaction', Final_AccountTxnID CHAR(64) COMMENT 'Account transaction ID after the transaction', Final_Domain VARCHAR(256) COMMENT 'Account domain after the transaction', Final_EmailHash CHAR(32) COMMENT 'Account email hash after the transaction', Final_MessageKey VARCHAR(66) COMMENT 'Account message key after the transaction', Final_TransferRate INT UNSIGNED COMMENT 'Transfer rate after the transaction', Final_TickSize TINYINT UNSIGNED COMMENT 'Tick size after the transaction', Final_NFTokenMinter VARCHAR(35) COMMENT 'NFToken minter after the transaction', Final_FirstNFTokenSequence INT UNSIGNED COMMENT 'First NFToken sequence after the transaction', Final_MintedNFTokens INT UNSIGNED COMMENT 'Number of minted NFTokens after the transaction', Final_BurnedNFTokens INT UNSIGNED COMMENT 'Number of burned NFTokens after the transaction', Final_AMMID CHAR(64) COMMENT 'AMM ID after the transaction', Final_WalletLocator CHAR(64) COMMENT 'Wallet locator after the transaction', Final_WalletSize INT UNSIGNED COMMENT 'Wallet size after the transaction', Final_RegularKey VARCHAR(35) COMMENT 'Regular key after the transaction', Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account address before the transaction', Previous_Balance BIGINT COMMENT 'Account balance in drops before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Account flags before the transaction', Previous_OwnerCount INT UNSIGNED COMMENT 'Number of owned objects before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_Sequence INT UNSIGNED COMMENT 'Account sequence number before the transaction', Previous_TicketCount INT UNSIGNED COMMENT 'Number of tickets before the transaction', Previous_AccountTxnID CHAR(64) COMMENT 'Account transaction ID before the transaction', Previous_Domain VARCHAR(256) COMMENT 'Account domain before the transaction', Previous_EmailHash CHAR(32) COMMENT 'Account email hash before the transaction', Previous_MessageKey VARCHAR(66) COMMENT 'Account message key before the transaction', Previous_TransferRate INT UNSIGNED COMMENT 'Transfer rate before the transaction', Previous_TickSize TINYINT UNSIGNED COMMENT 'Tick size before the transaction', Previous_NFTokenMinter VARCHAR(35) COMMENT 'NFToken minter before the transaction', Previous_FirstNFTokenSequence INT UNSIGNED COMMENT 'First NFToken sequence before the transaction', Previous_MintedNFTokens INT UNSIGNED COMMENT 'Number of minted NFTokens before the transaction', Previous_BurnedNFTokens INT UNSIGNED COMMENT 'Number of burned NFTokens before the transaction', Previous_WalletLocator CHAR(64) COMMENT 'Wallet locator before the transaction', Previous_WalletSize INT UNSIGNED COMMENT 'Wallet size before the transaction', Previous_RegularKey VARCHAR(35) COMMENT 'Regular key before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE, INDEX idx_affected_nodes_accountroot_final_balance (Final_Balance), INDEX idx_affected_nodes_accountroot_previous_balance (Previous_Balance), INDEX idx_affected_nodes_accountroot_base_id_final_balance (base_id, Final_Balance) ) COMMENT = 'Stores detailed information about AccountRoot ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_amendments Purpose: This table tracks changes to Amendments ledger entries, which manage network-wide feature updates in the XRPL. It records the enabled amendments and those with majority support, including their states before and after a transaction. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base for transaction context. - Node_PreviousTxnID: Last transaction ID that modified this node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_Amendments: JSON array of enabled amendments post-transaction. - Final_Majorities: JSON array of amendments with majority support post-transaction. - Previous_Amendments: Previous state of enabled amendments. - Previous_Majorities: Previous state of amendments with majority support. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_Amendments: List of amendments activated after the transaction. - Final_Majorities: List of amendments gaining majority support. - Previous_Amendments: Pre-transaction state of amendments. - Previous_Majorities: Pre-transaction state of majorities. */ CREATE TABLE affected_nodes_amendments ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID that modified this node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', Final_Amendments JSON COMMENT 'JSON array of enabled amendments post-transaction', Final_Majorities JSON COMMENT 'JSON array of amendments with majority support post-transaction', Previous_Amendments JSON COMMENT 'JSON array of enabled amendments before the transaction', Previous_Majorities JSON COMMENT 'JSON array of amendments with majority support before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to Amendments ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_check Purpose: This table stores details about Check ledger entries affected by transactions. Checks are deferred payment instruments in XRPL, and this table captures sender, recipient, and condition details before and after changes, including new checks created. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_*: Fields for newly created checks (CreatedNode). - Final_*: Final state fields after the transaction. - Previous_*: Previous state fields for modified checks. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - New_Account: Creator of a new check. - Final_Account: Creator of the check after modification. - Final_Destination: Recipient of the check. - Final_SendMax_Value: Maximum amount that can be cashed. - Final_Sequence: Sequence number of the check. - Final_Expiration: Check expiration time. - Previous_*: Pre-transaction state for comparison. */ CREATE TABLE affected_nodes_check ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new DirectoryNode', New_Account VARCHAR(35) COMMENT 'Account that created the check for CreatedNode', New_Destination VARCHAR(35) COMMENT 'Destination account for CreatedNode', New_SendMax DECIMAL(30,10) COMMENT 'SendMax for CreatedNode', New_SendMax_Value DECIMAL(30,10) COMMENT 'SendMax value for CreatedNode', New_SendMax_Currency VARCHAR(40) COMMENT 'SendMax currency for CreatedNode', New_SendMax_Issuer VARCHAR(35) COMMENT 'SendMax issuer for CreatedNode', New_Sequence INT UNSIGNED COMMENT 'Sequence for CreatedNode', New_SourceTag INT UNSIGNED COMMENT 'Source tag for CreatedNode', New_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier for new nodes', New_Expiration INT UNSIGNED COMMENT 'Expiration time for new Offer nodes', New_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction', New_InvoiceID CHAR(64) COMMENT 'Invoice ID associated with the check', Final_Account VARCHAR(35) COMMENT 'Account that created the check', Final_Destination VARCHAR(35) COMMENT 'Account eligible to cash the check', Final_Flags INT UNSIGNED COMMENT 'Flags set on the check after the transaction', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_SendMax DECIMAL(30,10) COMMENT 'Maximum amount value that can be cashed', Final_SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount value that can be cashed', Final_SendMax_Currency VARCHAR(40) COMMENT 'Currency of the maximum amount', Final_SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of the maximum amount', Final_Sequence INT UNSIGNED COMMENT 'Sequence number of the check', Final_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier after the transaction', Final_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction', Final_Expiration INT UNSIGNED COMMENT 'Check expiration time', Final_InvoiceID CHAR(64) COMMENT 'Invoice ID associated with the check', Final_SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account that created the check before the transaction', Previous_Destination VARCHAR(35) COMMENT 'Account eligible to cash the check before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags set on the check before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount value before the transaction', Previous_SendMax_Currency VARCHAR(40) COMMENT 'Currency of the maximum amount before the transaction', Previous_SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of the maximum amount before the transaction', Previous_Sequence INT UNSIGNED COMMENT 'Sequence number before the transaction', Previous_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier before the transaction', Previous_DestinationTag INT UNSIGNED COMMENT 'Destination tag before the transaction', Previous_Expiration INT UNSIGNED COMMENT 'Expiration time before the transaction', Previous_InvoiceID CHAR(64) COMMENT 'Invoice ID before the transaction', Previous_SourceTag INT UNSIGNED COMMENT 'Source tag before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about Check ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_depositpreauth Purpose: This table records changes to DepositPreauth ledger entries, which preauthorize deposits to an account. It tracks the granting account and the preauthorized account before and after a transaction. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_Account: Account granting the preauthorization. - Final_Authorize: Account preauthorized for deposits. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_Account: Account granting preauthorization. - Final_Authorize: Preauthorized account. - Final_Flags: Flags set on the preauthorization. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_depositpreauth ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account that created the check for CreatedNode', New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes', New_Authorize VARCHAR(35) COMMENT 'Account preauthorized for deposits', Final_Account VARCHAR(35) COMMENT 'Account granting the preauthorization', Final_Authorize VARCHAR(35) COMMENT 'Account preauthorized for deposits', Final_Flags INT UNSIGNED COMMENT 'Flags set on the preauthorization', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account granting preauthorization before the transaction', Previous_Authorize VARCHAR(35) COMMENT 'Account preauthorized before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Records changes to DepositPreauth ledger entries' ENGINE=InnoDB; CREATE TABLE affected_nodes_directorynode ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_ExchangeRate VARCHAR(16) COMMENT 'Exchange rate for new DirectoryNode', New_RootIndex CHAR(64) COMMENT 'Root index for new DirectoryNode', New_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets for new DirectoryNode', New_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency for new DirectoryNode', New_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays for new DirectoryNode', New_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency for new DirectoryNode', New_Flags INT UNSIGNED COMMENT 'Flags for new DirectoryNode', New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new DirectoryNode', New_IndexPrevious INT UNSIGNED COMMENT 'Previous index for new DirectoryNode', New_Owner VARCHAR(35) COMMENT 'Owner of the new DirectoryNode', New_NFTokenID CHAR(64) COMMENT 'ID of the NFT involved for new nodes', New_Destination VARCHAR(35) COMMENT 'Destination account for new Offer nodes', New_IndexNext INT UNSIGNED COMMENT 'Next index for new DirectoryNode', New_SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount to send value', New_SendMax_Currency VARCHAR(40) COMMENT 'Currency of SendMax', New_SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of SendMax', Final_Flags INT UNSIGNED COMMENT 'Flags set on the directory node', Final_Indexes JSON COMMENT 'JSON array of ledger entry indexes in the directory', Final_Owner VARCHAR(35) COMMENT 'Owner of the directory node', Final_RootIndex CHAR(64) COMMENT 'Root index of the directory', Final_NFTokenID CHAR(64) COMMENT 'NFToken ID after the transaction', Final_ExchangeRate VARCHAR(16) COMMENT 'Exchange rate after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_IndexNext INT UNSIGNED COMMENT 'Next index in the directory after the transaction', Final_IndexPrevious INT UNSIGNED COMMENT 'Previous index in the directory after the transaction', Final_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount after modification (XRP or currency object)', Final_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount after modification (XRP or currency object)', Final_TakerPays_Value DECIMAL(30,10) COMMENT 'Amount the taker pays after modification', Final_TakerPays_Currency VARCHAR(40) COMMENT 'Currency the taker pays after modification', Final_TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount after modification', Final_TakerGets_Value DECIMAL(30,10) COMMENT 'Amount the taker gets after modification', Final_TakerGets_Currency VARCHAR(40) COMMENT 'Currency the taker gets after modification', Final_TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount after modification', Final_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets after the transaction', Final_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays after the transaction', Final_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency after the transaction', Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_Indexes JSON COMMENT 'JSON array of indexes before the transaction', Previous_Owner VARCHAR(35) COMMENT 'Owner before the transaction', Previous_RootIndex CHAR(64) COMMENT 'Root index before the transaction', Previous_ExchangeRate VARCHAR(16) COMMENT 'Exchange rate before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_IndexNext INT UNSIGNED COMMENT 'Next index before the transaction', Previous_IndexPrevious INT UNSIGNED COMMENT 'Previous index before the transaction', Previous_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount before the transaction (e.g., XRP in drops)', Previous_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount before the transaction (e.g., XRP in drops)', Previous_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets before the transaction', Previous_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays before the transaction', Previous_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency before the transaction', Previous_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to DirectoryNode ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_escrow Purpose: This table stores details about Escrow ledger entries, which hold XRP until specific conditions or times are met. It captures escrow details like amount and conditions before and after transactions, including new escrow creations. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_*: Fields for newly created escrow nodes. - Final_*: Final state fields after the transaction. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - New_Account: Escrow creator for new nodes. - New_Destination: Escrow recipient for new nodes. - New_Amount: Escrowed XRP amount for new nodes. - New_FinishAfter: Finish time for new nodes. - Final_Account: Escrow creator. - Final_Destination: Escrow recipient. - Final_Amount: Escrowed XRP amount. - Final_Condition: Condition for fund release. - Final_CancelAfter: Time after which escrow can be canceled. - Final_FinishAfter: Time after which escrow can be finished. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_escrow ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account creating the escrow for new Escrow nodes', New_Amount BIGINT COMMENT 'Amount of XRP in escrow for new Escrow nodes', New_Destination VARCHAR(35) COMMENT 'Account receiving the escrowed funds for new Escrow nodes', New_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier for new nodes', New_FinishAfter INT UNSIGNED COMMENT 'Time after which escrow can be finished for new Escrow nodes', New_Condition VARCHAR(256) COMMENT 'Condition for fund release for new Escrow nodes', New_CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled for new Escrow nodes', New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes', New_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction', Final_Account VARCHAR(35) COMMENT 'Account creating the escrow', Final_Destination VARCHAR(35) COMMENT 'Account receiving the escrowed funds', Final_Amount BIGINT COMMENT 'Amount of XRP in escrow', Final_Condition VARCHAR(256) COMMENT 'Condition for fund release', Final_CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled', Final_FinishAfter INT UNSIGNED COMMENT 'Time after which escrow can be finished', Final_Flags INT UNSIGNED COMMENT 'Flags set on the escrow after the transaction', Final_SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', Final_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account creating the escrow before the transaction', Previous_Destination VARCHAR(35) COMMENT 'Account receiving funds before the transaction', Previous_Amount BIGINT COMMENT 'Amount in escrow before the transaction', Previous_Condition VARCHAR(256) COMMENT 'Condition before the transaction', Previous_CancelAfter INT UNSIGNED COMMENT 'Cancel time before the transaction', Previous_FinishAfter INT UNSIGNED COMMENT 'Finish time before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_SourceTag INT UNSIGNED COMMENT 'Source tag before the transaction', Previous_DestinationTag INT UNSIGNED COMMENT 'Destination tag before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction', Previous_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about Escrow ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_feesettings Purpose: This table tracks changes to FeeSettings ledger entries, which define transaction fee parameters in the XRPL. It records fee-related settings before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_BaseFee: Base transaction fee post-transaction. - Final_ReserveBase: Base reserve for accounts. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_BaseFee: Base fee for transactions. - Final_ReferenceFeeUnits: Reference fee units. - Final_ReserveBase: Base reserve amount. - Final_ReserveIncrement: Reserve increment per owned object. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_feesettings ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', Final_BaseFee BIGINT COMMENT 'Base transaction fee post-transaction', Final_ReferenceFeeUnits INT UNSIGNED COMMENT 'Reference fee units post-transaction', Final_ReserveBase BIGINT COMMENT 'Base reserve amount for accounts', Final_ReserveIncrement BIGINT COMMENT 'Reserve increment per owned object', Previous_BaseFee BIGINT COMMENT 'Base fee before the transaction', Previous_ReferenceFeeUnits INT UNSIGNED COMMENT 'Reference fee units before the transaction', Previous_ReserveBase BIGINT COMMENT 'Base reserve before the transaction', Previous_ReserveIncrement BIGINT COMMENT 'Reserve increment before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to FeeSettings ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_ledgerhashes Purpose: This table records changes to LedgerHashes ledger entries, which store hashes of previous ledgers for validation purposes. It tracks these hashes before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_Hashes: JSON array of previous ledger hashes. - Final_LastLedgerSequence: Last ledger sequence number. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_Hashes: List of previous ledger hashes. - Final_LastLedgerSequence: Sequence number of the last ledger. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_ledgerhashes ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', Final_Hashes JSON COMMENT 'JSON array of previous ledger hashes', Final_LastLedgerSequence INT UNSIGNED COMMENT 'Sequence number of the last ledger', Previous_Hashes JSON COMMENT 'JSON array of hashes before the transaction', Previous_LastLedgerSequence INT UNSIGNED COMMENT 'Last ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Records changes to LedgerHashes ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_negativeunl Purpose: This table tracks changes to NegativeUNL ledger entries, which list disabled validators in the XRPL network. It records validator status changes before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_ValidatorToDisable: Validator marked for disabling in CreatedNode actions. - Final_DisabledValidators: JSON array of disabled validators. - Final_ValidatorToDisable: Validator marked for disabling. - Final_ValidatorToReEnable: Validator marked for re-enabling. - Final_Flags: Flags after the transaction. - Previous_DisabledValidators: JSON array of disabled validators before the transaction. - Previous_ValidatorToDisable: Validator to disable before the transaction. - Previous_ValidatorToReEnable: Validator to re-enable before the transaction. - Previous_Flags: Flags before the transaction. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_DisabledValidators: List of currently disabled validators. - Final_ValidatorToDisable: Validator to disable. - Final_ValidatorToReEnable: Validator to re-enable. - Final_Flags: Flags after the transaction. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_negativeunl ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_ValidatorToDisable VARCHAR(40) COMMENT 'Validator marked for disabling in CreatedNode actions', New_ValidatorToReEnable VARCHAR(40) COMMENT 'Validator marked for re-enabling in CreatedNode actions', Final_DisabledValidators JSON COMMENT 'JSON array of disabled validators', Final_ValidatorToDisable VARCHAR(40) COMMENT 'Validator marked for disabling', Final_ValidatorToReEnable VARCHAR(40) COMMENT 'Validator marked for re-enabling', Final_Flags INT UNSIGNED COMMENT 'Flags after the transaction', Previous_DisabledValidators JSON COMMENT 'JSON array of disabled validators before the transaction', Previous_ValidatorToDisable VARCHAR(40) COMMENT 'Validator to disable before the transaction', Previous_ValidatorToReEnable VARCHAR(40) COMMENT 'Validator to re-enable before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to NegativeUNL ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_nftokenoffer Purpose: This table stores details about NFTokenOffer ledger entries, which represent offers to buy or sell Non-Fungible Tokens (NFTs) in XRPL. It tracks offer details before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_*: Fields for newly created nodes (e.g., New_Amount, New_Expiration). - Final_*: Fields after modification. - Previous_*: Previous state fields for modifications. */ CREATE TABLE affected_nodes_nftokenoffer ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Owner VARCHAR(35) COMMENT 'Owner of the NFT offer for new nodes', New_NFTokenID CHAR(64) COMMENT 'ID of the NFT involved for new nodes', New_Flags INT UNSIGNED COMMENT 'Flags for new Offer nodes', New_Destination VARCHAR(35) COMMENT 'Destination account for new Offer nodes', New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new nodes', New_Amount VARCHAR(40) COMMENT 'Raw offer amount for new nodes', New_Amount_Value DECIMAL(30,10) COMMENT 'Parsed offer amount value for new nodes', New_Amount_Currency VARCHAR(40) COMMENT 'Parsed currency of the offer amount for new nodes', New_Amount_Issuer VARCHAR(35) COMMENT 'Parsed issuer of the offer amount for new nodes', New_Expiration INT UNSIGNED COMMENT 'Expiration time for new Offer nodes', New_NFTokenOfferNode VARCHAR(64) COMMENT 'NFToken offer node for new Offer nodes', Final_Amount VARCHAR(40) COMMENT 'Raw offer amount after modification', Final_Owner VARCHAR(35) COMMENT 'Owner of the NFT offer', Final_NFTokenID CHAR(64) COMMENT 'ID of the NFT involved', Final_Amount_Value DECIMAL(30,10) COMMENT 'Offer amount value', Final_Amount_Currency VARCHAR(40) COMMENT 'Currency of the offer amount', Final_Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the offer amount', Final_Flags INT UNSIGNED COMMENT 'Flags set on the offer', Final_TransferFee INT UNSIGNED COMMENT 'Transfer fee for the NFT', Final_Destination VARCHAR(35) COMMENT 'Destination account for the offer', Final_Expiration INT UNSIGNED COMMENT 'Offer expiration time', Final_OfferID CHAR(64) COMMENT 'Unique identifier of the offer', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after modification', Final_NFTokenOfferNode VARCHAR(64) COMMENT 'NFToken offer node after modification', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Previous_Owner VARCHAR(35) COMMENT 'Owner before the transaction', Previous_NFTokenID CHAR(64) COMMENT 'NFT ID before the transaction', Previous_Amount_Value DECIMAL(30,10) COMMENT 'Offer amount before the transaction', Previous_Amount_Currency VARCHAR(40) COMMENT 'Currency before the transaction', Previous_Amount_Issuer VARCHAR(35) COMMENT 'Issuer before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_TransferFee INT UNSIGNED COMMENT 'Transfer fee before the transaction', Previous_Destination VARCHAR(35) COMMENT 'Destination before the transaction', Previous_Expiration INT UNSIGNED COMMENT 'Expiration before the transaction', Previous_OfferID CHAR(64) COMMENT 'Offer ID before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about NFTokenOffer ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_nftokenpage Purpose: This table tracks changes to NFTokenPage ledger entries, which organize NFTokens into pages for efficient storage. It records page navigation, flags, token entries, and transaction history before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_NFTokenPageEntries: JSON array of NFTokens for newly created nodes. - New_NextPageMin: Next page minimum index for new nodes. - New_PreviousPageMin: Previous page minimum index for new nodes. - Final_Flags: Flags set on the page after modification. - Final_PreviousPageMin: Minimum index of the previous page after modification. - Final_NextPageMin: Minimum index of the next page after modification. - Final_NFTokenPageEntries: JSON array of NFTokens on the page after modification. - Final_PreviousTxnID: Transaction ID of the last modification after the transaction. - Final_PreviousTxnLgrSeq: Ledger sequence of the last modification after the transaction. - Previous_Flags: Flags before the transaction. - Previous_PreviousPageMin: Previous page minimum before the transaction. - Previous_NextPageMin: Next page minimum before the transaction. - Previous_NFTokenPageEntries: JSON array of NFTokens before the transaction. */ CREATE TABLE affected_nodes_nftokenpage ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_NFTokenPageEntries JSON COMMENT 'JSON array of NFTokens for newly created nodes', New_NextPageMin CHAR(64) COMMENT 'Next page minimum index for new nodes', New_PreviousPageMin CHAR(64) COMMENT 'Previous page minimum index for new nodes', Final_Flags INT UNSIGNED COMMENT 'Flags set on the page after modification', Final_PreviousPageMin CHAR(64) COMMENT 'Minimum index of the previous page after modification', Final_NextPageMin CHAR(64) COMMENT 'Minimum index of the next page after modification', Final_NFTokenPageEntries JSON COMMENT 'JSON array of NFTokens on the page after modification', Final_PreviousTxnID CHAR(64) COMMENT 'Transaction ID of the last modification after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification after the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_PreviousPageMin CHAR(64) COMMENT 'Previous page minimum before the transaction', Previous_NextPageMin CHAR(64) COMMENT 'Next page minimum before the transaction', Previous_NFTokenPageEntries JSON COMMENT 'JSON array of NFTokens before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to NFTokenPage ledger entries' ENGINE=InnoDB; CREATE TABLE affected_nodes_offer ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account creating the offer for new nodes', New_Sequence INT UNSIGNED COMMENT 'Offer sequence number for new nodes', New_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount for new nodes (e.g., XRP in drops)', New_TakerPays_Value DECIMAL(30,10) COMMENT 'Amount the taker pays for new nodes', New_TakerPays_Currency VARCHAR(3) COMMENT 'Currency the taker pays for new nodes', New_TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount for new nodes', New_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount for new nodes (e.g., XRP in drops)', New_TakerGets_Value DECIMAL(30,10) COMMENT 'Amount the taker gets for new nodes', New_TakerGets_Currency VARCHAR(3) COMMENT 'Currency the taker gets for new nodes', New_TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount for new nodes', New_BookDirectory CHAR(64) COMMENT 'Book directory for new nodes', New_BookNode VARCHAR(64) COMMENT 'Book node identifier for new nodes', New_Flags INT UNSIGNED COMMENT 'Flags for new Offer nodes', New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new Offer nodes', New_Expiration INT UNSIGNED COMMENT 'Expiration time for new Offer nodes', Final_Account VARCHAR(35) COMMENT 'Account creating the offer after modification', Final_Sequence INT UNSIGNED COMMENT 'Offer sequence number after modification', Final_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount after modification (XRP or currency object)', Final_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount after modification (XRP or currency object)', Final_TakerPays_Value DECIMAL(30,10) COMMENT 'Amount the taker pays after modification', Final_TakerPays_Currency VARCHAR(3) COMMENT 'Currency the taker pays after modification', Final_TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount after modification', Final_TakerGets_Value DECIMAL(30,10) COMMENT 'Amount the taker gets after modification', Final_TakerGets_Currency VARCHAR(3) COMMENT 'Currency the taker gets after modification', Final_TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount after modification', Final_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets after the transaction', Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction', Final_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays after the transaction', Final_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency after the transaction', Final_BookDirectory CHAR(64) COMMENT 'Book directory after the transaction', Final_BookNode VARCHAR(64) COMMENT 'Book node identifier after the transaction', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_Flags INT UNSIGNED COMMENT 'Flags set on the offer after modification', Final_Expiration INT UNSIGNED COMMENT 'Offer expiration time after modification', Previous_Account VARCHAR(35) COMMENT 'Account creating the offer before the transaction', Previous_Sequence INT UNSIGNED COMMENT 'Sequence number before the transaction', Previous_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount before the transaction (e.g., XRP in drops)', Previous_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount before the transaction (e.g., XRP in drops)', Previous_TakerPays_Value DECIMAL(30,10) COMMENT 'Taker pays amount before the transaction', Previous_TakerPays_Currency VARCHAR(3) COMMENT 'Taker pays currency before the transaction', Previous_TakerPays_Issuer VARCHAR(35) COMMENT 'Taker pays issuer before the transaction', Previous_TakerGets_Value DECIMAL(30,10) COMMENT 'Taker gets amount before the transaction', Previous_TakerGets_Currency VARCHAR(3) COMMENT 'Currency the taker gets before the transaction', Previous_TakerGets_Issuer VARCHAR(35) COMMENT 'Taker gets issuer before the transaction', Previous_BookDirectory CHAR(64) COMMENT 'Book directory before the transaction', Previous_BookNode VARCHAR(64) COMMENT 'Book node before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_Expiration INT UNSIGNED COMMENT 'Expiration before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about Offer ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_paychannel Purpose: This table tracks changes to PayChannel ledger entries, which facilitate deferred payments in XRPL. It records channel details like amount and balance before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_*: Fields for newly created PayChannel nodes (e.g., New_Account, New_Amount). - Final_*: Fields after modification. - Previous_*: Previous state fields for modifications. */ CREATE TABLE affected_nodes_paychannel ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account creating the payment channel for new nodes', New_Amount BIGINT COMMENT 'Total amount allocated to the channel for new nodes', New_Destination VARCHAR(35) COMMENT 'Account receiving payments for new nodes', New_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier for new nodes', New_PublicKey VARCHAR(66) COMMENT 'Public key of the channel for new nodes', New_SettleDelay INT UNSIGNED COMMENT 'Time delay before settlement for new nodes', New_CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled for new Escrow nodes', New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes', New_SourceTag INT UNSIGNED COMMENT 'Source tag for CreatedNode', Final_Account VARCHAR(35) COMMENT 'Account creating the payment channel after modification', Final_Destination VARCHAR(35) COMMENT 'Account receiving payments after modification', Final_Amount BIGINT COMMENT 'Total amount allocated to the channel after modification', Final_Balance BIGINT COMMENT 'Current balance of the channel after modification', Final_Flags INT UNSIGNED COMMENT 'Flags set on the trust line', Final_PublicKey VARCHAR(66) COMMENT 'Public key of the channel after modification', Final_SettleDelay INT UNSIGNED COMMENT 'Time delay before settlement after modification', Final_Expiration INT UNSIGNED COMMENT 'Channel expiration time after modification', Final_CancelAfter INT UNSIGNED COMMENT 'Time after which channel can be canceled after modification', Final_SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', Final_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account creating the channel before the transaction', Previous_Destination VARCHAR(35) COMMENT 'Recipient before the transaction', Previous_Amount BIGINT COMMENT 'Allocated amount before the transaction', Previous_Balance BIGINT COMMENT 'Balance before the transaction', Previous_PublicKey VARCHAR(66) COMMENT 'Public key before the transaction', Previous_SettleDelay INT UNSIGNED COMMENT 'Settle delay before the transaction', Previous_Expiration INT UNSIGNED COMMENT 'Expiration before the transaction', Previous_CancelAfter INT UNSIGNED COMMENT 'Cancel time before the transaction', Previous_SourceTag INT UNSIGNED COMMENT 'Source tag before the transaction', Previous_DestinationTag INT UNSIGNED COMMENT 'Destination tag before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to PayChannel ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_ripplestate Purpose: This table stores details about RippleState ledger entries, representing trust lines between accounts for non-XRP assets in XRPL. It tracks balance and limits before and after transactions, including the initial state for created nodes. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - New_*: Fields for CreatedNode actions (initial state). - Final_*: Fields for ModifiedNode actions (final state). - Previous_*: Fields for ModifiedNode and DeletedNode actions (previous state). Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - New_Balance_Value: Initial trust line balance. - New_Flags: Initial flags set on the trust line. - New_HighLimit_Value: Initial high account’s limit. - New_LowLimit_Value: Initial low account’s limit. - Final_Balance_Value: Current trust line balance. - Final_Flags: Flags set on the trust line. - Final_HighLimit_Value: High account’s limit. - Final_LowLimit_Value: Low account’s limit. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_ripplestate ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Balance_Value DECIMAL(30,10) COMMENT 'Initial trust line balance value', New_Balance_Currency VARCHAR(40) COMMENT 'Initial trust line balance currency', New_Balance_Issuer VARCHAR(35) COMMENT 'Initial trust line balance issuer', New_Flags INT UNSIGNED COMMENT 'Initial flags set on the trust line', New_HighLimit_Value DECIMAL(30,10) COMMENT 'Initial high account’s trust limit value', New_HighLimit_Currency VARCHAR(40) COMMENT 'Initial high account’s trust limit currency', New_HighLimit_Issuer VARCHAR(35) COMMENT 'Initial high account’s trust limit issuer', New_HighNode VARCHAR(64) COMMENT 'Initial high account node identifier', New_LowLimit_Value DECIMAL(30,10) COMMENT 'Initial low account’s trust limit value', New_LowLimit_Currency VARCHAR(40) COMMENT 'Initial low account’s trust limit currency', New_LowLimit_Issuer VARCHAR(35) COMMENT 'Initial low account’s trust limit issuer', New_LowNode VARCHAR(64) COMMENT 'Initial low account node identifier', Final_Balance_Value DECIMAL(30,10) COMMENT 'Current trust line balance', Final_Balance_Currency VARCHAR(40) COMMENT 'Currency of the balance', Final_Balance_Issuer VARCHAR(35) COMMENT 'Issuer of the balance', Final_Flags INT UNSIGNED COMMENT 'Flags set on the trust line', Final_HighLimit_Value DECIMAL(30,10) COMMENT 'High account''s trust limit value', Final_HighLimit_Currency VARCHAR(40) COMMENT 'High limit currency', Final_HighLimit_Issuer VARCHAR(35) COMMENT 'High limit issuer', Final_LowLimit_Value DECIMAL(30,10) COMMENT 'Low account''s trust limit value', Final_LowLimit_Currency VARCHAR(40) COMMENT 'Low limit currency', Final_LowLimit_Issuer VARCHAR(35) COMMENT 'Low limit issuer', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_HighNode VARCHAR(64) COMMENT 'High account node identifier', Final_LowNode VARCHAR(64) COMMENT 'Low account node identifier', Final_HighQualityIn INT UNSIGNED COMMENT 'High account quality in', Final_HighQualityOut INT UNSIGNED COMMENT 'High account quality out', Final_LowQualityIn INT UNSIGNED COMMENT 'Low account quality in', Final_LowQualityOut INT UNSIGNED COMMENT 'Low account quality out', Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction', Previous_Balance_Value DECIMAL(30,10) COMMENT 'Balance before the transaction', Previous_Balance_Currency VARCHAR(40) COMMENT 'Balance currency before the transaction', Previous_Balance_Issuer VARCHAR(35) COMMENT 'Balance issuer before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_HighLimit_Value DECIMAL(30,10) COMMENT 'High limit value before the transaction', Previous_HighLimit_Currency VARCHAR(40) COMMENT 'High limit currency before the transaction', Previous_HighLimit_Issuer VARCHAR(35) COMMENT 'High limit issuer before the transaction', Previous_LowLimit_Value DECIMAL(30,10) COMMENT 'Low limit value before the transaction', Previous_LowLimit_Currency VARCHAR(40) COMMENT 'Low limit currency before the transaction', Previous_LowLimit_Issuer VARCHAR(35) COMMENT 'Low limit issuer before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_HighNode VARCHAR(64) COMMENT 'High node before the transaction', Previous_LowNode VARCHAR(64) COMMENT 'Low node before the transaction', Previous_HighQualityIn INT UNSIGNED COMMENT 'High quality in before the transaction', Previous_HighQualityOut INT UNSIGNED COMMENT 'High quality out before the transaction', Previous_LowQualityIn INT UNSIGNED COMMENT 'Low quality in before the transaction', Previous_LowQualityOut INT UNSIGNED COMMENT 'Low quality out before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about RippleState ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_signerlist Purpose: This table tracks changes to SignerList ledger entries, used for multi-signature transactions in XRPL. It records signer details and quorum requirements before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - New_*: Initial fields for newly created SignerList nodes. - Final_*: State after the transaction. - Previous_*: State before the transaction. - Node_*: Metadata about the node's transaction history. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - New_Flags, Final_Flags, Previous_Flags: Flags for the SignerList. - New_SignerEntries, Final_SignerEntries, Previous_SignerEntries: List of authorized signers. - New_SignerQuorum, Final_SignerQuorum, Previous_SignerQuorum: Minimum signatures required. */ CREATE TABLE affected_nodes_signerlist ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', New_Flags INT UNSIGNED COMMENT 'Flags for new SignerList nodes', New_SignerEntries JSON COMMENT 'JSON array of signers for new SignerList nodes', New_SignerQuorum INT UNSIGNED COMMENT 'Quorum for new SignerList nodes', New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', Final_Flags INT UNSIGNED COMMENT 'Flags after the transaction', Final_OwnerNode VARCHAR(64) COMMENT 'Node linking to the owning account after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_SignerEntries JSON COMMENT 'JSON array of authorized signers after the transaction', Final_SignerListID INT UNSIGNED COMMENT 'Identifier of the signer list after the transaction', Final_SignerQuorum INT UNSIGNED COMMENT 'Required quorum for multi-signing after the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_SignerEntries JSON COMMENT 'Signers before the transaction', Previous_SignerListID INT UNSIGNED COMMENT 'Signer list ID before the transaction', Previous_SignerQuorum INT UNSIGNED COMMENT 'Quorum before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to SignerList ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_ticket Purpose: This table stores details about Ticket ledger entries, used for deferred transaction sequencing in XRPL. It tracks ticket details before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_Account: Account owning the ticket. - Final_TicketSequence: Sequence number of the ticket. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_Account: Ticket owner. - Final_Flags: Flags set on the ticket. - Final_TicketSequence: Unique ticket sequence number. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_ticket ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account owning the ticket for new nodes', New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new nodes', New_TicketSequence INT UNSIGNED COMMENT 'Sequence number of the ticket for new nodes', Final_Account VARCHAR(35) COMMENT 'Account owning the ticket', Final_Flags INT UNSIGNED COMMENT 'Flags set on the ticket', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Final_TicketSequence INT UNSIGNED COMMENT 'Sequence number of the ticket', Previous_Account VARCHAR(35) COMMENT 'Account owning the ticket before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', Previous_TicketSequence INT UNSIGNED COMMENT 'Ticket sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about Ticket ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_did Purpose: This table tracks changes to DID (Decentralized Identifier) ledger entries, which store decentralized identity information in XRPL. It records DID details before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_Account: Account owning the DID. - Final_DIDDocument: DID document content. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_Account: DID owner. - Final_DIDDocument: DID document text. - Final_Data: Additional DID data. - Final_URI: URI associated with the DID. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_did ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account creating the AMM for new nodes', New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new nodes', New_URI VARCHAR(256) COMMENT 'URI associated with the DID', Final_Account VARCHAR(35) COMMENT 'Account owning the DID', Final_DIDDocument TEXT COMMENT 'DID document content', Final_Data TEXT COMMENT 'Additional DID data', Final_URI VARCHAR(256) COMMENT 'URI associated with the DID', Final_Flags INT UNSIGNED COMMENT 'Flags set on the DID', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account owning the DID before the transaction', Previous_DIDDocument TEXT COMMENT 'DID document before the transaction', Previous_Data TEXT COMMENT 'Data before the transaction', Previous_URI VARCHAR(256) COMMENT 'URI before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Tracks changes to DID ledger entries' ENGINE=InnoDB; /* Table: affected_nodes_amm Purpose: This table stores details about AMM (Automated Market Maker) ledger entries, which manage liquidity pools in XRPL. It tracks pool details like assets and fees before and after transactions. Structure: - id: Unique identifier for each entry. - base_id: Links to affected_nodes_base. - Node_PreviousTxnID: Last transaction ID modifying the node. - Node_PreviousTxnLgrSeq: Ledger sequence of the last modification. - Final_Account: Account creating the AMM. - Final_Asset_*: First asset details (currency, issuer). - Final_Asset2_*: Second asset details (currency, issuer). - Final_LPTokenBalance_*: Liquidity pool token balance. - Previous_*: Previous state fields for modifications. Key Fields: - id: Primary key for this table. - base_id: Foreign key to affected_nodes_base. - Final_Account: AMM creator. - Final_Asset_Currency: First asset currency. - Final_Asset2_Currency: Second asset currency. - Final_LPTokenBalance_Value: LP token balance. - Final_TradingFee: Trading fee percentage. - Previous_*: Pre-transaction state fields. */ CREATE TABLE affected_nodes_amm ( id BIGINT AUTO_INCREMENT, base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node', Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification', New_Account VARCHAR(35) COMMENT 'Account creating the AMM for new nodes', New_Asset_Currency VARCHAR(3) COMMENT 'First asset currency for new nodes', New_Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer for new nodes', New_Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency for new nodes', New_Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer for new nodes', New_AuctionSlot JSON COMMENT 'Auction slot details for new nodes', New_LPTokenBalance_Value DECIMAL(30,10) COMMENT 'LP token balance value for new nodes', New_LPTokenBalance_Currency VARCHAR(40) COMMENT 'LP token currency for new nodes', New_LPTokenBalance_Issuer VARCHAR(35) COMMENT 'LP token issuer for new nodes', New_TradingFee INT UNSIGNED COMMENT 'Trading fee percentage for new nodes', New_VoteSlots JSON COMMENT 'Vote slots for new nodes', Final_Account VARCHAR(35) COMMENT 'Account creating the AMM', Final_Asset_Currency VARCHAR(3) COMMENT 'First asset currency', Final_Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer', Final_Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency', Final_Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer', Final_AuctionSlot JSON COMMENT 'Auction slot details after the transaction', Final_LPTokenBalance_Value DECIMAL(30,10) COMMENT 'Liquidity pool token balance value', Final_LPTokenBalance_Currency VARCHAR(40) COMMENT 'LP token currency', Final_LPTokenBalance_Issuer VARCHAR(35) COMMENT 'LP token issuer', Final_TradingFee INT UNSIGNED COMMENT 'Trading fee percentage', Final_VoteSlots JSON COMMENT 'Vote slots after the transaction', Final_Flags INT UNSIGNED COMMENT 'Flags set on the AMM', Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction', Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction', Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction', Previous_Account VARCHAR(35) COMMENT 'Account creating the AMM before the transaction', Previous_Asset_Currency VARCHAR(3) COMMENT 'First asset currency before the transaction', Previous_Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer before the transaction', Previous_Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency before the transaction', Previous_Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer before the transaction', Previous_AuctionSlot JSON COMMENT 'Auction slot before the transaction', Previous_LPTokenBalance_Value DECIMAL(30,10) COMMENT 'LP token balance before the transaction', Previous_LPTokenBalance_Currency VARCHAR(40) COMMENT 'LP token currency before the transaction', Previous_LPTokenBalance_Issuer VARCHAR(35) COMMENT 'LP token issuer before the transaction', Previous_TradingFee INT UNSIGNED COMMENT 'Trading fee before the transaction', Previous_VoteSlots JSON COMMENT 'Vote slots before the transaction', Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction', Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction', Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction', Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_base_id_date (base_id, transaction_date), FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE ) COMMENT = 'Stores details about AMM ledger entries' ENGINE=InnoDB; /* Table: transactions_payment Purpose: This table stores specific details for Payment transactions, which transfer value between accounts in XRPL. It captures payment-specific fields like amount, destination, and the original flags integer from the transaction. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Amount being transferred (value, currency, issuer). - Destination: Recipient account. - Paths: JSON array of payment paths for multi-hop payments. - Flags: Original flags integer from the XRPL transaction. - PartialPayment: Flag for partial payment allowance. - NoDirectRipple: Flag to disable direct ripple. - LimitQuality: Flag to limit quality. - DeliveredAmount_*: Actual amount delivered (value, currency, issuer). - InvoiceID: Invoice ID associated with the payment. - TicketSequence: Ticket sequence number used in the transaction. - Signers: JSON array of signers for the transaction. - AccountTxnID: Hash of a previous transaction from the same account. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Intended transfer amount. - Destination: Target account. - Paths: Routes for the payment. - Flags: Raw integer value of transaction flags. - PartialPayment: Indicates if partial payment is allowed. - DeliveredAmount_Value: Actual amount delivered. - InvoiceID: Invoice ID associated with the payment. - TicketSequence: Ticket sequence number (if used). - Signers: Array of signers for the transaction. - AccountTxnID: Hash of a previous transaction from the same account. */ CREATE TABLE transactions_payment ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Intended transfer amount value', Amount_Currency VARCHAR(40) COMMENT 'Currency of the intended amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the intended amount', Destination VARCHAR(35) NOT NULL COMMENT 'Recipient account', DestinationTag INT UNSIGNED COMMENT 'Destination tag', SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount to send value', SendMax_Currency VARCHAR(40) COMMENT 'Currency of SendMax', SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of SendMax', DeliverMin_Value DECIMAL(30,10) COMMENT 'Minimum amount to deliver value', DeliverMin_Currency VARCHAR(40) COMMENT 'Currency of DeliverMin', DeliverMin_Issuer VARCHAR(35) COMMENT 'Issuer of DeliverMin', DeliverMax_Value DECIMAL(30,10) COMMENT 'Maximum amount to deliver value', DeliverMax_Currency VARCHAR(40) COMMENT 'Currency of DeliverMax', DeliverMax_Issuer VARCHAR(35) COMMENT 'Issuer of DeliverMax', Paths JSON COMMENT 'JSON array of payment paths for multi-hop payments', Flags INT UNSIGNED COMMENT 'Original flags integer from the transaction', PartialPayment TINYINT COMMENT 'Flag for partial payment allowance', NoDirectRipple TINYINT COMMENT 'Flag to disable direct ripple', LimitQuality TINYINT COMMENT 'Flag to limit quality', SourceTag INT UNSIGNED COMMENT 'Source tag', DeliveredAmount_Value DECIMAL(30,10) COMMENT 'Actual delivered amount value', DeliveredAmount_Currency VARCHAR(40) COMMENT 'Currency of delivered amount', DeliveredAmount_Issuer VARCHAR(35) COMMENT 'Issuer of delivered amount', InvoiceID CHAR(64) DEFAULT NULL COMMENT 'Invoice ID associated with the payment', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', Signers JSON COMMENT 'JSON array of signers for the transaction', AccountTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of a previous transaction from the same account', PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_payment_delivered_amount_value (DeliveredAmount_Value), INDEX idx_transactions_payment_destination (Destination), INDEX idx_transactions_payment_amount_value (Amount_Value) ) COMMENT = 'Stores specific details for Payment transactions' ENGINE=InnoDB; /* Table: transaction_signers Purpose: This table stores signer details for multi-signed transactions in XRPL. It allows multiple signers per transaction, each with their own account, public key, and signature. Structure: - id: Unique identifier for each signer entry. - transaction_id: Links to the transactions table. - account: Signer’s XRPL account address. - signing_pub_key: Public key used for signing. - txn_signature: Signature provided by the signer. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - account: Signer’s account. - signing_pub_key: Public key for verification. - txn_signature: Signature for validation. */ CREATE TABLE transaction_signers ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', account VARCHAR(35) COMMENT 'Signer''s XRPL account address', signing_pub_key VARCHAR(66) COMMENT 'Public key used for signing', txn_signature VARCHAR(128) COMMENT 'Signature provided by the signer', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_signer_date (transaction_id, account, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transaction_signers_transaction_id (transaction_id) ) COMMENT = 'Stores signer details for multi-signed transactions in XRPL' ENGINE=InnoDB; /* Table: transaction_memos Purpose: This table stores memo details attached to XRPL transactions. Memos allow users to include arbitrary data (e.g., notes, references) with a transaction. Structure: - id: Unique identifier for each memo entry. - transaction_id: Links to the transactions table. - memo_type: Type of the memo (hexadecimal). - memo_data: Content of the memo (hexadecimal). - memo_format: Format of the memo (hexadecimal). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - memo_type: Type of memo. - memo_data: Memo content. - memo_format: Memo format. */ CREATE TABLE transaction_memos ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', memo_index INT NOT NULL COMMENT 'Index of the memo in the Memos array', memo_type VARCHAR(255) COMMENT 'Type of the memo (hexadecimal)', memo_data TEXT COMMENT 'Content of the memo (hexadecimal)', memo_format VARCHAR(255) COMMENT 'Format of the memo (hexadecimal)', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_memo_date (transaction_id, memo_index, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transaction_memos_transaction_id (transaction_id) ) COMMENT = 'Stores memo details attached to XRPL transactions' ENGINE=InnoDB; /* Table: transactions_paymentchannelcreate Purpose: This table stores details for PaymentChannelCreate transactions, which establish a payment channel for deferred payments in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Amount allocated to the channel (value, currency, issuer). - Destination: Recipient account. - SettleDelay: Delay before settlement. - PublicKey: Channel’s public key. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Channel funding amount. - Destination: Payment recipient. - SettleDelay: Time before funds can be settled. - PublicKey: Key for channel operations. */ CREATE TABLE transactions_paymentchannelcreate ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Amount allocated to the channel', Amount_Currency VARCHAR(3) COMMENT 'Currency of the channel amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the channel amount', Destination VARCHAR(35) NOT NULL COMMENT 'Recipient account', SettleDelay INT UNSIGNED NOT NULL COMMENT 'Delay before settlement', PublicKey VARCHAR(66) NOT NULL COMMENT 'Channel''s public key', CancelAfter INT UNSIGNED COMMENT 'Time after which channel can be canceled', DestinationTag INT UNSIGNED COMMENT 'Destination tag', SourceTag INT UNSIGNED COMMENT 'Source tag', Flags INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Flags set for the transaction', TicketSequence INT UNSIGNED COMMENT 'Ticket sequence number used', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for PaymentChannelCreate transactions' ENGINE=InnoDB; /* Table: transactions_paymentchannelfund Purpose: This table stores details for PaymentChannelFund transactions, which add funds to an existing payment channel in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Channel: ID of the payment channel. - Amount_*: Additional amount to fund (value, currency, issuer). - SettleDelay: Optional new settle delay. - Expiration: Expiration time of the payment channel. - Flags: Flags set for the transaction. - SourceTag: Source tag for the transaction. - TicketSequence: Ticket sequence number used. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Channel: Unique channel identifier. - Amount_Value: Funding amount. - SettleDelay: Updated settle delay, if specified. - Expiration: Channel expiration time, if set. - Flags: Transaction flags. - SourceTag: Identifier for the transaction source, if set. - TicketSequence: Ticket sequence number, if used. */ CREATE TABLE transactions_paymentchannelfund ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Channel CHAR(64) NOT NULL COMMENT 'ID of the payment channel', Amount_Value DECIMAL(30,10) COMMENT 'Additional amount to fund', Amount_Currency VARCHAR(40) COMMENT 'Currency of the funding amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the funding amount', SettleDelay INT UNSIGNED COMMENT 'Optional new settle delay', Expiration INT UNSIGNED COMMENT 'Expiration time of the payment channel', SourceTag INT UNSIGNED COMMENT 'Source tag for the transaction', Flags INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Flags set for the transaction', TicketSequence INT UNSIGNED COMMENT 'Ticket sequence number used', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for PaymentChannelFund transactions' ENGINE=InnoDB; /* Table: transactions_paymentchannelclaim Purpose: This table stores details for PaymentChannelClaim transactions, which claim funds from a payment channel in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Channel: ID of the payment channel. - Balance: Updated channel balance. - Amount_*: Amount claimed (value, currency, issuer). - Signature: Claim signature. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Channel: Channel identifier. - Balance: Current channel balance. - Amount_Value: Claimed amount. - Signature: Validates the claim. */ CREATE TABLE transactions_paymentchannelclaim ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Channel CHAR(64) NOT NULL COMMENT 'ID of the payment channel', Balance BIGINT COMMENT 'Updated channel balance', Amount_Value DECIMAL(30,10) COMMENT 'Amount claimed', Amount_Currency VARCHAR(40) COMMENT 'Currency of the claimed amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the claimed amount', Signature VARCHAR(128) COMMENT 'Claim signature', PublicKey VARCHAR(66) COMMENT 'Public key for the claim', SourceTag INT UNSIGNED COMMENT 'Source tag', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for PaymentChannelClaim transactions' ENGINE=InnoDB; /* Table: transactions_escrowcreate Purpose: This table stores details for EscrowCreate transactions, which lock XRP in escrow until conditions are met in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Escrowed amount (value, currency, issuer). - Destination: Recipient account. - Condition: Release condition. - CancelAfter: Time after which escrow can be canceled. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Escrowed amount. - Destination: Target account. - Condition: Release condition. - CancelAfter: Cancellation deadline. - FinishAfter: Release deadline. */ CREATE TABLE transactions_escrowcreate ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Escrowed amount', Amount_Currency VARCHAR(40) COMMENT 'Currency of the escrowed amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the escrowed amount', Destination VARCHAR(35) NOT NULL COMMENT 'Recipient account', CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled', FinishAfter INT UNSIGNED COMMENT 'Time after which escrow can be finished', Signers JSON COMMENT 'JSON array of signers for the transaction', `Condition` VARCHAR(256) COMMENT 'Release condition', DestinationTag INT UNSIGNED COMMENT 'Destination tag', SourceTag INT UNSIGNED COMMENT 'Source tag', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for EscrowCreate transactions' ENGINE=InnoDB; /* Table: transactions_escrowfinish Purpose: This table stores details for EscrowFinish transactions, which release escrowed funds in XRPL upon meeting conditions. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - OfferSequence: Sequence number of the escrow creation. - Owner: Escrow owner. - Condition: Release condition. - Fulfillment: Proof meeting the condition. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - OfferSequence: Links to the escrow creation. - Owner: Account owning the escrow. - Condition: Required condition. - Fulfillment: Condition fulfillment proof. */ CREATE TABLE transactions_escrowfinish ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', OfferSequence INT UNSIGNED NOT NULL COMMENT 'Sequence number of the escrow creation', Owner VARCHAR(35) NOT NULL COMMENT 'Escrow owner', `Condition` VARCHAR(256) COMMENT 'Release condition', Fulfillment VARCHAR(256) COMMENT 'Proof meeting the condition', PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for EscrowFinish transactions' ENGINE=InnoDB; /* Table: transactions_escrowcancel Purpose: This table stores details for EscrowCancel transactions, which cancel an escrow in XRPL after the cancellation time. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - OfferSequence: Sequence number of the escrow creation. - Owner: Escrow owner. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - OfferSequence: Links to the escrow creation. - Owner: Account owning the escrow. */ CREATE TABLE transactions_escrowcancel ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', OfferSequence INT UNSIGNED NOT NULL COMMENT 'Sequence number of the escrow creation', Owner VARCHAR(35) NOT NULL COMMENT 'Escrow owner', PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for EscrowCancel transactions' ENGINE=InnoDB; /* Table: transactions_checkcreate Purpose: This table stores details for CheckCreate transactions, which create a check for deferred payment in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Destination: Check recipient. - SendMax_*: Maximum amount (value, currency, issuer). - Expiration: Check expiration time. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Destination: Target account. - SendMax_Value: Maximum check amount. - Expiration: Check validity deadline. */ CREATE TABLE transactions_checkcreate ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', Destination VARCHAR(35) NOT NULL COMMENT 'Check recipient', SendMax_Value DECIMAL(30,10) COMMENT 'Maximum check amount', SendMax_Currency VARCHAR(40) COMMENT 'Currency of the maximum amount', SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of the maximum amount', DestinationTag INT UNSIGNED COMMENT 'Destination tag', Expiration INT UNSIGNED COMMENT 'Check expiration time', InvoiceID CHAR(64) COMMENT 'Invoice ID associated with the check', SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for CheckCreate transactions' ENGINE=InnoDB; /* Table: transactions_checkcash Purpose: This table stores details for CheckCash transactions, which cash a check in XRPL to receive funds. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - CheckID: ID of the check being cashed. - Amount_*: Amount to cash (value, currency, issuer). - DeliverMin_*: Minimum amount to deliver. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - CheckID: Unique check identifier. - Amount_Value: Cashed amount. - DeliverMin_Value: Minimum delivery amount. */ CREATE TABLE transactions_checkcash ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', CheckID CHAR(64) NOT NULL COMMENT 'ID of the check being cashed', Amount_Value DECIMAL(30,10) COMMENT 'Amount to cash', Amount_Currency VARCHAR(40) COMMENT 'Currency of the cashed amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the cashed amount', DeliverMin_Value DECIMAL(30,10) COMMENT 'Minimum amount to deliver', DeliverMin_Currency VARCHAR(40) COMMENT 'Currency of DeliverMin', DeliverMin_Issuer VARCHAR(35) COMMENT 'Issuer of DeliverMin', SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for CheckCash transactions' ENGINE=InnoDB; /* Table: transactions_checkcancel Purpose: This table stores details for CheckCancel transactions, which cancel an existing check in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - CheckID: ID of the check to cancel. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - CheckID: Unique check identifier. */ CREATE TABLE transactions_checkcancel ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', CheckID CHAR(64) NOT NULL COMMENT 'ID of the check to cancel', SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for CheckCancel transactions' ENGINE=InnoDB; /* Table: transactions_accountset Purpose: This table stores details for AccountSet transactions, which modify account settings in XRPL, such as flags or domain. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - SetFlag: Flag to enable. - ClearFlag: Flag to disable. - Domain: Account domain to set. - EmailHash: Account email hash. - MessageKey: Account message key. - TransferRate: Fee rate for transfers. - TickSize: Tick size for the account. - NFTokenMinter: NFToken minter account. - WalletLocator: Wallet locator. - WalletSize: Wallet size. - TicketSequence: Ticket sequence number used in the transaction. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - SetFlag: Flag to activate. - ClearFlag: Flag to deactivate. - Domain: Account domain name. - TransferRate: Fee rate for transfers. - TicketSequence: Ticket sequence number (if used). */ CREATE TABLE transactions_accountset ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', SetFlag INT UNSIGNED COMMENT 'Flag to enable', ClearFlag INT UNSIGNED COMMENT 'Flag to disable', Domain VARCHAR(256) COMMENT 'Account domain to set', EmailHash CHAR(32) COMMENT 'Account email hash', MessageKey VARCHAR(66) COMMENT 'Account message key', TransferRate INT UNSIGNED COMMENT 'Fee rate for transfers', TickSize TINYINT UNSIGNED COMMENT 'Tick size for the account', NFTokenMinter VARCHAR(35) COMMENT 'NFToken minter account', WalletLocator CHAR(64) COMMENT 'Wallet locator', WalletSize INT UNSIGNED COMMENT 'Wallet size', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', OperationLimit INT UNSIGNED COMMENT 'Operation limit for the account', SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for AccountSet transactions' ENGINE=InnoDB; /* Table: transactions_setregularkey Purpose: This table stores details for SetRegularKey transactions, which set a regular key for an account in XRPL for authorization purposes. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - RegularKey: New regular key to set. - Flags: Transaction flags indicating specific options or behaviors. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - RegularKey: Account’s new regular key. - Flags: Integer representing transaction flags. */ CREATE TABLE transactions_setregularkey ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', RegularKey VARCHAR(35) COMMENT 'New regular key to set', Flags INT COMMENT 'Transaction flags indicating specific options or behaviors', SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', OperationLimit INT UNSIGNED COMMENT 'Operation limit for the account', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for SetRegularKey transactions' ENGINE=InnoDB; /* Table: transactions_signerlistset Purpose: This table stores details for SignerListSet transactions, which configure a signer list for multi-signature transactions in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - SignerQuorum: Required number of signatures. - SignerEntries: JSON array of signers. - Flags: Transaction flags (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - SignerQuorum: Minimum signatures needed. - SignerEntries: List of authorized signers. - Flags: Integer flags for the transaction. */ CREATE TABLE transactions_signerlistset ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Signers JSON COMMENT 'JSON array of signers for the transaction', SignerQuorum INT UNSIGNED NOT NULL COMMENT 'Required number of signatures', SignerEntries JSON COMMENT 'JSON array of signers', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for SignerListSet transactions' ENGINE=InnoDB; /* Table: transactions_trustset Purpose: This table stores details for TrustSet transactions, which establish or modify a trust line for non-XRP assets in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - LimitAmount_*: Trust line limit (value, currency, issuer). - QualityIn: Input quality setting. - QualityOut: Output quality setting. - Flags: Flags set on the trust line. - Ripple: Ripple flag. - Authorized: Authorization flag. - SourceTag: Source tag identifying the transaction source. - TicketSequence: Ticket sequence number used in the transaction. - Signers: JSON array of signers for the transaction. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - LimitAmount_Value: Trust line limit amount. - QualityIn: Quality for incoming payments. - QualityOut: Quality for outgoing payments. - SourceTag: Source tag identifying the transaction source. - TicketSequence: Ticket sequence number (if used). - Signers: Array of signers for the transaction. */ CREATE TABLE transactions_trustset ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', LimitAmount_Value DECIMAL(30,10) COMMENT 'Trust line limit amount', LimitAmount_Currency VARCHAR(40) COMMENT 'Currency of the trust limit', LimitAmount_Issuer VARCHAR(35) COMMENT 'Issuer of the trust limit', QualityIn INT UNSIGNED COMMENT 'Quality for incoming payments', QualityOut INT UNSIGNED COMMENT 'Quality for outgoing payments', Flags INT UNSIGNED COMMENT 'Flags set on the trust line', Ripple TINYINT COMMENT 'Ripple flag', Authorized TINYINT COMMENT 'Authorization flag', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for TrustSet transactions' ENGINE=InnoDB; /* Table: transactions_ammvote Purpose: This table stores information about AMMVote transactions in the XRP Ledger (XRPL). Each row represents a single AMMVote transaction, which allows an account to vote on the trading fee for an Automated Market Maker (AMM) instance. Structure: - transaction_id: The unique hash of the AMMVote transaction. - Asset_Currency: The currency code of the first asset in the AMM pair. - Asset_Issuer: The issuer address of the first asset, if applicable. - Asset2_Currency: The currency code of the second asset in the AMM pair. - Asset2_Issuer: The issuer address of the second asset, if applicable. - TradingFee: The proposed trading fee (in basis points) that the account is voting for. Key Fields: - transaction_id: Primary key, uniquely identifying each AMMVote transaction. */ CREATE TABLE transactions_ammvote ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Asset_Currency VARCHAR(50) COMMENT 'Currency code of the first asset in the AMM pair', Asset_Issuer VARCHAR(34) COMMENT 'Issuer address of the first asset, if applicable', Asset2_Currency VARCHAR(50) COMMENT 'Currency code of the second asset in the AMM pair', Asset2_Issuer VARCHAR(34) COMMENT 'Issuer address of the second asset, if applicable', TradingFee INT COMMENT 'Proposed trading fee in basis points (0-1000)', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_ammvote_asset_currency (Asset_Currency), INDEX idx_transactions_ammvote_asset2_currency (Asset2_Currency) ) COMMENT = 'Stores information about AMMVote transactions' ENGINE=InnoDB; /* Table: transactions_accountdelete Purpose: This table stores details for AccountDelete transactions, which remove an account in XRPL and send remaining XRP to a destination. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Destination: Account receiving remaining XRP. - DestinationTag: Tag for the destination. - Flags: Transaction flags (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Destination: Recipient of remaining funds. - DestinationTag: Optional destination identifier. - Flags: Integer flags for the transaction. */ CREATE TABLE transactions_accountdelete ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Destination VARCHAR(35) NOT NULL COMMENT 'Account receiving remaining XRP', DestinationTag INT UNSIGNED COMMENT 'Tag for the destination', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for AccountDelete transactions' ENGINE=InnoDB; /* Table: transactions_offercreate Purpose: This table stores details for OfferCreate transactions, which place a trading offer in XRPL’s decentralized exchange. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - TakerPays_*: Amount offered to pay (value, currency, issuer). - TakerGets_*: Amount offered to receive (value, currency, issuer). - Expiration: Offer expiration time. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - TakerPays_Value: Amount to pay. - TakerGets_Value: Amount to receive. - Expiration: Offer validity deadline. */ CREATE TABLE transactions_offercreate ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', TakerPays_Value DECIMAL(30,10) COMMENT 'Amount offered to pay', TakerPays_Currency VARCHAR(40) COMMENT 'Currency offered to pay', TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount', TakerGets_Value DECIMAL(30,10) COMMENT 'Amount offered to receive', TakerGets_Currency VARCHAR(40) COMMENT 'Currency offered to receive', TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount', Flags INT UNSIGNED COMMENT 'Flags set on the offer', Expiration INT UNSIGNED COMMENT 'Offer expiration time', OfferSequence INT UNSIGNED COMMENT 'Sequence number of the offer', SourceTag INT UNSIGNED COMMENT 'Source tag', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence used in the transaction', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_offercreate_taker_gets_value (TakerGets_Value), INDEX idx_transactions_offercreate_taker_pays_value (TakerPays_Value), INDEX idx_oc_expiration (Expiration), INDEX idx_oc_transaction_id (transaction_id) ) COMMENT = 'Stores details for OfferCreate transactions' ENGINE=InnoDB; /* Table: transactions_offercancel Purpose: This table stores details for OfferCancel transactions, which remove an existing offer from XRPL’s decentralized exchange. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - OfferSequence: Sequence number of the offer to cancel. - Flags: Transaction flags (optional). - SourceTag: Source tag identifying the transaction source (optional). - TicketSequence: Ticket sequence number used in the transaction (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - OfferSequence: Identifies the offer to cancel. - Flags: Integer flags for the transaction. - SourceTag: Integer source tag for the transaction. - TicketSequence: Integer ticket sequence number for the transaction. */ CREATE TABLE transactions_offercancel ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', OfferSequence INT UNSIGNED NOT NULL COMMENT 'Sequence number of the offer to cancel', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for OfferCancel transactions' ENGINE=InnoDB; /* Table: transactions_nftokenmint Purpose: This table stores details for NFTokenMint transactions, which create a new Non-Fungible Token (NFT) in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - URI: NFT metadata URI. - Flags: NFT flags. - TransferFee: Fee for NFT transfers. - Issuer: Issuer of the NFT. - NFTokenTaxon: Taxon associated with the NFT. - TicketSequence: Ticket sequence number used in the transaction. - SourceTag: Source tag identifying the transaction source. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - URI: NFT metadata reference. - Flags: Configuration flags. - TransferFee: Transfer fee percentage. - Issuer: Account that issued the NFT. - NFTokenTaxon: Categorization number for the NFT. - TicketSequence: Ticket sequence number (if used). - SourceTag: Source tag identifying the transaction source. */ CREATE TABLE transactions_nftokenmint ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Offer amount', Amount_Currency VARCHAR(40) COMMENT 'Currency of the offer amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the offer amount', Destination VARCHAR(35) COMMENT 'Target account for the offer', URI VARCHAR(256) COMMENT 'NFT metadata URI', Flags INT UNSIGNED COMMENT 'NFT flags', TransferFee INT UNSIGNED COMMENT 'Fee for NFT transfers', Issuer VARCHAR(35) COMMENT 'Issuer of the NFT', NFTokenTaxon INT UNSIGNED DEFAULT NULL COMMENT 'Taxon associated with the NFT', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', name VARCHAR(255) DEFAULT NULL COMMENT 'NFT name from metadata', description TEXT DEFAULT NULL COMMENT 'NFT description from metadata', image_url VARCHAR(512) DEFAULT NULL COMMENT 'NFT image URL from metadata (gateway resolved)', metadata_json JSON DEFAULT NULL COMMENT 'Raw JSON metadata fetched from IPFS', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for NFTokenMint transactions including fetched IPFS metadata' ENGINE=InnoDB; /* Table: transactions_nftokenburn Purpose: This table stores details for NFTokenBurn transactions, which destroy an existing NFT in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - NFTokenID: ID of the NFT to burn. - Flags: Flags associated with the transaction (optional). - Owner: Address of the NFT owner (optional). - SourceTag: Source tag identifying the transaction source (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - NFTokenID: Unique NFT identifier. - Flags: Integer flags for the transaction. - Owner: Owner’s Ripple address. - SourceTag: Integer source tag for the transaction. */ CREATE TABLE transactions_nftokenburn ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', NFTokenID CHAR(64) NOT NULL COMMENT 'ID of the NFT to burn', Flags BIGINT UNSIGNED DEFAULT NULL COMMENT 'Flags for the transaction', Owner VARCHAR(35) DEFAULT NULL COMMENT 'Owner of the NFT', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', Signers JSON COMMENT 'JSON array of signers for the transaction', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for NFTokenBurn transactions' ENGINE=InnoDB; /* Table: transactions_nftokencreateoffer Purpose: This table stores details for NFTokenCreateOffer transactions, which create an offer to buy or sell an NFT in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Offer amount (value, currency, issuer). - Destination: Target account for the offer. - NFTokenID: ID of the NFT. - Flags: Flags set on the transaction. - SourceTag: Source tag identifying the transaction source. - TicketSequence: Ticket sequence number used in the transaction. - Owner: Owner of the NFT. - Signers: JSON array of signers for the transaction. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Offer amount. - Destination: Offer recipient. - NFTokenID: NFT identifier. - Expiration: Offer validity deadline. - Flags: Transaction flags. - SourceTag: Source tag. - TicketSequence: Ticket sequence number. - Owner: Owner of the NFT. - Signers: Array of signers. */ CREATE TABLE transactions_nftokencreateoffer ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Offer amount', Amount_Currency VARCHAR(40) COMMENT 'Currency of the offer amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the offer amount', Destination VARCHAR(35) COMMENT 'Target account for the offer', Expiration INT UNSIGNED COMMENT 'Offer validity deadline', NFTokenID CHAR(64) COMMENT 'ID of the NFT', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Flags set on the transaction', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', Owner VARCHAR(35) DEFAULT NULL COMMENT 'Owner of the NFT', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for NFTokenCreateOffer transactions' ENGINE=InnoDB; /* Table: transactions_nftokenacceptoffer Purpose: This table stores details for NFTokenAcceptOffer transactions, which accept an NFT buy or sell offer in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - NFTokenBuyOffer: Buy offer ID. - NFTokenSellOffer: Sell offer ID. - Flags: Transaction flags (optional). - SourceTag: Source tag identifying the transaction source (optional). - NFTokenBrokerFee_Value: Broker fee value (optional). - NFTokenBrokerFee_Currency: Currency of the broker fee (optional). - NFTokenBrokerFee_Issuer: Issuer of the broker fee (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - NFTokenBuyOffer: Identifier of the buy offer. - NFTokenSellOffer: Identifier of the sell offer. - Flags: Integer flags for the transaction. - SourceTag: Integer source tag for the transaction. - NFTokenBrokerFee_Value: Value of the broker fee. - NFTokenBrokerFee_Currency: Currency of the broker fee. - NFTokenBrokerFee_Issuer: Issuer of the broker fee. */ CREATE TABLE transactions_nftokenacceptoffer ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', NFTokenBuyOffer CHAR(64) COMMENT 'Identifier of the buy offer', NFTokenSellOffer CHAR(64) COMMENT 'Identifier of the sell offer', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', NFTokenBrokerFee_Value DECIMAL(30,10) COMMENT 'Broker fee value', NFTokenBrokerFee_Currency VARCHAR(40) COMMENT 'Currency of the broker fee', NFTokenBrokerFee_Issuer VARCHAR(35) COMMENT 'Issuer of the broker fee', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for NFTokenAcceptOffer transactions' ENGINE=InnoDB; /* Table: transactions_nftokencanceloffer Purpose: This table stores details for NFTokenCancelOffer transactions, which cancel existing NFT offers in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - NFTokenOffers: JSON array of offer IDs to cancel. - SourceTag: Source tag identifying the transaction source. - Flags: Transaction flags (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - NFTokenOffers: List of offers to cancel. - SourceTag: Integer source tag for the transaction. - Flags: Integer flags for the transaction. */ CREATE TABLE transactions_nftokencanceloffer ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', NFTokenOffers JSON COMMENT 'JSON array of offer IDs to cancel', SourceTag INT UNSIGNED COMMENT 'Source tag', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for NFTokenCancelOffer transactions' ENGINE=InnoDB; /* Table: transactions_ammcreate Purpose: This table stores details for AMMCreate transactions, which establish an Automated Market Maker (AMM) pool in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Initial funding amount (value, currency, issuer). - TradingFee: Fee for trading in the pool. - Asset_*: First asset (currency, issuer). - Asset2_*: Second asset (currency, issuer). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Initial pool funding. - TradingFee: Trading fee percentage. - Asset_Currency: First asset currency. - Asset2_Currency: Second asset currency. */ CREATE TABLE transactions_ammcreate ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Initial pool funding amount for the first asset', Amount_Currency VARCHAR(40) COMMENT 'Currency of the first funding amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the first funding amount', Amount2_Value DECIMAL(30,10) COMMENT 'Initial pool funding amount for the second asset', Amount2_Currency VARCHAR(40) COMMENT 'Currency of the second funding amount', Amount2_Issuer VARCHAR(35) COMMENT 'Issuer of the second funding amount', TradingFee INT UNSIGNED COMMENT 'Trading fee percentage', Asset_Currency VARCHAR(40) COMMENT 'First asset currency', Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer', Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency', Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer', Flags INT UNSIGNED COMMENT 'Transaction flags', SourceTag INT UNSIGNED COMMENT 'Source tag', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for AMMCreate transactions' ENGINE=InnoDB; /* Table: transactions_ammdeposit Purpose: This table stores details for AMMDeposit transactions, which add liquidity to an AMM pool in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Deposit amount (value, currency, issuer). - EPrice_*: Expected price (value, currency, issuer). - Asset_*: First asset (currency, issuer). - Asset2_*: Second asset (currency, issuer). - Flags: Transaction flags. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Deposited amount. - EPrice_Value: Expected price of the deposit. - Asset_Currency: First asset currency. - Asset2_Currency: Second asset currency. - Flags: Integer value representing transaction flags. */ CREATE TABLE transactions_ammdeposit ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Deposit amount', Amount_Currency VARCHAR(40) COMMENT 'Currency of the deposit amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the deposit amount', Amount2_Value DECIMAL(30,10) COMMENT 'Second deposit amount', Amount2_Currency VARCHAR(40) COMMENT 'Currency of the second deposit amount', Amount2_Issuer VARCHAR(35) COMMENT 'Issuer of the second deposit amount', EPrice_Value DECIMAL(30,10) COMMENT 'Expected price of the deposit', EPrice_Currency VARCHAR(40) COMMENT 'Currency of the expected price', EPrice_Issuer VARCHAR(35) COMMENT 'Issuer of the expected price', Asset_Currency VARCHAR(40) COMMENT 'First asset currency', Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer', Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency', Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_ammdeposit_amount_value (Amount_Value) ) COMMENT = 'Stores details for AMMDeposit transactions' ENGINE=InnoDB; /* Table: transactions_ammwithdraw Purpose: This table stores details for AMMWithdraw transactions, which remove liquidity from an AMM pool in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Amount_*: Withdrawal amount (value, currency, issuer). - EPrice_*: Expected price (value, currency, issuer). - Asset_*: First asset (currency, issuer). - Asset2_*: Second asset (currency, issuer). - Flags: Transaction flags (optional). - LPTokenIn_*: LP token input (value, currency, issuer). - SourceTag: Source tag identifying the transaction source (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Amount_Value: Withdrawn amount. - EPrice_Value: Expected price of withdrawal. - Asset_Currency: First asset currency. - Asset2_Currency: Second asset currency. - Flags: Integer flags for the transaction. - LPTokenIn_Value: LP token input value. - LPTokenIn_Currency: LP token input currency. - LPTokenIn_Issuer: LP token input issuer. - SourceTag: Integer source tag for the transaction. */ CREATE TABLE transactions_ammwithdraw ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Amount_Value DECIMAL(30,10) COMMENT 'Withdrawal amount', Amount_Currency VARCHAR(40) COMMENT 'Currency of the withdrawal amount', Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the withdrawal amount', Amount2_Value DECIMAL(30,10) COMMENT 'Second withdrawal amount', Amount2_Currency VARCHAR(40) COMMENT 'Currency of the second withdrawal amount', Amount2_Issuer VARCHAR(35) COMMENT 'Issuer of the second withdrawal amount', EPrice_Value DECIMAL(30,10) COMMENT 'Expected price of withdrawal', EPrice_Currency VARCHAR(40) COMMENT 'Currency of the expected price', EPrice_Issuer VARCHAR(35) COMMENT 'Issuer of the expected price', Asset_Currency VARCHAR(40) COMMENT 'First asset currency', Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer', Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency', Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags', LPTokenIn_Value DECIMAL(30,10) COMMENT 'LP token input value', LPTokenIn_Currency VARCHAR(40) COMMENT 'LP token input currency', LPTokenIn_Issuer VARCHAR(35) COMMENT 'LP token input issuer', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_ammwithdraw_amount_value (Amount_Value) ) COMMENT = 'Stores details for AMMWithdraw transactions' ENGINE=InnoDB; /* Table: transactions_ticketcreate Purpose: This table stores details for TicketCreate transactions, which create tickets for deferred transaction sequencing in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - TicketCount: Number of tickets to create. - Flags: Flags set on the transaction (optional). - TicketSequence: Ticket sequence number used in the transaction (optional). Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - TicketCount: Number of tickets generated. - Flags: Integer flags for the transaction. - TicketSequence: Integer ticket sequence number for the transaction. */ CREATE TABLE transactions_ticketcreate ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', TicketCount INT UNSIGNED NOT NULL COMMENT 'Number of tickets to create', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Flags set on the transaction', TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction', SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for TicketCreate transactions' ENGINE=InnoDB; /* Table: transactions_depositpreauth Purpose: This table stores details for DepositPreauth transactions, which preauthorize deposits to an account in XRPL. Structure: - id: Unique identifier for each entry. - transaction_id: Links to the transactions table. - Authorize: Account to authorize for deposits. - Unauthorize: Account to remove authorization from. Key Fields: - id: Primary key for this table. - transaction_id: Foreign key to transactions. - Authorize: Newly authorized account. - Unauthorize: Newly unauthorized account. */ CREATE TABLE transactions_depositpreauth ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', Authorize VARCHAR(35) COMMENT 'Account to authorize for deposits', Flags INT UNSIGNED DEFAULT NULL COMMENT 'Flags set on the transaction', Unauthorize VARCHAR(35) COMMENT 'Account to remove authorization from', Signers JSON COMMENT 'JSON array of signers for the transaction', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE ) COMMENT = 'Stores details for DepositPreauth transactions' ENGINE=InnoDB; /* Table: transactions_unlmodify Purpose: The transactions_unlmodify table stores specific details for UNLModify transactions in the XRP Ledger (XRPL). UNLModify transactions are specialized transactions used to modify the Unique Node List (UNL), a critical component of the XRPL that defines the set of trusted validators responsible for network consensus. These transactions allow network operators to enable or disable validators, ensuring the integrity and adaptability of the consensus mechanism. Structure The table consists of the following columns, each with a defined data type and purpose: id: BIGINT AUTO_INCREMENT PRIMARY KEY A unique identifier for each record in the table, automatically incremented to ensure distinct entries. transaction_id: BIGINT NOT NULL A foreign key that links each UNLModify transaction to its corresponding record in the base transactions table, maintaining referential integrity. UNLModifyDisabling: TINYINT A flag indicating the action being performed: 1 to disable a validator, or 0 to enable it. Validator: VARCHAR(255) The identifier (typically a public key) of the validator being modified by the transaction. LedgerSequence: INT UNSIGNED The ledger index at which the modification (enabling or disabling of the validator) takes effect in the XRPL. Key Fields The following fields are essential for understanding and querying the transactions_unlmodify table: id: Serves as the primary key, uniquely identifying each record in the table. transaction_id: Acts as a foreign key, linking to the transactions table and ensuring each UNLModify transaction is tied to its base transaction record. UNLModifyDisabling: Specifies whether the transaction enables or disables a validator, defining the core action of the modification. Validator: Identifies the specific validator affected by the transaction, critical for tracking changes to the UNL. LedgerSequence: Indicates the ledger index when the modification becomes effective, providing temporal context. */ CREATE TABLE transactions_unlmodify ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key referencing the transactions table', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', UNLModifyEnable VARCHAR(40) COMMENT 'Validator to enable', UNLModifyDisabling TINYINT COMMENT 'Flag indicating whether to disable (1) or enable (0) the validator', UNLModifyValidator VARCHAR(66) COMMENT 'Identifier of the validator being modified', Validator VARCHAR(66) COMMENT 'Identifier of the validator being modified', LedgerSequence INT UNSIGNED COMMENT 'Ledger index at which the change takes effect', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_unlmodify_UNLModifyValidator (UNLModifyValidator), INDEX idx_transactions_unlmodify_ledger_sequence (LedgerSequence) ) COMMENT = 'Stores specific details for UNLModify transactions' ENGINE=InnoDB; /* Table: transactions_oracleset Purpose: This table stores specific details for OracleSet transactions in the XRP Ledger (XRPL). OracleSet transactions are used to set or update oracle data, which provides external information (e.g., price feeds) to the ledger. This table captures fields like the oracle document ID, provider, update time, URI, price data series, and flags. Structure: - id: Unique identifier for each entry in this table. - transaction_id: Foreign key linking to the transactions table. - OracleDocumentID: Unique identifier for the oracle document. - Provider: Identifier of the data provider. - LastUpdateTime: Timestamp of the last update (in Ripple epoch time). - URI: URI pointing to additional oracle data. - PriceDataSeries: JSON array containing price data points. - Flags: Integer representing transaction flags. Key Fields: - id: Primary key for this table. - transaction_id: Links to the base transactions table. - OracleDocumentID: Identifies the specific oracle document. - Provider: Specifies the source of the oracle data. - LastUpdateTime: Indicates when the data was last updated. - URI: Provides a reference to external data. - PriceDataSeries: Stores structured price data. - Flags: Captures transaction-specific options. */ CREATE TABLE transactions_oracleset ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', AssetClass VARCHAR(64) COMMENT 'Asset class of the oracle data', OracleDocumentID VARCHAR(64) COMMENT 'Unique identifier for the oracle document', Provider VARCHAR(64) COMMENT 'Identifier of the data provider', LastUpdateTime INT UNSIGNED COMMENT 'Timestamp of the last update (Ripple epoch time)', URI VARCHAR(256) COMMENT 'URI pointing to additional oracle data', PriceDataSeries JSON COMMENT 'JSON array containing price data points', Flags INT UNSIGNED DEFAULT 0 COMMENT 'Transaction flags', LastLedgerSequence BIGINT UNSIGNED DEFAULT NULL COMMENT 'Last ledger sequence for transaction validity', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_oracleset_oracle_document_id (OracleDocumentID) ) COMMENT = 'Stores specific details for OracleSet transactions' ENGINE=InnoDB; /* Table: transactions_oracledelete Purpose: This table stores specific details for OracleDelete transactions in the XRP Ledger (XRPL). OracleDelete transactions are used to delete oracle data entries, which provide external information (e.g., price feeds) to the ledger. This table captures fields like the oracle document ID, flags, and the last ledger sequence for transaction validity. Structure: - id: Unique identifier for each entry in this table. - transaction_id: Foreign key linking to the transactions table. - OracleDocumentID: Unique identifier for the oracle document to be deleted. - Flags: Integer representing transaction flags. - LastLedgerSequence: The last ledger sequence in which the transaction is valid. Key Fields: - id: Primary key for this table. - transaction_id: Links to the base transactions table. - OracleDocumentID: Identifies the specific oracle document to delete. - Flags: Captures transaction-specific options. - LastLedgerSequence: Specifies the last ledger sequence for transaction validity. */ CREATE TABLE transactions_oracledelete ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning', OracleDocumentID VARCHAR(64) COMMENT 'Unique identifier for the oracle document to delete', Flags INT UNSIGNED DEFAULT 0 COMMENT 'Transaction flags', LastLedgerSequence BIGINT UNSIGNED DEFAULT NULL COMMENT 'Last ledger sequence for transaction validity', PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_transactions_oracledelete_oracle_document_id (OracleDocumentID) ) COMMENT = 'Stores specific details for OracleDelete transactions' ENGINE=InnoDB; CREATE TABLE delivered_amounts ( id BIGINT AUTO_INCREMENT, transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions table (transactions.id)', transaction_hash CHAR(64) NOT NULL COMMENT 'Transaction hash for easier lookup', transaction_amount DECIMAL(38, 18) NULL COMMENT 'Original amount specified in the transaction (value)', transaction_currency VARCHAR(40) NULL COMMENT 'Original currency specified in the transaction', transaction_issuer VARCHAR(35) NULL COMMENT 'Issuer for original issued currency in transaction', delivered_amount DECIMAL(38, 18) NULL COMMENT 'Actual delivered amount (value) from meta', delivered_currency VARCHAR(40) NULL COMMENT 'Actual delivered currency code (e.g., XRP, USD, hex) from meta', delivered_issuer VARCHAR(35) NULL COMMENT 'Issuer for actual delivered issued currency from meta', recipient_xrp_balance_change DECIMAL(20, 6) NULL COMMENT 'Calculated change in recipient AccountRoot XRP balance (drops / 1,000,000). Not an equivalent for issued currencies.', transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp of the transaction, for partitioning', -- Partitioning column PRIMARY KEY (id, transaction_date), UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date), FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE, INDEX idx_delivered_tx_hash (transaction_hash), INDEX idx_tx_amount (transaction_amount), INDEX idx_tx_currency (transaction_currency), INDEX idx_tx_issuer (transaction_issuer), INDEX idx_delivered_actual_amount (delivered_amount), INDEX idx_delivered_actual_currency (delivered_currency), INDEX idx_delivered_actual_issuer (delivered_issuer), INDEX idx_delivered_xrp_change (recipient_xrp_balance_change) ) COMMENT = 'Stores original tx amount, actual delivered amount/currency from metadata AND calculated recipient XRP balance change for Payment transactions.' ENGINE=InnoDB; CREATE TABLE xrpl_flags ( id INT AUTO_INCREMENT PRIMARY KEY, flag_value BIGINT UNSIGNED NOT NULL COMMENT 'The numeric (integer) value of the flag bitmask', flag_name VARCHAR(100) NOT NULL COMMENT 'The common name of the flag (e.g., tfPartialPayment)', context VARCHAR(50) NOT NULL COMMENT 'Context where flag applies (e.g., Global, Payment, OfferCreate, AccountSet, RippleState, AccountRoot)', description TEXT COMMENT 'Explanation of what the flag does', is_deprecated BOOLEAN DEFAULT FALSE COMMENT 'Indicates if the flag is deprecated', UNIQUE KEY unique_flag_context (flag_value, context) COMMENT 'Prevent duplicate flag values within the same context', INDEX idx_context (context) ) COMMENT='Definitions and descriptions of known XRP Ledger flags' ENGINE=InnoDB; -- Global Flags INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (2147483648, 'tfFullyCanonicalSig', 'Global', 'Has no effect since the RequireFullyCanonicalSig amendment. (Deprecated)', TRUE); -- Payment Transaction Flags INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (65536, 'tfNoRippleDirect', 'Payment', 'Do not use the default path, only use paths included in the Paths field. This is intended to force the transaction to take arbitrage opportunities. Most clients do not need this.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (131072, 'tfPartialPayment', 'Payment', 'If the specified Amount cannot be sent without spending more than SendMax, reduce the received amount instead of failing outright. See Partial Payments for more details.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (262144, 'tfLimitQuality', 'Payment', 'Only take paths where all the conversions have an input:output ratio that is equal or better than the ratio of Amount:SendMax. See Limit Quality for details.', FALSE); -- OfferCreate Transaction Flags INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (65536, 'tfPassive', 'OfferCreate', 'If enabled, the offer never consumes an offer on the books that has the same or worse quality (price). As a consequence, enabling this flag causes this transaction to create an Offer object in the ledger even if it could consume a matching offer in full. However, if this transaction does consume one or more crossing offers, it consumes as much as possible from the crossing offers with the best quality (price), even if doing so would consume an offer with worse quality than the passive offer.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (131072, 'tfImmediateOrCancel', 'OfferCreate', 'Treat the offer as an Immediate or Cancel order. If enabled, the offer never creates an Offer object in the ledger: it only consumes at most one offer already in the ledger. If no such offer exists to consume or the offer cannot be fully consumed, the transaction is rejected with tesSUCCESS and no effect. If the offer can be fully executed against an offer on the books, it does so without creating an Offer object.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (262144, 'tfFillOrKill', 'OfferCreate', 'Treat the offer as a Fill or Kill order. If enabled, the offer must be fully filled or not at all. If the offer cannot be fully filled, the transaction fails with tesSUCCESS and no effect. If the offer is fully filled, it is consumed and does not create an Offer object in the ledger. This flag is incompatible with tfPassive and tfImmediateOrCancel.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (524288, 'tfSell', 'OfferCreate', 'Exchange the entire TakerGets amount, even if it means obtaining more than the TakerPays amount in exchange.', FALSE); -- AccountSet Transaction Flags INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (65536, 'tfRequireDestTag', 'AccountSet', 'Require a destination tag to be specified for payments to this account. This flag is replaced by the asfRequireDest flag and SetFlag field in the AccountSet transaction type.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (131072, 'tfOptionalDestTag', 'AccountSet', 'Do not require a destination tag for payments to this account. This flag is replaced by clearing the asfRequireDest flag in the AccountSet transaction type.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (262144, 'tfRequireAuth', 'AccountSet', 'Require authorization for users to hold balances issued by this account. Can only be enabled if the account has no trust lines connected to it. This flag is replaced by the asfRequireAuth flag and SetFlag field in the AccountSet transaction type.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (524288, 'tfOptionalAuth', 'AccountSet', 'Do not require authorization for users to hold balances issued by this account. This flag is replaced by clearing the asfRequireAuth flag in the AccountSet transaction type.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (1048576, 'tfDisallowXRP', 'AccountSet', 'Discourage users from sending XRP to this account. This flag is replaced by the asfDisallowXRP flag and SetFlag field in the AccountSet transaction type.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (2097152, 'tfAllowXRP', 'AccountSet', 'Allow users to send XRP to this account. This flag is replaced by clearing the asfDisallowXRP flag in the AccountSet transaction type.', FALSE); -- RippleState Ledger Entry Flags INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (65536, 'lsfLowReserve', 'RippleState', 'This RippleState object contributes to the low account''s owner reserve.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (131072, 'lsfHighReserve', 'RippleState', 'This RippleState object contributes to the high account''s owner reserve.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (262144, 'lsfLowAuth', 'RippleState', 'The account on the low side of this relationship has authorized the other account to hold tokens it issues.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (524288, 'lsfHighAuth', 'RippleState', 'The account on the high side of this relationship has authorized the other account to hold tokens it issues.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (1048576, 'lsfLowNoRipple', 'RippleState', 'The account on the low side of this relationship has disabled rippling on this trust line.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (2097152, 'lsfHighNoRipple', 'RippleState', 'The account on the high side of this relationship has disabled rippling on this trust line.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (4194304, 'lsfLowFreeze', 'RippleState', 'The account on the low side of this relationship has frozen the trust line, preventing the other account from transferring the asset.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (8388608, 'lsfHighFreeze', 'RippleState', 'The account on the high side of this relationship has frozen the trust line, preventing the other account from transferring the asset.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (16777216, 'lsfAMMNode', 'RippleState', 'This RippleState object is associated with an Automated Market Maker (AMM) instance.', FALSE); -- AccountRoot Ledger Entry Flags INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (65536, 'lsfPasswordSpent', 'AccountRoot', 'The account has used its free SetRegularKey transaction.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (131072, 'lsfRequireDestTag', 'AccountRoot', 'The account requires a destination tag for incoming payments.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (262144, 'lsfRequireAuth', 'AccountRoot', 'The account requires authorization to hold balances issued by this account.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (524288, 'lsfDisallowXRP', 'AccountRoot', 'The account discourages users from sending XRP to it.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (1048576, 'lsfDisableMaster', 'AccountRoot', 'The account has disabled the master key pair.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (2097152, 'lsfNoFreeze', 'AccountRoot', 'The account has permanently given up the ability to freeze trust lines.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (4194304, 'lsfGlobalFreeze', 'AccountRoot', 'The account has frozen all assets it issues.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (8388608, 'lsfDefaultRipple', 'AccountRoot', 'The account has enabled rippling on its trust lines by default.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (16777216, 'lsfDepositAuth', 'AccountRoot', 'The account has enabled Deposit Authorization.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (33554432, 'lsfAllowTrustLineClawback', 'AccountRoot', 'The account has enabled clawback on its trust lines.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (67108864, 'lsfDisallowIncomingNFTokenOffer', 'AccountRoot', 'The account disallows incoming NFTokenOffers.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (134217728, 'lsfDisallowIncomingCheck', 'AccountRoot', 'The account disallows incoming Checks.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (268435456, 'lsfDisallowIncomingPayChan', 'AccountRoot', 'The account disallows incoming Payment Channels.', FALSE); INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated) VALUES (536870912, 'lsfDisallowIncomingTrustline', 'AccountRoot', 'The account disallows incoming Trust Lines.', FALSE); CREATE INDEX idx_oc_expiration_transaction_id ON transactions_offercreate (Expiration, transaction_id) ALGORITHM=INPLACE;