What's it all about, Alfie?

Today I spent a good bit of the day trying to create a nice, user subscription system for my alerts. I needed it to be clear, but easy to use. With the database schema I had, I needed to use a left outer join to get all the alerts the user could possibly subscribe to. It was pretty fun to get it all worked out.

The core was a simple stored procedure that pulled all the data into a nice table:

  CREATE PROCEDURE dumpUserAlerts
      @username VARCHAR(80)
  AS
  BEGIN
      -- make a table to hold a little more than we'll be needing
      CREATE TABLE #everything (
          name        VARCHAR(32),
          description VARCHAR(8000),
          portfolios  VARCHAR(4096),
          chat        bit,
          email        bit
      )
      INSERT INTO #everything
          SELECT a.name, a.description, a.value AS portfolios, r.chat, r.email
            FROM ( SELECT fa.alertID, fa.name, fa.description, fp.value
                     FROM Alerts fa, AlertParams fp
                    WHERE fa.active=1
                      AND fa.alertID=fp.alertID
                      AND fp.name='Portfolio'
                      AND fp.setName='default' ) a
           LEFT OUTER JOIN AlertRecv r
            ON r.username=@username
            AND r.setName='default'
            AND r.alertID=a.alertID
 
      -- update the wildcard portfolios to something readable by the user
      UPDATE #everything
         SET portfolios='[All Portfolios]'
       WHERE portfolios='[*]'
 
      -- now let's add the portfolios to the end of the description
      UPDATE #everything
         SET description=description
                        + '<br><p class="hilite">'
                        + REPLACE(portfolios,',',', ')
                        + '</p>'
 
      -- now let's show the user what they were meant to see
      SELECT Name, Description, Chat, email AS Mail
        FROM #everything
  END

What I really like about the way this is coming together is the coordination of the SQL stored procedures, the Java Tomcat server (with its' servlets), and the web GUI with JavaScript. At my last job I did something like this, but not to this extent. I didn't have the nice JavaScript front-end... OK, I did, but the project didn't make use of it.

What I like is that I have three places to put code: the JavaScript, the Java, and the SQL. This means that I can distribute a solution in the most efficient manner possible. I can make use of the fact that the SQL can be changed independently of the Java or JavaScript, and that makes it a very powerful tool.

This is just how I like to code - across the entire system. Not just in one little slice. The code above was then massaged by the Java to remove NULL values and put in HTML INPUT check boxes and then using the onChange tag, these were able to send back messages to the same database to effect the change of the subscriptions.

Very nice.

This entry was posted on Monday, December 7th, 2009 at 4:19 pm and is filed under Coding, Cube Life. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.