sqlport 0.2
Aspires to port Informix SQL to PostgreSQL
License: BSD

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