andro-general

[Andro-general] MS SQL Server

[Kenneth D. <ke...@se...>]

Well I had to get my head out of postgres mode.  SQL Server does allow 
replacements pretty elegantly, but the approach is vastly different.  
It's actually easy.

The idea is you create an INSTEAD OF trigger.  In the case of an INSERT 
trigger, you actually have to code an INSERT that puts in the mix of 
what the user typed and what you are going to replace.  If you don't do 
that, nothing happens.

Conceptually this is easy to handle for all three operations, INSERT, 
UPDATE, DELETE.  The next question becomes how much work is involved?  
Can Andromeda's trigger generation system be generalized to allow for 
this other way of thinking, or will it work better if everything is 
hardcoded for each platform?

Here is the worst case:

1) Column A is a FETCHed value
2) Column A is also a foreign key to some other table, and must be checked
3) Column B is a FETCHED value, based on Column A
4) Column C is calculated from A and B

That's the hairiest situation Andromeda handles, and it does it rather 
well.  There is a complex column dependency system that works out the 
order of calculations and FK checks to make sure those go well.

So, can it be generalized to all platforms?

Well, in a row-by-row situation, yes.  Each platform has a slightly 
different syntax, but basically the trigger can generate all derived 
values and store them in variables (that's what it does now).  Then, if 
its SQL Server, it must actually issue an INSERT or UPDATE to commit the 
values.

But I still have to experiment and work out if MS SQL triggers are 
row-by-row or statement.  I seem to recall they are statement level by 
default, and so we get into a bit of a jam.  Have to conduct an 
experiment there.

More later...

-- 
Kenneth Downs
Secure Data Software
ke...@se...  www.andromeda-project.org www.secdat.com
Office: 631-689-7200
Cell: 631-379-0010
Fax: 631-689-0527

Re: [Andro-general] MS SQL Server

[Justin D. <zip...@gm...>]

Ken,

How exactly are you doing these replacements with INSTEAD OF triggers?
Are you using views? Unless I am missing the IF TRIGGER_NESTLEVEL() >
1 THEN DONT_INSTEAD_OF() syntax, I think its impossible to do without
views.

In my particular case, a view won't work. I'm using a trigger to
populate a new column from an attribute in an xml column because we
don't have time to change and test all the nasty stored procs used by
our ETL process. To illustrate a example:


Id | XmlColumn                        | NewColumn
1  | <Element Attrib1="blarg"/>  | blarg

While you can do wonderful things with xml columns in SQL server
200(5|8), the indexes are broken in such a way that joins are really
slow. Hence the quasi denormalization (for lack of a better term due
to the unholy matrimony of SQL and XML) you see above.

Now the trigger is just a work around, at some point we will update
and test all the stored procedures to populate that piece of data in
the column and not the attribute. Since this is a temporary trigger to
avoid changing code, introducing a view into the mix is not an option.
However, if I can make an INSTEAD OF trigger work, I'd like to compare
its performance to the AFTER trigger I now use.


On Sat, May 2, 2009 at 9:46 AM, Kenneth Downs <ke...@se...> wrote:
>
> Well I had to get my head out of postgres mode.  SQL Server does allow
> replacements pretty elegantly, but the approach is vastly different.
> It's actually easy.
>
> The idea is you create an INSTEAD OF trigger.  In the case of an INSERT
> trigger, you actually have to code an INSERT that puts in the mix of
> what the user typed and what you are going to replace.  If you don't do
> that, nothing happens.
>
> Conceptually this is easy to handle for all three operations, INSERT,
> UPDATE, DELETE.  The next question becomes how much work is involved?
> Can Andromeda's trigger generation system be generalized to allow for
> this other way of thinking, or will it work better if everything is
> hardcoded for each platform?
>
> Here is the worst case:
>
> 1) Column A is a FETCHed value
> 2) Column A is also a foreign key to some other table, and must be checked
> 3) Column B is a FETCHED value, based on Column A
> 4) Column C is calculated from A and B
>
> That's the hairiest situation Andromeda handles, and it does it rather
> well.  There is a complex column dependency system that works out the
> order of calculations and FK checks to make sure those go well.
>
> So, can it be generalized to all platforms?
>
> Well, in a row-by-row situation, yes.  Each platform has a slightly
> different syntax, but basically the trigger can generate all derived
> values and store them in variables (that's what it does now).  Then, if
> its SQL Server, it must actually issue an INSERT or UPDATE to commit the
> values.
>
> But I still have to experiment and work out if MS SQL triggers are
> row-by-row or statement.  I seem to recall they are statement level by
> default, and so we get into a bit of a jam.  Have to conduct an
> experiment there.
>
> More later...
>
> --
> Kenneth Downs
> Secure Data Software
> ke...@se...  www.andromeda-project.org www.secdat.com
> Office: 631-689-7200
> Cell: 631-379-0010
> Fax: 631-689-0527
>
>
> ------------------------------------------------------------------------------
> Register Now & Save for Velocity, the Web Performance & Operations
> Conference from O'Reilly Media. Velocity features a full day of
> expert-led, hands-on workshops and two days of sessions from industry
> leaders in dedicated Performance & Operations tracks. Use code vel09scf
> and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf
> _______________________________________________
> Andro-general mailing list
> And...@li...
> https://lists.sourceforge.net/lists/listinfo/andro-general

Re: [Andro-general] MS SQL Server

[Kenneth D. <ke...@se...>]

Justin Dearing wrote:
> Ken,
>
> How exactly are you doing these replacements with INSTEAD OF triggers?
> Are you using views? Unless I am missing the IF TRIGGER_NESTLEVEL() >
> 1 THEN DONT_INSTEAD_OF() syntax, I think its impossible to do without
> views.
>
>
>   

I am so far manually coding examples to see what the generator would 
have to do, but the pseudo-code looks something like this:

CREATE TRIGGER blah
  INSTEAD OF INSERT
  on TABLE
blah blah blah
   
   -- the trick is to do the insert that did
   -- not happen because of INSTEAD OF
   -- here is how a fetch and chain would work

   INSERT INTO table (customer,custtype,discount,amount,amt_discount)
   SELECT inserted.customer
             ,customers.custtype
             ,custtypes.discount
             ,inserted.amount
             ,inserted.amount * custtypes.discount
      FROM inserted
      JOIN customers ON inserted.customer = customers.customer
      JOIN custtypes ON customers.custtype = custtypes.custtype


It's a little messy for my taste.  I already have code that will 
generate those JOINs, but it is a monster.  I'm thinking about other 
tricks, that make much nastier code but are much easier to generate, 
such as:

   INSERT INTO table (customer,custtype,discount,amount,amt_discount)
   SELECT inserted.customer
             ,(SELECT customers.custtype FROM customers WHERE 
custtype=inserted.custtype)
             ,(Select custtype.discount from custtypes where custtype = 
(....insert subquery from line above))
             ,inserted.amount
             ,inserted.amount * (...insert discount subquery from two 
lines up)
      FROM inserted

As I said, this makes for extremely nasty looking INSERT/UPDATE 
statements, but is far far easier to generate, because you keep re-using 
the subquery snippets.  It also heavily depends upon SQL Server 
optimizing the statement, but my experience with SQL Server actually has 
me optimistic on that point.  We may even be able to get away with this:

   SELECT inserted.customer
             ,(subquery to get custtype) as custtype
             ,(Select custtype.discount from custtypes where custtype = 
custtype) as discount
             ,inserted.amount
             ,inserted.amount * discount
      FROM inserted

...where i have used "as" to name the results and refer to them later.  
SQL Server is much more generous in letting you do this than Postgres, 
but I have to brush up on it.



> On Sat, May 2, 2009 at 9:46 AM, Kenneth Downs <ke...@se...> wrote:
>   
>> Well I had to get my head out of postgres mode.  SQL Server does allow
>> replacements pretty elegantly, but the approach is vastly different.
>> It's actually easy.
>>
>> The idea is you create an INSTEAD OF trigger.  In the case of an INSERT
>> trigger, you actually have to code an INSERT that puts in the mix of
>> what the user typed and what you are going to replace.  If you don't do
>> that, nothing happens.
>>
>> Conceptually this is easy to handle for all three operations, INSERT,
>> UPDATE, DELETE.  The next question becomes how much work is involved?
>> Can Andromeda's trigger generation system be generalized to allow for
>> this other way of thinking, or will it work better if everything is
>> hardcoded for each platform?
>>
>> Here is the worst case:
>>
>> 1) Column A is a FETCHed value
>> 2) Column A is also a foreign key to some other table, and must be checked
>> 3) Column B is a FETCHED value, based on Column A
>> 4) Column C is calculated from A and B
>>
>> That's the hairiest situation Andromeda handles, and it does it rather
>> well.  There is a complex column dependency system that works out the
>> order of calculations and FK checks to make sure those go well.
>>
>> So, can it be generalized to all platforms?
>>
>> Well, in a row-by-row situation, yes.  Each platform has a slightly
>> different syntax, but basically the trigger can generate all derived
>> values and store them in variables (that's what it does now).  Then, if
>> its SQL Server, it must actually issue an INSERT or UPDATE to commit the
>> values.
>>
>> But I still have to experiment and work out if MS SQL triggers are
>> row-by-row or statement.  I seem to recall they are statement level by
>> default, and so we get into a bit of a jam.  Have to conduct an
>> experiment there.
>>
>> More later...
>>
>> --
>> Kenneth Downs
>> Secure Data Software
>> ke...@se...  www.andromeda-project.org www.secdat.com
>> Office: 631-689-7200
>> Cell: 631-379-0010
>> Fax: 631-689-0527
>>
>>
>> ------------------------------------------------------------------------------
>> Register Now & Save for Velocity, the Web Performance & Operations
>> Conference from O'Reilly Media. Velocity features a full day of
>> expert-led, hands-on workshops and two days of sessions from industry
>> leaders in dedicated Performance & Operations tracks. Use code vel09scf
>> and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf
>> _______________________________________________
>> Andro-general mailing list
>> And...@li...
>> https://lists.sourceforge.net/lists/listinfo/andro-general
>>     
>
> ------------------------------------------------------------------------------
> The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your
> production scanning environment may not be a perfect world - but thanks to
> Kodak, there's a perfect scanner to get the job done! With the NEW KODAK i700
> Series Scanner you'll get full speed at 300 dpi even with all image 
> processing features enabled. http://p.sf.net/sfu/kodak-com
> _______________________________________________
> Andro-general mailing list
> And...@li...
> https://lists.sourceforge.net/lists/listinfo/andro-general
>   


-- 
Kenneth Downs
Secure Data Software
ke...@se...  www.andromeda-project.org www.secdat.com
Office: 631-689-7200
Cell: 631-379-0010
Fax: 631-689-0527

Re: [Andro-general] MS SQL Server

[Justin D. <zip...@gm...>]

On Sat, May 9, 2009 at 12:48 PM, Kenneth Downs <ke...@se...> wrote:

> I am so far manually coding examples to see what the generator would have to
> do, but the pseudo-code looks something like this:
>
> CREATE TRIGGER blah
>   INSTEAD OF INSERT
>   on TABLE
> blah blah blah
>
>    -- the trick is to do the insert that did
>    -- not happen because of INSTEAD OF
>    -- here is how a fetch and chain would work
>
>    INSERT INTO table (customer,custtype,discount,amount,amt_discount)
>    SELECT inserted.customer
>              ,customers.custtype
>              ,custtypes.discount
>              ,inserted.amount
>              ,inserted.amount * custtypes.discount
>       FROM inserted
>       JOIN customers ON inserted.customer = customers.customer
>       JOIN custtypes ON customers.custtype = custtypes.custtype
>
>

It seems trigger recursion is off by default on sql server (or at
least on the sql server instanci I am playing with.) I thought this
was not the case normally.  Since trigger recursion is a server level
thing, its something you might want to look into. I'm going to do some
more research my self.

I initially thought you code worked due to the max recursion level
being reached. I now see it works because recursion isn't on. I should
have just tried to write the trigger the way you did even though my
brain said "infinitative recursion loop".

Regards,

Justin Dearing

Re: [Andro-general] MS SQL Server

[Kenneth D. <ke...@se...>]

Justin Dearing wrote:
> On Sat, May 9, 2009 at 12:48 PM, Kenneth Downs <ke...@se...> wrote:
>
>   
>> I am so far manually coding examples to see what the generator would have to
>> do, but the pseudo-code looks something like this:
>>
>> CREATE TRIGGER blah
>>   INSTEAD OF INSERT
>>   on TABLE
>> blah blah blah
>>
>>    -- the trick is to do the insert that did
>>    -- not happen because of INSTEAD OF
>>    -- here is how a fetch and chain would work
>>
>>    INSERT INTO table (customer,custtype,discount,amount,amt_discount)
>>    SELECT inserted.customer
>>              ,customers.custtype
>>              ,custtypes.discount
>>              ,inserted.amount
>>              ,inserted.amount * custtypes.discount
>>       FROM inserted
>>       JOIN customers ON inserted.customer = customers.customer
>>       JOIN custtypes ON customers.custtype = custtypes.custtype
>>
>>
>>     
>
> It seems trigger recursion is off by default on sql server (or at
> least on the sql server instanci I am playing with.) I thought this
> was not the case normally.  Since trigger recursion is a server level
> thing, its something you might want to look into. I'm going to do some
> more research my self.
>
> I initially thought you code worked due to the max recursion level
> being reached. I now see it works because recursion isn't on. I should
> have just tried to write the trigger the way you did even though my
> brain said "infinitative recursion loop".
>   


I think you might be misunderstanding trigger recursion.

With an INSTEAD OF trigger, the original command issued by the app is 
never executed, the trigger fires instead.  If the trigger does not do 
the INSERT, (or update or whatever), then it does not happen.  If the 
trigger *does* do the command, this does not cause recursion. 

To repeat:  An INSERT command inside an INSTEAD OF INSERT trigger does 
not cause trigger recursion to occur.

Recursion occurs when a "FOR" trigger writes to the same table that the 
trigger is on.  Then, if trigger recursion is on at the database level, 
the same trigger will fire again -- up to a limit of 32 total depth of 
trigger call stack.

There are likely more subtleties, but that is the broad picture.

> Regards,
>
> Justin Dearing
>
> ------------------------------------------------------------------------------
> The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your
> production scanning environment may not be a perfect world - but thanks to
> Kodak, there's a perfect scanner to get the job done! With the NEW KODAK i700
> Series Scanner you'll get full speed at 300 dpi even with all image 
> processing features enabled. http://p.sf.net/sfu/kodak-com
> _______________________________________________
> Andro-general mailing list
> And...@li...
> https://lists.sourceforge.net/lists/listinfo/andro-general
>   


-- 
Kenneth Downs
Secure Data Software
ke...@se...  www.andromeda-project.org www.secdat.com
Office: 631-689-7200
Cell: 631-379-0010
Fax: 631-689-0527