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

[Dennis Cote]

On Nov 29, 2007 12:03 PM, Wilson, Ron <[EMAIL PROTECTED]> wrote:

>
> http://www.sqlite.org/quickstart.html
>
> This page still shows the old callback method for usign sqlite3_exec().
> I actually haven't used SQlite in quite a long time, but judging from
> recent list topics, this is no longer the preferred method.  So where do
> I point my friend for using sqlite3_prepare_v2() etc.?  I've seen a few
> posts (long since deleted from my inbox) that had basic outlines.  Could
> someone please post a basic code snipit for open/query/close using the
> newer interface, i.e. avoiding the callback usage?  Perhaps the
> quickstart guide (above) could use an update as well?
>
>  Ron,
I believe the following code is equivalent to the existing quickstart code,
but it uses the newer APIs. This simple program will execute arbitrary SQL
on a database file. If the SQL is a query it will display all the results
returned.

#include 

#include 


int main(int argc, const char *argv[]){

  sqlite3 *db;

  sqlite3_stmt *stmt;

  int rc = 0;

  int col, cols;


  if( argc!=3 ){

fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);

  }else{

// open database

rc = sqlite3_open(argv[1], );

if( rc ){

  fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

}else{

  // prepare statement

  rc = sqlite3_prepare_v2(db, argv[2], -1, , 0);

  if( rc ){

fprintf(stderr, "SQL error: %d : %s\n", rc, sqlite3_errmsg(db));

  }else{

cols = sqlite3_column_count(stmt);

// execute the statement

do{

  rc = sqlite3_step(stmt);

  switch( rc ){

case SQLITE_DONE:

  break;

case SQLITE_ROW:

  // print results for this row

  for( col=0; col

[sqlite] Re[4]: rollback fails incomprehencibly

[??????? ????????]

But I use v2 interface. In particular I called
sqlite3_prepare_v2, not sqlite3_prepare(). So, something
is still wrong?

(remind that I use sqlite 3.5.1 (windows))


 >> Ok, I tried to do reset for my statement
 >> and ... got SQLITE_BUSY too!
 >> I.e. sqlite_reset() (and sqlite_finalize() too) returns SQLITE_BUSY,
 >> althought statement seems to become reset and rollback complete
 >> at last.

>  When sqlite3_step fails, it always returns a generic SQLITE_ERROR
code.
>  A subsequent call to sqlite3_reset or sqlite3_finalize returns the
>  specific error code that sqlite3_step should have returned. The
_reset
>  or _finalize itself succeeds, despite returning an error.

>  It's not clear why it was done this way originally, but now it is
>  maintained for backward compatibility. If you use sqlite3_prepare_v2
>  instead of sqlite3_prepare to prepare your statements, you opt out of
>  this legacy behavior: sqlite3_step would now return an appropriate
error
>  code directly, and sqlite3_reset and _finalize would only return
error
>  codes if they genuinely fail.

>  Igor Tandetnik

Re: [sqlite] Segmentation fault in syncJournal (pPager=0x102028) at src/pager.c:2417

[Joseph Hsu]

Sorry, I use SQLite 3.4.2, NOT SQLite 3.5.2.
The code in pager.c:2417 (SQLite 3.4.2) is

for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
 pPg->needSync = 0;// <= line 2417
}
Is there something wrong to pPg ? why ??

And in general, pPager=0x102028 when segmentation fault was occured.
(pPager is the parameter of syncJournal (pPager=0x102028)  )

Thanks to Richard to remind me the wrong SQLite version number.

Sincerely yours,

Joseph

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, November 29, 2007 10:48 PM
Subject: Re: [sqlite] Segmentation fault in syncJournal (pPager=0x102028) at 
src/pager.c:2417



"Joseph Hsu" <[EMAIL PROTECTED]> wrote:

Hello,
I use SQLite 3.5.2 on ARM9 embedded system. ( Linux 2.4 )
My program sometimes meets segmentation fault ! ( randomly, maybe several 
hours, maybe several days ! )

Here is a clip from GDB:
-- snip --
(gdb) backtrace
#0  syncJournal (pPager=0x102028) at src/pager.c:2417


In SQLite 3.5.2, syncJournal begins on line 2748.  Are you
really user your are running version 3.5.2?  Have you
modified the sources?


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


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


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

Re: [sqlite] version 3.3.12 -- malformed schema after using "limit 0" ??

[drh]

"Griggs, Donald" <[EMAIL PROTECTED]> wrote:
> I don't know if the following might be a problem with the new release,
> or just something odd on my system.
> 
> 
> In sqlite 3.3.12, I try to create an empty table copying the schema of
> an existing table by using a command such as the following:
> CREATE TABLE  newtable AS SELECT * FROM oldtable LIMIT 0;
> I seem to create an integrity_check failure (and the "newtable" is not
> created, btw).
> 
> If I then close the database and re-open it, the "malformed database
> schema" error is returned for most any operation, and no table within
> the database can be dumped.
> 

Problem fixed by check-in [3993] on 2007-05-14 14:05 GMT.

   http://www.sqlite.org/cvstrac/chngview?cn=3993

This was in between 3.3.17 and 3.4.0.  In other words, the fix
first appears in version 3.4.0.

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


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

RE: [sqlite] version 3.3.12 -- malformed schema after using "limit 0" ??

[James Dennett]

[EMAIL PROTECTED] wrote:

> 
> I don't know if the following might be a problem with the new release,
> or just something odd on my system.
> 
> 
> In sqlite 3.3.12, I try to create an empty table copying the schema of
> an existing table by using a command such as the following:
> CREATE TABLE  newtable AS SELECT * FROM oldtable LIMIT 0;
> I seem to create an integrity_check failure (and the "newtable" is not
> created, btw).
> 
> If I then close the database and re-open it, the "malformed database
> schema" error is returned for most any operation, and no table within
> the database can be dumped.
> 
> 
> I can produce the error with the pre-compiled windows binary
sqlite3.exe
> (I'm running winXP SP2).
> 
> When I tried an old sqlite 3.4.2 that I had lying around, it *did*
> create "newtable" and there was *no* integrity_check failure.   Same
> with version 2.8.13 (sqlite.exe).
> 
> Is this an easily-repeatible issue, or do I have some corrupted files
> somewhere?

With your recipe, I can reproduce it on 3.3.10 on Solaris 9/SPARC.
Clearly an upgrade is in order where practical.

-- James


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

[sqlite] version 3.3.12 -- malformed schema after using "limit 0" ??

[Griggs, Donald]

I don't know if the following might be a problem with the new release,
or just something odd on my system.


In sqlite 3.3.12, I try to create an empty table copying the schema of
an existing table by using a command such as the following:
CREATE TABLE  newtable AS SELECT * FROM oldtable LIMIT 0;
I seem to create an integrity_check failure (and the "newtable" is not
created, btw).

If I then close the database and re-open it, the "malformed database
schema" error is returned for most any operation, and no table within
the database can be dumped.


I can produce the error with the pre-compiled windows binary sqlite3.exe
(I'm running winXP SP2).

When I tried an old sqlite 3.4.2 that I had lying around, it *did*
create "newtable" and there was *no* integrity_check failure.   Same
with version 2.8.13 (sqlite.exe).

Is this an easily-repeatible issue, or do I have some corrupted files
somewhere?

Obviously, try this *only* on a new or expendable database!

 QUICK TEST PROCEDURE:
sqlite3  databasename

create table cat ( a, b );
insert into cat values (1, 2);
create table badboy as select * from cat limit 0;
pragma integrity_check;
.quit

See if you get an integrity failure.  Then open the same database and
operate on the database, e.g.:

sqlite3 databasename

select * from cat;

See if you get "SQL error: malformed database schema"

* LOG OF TRYING ABOVE ON MY SYSTEM:

C:\Data\zip5>sqlite3 newdb.db
SQLite version 3.3.12
Enter ".help" for instructions
sqlite> create table cat ( a, b);
sqlite> insert into cat values (1, 2);
sqlite> create table badboy as select * from cat limit 0;
sqlite> pragma integrity_check;
*** in database main ***
Page 3 is never used
sqlite> select * from cat;
1|2
sqlite> .quit

C:\Data\zip5>sqlite3 newdb.db
SQLite version 3.3.12
Enter ".help" for instructions
sqlite> select * from cat;
SQL error: malformed database schema
sqlite>

(Possibly related to Check-in 3993??)


 

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

Re: [sqlite] What is the preferred way to create SQLite databases?

[Chris Peachment]

At the command line prompt:

sqlite3 name_of_database_to_be_created

SQLite version 3.5.2
Enter ".help" for instructions

sqlite>.read database_definition_text_file_name

sqlite>.quit


On Thu, 2007-11-29 at 15:43 -0600, Mark Brown wrote:
> Hi-
> 
> What is the preferred way to create SQLite databases?  Ideally, I would like
> to have the entire schema in a file, and then generate the DB from that, as
> we make changes to the schema often.
> 
> We're currently using something called SQLite Database Browser, but I am
> becoming concerned that the version of SQLite it is using is not the same as
> the version of SQLite we are using in our application.
> 
> My developers all have Windows machines, but our application that uses
> SQLite runs on vxWorks.
> 
> Thanks,
> Mark
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

Re: [sqlite] commit and rollback

[John Stanton]

You can use the SQLITE_BUSY returned by sqlite3_step to synchronize. 
Just pause and resubmit the call when you get it.


arbalest06 wrote:

about this synchronization of multiple writers, can you please explain on how
to make this possible? or your just saying that i need to make a daemon that
will eventually synchronize the writers?



John Stanton-3 wrote:

Multiple writers merely have to be synchronized.

arbalest06 wrote:

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


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





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








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

[sqlite] What is the preferred way to create SQLite databases?

[Mark Brown]

Hi-

What is the preferred way to create SQLite databases?  Ideally, I would like
to have the entire schema in a file, and then generate the DB from that, as
we make changes to the schema often.

We're currently using something called SQLite Database Browser, but I am
becoming concerned that the version of SQLite it is using is not the same as
the version of SQLite we are using in our application.

My developers all have Windows machines, but our application that uses
SQLite runs on vxWorks.

Thanks,
Mark



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

Re: [sqlite] SQLite3 Issue

[drh]

David Gelt <[EMAIL PROTECTED]> wrote:
>   
>   db_reset_stmt(dbo, stmt);
> * SEGMENTATION FAULT AT THE 
> NEXT LINE *
> return_code = db_fetch_first(dbo, stmt);  /* initial call to retrieve 

sqlite3_column_xxx() may only be called after
a prior call to sqlite3_step() which returned
SQLITE_ROW and not after sqlite3_reset() or
sqlite3_finalize().  After you do your
sqlite3_reset() you need to call sqlite3_step()
again (and make sure it returns SQLITE_ROW) 
before you can see row data.

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


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

Re: [sqlite] Lemon: Conflicts with repeated TERMINALS

[Ralf Junker]

Joe Wilson <[EMAIL PROTECTED]> wrote:

>The following grammar may be clearer to you:

Yes, it is many thanks! I believe I am making progress! At least I can see the 
picture much clearer now and was able to come up with the following grammar 
with just one conflict unsolved:

  %left NEWLINE.   /* Do these matter here at all? */
  %nonassoc TEXT LINK.
  %left HEADING_START.
  %left HEADING_END.

  article ::= blocks.

  blocks ::= block. /* EOF */
  blocks ::= blocks NEWLINE./* EOF */
  blocks ::= blocks NEWLINE NEWLINE block.

  block ::= .   /* EOF */
  block ::= paragraph.
  block ::= heading.

  heading ::= HEADING_START text HEADING_END.

  paragraph ::= line.
  paragraph ::= paragraph NEWLINE line.

  line ::= text.

  text ::= textpiece.
  text ::= text textpiece.

  textpiece ::= TEXT.
  textpiece ::= LINK.

I of course appreciate any comments ;-) My idea is that

* A block can be either a paragraph or a heading. Multiple blocks are separated 
by two NEWLINEs.

* A paragraph is made up of n >= 1 lines. Each line within a paragraph ends 
with a single NEWLINE. Two NEWLINEs start a new block (see above).

* A line consists of text, which can be TEXT or LINK.

Not all works well with the grammer, and unfortunately I do not understand why. 
Given this input, for example:

  TEXT, NEWLINE

the parser gets stuck at

  paragraph ::= paragraph NEWLINE line.

instead of falling back to the line above
  
  paragraph ::= line.

to find the conditions of a paragraph fulfilled. Why does it not try the other 
alternatives? Or are there none in the grammar?

>Try reading some papers on parsing or search for the book
>"Compilers: Principles, Techniques, and Tools" (a.k.a. 
>the dragon book).

I certainly will.

>Also try writing on paper random sequences of tokens and 
>manually parse your grammar to see the conflicts firsthand.

As I throw different token sequences to my experimental parser I am slowly 
starting to make sense of the debugger output.

Ralf 


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

Re: [sqlite] quickstart

[Chris Peachment]

Here is a small code sample.

Open the database and then:

  Result = sqlite3_prepare_v2(hdb,
   "select Name, FilePrefix from Application", 100, , 0);
  if (Result != SQLITE_OK) {
fprintf(stderr,
  "cannot select from Application table. Error Code: %d\n", Result);
exit(1);
  }

  do {
Result = sqlite3_step(hstmt);
if (Result == SQLITE_ROW) {

... handle row data using the sqlite3_column_xxx functions

}
  } while (Result == SQLITE_ROW);

  if (Result == SQLITE_DONE) {
Result = SQLITE_OK;
  }
  else {
fprintf(stderr,"Error Code: %d\n", Result);
  }
  sqlite3_finalize(hstmt);
  sqlite3_close(hdb);


On Thu, 2007-11-29 at 14:03 -0500, Wilson, Ron wrote:
> So a friend of mine was asking me how to get started using SQlite, so I
> pointed him here:
> 
> http://www.sqlite.org/quickstart.html 
> 
> This page still shows the old callback method for usign sqlite3_exec().
> I actually haven't used SQlite in quite a long time, but judging from
> recent list topics, this is no longer the preferred method.  So where do
> I point my friend for using sqlite3_prepare_v2() etc.?  I've seen a few
> posts (long since deleted from my inbox) that had basic outlines.  Could
> someone please post a basic code snipit for open/query/close using the
> newer interface, i.e. avoiding the callback usage?  Perhaps the
> quickstart guide (above) could use an update as well?
> 
> RW
> 
> Ron Wilson, Senior Engineer, MPR Associates, 518.831.7546
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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

[sqlite] SQLite3 Issue

[David Gelt]

Hi there,
   
  I have started using recently SQLite which is pretty great for what I need, 
(i.e. a scientific application). After going through PostgreSQL, CDB, Tiny CDB 
and Berkeley DB  it feels like coming home when I am looking at the size, 
speed, number of features and license. :) 
   
  Anyway I have got a bit of an issue retrieving data from database. I have the 
code below which works fine until the second fetch_first. It keeps crashing 
with segmentation fault error. Is there something else that I should do before 
that, like preparing the stmt again (in documentation it says that is not 
needed) or clear_bindings (I tried that but I got an error in clear_bindings 
now and I am thinking that shouldn't really be needed). As a new user I can't 
seem to find too many C code examples but other than that everything is just 
great. 
   
  Is there any difference between execute() & callback() and step() in term of 
speed?
   
  Below is the source code.
   
  Any help would be greatly appreciated.
   
  Thanks,
  David
   
   db_type *dbo;
 db_sql_stmt *stmt;
 const char *object_id;
 int return_code;
 char *sql;
 
 db_open_read_only("objects.db", dbo, );
 
 sql = "SELECT * FROM Objects ORDER BY ID ASC;";
 db_prepare_stmt(dbo, sql, );
 
 return_code = db_fetch_first(dbo, stmt); /* initial call to retrieve first row 
of data */
 while(return_code == SQLITE_ROW) 
 {
 object_id = (const char *)db_get_column_string(stmt, 0);
  (printf object_id - the objects are printed fine, one by one)
  return_code = db_fetch_next(dbo, stmt);  /* subsequent calls retrieve the 
next rows */
 }
   (return_code is now SQLITE_DONE)
   while(criteria) 
 {
  (I need to reuse the same SQL statement a couple of times.)
  
  db_reset_stmt(dbo, stmt);
* SEGMENTATION FAULT AT THE 
NEXT LINE *
return_code = db_fetch_first(dbo, stmt);  /* initial call to retrieve first 
row of data */
  while(return_code == SQLITE_ROW) 
  {
   object_id = (const char *)db_get_column_string(stmt, 0);
   (printf object_id )
   return_code = db_fetch_next(dbo, stmt);  /* subsequent calls retrieve the 
next rows */
  }
 }
 db_finalize_stmt(dbo, stmt);   /* free statement memory */
   db_close(dbo);
   

void db_open_read_only(const char *file_name, db_type *db, db_type **pdb)
{
 int return_code;
 
return_code = sqlite3_open_v2(file_name, pdb, SQLITE_OPEN_READONLY, NULL);
if(return_code != SQLITE_OK)
 {
  /* get the message first and than free the memory before displaying the 
message */ 
  db_err_msg = sqlite3_errmsg(db);
  db_close(db); 
  
  error_message(ERR_FATAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg, 
NULL);
}
}
  void db_close(db_type *db)
{
 /* free database pointer memory */
 int return_code;
 
 return_code = sqlite3_close(db);
if(return_code != SQLITE_OK)
  error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, "Unable to 
close database file.", db_err_msg);
}
  void db_prepare_stmt(db_type *db, const char *sql, db_sql_stmt **pstmt)
{
 int return_code;
 
return_code = sqlite3_prepare_v2(db, sql, strlen(sql), pstmt, NULL);
 if (return_code != SQLITE_OK)
 {
  /* get the message and free memory before displaying the message */ 
  db_err_msg = sqlite3_errmsg(db);
  db_close(db);
  
  error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg, 
NULL);
 }
}
  void db_reset_stmt(db_type *db, db_sql_stmt *stmt)
{
 int return_code;
 
return_code = sqlite3_reset(stmt);
 if (return_code != SQLITE_OK)
 {
  /* get the message and free memory before displaying the message */ 
  db_err_msg = sqlite3_errmsg(db);
  db_close(db);
  
  error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg, 
NULL);
 }
}
  void db_finalize_stmt(db_type *db, db_sql_stmt *stmt)
{
 int return_code;
 
return_code = sqlite3_finalize(stmt);
 if (return_code != SQLITE_OK)
 {
  /* get the message and free memory before displaying the message */ 
  db_err_msg = sqlite3_errmsg(db);
  db_close(db);
  
  error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg, 
NULL);
 }
}
  int db_fetch_first(db_type *db, db_sql_stmt *stmt)
{
 int return_code;
 int retry_count = 0; /* counter that indicates how many times we have retried 
the same operation */
 
return_code = sqlite3_step(stmt);
 
 /* check if the returned code indicates low processing resources and if true 
retry the operation */ 
 while((return_code == SQLITE_BUSY) && (retry_count < 3))
 {
  sqlite3_sleep(500);  /* 500 milliseconds or rounded up to 1 second if not 
supported */
  retry_count++;
  
  return_code = sqlite3_step(stmt);
 }
 
 return return_code;
}
  int db_fetch_next(db_type *db, db_sql_stmt *stmt)
{
 int return_code;
 int retry_count = 0; /* counter that indicates how many times we have retried 
the same operation */
 
return_code = 

Re: [sqlite] Performance tuning using PRAGMA, other methods

[Joanne Pham]

Hi Scott,
Thanks for the information related to Performance tunning using PRAGMA.
My embeded app needs to be tunning too.
I have serveral questions for you.
Current I had these PRAGMAS in my app:

 sqlSt = sqlite3_exec(pDb, "PRAGMA page_size=32768", NULL, 0, ); 
  sqlSt = sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, ); 
  sqlSt = sqlite3_exec(pDb, "PRAGMA temp_store = MEMORY ", NULL, 0, ); 
  sqlSt = sqlite3_exec(pDb, "PRAGMA cache_size = 2000 ", NULL, 0, ); 
 Questions:
1) Do I need this : sqlSt = sqlite3_exec(pDb, "PRAGMA page_size=32768", NULL, 
0, ); 

2) sqlSt = sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, ); 
is this the same as your :  rc = sqlite3_exec(*dbHandle, "PRAGMA 
synchronous = 0;", 0, 0, 0);

3) sqlSt = sqlite3_exec(pDb, "PRAGMA temp_store = MEMORY ", NULL, 0, ); 
is this the same as your :  rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store 
= 2;", 0, 0, 0);

4) Do I need this : sqlSt = sqlite3_exec(pDb, "PRAGMA cache_size = 2000 ", 
NULL, 0, ); 

5)  Also Do I need these two setting as you mentioned in your previous email
rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0,0);
   rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0,0, 0);

Thank you so much.
JP

- Original Message 
From: Scott Krig <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 29, 2007 10:56:37 AM
Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods

BTW, several PRAGMAS actually increase performance in my embedded app
case - maybe 15-30% depending upon transaction activity and the way I
structure transaction commits. Specific PRAGMAS that helped include:




//
// Synchronous OFF (0)
//
rc = sqlite3_exec(*dbHandle, "PRAGMA synchronous = 0;", 0, 0, 0);


//
// Keep temporary storage in MEMORY (2) instead of a file
//
rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store = 2;", 0, 0, 0);


//
// Allow reads from uncommitted memory containing DB tables/records
//
rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0,
0);


//
// Exclusive access to DB to avoid lock/unlock for each transaction
//
rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0,
0, 0);






-Original Message-
From: Scott Krig [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 20, 2007 2:03 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods

There are apparently no folks with the experience to answer the
questions as given?


-Original Message-
From: Tom Briggs [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 20, 2007 1:41 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods


  re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think
that your question is too broad to be answerable (unless you're actually
attempting to assemble a collection of optimal values in all possible
situations for all existing pragmas... ;shrug)

  re: Q2 - At the risk of sounding crass, tuning queries is, has been
and always will be the best way to optimize the performance of any
database.  I've done a lot of tuning of SQLite and a half dozen other
databases, and query design is always what has the most impact.
Pragmas, #defines, API usage, etc. are always a distant second in the
race for performance gains.

  -T

> -Original Message-
> From: Scott Krig [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 20, 2007 4:24 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods
> 
> To the point, the questions are:
> 
> 
> Q1)) PRAGMA: Does anyone have experience and good results optimizing
> sqlite performance using PRAGMA's? If so, which ones, how were they
> used, and what was the performance increase?
> 
> Q2)) Other techniques: Any success stories on sqlite optimization
> methods of any type would be appreciated.
> 
> 
> 
>  
> 
> -Original Message-
> From: Tom Briggs [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 20, 2007 10:40 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods
> 
> 
>Which pragmas will be most effective (and what values you 
> should use
> for each) depends on what you're trying to do with the database.
> Synchronous is important if you're writing frequently, for 
> example, but
> won't matter much in a read-only setting.  Appropriate values for the
> page_size and cache_size pragmas vary depending on whether 
> the database
> is write-mostly or read-mostly and also depending on whether 
> you want to
> optimize for reading or writing.
> 
>So in short, the answer is, it depends.  Depends on what you're
> trying to tune for, that is.
> 
>-T
> 
> > -Original Message-
> > From: Scott Krig [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, November 20, 2007 1:13 PM
> > To: sqlite-users@sqlite.org

[sqlite] quickstart

[Wilson, Ron]

So a friend of mine was asking me how to get started using SQlite, so I
pointed him here:

http://www.sqlite.org/quickstart.html 

This page still shows the old callback method for usign sqlite3_exec().
I actually haven't used SQlite in quite a long time, but judging from
recent list topics, this is no longer the preferred method.  So where do
I point my friend for using sqlite3_prepare_v2() etc.?  I've seen a few
posts (long since deleted from my inbox) that had basic outlines.  Could
someone please post a basic code snipit for open/query/close using the
newer interface, i.e. avoiding the callback usage?  Perhaps the
quickstart guide (above) could use an update as well?

RW

Ron Wilson, Senior Engineer, MPR Associates, 518.831.7546

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

RE: [sqlite] Performance tuning using PRAGMA, other methods

[Scott Krig]

BTW, several PRAGMAS actually increase performance in my embedded app
case - maybe 15-30% depending upon transaction activity and the way I
structure transaction commits. Specific PRAGMAS that helped include:




//
// Synchronous OFF (0)
//
rc = sqlite3_exec(*dbHandle, "PRAGMA synchronous = 0;", 0, 0, 0);


//
// Keep temporary storage in MEMORY (2) instead of a file
//
rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store = 2;", 0, 0, 0);


//
// Allow reads from uncommitted memory containing DB tables/records
//
rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0,
0);


//
// Exclusive access to DB to avoid lock/unlock for each transaction
//
rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0,
0, 0);




 

