samonzeweb/godb

A Go SQL query builder and struct mapper.

godb's Issues

panic in conditions when slice is empty for IN condition

Code :

err := conn.Select(&descriptions).Where("noeud_id in (?)", idsNoeuds).Do()

Cause : idsNoeuds is an empty slice.

Error :

panic: strings: negative Repeat count

goroutine 1 [running]:
strings.Repeat(0x6162ae, 0x2, 0xffffffffffffffff, 0x0, 0x97)
C:/Go/src/strings/strings.go:587 +0x1ce
github.com/samonzeweb/godb.Q(0x618897, 0xf, 0xc042077cd8, 0x1, 0x1, 0x1)
C:/Samuel/Provisoire/ParcoursGED.NET/Golang/src/github.com/samonzeweb/godb/condition.go:45 +0x654
github.com/samonzeweb/godb.(*StructSelect).Where(0xc042100240, 0x618897, 0xf, 0xc042077cd8, 0x1, 0x1, 0x410aff)
C:/Samuel/Provisoire/ParcoursGED.NET/Golang/src/github.com/samonzeweb/godb/struct_select.go:37 +0x8a

Map type

Hi Sam,

I've added Map type.
DB type: VARCHAR,CHAR,TEXT
GO type: map[string]interface{}
Please review #31, thanks.

LeftJoins on pointer structs doesn't fill data

Hi Samuel,

I hope you're well in these corona-days.

When using LeftJoin and scanning on it doesn't fill data if target struct is pointer. Please check this code:

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"
	
	"github.com/samonzeweb/godb"
	"github.com/samonzeweb/godb/adapters/sqlite"
)

type Author struct {
	ID        int    `db:"id" json:"id"`
	Firstname string `db:"firstname" json:"firstname"`
	Lastname  string `db:"lastname" json:"lastname"`
}

type Book struct {
	ID       int           `db:"id" json:"id"`
	AuthorID sql.NullInt64 `db:"author_id" json:"author_id"`
	Title    string        `db:"title" json:"title"`
}

type BookInfo struct {
	Book   `db:",rel=b" json:""`
	Author *struct {
		ID        sql.NullInt64  `db:"id" json:"id"`
		Firstname sql.NullString `db:"firstname" json:"firstname"`
		Lastname  sql.NullString `db:"lastname" json:"lastname"`
	} `db:",rel=a" json:"author,omitempty"`
}

func chkErr(msg string, err error) {
	if err != nil {
		log.Printf(msg, err)
		os.Exit(1)
	}
}

func main() {
	db, err := godb.Open(sqlite.Adapter, "./data.db")
	chkErr("Can not connect to db, err: %v", err)
	_, err = db.CurrentDB().Exec(`
--DROP TABLE books; DROP TABLE authors;
CREATE TABLE IF NOT EXISTS authors ( id INTEGER NOT NULL PRIMARY KEY, firstname TEXT NOT NULL, lastname TEXT NOT NULL );
CREATE TABLE IF NOT EXISTS books ( id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER, published DATE,
	FOREIGN KEY(author_id) REFERENCES authors(id));
INSERT INTO authors VALUES(1, 'Orhan','Pamuk') ON CONFLICT DO NOTHING ;
INSERT INTO books VALUES(1, 'My Name Is Red', 1, '1998-01-01') ON CONFLICT DO NOTHING;
INSERT INTO books VALUES(2,'Anonymous', NULL, NULL) ON CONFLICT DO NOTHING;`)
	chkErr("Can not migrate db, err: %v", err)
	
	var books []BookInfo
	err = db.SelectFrom("books as b").
		LeftJoin("authors", "a", godb.Q("b.author_id = a.id")).Do(&books)
	chkErr("Can not get books, err: %v", err)
	out, err := json.MarshalIndent(books, "", "   ")
	chkErr("Can not get marshall, err: %v", err)
	fmt.Printf("%s\n", out)
}

This prints output as:

[
   {
      "id": 1,
      "author_id": { "Int64": 1, "Valid": true},
      "title": "My Name Is Red"
   },
   {
      "id": 2,
      "author_id": { "Int64": 0, "Valid": false},
      "title": "Anonymous"
   }
]

Author is missing in output for book record no: 1. If I change struct to this it fills data, but this time fills for null author with null values as it is not pointer:

type BookInfo struct {
	Book   `db:",rel=b" json:""`
	Author struct {
		ID        sql.NullInt64  `db:"id" json:"id"`
		Firstname sql.NullString `db:"firstname" json:"firstname"`
		Lastname  sql.NullString `db:"lastname" json:"lastname"`
	} `db:",rel=a" json:"author,omitempty"`
}

Am I missing something?
Thanks

White/Black list support for struct Update/Insert

Hi Sam,

Without w/b listing struct insert/update becomes useless and forces developer to use InsertInto/UpdateInto methods which results in lots of boilerplate code. So #21 is to resolve this.

