Skip to content

Instantly share code, notes, and snippets.

@samuelharmer

samuelharmer/find_minimally_unique_partial_digest.sql

Last active July 30, 2018 20:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save samuelharmer/2f81c047a2b4563700791c86ad501334 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save samuelharmer/2f81c047a2b4563700791c86ad501334 to your computer and use it in GitHub Desktop.
Download ZIP
Recursive CTEs in SQLite
Raw
find_minimally_unique_partial_digest.sql
BEGIN TRANSACTION;
DROP TABLE IF EXISTS File;
CREATE TABLE File (hash_digest CHAR(32) PRIMARY KEY NOT NULL);
INSERT INTO File
(hash_digest) -- word which produces sha256 digest
VALUES
('288971671685b8da56623362c82e1ead68186c5150a35e3b35b5ef74cd7ceebc'), -- onion
('28897ea1b742813bdd54a7cad5f43af5b2b2198419184b33ce5f5c61eeea90aa'), -- eavesdrop
('2898a07b2cf23dda8530b14b6aa522e67b002886d170c02219acc3598fdb50f3'), -- virus
('28263836ea961ac7e409f8d6311e2b91e6b97bf2cb446326ddf77cb74ef30809'), -- tune
('288965a1f2c883c71bff8a4b3a1b76cc77d11e65f70910d5feff411a4e5fe1b3'), -- management
('28d9e7bbbf81137ec80d8e02241329c02d7f629960e3afd7379d4e3855d7d662'); -- relationship
COMMIT;
WITH RECURSIVE len(x,partial_digest,matches) AS (
SELECT
1,
substr('28897ea1b742813bdd54a7cad5f43af5b2b2198419184b33ce5f5c61eeea90aa' , 1, 1)||'%',
(SELECT count(*) FROM File WHERE hash_digest LIKE substr('28897ea1b742813bdd54a7cad5f43af5b2b2198419184b33ce5f5c61eeea90aa' , 1, 1)||'%')
UNION ALL
SELECT
x+1,
substr('28897ea1b742813bdd54a7cad5f43af5b2b2198419184b33ce5f5c61eeea90aa' , 1, x+1)||'%',
(SELECT count(*) FROM File WHERE hash_digest LIKE substr('28897ea1b742813bdd54a7cad5f43af5b2b2198419184b33ce5f5c61eeea90aa' , 1, x+1)||'%')
FROM
len
WHERE
matches > 1
)
SELECT * FROM len WHERE matches = 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment