(Exqlite.Error) no such table: generate_series

[jzaehrin]

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|
||
...
||

[warmwaffles]

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

[warmwaffles]

@jzaehrin do you know if something like that works with postgres and ecto?

[jzaehrin]

i don’t test it on postgres. I can test this tomorrow, sorry for my late reply

[warmwaffles]

Not a huge deal. I'm still going to look into this.

[warmwaffles]

@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.

[jzaehrin]

As an alternative, would it be possible to have a specific compilation option for this?

[warmwaffles]

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"
  ]

[jzaehrin]

Totally agree, it would be great to be able to partially choose this kind of extension!