func (sv *SongVideo) Save(db *sqlpg.DB) error {
if sv.Item != nil {
return db.InsertIgnore(sv.Item)
} else {
return errors.New("Item field of struct is nil")
}
}
// SelectMissingIds accepts a table name as an input and a list of ids as a source.
// It returns a new list of ids that does not exist in the destination table
//
// * tblName : a table name
// * srcIds : a source ids
// * db : a pointer to connected databased
// * Returns a new list of ids which are not from the specified table
//
// The format of sql statement is:
// WITH dna (id) AS (VALUES (5),(6),(7),(8),(9))
// SELECT id FROM dna WHERE NOT EXISTS
// (SELECT 1 from ziartists WHERE id=dna.id)
func SelectMissingIds(tblName dna.String, srcIds *dna.IntArray, db *sqlpg.DB) (*dna.IntArray, error) {
if srcIds.Length() > 0 {
val := dna.StringArray(srcIds.Map(func(val dna.Int, idx dna.Int) dna.String {
return "(" + val.ToString() + ")"
}).([]dna.String))
selectStmt := "with dna (id) as (values " + val.Join(",") + ") \n"
selectStmt += "SELECT id FROM dna WHERE NOT EXISTS\n (SELECT 1 from " + tblName + " WHERE id=dna.id)"
ids := &[]dna.Int{}
err := db.Select(ids, selectStmt)
switch {
case err != nil:
return nil, err
case err == nil && ids != nil:
slice := dna.IntArray(*ids)
return &slice, nil
case err == nil && ids == nil:
return &dna.IntArray{}, nil
default:
panic("Default case triggered. Case is not expected. Cannot select non existed ids")
}
} else {
return nil, errors.New("Empty input array")
}
}
// 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)
}
// SelectMissingKeys accepts a table name as an input and a list of keys as a source.
// It returns a new list of keys that does not exist in the destination table
//
// * tblName : a table name
// * srcKeys : a source keys
// * db : a pointer to connected databased
// * Returns a new list of keys which are not from the specified table
//
// Notice: Only applied to a table having a column named "key".
// The column has to be indexed to ensure good performance
//
// The format of sql statement is:
// with dna (key) as (values ('43f3HhhU6DGV'),('uFfgQhKbwAfN'),('RvFDlckJB5QU'),('uIF7rwd5wo4p'),('Kveukbhre1ry'),('oJ1lzAlKwJX6'),('43f3HhhU6DGV'),('uFfgQhKbwAfN'),('hfhtyMdywMau'),('PpZuccjYqy1b'))
// select key from dna where key not in
// (select key from nctalbums where key in ('43f3HhhU6DGV','uFfgQhKbwAfN','RvFDlckJB5QU','uIF7rwd5wo4p','Kveukbhre1ry','oJ1lzAlKwJX6','43f3HhhU6DGV','uFfgQhKbwAfN','hfhtyMdywMau','PpZuccjYqy1b'))
func SelectMissingKeys(tblName dna.String, srcKeys *dna.StringArray, db *sqlpg.DB) (*dna.StringArray, error) {
if srcKeys.Length() > 0 {
val := dna.StringArray(srcKeys.Map(func(val dna.String, idx dna.Int) dna.String {
return `('` + val + `')`
}).([]dna.String))
val1 := dna.StringArray(srcKeys.Map(func(val dna.String, idx dna.Int) dna.String {
return `'` + val + `'`
}).([]dna.String))
selectStmt := "with dna (key) as (values " + val.Join(",") + ") \n"
selectStmt += "select key from dna where key not in \n(select key from " + tblName + " where key in (" + val1.Join(",") + "))"
keys := &[]dna.String{}
err := db.Select(keys, selectStmt)
switch {
case err != nil:
return nil, err
case err == nil && keys != nil:
slice := dna.StringArray(*keys)
return &slice, nil
case err == nil && keys == nil:
return &dna.StringArray{}, nil
default:
panic("Default case triggered. Case is not expected. Cannot select non existed keys")
}
} else {
return nil, errors.New("Empty input array")
}
}
func (lyric *Lyric) Save(db *sqlpg.DB) error {
song := NewSong()
song.Id = lyric.Id
song.Lyric = lyric.Content
song.HasLyric = true
return db.Update(song, "id", "lyric", "has_lyric")
}
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 (soca *SongCategory) Save(db *sqlpg.DB) error {
var last error
for _, song := range *(soca.Songs) {
// dna.Log(song)
last = db.Update(song, "id", "category")
}
return last
}
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$$$")
}
}
}
func (sf *APISongFreaksAlbum) Save(db *sqlpg.DB) error {
album, err := sf.ToAlbum()
if err != nil {
return err
} else {
return db.Update(album, "id", "ratings", "songids", "review_author", "review")
}
}
// Save stores some fields to DB.
func (svu *SongVideoUpdater) Save(db *sqlpg.DB) error {
switch svu.Item.(type) {
case *Song:
return db.Update(svu.Item, "id", "title", "artists", "authors", "topics", "album_title", "album_href", "album_coverart", "producer", "lyric", "date_released", "date_created", "is_lyric")
case *Video:
return db.Update(svu.Item, "id", "title", "artists", "authors", "topics", "producer", "lyric", "date_released", "date_created", "is_lyric")
default:
panic("Invalid type of SongVideoUpdater.Item")
}
}
// GetMaxId returns max id of a specified table.
func GetMaxId(tableName dna.String, db *sqlpg.DB) (dna.Int, error) {
var maxid dna.Int
err := db.QueryRow("SELECT max(id) FROM " + tableName).Scan(&maxid)
switch {
case err == sqlpg.ErrNoRows:
return 0, err
case err != nil:
return 0, err
default:
return maxid, nil
}
}
// GetLastedChecktime returns a map which maps site to lasted checktime.
// Ex: "nssongs" => 2014-03-17 12:09:37
func GetLastedChecktime(db *sqlpg.DB) (map[dna.String]time.Time, error) {
siteCts := &[]site_checktime{}
ret := make(map[dna.String]time.Time)
err := db.Select(siteCts, "select * from get_lasted_checktime();")
if err == nil {
for _, sitect := range *siteCts {
ret[sitect.Site] = sitect.Checktime
// dna.Log(sitect.Site, sitect.Checktime.Format(utils.DefaultTimeLayout))
}
return ret, nil
} else {
return nil, err
}
}
// SelectLastMissingIds returns a list of ids which is missing from a table.
// nLastIds is the total number of last ids in the table.
//
// Example: 3 last ids from table nssongs are 1,4,5. Therefore, the missing
// ids whose range is from 1->5 are 2,3.
func SelectLastMissingIds(tblName dna.String, nLastIds dna.Int, db *sqlpg.DB) (*dna.IntArray, error) {
var min, max dna.Int
query := dna.Sprintf("SELECT min(id), max(id) FROM (SELECT id FROM %v ORDER BY id DESC LIMIT %v) as AB", tblName, nLastIds)
// dna.Log(query)
db.QueryRow(query).Scan(&min, &max)
// totalItem := max - min + 1
// var ids = make(dna.IntArray, totalItem, totalItem+100)
// var idx = 0
// for i := min; i < max; i++ {
// ids[idx] = i
// idx += 1
// }
return SelectMissingIdsWithRange(tblName, min, max, db)
}
// GetMoviesCurrentEps returns a map of MovideId and CurrentEps
// if CurrentEps is less than MaxEp.
// it returns an error if available.
//
// This function is used when we need to find all possible movie ids
// to update.
func GetMoviesCurrentEps(db *sqlpg.DB, tblName dna.String) (map[dna.Int]dna.Int, error) {
var movieCurrentEps = make(map[dna.Int]dna.Int)
ids := &[]dna.Int{}
currentEps := &[]dna.Int{}
err := db.Select(ids, dna.Sprintf(`SELECT id from %v where current_eps < max_ep order by id DESC`, tblName))
if err != nil {
return nil, err
}
err = db.Select(currentEps, dna.Sprintf(`SELECT current_eps from %v where current_eps < max_ep order by id DESC`, tblName))
if err != nil {
return nil, err
}
if len(*currentEps) != len(*ids) {
return nil, errors.New("Length of IDs and CurrentEps is not correspondent")
}
for idx, movieid := range *ids {
movieCurrentEps[movieid] = (*currentEps)[idx]
}
return movieCurrentEps, nil
}
func (alca *AlbumCategory) Save(db *sqlpg.DB) error {
var last error
var aids = dna.IntArray{}
albums := &[]Album{}
for _, album := range *(alca.Albums) {
aids.Push(album.Id)
// dna.Log(album)
}
query := "SELECT id, topics, genres from nsalbums WHERE id IN (" + aids.Join(",") + ")"
// dna.Log(query)
err := db.Select(albums, query)
if err != nil {
dna.Log(query, alca, *alca.Albums)
dna.PanicError(err)
}
for _, album := range *(alca.Albums) {
foundIndex := 0
for j, anotherAlbum := range *(albums) {
if album.Id == anotherAlbum.Id {
foundIndex = j
}
}
if foundIndex < len(*albums) {
cat := album.Category.Concat((*albums)[foundIndex].Topics).Concat((*albums)[foundIndex].Genres).Unique()
album.Category = cat.Filter(func(v dna.String, i dna.Int) dna.Bool {
if v != "" {
return true
} else {
return false
}
})
}
last = db.Update(album, "id", "category")
}
return last
}
// 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
}
}
}
// SelectMissingIds accepts a table name as an input and a range as a source.
// It returns a new list of ids that does not exist in the destination table
//
// * tblName : a table name
// * head, tail : first and last number defines a range
// * db : a pointer to connected databased
// * Returns a new list of ids which are not from the specified table
//
// The format of sql statement is:
// SELECT id FROM generate_series(5,9) id
// WHERE NOT EXISTS (SELECT 1 from ziartists where id = id.id)
func SelectMissingIdsWithRange(tblName dna.String, head, tail dna.Int, db *sqlpg.DB) (*dna.IntArray, error) {
if head > tail {
panic("Cannot create range: head has to be less than tail")
}
selectStmt := dna.Sprintf("SELECT id FROM generate_series(%v,%v) id \n", head, tail)
selectStmt += "WHERE NOT EXISTS (SELECT 1 from " + tblName + " where id = id.id)"
ids := &[]dna.Int{}
err := db.Select(ids, selectStmt)
switch {
case err != nil:
return nil, err
case err == nil && ids != nil:
slice := dna.IntArray(*ids)
return &slice, nil
case err == nil && ids == nil:
return &dna.IntArray{}, nil
default:
panic("Default case triggered. Case is not expected. Cannot select non existed ids")
}
}
// SelectNewSidsFromAlbums returns a slice of songids from a table since the last specified time.
// The table has to be album type and has a column called songids.
func SelectNewSidsFromAlbums(tblName dna.String, lastTime time.Time, db *sqlpg.DB) *dna.IntArray {
idsArrays := &[]dna.IntArray{}
year := dna.Sprintf("%v", lastTime.Year())
month := dna.Sprintf("%d", lastTime.Month())
day := dna.Sprintf("%v", lastTime.Day())
checktime := dna.Sprintf("'%v-%v-%v'", year, month, day)
query := dna.Sprintf("SELECT songids FROM %s WHERE checktime >= %s", tblName, checktime)
// dna.Log(query)
err := db.Select(idsArrays, query)
dna.PanicError(err)
ids := &dna.IntArray{}
if idsArrays != nil {
for _, val := range *idsArrays {
for _, id := range val {
ids.Push(id)
}
}
return ids
} else {
return nil
}
}
// updateEmptyTitles returns an error if there is no missing titles
// of songs or videos.
func updateEmptyTitles(db *sqlpg.DB, siteConf *SiteConfig, lastId dna.Int) bool {
var queryPat dna.String = "select id from %v where id > %v and title = ''"
songids := &[]dna.Int{}
songQuery := dna.Sprintf(queryPat, "csnsongs", lastId)
db.Select(songids, songQuery)
videoQuery := dna.Sprintf(queryPat, "csnvideos", lastId)
videoids := &[]dna.Int{}
db.Select(videoids, videoQuery)
ids := dna.IntArray(*songids).Concat(dna.IntArray(*videoids))
if ids.Length() > 0 {
dna.Log(ids)
state := NewStateHandlerWithExtSlice(new(csn.SongVideoUpdater), &ids, siteConf, db)
Update(state)
RecoverErrorQueries(SqlErrorLogPath, db)
return false
} else {
// dna.Log("No record needs to be updated.")
return true
}
// return donec
}
func (song *Song) Save(db *sqlpg.DB) error {
return db.InsertIgnore(song)
}
func (album *Album) Save(db *sqlpg.DB) error {
return db.InsertIgnore(album)
}
func (tv *TV) Save(db *sqlpg.DB) error {
return db.InsertIgnore(tv)
}
func (album *Album) Save(db *sqlpg.DB) error {
// return db.Update(album, "id", "description")
return db.InsertIgnore(album)
}
func (song *Song) Save(db *sqlpg.DB) error {
// return db.Update(song, "id", "artist_id", "video_id")
return db.InsertIgnore(song)
}
func (artist *Artist) Save(db *sqlpg.DB) error {
return db.InsertIgnore(artist)
}
func (video *Video) Save(db *sqlpg.DB) error {
// return db.Update(video, "id", "lyric")
return db.InsertIgnore(video)
}
func (video *Video) Save(db *sqlpg.DB) error {
return db.InsertIgnore(video)
}