ProHoster > Blogi > Haldamine > PgGraph — tööriist PostgreSQL tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks

PgGraph — tööriist PostgreSQL tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks

PgGraph — tööriist PostgreSQL tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks
TÀna tahan tutvustada Habra lugejatele Pythonis kirjutatud tööriista tabelite sÔltuvuste haldamiseks PostgreSQL andmebaasis.

Tööriista API on lihtne ja koosneb kolmest meetodist:

  • archive_table — rekursiivne arhiveerimine/kustutamine antud Primary Key-dega ridade jaoks
  • get_table_references — tabeli sĂ”ltuvuste otsing (nĂ€itab tabeleid, millele antud viitab ja mis viitavad sellele)
  • get_rows_references — ridade otsing teistes tabelites, mis viitavad antud tabeli mÀÀratud ridadele

Eelalugu

Minu nimi on Oleg Borzov, olen CRM meeskonna arendaja, kes töötab kinnisvara laenude halduritele Domklikis.

Meie CRM-sĂŒsteemi peamine andmebaas on ettevĂ”tte suurimaid oma mahu poolest. See on ka ĂŒks vanimaid: see loodi projekti kĂ€ivitamise ajal, kui puud olid suured, Domklik oli start-up ja moodsa Python'i asĂŒnkroonse raamistikuna polnud veel mikroteenuseid, vaid suur monoliit PHP-s.

Üleminek PHP-lt Pythonile oli vĂ€ga pikk ja nĂ”udis kahe sĂŒsteemi samal ajal toe pakkumist, mis kajastus andmebaasi projekteerimises.

KokkuvÔttes on meil andmebaas, mis sisaldab suurt hulka tugevalt seotud ja suurte tabelitega, kus on palju indekseid erinevate pÀringute jaoks. See kÔik avaldab negatiivset mÔju andmebaasi jÔudlusele: suurte tabelite ja nende vaheliste paljude seoste tÔttu suureneb pÀringute keerukus, mis on eriti kriitiline kÔige koormatumate tabelite puhul.

Andmebaasi koormuse vĂ€hendamiseks otsustasime kirjutada skripti, mis iga pĂ€ev cron'i abil liiguks vanad kirjed kĂ”ige mahukamatest ja koormatud tabelitest arhiivi (nĂ€iteks tabelist task ĂŒhes task_archive).

See ĂŒlesanne muutub keeruliseks paljude tabelite vaheliste seoste tĂ”ttu: lihtsalt ridade ĂŒleviimine tabelist task ĂŒhes task_archive ei ole piisav, enne tuleb sama rekursiivselt teha kĂ”igi ridade viitavatega task tabelitega.

Demonstrin nÀite abil demonstratiivse andmebaasi lehelt postgrespro.ru:

PgGraph — tööriist PostgreSQL tabelite sĂ”ltuvuste arhiveerimiseks ja otsimiseks
Oletame, et peame eemaldama kirjed tabelist Flights. Lihtsalt nii seda Postgres meile ei luba: eelnevalt on vaja eemaldada kirjed kÔikidest viitavatest tabelitest, ja seda rekursiivselt, kuni tabeliteni, millele keegi ei viita.

Meie nÀites viitab Flights Ticket_flights , ja sellel onBoarding_passes SeetÔttu tuleb eemaldada jÀrgmisest jÀrjekorras:.

SeetÔttu tuleb kustutamine teha jÀrgmises jÀrjekorras:

  1. Saame primaarsete vÔtmete (Primary Keys, PK) vÀÀrtused ridadest , ja sellel on, mis viitavad kustutatavatele ridadele Flights.
  2. Saame ridade PK-d SeetÔttu tuleb eemaldada jÀrgmisest jÀrjekorras:, mis viitavad , ja sellel on.
  3. Kustutame PK-dega read p.2 tabelist SeetÔttu tuleb eemaldada jÀrgmisest jÀrjekorras:.
  4. Kustutame PK-dega read p.1-st , ja sellel on.
  5. Kustutame read Flights.

Tulemuseks sai utiliit nimega PgGraph, mille muutsime avatud lÀhtekoodiga.

Kuidas kasutada

Utiliit toetab kahte kasutusreĆŸiimi:

  • KĂ€ivitus kĂ€surealt (pggraph 
).
  • Kasutamine Python koodis (klass PgGraphApi).

Installeerimine ja seadistamine

Esmalt tuleb installida utiliit Pypi-repositooriumist:

pip3 install pggraph

SeejÀrel tuleb luua lokaalsel masinal fail config.ini, mis sisaldab andmebaasi ja arhiveerimise skripti konfiguratsiooni:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Valikuline parameeter, vaikimisi vÀÀrtus

[archive]  ; Seda jaotust ei pea tÀitma, allpool on toodud vaikimisi vÀÀrtused
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

KĂ€ivitus konsolist

Parameetrid

$ pggraph -h
kasutus: pggraph action [-h] --table TABEL [--ids ID-d] [--config_path CONFIG_PATH]
positiivsed argumendid:
  action        nÔutav tegevus: archive_table, get_table_references, get_rows_references

valikulised argumendid:
  -h, --help                    nÀita seda abiteadet ja vÀlju
  --table TABEL                 tabeli nimi
  --ids ID-d                    peavad vÔtmed, eraldatud komaga, nt 1,2,3
  --config_path CONFIG_PATH     tee config.ini faili
  --log_path LOG_PATH           tee logi kausta
  --log_level LOG_LEVEL         logi tase (debug, info, error)

