Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks Jay,

excellent response. I'll ask for clarity on one statement though.

That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>

I have a self styled routine (similar to the glibc manual example) for
concatenating the strings values that make up the sql statement. It uses
memcpy rather than the built in strcat etc. So what exactly is the issue
with the string building if it does  not include sql derived from user
input? I'm not quite seeing that bit, sorry or the vagueness.

It does however sound like it would just be better to adopt the three step
functions as the preferred method in all cases, which is probably what I'm
trying to come to grips with. I do see the prepare/step/finalize process
with bound parameters etc is very much preferred in most cases, but
wondered if those cases where SQL is application provided were an
exception. I'm leaning towards a no on that now. Thanks for your input and
in advance or any additional insight.


On 11 August 2016 at 14:32, Jay Kreibich  wrote:

>
> On Aug 10, 2016, at 9:21 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Hi all,
> >
> > just seeking some opinions, and perhaps some dev indications about
> > deprecation, in relation to the sqlite3_exec facility. I kind of like the
> > callback functionality in certain cases as it is convenient in some
> > circumstances where the sql injection problem is not an issue.
> >
> > Ok I say it is not an issue, but am I right. I am no security expert and
> > have often been surprised at some of the hack techniques used over the
> > years. The sql injection issue as far as I can tell depends on where the
> > offending sql originates, but don't hesitate to contradict that
> assumption
> > if you believe it is wrong.
>
> That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>
> > In a scenario where the sql supplied to the callback routine is
> application
> > generated or indeed application constant based does the sql injection
> > threat disappear?
>
> Yes.  The threat is when you start to use sprintf() to built your query
> strings.
> Even more so if some of those inputs can trace their origin to user
> generated
> values.
>
> > user supplied sql via arguments, with only database name and table name
> > required from the user. This would appear to be immune to that technique
> or
> > am I misguided? I'm never certain when it comes to security stuff, I hate
> > it.
>
> You are misguided.  The whole idea behind injections is that you can alter
> that
> database name so that it is much more than a database name.  If you can’t
> understand the whys of it, you can’t defend against it.  And that’s
> important
> in this case, since you can’t use bound parameters for database names or
> table names.  Switching to _prepare() won’t help in this specific case
> because
> you have no choice but to build the query from string primitives.
>
>
> Another issue with sqlite_exec() is that all the values are returned as
> strings.
> You have no idea what the type of the field is, and if it is a non-string
> value,
> it is converted to a string before the callback is called.  This can be a
> big issue
> for many designs.
>
> > In a similar vein I noted in an O'Reilly publication it mentioned that
> the
> > exec method was semi depracated and should be avoided. I wondered what
> the
> > view of the SQLite dev crew was. and if there were any plans in the
> future
> > to drop the exec function? In light of the teams focus on backward
> > compatibility I suspect there are no such plans but I thought I'd ask
> > anyway just to be sure. Thanks in advance for any helpful comments.
>
>   -j  (Author, Using SQLite).
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Exec vs Prepare, step, finalize.

[Jay Kreibich]

On Aug 10, 2016, at 9:21 PM, Michael Falconer  
wrote:

> Hi all,
> 
> just seeking some opinions, and perhaps some dev indications about
> deprecation, in relation to the sqlite3_exec facility. I kind of like the
> callback functionality in certain cases as it is convenient in some
> circumstances where the sql injection problem is not an issue.
> 
> Ok I say it is not an issue, but am I right. I am no security expert and
> have often been surprised at some of the hack techniques used over the
> years. The sql injection issue as far as I can tell depends on where the
> offending sql originates, but don't hesitate to contradict that assumption
> if you believe it is wrong.

That’s the basic theory, but even knowing that, most people get it wrong.
In short, if you’re using string manipulation functions to build your query
string, you’re very very very very likely doing it wrong.

> In a scenario where the sql supplied to the callback routine is application
> generated or indeed application constant based does the sql injection
> threat disappear?

Yes.  The threat is when you start to use sprintf() to built your query strings.
Even more so if some of those inputs can trace their origin to user generated
values.

> user supplied sql via arguments, with only database name and table name
> required from the user. This would appear to be immune to that technique or
> am I misguided? I'm never certain when it comes to security stuff, I hate
> it.

You are misguided.  The whole idea behind injections is that you can alter that
database name so that it is much more than a database name.  If you can’t
understand the whys of it, you can’t defend against it.  And that’s important
in this case, since you can’t use bound parameters for database names or
table names.  Switching to _prepare() won’t help in this specific case because
you have no choice but to build the query from string primitives.


Another issue with sqlite_exec() is that all the values are returned as strings.
You have no idea what the type of the field is, and if it is a non-string value,
it is converted to a string before the callback is called.  This can be a big 
issue
for many designs.

> In a similar vein I noted in an O'Reilly publication it mentioned that the
> exec method was semi depracated and should be avoided. I wondered what the
> view of the SQLite dev crew was. and if there were any plans in the future
> to drop the exec function? In light of the teams focus on backward
> compatibility I suspect there are no such plans but I thought I'd ask
> anyway just to be sure. Thanks in advance for any helpful comments.

  -j  (Author, Using SQLite).


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

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson



___
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 very much, it worked!

On Tue, Aug 9, 2016 at 11:49 PM, Richard Hipp  wrote:

> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite v3.14.0 Installation Error

[Weidong Shen]

Hi,
I just tried to install SQLite v3.14.0 on Windows 10 Pro with Anniversary 
update/Visual Studio 2015 Update 3, and received the following error. Also, 
everyone is reporting the same error at the following link:SQLite for Universal 
Windows Platform extension

  
|  
|   
|   
|   ||

   |

  |
|  
||  
SQLite for Universal Windows Platform extension
 SQLite is a software library that implements a self-contained, serverless, 
zero-configuration, transactional SQL...  |   |

  |

  |

 

==8/10/2016 10:04:08 PM - 
Microsoft VSIX Installer8/10/2016 10:04:08 PM - 
---8/10/2016 10:04:09 PM - Initializing 
Install...8/10/2016 10:04:09 PM - Extension Details...8/10/2016 10:04:09 PM -  
Identifier         : SQLite.UWP.20158/10/2016 10:04:09 PM -  Name               
: SQLite for Universal Windows Platform8/10/2016 10:04:09 PM -  Author          
   : SQLite Development Team8/10/2016 10:04:09 PM -  Version            : 
3.14.08/10/2016 10:04:09 PM -  Description        : SQLite is a software 
library that implements a self-contained, serverless, zero-configuration, 
transactional SQL database engine.8/10/2016 10:04:09 PM -  Locale             : 
en-US8/10/2016 10:04:09 PM -  MoreInfoURL        : 
http://www.sqlite.org/8/10/2016 10:04:09 PM -  InstalledByMSI     : 
False8/10/2016 10:04:09 PM -  SupportedFrameworkVersionRange : 
[0.0,2147483647.2147483647]8/10/2016 10:04:09 PM - 8/10/2016 10:04:09 PM -  
SignatureState     : Unsigned8/10/2016 10:04:09 PM -  Supported Products : 
8/10/2016 10:04:09 PM -  Microsoft.ExtensionSDK8/10/2016 10:04:09 PM -  Version 
: 8/10/2016 10:04:09 PM - 8/10/2016 10:04:09 PM -  References         : 
8/10/2016 10:04:09 PM - Signature Details...8/10/2016 10:04:09 PM -  Extension 
is not signed.8/10/2016 10:04:09 PM - 8/10/2016 10:04:09 PM - Searching for 
applicable products...8/10/2016 10:04:09 PM - Found installed product - 
Microsoft Visual Studio Enterprise 20158/10/2016 10:04:09 PM - Found installed 
product - Microsoft Visual Studio Professional 20158/10/2016 10:04:09 PM - 
Found installed product - Microsoft Visual Studio Community 20158/10/2016 
10:04:09 PM - Found installed product - Microsoft Visual Studio 2015 Shell 
(Integrated)8/10/2016 10:04:09 PM - Found installed product - Global 
Location8/10/2016 10:04:09 PM - The extension will be upgraded from version 
3.13.0.8/10/2016 10:04:11 PM - The following target products have been 
selected...8/10/2016 10:04:11 PM -  Global Location8/10/2016 10:04:11 PM - 
8/10/2016 10:04:11 PM - Beginning to install extension to Global 
Location...8/10/2016 10:04:11 PM - Upgrading 'SQLite for Universal Windows 
Platform', version 3.13.0 to version 3.14.0.8/10/2016 10:04:11 PM - Install 
Error : System.NotImplementedException: The method or operation is not 
implemented.   at 
VSIXInstaller.MemorySettingsManager.MemoryWritableSettingsStore.GetBoolean(String
 collectionPath, String propertyName, Boolean defaultValue)   at 
Microsoft.VisualStudio.ExtensionManager.ExtensionManagerService.IsCertCheckSkipped()
   at 
Microsoft.VisualStudio.ExtensionManager.ExtensionManagerService.CheckCertificateBeforeUpgrade(InstallableExtensionImpl
 extension, IInstalledExtension installedVersion, Boolean 
enforceCertificateCheckForUpgrade)   at 
Microsoft.VisualStudio.ExtensionManager.ExtensionManagerService.InstallInternal(InstallableExtensionImpl
 extension, Boolean perMachine, Boolean isNestedExtension, IDictionary`2 
extensionsInstalledSoFar, List`1 extensionsUninstalledSoFar, 
IInstalledExtensionList modifiedInstalledExtensionsList, AsyncOperation 
asyncOp, UInt64 totalBytesToWrite, UInt64& totalBytesWritten, Boolean 
enforceCertificateCheckForUpgrade)   at 
Microsoft.VisualStudio.ExtensionManager.ExtensionManagerService.BeginInstall(IInstallableExtension
 installableExtension, Boolean perMachine, AsyncOperation asyncOp, Boolean 
enforceCertificateCheckForUpgrade)   at 
Microsoft.VisualStudio.ExtensionManager.ExtensionManagerService.InstallWorker(IInstallableExtension
 extension, Boolean perMachine, AsyncOperation asyncOp, Boolean 
enforceCertificateCheckForUpgrade)==
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Hi all,

just seeking some opinions, and perhaps some dev indications about
deprecation, in relation to the sqlite3_exec facility. I kind of like the
callback functionality in certain cases as it is convenient in some
circumstances where the sql injection problem is not an issue.

Ok I say it is not an issue, but am I right. I am no security expert and
have often been surprised at some of the hack techniques used over the
years. The sql injection issue as far as I can tell depends on where the
offending sql originates, but don't hesitate to contradict that assumption
if you believe it is wrong.

In a scenario where the sql supplied to the callback routine is application
generated or indeed application constant based does the sql injection
threat disappear? Is this a valid assumption? In other words there is no
user supplied sql via arguments, with only database name and table name
required from the user. This would appear to be immune to that technique or
am I misguided? I'm never certain when it comes to security stuff, I hate
it.

In a similar vein I noted in an O'Reilly publication it mentioned that the
exec method was semi depracated and should be avoided. I wondered what the
view of the SQLite dev crew was. and if there were any plans in the future
to drop the exec function? In light of the teams focus on backward
compatibility I suspect there are no such plans but I thought I'd ask
anyway just to be sure. Thanks in advance for any helpful comments.


-- 
Regards,
 Michael.j.Falconer.
___
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

[Michael Falconer]

Thanks Ryan,

and apologies for singling your comment out from the many that suggested a
roll-your-own CLI. That last statement pretty much says it all IMHO but I
would go even further. For many it will not even be choice in future times.
There will always be those who can make their own arrangements and hedge
around the inevitable but for most it will be 64bit or nothing one day. An
above post regarding Android 32 bit platform support is one indication that
the choices will not last for much longer. I should add that it is not
entirely high on my personal wish list (64bit CLI) because I do take the
valid points made earlier that the current CLI works just fine in a 32 bit
environment.

All of that being said, I have ultimate faith in the core SQLite
development team to make the right call about when it would be appropriate
to go the 64 bit path. There would be much to consider and it should be
considered carefully as always, I'm in no rush but apparently some others
have more pressing needs. Just as I am not in a rush, I also have no
problem with the OP's request being implemented. I'm with Ryan on the warm
and cuddly approach, we might as well.


On 11 August 2016 at 10:58, R Smith  wrote:

>
> On 2016/08/10 11:35 PM, Michael Falconer wrote:
>
>> ...// Ryan is very
>> right about the relative ease of such exercises but IMHO wrong that it
>> suits all, or even most cases.
>>
>
> Just to be clear - if my post wasn't, I never advocated against the 64-bit
> CLI or that making it suits all, I very much supported the OP in the
> request (and still do) for some of the reasons Michael mentions here. All I
> said was that right now (until it is supplied), if he needs it as much as
> claimed, rolling his own is not all that hard (and certainly not a useless
> skill).
> I do not agree with the notion that the 64-bit CLI "shouldn't be
> pre-compiled because it's easy to do it yourself" - if that was true, why
> supply the 32-bit even?, or indeed ANYthing pre-compiled?
>
> To reiterate: 64-bit is the new thing - let's embrace it!
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
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

[R Smith]

On 2016/08/10 11:35 PM, Michael Falconer wrote:

...// Ryan is very
right about the relative ease of such exercises but IMHO wrong that it
suits all, or even most cases.


Just to be clear - if my post wasn't, I never advocated against the 
64-bit CLI or that making it suits all, I very much supported the OP in 
the request (and still do) for some of the reasons Michael mentions 
here. All I said was that right now (until it is supplied), if he needs 
it as much as claimed, rolling his own is not all that hard (and 
certainly not a useless skill).
I do not agree with the notion that the 64-bit CLI "shouldn't be 
pre-compiled because it's easy to do it yourself" - if that was true, 
why supply the 32-bit even?, or indeed ANYthing pre-compiled?


To reiterate: 64-bit is the new thing - let's embrace it!


___
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

[Keith Medcalf]

> On Aug 10, 2016, at 6:03 AM, Keith Medcalf  wrote:

> >> Even on a 64-bit processor, there’s usually no reason to run 64-bit
> >> Windows unless you have more than 4 GB of RAM, a threshold we didn’t
> >> pass very long ago.

> > Yes, please remember to keep the "addressable memory limits" linkage to
> "processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need
> 64-bit processors or 64-bit Operating systems to be able to address more
> than 4 GB of physical RAM.  In fact that there are 32-bit versions of
> Windows (NT 3.5, 4.0, 2000, XP, 2003 etc) which have been compiled without
> this artificially imposed limitation.  You pay more for "properly written
> and compiled" software however because, well, it is easy to do stupid
> things and impose stupid limits for no reason and you need higher wattage
> (therefore more expensive people) if you want software that is not bounded
> by a crapload of inane if not brain-dead) design decisions.
> 
> You must be talking about PAE, which is an unmitigated hack, in the
> dirtiest sense of that word:
> 
>   https://en.wikipedia.org/wiki/Physical_Address_Extension

It is not a hack.  It is how things work.  I do not see where you get the idea 
that it is a hack.  In fact, non-Windows have supported physical address limits 
beyond 4 GB as standard since a very long time (Linux since 2009).

> As you hint, some OSes allow individual apps to allocate extra RAM via PAE
> — UnixWare was one such — but due to the way PAE works, it can never be
> more than 8 GiB per process at a given time.

I hinted at no such thing.  The original quoted paragraph said "more than 4 GB 
of RAM".  This has nothing to do with the per-process allocation which is an 
artifact of how badly or ill-conceived the Operating System architecture and 
the physical implementation of the V:R handling.  Whether the machine and OS 
can physically address more than 4 GB of physical RAM has nothing whatsoever to 
do with the "bitedness" of the OS or the CPU, only the width of the physical 
address bus and the translation tables and hardware.

> The OS *could* page in and out 4 GiB segments to give a single application
> the run of the whole 64 GiB maximum space PAE allows, but I don’t know of
> any OS that does this.


Again you are talking about per-process addressable space which is an entirely 
separate issue from physical addressable RAM size.
 
> Linux in particular doesn’t let individual applications use PAE to access
> more than 3 GiB of VM space, with the standard 3/1 user/kernel split.
> Instead, if you have more than 4 GiB of RAM in the machine and are running
> a PAE kernel, it will let you have multiple programs *collectively* using
> more than 4 GiB of VM space.  That’s not going the help the OP.

And yet more of the same.  You are much confused between "CPU accessing 
physical RAM (the :R part)" and "processes accessing virtual RAM (the V: part).

> Quoting Wikipedia, “…regular application software continues to use
> instructions with 32-bit addresses and (in a flat memory model) is limited
> to 4 gigabytes of virtual address space…no single regular application can
> access [all 64 GiB] simultaneously.”

And your point is what exactly?  We are not discussing per process access to 
virtual memory limitations, but rather artificial physical memory access 
limitations.  You will notice that even Microsoft admits that they limit access 
to physical RAM purely so they can make more money, and for no other reason,. 
and they do so in the very article you linked!

> I believe the situation is essentially the same on PAE-enabled versions of
> Windows as on Linux.

Again, you are confused between per process addressing of Virtual Memory and 
CPU access of Physical Memory.  Even 32-bit Linux has been able to access more 
than 4 GB of physical RAM since at least 2009.
 
> It is also the case that most machines that shipped with 32-bit Intel
> processors either didn’t have enough slots to allow > 4 GiB of RAM or
> didn’t have BIOS/EFI/chipset support for that much RAM if you did have the
> slots for it.  And why should they have done?  It just adds cost with a
> low chance that the user can make use of it, so that capability only
> showed up in high-dollar machines.

