_sqldemo_gen.go

// Code generated by GREENPACK (github.com/glycerine/greenpack). DO NOT EDIT.

package testdata

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	//"github.com/glycerine/greenpack/msgp"
)

// NB: In this file, we have deleted all the
// non-sql code from sqldemo_gen.go
// to make it easy to reference the SQL interaction code.

/* For reference, from sqldemo.go, the definition of the struct is:

type StarShipFireAnt struct {
	Captain                string    `msg:"captain" zid:"0"`
	CargoAreaMetersSquared float64   `msg:"cargo" zid:"1"`
	Shuttles               int       `msg:"shuttles" zid:"2"`
	RawBytesData           []byte    `msg:"rawBytesData" zid:"3"`
	LastMessageTime        time.Time `msg:"lastMessageTime" zid:"4"`
}

*/

// StoreTogSQL stores z into the table specified by tableName,
// that resides inside the database dbName. If create is true,
// then we will attempt to create the table if it does not
// already exist. The resuseStmt can be nil. If it is nil,
// then a new insert statement will be prepared and
// returned in stmt. On subsequent calls, this stmt
// should be passed in as the reuseStmt parameter, in
// order to allow efficient re-use of the prepared statement.
// After the insert, the newly added rowid will be
// returned in injectedRowID. The insert SQL is returned in
// in sqlIns. If db is nil, only the insert SQL string
// will be returned, and the database will not be contacted.
// Similarly, sqlCreate will return the table creation SQL;
// if create was true. The rest will be inserted too, in
// one big batch, or rest can be nil if there is
// nothing more to insert.
func (z *StarShipFireAnt) StoreToSQL(
	db *sql.DB,
	dbName, tableName string,
	create bool,
	reuseStmt *sql.Stmt,
	rest []*StarShipFireAnt,
) (stmt *sql.Stmt, injectedRowID int64, sqlIns, sqlCreate string, err error) {

	stmt = reuseStmt

	var tx *sql.Tx
	if db != nil {
		tx, err = db.BeginTx(context.Background(), nil)
		if err != nil {
			return
		}
		defer tx.Rollback()
	}

	// create table to store type 'StarShipFireAnt'
	if create {
		sqlCreate = "CREATE TABLE IF NOT EXISTS " + dbName + "." + tableName + ` (
  rowid bigint AUTO_INCREMENT not null primary key
, updatetm TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, ”captain” LONGTEXT
, ”cargo” DOUBLE
, ”shuttles” BIGINT
, ”rawBytesData” LONGBLOB
, ”lastMessageTime” DATETIME(6)

)`
		// mariaDB needs backtick quoted strings
		sqlCreate = strings.ReplaceAll(sqlCreate, "”", "`")
		if db != nil {
			_, err = db.Exec(sqlCreate)
			if err != nil {
				err = fmt.Errorf("error creating table: '%v'; sql was: '%v'", err, sqlCreate)
				return
			}
		}
	} // end if create

	sqlIns = "insert into " + dbName + "." + tableName + "(”captain”, ”cargo”, ”shuttles”, ”rawBytesData”, ”lastMessageTime”) values (?,?,?,?,?)"
	sqlIns = strings.ReplaceAll(sqlIns, "”", "`")
	if db == nil {
		return
	}

	var vals []any
	znil := 1 // allow z to be nil
	if z != nil {
		znil = 0
		vals = []any{z.Captain, z.CargoAreaMetersSquared, z.Shuttles, z.RawBytesData, z.LastMessageTime}
	}

	var res sql.Result

	if len(rest) > 0 {
		// do multi-statement instead of prepared stmt
		sqlIns += strings.Repeat(",(?,?,?,?,?)", len(rest)-znil)
		for _, r := range rest {
			vals = append(vals, r.Captain, r.CargoAreaMetersSquared, r.Shuttles, r.RawBytesData, r.LastMessageTime)
		}
		// use a tmpStmt because the length of the batch will
		// rarely be repeated; this Stmt is not re-usable.
		var tmpStmt *sql.Stmt
		tmpStmt, err = tx.Prepare(sqlIns)
		if err != nil {
			err = fmt.Errorf("error preparing multi-insert: '%v'; sql was: '%v'", err, sqlIns)
			return
		}
		defer tmpStmt.Close()
		res, err = tmpStmt.Exec(vals...)
		if err != nil {
			err = fmt.Errorf("error on tmpStmt.Exec insert: '%v'; sql was: '%v'", err, sqlIns)
			return
		}

	} else {
		if stmt == nil {
			stmt, err = db.Prepare(sqlIns)
			if err != nil {
				err = fmt.Errorf("error preparing insert: '%v'; sql was: '%v'", err, sqlIns)
				return
			}
		}
		res, err = stmt.Exec(vals...)
		if err != nil {
			err = fmt.Errorf("error on stmt.Exec(): '%v'; sql was: '%v'", err, sqlIns)
			return
		}
	}

	injectedRowID, err = res.LastInsertId()
	if err != nil {
		err = fmt.Errorf("error getting res.LastInsertId(): '%v'", err)
		return
	}
	err = tx.Commit()

	return
}

