BLOBをデータベースで管理する

はじめに

突然ですが、皆さんはアプリケーションで扱うBLOBデータをどこに保存しているでしょうか？大きく分けると、以下のいずれかになると思います。

ファイルストレージ
データベース
オブジェクトストレージ

いずれも一長一短ありますが『SQLアンチパターン』という書籍では、データベースに格納することも検討せよ、と記載されています。

では、データベースで管理するメリットとデメリットは何でしょうか。

メリット
- RDBMSのトランザクション内で操作が完結する(データだけ消えた、ファイルだけ消えたという不整合な状態にならない)
- データと同時にファイルのバックアップも取得できる
- バックアップ復元時にファイルも戻せる
デメリット
- バックアップのサイズや取得時間が増加する
- バックアップの復元時間が増加する
- リソースを圧迫する

ちなみに『SQLアンチパターン』には、オブジェクトストレージに関する記述はなかったと記憶しています。これについては、単純に出版当初はオブジェクトストレージがメジャーじゃなかったんだと思います。(AWSのS3が2006年ごろ初リリースで、SQLアンチパターンが2013年に初版リリース)
参考 Amazon Simple Storage ドキュメント履歴

(オブジェクトストレージなら自動でバージョン管理できるものが多いですし、バックアップ/リストア時にも整合性が保てる上手いやり方あるんでしょうね)

上記設計に関する議論は様々あると思いますが、いったんそれらは隅に置き、BLOBデータをデータベースで管理する実装方法とそのテスト方法について試した事を記載します。

検証環境

PostgreSQL9.6とJava8を利用する。

dev=# select version();
                                                              version

--------------------------------------------------------------------------------
----------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Debian 9.6.9-2.pgdg90+1), compiled by
gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

> java -version
java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

実装方法

Spring Boot + MyBatis で検証する。

  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
   
    <dependencies> 
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
    ...
    </dependencies>

PostgreSQLのBLOBの扱い方

PostgreSQLとしては BYTEA か OID 型が候補となる。
参考 PostgreSQL リファレンス第33章ラージオブジェクト
参考 PostgreSQL リファレンス 8.4. バイナリ列データ型

特徴は以下の通り。
参考 PostgreSQLでバイナリファイルを扱う

BYTEAは1GB以下のデータしか扱えない
OIDは1GB以上のデータも扱えるが、扱いがやや難しい。例えば、行を削除しただけでは実際のオブジェクトが削除されない。※

※ OIDは、オブジェクトがテーブルとは別領域(pg_largeobject)に確保されるため。オブジェクトはlo_unlinkで削除しなければいけない。
参考 Let’s Postgres　ラージオブジェクト

今回は、OIDを採用する。以下、ラージオブジェクトの主な操作を試す。

ルート直下にsample.txtを用意する。また、OIDを設定したテーブルを作成する。

root@3f183604ec6a:/# pwd
/
root@3f183604ec6a:/# cat sample.txt
hello largeobject world
root@3f183604ec6a:/# psql -U dev
psql (9.6.9)
Type "help" for help.

dev=# \d file;
             Table "public.file"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | character varying(100) | not null
 name   | character varying(100) |
 file   | oid                    |
Indexes:
    "file_pkey" PRIMARY KEY, btree (id)

ファイル格納時には lo_importを利用する。

dev=# INSERT INTO file VALUES ('1', 'sample.txt', lo_import('/sample.txt'));
INSERT 0 1
dev=# SELECT * FROM file;
 id |    name    | file
----+------------+-------
 1  | sample.txt | 16751
(1 row)

ファイル取得時にはlo_exportを利用する。

dev=# SELECT lo_export(16751, '/tmp/hoge');
 lo_export
-----------
         1
(1 row)

root@3f183604ec6a:/# cat /tmp/hoge
hello largeobject world

ファイル削除時にはlo_unlinkを利用する。また、行も消す必要がある。どちらが先でも最終的にはファイルは削除される。

dev=# SELECT lo_unlink(16751);
 lo_unlink
-----------
         1
(1 row)

dev=# DELETE FROM file;
DELETE 1

dev=# select * from file;
 id | name | file
----+------+------
(0 rows)

dev=# \q

PostgreSQL JDBC DriverのBLOBの扱い方

先述した BYTEA と OID の型に対する操作ができる。

それぞれを操作するメソッドが別々に用意されている。

To use the BYTEA data type you should simply use the getBytes(), setBytes(), getBinaryStream(), or setBinaryStream() methods.
To use the Large Object functionality you can use either the LargeObject class provided by the PostgreSQL? JDBC driver, or by using the getBLOB() and setBLOB() methods.
参考 Chapter 7. Storing Binary Data

MyBatis上のBLOBの扱い方

MyBatisはTypeHandlerを利用して、型に応じたPreparedStatementの操作を行う。
参考 MyBatis リファレンス

またMyBatis3.4 からはBLOBのTypeHandlerがサポートされている。
参考 MyBatis 3.4でBLOB/CLOBのTypeHandlerがサポートされた

InputStreamはOIDに、byteはBYTEAに対応している。

Javaの型	TypeHandler	実行される jdbc driver のメソッド
InputStream	BlobInputStreamTypeHandler	setBlob()
byte	BlobTypeHandler	setBinaryStream()

コード例

以下のようなテーブルを想定する。

src/main/resources/schema.sql

DROP TABLE IF EXISTS file;
CREATE TABLE file (
  id varchar(100) primary key,
  name varchar(100),
  file oid
);

ファイルと、ファイルに付随する情報を扱うオブジェクトを用意する。

@Data
@Builder
public class File {
  private String id;
  private String name;
  private InputStream in; //BLOB
}

TypeHandlerがサポートされているため、Repositoryクラスの実装は通常通り。ただし、削除時のSQLは、pg_largeobjectを削除する必要がある。
参考 Java + PostgreSQL で BLOB を扱うその 3

@Mapper
public interface FileRepository {

  @Select("SELECT id, name, file AS in FROM file WHERE id = #{id}")
  File findOne(@Param("id") String id);

  @Insert("INSERT INTO file (id, name, file) VALUES (#{file.id}, #{file.name}, #{file.in})")
  int save(@Param("file") File file);

  @Delete("DELETE FROM file WHERE id = #{id} AND ( SELECT lo_unlink( (SELECT file FROM file WHERE id = #{id})) ) = 1")
  int delete(@Param("id") String id);
}

最後に、データベースの接続情報を記述する。 src/main/resources/application.properties

spring.datasource.url=jdbc:postgresql://192.168.11.116:5432/dev
spring.datasource.username=dev
spring.datasource.password=secret
spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.initialization-mode=always

メモリ利用

byte[]と比べてInputStreamを利用しているため、メモリを効率的に利用できる。

250MBのlarge.zipを同時に4つデータベースに保存するコードを実行し、そのときのメモリ利用状況を確認する。

  @Test
  @Rollback(false)
  public void 沢山インサートする() throws InterruptedException {
    IntStream.range(1, 5)
        .forEach(i -> {
          try {
            Path file = Paths.get(this.getClass().getResource("/com/example/blob/large.zip").toURI());
            target.save(File.builder()
                .id(String.valueOf(i))
                .name("large.zip")
                .in(Files.newInputStream(file))
                .build()
            );
          } catch (URISyntaxException | IOException e) {
            e.printStackTrace();
          }
        });
  }

実行すると、データベースにファイルが保存される。

dev=# select * from file;
 id |   name    | file
----+-----------+-------
 1  | large.zip | 16707
 2  | large.zip | 16708
 3  | large.zip | 16709
 4  | large.zip | 16710
(4 rows)

jconsoleの取得結果は以下の通り。ファイルサイズ(250MBx4)と比べて、少ないメモリで実行できている。したがって、性能面だけでいうとbyte[]よりもInputStreamを利用したほうが良い。

テスト方法

Spring Test + DBUnit で検証する。

  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
   
    <repositories>
        <repository>
            <id>sonatype-oss-snapshots</id>
            <name>Sonatype OSS Snapshots Repo</name>
             <url>https://oss.sonatype.org/content/repositories/snapshots/</url>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.dbunit</groupId>
            <artifactId>dbunit</artifactId>
            <version>2.6.0-SNAPSHOT</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.github.springtestdbunit</groupId>
            <artifactId>spring-test-dbunit</artifactId>
            <version>1.3.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter-test</artifactId>
            <version>1.3.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

ざっくりとした結論

ライブラリを組み合わせることで、BLOBをデータベースで管理するコードのテストが簡単に書ける(ある程度のところまで)
ただし、DBUnit 2.6.0-SNAPSHOT 以上じゃないとPostgeSQLを用いたBLOBのテストができない
Spring Test DBUnit が想定してるDBUnitのバージョンは2.5.2なので、この組み合わせでの動作は保証されていない。

参考 Spring DBUnit Issues #179 PostgreSQLDataTypeFactory and OID Data Type
参考 Spring DBUnit Dependencies

ここからは、互換性とか知らんけどたぶん動くっしょ！の精神で進めます。

コード例

設定

BLOBを利用する場合、PostgreSQL用のDataTypeFactoryを利用する必要がある。 Spring Test DBUnit の Issue (How to configure datatypeFactory for the Custom IDatabaseConnections?)を参考に、設定ファイルを用意する。

@Configuration
public class DbUnitConfig {

  @Bean(name="dbUnitDataTypeFactory")
  public DefaultDataTypeFactory datatypeFactory() {
    return new PostgresqlDataTypeFactory();
  }

  @Bean(name="dbUnitDatabaseConfig")
  public DatabaseConfigBean databaseConfigBean() {
    DatabaseConfigBean bean = new DatabaseConfigBean();
    bean.setDatatypeFactory(datatypeFactory());
    return bean;
  }

  @Bean(name="dbUnitDatabaseConnection")
  public DatabaseDataSourceConnectionFactoryBean databaseDataSourceConnectionFactoryBean(DataSource ds) {
    DatabaseDataSourceConnectionFactoryBean bean = new DatabaseDataSourceConnectionFactoryBean();
    bean.setDatabaseConfig(databaseConfigBean());
    bean.setDataSource(ds);
    return bean;
  }
}

次に DataSource の設定を SpringBoot のデフォルトの HikariCP から DriverManagerDataSource に変更する。この理由は、DBUnitの処理の中で org.postgresql.PGConnection に強引にキャストしている箇所があるから。
参考 PostgreSQLOidDataType ソースコード

また、schema.sqlをテスト全体の実行前に流すために initialization-mode を設定する.(具体的には、TestContextの生成時にschema.sqlが流れる)

src/test/resourcesapplication.properties

spring.datasource.url=jdbc:postgresql://192.168.11.116:5432/dev
spring.datasource.username=dev
spring.datasource.password=secret
spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.type=org.springframework.jdbc.datasource.DriverManagerDataSource
spring.datasource.initialization-mode=always

テストクラス

以下のようにテストクラスを作成する。アノテーションすんごい…！

@DbUnitConfiguration(dataSetLoader = ReplacementDataSetLoader.class)
@Import(DbUnitConfig.class)
@TestExecutionListeners({
  DependencyInjectionTestExecutionListener.class,
  DirtiesContextTestExecutionListener.class,
  TransactionalTestExecutionListener.class,
  DbUnitTestExecutionListener.class
})
@MybatisTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@RunWith(SpringRunner.class)
public class FileRepositoryTest {
  @Autowired
  private FileRepository target;

}

@Importによって、定義したDBUnitConfigが読み込まれる
@TestExecutionListenersによって、Spring の TestContext のライフサイクルにListnerが登録される。これによって、@DatabaseSetupや@ExpectedDatabaseが動作したり、DBUnitでSpringのDataSourceが利用できるようになる
@MyBatisTestによって、MyBatisに関するBean定義が自動で行われる
@MyBatisTestによって、メソッドごとにTxがRollbackする
@AutoConfigureTestDatabaseによって、組み込みDBではなくリアルのPostgreSQLが利用されるようになる(mybatis-spring-boot-test-autoconfigure リファレンス)

ファイル検索のテストメソッド

基本的にDBUnit + Spring Test DBUnitの使い方そのまま。 InputStream用のアサーションライブラリを導入してもいいが、今回は主題ではないので簡単に実装する。

  @Test
  @DatabaseSetup("setup-file3.xml")
  public void ファイルが検索できることを確認する() throws IOException {
    File file = target.findOne("1");
    Assert.assertThat(file.getId(), is("1"));
    Assert.assertThat(file.getName(), is("test.txt"));
    Assert.assertThat(convert(file.getIn()), is("test"));
}

  private static String convert(InputStream in) throws IOException {
    try (BufferedReader br = new BufferedReader(new InputStreamReader(in, "utf-8"))) {
      return br.lines().collect(Collectors.joining(System.lineSeparator()));
    }
  }

/com/example/blob/setup-file3.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <file id="1" name="test.txt" file="[TEXT]test"></file>
</dataset>

ファイル保存のテストメソッド

基本的にDBUnit + Spring Test DBUnitの使い方そのまま。

  @Test
  @DatabaseSetup("setup-file1.xml")
  @ExpectedDatabase("expected-file1.xml")
  public void ファイルが保存できることを確認する() throws IOException, URISyntaxException {
    Path file = Paths.get(this.getClass().getResource("/com/example/blob/test.txt").toURI());

    target.save(File.builder()
        .id("1")
        .name(file.getFileName().toString())
        .in(Files.newInputStream(file))
        .build()
    );
  }

テストファイルを用意する。

test

@DatabaseSetupで事前条件を定義する。

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
</dataset>

@ExpectedDatabaseで事後条件(期待されるDBの状態)を定義する。

expected-file1.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <file id="1" name="test.txt" file="[TEXT]test"></file>
</dataset>

ファイル削除のテストメソッド

基本的にDBUnit + Spring Test DBUnitの使い方そのまま。

  @Test
  @DatabaseSetup("setup-file4.xml")
  @ExpectedDatabase("expected-file2.xml")
  public void 削除する() {
    int actual = target.delete("1");
    Assert.assertThat(actual, is(1));
  }

setup-file4.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <file id="1" name="test.txt" file="[TEXT]test"></file>
    <file id="2" name="test2.txt" file="[TEXT]test"></file>
</dataset>

expected-file2.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <file id="2" name="test2.txt" file="[TEXT]test"></file>
</dataset>

ただし、pg_largeobjectが削除されていることは確認できない。メソッドの戻り値が1だと確認してるので、ある程度は担保できると思うが…。

BLOBファイルの指定方法

[TEXT]xxx以外にも、様々な方法が用意されている。例えば、[FILE]/xxxを利用すればシステムパスからファイルを指定できる。
参考 DBUnit Data Types

コードサンプル

Githubにpushしました。