Maxscape::Database::Base Package

Member Functions

1. InitAdminCursor
2. InitDatabaseHandler
3. initDBConnection
4. InstallDriver
5. listConfiguredServer
6. registerDatabaseProduct
7. registerDatabases

Synopsis

use Maxscape::Database::Base;

&Maxscape::Database::Base::InstallDriver ();
&Maxscape::Database::Base::registerDatabaseProduct($DataBase::Product);
&Maxscape::Database::Base::registerDatabases($ServerConfiguration->Databases);

Description

Basis maXscape database initialisation functions. Since the maxscape server calls them by default, they may not be called within application functions included via the maXscape application plugin.

The package is usefull to write programms, that have to connect to the Maxscape server databases.

InstallDriver

Synopsis

&Maxscape::Database::Base::InstallDriver ( %P );

Description

Installs the configured database driver. As a side effect sets the global variable $Database::DatabaseDriver.

Examples

my $Driver;
unless ( $Driver = &Maxscape::Database::Base::InstallDriver (
                     DatabaseType   => 'oracle',
                     [ Attributes   => \%Attributes, ]
                     [ CompatMode   =>  1, ]
                     [ Warn         => 0, ]
                     [ ChopBlanks   => 1 ]) )
{
  print STDERR ("Can't install database driver.");
}

registerDatabaseProduct

Synopsis

&Maxscape::Database::Base::registerDatabaseProduct($DatabaseProduct);

Description

Registers the database product type to the system and sets the necessary configuration variables.

The function is called by the runtime system and must not be called within applications included via the maXscape application plugin.

Examples

my $DataBaseProduct = 'mysql'; # e.g. msql, mysql, oracle
&Maxscape::Database::Base::registerDatabaseProduct($DatabaseProduct);

initDBConnection

Synopsis

Maxscape::Database::Base::initDBConnection ( $WebServerName );

Description

Initializes the connections to the database.

Examples

InitAdminCursor

Synopsis

&Maxscape::Database::Base::InitAdminCursor();

Description

Registers an additional database object with an own database connection to the process.

Examples

$WebServer->{ConfigAdminCursor} &&
                 &Maxscape::Database::Base::InitAdminCursor();

InitDatabaseHandler

Synopsis

&Maxscape::Database::Base::InitDatabaseHandler ( $WebServerName );

Description

Installs the database driver, creates a 'DataBase' object and connects this object to the database named '$WebServerName'.

Examples

$WebServer->{ConfigAdminCursor} &&
                 &Maxscape::Database::Base::InitAdminCursor();

registerDatabases

Synopsis

my %DataBases = ( ... );
&Maxscape::Database::Base::registerDatabases(\%Databases);

Description

Registers the configurated databases to the system. Takes a hash reference defining the server's databases.

listConfiguredServer

Synopsis

my @ConfiguredServerList = &Maxscape::Database::Base::listConfiguredServer();

Description

Lists the configurated application server.

Maxscape::Database Package

Member Functions

1. changeRecord
2. changeRecords
3. checkTextColumnSize
4. checkTextFieldLength
5. close
6. ColumnType
7. connect
8. connectedDatabaseName
9. copyNodeRecord
10. copyRecord
11. CountRecords
12. DatabaseName
13. DB_QueryColumnNames
14. DB_QueryColumnNamesTypesSizes
15. DB_QueryColumnSizes
16. DB_QueryColumnTypes
17. DB_TableColumnNamesTypesSizes
18. deleteRecord
19. deleteRecords
20. escapeTableName
21. escapeTableName
22. fetchColumns
23. fetchColumnsAsHash
24. fetchListOfHashes
25. fetchRow
26. fetchRowArray
27. fetchRowAsHash
28. fetchRowToHash
29. finish
30. getRecordHashRef
31. insertRecord
32. insertRecordFromFile
33. insertRecordFromString
34. lockTables
35. MaxRecord
36. MaxTextFieldlength
37. new
38. newCursor
39. preventSQLInjection
40. printError
41. QueryColumnIndex
42. QueryColumnNamesTypesSizes
43. quote
44. RowToHash
45. selectDataBase
46. showDatabases
47. Sql
48. TableColumnIndex
49. TableColumnNamesTypesSizes
50. TableQueryColumnValuePair
51. TextFieldlength
52. unlockTables
53. WebServerName

