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

> 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

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

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:

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

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

Re: [sqlite] FTS4/5 ranking function differences

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

Re: [sqlite] Can this be done more efficient

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

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

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

[sqlite] group_replace

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

[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

Re: [sqlite] AS being optional

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

Re: [sqlite] Setting temp location with Python

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 -

Re: [sqlite] group_replace

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

Re: [sqlite] AS being optional

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

Re: [sqlite] 64-bit SQLite3.exe

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

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

Re: [sqlite] 64-bit SQLite3.exe

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

Re: [sqlite] AS being optional

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

Re: [sqlite] 64-bit SQLite3.exe

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

Re: [sqlite] 64-bit SQLite3.exe

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

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

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

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

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

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

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

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

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] Can this be done more efficient

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,

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

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 (

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

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

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

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

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

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

Re: [sqlite] C API - Parameterized Atomic Transactions

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

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

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

Re: [sqlite] Can this be done more efficient

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

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

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

Re: [sqlite] FTS4/5 ranking function differences

>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

Re: [sqlite] FTS4/5 ranking function differences

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

Re: [sqlite] group_replace

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