リモートデスクトップで Windows10(1607) をシャットダウン/再起動する方法

以前、こんな記事を書いた。

shobon.hatenablog.com


先日、Windows 10 バージョン 1607 を適用したところ、スタートメニューが変わって、リモートデスクトップでも簡単にシャットダウンできるようになった。

f:id:savakan:20160925084821p:plain

うっかりクリックしないよう注意が必要か・・・。

CentOS 7 の psql で tab 補完が大文字にならない件

今更だけど、人に聞かれたついでにメモ。

psql で tab を押してSQLキーワードを補完できるが、バージョンによって大文字だったり小文字だったり違いがある。

CentOS 6 / psql (PostgreSQL) 8.4.20

db=> sel
 ↓ tab
db=> SELECT 
 →SQLキーワードが大文字で補完される


CentOS 7 / psql (PostgreSQL) 9.2.15

db=> sel
 ↓ tab
db=> select
 →SQLキーワードが大文字ではなく、小文字で補完される

PostgreSQL 8.4/9.2 で何が変わったかを、ドキュメントで確認。

8.4
https://www.postgresql.jp/document/8.4/html/app-psql.html
 →COMP_KEYWORD_CASE の項目が無い

9.2
https://www.postgresql.jp/document/9.2/html/app-psql.html
 →COMP_KEYWORD_CASE の項目がある

SQLキーワードを補完する時に大文字小文字のどちらを使用するかを決定します。
lowerまたはupperが設定された場合、補完された単語はそれぞれ小文字または大文字になります。
preserve-lowerまたはpreserve-upper(デフォルト)が設定された場合、 補完された単語は入力済みの文字の大文字小文字を引き継ぎますが、何も入力されていない場合はそれぞれ小文字または大文字に補完されます。

つまり、途中まで大文字で入力されていれば、大文字で補完される。

CentOS 7 / psql (PostgreSQL) 9.2.15

db=> SEL
 ↓ tab
db=> SELECT

以前のバージョンと同じ動作(途中まで小文字でも、大文字で補完する)にしたければ、psql のパラメータで変更できる。

db=> \set COMP_KEYWORD_CASE upper

これをデフォルトにしたい場合は、~/.psqlrc へ記載する。

SourceTree for Windows 1.8.3 → 1.9.5 へ更新したら

git操作アイコンの配置が変わりました。

1.8.3
f:id:savakan:20160723134032p:plain


1.9.5
f:id:savakan:20160723134114p:plain


プッシュとフェッチの位置が逆に(´・ω・`)


1.8 の時にあったコミットメッセージ欄で日本語を入力できない(フォーム内で右クリックすればOK)問題は解消されている。

ORDER BY で CASE文を使って特定の行を上位に表示する

CentOS 6.x の PostgreSQL 8.4 と SQLite3 で動作確認。

何らかのデータをソートして表示する際、特定の行だけは上位に表示したい事がある。

例えば、国マスタをコード順でソートした時に、日本を一番上に表示し、他はコードでソートして表示したい、等々。


単純にコードでソートした場合の例

db=> SELECT * FROM country ORDER BY iso;
  iso   |                    name
--------+--------------------------------------------
 AD     | アンドラ
 AE     | アラブ首長国連邦
 AF     | アフガニスタン
 AG     | アンティグア・バーブーダ
 AI     | アンギラ
 AL     | アルバニア
 AM     | アルメニア
 AN     | オランダ領アンティル
 AO     | アンゴラ
 AQ     | 南極


ORDER BY にCASE文を使った場合の例

db=> SELECT * FROM country ORDER BY CASE iso WHEN 'JP' THEN 1 ELSE 2 END, iso;
  iso   |                    name
--------+--------------------------------------------
 JP     | 日本
 AL     | アルバニア
 DZ     | アルジェリア
 AS     | サモア
 AD     | アンドラ
 AO     | アンゴラ
 AI     | アンギラ
 AQ     | 南極
 AG     | アンティグア・バーブーダ
 AR     | アルゼンチン
 AM     | アルメニア
 AW     | アルバ


注意点は、下記のように、ORDER BY の条件でCASE文だけにすると、ELSE部分のソートが意図した通りにならない事がある。
CASE文だけでなく、本来ソートしたかった条件も必要。

失敗な例

db=> SELECT * FROM country ORDER BY CASE iso WHEN 'JP' THEN 1 ELSE 2 END;
iso    name
-----  ---------------------------------
JP     日本
AF     アフガニスタン
AL     アルバニア
DZ     アルジェリア
AS     サモア
AD     アンドラ
AO     アンゴラ
AI     アンギラ
AQ     南極


同様に、都道府県の場合の例(東京都を一番上に表示、他はコードでソート)

db=> SELECT * FROM pref ORDER BY CASE cd WHEN 13 THEN 1 ELSE 2 END, cd;
 cd |   name
----+----------
 13 | 東京都
  1 | 北海道
  2 | 青森県
  3 | 岩手県
  4 | 宮城県
  5 | 秋田県
  6 | 山形県
  7 | 福島県
  8 | 茨城県
  9 | 栃木県


国のデータは以下を利用。
国名と国コードの対応表

SQLiteでIPソート

SQLiteIPアドレスを意図した順にソートする話。

複雑なわりには、使いどころが微妙(´・ω・`)


