FC2ブログ

スマフォのアプリを作りたい(30):番外編(MySQLからPostgreSQLへ)

   プログラミング [2020/08/01]
これまでの「スマフォのアプリを作りたい」シリーズの流れから少し脱線します。

ちょっと前に、このアプリのサーバ側DBをメンテナンスするための管理スクリプトを作成したのですが、DBはEclipseに含まれてたMySQLになってました。
わけあってPostgreSQLもサポートすることになりました。


◆Eclipse+PostgreSQL環境の構築
参考:https://www.dbonline.jp/postgresql/install/

<PostgreSQLのダウンロード>
上記のサイトからリンクされているhttps://www.postgresql.org/を表示
-上部の[ダウンロード→]ボタンをクリック
-対象OSのボタンをクリック
-説明文中の「インストーラをダウンロード」をクリック
-OSとバージョンの組み合わせで、「Windows x86-64」の最新(12.3)の[ダウンロード]ボタンをクリック

<PostgreSQLのインストール>
-ダウンロードしたexeを起動します
-いつもの、OS環境に変更を加えますか的なメッセージにOKします
-インストーラ(Setupダイアログ)の初期画面で[Next>]
-インストール先フォルダを指定して[Next>]
 ※さるの環境の場合、C:ドライブは空きがないので「D:\・・・\PostgreSQL\12」を指定
 以降、インストール先「D:\・・・\PostgreSQL」を「PostgreSQLフォルダ」と表記します。

-インストール・コンポーネント選択はデフォルトの全部のまま[Next>]
-データフォルダの指定はデフォルト(PostgreSQLフォルダ\data)のまま[Next>]
-スーパーユーザのパスワードを指定して[Next>]
-ポート番号はデフォルト(5432)のまま[Next>]
-ロケールは「Japanese, Japan」に変更して[Next]
-設定予定の内容がリストされるので、確認して[Next>]
-再度確認メッセージが表示されるので[Next]
 ※インストールが開始されます。結構な時間が掛かります。


参考:http://cmemo.net/articles/java/eclipse-%E3%81%A7-postgresql-%E3%81%AB%E6%8E%A5%E7%B6%9A%E3%81%99%E3%82%8B/

<ドライバのインストール>
-PostgreSQLのインストール完了画面の「Stack Builder・・・」をチェックして[Finish]
 ※あるいはインストール完了後、Windowsのスタートアップメニューから、[PostgreSQL 12]-[Application Stack Builder]を選択
-スタックビルダが起動します。
 「PostgreSQL・・・」を選択して[次へ]
-「カテゴリ」-「Database Drivers」-「pgJDBC・・・」をチェックして[次へ]
-ダウンロード先フォルダを指定して[次へ]
 ※ダウンロード先には、PostgreSQLフォルダを指定した
-[次へ](インストール開始)
-表示された「Setup pgJDBC」ダイアログで[Next>]
-インストール先ディレクトリを指定して[Next>]
 ※インストール先には「(PostgreSQLフォルダ)\pgJDBC」を指定
-確認メッセージに対して[Next>]
-完了メッセージ画面で[Finish]
-スタックビルダのダイアログで[終了]

※ここからは、
・Eclipseが既にPC内にインストールされていること
・MySQL版のPHPプロジェクトが既に作成されていてベーススクリプトがあること
を前提にしています。

以降、Eclipse(Pleiades)のインストール先フォルダを「Eclipseフォルダ」と表記します。


<新規PHPプロジェクトの作成>
-Eclipseを起動し、ワークスペースはMySQL版のプロジェクトと同じにして続行
-Eclipseのメニューから[ファイル]-[新規]-[PHPプロジェクト]を選択
-ダイアログ上でプロジェクト名を入力して、その他はデフォルトのまま[完了]
-エクスプローラで、ベース(MySQL版)のスクリプトをプロジェクトフォルダにまるっとコピー
-Eclipseに戻って、「プロジェクト・エクスプローラ」タブ上の新規に作成したプロジェクトを選択し、[F5]キーを押す
※これで、新しいプロジェクトの作成は完了。


<Eclipseに対するPostgreSQLドライバの設定>
※この手順はJava用なので不要。かつ動作確認をしていないので無保証です。
-「(PostgreSQLフォルダ)\pgJDBC\」にある postgresql-42.2.12.jre7.jarをコピーして「(Eclipseフィルダ)\eclipce\jre\lib\」に貼り付けます


