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

[Stephen Chrzanowski]

2019 WISH LIST
---
- A list of libraries that are known to be successfully added to SQLite.
- Have a base instance, select some check-boxes, press SUBMIT, something
somewhere somehow generates your .DLL, .SO, etc. so that you have
everything you want built in
- A million dollars
- More Pepsi [Check]
- No one to tell me that the first and second option are not feasible.
It's just a wish list. ;)

On Thu, May 23, 2019 at 6:28 PM R Smith  wrote:

>
> And to be clear, I don't think the Math lib would specifically be bloat,
> though it's an unneeded piece of added weight for my needs, but I will
> accept it happily if it makes most other people happy. But then, if we
> start with the math lib, what is next? Sure enough someone will come up
> with a very valid next bit to be added, and a next. I understand that
> Richard's decision on inclusion is not beyond reproach, but whose would be?
>
> Put another way, let's say we do add some libraries automatically,
> pushing the weight up a nice bit, but they are not the libs you wanted,
> would you be happy and consoled? If not, how do you expect anyone else
> to be happy with your choice being the chosen implemented? Would you
> then rather go back to how it was before?
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Warren Young]

On May 23, 2019, at 4:28 PM, R Smith  wrote:
> 
> it is very easy to add things to the base distro, but extremely hard to 
> impossible to ever take it away again, which means one should only ever "add" 
> with great caution.

Easy fix: -DSQLITE_OMIT_EXTENDED_MATH_LIBRARY

Include it by default, but let those on resource-constrained platforms yank it 
back out.

I think the biggest obstacle here is this: *which* math library do we use?

If SQLite’s math is based on the platform math library (e.g. libm.so) then it 
may substantially change behavior across platforms, which goes against one of 
the SQLite guarantees: your DB file behaves the same way everywhere.

If it’s a custom math library, that’ll take a lot of work to get right, and 
once done, then it’s “bloat” relative to the platform math library.

That can in turn be solved with more ifdefs (-DSQLITE_PLATFORM_MATH) but the 
way off-by-default options are usually handled in SQLite is that they aren’t 
checked as part of the test suites, so there are option combinations that don’t 
build a working library, much less give one that’s had all of the options 
cross-tested against each other.

I understand why that’s the case: the alternative is a combinatoric explosion 
where the number of test configurations is 2^N * M where N is the number of 
Boolean options and M is the number of the *non*-Boolean option combinations 
that need to be tested.  16 Boolean options is 64k runs of the test suite to 
cross-check every option against all possible combinations of the other 15 
options!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

On 2019/05/23 1:26 PM, J. King wrote:

On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:


This is SQLite. Perhaps some of us could collaborate on a fork called
SQLbloat //..

I find this a little condescending. There's a lot of reasons to like SQLite, and the aspect that 
sways me more than others is not "lite", but "zeroconf".

I mainly use SQLite in PHP and Tcl, so using my own SQLite library is often not 
practical, and in the case of PHP, loadable extensions are disabled by default. 
I as a programmer am at the mercy of what distributions package---which is 
often SQLite in its default configuration, so a less-lite-but-still-zeroconf 
SQLessLite as the default configuration would be extremely valuable to me. At 
the same time, those working in tiny systems still have tons of compile-time 
options to keep things lean.

In short, I'm skeptical that the choices Hwaci have made about what to exclude are 
necessarily beyond reproach or discussion. Derogatory references to  "SQLbloat" 
really don't further the cause of honest discussion.


This is a misunderstanding of my intent, much as the OP misunderstood 
that I don't consider a function that could confirm the presence of 
another function to be bloat, I quite welcome that, and I most certainly 
do not think any decision is beyond reproach. The entire bloat argument 
is towards the extent of the libraries included automatically.


And to be clear, I don't think the Math lib would specifically be bloat, 
though it's an unneeded piece of added weight for my needs, but I will 
accept it happily if it makes most other people happy. But then, if we 
start with the math lib, what is next? Sure enough someone will come up 
with a very valid next bit to be added, and a next. I understand that 
Richard's decision on inclusion is not beyond reproach, but whose would be?


Put another way, let's say we do add some libraries automatically, 
pushing the weight up a nice bit, but they are not the libs you wanted, 
would you be happy and consoled? If not, how do you expect anyone else 
to be happy with your choice being the chosen implemented? Would you 
then rather go back to how it was before?


Lastly, it is very easy to add things to the base distro, but extremely 
hard to impossible to ever take it away again, which means one should 
only ever "add" with great caution.


Everybody's needs are different and it is impossible to satisfy all, so 
I maintain that providing the base SQL functions and having the options 
for added functionality relegated to every user's personal choice (with 
multiple ways of achieving it no less) is a good solution.


I do however think that having the function-list pragma in the base 
distro is needed. Understanding dependency shortcomings would then be as 
easy as a quick query, which is especially useful where SQLite is used 
through wrappers.



Cheers,
Ryan

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

Re: [sqlite] User Defined Types implementations ...

[Keith Medcalf]

On Thursday, 23 May, 2019 14:39, Jens Alfke  wrote:

>> On May 22, 2019, at 8:16 PM, Keith Medcalf 
>wrote:

