Re: [sqlite] 64-bit SQLite3.exe

[Rousselot, Richard A]

I guess it is a matter of support.  Can the people using unpatched, unsupported 
32-bit windows instances just live with SQLite 3.13 (or whatever the cutover 
version)?  Are these 32-bit windows users really actively updating SQLite?

Can the command line tool interact with a driver?  How does a 32-bit windows 
user get SQLite3.exe to run on a legacy 16-bit (windows 3.1?) machine?

Sorry to press on this so much but I find all these arguments hollow.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Empson
Sent: Wednesday, August 10, 2016 12:09 AM
To: SQLite mailing list
Subject: Re: [sqlite] 64-bit SQLite3.exe


> On 10/08/2016, at 3:30 PM, Rousselot, Richard A 
>  wrote:
>
> As I said, I am not a software engineer.  I could spend a few hours figuring 
> this out and be fine but it will be painful for me.
>
> I see no downsides in a 64-bit CLI.  The last 32-bit Intel CPU was the PIII 
> in 2004, no supported Windows OS requires 32-bit CPUs, the file size may be 
> marginally bigger but who cares on a PC.  The 64-bit version will, I assume, 
> happily work on DBs created in the 32-bit version.  And for those that need 
> 32-bit for their applications and drivers still have access to the 32-bit 
> DLL.  What am I missing?  Are windows command line tools 32-bit only?

A 32-bit installation of Windows cannot run 64-bit executables (ignoring VM 
solutions).

Because of the large installed base of 32-bit Windows, the Windows command line 
tools for SQLite needs to be available as 32-bit versions. If 64-bit versions 
were provided, they would need to be in addition to the 32-bit versions.

There are an awful lot of 32-bit installations of Windows. This includes a lot 
of 32-bit installations of Windows on 64-bit processors, which exist for many 
reasons including defaults offered by the manufacturer, lack of 64-bit drivers, 
corporate policy decisions, reduced memory footprint in limited machines, or 
the user requiring 32-bit Windows in order to be able to run legacy 16-bit 
software (again, ignoring VM solutions).

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Rousselot, Richard A]

>On Aug 9, 2016, at 9:30 PM, Rousselot, Richard A 
> wrote:
>
>> I could spend a few hours figuring this out and be fine but it will be 
>> painful for me.
>
>Or you can spend many hours waiting for someone to build it for you.  How many 
>hours are you willing to wait to save yourself some pain?  (And since when did 
>learning something new cause pain?)
>
I like learning as much as the next guy but I prefer to spend my time on skills 
I can use in the future; compiling a 64-bit binary is not a useful skill.  I 
may wait for someone to compile and provide it to me but I am really wary of 
getting code from strangers these days.  I tend to trust the SQLite team.

>As to your problem with corporate IT, will they let you install Cygwin?  
>SQLite is well-supported in Cygwin, and there is a 64-bit version of Cygwin.  
>Due to the way Cygwin works, all packages available for 64-bit Cygwin are also 
>64-bit.
>
I would probably have an easier time getting a Mac (impossible) than get Cygwin 
installed at work. :)

>Cygwin SQLite should be nearly as fast as native SQLite.  There are some big 
>speed hits in Cygwin, but for the things SQLite does, I can’t see that you’re 
>going to run into any of the biggest ones.
>
>> The last 32-bit Intel CPU was the PIII in 2004
>
>That’s simply not true.  Many P4s were 32-bit, the Atom processors were 32-bit 
>only until 2008, and I believe the Core Solo processors were also 32-bit only.
>
>(That latter caused a lot of trouble for me when Apple went 64-bit only and 
>cut off a bunch of the still-useful Macs I had still in use.)
>
Ok, P4 in 2008 that is still 8 years ago.  (Your 32-bit Mac is not windows 
machine).  How long do I have to wait for everyone to upgrade?  So, if there is 
one person in the universe still using a 32-bit windows machine we all have to 
wait?

>> no supported Windows OS requires 32-bit CPUs
>
>But equally, Microsoft retrenched from their threat to make Windows 10 the 
>first 64-bit-only version of Windows.  Wonder why? :)
>
Microsoft keeps 32-bit compatibility for legacy applications.  I don't consider 
and actively developed piece of software from 2016 a legacy application, do you?

>> The 64-bit version will, I assume, happily work on DBs created in the 32-bit 
>> version.
>
>Yes.
>
>> What am I missing?
>
>Someone has to do it.  Time is not free.
>
I agree, time is not free.   If I compile a 64-bit SQLite3.exe that only helps 
me and wastes a lot of my time.  I bet Dr. Hipp et al could have that thing 
(build scripts at least) complied in a matter of minutes and his work would be 
available for anyone in the world to use.  Why not, on the other hand, save 
some time by not compiling the 32-bit version?

The 64-bit version will probably shave an hour off my many 8 hour processing 
jobs.  That will add up very quickly for me.

>> Are windows command line tools 32-bit only?
>
>The opposite, actually: the first 64-bit versions of the Visual C++ tool set 
>were command-line only, as I recall.  I believe that was back in the 
>pre-VC++2005 days.
>
>> Why add powerful features like CTE if you can't access their power?
>
>Because most of the SQLite binaries are shipped by third parties, not directly 
>from sqlite.org.  The biggest sources are OSes (virtually all mobile phones, 
>Mac OS X, Windows, etc.) and third-party applications (virtually all web 
>browsers, many Adobe and Apple products, etc.)  These third parties built 
>SQLite to meet their needs.
>
This doesn't make sense, what does a 3rd party binary based on a dll have to do 
with a command line tool?  Are you saying that no one needs the command line 
tool so its development should be abandoned?