Synopsis

use Maxscape::Database;

Description

The Maxscape::Database package supplies the basic functionality to deal with the server's database, especially the Content Database. It provides a portable API, that masks database product specifica. To use the package the software must previously be set up with the functions of the Maxscape::Database::Base package.

Examples

To access the data in the database, you can use the methods provided by the
run time objects of this class. For example, if you want to query a self
defined database table MY_TABLE', you can use the following code (not tested): 

 sub my_prefix_myFunction {
   my $self = @_;

   my $Text;
   my $TableName = 'myTable';
   my $where     = "myColumn='blabla'";
   my $Query     = "SELECT * from $TableName where $where";

   # create a new cursor object connected to the current database.
   my $Cursor = Maxscape::Database->newCursor ();

   if (!$Cursor->Sql ($Query)) {
     return $Cursor->printError;
   }

   my $Counter = 0;
   my @Row;
   while (1)
   {
     # fetch the database rows
     @Row = $Cursor->fetchRow;
     last if $#Row == -1 ;
     $Counter++;

     # e.g. return the rows of the first table column.
     $Text .= "$Counter: 1. Spalte" . $Row[0] . $::LF;
   }
   return $Text;
 }

new

Synopsis

my $Cursor = Maxscape::Database->new ( WebServerName => $ApplicationServerName);

Description

Makes a new Maxscape::Database object connected to the application server's database. Assumes that the system is previously set up by the functions of the Maxscape::Database::Base package.
Within application functions, allways use the Maxscape::Database->newCursor() function instead.

newCursor

Synopsis

my $Cursor = Maxscape::Database->newCursor( [ $origCursor ]);

Description

Makes a new Maxscape::Database object from an existing. Assumes that the system is previously set up by the functions of the Maxscape::Database::Base class.

printError

Synopsis

my $Cursor = Maxscape::Database->printError($ErrorMessage);

Description

Prints error messages conserning the database.

finish

Synopsis

my $DatabaseName = $Cursor->finish;

Description

Closes a cursor to an Application Database.

close

Synopsis

my $DatabaseName = $Cursor->close;

Description

Closes a connection to the database.

DatabaseName

Synopsis

my $DatabaseName = $Cursor->DatabaseName();

Description

Returns the name of the connected application server's database.

WebServerName

Synopsis

my $WebServerName = $Cursor->WebServerName();

Description

Returns the name of the connected application server.

connect

Synopsis

unless (my $Database->connect ()) {
  print $self->printError("Error");
}

Description

Connects the database object to the related database. Usualy it is not necessary to call this function within applications pluged in via tha application plugin mechanism, cause all database connections are set up by the system.

selectDataBase

Synopsis

my $OK = $Cursor->selectDataBase ($DatabaseName);

Description

Selects the appliction server's database given by the $DatabaseName parameter.

Examples

my $DatabaseName = 'Test';
unless ($Cursor->selectDataBase( $DatabaseName )) {
  $self->printError ("Error in fct. selectDataBase $DatabaseName.");
  return undef;
}

connectedDatabaseName

Synopsis

$DatabaseName = $Cursor->connectedDatabaseName();

Description

Returns the name of the connected database.

Examples

lockTables

Synopsis

$Cursor->lockTables('FORUM', 'WRITE');

Description

Locks a table.

unlockTables

Synopsis

$Cursor->unlockTables();

Description

Unlocks a table.

showDatabases

Synopsis

return $DBConnection->showDatabases();

Description

Shows the configured databases.

TableColumnNamesTypesSizes

Synopsis

my ($DB_ColumnNames, $DB_ColumnTypes, $DB_ColumnSizes,
     $internalDB_ColumnTypes, $internalDB_ColumnSizes)
                       = $self->{Cursor}->TableColumnNamesTypesSizes();

Description

Returns arrays containing the column names, internal and DB database types and sizes of the columns of a Table

DB_TableColumnNamesTypesSizes

Synopsis

$Cursor->DB_TableColumnNamesTypesSizes('MY_TABLE');

Description

Returns references to three arrays, containing the names, types and sizes of the taken database table (e.g. 'MY_TABLE').

Examples

