1package db
  2
  3import (
  4	"database/sql"
  5	"fmt"
  6	"math/big"
  7	"reflect"
  8	"strings"
  9
 10	"github.com/gobuffalo/packr/v2"
 11	"github.com/hermeznetwork/hermez-node/log"
 12	"github.com/hermeznetwork/tracerr"
 13	"github.com/jmoiron/sqlx"
 14	migrate "github.com/rubenv/sql-migrate"
 15	"github.com/russross/meddler"
 16)
 17
 18var migrations *migrate.PackrMigrationSource
 19
 20func init() {
 21	migrations = &migrate.PackrMigrationSource{
 22		Box: packr.New("hermez-db-migrations", "./migrations"),
 23	}
 24	ms, err := migrations.FindMigrations()
 25	if err != nil {
 26		panic(err)
 27	}
 28	if len(ms) == 0 {
 29		panic(fmt.Errorf("no SQL migrations found"))
 30	}
 31}
 32
 33// MigrationsUp runs the SQL migrations Up
 34func MigrationsUp(db *sql.DB) error {
 35	nMigrations, err := migrate.Exec(db, "postgres", migrations, migrate.Up)
 36	if err != nil {
 37		return tracerr.Wrap(err)
 38	}
 39	log.Info("successfully ran ", nMigrations, " migrations Up")
 40	return nil
 41}
 42
 43// MigrationsDown runs the SQL migrations Down
 44func MigrationsDown(db *sql.DB) error {
 45	nMigrations, err := migrate.Exec(db, "postgres", migrations, migrate.Down)
 46	if err != nil {
 47		return tracerr.Wrap(err)
 48	}
 49	log.Info("successfully ran ", nMigrations, " migrations Down")
 50	return nil
 51}
 52
 53// ConnectSQLDB connects to the SQL DB
 54func ConnectSQLDB(port int, host, user, password, name string) (*sqlx.DB, error) {
 55	// Init meddler
 56	initMeddler()
 57	meddler.Default = meddler.PostgreSQL
 58	// Stablish connection
 59	psqlconn := fmt.Sprintf(
 60		"host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
 61		host,
 62		port,
 63		user,
 64		password,
 65		name,
 66	)
 67	db, err := sqlx.Connect("postgres", psqlconn)
 68	if err != nil {
 69		return nil, tracerr.Wrap(err)
 70	}
 71	return db, nil
 72}
 73
 74// InitSQLDB runs migrations and registers meddlers
 75func InitSQLDB(port int, host, user, password, name string) (*sqlx.DB, error) {
 76	db, err := ConnectSQLDB(port, host, user, password, name)
 77	if err != nil {
 78		return nil, tracerr.Wrap(err)
 79	}
 80	// Run DB migrations
 81	if err := MigrationsUp(db.DB); err != nil {
 82		return nil, tracerr.Wrap(err)
 83	}
 84	return db, nil
 85}
 86
 87// initMeddler registers tags to be used to read/write from SQL DBs using meddler
 88func initMeddler() {
 89	meddler.Register("bigint", BigIntMeddler{})
 90	meddler.Register("bigintnull", BigIntNullMeddler{})
 91}
 92
 93// BulkInsert performs a bulk insert with a single statement into the specified table.  Example:
 94// `db.BulkInsert(myDB, "INSERT INTO block (eth_block_num, timestamp, hash) VALUES %s", blocks[:])`
 95// Note that all the columns must be specified in the query, and they must be
 96// in the same order as in the table.
 97// Note that the fields in the structs need to be defined in the same order as
 98// in the table columns.
 99func BulkInsert(db meddler.DB, q string, args interface{}) error {
100	arrayValue := reflect.ValueOf(args)
101	arrayLen := arrayValue.Len()
102	valueStrings := make([]string, 0, arrayLen)
103	var arglist = make([]interface{}, 0)
104	for i := 0; i < arrayLen; i++ {
105		arg := arrayValue.Index(i).Addr().Interface()
106		elemArglist, err := meddler.Default.Values(arg, true)
107		if err != nil {
108			return tracerr.Wrap(err)
109		}
110		arglist = append(arglist, elemArglist...)
111		value := "("
112		for j := 0; j < len(elemArglist); j++ {
113			value += fmt.Sprintf("$%d, ", i*len(elemArglist)+j+1)
114		}
115		value = value[:len(value)-2] + ")"
116		valueStrings = append(valueStrings, value)
117	}
118	stmt := fmt.Sprintf(q, strings.Join(valueStrings, ","))
119	_, err := db.Exec(stmt, arglist...)
120	return tracerr.Wrap(err)
121}
122
123// BigIntMeddler encodes or decodes the field value to or from JSON
124type BigIntMeddler struct{}
125
126// PreRead is called before a Scan operation for fields that have the BigIntMeddler
127func (b BigIntMeddler) PreRead(fieldAddr interface{}) (scanTarget interface{}, err error) {
128	// give a pointer to a byte buffer to grab the raw data
129	return new(string), nil
130}
131
132// PostRead is called after a Scan operation for fields that have the BigIntMeddler
133func (b BigIntMeddler) PostRead(fieldPtr, scanTarget interface{}) error {
134	ptr := scanTarget.(*string)
135	if ptr == nil {
136		return tracerr.Wrap(fmt.Errorf("BigIntMeddler.PostRead: nil pointer"))
137	}
138	field := fieldPtr.(**big.Int)
139	*field = new(big.Int).SetBytes([]byte(*ptr))
140	return nil
141}
142
143// PreWrite is called before an Insert or Update operation for fields that have the BigIntMeddler
144func (b BigIntMeddler) PreWrite(fieldPtr interface{}) (saveValue interface{}, err error) {
145	field := fieldPtr.(*big.Int)
146
147	return field.Bytes(), nil
148}
149
150// BigIntNullMeddler encodes or decodes the field value to or from JSON
151type BigIntNullMeddler struct{}
152
153// PreRead is called before a Scan operation for fields that have the BigIntNullMeddler
154func (b BigIntNullMeddler) PreRead(fieldAddr interface{}) (scanTarget interface{}, err error) {
155	return &fieldAddr, nil
156}
157
158// PostRead is called after a Scan operation for fields that have the BigIntNullMeddler
159func (b BigIntNullMeddler) PostRead(fieldPtr, scanTarget interface{}) error {
160	field := fieldPtr.(**big.Int)
161	ptrPtr := scanTarget.(*interface{})
162	if *ptrPtr == nil {
163		// null column, so set target to be zero value
164		*field = nil
165		return nil
166	}
167	// not null
168	ptr := (*ptrPtr).([]byte)
169	if ptr == nil {
170		return tracerr.Wrap(fmt.Errorf("BigIntMeddler.PostRead: nil pointer"))
171	}
172	*field = new(big.Int).SetBytes(ptr)
173	return nil
174}
175
176// PreWrite is called before an Insert or Update operation for fields that have the BigIntNullMeddler
177func (b BigIntNullMeddler) PreWrite(fieldPtr interface{}) (saveValue interface{}, err error) {
178	field := fieldPtr.(*big.Int)
179	if field == nil {
180		return nil, nil
181	}
182	return field.Bytes(), nil
183}
184
185// SliceToSlicePtrs converts any []Foo to []*Foo
186func SliceToSlicePtrs(slice interface{}) interface{} {
187	v := reflect.ValueOf(slice)
188	vLen := v.Len()
189	typ := v.Type().Elem()
190	res := reflect.MakeSlice(reflect.SliceOf(reflect.PtrTo(typ)), vLen, vLen)
191	for i := 0; i < vLen; i++ {
192		res.Index(i).Set(v.Index(i).Addr())
193	}
194	return res.Interface()
195}
196
197// SlicePtrsToSlice converts any []*Foo to []Foo
198func SlicePtrsToSlice(slice interface{}) interface{} {
199	v := reflect.ValueOf(slice)
200	vLen := v.Len()
201	typ := v.Type().Elem().Elem()
202	res := reflect.MakeSlice(reflect.SliceOf(typ), vLen, vLen)
203	for i := 0; i < vLen; i++ {
204		res.Index(i).Set(v.Index(i).Elem())
205	}
206	return res.Interface()
207}
208
209// Rollback an sql transaction, and log the error if it's not nil
210func Rollback(txn *sqlx.Tx) {
211	if err := txn.Rollback(); err != nil {
212		log.Errorw("Rollback", "err", err)
213	}
214}
215
216// RowsClose close the rows of an sql query, and log the errir if it's not nil
217func RowsClose(rows *sql.Rows) {
218	if err := rows.Close(); err != nil {
219		log.Errorw("rows.Close", "err", err)
220	}
221}