>> Basically, User Defined Types (UDT) were implemented in a fashion
>analgous to a C++ class (remember that at this time C++ was just a
>pre-processor for C and a C++ class was nothing more than a struct
>and mangled function names to operate on the class).

>…and a vtable to provide polymorphic dispatch of virtual methods,
>which is required for OOP.

>> Basically, when you declared something as a UDT you were giving a
>"blob" a type-domain.  Whenever you tried to do something with a UDT
>a "mangled function name" was generated that took that blob as the
>first argument

>Was DB2 using early or late binding of types to blobs? That is, was
>the type of a blob declared as part of the table column, or were
>individual blob values tagged with their own types? This makes a huge
>difference. In the former (early) case the UDTs are just syntactic
>sugar, while with late-binding they really do act like objects. Late-
>binding is also required if user-defined functions are to be allowed
>to return UDTs.

The initial version was entirely early binding via column declarations of the 
type, so they were syntactic sugar, not real polymorphic objects (in the sense 
of how real objects and types work).  Now I believe you can implement UDT's as 
true objects (but still early bound via the column declaration) as C++ or Java 
or whatever classes.  It was a long time ago and I haven't touched DB2 for a 
long time ... I only remember because at the time I was doing Level 2.5 support 
for the IBM CSet++ Compiler package at TOROLAB6 and we had lots of interaction 
with the DB2 developers who were just up the road

>> you merely implemented a bunch of functions with the appropriate
>names that DB2 would use whenever you referred to that UDT type,
>rather than using the builtin functions (this is similar to the way
>languages like Python implement classes).

>Not really; in Python (and Ruby, and Objective-C, and Smalltalk, and
>sort-of JavaScript…) a class is a dictionary that maps method names
>to anonymous functions. The functions themselves don't have names.
>(Sorry to be pedantic, but I'm a bit of a language geek.)

No need, but you are correct.  I believe that in fact the implementation that 
was released was based on proper dictionary based method dispatch and that all 
the other "standard internal types" ended up being implemented in the same 
fashion (just with standardized builtin models).  That is, even a standard 
"varchar(20)" or "integer" or "decimal(10,4)" was nothing more than a binary 
blob in the database that had a builtin method dictionary attached via the 
declaration.  The nice part was that the system implementation was extensible 
by being able to provide your own binding dictionaries, and that everything, 
whether an "internal" or UDT was implemented using the same methods.  I don't 
know if the types were ever actually polymorphic or supported multiple 
inheritance or not.

---
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Have SQLite handle values of my own type

[Keith Medcalf]

On Thursday, 23 May, 2019 08:35, Dominique Devienne  wrote:

>On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera
> wrote:
>
>> I have been working network for a long time, and I have never seen
>> any application that takes "zeroed left-filled" IP addresses.  Just
>> sharing...
>> Thanks.

> Works for me with a .001 at least, as shown below. But that wasn't
> really the point I was making, FWIW. --DD

Well, actually, that depends on how the code in the network stack converts from 
presentation format to network format.  Windows 10 1903 (at least) treats each 
component as an input number which means that a number that starts with a 0 is 
an octal number ... (ie, the BSD way)

>ping 172.217.14.227

Pinging 172.217.14.227 with 32 bytes of data:
Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
Reply from 172.217.14.227: bytes=32 time=26ms TTL=56

>ping 172.217.014.227

Pinging 172.217.12.227 with 32 bytes of data:
Reply from 172.217.12.227: bytes=32 time=73ms TTL=45
Reply from 172.217.12.227: bytes=32 time=72ms TTL=45

Other parsers may see the input as invalid:

sqlite> select ipblobaddr(ipaddrblob('172.217.014.227'));

sqlite> select ipblobaddr(ipaddrblob('172.217.14.227'));
172.217.14.227

So really, what you get depends on who wrote the code that is doing the 
translation.  Since the code that I used to implement ipblobaddr and ipaddrblob 
is taken from the ISC DNS Bind code based on code written by Paul Vixie in 
1996, it is quite possible that many things will see leading 0's as invalid 
input.  Some things (for example Cisco IOS) may also choose to just ignore the 
extra 0's.  Other things may take it as an indicator that the value is base-8 
rather than base-10.

See
https://tools.ietf.org/html/draft-main-ipaddr-text-rep-00
https://en.wikipedia.org/wiki/Dot-decimal_notation

---
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] User Defined Types implementations ...

[Jens Alfke]

> On May 22, 2019, at 8:16 PM, Keith Medcalf  wrote:
> 
> Basically, User Defined Types (UDT) were implemented in a fashion analgous to 
> a C++ class (remember that at this time C++ was just a pre-processor for C 
> and a C++ class was nothing more than a struct and mangled function names to 
> operate on the class). 

…and a vtable to provide polymorphic dispatch of virtual methods, which is 
required for OOP.

> Basically, when you declared something as a UDT you were giving a "blob" a 
> type-domain.  Whenever you tried to do something with a UDT a "mangled 
> function name" was generated that took that blob as the first argument

