Re: [sqlite] 64-bit SQLite3.exe

[Scott Robison]

On Thu, Aug 11, 2016 at 6:37 PM, Warren Young  wrote:

> On Aug 11, 2016, at 3:19 PM, Scott Robison 
> wrote:
> >
> > I think you guys are just talking past each other.
>
> Well, at least one of us isn’t communicating clearly, that’s certain.  I
> just don’t yet know if it’s me, him, or both of us. :)
>

I'm just glad I never have problems expressing myself klearly.


> > Windows versions that
> > support PAE have the Address Windowing Extensions (AWE) which allows a
> > single process to access more than 4 GiB total in a single process
>
> From what I can see, AWE and PAE are orthogonal, but AWE without PAE
> doesn’t let you get beyond 4 GiB in a single process on IA-32.
>

Correct. AWE depends on PAE to enable windowed access into a memory space >
4 GiB. I've never used it so I don't know how much utility it might have
outside PAE. https://en.wikipedia.org/wiki/Address_Windowing_Extensions
probably gives a better summary that I could hope to.


> But — and this may be where Keith was trying to go — a 32-bit app running
> on a 64-bit OS doesn’t need PAE because the host system does support more
> than 4 GiB of virtual memory.  I have yet to see anything that says that
> AWE on 64-bit Windows couldn’t give a 32-bit app access to some of the VM
> beyond 4 GiB, even on consumer versions of Windows.
>

What I assumed was being said was that "sqlite.exe could access more than 2
GiB even on a 32 bit OS thanks to PAE" which is a true statement. I very
well may not have read something carefully enough and missed something
though.


> If so, that’s what I was trying to get at with my request that he give a
> reference to the specific technology he’s talking about, instead of
> describing it in prose.
>
> > That being said, I don't think it is a reasonable or practical thing to
> > expect of cross platform source code like SQLite to use such a platform
> > specific API.
>
> Couldn’t it be abstracted behind a layer that used mmap() + tmpfs to pull
> off a similar trick on other 64-bit OSes?  (I’m not talking about > 4 GiB
> on 32-bit OSes here.)
>

Perhaps, but I was only addressing the > 4 GiB on a 32-bit OS scenario. It
was a great solution for apps that needed to process a lot of data in
memory at one time back before AMD64 was a thing.

http://stackoverflow.com/questions/1709243/how-to-use-more-than-3-gb-in-a-process-on-32-bit-pae-enabled-linux-app
suggests that a 32-bit Linux system could use mmap to do the equivalent bit
of functionality.


> It’d be a lot of work just to avoid rebuilding for 64-bit, but maybe it
> would be an interesting project for someone.  Like a master’s university
> project, maybe.
>

At first I thought to myself that a custom memory allocator for SQLite
could do this, but the real problem would be once a pointer is given to
SQLite, it is expected that pointer will be valid until disposed of in some
way. It would take a lot of effort to make SQLite access pointers
indirectly so that the virtual address window could be set to the proper
physical memory range for every memory access. It would be returning to a
segment:offset scheme that I certainly do not miss.

Certainly a valuable tool for heavy processes that need to run on 32-bit
PAE hardware with > 4 GiB of addressable ram. Anyone want to start work on
SQLHeavy? ;)


> Still, it looks like we’re on the cusp of all the major OSes moving to
> 64-bit-only, so a lot of work made here might be obsolete soon.  OS X and
> RHEL have already made the leap.  Ubuntu and Microsoft both threatened to
> do this recently, but both backed down after user outcry.
>

Right. PAE & AWE (and related interfaces on other OSes) were a great
solution from 1994 until the end of the 32-bit server era. They might still
have some application, but with every passing day their utility diminishes,
I expect.

-- 
Scott Robison
___
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]

Well, so if you want to increase the process virtual memory limits of a 32-bit 
process on 64-bit windows you can set the LargeAddressAware flag on the 
executable.  While this will not give you access to a 64-bit address space, it 
will give you access to 4 GB per process less a few megabytes used for the 
trampoline (since the OS is no longer mapped into the process address space).  
Of course, unless you want problems the executable needs to be 32-bit clean.  
That means no "signed" addresses/pointers, and no diddling of the sign (or 
other) bits in the address field to contain some kind of overloaded information 
store.

SQLite is 32-bit clean.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Warren Young
> Sent: Thursday, 11 August, 2016 14:38
> To: SQLite mailing list
> Subject: Re: [sqlite] 64-bit SQLite3.exe
> 
> On Aug 10, 2016, at 6:32 PM, Keith Medcalf  wrote:
> >> You must be talking about PAE, which is an unmitigated hack, in the
> >> dirtiest sense of that word
> >
> > It is not a hack.  It is how things work.  I do not see where you get
> the idea that it is a hack.
> 
> Because I know how PAE works, and I have the technical competence to
> express an informed opinion about it.
> 
> But if you don’t want to believe me, maybe you’ll believe Linus Torvalds:
> 
>   https://cl4ssic4l.wordpress.com/2011/05/24/linus-torvalds-about-pae/
> 
> > non-Windows have supported physical address limits beyond 4 GB as
> standard since a very long time (Linux since 2009).
> 
> Yes, via PAE.
> 
> If you mean something other than PAE, please give a technical reference to
> what you are talking about.  Like, maybe, a page in an Intel architecture
> reference manual.  Even a Wikipedia link would do.
> 
> >> 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.
> 
> You’re describing PAE:
> 
>   https://en.wikipedia.org/wiki/Physical_Address_Extension
> 
> PAE required extending an IA-32 processor’s normal 32-bit address bus to
> 36 bits, giving a maximum virtual address space of 64 GiB.
> 
> PAE does not change the machine code instructions for accessing memory,
> since that would require recompiling everything to allow 36-bit addresses
> at the program level.  This would require another incompatible Intel
> instruction set, as different from IA-32 as IA-64 is.
> 
> If you look at the GCC manuals, you will not find a “PAE mode” flag for
> giving a binary with 36-bit addresses, because an IA-32 processor doesn’t
> offer that addressing mode.  Such a flag would be on this page in the GCC
> manual:
> 
>   https://gcc.gnu.org/onlinedocs/gcc-6.1.0/gcc/x86-Options.html
> 
> Notice that there is no PAE flag, and no -m36 flag.  If you give -m64, you
> get IA-64 code, which won’t run on under a 32-bit kernel, even with PAE
> enabled.
> 
> You also won’t find such a compiler flag for Visual C++:
> 
>   https://msdn.microsoft.com/en-us/library/19z1t1wy.aspx
> 
> Though the OS kernel can use PAE to address more than 4 GiB via a 3-level
> TLB scheme — as opposed to the 2-level scheme Intel used before PAE — it
> doesn’t let a single program access more than 4 GiB at any given time.
> 
> Since this whole thread is about giving a single program — sqlite3.exe —
> access to more RAM, PAE doesn’t solve the OP’s problem.
> 
> You can install 32-bit Windows Server 2012 on a PAE-aware box with 16 GiB
> of RAM and run two instances of 32-bit sqlite3.exe on it, but they will
> only be able to chew up half the system’s RAM between themselves, no more.
> 
> >> 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).
> 
> Virtual memory still doesn’t solve the OP’s problem.
> 
> You can take a PAE-supporting box with 4 GiB of physical RAM in it,
> install a PAE-aware OS on it, then 

Re: [sqlite] AS being optional

[Keith Medcalf]

AS is optional as it was "syntactic sugar" added to SQL so that Managers and 
others with no computer skills could read SQL too, just like the overriding 
design principle of COBOL wasto be "so simple and english like that even 
management could understand it".  Having to end each statement with a period 
(.) was rejected because the period is already a reserved separator.  Other 
syntactic sugar include the JOIN and ON syntax and keywords.

If sugary coatings were made mandatory then perfectly legal SQL written between 
1972 and 1995 would all have to be recoded with the sugary coating, thus 
breaking backward compatibility, and this was not a desired outcome of the 
standardization process.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Tim Streater
> Sent: Thursday, 11 August, 2016 08:57
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] AS being optional
> 
> Is there a reason why AS is optional, such that:
> 
>   select a b, c from x
> 
> is equivalent to:
> 
>   select a as b, c from x;
> 
> I agree it couldn't be changed now, but luckily I spotted that I had
> omitted a comma, before it was too late.
> 
> --
> Cheers  --  Tim
> ___
> 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

[Warren Young]

On Aug 11, 2016, at 3:19 PM, Scott Robison  wrote:
> 
> I think you guys are just talking past each other.

Well, at least one of us isn’t communicating clearly, that’s certain.  I just 
don’t yet know if it’s me, him, or both of us. :)

> Windows versions that
> support PAE have the Address Windowing Extensions (AWE) which allows a
> single process to access more than 4 GiB total in a single process

From what I can see, AWE and PAE are orthogonal, but AWE without PAE doesn’t 
let you get beyond 4 GiB in a single process on IA-32.

But — and this may be where Keith was trying to go — a 32-bit app running on a 
64-bit OS doesn’t need PAE because the host system does support more than 4 GiB 
of virtual memory.  I have yet to see anything that says that AWE on 64-bit 
Windows couldn’t give a 32-bit app access to some of the VM beyond 4 GiB, even 
on consumer versions of Windows.

If so, that’s what I was trying to get at with my request that he give a 
reference to the specific technology he’s talking about, instead of describing 
it in prose.

> That being said, I don't think it is a reasonable or practical thing to
> expect of cross platform source code like SQLite to use such a platform
> specific API.

Couldn’t it be abstracted behind a layer that used mmap() + tmpfs to pull off a 
similar trick on other 64-bit OSes?  (I’m not talking about > 4 GiB on 32-bit 
OSes here.)

It’d be a lot of work just to avoid rebuilding for 64-bit, but maybe it would 
be an interesting project for someone.  Like a master’s university project, 
maybe.

Still, it looks like we’re on the cusp of all the major OSes moving to 
64-bit-only, so a lot of work made here might be obsolete soon.  OS X and RHEL 
have already made the leap.  Ubuntu and Microsoft both threatened to do this 
recently, but both backed down after user outcry.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_replace

[E . Pasma]

11 aug 2016, om 13:10, Anthony Lansbergen:


Hello,

I needed a way to make parameterized texts for logging in my current  
project.
For this purpose I missed a function in sqlite: group_replace, so I  
made an extension.
The function is just like group_concat, an aggregate function, but  
it replaces keys with values in a string instead of concattenating  
strings.


I put the code online, it's publicly available at:

https://github.com/adesys/sqlite3_group_replace_extension

It seems to work fine, but since this is my first extension, can  
someone please take a look at it and check if it is bug free :-)


thanks in advance,
Anthony Lansbergen

Hello, I am not very experienced. I do have an easy test to check  
memory leak. It runs on OS X. For  group_replace i tested the script  
below and that shows increasing memory use.
Two remarks/questions: 1. better use sqlite3_malloc and sqlite3_free  
instead of the C primitives.
2. is it imaginable to obtain the same functionality without a C  
extension, by using plain replace() inside a recursive CTE?

Thanks, E. Pasma

.load ./sqlite3_group_replace_extension
create table kv (k, v, reverse);
insert into kv values ('1', 'one',0),('2','two',0),('3','knock knock  
knock',0);

insert into kv select v,k,1 from kv;
create view v as
with r as  (
select 0 as i, '1 2 3 4 5 6 7 8 9' as s
union all
select i+1, (select group_replace (s, k, v) from kv where reverse=i%2)
from r where i<10
)
select * from r where i>=10-1
;
select * from v;
.system ps -o vsz -o command|grep sqlite3$
select * from v;
.system ps -o vsz -o command|grep sqlite3$
select * from v;
.system ps -o vsz -o command|grep sqlite3$

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

[Michael Falconer]

...as for Stephen, Mr Beal you need to get out more LOL! Little Johnny
Tables indeed. Rub it in, why not? LOL


On 12 August 2016 at 09:38, Michael Falconer 
wrote:

> Thanks all,
>
> must admit to being around db's for years but I never did get my head
> around the whole injection thing, sad but true. Keith summed it up in usual
> succinct fashion which when read by one old hack cause much reddening of
> the facial features. Bugger, says I, that speaks my language and it's
> saying you are a goose! I'm admitting to no more!
>
> Thanks all for opening my eyes, at long last, and excuse me while I grep
> my code for sqlite3_exec()grr...damnetc.
>
>
> On 11 August 2016 at 23:40, Quan Yong Zhai  wrote:
>
>> > From: michael.j.falco...@gmail.com
>> > Date: Thu, 11 Aug 2016 15:53:39 +1000
>> > To: sqlite-users@mailinglists.sqlite.org
>> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
>> >
>> > 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.
>> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
>> formattingoptions to defending SQL injection. '%Q' to quote string
>> parameters, '%w' to quote table name or column name..
>> >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.
>> >
>>
>> I am not a security expert, but I think the culprit of SQL injection
>> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
>> command text constructed. if you look into the SQLite source code, there
>> are many places used sqlite3_exec(), and  theparameters are carefully
>> quoted by '%Q', '%q' or '%w'.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



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

[Michael Falconer]

Thanks all,

must admit to being around db's for years but I never did get my head
around the whole injection thing, sad but true. Keith summed it up in usual
succinct fashion which when read by one old hack cause much reddening of
the facial features. Bugger, says I, that speaks my language and it's
saying you are a goose! I'm admitting to no more!

Thanks all for opening my eyes, at long last, and excuse me while I grep my
code for sqlite3_exec()grr...damnetc.


On 11 August 2016 at 23:40, Quan Yong Zhai  wrote:

> > From: michael.j.falco...@gmail.com
> > Date: Thu, 11 Aug 2016 15:53:39 +1000
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> >
> > 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.
> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
> formattingoptions to defending SQL injection. '%Q' to quote string
> parameters, '%w' to quote table name or column name..
> >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.
> >
>
> I am not a security expert, but I think the culprit of SQL injection
> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
> command text constructed. if you look into the SQLite source code, there
> are many places used sqlite3_exec(), and  theparameters are carefully
> quoted by '%Q', '%q' or '%w'.
>
> ___
> 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

[Scott Robison]

On Thu, Aug 11, 2016 at 2:37 PM, Warren Young  wrote:

> On Aug 10, 2016, at 6:32 PM, Keith Medcalf  wrote:
> >> You must be talking about PAE, which is an unmitigated hack, in the
> >> dirtiest sense of that word
> >
> > It is not a hack.  It is how things work.  I do not see where you get
> the idea that it is a hack.
>
> Because I know how PAE works, and I have the technical competence to
> express an informed opinion about it.
>
> But if you don’t want to believe me, maybe you’ll believe Linus Torvalds:
>
>   https://cl4ssic4l.wordpress.com/2011/05/24/linus-torvalds-about-pae/
>
> > non-Windows have supported physical address limits beyond 4 GB as
> standard since a very long time (Linux since 2009).
>
> Yes, via PAE.
>
> If you mean something other than PAE, please give a technical reference to
> what you are talking about.  Like, maybe, a page in an Intel architecture
> reference manual.  Even a Wikipedia link would do.
>

{a bunch of snipped stuff}

I think you guys are just talking past each other. Windows versions that
support PAE have the Address Windowing Extensions (AWE) which allows a
single process to access more than 4 GiB total in a single process, though
the application is responsible for what address ranges are mapped in or out
at any given point in time.

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366527(v=vs.85).aspx

That being said, I don't think it is a reasonable or practical thing to
expect of cross platform source code like SQLite to use such a platform
specific API. A 64 bit build of SQLite should probably be used to address
memory > 4 GiB in the most straightforward / reasonable / compatible way.

So could a 32 bit build of SQLite access more than 4 GiB with appropriate
hardware & OS support? Sure. Should a 32 bit build of SQLite stretch to
support managing the address space itself as might be required? I don't
think so.

-- 
Scott Robison
___
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:32 PM, Keith Medcalf  wrote:
>> You must be talking about PAE, which is an unmitigated hack, in the
>> dirtiest sense of that word
> 
> It is not a hack.  It is how things work.  I do not see where you get the 
> idea that it is a hack.

Because I know how PAE works, and I have the technical competence to express an 
informed opinion about it.

But if you don’t want to believe me, maybe you’ll believe Linus Torvalds:

  https://cl4ssic4l.wordpress.com/2011/05/24/linus-torvalds-about-pae/

> non-Windows have supported physical address limits beyond 4 GB as standard 
> since a very long time (Linux since 2009).

Yes, via PAE.

If you mean something other than PAE, please give a technical reference to what 
you are talking about.  Like, maybe, a page in an Intel architecture reference 
manual.  Even a Wikipedia link would do.

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

You’re describing PAE:

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

PAE required extending an IA-32 processor’s normal 32-bit address bus to 36 
bits, giving a maximum virtual address space of 64 GiB.

PAE does not change the machine code instructions for accessing memory, since 
that would require recompiling everything to allow 36-bit addresses at the 
program level.  This would require another incompatible Intel instruction set, 
as different from IA-32 as IA-64 is.

If you look at the GCC manuals, you will not find a “PAE mode” flag for giving 
a binary with 36-bit addresses, because an IA-32 processor doesn’t offer that 
addressing mode.  Such a flag would be on this page in the GCC manual:

  https://gcc.gnu.org/onlinedocs/gcc-6.1.0/gcc/x86-Options.html

Notice that there is no PAE flag, and no -m36 flag.  If you give -m64, you get 
IA-64 code, which won’t run on under a 32-bit kernel, even with PAE enabled.

You also won’t find such a compiler flag for Visual C++:

  https://msdn.microsoft.com/en-us/library/19z1t1wy.aspx

Though the OS kernel can use PAE to address more than 4 GiB via a 3-level TLB 
scheme — as opposed to the 2-level scheme Intel used before PAE — it doesn’t 
let a single program access more than 4 GiB at any given time.

Since this whole thread is about giving a single program — sqlite3.exe — access 
to more RAM, PAE doesn’t solve the OP’s problem.

You can install 32-bit Windows Server 2012 on a PAE-aware box with 16 GiB of 
RAM and run two instances of 32-bit sqlite3.exe on it, but they will only be 
able to chew up half the system’s RAM between themselves, no more.

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

Virtual memory still doesn’t solve the OP’s problem.

You can take a PAE-supporting box with 4 GiB of physical RAM in it, install a 
PAE-aware OS on it, then configure that OS for 60 GiB of swap to give 64 GiB of 
total virtual memory space, but the individual programs running under that OS 
*still* won’t be able to address more than 4 GiB of virtual memory each, 
because the memory addressing instructions will continue to use 32-bit pointers.

All virtual memory does in a situation like this is allow another program to 
come along and grab up to 4 GiB of virtual memory space itself.

That doesn’t help the OP, who is running a single program — sqlite3.exe — and 
needs it to address more than 4 GiB of memory.  It doesn’t matter whether you 
call it RAM, physical memory, or virtual memory, IA-32 simply doesn’t solve the 
OP’s problem, with or without PAE.

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

Re: [sqlite] group_replace

[Dominique Devienne]

On Thu, Aug 11, 2016 at 1:10 PM, Anthony Lansbergen 
wrote:

> Hello,
>
> I needed a way to make parameterized texts for logging in my current
> project.
> For this purpose I missed a function in sqlite: group_replace, so I made
> an extension.
> The function is just like group_concat, an aggregate function, but it
> replaces keys with values in a string instead of concattenating strings.
>
> I put the code online, it's publicly available at:
>
> https://github.com/adesys/sqlite3_group_replace_extension
>
> It seems to work fine, but since this is my first extension, can someone
> please take a look at it and check if it is bug free :-)
>

