[FR] cardano-db-sync - Create tests for new db-sync configuration

Start with the example db-sync configurations, SQL checks, and log output in this issue, then locate the existing regular cluster test suite and its database setup. Done means a separate or ordered suite covers configuration options and whitelists against populated data, including the documented tables, columns, logs, and database-reset requirements.

Feature Request: Automation of db-sync new configuration.

Here is an example of new config file:

  "insert_options": {
    "tx_cbor": "disable",
    "tx_out": {
      "value": "bootstrap",
      "force_tx_in": true,
      "use_address_table": false,
    },
    "ledger": "enable",
    "shelley": {
      "enable": true
    },
    "multi_asset": {
      "enable": true
    },
    "metadata": {
      "enable": true
    },
    "plutus": {
      "enable": true
    },
    "governance": "enable",
    "offchain_pool_data": "enable",
    "json_type": "text"
  },

Those tests should probably be all run in its own suite for convenience.

However taking into account that there are also options for filtering/whitelisting data those tests should operate on a blockchain that has already some data - at least in tables/columns that we want to filter - so that may mean that maybe tests should be run at the end - after regular cluster tests.

Examples for whitelisting

• METADATA KEYS:

    "metadata": {
      "enable": true,
      "keys": [1564, 6862]
    },

sancho_test_config_shelley_whitelist_metadata=# select id, key, json, tx_id from tx_metadata;
 id | key  |                                                                                      json                                                                                       | tx_id 
----+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------
  1 | 1564 | [2, []]                                                                                                                                                                         | 28966
  2 | 1564 | [2, []]                                                                                                                                                                         | 29177
  3 | 1564 | [2, []]                                                                                                                                                                         | 29288
  4 | 6862 | {"pools": [{"id": "8dd89d9dbd25641c20734e5c4f6f17affeadc066812ba402874c36ba", "weight": 100}]}                                                                                  | 29442
  5 | 6862 | {"pools": [{"id": "8dd89d9dbd25641c20734e5c4f6f17affeadc066812ba402874c36ba", "weight": 100}]}                                                                                  | 32804
(5 rows)

• MULTIASSET POLICIES:

    "multi_asset": {
      "enable": true,
    "policies": ["67f33146617a5e61936081db3b2117cbf59bd2123748f58ac9678656","0a1fa5015c69f9535c4c984a6fcb7c16b4f33cd52ffc840488f8459b"]
    },

sancho_test_config_shelley_whitelist_multiple_assets=# select * from multi_asset LIMIT 10;
 id |                           policy                           |   name   |                 fingerprint                  
----+------------------------------------------------------------+----------+----------------------------------------------
  1 | \x67f33146617a5e61936081db3b2117cbf59bd2123748f58ac9678656 | \xeeee   | asset16a8u8rwn5ateppld24q98t0td9yxgn6g476vle
  2 | \x0a1fa5015c69f9535c4c984a6fcb7c16b4f33cd52ffc840488f8459b | \xeeee   | asset13kapweshcckzk47lz7fzvxucnhpqxr4f9muwjv
  3 | \x0a1fa5015c69f9535c4c984a6fcb7c16b4f33cd52ffc840488f8459b | \xeeeeee | asset1f5fenzvzr6p6g4y0hqzdeee03lpzrfg2eyrhat
(3 rows)

• STAKE ADDRESSES:

    "shelley": {
      "enable": true,
      "stake_addresses": ["e083d27f03b197750ee41b7dca02c806718164b934b0fbfa747349e8f2", 
      "e0248f556b733c3ef24899ae0609d3796198d5470192304c4894dd85cb", 
      "e0101c5bfa799985a6e96adbb5859e90cbe4a0e4edcbef408a3622558b"]
    },

Example of test flow

Tests case for new feature - extracting address table

We start db-sync with this config and check:

    "tx_out": {
      "value": "consumed",
      "force_tx_in": true,
      "use_address_table": true,
    },

• if proper settings values

soptPruneConsumeMigration = PruneConsumeMigration {pcmPruneTxOut = False, pcmConsumedTxOut = True, pcmSkipTxIn = False}
ioTxOutTableType = TxOutVariantAddress

are reflected in the db-sync logs:

