SQL Features That SQLite Does Not Implement

classic Classic list List threaded Threaded
50 messages Options
123
Reply | Threaded
Open this post in threaded view
|

SQL Features That SQLite Does Not Implement

skywalk
I'm often scrambling to decide whether to do complicated queries in SQL or
my own code?
...Getting oh so close to a working query, only to fail at function not
defined?!!

Please add a note to the omitted page that many basic math functions are
NOT supported. (sqrt,mod,power,stdev,etc.)
https://www.sqlite.org/omitted.html
I am curious why these are not available when there are switches for much
larger search functions like FTS345?

...now reading how to build my own extensions...
Thanks for SQLite!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Simon Slavin-3
On 22 May 2019, at 6:51pm, [hidden email] wrote:

> Please add a note to the omitted page that many basic math functions are NOT supported. (sqrt,mod,power,stdev,etc.)

Which ones should the documentation mention ?  Can you find us a specification of SQL which includes that these functions ?  I'm not talking about a SQL engine you can use, but one of the SQL standards which mentions them, so we know what SQLite leaves out.

Compare SQLite with PostgreSQL:

<https://www.postgresql.org/docs/current/functions-math.html>

PostgreSQL lacks sqrt (though you can do it with power() or with '|/') and stdev (which you can't do).  Both SQLite and PostgreSQL do modulo, using the '%' operator.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Donald Griggs
In reply to this post by skywalk
Regarding: ... many basic math functions are NOT supported.
(sqrt,mod,power,stdev,etc.)...I am curious why these are not available  ...now
reading how to build my own extensions...

I'm not addressing the documentation question, but extended math functions
have been available since 2010.

On the *contributors* page, https://www.sqlite.org/contrib  Liam Healy
contributed
a runtime-loadable extension.

Windows users can even find pre-compiled versions at:
https://github.com/sqlitebrowser/sqlitebrowser/files/1559077/extension-functions.zip


extension-functions.c
<https://www.sqlite.org/contrib/download/extension-functions.c?get=25>
(50.96 KB)
contributed by Liam Healy on 2010-02-06 15:45:07

Provide mathematical and string extension functions for SQL queries using
the loadable extensions mechanism. Math: acos, asin, atan, atn2, atan2,
acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot,
cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil,
floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim,
trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate:
stdev, variance, mode, median, lower_quartile, upper_quartile.


>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

jreidthompson
In reply to this post by Simon Slavin-3
On Wed, 2019-05-22 at 19:05 +0100, Simon Slavin wrote:

> [EXTERNAL SOURCE]
>
>
>
> On 22 May 2019, at 6:51pm, [hidden email] wrote:
>
> > Please add a note to the omitted page that many basic math functions are NOT supported. (sqrt,mod,power,stdev,etc.)
>
> Which ones should the documentation mention ?  Can you find us a specification of SQL which includes that these functions ?  I'm not talking about a SQL engine you can use, but one of the SQL
> standards which mentions them, so we know what SQLite leaves out.
>
> Compare SQLite with PostgreSQL:
>
> <
>
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_current_functions-2Dmath.html&d=DwIGaQ&c=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM&r=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8&m=jbjQXvGZh8bReMAfosQNap-8S64F4Q3WwjrDjOA9gGM&s=F9oXfi9o0vpUEQ6Qv_pxcKMHwuLHHbrqTRyq_uqq7_8&e=
> >
>
> PostgreSQL lacks sqrt (though you can do it with power() or with '|/') and stdev (which you can't do).  Both SQLite and PostgreSQL do modulo, using the '%' operator.
> _______________________________________________



the referenced postgresql page lists sqrt in Mathematical Functions
sqrt(dp or numeric) (same as input) square root sqrt(2.0) 1.4142135623731




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

R Smith-2
In reply to this post by skywalk
How we can anyone document functions that do not exist?, they are
infinite and many of them (most) are not currently known to humanity.
Also, if somewhere it said "SQLite cannot do sqr() it would lie - sure
vanilla sqlite might not, but there are many ways in which it does.

!. Add-ons - there are many great sqlite add-ons (for lack of a better
word) via externally run-time loadable extensions.
See: https://www.sqlite.org/loadext.html

You can add any of this easily to your sqlite, or if you compile your
own, even add them to the source code. One great add-on is precisely the
math library that supports all (and more) of the mentioned functions.

Example extension via code:
 From this Page: https://www.sqlite.org/contrib

extension-functions.c
<https://www.sqlite.org/contrib/download/extension-functions.c?get=25>
(50.96 KB) contributed by Liam Healy on 2010-02-06 15:45:07

    Provide mathematical and string extension functions for SQL queries
    using the loadable extensions mechanism. Math: acos, asin, atan,
    atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos,
    sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign,
    sqrt, square, ceil, floor, pi. String: replicate, charindex,
    leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper,
    padl, padr, padc, strfilter. Aggregate: stdev, variance, mode,
    median, lower_quartile, upper_quartile.


Apart from that, you can add your own functions to sqlite (which may be
hard if you are working through a wrapper, but real easy if you access
the library or code directly).
See: https://www.sqlite.org/c3ref/create_function.html


Good luck and Happy SQLite-ing,
Ryan

On 2019/05/22 7:51 PM, [hidden email] wrote:

> I'm often scrambling to decide whether to do complicated queries in SQL or
> my own code?
> ...Getting oh so close to a working query, only to fail at function not
> defined?!!
>
> Please add a note to the omitted page that many basic math functions are
> NOT supported. (sqrt,mod,power,stdev,etc.)
> https://www.sqlite.org/omitted.html
> I am curious why these are not available when there are switches for much
> larger search functions like FTS345?
>
> ...now reading how to build my own extensions...
> Thanks for SQLite!
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

jreidthompson
In reply to this post by jreidthompson
On Wed, 2019-05-22 at 14:24 -0400, Reid Thompson wrote:

> On Wed, 2019-05-22 at 19:05 +0100, Simon Slavin wrote:
> > [EXTERNAL SOURCE]
> >
> >
> >
> > On 22 May 2019, at 6:51pm, [hidden email] wrote:
> >
> > > Please add a note to the omitted page that many basic math functions are NOT supported. (sqrt,mod,power,stdev,etc.)
> >
> > Which ones should the documentation mention ?  Can you find us a specification of SQL which includes that these functions ?  I'm not talking about a SQL engine you can use, but one of the SQL
> > standards which mentions them, so we know what SQLite leaves out.
> >
> > Compare SQLite with PostgreSQL:
> >
> > <
> >
>
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_current_functions-2Dmath.html&d=DwIGaQ&c=w8fEmIi-B5lsC0MnOiGTloUx5YmN70-3jVCmqqbXNxM&r=4r1gVE34nFB7YgioINuzq1cdGrlIBszDh26sKYP7ux8&m=jbjQXvGZh8bReMAfosQNap-8S64F4Q3WwjrDjOA9gGM&s=F9oXfi9o0vpUEQ6Qv_pxcKMHwuLHHbrqTRyq_uqq7_8&e=

> >
> > PostgreSQL lacks sqrt (though you can do it with power() or with '|/') and stdev (which you can't do).  Both SQLite and PostgreSQL do modulo, using the '%' operator.
> > _______________________________________________
>
>
> the referenced postgresql page lists sqrt in Mathematical Functions
> sqrt(dp or numeric) (same as input) square root sqrt(2.0) 1.4142135623731
>
>
>

additionally as info,
 https://www.postgresql.org/docs/9.1/functions-aggregate.html

 lists several stddev variants in

 Table 9-44. Aggregate Functions for Statistics


ala
[local:/home/rthompso/var]:5432 lite@lite=# select stddev(column1) from (select * from (values (10),(10),(100),(10)) as points) as foo;
       stddev        
---------------------
 45.0000000000000000
(1 row)

[local:/home/rthompso/var]:5432 lite@lite=# select stddev_pop(column1) from (select * from (values (10),(10),(100),(10)) as points) as foo;
     stddev_pop      
---------------------
 38.9711431702997391
(1 row)

[local:/home/rthompso/var]:5432 lite@lite=# select stddev_samp(column1) from (select * from (values (10),(10),(100),(10)) as points) as foo;
     stddev_samp    
---------------------
 45.0000000000000000
(1 row)





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

skywalk
Yes, I expected some pushback. However, my post was to save the next person
having to search for answers. I did not expect to requote the top line of
the page I listed?
"SQLite implements most of the common features of SQL. Rather than try to
list all the features of SQL that SQLite does support, *it is much easier
to list those that it does not.* Unsupported features of SQL are shown
below."

I am asking for a followup to the missing features list.
Similar to this: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

Yes, I am aware of the extension capabilities and will augment them with my
own.
Expect more questions in that effort. ;)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Keith Medcalf
In reply to this post by skywalk

On Wednesday, 22 May, 2019 11:51, [hidden email] wrote:

>Please add a note to the omitted page that many basic math functions
>are NOT supported. (sqrt,mod,power,stdev,etc.)

Traditionally "math library" functions provided by the various language runtimes were not included becase this would introduce dependancies on a "math library".  While this is available on *most* platforms, it is not available on *all* platforms which SQLite3 can be compiled for out of the box.  Furthermore the implementation of some transcendentals may be intrinsic on some CPU's and require huge amounts of library code on others.  Statistical functions are not included because, well, they require complex implementations to get right.

Moreover, even the builtin functions are "lite" (the round function does not round properly for instance (it does grade-school 4/5 rounding rather than half-even rounding), the average function is rather simple in implementation and suffers from trivally triggered sources of computational error (it uses sum/count rather than successive approximation to the mean), and many other limitations exist in the builtin implementations of many functions).

All of these issues can be "fixed" however, all you need to do is add the necessary code via the extension mechanism to add whatever functionality you require using whatever numerical methods you determine are suitable for your needs.  For example, I have added default support via the extension mechanism (and the EXTRA_INIT hook) to make all the distributed extensions available on every connection, to add all the standard platform math functions, to add a bunch of statistical functions, several platform APIs (Windows in this case), and to "fix" the builtin round, datetime (to include proper support for instant times and timezone manipulation using the standard IANA timezone database), and added support for basic Unicode nocase and noaccent collations without using the whole ICU library.

The downside of this is that the implementation of all these "goodies" quadruples the size of the base engine code (sqlite3.obj) and it is no longer "Lite".  There are other drawbacks as well.  For example, it is difficult to make many advanced numerical calculation methods (aggregates) compatible with window functions as currently implemented.

About the only thing that is missing from SQLite3 is the ability to declare and implement "user defined types" in a fully integrated way (such as was added to DB2 back in the late 80's early 90's, and which I do not think anyone else has implemented as nicely anywhere else).

Really, the issue is that SQLite3 is an SQL based relational storage manager, and it implements this function very well.  It does not provide a huge array of accoutrements that you may see with other more ex$pen$ive RDMS systems, but does provide the ability to add (most of) those accoutrements if you wish.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

James K. Lowden
On Wed, 22 May 2019 14:20:11 -0600
"Keith Medcalf" <[hidden email]> wrote:

>  (such as was added to DB2 back in the late 80's early 90's, and
> which I do not think anyone else has implemented as nicely anywhere
> else)

That's an interesting aside.  It would make an interesting OT thread,
if you're inclined to start it.  ;-)  

I've always thought user-defined types were unnecessary except as a
convenience.  There are few new primitive types; most user-defined
types I can think of are "structures" -- sets of columns -- that one
might like to name and constrain as a new type that may appear in many
tables.  About the only primitive type I can imagine are mathematical:
complex numbers or exact numeric representations.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Brent Wood
Umm...
geometries I use with Postgis & Spatialite - I don't have a database without them.
Can anyone come up with a data domain for which location has no relevance?
I used to think I had some (like taxonomies & dictionaries), but I've been corrected in each case :-)

also IP addresses
Brent Wood
      From: James K. Lowden <[hidden email]>
 To: [hidden email]
 Sent: Thursday, May 23, 2019 10:56 AM
 Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
   
On Wed, 22 May 2019 14:20:11 -0600
"Keith Medcalf" <[hidden email]> wrote:

>  (such as was added to DB2 back in the late 80's early 90's, and
> which I do not think anyone else has implemented as nicely anywhere
> else)

That's an interesting aside.  It would make an interesting OT thread,
if you're inclined to start it.  ;-) 

I've always thought user-defined types were unnecessary except as a
convenience.  There are few new primitive types; most user-defined
types I can think of are "structures" -- sets of columns -- that one
might like to name and constrain as a new type that may appear in many
tables.  About the only primitive type I can imagine are mathematical:
complex numbers or exact numeric representations. 

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Jens Alfke-2
In reply to this post by James K. Lowden


> On May 22, 2019, at 3:55 PM, James K. Lowden <[hidden email]> wrote:
>
> I've always thought user-defined types were unnecessary except as a convenience.


User-defined types are quite important if you’re doing fancy stuff in user-defined functions, where data that’s stored in tables as blobs has an internal structure visible to those functions. (JSON is a good example, and the reason why SQLite added its ‘subtypes’ feature.)

The biggest limitation with ‘subtypes’ is that they only exist during the execution of a query; they’re not visible in the final results. So when using the result of a query, one only sees a blob, with no indication what subtype of blob it is. In the project I work on, this has caused us some problems and required inelegant workarounds.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Have SQLite handle values of my own type

Simon Slavin-3
Since there are people posting who appear know about these things …

Suppose I want SQlite to handle my own type.  Or to do its best to simulate that.  IP address, x/y location, something like that.  What should I be doing ?  Do I store BLOBs and define my own COLLATEs ?  Or didn't I read somewhere that COLLATE works only on TEXT ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Have SQLite handle values of my own type

Keith Medcalf

On Wednesday, 22 May, 2019 19:06, Simon Slavin <[hidden email]> wrote:

>Since there are people posting who appear know about these things …

>Suppose I want SQlite to handle my own type.  Or to do its best to
>simulate that.  IP address, x/y location, something like that.  What
>should I be doing ?  Do I store BLOBs and define my own COLLATEs ?
>Or didn't I read somewhere that COLLATE works only on TEXT ?

Technically, COLLATE only works on TEXT.  Most people declare their own types as binary blobs and the programmer has to keep track of what is in there and how to work with it.  In the case of IPAddresses it is a little simpler, since they are really just text strings, so writing an IPADDRESS collation to apply to text strings that contain IP Addresses and a function like IPSubnetContains is pretty straightforward.  Other complex data types are more difficult since there is not builtin support for types.

http://www.dessus.com/files/ipaddress.c

The code is a little inefficient but it works ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.







_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Thomas Kurz
In reply to this post by skywalk
Before starting to support SQL2016 features, I would suggest support for missing features of older SQL standard versions first ;)

----- Original Message -----
From: [hidden email] <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Wednesday, May 22, 2019, 21:29:40
Subject: [sqlite] SQL Features That SQLite Does Not Implement

Yes, I expected some pushback. However, my post was to save the next person
having to search for answers. I did not expect to requote the top line of
the page I listed?
"SQLite implements most of the common features of SQL. Rather than try to
list all the features of SQL that SQLite does support, *it is much easier
to list those that it does not.* Unsupported features of SQL are shown
below."

I am asking for a followup to the missing features list.
Similar to this: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

Yes, I am aware of the extension capabilities and will augment them with my
own.
Expect more questions in that effort. ;)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Thomas Kurz
In reply to this post by Keith Medcalf
I agree in that not every math function can be included by default. My problem, however, is that I cannot know whether a user uses my self-compiled version with built-in extension-functions.c, or a downloaded version from sqlite.org.

