‘idx’ and ‘disable’ fields in database

[mpoly]

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

[fizze]

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 😉

[mpoly]

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

[fizze]

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….
🙄

[rpedde]

@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.

[mpoly]

@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

[rpedde]

@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

[fizze]

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.

[mpoly]

@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

[Author]

Posts