Was DB2 using early or late binding of types to blobs? That is, was the type of 
a blob declared as part of the table column, or were individual blob values 
tagged with their own types? This makes a huge difference. In the former 
(early) case the UDTs are just syntactic sugar, while with late-binding they 
really do act like objects. Late-binding is also required if user-defined 
functions are to be allowed to return UDTs.

> you merely implemented a bunch of functions with the appropriate names that 
> DB2 would use whenever you referred to that UDT type, rather than using the 
> builtin functions (this is similar to the way languages like Python implement 
> classes).

Not really; in Python (and Ruby, and Objective-C, and Smalltalk, and sort-of 
JavaScript…) a class is a dictionary that maps method names to anonymous 
functions. The functions themselves don't have names. (Sorry to be pedantic, 
but I'm a bit of a language geek.)

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

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

[James K. Lowden]

On Wed, 22 May 2019 17:56:23 -0700
Jens Alfke  wrote:

> > On May 22, 2019, at 3:55 PM, James K. Lowden
> >  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.)

I see.  So user-defined type here isn't some combination of extant
primitive types, but a blob that *could* be represented by extant types
but is instead represented another way.  

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

Re: [sqlite] User Defined Types implementations ...

[James K. Lowden]

On Wed, 22 May 2019 21:16:04 -0600
"Keith Medcalf"  wrote:

> Basically, when you declared something as a UDT you were giving a
> "blob" a type-domain.  Whenever you tried to do something with a UDT
> a "mangled function name" was generated that took that blob as the
> first argument and you merely implemented a bunch of functions with
> the appropriate names that DB2 would use whenever you referred to
> that UDT type, rather than using the builtin functions (this is
> similar to the way languages like Python implement classes).

I see.  First, declare a type U.  Then define a function, say, 

U max( U, U ) .

Now, if we have a table 

T( U u )

we can 

SELECT max(u) from T

to invoke our function instead of the built-in max().  

What about operators, then, as long as we're talking C++?  

U operator<(U u) ?

If you could define equivalence and less-than, you can join on, sort
by, or get the maximium.  In fact (speaking of Python), you get those
for free if there's an implicit conversion to string.  

--jkl

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

Re: [sqlite] Bug: sqlite ARM endian problem

[Sebastian Kemper]

On Wed, May 22, 2019 at 03:43:13PM -0400, Richard Hipp wrote:
> On 5/22/19, Sebastian Kemper  wrote:
> >
> > Hello Richard,
> >
> > I have run-tested this with qemu on two targets until now:
> >
> > arm_arm1176jzf-s_vfp
> > armeb_xscale
> >
> > Both worked fine. Unfortunately I still don't have feedback from the
> > user. He didn't receive my mail. I've sent it again and will get back to
> > you then. I'll also try out aarch64 in qemu in the meantime.
>
> Thanks for verifying the fix for us!

Hello again Richard,

The user got back to me confirming that the patched sqlite works on his
armeb xscale device. I also confirmed that it is as well working on
aarch64_cortex-a53 in qemu.

I think that's it for now. Have a nice day!

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

Re: [sqlite] Have SQLite handle values of my own type

[Simon Slavin]

On 23 May 2019, at 12:02pm, Dominique Devienne  wrote:

> On Thu, May 23, 2019 at 12:37 PM Simon Slavin  wrote:
> 
>> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the 
>> four parts is always three digits long.
> 
> Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual.  You'd 
> better have a CHECK constraint to enforce your format.

You misunderstood.  This is a storage, selection and sorting format.  Not one 
for interaction with users.  Much like you would never show a user a number 
like 2348923.6484 without putting commas in or something.

On 23 May 2019, at 4:35pm, Dominique Devienne  wrote:

> C:\Users\ddevienne>ping 10.65.9.030
> Pinging 10.65.9.24 with 32 bytes of data:

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

Re: [sqlite] Have SQLite handle values of my own type

[Dominique Devienne]

On Thu, May 23, 2019 at 5:14 PM Jen Pollock  wrote:

> This is getting pretty far off topic, but I think at least some tools
> will interpret values with leading zeroes as octal, which means 001 is
> the same as 1, but 010 isn't 10, it's 8.
>

Good catch! That's indeed what's happening. Win7 BTW. --DD

C:\Users\ddevienne>ping 10.65.9.30
Pinging 10.65.9.30 with 32 bytes of data:
...
C:\Users\ddevienne>ping 10.65.9.030
Pinging 10.65.9.24 with 32 bytes of data:
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Have SQLite handle values of my own type

[Jen Pollock]

This is getting pretty far off topic, but I think at least some tools
will interpret values with leading zeroes as octal, which means 001 is
the same as 1, but 010 isn't 10, it's 8.

On Thu, May 23, 2019 at 04:35:02PM +0200, Dominique Devienne wrote:
> On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera 
> wrote:
> 
> > I have been working network for a long time, and I have never seen any
> > application that takes "zeroed left-filled" IP addresses.  Just sharing...
> > Thanks.
> >
> 
> Works for me with a .001 at least, as shown below. But that wasn't really
> the point I was making, FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Have SQLite handle values of my own type

[Jose Isaias Cabrera]

Dominique Devienne, on Thursday, May 23, 2019 10:35 AM, wrote...
>On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera 
>wrote:
>
>Works for me with a .001 at least, as shown below. But that wasn't really
>the point I was making, FWIW. --DD
>

