[sqlite] Database corruption

[Sreedhar.a]

Hi,
 
I am using the Sqlite for my server database application purpose.
I want to make sure that the database does not get corrupted.
 
If by chance if i corrupts the database by what means can i restore it back?
 
What are the various possibilities of database corrupting methods in sqlite?
so that i can try to avoid all those.
 
 
Best Regards,
A. Sreedhar.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Sreedhar.a]

Hi,

We are working on different operating system other than unix and windows.
We are using the os_win.c for porting, we have ported the code by making
some changes with respect to the FAT32 file system apis in the code.

We have not tested much, my worry is whether we also need to take care of
such in the os_win.c
In our OS also.

Also, is this the OS dependent?

Best Regards,
A. Sreedhar.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Friday, February 08, 2008 7:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VFS memory leak : During lock / unlock operations

<[EMAIL PROTECTED]> wrote:
> Hi..
>  
>we are using sqlite3.3.4 with Integrity OS. we are facing a problem 
> where in the VFS memory is getting exhausted due
>to large lock/unlock calls made by sqlite. Integrity support team 
> said that,  for each file lock call made by sqlite,  a definite amount
>of memory is allocated, this memory is released only after the 
> unlock or when the file is closed. And they are claiming that
>number of file unlock calls are not same as the number of file lock 
> calls. they have put traces and identified that for 1000 lock calls
>there are only 950 unlock calls, which is a shortage of 50 unlock 
> calls. This will leak considerable amount of memory if the
>system is left for long hours, with continuous sqlite operations 
> being made.
>  
> can any one throw some light on this problem, is there any known 
> issue like this. Any information on this will be very helpful

The os_unix.c backend to SQLite makes no attempt to match lock/unlock calls,
because posix does not requires such.  If you are running on an operating
system that does require matching lock/unlock calls, you will probably need
to modify the os_unix.c layer in order for it to work properly on your
system.


>  
> thanks
> murthy
> 
> Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
> 
> The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s) and
may contain proprietary, confidential or privileged information. If you are
not the intended recipient, you should not disseminate, distribute or copy
this e-mail. Please notify the sender immediately and destroy all copies of
this message and any attachments. 
> 
> WARNING: Computer viruses can be transmitted via email. The recipient
should check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email. 
> 
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> .


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] For Better Performance

[Sreedhar.a]

Hi,
 
I am using sqlite for meta data storage of audio files.
I am storing the sqlite database in hard disk.
The sector size of FAT file system is 512 bytes.
Hard disk rpm is 4200
Page size = 1K
cache size = 2k
The processor speed is 600 Mhz.
 
I am using joins method in sqlite.The records are the meta data information
of the audio files.
How i can improve my search and insertion speeds?
 
any change in the above parameters will help?
 
Thanks and Regards,
A.Sreedhar.
 
 

[sqlite]For best Fragementation

[Sreedhar.a]

Hi,
 
I am working in 3.3.6 C code.
 
I created a database and i started inserting 100 records .
After inserting i checked the database File.Say 98% of the database is
filled and the remaining 2% is left out without use.
Then i inserted next 100 records and i found again 2% is left out.So totally
4% is left free.
I think by using "PRAGMA auto_vacuum = 1;" i can do fragmentation.
 
I need the database to shrink when ever it finds freespace in the database
file.
Can any one please help.
 
 
Best Regards,
A.Sreedhar.
 

[sqlite] Using Indexing in Joins Method

[Sreedhar.a]

Hi,

I am having 4 records and
My table looks like ,

"CREATE TABLE ALBUMARTIST(AlbumArtistId INTEGER PRIMARY KEY NOT
NULL,AlbumArtistName TEXT NOT NULL COLLATE NOCASE DEFAULT 'Unknown',
UNIQUE(AlbumArtistName));"

"CREATE TABLE ARTIST(ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
NOT NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(ArtistName));"

"CREATE TABLE BGM(BgmId INTEGER PRIMARY KEY NOT NULL,BgmName TEXT NOT NULL
COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(BgmName));"

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT NULL
COLLATE NOCASE DEFAULT 'Unknown',Track TEXT NOT NULL,URL TEXT NOT
NULL,Artist_Id INTEGER,AlbumArtist_Id INTEGER,Bgm_Id INTEGER);"

Where Artist_Id , AlbumArtist_Id , Bgm_Id are the type ids of table
ALBUMARTIST,ARTIST,BGM,MUSIC .

I will search for the following

1. SELECT *  FROM ARTIST ORDER BY ArtistName; 2. SELECT * FROM ALBUMARTIST
ORDER BY AlbumArtistName; 3. SELECT Track,URL FROM MUSIC ORDER BY Track ; 4.
SELECT BgmId,BgmName FROM BGM ; 5. SELECT DISTINCT Album FROM MUSIC WHERE
Artist_Id = ? ORDER BY Album ; 6. SELECT Track,URL FROM MUSIC WHERE
Artist_Id = %s ORDER BY Track ; 7. SELECT Track,URL FROM MUSIC WHERE
Artist_Id = ? and Album = ? ORDER BY Track; 8. SELECT Track,URL FROM MUSIC
WHERE AlbumArtist_Id = '%s' ORDER BY Track; 9. SELECT Track,URL FROM MUSIC
WHERE Bgm_Id = '%s' GROUP BY Track ;

To achieve better performance do I want to index the tables
ALBUMARTIST,ARTIST,BGM or its not needed.
Will the performance increase by doing indexing the Artist_Id , Album in
MUSIC table. 

