ProHoster > Blog > administratë > Antimodelët PostgreSQL: Kalimi i grupeve dhe zgjedhjeve në SQL

Antimodelët PostgreSQL: Kalimi i grupeve dhe zgjedhjeve në SQL

Herë pas here, zhvilluesi ka nevojë kalojë një grup parametrash apo edhe një përzgjedhje të tërë në kërkesë "në hyrje". Ndonjëherë ka zgjidhje shumë të çuditshme për këtë problem.
Antimodelët PostgreSQL: Kalimi i grupeve dhe zgjedhjeve në SQL
Le të shkojmë "nga e kundërta" dhe të shohim se si të mos e bëjmë atë, pse dhe si mund ta bëni më mirë.

"Futja" e drejtpërdrejtë e vlerave në trupin e kërkesës

Zakonisht duket diçka si kjo:

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

... ose si kjo:

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

Për këtë metodë thuhet, shkruhet dhe edhe të vizatuara mjaft:

Antimodelët PostgreSQL: Kalimi i grupeve dhe zgjedhjeve në SQL

Pothuajse gjithmonë është rruga e drejtpërdrejtë për injektimin SQL dhe një ngarkesë shtesë në logjikën e biznesit, e cila detyrohet të "ngjisë" vargun tuaj të pyetjeve.

Kjo qasje mund të justifikohet pjesërisht vetëm nëse është e nevojshme. përdorni ndarjen në PostgreSQL versionet 10 dhe më poshtë për një plan më efikas. Në këto versione, lista e seksioneve të skanuara përcaktohet pa marrë parasysh parametrat e transmetuar, vetëm në bazë të trupit të kërkesës.

$n argumente

Përdorim mbajtësit e vendeve parametrat janë të mira, ju lejon të përdorni DEKLARATA TË PËRGATITUR, duke reduktuar ngarkesën si në logjikën e biznesit (vargu i pyetjes formohet dhe transmetohet vetëm një herë) ashtu edhe në serverin e bazës së të dhënave (riparimi dhe planifikimi nuk kërkohet për çdo shembull të kërkesës).

Numri i ndryshueshëm i argumenteve

Problemet do të na presin kur duam të kalojmë paraprakisht një numër të panjohur argumentesh:

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

Nëse e lini kërkesën në këtë formë, atëherë megjithëse do të na shpëtojë nga injeksionet e mundshme, ajo prapë do të çojë në nevojën për të ngjitur / analizuar kërkesën për çdo opsion nga numri i argumenteve. Tashmë është më mirë sesa ta bësh çdo herë, por mund të bësh edhe pa të.

Mjafton të kalohet vetëm një parametër që përmban paraqitje e serializuar e një vargu:

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

Dallimi i vetëm është nevoja për të kthyer në mënyrë eksplicite argumentin në llojin e dëshiruar të grupit. Por kjo nuk shkakton probleme, pasi ne e dimë paraprakisht se ku po drejtohemi.

Transferimi i mostrës (matricë)

Zakonisht këto janë të gjitha llojet e opsioneve për transferimin e grupeve të të dhënave për futje në bazën e të dhënave "në një kërkesë":

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

Përveç problemeve të përshkruara më sipër me “ringjitjen” e kërkesës, kjo mund të na çojë edhe në jashtë kujtesës dhe dështimi i serverit. Arsyeja është e thjeshtë - PG rezervon memorie shtesë për argumentet, dhe numri i regjistrimeve në grup është i kufizuar vetëm nga aplikacioni i logjikës së biznesit Wishlist. Në raste veçanërisht klinike ishte e nevojshme të shihej argumente "të numëruara" më të mëdha se 9000 dollarë - mos e bëni në këtë mënyrë.

Le të rishkruajmë pyetjen, duke aplikuar tashmë serializimi "me dy nivele".:

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;

Po, në rastin e vlerave "komplekse" brenda një grupi, ato duhet të përshtaten me thonjëza.
Është e qartë se në këtë mënyrë ju mund të "zgjeroni" përzgjedhjen me një numër arbitrar fushash.

çrregullt, çrregullt,…

Herë pas here ka mundësi për të kaluar në vend të një "vargu vargjesh" disa "vargje kolonash" që përmenda në artikullin e fundit:

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

Me këtë metodë, nëse bëni një gabim kur krijoni lista vlerash për kolona të ndryshme, është shumë e lehtë të merrni plotësisht rezultate të papritura, të cilat gjithashtu varen nga versioni i serverit:

-- $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

Duke filluar nga versioni 9.3, PostgreSQL ka funksione të plota për të punuar me llojin json. Prandaj, nëse parametrat tuaj të hyrjes janë të përcaktuara në shfletues, mund të formoni pikërisht atje json objekt për pyetjen SQL:

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

Për versionet e mëparshme, e njëjta metodë mund të përdoret secili (hstore), por "palosja" e saktë me ikjen e objekteve komplekse në hstore mund të shkaktojë probleme.

json_populate_recordset

Nëse e dini paraprakisht se të dhënat nga grupi "input" json do të shkojnë për të plotësuar një tabelë, mund të kurseni shumë në fushat "dereferencing" dhe transmetimin në llojet e dëshiruara duke përdorur funksionin 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

Dhe ky funksion thjesht do të "zgjerojë" grupin e kaluar të objekteve në një përzgjedhje, pa u mbështetur në formatin e tabelës:

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

TABELA E PËRKOHSHME

Por nëse sasia e të dhënave në mostrën e transmetuar është shumë e madhe, atëherë hedhja e saj në një parametër të serializuar është e vështirë, dhe ndonjëherë e pamundur, pasi kërkon një kohë alokim i madh i memories. Për shembull, ju duhet të grumbulloni një grup të madh të dhënash ngjarjesh nga një sistem i jashtëm për një kohë të gjatë dhe të gjatë dhe më pas dëshironi t'i përpunoni ato një herë në anën e bazës së të dhënave.

Në këtë rast, zgjidhja më e mirë do të ishte përdorimi tavolina të përkohshme:

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

Metoda është e mirë për transmetim të rrallë të vëllimeve të mëdha të dhëna.
Nga pikëpamja e përshkrimit të strukturës së të dhënave të saj, një tabelë e përkohshme ndryshon nga një tabelë "e rregullt" vetëm në një veçori. në tabelën e sistemit pg_classdhe në pg_type, pg_varet, pg_atribute, pg_attrdef, ... - dhe asgjë fare.

Prandaj, në sistemet ueb me një numër të madh lidhjesh jetëshkurtër për secilën prej tyre, një tabelë e tillë do të gjenerojë çdo herë regjistrime të reja të sistemit, të cilat fshihen kur lidhja me bazën e të dhënave mbyllet. Përfundimisht, përdorimi i pakontrolluar i TEMP TABLE çon në "ënjtje" të tabelave në pg_catalog dhe duke ngadalësuar shumë operacione që i përdorin ato.
Sigurisht, kjo mund të luftohet kalim periodik VACUUM FULL sipas tabelave të katalogut të sistemit.

Variablat e sesionit

Supozoni se përpunimi i të dhënave nga rasti i mëparshëm është mjaft kompleks për një pyetje të vetme SQL, por ju dëshironi ta bëni atë mjaft shpesh. Kjo do të thotë, ne duam të përdorim përpunimin procedural në DO bllok, por përdorimi i transferimit të të dhënave përmes tabelave të përkohshme do të jetë shumë i shtrenjtë.

Ne gjithashtu nuk mund të përdorim $n-parametra për të kaluar në një bllok anonim. Variablat e sesionit dhe funksioni do të na ndihmojnë të dalim nga situata. përcaktimi_aktual.

Përpara versionit 9.2, duhej të konfiguroheshe paraprakisht hapësira e veçantë e emrave klasa_ndryshuese_e porositur për variablat e sesionit "të tyre". Në versionet aktuale, mund të shkruani diçka si kjo:

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

Ekzistojnë zgjidhje të tjera të disponueshme në gjuhë të tjera procedurale të mbështetura.

Di më shumë mënyra? Ndani në komente!

Burimi: www.habr.com

Shto një koment