where are user categories stored on calibre database?

xiquinho · 01-15-2013, 02:18 AM

I am trying to find where the user categories are stored on calibre database (metadata.db), but I don’t find any table or table_link where it might be. Am I looking on the wrong place? Thanks in advance.

kovidgoyal · 01-15-2013, 02:42 AM

IIRC, they are stored in the preferences table.

chaley · 01-15-2013, 02:58 AM

To confirm: they are stored as a JSON-encoded string in preferences/user_categories. The structure is a dict of arrays of triples. The dict keys are the category names. Each triple is a category entry [db_id, column_key, unused].

xiquinho · 01-15-2013, 03:18 AM

Thanks for your replies. Could you please give me an idea how can I read/decode this JSON field? (I am trying to build a website to read calibre database, so far I didn’t found any problem to read the other tables/fields but I don’t have idea how to read this one.) Thanks again!

chaley · 01-15-2013, 03:44 AM

Quote:

Originally Posted by xiquinho

Thanks for your replies. Could you please give me an idea how can I read/decode this JSON field? (I am trying to build a website to read calibre database, so far I didn’t found any problem to read the other tables/fields but I don’t have idea how to read this one.) Thanks again!

Select the val column from the row in the preferences table where the key column == "user_categories". Use the JSON decoder available in whatever language you are using to build your website to construct the JSON object from the value you retrieved.

xiquinho · 01-31-2013, 06:57 AM

Hi again! I am building this in VB and I was able to convert the JSON field to a XML file and from there read all the data originally stored on the JSON. I know this sound strange but it is exactly what I need. The part I don’t understand now is how to “relate” the data stored in the JSON field with the other tables. For example, I have a user category in JSON like this:
"Computers": [
[
"Adobe",
"tags",
10943
]…etc
After the conversion, my XML file looks like this:
<Computers>
<Computers>Adobe</Computers>
<Computers>tags</Computers>
<Computers>10943</Computers>
</Computers>…etc
But in the table “tags” the Adobe tag have an id:
"9383","Adobe"
So, my question is: where does the value 10943 (stored in JSON) comes from? How can I make a relationship between the two values? (10943 and 9383). Is there a formula for this or something?
Thanks in advance for your help!

chaley · 01-31-2013, 07:09 AM

Not sure what you are looking at. For me, the JSON of a user category looks like

{
"foo": [
[
"a...b...c",
"tags",
0
]
]
}

Where the first item in the triple is the value, the second item is the lookup key where it came from, and the third item is always 0.

xiquinho · 01-31-2013, 08:17 AM

Well, my JSON is pretty different: the same for the 1st and 2nd items but the 3rd item always change, ex:
"Computers": [
[
"Adobe",
"tags",
10943
],
[
"AI",
"tags",
10944
],
[
"ASP.NET",
"tags",
10942
],
[
"Assembly",
"tags",
10945
],
[
"BSD",
"tags",
10941
],
[
"C",
"tags",
6715
],
[
"C#",
"tags",
10877
],
[
"Google",
"tags",
5357
],

But thanks, anyway! I understand now I can lookup for the tag id by the tag name, using the 1st and 2nd items only and ignoring the 3rd.

Hope you have a nice day!

01-15-2013, 02:18 AM	#1
xiquinho Junior Member Posts: 7 Karma: 10 Join Date: Jan 2013 Device: ipad	where are user categories stored on calibre database? I am trying to find where the user categories are stored on calibre database (metadata.db), but I don’t find any table or table_link where it might be. Am I looking on the wrong place? Thanks in advance.

01-31-2013, 06:57 AM	#6
xiquinho Junior Member Posts: 7 Karma: 10 Join Date: Jan 2013 Device: ipad	Hi again! I am building this in VB and I was able to convert the JSON field to a XML file and from there read all the data originally stored on the JSON. I know this sound strange but it is exactly what I need. The part I don’t understand now is how to “relate” the data stored in the JSON field with the other tables. For example, I have a user category in JSON like this: "Computers": [ [ "Adobe", "tags", 10943 ]…etc After the conversion, my XML file looks like this: <Computers> <Computers>Adobe</Computers> <Computers>tags</Computers> <Computers>10943</Computers> </Computers>…etc But in the table “tags” the Adobe tag have an id: "9383","Adobe" So, my question is: where does the value 10943 (stored in JSON) comes from? How can I make a relationship between the two values? (10943 and 9383). Is there a formula for this or something? Thanks in advance for your help! Last edited by xiquinho; 01-31-2013 at 07:02 AM.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
[Android App] Calibre Companion & User Categories	K8HW	Devices	2	09-13-2012 11:37 AM
Ebooks stored in database	svetimas	Development	2	01-04-2012 05:55 AM
User Categories	jesscat	Library Management	13	03-07-2011 04:52 PM
0.7.47 - User Categories & Sub-Categories	nynaevelan	Library Management	0	02-28-2011 06:11 PM
different database for different categories	reup	Calibre	3	06-20-2010 11:25 AM

01-15-2013, 02:42 AM	#2
kovidgoyal creator of calibre Posts: 45,604 Karma: 28548974 Join Date: Oct 2006 Location: Mumbai, India Device: Various	IIRC, they are stored in the preferences table.

01-15-2013, 02:58 AM	#3
chaley Grand Sorcerer Posts: 12,525 Karma: 8065948 Join Date: Jan 2010 Location: Notts, England Device: Kobo Libra 2	To confirm: they are stored as a JSON-encoded string in preferences/user_categories. The structure is a dict of arrays of triples. The dict keys are the category names. Each triple is a category entry [db_id, column_key, unused].

01-15-2013, 03:18 AM	#4
xiquinho Junior Member Posts: 7 Karma: 10 Join Date: Jan 2013 Device: ipad	Thanks for your replies. Could you please give me an idea how can I read/decode this JSON field? (I am trying to build a website to read calibre database, so far I didn’t found any problem to read the other tables/fields but I don’t have idea how to read this one.) Thanks again!

01-31-2013, 07:09 AM	#7
chaley Grand Sorcerer Posts: 12,525 Karma: 8065948 Join Date: Jan 2010 Location: Notts, England Device: Kobo Libra 2	Not sure what you are looking at. For me, the JSON of a user category looks like { "foo": [ [ "a...b...c", "tags", 0 ] ] } Where the first item in the triple is the value, the second item is the lookup key where it came from, and the third item is always 0.

01-31-2013, 08:17 AM	#8
xiquinho Junior Member Posts: 7 Karma: 10 Join Date: Jan 2013 Device: ipad	Well, my JSON is pretty different: the same for the 1st and 2nd items but the 3rd item always change, ex: "Computers": [ [ "Adobe", "tags", 10943 ], [ "AI", "tags", 10944 ], [ "ASP.NET", "tags", 10942 ], [ "Assembly", "tags", 10945 ], [ "BSD", "tags", 10941 ], [ "C", "tags", 6715 ], [ "C#", "tags", 10877 ], [ "Google", "tags", 5357 ], But thanks, anyway! I understand now I can lookup for the tag id by the tag name, using the 1st and 2nd items only and ignoring the 3rd. Hope you have a nice day!