Re: [sqlite] trademark issue? - (was Re: Announcing the Madis project)

[Dennis Cote]

On 10-03-09 8:47 PM, Darren Duncan wrote:
>
> (Incidentally, I *have* registered my trademark.  But that is a non-issue 
> here.)
>
>
Darren,

Aren't you required to put the registered trademark symbol, ®, on each 
use of your trademarked name or logo? I didn't see it (or the ™symbol 
for an unregistered trademark for that matter) on your website, so I 
assumed you hadn't registered the trademark.

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

Re: [sqlite] SQLite version 3.6.23

[Melton Low]

I just tried to build 3.6.23 on a PPC Mac running OS X 10.4.

>
> I got an undefined symbol error and the make aborted.

/bin/sh ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1
 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2   -o libsqlite3.la -rpath
/usr/local/lib -no-undefined -version-info 8:6:8 sqlite3.lo
gcc -dynamiclib  -o .libs/libsqlite3.0.8.6.dylib  .libs/sqlite3.o
-install_name  /usr/local/lib/libsqlite3.0.dylib -compatibility_version 9
-current_version 9.6
ld: Undefined symbols:
_gethostuuid
/usr/libexec/gcc/powerpc-apple-darwin8/4.0.1/libtool: internal link edit
command failed
make: *** [libsqlite3.la] Error 1

I have attached the full configure and make output as an attachment.

Your help would be appreciated.

Mel
BigMac:~/Downloads/sqlite-3.6.23 melton$ ./configure 
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for style of include used by make... GNU
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables... 
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking dependency style of gcc... gcc3
checking for special C compiler options needed for large files... no
checking for _FILE_OFFSET_BITS value needed for large files... no
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ISO C89... (cached) none needed
checking dependency style of gcc... (cached) gcc3
checking for ranlib... ranlib
checking build system type... powerpc-apple-darwin8.11.0
checking host system type... powerpc-apple-darwin8.11.0
checking for a sed that does not truncate output... /usr/bin/sed
checking for grep that handles long lines and -e... /usr/bin/grep
checking for egrep... /usr/bin/grep -E
checking for ld used by gcc... /usr/libexec/gcc/powerpc-apple-darwin8/4.0.1/ld
checking if the linker (/usr/libexec/gcc/powerpc-apple-darwin8/4.0.1/ld) is GNU 
ld... no
checking for /usr/libexec/gcc/powerpc-apple-darwin8/4.0.1/ld option to reload 
object files... -r
checking for BSD-compatible nm... /usr/bin/nm -p
checking whether ln -s works... yes
checking how to recognise dependent libraries... pass_all
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking dlfcn.h usability... yes
checking dlfcn.h presence... yes
checking for dlfcn.h... yes
checking for g++... g++
checking whether we are using the GNU C++ compiler... yes
checking whether g++ accepts -g... yes
checking dependency style of g++... gcc3
checking how to run the C++ preprocessor... g++ -E
checking for g77... no
checking for xlf... no
checking for f77... no
checking for frt... no
checking for pgf77... no
checking for cf77... no
checking for fort77... no
checking for fl32... no
checking for af77... no
checking for xlf90... no
checking for f90... no
checking for pgf90... no
checking for pghpf... no
checking for epcf90... no
checking for gfortran... no
checking for g95... no
checking for xlf95... no
checking for f95... no
checking for fort... no
checking for ifort... no
checking for ifc... no
checking for efc... no
checking for pgf95... no
checking for lf95... no
checking for ftn... no
checking whether we are using the GNU Fortran 77 compiler... no
checking whether  accepts -g... no
checking the maximum length of command line arguments... 196608
checking command to parse /usr/bin/nm -p output from gcc object... ok
checking for objdir... .libs
checking for ar... ar
checking for ranlib... (cached) ranlib
checking for strip... strip
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... -fno-common
checking if gcc PIC flag -fno-common works... yes
checking if gcc static flag -static works... no
checking if gcc supports -c -o file.o... yes
checking whether the gcc linker 
(/usr/libexec/gcc/powerpc-apple-darwin8/4.0.1/ld) supports shared libraries... 
yes
checking dynamic linker characteristics... darwin8.11.0 dyld
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
configure: creating libtool
appending configuration tag "CXX" to 

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses)

[Pavel Ivanov]

> Pavel, regarding the question about VFS, I'm not using one to my knowledge 
> and have set the "name of VFS module" to NULL in sqlite3_open_v2.  Maybe NULL 
> means I'm using the standard VFS, but in any case, not a "non-standard" one.

If you pass NULL you use _default_ VFS, not a standard one. When
non-standard VFS is registered it can be set to be default.

> Here are some more timings...

I'm out of ideas about this. I understand that it shouldn't work this
way and I don't understand why it does.
Is it possible for you to run application in some sort of profiler?
Obviously it should show that a lot of time is spent in waiting on
some mutex and it will be able to show where this mutex is held
from...


Pavel

On Tue, Mar 9, 2010 at 1:02 PM, Luke Evans  wrote:
> Hi guys,
>
> Had to take a break for a couple of days from my SQLite experiments, but back 
> on it now.
>
> Pavel, regarding the question about VFS, I'm not using one to my knowledge 
> and have set the "name of VFS module" to NULL in sqlite3_open_v2.  Maybe NULL 
> means I'm using the standard VFS, but in any case, not a "non-standard" one.
> I'm selecting from a real table.
>
> Here are some more timings...
>
> Shared cache ON.  6 of the same query issued (as before) within the 
> multithreaded scenario.  All the queries bunch up and report complete more or 
> less together.
>
> 2010-03-09 09:32:33.013 SQLiteTest[16208:a0f] SQLite says multithreading is ON
> 2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SQLite's multithreading value 
> is 2
> 2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SHARED CACHE IS ON
> 2010-03-09 09:32:33.017 SQLiteTest[16208:1b03] Starting query on thread 
> {name = (null), num = 3}
> 2010-03-09 09:32:33.018 SQLiteTest[16208:5603] Starting query on thread 
> {name = (null), num = 4}
> 2010-03-09 09:32:33.017 SQLiteTest[16208:4403] Starting query on thread 
> {name = (null), num = 2}
> 2010-03-09 09:32:33.018 SQLiteTest[16208:5503] Starting query on thread 
> {name = (null), num = 5}
> 2010-03-09 09:32:33.019 SQLiteTest[16208:5703] Starting query on thread 
> {name = (null), num = 6}
> 2010-03-09 09:32:33.019 SQLiteTest[16208:5c03] Starting query on thread 
> {name = (null), num = 7}
> 2010-03-09 09:32:41.359 SQLiteTest[16208:5603] Finished query on thread 
> {name = (null), num = 4} in 8.34s
> 2010-03-09 09:32:41.359 SQLiteTest[16208:1b03] Finished query on thread 
> {name = (null), num = 3} in 8.34s
> 2010-03-09 09:32:41.359 SQLiteTest[16208:4403] Finished query on thread 
> {name = (null), num = 2} in 8.34s
> 2010-03-09 09:32:41.359 SQLiteTest[16208:5503] Finished query on thread 
> {name = (null), num = 5} in 8.34s
> 2010-03-09 09:32:41.360 SQLiteTest[16208:5703] Finished query on thread 
> {name = (null), num = 6} in 8.34s
> 2010-03-09 09:32:41.360 SQLiteTest[16208:5c03] Finished query on thread 
> {name = (null), num = 7} in 8.34s
> 2010-03-09 09:32:41.360 SQLiteTest[16208:a0f] Finished all queries in 8.34s
>
>
>
> 6 of the same query executed in separate processes.
> The query time is essentially the same as when you issue just a single query 
> in the 'multithreaded' configuration above - as one would expect.
>
> 2010-03-09 09:47:02.729 SQLiteTest[17083:a0f] SQLite says multithreading is ON
> 2010-03-09 09:47:02.731 SQLiteTest[17083:a0f] SQLite's multithreading value 
> is 2
> SHARED CACHE IS OFFRead uncommitted is 1
> SHARED CACHE IS OFFRead uncommitted is 1
> SHARED CACHE IS OFFRead uncommitted is 1
> SHARED CACHE IS OFFRead uncommitted is 1
> SHARED CACHE IS OFFRead uncommitted is 1
> SHARED CACHE IS OFFRead uncommitted is 1
> Cache size is 10
> Page size is 32768
> Temp store is 2
> Synchronous is 0
> Cache size is 10
> Page size is 32768
> Temp store is 2
> Synchronous is 0
> Cache size is 10
> Page size is 32768
> Temp store is 2
> Synchronous is 0
> Cache size is 10
> Page size is 32768
> Temp store is 2
> Synchronous is 0
> Cache size is 10
> Page size is 32768
> Temp store is 2
> Synchronous is 0
> Cache size is 10
> Page size is 32768
> Temp store is 2
> Synchronous is 0
> On pid 17089, 3717 rows read in approx 2s
> On pid 17088, 3717 rows read in approx 2s
> On pid 17093, 3717 rows read in approx 2s
> On pid 17090, 3717 rows read in approx 2s
> On pid 17091, 3717 rows read in approx 2s
> On pid 17092, 3717 rows read in approx 2s
> 2010-03-09 09:47:04.323 SQLiteTest[17083:a0f] Finished all queries in 1.59s
>
>
>
> Same as above (6 queries in separate processes), but now each process has 
> SHARED CACHE=ON.
> There's no significant difference in timing (within normal variance).
>
> 22010-03-09 09:49:41.217 SQLiteTest[17272:a0f] SQLite says multithreading is 
> ON
> 2010-03-09 09:49:41.219 SQLiteTest[17272:a0f] SQLite's multithreading value 
> is 2
> SHARED CACHE IS ONRead uncommitted is 1
> SHARED CACHE IS ONRead uncommitted is 1
> SHARED CACHE IS ONRead uncommitted is 1
> SHARED CACHE IS ONRead uncommitted is 1
> SHARED CACHE IS ONRead uncommitted is 1
> SHARED 

Re: [sqlite] trademark issue? - (was Re: Announcing the Madis project)

[Darren Duncan]

Dennis Cote wrote:
> On 10-02-23 3:23 PM, Darren Duncan wrote:
>> Elefterios Stamatogiannakis wrote:
>>
>>> Madis is a extensible relational database system built upon the SQLite
>>> database and with extensions written in Python (via APSW SQLite
>>> wrapper). Its is developed at:
>>>
>>> http://madis.googlecode.com
>>
> Hi Darren,
> 
> I don't think there is any reasonable grounds to expect confusion 
> between these two products. To me the names are only vaguely similar.
> 
> Furthermore, if you have an issue with his use of "madis" being too 
> similar to your products name, then you probably have an similar issue 
> (with roles reversed) concerning Borland's (now Embarcadero) database 
> middle ware product "midas" which was announced in April of 1997.
> 
> Perhaps "multics" is another example of a product name that is quite 
> similar to yours, and who's use pre-dates yours considerably. One of its 
> claimed novelties was that "Multics implemented a single level store for 
> data access" which could easily be confused with database functionality.
> 
> I'm not a lawyer, but I don't think you would have much ground to stand 
> on in trademark dispute, especially if you have not registered your 
> trademark.

Thanks for your response.

So people know in general, Elefterios and I had already talked about the matter 
in private, and I also got another third party response like Dennis's, and 
suffice it to say I have no serious problem with the current situation, and I 
consider the matter closed.  It was only a borderline case to begin with anyway.

In other words, I'm quite fine with Madis continuing with their name, although 
from the discussion they might be officially casing it MadIS from now on, which 
would increase the visual difference.

I also suggested that there may be unrelated reasons to change their name.