my ($ColumnNames, $ColumnTypes, $ColumnSizes) =
        $self->{Cursor}->DB_TableColumnNamesTypesSizes('MY_TABLE');

foreach $ColumnName (@{$ColumnNames}) {
  ...
}

TableColumnIndex

Synopsis

my $TableColumnIndex = $self->{Cursor}->TableColumnIndex
                                          ($ColumnNames, $ColumnName);

Description

Returns the index of '$ColumnName' given by the array reference '$ColumnNames', begining from 0. If nothing is found returns 'undef'.

escapeTableName

Synopsis

$Tables[$i] = $self->escapeTableName ($Tables[$i]);

Description

Returns the plain table name.

escapeTableName

Synopsis

$Tables[$i] = $self->escapeTableName ($Tables[$i]);

Description

Returns the plain table name.

TableQueryColumnValuePair

Synopsis

$ColumnValuePair = $DBConnection->TableQueryColumnValuePair (
                     $ColumnName, $ColumnValue, $ColumnType,
                     $ConcatenateSeparator
);

Description

Sql

Synopsis

$Cursor->Sql($Query, [ $DatabaseName ]);

Description

Sends a SQL query to an appliction server's database. Selected data are then processed by the fetch functions.

Examples

unless ($Cursor->Sql("select foo from table where COLUMN='bar'")) {
  return "Error";
}
my @Row = $Cursor->fetchRow ();
return $Row[0];

fetchRow

Synopsis

my @Row = $Cursor->fetchRow ();

Description

Fetches the data from a previously initiated query. Returns an array, that contains the database record.

Examples

