Initial Collection Organizing

[Sidetrack]

Hello everyone,

New to the world of Calibre and Mobileread, and I'm finding both wonderful. (also I need a way to get rid of the pesky 'Hello Sidetrack, you've never posted' message). The forums have been great to glean information so I can bring my existing collection into Calibre and organize it. I'm managing some duplicates as I do.

To compensate for some of my Garbage In, without full blown SQL available, some things that would help me (and maybe others) are some "canned SQL" functions:

unique, as in title:unique author:unique

or some other way to filter duplicate or unique terms, like maybe

expanded relational operators as in format:>1

which might turn a search like format:txt and (format:zip or format:lrf or ... ) in to format:txt and format:>1

Also, options to unmerge/split a record might be nice as well for cases when multiple editions that should be separated have gotten grouped together.

I'm finding ways to accomplish most of this within Calibre, but maybe not the best and fastest ways (enlighten me?).

This is the sort of slicing and dicing available in databases; is it worth coding in to the interface?

[kovidgoyal]

A relop for formats should be relatively easy to implement, open a ticket for it so it isn't forgotten.

[Sidetrack]

Wow, Kovid. Nice program you've got going here. Thanks for the reply.

I had to look up relop

Before I submit the ticket... I assume that the format count for a record is relatively easy, is it as easy to apply to a title/author/what-ever-else count across records? A title:>1 or title_count:<2 sort of thing?

[kovidgoyal]

No, per record counting is easy, cross record counting is not as easy.

[Starson17]

Originally Posted by Sidetrack

To compensate for some of my Garbage In, without full blown SQL available

Full blown SQL searching is available .... sort of. What's not available is Full blown SQL record updating, and the connection from the records you find via the SQL search and the GUI interface.

Thus, for duplicates:

calibre-debug -c "from calibre.library.database2 import LibraryDatabase2; db = LibraryDatabase2('/path/to/library/folder');dupes = db.conn.get('select title from books group by title having count(*) > 1;');print dupes;">dupes.txt

For duplicates with author_sort listed:

calibre-debug -c "from calibre.library.database2 import LibraryDatabase2; db = LibraryDatabase2('c:/users/frank/documents/my ebooks/calibre');dupes = db.conn.get('select title, author_sort from books group by title having count(*) > 1;');print dupes;">dupes.txt

Change the path to point to your library folder and paste it into a command window (terminal window, dos box, whatever you call it) and it will produce a file in the current directory called dupes.txt with all your duplicate titles in it.

You can also write code segments and run them with calibre-debug -e. I wrote some that generated cut and paste searches to find duplicates.

, some things that would help me (and maybe others) are some "canned SQL" functions:

Yes, these would be nice, and there have been some threads on them, and some proposals to build in certain specific functions, like duplicate finding. I've also used SQLLiteSpy on the metadata.db, when I get desperate.

[Sidetrack]

ticket #7559

[Sidetrack]

Originally Posted by Starson17

Full blown SQL searching is available .... sort of. What's not available is Full blown SQL record updating, and the connection from the records you find via the SQL search and the GUI interface.

OK, lots of good info. Gives me something to play with, thanks.

With a little formatting I could have a group of titles to past into the search dialog. Any idea how large of a string and how many search terms it'll handle?

[Starson17]

Originally Posted by Sidetrack

OK, lots of good info. Gives me something to play with, thanks.

With a little formatting I could have a group of titles to past into the search dialog. Any idea how large of a string and how many search terms it'll handle?

I don't know the limits. I generated some search query strings that exceeded my editor's line length limits. I didn't even try to paste them into the search box because I ran into another problem when searching for dupes - there was no demarcation between duplicate groups.