How To Setup hMailServer To Use a SSL Certificate

I am adding IMAP support to one of our products. Likely more that one person out there a needed to do this, so enjoy. I’ll take you from creating an SSL certificate to configuring hMailServer to work with both secure and regular connections to testing your setup.

Creating a Self Signed SSL Certificate

First things first you’ll need to download OpenSSL. I downloaded the 64bit 1.0 light version which required Visual C++ 2008 Redistributables (x64) to be installed first. I told the installer to put OpenSSL in my c:\utilites\ folder.

Create a Key

Next up you’ll need to create a key. I recommend you replace <host> with your machine name.

>openssl genrsa -out <host>.key 1024

Certificate Request

Now you need to create a certificate request. This is the file you normally send off to your SSL certificate provider.

>openssl req -new -nodes -key <host>.key -out <host>.csr

Oops if you’re like me here you’ll get this error:

Unable to load config info from C:/lan/ssl/openssl.cnf

Looks like the default config location is not cutting it. You can clue OpenSSL in with an environment variable:

SET OPENSSL_CONF=c:\utilities\OpenSSL-Win64\bin\openssl.cfg

Let’s try that again…

>openssl req -new -nodes -key <host>.key -out <host>.csr

Answer the questions the best you can.

For the Common Name you should put the fully qualified domain name of the server where you will deploy the certificate.

Self Signing the Certificate Request

If you are like me this certificate is for development and you won’t really need a certificate authority to sign it for testing purposes. Luckily it is easy to generate your own certificate.

>openssl x509 -req -days 1024 -in <host>.csr -signkey <host>.key -out <host>.cert

Configuring hMailServer With Your New SSL Certificate

Bring up your hMailServer administrator UI and add a SSL certificate

Next create new TCP/IP ports which use SSL for each protocol you are interested in testing.

Notice the port numbers I use for IMAP (993) POP3 (995) seem to be the default secure ports for these two protocols. Each change will restart hMailServer.

Test Your Secure Connection

Go ahead and fire up your favorite email client. I use Windows Live Mail which I’ve configured to pull email from one of the test accounts I’ve setup on hMailServer.

And now for a test message or two?

I’ve setup a few pop3 accounts and an IMAP and had a lot of fun sending my virtual test personas emails. The dialog is not so riveting.

You’ve Got SSL

There you go. Hopefully you didn’t run into anything weird that I did not.  This post is basically a roll up of the hMailServer SSL certificate documentation. And Generating Certificate Signing Requests from Jeremy Mate

How To Configure Log4J via C#

One of our products is currently using IKVM to interop with a java library. I kept seeing console warnings saying:

log4j:WARN Please initialize the log4j system properly.

Clearly the java library uses log4j and it is not getting initialized by my application. A customer was rightfully confused by this message so I spent a little time getting log4j setup and working.

At first I was hoping I could host log4j configuration XML along side my log4net configuration but sadly that was a pipedream. I opted for this approach.

public const string Log4JConfigFileName = "log4j.config";

private static void ConfigureLog4J()
{
    if(File.Exists(Log4JConfigFileName))
    {
        PropertyConfigurator.configureAndWatch(Log4JConfigFileName);
        return;
    }

    var patternLayout = new PatternLayout("%d [%t] %-5p %c - %m%n");
    var consoleAppender = new ConsoleAppender(patternLayout);
    var fileAppender = new RollingFileAppender(patternLayout, "./logs/file-extraction-errors.log", true);
    fileAppender.setMaxFileSize("1MB");
    fileAppender.setMaxBackupIndex(5);
        
    var rootLogger = Logger.getRootLogger();
    rootLogger.setLevel(Level.ERROR);
    rootLogger.addAppender(fileAppender);
    rootLogger.addAppender(consoleAppender);
}

Look for an existing log4j.config log file use that when it is present. Otherwise configure a console and a file appender and log only Errors to a particular file. The manual a lot.

