Re: [sqlite] Performance problems potentially related to index scalability

[Joe Wilson]

> I understand that I'm experiencing thrashing, but I'm curious as to why I'm
> experiencing it. My understanding is that the row_id PRIMARY KEY column has
> an index on it by default. Inserting into this index doesn't cause
> thrashing, but inserting into the secondary index does. I hoped that
> enabling the shared cache mode and periodically doing the select operation
> on it would keep it in SQLITE's cache. Based on what I saw from I/O stat,
> top, and my timing measurements, that doesn't seem to be the case. In fact,
> my cache "priming" experiment showed that it didn't help at all,  despite a
> cache size of 500 MB and the index being on a column with an INTEGER pragma.
> The transaction immediately following the cache priming would be just as
> slow as the transactions far away from it. Why would a transaction inserting
> 1024 rows of  0.5 KB each result in the eviction of that index, despite the
> abundance of cache?

The OS disk cache is not effective because the index fragments are currently 
scattered across all pages of the table it is indexing. Even VACUUM (as it 
is currently written in sqlite/src/vacuum.c) won't help such indexes be 
contiguous in the file.

The following patch to VACUUM allows pages of non-inline indexes explicitly 
made with CREATE INDEX to be contiguous in the file, which can improve VACUUM
times and non-inline index locality of reference somewhat:

 http://www.sqlite.org/cvstrac/tktview?tn=2075

It works by moving the index creation after the table row inserts (as 
one might do manually) so the index pages are not intermixed with table 
pages.

Unfortunately this patch will not aid inline indexes made within the 
CREATE TABLE statement (i.e., UNIQUE). Nor will this patch help the 
situation when rows are being first inserted into a table (prior to 
VACUUM). So it won't help table population speed at all.

> The solution you propose doesn't work when your table has multiple indices
> of this type. One can imagine altering the schema as to store each indexed
> column in a separate table, with a column containing the rowid to the main
> table, but that would result in a significant amount of redundant overhead.

Don't discount this workaround without first doing timings and database 
size experiments.

If SQLite could be persuaded to reserve contiguous blocks of pages in advance 
for exclusive use for each index, insert times could improve for table with 
more than one index. The database file would be larger while the tables are 
being populated, but it could be made to shrink to a smaller size with an 
explicit VACUUM. I think this time/space trade-off would be acceptable to most 
users. This could be controlled via a PRAGMA if you want the classic paging
behavior.


 

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com

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

RE: [sqlite] How do I get MATCH and REGEXP

[Info]

So, if I understand correctly, SQLite does not support these two operators
"out of the box".
Is there an easy way to get user-defined functions for regexp and match? Or
has no one developed those yet?

Regards,
Rick van der Lans

-Oorspronkelijk bericht-
Van: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Verzonden: Friday, February 09, 2007 10:55 PM
Aan: sqlite-users@sqlite.org
Onderwerp: Re: [sqlite] How do I get MATCH and REGEXP


On Fri, 9 Feb 2007 21:11:31 +0100, Rick wrote:

>The manual mentions the support for the MATCH and the REGEXP operators. But
>if I use it, it doesn't work. Do I have to do something special to install
>those operators?

http://www.sqlite.org/lang_expr.html tells:

"The REGEXP operator is a special syntax for the regexp() user
function. No regexp() user function is defined by default and so
use of the REGEXP operator will normally result in an error
message. If a user-defined function named "regexp" is added at
run-time, that function will be called in order to implement the
REGEXP operator.

The MATCH operator is a special syntax for the match() user
function. The default match() function implementation raises and
exception and is not really useful for anything. But extensions
can override the match() function with more helpful logic."

>Regards,
>Rick van der Lans

HTH
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





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

[sqlite] SQLITE_OMIT_TRIGGER

[Noah Hart]

I have no problems compiling under VS2005 using 3.3.12 when I don't
define SQLITE_OMIT_TRIGGER.

However, I do get an error compiling when I define SQLITE_OMIT_TRIGGER=1

The linker complains about sqlite3_enable_shared_cache not being
exported.

Is this just VS2005 wierdness, or is there a missing define for when
SQLITE_OMIT_TRIGGER=1

Regards,

Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.




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

Re: [sqlite] Performance problems potentially related to index scalability

[Stan Bielski]

On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Stan Bielski" <[EMAIL PROTECTED]> wrote:
>
> Anyone care to take a guess what's going on here?

The problem is that you are thrashing.  The working set
on the database file is exceeding the amount of memory
that your OS has set aside for disk cache.





The best solution, if possible, is to insert records in
sorted order by index.  If you are inserting in batches,
try inserting them all into a TEMP table first.  Then
do

   INSERT INTO maintab SELECT * FROM temptab ORDER BY indexcolumn;
   DELETE FROM temptab;




Thanks for your reply.

I understand that I'm experiencing thrashing, but I'm curious as to why I'm
experiencing it. My understanding is that the row_id PRIMARY KEY column has
an index on it by default. Inserting into this index doesn't cause
thrashing, but inserting into the secondary index does. I hoped that
enabling the shared cache mode and periodically doing the select operation
on it would keep it in SQLITE's cache. Based on what I saw from I/O stat,
top, and my timing measurements, that doesn't seem to be the case. In fact,
my cache "priming" experiment showed that it didn't help at all,  despite a
cache size of 500 MB and the index being on a column with an INTEGER pragma.
The transaction immediately following the cache priming would be just as
slow as the transactions far away from it. Why would a transaction inserting
1024 rows of  0.5 KB each result in the eviction of that index, despite the
abundance of cache?

The solution you propose doesn't work when your table has multiple indices
of this type. One can imagine altering the schema as to store each indexed
column in a separate table, with a column containing the rowid to the main
table, but that would result in a significant amount of redundant overhead.

Again, thanks for the reply... I'm just trying to figure out what's
happening internally without adding instrumentation to the source code ;-)

-Stan
'

If SQLite were smarter, it might be able to deal with

this situation automatically.  It is not clear at this
time if making SQLite smart enough to deal with this is
compatible with the goal of keeping SQLite small and
nimble.  We are working the problem.  Do not expect a
resolution in the near term.
--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Running out of disk space.

[Jeffrey Rennie]

On 2/9/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


Jeffrey Rennie wrote:
> I think the code in the next higher stackframe may be the culprit.
>
> I inserted a new line of code at vbde.c:2374 so it now reads:
>
>if( pOp->p2 ){
>  assert( i==1 );
>  sqlite3RollbackAll(db);
>  db->autoCommit = 1;
>}else{
>  db->autoCommit = i;
>  if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
>p->pTos = pTos;
>p->pc = pc;
>db->autoCommit = 1-i;
>p->rc = SQLITE_BUSY;
>return SQLITE_BUSY;
>  }
>  return SQLITE_OK == p->rc ? SQLITE_DONE : p->rc;   // my new line
>}
>return SQLITE_DONE;
>
>
> And sqlite_step() now returns SQLITE_FULL as I had expected.
>
Jeffrey,

I'm a little suspicious of your fix.  You said you are using version
3.3.4 and it only has the older version of sqlite_step which is
documented as only returning a subset of the sqlite error codes at
http://www.sqlite.org/capi3ref.html#sqlite3_step

In the lagacy interface, the return value will be either
SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.

So it should never return SQLITE_FULL. Under a disk full condition it
should return SQLITE_ERROR, and then you would get the SQLITE_FULL error
when you called sqlite_reset (see the section Goofy Interface Alert).



Indeed, my fix does not conform to the documentation.



Nonetheless, you are saying you are getting an SQLITE_DONE when the disk
is full.



Yes, I'm still seeing SQLITE_DONE when the disk is full.

But thanks for the pointer to the Goofy Interface Alert!  Even though the
sqlite_step() returns SQLITE_DONE, the sqlite_finalize() call returns
SQLITE_FULL, so I am able to detect the disk full situation.

Thanks again!  My problem is solved.

-Jeffrey Rennie

Re: [sqlite] Running out of disk space.

[Dennis Cote]

Jeffrey Rennie wrote:

I think the code in the next higher stackframe may be the culprit.