Positiivsed argumendid:

  • action — nĂ”utav tegevus: archive_table, get_table_references vĂ”i get_rows_references.

Nimetatud argumendid:

  • --config_path — tee konfigureerimise faili;
  • --table — tabel, millega toimingut teostada;
  • --ids — ID-de nimekiri, eraldatud komaga, nĂ€iteks, 1,2,3 (valikuline parameeter);
  • --log_path — tee logide kausta (valikuline parameeter, vaikimisi — kodukaust);
  • --log_level — logimise tase (valikuline parameeter, vaikimisi — INFO).

KÀskude nÀidised

Tabeli arhiveerimine

Tööriista peamine funktsioon — andmete arhiveerimine, st ridade edasiviimine peamiseks tabelist arhiivi (nĂ€iteks tabelist books ĂŒhes books_archive).

Samuti toetatakse kustutamist ilma arhiveerimata: selleks tuleb config.ini-s seada parameeter to_archive = false).

NĂ”utavad parameetrid — config_path, table ja ids.

PÀrast kÀivitamist eemaldatakse rekursiivselt kirjed ids tabelis tabel ja kÔikidesse viidatud tabelitesse.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: lennud - ALUSTUS
2020-06-20 19:27:44 INFO: lennud - alustamine archive_recursive 3 rida (sĂŒgavus=0)
2020-06-20 19:27:44 INFO:       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:       piletid_lennud - alustamine archive_recursive 3 rida (sĂŒgavus=1)
2020-06-20 19:27:44 INFO:               ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sĂŒgavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id jÀrgi
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sĂŒgavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id jÀrgi
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sĂŒgavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id jÀrgi
2020-06-20 19:27:44 INFO:               boarding_passes - alustamine archive_recursive 3 rida (sĂŒgavus=2)
2020-06-20 19:27:44 INFO:                       ALUSTUS ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:                       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rida pilet_number, lennu_id jÀrgi
2020-06-20 19:27:44 INFO:               LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO:       piletid_lennud - archive_by_ids 3 rida pilet_number, lennu_id jÀrgi
2020-06-20 19:27:44 INFO:       LÕPP ARHIIVIMISE TABELITE VIITAMISELE
2020-06-20 19:27:44 INFO: lennud - archive_by_ids 3 rida id jÀrgi
2020-06-20 19:27:44 INFO: lennud - LÕPP

MÀÀratud tabeli sÔltuvuste otsimine

SĂ”ltuvuste otsimise funktsioon mÀÀratud tabeli jaoks tabel. Kohustuslikud parameetrid — config_path ja tabel.

PÀrast kÀivitamist kuvatakse ekraanile sÔnastik, kus:

  • in_refs — tabel, mis viitab sellele tabelile, kus vĂ”ti on tabeli nimi ja vÀÀrtus on Foreign Key objektide loend (pk_main — peamine vĂ”ti pĂ”hitaabelis, pk_ref — peamine vĂ”ti viitavas tabelis, fk_ref — veeru nimi, mis on vĂ€lisvĂ”ti algsesse tabelisse);
  • out_refs — tabelite sĂ”nastik, millele viidatakse antud tabelis.

$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Otsib viiteid ridadele, millel on mÀÀratud Primary Key

Funktsioon ridade leidmiseks teistes tabelites, mis viitavad ridadele, kasutades Foreign Key-d ids tabel tabel. Kohustuslikud parameetrid — config_path, tabel ja ids.

PÀrast kÀivitamist kuvatakse ekraanile sÔnastik jÀrgmise struktuuriga:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Kutsumise nÀide:

$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Kasutamine koodis

Lisaks konsoolis kÀivitamisele saab raamatukogu kasutada ka Pythonis koodis. Allpool on nÀidatud kutsumise nÀited interaktiivses iPython keskkonnas.

Tabeli arhiveerimine

>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flights - ALUSTA
2020-06-20 23:12:08 INFO: flights - alustamine archive_recursive 2 rida (sĂŒgavus=0)
2020-06-20 23:12:08 INFO: 	ALUSTA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 DEBUG: 	ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: 	SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 30 rida (sĂŒgavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rida by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rida by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rida
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 30 rida (sĂŒgavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rida by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rida by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rida
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 30 rida (sĂŒgavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rida by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rida by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 30 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rida
2020-06-20 23:12:08 INFO: 	ticket_flights - alustamine archive_recursive 3 rida (sĂŒgavus=1)
2020-06-20 23:12:08 INFO: 		ALUSTA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 3 rida by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		KUSTUTADA FROM boarding_passes by FK flight_id, ticket_no - 3 rida
2020-06-20 23:12:08 INFO: 		LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rida by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	KUSTUTADA FROM ticket_flights by flight_id, ticket_no - 3 rida
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rida
2020-06-20 23:12:08 INFO: 	LÕPETA ARCHIVE VIITATAVAD TABELID
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rida by flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: KUSTUTADA FROM flights by flight_id - 2 rida
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rida
2020-06-20 23:12:09 INFO: flights - LÕPP

MÀÀratud tabeli sÔltuvuste otsimine

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Otsib viiteid ridadele, millel on mÀÀratud Primary Key

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Raamatukogu lÀhtekood on saadaval GitHub MIT litsentsi alusel, samuti hoidlas PyPI.

Ootan kommentaare, panuseid ja ettepanekuid.

Vastan kĂŒsimustele vĂ”imaluste piires siin ja hoidlas.

Allikas: habr.com

Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid | ProHoster