1#!/usr/bin/perl 2#------------------------------------------------------------------------------ 3# Project : Oracle to Postgresql converter 4# Name : ora2pg 5# Author : Gilles Darold, gilles _AT_ darold _DOT_ net 6# Copyright: Copyright (c) 2000-2021 : Gilles Darold - All rights reserved - 7# Function : Script used to convert Oracle Database to PostgreSQL 8# Usage : ora2pg configuration_file 9#------------------------------------------------------------------------------ 10# 11# This program is free software: you can redistribute it and/or modify 12# it under the terms of the GNU General Public License as published by 13# the Free Software Foundation, either version 3 of the License, or 14# any later version. 15# 16# This program is distributed in the hope that it will be useful, 17# but WITHOUT ANY WARRANTY; without even the implied warranty of 18# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 19# GNU General Public License for more details. 20# 21# You should have received a copy of the GNU General Public License 22# along with this program. If not, see < http://www.gnu.org/licenses/ >. 23# 24#------------------------------------------------------------------------------ 25use strict qw/vars/; 26 27use Ora2Pg; 28use Getopt::Long qw(:config no_ignore_case bundling); 29use File::Spec qw/ tmpdir /; 30use POSIX qw(locale_h sys_wait_h _exit); 31setlocale(LC_NUMERIC, ''); 32setlocale(LC_ALL, 'C'); 33 34my $VERSION = '22.1'; 35 36$| = 1; 37 38my $CONFIG_FILE = "/etc/ora2pg/ora2pg.conf"; 39my $FILE_CONF = ''; 40my $DEBUG = 0; 41my $QUIET = 0; 42my $HELP = 0; 43my $LOGFILE = ''; 44my $EXPORT_TYPE = ''; 45my $OUTFILE = ''; 46my $OUTDIR = ''; 47my $SHOW_VER = 0; 48my $PLSQL = ''; 49my $DSN = ''; 50my $DBUSER = ''; 51my $DBPWD = ''; 52my $SCHEMA = ''; 53my $TABLEONLY = ''; 54my $FORCEOWNER = ''; 55my $ORA_ENCODING = ''; 56my $PG_ENCODING = ''; 57my $INPUT_FILE = ''; 58my $EXCLUDE = ''; 59my $ALLOW = ''; 60my $VIEW_AS_TABLE = ''; 61my $ESTIMATE_COST; 62my $COST_UNIT_VALUE = 5; 63my $DUMP_AS_HTML; 64my $DUMP_AS_CSV; 65my $DUMP_AS_SHEET; 66my $THREAD_COUNT; 67my $ORACLE_COPIES; 68my $PARALLEL_TABLES; 69my $DATA_LIMIT; 70my $CREATE_PROJECT = ''; 71my $PROJECT_BASE = '.'; 72my $PRINT_HEADER = ''; 73my $HUMAN_DAY_LIMIT; 74my $IS_MYSQL = 0; 75my $AUDIT_USER = ''; 76my $PG_DSN = ''; 77my $PG_USER = ''; 78my $PG_PWD = ''; 79my $COUNT_ROWS = 0; 80my $DATA_TYPE = ''; 81my $GRANT_OBJECT = ''; 82my $PG_SCHEMA = ''; 83my $NO_HEADER = 0; 84my $ORACLE_SPEED = 0; 85my $ORA2PG_SPEED = 0; 86my $RELATIVE_PATH = 0; 87 88my @SCHEMA_ARRAY = qw( SEQUENCE TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION PROCEDURE TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY ); 89my @EXTERNAL_ARRAY = qw( KETTLE FDW ); 90my @REPORT_ARRAY = qw( SHOW_VERSION SHOW_REPORT SHOW_SCHEMA SHOW_TABLE SHOW_COLUMN SHOW_ENCODING ); 91my @TEST_ARRAY = qw( TEST TEST_VIEW TEST_DATA); 92my @SOURCES_ARRAY = qw( PACKAGE VIEW TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW ); 93my @DATA_ARRAY = qw( INSERT COPY ); 94my @CAPABILITIES = qw( QUERY LOAD ); 95 96my @MYSQL_SCHEMA_ARRAY = qw( TABLE VIEW GRANT TRIGGER FUNCTION PROCEDURE PARTITION DBLINK ); 97my @MYSQL_SOURCES_ARRAY = qw( VIEW TRIGGER FUNCTION PROCEDURE PARTITION ); 98 99my @GRANT_OBJECTS_ARRAY = ('USER','TABLE','VIEW','MATERIALIZED VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE BODY','TYPE','SYNONYM','DIRECTORY'); 100 101my $TMP_DIR = File::Spec->tmpdir() || '/tmp'; 102 103# Collect command line arguments 104GetOptions ( 105 'a|allow=s' => \$ALLOW, 106 'b|basedir=s' => \$OUTDIR, 107 'c|conf=s' => \$FILE_CONF, 108 'd|debug!' => \$DEBUG, 109 'D|data_type=s' => \$DATA_TYPE, 110 'e|exclude=s' => \$EXCLUDE, 111 'g|grant_object=s' => \$GRANT_OBJECT, 112 'h|help!' => \$HELP, 113 'i|input_file=s' => \$INPUT_FILE, 114 'j|jobs=i' => \$THREAD_COUNT, 115 'J|copies=i' => \$ORACLE_COPIES, 116 'l|log=s' => \$LOGFILE, 117 'L|limit=i' => \$DATA_LIMIT, 118 'm|mysql!' => \$IS_MYSQL, 119 'n|namespace=s' => \$SCHEMA, 120 'N|pg_schema=s' => \$PG_SCHEMA, 121 'o|out=s' => \$OUTFILE, 122 'p|plsql!' => \$PLSQL, 123 'P|parallel=i' =>\$PARALLEL_TABLES, 124 'q|quiet!' => \$QUIET, 125 'r|relative!' => \$RELATIVE_PATH, 126 's|source=s' => \$DSN, 127 't|type=s' => \$EXPORT_TYPE, 128 'T|temp_dir=s' => \$TMP_DIR, 129 'u|user=s' => \$DBUSER, 130 'v|version!' => \$SHOW_VER, 131 'w|password=s' => \$DBPWD, 132 'x|xtable=s' => \$TABLEONLY, # Obsolete 133 'forceowner=s' => \$FORCEOWNER, 134 'nls_lang=s' => \$ORA_ENCODING, 135 'client_encoding=s' => \$PG_ENCODING, 136 'view_as_table=s' => \$VIEW_AS_TABLE, 137 'estimate_cost!' =>\$ESTIMATE_COST, 138 'cost_unit_value=i' =>\$COST_UNIT_VALUE, 139 'dump_as_html!' =>\$DUMP_AS_HTML, 140 'dump_as_csv!' =>\$DUMP_AS_CSV, 141 'dump_as_sheet!' =>\$DUMP_AS_SHEET, 142 'init_project=s' => \$CREATE_PROJECT, 143 'project_base=s' => \$PROJECT_BASE, 144 'print_header!' => \$PRINT_HEADER, 145 'human_days_limit=i' => \$HUMAN_DAY_LIMIT, 146 'audit_user=s' => \$AUDIT_USER, 147 'pg_dsn=s' => \$PG_DSN, 148 'pg_user=s' => \$PG_USER, 149 'pg_pwd=s' => \$PG_PWD, 150 'count_rows!' => \$COUNT_ROWS, 151 'no_header!' => \$NO_HEADER, 152 'oracle_speed!' => \$ORACLE_SPEED, 153 'ora2pg_speed!' => \$ORA2PG_SPEED, 154); 155 156# Check command line parameters 157if ($SHOW_VER) { 158 print "Ora2Pg v$VERSION\n"; 159 exit 0; 160} 161if ($HELP) { 162 &usage(); 163} 164 165if ($IS_MYSQL) { 166 @SCHEMA_ARRAY = @MYSQL_SCHEMA_ARRAY; 167 @SOURCES_ARRAY = @MYSQL_SOURCES_ARRAY; 168 @EXTERNAL_ARRAY = (); 169} 170 171# Create project repository and useful stuff 172if ($CREATE_PROJECT) { 173 if (!-d "$PROJECT_BASE") { 174 print "FATAL: Project base directory does not exists: $PROJECT_BASE\n"; 175 &usage(); 176 } 177 print STDERR "Creating project $CREATE_PROJECT.\n"; 178 &create_project($CREATE_PROJECT, $PROJECT_BASE); 179 exit 0; 180} 181 182if ($GRANT_OBJECT && !grep(/^$GRANT_OBJECT$/, @GRANT_OBJECTS_ARRAY)) { 183 print "FATAL: invalid grant object type in -g option. See GRAND_OBJECT configuration directive.\n"; 184 exit 1; 185} 186 187# Clean temporary files 188unless(opendir(DIR, "$TMP_DIR")) { 189 print "FATAL: can't opendir $TMP_DIR: $!\n"; 190 exit 1; 191} 192my @files = grep { $_ =~ /^tmp_ora2pg.*$/ } readdir(DIR); 193closedir DIR; 194foreach (@files) { 195 if (not unlink("$TMP_DIR/$_\n")){ 196 print "FATAL: can not remove old temporary files $TMP_DIR/$_\n"; 197 exit 1; 198 } 199} 200 201# Check configuration file 202my $GOES_WITH_DEFAULT = 0; 203if ($FILE_CONF && ! -e $FILE_CONF) { 204 print "FATAL: can't find configuration file $FILE_CONF\n"; 205 &usage(); 206} elsif (!$FILE_CONF && ! -e $CONFIG_FILE) { 207 # At least we need configuration to connect to Oracle 208 if (!$DSN || (!$DBUSER && !$ENV{ORA2PG_USER}) || (!$DBPWD && !$ENV{ORA2PG_PASSWD})) { 209 print "FATAL: can't find configuration file $CONFIG_FILE\n"; 210 &usage(); 211 } 212 $CONFIG_FILE = ''; 213 $GOES_WITH_DEFAULT = 1; 214} 215 216push(@CAPABILITIES, @SCHEMA_ARRAY, @REPORT_ARRAY, @DATA_ARRAY, @EXTERNAL_ARRAY, @TEST_ARRAY); 217 218# Validate export type 219$EXPORT_TYPE = uc($EXPORT_TYPE); 220$EXPORT_TYPE =~ s/^DATA/COPY/; 221foreach my $t (split(/[,;\s\t]+/, $EXPORT_TYPE)) { 222 if ($t && !grep(/^$t$/, @CAPABILITIES)) { 223 print "FATAL: Unknown export type: $t. Type supported: ", join(',', @CAPABILITIES), "\n"; 224 &usage(); 225 } 226} 227 228# Preserve barckward compatibility 229if ($TABLEONLY) { 230 warn "-x | --xtable is deprecated, use -a | --allow option instead.\n"; 231 if (!$ALLOW) { 232 $ALLOW = $TABLEONLY; 233 } 234} 235 236sub getout 237{ 238 my $sig = shift; 239 print STDERR "Received terminating signal ($sig).\n"; 240 $SIG{INT} = \&getout; 241 $SIG{TERM} = \&getout; 242 243 # Cleaning temporary files 244 unless(opendir(DIR, "$TMP_DIR")) { 245 print "FATAL: can't opendir $TMP_DIR: $!\n"; 246 exit 1; 247 } 248 my @files = grep { $_ =~ /^tmp_ora2pg.*$/ } readdir(DIR); 249 closedir DIR; 250 foreach (@files) { 251 unlink("$TMP_DIR/$_\n"); 252 } 253 254 exit 1; 255} 256$SIG{INT} = \&getout; 257$SIG{TERM} = \&getout; 258 259# Replace ; or space by comma in the user list 260$AUDIT_USER =~ s/[;\s]+/,/g; 261 262# Create an instance of the Ora2Pg perl module 263my $schema = new Ora2Pg ( 264 config => $FILE_CONF || $CONFIG_FILE, 265 type => $EXPORT_TYPE, 266 debug => $DEBUG, 267 logfile=> $LOGFILE, 268 output => $OUTFILE, 269 output_dir => $OUTDIR, 270 plsql_pgsql => $PLSQL, 271 datasource => $DSN, 272 user => $DBUSER || $ENV{ORA2PG_USER}, 273 password => $DBPWD || $ENV{ORA2PG_PASSWD}, 274 schema => $SCHEMA, 275 pg_schema => $PG_SCHEMA, 276 force_owner => $FORCEOWNER, 277 nls_lang => $ORA_ENCODING, 278 client_encoding => $PG_ENCODING, 279 input_file => $INPUT_FILE, 280 quiet => $QUIET, 281 exclude => $EXCLUDE, 282 allow => $ALLOW, 283 view_as_table => $VIEW_AS_TABLE, 284 estimate_cost => $ESTIMATE_COST, 285 cost_unit_value => $COST_UNIT_VALUE, 286 dump_as_html => $DUMP_AS_HTML, 287 dump_as_csv => $DUMP_AS_CSV, 288 dump_as_sheet => $DUMP_AS_SHEET, 289 thread_count => $THREAD_COUNT, 290 oracle_copies => $ORACLE_COPIES, 291 data_limit => $DATA_LIMIT, 292 parallel_tables => $PARALLEL_TABLES, 293 print_header => $PRINT_HEADER, 294 human_days_limit => $HUMAN_DAY_LIMIT, 295 is_mysql => $IS_MYSQL, 296 audit_user => $AUDIT_USER, 297 temp_dir => $TMP_DIR, 298 pg_dsn => $PG_DSN, 299 pg_user => $PG_USER, 300 pg_pwd => $PG_PWD, 301 count_rows => $COUNT_ROWS, 302 data_type => $DATA_TYPE, 303 grant_object => $GRANT_OBJECT, 304 no_header => $NO_HEADER, 305 oracle_speed => $ORACLE_SPEED, 306 ora2pg_speed => $ORA2PG_SPEED, 307 psql_relative_path => $RELATIVE_PATH, 308); 309 310# Look at configuration file if an input file is defined 311if (!$INPUT_FILE && !$GOES_WITH_DEFAULT) { 312 my $cf_file = $FILE_CONF || $CONFIG_FILE; 313 my $fh = new IO::File; 314 $fh->open($cf_file) or die "FATAL: can't read configuration file $cf_file, $!\n"; 315 while (my $l = <$fh>) { 316 chomp($l); 317 $l =~ s/\r//gs; 318 $l =~ s/^\s*\#.*$//g; 319 next if (!$l || ($l =~ /^\s+$/)); 320 $l =~ s/^\s*//; $l =~ s/\s*$//; 321 my ($var, $val) = split(/\s+/, $l, 2); 322 $var = uc($var); 323 if ($var eq 'INPUT_FILE' && $val) { 324 $INPUT_FILE = $val; 325 } 326 } 327 $fh->close(); 328} 329 330# Proceed to Oracle DB extraction following 331# configuration file definitions. 332if ( ($EXPORT_TYPE !~ /^SHOW_/i) && !$INPUT_FILE ) { 333 $schema->export_schema(); 334} 335 336# Check if error occurs during data export 337unless(opendir(DIR, "$TMP_DIR")) { 338 print "FATAL: can't opendir $TMP_DIR: $!\n"; 339 exit 1; 340} 341@files = grep { $_ =~ /^tmp_ora2pg.*$/ } readdir(DIR); 342closedir DIR; 343if ($#files >= 0) { 344 print STDERR "\nWARNING: an error occurs during data export. Please check what's happen.\n\n"; 345 exit 2; 346} 347 348exit(0); 349 350#### 351# Show usage 352#### 353sub usage 354{ 355 print qq{ 356Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value] 357 358 -a | --allow str : Comma separated list of objects to allow from export. 359 Can be used with SHOW_COLUMN too. 360 -b | --basedir dir: Set the default output directory, where files 361 resulting from exports will be stored. 362 -c | --conf file : Set an alternate configuration file other than the 363 default /etc/ora2pg/ora2pg.conf. 364 -d | --debug : Enable verbose output. 365 -D | --data_type STR : Allow custom type replacement at command line. 366 -e | --exclude str: Comma separated list of objects to exclude from export. 367 Can be used with SHOW_COLUMN too. 368 -h | --help : Print this short help. 369 -g | --grant_object type : Extract privilege from the given object type. 370 See possible values with GRANT_OBJECT configuration. 371 -i | --input file : File containing Oracle PL/SQL code to convert with 372 no Oracle database connection initiated. 373 -j | --jobs num : Number of parallel process to send data to PostgreSQL. 374 -J | --copies num : Number of parallel connections to extract data from Oracle. 375 -l | --log file : Set a log file. Default is stdout. 376 -L | --limit num : Number of tuples extracted from Oracle and stored in 377 memory before writing, default: 10000. 378 -m | --mysql : Export a MySQL database instead of an Oracle schema. 379 -n | --namespace schema : Set the Oracle schema to extract from. 380 -N | --pg_schema schema : Set PostgreSQL's search_path. 381 -o | --out file : Set the path to the output file where SQL will 382 be written. Default: output.sql in running directory. 383 -p | --plsql : Enable PLSQL to PLPGSQL code conversion. 384 -P | --parallel num: Number of parallel tables to extract at the same time. 385 -q | --quiet : Disable progress bar. 386 -r | --relative : use \\ir instead of \\i in the psql scripts generated. 387 -s | --source DSN : Allow to set the Oracle DBI datasource. 388 -t | --type export: Set the export type. It will override the one 389 given in the configuration file (TYPE). 390 -T | --temp_dir DIR: Set a distinct temporary directory when two 391 or more ora2pg are run in parallel. 392 -u | --user name : Set the Oracle database connection user. 393 ORA2PG_USER environment variable can be used instead. 394 -v | --version : Show Ora2Pg Version and exit. 395 -w | --password pwd : Set the password of the Oracle database user. 396 ORA2PG_PASSWD environment variable can be used instead. 397 --forceowner : Force ora2pg to set tables and sequences owner like in 398 Oracle database. If the value is set to a username this one 399 will be used as the objects owner. By default it's the user 400 used to connect to the Pg database that will be the owner. 401 --nls_lang code: Set the Oracle NLS_LANG client encoding. 402 --client_encoding code: Set the PostgreSQL client encoding. 403 --view_as_table str: Comma separated list of views to export as table. 404 --estimate_cost : Activate the migration cost evaluation with SHOW_REPORT 405 --cost_unit_value minutes: Number of minutes for a cost evaluation unit. 406 default: 5 minutes, corresponds to a migration conducted by a 407 PostgreSQL expert. Set it to 10 if this is your first migration. 408 --dump_as_html : Force ora2pg to dump report in HTML, used only with 409 SHOW_REPORT. Default is to dump report as simple text. 410 --dump_as_csv : As above but force ora2pg to dump report in CSV. 411 --dump_as_sheet : Report migration assessment with one CSV line per database. 412 --init_project NAME: Initialise a typical ora2pg project tree. Top directory 413 will be created under project base dir. 414 --project_base DIR : Define the base dir for ora2pg project trees. Default 415 is current directory. 416 --print_header : Used with --dump_as_sheet to print the CSV header 417 especially for the first run of ora2pg. 418 --human_days_limit num : Set the number of human-days limit where the migration 419 assessment level switch from B to C. Default is set to 420 5 human-days. 421 --audit_user LIST : Comma separated list of usernames to filter queries in 422 the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT 423 and QUERY export type. 424 --pg_dsn DSN : Set the datasource to PostgreSQL for direct import. 425 --pg_user name : Set the PostgreSQL user to use. 426 --pg_pwd password : Set the PostgreSQL password to use. 427 --count_rows : Force ora2pg to perform a real row count in TEST action. 428 --no_header : Do not append Ora2Pg header to output file 429 --oracle_speed : Use to know at which speed Oracle is able to send 430 data. No data will be processed or written. 431 --ora2pg_speed : Use to know at which speed Ora2Pg is able to send 432 transformed data. Nothing will be written. 433 434See full documentation at https://ora2pg.darold.net/ for more help or see 435manpage with 'man ora2pg'. 436 437ora2pg will return 0 on success, 1 on error. It will return 2 when a child 438process has been interrupted and you've gotten the warning message: 439 "WARNING: an error occurs during data export. Please check what's happen." 440Most of the time this is an OOM issue, first try reducing DATA_LIMIT value. 441 442}; 443 exit 1; 444 445} 446 447#### 448# Create a generic project tree 449#### 450sub create_project 451{ 452 my ($create_project, $project_base) = @_; 453 454 # Look at default configuration file to use 455 my $conf_file = $CONFIG_FILE . '.dist'; 456 if ($FILE_CONF) { 457 # Use file given in parameter 458 $conf_file = $FILE_CONF; 459 } 460 if (!-f $conf_file || -z $conf_file) { 461 print "FATAL: file $conf_file does not exists.\n"; 462 exit 1; 463 } 464 # Build entire project tree 465 my $base_path = $project_base . '/' . $create_project; 466 if (-e $base_path) { 467 print "FATAL: project directory exists $base_path\n"; 468 exit 1; 469 } 470 mkdir("$base_path"); 471 print "$base_path/\n"; 472 mkdir("$base_path/schema"); 473 print "\tschema/\n"; 474 475 foreach my $exp (sort @SCHEMA_ARRAY ) { 476 my $tpath = lc($exp); 477 $tpath =~ s/y$/ie/; 478 mkdir("$base_path/schema/" . $tpath . 's'); 479 print "\t\t" . $tpath . "s/\n"; 480 } 481 mkdir("$base_path/sources"); 482 print "\tsources/\n"; 483 foreach my $exp (sort @SOURCES_ARRAY ) { 484 my $tpath = lc($exp); 485 $tpath =~ s/y$/ie/; 486 mkdir("$base_path/sources/" . $tpath . 's'); 487 print "\t\t" . $tpath . "s/\n"; 488 } 489 mkdir("$base_path/data"); 490 print "\tdata/\n"; 491 mkdir("$base_path/config"); 492 print "\tconfig/\n"; 493 mkdir("$base_path/reports"); 494 print "\treports/\n"; 495 print "\n"; 496 497 # Copy configuration file and transform it as a generic one 498 print "Generating generic configuration file\n"; 499 if (open(IN, "$conf_file")) { 500 my @cf = <IN>; 501 close(IN); 502 # Create a generic configuration file only if it has the .dist extension 503 # otherwise use the configuration given at command line (-c option) 504 if ($conf_file =~ /\.dist/) { 505 &make_config_generic(\@cf); 506 } 507 unless(open(OUT, ">$base_path/config/ora2pg.conf")) { 508 print "FATAL: can't write to file $base_path/config/ora2pg.conf\n"; 509 exit 1; 510 } 511 print OUT @cf; 512 close(OUT); 513 } else { 514 print "FATAL: can not read file $conf_file, $!.\n"; 515 exit 1; 516 } 517 518 # Generate shell script to execute all export 519 print "Creating script export_schema.sh to automate all exports.\n"; 520 unless(open(OUT, "> $base_path/export_schema.sh")) { 521 print "FATAL: Can't write to file $base_path/export_schema.sh\n"; 522 exit 1; 523 } 524 print OUT qq{#!/bin/sh 525#------------------------------------------------------------------------------- 526# 527# Generated by Ora2Pg, the Oracle database Schema converter, version $VERSION 528# 529#------------------------------------------------------------------------------- 530}; 531 print OUT "EXPORT_TYPE=\"", join(' ', @SCHEMA_ARRAY), "\"\n"; 532 print OUT "SOURCE_TYPE=\"", join(' ', @SOURCES_ARRAY), "\"\n"; 533 print OUT "namespace=\".\"\n"; 534 print OUT "unit_cost=$COST_UNIT_VALUE\n"; 535 print OUT qq{ 536ora2pg -t SHOW_TABLE -c \$namespace/config/ora2pg.conf > \$namespace/reports/tables.txt 537ora2pg -t SHOW_COLUMN -c \$namespace/config/ora2pg.conf > \$namespace/reports/columns.txt 538ora2pg -t SHOW_REPORT -c \$namespace/config/ora2pg.conf --dump_as_html --cost_unit_value \$unit_cost --estimate_cost > \$namespace/reports/report.html 539 540for etype in \$(echo \$EXPORT_TYPE | tr " " "\\n") 541do 542 ltype=`echo \$etype | tr '[:upper:]' '[:lower:]'` 543 ltype=`echo \$ltype | sed 's/y\$/ie/'` 544 echo "Running: ora2pg -p -t \$etype -o \$ltype.sql -b \$namespace/schema/\$\{ltype\}s -c \$namespace/config/ora2pg.conf" 545 ora2pg -p -t \$etype -o \$ltype.sql -b \$namespace/schema/\$\{ltype\}s -c \$namespace/config/ora2pg.conf 546 ret=`grep "Nothing found" \$namespace/schema/\$\{ltype\}s/\$ltype.sql 2> /dev/null` 547 if [ ! -z "\$ret" ]; then 548 rm \$namespace/schema/\$\{ltype\}s/\$ltype.sql 549 fi 550done 551 552for etype in \$(echo \$SOURCE_TYPE | tr " " "\\n") 553do 554 ltype=`echo \$etype | tr '[:upper:]' '[:lower:]'` 555 ltype=`echo \$ltype | sed 's/y\$/ie/'` 556 echo "Running: ora2pg -t \$etype -o \$ltype.sql -b \$namespace/sources/\$\{ltype\}s -c \$namespace/config/ora2pg.conf" 557 ora2pg -t \$etype -o \$ltype.sql -b \$namespace/sources/\$\{ltype\}s -c \$namespace/config/ora2pg.conf 558 ret=`grep "Nothing found" \$namespace/sources/\$\{ltype\}s/\$ltype.sql 2> /dev/null` 559 if [ ! -z "\$ret" ]; then 560 rm \$namespace/sources/\$\{ltype\}s/\$ltype.sql 561 fi 562done 563 564echo 565echo 566echo "To extract data use the following command:" 567echo 568echo "ora2pg -t COPY -o data.sql -b \$namespace/data -c \$namespace/config/ora2pg.conf" 569echo 570 571exit 0 572}; 573 close(OUT); 574 chmod(0700, "$base_path/export_schema.sh"); 575 576 577 # Generate shell script to execute all import 578 print "Creating script import_all.sh to automate all imports.\n"; 579 my $exportype = "EXPORT_TYPE=\"TYPE " . join(' ', grep( !/^TYPE$/, @SCHEMA_ARRAY)) . "\"\n"; 580 unless(open(OUT, "> $base_path/import_all.sh")) { 581 print "FATAL: Can't write to file $base_path/import_all.sh\n"; 582 exit 1; 583 } 584 while (my $l = <DATA>) { 585 $l =~ s/^EXPORT_TYPE=.*/$exportype/s; 586 $l =~ s/ORA2PG_VERSION/$VERSION/s; 587 print OUT $l; 588 } 589 close(OUT); 590 chmod(0700, "$base_path/import_all.sh"); 591} 592 593#### 594# Set a generic configuration 595#### 596sub make_config_generic 597{ 598 my $conf_arr = shift; 599 600 chomp(@$conf_arr); 601 602 my $schema = 'CHANGE_THIS_SCHEMA_NAME'; 603 $schema = $SCHEMA if ($SCHEMA); 604 for (my $i = 0; $i <= $#{$conf_arr}; $i++) { 605 if ($IS_MYSQL) { 606 $conf_arr->[$i] =~ s/^# Set Oracle database/# Set MySQL database/; 607 $conf_arr->[$i] =~ s/^(ORACLE_DSN.*dbi):Oracle:(.*);sid=SIDNAME/$1:mysql:$2;database=dbname/; 608 $conf_arr->[$i] =~ s/CHANGE_THIS_SCHEMA_NAME/CHANGE_THIS_DB_NAME/; 609 $conf_arr->[$i] =~ s/#REPLACE_ZERO_DATE.*/REPLACE_ZERO_DATE\t-INFINITY/; 610 } elsif ($ENV{ORACLE_HOME}) { 611 $conf_arr->[$i] =~ s/^ORACLE_HOME.*/ORACLE_HOME\t$ENV{ORACLE_HOME}/; 612 } 613 $conf_arr->[$i] =~ s/^USER_GRANTS.*0/USER_GRANTS\t1/; 614 $conf_arr->[$i] =~ s/^#SCHEMA.*SCHEMA_NAME/SCHEMA\t$schema/; 615 $conf_arr->[$i] =~ s/^(BINMODE.*)/#$1/; 616 $conf_arr->[$i] =~ s/^PLSQL_PGSQL.*1/PLSQL_PGSQL\t0/; 617 $conf_arr->[$i] =~ s/^FILE_PER_CONSTRAINT.*0/FILE_PER_CONSTRAINT\t1/; 618 $conf_arr->[$i] =~ s/^FILE_PER_INDEX.*0/FILE_PER_INDEX\t1/; 619 $conf_arr->[$i] =~ s/^FILE_PER_FKEYS.*0/FILE_PER_FKEYS\t1/; 620 $conf_arr->[$i] =~ s/^FILE_PER_TABLE.*0/FILE_PER_TABLE\t1/; 621 $conf_arr->[$i] =~ s/^FILE_PER_FUNCTION.*0/FILE_PER_FUNCTION\t1/; 622 $conf_arr->[$i] =~ s/^TRUNCATE_TABLE.*0/TRUNCATE_TABLE\t1/; 623 $conf_arr->[$i] =~ s/^DISABLE_SEQUENCE.*0/DISABLE_SEQUENCE\t1/; 624 $conf_arr->[$i] =~ s/^DISABLE_TRIGGERS.*0/DISABLE_TRIGGERS\t1/; 625 $conf_arr->[$i] =~ s/^(CLIENT_ENCODING.*)/#$1/; 626 $conf_arr->[$i] =~ s/^(NLS_LANG.*)/#$1/; 627 $conf_arr->[$i] =~ s/^#LONGREADLEN.*1047552/LONGREADLEN\t1047552/; 628 $conf_arr->[$i] =~ s/^AUTODETECT_SPATIAL_TYPE.*0/AUTODETECT_SPATIAL_TYPE\t1/; 629 $conf_arr->[$i] =~ s/^NO_LOB_LOCATOR.*/NO_LOB_LOCATOR\t0/; 630 $conf_arr->[$i] =~ s/^USE_LOB_LOCATOR.*/USE_LOB_LOCATOR\t1/; 631 $conf_arr->[$i] =~ s/^FTS_INDEX_ONLY.*0/FTS_INDEX_ONLY\t1/; 632 $conf_arr->[$i] =~ s/^DISABLE_UNLOGGED.*0/DISABLE_UNLOGGED\t1/; 633 $conf_arr->[$i] =~ s/^EMPTY_LOB_NULL.*0/EMPTY_LOB_NULL\t1/; 634 if ($DSN) { 635 $conf_arr->[$i] =~ s/^ORACLE_DSN.*/ORACLE_DSN\t$DSN/; 636 } 637 if ($DBUSER) { 638 $conf_arr->[$i] =~ s/^ORACLE_USER.*/ORACLE_USER\t$DBUSER/; 639 } 640 if ($DBPWD) { 641 $conf_arr->[$i] =~ s/^ORACLE_PWD.*/ORACLE_PWD\t$DBPWD/; 642 } 643 } 644 map { s/$/\n/; } @$conf_arr; 645} 646 647__DATA__ 648#!/bin/sh 649#------------------------------------------------------------------------------- 650# 651# Script used to load exported sql files into PostgreSQL in practical manner 652# allowing you to chain and automatically import schema and data. 653# 654# Generated by Ora2Pg, the Oracle database Schema converter, version ORA2PG_VERSION 655# 656#------------------------------------------------------------------------------- 657 658EXPORT_TYPE="TYPE,TABLE,PARTITION,VIEW,MVIEW,FUNCTION,PROCEDURE,SEQUENCE,TRIGGER,SYNONYM,DIRECTORY,DBLINK" 659AUTORUN=0 660NAMESPACE=. 661NO_CONSTRAINTS=0 662IMPORT_INDEXES_AFTER=0 663DEBUG=0 664IMPORT_SCHEMA=0 665IMPORT_DATA=0 666IMPORT_CONSTRAINTS=0 667NO_DBCHECK=0 668 669 670# Message functions 671die() { 672 echo "ERROR: $1" 1>&2 673 exit 1 674} 675 676usage() { 677 echo "usage: `basename $0` [options]" 678 echo "" 679 echo "Script used to load exported sql files into PostgreSQL in practical manner" 680 echo "allowing you to chain and automatically import schema and data." 681 echo "" 682 echo "options:" 683 echo " -a import data only" 684 echo " -b filename SQL script to execute just after table creation to fix database schema" 685 echo " -d dbname database name for import" 686 echo " -D enable debug mode, will only show what will be done" 687 echo " -e encoding database encoding to use at creation (default: UTF8)" 688 echo " -f force no check of user and database existing and do not try to create them" 689 echo " -h hostname hostname of the PostgreSQL server (default: unix socket)" 690 echo " -i only load indexes, constraints and triggers" 691 echo " -I do not try to load indexes, constraints and triggers" 692 echo " -j cores number of connection to use to import data or indexes into PostgreSQL" 693 echo " -n schema comma separated list of schema to create" 694 echo " -o username owner of the database to create" 695 echo " -p port listening port of the PostgreSQL server (default: 5432)" 696 echo " -P cores number of tables to process at same time for data import" 697 echo " -s import schema only, do not try to import data" 698 echo " -t export comma separated list of export type to import (same as ora2pg)" 699 echo " -U username username to connect to PostgreSQL (default: peer username)" 700 echo " -x import indexes and constraints after data" 701 echo " -y reply Yes to all questions for automatic import" 702 echo 703 echo " -? print help" 704 echo 705 exit $1 706} 707 708# Function to emulate Perl prompt function 709confirm () { 710 711 msg=$1 712 if [ "$AUTORUN" != "0" ]; then 713 true 714 else 715 if [ -z "$msg" ]; then 716 msg="Are you sure? [y/N/q]" 717 fi 718 # call with a prompt string or use a default 719 read -r -p "${msg} [y/N/q] " response 720 case $response in 721 [yY][eE][sS]|[yY]) 722 true 723 ;; 724 [qQ][uU][iI][tT]|[qQ]) 725 exit 726 ;; 727 *) 728 false 729 ;; 730 esac 731 fi 732} 733 734# Function used to import constraints and indexes 735import_constraints () { 736 if [ -r "$NAMESPACE/schema/tables/INDEXES_table.sql" ]; then 737 if confirm "Would you like to import indexes from $NAMESPACE/schema/tables/INDEXES_table.sql?" ; then 738 if [ -z "$IMPORT_JOBS" ]; then 739 echo "Running: psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/tables/INDEXES_table.sql" 740 if [ $DEBUG -eq 0 ]; then 741 psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/tables/INDEXES_table.sql 742 if [ $? -ne 0 ]; then 743 die "can not import indexes." 744 fi 745 fi 746 else 747 echo "Running: ora2pg -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/INDEXES_table.sql" 748 if [ $DEBUG -eq 0 ]; then 749 ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/INDEXES_table.sql 750 if [ $? -ne 0 ]; then 751 die "can not import indexes." 752 fi 753 fi 754 fi 755 fi 756 fi 757 758 if [ -r "$NAMESPACE/schema/tables/CONSTRAINTS_table.sql" ]; then 759 if confirm "Would you like to import constraints from $NAMESPACE/schema/tables/CONSTRAINTS_table.sql?" ; then 760 if [ -z "$IMPORT_JOBS" ]; then 761 echo "Running: psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/tables/CONSTRAINTS_table.sql" 762 if [ $DEBUG -eq 0 ]; then 763 psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/tables/CONSTRAINTS_table.sql 764 if [ $? -ne 0 ]; then 765 die "can not import constraints." 766 fi 767 fi 768 else 769 echo "Running: ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/CONSTRAINTS_table.sql" 770 if [ $DEBUG -eq 0 ]; then 771 ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/CONSTRAINTS_table.sql 772 if [ $? -ne 0 ]; then 773 die "can not import constraints." 774 fi 775 fi 776 fi 777 fi 778 fi 779 780 if [ -r "$NAMESPACE/schema/tables/FKEYS_table.sql" ]; then 781 if confirm "Would you like to import foreign keys from $NAMESPACE/schema/tables/FKEYS_table.sql?" ; then 782 if [ -z "$IMPORT_JOBS" ]; then 783 echo "Running: psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/tables/FKEYS_table.sql" 784 if [ $DEBUG -eq 0 ]; then 785 psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/tables/FKEYS_table.sql 786 if [ $? -ne 0 ]; then 787 die "can not import foreign keys." 788 fi 789 fi 790 else 791 echo "Running: ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/FKEYS_table.sql" 792 if [ $DEBUG -eq 0 ]; then 793 ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/FKEYS_table.sql 794 if [ $? -ne 0 ]; then 795 die "can not import foreign keys." 796 fi 797 fi 798 fi 799 fi 800 fi 801 802 if [ $NO_CONSTRAINTS -eq 1 ] && [ -r "$NAMESPACE/schema/triggers/trigger.sql" ]; then 803 if confirm "Would you like to import TRIGGER from $NAMESPACE/schema/triggers/trigger.sql?" ; then 804 echo "Running: psql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/triggers/trigger.sql" 805 if [ $DEBUG -eq 0 ]; then 806 psql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/triggers/trigger.sql 807 if [ $? -ne 0 ]; then 808 die "an error occurs when importing file $NAMESPACE/schema/triggers/trigger.sql." 809 fi 810 fi 811 fi 812 fi 813} 814 815# Command line options 816while getopts "b:d:e:h:j:l:n:o:p:P:t:U:aDfiIsyx?" opt; do 817 case "$opt" in 818 a) IMPORT_DATA=1;; 819 b) SQL_POST_SCRIPT=$OPTARG;; 820 d) DB_NAME=$OPTARG;; 821 D) DEBUG=1;; 822 e) DB_ENCODING=" -E $OPTARG";; 823 f) NO_DBCHECK=1;; 824 h) DB_HOST=" -h $OPTARG";; 825 i) IMPORT_CONSTRAINTS=1;; 826 I) NO_CONSTRAINTS=1;; 827 j) IMPORT_JOBS=" -j $OPTARG";; 828 n) DB_SCHEMA=$OPTARG;; 829 o) DB_OWNER=$OPTARG;; 830 p) DB_PORT=" -p $OPTARG";; 831 P) PARALLEL_TABLES=" -P $OPTARG";; 832 s) IMPORT_SCHEMA=1;; 833 t) EXPORT_TYPE=$OPTARG;; 834 U) DB_USER=" -U $OPTARG";; 835 x) IMPORT_INDEXES_AFTER=1;; 836 y) AUTORUN=1;; 837 "?") usage 1;; 838 *) die "Unknown error while processing options";; 839 esac 840done 841 842# Check if post tables import SQL script is readable 843if [ ! -z "$SQL_POST_SCRIPT" ]; then 844 if [ ! -r "$SQL_POST_SCRIPT" ]; then 845 die "the SQL script $SQL_POST_SCRIPT is not readable." 846 fi 847fi 848 849# A database name is mandatory 850if [ -z "$DB_NAME" ]; then 851 die "you must give a PostgreSQL database name (see -d option)." 852fi 853 854# A database owner is mandatory 855if [ -z "$DB_OWNER" ]; then 856 die "you must give a username to be used as owner of database (see -o option)." 857fi 858 859# Check if the project directory is readable 860if [ ! -r "$NAMESPACE/schema/tables/table.sql" ]; then 861 die "project directory '$NAMESPACE' is not valid or is not readable." 862fi 863 864# If constraints and indexes files are present propose to import these object 865if [ $IMPORT_CONSTRAINTS -eq 1 ]; then 866 if confirm "Would you like to load indexes, constraints and triggers?" ; then 867 import_constraints 868 fi 869 exit 0 870fi 871 872# When a PostgreSQL schema list is provided, create them 873if [ $IMPORT_DATA -eq 0 ]; then 874 is_superuser='f' 875 if [ $NO_DBCHECK -eq 0 ]; then 876 # Create owner user 877 user_exists=`psql -d $DB_NAME$DB_HOST$DB_PORT$DB_USER -Atc "select usename from pg_user where usename='$DB_OWNER';"` 878 is_superuser=`psql -d $DB_NAME$DB_HOST$DB_PORT$DB_USER -Atc "select usesuper from pg_user where usename='$DB_OWNER';"`; 879 if [ "a$user_exists" = "a" ]; then 880 if confirm "Would you like to create the owner of the database $DB_OWNER?" ; then 881 echo "Running: createuser$DB_HOST$DB_PORT$DB_USER --no-superuser --no-createrole --no-createdb $DB_OWNER" 882 if [ $DEBUG -eq 0 ]; then 883 createuser$DB_HOST$DB_PORT$DB_USER --no-superuser --no-createrole --no-createdb $DB_OWNER 884 if [ $? -ne 0 ]; then 885 die "can not create user $DB_OWNER." 886 fi 887 fi 888 fi 889 else 890 echo "Database owner $DB_OWNER already exists, skipping creation." 891 fi 892 893 # Create database if required 894 if [ "a$DB_ENCODING" = "a" ]; then 895 DB_ENCODING=" -E UTF8" 896 fi 897 db_exists=`psql -d $DB_NAME$DB_HOST$DB_PORT$DB_USER -Atc "select datname from pg_database where datname='$DB_NAME';"` 898 if [ "a$db_exists" = "a" ]; then 899 if confirm "Would you like to create the database $DB_NAME?" ; then 900 echo "Running: createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME" 901 if [ $DEBUG -eq 0 ]; then 902 createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME 903 if [ $? -ne 0 ]; then 904 die "can not create database $DB_NAME." 905 fi 906 fi 907 fi 908 else 909 if confirm "Would you like to drop the database $DB_NAME before recreate it?" ; then 910 echo "Running: dropdb$DB_HOST$DB_PORT$DB_USER $DB_NAME" 911 if [ $DEBUG -eq 0 ]; then 912 dropdb$DB_HOST$DB_PORT$DB_USER $DB_NAME 913 if [ $? -ne 0 ]; then 914 die "can not drop database $DB_NAME." 915 fi 916 fi 917 echo "Running: createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME" 918 if [ $DEBUG -eq 0 ]; then 919 createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME 920 if [ $? -ne 0 ]; then 921 die "can not create database $DB_NAME." 922 fi 923 fi 924 fi 925 fi 926 fi 927 928 # When schema list is provided, create them 929 if [ "a$DB_SCHEMA" != "a" ]; then 930 nspace_list='' 931 for enspace in $(echo $DB_SCHEMA | tr "," "\n") 932 do 933 lnspace=`echo $enspace | tr '[:upper:]' '[:lower:]'` 934 if confirm "Would you like to create schema $lnspace in database $DB_NAME?" ; then 935 echo "Running: psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -c \"CREATE SCHEMA $lnspace;\"" 936 if [ $DEBUG -eq 0 ]; then 937 psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -c "CREATE SCHEMA $lnspace;" 938 if [ $? -ne 0 ]; then 939 die "can not create schema $DB_SCHEMA." 940 fi 941 fi 942 nspace_list="$nspace_list$lnspace," 943 fi 944 done 945 # Change search path of the owner 946 if [ "a$nspace_list" != "a" ]; then 947 if confirm "Would you like to change search_path of the database owner?" ; then 948 echo "Running: psql$DB_HOST$DB_PORT$DB_USER -d $DB_NAME -c \"ALTER ROLE $DB_OWNER SET search_path TO ${nspace_list}public;\"" 949 if [ $DEBUG -eq 0 ]; then 950 psql$DB_HOST$DB_PORT$DB_USER -d $DB_NAME -c "ALTER ROLE $DB_OWNER SET search_path TO ${nspace_list}public;" 951 if [ $? -ne 0 ]; then 952 die "can not change search_path." 953 fi 954 fi 955 fi 956 fi 957 fi 958 959 # Then import all files from project directory 960 for etype in $(echo $EXPORT_TYPE | tr "," "\n") 961 do 962 963 if [ $NO_CONSTRAINTS -eq 1 ] && [ $etype = "TRIGGER" ]; then 964 continue 965 fi 966 967 if [ $etype = "GRANT" ] || [ $etype = "TABLESPACE" ]; then 968 continue 969 fi 970 971 ltype=`echo $etype | tr '[:upper:]' '[:lower:]'` 972 ltype=`echo $ltype | sed 's/y$/ie/'` 973 if [ -r "$NAMESPACE/schema/${ltype}s/$ltype.sql" ]; then 974 if confirm "Would you like to import $etype from $NAMESPACE/schema/${ltype}s/$ltype.sql?" ; then 975 echo "Running: psql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/${ltype}s/$ltype.sql" 976 if [ $DEBUG -eq 0 ]; then 977 psql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/schema/${ltype}s/$ltype.sql 978 if [ $? -ne 0 ]; then 979 die "an error occurs when importing file $NAMESPACE/schema/${ltype}s/$ltype.sql." 980 fi 981 fi 982 fi 983 fi 984 if [ ! -z "$SQL_POST_SCRIPT" ] && [ $etype = "TABLE" ]; then 985 if confirm "Would you like to execute SQL script $SQL_POST_SCRIPT?" ; then 986 echo "Running: psql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $SQL_POST_SCRIPT" 987 if [ $DEBUG -eq 0 ]; then 988 psql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $SQL_POST_SCRIPT 989 if [ $? -ne 0 ]; then 990 die "an error occurs when importing file $SQL_POST_SCRIPT." 991 fi 992 fi 993 fi 994 fi 995 done 996 997 # If constraints and indexes files are present propose to import these object 998 if [ $NO_CONSTRAINTS -eq 0 ] && [ $IMPORT_INDEXES_AFTER -eq 0 ]; then 999 if confirm "Would you like to process indexes and constraints before loading data?" ; then 1000 IMPORT_INDEXES_AFTER=0 1001 import_constraints 1002 else 1003 IMPORT_INDEXES_AFTER=1 1004 fi 1005 fi 1006 1007 # When the database owner is not superuser use postgres instead 1008 q_user='postgres' 1009 if [ "$is_superuser" = "t" ]; then 1010 q_user=$DB_OWNER 1011 fi 1012 1013 # Import objects that need superuser privilege: GRANT and TABLESPACE 1014 if [ -r "$NAMESPACE/schema/grants/grant.sql" ]; then 1015 if confirm "Would you like to import GRANT from $NAMESPACE/schema/grants/grant.sql?" ; then 1016 echo "Running: psql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/grants/grant.sql" 1017 if [ $DEBUG -eq 0 ]; then 1018 psql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/grants/grant.sql 1019 if [ $? -ne 0 ]; then 1020 die "an error occurs when importing file $NAMESPACE/schema/grants/grant.sql." 1021 fi 1022 fi 1023 fi 1024 fi 1025 if [ -r "$NAMESPACE/schema/tablespaces/tablespace.sql" ]; then 1026 if confirm "Would you like to import TABLESPACE from $NAMESPACE/schema/tablespaces/tablespace.sql?" ; then 1027 echo "Running: psql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/tablespaces/tablespace.sql" 1028 if [ $DEBUG -eq 0 ]; then 1029 psql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/tablespaces/tablespace.sql 1030 if [ $? -ne 0 ]; then 1031 die "an error occurs when importing file $NAMESPACE/schema/tablespaces/tablespace.sql." 1032 fi 1033 fi 1034 fi 1035 fi 1036fi 1037 1038 1039# Check if we must just import schema or proceed to data import too 1040if [ $IMPORT_SCHEMA -eq 0 ]; then 1041 # set the PostgreSQL datasource 1042 pgdsn_defined=`grep "^PG_DSN" config/ora2pg.conf | sed 's/.*dbi:Pg/dbi:Pg/'` 1043 if [ "a$pgdsn_defined" = "a" ]; then 1044 if [ "a$DB_HOST" != "a" ]; then 1045 pgdsn_defined="dbi:Pg:dbname=$DB_NAME;host=$DB_HOST" 1046 else 1047 #default to unix socket 1048 pgdsn_defined="dbi:Pg:dbname=$DB_NAME;" 1049 fi 1050 if [ "a$DB_PORT" != "a" ]; then 1051 pgdsn_defined="$pgdsn_defined;port=$DB_PORT" 1052 else 1053 pgdsn_defined="$pgdsn_defined;port=5432" 1054 fi 1055 fi 1056 1057 # remove command line option from the DSN string 1058 pgdsn_defined=`echo "$pgdsn_defined" | sed 's/ -. //g'` 1059 1060 # If data file is present propose to import data 1061 if [ -r "$NAMESPACE/data/data.sql" ]; then 1062 if confirm "Would you like to import data from $NAMESPACE/data/data.sql?" ; then 1063 echo "Running: psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/data/data.sql" 1064 if [ $DEBUG -eq 0 ]; then 1065 psql$DB_HOST$DB_PORT -U $DB_OWNER -d $DB_NAME -f $NAMESPACE/data/data.sql 1066 if [ $? -ne 0 ]; then 1067 die "an error occurs when importing file $NAMESPACE/data/data.sql." 1068 fi 1069 fi 1070 fi 1071 else 1072 # Import data directly from PostgreSQL 1073 if confirm "Would you like to import data from Oracle database directly into PostgreSQL?" ; then 1074 echo "Running: ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t COPY --pg_dsn \"$pgdsn_defined\" --pg_user $DB_OWNER" 1075 if [ $DEBUG -eq 0 ]; then 1076 ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t COPY --pg_dsn "$pgdsn_defined" --pg_user $DB_OWNER 1077 if [ $? -ne 0 ]; then 1078 die "an error occurs when importing data." 1079 fi 1080 fi 1081 fi 1082 fi 1083 1084 if [ $NO_CONSTRAINTS -eq 0 ] && [ $IMPORT_DATA -eq 0 ]; then 1085 # Import indexes and constraint after data 1086 if [ $IMPORT_INDEXES_AFTER -eq 1 ]; then 1087 import_constraints 1088 fi 1089 fi 1090fi 1091 1092exit 0 1093 1094