Re: [sqlite] Create VIEW passing despite invalid column name specified

On Fri, Jul 27, 2018 at 11:33 AM R Smith wrote: > On 2018/07/27 10:40 AM, Dominique Devienne wrote: > > On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote: > >> On 7/26/18, Tomasz Kot wrote: > >>> Beneath sql shall throw an error on CREATE VIEW statement (as inv

Re: [sqlite] Create VIEW passing despite invalid column name specified

On Fri, Jul 27, 2018 at 1:58 AM Richard Hipp wrote: > On 7/26/18, Tomasz Kot wrote: > > Hello, > > > > Beneath sql shall throw an error on CREATE VIEW statement (as invalid > > column is specified), but it passes (SQLite 3.23.1). > > The error is deferred until you try to use the view. The

Re: [sqlite] Bug: SQLITE_DEFAULT_LOOKASIDE does not compile without SQLITE_OMIT_COMPILEOPTION_DIAGS

On Mon, Jul 23, 2018 at 5:37 PM Dan Kennedy wrote: > On 07/23/2018 06:36 PM, Dominique Devienne wrote: > > On Mon, Jul 23, 2018 at 12:57 PM Dan Kennedy > wrote: > > The diff adds: > > > > #define CTIMEOPT_VAL2_(opt1,opt2) #opt1 "," #opt2 > >

Re: [sqlite] Bug: SQLITE_DEFAULT_LOOKASIDE does not compile without SQLITE_OMIT_COMPILEOPTION_DIAGS

On Mon, Jul 23, 2018 at 12:57 PM Dan Kennedy wrote: > On 07/22/2018 07:48 PM, Victor Costan wrote: > > In a custom SQLite build, SQLITE_DEFAULT_LOOKASIDE results in compilation > > errors, unless used with SQLITE_OMIT_COMPILEOPTION_DIAGS. > > > > This is because src/ctime.c includes the

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp wrote: > On 6/28/18, Dominique Devienne wrote: > > From reading this list, I've learned that for an index to have a change > to > > be used to consume an order by, the collation of the query and the index > > must match. >

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne wrote: > > On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: >> >> The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the approp

Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter wrote: > The xBestIndex function needs to call the sqlite_vtab_collation() function > to query the collation name required for each constraint and return the > appropriate index number. > > Subs: yes, yes, see above > Oh, great! Thanks Gunther!!!

Re: [sqlite] order by column_name collate custom_collation, with virtual table index

On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne wrote: > So is there a way to tell SQLite that vindex is of a given custom > collation, > to open the possibility of the index being used? > Note that there's no mention at all of "collation" or "collate" in htt

[sqlite] order by column_name collate custom_collation, with virtual table index

From reading this list, I've learned that for an index to have a change to be used to consume an order by, the collation of the query and the index must match. But in many instances, that index is one from a virtual table we implement. So is there a way to tell SQLite that vindex is of a given

Re: [sqlite] Performance of writing blobs

On Tue, Jun 12, 2018 at 12:49 PM Clemens Ladisch wrote: > Dominique Devienne wrote: > > In JOURNAL mode, new data goes to DB file directly, and modified pages > go to the JOURNAL file. > > And since here this is INSERT-only, from empty tables, I assumed pages > copied to

Re: [sqlite] Performance of writing blobs

On Tue, Jun 12, 2018 at 8:03 AM Clemens Ladisch wrote: > Dominique Devienne wrote: > > On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch > wrote: > >> It does write to the same pages, but those pages must be copied to the > >> rollback journal so that they can

Re: [sqlite] Performance of writing blobs

On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch wrote: > Dominique Devienne wrote: > > My assumption > > was that after the zeroblob(N), there was enough room in the main DBs > > pages, such that the subsequent blob open+write+close did not need to > > generate any

[sqlite] Performance of writing blobs

I'm surprised about the commit time of SQLite, when writing blobs is involved. Can anybody shed light on this subject? Below's a description of what I do, with the results. I've exporting data into SQLite, spread in several tables. I process only about 240,000 rows, and write around 1GB in 20,000

Re: [sqlite] [EXTERNAL] Selecting multiple similar columnname.

On Fri, Jun 8, 2018 at 2:38 PM Richard Hipp wrote: > On 6/8/18, Hick Gunter wrote: > > Adding the attribute "hidden" to a column prevents it from showing up in > the > > expansion of '*' in the select list, > > That only works for the CREATE TABLE passed into the > sqlite3_declare_vtab()

Re: [sqlite] Feature suggestion / requesst

On Fri, Jun 8, 2018 at 5:25 AM Rowan Worth wrote: > On 3 June 2018 at 07:28, Scott Robison wrote: > > > I've encountered a feature that I think would be awesome: > > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > > > Example: INSERT INTO blah (this, that, another) VALUES (x,

Re: [sqlite] Size of the SQLite library

On Thu, May 31, 2018 at 3:44 PM Richard Hipp wrote: > For many years, we have boasted that the size of the SQLite library is > "less than half a megabyte". > Given where the conversation is going, let me point out that many do not care one bit about the lib's size :) I'd much rather have an

Re: [sqlite] Congratulations on 18 years

On Wed, May 30, 2018 at 6:16 PM jungle Boogie wrote: > On 30 May 2018 at 03:27, Christian Schmitz > wrote: > > Congratulations to the SQLite team. > > > > As far as I see, the first checkin was 2000-05-29, which was over 18 > years ago. > > Way to go! What a truly awesome project this has been!

Re: [sqlite] Open and query sqlite db in a buffer

On Fri, May 18, 2018 at 12:02 PM R Smith wrote: > On 2018/05/18 11:50 AM, Lloyd wrote: > > I have a "buffer" containing data read from a file-based sqlite database. > > Is there any possibility for processing this "buffer" to query the data? > > But I imagine you had a

Re: [sqlite] Open and query sqlite db in a buffer

On Fri, May 18, 2018 at 11:50 AM Lloyd wrote: > I have a "buffer" containing data read from a file-based sqlite database. > Is there any possibility for processing this "buffer" to query the data? > Only https://www.sqlite.org/draft/c3ref/deserialize.html comes to mind,

Re: [sqlite] After update from 3.20 to 3.23 alter table throws error for boolean with default value FALSE

On Thu, May 17, 2018 at 6:51 PM Richard Hipp wrote: > On 5/17/18, David Raymond wrote: > > So what confuses me is that I would think that what comes after "DEFAULT" > > would have to be a string literal if it's not an identifier. So why does > it > >

Re: [sqlite] Is this really the best way to do this?

On Wed, May 16, 2018 at 8:33 PM Keith Medcalf wrote: > > SELECT coalsce((select action > from blocked > where mail='...'), 'OK') as action; > Nice one Keith. Works (see below), but I find it a bit intuitive, since returning no row is

Re: [sqlite] Is this really the best way to do this?

On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch wrote: > Stephen Chrzanowski wrote: > > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch > wrote: > >> SELECT action FROM blocked WHERE email = ? > >> UNION ALL > >> SELECT 'OK' > >> LIMIT 1; > > > > Out of

[sqlite] sqldiff wish list

Hi, First, thanks for the tool. It's a useful one. But here's a quick wish-list, after using the tool in a real use case: 1) a --version switch, to know which SQLite version is statically compiled inside sqldiff 2) in-row difference: Within a row-pair, generate a minimal UPDATE and omits all

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On Thu, Mar 22, 2018 at 3:22 PM, Richard Hipp <d...@sqlite.org> wrote: > On 3/22/18, Dominique Devienne <ddevie...@gmail.com> wrote: > > > > Hi Richard. Is 8.d from https://www.sqlite.org/draft/ > releaselog/current.html > > the result of this inquiry

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On Sat, Mar 17, 2018 at 1:28 AM, Richard Hipp wrote: > On 3/16/18, R Smith wrote: > > It's interesting to fathom what hypothesis is being tested with this > pole... > > INSERT operations on a table with AUTOINCREMENT do a full-table scan > against the

