diff options
Diffstat (limited to 'contrib/oracle/README.ora2pg')
| -rw-r--r-- | contrib/oracle/README.ora2pg | 485 |
1 files changed, 0 insertions, 485 deletions
diff --git a/contrib/oracle/README.ora2pg b/contrib/oracle/README.ora2pg deleted file mode 100644 index 548f6f95b8..0000000000 --- a/contrib/oracle/README.ora2pg +++ /dev/null @@ -1,485 +0,0 @@ -NAME - Ora2Pg - Oracle to PostgreSQL database schema converter - -SYNOPSIS - BEGIN { - $ENV{ORACLE_HOME} = '/usr/local/oracle/oracle816'; - } - - use strict; - - use Ora2Pg; - - # Init the database connection - my $dbsrc = 'dbi:Oracle:host=testdb.samse.fr;sid=TEST;port=1521'; - my $dbuser = 'system'; - my $dbpwd = 'manager'; - - # Create an instance of the Ora2Pg perl module - my $schema = new Ora2Pg ( - datasource => $dbsrc, # Database DBD datasource - user => $dbuser, # Database user - password => $dbpwd, # Database password - { - PrintError => 0, - RaiseError => 1, - AutoCommit => 0 - } - ); - - # Create the POSTGRESQL representation of all objects in the database - $schema->export_schema("output.sql"); - - exit(0); - - or if you only want to extract some tables: - - # Create an instance of the Ora2Pg perl module - my @tables = ('tab1', 'tab2', 'tab3'); - my $schema = new Ora2Pg ( - datasource => $dbsrc, # Database DBD datasource - user => $dbuser, # Database user - password => $dbpwd, # Database password - tables => \@tables, - or # Tables to extract - tables => [('tab1','tab2')], - debug => 1 # To show somethings when running - ); - - or if you only want to extract the 10 first tables: - - # Create an instance of the Ora2Pg perl module - my $schema = new Ora2Pg ( - datasource => $dbsrc, # Database DBD datasource - user => $dbuser, # Database user - password => $dbpwd, # Database password - max => 10 # 10 first tables to extract - ); - - or if you only want to extract tables 10 to 20: - - # Create an instance of the Ora2Pg perl module - my $schema = new Ora2Pg ( - datasource => $dbsrc, # Database DBD datasource - user => $dbuser, # Database user - password => $dbpwd, # Database password - min => 10, # Begin extraction at indice 10 - max => 20 # End extraction at indice 20 - ); - - To choose a particular Oracle schema to export just set the following - option to your schema name: - - schema => 'APPS' - - This schema definition can also be needed when you want to export data. - If export failed and complain that the table doesn't exists use this to - prefix the table name by the schema name. - - If you want to use PostgreSQL 7.3 schema support activate the init - option 'export_schema' set to 1. Default is no schema export - - To know at which indices tables can be found during extraction use the - option: - - showtableid => 1 - - To extract all views set the type option as follow: - - type => 'VIEW' - - To extract all grants set the type option as follow: - - type => 'GRANT' - - To extract all sequences set the type option as follow: - - type => 'SEQUENCE' - - To extract all triggers set the type option as follow: - - type => 'TRIGGER' - - To extract all functions set the type option as follow: - - type => 'FUNCTION' - - To extract all procedures set the type option as follow: - - type => 'PROCEDURE' - - To extract all packages and body set the type option as follow: - - type => 'PACKAGE' - - Default is table extraction - - type => 'TABLE' - - To extract all data from table extraction as INSERT statement use: - - type => 'DATA' - - To extract all data from table extraction as COPY statement use: - - type => 'COPY' - - and data_limit => n to specify the max tuples to return. If you set this - options to 0 or nothing, no limitation are used. Additional option - 'table', 'min' and 'max' can also be used. - - When use of COPY or DATA you can export data by calling method: - - $schema->export_data("output.sql"); - - Data are dumped to the given filename or to STDOUT with no argument. You - can also send these data directly to a PostgreSQL backend using the - following method: - - $schema->send_to_pgdb($destdatasrc,$destuser,$destpasswd); - - In this case you must call export_data() without argument after the call - to method send_to_pgdb(). - - If you set type to COPY and you want to dump data directly to a PG - database, you must call method send_to_pgdb but data will not be sent - via DBD::Pg but they will be load to the database using the psql - command. Calling this method is istill required to be able to extract - database name, hostname and port information. Edit the $PSQL variable to - match the path of your psql command (nothing to edit if psql is in your - path). - -DESCRIPTION - 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. - - 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 now can dump Oracle data into PostgreSQL DB as online process. You - can choose what columns can be exported for each table. - - 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. - - Features must include: - - - Database schema export (tables, views, sequences, indexes), - with unique, primary and foreign key. - - Grants/privileges export by user and group. - - Table selection (by name and max table) export. - - Export Oracle schema to PostgreSQL 7.3 schema. - - Predefined functions/triggers/procedures/packages export. - - Data export. - - Sql query converter (todo) - - My knowledge regarding database is really poor especially for Oracle so - contribution is welcome. - -REQUIREMENT - You just need the DBI, DBD::Pg and DBD::Oracle perl module to be - installed - -PUBLIC METHODS - new HASH_OPTIONS - - Creates a new Ora2Pg object. - - Supported options are: - - - datasource : DBD datasource (required) - - user : DBD user (optional with public access) - - password : DBD password (optional with public access) - - schema : Oracle internal schema to extract - - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE, - TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE - - debug : Print the current state of the parsing - - export_schema : Export Oracle schema to PostgreSQL 7.3 schema - - tables : Extract only the given tables (arrayref) - - showtableid : Display only the table indice during extraction - - min : Indice to begin extraction. Default to 0 - - max : Indice to end extraction. Default to 0 mean no limits - - data_limit : Number max of tuples to return during data extraction (default 0 no limit) - - Attempt that this list should grow a little more because all - initialization is done by this way. - - export_data FILENAME - - Print SQL data output to a filename or to STDOUT if no file is given. - - Must be used only if type option is set to DATA or COPY =cut - - sub export_data { my ($self, $outfile) = @_; - - $self->_get_sql_data($outfile); - } - - export_sql FILENAME - - Print SQL conversion output to a filename or simply return these data if - no file is given. - - send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD - - Open a DB handle to a PostgreSQL database - - modify_struct TABLE_NAME ARRAYOF_FIELDNAME - - Modify a table structure during export. Only given fieldname will be - exported. - -PRIVATE METHODS - _init HASH_OPTIONS - - 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}. - - _packages - - This function is used to retrieve all packages information. - - Set the main hash $self->{packages}. - - _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: - - $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. - - 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 : - - @{$self->{tables}{$class_name}{column_info}} = $self->_column_info($class_name, $owner); - @{$self->{tables}{$class_name}{primary_key}} = $self->_primary_key($class_name, $owner); - @{$self->{tables}{$class_name}{unique_key}} = $self->_unique_key($class_name, $owner); - @{$self->{tables}{$class_name}{foreign_key}} = $self->_foreign_key($class_name, $owner); - - _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. - - It then set the main hash as follow: - - # Definition of the view - $self->{views}{$table}{text} = $view_infos{$table}; - - _get_sql_data - - Returns a string containing the entire SQL Schema definition compatible - with PostgreSQL - - _get_data TABLE - - This function implements a Oracle-native data extraction. - - Return a list of array reference containing the data - - _sql_type INTERNAL_TYPE LENGTH PRECISION SCALE - - This function return the PostgreSQL datatype corresponding to the Oracle - internal type. - - _column_info TABLE OWNER - - 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 - - [( column name, column type, column length, nullable column, default - value )] - - _primary_key TABLE OWNER - - 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. - - _unique_key TABLE OWNER - - 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. - - _foreign_key TABLE OWNER - - 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. - - 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: - - @{$link{$fkey_name}{local}} = @local_columns; - @{$link{$fkey_name}{remote}} = @remote_columns; - - _get_users - - This function implements a Oracle-native users information. - - Return a hash of all users as an array. - - _get_roles - - This function implements a Oracle-native roles information. - - Return a hash of all groups (roles) as an array of associated users. - - _get_all_grants - - 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 OWNER - - This function implements a Oracle-native indexes information. - - 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. - - _get_sequences - - This function implements a Oracle-native sequences information. - - 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 view name with the SQL query it is based on. - - _alias_info - - This function implements a Oracle-native column information. - - Return a list of array reference containing the following informations - for each alias of the given view - - [( column name, column id )] - - _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 - - _get_packages - - This function implements a Oracle-native packages 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> - -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. - -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. - -SEE ALSO - the DBI manpage, the DBD::Oracle manpage, the DBD::Pg manpage - -ACKNOWLEDGEMENTS - Thanks to Jason Servetar who decided me to implement data extraction. - |