>I’d bet the number of regularly run instances of binaries downloaded directly 
>from sqlite.org is under 0.01% of the total usage of SQLite.
>
>(That’s a considered guess, not a wild guess.  There are billions of SQLite 
>instances in the world, and I’m betting there are less than 100,000 users of 
>the SQLite.org binaries.  I wouldn’t be surprised if it’s under 0.001%.)
>
>Of that tiny percentage, only a small fraction will actually need a 64-bit, 
>and of that fraction of a fraction, only a small number will be unable to 
>acquire or build a 64-bit binary.
>
>Why spend a lot of effort on such a small user base?
>
A 64-bit SQLite3.exe would help the whole user based of command line users.  
Why spend time making a 32-bit version for the minority of people still running 
8 year old equipment?  It's not like they can't download an older 32-bit 
version.
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users 

Re: [sqlite] 64-bit SQLite3.exe

[David Empson]

> On 10/08/2016, at 3:30 PM, Rousselot, Richard A 
>  wrote:
> 
> As I said, I am not a software engineer.  I could spend a few hours figuring 
> this out and be fine but it will be painful for me.
> 
> I see no downsides in a 64-bit CLI.  The last 32-bit Intel CPU was the PIII 
> in 2004, no supported Windows OS requires 32-bit CPUs, the file size may be 
> marginally bigger but who cares on a PC.  The 64-bit version will, I assume, 
> happily work on DBs created in the 32-bit version.  And for those that need 
> 32-bit for their applications and drivers still have access to the 32-bit 
> DLL.  What am I missing?  Are windows command line tools 32-bit only?

A 32-bit installation of Windows cannot run 64-bit executables (ignoring VM 
solutions).

Because of the large installed base of 32-bit Windows, the Windows command line 
tools for SQLite needs to be available as 32-bit versions. If 64-bit versions 
were provided, they would need to be in addition to the 32-bit versions.

There are an awful lot of 32-bit installations of Windows. This includes a lot 
of 32-bit installations of Windows on 64-bit processors, which exist for many 
reasons including defaults offered by the manufacturer, lack of 64-bit drivers, 
corporate policy decisions, reduced memory footprint in limited machines, or 
the user requiring 32-bit Windows in order to be able to run legacy 16-bit 
software (again, ignoring VM solutions).

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

Re: [sqlite] 64-bit SQLite3.exe

[Warren Young]

On Aug 9, 2016, at 9:30 PM, Rousselot, Richard A 
 wrote:
> 
> I could spend a few hours figuring this out and be fine but it will be 
> painful for me.

Or you can spend many hours waiting for someone to build it for you.  How many 
hours are you willing to wait to save yourself some pain?  (And since when did 
learning something new cause pain?)

As to your problem with corporate IT, will they let you install Cygwin?  SQLite 
is well-supported in Cygwin, and there is a 64-bit version of Cygwin.  Due to 
the way Cygwin works, all packages available for 64-bit Cygwin are also 64-bit.

Cygwin SQLite should be nearly as fast as native SQLite.  There are some big 
speed hits in Cygwin, but for the things SQLite does, I can’t see that you’re 
going to run into any of the biggest ones.

> The last 32-bit Intel CPU was the PIII in 2004

That’s simply not true.  Many P4s were 32-bit, the Atom processors were 32-bit 
only until 2008, and I believe the Core Solo processors were also 32-bit only.

(That latter caused a lot of trouble for me when Apple went 64-bit only and cut 
off a bunch of the still-useful Macs I had still in use.)

> no supported Windows OS requires 32-bit CPUs

But equally, Microsoft retrenched from their threat to make Windows 10 the 
first 64-bit-only version of Windows.  Wonder why? :)

> The 64-bit version will, I assume, happily work on DBs created in the 32-bit 
> version.

Yes.

> What am I missing?

Someone has to do it.  Time is not free.

> Are windows command line tools 32-bit only?

The opposite, actually: the first 64-bit versions of the Visual C++ tool set 
were command-line only, as I recall.  I believe that was back in the 
pre-VC++2005 days.

> Why add powerful features like CTE if you can't access their power?

Because most of the SQLite binaries are shipped by third parties, not directly 
from sqlite.org.  The biggest sources are OSes (virtually all mobile phones, 
Mac OS X, Windows, etc.) and third-party applications (virtually all web 
browsers, many Adobe and Apple products, etc.)  These third parties built 
SQLite to meet their needs.

I’d bet the number of regularly run instances of binaries downloaded directly 
from sqlite.org is under 0.01% of the total usage of SQLite.

(That’s a considered guess, not a wild guess.  There are billions of SQLite 
instances in the world, and I’m betting there are less than 100,000 users of 
the SQLite.org binaries.  I wouldn’t be surprised if it’s under 0.001%.)

Of that tiny percentage, only a small fraction will actually need a 64-bit, and 
of that fraction of a fraction, only a small number will be unable to acquire 
or build a 64-bit binary.

Why spend a lot of effort on such a small user base?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite .Net Entity Framework support

[Lloyd]

Hi,
I wish to use sqlite with my C++/CLI (.Net) application using entity
framework (EF). I was referring to this link -
 https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

and from the heading I understood that it is an ADO.Net provider. I have
also seen nuget package  by "drh"

https://www.nuget.org/packages/System.Data.SQLite.EF6.

