Project Euler solution # 1

I had an interest in doing some programming puzzles for the usual reasons: to practise and improve my logic skills, to explore new programming languages, and for simply to have...fun. The Python Challenge seemed appropriate when I started learning Python. I enjoyed doing the first few.

I then took a stab at one of the more popular puzzle websites, Project Euler (pronounced oil-er not you-ler which I incorrectly thought it to be.) This site not only encourages identifying efficient algorithms via your programming language of choice to solve the problems but strongly emphasizes math requiring more and more research as you advance to later problems.

Starting off with problem # 1:

Add all the natural numbers below one thousand that are multiples of 3 or 5.

Here is my first run, brute force attempt at the solution using Python:

divisors = [3, 5]
multiples = []
for i in range(1, 1000):
    del multiples[:]
    for divisor in divisors:
        if i % float(divisor) == 0:
            multiples.append(divisor)
    if multiples:
        print i,' is a multiple of ',', '.join([str(multiple) for multiple in multiples])

The earlier code solved the problem but, shortly after churning it out, I discovered that it did not offer the expected format of the answer. Therefore, re-worked the code to do so:

multiples = []
for i in range(1, 1000):
    if i % 3 == 0 or i % 5 == 0:
        multiples.append(i)

print sum(multiples)           

While this returns the correct value of 233168, the structure of the code could have some pythonic polish added to it. A dab of list comprehensions does the job:

print sum([x for x in range(1, 1000) if x % 3 == 0 or x % 5 ==0])

In the spirit of one reason why I'm doing these puzzles, here is a solution in C:

main()
{
  int sumOfMultiples = 0;
  int i;
  for(i = 1; i < 1000; i++)
    {
      if (i % 3 == 0 || i % 5 == 0){
          sumOfMultiples += i;         
      }
    }
  printf ("%i \n", sumOfMultiples);
}   

Speak to me...Interpreting C#

I came across A C# REPL (in Clojure) which discusses a means by which to interact with and run C# code via an interactive command-line by using Clojure and IKVM.NET . Now, Clojure I have heard of (a Lisp implementation that runs on the JVM) but this is my first time hearing about IKVM.NET (which I now know to be a .NET implementation for JVM). The post describes how combining these two technologies gives you the potential of working with a static language like C# in a way that is quite common in the world of dynamic languages such as Python, Ruby, Boo, etc.

Having an interactive code interpreter is a huge productivity boost. It allows you to easily run and test your code as you write and modify it while not needing to pay the dreaded compilation tax which can disrupt your development flow. This dramatically tightens and shortens the feedback loop on how well your code works ranging from whether it is behaving as intended for meeting spec requirements to much more quickly identifying any runtime bugs than you would using a development process common to traditionally compiled static languages. (As an aside, these are generally the same reasons that are given for creating and maintaining automated unit tests. Same goal but different methods.)

Not sure how well this C#/Clojure/IKVM.NET approach works or how well it realistically performs (typically, interpreted languages are slower). What is certain is that this unusual implementation requires the use of the very foreign-looking Lisp parentheses. I will openly admit as someone who does not program in Lisp it strikes me as kind of strange to use and see parentheses with C# but aside from this peculiar syntactical idiosyncrasy the general concept of REPL with C# overshadows even this oddity. This quote sums up its overall appeal in the world of C#:

A REPL is a Read-Eval-Print Loop, which is a fancy way of saying "an interactive programming command line". It's like the immediate window in the Visual Studio debugger on steroids, and its absence is one of the increasing number of things that makes C# painful to use as I gain proficiency in more advanced languages.

This is precisely how I felt when I initially started to learn and use Python. Suddenly, coding in C# with Visual Studio certainly seems to now be relatively more restrictive. Similarly, whenever I have had to touch any VBA code (yes, that does happen from time to time) I customarily inhabit the VB Editor's Immediate Window (IW) pushing its limits by attempting to use it in a manner that is similar to how I code in Python.

For example, in the VB language, not only is it not required to declare the data type of a variable but it is even unnecessary to explicitly declare the variables themselves (usually this is done using the 'Dim' keyword but this can be avoided by quite simply not including the 'Option Explicit' statement). Subsequently, the first time a value is assigned to a variable, the variable will automatically and implicitly be defined on the stack just like it does in Python. As a result, you can somewhat attain that same level of interaction with code in VB (via IW) as you would in Python (via its standard interpreter) potentially gaining the productivity benefits of writing less code in contrast to strongly typed languages.

