DougHughes.net

Sproc to Cascade Deletes In SQL Server

As a developer, from time to time you need to delete an record out of a database. And, if that record has a number of tables that refer to it by foreign key (and heaven forbid those tables are themselves referenced), deleting a single row can be quite a headache. This wouldn’t be so bad if your foreign keys were set to cascade deletes, but they rarely are. In these cases you’re pretty much stuck writing a lot of SQL to delete one single record.

For situations like this I’ve created a stored procedure which will automatically cascade delete records for you. Here’s the complete sproc:

 CREATE PROCEDURE cascadeDelete
     @table varchar(100),
     @column varchar(100),
     @value int
 AS

 Print 'Need to delete from ' + @table + ' where ' + @column + '=' + convert(varchar, @value)

 DECLARE @refrencingTable varchar(100)
 DECLARE @refrencingColumn varchar(100)
 DECLARE @refrencingTableKey varchar(100)

 DECLARE @sql varchar(4000)
 DECLARE @keyval int

 BEGIN TRY
     SET @sql = 'delete from ' + @table + ' where ' + @column + '=' + convert(varchar, @value)
     EXEC (@sql)
     PRINT 'Bulk delete worked'
 END TRY
 BEGIN CATCH
     -- first, find all the objects which refer to this object
     DECLARE ref CURSOR LOCAL  FOR
     SELECT DISTINCT
        OBJECT_NAME(f.parent_object_id) AS table_name
        , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
        , sc.name as table_key
     FROM sys.foreign_keys AS f
     INNER JOIN sys.foreign_key_columns AS fc
        ON f.object_id = fc.constraint_object_id
     JOIN sys.columns as sc
         ON f.parent_object_id = sc.object_id
     JOIN sys.indexes as i
         on f.parent_object_id = i.object_id
         AND i.is_primary_key = 1
     JOIN sys.index_columns as ic
         on i.index_id = ic.index_id
         AND i.object_id = ic.object_id
         AND i.is_primary_key = 1
         AND sc.column_id = ic.column_id
     WHERE f.referenced_object_id = OBJECT_ID(@table);

     -- loop over the referring objects
     OPEN ref
     FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, @refrencingTableKey
     WHILE @@FETCH_STATUS = 0
     BEGIN
         --EXEC cascadeDelete @refrencingTable, @refrencingColumn, @value
         print @table + ' is referenced by ' + @refrencingTable + '.' + @refrencingColumn

         -- get all the id values for all the referring records and put them into a temp table
         SET @sql = 'SELECT ' + @refrencingTableKey + ' as keyval FROM ' + @refrencingTable + ' WHERE ' + @refrencingColumn + '=' + CONVERT(varchar, @value)

         CREATE TABLE #temp (
             keyval int
         )

Intro 101

I’m sure this post will quickly skyrocket to the top of the ‘most viewed’ list, so I wont keep anyone waiting!

My name is Chris Peterson, and I started working for Doug with Alagad 4 weeks ago as a full time contract programmer. My first day on the job I dove head first into a Model-Glue / Coldspring / Transfer clustered application and haven’t looked back since. It’s great to be working with other people who get excited as much as I do about event gateways, autowired remote proxies, and event construction.

I have been a computer junkie all my life, in 4th grade I was learning BASIC, Logo, and GRAPHICS programming. We had a commodore 64 and a PC with a 286 SX processor, and I wrote hundreds of program in BASIC to do animation and other fun stuff. I am a hardware geek, I love all things enterprise, love learning new coding languages, and make cool stuff on the lathe. After being the head line cook at a small town resturant, I worked for 3 years as a contract sysadmin, 8 years as a programmer for a transportation company, and now I am here with the other Alagadians (and couldn’t be happier). Look forward to iSeries, MS SQL, hardware, and of course Coldfusion posts down the road from me.

I am currently going to school part time, working on a dual BS in Biometric Security and CIS with a programming focus.

Opera DOM Snapshot FTW (for the win)

The other day I was trying to troubleshoot a problem I was having and needed to open an completely different browser. I chose to open the offending page in Opera. After fixing the issue, I played around with Opera as I had heard some good things about it from some people. I decided that, for a short time, I would try to use Opera as my main browser to give it a fair shake.

One thing I love about FireFox is FireBug. FireBug is simply invaluable for a developer. I looked around to see if there were any ‘plug-ins’ for Opera that offered similar functionality. I stumbled upon this link which had 2 separate tools you can add to Opera.

The first tool was the ‘Opera Developer Console’ which offers a lot of similar functionality to FireBug, but not nearly as easy to use.

The second tool was ‘DOM Snapshot’. This is a cool tool. Basically, what it does is it gives you, nicely formatted, the HTML rendering of the page as it exists. This means, if you have had items dynamically added to page via AJAX, they will be included in the snapshot. The coolest thing was that when I did a snapshot of a page with a in it, you could actually see the HTML that makes up the grid.

I know that you can use the ‘inspect’ functionality of FireBug to do the same, but I have found that its difficult to sometimes highlight the item I wanted to look at, and it was cumbersome to view all the HTML. Also, unlike view source in FireFox, DOM Snapshot does not make another http request to get the the source.

I found myself still needing to go back to FireFox from time to time when I did not have time to fiddle with the Developer Console to get the information I needed, but I have pretty much switched over to Opera as my full time browser.

Building a Groovy / CFML Project: Core Concepts and Tools

This document was prepared by Joe Rinehart for Alagad, Inc.

Introduction

Building a CFML project with a Groovy model is a productive choice. In my experience, Groovy just “feels” better for modeling a domain, and its complimentary tooling (Hibernate, Spring) “just works.” It’s a complicated beast, however, and having a guide to setting it up is handy. I’ve spent a good deal of time, both on my own and at Broadchoice, learning both the concepts of JEE Web Applications and the ins and outs of setting up these hybrid projects. This document is intended to walk you through the concepts and tools used in such a setup. Actually setting up the environment is individual to your situation, and something we’ll work on together. A lot of this is still rough around the edges. Eventually, I’d like to document a “template” of two different models of Groovy+Spring+Hibernate+CFML applications. Any attempt to do so now, however, would be obsolete the moment the environment was complete: each time through is a learning experience!

Core Concepts

As you move from pure CFML to a Groovy/CFML environment, there’s some new ground to cover. At first glance, the just the terminology around JEE applications is intimidating. Terms like “descriptors,” “application contexts,” and “transaction demarcation” are foreign to ColdFusion developers. With a little bit of general knowledge and a smattering of core foundation code, it’s not hard to move past this into a productive state, even if the entire team doesn’t quite understand what’s going on under the hood. That’s the beauty of object oriented programming: not every coder needs to know how everything works! Let’s start off by walking through the general knowledge we’ll need to develop a hybrid application.

Concept 1: JEE applications vs. CFML applications

The largest shift in mentality is the shift away from the idea of a “ColdFusion” application. In the Java world, when you want to start a new Web project, you create a new JEE application. In the ColdFusion world, we use one JEE application (the CFML engine itself), writing multiple ColdFusion applications, each denoted with its own Application.cfc or Application.cfm file.

Why does this matter for a Groovy / CFML project?

It actually has nothing to do with Groovy, but with our underlying support frameworks. The easiest way to manage Spring and Hibernate in a Web environment is to use JEE application mechanics to load Spring and deal with a few quirks of Hibernate. It’s not an issue with the frameworks, it’s simply how they were designed: to take advantage of the most common way of writing Web applications in the pure Java world.

What can we do about it?

1. Be Enterprisey

   If you’re working on a large application, it’s likely to be the only one running on its CFML server. In fact, it may be the only one running across a cluster of CFML servers. If you’ve got this luxury, using the built in utilities to manage Spring in a manner that automagically binds one “application context” (we’ll define that in a bit!) to a JEE Web application is the way to go.

2. Manage things yourself

   If you need to run more than one application, life is more complicated. Through Application.cfc, it’d be possible to use onApplicationStart to create and configure the filesystem-based implementation of the Spring XMLApplicationContext, placing the Spring factory in the Application scope. This’d be just like creating your own ColdSpring factory.

   The second piece of the puzzle deals with Hibernate sessions. We’ll cover the “why” in a bit, but we need to hold what’s known as the “Hibernate session” open until the request ends. In a Spring Web application (Option 1), there’s a really simple servlet filter that can be used. In this setup, however, you don’t have that option. Instead, it should be possible to move the closure of the session into Application.cfc’s onRequestEnd method with the same result.

   I haven’t done this in practice, but it’s conceptually possible. Andrew Powell (UniversalMind) has demonstrated such a setup, so it’s definitely workable. One thing I’d like to research is a setup (using Model-Glue) that uses two Hibernate sessions: one in the listener method phase that is a normal read/write session, and one in the view phase that is read-only, using an optimized isolation level for increased performance (not to mention it’d stop anyone doing dao.save( instance ) from within a view!).

Concept 2: Stateful Persistence

