search

pgspider / sqlite_fdw

SQLite Foreign Data Wrapper for PostgreSQL
Other
218 stars 37 forks source link
fdw foreign-data-wrapper foreign-tables postgres postgresql postgresql-extension sql sqlite sqlite-fdw sqlite3

readme

SQLite Foreign Data Wrapper for PostgreSQL

This is a foreign data wrapper (FDW) to connect PostgreSQL to SQLite database file. This FDW works with PostgreSQL 12, 13, 14, 15, 16 and confirmed with SQLite 3.42.0.

PostgreSQL + SQLite

Contents

  1. Features
  2. Supported platforms
  3. Installation
  4. Usage
  5. Functions
  6. Identifier case handling
  7. Generated columns
  8. Character set handling
  9. Examples
  10. Limitations
  11. Tests
  12. Contributing
  13. Useful links

Features

Common features

Pushdowning

Notes about pushdowning

Notes about features

Also see Limitations

Supported platforms

sqlite_fdw was developed on Linux and should run on any reasonably POSIX-compliant system.

Installation

Package installation

For some Linux distributives internal packages with sqlite_fdw are avalilable.

Source installation

Prerequisites:

1. Install SQLite & Postgres Development Libraries

For Debian or Ubuntu: apt-get install libsqlite3-dev apt-get install postgresql-server-dev-XX, where XX matches your postgres version, i.e. apt-get install postgresql-server-dev-15

You can also download SQLite source code and build SQLite with FTS5 for full-text search.

2. Build and install sqlite_fdw

Add a directory of pg_config to PATH and build and install sqlite_fdw.

make USE_PGXS=1
make install USE_PGXS=1

If you want to build sqlite_fdw in a source tree of PostgreSQL, use

make
make install

Usage

CREATE SERVER options

sqlite_fdw accepts the following options via the CREATE SERVER command:

CREATE USER MAPPING options

There is no user or password conceptions in SQLite, hence sqlite_fdw no need any CREATE USER MAPPING command.

About access model and possible data modifications problems see about connection to SQLite database file and access control.

CREATE FOREIGN TABLE options

sqlite_fdw accepts the following table-level options via the CREATE FOREIGN TABLE command:

sqlite_fdw accepts the following column-level options via the CREATE FOREIGN TABLE command:

Datatypes

WARNING! The table below represents roadmap, work still in progress. Until it will be ended please refer real behaviour in non-obvious cases, where there is no ✔ or ∅ mark.

This table represents sqlite_fdw behaviour if in PostgreSQL foreign table column some affinity of SQLite data is detected. Some details about data values support see in limitations.

SQLite NULL affinity always can be transparent converted for a nullable column in PostgreSQL.

PostgreSQL SQLite
INT		 SQLite
REAL		 SQLite
BLOB		 SQLite
TEXT		 SQLite
TEXT but
empty		 SQLite
nearest
affinity
bool V ∅ T V+ ∅ INT
bit(n) V n<=64 ∅ ∅ ∅ ∅ INT
bytea ∅ ∅ ✔ - ? BLOB
date V V T V+ NULL ?
float4 V+ ✔ T - NULL REAL
float8 V+ ✔ T - NULL REAL
int2 V+ ? T - NULL INT
int4 V+ ? T - NULL INT
int8 ✔ ? T - NULL INT
json ? ? T V+ ? TEXT
name ? ? T V NULL TEXT
numeric V V T ∅ NULL REAL
text ? ? T ✔ V TEXT
time V V T V+ NULL ?
timestamp V V T V+ NULL ?
timestamp + tz V V T V+ NULL ?
uuid ∅ ∅ V+
(only
16 bytes)		 V+ ∅ TEXT, BLOB
varchar ? ? T ✔ V TEXT
varbit(n) V n<=64 ∅ V ∅ ∅ INT

IMPORT FOREIGN SCHEMA options

sqlite_fdw supports IMPORT FOREIGN SCHEMA (PostgreSQL 9.5+) and accepts following options via the IMPORT FOREIGN SCHEMA command:

Datatype tranlsation rules for IMPORT FOREIGN SCHEMA

SQLite PostgreSQL
int bigint
char text
clob text
text text
blob bytea
real double precision
floa double precision
doub double precision
datetime timestamp
time time
date date
uuid uuid

TRUNCATE support

sqlite_fdw implements the foreign data wrapper TRUNCATE API, available from PostgreSQL 14.

As SQLite does not provide a TRUNCATE command, it is simulated with a simple unqualified DELETE operation.

Actually, TRUNCATE ... CASCADE can be simulated if we create child table of SQLite with foreign keys and ON DELETE CASCADE, and then executing TRUNCATE (which will be deparsed to DELETE).

Following restrictions apply:

Connection to SQLite database file and access control

In OS sqlite_fdw works as executed code with permissions of user of PostgreSQL server. Usually it is postgres OS user.

Data read access

For succesfully connection to SQLite database file you must have at least existed and correct SQLite file readable for OS user of PostgreSQL server process. This means all directories by path to the file must be also readable (listable) for OS user of PostgreSQL server process. There are no other conditions for PostreSQL database superuser to read all of SQLite data if there are also sqlite_fdw extension in the database and FOREIGN SERVER for SQLite database file.

Data change access

Data modification access in sqlite_fdw drived by both operating system and PostgreSQL.

OS restrictions can disallow any SQLite data modifications. Hence any PostgreSQL FOREIGN SERVER or FOREIGN TABLE options or GRANTs can be absolutely not effective. In this case SQLite data modification operations allowed by PostgreSQL can cause error message from SQLite like attempt to write a readonly database with result code 8.

Full list of OS-leveled conditions of data modification access to SQLite database file

Full list of PostgreSQL-leveled conditions of data modification access to SQLite database file

Generally for sqlite_fdw access management FOREIGN SERVER owner can be like remote access manager for other FDWs.

Remote access manager can block any data modififcations in remote database for remote user of a FDW. In this case SQLite have no user or separate access conceptions, hence FOREIGN SERVER owner combines remote access manager role with internal PostgreSQL roles such as FOREIGN SERVER access management.

Functions

As well as the standard sqlite_fdw_handler() and sqlite_fdw_validator() functions, sqlite_fdw provides the following user-callable utility functions:

Identifier case handling

PostgreSQL folds identifiers to lower case by default, SQLite is case insensitive by default only for uppercase and lowercase ASCII base latin letters. It's important to be aware of potential issues with table and column names.

Following SQL isn't correct for SQLite: Error: duplicate column name: a, but is correct for PostgreSQL

    CREATE TABLE T (
      "A" INTEGER,
      "a" NUMERIC
    );

Following SQLs is correct for both SQLite and PostgreSQL because there is no column with names composed from ASCII base latin letters only.

    CREATE TABLE T_кир (
      "А" INTEGER,
      "а" NUMERIC
    );
    CREATE TABLE T_ελλ (
      "Α" INTEGER,
      "α" NUMERIC
    );
    CREATE TABLE T_dia (
      "Ä" INTEGER,
      "ä" NUMERIC
    );

For SQLite there is no difference between

    SELECT * FROM t;   -- №1
    SELECT * FROM T;   -- №2
    SELECT * FROM "t"; -- №3
    SELECT * FROM "T"; -- №4

For PostgreSQL the query with comment №4 is independend query to table T, not to table t as other queries. Please note this table name composed from ASCII base latin letters only. This is not applicable for other alphabet systems or mixed names. This is because toLower operation in PostgreSQL is Unicode operation but ASCII only operation in SQLite, hence other characters will not be changed.

    SELECT * FROM т;   -- №5
    SELECT * FROM Т;   -- №6
    SELECT * FROM "т"; -- №7
    SELECT * FROM "Т"; -- №8

In this case for PostgreSQL the query with comment №8 is independend query to table Т, not to table т as other queries. But for SQLite the queries with comments №6 and №8 belongs to table Т, and the queries with comments №5 and №7 belongs to table т.

If there is

    CREATE TABLE T (
      A INTEGER,
      b REAL
    );

in SQLite, both a and A , b and B columns will have the same real datasource in SQLite in follow foreign table:

    CREATE FOREIGN TABLE "SQLite test" (
      "A" int4 NULL,
      "B" float8 NULL,
      "a" int8 NULL,
      "b" numeric NULL
    )
    SERVER sqlite_server
    OPTIONS (table 'T');

Generated columns

SQLite provides support for generated columns. Behaviour of sqlite_fdw with this columns isn't yet described.

Note that while sqlite_fdw will INSERT or UPDATE the generated column value in SQLite, there is nothing to stop the value being modified within SQLite, and hence no guarantee that in subsequent SELECT operations the column will still contain the expected generated value. This limitation also applies to postgres_fdw.

For more details on generated columns see:

Character set handling

There is no character set metadata stored in SQLite, only PRAGMA encoding; with UTF-only values (UTF-8, UTF-16, UTF-16le, UTF-16be). SQLite text output function guarantees UTF-8 encoding.

When sqlite_fdw connects to a SQLite, all strings are interpreted acording the PostgreSQL database's server encoding. It's not a problem if your PostgreSQL database encoding belongs to Unicode family. Otherewise interpretation transformation problems can occur. Some unproper for PostgreSQL database encoding characters will be replaced to default 'no such character' character or there will error like character with byte sequence 0x** in encoding "UTF8" has no equivalent in encoding "**".

Character case functions such as upper, lower and other are not pushed down because they does not work with Unicode character in SQLite.

Sqlite_fdw tested with PostgreSQL database encodings EUC_JP, EUC_KR, ISO_8859_5, ISO_8859_6, ISO_8859_7, ISO_8859_8, LATIN1, LATIN2, LATIN3, LATIN4, LATIN5, LATIN6, LATIN7, LATIN8, LATIN9, LATIN9, LATIN10, WIN1250, WIN1251, WIN1252, WIN1253, WIN1254, WIN1255, WIN1256, WIN1257 and it's synomyms. Some other encodings also can be supported, but not tested.

Examples

Install the extension:

Once for a database you need, as PostgreSQL superuser.

    CREATE EXTENSION sqlite_fdw;

Create a foreign server with appropriate configuration:

Once for a foreign datasource you need, as PostgreSQL superuser. Please specify SQLite database path using database option.

    CREATE SERVER sqlite_server
    FOREIGN DATA WRAPPER sqlite_fdw
    OPTIONS (
          database '/path/to/database'
    );

Grant usage on foreign server to normal user in PostgreSQL:

Once for a normal user (non-superuser) in PostgreSQL, as PostgreSQL superuser. It is a good idea to use a superuser only where really necessary, so let's allow a normal user to use the foreign server (this is not required for the example to work, but it's secirity recomedation).

    GRANT USAGE ON FOREIGN SERVER sqlite_server TO pguser;

Where pguser is a sample user for works with foreign server (and foreign tables).

User mapping

There is no user or password conceptions in SQLite, hence sqlite_fdw no need any CREATE USER MAPPING command. About access problems see in CREATE USER MAPPING options.

Create foreign table

All CREATE FOREIGN TABLE SQL commands can be executed as a normal PostgreSQL user if there were correct GRANT USAGE ON FOREIGN SERVER. No need PostgreSQL supersuer for secirity reasons but also works with PostgreSQL supersuer.

Please specify table option if SQLite table name is different from foreign table name.

    CREATE FOREIGN TABLE t1 (
      a integer,
      b text
    )
    SERVER sqlite_server
    OPTIONS (
      table 't1_sqlite'
    );

If you want to update tables, please add OPTIONS (key 'true') to a primary key or unique key like the following:

    CREATE FOREIGN TABLE t1(
      a integer OPTIONS (key 'true'),
      b text
    )
    SERVER sqlite_server
    OPTIONS (
      table 't1_sqlite'
    );

If you need to convert INT SQLite column (epoch Unix Time) to be treated/visualized as TIMESTAMP in PostgreSQL, please add OPTIONS (column_type 'INT') when defining FOREIGN table at PostgreSQL like the following:

    CREATE FOREIGN TABLE t1(
      a integer,
      b text,
      c timestamp without time zone OPTIONS (column_type 'INT')
    )
    SERVER sqlite_server
    OPTIONS (
      table 't1_sqlite'
    );

As above, but with aliased column names:

    CREATE FOREIGN TABLE t1(
      a integer,
      b text OPTIONS (column_name 'test_id'),
      c timestamp without time zone OPTIONS (column_type 'INT', column_name 'unixtime')
    )
    SERVER sqlite_server
    OPTIONS (
      table 't1_sqlite'
    );

Import a SQLite database as schema to PostgreSQL:

    IMPORT FOREIGN SCHEMA someschema
    FROM SERVER sqlite_server
    INTO public;

Note: someschema has no particular meaning and can be set to an arbitrary value.

Access foreign table

For the table from previous examples

    SELECT * FROM t1;

Limitations

SQL commands

Mixed affinity support

SQLite text affinity values which is different for SQLite unique checks can be equal for PostgreSQL because sqlite_fdw unifyes semantics of values, not storage form. For example 1(integer), Y(text) and tRuE(text) SQLite values is different in SQLite but equal in PostgreSQL as true values of boolean column. This is also applicable for a data with text affinity in uuid, timestamp, double precision, float and numeric columns of foreign tables. Please be carefully if you want to use mixed affinity column as PostgreSQL foreign table primary key.

Arrays

Numbers (range and precision)

Boolean values

UUID values

bit and varbit support

Tests

Test directory have structure as following:

+---sql
|   +---12.16
|   |       filename1.sql
|   |       filename2.sql
|   |
|   +---13.12
|   |       filename1.sql
|   |       filename2.sql
|   |
.................
|   \---15.4
|          filename1.sql
|          filename2.sql
|
\---expected
|   +---12.16
|   |       filename1.out
|   |       filename2.out
|   |
|   +---13.12
|   |       filename1.out
|   |       filename2.out
|   |
.................
|   \---15.4
            filename1.out
            filename2.out

The test cases for each version are based on the test of corresponding version of PostgreSQL. You can execute test by test.sh directly. The version of PostgreSQL is detected automatically by $(VERSION) variable in Makefile. The corresponding sql and expected directory will be used to compare the result. For example, for Postgres 15.0, you can execute "test.sh" directly, and the sql/15.0 and expected/15.0 will be used to compare automatically.

Test data directory is /tmp/sqlite_fdw_test. If you have /tmp mounted as tmpfs the tests will be up to 800% faster.

Contributing

Opening issues and pull requests on GitHub are welcome. For pull request, please make sure these items below for testing:

Preferred code style see in PostgreSQL source codes. For example

type
funct_name (type arg ...)
{
    t1 var1 = value1;
    t2 var2 = value2;

    for (;;)
    {
    }
    if ()
    {
    }
}

Useful links

Source

General FDW Documentation

Other FDWs

License

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

See the License file for full details.