過去の桐井戸端ＢＢＳ　（桐ver.８）
21104 桐のデータの一部をweb公開するためPostgreSQLとリンクさせたい 文 2003/06/25-22:49
久しぶりに投稿します。
V8sp6です。
現在桐データのデータの一部をweb公開するためPostgreSQLとリンクさせたいと取り組んでいます。
従来の業務をそのまま継続するためと、桐とPostgreの項目のマッチングが取れないため、
桐tblを主データベースとして外部データベースでPostgreを開きtbl読み込みか、併合しかできません。
データのリンクをするにあたって以下の２方法を取っていますが、
どちらの方法が良いかご意見並びに手法をお聞きしたいと思います。

１．Postgreのデータを全て削除して、全て再読込する。
尚、web閲覧時に偶然データが空になる可能性があります。

２．主キー（カウンタ型）で照合、他の項目は置換挿入−複写する。
新規レコードは複写出来るが、削除項目は残ってしまいます。

現在、レコード数は１０００程度ですが５０００レコード／年増加する予定でスピード的にどちらが早そうなのかと、
もし、２の方法を取る場合、削除レコードをどのように同期させた方がよいのかが判りません。
１０００件のデータでは１の方が早いような感じですが、一度全データを読み込んで処理しているため、
どちらも２０秒以上かかっています。

宜しくお願いします。
21112 Re:PostgreSQLデータとの同期 hidetake 2003/06/26-12:31
記事番号21104へのコメント
やり方はいろいろあると思いますが・・・　(^^)

その前に，データの更新の時間も気にしないといけないほど
アクセスのあるサイトなのですか？
それにデータのアップデイトはどのぐらいの頻度で行うのでしょうか？

次に更新の間(とは言っても桐がどのような仕組みでデータを
アップしているかにより PostgreSQL の自動的なトランザクションがどのようにかかるかもよりますが？)，
相手に中間データを見られないようにするには，データ更新中のフラグを立ててから
(適当なテーブルにフラグデータを立てる)データを更新し，
Webアプリ側はそのフラグをみて「更新中」であれば，
Web に「データ更新中・・・」の表示もできると思います。


次に，「更新中」の時間を短くするには，実際の Web で使うデータと，
桐で直接処理するデータは別にし，桐側でデータを作りサーバにワークファイルとして
データをアップした後にサーバ側でデータを移し替えれば処理時間は相当短くなると思います。
１つはテーブル名自体を入れ替えちゃう方法もあるかも知れませんし，
メインの方のレコードを全て DELETE して，その後でINSERT する方法もあると思います。

で，先の更新中のフラグと組みあわせるのであれば，
先に桐でデータを作っておきアップした後で，フラグテーブルに例えば
１を INSERT して，その条件を元にトリガで，メインのデータを更新させると言う方法も
組みあわせる事ができると思います。
メインのデータが更新し終わったら，フラグテーブルのデータを削除までする。とか！

例えば次のような感じで・・・

実データが table だとしてワークテーブルが tablesrc で
フラグ用のテーブルを tableupf としてみた場合です。

CREATE FUNCTION tableupf() RETURNS OPAQUE AS '
BEGIN
IF NEW.update = 1 THEN
DELETE FROM table;
INSERT INTO table select * FROM tablesrc;
DELETE FROM tableupf;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE "tableupf" ("update" smallint);
CREATE UNIQUE INDEX tableupf_pkey ON tableupf("update");

CREATE TRIGGER tableupf AFTER INSERT OR UPDATE ON tableupf
FOR EACH ROW EXECUTE PROCEDURE tableupf();

これで tableupf に行を追加し「1」を入れればデータがメインの方に移されるはずです。(桐からでも)


もっと安全性や一時の中断も許されないと言うのならトランザクション処理まで細かく行わないといけないでしょうが，
PostgreSQLではトリガプロシージャ内ではトランザクションがかけられないようですので，
そのためには SQL を自由に処理できる方法を組みあわせないといけなくなると思います。

