What's it all about, Alfie?

Today I spent time taking the database schema I made yesterday and making it look like a Java Properties file so that I could easily create Properties objects and then feed them to the alerts I had and not have to worry about changing the rest of the code. The problem is, the database schema was logical, but it wasn't anything like what the previous Properties file layout looked like.

The ChatTo and MailTo properties were at the same level as the rest of the parameters, but in the new database schema, it's an entirely new (and separate) table, with links to an existing table for the email address. It's different, all right. After a little messing around in SQSH, I came up with the following stored procedure that takes a set name and returns two columns - keys and values, and from this, I can easily feed a newly created Properties object and we're good to go.

  CREATE PROCEDURE sp_getProperties
      @setName VARCHAR(80)
  AS
  BEGIN
      -- get an answer table to hold the whole lot of it
      CREATE TABLE #answer (
          name    VARCHAR(256),
          VALUE   VARCHAR(4096)
      )
 
      -- get the list of the alertIDs that are in this set
      CREATE TABLE #interesting (
          alertID     INT
      )
      INSERT INTO #interesting
          SELECT DISTINCT a.alertID
            FROM Alerts a, ALertParams p
           WHERE p.setName=@setName
             AND a.alertID=p.alertID
             AND a.active=1
 
      -- get the list of all active, interesting alerts
      DECLARE @id INT,
              @val VARCHAR(4096)
      SELECT @val=''
      SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+a.name
        FROM Alerts a
       WHERE a.active=1
      -- ...and now that it's built, put it into the answer
      INSERT INTO #answer
          VALUES('Alerts', @val)
 
      -- run through them all, processing each in turn
      DECLARE actives cursor FOR
          SELECT alertID FROM #interesting
      OPEN actives
      fetch NEXT FROM actives INTO @id
      while @@fetch_status = 0
      BEGIN
          -- get the ClassName for this active guy
          INSERT INTO #answer
              SELECT 'Alert.'+a.name+'.ClassName' AS name, a.classname AS VALUE
                FROM Alerts a
               WHERE a.alertID=@id
 
          -- get the VersionID for this active guy
          INSERT INTO #answer
              SELECT 'Alert.'+a.name+'.VersionID' AS name, a.versionID AS VALUE
                FROM Alerts a
               WHERE a.alertID=@id
 
          -- get the bulk of the parameters for the alert...
          INSERT INTO #answer
              SELECT 'Alert.'+name+'.'+p.name AS name, p.value AS VALUE
                FROM Alerts a, AlertParams p
               WHERE a.alertID=@id
                 AND a.alertID=p.alertID
                 AND p.setName=@setName
 
          -- get the ChatTo list for this active guy
          SELECT @val=''
          SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+u.username
            FROM AlertRecv r, Users u
           WHERE r.alertID=@id
             AND r.username=u.username
             AND r.chat=1
             AND r.setName=@setName
          -- ...now that it's built, insert it into the answer
          IF @val<>''
              INSERT INTO #answer
                  SELECT 'Alert.'+a.name+'.ChatTo' AS name, @val AS VALUE
                    FROM Alerts a
                   WHERE a.alertID=@id
 
          -- get the MailTo list for this active guy
          SELECT @val=''
          SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+u.username
            FROM AlertRecv r, Users u
           WHERE r.alertID=@id
             AND r.username=u.username
             AND r.email=1
             AND r.setName=@setName
          -- ...now that it's built, insert it into the answer
          IF @val<>''
              INSERT INTO #answer
                  SELECT 'Alert.'+a.name+'.MailTo' AS name, @val AS VALUE
                    FROM Alerts a
                   WHERE a.alertID=@id
 
          fetch NEXT FROM actives INTO @id
      END
      close actives
      deallocate actives
 
      -- now return this to the caller
      SELECT name, VALUE FROM #answer
  END

Now while I'll be the first to admit that this is not the most efficient way of getting the data into a format that I can easily parse, it's pretty fool-proof. I wanted to make the Java parsing as simple as possible, and this does the job - a simple table with two columns - names and values to put in the map. Can't get any simpler than that.

I will say that some of the parts that I think are clever are the building of the strings that are really individual rows in the database - like the list of active alerts, or the list of ChatTo or MailTo recipients:

      SELECT @val=''
      SELECT @val=@val+CASE WHEN @val='' THEN '' ELSE ';' END+a.name
        FROM Alerts a
       WHERE a.active=1
      -- ...and now that it's built, put it into the answer
      INSERT INTO #answer
          VALUES('Alerts', @val)

We know that the select statement contains an implicit loop, and by using the case statement in the select, we get the delimiters in the right place - as opposed to an unnecessary trailing, or leading, one. I have to give credit for the use of the case to a co-worker. Pretty nice and concise.

With this, I'm able to finish the replacement of the Properties file with a database schema, and that's what I needed. In the coming days I need to make an editor in my web app, and then put a user-serviceable subscription page together so that the users can update what they get on their own. That will be a nice win for the web app.

This entry was posted on Friday, December 4th, 2009 at 3:45 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.