I inserted a new line of code at vbde.c:2374 so it now reads:

   if( pOp->p2 ){
 assert( i==1 );
 sqlite3RollbackAll(db);
 db->autoCommit = 1;
   }else{
 db->autoCommit = i;
 if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
   p->pTos = pTos;
   p->pc = pc;
   db->autoCommit = 1-i;
   p->rc = SQLITE_BUSY;
   return SQLITE_BUSY;
 }
 return SQLITE_OK == p->rc ? SQLITE_DONE : p->rc;   // my new line
   }
   return SQLITE_DONE;


And sqlite_step() now returns SQLITE_FULL as I had expected.


Jeffrey,

I'm a little suspicious of your fix.  You said you are using version 
3.3.4 and it only has the older version of sqlite_step which is 
documented as only returning a subset of the sqlite error codes at 
http://www.sqlite.org/capi3ref.html#sqlite3_step


   In the lagacy interface, the return value will be either 
SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE.


So it should never return SQLITE_FULL. Under a disk full condition it 
should return SQLITE_ERROR, and then you would get the SQLITE_FULL error 
when you called sqlite_reset (see the section Goofy Interface Alert).


Nonetheless, you are saying you are getting an SQLITE_DONE when the disk 
is full.


What does the version 3.3.4 sqlite shell give you when you do an explain 
on a commit statement?


sqlite3 test.db
.explain on
explain commit;

Dennis Cote

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

Re: [sqlite] How do I get MATCH and REGEXP

[Kees Nuyt]

On Fri, 9 Feb 2007 21:11:31 +0100, Rick wrote:

>The manual mentions the support for the MATCH and the REGEXP operators. But
>if I use it, it doesn't work. Do I have to do something special to install
>those operators?

http://www.sqlite.org/lang_expr.html tells:

"The REGEXP operator is a special syntax for the regexp() user
function. No regexp() user function is defined by default and so
use of the REGEXP operator will normally result in an error
message. If a user-defined function named "regexp" is added at
run-time, that function will be called in order to implement the
REGEXP operator.

The MATCH operator is a special syntax for the match() user
function. The default match() function implementation raises and
exception and is not really useful for anything. But extensions
can override the match() function with more helpful logic."

>Regards,
>Rick van der Lans

HTH
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] Running out of disk space.

[Jeffrey Rennie]

I think the code in the next higher stackframe may be the culprit.

I inserted a new line of code at vbde.c:2374 so it now reads:

   if( pOp->p2 ){
 assert( i==1 );
 sqlite3RollbackAll(db);
 db->autoCommit = 1;
   }else{
 db->autoCommit = i;
 if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
   p->pTos = pTos;
   p->pc = pc;
   db->autoCommit = 1-i;
   p->rc = SQLITE_BUSY;
   return SQLITE_BUSY;
 }
 return SQLITE_OK == p->rc ? SQLITE_DONE : p->rc;   // my new line
   }
   return SQLITE_DONE;


And sqlite_step() now returns SQLITE_FULL as I had expected.

On 2/9/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


Jeffrey Rennie wrote:
> Debugging the code:
>
> winWrite returns SQLITE_FULL, which propagates back up the stack to
> vdbeaux.c, line 1270, in function sqlite3VdbeHalt(Vdbe *p):
>
>   }else if( rc!=SQLITE_OK ){
>  p->rc = rc;
>  sqlite3RollbackAll(db);
>
> Which is good, it's putting the SQLITE_FULL return code into p->rc and
> rolling everything back.  Good.
>
> But then the function returns SQLITE_OK on line 1337, so sqlite_step
> returns
> SQLITE_DONE.
>
> So indeed, when a COMMIT TRANSACTION fails because there isn't enough
> disk
> space, sqlite_step returns SQLITE_DONE.
>
> Is there a bug filed for this?  Has it been fixed in more recent
> releases?
>
Jeffrey,

This is not the problem. The assignment at 1270 is saving the error
return value into the sqlite3_stmt (or vdeb) structure to record the
failure of this statement. The value returned at 1337 simply tells the
caller that this op-code (Halt) executed correctly. This op-code is only
one step in the execution of the statement.

I'm not saying you haven't found a problem with respect to full disks,
but this code is not the culprit. You will need to keep digging.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] How do I get MATCH and REGEXP

