ProHoster > Blog > Идораи > Antipatterns PostgreSQL: интиқоли маҷмӯаҳо ва интихобҳо ба SQL

Antipatterns PostgreSQL: интиқоли маҷмӯаҳо ва интихобҳо ба SQL

Вақт аз вақт ба таҳиякунанда ниёз дорад Маҷмӯи параметрҳо ё ҳатто интихоби пурраро ба дархост гузаронед "дар даромадгоҳ". Баъзан шумо бо роҳҳои хеле аҷиби ин мушкилот дучор мешавед.
Antipatterns PostgreSQL: интиқоли маҷмӯаҳо ва интихобҳо ба SQL
Биёед ба қафо равем ва бубинем, ки чӣ кор кардан лозим нест, чаро ва чӣ тавр мо онро беҳтар карда метавонем.

Воридкунии мустақими арзишҳо ба мақоми дархост

Он одатан чунин ба назар мерасад:

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

... ё монанди ин:

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

Ин усул гуфта шудааст, навишта шудааст ва ҳатто кашида шудааст фаровон:

Antipatterns PostgreSQL: интиқоли маҷмӯаҳо ва интихобҳо ба SQL

Қариб ҳамеша ин аст роҳи мустақим ба тазриқи SQL ва сарбории нолозим ба мантиқи тиҷорат, ки маҷбур аст, ки хати дархости шуморо "часпонед".

Ин муносибатро факат дар сурати зарурат кисман асоснок кардан мумкин аст бо истифода аз тақсимкунӣ дар версияҳои 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 барои дархости SQL:

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

Барои версияҳои қаблӣ, ҳамин усулро метавон истифода бурд ҳар як (мағоза), аммо дурустии "конволютсия" бо фирор аз объектҳои мураккаб дар hstore метавонад боиси мушкилот гардад.

json_populate_recordset

Агар шумо пешакӣ донед, ки маълумот аз массиви "input" json барои пур кардани баъзе ҷадвалҳо истифода мешавад, шумо метавонед бо истифода аз функсияи 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, бояд пешакӣ танзим карда шавад фазои махсус синфҳои_тағйирёбанда барои тағирёбандаҳои сессияи "шумо". Дар версияҳои ҷорӣ шумо метавонед чунин нависед:

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

Роҳҳои ҳалли дигарро дар дигар забонҳои мурофиавии дастгирӣ ёфтан мумкин аст.

Оё шумо роҳҳои дигарро медонед? Дар шарҳҳо мубодила кунед!

Манбаъ: will.com

Илова Эзоҳ