Technical Documentation

Protocol

On this page...

SQL

The table definitions for the database in imms2.db:

Information about the file on disk.

CREATE TABLE Identify (
        'path' VARCHAR(4096) UNIQUE NOT NULL,
        'uid' INTEGER NOT NULL,
        'modtime' TIMESTAMP NOT NULL,
        'checksum' TEXT NOT NULL
);

Maintains the uid <-> sid mapping.
Every file has a unique id - uid.
Every song has an id - sid - but it is shared between different versions of the same song (eg. remixes)

CREATE TABLE Library (
        'uid' INTEGER UNIQUE NOT NULL,
        'sid' INTEGER DEFAULT -1,
        'playcounter' INTEGER DEFAULT 0,
        'lastseen' TIMESTAMP DEFAULT 0,
        'firstseen' TIMESTAMP DEFAULT 0
);

CREATE TABLE Last (
        'sid' INTEGER UNIQUE NOT NULL,
        'last' TIMESTAMP
);

CREATE TABLE Ratings (
        'uid' INTEGER UNIQUE NOT NULL,
        'rating' INTEGER NOT NULL,
        'dev' INTEGER DEFAULT 0
);

Journal maintains a record of all play events.
The 'played' field can be used to determine if the song was skipped or not, and 'flags' encodes the rest of the context, such as whether the song was manually selected by the user, etc.
Good for, for example, generating reports about songs popular last week, etc.

CREATE TABLE Journal (
        'uid' INTEGER NOT NULL,
        'played' TIME NOT NULL,
        'flags' INTEGER NOT NULL,
        'time' TIMESTAMP NOT NULL
);
CREATE INDEX Jouranl_uid_i ON Journal (uid);

This table is used to store old style (pre IMMS 3.0) ratings. Believe me, you don't care about it.

CREATE TABLE Bias (
        'uid' INTEGER NOT NULL,
        'mean' INTEGER NOT NULL,
        'trials' INTEGER NOT NULL
);
CREATE INDEX Bias_uid_i ON Bias (uid);

CREATE TABLE A.Acoustic (
        'uid' INTEGER UNIQUE NOT NULL,
        'mfcc' BLOB DEFAULT NULL,
        'bpm' BLOB DEFAULT NULL
);

CREATE TABLE Info (
        'sid' INTEGER UNIQUE NOT NULL,
        'aid' INTEGER NOT NULL,
        'title' TEXT NOT NULL
);

CREATE TABLE Tags (
        'uid' INTEGER UNIQUE NOT NULL,
        'title' TEXT NOT NULL,
        'album' TEXT NOT NULL,
        'artist' TEXT NOT NULL
);

Identifies an artist entity.
Of particular interest if the 'readable' field which holds IMMS' best guess at the human-readable artist name.
Good for generating reports and such.

CREATE TABLE Artists (
        'aid' INTEGER PRIMARY KEY,
        'artist' TEXT UNIQUE NOT NULL,
        'readable' TEXT UNIQUE,
        'trust' INTEGER DEFAULT 0
);

Old/Unused tables:

CREATE TABLE Rating (
        'uid' INTEGER UNIQUE NOT NULL,
        'rating' INTEGER NOT NULL,
        'trend' INTEGER DEFAULT 0
);

CREATE TABLE Acoustic (
        'uid' INTEGER UNIQUE NOT NULL,
        'spectrum' TEXT,
        'bpm' TEXT
);


CREATE TABLE Journal (
        'uid' INTEGER NOT NULL,
        'delta' INTEGER NOT NULL,
        'time' TIMESTAMP NOT NULL
);

CREATE TABLE Info (
        'sid' INTEGER UNIQUE NOT NULL,
        'artist' TEXT NOT NULL,
        'title' TEXT NOT NULL
);

CREATE TABLE A.Distances (
        'x' INTEGER NOT NULL,
        'y' INTEGER NOT NULL,
        'dist' INTEGER NOT NULL
);
CREATE UNIQUE INDEX A.Distances_x_y_i ON Distances (x, y);