[sqlite] FaceBook reduced Messenger size partly through increased use of SQLite

[Shane Harrelson]

FaceBook significantly reduced the size of its Messenger app partly by
relying on SQLite to do more of the heavy lifting:

"Project LightSpeed: How Facebook shrunk Messenger down by 75%"
https://www.fastcompany.com/90470219/project-lightspeed-how-facebook-shrunk-messenger-down-by-75


"With LightSpeed, the company found that it could replace much of this
functionality with the widely used SQLite open-source database. 'We made a
bigger bet on it,' says Agsen. 'Things we would have done in memory, we put
in a database.'"
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on expressions

[Shane Harrelson]

Use a trigger to populate your index column.
http://www.sqlite.org/lang_createtrigger.html


On Wed, Sep 3, 2014 at 9:01 AM, Dominique Devienne 
wrote:

> On Wed, Sep 3, 2014 at 2:52 PM, Richard Hipp  wrote:
>
> > SQLite does not (yet) support indexes on expressions.
>
>
> This begs the question: Are there plans, possibly ongoing, to add this
> support? Any timeframe?
>
> Asked differently, if adding this support, could this be done by adding
> virtual / computed columns to tables, and indexing those columns?
>
> You can emulate virtual columns with views of course, but then you have a
> table and view, necessarily named differently, and of course you cannot
> index views, while some DBMS allow indexing virtual  / computed columns.
> --DD
>
> Fantasy SQL:
> create table t (id number primary key, c1, c2, ... );
> alter table t add column c99 as (c1 + c2);
> create index idx_t_c99 on t.c99;
> ___
> 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] Improving Bulk Insert Speed (C/C++)

[Shane Harrelson]

Neither of these are your problem, but I noticed the following in your
posted code:

PRAGMA auto_vacuum=NONE;  has no affect after your tables are created.
You should move this setting earlier in your code.
http://www.sqlite.org/pragma.html#pragma_auto_vacuum

PRAGMA count_changes=OFF is deprecated and shouldn't be used.
http://www.sqlite.org/pragma.html#pragma_count_changes


On Thu, Apr 3, 2014 at 5:41 AM, Simon Slavin  wrote:

>
> On 3 Apr 2014, at 3:29am, Kevin Xu  wrote:
>
> > I have not discovered how to find internal memory throughput usage in
> OSX, and I agree that something is not allowing the system from maxing out
> the CPU or I/O.
>
> A single application can max a single core of a single CPU if it tries
> hard.  So if your bottleneck is CPU then Activity Monitor will show a
> section running at 98% or above.  If that's not happening, then something
> else is your bottleneck -- probably either main memory or main storage.
>  'nice' will probably not help unless you are knowingly running other
> applications which hog CPU time.  On modern Macs the OS tends to use one
> core and leave the other cores free for apps.
>
> If you can't find memory throughput out using the OS X 10.9 version of
> Activity Monitor, it's probably only possible to find it out using
> profiling tools and a debugger.  I don't see a way to find out memory
> throughput using Activity Monitor.
>
> Simon.
> ___
> 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] Improving Bulk Insert Speed (C/C++)

[Shane Harrelson]

I had a few suggestions and questions earlier in this thread that I don't
think have been responded to.

And yes, without seeing the source it will be difficult to make more
suggestions.
On Apr 2, 2014 4:57 PM, "Kevin Xu"  wrote:

> The app seems to use between 60-80% CPU while it is running (from Activity
> Monitor) while disk use (using sudo iotop -P on OSX) seem to suggest about
> 20%-30% I/O use. (spikes to over 30million% every 10s or so, might be when
> sqlite decides to page out)
>
> Clearly my computations on the data are not complex enough to max out the
> processor (the bulk inserts, at least, is single threaded) while iotop
> results suggests I/O isn't maxed out either, which is why I suspect
> performance improvements are still possible.
>
> On the other hand, it is difficult for anyone to suggest more ideas
> without seeing the actual source code (professor wants it private for now),
> so I will try coding up the virtual table, and pure binary file dump (as a
> baseline) to see if I discover any more issues.
>
> Kevin Xu
>
>
> On Apr 2, 2014, at 1:28 PM, Keith Medcalf  wrote:
>
> > On Tue, 1 Apr 2014 20:58:14 -0700
> > Kevin Xu  wrote:
> >
> >> I am trying to see if it is possible to achieve even higher
> >> throughput - my professor was adamant that he managed
> >> to insert 3 million rows in 7 seconds (which works out to
> >> over 420K inserts per second) though he could not find
> >> his code that did it or records of his insertions.
> >
> >> When I profiled the application (using Instruments), and after
> >> inverting the call tree, the time spent within the program is
> >> broken down as follows:
> >
> >> 2170ms 15.1% - sqlite3VdbeExec -> sqlite3_step -> insert function
> >> 2142ms 14.9% - pwrite -> unixWrite -> pager_write/pager_write_pagelist
> >> 1925ms 14.9% - std::string::insert -> boost::spirit
> >> 539ms3.7% - pack (my compression function)
> >
> > A question that nobody seems to have bothered to ask, and that will
> > entirely direct your solution search:
> >
> > Is the limit I/O or CPU?
> >
> > That is, which has hit 100% usage, CPU or I/O?  If is is I/O then you
> > might want to look for consumers of I/O.  The best way to make I/O go
> > faster is not to do it.
> >
> > If the limit is CPU, then you need to devise a way to consume less CPU
> > (or get a faster CPU) or to get more processors and parallelize your
> > processing.
> >
> > Secondly, what is the headroom you have available?  For example if you
> > are using 100% CPU and 97% I/O, then you are damn close to balanced and
> > the additional couple of K per second you can gain in I/O is unlikely
> > to make any significant difference, and will likely end up being the
> > bottleneck even if you spend a couple of million dollars on CPU and
> > MEMORY (to drive CPU usage down to 0.0001% but peg I/O to 100%).
> >
> > If the problem is CPU, then the first place to attack is boost:spirit
> > (or use multiprocessing) -- after checking to ensure that you I/O
> > system is properly offloaded from the main CPU and not consuming spin
> > cycles while doing I/O, of course.
> >
> > If neither I/O nor CPU is pegged at 100% then you have a crappy
> > scheduler or simply insufficient overlap between I/O and compute, and
> > you need to fix this first.
> > ___
> > 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] Improving Bulk Insert Speed (C/C++)

[Shane Harrelson]

What is your page size?   Make sure it matches the sector size of your
device.   SSDs can be picky about write sizes.  As an experiment, you might
try using larger page sizes that are multiples of the sector size.

Try to reduce the size of the records you are writing.  Ie. can you map any
string data to an integer enumeration?Perhaps you can "normalize" some
of the columns to de-duplicate the amount of data stored.   Can you
compress your blob data?  In general, the less bytes you have to write the
better.

If possible, make sure your records fit "nicely" into your page size as
this should make writes more efficient.  The sqlite-analyzer might give you
some clues about the "shape" of your database.
http://www.sqlite.org/download.html

Do you have a non-integer column defined as a "PRIMARY KEY"?  Perhaps the
"WITHOUT ROWID" optimization will help.
http://www.sqlite.org/withoutrowid.html

You mentioned that this work might get moved to a "computing cluster".  As
a caution, your tuning for your mac laptop/ssd combination might make
little difference (or even make it worse) on a machine with different
cpu/memory/io characteristics.  Ie. you might be severely CPU bound there.

Are you really going to be doing bulk loads like this all the time?
Usually data is loaded once, then read many times.  If not, perhaps you
should be focused on improving your query performance.

HTH.
-Shane





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

[sqlite] sqlite3_close() documentation inconsistency

[Shane Harrelson]

Documentation for sqlite3_close() on
http://www.sqlite.org/c3ref/close.html has two conflicting statements:

"If the database connection is associated with unfinalized prepared
statements or unfinished sqlite3_backup objects then sqlite3_close()
will leave the database connection open and return SQLITE_BUSY."

and

"If sqlite3_close() is called on a database connection that still has
outstanding prepared statements, BLOB handles, and/or sqlite3_backup
objects then it returns SQLITE_OK but..."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.dll for Win 64

[Shane Harrelson]

The makefile builds lemon.exe from lemon.c as part of the build process, so
make sure it was generated correctly as well.

"lemon -x" should print a version number.
"lemon -?" should print an error and help message.

Again, HTH.
-Shane


On Mon, Jul 25, 2011 at 5:51 PM, Shane Harrelson
<shane.harrel...@gmail.com>wrote:

>
> The TK_* identifiers are all defined in parse.h which is generated by
> lemon.exe from parse.y.
> parse.h is "included" in the amalgamation file, sqlite3.c.
> Try deleting your parse.c and parse.h and sqlite3.c and re-running your
> make.
> Check that parse.h was generated correctly and subsequently concatenated
> into sqlite3.c correctly.
>
> HTH.
> -Shane
>
>
>
> On Mon, Jul 25, 2011 at 3:49 PM, Everton Vieira <tonvie...@gmail.com>wrote:
>
>> So, now i'm stock on this errors:
>>
>> sqlite3.c
>> sqlite3.c(40972) : warning C4244: '=' : conversion from '__int64' to
>> 'int',
>> poss
>> ible loss of data
>> sqlite3.c(57259) : error C2065: 'TK_REGISTER' : undeclared identifier
>> sqlite3.c(57266) : error C2065: 'TK_UMINUS' : undeclared identifier
>> sqlite3.c(57266) : error C2065: 'TK_INTEGER' : undeclared identifier
>> sqlite3.c(57266) : error C2065: 'TK_FLOAT' : undeclared identifier
>> sqlite3.c(57273) : error C2065: 'TK_STRING' : undeclared identifier
>> sqlite3.c(57273) : error C2065: 'TK_FLOAT' : undeclared identifier
>> sqlite3.c(57273) : error C2065: 'TK_INTEGER' : undeclared identifier
>> sqlite3.c(57282) : error C2065: 'TK_FLOAT' : undeclared identifier
>> sqlite3.c(57284) : error C2065: 'TK_INTEGER' : undeclared identifier
>> sqlite3.c(57284) : error C2065: 'TK_FLOAT' : undeclared identifier
>> sqlite3.c(57293) : error C2065: 'TK_UMINUS' : undeclared identifier
>> sqlite3.c(57307) : error C2065: 'TK_NULL' : undeclared identifier
>> sqlite3.c(57312) : error C2065: 'TK_BLOB' : undeclared identifier
>> sqlite3.c(61966) : error C2065: 'TK_VARIABLE' : undeclared identifier
>> sqlite3.c(63498) : error C2196: case value '0' already used
>> sqlite3.c(63752) : error C2196: case value '0' already used
>> sqlite3.c(63822) : error C2196: case value '0' already used
>> sqlite3.c(63823) : error C2196: case value '0' already used
>> sqlite3.c(63824) : error C2196: case value '0' already used
>> sqlite3.c(63825) : error C2196: case value '0' already used
>> sqlite3.c(63826) : error C2196: case value '0' already used
>> sqlite3.c(63847) : error C2196: case value '0' already used
>> sqlite3.c(63848) : error C2196: case value '0' already used
>> sqlite3.c(63849) : error C2196: case value '0' already used
>> sqlite3.c(63870) : error C2196: case value '0' already used
>> sqlite3.c(63871) : error C2196: case value '0' already used
>> sqlite3.c(63872) : error C2196: case value '0' already used
>> sqlite3.c(64070) : error C2196: case value '0' already used
>> sqlite3.c(64071) : error C2196: case value '0' already used
>> sqlite3.c(64072) : error C2196: case value '0' already used
>> sqlite3.c(64073) : error C2196: case value '0' already used
>> sqlite3.c(64191) : error C2196: case value '0' already used
>> sqlite3.c(64214) : error C2196: case value '0' already used
>> sqlite3.c(64238) : error C2196: case value '0' already used
>> sqlite3.c(64254) : error C2196: case value '0' already used
>> sqlite3.c(64272) : error C2196: case value '0' already used
>> sqlite3.c(64354) : error C2196: case value '0' already used
>> sqlite3.c(64355) : error C2196: case value '0' already used
>> sqlite3.c(64356) : error C2196: case value '0' already used
>> sqlite3.c(64357) : error C2196: case value '0' already used
>> sqlite3.c(64358) : error C2196: case value '0' already used
>> sqlite3.c(64359) : error C2196: case value '0' already used
>> sqlite3.c(64410) : error C2196: case value '0' already used
>> sqlite3.c(64411) : error C2196: case value '0' already used
>> sqlite3.c(64412) : error C2196: case value '0' already used
>> sqlite3.c(64413) : error C2196: case value '0' already used
>> sqlite3.c(64546) : error C2196: case value '0' already used
>> sqlite3.c(64547) : error C2196: case value '0' already used
>> sqlite3.c(64588) : error C2196: case value '0' already used
>> sqlite3.c(64605) : error C2196: case value '0' already used
>> sqlite3.c(64654) : error C2196: case value '0' already used
>> sqlite3.c(64666) : error C2196: case value '0' already used
>> sqlite3.c(69909) : error C2065: 'TK_COLUMN' : undeclared identifier
>> sqlite3.c(69911) : error C2065: 'TK_AS' : undeclared identifier
>> sqlite3.c(70079) : error C2065: 'TK_DELETE' : undeclared identifier

Re: [sqlite] SQLite3.dll for Win 64

[Shane Harrelson]

The TK_* identifiers are all defined in parse.h which is generated by
lemon.exe from parse.y.
parse.h is "included" in the amalgamation file, sqlite3.c.
Try deleting your parse.c and parse.h and sqlite3.c and re-running your
make.
Check that parse.h was generated correctly and subsequently concatenated
into sqlite3.c correctly.

HTH.
-Shane



On Mon, Jul 25, 2011 at 3:49 PM, Everton Vieira  wrote:

> So, now i'm stock on this errors:
>
> sqlite3.c
> sqlite3.c(40972) : warning C4244: '=' : conversion from '__int64' to 'int',
> poss
> ible loss of data
> sqlite3.c(57259) : error C2065: 'TK_REGISTER' : undeclared identifier
> sqlite3.c(57266) : error C2065: 'TK_UMINUS' : undeclared identifier
> sqlite3.c(57266) : error C2065: 'TK_INTEGER' : undeclared identifier
> sqlite3.c(57266) : error C2065: 'TK_FLOAT' : undeclared identifier
> sqlite3.c(57273) : error C2065: 'TK_STRING' : undeclared identifier
> sqlite3.c(57273) : error C2065: 'TK_FLOAT' : undeclared identifier
> sqlite3.c(57273) : error C2065: 'TK_INTEGER' : undeclared identifier
> sqlite3.c(57282) : error C2065: 'TK_FLOAT' : undeclared identifier
> sqlite3.c(57284) : error C2065: 'TK_INTEGER' : undeclared identifier
> sqlite3.c(57284) : error C2065: 'TK_FLOAT' : undeclared identifier
> sqlite3.c(57293) : error C2065: 'TK_UMINUS' : undeclared identifier
> sqlite3.c(57307) : error C2065: 'TK_NULL' : undeclared identifier
> sqlite3.c(57312) : error C2065: 'TK_BLOB' : undeclared identifier
> sqlite3.c(61966) : error C2065: 'TK_VARIABLE' : undeclared identifier
> sqlite3.c(63498) : error C2196: case value '0' already used
> sqlite3.c(63752) : error C2196: case value '0' already used
> sqlite3.c(63822) : error C2196: case value '0' already used
> sqlite3.c(63823) : error C2196: case value '0' already used
> sqlite3.c(63824) : error C2196: case value '0' already used
> sqlite3.c(63825) : error C2196: case value '0' already used
> sqlite3.c(63826) : error C2196: case value '0' already used
> sqlite3.c(63847) : error C2196: case value '0' already used
> sqlite3.c(63848) : error C2196: case value '0' already used
> sqlite3.c(63849) : error C2196: case value '0' already used
> sqlite3.c(63870) : error C2196: case value '0' already used
> sqlite3.c(63871) : error C2196: case value '0' already used
> sqlite3.c(63872) : error C2196: case value '0' already used
> sqlite3.c(64070) : error C2196: case value '0' already used
> sqlite3.c(64071) : error C2196: case value '0' already used
> sqlite3.c(64072) : error C2196: case value '0' already used
> sqlite3.c(64073) : error C2196: case value '0' already used
> sqlite3.c(64191) : error C2196: case value '0' already used
> sqlite3.c(64214) : error C2196: case value '0' already used
> sqlite3.c(64238) : error C2196: case value '0' already used
> sqlite3.c(64254) : error C2196: case value '0' already used
> sqlite3.c(64272) : error C2196: case value '0' already used
> sqlite3.c(64354) : error C2196: case value '0' already used
> sqlite3.c(64355) : error C2196: case value '0' already used
> sqlite3.c(64356) : error C2196: case value '0' already used
> sqlite3.c(64357) : error C2196: case value '0' already used
> sqlite3.c(64358) : error C2196: case value '0' already used
> sqlite3.c(64359) : error C2196: case value '0' already used
> sqlite3.c(64410) : error C2196: case value '0' already used
> sqlite3.c(64411) : error C2196: case value '0' already used
> sqlite3.c(64412) : error C2196: case value '0' already used
> sqlite3.c(64413) : error C2196: case value '0' already used
> sqlite3.c(64546) : error C2196: case value '0' already used
> sqlite3.c(64547) : error C2196: case value '0' already used
> sqlite3.c(64588) : error C2196: case value '0' already used
> sqlite3.c(64605) : error C2196: case value '0' already used
> sqlite3.c(64654) : error C2196: case value '0' already used
> sqlite3.c(64666) : error C2196: case value '0' already used
> sqlite3.c(69909) : error C2065: 'TK_COLUMN' : undeclared identifier
> sqlite3.c(69911) : error C2065: 'TK_AS' : undeclared identifier
> sqlite3.c(70079) : error C2065: 'TK_DELETE' : undeclared identifier
> sqlite3.c(70082) : error C2065: 'TK_INSERT' : undeclared identifier
> sqlite3.c(70186) : error C2065: 'TK_STRING' : undeclared identifier
> sqlite3.c(70231) : error C2065: 'TK_TRIGGER' : undeclared identifier
> sqlite3.c(70231) : error C2065: 'TK_COLUMN' : undeclared identifier
> sqlite3.c(70255) : error C2065: 'TK_COLUMN' : undeclared identifier
> sqlite3.c(70327) : error C2065: 'TK_ID' : undeclared identifier
> sqlite3.c(70327) : error C2051: case expression not constant
> sqlite3.c(70334) : error C2065: 'TK_DOT' : undeclared identifier
> sqlite3.c(70334) : error C2051: case expression not constant
> sqlite3.c(70342) : error C2065: 'TK_ID' : undeclared identifier
> sqlite3.c(70357) : error C2065: 'TK_CONST_FUNC' : undeclared identifier
> sqlite3.c(70357) : error C2051: case expression not constant
> sqlite3.c(70358) : error C2065: 'TK_FUNCTION' : undeclared 

Re: [sqlite] Support for .Net CE 3.5

[Shane Harrelson]

You'll have to go back to one of the "legacy versions" from
http://sqlite.phxsoftware.com .
Compact framework support was one of the features that had to be temporarily
dropped when maintenance was moved to http://www.sqlite.org .
Restoring compact framework is currently one of our top priorities.



On Wed, Jun 1, 2011 at 1:08 PM, Jeff Hoffman wrote:

> I am programming for Windows Mobile 6.5.3 and would like to use SQLite.
>
> I see this message on the "Features" page:
>
> Supports the Full and Compact .NET Framework, and native C/C++ development.
> 100% binary compatible with the original sqlite3.dll.
> Compact framework not currently not included. We hope to have this feature
> included again soon.
>
>
>
> Can anyone tell me the latest version of System.Data.SQLite that supports
> .Net CE 3.5?   Any other advice would be welcome too!
>
> Thanks,
>
> Jeff
> ___
> 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] Discussing and contributing to new System.Data.SQLite project

[Shane Harrelson]

Hi-

The SDS project is currently being maintained by the same people who
maintain SQLite.
We're currently also using the same mailing list (this one, sqlite-users)
for discussions of SDS.

Contributions are always welcome, but please note we will need a
"contributor agreement" on file:
http://system.data.sqlite.org/index.html/doc/trunk/www/contribute.wiki

Current priorities going forward include restoring compact framework and
design time support.

Thanks for your interest in helping!
-Shane


On Fri, May 27, 2011 at 9:50 AM, Samuel Neff  wrote:

> Who is maintaining the new System.Data.SQLite project at
> http://system.data.sqlite.org?  Is there a separate mailing list for that
> project or should questions on that project be sent to the sqlite-users
> this
> mailing list?
>
> My company is interested in knowing what the plans are going forward and
> most specifically how can we contribute.  We have a patch that exposes the
> native backup API via the .NET wrapper which we'd like to contribute.  We
> also are planning on writing a new connection pool implementation that is
> not dependent on the garbage collector and is compatible with WAL mode.
>
> Thanks,
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .NET4 is available?

[Shane Harrelson]

The latest versions are available at http://system.data.sqlite.org


On Thu, May 26, 2011 at 11:54 AM, Massimo Savazzi  wrote:

> I'm using the "old":  1.0.66.1 .NET4
>
> http://sqlite.phxsoftware.com/tags/.NET+4/default.aspx
>
>
>
> Do we have an updated version?
>
>
>
>
>
> =
>
> STRICTLY PERSONAL AND CONFIDENTIAL
>
>
>
> This message may contain confidential and proprietary material for the sole
> use of the intended recipient.
>
> Any review or distribution by others is strictly prohibited.
>
> If you are not the intended recipient please contact the sender and delete
> all copies.
>
>
>
> Le informazioni contenute in questa comunicazione sono riservate e
> destinate
> esclusivamente alla/e persona/e o all'ente/i sopra indicati. E' vietato ai
> soggetti diversi dai destinatari qualsiasi uso, copia, diffusione di quanto
> in essa contenutaai sensi della Legge 196/03. Se questa comunicazione Vi e'
> pervenuta per errore, Vi preghiamo di rispondere a questa mail per
> segnalare
> l'errore e successivamente di cancellarla dal Vostro sistema
>
>
>
> =
>
>
>
> ___
> 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] Interop DLL question

