書店のアプリケーション例

Bookshop は、さまざまなカテゴリの本を購入したり、読んだ本を評価したりできる仮想オンライン書店アプリケーションです。

アプリケーション開発者ガイドをよりスムーズに読むために、Bookshop アプリケーションのテーブル構造とデータに基づいた SQL ステートメントの例を示します。このドキュメントでは、テーブル構造とデータをインポートする方法、およびテーブル構造の定義に焦点を当てます。

テーブル構造とデータをインポートする

Bookshop テーブルの構造とデータはTiUP経由またはTiDB Cloudのインポート機能経由いずれかでインポートできます。

TiDB Cloudの場合は、 方法 1: tiup demo経由スキップして Bookshop テーブル構造TiDB Cloudのインポート機能経由をインポートできます。

方法 1: tiup demo経由

TiDB クラスターがTiUPを使用してデプロイされている場合、または TiDBサーバーに接続できる場合は、次のコマンドを実行して、Bookshop アプリケーションのサンプル データをすばやく生成してインポートできます。

TiDB クラスターがTiUPを使用してデプロイされている場合、または TiDBサーバーに接続できる場合は、次のコマンドを実行して、Bookshop アプリケーションのサンプル データをすばやく生成してインポートできます。

tiup demo bookshop prepare

デフォルトでは、このコマンドにより、アプリケーションがアドレス127.0.0.1のポート4000に接続できるようになり、パスワードなしでrootユーザーとしてログインできるようになり、データベースbookshopにテーブル構造が作成されます。

接続情報を設定する

次の表に、接続パラメータを示します。環境に合わせてデフォルト設定を変更できます。

たとえば、 TiDB Cloud上のデータベースに接続する場合は、次のように接続情報を指定できます。

tiup demo bookshop prepare -U <username> -H <endpoint> -P 4000 -p <password>

データ量を設定する

次のパラメータを構成することで、各データベース テーブルで生成されるデータの量を指定できます。

たとえば、次のコマンドを実行すると、次のものが生成されます。

• --usersパラメータを介した 200,000 行のユーザー情報
• --booksパラメータによる 500,000 行の書籍情報
• --authorsパラメータによる 100,000 行の著者情報
• --ratingsパラメータによる 1,000,000 行の評価レコード
• --ordersパラメータによる 1,000,000 行の注文レコード

tiup demo bookshop prepare --users=200000 --books=500000 --authors=100000 --ratings=1000000 --orders=1000000 --drop-tables

--drop-tablesパラメータを使用して、元のテーブル構造を削除できます。パラメータの詳細については、 tiup demo bookshop --helpコマンドを実行してください。

方法 2: TiDB Cloudインポート経由

TiDB Cloudのクラスター詳細ページで、「インポート」領域の「データのインポート」をクリックして、 「データのインポート」ページに入ります。このページでは、次の手順を実行して、Bookshop サンプル データを AWS S3 からTiDB Cloudにインポートします。

1. [データ形式]で[SQL ファイル]を選択します。

2. 次のバケット URIとロール ARNを対応する入力ボックスにコピーします。

   バケット URI :

   s3://developer.pingcap.com/bookshop/
   

   役割 ARN :

   arn:aws:iam::494090988690:role/s3-tidb-cloud-developer-access
   

3. 「次へ」をクリックして「ファイルとフィルター」ステップに進み、インポートするファイルの情報を確認します。

4. もう一度「次へ」をクリックして「プレビュー」ステップに進み、インポートするデータのプレビューを確認します。

   この例では、次のデータが事前に生成されます。

   • 200,000 行のユーザー情報
   • 500,000 行の書籍情報
   • 100,000 行の著者情報
   • 1,000,000 行の評価レコード
   • 1,000,000 行の注文レコード

5. [インポートの開始]をクリックしてインポート プロセスを開始し、 TiDB Cloudがインポートを完了するまで待ちます。

データをTiDB Cloudにインポートまたは移行する方法の詳細については、 TiDB Cloud移行の概要を参照してください。

データのインポートステータスをビュー

