# 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