Prohoster > Blog > quản lý > PGGraph - tiện ích lưu trữ và tìm kiếm các phụ thuộc của bảng trong PostgreSQL

PGGraph - tiện ích lưu trữ và tìm kiếm các phụ thuộc của bảng trong PostgreSQL

PGGraph - tiện ích lưu trữ và tìm kiếm các phụ thuộc của bảng trong PostgreSQL
Hôm nay tôi muốn giới thiệu với người đọc Habr một tiện ích được viết bằng Python để làm việc với các phụ thuộc bảng trong Cơ sở dữ liệu PostgreSQL.

API của tiện ích này rất đơn giản và bao gồm ba phương thức:

  • archive_table - lưu trữ/xóa đệ quy các hàng có Khóa chính được chỉ định
  • get_table_references - tìm kiếm các phụ thuộc cho một bảng (sẽ hiển thị các bảng được tham chiếu bởi bảng được chỉ định và các bảng tham chiếu đến nó)
  • get_rows_references - tìm kiếm các hàng trong các bảng khác tham chiếu các hàng được chỉ định trong bảng mong muốn

thời tiền sử

Tên tôi là Oleg Borzov, tôi là nhà phát triển trong nhóm CRM dành cho người quản lý cho vay thế chấp ở Domklik.

Cơ sở dữ liệu chính của hệ thống CRM của chúng tôi là một trong những cơ sở dữ liệu lớn nhất về số lượng trong công ty. Nó cũng là một trong những cái lâu đời nhất: nó xuất hiện ngay khi bắt đầu dự án, khi cây còn lớn, Domklik là một công ty khởi nghiệp và thay vì một dịch vụ vi mô trên khung không đồng bộ Python thời thượng, có một khối nguyên khối khổng lồ trong PHP.

Quá trình chuyển đổi từ PHP sang Python diễn ra rất lâu và cần có sự hỗ trợ đồng thời của cả hai hệ thống, điều này ảnh hưởng đến thiết kế cơ sở dữ liệu.

Kết quả là, chúng tôi có một cơ sở dữ liệu với một số lượng lớn các bảng khổng lồ và có tính kết nối cao cùng với một loạt chỉ mục cho các loại truy vấn khác nhau. Tất cả điều này ảnh hưởng tiêu cực đến hiệu suất của cơ sở dữ liệu: do các bảng lớn và nhiều mối quan hệ giữa chúng, độ phức tạp của các truy vấn không ngừng tăng lên, điều này đặc biệt quan trọng đối với các bảng được tải nhiều nhất.

Để giảm tải cho cơ sở dữ liệu, chúng tôi quyết định viết một tập lệnh chuyển các bản ghi cũ từ các bảng có dung lượng lớn và được tải nhiều nhất sang các bảng được lưu trữ (ví dụ: từ task в task_archive).

Nhiệm vụ này phức tạp bởi số lượng lớn các mối quan hệ giữa các bảng: chỉ cần di chuyển các hàng từ task в task_archive vẫn chưa đủ, trước đó bạn cần thực hiện đệ quy tương tự với tất cả những tham chiếu đó task những cái bàn.

Tôi sẽ chứng minh bằng một ví dụ cơ sở dữ liệu demo từ trang postgrespro.ru:

PGGraph - tiện ích lưu trữ và tìm kiếm các phụ thuộc của bảng trong PostgreSQL
Giả sử chúng ta cần xóa các bản ghi khỏi một bảng Flights. Postgres sẽ không cho phép chúng ta làm điều này giống như vậy: trước tiên chúng ta cần xóa các bản ghi khỏi tất cả các bảng tham chiếu, v.v. theo cách đệ quy xuống các bảng không được bất kỳ ai tham chiếu.

Trong ví dụ của chúng tôi tại Flights đề cập đến Ticket_flights, và trên người cô ấy - Boarding_passes.

Vì vậy, bạn cần xóa nó theo thứ tự sau:

  1. Chúng tôi nhận được các giá trị khóa chính (PK) của các hàng trong Ticket_flights, đề cập đến các hàng sẽ bị xóa trong Flights.
  2. Chúng tôi nhận được hàng PK Boarding_passes, đề cập đến Ticket_flights.
  3. Chúng ta xóa các hàng bằng PK ở bước 2 trong bảng Boarding_passes.
  4. Xóa dòng bằng PK ở bước 1 Ticket_flights.
  5. Xóa các dòng khỏi Flights.

