Re: [sqlite] Multithreaded vs. serialized

[Dan]

On Jan 15, 2009, at 6:07 AM, John Belli wrote:

> Assuming I've decided to use evil threads, and am opening a new
> connection in each thread, does it matter whether I use multithreaded
> (-DSQLITE_THREADSAFE=2) or serialized (-DSQLITE_THREADSAFE=1)? Any
> idea if one is faster than the other? I am using SQLite on Win32 and
> WinCE, and I'll be using shared caching
> (sqlite3_enable_shared_cache(TRUE)).

In serialized mode, each database handle (sqlite3*) has an internal
mutex. Each time any API call is made on that database handle, the
mutex is obtained, the work of the API call is done, and the mutex
is released. Making an API call on a statement object prepared using
the database handle counts as making an API call on the database handle
itself for the purposes of this mutex.

Therefore, in serialized mode, SQLite database and statement handles
are threadsafe objects. You can make simultaneous calls on database
or statement handles from multiple evilthreads if you so desire.

In multi-thread mode, this database handle mutex is omitted. So things
run slightly faster because there is one less mutex grab each time
an API call is made. But if you make simultaneous calls on a single
database handle from multiple evilthreads, sqlite will crash or  
malfunction.

In your situation, where you are promising that a database handle will
only ever be used by the evilthread that created it, you could probably
get away with multi-thread mode. You would have no more or less
concurrency as when using serialized mode though. Possibly a tiny
performance improvement because you avoid a few mutex grabs though.

Don't forget that for thread-safety purposes, accessing a statement
handle counts as accessing the database handle that was used to
prepare it.



> JAB
> -- 
> John A. Belli
> Software Engineer
> Refrigerated Transport Electronics, Inc.
> http://www.rtelectronics.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to run speed tests (speedtest.tcl)

[goldy]

Hi,

i tried to run speedtest.tcl as mention in
http://www.sqlite.org/speed.htmlbut i am getting below error:

./testfixture: couldn't execute "./sqlite248": no such file or directory
while executing
"exec ./sqlite248 s2k.db <2kinit.sql"

How to generate sqlite248 ?

Regards
Avinash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross compilation

[mkrajachandru]

Hello MikeW
All the information in that link to cross compile sqlite
for ARM is Sqlite-3.3.7.

But now i am using sqlite-3.6.7. I am unable to find the lines to comment in
the latest configure script.

and I need to generate "testfixture" binary to run all test in arm
environment.

So how can I cross compile sqlite-3.6.7 for ARM?

How to generate "testfixture" binary for ARM?

Thanks in advance

Chandru

On Wed, Jan 14, 2009 at 3:18 PM, MikeW  wrote:

>    writes:
>
> >
> > Hello all
> >
> > How can i cross compile tcl and sqlite for arm and ppc
> >
> > Tahnks in advance
> >
> > Chandru K
>
> http://www.sqlite.org/cvstrac/wiki?p=HowToCompile
>
> MikeW
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Modularity, Joins

[Alex Krzos]

Hi all,

I am doing a project which involves looking through sqlite code and getting
a feel for how the dbms actually works.  I have three questions.

First off, sqlite is supposedly modular.  I am having a tough time
determining the modularity of the components.  How is sqlite modular, it
seems that most of the components are coded to be exactly the way they are.

The b-tree implemented by sqlite, is that a b+tree or b-tree?

Third, where can I find how joins are implemented?  Even better yet, how are
joins parsed then processed?

Thanks.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request to become co-maintainer of DBD::SQLite

[Darren Duncan]

These are replies to posts on the sqlite-users list.  However, if there is 
going 
to be ongoing discussion I prefer it happen on the dbi-dev list.  Not that 
sqlite-users isn't very on topic itself, dbi-dev just seems *more* on topic, I 
think.

Clark Christensen wrote:
>> One of my first code changes will be to require DBI 1.607+
> 
> The current DBD-SQLite works fine under older versions of DBI.  So unless 
> there's a compelling reason to do it, I would prefer you not make what seems 
> like an arbitrary requirement.

I have 2 answers to that:

1.  Sure, I can avoid changing the enforced dependency requirements for now, 
leaving them as Matt left them.  However, I will officially deprecate support 
for the older versions and won't test on them.  If something works with the 
newer dependencies but not the older ones, it will be up to those using or 
supporting the older dependencies to supply fixes.

2.  On one hand I could say, why not update your DBI when you're updating 
DBD::SQLite, since even the DBI added lots of fixes one should have.  On the 
other hand, I can understand the reality that you may have other legacy modules 
like drivers for other old databases that might break with a DBI update.  I say 
might, since on the other hand they might not break.  Still, I'll just go the 
deprecation angle for now.

> Otherwise, it sounds like a good start.  Matt must be really busy with other 
> work.
> 
> I'll be happy to contribute where I can, but no C-fu here, either :-(

Thank you.

