Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote: Hi Dennis, I have created 2 tables for PlayList as u suggested as Follows. "CREATE TABLE MUSIC (MusicId INTEGER PRIMARY KEY NOT NULL,Album_Id INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER, Track Text);" MusicId Album_Id Artist_Id Bgm_Id Track 1111 T1.mp3 2112 T2.mp3 3113 T3.mp3 4221 S1.mp3 5222 S2.mp3 6221 S3.mp3 "CREATE TABLE PLAYLIST(PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListName Text);" PlayListId PlayListName 1PlayList1 2PlayList2 "CREATE TABLE TRACKS(PlayListId INTEGER, MusicId INTEGER);" PlayListId MusicId 11 24 12 26 13 16 My Doubt is: If i want to list the MUSIC.Track for Playlist1.With the below statement i could able to get only the First result. ie, T1.mp3 "SELECT Track from MUSIC where MUSIC.Id=(SELECT MusicId FROM TRACKS WHERE TRACKS.PlayListId = (SELECT Id FROM PLAYLIST WHERE PlayListName ='Maha'));" But my desired result is as follows. T1.mp3 T2.mp3 T3.mp3 S3.mp3 Can u please correct where i am wrong. You need to join the playlist, tracks, and music tables to do this. Select Track from PLAYLIST join TRACKS using PlayListId join MUSIC using MusicId where PlayListName = 'Maha'; Also, I realized after I posted my last message that using last_insert_rowid is probably not a good idea. It will only return the correct rowid when the insert or ignore actually does an insert. If the row already exists in the table, it will return the wrong rowid. You should stick with the original selects, unless you know that you are inserting a new row. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite:Deletion in Joins method
Hi Dennis,
I have created 2 tables for PlayList as u suggested as Follows.
"CREATE TABLE MUSIC (MusicId INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER, Track Text);"
MusicId Album_Id Artist_Id Bgm_Id Track
1111
T1.mp3
2112
T2.mp3
3113
T3.mp3
4221
S1.mp3
5222
S2.mp3
6221
S3.mp3
"CREATE TABLE PLAYLIST(PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListName
Text);"
PlayListId PlayListName
1PlayList1
2PlayList2
"CREATE TABLE TRACKS(PlayListId INTEGER, MusicId INTEGER);"
PlayListId MusicId
11
24
12
26
13
16
My Doubt is:
If i want to list the MUSIC.Track for Playlist1.With the below statement i
could able to get only the First result. ie, T1.mp3
"SELECT Track from MUSIC where MUSIC.Id=(SELECT MusicId FROM TRACKS WHERE
TRACKS.PlayListId = (SELECT Id FROM PLAYLIST WHERE PlayListName ='Maha'));"
But my desired result is as follows.
T1.mp3
T2.mp3
T3.mp3
S3.mp3
Can u please correct where i am wrong.
_
>Dennis wrote:
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]
-
Re: [sqlite] sqlite:Deletion in Joins method
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]
-
Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text); CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE ); I created a trigger as follows . Here I am checking after deleting that record in Music Table I will check wheather that Artist_id is present now in that table MUSIC i.e, (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in the ARTIST table. But this is not happening with the below trigger. Do I need to add more constraints in the below trigger. Please help to solve this. "CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN SELECT CASE WHEN (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id' END; END;" You need to use a conditional delete in your trigger. You can't do that using a select with a case statement. You could try something like this (untested): CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN delete from Artist where ArtistId = old.ArtistId and not exist (select id from music where ArtistId = old.ArtistId); delete from Album where AlbumId = old.AlbumId and not exist (select id from music where AlbumId = old.AlbumId); delete from BGM where BgmId = old.BgmId and not exist (select id from music where BgmId = old.BgmId); END;" A couple of other things to note: You should probably change your table definitions to key the phrase "INTEGER PRIMARY KEY" together so that sqlite can use its btree key optimization. Instead of this: CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); use this: CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, Album Text); Also, if you want to speed up the searches in the deletes trigger above, at the expense of slowing down all the insert and delete operations into the tables, you could add indexes on the individual Id columns in the music table. create index MusicArtistId on Muisc(ArtistId); create index MusicAlbumId on Muisc(AlbumId); create index MusicBgmId on Muisc(BgmId); HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite:Deletion in Joins method
Dennis wrote: I think you need to add a delete trigger on the music that will delete records in the album, artist, and bgm tables if there are no other records with the same albumId, artitId, or bgmId in the music table (i.e. if this is the last record in the music table that references a record in each of the other tables). Hi, For the below tables, CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text); CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text); CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text); CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY,Album_Id INTEGER CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId) ON DELETE CASCADE,Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON DELETE CASCADE,Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES BGM(BgmId)ON DELETE CASCADE ); I created a trigger as follows . Here I am checking after deleting that record in Music Table I will check wheather that Artist_id is present now in that table MUSIC i.e, (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id).And if that Artist_Id is Null then I will delete it in the ARTIST table. But this is not happening with the below trigger. Do I need to add more constraints in the below trigger. Please help to solve this. "CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW BEGIN SELECT CASE WHEN (SELECT Artist_Id FROM MUSIC WHERE MUSIC.Artist_Id = OLD.Artist_Id) IS NOT NULL THEN 'DELETE FROM ARTIST WHERE ArtistId=OLD.Artist_Id' END; END;" -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 11:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite:Deletion in Joins method Sreedhar.a wrote: > > > I have only one ALBUM named 'Confession' with id 1 and > > only one ARTIST named 'Madonna' with id 2 and > > one BGM named 'rock' with id 5. > > MUSIC table will have all these details. > > > > Now If I delete the Album 'Confession' From table ALBUM. > > Then it is deleting that record with that id i.e,1 for 'confession' in > MUSIC table.So now there is no records in the MUSIC table. > > But it is not deleting in the ARTIST and the BGM table since that > Artist ' > Madonna ' and BGM 'rock' are present in one record and that record too > deleted in Music table. For me the complete record details to be > deleted in all the tables. > > > > I think I have to add some more constraints in triggers/sql statemets > > I think you need to add a delete trigger on the music that will delete records in the album, artist, and bgm tables if there are no other records with the same albumId, artitId, or bgmId in the music table (i.e. if this is the last record in the music table that references a record in each of the other tables). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote: I have only one ALBUM named 'Confession' with id 1 and only one ARTIST named 'Madonna' with id 2 and one BGM named 'rock' with id 5. MUSIC table will have all these details. Now If I delete the Album 'Confession' From table ALBUM. Then it is deleting that record with that id i.e,1 for 'confession' in MUSIC table.So now there is no records in the MUSIC table. But it is not deleting in the ARTIST and the BGM table since that Artist ' Madonna ' and BGM 'rock' are present in one record and that record too deleted in Music table. For me the complete record details to be deleted in all the tables. I think I have to add some more constraints in triggers/sql statemets I think you need to add a delete trigger on the music that will delete records in the album, artist, and bgm tables if there are no other records with the same albumId, artitId, or bgmId in the music table (i.e. if this is the last record in the music table that references a record in each of the other tables). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite:Deletion in Joins method
Hi, Thanks for your suggestion Dennis. Now I normalized the database as follows: * CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);" * CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text);" * CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text);" * CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY, Album_Id INTEGER CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId)ON DELETE CASCADE, Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON DELETE CASCADE, Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES BGM(BgmId) ON DELETE CASCADE );" The following are the triggers: // -- Foreign Key Preventing insert "CREATE TRIGGER fki_MUSIC_Album_Id_ALBUM_AlbumId BEFORE INSERT ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table MUSIC violates foreign key constraint fki_MUSIC_Album_Id_ALBUM_AlbumId') WHERE NEW.Album_Id IS NOT NULL AND (SELECT AlbumId FROM ALBUM WHERE AlbumId = NEW.Album_Id) IS NULL;END;" //-- Foreign key preventing update "CREATE TRIGGER fku_MUSIC_Album_Id_ALBUM_AlbumId BEFORE UPDATE ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table MUSIC violates foreign key constraint fku_MUSIC_Album_Id_ALBUM_AlbumId') WHERE NEW.Album_Id IS NOT NULL AND (SELECT AlbumId FROM ALBUM WHERE AlbumId = NEW.Album_Id) IS NULL;END;" //-- Cascading Delete "CREATE TRIGGER fkdc_MUSIC_Album_Id_ALBUM_AlbumId BEFORE DELETE ON ALBUM FOR EACH ROW BEGIN DELETE FROM MUSIC WHERE MUSIC.Album_Id = OLD.AlbumId; END;" //-- Foreign Key Preventing insert "CREATE TRIGGER fki_MUSIC_Artist_Id_ARTIST_ArtistId BEFORE INSERT ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table MUSIC violates foreign key constraint fki_MUSIC_Artist_Id_ARTIST_ArtistId') WHERE (SELECT ArtistId FROM ARTIST WHERE ArtistId = NEW.Artist_Id) IS NULL; END;" //-- Foreign key preventing update "CREATE TRIGGER fku_MUSIC_Artist_Id_ARTIST_ArtistId BEFORE UPDATE ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table MUSIC violates foreign key constraint fku_MUSIC_Artist_Id_ARTIST_ArtistId') WHERE (SELECT ArtistId FROM ARTIST WHERE ArtistId = NEW.Artist_Id) IS NULL; END;" //-- Cascading Delete "CREATE TRIGGER fkdc_MUSIC_Artist_Id_ARTIST_ArtistId BEFORE DELETE ON ARTIST FOR EACH ROW BEGIN DELETE FROM MUSIC WHERE MUSIC.Artist_Id = OLD.ArtistId; END;" //-- Foreign Key Preventing insert "CREATE TRIGGER fki_MUSIC_Bgm_Id_BGM_BgmId BEFORE INSERT ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table MUSIC violates foreign key constraint fki_MUSIC_Bgm_Id_BGM_BgmId') WHERE (SELECT BgmId FROM BGM WHERE BgmId = NEW.Bgm_Id) IS NULL; END;" //-- Foreign key preventing update "CREATE TRIGGER fku_MUSIC_Bgm_Id_BGM_BgmId BEFORE UPDATE ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table MUSIC violates foreign key constraint fku_MUSIC_Bgm_Id_BGM_BgmId') WHERE (SELECT BgmId FROM BGM WHERE BgmId = NEW.Bgm_Id) IS NULL; END;" //-- Cascading Delete "CREATE TRIGGER fkdc_MUSIC_Bgm_Id_BGM_BgmId BEFORE DELETE ON BGM FOR EACH ROW BEGIN DELETE FROM MUSIC WHERE MUSIC.Bgm_Id = OLD.BgmId; END;" My Database table looks similar like this: There is only one record in the database with the following values, ALBUM Table ARTIST TableBGM Table AlbumId : 1 ArtistId: 2 BgmId : 5 Album :Confession Artist : Madonna Bgm : rock MUSIC Table Id : 7 Album_Id: 1 Artist_Id : 2 Bgm_Id : 5 I have only one ALBUM named 'Confession' with id 1 and only one ARTIST named 'Madonna' with id 2 and one BGM named 'rock' with id 5. MUSIC table will have all these details. Now If I delete the Album 'Confession' From table ALBUM. Then it is deleting that record with that id i.e,1 for 'confession' in MUSIC table.So now there is no records in the MUSIC table. But it is not deleting in the ARTIST and the BGM table since that Artist ' Madonna ' and BGM 'rock' are present in one record and that record too deleted in Music table. For me the complete record details to be deleted in all the tables. I think I have to add some more constraints in triggers/sql statemets Can anyone please help me to solve this . Do i need to normalize anything in the create statements? Thanks & Regards, Sreedhar.A
RE: [sqlite] sqlite:Deletion in Joins method
Hi, Thank you very much Dennis for the reply. I will try the method suggested by you. Best Regards, A.Sreedhar. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 1:31 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite:Deletion in Joins method Sreedhar.a wrote: > Hi, > > I have created database using the joins method. > > My database caontains 4 tables with the Artist(1),Album(2),Genre(or) > BGM(3),Combination of all these and Track names in one table. > > I am having 4 tables as follows. > > * > > "create table ALBUM(AlbumId integer primary key,Album text);" > > 21 Album1 > 22 Album2 > 23 Album3 > > * "create table ARTIST(ArtistId integer primary key,Artist text);" > > 10 Madonna > 11 Artist1 > > * > > "create table BGM(BgmId integer primary key,Bgm text);" > > 31 rock > 32 pop > > * > > "create table MUSIC(Id integer primary key,AlbumName > text,Album_TypeId integer,ArtistName text,Artist_TypeId > integer,BgmName text,Bgm_TypeId integer);" > > 1 Album1 21Madonna10rock 31 > 2 Album2 22Madonna10pop 32 > > If we want to delete a particular Artist from ARTIST table. > I need to delete all the records corresponding with that artist name > in MUSIC table also and we need to check for the albums and Genre(BGM) > in Music are not present after deleting that particular Artist and > delete the records in ALBUM and BGM Table . > > Consider I have an Artist Madonna in ARTIST table. the user wants to > delete Madonna artist. > Currently, > 1.We are deleting Madonna in ARTIST table. > 2.Weare first reading the album ids of Madonna and Bgm > id's of Madonna in one buffer and then we are deleting that Artist > Madonna in the MUSIC table. > 3.Now we will check wheather that Album ids and BGM > ids in buffer is still present in MUSIC table.If it does not present > we will delete it in the ALBUM and BGM table.If it still exists we > wont delete it in ALBUM and BGM table. > > But if we do like this we got the desired result but buffer size is > incresing if records are increasing. > Is there any other method to solve deletion in multiple table. > > Can anyone of you suggest how i can do the deletion. > > > Best Regards, > A.Sreedhar. > > > Hi, I would first suggest that you normalize your database. By that I mean, remove the redundant copies of the artist name, album name, and BGM name from the music table. Given these tables: create table ALBUM(AlbumId integer primary key, Album text); create table ARTIST(ArtistId integer primary key, Artist text); create table BGM(BgmId integer primary key, Bgm text); Your music table should probably look something like this: create table MUSIC( Id integer primary key, AlbumId integer references ALBUM, ArtistId integer references ARTIST, BgmId integer references BGM ); Now you can generate a table of results much like your previous music table by joining these tables like this: select Album, Artist, Bgm from MUSIC join ALBUM using AlbumId join ARTIST using ArtistId join BGM using BgmId; The one thing to note here is that even though I have indicated that the *Id fields are foreign keys in the Music table by adding the references clause, SQLite does not do anything with that information. It is basically a comment for human readers. You can add triggers to the database that will automatically ensure referential integrity. With these triggers defined, SQLite will handle the the cascaded deletes for you. This means that it will automatically delete all music by an artist when that artist is deleted. You can get more info on these triggers at http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers Your code won't need to do these deletes and it won't need buffer space to store intermediate results you are using to do the deletes manualy. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite:Deletion in Joins method
Sreedhar.a wrote: Hi, I have created database using the joins method. My database caontains 4 tables with the Artist(1),Album(2),Genre(or) BGM(3),Combination of all these and Track names in one table. I am having 4 tables as follows. * "create table ALBUM(AlbumId integer primary key,Album text);" 21 Album1 22 Album2 23 Album3 *"create table ARTIST(ArtistId integer primary key,Artist text);" 10 Madonna 11 Artist1 * "create table BGM(BgmId integer primary key,Bgm text);" 31 rock 32 pop * "create table MUSIC(Id integer primary key,AlbumName text,Album_TypeId integer,ArtistName text,Artist_TypeId integer,BgmName text,Bgm_TypeId integer);" 1 Album1 21Madonna10rock 31 2 Album2 22Madonna10pop 32 If we want to delete a particular Artist from ARTIST table. I need to delete all the records corresponding with that artist name in MUSIC table also and we need to check for the albums and Genre(BGM) in Music are not present after deleting that particular Artist and delete the records in ALBUM and BGM Table . Consider I have an Artist Madonna in ARTIST table. the user wants to delete Madonna artist. Currently, 1.We are deleting Madonna in ARTIST table. 2.Weare first reading the album ids of Madonna and Bgm id's of Madonna in one buffer and then we are deleting that Artist Madonna in the MUSIC table. 3.Now we will check wheather that Album ids and BGM ids in buffer is still present in MUSIC table.If it does not present we will delete it in the ALBUM and BGM table.If it still exists we wont delete it in ALBUM and BGM table. But if we do like this we got the desired result but buffer size is incresing if records are increasing. Is there any other method to solve deletion in multiple table. Can anyone of you suggest how i can do the deletion. Best Regards, A.Sreedhar. Hi, I would first suggest that you normalize your database. By that I mean, remove the redundant copies of the artist name, album name, and BGM name from the music table. Given these tables: create table ALBUM(AlbumId integer primary key, Album text); create table ARTIST(ArtistId integer primary key, Artist text); create table BGM(BgmId integer primary key, Bgm text); Your music table should probably look something like this: create table MUSIC( Id integer primary key, AlbumId integer references ALBUM, ArtistId integer references ARTIST, BgmId integer references BGM ); Now you can generate a table of results much like your previous music table by joining these tables like this: select Album, Artist, Bgm from MUSIC join ALBUM using AlbumId join ARTIST using ArtistId join BGM using BgmId; The one thing to note here is that even though I have indicated that the *Id fields are foreign keys in the Music table by adding the references clause, SQLite does not do anything with that information. It is basically a comment for human readers. You can add triggers to the database that will automatically ensure referential integrity. With these triggers defined, SQLite will handle the the cascaded deletes for you. This means that it will automatically delete all music by an artist when that artist is deleted. You can get more info on these triggers at http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers Your code won't need to do these deletes and it won't need buffer space to store intermediate results you are using to do the deletes manualy. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
