467メモ

コンピュータのことや旅行記など趣味のことたくさんかきます。

ネスペ自己解説(2)


自己(事故)アドベントカレンダー
情報処理技術者試験ネットワークスペシャリスト試験に無事合格できたので,自分のためにもネスペの問題の自己解説を晒していきます.
信頼度0.01ぐらいなので,参考にはしないでください。

問題:https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2018h30_2/2018h30a_nw_pm2_qs.pdf
解答例:https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2018h30_2/2018h30a_nw_pm2_ans.pdf


午後2問1

~問題文の流れ~
Y社ではDCを運営しホスティングサービスを提供している。

新規顧客へのサービスの提供やサーバの増設を迅速に行えるようにするとともに導入コストや運用コストを削減してサービスの収益性を高める目的で、サービス基盤の構築を決定。サービス基盤ではマルチテナメント方式のIaaSを提供するようにする。

・従来の技術を用いて構築するか(従来方式)
・SDN技術を用いて構築するか(SDN方式)

サービス基盤を構築する場合や顧客が増減した場合の作業内容などを比較して構築方式を決めることにしよう!

設問1

(1)本文中のア~エに入れる適切な字句を答えよ。

ア.スタック
二つのL2SWをスタック接続すると一つのL2SWとして動作できるようになります。
イ.ステートフル
FW製品にはステートフルフェールオーバという機能が備わっています。
ステートフルフェールオーバ機能によって、アクティブFWからスタンバイFWへ突然切り替わっても、セッション情報を維持することができます。
ウ.負荷分散
LBではクラスタグループごとに仮想IPアドレスと負荷分散アルゴリズムが設定できます。
エ.チーミング
サーバにNICを2枚実装し、チーミング機能を利用すると2枚のNICをアクティブ/アクティブの状態にすることができます。
ーーーーー
アクティブ/アクティブの表現に惑わされて(うそですちゃんと理解してなかった自分が悪い)デュアルスタックってかいた。デュアルスタンバイ!ェ

設問2

(1)本文中の下線①の要件が必要になる理由を,30字以内で述べよ。

顧客ごとに異なるフィルタリングの設定が必要であるから
顧客ごとにルーティングの設定が必要であるから
下のように各顧客ごとにFWを介していたりLBを介していたりするシステムを
f:id:tn-mi:20181217234355j:plain
下のようにマルチテナント方式にします。
f:id:tn-mi:20181217234457j:plain
この時になぜ装置の中に複数の仮想FWを稼働させなくてはならないのかという問題です。
FWを介すシステムを構築している顧客は、マルチテナント方式になっても固有のフィルタリングルールがないと困りますよね(?)
また問題文に「(2)サービス基盤で稼働する顧客システムは、顧客ごとに論理的に独立させること」とあるように、顧客ごとにフィルタリングルールが必要なことがわかります。

(2)本文中の下線②の機能について,アクティブのFWをFWaからFWbに切り替えるのに,FWa又はFWbが監視する内容を三つ挙げ,図2中の機器名を用いて,それぞれ25字以内で答えよ。

以下のうちのいずれか3つ
FWbによるFWaの稼働状態
FWaによるL2SWaへの接続ポートのリンク状態
FWaによるLBaへの接続ポートのリンク状態
FWaによるFWbの稼働状態
FWbによるL2SWbへの接続ポートのリンク状態
FWbによるLBbへの接続ポートのリンク状態
FWaと、L2SWa,LBa,FWb間のリンクが切れた場合アクティブのFWをFWbに切り替える必要があります。
ーーーーーーーーー
L2SW-FW間の接続状態
FWとLB間の接続状態
FWaとFWb間の接続状態
ってかいた。あっていたんですかね。。。

(3)本文中の下線③について、VLANを設定するポート及び設定するVLANの内容を,50字以内で具体的に述べよ。

物理サーバへの接続ポートに,すべての顧客の仮想サーバに設定されたVLANIDを設定する。
従来方式によるサービス基盤の構築案です。
f:id:tn-mi:20181220000036j:plain
L2SWcとL2SWdの間はスタック接続で,物理サーバのNICチーミングにしてリンクアグリゲーションが組まれています。なので上の図を簡単にまとめると以下のようにあらわせます。
f:id:tn-mi:20181220011244j:plain
例えばここでいう物理サーバ1にあるP社サーバ群が物理サーバnへ移動する場合に必要となるVLAN設定(ポート・VLAN内容)はなにかという問題です。

物理サーバ1にあるP社サーバ群が物理サーバnへ移動する場合,L2SW①の物理サーバ1への接続ポートにP社サーバ群のVLANIDを設定していたものを,物理サーバnへの接続ポートに変更しなくてはいけません。
変更をしないとL2SW①はP社サーバ群のVLANIDがわからず、P社サーバ群宛のパケットを物理サーバnへ送ることができません。(もしくは変更前の物理サーバへ送ってしまう)
なので物理サーバ間の移動と同時に、「物理サーバへの接続ポートにすべての顧客の仮想サーバに設定されたVLANIDを設定する。」が正解です。
VLAN設定するときに

Switch# configure terminal
Switch(config)# interface ポート番号
Switch(config-if)# switchport trunk allowed vlan VLANID

という感じのことをしますよね。これと同様のことをL2SW①にするんだと思います。
ーーーーーーーーーーー
物理ごとに顧客の仮想サーバ分散するのかなあって思って
”物理サーバが接続されている各L2SWのポートに各顧客同一のNWになるようなタグVLAN"
って答えました。アホだなあ

設問3

本文中の下線④の情報を,15字以内で答えよ。

以下のうちいずれか一つ
・OFCのIPアドレス
・自OFSのIPアドレス
OFSは起動するとOFCとの間でTCPコネクションを確立します。このTCPコネクションの確立に必要な最小限の情報は何かという問題です。
TCPコネクションの確立⇒3ウェイハンドシェイクをするのですが,OFSは最低OFCのIPアドレスを持っていればSYNメッセージを送ることができます。
ーーーーーーーーーーー
ポート番号も必要なのでは...って思ったのですけれど、
ポート番号はnetstatコマンドとかtelnetコマンドとかを使ってひたすら調べるんですかね。
わかりません。

設問4

(1)表1中の項番2について,従来方式の場合,FWでは複数の仮想FWを設定することになる。仮想FWの設定に伴って,各仮想FWに対して設定が必要なネットワーク情報を三つ挙げ,それぞれ15字以内で答えよ。

以下のうちのいずれか3つ
・フィルタリングルール
・仮想FWのVLANID
・仮想FWのIPアドレス
・仮想FWのサブネットマスク
・仮想FWの仮想MACアドレス
・ルーティング情報
従来方式で,各仮想FWに必要なNW情報はなにかという問題です。
問題文「サービス基盤で稼働する顧客システムは,顧客ごとに論理的に独立させること」から
各顧客ごとのフィルタリングルール,VLANID,IPアドレスなどが必要なのがわかります。
ーーーーーーーーーー
この問題意味がわからなかった。
VLANIDとLBへつなぐための仮想IPアドレス~(GW)よくわからんことをかいた。

(2)表1中の項番3について,従来方式の場合,追加する顧客に対応したVLAN設定がサービス基盤の全ての機器及びサーバで必要になる。その中で,ポートVLANを設定する箇所を,図2中の名称を用いて,40字以内で答えよ。

顧客のL2SW又はL3SWに接続する,L2SWa及びL2SWbのポート
従来方式で,ポートVLANを設定する箇所はどこかという問題です。
f:id:tn-mi:20181220131429j:plain
(この解説,自信ないです)
L2SWaとL2SWbは各顧客システムへの分岐点となっています。
顧客間で通信することはなく,基本的にポートが顧客によって固定のため、ポートVLANです(?)
ーーーーーーーー
よくわからんことかいた...

設問5

(1)本番システムにおいて,図4の形態で3顧客の仮想サーバを配置した場合に発生する可能性がある問題を,40字以内で述べよ。また,その問題を発生させないための仮想サーバの配置を,40字以内で述べよ。

(発生する可能性がある問題)物理サーバ3の障害によって,3顧客のシステムが同時に停止してしまう。
(仮想サーバの配置)3顧客向けの仮想サーバを,それぞれ異なった物理サーバに配置する。
図4の形態で3顧客の仮想サーバを配置した場合,発生する可能性がある問題はなにかという問いです。
f:id:tn-mi:20181220144649j:plain
物理サーバ3にある
FWp,LBpはP社のもの,FWqはQ社のもの,LBzはZ社のものです。
例えばこの物理サーバ3が物理的に破壊されたり⚡燃やされたり🔥浸水したり🌂してしまったら,P社Q社Z社の3顧客のシステムが同時に停止してしまいます。
対策としてはこれら装置を物理サーバ3台に分散させて配置すれば解決します。

(2)表8のFテーブル4中には,FWpの内部側のポートからLBpの仮想IPアドレスをもつポートに,パケットを転送させるためのFエントリが生成されない。当該Fエントリが無くてもFWpとLBp間の通信が行われる理由を,70字以内で述べよ。

FWpの内部側ポートとLBpの仮想IPアドレスをもつポートは,同一セグメントであり,物理サーバ3内で処理されるから
なんでFエントリがないのにFWpとLBp間は通信できるの~???という問題。
f:id:tn-mi:20181221170849j:plain
FWpとLBpは下の図の通りb=VLANID110で繋がっていて,同一セグメントです。
f:id:tn-mi:20181221165500j:plain
同一セグメントなので,例えばFWpがLBpのMACアドレスを解決したい場合は
①FWpはARPリクエストを送信して,LBpのMACアドレスを求めてフレームを仮想L2SWに送る。
②仮想L2SWはフレームを受信したポートと同じVLANに所属しているMACアドレステーブルのエントリを検索する。
③送信MACアドレスLBpが接続されているポートがわかる。フレームをLBpが接続されているポートに転送し,LBpが受信する。
このように仮想L2SWのみで処理が完結します。【参考

(3)【パケット転送手順】中のオ~キに入れる適切なFテーブル名と項番を答えよ。

【パケット転送手順】
ルータ→L2SW→Fテーブル0,項番1→オ.Fテーブル1,項番2→FWp→LBp→カ.Fテーブル0,項番6キ.Fテーブル4,項番6→Webサーバp1
P社Webサーバp1宛のパケット転送手順はどうなるの~という問題。
現在の図1をみるとP社Webサーバp1への通信は
「ルータ→L2SW→FWp→LBp→L2SW→Webサーバp1」
という経路で流れています。この経路でテストシステムでも通信されるように手順を考えます。

オ.Fテーブル1,項番2
「Fテーブル0,項番1」でVLANIDが100のタグをセット,Fテーブル1で定義された処理を行うように促されています。
Fテーブル1では宛先MACアドレスがFWpのMACアドレス(mDES=mFWpw)ということで,項番2をみます。

カ.Fテーブル0,項番6
LBpはWebサーバp1にパケットを転送するためにパケットをOFSに入力します。
OFSはパケットの入力ポートを基にして処理を振り分けるために,Fテーブル0をみます。
Fテーブル0では入力ポートがp13(入力ポート=p13)ということで,項番6をみます。