-Original Message-
From: Scott Krig [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 20, 2007 2:03 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods

There are apparently no folks with the experience to answer the
questions as given?
 

-Original Message-
From: Tom Briggs [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 20, 2007 1:41 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods


   re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think
that your question is too broad to be answerable (unless you're actually
attempting to assemble a collection of optimal values in all possible
situations for all existing pragmas... ;shrug)

   re: Q2 - At the risk of sounding crass, tuning queries is, has been
and always will be the best way to optimize the performance of any
database.  I've done a lot of tuning of SQLite and a half dozen other
databases, and query design is always what has the most impact.
Pragmas, #defines, API usage, etc. are always a distant second in the
race for performance gains.

   -T

> -Original Message-
> From: Scott Krig [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 20, 2007 4:24 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods
> 
> To the point, the questions are:
> 
> 
> Q1)) PRAGMA: Does anyone have experience and good results optimizing
> sqlite performance using PRAGMA's? If so, which ones, how were they
> used, and what was the performance increase?
> 
> Q2)) Other techniques: Any success stories on sqlite optimization
> methods of any type would be appreciated.
> 
> 
> 
>  
> 
> -Original Message-
> From: Tom Briggs [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 20, 2007 10:40 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods
> 
> 
>Which pragmas will be most effective (and what values you 
> should use
> for each) depends on what you're trying to do with the database.
> Synchronous is important if you're writing frequently, for 
> example, but
> won't matter much in a read-only setting.  Appropriate values for the
> page_size and cache_size pragmas vary depending on whether 
> the database
> is write-mostly or read-mostly and also depending on whether 
> you want to
> optimize for reading or writing.
> 
>So in short, the answer is, it depends.  Depends on what you're
> trying to tune for, that is.
> 
>-T
> 
> > -Original Message-
> > From: Scott Krig [mailto:[EMAIL PROTECTED] 
> > Sent: Tuesday, November 20, 2007 1:13 PM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] Performance tuning using PRAGMA, other methods
> > 
> >  
> > What are the 'biggest bang for the buck' sqlite optimization 
> > techniques
> > to apply to a working system to tune performance?
> > 
> > Q1)) PRAGMA: Does anyone have experience and good results optimizing
> > sqlite performance using PRAGMA's? If so, which ones, how were they
> > used, and what was the performance increase?
> > Q2)) Other techniques: Any success stories on sqlite optimization
> > methods of any type would be appreciated.
> >  
> > Thanks.
> >  
> >  
> > Scott
> > -=-
> >  
> >  
> >  
> > Here is a list of the PRAGMA examples from the sqlite documentation:
> >  
> >  
> >  
> > PRAGMA auto_vacuum;
> > PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental;
> >  
> > PRAGMA cache_size; 
> > PRAGMA cache_size = Number-of-pages;
> >  
> > PRAGMA case_sensitive_like; 
> > PRAGMA case_sensitive_like = 0 | 1;
> >  
> > PRAGMA count_changes; 
> > PRAGMA count_changes = 0 | 1;
> >  
> > PRAGMA default_cache_size; 
> > PRAGMA default_cache_size = Number-of-pages;
> >  
> > PRAGMA default_synchronous;
> >  
> > PRAGMA empty_result_callbacks; 
> > PRAGMA empty_result_callbacks = 0 | 1;
> >  
> > PRAGMA encoding; 
> > PRAGMA encoding = "UTF-8"; 
> > PRAGMA encoding = "UTF-16"; 
> > PRAGMA encoding = "UTF-16le"; 
> > PRAGMA encoding = "UTF-16be";
> >  
> > PRAGMA full_column_names; 
> > PRAGMA full_column_names = 0 | 1;
> >  
> > PRAGMA fullfsync 