From https://www.sqlite.org/c3ref/value_blob.html:
Please pay particular attention to the fact that the pointer returned from
[...] sqlite3_value_text(),
[...] can be invalidated by a subsequent call to [...]
sqlite3_value_text(), [...]

I'd also check sqlite3_value_type() explicitly for SQLITE_TEXT to avoid
implicit conversions.

Who frees p->result in _final()? No one IMHO. So leak I think.

I didn't check the rest of the logic deeply. --DD

PS: I'm also a little wary of aligned issues from the allocation done by
sqlite3_aggregate_context()
but I guess that's the C++ dev talking. Back in my C days (a long time
ago...) I don't remember
worrying about it, so maybe that's a C++ thing only.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AS being optional

[John McKown]

That's the ANSI / ISO standard for the SQL language.

On Thu, Aug 11, 2016 at 10:02 AM, Richard Hipp  wrote:

> On Thu, 11 Aug 2016 15:57 +0100, Tim Streater 
> wrote:
> > Is there a reason why AS is optional
>
> Because that is what PostgreSQL does.  (Also MySQL.  Maybe others too.)
>
> --
> 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
>



-- 
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] AS being optional

[Richard Hipp]

On Thu, 11 Aug 2016 15:57 +0100, Tim Streater  wrote:
> Is there a reason why AS is optional

Because that is what PostgreSQL does.  (Also MySQL.  Maybe others too.)

-- 
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] AS being optional

[Tim Streater]

Is there a reason why AS is optional, such that:

  select a b, c from x

is equivalent to:

  select a as b, c from x;

I agree it couldn't be changed now, but luckily I spotted that I had omitted a 
comma, before it was too late.

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

Re: [sqlite] Setting temp location with Python

[Kevin O'Gorman]

On Wed, Aug 10, 2016 at 6:50 AM, Jonathan Moules <
jonathan-li...@lightpear.com> wrote:

> 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?
>
>
> Take a look at https://www.sqlite.org/tempfiles.html, specifically item
5.0.  which addresses this question.  It varies a bit depending on your
environment, so there's no single answer.  On my Linux system, I fiddle
with the SQLITE_TMPDIR environment variable.

You might want to describe your environment so that folks with a similar
environment can respond more helpfully.

-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] group_replace

[Anthony Lansbergen]

Hello, 

I needed a way to make parameterized texts for logging in my current project. 
For this purpose I missed a function in sqlite: group_replace, so I made an 
extension. 
The function is just like group_concat, an aggregate function, but it replaces 
keys with values in a string instead of concattenating strings. 

I put the code online, it's publicly available at: 

https://github.com/adesys/sqlite3_group_replace_extension 

It seems to work fine, but since this is my first extension, can someone please 
take a look at it and check if it is bug free :-) 

thanks in advance, 
Anthony Lansbergen 
___
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.

[Quan Yong Zhai]

> From: michael.j.falco...@gmail.com
> Date: Thu, 11 Aug 2016 15:53:39 +1000
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> 
> 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.
sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some 
formattingoptions to defending SQL injection. '%Q' to quote string parameters, 
'%w' to quote table name or column name..
>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.
> 

I am not a security expert, but I think the culprit of SQL injection 
vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL 
command text constructed. if you look into the SQLite source code, there are 
many places used sqlite3_exec(), and  theparameters are carefully quoted by 
'%Q', '%q' or '%w'.
  
___
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 11, 2016, at 12:53 AM, Michael Falconer  
wrote:

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

