Help with Merge Records code - E-reader users and developers?

[theducks]

Originally Posted by Starson17

So you've got two records in Calibre? And one has better metadata? What book formats are in these records? Are they different, or sometimes the same? I was just trying to deal with the issue of what to do when the destination record has a book format that is the same as a format in the record to be merged.

Originally I'd contemplated overwriting the destination format with same-formats in the to-be-merged records, then deleting the merged records. I suspect Kovid won't like that design. He didn't like my add books code that overwrote old formats with new. If I don't overwrite when formats overlap, then I also shouldn't delete the old record in a format overlap situation.

Hmmmm. I suppose I can merge only the non-overlap formats, merge the metadata, and warn about the situation. Complexity, complexifies....

If I leave records hanging around due to format overlap, does anyone feel strongly that I need to delete the formats that were merged from the merged records, or can I just leave those records alone after merging all non-overlapping formats into the destination record? The latter is easier (and already works!).

How about a tickbox(es) just like the Meta-data editor has for
Author title
social metadata
In your example case.

x Merge Comments
x Overwrite completely = Replace

[DoctorOhh]

Originally Posted by itimpi

As a general comment, in my view I do not think the merge facility should ever affect books already downloaded to an reading device. That makes the concentration on doing it in the main GUI the right way to proceed.

The merge should only concern itself with the main library not the devices.

[polly]

Originally Posted by Starson17

So you've got two records in Calibre? And one has better metadata? What book formats are in these records? Are they different, or sometimes the same? I was just trying to deal with the issue of what to do when the destination record has a book format that is the same as a format in the record to be merged.

Originally I'd contemplated overwriting the destination format with same-formats in the to-be-merged records, then deleting the merged records. I suspect Kovid won't like that design. He didn't like my add books code that overwrote old formats with new. If I don't overwrite when formats overlap, then I also shouldn't delete the old record in a format overlap situation.

Hmmmm. I suppose I can merge only the non-overlap formats, merge the metadata, and warn about the situation. Complexity, complexifies....

If I leave records hanging around due to format overlap, does anyone feel strongly that I need to delete the formats that were merged from the merged records, or can I just leave those records alone after merging all non-overlapping formats into the destination record? The latter is easier (and already works!).

I may not be the typical user. My problem/issue is that I've accumulated books over the years for reading on my Palm. Formatting wasn't nearly as important for that tiny screen as it is on the larger readers and well formatted books weren't always available. I'm something of a pack rat, so I've kept the source document plus the iSilo/mdb/mobi documents that I could read on the Palm. When I started using Calibre, I added my library with close attention to correct metadata. Then I found the books here on MobileRead and realized how much better formatted they are than the ones on Gutenberg. I'm now slowly downloading new versions of these books. When I get a new version, I tend to throw it into Calibre to see how it looks. I might convert it in an attempt to remove extra lines/headers etc. Sometimes books look better after a Calibre conversion. Finally, I decide which version looks better and delete the other one.

With your merging scheme and the check boxes recommended by TheDucks, this process would become much more simple for me. I'm with Kovid on not autodeleting duplicate formats. Just because they're both ePubs doesn't mean they're identical. Perhaps there could also be a check box for which version to keep? I think there would need to be some checkboxes for merging metadata too, since Calibre reads it from the file and inserts what it can. If my original book has all of the series information, the tags, a cover that I had to hunt down online, and a description, I'll want to keep all of that, even though I'm bringing in a book with better formatting. I would add one to TheDucks list.

x Add book but retain existing metadata.

It's more intuitively obvious to me to move the new book into an existing record versus adding the entire existing record to the new book's record. Either way, I agree that one of the steps in a merge should be deleting the duplicate.

[Starson17]

Originally Posted by polly

Just because they're both ePubs doesn't mean they're identical. Perhaps there could also be a check box for which version to keep?

I'm trying to keep it simple. At least with this first version it's going to be a true merge, which means at the end of the merge, there's only going to be one record left - the one you selected first. In that sense, it's just like the "Remove selected books" (delete) code, which the merge code I'm working on closely follows.

The main difference between merge and delete will be that merge will keep as much of the data in the records you are removing as Calibre permits. It may make it easier to understand if you just think of merge as a less drastic alternative to delete.

If you want to keep multiple copies of the same format, then you aren't really merging records. It might be nice to have some code to shuffle metadata and formats around, but that's not really what I'm trying to write.