RE: [sqlite] Re: Different between BEGIN, END and "begin transaction", "

[Fred Williams]

Actually the first looks like Pascal and the second COBOL  (Well, it
should be all upper case)...

> -Original Message-
> From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
> Sent: Thursday, November 29, 2007 12:47 PM
> To: SQLite
> Subject: [sqlite] Re: Different between BEGIN, END and "begin
> transaction", "
>
>
> Joanne Pham
>  wrote:
> > What is the different between
> >  BEGIN
> >  insert .
> >  END
> >
> > and
> >begin transaction
> >insert ...
> >end transaction
>
> None whatsoever.
>
> Igor Tandetnik
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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

[sqlite] Re: Re: Different between BEGIN, END and "begin transaction", "

[Igor Tandetnik]

Joanne Pham  wrote:

Sorry. I meant to say
What is the different between
BEGIN
  insert .
   END

and
  begin transaction
insert ...
   commit transaction
commit transaction NOT end transaction


Still no difference. COMMIT and END are synonyms.

Igor Tandetnik

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

Re: [sqlite] Re: Different between BEGIN, END and "begin transaction", "

[Joanne Pham]

Thanks Igor
Sorry. I meant to say
What is the different between
 BEGIN
   insert .
END

 and
   begin transaction
 insert ...
