// Code generated by db2go. DO NOT EDIT.
// https://github.com/civet148/sqlca

package models

import "github.com/civet148/sqlca/v2"

const TableNameUser = "user" 

const (
	USER_COLUMN_ID           = "id"
	USER_COLUMN_NAME         = "name"
	USER_COLUMN_PHONE        = "phone"
	USER_COLUMN_SEX          = "sex"
	USER_COLUMN_EMAIL        = "email"
	USER_COLUMN_DISABLE      = "disable"
	USER_COLUMN_BALANCE      = "balance"
	USER_COLUMN_SEX_NAME     = "sex_name"
	USER_COLUMN_DATA_SIZE    = "data_size"
	USER_COLUMN_EXTRA_DATA   = "extra_data"
	USER_COLUMN_CREATED_TIME = "created_time"
	USER_COLUMN_UPDATED_TIME = "updated_time"
)

type UserDO struct {
	Id          uint64        `json:"id" db:"id" bson:"_id"`                                               //auto inc id
	Name        string        `json:"name" db:"name" bson:"name"`                                          //user name
	Phone       string        `json:"phone" db:"phone" bson:"phone"`                                       //phone number
	Sex         uint8         `json:"sex" db:"sex" bson:"sex"`                                             //user sex
	Email       string        `json:"email" db:"email" bson:"email"`                                       //email
	Disable     int8          `json:"disable" db:"disable" bson:"disable"`                                 //disabled(0=false 1=true)
	Balance     sqlca.Decimal `json:"balance" db:"balance" bson:"balance"`                                 //balance of decimal
	SexName     string        `json:"sex_name" db:"sex_name" bson:"sex_name"`                              //sex name
	DataSize    int64         `json:"data_size" db:"data_size" bson:"data_size"`                           //data size
	ExtraData   UserExtra     `json:"extra_data" db:"extra_data" sqlca:"isnull" bson:"extra_data"`         //extra data (json)
	CreatedTime string        `json:"created_time" db:"created_time" sqlca:"readonly" bson:"created_time"` //created time
	UpdatedTime string        `json:"updated_time" db:"updated_time" sqlca:"readonly" bson:"updated_time"` //updated time
}

type UserExtra struct {
	HomeAddress string `json:"home_address"`
}

package main

import (
    "github.com/civet148/log"
    "github.com/civet148/sqlca-bench/models"
    "github.com/civet148/sqlca/v2"
)

const (
    MysqlDSN = "mysql://root:[email protected]:3306/sqlca-db?charset=utf8mb4"
)

var db *sqlca.Engine

func main() {
    var err error
    db, err = sqlca.NewEngine(MysqlDSN)
    if err != nil {
        log.Panic(err.Error())
    }
    //db.Debug(true) //open debug mode
}

func QueryById(db *sqlca.Engine) (err error) {
    var rows int64
    var user *models.UserDO
    
    //SELECT * FROM user WHERE id=1
    var userId = 1
    rows, err = db.Model(&user).
                    Table(models.TableNameUser).
                    Id(userId).
                    Query()
    if err != nil {
        return log.Errorf(err.Error())
    }
    if rows == 0 || user.Id == 0 {
        return log.Errorf("query user by id %v not found", userId)
    }
    log.Infof("[SELECT * FROM user WHERE id=1] query result: [%+v]", user)
    return nil
}

func QueryByPage(db *sqlca.Engine) (err error) {
	var rows, total int64
	var users []*models.UserDO

	//SELECT id, name, sex_name, balance, created_time, updated_time FROM user LIMIT 0, 100
	rows, total, err = db.Model(&users).
		Table(models.TableNameUser).
		Select(models.USER_COLUMN_ID, models.USER_COLUMN_NAME, models.USER_COLUMN_SEX_NAME, models.USER_COLUMN_CREATED_TIME, models.USER_COLUMN_UPDATED_TIME, models.USER_COLUMN_BALANCE).
		Page(0, 100).
		QueryEx()
	if err != nil {
		return log.Errorf(err.Error())
	}
	log.Infof("[SELECT id, name, sex_name, balance, created_time, updated_time FROM user LIMIT 0, 100] query result: rows [%v] total [%v]", rows, total)
	return nil
}

func QueryByConditions(db *sqlca.Engine, id int64, name, createdTime string) (err error) {
    var rows int64
    var users []*models.UserDO
    
    //SELECT id, name, sex_name, balance, created_time, updated_time FROM user WHERE id = ? AND name = ? AND created_time >= ?
    e := db.Model(&users).
            Table(models.TableNameUser).
            Select(models.USER_COLUMN_ID, models.USER_COLUMN_NAME, models.USER_COLUMN_SEX_NAME, models.USER_COLUMN_CREATED_TIME, models.USER_COLUMN_UPDATED_TIME, models.USER_COLUMN_BALANCE)
    if id != 0 {
        e.Eq(models.USER_COLUMN_ID, id)
    }
    if name != "" {
        e.Eq(models.USER_COLUMN_NAME, name)
    }
    if createdTime != "" {
        e.Gte(models.USER_COLUMN_CREATED_TIME, createdTime)
    }
    if rows, err = e.Query(); err != nil {
        return log.Errorf(err.Error())
    }
    log.Infof("[SELECT id, name, sex_name, balance, created_time, updated_time FROM user  WHERE id = ? AND name = ? AND created_time >= ?] query result: rows [%v] users [%+v]", rows, users)
    return nil
}

func Insert(db *sqlca.Engine) (err error) {
	//INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356')
	var user = &models.UserDO{
		Name:    "john",
		Phone:   "+0014155787342",
		Balance: sqlca.NewDecimal("12423.32356"),
		SexName: "male",
	}
	var id int64
	id, err = db.Model(&user).Table(models.TableNameUser).Insert()
	if err != nil {
		return log.Errorf(err.Error())
	}
	log.Infof("[INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356')] result: last insert id [%v]", id)
	return nil
}

func InsertBatch(db *sqlca.Engine) (err error) {
	//INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356'),('rose', '+0014155787343', 'female', '423.006')
	var users = []*models.UserDO{
		{
			Name:    "john",
			Phone:   "+0014155787342",
			Balance: sqlca.NewDecimal("12423.32356"),
			SexName: "male",
		},
		{
			Name:    "rose",
			Phone:   "+0014155787343",
			Balance: sqlca.NewDecimal("423.006"),
			SexName: "female",
		},
	}
	_, err = db.Model(&users).Table(models.TableNameUser).Insert()
	if err != nil {
		return log.Errorf(err.Error())
	}
	log.Infof("[INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356'),('rose', '+0014155787343', 'female', '423.006')] successful")
	return nil
}