インポートが完了したら、次の SQL ステートメントを実行して、各テーブルのデータ ボリューム情報を表示できます。

SELECT
    CONCAT(table_schema,'.',table_name) AS 'Table Name',
    table_rows AS 'Number of Rows',
    CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size',
    CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size',
    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS 'Total'
FROM
    information_schema.TABLES
WHERE table_schema LIKE 'bookshop';

結果は次のとおりです。

+-----------------------+----------------+-----------+------------+---------+
| Table Name            | Number of Rows | Data Size | Index Size | Total   |
+-----------------------+----------------+-----------+------------+---------+
| bookshop.orders       |        1000000 | 0.0373G   | 0.0075G    | 0.0447G |
| bookshop.book_authors |        1000000 | 0.0149G   | 0.0149G    | 0.0298G |
| bookshop.ratings      |        4000000 | 0.1192G   | 0.1192G    | 0.2384G |
| bookshop.authors      |         100000 | 0.0043G   | 0.0000G    | 0.0043G |
| bookshop.users        |         195348 | 0.0048G   | 0.0021G    | 0.0069G |
| bookshop.books        |        1000000 | 0.0546G   | 0.0000G    | 0.0546G |
+-----------------------+----------------+-----------+------------+---------+
6 rows in set (0.03 sec)

テーブルの説明

このセクションでは、Bookshop アプリケーションのデータベース テーブルについて詳しく説明します。

booksテーブル

このテーブルには書籍の基本情報が格納されます。

authorsテーブル

このテーブルには著者の基本情報が格納されます。

usersテーブル

このテーブルには、Bookshop ユーザーの情報が格納されます。

ratings表

このテーブルには、書籍に対するユーザー評価の記録が保存されます。

book_authorsテーブル

著者は複数の本を執筆する場合があり、また、1 つの本に複数の著者が関与する場合もあります。このテーブルには、書籍と著者間の対応関係が格納されます。

ordersテーブル

このテーブルにはユーザーの購入情報が格納されます。

データベース初期化スクリプトdbinit.sql

Bookshop アプリケーションでデータベース テーブル構造を手動で作成する場合は、次の SQL ステートメントを実行します。

CREATE DATABASE IF NOT EXISTS `bookshop`;

DROP TABLE IF EXISTS `bookshop`.`books`;
CREATE TABLE `bookshop`.`books` (
  `id` bigint(20) AUTO_RANDOM NOT NULL,
  `title` varchar(100) NOT NULL,
  `type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports') NOT NULL,
  `published_at` datetime NOT NULL,
  `stock` int(11) DEFAULT '0',
  `price` decimal(15,2) DEFAULT '0.0',
  PRIMARY KEY (`id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`authors`;
CREATE TABLE `bookshop`.`authors` (
  `id` bigint(20) AUTO_RANDOM NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `birth_year` smallint(6) DEFAULT NULL,
  `death_year` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`book_authors`;
CREATE TABLE `bookshop`.`book_authors` (
  `book_id` bigint(20) NOT NULL,
  `author_id` bigint(20) NOT NULL,
  PRIMARY KEY (`book_id`,`author_id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`ratings`;
CREATE TABLE `bookshop`.`ratings` (
  `book_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `score` tinyint NOT NULL,
  `rated_at` datetime NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (`book_id`,`user_id`) CLUSTERED,
  UNIQUE KEY `uniq_book_user_idx` (`book_id`,`user_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
ALTER TABLE `bookshop`.`ratings` SET TIFLASH REPLICA 1;

DROP TABLE IF EXISTS `bookshop`.`users`;
CREATE TABLE `bookshop`.`users` (
  `id` bigint AUTO_RANDOM NOT NULL,
  `balance` decimal(15,2) DEFAULT '0.0',
  `nickname` varchar(100) UNIQUE NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`orders`;
CREATE TABLE `bookshop`.`orders` (
  `id` bigint(20) AUTO_RANDOM NOT NULL,
  `book_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `quality` tinyint(4) NOT NULL,
  `ordered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) CLUSTERED,
  KEY `orders_book_id_idx` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin