SQL,プログラミング

こんにちは、しきゆらです。

今回は、タイトルに有るようにSQLのJOINに対する個人的な誤解が解けたのでそれをメモしておきます。

前半は事の経緯なので、無視しても大丈夫です。

 

そして、先人である巨人のURLはこちら。

SELECT構文：JOINを使ってテーブルを結合する – SMART

http://rfs.jp/sb/sql/s03/03_3.html#JOIN

また、SQLだけ見せろ！という方向け

今回のSQLサンプル

http://sqlfiddle.com/#!9/0a510a/2/0

 

ことのはじめ

そもそも、私はこれまでSQLでテーブルを作るとき、それぞれのテーブルをどう関連付けて取得すればいいのかわからず１つのテーブルにすべてのデータをぶち込んでいました。

いわゆる非正規の状態。

 

しかし、それではいかん、ということで表を分けるようにしました。

(なお、この段階ではまだJOINと言うものを知りません。)

必要なデータの取得は、Ruby等のプログラムで各テーブルからデータを取得し、結合・ソート等をしていました。

 

しかし、表を結合して必要なデータを表現するためにJOINを用いればいいということを(最近)知り、

何でもかんでもJOINしていたわけです。

 

そんななか、本日ぶつかった壁のお話をしましょう。

 

ほんだい

何でもかんでもテーブルをJOINしてデータを取得していた私。

ぶつかった壁というのは、「JOINすると特定のデータが取得できない」という問題です。

 

例えば、以下のような何かのメモを表すデータベースを考えます。

メモを表現するitemテーブル

[table id=5 /]

 

メモのタグを表現するtagテーブル

[table id=6 /]

 

メモとタグを紐付けるtag_mapテーブル

[table id=7 /]

 

これらをもとに、考えてみます。

 

やりたいこと

３つの表をまとめて、メモの内容とタグ名を一覧で取得したい。

もしタグが付いていなくても、ないことも知りたい。

 

解決案

こんな感じのSQLを書いてみる。

SELECT content, GROUP_CONCAT(tag.name separator ',') as tags FROM item JOIN (tag JOIN tag_map USING(tag_id)) USING(item_id) GROUP BY item_id;

なお、GROUP_CONCATは引数のものをセパレータで区切って結合してくれる関数です。

12.19.1 GROUP BY (集約) 関数 – MySQLリファレンス

https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_group-concat

こうすると、「content」と「tags」をいっぺんに取ってこれます。

やったね！！

 

もんだい

全てにタグが設定されていれば問題なく取得できます。

しかし、新しく以下のようなデータが入ってくると話は変わってきます。

[table id=8 /]

 

上記レコートが追加された状態で同じようにSQLを実行します。

 

変化がない！？Why！？！？！？！！？！？

 

かいせつ

単なるJOINや「from hoge, fuga, …」としてテーブルを結合するものを内部結合(INNER JOIN)と言います。

これは、条件にあったもののみを列挙する結合。

今回であれば、「item_idとtag_idが紐づけされているもの」が条件となります。

 

しかし、itemテーブルに追加したものはまだタグと紐づけされていません。

そうなると、内部結合では条件にマッチしていないため取得されません。

 

 

 

解決方法

この問題を解決するには、外部結合(OUTER JOIN)を使います。

これは、条件にあっていないものも含めて取得するもの。

 

上記SQLを外部結合に書き換えて、実行してみます。

SELECT content, GROUP_CONCAT(tag.name separator ',') as tags FROM item LEFT OUTER JOIN (tag JOIN tag_map USING(tag_id)) USING(item_id) GROUP BY item_id

SQL的には、「JOIN」の前に「LEFT OUTER」をつけただけ。

これだけで外部結合になります。

※LEFT OUTER JOINの他に、RIGHT OUTER JOINと言うものもあるらしいです。

つなげる方向が違うようですが、基本的にはどっちか片方に合わせておけば問題ないかと思います。