[Info]

The manual mentions the support for the MATCH and the REGEXP operators. But
if I use it, it doesn't work. Do I have to do something special to install
those operators?

Regards,
Rick van der Lans




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

RE: [sqlite] Does julianday work according to the manual?

[Info]

Thanks for your help, guys,
It now makes sense.

Rick van der Lans

-Oorspronkelijk bericht-
Van: Doug Currie [mailto:[EMAIL PROTECTED] 
Verzonden: Wednesday, January 31, 2007 4:16 PM
Aan: sqlite-users@sqlite.org
Onderwerp: Re: [sqlite] Does julianday work according to the manual?

On Wednesday, January 31, 2007 Rick van der Lans wrote:

> The manual states that the function julianday returns the number of days
> since noon in Greenwich on November 24, 4714 B.C. That would imply that
the
> statement:

> Select julianday('-4714-11-24 12:00:00');

> Should return 0.0. But it doesn't, it returns -365.0

> Does this mean, that the manual should say "since noon in Greenwich on
> November 24, 4713 B.C.? Or am I missing something?


sqlite> Select julianday('-0001-11-24 12:00:00');
1721022.0
sqlite> Select julianday('-11-24 12:00:00');
1721388.0
sqlite> Select julianday('0001-11-24 12:00:00');
1721753.0

There is no year 0. The calendar goes from -1 BCE to 1 CE. -11-24
is 1 BCE. So, if you want November 24, 4714 B.C. you need to say

sqlite> Select julianday('-4713-11-24 12:00:00');
0.0

e

-- 
Doug Currie
Londonderry, NH, USA



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





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

Re: [sqlite] Running out of disk space.

[Dennis Cote]

Jeffrey Rennie wrote:

Debugging the code:

winWrite returns SQLITE_FULL, which propagates back up the stack to
vdbeaux.c, line 1270, in function sqlite3VdbeHalt(Vdbe *p):

  }else if( rc!=SQLITE_OK ){
 p->rc = rc;
 sqlite3RollbackAll(db);

Which is good, it's putting the SQLITE_FULL return code into p->rc and
rolling everything back.  Good.

But then the function returns SQLITE_OK on line 1337, so sqlite_step 
returns

SQLITE_DONE.

So indeed, when a COMMIT TRANSACTION fails because there isn't enough 
disk

space, sqlite_step returns SQLITE_DONE.

Is there a bug filed for this?  Has it been fixed in more recent 
releases?



Jeffrey,

This is not the problem. The assignment at 1270 is saving the error 
return value into the sqlite3_stmt (or vdeb) structure to record the 
failure of this statement. The value returned at 1337 simply tells the 
caller that this op-code (Halt) executed correctly. This op-code is only 
one step in the execution of the statement.


I'm not saying you haven't found a problem with respect to full disks, 
but this code is not the culprit. You will need to keep digging.


HTH
Dennis Cote

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

Re: [sqlite] Running out of disk space.

[Martin Jenkins]

Jeffrey Rennie wrote:

Debugging the code:

winWrite returns SQLITE_FULL, which propagates back up the stack to
vdbeaux.c, line 1270, in function sqlite3VdbeHalt(Vdbe *p):

  }else if( rc!=SQLITE_OK ){
 p->rc = rc;
 sqlite3RollbackAll(db);

Which is good, it's putting the SQLITE_FULL return code into p->rc and
rolling everything back.  Good.

But then the function returns SQLITE_OK on line 1337, so sqlite_step 
returns

SQLITE_DONE.

So indeed, when a COMMIT TRANSACTION fails because there isn't enough 
disk

space, sqlite_step returns SQLITE_DONE.

Is there a bug filed for this?  Has it been fixed in more recent 
releases?
I had an instance once where I filled a disk up and thought I'd lost 
some data, but because the testsuite has a disk full test I assumed it 
was a problem on my part. I can't remember if it was repeatable but I 
know I didn't look into it as the disk getting full in the first place 
was caused by a bug in my code.  ISTR one of the tests is skipped (or is 
it only in the full test?) because it takes ages to fill the disk up.  
Have/can you  run the suite on your "full" disk?


Martin


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

Re: [sqlite] Running out of disk space.

[Jeffrey Rennie]

Debugging the code:

winWrite returns SQLITE_FULL, which propagates back up the stack to
vdbeaux.c, line 1270, in function sqlite3VdbeHalt(Vdbe *p):

  }else if( rc!=SQLITE_OK ){
 p->rc = rc;
 sqlite3RollbackAll(db);

Which is good, it's putting the SQLITE_FULL return code into p->rc and
rolling everything back.  Good.

But then the function returns SQLITE_OK on line 1337, so sqlite_step returns
SQLITE_DONE.

So indeed, when a COMMIT TRANSACTION fails because there isn't enough disk
space, sqlite_step returns SQLITE_DONE.

Is there a bug filed for this?  Has it been fixed in more recent releases?

On 2/9/07, Jeffrey Rennie <[EMAIL PROTECTED]> wrote:


It looks like the journal file itself is running out of disk space.  It
has only 512 bytes, even though I'm creating lots of tables in the
transaction, and the DB file itself is stuck at 0 bytes.  Then, COMMIT
TRANSACTION returns SQLITE_DONE, the journal file disappears, and I'm left
with a db of size 0 bytes.

There is 6144 bytes of free disk space on my drive.

On 2/9/07, Jeffrey Rennie <[EMAIL PROTECTED]> wrote:
>
> Thanks Artem.  Your description of events agrees with the documentation
> and what I would expect to happen, but not with what I'm observing in
> running code.
>
> I see that the sqlite_step() for the "COMMIT TRANSACTION" returns
> SQLITE_DONE, but then the changes in the transaction have been rolled back.
>
> On 2/8/07, Artem Yankovskiy < [EMAIL PROTECTED]> wrote:
> >
> > Hi.
> > You do not receive the error message until receive 0
> > free disk spaces.
> > When queryes are running in transaction, record in a
> > DB does not write, the journal-file is created only,
> > therefore you see your changes. As soon as you make
> > commit, there is a records of changes in a DB. During
> > this moment there can be an ending of an empty space
> > on volume, then sqlite will return an error and will
> > roll away changes.
> >
> > --- Jeffrey Rennie < [EMAIL PROTECTED]> wrote:
> >
> > > What happens, and/or what is supposed to happen when
> > > sqlite runs out of disk
> > > space?
> > >
> > > In an extremely disk-space constrained situation, I
> > > create a bunch of
> > > tables, without any sqlite errors, and then later
> > > the tables are not found.
> > > I see the same thing when inserting rows: no error,
> > > but later look-ups don't
> > > find inserted rows.  I'd like to detect that the
> > > write to DB failed at time
> > > of write, not a later read.  I'm also doing the
> > > INSERTS and CREATE TABLES
> > > within a transaction, and again all the sqlite calls
> > > succeed, even the
> > > COMMIT TRANSACTION.  There are no other pending
> > > statements at the time of
> > > the COMMIT TRANSACTION.
> > >
> > > I'm using version 3.3.4 on Windows.
> > >
> > > Thanks,
> > > Jeffrey Rennie
> > >
> >
> >
> > Best regards,
> > Artem Yankovskiy
> >
> >
> >
> >
> >
> >
> > 
> > Вы уже с Yahoo!?
> > Испытайте обновленную и улучшенную. Yahoo! Почту!
> > http://ru.mail.yahoo.com
> >
> >
> > 
-
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > 
-
> >
> >
> >
>

Re: [sqlite] Backing up a SQlite database

[Dennis Cote]

[EMAIL PROTECTED] wrote:


You should either have your backup application open the database and do a
BEGIN EXCLUSIVE; statement to ensure that no other processes can write to it
while you're backing it up, or if you don't want to do that, you can use the
command line shell and do:

  sqlite3 .dump database.db > database.sql

and then back up database.sql.  In this latter case, the shell ensures that
the database is unchanged during the entire dump.  To restore, you do
something like this on Windows (I'm not a Windows expert so the command may
need some fixing):

  del database.db
  type database.sql | sqlite3 database.db


  

Derrell,

I believe that should be:

 sqlite3 database.db .dump > database.sql

 sqlite3 database.db < database.sql

But I also wanted to point out that this does not create an exact copy 
of your database. In particular it does not include the pragams that may 
have been set in the original database. Settings like default_page_size 
and auto_vacuum are not dumped.


To create a complete backup you need to use your first approach to 
create a utility does a normal file copy in code inside a transaction 
(which prevents other processes from doing any writes).


Dennis Cote

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

Re: [sqlite] Running out of disk space.

[Jeffrey Rennie]

It looks like the journal file itself is running out of disk space.  It has
only 512 bytes, even though I'm creating lots of tables in the transaction,
and the DB file itself is stuck at 0 bytes.  Then, COMMIT TRANSACTION
returns SQLITE_DONE, the journal file disappears, and I'm left with a db of
size 0 bytes.

There is 6144 bytes of free disk space on my drive.

On 2/9/07, Jeffrey Rennie <[EMAIL PROTECTED]> wrote:


Thanks Artem.  Your description of events agrees with the documentation
and what I would expect to happen, but not with what I'm observing in
running code.

I see that the sqlite_step() for the "COMMIT TRANSACTION" returns
SQLITE_DONE, but then the changes in the transaction have been rolled back.

On 2/8/07, Artem Yankovskiy <[EMAIL PROTECTED]> wrote:
>
> Hi.
> You do not receive the error message until receive 0
> free disk spaces.
> When queryes are running in transaction, record in a
> DB does not write, the journal-file is created only,
> therefore you see your changes. As soon as you make
> commit, there is a records of changes in a DB. During
> this moment there can be an ending of an empty space
> on volume, then sqlite will return an error and will
> roll away changes.
>
> --- Jeffrey Rennie < [EMAIL PROTECTED]> wrote:
>
> > What happens, and/or what is supposed to happen when
> > sqlite runs out of disk
> > space?
> >
> > In an extremely disk-space constrained situation, I
> > create a bunch of
> > tables, without any sqlite errors, and then later
> > the tables are not found.
> > I see the same thing when inserting rows: no error,
> > but later look-ups don't
> > find inserted rows.  I'd like to detect that the
> > write to DB failed at time
> > of write, not a later read.  I'm also doing the
> > INSERTS and CREATE TABLES
> > within a transaction, and again all the sqlite calls
> > succeed, even the
> > COMMIT TRANSACTION.  There are no other pending
> > statements at the time of
> > the COMMIT TRANSACTION.
> >
> > I'm using version 3.3.4 on Windows.
> >
> > Thanks,
> > Jeffrey Rennie
> >
>
>
> Best regards,
> Artem Yankovskiy
>
>
>
>
>
>
> 
> Вы уже с Yahoo!?
> Испытайте обновленную и улучшенную. Yahoo! Почту!
> http://ru.mail.yahoo.com
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>

Re: [sqlite] Backing up a SQlite database

[Rich Shepard]

On Fri, 9 Feb 2007, Christian Smith wrote:


No, no, no! Copying the file is not atomic, and a live database may be
updated part way through the copy.


  Mea culpa! My response was based on my own use of sqlite, which is
embedded in models. Therefore, when I do any copying of the database file
it's when the model is not running. I've not used sqlite in a continuously
live environment.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] Running out of disk space.

