ProHoster > Blog > Идораи > PgGraph як утилита барои бойгонӣ ва дарёфти вобастагии ҷадвал дар PostgreSQL мебошад

PgGraph як утилита барои бойгонӣ ва дарёфти вобастагии ҷадвал дар PostgreSQL мебошад

PgGraph як утилита барои бойгонӣ ва дарёфти вобастагии ҷадвал дар PostgreSQL мебошад
Имрӯз ман мехоҳам ба хонандагони Habr утилитаеро пешниҳод кунам, ки дар Python барои кор бо вобастагии ҷадвал дар DBMS PostgreSQL навишта шудааст.

API-и утилита оддӣ аст ва аз се усул иборат аст:

  • архив_ҷадвал - бойгонии рекурсивӣ/несткунии сатрҳо бо Калидҳои ибтидоии муайяншуда
  • истинодҳои_ҷадвал — ҷустуҷӯи вобастагиҳо барои ҷадвал (ҷадвалҳое, ки аз рӯи ҷадвали зикршуда ва онҳое, ки ба он истинод мекунанд, нишон медиҳанд)
  • гирифтани_сатор_маърифатҳо - ҷустуҷӯи сатрҳо дар ҷадвалҳои дигар, ки ба сатрҳои мушаххаси ҷадвали дилхоҳ муроҷиат мекунанд

prehistory

Номи ман Олег Борзов аст, ман як таҳиягари дастаи CRM барои менеҷерони қарздиҳии ипотека дар Домклик ҳастам.

Пойгоҳи асосии системаи CRM-и мо аз ҷиҳати ҳаҷм дар ширкат яке аз калонтаринҳо мебошад. Он инчунин яке аз қадимтаринҳост: он дар оғози лоиҳа пайдо шуд, вақте ки дарахтон калон буданд, Domklik як стартап буд ва ба ҷои микросервис дар чаҳорчӯбаи асинхронии Python дар PHP як монолити бузург вуҷуд дошт.

Гузариш аз PHP ба Python хеле тӯлонӣ буд ва дастгирии ҳамзамон ҳарду системаро талаб мекард, ки ба тарҳрезии пойгоҳи додаҳо таъсир расонд.

Дар натиҷа, мо як пойгоҳи додаҳо дорем, ки миқдори зиёди ҷадвалҳои хеле пайваст ва азим бо як қатор индексҳо барои намудҳои гуногуни дархостҳо. Хамаи ин ба кори махзани маълумот таъсири манфй мерасонад: аз сабаби мизхои калон ва як даста муносибатхои байни онхо мураккабии пурсишхо мунтазам меафзояд, ки ин махсусан барои чадвалхои пурборкунанда хеле мухим аст.

Барои кам кардани сарбории базаи маълумот, мо тасмим гирифтем, ки скрипт нависем, ки сабтҳои кӯҳнаро аз ҷадвалҳои ҳаҷмтарин ва пурборшуда ба бойгонӣ интиқол диҳад (масалан, аз task в task_archive).

Ин вазифа аз сабаби шумораи зиёди муносибатҳои байни ҷадвалҳо душвор аст: танҳо аз сатрҳо ҳаракат кунед task в task_archive кифоя нест, пеш аз ин ба шумо лозим аст, ки бо ҳама истинодҳо ба таври рекурсивӣ ҳамин корро кунед task ҷадвалҳо.

Ман бо як мисол нишон медиҳам базаи намоишӣ аз сайти postgrespro.ru:

PgGraph як утилита барои бойгонӣ ва дарёфти вобастагии ҷадвал дар PostgreSQL мебошад
Фарз мекунем, ки мо бояд сабтҳоро аз ҷадвал тоза кунем Flights. Postgres ба мо имкон намедиҳад, ки ин корро кунем: аввал мо бояд сабтҳоро аз ҳама ҷадвалҳои истинодкунанда тоза кунем ва ғайра ба таври рекурсивӣ то ҷадвалҳое, ки касе истинод намекунад.

Дар мисоли мо дар Flights ишора мекунад Ticket_flights, ва бар вай - Boarding_passes.

Аз ин рӯ, шумо бояд онро бо ин тартиб нест кунед:

  1. Мо арзишҳои калидҳои ибтидоии (PK) сатрҳоро мегирем Ticket_flights, ки ба сатрҳои ҳазфшуда ишора мекунанд Flights.
  2. Мо сатрҳои PK мегирем Boarding_passes, ки ба он ишора мекунанд Ticket_flights.
  3. Мо сатрҳоро аз ҷониби PK аз қадами 2 дар ҷадвал нест мекунем Boarding_passes.
  4. Сатрҳоро аз ҷониби PK аз қадами 1 дар Ticket_flights.
  5. Хориҷ кардани сатрҳо аз Flights.

Дар натиҷа як утилита бо номи PgGraph буд, ки мо тасмим гирифтем, ки манбаи кушода созем.

Чӣ тавр истифода бурда мешавад

Утилита ду намуди истифодаро дастгирӣ мекунад:

  • Занг аз сатри фармон (pggraph …).
  • Истифода дар рамзи Python (синф PgGraphApi).

Насб ва танзимот

Аввалан шумо бояд утилитаро аз анбори Pypi насб кунед:

pip3 install pggraph

Сипас файли config.ini дар мошини маҳаллӣ бо конфигуратсияи пойгоҳи додаҳо ва скрипти бойгонӣ эҷод кунед:

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

Аз консол иҷро кунед

параметрҳо

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

Далелҳои мавқеъ:

  • action - амали зарурӣ: archive_table, get_table_references ё get_rows_references.

Аргументҳои номбаршуда:

  • --config_path — роҳ ба файли танзимот;
  • --table — ҷадвале, ки бо он шумо бояд амалеро иҷро кунед;
  • --ids — рӯйхати id бо вергул ҷудошуда, масалан, 1,2,3 (параметри ихтиёрӣ);
  • --log_path — роҳ ба ҷузвдони гузоришҳо (параметри ихтиёрӣ, бо нобаёнӣ — ҷузвдони хонагӣ);
  • --log_level — сатҳи сабти ном (параметри ихтиёрӣ, пешфарз INFO аст).

Намунаҳои фармон

Архив кардани ҷадвал

Вазифаи асосии утилита бойгонии маълумот мебошад, яъне. интиқоли сатрҳо аз ҷадвали асосӣ ба ҷадвали бойгонӣ (масалан, аз ҷадвал китобҳои в китобҳо_архив).

Несткунии бе бойгонӣ низ дастгирӣ мешавад: барои ин шумо бояд параметрро дар config.ini насб кунед to_archive = бардурӯғ).

Параметрҳои зарурӣ - config_path, ҷадвал ва ids.

Пас аз оғоз, сабтҳо ба таври рекурсивӣ нест карда мешаванд ids дар ҷадвал table ва дар ҳама ҷадвалҳое, ки ба он ишора мекунанд.

$ 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

Пайдо кардани вобастагӣ барои ҷадвали муайян

Функсия барои дарёфти вобастагии ҷадвали муайян table. Параметрҳои зарурӣ - config_path и table.

Пас аз оғозёбӣ, луғат дар экран намоиш дода мешавад, ки дар он:

  • in_refs — луғати ҷадвалҳое, ки ба ҷадвали додашуда истинод мекунанд, ки дар он калид номи ҷадвал аст, арзиш — рӯйхати объектҳои Калиди хориҷӣ (pk_main - калиди ибтидоӣ дар ҷадвали асосӣ, pk_ref - калиди ибтидоӣ дар ҷадвали истинод, fk_ref — номи сутуне, ки калиди хориҷии ҷадвали манбаъ мебошад);
  • out_refs — луғати ҷадвалҳое, ки ин ба он ишора мекунад.

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

Ҷустуҷӯи истинодҳо ба сатрҳо бо калиди ибтидоӣ

Функсияи ҷустуҷӯи сатрҳо дар ҷадвалҳои дигар, ки ба сатрҳо тавассути Калиди хориҷӣ ишора мекунанд ids ҷадвалҳо table. Параметрҳои зарурӣ - config_path, table и ids.

Пас аз оғоз, луғат бо сохтори зерин дар экран намоиш дода мешавад:

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

Намунаи занг:

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

Истифода дар код

Илова ба он дар консол, китобхона метавонад дар рамзи Python истифода шавад. Намунаҳои зангҳо дар муҳити интерактивии iPython дар зер нишон дода шудаанд.

Архив кардани ҷадвал

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

Пайдо кардани вобастагӣ барои ҷадвали муайян

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

Ҷустуҷӯи истинодҳо ба сатрҳо бо калиди ибтидоӣ

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

Рамзи сарчашмаи китобхона дар ин ҷо дастрас аст GitHub дар доираи иҷозатномаи MIT, инчунин дар анбор PyPI.

Ман аз шарҳҳо, ӯҳдадориҳо ва пешниҳодҳо шод хоҳам буд.

Ман кӯшиш мекунам ба саволҳо то ҳадди имкон дар ин ҷо ва дар анбор ҷавоб диҳам.

Манбаъ: will.com

Илова Эзоҳ