As I suggest to anyone coming up with a project, try inventing a new word that 
doesn't match anything in Google (and otherwise is easy to read and pronounce) 
and use that for a name.  This way, when someone searches on your project name, 
they don't have to wade through a whole bunch of unrelated uses of the same 
word.  Also it means all the domain names of the word would be free.  Currently 
a Google search for "madis" turns up lots of other companies and technologies 
and people's names and other things, besides Elefterios' project.  So a unique 
name is simply about standing out in the crowd.

(Incidentally, I *have* registered my trademark.  But that is a non-issue here.)

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

Re: [sqlite] trademark issue? - (was Re: Announcing the Madis project)

[Dennis Cote]

On 10-02-23 3:23 PM, Darren Duncan wrote:
> Elefterios Stamatogiannakis wrote:
>
>> Madis is a extensible relational database system built upon the SQLite
>> database and with extensions written in Python (via APSW SQLite
>> wrapper). Its is developed at:
>>
>> http://madis.googlecode.com
>>
>>
>>  
> I am concerned with your choice of project name, "Madis", because there may be
> reasonable grounds for confusion between your project and my "Muldis" 
> projects,
> which I have been releasing and promoting for about 3 years now (and for which
> I've had domain names for about 6 years).
>
>
> I also welcome any third party comments in regards to whether I have 
> reasonable
> grounds to think there may be confusion between the 2 projects that could 
> affect
> trademark matters.
>
>
Hi Darren,

I don't think there is any reasonable grounds to expect confusion 
between these two products. To me the names are only vaguely similar.

Furthermore, if you have an issue with his use of "madis" being too 
similar to your products name, then you probably have an similar issue 
(with roles reversed) concerning Borland's (now Embarcadero) database 
middle ware product "midas" which was announced in April of 1997.

Perhaps "multics" is another example of a product name that is quite 
similar to yours, and who's use pre-dates yours considerably. One of its 
claimed novelties was that "Multics implemented a single level store for 
data access" which could easily be confused with database functionality.

I'm not a lawyer, but I don't think you would have much ground to stand 
on in trademark dispute, especially if you have not registered your 
trademark.

Just my two cents.

Dennis Cote


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

Re: [sqlite] structure question

[Dennis Cote]

On 10-02-26 2:25 PM, Francisco Azevedo wrote:
> Hi all,
>
> I want to create a "publish/undo system" for some tables but i don't
> know what is the best approach to do it.
> Imagine i have a table with columns id (auto-inc), data (text) then i
> want to edit table data (eg: create 2 new rows now, delete one tomorrow,
> update 3 rows tomorow too) and then decide if i want to preserve that
> modification or reverse it to the state it was before start that
> modifications.
>
>
Hi Francisco,

You should read this page in the wiki 
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo. It explains how to do 
this sort of undo system using triggers.

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

Re: [sqlite] SQLite files on your computer

[D. Richard Hipp]

On Mar 9, 2010, at 8:35 PM, C G wrote:

> Yes I am sending this in regards to sqlite files I have found on my  
> computer. What are they ? Why are they on my computer ? Why even  
> after I re install windows , is it STILL  logging my key strokes ?  
> Is this a key logger ? How do I remove it from my computer  ?   
> Please get back to me ASAP. As I feel my privacy has been violated!!
>
> *** Cyndi ***
>
>
>

SQLite is a database engine used by many different programs to store  
data.  Programs that use SQLite include iTunes, Adobe Acrobat Reader,  
Skype, Firefox, Chrome, and Mcafee Antivirus, as well as countless  
others.

One or more of these other programs is leaving the SQLite files on  
your computer.

SQLite is not a program or application.  It is not a virus.


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



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

Re: [sqlite] SQLite version 3.6.23

[Darren Duncan]

As an update, I now confirm that the version of 
http://sqlite.org/sqlite-amalgamation-3.6.23.tar.gz that was on the website 10 
minutes ago seems to be fully corrected.  That is, the file size is now normal 
and the Perl binding DBD::SQLite successfully builds and passes all its tests 
with it. -- Darren Duncan

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

Re: [sqlite] SQLite version 3.6.23

[Darren Duncan]

D. Richard Hipp wrote:
> On Mar 9, 2010, at 4:40 PM, Terence Martin wrote:
> 
>> On Tue, 9 Mar 2010 16:00:27 -0500
>> "D. Richard Hipp"  wrote:
>>
>>> SQLite version 3.6.23 is now available from the SQLite website:
>>> http://www.sqlite.org/
>>
>> On closer examination it appears that the sqlite3.[ch] files are from
>> 3.6.22 (they diff out to be identical).
>>
>> I double checked that the file I downloaded says it's 3.6.23.
> 
> Yes - there was a boo boo in the build.  Please download a fresh copy  
> and try again.

There is *still* a build problem, of a different kind.

With the current http://sqlite.org/sqlite-amalgamation-3.6.23.tar.gz, the 
sqlite3.c file is 7.3MB and the sqlite3.h file is 3.7MB, and building fails 
horribly.  Each file is about 3.7MB larger than it was in 3.6.22.

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

Re: [sqlite] SQLite version 3.6.23

[William Kyngesburye]

Are you sure this is right?  Now the header sqlite3.h is a duplicate of the 
source sqlite3.c and sqlite3.c is double the size it should be.

On Mar 9, 2010, at 4:04 PM, D. Richard Hipp wrote:

> 
> On Mar 9, 2010, at 4:40 PM, Terence Martin wrote:
> 
>> On Tue, 9 Mar 2010 16:00:27 -0500
>> "D. Richard Hipp"  wrote:
>> 
>>> SQLite version 3.6.23 is now available from the SQLite website:
>>> http://www.sqlite.org/
>>> 
>>> 
>>> As always, please let me know if you find any problems in the new
>>> release.
>>> 
>>> D. Richard Hipp
>>> d...@hwaci.com
>> 
>> I think there may be a problem with the currentl 3.6.23 amalgamation
>> download. First I noticed that it compiled the library fine but blew  
>> up
>> in shell.c (2418) with an error regarding SQLITE_CONFIG_LOG not being
>> defined.
>> 
>> On closer examination it appears that the sqlite3.[ch] files are from
>> 3.6.22 (they diff out to be identical).
>> 
>> I double checked that the file I downloaded says it's 3.6.23.
> 
> Yes - there was a boo boo in the build.  Please download a fresh copy  
> and try again.
> 
> 
>> ___
>> 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

-
William Kyngesburye 
http://www.kyngchaos.com/

"We are at war with them. Neither in hatred nor revenge and with no particular 
pleasure I shall kill every ___ I can until the war is over. That is my duty."

"Don't you even hate 'em?"

"What good would it do if I did? If all the many millions of people of the 
allied nations devoted an entire year exclusively to hating the  it 
wouldn't kill one ___ nor shorten the war one day."

 "And it might give 'em all stomach ulcers."

- Tarzan, on war

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

Re: [sqlite] SQLite version 3.6.23

[Rich Shepard]

On Tue, 9 Mar 2010, D. Richard Hipp wrote:

>> The download page no longer mentions any version of SQLite:

> Oops.  Does now.

   And I thought it was me! I got lost in the repository, started over, tried
a different route, and suddenly the familiar download page appeared. Whew!

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

Re: [sqlite] SQLite version 3.6.23

[D. Richard Hipp]

On Mar 9, 2010, at 5:07 PM, Roger Binns wrote:

> D. Richard Hipp wrote:
>> SQLite version 3.6.23 is now available from the SQLite website:  
>> http://www.sqlite.org/
>
> The download page no longer mentions any version of SQLite:
>
>  http://www.sqlite.org/download.html


Oops.  Does now.

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



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

Re: [sqlite] SQLite version 3.6.23

[Roger Binns]

D. Richard Hipp wrote:
> SQLite version 3.6.23 is now available from the SQLite website:  
> http://www.sqlite.org/

The download page no longer mentions any version of SQLite:

  http://www.sqlite.org/download.html

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

Re: [sqlite] SQLite version 3.6.23

[D. Richard Hipp]

On Mar 9, 2010, at 4:40 PM, Terence Martin wrote:

> On Tue, 9 Mar 2010 16:00:27 -0500
> "D. Richard Hipp"  wrote:
>
>> SQLite version 3.6.23 is now available from the SQLite website:
>> http://www.sqlite.org/
>>
>>
>> As always, please let me know if you find any problems in the new
>> release.
>>
>> D. Richard Hipp
>> d...@hwaci.com
>
> I think there may be a problem with the currentl 3.6.23 amalgamation
> download. First I noticed that it compiled the library fine but blew  
> up
> in shell.c (2418) with an error regarding SQLITE_CONFIG_LOG not being
> defined.
>
> On closer examination it appears that the sqlite3.[ch] files are from
> 3.6.22 (they diff out to be identical).
>
> I double checked that the file I downloaded says it's 3.6.23.

Yes - there was a boo boo in the build.  Please download a fresh copy  
and try again.


> ___
> 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

Re: [sqlite] SQLite version 3.6.23

[Darren Duncan]

D. Richard Hipp wrote:
> SQLite version 3.6.23 is now available from the SQLite website:  
> http://www.sqlite.org/
> 
> Version 3.6.23 is a regularly scheduled bimonthly release of SQLite.   
> Upgrading from version 3.6.22 is optional.  For further information on  
> the enhancements in version 3.6.23 visit:
> 
>  http://www.sqlite.org/news.html
>  http://www.sqlite.org/releaselog/3_6_23.html
> 
> As always, please let me know if you find any problems in the new  
> release.

This release was improperly packaged, or at least 
http://www.sqlite.org/sqlite-amalgamation-3.6.23.tar.gz was; it contains SQLite 
3.6.22, not 3.6.23. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.23

[Terence Martin]

On Tue, 9 Mar 2010 16:00:27 -0500
"D. Richard Hipp"  wrote:

> SQLite version 3.6.23 is now available from the SQLite website:
> http://www.sqlite.org/
> 
> 
> As always, please let me know if you find any problems in the new  
> release.
> 
> D. Richard Hipp
> d...@hwaci.com

I think there may be a problem with the currentl 3.6.23 amalgamation
download. First I noticed that it compiled the library fine but blew up
in shell.c (2418) with an error regarding SQLITE_CONFIG_LOG not being
defined. 

On closer examination it appears that the sqlite3.[ch] files are from
3.6.22 (they diff out to be identical).

I double checked that the file I downloaded says it's 3.6.23.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min() ; was: Re: if exist

[Tim Romano]

On 3/9/2010 2:13 PM, P Kishor wrote:

<>


about the following example I provided:
>>   select min(c) from T where 1=2
>>   group by foo
>>
>> returns no rows,  presumably because the null value was removed from the
>> aggregated set.
>>  

Foo was simply my shorthand for "another column, not column 'c' ", 
Sorry.  The slanty lines are just drawing attention to the group-by 
clause, which was the subject of my post.

Tim Romano



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

Re: [sqlite] INSERT performance drops down as DB grows.

[Alexey Pechnikov]

Hello!

On Tuesday 09 March 2010 20:09:04 Kiril wrote:
> Fixes? Am I doing something wrong?
> 

You may do these:
1. Use transactions
2. Increase page_size (as example, PostgreSQL use 8k pages by default)
3. Increase default_cache_size (your biggest index size must be smaller than 
page_size*cache_size)


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.23

[Alexey Pechnikov]

Hello!

Will be the very helpful sqlite3_intarray interface included to SQLite core? Or 
this is
only example for developers?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite version 3.6.23

[D. Richard Hipp]

SQLite version 3.6.23 is now available from the SQLite website:  
http://www.sqlite.org/

Version 3.6.23 is a regularly scheduled bimonthly release of SQLite.   
Upgrading from version 3.6.22 is optional.  For further information on  
the enhancements in version 3.6.23 visit:

 http://www.sqlite.org/news.html
 http://www.sqlite.org/releaselog/3_6_23.html

For the past several years, we have been doing a new release of SQLite  
every month whether it needed one or not.  Beginning this year, we are  
attempting to slow down the release cycle.  We are currently aiming  
for a release every two months.  We may move to less frequent releases  
depending on how the current schedule is received.  Of course, if a  
serious bug is found, we will make unscheduled emergency releases as  
necessary.  But in the absence of serious problems, we will be  
striving to slow down the release cycle of SQLite, in order to  
mitigate the perception of excessive "code churn".

As always, please let me know if you find any problems in the new  
release.

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



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

Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[Pavel Ivanov]

> I prefer to get any answer - since I will learn from it - than no answer at 
> all.

What is "no answer at all" to someone can be a full answer to others.
It's like when some preschooler comes to you and asks "Daddy, tell me
please, how much is 2 + 2?" and you answer "Let's see, if we take
these 2 apples and add to them these 2 apples how much we'll get?
Let's count". That's definitely "no answer at all" from your kid point
of view, but you know that it's the only correct answer to the
question.


Pavel

On Tue, Mar 9, 2010 at 2:33 PM, a1rex  wrote:
>>- Original Message 
>>From: P Kishor 
>
>>To the veterans on the list, it is very clear that no one had a "how
>>stupid are you" attitude
>
> I totally agree, and I am new here.
> I prefer to get any answer - since I will learn from it - than no answer at 
> all.
>
>>Even if a particular reply might seem brusque, in reality itis only a
>>gentle, non-malicious attempt to veer the OP into the right direction.
>>In all likelihood, in fact, from all evidence from the postings, all
>>the folks responding are probably some of the nicest people we
>>SQL-wise lesser mortals will have the privilege of learning from.
>
>  I whole heartily agree.
>
> Reagrds,
> Samuel
>
>
>      __
> Be smarter than spam. See how smart SpamGuard is at giving junk email the 
> boot with the All-new Yahoo! Mail.  Click on Options in Mail and switch to 
> New Mail today or register for free at http://mail.yahoo.ca
> ___
> 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] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[a1rex]

>- Original Message 
>From: P Kishor 

>To the veterans on the list, it is very clear that no one had a "how
>stupid are you" attitude

I totally agree, and I am new here. 
I prefer to get any answer - since I will learn from it - than no answer at 
all. 

>Even if a particular reply might seem brusque, in reality itis only a
>gentle, non-malicious attempt to veer the OP into the right direction.
>In all likelihood, in fact, from all evidence from the postings, all
>the folks responding are probably some of the nicest people we
>SQL-wise lesser mortals will have the privilege of learning from.

 I whole heartily agree. 

Reagrds,
Samuel


  __
Be smarter than spam. See how smart SpamGuard is at giving junk email the boot 
with the All-new Yahoo! Mail.  Click on Options in Mail and switch to New Mail 
today or register for free at http://mail.yahoo.ca
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min() ; was: Re: if exist

[Scott Hess]

On Tue, Mar 9, 2010 at 10:46 AM, Tim Romano  wrote:
> On 3/9/2010 10:56 AM, Scott Hess wrote:
>> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote:
>>> Of these three:
>>>
>>> select c from T where 1=2                 // returns 0 rows
>>> select min(c) from T where 1=2         // returns 1 row
>>> select min(88,99) from T where 1=2  // returns 0 rows
>>>
>>> the only case that "threw" me is the second one, where a row is returned
>>> despite a WHERE condition that should yield an empty set (or so I thought).
>>>
>> The first and last cases will run for each row in the WHERE clause.
>> The second case is aggregating over all c, and will always return one
>> row, even if the WHERE clause selects many rows, so it is consistent
>> for it to return one row if the WHERE clause selects for no rows.
>> It's as if you coded it like this:
>>
>>     SELECT min(SELECT c FROM t WHERE ...)
>>
>> meaning the minimum of that set of inputs, and if that set is empty,
>> there is no minimum, so you get a result of NULL, but not no result,
>> if you see what I mean.
>
> Thanks for the replies, Scott and Igor and Pavel and Puneet.  What I see
> is that an aggregate function needs to partner with the GROUP BY clause
> in order for nulls to be removed from the aggregated set.
>
>     select min(c) from T where 1=2
>
> returns 1 row that contains  despite the presence of the aggregate
> function
>
> and so
>
>    select min(c) is null from T where 1 =2
>
> returns true (1).  But
>
>      select min(c) from T where 1=2
>      /group by/ foo
>
> returns no rows,  presumably because the null value was removed from the
> aggregated set.

No, the null value is not removed, group-by groups the results, and
you have no results, so there are no groups, so min(c) never comes up
at all.  When you run min(c) across the entire table, the table _does_
exist, though it might be empty, so min(c) has to have a result, and
since there are no c, the result has to be NULL.  [BTW, if you had a
group-by, but no values are present for column c in that group of
rows, min(c) would also be NULL for that group.]

NULL means that there was a result, and the result was not a value, as
distinct from not having a result at all.

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

Re: [sqlite] Min() ; was: Re: if exist

[P Kishor]

On Tue, Mar 9, 2010 at 12:46 PM, Tim Romano  wrote:
> On 3/9/2010 10:56 AM, Scott Hess wrote:
>> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote:
>>
>>> Of these three:
>>>
>>> select c from T where 1=2                 // returns 0 rows
>>> select min(c) from T where 1=2         // returns 1 row
>>> select min(88,99) from T where 1=2  // returns 0 rows
>>>
>>> the only case that "threw" me is the second one, where a row is returned
>>> despite a WHERE condition that should yield an empty set (or so I thought).
>>>
>> The first and last cases will run for each row in the WHERE clause.
>> The second case is aggregating over all c, and will always return one
>> row, even if the WHERE clause selects many rows, so it is consistent
>> for it to return one row if the WHERE clause selects for no rows.
>> It's as if you coded it like this:
>>
>>     SELECT min(SELECT c FROM t WHERE ...)
>>
>> meaning the minimum of that set of inputs, and if that set is empty,
>> there is no minimum, so you get a result of NULL, but not no result,
>> if you see what I mean.
>>
>
> Thanks for the replies, Scott and Igor and Pavel and Puneet.  What I see
> is that an aggregate function needs to partner with the GROUP BY clause
> in order for nulls to be removed from the aggregated set.
>
>     select min(c) from T where 1=2
>
> returns 1 row that contains  despite the presence of the aggregate
> function
>
> and so
>
>    select min(c) is null from T where 1 =2
>
> returns true (1).  But
>
>      select min(c) from T where 1=2
>      /group by/ foo
>
> returns no rows,  presumably because the null value was removed from the
> aggregated set.
>


I don't know what "/group by/ foo" means. Is that something you tried
and actually got no rows?

If you did the following

SELECT Min(c) AS foo FROM t WHERE 1=2 GROUP BY foo;

you will get an error: SQL error: aggregate functions are not allowed
in the GROUP BY clause

If you did the following

SELECT Min(c) AS c FROM t WHERE 1=2 GROUP BY foo;

You will get an error: SQL error: no such column: foo

Here is the rule -- if you are SELECT aggregate and non aggregate
columns, then you should use GROUP BY for the non-aggregate columns.
Assuming a table t(c, foo); the following is valid

SELECT Min(c) AS minc, foo FROM t WHERE 1=2 GROUP BY foo;

Min(), Max(), Count(), Sum(), etc. are aggregate functions, and act on
the returned result set. Abs(), Glob(), Hex(), etc. are non-aggregate
functions. Min(x,y,z...) behaves like a non-aggregate function.




-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[P Kishor]

purely unsolicited, non-SQL related words here (I don't want to
classify them as 'advice') --

On Tue, Mar 9, 2010 at 12:33 PM, Ed Curren  wrote:
>
> Yes.  At times I get so wound up in the details of various parts of the code 
> that I miss the obvious of setting the stmt pointer to null before using it.  
> Thank you to Jay for reminding me of this.
>
>
>
> Your collective replies, answers and advice are very much appreciated, 
> however the "how stupid are you" attitude of some of the posts are really 
> quite ridiculous.  Splitting hairs about my phrasing lacking the specifics of 
> the levels of indirection to the statement doesn't change the fundamentals of 
> the question, which was basically "how do I know if the stmt is initalized".  
> Again thank you Jay for getting me out of the details so that the obvious 
> became clear again.
>
>

To the veterans on the list, it is very clear that no one had a "how
stupid are you" attitude, although I can understand that those new to
the list might feel that way. This is a new place. Welcome.

One rule of SQL -- at least one person who replied to you is never
wrong. I am not joking here. That one person is NEVER wrong. Even the
creator of SQLite might be wrong, but at least one responder on this
list is NEVER wrong.

Even if a particular reply might seem brusque, in reality it is only a
gentle, non-malicious attempt to veer the OP into the right direction.
In all likelihood, in fact, from all evidence from the postings, all
the folks responding are probably some of the nicest people we
SQL-wise lesser mortals will have the privilege of learning from.

Good luck in your SQL adventures... hopefully soon you will be helping others.


>
> I'll try to be more clear in future questions.
>
>> From: paiva...@gmail.com
>> Date: Tue, 9 Mar 2010 13:21:18 -0500
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas 
>> been run through the sqlite3_prepare_v2 function?
>>
>> > int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
>> > **ppStmt, const char **pzTail);
>> >
>> > Please tell me what the 4th parameter is then if it not a statement so 
>> > that I may ask you in the words you are looking for.
>>
>> It's not a statement. Speaking in Igor's words it's a _pointer_ to
>> statement. Speaking in Jay's words it's a _pointer_ to _pointer_ to
>> statement structure.
>> Usually when people say they have "function that is being passed an
>> sqlite3_stmt object" they mean this prototype:
>>
>> void f(sqlite3_stmt* stmt);
>>
>> If you will check for NULL inside this function (as Jay suggested) and
>> prepare statement if it's NULL then you will never have a non-NULL
>> value there and memory will leak from you significantly.
>>
>>
>> Pavel
>>
>> On Tue, Mar 9, 2010 at 1:07 PM, Ed Curren  wrote:
>> >
>> > According to the documentation the function prototype for 
>> > sqlite3_prepare_v2 is the following:
>> >
>> >
>> >
>> > int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
>> > **ppStmt, const char **pzTail);
>> >
>> >
>> >
>> > Please tell me what the 4th parameter is then if it not a statement so 
>> > that I may ask you in the words you are looking for.
>> >
>> >> To: sqlite-users@sqlite.org
>> >> From: itandet...@mvps.org
>> >> Date: Tue, 9 Mar 2010 13:03:00 -0500
>> >> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas 
>> >> been run through the sqlite3_prepare_v2 function?
>> >>
>> >> Ed Curren  wrote:
>> >> > Okay, let's try asking the question this way then. How do I know if
>> >> > I need to call prepare based on the condition or value or whatever of
>> >> > a statement that may or may not have already been passed to perpare
>> >> > as a parameter?
>> >>
>> >> You _cannot_ pass a statement to prepare as a parameter.
>> >>
>> >> Igor Tandetnik
>> >>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min() ; was: Re: if exist

[Igor Tandetnik]

Tim Romano  wrote:
> select min(c) from T where 1=2
> 
> returns 1 row that contains  despite the presence of the
> aggregate function

Not despite - _because_ of. If you didn't have the aggregate there, you'd get 
zero rows.

