mysql: how to write a table of views

Question

I think my question is best explained via an example: Suppose I want to write a webserver for streaming music. I have the following columns in the songs table: |song_id|song_name|song_genre|song_length|artist

I would like to enable my users to save playlists, but I don't want my playlists to be defined by explicitly specifying the songs that are in the playlist, rather by something like "all songs by ringo starr", which would mean that when new songs by ringo starr are added to the table, the playlist will automatically have them. Actually what I want is a table called playlists that contains a list of mysql views.

The most naive approach would be to simply have a table called playlists, and one of it's columns would be called playlist_query which would store for the above example something like the string "select song_id from songs where artist='ringo starr'.

This is of course incredibly insecure, but it would suffice in case there is no better solution, since this application is used only internaly inside our company by just a few employees who are all good programmers that know their way around mysql.

So what do you suggest? go with this ugly solution, or is there some easier way to do this?

Answer 1

You could store the name of the view in the playlists table instead of the query itself.

You'd still have to create the required views though and I don't know how that helps your "security problem".

Could you elaborate on what kind of security is required?

Answer 2

I'd define a Playlist as a list of Filters where a filter might be a song ID, an artist ID, a song name, a genre ... I'd then fetch all songs for each filter, either in a single query combining filers with simple OR or using UNION if you want the playlist to be in the same order as the filters. Note though that it needs some additional effort to get results from UNION in the order of the queries, see this answer.

The advantages of this approach:

• no millions of views to manage
• no SQL queries stored in DB
• pretty flexible filtering

Admittedly, I can't say much about the performance of this solution. Shouldn't be too bad though.