Contents
- ファイルへのハードリンクを
PGDATA 下に置いて読み込み - 16
色のカラーテーブル、透過部分を抽出 - ビットマップ、カラーテーブル、透過部分を結合して
HTML 出力
サンプル
↓ こんなアイコン。Windows 7
ファイルへのハードリンクを
PostgreSQLWindows
-- set current directory to postgres data
# \cd %PGDATA%
-- create hardlink to icon file (not symbolic link)
# \! mklink /h testicon "d:/windows_101.ico"
Hardlink created for testicon <<===>> d:/windows_101.ico
-- output response only
# \t on \\ \pset format unaligned \\ \pset footer off
# select pg_read_binary_file('testicon');
\x
# select (regexp_matches(bin, '.{32}', 'g'))[1]
from substr(pg_read_binary_file('testicon') :: text, 3) as bin;
ビットマップ先頭までのオフセットの見当を付けるのには、substr
試行錯誤の結果
# create table icon_pixels as
select 32 + 1 - row_number() over() as nrow, reg[1] as pixel_lines
from pg_read_binary_file('testicon') as bin,
substr(bin :: text, 1 + 2 * 143, 32 * 32) as str,
regexp_matches(str, concat('.{32}'), 'g') as reg;
# \t off \\ \pset format aligned \\ \pset border 2
# select * from icon_pixels order by nrow;
16
上のとおり# create table icon_colors as
select to_hex(row_number() over() - 1) as hex,
regexp_replace(reg[1], '(.{2})(.{2})(.{2})', '\3\2\1') as rgb
-- bgr -> rgb
from pg_read_binary_file('testicon') as bin,
right(substr(bin :: text, 3, 2 * 142), 16 * 8) as str,
regexp_matches(str, '(.{6})00', 'g') as reg;
# select * from icon_colors order by hex;
+-----+--------+
| hex | rgb |
+-----+--------+
| 0 | 000000 |
| 1 | 800000 |
| 2 | 008000 |
| 3 | 808000 |
| 4 | 000080 |
| 5 | 800080 |
| 6 | 008080 |
| 7 | c0c0c0 |
| 8 | 808080 |
| 9 | ff0000 |
| a | 00ff00 |
| b | ffff00 |
| c | 0000ff |
| d | ff00ff |
| e | 00ffff |
| f | ffffff |
+-----+--------+
さらにアイコンの透過部分を抽出します。仕様によると
# create table icon_trans as
select 32 + 1 - row_number() over() as nrow,
concat('x', reg[1]) :: bit(32) :: text as trans_lines
from regexp_matches(
substr(pg_read_binary_file('testicon') :: text,
1 + 2 * 143 + 32 * 32, 32 * 32 / 4),
'.{8}', 'g') as reg;
# select * from icon_trans order by nrow;
ビットマップ、カラーテーブル、透過部分を結合して
元アイコンを構成する# select * from icon_pixels natural join icon_trans; +------+----------------------------------+----------------------------------+ | nrow | pixel_lines | transp_lines | +------+----------------------------------+----------------------------------+ | 1 | 00000000000000000000000000000000 | 11111111111111111111111111111111 | | 2 | 00000000000000000000000000000000 | 11111111111111111111111111111111 | | 3 | 88888888888888888888888888888880 | 00000000000000000000000000000000 | | 4 | 87777777777777777777777777777780 | 00000000000000000000000000000000 | | 5 | 87444444444444444444444444444480 | 00000000000000000000000000000000 | | 6 | 87444444444444444444477077077080 | 00000000000000000000000000000000 | | 7 | 87444444444444444444477077077080 | 00000000000000000000000000000000 | | 8 | 87444444444444444444400000000080 | 00000000000000000000000000000000 | | 9 | 87777777777777777777777777777780 | 00000000000000000000000000000000 | ...
この各行を
# with lines (nrow, hex, tr) as (
select nrow,
regexp_split_to_table(pixel_lines, ''),
regexp_split_to_table(trans_lines, '')
from icon_pixels natural join icon_trans
)
select *, row_number() over(partition by nrow) as ncol
from lines natural join icon_colors;
+-----+------+----+--------+------+
| hex | nrow | tr | rgb | ncol |
+-----+------+----+--------+------+
| 0 | 1 | 1 | 000000 | 1 |
| 0 | 1 | 1 | 000000 | 2 |
| 0 | 1 | 1 | 000000 | 3 |
| 0 | 1 | 1 | 000000 | 4 |
| 0 | 1 | 1 | 000000 | 5 |
| 0 | 1 | 1 | 000000 | 6 |
...
| 0 | 6 | 0 | 000000 | 27 |
| 7 | 6 | 0 | c0c0c0 | 28 |
| 7 | 6 | 0 | c0c0c0 | 29 |
| 0 | 6 | 0 | 000000 | 30 |
| 8 | 6 | 0 | 808080 | 31 |
| 0 | 6 | 0 | 000000 | 32 |
| 8 | 7 | 0 | 808080 | 1 |
| 7 | 7 | 0 | c0c0c0 | 2 |
| 4 | 7 | 0 | 000080 | 3 |
| 4 | 7 | 0 | 000080 | 4 |
...
これで元アイコンの情報が一応そろいました。後はどういう形で可視化してもいいですが、今回は簡単に
↓ 最終形。クエリ前に表示設定を「データだけ」にし、クエリ後にメタコマンド「\g
# \t on \\ \pset format unaligned \\ \pset footer off
# with lines (nrow, hex, tr) as (
select nrow,
regexp_split_to_table(pixel_lines, ''),
regexp_split_to_table(trans_lines, '') :: int
from icon_pixels natural join icon_trans
order by nrow
), pixels (nrow, ncol, tag) as (
select nrow, row_number() over(partition by nrow),
concat('<span style="',
case tr when 0 then concat('color: #', rgb)
else 'visibility: hidden'
end, '">██</span>')
from lines natural join icon_colors
)
select text '<div style="border: dashed 1px gray; '
|| ' display: inline-block; font-size: 4px; '
|| ' margin: 1em; padding: 2em ">'
union all
select concat(tag, case ncol when 32 then '<br>' end) from pixels
union all
select '</div>'
\g 'r:/icon_from_postgres.html'
↓ 結果がこちら（冒頭の再掲）。実際のHTMLもUPしました。ブラウザのフォントによって見え方が多少変わると思います。
今回は