Re: [sqlite] [EXTERNAL] R*Trees query data cached?

On Tue, Mar 20, 2018 at 10:45 PM, David Ashman - Zone 7 Engineering, LLC < da...@zone7engineering.com> wrote: > I don't see a .describe in the SQLite documentation. I've tried to use > .schema but that returns an error. > .describe [3] and .schema [2] are "dot-commands" of the sqlite3 command

Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

On Sun, Mar 18, 2018 at 4:48 PM, Richard Hipp wrote: > These APIs support the concept of using small databases (small enough > to fit in memory) as a container for passing information around. I very much like the concept. Thank you for this addition. But then, this is begging

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On Fri, Mar 16, 2018 at 4:37 PM, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the AUTOINCREMENT > in your overall

Re: [sqlite] Lazy virtual table creation

On Sat, Mar 17, 2018 at 11:42 PM, Marco Bambini wrote: > with a bit of work you can use the authorize api in order to know when an > access to a non existing table is performed. > https://sqlite.org/c3ref/set_authorizer.html Interesting work-around, if that works. I.e.

Re: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

On Fri, Feb 16, 2018 at 6:07 PM, David Raymond wrote: > Remember that the usefulness of an index depends on the ordering of the > fields. An index on (b, a) isn't useful if you're looking for a, it's only useful > if you're looking for b. > Sometimes it is. See

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 1:11 PM, x wrote: > Thanks for the replies. For my purpose it was about avoiding the > possibility of having to apply a limit to a query that might already have a > limit clause. Good point. I tried, and indeed that's an issue. I really really

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta wrote: > On 2018-02-16 11:18, x wrote: > >> If a query is sorted on an index is there any advantage to including >> LIMIT in the stmt as opposed to omitting it and stepping through the result >> set LIMIT times? >> > > No --

