[sqlite] step back
sqlite3_step() is great for scrolling forward through a result set. Is there a way to scroll backwards? If not, did anyone try implementing it? (I guess that the indexes would need backward pointers in order to do it.) Clive - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Files opened by sqlite
--- Richard Klein <[EMAIL PROTECTED]> wrote: > My understanding is that, even with all the above > features _OMIT_ed, SQLite 3 is still about twice > the size of SQLite 2. With the majority of options omitted, I was able to build a functioning 180,568 byte x86 linux sqlite3 binary. It is linked with a shared libc.so.6. Even if the generated RISC code (ARM or MIPS?) is twice as big as x86, it should be well under 400K. Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. http://mobile.yahoo.com/go?refer=1GNXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Files opened by sqlite
Richard Klein <[EMAIL PROTECTED]> wrote: > > My understanding is that, even with all the above > features _OMIT_ed, SQLite 3 is still about twice > the size of SQLite 2. > > It you don't think that's true, let me know: I don't think that is true. One x86, SQLite2 and SQLite3 are roughly the same size. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: step back
Clive.Bluston-cPKiotmf5pXN/[EMAIL PROTECTED] wrote: sqlite3_step() is great for scrolling forward through a result set. Is there a way to scroll backwards? Store the ROWIDs of the records you've seen so far, retrieve each record by its ROWID as needed. Or, just store complete rows. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Handling commit errors
WIESEN Bruno <[EMAIL PROTECTED]> wrote: I have another problem, I would like to create an unique index that rollbacks on conflict...I've read in the book "The definitive guide to SQLite" that it was possible by specifying a conflict clause. What I did but I have an error when creating the index ! CREATE UNIQUE INDEX AccountNumber ON Account (number ASC) ON CONFLICT ROLLBACK; For some reason, the syntax of CREATE INDEX statement doesn't allow one to specify conflict resolution clause: http://sqlite.org/lang_createindex.html You'll have to do it in CREATE TABLE, as I've shown in my first response. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Handling commit errors
Hello, Sorry for yesterday, I made a mistake in my code and consequently no messages were displayed when encountering a constraint violation ! But now it does... I have another problem, I would like to create an unique index that rollbacks on conflict...I've read in the book "The definitive guide to SQLite" that it was possible by specifying a conflict clause. What I did but I have an error when creating the index ! CREATE UNIQUE INDEX AccountNumber ON Account (number ASC) ON CONFLICT ROLLBACK; Where is the error? Thanks... Bien à vous, Bruno WIESEN .. Bruno WIESEN [EMAIL PROTECTED] Mac OS Software Developer Pyramide Ingenierie sprl Tel: +32 87 292130 188 rue de Liege Fax: +32 87 292139 B-4800 VerviersMail: [EMAIL PROTECTED] .. Le 4 oct. 07 à 14:27, Igor Tandetnik a écrit : WIESEN Bruno <[EMAIL PROTECTED]> wrote: The first insert statements were made as tests, in our application we wrap these statements in transaction...And we don't know when something wrong happens because commit doesn't notifie us... ... but INSERT does. Why is this not sufficient? Is there a solution to be notified when using commit? Have you read anything I was saying? Have you followed the link I've provided? I believe I've already answered this question quite thoroughly. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- ---
Re: [sqlite] Feature request - Tcl variables as "value-list"s
On Thu, 4 Oct 2007 21:35:30 -0500, Andy Goth wrote
> (See my original proposal writeup at the bottom of
> http://wiki.tcl.tk/2633 for more details.)
I made a significant update to the bottom of said page. I'll briefly cover it
here as well. Basically I revise my proposal to be less generic, to only
support the limited case of a single Tcl variable expanding to the entire
value-list.
Old script:
> $ set x {1 2 3}
> $ db eval {insert into xyzdata values({*}$x)}
New script:
$ db eval {insert into xyzdata values $x}
Note the lack of parentheses around $x. This distinguishes it from the case
of inserting a single value "1 2 3" into xyzdata.
--
Andy Goth
<[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Feature request - Tcl variables as "value-list"s
"Andy Goth" <[EMAIL PROTECTED]> wrote:
> On Thu, 4 Oct 2007 21:35:30 -0500, Andy Goth wrote
> > (See my original proposal writeup at the bottom of
> > http://wiki.tcl.tk/2633 for more details.)
>
> I made a significant update to the bottom of said page. I'll briefly cover it
> here as well. Basically I revise my proposal to be less generic, to only
> support the limited case of a single Tcl variable expanding to the entire
> value-list.
>
> Old script:
>
> > $ set x {1 2 3}
> > $ db eval {insert into xyzdata values({*}$x)}
>
> New script:
>
> $ db eval {insert into xyzdata values $x}
>
> Note the lack of parentheses around $x. This distinguishes it from the case
> of inserting a single value "1 2 3" into xyzdata.
>
There are deep technical reasons why this is very difficult
and unlikely to ever happen. I suggest you go head and write
a short TCL procedure to accomplish the same thing.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Feature request - Tcl variables as "value-list"s
On Fri, 05 Oct 2007 15:20:41 +, drh wrote
> "Andy Goth" <[EMAIL PROTECTED]> wrote:
> > http://wiki.tcl.tk/2633
>
> I suggest you go head and write a short TCL procedure to
> accomplish the same thing.
Like this?
proc sql_expand {varname} {
upvar 1 $varname var
set result [list]
foreach elem $var {
lappend result '[string map {' ''} $elem]'
}
return [join $result ,]
}
$ set x {1 2 3}
$ db eval "insert into xyzdata values ([sql_expand x])"
(expands to)
$ db eval "insert into xyzdata values ('1','2','3')"
Is there any problem with the spurious quotes around the values? Will that
interfere with integer primary key or anything like that?
> There are deep technical reasons why this is very difficult
> and unlikely to ever happen.
I will spend some time familiarizing myself with the relevant bits of the
SQLite sources to see why this is the case. :^)
--
Andy Goth
<[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Feature request - Tcl variables as "value-list"s
You really should be using an SQLite-specific quote function
somewhere. But ... I don't see one in there (I'd have expected it to
be something like [db quote $arg]). You could work around it by doing
something like [db eval {select quote($arg)}], but that feels clunky.
The quoting you're using will work fine for many cases, but are
subject to SQL injection attack.
-scott
On 10/5/07, Andy Goth <[EMAIL PROTECTED]> wrote:
> On Fri, 05 Oct 2007 15:20:41 +, drh wrote
> > "Andy Goth" <[EMAIL PROTECTED]> wrote:
> > > http://wiki.tcl.tk/2633
> >
> > I suggest you go head and write a short TCL procedure to
> > accomplish the same thing.
>
> Like this?
>
> proc sql_expand {varname} {
>upvar 1 $varname var
>set result [list]
>foreach elem $var {
> lappend result '[string map {' ''} $elem]'
>}
>return [join $result ,]
> }
>
> $ set x {1 2 3}
> $ db eval "insert into xyzdata values ([sql_expand x])"
> (expands to)
> $ db eval "insert into xyzdata values ('1','2','3')"
>
> Is there any problem with the spurious quotes around the values? Will that
> interfere with integer primary key or anything like that?
>
> > There are deep technical reasons why this is very difficult
> > and unlikely to ever happen.
>
> I will spend some time familiarizing myself with the relevant bits of the
> SQLite sources to see why this is the case. :^)
>
> --
> Andy Goth
> <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Determine version compatibility
I am trying to use PHP and SQLite on N800 Nokia Internet Tablet - a great handheld Linux based computer, but not exactly Linux (uses Maemo). That means compiling is not an option for me, and most Linux things are 'ported' over. I can create a new database and insert from both command line and using php pdo in the NGINX web server (localhost in browser), but in browser can't access database created from commandline, and in commandline can't access database created from browser/localhost. Both say "Encrypted or is not a database" Is there a way to get the version info from within PHP_PDO? And are my versions incompatible? Anyway, I installed SQLite and from command line it shows SQLite version 3.3.5 That one is easy. When running NGINX web server as localhost and doing phpinfo() I get (0nly applicable parts shown): Configure command shows -with sqlite PDO Drivers sqlite2, sqlite in the pdo_sqlite section: PDO Driver for SQLite 3.x enabled PECL Module version (bundled) 1.0.1 $id:pdo_sqlite.c, v1.10.2.6.2.1. 2007/01/01 sebastion Exp $ SQLite LIbrary 3.3.7 But then in the SQLite section SQLite support enabled PECL Module version 2.0-dev $id: sqlite.c, v 1.166.2.13.2.6 2007/01/01 sebastian Exp $ SQLite Library 2.8.17 SQLite Encoding iso8859 So I think it is creating databases in 3.3.7 - is that incompatible with 3.3.5? -- View this message in context: http://www.nabble.com/Determine-version-compatibility-tf4576460.html#a13063321 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Feature request - Tcl variables as "value-list"s
"Andy Goth" <[EMAIL PROTECTED]> wrote:
> On Fri, 05 Oct 2007 15:20:41 +, drh wrote
> > "Andy Goth" <[EMAIL PROTECTED]> wrote:
> > > http://wiki.tcl.tk/2633
> >
> > I suggest you go head and write a short TCL procedure to
> > accomplish the same thing.
>
> Like this?
>
> proc sql_expand {varname} {
>upvar 1 $varname var
>set result [list]
>foreach elem $var {
> lappend result '[string map {' ''} $elem]'
>}
>return [join $result ,]
> }
>
> $ set x {1 2 3}
> $ db eval "insert into xyzdata values ([sql_expand x])"
> (expands to)
> $ db eval "insert into xyzdata values ('1','2','3')"
>
> Is there any problem with the spurious quotes around the values? Will that
> interfere with integer primary key or anything like that?
What you have will work. The '...' will not effect integer values.
But you might change the routine as follows:
proc sql_expand {varname} {
upvar 1 $varname var
set result [list]
foreach elem $var {
if {[string is double -strict $elem]} {
lappend result $elem
} else {
lappend result '[string map {' ''} $elem]'
}
}
return [join $result ,]
}
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Feature request - Tcl variables as "value-list"s
"Scott Hess" <[EMAIL PROTECTED]> wrote:
> You really should be using an SQLite-specific quote function
> somewhere. But ... I don't see one in there (I'd have expected it to
> be something like [db quote $arg]). You could work around it by doing
> something like [db eval {select quote($arg)}], but that feels clunky.
>
> The quoting you're using will work fine for many cases, but are
> subject to SQL injection attack.
>
The built-in quoting function is:
zQuoted = sqlite3_mprintf("%Q", zUnquoted);
But the %Q quoter does exactly what Andy's code does.
It does exactly the same thing as
'[string map {' ''} $unquoted]'
So if you know of a way that this can lead to an SQL
injection attack, please let us know so that we can
fix the %Q quoter.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Feature request - Tcl variables as "value-list"s
On Fri, 5 Oct 2007 09:41:27 -0700, Scott Hess wrote
> On 10/5/07, Andy Goth <[EMAIL PROTECTED]> wrote:
> > proc sql_expand {varname} {
> >upvar 1 $varname var
> >set result [list]
> >foreach elem $var {
> > lappend result '[string map {' ''} $elem]'
> >}
> >return [join $result ,]
> > }
>
> You really should be using an SQLite-specific quote function
> somewhere.
I didn't know SQLite provided one. It normally doesn't need one, thanks to
its internal expansion of Tcl variables. Oh wait, it has quote(), but this
would not work for me because (by design!) its result is formatted in such a
way that SQLite interprets it as a single value, *not* a list. Or I could
invoke it repeatedly, once per element.
But after all that overhead I imagine I would be better off using Tcl to
expand my data into multiple variables; at least that way all bytecoding gets
cached and I'm guaranteed to be immune to injection.
> You could work around it by doing something like [db eval {select
> quote($arg)}], but that feels clunky.
I'm pretty sure I know how SQLite will perform quote()'ing, and I can
implement it in pure Tcl easily enough, so I might as well leave it in Tcl.
Tcl should be able to bytecode string manipulation better than SQLite; string
manipulation is Tcl's raison d'être.
But maybe I'm wrong about how quote() works. Can somebody confirm? In my
mind, quote($x) is equivalent to '[string map {' ''} $x]' .
> The quoting you're using will work fine for many cases, but are
> subject to SQL injection attack.
Could you please provide an example?
--
Andy Goth
<[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Determine version compatibility
bizshop <[EMAIL PROTECTED]> wrote: > > So I think it is creating databases in 3.3.7 - is that incompatible with > 3.3.5? > Except for corner cases (such as using language feature from one version of SQLite that did not exist in a different version) all versions of SQLite that being with "3" are compatible. That means 3.0.0 is compatible with 3.5.1 and everything in between. We take backwards compatibility very seriously. But 3.x.x is not compatible with 2.8.17. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Feature request - Tcl variables as "value-list"s
As drh indicated, you're already doing what any quote() function would
be doing, so it sounds like you're safe.
My general tendency is to assume that anytime I implement something in
parallel to another implementation, no matter how trivially obviously
identical the implementations are, at some point I'm going to pay a
price (usually because something gets added to the original
implementation and I don't realize I need to change my parallel
implementation). This may-or-may-not be an appropriate amount of
paranoia for any particular project :-).
-scott
On 10/5/07, Andy Goth <[EMAIL PROTECTED]> wrote:
> On Fri, 5 Oct 2007 09:41:27 -0700, Scott Hess wrote
> > On 10/5/07, Andy Goth <[EMAIL PROTECTED]> wrote:
> > > proc sql_expand {varname} {
> > >upvar 1 $varname var
> > >set result [list]
> > >foreach elem $var {
> > > lappend result '[string map {' ''} $elem]'
> > >}
> > >return [join $result ,]
> > > }
> >
> > You really should be using an SQLite-specific quote function
> > somewhere.
>
> I didn't know SQLite provided one. It normally doesn't need one, thanks to
> its internal expansion of Tcl variables. Oh wait, it has quote(), but this
> would not work for me because (by design!) its result is formatted in such a
> way that SQLite interprets it as a single value, *not* a list. Or I could
> invoke it repeatedly, once per element.
>
> But after all that overhead I imagine I would be better off using Tcl to
> expand my data into multiple variables; at least that way all bytecoding gets
> cached and I'm guaranteed to be immune to injection.
>
> > You could work around it by doing something like [db eval {select
> > quote($arg)}], but that feels clunky.
>
> I'm pretty sure I know how SQLite will perform quote()'ing, and I can
> implement it in pure Tcl easily enough, so I might as well leave it in Tcl.
> Tcl should be able to bytecode string manipulation better than SQLite; string
> manipulation is Tcl's raison d'être.
>
> But maybe I'm wrong about how quote() works. Can somebody confirm? In my
> mind, quote($x) is equivalent to '[string map {' ''} $x]' .
>
> > The quoting you're using will work fine for many cases, but are
> > subject to SQL injection attack.
>
> Could you please provide an example?
>
> --
> Andy Goth
> <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Determine version compatibility
> 3.0.0 is compatible with 3.5.1 and everything in between. Minor caveats if you happen to use different versions of sqlite libraries accessing the same database files: - Default databases created with versions 3.3.0 through 3.3.6 cannot be read by versions of sqlite from 3.0.0 through 3.2.8 (unless the legacy file format is explicitly used). - If you VACUUM a 3.3.0 - 3.3.6 database in a later sqlite version it will change the sqlite file format to the sqlite version of the program that issued the VACUUM, and versions 3.3.0 - 3.3.6 will not be able to read the VACUUMed database. Hopefully I've remembered the correct version numbers. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Determine version compatibility
> - If you VACUUM a 3.3.0 - 3.3.6 database in a later sqlite version it > will change the sqlite file format to the sqlite version of the program > that issued the VACUUM, and versions 3.3.0 - 3.3.6 will not be able to > read the VACUUMed database. My mistake. The above is wrong. This should read: - If you VACUUM a 3.0.0 - 3.2.8 database with a 3.3.0 - 3.3.6 sqlite version, it will change the sqlite file format to the sqlite version of the program that issued the VACUUM, and versions 3.0.0 - 3.2.8 will not be able to read the VACUUMed database. Please correct me if I've made any other errors. Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list=396545433 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] step back
[EMAIL PROTECTED] wrote: > sqlite3_step() is great for scrolling forward through a result set. > Is there a way to scroll backwards? > If not, did anyone try implementing it? > (I guess that the indexes would need backward pointers in order to do it.) > This issue keeps coming up so I did a wiki page. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] step back
[EMAIL PROTECTED] wrote: sqlite3_step() is great for scrolling forward through a result set. Is there a way to scroll backwards? If not, did anyone try implementing it? (I guess that the indexes would need backward pointers in order to do it.) This issue keeps coming up so I did a wiki page. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- D. Richard Hipp <[EMAIL PROTECTED]> Very clear and useful article. Thanks! - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Files opened by sqlite
My understanding is that, even with all the above features _OMIT_ed, SQLite 3 is still about twice the size of SQLite 2. It you don't think that's true, let me know: I don't think that is true. One x86, SQLite2 and SQLite3 are roughly the same size. -- D. Richard Hipp <[EMAIL PROTECTED]> Thanks for your replies, Richard and Joe. You've convinced me to take a closer look at the memory footprint of SQLite 3. I'll let you know what I find. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SELECT crashes with small cache?
Hello all, I am seeing SQLite crashing during execution of a SELECT statement when I make the page cache very small (40 pages). When I bump the cache up to 50 pages, the problem goes away. The problem only occurs on my RISC platform, not on my x86-based platform. Also, I am using SQLite 2 (not 3). My RISC platform prints out the stack trace in hex, not program symbols, so I can't yet tell you where in SQLite I'm crashing. (I'll try to get more info from the vendor on how to use their debugger.) Anyone ever see anything like this? Regards, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