PGPASSFILE=config/pgpass-sancho-regular db-sync-node/bin/cardano-db-sync --config config/sancho-config.json --socket-path ../sancho/node.socket --schema-dir schema/ --state-dir ledger-state/sancho_regular
[db-sync-node:Info:6] [2024-09-24 13:21:21.63 UTC] Version number: 13.5.0.2
[db-sync-node:Info:6] [2024-09-24 13:21:21.63 UTC] Git hash: dirty
[db-sync-node:Info:6] [2024-09-24 13:21:21.63 UTC] Enviroment variable DbSyncAbortOnPanic: True
[db-sync-node:Info:6] [2024-09-24 13:21:21.63 UTC] SyncNodeParams {enpConfigFile = ConfigFile {unConfigFile = "config/sancho-config.json"}, enpSocketPath = SocketPath {unSocketPath = "../sancho/node.socket"}, enpMaybeLedgerStateDir = Just (LedgerStateDir {unLedgerStateDir = "ledger-state/sancho_regular"}), enpMigrationDir = MigrationDir "schema/", enpPGPassSource = PGPassDefaultEnv, enpEpochDisabled = False, enpHasCache = True, enpSkipFix = False, enpOnlyFix = False, enpForceIndexes = False, enpHasInOut = True, enpSnEveryFollowing = 500, enpSnEveryLagging = 10000, enpMaybeRollback = Nothing}
[db-sync-node:Info:6] [2024-09-24 13:21:21.63 UTC] SyncOptions {soptEpochAndCacheEnabled = True, soptAbortOnInvalid = True, soptCache = True, soptSkipFix = False, soptOnlyFix = False, soptPruneConsumeMigration = PruneConsumeMigration {pcmPruneTxOut = False, pcmConsumedTxOut = True, pcmSkipTxIn = True}, soptInsertOptions = InsertOptions {ioTxCBOR = False, ioInOut = True, ioUseLedger = True, ioShelley = True, ioRewards = True, ioMultiAssets = True, ioMetadata = True, ioKeepMetadataNames = Nothing, ioPlutusExtra = True, ioOffChainPoolData = True, ioPoolStats = False, ioGov = True, ioRemoveJsonbFromSchema = False, ioTxOutTableType = TxOutVariantAddress}, snapshotEveryFollowing = 500, snapshotEveryLagging = 10000}

• if there is new table named address and has entries:

sancho_test_address_table=# select * from address limit 3;
 id |                             address                             |                                              raw                                               | has_script | payment_cred | stake_address_id 
----+-----------------------------------------------------------------+------------------------------------------------------------------------------------------------+------------+--------------+------------------
  1 | FHnt4NL7yPXqn7xha3WB99wYLxAc1FhceD3D1pQWaCthk9RYB46aGb6Tbq2KxV5 | \x82d818582483581c3f5eb76562f6db207efc47b65358d3ae00532e713a53a80f28d50ccca1024104001a61c9dd78 | f          |              |                 
  2 | FHnt4NL7yPXwj8m191s48v1RZtQqA2sVHpamzStuXTuAnzYUSR6hRPqhYmW3MY4 | \x82d818582483581c6795076654af812180cc1690a5e83183cb9ee8c7b3e4071ebcddfb89a1024104001adfae7639 | f          |              |                 
  3 | FHnt4NL7yPXzVZ5xexcb7rWqCYWuFU7y6Pp4tLTiv6txhDcpQ2m7AFGMirsi1F1 | \x82d818582483581c7a4786ea46c1b33e6b74d32182b70692bcdd93c9bff81ccc3aa21ce5a1024104001ab6a90594 | f          |              |                 
(3 rows)

• if there is a new column in tx_out table named consumed_by_tx_id and it has some non null entries:

sancho_normal=# select count(*) from tx_out;
 count 
-------
 52036
(1 row)

```sql
sancho_normal=# select count(*) from tx_out where consumed_by_tx_id is not null;
 count 
-------
  1015
(1 row)

• We also need to check if tx_in table has entries - if "force_tx_in" was not present we would need to check if table is empty.

sancho_normal=# select count(*) from tx_in;
 count 
-------
  1015
(1 row)

Possible difficulties

If user starts db-sync with some set of config values it is not possible to stop and start and continue with different values.

For example - from using "consumed" for tx_out user can't go back to "enabled", only "prune" will be accepted.
From "prune" we can't go back to "consumed".

Once "use_address_table" is set to true, user has to continue with that setting or drop database and start a new sync with false value for that setting on a new empty database.

    "tx_out": {
      "value": "consumed",
      "force_tx_in": true,
      "use_address_table": true,
    }

That means that db-sync database should be deleted before each new config test - other option could be to try to force some order of tests but I am not sure if this would be the best approach.