[Jeffrey Rennie]

Thanks Artem.  Your description of events agrees with the documentation and
what I would expect to happen, but not with what I'm observing in running
code.

I see that the sqlite_step() for the "COMMIT TRANSACTION" returns
SQLITE_DONE, but then the changes in the transaction have been rolled back.

On 2/8/07, Artem Yankovskiy <[EMAIL PROTECTED]> wrote:


Hi.
You do not receive the error message until receive 0
free disk spaces.
When queryes are running in transaction, record in a
DB does not write, the journal-file is created only,
therefore you see your changes. As soon as you make
commit, there is a records of changes in a DB. During
this moment there can be an ending of an empty space
on volume, then sqlite will return an error and will
roll away changes.

--- Jeffrey Rennie <[EMAIL PROTECTED]> wrote:

> What happens, and/or what is supposed to happen when
> sqlite runs out of disk
> space?
>
> In an extremely disk-space constrained situation, I
> create a bunch of
> tables, without any sqlite errors, and then later
> the tables are not found.
> I see the same thing when inserting rows: no error,
> but later look-ups don't
> find inserted rows.  I'd like to detect that the
> write to DB failed at time
> of write, not a later read.  I'm also doing the
> INSERTS and CREATE TABLES
> within a transaction, and again all the sqlite calls
> succeed, even the
> COMMIT TRANSACTION.  There are no other pending
> statements at the time of
> the COMMIT TRANSACTION.
>
> I'm using version 3.3.4 on Windows.
>
> Thanks,
> Jeffrey Rennie
>


Best regards,
Artem Yankovskiy







Вы уже с Yahoo!?
Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Backing up a SQlite database

[Derrell . Lipman]

Rich Shepard <[EMAIL PROTECTED]> writes:

> On Fri, 9 Feb 2007, Mikey C wrote:
>
>> This might be a dumb question, but is taking a backup of a live database
>> simply a matter of copying the file to a backup device/drive?
>
>   Yes. It's a regular file to your OS. As a matter of fact, you can copy the
> file to another name and open that other name to see the same tables and
> data as with the original. That's how I keep a backup of the database I'm
> developing.
>
>> And restoring it a matter of copying it back?
>
>   Yes.

That is a dangerous way to back up a live database.  If another process
decides to write to the database while you're backing it up, you'll be backing
up partially modified data, and you're also ignoring any possible journal
file.  Whey you restore, you'll find corrupt data in this case.

You should either have your backup application open the database and do a
BEGIN EXCLUSIVE; statement to ensure that no other processes can write to it
while you're backing it up, or if you don't want to do that, you can use the
command line shell and do:

  sqlite3 .dump database.db > database.sql

and then back up database.sql.  In this latter case, the shell ensures that
the database is unchanged during the entire dump.  To restore, you do
something like this on Windows (I'm not a Windows expert so the command may
need some fixing):

  del database.db
  type database.sql | sqlite3 database.db

Derrell

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

Re: [sqlite] Backing up a SQlite database

[Christian Smith]

Rich Shepard uttered:


On Fri, 9 Feb 2007, Mikey C wrote:


This might be a dumb question, but is taking a backup of a live database
simply a matter of copying the file to a backup device/drive?


 Yes. It's a regular file to your OS. As a matter of fact, you can copy the
file to another name and open that other name to see the same tables and
data as with the original. That's how I keep a backup of the database I'm
developing.



No, no, no! Copying the file is not atomic, and a live database may be 
updated part way through the copy.


Use the sqlite shell .dump command, which will implement the necessary 
locking:

$ sqlite3 db.file .dump  > backup.sql

The backed up file is a SQL script that will restore the database.


Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

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

Re: [sqlite] Emergency Bail Out

[Mark Richards]

Rich Shepard wrote:

  Yesterday I trapped myself in sqlite3 and could not exit the application.
I was running the SQLite shell in a virtual console and inadvertently
entered ',e' instead of '.e' to exit.

  Cue the theme from 'Jaws.' I was trapped and could not escape. Not, that
is, until I went to another vc and killed the process.

  The next time my fingers get ahead of my eyes and brain, I'd like to 
use a

more elegant method of recovering from my mis-typed command. What do I do?

CONTROL D


Rich




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

Re: [sqlite] Backing up a SQlite database

[Will Leshner]

On 2/9/07, Rich Shepard <[EMAIL PROTECTED]> wrote:

On Fri, 9 Feb 2007, Mikey C wrote:

> This might be a dumb question, but is taking a backup of a live database
> simply a matter of copying the file to a backup device/drive?

   Yes. It's a regular file to your OS. As a matter of fact, you can copy the
file to another name and open that other name to see the same tables and
data as with the original. That's how I keep a backup of the database I'm
developing.


