diff options
Diffstat (limited to 'contrib/oracle/README.ora2pg')
| -rw-r--r-- | contrib/oracle/README.ora2pg | 284 |
1 files changed, 188 insertions, 96 deletions
diff --git a/contrib/oracle/README.ora2pg b/contrib/oracle/README.ora2pg index 28be8e09176..159ac038b19 100644 --- a/contrib/oracle/README.ora2pg +++ b/contrib/oracle/README.ora2pg @@ -35,7 +35,9 @@ SYNOPSIS datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password - tables => \@tables, # Tables to extract + tables => \@tables, + or # Tables to extract + tables => [('tab1','tab2')], debug => 1 # To show somethings when running ); @@ -56,12 +58,17 @@ SYNOPSIS datasource => $dbsrc, # Database DBD datasource user => $dbuser, # Database user password => $dbpwd, # Database password - min => 10 # Begin extraction at indice 10 + min => 10, # Begin extraction at indice 10 max => 20 # End extraction at indice 20 ); - To know at which indices table can be found during extraction use the - option: + To choose a particular schema just set the following option to + your schema name : + + schema => 'APPS' + + To know at which indices table can be found during extraction + use the option: showtableid => 1 @@ -69,43 +76,77 @@ SYNOPSIS type => 'VIEW' + To extract all grants set the option type as follow: + + type => 'GRANT' + + To extract all sequences set the option type as follow: + + type => 'SEQUENCE' + + To extract all triggers set the option type as follow: + + type => 'TRIGGER' + + To extract all functions set the option type as follow: + + type => 'FUNCTION' + + To extract all procedures set the option type as follow: + + type => 'PROCEDURE' + Default is table schema extraction + type => 'TABLE' + DESCRIPTION - Ora2Pg is a perl OO module used to export an Oracle database schema to a - PostgreSQL compatible schema. + Ora2Pg is a perl OO module used to export an Oracle database + schema to a PostgreSQL compatible schema. - It simply connect to your Oracle database, extract its structure and - generate a SQL script that you can load into your PostgreSQL database. + It simply connect to your Oracle database, extract its structure + and generate a SQL script that you can load into your PostgreSQL + database. + + I'm not a Oracle DBA so I don't really know something about its + internal structure so you may find some incorrect things. Please + tell me what is wrong and what can be better. - I'm not a Oracle DBA so I don't really know something about its internal - structure so you may find some incorrect things. Please tell me what is - wrong and what can be better. + It currently dump the database schema (tables, views, sequences, + indexes, grants), with primary, unique and foreign keys into + PostgreSQL syntax without editing the SQL code generated. - It currently only dump the database schema, with primary, unique and - foreign keys. I've tried to excluded internal system tables but perhaps - not enougt, please let me know. + Functions, procedures and triggers PL/SQL code generated must be + reviewed to match the PostgreSQL syntax. Some usefull + recommandation on porting Oracle to PostgreSQL can be found at + http://techdocs.postgresql.org/ under the "Converting from other + Databases to PostgreSQL" Oracle part. I just notice one thing + more is that the trunc() function in Oracle is the same for + number or date so be carefull when porting to PostgreSQL to use + trunc() for number and date_trunc() for date. ABSTRACT - The goal of the Ora2Pg perl module is to cover all part needed to export - an Oracle database to a PostgreSQL database without other thing that - provide the connection parameters to the Oracle database. + The goal of the Ora2Pg perl module is to cover all part needed + to export an Oracle database to a PostgreSQL database without + other thing that provide the connection parameters to the Oracle + database. Features must include: - - Database schema export, with unique, primary and foreign key. + - Database schema export (tables, views, sequences, indexes), + with unique, primary and foreign key. - Grants/privileges export by user and group. - - Indexes and unique indexes export. - - Table or view selection (by name and max table) export. - - Predefined function/trigger export (todo) - - Data export (todo) + - Table selection (by name and max table) export. + - Predefined functions/triggers/procedures export. - Sql query converter (todo) + - Data export (todo) - My knowledge regarding database is really poor especially for Oracle so - contribution is welcome. + My knowledge regarding database is really poor especially for + Oracle so contribution is welcome. REQUIREMENT - You just need the DBI and DBD::Oracle perl module to be installed + You just need the DBI and DBD::Oracle perl module to be + installed PUBLIC METHODS new HASH_OPTIONS @@ -117,7 +158,8 @@ PUBLIC METHODS - datasource : DBD datasource (required) - user : DBD user (optional with public access) - password : DBD password (optional with public access) - - type : Type of data to extract, can be TABLE (default) or VIEW + - schema : Oracle internal schema to extract + - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE,TRIGGER,FUNCTION,PROCEDURE - debug : Print the current state of the parsing - tables : Extract only the given tables (arrayref) - showtableid : Display only the table indice during extraction @@ -129,153 +171,202 @@ PUBLIC METHODS export_sql FILENAME - Print SQL conversion output to a filename or to STDOUT if no file is - given. + Print SQL conversion output to a filename or to STDOUT if no + file is given. -PUBLIC METHODS +PRIVATE METHODS _init HASH_OPTIONS - Initialize a Ora2Pg object instance with a connexion to the Oracle - database. + Initialize a Ora2Pg object instance with a connexion to the + Oracle database. + + _grants + + This function is used to retrieve all privilege information. + + It extract all Oracle's ROLES to convert them as Postgres groups + and search all users associated to these roles. + + Set the main hash $self->{groups}. Set the main hash $self- + >{grantss}. + + _sequences + + This function is used to retrieve all sequences information. + + Set the main hash $self->{sequences}. + + _triggers + + This function is used to retrieve all triggers information. + + Set the main hash $self->{triggers}. + + _functions + + This function is used to retrieve all functions information. + + Set the main hash $self->{functions}. _tables This function is used to retrieve all table information. - Set the main hash of the database structure $self->{tables}. Keys are - the names of all tables retrieved from the current database. Each table - information compose an array associated to the table_info key as array - reference. In other way: + Set the main hash of the database structure $self->{tables}. + Keys are the names of all tables retrieved from the current + database. Each table information compose an array associated to + the table_info key as array reference. In other way: $self->{tables}{$class_name}{table_info} = [(OWNER,TYPE)]; - DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL - TEMPORARY, ALIAS, SYNONYM or a data source specific type identifier. - This only extract TABLE type. + DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, + LOCAL TEMPORARY, ALIAS, SYNONYM or a data source specific type + identifier. This only extract TABLE type. - It also get the following informations in the DBI object to affect the - main hash of the database structure : + It also get the following informations in the DBI object to + affect the main hash of the database structure : $self->{tables}{$class_name}{field_name} = $sth->{NAME}; $self->{tables}{$class_name}{field_type} = $sth->{TYPE}; - It also call these other private subroutine to affect the main hash of - the database structure : + It also call these other private subroutine to affect the main + hash of the database structure : - @{$self->{tables}{$class_name}{column_info}} = &_column_info($self, $class_name); - @{$self->{tables}{$class_name}{primary_key}} = &_primary_key($self, $class_name); - @{$self->{tables}{$class_name}{unique_key}} = &_unique_key($self, $class_name); - @{$self->{tables}{$class_name}{foreign_key}} = &_foreign_key($self, $class_name); + @{$self->{tables}{$class_name}{column_info}} = $self->_column_info($class_name); + @{$self->{tables}{$class_name}{primary_key}} = $self->_primary_key($class_name); + @{$self->{tables}{$class_name}{unique_key}} = $self->_unique_key($class_name); + @{$self->{tables}{$class_name}{foreign_key}} = $self->_foreign_key($class_name); _views This function is used to retrieve all views information. - Set the main hash of the views definition $self->{views}. Keys are the - names of all views retrieved from the current database values are the - text definition of the views. + Set the main hash of the views definition $self->{views}. Keys + are the names of all views retrieved from the current database + values are the text definition of the views. It then set the main hash as follow: # Definition of the view $self->{views}{$table}{text} = $view_infos{$table}; - # Grants defined on the views - $self->{views}{$table}{grants} = when I find how... _get_sql_data - Returns a string containing the entire SQL Schema definition compatible - with PostgreSQL + Returns a string containing the entire SQL Schema definition + compatible with PostgreSQL _sql_type INTERNAL_TYPE LENGTH - This function return the PostgreSQL datatype corresponding to the Oracle - internal type. + This function return the PostgreSQL datatype corresponding to + the Oracle internal type. _column_info TABLE This function implements a Oracle-native column information. - Return a list of array reference containing the following informations - for each column the given a table + Return a list of array reference containing the following + informations for each column the given a table - [( column name, column type, column length, nullable column, default - value )] + [( column name, column type, column length, nullable column, + default value )] _primary_key TABLE - This function implements a Oracle-native primary key column information. + This function implements a Oracle-native primary key column + information. - Return a list of all column name defined as primary key for the given - table. + Return a list of all column name defined as primary key for the + given table. _unique_key TABLE - This function implements a Oracle-native unique key column information. + This function implements a Oracle-native unique key column + information. - Return a list of all column name defined as unique key for the given - table. + Return a list of all column name defined as unique key for the + given table. _foreign_key TABLE This function implements a Oracle-native foreign key reference information. - Return a list of hash of hash of array reference. Ouuf! Nothing very - difficult. The first hash is composed of all foreign key name. The - second hash just have two key known as 'local' and remote' corresponding - to the local table where the foreign key is defined and the remote table - where the key refer. + Return a list of hash of hash of array reference. Ouuf! Nothing + very difficult. The first hash is composed of all foreign key + name. The second hash just have two key known as 'local' and + remote' corresponding to the local table where the foreign key + is defined and the remote table where the key refer. The foreign key name is composed as follow: 'local_table_name->remote_table_name' - Foreign key data consist in two array representing at the same indice - the local field and the remote field where the first one refer to the - second. Just like this: + Foreign key data consist in two array representing at the same + indice the local field and the remote field where the first one + refer to the second. Just like this: @{$link{$fkey_name}{local}} = @local_columns; @{$link{$fkey_name}{remote}} = @remote_columns; - _get_table_privilege TABLE + _get_users - This function implements a Oracle-native table grants information. + This function implements a Oracle-native users information. - Return a hash of array of all users and their grants on the given table. + Return a hash of all users as an array. _get_roles - This function implements a Oracle-native roles/users information. + This function implements a Oracle-native roles information. + + Return a hash of all groups (roles) as an array of associated + users. + + _get_all_grants - Return a hash of all groups (roles) as an array of associated users. + This function implements a Oracle-native user privilege + information. + + Return a hash of all tables grants as an array of associated + users. _get_indexes TABLE This function implements a Oracle-native indexes information. - Return an array of all indexes name which are not primary keys for the + Return hash of array containing all unique index and a hash of + array of all indexes name which are not primary keys for the given table. - Note: Indexes name must be created like this tablename_fieldname else - they will not be retrieved or if tablename false in the output - fieldname. - - _get_sequences TABLE - - This function implements a Oracle-native sequence information. + _get_sequences - Return a hash of array of sequence name with MIN_VALUE, MAX_VALUE, - INCREMENT and LAST_NUMBER for the given table. + This function implements a Oracle-native sequences information. - Not working yet. + Return a hash of array of sequence name with MIN_VALUE, + MAX_VALUE, INCREMENT and LAST_NUMBER for the given table. _get_views This function implements a Oracle-native views information. - Return a hash of array of sequence name with MIN_VALUE, MAX_VALUE, - INCREMENT and LAST_NUMBER for the given table. + Return a hash of view name with the SQL query it is based on. + + _get_triggers + + This function implements a Oracle-native triggers information. + + Return an array of refarray of all triggers informations + + _get_functions + + This function implements a Oracle-native functions information. + + Return a hash of all function name with their PLSQL code + + _table_info + + This function retrieve all Oracle-native tables information. + + Return a handle to a DB query statement AUTHOR Gilles Darold <gilles@darold.net> @@ -283,14 +374,15 @@ AUTHOR COPYRIGHT Copyright (c) 2001 Gilles Darold - All rights reserved. - This program is free software; you can redistribute it and/or modify it - under the same terms as Perl itself. + This program is free software; you can redistribute it and/or + modify it under the same terms as Perl itself. BUGS This perl module is in the same state as my knowledge regarding - database, it can move and not be compatible with older version so I will - do my best to give you official support for Ora2Pg. Your volontee to - help construct it and your contribution are welcome. + database, it can move and not be compatible with older version + so I will do my best to give you official support for Ora2Pg. + Your volontee to help construct it and your contribution are + welcome. SEE ALSO the DBI manpage, the DBD::Oracle manpage |