deepdark.net - James Green's Blog - Code Generation

More different ways to script database objects a different way

Wed, 21 Feb 2007 07:32:28 GMT

Rob Farley posted a very interesting codegen post on scripting objects this morning, and as a reforming codegen junky I just couldn't let it go without comment :-)

Firstly, I modified his query as so:



select quotename(si.name) as "@IndexName", quotename(ss.name) AS "@SchemaName", quotename(so.name) AS "@ObjectName",

    stuff((select ',' + quotename(sc.name)

            from sys.index_columns sic

            join sys.columns sc

                on sc.column_id
= sic.column_id

            where so.object_id =
sic.object_id


                and sic.index_id
= si.index_id

                and sc.object_id =
so.object_id


            order
by sic.key_ordinal

            for xml
path('')),1,1,'') as "@IndexColumns"

from sys.indexes si

join sys.objects so

    on so.object_id = si.object_id


join sys.schemas ss

    on ss.schema_id = so.schema_id

where so.type = 'U' and si.type =
1

for xml path('index'), root('indexes')

Things I'll point out:

Adding the extra for xml clause, this time specifying a better row element name than 'row' and also adding a document node
Once the outter for xml clause is in place, we can alias the columns using @ to have them come out as elements in the results

We can feed the results of that query straight into the following XSLT:

<?xml version="1.0" encoding="iso-8859-1"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output method="text"/>

    


    <xsl:template match='/'>

    <xsl:apply-templates select ='indexes/index'/>

    </xsl:template>



  <xsl:template match='index' >

create index <xsl:value-of select='@IndexName'/> 


  on <xsl:value-of select='@SchemaName'/>.<xsl:value-of select='@ObjectName'/>
(<xsl:value-of select='@IndexColumns'/>)

  </xsl:template>

  


</xsl:stylesheet>

I'm not claiming this method is superior, just different, which I think is in keeping with the spirit of Rob's post :-) and I have learned more about the improvements of the for xml clause in SQL Server 2005 in the process.

I'll leave final judgment on the utility of this approach as an exercise to the reader; my instinctive reaction is to include it as a build step to help you snapshot schema changes between check-ins, or as Rob suggests to take objects from one database and create slightly different objects in another database – there are no wrong answers and if you think of something interesting please leave a comment :-)

Grab the source files here: ScriptObjects.zip (.78 KB)

Code Generation in T-SQL

Mon, 11 Dec 2006 22:35:10 GMT

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

Basic Code Generation with XSLT (for Ninjas)

Mon, 04 Dec 2006 05:21:23 GMT

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.

Basic Code Generation with XSLT (...in a 2.0 world)

Mon, 27 Nov 2006 11:00:59 GMT

(this post is an appendix to my prior post on Basic Code Generation with XSLT, because I forgot this bit the first time!)

I just wanted to expand on thsi line in the VB code part of the template:

Public Class DatabaseAccess

    Inherits ConvienientBaseClass

Including a (facetiously named) base class for the generated code was no accident. It provides a good way to seperate the generated code from the human written code. All the generated code should be the tedious, repetitive, error prone code. Exception handling, transaction enrolement, logging etc should be implemented in the base class so it can be ripped out if need be and of equal importance; the generated code can be re-generated without clobbering any human written code.

But that isn't very .NET 2.0 now is it. We can extend the concept of seperating the generated code from any human written parts by changing our template to generate partial classes:

Public Partial Class DatabaseAccess
Inherits ConvienientBaseClass

Basic Code Generation with XSLT

Thu, 23 Nov 2006 23:58:13 GMT

This was meant as a "Part II" to my prior post on generating Text, HTML & more XML with XSLT. The point for today's post is that source code files are text files. The example I am thinking of is generating a library of VB.NET wrapper classes for the stored procedures in a SQL Server database.

(I've also ticked the Continuous Integration category for this post. It wouldn't be hard to think of a scenario where a build process would generate a library from a reference database on the check-in of a stored proc script, then deliver the latest rev of the library to the developers, anyway...)

Step one would be fetch the meta data about the stored procedures, for example:

SELECT procs.Specific_Name, params.Parameter_Name, params.Data_Type, params.Parameter_Mode
FROM INFORMATION_SCHEMA.ROUTINES procs
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS params
ON params.Specific_Name = procs.Specific_Name
WHERE procs.Routine_Type = 'PROCEDURE' AND
procs.Specific_Name NOT LIKE 'dt_%'
FOR XML AUTO >