<php.iniの修正>
「(Eclipseフォルダ)\xampp\php\php.ihi」をエディタで開いて、以下2つの指定のコメントアウトを外します。
 extension=php_pdo_pgsql.dll
 extension=php_pgsql.dll
1番目の指定は、PDO経由でのアクセスを有効にするためのものだそうです。「PDO」って何?
→PHP Data Objectsの略。「通常、DBの種類毎に異なるI/Fを使用しなければならないが、PDOは共通のI/Fを提供する。」んだそうです。
ちなみにMySQL版のスクリプトで使っているmysqliは、PDOではない。つまり、現状ではPDOを使ってないのでコメントのままでもよいのだが。


<phpPgAdminのダウンロードとインストール>
phpPgAdminは、httpベースでPostgreSQLのデータベースの設定を行えるツールのようです。
MySQLのDB設定のときに使ったxamppに含まれていたphpMyAdminのようなものなのかな?

ともかくダウンロードしてインストールしてみます。
-以下のURLのページ上の
 http://phppgadmin.sourceforge.net/doku.php?id=download
「https://github.com/phppgadmin/phppgadmin/archive/REL_5-6-0.zip」をクリック
-ダウンロードした「phppgadmin-REL_5-6-0.zip」をダブルクリックして、解凍先に「(Eclipseフォルダ)\xampp\htdocs」を指定
-「(Eclipseフォルダ)\xampp\htdocs\phppgadmin\conf」に含まれるconfig.inc.php-distを同フォルダにコピーしてconfig.inc.phpにリネームします。
-作成したconfig.inc.phpを編集します。
 $conf['servers'][0]['host'] = '';        → 'localhost';
  :PosrgreSQLサーバのhost名かIPを指定する。
 $conf['servers'][0]['defaultdb'] = 'template1'; → 'postgres';
  :ログインユーザがアクセスできるDB名を指定するらしい。
   template1はインストール直後は存在しない。
 $conf['extra_login_security'] = true;      → false;
  :trueにしてあると、postgres、root、adminなどのユーザでは
   ログインできないらしい。


<PostgreSQL上へのDBの作成>
-Eclipseに含まれるXAMPP Control Panel((Eclipseフォルダ)\xampp\xampp-control.exe)を起動
-「Apache」を[開始]
-「http://localhost/phppgadmin」をブラウズする
-「phpPgAdmin」画面左上側の「サーバ」>「PostgreSQL」を選択
-右側入力欄のIDに「postgres」を指定して、パスワードにはPostgreSQLをインストールしたときに指定したスーパーユーザ用パスワードを入力して[ログイン]
※環境によって「ログインできない」事例が多数あるようです。さるも上記のconfig.inc.phpの処置が分からなくて結構ハマった。
ちなみにこの現象の対策例として、config.inc.php同様編集対象に上げられる頻度が高いpg_hba.confは、Windows版では「(PostgreSQLフォルダ)\12\data」にありました。


-左側の「PostgreSQL」を選択
-右側の最終行「データベースを作成する」をクリック
-名前:データベース名、Templates:(デフォルト)、エンコード:UTF8、他:お好きに指定して[作成]

DBの枠だけ作成されます。
これって、http(apache)&PHPでPostgreSQLにアクセスできたってことですよね。

ちなみに、PostgreSQLをインストールした結果、スタートアップメニュー内の「PostgreSQL 12」フォルダが作成されています。
そのメニューフォルダ内に「pgAdmin 4」というメニューがあるのですが、実行させたら、Fatal Error:エラー「The application server could not be contacted.」になりました。当面放置します。


まだまだ続きます。


◆MySQLからPostgreSQLへのテーブルの移植
MySQLからエクスポートした.sqlファイルを使って、テーブルと最低限のレコードをPostgreSQLのDBに簡単に仕込めないものかとぐぐってみました。
なんかpgloaderという名前がよく出てきます。ツールのようです。
でも、インストールして何やらセッティングしてと、割と一筋縄では行かない印象を持ちました。

さしてたくさんのテーブルやデータを移行したいわけではなし、1回やればいいだけなので、SQL文を打ち込んでやることにした。
元になるSQL文は、phpMyAdminを使ってエクスポートした .sqlファイル
それをテキストエディタで開けば見られる/コピペもできる。

