Pertempuran laut di PostgreSQL

Ada perdebatan sengit di antara para programmer tentang bahaya dan manfaat prosedur tersimpan dalam database. Hari ini kita akan menyimpang dari mereka dan sekali lagi melakukan yang luar biasa dalam kondisi yang tidak mungkin.



Saat ini, pengembang mencoba untuk menghindari membangun logika bisnis di database bila memungkinkan. Namun demikian, ada peminat yang menantang diri mereka sendiri dan membuat, misalnya, pencocokan pertukaran , dan terkadang seluruh perusahaan mentransfer sisi server ke prosedur yang tersimpan di basis data. Penulis proyek semacam itu berpendapat bahwa Anda dapat melakukan apa saja pada database jika Anda mau.



Di sini saya tanpa sadar mengingat "pertempuran laut" atas BGP . Apakah mungkin membuat game ini dalam SQL? Untuk menjawab pertanyaan ini, kami akan menggunakan layanan PostgreSQL 12, serta PLpgSQL. Bagi mereka yang tidak sabar untuk melihat "di bawah tenda", link ke repositori .



Game pertempuran laut membutuhkan masukan konstan dari pengguna di sepanjang game. Cara termudah untuk berinteraksi dengan pengguna database adalah klien baris perintah.

Masukan data

Mendapatkan data dari pengguna adalah tugas tersulit dalam proyek ini. Cara termudah dari sudut pandang pengembangan adalah meminta pengguna untuk menulis kueri SQL yang benar untuk memasukkan informasi yang diperlukan ke dalam tabel yang disiapkan secara khusus. Metode ini relatif lambat dan mengharuskan pengguna untuk mengulangi permintaan tersebut berulang kali. Saya ingin dapat mengambil data tanpa menulis kueri SQL.



PostgreSQL menyarankan penggunaan COPY… FROM STDIN untuk menyimpan data dari input standar ke tabel. Tetapi solusi ini memiliki dua kekurangan.



Pertama, operator COPY tidak dapat dibatasi oleh jumlah informasi yang diunggah. Pernyataan COPY berakhir hanya jika menerima tanda akhir file. Dengan demikian, pengguna juga harus memasukkan EOF untuk menunjukkan penyelesaian entri informasi.



Kedua, tidak ada file stdin dan stdout dalam prosedur dan fungsi yang tersimpan. Aliran input dan output standar tersedia saat menjalankan kueri SQL reguler melalui klien, tetapi loop tidak tersedia di sana. Dengan demikian, Anda tidak dapat menjalankan game dalam satu perintah SQL. Ini bisa menjadi akhir dari cerita, tetapi solusi licik ditemukan.



PostgreSQL memiliki kemampuan untuk logsemua permintaan, termasuk yang salah. Selain itu, logging dapat dalam format CSV, dan operator COPY dapat menggunakan format ini. Mari kita konfigurasikan logging di file konfigurasi postgresql.conf:

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

File postgresql.csv sekarang akan merekam semua kueri SQL yang dijalankan di PostgreSQL. Dokumentasi, di bagian Menggunakan Output Log Format CSV , menjelaskan cara memuat log-csv dengan rotasi diaktifkan. Kami tertarik untuk memuat log dengan interval satu detik.



Karena tidak praktis memutar log setiap detik, kami akan memuat file log berulang kali, menambahkan ke tabel dengan log. Solusi langsung dari satu operator COPY hanya akan berfungsi pertama kali, dan kemudian akan menampilkan kesalahan karena konflik kunci utama. Masalah ini diselesaikan dengan menggunakan tabel pementasan dan klausa ON CONFLICT DO NOTHING .





Anda juga dapat menambahkan filter saat memigrasi data dari tabel sementara ke postgres_log, mengurangi jumlah informasi yang tidak perlu di tabel log. Karena kami tidak berencana untuk menerima kueri SQL yang benar dari pengguna, kami dapat membatasi diri pada kueri di mana terdapat teks kueri dan tag perintah tidak digunakan.



Sayangnya, PostgreSQL tidak memiliki penjadwal yang menjalankan rutinitas sesuai jadwal. Karena masalahnya terletak di bagian "server" dari permainan, maka masalah ini dapat diselesaikan dengan menulis skrip shell yang akan memanggil prosedur tersimpan untuk memuat log setiap detik.



String apa pun yang dimasukkan oleh pengguna yang bukan kueri SQL valid sekarang akan muncul di tabel postgres_log. Meskipun metode ini memerlukan pemisah titik koma wajib, ini jauh lebih mudah daripada mengirim EOF.