It would be very, very helpful (especially regarding views!) to have some kind of a "check function" so that one could write

SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END FROM bar


----- Original Message -----
From: Keith Medcalf <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Wednesday, May 22, 2019, 22:20:11
Subject: [sqlite] SQL Features That SQLite Does Not Implement


On Wednesday, 22 May, 2019 11:51, [hidden email] wrote:

>Please add a note to the omitted page that many basic math functions
>are NOT supported. (sqrt,mod,power,stdev,etc.)

Traditionally "math library" functions provided by the various language runtimes were not included becase this would introduce dependancies on a "math library".  While this is available on *most* platforms, it is not available on *all* platforms which SQLite3 can be compiled for out of the box.  Furthermore the implementation of some transcendentals may be intrinsic on some CPU's and require huge amounts of library code on others.  Statistical functions are not included because, well, they require complex implementations to get right.

Moreover, even the builtin functions are "lite" (the round function does not round properly for instance (it does grade-school 4/5 rounding rather than half-even rounding), the average function is rather simple in implementation and suffers from trivally triggered sources of computational error (it uses sum/count rather than successive approximation to the mean), and many other limitations exist in the builtin implementations of many functions).

All of these issues can be "fixed" however, all you need to do is add the necessary code via the extension mechanism to add whatever functionality you require using whatever numerical methods you determine are suitable for your needs.  For example, I have added default support via the extension mechanism (and the EXTRA_INIT hook) to make all the distributed extensions available on every connection, to add all the standard platform math functions, to add a bunch of statistical functions, several platform APIs (Windows in this case), and to "fix" the builtin round, datetime (to include proper support for instant times and timezone manipulation using the standard IANA timezone database), and added support for basic Unicode nocase and noaccent collations without using the whole ICU library.

