133 lines
5.3 KiB
SQL
133 lines
5.3 KiB
SQL
-- file: validation_sql/SQL_VALIDATION_RAYDIUM_CLMM_0_7_49.sql
|
|
-- Raydium CLMM final validation SQL for 0.7.49.
|
|
|
|
-- 1. CLMM coverage summary.
|
|
SELECT
|
|
decoder_code,
|
|
COUNT(*) AS listed_entry_count,
|
|
SUM(CASE WHEN local_event_kind IS NOT NULL AND local_event_kind <> '' THEN 1 ELSE 0 END) AS decoded_entry_count,
|
|
SUM(CASE WHEN observed_count > 0 THEN 1 ELSE 0 END) AS observed_entry_count,
|
|
SUM(CASE WHEN materialized_count > 0 THEN 1 ELSE 0 END) AS materialized_entry_count,
|
|
COALESCE(SUM(observed_count), 0) AS total_observed_count,
|
|
COALESCE(SUM(materialized_count), 0) AS total_materialized_count,
|
|
COALESCE(SUM(trade_count), 0) AS trade_count
|
|
FROM k_sol_dex_event_coverage_entries
|
|
WHERE decoder_code = 'raydium_clmm'
|
|
GROUP BY decoder_code;
|
|
|
|
-- 2. Residual CLMM instruction audits. Expected: zero rows.
|
|
SELECT
|
|
json_extract(payload_json, '$.discriminatorHex') AS discriminator_hex,
|
|
COUNT(*) AS residual_audit_count,
|
|
COUNT(DISTINCT transaction_id) AS transaction_count
|
|
FROM k_sol_dex_decoded_events
|
|
WHERE protocol_name = 'raydium_clmm'
|
|
AND event_kind = 'raydium_clmm.instruction_audit'
|
|
GROUP BY discriminator_hex
|
|
ORDER BY residual_audit_count DESC, discriminator_hex;
|
|
|
|
-- 3. Redundant upstream fallback matches for locally covered CLMM entries. Expected: zero rows.
|
|
SELECT
|
|
json_extract(ug.payload_json, '$.upstreamDecoderCode') AS upstream_decoder_code,
|
|
json_extract(ug.payload_json, '$.upstreamEntryName') AS entry_name,
|
|
json_extract(ug.payload_json, '$.upstreamDiscriminatorHex') AS discriminator_hex,
|
|
json_extract(ug.payload_json, '$.upstreamSourceRepo') AS source_repo,
|
|
COUNT(*) AS fallback_count,
|
|
COUNT(DISTINCT ug.transaction_id) AS tx_count
|
|
FROM k_sol_dex_decoded_events ug
|
|
JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = json_extract(ug.payload_json, '$.upstreamDecoderCode')
|
|
AND ce.entry_name = json_extract(ug.payload_json, '$.upstreamEntryName')
|
|
AND ce.discriminator_hex = json_extract(ug.payload_json, '$.upstreamDiscriminatorHex')
|
|
AND ce.local_event_kind IS NOT NULL
|
|
AND ce.local_event_kind <> ''
|
|
WHERE ug.protocol_name = 'upstream_git'
|
|
AND ug.event_kind = 'upstream_git.instruction_match'
|
|
AND json_extract(ug.payload_json, '$.upstreamDecoderCode') = 'raydium_clmm'
|
|
GROUP BY upstream_decoder_code, entry_name, discriminator_hex, source_repo
|
|
ORDER BY fallback_count DESC, entry_name;
|
|
|
|
-- 4. Instruction-observation links still pointing to redundant upstream fallback rows. Expected: zero rows.
|
|
SELECT
|
|
json_extract(ug.payload_json, '$.upstreamDecoderCode') AS upstream_decoder_code,
|
|
json_extract(ug.payload_json, '$.upstreamEntryName') AS entry_name,
|
|
json_extract(ug.payload_json, '$.upstreamDiscriminatorHex') AS upstream_discriminator_hex,
|
|
COUNT(*) AS linked_observation_count
|
|
FROM k_sol_instruction_observations io
|
|
JOIN k_sol_dex_decoded_events ug
|
|
ON ug.id = io.decoded_event_id
|
|
JOIN k_sol_dex_event_coverage_entries ce
|
|
ON ce.decoder_code = json_extract(ug.payload_json, '$.upstreamDecoderCode')
|
|
AND ce.entry_name = json_extract(ug.payload_json, '$.upstreamEntryName')
|
|
AND ce.discriminator_hex = json_extract(ug.payload_json, '$.upstreamDiscriminatorHex')
|
|
AND ce.local_event_kind IS NOT NULL
|
|
AND ce.local_event_kind <> ''
|
|
WHERE ug.protocol_name = 'upstream_git'
|
|
AND ug.event_kind = 'upstream_git.instruction_match'
|
|
AND json_extract(ug.payload_json, '$.upstreamDecoderCode') = 'raydium_clmm'
|
|
GROUP BY
|
|
json_extract(ug.payload_json, '$.upstreamDecoderCode'),
|
|
json_extract(ug.payload_json, '$.upstreamEntryName'),
|
|
json_extract(ug.payload_json, '$.upstreamDiscriminatorHex')
|
|
ORDER BY linked_observation_count DESC, entry_name;
|
|
|
|
-- 5. Any non-swap CLMM trade. Expected: zero rows.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(te.id) AS trade_count
|
|
FROM k_sol_dex_decoded_events de
|
|
LEFT JOIN k_sol_trade_events te
|
|
ON te.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'raydium_clmm'
|
|
AND de.event_kind NOT IN (
|
|
'raydium_clmm.swap',
|
|
'raydium_clmm.swap_v2'
|
|
)
|
|
GROUP BY de.event_kind
|
|
HAVING COUNT(te.id) > 0
|
|
ORDER BY trade_count DESC, de.event_kind;
|
|
|
|
-- 6. Failed transaction materialization guard. Expected: zero rows.
|
|
SELECT
|
|
de.event_kind,
|
|
COUNT(*) AS decoded_count,
|
|
COUNT(le.id) AS liquidity_count,
|
|
COUNT(fe.id) AS fee_count,
|
|
COUNT(re.id) AS reward_count,
|
|
COUNT(pa.id) AS admin_count,
|
|
COUNT(ple.id) AS lifecycle_count,
|
|
COUNT(oe.id) AS orderbook_count,
|
|
COUNT(te.id) AS trade_count
|
|
FROM k_sol_dex_decoded_events de
|
|
JOIN k_sol_chain_transactions tx
|
|
ON tx.id = de.transaction_id
|
|
LEFT JOIN k_sol_liquidity_events le
|
|
ON le.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_fee_events fe
|
|
ON fe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_reward_events re
|
|
ON re.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_admin_events pa
|
|
ON pa.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_pool_lifecycle_events ple
|
|
ON ple.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_orderbook_events oe
|
|
ON oe.decoded_event_id = de.id
|
|
LEFT JOIN k_sol_trade_events te
|
|
ON te.decoded_event_id = de.id
|
|
WHERE de.protocol_name = 'raydium_clmm'
|
|
AND tx.err_json IS NOT NULL
|
|
AND tx.err_json <> ''
|
|
AND tx.err_json <> 'null'
|
|
GROUP BY de.event_kind
|
|
HAVING
|
|
COUNT(le.id) > 0
|
|
OR COUNT(fe.id) > 0
|
|
OR COUNT(re.id) > 0
|
|
OR COUNT(pa.id) > 0
|
|
OR COUNT(ple.id) > 0
|
|
OR COUNT(oe.id) > 0
|
|
OR COUNT(te.id) > 0
|
|
ORDER BY de.event_kind;
|