Using Sqac

package main

import (
  "flag"
  "fmt"

  "github.com/1414C/sqac"
  _ "github.com/SAP/go-hdb/driver"
  _ "github.com/denisenkom/go-mssqldb"
  _ "github.com/go-sql-driver/mysql"
  _ "github.com/lib/pq"
  _ "github.com/mattn/go-sqlite3"
)

func main() {

  dbFlag := flag.String("db", "sqlite", "db-type for connection")
  csFlag := flag.String("cs", "testdb.sqlite", "connection-string for the database")
  logFlag := flag.Bool("l", false, "activate sqac detail logging to stdout")
  dbLogFlag := flag.Bool("dbl", false, "activate DDL/DML logging to stdout)")
  flag.Parse()

  // This will be the central access-point to the ORM and should be made
  // available in all locations where access to the persistent storage
  // (database) is required.
  var (
    Handle sqac.PublicDB
  )

  // Declare a struct to be used as the source for table creation.
  type Depot struct {
    DepotNum   int       `db:"depot_num" sqac:"primary_key:inc"`
    CreateDate time.Time `db:"create_date" sqac:"nullable:false;default:now();"`
    Region     string    `db:"region" sqac:"nullable:false;default:YYC"`
    Province   string    `db:"province" sqac:"nullable:false;default:AB"`
    Country    string    `db:"country" sqac:"nullable:false;default:CA"`
    Population int       `db:"population" sqac:"nullable:false;default:0;index:non-unique"`
  }

  // Create a PublicDB instance.  Check the sqac.Create function, as the return parameter contains
  // not only an implementation of PublicDB targeting the db-type/db, but also a pointer
  // facilitating access to the db via jmoiron's sqlx package.  This is useful if you wish
  // to access the sql/sqlx APIs directly.
  Handle = sqac.Create(*dbFlag, *logFlag, *dbLogFlag, *cs)

  // Execute a call to get the name of the db-driver being used.  At this point, any method
  // contained in the sqac.PublicDB interface may be called.
  driverName := Handle.GetDBDriverName()
  fmt.Println("driverName:", driverName)

  // Create a new table in the database
  err := Handle.CreateTables(Depot{})
  if err != nil {
    t.Errorf("%s", err.Error())
  }

  // Determine the table name as per the table creation logic
  tn := common.GetTableName(Depot{})

  // Expect that table depot exists
  if !Handle.ExistsTable(tn) {
    t.Errorf("table %s was not created", tn)
  }

  // prepare data for insertion into the db-table
  var newDepot = Depot{
      // DepotNum:         1234,      // not needed - autoinc primary key
      // CreateDate:           ,      // not needed - default:now()
      Region:              "YVR",
      Province:            "BC",
      Country:             "CA",
      Population:          2500000,
  }

  // create a new record via the CRUD Create call
  err = Handle.Create(&newDepot)
  if err != nil {
    t.Errorf(err.Error())
  }

  // Drop the table
  err = Handle.DropTables(Depot{})
  if err != nil {
    t.Errorf("table %s was not dropped", tn)
  }

  // Close the connection.
  Handle.Close()
}

go run -db sqlite -cs testdb.sqlite main.go

newDepot.Region = "YYC"           // "YVR"   -> "YYC"
newDepot.Province = "AB"          // "BC"    -> "AB"
newDepot.Population = 1000000     // 2500000 -> 1000000
err = Handle.Update(&newDepot)
if err != nil {
    t.Errorf(err.Error())
}

// create a struct to read into and populate the keys
depotRead := Depot{
    DepotNum: newDepot.DepotNum,
}

err = Handle.GetEntity(&depotRead)
if err != nil {
    t.Errorf("%s", err.Error())
}

if depotRead.Region != "YYC" || depotRead.Region != "YVR" {
    t.Errorf("depotRead.Region error!")
}

err = Handle.Delete(&newDepot)
if err != nil {
    t.Errorf("%s", err.Error())
}