AlterTables

The AlterTables method can be used to introduce additive changes to existing tables, or create new tables if the tables contained in the variadic parameter list are not present in the database. Permissible table changes include:

  • adding one or more columns
  • addition of simple or compound indices
  • addition of foreign-key relationships, as long as the existing data permits it

Inadmissible table changes include:

  • changing the type of an existing column
  • changing the name of an existing column
  • updating the properties of an existing column (nullable/default)
  • deleting an existing column
  • deleting an index
  • deleting an existing foreign-key relationship

To deal with inadmissible changes, it is best to call DropTables followed by CreateTables, or call the DestructiveResetTables method.

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

AlterTables Example

The following structure ‘Depot’ has been used to create table “depot” in the target database. We will add a new column and a new index to the table via the AlterTables method.

  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"`
  }

Column “county” and index “idx_depot_region_county” have been added to the Depot struct declaration:

  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"`
    // --> start of the alterations
    Region     string    `db:"region" sqac:"nullable:false;default:YYC;index:idx_depot_region_county"`
    County     string    `db:"county" sqac:"nullable:false;index:idx_depot_region_county"`
    Population int       `db:"population" sqac:"nullable:false;default:0;index:non-unique"`
    // --> end of the alterations
  }

A complete sample program to alter table “depot” is shown below:

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
  )

  //   Original Depot declaration - left for illustrative purposes
  //   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"`
  //   }

  // updated Depot declaration with new field "County" and new index "idx_depot_region_county"
  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;index:idx_depot_region_county"`
    County     string    `db:"county" sqac:"nullable:false;index:idx_depot_region_county"`
    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")

  // Alter table "depot" in the target database
  err := Handle.AlterTables(Depot{})
  if err != nil {
    log.Errorf("%s", err.Error())
  }

  // Close the connection
  Handle.Close()
}