|
#!/usr/bin/perl
# dbusers - manage MySQL user table
use ExtUtils::testlib;
use Getopt::Long;
use Config;
use Getopt::Std;
use Oraperl;
format HEADER =
hbjxzl.cublog.cn auxiliary Data Utility Version 1.0.0
This script will auxiliary data and struct.
-h - Give help screen
-C clear - truncate owner's all tables
-E export - export owner's data or struct
-D drop - for drop all data or struct
Examples:
auxiliary.pl -C/c
auxiliary.pl -E/e
auxiliary.pl -D/d
.
sub do_help {
# Give help screen and exit
$~ = "HEADER";
write;
exit();
}
sub replace_null_to_{
$str=shift;
if($str){
$str =~ s/ /_/g;
}
return $str;
}
sub replace_enter_to_{
$str=shift;
if($str){
$str =~ s/\n//g;
}
return $str;
}
sub write_file{
$put_name=shift;
$put_trunc_name=shift;
$put_drop_name=shift;
open(OUTPUT, "> $put_name ")
or die "Couldn't open $put_name for write: $!\n";
if ($put_trunc_name){
open(TRUNCATE_OUTPUT, "> $put_trunc_name ")
or die "Couldn't open $put_trunc_name for write: $!\n";
}
if ($put_drop_name){
open(DROP_OUTPUT, "> $put_drop_name ")
or die "Couldn't open $put_drop_name for write: $!\n";
}
}
sub close_write_file{
close(OUTPUT);
close(TRUNCATE_OUTPUT);
close(DROP_OUTPUT);
}
sub read_file{
$get_name=shift;
open(INPUT, "< $get_name ")
or die "Couldn't open $put_name for read: $!\n";
}
sub close_read_file{
close(INPUT);
}
sub connect_db{
my $dbname=shift;
my $dbuser=shift;
my $dbpasswd=shift;
#print " test dbname is ",$dbname," user is ", $dbuser," passwd is ", $dbpasswd,"\n";
$dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser,$dbpasswd,{ RaiseError=>1 });
return $dbh;
}
sub dis_connect_db{
$dbh->disconnect;
}
sub print_array{
my @cfg_lines=split(" ",$_);
foreach (@cfg_lines){
print $_,"\t";
}
print "\n";
return @cfg_lines;
}
sub obj_sql{
$sql_code=shift;
if($sql_code ==1){
$get_sql="select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) from dba_tables where owner = ? and table_name = ? "; #for all table's
}elsif($sql_code==2){
$get_sql="select dbms_metadata.get_ddl('INDEX',INDEX_NAME,OWNER) from dba_indexes where owner = ? and index_name = ? "; #for all index's
}elsif($sql_code==3){
$get_sql="select dbms_metadata.get_ddl('CONSTRAINT',CONSTRAINT_NAME,OWNER) from dba_constraints where owner = ? and constraint_name = ? "; #for all constraint's
}elsif($sql_code==4){
$get_sql="select dbms_metadata.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER) from dba_constraints where owner = ? and constraint_name = ? "; #for all foreign constraint's
}elsif($sql_code==5){
$get_sql="select distinct owner,index_name from dba_indexes di where owner like ? and table_name like ? and (owner,index_name) not in (select owner,constraint_name from dba_constraints dc where dc.owner=di.owner and dc.constraint_name=di.index_name) "; #for get table's indexes
}elsif($sql_code==6){#for get table's constraints
$get_sql="select distinct owner,constraint_name,constraint_type from dba_constraints where owner like ? and table_name like ? and constraint_name not like 'SYS_%'";
}elsif($sql_code==7){#for get all function
$get_sql="select dbms_metadata.get_ddl('FUNCTION',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'FUNCTION'";
}elsif($sql_code==8){#for gete all procedure
$get_sql="select dbms_metadata.get_ddl('PROCEDURE',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'PROCEDURE'";
}elsif($sql_code==9){#for get all package head
$get_sql="select dbms_metadata.get_ddl('PACKAGE_SPEC',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'PACKAGE'";
}elsif($sql_code==10){#for get all package body
$get_sql="select dbms_metadata.get_ddl('PACKAGE_BODY',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'PACKAGE BODY'";
}elsif($sql_code==11){#for get all triggers
$get_sql="select dbms_metadata.get_ddl('TRIGGER',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'TRIGGER'";
}elsif($sql_code==12){#for get all synonym
$get_sql="select dbms_metadata.get_ddl('SYNONYM',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'SYNONYM'";
}elsif($sql_code==13){#for get all sequence
$get_sql="select dbms_metadata.get_ddl('SEQUENCE',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'SEQUENCE'";
}elsif($sql_code==14){#for get all sequence
$get_sql="select dbms_metadata.get_ddl('TYPE_SPEC',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'TYPE'";
}elsif($sql_code==15){#for get all sequence
$get_sql="select dbms_metadata.get_ddl('TYPE_BODY',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'TYPE BODY'";
}elsif($sql_code==16){#for get all sequence
$get_sql="select dbms_metadata.get_ddl('VIEW',OBJECT_NAME,OWNER) from dba_objects where owner like ? and object_name like ? and object_type like 'VIEW'";
}elsif($sql_code==17){#for get all sequence
$get_sql="select 'drop table '||owner||'.'||table_name as dro_tab from dba_tables where owner like ? and table_name like ? ";
}elsif($sql_code==19){#for get all tables
$get_sql="select owner,table_name from dba_tables where owner = ? and cluster_name is null";
}elsif($sql_code==20){#for get all object expried table
$get_sql="select owner,object_name,object_type from dba_objects where owner = ? and object_type in ('SEQUENCE','SYNONYM','FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','TRIGGER','TYPE','TYPE BODY','VIEW')";
}else{
$get_sql="Err!";
}
return $get_sql;
}
sub obj_type{
my $dbh = shift;
my $obj_owner= shift;
my $obj_name = shift;
my $obj_type= shift;
my $get_obj_sql=&obj_sql($obj_type);
#print "test ",$get_obj_sql,"\n\t{dt is $obj_owner\.$obj_name\[$obj_type\]}\n";
my $sth = $dbh->prepare($get_obj_sql);
$sth->execute($obj_owner,$obj_name);
my @row = $sth->fetchrow;
return $row[0];
}
sub tab_ind_sql{
my $dbh = shift;
my $obj_owner= shift;
my $obj_name = shift;
my $data_text;
my $get_obj_sql=&obj_sql(5);
my $sth = $dbh->prepare($get_obj_sql);
$sth->execute($obj_owner,$obj_name);
while (@row= $sth->fetchrow){
$ind_sql=&obj_type($dbh,$row[0],$row[1],2);
$data_text= join("\n",$data_text,$ind_sql,"/") ;
}
$get_obj_sql="";
@row="";
my $get_obj_sql=&obj_sql(6);
my $sth = $dbh->prepare($get_obj_sql);
$sth->execute($obj_owner,$obj_name);
while (@row= $sth->fetchrow){
if($row[2] =~ m/R/){
$ind_sql=&obj_type($dbh,$row[0],$row[1],4);
}else{
$ind_sql=&obj_type($dbh,$row[0],$row[1],3);
}
$data_text= join("\n",$data_text,$ind_sql,"/") ;
}
return $data_text;
}
sub tab_struct{ # also used by test_leak()
my $dbh = shift;
my $tab_owner= shift;
my $tab_name = shift;
my $tmp_text;
$tmp_tab_text=&obj_type($dbh,$tab_owner,$tab_name,1);
$tmp_ind_text=&tab_ind_sql($dbh,$tab_owner,$tab_name);
$tmp_text= join "","--","="x30,$tab_owner,".",$tab_name,"="x30;
$tmp_text = join ("\n",$tmp_text,$tmp_tab_text,"/\n");
$tmp_text = join ("\n",$tmp_text,$tmp_ind_text,"\n");
return $tmp_text;
}
sub get_all_tabs_str{
my $dbh = shift;
my $exp_owner= shift;
my $get_obj_sql=&obj_sql(19);
my $sth = $dbh->prepare($get_obj_sql);
#print $get_obj_sql,"\t",$exp_owner,"\n";
$sth->execute($exp_owner);
while (@row= $sth->fetchrow){
$tab_owner=$row[0];
$tab_name=$row[1];
my $tab_str_data=&tab_struct($dbh,$tab_owner,$tab_name);
print OUTPUT $tab_str_data;
my $trunc_tab="TRUNCATE TABLE ";
$trunc_tab=join(" ",$trunc_tab,$tab_owner);
$trunc_tab=join(".",$trunc_tab,$tab_name);
print TRUNCATE_OUTPUT $trunc_tab,"\n";
#print $trunc_tab,$tab_owner,$tab_name;
my $drop_tab="DROP TABLE ";
$drop_tab=join(" ",$drop_tab,$tab_owner);
$drop_tab=join(".",$drop_tab,$tab_name);
print DROP_OUTPUT $drop_tab,"\n";
#print "test ",$drop_tab,"\n";;
}
}
sub get_all_obj_str{
my $dbh = shift;
my $exp_owner= shift;
my $p_orders = shift;
my $get_obj_sql=&obj_sql(20);
my $sth = $dbh->prepare($get_obj_sql);
#print $get_obj_sql,"\t",$exp_owner,"\n";
$sth->execute($exp_owner);
while (@row= $sth->fetchrow){
my $tmp_text= join "","--","="x30,$row[0],".",$row[1],"{",$row[2],"}","="x30;
$obj_str_data="";
#print @row[0],"\t",@row[1],"\t",$row[2],"\n";
if($row[2] =~ m/FUNCTION/){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],7);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/PROCEDURE/ && $p_orders==2 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],8);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/^PACKAGE$/ && $p_orders==2){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],9);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/PACKAGE BODY/ && $p_orders==2 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],10);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/TRIGGER/ && $p_orders==2 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],11);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/SYNONYM/ && $p_orders==2 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],12);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/SEQUENCE/ && $p_orders==1 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],13);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/^TYPE$/ && $p_orders==1 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],14);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/TYPE BODY/ && $p_orders==1 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],15);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}elsif($row[2] =~ m/^VIEW$/ && $p_orders==2 ){
$obj_str_data=&obj_type($dbh,$row[0],$row[1],16);
$drop_sql=join(""," drop ",$row[2]," ",$row[0],".",$row[1],"\n");
}else{
$obj_str_data="\n--Not export for $row[0],$row[1],{$row[2]},Please ask zhangl@dsgdata.com\n";
#print $obj_str_data,"\n";
}
#print $obj_str_data,"/\n";
print DROP_OUTPUT $drop_sql;
print OUTPUT $tmp_text,"\n";
print OUTPUT $obj_str_data,"/\n";
}
}
sub exp_owner_struct{
my $exp_owner=shift;
my $dbname =shift;
my $dbuser =shift;
my $dbpasswd=shift;
my $exp_file = shift;
my $truncate_file = shift;
my $drop_file = shift;
&connect_db($dbname,$dbuser,$dbpasswd);
$dbh->{LongReadLen} = 512 * 1024;
&write_file($exp_file,$truncate_file,$drop_file);
&get_all_obj_str($dbh,$exp_owner,1);
&get_all_tabs_str($dbh,$exp_owner);
&get_all_obj_str($dbh,$exp_owner,2);
&close_write_file;
&dis_connect_db();
}
sub clean_owner_obj{
my $exp_owner=shift;
my $dbname =shift;
my $dbuser =shift;
my $dbpasswd=shift;
my $drop_file =shift;
&connect_db($dbname,$dbuser,$dbpasswd);
&read_file($drop_file);
while(<INPUT>){
my $sth = $dbh->prepare($_);
$sth->execute;
print $_;
}
&close_read_file;
&dis_connect_db();
}
sub oper_from_file{
$cfg_file=shift;
$oper_p =shift;
open(INPUT_CONFIG, "< $cfg_file")
or die "Couldn't open $cfg_file for read: $!\n";
while (<INPUT_CONFIG>){
if(m/[_0-9a-zA-Z]/){
@cfg_lines=&print_array($_);
if ( $cfg_lines[4] ){
$cfg_exp_file=$cfg_lines[4];
}else{
$cfg_exp_file=sprintf("cfg_exp_%s_file.sql",lc($cfg_lines[0]));
}
if ( $cfg_lines[5] ){
$cfg_trunc_file=$cfg_lines[5];
}else{
$cfg_trunc_file=sprintf("cfg_trunc_%s_file.sql",lc($cfg_lines[0]));
}
if ( $cfg_lines[6] ){
$cfg_drop_file=$cfg_lines[6];
}else{
$cfg_drop_file=sprintf("cfg_drop_%s_file.sql",lc($cfg_lines[0]));
}
#print "test is ",$oper_p,"\t",$cfg_lines[0],"\t",$cfg_lines[1],"\t", $cfg_lines[2],"\t",$cfg_lines[3],"\n";
if ($oper_p==1){
&exp_owner_struct(uc($cfg_lines[0]),$cfg_lines[1],$cfg_lines[2],$cfg_lines[3],$cfg_exp_file,$cfg_trunc_file,$cfg_drop_file);
}elsif($oper_p==2){
&clean_owner_obj($cfg_lines[0],$cfg_lines[1],$cfg_lines[2],$cfg_lines[3],$cfg_trunc_file);
}elsif($oper_p==3){
&clean_owner_obj($cfg_lines[0],$cfg_lines[1],$cfg_lines[2],$cfg_lines[3],$cfg_drop_file);
}
}
}
close(INPUT_CONFIG);
}
sub do_opts{
%options=();
getopts("eEhHcCiIdD",\%options);
&do_help if ($options{h} or $options{H});
# like the shell getopt, "d:" means d takes an argument
if (defined $options{e}){
&oper_from_file("cfg.config",1);
}elsif(defined $options{E}){
&oper_from_file("cfg.config",1);
}elsif( defined $options{c}){
&oper_from_file("cfg.config",2);
}elsif( defined $options{C}){
&oper_from_file("cfg.config",2);
}elsif( defined $options{d}){
&oper_from_file("cfg.config",3);
}elsif( defined $options{D}){
&oper_from_file("cfg.config",3);
}elsif( defined $options{h}){
$do_help;
}elsif( defined $options{H}){
$do_help;
}else{
print "test opts Err:";
}
}
$argv=scalar @ARGV;
#print "test argv is :",$argv,"\n";
if($argv==0){
&do_help;
}else{
&do_opts;
}
|
