elixir-sqlite
(Exqlite.Error) no such table: generate_series
I am trying to make a mask similar to a binary mask with the database values. I tried to use a selection on generate_series with a left join to output the existing values or nil.
Unfortunately, exqlite tries to find the table generate_series.
from(p in fragment("generate_series(1,256,1)"),
left_join: e in Element,
on: e.position == p.value,
select: [e]
)
|> Repo.all()
** (Exqlite.Error) no such table: generate_series
SELECT e1."id", e1."position" FROM generate_series(1,256,1) AS f0 LEFT OUTER JOIN "elements" AS v1 ON (v1."position" = f0."value");
The outputted query in error log works perfectly fine
sqlite> SELECT e1."id", e1."position" FROM generate_series(1,256,1) AS f0 LEFT OUTER JOIN "elements" AS v1 ON (v1."position" = f0."value");
||
10|2|
||
...
||
Exqlite makes no attempt to find the table. I'll look into this a little later today. Ecto builds that query and then exqlite execs the built SQL string.
I suspect I might have messed up somewhere in ecto_sqlite3 handling fragments in the from
@jzaehrin do you know if something like that works with postgres and ecto?
i don’t test it on postgres. I can test this tomorrow, sorry for my late reply
@jzaehrin it seems like you are wanting to use an extension https://www.sqlite.org/series.html
The generate_series(START,STOP,STEP) table-valued function is a loadable extension included in the SQLite source tree, and compiled into the command-line shell.
Which is why it works on the CLI for you.
I don't compile the https://www.sqlite.org/src/file/ext/misc/series.c misc code as a loadable extension. I have toyed with the idea of packaging these misc extensions in with this distribution.
As an alternative, would it be possible to have a specific compilation option for this?
I need to think through the extension compilation and how it will function here. Not everyone will want to load the extensions, and thus I don't want to make them be forced to compile it all or download / waste space unnecessarily by downloading ALL of the precompiled binaries.
I'll have an answer for this soon. One option I am mulling around is making extension packages that you can add as deps and load them similar to https://github.com/elixir-sqlite/exqlite?tab=readme-ov-file#using-sqlite3-native-extensions
Something like:
config :myapp, Myapp.Repo,
database: "path/to/db",
load_extensions: [
Exqlite.Extensions.Series.load_path(),
# custom private compile extensions
"./priv/sqlite/\#{arch_dir}/vector0",
"./priv/sqlite/\#{arch_dir}/vss0"
]
Totally agree, it would be great to be able to partially choose this kind of extension!