Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Hi,
Ok.
Is there any way to tell the VDBE to stop execution moment it gets a record
from the IN list rather than continue to query for all the parameters?
I mean can it work like the C if clause
a = 1;
b = 100;
if( a == 1 or b == 10)
{
}
in the above case a is evauated but not b. Is this possible in SQL or SQLite?
Pls suggest.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Trevor Talbot <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 1:03 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> > I want to know why
> > prepareStatement: select * from xxx where IN (?);
> > stmt.bind("abc,xyz,123"); is not supported for multiple
> > values.
>
> It's not supported because it doesn't make sense. The parametric
> binding mechanism is for single values; it's not a macro-like text
> replacement system. With your syntax, how do I bind a set of
> integers? Strings? Blobs?
>
> One common use for parametric binding (besides convenience) is to
> avoid SQL injection attacks. The example you posted doesn't do that;
> you have to manually escape each individual value to make sure it's
> valid syntax for the IN() group in text form. Why even use parameters
> in that case? It's the same amount of work whether you build the
> entire SQL statement or not.
>
> All common databases I'm aware of work exactly the same way.
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] VFS in upcoming 3.5.0
You can create a 'shared memory VFS' to share a memory database against other thread / processes, and you can also 'copy' the RAW bytes of your memory with memcpy from/to another storage to accomplish the serialize / load you want to wire transfer SQLite memory databases. But this is not a simple code, may be after some development, it could be give to public domain and merged into SQLite if it prove to be robust and safe enough. -Original Message- From: Olaf Schmidt [mailto:[EMAIL PROTECTED] Sent: sábado, 1 de setembro de 2007 12:15 To: sqlite-users@sqlite.org Subject: [sqlite] VFS in upcoming 3.5.0 Hi, first - congrats to the planned changes in the new, upcoming version of SQLite. The new shared-cache behaviour sounds promising and also the new VFS-option. A few questions to VFS. 1. As known, an InMemory-DB is currently not (much) faster than working against a File. With the new VFS I think, that much faster InMemory- DB-Handling should be possible, is that right? 2. If so, is it planned, to automatically instantiate an appropriate (already built in) InMemory-VFS, if one sets the Filename-Param to ':memory:' in an Open-Call, so that InMemory DBs work against this implicite created MemVFS - meaning that the "Default-SQLite- engine" already implements such an "InMemory-VFS" for us "Wrapper- developers" (because you know best, how to do it in the fastest possible way and because of my following "feature-request" below)? ;-) 3. If you plan something like this, it would be very nice, if you could include (now that many new interfaces are coming in either way) an additional API-enhancement, wich would allow, to get the current "Byte-Content" of an InMemory-DB, wich makes use of this new (built in) InMemory-VFS? The background for these questions is, that we use SQLite behind an Appserver wich is currently able, to get Resultset- Objects at the serverside (done over our wrapper) and after retrieving such an Resultset, to serialize its "Query-Content" into a ByteArray, wich is then transferred over sockets back to the client. At the clientside we are able, to deserialize the Bytes and "materialize" a new Resultset-Object appropriately. With a built in InMemory-VFS (and its new "Dump-Interface") we could achieve many nice things in only one roundtrip. At the serverside we could attach an empty InMemory-DB (implicitely using the new MemVFS) to an already pooled SQLite-Connection and perform a bunch of "Insert Into-Queries", to create a small snapshot of e.g. a midsized "Master-Detail- Scenario". Now we could dump not only a "single-query-content" (as with our Resultsets currently), but could write a complete, related scenario (containing the prepared "InMemory-Tables") to a ByteArray and transfer *this* to the client. Now it would be great, if we could use the new MemVFS- interface, to create *and* initialize a new InMemory-DB at the clientside with the received ByteContent. This way, we could perform related queries (Joins, Filters, etc.) against the InMemory-DB (containing the midsize Master-Detail-Set) without doing any extra-roundtrips over the server. An already builtin InMemory-VFS would ease the burden of all wrapper-developers, to implement such kind of animal themselfes - and maybe such an implementation would help to cleanup (and speedup) the already contained InMemory-DB-Handling of the sqlite-engine too. What's your opinion on this feature-request? Best regards, Olaf Schmidt (developer of dhSQLite and dhRPCServer) -- View this message in context: http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12441170 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] extension-functions.tgz for sqlite3 3.4.1 ?
Good point. I guess my intent is this: I'd like to use the external
API completely if possible. If not, I'm not sure whether to include source
or use the current library (I can see advantages to each). I guess there
aren't external API calls that do the needed tasks, so I'll have to figure
out what to do.
Liam
On 9/1/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
>
> --- Liam Healy <[EMAIL PROTECTED]> wrote:
> > Thanks. I have changed the use of sqlite3CreateFunc to
> > sqlite3_create_function. I did not need to include the source code for
> > sqlite3utf8CharLen because there's a sqlite3Utf8CharLen (note different
> > capitalization) in the library. However, the definition
>
> How you intend to integrate your new SQL extension functions into sqlite?
> As a loadable module or as an sqlite3 source code patch?
>
> You can't use sqlite3 internal functions such as sqlite3Utf8CharLen
> if you're making an external loadable module, which is why it was
> suggested
> to copy the function into your code statically. You can only use the
> published sqlite3 external API in this case. But your library will survive
> without modifications over new sqlite3 releases.
>
> If you're not making an external loable module and are making an sqlite3
> source patch, just use the script provided earlier in this thread to
> change
> the old extension sources to be compatible with the 3.4.x sqlite3.c
> amalgamation. Mind you, if you're doing the patch approach you may have to
> keep updating it with every new sqlite release.
>
> > of sqlite3ReadUtf8 and needed definitions READ_UTF8, xtra_utf8_bytes,
> > xtra_utf8_bits, utf_mask are not in 3.4.2, so I needed to
> > salvage from 3.3.13 source. This compiles and loads OK, but I'm
> wondering
> > if there is a 3.4 way of doing what sqlite3ReadUtf8 did
> > so that I don't have to carry the definitions. If anyone has a
> suggestion
> > I'd appreciate hearing about it.
> >
> > Liam
> >
> >
> > On 8/31/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > >
> > > --- Liam Healy <[EMAIL PROTECTED]> wrote:
> > > > I was the one who packaged up extension-functions.tgz and posted on
> > > > contrib. I didn't author the original code but I'd like to fix this
> up.
> > > > I'm not clear on what needs to be changed. I gather that
> > > sqlite3utf8CharLen
> > > > and sqlite3CreateFunc shouldn't be used. I'm not sure how to
> convert to
> > > use
> > > > the external API. Anyone have a pointer?
> > >
> > > See:
> > >
> > > http://www.sqlite.org/capi3ref.html#sqlite3_create_function
> > >
> > > http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
> > >
> > > And include this statically in your code, if you require it:
> > >
> > > #define SQLITE_SKIP_UTF8(zIn) {\
> > > if( (*(zIn++))>=0xc0 ){ \
> > > while( (*zIn & 0xc0)==0x80 ){ zIn++; } \
> > > }\
> > > }
> > >
> > > int sqlite3Utf8CharLen(const char *zIn, int nByte){
> > > int r = 0;
> > > const u8 *z = (const u8*)zIn;
> > > const u8 *zTerm;
> > > if( nByte>=0 ){
> > > zTerm = [nByte];
> > > }else{
> > > zTerm = (const u8*)(-1);
> > > }
> > > assert( z<=zTerm );
> > > while( *z!=0 && z > > SQLITE_SKIP_UTF8(z);
> > > r++;
> > > }
> > > return r;
> > > }
>
>
>
>
>
>
> Yahoo! oneSearch: Finally, mobile search
> that gives answers, not web links.
> http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
Re: [sqlite] Looking for a cryptographic library
hi, after a long time being on a business trip, i finally came home and have now the chance to upload the sources to my webserver: http://greschenz.dyndns.org/sqlite.html these sources have never been in a productive system, i just implemented it for fun... what i want to say: i never tested it really good ! cu, gg - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] VFS in upcoming 3.5.0
>> 1. As known, an InMemory-DB is currently not (much) faster >>than working against a File. >>With the new VFS I think, that much faster InMemory- >>DB-Handling should be possible, is that right? > I don't think so. The vfs is really an interface for moving > data (pages) in and out of the pager cache. Yep, so I understood them. > The way in-memory databases currently work, pages are > just kept in the page-cache all the time and never written > out to whatever system implements the vfs interface. Ah, ok. Somehow thought, that the InMemory-Handling was implemented, working against the b-trees, but then going *through* a "non-sized" pager-cache against "something behind it", wich was implemented using standard-fileio-apis, working against memory-mapped-files (so being system-dependent and not so performant like a "real selfwritten Filesystem"). >From my Insert-Tests (50,000 records on 6 mixed Columns): Filebased (sync=off, pagesize 4kB): ca. 0.7sec InMemory (pagesize 4kB): ca. 0.55sec (all done using direct bindings on the same Insert-statement) So it seems, that *not* seeing a huge performance-boost is more, because most of the "insert-time" is spent populating the B-tree, right? Anyway, an easy to use, already built in "Dump-Interface" for InMemory-DBs (able to be initialized with a given Byte- Content) would be nice. ;-) (Yes, I saw patches for this ask, but some time ago, not knowing if those work with the current pager. Simply thought, that this is a good time, to ask for an "officially supported" interface-enhancement regarding this functionality, mainly because of the new VFS and because I thought, you would use its capabilities in either way for InMemory-DBs). Regards, Olaf Schmidt -- View this message in context: http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12442415 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> I want to know why
> prepareStatement: select * from xxx where IN (?);
> stmt.bind("abc,xyz,123"); is not supported for multiple
> values.
It's not supported because it doesn't make sense. The parametric
binding mechanism is for single values; it's not a macro-like text
replacement system. With your syntax, how do I bind a set of
integers? Strings? Blobs?
One common use for parametric binding (besides convenience) is to
avoid SQL injection attacks. The example you posted doesn't do that;
you have to manually escape each individual value to make sure it's
valid syntax for the IN() group in text form. Why even use parameters
in that case? It's the same amount of work whether you build the
entire SQL statement or not.
All common databases I'm aware of work exactly the same way.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] VFS in upcoming 3.5.0
On Sat, 2007-09-01 at 08:15 -0700, Olaf Schmidt wrote: > Hi, > > first - congrats to the planned changes in the new, upcoming > version of SQLite. The new shared-cache behaviour sounds > promising and also the new VFS-option. > > A few questions to VFS. > 1. As known, an InMemory-DB is currently not (much) faster >than working against a File. >With the new VFS I think, that much faster InMemory- >DB-Handling should be possible, is that right? I don't think so. The vfs is really an interface for moving data (pages) in and out of the pager cache. The way in-memory databases currently work, pages are just kept in the page-cache all the time and never written out to whatever system implements the vfs interface. So there's not a lot you can do with the vfs interface to speed up in-memory databases. Years ago (sqlite v2) there was different data structure used for in-memory databases - "red-black balanced trees" or something instead of b-trees. But IIRC it wasn't all that much faster than the current approach - maybe 15-20% faster or thereabouts. And it didn't do nearly as good a job of using memory efficiently. Dan. > 2. If so, is it planned, to automatically instantiate an appropriate >(already built in) InMemory-VFS, if one sets the Filename-Param >to ':memory:' in an Open-Call, so that InMemory DBs work against >this implicite created MemVFS - meaning that the "Default-SQLite- >engine" already implements such an "InMemory-VFS" for us "Wrapper- >developers" (because you know best, how to do it in the fastest >possible way and because of my following "feature-request" below)? ;-) > > 3. If you plan something like this, it would be very nice, if >you could include (now that many new interfaces are coming in >either way) an additional API-enhancement, wich would allow, >to get the current "Byte-Content" of an InMemory-DB, wich >makes use of this new (built in) InMemory-VFS? > > The background for these questions is, that we use SQLite > behind an Appserver wich is currently able, to get Resultset- > Objects at the serverside (done over our wrapper) and after > retrieving such an Resultset, to serialize its "Query-Content" > into a ByteArray, wich is then transferred over sockets back > to the client. > > At the clientside we are able, to deserialize the Bytes > and "materialize" a new Resultset-Object appropriately. > > With a built in InMemory-VFS (and its new "Dump-Interface") > we could achieve many nice things in only one roundtrip. > At the serverside we could attach an empty InMemory-DB > (implicitely using the new MemVFS) to an already pooled > SQLite-Connection and perform a bunch of "Insert Into-Queries", > to create a small snapshot of e.g. a midsized "Master-Detail- > Scenario". > Now we could dump not only a "single-query-content" (as > with our Resultsets currently), but could write a complete, > related scenario (containing the prepared "InMemory-Tables") > to a ByteArray and transfer *this* to the client. > > Now it would be great, if we could use the new MemVFS- > interface, to create *and* initialize a new InMemory-DB at > the clientside with the received ByteContent. > This way, we could perform related queries (Joins, Filters, > etc.) against the InMemory-DB (containing the midsize > Master-Detail-Set) without doing any extra-roundtrips > over the server. > > An already builtin InMemory-VFS would ease the burden > of all wrapper-developers, to implement such kind of animal > themselfes - and maybe such an implementation would > help to cleanup (and speedup) the already contained > InMemory-DB-Handling of the sqlite-engine too. > > What's your opinion on this feature-request? > > Best regards, > > Olaf Schmidt > (developer of dhSQLite and dhRPCServer) > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Merge different Sqlite DB's
Use SQL. You could use a script like Perl or TCL. RaghavendraK 70574 wrote: Hi, If i have 2 sqlite files is there a way to merge the files without corruption? assuming the schema of both the files are same. I don't want to use sql statements rather want to do using file api's. Similar to diffdb.c pls help. regards ragha - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Merge different Sqlite DB's
Hi, If i have 2 sqlite files is there a way to merge the files without corruption? assuming the schema of both the files are same. I don't want to use sql statements rather want to do using file api's. Similar to diffdb.c pls help. regards ragha - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] compiling
You can define them as literals or read them from a file. As a literal: char *sql_statement = "SELECT * FROM mytable"; If you read all your SQL from a file you can use the Sqlite capability of having it in one big string delimited with semi-colons and preparing it is a loop where each instatnce of sqlite3_prepare passes back a pointer to the start of the next SQL statement. That would work by opening the file, reading it into a buffer or memory mapping it, closing the file and then compiling all the SQL in a loop and then unmapping the file if you mmap'd it. You now have all your SQL compiled ready to bind and execute. The file method has the advantage that you can use it to test the SQL using the command line tool, sqlite3. To use sqlite3_prepare: sqlite3_prepare(,,-1,sqlite3_stmt>,); nishit sharma wrote: can anybody tell me that without using argc and argv in my main() program how can i pass sqlite3 statements in my C source code. more precise is that this time i m opening the database using argc and argv in my main and in sqlite3_open() call. similarly in sqlite3_exec(). but how can i make queries in C source code to open, exec and other things to my database without using these argc and argv. waiting for the reply regards Nishit On 8/31/07, John Stanton <[EMAIL PROTECTED]> wrote: nishit sharma wrote: hi all, i have made a sampe which is opening a database file but i m unable to compile that source code and getting error that undefined reference to sqlite3_open(). i m compiling as gcc test.c can anybody tell that these is the command to compile sqlite3 application or we have any other command waiting for reply regards Nishit You need to link with the sqlite3 library. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] compiling
can anybody tell me that without using argc and argv in my main() program how can i pass sqlite3 statements in my C source code. more precise is that this time i m opening the database using argc and argv in my main and in sqlite3_open() call. similarly in sqlite3_exec(). but how can i make queries in C source code to open, exec and other things to my database without using these argc and argv. waiting for the reply regards Nishit On 8/31/07, John Stanton <[EMAIL PROTECTED]> wrote: > > nishit sharma wrote: > > hi all, > > i have made a sampe which is opening a database file but i m > > unable to compile that source code and getting error that > > undefined reference to sqlite3_open(). > > i m compiling as > > gcc test.c > > > > can anybody tell that these is the command to compile > > sqlite3 application or we have any other command > > > > waiting for reply > > > > regards > > Nishit > > > You need to link with the sqlite3 library. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Performance tuning for Insert and select operations
On Fri, 31 Aug 2007 09:34:18 +0530, you wrote: >Hi, > >I am using SQLite 3_3_17with the default configuration of SQLite. >Using Windows XP C++ API. >50,000 inserts into Table (with primary key and no other index as single >transaction commit) is taking 4.609000 sec. >50,000 selects from Table ( having 1,00,000 rec with primary key and no >other index) , query using primary key is taking 8.751000 sec. >I have to achieve insertion bench mark of nearly 50,000 insertions in 1.5 to >2 sec. >I have to achieve selection bench mark of nearly 50,000 selections in 1.5 to >2 sec. >Please tell me if it is possible to tune Sqlite to achieve this performance. > >Regards, >Ramana In general: 1) Optimize your schema and queries. 2) Look at what PRAGMA can do for you, and apply them in the correct order. http://www.sqlite.org/pragma.html 3) Before loading, sort your input in key order. 4) Tune your operating system and file system. 5) Adjust hardware. Good luck -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Thx.
I want to know why
prepareStatement: select * from xxx where IN (?);
stmt.bind("abc,xyz,123"); is not supported for multiple
values.
Instead sqlite expect use to declare the statement var before hand,
is it due to limitation of Virtual Machine impl?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Saturday, September 1, 2007 11:59 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On Sat, 2007-09-01 at 11:58 +0800, RaghavendraK 70574 wrote:
> > Hi,
> >
> > In one of the slides "http://www.sqlite.org/php2004/page-052.html;
> > it is stated "OR will make DBEngine not to use Indexes"
> and IN will make DBEngine use
> > indexes"
> >
> > I could not understand the rationale abt this. Can u explain?
>
> The point is (was) that if you have a table like this:
>
> CREATE TABLE abc(a PRIMARY KEY, b, c);
>
> Then the first query uses the index on a, the second does (did) not:
>
> SELECT * FROM abc WHERE a IN ('hello', 'world');
> SELECT * FROM abc WHERE a = 'hello' OR a = 'world';
>
> However, since those slides were written SQLite has been updated
> so that
> queries of the second form are internally transformed to the
> first, and
> therefore may use an index.
>
> Dan.
>
> >
> > regards
> > ragha
> >
> >
> **>
> This email and its attachments contain confidential information from
> HUAWEI, which is intended only for the person or entity whose address is
> listed above. Any use of the information contained herein in any way
> (including, but not limited to, total or partial disclosure, reproduction, or
> dissemination) by persons other than the intended recipient(s) is prohibited.
> If you receive this e-mail in error, please notify the sender by phone or
> email immediately and delete it!
> >
> *>
>
> > -
>
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
>
> >
>
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
