depo

defmodule Depo do
  @moduledoc """
  Depo provides lightweight storage and querying capabilities
  in Elixir by providing a minimal and polished API
  that builds on the unique advantages of SQLite.

  There are many ways to use Depo, but the recommended
  way is to use `teach/2` and `transact/2`so that
  you can reap the performance benefit of [SQLite transactions
  ](https://sqlite.org/transactional.html) and 
  pre-compiled SQL statements. 
  It also has the benefit of forming a sort of
  DSL for your database. 

  Here are the basic steps to use Depo in the recommended way:

  1. Open or create a database using `open/1` to get a 
  new `Depo.DB` object that manages the database connection.
  2. Enclose all database actions in a function you pass to
  `transact/2` to group them in a transaction. 
  3. Use `write/2` to create any desired tables (data types are 
  optional because SQLite uses [dynamic data typing.](https://sqlite.org/datatype3.html))
  4. Use `teach/2` to create a set of pre-compiled SQL 
  statements you can use to write to or read from the database.
  You can include numbered arguments in the statements by using
  `?1` to refer to the first argument, and
  `?2` for the second argument, and so on.
  5. Use `write/3` to populate your database with data using
  the statements you taught the database and arguments to the
  statements supplied as the third argument. 
  6. Use `read/2` and `read/3` to run (optionally parameterized) 
  queries and get the results synchronously.
  7. Use `stream/3` and `stream/4` to run the same queries and
  stream the results asynchrously to any process. 
  
  Anywhere you give a command, you can give either a valid 
  string of SQL or an atom registered to a cached statement.
  You can also include numbered variables like `?1` in your
  SQL statements and pass a list of values as the third
  parameter to `write/3`, `read/3`, and `stream/3`. 

  ## Usage Example

  ```
  # Set up the database.
  {:ok, db} = Depo.open(:memory)
  # {:ok, #PID<0.155.0>}

  # Enclose operations within a transaction.
  Depo.transact(db, fn ->
    # Write SQL statements to the database.
    Depo.write(db, "CREATE TABLE greetings (phrase)")

    # Teach the database statements to cache them.
    Depo.teach(db, %{
      new_greeting: "INSERT INTO greetings VALUES (?1)",
      # By default, queries will return a map for each row.
      greetings: "SELECT * FROM greetings",
      # You can return a list of row value tuples.
      phrases: {:values, "SELECT phrase FROM greetings"},
      # You can return a single value (from one column in one row).
      first_phrase: {:single, "SELECT phrase FROM greetings LIMIT 1"},
    })
    Enum.each(["hola", "bonjour", "今日は"], fn phrase ->
      Depo.write(db, :new_greeting, phrase)
    end)
  end)

  Depo.read(db, :greetings)
  # [
  #   %{phrase: "今日は"},
  #   %{phrase: "bonjour"},
  #   %{phrase: "hola"}, 
  # ]

  # Any query can be streamed to a PID. 
  stream_id = Depo.stream(db, self(), :phrases)
  :timer.sleep(5)

  phrases = Enum.reduce(1..3, [], fn _i, phrases ->
    receive do
      {^stream_id, phrase} -> phrases ++ [phrase]
    end
  end)
  # phrases == [ 
  #   {"hola"}, 
  #   {"bonjour"},
  #   {"今日は"},
  # ]

  Depo.read(db, :first_phrase)
  # "hola"
  """

  defmacrop is_cmd(cmd) do
    quote do
      is_binary(unquote(cmd)) or is_atom(unquote(cmd))
    end
  end

  @doc """
  Open a connection to a database and return a new 
  `Depo.DB` object to manage the database connection.

  There are a few ways you can open a database:
  - pass a `path` to open an existing on-disk database
  - pass `create: path` to create and open a database at the path
  - pass `:memory` to create a new in-memory database
  """
  def open(:memory) do
    GenServer.start_link(Depo.DB, :memory)
  end
  def open(create: path) when is_binary(path) do
    GenServer.start_link(Depo.DB, create: path)
  end
  def open(path) when is_binary(path) do
    GenServer.start_link(Depo.DB, path)
  end

  @doc """
  Asynchronously write SQL statements to the database.

  Optionally supply a list of values as the third 
  argument to bind to variables in the statement.
  """
  def write(db, cmd, values) when is_cmd(cmd) do
    GenServer.cast(db, {:write, cmd, values})
  end
  def write(db, cmd) when is_cmd(cmd) do
    GenServer.cast(db, {:write, cmd})
  end

  @doc """
  Synchronously read an SQL query from the database
  and return a list of the results.

  Optionally supply a list of values as the third 
  argument to bind to variables in the query.
  """
  def read(db, query, values) when is_cmd(query) do
    GenServer.call(db, {:read, query, values})
  end
  def read(db, query) when is_cmd(query) do
    GenServer.call(db, {:read, query})
  end

  @doc """
  Asynchronously stream the results of an SQL query from 
  the database to the given PID.

  The given process will receive each result as a tuple
  `{stream_id, value}` where `stream_id` is the PID of the 
  stream process that uniquely identifies the stream,
  and `value` is a single result map.

  Optionally supply a list of values as the fourth 
  argument to bind to variables in the query.
  """
  def stream(db, pid, query, values) when is_cmd(query) do
    GenServer.call(db, {:stream, pid, query, values})
  end
  def stream(db, pid, query) when is_cmd(query) do
    GenServer.call(db, {:stream, pid, query})
  end

  @doc """
  Pre-compile, cache, and register named SQL statements for
  more efficient repeated use. 

  `statements` should be a keyword list, where the keys
  are atoms and the values are SQL statements.
  """
  def teach(db, stmts) do
    GenServer.cast(db, {:teach, stmts})
  end

  @doc """
  Wrap any operations within the given anonymous function
  in a nestable transaction. 
  If any error occurs within, the transaction will be
  automatically rolled back. 

  You can [read about SQLite's transactions in depth in its 
  documentation.](https://sqlite.org/lang_transaction.html)
  """
  def transact(db, func) do
    Depo.write(db, "SAVEPOINT _depo_transaction;")
    func.()
    Depo.write(db, "RELEASE _depo_transaction;")
  end

  @doc """
  Safely close the database connection.
  """
  def close(db) do
    GenServer.stop(db)
  end
end