[Shane Harrelson]

Switching to a "single format" greatly simplified things from an
"administrative" point of view - giving us a consistent single "packaging"
format across all platforms (Windows, Mono, Compact, ARM, etc.)  - a support
customer even specifically pointed out this made things much easier for them
as well.

However, cases like yours, and cases where inexperienced users didn't catch
some of the subtleties of the packaging change, are causing us to rethink
this.
We're currently discussing whether to revert back to offering a "mixed-mode"
DLL in addition to the current packages.

Thanks for you input.
-Shane



On Fri, May 20, 2011 at 12:10 AM, Michael Yeaney
wrote:

> For the 1.0.72.0 (3.7.6+) package (x64), is there a stand-alone (mixed
> mode) DLL offered anymore that does not require the interop library
> (much like the 1.0.66.0 version?  I'm using SQLite successfully in a
> few Microsoft Azure deployments, but with the new version, the servers
> are not able to locate the "SQLite.Interop.dll" library, as it is not
> in any search paths on the cloud machines. The goal here is simple
> xcopy deployment (in other words _not_ requiring GAC registration).
>
> Ideas? I'm assuming I'm missing something...but I just can't find it.
>
> Many thanks!
> ___
> 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] Unable to load dll "SQLite.Interop.dll"

[Shane Harrelson]

The setup packages available from here:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
are suppose to check if the MSVC runtimes are installed or not and install
them.  It's the main reason the packages are as large as they are.
How did you install the SDS DLLs?

Thanks.
-Shane


On Thu, May 19, 2011 at 11:24 PM, Anderson Laécio G. Trindade <
laec...@yahoo.com.br> wrote:

> Ops...
> I was dealing with [Unable to load dll "SQLite.Interop.dll"] error when I
> was running my software on other computers. Finally I figured out that the
> dependency msvcr100.dll was missing on those computers. This can be helpful
> for other users.
>
>
> Regards,
>
> Anderson Laécio Galindo Trindade
>
>
> 
> ___
> 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] System.Data.SQLite Status

[Shane Harrelson]

On Fri, May 13, 2011 at 3:24 AM, Clay Fowler wrote:

> The downloads at
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wikiappear
> broken in various ways:
>
> The precompiled binaries fail to work on desktop from Mono on OS X or on
> Microsoft's CLR from Windows (even with the included test.exe) because they
>

I've tested the setup on multiple versions of Windows.  If you're having
problems
please try this:

Run the appropriate "setup" for your Version of Windows from the downloads
page
and install to a temp directory like "C:\temp\SDS" - this is so it is
writable
without having to have administration privileges.
Choose all the default options for installation.

Run the test utility - "C:\temp\SDS\bin\test.exe".

Does it work? What errors are reported?



> attempt to load SQLite.Interop.dll. According to the bundled readme.html,
> this should not happen (it says SQLite.Interop.dll is only needed when
>
deploying on the compact framework and that normally only
> System.Data.SQLite.dll itself should be deployed with desktop apps). But
> even with the SQLite.Interop.dll present, it still fails to load.
>
>
The bundled readme, as well as the features page, have an update in red,
describing
the decision we made to ship the SDS DLL and the Interop DLL separately for
all platforms:

"Currently all versions provided as System.Data.SQLite.dll and
SQLite.Interop.dll.
This provides consistency across all packages, including Compact and Mono."

This was a very difficult decision to make, but from a support perspective,
what we
felt was the best choice.


Meanwhile, the source download is broken because core projects within the
> solution are completely empty (no source files), such as the main
> System.Data.SQLite assembly. This is true for both the 2008 and 2010
> solutions in the .zip.
>
>
The solution files "appear" empty because they both (2008 and 2010)
reference
common target files that specify shared build information.   See
System.Data.SQLite.Files.targets for example. You should have been able
to open the SQLite.NET.2010.sln solution file and hit rebuild solution.
(Depending on your VS setup, you may need update the references for the
designer to build, but it's not needed to build and use the interop or SDS
DLLs
or the test executable.)



> So it doesn't appear possible to get anything that works either from binary
> or source at the moment, or I am overlooking something.
> ___
> 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] Building managed only System.Data.SQLite

[Shane Harrelson]

Remember that System.Data.SQLite.dll needs to be able to find the
SQLite.Interop.dll.
There's a post-build step that attempts to copy the Interop DLL to the
appropriate debug directory.
You may still need to copy into your debug directory for your test purposes.



On Wed, Apr 20, 2011 at 3:04 PM, Rich Rattanni <ratta...@gmail.com> wrote:

> Shane:
>
> I downloaded http://system.data.sqlite.org/sqlite-dotnetsrc-1006900.zip
> and changed SQLite.NET.Settings.targets as per your recommendation.
> It did not appear to solve my problem since my application threw an
> exception stating that Sqlite.Interop was missing.  To be complete I
> tried 3 permutations of the two options UseInteropDll and
> UseSqliteStandard ( I did not try false, false).
>
> All resulting System.Data.SQLite.DLLs threw exceptions due to a
> missing InterOp DLL.  I pulled down a copy of System.Data.SQLite
> 1.0.66.0 and built the Managed Only version, and it appeared to work.
>
> I will mess around some more with the project to see if I can build
> the managed only version.  I was hoping to get my hands on the latest
> so I was not fighting issues that may have been fixed by a release.
> Thank you again for trying to help, rest assured it was appreciated.
>
> --
> Rich
>
> On Tue, Apr 19, 2011 at 5:36 PM, Rich Rattanni <ratta...@gmail.com> wrote:
> > Daniel:
> >
> > I have not tried Csharp-sqlite, it looks interesting but I do not know
> > if that is right for me at this moment.
> >
> > Shane:
> >
> > I will try what you recommend tomorrow, thank you.
> >
> > --
> > Rich
> >
> > On Tue, Apr 19, 2011 at 4:47 PM, Shane Harrelson
> > <shane.harrel...@gmail.com> wrote:
> >> The target build settings can be controlled from
> SQLite.NET.Settings.targets
> >> - in particular, you should probably look at UseInteropDll and
> >> UseSqliteStandard.
> >> To override the USE_INTEROP_DLL setting, try copying
> >> SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make
> the
> >> settings changes there.
> >> This should work with VS2008 and VS2010.
> >>
> >> HTH.
> >> -Shane
> >>
> >>
> >> On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni <ratta...@gmail.com>
> wrote:
> >>
> >>> I was wondering if anyone has had any luck building the Managed-Only
> >>> System.Data.SQLite .NET adapter for SQLite from the source provided at
> >>> system.data.sqlite.org?  I downloaded the pre-built binaries but they
> >>> appear to rely on the InterOp assembly.  My current project is running
> >>> under Linux 2.6 on an ARM processor, and uses managed-only copy of the
> >>> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
> >>> I am looking to do some bug tracing / upgrading so I would like to
> >>> build my own copy from source.
> >>>
> >>> --
> >>> Rich
> >>> ___
> >>> 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] Building managed only System.Data.SQLite

[Shane Harrelson]

The target build settings can be controlled from SQLite.NET.Settings.targets
- in particular, you should probably look at UseInteropDll and
UseSqliteStandard.
To override the USE_INTEROP_DLL setting, try copying
SQLite.NET.Settings.targets to SQLite.NET.Settings.targets.user and make the
settings changes there.
This should work with VS2008 and VS2010.

HTH.
-Shane


On Tue, Apr 19, 2011 at 3:09 PM, Rich Rattanni  wrote:

> I was wondering if anyone has had any luck building the Managed-Only
> System.Data.SQLite .NET adapter for SQLite from the source provided at
> system.data.sqlite.org?  I downloaded the pre-built binaries but they
> appear to rely on the InterOp assembly.  My current project is running
> under Linux 2.6 on an ARM processor, and uses managed-only copy of the
> System.Data.SQLite adapter + SQLite 3.5.0 + mono to run my .NET app.
> I am looking to do some bug tracing / upgrading so I would like to
> build my own copy from source.
>
> --
> Rich
> ___
> 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] BUG: mutex_w32.c; diagnostic message needs to be clearer

[Shane Harrelson]

I updated the debug statement here:

http://www.sqlite.org/src/info/def98fd23e

Thanks for the report.
-Shane

On Mon, Mar 14, 2011 at 7:54 PM, Noah Hart  wrote:
>
> In the routine winMutexTry at line 284
>
> -    printf("enter mutex %p (%d) with nRef=%d\n", p, p->trace, p->nRef);
> +    printf("try mutex %p (%d) with nRef=%d\n", p, p->trace, p->nRef);
>
> --
> View this message in context: 
> http://old.nabble.com/BUG%3A-mutex_w32.c--diagnostic-message-needs-to-be-clearer-tp31149931p31149931.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Source code for system.data.sqlite

[Shane Harrelson]

Thanks for the feedback.

We're currently trying to work out a Contributor License Agreement for
System.Data.SQLite - probably something along the lines of
http://www.fossil-scm.org/fossil/doc/trunk/www/copyright-release.html
used for Fossil.

You need to login to the Fossil site, even if only as "Anonymous", to
be able to add a ticket.

-Shane


On Tue, Mar 1, 2011 at 8:00 PM, Paul Shaffer  wrote:
> Since sqlite has taken on system.data.sqlite for .net you will be seeing
> issues on this until a forum is set up for it.
>
> Please try to make sure the entire source tips are available for download.
> The last time I checked 1.0.68 (or ?) did not compile due to missing linq
> related files. Hopefully there would be some way to contribute source
> changes to the project in future, there is no way now.
>
> There is also no way to add on to issues in the "tracker", and no way to
> distinguish contributors to issues.
>
> I can't get into the dev forum or I would post this there.
>
> ___
> 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] Problem with VACUUM feature

[Shane Harrelson]

Hi-

On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
temporary files automatically deleted after they are closed.  WINCE
doesn't support this flag, so you will see special logic in os_win.c,
wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
files.  You mentioned in an earlier post that you had ported to your
platform based on this code.   Could you check that your ported code
includes this logic?

-Shane

On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy  wrote:
> Hi,
>
> I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
> it is slightly related to it but the temporary files are created while
> running VACUUM command.
> ---
> Ticket 2829:
>
> This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
>
>   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
> -                    | SQLITE_OPEN_SUBJOURNAL) ){
> +                    | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){
>
> --
>
> The temp files were created in the below call sequence:
>
> -
>  62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
>  61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
>  60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
>  59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
>  58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
>  57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
>  56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
>  55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
>  54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
>  53 sqlite3Step() sqlite3.c:59380 0x3af87b34
>  52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
>  51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
> --
>
>
>
> Basically there 2 problems associated when i run VACUUM command.
> Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
> which are not deleted when main DB is closed.
> Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
> closing the main DB connection, the size of the main DB file MyDb.db does
> not change where as one of the temp file(etilqs_*) will actually contain the
> reduced size of the same data as of main DB file.
>
> I am not sure if this is the expected behaviour or there is some bug in the
> flow.
>
> Please let me know if there is a solution to resolve this issue.
>
> Thanks,
> Sudha
>
> On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin  wrote:
>
>>
>> On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
>>
>> > *Actual output: MyDb.db remains size 23KB(size not changes from original)
>> > and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as
>> > MyDb.db but the size is reduced to 13KB*
>>
>> Your problem is probably related to
>>
>> http://www.sqlite.org/cvstrac/tktview?tn=2829
>>
>> .  It's quite legitimate for your symptoms to occur while the database
>> handle is still open but you should not be seeing those files after you have
>> closed the connection to the database.  Either you are not closing the
>> database connection properly, or some part of the API you're using is not
>> closing the database connection properly.
>>
>> I'm not familiar with how this problem manifests because I don't use
>> Windows, so I'll leave it up to an expert to tell you if it needs fixing
>> somehow.
>>
>> Simon.
>> ___
>> 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] UPDATE/INSERTing 1-2k rows slower than expected

[Shane Harrelson]

On Mon, Feb 14, 2011 at 6:04 PM, Simon Slavin  wrote:
>
> If the data is backed up when SQLite has the files closed, things are fine.  
> The really hard part of this comes when you're working with systems that must 
> be live at all times.  Which is why you'll never see a bank use SQLite to 
> maintain its live transaction system.  The big DBMSs have systems inside 
> themselves that allow the backing-up of an active ever-changing database.
>
> Simon.

SQLite is used as the DB engine on some major 24/7 data stores (I
can't be more specific due to NDAs).   You can get an idea of some of
the high demand areas SQLite is used in by reviewing the consortium
membership list (http://www.sqlite.org) and well-known users list
(http://www.sqlite.org/famous.html), though this should not be seen as
an endorsement by any of the listed companies.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: test script exclusive2.test needs do_not_use_codec

[Shane Harrelson]

Thanks.   I think I got all the changes.

On Mon, Feb 14, 2011 at 7:47 PM, Noah Hart  wrote:
>
> exclusive2.test reads directly from the database using binary read
>
> It needs the following changes:
>
> -source $testdir/tester.tcl
> +source $testdir/tester.tcl
> +
> +# Do not use a codec for tests in this file, as the database file is
> +# manipulated directly using tcl scripts (using binary read)
> +#
> +do_not_use_codec
>
>
> ~Noah Hart
>
> --
> View this message in context: 
> http://old.nabble.com/BUG%3A-test-script-exclusive2.test-needs-do_not_use_codec-tp30927245p30927245.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: capi3e.test when compiled with SQLITE_OMIT_UTF16

[Shane Harrelson]

Thanks.Hopefully corrected here:  http://www.sqlite.org/src/info/b04304b967

-Shane


On Fri, Feb 11, 2011 at 4:59 PM, Noah Hart  wrote:
>
> capi3e.test needs
>      ifcapable utf16 logic before capi3e-2.1.$i
> to properly pass tests when compiled with SQLITE_OMIT_UTF16
>
> ~ Noah Hart
>
>
> --
> View this message in context: 
> http://old.nabble.com/BUG%3A-capi3e.test-when-compiled-with-SQLITE_OMIT_UTF16-tp30905474p30905474.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Outer query returning results not found in subquery

[Shane Harrelson]

On Thu, Feb 10, 2011 at 2:29 PM, Igor Tandetnik  wrote:
> On 2/10/2011 2:17 PM, Dan Kubb wrote:
>> Database setup:
>>
>>      CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER 
>> NOT NULL);
>>
>>      INSERT INTO "test" ("letter", "number") VALUES('b', 1);
>>      INSERT INTO "test" ("letter", "number") VALUES('a', 2);
>>      INSERT INTO "test" ("letter", "number") VALUES('c', 2);
>>
>> Initial query:
>>
>>      SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 
>> 1;
>>
>> This returns "a|2", the second row from the results as you would
>> expect given that we're sorting on the letter then the number.
>> However, here's what I did not expect:
>>
>> Initial query as a subquery:
>>
>>      SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" 
>> ORDER BY "letter", "number" LIMIT 1) AS "test";
>>
>> This returns "1"
>
> Yes, looks like a bug. If you drop DISTINCT, it returns a single row
> with the value 2.
>
> My guess is, DISTINCT is internally implemented as ORDER BY, and that
> overrules ORDER BY found in the subquery.
> --
> Igor Tandetnik
>


Thanks for the report and recreate.   A ticket was opened here:

http://www.sqlite.org/src/tktview/752e1646fcc7b649184e49783577a7feb5f7fc9c

I was able to recreate the problem.   Additionally, if query
optimizations are disabled, then the correct result is returned, so
this is most likely and issue with the query flattener.

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

Re: [sqlite] Windows performance problems associated with malloc()

[Shane Harrelson]

I believe the Windows default is to use the LFH on Vista and newer
versions of Windows.
The suggestion by Marcus Grimm to use  _set_sbh_threshold() to enable use
of the SBH (small block heap) may help under some usage scenarios on
those platforms.

-Shane


On Fri, Dec 17, 2010 at 6:29 PM, Doug  wrote:
> I wonder if HeapSetInformation (which can enable a low-fragmentation heap)
> would be helpful too.  You can set it on the process
> and the CRT heaps.  Note that it's not available in Win2K and earlier.
>
> Doug
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm
> Sent: Friday, December 17, 2010 9:21 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Windows performance problems associated with malloc()
>
>> An SQLite user has brought to our attention a performance issue in
>> SQLite that seems to be associated with malloc().  If you have
>> insights or corroborating experience with this issue please let me know.
>
> We recently had a malloc/free slowdown issue after changing to VS2008 in
> combination with XP.
> Not sure if it applies in your case but for us this helps:
> --
> if( _get_sbh_threshold() < 512 )
> {
>  _set_sbh_threshold(512);
> }
> ---
>
> I'm unable to run your sqlite3.exe: MSVCR100.dll no found.
>
> Anyway, maybe the above helps.
>
> KInd regards
>
> Marcus
>
>>
>> SQLite supports a "zero-malloc option" (see
>> http://www.sqlite.org/malloc.html#memsys5 for details) which uses its
>> own internal memory allocator rather than system malloc().  Earlier
>> today, we patched the command-line shell to allow the zero-malloc
>> option to be turned on.  If you do:
>>
>>      sqlite3 DATABASE
>>
>> then the regular system memory allocator is used, but if you say:
>>
>>      sqlite3 -heap 100M DATABASE
>>
>> then the MEMSYS5 memory allocator will be used with a pool of 100MB of
>> memory to work with.  (You can adjust the size of your memory pool for
>> whatever you need.)
>>
>> There are win32 and win64 builds of this updated command-line shell
>> compiled using vs2010 here:
>>
>>      http://www.sqlite.org/draft/download.html
>>
>> For certain full-text search queries against a large database, we are
>> seeing speeds which are 3x faster when using "-heap 300M" (the memsys5
>> memory
>> allocator) versus omitting the -heap option and thus using system
>> malloc().
>> This is on windows7.  Similar results are seen with both gcc and
>> vs2010 builds.
>>
>> If you have any large queries that you can run on windows using the
>> command-line shell, I would appreciate you timing those queries using
>> the new shells from the download page, both with "-heap 300M" and
>> without it, and letting me know about any performance differences you see.
>>
>> I also observe that compiling for 64-bit using vs2010 (not an option
>> with my ancient version 2.95.3 gcc cross-compiler) that the queries
>> are an additional 2x faster.  I was surprised at the dramatic
>> performance increase in going from 32-bit to 64-bit.  Is such a
>> speed-up typical?
>>
>> The use of "-heap 300M" seems to not make any performance difference
>> on Linux.
>>
>> Any insights into why this is, what we are doing wrong, or what we can
>> do to improve the performance of malloc() on windows will be
>> appreciated.
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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] WAL locking problem on Windows CE

[Shane Harrelson]

On Fri, Dec 10, 2010 at 9:06 AM, Richard Hipp  wrote:
> On Fri, Dec 10, 2010 at 4:02 AM, Nicklas Larsson 
> wrote:
>
>> Hi,
>>
>> we have been running SQLite (3.6.21) successfully on Windows CE for a
>> while.
>> When upgrading to version 3.7.3 our intent was to enable WAL. However, we
>> ran into problems related to the locking mechanism.
>>
>> In method winShmSystemLock calls are made to LockFileEx with parameters
>> that
>> will make 'winceLockFileEx' always return FALSE.
>>
>> Does anyone know if WAL is expected to work on Windows CE? I can't see how
>> if that is the case.
>>
>
> I don't think shared memory works on winCE.  Shane can answer that
> definitively when he gets online today.
>
> But with SQLite 3.7.4, there is a work-around.  Set PRAGMA
> locking_mode=EXCLUSIVE prior to engaging PRAGMA journal_mode=WAL and shared
> memory won't be used.  See http://www.sqlite.org/wal.html#noshm for details.
>
>
>>
>> Best Regards,
>>
>> Nicklas!
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


The locking primitives used for WAL are unfortunately not supported on
Windows CE.

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

Re: [sqlite] SQLite in WinCE Emulator - Disk I/O Error and change of pragma don´t work

[Shane Harrelson]

According to the MSDN support sites, there are a couple of known
issues with the Windows Mobile Emulator, when using emulated storage
cards.  With an emulated storage card, SetEndOfFile() (which we use
for truncating a file) and FlushFileBuffers() (which we use for
syncing) will fail.  They do not fail for a real storage card or for
other emulated storage.   These errors are handled by SQLite and
reported to the calling application.

Possible work arounds for you when using emulated storage cards include:

-Adding the define SQLITE_NO_SYNC and using the "PRAGMA journal_mode =
PERSIST" journaling modes
-Use system storage instead of an emulated storage card

These would only be needed when testing on the emulator with an
emulated storage card.

Regards.
-Shane


On Tue, Dec 7, 2010 at 2:53 PM, Horacio Rabelo Pereira
 wrote:
> Hi, all
>
>
> I'm using SQLite version 3.7.2 ported to Windows CE, in a application 
> developed using Lazarus
>
>
> I got the compiled SQLITE3.DLL for Windows CE in the site www.parmaja.com
>
>
> For now, I am  using  one emulator to run the native Windows CE application 
> in a desktop computer running Windows 2000 SP4.
>
>
> That emulator is the Microsoft Device Emulator V3.
>
>
> Well, considering that is the first time I work in the environment, I  made a 
> little application in Lazarus that load text files to the SQLite database.
>
>
> The process was done well, until the moment that  will close execute the 
> command “commit”. I this moment the application crashes showing the message 
> “Disk I/O Error”.
>
>
> During the execution, the journal file for database is created, and after the 
> crash it is removed and the database file remains without any modifications.
>
>
> Searching in the Web, I found a note describing a workaround, that consist in 
> change the pragma “jornal_mode” to value “truncate”.
>
>
> For my surprise, all my tries to change the value of pragma “journal_mode” 
> don't work. I try change via SQL DDL script, via SQL Expert and via 
> application.
>
>
> In all the cases, when I access the database after the changes, the values of 
> pragma “journal_mode” it ever “delete”, not the value that I change. I try 
> “truncate”, “memory”, etc, without any success.
>
>
> In resume, I have two problems
>
> 1)The “Disk  I/O Error” when I try to commit the transaction (running in a 
> Windows CE Emulator)
>
> 2)The change of value of pragma that don't work.
>
>
> Thanks in advance
>
> Horacio Pereira
> Belo Horizonte – MG – Brasil
> hora...@bysat.com.br
> ___
> 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] Some floats of 15 digits or less do not round-trip