// Note that the github.com/go-sql-driver/mysql driver will need parseTime=true
// added to the DSN string in order to pull DATETIME from sql into time.Time in Go.
// For example:
// db, err := sql.Open("mysql", "user:pw@tcp(localhost:3306)/databasename?parseTime=true")
//
// Also storing time.Time from Go into MariaDB will be lossy because Go keeps
// nanoseconds but MariaDB only stores microseconds.
//
// Beware that ctx cancellation will close the db connection(!) This is
// very unexpected. Likely this is the only way it can be implemented.
//
// From the github.com/go-sql-driver/mysql README docs:
//
// "The QueryContext, ExecContext, etc. variants provided by database/sql will
//  cause the connection to be closed if the provided context is
//  cancelled or timed out before the result is received by the driver"

// GetFromSQL fetches from the database and fills in z (this method's receiver)
// and returns a variable sized slice in res. res[0] will always be z.
//
// The where clause can be the empty string. It can also be
// used to get a subset of the contents of tableName back.
//
// With an empty where, all records in tableName
// are selected and returned. Otherwise, the where string
// is appended to a select for all fields.
//
// Advanced use:
// If the db is nil, the sqlSel will be returned but the
// database will not be contacted. This allows you get the
// query that has all the fields, and then
// modify the select query and re-submit it with complex
// joins, etc.
//
// Hence if the where clause starts with "select",
// then it will be used directly; it will not be appended
// to a select of all the columns. Be careful with this,
// however, since the rows.Scan() call generated below
// expects to get all the fields, so you must keep
// the select statement in sync with those expectations.
func (z *StarShipFireAnt) GetFromSQL(
	ctx context.Context,
	db *sql.DB,
	dbName, tableName, where string,
) (res []*StarShipFireAnt, sqlSel string, err error) {

	if strings.HasPrefix(where, "select") {
		sqlSel = where
	} else {

		// get from sql and write into 'StarShipFireAnt'
		sqlSel = "select ”captain”, ”cargo”, ”shuttles”, ”rawBytesData”, ”lastMessageTime” from " + dbName + "." + tableName
		sqlSel = strings.ReplaceAll(sqlSel, "”", "`") + " " + where
	}

	if db == nil {
		return // just providing the sqlSel statement.
	}

	var rows *sql.Rows
	rows, err = db.QueryContext(ctx, sqlSel)
	if err != nil {
		err = fmt.Errorf("error on select: '%v'; sql was '%v'", err, sqlSel)
		return
	}
	defer rows.Close()

	var v *StarShipFireAnt
	for i := 0; rows.Next(); i++ {
		if i == 0 {
			v = z
		} else {
			v = &StarShipFireAnt{}
		}
		err = rows.Scan(&v.Captain, &v.CargoAreaMetersSquared, &v.Shuttles, &v.RawBytesData, &v.LastMessageTime)
		if err != nil {
			return
		}
		res = append(res, v)
	}

	return
}