[Dev] How to properly return arrays from a driver?

[IngwiePhoenix]

Hello! Discussions are not enabled, so I am making a ticket for this... apologies. :)

I am currently working on a SurrealDB Driver and I am currently "fighting" with related records.

Basically, SurrealDB has no JOIN, instead you use FETCH $field... in SELECT to pick fields whose related records should be resolved.

To demonstrate:

DEFINE TABLE book;
DEFINE FIELD author ON book TYPE record<author>;

DEFINE TABLE author;
DEFINE FIELD name ON author TYPE string;

When querying, a JSON object is returned (or a nested CBOR structure) as a result and currently, my driver's Next(dest []any) error method will see any sub-object as being a complex type and return []byte to avoid crashes.

For instance:

{
    "ID": "book:eragon",
    "author": {
        "ID": "author:christoph",
        "name": "Christoph Paolini"
    }
}

And basically, this would result in:

dest[0] = ID -> string
dest[1] = author -> []byte

...but after reading through the docs, I am not so sure if this should be the case or not. Hence, why I would like to ask some people with more experience in this :)

When using sqlx' ScanStruct methods (and similiar), how does it actually determine the keys to use to read into the struct? Is there any "post processing" I could possibly do to assist from within my driver?

So far, I see that perhaps I should instead use dot-notation for the columns: ID, author.ID, author.name. Is that assumption correct?

I just want to make sure my driver is well compatible. So far I tried to make REL work - and it kinda does but just fails when automatically attempting to determine foreign keys which just do not exist in SurrealDB - but I think there is still room for improvements.

Thank you very much and kind regards,

Ingwie

[IngwiePhoenix]

I think I found part of my answer:

sqlx/reflectx/reflect.go

Lines 342 to 443 in 41dac16

	func getMapping(t reflect.Type, tagName string, mapFunc, tagMapFunc mapf) *StructMap {
	m := []*FieldInfo{}
	root := &FieldInfo{}
	queue := []typeQueue{}
	queue = append(queue, typeQueue{Deref(t), root, ""})
	QueueLoop:
	for len(queue) != 0 {
	// pop the first item off of the queue
	tq := queue[0]
	queue = queue[1:]
	// ignore recursive field
	for p := tq.fi.Parent; p != nil; p = p.Parent {
	if tq.fi.Field.Type == p.Field.Type {
	continue QueueLoop
	}
	}
	nChildren := 0
	if tq.t.Kind() == reflect.Struct {
	nChildren = tq.t.NumField()
	}
	tq.fi.Children = make([]*FieldInfo, nChildren)
	// iterate through all of its fields
	for fieldPos := 0; fieldPos < nChildren; fieldPos++ {
	f := tq.t.Field(fieldPos)
	// parse the tag and the target name using the mapping options for this field
	tag, name := parseName(f, tagName, mapFunc, tagMapFunc)
	// if the name is "-", disabled via a tag, skip it
	if name == "-" {
	continue
	}
	fi := FieldInfo{
	Field: f,
	Name: name,
	Zero: reflect.New(f.Type).Elem(),
	Options: parseOptions(tag),
	}
	// if the path is empty this path is just the name
	if tq.pp == "" {
	fi.Path = fi.Name
	} else {
	fi.Path = tq.pp + "." + fi.Name
	}
	// skip unexported fields
	if len(f.PkgPath) != 0 && !f.Anonymous {
	continue
	}
	// bfs search of anonymous embedded structs
	if f.Anonymous {
	pp := tq.pp
	if tag != "" {
	pp = fi.Path
	}
	fi.Embedded = true
	fi.Index = apnd(tq.fi.Index, fieldPos)
	nChildren := 0
	ft := Deref(f.Type)
	if ft.Kind() == reflect.Struct {
	nChildren = ft.NumField()
	}
	fi.Children = make([]*FieldInfo, nChildren)
	queue = append(queue, typeQueue{Deref(f.Type), &fi, pp})
	} else if fi.Zero.Kind() == reflect.Struct || (fi.Zero.Kind() == reflect.Ptr && fi.Zero.Type().Elem().Kind() == reflect.Struct) {
	fi.Index = apnd(tq.fi.Index, fieldPos)
	fi.Children = make([]*FieldInfo, Deref(f.Type).NumField())
	queue = append(queue, typeQueue{Deref(f.Type), &fi, fi.Path})
	}
	fi.Index = apnd(tq.fi.Index, fieldPos)
	fi.Parent = tq.fi
	tq.fi.Children[fieldPos] = &fi
	m = append(m, &fi)
	}
	}
	flds := &StructMap{Index: m, Tree: root, Paths: map[string]*FieldInfo{}, Names: map[string]*FieldInfo{}}
	for _, fi := range flds.Index {
	// check if nothing has already been pushed with the same path
	// sometimes you can choose to override a type using embedded struct
	fld, ok := flds.Paths[fi.Path]
	if !ok || fld.Embedded {
	flds.Paths[fi.Path] = fi
	if fi.Name != "" && !fi.Embedded {
	flds.Names[fi.Path] = fi
	}
	}
	}
	return flds
	}