Ribeiro, Glauber wrote:
 > My only suggestion at the moment, please use the amalgamation instead of
 > individual files. This makes it much easier to upgrade when SQLite
 > releases a new version.

Okay.

Jim Dodgen wrote:
 > I'm for the amalgamation too.  the rest of you ideas are great also.
 > excelent idea to use Audrey Tangs nameing convention.
 >
 > I have been stuck back at 3.4 for various issues.
 >
 > I do Perl and C and offer some help.

Okay and thank you.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A list as an SQL paramater

[Sam Thursfield]

Hi
Here are the results of my speed testing:

Recompile: 85.937500 total, 0.017188 avg.
Temporary table: 87.00 total, 0.017400 avg.
Virtual table: 85.562500 total, 0.017112 avg.

As you can see the whole thing was really a pointless exercise :( I
had imagined the impact of 'prepare' to be much higher than it
actually is - using a virtual table seems to be generally a tiny bit
faster, but we are talking .4 of a second over 50,000 calls, when I
don't see more than a few hundred being done at a time.

Bearing this in mind I will stop wasting my time worrying about such
silly performance issues :) Thanks for all the input in this thread I
have at least learned a bit about sqlite!

PS. if anyone is interested, the (shabby) code I used to find these
numbers is here:
http://dl.getdropbox.com/u/407072/junk/sqlite-test.c

Sam.

On Wed, Jan 14, 2009 at 9:26 PM, Sam Thursfield  wrote:
> On Wed, Jan 14, 2009 at 4:38 PM, Igor Tandetnik  wrote:
>> Sam Thursfield  wrote:
> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3,
> 6, 7") ...; which of course doesn't work.
>>>
>>> Do you know off hand whether this method would be much faster than
>>> compiling a new query for each new list of ids?
>>
>> I'm not sure I understand the question. How can one compare the speed of
>> the solution that doesn't work with one that does?
>>
>
> What I'm comparing is the speed of SELECTing the list from a temporary
> table, vs. building a new query string with printf and calling
> sqlite3_prepare each time I want to execute it with a different $list.
> This would definitely work but does incur the overhead of
> sqlite3_prepare each time I want to run the query.
>
> I had an idea for a third method too, which would use a virtual table
> instead of a temporary table, and get the list from the app to to the
> query that way. I think I'm going to do some profiling to found out
> which method is the fastest.
>
> MikeW, that's an ingenious idea but I have slightly more than 64 rows :)
>
> Sam
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multithreaded vs. serialized

[Lawrence Gold]

On Jan 14, 2009, at 4:07 PM, John Belli wrote:

> Assuming I've decided to use evil threads, and am opening a new
> connection in each thread, does it matter whether I use multithreaded
> (-DSQLITE_THREADSAFE=2) or serialized (-DSQLITE_THREADSAFE=1)? Any
> idea if one is faster than the other? I am using SQLite on Win32 and
> WinCE, and I'll be using shared caching
> (sqlite3_enable_shared_cache(TRUE)).

John,

I don't have a definitive answer, but I suspect that using the  
multithreaded configuration with a new connection in each thread would  
potentially be faster, since multiple readers can run concurrently  
instead of being serialized.  I don't know what effect a shared cache  
would have, but it sounds as if it supports multiple simultaneous  
readers.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multithreaded vs. serialized

[John Belli]

Assuming I've decided to use evil threads, and am opening a new
connection in each thread, does it matter whether I use multithreaded
(-DSQLITE_THREADSAFE=2) or serialized (-DSQLITE_THREADSAFE=1)? Any
idea if one is faster than the other? I am using SQLite on Win32 and
WinCE, and I'll be using shared caching
(sqlite3_enable_shared_cache(TRUE)).


JAB
-- 
John A. Belli
Software Engineer
Refrigerated Transport Electronics, Inc.
http://www.rtelectronics.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A list as an SQL paramater

[Clark Christensen]

> What I'm comparing is the speed of SELECTing the list from a temporary
> table, vs. building a new query string with printf and calling
> sqlite3_prepare each time I want to execute it with a different $list.

Please forgive me if I'm missing a key point, but it sounds like you already 
have the list in a temp table.  If that's the case, wouldn't something like

SELECT * ... WHERE foreign.id IN (select foreign_id from temptable...)

do the job for you?

FWIW, I often find myself wanting to pass a list (array) of variable length as 
bound parameters.  I understand why it may not be practical, but it would be a 
nice feature.  Is this even part of the SQL standard?

 -Clark



- Original Message 
From: Sam Thursfield 
To: General Discussion of SQLite Database 
Sent: Wednesday, January 14, 2009 1:26:14 PM
Subject: Re: [sqlite] A list as an SQL paramater

On Wed, Jan 14, 2009 at 4:38 PM, Igor Tandetnik  wrote:
> Sam Thursfield  wrote:
 - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3,
 6, 7") ...; which of course doesn't work.
>>
>> Do you know off hand whether this method would be much faster than
>> compiling a new query for each new list of ids?
>
> I'm not sure I understand the question. How can one compare the speed of
> the solution that doesn't work with one that does?
>

What I'm comparing is the speed of SELECTing the list from a temporary
table, vs. building a new query string with printf and calling
sqlite3_prepare each time I want to execute it with a different $list.
This would definitely work but does incur the overhead of
sqlite3_prepare each time I want to run the query.

I had an idea for a third method too, which would use a virtual table
instead of a temporary table, and get the list from the app to to the
query that way. I think I'm going to do some profiling to found out
which method is the fastest.

MikeW, that's an ingenious idea but I have slightly more than 64 rows :)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: A list as an SQL paramater

[Griggs, Donald]

 
Regarding:
   "This would definitely work but does incur the overhead of
sqlite3_prepare each time I want to run the query."




Maybe you would be preparing hundreds or thousands of different queries
per minute -- but if by chance you're not -- could anyone be expected to
notice the time for the prepare's?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A list as an SQL paramater

[Sam Thursfield]

On Wed, Jan 14, 2009 at 4:38 PM, Igor Tandetnik  wrote:
> Sam Thursfield  wrote:
 - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3,
 6, 7") ...; which of course doesn't work.
>>
>> Do you know off hand whether this method would be much faster than
>> compiling a new query for each new list of ids?
>
> I'm not sure I understand the question. How can one compare the speed of
> the solution that doesn't work with one that does?
>

What I'm comparing is the speed of SELECTing the list from a temporary
table, vs. building a new query string with printf and calling
sqlite3_prepare each time I want to execute it with a different $list.
This would definitely work but does incur the overhead of
sqlite3_prepare each time I want to run the query.

I had an idea for a third method too, which would use a virtual table
instead of a temporary table, and get the list from the app to to the
query that way. I think I'm going to do some profiling to found out
which method is the fastest.

MikeW, that's an ingenious idea but I have slightly more than 64 rows :)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request to become co-maintainer of DBD::SQLite

[Jim Dodgen]

I'm for the amalgamation too.  the rest of you ideas are great also.
excelent idea to use Audrey Tangs nameing convention.

I have been stuck back at 3.4 for various issues.

I do Perl and C and offer some help.

On Wed, Jan 14, 2009 at 8:44 AM, Ribeiro, Glauber <
glauber.ribe...@experian.com> wrote:

> My only suggestion at the moment, please use the amalgamation instead of
> individual files. This makes it much easier to upgrade when SQLite
> releases a new version.
>
> g
>
> -Original Message-
> From: Clark Christensen [mailto:cdcmi...@yahoo.com]
> Sent: Wednesday, January 14, 2009 10:19 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] request to become co-maintainer of DBD::SQLite
>
>
> > One of my first code changes will be to require DBI 1.607+
>
> The current DBD-SQLite works fine under older versions of DBI.  So
> unless there's a compelling reason to do it, I would prefer you not make
> what seems like an arbitrary requirement.
>
> Otherwise, it sounds like a good start.  Matt must be really busy with
> other work.
>
> I'll be happy to contribute where I can, but no C-fu here, either :-(
>
>  -Clark
>
>
>
> - Original Message 
> From: Darren Duncan 
> To: m...@sergeant.org; mserge...@cpan.org
> Cc: General Discussion of SQLite Database ; DBI
> Dev ; DBIx::Class user and developer list
> ; rose-db-obj...@googlegroups.com;
> modu...@perl.org; c...@audreyt.org
> Sent: Tuesday, January 13, 2009 7:55:30 PM
> Subject: [sqlite] request to become co-maintainer of DBD::SQLite
>
> Hello Matt Sergeant,
>
> I would like to request your permission or blessing to become an
> official
> co-maintainer of the DBD::SQLite module, which is the defacto standard
> binding
> for SQLite to Perl.
>
> (Also CC'd are some other concerned parties as FYI; my apologies if I've
> written
> too many people.  But this message is initially just for response by
> Matt,
> though others can write if they feel inclined, but try to keep the
> recipient
> list smaller than I just did here.  Focus any discussion to
> dbi-...@perl.org and
> modu...@perl.org as appropriate please, the former for what work needs
> doing and
> the latter for matters of module maintainership.)
>
> P.S.  Or if anyone else has the tuits and wants to make a better offer
> to be a
> co-maintainer now, please do so.
>
> I am interested in the long-term success of SQLite in combination with
> Perl, and
> in the short term I am particularly interested in using the latest
> SQLite 3.6.8
> (which adds the extremely important feature of nested transactions) with
> modern
> versions of Perl, and I am interested that it would be easy for the
> large number
> of other DBD::SQLite users to use this combination as well.
>
> I am also concerned with there apparently being a number of significant
> bugs in
> DBD::SQLite that have been reported on the RT system, some with patches,
> and
> DBD::SQLite hasn't seen new releases in awhile to either address bugs or
> update
> the bundled SQLite.  A number of people I trust are seeing that this is
> a
> serious matter to address, some in the mean-time recommending use of
> older
> DBD::SQLite versions, which is itself a problem since automatic CPAN
> install
> tools would select the newest versions, and access to newer SQLite
> library
> features is missing.
>
> Now I would of course be happiest if you had the time and motivation to
> bring
> your project up to date and address its bugs.  But otherwise I would
> like to
> offer you an out, and take on this responsibility myself, either alone
> or with
> partners such as yourself or other concerned parties that want to help.
>
> If you agree, then please say the word to modu...@perl.org.
>
> My CPAN account ID is DUNCAND.
>
> To summarize, this is my intention in the short term:
>
> 1.  Release a new version every time there is a SQLite core library
> release.
>
> 2.  Make only the most minimal changes to DBD::SQLite itself, to ensure
> that
> reported bugs are fixed and that it compiles on modern systems and
> passes its
> own test suite on the same.  There won't be any feature additions or
> architectural changes initially, except where such may be highly
> demanded and
> simple.  The priorities here are stability and correctness plus easy
> access to
> all the SQLite library's native features, and minimal additional
> features.
>
> 3.  All initial releases will have version numbers ending in _NN that
> mark them
> as developer releases, so the community can test them before they become
> what
> the CPAN tools install by default.
>
> 4.  Perhaps follow what Audrey Tang started and use the official
> amalgamated
> pre-compiled source files rather than the original-original source code,
> so
> users with less capable build environments can handle it.  Though in the
> short
> term this will depend on which version I can get to work with fewer
> 

Re: [sqlite] Get table name from table entry

[Igor Tandetnik]

Christoph Walser 
wrote:
> I have a table A with a row called 'services' which contains the names
> of other tables.

I recommend you change your design. You'll have nothing but trouble with 
this.

Merge all tables into a single table with the extra "service" column.

> What I want to do is to query table A and get from it
> the name of table B which is then accessed.
> What I tried is the following:
>
> SELECT *
> FROM (
> SELECT service
> FROM A
> WHERE a_key = 1
> )

No, this is not going to work, nor anything substantially similar. You 
will have to use "SELECT service FROM A WHERE a_key = 1" in your 
application to retrieve the table name, then build "select * from 
tableName" query on the fly.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Get table name from table entry

[Christoph Walser]

Hi,

I have a table A with a row called 'services' which contains the names 
of other tables. What I want to do is to query table A and get from it 
the name of table B which is then accessed.
What I tried is the following:

SELECT *
FROM (
SELECT service
FROM A
WHERE a_key = 1
)

The inner SELECT statements returns correctly the name of the table B 
but the outer loop does not understand that it should treat the result 
from the inner loop as a tablename rather than a result set.

Any ideas how I can solve this?

Thank you,

Christoph


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A quick question

[MikeW]

J. R. Westmoreland  writes:

> 
> So far, everything I have looked at is very old, long before Vista hit the
> scene.
> Still looking.
> I guess I could give up and turn around to the Linux console and try it
> there. 
> But, you understand how sometimes you really want to make things work in the
> current environment.
> Thanks for the info so far.
> 
> Regards,
> J. R.

Also, Eclipse has a CVS client of sorts, "Team SynchoniZing View",
but have not used it in anger.

MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Converted sqlite3VdbeSerialTypeLen into a macro

[Brown, Daniel]

Good morning list,

 

I've been doing some profiling of SQLite 3.6.1 on PC and some of our
other proprietary platforms.  It has become evident that the function
'sqlite3VdbeSerialTypeLen' is being hit a lot: enough that the function
is adding up to a fair % of program execution time.  As the actual
calculation the function is performing is relatively trivial compared to
the costs of a function call, I propose converting the function into an
macro to reduce the cost.  I am suggesting a macro as C has no reliable
cross compiler inline functionality or at least the compilers we have
here don't seem to support the inline functionality added in C99.  

 

I made the following addition to 'vdbeInt.h' and removed the original C
function, prototype and replaced any references to the original
function.

/* Convert sqlite3VdbeSerialTypeLen into a macro: no way to inline in C!
*/

static const u8 g_aSize[] = { 0, 1, 2, 3, 4, 6, 8, 8, 0, 0, 0, 0 };

#define SQLITE3VDBESERIALTYPELEN(serial_type) ( serial_type>=12 ?
(serial_type-12)/2 : g_aSize[serial_type])

 

Please not that I don't have TCL set-up on this PC so I am unable to
test this change against the current mainline right now.  I'd be
interested in any feedback on this proposed change.

 

Cheers,

 

Daniel Brown | Software Engineer 

"The best laid schemes o' mice an' men, gang aft agley"

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A quick question

[Thomas Briggs]

   Why not just use Cygwin?