キ.Fテーブル4,項番6
「Fテーブル0,項番6」でFテーブル4で定義された処理を行うように促されています。
Fテーブル4では宛先MACアドレスがWebサーバp1(mDES=mWSp1),また送信元MACアドレスがLBp(mSRC=mLBp)ということで,項番6をみます。


(4)P社のWebサーバp4が物理サーバ2に移動し、表7のOFS1のFテーブル3中の項番5によって,OFCにPacket-Inメッセージが送信されると,OFCは表8のFテーブル4中の二つの項番を変更する。Fテーブル4が変更されるOFS名を全て答えよ。また,項番3のほかに変更される項番及び変更後のアクションを答えよ。

(OFS名)OFS1,OFS2
(項番)7
(変更後のアクション)p12から出力
Webサーバp4が物理サーバ1から物理サーバ2に移動したら,OFCはOFSにどのようなFエントリの更新をするかという問題。
(OFS名)
同一OFCで同じシステムの場合,OFCに繋がる各OFSが持つテーブルエントリは共通です。なのでエントリを更新する場合はすべてのOFSにしなければいけません。
(項番)
Webサーバp4宛ての通信のマッチングがあるかFテーブル4をみていきます。
項番7にmDES=mWSp41 宛先MACアドレスがWebサーバp4の場合のアクションがかかれています。
(変更後のアクション)
p11(物理サーバ1に繋がるポート)から出力 だったものを p12(物理サーバ2に繋がるポート)から出力に更新します。



自分の回答と公式解答照らし合わせ

問題番号
自分の回答
公式解答 というかたちで晒していきます。

正誤
設問1(1)ア
スタック
スタック
設問1(1)イ
ステートフル
ステートフル
設問1(1)ウ
負荷分散
負荷分散
設問1(1)エ
デュアルスタック
チーミング×
設問2(1)
顧客ごとにFWのフィルタリングルールが異なるから
顧客ごとに異なるフィルタリングの設定が必要であるから
設問2(2)
・L2SWとFW間の接続状態
・FWとLB間の接続状態
・FWaとFWb間の接続状態
・FWbによるFWaの稼働状態
・FWaによるL2SWaへの接続ポートのリンク状態 など
○○○
設問2(3)
物理サーバが接続されているL2SWのポートに各顧客同一のNWになるようなタグVLAN
物理サーバへの接続ポートに,すべての顧客の仮想サーバに設定されたVLANIDを設定する。×
設問3
OFSのIPアドレス
OFSのIPアドレス
設問4(1)
・利用者側VLAN
・サーバ側VLAN
・LB仮想IPアドレス
・フィルタリングルール
・仮想FWのVLANID など
×××
設問4(2)
仮想L2SW内の顧客仮想サーバが接続されているポート
顧客のL2SW又はL3SWに接続する,L2SWa及びL2SWbのポート×
設問5(1)発生する可能性がある問題
1台の物理サーバがダウンしてしまうと顧客システム全てがダウンしてしまう。
物理サーバ3の障害によって,3顧客のシステムが同時に停止してしまう。
設問5(1)仮想サーバの配置
偏らないように分散して配置
3顧客向けの仮想サーバを,それぞれ異なった物理サーバに配置する。
設問5(2)
L2SWによってVLANID110利用してMACアドレスで送ることが可能だから
FWpの内部側ポートとLBpの仮想IPアドレスをもつポートは,同一セグメントであり,物理サーバ3内で処理されるから×
設問5(3)オ
(Fテーブル名)Fテーブル1
(項番)2
(Fテーブル名)Fテーブル1
(項番)2
設問5(3)カ
(Fテーブル名)Fテーブル1
(項番)6
(Fテーブル名)Fテーブル0
(項番)6
×
設問5(3)キ
(Fテーブル名)Fテーブル4
(項番)6
(Fテーブル名)Fテーブル4
(項番)6
設問5(4)OFS名
OFS1,OFS2
OFS1,OFS2
設問5(4)項番
設問5(4)変更後のアクション
p12から出力
p12から出力
○=15,×=8
自己採点=65点
採点=62点
ヴェ...

あとがき

仮想化,VLAN,SDN関連の問題でした。
相変わらずスタック/チーミング/リングアグリゲーションあたりは常識問題のようにでてきますね。
前半はしっかり卍とNW技術について理解していないと答えられないような問題で,後半は問題文をみてトレースしていけばわかるような問題で構成されていたように感じました。
私の解いていない問1ではWebサービス関係の問題がでていたみたいですね。お疲れさまでした。

おすすめの参考書

私のおすすめの参考書を2つ紹介したいと思います。
ネットワークスペシャリスト 合格テキスト 2018年度 (情報処理技術者試験対策)

ネットワークスペシャリスト 合格テキスト 2018年度 (情報処理技術者試験対策)

ネスペに出てくるNW技術について,一通りかいてあります。読みやすくわかりやすいです。
マスタリングTCP/IP 入門編 第5版

マスタリングTCP/IP 入門編 第5版

(買わなかったのですがおすすめ品です)
勉強をしていて,NWの実務経験がないからか問題解説や上の本を読んでもイメージがつかないことが多々ありました。
そんな時に欲しかった品。ネットワークの基本が丁寧にかかれています(立ち読み感想)。

ちなみに

私は去年の秋もネスペに受けていて
二度目の挑戦でした
ところで
この試験,落ちたからって2回目も受けるほど重要な試験なのでしょうか,なにかに役立つのでしょうか.
...わかりませんw
でもまあ1回目試験を受けた時よりもはるかに多くのNWの基礎知識を身につけることができたと思います。
( 'ω')b諦めないでよかった.
あくまで他の高度情報処理技術者試験(DBとSCだけ)を持っている人間の持論ですが,他の高度情報処理技術者試験と比べて,ネットワークスペシャリスト試験は知識量の分だけ合格に繋がる,そんな試験であるなあと個人的に思います。(出題される問題との相性や読解力もそうですが)
そのおかげか、過去問題も似たような問題が少なく飽きなかったし楽しかったです☀
ーーーーーーー
さいごに
私は4年制の専門学校に通っていて,学校から(半ば強制的に)情報処理技術者試験に受けさせられます。なので来年の春はES(エンベットシステムスペシャリスト)試験,秋は論文試験どれかを受けることになりそうです。組み込みシステム分野,未知の世界です。またがんばりたい.



イギリスのススメ~Bath/Cotswolds編~


自己(事故)アドベントカレンダー
”イギリスのススメ”として(私が知る範囲での)イギリスの街のおすすめスポットの紹介と、行き方と、おすすめの歩き方をかいていきたいとおもいます。

・イギリスのススメ~York / Hastings編~ 
・イギリスのススメ~FosseFarmHouse編~
・イギリスのススメ~Bath/Cotswolds編~ ←今回の記事
あまり海外に行ったことない人でもできるだけわかりやすいようにかいていけたらなあと思います。

というわけで今までのイギリスのススメ記事と同様BathとCotswoldsの紹介およびおすすめ場所などをかいていくのですが、この二つの都市に限っては私はオプショナルツアーをつかって短時間で軽く回っただけなので、あまり実体験に基づいた内容をたくさんかけません(じゃあなんでかくんだ)
まあでも短時間で回って素敵な場所だと感じた場所ということでもあるので少ない情報源ではありますが、かいていこうとおもいます。


Bath


バースは、イングランド西部、サマセットにある都市。
英国18世紀時代にロンドンの貴族や富裕な階層の保養地として大規模に都市再開発され、当時建造された美しい建物が数多く残っている歴史のある街です。街全体がユネスコ世界遺産に登録されています。
内装が細やかで美しいバース寺院(バースアビー)やにぎやかな通りのバース市街地など様々な見所がある街ですが、中でも最大の見所はローマン・バスです。ローマ時代の一大温泉保養施設のローマン・バスに訪れることができます。
またバースは「高慢と偏見(Pride and Prejudice)」の著者ジェーン・オースティンの出身地でもあり、ジェーン・オースティンの生活風景などを紹介した記念館ジェーン・オースティン・センターに訪れることもできます。

行き方

ロンドンに宿がある場合は以下の方法があります。
①鉄道を使う
LONDON PADDINGTONからBATH SPAまで所要時間は1時間30分、最安片道¥4000で行くことができます。
チケット購入方法などはここのサイトが非常に参考になると思います。ちなみに私はレイルヨーロッパで事前にネットで購入して、発券機がある場所で事前に発券しという流れで鉄道に乗りました。発車時間は事前に決まっていて基本変わりませんが、発車する駅の電光掲示板で確定時間を確認するという感じです。乗り場は事前に決まってないので(恐らく)、同じく発車する駅の電光掲示板で確認するという感じです。(大体20分前ぐらいに表示される)

②バスを使う
所要時間3時間程度最安£21で行くことができます。
ここのサイトで最寄りのLONDONのバス停留所(LONDON...とか入力すると予測候補がでてくる)とBath(Bus Station)間を購入しましょう。

③オプショナルツアーを使う
ここのサイトなんかでバース以外にもストーンヘンジウィンザー城に日帰り最安¥13,000で行くことのできるツアーなどをみつけることができます。
ちなみに私はレイコック村に行きたかったのでこのオプショナルツアーを使ってバースに行きました。

街の様子

ローマン・バスやバースアビーがある近くのバース市街地です。建物の形と色が調和していて綺麗な街並みです。ショップもたくさんあり,観光客で賑わっています。
f:id:tn-mi:20181222230540j:plain

おすすめの場所紹介

f:id:tn-mi:20181223011800j:plain
ハートのところがおすすめの場所です。
地図をみて下の方にBathSpa(駅)があります。鉄道かバスを使ってバースに行く場合はここら辺から周っていくことになると思います。
ちなみにBathSpa(駅)からthe jane austen centre(ジェーン・オースティン・センター)までは1kmぐらいです。一日で十分に全部まわることができるとおもいます。

簡単に各所を紹介していきます。