> and so
> 
>select min(c) is null from T where 1 =2
> 
> returns true (1).  But
> 
>  select min(c) from T where 1=2
>  /group by/ foo
> 
> returns no rows,  presumably because the null value was removed from
> the aggregated set.

No - because now there are no groups (as opposed to one group with zero rows as 
in the previous statement).


It is somewhat illogical - I can't think of any logical reason why an empty 
resultset (without group by) should be treated as one group with zero rows, and 
not as zero groups (and, thus, why the first statement should produce one row). 
But that's the way all SQL engines I know about have behaved for as long as I 
can remember.

Igor Tandetnik


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

Re: [sqlite] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

[Jonas Sandman]

Ok, my mistake.

On Tue, Mar 9, 2010 at 7:37 PM, Jay A. Kreibich  wrote:
> On Tue, Mar 09, 2010 at 07:30:54PM +0100, Jonas Sandman scratched on the wall:
>> Doesn't it return an array of sqlite3_stmt pointers?
>
>  No.
>
>> If you prepare this statement:
>>
>> "BEGIN; UPDATE something SET this='that'; COMMIT;"
>>
>> Then the array will contain the statement handles for the three
>> statements BEGIN, UPDATe and COMMIT.
>
>  http://sqlite.org/c3ref/prepare.html
>
>  "If pzTail is not NULL then *pzTail is made to point to the first
>  byte past the end of the first SQL statement in zSql. These routines
>  only compile the first statement in zSql, so *pzTail is left pointing
>  to what remains uncompiled."
>
>
>
>  sqlite3_exec() will process multiple statements.  Prepare will not.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] Min() ; was: Re: if exist

[Tim Romano]

On 3/9/2010 10:56 AM, Scott Hess wrote:
> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote:
>
>> Of these three:
>>
>> select c from T where 1=2 // returns 0 rows
>> select min(c) from T where 1=2 // returns 1 row
>> select min(88,99) from T where 1=2  // returns 0 rows
>>
>> the only case that "threw" me is the second one, where a row is returned
>> despite a WHERE condition that should yield an empty set (or so I thought).
>>  
> The first and last cases will run for each row in the WHERE clause.
> The second case is aggregating over all c, and will always return one
> row, even if the WHERE clause selects many rows, so it is consistent
> for it to return one row if the WHERE clause selects for no rows.
> It's as if you coded it like this:
>
> SELECT min(SELECT c FROM t WHERE ...)
>
> meaning the minimum of that set of inputs, and if that set is empty,
> there is no minimum, so you get a result of NULL, but not no result,
> if you see what I mean.
>

Thanks for the replies, Scott and Igor and Pavel and Puneet.  What I see 
is that an aggregate function needs to partner with the GROUP BY clause 
in order for nulls to be removed from the aggregated set.

 select min(c) from T where 1=2

returns 1 row that contains  despite the presence of the aggregate 
function

and so

select min(c) is null from T where 1 =2

returns true (1).  But

  select min(c) from T where 1=2
  /group by/ foo

returns no rows,  presumably because the null value was removed from the 
aggregated set.

  Regards
Tim Romano



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

Re: [sqlite] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

[Jay A. Kreibich]

On Tue, Mar 09, 2010 at 07:30:54PM +0100, Jonas Sandman scratched on the wall:
> Doesn't it return an array of sqlite3_stmt pointers?

  No.

> If you prepare this statement:
> 
> "BEGIN; UPDATE something SET this='that'; COMMIT;"
> 
> Then the array will contain the statement handles for the three
> statements BEGIN, UPDATe and COMMIT.

  http://sqlite.org/c3ref/prepare.html

  "If pzTail is not NULL then *pzTail is made to point to the first
  byte past the end of the first SQL statement in zSql. These routines
  only compile the first statement in zSql, so *pzTail is left pointing
  to what remains uncompiled."



  sqlite3_exec() will process multiple statements.  Prepare will not.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[Ed Curren]

Yes.  At times I get so wound up in the details of various parts of the code 
that I miss the obvious of setting the stmt pointer to null before using it.  
Thank you to Jay for reminding me of this.

 

Your collective replies, answers and advice are very much appreciated, however 
the "how stupid are you" attitude of some of the posts are really quite 
ridiculous.  Splitting hairs about my phrasing lacking the specifics of the 
levels of indirection to the statement doesn't change the fundamentals of the 
question, which was basically "how do I know if the stmt is initalized".  Again 
thank you Jay for getting me out of the details so that the obvious became 
clear again.

 

I'll try to be more clear in future questions.
 
> From: paiva...@gmail.com
> Date: Tue, 9 Mar 2010 13:21:18 -0500
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been 
> run through the sqlite3_prepare_v2 function?
> 
> > int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
> > **ppStmt, const char **pzTail);
> >
> > Please tell me what the 4th parameter is then if it not a statement so that 
> > I may ask you in the words you are looking for.
> 
> It's not a statement. Speaking in Igor's words it's a _pointer_ to
> statement. Speaking in Jay's words it's a _pointer_ to _pointer_ to
> statement structure.
> Usually when people say they have "function that is being passed an
> sqlite3_stmt object" they mean this prototype:
> 
> void f(sqlite3_stmt* stmt);
> 
> If you will check for NULL inside this function (as Jay suggested) and
> prepare statement if it's NULL then you will never have a non-NULL
> value there and memory will leak from you significantly.
> 
> 
> Pavel
> 
> On Tue, Mar 9, 2010 at 1:07 PM, Ed Curren  wrote:
> >
> > According to the documentation the function prototype for 
> > sqlite3_prepare_v2 is the following:
> >
> >
> >
> > int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
> > **ppStmt, const char **pzTail);
> >
> >
> >
> > Please tell me what the 4th parameter is then if it not a statement so that 
> > I may ask you in the words you are looking for.
> >
> >> To: sqlite-users@sqlite.org
> >> From: itandet...@mvps.org
> >> Date: Tue, 9 Mar 2010 13:03:00 -0500
> >> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas 
> >> been run through the sqlite3_prepare_v2 function?
> >>
> >> Ed Curren  wrote:
> >> > Okay, let's try asking the question this way then. How do I know if
> >> > I need to call prepare based on the condition or value or whatever of
> >> > a statement that may or may not have already been passed to perpare
> >> > as a parameter?
> >>
> >> You _cannot_ pass a statement to prepare as a parameter.
> >>
> >> Igor Tandetnik
> >>
> >> ___
> >> 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
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

[Jonas Sandman]

Doesn't it return an array of sqlite3_stmt pointers?

If you prepare this statement:

"BEGIN; UPDATE something SET this='that'; COMMIT;"

Then the array will contain the statement handles for the three
statements BEGIN, UPDATe and COMMIT.

/Jonas

On Tue, Mar 9, 2010 at 7:17 PM, Igor Tandetnik  wrote:
> Ed Curren  wrote:
>> According to the documentation the function prototype for
>> sqlite3_prepare_v2 is the following:
>>
>>
>>
>> int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
>> sqlite3_stmt **ppStmt, const char **pzTail);
>
> Note two stars in sqlite3_stmt **ppStmt. You pass a pointer to sqlite3_stmt* 
> (whose previous value is irrelevant and will be overwritten), and the 
> function fills it with the handle. Like this:
>
> sqlite3_stmt* stmt = NULL;
> sqlite3_prepare(db, "select * from mytable;", -1, , NULL);
> // Now stmt contains statement handle.
>
> In other words, it's an out parameter.
>
> Igor Tandetnik
>
>
> ___
> 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] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[Pavel Ivanov]

> int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
> **ppStmt, const char **pzTail);
>
> Please tell me what the 4th parameter is then if it not a statement so that I 
> may ask you in the words you are looking for.

It's not a statement. Speaking in Igor's words it's a _pointer_ to
statement. Speaking in Jay's words it's a _pointer_ to _pointer_ to
statement structure.
Usually when people say they have "function that is being passed an
sqlite3_stmt object" they mean this prototype:

void f(sqlite3_stmt* stmt);

If you will check for NULL inside this function (as Jay suggested) and
prepare statement if it's NULL then you will never have a non-NULL
value there and memory will leak from you significantly.


Pavel

On Tue, Mar 9, 2010 at 1:07 PM, Ed Curren  wrote:
>
> According to the documentation the function prototype for sqlite3_prepare_v2 
> is the following:
>
>
>
> int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
> **ppStmt, const char **pzTail);
>
>
>
> Please tell me what the 4th parameter is then if it not a statement so that I 
> may ask you in the words you are looking for.
>
>> To: sqlite-users@sqlite.org
>> From: itandet...@mvps.org
>> Date: Tue, 9 Mar 2010 13:03:00 -0500
>> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas 
>> been run through the sqlite3_prepare_v2 function?
>>
>> Ed Curren  wrote:
>> > Okay, let's try asking the question this way then. How do I know if
>> > I need to call prepare based on the condition or value or whatever of
>> > a statement that may or may not have already been passed to perpare
>> > as a parameter?
>>
>> You _cannot_ pass a statement to prepare as a parameter.
>>
>> Igor Tandetnik
>>
>> ___
>> 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] How can I query a sqlite3_stmt tofind outif ithas been run through the sqlite3_prepare_v2 function?

[Igor Tandetnik]

Ed Curren  wrote:
> According to the documentation the function prototype for
> sqlite3_prepare_v2 is the following: 
> 
> 
> 
> int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
> sqlite3_stmt **ppStmt, const char **pzTail); 

Note two stars in sqlite3_stmt **ppStmt. You pass a pointer to sqlite3_stmt* 
(whose previous value is irrelevant and will be overwritten), and the function 
fills it with the handle. Like this:

sqlite3_stmt* stmt = NULL;
sqlite3_prepare(db, "select * from mytable;", -1, , NULL);
// Now stmt contains statement handle.

In other words, it's an out parameter.

Igor Tandetnik


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

Re: [sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?

[Ed Curren]

Thank you for a straight answer Jay.
 
> Date: Tue, 9 Mar 2010 12:10:17 -0600
> From: j...@kreibi.ch
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if it has 
> been run through the sqlite3_prepare_v2 function?
> 
> On Tue, Mar 09, 2010 at 11:47:39AM -0500, Ed Curren scratched on the wall:
> > 
> > Hello all,
> > 
> > I have a function that is being passed an sqlite3_stmt object.
> 
> I assume you mean a sqlite3_stmt pointer. You should never
> instance an actual sqlite3_stmt structure yourself.
> 
> > Within this function I need to determine if the statement has been 
> > prepared. 
> > How can I accomplish this?
> 
> Ummm... it's a pointer. Set it to NULL when you initialize whatever
> data-structure it is in. When you prepare the statement, it will be
> non-NULL. If you have reason to call sqlite3_finalize(), set the
> pointer back to NULL.
> 
> Test for NULL.
> 
> -j
> 
> -- 
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
> 
> "Our opponent is an alien starship packed with atomic bombs. We have
> a protractor." "I'll go home and see if I can scrounge up a ruler
> and a piece of string." --from Anathem by Neal Stephenson
> ___
> 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] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?

[Jay A. Kreibich]

On Tue, Mar 09, 2010 at 11:47:39AM -0500, Ed Curren scratched on the wall:
> 
> Hello all,
> 
> I have a function that is being passed an sqlite3_stmt object.

  I assume you mean a sqlite3_stmt pointer.  You should never
  instance an actual sqlite3_stmt structure yourself.

> Within this function I need to determine if the statement has been prepared.  
> How can I accomplish this?

  Ummm... it's a pointer.  Set it to NULL when you initialize whatever
  data-structure it is in.  When you prepare the statement, it will be
  non-NULL.  If you have reason to call sqlite3_finalize(), set the
  pointer back to NULL.

  Test for NULL.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I query a sqlite3_stmt to find out if ithas been run through the sqlite3_prepare_v2 function?

