Entry

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

2008年07月28日

ということで,どんどん進む蔵書インデクシングプロジェクト。前回までの話はこちら。

前回は XSLT を使って AWS から目録データをもらってきて,作業テーブルに入れたのでした。今回はこれを(割と)ちゃんとしたスキーマに収め直すことにします。前回詰めたスキーマというのはこちら。頑張って描きました。

作業テーブルの図

で,今回それっぽく作ったのは,こちらのスキーマです。これも頑張って描いた(ちょっと正確じゃないので,実際に作ったテーブルは後掲 SQL を参照をば)。

今回作ったテーブルの図

PK の他に名前しかないテーブルもあるんですけど,これは後ほど拡張する予定があるのと,画面のことも考えてコマコマと分けました。一応,第3正規形になってるんじゃないかな。

画面のことってのは,例えば,製本(binding)テーブルには,「文庫本」とか「単行本」とかが入るんですけど,こゆのは,プルダウンメニューとかを作る時の具になるんですね。そゆときに,書籍(book)テーブルと一緒にしてると,プルダウンを作るたびにテーブルを GROUP BY しなくちゃいけません。画面のパーツを作るのに,いちいち GROUP BY するのは,パフォーマンス的に(というか常識的に)よろしくありません。

この構成だと,製本テーブルを SELECT するだけで,お手軽にプルダウンのモジュールを作ることができます。一方,通貨(currency)テーブルなんかは,書籍テーブルと一緒にしておいても,大して問題ないんですけど,この際だから分けました。

というわけで,とりあえず,このスキーマに作業テーブルから移行することにします。新しいスキーマでは,著者(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)テーブルのような,アクションを含むテーブルが欲しいところです(購入日や閲覧条件を記録する)。ともあれ,これで書籍マスタを管理する機能は作れるか……画面の設計でもしようと思います。

Trackback
Trackback URL:
Ads
About
Search This Site
Ads
Categories
Recent Entries
Log Archive
Syndicate This Site
Info.
クリエイティブ・コモンズ・ライセンス
Movable Type 3.36
Valid XHTML 1.1!
Valid CSS!
ブログタイムズ

© 2003-2012 AIAN