My increased reliance of the Immediate Window also extends to Visual Studio when coding in C# but it requires more work and syntax overhead versus IW in the old VB Editor. Overall, it is not quite the same experience as in Python. Regardless, as I have previously written, frequent use of the VB Editor's IW led me to lean heavily on the one in Visual Studio whenever coding in C#. Prior to that, I had somewhat forgotten it even existed. In fact, in VS 2005, the IW sometimes is missing and difficult to view when not in debug mode (this is allegedly also true for VS 2008). This is discouraging as it probably contributes to most .NET developers not favoring its use in more situations.

While I have seen other attempts at providing an interactive console for C# the following are ones I have noted to possibly try out in the very near future:

• Interactive C# Shell
• CSI: A Simple C# Interpreter

I am extremely curious if (and hopeful that) Microsoft will provide an improved implementation for VS's IW when the more dynamic C# 4.0 becomes mainstream. (How long before we have an official C#Script? It worked for VB and VBScript.)

Is JavaScript the "Next Big Language"?

I recently read an old post of Steve Yegge from early 2007 entitled The Next Big Language. In it, he describes what are the fundamental characteristics that a new programming language must have if it is to become popular. Throughout his post, he hints at what he thinks the next big language ("NBL") might be but intentionally does not mention it by name for various reasons.

However, it seems to be quite evident that Yegge is probably referring to JavaScript (and\or the ECMAScript standard). A lot of his later posts seem to support that conjecture. One post in particular entitled Dynamic Languages Strike Back really does emphasize the strong possibility of JavaScript being the NBL:

"...So JavaScript. JavaScript has been really interesting to me lately, because JavaScript actually does care about performance. They're the first of the modern dynamic languages where performance has become an issue not just for the industry at large, but also increasingly for academia.

Why JavaScript? Well, it was Ajax. See, what happened was... Lemme tell ya how it was supposed to be. JavaScript was going away. It doesn't matter whether you were Sun or Microsoft or anybody, right? JavaScript was going away, and it was gonna get replaced with... heh. Whatever your favorite language was.

I mean, it wasn't actually the same for everybody. It might have been C#, it might have been Java, it might have been some new language, but it was going to be a modern language. A fast language. It was gonna be a scalable language, in the sense of large-scale engineering. Building desktop apps. That's the way it was gonna be.

The way it's really gonna be, is JavaScript is gonna become one of the smokin'-est fast languages out there. And I mean smokin' fast..."

Yegge has also mentioned using JavaScript server-side for some project he was working on at Google. Yes, server-side and not client side. If what he is saying is true and that the JavaScript language is continuing to evolve with more and more features transforming it into a powerful multi-paradigm language and programmers start using it not just client side but more on the server side as its performance becomes better and faster then it could really position itself as a first class language on par with C# and Java. Quite a leap from JavaScript's somewhat humble beginnings.

Obviously, Yegge is just one voice (albeit a very popular one) on the topic but the importance of JavaScript as a fundamental language for web apps is not to be taken lightly. This reminded me of a recent conversation about JavaScript with another software developer with whom I used to work. He had stated that he felt like he was currently doing more coding in the client-side/UI layer with JavaScript than he was coding in the middle layer with C# even though the middle tier is where he is expected to be doing most of his development. He also made the observation how the web application he is working on was starting to resemble more like a classic Client-Server application despite the presence of a middle tier.

The implication of this is that this type of architecture is one that more people might be gravitating towards when developing robust web applications without realizing it. Interesting that with the advent and surging popularity of web applications, the decline of desktop applications ("fat", "thick" clients) was greatly exaggerated. They are coming back just in an altered and less recognizable form.

