createTable sql error, generate comma in last query field

[Newrite]

First of all sorry for my english

Repro

import db_sqlite
import gatabase
include gatabase/sugar

let db = open("data.sqlite3", "", "", "")

let myTable = createTable "kitten": [
  "age"    := 1,
  "sex"    := 'f',
  "name"   := "unnamed",
  "rank"   := 3.14,
  "weight" := int,
  "color"  := char,
  "owner"  := string,
  "food"   := float,
]

echo myTable.string

db.tryExec(myTable).echo

Result

Hint: C:\Users\nirn2\nimcache\repro_d\repro.exe  [Exec]
CREATE TABLE IF NOT EXISTS kitten(
  id    INTEGER PRIMARY KEY,
        age     INTEGER NOT NULL        DEFAULT 1,
        sex     VARCHAR(1)      NOT NULL        DEFAULT 'f',
        name    TEXT    NOT NULL        DEFAULT 'unnamed',
        rank    REAL    NOT NULL        DEFAULT 3.14,
        weight  INTEGER,
        color   VARCHAR(1),
        owner   TEXT,
        food    REAL,
);
false

When createTable generate fields, it alwayes add a comma as last character, if delete comma in last field SqlQuery is ok

createTable
...
  for field in code: cueri.add field
  cueri.delete(cueri.len-2, cueri.len-2) #import strutils for test
  cueri.add ");" # http://blog.2ndquadrant.com/postgresql-10-identity-columns 
  sql(cueri)

Result

Hint: C:\Users\nirn2\nimcache\repro_d\repro.exe  [Exec]
CREATE TABLE IF NOT EXISTS kitten(
  id    INTEGER PRIMARY KEY,
        age     INTEGER NOT NULL        DEFAULT 1,
        sex     VARCHAR(1)      NOT NULL        DEFAULT 'f',
        name    TEXT    NOT NULL        DEFAULT 'unnamed',
        rank    REAL    NOT NULL        DEFAULT 3.14,
        weight  INTEGER,
        color   VARCHAR(1),
        owner   TEXT,
        food    REAL
);
true

Sql error

G:\Programming\Languages\Nim\HelloTwo\repro.nim(20) repro
G:\Programming\nim\lib\impure\db_sqlite.nim(227) exec
G:\Programming\nim\lib\impure\db_sqlite.nim(147) dbError
Error: unhandled exception: near ")": syntax error [DbError]

[obemaru4012]

I had the same error as Alexander-San, so I looked into it and found this issue. I made a simple fix to createTable in sugar.nim and the comma is no longer attached to the last field in the column.

The modification is shown in the below.

template createTable*(name: static string; code: untyped): SqlQuery =
  ## Create a new database table `name` with fields from `code`, returns 1 `SqlQuery`.
  ## Works with Postgres and Sqlite. `SqlQuery` is pretty-printed when not built for release.
  ##
  ## .. code-block::nim
  ##   import db_sqlite
  ##   include gatabase/sugar
  ##   let myTable = createTable "kitten": [
  ##     "age"    := 1,
  ##     "sex"    := 'f',
  ##     "name"   := "unnamed",
  ##     "rank"   := 3.14,
  ##     "weight" := int,
  ##     "color"  := char,
  ##     "owner"  := string,
  ##     "food"   := float,
  ##   ]
  ##
  ## Generates the SQL Query:
  ##
  ## .. code-block::
  ##   CREATE TABLE IF NOT EXISTS kitten(
  ##     id      INTEGER      PRIMARY KEY,
  ##     age     INTEGER      NOT NULL      DEFAULT 1,
  ##     sex     VARCHAR(1)   NOT NULL      DEFAULT 'f',
  ##     name    TEXT         NOT NULL      DEFAULT 'unnamed',
  ##     rank    REAL         NOT NULL      DEFAULT 3.14,
  ##     weight  INTEGER,
  ##     color   VARCHAR(1),
  ##     owner   TEXT,
  ##     food    REAL,
  ##   );
  ##
  ## More examples:
  ## * https://github.com/juancarlospaco/nim-gatabase/blob/master/examples/database_fields_example.nim#L1
  assert name.len > 0, "Table name must not be empty string"
  const nl = when defined(release): " " else: "\n"

  template `:=`(dbfield: static string; value: static char): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & dbfield & "\t" & "VARCHAR(1)\tNOT NULL\tDEFAULT '" & $value & "'"

  template `:=`(dbfield: static string; value: static SomeFloat): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & dbfield & "\t" & "REAL\tNOT NULL\tDEFAULT "  & $value

  template `:=`(dbfield: static string; value: static SomeInteger): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & dbfield & "\t" & "INTEGER\tNOT NULL\tDEFAULT "  & $value

  template `:=`(dbfield: static string; value: static bool): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & dbfield & "\t" & "BOOLEAN\tNOT NULL\tDEFAULT "  & (if $value == "true": "1" else: "0")

  template `:=`(dbfield: static string; value: static string): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & dbfield & "\t" & "TEXT\tNOT NULL\tDEFAULT '"  & $value & "'"

  template `:=`(dbfield: static string; value: typedesc[char]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "VARCHAR(1)"

  template `:=`(dbfield: static string; value: typedesc[SomeFloat]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "REAL" 

  template `:=`(dbfield: static string; value: typedesc[SomeInteger]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "INTEGER"

  template `:=`(dbfield: static string; value: typedesc[bool]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "BOOLEAN"

  template `:=`(dbfield: static string; value: typedesc[string]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "TEXT"

  template `:=`(dbfield: static cstring; value: typedesc[char]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "VARCHAR(1)\tUNIQUE"

  template `:=`(dbfield: static cstring; value: typedesc[SomeFloat]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "REAL\tUNIQUE"

  template `:=`(dbfield: static cstring; value: typedesc[SomeInteger]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "INTEGER\tUNIQUE"

  template `:=`(dbfield: static cstring; value: typedesc[bool]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "BOOLEAN\tUNIQUE"

  template `:=`(dbfield: static cstring; value: typedesc[string]): string =
    assert dbfield.len > 0, "Table field name must not be empty string"
    "\t" & $dbfield & "\t" & "TEXT\tUNIQUE"

  var cueri = "CREATE TABLE IF NOT EXISTS " & name & "(" & nl & (
    when defined(postgres): "  id\tINTEGER\tGENERATED BY DEFAULT AS IDENTITY,"
    else: "  id\tINTEGER\tPRIMARY KEY,") & nl

  for index, field in code:
    if index != code.len - 1:
      cueri.add field & "," & nl
    else:
      cueri.add field & nl

  cueri.add ");" # http://blog.2ndquadrant.com/postgresql-10-identity-columns
  sql(cueri)

[juancarlospaco]

@obemaru4012 Make a Pull Request.

[obemaru4012]

After confirming that everything is OK, I will make a pull request. 💻👻💦