A tip transferring collections from one computer to another

[drDubbelklick]

Jag bytte nyligen dator från en som bara hade C: till en som både har C: och 😧

Problemet var då att få tillbaka alla kategorinamnen.

Om man ansluter läsplattan till datorn, kan man browsa foldrarna tills man hittar en fil som heter books.db.

Det visar sig att Sony använder SQLite, så om man laddar ner kommandoradsprogrammet för SQLLite, kan man med en enkel SELECT-fråga få ut kategorierna, och sedan importera dem till Excel eller i ett annat format.

Bara ett tips!

MVH

Thomas

Message was edited by: -Soup-

personal details removed

Message was edited by: drDubbelklick

Translated the title to English

[-Soup-]

Hi Thomas,

Welcome to Sony Forum! Thank you for your post. Just a note that this is an English language forum and it's recommended you post in English so your tip is easier understood by the other users.

Kind Regards,

-Soup-

Forum moderator

[drDubbelklick]

Oops, sorry. Here follows the post message in English.

This is a tip on how to retrieve the collection to book relation when you move from one computer to another.

I recently switched computers from one that only had a C drive to another that has both C and D drives.

The problem of how to restore the names of each collection as well as which book(s) belonged to the corresponding category arose.

I called the Swedish support, and they were not aware of the following, which makes it easier to rebuild your collections if you switch computers.

If you connect the PRS-T1 to the computer, you are able to browse the folders on it. Look for a file with the name books.db.

It became obvious that Sony is using SQLite as database storage for the connection between the collections and the books contained in each collection as I opened the file books.db in a text editor, and found the name SQLite at the beginning of the binary file.

(The separator I use below is TAB, which expands to eight visible spaces in the command line tool output, but it uses the tab character between each column.)

1. Surf to the page http://www.sqlite.com/download.html and right-click to download the file sqlite-shell-win32-x86-3071401.zip to a directory of your choice. Unzip this file, which only contains sqlite3.exe.

In my case, I downloaded the zip file to D:\Download\SQLite.

2. Copy the file books.db from the reader directory to the same directory that you downloaded and unzipped sqlite3.exe to.

3. Start a command prompt at the directory you downloaded the SQLite to. My command prompt window is 80 characters wide, which makes the commands and output to wrap automatically to the following line.

4. Open the client connectivity tool to the database by the following command:

D:\Download\SQLite>sqlite3.exe books.db

SQLite version 3.7.14.1 2012-10-04 19:37:12

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

5. Display the database schema:

sqlite> .tables

android_metadata  collection        dic_histories     markups

annotation        collections       freehand          periodicals

bookmark          current_position  history           preference

books             deleted_markups   layout_cache

6. Display the columns available in the tables we are to use:

sqlite> .schema collections

CREATE TABLE collections (_id INTEGER PRIMARY KEY AUTOINCREMENT,collection_id IN

TEGER,content_id INTEGER,added_order INTEGER);

sqlite> .schema collection

CREATE TABLE collection (_id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,kana_t

itle TEXT,source_id INTEGER,uuid TEXT);

CREATE TRIGGER collections_cleanup DELETE ON collection BEGIN DELETE FROM collec

tions WHERE collection_id = old._id;END;

sqlite> .schema books

CREATE TABLE books (_id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,author TEXT

,kana_title TEXT,kana_author TEXT,title_key TEXT,author_key TEXT,source_id INTEG

ER,added_date INTEGER,modified_date INTEGER,reading_time INTEGER,purchased_date

INTEGER,file_path TEXT,file_name TEXT,file_size INTEGER,thumbnail TEXT,mime_type

TEXT,corrupted INTEGER,expiration_date INTEGER,prevent_delete INTEGER,sony_id T

EXT,periodical_name TEXT,kana_periodical_name TEXT,periodical_name_key TEXT,publ

ication_date INTEGER,conforms_to TEXT,description TEXT,logos TEXT);

CREATE TRIGGER books_cleanup DELETE ON books BEGIN DELETE FROM preference WHERE

content_id = old._id;DELETE FROM current_position WHERE content_id = old._id;DEL

ETE FROM layout_cache WHERE content_id = old._id;DELETE FROM history WHERE conte

nt_id = old._id;DELETE FROM bookmark WHERE content_id = old._id;DELETE FROM anno

tation WHERE content_id = old._id;DELETE FROM freehand WHERE content_id = old._i

d;DELETE FROM collections WHERE content_id = old._id;DELETE FROM deleted_markups

WHERE content_id = old._id;END;

7. From the output above, it is obvious which tables are to be used. Set the separator between each column by issuing the following command (type .separator ", then press the tab key and type " to finish the command):

sqlite> .separator "        "

8. Tell SQLite that you want the output to be saved to a file instead of displaying it to the standard output (in the command window):

sqlite> .output collections_books.txt

9. Type the following command to extract the collection name and the name of the book:

sqlite> select collection.title, books.title from collections inner join collect

ion on collections.collection_id = collection._id inner join books on collection

s.content_id = books._id;

10. Quit SQLite in the following manner:

sqlite> .exit

Now you should have a tab separated text file called "collection_books.txt".

The file can then be imported to e.g. Excel for easier overview.

Just a tip!

Best Regard,

Thomas J. Ekman

Message was edited by: drDubbelklick

Just highlighted the word SQLite at the beginning