import (
    _ "github.com/go-sql-driver/mysql" //mysql driver
    "github.com/ilibs/gosql/v2"
)

func main(){
    configs := make(map[string]*gosql.Config)

    configs["default"] = &gosql.Config{
        Enable:  true,
        Driver:  "mysql",
        Dsn:     "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Asia%2FShanghai",
        ShowSql: true,
    }

    //connection database
    gosql.Connect(configs)
    gosql.QueryRowx("select * from users where id = 1")
}

//Exec
gosql.Exec("insert into users(name,email,created_at,updated_at) value(?,?,?,?)","test","[email protected]",time.Now(),time.Now())

//Queryx
rows,err := gosql.Queryx("select * from users")
for rows.Next() {
    user := &Users{}
    err = rows.StructScan(user)
}
rows.Close()

//QueryRowx
user := &Users{}
err := gosql.QueryRowx("select * from users where id = ?",1).StructScan(user)

//Get
user := &Users{}
err := gosql.Get(user,"select * from users where id = ?",1)

//Select
users := make([]Users)
err := gosql.Select(&users,"select * from users")

//Change database
db := gosql.Use("test")
db.Queryx("select * from tests")

gosql.SetDefaultLink("log")
gosql.Connect(configs)

type Users struct {
	Id        int       `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Status    int       `db:"status"`
	CreatedAt time.Time `db:"created_at"`
	UpdatedAt time.Time `db:"updated_at"`
}

func (u *Users) TableName() string {
	return "users"
}

func (u *Users) PK() string {
	return "id"
}

//Get
user := &Users{}
gosql.Model(user).Where("id=?",1).Get()

//All
user := make([]Users,0)
gosql.Model(&user).All()

//Create and auto set CreatedAt
gosql.Model(&User{Name:"test",Email:"[email protected]"}).Create()

//Update
gosql.Model(&User{Name:"test2",Email:"[email protected]"}).Where("id=?",1).Update()
//If you need to update the zero value, you can do so
gosql.Model(&User{Status:0}).Where("id=?",1).Update("status")

//Delete
gosql.Model(&User{}).Where("id=?",1).Delete()

//Get where id = 1 and name = "test1"
user := &Users{Id:1,Name:"test1"}
gosql.Model(&user).Get()

//Update default use primary key as the condition
gosql.Model(&User{Id:1,Name:"test2"}).Update()
//Use custom conditions
//Builder => UPDATE users SET `id`=?,`name`=?,`updated_at`=? WHERE (status = ?)
gosql.Model(&User{Id:1,Name:"test2"}).Where("status = ?",1).Update()

//Delete
gosql.Model(&User{Id:1}).Delete()

user := &Users{Id:1,Status:0}
gosql.Model(&user).Get("status")

gosql.Tx(func(tx *gosql.DB) error {
    for id := 1; id < 10; id++ {
        user := &Users{
            Id:    id,
            Name:  "test" + strconv.Itoa(id),
            Email: "test" + strconv.Itoa(id) + "@test.com",
        }
		
		//v2 support, do some database operations in the transaction (use 'tx' from this point, not 'gosql')
        tx.Model(user).Create()

        if id == 8 {
            return errors.New("interrupt the transaction")
        }
    }

    //query with transaction
    var num int
    err := tx.QueryRowx("select count(*) from user_id = 1").Scan(&num)

    if err != nil {
        return err
    }

    return nil
})

tx, err := gosql.Begin()
if err != nil {
    return err
}

for id := 1; id < 10; id++ {
    _, err := tx.Exec("INSERT INTO users(id,name,status,created_at,updated_at) VALUES(?,?,?,?,?)", id, "test"+strconv.Itoa(id), 1, time.Now(), time.Now())
    if err != nil {
        return tx.Rollback()
    }
}

return tx.Commit()

//Create
gosql.Table("users").Create(map[string]interface{}{
    "id":         1,
    "name":       "test",
    "email":      "[email protected]",
    "created_at": "2018-07-11 11:58:21",
    "updated_at": "2018-07-11 11:58:21",
})

//Update
gosql.Table("users").Where("id = ?", 1).Update(map[string]interface{}{
    "name":  "fifsky",
    "email": "[email protected]",
})

//Delete
gosql.Table("users").Where("id = ?", 1).Delete()

//Count
gosql.Table("users").Where("id = ?", 1).Count()

//Change database
gosql.Use("db2").Table("users").Where("id = ?", 1).Count()

//Transaction `tx`
tx.Table("users").Where("id = ?", 1}).Count()

type Users struct {
	Id          int            `db:"id"`
	Name        string         `db:"name"`
	Email       string         `db:"email"`
	Status      int            `db:"status"`
	SuccessTime sql.NullString `db:"success_time" json:"success_time"`
	CreatedAt   time.Time      `db:"created_at" json:"created_at"`
	UpdatedAt   time.Time      `db:"updated_at" json:"updated_at"`
}

user := &Users{
    Id: 1,
    SuccessTime: sql.NullString{
        String: "2018-09-03 00:00:00",
        Valid:  false,
    }
}

err := gosql.Model(user).Get()

gosql.Table("users").Update(map[string]interface{}{
    "id":2,
    "count":gosql.Expr("count+?",1)
})
//Builder SQL
//UPDATE `users` SET `count`=count + ?,`id`=?; [1 2]

SELECT * FROM users WHERE level IN (?);

var levels = []int{4, 6, 7}
rows, err := gosql.Queryx("SELECT * FROM users WHERE level IN (?);", levels)

//or

user := make([]Users, 0)
err := gosql.Select(&user, "select * from users where id in(?)",[]int{1,2,3})

type MomentList struct {
	models.Moments
	User   *models.Users    `json:"user" db:"-" relation:"user_id,id"`         //one-to-one
	Photos []models.Photos `json:"photos" db:"-" relation:"id,moment_id" connection:"db2"`     //one-to-many
}

moment := &MomentList{}
err := gosql.Model(moment).Where("status = 1 and id = ?",14).Get()
//output User and Photos and you get the result

var moments = make([]MomentList, 0)
err := gosql.Model(&moments).Where("status = 1").Limit(10).All()
//You get the total result  for *UserMoment slice

moment := &MomentList{}
err := gosql.Relation("User" , func(b *gosql.Builder) {
    //this is builder instance,
    b.Where("gender = 0")
}).Get(moment , "select * from moments")

func (u *Users) BeforeCreate(ctx context.Context) (err error) {
  if u.IsValid() {
    err = errors.New("can't save invalid data")
  }
  return
}

func (u *Users) AfterCreate(ctx context.Context, tx *gosql.DB) (err error) {
  if u.Id == 1 {
    u.Email = ctx.Value("email")
    tx.Model(u).Update()
  }
  return
}