🥇PgGraph - 'n program vir die argivering en soek van tabelafhanklikhede in PostgreSQL

Vandag wil ek aan Habr-lesers 'n program aanbied wat in Python geskryf is om met tabelafhanklikhede in die PostgreSQL DBMS te werk.

Die nut se API is eenvoudig en bestaan uit drie metodes:

argief_tabel - rekursiewe argivering / verwydering van rye met gespesifiseerde primêre sleutels
kry_tabel_verwysings - soek na afhanklikhede vir 'n tabel (sal tabelle wys waarna deur die gespesifiseerde een verwys word en diegene wat daarna verwys)
kry_rye_verwysings - soek na rye in ander tabelle wat verwys na gespesifiseerde rye in die verlangde tabel

voorgeskiedenis

My naam is Oleg Borzov, ek is 'n ontwikkelaar in die CRM-span vir verbandleningsbestuurders in Domklik.

Die hoofdatabasis van ons CRM-stelsel is een van die grootste in terme van volume in die maatskappy. Dit is ook een van die oudstes: dit het by die bekendstelling van die projek verskyn, toe die bome groot was, Domklik 'n beginonderneming was, en in plaas van 'n mikrodiens op 'n modieuse Python-asinchroniese raamwerk was daar 'n groot monoliet in PHP.

Die oorgang van PHP na Python was baie lank en het gelyktydige ondersteuning van beide stelsels vereis, wat die ontwerp van die databasis beïnvloed het.

As gevolg hiervan het ons 'n databasis met 'n groot aantal hoogs gekoppelde en groot tabelle met 'n klomp indekse vir verskillende tipes navrae. Dit alles beïnvloed die werkverrigting van die databasis negatief: as gevolg van groot tabelle en 'n klomp verhoudings tussen hulle, neem die kompleksiteit van navrae voortdurend toe, wat veral krities is vir die mees gelaaide tabelle.

Om die las op die databasis te verminder, het ons besluit om 'n skrif te skryf wat ou rekords van die mees lywige en gelaaide tabelle na geargiveerdes sal oordra (byvoorbeeld van task в task_archive).

Hierdie taak word bemoeilik deur die groot aantal verhoudings tussen tabelle: skuif eenvoudig rye van task в task_archive is nie genoeg nie, voor dit moet jy dieselfde rekursief doen met al die verwysings task tafels.

Ek sal met 'n voorbeeld demonstreer demo databasis van die webwerf postgrespro.ru:

Kom ons sê ons moet rekords uit 'n tabel verwyder Flights. Postgres sal ons nie toelaat om dit net so te doen nie: ons moet eers rekords van alle verwysingstabelle uitvee, ensovoorts rekursief tot by tabelle wat deur niemand verwys word nie.

In ons voorbeeld by Flights verwys Ticket_flights, en op haar - Boarding_passes.

Daarom moet u dit in hierdie volgorde uitvee:

Ons kry die primêre sleutels (PK) waardes van rye in Ticket_flights, wat verwys na die rye waarin uitgevee moet word Flights.
Ons kry PK-rye Boarding_passes, wat verwys na Ticket_flights.
Ons verwyder rye volgens PK vanaf stap 2 in die tabel Boarding_passes.
Vee reëls deur PK uit stap 1 in Ticket_flights.
Die verwydering van lyne uit Flights.

Die resultaat was 'n hulpprogram genaamd PgGraph, wat ons besluit het om oopbron te maak.

Hoe om te gebruik

Die hulpprogram ondersteun twee maniere van gebruik:

Bel vanaf die opdragreël (pggraph …).
Gebruik in Python-kode (klas PgGraphApi).

Installasie en konfigurasie

Eerstens moet u die hulpprogram vanaf die Pypi-bewaarplek installeer:

pip3 install pggraph

Skep dan 'n config.ini-lêer op die plaaslike masjien met die konfigurasie van die databasis en die argiveringskrip:

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

Hardloop vanaf konsole

Parameters

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

Posisionele argumente:

action - vereiste aksie: archive_table, get_table_references of get_rows_references.

Benoemde argumente:

--config_path - pad na die konfigurasielêer;
--table — 'n tabel waarmee jy 'n aksie moet uitvoer;
--ids - lys van ID geskei deur kommas, byvoorbeeld, 1,2,3 (opsionele parameter);
--log_path - pad na die gids vir logs (opsionele parameter, by verstek - tuisgids);
--log_level - logvlak (opsionele parameter, verstek is INFO).

Bevelvoorbeelde

Argiveer 'n tabel

Die hooffunksie van die hulpprogram is data-argivering, d.w.s. die oordrag van rye van die hooftabel na die argieftabel (byvoorbeeld vanaf die tabel boeke в boeke_argief).

Uitvee sonder argivering word ook ondersteun: hiervoor moet u die parameter in config.ini stel na_argief = vals).

Vereiste parameters - config_path, tabel en ID's.

Na bekendstelling sal rekords rekursief uitgevee word ids in die tabel table en in alle tabelle wat daarna verwys.

$ 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

Soek afhanklikhede vir 'n gespesifiseerde tabel

Funksie om afhanklikhede van 'n gespesifiseerde tabel te vind table. Vereiste parameters - config_path и table.

Na bekendstelling sal 'n woordeboek op die skerm vertoon word, waar:

in_refs — 'n woordeboek van tabelle wat na 'n gegewe een verwys, waar die sleutel die naam van die tabel is, die waarde 'n lys van vreemde sleutel-objekte is (pk_main - primêre sleutel in die hooftabel, pk_ref - primêre sleutel in die verwysingstabel, fk_ref — die naam van die kolom wat die vreemde sleutel tot die brontabel is);
out_refs — 'n woordeboek van tabelle waarna hierdie een verwys.

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

Soek verwysings na snare met die gespesifiseerde Primêre Sleutel

Funksie om na rye in ander tabelle te soek wat na rye verwys via Foreign Key ids tafels table. Vereiste parameters - config_path, table и ids.

Na bekendstelling sal 'n woordeboek met die volgende struktuur op die skerm vertoon word:

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

Voorbeeld oproep:

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

Gebruik in kode

Benewens om dit in die konsole te laat loop, kan die biblioteek in Python-kode gebruik word. Voorbeelde van oproepe in die iPython-interaktiewe omgewing word hieronder getoon.

Argiveer 'n tabel

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

Soek afhanklikhede vir 'n gespesifiseerde tabel

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

Soek verwysings na snare met die gespesifiseerde Primêre Sleutel

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

Die biblioteekbronkode is beskikbaar by GitHub onder MIT-lisensie, sowel as in die bewaarplek PyPI.

Ek sal bly wees vir kommentaar, commits en voorstelle.

Ek sal probeer om vrae na die beste van my vermoë hier en in die bewaarplek te beantwoord.

Bron: will.com

PgGraph is 'n hulpmiddel vir die argivering en vind van tabelafhanklikhede in PostgreSQL

voorgeskiedenis

Hoe om te gebruik

Installasie en konfigurasie

Hardloop vanaf konsole

Parameters

Bevelvoorbeelde

Argiveer 'n tabel

Soek afhanklikhede vir 'n gespesifiseerde tabel

Soek verwysings na snare met die gespesifiseerde Primêre Sleutel

Gebruik in kode

Argiveer 'n tabel

Soek afhanklikhede vir 'n gespesifiseerde tabel

Soek verwysings na snare met die gespesifiseerde Primêre Sleutel

Voeg 'n opmerking Kanselleer antwoord