Our log4j usage is pretty vertical so I can get away with hard coding the defaults and rely on throwing a log4j configuration file into the application directory when we need better control.

Hope this post helps if you ever have the pleasure of interop with log4j.

Seeker Indexer Architecture Changes

Great changes are happening with our Dovetail Seeker search product. I wanted to talk about a core change to the product. By moving our application architecture to use messaging we are seeing increased flexibility, testability and performance.

Why Messaging?

Based on our experience building Dovetail Carrier (pdf), our messaging oriented enterprise integration solution, we decided messaging was a great fit for Seeker’s search indexing windows service.

We decomposed the indexer’s behaviors into work (message) producers and consumers. For example to keep your database in-sync with the search index we have a service which watches the database for changes to the objects Seeker is indexing. When a database item is added or updated a message is created which gets put onto a work queue.

We have many message consumer threads standing by waiting for work to munch on. Our message consumers read like our feature set:

• UpdateClarifyDocument – indexes your Clarify objects.
• UpdateClarifyAttachment – indexes the file attachments on your Clairfy objects.
• UpdateFileDocument – indexes files

Each message is basically a feature. You’ll may notice may we’ve added indexing of files and attachments for clarify objects, but that’s another blog post. This new architecture makes it easier for us to plug in and try out new functionality.

What is the down side?

We are taking a dependency on Microsoft Message Queue. Which means there are more moving parts to keep track of.

A message oriented system is a bit tougher to debug as you have to get used to the parallel nature the messages can exhibit. More than once message can be consumed at a time which means we have to be careful to avoid introducing side-effects into our code.

Added complexity in managing the Lucene index writer. Only one writer is allowed to modify the index at a time so when multiple messages are running simultaneously we have to be careful.

Stopping the indexer windows service under heavy load can take a while because all the consumers have to finish their workload before completing.

Wow that is a lot of down side. Why is this worth it?

But there is a sunny upside.

I already mentioned that it is easier to plug in new functionality. We learned on Dovetail Carrier how to make it super easy to extend a message based system. We leveraged that knowledge in Seeker.

Performance. We are now getting parallelism in our message processing. Before our search indexer would chug through one clarify object at a time. Get it from the database. Update the index. Repeat. Now we have multiple consumer all working together on discrete pieces of the indexing puzzle. Although, there is a bit of overhead due to all the message passing going on.

Let’s talk more about performance.

Unscientific Back of the Napkin Benchmark Using Paper and Pencil

This is not very accurate or scientific benchmark. Your mileage may vary depending on your setup and such but on my development bench running in the background while I am failing to get other work done. That said. I like what what I see.

The Indexer – Where seeker gets installed.

My development machine is not stellar but pretty sweet. 4 cores, 8 cores with hyper threading, 8GB memory.

I installed Dovetail Seeker on my dev machine and pointed the database settings at a sample database where we have a decent sized set of data.

The Mark – The database under test.

The sample SQL Server real world database has over one million cases and a decent number of contacts and solutions. The machine is a low end (4 year old) server. The connection is a 100MB network connection with 2 hops.

I have attachment and file indexing turned off to make the comparison against the previous version of Seeker more apples to apples. Here is a snapshot of the number of objects we are dealing with.

That is 1.06 million database objects to index.

Indexing Run Results

I did 3 runs. The first with Seeker 1.5.1 out of the box. The last two runs are Seeker 2.0 with different numbers of consumer threads.

Carry the one. Dot the i... The new Seeker is seeing a 225 to 286% increase in indexing performance. I am pretty happy with ~1.7 million database objects per hour.

Consumers

An interesting detail is the significant boost in performance due to adding consumers to the run. Our Message Bus recommends 2 threads per processor and clearly that is good advice.

