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
Locked
