Audit
PostgreSQL Migrator helps auditing a migration project by pointing migration issues and scoring complexity of the migration.
Complexity score is an abstract number used to compare projects or objects. Several issues increase complexity of an object:
- properties like name, type, datatype, etc.
- transpilation errors
- inconsistencies with other objects
- manual migration required
- missing feature in PostgreSQL Migrator
- etc.
PostgreSQL Migrator identifies issue using a contextualized string called a issue id. Here is the reference of all migration issues. Unless specified, the complexity score of an issue is 1.
authentication: *¶
Oracle authentication method.
May requires pg_hba.conf configuration in PostgreSQL.
Default score is 0.
e.g. authentication: EXTERNAL.
column datatype: *¶
Column datatype.
e.g. column datatype: enum
Default score is 0. Here is the score for some known datatypes:
| Type | Complexity ID | Score |
|---|---|---|
| MySQL enum | column datatype: enum |
0.5 |
| MySQL set | column datatype: set |
2 |
common role¶
Oracle common role. Default score is 0.
composite partition key¶
Table is partitionned using multiple columns as key.
csv table¶
MySQL table engine CSV. Requires a foreign data wrapper in PostgreSQL. Default score is 10.
DBLink¶
Oracle synonym point to an Oracle Database link.
duplicate identifier¶
Object identifier will conflict with another in PostgreSQL. For example, PostgreSQL indexes are schema-wide, not table-wide. Also, PostgreSQL shares namespace for sequences, table, views, indexes and more. Use Convert.RenameConstraints, Convert.RenameIndexes, or Convert.Rules.Name to workaround this.
expired¶
Expired job. Default score is 0.
federated table¶
MySQL federated table. Default score is 10.
identifier is a reserved keyword¶
Object name is a reserved keyword. Object must be renamend or queries must quote identifier.
identifier is too long¶
Object name is longer than PostgreSQL limit. PostgreSQL will truncate identifier longer than 63 characters.
identity with scale¶
Oracle identity column has a scale. PostgreSQL accepts only integer for identity column.
incremental refresh¶
Oracle materialized view has fast refresh Requires pg_ivm extension in PostgreSQL. Default score is 50.
index organized table¶
Oracle IOT. Use covering index in PostgreSQL. Default score is 5.
invisible index¶
Index masked to planner.
irregular index type¶
Index uses an exotic method. May need an extension. Default score is 5.
job¶
Embedded job in RDBMS scheduler. Default score is 0.
job chain¶
Oracle chain of job. Requires pg_timetable extension in PostgreSQL. Default score is 50.
large datatype: *¶
Large object. Oracle only.
Includes CLOB, LOB, LONG and LONG RAW.
e.g. large datatype: CLOB.
line of code¶
A single line of code from view or procedural code. Used to estimate complexity based on count of lines of code.
Default score is 0.1.
members¶
Oracle type member. Default score is 1 for each member of type.
missing precision¶
Oracle NUMBER column misses precision. Override column datatype with a specific number datatype like smallint, integer, bigint.
missing primary key¶
Table without primary key. Default score is 0.1.
missing source¶
MySQL routine does not have source code available. Maybe a privilege issue while inspecting.
negative scale¶
Oracle NUMBER column is rounded by Oracle.
not implemented conversion: *¶
PostgreSQL Migrator does not yet implements migration of this type of object. File a feature request.
e.g. not implemented conversion: foreign servers
on update current timestamp¶
Table column with builtin update to transaction timestamp. Requires a trigger.
precision mismatch¶
Table column references a foreign NUMBER column with a different precision.
public database link¶
Oracle database link is shared.
public synonym¶
Oracle synonym is shared. Default score is 2.
read only view¶
Oracle readonly view. You need to adjust privileges. Default score is 5.
routine parameter¶
Occurrence of a parameter of a function or a procedure. Each parameter increases routine score. Default score for each parameter is 0.1.
same identifier as table¶
Constraint is name like its table. Default score is 0.
scheduled refresh¶
Oracle materialized view is refreshed automatically. Requires a pg_cron job in PostgreSQL. Default score is 10.
subtype¶
Oracle type inherits from a super type. Default score is 10.
system trigger¶
Oracle database and schema trigger. Default score is 50. Use PostgreSQL event trigger.
temporary table¶
Oracle temporary table. Requires pgtt extension in PostgreSQL. Default score is 10.
transqlate: rewrite panic¶
Transpiler paniced while converting to PostgreSQL. Likely a bug in transpiler.
transqlate: parser panic¶
Transpiler paniced while parsing expression. Likely a bug in transpiler.
transqlate: parse error¶
Transpiler encounter an unknown expression. Missing feature in transpiler.
transqlate: *¶
Transpilation conversion error. Requires manual conversion or a feature request.
type: *¶
Object type.
This is not an annotation, simply a scored migration issue.
e.g. type: Column, type: TemporaryTable, etc.
Default complexity of a type is 1, but PostgreSQL Migrator adjust complexity score of the following object types:
| Type | Issue ID | Score |
|---|---|---|
| Global catalog | type: Catalog |
0 |
| Table column | type: Column |
0.1 |
| Check constraint | type: Check |
0.1 |
| Oracle Database link | type: DatabaseLink |
2 |
| MySQL Foreign Server | type: ForeignServer |
2 |
| Table and view index | type: Index |
0.1 |
| Index column | type: IndexColumn |
0.1 |
| JAVA Source | type: JavaSource |
100 |
| Job | type: Job |
50 |
| Table primary and unique key | type: Key |
0.1 |
| Foreign key | type: ForeignKey |
0.1 |
| Schema | type: Schema |
0.1 |
| Sequence | type: Sequence |
0.1 |
| Oracle Synonym | type: Synonym |
2 |
| Table | type: Table |
0.1 |
| View | type: View |
0.1 |
type mismatch¶
Local column and foreign column does not have same datatype.
e.g. a local column numeric references a bigint column.
unhandled partition type¶
Partition type is not supported by PostgreSQL.
unparsed statement¶
Transpiler parser encounter an unknown syntax and skipped to next statement. Some idiomatism are likely ignored. Requires manual review.