Duplicate Table Names Cannot Be Resolved

[devinlyons]

Describe the bug
If you have a database with two tables named TableName and Table_Name, SQLProvider will be unable to resolve the names.

To Reproduce
Steps to reproduce the behavior:

1. Point SQLProvider at a database with pseudo duplicate names.
2. Access the tables in code and watch intellisense complain.

Expected behavior
SQLProvider should be able to resolve the names and display them in the list of tables.

Screenshots

Desktop (please complete the following information):

• OS: Windows 10

[halcwb]

@Thorium This should be really simple to fix:

This is the code that creates the table names:

open System

module internal SchemaProjections = 
    
    //Creatviely taken from FSharp.Data (https://github.com/fsharp/FSharp.Data/blob/master/src/CommonRuntime/NameUtils.fs)
    let private tryAt (s:string) i = if i >= s.Length then None else Some s.[i]
    let private sat f (c:option<char>) = match c with Some c when f c -> Some c | _ -> None
    let private (|EOF|_|) c = match c with Some _ -> None | _ -> Some ()
    let private (|LetterDigit|_|) = sat Char.IsLetterOrDigit
    let private (|Upper|_|) = sat (fun c -> Char.IsUpper c || Char.IsDigit c)
    let private (|Lower|_|) = sat (fun c -> Char.IsLower c || Char.IsDigit c)
    
    // --------------------------------------------------------------------------------------
    
    /// Turns a given non-empty string into a nice 'PascalCase' identifier
    let nicePascalName (s:string) = 
      if s.Length = 1 then s.ToUpperInvariant() else
      // Starting to parse a new segment 
      let rec restart i = seq {
        match tryAt s i with 
        | EOF -> ()
        | LetterDigit _ & Upper _ -> yield! upperStart i (i + 1)
        | LetterDigit _ -> yield! consume i false (i + 1)
        | _ -> yield! restart (i + 1) }
      // Parsed first upper case letter, continue either all lower or all upper
      and upperStart from i = seq {
        match tryAt s i with 
        | Upper _ -> yield! consume from true (i + 1) 
        | Lower _ -> yield! consume from false (i + 1) 
        | _ ->
            yield from, i
            yield! restart (i + 1) }
      // Consume are letters of the same kind (either all lower or all upper)
      and consume from takeUpper i = seq {
        match tryAt s i with
        | Lower _ when not takeUpper -> yield! consume from takeUpper (i + 1)
        | Upper _ when takeUpper -> yield! consume from takeUpper (i + 1)
        | Lower _ when takeUpper ->
            yield from, (i - 1)
            yield! restart (i - 1)
        | _ -> 
            yield from, i
            yield! restart i }
        
      // Split string into segments and turn them to PascalCase
      seq { for i1, i2 in restart 0 do 
              let sub = s.Substring(i1, i2 - i1) 
              if Array.forall Char.IsLetterOrDigit (sub.ToCharArray()) then
                yield sub.[0].ToString().ToUpperInvariant() + sub.ToLowerInvariant().Substring(1) }
      |> String.concat ""
    
    /// Turns a given non-empty string into a nice 'camelCase' identifier
    let niceCamelName (s:string) = 
      let name = nicePascalName s
      if name.Length > 0 then
        name.[0].ToString().ToLowerInvariant() + name.Substring(1)
      else name
    
    /// Add ' until the name is unique
    let rec avoidNameClashBy nameExists name =
      if nameExists name then avoidNameClashBy nameExists (name + "'")
      else name
        
    let buildTableName (tableName:string) = 
        //Current Name = [SCHEMA].[TABLE_NAME]
        if(tableName.Contains("."))
        then 
            let tableName = tableName.Replace("[", "").Replace("]", "")
            let startIndex = tableName.IndexOf(".")
            nicePascalName (tableName.Substring(startIndex))
        else nicePascalName tableName

    let buildFieldName (fieldName:string) = nicePascalName fieldName
    
    let buildSprocName (sprocName:string) = nicePascalName sprocName

    let buildTableNameWhereFilter columnName (tableNames : string) =
        let trim (s:string) = s.Trim()
        let names = tableNames.Split([|","|], StringSplitOptions.RemoveEmptyEntries)
                    |> Seq.map trim
                    |> Seq.toArray
        match names with
        | [||] -> ""
        | [|name|] -> sprintf "and %s like '%s'" columnName name
        | _ -> names |> Array.map (sprintf "%s like '%s'" columnName)
                     |> String.concat " or "
                     |> sprintf "and (%s)"

So, when you run this code you get TableName for "Table_Name" and "Table_Name_":

SchemaProjections.buildTableName "Table_Name"
SchemaProjections.buildTableName "Table_Name_"

This is all due to Char.IsLetterOrDigit, so underscores are ignored. If you fix this by using:

module Char =

    let isLetterDigitOrUnderscore c =
        c = '_' || c |> Char.IsLetterOrDigit

And replace all occurrences of Char.IsletterOrDigit with Char.isLetterDigitOrUnderscore

Then you get names including underscores and the problem is resolved.

P.S. Could also be used to fix the spaces problem by first replace spaces with underscores, see #759.

[Thorium]

I'm not very happy with this fix as it breaks backward compatibility of existing system: The users probably have already table-names that have removed underscores in the tablename. SQLProvider is used already a lot in production systems.

So I'd prefer some kind of "if table name ends with underscore or table-name already exist, then don't remove it", fixing this special case only.

[halcwb]

I'm not very happy with this fix as it breaks backward compatibility of existing system: The users probably have already table-names that have removed underscores in the tablename. SQLProvider is used already a lot in production systems.

Indeed, I just wanted to show that a fix is possible. However, if underscores have been removed, this fix wouldn't be a problem any way.

So I'd prefer some kind of "if table name ends with underscore or table-name already exist, then don't remove it", fixing this special case only.

I would suggest making these kinds of naming strategies optional using additional optional constructor parameters. Then it would also be quite trivial to solve #759, without breaking backward compatibility.