使用SQLite3数据库

guoxiaomi · 发表于 2019-12-27 03:40:35

加入我们，或者，欢迎回来。

您需要登录才可以下载或查看，没有帐号？注册会员

x

本帖最后由 guoxiaomi 于 2019-12-28 00:27 编辑

瞎编几个用SQLite3的好处：
1. SQLite3 是广泛应用的存储方案，久经考验，即使断电也不会导致数据库损坏
2. 外置数据库，方便查看和编辑
3. 独立于默认的存档机制
4. rm 是单进程，没有多进程的读写冲突
5. 支持加密，wxsqite的算法是强度很高的AES

友情上传 WXSQLite 3.30.1 的 dll 文件

sqlite3.zip (690.86 KB, 下载次数: 84) ，这个DLL提供了加密的功能：
1. 在调用 new 方法的时候传入字符串形式的密钥，没有密钥会自动设置
2. 修改密码需要在 new 之后，调用 rekey 函数。new 的时候仍然需要传入旧的正确的密钥

由于密钥是明文保存在脚本里，就把数据库加密的问题甩锅到脚本加密上了23333

RUBY 代码复制

# encoding: utf-8
# ---------------------------------------------------------
# SQLite3 For RMXP (by guoxiaomi)
# ---------------------------------------------------------
# version 0.3 at 2019/12/26
# ---------------------------------------------------------
# 注意：
# sqlite3_bind_double 无法正常使用，建议用格式化字符串拼接 SQL 语句
# sqlite3_column_double 在脚本里使用 sqlite3_column_blob 替代了
# bytesize，对更高版本的 RPG Maker，请搜索 "bytesize" 进行对应修改
#
# ---------------------------------------------------------
# 参考资料：
# 1. [url]https://renenyffenegger.ch/notes/development/databases/SQLite/VBA/index[/url]
# 2. [url]https://www.sqlite.org/download.html[/url]，下载 32 位的 DLL
# ---------------------------------------------------------
# 示例
# ---------------------------------------------------------
# 1. 打开数据库
# ---------------------------------------------------------
# db = SQLite3_RM.new("test.db")
# db = SQLite3_RM.new("test.db", "123456")
# ---------------------------------------------------------
# 2. 执行 SQL 语句
# ---------------------------------------------------------
# db.exec("create table user (id, name, gold)")
# ---------------------------------------------------------
# 3. query方法执行时可以用占位符代替部分内容，
#    如果是查询语句则返回查询结果的数组
# 4. query方法携带代码块时会将搜索的结果传给代码块，
#    此时不会返回结果数组，而是返回nil
# ---------------------------------------------------------
# db.query("insert into user values (?, ?, %.2f)" % [10], [0, "rmxp"])
# db.query("select * from user") { |row| p row }
# ---------------------------------------------------------
# 5. 关闭数据库
# ---------------------------------------------------------
# db.close
# ---------------------------------------------------------
 
require "win32api" if not defined? Win32API
 
