← Watch feed
plugins/sql: add listchainmoves and listchannelmoves.
Public commit record
73/100 · Adequate
What the developer wrote
Authored by Rusty Russell
plugins/sql: add listchainmoves and listchannelmoves.
And note the other commands in See Also section.
Note that this means handling the "outpoint" type.
Signed-off-by: Rusty Russell <rusty@rustcorp.com.au>
Changelog-Added: JSON-RPC: `sql` plugin now supports `chainmoves` and `channelmoves` tables.
✓ Specific, descriptive subject✓ Names a concrete action or component✓ Provides detailed explanatory context
What changed, and why it matters
This commit adds two new read-only SQL views, chainmoves and channelmoves, to the existing sql plugin. It also adds support for a new 'outpoint' data type (a transaction ID plus output number) and updates documentation and tests. There is no security-relevant change: no new permissions, no parsing of untrusted input beyond what the plugin already handles, and no changes to cryptographic or network code.
Recommended action
No security action required. This is a routine feature addition. Reviewers may optionally verify that the new outpoint binding uses SQLITE_STATIC consistently with other text types and that the generated SQL schema matches the documented columns.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The patch extends Core Lightning’s sql plugin to expose listchainmoves and listchannelmoves RPC results as SQL tables. Changes include: adding the two RPCs to SQL_LISTRPCS in plugins/Makefile; adding FIELD_OUTPOINT mapped to SQLite TEXT in plugins/sql.c; adding indices on account_id for the two new tables; binding outpoint values as text during JSON-to-SQLite insertion; updating schema see_also references; and adding tests verifying schema shape and data fidelity. The new code paths are additive and mirror existing handling for short_channel_id, string, hash, txid, etc.
Changed components
plugins/sql.cplugins/Makefiledoc/schemas/sql-template.jsoncontrib/msggen/msggen/schema.jsontests/test_coinmoves.pytests/test_plugin.pyInspect captured patch +115 / −1
diff --git a/contrib/msggen/msggen/schema.json b/contrib/msggen/msggen/schema.json
index 1074b683..ae0baa69 100644
--- a/contrib/msggen/msggen/schema.json
+++ b/contrib/msggen/msggen/schema.json
@@ -33031,8 +33031,17 @@
],
"see_also": [
"lightning-listtransactions(7)",
+ "lightning-listhtlcs(7)",
+ "lightning-listinvoices(7)",
+ "lightning-listoffers(7)",
"lightning-listchannels(7)",
"lightning-listpeers(7)",
+ "lightning-listpeerchannels(7)",
+ "lightning-listsendpays(7)",
+ "lightning-listchainmoves(7)",
+ "lightning-listchannelmoves(7)",
+ "lightning-bkpr-listaccountevents(7)",
+ "lightning-bkpr-listincome(7)",
"lightning-listnodes(7)",
"lightning-listforwards(7)"
],
diff --git a/doc/schemas/sql-template.json b/doc/schemas/sql-template.json
index eb332746..01c7f36a 100644
--- a/doc/schemas/sql-template.json
+++ b/doc/schemas/sql-template.json
@@ -123,8 +123,17 @@
],
"see_also": [
"lightning-listtransactions(7)",
+ "lightning-listhtlcs(7)",
+ "lightning-listinvoices(7)",
+ "lightning-listoffers(7)",
"lightning-listchannels(7)",
"lightning-listpeers(7)",
+ "lightning-listpeerchannels(7)",
+ "lightning-listsendpays(7)",
+ "lightning-listchainmoves(7)",
+ "lightning-listchannelmoves(7)",
+ "lightning-bkpr-listaccountevents(7)",
+ "lightning-bkpr-listincome(7)",
"lightning-listnodes(7)",
"lightning-listforwards(7)"
],
diff --git a/plugins/Makefile b/plugins/Makefile
index fc3f2c8c..6efb6c23 100644
--- a/plugins/Makefile
+++ b/plugins/Makefile
@@ -256,7 +256,7 @@ plugins/recover: common/gossmap.o common/sciddir_or_pubkey.o common/fp16.o $(PL
plugins/recklessrpc: $(PLUGIN_RECKLESSRPC_OBJS) $(PLUGIN_LIB_OBJS) $(PLUGIN_COMMON_OBJS) $(JSMN_OBJS)
# This covers all the low-level list RPCs which return simple arrays
-SQL_LISTRPCS := listchannels listforwards listhtlcs listinvoices listnodes listoffers listpeers listpeerchannels listclosedchannels listtransactions listsendpays bkpr-listaccountevents bkpr-listincome
+SQL_LISTRPCS := listchannels listforwards listhtlcs listinvoices listnodes listoffers listpeers listpeerchannels listclosedchannels listtransactions listsendpays listchainmoves listchannelmoves bkpr-listaccountevents bkpr-listincome
SQL_LISTRPCS_SCHEMAS := $(foreach l,$(SQL_LISTRPCS),doc/schemas/$l.json)
SQL_SCHEMA_PARTS := $(foreach l,$(SQL_LISTRPCS), plugins/sql-schema_$l_gen.h)
diff --git a/plugins/sql.c b/plugins/sql.c
index d61452f4..0a61dd4a 100644
--- a/plugins/sql.c
+++ b/plugins/sql.c
@@ -51,6 +51,7 @@ enum fieldtype {
FIELD_NUMBER,
FIELD_STRING,
FIELD_SCID,
+ FIELD_OUTPOINT,
};
struct fieldtypemap {
@@ -74,6 +75,7 @@ static const struct fieldtypemap fieldtypemap[] = {
{ "number", "REAL" }, /* FIELD_NUMBER */
{ "string", "TEXT" }, /* FIELD_STRING */
{ "short_channel_id", "TEXT" }, /* FIELD_SCID */
+ { "outpoint", "TEXT" }, /* FIELD_OUTPOINT */
};
struct column {
@@ -171,6 +173,14 @@ static const struct index indices[] = {
"transactions",
{ "hash", NULL },
},
+ {
+ "chainmoves",
+ { "account_id", NULL },
+ },
+ {
+ "channelmoves",
+ { "account_id", NULL },
+ },
};
static enum fieldtype find_fieldtype(const jsmntok_t *name)
@@ -626,6 +636,7 @@ static struct command_result *process_json_obj(struct command *cmd,
break;
case FIELD_SCID:
case FIELD_STRING:
+ case FIELD_OUTPOINT:
sqlite3_bind_text(stmt, (*sqloff)++, buf + coltok->start,
coltok->end - coltok->start,
SQLITE_STATIC);
diff --git a/tests/test_coinmoves.py b/tests/test_coinmoves.py
index 6db3e2d4..92eac09e 100644
--- a/tests/test_coinmoves.py
+++ b/tests/test_coinmoves.py
@@ -11,6 +11,20 @@ import time
from pyln.testing.utils import EXPERIMENTAL_DUAL_FUND
+# While we're doing this, check sql plugin's representation too.
+def check_sql(node, kind, expected):
+ columns = only_one(node.rpc.listsqlschemas(kind)['schemas'])['columns']
+ ret = node.rpc.sql(f"SELECT * FROM {kind}")
+ assert len(ret['rows']) == len(expected)
+ for row, e in zip(ret['rows'], expected):
+ assert len(row) == len(columns)
+ for val, col in zip(row, columns):
+ if col['name'] in e:
+ assert val == e[col['name']], f"{col['name']} is {val} not {e[col['name']]}: ({row} vs {columns})"
+ elif col['name'] not in ('rowid', 'timestamp'):
+ assert val is None, f"{col['name']} is not None ({row} vs {columns})"
+
+
def check_moves(moves, expected):
# Can't predict timestamp
for m in moves:
@@ -23,10 +37,17 @@ def check_moves(moves, expected):
def check_channel_moves(node, expected):
check_moves(node.rpc.listchannelmoves()['channelmoves'], expected)
+ check_sql(node, "channelmoves", expected)
def check_chain_moves(node, expected):
check_moves(node.rpc.listchainmoves()['chainmoves'], expected)
+ check_sql(node, "chainmoves", expected)
+ # Check extra_tags.
+ for e in expected:
+ rows = node.rpc.sql(f"SELECT cet.extra_tags FROM chainmoves_extra_tags cet LEFT JOIN chainmoves cm ON cet.row = cm.rowid WHERE cm.created_index={e['created_index']} ORDER BY cm.created_index, cet.arrindex;")['rows']
+ extra_tags = [only_one(row) for row in rows]
+ assert extra_tags == e['extra_tags']
def account_balances(accounts):
diff --git a/tests/test_plugin.py b/tests/test_plugin.py
index 2ac9da76..3ef2fde0 100644
--- a/tests/test_plugin.py
+++ b/tests/test_plugin.py
@@ -3756,6 +3756,65 @@ def test_sql(node_factory, bitcoind):
'type': 'msat'},
{'name': 'scriptPubKey',
'type': 'hex'}]},
+ 'chainmoves': {
+ 'indices': [['account_id']],
+ 'columns': [{'name': 'created_index',
+ 'type': 'u64'},
+ {'name': 'account_id',
+ 'type': 'string'},
+ {'name': 'credit_msat',
+ 'type': 'msat'},
+ {'name': 'debit_msat',
+ 'type': 'msat'},
+ {'name': 'timestamp',
+ 'type': 'u64'},
+ {'name': 'primary_tag',
+ 'type': 'string'},
+ {'name': 'peer_id',
+ 'type': 'pubkey'},
+ {'name': 'originating_account',
+ 'type': 'string'},
+ {'name': 'spending_txid',
+ 'type': 'txid'},
+ {'name': 'utxo',
+ 'type': 'outpoint'},
+ {'name': 'payment_hash',
+ 'type': 'hash'},
+ {'name': 'output_msat',
+ 'type': 'msat'},
+ {'name': 'output_count',
+ 'type': 'u32'},
+ {'name': 'blockheight',
+ 'type': 'u32'}]},
+ 'chainmoves_extra_tags': {
+ 'columns': [{'name': 'row',
+ 'type': 'u64'},
+ {'name': 'arrindex',
+ 'type': 'u64'},
+ {'name': 'extra_tags',
+ 'type': 'string'}]},
+ 'channelmoves': {
+ 'indices': [['account_id']],
+ 'columns': [{'name': 'created_index',
+ 'type': 'u64'},
+ {'name': 'account_id',
+ 'type': 'string'},
+ {'name': 'credit_msat',
+ 'type': 'msat'},
+ {'name': 'debit_msat',
+ 'type': 'msat'},
+ {'name': 'timestamp',
+ 'type': 'u64'},
+ {'name': 'primary_tag',
+ 'type': 'string'},
+ {'name': 'payment_hash',
+ 'type': 'hash'},
+ {'name': 'part_id',
+ 'type': 'u64'},
+ {'name': 'group_id',
+ 'type': 'u64'},
+ {'name': 'fees_msat',
+ 'type': 'msat'}]},
'bkpr_accountevents': {
'columns': [{'name': 'account',
'type': 'string'},
@@ -3822,6 +3881,7 @@ def test_sql(node_factory, bitcoind):
'hex': 'BLOB',
'hash': 'BLOB',
'txid': 'BLOB',
+ 'outpoint': 'TEXT',
'pubkey': 'BLOB',
'secret': 'BLOB',
'number': 'REAL',
@@ -3902,6 +3962,10 @@ def test_sql(node_factory, bitcoind):
val += ""
elif col['type'] == "short_channel_id":
assert len(val.split('x')) == 3
+ elif col['type'] == "outpoint":
+ txid, vout = val.split(':')
+ assert len(bytes.fromhex(txid)) == 32
+ int(vout)
else:
assert False
Risk score
Our methodology →Why this scored 15/100
Human-validated context
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
No validated notes yet.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.