rx

package main

import (
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	type Books struct {
		Title  string
		Author string
		Body   string
		ID     uint32
		// ...
	}

	b := Books{Title: `Нова земя`, Author: `Иванъ Вазовъ`, Body: `По стръмната южна урва на Амбарица...`}
	columns := rx.NewRx(b).Columns()
	fmt.Printf("Columns: %+v\n", columns)

}

Output:
Columns: [title author body id]

package main

import (
	"database/sql"
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	type Users struct {
		LoginName string
		GroupID   sql.NullInt64
		ChangedBy sql.NullInt64
		ID        int32 `rx:"id,auto"`
	}
	// []Users to be inserted (or updated, (LoginName is UNIQUE)).
	var users = []Users{
		Users{LoginName: "first", ChangedBy: sql.NullInt64{1, true}},
		Users{LoginName: "the_second", ChangedBy: sql.NullInt64{1, true}},
	}
	// Type parameter is guessed from the type of the parameters.
	m := rx.NewRx(users...)
	for _, u := range m.Data() {
		fmt.Printf("User.LoginName: %s, User.ChangedBy.Int64: %d\n", u.LoginName, u.ChangedBy.Int64)
	}
}

Output:
User.LoginName: first, User.ChangedBy.Int64: 1
User.LoginName: the_second, User.ChangedBy.Int64: 1

package main