Can you review #21?

Erkan.

Supporting Column Alias

Hi Sam,

Could you please review #29.

Thanks,
Erkan.

Need better UUID/GUID support

We use uuid as primary keys in Postgres, and this probably applies to GUID types in MS SQL Server. Things like hard-coded int64 primary keys (both Do and DoWithReturning on *InsertStatement) make it kind of a pain to work with. So, humbly requesting this feature.

Standardization of common errors for db backends

Hi Sam,

GODB supports multiple DB backends, but developing for multiple databases needs common error handling. For example to check UniqueConstraint error you should check error message for current db connection(if targeting 4 db types, 4 different if checks...) and this leads to lots of boiler code.

#23 is to solve this problem. Not all errors are added, but some common errors are added. Can you review #23?

Erkan.

Suporting cursors(get one row at a time)

Hi Sam,

It will be helpful to expose a function from SelectStatement to get rows one by one. It will be useful if user doesn't want to fetch all data from DB.

Of course we can use LIMIT and OFFSET but, if size of row is big(for example: rows with columns containing file contents) it will be helpful to get one by one and break if necessary.

derkan.

While trying to go get godb, unknown revision errors on golang.org/x/net

Go version:
go version go1.12.4 darwin/amd64

Observed behavior:

~/go/src/github.com/myrepo/myproject % go get github.com/samonzeweb/godb
go: finding golang.org/x/net v0.0.0-20190108225652-1e06a53dbb7e
go: finding golang.org/x/net v0.0.0-20181114220301-adae6a3d119a
go: golang.org/x/[email protected]: unknown revision adae6a3d119a
go: golang.org/x/[email protected]: unknown revision 1e06a53dbb7e
go: error loading module requirements

Expected behavior:
I expect that go get installs the module as intended.

Upsert support

I think this would be a good feature because it avoids one query.

Currently I've to do it this way:

err := db.Insert(&obj).Do()
if err != nil {
   db.Update(&obj).Do()
}

Eric

Create new release with module changes

Hello, Could you please create a new release with the go.mod fixes?

Thank you! :)

Null* times MarshalJSON and UnmarshalJSON

Hi Samuel,

In my first pull request for types, I've added wrappers around sql.Null* types, but later we removed these wrappers to use directly sql.Null* types. But sql.Null* types don't have MarshalJSON and UnmarshalJSON methods, which makes json data handling a problem. Also types.NullTime does not have such methods.

Is it OK to add wrappers for sql.Null* types again for MarshalJSON and UnmarshalJSON methods?

If it is OK I'll create a pull request for json compatibility to all(types.Null*, sql.Null*), if not I'll create a pull request for json compatibility to only our types(types.Null*).

Mapping pointer values

I had a problem where a column sometimes had float values and sometimes NULL values, something which should have been easily solved by using a pointer in my row struct. However for some reason this bit of code in newStructMappingDetails() in dbreflect.go prevents the use of pointers:

 // Only non pointers are mapped
if fieldInfo.Type.Kind() == reflect.Ptr {
	continue
}

Leaving this code in will return an "unknown column name" error when a row with a value shows up, and commenting it out works well for my purposes. Is there a reason pointers aren't mapped? This is so specific that I feel there must be some thought behind it. If not I would happily provide the worlds smallest pull request.

Added Int64Slice,StrSlice,Float64Slice and BoolSlice

Hi Sam,

Please review #27

Thanks,
Erkan.

postgresql schema

i use schema to group tables in my db. i usually write my query like this

SELECT * FROM auth.user

so i write a TableName() like this

func (*User) TableName() string { return "auth.user" }

but postgresql complains that relation "auth.user" does not exists. initial digging suggest that the Quote() function in godb simply apply quote around identifier, resulting in "auth.schema". while proper postgresql schema identifier should be "auth"."schema"

if i want to contribute a fix, should i modify the adapter's Quote() function or somewhere else?

Using one connection in several goroutines

When a connection is reused between differents goroutines, sometimes there's a race condition with "db.consumedTime" attribute.

The workaround is to use a RWMutex, but, I don't know if you have something in mind.

If it is ok for you, I can create the merge request

Fetching performance

Hi Samuel,

I did some benchmarking&profiling. Fetching is spending time&memory on calling (smd *structMappingDetails) traverseTree method. It is critical especially while scanning high number of rows from results(for reporting).

Full results of benchmarking is at this repo. As for every fetching of rows(single or multiple, after inserts and updates also) godb is calling traverseTree and that puts godb into middle of report. I think it will get better if a caching mechanism is added instead of running reflections for each time. Caching should be implemented specific to db session instance.

For example pg orm make use of this kind of caching.

I've checked if some kind of caching for column mapping is possible, but unfortunately couldn't find a place in code to implement. Can you also check this?

Here is profiling output for scanning rows of SQL with LIMIT 10000:

Showing top 10 nodes out of 81
      flat  flat%   sum%        cum   cum%
  274.51MB 29.92% 29.92%   457.53MB 49.87%  github.com/samonzeweb/godb/dbreflect.(*structMappingDetails).traverseTree /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/dbreflect/dbreflect.go
   95.01MB 10.36% 40.28%    95.01MB 10.36%  github.com/samonzeweb/godb/dbreflect.(*StructMapping).GetAllFieldsPointers.func1 /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/dbreflect/dbreflect.go
   58.50MB  6.38% 46.66%    58.50MB  6.38%  github.com/lib/pq.textDecode /data/Development/MyOpenSource/GODB/src/github.com/lib/pq/encode.go
   50.88MB  5.55% 52.20%    50.88MB  5.55%  github.com/lib/pq.(*stmt).exec /data/Development/MyOpenSource/GODB/src/github.com/lib/pq/buf.go
      44MB  4.80% 57.00%       44MB  4.80%  reflect.unsafe_New /usr/local/src/go/src/runtime/malloc.go
      43MB  4.69% 61.69%       43MB  4.69%  reflect.(*structType).Field /usr/local/src/go/src/reflect/type.go
   36.37MB  3.96% 65.65%    42.39MB  4.62%  github.com/samonzeweb/godb.(*SQLBuffer).WriteBytes /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/sqlbuffer.go
   33.27MB  3.63% 69.28%    33.77MB  3.68%  database/sql.driverArgs /usr/local/src/go/src/database/sql/convert.go
   27.50MB  3.00% 72.28%       38MB  4.14%  github.com/samonzeweb/godb/dbreflect.(*StructMapping).GetNonAutoFieldsValues.func1 /data/Development/MyOpenSource/GODB/src/github.com/samonzeweb/godb/dbreflect/dbreflect.go
   20.22MB  2.20% 74.48%   123.64MB 13.48%  github.com/lib/pq.(*conn).QueryContext /data/Development/MyOpenSource/GODB/src/github.com/lib/pq/conn_go18.go
(pprof) 

Sample SQL is:

type GDModel struct {
	Id      int           `db:"id,key,auto"`
	Name    string        `db:"name"`
	Title   string        `db:"title"`
	Fax     string        `db:"fax"`
	Web     string        `db:"web"`
	Age     int           `db:"age"`
	Right   bool          `db:"right"`
	Counter int64         `db:"counter"`
}

func (*GDModel) TableName() string {
	return "models"
}

var models []*GDModel
if err := db.Select(&models).Where("id > ?", 0).Limit(10000).Do(); err != nil {
	fmt.Printf("slice err: %v\n", err)
}

Run `staticcheck` with the CI

This Go tools dominikh/go-tools and especially staticcheck yielded some interesting results on godb.

question: how exec join query

does godb support join query
please show some example

Context support

Hi Sam,

What do you think about adding Context support? My suggestions are:
1- Setting it connection-wide like db.WithContext(context.Context) and each runner interface check if ctx is nil or not and call suitable sql func.
2- For each runner interface add context param with naming suffix ...Ctx like db.SelectFrom("books").DoCtx(&books, ctx). This one will need every runner and Transaction methods to be duplicated for Context parameter with Ctx suffix but more Go-like.
3- Without changing any interface, altering current runners with contex'ed ones internally, and get/set context with a function. For ex: db.SelectFrom("books").Do() internally will run ExecTx with ctx.Background.

If you can check and give directions I can add a PR for this according to your suggestions or better you can add this to godb.

Erkan.

Module moved: git.apache.org/thrift.git v0.12.0 go.mod

Hello,

This morning I found this issue when I tried to download the modules. Apparently, the dependency that your project is using (git.apache.org/thrift -> go.sum) has been moved to github.com. Could you please update the module file?

FYI: It has been moved to https://github.com/apache/thrift

Thanks in advance

Local cache, remote DB

Hi,

Could we think of some kind of cache driver that would be used as any other, but that would have some local copy of a DB in some SQLite. Of course, there would be some knobs to be able to configure when to consider a data stale and need to update the cache from the DB. But we should have some great default so no need to be knee deep in config to use it.
The big problem would be the consistency and sync, but someone looking for a solution like that would be aware of it.
Some system have limited connectivity, and having a reactive app even with stale data might be better than no answer/update at all.

For an extra integration, I could even think about some kind of integration with some REST-ful API transformed into local sqlite cache. Then a mobile app could always have some data in it without network. (this might be stupid and maybe a simple local HTTP cache could be smarter).
What do you think?

Request Feature - ToSQL on struct_insert.go

Gi. Can I request that the following is added to struct_insert.go

// ToSQL returns a string with the SQL statement (containing placeholders),
// the arguments slices, and an error.
func (si *StructInsert) ToSQL() (string, []interface{}, error) {
	a, b, c := si.insertStatement.ToSQL()
	return a, b, c
}