Mirror of forum.nim-lang.org

4894 :: Future direction of db_* modules

• JohnAD (orginal) [2019-05-31T03:50:52+02:00] view original

  Currently the database modules (db_mysql, db_postgre, and db_sqlite) all rely on using nil to represent NULL when passing data in/out of the module.

  As most folks know, Nim is moving away from production support of nil, so it would be good to make changes to those libraries. I'm happy to help with this, but rather arbitrarily deciding on a solution, I want to get opinions and/or direction from the community.

  But, to get started, let's formally describe the current solution:

  CURRENT

  Rows of data are passed in/out using:

  seq[string]
  Run

  Where NULL values are represented by entries with a nil. All data types are represented by the string equivalent of the data.

  So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT" and "desc" is NULL, you would pass:

  @["joe", nil, "30"]
  Run

  I see four ways we could change this:

  STRINGIFY

  Rows of data are passed in/out using:

  seq[string]
  Run

  Where NULL values are represented by entries with the strings NULL. Strings are quoted. All other data types are represented by the string equivalent of the data.

  So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT" and "desc" is NULL, you would pass:

  @["'joe'", "NULL", "30"]
  Run

  notice the extra inner quotes around the real string value.

  OPTION[STRING]

  Rows of data are passed in/out using:

  seq[Option[string]]
  Run

  Where NULL values are represented by entries with none. All other data types are represented by the string equivalent of the data.

  So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT" and "desc" is NULL, you would pass:

  @[some[string]("joe"), none(string), some[string]("30")]
  Run

  TUPLES

  Rows of data are passed in/out using:

  tuple[data: seq[string], are_null: seq[bool]]
  Run

  The data types are represented by the string equivalent of the data, and the null items are indicated in the separate sequence.

  So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT" and "desc" is NULL, you would pass:

  (data: @["joe", "", "30"], are_null: @[false, true, false])
  Run

  JSON

  Rows of data are passed in/out using:

  seq[JsonNode]
  Run

  Where all data types are represented by their JSON equivalents.

  So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT" and "desc" is NULL, you would pass:

  @[parseJson("""["joe", null, 30]""")]
  Run

  NULLABLE

  Rows of data are passed in/out using:

  seq[nstring]
  Run

  Where all data types are represented by string equivalents.

  So, if you have three columns of "name VARCHAR(50), desc VARCHAR(50), age INT" and "desc" is NULL, you would pass:

  @["joe", NULL, "30"]
  Run

  Note: I've not actually finished this type library yet. In fact, I only have nint's type operations fully flushed out.

  Okay, and now:

  NOTES:

  • A subtle problem with both the CURRENT method and the OPTION[STRING] method is that nil and none are best interpreted as "no value". But on a database, NULL which means "unknown value". That is why, in SQL, "NULL != NULL". Two real but unknown values cannot be assumed to be equal. https://www.essentialsql.com/get-ready-to-learn-sql-server-what-is-a-null-value/ Handling this correctly solves many odd border-case scenarios, especially with aggregation functions.
  • I mention my "nullable" database types library to be complete. I'm not pushing for this. In fact, using that would require that nullable be added to the standard library, which I'm not entirely sure is a good idea. But, if curious, details are at https://github.com/JohnAD/nullable .
  • Personal option, so far: I'm really not a fan on using JSON given the overhead that would raise. I think that STRINGABLE and OPTION[STRING] are the best candidates. But, I'm happy to go along with the community.

---

• mikra (orginal) [2019-05-31T07:37:12+02:00] view original

  Hi JohnAD,

  there is already an issue open : https://github.com/nim-lang/Nim/issues/9453

  The sqlite3-part is finished - if you like to dive in please take a look at the sqlite3_examples folder of my project. You can "mix" my module with Nim´s (db_sqlite3). The other modules are (unfinished) on my desk but not forgotten. Discussion and PR´s are welcome.

  regarding dbNull´s: thats a never ending story - best fit would be "do not handle it within the db-layer". Just let the consumer handle it. It´s a state and not a value - unfortunately handled vendor-specific - always take a look into the vendors's manual regarding dbNulls :-) So if you do a select null||'hello_nim' from dual you will be surprised :-)

  The option-type would be the best fit for Nim I think. But I would not enforce that - if you like to do bulk-processing the option-variant is significant slower than raw-handling. Regarding json on the database I have my own (private) opinion :->

---

• JohnAD (orginal) [2019-05-31T18:07:00+02:00] view original

  thanks @mikra!

  I did a search in Nim issues and somehow didn't find that one.

  Araq mentioned possibly moving ndb to the standard library if it can act as a replacement for the db_* modules. I'll look into volunteering to help with development of ndb. I like what I see so far, especially the handling of nulls and blobs with dbNull and dbBlob.

---