json_each() in a table trigger for an 'attached' db causes an error

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

json_each() in a table trigger for an 'attached' db causes an error

Lindsay Lawrence
Has anyone else run into this issue?

I have the following tables and trigger....

- Data table
CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    name TEXT,
    age INTEGER
);

-- Change log table
CREATE TABLE change_log (
    id INTEGER PRIMARY KEY,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action TEXT,
    table_name TEXT,
    obj_id INTEGER,
    changes TEXT
);

-- Insert Trigger
CREATE TRIGGER people_track_insert
AFTER INSERT ON people
BEGIN
  INSERT INTO change_log (action, table_name, obj_id, changes)
  SELECT
    'INSERT', 'people', NEW.id, changes
  FROM
    (SELECT
      json_group_object(col, json_array(oldval, newval)) AS changes
    FROM
      (SELECT
        json_extract(value, '$[0]') as col,
        json_extract(value, '$[1]') as oldval,
        json_extract(value, '$[2]') as newval
      FROM
        json_each(
          json_array(
            json_array('id', null, NEW.id),
            json_array('created', null, NEW.created),
            json_array('name', null, NEW.name),
            json_array('age', null, NEW.age)
          )
        )
      WHERE oldval IS NOT newval
      )
    );
END;

If I then do the following, the trigger works correctly, updating the
change_log table, etc

$> sqlite3  test.db
sqlite> INSERT INTO people (name, age) VALUES ('Alice', 30), ('Bob', 42);
sqlite> UPDATE people SET age = age + 2;
sqlite> UPDATE people SET name = 'Eva' WHERE name='Alice';
sqlite> DELETE FROM people WHERE name = 'Bob';
sqlite> SELECT * FROM change_log;

However, the above queries for the same db, when 'attached', fails.:

$> sqlite3
sqlite> attach './test.db' as test;
...

In the attached case any inserts into the table cause the trigger to fail
with something like the following error:

*Error: near line 1694: no such table: napp.json_each*

Somehow the virtual table json_each in the db trigger is not available for
an 'attached' db. It does work correctly for the main db. It also works for
an attached db as a normal query.

I am running the latest amalgamation code with the json1 extension enabled

sqlite-amalgamation-3250200.zip
(2.17 MiB) C source code as an amalgamation, version 3.25.2.
(sha1: c9ff08b91a0faacabe2acb240e5dba3cf81071f3)

Compiled with:

gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_JSON1 -DHAVE_USLEEP
-DHAVE_READLINE shell.c sqlite3.c -ldl -lreadline -lncurses -Os -o sqlite3

/Lindsay
_______________________________________________
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: json_each() in a table trigger for an 'attached' db causes an error

Richard Hipp-3
On 11/2/18, Lindsay Lawrence <[hidden email]> wrote:
> Has anyone else run into this issue?

