Re: [sqlite] More built-in functions for basic math

On Thursday, 9 March, 2017 07:30, Dominique Devienne wrote: > On Thu, Mar 9, 2017 at 3:05 PM, Keith Christian > wrote: > > At some point it's time to use a different database engine or offload > > to other code. Sqlite could easily burgeon to

Re: [sqlite] How to configure size of shared-cache in SQLite?

On Wednesday, 8 March, 2017 03:45, Qiu Xiafei wrote: > SQLite provide a shared-cache mode > (https://www.sqlite.org/sharedcache.html) > which will help to reduce cache size among multiple connections within a > process. But how to configure the size of the shared cache? I've

Re: [sqlite] sqlite3 feature or regression

On 03/09/2017 10:46 PM, James K. Lowden wrote: On Tue, 7 Mar 2017 19:04:47 +0100 Vermes Mátyás wrote: It is also unnecessarily complex and slow. The script demonstrates a regression (a bug). It is written in Ruby so that everybody can run it, and see its _results_. It is

Re: [sqlite] More built-in functions for basic math

On Thu, Mar 9, 2017 at 6:05 PM, Keith Medcalf wrote: > On Thursday, 9 March, 2017 07:30, Dominique Devienne > wrote: > > Last but not least, it's trivial to not use what's there and available > and > > easily disabled at compiled time, or ignored at

Re: [sqlite] More built-in functions for basic math

On Thu, 9 Mar 2017 10:45:36 +0100 Dominique Devienne wrote: > I find that I'm often missing basic mathematical functions in the > default shell. $ sqlite3 -header <<< 'select typeof(1/0) as "how many";' how many null Until SQLite deals with math as math, what is the point

Re: [sqlite] sqlite3 feature or regression

On Tue, 7 Mar 2017 19:04:47 +0100 Vermes Mátyás wrote: > > It is also unnecessarily complex and slow. > > The script demonstrates a regression (a bug). It is written in Ruby > so that everybody can run it, and see its _results_. It is absolutely > not interesting that it is

Re: [sqlite] More built-in functions for basic math

On Thu, Mar 9, 2017 at 3:04 PM, Jay Kreibich wrote: > The main downside is that SQLite builds on a ton of platforms, including > embedded devices. In some cases, those platforms don’t even support > floating point numbers, never mind high-level math functions. It would add > a

Re: [sqlite] More built-in functions for basic math

On Thu, Mar 9, 2017 at 3:05 PM, Keith Christian wrote: > At some point it's time to use a different database engine or offload > to other code. Sqlite could easily burgeon to the size of the other > databases if everything asked for was included. Where, then, would

Re: [sqlite] Producing SQLITE_MISMATCH

On 2017-03-09 09:35:00, "Dan Kennedy" wrote: On 03/09/2017 09:23 PM, J. King wrote: I'm trying to write a minimal test case to produce an SQLITE_MISMATCH response. The documentation suggests the following should be sufficient: CREATE TABLE test(id integer primary

Re: [sqlite] Producing SQLITE_MISMATCH

On 03/09/2017 09:23 PM, J. King wrote: I'm trying to write a minimal test case to produce an SQLITE_MISMATCH response. The documentation suggests the following should be sufficient: CREATE TABLE test(id integer primary key) WITHOUT ROWID; INSERT INTO test(id) VALUES('ook'); However, SQLite

Re: [sqlite] Producing SQLITE_MISMATCH

On 03/09/2017 09:39 PM, J. King wrote: On 2017-03-09 09:35:00, "Dan Kennedy" wrote: On 03/09/2017 09:23 PM, J. King wrote: I'm trying to write a minimal test case to produce an SQLITE_MISMATCH response. The documentation suggests the following should be sufficient:

Re: [sqlite] More built-in functions for basic math

On Thu, Mar 9, 2017 at 2:49 PM, Eric Grange wrote: > A bonus of having them defined in the core is that it avoids the minor > inconsistencies that are bound to arise in custom implementations (starting > with the name of math functions) > Yep. > Main downside is probably

Re: [sqlite] More built-in functions for basic math

On 3/9/17, Keith Medcalf wrote: > > note that fileio.c and shathree.c are inlined into shell.c, so in order to > remove them I have to modify shell.c to ifdef them out when I am building. > Seriously? I have no trouble loading the external fileio.c and shathree.c extensions

[sqlite] Aggregate and Scalar functions with the same name

Ok, I see why you cannot use the same name. Queries of the form: select func(x) from y; would be ambiguous if there were both an aggregate func and a scalar func, and the vdbe code generator uses the type of func (whether aggregate or scaler) to determine whether to interpret the query as an

Re: [sqlite] More built-in functions for basic math

I get complaints from the compiler about duplicate definitions of macro's and functions ... As an aside, I am trying to walk db->aFunc as follows: sqlite3 *db = sqlite3_context_db_handle(context); Hash *h = &(db->aFunc); HashElem *p; FuncDef *d; char *functype; for (p =

Re: [sqlite] Aggregate and Scalar functions with the same name

Note that you can have aggregates and scalars with the same name, as long as they have a different number of arguments. This comes up with the min() and max() functions. The two-or-more argument versions of these functions are scalars. The one-argument version is an aggregate. On 3/9/17, Keith

Re: [sqlite] sqlite3 feature or regression

tclsh, aside from the inconvenience of prefixing every sql statement with "db eval {", looks like a great way to gain the equivalent functionality of scalar output stored procedures compared to the plain vanilla sqlite shell. Is there anything in the works for the tcl bindings to define/export

Re: [sqlite] Producing SQLITE_MISMATCH

On 2017-03-09 09:59:25, "Dan Kennedy" wrote: On 03/09/2017 09:39 PM, J. King wrote: Out of curiosity, can you provide some insight as to why it does not produce a mismatch for a WITHOUT ROWID table? The main b-tree in which data for a "rowid table" - any table that is

Re: [sqlite] SQL help: by-range aggregation

On Wed, Mar 8, 2017 at 6:38 PM, R Smith wrote: > > On 2017/03/08 5:35 PM, Dominique Devienne wrote: > >> >> Now I only need a CTE to dynamically generate the ranges, >> > > Well, that seems like fun! > If I may... > Nice! Thanks Ryan. --DD PS: If we ever meet, your

[sqlite] More built-in functions for basic math

I find that I'm often missing basic mathematical functions in the default shell. Many SQLite clients add many, but given that the official SQLite shell misses them you can't use them in views for predefined "reports" within the DB file itself, for example. There's [1] which is 50KB, but only a

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Yes, but we can live and hope for the day a sqlite project roadmap is disclosed with eponymous vtab API which supports completely dynamic column outputs and a fully featured API for row valued language atoms. When that day comes pivot tables, matrix operations, and other row type introspection

Re: [sqlite] SQL help: by-range aggregation

On 2017/03/09 10:11 AM, Dominique Devienne wrote: Nice! Thanks Ryan. --DD PS: If we ever meet, your reasonably-priced beverage of choice is on me :) You're welcome, and safe, since I'm teetotal - which means I totally only drink tea. :) (Been this way since that fatal PUI incident -

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > commit of the subsequent unrelated transaction fails with error=5 > (SQLITE_BUSY) if the previous open sqlite3_blob object hasn't been > closed. > > This fails: > > sqlite3_blob_open // table b with rowids unique in db > sqlite3_blob_write // table b > BEGIN > UPDATE // table a >

Re: [sqlite] thousand separator for printing large numbers

On Fri, Feb 10, 2017 at 10:46 AM, Dominique Devienne wrote: > What would be the [...] most efficient way to do this (NDR: thousand > separator) in SQL? > Here's a little demo that evaluates the 3 approaches proposed in this thread. Formatting 10M numbers takes ~2s using

Re: [sqlite] SQL help: by-range aggregation

On Wed, Mar 8, 2017 at 6:08 PM, Simon Slavin wrote: > > On 8 Mar 2017, at 3:35pm, Dominique Devienne wrote: > > > > Then the results where in alphabetical order of bucket names, > > so I had to re-join on ranges to order by ranges.low. > > You should

Re: [sqlite] More built-in functions for basic math

I second this - Been having a hard time making basic queries with a simple x^y function in SQL for SQLite since there is no guarantee what the end-user's system will have it compiled-in. I can version-check or version-enforce easily, but compile-option check or enforce is a no-go. If we can

[sqlite] How to configure size of shared-cache in SQLite?

SQLite provide a shared-cache mode (https://www.sqlite.org/sharedcache.html) which will help to reduce cache size among multiple connections within a process. But how to configure the size of the shared cache? I've tried PRAGMA cache_size = 536870912. But no matter how much space i offered, it

Re: [sqlite] sqlite3 feature or regression

> > The script ... is written in Ruby so that > > everybody can run it, and see its _results_. > > The point is that I cannot run your Ruby script on my Ubuntu desktop > because I get an error: Perhaps you have not installed Ruby. sudo apt-get install ruby ruby-sqlite3 Then

Re: [sqlite] sqlite3 feature or regression

> Why are you wanting me to work so hard at this? Under no circumstances work hard, I rewrote the program for you to C. The results are attached also, so you do not have to compile it, if you do not want. You can download it from http://comfirm.hu/pub/sqlite3-regression.tar.gz. -- Vermes

Re: [sqlite] sqlite3 feature or regression

Well, as I wrote, I am not a Ruby programmer. I found this phenomena first in a CCC test program. CCC is a translator, which translate a Clipper-like language to C. So there is a state of a CCC program, which can be considered as pure C. There is no Ruby in it at all. Ruby version was only

[sqlite] Wrong date with release 3.16.1

Hey, It's something really little, but on the newspage , the date for release 3.16.1 is wrong. It's showing 2016-01-03, but must be 2017-01-03. Greetings, Pieter ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Wrong date with release 3.16.1

On 3/8/17, Pieter Parmentier wrote: > > It's something really little, but on the newspage , the date for release > 3.16.1 is wrong. > Fixed now. Thanks. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] thousand separator for printing large numbers

On Thu, Mar 9, 2017 at 11:59 AM, Dominique Devienne wrote: > On Fri, Feb 10, 2017 at 10:46 AM, Dominique Devienne > wrote: > >> What would be the [...] most efficient way to do this (NDR: thousand >> separator) in SQL? >> > > Here's a little demo that

Re: [sqlite] More built-in functions for basic math

A bonus of having them defined in the core is that it avoids the minor inconsistencies that are bound to arise in custom implementations (starting with the name of math functions) Main downside is probably not going to be the size, but that it reserves more names, and may conflict with existing

Re: [sqlite] More built-in functions for basic math

We all know this but it bears repeating: At some point it's time to use a different database engine or offload to other code. Sqlite could easily burgeon to the size of the other databases if everything asked for was included. Where, then, would we get a small but still functional SQL engine?

Re: [sqlite] How to configure size of shared-cache in SQLite?

Qiu Xiafei wrote: > SQLite provide a shared-cache mode (https://www.sqlite.org/sharedcache.html) > which will help to reduce cache size among multiple connections within a > process. But how to configure the size of the shared cache? There is not special shared cache; the shared-cache mode just

Re: [sqlite] sqlite3 feature or regression

On 3/8/17, Vermes Mátyás wrote: > I rewrote the program for you to C. Thank you for the translation. Below is the equivalent program in 34 lines of TCL. (Compare to 101 lines of Ruby and 430 lines of C++. Everybody: If you are unfamiliar with the TCL programming language,

Re: [sqlite] sqlite3 feature or regression

On 03/09/2017 06:28 AM, Vermes Mátyás wrote: Why are you wanting me to work so hard at this? Under no circumstances work hard, I rewrote the program for you to C. The results are attached also, so you do not have to compile it, if you do not want. You can download it from

Re: [sqlite] More built-in functions for basic math

The main downside is that SQLite builds on a ton of platforms, including embedded devices. In some cases, those platforms don’t even support floating point numbers, never mind high-level math functions. It would add a mess of new #defs. There used to be a standard math extension that

[sqlite] Producing SQLITE_MISMATCH

I'm trying to write a minimal test case to produce an SQLITE_MISMATCH response. The documentation suggests the following should be sufficient: CREATE TABLE test(id integer primary key) WITHOUT ROWID; INSERT INTO test(id) VALUES('ook'); However, SQLite happily accepts the second statement. Is