PG Day Russiaã«ã³ãã¡ã¬ã³ã¹ã¯æ¡å€§ããèŠæš¡ãæ¡å€§ããŠããŸããä»å¹Žã®å€ãæã人æ°ã®ãããã¹ãŠã®ããŒã¿ããŒã¹ãããã³ç®¡çãšããŒã¿ã¹ãã¬ãŒãžã«é¢ããã¬ããŒããšãã¬ãŒãã³ã°ãæºåããŠããŸãã æºåãšããŠãããŒã¿ããŒã¹ã®äžçã§äœãèµ·ãã£ãŠãããã«ã€ããŠã®è²Žéãªæ å ±ãå ±æããäºå®ã®äŒæ¥ããã°ãç«ã¡äžããŸããã æåã®æçš¿ã¯PostgreSQLã®éçºããŒã«ã«æ§ããããŠããããã®èè varanioã¯ããªãã®è³ªåãã³ã¡ã³ãã«åãã§ãçãããŸãïŒ
ããŸããŸãªPostgreSQLé¢é£ã®äººã
ã«ãPGã䜿çšããŠã¢ããªã±ãŒã·ã§ã³ãéçºããããã«å®éã«äœ¿çšããŠãããã®ãå°ããŸããã
ãã¡ãããããã¯å³å¯ãªæ°åŠçãµã³ãã«ã§ã¯ãããŸããããããã§ããããã§ã«ããã«ãµããããè³ã®æ¥œåšã®ãªã¹ããååŸããŸãããããã¯ãã®èšäºã§èª¬æããŸãã
ããªãã®ããŒã«ããªã¹ãã«ãªãå ŽåããŸãã¯ããªããäœãèšãããããšãããã°ãã³ã¡ã³ãããŠãã ããã
ãããã£ãŠãããã«éå ¬åŒã®ãããããã³äž»èŠ³çãªèª¬æããããŸãã
psql
ããããpsqlã§ãããããã¯é©ãããšã§ã¯ãããŸããã ã«ã©ã·ãã³ãçªæã©ã€ãã«ãšããŠãä¿¡é Œã§ãããç¡æã§ãç®±ããåºããŠã幞çŠã®ããã«ä»ã«äœãå¿
èŠã§ããïŒ ã¯ãšãªãç·šéããã«ã¯ãEDITORç°å¢å€æ°ã§æå®ããããšãã£ã¿ãŒã䜿çšããŸããéåžžãvimãnanoãªã©ã䜿çšããŸãã ãŸããäžè¬çã«ãpsqlã¯unix-wayãã€ãŸã ãšãã£ã¿ãŒãããŒãžã£ãŒã§çµæã衚瀺ããŠå®è¡ãããã€ããä»ããŠå
¥åã«sql-requestãéä¿¡ããå¿
èŠãªå Žæã«åºåãéä¿¡ã§ããŸãã
ãã€ãã¹ã®ãã¡ã匱ããªãŒãã³ã³ããªãŒããããã³\d
\dt+
\sf
ã·ãªãŒãºãªã©ã®çŽæçã§ãªãã³ãã³ããèšæ¶ããªããã°ãªããªããšããäºå®ã«æ³šæããããšãã§ããŸãã ïŒãã ãããã¹ãŠã®ã³ãã³ãã®èª¬æã¯\?
ã³ãã³ãã§å©çšã§ããŸãïŒ
ãŸããã³ã³ãœãŒã«ãšæ°Žæ³³ã§åããŠããŸã-äœããã®çç±ã§ããã¯èª°ã«ãé©ããŠããŸãã:)
å®éã«ã¯ãå·ŠåŽã®ã©ããã«ããŒãã«/ãã¥ãŒã®å®å šãªãªã¹ãããããå³åŽã®ãã®ãã¯ãªãã¯ããŠãããã«äœããããã確èªãããå ŽåããããŸãã ã€ãŸã å°ãªããšãããã€ãã®GUIã psqlã§ã®äœæ¥ã¯ãå¹æçã§ãããäžåºŠã«1ã€ã®ãªããžã§ã¯ãã®ã¿ãç §ããå°ããªæäžé»ç¯ã®ããæãéšå±ã§ã®äœæ¥ãæãèµ·ããããŸãã
ããŒã¿ã°ãªãã
ããŒã¿ããŒã¹çšã®IDEã 補åã¯æ¯èŒçæ°é®®ã§ããããã§ã«åºã䜿çšãããŠããŸãã äž»ã«ãJetBrainsã®å€§äººæ°è£œåã§ããIntelliJ IDEAãPyCharmãPhpStormãªã©ã«ããã«çµã¿èŸŒãŸããããã§ãã
å®éããã®çµã¿èŸŒã¿æ©èœã¯è£œåã®äž»èŠãªãã©ãŒæ©èœã§ããããŸããããšãã°ãphp-codeãç·šéãããšãsql-queryã®è¡ããããçªç¶IDEãïŒã³ãŒãå ã§ïŒSQLæ§æãååãéç¥ããããšãããããŸããããŒãã«ãšãã®ãã£ãŒã«ãã¯ãäœããééã£ãŠèšè¿°ãããŠããå Žåã¯èµ€ã匷調ããSQLãªã©ããã©ãŒãããããŸãã ãã¡ãããåãIDEã§ãããŒã¿ããŒã¹çšã®ä»ã®GUIã§ã§ããããšãå®è¡ã§ããŸããããŒãã«ãä»ã®ãšã³ãã£ãã£ã®ãªã¹ãã衚瀺ããããã¯ãšãªãåå¥ã«äœæããããç°ãªã圢åŒã«ããŒãã«ããšã¯ã¹ããŒãããããªã©ã
æ©èœã®ãã¡ã次ã®ããšã«æ³šæããŠãã ããã
- è€æ°ã®æ¿å ¥ãéžæããŠã[è¡šãšããŠç·šé]ãã¯ãªãã¯ã§ããŸãïŒå³ãåç §ïŒã 次ã«ãSQLæ§æã®ä»£ããã«äŸ¿å©ãªè¡šåœ¢åŒã§ç·šéããåãå Žæã§è¡ãåã®è¿œå ãcsvãžã®ãšã¯ã¹ããŒããªã©ãè¡ãããšãã§ããŸãã
- 2ã€ã®ã¯ãšãªã®çµæãæ¯èŒã§ããŸãã ããã¯ãäœãå£ããã«è€éãªã¯ãšãªãåçŽåããããšããå Žåã«äŸ¿å©ã§ãã
- çµã¿èŸŒã¿ã³ãŒãã¯å®å šã«ã¯éçºãããŠããŸããã ããšãã°ãã€ã³ã¿ãŒãã§ã€ã¹ã®ããŒãã«åã®ååãå€æŽãããšãIDEã¯ã³ãŒãå ã§å¿ èŠãªSQLè¡ãèŠã€ããŸããïŒãããã®è¡ã§ãªãŒãã³ã³ããªãŒããæ©èœããŠããéïŒããŸãã¯ãã®éã¯ãããçš®ã®ãã³ã»ã³ã¹ãèŠã€ããŸãã
- èŠèŠçãªçºéã¯ããŸããããŸããã ã€ãŸã ããŒãã«ãäœæã§ããŸããããã¥ãŒã¯äœæã§ããŸããã ããŒãã«ã«å€éšããŒãæã€IDïŒããšãã°ãç¹å®ã®ãã£ã¯ã·ã§ããªãžã®ãªã³ã¯ïŒãå«ãŸããŠããå Žåãèå¥åãå ¥åããã®ã§ã¯ãªããããŒãã«ã«ããŒã¿ãå ¥åãããšãã«ãã£ã¯ã·ã§ããªããå€ãéžæããŸãã
- ã¹ããŒã ã®ããããã®ããŒãã«ãèŠããšãDatagripã¯search_path = circuit nameãèšå®ãããªã¯ãšã¹ããéä¿¡ããŸããããã¯ãpgbouncerã䜿çšããããšæªãçµæã«ã€ãªãããŸãïŒãããŠãPHPã®å ŽåããŸãã¯å€ãã®ãµãŒããŒãããå Žåãã»ãšãã©åžžã«äœ¿çšãããŸãïŒãç°ãªãæ¥ç¶ã䜿çšããããšããå§ãããŸããã³ãŒããæ©èœããããã«-pgbouncerãä»ããŠãideã䜿çšããŠ-ããŒã¿ããŒã¹ã«çŽæ¥æ¥ç¶ããŸãã
Datagripã¯ç©æ¥µçã«éçºãé²ããŠãããç¹ã«ãæ§æã®åŒ·èª¿è¡šç€ºã«é¢ããããã€ãã®è¿·æãªãã°ãä¿®æ£ãããŠããŸãã
å šäœçã«åªããææ°ã®ããŒã«ã§ããããšãæšå¥šãããŸãã
pgAdmin