>C:\Users\ddevienne>ping 192.168.223.001
>
>Pinging 192.168.223.1 with 32 bytes of data:
>Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
>Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
>Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
>Reply from 192.168.223.1: bytes=32 time<1ms TTL=128

You must have one of those new Windows 10 machines. ;-)  They probably have 
some trick to get rid of the pre-zeroes-filled numbers.  If you look at the 
response, it does not have the 001. Just sayin'... :-)  Thanks.

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

Re: [sqlite] Have SQLite handle values of my own type

[Dominique Devienne]

On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera 
wrote:

> I have been working network for a long time, and I have never seen any
> application that takes "zeroed left-filled" IP addresses.  Just sharing...
> Thanks.
>

Works for me with a .001 at least, as shown below. But that wasn't really
the point I was making, FWIW. --DD

C:\Users\ddevienne>ping 192.168.223.1

Pinging 192.168.223.1 with 32 bytes of data:
Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
Reply from 192.168.223.1: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.223.1:
Packets: Sent = 2, Received = 2, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
Control-C
^C
C:\Users\ddevienne>ping 192.168.223.001

Pinging 192.168.223.1 with 32 bytes of data:
Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
Reply from 192.168.223.1: bytes=32 time<1ms TTL=128
Reply from 192.168.223.1: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.223.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jose Isaias Cabrera]

J. King, on Thursday, May 23, 2019 07:26 AM, wrote...
>On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:
>
>>This is SQLite. Perhaps some of us could collaborate on a fork called
>>SQLbloat and put out standard libs/code/precompileds for versions of
>>sqlite with everything - bbq sauce and all, for when you don't need
>>Lite
>>- then you can specify that the queries are for SQLbloat and they will
>>run out the box directly and correctly.  Linux distro wars coming to an
>
>I find this a little condescending. There's a lot of reasons to like
> SQLite, and the aspect that sways me more than others is not "lite", but
> "zeroconf".

I also feel the same way as Mr. King, but, I understand the "bloating" idea.  
And so, I am happy to have the liteness. But, do I pray every day for a little 
bloat-ness from SQLite for my own greed?  Yes, indeed.

>I mainly use SQLite in PHP and Tcl, so using my own SQLite library is
> often not practical, and in the case of PHP, loadable extensions are

I have been using D and SQlite since 2006. I have prepared tools for myself, 
work, friends, church using these two tools and the freeware libraries that I 
can find. They work perfectly, but I want more. :-) However, I am thrill to 
continue to use SQLite the way it is.  But, again, do I pray for more? Yes. :-) 
 Thanks for the chance to be part of this discussion.

josé

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

Re: [sqlite] Have SQLite handle values of my own type

[Jose Isaias Cabrera]

Dominique Devienne, on Thursday, May 23, 2019 07:02 AM, wrote...
>On Thu, May 23, 2019 at 12:37 PM Simon Slavin  wrote:

>> On 23 May 2019, at 3:55am, Keith Medcalf  wrote:
>> > 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.
>>
>> So it would seem that rather than define a function which turns a value of
>> my type into a BLOB, it would be better to create one which turns it into
>> some sort of normalised text form.  Then I can create whatever COLLATEs I
>> need to do my specialised sorting and handling.
>>
>> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of
>> the four parts is always three digits long.
>
>
>Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual.
>You'd better have a CHECK constraint to enforce your format.
>Otherwise lexical ordering won't work with non-expected (but more natural)
>1.1.1.1.
>While writing a custom collation (the so called "natural ordering") would
>work for either format. --DD

Microsoft ping fails with 010.116.082.097,

 9:45:39.71>ping 010.116.082.097
Ping request could not find host 010.116.082.097. Please check the name and try
again.

but, works with the normal unzeroed format,

 9:45:55.21>ping 10.116.82.97

Pinging 10.116.82.97 with 32 bytes of data:
Reply from 10.116.82.97: bytes=32 time<1ms TTL=128
Reply from 10.116.82.97: bytes=32 time<1ms TTL=128
Reply from 10.116.82.97: bytes=32 time<1ms TTL=128
Reply from 10.116.82.97: bytes=32 time<1ms TTL=128

Ping statistics for 10.116.82.97:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

I have been working network for a long time, and I have never seen any 
application that takes "zeroed left-filled" IP addresses.  Just sharing... 
Thanks.

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

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

[sky5walk]

Thank you for bringing topic back to original intent.
Clearly much is missing from the latest SQL standard. Debates aside, the
URL is equally lacking.

Yes, "own" extensions solve much of my needs.

On Thu, May 23, 2019, 7:26 AM J. King  wrote:

> On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:
>
> >This is SQLite. Perhaps some of us could collaborate on a fork called
> >SQLbloat and put out standard libs/code/precompileds for versions of
> >sqlite with everything - bbq sauce and all, for when you don't need
> >Lite
> >- then you can specify that the queries are for SQLbloat and they will
> >run out the box directly and correctly.  Linux distro wars coming to an
>
> I find this a little condescending. There's a lot of reasons to like
> SQLite, and the aspect that sways me more than others is not "lite", but
> "zeroconf".
>
> I mainly use SQLite in PHP and Tcl, so using my own SQLite library is
> often not practical, and in the case of PHP, loadable extensions are
> disabled by default. I as a programmer am at the mercy of what
> distributions package---which is often SQLite in its default configuration,
> so a less-lite-but-still-zeroconf SQLessLite as the default configuration
> would be extremely valuable to me. At the same time, those working in tiny
> systems still have tons of compile-time options to keep things lean.
>
> In short, I'm skeptical that the choices Hwaci have made about what to
> exclude are necessarily beyond reproach or discussion. Derogatory
> references to  "SQLbloat" really don't further the cause of honest
> discussion.
>
>
> --
> J. King
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Have SQLite handle values of my own type

[Shawn Wagner]

Don't forget IPv6 addresses.

On Thu, May 23, 2019, 3:37 AM Simon Slavin  wrote:

> On 23 May 2019, at 3:55am, Keith Medcalf  wrote:
>
> > 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.
>
> So it would seem that rather than define a function which turns a value of
> my type into a BLOB, it would be better to create one which turns it into
> some sort of normalised text form.  Then I can create whatever COLLATEs I
> need to do my specialised sorting and handling.
>
> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of
> the four parts is always three digits long.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[J. King]

On May 23, 2019 6:46:52 a.m. EDT, R Smith  wrote:

>This is SQLite. Perhaps some of us could collaborate on a fork called 
>SQLbloat and put out standard libs/code/precompileds for versions of 
>sqlite with everything - bbq sauce and all, for when you don't need
>Lite 
>- then you can specify that the queries are for SQLbloat and they will 
>run out the box directly and correctly.  Linux distro wars coming to an
 
I find this a little condescending. There's a lot of reasons to like SQLite, 
and the aspect that sways me more than others is not "lite", but "zeroconf".

I mainly use SQLite in PHP and Tcl, so using my own SQLite library is often not 
practical, and in the case of PHP, loadable extensions are disabled by default. 
I as a programmer am at the mercy of what distributions package---which is 
often SQLite in its default configuration, so a less-lite-but-still-zeroconf 
SQLessLite as the default configuration would be extremely valuable to me. At 
the same time, those working in tiny systems still have tons of compile-time 
options to keep things lean. 

In short, I'm skeptical that the choices Hwaci have made about what to exclude 
are necessarily beyond reproach or discussion. Derogatory references to  
"SQLbloat" really don't further the cause of honest discussion.


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

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

[Thomas Kurz]

This doesn't solve the problem. The database must work regardless of whether it 
is used within my own app or any other database viewer that might not have the 
extension functions available. In the first case, medians, standard deviations, 
etc. are included in the view, in the second case the values should be just 
NULL.


- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Thursday, May 23, 2019, 12:34:39
Subject: [sqlite] SQL Features That SQLite Does Not Implement

On 23 May 2019, at 7:57am, Thomas Kurz  wrote:

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

'if' in SQL language is CASE.

Near the beginning of your code, try to execute a function that contains 
stddev().  Make a note of whether it compiles without errors or not.  If it 
compiles, then the function is available.  Store this boolean somewhere and use 
it later on in your code.

What you propose doing: creating a library call which works differently 
depending on whether stddev() is available just leads to infinite regress.  If 
you create such a library call then you'll want to create another library to 
tell whether /your/ library call is available.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

[Thomas Kurz]

This ain't quite helpful. Sqlite also supports FTS3/4/5 which I would 
personally consider as bloat. So what is the actual "bloat" in supporting a 
function that can just check whether a function exists or not. That would 
probably add only some bytes to the library.


- Original Message - 
From: R Smith 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Thursday, May 23, 2019, 12:46:52
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Ok, but you can't have it both ways. Either you want to write generic 
use-everywhere SQL, in which case you need to stick to the generic 
included-everywhere functions,

OR

You want to write specialized specific queries that use either your own 
or other UDF's, in which case you must be able to load your own (or 
other's) add-on libraries.

If you think a construct like "SELECT COALESCE(myFunc(xx), 0);" which 
returns the function result, unless it errors out, in which case it 
returns Zero, is good programming, then you are making a first-principle 
mistake. Because now you add a layer of complexity to the end-system. 
The result is Zero - Is this an error, or is this actually the result? 
Extra checks needed.

Errors should cause errors so that everyone know it's an error. 
Silencing errors is a very Microsoftian Gooey (to quote Keith) way of 
thinking and a mistake often made by novice GUI programmers who value 
user-experience over truth.