PostgreSQL では inet 型にCASTすると簡単なものの、SQlite には inet型は無い。

0詰めしてソートしようとしても、PostgreSQL の「split_part」や「lpad」に相当する組み込み関数が無い。


どうにかできないか考えたところ、「SUBSTR」と「INSTR」を使えば何とかできそう。

但し、SQLite の「INSTR」は、バージョン 3.7.15 以降で利用可能。

なお、CentOS 7.2.1511 の標準リポジトリから yum でインストールされている SQLite は下記の通り。

$ sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

CentOS 6.8ではバージョンが低いので未対応。

$ sqlite3 --version
3.6.20


では、SQLite の「SUBSTR」と「INSTR」を使って、テキストとして保存されたIPアドレスを0詰めしてソートしてみる。

準備として、結果を見やすく設定。

sqlite> .header on
sqlite> .mode column
sqlite> .width 15


最初に、普通にソートした場合。

sqlite> SELECT ip FROM network ORDER BY ip;
ip
----------------
10.1.10.10
10.20.10.10
10.3.10.10
10.3.10.5
192.168.1.1
192.168.10.1
192.168.2.1
192.168.20.1
192.168.3.1

上記の通りになるので、各オクテットを0詰めして3桁にしてソートする事を考える。

0詰めする前に、まずはドットで区切る方法・・・

  • 第一オクテットは、最初のドットまでを抽出する
  • 第二オクテットだけを抽出するには、第一オクテットとドットを切り取った後、次のドットまでを抽出する
  • 第三オクテットと第四オクテットも同様。

上記の考え方で、ドットで区切った第一~第四オクテットをカラムとして取得するSQL

SELECT
  SUBSTR(
    ip, 0, INSTR(ip, '.')
  ) AS o1,
  SUBSTR(
    SUBSTR(ip, INSTR(ip, '.')+1),
    0,
    INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')
  ) AS o2,
  SUBSTR(
    SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1),
    0,
    INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')
  ) AS o3,
  SUBSTR(
    SUBSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1),
    0
  ) AS o4
FROM
  network;


o1                o2          o3          o4
----------------  ----------  ----------  ----------
192               168         1           1
192               168         10          1
192               168         2           1
192               168         20          1
192               168         3           1
10                1           10          10
10                20          10          10
10                3           10          10
10                3           10          5


各オクテットを0詰めするには、(LDAPが無いので)'00'という文字列を各オクテットに連結し、SUBSTR で下位3桁とドットを抽出する。

SELECT
  SUBSTR('00'||
    SUBSTR(
      ip, 0, INSTR(ip, '.')+1
    ), -4, 4
  )
  ||
  SUBSTR('00'||
    SUBSTR(
      SUBSTR(ip, INSTR(ip, '.')+1),
      0,
      INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1
    ), -4, 4
  )
  ||
  SUBSTR('00'||
    SUBSTR(
      SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1),
      0,
      INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1
    ), -4, 4
  )
  ||
  SUBSTR('00'||
    SUBSTR(
      SUBSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1),
      0+1
    ), -3, 3
  ) AS ip
FROM
  network
ORDER BY
  ip;


ip
----------------
010.001.010.010
010.003.010.005
010.003.010.010
010.020.010.010
192.168.001.001
192.168.002.001
192.168.003.001
192.168.010.001
192.168.020.001


SQL が長いし、可読性が悪い(´・ω・`)

ユーザ定義関数も使わず、もっと簡単にソートできないものか・・・。

PostgreSQLでIPアドレスをソートする

PostgreSQL にて、IPアドレスをtext型に保存している場合、そのままソートしても意図したようにソートされない。

SELECT ip FROM nic ORDER BY ip;
      ip
--------------
 192.168.1.1
 192.168.10.1
 192.168.2.1
 192.168.20.1
 192.168.3.1


inet 型にCAST するだけ。

SELECT ip::inet FROM nic ORDER BY ip;
      ip
--------------
 192.168.1.1
 192.168.2.1
 192.168.3.1
 192.168.10.1
 192.168.20.1


参考:ネットワークアドレス型
http://www.postgresql.jp/document/8.4/html/datatype-net-types.html


という簡単な事なんだけど、以前に0詰めする方法書いてた。
shobon.hatenablog.com

何故0詰めしたのか・・・CSVに出力した後Excelでソートする時を考えての事だったか・・・

・・・覚えてない(´・ω・`)

UserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する

