# SQLite

## A real library: SQLite

The raw `extern` layer stays private, and safe functions on top of it return `Result`. It uses resources and `out` parameters ([Types and resources](https://www.agentica-lang.com/agentica/library/io-and-system/native/types/index.md)). This program opens an in-memory database, fills a table and reads it back. It names the Linux library, `libsqlite3.so.0`; on macOS the system one is `libsqlite3.dylib`, and on Windows, write `"winsqlite3.dll"`, the SQLite that Windows 10 and later ship in the system folder (its version depends on the Windows build).

```agentica
SQLITE_OK = 0
SQLITE_ROW = 100
SQLITE_DONE = 101

extern "libsqlite3.so.0"
  resource Db, free: sqlite3_close_v2
  resource Stmt, free: sqlite3_finalize
  def sqlite3_open(filename: C.String, out db: Db) -> C.Int32
  def sqlite3_errstr(code: C.Int32) -> C.String
  def sqlite3_errmsg(db: Db) -> C.String
  def sqlite3_prepare_v2(db: Db, sql: C.String, bytes: C.Int32, out stmt: Stmt, out tail: C.Pointer) -> C.Int32
  compute def sqlite3_step(stmt: Stmt) -> C.Int32
  def sqlite3_column_int(stmt: Stmt, col: C.Int32) -> C.Int32
  def sqlite3_column_text(stmt: Stmt, col: C.Int32) -> C.String
end

## Opens a database. A failed open may still give a handle, which is closed here.
def open(path: String) -> Result[Db]
  opened = sqlite3_open(path)
  if opened.result != SQLITE_OK
    Native.close(opened.db)
    return error("sqlite: #{sqlite3_errstr(opened.result)}")
  end

  ok(opened.db)
end

## Prepares one statement, or gives SQLite's message.
def prepare(db: Db, sql: String) -> Result[Stmt]
  prepared = sqlite3_prepare_v2(db, sql, -1)
  return error("sqlite: #{sqlite3_errmsg(db)}") if prepared.result != SQLITE_OK

  ok(prepared.stmt)
end

## Runs a statement that returns no rows.
def run(db: Db, sql: String) -> Result[Void]
  stmt = try prepare(db, sql)
  if sqlite3_step(stmt) != SQLITE_DONE
    message = sqlite3_errmsg(db)
    Native.close(stmt)
    return error("sqlite: #{message}")
  end

  Native.close(stmt)
  ok()
end

def main() -> Void
  db = open(":memory:").unwrap!
  run(db, "create table langs (id integer, name text)").unwrap!
  run(db, "insert into langs values (1, 'Ruby'), (2, 'Agentica')").unwrap!
  query = prepare(db, "select id, name from langs order by id").unwrap!
  while sqlite3_step(query) == SQLITE_ROW
    puts("#{sqlite3_column_int(query, 0)} #{sqlite3_column_text(query, 1)}")
  end

  Native.close(query)
  IO.inspect(prepare(db, "select from nowhere")) # error("sqlite: near \"from\": syntax error")
  IO.inspect(open("/no/such/dir/x.db")) # error("sqlite: unable to open database file")
  Native.close(db)
end
```
