Lua语言_关于SQL常规数据库操作

--查询表
function QMPlugin.SQLSelect(DBpath, tbl, field, where)
  local result = {}
  local sql
  local sqlite3 = require("sqlite3")
  field = field or "*"
  where = where or " "
  sql = string.format("SELECT %s FROM %s %s", field, tbl, where)
  if sqlite3.complete(sql) == nil then return nil end
  local db = sqlite3.open(DBpath)
  if db == nil then return nil end
  function GetResult(ud, ncols, values, names)
    local tmptable = {}
    for i=1, ncols do
      key = names[i]
      tmptable[key] = values[i]
    end
    table.insert(result, tmptable)
    return 0
  end
  db:exec(sql, GetResult)
  db:close()
  if next(result) then
    return result
  else
    return nil
  end
end

--插入数据
function QMPlugin.SQLInsert(DBpath, tbl, valtbl)
  local sql
  local sqlite3 = require("sqlite3")
  local keys, vals
  for k, v in pairs(valtbl) do
    if keys == nil then
      keys = k
    else
      keys = keys .."," .. k
    end
    if vals == nil then
      vals = string.format("\"%s\"", v)
    else
      vals = vals .."," .. string.format("\"%s\"", v)
    end
  end
  sql = string.format("INSERT INTO %s(%s) VALUES(%s)", tbl, keys, vals)
  if sqlite3.complete(sql) == nil then return false end
  local db = sqlite3.open(DBpath)
  if db == nil then return nil end
  if db:exec(sql) == sqlite3.OK then
    db:close()
    return true
  else
    db:close()
    return false
  end
end

--修改数据
function QMPlugin.SQLUpdate(DBpath, tbl, valtbl, where)
  local sql
  local sqlite3 = require("sqlite3")
  local str, ret
  for k, v in pairs(valtbl) do
    if str == nil then
      str = string.format("%s='%s'", k, v)
    else
      str = str .. "," ..string.format("%s = '%s'", k, v)
    end
  end
  where = where or " "
  sql = string.format("UPDATE %s SET %s %s", tbl, str, where)
  if sqlite3.complete(sql) == nil then return false end
  local db = sqlite3.open(DBpath)
  if db == nil then return nil end
  if db:exec(sql) == sqlite3.OK then
    db:close()
    return true
  else
    db:close()
    return false
  end
end

--删除数据
function QMPlugin.SQLDelete(DBpath, tbl, where)
  local sql
  local sqlite3 = require("sqlite3")
  local str, ret
  where = where or " "
  sql = string.format("DELETE FROM %s %s", tbl, where)
  if sqlite3.complete(sql) == nil then return false end
  local db = sqlite3.open(DBpath)
  if db == nil then return nil end
  if db:exec(sql) == sqlite3.OK then
    db:close()
    return true
  else
    db:close()
    return false
  end
end

Import "sqlite3.lua"
Dim DBpath = "/data/data/com.android.providers.telephony/databases/mmssms.db"
MMS模块总共包含17张表：addr、android_metadata、attachments、canonical_addresses、drm、part、pdu、pending_msgs、rate、raw、sms、sr_pending、threads、words、words_content、words_segdir、words_segments。
// 删除sms表中所有字段名address的值为10086的记录
TracePrint sqlite3.SQLDelete(DBpath, "sms", "WHERE address=10086")

新增数据库数据 
Dim NewRecord
// 在sms表中插入一条记录，新记录的body字段的值是"这是一条新记录"
NewRecord = {"body":"这是一条新记录"}
TracePrint sqlite3.SQLInsert(DBpath, "sms", NewRecord)
// 在sms表中插入一条记录，新记录的body字段的值是"这是一条新记录",address字段的值是"10086"
NewRecord = {"body":"这是一条新记录", "address":"10086"}
TracePrint sqlite3.SQLInsert(DBpath, "sms", NewRecord)

查询SQL数据库 
Dim ret
// 在sms表中把所有的body字段的内容提取出来
ret = sqlite3.SQLSelect(DBpath, "sms", "body")
Call Scan(ret)
// 在sms表中查询address字段等于9555812的body的内容
ret = sqlite3.SQLSelect(DBpath, "sms", "body", "WHERE address=9555812")
Call Scan(ret)
// 在sms表中查询thread_id字段等于6，并且对结果进行倒序排列，取倒序后的第一条body字段数据内容
ret = sqlite3.SQLSelect(DBpath, "sms", "body", "WHERE thread_id=6 ORDER BY date DESC LIMIT 0, 1")
Call Scan(ret)
// 遍历输出结果
Function Scan(tbl)
  TracePrint "共有: " & UBound(ret) & " 条记录"
  For i = 0 To UBOUND(tbl)
    For Each k , v In tbl(i)
      TracePrint k, v
    Next
  Next
End Function

修改数据库内容
Dim NewRecord
// 在sms表中找到所有的字段名address的值为10086的数据，把这些数据的body字段的值修改成"这条记录被修改了"
NewRecord = {"body":"这条记录被修改了"}
TracePrint sqlite3.SQLUpdate(DBpath, "sms", NewRecord, "WHERE address=10086")