I have created a database
with SQLite
in Android
and I have a table
Playlist_Songs
which use foreign keys
on two tables: Songs
and Playlists
How can I add a value with foreign key from another table because values.put(playlists_songs_playlistid, "(SELECT "+playlists_id+" FROM "+TABLE_PLAYLISTS+" WHERE "+playlists_name+"="+playlist+")");
doesn't work in my case.
public class MusicDB extends SQLiteOpenHelper{
public static String DATABASE_NAME = "MusicDB";
public static final int DATABASE_VERSION = 4;
public static final String TABLE_CONFIG = "config";
public static final String TABLE_SONGS = "songs";
public static final String TABLE_PLAYLISTS = "playlists";
public static final String TABLE_PLAYLISTS_SONGS = "playlists_songs";
// CONFIG TABLE //
public static final String config_id = "id";
public static final String config_Directory = "Directory";
/////////////////
// SONGS TABLE //
public static final String songs_id = "id";
public static final String songs_name = "Name";
public static final String songs_path = "Path";
public static final String songs_downloadLing = "downloadLing";
////////////////
// PLAYLISTS TABLE //
public static final String playlists_id = "id";
public static final String playlists_name = "Name";
////////////////////
// PLAYLISTS SONGS TABLE //
public static final String playlists_songs_id = "id";
public static final String playlists_songs_playlistid = "playlist_id";
public static final String playlists_songs_songid = "song_id";
//////////////////////////
public MusicDB(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
final String CREATE_CONFIG_TABLE = "CREATE TABLE "+TABLE_CONFIG+" ("+config_id+" INTEGER PRIMARY KEY,"+config_Directory+" TEXT NOT NULL UNIQUE)";
db.execSQL(CREATE_CONFIG_TABLE);
final String CREATE_SONGS_TABLE = "CREATE TABLE "+TABLE_SONGS+" ("+songs_id+" INTEGER PRIMARY KEY,"+songs_name+" TEXT NOT NULL UNIQUE,"+songs_path+" TEXT NOT NULL,"+songs_downloadLing+" TEXT UNIQUE)";
db.execSQL(CREATE_SONGS_TABLE);
final String CREATE_PLAYLISTS_TABLE = "CREATE TABLE "+TABLE_PLAYLISTS+" ("+playlists_id+" INTEGER PRIMARY KEY,"+playlists_name+" TEXT NOT NULL UNIQUE)";
db.execSQL(CREATE_PLAYLISTS_TABLE);
final String CREATE_PLAYLISTS_SONGS = "CREATE TABLE "+TABLE_PLAYLISTS_SONGS+" ("+playlists_songs_id+" INTEGER PRIMARY KEY,"+playlists_songs_playlistid+" INTEGER NOT NULL,"+
playlists_songs_songid+" INTEGER NOT NULL, FOREIGN KEY ("+playlists_songs_playlistid+") REFERENCES "+TABLE_PLAYLISTS+"("+playlists_id+") ON DELETE CASCADE," +
"FOREIGN KEY ("+playlists_songs_songid+") REFERENCES "+TABLE_SONGS+"("+songs_id+") ON DELETE CASCADE);";
db.execSQL(CREATE_PLAYLISTS_SONGS);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("TEST", "Se apeleaza onUPDATE");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONFIG);
db.execSQL("DROP TABLE IF EXISTS "+TABLE_SONGS);
onCreate(db);
}
public void addDirectory(String name) {
Log.i("TEST", "Adaug in Config " + name);
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(config_Directory, name);
db.insert(TABLE_CONFIG, null, values);
db.close();
}
public ArrayList<String> getDirectories() {
ArrayList<String> directories = new ArrayList<>();
String selectQuery = "SELECT * FROM "+TABLE_CONFIG;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
directories.add(cursor.getString(1));
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return directories;
}
public void addSong(String name, String path) {
Log.i("TEST", "Adaug in Songs " + name);
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(songs_name, name);
values.put(songs_path, path);
db.insert(TABLE_SONGS, null, values);
db.close();
}
public ArrayList<String> getSongsByName() {
ArrayList<String> songs = new ArrayList<>();
String selectQuery = "SELECT * FROM "+TABLE_SONGS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
songs.add(cursor.getString(1));
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return songs;
}
public void addPlaylist(String name) {
Log.i("TEST", "Adaug in Playlists " + name);
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(playlists_name, name);
db.insert(TABLE_PLAYLISTS, null, values);
db.close();
}
public ArrayList<String> getPlaylists() {
ArrayList<String> playlists = new ArrayList<>();
String selectQuery = "SELECT * FROM "+TABLE_PLAYLISTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
playlists.add(cursor.getString(1));
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return playlists;
}
public void addSongToPlaylist(String song, String playlist) {
Log.i("TEST", "Adaug in Playlistul " + playlist +" melodia "+song);
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(playlists_songs_playlistid, "(SELECT "+playlists_id+" FROM "+TABLE_PLAYLISTS+" WHERE "+playlists_name+"="+playlist+")");
values.put(playlists_songs_songid, "(SELECT "+songs_id+" FROM "+TABLE_SONGS+" WHERE "+songs_name+"="+song+")");
db.insert(TABLE_PLAYLISTS_SONGS, null, values);
db.close();
}
}
Aucun commentaire:
Enregistrer un commentaire