Mirror of forum.nim-lang.org

7659 :: Experimenting with reflections to create SQL statements, got some questions

• Bonesinger (orginal) [2021-03-19T01:25:55+01:00] view original

  I'm experimenting with reflections on Nim, mostly to get a better grasp of the language, seeing if I can make a prepared SQL statement given any object with any number of fields. It works, but there are a few things I still didn't manage. Here's the code:

  import strutils
  
  proc createTable[Obj](): string =
    var tostr: Obj
    #The SQL should be compatible with PostgreSQL and MySQL/MariaDB
    var preparedSql = "CREATE OR REPLACE TABLE "
    preparedSql.add($Obj)
    preparedSql.add(" (")
    var vartype : string
    for name, field in fieldPairs(tostr):
      preparedSql.add(name)
      preparedSql.add(" ")
      if ($field.type=="string"):
        vartype = "varchar(3000)"
      else:
        vartype = $(field.type)
      preparedSql.add(vartype)
      preparedSql.add(", ")
    preparedSql.removeSuffix(", ") #This could be an if check in the for loop above, but this works fine right now.
    preparedSql.add(");")
    result = preparedSql #Might as well use result instead of preparedSql in the code above
  
  type Sample = object
    pid : int
    forkey : int
    name : string
    description : string
  
  var sam = Sample(pid: 1, forkey: 2, name: "Sample", description: "Plenty of words")
  echo createTable[sam]()
  #The result will print: CREATE OR REPLACE TABLE Sample (pid int, forkey int, name varchar(3000), description varchar(3000));
  Run

  I do think the code is much longer than needed, but I'm not entirely sure what could be trimmed/joined.

  Now, there are some questions I'd like to make:

  1. Why does fieldPairs() only work when called for an iterator? Is there a way to treat its result as a variable or something that can be printed/echo'ed?
  2. Why does the generic "accept any type of object" have to be declared in the brackets instead of parenthesis of a proc? I tried proc createTable (obj: T) and (obj: object), none worked.
  3. I am currently looking into custom annotations, which are done with pragmas defined as templates. Will the reflection recognize them? How do I check for their existence to properly treat each object field?
  4. Other than annotations/pragmas, is it possible to give extra details to object's fields, such as defining a primary key, foreign keys, if it is nullable, etc?
  5. What would be a decent alternative to the several preparedSql.add() that I call one after another?

---

• DavidKunz (orginal) [2021-03-19T07:56:19+01:00] view original

  Hi Bonesinger,

  That's an interesting project, I was also about to do something similar to this.

  I've found a nice repo called "norm", maybe you can find some inspiration: https://github.com/moigagoo/norm/blob/develop/src/norm/model.nim

  (It's acting on a special object though).

  Best regards, David

---

• mratsim (orginal) [2021-03-19T08:20:35+01:00] view original

  1. fieldPairs unrolls the iteration at compile-time. Types can only exist at compile-time and cannot be stored in a variable, use macros if you want to work on fields of an object. Additionally, replace `` if ($field.type=="string")`` by when field is string and use when (compile-time if) when you work with fieldPairs
  2. proc createTable(obj: typedesc): string =
  3. Use macros
  4. Yes, use custom pragmas

  You can have a look into how NESM or nim-serialization use custom tags: https://xomachine.gitlab.io/NESM/ https://github.com/status-im/nim-serialization#custom-serialization-of-user-defined-types

  type
    Foo = object
      a: string
      b {.dontSerialize.}: int
      c {.dontSerialize(JSON).}: float
      d {.serializedFieldName("z").}: int
  Run

---

• shirleyquirk (orginal) [2021-03-19T09:39:58+01:00] view original

  1: inline iterators transform themselves into a for loop, they are a special zero-cost abstraction that aren't like procs. fieldpairs is a particularly special case, in that each loop, the value can be a different type!

  you can of course turn it into something that can be echo'd, just as you're doing, by wrapping it in a proc and converting all the values to a single type, like string or JsonNode

  2:the syntax for generics` being square brackets may seem weird, but it's an important choice for a language, go went with square brackets as opposed to parentheses when they realized the ambiguity that could result, and rust's regrettable turbofish can't be removed`

  But it's also possible to achieve what you're doing by passing a typedesc :

  proc createTable(Obj:typedesc): string =
    var tostr: Obj
    for fld,val in Obj.fieldpairs:
      result.add(fld & ',' & $val & '\n')
  Run

  3: it's possible to check custom pragmas with hasCustomPragma and getCustomPragmaVal from macros

  4. getCustomPragmaVal in particular might be interesting for you, the user specifies {.customPragma:"arbitrary text".} and you can receive "arbitrary text" in your proc
  5. there's & for concatenating strings together, or for more complex situations strformat is pretty great

  putting it all together:

  import macros,strformat,strutils
  template primarykey{.pragma.}
  template foreignkey(s:string){.pragma.}
  proc createTable(Obj:typedesc): string =
    var tostr: Obj
    #The SQL should be compatible with PostgreSQL and MySQL/MariaDB
    var preparedSql = &"CREATE OR REPLACE TABLE {$Obj} ("
    var additions : string
    for name, field in fieldPairs(tostr):
      preparedSql.add name & " " & (if typeof(field) is string: "varchar(3000)" else: $typeof(field)) & ", "
      when hasCustomPragma(field,primarykey): additions.add "PRIMARY KEY (" & name & "), "
      elif hasCustomPragma(field,foreignkey): additions.add "FOREIGN KEY (" & name & ") REFERENCES " & getCustomPragmaVal(field,foreignkey) & "(" & name & "), "
    
    preparedSql.add additions
    preparedSql.removeSuffix(", ") #This could be an if check in the for loop above, but this works fine right now.
    
    preparedSql.add ");"
    result = preparedSql #Might as well use result instead of preparedSql in the code above
  
  type Sample = object
    pid{.primarykey.} : int
    forkey{.foreignkey:"OtherTable".} : int
    name : string
    description : string
  
  echo createTable(Sample)
  Run

  https://play.nim-lang.org/#ix=2Toa

---