It says system.data.sqlite.ef6

Does sqlite has support for Entity Framework?

If yes, what is the correct link to follow? I couldn't install nuget
package in my C++/CLI application so I manually downloaded the setup
"Setups for 32-bit Windows (.NET Framework 4.6)".
sqlite-netFx46-setup-bundle-x86-2015-1.0.102.0.exe. But it doesn't seem to
support EF.

Thanks a lot,
  Lloyd
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Rousselot, Richard A]

As I said, I am not a software engineer.  I could spend a few hours figuring 
this out and be fine but it will be painful for me.

I see no downsides in a 64-bit CLI.  The last 32-bit Intel CPU was the PIII in 
2004, no supported Windows OS requires 32-bit CPUs, the file size may be 
marginally bigger but who cares on a PC.  The 64-bit version will, I assume, 
happily work on DBs created in the 32-bit version.  And for those that need 
32-bit for their applications and drivers still have access to the 32-bit DLL.  
What am I missing?  Are windows command line tools 32-bit only?

Why add powerful features like CTE if you can't access their power?  Isn't this 
just a matter of making a few changes on some automated scripts that generate 
each releases files and done?

(Sorry if this double posts, I attempted to use Nabble and the message bounced)

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Donald Shepherd
Sent: Tuesday, August 09, 2016 9:28 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] 64-bit SQLite3.exe

Why don't you build it yourself as a 64 bit executable?

On Wed, 10 Aug 2016 at 00:31 Rousselot, Richard A < 
richard.a.rousse...@centurylink.com> wrote:

> I would like to request a SQLite official 64-bit SQLite3.exe CLI (not
> DLL) be created.
>
> I have reviewed the prior discussions regarding 64-bit SQLite3 and the
> reasoning for which why creating a 64-bit version is denied are "it
> does not make a real difference", "you can just use ram disks", etc., etc.
>
> Here is my plea...  I am using a set of complicated CTEs to crawl
> through a network (tree) to aggregate and calculate formulas.  I don't
> have exceptionally large datasets but my CTEs result in a ton of memory usage.
> The process works well from disk, in Windows, but using a smaller test
> sample I get about a 30% to 40% increase in processing time if I set
> the PRAGMA to temp_store = 2.  If I use a normal dataset, not a small
> test, I hit an approximate 2G limit and get a "out of memory" message,
> which I understand is due to SQLite3.exe being 32-bit.  I have found
> some 3rd party 64-bit builds for SQLite3 (best found is 3.8.5) but
> they are out of date and don't allow all functionality that I am
> using.  So, I do have a use case that requires 64-bit and I would see a 
> significant increase in speed.
>
> As to RAM disks, I work in a corporate environment that locks down
> user rights which precludes me from distributing a tool that requires
> the creation of a tool that needs administrator rights.  I also, would
> like to avoid having to compile it myself; I am not a software engineer.
>
> Thanks for your consideration.
>
> Richard
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the
> sender by reply e-mail and destroy all copies of the communication and
> any attachments.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Paulo Roberto  wrote:
>
> I found your solution pretty elegant and I tried to implement it.
> But after solving a lot of building issues with the sqlite3ext header

It does not have to be implemented as a loadable extension.  Just copy
the lines https://www.sqlite.org/src/artifact/8440f8d0b4?ln=41-53 into
your application, then invoke
https://www.sqlite.org/src/artifact/8440f8d0b4?ln=65-66 on the
database connection right after you get it back from sqlite3_open().

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

Thank you for all the answers.

Clemens,
The counterid in my case is a text field and not an integer. That's why I
need to sanitize.

Clemens and Keith,
As each of my process has its own connection to the database, I tried your
solution using BEGIN IMMEDIATE and it worked successfully.
Thank you.


Richard,

I found your solution pretty elegant and I tried to implement it.
But after solving a lot of building issues with the sqlite3ext header I was
not able to figure out how to use the function sqlite3_remember_init even
reading the MACROS definition.

My problem is with the parameter :

const sqlite3_api_routines *pApi

No matter what value I pass I keep receiving the signal SIGSEV
i.e. Segmentation fault.

Do you have another reference to give to me?

Thanks for your help.

Regards.

Paulo Roberto.


On Tue, Aug 9, 2016 at 9:07 PM, Keith Medcalf  wrote:

>
> > "BEGIN EXCLUSIVE TRANSACTION;"
> >  "SELECT counter FROM mytable WHERE counterid = ?;"
> >  "UPDATE mytable SET counter=? WHERE counterid = ?;"
> >   "COMMIT TRANSACTION;"
>
> > I have a counter that I need to increment and get its previous value in
> one
> > operation.
> > To access this counter I must pass as a parameter to the query a specific
> > WHERE condition.
>
> To paraphrase:
>
> I want to return the current value of a counter identified by an id and
> then increment it.  This operation must be atomic (have repeatable read
> isolation).
>
> BEGIN IMMEDIATE;
> SELECT counter FROM mytable WHERE counterid = ?;
> UPDATE mytable SET counter = counter + 1 WHERE counterid = ?;
> COMMIT;
>
> Of course, the 1 in the increment does not need to be a constant but can
> be a ? if you are incrementing by some arbitrary value.
>
> You need to prepare and execute the statements one after each.
>
> And yes, the select and update, performed inside the same transaction, on
> a connection not being simultaneously used for "other purposes" is executed
> with repeatable read isolation.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-bit SQLite3.exe

[Donald Shepherd]