commit transaction
commit transaction NOT end transaction
Thanks
JP

- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: SQLite 
Sent: Thursday, November 29, 2007 10:46:36 AM
Subject: [sqlite] Re: Different between BEGIN, END and "begin transaction", "

Joanne Pham  wrote:
> What is the different between
>  BEGIN
>  insert .
>  END
> 
> and
>begin transaction
>insert ...
>end transaction

None whatsoever.

Igor Tandetnik

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


  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

[sqlite] Re: Different between BEGIN, END and "begin transaction", "

[Igor Tandetnik]

Joanne Pham  wrote:

What is the different between
 BEGIN
 insert .
 END

and
   begin transaction
   insert ...
   end transaction


None whatsoever.

Igor Tandetnik

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

[sqlite] Different between BEGIN, END and "begin transaction", "

[Joanne Pham]

Hi All,
What is the different between
  BEGIN
  insert .
  END

and 
begin transaction
insert ...
end transaction

Are they the same?
Thanks,
JP


  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

Re: [sqlite] ftruncate() for values greater than file size

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> > On Nov 29, 2007, at 9:19 PM, Mark Brown wrote:
> > > I have finally tracked down a bug that has plagued by vxWorks  
> > > port.  It
> > > appears that our file system's implementation of ftruncate() does  
> > > not like
> > > to "truncate" a file larger than its current size, and returns  
> > > EINVAL for
> > > this operation.
> 
> This is interesting - ftruncate's behavior is not defined by POSIX
> in this case.  SQLite shouldn't rely on the commonly implemented 
> zero-fill extension.
> 

It doesn't.  In fact we didn't realize the ftruncate() was
being called with values larger than the current file size.

I have been looking into the problem.  It apparently only
occurs when you have autovacuum enabled.

It looks like attempt to ftruncate() to a larger size can
simply be ignored and everything continues to work.  We will
make sure that ftruncate() does not get called with a size
larger than the current file size in future releaes.

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


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