In the ColdFusion world, we’re used to a really simple way of working with databases. In the Java mindset, there “is no database.” That pretty much blows what we know out of the water. Under the idea of stateful persistence, you’re not working with records in a database. Instead, you work with objects, changing their state (values of their properties). When you’re ready, you ask “something” to save this state, whether it’s a hand-rolled Data Access Object or a full-bore ORM. In our case, we’ll be working with an ORM (Hibernate), so we’ll be focusing on its concepts. The first concept of working with Hibernate to understand the the idea of the session. In Hibernate terms, a session (not ColdFusion session, but Hibernate session!) is a “unit of work” against the database. It’s not necessarily a single CRUD operation: it could be the saving of a batch of instances, the deletion of one, or a combination of the above. Either way, all work is done within a session. When you’re ready to roll, the session is committed as a transaction. Yep, it’s complicated. Hibernate tutorials teach you to get the current session, do work, then commit and close, all manually. It’s good to know how this all works, but nobody wants to deal with it constantly. Luckily, a real-world framework (Spring) wraps all this rigamarole into utility classes and convenience Abstract classes that let you concentrate on writing simple DAOs to use Hibernate w/o having to worry about the mechanics of using it. In other words, Spring lets you write your DAO’s save() or HQL-based list() containing just your logic, not Hibernate-management code.

Concept 3: Development Environment

Your development environment is going to change. You’re likely to end up with 3-4 Eclipse projects:

1. A Java project. The Groovy plugin has issues compiling projects that have Java code that relies on Groovy code that relies on Java code (or any combination thereof). Basically, one has to compile before the other, which can make life….interesting. I typically run a separate Java project that contains some foundation code as well as any Java interfaces to which I’ll need to apply Spring AOP.
2. A Groovy project. This is where most of my code lives. My model, services, and implementations of any interfaces declared in the Java project live here. It’s a lot more fun to write in Groovy than in Java.
3. A CFEclipse project. A standard Model-Glue project living on my CFML server, with the exception that I don’t have a /model directory. Instead, I use a .jar export of the Java and Groovy projects into ColdFusion’s WEB-INF/lib.
4. (Optional) A ColdFusion Web Application project. I’ve only done this with Railo, but it’s possible to create a JEE Web project within Eclipse that is your CFML server itself, mimicking a standard JEE application. This lets you start / stop and publish to the server from within Eclipse. It’d likely replace project #3 entirely, as your CFML source code would just be part of the JEE application itself.

This setup does have the issue of requiring you to publish a .jar to your CFML server and having you restart the server any time you make a change. I thought I’d hate it at first, but it really makes you separate your work nicely: if you’ve written good unit tests for your Java / Groovy projects, you shouldn’t be deploying to your CFML server until things are ready to roll in the first place! On the other hand, I’m often guilty of just “sne
aking in” one little change, compiling, restarting ColdFusion, then finding out my little change breaks the whole works – and if I had unit tested in the first place, I wouldn’t have just wasted the 32 seconds it takes to start JBoss on my MacBook Pro. Just a “test first!” side note.

Concept 4: The Spring BeanFactory

We’re going to use Spring to manage Groovy-based services. It’ll work like ColdSpring, allowing autowiring of these services to your controller. There’s nothing much to say here: it “just works.” Spring’s a big beast though, and it’s worth reading a book. There’s simply too much goodness in there to describe in this document: everything from really easy JMS implementation to customizable security applied via simplified AOP. Buy “Spring in Action,” second edition. Read the core concepts and persistence (Hibernate) chapters. Skim whatever else is applicable. That is all.

Concept 5: Overall System Architecture

“But what does it all look like put together?” I’m sure someone’s been asking this for a while, but we had to settle a few other issues. Let’s start from the ground up.

1. A Spring ApplicationContext acts as a bean factory. It contains Groovy-defined services and other utility beans, such as Groovy-based DAOs. These Groovy-based DAOs implement Java-based interfaces, allowing you to use the Spring-based Hibernate conveniences that tell Hibernate when to start / commit a transaction. That’s because the conveniences rely on Spring AOP, which needs a true Java (not Groovy) interface. It sounds painful, but one single Java interface is all that’s really needed (defining a base DAO contact for all others to inherit).
2. A ColdSpring bean factory “wraps” the Spring application context, using it as a parent bean factory. I’ve got the code for this, and setting it up isn’t hard: you just twiddle your Model-Glue’s application’s index.cfm a bit.
3. You write tests for your domain objects (beans) in Groovy.
4. You write your domain objects in Groovy and test them, using Hibernate or JPA annotations to guide Hibernate in their persistence.
5. You write tests for your persistence layer in Groovy.
6. You write your persistence layer’s interfaces in Java then implement them in Groovy.
7. You write tests for your service layer in Groovy.
8. You write your service layer, coordinating your domain objects and persistence layer, in Groovy.
9. When all tests pass, you export the Groovy and Java projects to WEB-INF/lib and restart your CFML engine.
10. You then write Model-Glue controllers that use the service layer, and business as usual begins.

Tools

Okay, we’ve covered the new foundation knowledge we’ll need. It’s time to meet the a few new characters in our hybrid project show that we haven’t yet talked about.

