Golang DB.MustExec方法代码示例

func evolve(db *sqlx.DB, startVersion string, appName string) {
	files, _ := ioutil.ReadDir("./schema")
	sort.Sort(ByName(files))
	i := 0
	if startVersion != "-1" {
		for ; i < len(files); i++ {
			if stripExt(files[i].Name()) == startVersion {
				i++
				break
			}
		}
		files = files[i:]
	}
	for _, file := range files {
		log.Println("running file:", "./schema/"+file.Name())
		commands, err := ioutil.ReadFile("./schema/" + file.Name())
		if err != nil {
			log.Fatal(err)
		}
		_ = db.MustExec(string(commands))
		if err != nil {
			log.Fatal(err)
		}
		_ = db.MustExec(fmt.Sprintf("update %s_meta set version=$1", appName), stripExt(file.Name()))
		if err != nil {
			log.Fatal(err)
		}
	}
}

func initDB(db *sqlx.DB) {
	db.MustExec(schema)
	tx := db.MustBegin()
	tx.MustExec("INSERT into event (date, title, description, font) values ($1, $2, $3, $4)", timeToString(time.Now()), "Do something fun!", "description goes here", "Helvetica")
	tx.MustExec("INSERT into event (date, title, description, font) values ($1, $2, $3, $4)", timeToString(time.Now().Add(time.Hour*24)), "Do something Else!", "discription goes here", "Helvetica")
	tx.Commit()
}

func MustCreate(d *sqlx.DB) *postgresStore {
	// Set the timezone for the store. This is needed for the logic here.
	// This obviously assumes that noone else will change this.
	// TODO: make this more robust.
	d.MustExec(setTimeZoneStr)
	s := &postgresStore{db: d}
	return s
}

func buildTables(db *sqlx.DB) {
	userTable :=
		`CREATE TABLE users (
	id text NOT NULL,
	name text,
	email text,
	jwt text,
	passwordhash text);`

	db.MustExec(userTable)
}

func insertNotices(conn *sqlx.DB, results []NoticeResult) {
	sql := conn.Rebind(`
		INSERT INTO notice(id, published)
		SELECT ?, ? 
		WHERE NOT EXISTS (SELECT 1 FROM notice WHERE id = ?)`)
	for _, result := range results {
		conn.MustExec(
			sql, result.DocumentNumber, result.Published,
			result.DocumentNumber)
	}
	insertAgencies(conn, results)
}

func insertAgencies(conn *sqlx.DB, results []NoticeResult) {
	delete := conn.Rebind("DELETE FROM notice_agency WHERE notice_id = ?")
	insert := conn.Rebind(`
		INSERT INTO notice_agency (notice_id, agency)
		VALUES (?, ?)`)
	for _, result := range results {
		conn.MustExec(delete, result.DocumentNumber)
		for _, agency := range result.Agencies {
			conn.MustExec(insert, result.DocumentNumber, agency)
		}
	}
}

func CreateDBSchema(db *sqlx.DB) {
	db.MustExec(`
		CREATE TABLE IF NOT EXISTS "todo_items" (
			"id" integer,
			"title" varchar(255),
			"description" varchar(255),
			"done" bool,
			"done_at" datetime,
			"created_at" datetime,
			"updated_at" datetime,
			"group_id" integer ,

			 PRIMARY KEY ("id")
		 );
	`)
	db.MustExec(`
		CREATE INDEX IF NOT EXISTS idx_todo_items_group_id ON "todo_items"("group_id");
	`)

	db.MustExec(`
		CREATE TABLE IF NOT EXISTS "todo_groups" (
			"id" integer,
			"title" varchar(255),
			"created_at" datetime,
			"updated_at" datetime,
			"list_id" integer ,

			 PRIMARY KEY ("id")
		 );
	`)
	db.MustExec(`
		CREATE INDEX IF NOT EXISTS idx_todo_groups_list_id ON "todo_groups"("list_id");
	`)

	db.MustExec(`
		CREATE TABLE IF NOT EXISTS "todo_lists" (
			"id" integer,
			"title" varchar(255),
			"description" varchar(255),
			"created_at" datetime,
			"updated_at" datetime ,

			 PRIMARY KEY ("id")
		 );
	`)
}

