[GUI Plugin] Audit Log

[DaltonST]

[GUI Plugin] Audit Log


Summary:

Undo/Reverse most metadata deletes and updates days or weeks later for selected books and log entries. Creates a log entry for every addition, change or delete for most Calibre standard metadata columns, and for all custom metadata columns.

The 'Audit Log Activity Detail' listing may be viewed, optionally exported to the Clipboard as a tab-delimited file, and then purged (or not). If it is purged, no Undo's may be performed for the deleted items, but rather for only future metadata changes.

Undo's may themselves be Undone, of course.


Logging Metadata Scope:

The Standard Calibre metadata columns that are tracked by Audit Log are:

• title
• author_sort [1]
• pubdate [2]
• series
• series index
• tags
• publisher
• identifiers
• comments [3]
• languages
• formats

[1] Author_sort is used as a proxy for authors, as it greatly simplifies the logging for books with multiple co-authors. Example: a book with 100 co-authors would require 100 lines in the log, whereas using author_sort requires only 1 line in the log.

[2] Pubdate is expressed in the original +0000 UTC, not the Local Time of the User initiating a change in pubdate.

[3] Comments can be quite long, so only the first 25 characters are shown in the 'Audit Log Activity Detail'. Internally, AL captures the full length, which is available for viewing via a DB browsing utility if necessary. The table to browse is '_audit_log'.

Optionally, AL also tracks all changes to all Custom Columns.

You can track Custom Columns and not Standard Columns, and vice versa. Plus, both of course.


Undo/Reverse Metadata Scope:

The Standard Calibre metadata columns that may be Undone:

• title
• pubdate
• series
• publisher
• identifiers
• comments
• languages

All Custom Calibre metadata columns may be Undone.



Types of Activities Triggering Logging:

• User metadata changes via the Calibre GUI for the current Library, including Standard Calibre functionality as well as Calibre Plug-ins.
• User copying of books from the current Library to another Library. The activity in the other Library will be logged in that other Library. If the copy is a "copy it and then delete it" type, the deletion of the book from the current Library will be logged in the current Library.
• Executing Calibre via its command line utilities if it results in a metadata.db database update of any of the metadata currently within the scope of AL.
• Executing any non-Calibre (external third-party) programs that directly update the metadata.db database for any of the metadata currently within the scope of AL.

Example Uses:

• Track what metadata within the scope of AL has been changed, when it was changed, and what the old and new metadata values were. Why do this? Because you just want to, or actually need to.
• If you know who was using your Calibre Library at a specific time, then you have an audit trail of what they did. If so, you can reverse what they did that was logged by AL.
• Reconstruct the metadata for a book that was erroneously deleted (assuming you have the original ebook format itself to use to add back to Calibre), and for which you had no Calibre backup.
• Easily confirm the results of a 'Merge Books' add/copy scenario, and change the pre-existing book back to either what it was or what it should be if so desired.
• Use as a testing tool for Calibre plug-ins that change metadata columns that are within the scope of AL.
• Discover or keep track of unknown non-Calibre software (e.g., a third-party ebook application) that updates metadata.db via direct database updates.

Requires Minimum Calibre Version: 6.0.0


Version History:

Spoiler:

Version 1.0.19 - 2023-01-07 Qt.core.
Version 1.0.18 - 2022-04-13 Qt6 Compatibility; Minimum Calibre Version now 6.0.0 (5.99.9+)
Version 1.0.16 - 2020-12-19 Miscellany.
Version 1.0.15 - 2020-08-01 Qt tweaks for small displays.
Version 1.0.14 - 2019-12-20 Technical changes after Python 3.8 testing with Calibre 4.99.2
Version 1.0.13 - 2019-12-16 Technical Tweaks after compiling with Python 3.8.150
Version 1.0.12 - 2019-12-15 Python 3.8 Compatibility; Minimum Calibre Version now 4.6.0
Version 1.0.11 - 2019-04-30 Additional Python 3 Compatibility.
Version 1.0.10 - 2019-04-24 Additional Python 3 Compatibility.
Version 1.0.9 - 2019-04-21 Python 3 Compatibility; Minimum Calibre Version now 3.41.3.
Version 1.0.8 - 2017-09-12 New triggers added (silently) for table 'data' for formats.
Version 1.0.7 - 2017-05-22 New trigger added (silently) for comments that are 'updated' by certain plugins. Calibre itself does only an 'insert or replace', not an 'update'.
Version 1.0.6 - 2017-04-29 New menu option: Show Audit Log Status for Calibre Libraries.
Version 1.0.5 - 2017-04-28 Functionality tweaks.
Version 1.0.4 - 2017-04-27 Various functionality enhancements.
Version 1.0.3 - 2017-04-26 New menu option: Undo/Reverse Delete or Update. This is available for all custom columns plus title, pubdate, language, series, comments, identifiers, and publisher. Also, a few triggers have been reworked to improve functionality. To install this Version, AL must be totally deactivated to drop all of AL's SQLite objects in every Library for which it was ever activated, then Calibre restarted, and then AL re-activated.
Version 1.0.2 - 2017-04-20 All triggers have been reworked to improve both performance and functionality. AL must be totally deactivated to drop all of AL's SQLite objects, Calibre restarted, and then AL re-activated.
Version 1.0.1 - 2017-04-19 The scope of metadata now includes all custom columns if desired. Standard Calibre columns and/or custom columns may be activated. However, deactivation deactivates everything.
Version 1.0.0 - 2017-04-18 Initial release.

[DaltonST]

Reserved for future use.

[DaltonST]

Version 1.0.1 - 2017-04-19 The scope of metadata now includes all custom columns if desired. Standard Calibre columns and/or custom columns may be activated. However, deactivation deactivates everything.



