Ensure there are no duplicated script_pubkeys in sqlite

[afilini]

Description

Add a UNIQUE constraint on the script_pubkeys table so that it doesn't grow constantly when caching new addresses.

Fixes #801

Notes to the reviewers

Adding it to the 0.25 milestone since it's just a bugfix.

Still in draft because I need to add extra migration queries to clean up existing dbs.

Checklists

All Submissions:

• I've signed all my commits
• I followed the contribution guidelines
• I ran cargo fmt and cargo clippy before committing

Bugfixes:

• This pull request breaks the existing API
• I've added tests to reproduce the issue which are now passing
• I'm linking the issue being fixed by this PR

[notmandatory]

Oops, I didn't mean to remove this from 0.25, I'll hold off on the release until this is ready.

[notmandatory]

Here's a script to remove duplicates:

ALTER TABLE script_pubkeys RENAME TO script_pubkeys_old;
DROP INDEX idx_keychain_child;
DROP INDEX idx_script;
CREATE TABLE script_pubkeys (keychain TEXT, child INTEGER, script BLOB);
CREATE INDEX idx_keychain_child ON script_pubkeys(keychain, child);
CREATE INDEX idx_script ON script_pubkeys(script);
CREATE UNIQUE INDEX idx_script_pks_unique ON script_pubkeys(keychain, child);
INSERT OR REPLACE INTO script_pubkeys SELECT keychain, child, script FROM script_pubkeys_old;
DROP TABLE script_pubkeys_old;

EDIT: I added dropping and recreating the script_pubkey indexes because I'm not sure they are updated when the table they are indexing is renamed and a new one is created.

There's also a way to do it without creating/dropping tables but I think it has some limit on the number of rowids in the NOT IN section:

DELETE FROM script_pubkeys WHERE rowid NOT IN (SELECT min(rowid) FROM script_pubkeys GROUP BY keychain,child);

[notmandatory]

This should be complete now so I marked it as ready for review. I verified the new sqlite migration scripts manually against a db with inserted duplicate script_pubkeys rows.

[notmandatory]

ACK b5fcddc