[Pavel Ivanov]

Try to execute that statement. If you get "segmentation fault" then it
wasn't prepared yet. Though there's some 1% chance that it wouldn't
give you segmentation fault on the first execution attempt...

Pavel

P.S. To continue conversation answer the Igor's question: if you have
pointer how do you know if memory it points to was already allocated
or not?


On Tue, Mar 9, 2010 at 12:56 PM, Ed Curren  wrote:
>
> Okay, let's try asking the question this way then.  How do I know if I need 
> to call prepare based on the condition or value or whatever of a statement 
> that may or may not have already been passed to perpare as a parameter?
>
>> To: sqlite-users@sqlite.org
>> From: itandet...@mvps.org
>> Date: Tue, 9 Mar 2010 12:47:42 -0500
>> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if ithas 
>> been run through the sqlite3_prepare_v2 function?
>>
>> Ed Curren  wrote:
>> > This function will be called several times. The first time through
>> > the statment won't be prepared
>>
>> What do you mean, won't be prepared? How can one obtain a statement handle 
>> without calling prepare?
>>
>> > so in that case the function will
>> > call sqlite3_preapre_v2 to prepare the statement.
>>
>> Note that sqlite3_preapre_v2 doesn't take a statement as a parameter. It 
>> takes a database connection and a SQL string, and _produces_ the statement 
>> from them.
>>
>> Imagine one saying "I have a FILE* handle and I need to call fopen on it", 
>> or "I have a void* pointer and I need to call malloc on it". Well, your 
>> question makes about as much sense.
>>
>> Igor Tandetnik
>>
>>
>> ___
>> 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] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[Ed Curren]

According to the documentation the function prototype for sqlite3_prepare_v2 is 
the following:

 

int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt 
**ppStmt, const char **pzTail);

 

Please tell me what the 4th parameter is then if it not a statement so that I 
may ask you in the words you are looking for.
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Tue, 9 Mar 2010 13:03:00 -0500
> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been 
> run through the sqlite3_prepare_v2 function?
> 
> Ed Curren  wrote:
> > Okay, let's try asking the question this way then. How do I know if
> > I need to call prepare based on the condition or value or whatever of
> > a statement that may or may not have already been passed to perpare
> > as a parameter? 
> 
> You _cannot_ pass a statement to prepare as a parameter.
> 
> Igor Tandetnik
> 
> ___
> 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] Feasability of a Range function

[Jean-Christophe Deschamps]

Hi Alexey,


>1. See internal sqlite instarray interface:
>http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c
>http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h
>http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test
>
>Note: http://sqlite.mobigroup.ru include official SQLite trunk
>branch but anonymous autorization is not required.
>
>2. See my Tablefunc extension here:
>http://sqlite.mobigroup.ru/src/dir?name=ext/tablefunc

Warm thanks to the pointer.  That will prove helpful for other purpose 
as well (testing, as I believe this is why you wrote it).

Cheers,



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

Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?

[Igor Tandetnik]

Ed Curren  wrote:
> Okay, let's try asking the question this way then.  How do I know if
> I need to call prepare based on the condition or value or whatever of
> a statement that may or may not have already been passed to perpare
> as a parameter?   

You _cannot_ pass a statement to prepare as a parameter.

Igor Tandetnik

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

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses)

[Luke Evans]

Hi guys,

Had to take a break for a couple of days from my SQLite experiments, but back 
on it now.

Pavel, regarding the question about VFS, I'm not using one to my knowledge and 
have set the "name of VFS module" to NULL in sqlite3_open_v2.  Maybe NULL means 
I'm using the standard VFS, but in any case, not a "non-standard" one.
I'm selecting from a real table.

Here are some more timings...

Shared cache ON.  6 of the same query issued (as before) within the 
multithreaded scenario.  All the queries bunch up and report complete more or 
less together.

2010-03-09 09:32:33.013 SQLiteTest[16208:a0f] SQLite says multithreading is ON
2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SQLite's multithreading value is 2
2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SHARED CACHE IS ON
2010-03-09 09:32:33.017 SQLiteTest[16208:1b03] Starting query on thread 
{name = (null), num = 3}
2010-03-09 09:32:33.018 SQLiteTest[16208:5603] Starting query on thread 
{name = (null), num = 4}
2010-03-09 09:32:33.017 SQLiteTest[16208:4403] Starting query on thread 
{name = (null), num = 2}
2010-03-09 09:32:33.018 SQLiteTest[16208:5503] Starting query on thread 
{name = (null), num = 5}
2010-03-09 09:32:33.019 SQLiteTest[16208:5703] Starting query on thread 
{name = (null), num = 6}
2010-03-09 09:32:33.019 SQLiteTest[16208:5c03] Starting query on thread 
{name = (null), num = 7}
2010-03-09 09:32:41.359 SQLiteTest[16208:5603] Finished query on thread 
{name = (null), num = 4} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:1b03] Finished query on thread 
{name = (null), num = 3} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:4403] Finished query on thread 
{name = (null), num = 2} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:5503] Finished query on thread 
{name = (null), num = 5} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:5703] Finished query on thread 
{name = (null), num = 6} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:5c03] Finished query on thread 
{name = (null), num = 7} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:a0f] Finished all queries in 8.34s



6 of the same query executed in separate processes.
The query time is essentially the same as when you issue just a single query in 
the 'multithreaded' configuration above - as one would expect.

2010-03-09 09:47:02.729 SQLiteTest[17083:a0f] SQLite says multithreading is ON
2010-03-09 09:47:02.731 SQLiteTest[17083:a0f] SQLite's multithreading value is 2
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
On pid 17089, 3717 rows read in approx 2s
On pid 17088, 3717 rows read in approx 2s
On pid 17093, 3717 rows read in approx 2s
On pid 17090, 3717 rows read in approx 2s
On pid 17091, 3717 rows read in approx 2s
On pid 17092, 3717 rows read in approx 2s
2010-03-09 09:47:04.323 SQLiteTest[17083:a0f] Finished all queries in 1.59s



Same as above (6 queries in separate processes), but now each process has 
SHARED CACHE=ON.
There's no significant difference in timing (within normal variance).

22010-03-09 09:49:41.217 SQLiteTest[17272:a0f] SQLite says multithreading is ON
2010-03-09 09:49:41.219 SQLiteTest[17272:a0f] SQLite's multithreading value is 2
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
Cache size is 10
Cache size is 10
Page size is 32768
Page size is 32768
Temp store is 2
Temp store is 2
Synchronous is 0
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
On pid 17278, 3717 rows read in approx 1s
On pid 17281, 3717 rows read in approx 1s
On pid 17280, 3717 rows read in approx 1s
On pid 17277, 3717 rows read in approx 1s
On pid 17276, 3717 rows read in approx 1s
On pid 17279, 3717 rows read in approx 1s
2010-03-09 09:49:42.697 SQLiteTest[17272:a0f] Finished all queries in 1.48s





On 2010-03-05, at 12:45 PM, Pavel Ivanov wrote:

> Just a bit of thought here: if opening was at fault then 5 queries
> started at the same time would finish in different times (first open
> 

Re: [sqlite] How can I query a sqlite3_stmt to find out if ithas been run through the sqlite3_prepare_v2 function?

[Ed Curren]

Okay, let's try asking the question this way then.  How do I know if I need to 
call prepare based on the condition or value or whatever of a statement that 
may or may not have already been passed to perpare as a parameter?
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Tue, 9 Mar 2010 12:47:42 -0500
> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if ithas 
> been run through the sqlite3_prepare_v2 function?
> 
> Ed Curren  wrote:
> > This function will be called several times. The first time through
> > the statment won't be prepared
> 
> What do you mean, won't be prepared? How can one obtain a statement handle 
> without calling prepare?
> 
> > so in that case the function will
> > call sqlite3_preapre_v2 to prepare the statement.
> 
> Note that sqlite3_preapre_v2 doesn't take a statement as a parameter. It 
> takes a database connection and a SQL string, and _produces_ the statement 
> from them.
> 
> Imagine one saying "I have a FILE* handle and I need to call fopen on it", or 
> "I have a void* pointer and I need to call malloc on it". Well, your question 
> makes about as much sense.
> 
> Igor Tandetnik
> 
> 
> ___
> 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] How can I query a sqlite3_stmt to find out if ithas been run through the sqlite3_prepare_v2 function?

[Igor Tandetnik]

Ed Curren  wrote:
> This function will be called several times.  The first time through
> the statment won't be prepared

What do you mean, won't be prepared? How can one obtain a statement handle 
without calling prepare?

> so in that case the function will
> call sqlite3_preapre_v2 to prepare the statement.

Note that sqlite3_preapre_v2 doesn't take a statement as a parameter. It takes 
a database connection and a SQL string, and _produces_ the statement from them.

Imagine one saying "I have a FILE* handle and I need to call fopen on it", or 
"I have a void* pointer and I need to call malloc on it". Well, your question 
makes about as much sense.

Igor Tandetnik


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

Re: [sqlite] Problem with SQLite in BCB 4

[a1rex]

Thank you very much for your clarification! This is what I suspected.

Regards,
Samuel


- Original Message 
From: Igor Tandetnik 

a1rex wrote:
> What about void *p  =  sqlite3_column_blob()?
> From my tests it looks that pointer p survives sqlite3_finalize().
> Is it just a coincidence?

It "survives" in the same sense as in this example:

char* p = (char*)malloc(10);
strcpy(p, "Hello");
free(p);
printf(p);

Chances are high the last line will print "Hello", simply because the now-free 
memory referred to by now-dangling pointer p didn't have the chance to be 
overwritten with something else yet



  __
Looking for the perfect gift? Give the gift of Flickr! 

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

Re: [sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?

[Ed Curren]

Hi Igor,

This function will be called several times.  The first time through the 
statment won't be prepared, so in that case the function will call 
sqlite3_preapre_v2 to prepare the statement.  Subsequent calls to the function 
will have the statment that is already prepared, so I do not want to attempt to 
call sqlite3_prepare_v2 again on a statment that is already prepared.

 

Thanks very much for your help.
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Tue, 9 Mar 2010 12:04:15 -0500
> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if it has 
> been run through the sqlite3_prepare_v2 function?
> 
> Ed Curren  wrote:
> > I have a function that is being passed an sqlite3_stmt object. 
> > Within this function I need to determine if the statement has been
> > prepared. How can I accomplish this? 
> 
> Where else would a statement handle come from, if not from 
> sqlite3_prepare[_v2] ? What precisely is the other possibility you are trying 
> to defend against?
> 
> Igor Tandetnik
> 
> ___
> 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] Feasability of a Range function

[Alexey Pechnikov]

Hello!

1. See internal sqlite instarray interface:
http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c
http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h
http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test

Note: http://sqlite.mobigroup.ru include official SQLite trunk
branch but anonymous autorization is not required.

2. See my Tablefunc extension here:
http://sqlite.mobigroup.ru/src/dir?name=ext/tablefunc

As example:

create table testrange(rowid);
select intrange2table (1,10,1,'testrange');
select * from testrange;
1
2
3
4
5
6
7
8
9
10

select intrange2table (100,1000,100,'testrange');
select * from testrange;
1
2
3
4
5
6
7
8
9
10
100
200
300
400
500
600
700
800
900
1000


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT performance drops down as DB grows.

[Pavel Ivanov]

Did you try to raise value of cache_size? Do you execute any other
queries on the same database besides INSERT?

Pavel

On Tue, Mar 9, 2010 at 12:09 PM, Kiril  wrote:
> Hi all,
>
> I am facing performance issues after few thousand inserts.
> I have a DB with 3 tables, few columns each, each one has auto-increment ID.
> These tables are joined by ID, so after each INSERT, I read back the
> last_insert_rowid() from 2 tables.
> One or two columns per table are indexed.
>
> I am using transaction, of course. Also - set PRAGMA synchronous = 0
>
> All works pretty fast at the beginning - ~300 INSERTS per second, but then
> progressively performance drops down to 20 INSERTS per second as the DB
> grows to 200 Mb in size
>
> Any idea why and if this is expected behavior?
> Fixes? Am I doing something wrong?
>
> Thanks in advance,
>
> Kiril.
> ___
> 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] Newbie question - SQLite the best choice?

[Alexey Pechnikov]

Hello!

I think your structure is not good enough. You may use
cookie-based database selection instead and doesn't
store user information into your "Master DB". 

The algorithm is like to:
Username -> user_id -> check password by user_id database

As example:
User enter USERNAME and PASSWORD and we check
PASSWORD in database file md5(USERNAME).db

You may block brutforces in your application. For valid users
performance is usually fine by this schema.

P.S. For javascript auto-complete of user names is possible
to build list of usernames periodically.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?

[Igor Tandetnik]

Ed Curren  wrote:
> I have a function that is being passed an sqlite3_stmt object. 
> Within this function I need to determine if the statement has been
> prepared.  How can I accomplish this?  

Where else would a statement handle come from, if not from sqlite3_prepare[_v2] 
? What precisely is the other possibility you are trying to defend against?

Igor Tandetnik

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

Re: [sqlite] Min() ; was: Re: if exist

[Igor Tandetnik]

Tim Romano  wrote:
> Of these three:
> 
> select c from T where 1=2 // returns 0 rows
> select min(c) from T where 1=2 // returns 1 row
> select min(88,99) from T where 1=2  // returns 0 rows
> 
> 
> the only case that "threw" me is the second one, where a row is
> returned despite a WHERE condition that should yield an empty set (or
> so I thought).  

Aggregate functions work this way, yes.

> Regarding your point about the GROUP BY clause -- I'm not sure what
> you mean by "non-aggregate columns".

Expressions in the SELECT list that don't mention aggregate functions. As in

select x, min(y) from T;

>  Are you referring to a query
> where one wants to find the minimum value in a given column for the
> /entire/ table?   
> 
> select min(askingprice) from cars4sale
> group by rowid   //<= a group by is required here?

A GROUP BY doesn't make sense here. Without GROUP BY, the whole table (more 
precisely, all the rows matching the WHERE clause, if any) is in one group, and 
minimum is calculated across that group (if the group is empty, min() produces 
null). With GROUP BY clause, every individual row is in its own separate group, 
and minimum is calculated for each row, which is rather pointless.

Igor Tandetnik


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

Re: [sqlite] if exist

[Igor Tandetnik]

Andrea Galeazzi wrote:
> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
> INT length.
> I need a statement in order to yield 0 when the key doesn't exist.

Well, "select 0;" fits your spec (you never said what should be returned when 
the key does exist). I would hazard a guess that you meant something like this:

select coalesce((select length from T where id=?), 0);

-- 
Igor Tandetnik

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

[sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?

[Ed Curren]

Hello all,

I have a function that is being passed an sqlite3_stmt object.  Within this 
function I need to determine if the statement has been prepared.  How can I 
accomplish this?

 

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

Re: [sqlite] Min() ; was: Re: if exist

[P Kishor]

On Tue, Mar 9, 2010 at 9:56 AM, Scott Hess  wrote:
> On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote:
>> Of these three:
>>
>> select c from T where 1=2                 // returns 0 rows
>> select min(c) from T where 1=2         // returns 1 row
>> select min(88,99) from T where 1=2  // returns 0 rows
>>
>> the only case that "threw" me is the second one, where a row is returned
>> despite a WHERE condition that should yield an empty set (or so I thought).
>
> The first and last cases will run for each row in the WHERE clause.
> The second case is aggregating over all c, and will always return one
> row, even if the WHERE clause selects many rows, so it is consistent
> for it to return one row if the WHERE clause selects for no rows.
> It's as if you coded it like this:
>
>   SELECT min(SELECT c FROM t WHERE ...)
>
> meaning the minimum of that set of inputs, and if that set is empty,
> there is no minimum, so you get a result of NULL, but not no result,
> if you see what I mean.
>

That is a great explanation. Another way to think about it is to go
from right to left. In first and third cases, the WHERE clause is true
for 0 rows, and so SELECT is unable to return anything. In the second
case also the WHERE clause returns 0 rows, but there is no Min defined
for 0 rows, so a NULL is returned.

Now, you may ask, how is Min(88,89) different from Min(c), and that is
where the note from the docs I sent comes in. The Min(x,y..) form
makes min() perform like a simple function such as Sin() or Left(),
etc. But when given a single argument, Min() operates as an aggregate
function if given only a single argument. Consider

sqlite> SELECT Min(88,89) WHERE 1=2;
sqlite> SELECT Min(88) WHERE 1=2;
Min(88)
--

sqlite>


Note that Min(88) is returning a NULL row while Min(88,89) is not
returning anything.





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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min() ; was: Re: if exist

[Scott Hess]

On Tue, Mar 9, 2010 at 7:15 AM, Tim Romano  wrote:
> Of these three:
>
> select c from T where 1=2                 // returns 0 rows
> select min(c) from T where 1=2         // returns 1 row
> select min(88,99) from T where 1=2  // returns 0 rows
>
> the only case that "threw" me is the second one, where a row is returned
> despite a WHERE condition that should yield an empty set (or so I thought).

The first and last cases will run for each row in the WHERE clause.
The second case is aggregating over all c, and will always return one
row, even if the WHERE clause selects many rows, so it is consistent
for it to return one row if the WHERE clause selects for no rows.
It's as if you coded it like this:

   SELECT min(SELECT c FROM t WHERE ...)

meaning the minimum of that set of inputs, and if that set is empty,
there is no minimum, so you get a result of NULL, but not no result,
if you see what I mean.

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

Re: [sqlite] non-blocking concurrent read questions

[Pavel Ivanov]

I see your point and your algorithm looks pretty reasonable. Except
that again it can be reasonable for you but not for general case which
is SQLite for. You have one flaw: SQLite doesn't "read page list in
cache", it reads every page that is needed during query execution. So
if one applied your algorithm generally then he would read first page
of database (to check change counter which is already there now) after
reading of each database page. This will double disk I/O at cold start
and significantly slow down execution if all data that query needs is
already in cache (first page should still be read from disk at least
after each step through result set).

I understand your desire to use SQLite for your application. So if you
can afford paying some fee for its customization for your specific
needs (instead of trying to change sources by yourself) then I'd
suggest you to contact company developing it
(http://www.hwaci.com/contact.html). I don't know their pricing but I
believe they will be able to help you.


Pavel

On Tue, Mar 9, 2010 at 9:43 AM, Gabriel Corneanu
 wrote:
>  Hello again,
>
> I start with your final words, "it's a general database engine".
> On the main page it writes:
> "Think of SQLite not as a replacement for Oracle but as a replacement for
> fopen()"
> That's why I try sqlite and not other database (I actually tried embedded
> innodb but sqlite was muuuch better / faster from the first try).
>
> Now the rest.
> I'm not saying that I need 80 rec/s, just that I reached this speed with
> hdf5. Our devices are currently rated at "only" >3 "hits"/sec; therefore
> my results at >15 rec/sec are good enough for the start.
> I MIGHT need high data rate (>20MB/s), but that includes blobs (waveform)
> data. When using blobs sqlite can also achieve high numbers in MB/s (of
> course less records).
>
> Obviously we do have now a proprietary format; but as with any format,
> changes are required to make room for new features. We tried to design a
> much better, self describing, extensible file format; at the end I realize
> that it's not that far from existing solutions like hdf5 or sqlite. That's
> why I'm here...
>  Currently I have only 1 binary structure (union), but I would change that
> to 2 or 3 tables, one for each record type.
>
> I don't need any specific indexes during writing (other than native order);
> for later analysis optimizations, I might create some other indexes.
> Similar to what you wrote about B-trees for pages, I assumed that indexes
> are equally problematic on writing.
>
> I do understand that it is not feasible to change much about how it works
> right now, to make it valid for concurrent access.
>
> But I still have the feeling that it could work pretty well using this kind
> of model (let's call it "optimistic" locking):
> for the writer:
> 1. on write begin (write lock), increase a special counter (n -> n+1, odd
> value); this would mark pending changes
> 2. write new data/pages/references as usual
> 3. on write end (release write lock), increase the counter again (even
> value); this would mark the page structure as valid
> for the reader:
> 4. on begin read (shared lock), read (and store) the counter; if odd, a
> writer is active and should return "busy" immediately (just like now).
> 5. read the page list in cache (I assume it is doing this right now)
> 6. read the counter again and compare with the initial value; if no change
> is detected then page list is valid and it can read existing data. If change
> is detected, it should signal "busy" just like 4.
> 7. when releasing the shared lock, the counter can be checked again (against
> the value read on 4) and signal whether the data was changed or not. It is
> up to the user to decide what to do with the "dirty" data.
>
> For points 4, 6 it should either fail, or trying like now in a loop for a
> certain period.
> Even better, the change counter could (or actually "should") be per table.
> So for writing there would only be trivial changes (simple counter
> increment); for reading there would be some simple read/check of a value
> before using the page list.
>
> These relatively simple changes would open it for other applications; by
> searching I saw that I'm not the only one who needs this kind of behavior.
>
> Regards,
> Gabriel Corneanu
> ___
> 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] non-blocking concurrent read questions

[Pavel Ivanov]

> You said that only references are changed, right? That means, during appends
> the page content is still valid even if B-trees structure is changed because
> of references.

If B-tree is implemented with concurrency in mind then yes, but SQLite
wasn't implemented this way. First of all when page splits and some
keys of B-tree are left in the same page and another part goes to
newly created one some specific actions should be made to be sure that
reader looking for key that is gone was able to find newly created
page somehow. And another problem is that SQLite makes all changes in
the cache first and only then writes all changed pages to disk in
"random" order. So in the middle of this process you can get
references to non-existent pages or some other inconsistencies.


Pavel

On Tue, Mar 9, 2010 at 9:53 AM, Gabriel Corneanu
 wrote:
> Sure I could have some kind of intermediate storage, but that would mean
> unnecessary data moving / copying.
> I really hope that I'll find some time and try to study the source and
> eventually implement my ideas (maybe others find it interesting and/or
> useful too).
>
> You said that only references are changed, right? That means, during appends
> the page content is still valid even if B-trees structure is changed because
> of references.
>
> PS: what should I add to make the messages as reply (I'm using the web
> interface for email)?
>
> Regards,
> Gabriel
> ___
> 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] non-blocking concurrent read questions

[Max Vlasov]