The downside of this is that the implementation of all these "goodies" quadruples the size of the base engine code (sqlite3.obj) and it is no longer "Lite".  There are other drawbacks as well.  For example, it is difficult to make many advanced numerical calculation methods (aggregates) compatible with window functions as currently implemented.

About the only thing that is missing from SQLite3 is the ability to declare and implement "user defined types" in a fully integrated way (such as was added to DB2 back in the late 80's early 90's, and which I do not think anyone else has implemented as nicely anywhere else).

Really, the issue is that SQLite3 is an SQL based relational storage manager, and it implements this function very well.  It does not provide a huge array of accoutrements that you may see with other more ex$pen$ive RDMS systems, but does provide the ability to add (most of) those accoutrements if you wish.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Thomas Kurz
In reply to this post by James K. Lowden
> exact numeric representations.  

+1 for that as had already been in consideration for version 4

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Keith Medcalf
In reply to this post by Thomas Kurz

select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>
>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.
>
>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write
>
>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar
>
>
>----- Original Message -----
>From: Keith Medcalf <[hidden email]>
>To: SQLite mailing list <[hidden email]>
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>On Wednesday, 22 May, 2019 11:51, [hidden email] wrote:
>
>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)
>
>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library".  While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box.  Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others.  Statistical functions are not
>included because, well, they require complex implementations to get
>right.
>
>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).
>
>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs.  For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the whole ICU library.
>
>The downside of this is that the implementation of all these
>"goodies" quadruples the size of the base engine code (sqlite3.obj)
>and it is no longer "Lite".  There are other drawbacks as well.  For
>example, it is difficult to make many advanced numerical calculation
>methods (aggregates) compatible with window functions as currently
>implemented.
>
>About the only thing that is missing from SQLite3 is the ability to
>declare and implement "user defined types" in a fully integrated way
>(such as was added to DB2 back in the late 80's early 90's, and which
>I do not think anyone else has implemented as nicely anywhere else).
>
>Really, the issue is that SQLite3 is an SQL based relational storage
>manager, and it implements this function very well.  It does not
>provide a huge array of accoutrements that you may see with other
>more ex$pen$ive RDMS systems, but does provide the ability to add
>(most of) those accoutrements if you wish.
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Keith Medcalf
In reply to this post by Thomas Kurz
You can check if what you need is available on a connection and either load it if needed or just abort:

sqlite> select * from pragma_function_list order by 1, 2;
name                       builtin
-------------------------  ----------
aavg                       0
abs                        1
acos                       0
aggbitmask                 0
aggmd2                     0
aggmd4                     0
aggmd5                     0
aggsha1                    0
aggsha256                  0
aggsha2_256                0
aggsha2_384                0
aggsha2_512                0
aggsha384                  0
aggsha3_224                0
aggsha3_256                0
aggsha3_384                0
aggsha3_512                0
aggsha512                  0
ascw                       0
asin                       0
atan                       0
atan2                      0
avg                        0
avg                        1
avg_angle                  0
avg_time                   0
bitmask                    0
bm25                       0
ceil                       0
changes                    1
char                       1
chgsign                    0
chrw                       0
ci                         0
clrbits                    0
coalesce                   1
compress                   0
computerfqdn               0
computername               0
copysign                   0
cos                        0
cosh                       0
count                      1
covar                      0
cume_dist                  1
current_date               0
current_date               1
current_time               0
current_time               1
current_timestamp          0
current_timestamp          1
date                       0
date                       1
datetime                   0
datetime                   1
datetimesec                0
degrees                    0
delta_apply                0
delta_create               0
delta_output_size          0
dense_rank                 1
doesfileexist              0
edit                       0
editdist3                  0
epsilon                    0
eval                       0
exp                        0
exponent                   0
fabs                       0
feq                        0
fge                        0
fgt                        0
first_value                1
firstnotnull               0
fle                        0
flip                       0
floor                      0
flt                        0
fmod                       0
fne                        0
fold                       0
frac                       0
fts3_tokenizer             0
fts5                       0
fts5_decode                0
fts5_decode_none           0
fts5_expr                  0
fts5_expr_tcl              0
fts5_fold                  0
fts5_isalnum               0
fts5_rowid                 0
fts5_source_id             0
gavg                       0
geopoly_area               0
geopoly_bbox               0
geopoly_blob               0
geopoly_ccw                0
geopoly_contains_point     0
geopoly_debug              0
geopoly_group_bbox         0
geopoly_json               0
geopoly_overlap            0
geopoly_regular            0
geopoly_svg                0
geopoly_within             0
geopoly_xform              0
getfileattributes          0
glob                       1
globu                      0
group_concat               1
havg                       0
havg_angle                 0
hex                        1
hexagesimal                0
hexw                       0
highlight                  0
hypot                      0
ieee754                    0
if                         0
ifnull                     1
instr                      1
ipaddrblob                 0
ipblobaddr                 0
ipsubnetcontains           0
isclr                      0
ismaskclr                  0
ismaskset                  0
isset                      0
j0                         0
j1                         0
jn                         0
json                       0
json_array                 0
json_array_length          0
json_extract               0
json_group_array           0
json_group_object          0
json_insert                0
json_object                0
json_patch                 0
json_quote                 0
json_remove                0
json_replace               0
json_set                   0
json_type                  0
json_valid                 0
julianday                  1
kurt                       0
kurtp                      0
lag                        1
last_insert_rowid          1
last_value                 1
lastnotnull                0
ldexp                      0
lead                       1
length                     1
like                       1
likelihood                 1
likely                     1
likeu                      0
ln                         0
load_extension             1
log                        0
lookupname                 0
lookupsid                  0
lower                      1
loweru                     0
lsmode                     0
ltrim                      1
m_1_pi                     0
m_2_pi                     0
m_2_sqrtpi                 0
m_deg2rad                  0
m_e                        0
m_ln10                     0
m_ln2                      0
m_log10e                   0
m_log2e                    0
m_pi                       0
m_pi_2                     0
m_pi_4                     0
m_rad2deg                  0
m_sqrt1_2                  0
m_sqrt2                    0
mantissa                   0
match                      0
matchinfo                  0
max                        1
md2                        0
md2_query                  0
md4                        0
md4_query                  0
md5                        0
md5_query                  0
median                     0
min                        1
mprint                     0
mprint1                    0
mprint2                    0
mprint3                    0
next_char                  0
nth_value                  1
ntile                      1
nullif                     1
offsets                    0
olddatetime                0
olddatetimems              0
oldtime                    0
oldunlocalize              0
oldunlocalizems            0
optimize                   0
percent_rank               1
percentile                 0
pow                        0
prefix_length              0
printf                     1
proper                     0
quote                      1
radians                    0
random                     1
randomblob                 1
randomv                    0
range                      0
rank                       1
ravg                       0
readfile                   0
regexp                     0
replace                    1
rms                        0
rot13                      0
round                      1
roundhe                    0
row_number                 1
rtreecheck                 0
rtreedepth                 0
rtreenode                  0
rtrim                      1
sem                        0
setbits                    0
sha1                       0
sha1_query                 0
sha256                     0
sha256_query               0
sha2_256                   0
sha2_256_query             0
sha2_384                   0
sha2_384_query             0
sha2_512                   0
sha2_512_query             0
sha384                     0
sha384_query               0
sha3_224                   0
sha3_256                   0
sha3_384                   0
sha3_512                   0
sha3_query                 0
sha512                     0
sha512_query               0
shell_add_schema           0
shell_escape_crnl          0
shell_int32                0
shell_module_schema        0
shell_putsnl               0
sign                       0
sin                        0
sinh                       0
skew                       0
skewp                      0
snippet                    0
soundex                    1
spellfix1_editdist         0
spellfix1_phonehash        0
spellfix1_scriptcode       0
spellfix1_translit         0
sqlar_compress             0
sqlar_uncompress           0
sqlite_compileoption_get   1
sqlite_compileoption_used  1
sqlite_log                 1
sqlite_record              1
sqlite_source_id           1
sqlite_version             1
sqrt                       0
stdev                      0
stdevp                     0
strdup                     0
strfilter                  0
strftime                   1
strpos                     0
strtaboo                   0
substr                     1
sum                        1
tan                        0
tanh                       0
time                       0
time                       1
timebeginperiod            0
timeendperiod              0
title                      0
tointeger                  0
tokenhasname               0
tokenhassid                0
toreal                     0
total                      1
total_changes              1
trim                       1
trunc                      0
typeof                     1
typos                      0
ulp                        0
ulps                       0
unaccent                   0
uncompress                 0
unicode                    1
unifuzz                    0
unixinstant                0
unixtime                   0
unlikely                   1
unlocaldate                0
unlocalize                 0
unzorder                   0
upper                      1
upperu                     0
username                   0
usersid                    0
uuidcreatev1               0
uuidcreatev4               0
uuidfromstring             0
uuidstringcreatev1         0
uuidstringcreatev4         0
uuidtostring               0
var                        0
varp                       0
writefile                  0
y0                         0
y1                         0
yn                         0
zeroblob                   1
zipfile                    0
zipfile_cds                0
zorder                     0

