🥇PgGraph - PostgreSQL-də cədvəl asılılıqlarının arxivləşdirilməsi və axtarışı üçün yardım proqramı

Bu gün Habr oxucularına PostgreSQL DBMS-də cədvəl asılılıqları ilə işləmək üçün Python-da yazılmış yardım proqramı təqdim etmək istəyirəm.

Utiltin API sadədir və üç üsuldan ibarətdir:

arxiv_cədvəli - Müəyyən İlkin Açarlarla sətirlərin rekursiv arxivləşdirilməsi/silinməsi
cədvəl_referansları əldə edin — cədvəl üçün asılılıqları axtarın (göstərilən və ona istinad edən cədvəlləri göstərəcək)
sətirlər_istinadlarını əldə edin - istədiyiniz cədvəldə müəyyən edilmiş sətirlərə istinad edən digər cədvəllərdə sətirləri axtarın

Prehistorya

Mənim adım Oleq Borzov, mən Domklik-də ipoteka krediti menecerləri üçün CRM komandasında tərtibatçıyam.

CRM sistemimizin əsas məlumat bazası şirkətdə həcm baxımından ən böyüklərdən biridir. O, həm də ən qədimlərdən biridir: o, layihənin başlanğıcında, ağaclar böyük olanda, Domklik startap idi və dəbli Python asinxron çərçivəsindəki mikroservis əvəzinə PHP-də nəhəng monolit meydana çıxdı.

PHP-dən Python-a keçid çox uzun sürdü və hər iki sistemin eyni vaxtda dəstəyini tələb etdi, bu da verilənlər bazasının dizaynına təsir etdi.

Nəticədə, müxtəlif növ sorğular üçün bir dəstə indeksi olan çoxlu sayda yüksək əlaqəli və nəhəng cədvəllərdən ibarət verilənlər bazamız var. Bütün bunlar verilənlər bazasının işinə mənfi təsir göstərir: böyük cədvəllər və onlar arasındakı bir çox əlaqələr sayəsində sorğuların mürəkkəbliyi daim artır, bu da ən çox yüklənmiş cədvəllər üçün xüsusilə vacibdir.

Verilənlər bazasına yükü azaltmaq üçün köhnə qeydləri ən həcmli və yüklənmiş cədvəllərdən arxivləşdirilmişlərə köçürəcək bir skript yazmaq qərarına gəldik (məsələn, task в task_archive).

Bu tapşırıq cədvəllər arasında çox sayda əlaqə ilə çətinləşir: sadəcə olaraq sətirləri buradan köçürün task в task_archive kifayət deyil, ondan əvvəl bütün istinadlarla eyni şeyi rekursiv şəkildə etməlisiniz task masalar.

Mən bir nümunə ilə nümayiş etdirəcəyəm postgrespro.ru saytından demo verilənlər bazası:

Tutaq ki, cədvəldən qeydləri silmək lazımdır Flights. Postgres bizə bunu belə etməyə icazə verməyəcək: biz əvvəlcə bütün istinad cədvəllərindən qeydləri silməliyik və s. rekursiv olaraq heç kimin istinad etmədiyi cədvəllərə qədər.

Bizim nümunəmizdə Flights istinad edir Ticket_flights, və onun üzərində - Boarding_passes.

Buna görə də, onu bu ardıcıllıqla silməlisiniz:

Sətirlərin əsas açarlarının (PK) dəyərlərini alırıq Ticket_flights, silinəcək sətirlərə aiddir Flights.
PK sıralarını alırıq Boarding_passes, istinad edən Ticket_flights.
Cədvəlin 2-ci addımından PK ilə sətirləri silirik Boarding_passes.
1-ci addımdan PK ilə sətirləri silin Ticket_flights.
Sətirlərin çıxarılması Flights.

Nəticə PgGraph adlı bir yardım proqramı oldu və biz onu açıq mənbə etmək qərarına gəldik.

Necə istifadə

Faydalı proqram iki istifadə rejimini dəstəkləyir:

Komanda xəttindən zəng (pggraph …).
Python kodunda istifadə (sinif PgGraphApi).

Quraşdırma və konfiqurasiya

Əvvəlcə yardım proqramını Pypi deposundan quraşdırmalısınız:

pip3 install pggraph

Sonra verilənlər bazasının konfiqurasiyası və arxiv skripti ilə yerli maşında config.ini faylı yaradın:

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

Konsoldan qaçın

Parameters

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

Mövqe arqumentləri:

action - tələb olunan hərəkət: archive_table, get_table_references və ya get_rows_references.

Adlandırılmış arqumentlər:

--config_path — konfiqurasiya faylına gedən yol;
--table — hərəkəti yerinə yetirmək üçün lazım olan cədvəl;
--ids — vergüllə ayrılmış id siyahısı, məsələn, 1,2,3 (isteğe bağlı parametr);
--log_path — qeydlər üçün qovluğa gedən yol (isteğe bağlı parametr, standart olaraq — ev qovluğu);
--log_level — qeyd səviyyəsi (isteğe bağlı parametr, standart INFO-dur).

Əmr nümunələri

Cədvəlin arxivləşdirilməsi

Faydalı proqramın əsas funksiyası məlumatların arxivləşdirilməsidir, yəni. sətirlərin əsas cədvəldən arxiv cədvəlinə köçürülməsi (məsələn, cədvəldən Kitablar в kitablar_arxivi).

Arxivləşdirmədən silmə də dəstəklənir: bunun üçün config.ini-də parametr təyin etməlisiniz to_archive = false).

Tələb olunan parametrlər - config_path, cədvəl və idlər.

Başladıqdan sonra qeydlər rekursiv olaraq silinəcək ids cədvəldə table və ona istinad edən bütün cədvəllərdə.

$ 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

Müəyyən edilmiş cədvəl üçün asılılıqları tapın

Müəyyən edilmiş cədvəlin asılılıqlarını tapmaq funksiyası table. Tələb olunan parametrlər - config_path и table.

Başladıqdan sonra ekranda lüğət görünəcək, burada:

in_refs — verilmiş birinə istinad edən cədvəllər lüğəti, burada açar cədvəlin adıdır, dəyər isə Xarici Açar obyektlərinin siyahısıdır (pk_main - əsas cədvəldə əsas açar, pk_ref - istinad cədvəlindəki əsas açar, fk_ref — mənbə cədvəlinin xarici açarı olan sütunun adı);
out_refs — bunun istinad etdiyi cədvəllər lüğəti.

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

Göstərilən Əsas Açar ilə sətirlərə istinadların tapılması

Xarici Açar vasitəsilə sətirlərə istinad edən digər cədvəllərdə sətirləri axtarmaq funksiyası ids masalar table. Tələb olunan parametrlər - config_path, table и ids.

Başladıqdan sonra ekranda aşağıdakı quruluşa malik lüğət görünəcək:

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

Zəng nümunəsi:

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

Kodda istifadə

Konsolda işləməkdən əlavə, kitabxana Python kodunda istifadə edilə bilər. iPython interaktiv mühitində zənglərin nümunələri aşağıda göstərilmişdir.

Cədvəlin arxivləşdirilməsi

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

Müəyyən edilmiş cədvəl üçün asılılıqları tapın

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

Göstərilən Əsas Açar ilə sətirlərə istinadların tapılması

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

Kitabxananın mənbə kodu burada mövcuddur Github MIT lisenziyası altında, eləcə də depoda PyPI.

Şərhlərə, öhdəliklərə və təkliflərə şad olaram.

Burada və anbarda bacardığım qədər suallara cavab verməyə çalışacağam.

Mənbə: www.habr.com

PgGraph PostgreSQL-də cədvəl asılılıqlarını arxivləşdirmək və tapmaq üçün bir yardım proqramıdır

Prehistorya

Necə istifadə

Quraşdırma və konfiqurasiya

Konsoldan qaçın

Parameters

Əmr nümunələri

Cədvəlin arxivləşdirilməsi

Müəyyən edilmiş cədvəl üçün asılılıqları tapın

Göstərilən Əsas Açar ilə sətirlərə istinadların tapılması

Kodda istifadə

Cədvəlin arxivləşdirilməsi

Müəyyən edilmiş cədvəl üçün asılılıqları tapın

Göstərilən Əsas Açar ilə sətirlərə istinadların tapılması

Добавить комментарий Cavab ləğv