🥇PostgreSQL اينٽي پيٽرن: SQL ۾ حالت جو جائزو

SQL نه آهي C++، ۽ نه ئي اهو JavaScript آهي. تنهن ڪري، منطقي اظهار جي تشخيص مختلف آهي، ۽ اهو سڀ ڪجهه ساڳيو ناهي:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

جڏهن ته هڪ PostgreSQL سوال جي عملدرآمد پلان کي بهتر ڪرڻ پاڻمرادو "ٻيهر ترتيب ڏيڻ" جي برابر حالتون ڪري سگهن ٿيونانفرادي رڪارڊ لاءِ انهن مان ڪنهن به حساب نه ڪريو، لاڳو ٿيل انڊيڪس جي حالت ڏانهن رجوع ڪريو... مختصر ۾، آسان طريقو اهو آهي ته توهان انتظام نٿو ڪري سگهي اهو حڪم جنهن ۾ اهي هوندا (۽ ڇا انهن جو حساب ڪتاب ڪيو ويندو) برابر حالتون.

تنهن ڪري، جيڪڏهن توهان اڃا تائين ترجيح کي منظم ڪرڻ چاهيو ٿا، توهان کي ساخت جي ضرورت آهي انهن شرطن کي غير برابر بڻائي مشروط سان ڳوڙها и آپريٽرز.

ڊيٽا ۽ انهن سان گڏ ڪم ڪرڻ جو بنياد آهي اسان جي VLSI ڪمپليڪس جيتنهن ڪري، اهو اسان لاء تمام ضروري آهي ته انهن تي عمل نه رڳو صحيح طور تي، پر موثر پڻ. اچو ته ڪنڪريٽ مثالن تي نظر رکون جتي اظهار جي تشخيص ۾ غلطيون ٿي سگهن ٿيون، ۽ جتي اهو انهن جي ڪارڪردگي کي بهتر ڪرڻ جي قابل آهي.

#0: RTFM

شروع ٿيندڙ دستاويز مان مثال:

جڏهن تشخيص جو حڪم اهم آهي، ان کي تعمير سان مقرر ڪري سگهجي ٿو CASE. مثال طور، هي طريقو هڪ جملي ۾ صفر جي تقسيم کان بچڻ لاء WHERE ناقابل اعتبار:
SELECT ... WHERE x > 0 AND y/x > 1.5;
محفوظ اختيار:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
تعمير جو استعمال CASE اظهار کي بهتر ڪرڻ کان بچائي ٿو، تنهنڪري اهو صرف استعمال ڪيو وڃي جڏهن ضروري هجي.

#1: حرڪت واري حالت

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

هر شيءِ سٺي لڳي ٿي، پر... ڪو به واعدو نٿو ڪري ته سيڙپڪاري ڪئي وئي SELECT عمل نه ڪيو ويندو جيڪڏهن پهرين شرط غلط آهي. ان سان درست ڪريو nested IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

ھاڻي اچو ته غور سان ڏسو - ٽريگر فنڪشن جو سڄو جسم "ليپ ٿيل" ٿي ويو IF. ۽ هن جو مطلب آهي ته ڪجھ به نه روڪيو اسان کي هن حالت کي هٽائڻ کان طريقيڪار استعمال ڪندي WHEN- حالتون:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

اهو طريقو توهان کي اجازت ڏئي ٿو سرور وسيلن کي محفوظ ڪرڻ جي ضمانت سان جيڪڏهن شرط غلط آهي.

#2: يا/۽ زنجير

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

ٻي صورت ۾، اهو حاصل ڪري سگهجي ٿو ته ٻنهي EXISTS سچ هوندو، پر ٻنهي تي عمل ڪيو ويندو.

پر جيڪڏهن اسان پڪ ڄاڻون ٿا ته انهن مان هڪ "سچو" آهي گهڻو ڪري (يا "غلط" - لاء AND-زنجيرن) - ڇا اهو ممڪن آهي ته ڪنهن به طرح "ان جي ترجيح کي وڌايو" ته جيئن ٻيو هڪ ڀيرو ٻيهر عمل نه ڪيو وڃي؟

