🥇PgGraph - PostgreSQL でテーブルの依存関係をアーカイブおよび検索するためのユーティリティ

今日は、Habr 読者に、PostgreSQL DBMS でテーブルの依存関係を操作するための Python で書かれたユーティリティを紹介したいと思います。

このユーティリティの API はシンプルで、次の XNUMX つのメソッドで構成されています。

アーカイブテーブル - 指定された主キーを持つ行の再帰的アーカイブ/削除
get_table_references — テーブルの依存関係を検索します (指定されたテーブルによって参照されているテーブルと、それを参照しているテーブルが表示されます)
get_rows_references - 目的のテーブル内の指定された行を参照する他のテーブル内の行を検索します。

背景

私の名前はオレグ・ボルゾフです。ドムクリクの住宅ローン融資マネージャー向けの CRM チームの開発者です。

当社の CRM システムのメインデータベースは、社内で最大規模のデータベースの XNUMX つです。これは最も古いものの XNUMX つでもあります。プロジェクトの立ち上げ当初、ツリーが大きく成長し、Domklik がスタートアップであった頃、流行の 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.
テーブルのステップ 2 から PK によって行を削除します。 Boarding_passes.
の手順 1 から PK によって行を削除します。 Ticket_flights.
から行を削除しています Flights.

その結果、PgGraph というユーティリティが誕生し、これをオープンソースにすることにしました。

使用方法

このユーティリティは、次の XNUMX つの使用モードをサポートしています。

コマンドラインから呼び出します(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 — カンマで区切られた ID のリスト。例: 1,2,3 (オプションのパラメータ);
--log_path — ログのフォルダーへのパス (オプションのパラメーター、デフォルトではホームフォルダー)。
--log_level — ログレベル (オプションのパラメータ、デフォルトは INFO)。

コマンド例

テーブルのアーカイブ

このユーティリティの主な機能はデータのアーカイブです。メインテーブルからアーカイブテーブルへの行の転送（たとえば、テーブルから図書 в 書籍_アーカイブ).

アーカイブを行わずに削除することもできます。そのためには、config.ini でパラメータを設定する必要があります。 to_archive = false).

必須パラメータ - config_path、テーブル、および ID.

起動後、レコードは再帰的に削除されます 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.

喜んでコメント、コミット、提案をさせていただきます。

こことリポジトリでできる限り質問に答えるように努めます。

出所： habr.com

PgGraph は、PostgreSQL でテーブルの依存関係をアーカイブおよび検索するためのユーティリティです

背景

使用方法

インストールと構成

コンソールから実行

パラメータ

コマンド例

テーブルのアーカイブ

指定されたテーブルの依存関係を検索します

指定された主キーを持つ文字列への参照の検索

コード内での使用法

テーブルのアーカイブ

指定されたテーブルの依存関係を検索します

指定された主キーを持つ文字列への参照の検索

コメントを追加します返信をキャンセル

PgGraph は、PostgreSQL でテーブルの依存関係をアーカイブおよび検索するためのユーティリティです

背景

使用方法

インストールと構成

コンソールから実行

パラメータ

コマンド例

テーブルのアーカイブ

指定されたテーブルの依存関係を検索します

指定された主キーを持つ文字列への参照の検索

コード内での使用法

テーブルのアーカイブ

指定されたテーブルの依存関係を検索します

指定された主キーを持つ文字列への参照の検索

コメントを追加します 返信をキャンセル

コメントを追加します返信をキャンセル