Ⅳ. データベースの保守②

　前章ではLibreOffice Baseを利用して、データベースのデータ閲覧や修正などを表形式で簡単に行えるようになりました。しかし、テーブルの追加や構成フィールドの修正などを細かく行うためには、phpMyAdminというPHPで実装されたMySQLの管理ツールがとても便利です。データベースのバックアップや復元なども簡単にできます。ここではphpMyAdminを導入して基本的な操作を試してみましょう。目的に応じてLibreOffice Baseと使い分けてください。

１．phpMyAdminの導入

　phpMyAdminはMySQLサーバーをWebブラウザで管理できるデータベース接続ツールです。SQL文を記述することなく様々な操作が行え、必要に応じてSQL文をキー入力して実行させることもできます。

①phpMyAdminをインストールする

　PoderosaからRaspberry Piサーバーにログインしてインストールを指示します。
sudo apt-get update
sudo apt-get install phpmyadmin
　インストールが始まると次々と入力や確認を求めてきます。
　・webサーバーの選定
　　　　apache2を選びます。
　・新バージョンのインストール（関連ファイルの新バージョンがある場合のみ）
　　　　既定の［現在インストールされているローカルバージョンを保持］を選びます。
　・dbconfig-commonの設定方法
　　　　既定の［はい］を選びます。
　・データベース管理権限を持つユーザーのパスワード
　　　　MySQLのパスワードと同じが良いのではと思います。
　・phpMyAdmin用のMySQLアプリケーションパスワード
　　　　上と同じもので良いのではと思います。
　　さらにパスワードの確認入力を求められたら同じ内容を入力してください。

②phpMyAdminの起動とログイン

　Webブラウザを起動して「http://{Paspberry Piのアドレス}/phpmyadmin/」を入力します。ここでは「http://192.168.0.22/phpmyadmin/」と入力しています。
　次のようなphpMyAdminのログイン画面が表示されます。
　「ユーザー名」にはrootを入力し、「パスワード」には先に指定したものを入力します。
　［実行］ボタンをクリックしてログインに成功すると次の画面が表示されます。

２．phpMyAdminでデータベースを構築する

①データベースの作成

　第Ⅱ章で構築したworld_heritageと同じものをphpMyAdminで作成してみましょう。データベース名はworld_heritage2にします。
　［データベース］タブを開いて、「データベース名」にworld_heritage2、「照合順序」はutf8_general_ciを選択して［作成］ボタンをクリックしてください。
　左画面のデータベース一覧にworld_heritage2が追加表示されます。

②テーブルと構成フィールドの定義

右画面には「このデータベースにはテーブルがありません。」と表示されています。さっそくheritage_tableを定義してみましょう。「名前」にheritage_table、カラム数に構成フィールドの数4を入力して［実行］をクリックします。
　フィールド定義の項目がずらりと並んだものが、カラム数で指定したとおり4行現れました。画面を横にスライドするとさらにたくさんの設定項目があります。多くは既定値を適用すればよいので、ここではまず「名前」「データ型」を指定します。データ型が非数値の場合は「長さ」を指定してください。また、NULL値の入力を許容する（無入力を認める）項目には、右端に見えている「NULL」のチェックボックスをチェックしておきます。
　すべての設定が終わったら［保存する］ボタンをクリックしてください。
　以下、同じように他の3つのテーブルも定義します。

③プライマリーキーの設定

　すべてのテーブル定義が完了したら、画面左のデータベース名world_heritage2をクリックします。［構成］タブが開いてテーブル一覧が表示されます。「操作」列の［構造］をクリックしてフィールド一覧を表示させてプライマリーキーを設定します。まず、一番上のconfig_tableの［構造］をクリックしてみましょう。
　config_tableのフィールド一覧が表示されました。RegisterNoとConfigNoをペアにしたものをプライマリーキーにしたいので、両フィールド名の左端チェックボックスをチェックします。続いて「チェックしたものを：」の右の［主］をクリックします。これで主キー、つまりプライマリーキーの設定を指示したことになります。
　設定し終わると前の画面に戻りますが、プライマリーキーが設定されたフィールド名には下線が描かれています。
　同様にして他のテーブルにもプライマリーキーを設定してください。

