Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Readers > Amazon Kindle > Kindle Developer's Corner

Notices

Reply
 
Thread Tools Search this Thread
Old 12-13-2011, 02:12 PM   #1
Fizzblizz
Junior Member
Fizzblizz began at the beginning.
 
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
Kindle Touch Collections Database

Quote:
Originally Posted by nynaevelan View Post
Out of curiosity has this hack revealed any collections files that the Touch uses to store collections data??
The file /var/local/cc.db seems like the collections store. It is a simple sqlite3 database with ~3 tables and a few triggers. Should be a piece of cake to write a management application:

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)
...
It even has field documentation! Every attribute is described, sometimes with multiple lines of documentation...
Fizzblizz is offline   Reply With Quote
Old 12-13-2011, 02:16 PM   #2
yifanlu
Kindle Dissector
yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.yifanlu can program the VCR without an owner's manual.
 
Posts: 662
Karma: 170717
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.
yifanlu is offline   Reply With Quote
Old 12-13-2011, 02:23 PM   #3
Fizzblizz
Junior Member
Fizzblizz began at the beginning.
 
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
I am more into PostgreSQL. Maybe someone here can identify the needed external functions to be present from the schema description which can be downloaded from here.

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.
Fizzblizz is offline   Reply With Quote
Old 12-13-2011, 02:28 PM   #4
Fizzblizz
Junior Member
Fizzblizz began at the beginning.
 
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
Quote:
Originally Posted by yifanlu View Post
Even better is the appreg.db which shows different file types and extensions and how to handle them.
Yes, already seen that. It looks to me as the Touch would be the hackers paradise ;-)
Fizzblizz is offline   Reply With Quote
Old 12-13-2011, 02:30 PM   #5
Fizzblizz
Junior Member
Fizzblizz began at the beginning.
 
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
Quote:
Originally Posted by Fizzblizz View Post
Code:
sqlite> delete from Collections where i_member_uuid='c061612a-03fa-42b5-a089-0fc41de2f791';
Error: no such function: is_journaling_enabled
Also: this also happens when I use the sqlite3 binary present on the Kindle. I suppose they use an embedded sqlite3 client which uses create_function to add operations.
Fizzblizz is offline   Reply With Quote
Old 12-14-2011, 03:05 PM   #6
bhaak
Groupie
bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.bhaak can program the VCR without an owner's manual.
 
bhaak's Avatar
 
Posts: 161
Karma: 164959
Join Date: Dec 2011
Device: Palm IIIx, (iPhone|Kindle) Touch
Quote:
Originally Posted by Fizzblizz View Post
Also: this also happens when I use the sqlite3 binary present on the Kindle. I suppose they use an embedded sqlite3 client which uses create_function to add operations.
The function is defined /usr/lib/ccat/sql_functions.lua but it only consists of a call to is_collections_journaling_enabled() which I couldn't find anywhere.
bhaak is offline   Reply With Quote
Old 12-15-2011, 05:21 AM   #7
Fizzblizz
Junior Member
Fizzblizz began at the beginning.
 
Posts: 8
Karma: 10
Join Date: Dec 2011
Device: Kindle Touch 3G
Quote:
Originally Posted by bhaak View Post
The function is defined /usr/lib/ccat/sql_functions.lua but it only consists of a call to is_collections_journaling_enabled() which I couldn't find anywhere.
Ah, thanks. I think it shouldn't matter to just return false from the function. A management app could just copy the entire cc.db, commit the changes, and, if anything goes wrong just copy back the old version.

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? ;-)
Fizzblizz is offline   Reply With Quote
Old 12-19-2011, 12:18 PM   #8
sumpin
Enthusiast
sumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to behold
 
Posts: 25
Karma: 19952
Join Date: Sep 2011
Location: USA
Device: Kindle 3/5/PW, Nook ST/1, iPad 1/3
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;
}
sumpin is offline   Reply With Quote
Old 12-19-2011, 01:59 PM   #9
dasmoover
Connoisseur
dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.
 
Posts: 84
Karma: 26720
Join Date: Mar 2011
Device: Kindle 3 WIFI
How would one go about implementing this? ^
dasmoover is offline   Reply With Quote
Old 12-20-2011, 05:41 PM   #10
sumpin
Enthusiast
sumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to behold
 
Posts: 25
Karma: 19952
Join Date: Sep 2011
Location: USA
Device: Kindle 3/5/PW, Nook ST/1, iPad 1/3
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.
Attached Files
File Type: gz foo.tar.gz (3.1 KB, 109 views)

Last edited by sumpin; 01-06-2012 at 01:16 PM. Reason: Add better error handling to script
sumpin is offline   Reply With Quote
Old 12-20-2011, 06:34 PM   #11
dasmoover
Connoisseur
dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.dasmoover is not intimidated by interfenestral monkeys.
 
Posts: 84
Karma: 26720
Join Date: Mar 2011
Device: Kindle 3 WIFI
Here is the file cc.sqls found in /usr/share/cc/

Quote:
/* Copyright (c) 2010-2011 Amazon Technologies, Inc. All rights reserved.
* PROPRIETARY/CONFIDENTIAL
* Use is subject to license terms.
*
*
* This file contains SQL statements to initialize the Content Catalog database from
* scratch and to update an existing database.
*
http://gitbrew.org/~dasmoover/kindle/touch/dev/cc.sqls
dasmoover is offline   Reply With Quote
Old 01-05-2012, 08:08 PM   #12
vaniaspeedy
Connoisseur
vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.vaniaspeedy knows what is on the back of the AURYN.
 
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...
vaniaspeedy is offline   Reply With Quote
Old 01-06-2012, 01:17 PM   #13
sumpin
Enthusiast
sumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to beholdsumpin is a splendid one to behold
 
Posts: 25
Karma: 19952
Join Date: Sep 2011
Location: USA
Device: Kindle 3/5/PW, Nook ST/1, iPad 1/3
I've updated the script attachment to the original post...
sumpin is offline   Reply With Quote
Old 02-03-2012, 06:31 PM   #14
Faw
Junior Member
Faw began at the beginning.
 
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?
Faw is offline   Reply With Quote
Old 02-04-2012, 04:50 AM   #15
Culex
Member
Culex began at the beginning.
 
Posts: 13
Karma: 10
Join Date: Feb 2012
Device: Kindle Touch
Quote:
Originally Posted by Faw View Post
Has anyone been able to use any of this to update collections using the calibre plugin? Is there a how-to somewhere?
On the wiki there is a download for the collections script you can get here. Will require the Launcher to be installed.

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.
Culex is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

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


All times are GMT -4. The time now is 12:43 AM.


MobileRead.com is a privately owned, operated and funded community.