Kindly suggests some ways.

Thanks & Regards,
Sreedhar.A







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite]:Using sqlite3_progress_handler for GUI application

[Sreedhar.a]

Hi,

Thankyou very much for the suggestions.

Best Regards,
A.Sreedhar.
 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 19, 2007 12:41 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite]:Using sqlite3_progress_handler for GUI application

Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> On Tue, Dec 18, 2007 at 12:24:25PM +, Simon Davies wrote:
> 
> > Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> 
> One question - using the example mentioned there:
> 
> If we've created an index: CREATE INDEX example1 ON tracks(singer, 
> title);
> 
> So, it'll make the query, like below, much faster:
> 
> SELECT title FROM tracks
>  WHERE singer='Madonna'
>AND title<:firsttitle
>  ORDER BY title DESC
>  LIMIT 5;
> 
> but I understand, that when I'll try to add in the query a field 
> not covered by index "example1", like this:
> 
> SELECT title FROM tracks
>  WHERE singer='Madonna'
>AND title<:firsttitle
>AND year_ed > 1985;
> 
> so, then I'm losing every profit from having "example1" index, 
> right? Or perhaps "not quite every", and some speedup still remains - 
> just because _some_ columns are covered by example1 anyway?

The index is still used to speed the search.  But the extra "AND
year_ed>1985" term requires SQLite to check each row coming out of the index
an discard those for which the condition is not true.  This might be a small
or a large loss in performance, depending on how many rows match the
condition.

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite]:Using sqlite3_progress_handler for GUI application

[Sreedhar.a]

 
Hi,

 Say,I am having 4 entries in the table ALBUM.

"CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT
NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));"

Now I want to list the first 100 Album from ALBUM table.The result has to be
in sorting order.So my querry is like.

"SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;"

So for the next time I will change the offset to 200 then 300 ...
But the search Speed is fast initially and slows down later since it has
4 records.Since I want to display the results in the GUI Is there any
other way to can increase my speed.
I came across sqlite3_progress_handler() .Will this be better for my case.If
so kindly suggest where I can read to know more about this.

if ( sqlite3_prepare(pst_SqliteCallback->db"SELECT AlbumId ,Album FROM ALBUM
ORDER BY Album;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK) {
return SQLITE_EXEC_ERROR;
}
u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt);
sqlite3_progress_handler(db,26,xCallback,pArg);>Is this the right place.
u32_NumCols = sqlite3_column_count(pst_SearchPrepareStmt);

ps8_ColumnName = sqliteMalloc(2*u32_NumCols*sizeof(const char *) + 1);

while( u32_ReturnStatus == SQLITE_ROW )
{
for(u32_Count = 0; u32_Count < u32_NumCols; u32_Count++) {
  ps8_ColumnName[u32_Count] = sqlite3_column_name(pst_SearchPrepareStmt,
u32_Count);
  u8_SearchResult=sqlite3_column_text(pst_SearchPrepareStmt, u32_Count); }
u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt);
}
u32_ReturnStatus = sqlite3_finalize(pst_SearchPrepareStmt);

Kindly help to solve this.

Regards,
Sreedhar

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 04, 2007 9:56 PM
To: sqlite-users@sqlite.org
Subject: 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]

-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite]:Using sqlite3_progress_handler for GUI application

[Sreedhar.a]

 

Hi,

 Say,I am having 4 entries in the table ALBUM.

"CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT NOT
NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));"

Now I want to list the first 100 Album from ALBUM table.The result has to be
in sorting order.So my querry is like.

"SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;"

So for the next time I will change the offset to 200 then 300 ...
But the search Speed is fast initially and slows down later since it has
4 records.Since I want to display the results in the GUI Is there any
other way to can increase my speed.
I came across sqlite3_progress_handler() .Will this be better for my case.If
so kindly suggest where I can read to know more about this.

if ( sqlite3_prepare(pst_SqliteCallback->db"SELECT AlbumId ,Album FROM ALBUM
ORDER BY Album;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK) {
return SQLITE_EXEC_ERROR;
}
u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt);
sqlite3_progress_handler(db,26,xCallback,pArg);>Is this the right place.
u32_NumCols = sqlite3_column_count(pst_SearchPrepareStmt);

ps8_ColumnName = sqliteMalloc(2*u32_NumCols*sizeof(const char *) + 1);

while( u32_ReturnStatus == SQLITE_ROW )
{
for(u32_Count = 0; u32_Count < u32_NumCols; u32_Count++) {
  ps8_ColumnName[u32_Count] = sqlite3_column_name(pst_SearchPrepareStmt,
u32_Count);
  u8_SearchResult=sqlite3_column_text(pst_SearchPrepareStmt, u32_Count); }
u32_ReturnStatus = sqlite3_step(pst_SearchPrepareStmt);
}
u32_ReturnStatus = sqlite3_finalize(pst_SearchPrepareStmt);

Kindly help to solve this.

Regards,
Sreedhar

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 04, 2007 9:56 PM
To: sqlite-users@sqlite.org
Subject: 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]

-








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Heap Memory usage in Sqlite

[Sreedhar.a]

Hi,
 
I am working with the database of 40k records.
My database table contains the metadata information of audio files.
 
When I searched for the first 50 records, the heap usage is small.
when I searched for the last 50 records, the heap usage is almost equal to
searching the entire database table.
 