The problem is that under load when you tell the windows service to shutdown it takes a long time for all the consumer threads to finish up. Thankfully this is an edge case and we are looking at ways to improve this part of the user experience. For now if you need to control the number of consumers to improve the timeliness of shutdown there is a configuration setting for that.

Conclusion

We are really excited to get these improvements into the hands of our customers. Some of our customers have a very large number of objects being indexed and can experience multi-day indexing times. Seeker 2.0 is no silver bullet to making your indexing run go faster but every bit really helps.

AspNetHostingPermission exception got you down? Unblock Your Assemblies.

We ran into trouble with a web applications deployment of our only .Net product without a windows installer. Little did we know when you zip up a web application, have your on-site expert consultant download said zip file and extract it with Windows Explorer (Note: it does not happen with WinRar or 7zip) You will run into this exception trying spin up that web application.

Security Exception

Description: The application attempted to perform an operation not allowed by the security policy.  To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

You have happened into another in my series of blog posts where I relay some wisdom that has already been well covered with the subversive desire to never forget the answer in hopes to not waste the time of myself and others here at Dovetail and you my friend. Sadly I failed to use the Google well and this took way too long to fix. Hence this cathartic post. Enjoy.

We’re From Redmond and We’re Here To Help

Windows is trying to protect you from running untrusted code you’ve found on the InterWebs. I only wish that the exception above would give you some small clue as to its cause. Instead it is as if the ASP.Net team is blaming the exception on a your web server administrator and by proxy your ASP.Net Trust Level which it is not. This might lead you, my friend, down the wrong wrong path into the weeds. Don’t go there.

Right click on the zip file, the individual executable or assembly which you downloaded from that nasty untrusted zone of the InterWebs (a.k.a Internet.)

There you go. You’ll notice I use WinRar I like it. One of the reasons I like it is when I extract files with it it treats me like an Adult. WinRar does not block my files.

Windows Explorer treats you different. It puts on the kid gloves and coddles you and tells you everything is going to be OK and no scary scary naughty code from the InterWebs is going to hurt you one bit. Windows Explorer will block those nasty assemblies, executables and documentation so they are completely useless and will cause your web application to throw nonsensical errors wasting everyone’s time when they could be at home with loved ones or out at the pub laughing gleefully.

Unblock That Code

If you know where the code came from. Go ahead click that Unblock button. Unblock it that code. Go ahead and hook that code up to your web server and have it tell the world how awesome you are.

Lucene’s docFreq Got You Down? Replace It With a Custom Collector

I came across a weird thing with Lucene using their document frequency API.

int docFreq(Term term) - Expert: Returns the number of documents containing term. Called by search code to compute term weights.

You can use this call to quickly find the number of documents in your index matching a term you give it. The problem I ran into was that when you delete documents they still show up in the count of documents returned by docFreq(). Worse yet document frequencies will include deleted documents until an index optimization is done. Yikes! Index optimization is very very slow and expensive so I really do not want to optimize just because we deleted a few documents. The real answer is not to use docFreq at all. We can instead use a custom Collector to get the desired effect.

My Problem

I have an administrative view that looks at the search index and shows you the count of each “kind” of document present in the index.

An inaccurate count of index contents would cause administrator confusion. And it could easily happen too. During a re-index I delete all of a type's documents and start re-adding them. The total number of documents in some cases would double. Why? Because docFreq() was counting deleted documents. Let’s fix that.

A Counting Collector

When you do searches in Lucene you can give the searcher a Collector which feels a bit like the visitor pattern as the search calls your collector once for each document that matches your query.

public class CounterCollector : Collector
{
    public int Count { get; private set; }

    public void Reset()
    {
        Count = 0;
    }

    public override void Collect(int docID)
    {
        Count = Count + 1;
    }

    public override void SetScorer(Scorer scorer) { }

    public override void SetNextReader(IndexReader reader, int docBase) { }

    public override bool AcceptsDocsOutOfOrder()
    {
        return true;
    }
}