PostgreSQLにて、DBに保存されたUserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する。

  • ロボットの事は考慮していない。
  • ChromeのUserAgentには、「Safari」という文字列が含まれるので、Safariより先に判定する。
  • MSIE」は「IE 10」まで、「IE 11」は「MSIE」が削除されて「Trident」と「rv:11.0」と表示される。
  • 互換表示の場合、IE10:MSIE 7.0 と Trident/6.0 のように出てくるので、Tridentだけ判定して、「rv」があれば「rv」までを抽出。
  • Tridentとrevの間に、「Touch」「MALNJS」「MAFSJS」などが入っているものがあるが、そのまま抽出。
  • Mac OS Xのバージョンの表示は、FireFoxの場合「10.11」、その他の場合「10_11」と表記が異なる。
  • 期限切れの「Windows NT 5.1」=「Windows XP」が含まれているが、テストなので気にしない。

例えば、logテーブルのuser_agentというカラムにUserAgent情報が保存されている場合に、OSとブラウザの種類・バージョンをグループ化して件数を調べる。

db_name=> SELECT
  COUNT(*),
  CASE
    WHEN user_agent LIKE '%Macintosh%'  THEN SUBSTRING(user_agent from E'\(Mac OS[^)|;]*\)')
    WHEN user_agent LIKE '%iPad%'       THEN SUBSTRING(user_agent from E'\(iPad[^)]*\)')
    WHEN user_agent LIKE '%iPhone%'     THEN SUBSTRING(user_agent from E'\(iPhone[^)]*\)')
    WHEN user_agent LIKE '%Linux%'      THEN SUBSTRING(user_agent from E'\(X11[^)]*\)')
    WHEN user_agent LIKE '%Windows NT%' THEN SUBSTRING(user_agent from E'\(Windows NT [0-9.]+\)[)|;].*')
    ELSE user_agent
   END AS os,
  CASE
    WHEN user_agent LIKE '%Edge%'     THEN SUBSTRING(user_agent from E'\(Edge/[0-9.]+\)')
    WHEN user_agent LIKE '%Sleipnir%' THEN SUBSTRING(user_agent from E'\(Sleipnir/[0-9.]+\)')
    WHEN user_agent LIKE '%OPR%'      THEN SUBSTRING(user_agent from E'\(OPR/[0-9.]+\)')
    WHEN user_agent LIKE '%Vivaldi%'  THEN SUBSTRING(user_agent from E'\(Vivaldi/[0-9.]+\)')
    WHEN user_agent LIKE '%Firefox%'  THEN SUBSTRING(user_agent from E'\(Firefox/[0-9.]+\)')
    WHEN user_agent LIKE '%Chrome%'   THEN SUBSTRING(user_agent from E'\(Chrome/[0-9.]+\)')
    WHEN user_agent LIKE '%Trident%'  THEN SUBSTRING(user_agent from E'\(Trident/[0-9.]+;[^)]*\)')
    WHEN user_agent LIKE '%MSIE%'     THEN SUBSTRING(user_agent from E'\(MSIE [0-9.]+\)')
    WHEN user_agent LIKE '%Safari%'   THEN SUBSTRING(user_agent from E'\(Version/[0-9.]+ .*Safari/[0-9.]+\)')
    ELSE user_agent
   END AS browser
FROM
  log
GROUP BY
  os, browser
ORDER BY
  os, browser;


次のような結果になる。

 count |                 os                  |               browser
-------+-------------------------------------+-------------------------------------
     2 | Mac OS X 10.11                      | Firefox/46.0
     1 | Mac OS X 10_11_5                    | Chrome/50.0.2661.102
    10 | Mac OS X 10_11_5                    | Version/9.1.1 Safari/601.6.17
     9 | Windows NT 10.0                     | Chrome/50.0.2661.102
    19 | Windows NT 10.0                     | Edge/13.10586
     7 | Windows NT 10.0                     | Firefox/46.0
     1 | Windows NT 10.0                     | Sleipnir/6.1.11
     9 | Windows NT 10.0                     | Trident/7.0; rv:11.0
     1 | Windows NT 5.1                      | Chrome/49.0.2623.112
     1 | Windows NT 5.1                      | Firefox/46.0
     2 | Windows NT 6.0                      | Chrome/49.0.2623.112
     4 | Windows NT 6.1                      | Chrome/52.0.2743.10
    17 | Windows NT 6.1                      | Firefox/46.0
     1 | Windows NT 6.1                      | OPR/37.0.2178.43
     1 | Windows NT 6.1                      | Trident/7.0; MAFSJS; rv:11.0
    22 | Windows NT 6.1                      | Trident/7.0; rv:11.0
     1 | Windows NT 6.1                      | Vivaldi/1.1.453.59
     2 | Windows NT 6.3                      | Chrome/51.0.2704.63
     5 | Windows NT 6.3                      | Firefox/46.0
     1 | Windows NT 6.3                      | OPR/37.0.2178.43
     1 | Windows NT 6.3                      | Trident/7.0; MALNJS; rv:11.0
     3 | Windows NT 6.3                      | Trident/7.0; Touch; rv:11.0
     4 | Windows NT 6.3                      | Trident/7.0; rv:11.0
     1 | X11; Linux x86_64; rv:31.0          | Firefox/31.0
     1 | X11; Ubuntu; Linux x86_64; rv:46.0  | Firefox/46.0