class SQLite3_RM
  # ---------------------------------------------------------
  # 配置 DLL 的位置和是否加密
  # ---------------------------------------------------------
  SQLite3_DLL = "sqlite3.dll"
  SQLite3_Encrypt = true
  # ---------------------------------------------------------
 
  SQLite3_Open = Win32API.new(SQLite3_DLL, "sqlite3_open", "PP", "I")
  SQLite3_Exec = Win32API.new(SQLite3_DLL, "sqlite3_exec", "LPLLP", "I")
  SQLite3_Close = Win32API.new(SQLite3_DLL, "sqlite3_close", "L", "I")
 
  SQLite3_Prepare_V2 = Win32API.new(SQLite3_DLL, "sqlite3_prepare_v2", "LPLPP", "I")
  SQLite3_Bind_Int = Win32API.new(SQLite3_DLL, "sqlite3_bind_int", "LLL", "L")
  # SQLite3_Bind_Double = Win32API.new(SQLite3_DLL, "sqlite3_bind_double", "LLD", "L")
  SQLite3_Bind_Text = Win32API.new(SQLite3_DLL, "sqlite3_bind_text", "LLPIL", "L")
  SQLite3_Bind_Null = Win32API.new(SQLite3_DLL, "sqlite3_bind_null", "LL", "L")
  SQLite3_Bind_Blob = Win32API.new(SQLite3_DLL, "sqlite3_bind_blob", "LIPIL", "L")
 
  SQLite3_Step = Win32API.new(SQLite3_DLL, "sqlite3_step", "L", "I")
  SQLite3_Column_Count = Win32API.new(SQLite3_DLL, "sqlite3_column_count", "L", "I")
  SQLite3_Column_Type = Win32API.new(SQLite3_DLL, "sqlite3_column_type", "LI", "I")
  SQLite3_Column_Int = Win32API.new(SQLite3_DLL, "sqlite3_column_int", "LI", "I")
  # SQLite3_Column_Double = Win32API.new(SQLite3_DLL, "sqlite3_column_double", "LI", "D")
  SQLite3_Column_Text = Win32API.new(SQLite3_DLL, "sqlite3_column_text", "LI", "P")
  SQLite3_Column_Blob = Win32API.new(SQLite3_DLL, "sqlite3_column_blob", "LI", "P")
  SQLite3_Finalize = Win32API.new(SQLite3_DLL, "sqlite3_finalize", "L", "I")
 
  SQLite3_INTEGER = 1
  SQLite3_FLOAT = 2
  SQLite3_TEXT = 3
  SQLite3_BLOB = 4
  SQLite3_NULL = 5
  SQLite3_OK = 0
  SQLite3_DONE = 101
 
  def initialize(path)
    db = [0].pack("L")
    ret = SQLite3_Open.call(path, db)
    raise "SQLite3 Open Failed!" if ret != SQLite3_OK
    @db = db.unpack("L")[0]
  end
 
  def close
    ret = SQLite3_Close.call(@db)
    raise "SQLite3 Close Failed!" if ret != SQLite3_OK
  end
 
  def exec(query)
    ret = SQLite3_Exec.call(@db, query, 0, 0, 0)
    raise "SQLite3 Exec Failed!" if ret != SQLite3_OK
  end
 
  def query(query, bind = [])
    results = []
    # stmt
    stmt = [0].pack("L")
    ret = SQLite3_Prepare_V2.call(@db, query, -1, stmt, 0)
    raise "SQLite3 Prepare Failed!" if ret != SQLite3_OK
    stmt = stmt.unpack("L")[0]
    # bind
    bind.each_with_index do |var, index|
      case var
      when NilClass
        SQLite3_Bind_Null.call(stmt, index + 1)
      when Integer
        SQLite3_Bind_Int.call(stmt, index + 1, var)
      when String
        # 注意此处，如果不是 RMXP 需要改成 var.bytesize
        SQLite3_Bind_Text.call(stmt, index + 1, var, var.size, 0)
      when Float
        raise "SQLite3 Bind Failed!"
      end
    end
    # column counts
    col_count = SQLite3_Column_Count.call(stmt)
    # step
    until SQLite3_Step.call(stmt) == SQLite3_DONE
      row = (0...col_count).collect { |col|
        case SQLite3_Column_Type.call(stmt, col)
        when SQLite3_INTEGER
          SQLite3_Column_Int.call(stmt, col)
        when SQLite3_FLOAT
          SQLite3_Column_Blob.call(stmt, col).to_s.to_f
        when SQLite3_TEXT
          SQLite3_Column_Text.call(stmt, col).to_s
        when SQLite3_BLOB
          SQLite3_Column_Blob.call(stmt, col).to_s
        when SQLite3_NULL
          nil
        end
      }
      if block_given?
        yield row
      else
        results << row
      end
    end
    SQLite3_Finalize.call(stmt)
    if block_given?
      return nil
    else
      return results
    end
  end
 
  if SQLite3_Encrypt
    SQLite3_Key = Win32API.new(SQLite3_DLL, "sqlite3_key", "LPI", "I")
    SQLite3_Rekey = Win32API.new(SQLite3_DLL, "sqlite3_rekey", "LPI", "I")
 
    def key(key_string)
      SQLite3_Key.call(@db, key_string, key_string.size)
    end
 
    def rekey(key_string)
      SQLite3_Rekey.call(@db, key_string, key_string.size)
    end
 
    alias _key_initialize initialize
 
    def initialize(path, key = "")
      _key_initialize(path)
      if self.rekey(key) != 0
        self.key(key)
      end
    end
  end
