Consider using Sqlite instead of Mysql

[jzohrab]

Currently Lute using MySQL, which is potentially a bit heavyweight. Other tools like VocabSieve and Anki use Sqlite and it works fine for them.

If Lute used Sqlite, users wouldn't have to install and configure MySQL, which is pretty heavyweight. Install would be simplified to the following:

• install PHP
• get the Lute code

They should be able to use the built-in PHP web server from the public folder with $ php -S localhost:8000. The project could be initially config'd to run the db file from an internal folder and file (saved within the project directory). Then users could modify the .env.local file, with clear instructions on a wiki page. It should suffice, performance-wise.

(Of course, if they want, they can install the Symfony CLI (https://symfony.com/download) for a lightweight web server, and run it from the app folder with $ symfony server:start, or set up MAMP or XAMPP or whatever, but those would be overkill).

Sample branch

Branch sqlite_work_in_progress pushed to this repo has a few things done already. The unit tests all pass. The code appears to work, but I haven't tested it with any real volume of data, so I don't yet know if it's performant.

Remaining todos

• determine how to port data from existing MySQL installations to the Sqlite file. - data will be ported by CSV export and import.
• DB backup would just be a copy of the sqlite file to some folder
• how to handle new migrations? Still use the _migrations table idea, or simplify it further?
• have to baseline the latest schema to a new .sqlite file and commit that to the repo, using that as the baseline db. And stash/delete all existing migrations so they don't get re-applied
• the code has some "TODO:sqlite" comments. use dev:find to find them

creating sqlite baseline

• apply the field/key changes listed below to the sql db (with a migration, probably)
• fix the trigger
• run process to export the mysql db to the baseline .sqlite db (committed in the repo)

Can keep creating baselines as needed

porting data - test cases

• If importing, the Sqlite DB must be empty. The export from the Mysql db is just raw data, including primary keys. If the tables contain other data, all keys will get messed up.
• importing: bad fields should throw an error

misc notes accumulated during hacking:

exporting the schema

**** reset
rm var/data/test.sqlite

# using https://github.com/techouse/mysql-to-sqlite3
mysql2sqlite -f var/data/test.sqlite -d test_lute -u root --mysql-password root -W

fixing col types

**** fix col types so that conversion to sqlite sets up primary key as autoincrement

alter table languages modify column LgID INTEGER NOT NULL AUTO_INCREMENT;
alter table books modify column BkID INTEGER NOT NULL AUTO_INCREMENT;
alter table booktags modify column BtBkID INTEGER NOT NULL;
alter table booktags modify column BtT2ID INTEGER NOT NULL;
alter table sentences modify column SeID INTEGER NOT NULL AUTO_INCREMENT;
alter table statuses modify column StID INTEGER NOT NULL;
alter table tags modify column TgID INTEGER NOT NULL AUTO_INCREMENT;
alter table tags2 modify column T2ID INTEGER NOT NULL AUTO_INCREMENT;
alter table texts modify column TxID INTEGER NOT NULL AUTO_INCREMENT;
alter table texttags modify column TtTxID INTEGER NOT NULL;
alter table texttags modify column TtT2ID INTEGER NOT NULL;
alter table texttokens modify column TokTxID INTEGER NOT NULL;
alter table wordimages modify column WiID INTEGER NOT NULL AUTO_INCREMENT;
alter table wordparents modify column WpWoID INTEGER NOT NULL;
alter table wordparents modify column WpParentWoID INTEGER NOT NULL;
alter table words modify column WoID INTEGER NOT NULL AUTO_INCREMENT;
alter table words modify column WoLgID INTEGER NOT NULL;
alter table wordtags modify column WtWoID INTEGER NOT NULL;
alter table wordtags modify column WtTgID INTEGER NOT NULL;

**** fix trigger -- committed in rep. migrations

misc other notes

**** weird 'like' vs '=' issue in sqlite
ref Ref https://stackoverflow.com/questions/26719948/sqlite-why-select-like-works-and-equals-does-not
in SpaceDelimitedParser_IntTest.php

**** TODO db filename in settings
**** fix migration thing
***** commit a baseline _empty_ db to the reop
***** migration helper thing should copy the baseline

[jzohrab]

Data transfer will happen with CSV export and import. The develop branch already has a simple CSV export feature that runs when the page http://lute_dev.local:8080/utils/export_csv is hit. The link for that page exists in index.html.twig, but is disabled.

A first draft to import those same CSV files is in the sqlite_work_in_progress branch.

[jzohrab]

Done for v2.