Sreedhar.a wrote:
Thanks a lot for ur help.
You are welcome.
Right now I am trying to INSERT and UPDATE by means of triggers.
My doubt is:
"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album
Text,unique(Album));"
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,Artist
Text,unique(Artist));"
"CREATE TABLE BGM (BgmId INTEGER PRIMARY KEY NOT NULL,Bgm
Text,unique(Bgm));"
"CREATE TABLE PLAYLIST(PlayListName Text,TrackId INTEGER);"
"CREATE TABLE MUSIC (Id INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER,Track text);"
Now For insertion,
1.Do I want to insert in to ALBUM table first or I can directly insert into
MUSIC table.If I am entering into MUSIC table I wont get the AlbumId from
ALBUM table since it is not inserted.Similar for ARTIST and BGM table.
By using Triggers is it possible to insert in to all tables .In
ALBUM,ARTIST,BGM tables only unique values of Album,Artist,Bgm should be
present.i.e,if I am entering a record in MUSIC table and the Album_Id is
already present in ALBUM table then I wont insert that Album in ALBUM table.
I tried with the following and its working.
"insert or ignore into ALBUM (Album) values('Album3');"
"insert or ignore into ARTIST (Artist) values('Artist1');"
"insert or ignore into BGM (Bgm) values('Rock');"
"insert into MUSIC (Album_Id,Artist_Id,Bgm_Id,Track) values((select AlbumId
from ALBUM where Album='Album3'),(select ArtistId from ARTIST where
Artist='Artist1'),(select BgmId from BGM where Bgm='Rock'),'Track1.mp3');"
I want to know weather the above is better than TRIGGERS.
If we can do this by INSERT TRIGGERS how the statement looks like [For both
insert and update].
Even if update any records in one table It has to be changed in all the
others Please help to solve this.
I think you will need to use a sequence of insert statements as you have
shown above. Insert triggers won't work.
One further thing to note, you probably also need to store a reference
to the artist in the album table since multiple artists could have
albums with the same name ("Greatest Hits" comes to mind immediately).
Now the combination of the artist id and the album name must be unique.
This also means you will have to do the inserts into the artist table
before the insert into the album table, so that you have an artist id to
assign to the record in the album table.
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,Artist
Text,unique(Artist));"
"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, ArtistId INTEGER,
Album Text,unique(ArtistId, Album));"
"CREATE TABLE BGM (BgmId INTEGER PRIMARY KEY NOT NULL,Bgm
Text,unique(Bgm));"
You should probably also normalize your playlist information into two tables,
one that store the name and id of each playlist, and one that relates the
playlist to the music table entries in that playlist.
"CREATE TABLE MUSIC (MusicId INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER, Track Text);"
"CREATE TABLE PLAYLIST(PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListName
Text);"
"CREATE TABLE TRACKS(PlayListId INTEGER, MusicId INTEGER);"
You haven't said how you are accessing SQLite (i.e. C/C++ direct API calls or
some scripting language), but you can optimize your inserts if you use the
last_insert_id() function to retrieve and save the ids assigned as you insert
rows into the various tables. This will eliminate some unnecessary select
lookups.
"insert or ignore into ARTIST (Artist) values('Artist1');"
artist = sqlite3_last_insert_id();
"insert or ignore into ALBUM (Album, ArtistId) values('Album3', :artist);"
album = sqlite3_last_insert_id();
"insert or ignore into BGM (Bgm) values('Rock');"
bgm = sqlite3_last_insert_id();
"insert into MUSIC (Album_Id,Artist_Id,Bgm_Id) values(:album, :artist, :bgm,
'Track1.mp3');"
music = sqlite3_last_insert_id();
Now you can add this song to the playlist "New Music".
insert into table TRACKS values(
(select PlayListId from PLAYLIST where PlayListName = 'New Music'),
:music);
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