end

# encoding: utf-8


# ---------------------------------------------------------


# SQLite3 For RMXP (by guoxiaomi)


# ---------------------------------------------------------


# version 0.3 at 2019/12/26


# ---------------------------------------------------------


# 注意：


# sqlite3_bind_double 无法正常使用，建议用格式化字符串拼接 SQL 语句


# sqlite3_column_double 在脚本里使用 sqlite3_column_blob 替代了


# bytesize，对更高版本的 RPG Maker，请搜索 "bytesize" 进行对应修改


#


# ---------------------------------------------------------


# 参考资料：


# 1. [url]https://renenyffenegger.ch/notes/development/databases/SQLite/VBA/index[/url]


# 2. [url]https://www.sqlite.org/download.html[/url]，下载 32 位的 DLL


# ---------------------------------------------------------


# 示例


# ---------------------------------------------------------


# 1. 打开数据库


# ---------------------------------------------------------


# db = SQLite3_RM.new("test.db")


# db = SQLite3_RM.new("test.db", "123456")


# ---------------------------------------------------------


# 2. 执行 SQL 语句


# ---------------------------------------------------------


# db.exec("create table user (id, name, gold)")


# ---------------------------------------------------------


# 3. query方法执行时可以用占位符代替部分内容，


#    如果是查询语句则返回查询结果的数组


# 4. query方法携带代码块时会将搜索的结果传给代码块，


#    此时不会返回结果数组，而是返回nil


# ---------------------------------------------------------


# db.query("insert into user values (?, ?, %.2f)" % [10], [0, "rmxp"])


# db.query("select * from user") { |row| p row }


# ---------------------------------------------------------


# 5. 关闭数据库


# ---------------------------------------------------------


# db.close


# ---------------------------------------------------------


 


require "win32api" if not defined? Win32API


 


