I would like to create a small database of books using Go and sqlite. I took the main from this advise SQLite foreign key examples and redeveloped it a bit.
package main
import (
"database/sql"
...
_ "http://ift.tt/1lwG0Ak"
)
...
db, err := sql.Open("sqlite3", "./foo.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
sqlStmt := `
create table books (id integer primary key autoincrement, title text);
create table booksauthors ( bookid integer references books(id), authorid integer references authors(id) ON DELETE CASCADE ON UPDATE CASCADE);
create table authors (id integer primary key autoincrement,
fname text, mname text,lname text, unique (fname, mname, lname) on conflict ignore);
`
So, I'd like to keep list of unique authors and maintain many-to-many connection with books tables (one book may have more then one author, and author might write more then one book).
Then I simply add books in cycle, get LastIndexID and put it to junction table (code is reduces for illustration, b is book struct):
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
res, err := db.Exec("Insert into books(title) values(?)", b.Title)
if err != nil {
log.Fatal(err)
}
b_id, _ := res.LastInsertId()
for _, a := range b.Authors {
res, err = db.Exec("Insert into authors(fname, mname, lname) values(?, ?, ?)", a.Fname, a.Mname, a.Lname)
if err != nil {
log.Fatal(err)
}
a_id, _ := res.LastInsertId()
fmt.Println(a_id, b_id, a)
res, err = db.Exec("Insert into booksauthors(bookid, authorid) values(?, ?)", b_id, a_id)
if err != nil {
log.Fatal(err)
}
}
tx.Commit()
By here comes trouble - a_id is increased if I add more then one book of the same author, but junctions table contains older value of it. For example:
Books: Authors:
id | Title id | Fname | Mname | Lname
---|-------------------- ---|-------|-------|------
1 | Atlas Shrugged pt.1 702| Ayn | | Rand
2 | Atlas Shrugged pt.2
3 | Atlas Shrugged pt.3
Junction table:
BookId | AuthorID
-------|---------
1 | 700
2 | 701
3 | 702
How can I fix it so actual AuthorId would be reflected at the table? I don't want use GORM or any of ORM tools and trying to solve it using pure (well, more or less) SQL. One of the solutions I see now I can first SELECT, then INSERT is nothing is found and then SELECT once again, however I'm not sure how idiomatic this idea is. Please note that I have considerable numbers of records to add.
Aucun commentaire:
Enregistrer un commentaire