Recover "Modified" column values after adding new coluns in library

[John Maverick]

Yesterday I added 3 columns to a library with 30K of books downloaded with the plugin FanFicFare. The problem is that after doing the restart that Calibre asked, the column "last_modified" for all the books was changed to yesterday date. So now I can't use that column to sort my books for last updated (or at last last changed).

There is some way to recover the original values of "last_modified" column?

I do have backups of my library, but I was hoping to use them as a last resort.

Also, Is changing the is the last_modified column to the current time the expected behavior when adding new columns to a library?

Calibre v6.1.0
SO: Linux Mint 20.3 “Una”

if that is the case, there is some way to add new columns to a library without changing the last_modified column?

Thanks in advance.

[DNSB]

Originally Posted by John Maverick

Yesterday I added 3 columns to a library with 30K of books downloaded with the plugin FanFicFare. The problem is that after doing the restart that Calibre asked, the column "last_modified" for all the books was changed to yesterday date. So now I can't use that column to sort my books for last updated (or at last last changed).

You have modified the library entry and yes, that will update the last modified column. I have been surprised on several occasions by what trigger an update of the last modified column .

Originally Posted by John Maverick

There is some way to recover the original values of "last_modified" column?

I do have backups of my library, but I was hoping to use them as a last resort.

Also, Is changing the is the last_modified column to the current time the expected behavior when adding new columns to a library?

if that is the case, there is some way to add new columns to a library without changing the last_modified column?

The database has been written so, AFAIK, no way to restore the old values. Not that it does you much good now but you can use one of the Job Spy's functions to save and restore the last modified column values. Personally I use a custom column to record when I last edited/modified an ebook since the last modified date is affected by way too many possibilities to be worth using.

As for your last questions, yes that is the expected behaviour. You are modifying the library entry and that is reflected in the last modified date.

If there is a way of adding columns without updating the last modified value, either chaley or kovidgoyal are likely the best people to answer that question. I suspect the answer will be no.

[theducks]

Not really.
Last_modified changes any time the (book) record gets dirtied by a change.
Any change. adding/removing a column affects all records.
You can't even save the column value to another 'timestamp' column, because that activity changes things. Same goes for a restore attempt.

If you want to track (manually), some action you did on a book, do that in a custom column. I do that when I make edits (style tweaks or typo fixes)

[John Maverick]

Originally Posted by DNSB

Personally I use a custom column to record when I last edited/modified an ebook since the last modified date is affected by way too many possibilities to be worth using.

To think that was one of the reason I added the the new custom columns.

Originally Posted by theducks

Not really.
Last_modified changes any time the (book) record gets dirtied by a change.
Any change. adding/removing a column affects all records.
You can't even save the column value to another 'timestamp' column, because that activity changes things. Same goes for a restore attempt.

Just what I was fearing.

Leaving aside the last_modified column who was working as expected.

Is there a way to populate a custom column with the modified date of the epubs files of a library?

At least I would want a new column with the last time the book was updated.

Thanks for your answers.

[theducks]

<brainstorming> A whole lot of command line steps, if it was possible

dir *.epub /S /T:W >foo.log (Dir /? for what the switches do)

Example of One EPUB book found (you search for each format desired)

 Directory of C:\Users\BigHD\Public2\MainLib\Odette C. Bell\Vira Episode One (17367)

06/21/20  06:21           252,997 Vira Episode One - Odette C. Bell.epub
               1 File(s)        252,997 bytes

Now you need to parse each record for the ID and date-time (see the green highlight

NEED I SAY BACK THINGS UP, This could go waaaay south if not done perfect

Then use https://manual.calibre-ebook.com/gen...tml#set-custom to insert the timestamp into a custom column

[John Maverick]

Originally Posted by theducks

<brainstorming> A whole lot of command line steps, if it was possible

dir *.epub /S /T:W >foo.log (Dir /? for what the switches do)

Example of One EPUB book found (you search for each format desired)

 Directory of C:\Users\BigHD\Public2\MainLib\Odette C. Bell\Vira Episode One (17367)

06/21/20  06:21           252,997 Vira Episode One - Odette C. Bell.epub
               1 File(s)        252,997 bytes

Now you need to parse each record for the ID and date-time (see the green highlight

NEED I SAY BACK THINGS UP, This could go waaaay south if not done perfect

Then use https://manual.calibre-ebook.com/gen...tml#set-custom to insert the timestamp into a custom column

That seems like a possible, even if not easy, method.

Another thing I thought if I restore the library from the backup I have.

Is there a way to backup last_modified column to a external file or something? That way even when the last_modified is updated when I try to add the customs columns again I can import the values to one of the new custom columns.

And again a lot of thanks for the idea.

[theducks]

last-modified gets triggered by so many things that I am not sure what will happen.
Just writing ANY data that affects that title (like a case change for a TAG also used in this book) will dirty the record and update metadata.opf stored with the book

[John Maverick]

Originally Posted by theducks

last-modified gets triggered by so many things that I am not sure what will happen.
Just writing ANY data that affects that title (like a case change for a TAG also used in this book) will dirty the record and update metadata.opf stored with the book

I was hoping that reading the database file, somehow, get the data I need. I suppose the database format is one custom that only Calibre can read. And Calibre will always update last-modified.

Again, a lot of thanks.

[theducks]

Originally Posted by John Maverick

I was hoping that reading the database file, somehow, get the data I need. I suppose the database format is one custom that only Calibre can read. And Calibre will always update last-modified.

Again, a lot of thanks.

No the DB is a standard SQLlite (It IS fully relational). There are free tools to let you do stuff.
BE CAREFULL, you can muck things really bad if you are not absolutly correct.

BTW the commandline tools (calibredb.exe) can work with any LibraryDB specified and that deals with all the needed stuff.

[DNSB]

Originally Posted by John Maverick

That seems like a possible, even if not easy, method.

Another thing I thought if I restore the library from the backup I have.

Is there a way to backup last_modified column to a external file or something? That way even when the last_modified is updated when I try to add the customs columns again I can import the values to one of the new custom columns.

And again a lot of thanks for the idea.

As mentioned earlier, the Job Spy plugin has an option to save and restore the last modified data.

[John Maverick]

Originally Posted by DNSB

As mentioned earlier, the Job Spy plugin has an option to save and restore the last modified data.

I was checking that. It seems that there is no easy way to restore the values of modified data. So I'm going to delete my library and restore it with a previous backup. And use Job Spy to backup last modified column before adding the custom columns again.

@DNSB

[ownedbycats]

For the future, check out the Last Modified plugin. It's a lot more customizable than the built-in one.