Re: [sqlite] .dump-n-reload vs. vacuum - which is better?
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
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?
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'
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?
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
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?
[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
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
"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
"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
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
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
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?
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?
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?
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?
>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?
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
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
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
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
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
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
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
"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
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?
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
- 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
> 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
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?
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
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?
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?
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 per connect)
0.651: To create 10 tables with 1000 rows each
0.236: To connect 1000 times (234us per connect)
5.776: To create 100 tables with 1000 rows each
0.224: To connect 1000 times (223us per connect)
58.393: To create 1000 tables with 1000 rows each
0.219: To connect 1000 times (218us per connect)
I guess the parsing is delayed until necessary because what takes the
time is, say, the first select:
A "connect" here is "C=sqlite3.Connection('tmp.db'); C.execute('select a
from t0 limit 1'); C.close()"
0.119: To create 1 tables with 1000 rows each
1.008: To connect 1000 times (1007us per connect)
0.638: To create 10 tables with 1000 rows each
1.264: To connect 1000 times (1263us per connect)
5.801: To create 100 tables with 1000 rows each
4.193: To connect 1000 times (4192us per connect)
58.419: To create 1000 tables with 1000 rows each
32.468: To connect 1000 times (32466us per connect)
So the difference in connect times between a database with 1 table and
10 tables is about 25%, fairly trivial 250us. With 100 tables connect
time is 3ms, worse by a factor of 4 but still not bad. With 1000 tables
the 33ms connect time becomes significant, but I would imagine that 1000
table databases must be pretty unusual.
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] db design options
P Kishor wrote: On 2/23/07, Dennis Cote <[EMAIL PROTECTED]> wrote: P Kishor wrote: > > > Most of the time I am looking at one site, so there is a speed gain by > not plowing through other sites' data. This is what is causing me to > pause before I rush forward. > If you have an index on the readings table by site_id (and perhaps timestamp) sqlite will use the index to go directly to that site's records and will not plow through all the other site's data. Adding a second field, like the timestamp, to the index will let you do fast searches or selections based on time within a single site as well. P.S. I would recommend keeping the integer primary key ID fields. Because sqlite uses them for the rowid (which each record in each table must have anyway) they cost you nothing and make joins more direct and obvious. HTH yes, Dennis, this does help. Thanks. I liked John Stanton's suggestion as well, very elegant, but I think your approach is more scalable, as once the prototype is done and working well, it will be migrated to PostGres. A question -- you say If you have an index on the readings table by site_id (and perhaps timestamp) sqlite will use the index to go directly to that site's records and will not plow through all the other site's data. Adding a second field, like the timestamp, to the index will let you do fast well, site_id is a PRIMARY KEY, so it is automatically indexed. I am not creating another index on it. I am indexing timestamp, but are you suggesting a compound site,timestamp index? Puneet, The column site_id is a primary key in the sites table only. It is an unindexed column in the readings table. I was suggesting an index on site_id in readings table. This will provide an index so sqlite can start at the first site specific record when doing selects from the reading table with a where clause that includes the site. This sill eliminate the table scan of the readings table. create index reading_site on readings(site) select ... from readings where site = ? and ... Yes, I was suggesting a compound index. If you use the following compound index create index reading_site_timestamp on reading(site, timestamp) it will be used for site specific queries and will also let you order and/or condition your search by time. select ... from readings where site = ? and timestamp > ? order by timestamp. This will start at the first record that matches both the site and timestamp criteria and scan only records that match those criteria. Also, if I have two cols a and b, and I create separate indexes on a and b, would it hurt if I also have an additional compound index on a,b? In other words, does SQLite have the smarts to know when to use what? Yes and no. It will take more space to store the additional index, and it also take longer to do inserts since sqlite must update the additional indexes. And, sqlite will only use one of the indexes in any particular query, regardless of how many might be useful. If you do an analyze after the tables are populated, sqlite should pick the best available index for a given query. It is not useful to have an index on b if you never search by that column by itself. It can't be used in conjunction with the index on a to satisfy a query like the second one above, you need a compound index. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using same database with multiple processes
man, 26 02 2007 kl. 07:10 +, skrev Martin Jenkins: > Thomas Dybdahl Ahle wrote: > > Hi, I'm running a project using pysqlite for database support. It's > > awsome. > > Now I have a user who produces this warning: "Warning: You can only > > execute one statement at a time." > > Hard to say without knowing the value of sql in > > cur.execute(sql) It's a simple select > BTW, there is a pysqlite list (this one is for the SQLite, the > underlying C library) but it's a bit quiet. Well, I'll try to ask them then. -- Med venlig hilsen, Best regards, Thomas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] compiling with VC++
RB Smissaert wrote: Did you make the alterations to make the dll VB compatible? Nope. C/C++ all the way. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] PRAGMA temp_store -- is there a default_temp_store?
The SQLite optimization faq [1] mentions a PRAGMA default_temp_store which should set temp_store on a per-database level. However this doesn't seem to be correct--the official docs [2] don't mention default_temp_store and calling "PRAGMA default_temp_store;" always returns nothing (although calling "PRAGMA default_temp_store = MEMORY;" returns without error). If default_temp_store isn't available, is temp_store a per-connection setting or per-database? Thanks, Sam References: [1] Optimization FAQ: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragmas [2] Pragma Docs: http://sqlite.org/pragma.html --- 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] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: trigger with conditions
Thx :) 2007/2/26, Igor Tandetnik <[EMAIL PROTECTED]>: anis chaaba <[EMAIL PROTECTED]> wrote: > Can you tell how can i trigger with conditions such as: > create trigger foo > begin > if (NEW.VALUE = 'something') > insert into tables values.. > endif > END; http://sqlite.org/lang_createtrigger.html create trigger foo after update on someTable when new.value = 'something' begin ... end; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: trigger with conditions
anis chaaba <[EMAIL PROTECTED]> wrote: Can you tell how can i trigger with conditions such as: create trigger foo begin if (NEW.VALUE = 'something') insert into tables values.. endif END; http://sqlite.org/lang_createtrigger.html create trigger foo after update on someTable when new.value = 'something' begin ... end; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] developers mailing list
On Mon, 2007-02-26 at 11:37 +0100, Jakub Ladman wrote:
> > 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
> >
> >
> >
> > Dan.
>
> :-(
>
> This i get when i use makefile which is attached to this mail.
I think you accidentally typed "OPTS += -DNOTCL" instead of "OPTS +=
-DNO_TCL". Have another shot ;)
Dan.
> 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
> mv parse.h parse.h.temp
> awk -f ../sqlite-3.3.13/addopcodes.awk parse.h.temp >parse.h
> cat parse.h ../sqlite-3.3.13/src/vdbe.c |
> awk -f ../sqlite-3.3.13/mkopcodeh.awk >opcodes.h
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/alter.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/analyze.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/attach.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/auth.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/btree.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/build.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/callback.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/complete.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/date.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/delete.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/expr.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/func.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/hash.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/insert.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/loadext.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/main.c
> sort -n -b -k 3 opcodes.h | awk -f ../sqlite-3.3.13/mkopcodec.awk >opcodes.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> opcodes.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/os.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/os_os2.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/os_unix.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/os_win.c
> sh4-pc-linux-uclibc-gcc -Os -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DNOTCL
> -DTHREADSAFE=1 -DHAVE_USLEEP=1 -I. -I../sqlite-3.3.13/src -c
> ../sqlite-3.3.13/src/pager.c
>
Re: [sqlite] developers mailing list
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
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]
-
[sqlite] trigger with conditions
Hello people, Can you tell how can i trigger with conditions such as: create trigger foo begin if (NEW.VALUE = 'something') insert into tables values.. endif END; thanks in advance - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] The best way to delete a column ?
Stef Mientki wrote: I just read it's not possible to delete a column in an existing table. Now what would be the best way to remove the column indirect (from Delphi code), I've never needed to do this so the following are just hints. The special table "sqlite_master" contains the SQL that was used to create the table in the first place. You can erad it like any other table. Just copying a table won't recreate any indexes, constraints, triggers etc which exist on that table. You can create a table from the result of a select. As a first attempt I'd do something like: start transaction get the table schema and that of any triggers etc alter the schema to lose the unwanted column (in Delphi) drop the triggers, indexes etc rename the table you want to change create new table with the new structure copy the data from old table to new table recreate the new triggers, indexes etc end transaction drop the old table vacuum the database NB: I haven't tried or tested the above. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] The best way to delete a column ?
hello, I just read it's not possible to delete a column in an existing table. Now what would be the best way to remove the column indirect (from Delphi code), would it be something like this: start transaction create new table with the same structure, but without the column to delete (how?) copy the data from old table to new table delete the old table rename the new table to the old name vacuum-clean the database end transaction I this the best way, or are there any other ? Sorry I'm just starting SQL, so I just used human SQL. (and therefor a complete code section would be welcome ;-) thanks, Stef Mientki - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How fast is the sqlite connection created?
On Feb 25, 2007, at 10:03 AM, Alex Cheng wrote: I want to know how many time is spent when create a sqlite connection. Is it effeciency? My application creates a connection and close it when access DB everytime, is it OK? Hello Alex, 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. Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite3 in MAC Os
Kirrthana M wrote: Im developing an application using sqlite3 in MAC OS,I just wanted to know wheather sqlite3 can be used in MAC OS. I don't use Mac, but I *think* SQLite is bundled with later versions. If so can the same sqlite3 library and the executable used in windows can be used in MAC OS or a different version has to be used. Pretty unlikely I'd have thought, unless your Mac is x86 based and MacOS has a Windows emulation layer. You'll probably have to build from source unless you can find a site with pre-built binaries. As I said, I don't use Mac so the above is TTBOMK. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