> The writer application must be failsafe, as much as possible (acoustic
> emission recording devices); I simply can not afford that a reader makes a
> select and because of a programming error the acquisition be blocked. I had
> this just by opening sqliteman.
>
> The recording rate is variable; using a test structure (~14 fields in 1
> table, all integers 32/64bit) I was able to achieve ~9 records /sec
> with
> sqlite API, which was not really good enough (there are is no jurnal; I
> don't need any rollback / consistency check).
>


If you still prefer sqlite for some reasons, how about three-parts approach.
You have raw data writer working with the speed of I/O system and making
segmented output in very simple format, lazy writer that consumes non-active
(passed) segments writing them to the sqlite base, and general reader doing
necessary reads. In this case reader and write don't have strict
requirements about locking (at least the reader can be more important in
this case). You can tune their relationship in a way when raw writer and
sqlite writer are usually synchronized, but if the reader is more active
there are temporary more non-consumed segments than before.

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

Re: [sqlite] Min() ; was: Re: if exist

[Pavel Ivanov]

> sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
> m
> --
> sqlite> SELECT Min(m) FROM t;
> Min(m)
> --
> 88

Puneet, note that you probably missed one empty row of terminal output
in the first query above and when there's no row returned sqlite3
command line utility doesn't print any headers information. Try this
query to check things out: SELECT COUNT(*) FROM (SELECT Min(m) AS m
FROM t WHERE 1=2).


Pavel

On Tue, Mar 9, 2010 at 8:04 AM, P Kishor  wrote:
> On Tue, Mar 9, 2010 at 6:18 AM, Tim Romano  wrote:
>> Wrapping a column in the min() function causes a query that returns no
>> rows to return a  row?
>>
>> select c from T where 1=2                 // returns 0 rows
>
> The above is correct SQL, and the answer is correct.
>
>> select min(c) from T where 1=2         // returns 1 row
>
> You should be getting 0 rows; I do. What version of sqlite are you running?
>
> SQLite version 3.6.19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t (m);
> sqlite> INSERT INTO t VALUES (88);
> sqlite> INSERT INTO t VALUES (89);
> sqlite> SELECT * FROM t;
> m
> --
> 88
> 89
> sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
> m
> --
> sqlite> SELECT Min(m) FROM t;
> Min(m)
> --
> 88
>
>
>> select min(88,99) from T where 1=2  // returns 0 rows
>
> The above is correct SQL and the answer is correct. Per the docs,
> "Note that min() is a simple function when it has 2 or more arguments
> but operates as an aggregate function if given only a single
> argument."
>
> Finally, note that when returning both aggregate and non-aggregate
> columns, you should use the GROUP BY clause. I believe that SQLite
> will return rows even without GROUP BY, but the answer may be
> undependable.
>
>>
>> Tim Romano
>>
> ..
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> 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] Min() ; was: Re: if exist

[P Kishor]

On Tue, Mar 9, 2010 at 7:34 AM, Pavel Ivanov  wrote:
>> sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
>> m
>> --
>> sqlite> SELECT Min(m) FROM t;
>> Min(m)
>> --
>> 88
>
> Puneet, note that you probably missed one empty row of terminal output
> in the first query above and when there's no row returned sqlite3
> command line utility doesn't print any headers information. Try this
> query to check things out: SELECT COUNT(*) FROM (SELECT Min(m) AS m
> FROM t WHERE 1=2).
>
>


Yes, you are absolutely correct.


> Pavel
>
> On Tue, Mar 9, 2010 at 8:04 AM, P Kishor  wrote:
>> On Tue, Mar 9, 2010 at 6:18 AM, Tim Romano  wrote:
>>> Wrapping a column in the min() function causes a query that returns no
>>> rows to return a  row?
>>>
>>> select c from T where 1=2                 // returns 0 rows
>>
>> The above is correct SQL, and the answer is correct.
>>
>>> select min(c) from T where 1=2         // returns 1 row
>>
>> You should be getting 0 rows; I do. What version of sqlite are you running?
>>
>> SQLite version 3.6.19
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE t (m);
>> sqlite> INSERT INTO t VALUES (88);
>> sqlite> INSERT INTO t VALUES (89);
>> sqlite> SELECT * FROM t;
>> m
>> --
>> 88
>> 89
>> sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
>> m
>> --
>> sqlite> SELECT Min(m) FROM t;
>> Min(m)
>> --
>> 88
>>
>>
>>> select min(88,99) from T where 1=2  // returns 0 rows
>>
>> The above is correct SQL and the answer is correct. Per the docs,
>> "Note that min() is a simple function when it has 2 or more arguments
>> but operates as an aggregate function if given only a single
>> argument."
>>
>> Finally, note that when returning both aggregate and non-aggregate
>> columns, you should use the GROUP BY clause. I believe that SQLite
>> will return rows even without GROUP BY, but the answer may be
>> undependable.
>>
>>>
>>> Tim Romano
>>>
>> ..
>>
>>


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] non-blocking concurrent read questions

[Pavel Ivanov]

Probably I will express just my opinion but still...

Gabriel, what you described is clearly not a good or anywhere intended
use of SQLite. If you need writing at the rate of 800,000 records per
second you can't afford using database engine for this. Much better
option for you will be to have some file mapped to your application
memory and to write directly to that memory in your proprietary
format. Then you should think how you will deal with crashes and
inconsistencies inside the file (of course if you need to store those
data for long time and don't use your database as simple pipe between
applications).

And below are some comments about your vision of how things work.

> Sure the indexes might create problems... I only use the build-in rowid
> during writing.

What do you mean by "during writing"? Indexes either exist in database
or not and so they used during writing or not used at all. So do you
have indexes? Do you have only one table in the database?

> So the question are: how is expanding working? would it fit such model?

Definitely not. You may want to read this http://www.sqlite.org/fileformat.html.

> A trivial example:
> page count = 5, page list = 1,2,3,4,5
> after writing 2 new pages
> page list append 6,7 (does not change the old list entries) then set page
> count = 7 (atomic)
> The reader reads either 5 or 7; in any case, the page lists are valid.
> If necessary, a (transaction) counter might be used to detect "dirty" reads
> (when really incompatible).

Don't forget that tables are recorded in a form of B-trees, not as
simple heaps (as probably is in some other DBMS). So even if you just
append to the table and never delete or update it changes go to
several different pages anyway. Look what happens. First of all
there's no "page count" for the table - everything is made using
references. So let's say page 1 is a head of B-tree for the table, it
contains link to page 2, it contains links to page 3, 4 and 5. Let's
say you're adding one more page 6. It means you have to add link to it
to page 2 (jeopardizing the contents that reader should read). Let's
say you're adding page 7, it overflows page 2, so it should be split
(added one more page 8) and link should be added to page 1. Things are
even worse if page 1 overflows - you have to split it, add new head of
B-tree, rewrite head into page 1 and move old contents of page one
into some other page... As you see there're a lot of places where
reader could read inconsistent information from database. Of course
you can find a way to solve all those problems by selecting correct
sequence of writing to disk. But SQLite doesn't do that and won't do
that because it has locking and it is won't be adjusted to server such
very specific usage - it's a general database engine.


Pavel

On Tue, Mar 9, 2010 at 7:43 AM, Gabriel Corneanu
 wrote:
> Hello,
> Thanks for your attention,
>
>
>> Although speaking generally such method could be used in some situations, I
>> don't think it's good to allow to use it even with a "i know what I'm
>> doing"
>> pragma. Any structured file (sqlite is an example) have internal
>> dependencies. One of the reasons to block is to write different parts of
>> structured data together without intervention from other parties in order
>> to
>> keep the data integral. Imagine writing cache that kept changes for your
>> writer and finally it needed to flush the data and at the same time your
>> "anytime" reader started to perform some query in the middle of this
>> multiply pages writing process. I can't predict whether the reader will end
>> with some valid result or it will return with "database malformed" error.
>>
>> Instead consider changing your own logic. You wrote "without* any
>> possibility to be blocked". I suppose you already have a perfect writer
>> that
>> fits your needs, but if you post some info about the nature of your writer
>> and reader (records per second and something like this), it would help to
>> be
>> more specific with answers.
>>
>> Max
>
>
> The writer application must be failsafe, as much as possible (acoustic
> emission recording devices); I simply can not afford that a reader makes a
> select and because of a programming error the acquisition be blocked. I had
> this just by opening sqliteman.
>
> The recording rate is variable; using a test structure (~14 fields in 1
> table, all integers 32/64bit) I was able to achieve ~9 records /sec with
> sqlite API, which was not really good enough (there are is no jurnal; I
> don't need any rollback / consistency check).
> Then I was able to make a virtual table wrapper and insert/select and
> reached ~17 rec/sec, which is already a big step forward. I think is not
> planned, but I would like to have some bulk insert API (not sql) to speed up
> things; hdf5 with packet table API reached ~7-8 00 000 rec/sec and is
> essentially IO bound. I do not expect this kind of performance from sqlite
> soon... Maybe cache settings 

Re: [sqlite] Min() ; was: Re: if exist

[P Kishor]

On Tue, Mar 9, 2010 at 6:18 AM, Tim Romano  wrote:
> Wrapping a column in the min() function causes a query that returns no
> rows to return a  row?
>
> select c from T where 1=2                 // returns 0 rows

The above is correct SQL, and the answer is correct.

> select min(c) from T where 1=2         // returns 1 row

You should be getting 0 rows; I do. What version of sqlite are you running?

SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (m);
sqlite> INSERT INTO t VALUES (88);
sqlite> INSERT INTO t VALUES (89);
sqlite> SELECT * FROM t;
m
--
88
89
sqlite> SELECT Min(m) AS m FROM t WHERE 1=2;
m
--
sqlite> SELECT Min(m) FROM t;
Min(m)
--
88


> select min(88,99) from T where 1=2  // returns 0 rows

The above is correct SQL and the answer is correct. Per the docs,
"Note that min() is a simple function when it has 2 or more arguments
but operates as an aggregate function if given only a single
argument."

Finally, note that when returning both aggregate and non-aggregate
columns, you should use the GROUP BY clause. I believe that SQLite
will return rows even without GROUP BY, but the answer may be
undependable.

>
> Tim Romano
>
..


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] non-blocking concurrent read questions

[Gabriel Corneanu]

Hello,
Thanks for your attention,


> Although speaking generally such method could be used in some situations, I
> don't think it's good to allow to use it even with a "i know what I'm
> doing"
> pragma. Any structured file (sqlite is an example) have internal
> dependencies. One of the reasons to block is to write different parts of
> structured data together without intervention from other parties in order
> to
> keep the data integral. Imagine writing cache that kept changes for your
> writer and finally it needed to flush the data and at the same time your
> "anytime" reader started to perform some query in the middle of this
> multiply pages writing process. I can't predict whether the reader will end
> with some valid result or it will return with "database malformed" error.
>
> Instead consider changing your own logic. You wrote "without* any
> possibility to be blocked". I suppose you already have a perfect writer
> that
> fits your needs, but if you post some info about the nature of your writer
> and reader (records per second and something like this), it would help to
> be
> more specific with answers.
>
> Max


The writer application must be failsafe, as much as possible (acoustic
emission recording devices); I simply can not afford that a reader makes a
select and because of a programming error the acquisition be blocked. I had
this just by opening sqliteman.

The recording rate is variable; using a test structure (~14 fields in 1
table, all integers 32/64bit) I was able to achieve ~9 records /sec with
sqlite API, which was not really good enough (there are is no jurnal; I
don't need any rollback / consistency check).
Then I was able to make a virtual table wrapper and insert/select and
reached ~17 rec/sec, which is already a big step forward. I think is not
planned, but I would like to have some bulk insert API (not sql) to speed up
things; hdf5 with packet table API reached ~7-8 00 000 rec/sec and is
essentially IO bound. I do not expect this kind of performance from sqlite
soon... Maybe cache settings might also help, suggestions are welcome.
For reading it is much better; here I also used an workaround, a fake
aggregated function ("store(...)") is working much faster (and simpler to
implement than a virtual table).
So the reader can read faster anyway, it just needs to "follow" the data;
small delays are normal and acceptable.


Back to the technical problem; roughly described, I would expect that a
table is expanded first with new data then (on transaction end, I used
blocks of ~1000 records) update some metadata information about table pages
(which pages are used for the data). I image that it could be made somehow
safe (I'm sorry but I have no idea about the actual implementation): write
data (old data remains valid), invalidate page list (if necessary, one
single value which would be atomic on most systems if properly aligned),
write the new page list, then mark it as valid again   (nothing affects
current behavior when using existing locking anyway).
The reader could read without any problem "old" data; it would only need to
detect a "dirty" read if the page list is changed to be incompatible (if new
pages are appended only, the page list might always be valid). In this case
the reader would work in a short loop (similar to the current behavior when
locking) trying to get a "clean" page list.

A trivial example:
page count = 5, page list = 1,2,3,4,5
after writing 2 new pages
page list append 6,7 (does not change the old list entries) then set page
count = 7 (atomic)
The reader reads either 5 or 7; in any case, the page lists are valid.
If necessary, a (transaction) counter might be used to detect "dirty" reads
(when really incompatible).

So the question are: how is expanding working? would it fit such model?
Sure the indexes might create problems... I only use the build-in rowid
during writing.

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

[sqlite] non-blocking concurrent read questions

[Gabriel Corneanu]

 Hello again,

I start with your final words, "it's a general database engine".
On the main page it writes:
"Think of SQLite not as a replacement for Oracle but as a replacement for
fopen()"
That's why I try sqlite and not other database (I actually tried embedded
innodb but sqlite was muuuch better / faster from the first try).

Now the rest.
I'm not saying that I need 80 rec/s, just that I reached this speed with
hdf5. Our devices are currently rated at "only" >3 "hits"/sec; therefore
my results at >15 rec/sec are good enough for the start.
I MIGHT need high data rate (>20MB/s), but that includes blobs (waveform)
data. When using blobs sqlite can also achieve high numbers in MB/s (of
course less records).

Obviously we do have now a proprietary format; but as with any format,
changes are required to make room for new features. We tried to design a
much better, self describing, extensible file format; at the end I realize
that it's not that far from existing solutions like hdf5 or sqlite. That's
why I'm here...
 Currently I have only 1 binary structure (union), but I would change that
to 2 or 3 tables, one for each record type.

I don't need any specific indexes during writing (other than native order);
for later analysis optimizations, I might create some other indexes.
Similar to what you wrote about B-trees for pages, I assumed that indexes
are equally problematic on writing.

I do understand that it is not feasible to change much about how it works
right now, to make it valid for concurrent access.

But I still have the feeling that it could work pretty well using this kind
of model (let's call it "optimistic" locking):
for the writer:
1. on write begin (write lock), increase a special counter (n -> n+1, odd
value); this would mark pending changes
2. write new data/pages/references as usual
3. on write end (release write lock), increase the counter again (even
value); this would mark the page structure as valid
for the reader:
4. on begin read (shared lock), read (and store) the counter; if odd, a
writer is active and should return "busy" immediately (just like now).
5. read the page list in cache (I assume it is doing this right now)
6. read the counter again and compare with the initial value; if no change
is detected then page list is valid and it can read existing data. If change
is detected, it should signal "busy" just like 4.
7. when releasing the shared lock, the counter can be checked again (against
the value read on 4) and signal whether the data was changed or not. It is
up to the user to decide what to do with the "dirty" data.

For points 4, 6 it should either fail, or trying like now in a loop for a
certain period.
Even better, the change counter could (or actually "should") be per table.
So for writing there would only be trivial changes (simple counter
increment); for reading there would be some simple read/check of a value
before using the page list.

These relatively simple changes would open it for other applications; by
searching I saw that I'm not the only one who needs this kind of behavior.

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

[sqlite] non-blocking concurrent read questions

[Gabriel Corneanu]

Sure I could have some kind of intermediate storage, but that would mean
unnecessary data moving / copying.
I really hope that I'll find some time and try to study the source and
eventually implement my ideas (maybe others find it interesting and/or
useful too).

You said that only references are changed, right? That means, during appends
the page content is still valid even if B-trees structure is changed because
of references.

PS: what should I add to make the messages as reply (I'm using the web
interface for email)?

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

Re: [sqlite] non-blocking concurrent read questions

[Max Vlasov]

> Then I tried in a loop with 2 programs to write / read in parallel and it
> seems to work without problems. Can anyone advise if this has any chance to
> work (or say it would definitely NOT work)?
>
> As a short summary: would it be interesting for anyone to enable read-only
> open with a special pragma to allow reading without locking (that means,
> shared locks being a noop)?
>
>

Although speaking generally such method could be used in some situations, I
don't think it's good to allow to use it even with a "i know what I'm doing"
pragma. Any structured file (sqlite is an example) have internal
dependencies. One of the reasons to block is to write different parts of
structured data together without intervention from other parties in order to
keep the data integral. Imagine writing cache that kept changes for your
writer and finally it needed to flush the data and at the same time your
"anytime" reader started to perform some query in the middle of this
multiply pages writing process. I can't predict whether the reader will end
with some valid result or it will return with "database malformed" error.

Instead consider changing your own logic. You wrote "without* any
possibility to be blocked". I suppose you already have a perfect writer that
fits your needs, but if you post some info about the nature of your writer
and reader (records per second and something like this), it would help to be
more specific with answers.

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

[sqlite] Min() ; was: Re: if exist

[Tim Romano]

Wrapping a column in the min() function causes a query that returns no 
rows to return a  row?

select c from T where 1=2 // returns 0 rows
select min(c) from T where 1=2 // returns 1 row
select min(88,99) from T where 1=2  // returns 0 rows

Tim Romano

On 3/9/2010 4:15 AM, Martin.Engelschalk wrote:
> Hi,
>
> try this:
>
> select coalesce(min(length), 0) from t where id = ?
>
> Martin
>
> Andrea Galeazzi schrieb:
>
>> Hi All,
>> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
>> INT length.
>> I need a statement in order to yield 0 when the key doesn't exist. At
>> this moment the query is too simple:
>> SELECT length FROM T WHERE id = ?
>> Any idea about it?
>>  

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

Re: [sqlite] Min() ; was: Re: if exist

[Pavel Ivanov]

> Wrapping a column in the min() function causes a query that returns no
> rows to return a  row?

Yes, it's SQL standard for aggregate functions (min, max, avg and
count): without GROUP BY clause they always return one row.


Pavel

On Tue, Mar 9, 2010 at 7:18 AM, Tim Romano  wrote:
> Wrapping a column in the min() function causes a query that returns no
> rows to return a  row?
>
> select c from T where 1=2                 // returns 0 rows
> select min(c) from T where 1=2         // returns 1 row
> select min(88,99) from T where 1=2  // returns 0 rows
>
> Tim Romano
>
> On 3/9/2010 4:15 AM, Martin.Engelschalk wrote:
>> Hi,
>>
>> try this:
>>
>> select coalesce(min(length), 0) from t where id = ?
>>
>> Martin
>>
>> Andrea Galeazzi schrieb:
>>
>>> Hi All,
>>> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
>>> INT length.
>>> I need a statement in order to yield 0 when the key doesn't exist. At
>>> this moment the query is too simple:
>>> SELECT length FROM T WHERE id = ?
>>> Any idea about it?
>>>
>
> ___
> 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] Min() ; was: Re: if exist

[Tim Romano]

On 3/9/2010 8:04 AM, P Kishor wrote:
>
>> select min(88,99) from T where 1=2  // returns 0 rows
>>  
> The above is correct SQL and the answer is correct. Per the docs,
> "Note that min() is a simple function when it has 2 or more arguments
> but operates as an aggregate function if given only a single
> argument."
>
> Finally, note that when returning both aggregate and non-aggregate
> columns, you should use the GROUP BY clause. I believe that SQLite
> will return rows even without GROUP BY, but the answer may be
> undependable.
>

Of these three:

select c from T where 1=2 // returns 0 rows
select min(c) from T where 1=2 // returns 1 row
select min(88,99) from T where 1=2  // returns 0 rows


the only case that "threw" me is the second one, where a row is returned 
despite a WHERE condition that should yield an empty set (or so I thought).

Regarding your point about the GROUP BY clause -- I'm not sure what you mean by 
"non-aggregate columns".  Are you referring to a query where one wants to find 
the minimum value in a given column for the /entire/ table?

 select min(askingprice) from cars4sale
 group by rowid   //<= a group by is required here?


Regards
Tim Romano



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

Re: [sqlite] if exist

[Andrea Galeazzi]

It works fine!
Thanks!

Martin.Engelschalk ha scritto:
> Hi,
>
> try this:
>
> select coalesce(min(length), 0) from t where id = ?
>
> Martin
>
> Andrea Galeazzi schrieb:
>   
>> Hi All,
>> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and 
>> INT length.
>> I need a statement in order to yield 0 when the key doesn't exist. At 
>> this moment the query is too simple:
>> SELECT length FROM T WHERE id = ?
>> Any idea about it?
>> Cheers
>> ___
>> 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
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 4927 (20100309) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   

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

[sqlite] non-blocking concurrent read questions

[Gabriel Corneanu]

Hello everybody,

I have the following situation:
1. a writer needs to continuously append some data in 1 or 2 tables, *
without* any possibility to be blocked.
2. one (or eventually more) reader needs to read the data for analysis.

Pt 1 is very important; therefore I use a "PRAGMA locking_mode = EXCLUSIVE"
in the writer.
Then of course the reader can NOT read anything.

As I understood until now, there is no standard way to have real concurrent
read/write in sqlite.

I understand that in normal database operations a read uncommitted is not
possible with sqlite (I am talking about different processes).
In my situation I (try to) use sqlite as a data storage format. During
writing there are only appends, no inserts (in the middle) or updates.
(after the writing, standard database operations are applicable). The reader
needs to poll and try to read new data from the file.

I hope that using some usage restrictions, this might be possible. Therefore
I tried to enable reading without locking.
So found the "SQLITE_ENABLE_LOCKING_STYLE" define which might allow (value
2) exactly this behavior; unfortunately it seems to be used only for Unix
files.

Is there a reason not to enable this behavior via a special pragma (only
together with read-only)? I prefer to have a run-time switch, to be able to
use the same code everywhere.

Otherwise I modified winLock function with this code (the second condition
is mine):
..
if( pFile->locktype>=locktype ){
   return SQLITE_OK;
  }

// gc: always allow shared lock!
if(locktype==SHARED_LOCK){
  pFile->locktype = SHARED_LOCK;
  return SQLITE_OK;
}


I did not changed winUnlock, because it doesn't care if unlocking fails
(from shared lock). Of course this is not a full implementation, just a
quick change to test if it would work.

Then I tried in a loop with 2 programs to write / read in parallel and it
seems to work without problems. Can anyone advise if this has any chance to
work (or say it would definitely NOT work)?

As a short summary: would it be interesting for anyone to enable read-only
open with a special pragma to allow reading without locking (that means,
shared locks being a noop)?

PS. I also tried to use HDF5; it is faster, but I like the simplicity of
sqlite (and my data is not that complex to require hdf5).

Thanks for your attention,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] if exist

[Martin.Engelschalk]

Hi,

try this:

select coalesce(min(length), 0) from t where id = ?

Martin

Andrea Galeazzi schrieb:
> Hi All,
> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and 
> INT length.
> I need a statement in order to yield 0 when the key doesn't exist. At 
> this moment the query is too simple:
> SELECT length FROM T WHERE id = ?
> Any idea about it?
> Cheers
> ___
> 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] if exist

[Andrea Galeazzi]

Hi All,
I've got a table T made up of only two fields: INT id (PRIMARY KEY) and 
INT length.
I need a statement in order to yield 0 when the key doesn't exist. At 
this moment the query is too simple:
SELECT length FROM T WHERE id = ?
Any idea about it?
Cheers
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users