ProHoster > Dienoraštis > Administravimas > „PgGraph“ yra įrankis, skirtas archyvuoti ir rasti lentelių priklausomybes „PostgreSQL“.

„PgGraph“ yra įrankis, skirtas archyvuoti ir rasti lentelių priklausomybes „PostgreSQL“.

„PgGraph“ yra įrankis, skirtas archyvuoti ir rasti lentelių priklausomybes „PostgreSQL“.
Šiandien noriu Habr skaitytojams pristatyti Python parašytą įrankį, skirtą darbui su lentelių priklausomybėmis PostgreSQL DBVS.

Priemonės API yra paprasta ir susideda iš trijų būdų:

  • archyvas_lentelė - rekursyvus eilučių su nurodytais pirminiais raktais archyvavimas / trynimas
  • get_table_references — ieškoti lentelės priklausomybių (bus rodomos nurodytos lentelės ir jos nurodančios lentelės)
  • gauti_eilučių_nuorodas - ieškoti eilučių kitose lentelėse, kurios nurodo nurodytas norimos lentelės eilutes

priešistorė

Mano vardas Olegas Borzovas, esu Domkliko būsto paskolų vadybininkų CRM komandos kūrėjas.

Pagrindinė mūsų CRM sistemos duomenų bazė yra viena didžiausių pagal apimtis įmonėje. Jis taip pat yra vienas iš seniausių: jis pasirodė pačioje projekto pradžioje, kai medžiai buvo dideli, Domklik buvo startuolis, o vietoj mikropaslaugos madingoje Python asinchroninėje sistemoje buvo didžiulis PHP monolitas.

Perėjimas nuo PHP prie Python buvo labai ilgas ir reikėjo tuo pačiu metu palaikyti abi sistemas, o tai turėjo įtakos duomenų bazės dizainui.

Dėl to turime duomenų bazę su daugybe labai sujungtų ir didžiulių lentelių su daugybe indeksų, skirtų įvairių tipų užklausoms. Visa tai neigiamai veikia duomenų bazės našumą: dėl didelių lentelių ir daugybės ryšių tarp jų užklausų sudėtingumas nuolat didėja, o tai ypač svarbu labiausiai apkrautoms lentelėms.

Norėdami sumažinti duomenų bazės apkrovą, nusprendėme parašyti scenarijų, kuris perkeltų senus įrašus iš talpiausių ir įkeltų lentelių į archyvuotas (pvz. task в task_archive).

Šią užduotį apsunkina daugybė ryšių tarp lentelių: tiesiog perkelkite eilutes iš task в task_archive neužtenka, prieš tai tą patį reikia padaryti rekursyviai su visomis nuorodomis task lenteles.

Parodysiu pavyzdžiu demonstracinė duomenų bazė iš svetainės postgrespro.ru:

„PgGraph“ yra įrankis, skirtas archyvuoti ir rasti lentelių priklausomybes „PostgreSQL“.
Tarkime, kad turime ištrinti įrašus iš lentelės Flights. „Postgres“ neleis mums to daryti tiesiog taip: pirmiausia turime ištrinti įrašus iš visų nuorodų lentelių ir taip toliau rekursyviai iki lentelių, kurių niekas nenurodo.

Mūsų pavyzdyje adresu Flights nurodo Ticket_flights, ir ant jos - Boarding_passes.

Todėl jį reikia ištrinti tokia tvarka:

  1. Gauname eilučių pirminių raktų (PK) reikšmes Ticket_flights, kurie nurodo eilutes, kurias reikia ištrinti Flights.
  2. Gauname PK eilutes Boarding_passes, kurie nurodo Ticket_flights.
  3. Ištriname eilutes pagal PK iš 2 lentelės veiksmo Boarding_passes.
  4. Ištrinkite eilutes pagal PK nuo 1 veiksmo Ticket_flights.
  5. Pašalinamos linijos iš Flights.

Rezultatas buvo programa, vadinama PgGraph, kurią nusprendėme padaryti atviro kodo.

Kaip naudotis

Priemonė palaiko du naudojimo būdus:

  • Skambinkite iš komandinės eilutės (pggraph …).
  • Naudojimas Python kode (klasė PgGraphApi).

