samonzeweb / godb Goto Github PK
View Code? Open in Web Editor NEWA Go SQL query builder and struct mapper.
License: MIT License
A Go SQL query builder and struct mapper.
License: MIT License
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
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.
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.
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?
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
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
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.
Hello, Could you please create a new release with the go.mod fixes?
Thank you! :)
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
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)
}
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.
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
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
}
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.
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.
does godb support join query
please show some example
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*
).
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?
This Go tools dominikh/go-tools and especially staticcheck
yielded some interesting results on godb.
Hi Sam,
I've added Map type.
DB type: VARCHAR,CHAR,TEXT
GO type: map[string]interface{}
Please review #31, thanks.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.