Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
On Wed, 10 Apr 2019 11:05:59 +0100, you wrote: >Wednesday, April 10, 2019, 10:28:55 AM, Luuk wrote: > >> On 10-4-2019 10:28, Kees Nuyt wrote: > >>> sqlite> select * from pragma_function_list; > >> sqlite> select * from pragma_function_list; >> Error: no such table: pragma_function_list > > From the help page (https://www.sqlite.org/pragma.html#pragma_function_list) > this option is only available "...if SQLite is built using the > -DSQLITE_INTROSPECTION_PRAGMAS compile-time option.". My (standard) > copy of 3.27.2 doesn't support this either, so I'm guessing Keet must > have a custom-built version. My mistake, I have that option in all my builds, I forgot about it. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
On 10/04/2019 18:28, Kees Nuyt wrote: On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: I have not used extensions before. I understand that some are included in the amalgamation source file and that some of these are enabled by default. So, which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI? By this above, I meant the pre-compiled CLI. sqlite> .mode column sqlite> .header on sqlite> .width 28 8 sqlite> select * from pragma_function_list; Thank you Kees, that didn't work (as noted by Luuk, Graham and Shawn) but it got me looking in the Pragma document and this did: sqlite> pragma compile_options: 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> also this: sqlite> select * from pragma_compile_options; compile_options COMPILER=gcc-5.2.0 ENABLE_DBSTAT_VTAB ENABLE_FTS3 ... same thing, different method. If an extension is built-in and enabled, what do I need to do to use it. The instructions seem to be for the case where an extension is built as an external library (.dll) to be loaded by eg. .load ./csv where csv would be csv.dll in the current directory. If the csv extension was built-in, would I still need to load it to activate it? I don't think so. -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Tuesday, 9 April, 2019 21:17, John McMahon wrote: >I have not used extensions before. I understand that some are >included in the amalgamation source file and that some of these >are enabled by default. You are using the term "enabled" in this paragraph to mean: (a) compiled into object code (b) the object code loaded into the process (c) the "entry point(s)" being linked into the current connection For builtin extensions these steps are all achieved by: (a) including the source code in the library surrounded by an #ifdef (b) #define when you compile the library (thus compiling the source to object code and loading it into the process) (c) including special instructions surrounded by the same #ifdef which causes the "entry point(s)" to be linked into the current connection when each connection initializes. >So, which ones are built-in and which of those are enabled >in the standard downloadable Win32 SQLite CLI? The ones which have SQLITE_ENABLE_ or SQLITE_DISABLE_ are generally available and documented to ENABLE and DISABLE, at complile time, the extensions and features that are contained within SQLite3. Examination of the makefile might be revealing (in other words, I have no clue. I build my own containing what I want so what is default I have no clue). >If an extension is built-in and enabled, what do I need to do to use >it. Nothing as all of the necessary steps (compile / load / link into the library) have been done. >The instructions seem to be for the case where an extension is built >as an external library (.dll) to be loaded by eg. .load ./csv where csv >would be csv.dll in the current directory. If the csv extension was >built-in, would I still need to load it to activate it? Well, if you have a hunk of code that operates as a function in SQLite3 then first you must compile it. You can do this either by sticking it in the sqlite3.c source code or compiling it separately. Compiling the source code to object code is one of the prerequisite steps. How you choose to perform that step is up to you. If you have compiled, lets say, the csv module as a separate dll then you must load it by using the appropriate "load the module into this process" for your OS. This is the "load_extension" call (for which a human typeable interface is provided in the CLI by the .load command). Since merely loading the code into the process does nothing other than load the code into the process, the "load_extension" call also, after loading the module, attempts to call its "go connect yourself" function. Whether the csv extension was compiled as a separate object module (shared library) or is by some other method already "loaded" into the process, you still have to call the "go connect yourself" code. This is done by the "load_extension" API. You can of course compile as many extensions as you like and include their code in the sqlite3 engine itself, and then use the SQLITE_EXTRA_INIT hook to run some code that runs the init routines of all the modules you have added so that they are all available all the time. I do this to add several hundred extensions of various types into SQLite3 so they are always available all the time. The introspection pragma's are available to examine which functcions, modules, virtual tables, etc., are currently "available". apsw compile options 3.27.2-r1 Row(sqlite_version=u'3.28.0') Row(sqlite_source_id=u'2019-04-02 00:56:20 f7ba2daf91c96ba16958188d6084a0ff129310d348932de493d326f1d246alt2') Row(pragma_name=u'application_id') Row(application_id=0) Row(pragma_name=u'auto_vacuum') Row(auto_vacuum=0) Row(pragma_name=u'automatic_index') Row(automatic_index=1) Row(pragma_name=u'busy_timeout') Row(timeout=5000) Row(pragma_name=u'cache_size') Row(cache_size=65536) Row(pragma_name=u'cache_spill') Row(cache_spill=65536) Row(pragma_name=u'case_sensitive_like') Row(pragma_name=u'cell_size_check') Row(cell_size_check=0) Row(pragma_name=u'checkpoint_fullfsync') Row(checkpoint_fullfsync=0) Row(pragma_name=u'collation_list') Row(seq=0, name=u'ROT13') Row(seq=1, name=u'NUMERICS') Row(seq=2, name=u'UNACCENTED') Row(seq=3, name=u'NAMES') Row(seq=4, name=u'NOCASEU') Row(seq=5, name=u'IPADDRESS') Row(seq=6, name=u'RTRIM') Row(seq=7, name=u'NOCASE') Row(seq=8, name=u'BINARY') Row(pragma_name=u'compile_options') Row(compile_options=u'ALLOW_COVERING_INDEX_SCAN') Row(compile_options=u'ALLOW_URI_AUTHORITY') Row(compile_options=u'COMPILER=gcc-8.1.0') Row(compile_options=u'DATETIME_NEW') Row(compile_options=u'DEFAULT_CACHE_SIZE=65536') Row(compile_options=u'DEFAULT_FOREIGN_KEYS') Row(compile_options=u'DEFAULT_MMAP_SIZE=0') Row(compile_options=u'DEFAULT_PAGE_SIZE=4096') Row(compile_options=u'DEFAULT_PROXYDIR_PERMISSIONS=0755')
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
Interesting. Must be that the pragmas that take arguments need parens when used in table valued function form. I've never tried without them when using a pragma that way. Anyways, the sqlite3 shell is built with that introspection option, at least on Linux (tested with one built from source myself without any special configure arguments, and the stock Ubuntu one). I don't have a Windows version handy to test right now. It's apparently different for some reason? On Wed, Apr 10, 2019, 3:46 AM Graham Holden wrote: > Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner < > shawnw.mob...@gmail.com> wrote: > > > It should be pragma_function_list(). Note the parentheses. > > That doesn't make a difference if the shell hasn't been built with > -DSQLITE_INTROSPECTION_PRAGMAS, and they don't appear necessary for a > pragma that IS built-in (such as "pragma_database_list"): > > SQLite version 3.27.2 2019-02-25 16:06:06 > Enter ".help" for usage hints. > sqlite> select * from pragma_database_list; > 0|main|X:\V1LS\z > sqlite> select * from pragma_database_list(); > 0|main|X:\V1LS\z > sqlite> select * from pragma_function_list ; > Error: no such table: pragma_function_list > sqlite> select * from pragma_function_list() ; > Error: no such table: pragma_function_list > > > > ___ > 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] Built in and enabled Virtual Table extensions in SQLite CLI?
Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner wrote: > It should be pragma_function_list(). Note the parentheses. That doesn't make a difference if the shell hasn't been built with -DSQLITE_INTROSPECTION_PRAGMAS, and they don't appear necessary for a pragma that IS built-in (such as "pragma_database_list"): SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> select * from pragma_database_list; 0|main|X:\V1LS\z sqlite> select * from pragma_database_list(); 0|main|X:\V1LS\z sqlite> select * from pragma_function_list ; Error: no such table: pragma_function_list sqlite> select * from pragma_function_list() ; Error: no such table: pragma_function_list ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
It should be pragma_function_list(). Note the parentheses. On Wed, Apr 10, 2019, 2:29 AM Luuk wrote: > > On 10-4-2019 10:28, Kees Nuyt wrote: > > On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: > > > >> I have not used extensions before. I understand that some are included > >> in the amalgamation source file and that some of these are enabled by > >> default. So, which ones are built-in and which of those are enabled in > >> the standard downloadable Win32 SQLite CLI? > > sqlite> .mode column > > sqlite> .header on > > sqlite> .width 28 8 > > sqlite> select * from pragma_function_list; > > D:\>sqlite3 > SQLite version 3.27.2 2019-02-25 16:06:06 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> .mode column > sqlite> .header on > sqlite> .width 28 8 > sqlite> select * from pragma_function_list; > Error: no such table: pragma_function_list > sqlite> > > This is "the standard downloadable Win32 SQLite CLI?" > > >> If an extension is built-in and enabled, what do I need to do to use it. > >> The instructions seem to be for the case where an extension is built as > >> an external library (.dll) to be loaded by eg. .load ./csv where csv > >> would be csv.dll in the current directory. If the csv extension was > >> built-in, would I still need to load it to activate it? > > I don't think so. > > > ___ > 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] Built in and enabled Virtual Table extensions in SQLite CLI?
Wednesday, April 10, 2019, 10:28:55 AM, Luuk wrote: > On 10-4-2019 10:28, Kees Nuyt wrote: >> sqlite> select * from pragma_function_list; > sqlite> select * from pragma_function_list; > Error: no such table: pragma_function_list From the help page (https://www.sqlite.org/pragma.html#pragma_function_list) this option is only available "...if SQLite is built using the -DSQLITE_INTROSPECTION_PRAGMAS compile-time option.". My (standard) copy of 3.27.2 doesn't support this either, so I'm guessing Keet must have a custom-built version. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
On 10-4-2019 10:28, Kees Nuyt wrote: On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: I have not used extensions before. I understand that some are included in the amalgamation source file and that some of these are enabled by default. So, which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI? sqlite> .mode column sqlite> .header on sqlite> .width 28 8 sqlite> select * from pragma_function_list; D:\>sqlite3 SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .mode column sqlite> .header on sqlite> .width 28 8 sqlite> select * from pragma_function_list; Error: no such table: pragma_function_list sqlite> This is "the standard downloadable Win32 SQLite CLI?" If an extension is built-in and enabled, what do I need to do to use it. The instructions seem to be for the case where an extension is built as an external library (.dll) to be loaded by eg. .load ./csv where csv would be csv.dll in the current directory. If the csv extension was built-in, would I still need to load it to activate it? I don't think so. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: > I have not used extensions before. I understand that some are included > in the amalgamation source file and that some of these are enabled by > default. So, which ones are built-in and which of those are enabled in > the standard downloadable Win32 SQLite CLI? sqlite> .mode column sqlite> .header on sqlite> .width 28 8 sqlite> select * from pragma_function_list; > If an extension is built-in and enabled, what do I need to do to use it. > The instructions seem to be for the case where an extension is built as > an external library (.dll) to be loaded by eg. .load ./csv where csv > would be csv.dll in the current directory. If the csv extension was > built-in, would I still need to load it to activate it? I don't think so. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
On 10-4-2019 05:17, John McMahon wrote: which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI Some possibility tho show this like (i.e.) '.extensions' would be very nice!? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
Hi, I have not used extensions before. I understand that some are included in the amalgamation source file and that some of these are enabled by default. So, which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI? If an extension is built-in and enabled, what do I need to do to use it. The instructions seem to be for the case where an extension is built as an external library (.dll) to be loaded by eg. .load ./csv where csv would be csv.dll in the current directory. If the csv extension was built-in, would I still need to load it to activate it? John -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