This very simple collector just tracks the number times the Collect method is called. Better yet it does not count deleted documents. So finally I updated my search code.

How Do I Use One Of Those?

public int GetNumberOfDocumentsForTerm(Term term)
{
    return searchIndex(searcher =>
    {
        //replacing this
        //return searcher.DocFreq(term);
        
        //with this
        var counterCollector = new CounterCollector();
        searcher.Search(new TermQuery(term), counterCollector);
        return counterCollector.Count;
    });
}

private T searchIndex<T>(Func<IndexSearcher, T> searchAction)
{
    var indexSearcher = new IndexSearcher(_directory, true);

    T result;
    try
    {
        result = searchAction(indexSearcher);
    }
    catch(BooleanQuery.TooManyClauses tooManyClausesException)
    {
        throw new SearchException("Your wildcard query was too broad please narrow your search. Example - change a* to apple*", tooManyClausesException);
    }

    indexSearcher.Close();

    return result;
}

Take Away

Doing searches with custom collectors is quite easy. You just give an instance of one to the search method and interrogate it afterwards for the information you require.

You might notice some too fancy code to get this going but hopefully you get the idea. You may find the Func-y way of calling the searcher weird but it’s handy way to have all your search code do the same error handling.

Using Dovetail SDK Using Visual Basic.Net

