func DefineCategoryTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Category{}, "category")
	t.SetKeys(true, "id")
	t.ColMap("description").
		SetUnique(true).
		SetNotNull(true)
}

func DefineVirusdefTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(VirusDef{}, "virusdef")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetUnique(true).
		SetNotNull(true)
}

func getSequence(dbMap *gorp.DbMap) (uint64, error) {
	tx, err := dbMap.Begin()
	if err != nil {
		return 0, err
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()

	st, err := tx.Prepare("UPDATE seq set id = LAST_INSERT_ID(id + 1)")
	if err != nil {
		return 0, err
	}
	defer st.Close()
	st.Exec()

	stmt, err := tx.Prepare("SELECT LAST_INSERT_ID()")
	defer stmt.Close()
	var id uint64
	err = stmt.QueryRow().Scan(&id)
	if err != nil {
		return 0, err
	}
	return id, nil
}

// Query generates a Query for a target model.  The target that is
// passed in must be a pointer to a struct, and will be used as a
// reference for query construction.
func Query(m *gorp.DbMap, exec gorp.SqlExecutor, target interface{}) interfaces.Query {
	// Handle non-standard dialects
	switch src := m.Dialect.(type) {
	case gorp.MySQLDialect:
		m.Dialect = dialects.MySQLDialect{src}
	case gorp.SqliteDialect:
		m.Dialect = dialects.SqliteDialect{src}
	default:
	}
	plan := &QueryPlan{
		dbMap:    m,
		executor: exec,
	}

	targetVal := reflect.ValueOf(target)
	if targetVal.Kind() != reflect.Ptr || targetVal.Elem().Kind() != reflect.Struct {
		plan.Errors = append(plan.Errors, errors.New("A query target must be a pointer to struct"))
	}
	targetTable, err := plan.mapTable(targetVal)
	if err != nil {
		plan.Errors = append(plan.Errors, err)
		return plan
	}
	plan.target = targetVal
	plan.table = targetTable
	return plan
}

func DefineProfileTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Profile{}, "profile")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetUnique(true).
		SetNotNull(true)
}

func DefineUtmstatusTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(UtmStatus{}, "utmstatus")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetUnique(true).
		SetNotNull(true)
}

func DefineServiceTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Service{}, "service")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetUnique(true).
		SetNotNull(true)
}

func InitAllTables(dbm *gorp.DbMap) error {
	defineAllTables(dbm)
	if err := dbm.CreateTablesIfNotExists(); err != nil {
		return err
	}

	txn, err := dbm.Begin()
	if err != nil {
		return err
	}
	defer txn.Rollback()

	count, err := CountRows("loglevel", "id", txn)
	if err != nil {
		return err
	}
	if count == 0 {
		InitLoglevelTable(txn)
	}

	count, err = CountRows("utmstatus", "id", txn)
	if err != nil {
		return err
	}
	if count == 0 {
		InitUtmstatusTable(txn)
	}

	txn.Commit()
	return nil
}

func DefineHostTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Host{}, "host")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetUnique(true).
		SetNotNull(true)
}

func (s SqlChannelStore) get(id string, master bool) StoreChannel {
	storeChannel := make(StoreChannel)

	go func() {
		result := StoreResult{}

		var db *gorp.DbMap
		if master {
			db = s.GetMaster()
		} else {
			db = s.GetReplica()
		}

		if obj, err := db.Get(model.Channel{}, id); err != nil {
			result.Err = model.NewAppError("SqlChannelStore.Get", "We encountered an error finding the channel", "id="+id+", "+err.Error())
		} else if obj == nil {
			result.Err = model.NewAppError("SqlChannelStore.Get", "We couldn't find the existing channel", "id="+id)
		} else {
			result.Data = obj.(*model.Channel)
		}

		storeChannel <- result
		close(storeChannel)
	}()

	return storeChannel
}