import (
	"database/sql"
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

type Users struct {
	LoginName string
	Passwword string `rx:"password"`
	GroupID   sql.NullInt64
	ChangedBY sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

func main() {
	// A long time ago in a galaxy far, far away....
	// m := rx.NewRx(users...)
	// ...
	// r, e := m.Insert()
	// fmt.Printf("sql.Result:%#v; Error:%#v;", r, e)
	// ...
	// d, e := rx.NewRx[Users]().Select(`id>0`, nil)
	// fmt.Printf("%+v; e:%+v", d, e)
	// ...
	// Now
	bindVars := struct{ ID int32 }{ID: 4}
	u, err := rx.NewRx[Users]().Get(`id=:id`, bindVars)
	if err == nil {
		fmt.Println(u.LoginName)

		return
	}
	fmt.Printf("err: %s\n", err)
}

Output:
fourth

package main

import (
	"database/sql"
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

type Users struct {
	LoginName string
	Passwword string `rx:"password"`
	GroupID   sql.NullInt64
	ChangedBY sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

var users = []Users{
	Users{LoginName: "first", ChangedBY: sql.NullInt64{0, false}, Passwword: `a`},
	Users{LoginName: "the_second", ChangedBY: sql.NullInt64{1, true}, Passwword: `b`},
	Users{LoginName: "the_third", ChangedBY: sql.NullInt64{1, true}, Passwword: `c`},
}

type Groups struct {
	Name      string
	ChangedBy sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

func main() {
	_, e := rx.NewRx(users...).Insert()
	if e != nil {
		println(`Error inserting new users:`, e)
	}
	// users, e := rx.NewRx[Users]().Select(`id>=0`, nil)
	// fmt.Printf("Selected []Users %+v; %+v\n", users, e)
	groupRs, e := rx.NewRx[Groups](Groups{Name: `fifth`}).Insert()
	if e != nil {
		println(`Error inserting new group:`, e.Error())
	}
	lastGroupID, _ := groupRs.LastInsertId()
	fmt.Printf("Inserted new group with id: %d\n", lastGroupID)

	usrs := []Users{
		Users{LoginName: `fourth`, GroupID: sql.NullInt64{Int64: 4, Valid: true}, Passwword: `qwe4`},
		Users{LoginName: `fifth`, GroupID: sql.NullInt64{Int64: 5, Valid: true}, Passwword: `5th`},
	}
	r, err := rx.NewRx(usrs...).Insert()

	if err == nil {
		last, _ := r.LastInsertId()
		fmt.Println(`Last inserted user id:`, last)

		return
	}
	fmt.Printf("err: %s", err)
}

Output:
Inserted new group with id: 5
Last inserted user id: 5

package main

import (
	"database/sql"
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

type Users struct {
	LoginName string
	Passwword string `rx:"password"`
	GroupID   sql.NullInt64
	ChangedBY sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

func main() {
	bind := struct{ IDs []uint }{IDs: []uint{4, 5}}
	u := rx.NewRx[Users]()
	data, err := u.Select(`id IN(:ids) ORDER BY id DESC`, bind)
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Println(`Last two records in descending order:`)
	for _, u := range data {
		fmt.Printf("%d: %s\n", u.ID, u.LoginName)
	}

	// We can reuse the *Rx object for this parameter type for many and
	// different SQL queries.
	fmt.Println("\nUp to DefaultLimit records with OFFSET 0 in the default order:")
	data, err = u.Select(``, nil)
	if err != nil {
		fmt.Println(err.Error())
	}
	for _, u := range data {
		fmt.Printf("%d: %s\n", u.ID, u.LoginName)
	}
}

Output:
Last two records in descending order:
5: fifth
4: fourth

Up to DefaultLimit records with OFFSET 0 in the default order:
0: superadmin
1: first
2: the_second
3: the_third
4: fourth
5: fifth

package main

import (
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

type UserGroup struct {
	rx.Rx[UserGroup]
	UserID  int64
	GroupID int64

	Where whereParams `rx:"where,-"`
}
type whereParams struct{ GroupID int64 }

func main() {
	ugDataIns := []UserGroup{
		UserGroup{UserID: 1, GroupID: 1},
		UserGroup{UserID: 2, GroupID: 2},
		UserGroup{UserID: 3, GroupID: 3},
		UserGroup{UserID: 1, GroupID: 4},
		UserGroup{UserID: 2, GroupID: 4},
	}
	ug := rx.NewRx[UserGroup]().SetData(ugDataIns)
	for i, row := range ug.Data() {
		fmt.Printf("%d: UserID: %d; GroupID: %d\n", i+1, row.UserID, row.GroupID)
	}
}

Output:

1: UserID: 1; GroupID: 1
2: UserID: 2; GroupID: 2
3: UserID: 3; GroupID: 3
4: UserID: 1; GroupID: 4
5: UserID: 2; GroupID: 4

package main

import (
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	type WishYouWereHere struct {
		SongName string
		ID       uint32
	}
	f := WishYouWereHere{SongName: `Shine On You Crazy Diamond`}
	fmt.Printf("TableName: %s\n", rx.NewRx(f).Table())

}

Output:
TableName: wish_you_were_here

package main

import (
	"database/sql"
	"fmt"
	"strings"

	"github.com/jmoiron/sqlx"
	"github.com/labstack/gommon/log"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

var schema = `
PRAGMA foreign_keys = OFF;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
login_name varchar(100) UNIQUE,
password varchar(64) NOT NULL UNIQUE,
group_id INTEGER DEFAULT NULL REFERENCES groups(id),
changed_by INTEGER DEFAULT NULL REFERENCES users(id) ON DELETE SET DEFAULT);

INSERT INTO users(id,group_id,changed_by,login_name,password) VALUES (0,0,0,'superadmin','qwerty');

CREATE TABLE groups (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) UNIQUE NOT NULL,
changed_by INTEGER DEFAULT NULL REFERENCES users(id) ON DELETE SET DEFAULT);

INSERT INTO groups(id,name, changed_by) VALUES (0,'superadmin',0);
INSERT INTO groups(id,name, changed_by) VALUES (1,'admins',NULL);
INSERT INTO groups(id,name, changed_by) VALUES (2,'guests',NULL);
INSERT INTO groups(id,name, changed_by) VALUES (3,'editors',NULL);
INSERT INTO groups(id,name, changed_by) VALUES (4,'commenters',NULL);
CREATE TABLE user_group (
--  'ID of the user belonging to the group with group_id.'
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
--  'ID of the group to which the user with user_id belongs.'
  group_id INTEGER REFERENCES groups(id) ON DELETE CASCADE,
  PRIMARY KEY(user_id, group_id)
);
CREATE TABLE foo(
	bar INTEGER PRIMARY KEY AUTOINCREMENT,
	description VARCHAR(255) NOT NULL DEFAULT '',
	id VARCHAR(56) UNIQUE NOT NULL DEFAULT ''
);
PRAGMA foreign_keys = ON;
`

type Users struct {
	LoginName string
	Passwword string `rx:"password"`
	GroupID   sql.NullInt64
	ChangedBY sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

func NewUsers(u ...Users) rx.SqlxModel[Users] {
	return rx.NewRx[Users](u...)
}

type Groups struct {
	Name      string
	ChangedBy sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

// Stollen from sqlx_test.go.
func multiExec(e sqlx.Execer, query string) {
	stmts := strings.Split(query, ";\n")
	if len(strings.Trim(stmts[len(stmts)-1], " \n\t\r")) == 0 {
		stmts = stmts[:len(stmts)-1]
	}
	for _, s := range stmts {
		_, err := e.Exec(s)
		if err != nil {
			fmt.Println(err, s)
		}
	}
}

func init() {
	rx.Logger.SetLevel(log.WARN)
	multiExec(rx.DB(), schema)
}

type UserGroup struct {
	rx.Rx[UserGroup]
	UserID  int64
	GroupID int64

	Where whereParams `rx:"where,-"`
}
type whereParams struct{ GroupID int64 }

func main() {
	superAdmin := NewUsers()
	tx := superAdmin.Tx() // A new transaction just begun.
	defer func() { _ = tx.Rollback() }()
	admin, _ := superAdmin.Get(`login_name='superadmin'`)
	adminGroup := rx.NewRx(Groups{
		Name:      `MoreAdmins`,
		ChangedBy: sql.NullInt64{Int64: admin.ID, Valid: true}},
	).WithTx(tx)
	res, err := adminGroup.Insert()
	if err != nil {
		fmt.Println("adminGroup.Insert Error:", err.Error())
		return
	}
	gID, _ := res.LastInsertId()
	rx.NewRx(UserGroup{UserID: admin.ID, GroupID: gID}).WithTx(tx).Insert()
	if err := tx.Commit(); err != nil {
		fmt.Println(`tx.Commit Error`, err.Error())
		return
	}
	gr, _ := rx.NewRx[Groups]().Get(`id=:id`, rx.Map{`id`: gID})
	fmt.Printf(`new Group: %q`, gr.Name)
}

Output:
new Group: "MoreAdmins"

package main

import (
	"fmt"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	type whereBind struct{ GroupID uint32 }
	type UserGroup struct {
		rx.Rx[UserGroup]
		UserID  uint32
		GroupID uint32
		// Used only as bind parameters during UPDATE and maybe in other
		// queries. Must be a named struct, known at compile time!
		Where whereBind `rx:"where,-"` // - : Do not treat this field as column.
	}
	// rx.Rx can be embedded and used from within your record structure or
	// specialized type.
	ug := new(UserGroup)
	ugData := []UserGroup{
		UserGroup{UserID: 4, GroupID: 4},
		UserGroup{UserID: 5, GroupID: 5},
	}
	ug.SetData(ugData)
	_, e := ug.Insert()
	if e != nil {
		fmt.Println("Error inserting into user_group:", e.Error())
	}

	// Update one or many rows - move some user(5) to another group(4).
	ugDataUpd := []UserGroup{
		UserGroup{
			UserID: 5,
			// new value (to be updated in the database). Current value: 5
			GroupID: 4,
			Where: whereBind{
				// existing in the database value: 5
				GroupID: 5,
			},
		},
	}
	ug.SetData(ugDataUpd)
	//                    columns to be set                             the Where.GroupID field
	rs, err := ug.Update([]string{`group_id`}, `user_id=:user_id AND group_id=:where.group_id`)
	if err != nil {
		fmt.Println(err.Error())
	}
	affected, _ := rs.RowsAffected()
	fmt.Printf("RowsAffected: %d; err: %+v", affected, err)

}

Output:
RowsAffected: 1; err: <nil>

package main

import (
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"fmt"
	"strings"

	"github.com/jmoiron/sqlx"
	"github.com/labstack/gommon/log"

	"github.com/kberov/rowx/rx"
	_ "github.com/mattn/go-sqlite3"
)

var schema = `
PRAGMA foreign_keys = OFF;
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
login_name varchar(100) UNIQUE,
password varchar(64) NOT NULL UNIQUE,
group_id INTEGER DEFAULT NULL REFERENCES groups(id),
changed_by INTEGER DEFAULT NULL REFERENCES users(id) ON DELETE SET DEFAULT);

INSERT INTO users(id,group_id,changed_by,login_name,password) VALUES (0,0,0,'superadmin','qwerty');

CREATE TABLE groups (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) UNIQUE NOT NULL,
changed_by INTEGER DEFAULT NULL REFERENCES users(id) ON DELETE SET DEFAULT);

INSERT INTO groups(id,name, changed_by) VALUES (0,'superadmin',0);
INSERT INTO groups(id,name, changed_by) VALUES (1,'admins',NULL);
INSERT INTO groups(id,name, changed_by) VALUES (2,'guests',NULL);
INSERT INTO groups(id,name, changed_by) VALUES (3,'editors',NULL);
INSERT INTO groups(id,name, changed_by) VALUES (4,'commenters',NULL);
CREATE TABLE user_group (
--  'ID of the user belonging to the group with group_id.'
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
--  'ID of the group to which the user with user_id belongs.'
  group_id INTEGER REFERENCES groups(id) ON DELETE CASCADE,
  PRIMARY KEY(user_id, group_id)
);
CREATE TABLE foo(
	bar INTEGER PRIMARY KEY AUTOINCREMENT,
	description VARCHAR(255) NOT NULL DEFAULT '',
	id VARCHAR(56) UNIQUE NOT NULL DEFAULT ''
);
PRAGMA foreign_keys = ON;
`

type Users struct {
	LoginName string
	Passwword string `rx:"password"`
	GroupID   sql.NullInt64
	ChangedBY sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

type Groups struct {
	Name      string
	ChangedBy sql.NullInt64
	ID        int64 `rx:"id,auto"`
}

// Stollen from sqlx_test.go.
func multiExec(e sqlx.Execer, query string) {
	stmts := strings.Split(query, ";\n")
	if len(strings.Trim(stmts[len(stmts)-1], " \n\t\r")) == 0 {
		stmts = stmts[:len(stmts)-1]
	}
	for _, s := range stmts {
		_, err := e.Exec(s)
		if err != nil {
			fmt.Println(err, s)
		}
	}
}

func init() {
	rx.Logger.SetLevel(log.WARN)
	multiExec(rx.DB(), schema)
}

type UserGroup struct {
	rx.Rx[UserGroup]
	UserID  int64
	GroupID int64

	Where whereParams `rx:"where,-"`
}
type whereParams struct{ GroupID int64 }

func main() {
	superAdmin, _ := rx.NewRx[Users]().Get(`login_name='superadmin'`)
	superID := superAdmin.ID
	uname := `kberov`
	pswd := `123qwerty!`
	// This is how we usually begin a transaction!
	// We could have also started one automatically with tx := group.Tx().
	group := rx.NewRx(Groups{Name: uname}).WithTx(rx.DB().MustBegin())
	// The rollback will be ignored if tx has been committed already.
	defer func() { _ = group.Tx().Rollback() }()
	res, err := group.Insert()
	if err != nil {
		fmt.Println("group.Insert() Error:", err.Error())
	}
	groupID, err := res.LastInsertId()
	if err != nil {
		fmt.Println("group.LastInsertId Error:", err.Error())
	}
	passwd := hashPasswordWithSaltAndIterations(pswd, uname, groupID)
	user := rx.NewRx(Users{
		LoginName: `kberov`,
		Passwword: passwd,
		GroupID:   sql.NullInt64{groupID, true},
		ChangedBY: sql.NullInt64{superID, true},
		// Using the same transaction!
	}).WithTx(group.Tx())
	res, err = user.Insert()
	if err != nil {
		fmt.Println("user.Insert Error:", err.Error())
	}
	userID, err := res.LastInsertId()
	if err != nil {
		fmt.Println("user.LastInsertId Error:", err.Error())
	}
	res, err = rx.NewRx(UserGroup{
		UserID:  userID,
		GroupID: groupID,
		// Using the same transaction!
	}).WithTx(group.Tx()).Insert()
	if err != nil {
		fmt.Println("UserGroup.Insert Error:", err.Error())
	}
	// Commit the transaction. It is the same started with group.WithTx(...)
	if err = user.Tx().Commit(); err != nil {
		fmt.Println("Commit Error:", err.Error())
	}
	// Not using any transaction.
	if kberov, err := rx.NewRx[Users]().Get(`login_name='kberov'`); err == nil {
		fmt.Println(`Passwword:`, kberov.Passwword[:6])
	} else {
		fmt.Println("Users.Get Error:", err.Error())
	}
}

func hashPasswordWithSaltAndIterations(password, salt string, iterations int64) string {
	hash := salt + password
	for range iterations {
		hasher := sha256.New()
		hasher.Write([]byte(hash))
		hash = hex.EncodeToString(hasher.Sum(nil))
	}
	return hash
}

Output:
Passwword: 7fc19e