New metadata and OOP features in MacroTSQL

New feature 1.

Normal reflection over XML attributes (work on every object type whick keeps it's source code in sys.sql_modules (all except tables)

Assume that such view was created:

/*@
<myvar val1="1">2</myvar>
@*/
create view myview as select 1 as id

Now you can use comdiv.schemaview to find and test it:


select
type,
fullname,
comdiv.getattribute(sql,'myvar'),
comdiv.getsubattribute(sql,'myvar','val1')
from
comdiv.schemaview
where
comdiv.hasattribute(sql,'myvar')=1

So result will be :
type fullname col1 col2
---------------------------------------------------
VIEW dbo.myview 2 1


comdiv.schemaview
works for now above SP,F,T,V, not for tables.
Supports visualization of
name, module->name, module->class, module->method attributes,
events and some others
sygnal about 'isinterface','isprivate' due to applyed schema

For MacroSQL created object additionaly SourceSQL column provided
which contains pure sql (no declaration) of code before prepocessing

comdiv.validateschema
is FxCOP-like stored procedure that analyzes schema through comdiv.schemaview and search for errors, mistakes and not well formed metadata.

For example - after such view was created, i see:
exec comdiv.validateschema
=type====|===name====|=severity=|=errorcode=|info
=VIEW-----|-dbo.myview--|---40-----|--40-------|-module is not defined
=VIEW-----|-dbo.myview--|----5-----|--60-------|-best practice is to define class and method of object =

/*@
<myvar val1="1"></myvar>
<module name='mytestmodule' class='stub' method='sampleview' />
@*/
create view myview as select 1 as id

will match requirements


New feature 2.

Object-like style of calling inside MTSQL-style procedures.

Given such procedure (named due to some schema needs or historically):

/*@
<module class="utils" method="printdate" />
@*/
create proc dbo._SP_print_date as begin
declare @now datetime set @now = getdate()
print @now
end
in M-TSQL procdures you can call it not by real name, but by 'class'
NOTES: class names are applyed only if they are already exists!!!

/*@
<name>testproc&lt/name>
@*/
create proc _ as begin
exec utils.printdate
end
Code is very readable and is good for migration
You need just keep class->method names, but safely rename real object (with following recompilation of MTSQL code)

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

NEW! Method declarations in Macro TSQL

Here is last submit info (Вот что было в последних обновлениях):


1) NEW method declaration support for interfaces
2) FIX _drop now can drop triggers
3) FIX procedure renaming applyed correctly (only in outer declaration)
4) FIX pseudo object '_' now dropped when core module loaded
5) NEW syntax shugar - allow call to dynamic sql in short style :

exec _ '<<< select * from {{@table}} where {{@field}} = ''{{@value}}'' >>>'

6) UPG addition comdivpreprocessor.x300_table_interface_parameters_embeder - adds parameters to interface definitions if not provided manually (@table and @useconstraints parameters)
7) NEW safe and simple procedures to ensure (create if not exists) of filegroups and files of database
8) NEW comdiv.mtsql.core.interfaces.sql - module - some our best practices of table schemas (especially for hibernate mapping), expressed as comdiv table interfaces


What is most interesting? (Что наиболее интересно?)
method declarations (Определения методов в интерфейсах)
Look at this code:
-- INTERFACE CREATING:

/*@

<name>comdiv_table_interface.code</name>

<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

-- INTERFACE USAGE
GO
if(object_id('__X') is not null drop table __X
GO
_table '__X'
GO

_interface '__X', 'code'

GO
insert __X (code) values ('a')
GO

select dbo.__X_id('a')           -- 10

select dbo.__X_code(10)      -- 'a'

GO


Significant changes:
1) methods a totally defined on declarative maner
2) automatical name generation - don't need to copy in method implementation (use pseudo name __ instead)
3) special 'keyword' THIS that means table to which interface is applyed

(RU)
Значимые изменения:
1) методы полностью формируются в декларативной части
2) автоматическая генерация имен - не надо дублировать в реализации метода, используется псевдо-имя __
3) специальное "ключевое слово" THIS - обозначает целевую таблицу, к которой применяется интерфейс

Macro T-SQL project published (en)

More than 10 years i'm working with MS SQL



Something was upgraded during this years, and MS SQL 2005 is exactly good server for many and many projects that we develop and support.



But working with PostGres, system programming on C#, Boo and others give as not very good thing to think about. It's that T-SQL is not so good language as we want it to be... Not much readable, with some ugly constructions and everyday 'snippets' to do some simple stuff.



We were'nt want to live with this forever and wait MS to give as something fantastic in SQL 2016, but:

1. T-SQL is what it is - well complicated, but not extensible, not-declarational language and we cannot avoid it.
2. I hate any code-preparing utils that you MUST to call if you want to apply some DSL - what if i want to call SQL from APP - i must to supply it with very_good_utility.exe? and call it ?
3. Schema generator programs is far more ugly thing even than codegenerators...
4. Using of .NET assemblies looks mo prety, but... it's hard to support too and cause it's own problems
5. We like to solve problem in MS SQL 2005 itself and with T-SQL itself

In a moment we have 'evrica!'





Module we wrote is not a part of comercial order, is not selfish product, so I decide to publish it as open source.

Go to  http://code.google.com/p/macrotsql/.
Discussion at:

https://wave.google.com/wave/waveref/googlewave.com/w+zLyWVnrHA

By using special stored procedures and DDL triggers (without any .NET assembly or custom tool) you'll get folloing framework:

1. embeded XML documentation as in C#
2. declarative attribute programming (inspired by C# and Boo mete-attributes)
3. stored procedures and function SQL extensible preprocessor framework
4. object-oriented support for table schema definition (very wanted after POSTGRE, inspired by prototype.js :) - we define some set (with inheritance) of 'interfaces' that then are applyed to target table (extends it)
5. prepared set of preprocessors for some every day tasks:

• tracing of proc enter and leave
• wrapping procedure body in transaction
• explicite (in text) line numbering (for quick finding place of error from error comment)
• support fo  foreach macro block instead of ugly native  work with cursors (maybe worst thing in T-SQL)

Look at project, try it, send me feedback and patches. Wish that project will be usefull for you and you will be usefull for project :)

Проект Macro TSQL запущен (ru)

Более 10 лет работы с MS SQL не могли пройти даром.

Шли годы и многое менялось и движок становился лучше и краше и в принципе ни один из текущих коммерческих проектов и не требует ничего более SQL2005...

Но все же работа в параллельных проектах на PostGRES, системное программирование в C# привело к мысли, что T-SQL это уже довольно отсталый и неудобный язык для работы с БД, требущий много избыточного кода.

С этим не хотелось мириться, но:

1. T-SQL какой есть такой есть - не расширяемый, далекий от декларативности, с достаточно строгими и избыточными конструкциями
2. Очень не люблю всякие спец-утилиты для конвертации кода или что хуже всего - всякие программки, которые цепляются к базе и что-то там делают
3. Подцепление всяких хитрых .NET сборок - это конечно здорово, но существенно снижает переносимость и усложняет сопровождение баз.
4. Хотелось все делать просто с базой, просто из T-SQL

В какой-то момент нам это удалось.

И так как проект не связан с каким-то коммерческим заказом и не является продуктом сам по себе, решил опубликовать его в опен-сорс. Может кому пригодится.

Зайдите на:  http://code.google.com/p/macrotsql/.
Обсуждение в вейве:

https://wave.google.com/wave/waveref/googlewave.com/w+zLyWVnrHA

Там в целом все понятно:

При использовании специальных хранимых процедур и триггеров (без какого-бы то ни было вмешательства расширений .NET) вы получаете инфраструктуру для:

1. встроенного документирования функций в XML нотации - а-ля внедренной документации С#
2. декларативного программирования в форме XML атрибутов (тоже навеяно С#, хотя скорре это ближе к макроатрибутам Boo так как влияют больше на компиляцию, нежели чем на работу процедуры)
3. инфраструктуру для использования готовых и создания своих собственных перепроцессоров SQL для хранимых процедур и функций
4. объектную модель для формирования схемы таблиц - есть возможность создавать "интерфейсы", наследовать их друг от друга , а затем применять к таблицам с формированием наборов колонок, триггеров, специальных функций
5. готовый набор препроцессоров для ряда типовых задач:

• быстрое подключения трассировки на входе и выходе из процедуры
• обрамление тела процедуры в транзакцию
• явное нумерование строк (облегчает потом поиск места возникновения ошибки)
• поддержка простого в написании блока foreach в качестве макроса для работы с курсорами (которая в исходном T-SQL сделана просто ужасно)

Смотрите проект, пишите, присоединяйтесь к проекту. Надеюсь он понравится многим программистам на T-SQL и я получу тоже что-то полезное в проект