Why don't you build it yourself as a 64 bit executable?

On Wed, 10 Aug 2016 at 00:31 Rousselot, Richard A <
richard.a.rousse...@centurylink.com> wrote:

> I would like to request a SQLite official 64-bit SQLite3.exe CLI (not DLL)
> be created.
>
> I have reviewed the prior discussions regarding 64-bit SQLite3 and the
> reasoning for which why creating a 64-bit version is denied are "it does
> not make a real difference", "you can just use ram disks", etc., etc.
>
> Here is my plea...  I am using a set of complicated CTEs to crawl through
> a network (tree) to aggregate and calculate formulas.  I don't have
> exceptionally large datasets but my CTEs result in a ton of memory usage.
> The process works well from disk, in Windows, but using a smaller test
> sample I get about a 30% to 40% increase in processing time if I set the
> PRAGMA to temp_store = 2.  If I use a normal dataset, not a small test, I
> hit an approximate 2G limit and get a "out of memory" message, which I
> understand is due to SQLite3.exe being 32-bit.  I have found some 3rd party
> 64-bit builds for SQLite3 (best found is 3.8.5) but they are out of date
> and don't allow all functionality that I am using.  So, I do have a use
> case that requires 64-bit and I would see a significant increase in speed.
>
> As to RAM disks, I work in a corporate environment that locks down user
> rights which precludes me from distributing a tool that requires the
> creation of a tool that needs administrator rights.  I also, would like to
> avoid having to compile it myself; I am not a software engineer.
>
> Thanks for your consideration.
>
> Richard
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Keith Medcalf]

> "BEGIN EXCLUSIVE TRANSACTION;"
>  "SELECT counter FROM mytable WHERE counterid = ?;"
>  "UPDATE mytable SET counter=? WHERE counterid = ?;"
>   "COMMIT TRANSACTION;"

> I have a counter that I need to increment and get its previous value in one
> operation.
> To access this counter I must pass as a parameter to the query a specific
> WHERE condition.

To paraphrase:

I want to return the current value of a counter identified by an id and then 
increment it.  This operation must be atomic (have repeatable read isolation).

BEGIN IMMEDIATE;
SELECT counter FROM mytable WHERE counterid = ?;
UPDATE mytable SET counter = counter + 1 WHERE counterid = ?;
COMMIT;

Of course, the 1 in the increment does not need to be a constant but can be a ? 
if you are incrementing by some arbitrary value.

You need to prepare and execute the statements one after each.

And yes, the select and update, performed inside the same transaction, on a 
connection not being simultaneously used for "other purposes" is executed with 
repeatable read isolation.




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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Richard Hipp  wrote:

> Or, you could make remember() a two argument function:
>
>UPDATE mytable SET counter=remember(counter, $ptr)+1 WHERE counterid=$id
>

A sample implementation for this function can now been seen at
https://www.sqlite.org/src/artifact/8440f8d0b452c5cd
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Richard Hipp  wrote:
>
> UPDATE mytable SET counter=remember(counter)+1 WHERE counterid=?
>

Or, you could make remember() a two argument function:

   UPDATE mytable SET counter=remember(counter, $ptr)+1 WHERE counterid=$id

Then bind $ptr to the address of the variable in which you want to
store the original value (and also bind $id to the particular counter
you want to look up, of course).  That way, the same remember()
function could be reused with multiple variables for storing the
result - just rebind the $ptr value each time.

This requires casting a pointer into a 64-bit integer, which is not
guaranteed to work according to various C/C++ standards but which does
in fact work on all modern architectures.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Paulo Roberto  wrote:
>
> I need some help to ... increment a counter and get its
> former value.

> My question is: Preparing 4 statements, binding then and calling
> *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior or not? If not, how could I achieve this?
>

Yes.

Maybe another technique would be to create an application-defined
function named "remember()" that takes a single integer argument and
returns the same value, but remembers the value in a variable in your
application.  Then run:

UPDATE mytable SET counter=remember(counter)+1 WHERE counterid=?

That way you would only have a single prepared statement to deal with.
On the other hand, the results get written into a single variable, so
it wouldn't work to use this from multiple threads, unless each thread
had its own remember() function.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Simon Slavin]

On 9 Aug 2016, at 9:09pm, Paulo Roberto  wrote:

> My question is: Preparing 4 statements, binding then and calling *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior or not?

You might be happier with BEGIN IMMEDIATE.

No other connections can make changes between your BEGIN IMMEDIATE and your 
COMMIT.

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Clemens Ladisch]

Paulo Roberto wrote:
> I need some help to do a simple operation, increment a counter and get its
> former value.
> I could have some race condition, so the transaction must be atomic.
>
> I also would like to use prepared statements to accomplish that, so I have
> less effort sanitizing inputs.

Integer numbers would not need to be sanitized.

> "BEGIN EXCLUSIVE TRANSACTION;"
> "SELECT counter FROM mytable WHERE counterid = ?;"
> "UPDATE mytable SET counter=? WHERE counterid = ?;"
> "COMMIT TRANSACTION;"

> My question is: Preparing 4 statements, binding then and calling *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior?

Yes; that is what transactions are for.
(You have to ensure that errors in the middle statements are handled correctly.)


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

Re: [sqlite] switching from WAL to DELETE mode

[Clemens Ladisch]

Andrii Motsok wrote:
>> What problem do you think you can solve with this?
>
> I have one readonly connection. Is being used for reading.
> From time to time I need to modify data