func NewConnection(cfg Config) (*gorp.DbMap, error) {
	if !strings.HasPrefix(cfg.DSN, "postgres://") {
		return nil, errors.New("unrecognized database driver")
	}

	db, err := sql.Open("postgres", cfg.DSN)
	if err != nil {
		return nil, err
	}

	db.SetMaxIdleConns(cfg.MaxIdleConnections)
	db.SetMaxOpenConns(cfg.MaxOpenConnections)

	dbm := gorp.DbMap{
		Db:      db,
		Dialect: gorp.PostgresDialect{},
	}

	for _, t := range tables {
		tm := dbm.AddTableWithName(t.model, t.name).SetKeys(t.autoinc, t.pkey...)
		for _, unique := range t.unique {
			cm := tm.ColMap(unique)
			if cm == nil {
				return nil, fmt.Errorf("no such column: %q", unique)
			}
			cm.SetUnique(true)
		}
	}

	return &dbm, nil
}

func SelectAllEquipmentInfo(dbm *gorp.DbMap) ([]EquipmentInfo, error) {
	var results []EquipmentInfo

	q := sq.Select(
		`"EquipmentInfo"."ID"`,
		`"EquipmentInfo"."CertifiedName"`,
		`"RadioAccessTechnology"."Description" AS EquipmentType`,
		`"EquipmentInfo"."Model"`,
		`"EquipmentInfo"."AuthNumber"`,
		`"EquipmentInfo"."RadioType"`,
		`"EquipmentInfo"."IsApplied1421"`,
		`"EquipmentInfo"."AuthDate"`,
		`"EquipmentInfo"."Note"`,
		`"EquipmentInfo"."File"`,
	).From(
		"EquipmentInfo",
	).LeftJoin(
		`"RadioAccessTechnology" ON "EquipmentInfo"."EquipmentType" = "RadioAccessTechnology"."EquipmentType"`,
	)

	sql, _, err := q.ToSql()
	if err != nil {
		return results, err
	}

	if _, err := dbm.Select(&results, sql); err != nil {
		return nil, err
	}

	return results, nil
}

func runQueryPlanSuite(t *testing.T, dialect gorp.Dialect, connection *sql.DB) {
	dbMap := new(gorp.DbMap)
	dbMap.Dialect = dialect
	dbMap.Db = connection
	testSuite := new(QueryPlanTestSuite)
	testSuite.Map = dbMap
	suite.Run(t, testSuite)
}

func DefineVirusTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Virus{}, "virus")
	t.SetKeys(false, "id")
	SetNotNull(t, "status", "virusdef")
	t.ColMap("url").
		SetMaxSize(2048).
		SetNotNull(true)
}

func DefineLoglevelTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(LogLevel{}, "loglevel")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetMaxSize(15).
		SetUnique(true).
		SetNotNull(true)
}

func DefineUserTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(User{}, "user")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetMaxSize(45).
		SetUnique(true).
		SetNotNull(true)
}

// TODO: DRY the suite initialization code
func runQueryLanguageSuite(t *testing.T, dialect gorp.Dialect, connection *sql.DB) {
	dbMap := new(gorp.DbMap)
	dbMap.Dialect = dialect
	dbMap.Db = connection
	//dbMap.TraceOn("TEST DB: ", log.New(os.Stdout, "", log.LstdFlags))
	testSuite := new(QueryLanguageTestSuite)
	testSuite.Map = dbMap
	suite.Run(t, testSuite)
}

func LoadSQLDate(dbMap *gorp.DbMap, t interface{}, rows []map[string]interface{}) {
	for _, row := range rows {
		err := mapstructure.Decode(row, t)
		checkErr(err, "")

		err = dbMap.Insert(t)
		checkErr(err, "")
	}
}

func DefineDeviceTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Device{}, "device")
	t.SetKeys(true, "id")
	t.ColMap("name").
		SetNotNull(true)
	t.ColMap("serial").
		SetMaxSize(16).
		SetUnique(true).
		SetNotNull(true)
}

func DefineLogTable(dbm *gorp.DbMap) {
	t := dbm.AddTableWithName(Log{}, "log")
	t.SetKeys(true, "id")
	SetNotNull(t,
		"file", "logtype", "device", "level", "user", "service", "date",
		"policy_id", "source_if", "dest_port", "dest_if", "sent_byte",
		"received_byte")
	t.ColMap("source_ip").SetMaxSize(45).SetNotNull(true)
	t.ColMap("dest_ip").SetMaxSize(45).SetNotNull(true)
	t.ColMap("message").SetMaxSize(255)
}