Is it the same way the sqlite behaves? or I am doing anything wrong.
 
Thanks & Best Regards,
A.Sreedhar.
 
 

RE: [sqlite] Unicode support for Sqlite?

[Sreedhar.a]

 
Thankyou all for the quick replies.

Best Regards,
A.Sreedhar.
 

-Original Message-
From: Trevor Talbot [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 12, 2007 5:08 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unicode support for Sqlite?

On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

> I am using the sqlite to store the metadata of audio files.
> Is it possible to store the metadata in unicode character format in
sqlite.

Yes; SQLite assumes all TEXT type data in the database is Unicode. You can
work with it in UTF-8 with the *_text() APIs, or UTF-16 using the
*_text16() calls. SQLite will convert between the two encodings as
necessary.

The sqlite3 shell assumes UTF-8, but it depends on the platform's console to
actually use UTF-8 when talking to it, so it may be difficult to properly
test with it.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Unicode support for Sqlite?

[Sreedhar.a]

 
Hi,

I am using the sqlite to store the metadata of audio files.
Is it possible to store the metadata in unicode character format in sqlite.

Best Regards,
A.Sreedhar.
 

-Original Message-
From: Trevor Talbot [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 12, 2007 4:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unicode support for Sqlite?

On 12/12/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

> Does Sqlite support unicode?
> I have seen that it supports utf-8 and utf-16.
> I want to know whether it supports unicode character formats.

Unicode is a very large and complex topic, so that question is way too vague
to answer. Can you provide an example of what you're looking for?


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Unicode support for Sqlite?

[Sreedhar.a]

Hi,
 
Does Sqlite support unicode?
I have seen that it supports utf-8 and utf-16.
I want to know whether it supports unicode character formats.
 
Thanks and Best Regards,
A.Sreedhar.
 
 

RE: [sqlite] sqlite:Deletion in Joins method

[Sreedhar.a]

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]

-

[sqlite] sqlite:Deletion in Joins method

[Sreedhar.a]

 
Hi Dennis,

Thanks a lot for ur help.

Ya.Its deleting properly in all the tables by the following trigger.

"CREATE TRIGGER fkdc_MUSIC
 AFTER DELETE ON MUSIC
FOR EACH ROW BEGIN
DELETE from ARTIST where ArtistId = old.Artist_Id and not exists (select id
from MUSIC where Artist_Id = old.Artist_Id); DELETE from ALBUM where AlbumId
= old.Album_Id  and not exists (select Id from MUSIC where Album_Id =
old.Album_Id); DELETE from BGM where BgmId = old.Bgm_Id  and not exists
(select Id from MUSIC where Bgm_Id = old.Bgm_Id); DELETE from PLAYLIST where
TrackId = old.Id and not exists (select Id from MUSIC where Id = old.Id);
END;"


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.

Thanks & Regards,
Sreedhar.A


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 04, 2007 9:56 PM
To: sqlite-users@sqlite.org
Subject: 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 

RE: [sqlite] sqlite:Deletion in Joins method

[Sreedhar.a]

 

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]

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

[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 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]
-

[sqlite] compilation error in sqlite 3.5.3

[Sreedhar.a]

Hi,
 
I am trying to compile the latest version of the sqlite 3.5.3 in vc++.
i got the following error.
 
Can anyone help me, what i am missing to add.
I just downloaded all the source files and created a project and started
compiling.
 
Compiling...
tclsqlite.c
e:\sharing\sqlitedb\sqlite3_5_3\tclsqlite.c(17) : fatal error C1083: Cannot
open include file: 'tcl.h': No such file or directory
Error executing cl.exe.
 
Sqlite3_5_3.exe - 1 error(s), 0 warning(s)
 
Where i can get this tcl.h file
 
Best Regards,
A.Sreedhar.
 
 

[sqlite] sqlite:Deletion in Joins method

[Sreedhar.a]

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.
 
 

RE: [sqlite] commit and rollback

[Sreedhar.a]

>so there is really no way that multiple processes can write into the
database?..but multiple processes can read at the >>same time right?..

--Yes

-Sreedhar


Igor Tandetnik wrote:
> 
> arbalest06 <[EMAIL PROTECTED]> wrote:
>> q#1: is it possible that multiple users can write into the database 
>> at the same time?
> 
> No.
> 
>> q#2: if users A, B, C are writing to the database at the same time,
> 
> They can't.
> 
>> q#3: if users A, B, C are writing to the database at the same time,
> 
> They can't.
> 
> Igor Tandetnik
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 
> 
> 

--
View this message in context:
http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Lock and Unlock mechanism

[Sreedhar.a]

Hi,
 
I am working with OS which does not has the support of Lockfile and
Unlockfile functions.
How I can I implement the Sqlite using the File I/O method.
 
Thanks in advance, Your suggestions will help a lot in my project.
 
Best Regards,
A.Sreedhar.
 
Jasmin Infotech Pvt. Ltd.
Plot 119, Velachery Tambaram Road,
(Opposite NIOT), Pallikaranai,
Chennai 601 302
India
Tel: +91 44 3061 9600 ext 3057
Fax: + 91 44 3061 9605 
 

***
Information in this email is proprietary and Confidential to 
Jasmin Infotech. Any use, copying or dissemination of the
information in any manner is strictly prohibited. If you are 
not the intended recipient, please destroy the message and please inform us.
 

 

[sqlite] sqlite: open and close problem