So this connection is _not_ readonly.

Why do you think you cannot use a single read/write connection all the time?


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

[sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

Hello,

I need some help to do a simple operation, increment a counter and get its
former value.
I could have some race condition, so the transaction must be atomic.

I also would like to use prepared statements to accomplish that, so I have
less effort sanitizing inputs.

My problem:

I have a counter that I need to increment and get its previous value in one
operation.
To access this counter I must pass as a parameter to the query a specific
WHERE condition.

I would like something like this:

"BEGIN EXCLUSIVE TRANSACTION;"
 "SELECT counter FROM mytable WHERE counterid = ?;"
 "UPDATE mytable SET counter=? WHERE counterid = ?;"
  "COMMIT TRANSACTION;"

The *sqlite3_exec* doesn't allow me to pass parameters.
And the documentation of *sqlite3_prepare_v2 *says: "*These routines only
compile the first statement in zSql, so *pzTail is left pointing to what
remains uncompiled*"

My question is: Preparing 4 statements, binding then and calling *sqlite3_step
*for each one of then in order, would have the expected atomic operation
behavior or not? If not, how could I achieve this?

Thank you in advance.

Regards.

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

Re: [sqlite] switching from WAL to DELETE mode (Clemens Ladisch)

[Simon Slavin]

On 9 Aug 2016, at 1:58pm, Andrii Motsok  wrote:

> I don't want to bother clients of the first connection with reopening it 
> twice (DELETE->WAL before modification and WAL->DELETE after).

That's not how SQLite works.  The journal mode for a database is saved in the 
database file and new connections to the file automatically know what journal 
mode to use.  All connections to the same database automatically use the same 
journal mode.  You do not specify the journal mode every time you open a 
connection.

> I am not able to switch WAL->DELETE in second (readwrite connection) after 
> data modification.

The journal mode has nothing to do with whether you open readonly or readwrite. 
In order to change a journal mode (which you should never do in a production 
setting, only while making your initial decision about which mode to use) you 
need write permission.

If you have chosen to use WAL mode, stick with that mode.  Do not try to use 
any other mode with the database.  Each connection should open the database 
readonly or readwrite, whichever they need.  Once the connection is open you 
cannot switch between readonly and readwrite.

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

Re: [sqlite] How restrict access to SQLite database?

[Simon Slavin]

On 9 Aug 2016, at 8:16pm, Jaime Stuardo  wrote:

> No... something free out there?





Neither of these is as secure as SEE, however they are probably good enough for 
what you want to do.

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

Re: [sqlite] How restrict access to SQLite database?

[Scott Robison]

On Tue, Aug 9, 2016 at 1:16 PM, Jaime Stuardo  wrote:

> No... something free out there?
>

If you're limiting yourself to "SQLite manager of Firefox extension" then
you probably ought to check with them to see what if any options are
available to store the database in an encrypted form.

Even if they do provide some type of database level encryption, anyone who
knows you are using it only needs to obtain the password to have full
access to your data.

Another possibility depending on how you are working is to store your data
in binary blobs and do your own ad hoc encoding/encryption, but that is
risky (in that it is easy to get wrong) and makes SQL far less useful.

What about your data is confidential that you believe encryption is
required?



>
>
> Saluda cordialmente,
>
>
> Jaime Stuardo Bahamondes
> +56 9 9545 7258
> jaime.stuardo
> http://www.desytec.com
>
> Antes de imprimir este correo electrónico, piense bien si es necesario
> hacerlo: El medio ambiente es cuestión de todos.
>
> Esta comunicación (incluidos sus anexos) es Información Confidencial,
> dirigida exclusivamente a su destinatario, quien deberá resguardar la
> confidencialidad. Queda prohibido la divulgación, modificación,
> reproducción o uso de la información aquí contenida por cualquier otra
> persona que no sea su destinatario. Si Ud. ha recibido este mail por error,
> le agradeceremos nos lo informe a la brevedad respondiendo a este e-mail a
> la dirección del remitente, destruya las copias y por favor, borre de su
> sistema el mensaje recibido.
> This message (including any attachments) constitutes Confidential
> Information, and is intended for the exclusive use of the named addressee,
> who shall preserve its confidentiality. Readers of this message other than
> the intended recipient are forbidden to disseminate, modify, distribute or
> reproduce this message. If you have received this email in error please
> notify us immediately by replying to the sender of this message, discard
> any copies, and delete this message from your system
>
>
>
>
> -Mensaje original-
> De: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] En
> nombre de jungle Boogie
> Enviado el: martes, 09 de agosto de 2016 15:14
> Para: SQLite mailing list 
> Asunto: Re: [sqlite] How restrict access to SQLite database?
>
> On 9 August 2016 at 12:11, Jaime Stuardo  wrote:
> > Please, give more details. I have found I can encrypt the database, but
> how? I am using SQLite manager of Firefox extension.
>
>
> There's a paid sqlite extension called SEE:
> https://www.sqlite.org/see/doc/trunk/www/index.wiki
> http://www.hwaci.com/sw/sqlite/see.html
> http://www.hwaci.com/cgi-bin/see-step1
>
> Should be enough, right?
>
> --
> ---
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] How restrict access to SQLite database?

[Jaime Stuardo]

No... something free out there?


Saluda cordialmente,


Jaime Stuardo Bahamondes
+56 9 9545 7258
jaime.stuardo
http://www.desytec.com   

Antes de imprimir este correo electrónico, piense bien si es necesario hacerlo: 
El medio ambiente es cuestión de todos.