④データのインポート処理

　第Ⅱ章で使用したCSVデータをインポートしてみましょう。第Ⅱ章では、CSVファイルをRaspberry Pi側のtmpディレクトリーに複写しましたが、ここではWindows PC側のオリジナルのファイルを使用することになります。
　まず［インポート］タブを開いて［ファイルを選択］をクリックします。
　CSVファイルを選択して［開く］をクリックします。
　「フォーマット」が［CSV］に切り替わっているのを確認して［実行］をクリックします。
　インポートされた行数と内容が一覧表示されます。
　残る3つのデータも同様にしてインポートしてください。これでデータベースの構築は完了です。


３．phpMyAdminを使ってみる

　phpMyAdminでどんなことができるかを、出来上がったデータベースworld_heritage2で試してみましょう。データベースの保守はほとんど何でもできてしまうのですが、ここでは基本的で重要な事柄に絞ることにします。

①テーブルデータの保守

　左画面のデータベースツリーを展開してテーブル名をクリックすることで、いつでもテーブルの内容を表示・確認することができます。heritage_tableをクリックして表示される画面で、データ一覧の4行目のHeritageNameをダブルクリックすると入力ができるようになります（"白神山地"の部分）。この状態で内容を修正してカーソルを移動させると、修正結果がただちにデータベースに反映されます。
　1行の項目数が多い場合は、修正したい行の左にある［編集］をクリックします。するとその行の内容が縦方向に表示されて、必要な修正を行った後に［実行］ボタンをクリックすることで修正結果を反映させることができます。下図は5行目の［編集］をクリックしたときの状態です。

②テーブル構造の保守

　例えば、heritage_tableにフィールドを追加したい場合を考えてみましょう。左画面のテーブル名の左にある構造編集アイコン（赤丸の部分）をクリックして、フィールド構成を表示させます。Kindフィールドの次に、メモ用のフィールドとして、200文字の可変長フィールドMemoを追加してみることにします。
　［実行］ボタンのすぐ上で、フィールド（カラム）の追加を指示することができます。追加するカラム数"1"はそのままにして、右端のリストボックス「指定カラムの後に」を開いて［Kind］を選択します。
　フィールド定義画面に切り替わるので、「名前」「データ型」「長さ」を指定し、Memoは入力を省略する場合もあるので「NULL」をチェックします。
　［保存する］ボタンをクリックすると、「テーブル heritage_tableは正常に変更されました」のメッセージ画面に変わります。
　メッセージの下には、一連の指示の結果として実行されたSQL文が表示されています。
ALTER TABLE `heritage_table` ADD `Memo` VARCHAR(200) NULL AFTER `Kind`;
　こんなに簡単なSQL文を実行させるだけでテーブル構造を変更できるのですが、phpMyAdminでは若干手間はかかるけれど、SQL文を使わなくても視覚的かつ対話的にできるのが特徴です。もう一度heritage_tableの構造編集画面に切り替えると、予定の位置にMemoフィールドが追加されているのを確認できます。
　元に戻すために、Memoフィールドの「操作欄」の［削除］をクリックします。確認画面に［OK］と応えると削除されます。


４．phpMyAdminによるデータベースのバックアップ

　phpMyAdminを利用すると、データベースのバックアップや復元をとても簡単に行うことができます。新たに別のデータベースを作成して、そこにバックアップデータを復元することでデータベースを移行することもできます。ここでは先に作成したデータベースworld_heritage2をバックアップした後に、新たにworld_heritage3というデータベースを作成します。そしてworld_heritage3にバックアップデータを復元して、同じ内容のデータベースを構築してみましょう。

①データベースのバックアップ

　［エクスポート］タブを開き、「エクスポート方法」で［詳細 - 可能なオプションをすべて表示］をチェックします。
　「データベース」欄からバックアップするデータベース（ここではworld_heritage2）を選択します。他にも多くの条件を設定できますが、既定条件のままで［実行］ボタンをクリックします。
　バックアップファイルはダウンロードの形態で作成されます。ダウンロードフォルダーに、デフォールトでは「MySQLのサーバ名.sql」のファイル名で書き込まれます。ローカルホストで動かしていれば「localhost.sql」になります。必要に応じてファイル名を変更して管理してください。

②データベースの復元

　［データベース］タブを開いて、先ほどと同じようにしてworld_heritage3を作成します。
　［インポート］タブを開いて［ファイルを選択］をクリックして、先ほどのバックアップファイルを選択します。
　「フォーマット」が［SQL］になっていることを確認して［実行］ボタンをクリックします。数秒で復元が完了し、world_heritage3に構成テーブルが表示されます。それぞれのテーブルの内容が正しく復元されているのを確認してください。


〔参考〕

　バックアップファイルは次のようなSQL文で出力されています。これを見ると、シンプルで確実な方法に感心してしまいます。
-- phpMyAdmin SQL Dump -- version 4.2.12deb2+deb8u1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: 2016 年 ? 月 ? 日 ??:?? <=== ハンドで修正しています！ -- サーバのバージョン： 5.5.44-0+deb8u1 -- PHP Version: 5.6.20-0+deb8u1 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `world_heritage2` -- -- -------------------------------------------------------- -- -- テーブルの構造 `config_table` -- CREATE TABLE IF NOT EXISTS `config_table` ( `RegisterNo` int(11) NOT NULL, `ConfigNo` int(11) NOT NULL, `PrefCode` int(11) NOT NULL, `Configure` varchar(100) DEFAULT NULL, `Note` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- テーブルのデータのダンプ `config_table` -- INSERT INTO `config_table` (`RegisterNo`, `ConfigNo`, `PrefCode`, `Configure`, `Note`) VALUES (1, 1, 29, '', ''), (2, 1, 28, '', ''), (3, 1, 46, '', ''), (4, 1, 2, '', ''), (4, 2, 5, '', ''), (5, 1, 26, '賀茂別雷神社（かもわけいかづちじんじゃ）', '通称は上賀茂神社（かみがもじんじゃ）'), (5, 2, 26, '賀茂御祖神社（かもみおやじんじゃ）', '通称は下鴨神社（しもがもじんじゃ）'), (5, 3, 26, '教王護国寺（きょうおうごこくじ）', '東寺（とうじ）とも呼ばれる'), (5, 4, 26, '清水寺（きよみずでら）', ''), ： ： 以下のテーブル構造とデータダンプは省略 ： ： -- -- Indexes for dumped tables -- -- -- Indexes for table `config_table` -- ALTER TABLE `config_table` ADD PRIMARY KEY (`RegisterNo`,`ConfigNo`); -- -- Indexes for table `heritage_table` -- ALTER TABLE `heritage_table` ADD PRIMARY KEY (`RegisterNo`); -- -- Indexes for table `kind_table` -- ALTER TABLE `kind_table` ADD PRIMARY KEY (`Kind`), ADD UNIQUE KEY `Kind` (`Kind`); -- -- Indexes for table `pref_table` -- ALTER TABLE `pref_table` ADD PRIMARY KEY (`PrefCode`), ADD UNIQUE KEY `PrefCode` (`PrefCode`); -- -- ダンプしたテーブルの制約 -- -- -- テーブルの制約 `config_table` -- ALTER TABLE `config_table` ADD CONSTRAINT `config_table_ibfk_1` FOREIGN KEY (`RegisterNo`) REFERENCES `heritage_table` (`RegisterNo`) ON DELETE NO ACTION ON UPDATE CASCADE; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;