Golang DB.Exec方法代码示例

// updateUsersPoints updates users points.
func updateUsersPoints(tx *gorm.DB) error {
	q, err := getCurrentQuestionWithTX(tx)
	if err != nil {
		return nil
	}
	return tx.Exec("UPDATE `users` JOIN `answer_entries` ON users.id = answer_entries.user_id SET users.points = users.points + 1 WHERE answer_entries.question_id = ? AND answer_entries.answer_id = ?", q.ID, q.RightAnswerID).Error
}

func saveSegments(d *gorm.DB, segments []types.Segment, partid int64) error {
	var vals []interface{}
	valstrings := make([]string, len(segments))
	for i, s := range segments {
		valstrings[i] = "(?,?,?,?)"
		vals = append(vals, s.Segment, s.Size, s.MessageID, partid)
	}
	stmtString := fmt.Sprintf("INSERT INTO segment (segment, size, message_id, part_id) VALUES%s;", strings.Join(valstrings, ","))
	return d.Exec(stmtString, vals...).Error
}

func (e changedSortingPublishEvent) Discard(db *gorm.DB, event publish.PublishEventInterface) error {
	if event, ok := event.(*publish.PublishEvent); ok {
		scope := db.NewScope("")
		if err := json.Unmarshal([]byte(event.Argument), &e); err == nil {
			var conditions []string
			originalTable := scope.Quote(publish.OriginalTableName(e.Table))
			draftTable := scope.Quote(publish.DraftTableName(e.Table))
			for _, primaryKey := range e.PrimaryKeys {
				conditions = append(conditions, fmt.Sprintf("%v.%v = %v.%v", originalTable, primaryKey, draftTable, primaryKey))
			}
			sql := fmt.Sprintf("UPDATE %v SET position = (select position FROM %v WHERE %v);", draftTable, originalTable, strings.Join(conditions, " AND "))
			return db.Exec(sql).Error
		} else {
			return err
		}
	}
	return errors.New("invalid publish event")
}

// Create creates a new release.
func (s *releasesService) Create(ctx context.Context, db *gorm.DB, r *Release) (*Release, error) {
	// Lock all releases for the given application to ensure that the
	// release version is updated automically.
	if err := db.Exec(`select 1 from releases where app_id = ? for update`, r.App.ID).Error; err != nil {
		return r, err
	}

	// During rollbacks, we can just provide the existing Formation for the
	// old release. For new releases, we need to create a new formation by
	// merging the formation from the extracted Procfile, and the Formation
	// from the existing release.
	if r.Formation == nil {
		if err := buildFormation(db, r); err != nil {
			return r, err
		}
	}

	return releasesCreate(db, r)
}

// Create creates a new release then submits it to the scheduler.
func (s *releasesService) Create(ctx context.Context, db *gorm.DB, r *Release) (*Release, error) {
	// Lock all releases for the given application to ensure that the
	// release version is updated automically.
	if err := db.Exec(`select 1 from releases where app_id = ? for update`, r.App.ID).Error; err != nil {
		return r, err
	}

	// Create a new formation for this release.
	if err := createFormation(db, r); err != nil {
		return r, err
	}

	r, err := releasesCreate(db, r)
	if err != nil {
		return r, err
	}

	// Schedule the new release onto the cluster.
	return r, s.Release(ctx, r)
}

func upgrade_v2(db gorm.DB) {
	// Remove IsExecutable and IsTemplate from Resource
	// Add Type column
	logging.Info("Migrating 1 => 2")

	db.Exec(`ALTER TABLE "resources" ADD COLUMN res_type char(3)`)

	// If something is executable, it stays that way. Otherwise it becomes a template
	db.Exec("UPDATE resources SET res_type = ? WHERE is_executable = ?", plugin.ResJavascriptCode, true)
	db.Exec("UPDATE resources SET res_type = ? WHERE is_executable = ?", plugin.ResTemplate, false)

	db.Exec(`ALTER TABLE "resources" ALTER COLUMN res_type SET NOT NULL`)

	db.Model(&plugin.Resource{}).DropColumn("is_executable")
	db.Model(&plugin.Resource{}).DropColumn("is_template")
}

func ArchiveTokenAfterCutOffTime(db *gorm.DB) {

	tokenCutOffTimeString, err := GetAppProperties("TokenCutOffTime")
	if err != nil {
		ERROR.Println(err.Error())
	}

	tokenCutOffTimeInteger, err := strconv.Atoi(tokenCutOffTimeString)
	if err != nil {
		ERROR.Println("String to integer conversion failed for TokenCutOffTime, reverting to default value = 30 minutes")
		tokenCutOffTimeInteger = 30
	}

	TRACE.Println("Token archiving started at: " + time.Now().String())

	db.Exec("UPDATE TABLE tokens SET active = ? WHERE (SELECT EXTRACT(MINUTE FROM (? - expires_at)) FROM tokens) > ?", false, time.Now().UTC(), tokenCutOffTimeInteger)

	db.Exec("INSERT INTO archived_tokens (token, user_id, key, created_at, last_accessed_at, expires_at, device_type_id, active)(SELECT (token, user_id, key, created_at, last_accessed_at, expires_at, device_type_id, active) WHERE active = ?)", false)

	db.Exec("DELETE FROM tokens WHERE active = ?", false)

	TRACE.Println("Token archiving completed at: " + time.Now().String())
}

