ProHoster > Π‘Π»ΠΎΠ³ > Pagdumala > Ang PgGraph usa ka gamit sa pag-archive ug pagpangita sa mga dependency sa lamesa sa PostgreSQL

Ang PgGraph usa ka gamit sa pag-archive ug pagpangita sa mga dependency sa lamesa sa PostgreSQL

Ang PgGraph usa ka gamit sa pag-archive ug pagpangita sa mga dependency sa lamesa sa PostgreSQL
Karon gusto nakong ipresentar ang mga magbabasa sa Habr sa usa ka utility nga gisulat sa Python alang sa pagtrabaho sa mga dependency sa lamesa sa PostgreSQL DBMS.

Ang API sa utility yano ug adunay tulo ka mga pamaagi:

  • archive_table - recursive nga pag-archive/pagtangtang sa mga laray nga adunay gipiho nga Primary Keys
  • get_table_references - pagpangita alang sa mga dependency alang sa usa ka lamesa (ipakita ang mga lamesa nga gi-refer sa usa nga gitakda ug kadtong nag-refer niini)
  • get_rows_references - Pangitaa ang mga laray sa ubang mga lamesa nga nag-refer sa piho nga mga laray sa gusto nga lamesa

sa naunang kasaysayan

Ang akong ngalan mao si Oleg Borzov, ako usa ka developer sa CRM team alang sa mortgage lending managers sa Domklik.

Ang nag-unang database sa among CRM nga sistema mao ang usa sa pinakadako sa mga termino sa gidaghanon sa kompanya. Usa usab kini sa labing karaan: kini nagpakita sa mismong paglansad sa proyekto, kung ang mga kahoy dagko, ang Domklik usa ka pagsugod, ug imbes nga usa ka microservice sa us aka uso nga Python asynchronous nga balangkas adunay usa ka dako nga monolith sa PHP.

Ang transisyon gikan sa PHP ngadto sa Python taas kaayo ug nagkinahanglan og dungan nga suporta sa duha ka sistema, nga nakaapekto sa disenyo sa database.

Ingon usa ka sangputanan, kami adunay usa ka database nga adunay daghang daghang konektado ug daghang mga lamesa nga adunay daghang mga indeks alang sa lainlaing mga klase sa pangutana. Ang tanan niini negatibo nga nakaapekto sa paghimo sa database: tungod sa dagkong mga lamesa ug usa ka hugpong sa mga relasyon tali kanila, ang pagkakomplikado sa mga pangutana kanunay nga nagdugang, nga labi ka kritikal alang sa labing puno nga mga lamesa.

Aron makunhuran ang load sa database, nakahukom mi nga magsulat og script nga magbalhin sa daan nga mga rekord gikan sa pinakadaghan ug puno nga mga lamesa ngadto sa mga na-archive (pananglitan, gikan sa task Π² task_archive).

Kini nga buluhaton komplikado sa daghang gidaghanon sa mga relasyon tali sa mga lamesa: ibalhin lang ang mga laray gikan task Π² task_archive dili igo, sa wala pa kana kinahanglan nimo nga buhaton ang parehas nga recursively sa tanan nga mga paghisgot task mga lamesa.

Akong ipakita uban ang usa ka pananglitan demo database gikan sa site postgrespro.ru:

Ang PgGraph usa ka gamit sa pag-archive ug pagpangita sa mga dependency sa lamesa sa PostgreSQL
Ingnon ta nga kinahanglan natong papason ang mga rekord gikan sa usa ka lamesa Flights. Ang mga postgres dili motugot kanamo sa pagbuhat niini nga ingon niana: kinahanglan una namon nga papason ang mga rekord gikan sa tanan nga mga reference nga mga lamesa, ug uban pa nga balikbalik ngadto sa mga lamesa nga wala gi-refer ni bisan kinsa.

Sa among pananglitan sa Flights nagpasabut Ticket_flights, ug sa iya- Boarding_passes.

Busa, kinahanglan nimo nga papason kini sa kini nga han-ay:

  1. Nakuha namon ang panguna nga mga yawe (PK) nga kantidad sa mga laray Ticket_flights, nga nagtumong sa mga laray nga papason Flights.
  2. Nakuha namo ang mga laray sa PK Boarding_passes, nga nagtumong sa Ticket_flights.
  3. Gitangtang namon ang mga laray sa PK gikan sa lakang 2 sa lamesa Boarding_passes.
  4. Pagtangtang sa mga linya pinaagi sa PK gikan sa lakang 1 sa Ticket_flights.
  5. Pagtangtang sa mga linya gikan sa Flights.