The statements and experiences of the developer I mentioned earlier (combined with Yegge's writings) are extremely telling and is something to which we should pay attention. The increase use of JavaScript and more importantly how and where it's being used is a trend that should be closely watched in the ever changing world of modern software development.

Comprehending List Comprehensions

Since using Python to write build scripts (as well as for code generation) to support my development process I have come to increasingly learn and appreciate the power of the Python language. A recent coding situation demonstrated to me how understanding alternatives available in a multi-paradigm language such as Python can amplify the limitations of another language such as C# and can have a real influence on how you think about and write code.

Building working code first...

In my current project, the database for the system is maintained under source control. In the directory of my project's database files, a sub directory exists named 'CurrentReleaseOnly' which contains database unit tests written using the TSQLUnit framework. The purpose of this folder is to segregate out tests that are really just "one time only" with no intention to retain once the db schema changes are implemented in production [1]. In addition, the sub directory contains a plain old text file named 'README.TXT" which serves to explain why the folder exists to other developers working on the project.

Let's say in that folder, 'CurrentReleaseOnly', I have 4 files, three of which are unit test sprocs and one is that 'readme' file:

1. ut_uspVerifyDroppedColumns.sql
2. ut_uspVerifyDroppedTable.sql
3. ut_uspVerifyArchivedData.sql
4. README.TXT

Since the project files are maintained under Perforce (P4) [2], one of the project maintenance scripts needs to permanently delete all files within that folder from the source code repository with one exception of the aforementioned 'readme' file. In this example, that would imply deleting files # 1 through 3 but keeping # 4. Obviously, from one development cycle to another the number of files eligible for deletion would vary but only one would always be retained (i.e. # 4).

The command line syntax in P4 to open a file for delete is the following:

p4 delete file1.txt file2.txt file3.txt

The goal is to output and execute the above command which can be acheived with, the following was what was originally coded to achieve this action:

def open_for_delete_unit_tests_from_previous_release():
""" Open for delete in Perforce unit tests from previous release """

# find files to open for delete
exclude_file = 'README.TXT'
delete_files_dir = os.path.join(unit_test_dir, 'CurrentReleaseOnly')

# build delete command text
all_files = os.listdir(delete_files_dir)
for f in files:
 cmd = cmd + f + ' '

cmd = 'p4 delete ' + cmd

# execute 'open for delete' in source control
p4 = os.popen(cmd)
p4.read()
p4.close()

return True

Quite simply, a list object is first populated with the names of all the files in the directory. Then a loop through each file name is performed incrementally building the P4 command text. Eventually, the output for the command text should look like this:

p4 delete ut_uspVerifyDroppedColumns.sql README.TXT
ut_uspVerifyDroppedTable.sql ut_uspVerifyArchivedData.sql

However, as evident, it will also delete the 'readme' file which, if you recall, needs to remain to document the use of the directory. To make this happen, the following conditional statement was added to the loop:

...
for f in files:
 if f == exclude_file:
   continue                          
   cmd = cmd + f + ' '
        
cmd = 'p4 delete ' + cmd
...

As a result, the P4 output changes to now exclude the 'readme' file:

p4 delete ut_uspVerifyDroppedColumns.sql
ut_uspVerifyDroppedTable.sql ut_uspVerifyArchivedData.sql

We now have achieved our desired output and it actually works. All is good except...

Implementing List Comprehensions

Now, you are thinking "So what? What is the big deal? This is rudimentary programming that any four-year-old can do." Yes of course. However, I kept thinking that this was not very "Pythonic". Python is all about manipulating lists in an efficient and concise manner.

I immediately went back and re-read some more about list comprehensions. Armed with a better grasp of this style of programming, the code was altered to now implement this alternative way of building the same P4 command:

import os
...
def open_for_delete_unit_tests_from_previous_release():
""" Open for delete in Perforce unit tests from previous release """

# find files to open for delete
exclude_file = 'README.TXT'
delete_files_dir = os.path.join(unit_test_dir, 'CurrentReleaseOnly')

# build delete command text
all_files = os.listdir(delete_files_dir)
delete_files = [(delete_files_dir + os.sep + f) for f in all_files \
 if f != exclude_file]
cmd = 'p4 delete ' + ' '.join(delete_files)

# open for delete in source control
p4 = os.popen(cmd)
p4.read()
p4.close()

return True

By using Python's implementation of list comprehension, it allowed the building of a new list by filtering out the unneeded items based on some defined criteria. After the new filtered list is created the final p4 command text is generated using the join method of a single space (" ") string.

List comprehensions also support not just filtering but also repeatedly applying the same function against each item in a list. This is considered a variation and more shorthand way of implementing the "map" function commonly found in functional languages. Python does indeed explicitly support the classic functional programming functions of 'map', 'reduce', and 'filter' but it's list comprehensions are an even more "concise" way of implementing map and filter[3].

If you were not impressed with the previous filtering example then here is another more trivial example of applying a 'map' function using list comprehensions. This time the file extension is stripped out from each file name contained within a given list [4]:

>> files = ['ut_uspVerifyDroppedColumns.sql',  'ut_uspVerifyDroppedTable.sql',
'ut_uspVerifyArchivedData.sql', 'README.TXT']
>> print [f[:-4] for f in files] # remove file extension using list comprehensions
['ut_uspVerifyDroppedColumns', 'ut_uspVerifyDroppedTable',
'ut_uspVerifyArchivedData', 'README']

Comparisons to SQL

What really struck me about list comprehensions is how much it reminded me of the ubiquitous database language, SQL. Given my long experience with querying and data manipulation against sql databases I found Python's use and style of list comprehensions to be a much more interesting and maybe even more powerful. Shortly after noting the similarities I subsequently read that list comprehensions were even considered for database querying:

"Comprehensions were proposed as a query notation for databases and were implemented in the Kleisli database query language"

I plan to write a much longer post on my opinions regarding the future of SQL as a language but until then I will say the following. LINQ is a great attempt to bake into the C# language actual data querying features but with one major flaw. It still adopted the SQL syntax in the process which really does need its own makeover (or better yet a replacement).

I'm sure the main reason for Microsoft's decision to closely model LINQ after SQL was to give .NET developers something they were already deeply familiar with and thereby more apt to use it. However, if Microsoft had perhaps used something resembling list comprehensions instead of SQLish syntax it might have made C# an even more powerful language by baking a more initutive and compact syntax [5]
...

[1] If you are someone who is a TDD practitioner (like myself) you might be shouting: "How can you be throwing away unit tests after writing them? That is insanity and completely violates the the very essence of TDD!!!!". Yes, but just like any other methodology, the principles of TDD should not always be followed blindly and adhered to strictly. Sometimes, exceptions have to be made.

In this particular instance, the reasons for dropping certain unit tests after a period of time were primarily due to performance. Since tests against a database tend to be slower than more traditional unit tests found in app code, I decided that after each production release any tests that are no longer valuable beyond the next release would be purged from the project's code base.

For example, unit tests that are used to test such things as "one time only" migration of data from one database to another or tests that simplistically check for the existence (or, conversely, the non-existence) of db objects like columns or tables are good candidates for permanent removal from the suite of db tests. On the other hand, unit tests that assert and validate some complicated logic in stored procedures as an example would be kept and not be removed. Regardless, this is not point of this blog post so I digress...

[2] Admittedly, I would rather be using the significantly less intrusive Subversion.

[3] For some reason, the equivalent of 'reduce' is not supported by Python's list comprehensions. Perhaps it is because the creator of Python was not a fan of map/reduce/filter since the time of its inclusion into the Python language. He especially seems to have a dislike for 'reduce'.

[4] For improved readability, I could have defined a separate function stating more explicitly what exactly it was doing without resorting to using comments (which tend to be a 'code smell'):

def remove_file_extension(f): return f[:-4]
print [remove_file_extension(f) for f in files]

Or I could have used a lambda function for equal effect:

remove_file_extension = lambda f : f[:-4]
print [remove_file_extension(f) for f in files]

[5] Actually, as I recently discovered C# does support map, reduce, and filter as of version 3.0. (respectively, "Enumerable.Select", "Enumerable.Aggregate", and "Enumerable.Where") Not quite list comprehensions but definitely a huge lift for the language. In addition, my understanding is that F# being a functional language does support list comprehensions beyond the standard map/reduce/filter.

Snake bitten by Python (R.I.P. NAnt)

After first experimenting last year with IronPython, the .NET port of Python, I decided to take the full plunge into Python itself leading to another major milestone in my programming career. Why? Because I now have an incredibly handy language, Python, that can superbly manage rudimentary but necessary development tasks. Furthermore, Python has enlightened me to yet another way of thinking about how code can be written.

As a dynamic language, Python can be extremely powerful. It can be used for "glue" tasks like scripting but its potential is even greater. By being an interpreted language, pieces of your code can both be written and tested practically at the same time via its interpreter console (as if the Immediate Window in Visual Studio were the means to simultaneously see how your code works while you are writing it. No "compilation tax".) In addition, Python can do both OOP (e.g. classes, et al) as well as functional programming (e.g. treating functions like data in lists and supporting lambdas like Lisp). Finally, with its leaner and less verbose syntax, less code is written as compared with other static languages.

After a few days to ramp up and get acquainted with the language, I immediately started to implement Python on a few things. NAnt build scripts and Windows bat files were the main targets for Python conversion. I also intend on rewriting in Python a C# .NET console tool that merges the content of multiple files into a single one. It seemed more natural and sensible to use Python for these types of development tasks.

Replacing NAnt with Python is favored since NAnt is an XML-based DSL that might be doing a little too much. The problem is not that it is a DSL, generally a good thing particularly when done right (as the Ant/NAnt folks succeeded quite well in doing to their credit), but the part of it being "XML-based". Who really wants to program all day in XML? Apache Ant, NAnt in the Java world, was a victim of the exploding popularity of XML during the height of the dot com era web applications. XML should be left to do what it does best and what it was originally intended for: basic structured data storage and configuration. Ant (and, subsequently, NAnt) should not have mixed the following two: (1) formatting/organizing data and (2) build flow logic. Not a far cry from violating the principle "separation of concerns".

If I can avoid it, I am finished with NAnt (or any other equivalent build frameworks that rely heavily on XML for its flow logic). If given the choice in a development environment, I probably not opt to use NAnt to handle build scripts. Not that I have anything against NAnt itself, just that better, more programmer friendly alternatives exist. NAnt was (and still is) a great option as compared, say, with the inferior Windows bat files or with the dev shops that manually build their projects via the Visual Studio. Instead, a dynamic language like Python (or BOO or Ruby or whatever else) is preferable to manage this type of work. (A few build automation frameworks do exist written in Python, but I will like to take a look at the promising, .NET born BOO build system.)

NAnt documentation mentions that it has the advantage over native OS shell commands because it is "cross-platform". That might be true, but Python has that area easily covered specifically with its 'os' and 'shutil' modules. Portability is one of Python's key features.

Code generation of other programming languages is another area where I also started to use Python. Database change scripts written in TSQL that are repetitive and voluminous have benefited significantly from using Python (as one example, creating structurally similar 'drop column' statements for 100+ columns). In the future, for other kinds of code generation (e.g. NHibernate mapping files is one example), I will definitely consider Python as a substitute for heavier code-gen tools such as MyGeneration.

The more I use Python, the more I am convinced that it will be employed as my general, all-purpose utility programming language. I intend to use it as a vital supporting player handling the grunt work in my development processes. It does not matter what the primary language happens to be whether C#, TSQL, etc. By and large, I just like how quick and dirty a script can be whipped up to perform some auxiliary task without having to endure the overhead of compiling, creating, and running some executable file. (Who knows? Maybe one day I can work on a major project where Python is the star of the show.) All in all, it is just such a nice clean, readable language making it far more enjoyable to work with as compared with something like NAnt.

To give an idea on how visually different it is to use Python over NAnt, below are the code of two identical build scripts written in each language. This the first script I ported over to Python. The script runs the SQL Server Database Publishing Wizard to generate a file that contains the sql to create the schema of a baseline database required at the start of each development cycle. The following high level tasks are executed by the script:

• Create Schema Script- Generates the raw initial tsql schema script from target database using the DB pub wiz
• Convert Script File Encoding- Convert file from Unicode to ASCII
• Replace Script Values- Read from external csv file containing pairs of strings to replace in script.
• Checkout File From Source Control - Checkout from Perforce the existing schema file that will be replaced.
• Copy File To Build Location- Move sql script file to build directory
• Build Database And Run Unit Tests- Run another separate script (currently written in NAnt) that builds the db and runs the unit tests using TSQLUnit
  

NAnt Version

<?xml version="1.0"?>
<project name="Generic Database Build" default="BaselineDatabaseCreation"
        xmlns="http://nant.sf.net/release/0.85/nant.xsd">
 <property name="base.dir" value=".\" overwrite="false" readonly ="false" />
 <property name="sourceDB" value="" overwrite="false"/>
 <property name="sourceServer" value=".\sqlDev2005" overwrite="false"/>
 <property name="dbmsVersion" value="2000" overwrite="false"/>
 <property name="connectionString" value="Server=${sourceServer};Database=${sourceDB};Trusted_Connection=True;"/>
 <property name="dbBuild.dir" value="" overwrite="false"/>
 <property name="targetDB" value="" overwrite="false"/>
 <property name="targetServer" value=".\sqlDev2005" overwrite="false"/>
 <property name="sqlScriptingTool.dir" value="C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\" overwrite="false"/>
 <property name="sqlScript.fileName" value="CreateSchema.sql" overwrite="false"/>
 <property name="sqlScript.filePath" value="${path::combine(base.dir, sqlScript.fileName)}" overwrite="false"/>
 <property name="sourceControl.filePath" value="${dbBuild.dir}Schema\${sqlScript.fileName}" overwrite="false"/>
 <!-- replace values list variable -->
 <property name="temp.fileName" value="temp.txt"/>
 <property name="temp.filePath" value="${path::combine(base.dir, temp.fileName)}"/>
 <property name="replaceValuesList.fileName" value="" overwrite="false"/>
 <property name="replaceValuesList.filePath" value="${path::combine(base.dir, replaceValuesList.fileName)}"/>

 <target name="BaselineDatabaseCreation" description="Creates baseline database tsql script end-to-end.">
   <call target="CreateSchemaScript"/>
   <call target="ConvertScriptFileEncoding"/>
   <call target="ReplaceScriptValues" unless="${replaceValuesList.fileName==''}"/>
   <call target="CheckoutFileFromSourceControl"/>
   <call target="CopyNewScriptFileToBuildLocation"/>
   <call target="GetSeedTablesData"/>
   <call target="BuildDatabaseAndRunUnitTests" unless="${targetDB==''}"/>
 </target>
 <target name="CreateSchemaScript" description="Generates the raw initial tsql schema script from target database">
   <delete file="${sqlScript.filePath}" if="${file::exists(sqlScript.filePath)}" />
   <exec program="${sqlScriptingTool.dir}sqlpubwiz">
     <arg value="script" />
     <arg line="-C ${connectionString}" />
     <arg value="${sqlScript.filePath}" />
     <arg value="-schemaonly" />
     <arg line="-targetserver ${dbmsVersion}" />
    <!-- '-f' means overwrite existing files is true -->
     <arg value="-f" />
   </exec>
   <fail message="${sqlScript.filePath} was not created."
     unless="${file::exists(sqlScript.filePath)}" />
 </target>
 <target name="ConvertScriptFileEncoding" description="Convert file from Unicode to ASCII">
   <copy file="${sqlScript.filePath}" tofile="${temp.filePath}" outputencoding="ASCII" overwrite="true" />
   <move file="${temp.filePath}" tofile="${sqlScript.filePath}" overwrite="true"
         unless="${file::exists(replaceValuesList.filePath)}" /> 
 </target>
 <target name="ReplaceScriptValues"
         description="Read from external csv file containing pairs of strings to replace.">
   <fail message="${replaceValuesList.filePath} does not exist."
     unless="${file::exists(replaceValuesList.filePath)}" />
   <foreach item="Line" in="${replaceValuesList.filePath}" delim="," property="x,y">
     <echo message="Replacing '${x}' with '${y}'..." />
     <copy file="${temp.filePath}" tofile="${sqlScript.filePath}" overwrite="true">
       <filterchain>
         <replacestring from="${x}" to="${y}" />
       </filterchain>
     </copy>
     <copy file="${sqlScript.filePath}" tofile="${temp.filePath}" overwrite="true"/>
   </foreach>
   <move file="${temp.filePath}" tofile="${sqlScript.filePath}" overwrite="true"/>
 </target>
 <target name="CheckoutFileFromSourceControl" description="Checkout from source control the schema file that will be replaced.">
   <fail message="${sourceControl.filePath} does not exist."
     unless="${file::exists(sourceControl.filePath)}" />
   <p4edit view="${sourceControl.filePath}">
     <arg line="-t"/>
     <arg line="text+k"/>
   </p4edit>
 </target>
 <target name="CopyNewScriptFileToBuildLocation">
   <copy file="${sqlScript.filePath}" tofile="${sourceControl.filePath}" overwrite="true" />
 </target>
 <target name="GetSeedTablesData">
   <!--TODO: Create a separate NAnt build script for this-->
 </target>
 <target name="BuildDatabaseAndRunUnitTests">
   <nant buildfile="GenericDatabase.build" inheritall="false" >
     <properties>
       <property name="base.dir" value="${dbBuild.dir}"/>
       <property name="server"   value="${targetServer}" />
       <property name="database" value="${targetDB}"/>
       <property name="includeUnitTesting" value="true" />
     </properties>
   </nant>
 </target>
</project>

Python Version

import os
import csv
import shutil

sqlscripting_tool=r'C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe'
dbms_version = '2000'
connection_string = 'Server=' + source_server + ';Database=' + source_db + ';Trusted_Connection=True;'

sqlscript_filename = 'CreateSchema.sql'
sqlscript_filepath = os.path.join(sqlscript_dir, sqlscript_filename)
source_control_filepath = os.path.join(db_build_dir, sqlscript_filename)

replace_values_filepath = os.path.join(replace_values_dir, 'ReplaceList.csv')
error_found_message = 'Error found!'

def run_script():
   """Run all tasks"""
 
   tasks = [create_schema_script, convert_scriptfile_encoding, replace_script_values,
                checkout_file_from_source_control, copy_file_to_build_location, build_database_and_run_unit_tests]
   for task in tasks:
       print 'Executing \'' + task.func_name + '\'... '
       is_successful = task()
       if not is_successful:
           print 'Script Failure!'
           break
             
   if is_successful:
       print 'Script Success!'
     
def create_schema_script():
   """Generates the raw initial tsql schema script from target database"""
 
   if os.path.isfile(sqlscript_filepath):
       os.remove(sqlscript_filepath)

   args = ['sqlpubwiz', 'script', '-C ' + connection_string, '"' + sqlscript_filepath + '"',
               '-schemaonly', '-targetserver ' + dbms_version, '-f']
   os.spawnv(os.P_WAIT, sqlscripting_tool, args)

   if os.path.isfile(sqlscript_filepath) == False:
       print error_found_message
       print "File '" + sqlscript_filepath + "' was not created."
       return False

   return True

def convert_scriptfile_encoding():
       """Convert file from Unicode to ASCII"""    

       cmd1 = 'type "' + sqlscript_filepath + '" > temp.txt'
       cmd2 = 'move temp.txt "' + sqlscript_filepath + '"'
       cmds = [cmd1, cmd2]      
       for cmd in cmds:
               dos = os.popen(cmd)              
               dos.read()
       dos.close()

       return True

def replace_script_values():
       """ Read from external csv file containing pairs of strings to replace values in sql script. """

       # if 'replace values' list not provided then assume not needed
       if replace_values_filepath == '':
               return False

       # check for 'replace values' file existence      
       if os.path.isfile(replace_values_filepath) == False:
               print error_found_message
               print "Replace values list file '" + replace_values_filepath + "' does not exist."
               return False

       # modify file content with new values
       f = open(sqlscript_filepath, 'r')      
       text = f.read()
       replace_values = csv.reader(open(replace_values_filepath, 'r'))
       for row in replace_values:
           find_text = row[0]
           replace_with_text = row[1]
           text = text.replace(find_text, replace_with_text)
       f.close()

       # write to script file with new values
       f = open(sqlscript_filepath, 'w')
       f.write(text)
       f.close()

       return True

def checkout_file_from_source_control():
       """ Checkout from source control the schema file that will be replaced. """
     
       # look for source control file      
       if os.path.isfile(source_control_filepath) == False:
               print error_found_message
               print "Source control file '" + source_control_filepath + "' does not exist."
               return False

       # checkout file    (note: could use PyPerforce API framework instead)
       cmd = 'p4 edit -t text+k ' + source_control_filepath + ''
       p4 = os.popen(cmd)
       p4.read()
       p4.close()

       return True

def copy_file_to_build_location():
       """ Move sql script file to build directory """

       shutil.copy(sqlscript_filename, source_control_filepath)

       return True

def build_database_and_run_unit_tests():
       """ Build database and validate schema by running unit tests """

       nant_tool = os.path.join(base_dir, 'Tools\\NAnt\\bin\\', 'NAnt.exe')
       build_script_filepath = os.path.join(base_dir, 'Projects\\Libs\\Utils\\NAntScripts\\DatabaseBuilds\\', 'GenericDatabase.build')
       build_dir = os.path.split(os.path.normpath(db_build_dir))[0] # hack: need to remove 'Schema' folder; todo: need to remove this from generic db build script
     
       # todo: replace NAnt script with Python script
       args = ['NAnt', '-buildfile:' + build_script_filepath, '-D:base.dir=' + build_dir,'-D:server=' + target_server,
               '-D:database=' + target_db, '-D:installUnitTesting=' + 'true', ]
       os.spawnv(os.P_WAIT, nant_tool, args)
   
       return True
     
run_script()