Golang DB.Exec方法代碼示例

func runExec(ctx context.Context, db *sql.DB, query string) error {
	done := make(chan struct{})
	var (
		errMsg error
	)
	go func() {
		for {
			if _, err := db.Exec(query); err != nil {
				errMsg = err
				time.Sleep(time.Second)
				continue
			} else {
				errMsg = nil
				done <- struct{}{}
				break
			}
		}
	}()
	select {
	case <-done:
		return errMsg
	case <-ctx.Done():
		return fmt.Errorf("runExec %s timed out with %v / %v", query, ctx.Err(), errMsg)
	}
}

func dbUpdateFromV8(db *sql.DB) error {
	stmt := `
UPDATE certificates SET fingerprint = replace(fingerprint, " ", "");
INSERT INTO schema (version, updated_at) VALUES (?, strftime("%s"));`
	_, err := db.Exec(stmt, 9)
	return err
}

func v1CompatUpdateDatabase(
	ctx *grader.Context,
	db *sql.DB,
	run *grader.RunInfo,
) {
	_, err := db.Exec(
		`UPDATE
			Runs
		SET
			status = 'ready', verdict = ?, runtime = ?, memory = ?, score = ?,
			contest_score = ?, judged_by = ?
		WHERE
			run_id = ?;`,
		run.Result.Verdict,
		run.Result.Time,
		run.Result.Memory,
		run.Result.Score,
		run.Result.ContestScore,
		run.Result.JudgedBy,
		run.ID,
	)
	if err != nil {
		ctx.Log.Error("Error updating the database", "err", err, "run", run)
	}
}

func do(db *sql.DB, sql string) {
	_, err := db.Exec(sql)
	if err == nil {
		return
	}
	panic(fmt.Sprintf("Error %v running SQL: %s", err, sql))
}

// PostgreSQL INSERT Handler
func psqlInsertHandler(db *sql.DB) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {

		_, err := db.Exec("INSERT INTO models (uid, data) VALUES($1, $2)", "c8cb5c0b-a42e-4490-8e3d-f617f439dc27", "data")

		if err != nil {
			http.Error(w, http.StatusText(500), 500)
			return
		}

		/*
		   fmt.Println("# Deleting")
		    stmt, err = db.Prepare("delete from userinfo where uid=$1")
		    checkErr(err)

		    res, err = stmt.Exec(lastInsertId)
		    checkErr(err)

		    affect, err = res.RowsAffected()
		    checkErr(err)

		    fmt.Println(affect, "rows changed")
		*/

		w.Header().Set("Content-Type", "application/json")
		//json.NewEncoder(w).Encode(users)
	})
}

// supprime la vue de trollID puis sauvegarde des observations reçues par SOAP de MH, observées juste maintenant par trollId
func (store *MysqlStore) CleanAndSaveSoapItems(db *sql.DB, trollId int, items []*SoapItem) error {
	seconds := time.Now()
	sql := "delete from observation where auteur=?"
	_, err := db.Exec(sql, trollId)
	if err != nil {
		return err
	}
	sql = "insert into observation"
	sql += "        (auteur, num, date, type, nom, x, y, z)"
	sql += " values (      ?,  ?,    ?,    ?,   ?, ?, ?, ?)"
	for _, i := range items {
		var t string
		if i.Type == "TROLL" {
			t = "troll"
		} else if i.Type == "MONSTRE" {
			t = "monstre"
		} else if i.Type == "LIEU" {
			t = "lieu"
		} else if i.Type == "TRESOR" {
			t = "tresor"
		} else {
			continue
		}
		_, err = db.Exec(sql, trollId, i.Numero, seconds, t, i.Nom, i.PositionX, i.PositionY, i.PositionN)
		if err != nil {
			return err
		}
	}
	return nil
}

func createKVTable(t *testing.T, sqlDB *gosql.DB, numRows int) {
	// Fix the column families so the key counts don't change if the family
	// heuristics are updated.
	if _, err := sqlDB.Exec(`
CREATE DATABASE t;
CREATE TABLE t.kv (k INT PRIMARY KEY, v INT, FAMILY (k), FAMILY (v));
CREATE INDEX foo on t.kv (v);
`); err != nil {
		t.Fatal(err)
	}

	// Bulk insert.
	var insert bytes.Buffer
	if _, err := insert.WriteString(fmt.Sprintf(`INSERT INTO t.kv VALUES (%d, %d)`, 0, numRows-1)); err != nil {
		t.Fatal(err)
	}
	for i := 1; i < numRows; i++ {
		if _, err := insert.WriteString(fmt.Sprintf(` ,(%d, %d)`, i, numRows-i)); err != nil {
			t.Fatal(err)
		}
	}
	if _, err := sqlDB.Exec(insert.String()); err != nil {
		t.Fatal(err)
	}
}

