ecto_sqlite3

ecto_sqlite3 copied to clipboard

I'm looking at a project (live_beats modified to use SQLite3) with a query like this:

    Ecto.Multi.new()
    |> Ecto.Multi.delete_all(
      :delete_expired_songs,
      from(s in Song,
        where: s.inserted_at < from_now(^(-count), ^to_string(interval)),
        where: s.server_ip == ^server_ip,
        where:
          s.user_id in subquery(
            from(u in Accounts.User, where: u.username not in ^admin_usernames, select: u.id)
          ),
        select: %{user_id: s.user_id, mp3_filepath: s.mp3_filepath}
      )
    )
    |> Ecto.Multi.merge(&update_users_songs_count(&1))
    |> Repo.transaction()

The query was not returning the expected results because the format used to store a datetime in inserted_at is different than the one produced by from_now. The former uses either iso8601 (%Y-%m-%d %H:%M:%f, the default) or text_datetime (%Y-%m-%d %H:%M:%S), while the latter produces %Y-%m-%d %H:%M:%f000Z.

It took me some time to understand where those are coming from and I traced it back to:

https://github.com/elixir-sqlite/ecto_sqlite3/blob/eca01c10b0761b0c89b5f0db7655446d5f644d97/lib/ecto/adapters/sqlite3/connection.ex#L1324-L1334

https://github.com/elixir-sqlite/ecto_sqlite3/blob/eca01c10b0761b0c89b5f0db7655446d5f644d97/lib/ecto/adapters/sqlite3/codec.ex#L110-L136

So I wonder if changing the implementation of expr({:datetime_add, ... to match the configured format would be an acceptable change?