🥇PgGraph - utiliti untuk mengarkib dan mencari kebergantungan jadual dalam PostgreSQL

Hari ini saya ingin membentangkan pembaca Habr dengan utiliti yang ditulis dalam Python untuk bekerja dengan kebergantungan jadual dalam DBMS PostgreSQL.

API utiliti adalah mudah dan terdiri daripada tiga kaedah:

jadual_arkib - mengarkib/memadam baris rekursif dengan Kekunci Utama yang ditentukan
dapatkan_rujukan_jadual — cari kebergantungan untuk jadual (akan menunjukkan jadual yang dirujuk oleh yang ditentukan dan yang merujuknya)
get_rows_references - cari baris dalam jadual lain yang merujuk baris yang ditentukan dalam jadual yang dikehendaki

prasejarah

Nama saya Oleg Borzov, saya seorang pemaju dalam pasukan CRM untuk pengurus pinjaman gadai janji di Domklik.

Pangkalan data utama sistem CRM kami adalah salah satu yang terbesar dari segi volum dalam syarikat. Ia juga merupakan salah satu yang tertua: ia muncul pada pelancaran projek itu, apabila pokok-pokoknya besar, Domklik adalah permulaan, dan bukannya perkhidmatan mikro pada rangka kerja tak segerak Python yang bergaya terdapat monolit yang besar dalam PHP.

Peralihan daripada PHP kepada Python adalah sangat panjang dan memerlukan sokongan serentak kedua-dua sistem, yang menjejaskan reka bentuk pangkalan data.

Akibatnya, kami mempunyai pangkalan data dengan sejumlah besar jadual yang sangat bersambung dan besar dengan sekumpulan indeks untuk jenis pertanyaan yang berbeza. Semua ini menjejaskan prestasi pangkalan data secara negatif: disebabkan oleh jadual besar dan sekumpulan perhubungan di antara mereka, kerumitan pertanyaan sentiasa meningkat, yang sangat kritikal untuk jadual yang paling banyak dimuatkan.

Untuk mengurangkan beban pada pangkalan data, kami memutuskan untuk menulis skrip yang akan memindahkan rekod lama daripada jadual yang paling banyak dan dimuatkan kepada yang diarkibkan (contohnya, daripada task в task_archive).

Tugas ini rumit oleh bilangan besar perhubungan antara jadual: hanya alihkan baris dari task в task_archive tidak mencukupi, sebelum itu anda perlu melakukan perkara yang sama secara rekursif dengan semua rujukan tersebut task meja.

Saya akan menunjukkan dengan contoh pangkalan data demo dari tapak postgrespro.ru:

Katakan kita perlu memadamkan rekod daripada jadual Flights. Postgres tidak membenarkan kami melakukan perkara ini begitu sahaja: kami perlu memadamkan rekod daripada semua jadual rujukan, dan seterusnya secara rekursif ke jadual yang tidak dirujuk oleh sesiapa.

Dalam contoh kami di Flights merujuk Ticket_flights, dan padanya - Boarding_passes.

Oleh itu, anda perlu memadamkannya dalam susunan ini:

Kami mendapat nilai kunci utama (PK) baris masuk Ticket_flights, yang merujuk kepada baris yang akan dipadamkan Flights.
Kami mendapat baris PK Boarding_passes, yang merujuk kepada Ticket_flights.
Kami memadamkan baris mengikut PK daripada langkah 2 dalam jadual Boarding_passes.
Padamkan baris mengikut PK dari langkah 1 dalam Ticket_flights.
Mengalih keluar baris daripada Flights.

Hasilnya ialah utiliti yang dipanggil PgGraph, yang kami memutuskan untuk membuat sumber terbuka.

Bagaimana nak guna

Utiliti menyokong dua mod penggunaan:

Panggilan daripada baris arahan (pggraph …).
Penggunaan dalam kod Python (class PgGraphApi).

Pemasangan dan konfigurasi

Mula-mula anda perlu memasang utiliti dari repositori Pypi:

pip3 install pggraph

Kemudian buat fail config.ini pada mesin tempatan dengan konfigurasi pangkalan data dan skrip pengarkiban:

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

Jalankan dari konsol

Parameter

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

Hujah kedudukan:

action - tindakan yang diperlukan: archive_table, get_table_references atau get_rows_references.

Hujah yang dinamakan:

--config_path — laluan ke fail konfigurasi;
--table — jadual yang anda perlukan untuk melakukan tindakan;
--ids — senarai id yang dipisahkan dengan koma, contohnya, 1,2,3 (parameter pilihan);
--log_path — laluan ke folder untuk log (parameter pilihan, secara lalai — folder rumah);
--log_level — tahap pengelogan (parameter pilihan, lalai ialah INFO).

Contoh perintah

Mengarkibkan jadual

Fungsi utama utiliti ialah pengarkiban data, i.e. memindahkan baris dari jadual utama ke jadual arkib (contohnya, dari jadual buku в arkib_buku).

Pemadaman tanpa pengarkiban juga disokong: untuk ini anda perlu menetapkan parameter dalam config.ini to_archive = palsu).

Parameter yang diperlukan - config_path, jadual dan id.

Selepas pelancaran, rekod akan dipadamkan secara rekursif ids di dalam jadual table dan dalam semua jadual yang merujuk kepadanya.

$ 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

Cari kebergantungan untuk jadual tertentu

Berfungsi untuk mencari kebergantungan jadual tertentu table. Parameter yang diperlukan - config_path и table.

Selepas pelancaran, kamus akan dipaparkan pada skrin, di mana:

in_refs — kamus jadual yang merujuk kepada yang diberikan, di mana kuncinya ialah nama jadual, nilainya ialah senarai objek Kunci Asing (pk_main - kunci utama dalam jadual utama, pk_ref - kunci utama dalam jadual rujukan, fk_ref — nama lajur yang merupakan kunci asing kepada jadual sumber);
out_refs — kamus jadual yang dirujuk ini.

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

Mencari rujukan kepada rentetan dengan Kunci Utama yang ditentukan

Berfungsi untuk mencari baris dalam jadual lain yang merujuk kepada baris melalui Kunci Asing ids jadual table. Parameter yang diperlukan - config_path, table и ids.

Selepas pelancaran, kamus dengan struktur berikut akan dipaparkan pada skrin:

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

Contoh panggilan:

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

Penggunaan dalam kod

Selain menjalankannya dalam konsol, perpustakaan boleh digunakan dalam kod Python. Contoh panggilan dalam persekitaran interaktif iPython ditunjukkan di bawah.

Mengarkibkan jadual

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

Cari kebergantungan untuk jadual tertentu

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

Mencari rujukan kepada rentetan dengan Kunci Utama yang ditentukan

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

Kod sumber perpustakaan boleh didapati di GitHub di bawah lesen MIT, serta dalam repositori PyPI.

Saya dengan senang hati akan memberi komen, komitmen dan cadangan.

Saya akan cuba menjawab soalan dengan sebaik mungkin di sini dan dalam repositori.

Sumber: www.habr.com

PgGraph - utiliti untuk mengarkib dan mencari kebergantungan jadual dalam PostgreSQL

prasejarah

Bagaimana nak guna

Pemasangan dan konfigurasi

Jalankan dari konsol

Parameter

Contoh perintah

Mengarkibkan jadual

Cari kebergantungan untuk jadual tertentu

Mencari rujukan kepada rentetan dengan Kunci Utama yang ditentukan

Penggunaan dalam kod

Mengarkibkan jadual

Cari kebergantungan untuk jadual tertentu

Mencari rujukan kepada rentetan dengan Kunci Utama yang ditentukan

Tambah komen Batal reply