Duplicate Table Names Cannot Be Resolved
devinlyons opened this issue · 3 comments
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:
- Point SQLProvider at a database with pseudo duplicate names.
- 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.
Desktop (please complete the following information):
- OS: Windows 10
@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.
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.
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.