ProHoster > Blog > Administrazioa > PostgreSQLren aurkako ereduak: multzoak eta hautaketak SQLra pasatzea

PostgreSQLren aurkako ereduak: multzoak eta hautaketak SQLra pasatzea

Noizean behin, garatzaileak behar du parametro multzo bat edo baita hautaketa oso bat ere pasa eskaerari "sarreran". Batzuetan arazo honi irtenbide oso bitxiak daude.
PostgreSQLren aurkako ereduak: multzoak eta hautaketak SQLra pasatzea
Goazen “kontrakotik” eta ikus dezagun nola ez, zergatik eta nola egin dezakezun hobeto.

Eskaeraren gorputzean balioak "txertatzea" zuzena

Normalean honelako itxura du:

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

... edo honela:

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

Metodo honi buruz esaten da, idatzi eta marraztuta ere nahikoa:

PostgreSQLren aurkako ereduak: multzoak eta hautaketak SQLra pasatzea

Ia beti da SQL injekziorako zuzeneko bidea eta karga gehigarria negozio-logikan, zure kontsulta-katea "itsastera" behartuta dagoena.

Planteamendu hori partzialki justifika daiteke behar izanez gero. partizioa erabili PostgreSQL 10. bertsioetan eta ondorengoetan, plan eraginkorragoa izateko. Bertsio hauetan, eskaneatutako atalen zerrenda transmititutako parametroak kontuan hartu gabe zehazten da, eskaeraren gorputzaren arabera soilik.

$n argumentu

Erabili leku-markak parametroak ona da, erabiltzeko aukera ematen dizu PRESTATUTAKO ADIERAZPENAK, karga murriztuz bai negozio-logikan (kontsulta-katea behin bakarrik eratzen eta transmititzen da) bai datu-basearen zerbitzarian (ez da beharrezkoa kontsulta-instantzia bakoitzerako bir-analisia eta plangintza).

Argudio kopuru aldakorra

Arazoak itxarongo zaizkigu aldez aurretik argumentu kopuru ezezaguna pasa nahi dugunean:

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

Eskaera formulario honetan uzten baduzu, balizko injekzioetatik salbatuko gaituen arren, eskaera itsatsi / analizatu beharra ekarriko du. aukera bakoitzerako argumentu kopurutik. Dagoeneko aldi bakoitzean egitea baino hobea da, baina gabe egin dezakezu.

Nahikoa da parametro bakarra pasatzea array baten irudikapen serializatua:

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

Desberdintasun bakarra argumentua nahi den matrize motara esplizituki bihurtzeko beharra da. Baina horrek ez du arazorik sortzen, aldez aurretik badakigu eta nora zuzentzen garen.

Lagin-transferentzia (matrizea)

Normalean hauek dira datu-multzoak transferitzeko "eskaera bakarrean" datu-basean txertatzeko aukera guztiak:

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

Eskaeraren "berriro itsatsiarekin" deskribatutako arazoez gain, honek ere eraman gaitzake oroimenik gabe eta zerbitzariaren kraskatzea. Arrazoia sinplea da - PG-k memoria gehigarria gordetzen du argumentuetarako, eta multzoko erregistro-kopurua Wishlist negozio-logikako aplikazioak soilik mugatzen du. Batez ere kasu klinikoetan ikusi beharra zegoen $ 9000 baino handiagoak diren "zenbakitutako" argumentuak - Ez egin horrela.

Berridatzi dezagun kontsulta, dagoeneko aplikatuz "bi maila" serializazioa:

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;

Bai, array baten barruan balio "konplexuen" kasuan, komatxoekin markatu behar dira.
Argi dago modu honetan hautapena "zabaldu" dezakezula eremu kopuru arbitrario batekin.

desegin, ezetz,...

Noizean behin aipatu ditudan "array of arrays" baten ordez hainbat "array of columns" pasatzeko aukerak daude azken artikuluan:

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

Metodo honekin, zutabe ezberdinetarako balio-zerrendak sortzean akatsen bat egiten baduzu, oso erraza da guztiz lortzea. ustekabeko emaitzak, zerbitzariaren bertsioaren araberakoak ere:

-- $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 bertsiotik hasita, PostgreSQL-k json motarekin lan egiteko funtzio osoak ditu. Hori dela eta, zure sarrera-parametroak arakatzailean definituta badaude, bertan zuzenean eta formatu dezakezu json objektua SQL kontsultarako:

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

Aurreko bertsioetarako, metodo bera erabil daiteke bakoitza(hdenda), baina hstoreko objektu konplexuetatik ihes eginda "toleste" zuzenak arazoak sor ditzake.

json_populate_recordset

Aurrez badakizu "sarrera" json array-ko datuak taularen bat betetzera joango direla, asko aurrez dezakezu "deserreferentzian" eremuetan eta nahi dituzun motetara igortzen json_populate_recordset funtzioa erabiliz:

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

json_to_recordset

Eta funtzio honek pasatako objektuen array hautapen batean "zabalduko" du, taula formatuan fidatu gabe:

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

BEHIN BATEKO MAHAIA

Baina transmititutako laginaren datu-kopurua oso handia bada, serieko parametro batera botatzea zaila da, eta batzuetan ezinezkoa da, behin-behineko bat behar baita. memoria esleipen handia. Esate baterako, kanpoko sistema batetik gertaeren datu sorta handi bat bildu behar duzu denbora luzez, eta, ondoren, datu-basearen aldean behin-behinean prozesatu nahi duzu.

Kasu honetan, irtenbiderik onena erabiltzea litzateke behin-behineko mahaiak:

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

Metodoa ona da bolumen handien maiz ez transmititzeko datuak.
Bere datuen egitura deskribatzearen ikuspuntutik, aldi baterako taula bat "ohiko" taula batetik ezaugarri bakarrean desberdintzen da. pg_class sistemako taulaneta pg_type, pg_depend, pg_attribute, pg_attrdef, ... —eta ezer ez.

Hori dela eta, horietako bakoitzarentzat iraupen laburreko konexio ugari duten web sistemetan, halako taula batek sistema-erregistro berriak sortuko ditu aldi bakoitzean, datu-baserako konexioa ixtean ezabatzen direnak. Azkenean, TEMP TABLE kontrolatu gabe erabiltzeak pg_catalog-ko taulak "hantura" dakar eta erabiltzen dituzten eragiketa asko moteltzea.
Noski, horri aurre egin daiteke aldizkako pase HUTSEAN BETE sistemaren katalogo taulen arabera.

Saioko aldagaiak

Demagun aurreko kasuko datuen prozesamendua nahiko konplexua dela SQL kontsulta bakar baterako, baina sarritan egin nahi duzula. Hau da, prozedura prozedura erabili nahi dugu DO blokea, baina datu-transferentzia aldi baterako taulen bidez erabiltzea garestiegia izango da.

Ezin dugu $n-parametrorik erabili bloke anonimo batera pasatzeko. Saioaren aldagaiak eta funtzioak egoeratik ateratzen lagunduko digu. uneko_ezarpena.

9.2 bertsioa baino lehen, aurrez konfiguratu behar zenuen izen-espazio berezia aldagai_klase pertsonalizatuak "euren" saio aldagaietarako. Uneko bertsioetan, honelako zerbait idatz dezakezu:

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

Onartutako beste hizkuntza prozesal batzuetan beste irtenbide batzuk daude eskuragarri.

Modu gehiago ezagutzen dituzu? Partekatu iruzkinetan!

Iturria: www.habr.com

Gehitu iruzkin berria