func (ls *Languages) Delete(optsx ...go2sql.DeleteOption) (err error) {
opts := go2sql.DeleteOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
var ids []interface{}
var idsCons []string
for _, l := range *ls {
ids = append(ids, l.ID)
idsCons = append(idsCons, "id = ?")
}
_, err = db.Exec(`DELETE FROM languages WHERE `+strings.Join(idsCons, " or "), ids...)
if err != nil {
return
}
tables, _ := opts.GetTables()
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
var people People
for _, l := range *ls {
people = append(people, l.Author)
}
err = people.Delete(go2sql.DB(db))
case LanguageColumnKeywords:
var keywords Keywords
for _, l := range *ls {
keywords = append(keywords, l.Keywords...)
}
err = keywords.Delete(go2sql.DB(db))
case LanguageColumnTeachers:
var teachers Teachers
for _, l := range *ls {
teachers = append(teachers, l.Teachers...)
}
err = teachers.Delete(go2sql.DB(db))
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
}
if err != nil {
return
}
}
return
}
func TestFindLanguages(t *testing.T) {
resetDB()
populateDB()
var ls Languages
var err error
// normal
ls, err = FindLanguages()
if err != nil {
t.Fatal(err)
}
if got, want := len(ls), 99; got != want {
t.Error("len(ls) = %d; want %d", got, want)
}
if got, want := ls[0].Name, "Mr. Tester"; got != want {
t.Errorf("ls[0].Name = %s; want %s", got, want)
}
// sql patial
ls, err = FindLanguages(go2sql.NewSQL("order by id desc limit 10"))
if err != nil {
t.Fatal(err)
}
if got, want := len(ls), 10; got != want {
t.Error("len(ls) = %d; want %d", got, want)
}
if got, want := ls[0].ID, uint(99); got != want {
t.Errorf("ls[0].Name = %d; want %d", got, want)
}
// db specification
go2sql.SetDefaultDB(nil)
_, err = FindLanguages()
if err == nil {
t.Error("expect error for nil db")
}
ls, err = FindLanguages(go2sql.DB(db))
if err != nil {
t.Fatal(err)
}
if got, want := ls[0].Name, "Mr. Tester"; got != want {
t.Errorf("ls[0].Name = %s; want %s", got, want)
}
go2sql.SetDefaultDB(db)
// selects
ls, err = FindLanguages(go2sql.Selects{"id", "words_stat"})
if err != nil {
t.Fatal(err)
}
if got, want := ls[0].Name, ""; got != want {
t.Errorf("ls[0].Name = %s; want %s", got, want)
}
if got, want := ls[0].WordsCount, uint(1); got != want {
t.Errorf("ls[0].Name = %d; want %d", got, want)
}
}
func (ls *Languages) FetchAuthor(optsx ...go2sql.QueryOption) (err error) {
if len(*ls) == 0 {
return
}
opts := go2sql.QueryOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
var ids []string
for _, l := range *ls {
ids = append(ids, strconv.Itoa(int(l.AuthorID)))
}
// rows, err := db.Query(`SELECT id, name, email FROM people WHERE id IN (?)`, strings.Join(ids, ","))
// if err != nil {
// return
// }
// defer func() {
// if er := rows.Close(); er != nil {
// if err != nil {
// log.Println(er)
// } else {
// err = er
// }
// }
// }()
// var people []*Person
// for rows.Next() {
// var person Person
// if err = rows.Scan(&person.ID, &person.Name, &person.Email); err != nil {
// return
// }
// people = append(people, &person)
// }
people, err := FindPeople(go2sql.DB(db), go2sql.NewSQL("WHERE id IN (?)", strings.Join(ids, ", ")))
if err != nil {
return
}
for index, l := range *ls {
for _, person := range people {
if person.ID == l.AuthorID {
(*ls)[index].Author = person
}
}
}
return
}
func (ls *Languages) FetchKeywords(optsx ...go2sql.QueryOption) (err error) {
opts := go2sql.QueryOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
var ids []string
for _, l := range *ls {
ids = append(ids, strconv.Itoa(int(l.ID)))
}
// rows, err := db.Query(`SELECT id, name, type, language_id FROM keywords WHERE language_id IN (?)`, strings.Join(ids, ","))
// if err != nil {
// return
// }
// defer func() {
// if er := rows.Close(); er != nil {
// if err != nil {
// log.Println(er)
// } else {
// err = er
// }
// }
// }()
// var keywords []Keyword
// for rows.Next() {
// var k Keyword
// if err = rows.Scan(&k.ID, &k.Name, &k.Type, &k.LanguageID); err != nil {
// return
// }
// keywords = append(keywords, k)
// }
keywords, err := FindKeywords(go2sql.DB(db), go2sql.NewSQL("WHERE language_id IN (?)", strings.Join(ids, ",")))
if err != nil {
return
}
for i, l := range *ls {
for _, keyword := range keywords {
if keyword.LanguageID == l.ID {
(*ls)[i].Keywords = append((*ls)[i].Keywords, keyword)
}
}
}
return
}
func (l *Language) Delete(optsx ...go2sql.DeleteOption) (err error) {
if l == nil || l.IsEmptyRow() {
return
}
opts := go2sql.DeleteOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
_, err = db.Exec(`DELETE FROM languages WHERE id = ?`, l.ID)
if err != nil {
return
}
tables, _ := opts.GetTables()
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
err = l.Author.Delete(go2sql.DB(db), table.Tables)
case LanguageColumnKeywords:
keywords := Keywords(l.Keywords)
err = keywords.Delete(go2sql.DB(db), table.Tables)
case LanguageColumnTeachers:
teachers := Teachers(l.Teachers)
err = teachers.Delete(go2sql.DB(db), table.Tables)
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
}
if err != nil {
return
}
}
return
}
func (ls *Languages) Update(optsx ...go2sql.UpdateOption) (err error) {
if l == nil {
return
}
opts := go2sql.UpdateOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
tables, _ := opts.GetTables()
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
if l.Author.IsNewRow() {
continue
}
if err = l.Author.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
l.AuthorID = l.Author.ID
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
return
}
}
if l.IsNewRow() {
err = l.Insert(go2sql.DB(db))
} else {
_, err = db.Exec(`UPDATE languages SET name = ?, words_stat = ? WHERE id = ?`, l.Name, l.WordsCount, l.ID)
}
if err != nil {
return
}
for _, table := range tables {
switch table.Name {
case LanguageColumnKeywords:
for index := range l.Keywords {
l.Keywords[index].LanguageID = l.ID
}
keywords := Keywords(l.Keywords)
if err = keywords.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
case LanguageColumnTeachers:
teachers := Teachers(l.Teachers)
if err = teachers.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
return
}
}
return
}
func FindLanguage(optsx ...go2sql.QueryOption) (l *Language, err error) {
opts := go2sql.QueryOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
l = &Language{}
var fields []interface{}
var columns []string
if sel, ok := opts.GetSelect(); ok {
columns = []string(sel)
for _, c := range columns {
switch c {
case LanguageColumnID:
fields = append(fields, &l.ID)
case LanguageColumnName:
fields = append(fields, &l.Name)
case LanguageColumnWordsCount:
fields = append(fields, &l.WordsCount)
default:
err = fmt.Errorf("go2sql: unknown column %s", c)
return
}
}
} else {
columns = []string{"id", "name", "words_stat", "field1", "field2", "field3", "field4", "field5", "field6", "field7"}
fields = []interface{}{&l.ID, &l.Name, &l.WordsCount, &l.Field1, &l.Field2, &l.Field3, &l.Field4, &l.Field5, &l.Field6, &l.Field7}
}
var sql go2sql.SQL
if opt, ok := opts.GetSQL(); ok && opt.Full {
sql = opt
} else {
sql.SQL = fmt.Sprintf("select %s from languages %s", strings.Join(columns, ","), opt.SQL)
sql.Args = opt.Args
}
err = db.QueryRow(sql.SQL, sql.Args...).Scan(fields...)
if err != nil {
return
}
if tables, ok := opts.GetTables(); ok {
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
err = l.FetchAuthor(go2sql.DB(db), table.Tables)
case LanguageColumnKeywords:
err = l.FetchKeywords(go2sql.DB(db), table.Tables)
case LanguageColumnTeachers:
err = l.FetchTeachers(go2sql.DB(db), table.Tables)
}
if err != nil {
return
}
}
}
return
}
func (l *Language) Insert(optsx ...go2sql.InsertOption) (err error) {
if !l.IsNewRow() {
return
}
opts := go2sql.InsertOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
tables, _ := opts.GetTables()
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
if l.Author.IsEmptyRow() {
continue
}
if err = l.Author.Insert(go2sql.DB(db), table.Tables); err != nil {
return
}
l.AuthorID = l.Author.ID
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
return
}
}
// TODO: support selects
r, err := db.Exec(`INSERT INTO languages (name, words_stat) VALUES(?, ?)`, l.Name, l.WordsCount)
if err != nil {
return
}
id, err := r.LastInsertId()
if err != nil {
return
}
l.ID = uint(id)
for _, table := range tables {
switch table.Name {
case LanguageColumnKeywords:
for index := range l.Keywords {
l.Keywords[index].LanguageID = l.ID
// if err := l.Keywords[index].Update(db); err != nil {
// return
// }
}
keywords := Keywords(l.Keywords)
if err = keywords.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
case LanguageColumnTeachers:
teachers := Teachers(l.Teachers)
if err = teachers.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
for index := range l.Teachers {
// if l.Teachers[index].ID <= 0 {
// if _, err = l.Teachers[index].Insert(db); err != nil {
// return
// }
// }
if _, err = db.Exec("INSERT INTO languages_teachers_xref (language_id, teacher_id) VALUES (?, ?)", l.ID, l.Teachers[index].ID); err != nil {
return
}
}
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
return
}
}
return
}
func (ls *Languages) Insert(optsx ...go2sql.InsertOption) (err error) {
if len(*ls) == 0 {
return
}
opts := go2sql.InsertOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
tables, _ := opts.GetTables()
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
var authors People
for _, l := range *ls {
if l.Author != nil {
authors = append(authors, l.Author)
}
}
if err = authors.Insert(); err != nil {
return
}
default:
err = fmt.Errorf("go2sql: unknown table %s", table)
return
}
}
// TODO: support selects
stmt, err := db.Prepare(`INSERT INTO languages (name, words_stat) VALUES(?, ?)`)
if err != nil {
return
}
defer func() {
er := stmt.Close()
if err == nil {
err = er
} else {
log.Println(er)
}
}()
for _, l := range *ls {
var r sql.Result
r, err = stmt.Exec(l.Name, l.WordsCount)
if err != nil {
return
}
var id int64
id, err = r.LastInsertId()
if err != nil {
return
}
l.ID = uint(id)
}
for _, table := range tables {
switch table.Name {
case LanguageColumnKeywords:
var keywords Keywords
for _, l := range *ls {
for _, keyword := range l.Keywords {
keyword.LanguageID = l.ID
keywords = append(keywords, keyword)
}
}
if err = keywords.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
case LanguageColumnTeachers:
var teachers Teachers
for _, l := range *ls {
for _, teacher := range l.Teachers {
teacher.LanguageID = l.ID
teachers = append(teachers, teacher)
}
}
if err = teachers.Update(go2sql.DB(db), table.Tables); err != nil {
return
}
for _, teacher := range teachers {
if _, err = db.Exec("INSERT INTO languages_teachers_xref (language_id, teacher_id) VALUES (?, ?)", teacher.LanguageID, teacher.ID); err != nil {
return
}
}
// teachers := Teachers(l.Teachers)
// if err = teachers.Update(go2sql.DB(db), table.Tables); err != nil {
// return
// }
// for index := range l.Teachers {
// if _, err = db.Exec("INSERT INTO languages_teachers_xref (language_id, teacher_id) VALUES (?, ?)", l.ID, l.Teachers[index].ID); err != nil {
// return
// }
// }
default:
err = fmt.Errorf("go2sql: unknown column %s", table)
return
}
}
return
}
func FindLanguages(optsx ...go2sql.QueryOption) (ls Languages, err error) {
opts := go2sql.QueryOptions(optsx)
db, ok := opts.GetDB()
if !ok {
db = go2sql.DefaultDB.DB
}
if db == nil {
err = errors.New("should specify *sql.DB by go2sql.DB or init go2sql.DefaultDB")
return
}
var columns []string
if sel, ok := opts.GetSelect(); ok {
columns = []string(sel)
} else {
columns = []string{"id", "name", "words_stat", "field1", "field2", "field3", "field4", "field5", "field6", "field7"}
}
var sql go2sql.SQL
if opt, ok := opts.GetSQL(); ok && opt.Full {
sql = opt
} else {
sql.SQL = fmt.Sprintf("select %s from languages %s", strings.Join(columns, ","), opt.SQL)
sql.Args = opt.Args
}
rows, err := db.Query(sql.SQL, sql.Args...)
if err != nil {
return
}
defer func() {
if er := rows.Close(); er != nil {
if err != nil {
log.Println(er)
} else {
err = er
}
}
}()
for rows.Next() {
var l Language
var fields []interface{}
for _, c := range columns {
switch c {
case LanguageColumnID:
fields = append(fields, &l.ID)
case LanguageColumnName:
fields = append(fields, &l.Name)
case LanguageColumnWordsCount:
fields = append(fields, &l.WordsCount)
case "field1":
fields = append(fields, &l.Field1)
case "field2":
fields = append(fields, &l.Field2)
case "field3":
fields = append(fields, &l.Field3)
case "field4":
fields = append(fields, &l.Field4)
case "field5":
fields = append(fields, &l.Field5)
case "field6":
fields = append(fields, &l.Field6)
case "field7":
fields = append(fields, &l.Field7)
default:
err = fmt.Errorf("go2sql: unknown column %s", c)
return
}
}
err = rows.Scan(fields...)
if err != nil {
return
}
ls = append(ls, &l)
}
if tables, ok := opts.GetTables(); ok {
for _, table := range tables {
switch table.Name {
case LanguageColumnAuthor:
err = ls.FetchAuthor(go2sql.DB(db), table.Tables)
case LanguageColumnKeywords:
err = ls.FetchKeywords(go2sql.DB(db), table.Tables)
case LanguageColumnTeachers:
err = ls.FetchTeachers(go2sql.DB(db), table.Tables)
}
if err != nil {
return
}
}
}
return
}