[Sreedhar.a]

Hi,
 
I open the database sqlite3_open, and do the required processing uisng
sqlite3_exec()
and after that i calls sqlite3_close().
 
In this order:
 
sqlite3_open
 
sqlite3_exec()
 
sqlite3_close()
 
If i open only once and try to do the searching/inserting of records my
program is crashing.
 
Is it necessary that i should close the opened database once the
sqlite3_exec is called.
 
Your suggestions are most welcome.
 
Best Regards,
Sreedhar.

FW: [sqlite] select COUNT (DISTINCT column1, column2) from table?

[Sreedhar.a]

 
Seems useful

Best Regards,
A.Sreedhar.


-Original Message-
From: Dennis Povshedny [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 25, 2007 4:40 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?

Hi Phani!

For your sample the following query will fit:

select COUNT (DISTINCT year*12+month) FROM m

If you take a look at
EXPLAIN select COUNT (DISTINCT year*12+month) FROM m you will see that
effectiveness is almost the same than in case of EXPLAIN select COUNT
(DISTINCT year) FROM m

and significantly better than in
SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM m group by year,month);


If it is not a real sample and you have string data you may concatenate
or something like this.

Hope this helps.

Regards, Dennis


Xeepe Phone Solution Team
http://en.xeepe.com
mailto:[EMAIL PROTECTED]
sip:[EMAIL PROTECTED]

-Original Message-
From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 25, 2007 2:46 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from
table?


Hi Simon,

Yeah, I thought of the query which u mentioned. But the problem is
overhead is too much.

I was wondering why SQL doesn't support something like:
Select COUNT (DISTINCT year, month) FROM table 
when it supports:
select COUNT (DISTINCT year) FROM table

Regards,
Phani

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.488 / Virus Database: 269.13.30/1029 - Release Date:
24.09.2007 19:09
 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Insertion and Search at a same time is it possible?

[Sreedhar.a]

Hi John Stanton,

Thankyou very much, I will try in this method.

Best Regards,
A.Sreedhar.


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 21, 2007 6:20 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Insertion and Search at a same time is it possible?

You can read and write to the database concurrently provided that your
program can handle SQLITE_BUSY events.

Sreedhar.a wrote:
> Hi Everyone,
>  
> I am implementing server database using sqlite.
>  
> I will be having the multiple clients browsing the database.
> At the same time the database can also be updated.
>  
> I can copy the database into the local memory of my system and can 
> perform search.
> Can i implement inserting the records at the same time into the same file.
> while searching
>  
> Your suggestions will help me a lot in my project.
>  
> Best Regards,
> A.Sreedhar.
>  
>  
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Insertion and Search at a same time is it possible?

[Sreedhar.a]

Hi Everyone,
 
I am implementing server database using sqlite.
 
I will be having the multiple clients browsing the database.
At the same time the database can also be updated.
 
I can copy the database into the local memory of my system and can perform
search.
Can i implement inserting the records at the same time into the same file.
while searching
 
Your suggestions will help me a lot in my project.
 
Best Regards,
A.Sreedhar.
 
 

[sqlite] Inserting Problem : More number of records at a single stretch

[Sreedhar.a]

Hi,

 

I am working in Sqlite 3.3.6

 

I want to insert more records[like 20,000.] at a single stretch.

 

I did the following in shell.c,

 

Static void process_input (struct callback_data *p)

{

  char *zLine;

  int nLine;

  nLine = 200;

  zLine = malloc( nLine );

 

Strcpy (&zLine[n],"create table MUSIC (Id integer primary key, Album text
not null collate nocase);"

  "Insert into MUSIC (Album)
values ('Deshamuduru');"

..);

While (zLine[n])

{ 

n++; 

}

 

ZLine = realloc (zLine, n+1 );

}

 

 

Inside process_input I am doing strcpy where I will insert all my records.

But my means of this I can able to insert only some 200 records. even if
increase the nLine value I can not able to insert more than that at a single
stretch.

I want 4 records to be in my database, for that each time I can only
insert 200.

Can any one help me to solve this problem by some other way?

I read in this forum like we can insert 20,000 . at a single stretch. How it
can be done. Where we have to change in the code? 

 

Best Regards,

Sreedhar. 

 

[sqlite] Inserting the Records

[Sreedhar.a]

Hi,
 
1.If I choose In memory method, will it be possible for me to insert the
records into the database file?
 
2. If i choose Disk I/O method can I use temp buffer for searching the
records and also can I insert the records at the same time.
 
 
Best Regards,
A.Sreedhar.
 
 

[sqlite] Order by

[Sreedhar.a]

Hai,
 
I am having 4 distinct Albums in one table.
 
To display all the 4 Albums in sorted order.I used the following
statement
 
"select distinct Albums from TableName order by Albums;"
 
To display first 50 Albums in sorted order.I used the following statement
 
"select distinct Albums from TableName order by Albums limit 50 offset 0;"
 
To display last 50 Albums in sorted order.I used the following statement
 
"select distinct Albums from TableName order by Albums limit 50 offset
39950;"
 
I noticed that performance is much slower when we use ORDER BY.
I got the output in 3000 msecs without ORDER BY and 7500 msecs with ORDER
BY.
 
Is there any other method where i can store the sorted results and use that
whenever needed instead f doing order by each time.
 
 
Best Regards,
A.Sreedhar.
 

[sqlite] unique id maximum value limiting

[Sreedhar.a]