Diegimas ir konfigūravimas

Pirmiausia turite įdiegti įrankį iš Pypi saugyklos:

pip3 install pggraph

Tada vietiniame kompiuteryje sukurkite failą config.ini su duomenų bazės konfigūracija ir archyvavimo scenarijumi:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Необязательный параметр, указано значение по умолчанию

[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчанию
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Paleisti iš konsolės

Parametrai

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        required action: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    show this help message and exit
  --table TABLE                 table name
  --ids IDS                     primary key ids, separated by comma, e.g. 1,2,3
  --config_path CONFIG_PATH     path to config.ini
  --log_path LOG_PATH           path to log dir
  --log_level LOG_LEVEL         log level (debug, info, error)

Poziciniai argumentai:

  • action - būtini veiksmai: archive_table, get_table_references arba get_rows_references.

Vardiniai argumentai:

  • --config_path - kelias į konfigūracijos failą;
  • --table — lentelė, su kuria reikia atlikti veiksmą;
  • --ids — ID sąrašas, atskirtas kableliais, pavyzdžiui, 1,2,3 (neprivalomas parametras);
  • --log_path — kelias į žurnalų aplanką (pasirenkamas parametras, pagal numatytuosius nustatymus – namų aplankas);
  • --log_level — registravimo lygis (pasirenkamas parametras, numatytasis parametras yra INFO).

Komandų pavyzdžiai

Lentelės archyvavimas

Pagrindinė naudingumo funkcija yra duomenų archyvavimas, t.y. eilučių perkėlimas iš pagrindinės lentelės į archyvo lentelę (pavyzdžiui, iš lentelės knygos в knygų_archyvas).

Taip pat palaikomas ištrynimas be archyvavimo: tam reikia nustatyti parametrą config.ini į_archyvuoti = false).

Reikalingi parametrai - config_path, lentelė ir ID.

Po paleidimo įrašai bus rekursyviai ištrinti ids į lentelę table ir visose su juo susijusiose lentelėse.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END

Raskite nurodytos lentelės priklausomybes

Funkcija rasti nurodytos lentelės priklausomybes table. Reikalingi parametrai - config_path и table.

Po paleidimo ekrane bus rodomas žodynas, kuriame:

  • in_refs — lentelių, nurodančių duotą, žodynas, kur raktas yra lentelės pavadinimas, reikšmė yra svetimo rakto objektų sąrašas (pk_main - pagrindinis raktas pagrindinėje lentelėje, pk_ref - pirminis raktas nuorodų lentelėje, fk_ref — stulpelio, kuris yra šaltinio lentelės išorinis raktas, pavadinimas);
  • out_refs - lentelių žodynas, į kurį remiasi šis.

$ 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')]}}

Rasti nuorodas į eilutes su nurodytu pirminiu raktu

Funkcija ieškoti eilučių kitose lentelėse, kurios nurodo eilutes naudojant užsienio raktą ids stalai table. Reikalingi parametrai - config_path, table и ids.

Po paleidimo ekrane bus rodomas tokios struktūros žodynas:

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

Skambučio pavyzdys:

$ 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'}]}}}

Naudojimas kode

Biblioteka gali būti naudojama ne tik konsolėje, bet ir Python kode. Toliau pateikiami skambučių interaktyvioje iPython aplinkoje pavyzdžiai.

Lentelės archyvavimas

>>> 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 - START
2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVE REFERRING TABLES
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 - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
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 rows 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: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rows 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: 	DELETE FROM ticket_flights by flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: 	END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows 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: DELETE FROM flights by flight_id - 2 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END

Raskite nurodytos lentelės priklausomybes

>>> 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')]}}

Rasti nuorodas į eilutes su nurodytu pirminiu raktu

>>> 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'}]}}}

Bibliotekos šaltinio kodą rasite adresu GitHub pagal MIT licenciją, taip pat saugykloje PyPI.

Maloniai lauksiu pastabų, įsipareigojimų ir pasiūlymų.

Čia ir saugykloje pasistengsiu atsakyti į klausimus pagal savo galimybes.

Šaltinis: www.habr.com

Добавить комментарий