class SQLite3_RM


  # ---------------------------------------------------------


  # 配置 DLL 的位置和是否加密


  # ---------------------------------------------------------


  SQLite3_DLL = "sqlite3.dll"


  SQLite3_Encrypt = true


  # ---------------------------------------------------------


 


  SQLite3_Open = Win32API.new(SQLite3_DLL, "sqlite3_open", "PP", "I")


  SQLite3_Exec = Win32API.new(SQLite3_DLL, "sqlite3_exec", "LPLLP", "I")


  SQLite3_Close = Win32API.new(SQLite3_DLL, "sqlite3_close", "L", "I")


 


  SQLite3_Prepare_V2 = Win32API.new(SQLite3_DLL, "sqlite3_prepare_v2", "LPLPP", "I")


  SQLite3_Bind_Int = Win32API.new(SQLite3_DLL, "sqlite3_bind_int", "LLL", "L")


  # SQLite3_Bind_Double = Win32API.new(SQLite3_DLL, "sqlite3_bind_double", "LLD", "L")


  SQLite3_Bind_Text = Win32API.new(SQLite3_DLL, "sqlite3_bind_text", "LLPIL", "L")


  SQLite3_Bind_Null = Win32API.new(SQLite3_DLL, "sqlite3_bind_null", "LL", "L")


  SQLite3_Bind_Blob = Win32API.new(SQLite3_DLL, "sqlite3_bind_blob", "LIPIL", "L")


 


  SQLite3_Step = Win32API.new(SQLite3_DLL, "sqlite3_step", "L", "I")


  SQLite3_Column_Count = Win32API.new(SQLite3_DLL, "sqlite3_column_count", "L", "I")


  SQLite3_Column_Type = Win32API.new(SQLite3_DLL, "sqlite3_column_type", "LI", "I")


  SQLite3_Column_Int = Win32API.new(SQLite3_DLL, "sqlite3_column_int", "LI", "I")


  # SQLite3_Column_Double = Win32API.new(SQLite3_DLL, "sqlite3_column_double", "LI", "D")


  SQLite3_Column_Text = Win32API.new(SQLite3_DLL, "sqlite3_column_text", "LI", "P")


  SQLite3_Column_Blob = Win32API.new(SQLite3_DLL, "sqlite3_column_blob", "LI", "P")


  SQLite3_Finalize = Win32API.new(SQLite3_DLL, "sqlite3_finalize", "L", "I")


 


  SQLite3_INTEGER = 1


  SQLite3_FLOAT = 2


  SQLite3_TEXT = 3


  SQLite3_BLOB = 4


  SQLite3_NULL = 5


  SQLite3_OK = 0


  SQLite3_DONE = 101


 


  def initialize(path)


    db = [0].pack("L")


    ret = SQLite3_Open.call(path, db)


    raise "SQLite3 Open Failed!" if ret != SQLite3_OK


    @db = db.unpack("L")[0]


  end


 


  def close


    ret = SQLite3_Close.call(@db)


    raise "SQLite3 Close Failed!" if ret != SQLite3_OK


  end


 


  def exec(query)


    ret = SQLite3_Exec.call(@db, query, 0, 0, 0)


    raise "SQLite3 Exec Failed!" if ret != SQLite3_OK


  end


 


  def query(query, bind = [])


    results = []


    # stmt


    stmt = [0].pack("L")


    ret = SQLite3_Prepare_V2.call(@db, query, -1, stmt, 0)


    raise "SQLite3 Prepare Failed!" if ret != SQLite3_OK


    stmt = stmt.unpack("L")[0]


    # bind


    bind.each_with_index do |var, index|


      case var


      when NilClass


        SQLite3_Bind_Null.call(stmt, index + 1)


      when Integer


        SQLite3_Bind_Int.call(stmt, index + 1, var)


      when String


        # 注意此处，如果不是 RMXP 需要改成 var.bytesize


        SQLite3_Bind_Text.call(stmt, index + 1, var, var.size, 0)


      when Float


        raise "SQLite3 Bind Failed!"


      end


    end


    # column counts


    col_count = SQLite3_Column_Count.call(stmt)


    # step


    until SQLite3_Step.call(stmt) == SQLite3_DONE


      row = (0...col_count).collect { |col|


        case SQLite3_Column_Type.call(stmt, col)


        when SQLite3_INTEGER


          SQLite3_Column_Int.call(stmt, col)


        when SQLite3_FLOAT


          SQLite3_Column_Blob.call(stmt, col).to_s.to_f


        when SQLite3_TEXT


          SQLite3_Column_Text.call(stmt, col).to_s


        when SQLite3_BLOB


          SQLite3_Column_Blob.call(stmt, col).to_s


        when SQLite3_NULL


          nil


        end


      }


      if block_given?


        yield row


      else


        results << row


      end


    end


    SQLite3_Finalize.call(stmt)


    if block_given?


      return nil


    else


      return results


    end


  end


 


  if SQLite3_Encrypt


    SQLite3_Key = Win32API.new(SQLite3_DLL, "sqlite3_key", "LPI", "I")


    SQLite3_Rekey = Win32API.new(SQLite3_DLL, "sqlite3_rekey", "LPI", "I")


 


    def key(key_string)


      SQLite3_Key.call(@db, key_string, key_string.size)


    end


 


    def rekey(key_string)


      SQLite3_Rekey.call(@db, key_string, key_string.size)


    end


 


    alias _key_initialize initialize


 


    def initialize(path, key = "")


      _key_initialize(path)


      if self.rekey(key) != 0


        self.key(key)


      end


    end


  end


end

帝王攻2020 · 发表于 2020-6-13 22:44:09

感谢大佬相助0.0

[原创发布] 使用SQLite3数据库

加入我们，或者，欢迎回来。

评分

评分

开拓者

帐号		自动登录	找回密码
密码			注册会员