Pembaca yang penuh perhatian akan mencatat bahwa selama pelaksanaan prosedur atau fungsi yang tersimpan, klien baris perintah tidak akan memproses perintah dan akan benar-benar benar. Agar solusi seperti itu berhasil, dua klien diperlukan: "layar" dan "keyboard".



^{Klien layar (kiri) dan klien keyboard (kanan)}

Untuk "memasangkan" keyboard, layar menghasilkan urutan karakter pseudo-random yang harus dimasukkan pada keyboard klien. "Layar" mengidentifikasi keyboard dengan pengenal unik sesi klien (session_id) dan kemudian memilih dari baris tabel log saja dengan pengenal sesi yang diperlukan.



Sangat mudah untuk melihat bahwa output keyboard-klien tidak berguna, dan input ke layar klien dibatasi untuk satu panggilan prosedur. Untuk kemudahan penggunaan, Anda dapat mengirim "layar" ke latar belakang, dan mematikan keluaran "keyboard":

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

Kami sekarang memiliki kemampuan untuk memasukkan informasi dari input standar ke dalam database dan menggunakan prosedur yang tersimpan.

Putaran game

^{Bagian aktif dari permainan}

Permainan ini secara kondisional dibagi menjadi beberapa fase berikut:

• antarmuka klien layar dengan klien keyboard;
• membuat lobi atau menghubungkan ke yang sudah ada;
• penempatan kapal;
• bagian aktif dari permainan.

Permainan ini terdiri dari lima tabel:

• tampilan visual lapangan, dua tabel;
• daftar kapal dan kondisinya, dua tabel;
• daftar acara dalam game.

Selama pembuatan lobi, pemain A, server, membuat semua tabel dan mengisinya dengan nilai awal. Agar dapat memainkan beberapa game secara paralel, semua tabel dalam judul memiliki pengenal lobi sepuluh digit, yang dihasilkan secara pseudo-random di awal game.



Pengembangan logika permainan umumnya sangat mirip dengan pengembangan dalam bahasa pemrograman tradisional dan sebagian besar berbeda dalam sintaksis dan kurangnya perpustakaan untuk pemformatan yang bagus. Untuk keluaran, operator RAISE digunakan, yang untuk psql menampilkan pesan dengan awalan tingkat log. Anda tidak akan bisa menyingkirkannya, tapi ini tidak mengganggu permainan.



Ada perbedaan desain juga, dan itu membuat otak mendidih.

Komit waktu

Semua logika permainan diluncurkan oleh klien layar, yaitu, satu prosedur dijalankan dari awal hingga akhir. Selain itu, untuk satu transaksi, jika operator COMMIT tidak ditentukan secara eksplisit.



Artinya tabel baru dan data baru di tabel yang ada tidak akan berubah untuk pemain kedua sampai transaksi selesai. Selain itu, ketika bekerja dengan waktu, penting untuk diingat bahwa fungsi now () mengembalikan waktu saat ini pada saat transaksi dimulai .



Membuat komit tidak semudah kedengarannya. Mereka hanya diperbolehkan dalam prosedur . Upaya untuk melakukan transaksi dalam suatu fungsi akan menghasilkan kesalahan, karena beroperasi dalam transaksi di luar fungsi.

Menjalankan game

^{Memulai permainan}

Kami tidak menyarankan menjalankan permainan seperti itu di lingkungan nyata. Untungnya, database dapat diterapkan dengan cepat dan mudah dengan game. Di repositori, Anda dapat menemukan Dockerfile yang akan membangun image dengan PostgreSQL 12.4 dan konfigurasi yang diperlukan. Bangun dan jalankan gambar:

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

Menghubungkan ke database pada gambar:

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

Perhatikan bahwa PostgreSQL dalam wadah menggunakan kebijakan otentikasi kepercayaan, yaitu memungkinkan semua koneksi tanpa kata sandi. Jangan lupa untuk mencabut wadah setelah menyelesaikan semua game!

Kesimpulan

Penggunaan alat khusus untuk keperluan lain seringkali menimbulkan tanggapan negatif dari para profesional. Namun, menyelesaikan tugas yang tidak berarti tetapi menarik melatih pemikiran lateral dan memungkinkan Anda menjelajahi alat dari berbagai sudut pandang untuk mencari solusi yang sesuai.



Hari ini kami sekali lagi mengonfirmasi bahwa Anda dapat menulis apa pun yang Anda inginkan dalam SQL jika Anda mau. Namun demikian, kami merekomendasikan penggunaan alat dalam produksi untuk tujuan yang dimaksudkan, dan melakukan kesenangan seperti proyek rumah kecil secara eksklusif.