Server.pm

NAME

  Communiware::SQL::Server - generic class for sql-server dependent modules

SYNOPSIS

$dbh = new Communiware::SQL::Server($database,$user,$password,$charset)

DESCRIPTION

This module is ascendant for all server-dependent modules such as Communiware::SQL::Oracle and Communiware::SQL::Pg. This class specifies their interface - set of functions which should be implemented by these modules.

On other hand this module is descendat of DBI::db object.

METHODS

new

 new Communiware::SQL::Server($database, $user, $password, $charset)

Creates new instance of Server. This object has all the method defined below and all the methods of DBI::db. Due to this fact variable of Communiware::SQL::Server class would be denoted as $dbh below.

If 'charset' parameter is present, and equals to 'utf8' then session encoding sets to unicode utf8.

database_version

 $dbh->database_version

Returns database version.

Setting message language

set_lang

 $dbh->set_lang($lang)

Calls stored procedure set_lang, which should store specified language (which should have one of values specified in LOCALE field of LANGUAGES table) in the current session context.

Returns result of appropriate $dbh->do statement. In case of error sets $dbh->errstr

Handling texts

Following several methods handle ``long'' texts. These texts are stored in some server-depending storage (typically BLOB field) under same ids as items itself.

These methods work only with text storage and do not interact with other tables (i.e. item) in Communiware database.

text_item_set

 $dbh->text_item_set($id, $text)

Stores text $text for item $id. If there existed text with such id before, it is replaced by specified one.

text_item_get

 $dbh->text_item_get($id, [$len, [$offs]])

Returns previously saved text for $id. If $len is specified, no more than $len characters are returned. If $offs specified, then first returned character is character $offs in the text.

text_item_delete

 $dbh->text_item_delete($id)

Deletes text for id $id from the database

Handling text and item attributes together

item_gettext

 $dbh->item_gettext($id)

Return two element lists - text and lastchange field.

Later - in Communiware standard date format.

item_checktext

 $dbh->item_checktext($id, $date)

Does the same, as above but only if lastchange greater than specified date. Date should be specified in Communiware standard date format.

Accessing database metainformation

Following method returns information about table field in server-independent form.

get_table_info

 %info=$dbh->get_table_info($tablename)

Recieves table name. Returns undef if no such table exists. Otherwise returns hash where keys are names of the fields, and values are hash references, pointing to hash which describes given field

These hashes have following keys

ORDNUM
order of this field in the table, beginning with 1

NAME
field name

TYPE
field type as server returns it

LENGTH
Field length, where applicable

PRECISION
Field precision, where applicable

IS_DATE
true, if this field has type analogous to Oracle's DATE or Postgres' TIMESTAMP

IS_NUMBER
true, if it is analogous to SQL standard NUMERIC. This means that PRECISION should be set.

field_info

 @field_desc=$dbh->field_info($tableinfo,$name)

Recieves hash reference, as returned by get_table_info method and field name and returns server-independent field specification in the form, acceptable by alter_table and create_table methods.

get_index_info

 $dbh->get_index_info($tablename)

Returns info about indexes of $tablename (except primary key), or undef if the table does not exists. Info is hash, where keys are index names, and values - lists (unique, field1, field2,...). Unique - boolean value, shows if index is uniq.

get_constraint_info

 $dbh->get_constraint_info($tablename)

Returns info about check constraints for table $tablename. Info is hash where keys are constraint names and values are constraint definitions (text).

Changing constraint status

set_constraints_deferred, set_constraints_immediate

 $dbh->set_constraints_deferred, $dbh->set_constraints_immediate

Set checking of integrity constraints to immediate (upon each sql statement) or deferred (at the end of transaction).

Fixing DBD incompatibilities

#=head2 upcase_hash_keys

#This subroutine accepts one argument - result of $sth->fetchall_arrayref({}) #metrhod, and converts all fields names to uppercase.

#It shoulc be implemented only for SQL servers where there is no way to #force DBD return field names uppercase. On all other servers it should #be empty method for maximum performance.

#=cut

#sub upcase_hash_keys { # 1; #}

result_types

 $types = $dbh->result_types($sth);

Takes DBI statement handle after its executing and returns hash reference describbing names and Communiware (not database!) data types of data returned by $sth. Keys are column names and values are references to one- or two-element arrays. The only element in these arrays is Communiware data type, such as STRING or NUMBER.