my @Row;
while (1) {
  @Row = $Cursor->fetchRow ();
  last if ($#Row < 0);
  if ($Row[1] eq 'bar') {
    print "Foo: $Row[3]";
  }
}

fetchRowArray

Synopsis

my $ArrayReference = $Cursor->fetchRowArray ();

Description

Fetches the data from a previously initiated query. Returns a reference to an array, that contains the database record.

fetchRowAsHash

Synopsis

my $RowHashReference = $Cursor->fetchRowAsHash ();

Description

Fetches the data from a previously issued query. Returns a reference to a hash, that contains the database record.

Examples

my $RowHashReference = $Cursor->fetchRowAsHash ();

my $FooValue = ${$RowHashReference}{FOO_COLUMN};

fetchRowToHash

Synopsis

$Cursor->fetchRowToHash($HashReference);

Description

Fetches the data from a previously initiated query and adds it to the taken '$HashReference' hash reference parameter.

RowToHash

Synopsis

$Cursor->RowToHash($ListReference);

Description

Stores the data from a previously query and returns a hash reference.

fetchColumns

Synopsis

my $Row = { $Cursor->fetchColumns ("select COLUMNS from TABLE") };

Description

Fetches the specified COLUMNS from the database table. Returns a hash of arrays, whereas the hash key is the column name and the array contains the values of the COLUMNS.

fetchListOfHashes

Synopsis

my $Row = $Cursor->fetchListOfHashes ("select COLUMNS from TABLE");

Description

Fetches the specified records from the database table. Returns a list of hashs, whereas the hash key is the column name and the array contains the list of records as given by the SQL query.

fetchColumnsAsHash

Synopsis

my $PropertyHash = $self->{Cursor}->fetchColumnsAsHash ( %P );

Description

Returns a reference to a hash, where the hash key is the value of the 'HashKeyColumnName' column and corresponding hash value is the value of the 'HashValueColumnName' column.

Examples

my $PropertyHash = $self->{Cursor}->fetchColumnsAsHash (
               TableName           => 'WEB_SERVER',
               HashKeyColumnName   => 'PROPERTY_NAME',
               HashValueColumnName => 'PROPERTY_VALUE',
               where               => "PROPERTY_KEY = '$WebServerName'",
               order               => 'PROPERTY_NAME'
);
my $PropertyValue = ${$PropertyHash}{'myProperty'};

QueryColumnNamesTypesSizes

Synopsis

my ($DB_ColumnNames, $DB_ColumnTypes, $DB_ColumnSizes,
     $internalDB_ColumnTypes, $internalDB_ColumnSizes)
                     = $Cursor->QueryColumnNamesTypesSizes();

Description

Returns arrays containing the column names, internal and DB database types and sizes of the columns from the last SQL database query.

DB_QueryColumnNamesTypesSizes

Synopsis

my @DB_QueryColumnNames = @{$self->DB_QueryColumnNamesTypesSizes ()};

Description

Returns an array containing the database names, types and sizes of the columns from the last SQL database query.

DB_QueryColumnNames

Synopsis

my @DB_QueryColumnNames = @{$self->DB_QueryColumnNames ()};

Description

Returns an array containing the names of the columns from the last SQL database query.

DB_QueryColumnTypes

Synopsis

my @DB_QueryColumnTypes = @{$self->DB_QueryColumnTypes ()};

Description

Returns an array containing the types of the columns from the last SQL database query.

ColumnType

Synopsis

my $nternalColumnType = $self->ColumnType($ColumnType);

Description

Return the internal representation of a database column type.

DB_QueryColumnSizes

Synopsis

my @DB_QueryColumnSizes = @{$self->DB_QueryColumnSizes ()};

Description

Returns an array containing the sizes of the columns from the last SQL database query.

QueryColumnIndex

Synopsis

my $QueryColumnIndex = $self->{Cursor}->QueryColumnIndex ($ColumnName);

Description

Returns the index of '$ColumnName' from the last SQL database query. If nothing is found returns 'undef'.

MaxRecord

Synopsis

my $MaxRecord = $Cursor->MaxRecord (%P);

Description

Counts the records of a table.

Examples

my $MaxRecord = $Cursor->MaxRecord (TableName   => $TableName,
                                    CountColumn => $CountColumn,
                                    where       => $where,
                                    GroupBy     => $GroupBy,);

CountRecords

Synopsis

my $CountRecords = $Cursor->CountRecords ( %P );

Description

Returns min, max and count of the CountRecords column.

Examples

my ($MinRecord, $MaxRecord, $RecordCount) = $Cursor->CountRecords (
      TableName   => $TableName,
      CountColumn => $CountColumn,
      where       => $where,
      GroupBy     => $GroupBy,);

TextFieldlength

Synopsis

my $TextFieldlength = $Cursor->TextFieldlength();

Description

Returns the maximal number of bytes, a given database product may allocate for a given database text field. (This does not equal to the number of bytes).

MaxTextFieldlength

Synopsis

my $MaxTextFieldlength = $Cursor->MaxTextFieldlength();

Description

Returns the maximal number of bytes, that can be stored in a database text field (default=4000 bytes for compatibility with oracle 8).

checkTextFieldLength

Synopsis

$DBConnection->>checkTextFieldLength($Text);

Description

Check the length of a text to fit the purpose.

Examples

if ($self->checkTextFieldLength($Text) > -1) {
  # do something usefull...
} else {
  return $self->printError("Error in function ...:");
}

checkTextColumnSize

Synopsis

$Cursor->checkTextColumnSize($ColumnName, $ColumnType,
                             $ColumnSize, $ColumnValue);

Description

Returns -1, if the size exceeds the maximal size of the column, else returns the size of the variable '$ColumnValues' in bytes.
The variable '$ColumnType' may be in [ 'char', 'text' ]. '$ColumnSize' is the size in bytes.

Examples

if ($self->checkTextColumnSize($ColumnName, $ColumnType,
                               $ColumnSize, $ColumnValue) > -1) {
  # do something usefull...
} else {
  return $self->printError("Error in function ...:");
}

insertRecord

Synopsis

my $Message = $Cursor->insertRecord(TableName    => 'MY_TABLE',
                                    ColumnValues => $Page->{CGI});

Description

Inserts the values of the taken hash reference 'ColumnValues' to the table 'MY_TABLE'.

Examples

my $Cursor = Maxscape::Database->newCursor();

my $Message = $Cursor->insertRecord('TableName'    => 'MY_TABLE',
                                    'ColumnValues' => $self->{CGI});
if ($Message ne 'OK') {
  return "Error: $Message$::BR";
}
my $Message = $Cursor->insertRecord(
                TableName       => 'USER_PROFILE',
                ColumnValues    => \%{
                 { USER_ID         => $self->{USER_ID},
                   PROPERTY_NAME   => $CGI->{'USER_PROFILE.PropertyName'},
                   PROPERTY_VALUE  => $CGI->{'USER_PROFILE.PropertyValue'}
                 },
          });

insertRecordFromString

Synopsis

$Text .= $self->{Cursor}->insertRecordFromString ( %P );

Description

Inserts the values of the taken string 'Columns' to the table 'TableName'. 'MY_TABLE'.

Examples

my $TableName = 'MY_TABLE';
$Text .= $self->{Cursor}->insertRecordFromString(
                           'TableName'       => $TableName,
                           'Columns'         => $Columns,
                           'ColumnsToInsert' => $ColumnsToInsert
         );

insertRecordFromFile

Synopsis

return $self->{'Cursor'}->insertRecordFromFile (
         'TableName' => 'MY_TABLE',
         'File'      => "/maxscape/server/'server'/tables/$File.sql",
         'FieldsToChange' => { '0' => $NODE_NUMBER });
       );