Else what you are asking for is to have your bread buttered on both 
sides, which isn't feasible, and which isn't done in any RDBMS engine 
(though admittedly the big ones do include most general math functions 
as standard, but then they don't need to sometimes run on embedded devices).

This is SQLite. Perhaps some of us could collaborate on a fork called 
SQLbloat and put out standard libs/code/precompileds for versions of 
sqlite with everything - bbq sauce and all, for when you don't need Lite 
- then you can specify that the queries are for SQLbloat and they will 
run out the box directly and correctly.  Linux distro wars coming to an 
SQLite DB near you soon!


Cheers,
Ryan


On 2019/05/23 12:19 PM, Thomas Kurz wrote:
> That doesn't make any difference. Then I could use the extensions-functions.c 
> loadable module as well. My database has to work equally well no matter what 
> dll and/or extension is used or not.

> Regardless of my application, the problem stays the same when you open the 
> database in any other viewer that doesn't have the extension.




> - Original Message -
> From: Hick Gunter 
> To: 'SQLite mailing list' 
> Sent: Thursday, May 23, 2019, 11:58:10
> Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

> Put your function into a loadable extension and load it during application 
> startup so that it is always available to your code. This does not require 
> checking the sqlite3.dll

> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Thomas Kurz
> Gesendet: Donnerstag, 23. Mai 2019 11:41
> An: SQLite mailing list 
> Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not 
> Implement

> I want to define VIEWs that work equally well regardless of whether a default 
> sqlite3.dll or a custom build with built-in extension-functions.c is used. 
> Defining my own function as an extension module which checks that case would 
> additionally require checking whether a default sqlite3.dll or a custom build 
> is used? That doesn't make any sense to me?!?


> - Original Message -
> From: Hick Gunter 
> To: 'SQLite mailing list' 
> Sent: Thursday, May 23, 2019, 10:02:30
> Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

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

> function_present(,[,])

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

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

> BTW, what is your use case?

> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Thomas Kurz
> Gesendet: Donnerstag, 23. Mai 2019 08:58
> An: SQLite mailing list 
> 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 
> To: SQLite mailing list 
> 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 

Re: [sqlite] Have SQLite handle values of my own type

[Dominique Devienne]

On Thu, May 23, 2019 at 12:37 PM Simon Slavin  wrote:

> On 23 May 2019, at 3:55am, Keith Medcalf  wrote:
> > 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.
>
> So it would seem that rather than define a function which turns a value of
> my type into a BLOB, it would be better to create one which turns it into
> some sort of normalised text form.  Then I can create whatever COLLATEs I
> need to do my specialised sorting and handling.
>
> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of
> the four parts is always three digits long.


Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual.
You'd better have a CHECK constraint to enforce your format.
Otherwise lexical ordering won't work with non-expected (but more natural)
1.1.1.1.
While writing a custom collation (the so called "natural ordering") would
work for either format. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

Ok, but you can't have it both ways. Either you want to write generic 
use-everywhere SQL, in which case you need to stick to the generic 
included-everywhere functions,


OR

You want to write specialized specific queries that use either your own 
or other UDF's, in which case you must be able to load your own (or 
other's) add-on libraries.


If you think a construct like "SELECT COALESCE(myFunc(xx), 0);" which 
returns the function result, unless it errors out, in which case it 
returns Zero, is good programming, then you are making a first-principle 
mistake. Because now you add a layer of complexity to the end-system. 
The result is Zero - Is this an error, or is this actually the result? 
Extra checks needed.


Errors should cause errors so that everyone know it's an error. 
Silencing errors is a very Microsoftian Gooey (to quote Keith) way of 
thinking and a mistake often made by novice GUI programmers who value 
user-experience over truth.


Else what you are asking for is to have your bread buttered on both 
sides, which isn't feasible, and which isn't done in any RDBMS engine 
(though admittedly the big ones do include most general math functions 
as standard, but then they don't need to sometimes run on embedded devices).


This is SQLite. Perhaps some of us could collaborate on a fork called 
SQLbloat and put out standard libs/code/precompileds for versions of 
sqlite with everything - bbq sauce and all, for when you don't need Lite 
- then you can specify that the queries are for SQLbloat and they will 
run out the box directly and correctly.  Linux distro wars coming to an 
SQLite DB near you soon!



Cheers,
Ryan


On 2019/05/23 12:19 PM, Thomas Kurz wrote:

That doesn't make any difference. Then I could use the extensions-functions.c 
loadable module as well. My database has to work equally well no matter what 
dll and/or extension is used or not.

Regardless of my application, the problem stays the same when you open the 
database in any other viewer that doesn't have the extension.




- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 11:58:10
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Put your function into a loadable extension and load it during application 
startup so that it is always available to your code. This does not require 
checking the sqlite3.dll

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

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

function_present(,[,])

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

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

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
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 
To: SQLite mailing list 
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-
boun...@mailinglists.sqlite.org] 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 

Re: [sqlite] Have SQLite handle values of my own type

[Simon Slavin]

On 23 May 2019, at 3:55am, Keith Medcalf  wrote:

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

So it would seem that rather than define a function which turns a value of my 
type into a BLOB, it would be better to create one which turns it into some 
sort of normalised text form.  Then I can create whatever COLLATEs I need to do 
my specialised sorting and handling.

IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the 
four parts is always three digits long.

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

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

[Simon Slavin]

On 23 May 2019, at 7:57am, Thomas Kurz  wrote:

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

'if' in SQL language is CASE.

Near the beginning of your code, try to execute a function that contains 
stddev().  Make a note of whether it compiles without errors or not.  If it 
compiles, then the function is available.  Store this boolean somewhere and use 
it later on in your code.

What you propose doing: creating a library call which works differently 
depending on whether stddev() is available just leads to infinite regress.  If 
you create such a library call then you'll want to create another library to 
tell whether /your/ library call is available.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Thomas Kurz]

