使用SQLite3数据库

guoxiaomi

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

友情上传 WXSQLite 3.30.1 的 dll 文件 sqlite3.zip (690.86 KB, 下载次数: 84)

2019-12-27 06:38 上传

点击文件名下载附件

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

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

[pre lang="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. https://renenyffenegger.ch/notes ... es/SQLite/VBA/index
# 2. https://www.sqlite.org/download.html，下载 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
[/pre]

帝王攻2020

感谢大佬相助0.0