Esta comunicación (incluidos sus anexos) es Información Confidencial, dirigida 
exclusivamente a su destinatario, quien deberá resguardar la confidencialidad. 
Queda prohibido la divulgación, modificación, reproducción o uso de la 
información aquí contenida por cualquier otra persona que no sea su 
destinatario. Si Ud. ha recibido este mail por error, le agradeceremos nos lo 
informe a la brevedad respondiendo a este e-mail a la dirección del remitente, 
destruya las copias y por favor, borre de su sistema el mensaje recibido.
This message (including any attachments) constitutes Confidential Information, 
and is intended for the exclusive use of the named addressee, who shall 
preserve its confidentiality. Readers of this message other than the intended 
recipient are forbidden to disseminate, modify, distribute or reproduce this 
message. If you have received this email in error please notify us immediately 
by replying to the sender of this message, discard any copies, and delete this 
message from your system




-Mensaje original-
De: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] En 
nombre de jungle Boogie
Enviado el: martes, 09 de agosto de 2016 15:14
Para: SQLite mailing list 
Asunto: Re: [sqlite] How restrict access to SQLite database?

On 9 August 2016 at 12:11, Jaime Stuardo  wrote:
> Please, give more details. I have found I can encrypt the database, but how? 
> I am using SQLite manager of Firefox extension.


There's a paid sqlite extension called SEE:
https://www.sqlite.org/see/doc/trunk/www/index.wiki
http://www.hwaci.com/sw/sqlite/see.html
http://www.hwaci.com/cgi-bin/see-step1

Should be enough, right?

-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Need advice on choosing the right DB.

[Simon Slavin]

On 9 Aug 2016, at 7:47pm, Anandan Shanmugam  wrote:

> * Data must be stored securely, so that no one can manipulate the
> sales figures stored locally.

You will need to encrypt your database.  I would recommend you use SEE:



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

Re: [sqlite] How restrict access to SQLite database?

[jungle Boogie]

On 9 August 2016 at 12:11, Jaime Stuardo  wrote:
> Please, give more details. I have found I can encrypt the database, but how? 
> I am using SQLite manager of Firefox extension.


There's a paid sqlite extension called SEE:
https://www.sqlite.org/see/doc/trunk/www/index.wiki
http://www.hwaci.com/sw/sqlite/see.html
http://www.hwaci.com/cgi-bin/see-step1

Should be enough, right?

-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How restrict access to SQLite database?

[Simon Slavin]

On 9 Aug 2016, at 2:25pm, Jaime Stuardo  wrote:

> Currently, if any person knows that the application database is SQLite, he
> can open a SQLite Manager and see database content and eventually to change
> data. Is it possible to avoid that in SQLite?

You will need to encrypt your database.  I would recommend you use SEE:



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

Re: [sqlite] How restrict access to SQLite database?

[Jaime Stuardo]

Please, give more details. I have found I can encrypt the database, but how? I 
am using SQLite manager of Firefox extension.

Thanks
Jaime


-Mensaje original-
De: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] En 
nombre de Igor Korot
Enviado el: martes, 09 de agosto de 2016 15:09
Para: SQLite mailing list 
Asunto: Re: [sqlite] How restrict access to SQLite database?

Hi,

On Tue, Aug 9, 2016 at 9:25 AM, Jaime Stuardo  wrote:
> Hello,
>
>
>
> I am developing an application that should use a database. System is 
> very simple that I will try with a file based database system, 
> however, I have found a subject that is very critical for SQLite.
>
>
>
> Currently, if any person knows that the application database is 
> SQLite, he can open a SQLite Manager and see database content and 
> eventually to change data. Is it possible to avoid that in SQLite?

You can encrypt the database...

Thank you.

>
>
>
> Best regards
>
> Jaime
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] How restrict access to SQLite database?

[Igor Korot]

Hi,

On Tue, Aug 9, 2016 at 9:25 AM, Jaime Stuardo  wrote:
> Hello,
>
>
>
> I am developing an application that should use a database. System is very
> simple that I will try with a file based database system, however, I have
> found a subject that is very critical for SQLite.
>
>
>
> Currently, if any person knows that the application database is SQLite, he
> can open a SQLite Manager and see database content and eventually to change
> data. Is it possible to avoid that in SQLite?

You can encrypt the database...

Thank you.

>
>
>
> Best regards
>
> Jaime
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need advice on choosing the right DB.

[Anandan Shanmugam]

Hi There, 

 

I have a requirement to build a Windows based Point of Sales (POS)
application, the application must run on an ATOM based workstation hardware
which runs on Windows POS Ready 2009 (with 2 GB RAM) and following are
expected out of the application to be built. 

 

* The system must work off/online (without or with back office
system connectivity)

o   This means we need to have the complete product list, cashier data,
sales data sitting on the POS terminal and updated periodically by the
backend system.

o   The product list will have about 100,000 records with about 30 columns
per record

o   Cashier list is not much just about 100 records max but it will have the
biometric data (binary)

* System has to store every sales transaction captured in the
terminal and its pushed to the back end system if it is online or stored
locally until the system goes online

o   The sales data can be somewhere between 1 to 3 records per day
depends on the occasion and the POS terminal can be office line for months.

* Data stored in the POS terminal must survive during power
failures. 

* System must be fast enough to serve the customer

o   This means the system must read the record and present on the screen the
moment the barcode is scanned

* Data must be stored securely, so that no one can manipulate the
sales figures stored locally.