That doesn't make any difference. Then I could use the extensions-functions.c 
loadable module as well. My database has to work equally well no matter what 
dll and/or extension is used or not.

Regardless of my application, the problem stays the same when you open the 
database in any other viewer that doesn't have the extension.




- Original Message - 
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 11:58:10
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Put your function into a loadable extension and load it during application 
startup so that it is always available to your code. This does not require 
checking the sqlite3.dll

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

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

function_present(,[,])

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

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

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
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 
To: SQLite mailing list 
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-
>boun...@mailinglists.sqlite.org] 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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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" 

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

[Hick Gunter]

Put your function into a loadable extension and load it during application 
startup so that it is always available to your code. This does not require 
checking the sqlite3.dll

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 11:41
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message -
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

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

function_present(,[,])

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

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

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
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 
To: SQLite mailing list 
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-
>boun...@mailinglists.sqlite.org] 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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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 

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

[Thomas Kurz]

I want to define VIEWs that work equally well regardless of whether a default 
sqlite3.dll or a custom build with built-in extension-functions.c is used. 
Defining my own function as an extension module which checks that case would 
additionally require checking whether a default sqlite3.dll or a custom build 
is used? That doesn't make any sense to me?!?


- Original Message - 
From: Hick Gunter 
To: 'SQLite mailing list' 
Sent: Thursday, May 23, 2019, 10:02:30
Subject: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

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

function_present(,[,])

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

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

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
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 
To: SQLite mailing list 
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-
>boun...@mailinglists.sqlite.org] 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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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 

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

[Dominique Devienne]

On Thu, May 23, 2019 at 11:09 AM Keith Medcalf  wrote:

> >Keith, as can be seen below, those pragma_*list eponymous vtables are
> >you referring to as not built-in.
>
> Actually they are.  They are the eponymous vtables for the corresponding
> pragma's:
>
> pragma function_list;
> pragma collation_list;
> pragma module_list;
>

C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma function_list;
sqlite>

Now I recall... This is the "official" pre-built binaries from
https://sqlite.org/download.html
which does not turn ON these compile options. That's a shame IMHO.

>I wasn't even aware one could get the function-list at all. Did you
> >implement that as a pure extension w/o changing the official SQLite
> >source code? Are all your extensions Open-Source, on GitHub or
> somewhere?
>
> https://sqlite.org/pragma.html#pragma_function_list


What's strange is that there's zero hit for the string "function_list" in
https://www.sqlite.org/changes.html
When was this introduced again? I also search for "information" and
"schema", and failed to locate the ChangeLog entry.
Even SQLITE_INTROSPECTION_PRAGMAS has no hit in this page in fact! Or
instropection (instrospec does, but unrelated).
Mysterious... --DD

PS: Thanks for info on where to find your (own and collected) extensions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

On Thursday, 23 May, 2019 02:08, Dominique Devienne  wrote:

>On Thu, May 23, 2019 at 7:39 AM Keith Medcalf 
>wrote:

>> 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
>> ...
>> sqlite> select * from pragma_collation_list order by 1, 2;
>> seqname
>> -  --
>> 0  ROT13
>> ...
>> sqlite> select * from pragma_module_list order by 1;
>> name
>> -
>> approximate_match
>> carray
>> completion
>> ...
>> sqlite> select * from pragma_compile_options;
>> compile_options
>> ALLOW_COVERING_INDEX_SCAN
>> ...

>Keith, as can be seen below, those pragma_*list eponymous vtables are
>you referring to as not built-in.

Actually they are.  They are the eponymous vtables for the corresponding 
pragma's:

pragma function_list;
pragma collation_list;
pragma module_list;

>I wasn't even aware one could get the function-list at all. Did you
>implement that as a pure extension w/o changing the official SQLite 
>source code? Are all your extensions Open-Source, on GitHub or 
somewhere?

https://sqlite.org/pragma.html#pragma_function_list

