jet-mysql-repro.go

// To run this code, start create a MySQL DB named "jet-mysql-repro"
// and edit the setupDB variables to use the right connection settings.
package main

import (
  "fmt"
	"github.com/eaigner/jet"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"time"
)

var (
	Db jet.Db
)

const (
	// Time layout used to process DB time values.
	DbtimeLayout = "2006-01-02 15:04:05"
)

type User struct {
	Id             int
	Name           string
	Email          string
	Active         bool
	Created_at     time.Time
	Created_at_str string
}

func (u *User) Create() error {
	if u.Created_at.IsZero() {
		u.Created_at = time.Now()
	}
	err := Db.Query(`INSERT INTO users ( name, email, active, created_at, created_at_str ) VALUES ( ?, ?, ?, ?, ? )`,
		u.Name, u.Email, u.Active, u.Created_at, u.Created_at).Run()
	return err
}

func (u *User) IsZero() bool {
	return u.Id == 0
}

func (u *User) ParsedCreatedAt() (time.Time, error) {
	ts, err := time.Parse(DbtimeLayout, u.Created_at_str)
	if err != nil {
		return time.Time{}, err
	}
	return ts, err
}

func FindUserByEmail(email string) (*User, error) {
	var user User
	err := Db.Query(`select * from users WHERE email = ? limit 1`, email).Rows(&user)
	return &user, err
}

func setupDB() error {
	var err error
	username := "root"
	password := ""
	host := "127.0.0.1"
	port := 3306
	dbName := "jet-mysql-repro"
	dataSource := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8", username, password, host, port, dbName)
	Db, err = jet.Open("mysql", dataSource)
	if err != nil {
		return err
	}
	statement := `CREATE TABLE IF NOT EXISTS users (
  id int(11) NOT NULL AUTO_INCREMENT,
  created_at datetime DEFAULT NULL,
  created_at_str varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT "",
  email varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT "",
  active tinyint(1) DEFAULT 1,
  name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY index_users_on_email (email) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;`
	return Db.Query(statement).Run()
}

func main() {
	err := setupDB()
	if err != nil {
		log.Fatal(err)
	}

	// Delete all records
	err = Db.Query(`delete from users;`).Run()
	if err != nil {
		log.Fatal(err)
	}

	// create a record
	you := &User{Name: "John Doe", Email: "j.doe@i.net", Active: false}
	err = you.Create()
	if err != nil {
		log.Fatal(err)
	}

	// fetch a record
	record, err := FindUserByEmail("j.doe@i.net")
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("%v must be false (id)", record.IsZero())
	log.Printf("'%s' must == 'John Doe' (name)", record.Name)
	log.Printf("%s must == 'j.doe@i.net' (email)", record.Email)
	log.Printf("%v must be false (active)", record.Active)
	log.Printf("%v must be false (created_at)", record.Created_at.IsZero())
	ts, err := record.ParsedCreatedAt()
	if err != nil {
		log.Fatal(err)
	}
	log.Printf("%v must be false (parsed created_at string)", ts.IsZero())
}