JSON vs SQLITE

[varia]

Moving into the next phase of Reading Tracker plugin, I am exploring backup/import/export utility. For what this plugin does, what would make the most sense, JSON files or SQLITE? Currently some json files are in use, but moving into the next phase I want to ensure the correct choices are made.

As of now the plugin does the following:
Bulk metadata retrieval
Sorting and filtering
Dynamic queries
Yearly aggregation of reading and book statistics
Streak calculation with date parsing and timezone awreness
Tracking progress vs goals; challenge vs goals
Journal logging
Reading challenge management

If a user wants to load in a library of tens of thousands of books would json files be enough for export/import? Sqlite?
Any insight would be appreciated.

Couple of screenshots included for the cat tax.

[Comfy.n]

I've wondered the same thing so many times.

[theducks]

These are really a 'summary' of a small defined subset of books gathered from a full library scan? The report would only update if the process triggers?

Those results should be fairly small, so the JSON should be also.
BTW That first thumbnail looks great

[varia]

Originally Posted by Comfy.n

I've wondered the same thing so many times.

It's s struggle for sure. Part of me thinks it's overkill to layer on an SQLITE database for a simple (?) tracker but trying to rope in scattered json files is no picnic. On top of that I'm in expansion mode and I have no idea what features would work best with what.

[varia]

Originally Posted by theducks

These are really a 'summary' of a small defined subset of books gathered from a full library scan? The report would only update if the process triggers?

Those results should be fairly small, so the JSON should be also.
BTW That first thumbnail looks great

Yes, this shows a predefined list from a small test library. Upon first install the library is cached to an index.
Presumably most users would only want to track a relatively small list of books, but I am only guessing.
The plugin also creates files for challenges, goals, journal entries, and probably more to come with an upcoming new feature.
I suppose if users can back up all these as a single file, and import a single file (for instance after a reinstall), then it could work.
Thanks, still lots to do to get it to where I am 100% satisfied.

[BetterRed]

As I read this from Comfy.n, and the attached popop from his RSS Reader plugin, a SQLite database should not be located in the calibre configuration directory.

Yet, calibre stores its server-users.sqlite database in its configuration directory. Other apps also have sqlite databases stored in their equivalent to calibre's configuration directory - e.g. my %appdata%\Mozilla\Firefox directory has hundreds of them.

One advantage to .json files is that they can be accessed, and edited with a text editor, whereas a sqlite database requires a tool such as DB Browser or SQL Studio to do the same.

In my ideal world each plugin would have a subdirectory in which the plugin zip and all of its data, settings, help files etc were located.

BR

[varia]

Originally Posted by BetterRed

As I read this from Comfy.n, and the attached popop from his RSS Reader plugin, a SQLite database should not be located in the calibre configuration directory.

Yet, calibre stores its server-users.sqlite database in its configuration directory. Other apps also have sqlite databases stored in their equivalent to calibre's configuration directory - e.g. my %appdata%\Mozilla\Firefox directory has hundreds of them.
BR

I'm no expert but that seems to make sense, storing a plugin database out of the calibre confic directory, just to be on the safe side.

One advantage to .json files is that they can be accessed, and edited with a text editor, whereas a sqlite database requires a tool such as DB Browser or SQL Studio to do the same.

This did cross my mind so it's good to see that validation.

In my ideal world each plugin would have a subdirectory in which the plugin zip and all of its data, settings, help files etc were located.

Do you mean packaged in such a way so as to install the plugin with all user data/settings etc already in place? Like a personal single-file backup being installed to a new computer?
Or just for the zip and all data/settings to be located in a single folder?

[BetterRed]

Originally Posted by varia

I'm no expert but that seems to make sense, storing a plugin database out of the calibre confic directory, just to be on the safe side.

This did cross my mind so it's good to see that validation.

Safe from what? Maybe Comfy.n can tell us.

Why should a sqlite file used by a calibre plugin be regarded as being any more precious than the sqlite files used by the Azardi epub viewer, the FDM download manager, the Firefox web browser, or the calibre server etc.

And, why would it be safer to store the sqlite file in let's say c:/users/<userid>/someplugin-data directory rather than c:/users/<userid>/appdata/roaming/calibre/plugins/someplugin-data/ directory. If the Backup Configuration Folder plugin is installed it'll get backed up when calibre is closed.

Originally Posted by varia

Do you mean packaged in such a way so as to install the plugin with all user data/settings etc already in place? Like a personal single-file backup being installed to a new computer?
Or just for the zip and all data/settings to be located in a single folder?

The latter.

One roadblock to my ideal, is that some plugins (e.g. View Manager, Count Pages, Quality Check etc) have library specific settings that are stored in the preferences table of the library metadata.db files.

Another is that some plugin settings are stored in calibre wide json files, e.g. window sizes and positions in gui.json, I have entries in there for plugins that I removed 10+ years ago

BR

[theducks]

Something to consider
Does this DB need to be PER LIBRARY?
Some PI have generic settings in the Config folder and others become part of the DB because of the nature of custom columns makes the data only relevant to a specific library.

[kovidgoyal]

You use sqlite if you need to modify individual rows in the larger dataset with good performance and make queries against the larger dataset using indices for speed. If nether of those are a concern either because of the design or because the dataset is not large then use JSON.

[Comfy.n]

Originally Posted by BetterRed

Safe from what? Maybe Comfy.n can tell us.
BR

"Safe" refers more to calibre cache dir, which is mentioned in your screenshot: outside calibre's config and cache folders. More often than not, programs can reset/delete/overwrite their cache folders. Also, placing a feeds database inside config dir would not be reliable for use with the Backup Config plugin, because this would significantly affect shutdown time.

BTW, I will soon release a plugin that will help users create all columns Reading Tracker requires with a single click.

[varia]

Originally Posted by BetterRed

Safe from what?

Big shrug on that one. Have no idea if a database situated in x or y folder can risk corruption from who knows what. I'm no programmer so I don't know what's the best practice. Personally I save no files to my C drive, only installed programs. Everything goes to partitions.

Ahh, yes, my plugins/ folder is a fright. It is mayhem in there. With this plugin I am working on, I am trying to keep things contained in one folder. A set standard would help to keep things organized.

Originally Posted by theducks

Something to consider
Does this DB need to be PER LIBRARY?
Some PI have generic settings in the Config folder and others become part of the DB because of the nature of custom columns makes the data only relevant to a specific library.

I am moving away from custom columns. I don't have an issue with them per se, but from observation it seems to be the most hated feature in Calibre plugins. My preference is for data, config, and settings files to all remain in the tracker folder, and not in multiple places. If worst comes to worst, a user would then be able to copy the folder with all plugin files in one go. I haven't explored multi-library setups yet to know how plugins work in those cases.

[varia]

Originally Posted by kovidgoyal

You use sqlite if you need to modify individual rows in the larger dataset with good performance and make queries against the larger dataset using indices for speed. If nether of those are a concern either because of the design or because the dataset is not large then use JSON.

Thank you, Kovid. That is helpful to know. That would mean JSON is the way to go for this plugin.

[varia]

Originally Posted by Comfy.n

BTW, I will soon release a plugin that will help users create all columns Reading Tracker requires with a single click.

This is possible?! Well blow me down!

[Comfy.n]

Originally Posted by varia

This is possible?! Well blow me down!

I will see if I can release it today. Custom columns can be a pain to set up, but once they're in place it's the best approach IMO. They facilitate advanced searches.

@kovidgoyal thank you for the general guidelines!