If the EPUB's in your case aren't identical, and you still want to do a merge, then you're going to have to decide which version to keep. That means you're going to have to open them up, view them, etc. If you're going to do all that, you might as well just delete the EPUB format you don't want before you do the merge, and the issue of overlapping formats doesn't arise.

Quite honestly, it's a lot harder to write code that gets down to the level of comparing formats and putting in checkboxes for various options to deal with conflicts than it is to just deal with the ebooks at the level of the entire ebook record.

Of course, these comments apply to the code I'm writing for my personal use. Kovid is the final arbiter of what goes into Calibre. I want to be able to choose two or more records, and merge them without having to look at and compare all the individual formats. I recognize that what I want may not be what best meets the needs of others, but it's where I'm going to start.

If my original book has all of the series information, the tags, a cover that I had to hunt down online, and a description, I'll want to keep all of that, even though I'm bringing in a book with better formatting.

The code is rough, but is basically working now to do this. No checkboxes are required. It does not overwrite any formats or metadata in the first selected record. It brings in all the formats and metadata it can from the second selected record and then from the third, etc. If a metadata field is empty in the 1st, but not the second, it brings in the data from the 2nd, etc. For the comments metadata field, it concatenates the second record onto the first unless they are identical.

I still have to write the code that deals with moving the cover image if the 1st has none. I have some minor bugs in the tag code I know about. I want to clean up some of the code so it's easier to read, I've got some checking to do on how Kovid handles certain non-ascii chars and OS related issues, then I need to test it for a while on my own data. Then I'll send it off.

[polly]

Originally Posted by Starson17

If you want to keep multiple copies of the same format, then you aren't really merging records. It might be nice to have some code to shuffle metadata and formats around, but that's not really what I'm trying to write.

If the EPUB's in your case aren't identical, and you still want to do a merge, then you're going to have to decide which version to keep. That means you're going to have to open them up, view them, etc. If you're going to do all that, you might as well just delete the EPUB format you don't want before you do the merge, and the issue of overlapping formats doesn't arise.

By the time the merge hits, I would have to have looked at what I already had versus the new version and decided that the new version was better. But call me a coward. I don't delete nuttin' until I know for sure that the other one is safely in the record. None of my new stuff is in ePub and all of my new stuff is, so I suppose I can be cowardly without losing anything.


I have no interest at all in having more than one record for the same book.

Originally Posted by Starson17

The code is rough, but is basically working now to do this. No checkboxes are required. It does not overwrite any formats or metadata in the first selected record. It brings in all the formats and metadata it can from the second selected record and then from the third, etc. If a metadata field is empty in the 1st, but not the second, it brings in the data from the 2nd, etc. For the comments metadata field, it concatenates the second record onto the first unless they are identical.

So I'm assuming then that if record#1 has the metadata I want and record#2 has the book that I want, my metadata will stay put but the book from record#2 will come over? I have all of my authors as last name, first name in both author fields. If that field conflicts with record#2, would record#1's information remain? Will it be intuitively obvious which one is record #1? I could just see myself attempting the merge and having the wrong things go away.

I think this sounds like a great improvement and really appreciate you putting the time and effort into making it happen.

[Starson17]

Originally Posted by polly

So I'm assuming then that if record#1 has the metadata I want and record#2 has the book that I want, my metadata will stay put but the book from record#2 will come over?

Yes, and it doesn't matter which you select first in that case.

I have all of my authors as last name, first name in both author fields. If that field conflicts with record#2, would record#1's information remain?

Everything in Record1 is always protected.

Will it be intuitively obvious which one is record #1?

It's just the first one you click when selecting the records. It will give you a warning that you're about to merge (unless you turn the warning off)

I could just see myself attempting the merge and having the wrong things go away.

Normally, the order isn't all that important. It will keep as much metadata as can fit into a single record. If any records have a cover, it's kept. If any have publisher info, it's kept. You might have to fix the author/title if they differed and you chose the wrong one. I've been using the merge code for an hour tonight,looking for bugs. There've been a few cases where book 1 has the right title, but book 2 has the right author. I just merged and fixed the wrong one.

I think this sounds like a great improvement and really appreciate you putting the time and effort into making it happen.

I try to choose things I need, then pass them along to others. It's small potatoes compared to Kovid's work.

[Starson17]

As I use the code on my own data, I've slowly made some changes from how I first planned it:

Originally Posted by Starson17

At least with this first version it's going to be a true merge, which means at the end of the merge, there's only going to be one record left - the one you selected first.

I've reconsidered this. The Merge option will be available from a right click, like the Remove Books or Edit Metadata. Like those options, there can be multiple variations. I'm thinking of these two:

