Support SQLite3 Repos

[aquap]

Is your feature request related to a problem? Please describe.

Selecting sqlite3 as database adaptor fails during migration. The first error in lib/beacon/migrations/v001.ex is at line 89, due to the exsqlite3 library's lack of support for alter table - in turn probably related to sqlite3 DDL quirks, but probably not necessary to use 'alter table' for the very first migration anyway.

More concerning are the unsupported constraint checks, which may or may not be supported by sqlite3.

Describe the solution you'd like

It would be nice if sqlite3 was supported without any ceremony since it is a solid few-moving-parts database solution for a dynamic website.

Additional context

To get sqlite3 support working in my project, I copied the v001 and v002 migration steps into my own migration, moved the alter table add statements into the create table blocks immediately above them, commented out two sets of erroring constraints. As a proof of concept that sqlite3 support could be made to work by refactoring the SQL and perhaps moving constraints into ecto, this seems reasonably compelling.

[leandrocp]

Hey @aquap we'd like to support sqlite. One project that has a similar setup is https://github.com/oban-bg/oban/tree/main/lib/oban/migrations, which was the initial inspiration for our migrations.

Were you able to execute tests or use beacon sites or admin at all?

[aquap]

Nice to hear @leandrocp.

I was able to get the migrations up when I pushed the field definitions into the create table stanzas, and as I mentioned I've not investigated the constraints situation yet.

I ran into a further blocker mentioning an unsupported distinct across two columns immediately after the migrations completed.

** (Ecto.QueryError) DISTINCT with multiple columns is not supported by SQLite3 in query:

from p0 in Beacon.Content.PageEvent,
  where: p0.site == ^...,
  order_by: [asc: fragment("inserted_at desc, case when event = 'published' then 0 else 1 end")],
  distinct: [asc: p0.page_id],
  select: %{id: p0.id, site: p0.site, event: p0.event, page_id: p0.page_id, inserted_at: p0.inserted_at}

    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:846: Ecto.Adapters.SQLite3.Connection.distinct/3
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:854: Ecto.Adapters.SQLite3.Connection.select/2
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:179: Ecto.Adapters.SQLite3.Connection.all/2
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:1337: Ecto.Adapters.SQLite3.Connection.expr/3
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:1889: Ecto.Adapters.SQLite3.Connection.get_source/4
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:1021: anonymous fn/3 in Ecto.Adapters.SQLite3.Connection.joi
n/2
    (elixir 1.17.1) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3/connection.ex:180: Ecto.Adapters.SQLite3.Connection.all/2
    (ecto_sqlite3 0.17.2) lib/ecto/adapters/sqlite3.ex:187: Ecto.Adapters.SQLite3.prepare/2
    (ecto 3.12.4) lib/ecto/query/planner.ex:202: Ecto.Query.Planner.query_without_cache/4
    (ecto 3.12.4) lib/ecto/query/planner.ex:170: Ecto.Query.Planner.query_prepare/6
    (ecto 3.12.4) lib/ecto/query/planner.ex:143: Ecto.Query.Planner.query_with_cache/8
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:214: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (beacon 0.1.0) lib/beacon/content.ex:958: Beacon.Content.list_published_pages/2
    (beacon 0.1.0) lib/beacon/router_server.ex:81: Beacon.RouterServer.handle_continue/2
    (stdlib 6.0.1) gen_server.erl:2163: :gen_server.try_handle_continue/3
    (stdlib 6.0.1) gen_server.erl:2072: :gen_server.loop/7
    (stdlib 6.0.1) proc_lib.erl:329: :proc_lib.init_p_do_apply/3
Last message: {:continue, :async_init}
State: %Beacon.Config{site: :demo, endpoint: BeaconDemoWeb.Endpoint, router: BeaconDemoWeb.Router, repo: BeaconDemo.Repo, mode
: :live, css_compiler: Beacon.RuntimeCSS.TailwindCompiler, tailwind_config: "/vagrant/projects/beacon_demo/_build/dev/lib/beac
on_demo/priv/tailwind.config.bundle.js", live_socket_path: "/live", safe_code_check: false, template_formats: [heex: "HEEx (HT
ML)", markdown: "Markdown (GitHub Flavored version)"], assets: [{"image/jpeg", [processor: &Beacon.MediaLibrary.Processors.Ima
ge.process!/1, validations: [], providers: [Beacon.MediaLibrary.Provider.Repo]]}, {"image/gif", [processor: &Beacon.MediaLibra
ry.Processors.Image.process!/1, validations: [], providers: [Beacon.MediaLibrary.Provider.Repo]]}, {"image/png", [processor: &
Beacon.MediaLibrary.Processors.Image.process!/1, validations: [], providers: [Beacon.MediaLibrary.Provider.Repo]]}, {"image/we
bp", [processor: &Beacon.MediaLibrary.Processors.Image.process!/1, validations: [], providers: [Beacon.MediaLibrary.Provider.R
epo]]}, {"application/pdf", [processor: &Beacon.MediaLibrary.Processors.Default.process!/1, validations: [], providers: [Beaco
n.MediaLibrary.Provider.Repo]]}], allowed_media_accept_types: ["image/jpeg", "image/gif", "image/png", "image/webp", ".pdf"], 
lifecycle: [load_template: [heex: [], markdown: [convert_to_html: &Beacon.Template.Markdown.convert_to_html/2]], render_templa
te: [heex: [], markdown: []], after_create_page: [], after_update_page: [], after_publish_page: [], upload_asset: [thumbnail: 
&Beacon.Lifecycle.Asset.thumbnail/2]], extra_page_fields: [BeaconDemo.Beacon.PageFields.Type], extra_asset_fields: [{"image/*"
, [Beacon.MediaLibrary.AssetFields.AltText]}], default_meta_tags: []}

