SQLite FDW で PostgreSQL から SQLite3 データベースを使ってみよう!

# はじめに
SQLite FDW を使って、PostgreSQL から SQLite データベース にアクセスしてみました。今回、PostgreSQL, SQLite をソースコードからインストールしていますが、既に PostgreSQL Version 9.6, 10, 11 または 12、SQLite3 がインストールされている場合、PostgreSQL, SQLite のインストールを飛ばして、SQLite FDW のインストールから始めて下さい。

# FDW (Foreign Data Wrapper) とは
PostgreSQLには、SQL2003 SQL/MED 規格に沿った他データソースへのアクセス機能である、FDW(Foreign Data Wrapper) 機能が存在します。この機能により、様々なデータソースへのアクセスが可能となっています。現在、Oracle や SQL Server などの商業 RDBMS、SQLite や MySQL などのOSS RDBMS、Cassandra や GridDB といった NoSQL データベースといったデータソースへアクセスするためのFDWがユーザによって作成・公開されています。

<div align="center"><img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/308856/4e5ada49-32b3-0b56-694e-8622318e1bca.jpeg" width=70%></div>


# PostgreSQLをソースコードからインストール

PostgreSQLをソースコードからインストールします。[PostgreSQLドキュメント](https://www.postgresql.jp/document/11/html/installation.html)を参考しています。

CentOS7 で postgresユーザを作成します。

```bash
$ sudo groupadd postgresql
$ sudo useradd -d /home/postgres -g postgres -s /bin/bash postgres
$ sudo passwd postgres
$ su - postgres
```

PostgreSQLコミュニティから、PostgreSQLを入手します。

```bash
$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
$ tar -jxvf postgresql-12.2.tar.bz2
```

PostgreSQLをインストールします。

```bash
$ cd postgresql-12.2
$ ./configure 
$ make
$ make install
$ cd
```

# SQLiteをソースコードからインストール

SQLite をソースコードからインストールします。[SQLite - Installation](https://www.tutorialspoint.com/sqlite/sqlite_installation.htm) の Install SQLite on Linux を参考しています。
SQLiteコミュニティから SQLite を入手します。

```bash
$ wget https://www.sqlite.org/2020/sqlite-autoconf-3310100.tar.gz
$ tar xvfz sqlite-autoconf-3310100.tar.gz
$ mv sqlite-autoconf-3310100 sqlite3.31.1
```

SQLiteをインストールします。

```bash
$ cd sqlite3.31.1
$ ./configure
$ make
$ sudo make install
```

# 環境設定

環境設定 LD_LIBRARY_PATH を設定します。

```bash
$ LD_LIBRARY_PATH=/usr/local/pgsql/lib:/usr/local/lib
$ export LD_LIBRARY_PATH
```

環境設定 PATH を設定します。

```bash
$ PATH=/usr/local/pgsql/bin:$PATH
$ export PATH
```

# SQLite FDW のインストール

SQLite FDW をインストールします。GitHubにある [readme.md](https://github.com/pgspider/sqlite_fdw/blob/master/README.md) を参考しています。
[PostgreSQLコミュニティ](https://wiki.postgresql.org/wiki/Foreign_data_wrappers)によると、SQLite FDW が2つ存在しますが、更新やプッシュダウンが可能なこちらの [SQLite FDW](https://github.com/pgspider/sqlite_fdw) を使用します。以下の方法は、SQLite FDW ソースコードをPostgreSQLソースツリーに含めてインストールする方法です。

```bash
$ cd ~/postgresql-12.2/contrib
$ git clone https://github.com/pgspider/sqlite_fdw.git
$ cd sqlite_fdw
$ make
$ sudo make install
```

もし、pg_configコマンドにパスが通っている場合には、下記のコマンドでインストールできます。

```sql
$ make USE_PGXS=1
$ sudo make install USE_PGXS=1
```

PostgreSQL を起動します。

```bash
$ initdb -D /usr/local/pgsql/data
$ postgres -D /usr/local/pgsql/data &
```

# サンプルデータベースを入手

[SQLite tutorial](https://www.sqlitetutorial.net/)からサンプルデータベースを入手します。

```bash
$ cd /usr/local/pgsql/data/
$ wget https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip
$ unzip chinook.zip
```

Command Line Shell (sqlite3) でサンプルデータベース(chinnook.db) を確認してみましょう。11のテーブルが存在します。

```bash
$ sqlite3 chinook.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tab
albums          employees       invoices        playlists     
artists         genres          media_types     tracks        
customers       invoice_items   playlist_track
```

# SQLite FDW の初期設定

CREATE EXTENSION で SQLite FDW をデータベースに取り込みます。

```sql
$ psql
psql (12.2)
Type "help" for help.
postgres=# CREATE EXTENSION sqlite_fdw;
CREATE EXTENSION
```

CREATE SERVER で新しい外部サーバを定義します。 今回は sqlite_server を外部サーバ名とします。OPTIONSで、SQLiteのデータベースファイル名を指定します。
次に外部テーブル一覧で何もないことを確認します。

```sql
postgres=# CREATE SERVER sqlite_server FOREIGN DATA WRAPPER sqlite_fdw 
postgres=# OPTIONS (database '/usr/local/pgsql/data/chinook.db');
CREATE SERVER
postgres=# \dE
Did not find any relations.
```

# 外部テーブルの作成して動作確認

SQLite で、media_types テーブルのカラム情報を表示します。

```sql
sqlite> PRAGMA table_info(media_types);
0|MediaTypeId|INTEGER|1||1
1|Name|NVARCHAR(120)|0||0
```

media_types テーブルのカラム情報から、PostgreSQLで、media_types テーブルの外部テーブルを作成します。

```sql
postgres=# CREATE FOREIGN TABLE media_types("MediaTypeId" bigint, "Name" text) SERVER sqlite_server;
CREATE FOREIGN TABLE
```

media_types テーブルを SELECT します。5レコード表示されます。

```sql
postgres=# SELECT * FROM media_types;
 MediaTypeId |            Name             
-------------+-----------------------------
           1 | MPEG audio file
           2 | Protected AAC audio file
           3 | Protected MPEG-4 video file
           4 | Purchased AAC audio file
           5 | AAC audio file
(5 rows)
```

media_types テーブルに レコードを 1件、追加します。SELECTで確認すると、レコードが追加されています。

```sql
postgres=# INSERT INTO media_types VALUES(6, 'TEXT file');
INSERT 0 1
postgres=# select * from media_types;
 MediaTypeId |            Name             
-------------+-----------------------------
           1 | MPEG audio file
           2 | Protected AAC audio file
           3 | Protected MPEG-4 video file
           4 | Purchased AAC audio file
           5 | AAC audio file
           6 | TEXT file
(6 rows)
```
SQLiteでも、media_types テーブルを SELECT してみます。追加したレコードを確認できます。

```sql
sqlite> SELECT * FROM media_types;
1|MPEG audio file
2|Protected AAC audio file
3|Protected MPEG-4 video file
4|Purchased AAC audio file
5|AAC audio file
6|TEXT file
```

次の動作確認のために、一度、media_types テーブルを DROP します。

```sql
postgres=# DROP FOREIGN TABLE media_types;
```

# IMPORT FOREIGN SCHEMA で外部テーブルを一括して作成

1つ、2つのテーブルであれば、外部テーブルを作成しても気になりませんが、テーブルが11もあると、外部テーブル作成がイヤになりますよね。そこで、IMPORT FOREIGN SCHEMA で外部テーブルを一括して作成してみましょう。IMPORT FOREIGN SCHEMA は外部サーバ上に存在するテーブルの外部テーブルを作成します。

```sql
postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_server INTO public;
IMPORT FOREIGN SCHEMA
```

外部テーブル一覧で確認すると、11の外部テーブルが作成されています。

```sql
postgres=# \dE
                 List of relations
 Schema |      Name      |     Type      |  Owner   
--------+----------------+---------------+----------
 public | albums         | foreign table | postgres
 public | artists        | foreign table | postgres
 public | customers      | foreign table | postgres
 public | employees      | foreign table | postgres
 public | genres         | foreign table | postgres
 public | invoice_items  | foreign table | postgres
 public | invoices       | foreign table | postgres
 public | media_types    | foreign table | postgres
 public | playlist_track | foreign table | postgres
 public | playlists      | foreign table | postgres
 public | tracks         | foreign table | postgres
(11 rows)
```

# 外部テーブルでジョインを使った検索をやってみよう!

外部テーブルを使ったジョインを使った検索も可能です。
tracks テーブル、albums テーブル、artists テーブルでインナージョインで検索します。次のSQLは、artistid が 10 のレコードの Track 名、album 名、artist名を表示する SQL です。

```sql
postgres=# SELECT "TrackId", Tracks."Name" AS Track, Albums."Title" AS Album, Artists."Name" AS Artist
postgres-#   FROM Tracks 
postgres-#  INNER JOIN Albums ON Albums."AlbumId" = Tracks."AlbumId"
postgres-#  INNER JOIN Artists ON Artists."ArtistId" = Albums."ArtistId"
postgres-#  WHERE Artists."ArtistId" = 10;
 TrackId |                    track                     |          album           |    artist    
---------+----------------------------------------------+--------------------------+--------------
     123 | Quadrant                                     | The Best Of Billy Cobham | Billy Cobham
     124 | Snoopy's search-Red baron                    | The Best Of Billy Cobham | Billy Cobham
     125 | Spanish moss-"A sound portrait"-Spanish moss | The Best Of Billy Cobham | Billy Cobham
     126 | Moon germs                                   | The Best Of Billy Cobham | Billy Cobham
     127 | Stratus                                      | The Best Of Billy Cobham | Billy Cobham
     128 | The pleasant pheasant                        | The Best Of Billy Cobham | Billy Cobham
     129 | Solo-Panhandler                              | The Best Of Billy Cobham | Billy Cobham
     130 | Do what cha wanna                            | The Best Of Billy Cobham | Billy Cobham
(8 rows)
```

外部テーブルを使っても問題なくインナージョインを使った検索が実行できました。

# まとめ

SQLite FDW を使用して、PosgreSQL から SQLite データベース にアクセスしてみました。これまで、SQLite からデータを取り出して、PostgreSQLにロードして、使う必要がありましたが、SQLite FDW を使うと簡単にPostgreSQLからアクセスできました。手順も簡単なので気軽に使ってみて下さい。

もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。