最初phpPgAdmin上の[SQL]というタブメニューで出てくるSQL欄に入れてやればできるのか?と試してみたが・・・
「SELECT COUNT(*) AS total FROM (入力したSQL) AS sub」としてから実行するみたい。なので「CREATE TABLE ・・・」とかは実行できません。

次に、PostgreSQLに付属しているSQL Shell(psql)を使って見ました。
行けそうです。

MySQLのSQL文とPostgreSQLのSQL文には違いがあるのようなので、そこを修正しながら実行していく必要があります。
では手順です。

-スタートメニュー「PostgreSQL 12」フォルダ内の「SQL Shell(psql)」を起動
-ログインまでに色々入力を求められるで[デフォルト]以外にしたい場合は入力して[Enter]、[デフォルト]でいい場合は単に[Enter]
----psql------------------------------------------
Server [localhost]:
Database [postgres]: teburarec
Port [5432]:
Username [postgres]:
Client Encoding [SJIS]:
ユーザ postgres のパスワード:____
psql (12.3)
"help"でヘルプを表示します。

teburarec=#
---------------------------------------------------


-MySQLでエクスポートしたDBのバックアップ:.sqlファイルをテキストエディタで開く
-新規にテキストエディタを開く(全置換機能があると便利)
-.sql内の「CREATE TABLE ・・・」、あるいは「INSERT INTO ・・・」文をコピーし、新規エディタ側にペースト
-新規エディタ側で、CREATE/INSERT SQL文を編集
 1)テーブル名、カラム名を囲んでいる「`」バッククォートを削除
 (「`」を「」Emptyで全置換してやればよい。)
 2)「int(xx)」型を「integer」に置き換え
 3)「tinyint(x)」型を「smallint」に置き換え
 3)「UNSIGNED」は削除
 ※これによりカラムデータの最大値が変わるので要注意。
 4)「ON UPDATE CURRENT_TIMESTAMP」は削除
 ※タイムスタンプの更新はスクリプト内に作り込む必要がある。
 5)「ENGINE=InnoDB」、DEFAULT CHARSET=utf8」等は削除
-編集したSQL文をコピーしてSQL Shell(psql)プロンプト上にペーストし[Enter]
 ※エラーメッセージが出てないか確認すること。何も出ない場合もNGなので注意。
 ※一つのクエリの最後は「;」が入ってないとダメ。