Groovy

Groovy is a dynamic language for the JVM. If you like Javascript or CFScript, you’re going to love Groovy. It’s like Java with all the extra crap taken out and ColdFusion’s ability to be dynamic added in (except much more strongly!).

Spring

I thought I knew what Spring was until I used it. It’s not just “everything ColdSpring does, but for Java.” It’s more like everything Java should come with in the first place. It’s a Swiss-army knife of helpful functionality, providing modules that provide bean factories, persistence helpers, testing helpers, an MVC framework, a security framework, JMS simplification, Flex integration, and a whole lot more. Buy “Spring in Action,” second edition. It is essential.

Hibernate

Industry-standard ORM framework for Java. You can use it and forget the database exists. However, it’s a complicated beast, but with good reason. “Java Persistence with Hibernate” is a book well worth the investment, as it covers both the concepts behind ORM (showing why Hibernate is as complicated as it is!) and how to use the thing in the first place.

TestNG

TestNG is my preferred unit testing framework for Groovy. It’s a lot like CFCUnit or MXUnit. Spring provides TestNG conveniences that will allow us to load our entire application context inside our unit tests. It also provides a “transactional” convenience base test class that’ll make any work we do against the database in our tests function as a transaction that gets rolled back when the test completes (or fails!), meaning that our tests won’t pollute our development databases. TestNG + Spring rocks.

Groovy Eclipse Plug-In

The Groovy Eclipse Plug-In allows you to add a Groovy project nature to any Java project. It mostly works, but can sometimes go nuts. Cleaning your project usually helps. Its code hinting is utterly useless, primarily due to Groovy’s dynamic nature. This is similar to why CFC method introspection in CFEclipse is something of a fool’s errand.

Getting Started with Groovy, Spring, Hibernate and More with ColdFusion

The Alagad battleship is turning. For years now we have followed more or less the same process for developing applications. Namely, we’ve made use of now-traditional ColdFusion frameworks such as Model-Glue, ColdSpring, and Reactor. We’ve played around with various alternative frameworks and techniques, but essentially, this is what we’ve done for several years now. And through those years of experience we’ve learned a few valuable lessons.

For example, I’ve learned it’s a heck of a lot easier to not manage your own requests and let Model-Glue do that. I’ve learned the beauty that is Inversion Of Control. And, sadly, I’ve learned that ColdFusion Components are really, really, heavy.

Over the least few years we’ve had a number of applications we’ve created which, to me, never felt as performant as I felt they should. Granted, tuning ColdFusion and JRun makes a big difference, but more and more we’ve had to apply techniques to work around ColdFusion’s shortcomings in this respect.

There are still aspects of ColdFusion that I really, really like. For basic applications ColdFusion components work just fine. Then there are features like the ability to integrate with, essentially, everything which are easy to take for granted. Not to mention the features that allow you do pretty much anything easily. And it helps that my team knows ColdFusion inside and out. But, for me, the component performance has slowly been whittling my will down. And, when you realize that there are teams at companies like IBM and RedHat whose entire job is to create and support open source tools that compliment Java, you really begin to realize that tooling in ColdFusion is just subpar.

Last summer I was lucky enough to see Joe Rinehart at Scotch on the Rocks talk about Groovy. For those of you who have not yet heard of Groovy, it’s a really nice dynamic language similar to ColdFusion which compiles directly into Java classes that any Java application can use, including ColdFusion. And, because you end up working with Java classes, excellent tools like Hibernate and Spring are available to you and you no longer have to pay the CFC-overhead-tax.

Since seeing this presentation I’ve read up on Groovy and I’ve played around with it a bit and feel that it’s a good, strong, contender. I honestly don’t know or have an in-depth understanding of all the language features, but I like what I’ve seen so far.

Our plan at Alagad now is to start developing the model and service layer of our applications in Groovy. We will configure them using Spring and use Hibernate for persistence. We plan to continue to use ColdSpring, Model-Glue, ColdFusion (maybe some day Railo) for the presentation tier of HTML based applications. I believe that this will ultimately give us the best-of-breed structure for Enterprise class web applications. In fact, it should be fairly simple to start using BlaseDS for Flex integration with our Spring configuration as well.

With that said, none of this is easy stuff. It’s quite different from how my team of ColdFusion developers is used to developing. At this point we really only have the vaguest idea on how to configure a development environment. There is a lot to learn. We’re lucky in that Joe Rinehart has agreed to help us learn some of the more important concepts. As Alagad moves forward on this slowly I plan to blog about lessons we’ve learned, how we setup our development environment, how we test and deploy applications, and more. So, please watch this space. Be sure to ask questions and try to steer the topics I post on. The next thing I’ll post is a general overview document Joe wrote for us.