I can confirm that it is an issue and that we are working on it.
--
D. Richard Hipp
[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: json_each() in a table trigger for an 'attached' db causes an error

Lindsay Lawrence
Thanks!

As a further note, I also tried building the shell with JSON1 enabled and
building and loading the json1 extension separately, with the same results.

SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load './json1.so'
sqlite> attach "test.db" as test;
sqlite> INSERT INTO test.people (name, age) VALUES ('Alice', 30), ('Bob',
42);
Error: no such table: test.json_each

/Lindsay


On Fri, Nov 2, 2018 at 10:00 AM Richard Hipp <[hidden email]> wrote:

> On 11/2/18, Lindsay Lawrence <[hidden email]> wrote:
> > Has anyone else run into this issue?
>
> I can confirm that it is an issue and that we are working on it.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: json_each() in a table trigger for an 'attached' db causes an error

Richard Hipp-3
Please try your test script on the latest trunk check-in and let us
know whether or not it is working for you.

--
D. Richard Hipp
[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: json_each() in a table trigger for an 'attached' db causes an error

Lindsay Lawrence
P.S.

Here are the first few lines of 'fossil timeline' on my local that I am
building.

=== 2018-11-02 ===
17:38:39 [1fa74930ab] Enhance triggers so that they can use
table-valued-functions that exist in schemas outside of the schema in which
the trigger is defined. (user: drh)
=== 2018-10-31 ===
20:52:00 [4a6ad5190b] Deploy the sqlite3Strlen30NN() function (argument
guaranteed to be non-NULL) for a small performance improvement. (user: drh)
19:01:13 [790ea39a65] *MERGE* Add support for the SQLITE_PREPARE_NORMALIZED
flag and the sqlite3_normalized_sql() when compiling with
SQLITE_ENABLE_NORMALIZE. Also remove unnecessary whitespace from Makefiles.
         (user: drh)
18:24:29 [7107f0dacf] Tweaks to the test_intarray documentation and tests.
(user: drh)
01:26:24 [4b370c74ae] *MERGE* Merge fixes from trunk, especially rebustness
against corrupt database files. (user: drh tags: apple-osx)
01:12:06 [e0d30c1862] *MERGE* Merge fuzz test cases computed by dbfuzz2.
(user: drh)
01:04:18 [d57873337a] Improved corrupt database detection in the
relocatePage() routine of the b-tree module. (user: drh)

/Lindsay


On Fri, Nov 2, 2018 at 4:49 PM Lindsay Lawrence <[hidden email]>
wrote:

> Hi,
>
> Sorry. I have had no luck with this.
>
> I followed the instructions here
> https://www.sqlite.org/getthecode.html#clone
> then built from source following the directions in README.md.
>
>   mkdir bld                ;#  Build will occur in a sibling directory
>   cd bld                   ;#  Change to the build directory
>   ../sqlite/configure      ;#  Run the configure script
>   make                     ;#  Run the makefile.
>   make sqlite3.c           ;#  Build the "amalgamation" source file
>   make test                ;#  Run some tests (requires Tcl)
>
> The attached file has the results from running 'make tests'. There are
> some errors there.
>
> This is the platform I am building on 'uname -a':
>
> Linux debianE7440-LT 3.16.0-6-amd64 #1 SMP Debian 3.16.57-2 (2018-07-14)
> x86_64 GNU/Linux
>
> The current trunk source however does not have the JSON1 extension
> embedded in the amalgamation by default. How would I add that?
> However, building the json1 extension against this source and then loading
> it from the shell reports this:
>
> SQLite version 3.8.10.1 2015-05-13 04:50:30
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .version
> SQLite 3.8.10.1 2015-05-13 04:50:30
> 59e3e9e764440b7feaafadff74f422535d21bca2
> sqlite> .load "../../../bin/json1"
> Error: ../../../bin/json1.so: undefined symbol:
> sqlite3_create_window_function
> sqlite>
>
> I'll tinker with it  a more but at this point I am not sure if it is
> because an own build configuration issue trying to test your fix or if the
> fix itself is the issue.
>
> Regards,
> /Lindsay
>
>
> On Fri, Nov 2, 2018 at 10:38 AM Richard Hipp <[hidden email]> wrote:
>
>> Please try your test script on the latest trunk check-in and let us
>> know whether or not it is working for you.
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>> _______________________________________________
>> 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: json_each() in a table trigger for an 'attached' db causes an error

Lindsay Lawrence
In reply to this post by Richard Hipp-3
Hi,

Sorry. I have had no luck with this.

I followed the instructions here
https://www.sqlite.org/getthecode.html#clone
then built from source following the directions in README.md.

  mkdir bld                ;#  Build will occur in a sibling directory
  cd bld                   ;#  Change to the build directory
  ../sqlite/configure      ;#  Run the configure script
  make                     ;#  Run the makefile.
  make sqlite3.c           ;#  Build the "amalgamation" source file
  make test                ;#  Run some tests (requires Tcl)

This is the platform I am building on 'uname -a':

Linux debianE7440-LT 3.16.0-6-amd64 #1 SMP Debian 3.16.57-2 (2018-07-14)
x86_64 GNU/Linux

The current trunk source however does not have the JSON1 extension embedded
in the amalgamation by default. How would I add that?
However, building the json1 extension against this source and then loading
it from the shell reports this:

SQLite version 3.8.10.1 2015-05-13 04:50:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.8.10.1 2015-05-13 04:50:30 59e3e9e764440b7feaafadff74f422535d21bca2
sqlite> .load "../../../bin/json1"
Error: ../../../bin/json1.so: undefined symbol:
sqlite3_create_window_function
sqlite>

I'll tinker with it  a more but at this point I am not sure if it is
because an own build configuration issue trying to test your fix or if the
fix itself is the issue.

Regards,
/Lindsay


On Fri, Nov 2, 2018 at 10:38 AM Richard Hipp <[hidden email]> wrote:

> Please try your test script on the latest trunk check-in and let us
> know whether or not it is working for you.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: json_each() in a table trigger for an 'attached' db causes an error

Keith Medcalf

configure --enable-load-extension --enable-threadsafe --with-readline-lib=auto --with-pic --enable-json1 --enable-fts3 --enable-fts4 --enable-fts5 --enable-rtree --enable-session --enable-update-limit --enable-geopoly

Adjust the configure parameters to include what you want included.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Lindsay Lawrence
>Sent: Friday, 2 November, 2018 18:12
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] json_each() in a table trigger for an
>'attached' db causes an error
>
>Hi,
>
>Sorry. I have had no luck with this.
>
>I followed the instructions here
>https://www.sqlite.org/getthecode.html#clone
>then built from source following the directions in README.md.
>
>  mkdir bld                ;#  Build will occur in a sibling
>directory
>  cd bld                   ;#  Change to the build directory
>  ../sqlite/configure      ;#  Run the configure script
>  make                     ;#  Run the makefile.
>  make sqlite3.c           ;#  Build the "amalgamation" source file
>  make test                ;#  Run some tests (requires Tcl)
>
>This is the platform I am building on 'uname -a':
>
>Linux debianE7440-LT 3.16.0-6-amd64 #1 SMP Debian 3.16.57-2 (2018-07-
>14)
>x86_64 GNU/Linux
>
>The current trunk source however does not have the JSON1 extension
>embedded
>in the amalgamation by default. How would I add that?
>However, building the json1 extension against this source and then
>loading
>it from the shell reports this:
>
>SQLite version 3.8.10.1 2015-05-13 04:50:30
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .version
>SQLite 3.8.10.1 2015-05-13 04:50:30
>59e3e9e764440b7feaafadff74f422535d21bca2
>sqlite> .load "../../../bin/json1"
>Error: ../../../bin/json1.so: undefined symbol:
>sqlite3_create_window_function
>sqlite>
>
>I'll tinker with it  a more but at this point I am not sure if it is
>because an own build configuration issue trying to test your fix or
>if the
>fix itself is the issue.
>
>Regards,
>/Lindsay
>
>
>On Fri, Nov 2, 2018 at 10:38 AM Richard Hipp <[hidden email]> wrote:
>
>> Please try your test script on the latest trunk check-in and let us
>> know whether or not it is working for you.
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>> _______________________________________________
>> 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