[Shane Harrelson]

In Visual C, a  "long double" and a "double" are the same, and only
offer 53 bits of precision.
On GCC, a "long double" has 80 bits of precision.
Unfortunately, I don't think there's a way to have Visual C use more precision.
Because of this, round off error will always differ between the two
compilers when using
the "long double" type.

-Shane

On Mon, Nov 29, 2010 at 10:06 PM, Rick Regan  wrote:
> On Mon, Nov 29, 2010 at 3:04 PM, Rick Regan wrote:
>
>
>> So the question remains -- why does the Windows build get it wrong?
>>
>
>
> I think I figured it out. It is due to the use of extended precision. In
> sqlite3.c, if you change "#define LONGDOUBLE_TYPE long double" to "#define
> LONGDOUBLE_TYPE double" and set the FPU precision to 53 bits, you get the
> same wrong result as on Windows.
>
> Obviously, we don't want the same wrong result as on Windows -- so how do we
> fix Windows?
>
> What's the procedure for opening a bug report?
> ___
> 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] SQLite3.7.3_for_Pocket_PC_2003_can't_compile

[Shane Harrelson]

You need to compile with SQLITE_OMIT_WAL for now.

On Thu, Oct 21, 2010 at 10:09 PM, 祝久文  wrote:
> Hello,
> When compiling for Pocket PC 2003(VS2005),it gives out two errors as follows:
> .\os_win.c(1350) : error C2065: 'LOCKFILE_EXCLUSIVE_LOCK' : undeclared 
> identifier
> .\os_win.c(1357) : warning C4013: 'UnlockFileEx' undefined; assuming extern 
> returning int
> Would you give me some solutions,please?
>
> CN,
> Adlindary
> ___
> 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] Porting SQLite 3.7.2 to vxWorks 6.7

[Shane Harrelson]

See:

http://www.sqlite.org/src/info/d1ed743b6e

for changes for SQLITE_OMIT_WAL.

3.7.3 should be available from the downloads page:

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

as of October 8, 2010.  You might need to refresh your browser cache.

Direct link to the amalgamation is:

http://www.sqlite.org/sqlite-amalgamation-3_7_3.zip

HTH.
-Shane

On Mon, Oct 11, 2010 at 12:11 PM, Scott A Mintz  wrote:
> I think there is a bug in 3.7.2 when you define SQLITE_OMIT_WAL.  There
> are references to pagerPagecount() that cannot be resolved because that
> function is defined inside of a #ifndef SQLITE_OMIT_WAL.
>
> I also keep seeing references to 3.7.3 in this list.  But the SQLite
> download page only has links for 3.7.2.  Where can I download 3.7.3?
>
> -Scott
>
> sqlite-users-boun...@sqlite.org wrote on 10/09/2010 07:38:24 AM:
>
>> Hello Scott,
>>
>>     I did some searching with these symbols, they should be in
> 'libos.a'.
>>
>> rtpLib.o - rtpVerifyAndLock
>>
>> pgMgrLib.o - pgMgrPageFree, pgMgrPageAllocAt
>>
>>     you should modify kernel components with your VIP project.
>>
>> Best Regards,
>>
>> Huang ZhiHua
>>
>> 2010/10/9 Scott A Mintz 
>>
>> > Thank you.  Those changes (modified slightly for 3.7.2) allowed me to
>> > create a DKM project that compiles sqlite3.c to libSQLite3.a.
>> >
>> > However, when I link my main VIP project, I get the following
> unresolved
>> > errors:
>> > dld: warning: Undefined symbol 'rtpVerifyAndLock' in file
> 'partialImage.o'
>> > dld: warning: Undefined symbol 'pgMgrPageFree' in file
> 'partialImage.o'
>> > dld: warning: Undefined symbol 'pgMgrPageAllocAt' in file
> 'partialImage.o'
>> >
>> > Those are not directly used by SQLite.  But I have a feeling that one
> or
>> > more of the file I/O, semaphore, locking, or memory library system
> calls
>> > are...
>> >
>> > -Scott
>> >
>> > sqlite-users-boun...@sqlite.org wrote on 10/08/2010 07:38:45 AM:
>> >
>> > > Hello Scott,
>> > >
>> > >     Below is my patch on the latest SQLite 3.7.3. Please notice that
> I
>> > only
>> > > verify it with GCC 4.1.2 compiler in VxWorks 6.6/6.7/6.8(I have not
>> > verify
>> > > it with my real target machine yet).
>> > >
>> > > *** sqlite3.c.orig    2010-10-08 10:42:22.0 +0800
>> > > --- sqlite3.c    2010-10-08 19:24:18.390625000 +0800
>> > > ***
>> > > *** 17,22 
>> > > --- 17,26 
>> > >   ** language. The code for the "sqlite3" command-line shell is also
> in
>> > a
>> > >   ** separate file. This file contains only code for the core SQLite
>> > > library.
>> > >   */
>> > > + #if defined(OS_VXWORKS)
>> > > + #include 
>> > > + #endif /* OS_VXWORKS */
>> > > +
>> > >   #define SQLITE_CORE 1
>> > >   #define SQLITE_AMALGAMATION 1
>> > >   #ifndef SQLITE_PRIVATE
>> > > ***
>> > > *** 22795,22801 
>> > > --- 22799,22811 
>> > >   #include 
>> > >   #include 
>> > >   #include 
>> > > +
>> > > + #if defined(OS_VXWORKS) && defined(_WRS_KERNEL)
>> > > + #include 
>> > > + #else
>> > >   #include 
>> > > + #endif /* OS_VXWORKS */
>> > > +
>> > >   #include 
>> > >   #include 
>> > >
>> > > ***
>> > > *** 24945,24951 
>> > > --- 24955,24965 
>> > >   /*
>> > >    ** Close a file.
>> > >    */
>> > > + #if (OS_VXWORKS < 600)
>> > >   static int semClose(sqlite3_file *id) {
>> > > + #else
>> > > + static int semClose_native(sqlite3_file *id) {
>> > > + #endif
>> > >     if( id ){
>> > >       unixFile *pFile = (unixFile*)id;
>> > >       semUnlock(id, NO_LOCK);
>> > > ***
>> > > *** 25581,25587 
>> > > --- 25595,25607 
>> > >       }
>> > >       return -1;
>> > >     }
>> > > +
>> > > + #if defined(OS_VXWORKS) && defined(_WRS_KERNEL)
>> > > +   got = write(id->h, (char *)pBuf, cnt);
>> > > + #else
>> > >     got = write(id->h, pBuf, cnt);
>> > > + #endif /* OS_VXWORKS */
>> > > +
>> > >   #endif
>> > >     TIMER_END;
>> > >     if( got<0 ){
>> > > ***
>> > > *** 26762,26768 
>> > > --- 26782,26792 
>> > >     semIoFinder,              /* Finder function name */
>> > >     semIoMethods,             /* sqlite3_io_methods object name */
>> > >     1,                        /* shared memory is disabled */
>> > > + #if (OS_VXWORKS < 600)
>> > >     semClose,                 /* xClose method */
>> > > + #else
>> > > +   semClose_native,          /* xClose method */
>> > > + #endif
>> > >     semLock,                  /* xLock method */
>> > >     semUnlock,                /* xUnlock method */
>> > >     semCheckReservedLock      /* xCheckReservedLock method */
>> > > ***
>> > > *** 27517,27523 
>> > >     noLock = eType!=SQLITE_OPEN_MAIN_DB;
>> > >
>> > >
>> > > ! #if defined(__APPLE__) || SQLITE_ENABLE_LOCKING_STYLE
>> > >     struct statfs fsInfo;
>> > >     if( fstatfs(fd, ) == -1 ){
>> > >       ((unixFile*)pFile)->lastErrno = errno;
>> > > --- 27541,27547 
>> > >     noLock = eType!=SQLITE_OPEN_MAIN_DB;
>> > >
>> > >
>> > > ! 

Re: [sqlite] Compiling dll 3.7.3 issue was [BUG] JOIN subquery in FROM with FTS3 table

[Shane Harrelson]

You'll get this if your .DEF file includes any APIs that are not
compiled into your build.
A few sources are:

SQLITE_OMIT_LOAD_EXTENSION=1
SQLITE_ENABLE_COLUMN_METADATA=1
SQLITE_ENABLE_STAT2=1
SQLITE_ENABLE_FTS3=1
SQLITE_ENABLE_RTREE=1

Depending on how you're compiling, you have a couple of options.

1) enable RTREE extensions in your build
2) remove the RTREE APIs from your .DEF file
3) don't use the .DEF (see below)

All of the external SQLite APIs are prefixed with the define
SQLITE_API.  This is usually not defined to any value.  You could try
changing this to use the export declspec directive.  Something like
the following should work:

#define SQLITE_API __declspec(dllexport)

HTH.
-SHane


On Fri, Oct 8, 2010 at 10:03 AM, Shopsland gmail  wrote:
> Hi,
>
> Thanks Dan, Richard and Max for the (ultra) fast answer and Dan and
> Richard for the fix, because, as Max says, the bug is fixed now in
> 3.7.3.
>
> But I have a problem compiling sqlite with VC++. I was able to compile
> 3.7.2 without issues.
>
> With 3.7.3 The linker says:
>
> 'error LNK2001: unresolved external symbol sqlite3_rtree_geometry_callback'
>
> Keep up the good work! :-)
>
> Jochi Martinez
> www.bfreenews.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build

[Shane Harrelson]

This limitation has been around for a while in the MS Visual
debuggers...  I can't find the MSDN article that discusses it, but
once you exceed 64k lines, all bets are off.

Work arounds include using the canonical source to build and debug, or
stripping comment lines, white space etc. from the amalgamation to get
below 64k.


HTH.
-Shane


On Thu, Sep 23, 2010 at 10:54 AM, Ben Harper  wrote:
> I can't confirm this behaviour on anything other than 2010. But I seem to 
> recall the same business a few months ago, when I must have been on 2008.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Virgilio Fornazin
> Sent: 23 September 2010 03:24 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Breakpoints uncorrelated on MSVC amalgamation build
>
> Which MSVC compiler has this bug? There's a link to information on that ?
>
> On Thu, Sep 23, 2010 at 10:17, Ben Harper  wrote:
>
>> I just discovered the MSVC compiler generates bad debug info for source
>> files larger than 64k lines, which is the case with the Sqlite amalgamation.
>> Does anyone know of a workaround?
>>
>> Thanks,
>> Ben
>> ___
>> 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] memory wasted shm mapped file (3.7.2)

[Shane Harrelson]

I tried to reproduce this, and could not.

There are some questions inline below.Additionally, I want to
verify that you've tried this with a version of SQLite containing the
previously linked fix.

-Shane


On Fri, Sep 10, 2010 at 12:54 AM, Max Vlasov  wrote:
>> But as a side effect I got not expected result in other area, when I tried
>> to append 1,500,000 records to this 1.7G file having 5G of free space on the
>> disk, I got the error a user reported recently about win7 64bit, "Disk I/O
>> error". (http://www.mail-archive.com/sqlite-users@sqlite.org/msg54935.html,
>> but this seems was not related to WAL)
>>
>>
>
> Now I the problem is fully reproducible. A modified versions of the steps:
>
> Windows 7 64bit Home Premium, sqlite 3.7.2 in dll
>
> 1. Create db with the table
> CREATE TABLE [TestTable] (
> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
> [Text] VARCHAR(200)
> )
>

Which version of SQLite are you using?   What compilation options?  Do
you have syncs disabled?


> 2. Open the db that should currently be in journal_mode=delete
>

Does the DB contain anything at this point?  Or just the empty table?
What page size are you using?


> 3. Change journal_mode=WAL;

What are you using for the wal_autocheckpoint setting?

>
> 4. BEGIN TRANSACTION
>
> 4. Make 1,300,000 repeated queries
> INSERT INTO TestTable (Text) VALUES ("12345678912345 (the exact length
> of the string = 1152)
>
> 5. While the queries are executed, when the shm file grows to 11M
> (0xAC), the failure occurs with Disk I/O error (both result and extended
> are 10 (SQLITE_IOERR)).
>
> There's a change that there's something wrong with my program, can someone
> do a similar test on another Windows 64bit system?
>
> Thanks
>
> Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory wasted shm mapped file (3.7.2)

[Shane Harrelson]

Michele-

I've looked at trying to reproduce your issue on an 32-bit Windows XP
system using the latest code, and could not.

Even assuming the "worst case" of a 512 byte page size, starting with
a 1.2gb DB file, deleting all the records would result in a WAL file
of roughly the same size containing 2mil+ pages.  Each of the WAL
pages has an 8 byte entry in the SHM file.  The SHM file uses 32k
regions which can each hold 4k entries.   For 2mil+ entries, we would
need around 500 regions.  The SHM file is what is memory mapped, with
each 32k region being mapped into memory.  The Windows implementation
uses an average 48k for each region, so at most we would use around
24mb of memory.

I attempted to reproduce this by creating a 1.5gb DB, containing 17mil
records, each 65 bytes long, using a page size of 512 bytes.  Starting
with this DB in WAL mode, I deleted all records.   Maximum shared
memory usage did not exceed 24mb.

If you're sure you added the fix Dan indicated correctly into your
build, then we're going to need more info on exactly what you're
doing.  What Windows version are you using specifically?  What page
size are you using?   What SQL queries are you executing?  Can you
provide a short series of statements with the CLI to reproduce this?

HTH.
-Shane




On Thu, Sep 9, 2010 at 11:36 AM, Michele Pradella
 wrote:
>  Hi Max, I got the problem in both situations:
>
>   1. I have a journal_mode=DELETE database and I convert it to WAL.
>   2. I create a new database with WAL mode.
>
> I never check the handles in the task manager, but I always see the
> mapped files in vmmap growing up. I think it's the same.
> Anyway I have the memory wasted especially when I have a quite big
> DB(about 1.2GB with  about 17milions of records) and I try to Delete a
> lot of records: in this situation I see mapped files growing up and
> waste a lot of memory (I reached the maximum 32bit windows memory limit
> so my application crash).
>
> ps.With this DB the "SELECT count(ID) FROM table_name" it's very
> slow...it take  minutes(with the sqlite shell)!
>
> Il 09/09/2010 17.04, Max Vlasov ha scritto:
>> On Thu, Sep 9, 2010 at 11:37 AM, Dan Kennedy  wrote:
>>
>>> On Sep 9, 2010, at 1:12 PM, Michele Pradella wrote:
>>>
   Hi, do you have some news about the wasted memory? have you found the
 reason for the windows backend?
>>> Fixed here:
>>>
>>>    http://www.sqlite.org/src/ci/f213e133f6
>>>
>>>
>> Dan, don't know whether it is related, but I detected memory leak in 3.7.2
>> related to handle count increasing.
>>
>> Steps to reproduce
>> (Windows XP SP3, sqlite3.7.2.dll compiled with bcc)
>>
>> 1. Create or use previous db with the table
>> CREATE TABLE [TestTable] (
>> [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
>> [Text] VARCHAR(200)
>> )
>>
>> 2. Open the db that currently in journal_mode=delete
>>
>> 3. Change journal_mode=WAL;
>>
>> 4. BEGIN TRANSACTION
>>
>> 4. Make 50,000 repeated queries
>> INSERT INTO TestTable (Text) VALUES ("12345678912345 (in my case the
>> lengh of this string was about 1100 bytes)
>>
>> 5. See while the queries are processing how handles in Task manager
>> increasing (total about 14). The followiing commit does not help in
>> decreasing the number to the start value.
>>
>> I tried to look with Process explorer, it seems there are many handles
>> titled "section".
>> Also I could not reproduce this when the db is already in WAL mode when
>> opened. Michele, can you tell us what is the mode when you initially open
>> db?
>>
>> Max
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
> --
> Selea s.r.l.
>
>
>        Michele Pradella R
>
>
>        SELEA s.r.l.
>
> Via Aldo Moro 69
> Italy - 46019 Cicognara (MN)
> Tel +39 0375 889091
> Fax +39 0375 889080
> *michele.prade...@selea.com* 
> *http://www.selea.com*
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite fails to build for WinCE 4.2 (WM 2003)

[Shane Harrelson]

On Windows CE, you need to compile with SQLITE_OMIT_WAL. SQLite
currently doesn't support the WAL journaling mode on CE.



On Tue, Aug 24, 2010 at 6:32 AM, Aleksandr Jr.  wrote:

> Hi.
>
> I got a problem when compiling SQLte 3.7.2 for Windows CE 4.2 (Windows
> Mobile 2003) - it cannot be done.
>
> In the function winShmSystemLock(...) is called API-function
> UnlockFileEx(...), but it is implemented only in Windows CE 5.0 and later
> (and there in winShmSystemLock(...) is used constant LOCKFILE_EXCLUSIVE_LOCK
> - it is not exists in WM 2003 SDK).
>
> I suggest solution (2 "patches" in winShmSystemLock(...)):
> 1.
> old code:
>  if( lockType == _SHM_WRLCK ) dwFlags |= LOCKFILE_EXCLUSIVE_LOCK;
>
> new code:
> #if SQLITE_OS_WINCE==1 && _WIN32_WCE!=0x420 && _WIN32_WCE!=420
>  if( lockType == _SHM_WRLCK ) dwFlags |= LOCKFILE_EXCLUSIVE_LOCK;
> #endif // WinCE 4.20?
>
> 2.
> old code:
>  if( lockType==_SHM_UNLCK ){
>rc = UnlockFileEx(pFile->hFile.h, 0, nByte, 0, );
>  }else{
>
> new code:
>  if( lockType==_SHM_UNLCK ){
> #if SQLITE_OS_WINCE==1 && _WIN32_WCE!=0x420 && _WIN32_WCE!=420
>rc = UnlockFileEx(pFile->hFile.h, 0, nByte, 0, );
> #else // WinCE 4.20?
>rc = UnlockFile(pFile->hFile.h, ovlp.Offset, ovlp.OffsetHigh, nByte, 0);
> #endif // WinCE 4.20?
>  }else{
>
> I'm not sure whether I pass "right" parameters to the UnlockFile(...), it
> should be checked
>
> Why 2 conditions ("0x420" and "420")? In eVC 4.0 variable $(CEVER)
> (_WIN32_WCE get value from it) has "decimal form", but in VS2005 and later
> this variable has "hex. form".
>
> ---
> WBR,
> Aleksandr Jr.
>
> ___
> 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] poor insert performance with 3.7/Windows

[Shane Harrelson]

I've not seen the performance degradation you've reported here in my
testing.
If you could provide any more details that would help in reproducing this,
it would be appreciated.

And yes, you can safely modify the SQLITE_FCNTL_SIZE_HINT in os_win.c to be
a "no-op" in the same way as os_unix.c.

-Shane

On Fri, Jul 23, 2010 at 6:26 PM, GB  wrote:

>  Hi all,
>
> I had to recognise that our data-transforming (read: heavily inserting)
> procedures had a massive drop in performance with 3.7. Some
> investigation showed that this is related to "pager_write_pagelist"
> where a hint to an estimated target filesize is given to the filesystem,
> which on Windows translates to a winTruncate. This in turn causes a
> low-level-flush of file metadata and performance drops to nil. Is the
> setting of the file size really needed here or can we safely drop it?
> Dropping it seems to work for ourselves,
>
> Regards,
> Gerd
> ___
> 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] Regression with sqlite-snapshot-201007091257

[Shane Harrelson]

I tried to reproduce the issue with the latest version, as well as with the
referenced snapshot, and could not.
I ran two instances of the sqlite CLI as you indicated without issue.

C:\work\sqlite\win32\Debug>sqlite_snapshot test.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma encoding="UTF-8";
sqlite> pragma auto_vacuum=incremental;
sqlite> pragma journal_mode=truncate;
truncate
sqlite> select sqlite_version();
3.7.0
sqlite> select sqlite_source_id();
2010-07-09 12:57:54 0c32c4bbdd74297767dcf4ec4295f9cc72875af0
sqlite> PRAGMA compile_options;
DEBUG
OMIT_LOAD_EXTENSION
TEMP_STORE=1
TEST
THREADSAFE=1
sqlite>

I included the output of version, source_id, and compile_options for
reference.

What options are you compiling with?  Is there perhaps a journal file
somewhere that you're unaware of?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [csv extension] Error while reading long lines

[Shane Harrelson]

Thanks for the report.  The extension is still very a much a
work-in-progress and any feedback is greatly appreciated.

-Shane


On Sun, Apr 18, 2010 at 12:51 PM, gwenn  wrote:
> Hello,
> There is a little bug/typo in the csv extension when lines exceed 100
> characters:
> *** glibc detected *** sqlite3: realloc(): invalid pointer:
> 0x00ad1a78 ***
> === Backtrace: =
> /lib/libc.so.6[0x7f6dab009d16]
> /lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
> ./libSqliteCsv.so[0x7f6da9ef9dbf]
>
> A possible patch is:
> --- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05 05:14:30.0
> +0100
> +++ csv.c 2010-04-18 18:48:04.0 +0200
> @@ -160,7 +160,7 @@
>     }
>   }
>   if( bShrink ){
> -    pCSV->zRow = realloc( pCSV->zRow, n+1 );
> +    pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
>     pCSV->maxRow = n+1;
>   }
>   return bEol ? pCSV->zRow : 0;
>
> Regards.
> ___
> 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] Index and GLOB

[Shane Harrelson]

On Thu, Apr 15, 2010 at 9:51 AM, Mike Goins wrote:

> > > sqlite> explain query plan SELECT tb_file_key, basename, extension,
> > > path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b' AND
> > >  path GLOB '*';
> > > 0|0|TABLE tb_file WITH INDEX fullpath_idx
> > >
> > >
>
>
> > SQLite will not try to optimize a GLOB (to use an index) if it doesn't
> > contain wild cards -- although it probably should.
> > This optimization has been added to our list for a possible future
> > enhancement.
> >
> > As a workaround, as has been noted, you could simply use the  foo = 'b'
> > instead of foo GLOB 'b'.
>
>
> Is the "explain query plan" lying when it says it will use the index?
>  Can I just slap a trailing "*" on the end of each parameter for it
> really use the index?
>
> Yep, I know I can modify the query, but that requires more prepared
> statements, and each I'm adding extends the connect/prepare time for
> my embedded system.   How I wish the SSE was still around.
>
>
I'm not sure what you mean by "lying".  When I try EXPLAIN QUERY PLAN
on a GLOB 'b' example, it reports that it will not use an index.  Output of
the three cases below:

C:\work\sqlite\misc\glob_index>type test.sql
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (word TEXT);
CREATE INDEX t1_word ON t1(word);
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word GLOB 'b';
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word GLOB 'b*';
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE word = 'b';
C:\work\sqlite\misc\glob_index>sqlite3 < test.sql
0|0|TABLE t1
0|0|TABLE t1 WITH INDEX t1_word
0|0|TABLE t1 WITH INDEX t1_word

You could add a "*" to the end of each parameter if you like, but note that
GLOB 'b' and GLOB 'b*' are different conditions, and will only be the same
if your data set doesn't contain more than one entry beginning with 'b'.

I suppose you could also try something like:

SELECT * FROM t1 WHERE word GLOB 'b*' AND word GLOB 'b'

Where the first GLOB uses the parameter with '*' appended, and the
second GLOB uses the actual parameter.

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

Re: [sqlite] SIGSEGV on INSERT DEFAULT VALUES with triggers

[Shane Harrelson]

On Tue, Apr 13, 2010 at 12:30 PM, Tomasz Ł. Nowak <
tomasz.no...@man.poznan.pl> wrote:

> Shane, you wrote:
> > I could not reproduce this on the current or 3.6.23 build:
> >
> > >./sqlite3 -version
> > 3.6.23
> >
> > >cat script.sql
> > CREATE TABLE current(x,y,z);
> > INSERT INTO current DEFAULT VALUES ('a', 'b', 30);
> >
> > >./sqlite3 -init script.sql
> > -- Loading resources from script.sql
> > Error: near line 2: near "(": syntax error
>
> try exactly this code (it is even simpler than code I sent previously):
>
> CREATE TABLE current (src varchar, price decimal(10,2) NOT NULL, timestamp
> TEXT DEFAULT (DATETIME('NOW')));
> CREATE TRIGGER entry_in_tab_s before insert on current begin insert or
> ignore into tab_s values (new.src, new.dst, 9); end;
> INSERT INTO current DEFAULT VALUES ('a', 30);
>
> I used different compilations (e.g. amalgamation, Debian packages,
> static binary downloadable from sqlite web page). One of them is Debian
> source package, version 3.6.23:
>
> libtool: compile:  gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I./src
> -D_HAVE_SQLITE_CONFIG_H -DNDEBUG -I/usr/include/tcl8.5 -DSQLITE_THREADSAFE=1
> -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1
> -DSQLITE_TEMP_STORE=1 -c sqlite3.c -o sqlite3.o
>
> (enabling load extension doesn't change the described behaviour)
>
>

I missed the "trigger" part in my original reproduction.  Regardless, it has
been fixed previously here:

http://www.sqlite.org/src/info/f3162063fd

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

Re: [sqlite] Index and GLOB

[Shane Harrelson]

On Mon, Apr 12, 2010 at 9:14 AM, Igor Tandetnik  wrote:

> Mike Goins wrote:
> > sqlite> explain query plan SELECT tb_file_key, basename, extension,
> > path FROM tb_file WHERE basename GLOB 'a' AND  extension GLOB 'b' AND
> >  path GLOB '*';
> > 0|0|TABLE tb_file WITH INDEX fullpath_idx
> >
> >
> > The last one with the leading wildcard doesn't look like it should use
> > the index.
>
> SQLite can use the index to satisfy conditions on basename and extension,
> then do a full scan of what's left.
> --
> Igor Tandetnik
>
>

SQLite will not try to optimize a GLOB (to use an index) if it doesn't
contain wild cards -- although it probably should.
This optimization has been added to our list for a possible future
enhancement.

As a workaround, as has been noted, you could simply use the  foo = 'b'
instead of foo GLOB 'b'.

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

Re: [sqlite] Android database corruption

[Shane Harrelson]

On Fri, Mar 26, 2010 at 11:38 AM, cliff 2  wrote:

>
> Hi,
>
> Hopefully someone here can help where the android guys haven't been able
> to.
> We are having an issue with our sqlite database running on the android
> platform. We are accessing the db from the sdcard on an android phone
> running android 2.1.
>
> It works perfectly 99% of the time, but every now and again, we get
> database
> corruption appear randomly. We have not changed any pragmas, so synchronous
> is set to full and journal_mode delete. It does not correspond to any power
> failures, or application crashes, and will succeed in pulling back data
> from
> the database for hundreds of calls, and then fail on a query that has
> worked
> many times before. It can occur in a number of native calls. We have
> performed integrity checks on the db in question and they return ok (prior
> to the corruption - androids response to a corrupt db is to delete it, so
> we
> can't see its state after corruption).
>
> The database in question is downloaded as a complete binary and then
> accessed read-only using android's rawQuery call.
>
> A couple of typical stack traces for when the corruption occurs:
>
>  03-26 14:09:50.572 E/DatabaseHelper( 1253):
> android.database.sqlite.SQLiteDatabaseCorruptException: database disk image
> is malformed: , while compiling: SELECT o.FieldId,o.Reference, o.Category,
> o.OIndex,o.Description, c.Choice, c.Derivative FROM Option o, Choice c
> WHERE
> o.FieldId = c.FieldId and o.Reference = ? ORDER BY o.Option, o.OIndex
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteProgram.native_compile(Native Method)
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:59)
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:49)
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
>
> android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49)
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
>
> android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1221)
>  03-26 14:09:50.572 E/DatabaseHelper( 1253): at
> android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1194)
>
> We can run the exact same query hundreds of times and it is fine. Then all
> of a sudden it fails. Another typical stack trace:
>
>  03-24 14:54:04.678 E/AsyncTask( 6828): RuntimeException while executing
> background thread
>  1045  03-24 14:54:04.678 E/AsyncTask( 6828):
> android.database.sqlite.SQLiteDatabaseCorruptException: database disk image
> is malformed
>  1046  03-24 14:54:04.678 E/AsyncTask( 6828):  at
> android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
>  1047  03-24 14:54:04.678 E/AsyncTask( 6828):  at
> android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:75)
>  1048  03-24 14:54:04.678 E/AsyncTask( 6828):  at
> android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:288)
>  1049  03-24 14:54:04.678 E/AsyncTask( 6828):  at
> android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:269)
>  1050  03-24 14:54:04.678 E/AsyncTask( 6828):  at
> android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171)
>  1051  03-24 14:54:04.678 E/AsyncTask( 6828):  at
> android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248)
>
> The one above is while trying to move to the first row in a cursor.
>
> (android method native_fill_window source available here:
>
> http://www.netmite.com/android/mydroid/frameworks/base/core/jni/android_database_SQLiteQuery.cpp
> and native_compile
>
> http://www.netmite.com/android/mydroid/frameworks/base/core/jni/android_database_SQLiteProgram.cpp
> )
>
> We have been looking for some way we could be corrupting the database
> through incorrect use, however we are now out of options. We have triple
> checked we are opening and closing all cursors and connections correctly
> and
> checked there aren't multiple threads accessing the db at the same time and
> nothing seems out of the ordinary when corruption occurs.
>
> As all we are doing is reading the database, I really can't see how we can
> be corrupting it. I was wondering if anyone on this forum had experienced
> anything similar in the past, or could suggest things we could do to track
> down what is causing the corruption. From what I've read on the SQLite site
> this type of corruption should be all but impossible, however it is
> definitely happening.
>
> If you need any more information to help track down the problem, please let
> me know. Thanks in advance for any tips, or advice to help solve this.
>
>
> Cliff
> --
> View this message in context:
> http://old.nabble.com/Android-database-corruption-tp28044218p28044218.html
> Sent 

Re: [sqlite] "out of memory" in ANALYZE with SQLITE_ENABLE_STAT2

[Shane Harrelson]

On Thu, Mar 25, 2010 at 10:22 AM, Martin Wilck
wrote:

> Hello,
>
> with sqlite 3.6.23 with SQLITE_ENABLE_STAT2, I get
> an "out of memory" error running the following simple SQL code:
>
> CREATE TABLE dist_T (
> d_row INTEGER PRIMARY KEY,
> dist TEXT UNIQUE NOT NULL
> );
>
> INSERT INTO dist_T VALUES (NULL, '');
> INSERT INTO dist_T VALUES (NULL, 'a');
> INSERT INTO dist_T VALUES (NULL, 'b');
> INSERT INTO dist_T VALUES (NULL, 'c');
> INSERT INTO dist_T VALUES (NULL, 'd');
> INSERT INTO dist_T VALUES (NULL, 'e');
> INSERT INTO dist_T VALUES (NULL, 'f');
> INSERT INTO dist_T VALUES (NULL, 'g');
> INSERT INTO dist_T VALUES (NULL, 'h');
> INSERT INTO dist_T VALUES (NULL, 'i');
>
> ANALYZE;
>
>
> This is always reproducable if dist_T contains 10 or more rows. With 0-9
> rows, I don't see the problem. Also not if SQLITE_ENABLE_STAT2 is not set.
>
> The problem happens when interpreting the results of "SELECT
> idx,sampleno,sample FROM 'main'.sqlite_stat2".on line 66154 in sqlite3.c
> (sqlite3AnalysisLoad()), apparently because
> sqlite3_column_bytes() returned a size of 0 bytes for column 2 in line
> 66145:
>
> 66145   int n = sqlite3_column_bytes(pStmt, 2);
> 66146   if( n>24 ){
> 66147 n = 24;
> 66148   }
> 66149   pSample->nByte = (u8)n;
> 66150   pSample->u.z = sqlite3DbMallocRaw(dbMem, n);
> 66151   if( pSample->u.z ){
> 66152 memcpy(pSample->u.z, z, n);
> 66153   }else{
> 66154 db->mallocFailed = 1;
> 66155 break;
> 66156   }
>
> I am including some gdb output. Note that pResultSet[2].n = 0.
>
> (gdb) bt
> #0  sqlite3AnalysisLoad (db=0x55af68, iDb=0) at sqlite3.c:66154
> #1  0x2af0c5884bd8 in sqlite3VdbeExec (p=0x569e38) at sqlite3.c:57585
> #2  0x2af0c587c02d in sqlite3Step (p=0x569e38) at sqlite3.c:51342
> #3  0x2af0c587c249 in sqlite3_step (pStmt=0x569e38) at sqlite3.c:51402
> #4  0x00403eea in shell_exec (db=0x55af68, zSql=0x55aee0
> "ANALYZE;",
> xCallback=0x402ba1 , pArg=0x7fffe5377850,
> pzErrMsg=0x7fffe5375aa8) at shell.c:1012
> #5  0x00408019 in process_input (p=0x7fffe5377850, in=0x55ab50)
> at shell.c:2236
> #6  0x00406d1a in do_meta_command (zLine=0x5537d0 ".read",
> p=0x7fffe5377850) at shell.c:1860
> #7  0x00407deb in process_input (p=0x7fffe5377850, in=0x0) at
> shell.c:2195
> #8  0x00409183 in main (argc=1, argv=0x7fffe5378eb8) at
> shell.c:2616
>
>
> (gdb) p *pIdx
> $4 = {zName = 0x56c23d "sqlite_autoindex_dist_T_1", nColumn = 1,
> aiColumn = 0x56c230, aiRowEst = 0x56c234,
>   pTable = 0x56b328, tnum = 3, onError = 99 'c', autoIndex = 1 '\001',
> zColAff = 0x56bce8 "ab", pNext = 0x0,
>   pSchema = 0x55bb88, aSortOrder = 0x56c23c "", azColl = 0x56c228,
> aSample = 0x570058}
> (gdb) p *((Vdbe*) pStmt)
>
> $5 = {db = 0x55af68, pPrev = 0x0, pNext = 0x569e38, nOp = 15, nOpAlloc =
> 42, aOp = 0x570d88, nLabel = 4,
>   nLabelAlloc = 26, aLabel = 0x0, apArg = 0x570fd0, aColName =
> 0x571188, pResultSet = 0x570ef0, nResColumn = 3,
>   nCursor = 1, apCsr = 0x570fd0, errorAction = 2 '\002', okVar = 0
> '\0', nVar = 0, aVar = 0x570fd0,
>   azVar = 0x570fd0, magic = 3186757027, nMem = 4, aMem = 0x570eb8,
> cacheCtr = 3, pc = 8, rc = 0, zErrMsg = 0x0,
>   explain = 0 '\0', changeCntOn = 0 '\0', expired = 0 '\0', runOnlyOnce
> = 0 '\0', minWriteFileFormat = 255 '377',
>   inVtabMethod = 0 '\0', usesStmtJournal = 0 '\0', readOnly = 1 '\001',
> isPrepareV2 = 0 '\0', nChange = 0,
>   btreeMask = 1, startTime = 0, aMutex = {nMutex = 0, aBtree = {0x0
> }}, aCounter = {0, 0},
>   zSql = 0x5685b0 "SELECT idx,sampleno,sample FROM
> 'main'.sqlite_stat2", pFree = 0x0, nFkConstraint = 0,
>   nStmtDefCons = 0, iStatement = 0, pFrame = 0x0, nFrame = 0, expmask = 0}
>
> (gdb) p ((Vdbe*) pStmt)->pResultSet[0]
> $8 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
> = 0, db = 0x55af68,
>   z = 0x568fd8 "sqlite_autoindex_dist_T_1", n = 25, flags = 514, type =
> 3 '\003', enc = 1 '\001', xDel = 0,
>   zMalloc = 0x568fd8 "sqlite_autoindex_dist_T_1"}
>
> (gdb) p ((Vdbe*) pStmt)->pResultSet[1]
> $9 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0}, r
> = 0, db = 0x55af68, z = 0x568208 "0",
>   n = 1, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
> zMalloc = 0x568208 "0"}
>
> (gdb) p ((Vdbe*) pStmt)->pResultSet[2]
> $10 = {u = {i = 0, nZero = 0, pDef = 0x0, pRowSet = 0x0, pFrame = 0x0},
> r = 0, db = 0x55af68, z = 0x568bc8 "",
>   n = 0, flags = 514, type = 3 '\003', enc = 1 '\001', xDel = 0,
> zMalloc = 0x568bc8 ""}
>
>
> Regards
> Martin
>
>
> --
> Dr. Martin Wilck
> PRIMERGY System Software Engineer
> x86 Server Engineering
>
> Fujitsu Technology Solutions GmbH
> Heinz-Nixdorf-Ring 1
> 33106 Paderborn, Germany
>
> Phone:  ++49 5251 525 2796
> Fax: 

Re: [sqlite] Available alternatives to syntax diagrams in documentation

[Shane Harrelson]

On Thu, Mar 25, 2010 at 7:24 PM, P Kishor <punk.k...@gmail.com> wrote:

> On Thu, Mar 25, 2010 at 6:22 PM, P Kishor <punk.k...@gmail.com> wrote:
> > On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson <sh...@sqlite.org>
> wrote:
> >>
> >>
> >> On Thu, Mar 25, 2010 at 7:06 PM, P Kishor <punk.k...@gmail.com> wrote:
> >>>
> >>> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson <sh...@sqlite.org>
> wrote:
> >>> > I added a psuedo-BNF renderer for the bubble syntax graph data at
> >>> >
> >>> > http://www.sqlite.org/docsrc/artifact/873cf35adf
> >>> >
> >>> > to go along with the text based bubble graph at
> >>> >
> >>> > http://www.sqlite.org/docsrc/artifact/645054606c
> >>> >
> >>> > These are not meant to replace the official syntax specification at
> >>> >
> >>> > http://www.sqlite.org/syntaxdiagrams.html
> >>> >
> >>>
> >>>
> >>> This is gorgeous, but is it not possible to just have plain text docs?
> >>> Much like most other database manuals do?
> >>>
> >>> http://www.postgresql.org/docs/8.4/static/ddl-basics.html
> >>> http://dev.mysql.com/doc/refman/5.5/en/create-table.html
> >>>
> >>> or am I missing something?
> >>>
> >>>
> >>> --
> >>> Puneet Kishor
> >>>
> >>
> >> Is this
> >>
> >> http://www.sqlite.org/lang.html
> >>
> >> or
> >>
> >> http://www.sqlite.org/lang_createtable.html
> >>
> >> what you're looking for?  Otherwise, I'm not sure what you're asking
> for.
> >>
> >>
> >
> > The links you provided goes back to diagrams. I believe the OP was
> > asking if there was plain text documentation available. Whether or not
> > he was asking that, I am wondering... is there plain text
> > documentation available at all? I mean, where can I see syntax spelled
> > out like so
> >
> > DELETE FROM qualified_table-name WHERE expr
> >
> > It used to be available until the docs were migrated to the diagrams.
> >
>
>
>
> Here is another way of conveying the problem here. Go to your
> browser's settings and turn off images. Then try to read the
> documentation at sqlite.org.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


If you scroll down on on any of the lang_*.html pages, like

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

you'll see text based descriptions of the individual command.
Even with graphics turned off.

All of the individual pages are referenced from:

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

Is that what you mean?

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

Re: [sqlite] Available alternatives to syntax diagrams in documentation

[Shane Harrelson]

On Thu, Mar 25, 2010 at 7:22 PM, P Kishor <punk.k...@gmail.com> wrote:

> On Thu, Mar 25, 2010 at 6:19 PM, Shane Harrelson <sh...@sqlite.org> wrote:
> >
> >
> > On Thu, Mar 25, 2010 at 7:06 PM, P Kishor <punk.k...@gmail.com> wrote:
> >>
> >> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson <sh...@sqlite.org>
> wrote:
> >> > I added a psuedo-BNF renderer for the bubble syntax graph data at
> >> >
> >> > http://www.sqlite.org/docsrc/artifact/873cf35adf
> >> >
> >> > to go along with the text based bubble graph at
> >> >
> >> > http://www.sqlite.org/docsrc/artifact/645054606c
> >> >
> >> > These are not meant to replace the official syntax specification at
> >> >
> >> > http://www.sqlite.org/syntaxdiagrams.html
> >> >
> >>
> >>
> >> This is gorgeous, but is it not possible to just have plain text docs?
> >> Much like most other database manuals do?
> >>
> >> http://www.postgresql.org/docs/8.4/static/ddl-basics.html
> >> http://dev.mysql.com/doc/refman/5.5/en/create-table.html
> >>
> >> or am I missing something?
> >>
> >>
> >> --
> >> Puneet Kishor
> >>
> >
> > Is this
> >
> > http://www.sqlite.org/lang.html
> >
> > or
> >
> > http://www.sqlite.org/lang_createtable.html
> >
> > what you're looking for?  Otherwise, I'm not sure what you're asking for.
> >
> >
>
> The links you provided goes back to diagrams. I believe the OP was
> asking if there was plain text documentation available. Whether or not
> he was asking that, I am wondering... is there plain text
> documentation available at all? I mean, where can I see syntax spelled
> out like so
>
> DELETE FROM qualified_table-name WHERE expr
>
> It used to be available until the docs were migrated to the diagrams.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Is this

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

or

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

what you're looking for?  Otherwise, I'm not sure what you're asking for.

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

Re: [sqlite] Available alternatives to syntax diagrams in documentation

[Shane Harrelson]

On Thu, Mar 25, 2010 at 7:06 PM, P Kishor <punk.k...@gmail.com> wrote:

> On Thu, Mar 25, 2010 at 5:59 PM, Shane Harrelson <sh...@sqlite.org> wrote:
> > I added a psuedo-BNF renderer for the bubble syntax graph data at
> >
> > http://www.sqlite.org/docsrc/artifact/873cf35adf
> >
> > to go along with the text based bubble graph at
> >
> > http://www.sqlite.org/docsrc/artifact/645054606c
> >
> > These are not meant to replace the official syntax specification at
> >
> > http://www.sqlite.org/syntaxdiagrams.html
> >
>
>
> This is gorgeous, but is it not possible to just have plain text docs?
> Much like most other database manuals do?
>
> http://www.postgresql.org/docs/8.4/static/ddl-basics.html
> http://dev.mysql.com/doc/refman/5.5/en/create-table.html
>
> or am I missing something?
>
>
> --
> Puneet Kishor
>
>
Is this

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

or

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

what you're looking for?  Otherwise, I'm not sure what you're asking for.

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

Re: [sqlite] Available alternatives to syntax diagrams in documentation

[Shane Harrelson]

I added a psuedo-BNF renderer for the bubble syntax graph data at

http://www.sqlite.org/docsrc/artifact/873cf35adf

to go along with the text based bubble graph at

http://www.sqlite.org/docsrc/artifact/645054606c

These are not meant to replace the official syntax specification at

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

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

Re: [sqlite] SIGSEGV on INSERT DEFAULT VALUES with triggers

[Shane Harrelson]