If the values are truly 100% application derived, with no source or root 
material
from any user-generated inputs, config files, etc., then you’re good… assuming
you still quote correctly, and so forth.

The thing is, that’s rarely the case, and as the program changes and evolves, 
it is
almost inevitable that user-driven values make it into the statements.   Yes, 
that’s a
broad, sweeping, generalization, and you know your specific development needs
best.  But remember that SQL injection attacks are essentially impossible if
you correctly use bound parameters, yet it is also considered one of the top
10 security vectors.  In 99% of cases, they're really easy to avoid, and yet
they’re all over the place.  There is a lesson in caution in that.


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

Be aware that bound parameters are for *values* only.  You cannot bind
identifiers, which includes database names and table names.  From what
I gathered in the rest of this discussion, that might be an issue in your case.

 -j

--  
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] FTS4/5 ranking function differences

[Jan Berkel]

>>Another open question I have is how to access the user query expression
>>from within the ranking function. Don't think this is exposed anywhere.
>>(Looking at the source I noticed Fts5Expr *, but it is private).

>>The best option would then be to pass it through to the
>>ranking function as a parameter?

>I guess so. You can't actually get at the query text from within an 
>auxiliary function implementation. You can get the number of phrases, 
>query the FTS index for each phrase and some other things, but the 
>actual query text or parsed query is not currently available.

>What exactly do you need? What do you think the API should look like?

I use SQLite as backend to build a dictionary so my requirements might
not 
be typical (indexing a large number of very small documents, bm25 
is not a good fit)

Right now the main reason I want to be able to get the query term in 
the ranking function is to boost shorter matches over longer, partial
ones:

foo* => [  foo, foobar, foobarbaz ]

I was expecting to be able to get a reference to the query term
expression,
something like: 

Fts5ExprTerm *(*xPhraseTerm)(Fts5Context*, int iPhrase, int iTerm)

But actually I think for now I might be able to do this sort of ranking 
without knowing the exact query term, just by simply scoring results
based on their length.

  – Jan
___
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.

[Keith Medcalf]

So if a user typed in

authors; drop table importfinancialdata;

for the tablename, what would your software do?

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Michael Falconer
> Sent: Wednesday, 10 August, 2016 23:54
> To: SQLite mailing list
> Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> 
> 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
> > 

Re: [sqlite] Can this be done more efficient

[Cecil Westerhof]

​I already rewrote it with the info you gave in the other thread. :-)
It runs in around one second. And the slice length is easily modified.

2016-08-11 11:38 GMT+02:00 R Smith :

> STAT_INFO(count, onePercent, likeString) AS (  -- Please don't use
> reserved words like "count" as column


​I changed that and then I saw I did not need it anymore.
​



> SELECT   Slice
> ,Percentage
> FROM STAT_SLICES
> WHEREPercentage IS NOT NULL -- This line is superfluous, you can
> remove it (unless you meant zero - NULL is not Zero)
>

​Nope, it is necessary. Because some of the SELECT did not have a result
and then you get NULL. But the code is a lot cleaner now, so I do not need
it anymore.
​



> One other annoyance. I do this is the SQLite database browser. Every time I
>
>> execute this, my database is locked and I have to revert changes. What is
>> happening here.
>> By the way, this also happens when I execute:
>>  pragma table_info(vmstat)
>>
>>
> I think it executes it in a transaction - so won't unlock until the
> transaction is either committed or rolled back.
>

​Yes that is what happens. But (as far as I know) the database is not
changed. So why is it happening? And the rollback needs to be done in the
GUI. :-(

Just a SELECT does not have this problem, but I also get it with:
pragma table_info(vmstat)
​
​Well it is a problem of the DB browser I suppose.​


-- 
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-11 11:01 GMT+02:00 R Smith :

> Woops, I think I had it right the first time then re-thought it wrongly :)
>
>
> Your SQL would then look like this:
>
>
> CREATE TABLE T (Grp INT);
>
> INSERT INTO T VALUES (5),(21),(27),(28),(22),(39),(
> 44),(45),(46),(51),(88);
>
> WITH TC(Tot) AS (
>   SELECT CAST(COUNT(*) AS REAL) FROM T
> ), SL(Slice) AS (
>   SELECT CAST((T.Grp / 10) AS INT) * 10
> FROM T
> )
> SELECT Slice||' - '||(Slice+9) AS Slice, COUNT(*) AS Cnt, ROUND(COUNT(*) /
> Tot * 100,2) AS Perc
>   FROM SL, TC
>  GROUP BY Slice
>
>
>   -- Slice | Cnt |  Perc
>   -- - | --- | -
>   -- 0 - 9 |  1  |  9.09
>   -- 20 - 29   |  4  | 36.36
>   -- 30 - 39   |  1  |  9.09
>   -- 40 - 49   |  3  | 27.27
>   -- 50 - 59   |  1  |  9.09
>   -- 80 - 89   |  1  |  9.09


​That brought me to the right place. A little change because I want the
first slice to go from 0 to 10 including. Also it is now very simple to
change the slice length. An example for slice length = 5:
"86 - 90""55.7"
"81 - 85""27.87"
"91 - 95""12.82"
"76 - 80""2.69"
"71 - 75""0.28"
"66 - 70""0.24"
"61 - 65""0.23"
"96 - 100""0.07"
"56 - 60""0.05"
"26 - 30""0.02"
"11 - 15""0.01"
"21 - 25""0.01"
"41 - 45""0.01"
"51 - 55""0.01"

Not bad more as 95% more as 80% idle. ;-)

The SQL code:
WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
-- SELECT "%"
),
STAT_INFO(onePercent, likeString, sliceLength) AS (
SELECT COUNT(*) / 100.0
,  likeString
,  5
FROM   vmstat
,  STAT_DATE
WHERE  date LIKE likeString
),
STAT_SLICES(sliceStart) AS (
SELECT CAST(((idleTime - 1) / sliceLength) AS INT) * sliceLength
FROM   vmstat
,  STAT_INFO
WHERE  date LIKE likeString
)
SELECT   CASE WHEN (sliceStart = 0) THEN
 sliceStart
 ELSE
 (sliceStart + 1)
 END ||' - ' || (sliceStart + sliceLength) AS Slice
