RE: [sqlite] sqlite:Deletion in Joins method

[Sreedhar.a]

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     21    Madonna    10    rock     31
>     2       Album2     22    Madonna    10    pop      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]
-----------------------------------------------------------------------------