Re: [sqlite] Help with row values

On Wed, Feb 14, 2018 at 8:44 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > Dominique Devienne wrote: > > in https://www.sqlite.org/src/info/f3112e67cdb27c1a > > to fix above ticket, I see queries with order by +a, > > but in https://www.sqlite.org/lang_select.html#

Re: [sqlite] Help with row values

On Tue, Feb 13, 2018 at 7:09 PM, Richard Hipp wrote: > On 2/13/18, Simon Slavin wrote: > > On 13 Feb 2018, at 5:32pm, x wrote: > > > >> Surely it should be 3 in both cases? > > > > I agree. Here's verification with a version

Re: [sqlite] How to parameterize a loadable extension at runtime

On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle wrote: > > An alternative is to expose a virtual table with a fixed set of rows, and > > accepting updates on the values, which can also then be "typed" too. > > But that's a lot more complicated though. > > (and refusing

Re: [sqlite] Auto Index Warnings; key on deterministic functions

On Tue, Feb 6, 2018 at 2:24 AM, J Decker wrote: > create table tableA ( pk PRIMARY KEY, dataA ) > create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES tableA(pk) ON DELETE CASCADE ) > > if the table was also ON UPDATE CASCADE could it slave to the same index > as

Re: [sqlite] How to parameterize a loadable extension at runtime

On Tue, Feb 6, 2018 at 9:44 AM, Simon Slavin wrote: > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote: > > > Another possibility would be to add a user-defined function for the > > configuration of the extension that could be called from a SELECT > >

[sqlite] Small sqlite3_analyzer doc link issue

FYI: The "additional explanation" link (of target https://www.sqlite.org/'#defs') in https://www.sqlite.org/sqlanalyze.html is not working properly. Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Feature request for the Shell Tool: .mode json

On Mon, Jan 22, 2018 at 12:50 AM, Stadin, Benjamin < benjamin.sta...@heidelberg-mobil.com> wrote: > wrote a tool to convert an arbitrary SQLite result set to properly typed > json key/value pairs, using the SQLite type affinity of the objects. > ... > while ((rc = sqlite3_step(readStmt)) ==

Re: [sqlite] Speed issue of SELECT in my application

On Wed, Jan 17, 2018 at 3:44 PM, David Raymond wrote: > sqlite_autoindex_t1_1 is the index created by the unique constraint in the > schema, it's not a temporary index. Thanks. That name fouled me indeed. --DD ___

Re: [sqlite] Speed issue of SELECT in my application

On Wed, Jan 17, 2018 at 1:06 PM, Nick wrote: > What is more important is that, I think it is a better way to establish my > tables according to however the source data is. Because SQLite stores all cells in-row, including large text and blob columns, a common

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

On Tue, Jan 16, 2018 at 4:10 PM, Richard Hipp wrote: > On 1/16/18, Matthew Towler wrote: > > > > Firstly, here is a C++11 example application. > > Does not compile. These are the errors: > > x2.cpp:53:2: warning: missing terminating " character > R"(

Re: [sqlite] Can I create a stealth index?

On Tue, Jan 16, 2018 at 3:38 PM, Deon Brewis wrote: > I have seen a few cases where a newly added index would start showing up > uninvited in old, previously tested queries and bring performance down by > an order of magnitude. ('analyze' doesn't fix it). > That seems quite

Re: [sqlite] Can an SQL script be built from within sqlite?

On Tue, Jan 16, 2018 at 10:51 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > That terminal app is still sandboxed. AFAIAA you essentially get access to > the application's data folder and you can add, create, delete, etc files > within it. > Sounds good enough, no? But really, what

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On Tue, Jan 9, 2018 at 12:35 PM, Eric Grange wrote: > > But then, if your range queries are based on a rank derived from value, > why > > not index value directly? You'd still get fast range queries based on > values, no? > > You get fast value range queries, but rank range

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

On Tue, Jan 9, 2018 at 11:26 AM, Eric Grange wrote: > So the order by is used to control the insertion order, so that the RANK > autoinc primary key ends up with natural rank order But then, if your range queries are based on a rank derived from value, why not index value

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On Mon, Jan 8, 2018 at 12:33 PM, R Smith wrote: > using any other index means a round-trip reading and hitting values in > THAT index, then returning and looking up the hit result in the rowid table index, and then reading the pages(s) from it and extracting the data -

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On Mon, Jan 8, 2018 at 11:39 AM, x wrote: > However, I’m still confused. Reading this https://sqlite.org/ > queryplanner.html suggests the table is stored in RowID order. So what > happens if I insert a record into Tbl with a lower ID than the existing 2.4 > million Ids? >

Re: [sqlite] Atomic DELETE index optimisation?

On Tue, Dec 19, 2017 at 6:05 PM, Simon Slavin wrote: > On 19 Dec 2017, at 4:15pm, Dinu wrote: > > 3) "Deleted" bit field - presumably the "soft delete" as you call it; > If you do try this, the 'bit' column should be declared as INTEGER and the >

Re: [sqlite] sites inaccessible

On Tue, Nov 28, 2017 at 10:07 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Sat, Oct 29, 2016 at 8:09 AM, Dan Kennedy <danielk1...@gmail.com> > wrote: > >> On 10/29/2016 12:28 PM, jungle Boogie wrote: >> >>> Hi Dr. Hipp, >>> >>&

Re: [sqlite] sites inaccessible

On Sat, Oct 29, 2016 at 8:09 AM, Dan Kennedy wrote: > On 10/29/2016 12:28 PM, jungle Boogie wrote: > >> Hi Dr. Hipp, >> >> Probably a low concern for you at 1:30am your time but I can't connect >> to fossil-scm.org or sqlite.org over port 80. >> >> $ curl

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

On Fri, Nov 24, 2017 at 9:51 AM, R Smith wrote: > I'm not even a big fan of Integer IDs, I think codes / UUIDs are best, but > obviously the speed/size gain with an integer key (especially INTEGER > PRIMARY KEY row-id alias in SQLite) can't be ignored. > > Disclaimer: This is

Re: [sqlite] Many ML emails going to GMail's SPAM

On Wed, Nov 22, 2017 at 3:08 PM, Wout Mertens wrote: > One more reason for some forum vs a mailing list: You can "like" a post > without spamming everyone, thus showing your appreciation to the poster and > surfacing interesting content for summarization algorithms. Or

Re: [sqlite] Many ML emails going to GMail's SPAM

On Tue, Nov 21, 2017 at 3:30 PM, Richard Hipp wrote: > On 11/21/17, Paul Sanderson wrote: > > Coincidence! I have just been in my gmail folder marking a load of > SQLite > > email as 'not spam' > > I've been seeing mailing list emails go to spam

[sqlite] Many ML emails going to GMail's SPAM

Just FYI. Not sure if something changed on the mailer's settings. Possibly/likely linked to GMail changing it's SPAM heuristics I guess. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] LSM1 vtable extension portability

I'm reading https://www.sqlite.org/src/artifact/529255dc70428900, and stumbled on: ** For FLOAT values, the content is the IEEE754 floating point value in ** native byte-order. This means that FLOAT values will be corrupted when ** database file is moved between big-endian and little-endian

Re: [sqlite] Using .testcase and .check in continuous integration test

On Tue, Oct 17, 2017 at 3:52 PM Dominique Devienne <ddevie...@gmail.com> wrote: > On Tue, Oct 17, 2017 at 3:16 PM, Lodewijk Duymaer van Twist < > lodew...@adesys.nl> wrote: > >> Thank you for investigating. You're fix works. Should I repost this as a >> bug with

Re: [sqlite] Most efficient way to detect on-disk change

On Wed, Nov 8, 2017 at 7:45 AM, Dan Kennedy wrote: > On 7 Nov 2017, at 6:53pm, David Raymond wrote: >> >> I think pragma data_version is what you're looking for. >>> http://www.sqlite.org/pragma.html#pragma_data_version >>> >> > I think it's the

[sqlite] shell.c no longer in SCM [WAS: [sqlite-announce] Version 3.21.0]

On Wed, Oct 25, 2017 at 3:54 AM, D. Richard Hipp wrote: > SQLite version 3.21.0 is now available on the SQLite website: > https://sqlite.org/releaselog/3_21_0.html > Version 3.21.0 is a regularly scheduled maintenance release containing > performance improvements, feature

Re: [sqlite] Using .testcase and .check in continuous integration test

On Tue, Oct 17, 2017 at 3:16 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > Thank you for investigating. You're fix works. Should I repost this as a > bug with your fix, or will this be picked up as is right now? > Glad it did. Just sit tight and again wait and see if Dr Hipp

Re: [sqlite] Using .testcase and .check in continuous integration test

On Tue, Oct 17, 2017 at 12:30 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > That would be an other way, but what I'm looking for is using the Command > Line Shell ".testcase" and ".check" method. > OK. That's new information :) > Create an example test file: > echo ".testcase

Re: [sqlite] SQLite3 on Windows

On Sat, Oct 14, 2017 at 10:47 PM, Phoenix wrote: > Dominique wrote: > Not to sound too snarky, but both questions can easily be answered > through experimentation. > You can also use http://www.dependencywalker.com/ to inspect DLL and > EXE dependencies. --DD

Re: [sqlite] Using .testcase and .check in continuous integration test

On Mon, Oct 16, 2017 at 12:32 PM, Lodewijk Duymaer van Twist < lodew...@adesys.nl> wrote: > I would like use .testcase and .check in our GitLab Continuous Integration > test. > > GitLab pipelines will check process return code for success or fail. > > Consider a simple test: >

Re: [sqlite] Odd query plan for without rowid table

On Mon, Oct 16, 2017 at 12:28 AM, Richard Hipp wrote: > > Fixed on trunk. https://sqlite.org/src/info/ee31c043 FYI, small typo in that commit. --DD line 1885 of where.c ** Return TRUE if all of the following are true: ** ** (1) X has the same or lower cost that Y ** (2)

Re: [sqlite] sqlite3_expert Status

On Wed, Oct 11, 2017 at 10:58 AM, Philip Bennefall wrote: > I was curious to know the status of the sqlite3_expert extension? I > followed its development with great interest earlier in the year and was > wondering if there are any plans to merge it to trunk? What kind of

Re: [sqlite] SQLite3 on Windows

On Tue, Oct 10, 2017 at 11:49 AM, Phoenix wrote: > I've downloaded the dll-win32 version of SQLite3 and have a couple of > questions. > > 1) Does sqlite3.exe require the .dll file or is it stand-alone? > > 2) Does the .dll need to be in a specific folder or is it okay

Re: [sqlite] [EXTERNAL] Re: sqlite3_stmt limitations

On Wed, Sep 20, 2017 at 1:41 PM, heribert wrote: > Do i have to open a database connection for each threat? Like > > rc = sqlite3_open("file::memory:?cache=shared", ); I believe so, yes. --DD PS: See also this thread:

Re: [sqlite] Can't find the source to sqldiff - And yes I have looked :)

On Tue, Sep 19, 2017 at 9:58 AM, Rob Willett wrote: > I'm trying to find the source to sqldiff and am struggling to locate it. > doc: https://sqlite.org/sqldiff.html bin: Part of https://sqlite.org/2017/sqlite-tools-win32-x86-3200100.zip from

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On Mon, Sep 18, 2017 at 11:41 AM, David Wellman wrote: > [...] there isn't an api that gives this value ** because ** SQLite > doesn't build the full answer set before returning from that first > sqlite3_step function call. > [DD] Well, the answer is more that

Re: [sqlite] [EXTERNAL] Number of rows in answer set

On Mon, Sep 18, 2017 at 10:37 AM, Hick Gunter wrote: > SQLite uses some nifty heuristics to estimate the number of rows it > expects to process while formulating a query plan. [...] > Is there any way to get at that estimate? That would be interesting to pre-size some result

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams wrote: > On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin wrote: > > Can you not do it with WITH ? I don’t really understand how WITH works > > but it would seem to evaluate its terms just once for each

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 5:38 PM, Warren Young <war...@etr-usa.com> wrote: > On Sep 14, 2017, at 8:49 AM, Dominique Devienne <ddevie...@gmail.com> > wrote: > > > > On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp <d...@sqlite.org> wrote: > >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > On 9/14/17, Darko Volaric wrote: > > I think people are missing the point, probably becuase it's not a great > > example. Consider the following statement: > > > > SELECT funca(slow(10)), funkb(slow(10)) >

Re: [sqlite] Intel 17

On Tue, Sep 12, 2017 at 3:56 PM, Richard Hipp <d...@sqlite.org> wrote: > On 9/11/17, Dominique Devienne <ddevie...@gmail.com> wrote: > > FYI, here are the modifications we did to a 3.19.3 amalgamation to build > > with the Intel 17 compiler. Note that we have a few mod

Re: [sqlite] Intel 17

On Tue, Sep 12, 2017 at 8:47 AM, Clemens Ladisch <clem...@ladisch.de> wrote: > Dominique Devienne wrote: > > sqlite3.c(17654): error #265: floating-point operation result is out of > range > > Could this error be reduced to a warning? > Probably. We typically co

Re: [sqlite] Intel 17

On Mon, Sep 11, 2017 at 4:49 PM, Richard Hipp <d...@sqlite.org> wrote: > On 9/11/17, Dominique Devienne <ddevie...@gmail.com> wrote: > > FYI, here are the modifications we did to a 3.19.3 amalgamation to build > > with the Intel 17 compiler. Note that we have a few mod

[sqlite] Intel 17

FYI, here are the modifications we did to a 3.19.3 amalgamation to build with the Intel 17 compiler. Note that we have a few modifs of our own, so line numbers might be off. --DD PS: I hope the formatting goes through. Was garbled when I converted to plain text, so trying in rich-text, sorry.

Re: [sqlite] Proof that a line has been modified

On Fri, Sep 8, 2017 at 12:29 AM, Nico Williams wrote: > > Is there a solution to that? > > You have these choices: > > - hash the whole SQLite3 file and record or sign such hash values for >approved DB files (this will let you detect all changes) > See also

Re: [sqlite] [EXTERNAL] Is there a performance difference between COUNT(*) and COUNT(name)

On Wed, Sep 6, 2017 at 7:56 AM, Hick Gunter wrote: > Count() needs to extract the field from the record, tallying > only those that are NOT NULL. > Technically it would not need to "extract" the field, only lookup the row header and see whether that field/column is NULL or

Re: [sqlite] Yes, NULL is zero, is it?

On Fri, Sep 1, 2017 at 9:08 AM, Olivier Mascia wrote: > > The (calling program) bug starts here above. > sql_statement_request.data() is not guaranteed to be zero-terminated (and > generally isn't). > FWIW, it is since std C++11, i.e. .data() and .c_str() are equivalent going

Re: [sqlite] Issue with updating database content (C++)

On Wed, Aug 30, 2017 at 5:48 PM, Jens Alfke wrote: > > On Aug 29, 2017, at 6:22 PM, Ali Dorri wrote: > > > > *char* *zSQL = *sqlite3_mprintf*("UPDATE BC set Signature = null and PK > = > > null where PK = '%q' ;", endoced_pub.c_str()); > > FYI, your

Re: [sqlite] Compiling spellfix for sqlite3

On Thu, Aug 24, 2017 at 1:24 AM, Keith Medcalf wrote: > >On Wed, Aug 23, 2017 at 6:11 PM, Keith Medcalf > wrote: > >Where's that pragma from Keith? Thanks, --DD > > They were added "experimentally" on July 7, 2017 Oh cool, that's great! thanks for the

Re: [sqlite] Compiling spellfix for sqlite3

On Wed, Aug 23, 2017 at 6:11 PM, Keith Medcalf wrote: > > sqlite> pragma function_list; > group_concat|1 > group_concat|1 > julianday|1 > julianday|1 > nullif|1 > nullif|1 > sqlite_compileoption_get|1 > sqlite_compileoption_get|1 > current_timestamp|1 > current_timestamp|1 >

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

On Wed, Aug 16, 2017 at 4:15 PM, Keith Medcalf wrote: > Setting "multithreaded" mode disables these checks in the SQLite3 library > and it is up to the application level code to ensure the single-entrance > per connection is enforced at the application level. If the

Re: [sqlite] Problem with the new pointer-passing interface

On Fri, Aug 4, 2017 at 11:37 AM, Ulrich Telle wrote: > Regarding the development of SQLite extensions (UDFs) and using the > pointer-binding interface to communicate between different extensions (AFAIK one of the reasons to introduce the new pointer-binding interface) > the

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams wrote: > On Tue, Aug 01, 2017 at 10:56:47AM -0700, Matt Chambers wrote: > > load_extension() has the very sensible behavior of: > > > So for example, if "samplelib" cannot be loaded, then names like > > > "samplelib.so" or

Re: [sqlite] New draft document on the new pointer-passing interfaces

On Mon, Jul 24, 2017 at 1:54 PM, Richard Hipp wrote: > https://www.sqlite.org/draft/bindptr.html Thanks. Very helpful. Still unsure whether not having a destructor D for pointer P is a good thing though. The text explicitly says the pointer is "destroyed" when not flowing

Re: [sqlite] Draft docs typo

On Mon, Jul 17, 2017 at 5:43 PM, petern wrote: > Speaking of type string lifetime, what about pointer lifetime management? > > I think you've overlooked the pointer lifetime problem for > sqlite3_result_pointer(C,P,T). This form, unlike the blob form, lacks the >

Re: [sqlite] Any way to avoid scanning a large table several times?

On Thu, Jul 6, 2017 at 4:17 PM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > >> >> select sum(code_type == 'SET') as "#sets", >>sum(code_type == 'CST') as

Re: [sqlite] Any way to avoid scanning a large table several times?

On Thu, Jul 6, 2017 at 3:56 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Dominique Devienne wrote: > > It's a group-by query, so despite using the index, all rowids for the > only > > 4 different "index entries" must still be counted, > > and that's

Re: [sqlite] Any way to avoid scanning a large table several times?

On Thu, Jul 6, 2017 at 3:50 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Dominique Devienne wrote: > > On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote: > >> You could put kcounts into a temporary table. > > > > I could it in a table, bu

Re: [sqlite] Any way to avoid scanning a large table several times?

On Thu, Jul 6, 2017 at 2:49 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 6 Jul 2017, at 12:32pm, Dominique Devienne <ddevie...@gmail.com> wrote: > > Actually not that much apparently. > > No Simon, I didn't have an index on code_type. > > In fact keys is it

Re: [sqlite] Any way to avoid scanning a large table several times?

On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch <clem...@ladisch.de> wrote: > Dominique Devienne wrote: > > with > > kcounts(t, c) as ( > > select code_type, count(*) > >from keys > > group by code_type > > ), > > ... > > sel

[sqlite] Any way to avoid scanning a large table several times?

I have a view gathering statistics from 3 different tables, one of which is largish (~ 2M rows). The view is similar to this: with kcounts(t, c) as ( select code_type, count(*) from keys group by code_type ), ... select ..., (select c from kcounts where t = 'SET') as "#sets",

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

On Wed, May 10, 2017 at 1:35 PM, Gwendal Roué wrote: > > Le 9 mai 2017 à 15:41, Gwendal Roué a écrit : > >> How are you going to handle TRIGGERs ? > > > > That's a very good question. > > Very good news: foreign keys and triggers are 100% handled

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

On Tue, May 9, 2017 at 3:02 PM, Simon Slavin wrote: > On 9 May 2017, at 7:23am, Gwendal Roué wrote: > > As a reminder, I intend to use the authorisation system in order to tell > if a statement has an opportunity to impact on another statement, as a

Re: [sqlite] bug for user auth

2017-05-03 10:56 GMT+02:00 XIAO DAI : > I have compiled SQLite v3.15.2 with the functions > "sqlite3_user_authenticate, it runs well, for all the versions > 3.15.2, I > can add the logins into the database, but sqlite(shell.c) does NOT ask for > the authentication. > From

Re: [sqlite] -shm grows with large transaction

On Wed, Apr 26, 2017 at 5:34 PM, Kim Gräsman wrote: > Den 26 apr. 2017 3:45 em skrev "Richard Hipp" : > > > On 4/26/17, Richard Hipp wrote: > > > That would imply you are changing about 5 million pages. > > Great, that means the

Re: [sqlite] Performances and Foreign keys

On Mon, Apr 24, 2017 at 2:31 PM, Bubu Bubu wrote: > Foreign keys have been implemented in sqlite since 3.6.19. My boss has > always been reluctant to use this mechanism in our development under the > pretext of performance loss. He told me he read that somewhere once, but he

Re: [sqlite] Security vulnerabilities prevalent in web tutorials for PHP, javascript, etc..

On Mon, Apr 24, 2017 at 3:33 AM, Keith Medcalf wrote: > > I’m curious about binding as an idea. [...] > [...] The EXEC SQL interface has all but disappeared in most languages > [...] Oracle still supports https://en.wikipedia.org/wiki/Pro*C but that's pure client-side,

<    1   2   3   4   5   6   7   8   >