🥇PgGraph - ابزاری برای آرشیو و جستجوی وابستگی های جدول در PostgreSQL

امروز می خواهم به خوانندگان Habr یک ابزار نوشته شده در پایتون برای کار با وابستگی های جدول در PostgreSQL DBMS ارائه کنم.

API این ابزار ساده است و از سه روش تشکیل شده است:

آرشیو_جدول - بایگانی بازگشتی/حذف ردیف ها با کلیدهای اصلی مشخص شده
get_table_references - جستجوی وابستگی ها برای یک جدول (جدول های ارجاع شده توسط جدول مشخص شده و کسانی که به آن ارجاع می دهند را نشان می دهد)
get_rows_references - سطرهایی را در جداول دیگر جستجو کنید که به ردیف های مشخص شده در جدول مورد نظر اشاره می کنند

ماقبل تاریخ

نام من اولگ برزوف است، من یک توسعه دهنده در تیم CRM برای مدیران وام مسکن در Domklik هستم.

پایگاه داده اصلی سیستم CRM ما از نظر حجم یکی از بزرگترین پایگاه های داده در شرکت است. همچنین یکی از قدیمی ترین هاست: در همان زمان راه اندازی پروژه ظاهر شد، زمانی که درختان بزرگ بودند، Domklik یک استارتاپ بود، و به جای یک میکروسرویس در چارچوب ناهمزمان پایتون مد روز، یکپارچگی عظیم در PHP وجود داشت.

انتقال از PHP به Python بسیار طولانی بود و نیاز به پشتیبانی همزمان از هر دو سیستم داشت که بر طراحی پایگاه داده تأثیر گذاشت.

در نتیجه، ما یک پایگاه داده با تعداد زیادی جداول بسیار متصل و بزرگ با دسته ای از نمایه ها برای انواع مختلف پرس و جو داریم. همه اینها بر عملکرد پایگاه داده تأثیر منفی می گذارد: به دلیل جداول بزرگ و مجموعه ای از روابط بین آنها، پیچیدگی پرس و جوها به طور مداوم در حال افزایش است که به ویژه برای جداول با بیشترین بارگذاری بسیار مهم است.

برای کاهش بار روی پایگاه داده، تصمیم گرفتیم اسکریپتی بنویسیم که رکوردهای قدیمی را از حجیم ترین و بارگذاری شده ترین جداول به جداول آرشیو شده (مثلاً از task в task_archive).

این کار به دلیل تعداد زیاد روابط بین جداول پیچیده است: به سادگی سطرها را از آن جابجا کنید task в task_archive کافی نیست، قبل از آن باید همین کار را به صورت بازگشتی با همه آن ارجاع دهی انجام دهید task جداول

من با یک مثال نشان خواهم داد پایگاه داده آزمایشی از سایت postgrespro.ru:

فرض کنید باید رکوردها را از یک جدول حذف کنیم Flights. Postgres به ما اجازه نمی دهد که این کار را دقیقاً به این صورت انجام دهیم: ابتدا باید رکوردها را از تمام جداول مرجع حذف کنیم و به همین ترتیب به صورت بازگشتی به جداولی که توسط هیچکس ارجاع داده نشده است ادامه دهیم.

در مثال ما در Flights اشاره دارد Ticket_flightsو روی او - Boarding_passes.

بنابراین، شما باید آن را به ترتیب حذف کنید:

ما مقادیر کلیدهای اولیه (PK) ردیف ها را دریافت می کنیم Ticket_flights، که به ردیف هایی که قرار است حذف شوند اشاره دارد Flights.
ما ردیف های PK را دریافت می کنیم Boarding_passes، که به Ticket_flights.
از مرحله 2 جدول، سطرها را با PK حذف می کنیم Boarding_passes.
حذف خطوط توسط PK از مرحله 1 در Ticket_flights.
حذف خطوط از Flights.

نتیجه یک ابزار به نام PgGraph بود که تصمیم گرفتیم آن را منبع باز بسازیم.

نحوه استفاده

این ابزار از دو حالت استفاده پشتیبانی می کند:

تماس از خط فرمان (pggraph …).
استفاده در کد پایتون (کلاس 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 - لیست شناسه هایی که با کاما از هم جدا شده اند، برای مثال، 1,2,3 (پارامتر اختیاری)؛
--log_path - مسیر ورود به پوشه برای سیاهههای مربوط (پارامتر اختیاری، به طور پیش فرض - پوشه اصلی).
--log_level - سطح ورود به سیستم (پارامتر اختیاری، پیش‌فرض INFO است).

نمونه های دستوری

آرشیو کردن یک جدول

عملکرد اصلی ابزار بایگانی داده است، یعنی. انتقال سطرها از جدول اصلی به جدول آرشیو (مثلاً از جدول کتاب ها в کتاب_آرشیو).

حذف بدون بایگانی نیز پشتیبانی می شود: برای این شما باید پارامتر را در config.ini تنظیم کنید to_archive = نادرست).

پارامترهای مورد نیاز - config_path، جدول و id.

پس از راه اندازی، رکوردها به صورت بازگشتی حذف می شوند 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'}]}}}

استفاده در کد

علاوه بر اجرای آن در کنسول، کتابخانه را می توان در کد پایتون نیز استفاده کرد. نمونه هایی از تماس ها در محیط تعاملی 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.

از نظرات، تعهدات و پیشنهادات خوشحال خواهم شد.

من سعی می کنم در اینجا و در مخزن به بهترین شکل ممکن به سوالات پاسخ دهم.

منبع: www.habr.com

PgGraph یک ابزار برای بایگانی و یافتن وابستگی های جدول در PostgreSQL است.

ماقبل تاریخ

نحوه استفاده

نصب و پیکربندی

از کنسول اجرا شود

پارامترهای

نمونه های دستوری

آرشیو کردن یک جدول

پیدا کردن وابستگی برای یک جدول مشخص

یافتن ارجاع به رشته ها با کلید اصلی مشخص شده

استفاده در کد

آرشیو کردن یک جدول

پیدا کردن وابستگی برای یک جدول مشخص

یافتن ارجاع به رشته ها با کلید اصلی مشخص شده

اضافه کردن نظر انصراف از پاسخ