func atomicMethod(db *gorm.DB, num int, done chan bool) {
	for i := 0; i < num; i++ {
		panicOnError(db.Exec("UPDATE test_table SET counter = counter - 1").Error)
	}
	done <- true
}

		}

		l.Println("created:", a)
	},
}

var dbEmpty = &cobra.Command{
	Use:   "db:empty",
	Short: "Empty the database",
	Run: func(cmd *cobra.Command, args []string) {
		if db == nil {
			LoadEnv(true)
		}

		if err := db.Exec(`
			DROP SCHEMA public CASCADE;
			CREATE SCHEMA public;
		`).Error; err != nil {
			l.Fatal("Could not empty database.", err)
		}
	},
}

var dbMigrate = &cobra.Command{
	Use:   "db:migrate",
	Short: "Migrate the databse to the latest version",
	Run: func(cmd *cobra.Command, args []string) {
		if db == nil {
			LoadEnv(true)
		}

		var err error

func portsUnassign(db *gorm.DB, app *App) error {
	return db.Exec(`update ports set app_id = null where app_id = ?`, app.ID).Error
}

func (question *Question) ToDatabase(db gorm.DB, options *Options) (err error) {
	var q Question
	var id int64 = 0

	db.Where(
		&Question{
			SrcIp:    question.SrcIp,
			DstIp:    question.DstIp,
			Question: question.Question,
		},
	).First(&q)

	if q.Id != 0 {

		db.Model(&q).Update(
			&Question{
				UpdatedAt: time.Now().Unix(),
				SeenCount: q.SeenCount + 1,
			},
		)

		id = q.Id

	} else {
		options.Client.QuestionCount = options.Client.QuestionCount + 1

		db.Exec("UPDATE clients SET question_count=? WHERE id=?",
			options.Client.QuestionCount, options.Client.Id)

		var time int64 = time.Now().Unix()

		question.SeenCount = 1

		question.CreatedAt = time
		question.UpdatedAt = time

		db.Table("questions").Create(question)

		id = question.Id
	}

	go func() {

		for _, a := range question.Answers {

			var aa Answer

			db.Table("answers").Where(
				&Answer{
					Name:       a.Name,
					Data:       a.Data,
					Record:     a.Record,
					Class:      a.Class,
					QuestionId: id,
					ClientId:   options.Client.Id,
				},
			).First(&aa)

			if aa.Id != 0 {
				db.Model(&aa).Update(
					&Answer{
						SeenCount: aa.SeenCount + 1,
						Active:    true,
					},
				)
			} else {

				a.ClientId = options.Client.Id
				a.QuestionId = id
				a.SeenCount = 1
				a.Active = true

				db.Table("answers").Create(a)
			}
		}

		var olda []Answer

		db.Where(&Answer{
			QuestionId: id,
			ClientId:   options.Client.Id,
		}).Find(&olda)

		for _, a := range olda {
			index := contains(a, question.Answers)

			if index == -1 {
				a.Active = false
				a.UpdatedAt = time.Now().Unix()
				db.Save(&a)
			}
		}

	}()

	return nil
}

	BeforeEach(func() {
		api = api2go.NewAPIWithBaseURL("v0", "http://localhost:31415")
		var err error
		db, err = storage.InitDB()
		if err != nil {
			panic(err)
		}
		userStorage := storage.NewUserStorage(db)
		chocStorage := storage.NewChocolateStorage(db)
		api.AddResource(model.User{}, resource.UserResource{ChocStorage: chocStorage, UserStorage: userStorage})
		api.AddResource(model.Chocolate{}, resource.ChocolateResource{ChocStorage: chocStorage, UserStorage: userStorage})
		rec = httptest.NewRecorder()
	})
	AfterEach(func() {
		db.Exec("DROP TABLE chocolates")
		db.Exec("DROP TABLE users")
		db.Close()
	})

	var createUser = func() {
		rec = httptest.NewRecorder()
		req, err := http.NewRequest("POST", "/v0/users", strings.NewReader(`
		{
			"data": {
				"type": "users",
				"attributes": {
					"user-name": "marvin"
				}
			}
		}

func (dbM *DB) setDB(db *gorm.DB) {
	_ = db.Exec("set time zone 'utc';")
}