PgGraph hija utilità għall-arkivjar u s-sejba tad-dipendenzi tat-tabella f'PostgreSQL

Illum irrid nippreżenta lill-qarrejja Habr b'utilità miktuba f'Python biex jaħdmu mad-dipendenzi tat-tabella fid-DBMS PostgreSQL.

L-API tal-utilità hija sempliċi u tikkonsisti fi tliet metodi:

• archive_table - arkivjar rikorsiv/tħassir ta 'ringieli b'Ċwievet Primarji speċifikati
• get_table_references — tfittxija għad-dipendenzi għal tabella (se turi tabelli referenzjati minn dik speċifikata u dawk li jirreferu għaliha)
• get_rows_references - fittex ringieli f'tabelli oħra li jirreferu għal ringieli speċifikati fit-tabella mixtieqa

preistorja

Jisimni Oleg Borzov, jien żviluppatur fit-tim tas-CRM għall-maniġers tas-self b'ipoteka f'Domklik.

Id-database prinċipali tas-sistema CRM tagħna hija waħda mill-akbar f'termini ta 'volum fil-kumpanija. Huwa wkoll wieħed mill-eqdem: deher fit-tnedija stess tal-proġett, meta s-siġar kienu kbar, Domklik kienet istartjar, u minflok mikroservizz fuq qafas asinkroniku Python moda kien hemm monolitu enormi fil-PHP.

It-tranżizzjoni minn PHP għal Python kienet twila ħafna u kienet teħtieġ appoġġ simultanju taż-żewġ sistemi, li affettwa d-disinn tad-database.

Bħala riżultat, għandna database b'numru kbir ta 'tabelli konnessi ħafna u enormi b'mazz ta' indiċi għal tipi differenti ta 'mistoqsijiet. Dan kollu jaffettwa b'mod negattiv il-prestazzjoni tad-database: minħabba tabelli kbar u mazz ta 'relazzjonijiet bejniethom, il-kumplessità tal-mistoqsijiet qed tiżdied b'mod kostanti, li hija speċjalment kritika għat-tabelli l-aktar mgħobbija.

Biex tnaqqas it-tagħbija fuq id-database, iddeċidejna li niktbu skript li jittrasferixxi rekords qodma mit-tabelli l-aktar voluminużi u mgħobbija għal dawk arkivjati (pereżempju, minn task в task_archive).

Dan il-kompitu huwa kkumplikat min-numru kbir ta 'relazzjonijiet bejn it-tabelli: sempliċement iċċaqlaq ringieli minn task в task_archive mhuwiex biżżejjed, qabel ma trid tagħmel l-istess b'mod rikorsiv ma 'dawk kollha li jirreferu task tabelli.

Se nuri b'eżempju database demo mis-sit postgrespro.ru:

Ejja ngħidu li għandna bżonn inħassru r-rekords minn tabella Flights. Postgres mhux se jippermettilna nagħmlu dan hekk: l-ewwel għandna bżonn inħassru r-rekords mit-tabelli ta 'referenza kollha, u l-bqija b'mod rikorsiv sa tabelli li ma jirreferuhom ħadd.

Fl-eżempju tagħna fi Flights jirreferi Ticket_flights, u fuqha - Boarding_passes.

Għalhekk, għandek bżonn tħassarha f'din l-ordni:

1. Aħna nġibu l-valuri taċ-ċwievet primarji (PK) tar-ringieli Ticket_flights, li jirreferu għar-ringieli li għandhom jitħassru fihom Flights.
2. Ikollna ringieli PK Boarding_passes, li jirreferu għal Ticket_flights.
3. Aħna nħassru ringieli minn PK mill-pass 2 fit-tabella Boarding_passes.
4. Ħassar linji minn PK mill-pass 1 pulzieri Ticket_flights.
5. It-tneħħija tal-linji minn Flights.

Ir-riżultat kien utilità msejħa PgGraph, li ddeċidejna li nagħmlu sors miftuħ.

Kif tuża

L-utilità tappoġġja żewġ modi ta 'użu:

• Sejħa mil-linja tal-kmand (pggraph …).
• Użu fil-kodiċi Python (klassi PgGraphApi).

Installazzjoni u konfigurazzjoni

L-ewwel trid tinstalla l-utilità mir-repożitorju Pypi:

pip3 install pggraph

Imbagħad oħloq fajl config.ini fuq il-magna lokali bil-konfigurazzjoni tad-database u l-iskritt tal-arkivjar:

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

Mexxi minn console

Parametri

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

Argumenti pożizzjonali:

• action - azzjoni meħtieġa: archive_table, get_table_references jew get_rows_references.

Argumenti msemmija:

• --config_path — mogħdija għall-fajl tal-konfigurazzjoni;
• --table — tabella li biha għandek bżonn twettaq azzjoni;
• --ids — lista ta’ id separati b’virgoli, pereżempju, 1,2,3 (parametru fakultattiv);
• --log_path — mogħdija għall-folder għal zkuk (parametru fakultattiv, awtomatikament — folder tad-dar);
• --log_level — livell ta' illoggjar (parametru mhux obbligatorju, default huwa INFO).

Eżempji ta' kmand

Arkivjar ta' tabella

Il-funzjoni ewlenija tal-utilità hija l-arkivjar tad-dejta, i.e. it-trasferiment ta' ringieli mit-tabella prinċipali għat-tabella tal-arkivju (per eżempju, mit-tabella kotba в arkivju_kotba).

It-tħassir mingħajr arkivjar huwa appoġġjat ukoll: għal dan għandek bżonn tissettja l-parametru f'config.ini to_archive = falza).

Parametri meħtieġa - config_path, tabella u ids.

Wara t-tnedija, ir-rekords se jitħassru b'mod rikorsiv ids fit-tabella table u fit-tabelli kollha li jirreferu għaliha.

$ 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

Sib dipendenzi għal tabella speċifikata

Funzjoni biex issib dipendenzi ta 'tabella speċifikata table. Parametri meħtieġa - config_path и table.

Wara t-tnedija, se jintwera dizzjunarju fuq l-iskrin, fejn:

• in_refs — dizzjunarju ta’ tabelli li jirreferu għal waħda partikolari, fejn iċ-ċavetta hija l-isem tat-tabella, il-valur huwa lista ta’ oġġetti ta’ Ċavetta Barranija (pk_main - ċavetta primarja fit-tabella prinċipali, pk_ref - ċavetta primarja fit-tabella ta' referenza, fk_ref — l-isem tal-kolonna li hija ċ-ċavetta barranija għat-tabella tas-sors);
• out_refs — dizzjunarju ta' tabelli li dan jirreferi għalih.

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

Sib referenzi għal kordi biċ-Ċavetta Primarja speċifikata

Funzjoni biex tfittex ringieli f'tabelli oħra li jirreferu għal ringieli permezz ta' Ċavetta Barranija ids imwejjed table. Parametri meħtieġa - config_path, table и ids.

Wara t-tnedija, se jintwera dizzjunarju bl-istruttura li ġejja fuq l-iskrin:

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

Eżempju ta' sejħa:

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

Użu fil-kodiċi

Minbarra li titħaddem fil-console, il-librerija tista 'tintuża fil-kodiċi Python. Eżempji ta 'sejħiet fl-ambjent interattiv iPython huma murija hawn taħt.

Arkivjar ta' tabella

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

Sib dipendenzi għal tabella speċifikata

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

Sib referenzi għal kordi biċ-Ċavetta Primarja speċifikata

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

Il-kodiċi tas-sors tal-librerija huwa disponibbli fuq GitHub taħt liċenzja MIT, kif ukoll fir-repożitorju PyPI.

Inkun kuntent li nikkummenta, nimpenja u suġġerimenti.

Se nipprova nwieġeb il-mistoqsijiet bl-aħjar mod li nista' hawn u fir-repożitorju.

Sors: www.habr.com