,ROUND(COUNT(*) / onePercent, 2) AS Percentage
FROM STAT_SLICES
,STAT_INFO
GROUP BY Slice
ORDER BY Percentage DESC
​

​I am not easily satisfied. ;-)

As title for the first column I now have Slice, but I would prefer
something like:
Slice (2016-08-%)
So it is easily to know for which period the info is.​
I tried:
AS Slice || ' (' || likeString || ')'
​but that does not work.
Is what I want possible?​

-- 
Cecil Westerhof
___
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/11/2016 04:41 PM, Jan Berkel wrote:

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.

Thanks, I was under the assumption that this would be slow, since the
xColumn() access will cause the whole row to be loaded (separately,
instead of the more efficient join upfront), but I'll try it.

Another open question I have is how to access the user query expression
from within the ranking function. Don't think this is exposed anywhere.
(Looking at the source I noticed Fts5Expr *, but it is private).

The best option would then be to pass it through to the
ranking function as a parameter?


I guess so. You can't actually get at the query text from within an 
auxiliary function implementation. You can get the number of phrases, 
query the FTS index for each phrase and some other things, but the 
actual query text or parsed query is not currently available.


What exactly do you need? What do you think the API should look like?

Dan.


___
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

[Jan Berkel]

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

Thanks, I was under the assumption that this would be slow, since the 
xColumn() access will cause the whole row to be loaded (separately, 
instead of the more efficient join upfront), but I'll try it.

Another open question I have is how to access the user query expression
from within the ranking function. Don't think this is exposed anywhere.
(Looking at the source I noticed Fts5Expr *, but it is private). 

The best option would then be to pass it through to the 
ranking function as a parameter?

 – Jan





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

[Simon Slavin]

On 11 Aug 2016, at 5:32am, Jay Kreibich  wrote:

> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.

There are situations where it's the best (possibly only) way, though.  If you 
want to offer your users a flexible search/sort ability then there can be too 
many different possibilities to store all the possible queries.  A clearer 
formulation of the above is that when you build your SQL command string, you do 
not build it directly from user input.

So using a bunch of 'IF' statements to build a 'SELECT' string is okay.

Allowing the user to pick a column name from a popup list you populate, and 
concatenating a 'SELECT' string including the one the user picked is okay.

But allowing the user to type a column name and concatenating a 'SELECT' string 
including what the user typed is not.

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

Re: [sqlite] Can this be done more efficient

[R Smith]

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

I have the folowing SQL code:
//...//
With this I can get the idle time of my computer easily for different
periods of time. There is only one small problem: it takes quit long to
execute, between 8 and 9 seconds. It is not a huge problem: how often do I
want to get this information? But I was wondering if I did something wrong
and if it could be optimised.
The table has just under 400.000 records.


The next will sort it in principle, I can't test it so you might 
encounter a spelling mistake or syntax error, but if it should run well 
when fixed:

(note also remarks in comments)

WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
-- SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
SELECT "%"
),
STAT_INFO(count, onePercent, likeString) AS (  -- Please don't use reserved words like 
"count" as column names...
SELECT COUNT(*)
,  COUNT(*) / 100.0
,  likeString
FROM   vmstat
,  STAT_DATE
WHERE  date LIKE likeString
),
SL(Slice) AS (
SELECT CAST((MAX(idletime-1,0) / 10) AS INT) * 10
  FROM vmstat, STAT_DATE
 WHERE date LIKE likeString
),
 STAT_SLICES(Slice, Count, Percentage) AS (
SELECT (Slice+1)||' - '||(Slice+10), COUNT(*), ROUND(COUNT(*) / 
onepercent,2)
  FROM SL, STAT_INFO
 GROUP BY Slice
)
SELECT   Slice
,Percentage
FROM STAT_SLICES
WHEREPercentage IS NOT NULL -- This line is superfluous, you can remove it 
(unless you meant zero - NULL is not Zero)
ORDER BY Percentage DESC
 



One other annoyance. I do this is the SQLite database browser. Every time I

execute this, my database is locked and I have to revert changes. What is
happening here.
By the way, this also happens when I execute:
 pragma table_info(vmstat)



I think it executes it in a transaction - so won't unlock until the 
transaction is either committed or rolled back.



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

Woops, I think I had it right the first time then re-thought it wrongly :)


Your SQL would then look like this:


CREATE TABLE T (Grp INT);

INSERT INTO T VALUES (5),(21),(27),(28),(22),(39),(44),(45),(46),(51),(88);

WITH TC(Tot) AS (
  SELECT CAST(COUNT(*) AS REAL) FROM T
), SL(Slice) AS (
  SELECT CAST((T.Grp / 10) AS INT) * 10
FROM T
)
SELECT Slice||' - '||(Slice+9) AS Slice, COUNT(*) AS Cnt, ROUND(COUNT(*) 
/ Tot * 100,2) AS Perc

  FROM SL, TC
 GROUP BY Slice


  -- Slice | Cnt |  Perc
  -- - | --- | -
  -- 0 - 9 |  1  |  9.09
  -- 20 - 29   |  4  | 36.36
  -- 30 - 39   |  1  |  9.09
  -- 40 - 49   |  3  | 27.27
  -- 50 - 59   |  1  |  9.09
  -- 80 - 89   |  1  |  9.09



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

[sqlite] Can this be done more efficient

[Cecil Westerhof]

