OK,  so I was trying to kick this Code Generation bent I have been on of late, but… opportunity knocked yesterday for a query where one of the possible solutions involved codegen and I was weak  

 

So imagine we have some convieniently breif and neatly anonymized canonical example like the following table:

 

SELECT customer_id, order_id

FROM OrderCustomer

 

 

customer_id	order_id
1	32
1	33
1	34
2	821
2	831
2	851
2	861
2	871
2	911
3	1
3	2
3	3
3	4
3	5

 

Now imagine the project is to remove all the rows from this table, except the lowest number order for  each customer.  The first step is to write a query to exceptionalize these rows:

 

SELECT customer_id, min(order_id) AS AS LowestOrderID

FROM OrderCustomer

GROUP BY customer_id

 

customer_id	LowestOrderID
1	32
2	821
3	1

 

The next step is to select some string literals with the original query so that the result is valid T-SQL

 

SELECT 'DELETE FROM OrderCustomer WHERE customer_id = ', customer_id,

       'AND order_id > ', min(order_id) AS LowestOrderID

FROM OrderCustomer

GROUP BY customer_id

 

Tangentally the little unit of joy in this whole experience for me is that  the <Ctrl-T> keyboard shortcut is the same in SQL Server 2005 Management Studio as it was back in Query Analyzer. 

 

So, hit <Ctrl-T> to output the results window as Text, then F5 to return the results to get something like:

 

DELETE FROM OrderCustomer WHERE customer_id =  1           AND order_id >  32

DELETE FROM OrderCustomer WHERE customer_id =  2           AND order_id >  821

DELETE FROM OrderCustomer WHERE customer_id =  3           AND order_id >  1

 

(2 row(s) affected)

 

(5 row(s) affected)

 

(4 row(s) affected)

 

Select all the DELETE FROM statements and copy and paste them into a new query window, then F5 to remove the rows.

 

Look at the table to test the result:

 

SELECT customer_id, order_id

FROM OrderCustomer

 

customer_id	order_id
1	32
2	821
3	1

 

This is something I have noticed in a lot of the samples around plugin architecture (for example).  The common theme is to reflect over the DLLs in an path and load the types that impement a certain interface.  Fair enough so far.  Once you have a collection of pointers to entry points you have plug-ins... but you also have an attack vector.

These examples need to flesh out the scenario of testing the plug-in for authenticity imho.

My gut feeling is I want an X.509 cert in there somewhere as a pre-shared secret, but I don't quite have the full picture in my head just yet, should that tie in with strong naming assemblies, or be seperate additional layer.  Suggestions welcome

Love him or hate him, the late Bill Hicks had a way with words.  From his 1989 live recording, Sane Man...

Wouldn't you like to see a positive LSD story on the news? To hear what it's all about, perhaps? Wouldn't that be interesting? Just for once?

"Today, a young man on acid realized that all matter is merely energy condensed to a slow vibration … that we are all one consciousness experiencing itself subjectively. There's no such thing as death, life is only a dream, and we're the imagination of ourselves. Here's Tom with the weather."

This is the last one in this current thread for a while :)

By the end of the last example we had code that would discover what stored procs were in a SQL Server database and generate a managed wrapper library that could be extended.

The next step it occured to me was to be then generate an assembly from this source and load it into the currently executing program.  The MSDN pages I have linked to have some good samples.

I'm adding this one to my toolbox of hammers looking for a nail.