Merge into 1st selected record, delete other records after merge.
Merge into 1st selected record, keep other records after merge.

If you use the first option, you get as much metadata as will fit into the first record. Any empty fields will be populated from the other records, but duplicate formats, different authors, different titles, different series names, etc. found in the second record will all be deleted when the second and other records are deleted. You end up with only one record, and that record can only contain one author, one title, one publisher, one cover image, etc. Everything in the first record is always safe and never gets overwritten. If there's anything in the second record that you definitely want to keep, you can always delete that item from the first record and it will get moved from the second record into the empty slot in the first during merge.

If you use the second option, the first record is treated,just as above, but the other records are not deleted. It's a sort of "safe" merge, but it does leave you with the extra records. I could delete formats from the second and other records, but I think it's safer to leave them completely untouched. If you accidentally merged the wrong records, all you would have to do is delete out the wrongly added stuff from the first record. If the merge was correct, all you'd have to do is manually delete the extra records.

This would also provide a back door ability to shuffle metadata around. Delete the rating and publisher from the first record and safe merge with a second and the first gets the rating and publisher from the second. Yes, it would also get other metadata in any empty metadata slots, and that would have to be deleted, but it might be useful.

I'm planning a confirmation screen that lists the surviving record and the records that will be deleted.

I've decided to use the same confirmation dialog used for record deletion. The records you are about to delete/merge are highlighted in front of you. There's no reason to list them again in a dialog, unless you've forgotten which one you chose first, in which case, just cancel and choose again.

Concatenate all the comments from all records, <unless they are identical> (the user can edit it later)
Bring in the first valid cover image if the surviving record has none,
Concatenate tags, but remove duplicates,
Ignore publisher and ISBN,
Ratings?

I'm not sure about publisher and ISBN. At this point, I'm inclined to bring publisher in, but not ISBN, mainly because ISBN limits the results when fetching metadata. Ratings are currently being brought in from the other records if that field is empty in the first record.

What about author/title? I think author is filled with "Unknown" if it's blank. That is a valid author, but should it be overwritten if a merge record has some other author name? (If I overwrite the author in this one case, then I'd also overwrite the author_sort field) I'm not sure about title. Can that field be blank? (I'm not at home to test this stuff)

The date and pubdate fields never seem to be empty, so I'm ignoring them.

Any comments?

[theducks]

Unknown is a valid Author in my collection as I failed to note an Authors name on some FanFic that did not contain the Author in the body.

Unknown is not = to Anonymous

Does Calibre allow a blank Author or Title?
I think the SQL sets them to the default "Unknown" if left blank

[Starson17]

Originally Posted by theducks

Unknown is a valid Author in my collection

In mine, too. However, suppose you are merging two books, and one has author Unknown, and the other John Smith. Do you want to overwrite Unknown with John's name or not?

[title] I think the SQL sets them to the default "Unknown" if left blank

Same question. (I'll check to see what is in the title when it's left blank, but regardless....) If you are merging two books and one has a title exactly matching "Unknown" and the other does not, do you want to overwrite?

Generally, I assume I don't want to change author/title in the first selected record during merge, but maybe these are special cases where I should consider "Unknown" to be a blank field.

[theducks]

Originally Posted by Starson17

In mine, too. However, suppose you are merging two books, and one has author Unknown, and the other John Smith. Do you want to overwrite Unknown with John's name or not?

If "Unknown" replace if not blank

Same question. (I'll check to see what is in the title when it's left blank, but regardless....) If you are merging two books and one has a title exactly matching "Unknown" and the other does not, do you want to overwrite?

Title is defaulted to "Unknown" by Table rules if NOT explicitly set.

CREATE TABLE authors ( id   INTEGER PRIMARY KEY,
                              name TEXT NOT NULL COLLATE NOCASE,
                              sort TEXT COLLATE NOCASE,
                              UNIQUE(name)
                             );
CREATE TABLE books ( id      INTEGER PRIMARY KEY AUTOINCREMENT,
                             title     TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
                             sort      TEXT COLLATE NOCASE,
                             timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                             uri       TEXT,
                             series_index INTEGER NOT NULL DEFAULT 1,
                             author_sort TEXT COLLATE NOCASE,
                             isbn TEXT DEFAULT "" COLLATE NOCASE,
                             path TEXT NOT NULL DEFAULT ""
                        );

Question of logic :^)

You would never combine something into a "Unknown" title
and I can not see a reason setting a "Known" title to "Unknown"
How would you know which was which when combining without at least one decent title displayed.

Generally, I assume I don't want to change author/title in the first selected record during merge, but maybe these are special cases where I should consider "Unknown" to be a blank field.

Congratulations taking on this task. IMHO designing a well thought out UI is very difficult.

[kovidgoyal]

If you are special casing Unknown, be sure to use _('Unknown') so it works with languages other than english as well.

[Starson17]

Originally Posted by theducks

If "Unknown" replace ....

After looking at the code, reading comments here and thinking about it for a while, I think it makes sense to overwrite blank author/title fields (which Calibre fills with "Unknown") in the destination record (first selected) when the source record (second or subsequently selected record) has something other than "Unknown" in those fields. Author and author_sort will always be brought in as a pair, title will be treated separately.

This will treat the author/title fields as though they were empty if they have Unknown in them. Doing it this way also allows you to make a copy of a record by creating an empty record (from Add Book) then doing a safe merge into the empty record from another record. The empty record will have Unknowns in the author/title fields, and the safe merge will fill the new record with metadata from one or more subsequently selected records.

[Starson17]

Originally Posted by kovidgoyal

If you are special casing Unknown, be sure to use _('Unknown') so it works with languages other than english as well.

Will do.

Edit: Ignore the question below. As usual, when you think through the question well enough to ask it, the answer often becomes obvious.

If you read this, and can recall, I've got one last minor feature I'd like to add before final cleanup ("final cleanup" is where I try to hide all my ugly hacks before I'm found out).

After merge, I'd like to leave the records involved with the merge operation selected (i.e. highlighted in the interface).

For safe merge, that's easy, since I haven't removed any records and haven't changed the selection/highlighting in the interface. Whatever was highlighted/selected, remains highlighted/selected and there's a visual indication of what records were just processed. If a user wants to convert a safe merge to a normal merge, they can just deselect the first selected record (destination record) and hit delete.

However, I'm having trouble with a normal merge where I'd like to leave the highlighting/selection on the destination record so that the user can view or modify it. Currently, all the rows that were highlighted before merger remain highlighted after merger, and due to the removal of records above the destination record and deletion of merged records, these highlighted rows often have nothing to do with the merge operation.

Prior to merger and deletion of the second and subsequently selected records (source records), I know the book index from the database and the row number in the library_view GUI interface for the destination record. I also have the same information for the source records (which disappear after merge). If the first selected record is the "highest" row in the GUI, then the removal of the lower (source records) doesn't change its row # , but that's often not true. The first selected record might be the bottom record in the view, and removal of the other records above it cause its row # to change.

I can see how to select a record by row #, but is there a function that selects a record in the GUI by its library book id? If not, I'll write one to figure out the row # of the remaining record by comparing row #s of the source and destination records.

[Starson17]

Originally Posted by Starson17

I'm working on merge records code where you will be able to select a first record, which will survive, then more records to be merged into it.

Uploaded today as Ticket 5174.


Adds Merge books option to right click and toolbar. Select a first book, then books to merge into it. Formats and metadata are merged into the first book. No data or formats in the first book (destination) are overwritten (unless author or title are "Unknown").

Tags and comments are concatenated unless they match. Author and author_sort are moved as a unit, as are series and series_index. ISBN is not merged (my preference - easy to change).

Subsequently selected books (source) may be removed after merge or left untouched (safe merge). By safe merging into an empty book, a book and all its formats may be duplicated (useful to play around with format1->format1 conversions without risking the original format). Highlighting always remains on the selected books after normal and/or safe merge.

[Starson17]

Originally Posted by Starson17

It might be nice to have some code to shuffle metadata and formats around, but that's not really what I'm trying to write.

It's interesting, but I've actually ended up using what I did write to do exactly this - shuffle metadata. It's proved useful a few times already.

In one case, I had added a book that had title only, but it was similar to some books I was working on that had lots of metadata, and the same author. Since all books by this author were pdf's, I just safe merged an old book into the new one. The new pdf format wasn't overwritten and all the other metada was copied into the new record.

In another case, I was experimenting with HTML to EPUB conversion and wanted to change the metadata put into the EPUB but not change my good record. I created an empty record and safe merged books and data into it. Then I was free to experiment in the copy.

I wanted normal merge to merge up duplicate records that I'd inadvertently created during initial book entry. However, now that those are mostly cleaned up, I've found I use normal merge less, but I still use safe merge to do things unrelated to true record merger and more related to record and metadata copying and shuffling.