🥇PgGraph - PostgreSQL'de tablo bağımlılıklarını arşivlemek ve aramak için bir yardımcı program

Bugün Habr okuyucularına PostgreSQL DBMS'deki tablo bağımlılıklarıyla çalışmak için Python'da yazılmış bir yardımcı program sunmak istiyorum.

Yardımcı programın API'si basittir ve üç yöntemden oluşur:

arşiv_tablosu - belirtilen Birincil Anahtarlarla satırları yinelemeli olarak arşivleme/silme
get_table_references — bir tablonun bağımlılıklarını arayın (belirtilen tablonun referans verdiği tabloları ve ona referans veren tabloları gösterir)
get_rows_references - istenen tabloda belirtilen satırlara referans veren diğer tablolardaki satırları arayın

tarih öncesi

Adım Oleg Borzov, Domklik'teki ipotek kredisi yöneticilerinin CRM ekibinde geliştiriciyim.

CRM sistemimizin ana veri tabanı, şirketteki hacim açısından en büyük veri tabanlarından biridir. Aynı zamanda en eskilerinden biri: projenin başlangıcında, ağaçlar büyükken ortaya çıktı, Domklik bir başlangıçtı ve modaya uygun Python asenkron çerçevesindeki bir mikro hizmet yerine PHP'de devasa bir monolit vardı.

PHP'den Python'a geçiş çok uzun sürdü ve her iki sistemin eş zamanlı desteklenmesini gerektiriyordu, bu da veritabanının tasarımını etkiledi.

Sonuç olarak, farklı sorgu türleri için çok sayıda indeks içeren, yüksek düzeyde bağlantılı ve devasa tablolardan oluşan bir veritabanımız var. Tüm bunlar veritabanının performansını olumsuz yönde etkiler: Büyük tablolar ve aralarındaki bir dizi ilişki nedeniyle sorguların karmaşıklığı sürekli artmaktadır ve bu, özellikle en çok yüklenen tablolar için kritik öneme sahiptir.

Veritabanındaki yükü azaltmak için, eski kayıtları en hacimli ve yüklü tablolardan arşivlenmiş tablolara aktaracak bir komut dosyası yazmaya karar verdik (örneğin, task в task_archive).

Bu görev, tablolar arasındaki çok sayıda ilişki nedeniyle karmaşık hale gelir: yalnızca satırları task в task_archive yeterli değil, ondan önce tüm bu referanslarla aynı şeyi yinelemeli olarak yapmanız gerekir. task tablolar.

Bir örnekle göstereceğim postgrespro.ru sitesinden demo veritabanı:

Diyelim ki bir tablodaki kayıtları silmemiz gerekiyor Flights. Postgres bunu bu şekilde yapmamıza izin vermez: Öncelikle tüm referans tablolarından kayıtları silmemiz gerekir ve bu şekilde, hiç kimse tarafından referans verilmeyen tablolara kadar yinelemeli olarak devam etmeliyiz.

Bizim örneğimizde Flights ifade eder Ticket_flightsve onun üzerinde - Boarding_passes.

Bu nedenle, bu sırayla silmeniz gerekir:

Satırların birincil anahtar (PK) değerlerini alıyoruz Ticket_flightssilinecek satırlara atıfta bulunan Flights.
PK satırları alıyoruz Boarding_passes, atıfta bulunan Ticket_flights.
Tablodaki 2. adımdaki satırları PK'ye göre sileriz Boarding_passes.
1. adımdan itibaren satırları PK'ye göre silin Ticket_flights.
Çizgileri kaldırmak Flights.

Sonuç olarak, açık kaynak yapmaya karar verdiğimiz PgGraph adında bir yardımcı program ortaya çıktı.

Nasıl kullanılır?

Yardımcı program iki kullanım modunu destekler:

Komut satırından arayın (pggraph …).
Python kodunda kullanım (sınıf PgGraphApi).

Kurulum ve konfigürasyon

Öncelikle yardımcı programı Pypi deposundan yüklemeniz gerekir:

pip3 install pggraph

Ardından yerel makinede veritabanının yapılandırmasını ve arşivleme komut dosyasını içeren bir config.ini dosyası oluşturun:

[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 çalıştır

Parametreler

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

Konumsal argümanlar:

action - gerekli eylem: archive_table, get_table_references veya get_rows_references.

Adlandırılmış bağımsız değişkenler:

--config_path — yapılandırma dosyasının yolu;
--table — bir eylem gerçekleştirmeniz gereken bir tablo;
--ids — virgülle ayrılmış kimlik listesi, örneğin, 1,2,3 (isteğe bağlı parametre);
--log_path — günlükler klasörünün yolu (isteğe bağlı parametre, varsayılan olarak — ana klasör);
--log_level — kayıt düzeyi (isteğe bağlı parametre, varsayılan INFO'dur).

Komut örnekleri

Bir tablonun arşivlenmesi

Yardımcı programın ana işlevi veri arşivlemedir, yani. satırların ana tablodan arşiv tablosuna aktarılması (örneğin tablodan) kitaplar в kitaplar_arşiv).

Arşivlemeden silme de desteklenmektedir: bunun için config.ini dosyasındaki parametreyi ayarlamanız gerekir. to_archive = yanlış).

Gerekli parametreler - config_path, tablo ve kimlikler.

Başlatma sonrasında kayıtlar yinelemeli olarak silinecek ids masada table ve buna atıfta bulunan tüm tablolarda.

$ 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

Belirtilen tablo için bağımlılıkları bulun

Belirtilen tablonun bağımlılıklarını bulma işlevi table. Gerekli parametreler - config_path и table.

Başlattıktan sonra ekranda bir sözlük görüntülenecektir; burada:

in_refs — belirli bir tabloya referans veren tabloların sözlüğü; burada anahtar tablonun adıdır, değer ise Yabancı Anahtar nesnelerinin bir listesidir (pk_main - ana tablodaki birincil anahtar, pk_ref - referans tablosundaki birincil anahtar, fk_ref — kaynak tablonun yabancı anahtarı olan sütunun adı);
out_refs — bunun atıfta bulunduğu tabloların sözlüğü.

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

Belirtilen Birincil Anahtara sahip dizelere başvuruları bulma

Yabancı Anahtar aracılığıyla diğer tablolardaki satırlara başvuran satırları arama işlevi ids tablolar table. Gerekli parametreler - config_path, table и ids.

Başlattıktan sonra ekranda aşağıdaki yapıya sahip bir sözlük görüntülenecektir:

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

Örnek çağrı:

$ 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 kullanım

Kütüphane, konsolda çalıştırmanın yanı sıra Python kodunda da kullanılabilir. iPython etkileşimli ortamındaki çağrı örnekleri aşağıda gösterilmektedir.

Bir tablonun arşivlenmesi

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

Belirtilen tablo için bağımlılıkları bulun

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

Belirtilen Birincil Anahtara sahip dizelere başvuruları bulma

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

Kütüphane kaynak kodu şu adreste mevcuttur: GitHub MIT lisansı altında ve depoda PyPI.

Yorumlardan, taahhütlerden ve önerilerden memnuniyet duyacağım.

Soruları burada ve depoda elimden geldiğince cevaplamaya çalışacağım.

Kaynak: habr.com

PgGraph, PostgreSQL'de tablo bağımlılıklarını arşivlemek ve bulmak için kullanılan bir yardımcı programdır

tarih öncesi

Nasıl kullanılır?

Kurulum ve konfigürasyon

Konsoldan çalıştır

Parametreler

Komut örnekleri

Bir tablonun arşivlenmesi

Belirtilen tablo için bağımlılıkları bulun

Belirtilen Birincil Anahtara sahip dizelere başvuruları bulma

Kodda kullanım

Bir tablonun arşivlenmesi

Belirtilen tablo için bağımlılıkları bulun

Belirtilen Birincil Anahtara sahip dizelere başvuruları bulma

Yorum ekle Cevabı iptal