CentOS から Windows上のSQLServerへの道のり

副題: Road to SQLServer on Windows. (^^;

備忘録です。

今まで、CentOS6からSQLServerへのアクセスは、FreeTDS を使っていました。PHPからはPDO_DBLIB 経由。これは結構簡単にセットアップできました。yum で FreeTDSとか、もっと簡単に、php-sybaseなりphp-mssqlなり入れれば依存関係で必要なライブラリは勝手にインストールされますしね。設定自体も、/etc/freetds.conf と /etc/locales.conf とかいじればあとは、PDOからアクセスできます。

で、非常に疎かったので知らなかったんですが、Microsoftから Linux向けのSQLServer ODBCドライバが供給されている、とのこと。

今更何言ってんの??? って感じですが、知らなかったものはしょうがない。説明通りにやってみます(^^;

# curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit root

$ sudo ACCEPT_EULA=Y yum install msodbcsql
$ sudo ACCEPT_EULA=Y yum install mssql-tools
$ sudo yum install unixODBC-devel
$ sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
$ sudo ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

エラーもなく、インストール完了。
ここまでは説明書通り。

さて、ここから設定。

/etc/odbcinst.ini を開くと、以下のセクションが追加されているはず。

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.6.0
UsageCount=1

そして、/etc/odbc.ini を編集します。僕の環境ではodbc.ini は空のファイルでした。
データソースを記述していきます。

[Development]
Driver = ODBC Driver 13 for SQL Server #odbcinst.ini に追加されたセクション名
Description = for development databse #適当
Trace = Yes #わからん。 
Server = 10.1.1.1 #稼働中のSQLServerのIPアドレスもしくはホスト名 ファイヤーウォールでアクセス許可を出すこと。
Port = 1433 #SQLServer Configuration Manager で TCP/IP を有効に。
Database = Sample  #データベース名

この状態で、まず、isql コマンドで接続してみます。書式は、isql [データソース名] [ユーザー名] [パスワード]ですね。

$ isql Development dbuser dbpass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from hogehoge
+------------+
|            |
+------------+
| 5500       |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL>

無事接続できました!

今度は、マイクロソフトが提供しているツールを使用してみましょう。

$ sqlcmd -S 10.1.1.1  -U dbuser
Password: xxxxxx
1> use Sample
2> go
データベース コンテキストが 'Sample' に変更されました。
1> select count(*) from hogehoge
2> go

-----------
       5500

(1 rows affected)
1>

接続できました。

次に、PHP で PDO_ODBC経由で接続してみます。
DSNには、odbc.ini で設定した、データソース名ではなく、ドライバ名・サーバー名・データベース名をそれぞれ直接指定してみました。

$ php -a
Interactive shell

php > $pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=10.1.1.1;Database=Sample','dbuser','dbpass');
php > $sth = $pdo->query('select count(*) from hogehoge');
php > echo $sth->fetchColumn();
5500
php >

接続できました。

日本語(UTF-8)が正常にCRUDできるか、まだテストしていませんが、とりあえずは、正常に接続できることを確認できたことでヨシとしましょう。

フリーのSSL証明書をお試し中

追記 2017/3/11
最新のChromeおよびFirefoxでは、現在 startssl の無料版のSSLサーバー証明書は拒否されます。将来のバージョンでは復活する可能性もなくはないと思いますが、現在のところ、無料で発行してくれるSSL証明書は let’s encrypt のみとなっているみたい。

このサイトも、startsslのものを使っていましたが、lets encrypt に変更しました。
OSのバージョンが理由で、公式ツールは使えませんでしたが、dehydratedというシェルスクリプトで運用することにしました。


ずっとこのドメインは オレオレ認証でSSL通信してたわけですが、なんか世の中的にはSSL(HTTPS)がデフォルトに、という方向を向いてるようなので、とりあえず、無料のSSL証明書をググってみたら、非商用個人向けに限ると「StartSSL」というのが主流だそうです。というか、無料だとこれしかないみたいです(^^;;;
詳しいことはよく分かりませんが、 非商用個人だと 有効期限1年のClass1のServer証明書(DV)を5ドメインまで発行してくれるみたい。

あと、つい最近正式にサービスが開始された、Let’s Encrypt というのがあって、Let’s Encrypt 総合ポータルによると・・・

無料で利用できる自動化されていてオープンな認証局(CA)です。公共の利益を図る目的で Internet Security Research Group (ISRG) が運営しています。

だそうです。特筆すべきは、商用利用も可能だということ。まぁ、でも、あくまでDV(Domain Validation)だけですので商用利用・・・っていうのはどうなんでしょうかねぇ???という感じです。ただ、有効期限が3ヶ月なので、SSL証明書の更新を自動化して運用するのが前提のサービスですね。


startssl  lets_encrypt

最初、Let’s Ecrypt を使ってみよう・・・と思ったんですが・・・僕が利用しているVPSのOSがCentOS5のままなので、yumでインストールできるソフトが古すぎて、Let’s Encryptで使用するCertbot クライアントを導入できませんでした(笑) OSのバージョンアップとかメンドクサイので諦め。

ってことで、StartSSL で SSL証明書を発行してもらってApacheに設定して、オレオレ認証局から卒業です(^^)

CSSで文字回転

HTMLで表組みを組んでいくと、カラム数が多くなるとどうしても↓の左端のように不格好になりますよねぇ。。。。
damedame
イラレとかで文字を縦書きにして描画してSVGで書き出して配置すれば一応解決しますが・・・できるならそんなメンドッチーこと抜きに、画像を使わずCSSだけで済ませたい・・・。

はじめは、writing-mode プロパティを使えば縦書きにできるやろ???? と思ってth要素にwriting-mode: vertical-lrと設定してみたけど、だめだった。それならば・・・と思って、<th>タグ内のテキストを<span>タグで囲ってやり、そのspan要素に writing-mode: vertical-lr;を設定すれば一応縦書きになってくれた。
でも、望んでいるものと違う。縦書きだとアルファベットが90度回転してしまうので、縦書きではなく、横書きのまま90度回転した状態になってほしいんだな。。。

で、CSS3ならtransformプロパティでテキスト回転できる!ってのをグーグル先生に教えてもらって早速やってみたんですが・・・これがなかなか、圧倒的な知識不足でスマートな指定が分からない。試行錯誤して下記のような感じにできました。
kaiten
しかし・・・やり方がスマートじゃない・・・・。

<!-- HTML 抜粋 -->
<tr>
  <th><span style="margin-top:3em;">ほにゃらら</span></th>
  <td>
     ナンチャラ<br>
     かんちゃら
  </td>
</tr>

で、CSSは

/* ベンダープレフィックスは省略 */
th {
  vertical-align: middle;
  text-align: left;
  width: 1em !important;
}
th > span {
  transform: rotate(-90deg);
  transform-origin: left top;
  display: inline-block;
  white-space: nowrap;
  position: absolute;
  line-height: 1;
}

ようするに、セル内のspan要素を反時計回りに90度回転。デフォルトでspan要素の中心で回転するので不揃いになるので、回転軸は 左端上に設定。これだけではダメで、左端で回転するのでせっかくth要素でvertical-align:middleにして縦中央表示させているのにずれてしまう。これを補正するため、span要素にわざわざstyle属性で、span要素内の文字数の半分の長さだけマージンを設定。

span要素を補正するのはスクリプトを使って、文字数を割り出して・・・って自動でやればいいかもしれない。

/* サンプル */
(function($)
{
  $('th > span').each( function(){ $(this).css('margin-top', $(this).width() / 2); } );
}(jQuery);

一応画像は使わずできたけど、正直やり方が強引過ぎて納得できない。たぶんもっとCSS3のプロパティとか探せばパシッとスマートな設定(プロパティ・値)があるのかもしれないけど・・・ザラッと調べてみた感じ見つけられなかった。

グーグル先生にどういうワードで検索すればヒットするんだろ・・・T(;_;)T 教えてエロい人!

twitterの「いいね」画像をダウンロード 修正版

追記 2017/07/15
C#に書き直した修正版 → https://ptsv.org/2016/04/20/tweet-image-download-agent/


2015年11月27日・29日 コード修正
コード修正 画像ファイルの正規表現の間違い修正・ちょこっと追加。
コード修正 URL を favorites => likes に変更

2015年11月23日 コード修正
2回目以降のtimelineのURLの決定方法が間違っておりましたので修正しました。ごめんなさい(m_m)

2015年11月17日 コード修正
ログインを行うコードを追加。これにより、ブラウザからクッキーファイルをエクスポートする手間を省くようにした。ログインコードはこれでいいのかどうかわからん。twitterってrails使ってんだっっけ? よくわからんが、適当。
本来はTwitter APIを使わないといけないと思うけど・・・API経由は正直メンドクサイ。勉強する気なし。(m_m)

C#で書き直したプロトタイプをさっき書いたので、これもデバッグが終わり次第また書こっと。
『perlなんかインストールしてねーよ、ボケ!』って方は、C#で書き直した方に ビルドしたやつを置いてます。


今年の2月ぐらいに書いたtwitter画像ダウンロードスクリプトが動かなくなったので修正・修正(^_^;;

2回目以降のタイムライン取得のURLが変更になったみたい?。昼休みにブラウザでアクセスしてデベロッパーツールでちょこっと解析したんですが・・・どのパラメーターで読めばいいのか、いまいち分かんないです。適当です。あくまで、自分用の備忘録なんで、すみません。取れればいいんです(m_m)

twitterにログインするところまで書きなおそうと一瞬思いましたが、また今度にします(・・;
ブラウザでログインしてクッキーエクスポート、twitter.comドメインだけ抜き出して、カレントディレクトリへcookie.txtというファイル名で保存。テキトーですまない。

使い方は下記参考。ログインしたcookie.txtが必要なのは、いいね(旧称:お気に入り)の取得のみ。他人のIDの「いいね」も同様にログインが必要です。この辺よくわからん。自分もしくは他人のIDのタイムラインにログインクッキーは必要ない。です。

#!/usr/bin/perl
=pod

=head1 ツイッターでのタイムラインから、画像のみダウンロードするスクリプト

使い方 targetに画像を取得したいアカウント名を指定する。

(いいね 画像の場合は type に favo をセット(デフォルト) 
(※ いいね 取得は自分以外のアカウントでも必ずログインが必要みたいです。)

> twitter.pl --username=xxxxx --password=yyyyy --type=favo --target=zzzz


(単に任意のアカウントのタイムラインの画像が欲しい場合は type に profileをセット)
(TLに流れているリツイートも含めた画像を取得したい場合は、timelineをセット)
(※通常公開されているアカウントのタイムラインではusename,passwordは必要ありません。
(※非公開アカウントではフォローを許可されたアカウントのusername/passwordが必要です。)

> twitter.pl --type=profile --target=公開アカウント名


ディレクトリ<tmp>に画像が吐き出されます。

=cut

use strict;
use warnings;
use LWP::UserAgent;
use Getopt::Long qw/:config no_ignore_case/;

my %CONFIG = (username => '', password => '', dir => './tmp', type => 'favo', target => '');
my %TIMELINE = (favo    => 'https://twitter.com/%s/likes/timeline',
                profile => 'https://twitter.com/i/profiles/show/%s/media_timeline',
                timeline => 'https://twitter.com/i/profiles/show/%s/timeline');

#エージェント
my $UserAgent = LWP::UserAgent->new(cookie_jar => {});

&{sub
{
  my @argv = @_;
  my $result = Getopt::Long::GetOptionsFromArray(\@argv,
                                                 'username=s' => \$CONFIG{username},
                                                 'password=s' => \$CONFIG{password},
                                                 'type=s'     => \$CONFIG{type},
                                                 'dir=s'      => \$CONFIG{dir},
                                                 'target=s'   => \$CONFIG{target});

  exists $TIMELINE{$CONFIG{type}} or die "invalid type....\n";
  $CONFIG{dir} || die "specified output directory...\n";
  $CONFIG{target} || die "specified target account name...\n";

  mkdir $CONFIG{dir} unless(-e $CONFIG{dir});

  &use_lwp_agent;

}}(@ARGV);

sub use_lwp_agent
{
  my ($param,$result) = ('?include_available_features=1&include_entities=1','');

  if($CONFIG{username} && $CONFIG{password})
    {
      my $response = $UserAgent->get('https://twitter.com/login');
      $result = $response->decoded_content;

      $result =~ /<input type="hidden" value="([\d\w]+?)" name="authenticity_token"(?:\s*\/)?>/ or die "can not detect authenticity_token...\n";

      my $auth = $1;
      $response = $UserAgent->post('https://twitter.com/sessions',
                                   {'session[username_or_email]' => $CONFIG{username},
                                    'session[password]'          => $CONFIG{password},
                                    'authenticity_token'         => $auth,
                                    'remember_me'                => '1',
                                    'redirect_after_login'       => '/' }) or die "can not access login page... \n";

      $result = $response->decoded_content;
      die "failed to login...\n" if($result =~ /error/);
    }

  my $url = sprintf($TIMELINE{$CONFIG{type}},$CONFIG{target});
  do
    {
      my $timeline = $url.$param;

      print "---- getting and parsing\n$timeline ...\n----\n";
      $result = &lwp_agent($timeline,'-') || die "can not get timeline. may be wrong url.\n";

      $param = &get_images($result,\&lwp_agent);

    } while( $param );

  print "done!\n";
}

#タイムラインのJSONデータから画像を取得して、次のタイムラインのパラメータを返す。
sub get_images
{
  my ($json,$agent) = @_;

  $json =~ s/\\\//\//g;
  foreach my $url_($json =~ m!https://pbs\.twimg\.com/media/[\w\-]+\.\w{3,4}(?::large)?!g)
    {
      if($url_ =~ m/([\w\-]+\.\w{3,4})(:large)?$/)
        {
          my $basename = $1;
          my $filename = "$CONFIG{dir}/$basename";
          if($2)
            {
              $url_ =~ s/:large/:orig/;
            }
          else
            {
              $url_ .= ':orig';
            }

          unless(-e $filename)
            {
              print "fetching: $url_\n";
              &$agent($url_,$filename);
              print "saved $basename\n";
            }
        }
    }

  my @ids = $json =~ m/data-tweet-id=\\\"([0-9]+)/g;
  my $max_id = @ids > 0 ? pop @ids : '';

  return $max_id ? "?max_position=${max_id}&include_available_features=1&include_entities=1" : undef;
}

#URLを取得して返す。
sub lwp_agent
{
  my ($url,$ofile) = @_;
  my %options = ();

  if($ofile ne '-')
    {
      if($ofile eq ':src')
        {
          if($url =~ /([\w\-\.%]+?\.\w)$/)
            {
              $ofile = "$CONFIG{dir}/$1";
            }
          else
            {
              goto cleanup;
            }
        }

      return $UserAgent->get($url,':content_file' => $ofile);
    }

cleanup:
  if(my $response = $UserAgent->get($url))
    {
      return $response->decoded_content;
    }

  0;
}

__END__

iframe要素のsandbox属性

先日、iframeを使用している自作のjQueryプラグインを使って構築しているWebアプリの管理画面が突然正常に動かなくなった!

ブラウザの開発者ツールのコンソールから原因を調べたら、iframe要素内で読み込んでいる同じドメイン内のリソースからフォームを投げたり親ウィンドウへのアクセスがことごとくエラーになっていたorz コンソールには、allow-modalsがなんとかかんとか、という見たこともないエラー。

でも、エラーになるブラウザはchromeだけ。FireFoxやIEは問題なかったので、たぶんchromeの最近のバージョンアップでiframe要素のセキュリティが高くなってしまったのかなー、とよくよく調べたら、下記sandbox属性をiframeに追加すればちゃんと動くようになった。どうやらHTML5.1にchromeが対応しただけ? (というか、HTML5.1なんて、そんなもんあったの?無知は嫌だねぇ(_ _) 常にアンテナはっとかないとダメですよねぇ・・・)

  • allow-modals
  • allow-pointer-lock
  • allow-popups
  • allow-popups-to-escape-sandbox
  • allow-top-navigation

名前からだいたいの想像はつくんですが・・・allow-modalsとかallow-popupsとか違いが分からない。また今度時間あったら調べよう。

もともと、<iframe sandbox=”allow-same-origin allow-forms allow-scripts”> という風にしてたんですが・・・もっと調べたら、上記5つのsandbox属性は、HTML5.1から追加された?もののようですね。
(この辺、自分ではよく分かってません(_ _)

ってことは、このまま放っておくと、InternetExplorerはともかく、いずれはFireFoxもMicrosoft Edgeも動かなく・・・。このプラグイン使っているところは、修正したプラグインのJSファイルだけ上書き全部しないとなー。

根本的にはiframe要素をすべて排除して、全てajaxな作り方すれば良かったんですけどね・・・中途半端は良くないですね・・・でも、iframeって便利なんですよね(^.^;;;