DatabaseHandler.java

private static final package com.fennyfatal.agroovedownloader;

import java.util.ArrayList;
import java.util.List;

import com.fennyfatal.GrooveApi.Playlist;
import com.fennyfatal.GrooveApi.Song;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {
	 
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "grooveManager";
 
    // Table names
    private static final String TABLE_SONGS = "songs";
    private static final String TABLE_PLAYLIST = "playlist";
    private static final String TABLE_PLAYLIST_SONGS = "playlistSongs";
 
    // Table Columns names
    private static final String KEY_NAME = "Name";
    private static final String KEY_ID = "SongID"; 
    private static final String KEY_DURATION = "EstimateDuration"; 
    private static final String KEY_FLAGS = "Flags"; 
    private static final String KEY_HASLOWBITRATE = "IsLowBitrateAvailable"; 
    private static final String KEY_ISVERIFIED = "IsVerified"; 
    private static final String KEY_POPULARITY = "Popularity"; 
    private static final String KEY_ARTISTNAME = "ArtistName"; 
    private static final String KEY_ARTISTID = "ArtistID"; 
    private static final String KEY_ALBUMNAME = "AlbumName"; 
    private static final String KEY_ALBUMID = "AlbumID"; 
    private static final String KEY_YEAR = "Year"; 
    private static final String KEY_COVERARTFILENAME = "CoverArtFilename"; 
    private static final String KEY_TRACKNUM = "TrackNum"; 
    private static final String KEY_AVGDAILYWEIGHT = "AvgDailyWeight"; 
    private static final String KEY_NUMPLAYSMONTH = "NumPlaysMonth";
    
    private static final String KEY_PLAYLISTID = "PlaylistID";
    private static final String KEY_PLAYLISTNAME = "PlaylistName";
    private static final String KEY_PLAYLISTPOS = "PlaylistPOS";
    
    
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_SONGS_TABLE = "CREATE TABLE " + TABLE_SONGS + "("
                + KEY_NAME + " TEXT," + KEY_ID + " TEXT PRIMARY KEY,"
                + KEY_DURATION + " TEXT, " + KEY_FLAGS + " TEXT, " + KEY_HASLOWBITRATE + " TEXT, "
                + KEY_ISVERIFIED + " TEXT, " + KEY_POPULARITY + " TEXT, " 
                + KEY_ARTISTNAME + " TEXT, " + KEY_ARTISTID + " TEXT, " 
                + KEY_ALBUMNAME + " TEXT, " + KEY_ALBUMID + " TEXT, " 
                + KEY_YEAR + " TEXT, " + KEY_COVERARTFILENAME + " TEXT, " 
                + KEY_TRACKNUM + " TEXT, " + KEY_AVGDAILYWEIGHT + " TEXT, " 
                + KEY_NUMPLAYSMONTH + " TEXT" + ")";
        db.execSQL(CREATE_SONGS_TABLE);
        
        String CREATE_PLAYLISTS_TABLE = "CREATE TABLE " + TABLE_PLAYLIST + "("
                + KEY_PLAYLISTNAME + " TEXT,"
                + KEY_PLAYLISTID + " INTEGER PRIMARY KEY AUTOINCREMENT )";
        db.execSQL(CREATE_PLAYLISTS_TABLE);
        String CREATE_PLAYLIST_SONGS_TABLE = "CREATE TABLE " + TABLE_PLAYLIST_SONGS + "("
                + KEY_PLAYLISTID + " INTEGER,  " + KEY_PLAYLISTPOS + " TEXT, " 
                + KEY_ID + " TEXT," + "PRIMARY KEY (" + KEY_PLAYLISTID + ", " + KEY_PLAYLISTPOS + ")" + ")";
        db.execSQL(CREATE_PLAYLIST_SONGS_TABLE);
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SONGS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLIST);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLIST_SONGS);
        
        // Create tables again
        onCreate(db);
    }
    
 // Adding new song
    public void addSong(Song song) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	ContentValues values = new ContentValues();
    	
    	values.put(KEY_NAME, song.getName());
    	values.put(KEY_ID, song.getSongID());
    	values.put(KEY_DURATION, song.getEstimateDuration());
    	values.put(KEY_FLAGS, song.getFlags());
    	values.put(KEY_HASLOWBITRATE, song.getIsLowBitrateAvailable());
    	values.put(KEY_ISVERIFIED, song.getIsVerified());
    	values.put(KEY_POPULARITY, song.getPopularity());
    	values.put(KEY_ARTISTNAME, song.getArtistName());
    	values.put(KEY_ARTISTID, song.getArtistID());
    	values.put(KEY_ALBUMNAME, song.getAlbumName());
    	values.put(KEY_ALBUMID, song.getAlbumID());
    	values.put(KEY_YEAR, song.getYear());
    	values.put(KEY_COVERARTFILENAME, song.getCoverArtFilename());
    	values.put(KEY_TRACKNUM, song.getTrackNum());
    	values.put(KEY_AVGDAILYWEIGHT, song.getAvgDailyWeight());
    	values.put(KEY_NUMPLAYSMONTH, song.getNumPlaysMonth());
    	db.insert(TABLE_SONGS, null, values);
    	db.close();
    }
     
    // Getting single song
    public Song getSong(String id) {
    	SQLiteDatabase db = this.getReadableDatabase();
    	 
        Cursor cursor = db.query(TABLE_SONGS, new String[] { KEY_NAME,
                KEY_ID, KEY_DURATION, KEY_FLAGS, KEY_HASLOWBITRATE, KEY_ISVERIFIED,
                KEY_POPULARITY,KEY_ARTISTNAME,KEY_ARTISTID, KEY_ALBUMNAME, KEY_ALBUMID,
                KEY_YEAR, KEY_COVERARTFILENAME, KEY_TRACKNUM, KEY_AVGDAILYWEIGHT, KEY_NUMPLAYSMONTH}, KEY_ID + "=?",
                new String[] { id }, null, null, null, null);
        if (cursor != null && cursor.moveToFirst())
        {
        Song song = new Song(cursor.getString(0),
                cursor.getString(1), cursor.getString(2),cursor.getString(3),cursor.getString(4),cursor.getString(5),cursor.getString(6),cursor.getString(7),
                cursor.getString(8),cursor.getString(9),cursor.getString(10),cursor.getString(11),cursor.getString(12),cursor.getString(13),cursor.getString(14),
                cursor.getString(15));
        cursor.close();
        // return contact
        return song;
        }
        return null;
    }
     
    // Getting All Songs
    public List<Song> getAllSongs() {
    	SQLiteDatabase db = this.getReadableDatabase();
    	List<Song> songList = new ArrayList<Song>();
   	 
        Cursor cursor = db.query(TABLE_SONGS, new String[] { KEY_NAME,
                KEY_ID, KEY_DURATION, KEY_FLAGS, KEY_HASLOWBITRATE, KEY_ISVERIFIED,
                KEY_POPULARITY,KEY_ARTISTNAME,KEY_ARTISTID, KEY_ALBUMNAME, KEY_ALBUMID,
                KEY_YEAR, KEY_COVERARTFILENAME, KEY_TRACKNUM, KEY_AVGDAILYWEIGHT, KEY_NUMPLAYSMONTH}, null,
                null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                Song song = new Song(cursor.getString(0),
                        cursor.getString(1), cursor.getString(2),cursor.getString(3),cursor.getString(4),cursor.getString(5),cursor.getString(6),cursor.getString(7),
                        cursor.getString(8),cursor.getString(9),cursor.getString(10),cursor.getString(11),cursor.getString(12),cursor.getString(13),cursor.getString(14),
                        cursor.getString(15));
                songList.add(song);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return songList;
    }
     
    // Getting songs Count
    public int getSongsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_SONGS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int retval = cursor.getCount();
        cursor.close();
        return retval;
    }
    
    public void updatePlaylist (long playlistID, Playlist pl) {
    	SQLiteDatabase db = this.getReadableDatabase();
    	db.delete(TABLE_PLAYLIST_SONGS, KEY_PLAYLISTID + " =?", new String[]{String.valueOf(playlistID)});
    	int pos = 0;
    	for (Song s : pl)
    	{
    		ContentValues values = new ContentValues();
    		values.put(KEY_ID, s.getSongID());
    		values.put(KEY_PLAYLISTPOS, ++pos);
    		values.put(KEY_PLAYLISTID, playlistID);
    		db.insert(TABLE_PLAYLIST_SONGS, null, values);
    	}
    	db.close();
    }
    public void updatePlaylist (long playlistID, String playlistName, Playlist pl) {
    	SQLiteDatabase db = this.getReadableDatabase();
    	ContentValues values = new ContentValues();
    	values.put( KEY_PLAYLISTNAME , playlistName);
    	db.update(TABLE_PLAYLIST, values, KEY_PLAYLISTID + " = ?",
                new String[] { String.valueOf(playlistID) });
    	updatePlaylist(playlistID, pl);
    	db.close();
    }
    public void updatePlaylistByName (String playlistName, Playlist pl) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PLAYLIST, new String[] { KEY_PLAYLISTID },
        		KEY_PLAYLISTNAME + "=?", new String[] { playlistName },null,null,null);
        if (cursor.moveToFirst()) {
        	updatePlaylist(Long.parseLong(cursor.getString(0)),pl);
        }
    }
    public void removePlaylistByName (String playlistName) {
    	Cursor cursor = null;
        try
        {
    	SQLiteDatabase db = this.getReadableDatabase();
        cursor = db.query(TABLE_PLAYLIST, new String[] { KEY_PLAYLISTID },
        		KEY_PLAYLISTNAME + "=?", new String[] { playlistName },null,null,null);
        if (cursor.moveToFirst()) {
        	db.delete(TABLE_PLAYLIST,KEY_PLAYLISTID + "=?", new String[] { cursor.getString(0) });
            //db.delete(TABLE_PLAYLIST_SONGS,KEY_PLAYLISTID + "=?", new String[] { cursor.getString(0) });
        	/* We'll keep these for now for possible undelete. */
        }
        }
        finally
        {if (cursor != null) try {cursor.close();}catch (Exception ex){}}
        
    }
    public List<String> getPlaylists()
    {
    	String countQuery = "SELECT  * FROM " + TABLE_PLAYLIST;
    	List<String> playLists = new ArrayList<String>();
    	Cursor cursor = null;
    	try
    	{
    	SQLiteDatabase db = this.getReadableDatabase();
    	cursor = db.rawQuery(countQuery, null);
    	 if (cursor.moveToFirst()) {
             do {
                 playLists.add(cursor.getString(0));
             } while (cursor.moveToNext());
         }
        }
        finally
        {if (cursor != null) try {cursor.close();}catch (Exception ex){}}
    	return playLists;
    	 
    }
    
    public void createPlaylist (String playlistName, Playlist pl) {
    	SQLiteDatabase db = this.getReadableDatabase();
    	ContentValues values = new ContentValues();
    	values.put( KEY_PLAYLISTNAME , playlistName);
    	long playlistID = db.insert(TABLE_PLAYLIST, null, values);
    	updatePlaylist(playlistID, pl);
    	db.close();
    }
    public Playlist getPlaylistByName(String Name) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PLAYLIST, new String[] { KEY_PLAYLISTID },
        		KEY_PLAYLISTNAME + "=?", new String[] { Name },null,null,null);
        if (cursor.moveToFirst()) {
        	Playlist pls = getPlaylistById(cursor.getString(0));
        	cursor.close();
        	return pls;
        }
        cursor.close();
        // return count
        return null;
    }
    public Playlist getPlaylistById(String Id)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        Playlist playlist = new Playlist();
        Cursor cursor = db.query(TABLE_PLAYLIST_SONGS, new String[] { KEY_ID, KEY_PLAYLISTPOS },
        		KEY_PLAYLISTID + "=?", new String[] { Id },null,null, KEY_PLAYLISTPOS );
        if (cursor.moveToFirst()) {
        	do
        	{
        		playlist.add(getSong(cursor.getString(0)));
        	} while (cursor.moveToNext());
        }
        cursor.close();
 
        // return count
        return playlist;
    }
    
    // Do we have this song?
    public boolean ContainsSong(String id) {
    	SQLiteDatabase db = this.getReadableDatabase();
   	 
        Cursor cursor = db.query(TABLE_SONGS, null, KEY_ID + "=?",
                new String[] { id }, null, null, null, null);
        boolean retval = (cursor.getCount() == 1);
        cursor.close();
        return retval;
    }
    public boolean ContainsSong(Song s){
    	return ContainsSong(s.getSongID());
    }
    
    // Updating single song
    public int updateSong(Song song) {
    	SQLiteDatabase db = this.getWritableDatabase();
    	ContentValues values = new ContentValues();
    	
    	values.put(KEY_NAME, song.getName());
    	values.put(KEY_DURATION, song.getEstimateDuration());
    	values.put(KEY_FLAGS, song.getFlags());
    	values.put(KEY_HASLOWBITRATE, song.getIsLowBitrateAvailable());
    	values.put(KEY_ISVERIFIED, song.getIsVerified());
    	values.put(KEY_POPULARITY, song.getPopularity());
    	values.put(KEY_ARTISTNAME, song.getArtistName());
    	values.put(KEY_ARTISTID, song.getArtistID());
    	values.put(KEY_ALBUMNAME, song.getAlbumName());
    	values.put(KEY_ALBUMID, song.getAlbumID());
    	values.put(KEY_YEAR, song.getYear());
    	values.put(KEY_COVERARTFILENAME, song.getCoverArtFilename());
    	values.put(KEY_TRACKNUM, song.getTrackNum());
    	values.put(KEY_AVGDAILYWEIGHT, song.getAvgDailyWeight());
    	values.put(KEY_NUMPLAYSMONTH, song.getNumPlaysMonth());
    	return db.update(TABLE_SONGS, values, KEY_ID + " = ?",
                new String[] { song.getSongID() });
    	
    }
     
    // Deleting single song
    public void deleteSong(Song song) {
    	SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_SONGS, KEY_ID + " = ?",
                new String[] { song.getSongID() });
        db.close();
    }
}