CODEWITHSUNDEEP
androiddatabasesqlite
bfresh
bfresh

Reputation: 113

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?

Upvotes: 1

Views: 2411

Answers (1)

chubbsondubs
chubbsondubs

Reputation: 38706

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.

Upvotes: 3

Related Questions