Search

Monday, November 03, 2008

Using NAnt to Traverse Directories and Execute SQL

In the latest version of Ensemble Video I had a need to migrate the old mechanism of showing content to new way that involved a single page and some URL rewriting (more on that another time). The basics of the old mechanism were that for each publishing point used by the application, a separate directory with specific “index.aspx” was created. That was always a major pain when updates had to be applied since I always had to preserve the old files but still update existing content. Long story short, I got away from that but I still needed to figure out what publishing points were in use so I can set the database flag for the new mechanism. So, the NAnt fanatic that I am, I build a NAnt script to do that. In this example, you will learn:

  1. How to parse XML for a certain value
  2. How to traverse directories with NAnt
  3. How to execute an “if” statement in NAnt
  4. How to get file and directory names using the NAnt built-in functions
  5. How to execute SQL script with SQL task from NAntContrib

The prerequisites are that 1). You have NAnt installed and 2). You have NAntContrib installed.

So let’s go one by one from the list above and you will see the final script at the end.

  1. How to parse XML for certain value
    <xmlpeek
    file="${path::combine(path::get-full-path(webApplicationDirectory), 'config\connectionStrings.config')}"
    xpath="/connectionStrings/add[@name = 'ensembleVideoConnection']/@connectionString"
    property="connectionString" />

    This parses the XML file under webApplicationDirectory\config\connectionStrings.config and uses an XPath expression to get the property of the connectionString with the name ensembleVideoConnection. The value is stored in the NAnt property "connectionString"
  2. How to traverse directories with NAnt
    <foreach item="Folder" in="${sitesDirectory}" property="orgDirectoryName">
     <echo message="${orgDirectoryName}" />
    </foreach>

    This task traverses through the directories inside the directory specified by "sitesDirectory", stores the current directory in orgDirectoryName and then echoes the current directory.
  3. How to execute an “if” statement in NAnt
    <if test="${string::to-lower(path::get-file-name(orgDirectoryName)) != '_svn'}">
     <echo message="${path::get-file-name(orgDirectoryName)}" />
    </if>

    Here we test if the current directory name is equal to "_svn". Here is another example where we test if the current directory contains a desired file:
    <if test="${file::exists(path::combine(webSiteDirectoryName, 'index.aspx'))}">
     <property name="enableQuickPublish" value="1" />
    </if>

  4. How to get file and directory names using the NAnt built-in functions
    <-- Get only the directory name -->
    path::get-file-name(orgDirectoryName)
    <-- Get the directory name from the path -->
    path::get-directory-name(orgDirectoryName)
    <-- Create a full path based on the directory name and a file name -->
    path::combine(webSiteDirectoryName, 'index.aspx')

    You can find out more about NAnt built-in functions at NAnt Functions
  5. How to execute SQL script with SQL task from NAntContrib
    <sql
      connstring="Provider=SQLOLEDB;${connectionString}"
      transaction="true"
      delimiter=";"
      delimstyle="Normal">
     update dbo.webSites
     set isQuickPublished = ${enableQuickPublish}
     where webSiteID = (
       select webSiteID
       from dbo.listWebSites
       where webSafeOrganizationName = '${path::get-file-name(orgDirectoryName)}'
        and webSafeDepartmentName = '${path::get-file-name(departmentDirectoryName)}'
        and webSafeWebSiteName = '${path::get-file-name(webSiteDirectoryName)}'
      );
    </sql>

    There is not much to the SQL statement execution. As you can see you can use NAnt properties and function inside the SQL script. You can check out all of NAntContrib's tasks at NAntContrib Tasks

As promised here is the full contents of the build script:

<project name="renameSqlToText" default="run" xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
 <!-- Set the path to the web application -->
 <property name="webApplicationDirectory" value="D:\_Boyan's Documents\_Projects\_ensembleVideo\trunk\webApplication\app" />

 <!-- Set the name of the sites directory (under the applicaiton directory) -->
 <property name="sitesDirectoryName" value="sites" />

 <property name="sitesDirectory" value="${path::combine(path::get-full-path(webApplicationDirectory), sitesDirectoryName)}" overwrite="true" />

 <target name="run">
  <!-- Get the database connection string from the web application -->
  <xmlpeek
   file="${path::combine(path::get-full-path(webApplicationDirectory), 'config\connectionStrings.config')}"
   xpath="/connectionStrings/add[@name = 'ensembleVideoConnection']/@connectionString"
   property="connectionString" />

  <!-- For each directory in the sitesDirectory -->
  <foreach item="Folder" in="${sitesDirectory}" property="orgDirectoryName">
   <if test="${string::to-lower(path::get-file-name(orgDirectoryName)) != '_svn'}">
    <echo message="${path::get-file-name(orgDirectoryName)}" />

    <foreach item="Folder" in="${orgDirectoryName}" property="departmentDirectoryName">
     <echo message="  ${path::get-file-name(departmentDirectoryName)}" />

     <foreach item="Folder" in="${departmentDirectoryName}" property="webSiteDirectoryName">
      <echo message="    ${path::get-file-name(webSiteDirectoryName)}" />

      <!-- Set the 'enableQuickPublish' property to false -->
      <property name="enableQuickPublish" value="0" />

      <!-- If a index.aspx file exists, set the 'enableQuickPublish' property to true -->
      <if test="${file::exists(path::combine(webSiteDirectoryName, 'index.aspx'))}">
       <property name="enableQuickPublish" value="1" />
      </if>

      <!-- Run a database query to update the flag in the database -->
            <sql
              connstring="Provider=SQLOLEDB;${connectionString}"
              transaction="true"
              delimiter=";"
              delimstyle="Normal">
       update dbo.webSites
       set isQuickPublished = ${enableQuickPublish}
       where webSiteID = (
         select webSiteID
         from  dbo.listWebSites
         where  webSafeOrganizationName = '${path::get-file-name(orgDirectoryName)}'
           and
           webSafeDepartmentName = '${path::get-file-name(departmentDirectoryName)}'
           and
           webSafeWebSiteName = '${path::get-file-name(webSiteDirectoryName)}'
         );
           </sql>
     </foreach>
    </foreach>
   </if>
  </foreach>
 </target>
</project>

Downloads

http://tech-cats.net/blog/nantScripts/traverseDirectoriesAndExecuteSQL.build

// //]]>