CreateIndex

CreateIndex creates the index contained in the incoming IndexInfo structure. Indexes are created as non-unique by default, and in compound index situations the fields (columns) will be added to the index in the order they are contained in the IndexInfo.[]IndexFields slice.

Care should be taken when providing an index name, as the value of input variable ‘in’ is used verbatim when the index is created in the database. The suggested format for index naming is as follows:

“idx_” + table_name + “_” + column_name1 + “_” + column_name2 + “_” + …

Incorporating the table name in the index name will prevent index name collisions in the target database.

Generally speaking, it is best to maintain indexes via changes to the ‘sqac:’ tag attributes in the go struct related to the target database table. CreateIndex is provided as a convenience mechanism for (rare?) situations where changes to the model are prohibited, or where an index is required temporarily.

Errors encountered during CreateIndex execution are returned to the caller immediately

CreateIndex Example

The following structure ‘Depot’ has been used to create table “depot” in the target database. We will subsequently use the PublicDB.CreateIndex() method to create a new non-unique index on table columns “region” and “county”:

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
  )

  // table depot declaration - no index
  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"`
    County     string    `db:"county" sqac:"nullable:false"`
    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")
  defer Handle.Close()

  // Ensure table "depot" exists in the database
  err := Handle.DestructiveResetTables(Depot{})
  if err != nil {
    log.Fatalf("%s", err.Error())
  }

  // Determine the table name in the database
  tn := sqac.GetTableName(Depot{})    // "depot"
  if tn == "" {
    log.Fatalf("Unable to determine table name for struct %v", Depot{})
  }

  // Create the new index (without checking for its existence).
  // See PublicDB.ExistsIndex()
  var indexInfo sqac.IndexInfo
  indexInfo.TableName = tn
  indexInfo.Unique = false
  indexInfo.IndexFields = []string{"region", "county"}
  err = Handle.CreateIndex("idx_depot_region_county", indexInfo)
  if err != nil {
    log.Errorf("%s", err.Error())
  }
  Handle.Close()
}