Once we add a document node to this, we will have a document that contains many element sets like this one:

  <procs Specific_Name="GetContactByID">

    <params Parameter_Name="@ContactID" Data_Type="nvarchar" Parameter_Mode="IN"/>

    <params Parameter_Name="@ContactGUID" Data_Type="int" Parameter_Mode="INOUT"/>

    <params Parameter_Name="@Found" Data_Type="bit" Parameter_Mode="INOUT"/>

  </procs>

I think that's all we need to get started.

Step two is to transform this data into VB.NET code. For today's example it suits me to generate two files:

One will contain classes that wrap ADO.NET calls to the database
The other will provide types that wrap properties for passing into and out of the first

The text of the first template is as follows

<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="2.0">

  <xsl:output method="text" indent="yes" />

  


  <xsl:template match="/">


Imports System

Imports System.Data.SqlClient

Imports System.Data

Public Class DatabaseAccess

    Inherits ConvienientBaseClass

   
Public Sub New(ByVal cn As SqlConnection, ByVal trn As SqlTransaction)

        MyBase.New(cn, trn)

    End Sub

    


    <xsl:apply-templates select="/database/procs"
/> 


    

End Class

  </xsl:template> 


  


  <xsl:template match="procs">

    '

    ' Wraps stored proc: <xsl:value-of select="@Specific_Name"
/>


    ' 


    Public Function Execute<xsl:value-of select="@Specific_Name"
/>(ByVal params As <xsl:value-of select="@Specific_Name"
/>Struct) _

                                             As <xsl:value-of
select="@Specific_Name" />Struct

       
Dim exec As New SqlCommand

        Dim param As SqlParameter

       
With exec

            .CommandText = "<xsl:value-of
select="@Specific_Name" />"

            .CommandType =
CommandType.StoredProcedure

            .Connection = MyBase.DatabaseConnection

            .Transaction =
MyBase.CurrentTransaction

        End With

  <xsl:apply-templates
select="params" />

       
Try

            If Not exec.Connection.State
= ConnectionState.Open Then exec.Connection.Open()

            exec.ExecuteNonQuery()

       
Catch ex As Exception

            Throw

        Finally

            If Not exec Is
Nothing Then exec.Dispose()

        End Try

   
End Function

  </xsl:template>


  


  


  <xsl:template match="params">


        param = exec.CreateParameter

        With param

            <xsl:if
test="@Parameter_Mode='INOUT'">.Direction = ParameterDirection.Output</xsl:if>


            <xsl:if
test="@Parameter_Mode='IN'">.Direction = ParameterDirection.Input</xsl:if> 

            .DbType = DbType.<xsl:value-of
select="@Data_Type" />


            .Value = params.<xsl:value-of
select="@Parameter_Name" />


            .ParameterName
= "<xsl:value-of
select="@Parameter_Name" />"

        End With

        exec.Parameters.Add(param)

  </xsl:template>

  


</xsl:stylesheet>

And the text of the second template is as follows:

<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="2.0">

  <xsl:output method="text" indent="yes" />

  


  <xsl:template match="/">


Imports System

    <xsl:apply-templates
select="/database/procs" /> 


  </xsl:template>


  


  <xsl:template match="procs">


' 


' Parameters for method: Execute<xsl:value-of select="@Specific_Name"
/>

' 


Public Class <xsl:value-of select="@Specific_Name" />Struct

   <xsl:apply-templates select="params" />   


End Class 

  </xsl:template>


  


  <xsl:template match="params">


   Private _<xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)"
/> As Date

 Public Property <xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)"
/>() _

                                                                     As  <xsl:value-of
select="@Data_Type" />


    Get

      Return _<xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)"
/>


    End Get

    Set(ByVal Value As <xsl:value-of select="@Data_Type" />)

      _<xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)"
/> = Value

    End Set

   End Property

  </xsl:template>

  


</xsl:stylesheet>

To my eye, these look more like VB.NET source files than XSLT templates. That's because they started life as .vb files. Then I renamed them .xslt and started inserting the XSLT tags in places where I needed substitution from the XML source.

To emphasise the hybrid-ness (is that a word) of these files, I have highlighted the VB.NET parts blue and the XSLT parts green, rather than keeping the VB.NET syntax highlighting.

Some breif thoughts:

The value of this to my mind is in not having to hand code *every* one. You hand code one, then generate the rest - in theory this reduces the opertunity for bugs which should be the focus of the exercise.
Maybe this may find a home in a long-running project to have this as part of the build process, or maybe as part of some tooling focused on small one-off
This are commercial products that do code gen based on XSLT. I haven't used them, but they may well be better than my home-brew sample :-)
Maybe useful for trainers, or producting samples for demos etc. I'm thinking now about times where there may be a need to generate side-by-side VB.Net, C# & Java sample code for example.
Also include comment blocks that are readable by NDoc!