date_columns

 @dc = $dbh->date_columns($sth);

Returns list of column names in $sth result where Communiware datatype is DATE. In fact this method analyses source text of statement and fetches all fdate(...) as name entries.

Creating data object

Communiware has its own set of data type names etc. For some SQL servers like Oracle, it also has its own consideration of managing object storage. Thus we are providing server-depenedent methods to create data objects (tables, indices, integrity constraints)

create_table

Syntax

 create_table ( table_name,-fields =>[ [ name,type,size,prec],[name,type,size] ...],
                -key => [name,name,name], -storage=name)
                
Creates table with fields listed as list of -fields parameter.
name is name of field, type is type of field in Communiware notation -
NUMBER, DATE, STRING or RICHTEXT, which is translated into most
appropriate native datatype by server specific module. prec is optional
decimal precision.

Value of -key parameter is a list of fields (which should match fieldnames in -fields parameter, which constitute primary key of the table.

-storage is storage class (not supported by some SQL servers and ignored by corresponding communiware modules).

List of classes and their corresponding values is defined when database is created.

If attribute ``-is-extattr-table'' is set to true, then trigger would be automatically created which updates LASTCHANGE field in the corresponding record in ITEM table. foreign key to ITEM(ITEM_ID) should be created by hand, and ITEM_ID should be explicitely specified as primary key.

alter_table

Syntax

  $dbh->alter_table($table,-add=>[$name,$type,$size,$prec])

Adds new field to existing table. Column specification is specified same way as in create_table procedure.

field_desc

 $sql=field_desc($name,$type[,$size[,$prec]]);

Recieves server-independent field specification, and returns server-specific SQL fragment to insert into CREATE TABLE or ALTER TABLE SQL statement

typemap

 $type = typemap($type)

Recieves Communiware type name and returns server-specific type name.

create_index

Syntax

  create_index (index_name, table_name, 
                -fields =>[field1, field2,...] or <field>, 
                -unique => unique, -storage => storage_class );

Creates auxilary index on existing table table_name See create_type for discussion of storage clases.

Pass array reference if you want to create index on multiple columns

foreign_key

Syntax 


  foreign_key (name, table, field, referenced_table, referenced_field,
    [-deferrable=>deferrable|..., -delete => cascade|...]);

Creates integrity constraint which links given field of table to another table.

field and reference_field can be array references

if deferrable is true, created constraint may be deferred. If SQL server does not support notion of non-deferable constraints, this arg would be ignored.

Query syntax checking

These methods allow to check server-depended syntax of any sql query. There are two methods, which do following tasks: check if this query is accessable for ``me'' and check if this query has ``my'' sql features and cannot be used for another servers.

check_import_query

 $dbh->check_import_query($query)

Checks if $query is 'good' sql text for used server. Query can contain '?' placeholders, accepted by $dbh->prepare. Does nothing on success or dies on fail. After fail $dbh->errstr contains error message.

check_export_query

 $dbh->check_export_query($query)

Checks if $query contains some features specific for used server. Does nothing if query is (more or less) standard or dies if query is server-specific. In this case $dbh->errstr contains error message.

dialect

 $dbh->dialect

Returns name of SQL dialect supported by this server as specified by ontology.dtd.

metaedit_call

 $dbh->metaedit_call

Returns expression for use for call to stored-procedures for fork with metalinks.

        $dbh->do(metaedit_call('ins_metalinkfast(?,?)'), {}, $metalink, $name);

Error handling

error_info

 ($errc, $errobj, $mess) = $dbh->error_info;

This method unifies error information in different DBs. Returns 3-element list - integer error code, object name related to this code and database native error message. $errc take one from constant values, listed in the following table. Meaning of $errobj depends of $errcode.

Constants must be used as $dbh->CDBE_UNIQ etc.

 Code           object            meaning
 ========================================================
 CDBE_OK        none              no error
 CDBE_OTHER     none              other errord
 CDBE_NOTNULL   field name        try to insert
                                  null into not null
                                  column
 CDBE_REFINT    constraint name   referential integrity
                                  violation
 CDBE_UNIQ      table name        try to insert duplicate
                                  value into unique
                                  column(s)
 CDBE_CHECK     constraint name   field 'check' constraint
 CDBE_RELTYPE   item id           try to violate relation
                                  type for link, for example
                                  make 1:1 link to more than
                                  one item

cache_queries

Syntax:

  $dbh->cache_queres(\%queries_hash)

Prepares set of select queres and stores handles inside object for subsequent usage by the selectrow_cached manpage and the execute_cached manpage.

cache_internal_queries

  $dbh->cache_internal_queries()

Prepares and caches set of 'internal' frequently used and required by core function queries.

cache_standard_queries

  $dbh->cache_standard_queries()

Prepares and caches set of 'standard' frequently used queries.

cache_query

Syntax:

  $dbh->cache_query('id','SQL-text')

Prepares select query and stores its handle inside object for subsequent usage by the selectrow_cached manpage and the execute_cached manpage.

Returns cached handle or undef if there was an error preparing query.

cache_handle

Syntax

  $dbh->cache_handle(id,sth)

Caches already prepared statement and returns it

get_cached

Syntax

   my $sth = get_cached('id');

Returns statement handle for cached statement. If we currentry not under persistent envinronment make attempt to find standart query with given id and cache it.

selectrow_cached

Syntax

        @list = $dbh->selectrow_cached(id,param,....);
  
Executes previously cached query and returns single row of results as list.
 
=cut

sub selectrow_cached {
my ($self, $id, @args) = @_;
        my $sth = $self->get_cached($id);
        Carp::confess("Unknown cached statement '$id'!") unless $sth;
        my @ov = ($self->{RaiseError}, $self->{PrintError});
        $self->{RaiseError} = $self->{PrintError} = 0;
        $sth->execute(@args) || Carp::croak "Cached statement '$id':" . $self->errstr;
        my @res = $sth->fetchrow_array();
        $sth->finish;
        ($self->{RaiseError}, $self->{PrintError}) = @ov;
        wantarray ? return (@res): return $res[0];
}

execute_cached

Syntax

        $sth = $dbh->execute_cached(id,param,....);
  
Executes previously cached query.
 
=cut

sub execute_cached {
my ($self, $id, @args) = @_;
        my $sth = $self->get_cached($id);
        Carp::confess("Unknown cached statement '$id'!") unless $sth;
        my @ov = ($self->{RaiseError}, $self->{PrintError});
        $self->{RaiseError} = $self->{PrintError} = 0;
        my $res = $sth->execute(@args) || Carp::croak "Cached statement '$id':" . $self->errstr;
        ($self->{RaiseError}, $self->{PrintError}) = @ov;
        return wantarray ? ($sth, $res): $sth;
}

clean_cache

Cleans all cached statement handles.

explain_plan

 $explan = $dbh->explain_plan($query, \%config);

Method returns string which describes explain plan for $query. Hash %config is usually %Communiware::config, it contains data needed for some sql-servers (Pg), such as dbname, user name etc.

referencing_tables

$reflist = $dbh->referencing_tables($table)

Returns ref to array of refs [table_name, column_name] table_name is a name of table referencing to primary key of specified table column_name is a name of column in this table

resize_table_field

        $status = $dbh->resize_table_field(
                'table'     => $tname,
                'field'     => $fname,
                'type'      => $ftype,
                'length'    => $flength,
                'precision' => $fprecision
        );

All args except 'type' and 'precision' are required.

Returns status of requested operation.

alter_column

        $dbh->alter_column($tname, $fname, $ftype, $fsize)

Method changes the type or size of a column of a table.

savepoint

        $dbh->savepoint('pointname')

Creates a savepoint. This will fail unless you are inside of a transaction. The only argument is the name of the savepoint. Note that PostgreSQL does allow multiple savepoints with the same name to exist.

rollback_to

        $dbh->rollback_to('pointname')

Rolls the database back to a named savepoint, discarding any work performed after that point. If more than one savepoint with that name exists, rolls back to the most recently created one.

release

        $dbh->release('pointname')

Releases (or removes) a named savepoint. If more than one savepoint with that name exists, it will only destroy the most recently created one. Note that all savepoints created after the one being released are also destroyed.

safe_prepare

        $sth = $dbh->safe_prepare('query')

Due to Pg have very strict checking of success of prepare inside transaction, we need to wrap prepare routine.

Syntax of this function similar to prepare.

safe_execute

        $dbh->safe_execute($sth, ...)

Due to Pg have very strict checking of success of execute inside transaction, we need to wrap execute routine.

First argument of this wrapper - prepared statement, all other the same as in execute.

16 октябрь 2007 13:45