Ignore missing UDFs for command-line EXPLAIN?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Ignore missing UDFs for command-line EXPLAIN?

nomad
The SQLite CLI is a handy tool for explaining query plans and virtual
machine opcodes. Unfortunately it mostly doesn't do me any good because
of user-defined functions in triggers:

    sqlite> explain insert into changes default values;
    Error: no such function: debug

It would be quite useful if there was a mode/pragma/feature to ignore
these types of errors for explain [query plan].

--
Mark Lawrence
_______________________________________________
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: [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

Hick Gunter
What about loading your UDF in the CLI?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Mittwoch, 29. November 2017 09:58
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Ignore missing UDFs for command-line EXPLAIN?

The SQLite CLI is a handy tool for explaining query plans and virtual machine opcodes. Unfortunately it mostly doesn't do me any good because of user-defined functions in triggers:

    sqlite> explain insert into changes default values;
    Error: no such function: debug

It would be quite useful if there was a mode/pragma/feature to ignore these types of errors for explain [query plan].

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

nomad
On Wed Nov 29, 2017 at 09:21:47AM +0000, Hick Gunter wrote:
> What about loading your UDF in the CLI?

Do you mean with a .so/.dll? Most of my UDFs are written in Perl, as is
much of the rest of my code, and are not standalone compiled objects.

It would be sufficent if there was a way to write stub UDFs that didn't
actually do anything. For example:

    sqlite> select create_udf_stub('my_udf', 1)
    sqlite> explain select my_udf('arg');   # OK
    sqlite> select my_udf('arg');           # Errors out..

--
Mark Lawrence
_______________________________________________
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: [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

Hick Gunter
You need to duplicate whatever method you use to load your UDFs in your application - which in the end boils down to calling sqlite3_create_function() with appropriate arguments that cause "glue code" to be executed- into the CLI.

Then make that code available as an extension or a new ".perlfunc" command in your copy of the shell code.

We have shell scripts and lua packages that implement virtual tables here.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Mittwoch, 29. November 2017 11:10
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

On Wed Nov 29, 2017 at 09:21:47AM +0000, Hick Gunter wrote:
> What about loading your UDF in the CLI?

Do you mean with a .so/.dll? Most of my UDFs are written in Perl, as is much of the rest of my code, and are not standalone compiled objects.

It would be sufficent if there was a way to write stub UDFs that didn't actually do anything. For example:

    sqlite> select create_udf_stub('my_udf', 1)
    sqlite> explain select my_udf('arg');   # OK
    sqlite> select my_udf('arg');           # Errors out..

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

David Raymond
http://www.sqlite.org/compile.html


SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION

    When the SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION compile-time option is activated, SQLite will suppress "unknown function" errors when running an EXPLAIN or EXPLAIN QUERY PLAN. Instead of throwing an error, SQLite will insert a substitute no-op function named "unknown()". The substitution of "unknown()" in place of unrecognized functions only occurs on EXPLAIN and EXPLAIN QUERY PLAN, not on ordinary statements.

    When used in the command-line shell, the SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION feature allows SQL text that contains application-defined functions to be pasted into the shell for analysis and debugging without having to create and load an extension that implements the application-defined functions.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: Wednesday, November 29, 2017 5:38 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

You need to duplicate whatever method you use to load your UDFs in your application - which in the end boils down to calling sqlite3_create_function() with appropriate arguments that cause "glue code" to be executed- into the CLI.

Then make that code available as an extension or a new ".perlfunc" command in your copy of the shell code.

We have shell scripts and lua packages that implement virtual tables here.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von [hidden email]
Gesendet: Mittwoch, 29. November 2017 11:10
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

On Wed Nov 29, 2017 at 09:21:47AM +0000, Hick Gunter wrote:
> What about loading your UDF in the CLI?

Do you mean with a .so/.dll? Most of my UDFs are written in Perl, as is much of the rest of my code, and are not standalone compiled objects.

It would be sufficent if there was a way to write stub UDFs that didn't actually do anything. For example:

    sqlite> select create_udf_stub('my_udf', 1)
    sqlite> explain select my_udf('arg');   # OK
    sqlite> select my_udf('arg');           # Errors out..

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

nomad
On Wed Nov 29, 2017 at 01:57:29PM +0000, David Raymond wrote:
> http://www.sqlite.org/compile.html
>
> SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION

Exactly what I was looking for, just in the wrong places. Thanks David.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users