A customer recently asked for a code example demonstrating how to use our Dovetail SDK to do data access custom objects using Generics (the Dovetail kind not the C# kind)

Imports FChoice.Foundation.Clarify
Imports System.Collections.Specialized
Imports FChoice.Foundation
Imports NUnit.Framework

Module SDKExample

    <TestFixture()> Public Class SDK_test
        <Test()> Public Sub update_case_title()

            InitializeDovetailSDK()

            UseGenericToUpdateExistingCaseTitle("1", "new title")

        End Sub
    End Class

    Sub InitializeDovetailSDK()
        Dim config As New NameValueCollection

        'These are hard coded database credentials. More typically you'd use application configuration
        config.Add("fchoice.dbtype", "mssql")
        config.Add("fchoice.connectionstring", "Data Source=.; Initial Catalog=SDKcl125_2k5; User Id=sa; Password=sa;")

        ClarifyApplication.Initialize(config)
    End Sub

    Sub UseGenericToUpdateExistingCaseTitle(ByVal caseId As String, ByVal title As String)
        Dim session As ClarifySession

        session = ClarifyApplication.Instance.CreateSession("sa", "sa", ClarifyLoginType.User)

        Dim dataset As New ClarifyDataSet(session)

        Dim caseGeneric As ClarifyGeneric

        caseGeneric = dataset.CreateGeneric("case")
        caseGeneric.AppendFilter("id_number", StringOps.Equals, caseId)

        caseGeneric.Query()

        If caseGeneric.Rows.Count < 1 Then
            Throw New ApplicationException(String.Format("Case {0} was not found.", caseId))
        End If

        caseGeneric.Rows(0)("title") = title

        dataset.Update(caseGeneric)

    End Sub

End Module

Here is code that uses Dovetail generics to connect to a database, located and update the title of a case. Here is a gist of this code snippet in case I evolve it in the future.

Refreshing Component Guids in your Wix XML

Right now I am working once again with Wix which can be quite shall we put it nicely: freaking traumatic. I promise this post will not entirely be me moaning about Windows Installer crap-titude. Rather, this post may actually help you out of a weird jam some day.

My Windows Installer (.msi based) installed application started to refuse to uninstall with a weird error.

Registry Component\<GUID>\<GUID> could not be found.

The only resulting way for me to get rid of the install was to use the Windows Install Clean Up which is pretty scary because Microsoft doesn’t provide it as a download because:

While the Windows Installer Cleanup utility resolved some installation problems, it sometimes damaged other components installed on the computer. Because of this, the tool has been removed from the Microsoft Download Center.

Yikes! Anyhow. The reason this was happening seems to be related to colliding component Guids . This was likely caused by me doing a cut and paste of existing Wix component XML and leaving the duplicate Guid laying around like a ticking time bomb. Did I mention I dislike Windows Installer?

My colleague Joey Vano ran into this problem and solved it by refreshing his entire project’s component Guids. FYI, this is only OK to do if your doing a “major” upgrade of your installer. Don’t know what that means? Then it is amazing you are this far into this post.

Indeed refreshing the Guids ended up being the fix. However, generating and replacing all the Guids by hand seemed a bad idea and ripe cause for an RSI injury to my wrists as my project had a lot of Guids. Being lazy I wrote a console app which refreshes all the Guids in the .wxs files in the current working directory. Here is the code. I hope you never need to find it useful.

using System;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using System.Xml.XPath;

namespace reguid
{
    public class WixComponentGuidRefresh
    {
        static void Main(string[] args)
        {
            var files = System.IO.Directory.EnumerateFiles(".", "*.wxs");

            var nsmgr = new XmlNamespaceManager(new NameTable());
            nsmgr.AddNamespace("w", "http://schemas.microsoft.com/wix/2006/wi");

            foreach (var file in files)
            {
                Console.WriteLine("Opening file {0}.", file);

                var document = XDocument.Load(file);

                var components = document.XPathSelectElements("//w:Component", nsmgr);

                Console.WriteLine("Found {0} components.", components.Count());

                foreach (var component in components)
                {
                    var id = component.Attribute("Id").Value;
                    var guidString = "{" + Guid.NewGuid() + "}";

                    Console.WriteLine("Setting guid for component {0} to {1}.", id, guidString);
                    
                    component.Attribute("Guid").SetValue(guidString);
                }

                document.Save(file);
            }
        }
    }
}

Using the Tika Java Library In Your .Net Application With IKVM

This may sound scary and heretical but did you know it is possible to leverage Java libraries from .Net applications with no TCP sockets or web services getting caught in the crossfire? Let me introduce you to IKVM, which is frankly magic:

IKVM.NET is an implementation of Java for Mono and the Microsoft .NET Framework. It includes the following components:

• A Java Virtual Machine implemented in .NET
• A .NET implementation of the Java class libraries
• Tools that enable Java and .NET interoperability

Using IKVM we have been able to successfully integrate our Dovetail Seeker search application with the Tika text extraction library implemented in Java. With Tika we can easily pull text out of rich documents from many supported formats. Why Tika?  Because there is nothing comparable in the .Net world as Tika.

This post will review how we integrated with Tika. If you like code you can find this example in a repo up on Github.

Compiling a Jar Into An Assembly

First thing, we need to get our hands on the latest version of Tika. I downloaded and built the Tika source using Maven as instructed. The result of this was a few jar files. The one we are interested in is tika-app-x.x.jar which has everything we need bundled into one useful container.

Next up we need to convert this jar we’ve built to a .Net assembly. Do this using ikvmc.exe.

tika\build>ikvmc.exe -target:library tika-app-0.7.jar

Unfortunately, you will see tons of troublesome looking warnings but the end result is a .Net assembly wrapping the Java jar which you can reference in your projects. 

Using Tika From .Net

IKVM is pretty transparent. You simply reference the the Tika app assembly and your .Net code is talking to Java types. It is a bit weird at first as you have Java versions of types and .Net versions. Next you’ll want to make sure that all the dependent IKVM runtime assemblies are included with your project. Using Reflector I found that the Tika app assembly referenced a lot of IKVM assemblies which do not appear to be used. I had to figure out through trial and error which assemblies where not being touched by the rich document extractions being done. If need be you could simple include all of the referenced IKVM assemblies with your application. Below I have done the work for you and eliminated all references to all the IKVM assemblies which appear to be in play.

16 assemblies down to 5. A much smaller deployment.

Using Tika

To do some text extraction we’ll ask Tika, very nicely, to parse the files we throw at it. For my purposes this involved having Tika automatically determine how to parse the stream and extract the text and metadata about the document.

public TextExtractionResult Extract(string filePath)
{
    var parser = new AutoDetectParser();
    var metadata = new Metadata();
    var parseContext = new ParseContext();
    java.lang.Class parserClass = parser.GetType();
    parseContext.set(parserClass, parser);

    try
    {
        var file = new File(filePath);
        var url = file.toURI().toURL();
        using (var inputStream = MetadataHelper.getInputStream(url, metadata))
        {
            parser.parse(inputStream, getTransformerHandler(), metadata, parseContext);
            inputStream.close();
        }

        return assembleExtractionResult(_outputWriter.toString(), metadata);
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Extraction of text from the file '{0}' failed.".ToFormat(filePath), ex);
    }
}

One Important Cavet

Java has a concept called a ClassLoader which has something to do with how Java types are found and loaded. There is probably a better way around this but for some reason if you do not implement a custom ClassLoader and also set an application setting cueing the IKVM runtime about which .Net type to use as the ClassLoader.

    public class MySystemClassLoader : ClassLoader
    {
        public MySystemClassLoader(ClassLoader parent)
            : base(new AppDomainAssemblyClassLoader(typeof(MySystemClassLoader).Assembly))
        {
        }
    }

Here is an example app.config telling IKVM where the ClassLoader is found.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="ikvm:java.system.class.loader" value="TikaOnDotNet.MySystemClassLoader, TikaOnDotNet" />
    </appSettings>
</configuration>

This step is very important. If IKVM cannot find a class loader, for some horrible reason, Tika will work fine but extract only empty documents with no metadata. The main reason this is troubling is that no exception is raised. For this reason we actually have a validation step in our application ensuring that the app setting is present and that it resolves to a valid type.

Demo

Here is a test demonstrating an extraction and the result.

    [Test]
    public void should_extract_from_pdf()
    {
        var textExtractionResult = new TextExtractor().Extract("Tika.pdf");

        textExtractionResult.Text.ShouldContain("pack of pickled almonds");

        Console.WriteLine(textExtractionResult);
    }

Put simply rich documents like this go in.

And a TextExtractionResult comes out:

public class TextExtractionResult
{
    public string Text { get; set; }
    public string ContentType { get; set; }
    public IDictionary<string, string> Metadata { get; set; } 
    //toString() override
}

Here is the raw output from Tika:

Conclusion

I hope this helps boost your confidence that you can use Java libraries in your .Net code and I hope my example repo will be of assistance if you need to do some work with Tika on the .Net platform. Enjoy.

Dovetail Seeker Sneak Preview

Lately we have been giving some love to Dovetail Seeker our search solution for Clarify/Dovetail CRM. I wanted to share a couple teaser screenshots of a couple features we are working on for our next release.

These screenshots are from an example “reference” web application we are including with Dovetail Seeker to show off all its new fancy features and to get developers excited and empowered to build their own search clients.

The these screens are pretty raw they are from an example app, a proving ground, which will undergo much improvement and change.

You’ve Got Your Documents In My Search Index!

The most requested and exciting new feature to Dovetail Seeker is “Rich Document Indexing”.

You select a directory you want indexed and our Seeker will look for files, extracting their content making them searchable. We have support of all your favorite document formats: Office, PDF, Rich Text, Text file etc.

In this example. We have a domain in the index documentation which contains a bunch of rich documents. The contents of these documents are now searchable and once found downloadable.

We’ve been working hard to come up with a good solution for exposing your file repositories to search and making it easy to download your documents directly from the search results.

There is more rich document goodness coming in Seeker but I’ll save that for later. Yep, that’s an official Dovetail feature teaser.

Need Spelling Assistance? We’re Here To Help.

The second most often requested feature for Dovetail Seeker was to add spelling assistance to search results similar to Google’s “Did you mean?”

It’s simple. If the spell checker thinks that one of your search terms is misspelled it looks for spelling suggestion which exist in the search index.

To accomplish this we’ve integrated with an excellent open source spell checking library to expose spell checking capabilities via the Dovetail Seeker web service. This feature is already in use at a customer site and was well received. Will that infrastructure in place it was easy to tie together search and spellchecking to expose this new search feature.

Authoring Schema Script to ‘Upsert’ changes to your database. (a.k.a Add and/or Update)

This post will teach you the best way to author your Schema Editor schema script to do upserts, adds and/or updates to your Amdocs database schema. Luckily you do not often have to worry about doing upserts but there is one situation where not doing them can get you into trouble.

Anytime you provide schema script which should work whether the object exists or not and must change properties of the original.

Sorry if that just lost you. Lets start out with the basics.

What is this Schema Script?

We created Schema Editor for Amdocs users who need a better way to update the database schema of their Clarify/Amdocs database. When we created SchemaEditor Gary Sherman was adamant that we needed a new workflow for updating the schema. The classic workflow involved exporting the database schema, manually editing the result, and reapplying the changes. It is not easy to automate the classic workflow. Thus SchemaScript was born as a one shot way of applying additional schema (tables, fields, views.. what not) to your database. Best to illustrate with an example.

Example of adding a table

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">

    <!-- Schema required for password reset functionality -->
    <addTable id="4304" name="password_reset"/>
    <addColumn table="password_reset" name="objid" dataType="Integer">
        <clarifyGenericFieldID>3</clarifyGenericFieldID>
    </addColumn>
    <addColumn table="password_reset" name="email" dataType="String">
        <length>80</length>
    </addColumn>
    <addColumn table="password_reset" name="loginName" dataType="String">
        <length>30</length>
    </addColumn>
    <addColumn table="password_reset" name="token" dataType="String">
        <length>80</length>
    </addColumn>
    <addColumn table="password_reset" name="expiration_date" dataType="DateTime"/>
    <addColumn table="password_reset" name="user_objid" dataType="Integer" />

</schemaScript>

The script above adds a table called password_reset which is used by or web clients to facilitate, guess what.. resetting passwords.

When you apply this script against your database it does not matter if the table already exists. Schema scripts are additive. When you say addTable and the table already exists nothing changes.

Updating existing schema

Besides adding new schema you can update existing tables, views, what-have-you. The difference between an add and an update is that the what-have-you needs to exist or you’ll get an error.

So lets update the password_reset table’s description using schema script as it’s always a good idea to document your schema.

Example of updating a table

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
    
    <updateTable name="password_reset">
        <description>Used by Dovetail client applications to facilitate resetting user passwords without administrator intervention</description>
    </updateTable>

</schemaScript>

Upserting Schema

Finally here is the point of the post. Sometimes you have to do both an add and an update of your schema. Why? Read on.

When packaging schema script for our applications I recently ran into a situation where I needed to add a column to a SQL view which was already in use by customers and thus already in my test database. So updated my schema script to have an additional field and updated the SQL statement appropriately. Here is the result.

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
    
    <addSqlView name="fc_contact_view" id="4759">
        <baseTable>contact</baseTable>
        <groupName>Dovetail Support</groupName>
        <description>Distinct contacts at a site with no duplicates in the case where a contact has multiple roles at a site.</description>
        <sql>
            SELECT DISTINCT r.objid, c.objid as contact_objid, s.objid as site_objid, c.first_name, c.last_name, c.phone, c.update_stamp as last_modified,
            s.name as site_name, s.site_id as site_id, r.primary_site as is_primary_site, c.status as is_active, c.e_mail, r.role_name
            FROM table_contact c
            INNER JOIN table_contact_role r ON c.objid = r.contact_role2contact
            INNER JOIN table_site s ON s.objid = r.contact_role2site
            WHERE c.status = 0 AND 0=0
        </sql>
    </addSqlView>
    
    <addSqlViewColumn name="contact_role_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="contact_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="site_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="first_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="last_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="phone" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="last_modified" sqlView="fc_contact_view" dataType="DateTime"></addSqlViewColumn>
    <addSqlViewColumn name="site_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="site_id" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="is_primary_site" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="is_active" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="e_mail" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="role_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>

</schemaScript>

The highlighted lines show the schema script for the new view column and SQL statement changes.

Oops that didn’t work

When these changes are applied and the SQL view already exists in the schema you will get this error:

Error Occurred : 'table_fc_contact_view' has fewer columns than were specified in the column list.

This is an error coming from the database complaining that the fields in you SQL query do not match the number of columns you’ve defined. Why? Because the SQL statement is contained in an additive operation addSqlView it is ignored when that view already exists in your database. Schema Editor will ignore an add operation when it figures out that that object is already present. This warning from Schema Editor should clue you in.

Warning: SqlView "fc_contact_view" was not added. It is already in the schema.

Because the addSqlView was ignored the new SQL statement did not make it into the schema and thus the number of view columns does not match the SQL statement.

The Solution

The way to solve this problem is to do both an add and update operations. An add to put the object in the schema in the case where it does not yet exist. And an update to make sure any new changes get written to the schema object. You’ll need to do this anytime you want to change the state of an object that may already exist in your schema yet want to create the object for the situation where it does not yet exist.

Like I mentioned at the beginning most people will not run into this issue but for Dovetail when we ship updates to products which modify existing schema we need to worry about this.

The full correct schema script

<schemaScript xmlns="http://www.dovetailsoftware.com/2006/10/SchemaScript.xsd">
    
    <addSqlView name="fc_contact_view" id="4759"><sql/></addSqlView>
    <updateSqlView name="fc_contact_view">
        <baseTable>contact</baseTable>
        <groupName>Dovetail Support</groupName>
        <description>Distinct contacts at a site with no duplicates in the case where a contact has multiple roles at a site.</description>
        <sql>
            SELECT DISTINCT c.objid as contact_objid, s.objid as site_objid, c.first_name, c.last_name, c.phone, c.update_stamp as last_modified,
            s.name as site_name, s.site_id as site_id, r.primary_site as is_primary_site, c.status as is_active, c.e_mail, r.role_name, r.objid as contact_Role_objid
            FROM table_contact c
            INNER JOIN table_contact_role r ON c.objid = r.contact_role2contact
            INNER JOIN table_site s ON s.objid = r.contact_role2site
            WHERE c.status = 0 AND 0=0
        </sql>
    </updateSqlView>
    <addSqlViewColumn name="contact_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="site_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="first_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="last_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="phone" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="last_modified" sqlView="fc_contact_view" dataType="DateTime"></addSqlViewColumn>
    <addSqlViewColumn name="site_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="site_id" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="is_primary_site" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="is_active" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn>
    <addSqlViewColumn name="e_mail" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="role_name" sqlView="fc_contact_view" dataType="String"></addSqlViewColumn>
    <addSqlViewColumn name="contact_role_objid" sqlView="fc_contact_view" dataType="Integer"></addSqlViewColumn> 
</schemaScript>

Update (July 30th, 2010)

I moved the contact_role_objid addSqlViewColumn element to the end of the XML above. The reason is when adding sql view columns to the view the view columns appear in the select statement in the order they are added. I also corrected the SQL to reflect the expected order of the view columns in the generated view.

Conclusion

Schema script is a great feature of Schema Editor. I hope you do use it when scripting changes to your database schema. I do realize that we may want to add native support for ‘upserts’ to Schema Editor but this scenario is relatively rare and the workaround I talk about is easy to use when you run into it. Let me know what you think and about any of the cool stuff you’ve done with Schema Editor.