Can you provide more details?
What options are you using?
What version of the source? Amalgamation?  Preprocessed?  Complete package?

I could not reproduce this on the current or 3.6.23 build:

>./sqlite3 -version
3.6.23

>cat script.sql
CREATE TABLE current(x,y,z);
INSERT INTO current DEFAULT VALUES ('a', 'b', 30);

>./sqlite3 -init script.sql
-- Loading resources from script.sql
Error: near line 2: near "(": syntax error

If you are using the amalgamation or other pre-processed sources, you should
view the warnings at
   http://www.sqlite.org/compile.html#omitfeatures

Trying to OMIT or ENABLE features with compile options without using the
"canonical" sources can lead to issues like you are seeing.

HTH.
-Shane


2010/3/24 Tomasz Ł. Nowak 

> Hi!
>
> A bug resulting in a crash (segmentation fault) of sqlite3 has been
> detected. Please find attached the script causing problems and a debug
> session log.
>
> Tested to be vulnerable:
> linux 3.6.23
> linux 3.6.22
> linux 3.6.4
> solaris 3.5.1
>
> Tested to be invulnerable:
> linux 2.8.17
>
> Best wishes
> Tomasz Nowak
>
> ___
> 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] corruption problem with attached macintosh database

[Shane Harrelson]

On Wed, Mar 24, 2010 at 7:20 PM, Dave Dyer  wrote:

>
> >
> >Could you help us by adding any of the following details ?
> >
> >What OS is the Mac running ?
>
> OSX 10.4.11 for me, but also snow leopard.
>
> >What OS is the PC running ?
>
> Windows 2003 server for me, but also XP (note the file systems are all mac
> file systems)
>
> >What protocol is being used to access the Mac file share ?
>
> Presumably windows standard file sharing protocol over tcp
>
> >Is the file-sharing host accessing the database as a shared file, or as a
> file on its hard disk ?
>
> The mac acting as file host is accessing the file as a local file.
>
>
> >Does this happen without any data-changing instructions ?  In other words
> can I get this fault using only _open, ATTACH and lots of SELECT commands
> until something falls over ?  Or even just repeated _open, ATTACH and _close
> until something falls over ?
>
> No data on this question.  The purpose of this querty setup
> is to copy some data into an auxialiary database.
>
> >> Note that the main database, which is updated periodically, doesn't have
> >> a corruption problem,
> >
> >Are both 'main' and 'auxiliary' on in the same folder, being accessed the
> same way ?
>
> Yes.  And significantly, there is no corruption problem with simultaneous
> updates to the main database.
>
>
> >> and that this is only a problem with databases resident
> >> on a mac.  The same scenario, with databases resident on the PC disk,
> works
> >> fine.
> >
> >When the databases are on the PC disk, what protocol is the Mac using to
> access them ?
>
> Presumably the same.  Before recent versions of OSX this kind of access
> used "samba" server.
>
>
>

What journaling mode are you using?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Available alternatives to syntax diagrams in documentation

[Shane Harrelson]

More updates for Chrome support.

http://www.sqlite.org/docsrc/artifact/981f61a5e4


On Tue, Mar 23, 2010 at 11:50 PM, Shane Harrelson <sh...@sqlite.org> wrote:

> I knocked out a quick TCl script that uses *exactly* the same syntax graph
> specification as bubble-generator.tcl but attempts to render the graphs
> without GIFs.   You can see the script here:
>
>
> http://www.sqlite.org/docsrc/finfo?name=art/syntax/bubble-generator-text.tcl
>
> You can view the output here:
>
> http://www.sqlite.org/docsrc/artifact/149ec0f41f
>
> The script still needs a little work (especially in browsers other than
> FF), but the graphs are reasonable similar to the GIF based versions and
> usable.
>
> I have no idea what kinds of things a "screen reader" would need to make
> this version useful, but if you let me know, I will try to add them.
>
> HTH.
> -Shane
>
>
>
> On Mon, Mar 22, 2010 at 2:29 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
>
>> On Mon, Mar 22, 2010 at 11:13:25PM +0530, Roger Binns scratched on the
>> wall:
>> > -BEGIN PGP SIGNED MESSAGE-
>> > Hash: SHA1
>> >
>> > Jay A. Kreibich wrote:
>> > > On Mon, Mar 22, 2010 at 05:57:18PM +0530, Roger Binns scratched on the
>> wall:
>> > >
>> > >> If you are the developer type yourself then it is a simple matter of
>> > >> programming :-)
>> > >
>> > >   As someone that's been spending a lot of time with that script,
>> > >   excuse me while I run around the room laughing maniacally.
>> >
>> > Perhaps you may want to review the definition SMOP
>>
>>   Yes, I'm well aware.  I was confirming, not trying to contradict.
>>
>>  And in defense of the script, the biggest issue is that I don't
>>  know TCL and it's somewhat casual syntax for literals can be very
>>  frustrating for someone that has not used this class of language.
>>
>>   -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] Available alternatives to syntax diagrams in documentation

[Shane Harrelson]

I knocked out a quick TCl script that uses *exactly* the same syntax graph
specification as bubble-generator.tcl but attempts to render the graphs
without GIFs.   You can see the script here:

http://www.sqlite.org/docsrc/finfo?name=art/syntax/bubble-generator-text.tcl

You can view the output here:

http://www.sqlite.org/docsrc/artifact/149ec0f41f

The script still needs a little work (especially in browsers other than FF),
but the graphs are reasonable similar to the GIF based versions and usable.

I have no idea what kinds of things a "screen reader" would need to make
this version useful, but if you let me know, I will try to add them.

HTH.
-Shane


On Mon, Mar 22, 2010 at 2:29 PM, Jay A. Kreibich  wrote:

> On Mon, Mar 22, 2010 at 11:13:25PM +0530, Roger Binns scratched on the
> wall:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > Jay A. Kreibich wrote:
> > > On Mon, Mar 22, 2010 at 05:57:18PM +0530, Roger Binns scratched on the
> wall:
> > >
> > >> If you are the developer type yourself then it is a simple matter of
> > >> programming :-)
> > >
> > >   As someone that's been spending a lot of time with that script,
> > >   excuse me while I run around the room laughing maniacally.
> >
> > Perhaps you may want to review the definition SMOP
>
>   Yes, I'm well aware.  I was confirming, not trying to contradict.
>
>  And in defense of the script, the biggest issue is that I don't
>  know TCL and it's somewhat casual syntax for literals can be very
>  frustrating for someone that has not used this class of language.
>
>   -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] Bug Report: DateTime incorrect for Windows

[Shane Harrelson]

On Fri, Feb 26, 2010 at 1:49 AM, 4eanlss <4ean...@engineer.com> wrote:

>
> Shane,
>
> I apologize for my quick snap response.
> I have debugged the code and have identified what is happening.
> The calculation in my environment results in 0 (zero) for this:
>  static const sqlite3_int64 max32BitValue =
>  (sqlite3_int64)20 + (sqlite3_int64)20 +
>  (sqlite3_int64)294967296;
>
> Which breaks all of the following datetime calculations.
> When compiled with the type sqlite3_uint64 then it works correctly.
>
> I am working on a much simpler winCurrentTime solution but I'm stilling
> creating all test cases to verify the algorithm before posting.
>
> 4eanlss
>
>
I'd be worried that other 64 bit calculations are going wrong as well.

Can you verify what type sqlite3_int64 is being set to in your environment?

Also can you try the following (note the L suffix on the constants):

static const sqlite3_int64 max32BitValue =
 (sqlite3_int64)20L + (sqlite3_int64)20L +
 (sqlite3_int64)294967296L;

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

Re: [sqlite] Bug in porter stemmer

[Shane Harrelson]

Additionally, your algorithm reference for step1c is from the "Snowball
English (Porter2)" algorithm.
The implementation used in SQLite is for the original "Porter" algorithm
discussed here:
http://tartarus.org/~martin/PorterStemmer/

HTH.
-SHane



On Wed, Feb 24, 2010 at 10:05 AM, D. Richard Hipp  wrote:

> We got the Porter stemmer code directly from Martin Porter.
>
> I'm sorry it does not work like you want it to.  Unfortunately, we
> cannot change it now without introducing a serious incompatibility
> with the millions and millions of applications already in the field
> that are using the existing implementation.
>
> FTS3 has a pluggable stemmer module.  You can write your own stemmer
> that works "correctly" if you like, and link it in for use in your
> applications.  We will also investigate making your recommended
> changes for FTS4.  However, in order to maintain backwards
> compatibility of FTS3, we cannot change the stemmer algorithm, even to
> fix a "bug".
>
> On Feb 24, 2010, at 9:59 AM, James Berry wrote:
>
> > Can somebody please clarify the bug reporting process for sqlite? My
> > understanding is that it's not possible to file bug reports
> > directly, and that the advise is to write to the user list first.
> > I've done that (below) but have no response so far and am concerned
> > that this means the bug report will just be forgotten others, as
> > well as by me.
> >
> > How does this bug move from a message on a list to a ticket (and
> > ultimately a patch, we hope) in the system?
> >
> > James
> >
> > On Feb 22, 2010, at 2:51 PM, James Berry wrote:
> >
> >> I'm writing to report a bug in the porter-stemmer algorithm
> >> supplied as part of the FTS3 implementation.
> >>
> >> The stemmer has an inverted logic error that prevents it from
> >> properly stemming words of the following form:
> >>
> >>  dry -> dri
> >>  cry -> cri
> >>
> >> This means, for instance, that the following words don't stem the
> >> same:
> >>
> >>  dried -> dri   -doesn't match-   dry
> >>  cried -> cry   -doesn't match-   cry
> >>
> >> The bug seems to have been introduced as a simple logic error by
> >> whoever wrote the stemmer code. The original description of step 1c
> >> is here: http://snowball.tartarus.org/algorithms/english/stemmer.html
> >>
> >>  Step 1c:
> >>  replace suffix y or Y by i if preceded by a non-vowel which
> is
> >> not the first letter of the word (so cry -> cri, by -> by, say ->
> >> say)
> >>
> >> But the code in sqlite reads like this:
> >>
> >> /* Step 1c */
> >> if( z[0]=='y' && hasVowel(z+1) ){
> >>   z[0] = 'i';
> >> }
> >>
> >> In other words, sqlite turns the y into an i only if it is preceded
> >> by a vowel (say -> sai), while the algorithm intends this to be
> >> done if it is _not_ preceded by a vowel.
> >>
> >> But there are two other problems in that same line of code:
> >>
> >>  (1) hasVowel checks whether a vowel exists anywhere in the string,
> >> not just in the next character, which is incorrect, and goes
> >> against the step 1c directions above. (amplify would not be
> >> properly stemmed to amplifi, for instance)
> >>
> >>  (2) The check for the first letter is not performed (for words
> >> like "by", etc)
> >>
> >> I've fixed both of those errors in the patch below:
> >>
> >>  /* Step 1c */
> >> -  if( z[0]=='y' && hasVowel(z+1) ){
> >> + if( z[0]=='y' && isConsonant(z+1) && z[2] ){
> >>z[0] = 'i';
> >>  }
> >>
> >> ___
> >> 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
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[Shane Harrelson]

On Tue, Feb 23, 2010 at 1:56 AM, 4eanlss <4ean...@engineer.com> wrote:

> Shane Harrelson <shane.harrel...@...> writes:
>
> >
> > I was unable to duplicate your error with the CLI and 3.6.22 (compiled
> with
> > cygwin\gcc or msvc):
> Ok, so Borland not supported and windows API not followed.
> Just as I expected from open source software.
>
>

I never said Borland was not supported.  I said I wasn't able to reproduce
the problem with the two compilers currently available to me.   It would be
very difficult to maintain build environments for every compiler and
compiler version used by SQLite users.
If you check the full source, you'll see where we've made specific
modifications for many compilers not used by us, like the Borland compilers,
all done and tested with the help of users.

I'm not certain what you mean by "windows API not followed".  Could you
provide more information?  In non CE builds, the only Windows API called in
the winCurrentTime() function is GetSystemTimeAsFileTime(). This returns a
FILETIME structure containing two DWORDS (u32s) representing the the number
of 100-nanosecond intervals since January 1, 1601.

The MSDN docs on the Windows API that use and manipulate system time values
state that it will be zero or positive, and less than 0x8000
(except for functions such as SetFileTime() which use 0x to
indicate that the previous file time should be preserved.)   Regardless, an
int64 is large enough to represent all the 100-nanosecond intervals from
January 1, 1601 through sometime in the year 30,828.

We use casting and 64-bit math to convert and manipulate these two DWORDS as
64-bit *signed *integers.   We avoid using uint64s because it's difficult
enough working through all the various compiler quirks out there for
manipulating 64-bit types with just int64s without introducing another
type.   Case in point, the odd initialization of some of the "consts" we use
in the winCurrentTime() function and the use of "math" instead of shifting.

I suspect that one of these "quirks" in your build environment is being
exposed by the code in winCurrentTime().Perhaps you could debug through
the code and find where the calculations are going wrong?   In this way we
could provide more help.

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

Re: [sqlite] Writing and reading a csv using sqlite3

[Shane Harrelson]

On Mon, Feb 15, 2010 at 4:31 PM, Simon Slavin  wrote:

>
> On 15 Feb 2010, at 9:28pm, Roger Binns wrote:
>
> > Simon Slavin wrote:
> >> It uses one when it outputs, but it won't accept the same format when it
> inputs.  So the program is itself inconsistent: however you define 'csv
> format', either its output or input function is broken.
> >
> > There is a ticket covering this:
> >
> >  http://www.sqlite.org/src/tktview?name=c25aab7e7e
>
> Ahha.  And that points to
>
> http://www.sqlite.org/cvstrac/tktview?tn=3276
>
> which explains and fixes the problem in one way.  Okay.
>
> Simon.
>
>

In the past, the shell tool (CLI) was never "officially" supported.   There
were no testscripts to verify correct operation, or guarantees of included
features or compatibility from one version to the next, unlike with the
"core" SQLite library.

We've taken steps to more formally support this, as is indicated by the
addition of some test scripts for it here:
http://www.sqlite.org/src/dir?name=tool   However, there are still many
features of the CLI that we've yet to write tests for, including tests for
CSV support.

Work has begun to "revamp" the CSV support entirely, by implementing it with
a virtual table interface.   You can see the beginnings of it here in this
branch:  http://www.sqlite.org/src/timeline?t=csv_ext   There are a couple
more third party CSV extensions for SQLite that are much more advanced, and
it's unlikely that this implementation will ever reach that level of
sophistication or support much more than the current import/export needs of
the CLI.

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

Re: [sqlite] round problem?

[Shane Harrelson]

On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs  wrote:

> Shane Harelson:
> > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite
> adds
> > 0.5 and then truncates.Because of floating point precision, some
> numbers
> > can not be represented exactly... causing the odd rounding you saw in
> your
> > examples.
>
> I've just had a look "under the covers", and indeed it seems that
> SQLite has its own printf implementation. Makes sense, as I think the
> C standard itself is also implementation-defined in this case, and
> this might be the kind of thing that SQLite coders want control over.
>
> It was hasty to conclude that round-to-even is the rule, as one more
> example shows:
>
> sqlite> select round(40226.5);
> 40227.0
>
> I think the 40223.5 case is just an example of binary/decimal floating
> point incompatibility. As Kernighan and Plauger put it:
> "Floating-point numbers are a lot like sandpiles: Every time you move
> one you lose a little sand and pick up a little dirt."
>
> I'm a little surprised that it's going wrong with a number ending in
> 0.5 though, I'd have thought that that would be expressible perfectly
> in binary without loss. I don't know enough about it though.
>
> Phil Hibbs.
>
>

As you and Igor point out, the numbers being tested can be accurately
represented.   However, as part of the SQLite's internal printf()
implementation, the floating point value is shifted to (loosely) scientific
form and manipulated for formatting.   So 40224.5 is converted to something
like 4.02245e4 before printing.   The errors get introduced as part of this
manipulation.  I'm looking at how this can be improved.

HTH.
-Shane

PS.  Sorry if this got posted multiple times.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] round problem?

[Shane Harrelson]

On Mon, Feb 15, 2010 at 12:52 PM, Phil Hibbs  wrote:

> Shane Harelson:
> > Under the covers, when the second term to ROUND(X,y) is omitted, SQLite
> adds
> > 0.5 and then truncates.Because of floating point precision, some
> numbers
> > can not be represented exactly... causing the odd rounding you saw in
> your
> > examples.
>
> I've just had a look "under the covers", and indeed it seems that
> SQLite has its own printf implementation. Makes sense, as I think the
> C standard itself is also implementation-defined in this case, and
> this might be the kind of thing that SQLite coders want control over.
>
> It was hasty to conclude that round-to-even is the rule, as one more
> example shows:
>
> sqlite> select round(40226.5);
> 40227.0
>
> I think the 40223.5 case is just an example of binary/decimal floating
> point incompatibility. As Kernighan and Plauger put it:
> "Floating-point numbers are a lot like sandpiles: Every time you move
> one you lose a little sand and pick up a little dirt."
>
> I'm a little surprised that it's going wrong with a number ending in
> 0.5 though, I'd have thought that that would be expressible perfectly
> in binary without loss. I don't know enough about it though.
>
> Phil Hibbs.
>
>

As you and Igor point out, the numbers being tested can be accurately
represented.   However, as part of the SQLite's internal printf()
implementation, the floating point value is shifted to (loosely) scientific
form and manipulated for formatting.   So 40224.5 is converted to something
like 4.02245e4 before printing.   The errors get introduced as part of this
manipulation.  I'm looking at how this can be improved.

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

Re: [sqlite] round problem?

[Shane Harrelson]

http://www.sqlite.org/lang_corefunc.html#round

Under the covers, when the second term to ROUND(X,y) is omitted, SQLite adds
0.5 and then truncates.Because of floating point precision, some numbers
can not be represented exactly... causing the odd rounding you saw in your
examples.

I'll see what I can do to update the documentation and/or make the rounding
more consistent.

HTH.
-Shane


On Mon, Feb 15, 2010 at 12:03 PM, Phil Hibbs  wrote:

> Igor:
> > http://en.wikipedia.org/wiki/Rounding#Round_half_to_even
>
> So, are you saying round-half-to-even is the SQLite behaviour? I would
> have expected it to have used the "normal" mathematical convention of
> round-half-away-from-zero. The reason this is "normal" mathematical
> behaviour is that any decimal result is likely to be a truncation of
> the real result, e.g. "pi = 3.14195". The SQL standard leaves it up to
> the implementation:
>
> sec 4.4.1:
> An approximation obtained by rounding of a numerical value N for
> an  T is a value V representable in T such
> that the absolute value of the difference between N and the nu-
> merical value of V is not greater than half the absolute value
> of the difference between two successive numerical values repre-
> sentable in T. If there are more than one such values V, then it is
> implementation-defined which one is taken.
>
> So I guess "round half to even" is a reasonable interpretation, but
> I'd like to see this, and all implementation-defined behaviour,
> documented.
>
> Phil Hibbs.
> --
> Don't you just hate self-referential sigs?
> ___
> 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] any command to find last rowid in a table

[Shane Harrelson]

Assuming you have a table with the following schema:

CREATE TABLE t1 (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   col2 REAL,
   col3 TEXT
);

your C code *could* look something like the following:

 sqlite3_exec(db, "INSERT INTO t1 (col2,col3) VALUES (1.0,'row one');",
0, 0, 0);
 rowid = sqlite3_last_insert_rowid(db);

HTH.
-Shane



On Mon, Feb 8, 2010 at 2:59 PM, Vasanta  wrote:

> I understand "SELECT last_insert_rowid()" gives rowird, but I have to call
> these SQL statements in C language code, for that I have to sqlite3_prepare
> and sqlite3_step() calls, I am looking for sample of sqlite3_step, how that
> return the rowid, can I call like this:
>
> int rowid;
> sqlite3_stmt *pStmt;
>
> sqlite3(pDB, "SELECT last_insert_rowid()", -1, , 0);
> rowid = sqlite3_step(pStmt);
>
> On Mon, Feb 8, 2010 at 2:36 PM, Kees Nuyt  wrote:
>
> > On Mon, 8 Feb 2010 11:44:39 -0500, Vasanta
> >  wrote:
> >
> > > I tried to use this function call
> > > "sqlite3_last_insert_rowid()<
> > http://www.sqlite.org/c3ref/last_insert_rowid.html>"
> > > calling from C language function, but it always returns zero, any
> idea?.
> > > I have valid DB handle.
> >
> > The function only returns the rowid of the last successful
> > INSERT statement on the same connection / DB handle. It
> > tells you which row has been inserted.
> >
> > Perhaps you expected it to predict which row would be
> > inserted on the next INSERT statement?
> >
> > If you need a new ID for every row you insert, don't try to
> > find out which value to use, but let SQLite do the work.
> >
> > Sample code:
> >
> > CREATE TABLE t1 (
> >id INTEGER PRIMARY KEY AUTOINCREMENT,
> >col2 REAL,
> >col3 TEXT
> > );
> >
> > BEGIN;
> > INSERT INTO t1 (col2,col3)
> >VALUES (julianday('now'),'row one');
> > SELECT last_insert_rowid();
> > INSERT INTO t1 (col2,col3)
> >VALUES (julianday('now'),'row two');
> > SELECT last_insert_rowid();
> > INSERT INTO t1 (col2,col3)
> >VALUES (julianday('now'),'row three');
> > SELECT last_insert_rowid();
> > COMMIT;
> >
> > Read http://www.sqlite.org/c3ref/last_insert_rowid.html
> > again for more details.
> > --
> >  (  Kees Nuyt
> >  )
> > c[_]
> >  ___
> > 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] system.data.sqlite & encryption

[Shane Harrelson]