sqlite> select * from pragma_collation_list order by 1, 2;
seq                        name
-------------------------  ----------
0                          ROT13
1                          NUMERICS
2                          UNACCENTED
3                          NAMES
4                          NOCASEU
5                          IPADDRESS
6                          RTRIM
7                          NOCASE
8                          BINARY

sqlite> select * from pragma_module_list order by 1;
name
-------------------------
approximate_match
carray
completion
csv
dbstat
delta_parse
fsdir
fts3
fts3tokenize
fts4
fts4aux
fts5
fts5vocab
fuzzer
generate_series
geopoly
interpolate
json_each
json_tree
pragma_collation_list
pragma_function_list
pragma_module_list
prefixes
rtree
rtree_i32
spellfix1
sqlite_btreeinfo
sqlite_dbdata
sqlite_dbpage
sqlite_dbptr
sqlite_memstat
sqlite_stmt
swarmvtab
transitive_closure
unionvtab
wholenumber
zipfile

sqlite> select * from pragma_compile_options;
compile_options
ALLOW_COVERING_INDEX_SCAN
ALLOW_URI_AUTHORITY
COMPILER=gcc-8.1.0
DATETIME_NEW
DEFAULT_CACHE_SIZE=262144
DEFAULT_FOREIGN_KEYS
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PROXYDIR_PERMISSIONS=0755
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_WAL_AUTOCHECKPOINT=256
DEFAULT_WAL_SYNCHRONOUS=1
DEFAULT_WORKER_THREADS=8
ENABLE_8_3_NAMES=1
ENABLE_API_ARMOR
ENABLE_COLUMN_METADATA
ENABLE_COLUMN_USED_MASK
ENABLE_COSTMULT
ENABLE_CURSOR_HINTS
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_LOCKING_STYLE=1
ENABLE_MEMORY_MANAGEMENT
ENABLE_NORMALIZE
ENABLE_PREUPDATE_HOOK
ENABLE_RBU
ENABLE_RTREE
ENABLE_STAT4
ENABLE_STMTVTAB
ENABLE_STMT_SCANSTATUS
EXPLAIN_ESTIMATED_ROWS
EXTRA_INIT=core_init
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_ATTACHED=15
MAX_WORKER_THREADS=8
PRECISE_TIME
SOUNDEX
TEMP_STORE=1
THREADSAFE=1
USE_URI

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement
>
>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.
>
>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write
>
>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar
>
>
>----- Original Message -----
>From: Keith Medcalf <[hidden email]>
>To: SQLite mailing list <[hidden email]>
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>On Wednesday, 22 May, 2019 11:51, [hidden email] wrote:
>
>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)
>
>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library".  While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box.  Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others.  Statistical functions are not
>included because, well, they require complex implementations to get
>right.
>
>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).
>
>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs.  For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the whole ICU library.
>
>The downside of this is that the implementation of all these
>"goodies" quadruples the size of the base engine code (sqlite3.obj)
>and it is no longer "Lite".  There are other drawbacks as well.  For
>example, it is difficult to make many advanced numerical calculation
>methods (aggregates) compatible with window functions as currently
>implemented.
>
>About the only thing that is missing from SQLite3 is the ability to
>declare and implement "user defined types" in a fully integrated way
>(such as was added to DB2 back in the late 80's early 90's, and which
>I do not think anyone else has implemented as nicely anywhere else).
>
>Really, the issue is that SQLite3 is an SQL based relational storage
>manager, and it implements this function very well.  It does not
>provide a huge array of accoutrements that you may see with other
>more ex$pen$ive RDMS systems, but does provide the ability to add
>(most of) those accoutrements if you wish.
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL Features That SQLite Does Not Implement

