ProHoster > Π‘Π»ΠΎΠ³ > Maamulka > PgGraph waa utility kaydinta iyo helida ku tiirsanaanta miiska PostgreSQL

PgGraph waa utility kaydinta iyo helida ku tiirsanaanta miiska PostgreSQL

PgGraph waa utility kaydinta iyo helida ku tiirsanaanta miiska PostgreSQL
Maanta waxaan rabaa inaan u soo bandhigo akhristayaasha Habr oo wata qalab ku qoran Python si ay ula shaqeeyaan ku tiirsanaanta miiska PostgreSQL DBMS.

API utility waa mid fudud wuxuuna ka kooban yahay saddex hab:

  • miiska_ kaydka - kaydinta/tirtirka safafka leh furayaasha aasaasiga ah ee la cayimay
  • hel_tixraacyada_miiska - raadi ku tiirsanaanta miis (waxay tusi doontaa jaantusyada laga tixraacay midka la cayimay iyo kuwa tixraacaya)
  • hel_tixraacyo_safka - Ka raadi safafka miisaska kale ee tixraacaya safafka la cayimay ee shaxda la rabo

prehistory

Magacaygu waa Oleg Borzov, waxaan ahay horumariye ka tirsan kooxda CRM ee maamulayaasha amaahda amaahda guryaha ee Domklik.

Xogta ugu weyn ee nidaamkayaga CRM waa mid ka mid ah kuwa ugu weyn marka loo eego mugga shirkadda. Sidoo kale waa mid ka mid ah kuwa ugu da'da weyn: waxay ka soo muuqatay bilawga mashruuca, markii geeduhu weynaayeen, Domklik wuxuu ahaa bilawga, iyo halkii microservice ee qaabka Python asynchronous ee moodada ah waxaa jiray monolith weyn oo PHP ah.

Ka gudubka PHP una gudubtay Python aad buu u dheeraa wuxuuna u baahday taageero isku mar ah labada nidaam, taas oo saamaysay naqshadaynta xogta.

Natiijo ahaan, waxaan haysanaa xog ururin leh tiro badan oo aad isugu xiran oo miisas waaweyn leh oo leh farabadan tusiyaal noocyo kala duwan ah. Waxaas oo dhan waxay si xun u saameeyaan waxqabadka xogta xogta: sababtoo ah miisaska waaweyn iyo xidhidhka u dhexeeya iyaga, kakanaanta su'aalaha ayaa si joogto ah u kordhaya, taas oo si gaar ah muhiim ugu ah miisaska ugu badan ee la raro.

Si loo dhimo culayska ku jira kaydka xogta, waxaanu go'aansanay inaanu qorno qoraal ka soo wareejinaya diiwaanadii hore ee miisaska ugu mug iyo rarnaa una gudbiya kuwa kaydsan (tusaale, ka task Π² task_archive).

Hawshani waxay ku adag tahay tirada badan ee xidhiidhada u dhexeeya miisaska: si fudud uga guuri safafka task Π² task_archive kuma filna, intaa ka hor waxaad u baahan tahay inaad si la mid ah u sameyso si isdaba joog ah dhammaan tixraacyadaas task miisaska.

Waxaan ku muujin doonaa tusaale Xogta demo ee goobta postgrespro.ru:

PgGraph waa utility kaydinta iyo helida ku tiirsanaanta miiska PostgreSQL
Aynu nidhaahno waxaan u baahanahay inaan ka tirtirno diiwaanada miiska Flights. Postgres noo ogolaan mayso inaan sidan oo kale samayno: waxaan marka hore u baahanahay inaan tirtirno diiwaanada dhamaan miisaska tixraaca, iyo wixii la mid ah si isdaba joog ah ilaa miisaska aan cidna tixraacin.

Tusaalahayaga at Flights waxaa loola jeedaa Ticket_flightsiyo iyada - Boarding_passes.

Sidaa darteed, waxaad u baahan tahay inaad u tirtirto sidatan:

  1. Waxaan helnaa furayaasha aasaasiga ah (PK) qiimayaasha safafka Ticket_flights, kuwaas oo tixraacaya safafka lagu tirtirayo Flights.
  2. Waxaan helnaa safafka PK Boarding_passes, kuwaas oo tixraacaya Ticket_flights.
  3. Waxaan ka tirtirnaa safafka PK tallaabada 2 ee shaxda Boarding_passes.
  4. Ka tirtir khadadka PK ka tilaabada 1 in Ticket_flights.
  5. Ka saarida khadadka Flights.

