Add SQLlite support for statistic-data #118
Description
Description
To extend the possibilities of statistics, SQL Support would help support more tools out-of-the-box. Many statistic-tools support some sql-backend but none support el-files. speed-type-statistic.el doesn't follow an official standard, it's rather a fancy practice in the elisp ecosystem.
speed-type-statistic-filename should support pointing to an SQLlite-Database-File and if so the code will use the sql-commands. This would ez the process to write meaningful queries (meaningless example using the el-file #112 (comment)).
Currently only add an entry and load would need be added.
A single table would be sufficient and the table-fields are already defined by speed-type-statistic-variables. Maybe a uuid as primary key would be a nice addition.
Depending how the experience "working with sql in emacs" in the future we would focus on the sql-database and the el-file would still be supported but discouraged. There would be no bridges in between. Maybe an import to the database from an el-file could be ones written, but not further maintained.
Migrations would work by adding a db-folder which holds sql-migration-scripts and via speed-type-file-format-version we would apply these migrations respectively. This would allow adding new fields in a similar manner we already did before with the el-file. The initial structure would therefore also be created via this db-folder (kind of replicating "flyway"-behaviour).
Comparison
speed-type-statistic.el:
Good:
- does the job (fits it's purpose and scale for single users quite well)
- simple operation (uses
ppwhich can be out-of-the-box evaluated by elisp-interpreter) - simple data structure, selecting fields is easy
- infrastructure-code for migrations is provided (version)
- easy correction of data-entries by editing the file directly
Bad:
- one entry cost 673 bytes (redundancy)
- requires text parsing to select single entries
- no sql support (out of the box)
speed-type-statistic.db:
Good:
- optimised for data-storage
- sql support
- more scalable
- more plugable to other tools
Bad:
- requires relational database knowledge for optimal operation
- Emacs must be compiled with SQLlite-support
- Table entries can't be easily manipulated via emacs (sql must be written and supplied to the SQLlite-command)
Various
- https://www.gnu.org/software/emacs/manual/html_node/elisp/Database.html (Offical Emacs Documentation on SQLlite)
- https://github.com/ndwarshuis/org-sql (handles different sql-dialects)