Chiroru's Diary

日々の学びをちょこちょこメモしていきます

Railsの認証について

Rails 勉強会

7/11(土)にRailsGirls Shiojiri Moreにて、『Webを支える技術』の9.8~9.12HTTP認証について学び、Railsの認証についても気になったのでまとめてみました。

【目次】

Railsにおける認証

Railsでは、Basic認証・Digest認証・Token認証が利用されています。
詳しくはこちらに載っております。

Basic認証

http_basic_authenticate_withメソッドを追加することで利用可能となります。
全てのアクションでフィルタが実行されるので、アクションを除外する場合にはexcept で指定します。

 class PostsController < ApplicationController
   http_basic_authenticate_with name: "dhh", password: "secret", except: :index

   def index
     render plain: "Everyone can see me!"
   end

   def edit
     render plain: "I'm only accessible if you know the password"
   end
end

Digest認証

authenticate_or_request_with_http_digestメソッドを追加することで利用可能となります。
ブロックの引数にはユーザー名をとり、認証に成功すればここからPWが返り、失敗すればfalseやnilが返ります。

require 'digest/md5'
class PostsController < ApplicationController
  REALM = "SuperSecret"
  USERS = {"dhh" => "secret", #plain text password
           "dap" => Digest::MD5.hexdigest(["dap",REALM,"secret"].join(":"))}  #ha1 digest password

  before_action :authenticate, except: [:index]

  def index
    render plain: "Everyone can see me!"
  end

  def edit
    render plain: "I'm only accessible if you know the password"
  end

  private
    def authenticate
      authenticate_or_request_with_http_digest(REALM) do |username|
        USERS[username]
      end
    end
end

Token認証

その名の通りTokenを利用する認証方式です。

クライアントがサーバーに接続した際に、サーバーに送られたユーザー情報(セッションデータ)を暗号化したTokenが返されます。この時サーバーにはその情報が保存されず、Token（認証情報）として持ち、クライアントはリクエストする度にリクエストヘッダにToken含めて送り返し、サーバーは返されたTokenを復号して認証情報を取得する仕組みになっています。

これまで実装したDevise(ユーザー認証で利用)や、OmniAuth(twitterやgithub認証で利用)の機能もまた、Token認証になります。

仕組みとしては以下のようになります。

class PostsController < ApplicationController
  TOKEN = "secret"

  before_action :authenticate, except: [ :index ]

  def index
    render plain: "Everyone can see me!"
  end

  def edit
    render plain: "I'm only accessible if you know the password"
  end

  private
    def authenticate
      authenticate_or_request_with_http_token do |token, options|
        # Compare the tokens in a time-constant manner, to mitigate
        # timing attacks.
        ActiveSupport::SecurityUtils.secure_compare(token, TOKEN)
      end
    end
end

参考

感想・まとめ

『Webを支える技術』のHTTP認証の学びから、Railsの認証についても調べるきっかけにできたことがよかったです。
HTTP認証のまとめはこちらより。

HTTPの認証と機能(キャッシュ/パイプライン化)

Rails 勉強会 HTTP

7/11(土)にRailsGirls Shiojiri Moreに参加しました！
今回は『Webを支える技術』の9.8~9.12の部分を読んだので、そのまとめを書いていきます。

【目次】

HTTP認証

主流のHTTP認証方式として、Basic認証・Digest認証があります。またWebAPIでは、拡張機能であるWSSE認証を利用することもあります。

これらの認証機能は、アクセス制御がかかったリソースに、まずクライアントがアクセスをし、サーバーが「ステータスコード401」と必要な認証情報がわかる「WWW-Authenticateヘッダ」を含んだレスポンスを返すことで区別が可能となります。

これより、クライアントはその方式に従った認証情報を送ることができます。

Basic

ユーザー名とPWを利用する認証方式です。

リクエストごとにAuthorizationヘッダに、平文から暗号化したユーザー名とPWの2つを入れて送信しますが、この平文から暗号化された文字列は容易に元に戻すことができてしまうので、HTTPS通信の利用など、対応を考えなければいけません。

Digest

Basicと比べ、PWがハッシュ関数を適用して暗号化される、よりセキュアな認証方式です。

WWW-Authenticateヘッダの値(チャレンジ)には、リクエストごとに変化するnonce(number of used)、ダイジェストの作成方法を決めるqop(quality of protection)、推測不可能なopaqueが含まれます。

ちなみにqopにはauthとauth-initの２種類があり、

  • 「auth」はメソッド＋URI
  • 「auth-init」はメソッド＋URI＋メッセージボディ

がダイジェストの作成に利用されます。

Basicと同じくユーザー名は平文からの暗号化なので、こちらの対応策もまたHTTPS通信の利用となります。

あまり普及していない・・

みたいです。この理由は、リクエストの度に401レスポンスを得なければならず、クライアントにとって操作が煩雑となること、またwebサーバーによって認証がオプション扱いされており、サポートされてない可能性があるためです。

一方Basic認証は

同じURI空間のリソースであれば、一度の認証のみで後は自動で認証情報が送信されます。

WSSE

Basic認証とDigest認証の中間のような認証方法です。
HTTP1.1の標準外の認証方式でWebAPIの認証に使用されます。

クライアントはAuthenticationヘッダにWSSE、profile値、PWダイジェスト、nonce、日時を含めてリクエストを送信します。

PWをネットワーク状に流さない反面、生のPWをサーバーに置いておく必要があります。

認証と認可

認証は「誰(何)であるか」がポイントなのに対し、認可は「権限があるか」がポイントととなります。

認証(authentication)

基本的に以下の3つのうち一つを満たしていればOKです。

  1. WHAT YOU ARE (inherence factor)
    顔貌、指紋、署名など、その人自身を提示して、相手にアイデンティティを確認させる方法

  2. WHAT YOU HAVE (possession factor)
    身分証などその人だけが持っているものを提示する方法

  3. WHAT YOU KNOW (knowledge factor)
    パスワード、秘密の質問等、その人だけが知っていることを提示する方法

認可(authorization)

ある特定の条件に対して、リソースアクセスの権限を与えることです。

自分が誰であろうと、権限を持っていることによってリソースアクセスが許可されます。

参考：よくわかる認証と認可

HTTPの機能

キャッシュ

サーバーから得たリソースデータをローカルストレージに一定期間保存しておき、期間内の再アクセス時に再利用する仕組みのことです。

こうすることで、同じデータを表示させる時に、前回より速く表示が可能となります。

サーバーから取得したリソースのヘッダに含まれる以下の3種類から、キャッシュの可否、またその期間がわかります。

  • Pragma:no-cache：キャッシュ不可
  • Expires：キャッシュ有効期間(max1年の指定を推奨)
  • Cache-Control：より細かい指定が可能

条件付きGET

キャッシュが使えるか検証する仕組みのことです。

  • If-Modifide-Since：リソースの更新日時を確認
  • If-None-Match：指定した値にマッチしていないかを確認

サーバー側を実装する際は、より正確に更新確認ができるIf-None-MatchでEtag(リソースの更新状態を比較できる文字列)ヘッダの利用が推奨されています。

パイプライン化

クライアントとサーバー間でのリクエストとレスポンスのやり取りを持続させ、レスポンスを待たずしてリクエストを送ることができます。

接続を切断するには、Connectionヘッダに値closeを指定します。

感想・まとめ

Railsには便利なgemがあるので、これまで認証機能の中身についてあまり考えることがなかったなぁと読みながら考えていました。
また現在では、二段階認証や多要素認証などの方式もあるのでそれらの勉強も改めて必要そうです。
その際には『Real World HTTP』という参考書を紹介していただいたので、また読んでみたいと思いました！

RailsGirls Online More!に参加しました！

ruby rails 勉強会

6月27日(土)にRailsGirls Tokyo, Nagoya, Nagano, Sendai合同で開催されたOnline More!(以下RGMore)に参加させていただきました！

【目次】

当日について

SpatialChatを使い、座学エリア・もくもくエリア・雑談エリアに分かれて各自好きなことを行うスタイルでした。

私は、座学エリアにて『Ruby超入門』の著者で、現在Fjord bootcampでアドバイザー(7/1より技術顧問)の Kuniaki Igarashi さんによるRuby・Rails・学習ガイドのお話を聞かせていただきました！