On Wed, Jan 14, 2009 at 12:55 PM, J. R. Westmoreland  wrote:
> So far, everything I have looked at is very old, long before Vista hit the
> scene.
> Still looking.
> I guess I could give up and turn around to the Linux console and try it
> there. 
> But, you understand how sometimes you really want to make things work in the
> current environment.
> Thanks for the info so far.
>
> Regards,
> J. R.
>
> 
> J. R. Westmoreland
> E-mail: j...@jrw.org
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW
> Sent: Wednesday, January 14, 2009 3:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] A quick question
>
> J. R. Westmoreland  writes:
>
>>
>> I hope this is not the wrong place to ask this but .
>>
>> If you are using Windows Vista Ultimate which CVS package does one want to
>> get/use to view the source tree?
>>
>> I tried Tortoise CVS and insipte of what a note implies it fails to
> install.
>>
>> I thought this might be a good one since I'm running their SVN package and
>> it's pretty good.
>>
>> Thanks in advance for any suggestions.
>>
>> J. R.
>
> Vista breaks Tortoise CVS's display of column data in Explorer due
> to withdrawing the IColumnProvider interface from the Windows API
> with no thought to back-compatibility.
>
> You could try http://www.wincvs.org/
>
> MikeW
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A quick question

[J. R. Westmoreland]

So far, everything I have looked at is very old, long before Vista hit the
scene.
Still looking.
I guess I could give up and turn around to the Linux console and try it
there. 
But, you understand how sometimes you really want to make things work in the
current environment.
Thanks for the info so far.

Regards,
J. R.


J. R. Westmoreland
E-mail: j...@jrw.org


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MikeW
Sent: Wednesday, January 14, 2009 3:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] A quick question

J. R. Westmoreland  writes:

> 
> I hope this is not the wrong place to ask this but .
> 
> If you are using Windows Vista Ultimate which CVS package does one want to
> get/use to view the source tree?
> 
> I tried Tortoise CVS and insipte of what a note implies it fails to
install.
> 
> I thought this might be a good one since I'm running their SVN package and
> it's pretty good.
> 
> Thanks in advance for any suggestions.
> 
> J. R.

Vista breaks Tortoise CVS's display of column data in Explorer due
to withdrawing the IColumnProvider interface from the Windows API
with no thought to back-compatibility.

You could try http://www.wincvs.org/

MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A list as an SQL paramater - correction

[MikeW]

