蔵書インデクシングプロジェクト活動進捗(4)

というわけで，とりあえず，このスキーマに作業テーブルから移行することにします。新しいスキーマでは，著者（author）テーブルに ID をつけているので，同姓同名でも内部的に区別できるんですけど，AWS からのレスポンスや前回の作業テーブルでは区別がつきません。今のところ，同姓同名が混じっていた覚えはないので，全部同じ ID を振っちゃいます。間違えてたらあとで直す。

で，こゆのは，どどーんと一気に移行。

DROP TABLE IF EXISTS book_author;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS publisher;
DROP TABLE IF EXISTS binding;
DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS currency;
DROP TABLE IF EXISTS author;

CREATE TABLE publisher (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256) NOT NULL
);

CREATE TABLE binding (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);

CREATE TABLE role (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL
);

CREATE TABLE currency (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  currency_cd CHAR(3) NOT NULL
);

CREATE TABLE author (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);

CREATE TABLE book (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  isbn CHAR(10),
  ean CHAR(13),
  title VARCHAR(256) NOT NULL,
  pubdate DATE,
  price DECIMAL(10,2),
  pages INTEGER(6),
  publisher_id INTEGER(8),
  binding_id INTEGER(8),
  currency_id INTEGER(8)
);
ALTER TABLE book ADD FOREIGN KEY (publisher_id)
  REFERENCES publisher (id);
ALTER TABLE book ADD FOREIGN KEY (binding_id)
  REFERENCES binding (id);
ALTER TABLE book ADD FOREIGN KEY (currency_id)
  REFERENCES currency (id);

CREATE TABLE book_author (
  id INTEGER(8) PRIMARY KEY AUTO_INCREMENT,
  book_id INTEGER(8) NOT NULL,
  author_id INTEGER(8) NOT NULL,
  role_id INTEGER(8)
);
ALTER TABLE book_author ADD FOREIGN KEY (book_id)
  REFERENCES book (id);
ALTER TABLE book_author ADD FOREIGN KEY (author_id)
  REFERENCES author (id);
ALTER TABLE book_author ADD FOREIGN KEY (role_id)
  REFERENCES role (id);

INSERT INTO binding (
    name
  )
  SELECT
    binding
  FROM
    tmp_book
  GROUP BY
    binding
  ;

INSERT INTO role (
    name
  )
  SELECT
    role
  FROM
    tmp_author
  GROUP BY
    role
  ;

INSERT INTO currency (
    currency_cd
  )
  SELECT
    currency_cd
  FROM
    tmp_book
  GROUP BY
    currency_cd
  ;

INSERT INTO publisher (
    name
  )
  SELECT
    publisher
  FROM
    tmp_book
  GROUP BY
    publisher
  ;

INSERT INTO author (
    name
  )
  SELECT
    name
  FROM
    tmp_author
  GROUP BY
    name
  ;

INSERT INTO book (
    isbn,
    ean,
    title,
    pubdate,
    price,
    pages,
    publisher_id,
    binding_id,
    currency_id
  )
  SELECT
    bk.isbn,
    bk.ean,
    bk.title,
    STR_TO_DATE(bk.pubdate, '%Y-%m-%d'),
    IF(bk.price = '', NULL, bk.price),
    IF(bk.pages = '', NULL, bk.pages),
    fr.pid,
    fr.bid,
    fr.cid
  FROM
    tmp_book AS bk,
    (
      SELECT
        tb.id AS tid,
        pb.id AS pid,
        bd.id AS bid,
        cr.id AS cid
      FROM
        tmp_book AS tb,
        publisher AS pb,
        binding AS bd,
        currency AS cr
      WHERE 1 = 1
        AND tb.publisher = pb.name
        AND tb.binding = bd.name
        AND tb.currency_cd = cr.currency_cd
    ) fr
  WHERE 1 = 1
    AND bk.id = tid
  ;

INSERT INTO book_author (
    book_id,
    author_id,
    role_id
  )
  SELECT
    ibk.book_id,
    iat.author_id,
    iat.role_id
  FROM
    (
      SELECT
        tb.isbn AS isbn,
        bk.id AS book_id
      FROM
        tmp_book AS tb,
        book AS bk
      WHERE 1 = 1
        AND tb.isbn = bk.isbn
    ) ibk,
    (
      SELECT
        ta.isbn AS isbn,
        at.id AS author_id,
        rl.id AS role_id
      FROM
        tmp_author AS ta,
        author AS at,
        role AS rl
      WHERE 1 = 1
        AND ta.name = at.name
        AND ta.role = rl.name
    ) iat
  WHERE 1 = 1
    AND ibk.isbn = iat.isbn
  ;

一応，件数だけからして，それらしくデータが入ったみたいです。結果を見たいんですけど，残念なことに，データベースを UTF-8 で作っているので，今使ってるシェルでは文字化けして見ることができません。これってどっかで直せるんだっけ？このためだけに，サードパーティ製ツールを使うのもシャクなんで，あまり使いたくありません。つことは，データがちゃんと入ってるかは，アプリケーションを仮組みしないとダメなのかな……。

今回作ったテーブルは，あくまでも書籍のマスタなので，これを利用するためには，書棚（shelf）テーブルのような，アクションを含むテーブルが欲しいところです（購入日や閲覧条件を記録する）。ともあれ，これで書籍マスタを管理する機能は作れるか……画面の設計でもしようと思います。