Thomas Kurz
In reply to this post by Keith Medcalf
Ok, thank you for that hint. But it is still very unconvenient. How can I define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

----- Original Message -----
From: Keith Medcalf <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement

>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.

>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write

>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar


>----- Original Message -----
>From: Keith Medcalf <[hidden email]>
>To: SQLite mailing list <[hidden email]>
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, [hidden email] wrote:

>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)

>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library".  While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box.  Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others.  Statistical functions are not
>included because, well, they require complex implementations to get
>right.

>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).

>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs.  For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the whole ICU library.

>The downside of this is that the implementation of all these
>"goodies" quadruples the size of the base engine code (sqlite3.obj)
>and it is no longer "Lite".  There are other drawbacks as well.  For
>example, it is difficult to make many advanced numerical calculation
>methods (aggregates) compatible with window functions as currently
>implemented.

>About the only thing that is missing from SQLite3 is the ability to
>declare and implement "user defined types" in a fully integrated way
>(such as was added to DB2 back in the late 80's early 90's, and which
>I do not think anyone else has implemented as nicely anywhere else).

>Really, the issue is that SQLite3 is an SQL based relational storage
>manager, and it implements this function very well.  It does not
>provide a huge array of accoutrements that you may see with other
>more ex$pen$ive RDMS systems, but does provide the ability to add
>(most of) those accoutrements if you wish.

