Dari waktu ke waktu, pengembang perlu
memberikan serangkaian parameter ke permintaan atau bahkan seluruh pilihan "input". Solusi yang sangat aneh untuk masalah ini terkadang muncul.
Mari kita “dari kebalikan” dan melihat bagaimana itu tidak layak dilakukan, mengapa, dan bagaimana melakukan yang lebih baik.
Penyisipan langsung nilai ke dalam tubuh permintaan
Biasanya terlihat seperti ini:
query = "SELECT * FROM tbl WHERE id = " + value
... atau lebih:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)
Tentang metode ini dikatakan, ditulis, dan
bahkan digambar dengan berlimpah:
Hampir selalu, ini adalah
jalur langsung ke injeksi SQL dan beban tambahan pada logika bisnis, yang dipaksa untuk "merekatkan" string kueri Anda.
Pendekatan semacam itu hanya dapat dibenarkan sebagian jika perlu
menggunakan sectioning dalam versi PostgreSQL 10 dan lebih rendah untuk mendapatkan rencana yang lebih efisien. Dalam versi ini, daftar bagian yang dipindai ditentukan bahkan tanpa memperhitungkan parameter yang dikirimkan, hanya berdasarkan badan permintaan.
argumen $ n
Menggunakan
penampung parameter adalah baik, ini memungkinkan Anda untuk menggunakan
PERNYATAAN YANG DIPERSIAPKAN , mengurangi beban pada kedua logika bisnis (string kueri dihasilkan dan dikirim hanya sekali) dan server database (penguraian ulang dan penjadwalan untuk setiap instance dari permintaan tidak diperlukan).
Jumlah argumen yang bervariasi
Masalah akan menunggu kami saat kami ingin menyampaikan terlebih dahulu sejumlah argumen yang tidak diketahui:
... id IN ($1, $2, $3, ...)
Jika Anda meninggalkan permintaan dalam formulir ini, maka meskipun itu akan menyelamatkan kami dari kemungkinan suntikan, namun tetap akan mengarah pada kebutuhan untuk mengelem / mem-parsing permintaan
untuk setiap opsi dari sejumlah argumen . Sudah lebih baik daripada melakukannya setiap saat, tetapi Anda bisa melakukannya tanpa itu.
Cukup hanya melewatkan satu parameter yang berisi
representasi serial array :
... id = ANY($1::integer[])
Satu-satunya perbedaan adalah kebutuhan untuk secara eksplisit mengkonversi argumen ke tipe array yang diinginkan. Tapi ini tidak menimbulkan masalah, karena kita sudah tahu sebelumnya di mana kita menangani.
Transfer sampel (matriks)
Biasanya ini adalah segala macam opsi untuk mentransfer set data untuk dimasukkan ke dalam database "dalam satu permintaan":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...
Selain masalah yang dijelaskan di atas dengan "menempel kembali" permintaan, ini juga dapat menyebabkan
kehabisan memori dan server crash. Alasannya sederhana - PG menyimpan memori tambahan untuk argumen, dan jumlah catatan di set hanya dibatasi oleh logika bisnis Wishlist yang diterapkan. Dalam kasus-kasus klinis khususnya, orang harus melihat
argumen "bernomor" lebih dari $ 9.000 - tidak perlu.
Kami menulis ulang permintaan, menerapkan
serialisasi "dua tingkat" :
INSERT INTO tbl SELECT unnest[1]::text k , unnest[2]::integer v FROM ( SELECT unnest($1::text[])::text[]
Ya, dalam kasus nilai "kompleks" di dalam array, mereka harus dikelilingi oleh tanda kutip.
Jelas bahwa dengan cara ini Anda dapat "memperluas" seleksi dengan jumlah bidang yang berubah-ubah.
tidak, tidak, ...
Secara berkala, ada opsi transmisi alih-alih “array array” dari beberapa “array kolom”, yang saya sebutkan
di artikel sebelumnya :
SELECT unnest($1::text[]) k , unnest($2::integer[]) v;
Dengan metode ini, membuat kesalahan saat membuat daftar nilai untuk kolom yang berbeda, sangat mudah untuk mendapatkan
hasil yang benar-benar
tidak terduga , yang juga tergantung pada versi server:
Json
Dimulai dengan versi 9.3, PostgreSQL memperkenalkan fungsi lengkap untuk bekerja dengan tipe json. Oleh karena itu, jika definisi parameter input di browser Anda berlangsung, Anda dapat membuat
objek json untuk permintaan SQL di sana:
SELECT key k , value v FROM json_each($1::json);
Untuk versi sebelumnya, metode yang sama dapat digunakan untuk
masing -
masing (hstore) , tetapi "konvolusi" yang benar dengan melarikan diri objek kompleks di hstore dapat menyebabkan masalah.
json_populate_recordset
Jika Anda tahu sebelumnya bahwa data dari "input" json array akan masuk untuk mengisi beberapa jenis tabel, Anda dapat menyimpan banyak dalam "dereferencing" bidang dan casting ke tipe yang diperlukan menggunakan fungsi json_populate_recordset:
SELECT * FROM json_populate_recordset( NULL::pg_class , $1::json
json_to_recordset
Dan fungsi ini hanya "memperluas" array yang ditransfer objek ke dalam seleksi, tanpa bergantung pada format tabel:
SELECT * FROM json_to_recordset($1::json) T(k text, v integer);
MEJA SEMENTARA
Tetapi jika jumlah data dalam sampel yang ditransmisikan sangat besar, maka melemparkannya ke dalam satu parameter berseri adalah sulit, dan kadang-kadang tidak mungkin, karena memerlukan
alokasi satu kali
dari sejumlah besar memori . Misalnya, Anda perlu mengumpulkan paket besar data tentang peristiwa dari sistem eksternal untuk waktu yang sangat lama, dan kemudian Anda ingin memprosesnya sekali di sisi basis data.
Dalam hal ini, solusi terbaik adalah dengan menggunakan
tabel sementara :
CREATE TEMPORARY TABLE tbl(k text, v integer); ... INSERT INTO tbl(k, v) VALUES($1, $2);
Metode ini baik
untuk pengiriman data
dalam jumlah besar yang langka .
Dari sudut pandang menggambarkan struktur datanya, tabel sementara berbeda dari yang "normal" hanya dengan satu fitur
dalam tabel sistem pg_class , dan dalam
pg_type, pg_depend, pg_attribute, pg_attrdef, ... - tidak ada sama sekali.
Oleh karena itu, dalam sistem web dengan sejumlah besar koneksi berumur pendek untuk masing-masingnya, tabel seperti itu akan menghasilkan catatan sistem baru setiap kali, yang dihapus dengan koneksi ke database ditutup. Akibatnya,
penggunaan TEMP TABLE yang tidak terkontrol menyebabkan "pembengkakan" tabel di pg_catalog dan memperlambat banyak operasi yang menggunakannya.
Tentu saja, ini dapat diperjuangkan dengan bantuan
lulus berkala VACUUM FULL melalui tabel katalog sistem.
Variabel sesi
Misalkan pemrosesan data dari kasus sebelumnya cukup rumit untuk satu query SQL, tetapi Anda cukup sering melakukannya. Artinya, kami ingin menggunakan pemrosesan prosedural di
blok DO , tetapi menggunakan transfer data melalui tabel sementara akan terlalu mahal.
Kami tidak akan dapat menggunakan $ n-parameter untuk mentransfer ke blok anonim. Variabel sesi dan fungsi
current_setting akan membantu kita keluar dari situasi ini.
Sebelum versi 9.2, perlu untuk
mengkonfigurasi ruang nama custom_variable_classes untuk variabel sesi "Anda". Pada versi saat ini, Anda dapat menulis sesuatu seperti ini:
SET my.val = '{1,2,3}'; DO $$ DECLARE id integer; BEGIN FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP RAISE NOTICE 'id : %', id; END LOOP; END; $$ LANGUAGE plpgsql;
Bahasa prosedural yang didukung lainnya dapat menemukan solusi lain.
Apakah Anda tahu lebih banyak cara? Bagikan komentar Anda!