Clever Geek Handbook

PostgreSQLでの海戦



プログラマーは、データベースに保存されたプロシージャの危険性と利点について激しい議論をしています。今日、私たちは彼らから逸脱し、不可能な状況で再び信じられないほどのことをします。



今日、開発者は可能な限りデータベースにビジネスロジックを構築することを避けようとしています。それでも、たとえばエクスチェンジマッチャーなどに挑戦して作成する愛好家がいて、企業全体がサーバー側をデータベースの保存されたプロシージャに転送することもあります。そのようなプロジェクトの作者は、必要に応じてデータベースで何でもできると主張しています。



ここで私は思わずBGPをめぐる「海の戦い」を思い出します。このゲームをSQLで作成することは可能ですか？この質問に答えるために、私たちはPostgreSQL12サービスとPLpgSQLを使用します。「内部」を見るのが待ちきれない人のために、リポジトリへのリンク。



海戦ゲームでは、ゲーム全体を通してユーザーからの絶え間ない入力が必要です。データベースユーザーと対話する最も簡単な方法は、コマンドラインクライアントです。



データ入力

ユーザーからデータを取得することは、このプロジェクトで最も難しいタスクです。開発の観点から最も簡単な方法は、特別に準備されたテーブルに必要な情報を挿入するために、正しいSQLクエリを作成するようにユーザーに依頼することです。この方法は比較的遅く、ユーザーはリクエストを何度も繰り返す必要があります。 SQLクエリを記述せずにデータを取得できるようにしたいと思います。



PostgreSQLは、COPY…FROM STDINを使用して、標準入力からテーブルにデータを保存することをお勧めします。しかし、このソリューションには2つの欠点があります。



まず、COPY演算子は、アップロードされる情報の量によって制限することはできません。 COPYステートメントは、ファイルの終わりのサインを受け取ったときにのみ終了します。したがって、ユーザーはさらにEOFを入力して、情報入力の完了を示す必要があります。



第二に、保存されたプロシージャと関数にはstdinファイルとstdoutファイルがありません。クライアントを介して通常のSQLクエリを実行する場合、標準の入力ストリームと出力ストリームを使用できますが、ループは使用できません。したがって、1つのSQLコマンドでゲームを実行することはできません。これで話は終わりだったかもしれませんが、狡猾な解決策が見つかりました。



PostgreSQLにはログを記録する機能があります間違ったものを含むすべての要求。さらに、ロギングはCSV形式にすることができ、COPYオペレーターはこの形式で作業できます。 postgresql.conf構成ファイルでロギングを構成しましょう： 



log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_min_error_statement = error
log_statement = 'all'


postgresql.csvファイルは、PostgreSQLで実行されるすべてのSQLクエリを記録するようになりました。ドキュメントの「CSV形式のログ出力の使用」セクションでは、ローテーションを有効にしてcsvログをロードする方法について説明しています。1秒間隔でログをロードすることに関心があります。



ログを毎秒ローテーションするのは実用的ではないため、ログファイルを何度もロードし、ログを含むテーブルに追加します。1人のCOPYオペレーターによる簡単な解決策は、最初にのみ機能し、その後、プライマリキーの競​​合によるエラーが表示されます。この問題は、ステージングテーブルとON CONFLICT DONOTHING句を使用することで解決されます。



ログをテーブルにロードする 
CREATE TEMP TABLE tmp_table ON COMMIT DROP
AS SELECT * FROM postgres_log WITH NO DATA;

COPY tmp_table FROM '/var/lib/postgresql/data/pg_log/postgresql.csv' WITH csv;

INSERT INTO postgres_log
SELECT * FROM tmp_table WHERE query is not null AND command_tag = 'idle' ON CONFLICT DO NOTHING;


データを一時テーブルからpostgres_logに移行するときにフィルターを追加して、ログテーブル内の不要な情報の量を減らすこともできます。ユーザーから正しいSQLクエリを受信する予定はないため、クエリテキストがあり、コマンドタグがアイドル状態のクエリに制限できます。



残念ながら、PostgreSQLにはスケジュールに従ってルーチンを実行するスケジューラがありません。問題はゲームの「サーバー」部分にあるため、ログを毎秒ロードするための保存されたプロシージャを呼び出すシェルスクリプトを作成することで解決できます。



有効なSQLクエリではない、ユーザーが入力した文字列は、postgres_logテーブルに表示されます。この方法では必須のセミコロン区切り文字が必要ですが、EOFを送信するよりもはるかに簡単です。



注意深い読者は、保存されたプロシージャまたは関数の実行中、コマンドラインクライアントはコマンドを処理せず、絶対的に正しいことに気付くでしょう。このようなソリューションが機能するには、「画面」と「キーボード」の2つのクライアントが必要です。



画面クライアント（左）とキーボードクライアント（右）キーボード

を「ペアリング」するために、画面は、キーボードクライアントで入力する必要のある文字の疑似ランダムシーケンスを生成します。「画面」は、クライアントのセッションの一意の識別子（session_id）によってキーボードを識別し、ログテーブルから必要なセッション識別子を持つ行のみを選択します。



クライアントキーボードの出力が役に立たないことは簡単にわかります。また、クライアント画面への入力は1回のプロシージャ呼び出しに制限されています。使いやすくするために、「画面」を背景に送信し、「キーボード」の出力を消すことができます。 



psql <<<'select keyboard_init()' & psql >/dev/null 2>&1


標準入力からデータベースに情報を入力し、保存されたプロシージャを使用できるようになりました。



ゲームループ

ゲームのアクティブな部分

ゲームは条件付きで次のフェーズに分けられます。



  • スクリーンクライアントとキーボードクライアントのインターフェイス。
  • ロビーを作成するか、既存のロビーに接続します。
  • 船の配置;
  • ゲームのアクティブな部分。


ゲームは5つのテーブルで構成されています。



  • フィールドの視覚的表示、2つのテーブル。
  • 船とその状態のリスト、2つの表。
  • ゲーム内のイベントのリスト。


ロビーの作成中に、サーバーであるプレーヤーAがすべてのテーブルを作成し、それらに初期値を入力します。複数のゲームを並行してプレイできるようにするために、タイトルのすべてのテーブルには10桁のロビー識別子があり、ゲームの開始時に疑似ランダムに生成されます。



ゲームロジックの開発は、一般に従来のプログラミング言語での開発と非常によく似ており、構文がほとんど異なり、優れたフォーマット用のライブラリがありません。出力にはRAISE演算子が使用され、psqlの場合はログレベルのプレフィックスが付いたメッセージが表示されます。あなたは彼を取り除くことはできませんが、これはゲームに干渉しません。



デザインの違いもあり、脳を沸騰させます。



コミット時間

ゲームのすべてのロジックはクライアント画面によって起動されます。つまり、1つの手順が最初から最後まで実行されます。さらに、1つのトランザクションについて、COMMIT演算子が明示的に指定されていない場合。



これは、トランザクションが完了するまで、2番目のプレーヤーの新しいテーブルと既存のテーブルの新しいデータが変更されないことを意味します。さらに、時間を操作する場合、now（）関数はトランザクションが開始されたときの現在の時刻を返すことを覚えておくことが重要です。



コミットするのは思ったほど簡単ではありません。それらは手順でのみ許可されます。関数内でトランザクションをコミットしようとすると、関数の外部のトランザクション内で動作するため、エラーが発生します。



ゲームの実行

ゲームの開始この

ようなゲームを実際の環境で実行することはお勧めしません。幸い、ゲームを使用してデータベースをすばやく簡単に展開することができます。ではリポジトリあなたは、PostgreSQL 12.4と、必要な構成でイメージを構築しますDockerfileを見つけることができます。イメージをビルドして実行します。 



docker build -t sql-battleships .
docker run -p 5432:5432 sql-battleships


画像内のデータベースへの接続： 



psql -U postgres <<<'call screen_loop()' & psql -U postgres


コンテナ内のPostgreSQLは信頼認証ポリシーを使用することに注意してください。つまり、パスワードなしですべての接続を許可します。すべてのゲームを完了したら、コンテナのプラグを抜くことを忘れないでください！



結論

他の目的で特別なツールを使用すると、専門家から否定的なフィードバックが生じることがよくあります。ただし、意味のない興味深いタスクを解決すると、横方向の思考が訓練され、適切な解決策を探すためにさまざまな観点からツールを探索できます。



本日、必要に応じてSQLで必要なものを記述できることを再度確認しました。それでも、ツールを本来の目的のために本番環境で使用し、小さなホームプロジェクトのように楽しむことをお勧めします。





More articles:


All Articles