You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
540 lines
16 KiB
540 lines
16 KiB
package NGCP::BulkProcessor::SqlConnectors::OracleDB;
|
|
use strict;
|
|
|
|
## no critic
|
|
|
|
use NGCP::BulkProcessor::Globals qw($LongReadLen_limit);
|
|
use NGCP::BulkProcessor::Logging qw(
|
|
getlogger
|
|
dbinfo
|
|
dbdebug
|
|
texttablecreated
|
|
temptablecreated
|
|
indexcreated
|
|
primarykeycreated
|
|
tabletruncated
|
|
tabledropped);
|
|
use NGCP::BulkProcessor::LogError qw(dberror dbwarn fieldnamesdiffer);
|
|
|
|
use DBI;
|
|
use DBD::Oracle 1.21;
|
|
|
|
use NGCP::BulkProcessor::Array qw(contains arrayeq setcontains);
|
|
|
|
use NGCP::BulkProcessor::SqlConnector;
|
|
|
|
require Exporter;
|
|
our @ISA = qw(Exporter NGCP::BulkProcessor::SqlConnector);
|
|
our @EXPORT_OK = qw(get_tableidentifier);
|
|
|
|
my $defaultport = '1521';
|
|
|
|
#$NLS_LANG = 'GERMAN_AUSTRIA.WE8ISO8859P1';
|
|
|
|
my $connNLS_LANGUAGE = 'GERMAN';
|
|
my $connNLS_TERRITORY = 'AUSTRIA';
|
|
|
|
#my $connNLS_CHARACTERSET = 'WE8ISO8859P1';
|
|
|
|
my $varcharsize = 4000;
|
|
my $max_identifier_length = 30;
|
|
|
|
my $LongReadLen = $LongReadLen_limit; #bytes
|
|
my $LongTruncOk = 0;
|
|
|
|
#my $logger = getlogger(__PACKAGE__);
|
|
|
|
#my $lock_do_chunk = 0;
|
|
#my $lock_get_chunk = 0;
|
|
|
|
my $rowblock_transactional = 1;
|
|
|
|
my $isolation_level = ''; #'SERIALIZABLE'
|
|
|
|
my $enable_numeric_sorting = 0;
|
|
|
|
sub new {
|
|
|
|
my $class = shift;
|
|
|
|
my $self = NGCP::BulkProcessor::SqlConnector->new(@_);
|
|
|
|
$self->{host} = undef;
|
|
$self->{port} = undef;
|
|
$self->{servicename} = undef;
|
|
$self->{sid} = undef;
|
|
$self->{username} = undef;
|
|
$self->{password} = undef;
|
|
$self->{schema} = undef;
|
|
|
|
$self->{drh} = DBI->install_driver('Oracle');
|
|
|
|
bless($self,$class);
|
|
|
|
dbdebug($self,__PACKAGE__ . ' connector created',getlogger(__PACKAGE__));
|
|
|
|
return $self;
|
|
|
|
}
|
|
|
|
sub _connectidentifier {
|
|
|
|
my $self = shift;
|
|
if (defined $self->{schema}) {
|
|
if ($self->{servicename}) {
|
|
return $self->{username} . '@' . $self->{host} . ':' . $self->{port} . '/' . $self->{servicename} . '.' . $self->{schema};
|
|
} elsif ($self->{sid}) {
|
|
return $self->{username} . '@' . $self->{host} . ':' . $self->{port} . '/SID ' . $self->{sid} . '.' . $self->{schema};
|
|
} else {
|
|
return undef;
|
|
}
|
|
} else {
|
|
return undef;
|
|
}
|
|
|
|
|
|
}
|
|
|
|
sub tableidentifier {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
return get_tableidentifier($tablename,$self->{schema});
|
|
|
|
}
|
|
|
|
sub _columnidentifier {
|
|
|
|
my $self = shift;
|
|
my $columnname = shift;
|
|
|
|
return $columnname;
|
|
|
|
}
|
|
|
|
sub _chopidentifier {
|
|
my $identifier = shift;
|
|
return substr($identifier,0,$max_identifier_length);
|
|
}
|
|
|
|
sub get_tableidentifier {
|
|
|
|
my ($tablename,$schema,$servicename,$sid) = @_;
|
|
my $tableidentifier = $tablename;
|
|
if (defined $schema) {
|
|
$tableidentifier = $schema . '.' . $tableidentifier;
|
|
}
|
|
if ($servicename) {
|
|
$tableidentifier = $servicename . '.' . $tableidentifier;
|
|
} elsif ($sid) {
|
|
$tableidentifier = $sid . '.' . $tableidentifier;
|
|
}
|
|
return $tableidentifier;
|
|
|
|
}
|
|
|
|
sub getsafetablename {
|
|
|
|
my $self = shift;
|
|
my $tableidentifier = shift;
|
|
return uc($self->SUPER::getsafetablename($tableidentifier));
|
|
#if (defined $self->{schema}) {
|
|
# return $self->{schema} . '.' . $tablename;
|
|
#} else {
|
|
# return $tablename;
|
|
#}
|
|
|
|
}
|
|
|
|
sub paginate_sort_query {
|
|
|
|
my $self = shift;
|
|
my $statement = shift;
|
|
my $offset = shift;
|
|
my $limit = shift;
|
|
my $sortingconfigurations = shift;
|
|
|
|
my $orderby = $self->_orderby_columns($sortingconfigurations);
|
|
if (length($orderby) > 0) {
|
|
$statement .= ' ORDER BY ' . $orderby;
|
|
}
|
|
if (defined $offset and defined $limit) {
|
|
$statement = 'SELECT * FROM (SELECT p.*, rownum rnum FROM (' . $statement . ') p WHERE rownum < ' . ($offset + $limit + 1) . ') WHERE rnum >= ' . ($offset + 1);
|
|
}
|
|
return $statement;
|
|
|
|
}
|
|
|
|
sub _force_numeric_column {
|
|
my $self = shift;
|
|
my $column = shift;
|
|
return 'try_to_number(' . $column . ')';
|
|
}
|
|
|
|
sub getdatabases {
|
|
|
|
my $self = shift;
|
|
return $self->db_get_col('SELECT DISTINCT owner FROM all_objects');
|
|
|
|
}
|
|
|
|
#sub _createdatabase {
|
|
#
|
|
# my $self = shift;
|
|
# my ($schema) = @_;
|
|
#
|
|
# #SQL> create tablespace test datafile 'C:\oraclexe\app\oracle\oradata\XE\test.dbf' size 10M autoextend on;
|
|
# #Tablespace created.
|
|
# #SQL> create user test identified by test default tablespace test;
|
|
# #User created.
|
|
# #alter user test quota unlimited on test
|
|
#
|
|
# $self->db_do('CREATE SCHEMA AUTHORIZATION ' . $schema);
|
|
# dbinfo($self,'schema \'' . $schema . '\' created',getlogger(__PACKAGE__));
|
|
#
|
|
#}
|
|
|
|
sub db_connect {
|
|
|
|
my $self = shift;
|
|
|
|
my ($servicename,$sid,$schema,$username,$password,$host,$port,$create_database) = @_;
|
|
|
|
$self->SUPER::db_connect($servicename,$sid,$schema,$username,$password,$host,$port);
|
|
|
|
$port = $defaultport if (not $port);
|
|
|
|
$self->{host} = $host;
|
|
$self->{port} = $port;
|
|
$self->{servicename} = $servicename;
|
|
$self->{sid} = $sid;
|
|
$self->{username} = $username;
|
|
$self->{password} = $password;
|
|
$self->{schema} = $schema;
|
|
|
|
dbdebug($self,'connecting',getlogger(__PACKAGE__));
|
|
|
|
my $dbh;
|
|
if ($servicename) {
|
|
$dbh = DBI->connect(
|
|
'dbi:Oracle:host=' . $host . ';service_name=' . $servicename . ';port=' . $port,$username,$password,
|
|
{
|
|
PrintError => 0,
|
|
RaiseError => 0,
|
|
AutoCommit => 1,
|
|
#AutoCommit => 0,
|
|
}
|
|
) or dberror($self,'error connecting - service name: ' . $self->{drh}->errstr(),getlogger(__PACKAGE__));
|
|
} elsif ($sid) {
|
|
$dbh = DBI->connect(
|
|
'dbi:Oracle:host=' . $host . ';sid=' . $sid . ';port=' . $port,$username,$password,
|
|
{
|
|
PrintError => 0,
|
|
RaiseError => 0,
|
|
AutoCommit => 1,
|
|
}
|
|
) or dberror($self,'error connecting - sid: ' . $self->{drh}->errstr(),getlogger(__PACKAGE__));
|
|
} else {
|
|
dberror($self,'neither service name nor sid specified',getlogger(__PACKAGE__));
|
|
}
|
|
|
|
$self->{dbh} = $dbh;
|
|
|
|
if ($create_database and not contains($schema,$self->getdatabases(),0)) {
|
|
$self->_createdatabase($schema); #notimplemented error...
|
|
}
|
|
|
|
$dbh->{InactiveDestroy} = 1;
|
|
|
|
$dbh->{LongReadLen} = $LongReadLen;
|
|
$dbh->{LongTruncOk} = $LongTruncOk;
|
|
|
|
$self->db_do('ALTER SESSION SET NLS_LANGUAGE = \'' . $connNLS_LANGUAGE . '\'');
|
|
$self->db_do('ALTER SESSION SET NLS_TERRITORY = \'' . $connNLS_TERRITORY . '\'');
|
|
#$self->db_do('ALTER SESSION SET NLS_CHARACTERSET = \'' . $connNLS_CHARACTERSET . '\'');
|
|
$self->db_do('ALTER SESSION SET NLS_NUMERIC_CHARACTERS = \'.,\'');
|
|
$self->db_do('ALTER SESSION SET NLS_DATE_FORMAT = \'YYYY-MM-DD\'');
|
|
$self->db_do('ALTER SESSION SET NLS_TIMESTAMP_FORMAT = \'YYYY-MM-DD HH24:MI:SS\'');
|
|
|
|
if (length($isolation_level) > 0) {
|
|
$self->db_do('ALTER SESSION SET ISOLATION_LEVEL = ' . $isolation_level);
|
|
}
|
|
|
|
if ($enable_numeric_sorting) {
|
|
#http://stackoverflow.com/questions/6470819/sql-if-cannot-convert-to-number-set-as-null
|
|
eval {
|
|
$self->db_do("CREATE OR REPLACE FUNCTION try_to_number( p_str IN VARCHAR2 )\n" .
|
|
" RETURN NUMBER\n" .
|
|
"IS\n" .
|
|
" l_num NUMBER;\n" .
|
|
"BEGIN\n" .
|
|
" BEGIN\n" .
|
|
" l_num := to_number( p_str );\n" .
|
|
" EXCEPTION\n" .
|
|
" WHEN others THEN\n" .
|
|
" l_num := null;\n" .
|
|
" END;\n" .
|
|
" RETURN l_num;\n" .
|
|
"END;");
|
|
};
|
|
if ($@) {
|
|
dbwarn($self,'numeric sorting not supported',getlogger(__PACKAGE__));
|
|
}
|
|
} else {
|
|
dbdebug($self,'numeric sorting not enabled',getlogger(__PACKAGE__));
|
|
}
|
|
|
|
dbinfo($self,'connected',getlogger(__PACKAGE__));
|
|
|
|
}
|
|
|
|
sub vacuum {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
|
|
}
|
|
|
|
sub _db_disconnect {
|
|
|
|
my $self = shift;
|
|
|
|
$self->SUPER::_db_disconnect();
|
|
|
|
}
|
|
|
|
sub getfieldnames {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
return $self->db_get_col('SELECT column_name FROM all_tab_columns WHERE CONCAT(CONCAT(owner,\'.\'),table_name) = ?',$self->tableidentifier($tablename));
|
|
|
|
}
|
|
|
|
sub getprimarykeycols {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
return $self->db_get_col('SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE
|
|
CONCAT(CONCAT(cols.owner,\'.\'),cols.table_name) = ? AND
|
|
cons.constraint_type = \'P\' AND
|
|
cons.constraint_name = cols.constraint_name AND
|
|
cons.owner = cols.owner
|
|
ORDER BY cols.table_name, cols.position',$self->tableidentifier($tablename));
|
|
|
|
}
|
|
|
|
sub create_temptable {
|
|
|
|
my $self = shift;
|
|
my $select_stmt = shift;
|
|
my $indexes = shift;
|
|
|
|
my $index_tablename = $self->_gettemptablename();
|
|
my $temp_tablename = $self->tableidentifier($index_tablename);
|
|
|
|
#$self->db_do('CREATE GLOBAL TEMPORARY TABLE ' . $temp_tablename . ' ON COMMIT PRESERVE ROWS AS ' . $select_stmt);
|
|
$self->db_do('CREATE TABLE ' . $temp_tablename . ' AS ' . $select_stmt);
|
|
push(@{$self->{temp_tables}},$index_tablename);
|
|
|
|
temptablecreated($self,$index_tablename,getlogger(__PACKAGE__));
|
|
|
|
#$self->{temp_table_count} += 1;
|
|
|
|
if (defined $indexes and ref $indexes eq 'HASH' and scalar keys %$indexes > 0) {
|
|
foreach my $indexname (keys %$indexes) {
|
|
my $indexcols = $self->_extract_indexcols($indexes->{$indexname});
|
|
#if (not arrayeq($indexcols,$keycols,1)) {
|
|
#$statement .= ', INDEX ' . $indexname . ' (' . join(', ',@{$indexes->{$indexname}}) . ')';
|
|
$indexname = _chopidentifier(lc($index_tablename) . '_' . $indexname);
|
|
$self->db_do('CREATE INDEX ' . $indexname . ' ON ' . $temp_tablename . ' (' . join(', ',map { local $_ = $_; $_ = $self->columnidentifier($_); $_; } @$indexcols) . ')');
|
|
indexcreated($self,$index_tablename,$indexname,getlogger(__PACKAGE__));
|
|
#}
|
|
}
|
|
}
|
|
|
|
return $temp_tablename;
|
|
|
|
}
|
|
|
|
sub create_primarykey {
|
|
|
|
my $self = shift;
|
|
my ($tablename,$keycols,$fieldnames) = @_;
|
|
|
|
if (length($tablename) > 0 and defined $fieldnames and ref $fieldnames eq 'ARRAY') {
|
|
|
|
if (defined $keycols and ref $keycols eq 'ARRAY' and scalar @$keycols > 0 and setcontains($keycols,$fieldnames,1)) {
|
|
my $statement = 'ALTER TABLE ' . $self->tableidentifier($tablename) . ' ADD PRIMARY KEY (' . join(', ',map { local $_ = $_; $_ = $self->columnidentifier($_); $_; } @$keycols) . ')';
|
|
$self->db_do($statement);
|
|
primarykeycreated($self,$tablename,$keycols,getlogger(__PACKAGE__));
|
|
return 1;
|
|
}
|
|
|
|
}
|
|
|
|
return 0;
|
|
}
|
|
|
|
sub create_indexes {
|
|
|
|
my $self = shift;
|
|
my ($tablename,$indexes,$keycols) = @_;
|
|
|
|
my $index_count = 0;
|
|
if (length($tablename) > 0) {
|
|
|
|
if (defined $indexes and ref $indexes eq 'HASH' and scalar keys %$indexes > 0) {
|
|
foreach my $indexname (keys %$indexes) {
|
|
my $indexcols = $self->_extract_indexcols($indexes->{$indexname});
|
|
if (not arrayeq($indexcols,$keycols,1)) {
|
|
#$statement .= ', INDEX ' . $indexname . ' (' . join(', ',@{$indexes->{$indexname}}) . ')';
|
|
$indexname = _chopidentifier($indexname);
|
|
$self->db_do('CREATE INDEX ' . $indexname . ' ON ' . $self->tableidentifier($tablename) . ' (' . join(', ',map { local $_ = $_; $_ = $self->columnidentifier($_); $_; } @$indexcols) . ')');
|
|
indexcreated($self,$tablename,$indexname,getlogger(__PACKAGE__));
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
return $index_count;
|
|
}
|
|
|
|
sub create_texttable {
|
|
|
|
my $self = shift;
|
|
my ($tablename,$fieldnames,$keycols,$indexes,$truncate,$defer_indexes) = @_;
|
|
#my ($tableidentifier,$fieldnames,$keycols,$indexes,$truncate) = @_;
|
|
|
|
#my $tablename = $self->getsafetablename($tableidentifier);
|
|
|
|
if (length($tablename) > 0 and defined $fieldnames and ref $fieldnames eq 'ARRAY') {
|
|
|
|
my $created = 0;
|
|
if ($self->table_exists($tablename) == 0) {
|
|
my $statement = 'CREATE TABLE ' . $self->tableidentifier($tablename) . ' (';
|
|
$statement .= join(' VARCHAR2(' . $varcharsize . '), ',map { local $_ = $_; $_ = $self->columnidentifier($_); $_; } @$fieldnames) . ' VARCHAR2(' . $varcharsize . ')';
|
|
if (not $defer_indexes and defined $keycols and ref $keycols eq 'ARRAY' and scalar @$keycols > 0 and setcontains($keycols,$fieldnames,1)) {
|
|
#$statement .= ', CONSTRAINT ' . $tablename . '_pk PRIMARY KEY (' . join(', ',@$keycols) . ')';
|
|
$statement .= ', CONSTRAINT PRIMARY KEY (' . join(', ',map { local $_ = $_; $_ = $self->columnidentifier($_); $_; } @$keycols) . ')';
|
|
}
|
|
#if (defined $indexes and ref $indexes eq 'HASH' and scalar keys %$indexes > 0) {
|
|
# foreach my $indexname (keys %$indexes) {
|
|
# $statement .= ', INDEX ' . $indexname . ' (' . join(', ',@{$indexes->{$indexname}}) . ')';
|
|
# }
|
|
#}
|
|
$statement .= ')';
|
|
|
|
$self->db_do($statement);
|
|
texttablecreated($self,$tablename,getlogger(__PACKAGE__));
|
|
|
|
if (not $defer_indexes and defined $indexes and ref $indexes eq 'HASH' and scalar keys %$indexes > 0) {
|
|
foreach my $indexname (keys %$indexes) {
|
|
my $indexcols = $self->_extract_indexcols($indexes->{$indexname});
|
|
if (not arrayeq($indexcols,$keycols,1)) {
|
|
#$statement .= ', INDEX ' . $indexname . ' (' . join(', ',@{$indexes->{$indexname}}) . ')';
|
|
$indexname = _chopidentifier($indexname);
|
|
$self->db_do('CREATE INDEX ' . $indexname . ' ON ' . $self->tableidentifier($tablename) . ' (' . join(', ',map { local $_ = $_; $_ = $self->columnidentifier($_); $_; } @$indexcols) . ')');
|
|
indexcreated($self,$tablename,$indexname,getlogger(__PACKAGE__));
|
|
}
|
|
}
|
|
}
|
|
$created = 1;
|
|
} else {
|
|
my $fieldnamesfound = $self->getfieldnames($tablename);
|
|
if (not setcontains($fieldnames,$fieldnamesfound,1)) {
|
|
fieldnamesdiffer($self,$tablename,$fieldnames,$fieldnamesfound,getlogger(__PACKAGE__));
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
if (not $created and $truncate) {
|
|
$self->truncate_table($tablename);
|
|
}
|
|
return 1;
|
|
} else {
|
|
return 0;
|
|
}
|
|
|
|
}
|
|
|
|
sub multithreading_supported {
|
|
|
|
my $self = shift;
|
|
return 1;
|
|
|
|
}
|
|
|
|
sub rowblock_transactional {
|
|
|
|
my $self = shift;
|
|
return $rowblock_transactional;
|
|
|
|
}
|
|
|
|
sub truncate_table {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
|
|
$self->db_do('TRUNCATE ' . $self->tableidentifier($tablename));
|
|
tabletruncated($self,$tablename,getlogger(__PACKAGE__));
|
|
|
|
}
|
|
|
|
sub table_exists {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
|
|
return $self->db_get_value('SELECT COUNT(*) FROM all_tables WHERE CONCAT(CONCAT(owner,\'.\'),table_name) = ?',$self->tableidentifier($tablename));
|
|
|
|
}
|
|
|
|
sub drop_table {
|
|
|
|
my $self = shift;
|
|
my $tablename = shift;
|
|
|
|
if ($self->table_exists($tablename) > 0) {
|
|
$self->db_do('DROP TABLE ' . $self->tableidentifier($tablename) . ' PURGE'); #CASCADE CONSTRAINTS PURGE');
|
|
tabledropped($self,$tablename,getlogger(__PACKAGE__));
|
|
return 1;
|
|
}
|
|
return 0;
|
|
|
|
}
|
|
|
|
sub db_do_begin {
|
|
|
|
my $self = shift;
|
|
my $query = shift;
|
|
#my $tablename = shift;
|
|
|
|
$self->SUPER::db_do_begin($query,$rowblock_transactional,@_);
|
|
|
|
}
|
|
|
|
sub db_get_begin {
|
|
|
|
my $self = shift;
|
|
my $query = shift;
|
|
#my $tablename = shift;
|
|
|
|
$self->SUPER::db_get_begin($query,$rowblock_transactional,@_);
|
|
|
|
}
|
|
|
|
sub db_finish {
|
|
|
|
my $self = shift;
|
|
#my $unlock = shift;
|
|
my $rollback = shift;
|
|
|
|
$self->SUPER::db_finish($rowblock_transactional,$rollback);
|
|
|
|
}
|
|
|
|
1;
|