DBのプライマリキーは複合主キーではなく、サロゲートキーを使うべし
別件でUnicodeの「サロゲートペア」について調べていたところ、データベースの「サロゲートキー」と「複合主キー」についての記事が サロゲート つながりで引っかかって来て思いっきり脱線しました（笑） そんなこんなで、あまりデータベースに慣れ親しんでいないと、「サロゲートキー」「複合主キー」「ナチュラルキー」って何？──となる人は多いはずなので、自分自身のおさらいも兼ねて一度まとめておこうかと思った次第。 プライマリキー（主キー） データベースのたいていのテーブルに存在する、データを一意に管理するためのキー制約。このキーが指定された列中には重複する値を入れることができず（ユニークキー制約）、空白（NULL）にすることもできない。 ユニークキー（一意キー、ユニークインデックス） このキーが指定されている列中には重複する値を格納できず、一意にならなければならないというキー制約。ただし、プライマリキーと異なり空白（NULL）は格納可能で、空白（NULL）のみは重複ができる。このキーが指定されると同時にインデックスも貼られるため、ユニークインデックスと同義である。 サロゲートキー（代理キー） オートインクリメント属性等により、連番の数値が格納されているユニークな値を持つカラムに対してプライマリキー制約を付与した場合のプライマリキーのこと。テーブル内で一意となる唯一の行レコードを特定するための列の集合（ナチュラルキー）を代替するため代理キーとも言われる。単一の列で全行レコードの一意性を識別できるのでシステム的に有益なのだが、実サービス等に利用するデータとしては意味をなさず、データ利用者側の観点からだとデータベースの物理ストレージ領域を無駄に圧迫する列でしかない。 ナチュラルキー（自然キー） テーブル内の行レコードを特定するためのユニークキー制約のついた列の集合のこと。行レコードの一意性が識別できれば単一の列でもかまわないが、例えば、WordPressだと「投稿ID」「タグID」の組み合わせで、とあるタグに属する投稿データを一意に識別できるような場合、それら2列の集合体がナチュラルキーと呼ばれ、そのナチュラルキーを持つテーブルがwp_term_relationshipsだ。原則的にナチュラルキーに含まれる列はユニークキー制約をもっていなければならないため、プライマリキーに設定されていることが多い。データ利用者側の視点では、意味あるデータのみによって一意性が識別されるナチュラルキー型テーブルは用途が明確で無駄がない（と見えるようだ）。 複合主キー 複数プライマリキー、サロゲートキー＋ナチュラルキー、プライマリキーなしの複数列集合のナチュラルキーのみといったテーブルが複合主キー型のテーブルといえる。要は単一列のみで行レコードの一意性が識別できないタイプのテーブルである。他のテーブル同士をリレーションさせるために外部キーの結合のみを管理するようなリレーションテーブルによく見られるタイプ（ナチュラルキーの項で紹介した、WordPressのwp_term_relationshipsテーブルなど）。リレーションテーブルを覗いたことがある人やDB設計したことがある人には良く理解できるはずだが、このような複合主キー型テーブルはそれぞれの外部テーブルのサロゲートキーを集中管理するような構造になっていて、実データのリレーション関係が一目ではわからないのが常だ。かといって、ナチュラルキーをプライマリキーとしたテーブルのプライマリキーを外部キーとしてリレーションさせるとそのリレーションテーブルを見るだけでデータの結合性が一目でわかるものの、参照コストの高い文字列データ等がデータベース内に重複して存在することになって、データベース全体としてパフォーマンス低下を招く上、物理ストレージ領域の圧迫もサロゲートキー以上に進んでしまう。 また、特にナチュラルキーが一意性識別に関与するようなテーブルだと、ユニークなデータを取り扱う際のシステムコストが高くなる。 言葉の整理をしてて改めて感じたのだが、複合主キーのテーブルを操作するのは結構面倒そうだということ。まぁ、実際に面倒なんですがね。例えば、複合主キーのナチュラルキーのうち一つの列の値を変更したい場合、その行レコードを特定するために同じナチュラルキーを検索条件に入れなければならなかったりして、ちょっと処理を考えるのがイヤになる。 プライマリキーが二つ以上あるテーブルをUPDATEしようとするとMySQLに怒られたりするしね…。 私個人の結論としては、データベースは人ではなく、システムが応答し易いように作った方が良く、パフォーマンスを犠牲にしてまで人間側のデータ視認性にこだわるのは本末転倒だと思いますね。人間側に認識し易いデータは別途中継処理等を作って整形してあげるのがデータベースを利用するシステムの正しいカタチではないかと。なぜなら、人間側が要求するデータは、時と場合と人によってフィルタリングさせないと有益なデータにならないケースがほとんどだから…。つまり、プライマリキーは、複合主キーとかは使わず、サロゲートキーを使うのがベストプラクティスだと思うわけです。...Amazon Linux に Moodle2.8.2 をインストールする方法
eラーニングパッケージMoodleをインストールする機会がありまして速攻でggったところ Amazon EC2 での Moodle セットアップ方法 がヒット。 あまりに詳細な内容に勝ったな、、と思いつつセットアップしたところMoodleバージョンアップによる地雷が埋まっていたため地雷除去メモを作成しました。 Amazon Linux AMI 64biをセットアップ <-変わらず phpとMySQLのインストール <- ココ！ 上記リンク先の手順通りやると、以下のメッセージにぶちあたります。 Moodle 2.7 or later requires at least PHP 5.4.4 (currently using version 5.3.29).<br />Please upgrade your server software or install older Moodle version. てか、Amazon LinuxのPHPって5.3なのかよ！ ということで、以下のコマンドでphp5.5,MySQLをセットアップ apache2.4とphp55-mysqlndあたりがポイントです。 sudo yum -y update; yum install httpd24 mysql-server git php55 php55-gd php-pear php55-mbstring php55-mcrypt php-zts php55-xmlrpc php55-soap php55-intl php-zip php55-xml memcached php55-mysqlnd php55-pecl-apc php55-common sudo chkconfig mysqld on sudo chkconfig httpd on sudo service mysqld restart sudo service httpd restart sudo mkdir /var/www/moodledata sudo chown apache:apache /var/www/moodledata 3....AWS ： EC2 C4インスタンスリリース！
今月の初め、AWS より EC2 サービスにて新しいタイプのインスタンス、 C4 インスタンスの提供開始とのアナウンスがありました。 トピックを共有したいと思います。 Intel が AWS 専用にカスタマイズして提供している Haswell ベースの CPU を採用。 EBS 最適化オプションを標準装備。その代りSSD ベースのインスタンスストレージは無し。 利用できる仮想化タイプは hvm のみ。PV (paravirtual) は利用できません。 拡張ネットワーキングも標準装備され、低遅延化などが施されている。 パフォーマンスの劇的な向上や新しい何かを期待されていたユーザー様は残念がっている方もいるようですが、 HVM のみのサポート、CPU、ストレージ、ネットワーク周りの底上げを行っており、インフラとして順当な進化を遂げているなぁ、というのが僕の感想です。...AWSのCloudwatchにアラート(Alarm）を設定し、監視する
こんにちは。新人の橋本です。 これまではオンプレミスのハイブリッドクラウド(プライベート+パブリック)や物理サーバにて運用構築業務についてましたが、 AWS自体は初めて取り扱う環境ですので、その目線から、ブログを書いていきたいと思います。 今回はAWS導入後、基本的なリソース監視ができるCloudwatchの設定、およびAlarmを設定し、閾値を超えたらEメールを送付するという ごくごく基本的な設定について記載します。 1)設定画面 EC2にてインスタンス作成後、EC2の[Instance]項目にてにてAlarm設定したいインスタンスを選択し、[monitoring]タブを選択します。 2)Alarm設定 右部分にある[Create Alarm]を選択すると、CreateAlarm画面が表示されるため、各項目を入力します。 Send a Notification to → 別項目にて、SNS topicを作成している場合、リストに追加されます(今回は追加しません) Take the action → Alarm発生時のアクション。発生した場合どうするかを選択できます(今回は設定しません) Whenever → リストより程度(Average,Max,Minimumなど)、および項目(CPU Utilization、Disk Usageなど)を選択します Iｓ → 閾値(○○パーセント以上or以下など)を設定します For at least → 判定条件(○分間毎に△回[Whenever]項目が[Is]だった場合にAlarmカウントする)を設定します Name of Alarm → Alarm名を設定します 3)設定追加 各項目設定後、右下の[Create Alarm]を選択すると、Alarm項目が作成され、次の画面が表示されるので、リンクをクリックします。 4)項目追加確認 CloudWatch Management Console画面に遷移するため、項目が追加されたことを確認し、[Modify]タブをクリックします。 5)メール送信設定① [Modify Alarm]画面が表示されるため、[Actions]項目の[+Notification]をクリックします。...【AWS】CloudWatchを使ってEC2インスタンスのリソース状況を確認する
はじめまして、ディレクターの白川です。 AWSではGUIから様々なサービスが利用出来るので、ディレクター職の自分にも非常に優しくていいですね。 さて、Webサービスを運用していくためには、インフラの稼働状況を監視することが必要不可欠です。 機器自体の死活監視に加え、メモリやCPU、ディスクなどのリソース使用状況を常にモニタリングし、負荷状況に応じて適切な対応を行わなければなりません。 そこで今回は、AWSが提供するクラウド監視ツール、CloudWatchを利用してEC2インスタンスのリソース使用状況を確認する方法についてご紹介します。 １．CloudWatchの確認方法 AWSマネージメントコンソールにアクセスし、メニューの中からCloudWatchを選択します。 画面左の「Dashboard」の中から、[EC2]を選択します。 インスタンス毎のIDとメトリクス名が表示されます。グラフを表示したいインスタンスとメトリクスの組み合わせを確認し、チェックボックスにチェックを入れます。 画面下にグラフが表示されます。 ※図はCPU使用率を表示したイメージです。 ２．グラフの見方 [Average]というドロップダウンボックスが、各データの集計方式(Statistics)を指します。 それぞれ、平均(Average)、最小値(Minimum)、最大値(Maximum)、合計(Sum)が選択出来ます。 ※データサンプル数(Data Samples)は、サンプルとして取得したデータの数になります。こちらは、CPUやメモリなどの使用状況を確認する上ではあまり使用しないかと思います。 ３．CloudWatchで確認出来る、EC2の標準メトリクス CloudWatch上で確認出来るEC2のメトリクスなどは、下記のAWSドキュメントを確認ください。 参考： AWSドキュメンテーション「Amazon EC2 メトリックスを表示する」 ４．まとめ CloudWatchでは、今回ご紹介したEC2のリソース以外にも、RDSやELB、Auto Scalingなど様々なAWSリソースの監視が可能です。 ただし、CloudWatchの統計は2週間しか保存されないため、長期的なスパンでのリソース分析には向きません。 そういった場合は、Zabbixなどの高機能な監視ソフトウェアの利用を検討する必要があります。 ISAOでも、Zabbix等を利用した高レベルなクラウド監視実績が多数ありますので、ご検討の際は是非ともお声掛けください。...Rubyのややこしい配列とハッシュとシンボルについて整理してみた
皆様、あけましておめでとうございます。 さて、2015年の初投稿は、私的に今最も熱いRubyネタで始めようかと思います。 まず基礎のおさらいとして、Rubyにおける配列には一次元配列のarrayクラスオブジェクトと多次元配列（連想配列）であるhashクラスオブジェクトの二種類があります。それぞれの配列オブジェクトは定義する際に要素を囲い込むリテラル記号によって以下のように区別されています。 array = ["A", "B", "C"] # 配列変数arrayを定義 hash1 = {:first => "A", :second => "B", :third => "C"} # ハッシュhash1を定義 一次元配列であるarrayクラスオブジェクトは各要素値に数値添字が自動で割り振られるため、数値添字のインデックスにて各要素にアクセスができます。 array = ["A", "B", "C"] puts array[0] # "A"が表示される 一方hashクラスオブジェクトはキー・バリュー型のオブジェクトなので、文字列型の添字（キー）で要素値にアクセスができます。 hash2 = {"first" => "A", "second" => "B", "third" => "C"} puts hash2["first"] # "A"が表示される さて、ここで一番最初の例で定義したhash1とhash2では、キーの指定の仕方が異なっていることに気づきましたでしょうか？ hash1とhash2は要素値こそ一緒ですが、キーが異なるため同じハッシュではありません。...AWS SDK for Rubyを使ってEC2インスタンスのステータスを確認する
アプリケーション側で、任意のAWSのEC2インスタンスについて、現在の稼働状況をリアルタイムに確認したい時がある。例えば外部アプリケーションからEC2インスタンスを起動させたり、停止させたりする場合などに、インスタンスの稼動状況を確認してステータスが変わったら次のプロセスを実行したいとかいうケースが、それに当てはまる。 SDK for Rubyでは、AWS::EC2クラスのclient.describe_instance_statusメソッドで特定のECインスタンスのステータスを取得することができる。インスタンスのステータスにはsystem_statusとinstance_statusの2種類があって、正確な稼動状態を確認したい場合はこの2つのステータスを取得する必要がある。 そして、注意しないといけないのが、インスタンスが停止している時はステータスが存在していないということだ。つまり、停止しているインスタンスに対してclient.describe_instance_statusメソッドを実行した場合、ステータス情報が格納されているinstance_status_setオブジェクトの中身が取得できないので、それを踏まえてコーディングしないと、ステータス取得エラーとなってしまう。 例えば、停止状態のインスタンスを起動した場合のステータスを監視する時など、最初はインスタンスが停止しているので、instance_status_setオブジェクト内にステータスがないということをあらかじめ想定して処理を作る必要がある。 また、ターミネートされたインスタンスはclient.describe_instance_statusメソッドを実行する対象自体がないことので、こちらも注意する必要がある。 さて、上記もろもろを踏まえてEC2インスタンスのステータス確認するRubyプログラムを作ってみた。 chkins.rb # encoding: utf-8 require "aws-sdk" def check_instance_status ec2 = AWS::EC2.new( :access_key_id => Params[:access_key_id], :secret_access_key => Params[:secret_access_key], :region => Params[:region] ) AWS.memoize do status = [] if ec2.instances[Params[:instance_id]].exists? ec2info = ec2.client.describe_instance_status({ 'instance_ids' => [Params[:instance_id]] }) if ec2info.instance_status_set.empty? # instance has stopped status << 'stopped' message = '%s has stopped' else ec2info....Mac どこでもターミナル
最近ubuntuからmacに乗り換えました。 これでiPhoneアプリ開発し放題だぜ！(ウソ しかし同じunix由来のOSとはいえ、異なる点もちらほら。 一番気になるのが、基本いちアプリ、いちウィンドウである点。 たくさん仮想デスクトップを生成してあっちこっちでターミナルを立ち上げたい派の私としては、この仕様がイマイチなじまないのです。 まあ仮想デスクトップ使いまくり、ターミナル立ち上げまくりなのはubuntuerの悪いクセだとは思いますが。 複数のターミナル立ち上げようと思ったら 1.spotlightでterminal.appを呼び出す。 2.⌘ + N で新しいターミナルを表示 3.表示されたターミナルを目的の仮想デスクトップまでドラック だるぃ…. ggってみると、スクリプトをアプリケーション化する方法があるみたいなので作ってみました。 https://blog.colorkrew.com/uploads/T.zip zipを展開してApplicationsに放り込めば使用可能です。 ▪️使用方法 1. spotlightでT.appと入力(t.で補完されるはず) 2. 今いる仮想デスクトップでターミナル起動！ やってることは open -n /Applications/Utilities/Terminal.app だけです。 では良いターミナルライフを！ ▪️参考 技術/MacOSX/シェルスクリプトを".app"(“Bundle”)化する http://www.glamenv-septzen.net/view/1201...MySQL5.5以降 sjis環境でDATETIMEにindexが効かず遅い
MySQLを5.6にバージョンアップした際に、一部のクエリがやたら遅くなってしまい困りました。 散々ggった結果、ヒットした情報が以下。 character_set_connection = sjis でDATETIME型のカラムにインデックスがきかないはなし http://bugs.mysql.com/bug.php?id=74449 なんと、character_set_connection = sjis の場合に限ってDATETIME型,DATE型カラムに誤った型変換が行われるらしい。 sjisなんてやめなはれ、は正論ですが、残念ながらsjisを捨てられるシステムではないので、以下のようにCASTで対処。 date型の場合 AND dl.date >= '2014-08-10' ↓ AND dl.date >= cast('2014-08-10' as date) datetime型の場合 AND dl.date >= '2014-08-10' ↓ AND dl.date >= cast('2014-08-10' as datetime) ご参考まで。...Capistrano3のタスク内でトップレベルのタスク名を取得する
Capistrano3でタスク書いていて、タスク内でcapコマンドとして実行されたトップレベルのタスク名を引いて、処理を分岐させる必要が生じて、そのトップレベルのタスク名取得に苦労したので、ここに備忘録化しておく。 基本的にCapistrano3でタスク内で自分のタスク名を取得したい場合、次のようにブロックの引数として引ける。 desc "main task" task :main_task => :sub_task do |task| run_locally do info ": This task name: #{task}" info ": Running main deploy task!" end end desc "sub task" task :sub_task do |task| run_locally do current_task = task.name_with_args.split(':').last info ": This task name: #{current_task}" end end 上記タスクを実行してみると、こうなる。 $ cap test deploy:main_task INFO: This task name: sub_task INFO: This task name: deploy:main_task INFO: Running main deploy task!...素のRubyでMySQLクエリの結果を取得する
Ruby+MySQLの処理をする時、たいていはMySQL操作系のライブラリでruby-mysqlやmysql2とかを使うケースが多いのだろうが、それらのライブラリなしの素のRuby環境でMySQLのクエリ操作を行う必要があったので、その際に書いたソースを汎用化してまとめてみた。まぁ…ほとんどニーズはないかもしれんが、一応TIPSとして共有化しておこうかと。 mysql_query.rb db = { :db_config => "~/.user.cnf", :db_name => "database_name" } query = "SELECT * FROM db_table_name WHERE primary_key_id = 1;" res = `/usr/bin/mysql --defaults-extra-file=#{db.fetch(:db_config)} -D #{db.fetch(:db_name)} -e \"#{query}\" ` if res.empty? then p "data is none." else lines = res.split("\n") columns = lines.first.split("\t") results = [] lines.each_with_index { |line, i| if i > 0 then tmp_hash = {} values = line....authorクエリを利用したWordPressのユーザー名漏洩を防ぐ方法
DEVLABはWordPressで運用しているのだが、そのWordPressについて気になる記事を見つけた。 WordPressサイトのホームURLに「?author=x」のGETクエリをつけることで、サイト内のユーザー名がばれてしまう というものだ（※ 詳しくはMT SystemsさんのWordPress TIPを参照）。?author=xのxは数値で、WordPressのユーザーID（ユーザーテーブルのプライマリキー）となる。つまりxに1を指定すると、WordPressのルート管理ユーザーになるわけだ。 さっそく、DEVLABでも試してみたところ･･･ http://devlab.isao.co.jp?author=1がみごとにパーマリンク設定で指定されているリライトルールに沿ってリライトされ、https://blog.colorkrew.com/author/<ルート管理ユーザー名>/にリダイレクトされてしまった。このままだと、管理ユーザーのユーザー名に対してパスワードの総当りをされて、もしログイン成功とかされちゃうと、サイトがクラッキングされてしまうじゃないですか！ ただ、DEVLABの場合、一般的なWordPressのログイン画面wp-login.phpは無効化してあって、ログイン方法を探すのが大変なので、一応は安全ではある。しかし、ユーザー名が漏洩してしまう脆弱性があるのは防止しないといけないので、対策を考えてみた次第。 MT Systemsさんのサイトで紹介されているように、著作者アーカイブページのテンプレートauthor.phpによってリダイレクトしてしまう方法ではHTTPのレスポンスヘッダにリダイレクトのlocationとしてユーザー名が出力されてしまうため、たとえ.htaccessにrewriteルールを追加したとしても防止できないのが厄介だ。 MT Systemsさんのサイトでは、最終的にユーザーデータのuser_nicenameを書き換えて、ログインアカウントであるuser_loginと異なる値にしてしまう対処策が掲載されていたんだが、管理パネルから直接編集できない項目でもあって、なかなかに難儀である。 もうちょっと簡単に対策できないものか…。 ──と、言うわけで、対策方法を自作してみた。 // prevent the leakage of user name by author query on WordPress. function knockout_author_query() { // disable author rewrite rule global $wp_rewrite; $wp_rewrite->flush_rules(); $wp_rewrite->author_base = ''; $wp_rewrite->author_structure = '/'; // for author query request if (isset($_REQUEST['author']) && !...Capistrano3でタスクが二重起動してしまう時の対処法
Capistrano3でステージ環境を変えてタスクを実行した時に、タスクが二重起動するという症状に陥った。二重でタスクが実行されるので、ラウンチするAWSインスタンスを“2つ”と設定していても“4つ”起つし、MySQLに発行するクエリも2倍になって、INSERTするレコードが重複して挿入される。一時的にファイルに書き出していた設定なども二重起動している後続タスクによって上書きされてしまい、もうデプロイはしっちゃかめっちゃかな状態だった。 解決できたので、その時の対処法を備忘録として残しておく。 原因はinvoke設定でデフォルトのデプロイ環境を指定していたためだった。 はじめ、試験環境でデプロイタスクの開発を行っていた時、Capfileに下記のような設定を書いていた。 Rake::Task[:develop].invoke invoke :develop これを書いておくと、デフォルトデプロイ環境がdevelopに固定化されるので、デプロイコマンドを実行するときに本来ならデプロイ環境（ステージ名）を指定して、 $ cap develop deploy:task_name のようにするところを、 $ cap deploy:task_name と、デプロイ環境を省略できてちょっとだけ楽だったのだが、これがデフォルトデプロイ環境以外にデプロイを行う時に悪影響を及ぼしたのだ。 今回、試験環境でデプロイが上手くいったので、次に本番環境でデプロイを行うことになり、デプロイ環境（ステージ名）をproductionで実行することになった。デプロイ内容は試験環境とまったく同一だったため、デプロイタスク設定の差分はなく、ロール設定やSSH接続設定も同じだったため、config/deploy/develop.rbをコピーしてconfig/deploy/production.rbを作成していた。 この状態で、下記のようにデプロイ環境を指定してデプロイを実行した次第。 $ cap production deploy:task_name これだと、省略されているデフォルトデプロイ環境へのデプロイも同時に起動してしまうわけです。実際には、 $ cap develop production deploy:task_name と環境を二つ指定してデプロイを実行しているのと同じことになっているわけです。 この状態を解消するには、前述のinvoke設定を削除するだけです。 というか、複数環境に対してデプロイが発生する時は混乱の元になるんで、invoke設定とかはしない方がいいですね。 いやはや、何気に解決するまで数時間ハマってました。今後は注意しないと。...Capistrano3でEC2インスタンス新規作成から初期設定までのデプロイ（まとめ）
ここまでAWSのEC2インスタンスを新規作成して、そのインスタンスに対しての初期設定までを、Capistrano3でタスク化することをやって来ました。難儀したものの、ようやくEC2インスタンスの準備が出来て、あとはミドルウェアやアプリケーションをインストールするだけ──というところまでたどり着きました。そこで今回は、これまでのデプロイの流れを一度総括してまとめてみようかと思います。 まず、Capistrano3を稼動させるデプロイサーバの準備から。公式のAmazon Linux環境などのEC2インスタンスをAWSマネージメントコンソール等からラウンチして、ログインしたら、Capistrano3をインストールします1。 # yum update -y # yum groupinstall -y "Development Tools" # openssl version OpenSSL 1.0.1h-fips 5 Jun 2014 # openssl version -d OPENSSLDIR: "/etc/pki/tls" # curl -L https://get.rvm.io | bash -s stable # source /etc/profile.d/rvm.sh # rvm list （※ rvm rubies と表示されればインストール完了） # ruby -v ruby 2.0.0p451 (2014-02-24 revision 45167) [x86_64-linux] # rvm install 2....