SQLite-Support

[robwa]

The following problems occur when using an SQLite database. They have probably not been noticed so far, as most people tested with MariaDB. So far, Foodsoft has also run with an SQLite database, do we want to give that up?

• The migration 20240726083743_create_article_units.rb inserts unit codes directly into the database. We should consider being able to insert the data as seed data at any time and/or at the push of a button on the units page. This was noticed because the migration doesn't work with a SQLite database (use of NOW()).
• In the migration 20240726083741_alter_articles_add_more_unit_logic.rb the default value for order_articles.quantity is removed. As a result, the creation of a new order for a supplier fails when using an SQLite database:

ActiveRecord::NotNullViolation - SQLite3::ConstraintException: NOT NULL constraint failed: order_articles.quantity:
  app/models/order.rb:369:in `block in save_order_articles'
  app/models/order.rb:369:in `save_order_articles'
  app/controllers/orders_controller.rb:86:in `create'

[lentschi]

We had no intention of ending SQLite support, so this is simply a bug I hadn't known of and we need to fix it.

-> I created the issue #1124 from this.

However, I'll leave the discussion open for this point:

The migration 20240726083743_create_article_units.rb inserts unit codes directly into the database. We should consider being able to insert the data as seed data at any time and/or at the push of a button on the units page.

Having this in a migration was actually a deliberate decision - see https://github.com/foodcoops/foodsoft/blob/feature/1058-extended-article-units/doc/article_units_fork_architecture_changes.md#unit-selection:

This table is initially populated with an arbitrary set of units - see the respective rails migration. If the original db seems to contain plain text imperial units, those are added too.

I think having something like this in an automated rails migration is without alternative. If we were to remove it, users initially wouldn't have any units to choose from when creating new articles.

But I think, what you might be suggesting, is to have something like a "Reset to default units" button in the articles list view - is that it? We could surely discuss having this as an extra feature.

[lentschi]

If someone were to initialise the database with db:schema:load, it would not be possible to access the unit data.

True, that is a point, I hadn't thought about to be honest. 🤔 However: Just running db:schema:load will result in a completely empty DB - it doesn't have any users in there either.

So I assume admins setting up a new foodsoft instance will usually run rake db:schema:load db:seed:minimal, which doesn't include any default units.

rake db:seed:small.en would already include them (see the code here), but otherwise contains sample data which seems to be intended for local development only.

So if everyone agrees (?), I'd add an issue to add the default metric units to the minimal.seeds.rb too. Also we could have a minimal.imperial_units.seeds.rb in addition.

[twothreenine]

@mortbauer Which seed do you use when setting up a new Foodsoft instance on the IG server?
(We should add the units to that seed, right?)

[lentschi]

@mortbauer In case you just missed twothreenine's question bump 🙂

[mortbauer]

seems I also missed the bump. I think I usually create an empty fc db.

[lentschi]

I think I usually create an empty fc db.

And then you manually create an admin workgroup & user...? (Plus any initial supplier/article categories, financial transaction types, ...?)

[lentschi]

How are you using SQLite in the first place, @robwa ?
I'm getting this error if I try it:
can't activate sqlite3 (~> 1.4), already activated sqlite3-1.3.13

This can be fixed by setting gem 'sqlite3', '~> 1.4' in Gemfile and running bundle update sqlite3.
Did you have to do this too? If so, I guess we should create an issue for it. (But this is unrelated to extended units PR)

[robwa]

Yes, I did the same locally. Opened #1138 on this.