🥇PgGraph - una utilità per l'archiviazione è a ricerca di e dipendenze di e tabelle in PostgreSQL

Oghje vogliu presentà i lettori di Habr cù una utilità scritta in Python per travaglià cù dipendenze di tavule in u DBMS PostgreSQL.

L'API di l'utilità hè simplice è si compone di trè metudi:

archive_table - archiviazione recursiva / eliminazione di file cù Chjave Primarie specificate
get_table_references - cercate dependenzii per una tavula (mostrarà e tabelle riferite da quellu specificatu è quelli chì si riferite)
get_rows_references - cercate fila in altre tabelle chì riferite à e file specificate in a tabella desiderata

Pristoria

Mi chjamu Oleg Borzov, sò un sviluppatore in a squadra CRM per i gestori di prestiti ipotecari in Domklik.

A basa di dati principale di u nostru sistema CRM hè unu di i più grandi in quantu à u voluminu in a cumpagnia. Hè ancu unu di i più antichi: apparsu à u principiu di u prugettu, quandu l'arburi eranu grandi, Domklik era una startup, è invece di un microserviziu nantu à un quadru asincronu Python di moda ci era un monolitu enormu in PHP.

A transizione da PHP à Python hè stata assai longa è hà bisognu di supportu simultaneu di i dui sistemi, chì hà influinzatu u disignu di a basa di dati.

In u risultatu, avemu una basa di dati cù un gran numaru di tavule assai cunnessi è enormi cù una mansa di indici per diversi tipi di dumande. Tuttu chistu affetta negativamente u funziunamentu di a basa di dati: per via di e grande tavule è una mansa di relazioni trà elli, a cumplessità di e dumande hè in constantemente crescente, chì hè soprattuttu criticu per e tavule più caricate.

Per riduce a carica nantu à a basa di dati, avemu decisu di scrive un script chì trasfirìanu vechji registri da e tavule più voluminose è caricate à quelli archiviati (per esempiu, da task в task_archive).

Stu compitu hè cumplicatu da u gran numaru di rilazioni trà e tavule: simpricimenti spustà fila da task в task_archive ùn hè micca abbastanza, prima chì avete bisognu di fà u listessu recursively cù tutti quelli chì riferimentu task tavule.

Dimustraraghju cù un esempiu basa di dati demo da u situ postgrespro.ru:

Dicemu chì avemu bisognu di sguassà i registri da una tavula Flights. Postgres ùn ci permette micca di fà cusì cusì: prima avemu bisognu di sguassà i registri da tutte e tavule di riferimentu, è cusì ricursivamente finu à e tavule chì ùn sò micca riferite da nimu.

In u nostru esempiu at Flights si riferisce Ticket_flights, è nantu à ella - Boarding_passes.

Dunque, avete bisognu di sguassà in questu ordine:

Ricevemu i valori di e chjave primarie (PK) di e file Ticket_flights, chì si riferiscenu à e fila da esse eliminate in Flights.
Avemu PK fila Boarding_passes, chì si riferiscenu à Ticket_flights.
Eliminate e fila da PK da u passu 2 in a tavula Boarding_passes.
Eliminate e linee da PK da u passu 1 in Ticket_flights.
Eliminà e linee da Flights.

U risultatu era una utilità chjamata PgGraph, chì avemu decisu di fà open source.

Cumu aduprà

L'utilità supporta dui modi di usu:

Chjama da a linea di cummanda (pggraph …).
Utilizà in codice Python (class PgGraphApi).

Stallazione è cunfigurazione

Prima avete bisognu di stallà l'utilità da u repositoriu Pypi:

pip3 install pggraph

Allora crea un schedariu config.ini nantu à a macchina lucale cù a cunfigurazione di a basa di dati è u script d'archiviazione:

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

Corri da a cunsola

login

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

Argumentu pusitivu:

action - azzione necessaria: archive_table, get_table_references o get_rows_references.

Argumenti chjamati:

--config_path - percorso à u schedariu di cunfigurazione;
--table - una tavola cù quale avete bisognu di fà una azione;
--ids - lista di id separati da virgule, per esempiu, 1,2,3 (parametru facultativu);
--log_path - percorsu à u cartulare per i logs (parametru facultativu, per difettu - cartulare di casa);
--log_level - livellu di logging (parametru facultativu, u default hè INFO).

Esempi di cumandamenti

Archiviazione di una tavola

A funzione principale di l'utilità hè l'archiviazione di dati, i.e. trasferimentu di fila da a tavula principale à a tavola d'archiviu (per esempiu, da a tavola libbra в archivi_libri).

L'eliminazione senza archiviazione hè ancu supportata: per questu avete bisognu di stabilisce u paràmetru in config.ini to_archive = false).

paràmetri richiesti - config_path, table è ids.

Dopu à u lanciamentu, i registri seranu sguassati recursivamente ids in a tavula table è in tutte e tavule chì si riferenu.

$ 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

Truvate dipendenze per una tabella specifica

Funzione per truvà dipendenze di una tabella specifica table. paràmetri richiesti - config_path и table.

Dopu à u lanciu, un dizziunariu serà visualizatu nantu à u screnu, induve:

in_refs - un dizziunariu di tavule chì riferenzianu un datu, induve a chjave hè u nome di a tavula, u valore hè una lista di l'uggetti di a Chjave Straniera (pk_main - chjave primaria in a tavola principale, pk_ref - chjave primaria in a tabella di riferimentu, fk_ref - u nome di a colonna chì hè a chjave straniera à a tavola fonte);
out_refs - un dizziunariu di tavule chì questu si riferisce.

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

Truvà riferimenti à strings cù a Chjave Primaria specificata

Funzione per ricercà fila in altre tavule chì si riferiscenu à fila via Chjave Estera ids tavuli table. paràmetri richiesti - config_path, table и ids.

Dopu à u lanciu, un dizziunariu cù a struttura seguente serà visualizatu nantu à u screnu:

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

Esempiu di chjama:

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

Utilizà in codice

In più di eseguisce in a cunsola, a biblioteca pò esse usata in codice Python. Esempii di chjama in l'ambiente interattivu iPython sò mostrati quì sottu.

Archiviazione di una tavola

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

Truvate dipendenze per una tabella specifica

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

Truvà riferimenti à strings cù a Chjave Primaria specificata

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

U codice fonte di a biblioteca hè dispunibule à GitHub sottu licenza MIT, è ancu in u repository PyPI.

Seraghju felice di cumenti, impegni è suggerimenti.

Pruvaraghju di risponde à e dumande à u megliu di a mo capacità quì è in u repository.

Source: www.habr.com

PgGraph hè una utilità per l'archivimentu è truvà e dipendenze di e tavule in PostgreSQL

Pristoria

Cumu aduprà

Stallazione è cunfigurazione

Corri da a cunsola

login

Esempi di cumandamenti

Archiviazione di una tavola

Truvate dipendenze per una tabella specifica

Truvà riferimenti à strings cù a Chjave Primaria specificata

Utilizà in codice

Archiviazione di una tavola

Truvate dipendenze per una tabella specifica

Truvà riferimenti à strings cù a Chjave Primaria specificata

Add a comment annuler risponde