I have the folowing SQL code:
WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
-- SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
SELECT "%"
),
STAT_INFO(count, onePercent, likeString) AS (
SELECT COUNT(*)
,  COUNT(*) / 100.0
,  likeString
FROM   vmstat
,  STAT_DATE
WHERE  date LIKE likeString
),
STAT_SLICES(Slice, Count, Percent) AS (
SELECT ' 0 -  10', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >=  0 AND idletime <=  10
UNION
SELECT '11 -  20', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 11 AND idletime <=  20
UNION
SELECT '21 -  30', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 21 AND idletime <=  30
UNION
SELECT '31 -  40', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 31 AND idletime <=  40
UNION
SELECT '41 -  50', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 41 AND idletime <=  50
UNION
SELECT '51 -  60', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 51 AND idletime <=  60
UNION
SELECT '61 -  70', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 61 AND idletime <=  70
UNION
SELECT '71 -  80', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 71 AND idletime <=  80
UNION
SELECT '81 -  90', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 81 AND idletime <=  90
UNION
SELECT '91 - 100', COUNT(*), COUNT(*) / onePercent FROM vmstat,
STAT_INFO WHERE date LIKE likeString AND idletime >= 91 AND idletime <= 100
)
SELECT   Slice
,ROUND(Percent, 2) AS Percentage
FROM STAT_SLICES
WHERENOT Percentage IS NULL
ORDER BY Percentage DESC

The important part of the table is:
"0""vmstatID""INTEGER""0""NULL""1"
"1""date""TEXT""1""CURRENT_DATE""0"
"2""time""TEXT""1""CURRENT_TIME""0"
…
"18""idletime""INTEGER""1""NULL""0"

With this I can get the idle time of my computer easily for different
periods of time. There is only one small problem: it takes quit long to
execute, between 8 and 9 seconds. It is not a huge problem: how often do I
want to get this information? But I was wondering if I did something wrong
and if it could be optimised.
The table has just under 400.000 records.

I tried to change:
idletime >=  0 AND idletime <=  10
to:
idletime  BETWEEN 0 AND 10
but that seemed to take more time. (Between 10 to 100% more.)


I did the following measurements if that is any help.

The following takes about 900 ms
WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
-- SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
SELECT "%"
),
STAT_INFO(count, onePercent, likeString) AS (
SELECT COUNT(*)
,  COUNT(*) / 100.0
,  likeString
FROM   vmstat
,  STAT_DATE
WHERE  date LIKE likeString
)
SELECT ' 0 -  10'
,  COUNT(*)
,  COUNT(*) / onePercent
FROM   vmstat
,  STAT_INFO
WHERE  date LIKE likeString
   AND idletime  BETWEEN 0 AND 10

When made simpler it takes about 500 ms
WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
-- SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
SELECT "%"
)
SELECT ' 0 -  10'
,  COUNT(*)
FROM   vmstat
,  STAT_DATE
WHERE  date LIKE likeString
   AND idletime  BETWEEN 0 AND 10

And the following takes about 300 ms:
SELECT ' 0 -  10'
,  COUNT(*)
FROM   vmstat
WHERE  idletime BETWEEN 0 AND 10

So probably when I write a program I could make it run in 3 seconds.


One other annoyance. I do this is the SQLite database browser. Every time I
execute this, my database is locked and I have to revert changes. What is
happening here.
By the way, this also happens when I execute:
pragma table_info(vmstat)

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

Re: [sqlite] performance issue, v.3.10 compared to v.3.14 (inner joins)

[Richard Hipp]

On 8/10/16, Raja Kajiev  wrote:
> The issue is: one of requests performed in my app was executed in ~7
> seconds in v.3.10, but in v.3.14 execution time is ~13-15 seconds. That's
> really itching in my case.
> The sql request in question includes "inner join" constructs.
> I also remember that in version prior to 3.10 the execution time for that
> particular request was much larger than 10 seconds.
> The full code of the routine is below. I can put on share somewhere a copy
> of the db which is used (it is not a secret data, but it is a little large
> - about 290 MB).

Please send:

(1) The output of the ".fullschema -indent" command as run from the
command-line shell.
(2) The text of the SQL statement that is running slowly.

-- 
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] performance issue, v.3.10 compared to v.3.14 (inner joins)

[Raja Kajiev]

The issue is: one of requests performed in my app was executed in ~7
seconds in v.3.10, but in v.3.14 execution time is ~13-15 seconds. That's
really itching in my case.
The sql request in question includes "inner join" constructs.
I also remember that in version prior to 3.10 the execution time for that
particular request was much larger than 10 seconds.
The full code of the routine is below. I can put on share somewhere a copy
of the db which is used (it is not a secret data, but it is a little large
- about 290 MB).
I would appreciate any hint or advice on any detail of the implementation.