Ang resulta mao ang usa ka utility nga gitawag ug PgGraph, nga nakahukom kami nga himoong open source.

Giunsa paggamit

Ang utility nagsuporta sa duha ka paagi sa paggamit:

  • Tawag gikan sa command line (pggraph …).
  • Paggamit sa Python code (class PgGraphApi).

Pag-instalar ug pag-ayo

Una kinahanglan nimo nga i-install ang utility gikan sa Pypi repository:

pip3 install pggraph

Dayon paghimo og config.ini file sa lokal nga makina nga adunay configuration sa database ug ang archiving script:

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

Pagdagan gikan sa console

lantugi

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

Posisyon nga mga argumento:

  • action - gikinahanglan nga aksyon: archive_table, get_table_references o get_rows_references.

Ngalan nga mga argumento:

  • --config_path β€” dalan sa config file;
  • --table β€” usa ka lamesa diin kinahanglan nimo nga buhaton ang usa ka aksyon;
  • --ids β€” lista sa id nga gibulag sa mga koma, pananglitan, 1,2,3 (opsyonal nga parameter);
  • --log_path - agianan sa folder alang sa mga troso (opsyonal nga parameter, pinaagi sa default - folder sa balay);
  • --log_level - lebel sa pag-log (opsyonal nga parameter, ang default mao ang INFO).

Mga pananglitan sa sugo

Pag-archive sa usa ka lamesa

Ang nag-unang function sa utility mao ang data archiving, i.e. pagbalhin sa mga laray gikan sa main table ngadto sa archive table (pananglitan, gikan sa table mga libro Π² books_archive).

Ang pagtangtang nga walay pag-archive gisuportahan usab: alang niini kinahanglan nimo nga ibutang ang parameter sa config.ini to_archive = bakak).

Gikinahanglan nga mga parameter - config_path, lamesa ug id.

Pagkahuman sa paglansad, ang mga rekord matangtang sa balikbalik nga paagi ids sa lamesa table ug sa tanang mga lamesa nga nagtumong niini.

$ 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

Pangitaa ang mga dependency alang sa usa ka piho nga lamesa

Function sa pagpangita sa mga dependency sa usa ka piho nga lamesa table. Gikinahanglan nga mga parameter - config_path ΠΈ table.

Pagkahuman sa paglansad, usa ka diksyonaryo ang ipakita sa screen, diin:

  • in_refs β€” usa ka diksyonaryo sa mga lamesa nga nagpunting sa usa ka gihatag, diin ang yawe mao ang ngalan sa lamesa, ang kantidad usa ka lista sa mga butang sa Langyaw nga Key (pk_main - pangunang yawe sa main table, pk_ref - nag-unang yawe sa reference table, fk_ref β€” ang ngalan sa kolum nga mao ang langyaw nga yawe sa gigikanan nga lamesa);
  • out_refs β€” usa ka diksyonaryo sa mga lamesa nga gitumong niini.

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

Pagpangita mga pakisayran sa mga kuwerdas nga adunay gipiho nga Primary Key

Function sa pagpangita sa mga laray sa ubang mga lamesa nga nagtumong sa mga laray pinaagi sa Foreign Key ids mga lamesa table. Gikinahanglan nga mga parameter - config_path, table ΠΈ ids.

Pagkahuman sa paglansad, ang usa ka diksyonaryo nga adunay mosunud nga istruktura ipakita sa screen:

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

Pananglitan nga tawag:

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

Paggamit sa code

Dugang sa pagpadagan niini sa console, ang librarya mahimong gamiton sa Python code. Ang mga pananglitan sa mga tawag sa iPython interactive nga palibot gipakita sa ubos.

Pag-archive sa usa ka lamesa

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

Pangitaa ang mga dependency alang sa usa ka piho nga lamesa

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

Pagpangita mga pakisayran sa mga kuwerdas nga adunay gipiho nga Primary Key

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

Ang library source code anaa sa GitHub ubos sa lisensya sa MIT, ingon man sa repositoryo PyPI.

Malipay ako sa mga komento, pagpasalig ug mga sugyot.

Paningkamutan nako nga tubagon ang mga pangutana kutob sa akong mahimo dinhi ug sa repository.

Source: www.habr.com

Idugang sa usa ka comment