sqlport 0.2

Aspires to port Informix SQL to PostgreSQL

## 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.