SQLite, Golang и соединительная таблица

Question

Я хотел бы создать небольшую базу данных книг, используя Go и sqlite. Я взял из этого совета примеры внешнего ключа SQLite и немного переработал его.

package main                                                                                                                                                

import (
    "database/sql"
...
    _ "github.com/mattn/go-sqlite3"
)

...
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), 
        uthorid 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
    );
`

Итак, я хотел бы сохранить список уникальных авторов и поддерживать связь "многие ко многим" с таблицами книг (одна книга может иметь более одного автора, а автор может написать более одной книги).

Затем я просто добавляю книги в цикле, получаю LastIndexID и помещаю его в таблицу соединений (код сокращен для иллюстрации, b - структура книги):

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()

Здесь возникает проблема - a_id увеличивается, если я добавляю более одной книги одного автора, но таблица соединений содержит более старое значение. Например:

Books:                       
id | Title                   
---|--------------------
1  | Atlas Shrugged pt.1 
2  | Atlas Shrugged pt.2
3  | Atlas Shrugged pt.3

Authors:
id | Fname | Mname | Lname 
---|-------|-------|------
702| Ayn   |       | Rand

Junction table:
BookId | AuthorID
-------|---------
 1     |  700
 2     |  701
 3     |  702

What I want - Junction table:
BookId | AuthorID
-------|---------
 1     |  702
 2     |  702
 3     |  702

Как я могу исправить это так, что правильный AuthorId будет отражен в таблице? Я не хочу использовать GORM или любой из инструментов ORM и пытаться решить его с помощью чистого (ну, более или менее) SQL.

Одно из решений, которые я вижу сейчас, я могу сначала выбрать SELECT, затем INSERT, если ничего не найдено, и затем SELECT еще раз, однако я не уверен, насколько идиоматична эта идея. Обратите внимание, что у меня есть значительное количество записей для добавления.

Answer 1

В качестве быстрого взлома я сделал выбор после добавления автора:

err = db.QueryRow("select id from authors where fname = ? and mname = ? and lname = ?", a.Fname, a.Mname, a.Lname).Scan(&a_id)
if err != nil {
    log.Fatal(err)
}
res, err = db.Exec("Insert into booksauthors(bookid, authorid) values(?, ?)", b_id, a_id)
if err != nil {
    enter code herelog.Fatal(err)
}

Но я все еще ищу лучший ответ...

Answer 2

Мой совет будет SELECT вы список авторов и INSERT те, которые отсутствуют. Решение наивно, но работает и просто. Я не знаком с реляционными функциями SQLite, но их использование не всегда является самым простым способом работы с внешними ключами….

Теперь, если у вас есть большой объем данных для обработки, выполните ту же операцию, но начните с объединения авторов книг, чтобы вы могли иметь только один SELECT затем один INSERT сделать.