🚏 🤖 🦈 PgGraph - أداة أرشفة وإيجاد تبعيات الجدول في PostgreSQL 👌🏻 💖 🏾

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

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

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

خلفية

اسمي أوليغ بورزوف ، وأنا مطور في فريق CRM لمديري قروض الرهن العقاري في Domklik.

تعد قاعدة البيانات الرئيسية لنظام إدارة علاقات العملاء لدينا واحدة من أكبر قواعد البيانات من حيث الحجم في الشركة. كما أنها واحدة من أقدمها: ظهرت عند إطلاق المشروع ، عندما كانت الأشجار كبيرة ، كانت Domclick شركة ناشئة ، وبدلاً من خدمة صغيرة في إطار 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 …).
الاستخدام في كود 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- قائمة معرف مفصولة بفواصل ، على سبيل المثال ، 1,2,3(اختياري) ؛
--log_path - المسار إلى مجلد السجلات (المعلمة الاختيارية ، افتراضيًا - المجلد الرئيسي) ؛
--log_level - مستوى التسجيل (معلمة اختيارية ، افتراضيًا - INFO).

أمثلة القيادة

أرشفة طاولة

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

الحذف بدون الأرشفة مدعوم أيضًا: للقيام بذلك ، قم بتعيين المعلمة إلى_archive = false في config.ini ).

المعلمات المطلوبة هي 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 بموجب ترخيص MIT ، وكذلك في مستودع PyPI .

التعليقات والالتزامات والاقتراحات مرحب بها

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

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

خلفية

كيف تستعمل

التثبيت والتكوين

يعمل من وحدة التحكم

المعلمات

أمثلة القيادة

أرشفة طاولة

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

البحث عن روابط إلى سلاسل باستخدام مفتاح أساسي محدد

الاستخدام في التعليمات البرمجية

أرشفة طاولة

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

البحث عن روابط إلى سلاسل باستخدام مفتاح أساسي محدد

More articles: