SQLiteDatabase: Create multiple tables dynamically at runtime?

So I am creating an application that requires me to store multiple playlists in memory. Each playlists consists of a group of songs in specified order. Obviously it is unreasonable to store the entire mp3 in the database itself. The way I am storing it is, storing its Name, Location, Artist, Album and a unique ID number.

My question is this:

How to make it so each playlist can be its own table in the database?

So each playlist table looks like this:

Playlist {
    _id    PRIMARY KEY AUTOINCREMENT INTEGER,
    title  TEXT,
    uri    TEXT, // Location of file in the phone
    album  TEXT,
    artist TEXT
}

Every tutorial I have searched for has tables predefined, not dynamically created at runtime.

Also if you guys think that creating a new table for each playlist is not the best way to go about this, what do you suggest?

Answers


Do not create tables dynamically at runtime that's crazy. You can do this with a very simple table structure, and it's easy for anyone to understand should your program ever be given to someone else.

All you need to do is create a Playlist table, and PlaylistItems. Playlist would contain at a bare minimum (id, name). PlayListItems would be (id, playlist_id, artist, album, location, ordering). PlayListItems.playlist_id would be a foreign key back to the PlayList.id field. Normally that's easy because you'll display a list of playlists by name to the user. Then when the click on it you'll make a separate call to fetch the contents of the playlist (select * from PlayListItems where playlist_id=?).

An alternative would be to simply have PlayListItems contain (id, playlist_id, media_id, ordering) where media_id would be a foreign key into your Media table which would hold all MP3s you've found on the device. In that case you wouldn't need to duplicate the artist name, album, and file location into that table which can help save space as well as keep any duplication of work to update those tables should media change. The downside is you'll need to join PlayListItems and Media together to get the information, but an index on the table should keep it performing quickly.


Need Your Help

QFileDialog does not give selected file

c++ qt

I have a little problem using the QFileDialog in Qt 5.2. When I open the dialog the dialog will show up but does not send me any selected files back. In the code example below the 'selectedFiles.at...

Use the $_POST method with PHP

php mysql

So, I have a user driven website. Users can post whatever they want, even a picture. Now, I use PHP to store the posts in my database table and PHP and MySQL to retrieve display the posts on each u...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.