Nor did cheap motherboards wire the bus master request pin properly to the 
expansion bus even though the correct wiring was part of the "IBM compatible" 
specification.  Just because someone may choose to purchase low-end product 
does not mean that the capabilities for bus mastering (or in the case of the 
present conversation, accessing more than 4 GB of physical memory) does not 
validate your argument.

> PAE is also not restricted to non-Windows OSes.  Microsoft simply chose
> not to support it on the non-Server versions of Windows, but that is
> essentially a market segmentation issue, not a technical one.

Exactly -- it is an artifice to make more money for nothing.

> PAE’s time is long past.  64-bit 

Re: [sqlite] Percentage instead of count in GROUP BY

[R Smith]

On 2016/08/11 12:43 AM, Cecil Westerhof wrote:

2016-08-10 21:54 GMT+02:00 R Smith :



On 2016/08/10 5:13 PM, Cecil Westerhof wrote:


2016-08-10 11:43 GMT+02:00 R Smith :

On 2016/08/10 11:20 AM, Cecil Westerhof wrote:

At the moment I have:

SELECT   idletime, COUNT(*)
FROM vmstat
GROUP BY idletime

But because there are about 400.000 records (and counting) it is not the
most convenient information. Instead of the number of records I would
like
to have the percentage of the records. Is this possible?

Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.


Question 1:

WITH ST(cAll) AS ( SELECT COUNT(*) FROM vmstat )
SELECT idletime, (100 * COUNT(*) / ST.cAll) AS Perc
FROM vmstat, ST
   GROUP BY idletime

​Has the same problem as the solution of Clemens: delivers only zeros.

But
rewritten like this works:
WITH ST(onePercent) AS (SELECT COUNT(*) / 100.0 FROM vmstat)
SELECT idletime, COUNT(*), COUNT(*) / ST.onePercent AS Percentage
FROM vmstat, ST
GROUP BY idletime
ORDER BY Percentage DESC


Impossible - unless something is weird about your schema/data, I think
Clemens' solution should work too, if it gets zeroes, something else is
wrong. Any chance you can give us a bit of sql to build a sample dataset
table or perhaps post your DB somewhere we can download it and try?


​It is because of integer arithmetic. When using:
 SELECT 100 * 50 / 5, 100 * 50 / 5.0
I get:
 0 and 0.1

Or is SQLite browser the culprit here?


No, you're quite right - It's some integer arithmetic being the culprit. 
This next script demonstrates the problem, a fix for it and a way to do 
what you originally intended without the convolution (and with thwarting 
the integer calcs):


CREATE TABLE T (Grp INT);

INSERT INTO T VALUES (1),(1),(2),(2),(2),(3),(4),(4),(4),(4),(4),(4),(5);

WITH TC(Tot) AS (
  SELECT COUNT(*) FROM T
)
SELECT Grp, (COUNT(*) / Tot * 100) AS Perc
  FROM T, TC
 GROUP BY Grp;


  --  Grp | Perc
  --  | 
  --   1  |   0
  --   2  |   0
  --   3  |   0
  --   4  |   0
  --   5  |   0

WITH TC(Tot) AS (
  SELECT CAST(COUNT(*) AS REAL) FROM T
)
SELECT Grp, (COUNT(*) / Tot * 100) AS Perc
  FROM T, TC
 GROUP BY Grp;


  --  Grp | Perc
  --  | 
  --   1  | 15.3846153846154
  --   2  | 23.0769230769231
  --   3  | 7.69230769230769
  --   4  | 46.1538461538462
  --   5  | 7.69230769230769

WITH TC(Tot) AS (
  SELECT CAST(COUNT(*) AS REAL) FROM T
), SL(Slice) AS (
  SELECT CAST(((100 * COUNT(*) / Tot) / 5) AS INT) * 5
FROM T, TC
   GROUP BY Grp
), ST(SliceTot) AS (
  SELECT CAST(COUNT(*) AS REAL) FROM SL
)
SELECT Slice, COUNT(*), (COUNT(*) / SliceTot * 100) AS Perc
  FROM SL, ST
 GROUP BY Slice


  -- Slice| COUNT(*) |  Perc
  --  |  | --
  --   5  | 2|  40.0
  --  15  | 1|  20.0
  --  20  | 1|  20.0
  --  45  | 1|  20.0

___
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

[John McKown]

On Wed, Aug 10, 2016 at 4:35 PM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> Great discussion, if somewhat amusing (with a hint of deja vu) for an old
> hack like myself.
>
> I remember discussions like this back in CP/M days when it was a move rom 8
> to 16bit. Oh, and it all happened again when 32bit came along and yes now
> we are into 64bit and it carries on.
>

​A bit OT, but this entire thing is mildly amusing to me. I've worked on
the "obsolete" IBM mainframes for 30+ years. Originally 24 bit addressing;
then 31 bit (yeah, 31 not 32 - weird); now up to 64 bit. An guess what? The
programs that I wrote 30+ years ago in 24 bit still work correctly in
today's 31 ​and 64 bit systems as is without recompiling. Of course, the
programs don't use any of the new, fancy stuff. But they still work
correctly.


> --
> Regards,
>  Michael.j.Falconer.
>


-- 
Klein bottle for rent -- inquire within.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Percentage instead of count in GROUP BY

[Cecil Westerhof]

2016-08-10 21:54 GMT+02:00 R Smith :

>
>
> On 2016/08/10 5:13 PM, Cecil Westerhof wrote:
>
>> 2016-08-10 11:43 GMT+02:00 R Smith :
>>
>> On 2016/08/10 11:20 AM, Cecil Westerhof wrote:
>>>
>>> At the moment I have:
 SELECT   idletime, COUNT(*)
 FROM vmstat
 GROUP BY idletime

 But because there are about 400.000 records (and counting) it is not the
 most convenient information. Instead of the number of records I would
 like
 to have the percentage of the records. Is this possible?

 Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.


 Question 1:
>>> WITH ST(cAll) AS ( SELECT COUNT(*) FROM vmstat )
>>> SELECT idletime, (100 * COUNT(*) / ST.cAll) AS Perc
>>>FROM vmstat, ST
>>>   GROUP BY idletime
>>>
>>> ​Has the same problem as the solution of Clemens: delivers only zeros.
>> But
>> rewritten like this works:
>> WITH ST(onePercent) AS (SELECT COUNT(*) / 100.0 FROM vmstat)
>> SELECT idletime, COUNT(*), COUNT(*) / ST.onePercent AS Percentage
>> FROM vmstat, ST
>> GROUP BY idletime
>> ORDER BY Percentage DESC
>>
>
> Impossible - unless something is weird about your schema/data, I think
> Clemens' solution should work too, if it gets zeroes, something else is
> wrong. Any chance you can give us a bit of sql to build a sample dataset
> table or perhaps post your DB somewhere we can download it and try?
>

​It is because of integer arithmetic. When using:
SELECT 100 * 50 / 5, 100 * 50 / 5.0
I get:
0 and 0.1

Or is SQLite browser the culprit here?


pragma table_info(vmstat) gives:
"0""vmstatID""INTEGER""0""NULL""1"
"1""date""TEXT""1""CURRENT_DATE""0"
"2""time""TEXT""1""CURRENT_TIME""0"
"3""runlength""INTEGER""1""NULL""0"
"4""runable""INTEGER""1""NULL""0"
"5""uninteruptable""INTEGER""1""NULL""0"
"6""swap""INTEGER""1""NULL""0"
"7""free""INTEGER""1""NULL""0"
"8""buffers""INTEGER""1""NULL""0"
"9""cache""INTEGER""1""NULL""0"
"10""swapin""INTEGER""1""NULL""0"
"11""swapout""INTEGER""1""NULL""0"
"12""blockin""INTEGER""1""NULL""0"
"13""blockout""INTEGER""1""NULL""0"
"14""interuptspersec""INTEGER""1""NULL""0"
"15""contextswitchespersec""INTEGER""1""NULL""0"
"16""usertime""INTEGER""1""NULL""0"
"17""systemtime""INTEGER""1""NULL""0"
"18""idletime""INTEGER""1""NULL""0"
"19""waittime""INTEGER""1""NULL""0"
"20""stolentime""INTEGER""1""NULL""0"

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

[Michael Falconer]

Great discussion, if somewhat amusing (with a hint of deja vu) for an old
hack like myself.

I remember discussions like this back in CP/M days when it was a move rom 8
to 16bit. Oh, and it all happened again when 32bit came along and yes now
we are into 64bit and it carries on.

Simple really, the OP is completely correct to be requesting a 64bit CLI as
we are now very much in that world. Of course a legacy 32bit version is
appropriate but surely the mainstream dev must move on to 64bit at  some
stage? Not for me to say when this should occur but surely it is inevitable
or am I just an old dinosaur?