اهو ظاهر ٿئي ٿو ته اهو ممڪن آهي - الورورٿمي طريقي سان آرٽيڪل جي موضوع جي ويجهو آهي PostgreSQL Antipatterns: ناياب رڪارڊ شامل ٿيڻ جي وچ ۾ پهچي ويندو.

اچو ته صرف انهن ٻنهي شرطن کي "ڪيس هيٺ ڇڪيو":

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

هن معاملي ۾، اسان کي بيان نه ڪيو آهي ELSE-value، اھو آھي، جيڪڏھن ٻئي حالتون غلط آھن CASE واپس ڪندو NULL، جنهن جي تشريح ڪئي وئي آهي FALSE в WHERE- حالتون.

هي مثال ٻئي طريقي سان گڏ ڪري سگهجي ٿو - ذائقو ۽ رنگ ڪرڻ لاء:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: شرطن کي ڪيئن [نه] لکجي

اسان ٻه ڏينهن هن ٽريگر جي ”عجيب“ ٿيڻ جي سببن جو تجزيو ڪرڻ تي گذاريا - اچو ته ڏسون ڇو.

ذريعو:

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

مسئلو #1: عدم مساوات NULL لاءِ حساب نه رکي ٿي

اچو ته اهو سڀ ڪجهه فرض ڪريون OLD- فيلڊ اهم آهن NULL. ڇا ٿيندو؟

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

۽ حالتون ڪم ڪرڻ جي نقطي نظر کان NULL برابر FALSE، جيئن مٿي ذڪر ڪيو ويو آهي.

فيصلو: استعمال ڪندڙ آپريٽر IS DISTINCT FROM от ROW-آپريٽر، هڪ ئي وقت سڄي رڪارڊ جو مقابلو ڪندي:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

مسئلو نمبر 2: ساڳئي ڪارڪردگي جي مختلف عمل درآمد

ڀيٽيو

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

ڇو اتي اضافي سيڙپڪاري آهن SELECT؟ هڪ فنڪشن to_regclass؟ اهو مختلف ڇو آهي ...

اچو ته درست ڪريون:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

مسئلو # 3: بول جي ترجيح

اچو ته فارميٽ جو ذريعو:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

اڙي... حقيقت ۾ معلوم ٿيو ته پهرين ٻن شرطن مان ڪنهن جي سچائيءَ جي صورت ۾ پوري حالت بدلجي ويندي آهي. TRUE، عدم مساوات کي نظرانداز ڪرڻ. ۽ اهو سڀ ڪجهه نه آهي جيڪو اسان چاهيون ٿا.

اچو ته درست ڪريون:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

مسئلو #4 (ننڍو): پيچيده يا حالت ھڪڙي فيلڊ لاءِ

دراصل، اسان کي نمبر 3 ۾ خاص طور تي مسئلا هئا ڇو ته اتي ٽي حالتون هيون. پر انھن جي بدران، توھان حاصل ڪري سگھو ٿا ھڪڙي سان، ميکانيزم کي استعمال ڪندي coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

ائين ئي آهيون NULL "پڪڙ"، ۽ پيچيده OR توهان کي قوسن سان ڀڄڻ جي ضرورت ناهي.

ڪل

اچو ته درست ڪريون جيڪو اسان حاصل ڪيو:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

۽ ڏنو ويو آهي ته هي ٽرگر فنڪشن صرف استعمال ڪري سگهجي ٿو UPDATEحرڪت جي موجودگي جي ڪري OLD/NEW مٿين سطح جي حالت ۾، پوء اها حالت عام طور تي ٻاهر ڪڍي سگهجي ٿي WHEN- حالت جيئن # 1 ۾ ڏيکاريل آهي ...

جو ذريعو: www.habr.com

PostgreSQL Antipatterns: حالت جو جائزو SQL ۾