Page 1 of 1

Database help

Unread postPosted: 21 Dec 2016, 19:53
by mwpmorris
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.

Re: Database help

Unread postPosted: 22 Dec 2016, 13:58
by mwpmorris
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).