I've taken a cursory glance at the encryption support in the
system.data.sqlite C# wrapper from http://sqlite.phxsoftware.com
It does appear, that you could, with a little effort, modify the wrapper to
work with the SEE extension.   I don't see anything in the wrapper that
would make it impossible.   The only tricky bit would be utilizing the
support for passing the encryption key on the open commands (although you
should be able to sidestep this and use the encryption pragmas of the SEE
extension directly.)

HTH.
-Shane

On Sat, Feb 6, 2010 at 7:21 PM, Sylvain Pointeau <sylvain.point...@gmail.com
> wrote:

> I already recompiled it, but integrating the encryption extension is again
> another step.
> how to be sure of that before to buy the extension ($2'000)  ?
>
> Sylvain
>
> On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson <sh...@sqlite.org> wrote:
>
>> Yes.   If you're willing to compile (and possibly modify) the
>> system.data.sqlite libraries for yourself, it should work.
>>
>> -Shane
>>
>> On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau <
>> sylvain.point...@gmail.com
>> > wrote:
>>
>> > I posted on this forum
>> > http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx
>> >
>> > The answer was: "I'm afraid not.  This is an open-source project, and
>> the
>> > encryption Dr. Hipp sells is not open source."
>> >
>> > but as the license of system.data.sqlite is "Released to the public
>> domain,
>> > use at your own risk!"
>> > I think I can integrate the encrypted sqlite lib, that I will buy, into
>> > system.data.sqlite.
>> >
>> > I would just like to have a bit of guidance...
>> >
>> > Best regards,
>> > Sylvain
>> >
>> > On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson <sh...@sqlite.org>
>> wrote:
>> >
>> > > I think you should probably ask this question on the
>> System.data.sqlite
>> > > support forums at
>> > >
>> > > http://sqlite.phxsoftware.com/forums
>> > >
>> > > I'm not familiar with enough with their ADO .NET implementation to
>> answer
>> > > your question.
>> > >
>> > > -Shane
>> > >
>> > >
>> > > On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
>> > > sylvain.point...@gmail.com> wrote:
>> > >
>> > > > Hi all,
>> > > >
>> > > > I am using System.data.sqlite from my .NET project,
>> > > > but I am planning to access my db from C/C++ and C# / C++/CI
>> > > >
>> > > > The point is that I want an encryted database, and I plan to use the
>> > > > extension from
>> > > > http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
>> > > >
>> > > > how to use / implement this exension in system.data.sqlite?
>> > > > or are they compatible both?
>> > > >
>> > > > thank you in advance for your help.
>> > > >
>> > > > Best regards,
>> > > > Sylvain
>> > > > ___
>> > > > 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] system.data.sqlite & encryption

[Shane Harrelson]

I've taken a cursory glance at the encryption support in the
system.data.sqlite C# wrapper from http://sqlite.phxsoftware.com
It does appear, that you could, with a little effort, modify the wrapper to
work with the SEE extension.   I don't see anything in the wrapper that
would make it impossible.   The only tricky bit would be utilizing the
support for passing the encryption key on the open commands (although you
should be able to sidestep this and use the encryption pragmas of the SEE
extension directly.)

HTH.
-Shane


On Sat, Feb 6, 2010 at 7:21 PM, Sylvain Pointeau <sylvain.point...@gmail.com
> wrote:

> I already recompiled it, but integrating the encryption extension is again
> another step.
> how to be sure of that before to buy the extension ($2'000)  ?
>
> Sylvain
>
> On Sat, Feb 6, 2010 at 1:44 AM, Shane Harrelson <sh...@sqlite.org> wrote:
>
>> Yes.   If you're willing to compile (and possibly modify) the
>> system.data.sqlite libraries for yourself, it should work.
>>
>> -Shane
>>
>> On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau <
>> sylvain.point...@gmail.com
>> > wrote:
>>
>> > I posted on this forum
>> > http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx
>> >
>> > The answer was: "I'm afraid not.  This is an open-source project, and
>> the
>> > encryption Dr. Hipp sells is not open source."
>> >
>> > but as the license of system.data.sqlite is "Released to the public
>> domain,
>> > use at your own risk!"
>> > I think I can integrate the encrypted sqlite lib, that I will buy, into
>> > system.data.sqlite.
>> >
>> > I would just like to have a bit of guidance...
>> >
>> > Best regards,
>> > Sylvain
>> >
>> > On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson <sh...@sqlite.org>
>> wrote:
>> >
>> > > I think you should probably ask this question on the
>> System.data.sqlite
>> > > support forums at
>> > >
>> > > http://sqlite.phxsoftware.com/forums
>> > >
>> > > I'm not familiar with enough with their ADO .NET implementation to
>> answer
>> > > your question.
>> > >
>> > > -Shane
>> > >
>> > >
>> > > On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
>> > > sylvain.point...@gmail.com> wrote:
>> > >
>> > > > Hi all,
>> > > >
>> > > > I am using System.data.sqlite from my .NET project,
>> > > > but I am planning to access my db from C/C++ and C# / C++/CI
>> > > >
>> > > > The point is that I want an encryted database, and I plan to use the
>> > > > extension from
>> > > > http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
>> > > >
>> > > > how to use / implement this exension in system.data.sqlite?
>> > > > or are they compatible both?
>> > > >
>> > > > thank you in advance for your help.
>> > > >
>> > > > Best regards,
>> > > > Sylvain
>> > > > ___
>> > > > 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] system.data.sqlite & encryption

[Shane Harrelson]

Yes.   If you're willing to compile (and possibly modify) the
system.data.sqlite libraries for yourself, it should work.

-Shane

On Fri, Feb 5, 2010 at 5:05 AM, Sylvain Pointeau <sylvain.point...@gmail.com
> wrote:

> I posted on this forum
> http://sqlite.phxsoftware.com/forums/p/2170/8904.aspx
>
> The answer was: "I'm afraid not.  This is an open-source project, and the
> encryption Dr. Hipp sells is not open source."
>
> but as the license of system.data.sqlite is "Released to the public domain,
> use at your own risk!"
> I think I can integrate the encrypted sqlite lib, that I will buy, into
> system.data.sqlite.
>
> I would just like to have a bit of guidance...
>
> Best regards,
> Sylvain
>
> On Mon, Feb 1, 2010 at 4:41 PM, Shane Harrelson <sh...@sqlite.org> wrote:
>
> > I think you should probably ask this question on the System.data.sqlite
> > support forums at
> >
> > http://sqlite.phxsoftware.com/forums
> >
> > I'm not familiar with enough with their ADO .NET implementation to answer
> > your question.
> >
> > -Shane
> >
> >
> > On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
> > sylvain.point...@gmail.com> wrote:
> >
> > > Hi all,
> > >
> > > I am using System.data.sqlite from my .NET project,
> > > but I am planning to access my db from C/C++ and C# / C++/CI
> > >
> > > The point is that I want an encryted database, and I plan to use the
> > > extension from
> > > http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
> > >
> > > how to use / implement this exension in system.data.sqlite?
> > > or are they compatible both?
> > >
> > > thank you in advance for your help.
> > >
> > > Best regards,
> > > Sylvain
> > > ___
> > > 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] any command to find last rowid in a table

[Shane Harrelson]

As stated before, in general, you should not specify the ROWID on inserts,
but instead, let the database engine choose it for you.
This is true of most/all database engines.

The syntax you're trying below is not supported.   Indeed, even it were,
max(ROWID) is the maximum ROWID *in use*.  Trying to insert another row with
the same ROWID will result in a collision.

At the very least you would need to do something like "SELECT max(ROWID)+1
from EVENTLOG_TBL;", use sqlite_step() to run this query, and bind the ROWID
returned here into your insert statement and run that.

You can read more on SQLite's ROWID usage at
http://www.sqlite.org/search?q=rowid

HTH.
-Shane




On Fri, Feb 5, 2010 at 3:22 PM, Vasanta  wrote:

> This is my actual string, still not working:
>
>  const char *replaceSql = "INSERT INTO "EVENTLOG_TBL \
>   "(_ROWID_, component, facilityId, logLevel,"\
>   "textMessage, binMessage) VALUES(?,?,?,?,?,?);
> SELECT max(ROWID) from EVENTLOG_TBL";
>
>
> On Fri, Feb 5, 2010 at 3:05 PM, Vasanta  wrote:
>
> > Thanks jay.
> >
> > Can I combine like this:
> >
> > "INSERT INTO trends(UnitID,HeureTrends,DateTrends) VALUES(?,?,?);SELECT
> > max(ROWID) FROM table-name";
> >
> > or
> >
> > "INSERT INTO trends(UnitID,HeureTrends,DateTrends) VALUES(?,?,?);SELECT
> > last_insert_rowid() AS [ID]";
> >
> >
> > On Fri, Feb 5, 2010 at 2:49 PM, Jay A. Kreibich  wrote:
> >
> >> On Fri, Feb 05, 2010 at 02:28:33PM -0500, Vasanta scratched on the wall:
> >>  > command "SELECT rowid from table-name;"  gives all rows from 1 to 100
> >> for
> >> > total 100 rows, any command to get last rowid?. I need insert from
> last
> >> > rowid onwards (if table already 100 records, need to insert from 101
> >> > onwards)
> >>
> >>  SELECT max(ROWID) FROM table-name;
> >>
> >>
> >>  You shouldn't be setting ROWIDs manually, however.  Just insert the
> >>  row and let SQLite pick the ROWID.  If you have a ROWID alias in the
> >>  form of an INTEGER PRIMARY KEY, in most cases you should still just
> >>  let SQLite pick the value, possibly with AUTOINCREMENT.
> >>
> >>   -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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: DateTime incorrect for Windows

[Shane Harrelson]

I was unable to duplicate your error with the CLI and 3.6.22 (compiled with
cygwin\gcc or msvc):

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT datetime('now') as NOW;
2010-02-05 16:33:50
sqlite> SELECT datetime('now','utc') as UTC;
2010-02-05 21:33:50
sqlite> SELECT datetime('now','localtime') as MST;
2010-02-05 11:33:50

The date/time functions are described at
http://www.sqlite.org/lang_datefunc.html

It states that the '"utc" assumes that the string to its left is in the
local timezone and adjusts that string to be in UTC' so I think at least the
results for datetime('now','utc') are consistent.

-Shane

On Fri, Feb 5, 2010 at 1:04 AM, <4ean...@engineer.com> wrote:

>
>
>
>  Hello,
>
> I'm compiling the 3.6.21 amalgamation with Borland command-line compiler
> version 5.2 for Windows XP.
> My compilation command is bcc32.exe -5 -H- -O2 -RT- -a8 -x- maintest.c
> sqlite3.c
> where maintest.c is similar to the 
> http://www.sqlite.org/quickstart.htmlsample C code.
>
> The 3 SQL statements executed in maintest.c are:
> SELECT datetime('now') as NOW;
> SELECT datetime('now','utc') as UTC;
> SELECT datetime('now','localtime') as MST;
>
> Current local-time is 22:50 MST.
> The results are:
> NOW = 1601-01-01 00:04:49
> UTC = 1601-01-01 07:04:49
> MST = 1601-01-01 17:04:49
>
> These are not as expected.
>
> If I adjust function winCurrentTime() to use sqlite3_uint64 instead of
> sqlite3_int64 then the math for system time works out correctly.
> I did this by adding option -DSQLITE_FIX_WINTIME=1 to my compiler command
> line and code modifications as show below.
>
> Here are the results with unsigned int64:
> NOW = 2010-02-05 05:51:05
> UTC = 2010-02-05 12:51:05
> MST = 2010-02-04 22:51:05
>
> My compiler's defines are:
> _INTEGRAL_MAX_BITS
> __TLS__
> __FLAT__
> _Windows
> __BORLANDC__
> __CDECL__
> __CONSOLE__
> _MT
> __CGVER__
> __MT__
> __BCOPT__
> _CPPUNWIND
> __TURBO__
> _WIN32
> _M_IX86
> __WIN32__
>
> Here is the code inserted immediately before winCurrentTime() function:
> #if defined(SQLITE_FIX_WINTIME) && SQLITE_FIX_WINTIME
> #define sqlite3_int64 sqlite3_uint64
> #endif
>
> Then the code inserted immediately after winCurrentTime() function:
> #if defined(SQLITE_FIX_WINTIME) && SQLITE_FIX_WINTIME
> #undef sqlite3_int64
> #endif
>
>
>
> ___
> 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] journal files

[Shane Harrelson]

Actually, I believe the entire 28 byte header is zeroed, not just the first
4 bytes.   See the zeroJournalHdr() function in pager.c for details.

-Shane


On Tue, Feb 2, 2010 at 7:09 AM, Pavel Ivanov  wrote:

> What do you want to see in journal files? You can execute 'PRAGMA
> journal_mode = persist' and all information in journal file except
> first 4 bytes will be left on disk for you. Is it enough?
>
> Pavel
>
> On Tue, Feb 2, 2010 at 7:00 AM, rishabh  wrote:
> >
> > hey,
> >
> > I am coding for an application wherein i need to check the journal files
> as
> > in i dont want it to get deleted after the commit. how to go about it?
> where
> > in the Sqlite3.c code can i edit it.
> >
> > also, is it possible to customize the sqlite code for the journal file a
> bit
> > as per my needs?
> >
> > thanx
> > --
> > View this message in context:
> http://old.nabble.com/journal-files-tp27419280p27419280.html
> > Sent from the SQLite mailing list archive at Nabble.com.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] system.data.sqlite & encryption

[Shane Harrelson]

I think you should probably ask this question on the System.data.sqlite
support forums at

http://sqlite.phxsoftware.com/forums

I'm not familiar with enough with their ADO .NET implementation to answer
your question.

-Shane


On Sun, Jan 31, 2010 at 11:54 AM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> Hi all,
>
> I am using System.data.sqlite from my .NET project,
> but I am planning to access my db from C/C++ and C# / C++/CI
>
> The point is that I want an encryted database, and I plan to use the
> extension from
> http://www.hwaci.com/sw/sqlite/prosupport.html#crypto
>
> how to use / implement this exension in system.data.sqlite?
> or are they compatible both?
>
> thank you in advance for your help.
>
> Best regards,
> Sylvain
> ___
> 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] grabbing row contents?

[Shane Harrelson]

Instead of using sqlite3_exec() and a callback, use sqlite3_prepare_v2() on
your statement and then run it with sqlite3_step() in a loop (there will be
one iteration be row of the result set).  sqlite3_step() will return
something other than SQLITE_ROW when it's done.   When it returns
SQLITE_ROW, you can then use the sqlite3_column_*() apis to look at the
result row.

HTH.
-Shane

On Fri, Jan 22, 2010 at 10:57 AM, gary clark  wrote:

>
> Hiya,
>
> Got a quick question. I perform the following on a table:
>
> select * from table
>
> How do I know when the select has completed all its transactions?
>
> sqlite3_exec(db,query.c_str(),callback, this, _err);
>
> I'm using the above statement,the callback is getting called as expected.
>
> Thanks,
> garyc
>
> ___
> 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] SQL Crash with sqlite 3.6.22 commandline

[Shane Harrelson]

Can you verify that changes for ticket
http://www.sqlite.org/src/info/1258875e07 checked-in yesterday resolve your
issue?

Thanks.
-Shane

On Fri, Jan 22, 2010 at 9:51 AM, ve3meo  wrote:

> I just discovered that attachments can be sent through this newsgroup so I
> have attached a small database with which you should be able to reproduce
> the problem. The one table in it has a field collated RMNOCASE. The
> following query produces these results in three different versions of
> sqlite:
>
> 3.5.4 works perfectly
> 3.6.17 gracefully reports an error - missing RMNOCASE collation
> 3.6.21 crash
>
> SELECT
>  Name COLLATE NOCASE
> FROM
>  AddressTable
> WHERE
>  Name LIKE '%_';
>
> 3.5.4 carried the COLLATE NOCASE override at the beginning of the SELECT
> through to the comparison in the WHEN. The later ones do not, and they
> 'progress' from reporting an error to a crash.
>
> This regression renders queries, on databases having collations unavailable
> to the sqlite in use, that were developed on older versions of sqlite
> problematic when run from newer versions.
>
> Regards,
> Tom
>
> "D. Richard Hipp"  wrote in
> message news:41371dfd-279f-429d-9186-476efb63e...@hwaci.com...
> >I am unable to reproduce this problem.  Using the script below, with
> > RMNOCASE changed to just NOCASE, everything works fine on the SQLite
> > command-line shell on the website on Linux.  I also tried various
> > other versions of SQLite with the same result.
> >
> >
> > On Jan 21, 2010, at 8:00 AM, Hub Dog wrote:
> >
> >> I hava a table. The table schema is
> >>
> >> CREATE TABLE AddressTable
> >> (
> >>  AddressID INTEGER PRIMARY KEY ,
> >>  AddressType INTEGER ,
> >>  Name TEXT COLLATE RMNOCASE ,
> >>  Street1 TEXT ,
> >>  Street2 TEXT ,
> >>  City TEXT ,
> >>  State TEXT ,
> >>  Zip TEXT ,
> >>  Country TEXT ,
> >>  Phone1 TEXT ,
> >>  Phone2 TEXT ,
> >>  Fax TEXT ,
> >>  Email TEXT ,
> >>  URL TEXT ,
> >>  Latitude INTEGER ,
> >>  Longitude INTEGER ,
> >>  Note BLOB
> >> ) ;.
> >>
> >> if I execute following sql to query data , the sqlite 3.6.22 command
> >> line
> >> downloaded from www.sqlite.org will crash.
> >>
> >> SELECT
> >>  Adr.Name COLLATE NOCASE AS AddressName
> >> FROM
> >>  AddressTable AS Adr
> >> WHERE
> >>  Adr.Name LIKE '%_'.
> >>
> >> if I change the Adr.Name to AddressName  , the sql execute result is
> >> ok.
> >>
> >> SELECT
> >>  Adr.Name COLLATE NOCASE AS AddressName
> >> FROM
> >>  AddressTable AS Adr
> >> WHERE
> >>  AddressName LIKE '%_' ;
> >>
> >> it seems the crash was related with the collate RMNOCASE of
> >> AddressTable
> >> table's field Name.
> >> in default sqlite command line, there is no rmnocase collation. so I
> >> mapped
> >> it to the default  nocase collation.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > D. Richard Hipp
> > d...@hwaci.com
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: .echo ON in command line tool doesn't echo all statements

[Shane Harrelson]

On Fri, Jan 8, 2010 at 5:17 AM, Kees Nuyt  wrote:

>
> (Repeated with the proper BUG: subject)
>
> On Mon, 04 Jan 2010 19:55:13 +0100, Kees Nuyt
>  wrote:
>
> > Between versions 3.6.19 and 3.6.20 something has changed
> > which causes the command line tool to ignore the .echo ON
> > command for some statements.
> >
> > CREATE and INSERT statements aren't echoed anymore,
> > but SELECT statements are echoed correctly.
> >
> > Needless to say that this makes it a bit harder to debug SQL
> > scripts or prepare demo output to help other people.
> >
> > Platform tested: MS Windows (Vista 32-bit).
>
> Additional info:
>
> sqlite3 command line tool executable for Windows straight
> from http://www.sqlite.org/download.html
>
> Version 3.6.22 has the same bug.
>
> Can anyone confirm this behaviour?
> --
>  (  Kees Nuyt
>  )
> c[_]
>
>

Confirmed.   Fixed here:   http://www.sqlite.org/src/vinfo/7080ae3bc3
Thanks for the report.

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

Re: [sqlite] BUG Report -- schema.test does not check for authorization in build

[Shane Harrelson]

Thanks for the reports on the TCL test cases.  I think
http://www.sqlite.org/src/vinfo/97f8a886b6 should correct everything you
reported.

On Tue, Dec 29, 2009 at 1:02 PM, Noah Hart  wrote:

> Test schema-13.1 fails with
> Error: {authorization not available in this build}
>
> Test needs to be bracket with
> ifcapable auth {
>
> do_test schema-13.1 {
>  set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]
>  db function hello hello
>  db function hello {}
>  db auth auth
>
>
> db auth fails because tclsqlite.c has
>
> #ifdef SQLITE_OMIT_AUTHORIZATION
>Tcl_AppendResult(interp, "authorization not available in this
> build", 0);
>return TCL_ERROR;
> #else
>
>
>
> Regards,
>
> Noah Hart
>
>
>
> CONFIDENTIALITY NOTICE:
> This message may contain confidential and/or privileged information. If you
> are not the addressee or authorized to receive this for the addressee, you
> must not use, copy, disclose, or take any action based on this message or
> any information herein. If you have received this message in error, please
> advise the sender immediately by reply e-mail and delete this message. Thank
> you for your cooperation.
>
>
> ___
> 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] sqlite3_complete always returning 1?

[Shane Harrelson]

Thanks for the report.

You're right that it was indeed inconsistent with the stated requirements.
I've checked in a fix here:
http://www.sqlite.org/src/vinfo/76eca7295cf7df4bef013af6c8c37251300cd383

We're still evaluating whether to keep the code change or fall back to
editing the requirement, as the issue has been around for a long time and
this change affects the existing operation of the interface.

-Shane

On Thu, Dec 17, 2009 at 10:39 AM, Nicolas Rivera wrote:

> >>If I am doing something wrong, does anyone have an idea of what it could
> be?
>
> >It doesn't sound like it.
>
> That's all I was looking for.
>
> Thanks!
>
> Nick
> ___
> 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] Putting images into SQLite.

[Shane Harrelson]

As others have said, there are lots of ways to store the image data directly
in the DB with BLOBs, encoding, etc.   Alternatively, you could store the
pics separate from the DB and just store the path to the pic file in the DB.

-Shane


On Sun, Dec 6, 2009 at 8:35 PM, Ted Rolle, Jr.  wrote:

> From what I read, it is necessary to have a programmatic interface to
> put images into a database.  True?
>
> ---
> 
> 3.14159265358979323846264338327950  Let the spirit of pi spread
> 2884197169399375105820974944592307  all around the world!
> 8164062862089986280348253421170679  http://pi314.at PI VOBISCUM!
> 
> ___
> 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] substr in sqlite3 3.6.20

