aboutsummaryrefslogtreecommitdiff
path: root/contrib/oracle/README.ora2pg
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/oracle/README.ora2pg')
-rw-r--r--contrib/oracle/README.ora2pg284
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