Hi,
 
I am working on a 16 bit processor.
In windows the maximum value of unique id is 2 power 63 -1 
 
I want to restrict the maximum value of the unique id to 2 power 15 -1
 
Can anyone help me in this?.
 
Best Regards,
A.Sreedhar.
 
Jasmin Infotech Pvt. Ltd.
Plot 119, Velachery Tambaram Road,
(Opposite NIOT), Pallikaranai,
Chennai 601 302
India
Tel: +91 44 3061 9600 ext 3057
Fax: + 91 44 3061 9605 
 

***
Information in this email is proprietary and Confidential to 
Jasmin Infotech. Any use, copying or dissemination of the
information in any manner is strictly prohibited. If you are 
not the intended recipient, please destroy the message and please inform us.
 

 

RE: [sqlite] Client/Server approach

[Sreedhar.a]

Hi,

Yes, The server and clients are in Network.

Best Regards,
Sreedhar.

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 24, 2007 6:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Client/Server approach

You do not need a server to manage 5 clients on the same machine, only if
they are on a network.

Sreedhar.a wrote:
>  
> Hi,
> 
> I am working in sqlite 3.3.6.
> I want 5 clients to browse at the same time with good performance.
> I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT .
> I think by using test_server.c  we can do 5 clients browsing with 
> single server.
> Can any one please help me by providing some patch for implementing this.
> 
> My doubts are:
> 1.Do I need  to create a thread for each client.If so it has to be 
> created before Sqlite3_server_start().
> 
> (Or)
> Some thing like below has to be followed.
> 
> Main()
> {
> 
>sqlite3_client_open()
>sqlite3_client_prepare()
>sqlite3_client_step()
>sqlite3_client_reset()
>sqlite3_client_finalize()
>sqlite3_client_close()
> 
> Sqlite3_server_start(); For starting the server.
> 
> sqlite3_server_stop();For closing the server.
> 
> 
> 
> }
> 
> Kindly help me to solve this.
> Thanks in advance
> 
> Best Regards,
> A.Sreedhar.
>  
> 
> 
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How to generate Unique ID?

[Sreedhar.a]

Phani,

Read the "auto increment" in Sqlite FAQ s it is the first one in the list,
it will solve your problem.


Best Regards,
A.Sreedhar.
 
-Original Message-
From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 30, 2007 2:11 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to generate Unique ID?

Assume I have a table:

Create table YYY (id Interger PRIMARY KEY, puid Unique integer)

Id is the primary key.

Puid is an unsque interger, whose values needs to be assigned by the user.

 

Currently my approach is get the maximum value of puid stored in the table;
add 1 to it and uses this value as puid for any new row that needs to be
added. The problem occurs when I reach the max value.
Meanwhile, some rows might have been deleted. In case, when I reach the
maximum value I want to reuse the puids of the deleted rows for new rows
that are to be added. Currently SQLite uses some algorithm to generate a
unique rowid (even when it reaches the limit). I want to use the same
algorithm here also. I tried to understand the algorithm but couldn't. I
need a simple way by which I can generate a unique puid without writing the
algorithm.

 

 

Regards,

Phani

 

 




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Unique ids for each record

[Sreedhar.a]

Hi,

First my thanks to everyone for the response.
Sorry for the delay in replying I was on leave.
I am working on client server database method.

I am going to manage a large amount of data. Somewhere around 4 records.
Is it possible for me to fix the id range for each and every column.

So that I can do the search fast.

Best Regards,
Sreedhar.


-Original Message-
From: Dwight Ingersoll [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 21, 2007 11:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unique ids for each record

On 8/20/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

<>

>
> I want to restrict the Artist range to 100 to 199 so that with the id 
> I can make my search fast and also I knew with unique id that I need 
> to search for the Artist alone.


<>

Considering the small range of data it appears you want to deal with, SQLite
should be more than capable of quickly returning the results you desire,
especially since it appears that you're using a numeric field as primary
keys.

It would be helpful to understand why you chose this approach, and the
source of your data.  The reason I'm asking is because the project I'm
currently working on is an offline viewer which utilizes the data available
from the FreeDB Organization.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Client/Server approach

[Sreedhar.a]

 I am not using 5 clients from the same machine.
In a network 5 clients and 1 server will be present.


Regards,
A.Sreedhar.
 

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 24, 2007 6:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Client/Server approach

You do not need a server to manage 5 clients on the same machine, only if
they are on a network.

Sreedhar.a wrote:
>  
> Hi,
> 
> I am working in sqlite 3.3.6.
> I want 5 clients to browse at the same time with good performance.
> I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT .
> I think by using test_server.c  we can do 5 clients browsing with 
> single server.
> Can any one please help me by providing some patch for implementing this.
> 
> My doubts are:
> 1.Do I need  to create a thread for each client.If so it has to be 
> created before Sqlite3_server_start().
> 
> (Or)
> Some thing like below has to be followed.
> 
> Main()
> {
> 
>sqlite3_client_open()
>sqlite3_client_prepare()
>sqlite3_client_step()
>sqlite3_client_reset()
>sqlite3_client_finalize()
>sqlite3_client_close()
> 
> Sqlite3_server_start(); For starting the server.
> 
> sqlite3_server_stop();For closing the server.
> 
> 
> 
> }
> 
> Kindly help me to solve this.
> Thanks in advance
> 
> Best Regards,
> A.Sreedhar.
>  
> 
> 
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Client/Server approach

[Sreedhar.a]

 
Hi,

I am working in sqlite 3.3.6.
I want 5 clients to browse at the same time with good performance.
I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT .
I think by using test_server.c  we can do 5 clients browsing with single
server.
Can any one please help me by providing some patch for implementing this.

My doubts are:
1.Do I need  to create a thread for each client.If so it has to be created
before Sqlite3_server_start().

(Or)
Some thing like below has to be followed.

Main()
{

 sqlite3_client_open()
   sqlite3_client_prepare()
   sqlite3_client_step()
   sqlite3_client_reset()
   sqlite3_client_finalize()
   sqlite3_client_close()

Sqlite3_server_start(); For starting the server.

sqlite3_server_stop();  For closing the server.



}

Kindly help me to solve this.
Thanks in advance

Best Regards,
A.Sreedhar.
 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Unique ids for each record

[Sreedhar.a]

Hi Lokesh,

Thanks for your suggestions.

My proble is assigning a unique record for each record in a table.

The database table name MUSIC.

I have 3 columns Artist Album and Tracks.

Artist1 Album1  Track1
Artist2 Album2  Track2
Artist3 Album2  Track3

I need to assign a unique id value for each item in the row.

For example Artist1 with id = 100, Album1 id = 200 Track1 id = 300
Artist2 with id = 101, Album2 id = 201 Track2 id = 301
Etc.

How I can achieve in assigning a unique id for each and every item.

One more problem:

I want to restrict the Artist range to 100 to 199 so that with the id 
I can make my search fast and also I knew with unique id that I need to
search for the Artist alone.


Thanks & Best Regards,
A.Sreedhar.
 
-Original Message-
From: Babu, Lokesh [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 20, 2007 6:27 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unique ids for each record

refer *In-Memory Database: Delete rows on a Table increases the memory usage
*

More help needed or didn't understand, let me know.


On 8/17/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:
>
> Thanks Marcus
>
>
>
> I am working in 3.3.6 version.
>
> I created a table with id as integer primary key and with constraint id<6.
>
>
>
> For eg,
> "create table Test(id INTEGER PRIMARY KEY CHECK (id > 0 and id < 
> 6),name integer);"
>
> "insert into Test (name) values('name1');"
> "insert into Test (name) values('name2');"
> "insert into Test (name) values('name3');"
> "insert into Test (name) values('name4');"
> "insert into Test (name) values('name5');"
>
> The output for :"select rowid,* from Test;"
> Rowid   id  name
> 1  1  name1
> 2  2  name2
> 3  3  name3
> 4  4  name4
> 5  5  name5
>
> Now I deleted 2 records.
>
> "delete from Test where id=3;"
> "delete from Test where id=4;"
>
> "vacuum Test;"
>
> The output for   :"select rowid,* from Test;"
>
> Rowid   id  name
> 1  1  name1
> 2  2  name2
> 5 5  name5
>
>
>
> My doubt is
>
> . if I tried to insert one more file ,I cant able to insert.error
> like "constraint failed" flashes.Since I deleted 2 records that space 
> is freed only.i tried after doing vacuum also.Is there any other way 
> to insert.
>
>
>
> . The rowid is also not updated after doing vacuum.Is there any
> other way to update.If I tried the same by creating a table with 
> INTEGER alone at that time rowed is updating properly after "Vacuum Test".
>
> Can any one please clarify my doubts.
>
>
>
> Best Regards,
> A.Sreedhar.
>
>
> -Original Message-
> From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 16, 2007 7:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Unique ids for each record
>
> I assume you'd rather want three separate tables (artist, album,
> track) with an autoincrementing ID field per table. Your approach 
> would not allow users to own more than 100 albums.
>
> regards,
> Markus
>
> Quoting "Sreedhar.a" <[EMAIL PROTECTED]>:
>
> > Hi,
> >
> > I have a table with 3 columns.
> >
> > Artist Album and tracks.
> >
> > Can i fix a range of ids for each column like 1-100 for Artist 
> > 101-200 for Album and 201-300 for tracks
> >
> > So that I can have a unique number(id) for each record.
> >
> > Will there be a problem while deleting and inserting the records?
> >
> > Thanks and best regards,
> > A.Sreedhar.
> >
> >
> >
> >
>
>
>
> --
> Markus Hoenicka
> [EMAIL PROTECTED]
> (Spam-protected email: replace the quadrupeds with "mhoenicka") 
> http://www.mhoenicka.de
>
>
>
> --
> --
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> --
> --
> -
>
>
>
>
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
>
> --
> ---
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] To increase search speed

[Sreedhar.a]

Thanks Raghavendra, 

We use sqlite statements for search. 


Best Regards,
A.Sreedhar.
 

-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: Saturday, August 18, 2007 10:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] To increase search speed


use sqlite statements.

regards
ragha

**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
 

*

- Original Message -
From: "Sreedhar.a" <[EMAIL PROTECTED]>
Date: Saturday, August 18, 2007 10:52 am
Subject: [sqlite] To increase search speed

