Database help

Need help? Post your questions here.
mwpmorris
Posts: 6
Joined: 18 Nov 2016, 17:33
Has thanked: 0
Been thanked: 6 times

Database help

Unread post 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.
These users thanked the author mwpmorris for the post (total 2):
MadsonicMatt Zornig
Rating: 15.38%
mwpmorris
Posts: 6
Joined: 18 Nov 2016, 17:33
Has thanked: 0
Been thanked: 6 times

Re: Database help

Unread post 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).
These users thanked the author mwpmorris for the post:
Matt Zornig
Rating: 7.69%
Post Reply