* Recovery of the sales information during the system failures must
be easy

* Application must be easily deployable 

* There are few other tables (around 6) to store the device
(printer, scanner, etc.) configuration, user configuration, etc.

 

I'm thinking going ahead with WPF based on Windows application for the touch
interface, SQLite for the local data store and micro services to
send/receive information from back office system. 

Just wanted to make sure I'm making the right choice for the local data
store. 

Please share your valuable inputs for me to decide the right data store
here.

 

 

Thanks and Regards

 

Anandan Shanmugam

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

[sqlite] How restrict access to SQLite database?

[Jaime Stuardo]

Hello,

 

I am developing an application that should use a database. System is very
simple that I will try with a file based database system, however, I have
found a subject that is very critical for SQLite.

 

Currently, if any person knows that the application database is SQLite, he
can open a SQLite Manager and see database content and eventually to change
data. Is it possible to avoid that in SQLite?

 

Best regards

Jaime

 

 

 

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

Re: [sqlite] switching from WAL to DELETE mode (Clemens Ladisch)

[Andrii Motsok]

Hi,


> What problem do you think you can solve with this?

I have one readonly connection. Is being used for reading.
From time to time I need to modify data using WAL approach. I don't want to 
bother clients of the first connection with reopening it twice (DELETE->WAL 
before modification and WAL->DELETE after). I am trying to do that from 
separate read-write connection. And problem which I cannot solve is that I am 
not able to switch WAL->DELETE in second (readwrite connection) after data 
modification.  So:

1)maybe there is something like purge or sync or whatever just to let first 
connection to unlock databases completely for a while

2)maybe I could use the same readonly connection but is that possible to switch 
connection like READONLY->READWRITE and than READWRITE->READONLY? I mean to 
force sqlite to reopen underlying file socket with readwrite permissions and 
than again with readonly?

Regards,
Andrii


From: sqlite-users  on behalf of 
sqlite-users-requ...@mailinglists.sqlite.org 

Sent: Monday, August 8, 2016 2:00 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: sqlite-users Digest, Vol 104, Issue 8