Natiijadu waxay ahayd utility loo yaqaan PgGraph, kaas oo aanu go'aansanay inaanu samayno ilo furan.

Sida loo isticmaalo

Utility wuxuu taageeraa laba nooc oo isticmaalka:

  • Ka wac khadka taliska (pggraph …).
  • Isticmaalka koodka Python (class PgGraphApi).

Rakibaadda iyo qaabeynta

Marka hore waxaad u baahan tahay inaad ku rakibto utility-ga kaydka Pypi:

pip3 install pggraph

Ka dib ku samee faylka config.ini mashiinka deegaanka oo leh qaabeynta xogta iyo qoraalka kaydinta:

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

Ka orod konsole

xuduudaheedu

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

Doodaha mawqifka ah:

  • action - ficil loo baahan yahay: archive_table, get_table_references ama get_rows_references.

Doodaha la magacaabay:

  • --config_path - jidka loo maro faylka config;
  • --table - miiska oo aad u baahan tahay inaad ku qabato ficil;
  • --ids - liiska id ay u kala qaybsan yihiin hakad, tusaale ahaan, 1,2,3 (halbeegga ikhtiyaariga ah);
  • --log_path - Jidka loo maro galka diiwaanka (xaddiga ikhtiyaariga ah, sida caadiga ah - galka guriga);
  • --log_level - heerka goynta (halbeegga ikhtiyaariga ah, default waa INFO).

Tusaalooyinka amarka

Kaydinta miis

Shaqada ugu weyn ee utility waa kaydinta xogta, i.e. ka wareejinta safafka miiska weyn una wareejinaya miiska kaydka (tusaale, miiska buugaag Π² kayd_buugaagta).

Tirtir la'aanta kaydinta sidoo kale waa la taageeray: tan waxaad u baahan tahay inaad dejiso cabbirka config.ini to_archive = been).

Xuduudaha loo baahan yahay - config_path, miiska iyo ids.

Bilawga ka dib, diiwaanada si isdaba joog ah ayaa loo tirtiri doonaa ids miiska table iyo dhammaan jaantusyada tilmaamaya.

$ 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

Soo hel waxyaalaha ku tiirsanaanta miis cayiman

Shaqada lagu helo ku tiirsanaanta miis cayiman table. Xuduudaha loo baahan yahay - config_path ΠΈ table.

Daah-furka ka dib, qaamuuska ayaa lagu soo bandhigi doonaa shaashadda, halkaasoo:

  • in_refs - qaamuuska shaxanka tixraacaya mid la bixiyay, halkaasoo furuhu yahay magaca miiska, qiimuhu waa liiska walxaha furaha shisheeye (pk_main - furaha aasaasiga ah ee miiska weyn, pk_ref - furaha aasaasiga ah ee miiska tixraaca, fk_ref - magaca tiirka kaas oo ah furaha shisheeye ee miiska isha;
  • out_refs - Qaamuuska miisaska middani waxa ay tilmaamaysaa.

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

Helitaanka tixraacyada xargaha leh furaha aasaasiga ah ee la cayimay

Shaqada lagu raadiyo safafka shaxanka kale ee tixraacaya safafka loo maro Furaha Shisheeye ids miisaska table. Xuduudaha loo baahan yahay - config_path, table ΠΈ ids.

Daah-furka ka dib, qaamuuska qaabkan soo socda ayaa lagu soo bandhigi doonaa shaashadda:

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

Tusaale ahaan wac:

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

Isticmaalka koodka

Marka laga soo tago in lagu dhex wado console-ka, maktabadda waxaa loo isticmaali karaa koodka Python. Tusaalooyinka wicitaanada ee deegaanka isdhexgalka iPython ayaa lagu muujiyay hoos.

Kaydinta miis

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

Soo hel waxyaalaha ku tiirsanaanta miis cayiman

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

Helitaanka tixraacyada xargaha leh furaha aasaasiga ah ee la cayimay

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

Koodhka isha maktabadda ayaa laga heli karaa GitHub hoos shatiga MIT, iyo sidoo kale in kaydka PyPI.

Waan ku farxi doonaa faallooyinka, ballan-qaadyada iyo soo-jeedinta.

Waxaan isku dayi doonaa inaan ka jawaabo su'aalaha sida ugu fiican ee aan awoodo halkan iyo kaydka.

Source: www.habr.com

Add a comment