Question about AFT and duplicate entries

[Ian Monroe]

On Sun, Feb 24, 2008 at 10:46 AM, Colin Guthrie <gmane at colin.guthr.ie> wrote:
> Hi,
>
>  I have a quick question about AFT.
>
>  I've got my laptop and my home server which I mount over NFS. This works
>  well and I can attach and detach the collection quite happily. However,
>  I do duplicate some of the music on NFS to my local machine for when I'm
>  not at home etc.
>
>  This setup results in having two entries in the tags table for each
>  duplicated file. The statistics table has a unique key on uniqueid field
>  so it will only ever get one entry per track, even if there are two used
>  (is there any reason to have deviceid and url in this table?? Surely the
>  uniqueid is the relevent joining field?) but the collection browser
>  shows both entries meaning that for some albums I have two of every
>  track which is not very user friendly.
>
>  Am I doing something wrong or is the collection browser not filtering
>  out the dupicates by using the uniqueid table?
>
>  That said, I wonder if my uniqueid table is being populated correctly.
>
>  e.g.
>  mysql> SELECT COUNT(uniqueid) FROM uniqueid;
>  +-----------------+
>  | COUNT(uniqueid) |
>  +-----------------+
>  |           14840 |
>  +-----------------+
>  1 row in set (0.01 sec)
>
>  mysql> SELECT COUNT(DISTINCT uniqueid) FROM uniqueid;
>  +--------------------------+
>  | COUNT(DISTINCT uniqueid) |
>  +--------------------------+
>  |                    14840 |
>  +--------------------------+
>  1 row in set (0.04 sec)
>
>
>  From some more indepth testing, it transpires I only have one entry in
>  uniqueid per unique track and some tracks are actually not listed:
>  e.g.
>  mysql> SELECT * FROM uniqueid WHERE url LIKE '%Cold Water%Intro%mp3' \G
>  *************************** 1. row ***************************
>      url: ./home/colin/Music/Aim/Cold Water Music/01 - Intro.mp3
>  deviceid: -1
>  uniqueid: f4575b93e55a0cb1095ff870ac76cd61
>      dir: ./home/colin/Music/Aim/Cold Water Music
>  1 row in set (0.02 sec)
>
>
>  But:
>  mysql> SELECT * FROM tags WHERE url LIKE '%Cold Water%Intro%mp3' \G
>  *************************** 1. row ***************************
>        url: ./audio/mp3/Aim/Cold Water Music/01 - Intro.mp3
>        dir: ./audio/mp3/Aim/Cold Water Music
>  createdate: 1164990147
>  modifydate: 1111277784
>      album: 203
>     artist: 184
>   composer: 1
>      genre: 49
>      title: Intro
>       year: 9
>    comment: Aim - Cold Water Music - 1/12 - Intro
>      track: 1
>  discnumber: 0
>    bitrate: 256
>     length: 55
>  samplerate: 44100
>   filesize: 1777802
>   filetype: 1
>    sampler: 0
>        bpm: 0
>   deviceid: 2
>  *************************** 2. row ***************************
>        url: ./home/colin/Music/Aim/Cold Water Music/01 - Intro.mp3
>        dir: ./home/colin/Music/Aim/Cold Water Music
>  createdate: 1203086177
>  modifydate: 1203086177
>      album: 203
>     artist: 184
>   composer: 1
>      genre: 49
>      title: Intro
>       year: 9
>    comment: Aim - Cold Water Music - 1/12 - Intro
>      track: 1
>  discnumber: 0
>    bitrate: 256
>     length: 55
>  samplerate: 44100
>   filesize: 1777802
>   filetype: 1
>    sampler: 0
>        bpm: 0
>   deviceid: -1
>
>
>  This shows that I have this same file stored twice on my system (once on
>  my local disk (deviceid -1) and once on my NFS mount (deviceid 2).
>
>  But only one entry in uniqueid.
>
>  Looking at the uniqueid table in more depth, I see there is a unique key
>  on the uniqueid field. This means that it is impossible to store the
>  uniqueid from both locations of the same file at once. This means that
>  if you were to try and hide the duplicates in the collection browser, it
>  would be impossible to do so via reduction based on uniqueid (favoyring
>  the lower deviceid if duplicates are found).
>
>  Is perhaps the unique key in the uniqueid table a fundamentally broken
>  concept? I think there *should* be a unique key on the url and deviceid
>  fields for the uniqueid table.
>
>  I'm possibly doing something wrong, so thanks for any insights :)
>
>  Col

The purpose of AFT is identifying moved files and keeping statistics.
It's not for removing duplicates as far as I know, at least this is a
feature that is not implemented.

Ian