Some excellent side points made (single out DD here) and one o the
standouts is the quite ridiculous stance many developers take regarding
user requests such as the one made in the OP. There is absolutely no
requirement at all for end users to be software developers/engineers, that
would be absurd and I doubt this was ever the intent of sqlite development.
Roll your own compilations in some cases are not an option and quite
possibly for multiple reasons. I'm not going to bore you by expanding the
obvious, but the OP identified one such case as a regulated corporate
environment, a beast I am unfortunately all too familiar with. Ryan is very
right about the relative ease of such exercises but IMHO wrong that it
suits all, or even most cases.

I may be old, but I'm a progressive dinosaur. We have to move on, and in
this biz, quickly if we want to remain in any way current. We can make
choices as developers about whether we embrace such things as OO or R or
whatever new methodology comes along but we don't get that same choice
about base system changes such as 16/32/64 bit processors. They are like
death and taxes, inevitable.

So when the 64bit CLI arrives, as it surely will do, the OP will be
vindicated and all the rest will be historic fluff. Just my opinion, but as
I said great discussion from my very favourite technical list.


On 11 August 2016 at 01:11, Jim Callahan 
wrote:

> The issue is not simply recompiling the CLI for 64-bit; I assume you want
> the tested reliability of SQLite.
>
> "The [SQLite] project has 787 times as much test code and test scripts" as
> it does source code for the executable.
> https://www.sqlite.org/testing.html
>
>
> Running the exact same tests would not be enough, because you (and I) would
> want special tests for larger than 4 GB
> RAM. So, additional tests would have to be developed.
>
> So, I see three alternate solutions:
>
> 1. Find a corporate sponsor to fund development of 64-bit SQLite CLI
>
> 2. Remove some tables from memory (though it sounds like your difficulties
> are caused
> by recursion rather than the size of the raw data)
>
> 3. Use SQLite for persistent storage but move the in memory (tree
> navigation) operations to another (open source)  language
> such as C, Java, Python or R (or the new Julia language that is approaching
> version 1.0)  that has interfaces for SQLite
> and a 64-bit build for Windows.  You will probably need another language to
> display the output anyway why not take
> advantage of Python, R or Julia?
>
> Even if you move to another language, you may find that your problem is
> recursion.
> In my experience, computer science textbooks give elegant examples using
> recursion,
> but then say the solution is not scale-able and give a less elegant
> solution using iterative techniques.
>
> Jim Callahan
> Data Scientist
> Orlando, FL
>
>
> On Tue, Aug 9, 2016 at 10:31 AM, 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 

Re: [sqlite] Percentage instead of count in GROUP BY

[R Smith]

On 2016/08/10 5:13 PM, Cecil Westerhof wrote:

2016-08-10 11:43 GMT+02:00 R Smith :


On 2016/08/10 11:20 AM, Cecil Westerhof wrote:


At the moment I have:
SELECT   idletime, COUNT(*)
FROM vmstat
GROUP BY idletime

But because there are about 400.000 records (and counting) it is not the
most convenient information. Instead of the number of records I would like
to have the percentage of the records. Is this possible?

Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.



Question 1:
WITH ST(cAll) AS ( SELECT COUNT(*) FROM vmstat )
SELECT idletime, (100 * COUNT(*) / ST.cAll) AS Perc
   FROM vmstat, ST
  GROUP BY idletime


​Has the same problem as the solution of Clemens: delivers only zeros. But
rewritten like this works:
WITH ST(onePercent) AS (SELECT COUNT(*) / 100.0 FROM vmstat)
SELECT idletime, COUNT(*), COUNT(*) / ST.onePercent AS Percentage
FROM vmstat, ST
GROUP BY idletime
ORDER BY Percentage DESC


Impossible - unless something is weird about your schema/data, I think 
Clemens' solution should work too, if it gets zeroes, something else is 
wrong. Any chance you can give us a bit of sql to build a sample dataset 
table or perhaps post your DB somewhere we can download it and try?




​


​And this solution is of-course better, because now the total count is only
fetched once. (I think.) Not really important in this case, but nice to
have.​


That is the seeming situation, but do not underestimate the power of the 
SQLite query engine, it will likely iron out that sub-query into a 
constant expression. I like the CTE more as a logic expression than an 
efficiency mechanism in this case, however, in cases where the sub-query 
cannot be ironed out automatically, I think the CTE will be best, so I 
always opt for it as a standard. (but that's just me...)



As to your next e-mail about the slices not working, again, it should... 
if we could have a look at an actual table you have, perhaps we can give 
better queries or see what's amiss.


Cheers,
Ryan

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

Re: [sqlite] Percentage instead of count in GROUP BY

[Cecil Westerhof]

2016-08-10 17:31 GMT+02:00 Cecil Westerhof :

> 2016-08-10 17:13 GMT+02:00 Cecil Westerhof :
>
>> Question 2:
>>
>>> Not sure how you want to slice data, I am assuming you mean you want to
>>> know how many values (as in percent of total) falls within each slice? If
>>> so, this should work:
>>>
>>
>> ​Yes, that is what I mean. I am going to try it out.​
>>
>>
>>
>>
>>> WITH ST(cAll) AS (
>>> SELECT COUNT(*) FROM vmstat
>>> ), SL(Slice) AS (
>>> SELECT (CAST(((100 * COUNT(*) / ST.cAll) / 5) AS INT) * 5)
>>>  FROM vmstat, ST
>>> )
>>> SELECT Slice||' - '||(Slice+5), (100 * COUNT(*) / ST.cAll) AS Perc
>>>   FROM SL, ST
>>>  GROUP BY Slice
>>>
>>> [Might need a little fiddling to produce the output exactly how you want
>>> it, but the basics should be clear]
>>>
>>
> ​Does not work. SL gives a table that has exactly one element.
>


​I have something working. It is a bit labour intensive (that is why I went
for slices of 10%), so if someone has a better option …

WITH ST(onePercent) AS (
SELECT COUNT(*) / 100.0 FROM vmstat
), SL(Slice, Count, Percent) AS (
SELECT ' 0 -  10', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >=  0 AND idletime <=   10
UNION
SELECT '11 -  20', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 11 AND idletime <=   20
UNION
SELECT '21 -  30', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 21 AND idletime <=   30
UNION
SELECT '31 -  40', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 31 AND idletime <=   40
UNION
SELECT '41 -  50', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 41 AND idletime <=   50
UNION
SELECT '51 -  60', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 51 AND idletime <=   60
UNION
SELECT '61 -  70', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 61 AND idletime <=   70
UNION
SELECT '71 -  80', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 71 AND idletime <=   80
UNION
SELECT '81 -  90', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 81 AND idletime <=   90
UNION
SELECT '91 - 100', COUNT(*), COUNT(*) / onePercent  FROM vmstat, ST
WHERE idletime >= 91 AND idletime <= 100
)
SELECT Slice, ROUND(Percent, 2) AS Percentage
FROM SL
ORDER BY Percentage DESC



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

Re: [sqlite] Percentage instead of count in GROUP BY

[Cecil Westerhof]

2016-08-10 17:13 GMT+02:00 Cecil Westerhof :

> Question 2:
>
>> Not sure how you want to slice data, I am assuming you mean you want to
>> know how many values (as in percent of total) falls within each slice? If
>> so, this should work:
>>
>
> ​Yes, that is what I mean. I am going to try it out.​
>
>
>
>
>> WITH ST(cAll) AS (
>> SELECT COUNT(*) FROM vmstat
>> ), SL(Slice) AS (
>> SELECT (CAST(((100 * COUNT(*) / ST.cAll) / 5) AS INT) * 5)
>>  FROM vmstat, ST
>> )
>> SELECT Slice||' - '||(Slice+5), (100 * COUNT(*) / ST.cAll) AS Perc
>>   FROM SL, ST
>>  GROUP BY Slice
>>
>> [Might need a little fiddling to produce the output exactly how you want
>> it, but the basics should be clear]
>>
>
​Does not work. SL gives a table that has exactly one element.

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

Re: [sqlite] Percentage instead of count in GROUP BY

[Cecil Westerhof]

2016-08-10 11:43 GMT+02:00 R Smith :

> On 2016/08/10 11:20 AM, Cecil Westerhof wrote:
>
>> At the moment I have:
>> SELECT   idletime, COUNT(*)
>> FROM vmstat
>> GROUP BY idletime
>>
>> But because there are about 400.000 records (and counting) it is not the
>> most convenient information. Instead of the number of records I would like
>> to have the percentage of the records. Is this possible?
>>
>> Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.
>>
>>
> Question 1:
> WITH ST(cAll) AS ( SELECT COUNT(*) FROM vmstat )
> SELECT idletime, (100 * COUNT(*) / ST.cAll) AS Perc
>   FROM vmstat, ST
>  GROUP BY idletime
>

​Has the same problem as the solution of Clemens: delivers only zeros. But
rewritten like this works:
WITH ST(onePercent) AS (SELECT COUNT(*) / 100.0 FROM vmstat)
SELECT idletime, COUNT(*), COUNT(*) / ST.onePercent AS Percentage
FROM vmstat, ST
GROUP BY idletime
ORDER BY Percentage DESC
​


​And this solution is of-course better, because now the total count is only
fetched once. (I think.) Not really important in this case, but nice to
have.​

Question 2:
> Not sure how you want to slice data, I am assuming you mean you want to
> know how many values (as in percent of total) falls within each slice? If
> so, this should work:
>

​Yes, that is what I mean. I am going to try it out.​




> WITH ST(cAll) AS (
> SELECT COUNT(*) FROM vmstat
> ), SL(Slice) AS (
> SELECT (CAST(((100 * COUNT(*) / ST.cAll) / 5) AS INT) * 5)
>  FROM vmstat, ST
> )
> SELECT Slice||' - '||(Slice+5), (100 * COUNT(*) / ST.cAll) AS Perc
>   FROM SL, ST
>  GROUP BY Slice
>
> [Might need a little fiddling to produce the output exactly how you want
> it, but the basics should be clear]
>

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

[Jim Callahan]

The issue is not simply recompiling the CLI for 64-bit; I assume you want
the tested reliability of SQLite.

"The [SQLite] project has 787 times as much test code and test scripts" as
it does source code for the executable.
https://www.sqlite.org/testing.html


Running the exact same tests would not be enough, because you (and I) would
want special tests for larger than 4 GB
RAM. So, additional tests would have to be developed.

So, I see three alternate solutions:

1. Find a corporate sponsor to fund development of 64-bit SQLite CLI

2. Remove some tables from memory (though it sounds like your difficulties
are caused
by recursion rather than the size of the raw data)

3. Use SQLite for persistent storage but move the in memory (tree
navigation) operations to another (open source)  language
such as C, Java, Python or R (or the new Julia language that is approaching
version 1.0)  that has interfaces for SQLite
and a 64-bit build for Windows.  You will probably need another language to
display the output anyway why not take
advantage of Python, R or Julia?

Even if you move to another language, you may find that your problem is
recursion.
In my experience, computer science textbooks give elegant examples using
recursion,
but then say the solution is not scale-able and give a less elegant
solution using iterative techniques.

Jim Callahan
Data Scientist
Orlando, FL


On Tue, Aug 9, 2016 at 10:31 AM, 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] 64-bit SQLite3.exe

[Andy Ling]

You still seem to have missed the point that several have made.

There are lots of people still using 32bit Windows. They cannot use a 64bit 
SQLite3.exe.
Therefore the 32bit one has to stay. The 32bit one will work on a 64bit OS, 
with restrictions.
The number of people who hit those restrictions is quite small. You are the 
only person
I can remember posting about hitting one. Most that do can build their own 
64bit exe.

So I guess currently the team have decided it isn't worth the effort to provide 
both exes
to support those few people. Maybe this will change if the number increase.

Regards

Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Rousselot, Richard A
Sent: Wed 10 August 2016 15:43
To: SQLite mailing list
Subject: Re: [sqlite] 64-bit SQLite3.exe

First of all thanks for the discussion.  Special thanks out to DD. ;)

I will just summarize my main view.
* CTEs can easily exceed memory limits of the 32-bit SQLite3.exe.  So I have a 
valid need and am not on a crusade to kill 32-bits.  Telling me that other 
people don't have my problems is irrelevant, to me. :)

Response to all other banter; in good humor.
* The rest of the arguments about Microsoft currently shipping software that is 
32-bit, is a red herring.  I bet if you tried hard enough, you could find code 
from Windows 3.1 in Windows 10.  That wasn’t a properly thought out design 
decision.  That is laziness and frugality (possibly stupidity) on Microsoft's 
part.  I once read that MS would hire the developer of ReactOS so they could 
explain how their own operating system works.   (I digress)
* If I did figure out how to build the 64-bit executable, I guarantee, that it 
would be a catastrophe.  I can't even figure out how to post on Nabble for 
freaks sake. ;)
* I can still buy a VW bug designed in the 30s; does that mean that all new 
engines developed should have to fit in the back of that thing?
* I acquiesce on all other points, I am weary.

Finally, I just want to say I really appreciate the work that is put in to 
SQLite.  It is one of many tools I use on a day to day basis but it is the only 
one where I actively watch the discussions on a mailing list.  The regulars 
here are thoughtful, intelligent and infinitely patient.  I learn new things 
every day watching you guys and for that I thank you.

Richard

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


> Even on a 64-bit processor, there’s usually no reason to run 64-bit
> Windows unless you have more than 4 GB of RAM, a threshold we didn’t
> pass very long ago.

Yes, please remember to keep the "addressable memory limits" linkage to 
"processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need 64-bit 
processors or 64-bit Operating systems to be able to address more than 4 GB of 
physical RAM.  In fact that there are 32-bit versions of Windows (NT 3.5, 4.0, 
2000, XP, 2003 etc) which have been compiled without this artificially imposed 
limitation.  You pay more for "properly written and compiled" software however 
because, well, it is easy to do stupid things and impose stupid limits for no 
reason and you need higher wattage (therefore more expensive people) if you 
want software that is not bounded by a crapload of inane if not brain-dead) 
design decisions.  It is also quite profitable to claim that your prior 
incompetencies were to blame on "something else" and if everyone would just 
spend a boatload of money and replace all the defective crap we sold them with 
a new boatload of defective crap (every six months), a very few of the 
deliberately imposed limitations will be removed (plus we make yet another 
boatload of money by ripping off the customer).

> Or maybe you’d like to look to a less legacy-bound company?  Say,
> Google, who ships Chrome still built as 32-bit, originally for
> compatibility with 32-bit NSAPI plugins.  Since they dropped that, I
> can only guess why they’re still building 32-bit binaries, and that
> guess is that with the tab-per-process isolation, no single tab needs more 
> than 4 GB of VM space.

Or they are using defective compilers (primary supplier in that field is 
Microsoft) that cannot switch memory models without re-writing the code.




___
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 

Re: [sqlite] Percentage instead of count in GROUP BY

[Cecil Westerhof]

2016-08-10 11:33 GMT+02:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > SELECT   idletime, COUNT(*)
> > FROM vmstat
> > GROUP BY idletime
> >
> > But because there are about 400.000 records (and counting) it is not the
> > most convenient information. Instead of the number of records I would
> like
> > to have the percentage of the records. Is this possible?
>
> SELECT idletime, COUNT(*) / (SELECT COUNT(*) FROM vmstat) * 100 ...
>

​That would only give zeros. What does work is:
COUNT(idletime) / (SELECT COUNT(*) / 100.0 FROM vmstat) AS Percentage

-- 
Cecil Westerhof
___
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]

First of all thanks for the discussion.  Special thanks out to DD. ;)

I will just summarize my main view.
* CTEs can easily exceed memory limits of the 32-bit SQLite3.exe.  So I have a 
valid need and am not on a crusade to kill 32-bits.  Telling me that other 
people don't have my problems is irrelevant, to me. :)

Response to all other banter; in good humor.
* The rest of the arguments about Microsoft currently shipping software that is 
32-bit, is a red herring.  I bet if you tried hard enough, you could find code 
from Windows 3.1 in Windows 10.  That wasn’t a properly thought out design 
decision.  That is laziness and frugality (possibly stupidity) on Microsoft's 
part.  I once read that MS would hire the developer of ReactOS so they could 
explain how their own operating system works.   (I digress)
* If I did figure out how to build the 64-bit executable, I guarantee, that it 
would be a catastrophe.  I can't even figure out how to post on Nabble for 
freaks sake. ;)
* I can still buy a VW bug designed in the 30s; does that mean that all new 
engines developed should have to fit in the back of that thing?
* I acquiesce on all other points, I am weary.

Finally, I just want to say I really appreciate the work that is put in to 
SQLite.  It is one of many tools I use on a day to day basis but it is the only 
one where I actively watch the discussions on a mailing list.  The regulars 
here are thoughtful, intelligent and infinitely patient.  I learn new things 
every day watching you guys and for that I thank you.

Richard

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


> Even on a 64-bit processor, there’s usually no reason to run 64-bit
> Windows unless you have more than 4 GB of RAM, a threshold we didn’t
> pass very long ago.

Yes, please remember to keep the "addressable memory limits" linkage to 
"processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need 64-bit 
processors or 64-bit Operating systems to be able to address more than 4 GB of 
physical RAM.  In fact that there are 32-bit versions of Windows (NT 3.5, 4.0, 
2000, XP, 2003 etc) which have been compiled without this artificially imposed 
limitation.  You pay more for "properly written and compiled" software however 
because, well, it is easy to do stupid things and impose stupid limits for no 
reason and you need higher wattage (therefore more expensive people) if you 
want software that is not bounded by a crapload of inane if not brain-dead) 
design decisions.  It is also quite profitable to claim that your prior 
incompetencies were to blame on "something else" and if everyone would just 
spend a boatload of money and replace all the defective crap we sold them with 
a new boatload of defective crap (every six months), a very few of the 
deliberately imposed limitations will be removed (plus we make yet another 
boatload of money by ripping off the customer).

