/hydrus/ - Hydrus Network

Hey, this sounds interesting. I've done similar a couple of times just for fun to report to people the top tags–which are usually some combination of 'female', 'breasts', '1girl', and 'touhou'.

I expect to have some gui out at some point that'll present this to you as a tag cloud, or as a new tab of popular tags on the tag autocomplete, but if you want to do it in SQLite, you'll want something like this:

Open the sqlite3 executable in your db folder and type (you should be able to copy/paste this):

.open client.db
ATTACH "client.caches.db" as cach;
ATTACH "client.master.db" as mast;
SELECT service_id, name FROM services;
(make a note of the file service id and tag service id you want to harvest from)

For popular tags on a service:

SELECT current_count, namespace, subtag FROM combined_files_ac_cache_(tag_service_id) NATURAL JOIN tags NATURAL JOIN namespaces NATURAL JOIN subtags ORDER BY current_count DESC LIMIT 25;

For popular tags on a service cross-references with your files (typically, you'll want your 'my files' service):

SELECT current_count, namespace, subtag FROM specific_ac_cache_(file_service_id)_(tag_service_id) NATURAL JOIN tags NATURAL JOIN namespaces NATURAL JOIN subtags ORDER BY current_count DESC LIMIT 25;

Replace the id just as the number, like 'specific_ac_cache_2_4'.

When you are done, go:

.exit

If you want, you can add a 'namespace="series"' just before the ORDER BY to specify just series tags and so on.

>5 million 'female' tags

>>8878

>>8872

Ah, sorry, I missed the inbox part in my first read.

Inbox is going to be more complicated. The numbers I am pulling here are from my 'autocomplete cache' for difference file/tag service cross-sections. I pre-compute these numbers and increment/decrement them as the underlying tags and files change, which also makes them easy for quick selection and sort. But it also means they are service-wide.

If you want to get a smaller slice, you'll want to count up the tags manually, which could take a few minutes.

It is probably doable to try and count all that shit simultaneously to sorting and displaying the result, but it'll be a headache and take ages to see if the query is wrong, so I would recommend something like this:

WARNING: This now has CREATE and INSERT! It should not write anything to your db, but it could if you mistype something or mess up somehow! Make sure you back up your db files before you start the session!

ATTACH "client.mappings.db" as mapp;

CREATE TEMP TABLE t_count ( tag_id INTEGER PRIMARY KEY, current_count INTEGER );

INSERT INTO t_count SELECT tag_id, COUNT( * ) FROM current_mappings_table_name (magic selective join phrase) GROUP BY tag_id;

SELECT current_count, namespace, subtag FROM t_count NATURAL JOIN tags NATURAL JOIN namespaces NATURAL JOIN subtags ORDER BY current_count DESC LIMIT 25;

Your current_mappings_table_name should be current_mappings_2 or whatever, where 2 is your tag service service_id. These tables are stored in current_mappings.db, so make sure you do add the new attach (along with caches and master as before) to your session.

The (magic selective join phrase) is anything that intersects the large mappings pool with a smaller subset of files or tags. For you, it is probably going to be:

NATURAL JOIN file_inbox

The mappings table is pairs of (hash_id, tag_id), and files_inbox is a just a list of (hash_id), so the join there will only count up tags that apply to a file in the inbox. If you want to get more complicated, check out client.db's files_info and current_files tables in SQLiteStudio or some other ui.

The temp table will be deleted as soon as you .exit.

>>8880

This works so great, thank you. Fucking around with databases is oddly satisfying, can't wait to ruin something. I'm working from backups, though, so I'm not particularly worried.

Here's the complete code that I figured out, 99% of this is your work of course:

.open client.db
ATTACH "client.caches.db" as cach;
ATTACH "client.master.db" as mast;
ATTACH "client.mappings.db" as mapp;
SELECT service_id, name FROM services;
CREATE TEMP TABLE t_count ( tag_id INTEGER PRIMARY KEY, current_count INTEGER );

INSERT INTO t_count SELECT tag_id, COUNT( * ) FROM current_mappings_3

NATURAL JOIN file_inbox GROUP BY tag_id; SELECT current_count, namespace, subtag FROM t_count

NATURAL JOIN tags NATURAL JOIN namespaces NATURAL JOIN subtags ORDER BY current_count DESC LIMIT 25;

.open client.db
ATTACH "client.caches.db" as cach;
ATTACH "client.master.db" as mast;
ATTACH "client.mappings.db" as mapp;
SELECT service_id, name FROM services;
CREATE TEMP TABLE t_count ( tag_id INTEGER PRIMARY KEY, current_count INTEGER );

INSERT INTO t_count SELECT tag_id, COUNT( * ) FROM current_mappings_3

NATURAL JOIN file_inbox GROUP BY tag_id; SELECT current_count, namespace, subtag FROM t_count

NATURAL JOIN tags NATURAL JOIN namespaces NATURAL JOIN subtags WHERE namespace="SUBTAG" ORDER BY current_count DESC LIMIT 25;

This is what I am using to pull more specific data. I really appreciate you taking the time to show me this, I do think I'm going to go a bit ham with it.