As far as I understand, any subelements of a struct will have their name in the TypeQueue be separated by a dot.

type Author struct {
  ID string
  Name string
}

type Book struct {
  ID string
  WrittenBy Author
}

If I am not wrong, this would result, that starting with Book, the names would be:

ID
written_by
written_by.ID
written_by.name

That said, there's so much going on in that function alone, I am quite sure I overlooked at least something...

[IngwiePhoenix]

Changed the title, after doing some tests:

package main

import (
	"fmt"

	_ "github.com/IngwiePhoenix/surrealdb-driver"
	"github.com/jmoiron/sqlx"
)

type Book struct {
	ID    string
	Title string
}

type Author struct {
	Id    string
	Name  string
	Likes []string
}

func main() {
	defSql := `
		DEFINE NAMESPACE IF NOT EXISTS p2;
		USE NS p2;
		DEFINE DATABASE IF NOT EXISTS p2;
		USE DB p2;
		
		DEFINE TABLE IF NOT EXISTS books SCHEMAFULL;
		DEFINE FIELD IF NOT EXISTS title ON books TYPE string;

		DEFINE TABLE IF NOT EXISTS authors SCHEMAFULL;
		DEFINE FIELD IF NOT EXISTS name ON authors TYPE string;
		DEFINE FIELD IF NOT EXISTS likes ON authors TYPE array<string>;
		// DEFINE FIELD IF NOT EXISTS written ON author TYPE array<optional<record<books>>>
	`
	db, err := sqlx.Connect("surrealdb", "ws://db:db@localhost:8000/rpc?method=root&db=p2&ns=p2")
	if err != nil {
		panic(err.Error())
	}

	res, err := db.Exec(defSql)
	if err != nil {
		panic(err.Error())
	}
	ins, _ := res.RowsAffected()
	fmt.Println(ins)


	res, _ = db.Exec(`
		CREATE authors:chris CONTENT {
			name: "Christopher",
			likes: ["a", "lot", "of", "stuff"]
		}
	`) // ignored; exists after rerun

	rows, err := db.Queryx("SELECT * FROM authors;")
	if err != nil {
		panic(err.Error())
	}

	for rows.Next() {
		if rows.Err() != nil {
			panic(rows.Err())
		}
		cols, err := rows.Columns()
		if err != nil {
			panic(err.Error())
		}
		a := Author{}

		fmt.Println("----------")
		fmt.Println(cols)
		err = rows.StructScan(&a) // <- implodes here
		if err != nil {
			panic(err.Error())
		}
		//a.Likes = make([]string, 4)
		//rows.Scan(&a.Id, &a.Likes[0], &a.Likes[1], &a.Likes[2], &a.Likes[3], &a.Name)
		fmt.Println(a)
		fmt.Println("----------")
	}
}

As you can see, author.likes is an array and I am not sure how to best present that to sqlx as it is very much not a common type.

The driver returned the following:

surrealdb:driver:rows:Next []string{"id", "likes.0", "likes.1", "likes.2", "likes.3", "name"} +0s
surrealdb:driver:rows:Next PUT "id" dest[0] = authors:chris +0s
surrealdb:driver:rows:Next PUT "likes.0" dest[1] = a +0s
surrealdb:driver:rows:Next PUT "likes.1" dest[2] = lot +0s
surrealdb:driver:rows:Next PUT "likes.2" dest[3] = of +0s
surrealdb:driver:rows:Next PUT "likes.3" dest[4] = stuff +0s
surrealdb:driver:rows:Next PUT "name" dest[5] = Christopher +0s

The records are internally received as a JSON message - so it's nested.

So, what do I "tell" sqlx, in the columns, to make it like arrays? Do I just return a blank array for likes and keep the .0, .1, .2, .3 columns?

Thanks!

[mrj0]

Just a guess, but when using postgres we'd use pg.Array types. I think if you declare likes as surrealtypes.ArrayOf[string] it'd probably work:

https://github.com/IngwiePhoenix/surrealdb-driver/blob/master/surrealtypes/arrays.go