macrotsql

Native T-SQL extensions fo SPs and functions for MS SQL 2005

Macro T-SQL project - extended and extensible T-SQL

MISSION AND CREDO

  1. T-SQL MUST AND HAVE TO BE BETTER
  2. T-SQL CAN BE BETTER WITH IT'S OWN POWER
  3. WE HAVE TO USE AND HACK ABILITIES OF T-SQL TO HELP IT BE BETTER

APPROACHES

  1. T-SQL syntax simplification
  2. T-SQL metadata management
  3. Declarative, OOP and AOP language improvements
  4. Easy schema creation, upgrading and migration support

CURRENT STATE

Code is working, ready to use.

Many features already implemented.

REQUIREMENTS

Any version of MS SQL 2005 (not tested on 2008, not working on 2000)

HOW TO START

  1. Checkout repository or download ZIP file
  2. Create empty MS SQL 2005 database
  3. Open and execute comdiv.mtsql.core.sql script
  4. Open and execute comdiv.mtsql.core.interfaces.sql script
  5. All must be executed without errors, table of generated objects must be shown
  6. See examples here, in code itself and in project blog and enjoy "T-SQL++"

FEATURES

ALL CODE IS WORKING AND CAN BE EXECUTED FROM SQL WORKBENCH IF comdiv.mtsql.core.sql HAVE BEEN EXECUTED ON GIVEN DATABASE

MACRO T-SQL ABILITIES ARE TRIGGERED ON OBJECT IF IT'S CREATED ON SPECIAL MANER