> Or maybe you’d like to look to a less legacy-bound company?  Say,
> Google, who ships Chrome still built as 32-bit, originally for
> compatibility with 32-bit NSAPI plugins.  Since they dropped that, I
> can only guess why they’re still building 32-bit binaries, and that
> guess is that with the tab-per-process isolation, no single tab needs more 
> than 4 GB of VM space.

Or they are using defective compilers (primary supplier in that field is 
Microsoft) that cannot switch memory models without re-writing the code.




___
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

[Warren Young]

On Aug 10, 2016, at 6:03 AM, Keith Medcalf  wrote:
> 
>> Even on a 64-bit processor, there’s usually no reason to run 64-bit
>> Windows unless you have more than 4 GB of RAM, a threshold we didn’t pass
>> very long ago.
> 
> Yes, please remember to keep the "addressable memory limits" linkage to 
> "processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need 
> 64-bit processors or 64-bit Operating systems to be able to address more than 
> 4 GB of physical RAM.  In fact that there are 32-bit versions of Windows (NT 
> 3.5, 4.0, 2000, XP, 2003 etc) which have been compiled without this 
> artificially imposed limitation.  You pay more for "properly written and 
> compiled" software however because, well, it is easy to do stupid things and 
> impose stupid limits for no reason and you need higher wattage (therefore 
> more expensive people) if you want software that is not bounded by a crapload 
> of inane if not brain-dead) design decisions.

You must be talking about PAE, which is an unmitigated hack, in the dirtiest 
sense of that word:

  https://en.wikipedia.org/wiki/Physical_Address_Extension

As you hint, some OSes allow individual apps to allocate extra RAM via PAE — 
UnixWare was one such — but due to the way PAE works, it can never be more than 
8 GiB per process at a given time.

The OS *could* page in and out 4 GiB segments to give a single application the 
run of the whole 64 GiB maximum space PAE allows, but I don’t know of any OS 
that does this.

Linux in particular doesn’t let individual applications use PAE to access more 
than 3 GiB of VM space, with the standard 3/1 user/kernel split.  Instead, if 
you have more than 4 GiB of RAM in the machine and are running a PAE kernel, it 
will let you have multiple programs *collectively* using more than 4 GiB of VM 
space.  That’s not going the help the OP.

Quoting Wikipedia, “…regular application software continues to use instructions 
with 32-bit addresses and (in a flat memory model) is limited to 4 gigabytes of 
virtual address space…no single regular application can access [all 64 GiB] 
simultaneously.”

I believe the situation is essentially the same on PAE-enabled versions of 
Windows as on Linux.

It is also the case that most machines that shipped with 32-bit Intel 
processors either didn’t have enough slots to allow > 4 GiB of RAM or didn’t 
have BIOS/EFI/chipset support for that much RAM if you did have the slots for 
it.  And why should they have done?  It just adds cost with a low chance that 
the user can make use of it, so that capability only showed up in high-dollar 
machines.

PAE is also not restricted to non-Windows OSes.  Microsoft simply chose not to 
support it on the non-Server versions of Windows, but that is essentially a 
market segmentation issue, not a technical one.

PAE’s time is long past.  64-bit is the proper solution today.

> It is also quite profitable to claim that your prior incompetencies were to 
> blame on "something else" and if everyone would just spend a boatload of 
> money and replace all the defective crap we sold them with a new boatload of 
> defective crap

So…the software development industry is at fault for not building all their 
apps for 64-bit from the start, going back to the 1950s?  Just think, you could 
be booting your Broadwell i7 into UNIX V1 today instead of this bloated Linux 
stuff!  What a great thing that would be!

>> Or maybe you’d like to look to a less legacy-bound company?  Say, Google,
>> who ships Chrome still built as 32-bit, originally for compatibility with
>> 32-bit NSAPI plugins.  Since they dropped that, I can only guess why
>> they’re still building 32-bit binaries, and that guess is that with the
>> tab-per-process isolation, no single tab needs more than 4 GB of VM space.
> 
> Or they are using defective compilers (primary supplier in that field is 
> Microsoft) that cannot switch memory models without re-writing the code.

Chrome runs just fine as a 64-bit executable.  It just wasn’t the default 
version until recently.  If you’ve been using it for a while, you will still be 
on the 32-bit version, as I am here.  You have to do a full reinstall to switch 
to the 64-bit version, which is now the default for new users on 64-bit systems.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Setting temp location with Python

[Jonathan Moules]

Hi List,
   I'm using Python's sqlite3 library to access a SQLite db. I'd like to set 
the location for the temporary databases in a platform agnostic fashion (*nix 
or Windows).

This page - https://www.sqlite.org/tempfiles.html - gives a number of options, 
but the only good one I can see for using it via Python would be the PRAGMA.
But the docs for PRAGMA temp_store_directory; ( 
https://www.sqlite.org/pragma.html#pragma_temp_store_directory ) say this is 
deprecated.

So what's the recommended way to achieve this?

Thanks,
Jonathan

___
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

[Keith Medcalf]

> Even on a 64-bit processor, there’s usually no reason to run 64-bit
> Windows unless you have more than 4 GB of RAM, a threshold we didn’t pass
> very long ago.
 
Yes, please remember to keep the "addressable memory limits" linkage to 
"processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need 64-bit 
processors or 64-bit Operating systems to be able to address more than 4 GB of 
physical RAM.  In fact that there are 32-bit versions of Windows (NT 3.5, 4.0, 
2000, XP, 2003 etc) which have been compiled without this artificially imposed 
limitation.  You pay more for "properly written and compiled" software however 
because, well, it is easy to do stupid things and impose stupid limits for no 
reason and you need higher wattage (therefore more expensive people) if you 
want software that is not bounded by a crapload of inane if not brain-dead) 
design decisions.  It is also quite profitable to claim that your prior 
incompetencies were to blame on "something else" and if everyone would just 
spend a boatload of money and replace all the defective crap we sold them with 
a new boatload of defective crap (every six months), a very few of the 
deliberately imposed limitations will be removed (plus we make yet another 
boatload of money by ripping off the customer).

> Or maybe you’d like to look to a less legacy-bound company?  Say, Google,
> who ships Chrome still built as 32-bit, originally for compatibility with
> 32-bit NSAPI plugins.  Since they dropped that, I can only guess why
> they’re still building 32-bit binaries, and that guess is that with the
> tab-per-process isolation, no single tab needs more than 4 GB of VM space.

Or they are using defective compilers (primary supplier in that field is 
Microsoft) that cannot switch memory models without re-writing the code.




___
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

[Dominique Devienne]

On Wed, Aug 10, 2016 at 11:47 AM, Warren Young  wrote:

> On Aug 10, 2016, at 3:22 AM, J Decker  wrote:
> > I'd think many of you would know 64 bit
> > mode has more general purpose registers to carry values and the default
> > calling ABI is improved to be more of a register centric model.
>
> SQLite is largely I/O bound.
>

With CTEs, you can go compute-bound.
OP already mentioned going over the 2GB limit for complex CTEs.
So that's no longer the whole truth. --DD
___
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

[Christian Schmitz]

