// 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 (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
}
}
// This function will decode a cipher string into id.
func Decrypt(cipher dna.String) dna.Int {
arr := dna.StringArray{cipher[0:2], cipher[2:3], cipher[3:4], cipher[4:6], cipher[6:7], cipher[7:8], cipher[8:10]}.Filter(
func(value dna.String, index dna.Int) dna.Bool {
return value != ""
})
return dna.IntArray(arr.Map(func(v dna.String, i dna.Int) dna.Int {
return ns[6-i].IndexOf(v)
}).([]dna.Int)).Join("").ToInt()
}
// ExecQueriesInTransaction executes queries in a transaction.
// If one statement fails, the whole queries cannot commit.
//
// The returned error is nil if there is no error.
// If an error occurs, each statement will be enclosed in
// format $$$error$$$.
// $$$error$$$ Your Custom Query $$$error$$$
//
// This function is seen in songfreaks and allmusic sites.
func ExecQueriesInTransaction(db *DB, queries *dna.StringArray) error {
var err error
globalSqlTransactoNo += 1
// tx, err := db.Begin()
// if err != nil {
// dna.Log("Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo).String() + err.Error() + " Could not create transaction\n")
// }
for idx, query := range *queries {
_, err = db.Exec(query.String())
// _, err = tx.Exec(query.String())
if err != nil {
dna.Log(dna.Sprintf("DNAError: Query series No: %v - %v - %v - %v\n", dna.Sprintf("%v", globalSqlTransactoNo), idx, err.Error(), "Could not execute the statement"))
}
// stmt, err := tx.Prepare(query.String())
// if err != nil {
// dna.Log(dna.Sprintf("DNAError Transaction No: %v - %v - %v - %v \n", dna.Sprintf("%v", globalSqlTransactoNo), idx, err.Error(), "Could not prepare"))
// } else {
// _, err = stmt.Exec()
// if err != nil {
// dna.Log(dna.Sprintf("DNAError: Transaction No: %v - %v - %v - %v\n", dna.Sprintf("%v", globalSqlTransactoNo), idx, err.Error(), "Could not execute the prepared statement"))
// }
// err = stmt.Close()
// if err != nil {
// dna.Log("Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo).String() + err.Error() + " Could not close\n")
// }
// }
}
// err = tx.Commit()
// if err != nil {
// dna.Log("Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo).String() + err.Error() + " Could not commit transaction\n")
// }
if err != nil {
errQueries := dna.StringArray(queries.Map(func(val dna.String, idx dna.Int) dna.String {
return "Transaction No:" + dna.Sprintf("%v", globalSqlTransactoNo) + " $$$error$$$" + val + "$$$error$$$"
}).([]dna.String))
return errors.New(err.Error() + errQueries.Join("\n").String())
} else {
return nil
}
}