Kết quả là chúng tôi đã tạo ra một tiện ích có tên PGGraph, tiện ích này đã quyết định tạo thành nguồn mở.

Cách sử dụng

Tiện ích hỗ trợ hai chế độ sử dụng:

  • Gọi từ dòng lệnh (pggraph …).
  • Cách sử dụng trong mã Python (lớp PgGraphApi).

Cài đặt và cấu hình

Đầu tiên bạn cần cài đặt tiện ích từ kho Pypi:

pip3 install pggraph

Sau đó tạo tệp config.ini trên máy cục bộ với cấu hình cơ sở dữ liệu và tập lệnh lưu trữ:

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

Chạy từ bảng điều khiển

Tham số

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

Đối số vị trí:

  • action - Yêu cầu hành động: archive_table, get_table_references hoặc get_rows_references.

Đối số được đặt tên:

  • --config_path - đường dẫn đến tập tin cấu hình;
  • --table — một bảng mà bạn cần thực hiện một hành động;
  • --ids - danh sách id được phân tách bằng dấu phẩy, ví dụ: 1,2,3 (tham số tùy chọn);
  • --log_path — đường dẫn đến thư mục chứa nhật ký (tham số tùy chọn, theo mặc định - thư mục chính);
  • --log_level — mức ghi nhật ký (tham số tùy chọn, mặc định là INFO).

Ví dụ lệnh

Lưu trữ một bảng

Chức năng chính của tiện ích là lưu trữ dữ liệu, tức là. chuyển các hàng từ bảng chính sang bảng lưu trữ (ví dụ: từ bảng sách в sách_archive).

Xóa mà không lưu trữ cũng được hỗ trợ: để làm được điều này, bạn cần đặt tham số trong config.ini to_archive = sai).

Các thông số bắt buộc - config_path, bảng và id.

Sau khi khởi chạy, các bản ghi sẽ bị xóa đệ quy ids trong bảng table và trong tất cả các bảng tham chiếu đến nó.

$ 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

Tìm phần phụ thuộc cho một bảng được chỉ định

Hàm tìm phụ thuộc của một bảng được chỉ định table. Các thông số bắt buộc - config_path и table.

Sau khi khởi chạy, một từ điển sẽ được hiển thị trên màn hình, trong đó:

  • in_refs - một từ điển các bảng tham chiếu đến một bảng nhất định, trong đó khóa là tên của bảng, giá trị là danh sách các đối tượng Khóa ngoài (pk_main - khóa chính trong bảng chính, pk_ref - khóa chính trong bảng tham chiếu, fk_ref — tên của cột là khóa ngoại của bảng nguồn);
  • out_refs - một từ điển các bảng mà cái này đề cập đến.

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

Tìm tham chiếu đến chuỗi có Khóa chính được chỉ định

Chức năng tìm kiếm các hàng trong bảng khác tham chiếu đến các hàng thông qua Khóa ngoài ids bảng table. Các thông số bắt buộc - config_path, table и ids.

Sau khi khởi chạy, trên màn hình sẽ hiển thị một từ điển có cấu trúc như sau:

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

Cuộc gọi ví dụ:

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

Cách sử dụng trong mã

Ngoài việc chạy nó trong bảng điều khiển, thư viện có thể được sử dụng bằng mã Python. Ví dụ về các cuộc gọi trong môi trường tương tác iPython được hiển thị bên dưới.

Lưu trữ một bảng

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

Tìm phần phụ thuộc cho một bảng được chỉ định

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

Tìm tham chiếu đến chuỗi có Khóa chính được chỉ định

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

Mã nguồn thư viện có sẵn tại GitHub theo giấy phép MIT, cũng như trong kho lưu trữ PyPI.

Tôi sẽ vui mừng nhận xét, cam kết và đề xuất.

Tôi sẽ cố gắng trả lời các câu hỏi trong khả năng tốt nhất của mình ở đây và trong kho lưu trữ.

Nguồn: www.habr.com

Thêm một lời nhận xét