> Hi
> I am working in 3.3.6.
> I have created a table with 4 records with 12 fields as follows.
> 
> "create table MUSIC (Id integer primary key, AlbumName text not null 
> collate nocase,Track text not null collate nocase,ArtistName text not 
> null collatenocase,URL text not null collate nocase , Duration 
> integer, TrackFormat text not null collate nocase, BitRate integer, 
> sampleRate integer, Channels integer, Filesize integer GenreName text 
> not null collate nocase);"
> 
> I will often search for the following fields only.
> 
>Select distinct 
> AlbumName from MUSIC;
>Select  distinct 
> ArtistName from MUSIC;
>Select  distinct 
> GenreName from MUSIC;
>Select  distinct 
> AlbumName for particular ArtistName
>Select  Track for 
> particular AlbumName
>Select  distinct 
> ArtistName for particular GenreName
>  
> To obtain nice search speed which method will work fine.
> I have tried wilth the following methods.
> 
> Method 1:
> 
> It's the one described above
> 
> Method 2:
> 
> By doing indexing.I tried with the following.
> 
>"create  index Musicidx1 on MUSIC(ArtistName  collate 
> nocase,AlbumNamecollate nocase);"   
>"create  index Musicidx2 on MUSIC(AlbumName collate 
> nocase,ArtistNamecollate nocase,URL collate nocase);"
> 
> This gives better performance than method 1 for the following 
> searches;
>
> Selectdistinct  AlbumName for particular ArtistName
>
> SelectTrack for particular AlbumName
>
> Selectdistinct ArtistName for particular GenreName
> 
> Method 3:
> 
> Joining Multiple tables.
> I have created 4 tables as follows;
> 
>"create table  ALBUMS (id integer primary key,Album 
> text,unique(Album));"
>"create table  ARTISTS (id integer primary key,Artist 
> text,unique(Artist));"
>"create table  GENRES (id integer primary key,Genre 
> text,unique(Genre));"
> 
>"create table MUSIC (Id integer primary key, AlbumName text 
> not null collate nocase,Track text not null collate nocase,ArtistName 
> text not null collate nocase,URL text not null collate nocase , 
> Duration integer,TrackFormat text not null collate nocase, BitRate 
> integer, sampleRateinteger, Channels integer, Filesize 
> integer,album_id integer,artist_idinteger, GenreName text not null 
> collate nocase ,Genre_Id integer);"
> 
> Here album_id , artist_id and Genre_Id are the id values of 
> ALBUMS,ARTISTS,GENRES Tables.
> This shows better performance than indexing for the following 
> searches.
> 
> Select distinct AlbumName from MUSIC;
> 
> Select  distinct ArtistName from MUSIC;
> 
> Select  distinct  GenreName from MUSIC; Method 4:
> 
> Inmemory method.I will copy all the content from the temporary 
> database to inmemory and then performing search.
> If I am using this method means then while inserting records , that 
> recordwill be inserted into the temporary memory only.
> But I want to be inserted in to t

[sqlite]Inmemory database

[Sreedhar.a]

Hi,
I am working in 3.3.6
Now I am working with inmemory concept.
Actually I am  copying all the contents of the database into temporary
table.
Then I am inserting one record in that temporary table.
But I need that record to be returned finally in to the original database
file.

Is there any other way to do this.
I did the following.

"ATTACH DATABASE 'test.db' AS Newdb"
"CREATE TABLE INMEMORYTABLE AS SELECT * FROM Newdb.temp"
"DETACH DATABASE Newdb"
insert into INMEMORYTABLE(d,name) values(7,'name7');"
select * from INMEMORYTABLE ;"


Can any one help.I want that inserted record in the temporary table to be in
the original database[ test.db]

Best Regards,
A.Sreedhar.
  

[sqlite] To increase search speed

[Sreedhar.a]

Hi
I am working in 3.3.6.
I have created a table with 4 records with 12 fields as follows.

"create table MUSIC (Id integer primary key, AlbumName text not null collate
nocase,Track text not null collate nocase,ArtistName text not null collate
nocase,URL text not null collate nocase , Duration integer, TrackFormat text
not null collate nocase, BitRate integer, sampleRate integer, Channels
integer, Filesize integer GenreName text not null collate nocase);"

I will often search for the following fields only.

Select distinct
AlbumName from MUSIC;
Select  distinct
ArtistName from MUSIC;
Select  distinct
GenreName from MUSIC;
Select  distinct
AlbumName for particular ArtistName
Select  Track for
particular AlbumName
Select  distinct
ArtistName for particular GenreName
  
To obtain nice search speed which method will work fine.
I have tried wilth the following methods.

Method 1:

It's the one described above

Method 2:

By doing indexing.I tried with the following.

"create  index Musicidx1 on MUSIC(ArtistName  collate nocase,AlbumName
collate nocase);"   
"create  index Musicidx2 on MUSIC(AlbumName collate nocase,ArtistName
collate nocase,URL collate nocase);"

This gives better performance than method 1 for the following searches;

Select
distinct  AlbumName for particular ArtistName
Select
Track for particular AlbumName
Select
distinct ArtistName for particular GenreName

Method 3:

Joining Multiple tables.
I have created 4 tables as follows;

"create table  ALBUMS (id integer primary key,Album
text,unique(Album));"
"create table  ARTISTS (id integer primary key,Artist
text,unique(Artist));"
"create table  GENRES (id integer primary key,Genre
text,unique(Genre));"

"create table MUSIC (Id integer primary key, AlbumName text not
null collate nocase,Track text not null collate nocase,ArtistName text not
null collate nocase,URL text not null collate nocase , Duration integer,
TrackFormat text not null collate nocase, BitRate integer, sampleRate
integer, Channels integer, Filesize integer,album_id integer,artist_id
integer, GenreName text not null collate nocase ,Genre_Id integer);"

