March 21, 2010

at Sunday, March 21, 2010 Labels: Posted by Billy

SQL Prompt is a great add-on tool for SQL Server Management Studio that I use at work extensively. One of the features it has is "Snippets", which allow you to type a piece of code that expands into other code. For example, typing "ssf" and hitting Tab expands into "SELECT * FROM".

Users can customize snippets, which are saved in a file and can be shared with others (more info here). The database schema I use at work contains hundreds of tables, each named accoring to their abbreviated primary keys. Since some names are very long and cumbersome to type even with SQL Prompt, I created a Lisp script to auto generate snippets for each of the tables.

For example, lets say our database models baseball statistics. We have tables for players and teams. Since we want to tract statistics for multiple years, we have a year table and tables relating the three. So, our very simple database may look like this:

- Teams
- Players
- Years
- PlayersYears
- TeamsYears
- PlayersTeamsYears

My simple Lisp program created snippets for each of the tables, expanding "pty" to "PlayersTeamsYears" and such for each of the tables.

After saving my new SQL Prompt snippets file and starting Management Studio, SQL Prompt generated a run-time error because I had duplicate shortcut keys. Unfortuately, the error message didn't say which keys were duplicates, so I used the s-xml package and parsed the SQL Prompt file looking for duplicate shortcuts.

s-xml works by parsing the xml file sequentially. For each new element, it calls a new-element hook function, and for each new piece of text it calls a new-text hook. The new-element hook takes as pararmenters the name of the element and the attributes. The new-text hook takes the string of text. Each hook also accepts a seed element, which can be any object and is passed along while parsing the file.

I wanted to find duplicate shortcuts, so I decided to keep a hash-table with shortcuts as the key and the number of occurances as values. The shortcuts live inside of the "Shortcut" element, so my seed also needed to know the name of current element it was working on. I created the following class for my seed:

(defclass dup-shortcut-seed ()
  ((name :accessor name)
   (hash :initform (make-hash-table :test 'equalp) :accessor hash)))

I created a method to easily increment the class's hash table:
(defgeneric inc-hash (dup-shortcut-seed key))

(defmethod inc-hash ((dup-shortcut-seed dup-shortcut-seed) key)
  (incf (gethash key (hash dup-shortcut-seed) 0)))

Now that I have my seed, I created the element and text hooks that use the seed to keep track of duplicate shortcuts:
(defun dup-shortcut-xml-new-element-hook (name attributes seed)
  (declare (ignore attributes))
  (let ((name (print-xml-string name)))
    (setf (name seed) name)
    (format t "New element: ~s~%" name))

(defun dup-shortcut-xml-text-hook (string seed)
  (when (equalp (name seed) "Shortcut")
    (format t "Modifying hash~%")
    (inc-hash seed string))

Next I created a function to call the parser on a stream:
(defun dup-shortcut-xml (in)
   (make-instance 'xml-parser-state
    :seed (make-instance 'dup-shortcut-seed) 
    :new-element-hook #'dup-shortcut-xml-new-element-hook
    :text-hook #'dup-shortcut-xml-text-hook)))

Finally, I created the main function, which parses the xml file and diplays any duplicate shortcuts:
(defun find-dup-shortcuts-in-xml (&optional (file *output-file*))
  (with-open-file (in file)
    (let ((hash (hash (dup-shortcut-xml in))))
      (maphash (lambda (key value)
   (when (> value 1)
     (format t "~s:~s" key value)))

After finding the shortcuts, I modified my snippet file and it worked beautifully!