>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.





>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Hick Gunter
Just write a function that takes a function name, a default value and an unspecified number of arguments.

function_present(<default>,<function>[,<args>])

The implementation will then check if the named function is available;
If so, prepare, execute and return the result of "SELECT <function>(<args>)"
If not, just return <default>.

This is probably going to make your queries run blindingly slow...

BTW, what is your use case?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] SQL Features That SQLite Does Not Implement

Ok, thank you for that hint. But it is still very unconvenient. How can I define a view based on your suggestion? I want to have something like

CREATE VIEW foo AS SELECT {if has stddev then stddev(...) else null} FROM ...

----- Original Message -----
From: Keith Medcalf <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Thursday, May 23, 2019, 07:18:45
Subject: [sqlite] SQL Features That SQLite Does Not Implement


select name from pragma_function_list where name == 'M_Pi' collate nocase;

returns the name of the function if it exists.  See pragma function_list

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Thomas Kurz
>Sent: Wednesday, 22 May, 2019 22:19
>To: SQLite mailing list
>Subject: Re: [sqlite] SQL Features That SQLite Does Not Implement

>I agree in that not every math function can be included by default.
>My problem, however, is that I cannot know whether a user uses my
>self-compiled version with built-in extension-functions.c, or a
>downloaded version from sqlite.org.