Re: [sqlite] Doubtful code in os_unix.c

[drh]

"Alexandre Balaban" <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> did someone recently tested the unix code with the locking
> style enabled ?

No.  Locking styles are not recommended right now.  Watch
for updates to that code in a future release.

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


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

RE: [sqlite] RE: Doubtful code in os_unix.c

[Mark Brown]

My fix is to REMOVE the lines of code I mentioned.  The malloc is done
outside of this method already.

Thanks,
Mark


> -Original Message-
> From: Alexandre Balaban [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 29, 2007 11:13 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] RE: Doubtful code in os_unix.c
> 
> 
> Hi Simon,
> 
> yes but here this is not a memory leak issue i was talking about, but
> rather a crash :
> 
>  if( pNew==0 ){
>[...]
>releaseLockInfo(pNew->pLock);
>^- Here
>releaseOpenCnt(pNew->pOpen);
>^- and here
> 
> But okay, locking style aren't working yet under Unix and 
> will be worked
> on soon.
> 
> Regards,
> 
> Alex.
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 



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

Re: [sqlite] FTS Usage

[Scott Hess]

Also http://code.google.com/apis/gears/api_database.html#sqlite_fts
might add some information.

I have an item on my todo list which goes something like "Refactor the
FTS docs on the wiki".  Unfortunately, it's been on my todo list for a
couple months, now, with little progress.

-scott


On Nov 29, 2007 5:39 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> On 11/29/07, Jiri Hajek <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > I'm considering usage of FTS, but from the documentation it's a little
> > unclear to me what's the recommended way of using it, particularly in
> > the following scenario:
> >
> > Let's say that I already have some database structure containing
> > several tables with mixes type of data (some string fields that I
> > would like to index by FTS and others that I wouldn't need to index).
> > How should I use FTS in this case? Create a new FTS table and store
> > all text data there? Would it actually be a duplication, or can FTS
> > store only index, without the actual full strings?
> >
>
> You create a new VIRTUAL TABLE and let fts index only the fields that
> you want to conduct full-text search against. Your original table with
> its columns still remains.
>
> See  for a pretty
> nice explanation. If that explanation is not clear, please let us know
> what is confusing. I will try to make that explanation better.
>
> --
> Puneet Kishor
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] RE: Doubtful code in os_unix.c

[Alexandre Balaban]

Hi Simon,

yes but here this is not a memory leak issue i was talking about, but
rather a crash :

 if( pNew==0 ){
   [...]
   releaseLockInfo(pNew->pLock);
   ^- Here
   releaseOpenCnt(pNew->pOpen);
   ^- and here

But okay, locking style aren't working yet under Unix and will be worked
on soon.

Regards,

Alex.

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

[sqlite] 'nuther gui for SQLite

[P Kishor]

another gui for SQLite, among many other databases. Uses their own
jdbc driver to connect.



-- 
Puneet Kishor

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

Re: [sqlite] Doubtful code in os_unix.c

[Simon Davies]

See http://www.nabble.com/SQLite-3.5.2---Unix---Memory-issue--tf4851124.html

Rgds,
Simon

On 29/11/2007, Alexandre Balaban <[EMAIL PROTECTED]> wrote:
> Hello,
>
> did someone recently tested the unix code with the locking
> style enabled ?
> I ask because I saw dubious code in this file, specifically :
>
>  if( pNew==0 ){
>close(h);
>enterMutex();
>releaseLockInfo(pNew->pLock);
>releaseOpenCnt(pNew->pOpen);
>leaveMutex();
>return SQLITE_NOMEM;
>  }else{
>
> Which will lead to a crash for sure.
>
> I'm in process to update my AmigaOS4 port of version 3.3.18 to
> latest code and I am using the unix source as a reference
> implementation, am I right or wrong ?
>
> best regards,
>
> Alexandre BALABAN.
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

[sqlite] Doubtful code in os_unix.c

[Alexandre Balaban]

Hello,

did someone recently tested the unix code with the locking
style enabled ?
I ask because I saw dubious code in this file, specifically :

  if( pNew==0 ){
close(h);
enterMutex();
releaseLockInfo(pNew->pLock);
releaseOpenCnt(pNew->pOpen);
leaveMutex();
return SQLITE_NOMEM;
  }else{

Which will lead to a crash for sure.

I'm in process to update my AmigaOS4 port of version 3.3.18 to
latest code and I am using the unix source as a reference
implementation, am I right or wrong ?

best regards,

Alexandre BALABAN.

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

RE: [sqlite] Sqlite3Explorer Version 3.0 is available

[Cariotoglou Mike]

will do asap 

> -Original Message-
> From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 29, 2007 5:15 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Sqlite3Explorer Version 3.0 is available
> 
> Hi Mike,
> 
> Thanks for updated version, but still no drag n drop support 
> for opening database files :( 
> 
> Regards,
> Miha
> 
> "Cariotoglou Mike" <[EMAIL PROTECTED]> wrote on 29.11.2007 9:09:59:
> >New version with a lot of enhancements to the user interface, also 
> >updated to be compatible with the latest sqlite dll.
> >
> >information, change list and download here :
> >
> >http://www.singular.gr/sqlite
> >
> >(New features are described in the link near the top).
> >
> >Documentation, such as it is,  is out of date :)
> >
> >
> >-
> --
> >-- To unsubscribe, send email to 
> >[EMAIL PROTECTED]
> >-
> --
> >--
> >
> >
> 
> 
> --
> It's time to get rid of your current e-mail client ...
> ... and start using si.Mail.
> 
> It's small & free. ( http://simail.sourceforge.net/ )
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> 


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

Re: [sqlite] Sqlite3Explorer Version 3.0 is available

[Miha Vrhovnik]

Hi Mike,

Thanks for updated version, but still no drag n drop support for opening 
database files :(


Regards,
Miha

"Cariotoglou Mike" <[EMAIL PROTECTED]> wrote on 29.11.2007 9:09:59:
>New version with a lot of enhancements to the user interface, also
>updated to be compatible with
>the latest sqlite dll.
>
>information, change list and download here :
>
>http://www.singular.gr/sqlite
>
>(New features are described in the link near the top).
>
>Documentation, such as it is,  is out of date :)
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>
>


--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )

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

Re: [sqlite] ftruncate() for values greater than file size

[Dan]

On Nov 29, 2007, at 9:19 PM, Mark Brown wrote:


Hi-

I have finally tracked down a bug that has plagued by vxWorks  
port.  It
appears that our file system's implementation of ftruncate() does  
not like
to "truncate" a file larger than its current size, and returns  
EINVAL for

this operation.

My question...assuming that I can't get ftruncate to actually  
expand the
size of the database file by using this larger value, will SQLite  
have any
trouble with that?  If not, I'm just proposing I add a little check  
in this
method to get the current size of the file and then make sure that  
whatever

is passed to ftruncate does not exceed this value.


I think that will work.

You *might* run into a problem if you run the integrity-check after a
crash or power failure in incr-vacuum mode. But I think the problem
would only be with the integrity-check not with normal operation. Not
100% sure, just guessing...

Dan.




Thanks!
Mark



-- 
---

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





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

Re: [sqlite] Segmentation fault in syncJournal (pPager=0x102028) at src/pager.c:2417

[drh]

"Joseph Hsu" <[EMAIL PROTECTED]> wrote:
> Hello,
> I use SQLite 3.5.2 on ARM9 embedded system. ( Linux 2.4 )
> My program sometimes meets segmentation fault ! ( randomly, maybe several 
> hours, maybe several days ! )
> Here is a clip from GDB:
> -- snip --
> (gdb) backtrace
> #0  syncJournal (pPager=0x102028) at src/pager.c:2417

In SQLite 3.5.2, syncJournal begins on line 2748.  Are you
really user your are running version 3.5.2?  Have you 
modified the sources?


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


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

[sqlite] ftruncate() for values greater than file size

[Mark Brown]

Hi-

I have finally tracked down a bug that has plagued by vxWorks port.  It
appears that our file system's implementation of ftruncate() does not like
to "truncate" a file larger than its current size, and returns EINVAL for
this operation.

My question...assuming that I can't get ftruncate to actually expand the
size of the database file by using this larger value, will SQLite have any
trouble with that?  If not, I'm just proposing I add a little check in this
method to get the current size of the file and then make sure that whatever
is passed to ftruncate does not exceed this value.

Thanks!
Mark



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

Re: [sqlite] Lemon: Conflicts with repeated TERMINALS

[Joe Wilson]

--- Ralf Junker <[EMAIL PROTECTED]> wrote:
> >  paragraph ::= PARA text.
> 
> I observed the new PARA terminal token (the clear separator!?). Unfortunately 
> the lexer does not
> generate such a token. Paragraph repeats are also removed.

It was just an HTML-like example. I just wanted to demonstrate one
possible way to remove the conflicts by adding a special tag. 
I'm not suggesting that you alter your grammar in this way.

> >Here's another:
> >
> >  article ::= blocks.
> >
> >  blocks ::= block.
> >  blocks ::= blocks block.
> >
> >  block ::= heading NEWLINE.
> >  block ::= paragraph NEWLINE.
> >
> >  heading ::= HEADING_START text HEADING_END.
> >  heading ::= HEADING_START text.
> >  heading ::= HEADING_START.
> >
> >  paragraph ::= text.
> >
> >  text ::= textpiece.
> >  text ::= text textpiece.
> >
> >  textpiece ::= TEXT.
> >  textpiece ::= LINK.
> 
> This one also removes paragraph repeats, doesn't it? Unfortunately paragraphs 
> need to repeat for
> my grammar. Is there a way to achieve this without conflicts?

In your original grammar, you could have random sequences of TEXT 
and LINK and NEWLINE tokens without any way of differentiating whether 
they were part of a "text" or "paragraph" or "heading", hence the conflict.
So I figured that a paragraph may as well be any combination of
TEXT and LINK tokens ending with NEWLINE. The headings in my 2nd grammar
also have to end with NEWLINE. "paragraph" will not repeat, per se, but you 
can repeat "block" (see the "blocks" rules), where you can have several 
consecutive "block"s that happen to be of type paragraph so you can achieve 
the same effect. 

The following grammar may be clearer to you:

  article ::= blocks.

  blocks ::= block.
  blocks ::= blocks block.

  block ::= heading.
  block ::= paragraph.

  heading ::= HEADING_START text HEADING_END.
  heading ::= HEADING_START text NEWLINE.
  heading ::= HEADING_START NEWLINE.

  paragraph ::= NEWLINE.
  paragraph ::= text NEWLINE.

  text ::= textpiece.
  text ::= text textpiece.

  textpiece ::= TEXT.
  textpiece ::= LINK.

It's much the same as the previous grammar, but puts the NEWLINEs as part 
of the paragraph and heading rules instead of in the block rule.
The difference being that heading no longer needs to end with NEWLINE 
in one case if HEADING_END is encountered, as it is not ambiguous:

  heading ::= HEADING_START text HEADING_END.

and a paragraph in this grammar may also be empty so you can parse 
consecutive NEWLINE tokens with this rule:

  paragraph ::= NEWLINE.

Again, this was just an example of how to disambiguate the grammar. 
You can find other ways.

> >Lemon generates an .out file for the .y file processed.
> >You can examine it for errors.
> 
> I have tried to make sense of the .out file before. It tells me where to look 
> for the problem,
> but not how to fix it ...

Try reading some papers on parsing or search for the book
"Compilers: Principles, Techniques, and Tools" (a.k.a. 
the dragon book).

Also try writing on paper random sequences of tokens and 
manually parse your grammar to see the conflicts firsthand.



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

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

Re: [sqlite] FTS Usage

[P Kishor]

On 11/29/07, Jiri Hajek <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I'm considering usage of FTS, but from the documentation it's a little
> unclear to me what's the recommended way of using it, particularly in
> the following scenario:
>
> Let's say that I already have some database structure containing
> several tables with mixes type of data (some string fields that I
> would like to index by FTS and others that I wouldn't need to index).
> How should I use FTS in this case? Create a new FTS table and store
> all text data there? Would it actually be a duplication, or can FTS
> store only index, without the actual full strings?
>

You create a new VIRTUAL TABLE and let fts index only the fields that
you want to conduct full-text search against. Your original table with
its columns still remains.

See  for a pretty
nice explanation. If that explanation is not clear, please let us know
what is confusing. I will try to make that explanation better.

--
Puneet Kishor

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

[sqlite] Re: Re[2]: rollback fails incomprehencibly

[Igor Tandetnik]

???  <[EMAIL PROTECTED]> wrote:

Ok, I tried to do reset for my statement
and ... got SQLITE_BUSY too!
I.e. sqlite_reset() (and sqlite_finalize() too) returns SQLITE_BUSY,
althought statement seems to become reset and rollback complete
at last.


When sqlite3_step fails, it always returns a generic SQLITE_ERROR code. 
A subsequent call to sqlite3_reset or sqlite3_finalize returns the 
specific error code that sqlite3_step should have returned. The _reset 
or _finalize itself succeeds, despite returning an error.


It's not clear why it was done this way originally, but now it is 
maintained for backward compatibility. If you use sqlite3_prepare_v2 
instead of sqlite3_prepare to prepare your statements, you opt out of 
this legacy behavior: sqlite3_step would now return an appropriate error 
code directly, and sqlite3_reset and _finalize would only return error 
codes if they genuinely fail.


Igor Tandetnik 



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

[sqlite] FTS Usage

[Jiri Hajek]

Hello,

I'm considering usage of FTS, but from the documentation it's a little
unclear to me what's the recommended way of using it, particularly in
the following scenario:

Let's say that I already have some database structure containing
several tables with mixes type of data (some string fields that I
would like to index by FTS and others that I wouldn't need to index).
How should I use FTS in this case? Create a new FTS table and store
all text data there? Would it actually be a duplication, or can FTS
store only index, without the actual full strings?

Thanks for your help,
Jiri

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

Re: [sqlite] Undefined collation: Peculiar observations ...

[Ralf Junker]

Hello Trevor Talbot,

>> Trevor, I am not sure what you mean by "subselect on its own". Is this what 
>> you are looking for?
>
>> sqlite> SELECT * FROM (SELECT * FROM a);
>> SQL error: no such collation sequence: unknown
>
>Yes, exactly.  I was curious to see if it made any kind of difference.
>Unfortunately I don't have an explanation/fix for you though.

I created two tickets about these collation peculiarities yesterday.

Ralf 


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

[sqlite] Re[2]: rollback fails incomprehencibly

[??????? ????????]

Ok, I tried to do reset for my statement
and ... got SQLITE_BUSY too!
I.e. sqlite_reset() (and sqlite_finalize() too) returns SQLITE_BUSY,
althought statement seems to become reset and rollback complete
at last.

Now I have a question: is it normal in my case that sqlite_reset()
returns an error, and is it correct to ignore it?


> Hi,

>  Statements will be in progress until you issue an sqlite_reset or
>  sqlite_finalize on them.

>  I don't know why rollback doesn't take care of this; perhaps someone
>  will explain,

>  Rgds,
>  Simon

>  On 23/11/2007, ???  <[EMAIL PROTECTED]> wrote:
 >> Hi all...
 >>
 >> Explain me something please. Consider scenario:
 >>
 >>  Connection 1: begin
 >>  Connection 1: update...
 >>  Connection 2: begin
 >>  Connection 2: update...  -> SQLITE_BUSY
 >>  Connection 2: rollback  -> "cannot rollback transaction - SQL
 >> statements in progress"
 >>
 >> Why didn't rollback work?
 >> How can I cancel the transaction in connection 2?
 >>
 >> P.S. I use SQLite 3.5.1.
 >> I have read documentation for BEGIN-ROLLBACK and ON CONFLICT.
 >>

Re: [sqlite] Lemon: Conflicts with repeated TERMINALS

[Ralf Junker]

Many thanks, Joe,

>Your grammar is ambiguous. The text tokens run together for 
>various rules because the grammar lacks clear separators between 
>them. 

OK, I begin to understand. The "clear separators" need to be TERMINALs, right? 
I believed that these were imlicit because there are TEXT and LINK after all 
text tokens are fully expanded. Therefore I thought that the grammar would not 
be ambiguous.

>You can fix it a million ways by altering your grammar.

Thanks for the suggestions - I can see that they do not generate conflicts, but 
they certainly alter the grammar.

>Here is one way:
>
>  article ::= blocks.
>
>  blocks ::= block.
>  blocks ::= blocks block.
>
>  block ::= heading.
>  block ::= paragraph.
>
>  heading ::= HEADING_START text HEADING_END.
>  heading ::= HEADING_START text.
>  heading ::= HEADING_START.
>
>  paragraph ::= PARA text.
>
>  text ::= textpiece.
>  text ::= text textpiece.
>
>  textpiece ::= TEXT.
>  textpiece ::= LINK.

I observed the new PARA terminal token (the clear separator!?). Unfortunately 
the lexer does not generate such a token. Paragraph repeats are also removed.

>Here's another:
>
>  article ::= blocks.
>
>  blocks ::= block.
>  blocks ::= blocks block.
>
>  block ::= heading NEWLINE.
>  block ::= paragraph NEWLINE.
>
>  heading ::= HEADING_START text HEADING_END.
>  heading ::= HEADING_START text.
>  heading ::= HEADING_START.
>
>  paragraph ::= text.
>
>  text ::= textpiece.
>  text ::= text textpiece.
>
>  textpiece ::= TEXT.
>  textpiece ::= LINK.

This one also removes paragraph repeats, doesn't it? Unfortunately paragraphs 
need to repeat for my grammar. Is there a way to achieve this without conflicts?

>Lemon generates an .out file for the .y file processed.
>You can examine it for errors.

I have tried to make sense of the .out file before. It tells me where to look 
for the problem, but not how to fix it ...

I am sorry to appear stupid, but I still can not make sense of it all. Can 
someone still help, please?

Ralf 


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

[sqlite] Sqlite3Explorer Version 3.0 is available

[Cariotoglou Mike]

New version with a lot of enhancements to the user interface, also
updated to be compatible with
the latest sqlite dll.

information, change list and download here :

http://www.singular.gr/sqlite

(New features are described in the link near the top).

Documentation, such as it is,  is out of date :)


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

Re: [sqlite] Undefined collation: Peculiar observations ...

[Trevor Talbot]

On 11/28/07, Ralf Junker <[EMAIL PROTECTED]> wrote:

> >> Imagine that a SQLite3 database opened in a custom application with a 
> >> registered a collation sequence named "unknown" has created the following 
> >> table:
> >>
> >>   CREATE TABLE a (b COLLATE unknown);

> Trevor, I am not sure what you mean by "subselect on its own". Is this what 
> you are looking for?

> sqlite> SELECT * FROM (SELECT * FROM a);
> SQL error: no such collation sequence: unknown

Yes, exactly.  I was curious to see if it made any kind of difference.
Unfortunately I don't have an explanation/fix for you though.

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