Support for hidden columns and table-valued functions

[pawelsalawa]

This is feature of virtual tables. Should be done together with better handling of virtual tables overall (#3060): https://sqlite.org/vtab.html#hidden_columns_in_virtual_tables

[houghtonap]

As a quick fix, the next time you release a new version, build SQLite with the SQLITE_ENABLE_HIDDEN_COLUMNS option. Also it would be nice if you compile SQLite to a DLL (Windows), like all the other DLL that are found in the release. That way after it gets installed a person could update SQLite with a later version or change the options for limits or features like:

• SQLITE_ENABLE_FTS5
• SQLITE_ENABLE_GEOPOLY
• SQLITE_ENABLE_ICU
• SQLITE_ENABLE_ICU_COLLATIONS
• SQLITE_ENABLE_JSON1
• SQLITE_ENABLE_MATH_FUNCTIONS
• etc.

Right now SQLite appears to be statically linked into the SQLiteStudio application since I could not find it under any of the release directories.

BTW, it would also be helpful to know what options were used to compile SQLite and list them on the website so one could determine what feature were enabled or disabled or limits changed.

FYI, this is the config.h file I use along with the -D_HAVE_SQLITE_CONFIG_H command line option when compiling SQLite for command line use on my system:

/*
** config.h
**
** -D_HAVE_SQLITE_CONFIG_H
**
** See https://sqlite.org/compile.html
** See also https://sqlite.org/limits.html
**
**/

  /* Size Limit Options */

#define SQLITE_MAX_ATTACHED=32               /* increased from         10 */
#define SQLITE_MAX_COLUMN=1024               /* decreased from       2000 */
#define SQLITE_MAX_COMPOUND_SELECT=256       /* decreased from        500 */
#define SQLITE_MAX_EXPR_DEPTH=512            /* decreased from       1000 */
#define SQLITE_MAX_FUNCTION_ARG=64           /* decreased from        100 */
#define SQLITE_MAX_LENGTH=1000000            /* decreased from 1000000000 */
#define SQLITE_MAX_LIKE_PATTERN_LENGTH=1024  /* decreased from      50000 */
#define SQLITE_MAX_PAGE_COUNT=1073741823     /* defaulted size 1073741823 */
#define SQLITE_MAX_SQL_LENGTH=1000000        /* decreased from 1000000000 */
#define SQLITE_MAX_VARIABLE_NUMBER=99        /* decreased from        999 */

  /* Recommended Options */

#define SQLITE_DEFAULT_FOREIGN_KEYS=1
#define SQLITE_DEFAULT_MEMSTATUS=0
#define SQLITE_DQS=0
#define SQLITE_FTS3_MAX_EXPR_DEPTH=12
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS
#define SQLITE_OMIT_DECLTYPE
#define SQLITE_OMIT_DEPRECATED
#define SQLITE_OMIT_PROGRESS_CALLBACK
#define SQLITE_OMIT_SHARED_CACHE

  /* Feature Options */

#define SQLITE_ENABLE_FTS5
#define SQLITE_ENABLE_GEOPOLY
#define SQLITE_ENABLE_HIDDEN_COLUMNS
#define SQLITE_ENABLE_ICU
#define SQLITE_ENABLE_ICU_COLLATIONS
#define SQLITE_ENABLE_JSON1
#define SQLITE_ENABLE_LOAD_EXTENSION
#define SQLITE_ENABLE_MATH_FUNCTIONS
#define SQLITE_ENABLE_OFFSET_SQL_FUNC
#define SQLITE_ENABLE_PREUPDATE_HOOK
#define SQLITE_ENABLE_RBU
#define SQLITE_ENABLE_RTREE
#define SQLITE_ENABLE_SESSION
#define SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
#define SQLITE_ENABLE_ZIPVFS

#define SQLITE_HAVE_C99_MATH_FUNCS
#define SQLITE_HAVE_ZLIB            /* add -lz to link library */
#define SQLITE_SOUNDEX
#define SQLITE_USER_AUTHENTICATION

[pawelsalawa]

Which SQLiteStudio version have you used?

[houghtonap]

I'm using 3.3.2 and having a heck of a time downloading 3.3.3. Every which way I try Chrome blocks me with: Your connection is not private

Attackers might be trying to steal your information from github-releases.githubusercontent.com (for example, passwords, messages, or credit cards). Learn morechrome-error://chromewebdata/#

NET::ERR_CERT_AUTHORITY_INVALID To get Chrome’s highest level of security, turn on enhanced protectionchrome-error://chromewebdata/# ReloadHide advanced

github-releases.githubusercontent.com normally uses encryption to protect your information. When Google Chrome tried to connect to github-releases.githubusercontent.com this time, the website sent back unusual and incorrect credentials. This may happen when an attacker is trying to pretend to be github-releases.githubusercontent.com, or a Wi-Fi sign-in screen has interrupted the connection. Your information is still secure because Google Chrome stopped the connection before any data was exchanged.

You cannot visit github-releases.githubusercontent.com right now because the website uses HSTS. Network errors and attacks are usually temporary, so this page will probably work later.

---

From: Paweł Salawa @.> Sent: Wednesday, April 14, 2021 4:25 PM To: pawelsalawa/sqlitestudio @.> Cc: Andrew Houghton @.>; Comment @.> Subject: Re: [pawelsalawa/sqlitestudio] Support for hidden columns and table-valued functions (#3949)

Which SQLiteStudio version have you used?

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpawelsalawa%2Fsqlitestudio%2Fissues%2F3949%23issuecomment-819808545&data=04%7C01%7C%7Ce1da934fc6734188f5b408d8ff8368d2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637540287147225933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=uixO7XCd7zKQQeQD%2FpLIfOgUj8nlK7xuZV7VESza%2FvI%3D&reserved=0, or unsubscribehttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAIKTRA7VF4644LPF5VLRQZTTIX22RANCNFSM4XDRZJAQ&data=04%7C01%7C%7Ce1da934fc6734188f5b408d8ff8368d2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637540287147235928%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=i9R7X6RBxMy1ogYSxjxs2zfaZAX39WYV6ki%2BiY48wr0%3D&reserved=0.

[pawelsalawa]

The download issue seems to be on your network. GitHub is well recognized service and I do not have such problems. Nobody else reported such problems so far. You can try downloading it from Softpedia (it is also well known and respected service) - https://www.softpedia.com/get/Internet/Servers/Database-Utils/SQLiteStudio.shtml

Regarding compilation flags, currently they are:

                    -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
                    -DSQLITE_ENABLE_DBSTAT_VTAB \
                    -DSQLITE_ENABLE_BYTECODE_VTAB \
                    -DSQLITE_ENABLE_COLUMN_METADATA \
                    -DSQLITE_ENABLE_EXPLAIN_COMMENTS \
                    -DSQLITE_ENABLE_FTS3 \
                    -DSQLITE_ENABLE_FTS4 \
                    -DSQLITE_ENABLE_FTS5 \
                    -DSQLITE_ENABLE_GEOPOLY \
                    -DSQLITE_ENABLE_JSON1 \
                    -DSQLITE_ENABLE_RTREE \
                    -DSQLITE_ENABLE_MATH_FUNCTIONS

I did not include ICU extension, because - if I'm not mistaken - it overrides the default collation, which changes the default comparing and sorting behavior, which will cause different results than in a default "precompiled" SQLite libraries available at sqlite.org, thus it will confuse most of users, by giving different results in SQLiteStudio in comparision to binaries from sqlite.org.

But in 3.3.3 the separate dll is back and you can replace it there.

[houghtonap]

I put an HTTP debug proxy on my connection. The reason downloading the latest release fails probably has to do with Microsoft and github. Only Microsoft would do something so outrageous that it would break software that lower limits on the HTTP protocol to avoid malicious hacking attempts against the browser. When you click on the link to download the zip file Microsoft github generates an HTTP 302 redirect with a host header of:

Host: github-releases.githubusercontent.com.x.98f24225093ad0405c09689026294ba3173a.9270fa55.id.opendns.com

which has a host name that is 100 characters long!!! and a URI path of:

GET /s/github-releases.githubusercontent.com/117119718/c2dd9c80-9bc8-11eb-9d18-f7869f469480?X-OpenDNS-Session=_98f24225093ad0405c09689026294ba3173a9270fa55_xxEqDmu6_X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210415%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210415T092506Z&X-Amz-Expires=300&X-Amz-Signature=b6eac28919b055e6a243cf922588a6f31c66d1a5348976913e8928d97112a9e9&X-Amz-SignedHeaders=host&actor_id=34945155&key_id=0&repo_id=117119718&response-content-disposition=attachment%3B%20filename%3Dsqlitestudio-3.3.3.zip&response-content-type=application%2Foctet-stream HTTP/1.1

which is 584 characters long!! I suspect chrome looks for long URI and host names or our corporate network does and possibly truncates them to thwart malicious hacking attempts.

As far as options go, those are not bad choices, you might want to consider -DSQLITE_HAVE_C99_MATH_FUNCS to enable a few additional math functions: trunc, acosh, asinh, atanh, -DSQLITE_ENABLE_HIDDEN_COLUMNS which enables hidden columns for virtual tables, -DSQLITE_SOUNDEX which enables soundex.

I get your point about enabling ICU and this is where it would be beneficial to compile SQLite to a DLL and place in the app directory rather than compiling it into the app, then it could be manually updated by the user to a newer version or re-compiled with different options and dropped into the app directory by the user.

[houghtonap]

I was curious about coverage of SQLite math function vs. MySQL. Here is a reference table which assumes SQLite was compiled with the -DSQLITE_ENABLE_MATH_FUNCTION and -DSQLITE_HAVE_C99_MATH_FUNCS options. There are a few minor differences:

• MySQL does not have acosh, asinh and atanh while SQLite does when the -DSQLITE_HAVE_C99_MATH_FUNCS option is specified.
• MySQL has conv, cot, crc32, format while SQLite does not.
• MySQL and SQLite differ in implementation of rand vs. random and truncate vs. trunc. SQLite's trunc is available only when the -DSQLITE_HAVE_C99_MATH_FUNCS option is specified.

package	function	args	sqlite	mysql	synonym of	note
math	abs	1	TRUE	TRUE		computes absolute value of given number
math	acos	1	TRUE	TRUE		computes arc cosine of given number
math	acosh	1	TRUE	FALSE		computes inverse hyperbolic cosine of given number
math	asin	1	TRUE	TRUE		computes arc sine of given number
math	asinh	1	TRUE	FALSE		computes the inverse hyperbolic sine of given number
math	atan	1	TRUE	TRUE		computes arc tangent of given number
math	atanh	1	TRUE	FALSE		computes the inverse hyperbolic tangent of given number
math	atan2	2	TRUE	TRUE		computes arc tangent of given numbers
math	ceil	1	TRUE	TRUE	ceiling	computes smallest integer value not less than given number
math	ceiling	1	TRUE	TRUE		computes smallest integer value not less than given number
math	conv	3	FALSE	TRUE		converts between different given number bases
math	cos	1	TRUE	TRUE		computes cosine of given number
math	cot	1	FALSE	TRUE		computes cotangent of given number
math	crc32	1	FALSE	TRUE		computes cyclic redundancy check value of given string
math	degrees	1	TRUE	TRUE		converts given radians to degrees
math	exp	1	TRUE	TRUE		computes natural logarithm base e raised to the given power
math	floor	1	TRUE	TRUE		computes largest integer value not greater than given number
math	format	2	FALSE	TRUE		formats given number rounded to given decimal places
math	ln	1	TRUE	TRUE		computes natural logarithm of given number
math	log	1	TRUE	TRUE		computes natural logarithm of given number
math	log	2	TRUE	TRUE		computes natural logarithm of given number to given base
math	log2	1	TRUE	TRUE		computes base 2 logarithm of given number
math	log10	1	TRUE	TRUE		computes base 10 logarithm of given number
math	mod	2	TRUE	TRUE		computes modulo of given number
math	pi	0	TRUE	TRUE		computes value of pi
math	pow	2	TRUE	TRUE		computes given number raised to given power
math	power	2	TRUE	TRUE	pow	computes given number raised to given power
math	radians	1	TRUE	TRUE		converts given degrees to radians
math	rand	1	FALSE	TRUE		computes random number based on given seed
math	random	0	TRUE	FALSE		computes random number based on given seed
math	round	1	TRUE	TRUE		rounds given number to nearest integer value
math	round	2	TRUE	TRUE		rounds given number to given decimal places
math	sign	1	TRUE	TRUE		computes sign of the given number
math	sin	1	TRUE	TRUE		computes sine of given number of radians
math	sqrt	1	TRUE	TRUE		computes square root of the nonnegative given number
math	tan	1	TRUE	TRUE		computes tangent of given number of radians
math	truncate	2	FALSE	TRUE		truncates given number to given number of decimal places
math	trunc	1	TRUE	FALSE		truncates given number to smallest integer value

[pawelsalawa]

Do you have any idea why these options (about hidden columns and c99 functions) are not mentioned at https://www.sqlite.org/compile.html? I'm not eager to enable options not documented officialy.

Also, MySQL is MySQL and SQLite is SQLite. You cannot expect to have everything from MySQL available in SQLite.

Soundex - makes sense.

[houghtonap]

Not sure why the options are not specified on the compile page, but suspect the documentation hasn't been updated. I would query the SQLite developers about that.

The -DSQLITE_ENABLE_HIDDEN_COLUMNS option is probably an oversight since everywhere else in the documentation it talks about virtual tables having hidden columns and how to access them. So it's not an undocumented or experimental feature. Someone reading the documentation and trying to access the columns will not understand why they cannot because this option was not enabled.

The -DSQLITE_HAVE_C99_MATH_FUNCS option is probably another oversight or it may have been intentional. If you look at the amalgamation version you will find this code starting on line 996 of version 3.35.0400:

/*
** Some C99 functions in "math.h" are only present for MSVC when its version
** is associated with Visual Studio 2013 or higher.
*/
#ifndef SQLITE_HAVE_C99_MATH_FUNCS
# if MSVC_VERSION==0 || MSVC_VERSION>=1800
#  define SQLITE_HAVE_C99_MATH_FUNCS (1)
# else
#  define SQLITE_HAVE_C99_MATH_FUNCS (0)
# endif
#endif

so it appears that if you do not specify the -DSQLITE_HAVE_C99_MATH_FUNCS option, then it checks to see whether you are compiling under a non-MSVC compiler (MSVC_VERSION=0) or anything higher than VS 2013 and it will automatically enable it!! So I guess there is no need to specifically enable this option, it does it for you.

[houghtonap]

As far as differences between MySQL vs SQLite, it's important to know the differences when you are porting SQL from either environment to the other, as I often do. It's not necessarily making SQLite into MySQL. The fact that there are minor differences isn't surprising and by the looks of it they could be handled with some additional extension functions to make porting SQL easier. Not a SQLiteStudio issue, but nice to know when working with multiple databases from different vendors.

[JrgMyr]

Some of the "missing" functions can easily added: rand <-> random and cot = 1 / tan. That way the same query runs on both engines without changes.