Description

Inserts the 'SQL insert statements' contained in the file 'File' into the table 'MY_TABLE'.

getRecordHashRef

Synopsis

my $RecordAsHash = $self->{Cursor}->getRecordHashRef(
                                      $QueryColumns, $TableName, $where
                   );

Description

Get exactly one record as a hash reference from the database.

Examples

my $QueryColumns = '*';
my $TableName    = 'USER_PROFILE';
my $where        = "USER_ID='Foo' and PROPERTY_NAME='bar'";

my $RecordAsHash = $self->{Cursor}->getRecordHashRef(
                         $QueryColumns, $TableName, $where);

my $Text = ${$RecordAsHash}{PROPERTY_VALUE};

changeRecord

Synopsis

my $ErrorText = $Cursor->changeRecord (
                   TableName    => $TableName,
                   where        => "(NODE_NUMBER = $PageNumber)",
                   ColumnValues => { MODIFICATION_DATE => $Time });

Description

Changes the record(s) given by the functions parameters.

Examples

my $ErrorText = $Cursor->changeRecord (
                     TableName     => $TableName,
                     ColumnNames   => [ $ColumnName ],
                     ColumnTypes   => [ $Field{Type} ],
                     ColumnSizes   => [ $Field{Size} ],
                     where         => $self->{FieldKey},
                     ColumnValues  => { $ColumnName => "$FieldValueFromCGI" },
          );

changeRecords

Synopsis

$Cursor->changeRecords ( %P );

Description

So far, only used in the 'Maxscape::WebServer::UserAdmin' package.

copyRecord

Synopsis

$Message = $self->{Cursor}->copyRecord(
     'TableName'           => $TableName,
     'FieldsToChangeByName' => { 'NODE_NUMBER' => $NewPageNumber },
     'ColumnValues'         => $CGI,
     'where'                => $where);
if ($Message) {
  $ErrorText .= "$self->{ClassName}-err-2: copyRecord: $Message$::BR";
}

Description

copyNodeRecord

Synopsis

$Message = $self->{Cursor}->copyNodeRecord (
     'TableName'           => $TableName,
     'FieldsToChangeByName' => { 'NODE_NUMBER' => $NewPageNumber },
     'ColumnValues'         => $CGI,
     'where'                => $where);
if ($Message) {
  $ErrorText .= "$self->{ClassName}-err-2: copyNodeRecord: $Message$::BR";
}

Description

deleteRecord

Synopsis

my $ErrorText = $Cursor->deleteRecord('MY_TABLE', "COLUMN='foo'")

Description

Deletes the records given by the where clause (e.g. "COLUMN='foo'") from the table (e.g. 'MY_TABLE').

Examples

my $ErrorText = $Cursor->deleteRecord('ELEMENTS using NODE, ELEMENTS',
                                      "NODE.NODE_CATEGORY='/deleteMe");

deleteRecords

Synopsis

my $ErrorText = $Cursor->deleteRecords('USERS', $whereClauses);

Description

Deletes records of a table, selected by the where clause.

quote

Synopsis

my $quotedText = $Cursor->quote($Text);

Description

Calls DBI's quote function.

preventSQLInjection

Synopsis

my $Text = $Cursor->preventSQLInjection ($Text);

Description

Removes dangerous SQL injection characters.