代码之家 › 专栏 › 技术社区 › James Black

创建updatesql查询的更正确的方法

functional-programming f# sql

James Black · 技术社区 · 16 年前

我正在使用F#访问一个数据库,我最初尝试创建一个函数来创建更新查询是有缺陷的。

let BuildUserUpdateQuery (oldUser:UserType) (newUser:UserType) =
    let buf = new System.Text.StringBuilder("UPDATE users SET ");
    if (oldUser.FirstName.Equals(newUser.FirstName) = false)  then buf.Append("SET first_name='").Append(newUser.FirstName).Append("'" ) |> ignore
    if (oldUser.LastName.Equals(newUser.LastName) = false)  then buf.Append("SET last_name='").Append(newUser.LastName).Append("'" ) |> ignore
    if (oldUser.UserName.Equals(newUser.UserName) = false)  then buf.Append("SET username='").Append(newUser.UserName).Append("'" ) |> ignore
    buf.Append(" WHERE id=").Append(newUser.Id).ToString()

这并不能很好地解决问题 ,

UPDATE users SET first_name='Firstname', last_name='lastname' WHERE id=...

set

我可以创建一个元组列表,其中每个元组都是oldtext、newtext、columnname,这样我就可以在列表中循环并构建查询,但是似乎我应该传递一个 StringBuilder boolean 然后作为参数传递给递归函数。

这似乎是最好的办法,还是有更好的办法?

更新:

SET

let BuildUserUpdateQuery3 (oldUser:UserType) (newUser:UserType) =
    let properties = List.zip3 oldUser.ToSqlValuesList newUser.ToSqlValuesList oldUser.ToSqlColumnList 
    let init = false, new StringBuilder()
    let anyChange, (formatted:StringBuilder) = 
        properties |> Seq.fold (fun (anyChange, sb) (oldVal, newVal, name) ->
            match(oldVal=newVal) with
            | true -> anyChange, sb
            | _ ->
                match(anyChange) with
                | true -> true, sb.AppendFormat(",{0} = '{1}'", name, newVal)
                | _ -> true, sb.AppendFormat("{0} = '{1}'", name, newVal)                    
            ) init
    formatted.ToString()

let BuildUserUpdateQuery (oldUser:UserType) (newUser:UserType) (updatequery:UserType->UserType->String) =
    let buf = StringBuilder("UPDATE users SET ");
    buf.AppendFormat(" {0} WHERE id={1}", (updatequery oldUser newUser), newUser.Id)

let UpdateUser conn (oldUser:UserType) (newUser:UserType) =
    let query = BuildUserUpdateQuery oldUser newUser BuildUserUpdateQuery3
    execNonQuery conn (query.ToString())

3 回复 | 直到 16 年前

Mauricio Scheffer 16 年前

这就是你想的元组解决方案吗?

let BuildUserUpdateQuery (oldUser:UserType) (newUser:UserType) =
    let buf = StringBuilder("UPDATE users set ")
    let properties = 
        [(oldUser.FirstName, newUser.FirstName, "first_name")
         (oldUser.LastName, newUser.LastName, "last_name")
         (oldUser.UserName, newUser.UserName, "username")]
         |> Seq.map (fun (oldV, newV, field) -> 
                        if oldV <> newV 
                            then sprintf "%s='%s'" field newV 
                            else null)
         |> Seq.filter (fun p -> p <> null)
         |> Seq.toArray
    if properties.Length = 0
        then None
        else
            bprintf buf "%s" (String.Join(", ", properties))
            bprintf buf " where id=%d" newUser.Id
            Some <| buf.ToString()

我不明白递归解怎么会比这更简单。。。

顺便说一句,我强烈建议使用适当的SQL参数,而不是仅仅串联的值,你可能会变得容易受到注入攻击。。。

Tomas Petricek 16 年前

为了完整起见,这里有一个直接使用 fold 功能。这可以做得相当优雅,因为 StringBuilder 字符串拼接 (这允许您将它们链接到C#中)。这也可以很好地用于折叠。

假设我们有Mauricio解决方案中的元组列表:

let properties =  
   [ (oldUser.FirstName, newUser.FirstName, "first_name") 
     (oldUser.LastName, newUser.LastName, "last_name") 
     (oldUser.UserName, newUser.UserName, "username") ]

现在您可以编写以下代码(它还返回一个标志,指示是否有任何更改):

let init = false, new StringBuilder()
let anyChange, formatted = 
  properties |> Seq.fold (fun (anyChange, sb) (oldVal, newVal, name) ->
      if (oldVal = newVal) anyChange, sb
      else true, sb.AppendFormat("{0} = '{1}'", name, newVal)) init

折叠过程中保持的状态具有类型 bool * StringBuilder true 以及新版本的 字符串拼接 退回人 AppendFormat .

显式地使用递归也可以,但是当您可以使用一些内置的F#函数时,使用这种方法通常更容易。如果需要处理每个实体的嵌套实体,可以使用 Seq.collect 函数与递归一起使用,以获得需要使用 折叠 . 伪代码可能如下所示:

let rec processEntities list names =
  // Pair matching entity with the name from the list of names
  List.zip list names 
  |> List.collect (fun (entity, name) ->
    // Current element containing old value, new value and property name
    let current = (entity.OldValue, entity.NewValue, name)
    // Recursively proces nested entitites
    let nested = processEntities entity.Nested
    current::nested)

let rec processEntities list =
  seq { for entity, name in List.zip list names do 
          yield (entity.OldValue, entity.NewValue, name)
          yield! processEntities entity.Nested }

你可以打电话给我 processEntities 它返回实体的平面列表,并使用 折叠 就像第一种情况一样。

kvb 16 年前

我喜欢毛里西奥和托马斯的解决方案,但也许这更像你最初的设想?

let sqlFormat (value:'a) = //'
  match box value with
  | :? int | :? float -> value.ToString()
  | _ -> sprintf "'%A'" value // this should actually use database specific escaping logic to make it safe

let appendToQuery getProp (sqlName:string) (oldEntity,newEntity,statements) =
  let newStatements =
    if (getProp oldEntity <> getProp newEntity) then (sprintf "%s=%s" sqlName (sqlFormat (getProp newEntity)))::statements
    else statements
  (oldEntity, newEntity, newStatements)

let createUserUpdate (oldUser:UserType) newUser =
  let (_,_,statements) =
    (oldUser,newUser,[])
    |> appendToQuery (fun u -> u.FirstName) "first_name"
    |> appendToQuery (fun u -> u.LastName) "last_name"
    |> appendToQuery (fun u -> u.UserName) "username"
    // ...

  let statementArr = statements |> List.toArray
  if (statementArr.Length > 0) then
    let joinedStatements = System.String.Join(", ", statementArr)
    Some(sprintf "UPDATE users SET %s WHERE ID=%i" joinedStatements newUser.ID)
  else
    None