Built in and enabled Virtual Table extensions in SQLite CLI?

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

Built in and enabled Virtual Table extensions in SQLite CLI?

John McMahon-2
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
       [hidden email]


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

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Luuk

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Kees Nuyt
In reply to this post by John McMahon-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Luuk

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Graham Holden
Wednesday, April 10, 2019, 10:28:55 AM, Luuk <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Shawn Wagner
In reply to this post by Luuk
It should be pragma_function_list(). Note the parentheses.

On Wed, Apr 10, 2019, 2:29 AM Luuk <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Graham Holden
Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Shawn Wagner
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 <[hidden email]> wrote:

> Wednesday, April 10, 2019, 11:16:07 AM, Shawn Wagner <
> [hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Keith Medcalf
In reply to this post by John McMahon-2




---
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 <[hidden email]> 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 <something>
 (b) #define <something> 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 <something> 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_<something> or SQLITE_DISABLE_<something> 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')
    Row(compile_options=u'DEFAULT_RECURSIVE_TRIGGERS')
    Row(compile_options=u'DEFAULT_WAL_AUTOCHECKPOINT=256')
    Row(compile_options=u'DEFAULT_WAL_SYNCHRONOUS=1')
    Row(compile_options=u'DEFAULT_WORKER_THREADS=8')
    Row(compile_options=u'ENABLE_8_3_NAMES=1')
    Row(compile_options=u'ENABLE_API_ARMOR')
    Row(compile_options=u'ENABLE_COLUMN_METADATA')
    Row(compile_options=u'ENABLE_COLUMN_USED_MASK')
    Row(compile_options=u'ENABLE_COSTMULT')
    Row(compile_options=u'ENABLE_CURSOR_HINTS')
    Row(compile_options=u'ENABLE_DBSTAT_VTAB')
    Row(compile_options=u'ENABLE_FTS3')
    Row(compile_options=u'ENABLE_FTS3_PARENTHESIS')
    Row(compile_options=u'ENABLE_FTS4')
    Row(compile_options=u'ENABLE_FTS5')
    Row(compile_options=u'ENABLE_GEOPOLY')
    Row(compile_options=u'ENABLE_JSON1')
    Row(compile_options=u'ENABLE_LOAD_EXTENSION')
    Row(compile_options=u'ENABLE_LOCKING_STYLE=1')
    Row(compile_options=u'ENABLE_MEMORY_MANAGEMENT')
    Row(compile_options=u'ENABLE_NORMALIZE')
    Row(compile_options=u'ENABLE_PREUPDATE_HOOK')
    Row(compile_options=u'ENABLE_RBU')
    Row(compile_options=u'ENABLE_RTREE')
    Row(compile_options=u'ENABLE_STAT4')
    Row(compile_options=u'ENABLE_STMTVTAB')
    Row(compile_options=u'ENABLE_STMT_SCANSTATUS')
    Row(compile_options=u'EXPLAIN_ESTIMATED_ROWS')
    Row(compile_options=u'EXTRA_INIT=core_init')
    Row(compile_options=u'HAVE_ISNAN')
    Row(compile_options=u'LIKE_DOESNT_MATCH_BLOBS')
    Row(compile_options=u'MAX_ATTACHED=15')
    Row(compile_options=u'MAX_WORKER_THREADS=8')
    Row(compile_options=u'PRECISE_TIME')
    Row(compile_options=u'SOUNDEX')
    Row(compile_options=u'TEMP_STORE=1')
    Row(compile_options=u'THREADSAFE=1')
    Row(compile_options=u'USE_URI')
Row(pragma_name=u'count_changes')
    Row(count_changes=0)
Row(pragma_name=u'data_store_directory')
Row(pragma_name=u'data_version')
    Row(data_version=1)
Row(pragma_name=u'database_list')
    Row(seq=0, name=u'main', file=u'')
Row(pragma_name=u'default_cache_size')
    Row(cache_size=65536)
Row(pragma_name=u'defer_foreign_keys')
    Row(defer_foreign_keys=0)
Row(pragma_name=u'empty_result_callbacks')
    Row(empty_result_callbacks=0)
Row(pragma_name=u'encoding')
    Row(encoding=u'UTF-8')
Row(pragma_name=u'foreign_key_check')
Row(pragma_name=u'foreign_key_list')
Row(pragma_name=u'foreign_keys')
    Row(foreign_keys=1)
Row(pragma_name=u'freelist_count')
    Row(freelist_count=0)
Row(pragma_name=u'full_column_names')
    Row(full_column_names=0)
Row(pragma_name=u'fullfsync')
    Row(fullfsync=0)
Row(pragma_name=u'function_list')
Row(pragma_name=u'ignore_check_constraints')
    Row(ignore_check_constraints=0)
Row(pragma_name=u'incremental_vacuum')
Row(pragma_name=u'index_info')
Row(pragma_name=u'index_list')
Row(pragma_name=u'index_xinfo')
Row(pragma_name=u'integrity_check')
    Row(integrity_check=u'ok')
Row(pragma_name=u'journal_mode')
    Row(journal_mode=u'memory')
Row(pragma_name=u'journal_size_limit')
    Row(journal_size_limit=-1)
Row(pragma_name=u'legacy_alter_table')
    Row(legacy_alter_table=0)
Row(pragma_name=u'legacy_file_format')
    Row(legacy_file_format=0)
Row(pragma_name=u'lock_proxy_file')
Row(pragma_name=u'locking_mode')
    Row(locking_mode=u'normal')
Row(pragma_name=u'max_page_count')
    Row(max_page_count=1073741823)
Row(pragma_name=u'mmap_size')
Row(pragma_name=u'module_list')
Row(pragma_name=u'optimize')
Row(pragma_name=u'page_count')
    Row(page_count=1)
Row(pragma_name=u'page_size')
    Row(page_size=4096)
Row(pragma_name=u'pragma_list')
Row(pragma_name=u'query_only')
    Row(query_only=0)
Row(pragma_name=u'quick_check')
    Row(quick_check=u'ok')
Row(pragma_name=u'read_uncommitted')
    Row(read_uncommitted=0)
Row(pragma_name=u'recursive_triggers')
    Row(recursive_triggers=1)
Row(pragma_name=u'reverse_unordered_selects')
    Row(reverse_unordered_selects=0)
Row(pragma_name=u'schema_version')
    Row(schema_version=0)
Row(pragma_name=u'secure_delete')
    Row(secure_delete=0)
Row(pragma_name=u'short_column_names')
    Row(short_column_names=1)
Row(pragma_name=u'shrink_memory')
Row(pragma_name=u'soft_heap_limit')
    Row(soft_heap_limit=0)
Row(pragma_name=u'synchronous')
    Row(synchronous=2)
Row(pragma_name=u'table_info')
Row(pragma_name=u'table_xinfo')
Row(pragma_name=u'temp_store')
    Row(temp_store=0)
Row(pragma_name=u'temp_store_directory')
Row(pragma_name=u'threads')
    Row(threads=8)
Row(pragma_name=u'user_version')
    Row(user_version=0)
Row(pragma_name=u'wal_autocheckpoint')
    Row(wal_autocheckpoint=256)
Row(pragma_name=u'wal_checkpoint')
Row(pragma_name=u'writable_schema')
    Row(writable_schema=0)
Row(function_name=u'aavg', builtin=0)
Row(function_name=u'abs', builtin=1)
Row(function_name=u'acos', builtin=0)
Row(function_name=u'aggbitmask', builtin=0)
Row(function_name=u'aggmd2', builtin=0)
Row(function_name=u'aggmd4', builtin=0)
Row(function_name=u'aggmd5', builtin=0)
Row(function_name=u'aggsha1', builtin=0)
Row(function_name=u'aggsha256', builtin=0)
Row(function_name=u'aggsha2_256', builtin=0)
Row(function_name=u'aggsha2_384', builtin=0)
Row(function_name=u'aggsha2_512', builtin=0)
Row(function_name=u'aggsha384', builtin=0)
Row(function_name=u'aggsha3_224', builtin=0)
Row(function_name=u'aggsha3_256', builtin=0)
Row(function_name=u'aggsha3_384', builtin=0)
Row(function_name=u'aggsha3_512', builtin=0)
Row(function_name=u'aggsha512', builtin=0)
Row(function_name=u'ascw', builtin=0)
Row(function_name=u'asin', builtin=0)
Row(function_name=u'atan', builtin=0)
Row(function_name=u'atan2', builtin=0)
Row(function_name=u'avg', builtin=1)
Row(function_name=u'avg', builtin=0)
Row(function_name=u'avg_angle', builtin=0)
Row(function_name=u'avg_time', builtin=0)
Row(function_name=u'bitmask', builtin=0)
Row(function_name=u'bm25', builtin=0)
Row(function_name=u'ceil', builtin=0)
Row(function_name=u'changes', builtin=1)
Row(function_name=u'char', builtin=1)
Row(function_name=u'chgsign', builtin=0)
Row(function_name=u'chrw', builtin=0)
Row(function_name=u'ci', builtin=0)
Row(function_name=u'clrbits', builtin=0)
Row(function_name=u'coalesce', builtin=1)
Row(function_name=u'compress', builtin=0)
Row(function_name=u'computerfqdn', builtin=0)
Row(function_name=u'computername', builtin=0)
Row(function_name=u'copysign', builtin=0)
Row(function_name=u'cos', builtin=0)
Row(function_name=u'cosh', builtin=0)
Row(function_name=u'count', builtin=1)
Row(function_name=u'covar', builtin=0)
Row(function_name=u'cume_dist', builtin=1)
Row(function_name=u'current_date', builtin=1)
Row(function_name=u'current_date', builtin=0)
Row(function_name=u'current_time', builtin=1)
Row(function_name=u'current_time', builtin=0)
Row(function_name=u'current_timestamp', builtin=1)
Row(function_name=u'current_timestamp', builtin=0)
Row(function_name=u'date', builtin=1)
Row(function_name=u'date', builtin=0)
Row(function_name=u'datetime', builtin=1)
Row(function_name=u'datetime', builtin=0)
Row(function_name=u'datetimesec', builtin=0)
Row(function_name=u'degrees', builtin=0)
Row(function_name=u'delta_apply', builtin=0)
Row(function_name=u'delta_create', builtin=0)
Row(function_name=u'delta_output_size', builtin=0)
Row(function_name=u'dense_rank', builtin=1)
Row(function_name=u'doesfileexist', builtin=0)
Row(function_name=u'editdist3', builtin=0)
Row(function_name=u'epsilon', builtin=0)
Row(function_name=u'eval', builtin=0)
Row(function_name=u'exp', builtin=0)
Row(function_name=u'exponent', builtin=0)
Row(function_name=u'fabs', builtin=0)
Row(function_name=u'feq', builtin=0)
Row(function_name=u'fge', builtin=0)
Row(function_name=u'fgt', builtin=0)
Row(function_name=u'first_value', builtin=1)
Row(function_name=u'firstnotnull', builtin=0)
Row(function_name=u'fle', builtin=0)
Row(function_name=u'flip', builtin=0)
Row(function_name=u'floor', builtin=0)
Row(function_name=u'flt', builtin=0)
Row(function_name=u'fmod', builtin=0)
Row(function_name=u'fne', builtin=0)
Row(function_name=u'fold', builtin=0)
Row(function_name=u'frac', builtin=0)
Row(function_name=u'fts3_tokenizer', builtin=0)
Row(function_name=u'fts5', builtin=0)
Row(function_name=u'fts5_decode', builtin=0)
Row(function_name=u'fts5_decode_none', builtin=0)
Row(function_name=u'fts5_expr', builtin=0)
Row(function_name=u'fts5_expr_tcl', builtin=0)
Row(function_name=u'fts5_fold', builtin=0)
Row(function_name=u'fts5_isalnum', builtin=0)
Row(function_name=u'fts5_rowid', builtin=0)
Row(function_name=u'fts5_source_id', builtin=0)
Row(function_name=u'gavg', builtin=0)
Row(function_name=u'geopoly_area', builtin=0)
Row(function_name=u'geopoly_bbox', builtin=0)
Row(function_name=u'geopoly_blob', builtin=0)
Row(function_name=u'geopoly_ccw', builtin=0)
Row(function_name=u'geopoly_contains_point', builtin=0)
Row(function_name=u'geopoly_debug', builtin=0)
Row(function_name=u'geopoly_group_bbox', builtin=0)
Row(function_name=u'geopoly_json', builtin=0)
Row(function_name=u'geopoly_overlap', builtin=0)
Row(function_name=u'geopoly_regular', builtin=0)
Row(function_name=u'geopoly_svg', builtin=0)
Row(function_name=u'geopoly_within', builtin=0)
Row(function_name=u'geopoly_xform', builtin=0)
Row(function_name=u'getfileattributes', builtin=0)
Row(function_name=u'glob', builtin=1)
Row(function_name=u'globu', builtin=0)
Row(function_name=u'group_concat', builtin=1)
Row(function_name=u'havg', builtin=0)
Row(function_name=u'havg_angle', builtin=0)
Row(function_name=u'hex', builtin=1)
Row(function_name=u'hexagesimal', builtin=0)
Row(function_name=u'hexw', builtin=0)
Row(function_name=u'highlight', builtin=0)
Row(function_name=u'hypot', builtin=0)
Row(function_name=u'ieee754', builtin=0)
Row(function_name=u'if', builtin=0)
Row(function_name=u'ifnull', builtin=1)
Row(function_name=u'instr', builtin=1)
Row(function_name=u'ipaddrblob', builtin=0)
Row(function_name=u'ipblobaddr', builtin=0)
Row(function_name=u'ipsubnetcontains', builtin=0)
Row(function_name=u'isclr', builtin=0)
Row(function_name=u'ismaskclr', builtin=0)
Row(function_name=u'ismaskset', builtin=0)
Row(function_name=u'isset', builtin=0)
Row(function_name=u'j0', builtin=0)
Row(function_name=u'j1', builtin=0)
Row(function_name=u'jn', builtin=0)
Row(function_name=u'json', builtin=0)
Row(function_name=u'json_array', builtin=0)
Row(function_name=u'json_array_length', builtin=0)
Row(function_name=u'json_extract', builtin=0)
Row(function_name=u'json_group_array', builtin=0)
Row(function_name=u'json_group_object', builtin=0)
Row(function_name=u'json_insert', builtin=0)
Row(function_name=u'json_object', builtin=0)
Row(function_name=u'json_patch', builtin=0)
Row(function_name=u'json_quote', builtin=0)
Row(function_name=u'json_remove', builtin=0)
Row(function_name=u'json_replace', builtin=0)
Row(function_name=u'json_set', builtin=0)
Row(function_name=u'json_type', builtin=0)
Row(function_name=u'json_valid', builtin=0)
Row(function_name=u'julianday', builtin=1)
Row(function_name=u'kurt', builtin=0)
Row(function_name=u'kurtp', builtin=0)
Row(function_name=u'lag', builtin=1)
Row(function_name=u'last_insert_rowid', builtin=1)
Row(function_name=u'last_value', builtin=1)
Row(function_name=u'lastnotnull', builtin=0)
Row(function_name=u'ldexp', builtin=0)
Row(function_name=u'lead', builtin=1)
Row(function_name=u'length', builtin=1)
Row(function_name=u'like', builtin=1)
Row(function_name=u'likelihood', builtin=1)
Row(function_name=u'likely', builtin=1)
Row(function_name=u'likeu', builtin=0)
Row(function_name=u'ln', builtin=0)
Row(function_name=u'load_extension', builtin=1)
Row(function_name=u'log', builtin=0)
Row(function_name=u'lookupname', builtin=0)
Row(function_name=u'lookupsid', builtin=0)
Row(function_name=u'lower', builtin=1)
Row(function_name=u'loweru', builtin=0)
Row(function_name=u'lsmode', builtin=0)
Row(function_name=u'ltrim', builtin=1)
Row(function_name=u'm_1_pi', builtin=0)
Row(function_name=u'm_2_pi', builtin=0)
Row(function_name=u'm_2_sqrtpi', builtin=0)
Row(function_name=u'm_deg2rad', builtin=0)
Row(function_name=u'm_e', builtin=0)
Row(function_name=u'm_ln10', builtin=0)
Row(function_name=u'm_ln2', builtin=0)
Row(function_name=u'm_log10e', builtin=0)
Row(function_name=u'm_log2e', builtin=0)
Row(function_name=u'm_pi', builtin=0)
Row(function_name=u'm_pi_2', builtin=0)
Row(function_name=u'm_pi_4', builtin=0)
Row(function_name=u'm_rad2deg', builtin=0)
Row(function_name=u'm_sqrt1_2', builtin=0)
Row(function_name=u'm_sqrt2', builtin=0)
Row(function_name=u'mantissa', builtin=0)
Row(function_name=u'match', builtin=0)
Row(function_name=u'matchinfo', builtin=0)
Row(function_name=u'max', builtin=1)
Row(function_name=u'md2', builtin=0)
Row(function_name=u'md2_query', builtin=0)
Row(function_name=u'md4', builtin=0)
Row(function_name=u'md4_query', builtin=0)
Row(function_name=u'md5', builtin=0)
Row(function_name=u'md5_query', builtin=0)
Row(function_name=u'median', builtin=0)
Row(function_name=u'min', builtin=1)
Row(function_name=u'mprint', builtin=0)
Row(function_name=u'mprint1', builtin=0)
Row(function_name=u'mprint2', builtin=0)
Row(function_name=u'mprint3', builtin=0)
Row(function_name=u'next_char', builtin=0)
Row(function_name=u'nth_value', builtin=1)
Row(function_name=u'ntile', builtin=1)
Row(function_name=u'nullif', builtin=1)
Row(function_name=u'offsets', builtin=0)
Row(function_name=u'olddatetime', builtin=0)
Row(function_name=u'olddatetimems', builtin=0)
Row(function_name=u'oldtime', builtin=0)
Row(function_name=u'oldunlocalize', builtin=0)
Row(function_name=u'oldunlocalizems', builtin=0)
Row(function_name=u'optimize', builtin=0)
Row(function_name=u'percent_rank', builtin=1)
Row(function_name=u'percentile', builtin=0)
Row(function_name=u'pow', builtin=0)
Row(function_name=u'prefix_length', builtin=0)
Row(function_name=u'printf', builtin=1)
Row(function_name=u'proper', builtin=0)
Row(function_name=u'quote', builtin=1)
Row(function_name=u'radians', builtin=0)
Row(function_name=u'random', builtin=1)
Row(function_name=u'randomblob', builtin=1)
Row(function_name=u'randomv', builtin=0)
Row(function_name=u'range', builtin=0)
Row(function_name=u'rank', builtin=1)
Row(function_name=u'ravg', builtin=0)
Row(function_name=u'readfile', builtin=0)
Row(function_name=u'regexp', builtin=0)
Row(function_name=u'replace', builtin=1)
Row(function_name=u'rms', builtin=0)
Row(function_name=u'rot13', builtin=0)
Row(function_name=u'round', builtin=1)
Row(function_name=u'roundhe', builtin=0)
Row(function_name=u'row_number', builtin=1)
Row(function_name=u'rtreecheck', builtin=0)
Row(function_name=u'rtreedepth', builtin=0)
Row(function_name=u'rtreenode', builtin=0)
Row(function_name=u'rtrim', builtin=1)
Row(function_name=u'sem', builtin=0)
Row(function_name=u'setbits', builtin=0)
Row(function_name=u'sha1', builtin=0)
Row(function_name=u'sha1_query', builtin=0)
Row(function_name=u'sha256', builtin=0)
Row(function_name=u'sha256_query', builtin=0)
Row(function_name=u'sha2_256', builtin=0)
Row(function_name=u'sha2_256_query', builtin=0)
Row(function_name=u'sha2_384', builtin=0)
Row(function_name=u'sha2_384_query', builtin=0)
Row(function_name=u'sha2_512', builtin=0)
Row(function_name=u'sha2_512_query', builtin=0)
Row(function_name=u'sha384', builtin=0)
Row(function_name=u'sha384_query', builtin=0)
Row(function_name=u'sha3_224', builtin=0)
Row(function_name=u'sha3_256', builtin=0)
Row(function_name=u'sha3_384', builtin=0)
Row(function_name=u'sha3_512', builtin=0)
Row(function_name=u'sha3_query', builtin=0)
Row(function_name=u'sha512', builtin=0)
Row(function_name=u'sha512_query', builtin=0)
Row(function_name=u'sign', builtin=0)
Row(function_name=u'sin', builtin=0)
Row(function_name=u'sinh', builtin=0)
Row(function_name=u'skew', builtin=0)
Row(function_name=u'skewp', builtin=0)
Row(function_name=u'snippet', builtin=0)
Row(function_name=u'soundex', builtin=1)
Row(function_name=u'spellfix1_editdist', builtin=0)
Row(function_name=u'spellfix1_phonehash', builtin=0)
Row(function_name=u'spellfix1_scriptcode', builtin=0)
Row(function_name=u'spellfix1_translit', builtin=0)
Row(function_name=u'sqlite_compileoption_get', builtin=1)
Row(function_name=u'sqlite_compileoption_used', builtin=1)
Row(function_name=u'sqlite_log', builtin=1)
Row(function_name=u'sqlite_record', builtin=1)
Row(function_name=u'sqlite_source_id', builtin=1)
Row(function_name=u'sqlite_version', builtin=1)
Row(function_name=u'sqrt', builtin=0)
Row(function_name=u'stdev', builtin=0)
Row(function_name=u'stdevp', builtin=0)
Row(function_name=u'strdup', builtin=0)
Row(function_name=u'strfilter', builtin=0)
Row(function_name=u'strftime', builtin=1)
Row(function_name=u'strpos', builtin=0)
Row(function_name=u'strtaboo', builtin=0)
Row(function_name=u'substr', builtin=1)
Row(function_name=u'sum', builtin=1)
Row(function_name=u'tan', builtin=0)
Row(function_name=u'tanh', builtin=0)
Row(function_name=u'time', builtin=1)
Row(function_name=u'time', builtin=0)
Row(function_name=u'timebeginperiod', builtin=0)
Row(function_name=u'timeendperiod', builtin=0)
Row(function_name=u'title', builtin=0)
Row(function_name=u'tointeger', builtin=0)
Row(function_name=u'tokenhasname', builtin=0)
Row(function_name=u'tokenhassid', builtin=0)
Row(function_name=u'toreal', builtin=0)
Row(function_name=u'total', builtin=1)
Row(function_name=u'total_changes', builtin=1)
Row(function_name=u'trim', builtin=1)
Row(function_name=u'trunc', builtin=0)
Row(function_name=u'typeof', builtin=1)
Row(function_name=u'typos', builtin=0)
Row(function_name=u'ulp', builtin=0)
Row(function_name=u'ulps', builtin=0)
Row(function_name=u'unaccent', builtin=0)
Row(function_name=u'uncompress', builtin=0)
Row(function_name=u'unicode', builtin=1)
Row(function_name=u'unifuzz', builtin=0)
Row(function_name=u'unixinstant', builtin=0)
Row(function_name=u'unixtime', builtin=0)
Row(function_name=u'unlikely', builtin=1)
Row(function_name=u'unlocaldate', builtin=0)
Row(function_name=u'unlocalize', builtin=0)
Row(function_name=u'unzorder', builtin=0)
Row(function_name=u'upper', builtin=1)
Row(function_name=u'upperu', builtin=0)
Row(function_name=u'username', builtin=0)
Row(function_name=u'usersid', builtin=0)
Row(function_name=u'uuidcreatev1', builtin=0)
Row(function_name=u'uuidcreatev4', builtin=0)
Row(function_name=u'uuidfromstring', builtin=0)
Row(function_name=u'uuidstringcreatev1', builtin=0)
Row(function_name=u'uuidstringcreatev4', builtin=0)
Row(function_name=u'uuidtostring', builtin=0)
Row(function_name=u'var', builtin=0)
Row(function_name=u'varp', builtin=0)
Row(function_name=u'writefile', builtin=0)
Row(function_name=u'y0', builtin=0)
Row(function_name=u'y1', builtin=0)
Row(function_name=u'yn', builtin=0)
Row(function_name=u'zeroblob', builtin=1)
Row(function_name=u'zorder', builtin=0)
Row(module_name=u'approximate_match')
Row(module_name=u'carray')
Row(module_name=u'csv')
Row(module_name=u'dbstat')
Row(module_name=u'delta_parse')
Row(module_name=u'fsdir')
Row(module_name=u'fts3')
Row(module_name=u'fts3tokenize')
Row(module_name=u'fts4')
Row(module_name=u'fts4aux')
Row(module_name=u'fts5')
Row(module_name=u'fts5vocab')
Row(module_name=u'fuzzer')
Row(module_name=u'generate_series')
Row(module_name=u'geopoly')
Row(module_name=u'interpolate')
Row(module_name=u'json_each')
Row(module_name=u'json_tree')
Row(module_name=u'pragma_application_id')
Row(module_name=u'pragma_auto_vacuum')
Row(module_name=u'pragma_automatic_index')
Row(module_name=u'pragma_busy_timeout')
Row(module_name=u'pragma_cache_size')
Row(module_name=u'pragma_cache_spill')
Row(module_name=u'pragma_cell_size_check')
Row(module_name=u'pragma_checkpoint_fullfsync')
Row(module_name=u'pragma_collation_list')
Row(module_name=u'pragma_compile_options')
Row(module_name=u'pragma_count_changes')
Row(module_name=u'pragma_data_version')
Row(module_name=u'pragma_database_list')
Row(module_name=u'pragma_default_cache_size')
Row(module_name=u'pragma_defer_foreign_keys')
Row(module_name=u'pragma_empty_result_callbacks')
Row(module_name=u'pragma_encoding')
Row(module_name=u'pragma_foreign_key_check')
Row(module_name=u'pragma_foreign_key_list')
Row(module_name=u'pragma_foreign_keys')
Row(module_name=u'pragma_freelist_count')
Row(module_name=u'pragma_full_column_names')
Row(module_name=u'pragma_fullfsync')
Row(module_name=u'pragma_function_list')
Row(module_name=u'pragma_ignore_check_constraints')
Row(module_name=u'pragma_index_info')
Row(module_name=u'pragma_index_list')
Row(module_name=u'pragma_index_xinfo')
Row(module_name=u'pragma_integrity_check')
Row(module_name=u'pragma_journal_mode')
Row(module_name=u'pragma_journal_size_limit')
Row(module_name=u'pragma_legacy_alter_table')
Row(module_name=u'pragma_legacy_file_format')
Row(module_name=u'pragma_locking_mode')
Row(module_name=u'pragma_max_page_count')
Row(module_name=u'pragma_module_list')
Row(module_name=u'pragma_optimize')
Row(module_name=u'pragma_page_count')
Row(module_name=u'pragma_page_size')
Row(module_name=u'pragma_pragma_list')
Row(module_name=u'pragma_query_only')
Row(module_name=u'pragma_quick_check')
Row(module_name=u'pragma_read_uncommitted')
Row(module_name=u'pragma_recursive_triggers')
Row(module_name=u'pragma_reverse_unordered_selects')
Row(module_name=u'pragma_schema_version')
Row(module_name=u'pragma_secure_delete')
Row(module_name=u'pragma_short_column_names')
Row(module_name=u'pragma_soft_heap_limit')
Row(module_name=u'pragma_synchronous')
Row(module_name=u'pragma_table_info')
Row(module_name=u'pragma_table_xinfo')
Row(module_name=u'pragma_temp_store')
Row(module_name=u'pragma_threads')
Row(module_name=u'pragma_user_version')
Row(module_name=u'pragma_writable_schema')
Row(module_name=u'prefixes')
Row(module_name=u'rtree')
Row(module_name=u'rtree_i32')
Row(module_name=u'spellfix1')
Row(module_name=u'sqlite_btreeinfo')
Row(module_name=u'sqlite_dbpage')
Row(module_name=u'sqlite_memstat')
Row(module_name=u'sqlite_stmt')
Row(module_name=u'swarmvtab')
Row(module_name=u'transitive_closure')
Row(module_name=u'unionvtab')
Row(module_name=u'wholenumber')





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

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

John McMahon-2
In reply to this post by Kees Nuyt


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
       [hidden email]


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

Re: Built in and enabled Virtual Table extensions in SQLite CLI?

Kees Nuyt
In reply to this post by Graham Holden
On Wed, 10 Apr 2019 11:05:59 +0100, you wrote:

>Wednesday, April 10, 2019, 10:28:55 AM, Luuk <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users