پوسٹگری ایس کیو ایل اینٹی پیٹرنز: سیٹ پاس کرنا اور ایس کیو ایل کو منتخب کرتا ہے۔

وقتاً فوقتاً ایک ڈویلپر کی ضرورت ہوتی ہے۔ درخواست پر پیرامیٹرز کا ایک سیٹ یا یہاں تک کہ ایک مکمل انتخاب پاس کریں۔ "دروازے پر". کبھی کبھی آپ کو اس مسئلے کا بہت ہی عجیب حل ملتا ہے۔

آئیے پیچھے کی طرف چلتے ہیں اور دیکھتے ہیں کہ کیا نہیں کرنا چاہیے، کیوں، اور ہم اسے بہتر طریقے سے کیسے کر سکتے ہیں۔

درخواست کے جسم میں اقدار کا براہ راست اندراج

یہ عام طور پر کچھ اس طرح لگتا ہے:

query = "SELECT * FROM tbl WHERE id = " + value

...یا اس طرح:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

یہ طریقہ کہا گیا ہے، لکھا گیا ہے اور یہاں تک کہ کھینچا گیا۔ کافی:

تقریباً ہمیشہ یہی ہوتا ہے۔ ایس کیو ایل انجیکشن کا براہ راست راستہ اور کاروباری منطق پر غیر ضروری بوجھ، جو آپ کی استفسار لائن کو "گلو" کرنے پر مجبور ہے۔

اگر ضروری ہو تو اس نقطہ نظر کو جزوی طور پر جائز قرار دیا جاسکتا ہے۔ تقسیم کا استعمال کرتے ہوئے PostgreSQL ورژن 10 اور اس سے نیچے میں زیادہ موثر منصوبہ حاصل کرنے کے لیے۔ ان ورژنز میں، سکین شدہ حصوں کی فہرست کا تعین منتقل شدہ پیرامیٹرز کو مدنظر رکھے بغیر کیا جاتا ہے، صرف درخواست کی باڈی کی بنیاد پر۔

$n-دلائل

استعمال کریں جگہ دار پیرامیٹرز اچھے ہیں، یہ آپ کو استعمال کرنے کی اجازت دیتا ہے۔ تیار کردہ بیانات، کاروباری منطق (استفسار کی تار صرف ایک بار تیار اور منتقل کی جاتی ہے) اور ڈیٹا بیس سرور پر (ہر استفسار کی مثال کے لئے دوبارہ تجزیہ اور شیڈولنگ کی ضرورت نہیں ہے) دونوں پر بوجھ کو کم کرنا۔

دلائل کی متغیر تعداد

جب ہم نامعلوم تعداد میں دلائل دینا چاہیں گے تو مسائل ہمارے منتظر ہوں گے:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

اگر ہم درخواست کو اس فارم میں چھوڑ دیتے ہیں، اگرچہ یہ ہمیں ممکنہ انجیکشن سے بچائے گا، لیکن اس کے باوجود درخواست کو ضم کرنے/پارس کرنے کی ضرورت پیش آئے گی۔ ہر آپشن کے لیے دلائل کی تعداد پر منحصر ہے۔. یہ ہر بار کرنے سے بہتر ہے، لیکن آپ اس کے بغیر بھی کر سکتے ہیں۔

اس پر مشتمل صرف ایک پیرامیٹر پاس کرنا کافی ہے۔ سیریلائزڈ صف کی نمائندگی:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

فرق صرف یہ ہے کہ دلیل کو واضح طور پر مطلوبہ صف کی قسم میں تبدیل کرنے کی ضرورت ہے۔ لیکن اس سے مسائل پیدا نہیں ہوتے، کیونکہ ہم پہلے سے ہی جانتے ہیں کہ ہم کہاں جا رہے ہیں۔

نمونے کی منتقلی (میٹرکس)

عام طور پر "ایک درخواست میں" ڈیٹا بیس میں داخل کرنے کے لیے ڈیٹا سیٹ کی منتقلی کے لیے یہ تمام قسم کے اختیارات ہیں:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

درخواست کو "دوبارہ چپکنے" کے ساتھ اوپر بیان کردہ مسائل کے علاوہ، یہ ہمیں بھی لے جا سکتا ہے۔ یادداشت سے باہر اور سرور کریش۔ وجہ سادہ ہے - PG دلائل کے لیے اضافی میموری محفوظ رکھتا ہے، اور سیٹ میں ریکارڈز کی تعداد صرف کاروباری منطق کے اطلاق کی ضروریات کے مطابق محدود ہے۔ خاص طور پر طبی معاملات میں مجھے دیکھنا پڑا "نمبر" کے دلائل $9000 سے زیادہ ہیں۔ - اس طرح مت کرو.

آئیے پہلے سے ہی درخواست کو دوبارہ لکھتے ہیں۔ "دو سطحی" سیریلائزیشن:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

ہاں، ایک صف کے اندر "پیچیدہ" قدروں کی صورت میں، ان کو اقتباسات سے گھرا ہونا چاہیے۔
یہ واضح ہے کہ اس طرح آپ قطعات کی من مانی تعداد کے ساتھ انتخاب کو "بڑھا" سکتے ہیں۔

بے ہودہ، بے گھر،…

وقتاً فوقتاً پاسنگ کے آپشنز موجود ہوتے ہیں ایک "ارے کی صف" کے بجائے کئی "کالموں کی صفیں" جن کا میں نے ذکر کیا آخری مضمون میں:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

اس طریقہ کے ساتھ، اگر آپ مختلف کالموں کے لیے اقدار کی فہرست تیار کرتے وقت غلطی کرتے ہیں، تو اسے حاصل کرنا بہت آسان ہے۔ غیر متوقع نتائج، جو سرور ورژن پر بھی منحصر ہے:

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

ورژن 9.3 کے بعد سے، PostgreSQL میں json قسم کے ساتھ کام کرنے کے لیے مکمل فنکشنز ہیں۔ لہذا، اگر ان پٹ پیرامیٹرز کی تعریف آپ کے براؤزر میں ہوتی ہے، تو آپ اسے وہیں بنا سکتے ہیں۔ ایس کیو ایل استفسار کے لئے json آبجیکٹ:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

پچھلے ورژن کے لیے بھی یہی طریقہ استعمال کیا جا سکتا ہے۔ ہر ایک (ہسٹور)، لیکن hstore میں پیچیدہ اشیاء کے فرار کے ساتھ درست "convolution" مسائل پیدا کر سکتا ہے۔

json_populate_recordset

اگر آپ پہلے سے جانتے ہیں کہ "ان پٹ" json ارے سے ڈیٹا کچھ ٹیبل کو بھرنے کے لیے استعمال کیا جائے گا، تو آپ "dereferencing" والے فیلڈز میں بہت کچھ بچا سکتے ہیں اور json_populate_recordset فنکشن کا استعمال کرکے انہیں مطلوبہ اقسام میں ڈال سکتے ہیں:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

اور یہ فنکشن ٹیبل فارمیٹ پر بھروسہ کیے بغیر اشیاء کی پاس کردہ سرنی کو آسانی سے "توسیع" کرے گا:

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

عارضی ٹیبل

لیکن اگر منتقل کیے گئے نمونے میں ڈیٹا کی مقدار بہت زیادہ ہے، تو اسے ایک سیریلائزڈ پیرامیٹر میں ڈالنا مشکل اور بعض اوقات ناممکن ہوتا ہے، کیونکہ اس کے لیے ایک بار کی ضرورت ہوتی ہے۔ میموری کی ایک بڑی مقدار مختص کریں۔. مثال کے طور پر، آپ کو ایک بیرونی سسٹم سے لمبے عرصے تک واقعات پر ڈیٹا کا ایک بڑا پیکج جمع کرنے کی ضرورت ہے، اور پھر آپ ڈیٹا بیس کی طرف سے ایک بار اس پر کارروائی کرنا چاہتے ہیں۔

اس صورت میں، بہترین حل استعمال کرنا ہوگا عارضی میزیں:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

طریقہ اچھا ہے۔ بڑی مقداروں کی کبھی کبھار منتقلی کے لیے ڈیٹا
اس کے اعداد و شمار کی ساخت کو بیان کرنے کے نقطہ نظر سے، ایک عارضی جدول صرف ایک طریقے سے "باقاعدہ" سے مختلف ہوتا ہے۔ pg_class سسٹم ٹیبل میںاور اندر pg_type, pg_depend, pg_attribute, pg_attrdef, … - کچھ بھی نہیں.

لہذا، ان میں سے ہر ایک کے لیے بڑی تعداد میں قلیل المدتی کنکشن والے ویب سسٹمز میں، ایسا ٹیبل ہر بار سسٹم کے نئے ریکارڈ تیار کرے گا، جو ڈیٹا بیس سے کنکشن بند ہونے پر حذف ہو جاتے ہیں۔ بالآخر، TEMP TABLE کا بے قابو استعمال pg_catalog میں میزوں کی "سوجن" کا باعث بنتا ہے اور ان کو استعمال کرنے والے بہت سے آپریشنز کو سست کر رہے ہیں۔
یقینا، اس کے استعمال سے نمٹا جا سکتا ہے۔ متواتر گزرنا ویکیوم فل سسٹم کیٹلاگ ٹیبل کے مطابق۔

سیشن متغیرات

آئیے فرض کریں کہ پچھلے کیس سے ڈیٹا پر کارروائی کرنا ایک SQL استفسار کے لیے کافی پیچیدہ ہے، لیکن آپ اسے اکثر کرنا چاہتے ہیں۔ یعنی ہم طریقہ کار پراسیسنگ کو استعمال کرنا چاہتے ہیں۔ بلاک کرولیکن عارضی ٹیبلز کے ذریعے ڈیٹا کی منتقلی کا استعمال بہت مہنگا ہو گا۔

ہم کسی گمنام بلاک کو منتقل کرنے کے لیے $n-پیرامیٹر بھی استعمال نہیں کر سکیں گے۔ سیشن متغیرات اور فنکشن اس صورتحال سے نکلنے میں ہماری مدد کریں گے۔ موجودہ_ترتیب.

ورژن 9.2 سے پہلے پہلے سے ترتیب دینا ضروری تھا۔ خصوصی نام کی جگہ custom_variable_classes "آپ کے" سیشن متغیرات کے لیے۔ موجودہ ورژن پر آپ کچھ اس طرح لکھ سکتے ہیں:

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;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

دیگر حل دیگر معاون طریقہ کار کی زبانوں میں مل سکتے ہیں۔

کیا آپ کو کوئی اور طریقہ معلوم ہے؟ تبصرے میں اشتراک کریں!

ماخذ: www.habr.com