LEFTとRIGHTで動作上の違いとか取得時の違いとかがあれば教えてください・・・

 

結果は以下の通り。

きちんと、取得できていることがわかります。

タグがあるものは、タグ名も一緒に取得し、タグがないものはnullとして取得しています。

 

 

参考　SELECT構文：JOINを使ってテーブルを結合する – SMART

http://rfs.jp/sb/sql/s03/03_3.html#JOIN

 

今回のSQLサンプル

http://sqlfiddle.com/#!9/0a510a/2/0

 

まとめ

今回はSQLの内部結合と外部結合の違いを知りました。

条件に合うものだけが必要なのか、そうでないのかによって使い分ける必要が有ることを知りました。

 

まだまだSQLとは仲良くなれていないので、もっとSQLと戯れていきたいです。

 

今回はこのへんで。

おわり

Linux,Mac,プログラミング

こんにちは、しきゆらです。

9月の中頃から10月の頭にかけて、学会とインターンシップ参加、さらに学会と

なんだか忙しい日々を過ごしていました。

 

いくつか、メモしておきたいことは溜まっているのでそれを放出していきます。

まずは、タイトル通りTerminalの設定をいじって見たお話を。

 

MacやLinuxではよく使うTerminal。

これの見た目を自分好みに変えることができることを最近知りました。

例えば、

$ cd hoge

とかの「＄」の前にはユーザ名とかが書かれていると思います。

ここの文言や表示する色などを変更することができるようです。

 

方法に関しては、以下のサイトを参考にしました。

【mac】ターミナルのプロンプトの変更 – Qiita

https://qiita.com/griffin3104/items/0d610ad1a57fa6ad6a09

 

「.bashrc」に対して以下のように書けばいいようです。

# 上記サイトより
export PS1='\h:\W \u\$ '

 

設定の内容等も、上記サイトに書かれています。

設定項目：

\h ホスト名（最初の.まで）
\H ホスト名
\W ディレクトリ
\w ディレクトリ（フルパス）
\u ユーザ名
\t 時間

色の設定：

