среда, 14 июля 2010 г.

MTSQL : Strange '_'-named objects, why?

PROBLEMATIC
Almost all procedures, functions and other programmatical stuff in Macro T-SQL core and descendant scripts are  implemented in following mode:

/*@
<name>myprocname</name>
this is my real proc...
@*/ 
create proc _ as begin
         -- some stuff
         return
end



Why we use such strange syntax except usual :

create proc procname as begin
/* this is my real proc... */
         -- some stuff
         return
end

QUESTION ONE: WHAT'S FOR?

ANSWER : "CREATE OR REPLACE" AND NAME AMBIGUITY
T-SQL not have CREATE OR REPLACE constructions in it. So real-world scripts are looks like:

if object_id('procname') is not null drop proc procname
go 
create proc procname as begin
         -- some stuff
         return
end

Where are some visible problems in such code:
  1. name is repeated 3 times - so it's not good when you copy paste it or correct names, and it's anti-pattern
  2. you MUST execute this code in 2 commands - it is not big problem in SQLWB, but when you work from program client you must keep it in mind and perform 'GO' parsing and batching code
But in MacroSQL-style definition we have problems solved:
  1. no ambiguity - name defined at one place
  2. one command execution - cleaning up of objects performed by MTSQL DDL Trigger which drops object and then recreate it by provided SQL ('_' object dropped TOO, so you ALWAYS can create it
QUESTION TWO : WHY DDL TRIGGER NOT PERFORM IT'S STUFF WITHOUT SPECIAL-NAME USING?

ANSWER ONE: IT CANNOT
Usual (not AFTER) DDL triggers are executed not AFTER  COMMIT of creation, but AFTER CREATION ITSELF, so object MUST BE CREATED. If we try to call create proc procname, we still will caught error if such object exists even if we use DDL trigger for CREATE_PROCEDURE. (no analog of 'INSTEAD OF' ralized for DDL triggers).

SO WE NEED TO PROVIDE SPECIAL NAME WHICH NEVER BE REALLY EXISTED AFTER PROCESSING.

We decide to use '_' style - it's realy mimimalistic object name, and we havn't see real databases with object called 'dbo._' 

As alternative we choose it from another syntax:


create proc procname_mtsq as begin
         -- some stuff
         return
end
Trigger must have response to _mtsql suffix and drop it with creation of normally named object. But it's very bad:
  1. less readable  - no visible difference with usual definitions
  2. less ecological - we not have reserved just unusable '_' name, but whole NAMING RULE
ANSWER TWO: ECOLOGY OF PROJECT
MacroTSQL Trigger and Preprocessors that it calls, may be broken for usual syntax, they awaits some attributes (not provided by usual code), they perform some large operations on source code.
If such trigger will work on everything that occures in given DB, it will be dangerous for usual schemas and usual scripting.
By '_' name we have expressive and easy to use 'keyword' which differentiate Macro TSQL-awared object creation scripts from usual. MTSQL works only with it's stuff, not on usual procedures. 

QUESTION 3. What to do if my database have object named 'dbo._'?
Answer 1.: if it is possible - rename it
Answer 2 : if impossible - rewrite MTSQL source code for this database to use another name to define MTSQL objects

Комментариев нет: