Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread Dan Kennedy
On Mon, 2007-02-26 at 17:05 -0800, Travis Daygale wrote:
> I'll do that.  I was troubled enough by that bug report and this new testing 
> info to be so motivated.  :-)

This would be handy for Tcl developers.

In case you haven't noticed it yet, the SQL quote() function
in func.c will help with this. It's marked as experimental,
but it will help you get around some fiddly issues with strings
that look like numbers and so on.

Dan.




> If I think about it, the copy method has "filename" as an argument, and a 
> dump method for the tcl driver would need that too.  All other methods I 
> believe do not need to specify the filesystem particulars (i.e. a filename 
> path).
> 
> Is that (partly?) why the copy is not tested and why there is no db1 dump 
> filename method?  
> 
> 
> -T
> 
> 
> [EMAIL PROTECTED] wrote: Travis Daygale  wrote:
> > That is useful to know (i.e. non-testing of the shell).  Thanks.
> > 
> > Does "the core" include the tcl driver (what I use)?  (It must- the driver 
> > is in there and the testing is done with tcl, all of this being partly why 
> > I chose tcl for my app- but I want to make sure I'm not somehow 
> > misunderstanding...)
> 
> Everything except the COPY command is tested.
> 
> > 
> > Then:
> > 
> > How might one do the equivalent of a .dump from a trivial tcl script (and 
> > therefore avoid the shell)?   Sort of a reverse "copy" method... and not 
> > the same as logging (trace).  Is there a way to dump from tcl?  Am I being 
> > stupid here- I haven't seen it...
> > 
> > Based on the testing info, if one could do this, presumably one would have 
> > a (more reliable) dump/backup in a simple script.  (And if it happens that 
> > one's sqlite code is tcl using the tcl driver, as mine is, so much the 
> > better in all kinds of ways including crossplatform considerations.)
> > 
> 
> It would probably not require more than a few lines of TCL code to
> implement a "dump" command as a TCL proc.  Why don't you work something
> up and post it on either the TCLers wiki or on the SQLite wiki or
> both?
> 
> --
> D. Richard Hipp  
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
>  
> -
> Expecting? Get great news right away with email Auto-Check.
> Try the Yahoo! Mail Beta.


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



[sqlite] more questions from the reasonably naïve

2007-02-26 Thread Eric S. Johansson
I think some of these questions fall in the category of "running in the 
dark with scissors".  If it's a simple matter of I need to read 
something, just point me at the appropriate documentation.


I'm trying to get a handle on this cursor thing.  obviously it preserves 
state of some sort but I'm missing what state it is preserving.  For 
example, do I need a different cursor for each table in the database? 
if it helps any, multiple tables will be in play at the same time.


Record locking.  I did read http://www.sqlite.org/lockingv3.html  I do a 
fair number of read/modify/write cycles on individual rows in all of the 
tables from multiple processes.  if I interpret the documentation 
correctly, I need to create a transaction and when I am done changing 
things, "commit" which is really just turning on auto commit.


Committing.  how often should one commit?  Is it even necessary if you 
close?  What happens in case of a crash?  Does journaling save one's 
bacon?  If the change has not been "committed" will be visible to other 
processes using the same table?


Many thanks for your answers and patience.

--
Speech-recognition in use.  It makes mistakes, I correct some.

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



RE: [sqlite] How fast is the sqlite connection created?

2007-02-26 Thread Samuel R. Neff

I ran some tests and received fairly drastic results.  Our schema has 67
tables right now (once we add in the history tables, it'll have about double
that) and 116 indexes, excluding the automatic primary key indexes.

I ran 1,000 simple select statements

SELECT COUNT(*) FROM USERS;

and the USERS table is empty (actually, the whole db is empty, only
structure created for the tests).

One statement per connection  : 2906.3 ms
One connection for all statements :   62.5 ms

Some of this performance gain is probably related to caching data and query
plan, not just opening the connection, but still that caching is connection
related and is lost when you close the connection so it's a very real-world
valid comparison.

Best regards,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 26, 2007 12:53 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?

Samuel R. Neff wrote:
> Thank you for the testing and information.

;)

> When I have time to run some tests using our actual schema (120+ tables,
> several hundred indexes) I'll post back here in case others are interested
> in our results.  From your tests it looks like more complex schemas
probably
> take more time to parse (as would be expected) so a real-world schema
might
> take longer than a test schema.

Please do, the more info the merrier. I wasn't sure if anyone would find 
it useful but thought I'd seed the list for future reference.

The schema was pretty trivial - all tables looked like

"tXXX(a INT, b INT, c FLOAT, d TEXT)"

and to add the indexes I just added a UNIQUE constraint to a, b & d. It 
would be good to have some results from a real database.

Best Regards,

Martin


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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



Re: [sqlite] How to read next record of a table after a query 'select from where'

2007-02-26 Thread Alex Cheng

I am using pysqlite2 to access sqlite DB.

from pysqlite2 import dbapi2 as sqlite
conn = sqlite.connect("db1.db")
cur = conn.cursor()
cur.execute()
cur.fetchone() # get one record
cur.fetchone() # get next record
conn.close()


