Hi, all, i got the itch (based on the UUID trimming idea for the file list), to figure out how many collisions we've got for a given prefix length, and i'm looking for confirmation as to whether this SQL does what i think it really does:
The goal is for it to output: a) an "N" value (prefix length) b) a uuid prefix N bytes long c) the number of collisions for that prefix. Does the following SQL actually do that? WITH prefix(n) AS ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) SELECT nn.n as n, substr(blob.uuid,1,n) as part, count(*) as cnt from blob, (select n from prefix) AS nn group by part having count(*) > 1 order by n, part ; which outputs... [stephan@host:~/cvs/fossil/fossil/src]$ f-query -e ~/tmp/uuid-prefixes.sql | head n part cnt 1 0 1768 1 1 1751 1 2 1757 1 3 1817 1 4 1824 1 5 1759 1 6 1757 1 7 1721 1 8 1724 [stephan@host:~/cvs/fossil/fossil/src]$ f-query -e ~/tmp/uuid-prefixes.sql | tail 6 bdbf14 2 6 be32eb 2 6 c1b1ba 2 6 c8735d 2 6 d07537 2 6 d43165 2 6 e980ba 2 6 ef17fb 2 6 f94f7e 2 7 b652b90 2 [stephan@host:~/cvs/fossil/fossil/src]$ f-query -e ~/tmp/uuid-prefixes.sql | wc -l 9325 :-? -- ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________ fossil-dev mailing list fossil-dev@lists.fossil-scm.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/fossil-dev