黒\[\e[0;30m\]色をつけたい部分\[\e[0;0m\]
赤\[\e[0;31m\]色をつけたい部分\[\e[0;0m\]
緑\[\e[0;32m\]色をつけたい部分\[\e[0;0m\]
黄\[\e[0;33m\]色をつけたい部分\[\e[0;0m\]
青\[\e[0;34m\]色をつけたい部分\[\e[0;0m\]
紫\[\e[0;35m\]色をつけたい部分\[\e[0;0m\]
水\[\e[0;36m\]色をつけたい部分\[\e[0;0m\]
白\[\e[0;37m\]色をつけたい部分\[\e[0;0m\]

 

これにより、「＄」の前部分を自分好みに変えることができます。

例えば、顔文字を入れたりもできますよ。

 

さて、これらの情報をもとにUbuntuっぽいTerminalを作っていきましょう。

まずは、プロンプトの文言は以下のような感じ。

PS1="\[\e[0;32m\]\u\[\e[0;0m\]:\[\e[0;34m\]\W\[\e[0;0m\]$ "

本当であれば「ユーザ名@PC名」となりますが、長くなるのでユーザ名までしか記載していません。

充実に再現する場合は「\u」のあとに「@\h」とするといいかもしれません。

 

最後に、背景色です。

これに関してはブログで紹介してくれている方が。

Default background color of Terminal in Ubuntu – Code Yarns

https://codeyarns.com/2013/01/21/default-background-color-of-terminal-in-ubuntu/

このサイトによると、UbuntuのTermilan背景色は「RGB = (48, 10, 36)」だそうです。

これをTerminalの環境設定から指定してあげます。

 

Terminalの「環境設定」=> 「プロパティ」タブの中に複数のテンプレがあるので、そのどれかをいじるか、

新しく追加しましょう。

私は新規追加しました。

(新規追加は、左側のリストの下にある「＋」ボタンを押すだけ)

適当な名前をつけて、「背景色」を上記のRGB値にします。

 

そして、作成したプロファイルをダブルクリックすれば、作成した設定でTerminalが開きます。

紫っぽい背景色、緑色の文字。

Ubuntuっぽい。

個人的にはUbuntuの背景色が好きで、このためだけにUbuntuをインストールするくらいです。

最近はもっぱらMacしか使いませんが、心の隅っこくらいにはUbuntuのTerminalのことを覚えておいてあげましょう。

 

さらに追加で、「ls」コマンドを実行したとき、UbuntuのTerminalは以下のような感じで、

ファイルとディレクトリを色分けしてくれました。

これを再現するのはかんたん。

プロンプトの設定をした「.bashrc」に以下を追加すればOK。

alias ls='ls -G'

単純に、lsコマンドのオプションを付けるようにするだけ。

これで、上記の画像のようにディレクトリを色分けしてくれます。

 

 

今回はここまで。

需要はないと思いますが、Ubuntu好きな方はTerminalの色をUbuntuっぽくしてみてはいかがでしょうか。

おわり

JavaScript,Ruby,プログラミング

こんにちは、しきゆらです。

今回は、やんごとなき理由により日付を表す文字列をサーバ側からもらい、JSで処理するような状況で困った私が、その時解決した方法をメモしておきます。

 

少しだけ状況を説明すると、

サーバ側ではRubyさんを使って日付を文字列にしてDBへ入れています。

(この段階でSQLのtimestampとか使えばいいのに、と思いうツッコミはなしで)

その日付文字列をJSでパースしてあれこれ処理するようなプログラムを書いていました。

例えば、前回のログイン時間との時間差とか。

 

ここで、問題が起こります。

Rubyで「Time.now」として取得できる日付を表す文字列は、

  • 2017-10-20 23:32:04 +0900

JSで「Date.parse()」で理解できる日付文字列は、

  • Fri Oct 20 2017 23:32:04 GMT+0900

 

これでは、JSは日付を理解できません！

ここからが本題です。

 

JSは日付をパースしたりするとき、IETDF標準日付構文としてパースするようです。

時刻を表す文字列を与えると、parse() は time 値を返します。これは、"Mon, 25 Dec 1995 13:30:00 GMT" のような RFC2822 / IETF 標準の日付構文 (RFC2822 Section 3.3) を受け入れます。

Date.parse() – JavaScript | MDN

JSのDate()で返ってくる文字列は「Fri Oct 06 2017 23:49:19 GMT+0900 (JST)」のような形です。

パースするときも、この文字列形式だと思ってやるようなので、

YYYY-MM-DD HH:MM:SS +0900のような文字列は受け付けてくれません。

※Chrome系のブラウザでは受け付けてくれるようです。

 

何かやんごとなき状況により、「YYYY-MM-DD HH:MM:SS +0900」のような文字列を扱わざるを得ないような場合があるかもしれません。

そこで、サーバ側でJSで理解できる形式に変換してみましょう。

もちろん、Rubyさんでやってみます。

他の言語でも、多分同じような関数があるはずなのでいけるはずです。

time = Time.now
#=> 2017-10-06 23:53:45 +0900
time.strftime("%a %b %d %Y %T GMT%z (%Z)")
#=> "Fri Oct 06 2017 23:53:45 GMT+0900 (JST)"

重要なのは3行目のstrftime。

この書き方で、JSが理解できる日付を表す文字列にできます。

 

日本決め打ちですが、とりあえずこれでJSさんもRubyさんも、この日付をきちんと理解してくれます。
JSさんのDate型には罠が多くて面倒ですね。
早く同じ挙動になってくれ・・・と思う今日このごろ。

とりあえず、Chrome系、Firefox、Safariできちんと読み込めたので大丈夫だと思います。

 

今回はここまで。

おわり。

JavaScript,プログラミング

こんにちは、しきゆらです。

今日は、JavaScriptでフォームのデータを簡単に取得したり、XMLHttpRequestなどで送信しやすいFormDataオブジェクトに触れたら便利だったので、メモしておきます。

 

formタグのデータをJSで取得し、それに追加して他のデータを送りたい場合などはよくあると思います。

そんな時は、formの中にhiddenでinput要素を配置するとか、方法はいくつかあると思います。

でも、HTML的にはhiddenの要素は意味がないので、データの扱いはJSだけで完結していたいですよね。(個人的な意見です)

 

そこで使えるのがFormDataというもの。

formタグの中身をそのままkey, valueの組み合わせで表現できる魔法のようなオブジェクトです。

参考：FormData – MDN

簡単な使い方をまとめておきます。

 

JavaScript,プログラミング

こんにちは、しきゆらです。

今回は、NodeListなどのArrayっぽいオブジェクトの扱いについてメモしておきます。

 

DOMを扱うとよく現れるNodeListなど、Arrayっぽくあるものがちょくちょくあります。

Arrayに似ているけど、concatとかArrayの便利メソッドは使えません。

このArrayっぽいものをArrayに直す方法です。

// NodeList
var divs = document.querySelectorAll("div");
// HTMLCollection
var ps = document.getElementsByTagName("p");

// Arrayに変換
divs = [].slice.call(divs);
ps = [].slice.call(ps);

[].sliceはどのブラウザでも動くようです。

IEを無視すれば、以下のものでも良さそうです。

divs = Array.from(divs); 
ps = Array.from(ps);

 

今回のことは、ほとんどMDNに書いてあることでした。

意外と見えていないんだなぁと。

以下のあたりを見ました。

参考

 

 

今回はここまで。

おわり

Rust,プログラミング

こんにちは、しきゆらです。

今回はタイトル通りRustという言語のチュートリアルをやってみたのでその記録として残しておきます。

 

Rustさんは、FirefoxでおなじみMozilla製のプログラミング言語です。

コンパイル言語であり、速度はそれなり、メモリリークなどを極力排除することができるような言語だそうな。

詳しいことは、ググっていただけるとありがたいです。

 

それでは、見ていきます。

内容に関しては、以下のものをそのまま実行していくだけです。

はじめる – Rust

 

Linux,Raspberry Pi,Ruby,プログラミング,環境構築

こんにちは、しきゆらです。

今回は、前回にセットアップしたRaspberry PiにRubyをコンパイルし、SSH接続ができるようにします。

 

先回の記事はこちら

【Raspberry Pi】OSのインストールから日本語化まで

 

Linux,Raspberry Pi,プログラミング,環境構築

こんにちは、しきゆらです。

前から気になっていたRaspberry Piを最近買いました。

5000円程度で簡易なLinux機が使えるのは嬉しいですね。

 

さて、たいていの人が面倒で放置してしまう初期設定です。

買って数週間、何度かデータが吹っ飛び、その度に再セットアップしたので

忘れないうちにメモしておきます。

合わせて、必要なコマンドもメモしておきます。

 

Ruby,プログラミング

こんにちは、しきゆらです。

今回は、タイトルにある通りCSVに関するメモです。

 

時々触ることになるCSVですが、普通のDBでいいじゃん、って思いませんか？

私はそう思ってあまり触ってこなかったんですが、DBを適当に作ると後々変更しにくくなるということにようやく気付きました。

そこで、今回は忘れないうちに基本的なCSVの触り方をメモしておきます。

 

Ruby,プログラミング

こんにちは、しきゆらです。

昨年に投稿した「【Ruby】メールを送受信する」のプログラムを書き直す機会があったので、改めて書いておきます。

 

上記の投稿に載せたスクリプトをクラスに直して、いくつかのバグを修正しました。

その結果はこれ

require 'net/smtp'
require 'json'
require 'base64'

class SendMail
  def initialize(to: 'to@mail.com', from: 'from@mail.com', option: {})
    # 引数を取り込む
    @smtp_option = {
      server:        'smtp.gmail.com',
      port:            465,
      authentication: :plain,
      ssl:             true, 
    }

    @smtp_option.merge!(option)
    # 認証情報を合わせ
    @smtp_option.merge!(load_secret)
    @from = from
    @to = to
    @date = Time.now.strftime("%a, %d %b %Y %X")
  end

  # パスワードとかを記しておくもの
  def load_secret
    File.open("./secret.json") do |file|
      JSON.load(file)
    end
  end
  
  def create_subject(subject)
    str = ""
    Base64.encode64(subject).split("\n").each do |string|
      str += "=?UTF-8?B?#{string}?= "
    end
    return str.rstrip
  end

  def create_body(subject: 'sample subject', body: 'mail main body')
    @message = <<EOS
Date: #{@date}
From: #{@from}
To: #{@to}
Subject: #{create_subject(subject)}
Content-Type: text/plain; charset=UTF-8
Mine-Version: 1.0

#{body}
EOS
  end

  def send(subject: "test mail", body: "this mail is test!")
    count = 0
    begin
      create_body(subject: subject, body: body)
      mail = Net::SMTP.new(@smtp_option[:server], @smtp_option[:port])
      mail.enable_ssl if @smtp_option[:ssl]
      mail.start(@smtp_option[:server], @smtp_option["mail"], @smtp_option["pass"])
      mail.send_mail(@message, @from, @to)
      mail.finish
    rescue => e
        count += 1
        if count < 10 then
          puts "retry"
          retry
        else
          puts e.class
          puts e.message
        end
    end
  end
end

 

極力外部のライブラリを使わずにメール機能を実装してみました。

使っているのは、標準添付ライブラリのみ。

 

基本的な使い方

SendMailクラスのインスタンスを作るときに送信先、送信者のメールアドレスとオプションを渡します。

オプションでは、smtpサーバ名・ポート番号・SSLを使うかどうかなどを連想配列で渡します。

 

認証情報をsecret.jsonというファイルにJSON形式で書きます。

例えば以下のような感じ。

{"mail": "mail@mail.com","pass": "password"}

これを読み込んで使うようになっています。

 

デフォルトでは、Gmailのsmtpサーバを利用するようになっています。

Googleで2段階認証を設定している場合は、ログインするためのパスワードでは認証できません。

これに対しては、アプリケーション用のパスワードを作成して、secret.jsonに書きます。

作成方法は以下あたりから。

 

送信するときは、そのままsendメソッドを使います。

引数として、subjectとbodyを文字列として渡してやります。

これだけでメールを送ることができます。

一定のタイミングで通知を送るとか、使い方はそれなりにありそう。

 

件名の文字化け対策

また、以前のスクリプトでは件名が文字化けしてしまいます。

これをなんとかするために、create_subjectでいい感じに変換しています。

参考としたのは、以下のサイトたち。

charsetを指定しただけでは件名が化けてしまうようです。

どうも、アスキー文字のみしか使えないようです。

これをなんとかするためには、

=?CHARSET?B?エンコードした文字列?=

という形で書かないといけないようです。

UTF-8で場合は

=?UTF-8?B?エンコードした文字列?=

とします。

エンコードはBase64というものを使ってエンコードしています。

 

また、一定の文字数で区切らないといけないようです。

これに関しては、Base64でエンコードするといい感じのところで区切り文字を入れてくれます。

そこでsplitして、それぞれの文字列を上記のフォーマットで囲ってやっています。

 

 

ブログを見ると、メールを書くプログラムをいつかいたのかと思ったら約1年前だったとは。

久々にこの遺産が役に立ったので、驚きました。

思いついたら書いて、保持しておくのは大事なんだなと思いました。

 

おわり