DaltonST

[DaltonST]

Version 1.0.2 - 2017-04-20 All triggers and the log table have been reworked to improve both performance and functionality. AL must be totally deactivated to drop all of AL's SQLite objects, Calibre restarted, and then AL re-activated.



DaltonST

[DaltonST]

New menu option: Undo/Reverse Delete or Update. This is available for all custom columns plus title, pubdate, language, series, comments, identifiers, and publisher.

A few SQLite triggers have been reworked to improve functionality, so to install this Version, AL must be totally deactivated to drop all of AL's SQLite objects in every Library for which it was ever activated, then Calibre restarted, and then AL re-activated.

See the attached Undo/Reverse example.


DaltonST

[Dopedangel]

This plugin seem really useful specially for mass metadata downloads. But I think the name doesn't describe what it does as well as it should. You should name it something like undo metadata or Log for metadata undo.

[Fbone]

Where does it obtain the book number?

[DaltonST]

Version 1.0.9 - 2019-04-21 Python 3 Compatibility; Minimum Calibre Version now 3.41.3


Back up your Calibre metadata.db files before installing or upgrading Audit Log.



DaltonST

[DaltonST]

Version 1.0.13 - 2019-12-16 Technical Tweaks after compiling with Python 3.8.

[DaltonST]

Version 1.0.14 - 2019-12-20 Technical changes after Python 3.8 testing with Calibre 4.99.2


DaltonST

[mobidummy]

Can you Please release HERE your last Calibre 3.48.0 compatible version of your plugin.

I think it was 1.0.9 maybe 1.0.11

There are some poor People who have to stay on 3.48 on some Devices.
I lost my last 3.48 instance with the old Pluginversion.
And its not possible to download old versions from inbuilt PluginManager.

THANKs

[Mountain343]

Is there a way to have this plugin track changes made via the Tag Browser?

For example, if I change a misspelled tag, Los Angels to Los Angeles and it edits the tag in 18 books, I'd like that to be logged.

Also is there a way to know what book # corresponds to what book? For example when it says book 30583 books_tags_link tag insert ... what's the easiest way of finding book 30583?

Thanks!

[DaltonST]

Originally Posted by Mountain343

Is there a way to have this plugin track changes made via the Tag Browser?

For example, if I change a misspelled tag, Los Angels to Los Angeles and it edits the tag in 18 books, I'd like that to be logged.

As the Original Post says, Tags is one of the Standard Columns that AL logs at the database level.

If some Calibre action changes table "tags" in metadata.db, and if you have AL activated for that Library, it will log it as soon as Calibre updates the database from its Cache, which is updated from its GUI by you.

AL has no control over when Calibre updates metadata.db.

To force the Tag Browser to totally refresh itself, simply close it and then reopen it using the icon in the lower right corner of the GUI.

You might use a more powerful editing tool such as "Manage Tags" to change the spelling en masse.

Also is there a way to know what book # corresponds to what book? For example when it says book 30583 books_tags_link tag insert ... what's the easiest way of finding book 30583?

Search for 'id:30583'.


DaltonST

[Mountain343]

Originally Posted by DaltonST

As the Original Post says, Tags is one of the Standard Columns that AL logs at the database level.

If some Calibre action changes table "tags" in metadata.db, and if you have AL activated for that Library, it will log it as soon as Calibre updates the database from its Cache, which is updated from its GUI by you.

AL has no control over when Calibre updates metadata.db.

To force the Tag Browser to totally refresh itself, simply close it and then reopen it using the icon in the lower right corner of the GUI.

You might use a more powerful editing tool such as "Manage Tags" to change the spelling en masse.


Search for 'id:30583'.


DaltonST

Thank you for the reply!

I think I've done what you said, but I still can't seem to get the plugin to work though when changing tags via the "Manage Tags" or "Tag Browser"

calibre, version 4.15.0
Audit Log: These are the current Audit Log statuses of each Library for which AL activation has taken place.
Missing Libraries, if any, will be added here the next time that you use them: T:/FULL LIBRARY-2017-07-25 status: ...ACTIVATED...

If I change a single book's tag the log looks like this:

Time +0000 UTC	Book	Table Name	Table Column	Table Action	Value Old	Value New	Other
2020-05-13 19:47:26	30562	books_tags_link	tag 	insert		Test Tag

Bulk edit from the main window works as well.

2020-05-13 19:59:36Time +0000 UTC	Book	Table Name	Table Column	Table Action	Value Old	Value New	Other
2020-05-13 19:59:36	30580	books_tags_link	tag 	insert		Test Tag	
2020-05-13 19:59:36	30582	books_tags_link	tag 	insert		Test Tag

If I go to tag browser and rename Test Tag to TestTag, close tag browser, reopen tag browser, I see the changes in the books, as well as in the tag browser, but nothing in the audit log. It still shows just the 1 row from above.

If I go to Category Editor (tags) and rename TestTag to Testing Tag, click ok, close the tag browser, reopen tag browser, I still see the changes in the books, and in the tag browser, but nothing in the audit log.

I even tried rebooting the computer and restarting Calibre.

I know I must be doing something wrong if you say it tracks changes made by the tag browser and Category Editor... but I haven't a clue what!

Any ideas?

Thanks!

[DaltonST]

AL logs at the book level. Every log entry it makes must have a book id in it. When you did a rename at the very top tag level, and did nothing at the book level, nothing was logged because nothing at the book level was changed.

However, if you selected only books with the "old tag", and then used bulk metadata edit of books (book level) to simultaneously delete the "old tag" and add the "new tag", then AL would show everything (deletes and adds).

Also, if you drag-and-drop a book onto a tag in the Tag Browser to which it should be assigned, that also triggers a book-level event which will be logged by AL.



DaltonST