これは桐からはできないので，サーバ側に Web からの命令で
この辺を処理するスクリプトをおいて，桐のデータをアップし終わった段階で
(桐からでも)リクエストを発生させ，データの更新作業を行うとか・・・


# PostgreSQL の場合，トランザクションを使わないのなら
# データ削除は TRUNCATE TABLE name; の方が良かったかな？　(^^;



21113 Re:PostgreSQLデータとの同期 野良犬 2003/06/26-12:53
記事番号21104へのコメント
私も同様のことをしていますので、参考までに

>１．Postgreのデータを全て削除して、全て再読込する。

> 尚、web閲覧時に偶然データが空になる可能性があります。

>

>２．主キー（カウンタ型）で照合、他の項目は置換挿入−複写する。

> 新規レコードは複写出来るが、削除項目は残ってしまいます。


現在２に近い方法でやっています。既存のレコード数がかなり多いので、
１の方法では相当時間がかかり採用しませんでした。

　また、２の方法でも、数万件規模になると、
工夫しないと現実的な処理速度を得るのは難しいです。


具体的には、処理を三段階のわけます。

１．削除データの処理

　　これは、外部ＤＢを開いておいて、主キーで併合絞り込みをした、

　　補集合になります。これを削除します。

２．更新データの処理

　　外部ＤＢを開き、全項目で併合絞り込みをした補集合で、再度併合置換を行います。

３．追加データの処理

　　桐表を開いておいて、外部ＤＢに主キーで併合絞り込みをして補集合をとり外部ＤＢから、
絞り込まれている桐表を読み込みコマンドで読み込みます。


さて、データ数が5000/年 くらいなら、
この程度でも十分実用的な速度がでるとは思いますが、それ以上の速度が必要なら、
作業表を作ることで、併合にかかる時間を短縮できます。


21115 Re:PostgreSQLデータとの同期 hidetake 2003/06/26-15:40
記事番号21112へのコメント
>次に，「更新中」の時間を短くするには，実際の Web で使う
>データと，桐で直接処理するデータは別にし，桐側でデータを
>作りサーバにワークファイルとしてデータをアップした後に
>サーバ側でデータを移し替えれば処理時間は相当短くなると
>思います。

PostgreSQL 側で元にするデータを作るにも ODBC 経由でやると
それなりに時間もかかるだろうから，桐側でデータはテキストで作り
(pg_dumpで作られるデータと同じ形式)，
それをサーバにアップデイトしておき COPY コマンドで
テーブルに読み込ませた方が速いかも？

と言う方法もあるのかな？

先の例だと tablesrc はデータをアップするだけだから
桐で表を開いて「外部DB書き出し」だけで，元データの削除と新規データの作成は済みます。
（時間もそれほど気にしなくても良くなります）