[Shane Harrelson]

On Fri, Nov 20, 2009 at 2:30 PM, priimak  wrote:

> Hi.
>
> What happened to substr function. I upgraded from 3.6.7 to 3.6.20 and
> found following difference.
>
> in 3.6.7
>
> $ sqlite3 a.db
> sqlite> create table X ( v text not null );
> sqlite> insert into X ( v ) values ( "123456789" );
> sqlite> select substr( v, 0, 5 ) from X;
> 12345
>
>
> in 3.6.20
>
> sqlite> create table X ( v text not null );
> sqlite> insert into X ( v ) values ( "123456789" );
> sqlite> select substr( v, 0, 5 ) from X;
> 1234
>
> You can see that they behave differently with 3.6.7 doing the right thing.
> I did not find any mentioning of that here
> http://www.sqlite.org/changes.html
> If that is a bug, it *is* a major one!
>
>
It's not a bug.  substr() is suppose to be "1" based.
Older versions behaved incorrectly.
It was "fixed" by this ticket:
http://www.sqlite.org/cvstrac/tktview?tn=3628

-Shane



> --
> Dmitri Priimak
>
> ___
> 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] bad data in a database file or bug?

[Shane Harrelson]

in one case you do:

select count(*) from feeds  where _id = '0';

in the other you do:

select count(*) from feeds  where _id = 0;  <--- note the missing quotes

this shouldn't make a difference (since SQLite is typeless), but I wonder if
it is in this case.
what is the type of _id?

can you run the query that DRH suggested?

SELECT _id, typeof(_id) FROM feeds;


-Shane



On Tue, Nov 17, 2009 at 8:57 PM, Vasu Nori  wrote:

> if I vacuum it, database file sems to have correct data.
> sqlite> vacuum
>   ...> ;
> sqlite> select count(*) from feeds  where _id = '0';
> count(*)
> --
> 1
>
>
> I can't tell if this is a bug in sqlite3 or if the database file is corrupt
> but sqlite3 can't recognize it when I do "pragma integrity_check".
>
>
> On Tue, Nov 17, 2009 at 5:09 PM, Vasu Nori  wrote:
>
> > hi all
> >
> > attached is a database file with a strange behavior.
> > it has a table "feeds". has the following data (just selected 2 columns
> for
> > discussion purpose)
> >
> > sqlite> select _id, feed from feeds;
> > _id feed
> >
> > --
> >
>  
> -
> >
> > 1
> >
> http://www.google.com/calendar/feeds/1acpbk5jj328tc9up029ml6nog%40group.calendar.google.com/private/full
> > 2
> >
> http://www.google.com/calendar/feeds/1hpeekrpepcq41if0r6cnrf3nc%40group.calendar.google.com/private/full
> > 3
> >
> http://www.google.com/calendar/feeds/43p69tkmn5k339b10e9f1rrqgo%40group.calendar.google.com/private/full
> > 4
> >
> http://www.google.com/calendar/feeds/9rdtjbibdsve143hvcbrf2ru1c%40group.calendar.google.com/private/full
> > 5
> >
> http://www.google.com/calendar/feeds/fpbubjo06bb9t86o2bm9c3456c%40group.calendar.google.com/private/full
> > 6
> >
> http://www.google.com/calendar/feeds/m76b3e2ssuos4qm6qijb7o2pak%40group.calendar.google.com/private/full
> > 7
> >
> http://www.google.com/calendar/feeds/tijtufmt5st0m4rnvjhug7f8r8%40group.calendar.google.com/private/full
> > 8
> >
> http://www.google.com/calendar/feeds/tomastaylor%40gmail.com/private/full
> > 9
> >
> http://www.google.com/calendar/feeds/ttevp5oljm14v1m176spijqiag%40group.calendar.google.com/private/full
> > 10
> >
> http://www.google.com/calendar/feeds/usa%40holiday.calendar.google.com/private/full
> > 11
> >
> http://www.google.com/calendar/feeds/uu7524qmpvorujaq6hnfnvfrtg%40group.calendar.google.com/private/full
> > 0   https://mail.google.com/mail/g/?client=1256578631218
> >
> > 13
> >
> http://www.google.com/m8/feeds/groups/tomastay...@gmail.com/base2_property-android?v=3.0
> >
> > database file doesn't 'seem' to be corrupt
> >
> > sqlite> pragma integrity_check;
> > integrity_check
> > ---
> > ok
> >
> >
> > but can't select the row _id = 0
> >
> > sqlite> select count(*) from feeds  where _id = 0;
> > count(*)
> > --
> > 0
> >
> > what gives? any insights into this interesting behavior?
> >
> > thanks
> >
> >
> ___
> 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] Why does LIKE operator affect order of query plan?

[Shane Harrelson]

On Sun, Nov 15, 2009 at 11:41 AM, P Kishor  wrote:
> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>> I have a query with joined inline views that runs in about 100ms against
>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>
>> But when I use the LIKE operator instead of the = operator, the order of
>> the query plan changes, though the same indexes are involved, and the
>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>> can do to guide SQLite here.
>>
>> In broad terms, what is it about the use of the LIKE operator that
>> causes SQLite to re-order the plan, and is there any way to guide?
>>
>
> LIKE doesn't use indexes, although there are tricks that these SQL
> gurus will probably tell that could help you with workarounds. LIKE
> does a full scan.
>
>

There are certain conditions in which a LIKE (or GLOB) term will be
transformed by the query planner into an equivalent expression to
allow the use of indices.  Please see
http://www.sqlite.org/optoverview.html for a more in-depth discussion.


>> And what does the "from" column in the explain plan results refer to?
>> Are the values the tables/relations in the query statement? If so, how
>> are  they mapped? In order of appearance in the statement, so that 0 is
>> the first table mentioned in the statement?
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SQLite 3.6.20 problem

[Shane Harrelson]

Fixed in check-in http://www.sqlite.org/src/info/8097c64acf


On Mon, Nov 9, 2009 at 10:52 AM, D. Richard Hipp  wrote:
>
>
> Begin forwarded message:
>
>> From: Ken Zalewski 
>> Date: November 9, 2009 9:45:22 AM EST
>> To: d...@hwaci.com
>> Subject: SQLite 3.6.20 problem
>>
>> Rich,
>>
>> It seems that the "-batch" option in 3.6.20 has stopped working.
>>
>> If I run "sqlite3 -batch blah.db < create_tables.sql"
>>
>> the blah.db database is never created.
>>
>> In versions up to and including 3.6.19, the blah.db file would
>> properly be created.
>
>
>> In fact, the -batch option, even when used with an already-existing
>> database, seems to have no effect.  If I run "sqlite3 -batch
>> existing.db < create_tables.sql", no tables are actually created.
>>
>> This seems to be a major change.  Does -batch serve any purpose any
>> longer?
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Conflict with SQLite ODBC Driver and MS Office 2007 ?

[Shane Harrelson]

I know of no conflicts.  I regularly test against SQL Server 2005,
MySQL, OracleXE, and SQLite all via ODBC interfaces (specifically
Christian Werner's ODBC interface for SQLite).  The various ODBC
interfaces all play happily.

HTH
-Shane


On Wed, Oct 14, 2009 at 4:15 PM, Jack Ort  wrote:
> Hello!  Not sure where to ask this question, so I apologize if this is not
> appropriate.  I want to use SQLite as the basis for a new project where I
> work.  I'm new to SQLite - this would be my first use of it beyond some
> simple test applications.  Plan to use REBOL to develop a GUI frontend.  For
> reporting, I thought I might use MS Access called by REBOL code to provide
> canned reports against the SQLite database.  I believe I need to use the
> SQLite ODBC driver (http://www.ch-werner.de/sqliteodbc/) for Access to link
> to the SQLite db.
>
> Now my question: my IT Manager is witholding approval of using SQLite
> because he thinks he's heard of a conflict between the SQLite ODBC driver
> and the SQL Server 2005 "stuff" that gets installed as part of a MS Office
> 2007 installation.  (Some users have Office 2007 - I have Office 2003 so I
> cannot test.)
>
> I suspect someone meant to refer to a conflict with SQL Server Express
> instead of SQLite.  I cannot find anything in Google searches.
>
> Does anyone here know of any conflicts I should be concerned about?  This
> would be in a XP SP2 environment.
>
> Thanks in advance!
> -Jack
> ___
> 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] Sqlite testing

[Shane Harrelson]

I'd like to help, but you've not provided any relevant information.
Obviously, in order to test, you're going to need to resolve your
build failure.  SQLite version, compiler, compiler options, compiler
output, etc. would all be useful towards this end.

-Shane


On Fri, Sep 25, 2009 at 12:17 AM, ydlu <yudian...@gmail.com> wrote:
> I create the "Windows CE 6.0" OS image, so the Console command windows was
> included. I try to build "SQLite3.exe" but failed! So how to test SQLite3 on
> WIndows CE environment on every build?
>
> Thanks
>
> On Wed, Sep 23, 2009 at 8:03 AM, Shane Harrelson <sh...@sqlite.org> wrote:
>>
>> On Wed, Sep 23, 2009 at 12:22 AM, Roger Binns <rog...@rogerbinns.com>
>> wrote:
>> > -BEGIN PGP SIGNED MESSAGE-
>> > Hash: SHA1
>> >
>> > ydlu wrote:
>> >> I am Windows CE software developer, so I am really, really want to
>> >> learn how
>> >> you built and test "sqlite3.exe" in Windows CE platform. so I can run a
>> >> console command line in Windows CE enivroment.
>> >
>> > http://www.sqlite.org/testing.html
>> >
>> > Most likely TH3 is used.
>> >
>> > Roger
>>
>>
>> As Roger guessed, we do indeed use TH3
>> (http://www.sqlite.org/th3.html).   Most Windows CE platforms don't
>> include a "console", so the standard TCL based test fixture would be
>> tricky to port (PocketConsole would be would a tool you could use, but
>> it looks to be no longer available).  Additionally, providing access
>> to the 500+ test files on the test device would be cumbersome, as well
>> as having sufficient resources (memory, etc.) on the device to execute
>> all the tests.
>>
>> HTH.
>> -Shane
>> ___
>> 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] Sqlite testing

[Shane Harrelson]

I'd like to help, but you've not provided any relevant information.
Obviously, in order to test, you're going to need to resolve your
build failure.  SQLite version, compiler, compiler options, compiler
output, etc. would all be useful towards this end.

-Shane


On Fri, Sep 25, 2009 at 12:17 AM, ydlu <yudian...@gmail.com> wrote:
> I create the "Windows CE 6.0" OS image, so the Console command windows was
> included. I try to build "SQLite3.exe" but failed! So how to test SQLite3 on
> WIndows CE environment on every build?
>
> Thanks
>
> On Wed, Sep 23, 2009 at 8:03 AM, Shane Harrelson <sh...@sqlite.org> wrote:
>>
>> On Wed, Sep 23, 2009 at 12:22 AM, Roger Binns <rog...@rogerbinns.com>
>> wrote:
>> > -BEGIN PGP SIGNED MESSAGE-
>> > Hash: SHA1
>> >
>> > ydlu wrote:
>> >> I am Windows CE software developer, so I am really, really want to
>> >> learn how
>> >> you built and test "sqlite3.exe" in Windows CE platform. so I can run a
>> >> console command line in Windows CE enivroment.
>> >
>> > http://www.sqlite.org/testing.html
>> >
>> > Most likely TH3 is used.
>> >
>> > Roger
>>
>>
>> As Roger guessed, we do indeed use TH3
>> (http://www.sqlite.org/th3.html).   Most Windows CE platforms don't
>> include a "console", so the standard TCL based test fixture would be
>> tricky to port (PocketConsole would be would a tool you could use, but
>> it looks to be no longer available).  Additionally, providing access
>> to the 500+ test files on the test device would be cumbersome, as well
>> as having sufficient resources (memory, etc.) on the device to execute
>> all the tests.
>>
>> HTH.
>> -Shane
>> ___
>> 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] Sqlite testing

[Shane Harrelson]

On Wed, Sep 23, 2009 at 12:22 AM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> ydlu wrote:
>> I am Windows CE software developer, so I am really, really want to learn how
>> you built and test "sqlite3.exe" in Windows CE platform. so I can run a
>> console command line in Windows CE enivroment.
>
> http://www.sqlite.org/testing.html
>
> Most likely TH3 is used.
>
> Roger


As Roger guessed, we do indeed use TH3
(http://www.sqlite.org/th3.html).   Most Windows CE platforms don't
include a "console", so the standard TCL based test fixture would be
tricky to port (PocketConsole would be would a tool you could use, but
it looks to be no longer available).  Additionally, providing access
to the 500+ test files on the test device would be cumbersome, as well
as having sufficient resources (memory, etc.) on the device to execute
all the tests.

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

Re: [sqlite] sqlite3_exec fails on arm

[Shane Harrelson]

I wasn't able to duplicate this with my ARM system.   Can you provide
more details?  or was the issue DRH pointed out above your problem?

On Wed, Sep 9, 2009 at 7:02 AM, gprand  wrote:
>
> Hi,
>
> I can confirm this behavoir. Now, after 2 days debugging and unsuccessful
> searching around I have found your posting.  It drives me crazy, but I can
> not locate the problem. Exactly the same code works, compiled for X86, and
> fails, compiled for ARM. All machine depending configuration is omitted.
>
> My poor debugging results are:
> * 100 Bytes be read from the file.
> * SQLite attempts to parse the sql text
> * SQLite creates sqlite_master table
> * Now it attempts to parse the sql statement again
> * Now SQLite crashes in ARM mode.
>
> In X86 mode 1024 bytes be read and all works fine.
> Why?
>
> Gottfried
>
> Sorry for poor english.
>
>>Hi,
>>I am using sqlite3.6.17 on arm_v7 . sqlite3_Exec fails giving the following
>>error sqlite3_exec  malformed database schema error : unrecognized token
> "'".
>>I am able to read the database manually using select commands. Executing
> the
>>same sqlite3_exec statements on x86 machine on the same db file does not
>>give any error . Any help on this issue will be welcome,
> --
> View this message in context: 
> http://www.nabble.com/sqlite3_exec-fails-on-arm-tp25293839p25362643.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Windows Mobile 6 Edition

[Shane Harrelson]

What compiler and compile time options are you using?


On Fri, Sep 4, 2009 at 2:00 PM, Dominique Jann wrote:
> Good night.
>
> I have a question about SQLite, very interesting project, how
> compiling SQLite sources for Windows Mobile 6.
> I couldn't compile project for this platform because have a many
> errors in compiling process.
> If you can hel me with this, i will be very thankful.
> ___
> 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] AIX test failures for 3.6.17

[Shane Harrelson]

On Tue, Aug 18, 2009 at 3:28 PM, Ken wrote:
> Running make test resulted in the following failures on aix 5.3
> Note that the CFLAGS=-DSQLITE_DISABLE_DIRSYNC was set for the make.
>
> 14 errors out of 40926 tests
> Failures on these tests: backup2-10 io-4.1 io-4.2.3 nan-1.1.2 nan-1.1.3 
> nan-1.1.4 nan-1.1.5 nan-1.1.5 nan-4.7 nan-4.8 nan-4.14 nan-4.15 nan-4.16 
> nan-4.17
>
> Any suggestions or reason why the io test would fail?
> io-3.3... Ok
> io-4.1...
> Expected: [3]
>     Got: [2]
> io-4.2.1... Ok
> io-4.2.2... Ok
> io-4.2.3...
> Expected: [3]
>     Got: [2]
> io-4.3.1... Ok
> io-4.3.2... Ok
>
> Thanks,
> Ken
>

Most Unix's support fsync()'ing directories, and the expected sync
numbers in io.test assume that if you're on a "Unix" platform, you're
going to be calling fsync() on the directory.  On AIX, which typically
doesn't support fsync() on directories (hence, your compiler option
-DSQLITE_DISABLE_DIRSYNC), the sync counts are different then what the
tests were expecting.

I've checked in an update to io.test which I think should correct the
test for you.

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

Re: [sqlite] Does PRAGMA synchronous=OFF ensure that no synching is done for the entire session?

[Shane Harrelson]

On Mon, Aug 17, 2009 at 11:53 AM, D. Richard Hipp  wrote:

>
> On Aug 17, 2009, at 11:41 AM, Matt Sergeant wrote:
> >
> > Kernels will fflush when a file handle is closed
>
> Not according to Ted Ts'o (creator of the Ext2/3/4 filesystems).  See,
> for example, the extensive discussions of this at
>
>
> http://thunk.org/tytso/blog/2009/03/12/delayed-allocation-and-the-zero-length-file-problem/
> http://thunk.org/tytso/blog/2009/03/15/dont-fear-the-fsync/
>
> Ted says that it is widely believed among programmers that close()
> will sync a file, but in fact nothing in POSIX requires this and in
> fact Linux does not do it.  Some hacks were added to ext4 in the
> 2.6.30 kernel release to mitigate the damage following a power loss
> when programs fail to fsync() prior to close().  But everybody agrees
> those changes are an ugly hack.
>
> In POSIX, the bottom line is this:  The *only* way to force data to
> oxide is to call sync() or fsync().  Some kernels and/or some
> filesystems might sync at other times, but it is not something that
> you can rely on.
>
> D. Richard Hipp
> d...@hwaci.com
>


To the original question though, with PRAGMA synchronous=OFF, SQLite will
NOT do explicit fsync()'s.  A exception to this occurs with attached DB's
and a transaction; when the transaction is committed and the master journal
is deleted, SQLite fsyncs the directory that contained the journal to ensure
the delete is "flushed".   The only way around this fsync() is to compile
with SQLITE_DISABLE_DIRSYNC.  This might be changed in the future.

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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

On Mon, Aug 17, 2009 at 3:15 AM, Dan Kennedy  wrote:

> >> The INDEXED BY feature was introduced to address concerns that SQLite
> >> might
> >> suddenly start using a different plan for a query in the field than
> >> it
> >> did
> >> in the office during testing. Either because somebody ran ANALYZE, or
> >> because
> >> the SQLite version was upgraded. In this situation, some users
> >> consider it
> >> better to throw an exception than to run the query with a different,
> >> possibly
> >> slower, plan.
> >
> > Confusion reigns supreme. Your second last paragraph says (about your
> > last scenario) that it uses index i1 instead of the apparently better
> > index i2 -- no exception throwing. Your last paragraph indicates
> > that in
> > this case an exception would be thrown.
>
> I guess I got that wrong then. Said users considered it better to throw
> an error if the index that the author of the SQL query expected it to
> use had been removed or radically altered.
>
> Dan.
>


Sorry for muddying the waters.  I was just trying to make clear that INDEXED
BY isn't intended to be used as a tuning mechanism for index selection.  My
statement should have been clearer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

INDEXED BY doesn't allow you to specify which index to use.  It just causes
the query to fail if SQLite thinks it should use an index different then the
one specified by the INDEXED BY clause.

On Sun, Aug 16, 2009 at 7:59 PM, His Nerdship  wrote:

>
> Hi Pavel,
>
> > Does INDEXED BY clause work for you?
> > http://www.sqlite.org/lang_select.html
>
> The page suggests that INDEXED BY can only be used in single-table queries.
> The report queries also join other smaller tables, some of them from an
> attached database, so I don't think this will work.
> However, you have given me an idea - maybe I could break it down to two
> queries. Use the INDEX BY on the big table to get a subset table, and do
> another SELECT on the subset. Thanks.
> Sholto (His Nerdship)
>
>
>
> > Is there any way the code can 'suggest' SQLite use a certain index?
>
>
> Pavel
>
> --
> View this message in context:
> http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24999340.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

No.  It only collects/updates stats when you explicitly call the ANALYZE.

On Sat, Aug 15, 2009 at 2:48 AM, Jim Showalter  wrote:

> It doesn't collect those statistics automatically, as part of query
> plan optimization?
>
> - Original Message -
> From: "Dan Kennedy" 
> To: "General Discussion of SQLite Database" 
> Sent: Friday, August 14, 2009 11:37 PM
> Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which
> to use
>
>
> >
> > On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:
> >
> >> How will that help him fix this problem, if the problem is that
> >> SQLite's query optimizer is selecting a suboptimal index to use,
> >> and
> >> there is no way to specify which index to use?
> >
> > The statistics collected by the ANALYZE command will be used by
> > SQLite to (hopefully) select the optimal index.
> >
> > ___
> > 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] DELETE ststement not working as expected

[Shane Harrelson]

I believe sqlite3_bind_int64() takes a signed int for the value to bind.
You state you are passing it a large unsigned int.   If the unsigned value
is large enough, it will appear to be a negative signed int.

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

Re: [sqlite] Topics 1 - Re: sqllogictest tool - please help :-)

[Shane Harrelson]

What have you been able to do so far?
Have you been able to build sqllogictest (SLT)?
Were you able to retrieve some of the test files for SLT?
 Have you gotten MySQL installed?
Are you able to use MySQL via an ODBC interface?  (SLT uses an ODBC
interface to non-SQLite databases.)



On Wed, Jun 10, 2009 at 3:27 AM, tsachi ofir  wrote:

> >
> > Hi,
>
>   we are working with SQLite in our company.
>   we need a tool that check if SQL returns the correct answer and to
> compare with MySQL.
>   I got a new job and its my first  assignment ( to start working on
> sqlogictest) , and I don't understand much about sqllogictest.
>   Please help.
>
>   Tsachi
>
>
> >
> > --
> >
> > Message: 1
> > Date: Tue, 9 Jun 2009 16:12:30 -0400
> > From: "D. Richard Hipp" 
> > Subject: Re: [sqlite] sqllogictest tool - please help :-)
> > To: General Discussion of SQLite Database 
> > Message-ID: <511286c3-5b52-4f01-8f74-3448d23d8...@hwaci.com>
> > Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
> >
> >
> > On Jun 9, 2009, at 10:38 AM, tsachi ofir wrote:
> >
> > > Hi ,
> > > I wont to use the "sqllogictest" program.
> >
> > Let's start with the basics.  Why do you think you want to use this
> > program? What do you think it will accomplish for you?
> >
> > >
> > > I'm using SQLite.
> > >
> > > I have read the "About Sqllogictest" and didn't fully understand how
> > > to work
> > > with it.
> > > Can you please help on how I can use it? I will be very grateful for
> > > your
> > > help.
> > >
> > >   - Where are the files of the program? is it those three files?
> > >
> > >
> > >   1. sqllogictest.mk
> > >   2. sqllogictest.h
> > >   3. sqllogictest.c
> > >
> > >
> > >   - How can I install it?
> > >   - Just to migrate the files to which folder?
> > >
> > > Thanks for your help.
> > >
> > > Tsachi Ofir
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > D. Richard Hipp
> > d...@hwaci.com
> >
> >
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Version 2.1

[Shane Harrelson]

The 2.8 version should be able to read the 2.1... although it will try
to convert the format first, so make a backup copy of the original.

On Mon, Jun 8, 2009 at 12:35 PM, Marcus Haßmann<mar...@hassmann.eu> wrote:
> The empty database in an example.
> I have to do an investigation for a non empty database...
>
> Marcus Haßmann
>
> Shane Harrelson schrieb:
>> http://www.sqlite.org/sqlite-2.8.17.tar.gz  I think is the oldest
>> version still available from the SQLite website.
>>
>> BTW, I'm not certain what kind of forensic evidence you can get from
>> your db if it's empty as you say.
>>
>> HTH.
>> -Shane
>>
>> On Sun, Jun 7, 2009 at 5:12 PM, Marcus Haßmann<mar...@hassmann.eu> wrote:
>>
>>> I tested BearShare Lite Version 5.2.5.1
>>> The database is located in C:\Program Files\BearShare\db and it is named
>>> "library.db".
>>> The header of the empty database is "** This file contains an SQLite 2.1
>>> database **"
>>>
>>> Marcus Haßmann
>>>
>>> Simon Slavin schrieb:
>>>
>>>> On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote:
>>>>
>>>>
>>>>
>>>>> I tested all versions of sqlite browser already - without success.
>>>>>
>>>>>
>>>> Are you certain that this really is a sqlite database, and not just a
>>>> file with the right extension ?  Does it start with the right header ?
>>>>
>>>> Simon.
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-user
>>>>
>>>>
>>> ___
>>> 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
>>
>
>
> --
> Mit freundlichen Grüßen
> Marcus Haßmann
> --
> Hassmann-Software
> In der Pottaschdell 31
> 66333 Völklingen
> ICQ:   192 982 963
> Tel.:  06898 / 49 32 30
> Mobil: 0176 / 400 53 54 5
> Email: i...@hassmann-software.de
> Web:   http://www.hassmann-software.de
> XING:  http://www.xing.com/profile/Marcus_Hassmann
>
> ___
> 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] SQLite Version 2.1

[Shane Harrelson]

http://www.sqlite.org/sqlite-2.8.17.tar.gz  I think is the oldest
version still available from the SQLite website.

BTW, I'm not certain what kind of forensic evidence you can get from
your db if it's empty as you say.

HTH.
-Shane

On Sun, Jun 7, 2009 at 5:12 PM, Marcus Haßmann wrote:
> I tested BearShare Lite Version 5.2.5.1
> The database is located in C:\Program Files\BearShare\db and it is named
> "library.db".
> The header of the empty database is "** This file contains an SQLite 2.1
> database **"
>
> Marcus Haßmann
>
> Simon Slavin schrieb:
>> On 7 Jun 2009, at 8:22pm, Marcus Haßmann wrote:
>>
>>
>>> I tested all versions of sqlite browser already - without success.
>>>
>>
>> Are you certain that this really is a sqlite database, and not just a
>> file with the right extension ?  Does it start with the right header ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-user
>>
> ___
> 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] most efficient way to get 1st row

[Shane Harrelson]

We're not talking about transaction control - we're talking about limiting
the size of the result set.   And LIMIT/OFFSET clauses are not supported by
the SQL-92 standard.   LIMIT is listed as a reserved word, but that's it.

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt




On Wed, May 20, 2009 at 2:47 PM, John Stanton <jo...@viacognis.com> wrote:

> What  makes you think that transaction control is not part of SQL-92?
>
> If a database engine does not support transaction control it is not a
> full implementation.   Transaction control is at the very heart of
> effective data management.
>
> Shane Harrelson wrote:
> > Additionally, it's important to note that the LIMIT/OFFSET clause is not
> > standard SQL, and although it is supported by SQLite, and many other SQL
> > engines, there are some that do NOT support it, most notably Microsoft
> SQL
> > Server.
> >
> > HTH.
> > -Shane
> >
> >
> > On Tue, May 19, 2009 at 2:23 PM, Sam Carleton <scarle...@gmail.com>
> wrote:
> >
> >
> >> Kees Nuyt wrote:
> >>
> >>
> >>> Imagine a SELECT with an ORDER BY which makes SQLite sort
> >>> the resultset before it can return the first row in the
> >>> resultset. Need I say more?
> >>>
> >>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> >>>
> >>>
> >>>
> >> Thank you for the link, it is a VERY useful read, VERY useful!
> >>
> >> Sam
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >>
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> ___
> sqlite-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] most efficient way to get 1st row

[Shane Harrelson]

Additionally, it's important to note that the LIMIT/OFFSET clause is not
standard SQL, and although it is supported by SQLite, and many other SQL
engines, there are some that do NOT support it, most notably Microsoft SQL
Server.

HTH.
-Shane


On Tue, May 19, 2009 at 2:23 PM, Sam Carleton  wrote:

> Kees Nuyt wrote:
>
>> Imagine a SELECT with an ORDER BY which makes SQLite sort
>> the resultset before it can return the first row in the
>> resultset. Need I say more?
>>
>> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
>>
>>
> Thank you for the link, it is a VERY useful read, VERY useful!
>
> Sam
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite crashing on iPhone (or so says Apple)

[Shane Harrelson]

Version 3.6.13 fixed some potential alignment issues that could occur on
SPARC (and potentially other) architectures.  I don't know how you or Apple
are testing your app, but if you (or they) are using a device emulator for
the testing, the emulator might not be testing alignment conditions the
same.

HTH.
-Shane

On Tue, Apr 21, 2009 at 11:27 AM, D. Richard Hipp  wrote:

>
> On Apr 21, 2009, at 11:10 AM, Mark Spiegel wrote:
>
> > I'm a bit confused by the following:
> >
> > "The assign 100K or so to each database connection's lookaside memory
> > allocator using sqlite3_db_config(SQLITE_DBCONFIG_LOOKASIDE, ...)
> > immediately after it is opened."
> >
> > If memory is at a premium, why would you reserve a large amount of it
> > for SQLite's "look aside allocator"?  (It's really a zone allocator.)
> > This SQLite mechanism ostensibly attempts to trade memory for
> > speed.  If
> > memory is at a premium, in this case a fixed upper bound, that trade
> > off
> > doesn't seem to make sense.  I would think in a case where memory is
> > tight, zero bytes should be reserved.
> >
>
> This is a reasonable observation.
>
> On the other hand, the lookaside memory allocator (which is just a
> zone allocator, as you observe) makes a big performance difference.
> And if you only have a single database connection, it doesn't really
> matter if the memory goes into lookaside or is in the global heap.  If
> you have multiple database connections, you might get increased memory
> efficiency by sharing between those two connections - which cannot
> happen with lookaside.
>
> The page cache is going to be the biggest user of memory.  The page
> cache memory will probably be measured in megabytes.  Memory used by
> lookaside is measured in kilobytes.  A few dozen KB of additional
> memory assigned to lookaside won't make that much difference in your
> overall memory usage, but it will make a difference in performance.
> So it seems to me to be worth the tradeoff, even if memory is tight.
>
> The reason I suggested using sqltie3_db_config() to assign a static
> buffer for lookaside is so that the lookaside subsystem will not go to
> the heap to get its (default) 50K allocation.  The MEMSYS5 memory
> allocator is a first-fit power-of-two memory allocator specifically
> designed to avoid memory fragmentation and hence allow applications to
> be designed that are guaranteed to never fail a memory allocation.
> But doing large heap allocations (more than 2K or 4K) tends to defeat
> the anti-fragmentation properties of MEMSYS5.   Hence, we desire to
> avoid the 50K heap allocation for the initial lookaside buffer.  One
> could, as you observe, achieve the same result by turning lookaside
> off all together, but then you take a performance hit.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select the first 2 rows

[Shane Harrelson]

Remember, the order of rows returned from a SELECT, even one with a LIMIT
clause, is undefined, so the "first 2 rows" may not be consistently the
same.   If you are dependent upon the rows being returned in a particular
order (say by "rowid"), you should include an ORDER BY clause on your SELECT
to ensure the rows are returned in the order you require.

-Shane


On Wed, Apr 1, 2009 at 3:02 PM, Eric Minbiole wrote:

> > Hi all,
> > I have a big table and I want only select the first 2 rows.
> > I have tried this :
> > select top 2 from table;
> > but it doesn't work! Any help please.
> > JP
>
> Use a LIMIT clause instead of TOP:
>
> SELECT * FROM table LIMIT 2;
>
> http://www.sqlite.org/lang_select.html
> ___
> 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] SQLite Transaction Rate and speed...

[Shane Harrelson]

Already there:  http://www.sqlite.org/faq.html#q19

On Sun, Mar 8, 2009 at 8:15 AM, Alexey Pechnikov  wrote:
> Hello!
>
> On Saturday 07 March 2009 01:59:13 Roger Binns wrote:
>> A transaction requires two syncs (ie requesting the drive write the data
>> to the metal and not return until it does).  On average each sync will
>> take a disk rotation so a 7200rpm drive maxes out at 60 transactions a
>> second.  If you require each data update to be physically on the disk
>> then this is your limit no matter what library or storage mechanism you
>> use.
>
> That's great description! Can you add this to documentation? I did know the
> limit experimentally but I didn't can to calculate this.
>
>> You can also look at alternate storage mechanisms.  For example rotating
>> drives can be paired with a battery backed controller, or solid state
>> could be used if the wear leveling and write-erase performance meet your
>> needs.
>
> Do you have experimental results of SQLite performance on SSD?
>
> Best regards.
> ___
> 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] Visual Studio 2005 Linker Problems - SQLite v3.6.3

[Shane Harrelson]

The workaround I gave you was overly simplistic.

You should take a look at the sample makefiles provided
for examples of what compilation options to use.

In particular, you may need to define SQLITE_CORE
for your files.  This flag is used to control the definition
of sqlite3_api in the sqlite2ext.h header file.   Typically,
you do not need to worry about this define, but
depending on your build environment and options,
you may need to adjust its use.



On Tue, Feb 17, 2009 at 9:27 AM, Ian Thomas <tho...@ccdc.cam.ac.uk> wrote:
> Hello again,
>
> Although the previous suggestion does fix the linker problem for VS2005
> (Release and Debug), and for the VS2003 Debug build, I still cannot link
> the Release build in VS2003. With the 'rtree.c' source file excluded I
> now get this similar error message:
>
> 
> Linking...
> fts3_tokenizer.obj : error LNK2005: _sqlite3_api already defined in fts3.obj
> fts3_tokenizer.obj : warning LNK4006: _sqlite3_api already defined in
> fts3.obj; second definition ignored
> 
>
> I am using FTS3 so I cannot disable this optional module. Any ideas please?
>
> Thanks again,
>
> Ian
>
>
> Ian Thomas wrote:
>> Hi Shane,
>>
>> Thanks very much for the suggestion. I excluded the 'rtree.c' source
>> file from my build configuration and it now links fine (release and debug).
>>
>> I guess there's still an underlying issue but as I don't need the rtree
>> module it doesn't affect me!
>>
>> Thanks again,
>>
>> Ian
>>
>>
>> Shane Harrelson wrote:
>>
>>> rtree support is an extension to SQLite
>>> and is not needed to use the core functionality.
>>> Try leaving the rtree source files out of
>>> your builds to see if this corrects your
>>> problem.  You might also consider using
>>> the SQLITE_OMIT_LOAD_EXTENSION
>>> option as well.
>>>
>>> http://www.sqlite.org/compile.html
>>>
>>>
>>> On Mon, Feb 16, 2009 at 9:29 AM, Ian Thomas <tho...@ccdc.cam.ac.uk> wrote:
>>>
>>>
>>>> Hello,
>>>>
>>>> I'm trying to compile my own debug build of SQLite v3.6.3 in Visual
>>>> Studio 2005 so that I can step through a problem I'm having with my
>>>> application. I have followed the instructions found here:
>>>>
>>>> http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet
>>>>
>>>> Using the standard v3.6.3 source tree (not the amalgamation version)
>>>> everything compiles fine but fails to link with this error:
>>>>
>>>> 1>rtree.obj : error LNK2005: _sqlite3_api already defined in
>>>> fts3_tokenizer.obj
>>>> 1>   Creating library
>>>> D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.lib
>>>> and object
>>>> D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.exp
>>>> 1>D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.dll
>>>> : fatal error LNK1169: one or more multiply defined symbols found
>>>>
>>>> I have only two SQLite preprocessor defines set in my project:
>>>>
>>>> SQLITE_ENABLE_COLUMN_METADATA
>>>> SQLITE_ENABLE_FTS3
>>>>
>>>> I get this error both with and without the SQLITE_ENABLE_FTS3 define.
>>>>
>>>> I get the same linker error when trying to build SQLite v3.6.10 and when
>>>> trying to use Visual Studio 2003 instead of 2005.
>>>>
>>>> I'm sure there must be an easy fix for this problem - any advice would
>>>> be really appreciated. I imagine I could use the /FORCE:MULTIPLE linker
>>>> option to get past this but I'm reluctant to do so without checking first.
>>>>
>>>> Thanks in advance,
>>>>
>>>> Ian Thomas
>>>>
>>>>
>>>> LEGAL NOTICE
>>>> Unless expressly stated otherwise, information contained in this
>>>> message is confidential. If this message is not intended for you,
>>>> please inform postmas...@ccdc.cam.ac.uk and delete the message.
>>>> The Cambridge Crystallographic Data Centre is a company Limited
>>>> by Guarantee and a Registered Charity.
>>>> Registered in England No. 2155347 Registered Charity No. 800579
>>>> Registered office 12 Union Road, Cambridge CB2 1EZ.
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.or

Re: [sqlite] Visual Studio 2005 Linker Problems - SQLite v3.6.3

[Shane Harrelson]

rtree support is an extension to SQLite
and is not needed to use the core functionality.
Try leaving the rtree source files out of
your builds to see if this corrects your
problem.  You might also consider using
the SQLITE_OMIT_LOAD_EXTENSION
option as well.

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


On Mon, Feb 16, 2009 at 9:29 AM, Ian Thomas  wrote:
> Hello,
>
> I'm trying to compile my own debug build of SQLite v3.6.3 in Visual
> Studio 2005 so that I can step through a problem I'm having with my
> application. I have followed the instructions found here:
>
> http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet
>
> Using the standard v3.6.3 source tree (not the amalgamation version)
> everything compiles fine but fails to link with this error:
>
> 1>rtree.obj : error LNK2005: _sqlite3_api already defined in
> fts3_tokenizer.obj
> 1>   Creating library
> D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.lib
> and object
> D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.exp
> 1>D:\x_mirror\buildman\tools\sqlite-3.6.3\SQLiteVS2005\Debug\SQLiteVS2005.dll
> : fatal error LNK1169: one or more multiply defined symbols found
>
> I have only two SQLite preprocessor defines set in my project:
>
> SQLITE_ENABLE_COLUMN_METADATA
> SQLITE_ENABLE_FTS3
>
> I get this error both with and without the SQLITE_ENABLE_FTS3 define.
>
> I get the same linker error when trying to build SQLite v3.6.10 and when
> trying to use Visual Studio 2003 instead of 2005.
>
> I'm sure there must be an easy fix for this problem - any advice would
> be really appreciated. I imagine I could use the /FORCE:MULTIPLE linker
> option to get past this but I'm reluctant to do so without checking first.
>
> Thanks in advance,
>
> Ian Thomas
>
>
> LEGAL NOTICE
> Unless expressly stated otherwise, information contained in this
> message is confidential. If this message is not intended for you,
> please inform postmas...@ccdc.cam.ac.uk and delete the message.
> The Cambridge Crystallographic Data Centre is a company Limited
> by Guarantee and a Registered Charity.
> Registered in England No. 2155347 Registered Charity No. 800579
> Registered office 12 Union Road, Cambridge CB2 1EZ.
> ___
> 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] INVALID COMMAND NAME sqlite3_soft_heap_limit

[Shane Harrelson]

If you use the supplied makefile, "make fulltest" should build the
testfixture and run the all.test set.  The testfixture is similar to the
sqlite3 CLI except that it includes a TCL interpreter as well as an
extensive set of test harnesses for virtually every part of the SQLite core.

On Mon, Dec 22, 2008 at 9:05 AM,  wrote:

> Hello Dan,
>
> Thank U for ur reply..
>
> Could you please let me know more about testfixture.
>
> Also, please forward releated links
>
> Thanks
> Chandru
>
>
>
> On Mon, Dec 22, 2008 at 3:06 PM, Dan  wrote:
>
> >
> > On Dec 22, 2008, at 3:55 PM, mkrajachan...@gmail.com wrote:
> >
> > > Hello all,
> > >
> > > While i am trying to run the all.test file through the tcl shell
> > >
> > > I am getting the following error
> > >
> > >
> > > # tclsh all.test
> > > invalid command name "sqlite3_soft_heap_limit"
> > >while executing
> > > "sqlite3_soft_heap_limit $soft_limit"
> > >(file "./tester.tcl" line 50)
> > >invoked from within
> > > "source $testdir/tester.tcl"
> > >(file "all.test" line 16)
> > > #
> > >
> > > how can i getrid of this problem
> > >
> > > Thanks in advance
> >
> > You need to build and use 'testfixture', not the regular sqlite3
> > tcl shell, to run the test suite. Are you doing so?
> >
> > ___
> > 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] self test for sqlite-amalgamation-3.6.5

[Shane Harrelson]

Can you provide more details?  What platform (Windows, Linux, etc.)?  What
version of TCL?  What compilation options did you use for the testfixture?

On Mon, Nov 24, 2008 at 1:30 AM, Avinash Mittal <[EMAIL PROTECTED]>wrote:

> Hi,
>
> I tried to execute the test for sqlite-amalgamation-3.6.5 repository but
> there are some test cases which getting hanged like
> lock-4.3...
> Expected: [1 {database is locked} {0 1 2 3 4 5}]
> Got: [1 {database is locked} {}]
> lock-5.1... Ok
> lock-5.2..---> this one is
> hanging.
>  and
> pragma-3.4    > hanging
>
>
> i am not getting understood this behavior as i did execute test for
> sqlite-3.6.4 repository and all ended well
> ___
> 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] sqlite crash with "WHERE x in ()" query

[Shane Harrelson]

Thanks for the report.  The current test suite has many cases testing
queries of the form you mention.  I added one specifically for the query you
suggested to in4.test.   Everything passes for the current version.   If you
could test with the latest version of SQLite, it might help show where the
problem lies (in SQSLite, the Python bindings, or possibly in your
application code).   Most of the historical versions of SQLite are also
available from the website.  If this was a problem in an older version, but
not with the latest, it might be beneficial for you to try incremental
versions to find where it was fixed.

-Shane



On Fri, Nov 7, 2008 at 6:48 PM, Matt Craighead <
[EMAIL PROTECTED]> wrote:

> Hi all,
>
> I've hit what I believe may be a crash bug in SQLite.  I'm using the
> version
> bundled with Python 2.5.2 on Windows, which is SQLite 3.3.4, it would
> appear.  I know, this isn't the latest version, but this happens to be the
> version bundled with the version of Python my product relies on.  (I
> haven't
> switched to Python 2.6 yet; for one, there are no publicly available
> mod_python Win32 binaries for Apache.)
>
> Originally my query looked something like:
>
> SELECT x FROM table WHERE id IN (SELECT id FROM other_table WHERE y=1)
>
> I assumed this would do the subquery only once, but I found (both from slow
> performance and by using EXPLAIN QUERY PLAN) that in fact it was doing a
> full table scan of "table", and for each element, looking at its id to see
> whether that id could be found in other_table.
>
> On the other hand, if I did two separate queries, and wrote the second
> query
> as follows:
>
> SELECT x FROM table WHERE id IN (1,3,5,7)
>
> ...it would use table's index, which was a lot faster.  Unfortunately if
> the
> list of id's was empty, I'd end up constructing a query with a "WHERE x IN
> ()" clause -- and this would cause a crash deep in the guts of sqlite3.dll.
> Without symbols it wasn't clear where the crash was coming from.
>
> From the grammar diagram at http://www.sqlite.org/lang_expr.html, it would
> appear that using () for an empty list is not legal SQL syntax as
> recognized
> by SQLite.  Is that correct?  Even if so, it still shouldn't crash, right?
> I would think that it should consistently either produce an error, or
> consistently treat () as an empty set.  I don't get an error, and while
> sometimes it appears to be treated as an empty set, sometimes I get a
> crash.
>
> Oddly this crash doesn't happen all the time.  It only seems to happen for
> the following specific query:
>
> SELECT x FROM table
> WHERE id IN ()
> AND x IN (SELECT x FROM table2 WHERE id=)
> Any ideas?  Has this been fixed in a newer version already?  I've already
> worked around this and I no longer issue SQLite any "IN ()" queries, but I
> figured I'd still report the problem.
>
> --
> Matt Craighead
> Founder/CEO, Conifer Systems LLC
> http://www.conifersystems.com
> 512-772-1834
> ___
> 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