func do(db *sql.DB, sql string) {
	_, err := db.Exec(sql)
	if err == nil {
		return
	}
	log.Fatalf("Error %v running SQL: %s", err, sql)
}

// sauvegarde un nouveau partage (à l'état de proposition de a pour b)
func (store *MysqlStore) InsertPartage(db *sql.DB, trollA int, trollB int) error {
	sql := "insert ignore into"
	sql += " partage (troll_a, troll_b)"
	sql += " values (       ?,       ?)"
	_, err := db.Exec(sql, trollA, trollB)
	return err
}

func addToMBTile(tile Tile, db *sql.DB) error {
	_, err := db.Exec("insert into tiles (zoom_level, tile_column, tile_row, tile_data) values (?, ?, ?, ?);", tile.z, tile.x, tile.flipped_y(), tile.Content)
	if err != nil {
		return err
	}
	return nil
}

func UpdateHook(db *sql.DB, query string, v *Hook) error {

	args := SliceHook(v)[1:]
	args = append(args, v.ID)
	_, err := db.Exec(query, args...)
	return err
}

// createTestTable tries to create a new table named based on the passed in id.
// It is designed to be synced with a number of concurrent calls to this
// function. Before starting, it first signals a done on the start waitgroup
// and then will block until the signal channel is closed. Once closed, it will
// proceed to try to create the table. Once the table creation is finished (be
// it successful or not) it signals a done on the end waitgroup.
func createTestTable(
	t *testing.T,
	tc *testcluster.TestCluster,
	id int,
	db *gosql.DB,
	wgStart *sync.WaitGroup,
	wgEnd *sync.WaitGroup,
	signal chan struct{},
	completed chan int,
) {
	defer wgEnd.Done()

	wgStart.Done()
	<-signal

	tableSQL := fmt.Sprintf(`
		CREATE TABLE IF NOT EXISTS "test"."table_%d" (
			id INT PRIMARY KEY,
			val INT
		)`, id)

	for {
		if _, err := db.Exec(tableSQL); err != nil {
			if testutils.IsSQLRetryableError(err) {
				continue
			}
			t.Errorf("table %d: could not be created: %v", id, err)
			return
		}
		completed <- id
		break
	}
}

func addChannelHistory(s *slack.Slack, db *sql.DB, channel, oldest string) (string, error) {
	params := slack.NewHistoryParameters()
	params.Oldest = oldest
	params.Count = 1000
	history, err := s.GetChannelHistory(channel, params)
	if err != nil {
		return "", err
	}
	for _, msg := range history.Messages {
		if msg.SubType != "" {
			continue
		}
		fmt.Printf("%s: %s\n", msg.User, msg.Text)
		ts := timestampToTime(msg.Timestamp)
		_, err := db.Exec(
			`INSERT INTO messages(channel_id, user_id, text, timestamp, created_at, updated_at)
			 VALUES(?, ?, ?, ?, NOW(), NOW())
			`,
			channel, msg.User, msg.Text, ts.Format("2006-01-02 15:04:05"))
		if err != nil {
			return "", err
		}
	}

	if len(history.Messages) > 0 {
		return history.Messages[0].Timestamp, nil
	}

	return "", nil
}

func prepareDatabase(db *sql.DB) error {
	_, err := db.Exec(fmt.Sprintf("CREATE TABLE IF NOT EXISTS %v (id text not null, applied_at timestamp not null)", tableName))
	if err != nil {
		return err
	}
	return nil
}

func insertTask(db *sql.DB, serializer format.Serializer, task *models.Task, malformedTaskDefinition bool) {
	taskDefData, err := serializer.Marshal(logger, format.ENCRYPTED_PROTO, task.TaskDefinition)
	Expect(err).NotTo(HaveOccurred())

	if malformedTaskDefinition {
		taskDefData = []byte("{{{{{{{{{{")
	}

	queryStr := `INSERT INTO tasks
						  (guid, domain, created_at, updated_at, first_completed_at, state,
							cell_id, result, failed, failure_reason, task_definition)
					    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	if test_helpers.UsePostgres() {
		queryStr = test_helpers.ReplaceQuestionMarks(queryStr)
	}
	result, err := db.Exec(
		queryStr,
		task.TaskGuid,
		task.Domain,
		task.CreatedAt,
		task.UpdatedAt,
		task.FirstCompletedAt,
		task.State,
		task.CellId,
		task.Result,
		task.Failed,
		task.FailureReason,
		taskDefData,
	)
	Expect(err).NotTo(HaveOccurred())
	Expect(result.RowsAffected()).NotTo(Equal(1))
}