[edit: added line breaks for formatting.]

SQL Server system tables for better scripting

Thu, 06 Oct 2005 13:24:38 GMT

Looks like it's about time for another post!

This one is about the SQL Server system tables. These are a fav of mine because I find them so useful in scripts.

The caveats when dealing with them are you need to be mindful of SQL Server versions. Everything that worked on SQL Server 7.0 will work on SQL Server 2000, but there are some minor tweaks in SQL Server 2000 that are not valid in SQL Server 7.0. Now is not a good time to mention SQL Server <= v6.5 because the system tables had an overhaul for 7.0, and I haven't checked any of this code on Yukkon/SQL Server 2005 yet.

My fav thing to use the tables for is dealing with object existance in scripts. In my books a good T-SQL script can be run over and over without damaging the database. Put another way, if your SQL Script throws an error if it is run twice against the same database it's not a good T-SQL script.

Consider we are dealing with the following table:

CREATE TABLE testing_data (
    pkey INT IDENTITY (1,1) NOT NULL,
    created DATETIME DEFAULT getdate() NOT NULL,
    modified DATETIME NULL,
    deleted BIT DEFAULT 0 NOT NULL,
    testing_val_1 NVARCHAR(15) NOT NULL,
    testing_val_2 NVARCHAR(255)
)

For whatever reason we want to drop and recreate this table each run, you could put the following statement before it:

IF Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testing_data]')
    AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE testing_data
GO

This is the syntax you will see if you choose to include Drops in scripts you generate from the Enterprise Manager, but I don't use it much, mainly because I can never remember the OBJECTPROPERTY() syntax!

Typically I do the following:

IF Exists(SELECT [id] FROM sysobjects
    WHERE sysobjects.[name] = N'testing_data'
    AND sysobjects.[type] = N'U')
    DROP TABLE testing_data
GO

I feel that Microsoft are hinting us towards using OBJECTPROPERTY() for future version compatability, but I still favor this syntax because apart from being easy to remember it's easy to adapt for other kinds of objects, e.g:

IF Exists(SELECT [id] FROM sysobjects
        WHERE sysobjects.[name] = 'prGetTestingDataRows'
        AND sysobjects.[type] = 'P')
    DROP PROC prGetTestingDataRows
GO

So if the Type column in sysobjects is 'U' for user tables, and 'P' for procedures then there are no prizes for guessing what TR, D & V might mean.

For a more complex example, lets say you want to change the type of the fileds testing_val_1 to NVARCHAR(35) ONLY if it has not been changed before, you could wrap the ALTER TABLE stateement in the following BEGIN... END:

IF Exists(SELECT syscolumns.[name]
    FROM syscolumns
    LEFT JOIN sysobjects
        ON syscolumns.[id] = sysobjects.[id]
    LEFT JOIN systypes
        ON syscolumns.[xtype] = systypes.[xtype]
    WHERE syscolumns.[name] = 'testing_val_1'
    AND systypes.[name] = 'nvarchar'
    AND sysobjects.[name] = 'testing_data')
BEGIN
    ALTER TABLE -- ... Implementation ommited for clarity
END

So having only touched two or three sys tables we have a couple of good tools that are easy to use. I'll cover more at a later date, in the mean time enjoy the extra metadata!

After blog mint [?]:

Here's an actual practical example from a script I have been working on recently (user name changed). This script makes a dozen or so procs, the svcapp account is a login used by a service that only has rights to exec these procs, and no rights granted to the base tables. This automates granting access to the created procs, and it much quicker than doing it in the SQL EM:

PRINT 'PART 4 - Granting access to user account'
GO

DECLARE @sql NVARCHAR(512)
DECLARE @name NVARCHAR(128)
DECLARE @usernm NVARCHAR(128)
DECLARE cr CURSOR FOR
    SELECT [name] FROM sysobjects
    WHERE type='P'
    ORDER BY [name]

SET @usernm = 'svcapp'

OPEN cr
FETCH NEXT FROM cr INTO @name

WHILE @@fetch_status = 0
BEGIN
    SET @sql = 'grant exec on ' + @name + ' to ' + @usernm
    EXEC sp_executesql @sql
    PRINT 'Granting EXEC on ' + @name + ' to user: '
    FETCH NEXT FROM cr INTO @name
END

CLOSE cr
DEALLOCATE cr

PRINT 'Done.'
GO