Menu

Tag Archives: generating databases in f#

As part of the fluent database builder for SqlJuxt I automatically generate names for objects that you create on the database such as primary keys and indexes. For primary keys this is an easy task as you can only have one primary key on a table so I can simply use the string “PK_

<

table>”. As a table name has to be unique I’m assured that the primary key name will be unique.

The problem comes when you want to create unique names for indexes. On Sql Server it is legal to have more than one non clustered index on a table on the same columns. So I am left with two options I either get the user to specify the index name which feels a bit clunky and unnecessary or I have to check for duplicate names and generate unique names. I chose the latter approach.

The naming convention for the indexes I have gone with is “IDX_

<

table>_” where column_names is a list of column names separated by underscores.

To keep the names unique I decided that if there was already an index with the name that is generated using the formula above then I would append a number on the end. If that name was taken then I would increment the number until I found a name that wasn’t taken.

To do the work of finding a unique name I thought it was best to abstract this out into its own function that could work with any name. The function signature I came up with was:

getNextAvailableName: string -> string list -> string

The function takes a string which is the name and a list of string which are the names that have been taken it then gives you back a new string which will be unique.

To write this function I needed a set of tests (test first remember) to prove out my test cases, these are:

I love how readable tests are in F# when you use FsUnit!! Now we have our tests defined we can go ahead an implement the function. I am sure that I didn’t do this in the most functional and efficient way. If anyone could help tidy this up then I would greatly appreciate it. My implementation is:

I’m sure there are some tricks you can do with pattern matching to shorten this down. Now that we have this function and all of the tests pass it is trivial to plug it in to our database builder. All we have to do is generate the index name using the formula above and then call the getNextAvailableName function with the generated index name and a list of all of the index names on the table. The function will then give us back a unique name to use. This gets proved out by the following test: