Skip to main content

Aspires to port Informix SQL to PostgreSQL

Project description

## sqlport - aspires to port Informix SQL to PostgreSQL

```
$ echo 'select first 1 hello from world' | sqlport
SELECT hello FROM world LIMIT 1;
```

```
$ sqlport -h
usage: sqlport [-h] [--outfile OUTFILE | --outdir OUTDIR | --replace]
[--file-list [FILE]] [--quiet] [--verbose] [--debug]
[--parse-tree] [--lex] [--informix]
[INFILE [INFILE ...]]

Ports SQL code to another dialect.

positional arguments:
INFILE

optional arguments:
-h, --help show this help message and exit
--outfile OUTFILE, -o OUTFILE
output file path pattern with place holders: "#" =
input file path; "%" = input file path with last file
extension removed; "%%" = input file path with last
two file extensions removed; ...
--outdir OUTDIR, -d OUTDIR
output base directory
--replace, -r replace input file
--file-list [FILE], -f [FILE]
read file list from file or stdin
--quiet, -q do not output anything
--verbose, -v verbose output
--debug, -D debugging output
--parse-tree, -T show parse tree
--lex, -L show lexer output
--informix, -i generate informix SQL
```

### Implemented transformations

#### Data types

| Informix | Postgres |
| --------- | ------------------- |
| `lvarchar` | `varchar` |
| `varchar(x,y)` | `varchar(x)` |
| `byte` | `bytea` |
| `interval (1) year to month` | `interval` |

#### Literals

| Informix | Postgres |
| --------- | ------------------- |
| `current` | `current_timestamp` |
| `today` | `current_date` |
| `"some text"` | `'some text'` |

#### Misc

| Informix | Postgres |
| --------- | ------------------- |
| `SELECT FIRST 1 ...` | `SELECT ... LIMIT 1` |
| `SELECT UNIQUE ...` | `SELECT DISTINCT ...` |
| `SELECT ... INTO TEMP x` | `CREATE TEMP TABLE x AS SELECT ...` |
| `SELECT x, y, z FROM TABLE(some_function(a, b)) AS t (x, y, z)` | SELECT x, y, z FROM some_function(a, b) AS t (x, y, z) |
| `nvl(x, y)` | `coalesce(x, y)` |
| `ALTER TABLE ADD CONSTRAINT PRIMARY KEY ...` | `ALTER TABLE ADD PRIMARY KEY ...` |
| `UPDATE STATISTICS [FOR table_name]` | `ANALYZE [table_name]` |

#### Procedures

| Informix | Postgres |
| -------- | -------- |
| `CREATE PROCEDURE` | `CREATE FUNCTION` |
| `DROP PROCEDURE` | `DROP FUNCTION` |
| `RETURNING` | `RETURNS` |
| no return value | `RETURNS void` |
| `DEFINE x integer` | `x integer` in `DECLARE` block |
| `DEFINE x, y integer` | converted to individual declarations |
| `LET x = y` | `x := y` |
| `IF ... ELIF ... END IF` | `IF ... ELSIF ... END IF` |
| `WHILE x=y ... END WHILE` | `WHILE x=y LOOP ... END LOOP` |
| `EXIT WHILE`, `EXIT FOR`, ... | `EXIT` |
| `RAISE EXCEPTION -746, 0, "some text"` | `RAISE EXCEPTION "Error: %", 'some text'` |
| semicolon optional after `END IF`, `END FOR`, ... | semicolon always required |
| `EXECUTE PROCEDURE name(x,y)`, `CALL name(x, y)` | `SELECT name(x, y)`, `PERFORM name(x, y)` |

#### MERGE

- Informix:
```
MERGE INTO x USING y ON y.y1 = x.x1
WHEN MATCHED THEN UPDATE SET x.x2 = y.y2
WHEN NOT MATCHED THEN INSERT (x1, x2) VALUES (y1, y2)
```
- Postgres:
```
INSERT INTO x (x1, x2)
SELECT y1, y2 FROM y
ON CONFLICT (x1) DO UPDATE SET x1 = y1, x2 = y2
```
- `MERGE` without WHEN NOT MATCHES THEN INSERT is translated into UPDATE FROM syntax.

#### Keywords as names

- Informix: `all`, `end`, `default`, ...
- Postgres: not allowed
- append underscore, e.g. `all_`, `end_`, `default_`, ...

#### Constraint names

- Postgres: contraint name must differ from table name
- Prefix constraint name, e.g. with `pk_`

### Limited transformations

#### SYSTEM

- Informix: `SYSTEM "sleep 10"`
- Postgres: `PERFORM system('sleep 10')`
- The `system` function has to be defined separately

#### ADD column BEFORE

- Informix: `ALTER TABLE x ADD a int BEFORE c`
- Postgres: `BEFORE` is not supported
- `BEFORE c` is dropped

#### FOREACH

- Informix:
```
FOREACH SELECT a, b INTO x, y FROM ...
...
END FOREACH
```
- Postgres:
```
FOR record IN SELECT a AS x, b AS y
FROM ... LOOP
...
X := record x;
y := record y;
...
END LOOP;
```
- Using the record type directly could be cleaner.

#### OUTER(table)

- Informix: `SELECT ... FROM a, OUTER(b)`
- Postgres: not supported
- Supports limited translation to ANSI JOINs for simple cases.

#### Exception handlers and error codes

- Informix:
```
ON EXCEPTION IN (-206, -958)
...
END EXCEPTION
```
- Postgres:
```
BEGIN
...
EXCEPTION
WHEN undefined_table OR duplicate_table THEN
...
END
```
- Only a few error codes are mapped
- WITH RESUME is not supported
- ON EXCEPTION without error code is not supported

#### Unscaled decimal

- Informix: `decimal(20)`
- If you omit the scale in Informix it is not fixed.
- If you omit the scale in Postgres it defaults to zero.
- Currently these cases are translated to `DECIMAL(30,10)`.

#### MATCHES

- Informix: `MATCHES "*[a-z]?"`
- Postgres: `SIMILAR TO "%[a-z]_"`
- This is converted for literal string patterns, but not if the pattern is a variable.

#### Slice

- Informix: `text[2,4]`
- Postgres: `substring(text from 2 for 3)`
- This is automatically converted. However this does not work if the slice is on the left side of a `let` statement (variable assignment).

### Not Supported

| Informix | Postgres |
| -------- | -------- |
| `multiset(integer)` | |
| `SET LOCK MODE` | |
| `DEFINE GLOBAL` | |
| `database[@server]:name` | |
| `sys*` tables | |
| `LET x, y = y, x` | |
| `GRANT`, `REVOKE` | |
| multiple return values | use `record` type or `OUT` paramters |
| named return parameters | |

If something is not automatically translated a `NOT_SUPPORTED` message is included in the output.


Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for sqlport, version 0.2
Filename, size File type Python version Upload date Hashes
Filename, size sqlport-0.2-py3-none-any.whl (37.8 kB) File type Wheel Python version py3 Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page