classification
Title: include built-in Math functions in SQLite to 3.35.0 of march 2021
Type: enhancement Stage: patch review
Components: Library (Lib), Windows Versions:
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: Big Stone, erlendaasland, paul.moore, rhettinger, steve.dower, steven.daprano, tim.golden, zach.ware
Priority: normal Keywords: patch

Created on 2020-12-19 15:32 by Big Stone, last changed 2021-02-18 18:11 by mark.dickinson.

Pull Requests
URL Status Linked Edit
PR 24053 open erlendaasland, 2021-01-01 20:18
Messages (12)
msg383383 - (view) Author: Big Stone (Big Stone) Date: 2020-12-19 15:32
SQlite-3.35.0 of mach 2021 will have Built-In Math Functions option.

https://sqlite.org/draft/releaselog/3_35_0.html

Would it be possible to have it activated in the following versions update of Python ? It's pretty usefull for some basic statistics
msg383386 - (view) Author: Steven D'Aprano (steven.daprano) * (Python committer) Date: 2020-12-19 16:21
As far as I can tell, every one of those are already available in Python.

https://docs.python.org/3/library/math.html
msg383503 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2020-12-21 09:50
I see no reason not to build SQLite with -DSQLITE_ENABLE_MATH_FUNCTIONS for the Windows and macOS builds. Why take the detour through the Python library when you can use the built-in SQLite functions?

The change is trivial: Add -DSQLITE_ENABLE_MATH_FUNCTIONS to PCbuild/sqlite3.vcxproj and Mac/BuildScript/build-installer.py.
msg383506 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2020-12-21 10:12
Seems like the math functions will be included by default anyways, so we'll have to explicitly exclude them if we don't want them.

https://www.sqlite.org/draft/compile.html#enable_math_functions:
"This optionĀ [SQLITE_ENABLE_MATH_FUNCTIONS] is automatically added to the Makefile by the configure script on unix platforms, unless the --disable-math option is used."
msg383552 - (view) Author: Steven D'Aprano (steven.daprano) * (Python committer) Date: 2020-12-21 20:59
It is probably harmless to enable the option, especially if it will be 
the SQLite default, but why would you do your maths computations in sql, 
using the limited set of functions available, when you could do them in 
Python, with a much larger set of available functions and better 
debugging facilities?
msg383555 - (view) Author: Raymond Hettinger (rhettinger) * (Python committer) Date: 2020-12-21 21:22
> but why would you do your maths computations in sql?

There are number of reasons it might be more convenient in SQL.  For example, it could be handy to create views measured in degrees instead of radians.  That might make where clauses in queries a little easier to read.


> Seems like the math functions will be included by default anyways

So, can this be closed?
msg383556 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2020-12-21 21:33
> So, can this be closed?

For macOS, the SQLITE_ENABLE_MATH_FUNCTIONS option is enabled by default, but I'm not sure about the Windows build. The SQLite docs says: 
"This option is also included on Windows builds using the "Makefile.msc" makefile for nmake."
msg384095 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2020-12-30 23:47
Using SQLite 3.35.0 nightly build, building Python using PCbuild/build.bat and PCBuild\pcbuild.sln with VS 2017 (does this reflect how the Windows team build the installers?), I can confirm that math functions are _not_ included by default. I added SQLITE_ENABLE_MATH_FUNCTIONS to PCbuild/sqlite3.vcxproj, but then VS 2017 fails building sqlite:

1>------ Rebuild All started: Project: sqlite3, Configuration: Debug Win32 ------
1>sqlite3.c
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119997): error C2099: initializer is not a constant
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119997): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119997): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [5]'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119998): error C2099: initializer is not a constant
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119998): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119998): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [8]'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119999): error C2099: initializer is not a constant
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119999): warning C4047: 'initializing': 'FuncDef *' differs in levels of indirection from 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)'
1>c:\src\cpython.git\externals\sqlite-3.33.0.0\sqlite3.c(119999): warning C4047: 'initializing': 'void (__cdecl *)(sqlite3_context *,int,sqlite3_value **)' differs in levels of indirection from 'char [6]'
1>Done building project "sqlite3.vcxproj" -- FAILED.
========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========

(Note, I've overwritten the files in the externals\sqlite-3.33.0.0 directory with the nightly build.)
msg384137 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2020-12-31 20:25
FYI, I've opened a thread on the SQLite Forum: https://sqlite.org/forum/forumpost/721645dc36
msg384193 - (view) Author: Big Stone (Big Stone) Date: 2021-01-01 17:50
it seems resolved now, at least with VS 2019
msg384204 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2021-01-01 19:56
Yes, R. Hipp has fixed this here: https://www2.sqlite.org/cgi/src/info/e5d7209e118a8453
We still need to modify PCbuild in order to build the SQLite library with math functions from 3.35.0 and onwards.
msg386624 - (view) Author: Erlend Egeberg Aasland (erlendaasland) * Date: 2021-02-08 08:52
Steve, I've put up a PR that prepares the Windows build for this. Would you mind taking a look at it?
History
Date User Action Args
2021-02-18 18:11:35mark.dickinsonsetnosy: - mark.dickinson
2021-02-09 11:37:11erlendaaslandsetnosy: + paul.moore, tim.golden, zach.ware
components: + Library (Lib), Windows
2021-02-08 12:10:20mark.dickinsonsetnosy: + mark.dickinson
2021-02-08 08:52:41erlendaaslandsetnosy: + steve.dower
messages: + msg386624
2021-01-01 20:18:48erlendaaslandsetkeywords: + patch
stage: patch review
pull_requests: + pull_request22888
2021-01-01 19:56:08erlendaaslandsetmessages: + msg384204
2021-01-01 17:50:30Big Stonesetmessages: + msg384193
2020-12-31 20:25:41erlendaaslandsetmessages: + msg384137
2020-12-30 23:47:00erlendaaslandsetmessages: + msg384095
2020-12-21 21:33:34erlendaaslandsetmessages: + msg383556
2020-12-21 21:22:35rhettingersetnosy: + rhettinger
messages: + msg383555
2020-12-21 20:59:43steven.dapranosetmessages: + msg383552
2020-12-21 10:12:32erlendaaslandsetmessages: + msg383506
2020-12-21 09:50:58erlendaaslandsetnosy: + erlendaasland
messages: + msg383503
2020-12-19 16:21:14steven.dapranosetnosy: + steven.daprano
messages: + msg383386
2020-12-19 15:32:41Big Stonecreate