view release on metacpan or  search on metacpan

dbd-illustra.pod  view on Meta::CPAN

       09:40:00   12:40:00+03:00


=head3 LONG/BLOB Data Handling

Illustra supports the following large object types:

  LARGE_OBJECT    - Binary large object
  LARGE_TEXT      - Large object masquerading as normal text
  EXTERNAL_FILE   - Locator for external large binary file

The maximum size of large objects is not documented by Illustra
but is probably 4GB.
None of the types are passed to and from the database as pairs of hex digits.

The I<LongReadLen> and I<LongTruncOk> attributes are untested as yet.

LARGE_TEXT fields may be treated just like any other fields, but LARGE_OBJECT
fields and EXTERNAL_FILE fields must be updated with Illustra's C<FileToLO()>
SQL function. LARGE_OBJECT and EXTERNAL_FILE fields may be read using Illustra's
C<LOToFile()> SQL function, or their contents may be read using the non-standard
C<read_large_object> method after the query has been finished:

  my $sth=$dbh->prepare('SELECT lofield FROM tab1 where key=?');
  $sth->execute($key);
  my($lohandle)=$sth->fetchrow_array;
  $sth->finish;

  my $offset=0;
  my $locontent;
  while(defined(my $frag=$dbh->func($lohandle,$offset,4096,'read_large_object'))){
    length $frag or last;
    $locontent.=$frag;
  }


=head3 Other Data Handling issues

The C<DBD::Illustra> driver does not currently support the C<type_info()> method. This
is under development, but Illustra doesn't generally provide enough information
to make this particularly useful.

Illustra automatically converts dates to strings, strings to dates, and
strings to numbers, but numbers must be explicitly converted to strings:

  INSERT INTO foo (num_field, str_field) VALUES ('42', 42::text)


=head2 Transactions, Isolation and Locking

Illustra and C<DBD::Illustra> support transactions.
The default transaction isolation level is Serializable.

Illustra supports all four standard isolation levels: Serializable,
Repeatable Read, Read Commited, and Read Uncommited.  The level be changed
per-transaction by executing a C<SET TRANSACTION ISOLATION LEVEL x>
statement where I<x> is the name of the isolation level required.

The default locking behavior is for readers to block writers.

Rows returned by a SELECT statement can be locked to prevent them from
being changed
by another transaction, by including "LOCK=EXCLUSIVE" or "LOCK=UPDATE" in the
optimizer hints for a given table:

  SELECT * FROM xyz USING(LOCK=UPDATE) WHERE xid = 'abc'

Exclusive locks provide less concurrency, but update locks must be
upgraded to exclusive when updates are required. This can cause
deadlock if another transaction has acquired a read lock in the
meantime.

There doesn't seem to be any way to explicitly lock a table other than
by issuing a dummy SELECT statement with a LOCK=EXCLUSIVE hint as above.


=head2 SQL Dialect

=head3 Case Sensitivity of LIKE Operator

The Illustra LIKE operator is case sensitive.


=head3 Table Join Syntax

Illustra does not support outer joins, but normal, inner
joins are supported with the standard syntax.


=head3 Table and Column Names

The maximum size of table and column names appears to be 212 characters.
The first character must be a letter, but the rest can be any
combination of letters, numerals and underscores (_).

However, if an Illustra identifier is enclosed by double quotation
marks (C<">), it can contain any combination of characters, including
spaces. Double quotes in identifier names must be escaped with another
double quote, e.g, C<"double""quote">.

Identifiers are stored as entered. All identifiers are case sensitive.
National character set characters can be used if enclosed in double
quotation marks.


=head3 Row ID

The Illustra "row ID" pseudocolumn is called I<oid>.  Illustra I<oid>'s look
like "2d52.2001".  I<Oid>'s can be treated as a string and used to rapidly
(re)select rows.


=head3 Automatic Key or Sequence Generation

Illustra does not support automatic key generation such as "auto
increment" or "system generated" keys.

It also doesn't offer sequence generators.


=head3 Automatic Row Numbering and Row Count Limiting