当然，実データの削除については ODBC 経由で全件表示させて全選択し削除するより
サーバ側で DELETE (あるいは TRUNCATE)した方が桁違いに速いでしょうし，
実データと差分の処理を行うとしても，これもアップされたワークテーブルとの間で，
サーバ側で SQL 文書いて実行させるのもオツなような気がします。　(^^;

それをどう言った方法で実行させるタイミングを作るのか？
と言う課題はあるのかと思います。トリガだけですますのか？
　トランザクションまで行わせるのか？　とか・・・

結構大きなデータ(件数の多い)になって時間がかかるのだったら，
それはそれなりに？工夫のし甲斐もありそうです。
でも，あまりにデータが大きいと全てのデータを全部サーバに読み込ますと言うのも無駄ですね！　(^^;

21118 Re:PostgreSQLデータとの同期 hidetake 2003/06/26-16:08
記事番号21115へのコメント
>当然，実データの削除については ODBC 経由で全件表示
>させて全選択し削除するよりサーバ側で DELETE (あるい
>は TRUNCATE)した方が桁違いに速いでしょうし

Access の場合はパススルークエリも使えるけど
桐の場合は SQL を直接投げかける手立てが今のところ無いようなので，
先のトリガーを使ってある値を代入したら，元のデータを削除するような，
コマンドメールならぬコマンドクエリーを作って
利用するのも面白いかも知れませんね？

例えば先の tableupf に「2」を入れたら
DELETE FROM table
するとか，「3」を入れたらどうするだとか・・・

もし，PostgreSQL の plpgsql 等で eval が使えるのなら，
それこそ値として，コマンドそのものを与えるとか？
　そう言う工夫も楽しいような気がします。　(^^;


21119 Re:PostgreSQLデータとの同期 hidetake 2003/06/26-16:20
記事番号21118へのコメント
>もし，PostgreSQL の plpgsql 等で eval が使え
>るのなら，それこそ値として，コマンドそのもの
>を与えるとか？　そう言う工夫も楽しいような
>気がします。　(^^;

EXECUTE と言うのがあるのですね・・・

23.5.4. 動的問い合わせの実行
http://www.postgresql.jp/document/pg721doc/programmer/plpgsql-statements.html


21121 コマンドクエリ on PostgreSQL hidetake 2003/06/26-17:56
記事番号21119へのコメント
>コマンドクエリ

桐でパススルークエリなど直接 SQL を投げかける手段が無いので奥の手として，仕方なしに・・・　(;_;)

psql -d DATABASE -f commad.sql

で，桐からのデータ更新で，その文字列をコマンドとして実行できるようになりました！

DELETE FROM table;

とか

INSERT INTO table SELECT * FROM tablesrc;

もできました！　ヽ(^o^)丿

当然，エラー処理や実際にどうなったか？などの制限はありますが！

========================================================
comand.sql
--------------------------------------------------------
DROP FUNCTION commandf();
\i commandf.sql

DROP TABLE command;
CREATE TABLE command (id int2 primary key, command text);

DROP TRIGGER command ON command;
CREATE TRIGGER command AFTER INSERT OR UPDATE ON command
FOR EACH ROW EXECUTE PROCEDURE commandf();
========================================================

========================================================
comandf.sql
--------------------------------------------------------
CREATE FUNCTION commandf() RETURNS OPAQUE AS '
BEGIN
IF NEW.command IS NOT NULL THEN
EXECUTE NEW.command;
END IF;
DELETE FROM command;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
========================================================


21122 Re:コマンドクエリ on PostgreSQL hidetake 2003/06/26-18:33
記事番号21121へのコメント
>コマンドクエリ

桐からの命令で，例えば
DELETE FROM table; INSERT INTO tableupf values (-1);
と言った，２つの命令とも成り立つ文字列を投げかけた場合と

DELETE FROM table; INSERT INTO tableupf values (a);
と言った，２つ目で成り立たない文字列を投げかけた場合，

１つ目は当然的にエラー無しに実行されますが，２つ目は桐にエラー表示されて，
１つ目の命令も結果的に実行されません！

と言う事は１つの桐からのあるテーブルの更新も，例え AFTERなトリガであっても，
全ての命令が完全にエラー無しで実行終了しないと，
全体がコミットされないような仕組み（自動的なトランザクション処理）になっているようです。

ですので，この状態では命令が全部実行し終えるまでは，
外部からの SELECT 等も古いままの状態を返すだけでは無いでしょうか？

桐側でデータ操作をこまめにやれば，その度にトランザクションはかかるものの，
その状態状態で区切れて，相手にはその途中状態が見られてしまうだろうけど，
桐側で一連の作業としてデータが流れるように，
SQL Server 側に仕組みを作っておけば，
桐だけでも，何も任意的なトランザクション処理をしなくても用途によっては結構いけるのでは無いのかなぁ〜？と
そんな感じがしました。


21123 Re:コマンドクエリ on PostgreSQL 文 2003/06/26-20:38
記事番号21122へのコメント
色々と丁寧にありがとうございます。
ほとんどの内容が？？ 理解できませんでした。すみません。
一応閲覧規模は全国の営業所１５０人程度になると考えていますが。
サーバー部分はシステム部門に相談して設定してもらいますので、
余り詳しくなく細かな変更や設定が難しいため、野良犬さんの方法をとりたいと考えています。
21124 Re:PostgreSQLデータとの同期 文 2003/06/26-20:49
記事番号21113へのコメント
ありがとうございます。
併合絞り込みは使ったことがありませんがチャレンジしてみます。

以前、試しにテーブルを絞り込んで外部データベースで読み込んだところ、
全データが読み込まれてしまったため、絞り込んだ部分のみの読み込みは出来ないと思いこんでいました。
21130 Re:コマンドクエリ on PostgreSQL hidetake 2003/06/27-07:02
記事番号21123へのコメント
文さんの担当とかどこまでやるのとか良くわかりませんが，
Webアプリケーションまで担当でなくて単にデータ渡すところまでなのですかね？
　Web アプリケーションまで担当されるとなると PostgreSQL (Server) 側も弄れる(弄れないといけない)
必要もあるでしょうし？

それと，Web アプリケーション(動的なスクリプト全般)にタッチするなら，
例え会社内での利用であってもインターネットを利用するなら，
少なくとも次の内容などを充分に勉強された方がよろしいかと思います。そうでないと，
後でとんでも無く危ないシステムができたりしますから・・・

IPA ISEC　セキュア・プログラミング講座
http://www.ipa.go.jp/security/awareness/vendor/programming/


21132 Re:PostgreSQLデータとの同期 野良犬 2003/06/27-08:51
記事番号21124へのコメント
>以前、試しにテーブルを絞り込んで外部データベースで読み込んだところ、全データ


>が読み込まれてしまったため、絞り込んだ部分のみの読み込みは出来ないと思いこんでいまし
>た。

読み込み条件の"編集表を使用する"のチェックをはずすとそうなってしまいます。

デフォルトでは、チェックはついているはずなんですが。
一括処理からでもなにも指定しなければ、絞り込まれたデータのみが処理対象になります。


21133 Re:コマンドクエリ on PostgreSQL 野良犬 2003/06/27-08:54
記事番号21122へのコメント
たいへん、おもしろい方法ですね。参考になります。

機会があれば使ってみようと思います。

>ですので，この状態では命令が全部実行し終えるまでは，外部
>からの SELECT 等も古いままの状態を返すだけでは無いでしょ
>うか？

ですね、PostgreSQLではそうなると思います。

21141 Re:コマンドクエリ on PostgreSQL 文 2003/06/27-15:25
記事番号21130へのコメント
>文さんの担当とかどこまでやるのとか良くわかりませんが，
>Webアプリケーションまで担当でなくて単にデータ渡すところ
>までなのですかね？

データを渡すところ止まりです。
web上のスクリプト（表示、検索、絞り込み）はシステム部門で担当してもらてい ます。

過去、アクセスデータをアップロードしたりして同期させたり、他の方法を取ったりしたことがありますが、詳しい説明ができません。
他の方法ではpostgresODBCのposdloadbc.dllを書き換え、リアルタイムに同期させているようでしたが、内容はわかりません。
また、社内でのアクセス権限制御や閲覧者（今回は協力会社）でのセキュリティ、
会社間の接続もよく判っていません。 ←勉強不足ですね。

今回は部署で使用している桐（昔から使用しているがかなり、かたみがせまくねっている）からのデータアップとなりました。

>それと，Web アプリケーション(動的なスクリプト全般)に
>タッチするなら，例え会社内での利用であってもインター
>ネットを利用するなら，少なくとも次の内容などを充分に
>勉強された方がよろしいかと思います。そうでないと，後
>でとんでも無く危ないシステムができたりしますから・・・
>
>IPA ISEC　セキュア・プログラミング講座
>http://www.ipa.go.jp/security/awareness/vendor/programming/
>
参考にしてみます。
サーバー部に触れる環境にないため、実践がともないませんが・・・。
21176 Re:コマンドクエリ on PostgreSQL hidetake 2003/06/29-21:45
記事番号21121へのコメント
>>コマンドクエリ
>桐でパススルークエリなど直接 SQL を投げかける手段が
>無いので奥の手として，仕方なしに・・・　(;_;)

桐からは直接 SQL を投げかけれませんが，このトリガーを用いた方法以外に，
#21172 の #DSQL 関数を書いていて，もう一つの方法を思いつきました！　(^^;

それは Webアプリケーション等で「ダイレクトSQLコマンドインジェクション攻撃」と
言われるアタック方法と同じ手法です。

ダイレクトSQLコマンドインジェクション
http://www.ipa.go.jp/security/awareness/vendor/programming/a02_01.html

桐の #DSQL 関数は SELECT 文の WHERE 以降の内容に関して，
任意の文字列を投げかけられる関数ですがそれを応用します。WHERE 句までは
正常に通るようにして ; で区切り，その後に任意の SQL 文を書きます。
但し，() 等を含んでいると桐側で勝手に () を補う場合があるので，
それを見越した括弧の記述が必要になります。

例えば全く新しいテーブルを作る場合は次のような #DSQL になります。
#DSQL("id>0);DROP TABLE reikaiupf;CREATE TABLE reikaiupf (update
smallint PRIMARY KEY")

データを挿入する場合は次のような #DSQL で可能でした。
#DSQL("id>0);delete from reikaiupf;insert into reikaiupf values (-1")

一応，これで本来の望むべきデータも桐側に返されて来ます。

但し，これが成功した一発目に現れた桐のテーブルは非常にレイアウトの崩れたというか，
行の高さがきわめて低い状態で表示されました。
高さを直してあげたら次からは，その設定し直した状態で表示されましたが・・・　(^^;
　　この現象の出た桐のバージョンは 桐Ver8 SP7です。

まぁ〜，こう言う？桐のセキュリティホール？をつついた SQL の投げ方もあるようです。　ヽ(;´o｀)丿

最初から素直に SQL文を投げかけてくれさせる方法もあって良いのに・・・　(;_;)


21185 Re:コマンドクエリ on PostgreSQL hidetake 2003/06/30-14:10
記事番号21176へのコメント
>データを挿入する場合は次のような #DSQL で可能でした。
>#DSQL("id>0);delete from reikaiupf;insert into reikaiupf values (-1")

一応サンプルとして，任意の SQL文を投げかけるものを作ってみました。
http://s3.kcn-tv.ne.jp/users/hidetake/data/runsql.txt.gz

例えば入力の画面で次のような内容を入力して実行させてみましたが
うまく動いているようです。
BEGIN;DROP TABLE reikaiwrk;CREATE TABLE reikaiwrk AS SELECT * FROM reikai;COMMIT;

但し，XVW を開く時は ODBC への接続を引き継ぐ方法(書き出しで言えば
「引継ぐ=する」)が無いので会話で表示されますので指定が必要です。　(;_;)

XVWに埋め込んでしまえばダイアログは出ないようにできます。
でも今回のように一般性を求める場合のサンプルになりにくいので・・・

あとは興味ある方は実験してみて下さい。
(何か桐は SQLを２回呼び出しているような？・・・)

先のトリガを使った方法の場合も面白い方法だと思いますが，
こちらはこちらでサーバを選ばない方法でもあると思います。　(^^;


21206 Re:コマンドクエリ on PostgreSQL うにん 2003/07/01-15:49
記事番号21185へのコメント
>但し，XVW を開く時は ODBC への接続を引き継ぐ方法(書き出しで言えば
>「引継ぐ=する」)が無いので会話で表示されますので指定が必要です。　(;_;)

HELPに「接続中の外部データベース」ってありますが、同時に複数接続できるのだから
「選択中の」ですよねえ。
つまり、「引継ぐ」でもいいんですが、外部DBコマンドで接続する時取得した
ハンドルを指定できるコマンドが切断しかないのが何か変なんですよね。。。

21210 Re:コマンドクエリ on PostgreSQL hidetake 2003/07/01-16:58
記事番号21206へのコメント
>HELPに「接続中の外部データベース」ってありますが、同時に複数接続できる
>のだから「選択中の」ですよねえ。
>つまり、「引継ぐ」でもいいんですが、外部DBコマンドで接続する時取得した
>ハンドルを指定できるコマンドが切断しかないのが何か変なんですよね。。。

桐の「外部ＤＢ」コマンドの存在意義がわからなくなってしまいました。

今まで自分で使う時は XVW に関しては DSN もパスワードも埋め込んで使用していました。
だから，「外部ＤＢ」コマンドってあまり意識した事は無かったです。

で，.XVW を開く時は，この「外部ＤＢ」コマンドで接続された状態の意味は無いに等しい？
　その一方で，「書き出し」コマンドの「外部ＤＢ」では「引継ぐ」オプションが使用できるようになったのは，
桐のV9 からで V8 までは，その設定はなく，その都度会話で接続管理を行う必要があったはずです。

だとしたら，「外部ＤＢ」コマンドで接続した外部データベースって
桐 V8 の時は何で使用できる？　使用させようとしていたのでしょう？


私が桐 V8 の時に「外部ＤＢ」コマンドを使ったのは，隠しコマンドの
「SQL実行」を試そうとした時ですが，その時は確かにハンドルの指定が
必要であったように思います。結局，SQL実行は実行できずじまいでした。
なお，「SQL実行」コマンドは，桐 V9 では存在さえも無くなりました！

で，桐 V8 では隠しである「SQL実行」には関係したのかも知れないけど
何をどのようにして使わせようとしていたのでしょう？

また，桐 V9 においては「書き出し」コマンドの「引継ぎ=する」以外に
何に影響するのでしょうか？　わけがわからなくなりました。(?_?)


21225 Re:コマンドクエリ on PostgreSQL うにん 2003/07/02-13:12
記事番号21210へのコメント
>今まで自分で使う時は XVW に関しては DSN もパスワードも埋め込んで
>使用していました。だから，「外部ＤＢ」コマンドってあまり意識した
>事は無かったです。

普通はそうしますよね。定義自体が特定のデータソースでしか意味を持たないので。

>また，桐 V9 においては「書き出し」コマンドの「引継ぎ=する」以外に
>何に影響するのでしょうか？　わけがわからなくなりました。(?_?)

ファイル変換 ODBC ですね。
しかし、外部DBの選択にも「外部DB 接続」コマンドで名前指定するしかないので、
ハンドルの意味がない(^^;切断も名前ですれば済むような？

21232 Re:コマンドクエリ on PostgreSQL hidetake 2003/07/02-18:04
記事番号21225へのコメント
>>また，桐 V9 においては「書き出し」コマンドの「引継ぎ=する」以外に
>>何に影響するのでしょうか？　わけがわからなくなりました。(?_?)
>
>ファイル変換 ODBC ですね。

確かにありました。全然思い浮かばなかったです！　(^_^ゞ

結局「外部ＤＢ」コマンドって，「ファイル変換 ODBC」
コマンドのタメだけに最初は存在したと言う事なのですね？


>しかし、外部DBの選択にも「外部DB 接続」コマンドで名前指定するしかないので、
>ハンドルの意味がない(^^;切断も名前ですれば済むような？

そうですね・・・


取り敢えずお礼として，と言ったって例にはならないけど・・・　(^^;
ハンドルを使う場面がありそうで使えなかった例です。(桐8限定)

外部DB 接続,ODBC="hogehoge" \
,ユーザ名="" \
,パスワード="" \
,接続ハンドル=&秒 \
,終了状態=&実行リターン
sql実行 &秒,sql="create table test (a varchar(10),b varchar(40))"
/*sql実行 &秒,sql="drop table test"

これをやっても，sql実行のところで「ハンドルエラー」になって
しまうのですよね！　無理やり違う値を与えるとハンドルが無効だって怒るし！　:-)

別に使えるコマンドでは無いのであしからず？　_o_


戻る