You are here: Home » Topic » ‘idx’ and ‘disable’ fields in database

‘idx’ and ‘disable’ fields in database

This topic contains 8 replies, has 3 voices, and was last updated by  mpoly 10 years, 4 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #1276

    mpoly
    Participant

    For various reasons, I decided to disable firefly’s file scanner, and wrote a simple perl script to update the database directly from my own database of songs. Field names are obvious enough, and it is working fine. I only have two questions:

    a) There is a ‘disabled’ field in the database. Does it have a function? I was hoping that it would allow me to ‘hide’ songs from the Roku, but nothing happened when I tried it.

    b) What is the purpose of the idx field?

    Many thanks,

    Michael

    #10053

    fizze
    Participant

    ad b.)

    Each (proper) database needs an primary key, for firefly’s song-db this is the field called “idx”.
    One index uniquely identifies an mp3.

    Normally, this is linked to a sequence, so that any inserts should youse NEXTVAL (or similiar) from that sequence to populate the songs.idx field.

    Also, and index greatly increases performance on said fields.
    A

    select * from songs where idx=1232;

    doesnt do a full table scan, for instance šŸ˜‰

    #10054

    mpoly
    Participant

    Each (proper) database needs an primary key, for firefly’s song-db this is the field called “idx”.

    You are absolutely correct, except that the PK is the ‘id’ column:

    CREATE TABLE songs (
    id INTEGER PRIMARY KEY NOT NULL,
    path VARCHAR(4096) NOT NULL,
    fname VARCHAR(255) NOT NULL,
    ...
    codectype VARCHAR(5) DEFAULT NULL,
    idx INTEGER NOT NULL,
    has_video INTEGER DEFAULT 0,
    ...

    CREATE INDEX idx_path on songs(path,idx)

    In practice, firefly’s file scanner always (?) populates idx with 0.

    It appears that idx is there to enable a file to be inserted into the database more than once – but why?

    Regards,

    Michael

    #10055

    fizze
    Participant

    ok, my bad then šŸ˜‰
    ‘should have checked the create statement before blurting stuff out…. šŸ˜³

    Anywho, maybe this is meant to identify links or iTunes dot files that just link to another idx…. *shrug*

    I’ve never actually seen use for that.
    Peculiar though, as to why it is ‘NOT NULL’ and set to zero everytime….
    šŸ™„

    #10056

    rpedde
    Participant

    @fizze wrote:

    ok, my bad then šŸ˜‰
    ‘should have checked the create statement before blurting stuff out…. šŸ˜³

    Anywho, maybe this is meant to identify links or iTunes dot files that just link to another idx…. *shrug*

    I’ve never actually seen use for that.
    Peculiar though, as to why it is ‘NOT NULL’ and set to zero everytime….
    šŸ™„

    path + idx are a composite index.

    For example, if there was an audio iso and I wanted to index the tracks in the iso, it would be /path/to/iso,0 and /path/to/iso,1, etc.

    I haven’t used it yet, but it’s there to support single files with multiple audio tracks, as well as scripts that generate db items.

    As far as disabled, that’s the iTunes checkbox. The SB isn’t supposed to play “unchecked” items. Or has a prefs option for it, anyway.

    #10057

    mpoly
    Participant

    @rpedde wrote:

    path + idx are a composite index.

    I haven’t used it yet, but it’s there to support single files with multiple audio tracks, as well as scripts that generate db items.

    That will be cool when it happens.

    @rpedde wrote:

    As far as disabled, that’s the iTunes checkbox. The SB isn’t supposed to play “unchecked” items. Or has a prefs option for it, anyway.

    I tried some reasonable values for disabled (-1,0,1,255), with the pref in SB both checked and unchecked. No joy. No big deal, now that my script handles additions/deletions based on genres/ratings automatically (for christmas songs, poorly rated tracks, intro tracks, etc.)

    Cheers,

    Michael

    #10058

    rpedde
    Participant

    @mpoly wrote:

    I tried some reasonable values for disabled (-1,0,1,255), with the pref in SB both checked and unchecked. No joy. No big deal, now that my script handles additions/deletions based on genres/ratings automatically (for christmas songs, poorly rated tracks, intro tracks, etc.)

    Should be 1 and 0, but it may be that they don’t handle it on the SB side when using RSP instead of DAAP.

    — Ron

    #10059

    fizze
    Participant

    You wouldn’t mind sharing that script, mpoly? šŸ˜‰
    I hate having to tap into the webinterface to have firefly force a scan for some 10 new tracks …..

    thx in advance.

    #10060

    mpoly
    Participant

    @fizze wrote:

    You wouldn’t mind sharing that script, mpoly? šŸ˜‰
    I hate having to tap into the webinterface to have firefly force a scan for some 10 new tracks …..

    thx in advance.

    It is a quickly-put-together perl script right now, that will need to be touched up in order to work for you. Two things might interest you:

    I use “Album Artist” instead of “Artist”, and some other substitutions that are specific to the way I organize things.

    I dynamically populate the ‘grouping’ field with single-character codes that I then use in smart playlists, using rules based on genres and other flags.

    It does not yet handle static playlists. Also all my tags are UTF-8, so I don’t bother with code to parse other codepages.

    I don’t want to publically post code that’s really not ready (I just hacked it together to meet my needs without any thought of releasing it, you know how that works). If you want it, just send me a message at @panix.com. I will add some comments before I send it out.

    I suspect that you might be able to use it as a starting point for something useful to you.

    Cheers,

    Michael

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.