Here album_id , artist_id and Genre_Id are the id values of
ALBUMS,ARTISTS,GENRES Tables.
This shows better performance than indexing for the following searches.

 
Select distinct AlbumName from MUSIC;
 
Select  distinct ArtistName from MUSIC;
 
Select  distinct  GenreName from MUSIC;
Method 4:

Inmemory method.I will copy all the content from the temporary database to
inmemory and then performing search.
If I am using this method means then while inserting records , that record
will be inserted into the temporary memory only.
But I want to be inserted in to the original database also. Is there any
other way to do this.

Can any one help to increase my search speed.
Thanks in Advance.
 
Regards,
Sreedhar



















  

RE: [sqlite] Unique ids for each record

[Sreedhar.a]

Thanks Marcus



I am working in 3.3.6 version.

I created a table with id as integer primary key and with constraint id<6.

 

For eg,
"create table Test(id INTEGER PRIMARY KEY CHECK (id > 0 and id < 6),name
integer);"

"insert into Test (name) values('name1');"
"insert into Test (name) values('name2');"
"insert into Test (name) values('name3');"
"insert into Test (name) values('name4');"
"insert into Test (name) values('name5');"

The output for :"select rowid,* from Test;"
Rowid   id  name
1  1  name1
2  2  name2
3  3  name3
4  4  name4
5  5  name5

Now I deleted 2 records.

"delete from Test where id=3;"
"delete from Test where id=4;"

 "vacuum Test;"

The output for   :"select rowid,* from Test;"

 Rowid   id  name
1  1  name1
2  2  name2
5 5  name5

 

My doubt is

. if I tried to insert one more file ,I cant able to insert.error
like "constraint failed" flashes.Since I deleted 2 records that space is
freed only.i tried after doing vacuum also.Is there any other way to insert.

 

. The rowid is also not updated after doing vacuum.Is there any
other way to update.If I tried the same by creating a table with INTEGER
alone at that time rowed is updating properly after "Vacuum Test".

Can any one please clarify my doubts.



Best Regards,
A.Sreedhar.


-Original Message-
From: Markus Hoenicka [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 16, 2007 7:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unique ids for each record

I assume you'd rather want three separate tables (artist, album,
track) with an autoincrementing ID field per table. Your approach would not
allow users to own more than 100 albums.

regards,
Markus

Quoting "Sreedhar.a" <[EMAIL PROTECTED]>:

> Hi,
>
> I have a table with 3 columns.
>
> Artist Album and tracks.
>
> Can i fix a range of ids for each column like 1-100 for Artist 101-200 
> for Album and 201-300 for tracks
>
> So that I can have a unique number(id) for each record.
>
> Will there be a problem while deleting and inserting the records?
>
> Thanks and best regards,
> A.Sreedhar.
>
>
>
>



--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Aggregate and query limit

[Sreedhar.a]

Hi,

One idea,Please check whether each row has 11 elements.
That's could be the reason why u have got 33 times call back.


Best Regards,
A.Sreedhar.
 

-Original Message-
From: Mina R Waheeb [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 17, 2007 12:12 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Aggregate and query limit

Hi,
  I have some questions about the behavior of aggregate functions and the
result LIMIT.
After register TEST function and executing query (SELECT TEST() FROM objects
group by id LIMIT 30,3)

I was expect calling back TEST() only 3 times but what happened is the
TEST() is called 33 time, and the result set is correct 3 rows.

My questions:
- Why TEST() is called for non-result rows? Is this designed  feature?
- When the evaluation of the result-column functions happen?
- How SQLite preform the LIMIT?

Thanks,
Mina.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Auto Increment of Integer Primary Key

[Sreedhar.a]

Hi,
 
I am working with sqlite 3.3.6 version.
I have defined the macro SQLITE_OMIT_FLOATING_POINT.
 
1.
this is my test program
 
"create table Test(id integer primary key,player char);"
"insert into Test(id,player) values(2,'surya');"
"insert into Test(id,player) values(9223372036854775807,'sree');"
"insert into Test(player) values('sree1');"
 
A random number is choosen for the player sree1.
 
What i need is i want to fix the maximum limit to say 64k.
So that it will be easy for me for maintaining the records. is there any
possibility of setting the maximum limit.
 
I tried changing the value of  SQLITE_BIG_DBL  it did not worked.
 
2.
 
My second doubt.
 
"create table Test(id integer primary key,player char);"
"insert into Test(id,player) values(2,'surya');"
"insert into Test(id,player) values(9223372036854775807,'sree');"
"insert into Test(id,player) values(9223372036854775808,'sree1');"
"select * from Test;"
 
The result is 
 
-9223372036854775808sree1
2surya
9223372036854775807sree
 
I tried inserting 2 power 63 value but the database has converted it to -2
power 63 and stored.
Can anyone explain why this has happened.
 
 
 
 
Best Regards,
A.Sreedhar.
 
Jasmin Infotech Pvt. Ltd.
Plot 119, Velachery Tambaram Road,
(Opposite NIOT), Pallikaranai,
Chennai 601 302
India
Tel: +91 44 3061 9600 ext 3057
Fax: + 91 44 3061 9605 
 

***
Information in this email is proprietary and Confidential to 
Jasmin Infotech. Any use, copying or dissemination of the
information in any manner is strictly prohibited. If you are 
not the intended recipient, please destroy the message and please inform us.