> I agree, but now you're talking getting the amalgamation/repo and stuff (also 
> not exceedingly difficult, but granted will take a lot longer), he was 
> however not claiming having difficulty with that, the difficulty was only the 
> "learning how to make a build script" and that (in SQLite's case) is well 
> documented and can be done in minutes.


I'd also like to have prebuild console app and dlls for various platforms, so 
you don't need to build yourself just to try something.
Or when using official binaries, we could link to them and tell clients to 
update them independent of our software later.

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/

___
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

[R Smith]

On 2016/08/10 12:01 PM, Dominique Devienne wrote:

On Wed, Aug 10, 2016 at 11:52 AM, R Smith  wrote:


I disagree on the second point though - it isn't false. Have you set up a
script for building SQLite yet? It really is that easy.


A non-developer downloading 100's of MBs of compiler/IDE and trying to
figure it out for the very first time,
then getting the amalgamation (or worse the repo: fossile, etc...), using
the proper command line flags,
troubleshooting what's wrong, etc... 30 min, that's completely false for
people w/o that experience for 99% of people, if not more.


I agree, but now you're talking getting the amalgamation/repo and stuff 
(also not exceedingly difficult, but granted will take a lot longer), he 
was however not claiming having difficulty with that, the difficulty was 
only the "learning how to make a build script" and that (in SQLite's 
case) is well documented and can be done in minutes.


___
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

[Dominique Devienne]

On Wed, Aug 10, 2016 at 11:52 AM, R Smith  wrote:

> I disagree on the second point though - it isn't false. Have you set up a
> script for building SQLite yet? It really is that easy.
>

A non-developer downloading 100's of MBs of compiler/IDE and trying to
figure it out for the very first time,
then getting the amalgamation (or worse the repo: fossile, etc...), using
the proper command line flags,
troubleshooting what's wrong, etc... 30 min, that's completely false for
people w/o that experience for 99% of people, if not more.
___
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

[R Smith]

On 2016/08/10 11:38 AM, Dominique Devienne wrote:

On Wed, Aug 10, 2016 at 11:11 AM, R Smith  wrote:

On 2016/08/10 7:39 AM, Rousselot, Richard A wrote:

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.//...


You spend several posts and a multitude of lines explaining how useful a
64-bit SQLite.exe would be for you - and then claim knowing how to make one
isn't a useful skill - LoL - There's documentation, it will take you less
than 30 minutes to set up a maker script (assuming you are completely green
on the subject, else probably 5 minutes).


Now that's both condescending and false IMHO...

I think people on this list who don't care or need it, and those not in a
position to do it or not (which leaves only DRH basically), so stop this
bashing of a simple and legitimate (again IMHO) request.

Lets move on. --DD


Apologies to the OP and Dominique both if it sounded condescending - not 
meant that way (and very definitely not meant to be bashful), I found it 
genuinely amusing.


I disagree on the second point though - it isn't false. Have you set up 
a script for building SQLite yet? It really is that easy.



___
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 10, 2016, at 3:22 AM, J Decker  wrote:
> 
> I'd think many of you would know 64 bit
> mode has more general purpose registers to carry values and the default
> calling ABI is improved to be more of a register centric model.

SQLite is largely I/O bound.

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

Re: [sqlite] Percentage instead of count in GROUP BY

[R Smith]

On 2016/08/10 11:20 AM, Cecil Westerhof wrote:

At the moment I have:
SELECT   idletime, COUNT(*)
FROM vmstat
GROUP BY idletime

But because there are about 400.000 records (and counting) it is not the
most convenient information. Instead of the number of records I would like
to have the percentage of the records. Is this possible?

Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.



Question 1:
WITH ST(cAll) AS ( SELECT COUNT(*) FROM vmstat )
SELECT idletime, (100 * COUNT(*) / ST.cAll) AS Perc
  FROM vmstat, ST
 GROUP BY idletime


Question 2:
Not sure how you want to slice data, I am assuming you mean you want to 
know how many values (as in percent of total) falls within each slice? 
If so, this should work:


WITH ST(cAll) AS (
SELECT COUNT(*) FROM vmstat
), SL(Slice) AS (
SELECT (CAST(((100 * COUNT(*) / ST.cAll) / 5) AS INT) * 5)
 FROM vmstat, ST
)
SELECT Slice||' - '||(Slice+5), (100 * COUNT(*) / ST.cAll) AS Perc
  FROM SL, ST
 GROUP BY Slice

[Might need a little fiddling to produce the output exactly how you want 
it, but the basics should be clear]


** Needs SQLite 3.8 or later

___
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

[Dominique Devienne]

On Wed, Aug 10, 2016 at 11:11 AM, R Smith  wrote:
>
> On 2016/08/10 7:39 AM, Rousselot, Richard A wrote:
>>
>> 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.//...
>>
>
> You spend several posts and a multitude of lines explaining how useful a
> 64-bit SQLite.exe would be for you - and then claim knowing how to make one
> isn't a useful skill - LoL - There's documentation, it will take you less
> than 30 minutes to set up a maker script (assuming you are completely green
> on the subject, else probably 5 minutes).
>

Now that's both condescending and false IMHO...

I think people on this list who don't care or need it, and those not in a
position to do it or not (which leaves only DRH basically), so stop this
bashing of a simple and legitimate (again IMHO) request.

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

Re: [sqlite] Percentage instead of count in GROUP BY

[Clemens Ladisch]

Cecil Westerhof wrote:
> SELECT   idletime, COUNT(*)
> FROM vmstat
> GROUP BY idletime
>
> But because there are about 400.000 records (and counting) it is not the
> most convenient information. Instead of the number of records I would like
> to have the percentage of the records. Is this possible?

SELECT idletime, COUNT(*) / (SELECT COUNT(*) FROM vmstat) * 100 ...

> how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.

Instead of 100, multiply with 20, then cast to integer, and multiply with 5.


Regards,
Clemens
___
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

[J Decker]

I'm actually kind of surprised more people aren't like 'ya, why isn't 64
bit just available?' ( *pounds on tables* "We Want 64 Bit!", no? )
being fairly low level developers I'd think many of you would know 64 bit
mode has more general purpose registers to carry values and the default
calling ABI is improved to be more of a register centric model.
Both windows and linux 64 bit programs do run a hair faster than their same
32 bit builds.  It's not like night and day and I'd be surprised if it's as
high as 5% gain. Plus, who doesn't have more than 2G of memory and a 64 bit
system that's already responded?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Percentage instead of count in GROUP BY

[Cecil Westerhof]

At the moment I have:
SELECT   idletime, COUNT(*)
FROM vmstat
GROUP BY idletime

But because there are about 400.000 records (and counting) it is not the
most convenient information. Instead of the number of records I would like
to have the percentage of the records. Is this possible?

Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.

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

Re: [sqlite] Odd behavior when using ORDER BY on a VIEW with a LIMIT clause

[Clemens Ladisch]

jef wrote:
> CREATE VIEW v1  AS SELECT x FROM t1 ORDER BY y DESC;
> CREATE VIEW vv1 AS SELECT x FROM v1 LIMIT 3;
>
> SELECT x FROM vv1 ORDER BY x;
>
> With vv1, the query planner/optimizer seems to push the LIMIT clause
> all the way to the "end" of the query. Is this the desired behavior?

A query is not guaranteed to have any specific sorting unless it is
using ORDER BY.  So for LIMIT to make sense, it must be in the same
query as the ORDER BY.


Regards,
Clemens
___
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

[R Smith]

On 2016/08/10 7:39 AM, Rousselot, Richard A wrote:


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.//...


You spend several posts and a multitude of lines explaining how useful a 
64-bit SQLite.exe would be for you - and then claim knowing how to make 
one isn't a useful skill - LoL - There's documentation, it will take you 
less than 30 minutes to set up a maker script (assuming you are 
completely green on the subject, else probably 5 minutes).


That said, I would like to add my vote to adding a 64-bit SQLite3.exe - 
not so much because of the few people who may need it - but because it 
is the new thing, it is the way the World is moving. Sure people still 
need 32bit, but gradually it's changing over. I am sure pretty soon, if 
not the next release of Windows, things will have moved to 64-bit only. 
Apple is already there. (Plus refer the Android tools note by J. Decker) 
etc.


SQLite's mindset (If I may use the collective) to my understanding has 
always been that of a pioneer. Unlike Richard (the OP) I use the 32 bit 
CLI simply because it's available and works fine (meaning I have no 
reason to roll my own yet), but I will happily use the 64bit if it 
becomes available, which probably lots of others like me would do, and 
it will widen the user base and go a long way towards sussing out 
possible 64bit bugs/improvements and the like.




Ryan

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

Re: [sqlite] SQLite 3.12.2 SCALARS NOT VISIBLE TO CORRELATED SUBQUERY LIMIT CLAUSE

[Clemens Ladisch]

petern wrote:
> I am eager to read comments. I would call this a bug report rather than 
> feature request
> since the expected result is reasonable and the choice of subquery scalar 
> scoping
> for LIMIT seems arbitrary.
>
> Is there some documentation I may read which explains this design choice
> for the LIMIT clause of correlated subqueries?

No results from a similar earlier report:
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2013-April/045633.html


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

[sqlite] Odd behavior when using ORDER BY on a VIEW with a LIMIT clause

[jef]

In simplified terms, I am trying to select the top N name/value pairs
according to value, then sort those N rows by name.

CREATE TABLE t1(x TEXT,y INT);
INSERT INTO t1 VALUES('a',1);
INSERT INTO t1 VALUES('b',2);
INSERT INTO t1 VALUES('c',3);
INSERT INTO t1 VALUES('d',4);
INSERT INTO t1 VALUES('e',5);
CREATE VIEW v1  AS SELECT x FROM t1 ORDER BY y DESC;
CREATE VIEW vv1 AS SELECT x FROM v1 LIMIT 3;
CREATE VIEW vv2 AS SELECT x FROM (SELECT x FROM v1 LIMIT 3) WHERE 1;

SELECT x FROM vv1;-- e,d,c -- OK --
SELECT x FROM vv1 ORDER BY x; -- a,b,c -- ?? --
SELECT x FROM vv2;-- e,d,c -- OK --
SELECT x FROM vv2 ORDER BY x; -- c,d,e -- OK --

Why does vv1 behave differently than vv2? With vv1, the query
planner/optimizer seems to push the LIMIT clause all the way to the "end"
of the query. Is this the desired behavior? With vv1, it seems wrong to
return two different sets of rows simply based on an ORDER BY of the view.
vv2 works as expected but it seems wrong to wrap the LIMIT-ed view as a
subquery and apply a trivial WHERE (removing the WHERE 1 gives the same
erronious results as vv1). Is this a bug or am I missing something and this
is the proper behavior?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 3.12.2 SCALARS NOT VISIBLE TO CORRELATED SUBQUERY LIMIT CLAUSE

