view release on metacpan or  search on metacpan

dbd-illustra.pod  view on Meta::CPAN

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

Neither automatic row numbering nor row count limitations are supported.


=head3 Positioned updates and deletes

Illustra supports positioned updates or deletes in cursors that have been
explicitly created and opened C<FOR UPDATE>. For example:

  $dbh->do("DECLARE cur1 CURSOR FOR SELECT * FROM tab1 FOR UPDATE");
  $dbh->do("OPEN cur1");
  $sth = $dbh->prepare("FETCH NEXT FROM cur1");
  while ($sth->execute && $row = $sth->fetchrow_arrayref) {
      $dbh->do("UPDATE tab1 SET col2='zyx' WHERE CURRENT OF cur1");
  }

The statements must all occur within the same transaction.


=head2 Parameter Binding

Parameter binding is not supported by Illustra, but the driver emulates
the binding of C<?> style input parameters. The TYPE attribute of the
C<bind_param()> method is ignored - Illustra accepts quoted literals
for all types.


=head2 Stored Procedures

The closest match to stored procedures that Illustra supports is user
defined functions. These may be used just like system defined functions
in SELECT or RETURN statements:

  $sth = $dbh->prepare("RETURN foo('bar')");
  $sth->execute();
  @result = $sth->fetchrow_array();


=head2 Table Metadata

C<DBD::Illustra> supports the C<table_info()> method. However, since the information
comes from Illustra's "tables" table, C<table_info()> will only return useful
information if "tables" is readable. By default, only the DBA has access to
"tables".

The "columns" table contains detailed information about all columns of
all the table in the database, one row per table. However, the same access
restrictions described above for the "tables" table will probably apply.

The "tables" and "columns" tables contain information about indexes as well
as normal tables. Use C<tables.table_kind='i'> for index tables (and C<'t'> 
for normal tables).

The C<tables.table_unique> field holds an array of column numbers. Each
unique constraint, including the primary key, is held as a -1 terminated
list of column numbers. The primary key is always the first list, even
if it is not present.

  (no constraints)                   =>  '[]'
  primary key(c1,c2)                 =>  '[1,2,-1]'
  primary key(c1,c2),unique(c1,c3)   =>  '[1,2,-1,1,3,-1]'
  unique(c1,c3)                      =>  '[-1,1,3,-1]'

These arrays are returned as strings although you can retrieve
individual elements instead using SQL functions.