BLOB data

[arkanoid87]

need a way to store generic data, exactly as it was input

what about mapping seq of byte/uint8 like here?
https://nim-lang.org/docs/db_sqlite.html#bindParam%2CSqlPrepared%2Cint%2CopenArray%5Bbyte%5D

[moigagoo]

Thanks for taking the time to report the issue!

I'm heavily relying on ndb. On one hand, it has blob type support for SQLite, so it should be trivial to add a mapping from any Nim to blob: https://xzfc.github.io/ndb.nim/v0.19.8/sqlite.html#DbValueKind Yeas we can choose byte or uint8. I think byte fits better, the mnemonic is better IMHO.

However, there's no ready to use blob type for Postgres: https://xzfc.github.io/ndb.nim/v0.19.8/postgres.html#DbValue Which means, blob support must be done through dvkOther handling. There's a issue with that as the value of dvkOther can't be accessed at the moment. I'm currently working on varchar and char support, which too requires access to dvkOther fields, so I've sent a PR to ndb: xzfc/ndb.nim#17

Hopefully, if the PR is merged and after I've found a way to support the various types Postgres offers, I'll be able to tackle this issue.

[arkanoid87]

literally just one character change on ndb. I see that same request has been waiting since May 25, 2020, do we have any chance?

What about proceeding on a different fork meanwhile so we can experimenti this while waiting for updates?

[moigagoo]

Well I'm using my fork in the meantime, until the PR is merged. If it's not though, I'll have to use that fork permanently.

[arkanoid87]

not sure why, but while using strings as data containers as workaround, I'm encountering some strange behaviour in selecting data.
I'm quite sure the insertion works correctly, as final db size matches and opening db file with any db manager shows correct number of bytes for each cell, but the select with norm always returns a 12 characters long string, no matter the row.

norm v. 2.2.2

my model

type
  DbBuffer* = ref object of Model
    data: string

func newDbBuffer*(data: string = ""): DbBuffer =
    DbBuffer(data: data)

func getData*(buffer: DbBuffer): string =
    buffer.data

insert:

proc handleBuffer(data: openArray[uint8]) =
  var s: string = newString(data.len)
  for i in 0..data.len-1:
    s[i] = char(data[i])
  echo s.len # variable 200 ... 1500
  var dbBuffer = newDbBuffer(s)
  with dbConn:
    insert dbBuffer

select:

var buf = newDbBuffer()
with dbConn:select(buf, "id = ?", 50)
echo buf.getData.len # always 12

UPDATE:
apparently not a norm problem, also std sb_sqlite does the same, but yet I don't know why

import db_sqlite

proc main =
  let db = open("mydb.sqlite", "", "", "")
  defer: db.close
  var v = db.getValue(sql"SELECT data FROM DbBuffer WHERE id=?", 50)
  echo v # ��s;,�ب",�
  echo v.len # 12

if isMainModule:
  main()

UPDATE 2:
I've successfully retrieved cell with small python code, but I had to use the text_factory attribute to manually set that text is bytes

import sqlite3

con = sqlite3.connect("mydb.sqlite")
con.text_factory = bytes
cur = con.cursor()
for row in cur.execute('SELECT data FROM mytable WHERE id=1;'):
    b = row[-1]
    print(len(b)) # 1360
con.close()

seems that the problem is caused by null byte nim-lang/db_connector#12
is there anything similar in nim/norm?

UPDATE 3:
finally achieved it using ndb

import ndb/sqlite
let db = open("mydb.sqlite", "", "", "")
for r in db.instantRows(sql"SELECT * FROM mytable LIMIT ?", 1):
  var v = r[0,DbBlob].string
  echo v
db.close

[arkanoid87]

Well I'm using my fork in the meantime, until the PR is merged. If it's not though, I'll have to use that fork permanently.

it has been accepted! Hooray!

[Clonkk]

apparently not a norm problem, also std sb_sqlite does the same, but yet I don't know why

The problem is that you declared your field as string and not a DbBlob.
This is important because the underlying representation of stringis TEXT which gets converted to a cstring when calling the C-API for a select query.

If you store binary data into a cstring it will gets truncated at the first byte with value 00 . This is why, your data gets truncated at length 12 because the value of the byte is 00 (and also why Python does not have this - because python handles it differently).

You have the same result using std/db_sqlite because of the same reason (cstring convertion) except it convert both TEXT and BLOB field to cstring internally before returning a string (yes, converting BLOB field to cstring is a bug - I fixed it in this commit : nim-lang/Nim@6fa82a5 but it hasn't been released yet).

For Norm, using a DbBlob type field - I use Norm 2.2.1 and half the fields in my SQLite database are DbBlob - should work :

type
  DbBuffer* = ref object of Model
    data: DbBlob 

DbBlob is simply defined as type DbBlob = distinct string so it's easy enough to work with if you already have a string type. If you buffer is stored in seq[byte] you can just convert it to a string.

If you have issue working with both seq[byte] and stringas buffer, you can take a look at https://github.com/Clonkk/bytesequtils (it's tailored for my personal usage so it's a bit "raw", feel free to open an issue if you have any problems / questions).

[arkanoid87]

Thanks for the solution and explanation. I think I will need bytesequtils too as I'm already facing the struggle of different modules where some like OpenArray[uint8] some like string

[Yu-Vitaqua-fer-Chronos]

Hey there, any updates on this? I'm using norm for my chat platform project, but I'm trying to figure out how to correctly store hashes as seq[byte] doesn't seem to be supported (For Postgres could be stored as bytea, and as a BLOB for Sqlite?) which is an issue for me

[moigagoo]

@Yu-Vitaqua-fer-Chronos Hi! No news here. But I'm not seeing any obstacles to implementing this feature.

Also, it seems like BLOBs are already supported for SQLite.

[Clonkk]

Also, it seems like BLOBs are already supported for SQLite.

Can confirm; I use them all the time.

I took a peak at postgres a while back but I don't recall having access to bytea type