-.sql内の「ALTER TABLE ・・・」文をコピーし、新規エディタ側にペースト
-新規エディタ側で、ALTER SQL文を編集
 1)テーブル名、カラム名を囲んでいる「`」バッククォートを削除
 2)あとは、設定内容次第なので以下に例を記載
----SQL-------------------------------------------
(1)PRIMARY KEY、INDEX設定
【MySQL】
ALTER TABLE tablename
ADD PRIMARY KEY (columnname),
ADD KEY columnname (columnname) USING BTREE;

【PostgreSQL】
ALTER TABLE tablename ADD PRIMARY KEY (columnname);
CREATE INDEX tablename_columnname_idx ON tablename USING BTREE (columnname);

【補足】
INDEX指定は「ALTER TABLE ・・・, ADD KEY ・・・」ではできない模様。
なので「CREATE INDEX ・・・」で指定する。その際のインデックス名は、
テーブルが異なってもユニークでなければならないらしい。

(2)UNIQUE KEY設定
【MySQL】
ALTER TABLE tablename
ADD UNIQUE KEY columnname (columnname);

【PostgreSQL】
ALTER TABLE tablename ADD UNIQUE (columnname);

【補足】
UNIQUE指定は、「KEY」を無くすだけでできた。

(3)AUTO_INCREMENT設定
【MySQL】
ALTER TABLE tablename
MODIFY columnname integer NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

【PostgreSQL】
CREATE SEQUENCE tablename_columnname_seq;
ALTER TABLE tablename ALTER columnname SET DEFAULT nextval('tablename_columnname_seq');
ALTER SEQUENCE tablename_columnname_seq OWNED BY tablename.columnname;

【補足】
AUTO_INCREMENT属性は、PostgreSQLではSERIAL型のカラムとして作成する
ことができるそうです。
ただし、「型」と表現したりするものの、正式な「型」ではないらしい。
「ALTER TABLE tablename ALTER COLUMN columnname SERIAL;」とやると型指定エラー
になる。

参考:http://dqn.sakusakutto.jp/2012/03/postgresql_alterserial.html
上記の変更後SQLは、数値インクリメントのシーケンスを作成してやって、
カラムにリンクしている感じ。
すでにテーブルにデータが入っている場合は、
「SELECT setval('tablename_columnname_seq', 19);」とかで初期値を設定して
やる必要もある。

(4)FOREIGN KEY設定
【MySQL】
ALTER TABLE tablename
ADD CONSTRAINT tablename_fkN FOREIGN KEY (columnname) REFERENCES ref_tanlename (ref_columnname);

【PostgreSQL】
ALTER TABLE tablename
ADD CONSTRAINT tablename_fkN FOREIGN KEY (columnname) REFERENCES ref_tanlename (ref_columnname);

【補足】
MySQLと同じSQLでできた。
---------------------------------------------------

-編集したSQL文をコピーしてSQL Shell(psql)プロンプト上にペーストし[Enter]
 ※複数SQL文(;区切り)をコピペして一度に実行できます。

-全ての作成・設定が終了したら、\d[Enter]とやるとテーブル一覧を出力します。※
-psqlを終了する場合は、\q[Enter]とやれば抜けられます。

※この手の結果参照に関しては、phpPgAdminの方が便利です。
phpPgAdminを起動して、左側欄から作成したDBを展開していくと作成されたテーブルがツリー表示されます。
テーブル名を選択して、右側上部の「カラム」タブを選択すると右側下にカラム一覧が表示されます。
「インデックス?」タブを選択すると、INDEXとPRIMARY KEYの一覧が表示されます。
「検査制約?」タブを選択すると、PRIMARY KEYとFOREIGN KEYの一覧が表示されます。

注:「表示」タブを選択すると「ERROR: 列"relhasoids"は存在しません」と出てます。
インストールしたphpPgAdminがPostgreSQL V12に対応できてないから?
ともかく、しばらく放置します。



◆PostgreSQLへのアカウントの追加
これまで、PostgreSQL(DB)へのアクセスに、「postgres」というデフォルトのスーパーユーザのアカウントを使ってきましたが、PHPからアクセスする場合のアカウントもこのままというのはちょっと不味い気がするので、新規にアカウントを作成します。
PostgreSQLでは、ユーザアカウントのことをロールと呼ぶんだそうです。

SQL Shell(psql)を使う場合は、postgresでログインして、
CREATE ROLE new_account WITH LOGIN PASSWORD 'password';
GRANT ALL ON ALL TABLES IN SCHEMA public TO new_account;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO new_account;
とかやればいいみたいです。
2行目、3行目は、テーブルとシーケンスへのアクセス権を設定するものです。
スキーマ:publicの下にある全テーブルと全シーケンスへの権限を設定しています。たぶん。

phpPgAdminを使う場合は、postgresでログインして、
-左側欄の「PostgreSQL」を選択
-右側上部の「ロール?」タブを選択
-ロールリストの下の「ロールを作成する」をクリック
-右側の表示内容がかわるので、
 「名前」:ユーザアカウント入力
 「パスワード」:パスワード入力
 「確認」:パスワード入力
 「特権を引き継ぎますか?」:チェック
 「ログインできますか?」:チェック
 した後、画面したの[作成]ボタンをクリック
 ※「メンバ」絡みの設定欄から何か選択したりするとうまくいかなかった。

さるphpPgAdminでやってみました。
でも結局上記だけでは、作成したDBへのアクセス権が得られず、SQL Shell(psql)で「GRAND ALL ・・・」をやりました。
上記phpPgAdminの操作では不足があるということですので、ご注意ください。


◆PostgreSQL向けスクリプトへの変更
それでは、MySQLを前提として作成した、PHPスクリプトを変更していきます。
単にMySQL依存部分をPostgreSQL用に書き換えるのはあまり・・・なので、定数の値に従ってどちらかの処理を実行するように処理を追加しました。
以下、各DB向けの関数の対応表とマルチDB対応時の処置内容です。

参考:https://www.php.net/manual/ja/ref.pgsql.php
参考:https://www.javadrive.jp/php/postgresql/
参考:https://blog.webcreativepark.net/2005/12/14-162805.html

(1)DBへの接続と切断
MySQLPostgreSQL用途
$mydbi = new mysqli(・・・);$mydbi = pg_connect($connection_string);DB接続
$errno = mysqli_connect_errno();(該当なし?)エラー番号取得
$errmsg = mysqli_connect_error();$errmsg = pg_last_error();エラーメッセージ取得
$mydbi->close();pg_close($mydbi);DB切断


(2)SQL(クエリ)の実行
MySQLPostgreSQL用途
$result = $mydbi->query($str_query);$result = pg_query($mydbi, $str_query);クエリ実行
$count = $result->num_rows;$count = pg_num_rows($result);結果行数を取得
$row = $result->fetch_array(MYSQLI_ASSOC);$row = pg_fetch_array($result, NULL, PGSQL_ASSOC);行取出し


(3)トランザクションとロールバック
MySQLPostgreSQL用途
$mydbi->query("START TRANSACTION");pg_query($mydbi, "BEGIN");トランザクション開始
$mydbi->commit();pg_query($mydbi, "COMMIT");コミット
$mydbi->rollback();pg_query($mydbi, "ROLLBACK");ロールバック


(4)文字列のエスケープ
MySQLPostgreSQL用途
$str_esc = $mydbi->real_escape_string($str_in);$str_esc = pg_escape_string($mydbi, $str_in);特殊文字のエスケープ


(5)その他
上記(1)~(4)に記載したPostgreSQL対応を追加しただけでは、うまくいかない点が若干ありました。

・「NOT NULL」指定のカラム値を省略したレコード追加(INSERT)
MySQLではエラーにはならず、データ型に応じたデフォルト値を勝手に入れてくれていたようです。
PostgreSQLは、「NOT NULL」指定があろうがなかろうが、「DEFAULT ・・・」指定がされていないカラムを省略してレコードを追加しようとした場合、その値をNULLにしようとします。でも、「NOT NULL」指定は有効なのでエラーになります。
これは、レコード追加時にセットを省略する可能性のあるカラムには「DEFAULT ・・・」指定をすことで対応。
phpPgAdminでもできます。psqlでやる場合は、「ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT value;」でできるようです。

・AUTO_INCREMENTカラムの次の値の取得
つまり、AUTO_INCREMENTのカラムがあった場合、レコードを追加するときにそのカラムは未指定でも勝手に値が設定されますよね。
MySQL版では、「SHOW TABLE STATUS LIKE 'tablename'」をクエリとして実行すれば、次に予定している値を取得することができました。
取り出したときの連想配列の要素名がMySQLの場合、「Auto_increment」になります。

でも、PostgreSQLでは「シーケンス」なる仕掛けでやってるので、当然取り出し方も違います。
クエリは「SELECT last_value+1 AS next_no FROM sequencename」です。
この場合、「AS Auto_increment」と指定ても、取り出し時の要素名は「auto_increment」(先頭が小文字)なってしまうので、その後の取り出しの処理もMySQLとは分けました。

・「ON UPDATE CURRENT_TIMESTAMP」属性の代わりの処理の追加(後日追伸)
すっかり忘れてました。
PostgreSQLには「ON UPDATE CURRENT_TIMESTAMP」がないために、レコードの最終更新日付を取ってあるような場合、UPDATEする度にTIMESTAMPカラムを更新してあげる必要があります。
PostgreSQLでは=current_timestampと指定することもできるそうですが、MySQLと共通の=now()を使ってUPDATE時のSQL作成処理を共通にします。



だいたい以上で、PostgreSQLを使った動きもまあまあ確認できました。
環境構築/DB作成が面倒でしたが、スクリプトは割と共通化できたと思います。


では、この話はこの辺で。
次回は、元の「スマフォだけで動作する音声認識」に戻ります。

m(__)m
スポンサーサイト





コメントの投稿

非公開コメント

カレンダー
01 | 2024/02 | 03
- - - - 1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 - -
プロフィール

さるもすなる

Author:さるもすなる
さるです。別HPサイト「さるもすなる」から侵食してきました。 山菜/きのこ、それとタイトルにしたPPバンド籠のことをメインに徒然に・・・・暇を持て余したさるの手仕事:男手芸のブログってことで。

最新記事
最新コメント
月別アーカイブ
カテゴリ
天気予報

-天気予報コム- -FC2-
本家のHPのトップ
山菜や茸の話です
PPバンドの籠作品と作り方です
投稿をお待ちしております



PVアクセスランキング にほんブログ村 にほんブログ村 ハンドメイドブログへ



マニュアルのお申し込み



検索フォーム
リンク
RSSリンクの表示
ブロとも申請フォーム

この人とブロともになる

QRコード
QR