å€ãã®äººã ãããã䜿çšããŸããããããç¿æ £ã§ã¯ãããŸããã ãŸãã¯ç¡æã ããã§ãã pgAdmin4ã¯å¥åŠãªè£œåã§ããã説æã§ã¯éçºãšç®¡çã«æé©ãªãªãŒãã³ãœãŒã¹è£œåã§ãããšèª¬æãããŠããŸãã
管çã«äœ¿çšããæ¹æ³ã¯ããŸãæ確ã§ã¯ãããŸããã pgAdmin'omã¯æ°ãããµãŒããŒããåŒã³åºããããšã¯ã§ããŸãããpg_hba.confãŸãã¯postgresql.confãä¿®æ£ããããšã¯ã§ããŸããã ã©ããããããã¯ã1ç§ãããã®ããããªã¯ãšãªã¹ã±ãžã¥ãŒã«ããµãŒããŒæ§æã®è©³çŽ°ããã³ããŒãã«å ã®çµ±èšã®åºåãæããŸãã äžè¬çã«ã¯ããããŸããã 管çã«pgAdminãã©ã®ããã«äœ¿çšããŸããïŒ
éçºã®èŠ³ç¹ããããã䜿çšããæ¹æ³ã¯ããã«æ確ã§ã¯ãããŸããã 䞻芳çã«ã¯ãã€ã³ã¿ãŒãã§ãŒã¹å šäœã¯éçºã«äŸ¿å©ã§ã¯ãããŸããã 4çªç®ã®ããŒãžã§ã³ãpython + JSãšjQueryã§æžãçŽããããšããäºå®ã«ãããããããæ¬è³ªçã«åãããšãæ®ã£ãŠããŸãã
ç¶æ³ãå°ãæ確ã«ããããã«ãéçºè ã®é ã®äžã®åçïŒãµãŒããŒã«åºç€ãããããã®äžã«å³ããããå³ã«è¡šãšãã¥ãŒããããŸãã ã€ãŸã ããŒãã«-æ倧ã第3ã¬ãã«ã ãããŠãããŒã¹ã1ã§ããå Žåãéåžžã¯2çªç®ã®ã¬ãã«ã§ãã ããŒãã«ã«çªã£èŸŒãã -æåã®æ°è¡ãèŠãã
pgAdminéçºè ã®é ã®äžã§ã¯ããéã®å ã§ã®ã³ã·ã§ãšãã®æ»ãåµã®äžã®ãã®éããããŠã¢ãã«ã®åµããããã®ãã²ããèžã®ããããèžã¯é«ããªãŒã¯ã®æšã«ãããã³ã·ã§ã€ã®æšã¯ããªãèªèº«ã®ç®ã®ãããªãã®ã§ããä¿åããã€ãŸãïŒç»åãåç §ïŒïŒ
ãµãŒããŒã®ã°ã«ãŒããããããµãŒããŒãããããµãŒããŒäžã«ããŒã¹ãããŒã«ãªã©ããããŸããããŒã¿ããŒã¹ããç¹å®ã®ããŒã¿ããŒã¹ãéžæã§ããå³ãèšèªãç¥ãç¥ã£ãŠãããã®ãèŠãããšãã§ããŸãã ã¹ããŒã ã§ã¯ãç®çã®ã¹ããŒã ãéžæã§ããã¹ããŒã 100500ã§ã¯ãã¹ãŠããããŠãããŒãã«ãã®ãªã¹ãã®æåŸã®ã©ããã§éžæã§ããŸãã ããŒãã«ã§ã¯ãç®çã®ããŒãã«ãéžæããŠå³ã¯ãªãã¯ãã倧ããªãªã¹ãã§ãããŒã¿ã衚瀺ããéžæã§ããŸãããã®ãããŒã¿ã衚瀺ãã«ã¯ãæåã®100è¡ã衚瀺ããæåŸã« ã³ã·ã§ãšãã®æ» åèã®ããã«æ°è¡ã
pgAdminã®ãã©ãŒæ©èœã¯ãpl / pgsqlã¹ãã¢ãããã·ãŒãžã£ããããã°ããæ©èœã§ãã ãã®æ©èœãåããä»ã®ç¡æããã°ã©ã ã¯èŠãããšããããŸããã
EMSã¹ã¿ãžãª
EMS Studioã¯Windowsã§ã®ã¿åäœããããã§ãã ãåç¥ã®ããã«ãPostgreSQLã¯Windowsã§äœ¿çšãããããšã¯ã»ãšãã©ãªãããããããäž»ãªæ¬ ç¹ã§ãã
ç§ã¯ãã®ãœãããŠã§ã¢ãWineã§äžåºŠããèŠãªãã£ããããééã£ãŠããå¯èœæ§ããããŸããããäžè¬çã«ã¯æ°ã«å ¥ããªãã£ãã§ãã äžå¯è§£ãªã¢ã€ã³ã³ãäžæçãªã€ã³ã¿ãŒãã§ã€ã¹ã®å¿ æ»ã®å±±ã ã¡ãªã¿ã«ãWineã§ã¯ããŒã«ãããããªãã«ãªãããåçããæ©èœãæšæž¬ããŸããããã¬ã€ããŸããã ãšãŠãé£ããã
äœããã®çç±ã§ãããžã¥ã¢ã«ã¯ãšãªãã¶ã€ããããŒãçšã«äœæãããŸãã ããã¹ãwhere id = 5
代ããã«ãããŠã¹ã§ããã€ãã®ãã¿ã³ãã¯ãªãã¯ããããããããŠã³ãªã¹ãããéžæããå¿
èŠããããŸãã SQLãç¥ã£ãŠãã人-ããã¯å¿
èŠã§ã¯ãããŸãããç¥ããªã人-ããã¯å©ãã«ã¯ãªããŸããã
䟿å©ãšåŒã°ããæ©èœïŒãšã€ãªã¢ã¹ã®èªåè£å®ãSQL圢åŒã§ã®ã¯ãšãªçµæã®ãšã¯ã¹ããŒãïŒæ¿å ¥ïŒãããŒã¿ããŒã¹ã®ãšã¯ã¹ããŒãã«äŸ¿å©ãªGUIãSQLã®éžæããéšåã®ã¿ãå®è¡ããæ©èœã
pl / pgsqlã®ãããã°ãå¯èœã äžè¬çã«ãã§ããããšã¯ãããããããŸãããä»ã®æ©èœãšåºå¥ã§ãããããªåªããæ©èœã«ååãä»ããããšã¯ã§ããŸããã
NAVICAT
Navicatã¯ããããæãæ©èœãè±å¯ãªããã°ã©ã ã§ãã 圌女ã¯ãããŒã¿ããŒã¹ã®ä»ã®GUIãã§ããããšããã¹ãŠç¥ã£ãŠããŸãïŒãªããžã§ã¯ããã¶ã€ããŒãããŒãã«ãã¥ãŒã¢ãŒããªãŒãã³ã³ããªãŒããããŒã¿ããŒã¹èšèšããŒã«ãpl / pgsqlãããã°ãã€ã³ããŒã/ãšã¯ã¹ããŒããªã©ã
ã»ãšãã©ãã¹ãŠã®OSã§åäœããçã«å æ¬çãªãœãããŠã§ã¢ã 䜿ãããããã¯ããã«äŸ¿å©ãªEMSã¹ã¿ãžãªã
ç§ã®æèŠã§ã¯ããã©ãŒæ©èœã¯ããŒã¹ã®æ¯èŒã§ãã ã€ãŸã 2ã€ã®ããŒã¿ããŒã¹ãååŸããæ§é ãã©ã®ããã«ç°ãªããã確èªããåæã®ããã®ã¯ãšãªãäœæã§ããŸãã
ãããã圌ããèšãããã«ãäŸ¡æ Œã¯ã銬ããEMSã®2åé«äŸ¡ã§ãã ããããããã§ã¯ãå®å šã«æ£åœåãããŠããããã§ãã
PGCLI
psqlã䜿çšããããšãã人ã¯ãããã«è±å¯ãªæ©èœïŒèªåè£å®ãªã©ïŒã倢èŠå§ããŸãã ãããã®ãŠã£ãã·ã¥ãªã¹ãã®å®è£
ã«ã¯ãpgcliããããŸãã
pgcliã¯ãããŒã¯ãŒããé¢æ°ãããŒãã«ãåãã¢ãªã¹ã®åãèªåè£å®ã§ããŸãã æ§æã匷調衚瀺ããããå¥ã®ãšãã£ã¿ãŒã䜿çšããã«è€æ°è¡ã¢ãŒãã§SQLãç·šéãããã§ããŸãã
èŠããã«ãpgcliã¯æ¬è³ªçã«ã¹ããã€ãã®psqlã§ãã
phppgadmin
MySQLãã移è¡ãã人ã®å€ãã¯ãæ¬èœçã«phpmyadminã®é¡äŒŒç©ãæ¢ããphppgadminãèŠã€ããŸãã æ®å¿µãªãããphppgadminã¯æ°å¹ŽééçºãããŠããªãã®ã§ãæ»ãã§ãè¯ããšããŠããäœããããŸããã äžè¬ã«ãäœãèšããªãã§ãã ããã
ãŸãšã
ãããã®ããŒã«ã®ãã¹ãŠãç§ã«çµéšãããããã§ã¯ãªãã®ã§ãã³ã¡ã³ãã«ã³ã¡ã³ãããŠãã ããã äœã䜿ã£ãŠããŸããïŒ
ãŸããpostgresã ãã§ãªããä»ã®ããŒã¿ããŒã¹çšã®äžè¬çãªããŒã«ã®éçºè ãpgdayäŒè°ã«åå ãã質åã§èŠãã¿ãæ©èœã«é¢ããèŠæãè¿°ã¹ãããšãã§ããããšã«æ³šæããå¿ èŠããããŸãã ãããã«ããã7æ5ã7æ¥ã«ãµã³ã¯ãããã«ãã«ã¯ã§éå¬ããããã®æãæçšãªã€ãã³ãã«ãå šå¡ãæåŸ ããŸãã