MikeW  writes:
> 
> If your list values are relatively small you could use a set
> of bits for your IN clause and use
>  bitwise:  WHERE ((1< encodes the required elements as bits set to 1.
> 
..snip..
> 
> Regards,
> MikeW

Oops, that should be '&' not 'AND' !

MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request to become co-maintainer of DBD::SQLite

[Ribeiro, Glauber]

My only suggestion at the moment, please use the amalgamation instead of
individual files. This makes it much easier to upgrade when SQLite
releases a new version.

g 

-Original Message-
From: Clark Christensen [mailto:cdcmi...@yahoo.com] 
Sent: Wednesday, January 14, 2009 10:19 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] request to become co-maintainer of DBD::SQLite


> One of my first code changes will be to require DBI 1.607+

The current DBD-SQLite works fine under older versions of DBI.  So
unless there's a compelling reason to do it, I would prefer you not make
what seems like an arbitrary requirement.

Otherwise, it sounds like a good start.  Matt must be really busy with
other work.

I'll be happy to contribute where I can, but no C-fu here, either :-(

 -Clark



- Original Message 
From: Darren Duncan 
To: m...@sergeant.org; mserge...@cpan.org
Cc: General Discussion of SQLite Database ; DBI
Dev ; DBIx::Class user and developer list
; rose-db-obj...@googlegroups.com;
modu...@perl.org; c...@audreyt.org
Sent: Tuesday, January 13, 2009 7:55:30 PM
Subject: [sqlite] request to become co-maintainer of DBD::SQLite

Hello Matt Sergeant,

I would like to request your permission or blessing to become an
official 
co-maintainer of the DBD::SQLite module, which is the defacto standard
binding 
for SQLite to Perl.

(Also CC'd are some other concerned parties as FYI; my apologies if I've
written 
too many people.  But this message is initially just for response by
Matt, 
though others can write if they feel inclined, but try to keep the
recipient 
list smaller than I just did here.  Focus any discussion to
dbi-...@perl.org and 
modu...@perl.org as appropriate please, the former for what work needs
doing and 
the latter for matters of module maintainership.)

P.S.  Or if anyone else has the tuits and wants to make a better offer
to be a 
co-maintainer now, please do so.

I am interested in the long-term success of SQLite in combination with
Perl, and 
in the short term I am particularly interested in using the latest
SQLite 3.6.8 
(which adds the extremely important feature of nested transactions) with
modern 
versions of Perl, and I am interested that it would be easy for the
large number 
of other DBD::SQLite users to use this combination as well.

I am also concerned with there apparently being a number of significant
bugs in 
DBD::SQLite that have been reported on the RT system, some with patches,
and 
DBD::SQLite hasn't seen new releases in awhile to either address bugs or
update 
the bundled SQLite.  A number of people I trust are seeing that this is
a 
serious matter to address, some in the mean-time recommending use of
older 
DBD::SQLite versions, which is itself a problem since automatic CPAN
install 
tools would select the newest versions, and access to newer SQLite
library 
features is missing.

Now I would of course be happiest if you had the time and motivation to
bring 
your project up to date and address its bugs.  But otherwise I would
like to 
offer you an out, and take on this responsibility myself, either alone
or with 
partners such as yourself or other concerned parties that want to help.

If you agree, then please say the word to modu...@perl.org.

My CPAN account ID is DUNCAND.

To summarize, this is my intention in the short term:

1.  Release a new version every time there is a SQLite core library
release.

2.  Make only the most minimal changes to DBD::SQLite itself, to ensure
that 
reported bugs are fixed and that it compiles on modern systems and
passes its 
own test suite on the same.  There won't be any feature additions or 
architectural changes initially, except where such may be highly
demanded and 
simple.  The priorities here are stability and correctness plus easy
access to 
all the SQLite library's native features, and minimal additional
features.

3.  All initial releases will have version numbers ending in _NN that
mark them 
as developer releases, so the community can test them before they become
what 
the CPAN tools install by default.

4.  Perhaps follow what Audrey Tang started and use the official
amalgamated 
pre-compiled source files rather than the original-original source code,
so 
users with less capable build environments can handle it.  Though in the
short 
term this will depend on which version I can get to work with fewer
problems on 
my own machine (Mac OS X Leopard).

5.  I may use an older DBD::SQLite than the current one, such as 1.12,
as an 
initial point of departure, if doing so makes for a more trouble-free
solution.

6.  I will have this in a public GIT source repository and I will
regularly seek 
feedback, help, patches, testing, etc from the user community that have
a stake 
in this working.

7.  I am assuming until corrected that the primary discussion forum for
people 
to discuss actual work to do and patches 

Re: [sqlite] A list as an SQL paramater

[MikeW]

Sam Thursfield  writes:

> 
> Hello everyone,
> I have a question which I'm hoping someone who knows a bit about
> SQlite's internals can answer easily.
> 
> I have a query such as this:
> 
> SELECT * FROM local INNER JOIN foreign ON local.foreign_id =
> foreign.id WHERE foreign.id IN (4, 3, 6, 7) ORDER BY local.name;
> 
> Here the list of numbers in the WHERE clause is subject to change. How
> possible do you think it is to have this as a prepared query?
> 
> SELECT * ... WHERE foreign.id IN ($list) ...;
> 
> Bearing in mind:
>  - the length of the list is completely variable.
>  - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, 6,
> 7") ...; which of course doesn't work.
>  - binding seems to set specific values in the VM, so my instinct says
> this won't be possible because the query will change too much based on
> the list of ids - it will need too much recompiling each time.
> 
> However, I thought I would throw this problem out to people who knew
> sqlite in more depth than me to see if there's an easy answer that I'm
> missing.
> 
> Thanks in advance for any help!
> Sam

If your list values are relatively small you could use a set
of bits for your IN clause and use
 bitwise:  WHERE ((1<

Re: [sqlite] A list as an SQL paramater

[Igor Tandetnik]

Sam Thursfield  wrote:
>>> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3,
>>> 6, 7") ...; which of course doesn't work.
>
> Do you know off hand whether this method would be much faster than
> compiling a new query for each new list of ids?

I'm not sure I understand the question. How can one compare the speed of 
the solution that doesn't work with one that does?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A list as an SQL paramater

[Sam Thursfield]

Hi Igor,
Thanks for your reply.

On Wed, Jan 14, 2009 at 4:16 PM, Igor Tandetnik  wrote:
> Sam Thursfield  wrote:
>> I have a query such as this:
>>
>> SELECT * FROM local INNER JOIN foreign ON local.foreign_id =
>> foreign.id WHERE foreign.id IN (4, 3, 6, 7) ORDER BY local.name;
>>
>> Here the list of numbers in the WHERE clause is subject to change. How
>> possible do you think it is to have this as a prepared query?
>>
>> SELECT * ... WHERE foreign.id IN ($list) ...;
>
> The usual solution is to create a temp table, populate it with your list
> (using a prepared INSERT statement), then in SELECT statement write
> something like
>
> WHERE foreign.id IN (SELECT id FROM temp.ids)
>
>> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, 6,
>> 7") ...; which of course doesn't work.

Do you know off hand whether this method would be much faster than
compiling a new query for each new list of ids? If not I think I'll do
some quick research.

> But something like this would work, if you want to go this way:
>
> WHERE ',4,3,6,7,' LIKE '%,' || foreign.id || ',%'
>
> Though if you are prepared to engage in string manipulation like this,
> you could just as well embed the list into the query string and not
> bother with parameters.

Yeah this is an interesting solution but I figure just making a new
query with printf and preparing it each time would be quicker :)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] request to become co-maintainer of DBD::SQLite

[Clark Christensen]

> One of my first code changes will be to require DBI 1.607+

The current DBD-SQLite works fine under older versions of DBI.  So unless 
there's a compelling reason to do it, I would prefer you not make what seems 
like an arbitrary requirement.

Otherwise, it sounds like a good start.  Matt must be really busy with other 
work.

I'll be happy to contribute where I can, but no C-fu here, either :-(

 -Clark



- Original Message 
From: Darren Duncan 
To: m...@sergeant.org; mserge...@cpan.org
Cc: General Discussion of SQLite Database ; DBI Dev 
; DBIx::Class user and developer list 
; rose-db-obj...@googlegroups.com; 
modu...@perl.org; c...@audreyt.org
Sent: Tuesday, January 13, 2009 7:55:30 PM
Subject: [sqlite] request to become co-maintainer of DBD::SQLite

Hello Matt Sergeant,

I would like to request your permission or blessing to become an official 
co-maintainer of the DBD::SQLite module, which is the defacto standard binding 
for SQLite to Perl.

(Also CC'd are some other concerned parties as FYI; my apologies if I've 
written 
too many people.  But this message is initially just for response by Matt, 
though others can write if they feel inclined, but try to keep the recipient 
list smaller than I just did here.  Focus any discussion to dbi-...@perl.org 
and 
modu...@perl.org as appropriate please, the former for what work needs doing 
and 
the latter for matters of module maintainership.)

P.S.  Or if anyone else has the tuits and wants to make a better offer to be a 
co-maintainer now, please do so.

I am interested in the long-term success of SQLite in combination with Perl, 
and 
in the short term I am particularly interested in using the latest SQLite 3.6.8 
(which adds the extremely important feature of nested transactions) with modern 
versions of Perl, and I am interested that it would be easy for the large 
number 
of other DBD::SQLite users to use this combination as well.

I am also concerned with there apparently being a number of significant bugs in 
DBD::SQLite that have been reported on the RT system, some with patches, and 
DBD::SQLite hasn't seen new releases in awhile to either address bugs or update 
the bundled SQLite.  A number of people I trust are seeing that this is a 
serious matter to address, some in the mean-time recommending use of older 
DBD::SQLite versions, which is itself a problem since automatic CPAN install 
tools would select the newest versions, and access to newer SQLite library 
features is missing.

Now I would of course be happiest if you had the time and motivation to bring 
your project up to date and address its bugs.  But otherwise I would like to 
offer you an out, and take on this responsibility myself, either alone or with 
partners such as yourself or other concerned parties that want to help.

If you agree, then please say the word to modu...@perl.org.

My CPAN account ID is DUNCAND.

To summarize, this is my intention in the short term:

1.  Release a new version every time there is a SQLite core library release.

2.  Make only the most minimal changes to DBD::SQLite itself, to ensure that 
reported bugs are fixed and that it compiles on modern systems and passes its 
own test suite on the same.  There won't be any feature additions or 
architectural changes initially, except where such may be highly demanded and 
simple.  The priorities here are stability and correctness plus easy access to 
all the SQLite library's native features, and minimal additional features.

3.  All initial releases will have version numbers ending in _NN that mark them 
as developer releases, so the community can test them before they become what 
the CPAN tools install by default.

4.  Perhaps follow what Audrey Tang started and use the official amalgamated 
pre-compiled source files rather than the original-original source code, so 
users with less capable build environments can handle it.  Though in the short 
term this will depend on which version I can get to work with fewer problems on 
my own machine (Mac OS X Leopard).

5.  I may use an older DBD::SQLite than the current one, such as 1.12, as an 
initial point of departure, if doing so makes for a more trouble-free solution.

6.  I will have this in a public GIT source repository and I will regularly 
seek 
feedback, help, patches, testing, etc from the user community that have a stake 
in this working.

7.  I am assuming until corrected that the primary discussion forum for people 
to discuss actual work to do and patches etc for DBD::SQLite is 
dbi-...@perl.org.

Some caveats:

1.  I have very little C-fu right now and won't be able to do much in the short 
term besides update the SQLite source files, and apply third-party patches to 
the C, and make more involved fixes to the portions written in Perl.

2.  It will probably be several weeks before my first release, partly because I 
am busy with other 

Re: [sqlite] A list as an SQL paramater

[Igor Tandetnik]

Sam Thursfield  wrote:
> I have a query such as this:
>
> SELECT * FROM local INNER JOIN foreign ON local.foreign_id =
> foreign.id WHERE foreign.id IN (4, 3, 6, 7) ORDER BY local.name;
>
> Here the list of numbers in the WHERE clause is subject to change. How
> possible do you think it is to have this as a prepared query?
>
> SELECT * ... WHERE foreign.id IN ($list) ...;

The usual solution is to create a temp table, populate it with your list 
(using a prepared INSERT statement), then in SELECT statement write 
something like

WHERE foreign.id IN (SELECT id FROM temp.ids)

> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, 6,
> 7") ...; which of course doesn't work.

But something like this would work, if you want to go this way:

WHERE ',4,3,6,7,' LIKE '%,' || foreign.id || ',%'

Though if you are prepared to engage in string manipulation like this, 
you could just as well embed the list into the query string and not 
bother with parameters.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A list as an SQL paramater

[Sam Thursfield]

Hello everyone,
I have a question which I'm hoping someone who knows a bit about
SQlite's internals can answer easily.

I have a query such as this:

SELECT * FROM local INNER JOIN foreign ON local.foreign_id =
foreign.id WHERE foreign.id IN (4, 3, 6, 7) ORDER BY local.name;

Here the list of numbers in the WHERE clause is subject to change. How
possible do you think it is to have this as a prepared query?

SELECT * ... WHERE foreign.id IN ($list) ...;

Bearing in mind:
 - the length of the list is completely variable.
 - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, 6,
7") ...; which of course doesn't work.
 - binding seems to set specific values in the VM, so my instinct says
this won't be possible because the query will change too much based on
the list of ids - it will need too much recompiling each time.

However, I thought I would throw this problem out to people who knew
sqlite in more depth than me to see if there's an easy answer that I'm
missing.

Thanks in advance for any help!
Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension (SEE) memory usage andperformance

[Ulric Auger]

Great!!!
Thanks for the info.

In case somebody read this post and wander what are my computer spec
(to compare with my results):
Toshiba Satellite P200 -RT3
CPU: Intel Core2 T2450 @ 2.00GHz
RAM: 2Gb
HHD: Hitachi HTS541616J9SA00 (160 Gb)
OS:  Windows XP SP2

Ulric

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: January 13, 2009 9:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Encryption Extension (SEE) memory usage
andperformance


On Jan 13, 2009, at 9:29 PM, Ulric Auger wrote:

> I started to use SEE and I was surprise to notice that the file size  
> of my
> encrypted database is the same has the un-encrypted database, how  
> come?
>
> It seems too good to be true, I was sure that the encrypted database  
> would
> have been bigger.

AES128 uses 12 bytes per database page for the encryption nonce - so  
that is only about a 1% overhead.  AES256 uses 28 bytes per database  
page.  So an encrypted database might be 1% or 2% larger on average.   
But all database files round off to the page size, so in your case,  
they are probably rounding off to the same size.  If you keep  
searching, you might eventually find a database that is one page  
larger after encryption.

>
>
>
>
> Also, I timed some query (insert and select) and couldn't see  
> noticeable
> speed difference.
>
> Again this seems too good to be true.
>

You probably have a fast CPU relative to the speed of your disk.   
SQLite is really spending all its time doing disk I/O.  The extra  
overhead for AES doesn't make that much difference.

Your mileage may vary on other machines with different CPU-speed to  
Disk-speed ratios.

>
>
>
> Anybody having these great performances with SEE?
>
>
>
> My test where done with SQLite 3.6.8 with AES128 and AES256.
>
> Using a hex editor the encrypted database was definitely scrambled.
>
>
>
> Thanks
>
>
>
> Ulric
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does "PRAGMA integrity_check" actua lly do?

[MikeW]

Richard Klein  writes:

> 
...snip...
> We're confident of our file system, because we've never had
> any problems with it.  However, we have seen some nasty data
> corruption problems with our current database system, which
> we hope to eliminate by converting to SQLite.
> 
> - Richard Klein

Sounds like concurrent-update issues ? Try a mutex on writes to the DB !
SQLite just uses a Big Lock ! (ok, ok, not really)

MikeW



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A quick question

[MikeW]

J. R. Westmoreland  writes:

> 
> I hope this is not the wrong place to ask this but .
> 
> If you are using Windows Vista Ultimate which CVS package does one want to
> get/use to view the source tree?
> 
> I tried Tortoise CVS and insipte of what a note implies it fails to install.
> 
> I thought this might be a good one since I'm running their SVN package and
> it's pretty good.
> 
> Thanks in advance for any suggestions.
> 
> J. R.

Vista breaks Tortoise CVS's display of column data in Explorer due
to withdrawing the IColumnProvider interface from the Windows API
with no thought to back-compatibility.

You could try http://www.wincvs.org/

MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cross compilation

[MikeW]

  writes:

> 
> Hello all
> 
> How can i cross compile tcl and sqlite for arm and ppc
> 
> Tahnks in advance
> 
> Chandru K

http://www.sqlite.org/cvstrac/wiki?p=HowToCompile

MikeW

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users