[petern]

Pasted from comments in one my database project files:
-
--SQLite 3.12.2 SCALARS NOT VISIBLE TO CORRELATED SUBQUERY LIMIT CLAUSE.

--Observe that "s.off" is a scalar constant with respect to subquery "v" 's
scope in the following example.

with data(v) as (values ('A'),('B'),('C')), selector(off) as (values
(2),(1))
select *,(select v from data limit 1 offset s.off)v from selector s;

--OUTPUT:  'Error: no such column: s.off'

--Expected output would be the two rows {(2,B),(1,A)}, with column names
(off,v).

--It is worthwhile to note that column expressions, WHERE clauses, and
HAVING clauses
--have the expected correlated subquery scalar scoping including at
arbitrary depth of
--nested correlated FROM clause. It is only the LIMIT and ORDER BY clauses
which
--are mysteriously globally scoped with respect to outer scalars.
-

Yes, I do appreciate my example is contrived. But the place in my code
where the
expected capability would have been better than the workaround data caching
table, trigger,
and view 'warts' is not contrived or trivial.   The example is merely the
briefest bug/QA
regression case I could think of to write in the comment above the ugly
workaround.

I am eager to read comments. I would call this a bug report rather than
feature request
since the expected result is reasonable and the choice of subquery scalar
scoping
for LIMIT seems arbitrary.

Is there some documentation I may read which explains this design choice
for the LIMIT clause of correlated subqueries?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4/5 ranking function differences

[Dan Kennedy]

On 08/10/2016 12:47 AM, Jan Berkel wrote:

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.


If an auxiliary fts5 function is bound to the "rank" column, it cannot 
be passed extra parameters from the outer query. The reason is that when 
you use "ORDER BY rank", the virtual table module sorts the rows before 
returning them to the SQLite core. And of course, the virtual table 
module knows nothing about the outer query - it only knows that SQLite 
requested a set of matching rows in sorted order.


You could run a regular database query from within the auxiliary 
function implementation. Not sure how that would perform though - 
perhaps similarly to the FTS3/4 approach, perhaps a little better.


Or, you could add the "weight" as an UNINDEXED column to the fts5 
documents table. Then access it via the auxiliary function xColumn() API.


Dan.



___
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

[David Empson]

> On 10/08/2016, at 5:50 PM, Rousselot, Richard A 
>  wrote:
> 
> 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?

You are missing an important point: it isn’t only the processor architecture, 
but the Windows installation architecture which matters.

Windows Vista, 7, 8, 8.1 and 10 are/were all available in 32-bit. All of them 
are still supported by Microsoft.

There are a lot of PCs with 64-bit processors running 32-bit Windows, because 
that is how they were supplied or originally set up. I’m not talking ten year 
old computers: my work PC is a 2011 HP with a 64-bit Core i5 that was supplied 
with 32-bit Windows 7 (we’ve upgraded these PCs to Windows 10, but it is still 
32-bit Windows 10). There will be many newer 64-bit PCs also running 32-bit 
Windows.

Numbers will dwindle over time as PCs are replaced (or the occasional OS 
reinstall), but there is probably still a significant number of PCs running a 
supported 32-bit Windows.

If the only distributed build of sqlite3.exe was 64-bit, I expect it would 
inconvenience a fair number of people on 32-bit Windows who use an up-to-date 
version of SQLite including the command line tools, but can't build it 
themselves. (I can build it, so wouldn’t mind if this happened.)

The 32-bit build runs on 64-bit Windows, and is only a limit for those who need 
to do things with the command line tool that require more than 2 GB of memory.

Having both 32-bit and 64-bit versions would be ideal, probably with a plan to 
phase out the 32-bit version, but it would mean more work for the SQLite 
developers in the meantime.

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

Anything that old is not supported by SQLite 3.

___
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

[J Decker]

On Tue, Aug 9, 2016 at 10:08 PM, David Empson  wrote:

>
> > On 10/08/2016, at 3:30 PM, Rousselot, Richard A  centurylink.com> 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).
>
>
If you're going that way; Android just pulled x86 support for their dev
tools.  Turns out noone in QA had a 32 bit computer; and when they posted a
message about it there wasn't a lot of 'no wait! I have x86!' actually not
one reply; just me saying 'ya, I can see most development machines are 64
bit; although; you shuold provide a patch now; but retooling the QA dept
for 10% of the market doesn't really make sense
see their numbers aren't 90% 32 bit, but rather 90% 64 bit, so really it
shuld be the 64 bit that's provided,  and MAYBE the 32 bit in addition to
it.


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

[Dominique Devienne]

On Tue, Aug 9, 2016 at 4:31 PM, 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.
>

+1. You make a good point, and researched the issue. --DD

PS: Not sure why some want everyone to turn into programmers and a least
people compiling code...
___
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 11:39 PM, Rousselot, Richard A 
 wrote:
> compiling a 64-bit binary is not a useful skill

It keeps me fed pretty well. :)

> Your 32-bit Mac is not windows machine

What, you think Intel only made Core Solos for Apple?

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

It would be interesting to get data on 32-bit vs 64-bit Windows installs.  I 
wouldn’t be surprised if it’s still more than 50% 32-bit.  It wasn’t that long 
ago that XP installs finally dropped below 50%, and a huge chunk of those were 
32-bit.

Even on a 64-bit processor, there’s usually no reason to run 64-bit Windows 
unless you have more than 4 GB of RAM, a threshold we didn’t pass very long ago.

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

You mean like Visual Studio 2015, released less than a year ago today?  Yes, 
the VS IDE remains 32-bit, even though the underlying compilers will build 
64-bit executables.

Maybe you’d like to talk about PowerPoint?  The version currently on my 64-bit 
Windows 10 machine runs as 32-bit.

Or maybe you’d like to look to a less legacy-bound company?  Say, Google, who 
ships Chrome still built as 32-bit, originally for compatibility with 32-bit 
NSAPI plugins.  Since they dropped that, I can only guess why they’re still 
building 32-bit binaries, and that guess is that with the tab-per-process 
isolation, no single tab needs more than 4 GB of VM space.

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

…And teaches you a useful skill that you may use later.

You say you don’t trust software from others.  What is a more trustworthy way 
to get executables than those you have built yourself from source code?

> The 64-bit version will probably shave an hour off my many 8 hour processing 
> jobs.

As a rule, 64-bit software runs a bit slower than 32-bit software on Intel 
CPUs.I/O channels all run the same speed, so for anything not CPU-bound, 
there is no advantage.  64-bit executables are a fair bit larger, which means 
you have more cache misses.  Unless you’re running something able to be highly 
register-optimized, you don’t get the only real speed advantage of Intel 64-bit 
CPUs, that being access to more registers.

> That will add up very quickly for me.

I’d love to study your benchmarks after you manage to get a 64-bit executable.

>>> 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.
>> 
> This doesn't make sense, what does a 3rd party binary based on a dll have to 
> do with a command line tool?

I’m saying that the vast majority of SQLite users are not using the Windows EXE 
downloaded from sqlite.org, therefore the answer to your question asking how 
anyone could use powerful features like CTE and > 2 GB of RAM is that the vast 
majority of SQLite users aren’t affected by the lack of a 64-bit Windows 
executable.

If I run sqlite3 on my Mac, I get a 64-bit executable shipped by Apple.

If I run an app on my iPhone and it opens a SQLite DB, it runs a 64-bit version 
of SQLite also shipped by Apple.

Those two alone account for roughly a billion of the installations of SQLite.

> Are you saying that no one needs the command line tool so its development 
> should be abandoned?

What’s with the hyperbole?  100,000 users (my informed guess) is not zero; it 
is merely small compared to the total SQLite user base.  That small user base 
is further reduced by the number of those users who would actually benefit from 
a 64-bit Windows executable.

> Why spend time making a 32-bit version for the minority of people still 
> running 8 year old equipment?

You’re making an unwarranted assumption that the last 32-bit Windows 
installation was done in 2008 just because that was the last 32-bit Intel CPU 
introduction date.  (And the latter turns out to be incorrect anyway.)

Here’s an Atom E620, introduced in Q3 2010, 32-bit only, which you can still 
buy today:

  https://www.amazon.com/dp/B0137IIR88/ref=cm_sw_r_tw_dp_x_dxSQxb2J53HJC

Here’s a mini PC rocking a 32-bit Intel processor a couple of years newer than 
that one, still commercially available:

  https://www.amazon.com/dp/B00AXK56M4/ref=cm_sw_r_tw_dp_x_3DSQxbD1Q2K25

Here’s Intel’s complete 32-bit-only CPU product table, limited to current 
products only:

  http://goo.gl/jw1vyA

I get 122 results today.

And this totally ignores all the 64-bit Intel based boxes and VMs still running 
32-bit Windows for whatever reason.