func Export(w *sync.WaitGroup, c chan string, db *sqlx.DB, format *string) {
	w.Add(1)
	defer w.Done()
	helpers.DropAndCreateTable(schema, tableName, db)

	var format2 string
	format2 = *format
	fileName, err2 := helpers.SearchFile(tableName, format2)
	if err2 != nil {
		fmt.Println("Error searching file:", err2)
		return
	}

	pathToFile := format2 + "/" + fileName

	// Подсчитываем, сколько элементов нужно обработать
	//fmt.Println("Подсчет строк")
	// _, err := helpers.CountElementsInXML(pathToFile, elementName)
	// if err != nil {
	// 	fmt.Println("Error counting elements in XML file:", err)
	// 	return
	// }
	//fmt.Println("\nВ ", elementName, " содержится ", countedElements, " строк")

	xmlFile, err := os.Open(pathToFile)
	if err != nil {
		fmt.Println("Error opening file:", err)
		return
	}

	defer xmlFile.Close()

	decoder := xml.NewDecoder(xmlFile)
	total := 0
	var inElement string
	for {
		// Read tokens from the XML document in a stream.
		t, _ := decoder.Token()
		if t == nil {
			break
		}
		// Inspect the type of the token just read.
		switch se := t.(type) {
		case xml.StartElement:
			// If we just read a StartElement token
			inElement = se.Name.Local

			if inElement == elementName {
				total++
				var item XmlObject

				// decode a whole chunk of following XML into the
				// variable item which is a ActualStatus (se above)
				err = decoder.DecodeElement(&item, &se)
				if err != nil {
					fmt.Println("Error in decode element:", err)
					return
				}
				query := "INSERT INTO " + tableName + " (center_st_id, name) VALUES ($1, $2)"
				db.MustExec(query, item.CENTERSTID, item.NAME)

				c <- helpers.PrintRowsAffected(elementName, total)
			}
		default:
		}

	}

	//fmt.Printf("\nTotal processed items in "+elementName+": %d \n", total)
}

func Export(w *sync.WaitGroup, c chan string, db *sqlx.DB, format *string) {
	w.Add(1)
	defer w.Done()
	helpers.DropAndCreateTable(schema, tableName, db)

	var format2 string
	format2 = *format
	fileName, err2 := helpers.SearchFile(tableName+"_", format2)
	if err2 != nil {
		fmt.Println("Error searching file:", err2)
		return
	}

	pathToFile := format2 + "/" + fileName

	xmlFile, err := os.Open(pathToFile)
	if err != nil {
		fmt.Println("Error opening file:", err)
		return
	}

	defer xmlFile.Close()

	decoder := xml.NewDecoder(xmlFile)
	total := 0
	var inElement string
	for {
		// Read tokens from the XML document in a stream.
		t, _ := decoder.Token()
		if t == nil {
			break
		}
		// Inspect the type of the token just read.
		switch se := t.(type) {
		case xml.StartElement:
			// If we just read a StartElement token
			inElement = se.Name.Local

			if inElement == elementName {
				total++
				var item XmlObject

				// decode a whole chunk of following XML into the
				// variable item which is a ActualStatus (se above)
				err = decoder.DecodeElement(&item, &se)
				if err != nil {
					fmt.Println("Error in decode element:", err)
					return
				}

				query := `INSERT INTO ` + tableName + ` (house_guid,
					postal_code,
					ifns_fl,
					terr_ifns_fl,
					ifns_ul,
					terr_ifns_ul,
					okato,
					oktmo,
					update_date,
					house_num,
					est_status,
					build_num,
					struc_num,
					str_status,
					house_id,
					ao_guid,
					start_date,
					end_date,
					stat_status,
					norm_doc,
					counter
					) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
						$11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
						$21)`

				db.MustExec(query,
					item.HOUSEGUID,
					item.POSTALCODE,
					item.IFNSFL,
					item.TERRIFNSFL,
					item.IFNSUL,
					item.TERRIFNSUL,
					item.OKATO,
					item.OKTMO,
					item.UPDATEDATE,
					item.HOUSENUM,
					item.ESTSTATUS,
					item.BUILDNUM,
					item.STRUCNUM,
					item.STRSTATUS,
					item.HOUSEID,
					item.AOGUID,
					item.STARTDATE,
					item.ENDDATE,
					item.STATSTATUS,
					item.NORMDOC,
					item.COUNTER)

				c <- helpers.PrintRowsAffected(elementName, total)
			}
//.........这里部分代码省略.........

func (p *Post) Save(db *sqlx.DB) {

	db.MustExec("UPDATE posts SET title=$1,shortmessage=$2, body=$3,modified=NOW(),created=$8,status=$4,tags=$5,slug=$6 WHERE id=$7",
		p.Title, p.ShortMessage, p.Body, p.Status, p.Tags, p.Slug, p.Id, p.Created)
}

func DeletePost(db *sqlx.DB, post_id int) {

	db.MustExec("DELETE FROM posts WHERE id=$1", post_id)
}

// NewSQLTodo returns a new sql todo manager
func NewSQLTodo(db *sqlx.DB) TodoDatabase {
	db.MustExec(createSchema)
	return &SQLTodo{db}
}