// 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")
}
}
// 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")
}
}
// 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
}
}
// 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
}
// 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
}
}
// 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")
}
}
// 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
}