-
Notifications
You must be signed in to change notification settings - Fork 103
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support SQLite3 Repos #615
Comments
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? |
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.
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.
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. |
Hey @aquap sorry for the late response. The first one 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 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 |
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.
The text was updated successfully, but these errors were encountered: