Enable strict tables

[fire]

https://www.sqlite.org/draft/stricttables.html

Is there anything I need to do to force strict to be always on.

[warmwaffles]

We can, but I don't understand the benefit of that since the application / adapter layer is taking care of the coercion.

[warmwaffles]

I'll play with the feature some and see what comes of it. The database once it has a STRICT table defined can not be opened by any version of sqlite3 older than 3.37, that is something to keep in mind especially if your local version sqlite3 lags behind due to your upstream not cutting a release sooner.

If you want to give it a shot before I take a swing some points of interest are

• https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3/data_type.ex
• https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3/codec.ex
• https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3.ex#L228-L399

Ideally I would want to make strict tables opt-in since it is not on by default in sqlite. So we'd need to intercept the options before they are passed to exqlite

• https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3/connection.ex#L29
• https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3/connection.ex#L36

This is one solution. Another would be to just pass strict: true with the options when creating a database table.

I'm leaving this here for me to come back to later when I take a deeper look into it.

[fire]

As someone that does not know the internals of ecto_sqlite3 passing strict: true with the options when creating a database table seems like a localized solution.

[warmwaffles]

the options when creating a database table seems like a localized solution.

I'll have to check out a strict table definition to see if this adapter blows up or if it just silently works. If it is the latter, this will make it significantly easier.

One way to test it out is to just hand create the table with an execute in your migration and CREATE TABLE () STRICT and plop around with it.

[fire]

I'm not sure how to do any of this.

Will try to do what you said, but I might flail around :D

[warmwaffles]

@fire when you create an ecto migration for your application instead of using create table() use https://hexdocs.pm/ecto_sql/Ecto.Migration.html#execute/1 and craft the table by hand. Then just pop an IEX console and insert a few records and see what happens.

[fire]

Trying to port https://github.com/aesmail/kaffy-demo to sqlite.

1. Needed to bump mix.exs to {:exqlite, "~> 0.8"},
2. change dev.exs to database: "bakery_dev.db", etc.
3. change adapter: Ecto.Adapters.SQLite3
4. ILIKE is not supported.
5. Tried forcing STRICT in create table
6. Was blocked by TEXT_DATATIME not being supported

[warmwaffles]

ILIKE is not supported.

Sqlite does not support ILIKE you'll have to use LIKE and make both left and right side operators upper and lower case the string.

Tried forcing STRICT in create table TEXT_DATATIME not being supported

I figured something like this would happen with strict typing. Because SQLite lacks a proper datetime that stores timezone with it, we have to store it as a text field that is iso8601 compliant.

• https://sqlite.org/quirks.html#no_separate_datetime_datatype
• https://sqlite.org/lang_datefunc.html

@kevinlang have you looked into the new STRICT table stuff?

I'm tempted to say no to adding strict typing as the flexible typing is a feature not a bug https://sqlite.org/flextypegood.html

[fire]

I'll close this issue for now as I realized that the feature needs time to bake even if it was released and my related libraries like kaffy weren't ready.