Use a struct as response from executed query to get the rows and columns

[aleDsz]

Hi!

Firstly, I want to say that I loved the project and it's great to have support for SQLite in Elixir.

We have been building a new feature to support SQLite through livebook app and kino_db library, which allows users to input their database path and loads data directly from a Livebook (same as Jupyter Notebook).

And one of our features is to support the "table" format to visualize data, but Exqlite doesn't return a response with columns and rows to make it happen, so I've been thinking about make some contributions to the project.

Context

In this PR we added the support to handle Exqlite connection and how we should execute the user's query input. And our goal is make it easy to implement the Table.Reader protocol from table. Since we're not going to add the protocol here, we only need row and columns information.

Then. I've been thinking if it should be a option in the Exqlite.Connection.connect/1 function or maybe create a new function to fetch all rows and returns the Exqlite.Query struct or maybe a new one to represent the result from SQLite.

What you all think? Would be great to talk about, and if y'all have any recommendations or thoughts, please feel free to send.

[warmwaffles]

@aleDsz has this been implemented for other database connections? Ones off the top of my head, postgrex, myxql, etc...

I ask because it would be wise to follow a similar pattern if it is.

I am definitely open to it either way.

[aleDsz]

@warmwaffles Yes, we have Postgrex and MyXQL already implemented and with the Table.Reader protocol implemented by kino_db.

[warmwaffles]

Can you point me to those implementations? Perhaps I could do this change quickly.

[aleDsz]

Can you point me to those implementations? Perhaps I could do this change quickly.

Sure.

We have been using the function query!/4 present in both (postgrex and mysql) and using the same struct format to return the result for both (postgrex and myxql)

query!/4

  def query!(conn, statement, params, opts \\ []) do
    case query(conn, statement, params, opts) do
      {:ok, result} -> result
      {:error, err} -> raise err
    end
  end

Result struct

  defstruct [
    :columns,
    :connection_id,
    :last_insert_id,
    :num_rows,
    :rows,
    :num_warnings
  ]

Probably not all information will be available, but would be nice to have some of then.

Both have the same function and the same struct format to be handle by kino_db

[aleDsz]

One single function to receive the conn, the query, parameters to be binded and extra options (if possible, off course)

[warmwaffles]

That looks simple enough.

[warmwaffles]

I think this could be as simple as

  def query(conn, statement, params, opts \\ []) do
    query = %Query{name: "", statement: IO.iodata_to_binary(statement)}

    case DBConnection.prepare_execute(conn, query, params, opts) do
      {:ok, _, result} ->
        {:ok, result}

      {:error, _} = error ->
        error
    end
  end

  def query!(conn, statement, params, opts \\ []) do
    case query(conn, statement, params, opts) do
      {:ok, result} -> result
      {:error, err} -> raise err
    end
  end

[warmwaffles]

I haven't been using the top level Exqlite module and I think replicating a similar interface that postgrex has is a good idea. I'll make a PR for that change with some tests for it as well.

[warmwaffles]

@aleDsz #203 mind checking this branch out and giving your thoughts?

[aleDsz]

@aleDsz #203 mind checking this branch out and giving your thoughts?

I'm checking it right now

[aleDsz]

To me, it's all good to connect and make queries.

I've talking with Jose about maybe implementing the table dependency as optional here and implement the table reader protocol to convert columns and rows, like we did in postgrex.

WDYT?

[warmwaffles]

That sounds good to me. I can roll that into my PR above as well.

[warmwaffles]

@aleDsz done. This is going to go out with release 0.11.0 here shortly

aa6fb8a