Question on editing database directly

[ownedbycats]

I accidentally erased timestamps of a few books while working in Calibre. No big deal:

1. Close Calibre.
2. Grab latest db backup open in SQLiteStudio.
3. Go to books table, find book, copy timestamp column.
4. Go to my library db. Paste the timestamp into same book.
5. Commit.

I've done this before with custom column, never directly on books column. It didn't work this time. SQLIteStudio reported on commit:
[11:19:09] An error occurred while committing the data: no such function: title_sort

What does this mean?

I can restore the date through GUI, will lose exact timestamp info though.

[chaley]

Several tables, including books, use sqlite triggers that ensure the data written to the table are correctly processed. The triggers reference functions in calibre's db layer, for example title_sort(). This is why using SQL browsers to modify the db often doesn't work. The only safe way to do it is by using calibre's API via a python script run with calibre-debug or via a python template.

[ownedbycats]

Ah, that makes sense. For future, is there a way to edit the time part of date column directly, aside from maybe changing the column format?

[chaley]

Originally Posted by ownedbycats

Ah, that makes sense. For future, is there a way to edit the time part of date column directly, aside from maybe changing the column format?

Not that I can think of. Perhaps an SFE in action chains along with some dialog to capture the date.

Here is a template that changes the date (timestamp) to whatever you put in line 9. Be sure to get an example so the timezone is correct.

python:
def evaluate(book, context):
	db = context.db.new_api
	# Get the current timestamp.
	v = db.field_for('timestamp', book.id)
	print(1, v)

	# The corrected timespamp goes here
	db.set_field('timestamp', {book.id: '2023-06-23 09:36:34+00:00'})

	# See if the timestamp changed.
	v = db.field_for('timestamp', book.id)
	print(2, v)

	# return the new timestamp
	return str(v)

[capink]

If you have multiple books and don't want to enter the value manually for each, another solution would be like this:

1. In your SQL Viewer/Editor export the data you want to csv. If your SQL Editor does not support exporting to csv, look for another one in google.
2. Use the Import List plugin to import the timestamps. Import List does not support importing to the original timestamps field, so you will have to import into a custom date column.
3. Use Action Chains SFE template to copy the timestamps from you custom column.

Notes:

• For your custom date column, use the iso format. It guarantees parse_date is not confused between months and days, it also includes times and time zone.
• Import List can match books using uuid. So make sure to choose that for matching books.
• If you use this method for copying values FROM custom columns, you will find the resulting csv file without the uuid field. In which case you will have to also export the books table to another csv file, and link them using VLOOKUP in excel.
• You only need to do steps 2 and 3 if you are importing into timestamps. Since Import List supports all other columns, you can directly import into them, with caveat mentioned above for custom columns.

Edit: An earlier version of this post was deleted, because I realized after posting that I needed to check some things in calibre, which I did not available at the time. Sorry to the mods for the inconvenience.

Edit2: Instead of using sql viewer to export to csv, it is better to open the backup library with calibre itself and use the catalog function to export to csv.