norm
norm copied to clipboard
BLOB data
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
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: https://github.com/xzfc/ndb.nim/pull/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.
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?
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.
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 https://github.com/nim-lang/db_connector/issues/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
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!
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 string
is 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 : https://github.com/nim-lang/Nim/commit/6fa82a5b3afbe644eef3fb41647a341d8e9b21c4 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 string
as 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).
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
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
@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.
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