/*@ <name>hello_world</name> @*/ create procedure _ as begin declare @message nvarchar(255) set @message = 'hello world from MTSQL proc {{""SELF.NAME""}} at {{getdate()}} ' print @message end go exec first_example /* prints : hello world from MTSQL proc dbo.hello_world at Jul 15 2010 10:21PM -- time of course is 'now' */

  • name provided at one point on declaration maner
  • no checking of object existense - performs on background - CREATE PROC works with ' _ ' named objects as CREATE OR REPLACE (as in PostGRES) - see details about '_' names at this blog article
  • splices in form {{...any castable to nvarchar(max) call...}} are supported ("" are substituted by ' )
  • special accessor SELF provides SELF.NAME means fullname of current object
  • you can still create objects usual way without any troubles from Macro T-SQL

EXTENDED METADATA SUPPORT

see this article at my blog

You can create any VIEW, PROCEDURE, FUNCTION or TRIGGER object and assign XML declaration to it:

/*@ <name>dialog_printer</name> <question>why?</question> <answer who="bill gates">by default!</answer> <important/> @*/ create proc _ as begin declare @question nvarchar(max) set @question = SELF.ATTR('question') declare @answer nvarchar(max) set @answer = '{{SELF.ATTR(""answer"") }} ( {{SELF.SUBATTR(""answer"",""who"")}} )' if SELF.HASATTR('important') = 1 begin set @answer = '!!! ' + @answer + ' !!!' end print @question print @answer declare @message nvarchar(255) set @message = 'i can reach xml : {{ SELF.QUERY(""//*[@*]"") }} ' print @message end go exec dialog_printer /* will print : why? !!! by default! ( bill gates ) !!! i can reach xml : <answer who="bill gates">by default!</answer> */

  • you can define any attributes
  • in '_' named object you can use quick accessors to metadata (SELF.ATTR, SELF.QUERY, SELF.HASATTR, SELF.SUBATTR)
  • you can apply metadata to ANY object (except TABLES) not only for special MTSQL '_' named objects
  • note - TABLES not stores it's SQL in sys.sql_modules - for now tables are not supported by MTSQL abilities
  • you can use it to find objects by metadata:

```

select fullname, comdiv.getsubattribute(sql,'answer','who'), xml.query('//question') from comdiv.schemaview where type='PROCEDURE' and comdiv.hasattribute(sql,'important')=1

/* this will return our dialog_printer procedure with data : dbo.dialog_printer, 'bill gates', 'why?'

NOTE: special syntax is option of special '{{{_}}}' named objects "Macro TSQL objects", All context of T-SQL processing cannot be modified in global context, so in usual queries and in usual object declarations you cannot use splices and other MTSQL Stuff, it is real limitation of MS SQL Engine - some of them are avoided by ONE-EXECUTE procedures (see below)

*/ ```

TEMPORARY, EMMIDIATLY EXECUTED PROCEDURES

We supply simply syntax to use Macro TSQL in usual scripts - ONE-EXECUTE procedures.

Such procedures are executed and dropped emmidiatly after creation create proc _ as begin print 'splices used {{getdate()}} {{""SELF.NAME""}}' end go /*@<trace/>@*/ create proc _ as begin print 'aspected' end go /* emmidiatly executed and print: splices used Jul 16 2010 4:09AM dbo.__temporary_macro_tsql_procedure procedure "__temporary_macro_tsql_procedure" started aspected procedure "__temporary_macro_tsql_procedure" finished */

  • just wrap tour code into create proc _ as begin ..... end block
  • you can use any Macro TSQL features without creating persistent objects

TABLE INTERFACES SUPPORT

You can define an 'interface' expressed in declarative maner with fields and methods (procedures, functions, triggers) which then can be safely applyed to target table. Interface as object is stored procedure that get tablename as parameter and performs ANY operations on it.

Look at interface and it's using: /*@ <name>comdiv_table_interface.code</name> -- special schema must be used, name of interface itself is 'code' <module name="comdiv.mtsql.core.interfaces" class="i_code" method="_declare" /> -- optional attribute for OOP support - described below <field name='code' type='nvarchar(255)' constraint='not null unique default cast(newid() as nvarchar(255))' /> <method name="id"> create function __ (@code nvarchar(255)) returns bigint as begin return (select top 1 id from THIS where code = @code ) end </method> <method name="code"> create function __ (@id bigint) returns nvarchar(255) as begin return (select top 1 code from THIS where id = @id ) end </method> @*/ create proc _ as begin return end go exec comdiv.definetable 'test' -- safe procedure which 'create if not exists' table with given name and single ID bigint (identity(10,10),PK) field go exec comdiv.implement 'test','code' -- we apply interface to table, all fields and methods verified on target table go insert test(code)values('test') -- code field exists print dbo.test_id('test') -- test_id method exists print dbo.test_code(dbo.test_id('test')) -- test_code exists too /* prints (1 row(s) affected) 10 test */ go * method names created automatically * interfaces can be applyed in two modes - full (with field constraints and methods) and simple (field schema only) - for temp tables and back storage copies of data * method bodies can use THIS keyword to access name of table that method is applyed to * interface can be applyed safely many times (fields are not recreated, methods recompiled) * interfaces can have inheritance by using <import>other_interface_name</import> attributes

OBJECT-ORIENTED METADATA, ORGANIZATION AND CALLING

We assume that metadata is very important thing for serious applications. 1. schemas can be imagine as 'namespaces' 1. for more flexible definition we provide <module> attribute with following structure: 1. <module name='modulename' class='classname' method='methodname'/> 1. comdiv.schemaview knows this attribute and export Module,Class and Method columns in view 1. we think that ANY real working object CAN and MUST be described by this attribute 1. name of module must map to SOURCE from which it was got (we use copy of filename without extension) 1. class must map to LOGICAL class (not to real schema or table name ) and group of methods that incapsulate some close functionality

for example your may have myschema.perform_index_rebuild and dbo.find_unique_indexes procedures but we can think that both of them are of class 'indexes' 1. method is functional name of concrete object - in previous example we can think that : * yschema.perform_index_rebuild**==indexes.rebuild**and**dbo.find_unique_indexes==indexes.findallunique**Macro TSQL supports automatically name resolving for STORED PROCEDURES and TRIGGERS (FUNCTIONS NOT SUPPORTS THIS FEATURE due to some T-SQL limitations) /*@ <name>i_need_this_schema.and_by_my_project_i_need_this_name_for_function</name> --schema needs <module name='mycompany.utils' class='echo' method='call'/> -- our metadata @*/ create function _ (@call nvarchar(255)) returns nvarchar(255) as begin declare @result nvarchar(255) set @result = 'echo to: '+ @call return @result end go print i_need_this_schema.and_by_my_project_i_need_this_name_for_function('send') /* echo to: send */ go create proc _ as begin print echo.call('i can use this short and good name!!!') end go /* echo to: i can use this short and good name!!! */

FOREACH BLOCK INSTEAD OF MANUAL CURSOR WORKING

No comments : create proc _ as begin declare @name nvarchar(255), @type nvarchar(10) declare @idx int set @idx = 1 --foreach: select @name=name, @type = type from sys.objects --begin: print cast(@idx as nvarchar(255)) + '. : ' + @name +' ( '+ @type + ')' set @idx = @idx + 1 --end; end go /* 1. : sysrowsetcolumns ( S ) 2. : sysrowsets ( S ) 3. : sysallocunits ( S ) 4. : sysfiles1 ( S ) 5. : syshobtcolumns ( S ) ........ */ * fail-safe (try..catch) around any call to body * guarantee of cursor freeing * can be multiple in one script * CANNOT BE NESTED FOR NOW!!!

Extensible preprocessor and Aspect-oriented programming support

  1. Most of MTSQL features are performed by special DDL trigger which make calls to preprocessor dispatcher procedure.
  2. Dispatcher procedure search 'preprocessor' schema, get all procedures from it, orders them (by name) and then apply them one-by-one to source SQL code of object
  3. After processing, trigger performs real creation of object with normal name, '_' object always dropped
  4. Many preprocessors already wrote and included into core module (such splice_formatter, self_accessor_support and others)
  5. You can write your own preprocessor procedures and Macro TSQL trigger automatically will work with them
  6. One of preprocessor's type is 'ASPECTS' - something that prefixes, suffixes, or wrapps body of target objects, - aspects are embeded into object if declaration contains an flag attribute that launch aspect
  7. Special preprocessor exists for simple aspects creation (contains shortcuts to access source sql and apply prefix and suffix)

See code of one simple aspect from core module which tell procedure to signal entrance and leaving from it: ```

/@ comdivpreprocessor.x110_trace_aspect ..... -- make preprocessor 'aspect' preprocessor ...... @/ create proc _ as begin if comdiv.hasattribute('$sql','trace')=1 begin -- '$sql' - is special 'keyword' for aspects insert #before(s) values (' print '' procedure "{{@name}}" started '' ') insert #after(s) values ( ' print ''procedure "{{@name}}" finished '' ')
end end go /@ test_of_trace -- flag of using 'trace' aspect @/ create proc _ as begin print 1 end go /* prints: rocedure "test_of_trace" started 1 procedure "test_of_trace" finished

*/ ```

As you see T-SQL became significally NEW and modern language with OOP,AOP, self-reflection and declarative-styled syntax

AND WE HAVE CALLED IT MACRO TSQL

DUE TO THAT FACT THAT WE JUST REWRITE SOURCE CODE BY SOME PATTERN LOGIC

NOTES

YOU CAN SEE RESULT SQL OF ANY MACRO TSQL PROCEDURE EMMIDIATLY AFTER CREATION in print result by applying <sql/> attribute: ``` /@@/ create proc _ begin print '{{getdate()}}' end /* /@__temporary_macro_tsql_procedure@/ --ln:1 create proc __temporary_macro_tsql_procedure as begin --ln:2 print '' + cast(getdate() as nvarchar(max)) + '' --ln:3 end --ln:4

            /*####

create proc _ as begin print '{{getdate()}}' end ####*/

*/ ``` * you will see processed script at top * /#### block keeps source SQL which was primarely supplied to preprocessor

TSQL Macroses project - is ANOTHER project, with another approaches. Names are closer, because projects are oriented on code rewriting. TSQL Macroses is interesting thing and may be i'll import or rewrite some of those features here

Project Information

Labels:
tsql T-SQL aspects oop AOP