読者です 読者をやめる 読者になる 読者になる

opamp_sando's blog

クソザコが割りと適当なことを書くためにある備忘録です。あとたまに普通の日記も書きます

PostgreSQLに画像を放り込んで遊んだ日記

半分日記のような内容です。

やったこと

  • Common LispPostgreSQLに接続してディレクトリ内の画像を放り込む簡単な関数を書いた
  • psqlコマンドで放り込んだ画像データのHEXテキストを取り出した
  • xxdコマンドで元に戻した
  • ちゃんと取り出せた(やったー)

みたいな内容です。

Common Lispでデータを放り込むプログラム

ブログに貼るとは思ってなかったのでほとんど抽象化もクソもないほげぴよコードですが...
とりあえずcl-fadとcl-dbiを使わせて頂いています。

picturesデータベースのtestテーブルを予め作成しておいたのでそのへんを作成してるコードはないです。

CREATE TABLE test (filename varchar(256) primary key,body bytea);
(defun to-bytea-str (sarray)
  (let ((bstr (loop for i from 0 to (1- (array-total-size sarray)) by 1 collect (format nil "~2,'0X" (aref sarray i)))))
    (format nil "~{~A~}" bstr)))

(defun read-bindata (filename)
  (with-open-file (in filename :element-type '(unsigned-byte 8))
    (let ((binary (make-array (file-length in) :element-type '(unsigned-byte 8))))
      (read-sequence binary in)
      binary)))

(defun insert-dir (path)
  (if (cl-fad:directory-exists-p path)
   (let ((db (dbi:connect :postgres
                          :host "192.168.11.10"
                          :database-name "pictures"
                          :username "opamp"
                          :password "opamp"))
         (files (remove-if #'(lambda (x)
                               (cl-fad:directory-pathname-p x))
                           (cl-fad:list-directory path :follow-symlinks nil))))
     (dbi:with-transaction db
       (dolist (f files)
         (let ((bin (read-bindata f)))
           (let ((q (concatenate 'string "INSERT INTO test (filename,body) VALUES ('"
                                 (namestring f) "',"
                                 "E'\x" (to-bytea-str bin) "'"
                                 ")")))
               (dbi:do-sql db q)
               (format t "~A Finished." f))))) 
     (dbi:disconnect db))
   nil))

当初はsxqlを使って見てたんですが、bytea型のデータを格納する場合にinsert-intoのset=をどうすればよいかわからなかった(いろいろ試したけどどれもエラーだった)ので諦めてSQLを直書きしています。

あとは、format関数が結構便利でしたというくらいですかね。リストになった文字列の連結や、1Byteを表す2文字の16進数文字列を作るときとか便利でした。
はじめはwrite-to-stringでやろうとしてたんですが、例えば"0"のような1桁の数値はそのまま1桁になってしまうので、SQLに記述する文字列に直した時に4bitずれるんですよね。
具体的には

ffd8 ffe0 0010

のようなデータが

ffd8 ffe0 0104

のようになってしまいます。本来"00"となってINSERTのVALUESにかかれてほしいところが"0"になったためですね。他にも"01"なども"1"となるのでこうなります。
formatで以下のように書くと解決できました。

(format nil "~2,'0X" 0)  

確認までに

* (loop for i from 0 to 15 do (format t "~2,'0X~%" i))
00
01
02
03
04
05
06
07
08
09
0A
0B
0C
0D
0E
0F
NIL

という感じです。

psqlコマンドで取り出した

取り出すときはpsqlを使いました。psqlでpicturesデータベースに入って以下のようにしてみます。

\copy (SELECT encode(body, 'escape') FROM test LIMIT 1) TO '/home/xyzzy/dbout.hex'

これで"/home/xyzzy/dbout.hex"にテーブルの要素1つのbodyが16進数ダンプされたような形式で出力されます。
これはただののASCIIテキストなので画像ビュアーなどでは開けません。

xxdで逆変換する

xxdで逆変換します。

$ cat ~/dbout.hex | xxd -r -p > image.jpg

xxdコマンドつよい。

終わり

如何せんPostgresもCommon Lispも初心者なのでもっと良い方法があるかもしれないが、まあ今日は別に特に目的なく遊んでみただけなので...

それにしてもxxdはvim付属コマンドなのでvimを入れないと使えないのがアレですね。同じような機能をもってる似たようなソフトはないものか。今や黒歴史と化した昔作った某アレもありますが、あれは決まったフォーマット以外は読み込めないのでクソです。

DBはLAN内の勉強用に立ててるPostgreSQL9.4.1を使ったんですが、勉強用だし使うのは私一人でたかが知れてるからとlog_statementをmodにしてたんですが、100ファイルくらいある画像フォルダをinsert-dir関数でうpしたらログファイルの容量が瞬間的に900MBくらいになってまともにログ見れなくなって困りました。でかいログファイルはどうやってみるのが普通なんですかね。思いつく方法といえばheadやtailやgrepで行数削ってみるかsplit当たりで分割して見るくらいしか思いつきませんが。

参考資料

http://qiita.com/tamurashingo@github/items/f4ec10dc87720243c8e5

http://stackoverflow.com/questions/1521509/common-lisp-integer-to-hex-conversion

http://stackoverflow.com/questions/6730729/how-to-download-postgres-bytea-column-as-file

あとは利用したライブラリのドキュメントとHyperSpecですね。

Firefox ブラウザ無料ダウンロード