Forum OpenACS Development: Overview of InterBase's limitations

As suggested in another thread, here are some of InterBase's limitations that are related to ACS development:
  • max. key/index width = 250 bytes

    I think that this limit won't go away any time soon, it seems that it is too deeply buried in server code. Even worse, if you use some other encoding besides LATIN1, this gets further reduced to 83 chars, since each char spans 3 bytes.

  • max. row width = 64K

    ... or so, the last time I looked. This seems reasonable.

  • max. varchar = 32K

    ... or 10900 chars for multi-byte encodings. This should be good enough for Web publishing, but note that InterBase's VARCHARs implementation is not particularly efficient, so you most probably would want to escape to BLOBs anyway.

  • procedural language

    User written SQL functions written in InterBase's PL are *not* implemented. OTOH, stored procedures can return multiple rows. Next, each stored procedure is compiled into BLR (Binary Language Representation) at creation time, meaning that both syntax and dependency checks have to be performed at that time.

Collapse
Posted by Don Baccus on
Can you give us some more specifics in regard to InterBase's PL?  Howwell will PL/SQL functions map to this?  Are we going to have troublehere?
Collapse
Posted by Sebastian Skracic on
  • No PL functions

    IB doesn't support user-defined functions written in PL, only stored procedures. Its PL is very weak compared to Oracle or PG. Dynamic SQL (i.e. SQL not known at compile time) is not supported, so you're pretty much stuck with zillion of IF's and branching all over the code if you want to write anything more advanced than simple selects and looping.

  • No CURSORs in procedures

    CURSORs are not available in PL. You have simple looping constructs with FOR SELECT ..., which loops over each row in result set, and that's it.

  • SELECT FROM procedure

    On a positive side, stored procedures can return multiple rows *and* columns. Very interesting (and highly IB-specific) feature: stored procedure share the same namespace with tables and views, so it is quite common to

    CREATE PROCEDURE stored_proc (
      param1        INTEGER,
      param2        VARCHAR(100)
    ) RETURNS (
      output_field1 INTEGER,
      output_field2 NUMERIC(15,2),
      output_field3 TIMESTAMP
    )
    AS
    BEGIN
      ...
    END ;
    
    SELECT output_field1, output_field3
      FROM stored_proc(10, 'test')
      WHERE output_field2 > 14500.75 ;
    
    output_field1   |  output_field3
    ----------------+---------------------
              123   | 2000-10-26 17:48:00
               17   | 2000-01-01 09:12:01
       7832465123   | 2000-09-09 09:09:09
    

    So, because stored procedure's output behaves like normal result set, you can perform any operations with it as you would with 'normal' result set, for example you can apply GROUP BY to it, and you can JOIN it to another table/view/stored procedure as well!

  • Porting Oracle PL/Sql in ACS 4.0

    Packages are not implemented. Since we have no true IB functions, ACS objects' constructors will have to use stored procedures that return single row/column.

    SELECT acs_attribute_create('blah', 'trah', 'gnah', NULL, ...)
       FROM rdb$database;
    
    (rdb$database is table that is guaranteed to contain only one row)

    Next, default values cannot be specified and there is no such thing as Oracle's nifty '=>' syntax.

    For destructors, which generally don't return anything, we should use stored procedures with no result set. Those must be invoked with

      EXECUTE PROCEDURE acs_attribute_destroy(...);
    
    Permission methods will be slightly complicated to invoke, in absence of true PL functions in IB. However, InterBase supports scalar subselects in target (SELECT list), so this is no-brainer:

    Oracle:

        select forum_id, short_name, moderated_p,
               acs_permission.permission_p(forum_id, :user_id,
                                           'bboard_write_forum') as write_p
          from acs_objects o, bboard_forums f
          where object_id = forum_id
            and context_id = :package_id
            and acs_permission.permission_p(object_id,:user_id,'bboard_read_forum')
    = 't'
        order by short_name
      

    InterBase:

    CREATE PROCEDURE acs_permission (
      object_id     INTEGER,
      party_id      INTEGER,
      privilege     VARCHAR(100)
    ) RETURNS (
      permission_p  CHAR
    )
    AS
    BEGIN
        ...
    END ;
    
        select forum_id, short_name, moderated_p,
               (SELECT permission_p
               FROM acs_permission(forum_id, ?, 'bboard_write_forum') as write_p
          from acs_objects o, bboard_forums f
          where object_id = forum_id
            and context_id = ?
            and
            (SELECT permission_p
             FROM acs_permission(object_id, ?, 'bboard_read_forum')) = 't'
        order by short_name
      
    Note that bind variables are replaced with positional parameter markers (?'s), which should be appropriately filled before actual SQL execution. A simple Tcl layer could take care of this.
Collapse
Posted by Don Baccus on
Thanks a lot for the detailed explanation, Sebastian.  It doesn't look  terribly difficult.  Being able to return result sets is pretty cool,  even if having that as the only thing you can return is a bit obnoxious :)
Collapse
Posted by Sebastian Skracic on
  • ORDER BY expression not supported

    In ORDER BY clause, you can only specify column names which appear in SELECT list or reference them by their position in SELECT list as in

    SELECT user_id, count(*) FROM ratings GROUP BY user_id ORDER BY 2
    

  • GROUP BY expression not supported

    This time, you can't even do "GROUP BY 2" as in ORDER BY clause. So what if you want to GROUP BY pretty_date that is calculated on-the-fly? You must resort to either defining a view (or stored procedure :-) that will calculate this field and GROUP BY that fieldname, or you can extend the table by creating COMPUTED BY field (InterBase SQL extension):

    ALTER TABLE bboard ADD pretty_date COMPUTED BY (
       EXTRACT (YEAR FROM posting_time) || ' ' ||
          EXTRACT (MONTH FROM postign_time) )
    
    and then GROUP BY pretty_date.
Collapse
Posted by Sebastian Skracic on
  • Adding referential constraints only possible in single-user mode

    Yes, unfortunately, it's true. You cannot

    ALTER TABLE my_table ADD/DROP CONSTRAINT ... REFERENCES other_table
    
    while there's someone else accessing your database. You'll receive friendly "Statement failed, SQLCODE = -607 unsuccessful metadata update -object OTHER_TABLE is in use". This is still true for 6.0.
Collapse
Posted by Don Baccus on
The alter table issue is annoying, but not fatal, since most folks will do site maintenance in single-user mode anyway (at least, I do!).

The ORDER BY and GROUP BY issues are more serious in regard to the ACS, as you can't do things like "ORDER BY upper(foo)" directly.  You can do "SELECT foo, upper(foo) as up_foo ... ORDER BY up_foo", though,  so it's not that big a deal perhaps????

BTW, in IB's defense their implementation of ORDER BY and GROUP BY is standard SQL92 - PG and Oracle have extended the construct beyond the standard (fine by me!).

Collapse
Posted by Sebastian Skracic on
  • Missing IFNULL(), NULLIF(), COALESCE, CASE and almost all standard SQL92 functions

    My biggest complaint (besides too short keys and inability to GROUP BY column_alias) is absence of almost all SQL functions. In "core" InterBase there is no string and math functions at all (all that is left are operators LIKE, CONTAINING, STARTING WITH, || and math arithmetic operators).

    The standard solution to this problem is library of user defined functions (UDF) written in C (on Whindows you can also use Pascal). This is very similar to PostgreSQL's user-defined functions but, unfortunately, overloading is not supported.

    That would be end of story if UDF could deal with NULL values. But they can't. Even worse, if you pass NULL as an argument to UDF, the result is unpredictable because UDFs simply ignore null indicator and grab argument value, which is just some random garbage. In any case UDF cannot return NULL. That leaves you pretty much in the dark if you need IFNULL(), or its generalized friend COALESCE().

  • No recursion alla CONNECT BY

    But recursive stored procedures are supported, so it's not too hard to write the procedure that calls itself and traverse all nodes of a tree.

Collapse
Posted by Sebastian Skracic on
In InterBase, you can't create a VIEW containing UNION. Actually this is not 100% true, because engine supports such views. The problem is bug in DSQL (Dynamic SQL) parser, which barfs at 'UNION' keyword in 'CREATE VIEW' constructs. Since InterBase does not allow DDL statements in stored procedures, the only way to create such view currently is to write ESQL (Embedded SQL) code and preprocess it with gpre, embedded SQL code preprocessor. This is possible because gpre handles parsing by itself, compiling directly into BLR.
Collapse
Posted by Sebastian Skracic on
I stand corrected: at least in 6.0, VIEWs with UNION can be created regardless of the execution method (that means yes, you can fire up isql and create view from there).
Collapse
Posted by cosmin apreutesei on
Actually I used COALESCE quite a lot in Firebird 1.5, are you sure that wasn't in IB when they forked the source tree?

Also (not an IB fan, but) the mechanism of returning multiple rows from a stored procedure (usually called currying in functional languages) plus some other SQL "limitations" can help other purposes, like clean, scalable design quite well. I'll never forget some appealing MSSQL features I used years ago that blown right in my face when the system got big.

Collapse
Posted by cosmin apreutesei on
... as a personal aproach to the SUSPEND mechanism: recursivity finally feels declarative. and temporary tables suck anyway.