You do, however, want to be sure there isn't a hot journal lying
around when you do the copy. Otherwise you could end up copying the
database file and not the journal, which effectively corrupts the
database.

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

Re: [sqlite] Backing up a SQlite database

[Rich Shepard]

On Fri, 9 Feb 2007, Mikey C wrote:


This might be a dumb question, but is taking a backup of a live database
simply a matter of copying the file to a backup device/drive?


  Yes. It's a regular file to your OS. As a matter of fact, you can copy the
file to another name and open that other name to see the same tables and
data as with the original. That's how I keep a backup of the database I'm
developing.


And restoring it a matter of copying it back?


  Yes.


I am using Windows with NTFS drives.


  Works there, too.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] Emergency Bail Out

[Rich Shepard]

On Fri, 9 Feb 2007, P Kishor wrote:


hmmm, I don't know what a "virtual console" is but on my computer...


  That's a console in X that looks like a regular terminal console. I use
rxvt, others use xterm, eterm, aterm, etc.


Lucknow:~ punkish$ sqlite3 foo.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> ,e
 ...> ;
SQL error: near ",": syntax error
sqlite> .q


  I should have written that using a semicolon was the first thing I tried.
It produces the ...> prompt again. However, ... just now it worked.

  Must have been one of those transient computer moments.

Thanks,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] Emergency Bail Out

[P Kishor]

hmmm, I don't know what a "virtual console" is but on my computer...

Lucknow:~ punkish$ sqlite3 foo.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> ,e
  ...> ;
SQL error: near ",": syntax error
sqlite> .q
Lucknow:~ punkish$

On 2/9/07, Rich Shepard <[EMAIL PROTECTED]> wrote:

   Yesterday I trapped myself in sqlite3 and could not exit the application.
I was running the SQLite shell in a virtual console and inadvertently
entered ',e' instead of '.e' to exit.

   Cue the theme from 'Jaws.' I was trapped and could not escape. Not, that
is, until I went to another vc and killed the process.

   The next time my fingers get ahead of my eyes and brain, I'd like to use a
more elegant method of recovering from my mis-typed command. What do I do?

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

[sqlite] Backing up a SQlite database

[Mikey C]

This might be a dumb question, but is taking a backup of a live database
simply a matter of copying the file to a backup device/drive?

And restoring it a matter of copying it back?

I am using Windows with NTFS drives.


-- 
View this message in context: 
http://www.nabble.com/Backing-up-a-SQlite-database-tf3201601.html#a8889729
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] Emergency Bail Out

[Rich Shepard]

  Yesterday I trapped myself in sqlite3 and could not exit the application.
I was running the SQLite shell in a virtual console and inadvertently
entered ',e' instead of '.e' to exit.

  Cue the theme from 'Jaws.' I was trapped and could not escape. Not, that
is, until I went to another vc and killed the process.

  The next time my fingers get ahead of my eyes and brain, I'd like to use a
more elegant method of recovering from my mis-typed command. What do I do?

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

[sqlite] Re: Need help on build query.

[Igor Tandetnik]

Artem Yankovskiy 
wrote: 

I like delete some of table.


DROP TABLE tableName;

http://sqlite.org/lang_droptable.html

Igor Tandetnik

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

[sqlite] DRH - Future of SQLite?

[Mikey C]

May I ask what the dev plan is for SQLIte?

For example, out of the missing SQL-92 features, what is likely to be
implemented first to complete the standard?

I would like to see referential integrity natively enforced No.1 and then
support for stored procedures with cached query plans.

Thanks,

Mikey - Big SQLite fan
-- 
View this message in context: 
http://www.nabble.com/DRH---Future-of-SQLite--tf3200150.html#a8884974
Sent from the SQLite mailing list archive at Nabble.com.


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

Re: [sqlite] Select and deletion

[DragonK]

On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


DragonK <[EMAIL PROTECTED]> wrote:
>
> So, as I understand it, this is supported from version 3.3.8 upwards,
right?
>

Correct.



Thanks for  straigthening out the issue! :)




--

D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
...it's only a matter of time...