int ClippedUpdatesData::prepareClippedUpdates_sqlite(cMem& m_memUpdtReqSrv,
   int
exSetID,

CComQIPtr& m_pICrypt,
   cMem&
m_memBufNames3,
   cMem&
m_memBufData,
   long
m_MaxLengthData,
   const char*
m_path)
{
int processedFiles = 0;

const std::string sql_createTempTable = "CREATE TEMP TABLE temp.trtu
(name TEXT, edition INTEGER, update_no INTEGER, pos INTEGER)";
const std::string sql_dropTempTable = "DROP TABLE temp.trtu";
const std::string sql_insertTemp = "INSERT INTO temp.trtu VALUES
(@name, @edition, @update_no, @pos)";

int dbResult;
sqlite3_stmt *pStmt;
if(sqlite3_prepare_v2(m_sqlite_pDb, sql_createTempTable.c_str(),
sql_createTempTable.length(), , NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}
dbResult = sqlite3_step(pStmt);
sqlite3_finalize(pStmt);

sqlite3_exec(m_sqlite_pDb, "BEGIN TRANSACTION", NULL, NULL, NULL);
if(sqlite3_prepare_v2(m_sqlite_pDb, sql_insertTemp.c_str(),
sql_insertTemp.length(), , NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}

LOG4CPLUS_DEBUG(m_logger, "prepareClippedUpdates_sqlite - fill
temporary table");
int tempDataCounter = 0;
int nDatasets = m_memUpdtReqSrv.GetStructNumbers();
UPDATE_REC_SRV *aDataset = (UPDATE_REC_SRV
*)m_memUpdtReqSrv.GetStructPointer();
for (int i = 0; i < nDatasets; ++i) {
const UPDATE_REC_SRV& theDataset = aDataset[i];
if (theDataset.numbUpdt != 0) {
tempDataCounter += 1;
sqlite3_bind_text(pStmt, 1, theDataset.name, 8, SQLITE_STATIC);
sqlite3_bind_int(pStmt, 2, theDataset.numbEdit);
sqlite3_bind_int(pStmt, 3, (theDataset.numbUpdt - 1));
sqlite3_bind_int(pStmt, 4, i);

dbResult = sqlite3_step(pStmt);
sqlite3_reset(pStmt);
}
}
sqlite3_exec(m_sqlite_pDb, "END TRANSACTION", NULL, NULL, NULL);
sqlite3_finalize(pStmt);

if(tempDataCounter > 0) {
LOG4CPLUS_DEBUG(m_logger, "getting datasets info from the DB...");
char szSql[1024];
sprintf(szSql,
"SELECT cu.name name, cu.edition edition, cu.update_no
update_no, t.pos pos, cu.data data "
"FROM CLIPPED_UPDATE cu "
" INNER JOIN temp.trtu t ON cu.name=t.name AND
cu.edition=t.edition "
" INNER JOIN EXSET_DATASET ed ON ed.name=t.name AND
ed.edition=t.edition AND ed.coverage_id=cu.coverage_id "
" WHERE ed.exset_id=%d AND cu.update_no>t.update_no "
"ORDER BY name, edition, update_no", exSetID);
if(sqlite3_prepare_v2(m_sqlite_pDb, szSql, strlen(szSql), ,
NULL) != SQLITE_OK) {
throw std::runtime_error(sqlite3_errmsg(m_sqlite_pDb));
}

int nDataset = 0; // last processed dataset
while(true) {
dbResult = sqlite3_step(pStmt);
if(dbResult == SQLITE_ROW) {
int nEdition = sqlite3_column_int(pStmt, 1);
nDataset = sqlite3_column_int(pStmt, 3);
const void* blobBuf = sqlite3_column_blob(pStmt, 4);
int blobSize = sqlite3_column_bytes(pStmt, 4);

CComVariant vUpdate;
if((HRESULT)m_pICrypt->Decompress((BYTE*)blobBuf, blobSize,
) < 0) {
throw std::runtime_error("'prepareClippedUpdates_mssql'
- crypt problem");
}
BEG_HEAD_UPDT_FILE *pBegHeadUpdt = (BEG_HEAD_UPDT_FILE
*)vUpdate.parray->pvData;
if (pBegHeadUpdt->nTotalNumberOfRecords == 1) {
// possible termination
if(pBegHeadUpdt->wEditionNumber != nEdition  //
empty update
&& pBegHeadUpdt->wEditionNumber != nEdition + 1  //
new edition available
&& pBegHeadUpdt->wEditionNumber != 0 //
terminated
) {
throw
std::runtime_error("'prepareClippedUpdates_mssql' - data 

[sqlite] [BUG] sqldiff --schema silently ignores "NOT NULL" differences in columns

[Moritz Lenz]

Hi all,


please consider the following short shell script:


#!/bin/sh

sqlite3 --version
rm -f 1.sqlite3 2.sqlite3
echo 'CREATE TABLE test ( a VARCHAR );' | sqlite3 1.sqlite3
echo 'CREATE TABLE test ( a VARCHAR NOT NULL );' | sqlite3 2.sqlite3
sqldiff --schema 1.sqlite3 2.sqlite3 | wc -l


Running on Ubuntu 16.04, the output for me is

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
0

So sqldiff --schema produces an empty difference when there is a real,
observable difference between the two schemas.

IMHO, sqldiff should emit DDL along the lines of
https://www.sqlite.org/lang_altertable.html#otheralter to change the
schema, or at least https://www.sqlite.org/sqldiff.html should mention
this shortcoming in the "Limitations" section.


Best regards, and thanks to every involved in making sqlite,

Moritz

___
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-11 2:29 GMT+02:00 R Smith :

> 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


​This does something completely different. It takes the percentages rounded
down to 5 and shows how often they occur.
 7.69​ -> 5 (twice)
​15.38 -> 15
​23.07 -> 20
46.15 -> 45

​The values I look at can only be between 0 and 100, so I want the
percentage of values that are
>= 0  AND <= 10
>= 11 AND <= 20
…

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

[Dominique Devienne]

On Thu, Aug 11, 2016 at 4:34 AM, Paulo Roberto 
wrote:

> Thank you very much, it worked!


Just remember that exposing a SQL function that de-references a
"user"-supplied integer value as a pointer is inherently unsafe.
Anyone can select remember(val, 0) or select remember(val, 101) and crash
(at best) your app, or worse silently corrupt it in some way.

A safer (but slower) approach would be to add a level of indirection, and
not use the int as a "pointer" directly, but as a *key* in a map
(a global) used to lookup the memory address of the counter, so you *can*
error out (instead of crash/corrupt) on a bad input/key. My $0.02.
___
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.

[Clemens Ladisch]

Michael Falconer wrote:
> So what exactly is the issue with the string building if it does not
> include sql derived from user input?

That somebody will change the code later, or use it as a template.


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

[Stephan Beal]

On Thu, Aug 11, 2016 at 7:53 AM, Michael Falconer <
michael.j.falco...@gmail.com> wrote:

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

This short strip explains SQL injection better than any book, IMO:

https://xkcd.com/327/


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

FWIW, internally, exec() is just a proxy for prepare/step/finalize.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users