[nrdo-list] More portable-sql changes coming

[Stuart Ballard]

Derek has asked that I be more pro-active in seeking feedback with 
regard to new features in nrdo. In particular, he would like me to ask 
for feedback from other NetReach developers regarding the portable SQL 
(::true, ::false, :+, ::now) features that I added yesterday, and 
whether the syntax for them is okay[1].

I'm also interested in feedback on another new feature I just added in 
the same spirit, and some more that I'm planning. I'm using nrdo-list to 
ask for feedback because the discussion will be archived, and to 
encourage its use for nrdo discussion in general - that way if we ever 
end up with any non-NetReach users of nrdo, they can join in the discussion.

The new one that I just added is as follows:

  ::identity(module:table_name)
  This expands to "@@identity" on SQL server, and to the equivalent 
code on other databases. You have to provide the full table name (eg 
core:compass_site) because other databases don't automatically keep 
track of which table was last inserted into. Remember that SQL server 
just translates this to @@identity, so the table you specify here does 
have to be the one that @@identity will refer to.
  Example usage:
    insert into core_some_table (x, y) values ('x', 'y');
    update core_other_table set some_id = ::identity(core:some_table);

(Another portability guideline, btw, is that if you're ever going to 
include multiple SQL statements in a single 'before' block or elsewhere, 
they must always be separated by semicolons. SQL server doesn't care but 
other databases do)

I have one more set of directives that are needed to get the whole of 
Ivory compiling against PostgreSQL, and these are the following:

  1) ::declare(varname vartype)
  2) ::assign(varname = value)
  3) ::var(varname)

These three will allow declaring a variable and reusing it. On SQL 
server, these would translate to the following SQL:

  1) declare @varname vartype
  2) select @varname = value
  3) @varname

On Postgres, it appears you can only declare variables like this inside 
stored procedures, so instead this would be emulated by the following 
ugly hack:

  1) create temp table nrdovar_varname (varname vartype);
     insert into nrdovar_varname (varname) values (null)
  2) update nrdovar_varname set varname = value
  3) (select varname from nrdovar_varname)

Please reply to the list if you have any feedback or strong feelings 
about these features or any other aspects of nrdo.

Stuart.

[1] Especially, Derek has a strong opposition to the "::" used to 
introduce portable-sql directives. He would rather use a single ":", 
currently reserved for parameters, and specify a list of keywords that 
can never be used for parameter names. I disagree (obviously, since I 
implemented it the other way). If you have strong opinions one way or 
the other I'd love to hear them (although I don't promise to follow them 
;) )

--
Stuart Ballard, Senior Web Developer
NetReach, Inc.
(215) 283-2300, ext. 126
http://www.netreach.com/