Database help

Need help? Post your questions here.

Database help

Unread postby mwpmorris » 21 Dec 2016, 19:53

Hi,

I'm pretty new to Madsonic and am hoping to get a little help with a couple of database commands.

I'm emulating some iTunes "Smart Playlist" features by populating playlists with a sequence of commands based on below:

DELETE FROM playlist_file WHERE PLAYLIST_ID = 0;
INSERT INTO playlist_file (MEDIA_FILE_ID, PLAYLIST_ID)
SELECT ID, '0'
FROM media_file WHERE (CHANGED > (NOW() - 1 MONTH)) AND TYPE='MUSIC' OR (CHANGED > (NOW() - 1 MONTH)) AND TYPE='VIDEO'
SELECT COUNT(*) FROM playlist_file WHERE PLAYLIST_ID=0;

My problem is that when I do a full rescan / cleanup, the playlists get deleted. When I recreate them using the regular web GUI they show in the DB as having a new index number, which means that to subsequently run my playlist commands I have to manually cross-reference and change the ID numbers.

1. Is there a way of creating playlists with a specific index number in the database? Alternatively, what about permanently deleting a playlist so that its index number can be reused?

2. I'd also like to manipulate the "FIRST_SCANNED" date in the media_file table so that it matches the dates in the "CHANGED" fields. Is there a way to do this? I ran a script on my music files so that the created/modified dates are the dates that they were originally added to my iTunes library. If I can crack this then the "recently added" functionality in Madsonic will be accurate.

Thanks in advance, Matt.
mwpmorris
 
Posts: 6
Joined: 18 Nov 2016, 17:33
Has thanked: 0 time
Been thanked: 6 times

Re: Database help

Unread postby mwpmorris » 22 Dec 2016, 13:58

If anyone is interested, I found a way of achieving #2 and have managed to copy the date info between columns by issuing variations on the following command:

Code: Select all
Update media_file SET FIRST_SCANNED = CHANGED


No solution to #1 as yet (how to restore playlists into specific index number).
mwpmorris
 
Posts: 6
Joined: 18 Nov 2016, 17:33
Has thanked: 0 time
Been thanked: 6 times


Return to Support 6.x

Who is online

Users browsing this forum: Google [Bot] and 2 guests