・The Roman Baths(ローマン・バス)
バース最大の見所。ローマ時代の一大温泉保養施設です。地上より一段低い造りなっており、中には大きな大浴場、聖泉、神殿や多数の展示品があります。
公式サイト:https://www.romanbaths.co.uk/
入場料:大人£16.50 学生£14.50 子供£10.25 シニア£14.50(参照
営業時間:1月~2月.09:30~18:00 3/20~6月.09:00~18:00 6/21~8/30.09:00~22:00 9月~10月.09:00~17:00 11月~12月.09:30~18:00(参照

・Sally Lunn's Historic Eating House & Museum
Sally lunns bunというパンが有名なバースの街で一番古いレストランです。
公式サイト(デザインがめっちゃかわいい):https://www.sallylunns.co.uk/
営業時間:10:00~22:00


バース寺院(バースアビー)


街の中心地に位置する教会です。外装はいたって普通の教会の見た目ですが、内装のステンドグラスの窓と天井が細やかで美しい造りになっています。
公式サイト:http://www.bathabbey.org/
入場料:無料(寄付あり)
営業時間(基本):月曜日.09:30~17:30 火~金曜日.09:00~17:30 土曜日.09:00~18:00 日曜日.01:00~02:30 && 04:30~18:00(詳細
※ショップの営業時間は異なります。

クリスマスシーズンのイギリスは街の至る所にクリスマスツリー🎄があってかわいいですね。下手くそな写真でごめんなさい。
f:id:tn-mi:20181212144552j:plain


パルトニー橋


18世紀後半に活躍したロバート・アダムが設計して建てられた橋です。橋の上にショップがある、珍しい設計になっています。

こんな感じの普通の橋の見た目(?)ですが
f:id:tn-mi:20181212144735j:plain
橋の上にはたくさんショップが並んでいます。
f:id:tn-mi:20181212144820j:plain
夜のライトアップが綺麗らしいので日が沈んでから行ってみてもいいかもしれません。


The Jane Austen Centre


高慢と偏見(Pride and Prejudice)」の著者ジェーン・オースティンに関する資料館です。オースティンのバース時代についての資料が展示されています。
1階のショップがあるところでチケットを買う
2階に行ってガイドの人のオースティンについて話を聞く
地下1階に行って展示品をみる
という流れです。
3階にはティールームがあります。

私はオースティンが書いた小説を一切みたことがなかったのですが、英国の19世紀時代の衣装を着たかったので行きました(は?)
小説を知らなくても、1つ1つの展示品やショップ品が英国らしい可愛いくて楽しめたので気になる方はぜひ。

公式サイト:https://www.janeausten.co.uk/
入場料:大人£12.00 シニア£10.50 子供£6.20 学生£9.50など
営業時間:参照
11月~3月 土曜日以外 10:00~16:00
11月~3月 土曜日 09:45~17:30
7月~8月 毎日 09:45~17:30
4,5,6月~9,10月 毎日09:30~18:30

扇子を使ったボディランゲージ。
f:id:tn-mi:20181212153110j:plain
こんな感じの英国の19世紀時代の衣装がたくさん展示されています。地下では試着もすることができます。
f:id:tn-mi:20181212153205j:plain
2階で頂いたアフタヌーンティーです。(写真下手くそ)
f:id:tn-mi:20181212154100j:plain


Cotswolds


イングランド南西部の北から南方向に延びる丘陵地です。北にはチッピング・カムデンやブロードウェイ、中央にはボートン=オン=ザ=ウォーターやバーフォード、南にはバイブリーやレイコック村など、コッツウォルズには数多くのとっても可愛いくて美しい村が点在しています。

行き方

個人でバス、鉄道を使っていく方法は、ここのサイトに詳しくかかれています。
オプショナルツアーを使っていく場合はここのサイトなんかで探すといいとおもいます。
ちなみに私はこのツアーを使っていきました。各村の所要時間が50分程度で忙しくなるかなあなどと思っていたのですが、そんなことなく、十分にまわることができました。


おすすめの村紹介

コッツウォルズの中でも特に印象に残っているバイブリーとボートン・オン・ザ・ウォーターという村を簡単に紹介します。

バイブリー


((バイブリーなのか...バイベリーなのか...))
詩人であり画家のウィリアム・モリスに「英国で最も美しい村」と賞賛された村だそうです。他のコッツウォルズの村と比べるとショップやレストランなどが少なく、ほんと小さな村です。小さな村ですが個人的に””コッツウォルズの良さ””が一番出ている素敵な村だなと感じました。

14世紀に建てられたコテージが並んでいます。これらはアーリントン・ロウと呼ばれ、イギリスの文化財建造物に登録されているそうです。
f:id:tn-mi:20181213102331j:plain
奥に見えるのがアーリントン・ロウです。村には綺麗な川があります。
f:id:tn-mi:20181213104145j:plain
写真の左側(ほんの少しみえるところ)にある橋を渡ってアーリントン・ロウ方面に行くことができます。川の水がほんっと綺麗。
f:id:tn-mi:20181223012350j:plain
バイブリーにあるショップです。
f:id:tn-mi:20181213125354j:plain
ちなみにここのショップはアニメ「きんいろモザイク」の中でも登場しています。
f:id:tn-mi:20181215103916j:plain
私はもちろんここでアイスクリームを食べ...たかったのですが時間がなくて、ここでクロテッドクリームを買いました。クロテッドクリームという脂肪分の暴力、めちゃくちゃおいしいですよね。
f:id:tn-mi:20181215104512j:plain

ボートン・オン・ザ・ウォーター


((バートン・オン・ザ・ウォーターなのかボートン・オン・ザ・ウォーターなのか...))
コッツウォルズの地区の中で最も人気な村です。町の中心を流れるウィンドラッシュ川に沿って多くのレストラン、パブ、ティーハウスなどがあります。
私が行ったコッツウォルズの村の中で一番賑やかで大きい村でした。

このようなウィンドラッシュ川という大きな川が町の中心を流れています。
f:id:tn-mi:20181215113133j:plain
川にクリスマスツリーが!!はい、かわいい。
f:id:tn-mi:20181215113224j:plain
川の脇に座ってテイクアウトしたサンドウィッチなどを食べている人もいます。
f:id:tn-mi:20181223013155j:plain

村にはレストランやパブ、ティーハウスから中華料理など様々な飲食店があります。

Small Talk TeaRooms
公式サイト:https://www.smalltalktearooms.co.uk/default.html
営業時間:平日・土曜日.09:00-17:00 日曜日.10:00-16:00
村の中心地にあるティーハウスです。
ビーフシチューと紅茶がありえん美味しかった。
f:id:tn-mi:20181215113451j:plain

街には可愛い雑貨屋などもたくさん並んでいます。
f:id:tn-mi:20181215114058j:plain


また画像ばかりになってしまいましたが、これでイギリスのススメはおわりです。
イギリスには私が紹介した都市以外にもエディンバラ城のあるエディンバラビートルズの故郷リバプール湖水地方など様々な魅力的な都市があります。
ロンドンの主要地を回るだけでもイギリス旅行は満足できると思います。ですがロンドンだけではイギリスの良さは伝わらないなと個人的に思うので、イギリス旅行に行く際はロンドンだけではなく、ぜひ地方都市にも足を運んでみてください。おすすめです。

次はフランスのススメかきたい(いつか)


ネスペ自己解説


自己(事故)アドベントカレンダー
情報処理技術者試験ネットワークスペシャリスト試験に無事合格できたので,自分のためにもネスペの問題の自己解説を晒していきます.
信頼度0.01ぐらいなので,参考にはしないでください。

問題:https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2018h30_2/2018h30a_nw_pm1_qs.pdf
解答例:https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2018h30_2/2018h30a_nw_pm1_ans.pdf


午後1問1

~問題文の流れ~
F社では、IPsec VPNを使って本社と営業所を接続している。グループウェア機能が老朽化している。

G社SaaSを導入することにした。セキュリティ対策考えてみた。

G社SaaSの利用でプロキシサーバの処理可能セッション数の超過、インターネット接続回線の帯域不足が予想。

元々利用していた本社と営業所を接続しているIPsecルータをSD-WANルータにして、SD-WANを利用してG社SaaS利用の問題を解決しよう。

設問1

(1)本文中のア、イに入れる適切な字句を答えよ。

ア.フォワード
「社内に対して」「アクセス先URLのログ取得」という文から”内部が利用するのかあ”、フォワードプロキシということがわかります。
イ.リバース
「外部から公開サーバのコンテツに直接アクセスさせない」「負荷分散」という文から”外部が利用するのかあ”、リバースプロキシということがわかります。

(2)本文中の下線①について、プロキシサーバで認証を行うことによってアクセスログに付加できる情報を答えよ。

利用者ID
G社SaaSを導入してセキュリティ強化をすると、社内PCからインターネットへのアクセスログになにを付加できるかという問題です。
3ページの真ん中あたりに「G社SaaS導入に合わせて…プロキシサーバで次のログを取得する。-アクセス先URLと利用者ID…」とかいてあります。
ここからG社SaaSを導入すると、プロキシサーバにおいてアクセス先URLと利用者IDのログ取得を行うようになることがわかります。
そのプロキシサーバを利用して従来通り社内PCからインターネットへのアクセスも行います。
なのでG社SaaS導入前の社内PCからインターネットへのアクセスログにはなかった"利用者ID"の情報をG社SaaS導入後には新たに付加することができるということがわかります。
---------
ちなみに私は問題文をちゃんと読まなかったので、G社SaaS導入すると利用者IDのログ取得行うんだ。へ~。わからんけど利用認証情報とれるんじゃない?→”利用者認証時間”!とか意味不明なことを答えにかきました。アホ

設問2

(1)下線②についてHTTPSでアクセスするためのHTTPプロトコルのメソッド名を答えよ。またHTTPS以外のマルウェア通信を遮断するためのプロキシサーバでの対策を答えよ。

(メソッド名)CONNECT
下線②をみるとプロキサーバでHTTPSアクセスログを確認しているところなのがわかります。プロキシサーバを経由してHTTPSを行うためにはGETメソッドではなく、CONNECTメソッドを使って通信します。
(対策)HTTPS以外のポートのCONNECTを拒否する
例えば内部LANの端末が外部C&Cサーバと通信するようなbot型のマルウェアに感染している場合
マルウェアは独自のプロトコルやランダムなポート番号を使って外部のC&Cサーバと通信します。
こういった場合に、プロキシでHTTPS以外のポートのCONNECTを拒否しておけば、マルウェア通信を遮断できますよね。
---------
プロキシ経由のHTTPS通信の仕組みを知らなかったので(メソッド名)GETメソッド(対策)意味不明なことをかいた。アホ

(2)本文中ウに入れる適切な字句を答えよ

プロキシサーバのルート証明書
HTTTPS通信のアクセス先URLを把握するためにプロキシサーバで暗号/復号処理をしたら、社内PC側で"証明書が信頼できない"エラーメッセージが表示される。解決するために社内PCに何をインストールするかという趣旨の問題です。
[イメージ図]
f:id:tn-mi:20181210233209j:plain
HTTPS通信をプロキシサーバで暗号/復号処理するためには
①PCとプロキシサーバ間でSSLハンドシェイク
②プロキシサーバと接続先サーバ間でSSLハンドシェイク
という仕組みになります。なのでPCに"証明書が信頼できない"とエラーメッセージが表示されるのは、①PCとプロキシサーバ間のSSLハンドシェイクで用いられる証明書が信頼できない=プロキシサーバの証明書が信頼できないということが原因であることがわかります。
プロキシサーバの証明書が信頼できない原因の理由に証明書の有効期限が切れているから、証明書が失効されているからなど様々な理由があげられるとおもいますが、問題文「ウをインストールして解決した」という文から、何かがインストールされていないというのが原因の理由である=プロキシサーバのルート証明書のインストール というながれで答えもふくめわかります。

設問3

(1)本文中のエに入れる適切な字句を答えよ。

コントロール
SDNは、利用者の通信トラフィックを転送するデータプレーンと、通信装置を集中制御するコントロールプレーンから構成されています。
---------
コントロールだなあとか思っていた。でも”制御”ってかいた。アホ

(2)本文中の下線③について、設定変更後の静的経路情報を35字以内で答えよ。

デフォルトルートのネクストホップをSD-WANルータに設定する
G社SaaSIPアドレスが変更された場合、その都度L3SWのルーティング設定を変更しなくてよいようにする方法です。
L3SWのルーティング設定に
ネットワークアドレス:ネクストホップ
G社SaaSNWアドレス:SD-WANルータのIPアドレス
というかたちでデフォルトルートのネクストホップを設定し,GSaaSネットワークアドレス宛ての通信はとりあえずSD-WANルータに渡すよな仕組みしておけばよいことがわかります。
---------
ネクストホップのことを知らず、わからなかったので空白。

(3)本文中のオに入れる適切な字句を図2中の機器名で答えよ。

SD-WANコントローラ
G社SaaSIPアドレスブロックの更新どこに指示すれば良いのか~という問題です。
SDNの仕組みを理解していれば”そういうことはとりあえずコントローラに渡しておけ”というのがすぐにわかると思います。また問題文からも「SD-WANのルータ設定は、SD-WANコントローラによって集中制御される」というところからSD-WANコントローラに指示するということがわかります。

(4)本文中の下線④について、このファイルを作成することによってプロキシから除外する通信を20字以内で答えよ。

G社SaaSへのHTTPS通信
プロキシ自動設定ファイル(適切なプロキシサーバを選択していくれるファイル?:PAC)を作成するにあたって除外する通信=プロキシサーバを経由させない通信はなにかという問題です。
問題文「G社SaaSへのWebアクセスは、プロキシサーバを経由せず各SD-WANルータを経由する」という文からG社SaaSへのHTTPS通信はプロキシサーバを経由させないことがわかります。
したがってG社SaaSへのHTTPS通信をプロキシから除外します。

(5)本文中の下線⑤について、G社SaaSAPI経由で取得する理由を二つ挙げ、それぞれ40字以内で述べよ。

本来プロキシサーバでG社SaaSへのアクセスログを取得する予定だったものを、なぜG社SaaSAPI経由で取得するのかという問題です。
①社内PCからG社SaaSへのアクセスがプロキシサーバを経由しなくなるから
問題文「G社SaaSへのWebアクセスは、プロキシサーバを経由せず各SD-WANルータを経由する」という文から
G社SaaSへの通信はSD-WANルータを経由しプロキシサーバを経由しないため、G社SaaSAPI経由で取得すべきということがわかります。
②出張先のPCからG社SaaSへのアクセスが記録されるから
出張先のPCからは”直接”G社SaaSを利用する=プロキシサーバを経由しない、G社SaaSで取るしかない、G社SaaSAPI経由で取得すべきということがわかります。
---------
②の答えが全く思いつかなく、G社SaaSAPI経由で取得すればプロキシ上のセキュリティ認証どうこうの話なくなるんじゃない?→”プロキシでの暗号化復号化作業がなくなる”って答えました。アホ

午後1問2

~問題文の流れ~
A社システム部門では、社内に監視サーバを構築しA社のサーバやLANの運用監視を行っている。
監視サーバはping監視とSYSLOG監視(ポートのリンク状態遷移/STP状態遷移/VRRP状態遷移)を行っている。

ケーブル切断によるネットワーク障害を監視サーバで検知できないことが判明。

現状ネットワーク環境を理解。

障害発生時の状況を確認。ping監視とSYSLOG監視の仕様が原因みたい(?)

そうだSNMPを使おう。

設問1

(1)本文中のア~エに入る適切な字句を答えよ。

ア.ICMP
pingはICMPプロトコルを利用します。
イ.IPアドレス
echo requestパケットの宛先としてIPアドレスが必要です。

$ ping 127.0.0.1

こんな感じでIPアドレス使いますよね。
ウ.UDP
通常syslogメッセージはUDPまたはTCPで送られるっぽい?<たぶん
あとは問題文「直ちにSYSLOGメッセージを送信する」という文からUDPっぽいということがわかります。
エ.コミュニティ
SNMPで管理するネットワークシステムの範囲はコミュニティというグループで管理しています。
また同じコミュニティ名内でMIBを共有します。
---------
エ.コミュニティの存在を知らず”マルチキャスト”ってかいた。

設問2

(1)本文中の下線①について、PC及びサーバに設定する情報に着目してVRRPによる冗長化対象を15字以内で答えよ。

デフォルトゲートウェイ
VRRPを用いるとPC及びサーバ側で、なにを冗長化できるかという問題です。
VRRPを用いる=ルータが冗長化される
PCとルータの繋がりってPCに設定するデフォルトゲートウェイぐらいですよね(?)なのでルータが冗長化されている=デフォルトゲートウェイ冗長化されているというかんじでわかります。

(2)本文中の下線②について、バックアップルータはあるメッセージを受信しなくなったときにマスタルータに切り替わる。VRRPで規定されているメッセージ名を15字以内で答えよ。

VRRPアドバタイズメント
マスタルータは定期的にバックアップルータにVRRP広告(HSRPでのHelloPacket)を送信し、マスタルータが正常稼働しているということを報告します。
ーーーーーー
VRRPAdvertisementって書こうと思ったんですけれど、回答欄の15字以内に収まらなくてうーーーーーんって5秒ぐらい悩みました。カタカナに自信がなかったのでVRRP広告ってかきました。

(3)本文中の下線③について、p4ポートでトランクポートに設定するVLANIDを全て答えよ。

VLAN100,VLAN200,VLAN300
f:id:tn-mi:20181211001215j:plain
p4を用いてコアSW1とコアSW2をトランクポート接続して、トランクポートに設定するVLANIDは何かという問題。
上の画像を綺麗に(?)まとめるとこんなかんじになります。
f:id:tn-mi:20181211002654j:plain
VLAN100,VLAN200,VLAN300をトランクポートに設定する必要のあるVLANIDであることがわかります。

設問3

(1)本文中の下線④について、BPDU(Bridge Protocol Data Unit)を受信しなくなったフロアSW2のポートを、図2中の字句を用いて答えよ。

p2
STPではBPDUというプロトコルを定期的にRootBridgeから送ることで、制御を行っています。
本文中「正常時はコアSW1がルートブリッジとなるように...」というところからコアSW1がRootBridgeということ
本文中下線④からSW2のp1ポートがBlockingPortだったことがわかります。
こんな感じでコアSW1から送っていたBPDUが
f:id:tn-mi:20181215142250j:plain
ケーブル1が断線してしまったがためにフロアSW2のp2ポートに届かないことがわかります。
f:id:tn-mi:20181215142530j:plain

(2)本文中の下線⑤について、フロアSW1が送信したSYSLOGメッセージが監視サーバに到達できなかったのはなぜか。”スパニングツリー”の字句を用いて25字以内で述べよ。

スパニングツリーが再構築中だったから
スパニングツリーの再構築が安定するには比較的長い時間が必要です。
フロアSW1はそんなケーブル1が断線して安定していない経路をつかって、UDPのSYSLOGメッセージを監視サーバに送ろうとします。これが原因で監視サーバにSYSLOGメッセージが届かなかった。と考えられます。
ーーーーーーーーーーー
よくわからんことかいた。

設問4

(1)本文中の下線⑥について、SNMPエージェントとSNMPマネージャに該当する機器名を、図1中の機器名を用いてそれぞれ一つ答えよ。

SNMPエージェント:コアSW1,コアSW2,サーバSW,フロアSW1,フロアSW2,フロアSw3,フロアSW4のうちいずれか一つ。
SNMPマネージャ:監視サーバ
SNMPは管理する側のSNMPマネージャと管理される側のSNMPエージェントの二つにより構成されています。

(2)本文中の下線⑦について、ポーリングとトラップの問題を、それぞれ35字以内で述べよ。

(ポーリング)5分ごとに状態を取得するので多くの場合異常検知が遅れる。
(トラップ)到達確認がでないのでメッセージが失われる可能性がある。
SNMPを利用しても今回のNWの異常においてはそれぞれ問題がある。それはなぜか~という問い。
SNMP基本動作~
ポーリング:SNMPマネージャがSNMPエージェントに、定期的(5分間隔)にMIBの問い合わせを行う。
トラップ:SNMPエージェントがSNMPマネージャに、MIBに変化が起きた際にメッセージを送信する。
~今回のNW異常~
ケーブル断線の連続&&スパニングツリーの再構築時間問題
でSYSLOGメッセージが監視サーバに届かなかった。

SNMPを使って
ポーリングでエージェントに定期的に問合せを行っても、定期的なので、SYSLOGメッセージみたいにすぐに異常検知はできず、検知が遅れる可能性があると考えられます。
トラップでMIBに変化が起きた際にメッセージを送信しても、スパニングツリーが再構築中だったら、設問3の(2)みたいに、検知を届けることができない可能性があると考えられます。

ーーーーーーーーーー
意味不明なことをかいた。

(3)本文中の下線⑧について、SNMPエージェントが満たすべき動作の内容を、40字以内で述べよ。

スパニングツリーが再構築するまでインフォームの再送信を繰り返す。
設問4の(2)の問題点を解決するために、SNMPのインフォームと呼ばれるイベント通知機能を利用しよう!
SNMPエージェントが満たすべき動作の内容はなーんだ?という問題。

SNMPはバージョン2からインフォームと呼ばれるイベント通知機能が使えるようになりました。
この機能はSNMPエージェントがインフォームリクエストでSNMPマネージャに通知した場合、SNMPマネージャからインフォームレスポンスがあるまで、SNMPエージェントは再送を試みてくる機能です。
この機能を使って、SNMPエージェントがスパニングツリーが再構築するまでインフォームの再送信をしてくれれば、今回の場合のような問題点を解決できます。

ーーーーーーーーーー
意味不明なことをかいた。

自分の回答と公式解答照らし合わせ

問題番号
自分の回答
公式解答 というかたちで晒していきます。

午後1問1


正誤
設問1(1)ア
フォワード
フォワード
設問1(1)イ
リバース
リバース
設問1(2)
利用者認証時間
利用者ID×
設問2(1)メソッド名
GETメソッド
CONNECTメソッド×
設問2(1)対策
CONNECTメソッドで接続済でない通信を遮断
HTTPS以外のポートのCONNECTを拒否する×
設問2(2)
プロキシサーバのルート証明書
プロキシサーバのルート証明書
設問3(1)
制御
コントロール×
設問3(2)
null
ネクストホップがSD-WANルータとなるデフォルトルート×
設問3(3)
SD-WANコントローラ
SD-WANコントローラ
設問3(4)
G社SaaS宛への通信
G社SaaSへのHTTPS通信
設問3(5)①
プロキシサーバを経由しないから
社内PCからG社SaaSへのアクセスがプロキシサーバを経由しなくなるから
設問3(5)②
暗号復号処理をせずURLを記録できるから
出張先のPCからG社SaaSへのアクセスが記録されるから×
○=6,×=6

午後1問2


正誤
設問1(1)ア
ICMP
ICMP
設問1(1)イ
IPアドレス
IPアドレス
設問1(1)ウ
UDP
UDP
設問1(1)エ
マルチキャスト
コミュニティ×
設問2(1)
デフォルトゲートウェイ
デフォルトゲートウェイ
設問2(2)
VRRP広告
VRRPアドバタイズメント
設問2(3)
VLAN100,VLAN200,VLAN300
VLAN100,VLAN200,VLAN300
設問3(1)
p2
p2
設問3(2)
p3にスパニングツリーが設定されていないから
スパニングツリーが再構築中だったから×
設問4(1)SNMPエージェント
フロアSW1
フロアSW1
設問4(1)SNMPマネージャ
監視サーバ
監視サーバ
設問4(2)ポーリング
特定のポートしか監視できない
5分ごとに状態を取得するので多くの場合異常検知が遅れる×
設問4(2)トラップ
再送を行わない
到達確認がないのでメッセージが失われる可能性がある×
設問4(3)
すべてのポートに関する情報を~
スパニングツリーが再構築するまでインフォームの再送信を繰り返す。×
○=9,×=5

○=15,×=11
自己採点=57.6点
採点=63点
傾斜あってよかったw

あとがき

去年と同じくネットワークの情報セキュリティ対策関連,SDN関連の話題がホットですね。
まさかSNMPが出題されるとは思っていなかった...!
私の取らなかった問題3なんかはMPLSとかVPNとかWANサービス関連の問題だったみたいです。
新出問題豊富な午後1でした。


ShellScriptでいろいろ


自己(事故)アドベントカレンダー

ShellScriptでmysqlをいじれるようなもの、SQL文にORDER句を入れるもの、CSVあたり,テーブルデータを表示に少し条件を,などをつくりました。
簡単にコードの説明と実行結果をのせます。



SQL文にORDER BY句をつける

github.com
「ORDER BY 項目名 desc/asc;」
Colum関数でSQLファイルに存在する項目名を配列に格納しています。
Sort関数で項目名とdesc/ascを配列に格納しています。
Sort関数でつくった配列を利用して、Update_SQL関数でSQLファイルに追記する流れです。

・SELECT文の項目名を配列に格納する

#引数のファイル名($1)を使って2行目(SELECT句)を取り出す
local colum_list=`sed -n '2p' $1`
#"SELECT hoge1 hoge2…"という感じになっているのでcutコマンドで順に項目を取り出す
colum=`echo $colum_list | cut -d' ' -f$i`
#項目にASで別の表示名が使われているか判別
if [ "AS" == `echo $colum_list | cut -d' ' -f$(( $i + 1 ))` ] 
then
        #ASが使われている場合そちらを優先、","を削除し格納
	ARRAY+=(`echo $colum_list | awk '{print $'$(( $i + 2 ))'}' | sed 's/,//'`)
else
     #使われていない場合、","を削除し格納
	ARRAY+=(`echo $colum | sed 's/,//'`)
fi

・項目名とdesc/ascを配列に格納

read -p "ORDER BYに指定する項目を優先度が高いものから選んでください[終了:q]:" colum
COLUM+=("$colum")
read -p "DESC/ASC:" colum
COLUM+=("$colum")

SQLファイルに追記する

#項目名とdesc/ascを格納した配列を回していく
#1項目目
ordersql="ORDER BY ${COLUM[$i]} ${COLUM[$i+1]}"
#2項目以降
ordersql="$ordersql, ${COLUM[$i]} ${COLUM[$i+1]}"
#追記するSQLファイル($1)の末尾から";"を削除し、ORDER句を追記したものをokikae.sqlとしてつくる
tail1=`tail -n 1 $1`
tail2=`tail -n 1 $1 | sed 's/;//'`
{ cat $1 | sed -e "s/$tail1/$tail2/";
	echo "$ordersql;";
} > okikae.sql
#okikae.sqlを追記するSQLファイルにコピーし削除
cp okikae.sql $SQLFILE
rm -f okikae.sql

実行(Sort.sh)

$ sudo ./Sort.sh
ORDER BYつきのSQLにする(2行目がSELECT文のみ可能)
ファイル名を入力(拡張子あり):test.sql
/* 項目一覧 */
hoge1 hoge2 hoge3
ORDER BYに指定する項目を優先度が高いものから選んでください[終了:q]:hoge3
DESC/ASC:desc
----------------
ファイル名:test.sql
・・・
ORDER BY hoge3 desc;

$ cat test.sql
・・・
ORDER BY hoge3 desc;

SQLファイルを実行結果を表示する

github.com
Query関数で"source ファイル名;"を実行し、その結果を直接表示する流れです。

SQLファイル実行結果を表示・実行時間を測定

#直接表示 timeコマンドで実行時間を測定しています。
time mysql --defaults-extra-file=./$file -u $USER -e "source $SQLFILE;"

実行(Query.sh)

$ sudo ./Query.sh
SQLファイル名:test.sql
/* ログインユーザー */
hoge
ユーザー名を指定[変更:change]:hoge
MYSQLに接続できました
hoge@localhostでログインしました
クエリ実行結果
((クエリ実行結果表示))
real   0m0.000s(プログラム呼び出しから終わるまでの時間)
user  0m0.000s(ユーザCPU時間)
sys    0m0.000s(システム時間)

CSVファイルをつくる

github.com
Create_File関数で新規ファイル名を指定しています。
CSV関数で指定された行数と列数の分だけ入力を促し、1行ずつファイルに書き込んでいる流れです。

・入力値が1以上か

#引数の入力値($1)に整数計算
expr $1 + 1 > /dev/null 2>&1
#整数計算の結果をretにいれる
ret=$?
if [ "$ret" -lt 2 ]
then
	if [ $1 -le 0 ]
	then
		#入力値が0
   else
                #入力値が1以上
	fi
else
        #入力値が数値でない	
fi

実行(Create_csv.sh)

$ sudo ./Create_csv.sh
CSVファイルを新規作成します
行数を指定:2
列数を指定:3
ファイル名を指定(拡張子なし):test
2行3列のtest.csvを作成します
1行1列目を入力:one
1行2列目を入力:one
1行3列目を入力:one
one, one, oneを書き込み
2行1列目を入力:two
2行2列目を入力:two
2行3列目を入力:two
two, two, twoを書き込み
----------------
ファイル名:test.csv
nyan, nyan, nyan
wan, wan, wan

$ cat test.csv
nyan, nyan, nyan
wan, wan, wan

CSVファイルをINSERT文に変換

github.com
「INSERT INTO テーブル名(項目名) VALUES(項目内容);」

Csv_colum関数でCSVファイルの列数=項目名の数を数えています。
Insert_colum関数で項目名を配列に格納しています。

その配列とCSVファイル上の入力を使ってCreate_sqlSQL文をつくるという流れです。

CSVファイルの列数を数える

#CSVファイル($1)の1行目を","を" "に変換して取り出す
ret=`head -n 1 $1 | sed -e '1s/,/ /g'`
#取り出したものを配列へ
array+=($ret)
#配列の要素数を数える
COLUM=${#array[@]}

・項目名を配列に格納

read -p "$(( $a + 1 ))番目の項目名:" colum
SET+=("$colum")

・INSERT文をつくる

###CSVファイルの行の数だけ以下を繰り返す
##1項目目
#項目名
set_sql="INSERT INTO $2(${SET[i]}"
#CSVファイル($FILE)内の項目内容を一項目ずつとりだす
value=`sed 's/ //g' $FILE | cut -d ',' -f $(( $i + 1 )) | sed -n -e "$j"p`
#項目内容
values_sql="VALUES('$value'"
##2項目以降
#項目名
set_sql="$set_sql,${SET[i]}"
#項目内容
value=`sed 's/ //g' $FILE | cut -d ',' -f $(( $i + 1 )) | sed -n -e "$j"p`
values_sql="$values_sql,'$value'"
##行おわりに");"をつける
set_sql="$set_sql)"
values_sql="$values_sql);"
##全行をまとめる
#1行目
sql="$set_sql $values_sql"
#2行目
sql="$sql\n$set_sql $values_sql"
##ファイルにかきこむ
{ echo "use $1;"
   echo -e "$sql"
} >> $FILE2

実行(CSV_INSERT.sh)

$ cat test.csv
nyan, nyan, nyan
wan, wan, wan

$ sudo ./CSV_INSERT.sh
csvファイルからINSERT文をつくります。
csvファイルを指定(拡張子なし):test
(中略 : mysql接続処理)
データベース名を入力:hoge_DB
テーブル名を入力:hoge_Table
Field  |       Type  | Null |  Key |  Default | Extra
Colum1 |     int(11) |   NO |  PRI |     NULL | 
Colum2 | varchar(45) |   NO |  PRI |     NULL |
Colum3 |     int(11) |  YES |      |     NULL |      
入力必須項目 Colum1 Colum2
項目名を3つ選んでください
1番目の項目名:Colum1
2番目の項目名:Colum2
3番目の項目名:Colum3
SQLファイル名を入力(拡張子なし):test2
----------------
ファイル名:test2.sql
use hoge_DB;
INSERT INTO hoge_Table(Colum1,Colum2,Colum3) VALUES('nyan','nyan','nyan');
INSERT INTO hoge_Table(Colum1,Colum2,Colum3) VALUES('wan','wan','wan');

$ cat test2.sql
use hoge_DB;
INSERT INTO hoge_Table(Colum1,Colum2,Colum3) VALUES('nyan','nyan','nyan');
INSERT INTO hoge_Table(Colum1,Colum2,Colum3) VALUES('wan','wan','wan');

テーブル内のデータを表示(WHERE句を1つ指定)

github.com
このコードを少し変えただけです。

Exec_SQL関数で引数の条件項目名($3)と条件項目内容($4)を使って

mysql --defaults-extra-file=./$file -u $USER -e "use $1;SELECT * FROM $2 WHERE $3 = '$4';"

を実行し結果を表示しています。

実行(Show_Data_Targ.sh)

$ sudo ./Show_Data_Targ.sh
テーブル内のデータを表示(WHERE指定)
(中略 : mysql接続処理)
(中略:DB表示処理)
データベース名を入力:hoge_DB
テーブル名を入力:hoge_Table1
/* hoge_Table1内のデータ一覧 */
Column1|Coumn2
      1|hoge
      2|hoge2 
条件項目名を入力:Column1
条件項目内容を入力:1
----------------
use hoge_DB;
SELECT * FROM hoge_Table1 WHERE Column1 = '1';
を実行した結果
Column1|Coumn2
      1|hoge

テーブル内のデータを表示(SELECT句/WHERE句を複数指定)

github.com
このコードを少し変えただけです。
「SELECT 抽出項目名 FROM 主テーブル名
 WHERE 条件項目名 = 条件項目内容
 GROUP BY 抽出項目名;」
COLUM関数で抽出項目名を配列に格納しています。
Targ_Colum関数で条件項目名と条件項目内容を配列に格納しています。

これら配列を使って
SQL_1関数:SELECT句とGROUP BY句
SQL_3関数:WHERE句
をつくって、Create_sql関数で組み合わせてSQL文をつくるというながれです。

実行(Show_Data_Targ1.sh)

$ sudo ./Show_Data_Targ.sh
テーブル内のデータを表示(SELECT/WHERE指定)
(中略 : mysql接続処理)
(中略:DB表示処理)
データベース名を入力:hoge_DB
テーブル名を入力:hoge_Table1
/* hoge_Table1内のデータ一覧 */
Column1|Coumn2|Column3
      1|hoge | 100
      2|hoge2| 100
      3|hoge | 100
      4|hoge | 100
条件項目名を入力[無し/終了:q]:Column2
条件項目内容を入力:hoge
条件項目名を入力[無し/終了:q]:q
/* hoge_Table1テーブルの列情報 */
Field   | Type        | Null | Key | Default |  Extra
Column1 | int(11)     |  No  | PRI |   NULL  |  auto_increment
Column2 | varchar(45) | No  | PRI |   NULL
Column3 | int(11)     |  No  |     |   NULL  |  
集合関数は直接入力
抽出項目名[終了:q]:SUM(Column3)
SUM(Column3)項目の表示名[項目名と同一:q]:合計
抽出項目名[終了:q]:Column2
Column2項目の表示名[項目名と同一:q]:q
抽出項目名[終了:q]:q
----------------
use hoge_DB;
SELECT Column2, SUM(Column3) AS 合計 FROM hoge_Table1 WHERE hoge_Table1.Column2 = 'hoge' GROUP BY Column2;
を実行した結果
Column2|合計
 hoge| 300

イギリスのススメ~FosseFarmHouse編~


自己(事故)アドベントカレンダー
”イギリスのススメ”として(私が知る範囲での)イギリスのおすすめ地方都市の紹介と、行き方と、おすすめの歩き方などなどをかいていきたいとおもいます。

・イギリスのススメ~York / Hastings編~
・イギリスのススメ~FosseFarmHouse編~ ←今回の記事
・イギリスのススメ~Bath/Cotswolds編~ 
あまり海外に行ったことない人でもできるだけわかりやすいようにかいていけたらなあと思います。


FosseFarmHouse

FosseFarmHouseとは


イングランド南部・コッツウォルズ地方のカースルクームにあるB&Bです。キャロンさんがオーナーをつとめています。
またここはアニメ「きんいろモザイク」の登場人物、アリス・カータレットの家の舞台となった場所でもあり、£175~でシノがホームステイした部屋(パインルーム)とアリスの部屋(ピンクルーム)に実際に宿泊することができます。B&Bではほかにも、テディベアのドアストッパーやアリスのパパが運転していた車など劇中に出てきたものや、きんいろモザイクの声優を務める方や製作者の方のサインをみることができます。
公式サイト:https://www.fossefarmhouse.com/kiniro-mosaic/

宿泊予約の仕方

上記公式サイトからオーナーのキャロンさんに直接メールでやりとりをすれば予約ができます。チェックインチェックアウトの時間、および夕食いるかいらないか、食べることのできない食べ物は何かあるか否かを英語で伝えるだけで基本予約は完了します。

FosseFarmHouseまでの行き方

ロンドンに宿がある場合は
①Chippenham駅まで鉄道、Chppenham駅からFosseFarmHouseまでバス
②Chippenham駅まで鉄道、Chippenham駅からFosseFarmHouseまでタクシー
どこか観光地に寄って行きたいのならば
③Chippenham駅まで鉄道、Chippenham駅からCastleCombe(カッスルクーム村)までタクシー、そこからFosseFarmHouseまで徒歩
という行き方があります。

鉄道を使う場合

LONDON PADDINGTON ~ CHIPPENHAM まで1時間10分程度、最安片道¥6,300で行けます。
チケットの購入の仕方などはここのサイトが非常に参考になります。
ちなみに私はレイルヨーロッパで事前にネットで購入して、発券機がある場所で発券していきました。
発車時間は事前に決まっていて基本変わりませんが、発車する駅の電光掲示板で確定時間を確認するという感じです。
乗り場は事前に決まってないので(恐らく)、同じく発車する駅の電光掲示板で確認するという感じです。(大体20分前ぐらいに表示される)

バスを使う場合

このサイトでChippenham (Wilts), Bus Station~The Gibb, Salutation Inn間を検索すれば時刻と経路を確認することができます。
恐らく35番線の32分で行けるバスで行くことになると思います。
バスを降りたら下の水色の道をまっすぐ歩くとFosseFarmHouseにつきます。1.5kmぐらいです。
f:id:tn-mi:20181217153835p:plain

タクシーを使う場合

Chppenham駅からは駅を出て左手にタクシー乗り場があるのでそこからのりましょう。FosseFarmHouseからはオーナーのキャロンさんに頼んで呼んでもらいましょう。

CastleCombe(カッスルクーム村)とFosseFarmHouse間の行き来

f:id:tn-mi:20181204102706j:plain
二つのルートがあります。
ピンクのルートはブルーのルートよりも比較的近道をして行くことができます。ですがゴルフ場の整備されていない道を通っていくので歩きにくいという難点があります。
ブルーのルートはピンクのルートよりも整備された道を通って行くことができます。ですがピンクのルートよりも若干距離があり(2.3kmぐらい?)CastleCombeから一般道路にでるまでの道が坂道で辛いという難点があります。

各所紹介

FosseFarmHouseに行くまでの各所の紹介を簡単にします。

Chippenham駅


小さい駅です。駅を降りると中央にバスターミナル、左手にタクシー乗り場があります。
f:id:tn-mi:20181204110728j:plain

CastleCombe(カッスルクーム村)


イングランドのウィルトシャーにある人口約350人の小さな村です。村には4ツ星のマナーハウスや教会、ティーハウスなどがあります。
f:id:tn-mi:20181204123809j:plain


CastleCombe(カッスルクーム村)とFosseFarmHouse間の行き来

ピンクのルートの場合です。
ちょっとわかりにくいのですが、ザ・ストリート(村の中心)から教会の方へまっすぐいくと、写真のようなマナーハウスがみえます。
マナーハウスの前を通って道沿いに歩いていくとゴルフ場方面に向かうことができます。
f:id:tn-mi:20181219145717j:plain
こんな感じの静かな平地(ゴルフ場)を歩いていきます。
f:id:tn-mi:20181204124608j:plain
周りにマジで何もないので不安になりますが、GPSも繋がりますし、道に迷うことはないとおもいます
f:id:tn-mi:20181219153327j:plain
ずっと歩いて行きFosseFarmHouseの近くにつくと柵があります。ここがゴールです。
f:id:tn-mi:20181204124629j:plain
柵を越えて少し歩くとFosseFarmHouseにたどりつきます。
FosseFarmHouseに着くとキャロンさんが「Nice me too!!!」と、明るい笑顔で迎えてくれます。
f:id:tn-mi:20181219153353j:plain
ちなみに英語が全然できない私でも聞き取れて意味が分かるぐらい、キャロンさんは丁寧に会話をしてくれます。
なので英語に自信がない...という心配はここに限っては本当にいらないとおもいます。


FosseFarmHouseの紹介

FosseFarmHouseの紹介です。

外観

昼 2階の右側の窓の部屋がアリスの部屋、左側の窓の部屋がシノがホームステイした部屋です。
f:id:tn-mi:20181204125415j:plain

f:id:tn-mi:20181204125452j:plain
アニメ 忠実に再現されていますね。
f:id:tn-mi:20181204221401j:plain

アリスの部屋

ピンクで統一されていて可愛い部屋です。バスローブやアメニティも充実していて、バスタブもあります。
f:id:tn-mi:20181219230557j:plain
ほんっとかわいい(吐血)
f:id:tn-mi:20181219230534j:plain
ヌァ~(言葉にならない尊さ)
ドレッサーも小さいベッドもカーテンも配置も劇中とそっくりです。
f:id:tn-mi:20181219154456j:plain
こういうところにいくと「オタクでよかった」ってほんとに思えますよね。
f:id:tn-mi:20181204221758j:plain
アリスの部屋から外をみると庭がみえます。
f:id:tn-mi:20181204221808j:plain

シノがホームステイした部屋

軽くみせていただいただけなのであまり写真がありませんがPineRoomもかわいかったです。
f:id:tn-mi:20181204222133j:plain

朝食・夕食

夕食は事前にキャロンさんから何時頃に始まるよ~といわれるのでその時間に頂いて、朝食はキャロンさんから何時に食べる?と聞かれるので答えた好きな時間に朝食を頂くながれです。
夕食・朝食頂いたのですが、夕食は鴨肉を美味しいソースなどで煮込んだもの、マッシュポテトなどをいただきました。朝食はトースト、ベーコン、トマトなどイングリッシュブレックファーストセットをいただきました。夕食・朝食ともにとてもおいしかったので宿泊する際はぜひ夕食・朝食ともにいただくのをおすすめします。
食事部屋の様子 ちなみにきんいろモザイクのキャラソンなどをききながら食事をすることができます(なかなかシュールで個人的におもしろかった)
f:id:tn-mi:20181204222749j:plain
イングリッシュブレックファーストです。焼きトマト,キノコ,ベーコン,ソーセージ,パンなどが並んでいます。それからフレークが(見えないところに)たくさんあって,自由に食べていく形です。
イギリスのソーセージ,日本でいう魚肉ソーセージみたいな感触なのですが、魚肉ソーセージとは違う味わいで私は好きだった。
f:id:tn-mi:20181219230943j:plain

一階にはたくさんきんいろモザイクグッズがあります。読み放題です。
f:id:tn-mi:20181204224356j:plain
アリスのパパが運転していた車です。
f:id:tn-mi:20181204224413j:plain
このように日本語の案内もあります。
f:id:tn-mi:20181219232058j:plain
あとフォスファームハウスにはきんモザVISITOR'sBOOKというものがあって、きんモザファンの方々・制作側の方々の訪問記録がかかれています。訪れた際はぜひぜひ記念にかいていきましょう。
f:id:tn-mi:20181220091442j:plain
(ほんとしょうもない自慢ですがそのVISITOR'sBOOKの声優の種田梨沙さんと高橋未奈美さんと同じページに私の記録があります(ほんとすみません))
f:id:tn-mi:20181204230033j:plain

なんだか画像ばかりで終わってしまいましたがイギリスのススメ~FosseFarmHouse編~は以上です。

言葉が通じない世界での旅はなかなかつらいものではあります。ですがつらい分計画が達成したときの喜びというものは大きいもので、国内旅行では味わえない達成感があるなあと個人的にかんじます。実際に見たい場所、行きたい場所がある場合は積極的にこれからもチャレンジしていきたい。問題なんてなにもないよケッコーケッコーいけるもんね(これがいいたかっただけ)




ShellScriptをつかってSQL文をつくる


自己(事故)アドベントカレンダー

ShellScriptでSQL文をつくるものをつくりました。(mysqlをいじれるSQL文)
mysqlへつなぐ、データベース表示する、テーブル表示する、テーブル列情報表示する、テーブルデータ表示するなどは前記事にかいてあります。
簡単にコードの説明と実行結果をのせます。



DELETE文(削除SQL

github.com
「DELETE FROM テーブル名 WHERE 条件項目名 = 条件項目内容;」
Target_inf関数で、条件項目名と条件項目内容を配列に格納しています。
その配列を使ってCreate_sql関数にてSQL文を組み立てる流れです。

・入力された項目名が存在するかチェック

#引数のデータベース名($1)テーブル名($2)項目名($3)を利用して返却値をretに入れる
ret=`mysql --defaults-extra-file=./$file -u $USER -e "use $1;select $3 FROM $2;"`
#retにエラーがある場合(項目名が存在しない場合)exit
if [ $? -gt 0 ]
then
  exit 1
fi

・条件項目名と条件項目内容を配列に格納

read -p "削除条件項目名[exit:q] : " delete_colum
read -p "削除条件項目内容 : " delete_data
ARRAY4+=("$delete_colum")
ARRAY4+=("$delete_data")

・DELETE文をつくる

#条件項目名と条件項目内容を格納した配列を回していく
#1項目目
targ_sql=" WHERE ${ARRAY4[i]} = \"${ARRAY4[i+1]}\""
#2項目以降
targ_sql="$targ_sql AND ${ARRAY4[i]} = \"${ARRAY4[i+1]}\""
#引数ファイル名($3)へ書き込む
{ echo "use $1;"
	echo "DELETE FROM $2$targ_sql;"
} >> $3

実行(Delete.sh)

$ sudo ./Delete.sh
SQLファイルを新規作成(DELETE)
ファイル名を入力:test
test.sqlを新規作成
(中略 : mysql接続処理)
データベース名を入力:hoge_DB
テーブル名を入力:hoge_Table
/* hoge_Tableテーブルデータ一覧 */
Colum1 | Colum2 | Colum3
      1|     one |      0
      1|     two |      1
/* WHERE内容作成 */
削除条件項目名[exit:q]:Colum1
削除条件項目内容:1
削除条件項目名[exit:q]:Colum2
削除条件項目内容:one
削除条件項目名[exit:q]:q
----------------
ファイル名:test.sql
use hoge_DB;
DELETE FROM hoge_Table WHERE Colum1 = "1" AND Colum2 = "one";

$ cat test.sql
use hoge_DB;
DELETE FROM hoge_Table WHERE Colum1 = "1" AND Colum2 = "one";


INSERT文(挿入SQL

github.com
「INSERT INTO テーブル名(項目名) VALUES(項目内容);」
Insert_inf関数で項目名と項目内容を配列に格納しています。

その配列を使ってCreate_sql関数でSQL文をつくるというながれです。

・テーブル内の入力必須項目を取得・表示

#テーブルの列情報を取得
ret=`mysql --defaults-extra-file=./$file -u $USER -e "use $1;SHOW COLUMNS FROM $2"`
#テーブルの列情報を配列に格納
ARRAY_INF=($ret)
#配列を回してテーブル内の入力必須項目名(NULLがNOでAutoIncrementではない項目)を配列に格納
if [ "${e}" == "NO" ] && [ "${ARRAY_INF[i+3]}" != "auto_increment" ]
then
	ARRAY2[n]="${ARRAY_INF[i-2]}"
fi
#配列内を横方向に表示
for e in "${ARRAY2[@]}"
do
	data="$data ${e}"
done
echo $data

・項目名と項目内容を配列に格納

read -p "追加項目名[exit:q] : " insert_colum
read -p "追加内容 : " insert_data
ARRAY3+=("$insert_colum")
ARRAY3+=("$insert_data")

・INSERT文をつくる

#項目名と項目内容を格納した配列を回していく
#1項目目
colum_sql="INSERT INTO $2(${ARRAY3[i]}"
data_sql="VALUES('${ARRAY3[i+1]}'"
#2項目以降
colum_sql="$colum_sql,${ARRAY3[i]}"
data_sql="$data_sql,'${ARRAY3[i+1]}'"
#引数ファイル名($3)へ書き込む
{ echo "use $1;"
	echo "$colum_sql $data_sql"
} >> $3

実行(Insert.sh)

$ sudo ./Insert.sh
SQLファイルを新規作成(INSERT)
ファイル名を入力:test
test.sqlを新規作成
(中略 : mysql接続処理)
データベース名を入力:hoge_DB
テーブル名を入力:hoge_Table
/* hoge_Tableテーブル列情報 */
Field  |       Type  | Null |  Key |  Default | Extra
Colum1 |     int(11) |   NO |  PRI |     NULL | 
Colum2 | varchar(45) |   NO |  PRI |     NULL |
Colum3 |     int(11) |  YES |      |     NULL |        
/* 入力必須項目 */ 
Colum1 Colum2
追加項目名[exit:q]:Colum1
追加内容:1
追加項目名[exit:q]:Colum2
追加内容:three
追加項目名[exit:q]:q
----------------
ファイル名:test.sql
use hoge_DB;
INSERT INTO hoge_Table(Colum1,Colum2) VALUES('1','three');

$ cat test.sql
use hoge_DB;
INSERT INTO hoge_Table(Colum1,Colum2) VALUES('1','three');


UPDATE文(更新SQL

github.com
「UPDATE SET 項目名 = 項目内容 WHERE 条件項目名 = 条件項目内容;」
Update_inf関数で項目名と項目内容を配列に格納しています。
Target_inf関数で条件項目名と条件項目内容を配列に格納しています。

これら配列を使ってCreate_sql関数でSQL文をつくるというながれです。

・項目名と項目内容を配列に格納

read -p "更新項目名[exit:q] : " update_colum
read -p "更新内容 : " update_data
ARRAY3+=("$update_colum")
ARRAY3+=("$update_data")

・条件項目名と条件項目内容を配列に格納

read -p "更新条件項目名[exit:q] : " update_colum
read -p "更新条件項目内容 : " update_data
ARRAY4+=("$update_colum")
ARRAY4+=("$update_data")

・INSERT文をつくる

#項目名と項目内容を格納した配列を回していく
#1項目目
targ_sql=" SET ${ARRAY3[i]} = \"${ARRAY3[i+1]}\""
#2項目以降
targ_sql="$targ_sql,${ARRAY3[i]} = \"${ARRAY3[i+1]}\""
#条件項目名と条件項目内容を格納した配列を回していく
#1項目目
data_sql=" WHERE ${ARRAY4[i]} = \"${ARRAY4[i+1]}\""
#2項目以降
data_sql="$data_sql AND ${ARRAY4[i]} = \"${ARRAY4[i+1]}\""
#引数ファイル名($3)へ書き込む
{ echo "use $1;"
	echo "UPDATE $2$targ_sql$data_sql;"
} >> $3

実行(Update.sh)

$ sudo ./Update.sh
SQLファイルを新規作成(UPDATE)
ファイル名を入力:test
test.sqlを新規作成
(中略 : mysql接続処理)
データベース名を入力:hoge_DB
テーブル名を入力:hoge_Table
/* hoge_Tableテーブルデータ一覧 */
Colum1 | Colum2 | Colum3
      1|     one |      0
      1|     two |      1
/* WHERE内容作成 */
更新条件項目名[exit:q]:Colum1
更新条件項目内容:1
更新条件項目名[exit:q]:Colum2
更新条件項目内容:one
更新条件項目名[exit:q]:q
/* SET内容作成 */
更新項目名[exit:q]:Colum3
更新内容:1
更新項目名[exit:q]:q
----------------
ファイル名:test.sql
use hoge_DB;
UPDATE hoge_Table SET Colum3 = "1" WHERE Colum1 = "1" AND Colum2 = "one";

$ cat test.sql
use hoge_DB;
UPDATE hoge_Table SET Colum3 = "1" WHERE Colum1 = "1" AND Colum2 = "one";


INNERJOIN(内部結合SQL)【1:N結合】

github.com
主テーブルと結合テーブルが1:Nの内部結合SQLです。
こんな感じで取り出すことのできるSQLをつくります。
f:id:tn-mi:20181216170024j:plain
「SELECT 抽出項目名 FROM 主テーブル名
 INNER JOIN ON 主テーブル名 = 結合テーブル名
 INNER JOIN ON 主テーブル名 = 結合テーブル名
 WHERE 条件項目名 = 条件項目内容
 GROUP BY 抽出項目名;」

FACT_COLUM関数で主テーブルの抽出項目名を配列に格納しています。
DIMANTION関数で結合テーブルの抽出項目名を配列に格納しています。
Fact_JOIN関数で主テーブル名をDimantion_JOIN関数で結合テーブル名を配列に格納しています。
Targ_Colum関数で条件項目名と条件項目内容を配列に格納しています。

これら配列を使って
SQL_1関数:SELECT句とGROUP BY句
SQL_2関数:INNER JOIN ~ 句
SQL_3関数:WHERE句
をつくって、Create_sql関数で組み合わせてSQL文をつくるというながれです。

・入力項目名が集合関数か判別

if [ "`echo $colum | grep "("`" == "$colum" ]

実行(Inner_join.sh)

$ sudo ./Inner_join.sh
SQLファイルを新規作成(INNERJOIN)
ファイル名を入力:test
test.sqlを新規作成
(中略 : mysql接続処理)
データベース名を入力:hoge_DB
/* テーブル一覧 */
*** F_Table
*** D_Table1
メインテーブルを入力:F_Table
/* F_Tableテーブルデータ一覧 */
D1_key  | F_number 
      1|     100
      1|     200
F_Tableテーブル結合条件項目名[無し/終了:q]:q

/* テーブル一覧 */
*** D_Table1
結合テーブルを入力[終了:q]:D_Table1
/* F_Tableの列情報 */
Field    |       Type  | Null |  Key |  Default | Extra
D1_key   |     int(11) |   NO |  PRI |     NULL | 
F_number |     int(11) |  YES |      |     NULL |    
/* D_Table1の列情報 */
Field   |       Type  | Null |  Key |  Default | Extra
D1_key  |     int(11) |   NO |  PRI |     NULL | 
D1_code |     int(11) |      |      |     NULL |  
F_Tableテーブル結合項目名:D1_key
D_Table1テーブル結合項目名:D1_key
/* D_Tableテーブルデータ一覧 */
D1_key | D_code 
      1|     d1 
      2|     d2
D_Tableテーブル結合条件項目名[無し/終了:q]:q

/* テーブル一覧 */
結合テーブル名を入力[終了:q]:q

/* F_Tableの列情報 */
Field    |       Type  | Null |  Key |  Default | Extra
D1_key   |     int(11) |   NO |  PRI |     NULL | 
F_number |     int(11) |  YES |      |     NULL |        
F_Tableテーブル抽出項目名[終了:q]:SUM(F_number)
SUM(F_number)項目の表示名[項目名と同一:q]:売上数
F_Tableテーブル抽出項目名[終了:q]:q

/* D_Table1の列情報 */
Field   |       Type  | Null |  Key |  Default | Extra
D1_key  |     int(11) |   NO |  PRI |     NULL | 
D1_code |     int(11) |      |      |     NULL |  
D_Table1テーブル抽出項目名[終了:q]:D1_code
D1_code項目の表示名[項目名と同一:q]:q
D_Table1テーブル抽出項目名[終了:q]:q

----------------
ファイル名:test.sql
use hoge_DB;
SELECT D_Table1.D1_code, SUM(F_number) AS 売上数
FROM F_Table
INNER JOIN D_Table1 ON F_Table.D1_key = D_Table1.D1_key
GROUP BY D_Table1.D1_code;

$ cat test.sql
use hoge_DB;
SELECT D_Table1.D1_code, SUM(F_number) AS 売上数
FROM F_Table
INNER JOIN D_Table1 ON F_Table.D1_key = D_Table1.D1_key
GROUP BY D_Table1.D1_code;

INNER JOIN・LEFT OUTER JOIN(内部結合・左外部結合SQL)【N:N結合】

github.com
主テーブルと結合テーブルがN:Nの内部結合と左外部結合SQLです。
こんな感じで取り出すことのできるSQLをつくります。
f:id:tn-mi:20181216170133j:plain
上の1:N結合のやり方と同じです。
*追加したところ
・Join関数を作って、内部結合が左外部結合かを選択させています。
・下のような感じで階層的に結合できるようにちょっと変えた。
「FROM 主テーブル
 INNER JOIN 結合テーブル1 ON 主テーブルキー = 結合テーブル1キー
 INNER JOIN 結合テーブル2 ON 結合テーブル1キー = 結合テーブル2キー
・主テーブル以外にも集合関数項目使えるようにした。

・内部結合か左外部結合か選ぶ

read -p "結合の種類:" select </dev/tty
#グローバル変数JOIN_SELECTに結合句を入れる
case "$select" in
	"1" ) JOIN_SELECT="INNER JOIN";;
	"2" ) JOIN_SELECT="LEFT OUTER JOIN";;
	* ) echo -e "存在しない選択です。\n最初からやり直してください。"
		exit 0;;
esac
#1項目目のときにこんな感じでつかう
JOIN="$JOIN_SELECT ${TABLE[j]} ON ${JOIN_COLUM[i]} = ${JOIN_COLUM[i+1]}"

実行(Join.sh)

$ sudo ./Join.sh
SQLファイルを新規作成(INNERJOIN)
ファイル名を入力:test
test.sqlを新規作成
結合の種類を選んでください
内部結合:1
左外部結合:2
結合の種類を入力:2
(中略 : mysql接続処理/DB表示)
データベース名を入力:hoge_DB
/* テーブル一覧 */
*** F_Table
*** D_Table1
メインテーブル名を入力:D_Table1
/* D_Table1テーブルデータ一覧 */
D1_key | D_code 
      1|     d1 
      2|     d2
D_Table1テーブル結合条件項目名[無し/終了:q]:q

/* テーブル一覧 */
*** F_Table
結合テーブル名を入力[終了:q]:F_Table
/* D_Table1の列情報 */
Field   |       Type  | Null |  Key |  Default | Extra
D1_key  |     int(11) |   NO |  PRI |     NULL | 
D1_code |     int(11) |      |      |     NULL |  
/* F_Tableの列情報 */
Field    |       Type  | Null |  Key |  Default | Extra
D1_key   |     int(11) |   NO |  PRI |     NULL | 
F_number |     int(11) |  YES |      |     NULL |    
D_Table1テーブル結合項目名:D1_key
F_Tableテーブル結合項目名:D1_key
/*  F_Tableテーブルデータ一覧 */
D1_key  | F_number 
      1|     100
      1|     200
F_Tableテーブル結合条件項目名[無し/終了:q]:q

/* テーブル一覧 */
結合テーブル名を入力[終了:q]:q

/* D_Table1の列情報 */
Field   |       Type  | Null |  Key |  Default | Extra
D1_key  |     int(11) |   NO |  PRI |     NULL | 
D1_code |     int(11) |      |      |     NULL |  
D_Table1テーブル抽出項目名[終了:q]:D1_code
D1_code項目の表示名[項目名と同一:q]:q
D_Table1テーブル抽出項目名[終了:q]:q

/* F_Tableの列情報 */
Field    |       Type  | Null |  Key |  Default | Extra
D1_key   |     int(11) |   NO |  PRI |     NULL | 
F_number |     int(11) |  YES |      |     NULL |        
F_Tableテーブル抽出項目名[終了:q]:SUM(F_number)
SUM(F_number)項目の表示名[項目名と同一:q]:売上数
F_Tableテーブル抽出項目名[終了:q]:q

----------------
ファイル名:test.sql
use hoge_DB;
SELECT D_Table1.D1_code, SUM(F_number) AS 売上数
FROM D_Table1
LEFT OUTER  F_Table ON = D_Table1.D1_key =  F_Table.D1_key
GROUP BY D_Table1;

$ cat test.sql
use hoge_DB;
SELECT D_Table1.D1_code, SUM(F_number) AS 売上数
FROM D_Table1
LEFT OUTER  F_Table ON = D_Table1.D1_key =  F_Table.D1_key
GROUP BY D_Table1;

ShellScriptをつかってmysql操作を楽にする


自己(事故)アドベントカレンダー

ShellScriptでmysql操作を楽にするようなものをつくりました。
簡単にコードの説明と実行結果をのせます。


ログインユーザーを指定してmysqlへつなぐ

github.com
mysqlログイン情報を記憶するファイル(.my.cnf)をつかってログインしています。

mysqlログイン情報をファイルに記録

{ echo "[client]";
	echo "user = $username";
	echo "password = $password";
	echo "host = $hostname";
} > $file

・ユーザーを指定する

#ユーザー名を指定してもらう
read -p"ユーザー名を指定[変更:change] : " login_user </dev/tty
#グローバル変数に入れる
USER="$login_user"
#-u $USER でユーザー名を指定してログイン
ret=`mysql --defaults-extra-file=./$file -u $USER -e"select user();"`

mysqlログインユーザー情報を取得

local file=".my.cnf"
local ret
#retにログインユーザー情報を入れる
ret=`mysql --defaults-extra-file=./$file -u $USER -e"select user();"`
#「user() ユーザー名@ホスト名」 の「ユーザ名@ホスト名」のみを取り出す
local user_list=`echo $ret | awk '{ print $2; }'`


実行(Login.sh)

$ sudo ./Login.sh
ユーザー名を指定してMYSQLへログイン
ユーザーを新規作成・変更
ユーザー名:hoge1((mysqlのログインユーザー名)
パスワード:((ログインユーザーのパスワード))
ホスト名:127.0.0.1((mysqlのホスト名))
新規作成・変更完了
/* ログインユーザー */
hoge1
hoge2
ユーザー名を指定[新規作成:change] : hoge1
MYSQLに接続できました
hoge1@localhostでログインしました

$ sudo ./Login.sh
ユーザー名を指定してMYSQLへログイン
/* ログインユーザー */
hoge1
hoge2
ユーザー名を指定[新規作成:change] : hoge1
MYSQLに接続できました
hoge1@hogehostでログインしました

mysql上のデータベース一覧を表示する

github.com
Show_DB関数で"Show databases;"を実行して、取得したDB一覧を配列に格納しています。
その配列内をShow_array関数で表示する流れです。

・データベース一覧を取得・表示

#retにデータベース一覧を入れる
ret=`mysql --defaults-extra-file=./$file -u $USER -e "show databases;"`
#retを配列に格納
ARRAY=($ret)
#配列[0]を削除
unset ARRAY[0]
#配列内を表示
for e in "${ARRAY[@]}"
do
	echo "*** ${e}"
done

show databasesを実行すると
"Database データベース名1 データベース名2 データベース名3"という感じで返されるので、一旦配列に格納してunsetで"Database"を消しています。


実行(Show_DB.sh)

$ sudo ./Show_DB.sh
データベース一覧を表示
(中略 : mysql接続処理)
hoge1@hogehostでログインしました
/* データベース一覧 */
*** hoge_DB1
*** hoge_DB2


mysql上のテーブル一覧を表示する

github.com
Show_DB関数とShow_array関数でデータベース一覧を表示します。
指定したデータベース名を引数にShow_Table関数で"use データベース名;show tables;"を実行し、取得したテーブル一覧を配列に格納しています。
そしてその配列内をShow_array関数で表示する流れです。

・テーブル一覧を取得・表示

#引数データベース名($1)を利用してretにテーブル一覧を入れる
ret=`mysql --defaults-extra-file=./$file -u $USER -e "use $1;show tables;"`
#retを配列に格納
ARRAY=($ret)
#配列[0]を削除
unset ARRAY[0]
#配列内を表示
for e in "${ARRAY[@]}"
do
	echo "*** ${e}"
done

show tablesを実行すると
"Tables_in_データベース名 テーブル名1 テーブル名2 テーブル名3"という感じで返されるので、一旦配列に格納してunsetで”Tables_in_データベース名"を消しています。


実行(Show_Table.sh)

$ sudo ./Show_Table.sh
データベース一覧を表示
(中略 : mysql接続処理)
hoge1@hogehostでログインしました
/* データベース一覧 */
*** hoge_DB1
*** hoge_DB2
データベース名を入力:hoge_DB1
/* hoge_DB1内のテーブル一覧 */
*** hoge_Table1
*** hoge_Table2


mysql上のテーブル内データを表示する

github.com
Show_DB関数とShow_array関数でデータベース一覧を表示します。
Show_Table関数とShow_array関数でテーブル一覧を表示します。
指定したデータベース名とテーブル名を引数にShow_TableData関数で"use データベース名;select * from テーブル名;"を実行し、その結果を直接表示する流れです。

・テーブル内データを表示

#直接表示
mysql --defaults-extra-file=./$file -u $USER -e "use $1;SELECT * FROM $2"

実行(Show_Table_Data.sh)

$ sudo ./Show_Table_Data.sh
テーブル内のデータを表示
(中略 : mysql接続処理)
(中略:DB表示処理)
/* hoge_DB1内のテーブル一覧 */
*** hoge_Table1
*** hoge_Table2
テーブル名を入力:hoge_Table1
/* hoge_Table1内のデータ一覧 */
Column1|Coumn2
      1|hoge
      2|hoge2 

mysql上のテーブルの列情報を表示する

github.com
Show_DB関数とShow_array関数でデータベース一覧を表示します。
Show_Table関数とShow_array関数でテーブル一覧を表示します。
そして指定したデータベース名とテーブル名を引数にShow_TableInf関数で"use データベース名;show clumns from テーブル名;"を実行し、その結果を直接表示する流れです。

・テーブルの列情報を表示

#直接表示
mysql --defaults-extra-file=./$file -u $USER -e "use $1;SHOW COLUMNS FROM $2"


実行(Show_Table_Inf.sh)

$ sudo ./Show_Table_Inf.sh
テーブル内のデータを表示
(中略 : mysql接続処理)
(中略:DB表示処理)
/* hoge_DB1内のテーブル一覧 */
*** hoge_Table1
*** hoge_Table2
テーブル名を入力:hoge_Table1
/* hoge_Table1テーブルの列情報 */
Field   | Type        | Null | Key | Default |  Extra
Column1 | int(11)     |  No  | PRI |   NULL  |  auto_increment
Column2 | varchar(45) | Yes  |     |   NULL  |