ProHoster > Блог > Administrado > PgGraph estas ilo por arkivi kaj trovi tabelajn dependecojn en PostgreSQL

PgGraph estas ilo por arkivi kaj trovi tabelajn dependecojn en PostgreSQL

PgGraph estas ilo por arkivi kaj trovi tabelajn dependecojn en PostgreSQL
Hodiaŭ mi volas prezenti Habr-legantojn kun ilo skribita en Python por labori kun tabeldependecoj en la PostgreSQL DBMS.

La API de la utileco estas simpla kaj konsistas el tri metodoj:

  • arkivo_tabelo - rekursiva arkivado/forigado de vicoj kun specifitaj Ĉefŝlosiloj
  • get_table_references — serĉu dependecojn por tabelo (montros tabelojn referencitaj de la specifita kaj tiuj referencantaj ĝin)
  • get_rows_references - serĉu vicojn en aliaj tabeloj kiuj referencas specifitajn vicojn en la dezirata tabelo

antaŭhistorio

Mi nomiĝas Oleg Borzov, mi estas programisto en la CRM-teamo por administrantoj de hipotekaj pruntoj en Domklik.

La ĉefa datumbazo de nia CRM-sistemo estas unu el la plej grandaj laŭ volumeno en la kompanio. Ĝi ankaŭ estas unu el la plej malnovaj: ĝi aperis ĉe la lanĉo mem de la projekto, kiam la arboj estis grandaj, Domklik estis starto, kaj anstataŭ mikroservo sur moda Python nesinkrona kadro estis grandega monolito en PHP.

La transiro de PHP al Python estis tre longa kaj postulis samtempan subtenon de ambaŭ sistemoj, kio influis la dezajnon de la datumbazo.

Kiel rezulto, ni havas datumbazon kun granda nombro da tre konektitaj kaj grandegaj tabeloj kun amaso da indeksoj por malsamaj specoj de demandoj. Ĉio ĉi negative influas la agadon de la datumbazo: pro grandaj tabeloj kaj amaso da rilatoj inter ili, la komplekseco de demandoj konstante pliiĝas, kio estas precipe kritika por la plej ŝarĝitaj tabeloj.

Por redukti la ŝarĝon sur la datumbazo, ni decidis skribi skripton, kiu translokigus malnovajn rekordojn de la plej grandaj kaj ŝarĝitaj tabeloj al arkivitaj (ekzemple, de task в task_archive).

Ĉi tiu tasko estas malfaciligita pro la granda nombro da rilatoj inter tabeloj: simple movi vicojn de task в task_archive ne sufiĉas, antaŭ tio vi devas fari la samon rekursie kun ĉiuj tiuj referencantaj task tabloj.

Mi pruvos per ekzemplo demo-datumbazo de la retejo postgrespro.ru:

PgGraph estas ilo por arkivi kaj trovi tabelajn dependecojn en PostgreSQL
Ni diru, ke ni devas forigi rekordojn de tabelo Flights. Postgres ne permesos al ni fari ĉi tion ĝuste tiel: ni unue devas forigi rekordojn de ĉiuj referencaj tabeloj, kaj tiel plu rekursie ĝis tabeloj kiuj ne estas referencitaj de neniu.

En nia ekzemplo ĉe Flights rilatas Ticket_flights, kaj sur ŝi - Boarding_passes.

Tial vi devas forigi ĝin en ĉi tiu ordo:

  1. Ni ricevas la valorojn de primaraj ŝlosiloj (PK) de vicoj Ticket_flights, kiuj rilatas al la vicoj forigotaj en Flights.
  2. Ni ricevas PK-vicojn Boarding_passes, kiuj rilatas al Ticket_flights.
  3. Ni forigas vicojn per PK de paŝo 2 en la tabelo Boarding_passes.
  4. Forigu liniojn per PK de paŝo 1 in Ticket_flights.
  5. Forigante liniojn de Flights.

La rezulto estis ilo nomita PgGraph, kiun ni decidis fari malferman fonton.

Kiel uzi

La ilo subtenas du reĝimojn de uzo:

  • Voku de la komandlinio (pggraph …).
  • Uzado en Python-kodo (klaso PgGraphApi).

Instalado kaj agordo

Unue vi devas instali la ilon el la deponejo de Pypi:

pip3 install pggraph

Poste kreu config.ini-dosieron sur la loka maŝino kun la agordo de la datumbazo kaj la arkiva skripto:

[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'

Kuru de konzolo

parametroj

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

Poziciaj argumentoj:

  • action - postulata ago: archive_table, get_table_references aŭ get_rows_references.

Nomitaj argumentoj:

  • --config_path — vojo al la agorda dosiero;
  • --table — tablo, per kiu vi bezonas fari agon;
  • --ids — listo de identigiloj apartigitaj per komoj, ekzemple, 1,2,3 (nedeviga parametro);
  • --log_path — vojo al la dosierujo por protokoloj (laŭvola parametro, defaŭlte — hejma dosierujo);
  • --log_level — ensaluta nivelo (laŭvola parametro, defaŭlta estas INFO).

Ekzemploj de komando

Arkivante tabelon

La ĉefa funkcio de la utileco estas datumarkivado, t.e. transdonante vicojn de la ĉefa tabelo al la arkiva tabelo (ekzemple, de la tabelo libroj в libroj_arkivo).

Forigo sen arkivado ankaŭ estas subtenata: por tio vi devas agordi la parametron en config.ini to_archive = malvera).

Bezonataj parametroj - konfig_path, tabelo kaj identigiloj.

Post lanĉo, rekordoj estos rekursie forigitaj ids en la tablo table kaj en ĉiuj tabeloj kiuj rilatas al ĝi.

$ 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

Trovu dependecojn por specifa tabelo

Funkcio por trovi dependecojn de specifita tabelo table. Bezonataj parametroj - config_path и table.

Post lanĉo, vortaro estos montrata sur la ekrano, kie:

  • in_refs — vortaro de tabeloj referencantaj antaŭfiksitan unu, kie la ŝlosilo estas la nomo de la tabelo, la valoro estas listo de Fremdŝlosilobjektoj (pk_main - ĉefa ŝlosilo en la ĉefa tabelo, pk_ref - ĉefa ŝlosilo en la referenctabelo, fk_ref — la nomo de la kolumno kiu estas la fremda ŝlosilo al la fonta tabelo);
  • out_refs — vortaro de tabeloj al kiu ĉi tiu rilatas.

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

Trovi referencojn al ŝnuroj kun la specifita Ĉefŝlosilo

Funkcio por serĉi vicojn en aliaj tabeloj kiuj rilatas al vicoj per Fremda Ŝlosilo ids tabloj table. Bezonataj parametroj - config_path, table и ids.

Post lanĉo, vortaro kun la sekva strukturo aperos sur la ekrano:

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

Ekzemplo voko:

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

Uzado en kodo

Krom ruli ĝin en la konzolo, la biblioteko povas esti uzata en Python-kodo. Ekzemploj de vokoj en la iPython interaga medio estas montritaj malsupre.

Arkivante tabelon

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

Trovu dependecojn por specifa tabelo

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

Trovi referencojn al ŝnuroj kun la specifita Ĉefŝlosilo

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

La biblioteka fontkodo haveblas ĉe GitHub sub MIT-licenco, same kiel en la deponejo PyPI.

Mi ĝojos pri komentoj, kompromisoj kaj sugestoj.

Mi provos respondi demandojn laŭ mia kapablo ĉi tie kaj en la deponejo.

fonto: www.habr.com

Aldoni komenton