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.

• 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.

• 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.

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!).

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.

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.