🥇PgGraph - أداة مساعدة للأرشفة والبحث في تبعيات الجدول في PostgreSQL

أريد اليوم أن أقدم لقراء Habr أداة مساعدة مكتوبة بلغة Python للعمل مع تبعيات الجدول في PostgreSQL DBMS.

واجهة برمجة التطبيقات الخاصة بالأداة بسيطة وتتكون من ثلاث طرق:

archive_table - أرشفة/حذف الصفوف بشكل متكرر باستخدام المفاتيح الأساسية المحددة
get_table_references - البحث عن تبعيات الجدول (سيظهر الجداول المشار إليها بواسطة الجدول المحدد وتلك التي تشير إليه)
get_rows_references - البحث عن صفوف في جداول أخرى تشير إلى صفوف محددة في الجدول المطلوب

قبل التاريخ

اسمي أوليغ بورزوف، وأنا مطور في فريق إدارة علاقات العملاء (CRM) لمديري الإقراض العقاري في دومكليك.

تعد قاعدة البيانات الرئيسية لنظام إدارة علاقات العملاء لدينا واحدة من أكبر قواعد البيانات من حيث الحجم في الشركة. إنها أيضًا واحدة من الأقدم: ظهرت عند إطلاق المشروع، عندما كانت الأشجار كبيرة، كانت Domklik شركة ناشئة، وبدلاً من خدمة صغيرة على إطار عمل Python غير المتزامن العصري، كان هناك كتلة ضخمة في 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.
نقوم بحذف الصفوف حسب PK من الخطوة 2 في الجدول Boarding_passes.
احذف الأسطر بواسطة PK من الخطوة 1 في Ticket_flights.
إزالة الخطوط من Flights.

وكانت النتيجة أداة مساعدة تسمى PgGraph، والتي قررنا أن نجعلها مفتوحة المصدر.

كيفية الاستخدام

تدعم الأداة وضعين للاستخدام:

الاتصال من سطر الأوامر (pggraph …).
الاستخدام في كود بايثون (class 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).

أمثلة الأوامر

أرشفة الجدول

الوظيفة الرئيسية للأداة هي أرشفة البيانات، أي. نقل الصفوف من الجدول الرئيسي إلى جدول الأرشيف (على سبيل المثال، من الجدول الكتب в books_archive).

يتم أيضًا دعم الحذف بدون أرشفة: لهذا تحتاج إلى تعيين المعلمة في config.ini to_archive = خطأ).

المعلمات المطلوبة - config_path والجدول والمعرفات.

بعد الإطلاق، سيتم حذف السجلات بشكل متكرر 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 جيثب: بموجب ترخيص معهد ماساتشوستس للتكنولوجيا، وكذلك في المستودع PyPI.

سأكون سعيدًا بالتعليقات والالتزامات والاقتراحات.

سأحاول الإجابة على الأسئلة بأفضل ما أستطيع هنا وفي المستودع.

المصدر: www.habr.com

PgGraph هي أداة مساعدة للأرشفة وإيجاد تبعيات الجدول في PostgreSQL

قبل التاريخ

كيفية الاستخدام

التثبيت والإعداد

تشغيل من وحدة التحكم

المعلمات

أمثلة الأوامر

أرشفة الجدول

ابحث عن التبعيات لجدول محدد

البحث عن مراجع للسلاسل باستخدام المفتاح الأساسي المحدد

الاستخدام في الكود

أرشفة الجدول

ابحث عن التبعيات لجدول محدد

البحث عن مراجع للسلاسل باستخدام المفتاح الأساسي المحدد

إضافة تعليق إلغاء الرد