>It would be very, very helpful (especially regarding views!) to have
>some kind of a "check function" so that one could write

>SELECT CASE WHEN defined('stddev') THEN stddev(foo) ELSE NULL END
>FROM bar


>----- Original Message -----
>From: Keith Medcalf <[hidden email]>
>To: SQLite mailing list <[hidden email]>
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, [hidden email] wrote:

>>Please add a note to the omitted page that many basic math functions
>>are NOT supported. (sqrt,mod,power,stdev,etc.)

>Traditionally "math library" functions provided by the various
>language runtimes were not included becase this would introduce
>dependancies on a "math library".  While this is available on *most*
>platforms, it is not available on *all* platforms which SQLite3 can
>be compiled for out of the box.  Furthermore the implementation of
>some transcendentals may be intrinsic on some CPU's and require huge
>amounts of library code on others.  Statistical functions are not
>included because, well, they require complex implementations to get
>right.

>Moreover, even the builtin functions are "lite" (the round function
>does not round properly for instance (it does grade-school 4/5
>rounding rather than half-even rounding), the average function is
>rather simple in implementation and suffers from trivally triggered
>sources of computational error (it uses sum/count rather than
>successive approximation to the mean), and many other limitations
>exist in the builtin implementations of many functions).

>All of these issues can be "fixed" however, all you need to do is add
>the necessary code via the extension mechanism to add whatever
>functionality you require using whatever numerical methods you
>determine are suitable for your needs.  For example, I have added
>default support via the extension mechanism (and the EXTRA_INIT hook)
>to make all the distributed extensions available on every connection,
>to add all the standard platform math functions, to add a bunch of
>statistical functions, several platform APIs (Windows in this case),
>and to "fix" the builtin round, datetime (to include proper support
>for instant times and timezone manipulation using the standard IANA
>timezone database), and added support for basic Unicode nocase and
>noaccent collations without using the whole ICU library.

>The downside of this is that the implementation of all these
>"goodies" quadruples the size of the base engine code (sqlite3.obj)
>and it is no longer "Lite".  There are other drawbacks as well.  For
>example, it is difficult to make many advanced numerical calculation
>methods (aggregates) compatible with window functions as currently
>implemented.

>About the only thing that is missing from SQLite3 is the ability to
>declare and implement "user defined types" in a fully integrated way
>(such as was added to DB2 back in the late 80's early 90's, and which
>I do not think anyone else has implemented as nicely anywhere else).

>Really, the issue is that SQLite3 is an SQL based relational storage
>manager, and it implements this function very well.  It does not
>provide a huge array of accoutrements that you may see with other
>more ex$pen$ive RDMS systems, but does provide the ability to add
>(most of) those accoutrements if you wish.

>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.





>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123