func (e *ChefEnvironment) saveEnvironmentPostgreSQL() util.Gerror {
	dab, daerr := datastore.EncodeBlob(&e.Default)
	if daerr != nil {
		return util.CastErr(daerr)
	}
	oab, oaerr := datastore.EncodeBlob(&e.Override)
	if oaerr != nil {
		return util.CastErr(oaerr)
	}
	cvb, cverr := datastore.EncodeBlob(&e.CookbookVersions)
	if cverr != nil {
		return util.CastErr(cverr)
	}

	tx, err := datastore.Dbh.Begin()
	if err != nil {
		gerr := util.CastErr(err)
		return gerr
	}

	_, err = tx.Exec("SELECT goiardi.merge_environments($1, $2, $3, $4, $5)", e.Name, e.Description, dab, oab, cvb)
	if err != nil {
		tx.Rollback()
		gerr := util.CastErr(err)
		return gerr
	}
	tx.Commit()
	return nil
}

func (s *Shovey) getShoveyRunSQL(nodeName string) (*ShoveyRun, util.Gerror) {
	sr := new(ShoveyRun)
	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "SELECT id, shovey_uuid, node_name, status, ack_time, end_time, error, exit_status FROM shovey_runs WHERE shovey_uuid = ? AND node_name = ?"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "SELECT id, shovey_uuid, node_name, status, ack_time, end_time, error, exit_status FROM goiardi.shovey_runs WHERE shovey_uuid = $1 and node_name = $2"
	} else {
		return nil, util.NoDBConfigured
	}

	stmt, err := datastore.Dbh.Prepare(sqlStatement)
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return nil, gerr
	}
	defer stmt.Close()
	row := stmt.QueryRow(s.RunID, nodeName)
	err = sr.fillShoveyRunFromSQL(row)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		return nil, gerr
	}

	return sr, nil
}

func (e *ChefEnvironment) saveEnvironmentMySQL() util.Gerror {
	dab, daerr := datastore.EncodeBlob(&e.Default)
	if daerr != nil {
		return util.CastErr(daerr)
	}
	oab, oaerr := datastore.EncodeBlob(&e.Override)
	if oaerr != nil {
		return util.CastErr(oaerr)
	}
	cvb, cverr := datastore.EncodeBlob(&e.CookbookVersions)
	if cverr != nil {
		return util.CastErr(cverr)
	}

	tx, err := datastore.Dbh.Begin()
	if err != nil {
		return util.CastErr(err)
	}

	_, err = tx.Exec("INSERT INTO environments (name, description, default_attr, override_attr, cookbook_vers, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE description = ?, default_attr = ?, override_attr = ?, cookbook_vers = ?, updated_at = NOW()", e.Name, e.Description, dab, oab, cvb, e.Description, dab, oab, cvb)
	if err != nil {
		tx.Rollback()
		gerr := util.CastErr(err)
		return gerr
	}

	tx.Commit()
	return nil
}

func (s *Shovey) checkCompletedSQL() util.Gerror {
	var c int
	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "SELECT count(id) FROM shovey_runs WHERE shovey_uuid = ? AND status IN ('invalid', 'succeeded', 'failed', 'down', 'nacked')"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "SELECT count(id) FROM goiardi.shovey_runs WHERE shovey_uuid = $1 AND status IN ('invalid', 'succeeded', 'failed', 'down', 'nacked')"
	} else {
		return util.NoDBConfigured
	}

	stmt, err := datastore.Dbh.Prepare(sqlStatement)
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	defer stmt.Close()
	err = stmt.QueryRow(s.RunID).Scan(&c)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		return gerr
	}
	if c == len(s.NodeNames) {
		s.Status = "complete"
		s.save()
	}

	return nil
}

func (sr *ShoveyRun) addStreamOutSQL(output string, outputType string, seq int, isLast bool) util.Gerror {
	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "INSERT INTO shovey_run_streams (shovey_run_id, seq, output_type, output, is_last, created_at) VALUES (?, ?, ?, ?, ?, NOW())"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "INSERT INTO goiardi.shovey_run_streams (shovey_run_id, seq, output_type, output, is_last, created_at) VALUES ($1, $2, $3, $4, $5, NOW())"
	} else {
		return util.NoDBConfigured
	}
	tx, err := datastore.Dbh.Begin()
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	_, err = tx.Exec(sqlStatement, sr.ID, seq, outputType, output, isLast)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		tx.Rollback()
		return gerr
	}
	tx.Commit()
	return nil
}

func (s *Shovey) cancelRunsSQL() util.Gerror {
	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "UPDATE shovey_runs SET status = 'cancelled', end_time = NOW() WHERE shovey_uuid = ? AND status NOT IN ('invalid', 'succeeded', 'failed', 'down', 'nacked')"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "UPDATE goiardi.shovey_runs SET status = 'cancelled', end_time = NOW() WHERE shovey_uuid = $1 AND status NOT IN ('invalid', 'succeeded', 'failed', 'down', 'nacked')"
	} else {
		return util.NoDBConfigured
	}
	tx, err := datastore.Dbh.Begin()
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	_, err = tx.Exec(sqlStatement, s.RunID)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		return gerr
	}
	tx.Commit()
	return nil
}

func checkAuth12Headers(user actor.Actor, r *http.Request, headToCheck, signedHeaders string) util.Gerror {
	sig, err := base64.StdEncoding.DecodeString(signedHeaders)
	if err != nil {
		gerr := util.CastErr(err)
		return gerr
	}
	sigSha := sha1.Sum([]byte(headToCheck))
	err = chefcrypto.Auth12HeaderVerify(user.PublicKey(), sigSha[:], sig)
	if err != nil {
		return util.CastErr(err)
	}
	return nil
}

func (s *Shovey) savePostgreSQL() util.Gerror {
	tx, err := datastore.Dbh.Begin()
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	_, err = tx.Exec("SELECT goiardi.merge_shoveys($1, $2, $3, $4, $5)", s.RunID, s.Command, s.Status, s.Timeout, s.Quorum)
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	tx.Commit()
	return nil
}

func (cbv *CookbookVersion) updateCookbookVersionMySQL(defb, libb, attb, recb, prob, resb, temb, roob, filb, metb []byte, maj, min, patch int64) util.Gerror {
	tx, err := datastore.Dbh.Begin()
	if err != nil {
		gerr := util.Errorf(err.Error())
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}

	res, err := tx.Exec("INSERT INTO cookbook_versions (cookbook_id, major_ver, minor_ver, patch_ver, frozen, metadata, definitions, libraries, attributes, recipes, providers, resources, templates, root_files, files, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), frozen = ?, metadata = ?, definitions = ?, libraries = ?, attributes = ?, recipes = ?, providers = ?, resources = ?, templates = ?, root_files = ?, files = ?, updated_at = NOW()", cbv.cookbookID, maj, min, patch, cbv.IsFrozen, metb, defb, libb, attb, recb, prob, resb, temb, roob, filb, cbv.IsFrozen, metb, defb, libb, attb, recb, prob, resb, temb, roob, filb)
	if err != nil {
		tx.Rollback()
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	cID, err := res.LastInsertId()
	if err != nil {
		tx.Rollback()
		gerr := util.Errorf(err.Error())
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	cbv.id = int32(cID)

	tx.Commit()
	return nil
}

func (s *Shovey) saveMySQL() util.Gerror {
	tx, err := datastore.Dbh.Begin()
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return gerr
	}
	_, err = tx.Exec("INSERT INTO shoveys (run_id, command, status, timeout, quorum, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE status = ?, updated_at = NOW()", s.RunID, s.Command, s.Status, s.Timeout, s.Quorum, s.Status)
	if err != nil {
		tx.Rollback()
		gerr := util.CastErr(err)
		return gerr
	}
	tx.Commit()
	return nil
}

// Get a report.
func Get(runID string) (*Report, util.Gerror) {
	var report *Report
	var found bool
	if config.UsingDB() {
		var err error
		report, err = getReportSQL(runID)
		if err != nil {
			if err == sql.ErrNoRows {
				found = false
			} else {
				gerr := util.CastErr(err)
				gerr.SetStatus(http.StatusInternalServerError)
				return nil, gerr
			}
		} else {
			found = true
		}
	} else {
		ds := datastore.New()
		var r interface{}
		r, found = ds.Get("report", runID)
		if r != nil {
			report = r.(*Report)
		}
	}
	if !found {
		err := util.Errorf("Report %s not found", runID)
		err.SetStatus(http.StatusNotFound)
		return nil, err
	}
	return report, nil
}

// New creates a new environment, returning an error if the environment already
// exists or you try to create an environment named "_default".
func New(name string) (*ChefEnvironment, util.Gerror) {
	if !util.ValidateEnvName(name) {
		err := util.Errorf("Field 'name' invalid")
		err.SetStatus(http.StatusBadRequest)
		return nil, err
	}

	var found bool
	if config.UsingDB() {
		var eerr error
		found, eerr = checkForEnvironmentSQL(datastore.Dbh, name)
		if eerr != nil {
			err := util.CastErr(eerr)
			err.SetStatus(http.StatusInternalServerError)
			return nil, err
		}
	} else {
		ds := datastore.New()
		_, found = ds.Get("env", name)
	}
	if found || name == "_default" {
		err := util.Errorf("Environment already exists")
		return nil, err
	}

	env := &ChefEnvironment{
		Name:             name,
		ChefType:         "environment",
		JSONClass:        "Chef::Environment",
		Default:          map[string]interface{}{},
		Override:         map[string]interface{}{},
		CookbookVersions: map[string]string{},
	}
	return env, nil
}

// New creates a new cookbook.
func New(name string) (*Cookbook, util.Gerror) {
	var found bool
	if !util.ValidateEnvName(name) {
		err := util.Errorf("Invalid cookbook name '%s' using regex: 'Malformed cookbook name. Must only contain A-Z, a-z, 0-9, _ or -'.", name)
		return nil, err
	}
	if config.UsingDB() {
		var cerr error
		found, cerr = checkForCookbookSQL(datastore.Dbh, name)
		if cerr != nil {
			err := util.CastErr(cerr)
			err.SetStatus(http.StatusInternalServerError)
			return nil, err
		}
	} else {
		ds := datastore.New()
		_, found = ds.Get("cookbook", name)
	}
	if found {
		err := util.Errorf("Cookbook %s already exists", name)
		err.SetStatus(http.StatusConflict)
	}
	cookbook := &Cookbook{
		Name:     name,
		Versions: make(map[string]*CookbookVersion),
	}
	return cookbook, nil
}

// New creates a new report.
func New(runID string, nodeName string) (*Report, util.Gerror) {
	var found bool
	if config.UsingDB() {
		var err error
		found, err = checkForReportSQL(datastore.Dbh, runID)
		if err != nil {
			gerr := util.CastErr(err)
			gerr.SetStatus(http.StatusInternalServerError)
			return nil, gerr
		}
	} else {
		ds := datastore.New()
		_, found = ds.Get("report", runID)
	}
	if found {
		err := util.Errorf("Report already exists")
		err.SetStatus(http.StatusConflict)
		return nil, err
	}
	if u := uuid.Parse(runID); u == nil {
		err := util.Errorf("run id was not a valid uuid")
		err.SetStatus(http.StatusBadRequest)
		return nil, err
	}
	report := &Report{
		RunID:    runID,
		NodeName: nodeName,
		Status:   "started",
	}
	return report, nil
}

// Get a node.
func Get(nodeName string) (*Node, util.Gerror) {
	var node *Node
	var found bool
	if config.UsingDB() {
		var err error
		node, err = getSQL(nodeName)
		if err != nil {
			if err == sql.ErrNoRows {
				found = false
			} else {
				return nil, util.CastErr(err)
			}
		} else {
			found = true
		}
	} else {
		ds := datastore.New()
		var n interface{}
		n, found = ds.Get("node", nodeName)
		if n != nil {
			node = n.(*Node)
		}
	}
	if !found {
		err := util.Errorf("node '%s' not found", nodeName)
		err.SetStatus(http.StatusNotFound)
		return nil, err
	}
	return node, nil
}

// ValidatePublicKey checks that the provided public key is valid. Wrapper
// around chefcrypto.ValidatePublicKey(), but with a different error type.
func ValidatePublicKey(publicKey interface{}) (bool, util.Gerror) {
	ok, pkerr := chefcrypto.ValidatePublicKey(publicKey)
	var err util.Gerror
	if !ok {
		err = util.CastErr(pkerr)
	}
	return ok, err
}

func (s *Shovey) getShoveyNodeRunsSQL() ([]*ShoveyRun, util.Gerror) {
	var shoveyRuns []*ShoveyRun
	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "SELECT id, shovey_uuid, node_name, status, ack_time, end_time, error, exit_status FROM shovey_runs WHERE shovey_uuid = ?"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "SELECT id, shovey_uuid, node_name, status, ack_time, end_time, error, exit_status FROM goiardi.shovey_runs WHERE shovey_uuid = $1"
	} else {
		return nil, util.NoDBConfigured
	}

	stmt, err := datastore.Dbh.Prepare(sqlStatement)
	if err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return nil, gerr
	}
	defer stmt.Close()
	rows, err := stmt.Query(s.RunID)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		rows.Close()
		return nil, gerr
	}
	for rows.Next() {
		sr := new(ShoveyRun)
		err = sr.fillShoveyRunFromSQL(rows)
		if err != nil {
			gerr := util.CastErr(err)
			if err == sql.ErrNoRows {
				gerr.SetStatus(http.StatusNotFound)
			} else {
				gerr.SetStatus(http.StatusInternalServerError)
			}
			return nil, gerr
		}
		shoveyRuns = append(shoveyRuns, sr)
	}

	return shoveyRuns, nil
}

// Start kicks off all the shovey runs for this shovey instance.
func (s *Shovey) Start() util.Gerror {
	err := s.startJobs()
	if err != nil {
		s.Status = err.Status()
		s.save()
		return util.CastErr(err)
	}
	s.Status = "running"
	s.save()
	return nil
}

func allShoveyIDsSQL() ([]string, util.Gerror) {
	shoveyList := make([]string, 0)

	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "SELECT run_id FROM shoveys"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "SELECT run_id FROM goiardi.shoveys"
	} else {
		return nil, util.NoDBConfigured
	}

	rows, err := datastore.Dbh.Query(sqlStatement)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		rows.Close()
		return nil, gerr
	}
	for rows.Next() {
		var runID string
		err = rows.Scan(&runID)
		if err != nil {
			gerr := util.CastErr(err)
			gerr.SetStatus(http.StatusInternalServerError)
			return nil, gerr
		}
		shoveyList = append(shoveyList, runID)
	}
	rows.Close()
	if err = rows.Err(); err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return nil, gerr
	}
	return shoveyList, nil
}

func (sr *ShoveyRun) getStreamOutSQL(outputType string, seq int) ([]*ShoveyRunStream, util.Gerror) {
	var streams []*ShoveyRunStream
	var sqlStatement string
	if config.Config.UseMySQL {
		sqlStatement = "SELECT sr.shovey_uuid, sr.node_name, seq, output_type, streams.output, is_last, created_at FROM shovey_run_streams streams JOIN shovey_runs sr ON streams.shovey_run_id = sr.id WHERE shovey_run_id = ? AND output_type = ? AND seq >= ?"
	} else if config.Config.UsePostgreSQL {
		sqlStatement = "SELECT sr.shovey_uuid, sr.node_name, seq, output_type, streams.output, is_last, created_at FROM goiardi.shovey_run_streams streams JOIN goiardi.shovey_runs sr ON streams.shovey_run_id = sr.id WHERE shovey_run_id = $1 AND output_type = $2 AND seq >= $3"
	} else {
		return nil, util.NoDBConfigured
	}

	rows, err := datastore.Dbh.Query(sqlStatement, sr.ID, outputType, seq)
	if err != nil {
		gerr := util.CastErr(err)
		if err == sql.ErrNoRows {
			gerr.SetStatus(http.StatusNotFound)
		} else {
			gerr.SetStatus(http.StatusInternalServerError)
		}
		return nil, gerr
	}
	for rows.Next() {
		srs := new(ShoveyRunStream)
		err = srs.fillShoveyRunStreamFromSQL(rows)
		if err != nil {
			gerr := util.CastErr(err)
			gerr.SetStatus(http.StatusInternalServerError)
			return nil, gerr
		}
		streams = append(streams, srs)
	}
	rows.Close()
	if err = rows.Err(); err != nil {
		gerr := util.CastErr(err)
		gerr.SetStatus(http.StatusInternalServerError)
		return nil, gerr
	}
	return streams, nil
}