Home
Andrew Dolgov edited this page 2 years ago

Known issues

  • On 32-bit systems 64-bit perceptual hash is limited to a 32-bit integer (i.e. only half of the hash is actually stored and used).

How to check database contents/hash values easily

DROP TABLE IF EXISTS phash_check_setof_type cascade;
CREATE TABLE phash_check_setof_type (created_at timestamp, url text, phash bit(64), owner_uid int, distance int);

CREATE OR REPLACE FUNCTION phash_check_url(text) RETURNS SETOF phash_check_setof_type AS $$
   SELECT created_at,
      url,
      phash::bit(64),
      owner_uid,
      unique_1bits(
         (SELECT phash FROM ttrss_plugin_img_phash_urls WHERE url LIKE '%' ||
            SUBSTRING($1 FROM position('//' IN $1))),
         phash) AS distance
      FROM ttrss_plugin_img_phash_urls
      ORDER BY distance limit 10
$$ LANGUAGE SQL;

Use the function like this:

select * from phash_check_url('http://example.com/something.jpg');