CreateTables

The CreateTables method is used to create one or more tables in the target database. CreateTables accepts one or more go struct definitions that have been annotated with “sqac:” tags and uses the provided information to create new tables in the database.

Errors encountered during table creation are returned to the caller immediately. This may result in the incomplete processing of the tables contained in parameter i …interface{}.

CreateTables Example

  // 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();"`
    Country    string    `db:"country" sqac:"nullable:false;default:CA"`
    Province   string    `db:"province" sqac:"nullable:false;default:AB"`
    Region     string    `db:"region" sqac:"nullable:false;default:YYC"`
    Population int       `db:"population" sqac:"nullable:false;default:0;index:non-unique"`
  }

The struct shown above can be used by sqac to create a table named “depot” in the target database. In order to call the PublicDB.CreateTables() method, an instance of PublicDB must be created and connected to the database. A small sample program illustrating the creation of table “depot” follows:

package main

import (
  "log"

  "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() {

  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();"`
    Country    string    `db:"country" sqac:"nullable:false;default:CA"`
    Province   string    `db:"province" sqac:"nullable:false;default:AB"`
    Region     string    `db:"region" sqac:"nullable:false;default:YYC"`
    Population int       `db:"population" sqac:"nullable:false;default:0;index:non-unique"`
  }

  // Create a PublicDB instance to connect to the test sqlite db
  Handle = sqac.Create("sqlite", false, false, "testdb.sqlite")

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

  // Close the connection
  Handle.Close()
}

Create Table with Nested Structs

Table declarations may also contain nested structs. In the following example, struct Triplet is defined with db and sqac tags and then included in struct Equipment. When CreateTables(Equipment{}) is executed, the nested struct is unpacked resulting in the creation of a table containing the merged fields of structs Triplet and Equipment. Any number of structs may be included in a sqac table declaration, and the nesting depth is limited only by the Go compiler.

type Triplet struct {
  TripOne   string `db:"trip_one" sqac:"nullable:false"`
  TripTwo   int64  `db:"trip_two" sqac:"nullable:false;default:0"`
  Tripthree string `db:"trip_three" sqac:"nullable:false"`
}

type Equipment struct {
  EquipmentNum   int64     `db:"equipment_num" sqac:"primary_key:inc;start:55550000"`
  ValidFrom      time.Time `db:"valid_from" sqac:"primary_key;nullable:false;default:now()"`
  ValidTo        time.Time `db:"valid_to" sqac:"primary_key;nullable:false;default:make_timestamptz(9999, 12, 31, 23, 59, 59.9)"`
  CreatedAt      time.Time `db:"created_at" sqac:"nullable:false;default:now()"`
  InspectionAt   time.Time `db:"inspection_at" sqac:"nullable:true"`
  MaterialNum    int       `db:"material_num" sqac:"index:idx_material_num_serial_num"`
  Description    string    `db:"description" sqac:"sqac:nullable:false"`
  SerialNum      string    `db:"serial_num" sqac:"index:idx_material_num_serial_num"`
  Triplet        // structs can be nested to any level
}