12-13-2011, 02:12 PM | #1 | |
Junior Member
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
|
Kindle Touch Collections Database
Quote:
Code:
$ sqlite3 ./cc.db SQLite version 3.7.7 2011-06-23 19:49:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema Entries CREATE TABLE Entries ( p_uuid PRIMARY KEY NOT NULL, -- ID of entry (UUID) p_type, -- App-defined type (string) p_location, -- Location (URI to content) p_lastAccess, -- Last access (datetime) ... |
|
12-13-2011, 02:16 PM | #2 |
Kindle Dissector
Posts: 662
Karma: 475607
Join Date: Jul 2010
Device: Amazon Kindle 3
|
Even better is the appreg.db which shows different file types and extensions and how to handle them.
|
Advert | |
|
12-13-2011, 02:23 PM | #3 |
Junior Member
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
|
Ok, it seems that Amazon has used the sqlite3 API to add some native functions to the used SQL dialect. From the start, I wasn't been able to delete rows from either the Entries nor the Collections tables.
Code:
sqlite> delete from Collections where i_member_uuid='c061612a-03fa-42b5-a089-0fc41de2f791'; Error: no such function: is_journaling_enabled Caution: the file contains only the schema and some example inserts. I would like not to disclose my reading interests to the world ;-) So don't expect to get a consistent database when importing this to sqlite. Maybe someone else can provide a direct dump of the cc.db file from a vanilla Kindle. |
12-13-2011, 02:28 PM | #4 |
Junior Member
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
|
|
12-13-2011, 02:30 PM | #5 | |
Junior Member
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
|
Quote:
|
|
Advert | |
|
12-14-2011, 03:05 PM | #6 | |
Groupie
Posts: 164
Karma: 164969
Join Date: Dec 2011
Device: Palm IIIx, (iPhone|Kindle) Touch
|
Quote:
|
|
12-15-2011, 05:21 AM | #7 | |
Junior Member
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
|
Quote:
But I wonder why Amazon has added journaling to that database. Or: what happens if the systems encounters a broken/inconsistent cc.db? Has this any fatal consequences for the catalog app? Anyone brave enough to try? ;-) |
|
12-19-2011, 12:18 PM | #8 |
Enthusiast
Posts: 29
Karma: 19952
Join Date: Sep 2011
Location: USA & UK
Device: down to just a PW4
|
I wrote stubs for missing functions so I could add my collections.json created by the kindle collections calibre plug-in to cc.db. Worked without a problem with is_journaling_enabled returning 0 (or 1, for that matter).
/* gcc -I/usr/local/include -L/usr/local/lib -Wl,-R/usr/local/lib -shared -fPIC -o libfoo.so foo.c sqlite3: .load ./libfoo.so */ #include <stdio.h> #include <sqlite3ext.h> SQLITE_EXTENSION_INIT1 //is_journaling_enabled static void ije(sqlite3_context *context, int argc, sqlite3_value **argv) { sqlite3_result_int(context, 0); } //json_string static void js(sqlite3_context *context, int argc, sqlite3_value **argv) { const char *z = sqlite3_value_text(argv[0]); sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); } //get_language_from_titles static void glft(sqlite3_context *context, int argc, sqlite3_value **argv) { sqlite3_result_text(context, "en", -1, SQLITE_STATIC); } int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) if ( sqlite3_create_function(db, "is_journaling_enabled", 0, SQLITE_ANY, NULL, &ije, NULL, NULL) ) { *pzErrMsg = sqlite3_mprintf("Can't create function: %s\n", sqlite3_errmsg(db)); return SQLITE_ERROR; } if ( sqlite3_create_function(db, "json_string", 1, SQLITE_ANY, NULL, &js, NULL, NULL) ) { *pzErrMsg = sqlite3_mprintf("Can't create function: %s\n", sqlite3_errmsg(db)); return SQLITE_ERROR; } if ( sqlite3_create_function(db, "get_language_from_titles", 1, SQLITE_ANY, NULL, &glft, NULL, NULL) ) { *pzErrMsg = sqlite3_mprintf("Can't create function: %s\n", sqlite3_errmsg(db)); return SQLITE_ERROR; } return SQLITE_OK; } |
12-19-2011, 01:59 PM | #9 |
Connoisseur
Posts: 84
Karma: 26720
Join Date: Mar 2011
Device: Kindle 3 WIFI
|
How would one go about implementing this? ^
|
12-20-2011, 05:41 PM | #10 |
Enthusiast
Posts: 29
Karma: 19952
Join Date: Sep 2011
Location: USA & UK
Device: down to just a PW4
|
I wrote the attached script to add the content of my collections.json file produced by the kindle collections calibre plug-in to the collections database cc.db on the kindle touch.
Since I've jailbroken my kindle and modified iptables, I scp the json file from /mnt/us/system on my kindle touch (put there by calaibre) to my unix box, run the foo script, and then scp the cc.db file back. (I made /var/local/cc.db a symbolic link to /mnt/us/system/cc.db.) If I knew how to cross-compile libfoo.so, I suppose it could easily be run on the KT itself. I finally have my Kindle Touch as usable/useful as my Kindle Keyboard! I will name my next cat Yifan. Thankyouthankyouthankyou!!! EDIT: I added better error handling/reporting and command line arguments to the script in the course of getting it to work on mac os lion as well. One important(!) thing I forgot to mention is that it relies on storing calibre managed content in documents/calibre on the Kindle. (I have my "Sending books to devices" settings to: "calibre/{author_sort}/{series:|| - }{series_index:0>2s|| - }{title_sort}".) I you don't like this, you can remove the 'and p_location like "%/calibre/&" from the sql. Last edited by sumpin; 01-06-2012 at 01:16 PM. Reason: Add better error handling to script |
12-20-2011, 06:34 PM | #11 | |
Connoisseur
Posts: 84
Karma: 26720
Join Date: Mar 2011
Device: Kindle 3 WIFI
|
Here is the file cc.sqls found in /usr/share/cc/
Quote:
|
|
01-05-2012, 08:08 PM | #12 |
Connoisseur
Posts: 51
Karma: 9502
Join Date: Oct 2010
Location: California
Device: Kindle 3 WiFi, Kindle 4 Touch
|
Thank you for the script! I finally got it to compile (LIBsqlite3, not plain sqlite3). Fail... Anyway, I ran the script, and it seems to have written to the cc.db file (access time is updated), however the kindle did not read the new collections. After rebooting the kindle to make sure, I took a look into the cc.db file (both on the kindle and the unix box) and both have no entries for collections.
Is there a step I am missing? Seems that the problem is with adding the correct entries to the cc.db file in the first place... |
01-06-2012, 01:17 PM | #13 |
Enthusiast
Posts: 29
Karma: 19952
Join Date: Sep 2011
Location: USA & UK
Device: down to just a PW4
|
I've updated the script attachment to the original post...
|
02-03-2012, 06:31 PM | #14 |
Junior Member
Posts: 7
Karma: 12
Join Date: Dec 2011
Device: Kindle Touch
|
Has anyone been able to use any of this to update collections using the calibre plugin? Is there a how-to somewhere?
|
02-04-2012, 04:50 AM | #15 | |
Member
Posts: 13
Karma: 10
Join Date: Feb 2012
Device: Kindle Touch
|
Quote:
In calibre I changed in Preferences -> Device Interface Plugins -> Kindle 2/3/4/Touch Device Interface, Save Template to "{series}/{title}" since I would like collections to be based on Series. The script works fine, however there are 2 things wrong with it. 1. It will delete existing collections each time. 2. It will only create Collections with Single Words. Apparently it can not handle Series(Directories?) with spaces in them. ie. Discworld will be created and books properly assigned, however The Lord of the Rings collection will not be created. If someone can fix the script, that would be awesome. |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can I set up collections on the Kindle Touch in Windows? | guspasho | Amazon Kindle | 10 | 12-13-2011 03:18 PM |
Kindle Touch sorting by collections question | tjsoundguy | Amazon Kindle | 4 | 12-01-2011 08:06 AM |
Kindle Collections on Kindle 4 (non-touch) | patrickmullin | Plugins | 0 | 11-23-2011 06:39 AM |
Touch - Will not Import Collections | nynaevelan | Amazon Kindle | 9 | 11-19-2011 06:40 PM |
Calibre Database cp Kindle Database | mitch13 | Library Management | 1 | 05-22-2011 07:33 PM |