I believe that this might not be the actual problem as sqlite3 does support some distinct operations.

And on the off-chance that something might indeed work, the following gets thrown - I actually had to lookup SQL offset, never used it before ;-) Thus no dice on the web interface.

[error] GenServer {Beacon.Registry, {:demo, Beacon.Loader.Worker, 2242}} terminating
** (Exqlite.Error) near "OFFSET": syntax error
SELECT b0."id", b0."site", b0."name", b0."description", b0."body", b0."template", b0."example", b0."category", b0."thumbnail", b0."inserted_at", b0."updated_at" FROM "beacon_components" AS b0 WHERE (b0."site" = ?) ORDER BY b0."name" OFFSET 0
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:994: Ecto.Adapters.SQL.execute/6
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
    (ecto 3.12.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (beacon 0.1.0) lib/beacon/loader/worker.ex:405: Beacon.Loader.Worker.reload_components_module/1
    (beacon 0.1.0) lib/beacon/loader/worker.ex:348: Beacon.Loader.Worker.handle_call/3
    (stdlib 6.0.1) gen_server.erl:2209: :gen_server.try_handle_call/4
    (stdlib 6.0.1) gen_server.erl:2238: :gen_server.handle_msg/6
    (stdlib 6.0.1) proc_lib.erl:329: :proc_lib.init_p_do_apply/3
Last message (from #PID<0.347.0>): :reload_components_module
State: %{site: :demo}
Client #PID<0.347.0> is alive

    (stdlib 6.0.1) gen.erl:260: :gen.do_call/4
    (elixir 1.17.1) lib/gen_server.ex:1125: GenServer.call/3
    (beacon 0.1.0) lib/beacon/boot.ex:61: Beacon.Boot.live_init/1
    (stdlib 6.0.1) gen_server.erl:2057: :gen_server.init_it/2
    (stdlib 6.0.1) gen_server.erl:2012: :gen_server.init_it/6
    (stdlib 6.0.1) proc_lib.erl:329: :proc_lib.init_p_do_apply/3

It looks like this might need a bit of work from the Beacon side to keep to a safer or more limited subset of SQL, but ecto_sqlite3 and friends might need to get some fixes happening too, if they're too pessimistic about supported features in SQLite3.

[leandrocp]

Hey @aquap sorry for the late response. The first one ** (Ecto.QueryError) DISTINCT with multiple columns is not supported by SQLite3 in query: I'd expect some issues since that query is a bit more complex, but the other one is a bit of a surprise for me because that's a relatively simple query.

Here's the first query for reference:

SELECT
    b0."id",
    b0."site",
    b0."schema_version",
    b0."page",
    b0."page_id",
    b0."path",
    b0."title",
    b0."format",
    b0."extra",
    b0."event_id",
    b0."inserted_at"
FROM
    "beacon_page_snapshots" AS b0
    INNER JOIN (
        SELECT
            DISTINCT ON (sb0."page_id") sb0."id" AS "id",
            sb0."site" AS "site",
            sb0."event" AS "event",
            sb0."page_id" AS "page_id",
            sb0."inserted_at" AS "inserted_at"
        FROM
            "beacon_page_events" AS sb0
        WHERE
            (sb0."site" = 'dev')
        ORDER BY
            sb0."page_id",
            inserted_at DESC,
            CASE
                WHEN event = 'published' THEN 0
                ELSE 1
            END
    ) AS s1 ON b0."event_id" = s1."id"
WHERE
    (b0."site" = 'dev')
ORDER BY
    b0."title" OFFSET 0

I believe it doesn't work due to DISTINCT ON, but that statement is necessary to return the "latest published snapshot", ie: the latest page data that was published. I'm not sure we can replicate the same query on sqlite but we can create variants of list_published_pages that does the job on the Elixir side whenever an adapter is not capable of querying the data natively, for eg:

def list_published_pages(site, opts \\ []) do
  case repo(site).__adapter__() do
    Ecto.Adapters.Postgres -> # pg compatible query
    _ -> # a simple query that returns the data to Elixir and then use Enum/whatever to filter the data as the query would
  end
end

[joelpaulkoch]

I came across something similar on a project today.
I believe we can replicate the query in sqlite by using the row_number() window function to assign each row a number in the correct order and then only keep the first row by filtering for row_number = 1.

[leandrocp]

Hey @joelpaulkoch that's interesting if we can get all queries to work across all vendors would be perfect.