SqlJuxt – Making index names unique

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:

[<Test>]
let ``should return name passed in when name is not in collection as collection is empty``() =
    getNextAvailableName "my_index" []
        |> should equal "my_index"

[<Test>]
let ``should return name passed in when name is not in collection``() =
    getNextAvailableName "my_index" ["some_index"; "some_other"]
        |> should equal "my_index"

[<Test>]
let ``should return my_index2 when my_index is in collection``() =
    getNextAvailableName "my_index" ["my_index"]
        |> should equal "my_index2"


[<Test>]
let ``should return my_index3 when my_index and my_index2 are in collection``() =
    getNextAvailableName "my_index" ["my_index"; "my_index2"]
        |> should equal "my_index3"

[<Test>]
let ``should return my_index3 when my_index and my_index2 and my_index33 are in collection``() =
    getNextAvailableName "my_index" ["my_index"; "my_index2"; "my_index33"]
        |> should equal "my_index3"

[<Test>]
let ``should return my_index2 when my_index and my_index22 are in collection``() =
    getNextAvailableName "my_index" ["my_index"; "my_index22"]
        |> should equal "my_index2"

[<Test>]
let ``should return my_index10 when my_index 2-9 are already in collection``() =
    getNextAvailableName "my_index" ["my_index"; "my_index2"; "my_index3"; "my_index4"; "my_index5"; "my_index6"; "my_index7"; "my_index8"; "my_index9"]
        |> should equal "my_index10"

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:

let rec getNextAvailableName (name:string) (names: string list) =

        let getNumber (chr:char) =
            match Int32.TryParse(chr.ToString()) with
                | (true, i) -> Some i
                | _ -> None

        let grabLastChar (str:string) =
            str.[str.Length-1]

        let pruneLastChar (str:string) =
            str.Substring(0, str.Length - 1)

        let pruneNumber (str:string) i =
            str.Substring(0, str.Length - i.ToString().Length)

        let getNumberFromEndOfString (s:string)  =

            let rec getNumberFromEndOfStringInner (s1:string) (n: int option) =
                match s1 |> String.IsNullOrWhiteSpace with
                    | true -> n
                    | false -> match s1 |> grabLastChar |> getNumber with
                                | None -> n
                                | Some m ->  let newS = s1 |> pruneLastChar
                                             match n with 
                                                | Some n1 -> let newN = m.ToString() + n1.ToString() |> Convert.ToInt32 |> Some
                                                             getNumberFromEndOfStringInner newS newN
                                                | None -> getNumberFromEndOfStringInner newS (Some m) 
            let num = getNumberFromEndOfStringInner s None
            match num with
                | Some num' -> (s |> pruneNumber <| num', num)
                | None -> (s, num)
            

        let result = names |> List.tryFind(fun x -> x = name)
        match result with
            | Some r -> let (n, r) = getNumberFromEndOfString name
                        match r with 
                            | Some r' -> getNextAvailableName (n + (r'+1).ToString()) names
                            | None -> getNextAvailableName (n + "2") names
                        
            | None -> name

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:

[<Test>]
let ``should name indexes sequentially when there are multiple indexes defined that would generate the same name``() =
    CreateTable "MyIndexedTable"
        |> WithInt "MyKeyColumn"
        |> WithInt "SecondKeyColumn"
        |> WithNonClusteredIndex UNIQUE [("MyKeyColumn", ASC); ("SecondKeyColumn", DESC)]
        |> WithNonClusteredIndex UNIQUE [("MyKeyColumn", ASC); ("SecondKeyColumn", DESC)]
        |> WithNonClusteredIndex NONUNIQUE [("MyKeyColumn", ASC) ; ("SecondKeyColumn", DESC)]
        |> ScriptTable
        |> should equal @"CREATE TABLE [dbo].[MyIndexedTable]( [MyKeyColumn] [int] NOT NULL, [SecondKeyColumn] [int] NOT NULL )
GO

CREATE UNIQUE NONCLUSTERED INDEX IDX_MyIndexedTable_MyKeyColumn_SecondKeyColumn ON [dbo].[MyIndexedTable] ([MyKeyColumn] ASC, [SecondKeyColumn] DESC)
GO


CREATE UNIQUE NONCLUSTERED INDEX IDX_MyIndexedTable_MyKeyColumn_SecondKeyColumn2 ON [dbo].[MyIndexedTable] ([MyKeyColumn] ASC, [SecondKeyColumn] DESC)
GO


CREATE NONCLUSTERED INDEX IDX_MyIndexedTable_MyKeyColumn_SecondKeyColumn3 ON [dbo].[MyIndexedTable] ([MyKeyColumn] ASC, [SecondKeyColumn] DESC)
GO"

We can see how the generated index names are the same so they have been numbered.

Check out the full source code at SqlJuxt GitHub repository.

SqlJuxt – Defining indexes on a table

I have just finished implementing the first implementation of table indexes. Both scripting out an index on the table using the fluent builder syntax and comparing indexes on tables. When writing this feature I had some interesting design decisions to make…

My first design for the type to represent index is shown below:

type Constraint = {name: string; columns: (Column * SortDirection)  list; clustering: Clustering}
type Index = {name: string; columns: (Column * SortDirection) list; clustering: Clustering; uniqueness: Uniqueness}

type Table = {schema: string; name: string; columns: Column list; primaryKey: Constraint option; indexes: Index list}

As you can see the primary key on the table is defined as Constraint of Option and the indexes are defined as Index list. When I started writing the code to use these types I noticed a lot of duplication. Then I realised that an index and a primary key are both really constraints just with slightly different properties. Those being that a primary key is always unique that’s what makes it a key!

I decided to extend the Constraint type by adding the uniqueness property to it. Then it was a simple job of extending the primary key methods to always set the uniqueness to unique. Now the type for a table looks like:

type Table = {schema: string; name: string; columns: Column list; primaryKey: Constraint option; indexes: Constraint list}

So a table has a list of indexes which could of course be empty and it may or may not have a primary key which we can represent by using a Constraint of option. The other advantage of modelling both primary keys and indexes using the constraint type is that we can select them out of the database when loading up to build the schema for comparison all at the same time. We simply have to extend the query to bring back the additional information of whether the constraint is unique and if it is a primary key or not so we know whether to put it on the primary key property of the table.

I did toy with the idea of having all constraints in a list on the table called constraints. That list would’ve the primary key if there was one and all of the indexes for the table. I decided against that approach as it feels a bit clunky to have to go through a list to find the primary key of a table. Also the reason I didn’t like that approach is that where possible you should use the type system and thus the compiler to enforce correctness in your program and make it impossible to model illegal state. If I had a list of constraints on a table I would have to manually check to make sure there was only one primary key in the list. Whereas if I have the primary key defined on the table as option of Constraint then there can only ever be one primary key.

If you want to check out the full source code and delve deeper feel free to check out the SqlJuxt GitHub repository.