Project1

标题: 使用SQLite3数据库 [打印本页]
作者: guoxiaomi    时间: 2019-12-27 03:40
标题: 使用SQLite3数据库
本帖最后由 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, 下载次数: 90) ，这个DLL提供了加密的功能：
1. 在调用 new 方法的时候传入字符串形式的密钥，没有密钥会自动设置
2. 修改密码需要在 new 之后，调用 rekey 函数。new 的时候仍然需要传入旧的正确的密钥

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

RUBY 代码复制
  1. # encoding: utf-8
  2. # ---------------------------------------------------------
  3. # SQLite3 For RMXP (by guoxiaomi)
  4. # ---------------------------------------------------------
  5. # version 0.3 at 2019/12/26
  6. # ---------------------------------------------------------
  7. # 注意：
  8. # sqlite3_bind_double 无法正常使用，建议用格式化字符串拼接 SQL 语句
  9. # sqlite3_column_double 在脚本里使用 sqlite3_column_blob 替代了
  10. # bytesize，对更高版本的 RPG Maker，请搜索 "bytesize" 进行对应修改
  11. #
  12. # ---------------------------------------------------------
  13. # 参考资料：
  14. # 1. [url]https://renenyffenegger.ch/notes/development/databases/SQLite/VBA/index[/url]
  15. # 2. [url]https://www.sqlite.org/download.html[/url]，下载 32 位的 DLL
  16. # ---------------------------------------------------------
  17. # 示例
  18. # ---------------------------------------------------------
  19. # 1. 打开数据库
  20. # ---------------------------------------------------------
  21. # db = SQLite3_RM.new("test.db")
  22. # db = SQLite3_RM.new("test.db", "123456")
  23. # ---------------------------------------------------------
  24. # 2. 执行 SQL 语句
  25. # ---------------------------------------------------------
  26. # db.exec("create table user (id, name, gold)")
  27. # ---------------------------------------------------------
  28. # 3. query方法执行时可以用占位符代替部分内容，
  29. #    如果是查询语句则返回查询结果的数组
  30. # 4. query方法携带代码块时会将搜索的结果传给代码块，
  31. #    此时不会返回结果数组，而是返回nil
  32. # ---------------------------------------------------------
  33. # db.query("insert into user values (?, ?, %.2f)" % [10], [0, "rmxp"])
  34. # db.query("select * from user") { |row| p row }
  35. # ---------------------------------------------------------
  36. # 5. 关闭数据库
  37. # ---------------------------------------------------------
  38. # db.close
  39. # ---------------------------------------------------------
  40.  
  41. require "win32api" if not defined? Win32API
  42.  
  43. class SQLite3_RM
  44.   # ---------------------------------------------------------
  45.   # 配置 DLL 的位置和是否加密
  46.   # ---------------------------------------------------------
  47.   SQLite3_DLL = "sqlite3.dll"
  48.   SQLite3_Encrypt = true
  49.   # ---------------------------------------------------------
  50.  
  51.   SQLite3_Open = Win32API.new(SQLite3_DLL, "sqlite3_open", "PP", "I")
  52.   SQLite3_Exec = Win32API.new(SQLite3_DLL, "sqlite3_exec", "LPLLP", "I")
  53.   SQLite3_Close = Win32API.new(SQLite3_DLL, "sqlite3_close", "L", "I")
  54.  
  55.   SQLite3_Prepare_V2 = Win32API.new(SQLite3_DLL, "sqlite3_prepare_v2", "LPLPP", "I")
  56.   SQLite3_Bind_Int = Win32API.new(SQLite3_DLL, "sqlite3_bind_int", "LLL", "L")
  57.   # SQLite3_Bind_Double = Win32API.new(SQLite3_DLL, "sqlite3_bind_double", "LLD", "L")
  58.   SQLite3_Bind_Text = Win32API.new(SQLite3_DLL, "sqlite3_bind_text", "LLPIL", "L")
  59.   SQLite3_Bind_Null = Win32API.new(SQLite3_DLL, "sqlite3_bind_null", "LL", "L")
  60.   SQLite3_Bind_Blob = Win32API.new(SQLite3_DLL, "sqlite3_bind_blob", "LIPIL", "L")
  61.  
  62.   SQLite3_Step = Win32API.new(SQLite3_DLL, "sqlite3_step", "L", "I")
  63.   SQLite3_Column_Count = Win32API.new(SQLite3_DLL, "sqlite3_column_count", "L", "I")
  64.   SQLite3_Column_Type = Win32API.new(SQLite3_DLL, "sqlite3_column_type", "LI", "I")
  65.   SQLite3_Column_Int = Win32API.new(SQLite3_DLL, "sqlite3_column_int", "LI", "I")
  66.   # SQLite3_Column_Double = Win32API.new(SQLite3_DLL, "sqlite3_column_double", "LI", "D")
  67.   SQLite3_Column_Text = Win32API.new(SQLite3_DLL, "sqlite3_column_text", "LI", "P")
  68.   SQLite3_Column_Blob = Win32API.new(SQLite3_DLL, "sqlite3_column_blob", "LI", "P")
  69.   SQLite3_Finalize = Win32API.new(SQLite3_DLL, "sqlite3_finalize", "L", "I")
  70.  
  71.   SQLite3_INTEGER = 1
  72.   SQLite3_FLOAT = 2
  73.   SQLite3_TEXT = 3
  74.   SQLite3_BLOB = 4
  75.   SQLite3_NULL = 5
  76.   SQLite3_OK = 0
  77.   SQLite3_DONE = 101
  78.  
  79.   def initialize(path)
  80.     db = [0].pack("L")
  81.     ret = SQLite3_Open.call(path, db)
  82.     raise "SQLite3 Open Failed!" if ret != SQLite3_OK
  83.     @db = db.unpack("L")[0]
  84.   end
  85.  
  86.   def close
  87.     ret = SQLite3_Close.call(@db)
  88.     raise "SQLite3 Close Failed!" if ret != SQLite3_OK
  89.   end
  90.  
  91.   def exec(query)
  92.     ret = SQLite3_Exec.call(@db, query, 0, 0, 0)
  93.     raise "SQLite3 Exec Failed!" if ret != SQLite3_OK
  94.   end
  95.  
  96.   def query(query, bind = [])
  97.     results = []
  98.     # stmt
  99.     stmt = [0].pack("L")
  100.     ret = SQLite3_Prepare_V2.call(@db, query, -1, stmt, 0)
  101.     raise "SQLite3 Prepare Failed!" if ret != SQLite3_OK
  102.     stmt = stmt.unpack("L")[0]
  103.     # bind
  104.     bind.each_with_index do |var, index|
  105.       case var
  106.       when NilClass
  107.         SQLite3_Bind_Null.call(stmt, index + 1)
  108.       when Integer
  109.         SQLite3_Bind_Int.call(stmt, index + 1, var)
  110.       when String
  111.         # 注意此处，如果不是 RMXP 需要改成 var.bytesize
  112.         SQLite3_Bind_Text.call(stmt, index + 1, var, var.size, 0)
  113.       when Float
  114.         raise "SQLite3 Bind Failed!"
  115.       end
  116.     end
  117.     # column counts
  118.     col_count = SQLite3_Column_Count.call(stmt)
  119.     # step
  120.     until SQLite3_Step.call(stmt) == SQLite3_DONE
  121.       row = (0...col_count).collect { |col|
  122.         case SQLite3_Column_Type.call(stmt, col)
  123.         when SQLite3_INTEGER
  124.           SQLite3_Column_Int.call(stmt, col)
  125.         when SQLite3_FLOAT
  126.           SQLite3_Column_Blob.call(stmt, col).to_s.to_f
  127.         when SQLite3_TEXT
  128.           SQLite3_Column_Text.call(stmt, col).to_s
  129.         when SQLite3_BLOB
  130.           SQLite3_Column_Blob.call(stmt, col).to_s
  131.         when SQLite3_NULL
  132.           nil
  133.         end
  134.       }
  135.       if block_given?
  136.         yield row
  137.       else
  138.         results << row
  139.       end
  140.     end
  141.     SQLite3_Finalize.call(stmt)
  142.     if block_given?
  143.       return nil
  144.     else
  145.       return results
  146.     end
  147.   end
  148.  
  149.   if SQLite3_Encrypt
  150.     SQLite3_Key = Win32API.new(SQLite3_DLL, "sqlite3_key", "LPI", "I")
  151.     SQLite3_Rekey = Win32API.new(SQLite3_DLL, "sqlite3_rekey", "LPI", "I")
  152.  
  153.     def key(key_string)
  154.       SQLite3_Key.call(@db, key_string, key_string.size)
  155.     end
  156.  
  157.     def rekey(key_string)
  158.       SQLite3_Rekey.call(@db, key_string, key_string.size)
  159.     end
  160.  
  161.     alias _key_initialize initialize
  162.  
  163.     def initialize(path, key = "")
  164.       _key_initialize(path)
  165.       if self.rekey(key) != 0
  166.         self.key(key)
  167.       end
  168.     end
  169.   end
  170. end
作者: 帝王攻2020    时间: 2020-6-13 22:44
感谢大佬相助0.0



欢迎光临 Project1 (https://rpg.blue/) Powered by Discuz! X3.1