Railsのお話

Rails6.0から追加されたActionText機能について、実践を交えながら紹介いただきました。

ActionText

Railsにリッチテキストコンテンツと編集機能が追加できます。

コードをほとんど書かずにWordのような機能を実現でき、画像はドラック&ドロップで貼り付けられるので、例えばTwitterから画像を貼ったりすることもできました。
またリサイズも自動で行ってくれます。

私も作ってみました👇

f:id:chiroru_memo:20200629152734p:plain
editページ

写真はアルバニアに旅をした時のものです。
(日本人の99%は行かないって言われているマイナーな国です。。)

Contentあたりを見るとわかるように、ちょっとした字体の調整だったり、引用だったり目次だったりを表示する機能がついています。

また右端の矢印で作業を戻したり進めたりできます。本当にWordみたい！

下の方が切れてしまっているのですが、完成したらページ下のcreate/updateボタンで完成。

f:id:chiroru_memo:20200629152813p:plain
indexページ

このindexで見るとわかるように、さっき見切れていた写真がいい感じにリサイズされています！ ちょっと手を加えれば簡単なブログのようなものも作れちゃいそうです✨

その他注意点など

  • モデルはmessage.rbだが、テーブルはaction_text_rich_textsで、record_typeとrecord_idでポリモーフィック関連になっているのでN+1問題を考慮する必要がある

  • その他active_storage_blobsとactive_storage_attachmentsのテーブルもあり、これらはActive Storageに関するもの。
    画像やファイル名は、active_storage_blobsに保存されている

  • N+1問題への対応としてwith_rich_text_#{name}_and_embedsメソッドを使う場合、action_text_rich_textsとactive_storage_attachmentsテーブルは取得するが、active_storage_blobsは取得しない

【その他参考】

Rubyのお話

リファレンスマニュアルを利用して、ArrayとEnumerableのメソッドを見ていきました。

その中でいくつか気になったメソッドをまとめてみました👇

Array

self * sep -> String

Array#joinと同じような動きができます。

p [1,2,3] * ","
# => "1,2,3"

bsearch { |x| ... } -> object | nil

条件を満たす値を二分探索するので、検索が遅いarrayを速く処理できます。
(利用する場合、事前にソートしておく必要があります)

ary = [0, 4, 7, 10, 12]
ary.bsearch {|x| x >=   6 } # => 7
ary.bsearch {|x| x >= 100 } # => nil

cycle(n=nil) {|obj| block } -> nil

配列の全要素を n 回(nilの場合は無限に)繰り返し、ブロックを呼びだします。

a = ["a", "b", "c"]
a.cycle {|x| puts x }  # print, a, b, c, a, b, c,.. forever.

combination(n) {|c| block } -> Array

サイズ n の組み合わせをすべて生成し、それを引数としてブロックを実行します。
順序の指定はできません。

a = [1, 2, 3, 4]
a.combination(3).to_a  #=> [[1,2,3],[1,2,4],[1,3,4],[2,3,4]]

concat(*other_arrays) -> self

自身の末尾に破壊的に連結します。 破壊的ではあるが、「!」が付かない。

[ "a", "b" ].concat( ["c", "d"] ) #=> [ "a", "b", "c", "d" ]
[ "a" ].concat( ["b"], ["c", "d"] ) #=> [ "a", "b", "c", "d" ]

a = [ 1, 2 ]
a.concat(a, a)                    #=> [1, 2, 1, 2, 1, 2]

intersection(*other_arrays) -> Array

共通要素を新しい配列として返します。

[1, 1, 3, 5].intersection([3, 2, 1])                  # => [1, 3]
["a", "b", "z"].intersection(["a", "b", "c"], ["b"])  # => ["b"]

Enumerable

まずEnumerableモジュール・Enumeratorとは

  • 「Enumerableモジュール」は繰り返しを行なうクラスのための Mix-in

  • 「Enumeratorクラス」は(each以外のメソッドにも) Enumerableの機能を提供する

  • 「Enumeratorオブジェクト」はeachなどの繰り返し処理を行うメソッドのブロックを省略した場合に(も)返ってくる

Enumerableをincludeしてeachを用いることで、Arrayのメソッドが大体使えるようになります。

【6/30追記】

上記に関して、FBをigaigaさんにいただきました！ 🙏

大筋あってるので細かいのですが、詳しく書くとこんな感じです

・eachメソッドを定義してあるクラスにEnumerableモジュールをincludeできる
・includeするとEnumerableに定義されてあるメソッドが全部つかえるようになる
・Arrayは最初からEnumerableがinclude済み（なのでEnumerableのメソッド群もつかえる）

chunk {|elt| ... } -> Enumerator

条件にそうもの、そわないものを分ける時などに便利です。
(even?は偶数であれば真を、そうでない場合は偽を返す)

enum.chunk {|elt| key }.each {|key, ary| do_something }

# 整数列を連続する奇数/偶数に分ける
[3,4, 1, 5,].chunk {|n|
  n.even?
}.each {|even, ary|
  p [even, ary]
}
# => [false, [3]]
#    [true, [4]]
#    [false, [1, 5]]

detect(ifnone = nil) -> Enumerator

findと同じだが、Railsのfindと被ってしまうため、代わりにこちらを使う。

[1, 2, 3, 4, 5].detect {|i| i % 3 == 0 }   # => 3

tally -> Hash

self に含まれる要素を数え上げた結果を Hash で返します。

["a", "b", "c", "b"].tally  #=> {"a"=>1, "b"=>2, "c"=>1}

学習ガイド

RubyとRailsの学習ガイド2019年版をもとにお話いただきました。

その中でも「Railsの技術を伸ばしていくときにお勧め」として挙げられていた『パーフェクト Ruby on Rails 』は中級者向けとのことなので、勉強が進んだら読んでみたいと思いました。

もうすぐ発売される『パーフェクト Ruby on Rails 【改訂2版】』では、igaigaさんも執筆されていたそうでこちらも気になります！

またHTTPのおすすめ文献『暗号技術入門』も読んでみたいなと思いました。

【6/30追記】
igaigaさんより🙏

「HTTPでも使われている暗号技術を解説した本」という感じです。一言だと「暗号技術の本」ですね。めっちゃ良い本なのでお勧めです。

分野別でレベルごとに読むべき技術書を書いてくださっているので、ぜひこれからの学習の際にも参考にさせていただきたいと思いました。

感想

RGMore!あっという間の3時間でした。私がいつも参加させていただいているNagoya,NaganoのMore!は、主に本をみんなで読み進めるスタイルの勉強会なので、今回igaigaさんがお話してくださった座学形式の勉強会は新鮮でした。
中でもやはりRailsのActionTextは便利なのに一瞬で実現できてしまい、改めてRailsの面白さを感じられた気がします！
そして、普段中々お会いできない全国のRGさんたちと交流できたことも、とても嬉しかったです！ 第2回があればぜひまた参加したい😆

今回このような場を用意してくださったRailsGirlsJPの皆さま、貴重なお話を聞かせてくださったigaigaさん、そしてRGのみなさん、ありがとうございました🤲✨🌼

DB設計の手順

データーベース

データベース設計(論理設計)について学習したので、その方法をまとめていきたいと思います。

【✏️目次】

論理設計の手順

論理設計とはRDBにおいて、データから何をどのようなフォーマットで保存するかを決めること。

今回2冊の本を読んだので、2通りのやり方を書こうと思います。

『達人に学ぶDB設計徹底指南書』より

設計のステップは以下となる。

1. エンティティの抽出  
2. エンティティの定義  
3. 正規化する(1~3段階)  
4. ER図の作成

1. エンティティの抽出

システムのためにどのようなエンティティ(データ)が必要になるかを抽出する。

2. エンティティの定義

エンティティがデータをどのような属性(列)で保持するかを定義する。

3. 正規化(1~3段階)

データの冗長性を排除して、一貫性を持たせることで更新時の不都合を防ぐことが目的。 実務では５段階あるうちの第1~第3正規形までを考えることが多い。 (またこの逆の操作は結合である)

第1正規形

「一つのセルには一つの値(=スカラ値)」しか含まないこと。

第2正規形

主キーの一部の列に対して従属する列(部分関数従属)を、主キーに全ての列に対して従属する列(完全関数従属)にすること。

第3正規形

テーブル内に存在する段階的な従属関係(推移的関数従属)によるデータ登録時の不都合を、デーブルを最適に分割することで解決すること。

4. ER図の作成

エンティティの見取り図の作成。それぞれの関係性を表記方法に従い記述する。
「多対多」の関係にあるエンティティの場合、「関連実体」というエンティティを用いて「1対多」に分解する。 関連実体では、多対多の関係にある2つのエンティティの主キーを組み合わせたキーを、主キーとして持つ。

『楽々ERDレッスン』より

設計のステップは以下となる。

1. イベント系テーブルの洗い出し
2. リソース系テーブルの洗い出し
3. 各テーブルに項目を追加する
4. リレーションシップを設定する

1.2各種テーブルの用意

テーブル(エンティティ)を洗い出す。

イベント系 洗い出し

「イベント系」とは、「〜する」などの行為の記録となるもののことを指す。

リソース系 洗い出し

「リソース系」とは、イベント系テーブルに対して「誰が」「何を」するのかを指す。

3. 各テーブルに項目を追加

各テーブルのカラムを洗い出し、追加する。

4.リレーションシップを設定

レコードを特定するためには値が変動することのない主キーが必要になるので、「テーブル名＋ID」のようにIDを導入し、テーブル同士の関連付けを行う。

関連ワードのメモ

関数従属

2つの項目があり、1つの値が決まれば自ずともう一方の値も特定できる関係のこと。例えば「商品コード→商品名」など。

キー

「商品コード→商品名」でいう商品コードの方を、識別子またはキーと呼ぶ。 キーは正しくは「候補キー」といい、これはテーブル構造によっては主キーになり得る可能性があるという意味合い。

主キー

レコードを一意に識別するための項目。

項目が主キーとして使用できる基準は

  • Not NULL制約がある
  • 重複がないこと
  • 値が変更されないこと

複合キー

単体では主キーになれないものを、組み合わせることで一意に特定可能となり、主キーに使用できるキーの組み合わせのこと。
例えば、商品名と連番(同じ名称がきたら番号をずらす)など。

ER図作成ツールについて(VSCode)

普段私はVSCodeを使用しているのですが、ちょうどDraw.ioがプラグインとして利用できるようになったこちらを使用してみました。

私は作図後にエクスポートしようとしたらできなかったので、以下を参考に設定をしました。

まとめ(感想)

今回DB設計を１から学ぶにあたって2冊の参考書を利用しました。
はじめ『楽々ERDレッスン』を読んでいたのですが、語彙など理解ができないことが多く、一度読むのを中断して『達人に学ぶDB設計徹底指南書』を読みました。こちらは初心者にも優しい丁寧な解説が書いてあり、スラスラ読み進めることができました。その後、『楽々ERDレッスン』を読みなおすとスムーズに読むことができるようになっていました。
設計は2冊の方法を網羅的に考えることで進めやすくなりました！

参考

PostgreSQL構文のまとめ

データーベース PostgreSQL

『SQLゼロからはじめるデータベース操作』を参考に、PostgreSQLについて学んだので構文についてまとめてみようと思います。

※SQLの語彙についてはSQL基本の理解(言葉の整理編)でまとめています。

【目次】


※SQLの語彙について：SQL基本の理解(言葉の整理編)

データベース

作成

CREATE DATABASE <データベース名>;

-- 一度psqlを終了し、新しく作ったDBに接続
# \q

postgres@:~$ psql -U <ユーザー名> -d <データベース名> -h localhost

テーブル

作成

CREATE TABLE <テーブル名>
(  
<列名>　<データ型>　<制約>,  
・・
<テーブルの制約>, ・・  
);

PRIMARY KEY (<列名>)

定義の変更

-- 列を追加
ALTER TABLE <テーブル名> ADD COLUMN <列の定義(※列名・データ型・制約)>;

-- 列を削除
ALTER TABLE <テーブル名> DROP COLUMN <列名>;

-- テーブル名の変更
ALTER TABLE <古テーブル名> RENAME TO <新テーブル名>;

データを登録する

-- トランザクションを開始
BEGIN TRANSACTION

-- 行の追加(※この場合は全カラムに対応した値が必要)
INSERT INTO <テーブル名> VALUES (値);

-- 追加を確定する
COMMIT;

削除

DROP TABLE <テーブル名>;

テーブル関連のpostgresqlコマンド

-- テーブル一覧の表示
\dt;

-- テーブル構造の表示
\d <テーブル名>;

-- テーブル内のデータ一覧表示
SELECT * FROM <テーブル名>;

SELECT文

テーブルからデータを検索し、取り出す(問い合わせ)時に使用する。

-- 列を検索する
SELECT <列名>, <列名>・・
FROM <テーブル名>;

列に別名をつける

SELECT <列名>　AS　<別名>,
　　　  <列名>　AS　"<日本語別名>"
FROM <テーブル名>;

重複を除く

SELECT DISTINCT <列名>, <列名>
FROM <テーブル名>;

行の条件指定

SELECT <列名>, <列名>・・
FROM <テーブル名>;
WHERE <行に対する条件>;

集約関数

COUNT(*)を除き、基本的に全ての集約関数はNULLを除外している。

-- テーブルの行数をカウントする
SELECT COUNT(*)
FROM <テーブル名>;

-- NULLを省いた行数をカウントする
SELECT COUNT(列名)
FROM <テーブル名>;

-- 列の合計値を求める
SELECT SUM(列名)
FROM <テーブル名>;

-- 列の平均値を求める
SELECT AVG(列名)
FROM <テーブル名>;        

-- 列の最大値を求める
SELECT MAX(列名)
FROM <テーブル名>;

-- 列の最小値を求める
SELECT MIN(列名)
FROM <テーブル名>;

--重複を除外してカウントする
SELECT COUNT(DISTINCT 列名)
FROM <テーブル名>;

テーブルのグループ分け

SELECT <列名>
FROM <テーブル名>
GROUP BY <列名>;

グループに対する条件指定

SELECT <列名>
FROM <テーブル名>
GROUP BY <列名>
HAVING <グループに対する条件>;

DISTINCTとGROUP BY

どちらも重複を排除し、NULLをひとまとめにし、実行速度もほぼ同じである。この二つの使い分けとしては以下のようにすると良い。

DISTINCT：選択結果から重複を除外したい時
GROUP BY ：集約した結果を求めたい時

検索結果の並べ替え

デフォルトでは昇順(ASCキーワード)で、降順にしたければDESCを末尾につける。

SELECT <列名>
FROM <テーブル名>
ORDER BY <並べ替えの基準となる列=ソートキー> DESC;

データ

登録

文字型の値を挿入する時は「' '」で囲うことを忘れないこと。

-- 基本構文(※列に対してDEFAULT制約をつけている場合、値にDEFAULTキーワードの指定が可能)
INSERT INTO <テーブル名> (列, 列・・) VALUES (値, DEFAULT,・・)

-- 複数行INSERT
INSERT INTO <テーブル名> (列1, 列2・・) VALUES (値1・・),
                                          (値2・・);

-- 全列にINSERTする場合は、列リストの省略可
INSERT INTO <テーブル名> VALUES (値, 値・・)

-- NULLの挿入
INSERT INTO <テーブル名> (列, 列,・・) VALUES (値, NULL,・・)

他テーブルのデータをコピーする

-- テーブルBのデータを、テーブルAにコピー
INSERT INTO <テーブルA> (列, 列・・)
SELECT 列, 列・・
FROM <テーブルB>;

削除

-- 基本構文
DELETE FROM <テーブル名>;

-- 一部の行を削除
DELETE FROM <テーブル名>
WHERE <行の条件>;

-- 必ず全行削除(※DELETEとの違いは、ロールバックできないこと)
TRUNCATE <テーブル名>;

更新

-- 基本構文
UPDATE <テーブル名>
SET <対象の列名> = <更新後の値>;

-- 一部の列のみ変更
UPDATE <テーブル名>
SET <対象の列名> = <更新後の値>
WHERE <行の条件>;

トランザクション

「COMMIT」は処理の変更を確定し、「ROLLBACK」は処理の変更を破棄する。

BEGIN TRANSACTION
DML文
DML文
COMMIT / ROLLBACK;

PostgreSQL

ちなみにPostgreSQLは、全てのSQL文をトランザクション内で実行している。どういうことかというと、BEGINを明示的に発行せずとも全てのSQL文はBEGINとCOMMITに囲まれている扱いとなっている。
そして、複数のSQL文のうち一つでもエラーを起こせばその後のものもエラーとなり、コミットは失敗して強制ロールバックされる仕組みになっている。

ビュー

-- ビューの作成
CREATE VIEW <ビューの名前> (ビュー列名, ビュー列名, ・・)
AS SELECT;

-- ビューの削除
DROP VIEW <ビューの名前>;

-- 多段ビューの削除
DROP VIEW <ビューの名前> CASCADE;

関数まとめ

算術

-- 絶対値を求める
ABS(数値)

-- 余剰を求める
MOD(被除数, 除数)

-- 四捨五入
ROUND(対象数, 丸めの桁数)

文字列

-- 小文字に変換する
LOWER(文字列)

-- 大文字に変換する
UPPER(文字列)

-- 文字列の置換
REPLACE(対象文字列, 置換前, 置換後)

-- 文字列切り出し(切り出し開始の位置=左から何番めか)
SUBSTRING(対象文字列 FROM 切り出し開始の位置 FOR 数)

変換

-- 型の変換
CAST(変換前の値 AS 変換するデータ型)

-- 最初にNULLでない値を返す
COALESCE(データ, データ,・・)

日付

-- 現在の日付
CURRENT_DATE

-- 現在の時間  
CURRENT_TIME

-- 日時
CURRENT_TIMESTAMP

-- 日付要素の切り出し
EXTRACT(日付要素 FROM 日付)

述語

戻り値が真理値になる関数のこと。

-- 文字列の部分一致検索
<列> LIKE <文字列>

-- 範囲検索
<列> BETWEEN <値> AND <値>

-- NULL行の選択/NULL行以外の選択
IS NULL
IS NOT NULL

-- 複数のORを省略する(引数にビューの指定可)
<列> IN (値, 値, ・・)

-- 条件に合うレコードの存在を調べる
EXISTS <SELECT * の相関サブクエリ>;

CASE式

SELECT
  CASE
    WHEN <列=値> THEN <真の時の式>
    ELSE <真ではない時の式>
  END
FROM <テーブル名>;

テーブルの集合演算

足し算(行方向に連結)

-- 重複を排除しない場合はUNION ALL
SELECT
UNION 
SELECT;

共通部分を選択する

SELECT
INTERSECT
SELECT;

差分を抽出(引き算)

SELECT  
EXCEPT  
SELECT;

結合

別のテーブルから列を持ってきて、列方向に連結する。

-- 両テーブルに存在する情報のみ出力(内部結合)
SELECT
FROM <テーブル名1> INNER JOIN <テーブル名2>
ON <テーブル名1>.列名 =  <テーブル名2>.列名

-- テーブル情報が欠けることなく出力(外部結合)
SELECT
FROM <テーブル名1> OUTER JOIN <テーブル名2>
ON <テーブル名1>.列名 =  <テーブル名2>.列名

ウィンドウ関数

-- レコードのランキングの算出
SELECT
RANK () OVER (PARTITION BY ランキングの範囲)
FROM <テーブル名>;

-- 累計の算出
SELECT
SUM(列名) OVER (ORDER BY 並びの基準の列名)
FROM <テーブル名>;

GROUPING演算子

合計と小計を同時に求めることができる。

SELECT
FROM <テーブル名>
GROUP BY ROLLUP(列, 列);

参考

SQL基本の理解(言葉の整理編)

【目次】

SQLとは

DBMS（データベース管理システム）上でデータやデータベースを操作・制御するためのもので、ユーザーからの命令でRDB（リレーショナルデータベース）にクエリをし、その結果を返す。
「SQL」はStructured Query Languageの略であり、語源のクエリ言語(Query Language)=問い合わせ言語は、データに対して問い合わせするためのコンピューター言語である。その中でもデータベースを扱うものを「データベース言語」と言い、SQLもその一つである。

データベース言語とコンピューター言語

データベース

そもそもデータベースとは、集めたデータをDBMSで整理し、操作できるようにしたもののことで、その中でも多く使われているRDBはテーブルでデータ同士の関係を表現している。

データベース言語

DBMSに処理を命令する言語であり、データの管理やデータを見つけ出すことのみが役割であるため、開発はできない非手続き型言語である。

コンピュータ言語

プログラム本体を作る時に使用する言語であり、処理の手続きを記載し、どう処理するかをコンピュータに指示する役割を持つ。

SQLを構成する3種の言語

  1. データ定義言語(DDL)
  2. データ操作言語(DML)
  3. データ制御言語(DCL)

1. データ定義言語(DDL)

DBMSで扱うオブジェクト(テーブルなど)の構造や、オブジェクト同士の関係を定義する。

【DDLの命令文】

  • CREATE：オブジェクト(データベースやテーブルなど)を定義
  • DROP：オブジェクトを削除
  • ALTER：オブジェクトの内容変更
  • TRUNCATE：データを全削除

2. データ操作言語(DML)

目的語や条件(を示すキーワード)と組み合わせて、データベースを操作する。

【DMLの命令文】

  • SELECT：データベース検索(テーブルから行を検索)
  • INSERT：データ挿入(テーブルに行追加)
  • JOIN：テーブルを結合
  • DELETE：データ削除(テーブルから行削除)
  • UPDATE：データ更新(テーブルの行更新)

3. データ制御言語(DCL)

データへのアクセスを制御する。 トランザクションやシステムを管理したり、ユーザーのアクセス権を制御したりする。

【DCLの命令文】

トランザクション

アプリケーションにとって意味のある処理の単位のことで、複数の処理のまとまりを指す。これらの処理同士の分離は不可能であり、処理結果は成功か失敗のどちらかである。

参考

Linux(Debian10.3)にPostgreSQLをインストールする

Linux PostgreSQL データーベース

【学習内容】

【目次】

はじめに

データベースの学習に伴いLinux(Debian)にPostgreSQLインストールをしたので、主にその手順のまとめと、ユーザーの追加方法を書いておきます。

環境

環境は以下の通り。

$ cat /etc/debian_version
10.3

$ psql --version
psql (PostgreSQL) 11.7

こちらLinux downloads (Debian) を参考にインストールを進めた。

【インストール手順 】

1.リポジトリ追加、パスを記載

/etc/apt/sources.list.d/pgdg.list のファイルを作成後、以下のリポジトリのパスを記載する。

# echo  deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main  > /etc/apt/sources.list.d/pgdg.list

2. CA証明書インストール

CA証明書(ルート証明書)を取得する。
今回は同時にwgetコマンドもインストールしている。

$ sudo apt install wget ca-certificates

3. PostgreSQL公開鍵を追加

PostgreSQLの公開鍵をインポートし、updateする。

$ sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

$ sudo apt-get update

4. PostgreSQLをインストール

DebianにはデフォルトでPostgreSQLが含まれているため、apt-getでインストールする。

$ sudo apt-get install postgresql-11

$ psql --version
psql (PostgreSQL) 11.7 (Debian 11.7-0+deb10u1)

PostgreSQLのインストールと同時にpostgresユーザー(データベースの管理ユーザー)が作成される。さっそくこのユーザーでログインする。

$ su - postgres
postgres@:~$

ユーザーの追加

次に新規ユーザーとPWの登録をする。

$ createuser --pwprompt --interactive <ユーザー名>

Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n)　→ y

作成したユーザーでログイン(データーベース接続)してみる。

$ psql -U <ユーザー名> -d postgres -h localhost

※ $ psql -d database -U user -h host
-d: データベース名(未指定だと、ログインユーザー名のデータベースに接続)
-U: ユーザ名(未指定だと、ログインユーザー名)
-h: ホスト名(未指定だと、localhost)

以下が表示されればOK

postgres=#

ユーザー一覧の確認は以下。

postgres=# \du

終了の際は以下。

-- psqlの終了
postgres=# \q

参考