Golang DB.Exec方法代码示例

// RecoverSQLLogError re-executes failed sql queries in sql error log file from specified path.
// It returns the number of failed -reexec queries, and new failed
// queries will be written to the file
//
// The format of error file is:
// 	Error description - $$$error$$$SQL_QUERY$$$error$$$
// Therefore only get statements enclosed by special `$$$error$$$`
func RecoverSQLLogError(sqlErrFilePath dna.String, db *sqlpg.DB) dna.Int {
	var errCount = 0
	var errStrings = dna.StringArray{}
	b, err := ioutil.ReadFile(sqlErrFilePath.String())
	if err != nil {
		panic(err)
	}
	data := dna.String(string(b))
	// dna.Log("\n", data.Length())
	sqlArr := data.FindAllString(`(?mis)\$\$\$error\$\$\$.+?\$\$\$error\$\$\$`, -1)
	// dna.Log("\nTOTAL SQL STATEMENTS FOUND:", sqlArr.Length())
	for _, val := range sqlArr {
		sqlStmtArr := val.FindAllStringSubmatch(`(?mis)\$\$\$error\$\$\$(.+?)\$\$\$error\$\$\$`, -1)
		if len(sqlStmtArr) > 0 {
			_, err := db.Exec(sqlStmtArr[0][1].String())
			if err != nil {
				if dna.String(err.Error()).Contains(`duplicate key value violates unique constraint`) == false {
					errCount += 1
					errStrings.Push("$$$error$$$" + sqlStmtArr[0][1] + "$$$error$$$")
				}
			}
		}
	}
	if errCount == 0 {
		err = ioutil.WriteFile(sqlErrFilePath.String(), []byte{}, 0644)
	} else {
		err = ioutil.WriteFile(sqlErrFilePath.String(), []byte(errStrings.Join("\n").String()), 0644)
	}
	if err != nil {
		panic(err)
	}
	return dna.Int(errCount)
}

func (apiSong *APIFullSong) Save(db *sqlpg.DB) error {

	var queries = dna.StringArray{}
	var err error

	// Getting artist queries
	artists := apiSong.ToArtists()
	for _, artist := range artists {
		queries.Push(sqlpg.GetInsertIgnoreStatement(sqlpg.GetTableName(artist), artist, "id", artist.Id, false))
	}

	// Getting album query
	album := apiSong.ToAlbum()
	queries.Push(sqlpg.GetInsertIgnoreStatement(sqlpg.GetTableName(album), album, "id", album.Id, false))

	// Getting song query
	song := apiSong.ToSong()
	queries.Push(sqlpg.GetInsertStatement(sqlpg.GetTableName(song), song, false))

	for _, query := range queries {
		_, err = db.Exec(query.String())
	}

	if err != nil {
		errQueries := dna.StringArray(queries.Map(func(val dna.String, idx dna.Int) dna.String {
			return "$$$error$$$" + val + "$$$error$$$"
		}).([]dna.String))
		return errors.New(err.Error() + errQueries.Join("\n").String())
	} else {
		return nil
	}

}

func (episode *Episode) Save(db *sqlpg.DB) error {
	insertStmt := getInsertStmt(episode, dna.Sprintf("WHERE NOT EXISTS (SELECT 1 FROM %v WHERE movie_id=%v and ep_id=%v)", getTableName(episode), episode.MovieId, episode.EpId))
	_, err := db.Exec(insertStmt.String())
	if err != nil {
		err = errors.New(err.Error() + " $$$error$$$" + insertStmt.String() + "$$$error$$$")
	}
	return err
}

func (movie *Movie) Save(db *sqlpg.DB) error {
	insertStmt := getInsertStmt(movie, dna.Sprintf("WHERE NOT EXISTS (SELECT 1 FROM %v WHERE id=%v)", getTableName(movie), movie.Id))
	_, err := db.Exec(insertStmt.String())
	if err != nil {
		err = errors.New(err.Error() + " $$$error$$$" + insertStmt.String() + "$$$error$$$")
	}
	return err
}

func IsValidTable(tblName dna.String, db *sqlpg.DB) dna.Bool {
	_, err := db.Exec("select * from " + tblName.String() + " limit 0")
	if err == nil {
		return true
	} else {
		return false
	}
}

func SaveLastestMovieCurrentEps(db *sqlpg.DB, tblName dna.String, logger *terminal.Logger) {
	for mvid, currentEp := range LastestMovieCurrentEps {
		query := "UPDATE " + tblName + " SET current_eps=" + currentEp.ToString()
		query += " WHERE id=" + mvid.ToString() + ";"
		_, err := db.Exec(query.String())
		if err != nil {
			logger.Println("$$$error$$$" + query + "$$$error$$$")
		}
	}
}

// SaveNewAlbums finds all new albums available in new found songs.
// New albums depends on aggregate funcs from the last songid.
// It returns an error or nil and the number of new albums inserted into DB
func SaveNewAlbums(db *sqlpg.DB) (nAlbumsInserted dna.Int, err error) {
	if LastSongId <= 0 {
		return 0, errors.New("Last song id has to be greater than Zero (0)")
	}
	query := dna.Sprintf(`insert into %v 
		(title,artists,topics,href,nsongs,coverart,producer,downloads,plays,date_released,date_created,songids) 
		(
		select title,array_agg_csn_artist_cat(artists) as artists,array_agg_csn_cat(topics) as topics, min(link) as href ,
		sum(nsongs)::int8 as nsongs,max(coverart) as coverart, max(producer) as producer,
		floor(avg(downloads))::int8 as downloads, floor(avg(plays))::int8 as plays,
		max(date_released) as date_released , max(date_created) as date_created, array_agg_cat_unique(songids) as songids  
		from 
		(
		select max(album_title) as title,array_agg_csn_artist_cat(artists) as artists,
		array_agg_csn_cat(topics) as topics , min(album_href) as link, 
		count(*) as nsongs, album_coverart as coverart, max(producer) as producer, 
		floor(avg(downloads)) as downloads, floor(avg(plays)) as plays, 
		max(date_released) as date_released,max(date_created) as date_created, array_agg(id) as songids 
		from %v
		where id > %v 
		and album_title <> '' 
		and album_href <> '' 
		group by album_coverart 
		) as anbinh
		group by title
		)`, "csnalbums", "csnsongs", LastSongId)
	// dna.Log(query)
	ret, err := db.Exec(query.String())
	if err != nil {
		return 0, err
	} else {
		nAl, err := ret.RowsAffected()
		nAlbumsInserted = dna.Int(nAl)
		if err != nil {
			return 0, nil
		} else {
			return nAlbumsInserted, nil
		}

	}
}