The pragma's and the eponymous vtables are built in but only if you compile the 
code with the SQLITE_INTROSPECTION_PRAGMAS defined during the compile (and that 
symbol is not listed as part of the pragma compile_options, unfortunately.  
Perhaps Richard could add than so it would be easier to tell if those were 
available rather than trying to use the pragma or the vtables and getting 
nothing or an error respectively.

You can find the source for all the extensions (and the compiled dll's for 
windows, compiled with MinGW) here (excluding the sqltime.c module which only 
works internally and not as an extension and does require changes to the 
sqlite3.c date.c module, that makes the DateTime output default to having 
miliseconds and a timezone offset, plus the ability to specify IANA timezone 
names as parameters eg datetime('now','america/chicago') -- that requires 
certain tables to be present as well to do the translations):

https://www.dessus.com/files/SQLiteExtensions.zip
https://www.dessus.com/files/SQLiteExtensions64.zip

A bunch of them are copies of the standard SQLite3 ext/misc extensions, some of 
them I wrote, and some of them came from elsewhere and the attributions are in 
the source files and are all freely available (public domain or otherwise).  
Basically I append the whole lot to the end of the sqlite3.c source and then 
add a coreinit.c that uses the builtin SQLITE_EXTRA_INIT=core_init that hooks 
all the init routines to each connection using the sqlite3_auto_extension 
interface when sqlite3 is initialized.

I will eventually get around to publishing copies of them somewhere.

--//-- coreinit.c --//--

#ifdef USE_NUNICODE
extern void* sqlite3_nunicode_init(void*);
#endif

int core_init(const char* dummy)
{
int nErr = 0;

nErr += sqlite3_auto_extension((void*)sqlite3_autobusy_init);
nErr += sqlite3_auto_extension((void*)sqlite3_ipaddress_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlfcmp_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlfunc_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlfwin_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlhash_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlitemprint_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqlmath_init);
nErr += sqlite3_auto_extension((void*)sqlite3_sqltime_init);

#ifdef USE_NUNICODE
nErr += sqlite3_auto_extension((void*)sqlite3_nunicode_init);
#else
nErr += sqlite3_auto_extension((void*)sqlite3_unifuzz_init);
#endif

nErr += sqlite3_auto_extension((void*)sqlite3_eval_init);
nErr += sqlite3_auto_extension((void*)sqlite3_fileio_init);
nErr += sqlite3_auto_extension((void*)sqlite3_ieee_init);
nErr += sqlite3_auto_extension((void*)sqlite3_nextchar_init);
nErr += sqlite3_auto_extension((void*)sqlite3_percentile_init);
nErr += sqlite3_auto_extension((void*)sqlite3_regexp_init);
nErr += sqlite3_auto_extension((void*)sqlite3_rot_init);
//  nErr += sqlite3_auto_extension((void*)sqlite3_sha_init);
nErr += sqlite3_auto_extension((void*)sqlite3_totype_init);
nErr += sqlite3_auto_extension((void*)sqlite3_zorder_init);

nErr += sqlite3_auto_extension((void*)sqlite3_fossildelta_init);

#ifndef SQLITE_OMIT_VIRTUALTABLE
nErr += sqlite3_auto_extension((void*)sqlite3_amatch_init);
nErr += sqlite3_auto_extension((void*)sqlite3_btreeinfo_init);
nErr += sqlite3_auto_extension((void*)sqlite3_carray_init);
nErr += sqlite3_auto_extension((void*)sqlite3_closure_init);
nErr += sqlite3_auto_extension((void*)sqlite3_csv_init);
nErr += 

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

[Keith Medcalf]

Just execute the SQL containing the function.  If the function does not exist 
then you will get an error thrown when you attempt to prepare the statement 
containing the function that does not exist ... and your application can parse 
the error message and do the needful.  Of course, just because the function x 
exists doesn't mean that it is the function x that you want, merely that it is 
a function named x ...

SQLite version 3.29.0 2019-05-22 23:12:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(x);
sqlite> insert into t values (10),(20),(30),(40);
sqlite> create view v1 as select stdev(x) from t;
sqlite> create view v2 as select stddev(x) from t;
sqlite> select * from v1;
12.9099444873581
sqlite> select * from v2;
Error: no such function: stddev

 
---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Thursday, 23 May, 2019 00:58
>To: SQLite mailing list
>Subject: 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 
>To: SQLite mailing list 
>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-
>>boun...@mailinglists.sqlite.org] 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 
>>To: SQLite mailing list 
>>Sent: Wednesday, May 22, 2019, 22:20:11
>>Subject: [sqlite] SQL Features That SQLite Does Not Implement
>
>
>>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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 

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

[Dominique Devienne]

On Thu, May 23, 2019 at 7:39 AM Keith Medcalf  wrote:

> 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
> ...
> sqlite> select * from pragma_collation_list order by 1, 2;
> seqname
> -  --
> 0  ROT13
> ...



> sqlite> select * from pragma_module_list order by 1;
> name
> -
> approximate_match
> carray
> completion
> ...
>


> sqlite> select * from pragma_compile_options;
> compile_options
> ALLOW_COVERING_INDEX_SCAN
> ...
>

Keith, as can be seen below, those pragma_*list eponymous vtables are you
referring to as not built-in.
I wasn't even aware one could get the function-list at all. Did you
implement that as a pure extension w/o
changing the official SQLite source code? Are all your extensions
Open-Source, on GitHub or somewhere?

Thanks, --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select * from pragma_module_list;
Error: no such table: pragma_module_list
sqlite> select * from pragma_function_list;
Error: no such table: pragma_function_list
sqlite> select * from pragma_compile_options;
COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [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(,[,])

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

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

BTW, what is your use case?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Thomas Kurz
Gesendet: Donnerstag, 23. Mai 2019 08:58
An: SQLite mailing list 
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 
To: SQLite mailing list 
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-
>boun...@mailinglists.sqlite.org] 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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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

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

[Thomas Kurz]

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 
To: SQLite mailing list 
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-
>boun...@mailinglists.sqlite.org] 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 
>To: SQLite mailing list 
>Sent: Wednesday, May 22, 2019, 22:20:11
>Subject: [sqlite] SQL Features That SQLite Does Not Implement


>On Wednesday, 22 May, 2019 11:51, sky5w...@gmail.com 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org