db_sqlite: Update documentation (#10330) (#11266)

1 files changed, 359 insertions, 71 deletions

diff --git a/lib/impure/db_sqlite.nim b/lib/impure/db_sqlite.nim
index ad2be5882..9b714a778 100644
--- a/lib/impure/db_sqlite.nim
+++ b/lib/impure/db_sqlite.nim
@@ -10,76 +10,96 @@
 ## A higher level `SQLite`:idx: database wrapper. This interface
 ## is implemented for other databases too.
 ##
-## See also: `db_odbc <db_odbc.html>`_, `db_postgres <db_postgres.html>`_,
-## `db_mysql <db_mysql.html>`_.
+## Basic usage
+## ===========
+##
+## The basic flow of using this module is:
+##
+## 1. Open database connection
+## 2. Execute SQL query
+## 3. Close database connection
 ##
 ## Parameter substitution
-## ======================
+## ----------------------
 ##
 ## All ``db_*`` modules support the same form of parameter substitution.
 ## That is, using the ``?`` (question mark) to signify the place where a
 ## value should be placed. For example:
 ##
 ## .. code-block:: Nim
-##     sql"INSERT INTO myTable (colA, colB, colC) VALUES (?, ?, ?)"
 ##
-## Examples
-## ========
+##    sql"INSERT INTO my_table (colA, colB, colC) VALUES (?, ?, ?)"
 ##
 ## Opening a connection to a database
 ## ----------------------------------
 ##
 ## .. code-block:: Nim
-##     import db_sqlite
-##     let db = open("mytest.db", "", "", "")  # user, password, database name can be empty
-##     db.close()
+##
+##    import db_sqlite
+##
+##    # user, password, database name can be empty.
+##    # These params are not used on db_sqlite module.
+##    let db = open("mytest.db", "", "", "")
+##    db.close()
 ##
 ## Creating a table
 ## ----------------
 ##
 ## .. code-block:: Nim
-##      db.exec(sql"DROP TABLE IF EXISTS myTable")
-##      db.exec(sql("""CREATE TABLE myTable (
-##                       id integer,
-##                       name varchar(50) not null)"""))
+##
+##    db.exec(sql"DROP TABLE IF EXISTS my_table")
+##    db.exec(sql"""CREATE TABLE my_table (
+##                     id   INTEGER,
+##                     name VARCHAR(50) NOT NULL
+##                  )""")
 ##
 ## Inserting data
 ## --------------
 ##
 ## .. code-block:: Nim
-##     db.exec(sql"INSERT INTO myTable (id, name) VALUES (0, ?)",
-##             "Jack")
+##
+##    db.exec(sql"INSERT INTO my_table (id, name) VALUES (0, ?)",
+##            "Jack")
 ##
 ## Larger example
 ## --------------
 ##
 ## .. code-block:: nim
 ##
-##  import db_sqlite, math
+##    import db_sqlite, math
+##
+##    let db = open("mytest.db", "", "", "")
+##
+##    db.exec(sql"DROP TABLE IF EXISTS my_table")
+##    db.exec(sql"""CREATE TABLE my_table (
+##                     id    INTEGER PRIMARY KEY,
+##                     name  VARCHAR(50) NOT NULL,
+##                     i     INT(11),
+##                     f     DECIMAL(18, 10)
+##                  )""")
 ##
-##  let theDb = open("mytest.db", "", "", "")
+##    db.exec(sql"BEGIN")
+##    for i in 1..1000:
+##      db.exec(sql"INSERT INTO my_table (name, i, f) VALUES (?, ?, ?)",
+##              "Item#" & $i, i, sqrt(i.float))
+##    db.exec(sql"COMMIT")
 ##
-##  theDb.exec(sql"Drop table if exists myTestTbl")
-##  theDb.exec(sql("""create table myTestTbl (
-##       Id    INTEGER PRIMARY KEY,
-##       Name  VARCHAR(50) NOT NULL,
-##       i     INT(11),
-##       f     DECIMAL(18,10))"""))
+##    for x in db.fastRows(sql"SELECT * FROM my_table"):
+##      echo x
 ##
-##  theDb.exec(sql"BEGIN")
-##  for i in 1..1000:
-##    theDb.exec(sql"INSERT INTO myTestTbl (name,i,f) VALUES (?,?,?)",
-##          "Item#" & $i, i, sqrt(i.float))
-##  theDb.exec(sql"COMMIT")
+##    let id = db.tryInsertId(sql"""INSERT INTO my_table (name, i, f)
+##                                  VALUES (?, ?, ?)""",
+##                            "Item#1001", 1001, sqrt(1001.0))
+##    echo "Inserted item: ", db.getValue(sql"SELECT name FROM my_table WHERE id=?", id)
 ##
-##  for x in theDb.fastRows(sql"select * from myTestTbl"):
-##    echo x
+##    db.close()
 ##
-##  let id = theDb.tryInsertId(sql"INSERT INTO myTestTbl (name,i,f) VALUES (?,?,?)",
-##        "Item#1001", 1001, sqrt(1001.0))
-##  echo "Inserted item: ", theDb.getValue(sql"SELECT name FROM myTestTbl WHERE id=?", id)
+## See also
+## ========
 ##
-##  theDb.close()
+## * `db_odbc module <db_odbc.html>`_ for ODBC database wrapper
+## * `db_mysql module <db_mysql.html>`_ for MySQL database wrapper
+## * `db_postgres module <db_postgres.html>`_ for PostgreSQL database wrapper
 
 {.deadCodeElim: on.}  # dce option deprecated
 
@@ -89,21 +109,35 @@ import db_common
 export db_common
 
 type
-  DbConn* = PSqlite3  ## encapsulates a database connection
-  Row* = seq[string]  ## a row of a dataset. NULL database values will be
-                       ## converted to nil.
-  InstantRow* = Pstmt  ## a handle that can be used to get a row's column
-                       ## text on demand
+  DbConn* = PSqlite3  ## Encapsulates a database connection.
+  Row* = seq[string]  ## A row of a dataset. `NULL` database values will be
+                      ## converted to an empty string.
+  InstantRow* = Pstmt ## A handle that can be used to get a row's column
+                      ## text on demand.
 
 proc dbError*(db: DbConn) {.noreturn.} =
-  ## raises a DbError exception.
+  ## Raises a `DbError` exception.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##    if not db.tryExec(sql"SELECT * FROM not_exist_table"):
+  ##      dbError(db)
+  ##    db.close()
   var e: ref DbError
   new(e)
   e.msg = $sqlite3.errmsg(db)
   raise e
 
 proc dbQuote*(s: string): string =
-  ## DB quotes the string.
+  ## Escapes the `'` (single quote) char to `''`.
+  ## Because single quote is used for defining `VARCHAR` in SQL.
+  runnableExamples:
+    doAssert dbQuote("'") == "''''"
+    doAssert dbQuote("A Foobar's pen.") == "'A Foobar''s pen.'"
+
   result = "'"
   for c in items(s):
     if c == '\'': add(result, "''")
@@ -123,7 +157,16 @@ proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string =
 proc tryExec*(db: DbConn, query: SqlQuery,
               args: varargs[string, `$`]): bool {.
               tags: [ReadDbEffect, WriteDbEffect].} =
-  ## tries to execute the query and returns true if successful, false otherwise.
+  ## Tries to execute the query and returns `true` if successful, `false` otherwise.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##    if not db.tryExec(sql"SELECT * FROM my_table"):
+  ##      dbError(db)
+  ##    db.close()
   assert(not db.isNil, "Database not connected.")
   var q = dbFormat(query, args)
   var stmt: sqlite3.Pstmt
@@ -134,7 +177,20 @@ proc tryExec*(db: DbConn, query: SqlQuery,
 
 proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`])  {.
   tags: [ReadDbEffect, WriteDbEffect].} =
-  ## executes the query and raises DbError if not successful.
+  ## Executes the query and raises a `DbError` exception if not successful.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##    try:
+  ##      db.exec(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
+  ##              1, "item#1")
+  ##    except:
+  ##      stderr.writeLine(getCurrentExceptionMsg())
+  ##    finally:
+  ##      db.close()
   if not tryExec(db, query, args): dbError(db)
 
 proc newRow(L: int): Row =
@@ -158,11 +214,33 @@ iterator fastRows*(db: DbConn, query: SqlQuery,
                    args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
   ## Executes the query and iterates over the result dataset.
   ##
-  ## This is very fast, but potentially dangerous.  Use this iterator only
+  ## This is very fast, but potentially dangerous. Use this iterator only
   ## if you require **ALL** the rows.
   ##
-  ## Breaking the fastRows() iterator during a loop will cause the next
-  ## database query to raise a DbError exception ``unable to close due to ...``.
+  ## **Note:** Breaking the `fastRows()` iterator during a loop will cause the
+  ## next database query to raise a `DbError` exception ``unable to close due
+  ## to ...``.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    for row in db.fastRows(sql"SELECT id, name FROM my_table"):
+  ##      echo row
+  ##
+  ##    # Output:
+  ##    # @["1", "item#1"]
+  ##    # @["2", "item#2"]
+  ##
+  ##    db.close()
   var stmt = setupQuery(db, query, args)
   var L = (column_count(stmt))
   var result = newRow(L)
@@ -176,8 +254,36 @@ iterator fastRows*(db: DbConn, query: SqlQuery,
 iterator instantRows*(db: DbConn, query: SqlQuery,
                       args: varargs[string, `$`]): InstantRow
                       {.tags: [ReadDbEffect].} =
-  ## same as fastRows but returns a handle that can be used to get column text
-  ## on demand using []. Returned handle is valid only within the iterator body.
+  ## Similar to `fastRows iterator <#fastRows.i,DbConn,SqlQuery,varargs[string,]>`_
+  ## but returns a handle that can be used to get column text
+  ## on demand using `[]`. Returned handle is valid only within the iterator body.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    for row in db.instantRows(sql"SELECT * FROM my_table"):
+  ##      echo "id:" & row[0]
+  ##      echo "name:" & row[1]
+  ##      echo "length:" & $len(row)
+  ##
+  ##    # Output:
+  ##    # id:1
+  ##    # name:item#1
+  ##    # length:2
+  ##    # id:2
+  ##    # name:item#2
+  ##    # length:2
+  ##
+  ##    db.close()
   var stmt = setupQuery(db, query, args)
   try:
     while step(stmt) == SQLITE_ROW:
@@ -210,8 +316,33 @@ proc setColumns(columns: var DbColumns; x: PStmt) =
 iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery,
                       args: varargs[string, `$`]): InstantRow
                       {.tags: [ReadDbEffect].} =
-  ## same as fastRows but returns a handle that can be used to get column text
-  ## on demand using []. Returned handle is valid only within the iterator body.
+  ## Similar to `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_,
+  ## but sets information about columns to `columns`.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    var columns: DbColumns
+  ##    for row in db.instantRows(columns, sql"SELECT * FROM my_table"):
+  ##      discard
+  ##    echo columns[0]
+  ##
+  ##    # Output:
+  ##    # (name: "id", tableName: "my_table", typ: (kind: dbNull,
+  ##    # notNull: false, name: "INTEGER", size: 0, maxReprLen: 0, precision: 0,
+  ##    # scale: 0, min: 0, max: 0, validValues: @[]), primaryKey: false,
+  ##    # foreignKey: false)
+  ##
+  ##    db.close()
   var stmt = setupQuery(db, query, args)
   setColumns(columns, stmt)
   try:
@@ -221,17 +352,50 @@ iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery,
     if finalize(stmt) != SQLITE_OK: dbError(db)
 
 proc `[]`*(row: InstantRow, col: int32): string {.inline.} =
-  ## returns text for given column of the row
+  ## Returns text for given column of the row.
+  ##
+  ## See also:
+  ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
+  ##   example code
   $column_text(row, col)
 
 proc len*(row: InstantRow): int32 {.inline.} =
-  ## returns number of columns in the row
+  ## Returns number of columns in a row.
+  ##
+  ## See also:
+  ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
+  ##   example code
   column_count(row)
 
 proc getRow*(db: DbConn, query: SqlQuery,
              args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
-  ## retrieves a single row. If the query doesn't return any rows, this proc
-  ## will return a Row with empty strings for each column.
+  ## Retrieves a single row. If the query doesn't return any rows, this proc
+  ## will return a `Row` with empty strings for each column.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    doAssert db.getRow(sql"SELECT id, name FROM my_table"
+  ##                       ) == Row(@["1", "item#1"])
+  ##    doAssert db.getRow(sql"SELECT id, name FROM my_table WHERE id = ?",
+  ##                       2) == Row(@["2", "item#2"])
+  ##
+  ##    # Returns empty.
+  ##    doAssert db.getRow(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
+  ##                       3, "item#3") == @[]
+  ##    doAssert db.getRow(sql"DELETE FROM my_table WHERE id = ?", 3) == @[]
+  ##    doAssert db.getRow(sql"UPDATE my_table SET name = 'ITEM#1' WHERE id = ?",
+  ##                       1) == @[]
+  ##    db.close()
   var stmt = setupQuery(db, query, args)
   var L = (column_count(stmt))
   result = newRow(L)
@@ -241,21 +405,77 @@ proc getRow*(db: DbConn, query: SqlQuery,
 
 proc getAllRows*(db: DbConn, query: SqlQuery,
                  args: varargs[string, `$`]): seq[Row] {.tags: [ReadDbEffect].} =
-  ## executes the query and returns the whole result dataset.
+  ## Executes the query and returns the whole result dataset.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    doAssert db.getAllRows(sql"SELECT id, name FROM my_table") == @[Row(@["1", "item#1"]), Row(@["2", "item#2"])]
+  ##    db.close()
   result = @[]
   for r in fastRows(db, query, args):
     result.add(r)
 
 iterator rows*(db: DbConn, query: SqlQuery,
                args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
-  ## same as `FastRows`, but slower and safe.
+  ## Similar to `fastRows iterator <#fastRows.i,DbConn,SqlQuery,varargs[string,]>`_,
+  ## but slower and safe.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    for row in db.rows(sql"SELECT id, name FROM my_table"):
+  ##      echo row
+  ##
+  ##    ## Output:
+  ##    ## @["1", "item#1"]
+  ##    ## @["2", "item#2"]
+  ##
+  ##    db.close()
   for r in fastRows(db, query, args): yield r
 
 proc getValue*(db: DbConn, query: SqlQuery,
                args: varargs[string, `$`]): string {.tags: [ReadDbEffect].} =
-  ## executes the query and returns the first column of the first row of the
-  ## result dataset. Returns "" if the dataset contains no rows or the database
-  ## value is NULL.
+  ## Executes the query and returns the first column of the first row of the
+  ## result dataset. Returns `""` if the dataset contains no rows or the database
+  ## value is `NULL`.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    doAssert db.getValue(sql"SELECT name FROM my_table WHERE id = ?",
+  ##                         2) == "item#2"
+  ##    doAssert db.getValue(sql"SELECT id, name FROM my_table") == "1"
+  ##    doAssert db.getValue(sql"SELECT name, id FROM my_table") == "item#1"
+  ##
+  ##    db.close()
   var stmt = setupQuery(db, query, args)
   if step(stmt) == SQLITE_ROW:
     let cb = column_bytes(stmt, 0)
@@ -271,8 +491,19 @@ proc getValue*(db: DbConn, query: SqlQuery,
 proc tryInsertID*(db: DbConn, query: SqlQuery,
                   args: varargs[string, `$`]): int64
                   {.tags: [WriteDbEffect], raises: [].} =
-  ## executes the query (typically "INSERT") and returns the
+  ## Executes the query (typically "INSERT") and returns the
   ## generated ID for the row or -1 in case of an error.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##    db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
+  ##
+  ##    doAssert db.tryInsertID(sql"INSERT INTO not_exist_table (id, name) VALUES (?, ?)",
+  ##                            1, "item#1") == -1
+  ##    db.close()
   assert(not db.isNil, "Database not connected.")
   var q = dbFormat(query, args)
   var stmt: sqlite3.Pstmt
@@ -285,29 +516,86 @@ proc tryInsertID*(db: DbConn, query: SqlQuery,
 
 proc insertID*(db: DbConn, query: SqlQuery,
                args: varargs[string, `$`]): int64 {.tags: [WriteDbEffect].} =
-  ## executes the query (typically "INSERT") and returns the
-  ## generated ID for the row. For Postgre this adds
-  ## ``RETURNING id`` to the query, so it only works if your primary key is
-  ## named ``id``.
+  ## Executes the query (typically "INSERT") and returns the
+  ## generated ID for the row.
+  ##
+  ## Raises a `DbError` exception when failed to insert row.
+  ## For Postgre this adds ``RETURNING id`` to the query, so it only works
+  ## if your primary key is named ``id``.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##    db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
+  ##
+  ##    for i in 0..2:
+  ##      let id = db.insertID(sql"INSERT INTO my_table (id, name) VALUES (?, ?)", i, "item#" & $i)
+  ##      echo "LoopIndex = ", i, ", InsertID = ", id
+  ##
+  ##    # Output:
+  ##    # LoopIndex = 0, InsertID = 1
+  ##    # LoopIndex = 1, InsertID = 2
+  ##    # LoopIndex = 2, InsertID = 3
+  ##
+  ##    db.close()
   result = tryInsertID(db, query, args)
   if result < 0: dbError(db)
 
 proc execAffectedRows*(db: DbConn, query: SqlQuery,
                        args: varargs[string, `$`]): int64 {.
                        tags: [ReadDbEffect, WriteDbEffect].} =
-  ## executes the query (typically "UPDATE") and returns the
+  ## Executes the query (typically "UPDATE") and returns the
   ## number of affected rows.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##
+  ##    # Records of my_table:
+  ##    # | id | name     |
+  ##    # |----|----------|
+  ##    # |  1 | item#1   |
+  ##    # |  2 | item#2   |
+  ##
+  ##    doAssert db.execAffectedRows(sql"UPDATE my_table SET name = 'TEST'") == 2
+  ##
+  ##    db.close()
   exec(db, query, args)
   result = changes(db)
 
 proc close*(db: DbConn) {.tags: [DbEffect].} =
-  ## closes the database connection.
+  ## Closes the database connection.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    let db = open("mytest.db", "", "", "")
+  ##    db.close()
   if sqlite3.close(db) != SQLITE_OK: dbError(db)
 
 proc open*(connection, user, password, database: string): DbConn {.
   tags: [DbEffect].} =
-  ## opens a database connection. Raises `EDb` if the connection could not
-  ## be established. Only the ``connection`` parameter is used for ``sqlite``.
+  ## Opens a database connection. Raises a `DbError` exception if the connection
+  ## could not be established.
+  ##
+  ## **Note:** Only the ``connection`` parameter is used for ``sqlite``.
+  ##
+  ## **Examples:**
+  ##
+  ## .. code-block:: Nim
+  ##
+  ##    try:
+  ##      let db = open("mytest.db", "", "", "")
+  ##      ## do something...
+  ##      ## db.getAllRows(sql"SELECT * FROM my_table")
+  ##      db.close()
+  ##    except:
+  ##      stderr.writeLine(getCurrentExceptionMsg())
   var db: DbConn
   if sqlite3.open(connection, db) == SQLITE_OK:
     result = db
@@ -316,10 +604,10 @@ proc open*(connection, user, password, database: string): DbConn {.
 
 proc setEncoding*(connection: DbConn, encoding: string): bool {.
   tags: [DbEffect].} =
-  ## sets the encoding of a database connection, returns true for
-  ## success, false for failure.
+  ## Sets the encoding of a database connection, returns `true` for
+  ## success, `false` for failure.
   ##
-  ## Note that the encoding cannot be changed once it's been set.
+  ## **Note:** The encoding cannot be changed once it's been set.
   ## According to SQLite3 documentation, any attempt to change
   ## the encoding after the database is created will be silently
   ## ignored.