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 <thinknl...@gmail.com> 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 <d...@sqlite.org> 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 >> d...@sqlite.org >> _______________________________________________ >> 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