2007/2/20, Adriano <[EMAIL PROTECTED]>:


"SELECT * FROM mytable WHERE NameField = '"" & Name & 
Set r= db.execute(cmd)
if i'd like to read next record in the same table how to do ?
I use last version of sqlite avaible.
Thanks
Adriano


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
powered by python


Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread Travis Daygale
I'll do that.  I was troubled enough by that bug report and this new testing 
info to be so motivated.  :-)

If I think about it, the copy method has "filename" as an argument, and a dump 
method for the tcl driver would need that too.  All other methods I believe do 
not need to specify the filesystem particulars (i.e. a filename path).

Is that (partly?) why the copy is not tested and why there is no db1 dump 
filename method?  


-T


[EMAIL PROTECTED] wrote: Travis Daygale  wrote:
> That is useful to know (i.e. non-testing of the shell).  Thanks.
> 
> Does "the core" include the tcl driver (what I use)?  (It must- the driver is 
> in there and the testing is done with tcl, all of this being partly why I 
> chose tcl for my app- but I want to make sure I'm not somehow 
> misunderstanding...)

Everything except the COPY command is tested.

> 
> Then:
> 
> How might one do the equivalent of a .dump from a trivial tcl script (and 
> therefore avoid the shell)?   Sort of a reverse "copy" method... and not the 
> same as logging (trace).  Is there a way to dump from tcl?  Am I being stupid 
> here- I haven't seen it...
> 
> Based on the testing info, if one could do this, presumably one would have a 
> (more reliable) dump/backup in a simple script.  (And if it happens that 
> one's sqlite code is tcl using the tcl driver, as mine is, so much the better 
> in all kinds of ways including crossplatform considerations.)
> 

It would probably not require more than a few lines of TCL code to
implement a "dump" command as a TCL proc.  Why don't you work something
up and post it on either the TCLers wiki or on the SQLite wiki or
both?

--
D. Richard Hipp  


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



 
-
Expecting? Get great news right away with email Auto-Check.
Try the Yahoo! Mail Beta.

Re: [sqlite] Bug wit length() SQL Query function

2007-02-26 Thread venkat akella

Thanks Rechard and Gerry for the quick reply. I will use that alternative
solution.

Is there any easy way to get the size of data stored in a table, size of a
row  usig Sqlite C APIs?

Venkat.


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


"venkat akella" <[EMAIL PROTECTED]> wrote:
> Hi
>
> lenght() function in SQL Query is not behaving properly.  I have
> explained this below with an example.
>
>Select length(col1) + length(col2) + length(col3) + length(col4) from
> TestTable;
>
> For example, there are four columns in a table and in a row three
> columns (col1, col2 and col3) have propervalues inserted. col4 has NULL.
> When I execute the above query on that table, then it doesnt return any
> thing. Basically if I just do "select length(col1) + length(col2) +
> length(col3) from TestTable" it properly returns the sum of the lengths
of
> three columns. But when I include length(col4) in the sum which has
NULL,
> then it doesnt return anything. I was expecting it to return the sum of
> lengths of col1, col2 and col3 even after including the lenght(col4) in
the
> sum.
>
> That means, even if one column is NULL, then its effecting the whole
query.
>
> Above explained behaviour is same irrespective of using command line or
C
> API. I am using sqlite-3.3.8 .
>
> Can some one please comeabck on this.
>

length(NULL) is NULL, and +NULL is NULL.  So if any of
hour four columns contains a NULL, the result is NULL.  Strange though
this may seem, it is what SQL is suppose to do.

You can work around the problem by doing:

  length(coalesce(col1,'')) + length(coalesce(col2,'')) +...

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread Gerry Snyder

[EMAIL PROTECTED] wrote:



It would probably not require more than a few lines of TCL code to
implement a "dump" command as a TCL proc.


I know that copying from one database to another is not the same as 
dumping, but the following might be a useful starting point. It gets the 
filenames of an existing version 2 SQLite file and a desired version 3 
file, and does the copy.


I am placing it in the public domain, if anyone wants to use it. It has 
had a little testing on small files, but it seems to work. I hope it is 
small enough that posting here is not a problem for anyone.


Gerry Snyder


# ***
# Convert an SQLite data-base file from V2 to V3
# ***
proc two2three {} {
  global mainlabel
  if {[set dbfilein [tk_getOpenFile]] == {} } {
return
  }
  if {[set dbfileout [tk_getSaveFile]] == {} } {
return
  }
 sqlite sqin $dbfilein
 sqlite3 sqout $dbfileout
 sqout eval begin
# Duplicate schema
  sqin eval {SELECT sql FROM sqlite_master WHERE sql NOT NULL} {} {
sqout eval $sql
  }
# Copy data
# For each table...
  sqin eval {SELECT name FROM sqlite_master WHERE type='table'} {} {
set fieldnamescomma {}
#   get field names and values
sqin eval "SELECT * FROM $name" fieldnames {
# but use them only if table is non-empty
  if {[llength $fieldnames(*)] > 0} {
# Now $fieldnames(*) is a list of the field names
#   and the rest of the $fieldnames array is values
#--set up params for insert (first time thru)
if {[string length $fieldnamescomma] == 0} {
  set fieldnamescomma [join $fieldnames(*) ,]
  set valuevarscomma "\$fieldnames([join $fieldnames(*) 
),\$fieldnames(])"
  set sqlstmnt "insert into $name\($fieldnamescomma) 
values($valuevarscomma)"

}
#   Now sqltrmnt is something like "insert into 
table1(field1,field2) values($fieldnames(field1),$fieldnames(field2))"

sqout eval $sqlstmnt
  }
}
  }
sqout eval commit
}



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



Re: [sqlite] Bug wit length() SQL Query function

2007-02-26 Thread Dennis Cote

venkat akella wrote:


   lenght() function in SQL Query is not behaving properly.  I have
explained this below with an example.

  Select length(col1) + length(col2) + length(col3) + length(col4) from
TestTable;

   For example, there are four columns in a table and in a row three
columns (col1, col2 and col3) have propervalues inserted. col4 has NULL.
When I execute the above query on that table, then it doesnt return any
thing. Basically if I just do "select length(col1) + length(col2) +
length(col3) from TestTable" it properly returns the sum of the 
lengths of

three columns. But when I include length(col4) in the sum which has NULL,
then it doesnt return anything. I was expecting it to return the sum of
lengths of col1, col2 and col3 even after including the lenght(col4) 
in the

sum.

That means, even if one column is NULL, then its effecting the whole 
query.


Above explained behaviour is same irrespective of using command line or C
API. I am using sqlite-3.3.8 .


The length function is behaving as it should, your expectations are 
wrong :-)


The length of a NULL value is unknown or NULL. The sum of anything and a 
NULL value is also NULL or unknown.


You need to subsitute a known string for the NULL values  from the table 
so you  can get the desired result.


   select length(coalesce(col1, '')) + length(coalesce(col2, '') + 

The coalesce function returns the first non NULL value in its arguments. 
If the column is NULL it wil return the empty string (an known string of 
length zero).


HTH
Dennis Cote


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



Re: [sqlite] Question about LIKE

2007-02-26 Thread drh
"Doug" <[EMAIL PROTECTED]> wrote:
> I appologize if this has been answered--haven't found it in the docs, wiki
> or mail archive.
> 
> I have a text column with the following text value in it:
> c:\Temp\Temp1\Audit1.log
> 
> I'm trying to figure out how LIKE works.  When using the following patterns,
> I get the following results:
> 
> '%audit%' match
> '%\audit%'  non-match
> '%\\audit%' non-match (need a different escape sequence??)
> '%1\audit%'  match (guess don't need an escape sequence after all)
> '%temp1%'   non-match -- huh?
> '%Temp1%'   non-match
> '%\Temp1%'   non-match
> '%Temp1\%'   non-match
> '%p\Temp1%'  match -- starting to think it can't start or end with \ (except
> temp1 case above)
> '%p\Temp1\%' match -- wrong again
> '%p\Temp1\a%' match
> 
> What I'd like to do is let someone enter an arbitrary piece of a filename
> path and find matches.  I'll start experimenting with GLOB, but I expected
> LIKE to be able to do this.
> 
> Can someone help me understand the finer points of LIKE?
> 

When I run these I get a match on every one except '%\\audit%'.
I suspect that you have bugs in whatever program you are using
to run your tests.  Likely the backslashes are being interpreted
as an escape sequence by whatever programming language you are
using before the SQL ever gets to SQLite.

My test script is below.  Try running it using the SQLite shell
and see if you don't get the same answer as I do:

 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%audit%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%\audit%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%\\audit%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%1\audit%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%temp1%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%Temp1%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%\Temp1%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%Temp1\%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%p\Temp1%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%p\Temp1\%';
 SELECT 'c:\Temp\Temp1\Audit1.log' LIKE '%p\Temp1\a%';

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


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



Re: [sqlite] Bug wit length() SQL Query function

2007-02-26 Thread drh
"venkat akella" <[EMAIL PROTECTED]> wrote:
> Hi
> 
> lenght() function in SQL Query is not behaving properly.  I have
> explained this below with an example.
> 
>Select length(col1) + length(col2) + length(col3) + length(col4) from
> TestTable;
> 
> For example, there are four columns in a table and in a row three
> columns (col1, col2 and col3) have propervalues inserted. col4 has NULL.
> When I execute the above query on that table, then it doesnt return any
> thing. Basically if I just do "select length(col1) + length(col2) +
> length(col3) from TestTable" it properly returns the sum of the lengths of
> three columns. But when I include length(col4) in the sum which has NULL,
> then it doesnt return anything. I was expecting it to return the sum of
> lengths of col1, col2 and col3 even after including the lenght(col4) in the
> sum.
> 
> That means, even if one column is NULL, then its effecting the whole query.
> 
> Above explained behaviour is same irrespective of using command line or C
> API. I am using sqlite-3.3.8 .
> 
> Can some one please comeabck on this.
> 

length(NULL) is NULL, and +NULL is NULL.  So if any of
hour four columns contains a NULL, the result is NULL.  Strange though
this may seem, it is what SQL is suppose to do.

You can work around the problem by doing:

   length(coalesce(col1,'')) + length(coalesce(col2,'')) +...

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


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



Re: [sqlite] Bug wit length() SQL Query function

2007-02-26 Thread Gerry Snyder

venkat akella wrote:



 even if one column is NULL, then its effecting the whole query.




If you want NULL treated as zero numerically for col4, use 
coalesce(col4,0). Or you could find a way to get 0 into the column 
rather than NULL before the expression is evaluated.


HTH,

Gerry

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



[sqlite] Question about LIKE

2007-02-26 Thread Doug
I appologize if this has been answered--haven't found it in the docs, wiki
or mail archive.

I have a text column with the following text value in it:
c:\Temp\Temp1\Audit1.log

I'm trying to figure out how LIKE works.  When using the following patterns,
I get the following results:

'%audit%'   match
'%\audit%'  non-match
'%\\audit%' non-match (need a different escape sequence??)
'%1\audit%'  match (guess don't need an escape sequence after all)
'%temp1%'   non-match -- huh?
'%Temp1%'   non-match
'%\Temp1%'   non-match
'%Temp1\%'   non-match
'%p\Temp1%'  match -- starting to think it can't start or end with \ (except
temp1 case above)
'%p\Temp1\%' match -- wrong again
'%p\Temp1\a%' match

What I'd like to do is let someone enter an arbitrary piece of a filename
path and find matches.  I'll start experimenting with GLOB, but I expected
LIKE to be able to do this.

Can someone help me understand the finer points of LIKE?

Thanks
Doug


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



[sqlite] Bug wit length() SQL Query function

2007-02-26 Thread venkat akella

Hi

   lenght() function in SQL Query is not behaving properly.  I have
explained this below with an example.

  Select length(col1) + length(col2) + length(col3) + length(col4) from
TestTable;

   For example, there are four columns in a table and in a row three
columns (col1, col2 and col3) have propervalues inserted. col4 has NULL.
When I execute the above query on that table, then it doesnt return any
thing. Basically if I just do "select length(col1) + length(col2) +
length(col3) from TestTable" it properly returns the sum of the lengths of
three columns. But when I include length(col4) in the sum which has NULL,
then it doesnt return anything. I was expecting it to return the sum of
lengths of col1, col2 and col3 even after including the lenght(col4) in the
sum.

That means, even if one column is NULL, then its effecting the whole query.

Above explained behaviour is same irrespective of using command line or C
API. I am using sqlite-3.3.8 .

Can some one please comeabck on this.

Thanks for your time.
Venkata S Akella


Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread drh
Travis Daygale <[EMAIL PROTECTED]> wrote:
> That is useful to know (i.e. non-testing of the shell).  Thanks.
> 
> Does "the core" include the tcl driver (what I use)?  (It must- the driver is 
> in there and the testing is done with tcl, all of this being partly why I 
> chose tcl for my app- but I want to make sure I'm not somehow 
> misunderstanding...)

Everything except the COPY command is tested.

> 
> Then:
> 
> How might one do the equivalent of a .dump from a trivial tcl script (and 
> therefore avoid the shell)?   Sort of a reverse "copy" method... and not the 
> same as logging (trace).  Is there a way to dump from tcl?  Am I being stupid 
> here- I haven't seen it...
> 
> Based on the testing info, if one could do this, presumably one would have a 
> (more reliable) dump/backup in a simple script.  (And if it happens that 
> one's sqlite code is tcl using the tcl driver, as mine is, so much the better 
> in all kinds of ways including crossplatform considerations.)
> 

It would probably not require more than a few lines of TCL code to
implement a "dump" command as a TCL proc.  Why don't you work something
up and post it on either the TCLers wiki or on the SQLite wiki or
both?

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


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



Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread Travis Daygale
That is useful to know (i.e. non-testing of the shell).  Thanks.

Does "the core" include the tcl driver (what I use)?  (It must- the driver is 
in there and the testing is done with tcl, all of this being partly why I chose 
tcl for my app- but I want to make sure I'm not somehow misunderstanding...)

Then:

How might one do the equivalent of a .dump from a trivial tcl script (and 
therefore avoid the shell)?   Sort of a reverse "copy" method... and not the 
same as logging (trace).  Is there a way to dump from tcl?  Am I being stupid 
here- I haven't seen it...

Based on the testing info, if one could do this, presumably one would have a 
(more reliable) dump/backup in a simple script.  (And if it happens that one's 
sqlite code is tcl using the tcl driver, as mine is, so much the better in all 
kinds of ways including crossplatform considerations.)



[EMAIL PROTECTED] wrote: Travis Daygale  wrote:
> Tangentially, but hopefully in keeping with this thread, for the 3.3.9 
> release, the change log shows:
> Fixed the ".dump" command in the command-line shell to show indices, triggers 
> and views again.
> 
> There was apparently a bug there. I was unaffected but _apparently_ would 
> have been hurt had I not skipped some versions.  (I have A LOT of important 
> triggers for foreign key handling.)
> 
> Should we feel insecure about dump?  I know sqlite3 is in development, but 
> overall, is the .dump command usually reliable (is there anything about the 
> relevent code that might make the bug reports not the full story)?  Are there 
> recommended ways of doing backups that would be more reliable?
> 

Good point.

Everything in the SQLite core, including VACUUM, is carefully tested
before each release.  But this is not true of things in the shell.
So one can expect the core to be much more reliable than the shell.
--
D. Richard Hipp  


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



 
-
Don't get soaked.  Take a quick peak at the forecast 
 with theYahoo! Search weather shortcut.

Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread drh
Travis Daygale <[EMAIL PROTECTED]> wrote:
> Tangentially, but hopefully in keeping with this thread, for the 3.3.9 
> release, the change log shows:
> Fixed the ".dump" command in the command-line shell to show indices, triggers 
> and views again.
> 
> There was apparently a bug there. I was unaffected but _apparently_ would 
> have been hurt had I not skipped some versions.  (I have A LOT of important 
> triggers for foreign key handling.)
> 
> Should we feel insecure about dump?  I know sqlite3 is in development, but 
> overall, is the .dump command usually reliable (is there anything about the 
> relevent code that might make the bug reports not the full story)?  Are there 
> recommended ways of doing backups that would be more reliable?
> 

Good point.

Everything in the SQLite core, including VACUUM, is carefully tested
before each release.  But this is not true of things in the shell.
So one can expect the core to be much more reliable than the shell.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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



RE: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread James Dennett
>From what I know, it seems plausible that a filesystem snapshot should
provide a robust backup for an SQLite3 database; most modern OS's have
some way to atomically grab an image of a filesystem (LVM on Linux, VSS
on Windows, fssnap on Solaris, etc.).  I'm no SQLite expert though, so
I'd suggest you wait to hear from someone who is before you expend a lot
of energy checking out this option.

(Another alternative often suggested is to start a read transaction and
then backup the database file before ending that transaction, to ensure
that writers don't change the file while you're backing it up.)

-- James

> -Original Message-
> From: Travis Daygale [mailto:[EMAIL PROTECTED]
> Sent: Monday, February 26, 2007 1:05 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
> 
> Tangentially, but hopefully in keeping with this thread, for the 3.3.9
> release, the change log shows:
> Fixed the ".dump" command in the command-line shell to show indices,
> triggers and views again.
> 
> There was apparently a bug there. I was unaffected but _apparently_
would
> have been hurt had I not skipped some versions.  (I have A LOT of
> important triggers for foreign key handling.)
> 
> Should we feel insecure about dump?  I know sqlite3 is in development,
but
> overall, is the .dump command usually reliable (is there anything
about
> the relevent code that might make the bug reports not the full story)?
> Are there recommended ways of doing backups that would be more
reliable?
> 
> 
> Thanks in advance,
> 
> -T
> 
> 
> mr sql <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote: mr sql  wrote:
> > I found out that doing a:
> >
> > sqlite3 my.db .dump > mydump.sql
> > rm my.db
> > sqlite3 my.db < mydump.sql
> >
> > is faster than doing a VACUUM on my.db.
> >
> > Are there any advantages of doing one over the other?  My goal is to
> keep the database's structures in their best shape for performance and
> integrity.  So I want to run this process every once in a while.
> >
> 
> Have you tried running VACUUM out of the latest code in CVS?
> It should be faster and it should do a better job of defragmenting
> the database.
> --
> D. Richard Hipp
> Not sure, I am using 3.3.13 on winxp sp2, using the downloadable
> (precompiled) sqlite3.exe and sqlite3.dll.  Is there any 3.3.14 on the
> way?  For some reason, when I do my own compilations, sqlite3 (both
the
> exe and dll) crash randomly so I prefer to use the precompiled
versions.
> 
> jp
> 
> 
> -
> 8:00? 8:25? 8:40?  Find a flick in no time
>  with theYahoo! Search movie showtime shortcut.
> 
> 
> -
> Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.

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



Re: [sqlite] .dump-n-reload vs. vacuum - which is better?

2007-02-26 Thread Travis Daygale
Tangentially, but hopefully in keeping with this thread, for the 3.3.9 release, 
the change log shows:
Fixed the ".dump" command in the command-line shell to show indices, triggers 
and views again.

There was apparently a bug there. I was unaffected but _apparently_ would have 
been hurt had I not skipped some versions.  (I have A LOT of important triggers 
for foreign key handling.)

Should we feel insecure about dump?  I know sqlite3 is in development, but 
overall, is the .dump command usually reliable (is there anything about the 
relevent code that might make the bug reports not the full story)?  Are there 
recommended ways of doing backups that would be more reliable?


Thanks in advance,

-T


mr sql <[EMAIL PROTECTED]> wrote: 
[EMAIL PROTECTED] wrote: mr sql  wrote:
> I found out that doing a: 
> 
> sqlite3 my.db .dump > mydump.sql 
> rm my.db
> sqlite3 my.db < mydump.sql
> 
> is faster than doing a VACUUM on my.db.
> 
> Are there any advantages of doing one over the other?  My goal is to keep the 
> database's structures in their best shape for performance and integrity.  So 
> I want to run this process every once in a while.
> 

Have you tried running VACUUM out of the latest code in CVS?
It should be faster and it should do a better job of defragmenting
the database.
--
D. Richard Hipp  
Not sure, I am using 3.3.13 on winxp sp2, using the downloadable (precompiled) 
sqlite3.exe and sqlite3.dll.  Is there any 3.3.14 on the way?  For some reason, 
when I do my own compilations, sqlite3 (both the exe and dll) crash randomly so 
I prefer to use the precompiled versions.

jp

 
-
8:00? 8:25? 8:40?  Find a flick in no time
 with theYahoo! Search movie showtime shortcut.

 
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.

Re: [sqlite] developers mailing list

2007-02-26 Thread Jakub Ladman
Ok, now is it linked together, i will try, if it is functional.

Jakub Ladman



Dne pondělí 26 únor 2007 20:23 Martin Jenkins napsal(a):
> Jakub Ladman wrote:
> > but after correction of this i get:
> >
> > libsqlite3.a   -lpthread
> > libsqlite3.a(os_unix.o): In function `sqlite3UnixDlopen':
> > os_unix.c:(.text+0x848): undefined reference to `dlopen'
> > libsqlite3.a(os_unix.o): In function `sqlite3UnixDlsym':
> > os_unix.c:(.text+0x85c): undefined reference to `dlsym'
> > libsqlite3.a(os_unix.o): In function `sqlite3UnixDlclose':
> > os_unix.c:(.text+0x870): undefined reference to `dlclose'
> > collect2: ld returned 1 exit status
> > make: *** [sqlite3] Error 1
> > [EMAIL PROTECTED] ~/src/sqlite-3.3.13 $
>
> You're nearly there. The compile has completed, and it's failed at the
> link stage. You need to add "-ldl" to the end of the recipe so the
> linker can find dlopen et al.
>
> Martin
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

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



Re: [sqlite] developers mailing list

2007-02-26 Thread Jakub Ladman
more accurately

ranlib libsqlite3.a
sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNO_TCL 
-DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src  -o 
sqlite3 ../sqlite-3.3.13/src/shell.c \
libsqlite3.a   -lpthread
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlopen':
os_unix.c:(.text+0x848): undefined reference to `dlopen'
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlsym':
os_unix.c:(.text+0x85c): undefined reference to `dlsym'
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlclose':
os_unix.c:(.text+0x870): undefined reference to `dlclose'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1
[EMAIL PROTECTED] ~/src/sqlite-3.3.13 $   

Jakub Ladman

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



Re: [sqlite] developers mailing list

2007-02-26 Thread Martin Jenkins

Jakub Ladman wrote:

but after correction of this i get:

libsqlite3.a   -lpthread
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlopen':
os_unix.c:(.text+0x848): undefined reference to `dlopen'
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlsym':
os_unix.c:(.text+0x85c): undefined reference to `dlsym'
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlclose':
os_unix.c:(.text+0x870): undefined reference to `dlclose'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1
[EMAIL PROTECTED] ~/src/sqlite-3.3.13 $   


You're nearly there. The compile has completed, and it's failed at the 
link stage. You need to add "-ldl" to the end of the recipe so the 
linker can find dlopen et al.


Martin

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



Re: [sqlite] Re: sqlite / tcl syntax help

2007-02-26 Thread Jeff Godfrey

Michael / Richard / Dennis,

Thanks for the additional input.  Problem fixed...

Jeff

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



Re: [sqlite] Re: sqlite / tcl syntax help

2007-02-26 Thread Dennis Cote

Jeff Godfrey wrote:
So, how can I get the correct column types returned for all 
columns, while at the same time properly handle column 
names containing spaces?



  

Thanks for any insight.



  

Jeff



Answering my own post, I just found that the following works as expected...

set dataType [$db onecolumn "select typeof(\"$colName\") from $table"]

I'm OK with that, but if anyone has a suggestion for a more preferred syntax,
I'm listening.

Also, should space-separated column names "just work" as an argument to
"typeof".  That is, is this a bug?

Thanks,


Jeff

  
No bug here, the typeof function takes an expression as its argument. If 
you want to pass a column name that contains special characters as the 
expression you need to use the SQL standard double quotes to surround 
that column name identifier. SQLite also accepts a MS Access compatible 
extension that allows identifiers to be enclosed in square brackets.


   'string literal'
   "quoted identifier"
   [extension quoted identifier]

HTH
Dennis Cote

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



Re: [sqlite] developers mailing list

2007-02-26 Thread Jakub Ladman
Dne pondělí 26 únor 2007 12:25 Gunnar Roth napsal(a):
> Jakub Ladman schrieb:
> >> It's seems a bit strange to me that Makefile.linux-gcc includes tcl in
> >> the build by default, but it does. Maybe that should change...
> >>
> >> If you add "-DNO_TCL" to the OPTS variable in Makefile.linux-gcc this
> >> error should go away. i.e. add the following line somewhere after the
> >> "OPTS = -DNDEBUG" bit:
> >>
> >> OPTS += -DNO_TCL
Yes, i am idiot!!

but after correction of this i get:

libsqlite3.a   -lpthread
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlopen':
os_unix.c:(.text+0x848): undefined reference to `dlopen'
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlsym':
os_unix.c:(.text+0x85c): undefined reference to `dlsym'
libsqlite3.a(os_unix.o): In function `sqlite3UnixDlclose':
os_unix.c:(.text+0x870): undefined reference to `dlclose'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1
[EMAIL PROTECTED] ~/src/sqlite-3.3.13 $   

> >>
> >>
> >>
> >> Dan.
> >>
> > :-(
> >
> > This i get when i use makefile which is attached to this mail.
> >
> > Jakub Ladman
> >
> > [EMAIL PROTECTED] ~/src/sqlite-3.3.13 $ make -f mymakefile
> > sed -e s/--VERS--/`cat ../sqlite-3.3.13/VERSION`/ \
> > -e s/--VERSION-NUMBER--/`cat ../sqlite-3.3.13/VERSION |
> > sed 's/[^0-9]/ /g' | awk '{printf "%d%03d%03d",$1,$2,$3}'`/ \
> >  ../sqlite-3.3.13/src/sqlite.h.in >sqlite3.h
> > gcc -g -O2 -o lemon ../sqlite-3.3.13/tool/lemon.c
> > cp ../sqlite-3.3.13/tool/lempar.c .
> > cp ../sqlite-3.3.13/src/parse.y .
> > ./lemon -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL parse.y
>
> NOTCL != NO_TCL
>
> you see?
>
> regards,
> gunnar
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

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



Re: [sqlite] sqlite / tcl syntax help

2007-02-26 Thread drh
"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> Hi All,
> 
> I'm trying to determine the datatype of a given column using Tcl and the 
> following code snippet...
> 
> set dataType [$db onecolumn "select typeof($colName) from $table"]
> 

Try this:

   set quotedColName [string subst {" ""} $colName]
   set dataType [$db onecolumn "SELECT typeof(\"$quotedColName\") FROM $table"]

Note you will also need to quote $table if it contains spaces or
special characters.

You cannot use curly brackes, like this:

   set datatype [$db onecolumn {SELECT typeof($colName) FROM $table}]

The reason is that TCL variable names are only allowed in places 
where you can put a host parameter "?".  In face, TCL variable
names are just another way of writing SQL host parameters.  Before
evaluating each statement, TCL checks for host parameter names and
binds the values of corresponding TCL variables to those parameters.

The TCL interface is working correctly and as designed.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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



Re: [sqlite] sqlite / tcl syntax help

2007-02-26 Thread Michael Schlenker

Jeff Godfrey schrieb:


- Original Message - From: "Michael Schlenker" <[EMAIL PROTECTED]>


set dataType [$db onecolumn {select typeof($colName) from $table}]

should do what you want. Note the {} instead of the "", which prevent 
early substitution, so sqlite can use the $colName as a bind variable.


Michael


Hi Michael,

Yeah, I agree that makes sense from a Tcl standpoint.  In fact, that's 
exactly what I started with.  Unfortunately, it doesn't work as expected 
and just returns the following:


Error: near "$table": syntax error

So, the curly-braces are preventing the Tcl parser from substituting the 
vars (as they should), but then SQLite doesn't substitute the $table var 
for some reason.  I guess I'm a bit unclear on exactly *what* SQLite is 
capable of substituting with regard to Tcl variables. It only seems 
capable of substituting variables in certain cases, or certain 
locations, or ???  I'm not sure.


Looks like it does not allow variable substitution for identifiers, just 
for literal values. The docs for db eval state it.


As a workaround try this:

set datatype [db eval onecolumn \
[format {select typeof($colName) from %s} $table]]


--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

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



Re: [sqlite] How fast is the sqlite connection created?

2007-02-26 Thread Martin Jenkins

Samuel R. Neff wrote:

Thank you for the testing and information.


;)


When I have time to run some tests using our actual schema (120+ tables,
several hundred indexes) I'll post back here in case others are interested
in our results.  From your tests it looks like more complex schemas probably
take more time to parse (as would be expected) so a real-world schema might
take longer than a test schema.


Please do, the more info the merrier. I wasn't sure if anyone would find 
it useful but thought I'd seed the list for future reference.


The schema was pretty trivial - all tables looked like

"tXXX(a INT, b INT, c FLOAT, d TEXT)"

and to add the indexes I just added a UNIQUE constraint to a, b & d. It 
would be good to have some results from a real database.


Best Regards,

Martin

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



Re: [sqlite] sqlite / tcl syntax help

2007-02-26 Thread Jeff Godfrey


- Original Message - 
From: "Michael Schlenker" <[EMAIL PROTECTED]>



set dataType [$db onecolumn {select typeof($colName) from $table}]

should do what you want. Note the {} instead of the "", which 
prevent early substitution, so sqlite can use the $colName as a bind 
variable.


Michael


Hi Michael,

Yeah, I agree that makes sense from a Tcl standpoint.  In fact, that's 
exactly what I started with.  Unfortunately, it doesn't work as 
expected and just returns the following:


Error: near "$table": syntax error

So, the curly-braces are preventing the Tcl parser from substituting 
the vars (as they should), but then SQLite doesn't substitute the 
$table var for some reason.  I guess I'm a bit unclear on exactly 
*what* SQLite is capable of substituting with regard to Tcl variables. 
It only seems capable of substituting variables in certain cases, or 
certain locations, or ???  I'm not sure.


I've found that if my table name is variable, I need for Tcl to 
substitute it instead of SQLite - which lead me to my original 
problem.  Is SQLite's substitution working as expected?  If so, can 
someone point to more documentation regarding SQLite's Tcl var 
substitution?


Thanks,

Jeff 



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



[sqlite] Re: sqlite / tcl syntax help

2007-02-26 Thread Jeff Godfrey
> So, how can I get the correct column types returned for all 
> columns, while at the same time properly handle column 
> names containing spaces?

> Thanks for any insight.

> Jeff

Answering my own post, I just found that the following works as expected...

set dataType [$db onecolumn "select typeof(\"$colName\") from $table"]

I'm OK with that, but if anyone has a suggestion for a more preferred syntax,
I'm listening.

Also, should space-separated column names "just work" as an argument to
"typeof".  That is, is this a bug?

Thanks,


Jeff


Re: [sqlite] sqlite / tcl syntax help

2007-02-26 Thread Michael Schlenker

Jeff Godfrey schrieb:

Hi All,

I'm trying to determine the datatype of a given column using Tcl and the 
following code snippet...

set dataType [$db onecolumn "select typeof($colName) from $table"]


Read about the Tcl substitution rules (
http://www.tcl.tk/man/tcl8.4/TclCmd/Tcl.htm#M11 , [4] and [5])
especially and SQLites quoting rules...

set dataType [$db onecolumn {select typeof($colName) from $table}]

should do what you want. Note the {} instead of the "", which prevent 
early substitution, so sqlite can use the $colName as a bind variable.


Michael

--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

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



RE: [sqlite] How fast is the sqlite connection created?

2007-02-26 Thread Samuel R. Neff
Martin,

Thank you for the testing and information.

We're just starting to use SQLite and are using SQLite.NET.  This library
doesn't support connection pooling and we've been discussing whether to
implement connection pooling external to the library.  Your post certainly
makes it look worthwhile.  

When I have time to run some tests using our actual schema (120+ tables,
several hundred indexes) I'll post back here in case others are interested
in our results.  From your tests it looks like more complex schemas probably
take more time to parse (as would be expected) so a real-world schema might
take longer than a test schema.

Best regards,

Sam



---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 26, 2007 10:58 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How fast is the sqlite connection created?

Martin Jenkins wrote:

> So the difference in connect times between a database with 1 table and 
> 10 tables is ...

It appears that adding indexes (and triggers?) increases the time at 
about the same rate as adding tables. That is a connect/first select to 
a database with 1 table and 3 indexes takes about as long it does to a 
database with 4 tables.

Martin


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



[sqlite] sqlite / tcl syntax help

2007-02-26 Thread Jeff Godfrey
Hi All,

I'm trying to determine the datatype of a given column using Tcl and the 
following code snippet...

set dataType [$db onecolumn "select typeof($colName) from $table"]

This works correctly as long as $colName (the name of the current column) 
doesn't contain a space.  When the column name contains a space (such as 
"ASSEMBLY NUMBER"), the above code fails with:

Error: near "NUMBER": syntax error

Obviously, the "NUMBER" text depends on the column name.  Now, that's easily 
fixed by wrapping the variable name in single-quotes, like this:

set dataType [$db onecolumn "select typeof('$colName') from $table"]

While that fixes the issue with the space, the single-quote wrapper causes the 
"typeof" function to return "text" for all column types, which is incorrect.  

So, how can I get the correct column types returned for all columns, while at 
the same time properly handle column names containing spaces?

Thanks for any insight.

Jeff

Re: [sqlite] How fast is the sqlite connection created?

2007-02-26 Thread Martin Jenkins

Martin Jenkins wrote:

So the difference in connect times between a database with 1 table and 
10 tables is ...


It appears that adding indexes (and triggers?) increases the time at 
about the same rate as adding tables. That is a connect/first select to 
a database with 1 table and 3 indexes takes about as long it does to a 
database with 4 tables.


Martin

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



Re: [sqlite] How fast is the sqlite connection created?

2007-02-26 Thread Martin Jenkins

Peter van Dijk wrote:
every time you open an sqlite database file, the sqlite library has to 
parse all table structures. It is much better to keep your 
connection/handle open for longer periods of time.


On my XP box it takes about 220us to connect to an SQLite database from 
Python, whether there is 1 table or 1000.


A "connect" here is "C=sqlite3.Connection('tmp.db'); C.close()"

   0.121: To create 1 tables with 1000 rows each
   0.223: To connect 1000 times (222us p