Hash DB #67

New Issue

boiii · 2024-03-15T22:55:05Z

boiii commented

2024-03-15 22:55:05 +00:00

Submitting and storing hashes (per API, we need API tokens!) allows users to retrieve data with very high accuracy by finding the file fingerprint in traxxx.

Example table design:

id
unique ID of submission

value
the actual hash value (probably base64)

type
the hash type (oshash, ...)

release_id
release ID, index on this column

user_id
user ID

We can then show a table of submitted hashes, by doing:

SELECT "type", value, COUNT(*)
WHERE release_id = SceneID
GROUP BY value

Sample SQL schema

CREATE TABLE releases (
    id int PRIMARY KEY
);

CREATE TABLE users (
    id int PRIMARY KEY
);

-- ^ already exist in traxxx

CREATE TABLE hash_submissions (
    id text PRIMARY KEY,
    value text NOT NULL,
    "type" text NOT NULL,
    release_id int NOT NULL,
    user_id int NOT NULL,

    CONSTRAINT fk_release
      FOREIGN KEY(release_id) 
        REFERENCES releases(release_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_user
      FOREIGN KEY(user_id) 
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

CREATE INDEX hash_value ON hash_submissions (value);
CREATE INDEX release_hashes ON hash_submissions (release_id);

Submitting and storing hashes (per API, we need API tokens!) allows users to retrieve data with very high accuracy by finding the file fingerprint in traxxx. Example table design: **id** unique ID of submission **value** the actual hash value (probably base64) **type** the hash type (oshash, ...) **release_id** release ID, index on this column **user_id** user ID We can then show a table of submitted hashes, by doing: ```sql SELECT "type", value, COUNT(*) WHERE release_id = SceneID GROUP BY value ``` ### Sample SQL schema ```sql CREATE TABLE releases ( id int PRIMARY KEY ); CREATE TABLE users ( id int PRIMARY KEY ); -- ^ already exist in traxxx CREATE TABLE hash_submissions ( id text PRIMARY KEY, value text NOT NULL, "type" text NOT NULL, release_id int NOT NULL, user_id int NOT NULL, CONSTRAINT fk_release FOREIGN KEY(release_id) REFERENCES releases(release_id) ON DELETE CASCADE, CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE ); CREATE INDEX hash_value ON hash_submissions (value); CREATE INDEX release_hashes ON hash_submissions (release_id); ```

Sign in to join this conversation.