[Druse]

the MySQL setup assumes that all media in the DB is accessible by all devices. this requires all devices to share their media.
in the scenario where a device contains local media that is not shared, the media is scanned and added to the DB. this can cause an issue from other devices, for example: during randomized play or cleaning the library.

my suggestion is to add a column in the DB that would contain either public or device specific ID (name, serial, hash, etc). this would prevent another device from either attempting to access the media or remove the items from the library. in addition, duplicate situations could be handled.

example:
NAS contains: a,b,c,d
DEVA contains: a,b,e,f
DEVB contains: g,h

both DEVA & DEVB have access to shared media on the NAS.
media list in the DB would look like: a,b,c,d,e,f,g,h.

however:

• DEVB would fail to access e,f.
  
• DEVA would fail to access g,h.
  
• cleaning the library on DEVB would remove e,f.
  
• cleaning the library on DEVA would remove g,h.
  
• DEVA would see a,b twice.
  

i imagine a flag could be set as either public or local media - where the media in library is tagged with device specific ID.

what do you think?

[Druse]

i was wrong about the duplicate situation.
what seems to be happening is that the first media of a file-type is recorded into the DB. future duplicates (of the same file-type) are currently ignored / not added. same song of another file-type is added however.

example:
DEVA:/sdcard/Music/Artist/SongA1.mp3
smb://NAS/Shared/Music/Artist/SongA1.mp3
smb://NAS/Amazon/Artist/SongA1.mp3
DEVB:/storage/sdcard1/Music/Genre/Artist - SongA1.mp3
DEVA:/sdcard/Music/Artist/SongA1.wma

in this case, assuming top to bottom scan order, the DB will end up with:
/sdcard/Music/Artist/SongA1.mp3
/sdcard/Music/Artist/SongA1.wma
in the path table.

which means that DEVB cannot access* the NAS nor its local media.
hmm. i'll think this thru and do a follow-up post with more details.

EDIT> * using categories rather than Files.

i was wrong again.

correction:
the song is updated with the new path. so duplicates rewrite previous data.
what constitutes a duplicate is a song with the same baseFileName, song title (from ID3), track# (from ID3) and MusicBrainsTrackID from the same album. an album is defined by Artist, AlbumName and MusicBrainsAlbumID.

in the above example, the DB will end up with:
/storage/sdcard1/Music/Genre/Artist - SongA1.mp3
/sdcard/Music/Artist/SongA1.wma

details:

1. a hash is calculated for each directory, based on file(names,sizes,dates). then compared to the one stored in the DB:
   
   Code:

   select strHash from path where strPath='...'
   if the hash has not changed, it will not scan the files inside the directory.
   _
   
2. if hash is different then the given path is removed from the DB:
   
   Code:

   delete from path where strPath='...'
   _
   
3. album (idAlbum, idArtist) is found based on Artist, AlbumName and MusicBrainsAlbumID
   
   Code:

   SEL * FROM album WHERE strArtists LIKE '...' AND strAlbum LIKE '...' AND strMusicBrainzAlbumID IS NULL
   _
   
4. idAlbum is used to update album genre and year
   
   Code:

   UPD album SET strGenres = '...', iYear=####, bCompilation=#, strReleaseType = 'album', lastScraped = NULL WHERE idAlbum=#
DEL FROM album_artist WHERE idAlbum = #
DEL FROM album_genre WHERE idAlbum = #
replace into album_artist (idArtist, idAlbum, strArtist, strJoinPhrase, boolFeatured, iOrder) values(#,#,'...','',0,0)
   _
   
5. rewrite path into DB (new idPath is generated):
   
   Code:

   select * from path where strPath='...'
insert into path (idPath, strPath) values (NULL,'...')
   _
   
6. look for the song by AlbumID (from step 3), FileName, song title (from ID3), track# (from ID3) and MusicBrainsTrackID
   
   Code:

   SEL * FROM song WHERE idAlbum=# AND strFileName='...' AND strTitle='...' AND iTrack=# AND strMusicBrainzTrackID IS NULL
   _
   
7. if found then the song is updated with the new path (idPath from step 5)
   
   Code:

   UPD song SET idPath = #, strArtists = '...', strGenres = '...', strTitle = '...', iTrack = #, iDuration = #, iYear = ####, strFileName = '...', strMusicBrainzTrackID = NULL, iTimesPlayed = 0, iStartOffset = 0, iEndOffset = 0, lastplayed = NULL, rating = '0', comment = '...', mood = '' WHERE idSong = #
   _
   
8. if not found then write the song to the DB:
   
   Code:

   INS INTO song (idSong,idAlbum,idPath,strArtists,strGenres,strTitle,iTrack,iDuration,iYear,strFileName,strMusicBrainzTrackID,iTimesPlayed,iStartOffset,iEndOffset,lastplayed,rating,comment,mood) values (NULL, #, #, '...', '...', '...', #, #, ####, '...',NULL,0,0,0,NULL,'0','...', '')

[helta]

You should be sharing EVERYTHING via SMB, even if its local to that machine. That way, every client can play anything.

[Druse]

i think you misunderstood that i'm requesting a new feature; not reporting a bug.

[trogggy]

I don't understand the reasoning behind this. Why would you want stuff to show up in the library that can't be played? It's as easy to use a network address as a local one, so everything's playable everywhere.

[Druse]

that made me re-think why i proposed this in the first place. i should have mentioned the specific scenario i was trying to solve and not propose a solution. now that i think about it, the solution does not address the scenario, so it's pointless anyway. also, if i had mentioned the scenario then it would have allowed people to gauge its general likelihood.

for the record, i withdraw the above feature request and propose the following scenario for discussion:

1 NAS with all the media (a,b,c,d,e,f,g)
2 mobile devices with a subset of the media on an sdcard:
a. DEVA (a,b)
b. DEVB (a,b,c,d)
1 hard-wired android device, getting its media from the NAS

while at home, the mobile devices are on the network, thus able to access the NAS.
i want the ability for each mobile device to play its local media when offline (not on the network. example: on the road)
by play i mean access the media thru the library; party mode, etc.
--
it would not make sense to add the local media to MySQL because when the device is offline it won't be able to access the MySQL DB anyway.
maybe have a local DB for the local media and fall-back to it if connecting to MySQL DB fails?

[helta]

Use two different profiles, one at "home" profile (mysql), one "away" profile (local).

[Druse]

done

let's close this thread.