_______________________________________________
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: json_each() in a table trigger for an 'attached' db causes an error

Keith Medcalf
In reply to this post by Richard Hipp-3

Works for me using Lindsay's original scripts.

3.26.0 2018-11-02 17:38:39 1fa74930ab56171e2e840d4a5b259abafb0ad1e0320fc3030066570a6dd1alt2

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Richard Hipp
>Sent: Friday, 2 November, 2018 11:39
>To: SQLite mailing list
>Subject: Re: [sqlite] json_each() in a table trigger for an
>'attached' db causes an error
>
>Please try your test script on the latest trunk check-in and let us
>know whether or not it is working for you.
>
>--
>D. Richard Hipp
>[hidden email]
>_______________________________________________
>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: json_each() in a table trigger for an 'attached' db causes an error

Lindsay Lawrence
In reply to this post by Lindsay Lawrence
Ok. I am a fossil newbie. My effort to build from that did not work. The
amalgamation from configure+make seemed incomplete and other files like
json1.c were missing.
I am on trunk and fossil timeline showed Dr Hipp's fix.  I'll educate
myself a bit more on Fossil to see where I went wrong.

However, I downloaded the latest snapshot (
https://www3.sqlite.org/cgi/src/doc/trunk/README.md) and built from that
and.. *SUCCESS!*

As a note, my code is based on this article.
https://blog.budgetwithbuckets.com/2018/08/27/sqlite-changelog.html . Many
thanks to that author for the idea and sample code.

A big thank you to Dr Hipp for the prompt fix.

/Lindsay

Code output from shell built from latest trunk snapshot tarball:

SQLite version 3.26.0 2018-11-02 17:38:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.26.0 2018-11-02 17:38:39
1fa74930ab56171e2e840d4a5b259abafb0ad1e0320fc3030066570a6dd10002
gcc-4.9.2
sqlite> attach "napp.db" as napp;
sqlite> DROP TABLE napp.people;
sqlite> DROP TABLE napp.change_log;
sqlite>
sqlite> -- Data table
sqlite> CREATE TABLE napp.people (
   ...>     id INTEGER PRIMARY KEY,
   ...>     created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   ...>     name TEXT,
   ...>     age INTEGER
   ...> );
sqlite>
sqlite> -- Change log table
sqlite> CREATE TABLE napp.change_log (
   ...>     id INTEGER PRIMARY KEY,
   ...>     created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   ...>     action TEXT,
   ...>     table_name TEXT,
   ...>     obj_id INTEGER,
   ...>     changes TEXT
   ...> );
sqlite>
sqlite> -- Insert Trigger
sqlite> CREATE TRIGGER napp.people_track_insert
   ...> AFTER INSERT ON people
   ...> BEGIN
   ...>   INSERT INTO change_log (action, table_name, obj_id, changes)
   ...>   SELECT
   ...>     'INSERT', 'people', NEW.id, changes
   ...>   FROM
   ...>     (SELECT
   ...>       json_group_object(col, json_array(oldval, newval)) AS changes
   ...>     FROM
   ...>       (SELECT
   ...>         json_extract(value, '$[0]') as col,
   ...>         json_extract(value, '$[1]') as oldval,
   ...>         json_extract(value, '$[2]') as newval
   ...>       FROM
   ...>         json_each(
   ...>           json_array(
   ...>             json_array('id', null, NEW.id),
   ...>             json_array('created', null, NEW.created),
   ...>             json_array('name', null, NEW.name),
   ...>             json_array('age', null, NEW.age)
   ...>           )
   ...>         )
   ...>       WHERE oldval IS NOT newval
   ...>       )
   ...>     );
   ...> END;
sqlite>
sqlite> -- Update Trigger
sqlite> CREATE TRIGGER napp.people_track_update
   ...> AFTER UPDATE ON people
   ...> BEGIN
   ...>   INSERT INTO change_log (action, table_name, obj_id, changes)
   ...>   SELECT
   ...>     'UPDATE', 'people', OLD.id, changes
   ...>   FROM
   ...>     (SELECT
   ...>       json_group_object(col, json_array(oldval, newval)) AS changes
   ...>     FROM
   ...>       (SELECT
   ...>         json_extract(value, '$[0]') as col,
   ...>         json_extract(value, '$[1]') as oldval,
   ...>         json_extract(value, '$[2]') as newval
   ...>       FROM
   ...>         json_each(
   ...>           json_array(
   ...>             json_array('id', OLD.id, NEW.id),
   ...>             json_array('created', OLD.created, NEW.created),
   ...>             json_array('name', OLD.name, NEW.name),
   ...>             json_array('age', OLD.age, NEW.age)
   ...>           )
   ...>         )
   ...>       WHERE oldval IS NOT newval
   ...>       )
   ...>     );
   ...> END;
sqlite>
sqlite> -- Delete Trigger
sqlite> CREATE TRIGGER napp.people_track_delete
   ...> AFTER DELETE ON people
   ...> BEGIN
   ...>   INSERT INTO change_log (action, table_name, obj_id, changes)
   ...>   SELECT
   ...>     'DELETE', 'people', OLD.id, changes
   ...>   FROM
   ...>     (SELECT
   ...>       json_group_object(col, json_array(oldval, newval)) AS changes
   ...>     FROM
   ...>       (SELECT
   ...>         json_extract(value, '$[0]') as col,
   ...>         json_extract(value, '$[1]') as oldval,
   ...>         json_extract(value, '$[2]') as newval
   ...>       FROM
   ...>         json_each(
   ...>           json_array(
   ...>             json_array('id', OLD.id, null),
   ...>             json_array('created', OLD.created, null),
   ...>             json_array('name', OLD.name, null),
   ...>             json_array('age', OLD.age, null)
   ...>           )
   ...>         )
   ...>       WHERE oldval IS NOT newval
   ...>       )
   ...>     );
   ...> END;
sqlite> INSERT INTO people (name, age) VALUES ('Alice', 30), ('Bob', 42);
sqlite> UPDATE people SET age = age + 2;
sqlite> UPDATE people SET name = 'Eva' WHERE name='Alice';
sqlite> DELETE FROM people WHERE name = 'Bob';
sqlite> SELECT * FROM change_log;
1|2018-11-03
00:56:22|INSERT|people|1|{"id":[null,1],"created":[null,"2018-11-03
00:56:22"],"name":[null,"Alice"],"age":[null,30]}
2|2018-11-03
00:56:22|INSERT|people|2|{"id":[null,2],"created":[null,"2018-11-03
00:56:22"],"name":[null,"Bob"],"age":[null,42]}
3|2018-11-03 00:56:22|UPDATE|people|1|{"age":[30,32]}
4|2018-11-03 00:56:22|UPDATE|people|2|{"age":[42,44]}
5|2018-11-03 00:56:22|UPDATE|people|1|{"name":["Alice","Eva"]}
6|2018-11-03 00:56:22|DELETE|people|2|{"id":[2,null],"created":["2018-11-03
00:56:22",null],"name":["Bob",null],"age":[44,null]}
sqlite>


On Fri, Nov 2, 2018 at 5:11 PM Lindsay Lawrence <[hidden email]>
wrote:

> Hi,
>
> Sorry. I have had no luck with this.
>
> I followed the instructions here
> https://www.sqlite.org/getthecode.html#clone
> then built from source following the directions in README.md.
>
>   mkdir bld                ;#  Build will occur in a sibling directory
>   cd bld                   ;#  Change to the build directory
>   ../sqlite/configure      ;#  Run the configure script
>   make                     ;#  Run the makefile.
>   make sqlite3.c           ;#  Build the "amalgamation" source file
>   make test                ;#  Run some tests (requires Tcl)
>
> This is the platform I am building on 'uname -a':
>
> Linux debianE7440-LT 3.16.0-6-amd64 #1 SMP Debian 3.16.57-2 (2018-07-14)
> x86_64 GNU/Linux
>
> The current trunk source however does not have the JSON1 extension
> embedded in the amalgamation by default. How would I add that?
> However, building the json1 extension against this source and then loading
> it from the shell reports this:
>
> SQLite version 3.8.10.1 2015-05-13 04:50:30
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .version
> SQLite 3.8.10.1 2015-05-13 04:50:30
> 59e3e9e764440b7feaafadff74f422535d21bca2
> sqlite> .load "../../../bin/json1"
> Error: ../../../bin/json1.so: undefined symbol:
> sqlite3_create_window_function
> sqlite>
>
> I'll tinker with it  a more but at this point I am not sure if it is
> because an own build configuration issue trying to test your fix or if the
> fix itself is the issue.
>
> Regards,
> /Lindsay
>
>
> On Fri, Nov 2, 2018 at 10:38 AM Richard Hipp <[hidden email]> wrote:
>
>> Please try your test script on the latest trunk check-in and let us
>> know whether or not it is working for you.
>>
>> --
>> D. Richard Hipp
>> [hidden email]
>> _______________________________________________
>> 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