Send sqlite-users mailing list submissions to
sqlite-users@mailinglists.sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@mailinglists.sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@mailinglists.sqlite.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Re: switching from WAL to DELETE mode (Clemens Ladisch)
   2. Re: Locking databases - Possibly (probably?) a dumb   question
  (Rob Willett)
   3. Re: Locking databases - Possibly (probably?) a dumb   question
  (Rob Willett)
   4. Re: newbie has waited days for a DB build to complete. what's
  up with this. (Kevin O'Gorman)
   5. Re: Locking databases - Possibly (probably?) a dumb question
  (Jean-Christophe Deschamps)
   6. Re: Bug in CREATE INDEX (Kevin O'Gorman)
   7. Re: Bug in CREATE INDEX (Olivier Mascia)
   8. Re: Bug in CREATE INDEX (Dominique Pellé)
   9. Re: Bug in CREATE INDEX (Kevin O'Gorman)
  10. Re: Bug in CREATE INDEX (Dan Kennedy)
  11. SQLite 3.12 refuses to load fts3 tokenizer in Tcl and Perl
  DBD::SQLite (or missing api for scripting case)
  (hkoba {Kobayasi Hiroaki})
  12. Re: SQLite 3.12 refuses to load fts3 tokenizer in Tcl and
  Perl DBD::SQLite (or missing api for scripting case)
  (Kenichi Ishigaki)
  13. Re: Bug in CREATE INDEX (Stephan Mueller)
  14. Re: Bug in CREATE INDEX (Philip Newton)


--

Message: 1
Date: Sun, 7 Aug 2016 15:20:21 +0200
From: Clemens Ladisch 
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] switching from WAL to DELETE mode
Message-ID: <3ac6c8eb-d10d-7bca-b33c-9e37e81da...@ladisch.de>
Content-Type: text/plain; charset=utf-8

Andrii Motsok wrote:
> Is that possible to switch database from WAL to DELETE mode when holding more 
> than one connection?

No.  There is no mechanism to tell the other connections about the change.

> If no, which workaround could we use

Don't do it.  What problem do you think you can solve with this?


Regards,
Clemens


--

Message: 2
Date: Sun, 07 Aug 2016 17:20:57 +0100
From: "Rob Willett" 
To: "SQLite mailing list" 
Subject: Re: [sqlite] Locking databases - Possibly (probably?) a dumb
question
Message-ID: <27ecdfb8-b65c-46b6-9dcf-af789aaa5...@robertwillett.com>
Content-Type: text/plain; charset=utf-8; format=flowed

Ryan,

Thanks for the update.

We have done a few more tests during the day and not had any issues to
date. This is still on a test version but we are getting a warm, cuddly
feeling about using WAL mode.

The -wal file grows as you describe and you have explained it very well.
We were groping in the twilight to get to where we wanted to go, your
explanation brought a bright beacon of light onto the proceedings. (I
have been watching too many speeches from the various US political
conventions in the US though I am British).

We will investigate changing the page size. We would need to work out
the row size.

I will note in future your OCD and ensure that I am accurate in
reporting numbers rather than have self inflicted rounding errors, 60x
is a nicer number than 50x as it maps to mins and secs more easily :)

Thanks again for the help.

Rob

On 7 Aug 2016, at 12:11, R Smith wrote:

> On 2016/08/07 8:55 AM, Rob Willett wrote:
>> Richard, Ryan,
>>
>> Thanks for 

[sqlite] FTS4/5 ranking function differences

[Jan Berkel]

I'm currently implementing FTS5 in my application and I'm at the stage
where
I want to rank the results in an efficient way. I'm following the
examples
from "Appendix A: Search Application Tips"
(https://www.sqlite.org/fts3.html#appendix_a).

Similar to the example there I have a static weight component (a
separate rank
table with scores) which I want to combine with a query-specific ranking
function. The FTS4 example from the appendix reads:

  SELECT docid, rank(matchinfo(documents), documents_data.weight) AS
  rank
  ...
  ORDER BY RANK desc

The rank function gets the static score passed in and calculates a new
score, 
taking the query into account.

In FTS5, rank functions are defined with

 SELECT ... WHERE fts MATCH ? AND rank MATCH 'functionName(parameters)'
 ORDER BY RANK

The parameters need to be SQL literals, therefore I can't
pass in arbitrary data as done in the previous FTS4 example.

What would be the best way to pass the data to the function, in order to
achieve similar results?
As far as I can tell the function does not have access to the "outer"
data.

  Thanks, 

Jan


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

[sqlite] 64-bit SQLite3.exe

[Rousselot, Richard A]

I would like to request a SQLite official 64-bit SQLite3.exe CLI (not DLL) be 
created.

I have reviewed the prior discussions regarding 64-bit SQLite3 and the 
reasoning for which why creating a 64-bit version is denied are "it does not 
make a real difference", "you can just use ram disks", etc., etc.

Here is my plea...  I am using a set of complicated CTEs to crawl through a 
network (tree) to aggregate and calculate formulas.  I don't have exceptionally 
large datasets but my CTEs result in a ton of memory usage.  The process works 
well from disk, in Windows, but using a smaller test sample I get about a 30% 
to 40% increase in processing time if I set the PRAGMA to temp_store = 2.  If I 
use a normal dataset, not a small test, I hit an approximate 2G limit and get a 
"out of memory" message, which I understand is due to SQLite3.exe being 32-bit. 
 I have found some 3rd party 64-bit builds for SQLite3 (best found is 3.8.5) 
but they are out of date and don't allow all functionality that I am using.  
So, I do have a use case that requires 64-bit and I would see a significant 
increase in speed.

As to RAM disks, I work in a corporate environment that locks down user rights 
which precludes me from distributing a tool that requires the creation of a 
tool that needs administrator rights.  I also, would like to avoid having to 
compile it myself; I am not a software engineer.

Thanks for your consideration.

Richard
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected behavior in LEMON parser generator?

[Richard Hipp]

On 8/9/16, Kraus, Stefan  wrote:
> Hello,
>
> i have a question regarding the LEMON parser generator:
>
> I am writing an expression grammar. The token LT means "<", while GT means
> ">".
> When i use this grammar, everything works as expected:
>
> %nonassoc  LT GT .
> ex ::= ex LT ex .
> ex ::= ex GT ex .
>
> There are no conflicts, and the non-associativity is handled correctly, e.g.
> the expression "2 < 3 < 4" is reported as a syntax error.
>
> However, changing the grammar to
>
> %nonassoc  COMPARISON .
> ex ::= ex LT ex . [COMPARISON]
> ex ::= ex GT ex . [COMPARISON]
>
> does not work as i expected. First, the expression "2 < 3 < 4" is not
> reported as a syntax error, but is processed instead. Moreover, parsing
> conflicts are reported between LT and GT. I thought that both versions
> should be equivalent?

The two grammars are *not* equivalent.

Precedence can be associated with both tokens and rules.  The
%nonassoc directive assigns a precedence to a token.  The [ID]
construct assigns a precedence to a rule.  Any rule that lacks the
[...] construct (which is to say, most rules) is assigned the
precedence of the left-most token in the rule.

When a SHIFT/REDUCE conflict occurs, the conflict is resolved by
comparing the precedence of the SHIFT token against the precedence of
the REDUCE rule.  In the first grammar above, both the SHIFT token and
the REDUCE rule have NONASSOC precedence, which means the SHIFT/REDUCE
conflict is resolved by generating a syntax error, which is what you
want.

But in the second grammar, the LT and GT tokens have no precedence.
And hence the SHIFT/REDUCE conflict resolution rules cannot be
applied.  A SHIFT/REDUCE conflict is reported.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected behavior in LEMON parser generator?

[Kraus, Stefan]

Hello,

i have a question regarding the LEMON parser generator:

I am writing an expression grammar. The token LT means "<", while GT means ">".
When i use this grammar, everything works as expected:

%nonassoc  LT GT .
ex ::= ex LT ex .
ex ::= ex GT ex .

There are no conflicts, and the non-associativity is handled correctly, e.g. 
the expression "2 < 3 < 4" is reported as a syntax error.

However, changing the grammar to

%nonassoc  COMPARISON .
ex ::= ex LT ex . [COMPARISON]
ex ::= ex GT ex . [COMPARISON]

does not work as i expected. First, the expression "2 < 3 < 4" is not reported 
as a syntax error, but is processed instead. Moreover, parsing conflicts are 
reported between LT and GT. I thought that both versions should be equivalent?

Do i make a mistake or is that a bug? I have tried the above example in the 
LIME parser generator for PHP, which is described as ported from LEMON, and 
there it worked as expected (using the %prec directive). I will need this 
feature (setting precedence in a rule) for a domain specific language with a 
rather complex expression grammar.

Greetings + Thanks for Helping
Stefan

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