PRS-500 Manually Creating Collections

[phumphries]

Hi, all.

(If this post belongs in the Dev forum, moderators, please move it.)

I have a Sony PRS-500; the other members of my family are PRS-T1 users. Both readers allow for "Collections," which seems like a great idea because we all love reading series which means that I often load a whole bunch of books with similar titles onto each ereader. However, the PRS-500 does not allow the creation of Collections on the device, itself; one must use Calibre or Sony's software. The T1 allows for the creation of Collections on the device, but I have four T1s on which I want to create (mostly the same) collections.

Why not use one of those software packages? Well, call me a Libertarian, if you like, but I do not think that I should have to link my ereader to my computer before I can use it. So, I put books on the SD and MicroSD cards, and that is the extent of the connection between the ereader(s) and the outside world. (I did not see an "old man" smiley in the list.)

Maybe I was not using the correct keywords, but I had a terrible time finding out anything about the "admin" file formats on the Sony ereaders. I finally found a post in these forums that lead me to the XML code that I needed for the PRS-500, and a little careful reading of the database file, itself, finally revealed the format of the file on the T1.

I was hoping for an easy way to make the same collections on multiple devices. Editing the XML works great on the PRS-500, but loading the T1's database externally is really only kind of an interesting hack because using the UI on the device is actually quicker, since I cannot "bulk add" the collections, anyway.

So, if it makes you happy, use Calibre or the Sony software or, on a newer ereader, the ereader UI, itself, to make your lists, but here is how I do it (on my own PRS-500, at least):

PRS-500:
The PRS-500 uses a simple XML file to organize its books; "Collections" are the ebook equivalent of music playlists, and, in the case of the PRS-500, use the same name.

The file of interest for creating Collections is the cache.xml file, which you can find on the ereader's internal drive or on the SD card in the Sony Reader\database directory. Since I use only the SD card, I just plug it into my computer, edit the file and then insert it back into the ereader. (You need an "upgraded" PRS-500 to use Collections on the SD card, I understand, but the instructions are the same for the internal memory.)

Here is a sample of code that you would find in the cache.xml file:

<text author="A. Great Author" status="0" sourceid="13" id="87" date="Tue, 28 Aug 2012 15:49:31 GMT" mime="application/epub+zip" path="Sony eReader/Books/A Series/06 Book 6.epub" size="255039" title="Book 6"/>
<text author="A. Great Author" page="2" pageOffset="0" part="0" scale="0" status="0" sourceid="13" id="88" date="Tue, 28 Aug 2012 15:49:31 GMT" mime="application/epub+zip" path="Sony eReader/Books/A Series/01 Book 1.epub" size="224570" title="Book 1">
	<history>
		<bookmark date="Wed, 15 Apr 2009 20:54:19 GMT" name="A bookmark" page="2" pageOffset="0" pages="127" part="0" scale="0" synced="false"/>
	</history>
</text>
<text author="A. Great Author" page="116" pageOffset="1" part="0" scale="0" status="0" sourceid="13" id="89" date="Tue, 28 Aug 2012 15:49:32 GMT" mime="application/epub+zip" path="Sony eReader/Books/A Series/02 Book 2.epub" size="275996" title="Book 2"/>
<text author="A. Great Author" page="78" pageOffset="2" part="0" scale="0" status="0" sourceid="13" id="90" date="Tue, 28 Aug 2012 15:49:32 GMT" mime="application/epub+zip" path="Sony eReader/Books/A Series/03 Book 3.epub" size="258623" title="Book 3"/>
<text author="A. Great Author" page="0" pageOffset="0" part="0" scale="0" status="0" sourceid="13" id="91" date="Tue, 28 Aug 2012 15:49:32 GMT" mime="application/epub+zip" path="Sony eReader/Books/A Series/04 Book 4.epub" size="302551" title="Book 4"/>
<text author="A. Great Author" page="0" pageOffset="0" part="0" scale="0" status="0" sourceid="13" id="92" date="Tue, 28 Aug 2012 15:49:32 GMT" mime="application/epub+zip" path="Sony eReader/Books/A Series/05 Book 5.epub" size="307212" title="Book 5"/>

These entries illustrate what you would typically find. The important part is to identify, and in the order that you want to list them, the ID numbers, in this case, 87 through 92 (although, note, not exactly in that order!).

So, each book has an ID number, and each Collection will need one, too. You just use the next available ID number; skip to the end of the cache.xml file, read the last "id=" entry, and add one to it to create your new Collection.

The code to add a collection just goes between the last entry (book or collection) and the </cache> tag that closes the file. To create a Collection for the sample books, the code looks like this:

<playlist title="A Great Series" sourceid="13" id="131">
	<item id="88"/>
	<item id="89"/>
	<item id="90"/>
	<item id="91"/>
	<item id="92"/>
	<item id="87"/>
</playlist>

The last book in my file had an ID of 130 so that my Collection became 131. Use a descriptive title for display in your list and add an "item" element for each book, in the order in which you want them listed, between the playlist tags. This is really what I was after -- my books grouped by series and listed in the order that I want to read them. I want to manually specify the series, rather than just using the built in "by author" list, because the author field seems to be open to wide interpretation by publishers and not all series are by a single author or editor, either. And, I want to manually specify the order of the books, since the titles do not always indicate in which order I should read them.

This technique works really well for me on my PRS-500, and I just edit the cache.xml file directly on my SD card (my editor automatically creates a backup file should I ever mess it up).

PRS-T1:
The new ereaders use a SQLite database file to manage the various "admin" elements of the UI. I use SQLite Manager for Firefox to work with SQLite databases, but any SQLite tool will do.

The file of interest for this workaround is books.db in the Sony_Reader\database directory. I copy this file to my desktop, work with it, there, and then copy it back so that I have a "good" copy at all times.

Using Firefox and the SQLite Manager extension, open the books.db database. Click on the books table in the left pane and identify the "_id" numbers and order for the books that you want to add to your new Collection. (I copy the "_id" cell for each book -- by right clicking on the "_id" cell of interest -- into a text document to create my list.)

Click on the collection (no "s" at the end) table and add a collection by clicking on the Add button in the right pane. The "_id" field will automatically fill when you save the new entry, generating the "_id" number that you will need to associate the books with the Collection. You do not need to worry about the "kana_title" and "uuid" fields. Fill in the "title" field with a name for your collection ("A Great Series") and put the number one (1) in the "source_id" field. With SQLite Manager, you have to click "OK" and then confirm the creation of the row by clicking "OK" again, and, finally, click "Cancel" to close the "Add" dialog box.

Copy the newly generated "_id" number from the new Collection.

Now, you click on the collections (note the "s" at the end!) table and add your books. Each entry will be automatically assigned its own unique "_id" -- you have to put the "collection_id" from the new collection that you created and the "content_id" from each book into each record. Ignore the "added_order" field.

Because of the need to find the internal ID numbers, doing this manually is not really a time saver. Using the UI on the ereader is faster and, I imagine, safer. But, I can see the potential to create a GUI to make doing a whole bunch a lot faster, and perhaps even tie in the directory structure to provide a "wizard" for it. If I load up any more 19-book series that everyone wants to read, I will consider it, but I am happy just to have figured it out, for now.

[BaenSidhe]

Why don't you use Calibre to manage your collections? Create your collections on Calibre, send the books to your SD card, and then put your SD card in your reader. The collections should show up in your T1s and 500.

[phumphries]

I do use Calibre extensively to fix up the strangely large number of poorly published ebooks that I come across, before they go on my ereader, but I want a direct method to manage my Collections. To expand upon my remarks, above, I do not link my books to the shelves on which I keep them; so, I see no reason to link my ereader to specific software packages or desktop hardware, either.

Editing the XML file works for me on my PRS-500.

I offer the details, here, for anyone who is interested in the internal workings of the Sony ereaders.