現(xiàn)用戶管理部門(mén)管理模塊的設(shè)計(jì)與優(yōu)化)
做LabVIEW上位機(jī)這些年我越來(lái)越覺(jué)得“用戶管理”“部門(mén)管理”這兩個(gè)模塊是設(shè)備管理系統(tǒng)里最容易被低估的部分。最近我正好在一套測(cè)試工站管理軟件里完整實(shí)現(xiàn)了基于SQLite的用戶管理部門(mén)管理模塊涉及數(shù)據(jù)庫(kù)表設(shè)計(jì)、LabVIEW調(diào)用SQLite DLL、批量數(shù)據(jù)操作優(yōu)化等一系列問(wèn)題。這套模塊跑下來(lái)很穩(wěn)定而且不依賴MySQL之類(lèi)的數(shù)據(jù)庫(kù)服務(wù)一個(gè).db文件就搞定全部數(shù)據(jù)。如果你正在做設(shè)備管理上位機(jī)、MES工位軟件、測(cè)試數(shù)據(jù)管理系統(tǒng)需要在LabVIEW里搞一套能落地、不折騰的賬號(hào)權(quán)限和部門(mén)數(shù)據(jù)維護(hù)功能這篇文章值得收藏。1. 項(xiàng)目背景與整體設(shè)計(jì)思路1.1 為什么選SQLite而不是MySQL或Access工控機(jī)上的LabVIEW上位機(jī)部署環(huán)境五花八門(mén)有的機(jī)器沒(méi)聯(lián)網(wǎng)有的系統(tǒng)是Windows 7有的連數(shù)據(jù)庫(kù)驅(qū)動(dòng)都不齊。如果這時(shí)候上MySQL或PostgreSQL光是安裝服務(wù)、配置賬號(hào)、處理防火墻就夠喝一壺。Access雖然也是單文件但并發(fā)寫(xiě)入很容易鎖庫(kù)而且在64位LabVIEW下ODBC驅(qū)動(dòng)經(jīng)常出問(wèn)題。SQLite的優(yōu)勢(shì)非常直接不依賴服務(wù)不用安裝整個(gè)庫(kù)就是單個(gè).db文件官方提供了C語(yǔ)言接口LabVIEW通過(guò)CLFNCall Library Function Node調(diào)用一份sqlite3.dll就能操作支持標(biāo)準(zhǔn)SQL事務(wù)、外鍵、索引全都有。對(duì)單機(jī)應(yīng)用來(lái)說(shuō)SQLite的性能完全夠用。我這次的用戶和部門(mén)數(shù)據(jù)日常幾千行未來(lái)可擴(kuò)展至十萬(wàn)行級(jí)別SQLite處理起來(lái)毫無(wú)壓力。對(duì)比項(xiàng)SQLiteAccessMySQL/PostgreSQL部署復(fù)雜度極低一個(gè)DLLdb文件需要驅(qū)動(dòng)易損壞需要安裝服務(wù)運(yùn)維成本高并發(fā)能力單寫(xiě)多讀夠用較差易鎖庫(kù)強(qiáng)適合C/S架構(gòu)LabVIEW接入CLFN直接調(diào)用可控ODBC配置繁瑣需要ODBC或?qū)Ｓ霉ぞ甙缙脚_(tái)支持Windows/Linux基本W(wǎng)indows多平臺(tái)選SQLite還有一個(gè)現(xiàn)實(shí)原因用戶管理模塊通常和主程序一起啟動(dòng)、一起退出沒(méi)有高頻并行寫(xiě)入SQLite的事務(wù)模型完全能保證數(shù)據(jù)完整性。它的單文件特性也方便備份和遷移拷貝出去就是一個(gè)完整的數(shù)據(jù)庫(kù)。1.2 需求拆解用戶、部門(mén)、權(quán)限三個(gè)核心對(duì)象用戶管理和部門(mén)管理聽(tīng)上去簡(jiǎn)單但放在實(shí)際系統(tǒng)里需求其實(shí)要更細(xì)。我先從業(yè)務(wù)場(chǎng)景拆了一下用戶管理賬號(hào)登錄、密碼修改、新增用戶、編輯用戶信息、停用/啟用賬號(hào)、刪除用戶、按姓名或部門(mén)檢索用戶。部門(mén)管理部門(mén)樹(shù)形結(jié)構(gòu)部門(mén)可以分級(jí)新增子部門(mén)、修改部門(mén)名稱、刪除部門(mén)、部門(mén)排序刪除部門(mén)前必須處理該部門(mén)下的用戶不能產(chǎn)生臟數(shù)據(jù)。權(quán)限控制這里我簡(jiǎn)化成角色字段admin、operator、viewer三種。登錄后根據(jù)角色決定哪些界面按鈕可見(jiàn)比如只有admin能打開(kāi)用戶管理界面。權(quán)限這塊沒(méi)有做成復(fù)雜的RBAC因?yàn)樾枨蠓街灰唇巧珔^(qū)分操作范圍過(guò)度設(shè)計(jì)反而增加維護(hù)成本。關(guān)鍵點(diǎn)在于“用戶”和“部門(mén)”是強(qiáng)關(guān)聯(lián)關(guān)系每個(gè)用戶必須歸屬于一個(gè)部門(mén)而部門(mén)調(diào)整合并、刪除、改名會(huì)直接影響用戶。所以數(shù)據(jù)模型和操作流程要圍繞這個(gè)關(guān)聯(lián)設(shè)計(jì)比如刪除部門(mén)時(shí)先查詢是否有用戶掛在下面有就禁止刪除或強(qiáng)制轉(zhuǎn)移。1.3 總體架構(gòu)生產(chǎn)者消費(fèi)者模式與分層設(shè)計(jì)LabVIEW天生適合做界面和邏輯分離但很多開(kāi)發(fā)者在處理數(shù)據(jù)庫(kù)時(shí)偷懶直接在界面事件里同步執(zhí)行SQL導(dǎo)致點(diǎn)擊按鈕后界面卡死。這次模塊我沿用LabVIEW的經(jīng)典生產(chǎn)者消費(fèi)者架構(gòu)用戶操作增刪改查請(qǐng)求作為生產(chǎn)者放入隊(duì)列后臺(tái)消費(fèi)者線程從隊(duì)列取出命令執(zhí)行SQLite操作再把結(jié)果通過(guò)用戶事件反饋回界面。架構(gòu)分成三層界面層負(fù)責(zé)登錄窗口、用戶管理界面、部門(mén)樹(shù)控件、表格控件的事件處理和顯示刷新。服務(wù)層接收界面指令把功能請(qǐng)求轉(zhuǎn)換成具體的SQL語(yǔ)句或預(yù)編譯操作。數(shù)據(jù)層封裝SQLite連接、SQL執(zhí)行、事務(wù)管理、結(jié)果集解析對(duì)外只暴露Open/Close/Query/Execute等子VI。這樣的好處是界面線程永遠(yuǎn)不碰數(shù)據(jù)庫(kù)即使一次批量導(dǎo)入十萬(wàn)條數(shù)據(jù)界面也不會(huì)變成“未響應(yīng)”狀態(tài)。后續(xù)要替換數(shù)據(jù)庫(kù)只要改數(shù)據(jù)層就行。2. SQLite數(shù)據(jù)庫(kù)設(shè)計(jì)與LabVIEW接入方式2.1 表結(jié)構(gòu)設(shè)計(jì)部門(mén)表和用戶表使用DB Browser for SQLite就是那個(gè)免費(fèi)開(kāi)源工具可以很直觀地建表、看數(shù)據(jù)。前期設(shè)計(jì)階段我建議先用這個(gè)工具把表結(jié)構(gòu)跑通再回LabVIEW里寫(xiě)程序。我設(shè)計(jì)的核心表結(jié)構(gòu)如下部門(mén)表 departmentCREATE TABLE department ( dept_id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER NOT NULL DEFAULT 0, dept_name TEXT NOT NULL, dept_code TEXT UNIQUE, description TEXT, sort_order INTEGER NOT NULL DEFAULT 0 );用戶表 userCREATE TABLE user ( user_id INTEGER PRIMARY KEY AUTOINCREMENT, dept_id INTEGER NOT NULL, username TEXT NOT NULL UNIQUE, password TEXT NOT NULL, role TEXT NOT NULL DEFAULT operator, enabled INTEGER NOT NULL DEFAULT 1, email TEXT, remark TEXT, created_time TEXT DEFAULT (datetime(now, localtime)), FOREIGN KEY (dept_id) REFERENCES department(dept_id) );設(shè)計(jì)時(shí)要注意幾點(diǎn)parent_id為0表示根部門(mén)方便構(gòu)建樹(shù)形結(jié)構(gòu)一個(gè)系統(tǒng)通常有一個(gè)“公司總部”作為根節(jié)點(diǎn)不能瞎刪。username必須有UNIQUE約束賬號(hào)重復(fù)在數(shù)據(jù)庫(kù)層面就能攔住。password字段存的是MD5哈希值不是明文這一點(diǎn)下面會(huì)細(xì)說(shuō)。enabled字段用0/1表示停用/啟用比直接刪賬號(hào)更安全保留歷史記錄。created_time用SQLite的datetime(now,localtime)生成時(shí)區(qū)不會(huì)出現(xiàn)8小時(shí)偏差。索引不能漏。部門(mén)表按父級(jí)查找時(shí)經(jīng)常用到用戶表按部門(mén)過(guò)濾、按用戶名搜索都很常見(jiàn)。所以至少建立兩個(gè)索引CREATE INDEX idx_user_dept ON user(dept_id); CREATE INDEX idx_dept_parent ON department(parent_id);外鍵約束記得在每次連接后執(zhí)行PRAGMA foreign_keys ON;不開(kāi)啟外鍵的話FOREIGN KEY只是擺設(shè)你完全能插入一個(gè)不存在的dept_id。這個(gè)坑我踩過(guò)最后排查臟數(shù)據(jù)查了半天。2.2 LabVIEW訪問(wèn)SQLite的三種主流方案方案一LabVIEW Database Connectivity Toolkit加ODBC驅(qū)動(dòng)。這是最“官方”的路徑但你要在部署機(jī)上額外裝SQLite ODBC驅(qū)動(dòng)還要配DSN或者寫(xiě)連接串。好處是能用現(xiàn)成的DB Tools子VI缺點(diǎn)是連接配置和驅(qū)動(dòng)版本問(wèn)題多性能也不是最好。方案二用CLFN直接調(diào)用官方sqlite3.dll。這是我這次采用的方式。sqlite3.dll是公開(kāi)、免授權(quán)的和項(xiàng)目一起分發(fā)沒(méi)有任何法律風(fēng)險(xiǎn)。只要CLFN參數(shù)配置正確性能、可控性都是最好的。方案三用第三方封裝好的LabVIEW庫(kù)比如LavaSQL或VIPM里的SQLite工具包。這確實(shí)省事但第三方庫(kù)更新慢且遇到bug你很難自己定位。我早期用過(guò)一次換了一個(gè)LabVIEW版本后DLL加載失敗差點(diǎn)被坑。綜合評(píng)估長(zhǎng)期維護(hù)的項(xiàng)目我會(huì)選方案二。表結(jié)構(gòu)是固定的SQL是可控的CLFN配置一次封裝成子VI后面用起來(lái)和調(diào)用普通工具包沒(méi)什么區(qū)別但心里踏實(shí)。2.3 CLFN封裝SQLite核心函數(shù)的具體配置用CLFN調(diào)sqlite3.dll不是所有函數(shù)都要封裝。我實(shí)際用到的核心函數(shù)只有這些sqlite3_open打開(kāi)數(shù)據(jù)庫(kù)文件返回?cái)?shù)據(jù)庫(kù)句柄。sqlite3_close關(guān)閉句柄釋放資源。sqlite3_exec執(zhí)行不帶參數(shù)的SQL比如建表、PRAGMA、簡(jiǎn)單的增刪改。sqlite3_prepare_v2預(yù)編譯SQL語(yǔ)句支持“?”占位符。sqlite3_bind_text給預(yù)編譯語(yǔ)句綁定字符串參數(shù)。sqlite3_step執(zhí)行一步INSERT/UPDATE/DELETE時(shí)調(diào)用一次即可查詢時(shí)循環(huán)調(diào)用直到返回SQLITE_DONE。sqlite3_finalize釋放預(yù)編譯語(yǔ)句。sqlite3_errmsg拿錯(cuò)誤信息。在CLFN函數(shù)配置里最容易翻車(chē)的是指針參數(shù)。sqlite3_open的原型是int sqlite3_open(const char* filename, sqlite3** ppDb);LabVIEW側(cè)傳入數(shù)據(jù)庫(kù)路徑字符串應(yīng)配置為C String Pointer第二個(gè)參數(shù)ppDb在CLFN里要配置為“Pointer to Value”并且用整型數(shù)輸出數(shù)據(jù)庫(kù)句柄。signed int指針默認(rèn)8字節(jié)還是32位取決于sqlite3用多大指針但作為句柄我們一般將其作為一個(gè)i32輸出實(shí)際地址存入整型后續(xù)再傳給其它函數(shù)時(shí)也要按相同類(lèi)型配置。位寬不一致是很多崩潰的元兇。無(wú)論是sqlite3_open還是sqlite3_execDLL返回int型錯(cuò)誤碼。我封裝的SQLite Open.vi長(zhǎng)期保持一個(gè)輸出端子dbHandle和errorCode。SQLite函數(shù)只在成功時(shí)返回0其它值都可以轉(zhuǎn)成可讀錯(cuò)誤信息串方便排查。查詢語(yǔ)句的執(zhí)行流程不建議用sqlite3_exec加回調(diào)CLFN里回調(diào)函數(shù)配置很別扭而是用prepare/step循環(huán)。封裝一個(gè)“SQLite Query.vi”執(zhí)行流程sqlite3_prepare_v2(dbHandle, sqlString, -1, stmtHandle, NULL) 循環(huán) { sqlite3_step(stmtHandle); 若返回值 SQLITE_ROW則取出列數(shù)據(jù); 若返回值 SQLITE_DONE則結(jié)束; } sqlite3_finalize(stmtHandle);取出列數(shù)據(jù)時(shí)sqlite3_column_text返回字符串指針LabVIEW側(cè)在CLFN里配置為C String Pointer并指定返回緩沖區(qū)大小例如返回255個(gè)字符。超過(guò)255就截?cái)嗨宰霾樵兘缑鏁r(shí)要預(yù)估最大字段長(zhǎng)度。3. 用戶管理部門(mén)管理模塊的功能實(shí)現(xiàn)3.1 部門(mén)管理樹(shù)形結(jié)構(gòu)顯示與增刪改部門(mén)管理界面上最常見(jiàn)的控件是Tree樹(shù)形控件。數(shù)據(jù)來(lái)源是department表通過(guò)parent_id形成父子關(guān)系。加載時(shí)我先把所有部門(mén)一次性查出來(lái)放進(jìn)數(shù)組然后在內(nèi)存里按parent_id構(gòu)建層級(jí)關(guān)系再填充到Tree控件。不要每次展開(kāi)節(jié)點(diǎn)都查一次數(shù)據(jù)庫(kù)那樣性能差而且邏輯亂。新增部門(mén)的SQL很簡(jiǎn)單INSERT INTO department (parent_id, dept_name, dept_code, sort_order) VALUES (?, ?, ?, ?);注意dept_code有UNIQUE約束如果重復(fù)插入會(huì)返回SQLITE_CONSTRAINT錯(cuò)誤。新增前先查一次同名同編碼是否存在或者直接在錯(cuò)誤處理里給用戶提示我用的是后者更省一次查詢。修改部門(mén)相對(duì)簡(jiǎn)單但要注意同步更新其它相關(guān)字段。刪除部門(mén)的處理不能莽。我做的流程是查該部門(mén)下是否有子部門(mén)SELECT COUNT(*) FROM department WHERE parent_id ?;查該部門(mén)下是否有用戶SELECT COUNT(*) FROM user WHERE dept_id ?;如果兩者都為零允許刪除。如果存在子部門(mén)禁止刪除提示用戶先處理子部門(mén)。如果存在用戶提供“轉(zhuǎn)移到上級(jí)部門(mén)”或“選擇新部門(mén)”的操作并在事務(wù)中執(zhí)行?！稗D(zhuǎn)移用戶”這一步必須用事務(wù)BEGIN; UPDATE user SET dept_id ? WHERE dept_id ?; DELETE FROM department WHERE dept_id ?; COMMIT;如果某一步失敗回滾事務(wù)保證用戶不會(huì)變成“無(wú)部門(mén)狀態(tài)”。這個(gè)邏輯我在LabVIEW里封裝成一個(gè)“DeleteDepartment.vi”內(nèi)部依次執(zhí)行三條SQL任何一個(gè)出錯(cuò)則執(zhí)行ROLLBACK。3.2 用戶管理賬號(hào)維護(hù)與密碼處理用戶管理功能涉及列表查詢、新增、編輯、刪除、停用、重置密碼。列表查詢我加了兩個(gè)過(guò)濾條件按用戶名模糊搜索、按部門(mén)篩選。聯(lián)合查詢的表來(lái)自u(píng)ser同時(shí)要顯示部門(mén)名稱所以SQL寫(xiě)成SELECT u.user_id, u.username, d.dept_name, u.role, u.enabled, u.created_time, u.remark FROM user u LEFT JOIN department d ON u.dept_id d.dept_id WHERE (? IS NULL OR u.username LIKE % || ? || %) AND (? IS NULL OR u.dept_id ?) ORDER BY u.user_id;參數(shù)綁定在CLFN里可以一次綁定多個(gè)注意編號(hào)從1開(kāi)始。LabVIEW里用“?”占位符時(shí)需要綁定文本。用NULL條件構(gòu)造動(dòng)態(tài)查詢時(shí)先判斷是否過(guò)濾再?zèng)Q定SQL拼接這在實(shí)際代碼里比在SQL里處理NULL更直觀。密碼處理是用戶模塊的底線。最簡(jiǎn)單可靠的方案是存入數(shù)據(jù)庫(kù)的不是明文而是MD5哈希值。LabVIEW實(shí)現(xiàn)MD5的方法我試過(guò)三種調(diào)用Windows CryptoAPI比較復(fù)雜、用VIPM里的OpenG Crypto庫(kù)簡(jiǎn)單但增加依賴、自己封裝一個(gè)MD5子VI。我最終選擇的是自己在LabVIEW里實(shí)現(xiàn)MD5算法后來(lái)發(fā)現(xiàn)工程中直接引用一個(gè)成熟的MD5.vi即可。校驗(yàn)密碼時(shí)把用戶輸入的密碼同樣做MD5再和數(shù)據(jù)庫(kù)里的哈希值比較。如果擔(dān)心MD5不夠安全可以加鹽例如MD5(MD5(password) salt)但實(shí)際工控系統(tǒng)內(nèi)網(wǎng)環(huán)境MD5已經(jīng)夠用。新增用戶的SQLINSERT INTO user (dept_id, username, password, role, enabled, email, remark) VALUES (?, ?, ?, ?, ?, ?, ?);新增前要檢查用戶名是否唯一這個(gè)約束數(shù)據(jù)庫(kù)層有但為了給用戶友好提示我會(huì)先執(zhí)行一次SELECT COUNT(*)。停用用戶不是刪除而是UPDATE user SET enabled 0 WHERE user_id ?;這樣既保留賬號(hào)歷史又禁止該用戶登錄。3.3 部門(mén)與用戶聯(lián)動(dòng)操作與界面交互用戶和部門(mén)的聯(lián)動(dòng)是這個(gè)模塊最需要花心思的地方。界面上我設(shè)計(jì)成左側(cè)部門(mén)樹(shù)、右側(cè)用戶表格。點(diǎn)擊部門(mén)樹(shù)節(jié)點(diǎn)右側(cè)表格自動(dòng)刷新為該部門(mén)下用戶。事件流程是Tree控件Value Change事件觸發(fā)后把當(dāng)前選中部門(mén)ID放到隊(duì)列后臺(tái)消費(fèi)者收到后執(zhí)行用戶查詢SQL再通過(guò)用戶事件把結(jié)果發(fā)回界面更新表格。整個(gè)過(guò)程不阻塞界面。新增用戶時(shí)部門(mén)下拉框的數(shù)據(jù)來(lái)自department表。如果用戶管理界面打開(kāi)時(shí)部門(mén)數(shù)據(jù)被修改過(guò)需要重新加載下拉框。我用一個(gè)簡(jiǎn)單的辦法每次打開(kāi)用戶管理界面的“刷新”按鈕同時(shí)刷新部門(mén)下拉框和用戶表避免不一致。部門(mén)合并或調(diào)整時(shí)最簡(jiǎn)單的是在“修改部門(mén)”功能里提供一個(gè)選擇當(dāng)前部門(mén)用戶是否隨部門(mén)遷移的選項(xiàng)。實(shí)現(xiàn)上就是在同一個(gè)事務(wù)中UPDATE user表。這里如果缺少事務(wù)一條失敗另一條成功數(shù)據(jù)就分叉了。另外操作日志我也補(bǔ)了一下。每個(gè)管理員操作完新增、修改、刪除后會(huì)往獨(dú)立的一張op_log表里寫(xiě)一條帶時(shí)間戳的記錄。這個(gè)不是硬需求但后期追責(zé)非常好用。表和用戶表沒(méi)有強(qiáng)外鍵這樣即使刪了用戶操作日志仍然保留。4. 數(shù)據(jù)操作性能優(yōu)化與問(wèn)題排查實(shí)錄4.1 十萬(wàn)條數(shù)據(jù)下SQLite的表現(xiàn)與優(yōu)化措施很多人擔(dān)心SQLite在十萬(wàn)條數(shù)據(jù)下會(huì)卡。我實(shí)測(cè)過(guò)如果是帶索引的等值查詢比如“SELECT * FROM user WHERE dept_id 1”毫秒級(jí)返回如果是模糊搜索且沒(méi)有索引比如“LIKE %abc%”可能幾十到幾百毫秒這取決于數(shù)據(jù)分布。十萬(wàn)條對(duì)SQLite真不叫事真正會(huì)出問(wèn)題的是寫(xiě)入方式。以前有個(gè)開(kāi)發(fā)同事用一條條INSERT插入測(cè)試數(shù)據(jù)幾千條花了半分鐘。問(wèn)題不在SQLite而在沒(méi)有用事務(wù)。SQLite默認(rèn)每條INSERT都是獨(dú)立事務(wù)每插一條都要做同步、寫(xiě)日志、提交當(dāng)然慢。改成“手動(dòng)事務(wù)預(yù)編譯”后效果非常明顯BEGIN TRANSACTION; INSERT INTO user (dept_id, username, password, role) VALUES (?, ?, ?, ?); COMMIT;在LabVIEW里不能在CLFN中一條Exec里寫(xiě)多行實(shí)際上多行SQL在一個(gè)sqlite3_exec里執(zhí)行沒(méi)問(wèn)題但參數(shù)綁定不行所以要用prepare/step。我封裝了一個(gè)“SQLite PrepareInsert.vi”流程是調(diào)用sqlite3_exec(dbHandle, “BEGIN TRANSACTION;”, ...)prepare_v2得到stmt句柄循環(huán)綁定每個(gè)字段執(zhí)行sqlite3_step每滿一定條數(shù)我常用5000條執(zhí)行一次COMMIT重新BEGIN最后COMMIT并release。用這個(gè)方式向user表插入一萬(wàn)條記錄我本地固態(tài)硬盤(pán)上大約在1秒以內(nèi)。十萬(wàn)條大概也就十秒左右完全可接受。批量插入時(shí)盡量別讓事務(wù)太長(zhǎng)避免數(shù)據(jù)庫(kù)文件增長(zhǎng)過(guò)大以及內(nèi)存占用。查詢優(yōu)化方面超過(guò)十萬(wàn)條數(shù)據(jù)時(shí)建議分頁(yè)加載。用戶管理界面表格控件沒(méi)必要一次顯示十萬(wàn)行。我使用SELECT ... LIMIT 50 OFFSET 0;配合總行數(shù)查詢和翻頁(yè)按鈕體驗(yàn)好很多。大量的分頁(yè)計(jì)算由SQLite完成LabVIEW側(cè)只接收當(dāng)前頁(yè)數(shù)據(jù)內(nèi)存占用穩(wěn)定。4.2 常見(jiàn)錯(cuò)誤與排查速查表這幾個(gè)月遇到不少問(wèn)題挑幾個(gè)典型的說(shuō)一說(shuō)?，F(xiàn)象可能原因解決辦法“unable to open database file”數(shù)據(jù)庫(kù)路徑不存在、權(quán)限不足、目錄不存在檢查路徑分隔符和目錄權(quán)限確保上層目錄存在不要用未創(chuàng)建的空路徑“file is not a database”打開(kāi)了一個(gè)不是SQLite的文件或文件損壞確認(rèn)文件確實(shí)由SQLite創(chuàng)建嘗試用DB Browser打開(kāi)修復(fù)“database is locked”另一個(gè)連接正在寫(xiě)庫(kù)或事務(wù)未提交檢查是否有連接沒(méi)關(guān)閉用生產(chǎn)者消費(fèi)者串行化寫(xiě)入避免多線程同時(shí)寫(xiě)中文亂碼LabVIEW字符串編碼與SQLite的UTF-8不匹配寫(xiě)入和讀取時(shí)統(tǒng)一做UTF-8轉(zhuǎn)換在CLFN中配置“UTF-8 String”或在子VI里將LabVIEW String轉(zhuǎn)UTF-8 byte array加載sqlite3.dll失敗位數(shù)不匹配或DLL缺失32位LabVIEW用32位DLL64位用64位DLL發(fā)布時(shí)把對(duì)應(yīng)DLL放在exe目錄LabVIEW直接崩潰CLFN參數(shù)類(lèi)型或指針配置錯(cuò)誤逐條核對(duì)函數(shù)原型字符串用C String Pointer輸出句柄用Pointer to Value避免返回局部指針“database is locked”這個(gè)最坑。有次我忘了關(guān)閉一個(gè)預(yù)編譯語(yǔ)句結(jié)果事務(wù)一直掛在WAL文件上后續(xù)所有寫(xiě)入都失敗。排查方法很簡(jiǎn)單用一個(gè)數(shù)據(jù)庫(kù)查看工具DB Browser for SQLite看是否有連接占用或者在代碼里任何Open之后確保有Close任何Prepare之后確保有Finalize。還有一次LabVIEW崩潰原因是sqlite3_column_text返回的指針是DLL內(nèi)部管理的我在CLFN里配置成了字符串?dāng)?shù)組返回試圖讓LabVIEW復(fù)制出來(lái)結(jié)果內(nèi)存訪問(wèn)越界。正確做法是在CLFN返回類(lèi)型中選“C String Pointer”并設(shè)置緩沖區(qū)大小為足夠長(zhǎng)或者只是讀取一次立即復(fù)制到LabVIEW字符串再做后續(xù)處理。這個(gè)細(xì)節(jié)必須謹(jǐn)記。4.3 部署與維護(hù)建議發(fā)布LabVIEW上位機(jī)時(shí)別只拷貝EXE要把數(shù)據(jù)庫(kù)文件、sqlite3.dll等相關(guān)文件按目錄結(jié)構(gòu)打包。我的習(xí)慣是程序根目錄/ 上位機(jī).exe sqlite3.dll Data/ system.db Config/ config.ini數(shù)據(jù)庫(kù)路徑不要硬編碼成絕對(duì)路徑應(yīng)該讀取程序所在目錄的相對(duì)路徑。這樣成果拷到任何一臺(tái)工控機(jī)上都能跑不會(huì)因?yàn)楸P(pán)符不一樣導(dǎo)致找不到數(shù)據(jù)庫(kù)。備份也是最容易被忽略的。SQLite支持在線備份API但LabVIEW里調(diào)用那套API太繁瑣。簡(jiǎn)單做法是在程序退出時(shí)或每天第一次啟動(dòng)時(shí)把db文件復(fù)制到備份目錄。如果數(shù)據(jù)庫(kù)開(kāi)啟WAL模式只復(fù)制.db文件不一定包含全部最新數(shù)據(jù)還要復(fù)制同目錄的.db-wal和.db-shm文件。更穩(wěn)妥的做法是程序退出前執(zhí)行一次“PRAGMA wal_checkpoint(TRUNCATE);”合并WAL文件后再?gòu)?fù)制db文件。sqlite3.dll是公共領(lǐng)域可以放心隨程序分發(fā)。唯一要注意的是版本位寬32位LabVIEW必須帶32位sqlite3.dll64位必須帶64位。我曾經(jīng)演示時(shí)拿錯(cuò)DLL現(xiàn)場(chǎng)演示數(shù)據(jù)庫(kù)打不開(kāi)尷尬得一塌糊涂。做到這里用戶管理、部門(mén)管理和數(shù)據(jù)操作的基礎(chǔ)鏈路已經(jīng)通了。我在實(shí)際工程中的習(xí)慣是每個(gè)模塊寫(xiě)一份簡(jiǎn)短的接口說(shuō)明比如“GetUserList.vi輸入條件、輸出結(jié)果集格式”這樣別人接手或自己半年后再看都不需要重新讀代碼。LabVIEW里數(shù)據(jù)庫(kù)模塊不像C#那樣有現(xiàn)成的類(lèi)庫(kù)但封裝成清晰子VI之后用起來(lái)一點(diǎn)都不差。畢竟最穩(wěn)定的系統(tǒng)往往是每個(gè)小模塊都足夠簡(jiǎn)單、職責(zé)單一。 LabVIEW基于SQLite實(shí)現(xiàn)用戶管理部門(mén)管理模塊的設(shè)計(jì)與優(yōu)化
做LabVIEW上位機(jī)這些年我越來(lái)越覺(jué)得“用戶管理”“部門(mén)管理”這兩個(gè)模塊是設(shè)備管理系統(tǒng)里最容易被低估的部分。最近我正好在一套測(cè)試工站管理軟件里完整實(shí)現(xiàn)了基于SQLite的用戶管理部門(mén)管理模塊涉及數(shù)據(jù)庫(kù)表設(shè)計(jì)、LabVIEW調(diào)用SQLite DLL、批量數(shù)據(jù)操作優(yōu)化等一系列問(wèn)題。這套模塊跑下來(lái)很穩(wěn)定而且不依賴MySQL之類(lèi)的數(shù)據(jù)庫(kù)服務(wù)一個(gè).db文件就搞定全部數(shù)據(jù)。如果你正在做設(shè)備管理上位機(jī)、MES工位軟件、測(cè)試數(shù)據(jù)管理系統(tǒng)需要在LabVIEW里搞一套能落地、不折騰的賬號(hào)權(quán)限和部門(mén)數(shù)據(jù)維護(hù)功能這篇文章值得收藏。1. 項(xiàng)目背景與整體設(shè)計(jì)思路1.1 為什么選SQLite而不是MySQL或Access工控機(jī)上的LabVIEW上位機(jī)部署環(huán)境五花八門(mén)有的機(jī)器沒(méi)聯(lián)網(wǎng)有的系統(tǒng)是Windows 7有的連數(shù)據(jù)庫(kù)驅(qū)動(dòng)都不齊。如果這時(shí)候上MySQL或PostgreSQL光是安裝服務(wù)、配置賬號(hào)、處理防火墻就夠喝一壺。Access雖然也是單文件但并發(fā)寫(xiě)入很容易鎖庫(kù)而且在64位LabVIEW下ODBC驅(qū)動(dòng)經(jīng)常出問(wèn)題。SQLite的優(yōu)勢(shì)非常直接不依賴服務(wù)不用安裝整個(gè)庫(kù)就是單個(gè).db文件官方提供了C語(yǔ)言接口LabVIEW通過(guò)CLFNCall Library Function Node調(diào)用一份sqlite3.dll就能操作支持標(biāo)準(zhǔn)SQL事務(wù)、外鍵、索引全都有。對(duì)單機(jī)應(yīng)用來(lái)說(shuō)SQLite的性能完全夠用。我這次的用戶和部門(mén)數(shù)據(jù)日常幾千行未來(lái)可擴(kuò)展至十萬(wàn)行級(jí)別SQLite處理起來(lái)毫無(wú)壓力。對(duì)比項(xiàng)SQLiteAccessMySQL/PostgreSQL部署復(fù)雜度極低一個(gè)DLLdb文件需要驅(qū)動(dòng)易損壞需要安裝服務(wù)運(yùn)維成本高并發(fā)能力單寫(xiě)多讀夠用較差易鎖庫(kù)強(qiáng)適合C/S架構(gòu)LabVIEW接入CLFN直接調(diào)用可控ODBC配置繁瑣需要ODBC或?qū)Ｓ霉ぞ甙缙脚_(tái)支持Windows/Linux基本W(wǎng)indows多平臺(tái)選SQLite還有一個(gè)現(xiàn)實(shí)原因用戶管理模塊通常和主程序一起啟動(dòng)、一起退出沒(méi)有高頻并行寫(xiě)入SQLite的事務(wù)模型完全能保證數(shù)據(jù)完整性。它的單文件特性也方便備份和遷移拷貝出去就是一個(gè)完整的數(shù)據(jù)庫(kù)。1.2 需求拆解用戶、部門(mén)、權(quán)限三個(gè)核心對(duì)象用戶管理和部門(mén)管理聽(tīng)上去簡(jiǎn)單但放在實(shí)際系統(tǒng)里需求其實(shí)要更細(xì)。我先從業(yè)務(wù)場(chǎng)景拆了一下用戶管理賬號(hào)登錄、密碼修改、新增用戶、編輯用戶信息、停用/啟用賬號(hào)、刪除用戶、按姓名或部門(mén)檢索用戶。部門(mén)管理部門(mén)樹(shù)形結(jié)構(gòu)部門(mén)可以分級(jí)新增子部門(mén)、修改部門(mén)名稱、刪除部門(mén)、部門(mén)排序刪除部門(mén)前必須處理該部門(mén)下的用戶不能產(chǎn)生臟數(shù)據(jù)。權(quán)限控制這里我簡(jiǎn)化成角色字段admin、operator、viewer三種。登錄后根據(jù)角色決定哪些界面按鈕可見(jiàn)比如只有admin能打開(kāi)用戶管理界面。權(quán)限這塊沒(méi)有做成復(fù)雜的RBAC因?yàn)樾枨蠓街灰唇巧珔^(qū)分操作范圍過(guò)度設(shè)計(jì)反而增加維護(hù)成本。關(guān)鍵點(diǎn)在于“用戶”和“部門(mén)”是強(qiáng)關(guān)聯(lián)關(guān)系每個(gè)用戶必須歸屬于一個(gè)部門(mén)而部門(mén)調(diào)整合并、刪除、改名會(huì)直接影響用戶。所以數(shù)據(jù)模型和操作流程要圍繞這個(gè)關(guān)聯(lián)設(shè)計(jì)比如刪除部門(mén)時(shí)先查詢是否有用戶掛在下面有就禁止刪除或強(qiáng)制轉(zhuǎn)移。1.3 總體架構(gòu)生產(chǎn)者消費(fèi)者模式與分層設(shè)計(jì)LabVIEW天生適合做界面和邏輯分離但很多開(kāi)發(fā)者在處理數(shù)據(jù)庫(kù)時(shí)偷懶直接在界面事件里同步執(zhí)行SQL導(dǎo)致點(diǎn)擊按鈕后界面卡死。這次模塊我沿用LabVIEW的經(jīng)典生產(chǎn)者消費(fèi)者架構(gòu)用戶操作增刪改查請(qǐng)求作為生產(chǎn)者放入隊(duì)列后臺(tái)消費(fèi)者線程從隊(duì)列取出命令執(zhí)行SQLite操作再把結(jié)果通過(guò)用戶事件反饋回界面。架構(gòu)分成三層界面層負(fù)責(zé)登錄窗口、用戶管理界面、部門(mén)樹(shù)控件、表格控件的事件處理和顯示刷新。服務(wù)層接收界面指令把功能請(qǐng)求轉(zhuǎn)換成具體的SQL語(yǔ)句或預(yù)編譯操作。數(shù)據(jù)層封裝SQLite連接、SQL執(zhí)行、事務(wù)管理、結(jié)果集解析對(duì)外只暴露Open/Close/Query/Execute等子VI。這樣的好處是界面線程永遠(yuǎn)不碰數(shù)據(jù)庫(kù)即使一次批量導(dǎo)入十萬(wàn)條數(shù)據(jù)界面也不會(huì)變成“未響應(yīng)”狀態(tài)。后續(xù)要替換數(shù)據(jù)庫(kù)只要改數(shù)據(jù)層就行。2. SQLite數(shù)據(jù)庫(kù)設(shè)計(jì)與LabVIEW接入方式2.1 表結(jié)構(gòu)設(shè)計(jì)部門(mén)表和用戶表使用DB Browser for SQLite就是那個(gè)免費(fèi)開(kāi)源工具可以很直觀地建表、看數(shù)據(jù)。前期設(shè)計(jì)階段我建議先用這個(gè)工具把表結(jié)構(gòu)跑通再回LabVIEW里寫(xiě)程序。我設(shè)計(jì)的核心表結(jié)構(gòu)如下部門(mén)表 departmentCREATE TABLE department ( dept_id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER NOT NULL DEFAULT 0, dept_name TEXT NOT NULL, dept_code TEXT UNIQUE, description TEXT, sort_order INTEGER NOT NULL DEFAULT 0 );用戶表 userCREATE TABLE user ( user_id INTEGER PRIMARY KEY AUTOINCREMENT, dept_id INTEGER NOT NULL, username TEXT NOT NULL UNIQUE, password TEXT NOT NULL, role TEXT NOT NULL DEFAULT operator, enabled INTEGER NOT NULL DEFAULT 1, email TEXT, remark TEXT, created_time TEXT DEFAULT (datetime(now, localtime)), FOREIGN KEY (dept_id) REFERENCES department(dept_id) );設(shè)計(jì)時(shí)要注意幾點(diǎn)parent_id為0表示根部門(mén)方便構(gòu)建樹(shù)形結(jié)構(gòu)一個(gè)系統(tǒng)通常有一個(gè)“公司總部”作為根節(jié)點(diǎn)不能瞎刪。username必須有UNIQUE約束賬號(hào)重復(fù)在數(shù)據(jù)庫(kù)層面就能攔住。password字段存的是MD5哈希值不是明文這一點(diǎn)下面會(huì)細(xì)說(shuō)。enabled字段用0/1表示停用/啟用比直接刪賬號(hào)更安全保留歷史記錄。created_time用SQLite的datetime(now,localtime)生成時(shí)區(qū)不會(huì)出現(xiàn)8小時(shí)偏差。索引不能漏。部門(mén)表按父級(jí)查找時(shí)經(jīng)常用到用戶表按部門(mén)過(guò)濾、按用戶名搜索都很常見(jiàn)。所以至少建立兩個(gè)索引CREATE INDEX idx_user_dept ON user(dept_id); CREATE INDEX idx_dept_parent ON department(parent_id);外鍵約束記得在每次連接后執(zhí)行PRAGMA foreign_keys ON;不開(kāi)啟外鍵的話FOREIGN KEY只是擺設(shè)你完全能插入一個(gè)不存在的dept_id。這個(gè)坑我踩過(guò)最后排查臟數(shù)據(jù)查了半天。2.2 LabVIEW訪問(wèn)SQLite的三種主流方案方案一LabVIEW Database Connectivity Toolkit加ODBC驅(qū)動(dòng)。這是最“官方”的路徑但你要在部署機(jī)上額外裝SQLite ODBC驅(qū)動(dòng)還要配DSN或者寫(xiě)連接串。好處是能用現(xiàn)成的DB Tools子VI缺點(diǎn)是連接配置和驅(qū)動(dòng)版本問(wèn)題多性能也不是最好。方案二用CLFN直接調(diào)用官方sqlite3.dll。這是我這次采用的方式。sqlite3.dll是公開(kāi)、免授權(quán)的和項(xiàng)目一起分發(fā)沒(méi)有任何法律風(fēng)險(xiǎn)。只要CLFN參數(shù)配置正確性能、可控性都是最好的。方案三用第三方封裝好的LabVIEW庫(kù)比如LavaSQL或VIPM里的SQLite工具包。這確實(shí)省事但第三方庫(kù)更新慢且遇到bug你很難自己定位。我早期用過(guò)一次換了一個(gè)LabVIEW版本后DLL加載失敗差點(diǎn)被坑。綜合評(píng)估長(zhǎng)期維護(hù)的項(xiàng)目我會(huì)選方案二。表結(jié)構(gòu)是固定的SQL是可控的CLFN配置一次封裝成子VI后面用起來(lái)和調(diào)用普通工具包沒(méi)什么區(qū)別但心里踏實(shí)。2.3 CLFN封裝SQLite核心函數(shù)的具體配置用CLFN調(diào)sqlite3.dll不是所有函數(shù)都要封裝。我實(shí)際用到的核心函數(shù)只有這些sqlite3_open打開(kāi)數(shù)據(jù)庫(kù)文件返回?cái)?shù)據(jù)庫(kù)句柄。sqlite3_close關(guān)閉句柄釋放資源。sqlite3_exec執(zhí)行不帶參數(shù)的SQL比如建表、PRAGMA、簡(jiǎn)單的增刪改。sqlite3_prepare_v2預(yù)編譯SQL語(yǔ)句支持“?”占位符。sqlite3_bind_text給預(yù)編譯語(yǔ)句綁定字符串參數(shù)。sqlite3_step執(zhí)行一步INSERT/UPDATE/DELETE時(shí)調(diào)用一次即可查詢時(shí)循環(huán)調(diào)用直到返回SQLITE_DONE。sqlite3_finalize釋放預(yù)編譯語(yǔ)句。sqlite3_errmsg拿錯(cuò)誤信息。在CLFN函數(shù)配置里最容易翻車(chē)的是指針參數(shù)。sqlite3_open的原型是int sqlite3_open(const char* filename, sqlite3** ppDb);LabVIEW側(cè)傳入數(shù)據(jù)庫(kù)路徑字符串應(yīng)配置為C String Pointer第二個(gè)參數(shù)ppDb在CLFN里要配置為“Pointer to Value”并且用整型數(shù)輸出數(shù)據(jù)庫(kù)句柄。signed int指針默認(rèn)8字節(jié)還是32位取決于sqlite3用多大指針但作為句柄我們一般將其作為一個(gè)i32輸出實(shí)際地址存入整型后續(xù)再傳給其它函數(shù)時(shí)也要按相同類(lèi)型配置。位寬不一致是很多崩潰的元兇。無(wú)論是sqlite3_open還是sqlite3_execDLL返回int型錯(cuò)誤碼。我封裝的SQLite Open.vi長(zhǎng)期保持一個(gè)輸出端子dbHandle和errorCode。SQLite函數(shù)只在成功時(shí)返回0其它值都可以轉(zhuǎn)成可讀錯(cuò)誤信息串方便排查。查詢語(yǔ)句的執(zhí)行流程不建議用sqlite3_exec加回調(diào)CLFN里回調(diào)函數(shù)配置很別扭而是用prepare/step循環(huán)。封裝一個(gè)“SQLite Query.vi”執(zhí)行流程sqlite3_prepare_v2(dbHandle, sqlString, -1, stmtHandle, NULL) 循環(huán) { sqlite3_step(stmtHandle); 若返回值 SQLITE_ROW則取出列數(shù)據(jù); 若返回值 SQLITE_DONE則結(jié)束; } sqlite3_finalize(stmtHandle);取出列數(shù)據(jù)時(shí)sqlite3_column_text返回字符串指針LabVIEW側(cè)在CLFN里配置為C String Pointer并指定返回緩沖區(qū)大小例如返回255個(gè)字符。超過(guò)255就截?cái)嗨宰霾樵兘缑鏁r(shí)要預(yù)估最大字段長(zhǎng)度。3. 用戶管理部門(mén)管理模塊的功能實(shí)現(xiàn)3.1 部門(mén)管理樹(shù)形結(jié)構(gòu)顯示與增刪改部門(mén)管理界面上最常見(jiàn)的控件是Tree樹(shù)形控件。數(shù)據(jù)來(lái)源是department表通過(guò)parent_id形成父子關(guān)系。加載時(shí)我先把所有部門(mén)一次性查出來(lái)放進(jìn)數(shù)組然后在內(nèi)存里按parent_id構(gòu)建層級(jí)關(guān)系再填充到Tree控件。不要每次展開(kāi)節(jié)點(diǎn)都查一次數(shù)據(jù)庫(kù)那樣性能差而且邏輯亂。新增部門(mén)的SQL很簡(jiǎn)單INSERT INTO department (parent_id, dept_name, dept_code, sort_order) VALUES (?, ?, ?, ?);注意dept_code有UNIQUE約束如果重復(fù)插入會(huì)返回SQLITE_CONSTRAINT錯(cuò)誤。新增前先查一次同名同編碼是否存在或者直接在錯(cuò)誤處理里給用戶提示我用的是后者更省一次查詢。修改部門(mén)相對(duì)簡(jiǎn)單但要注意同步更新其它相關(guān)字段。刪除部門(mén)的處理不能莽。我做的流程是查該部門(mén)下是否有子部門(mén)SELECT COUNT(*) FROM department WHERE parent_id ?;查該部門(mén)下是否有用戶SELECT COUNT(*) FROM user WHERE dept_id ?;如果兩者都為零允許刪除。如果存在子部門(mén)禁止刪除提示用戶先處理子部門(mén)。如果存在用戶提供“轉(zhuǎn)移到上級(jí)部門(mén)”或“選擇新部門(mén)”的操作并在事務(wù)中執(zhí)行?！稗D(zhuǎn)移用戶”這一步必須用事務(wù)BEGIN; UPDATE user SET dept_id ? WHERE dept_id ?; DELETE FROM department WHERE dept_id ?; COMMIT;如果某一步失敗回滾事務(wù)保證用戶不會(huì)變成“無(wú)部門(mén)狀態(tài)”。這個(gè)邏輯我在LabVIEW里封裝成一個(gè)“DeleteDepartment.vi”內(nèi)部依次執(zhí)行三條SQL任何一個(gè)出錯(cuò)則執(zhí)行ROLLBACK。3.2 用戶管理賬號(hào)維護(hù)與密碼處理用戶管理功能涉及列表查詢、新增、編輯、刪除、停用、重置密碼。列表查詢我加了兩個(gè)過(guò)濾條件按用戶名模糊搜索、按部門(mén)篩選。聯(lián)合查詢的表來(lái)自u(píng)ser同時(shí)要顯示部門(mén)名稱所以SQL寫(xiě)成SELECT u.user_id, u.username, d.dept_name, u.role, u.enabled, u.created_time, u.remark FROM user u LEFT JOIN department d ON u.dept_id d.dept_id WHERE (? IS NULL OR u.username LIKE % || ? || %) AND (? IS NULL OR u.dept_id ?) ORDER BY u.user_id;參數(shù)綁定在CLFN里可以一次綁定多個(gè)注意編號(hào)從1開(kāi)始。LabVIEW里用“?”占位符時(shí)需要綁定文本。用NULL條件構(gòu)造動(dòng)態(tài)查詢時(shí)先判斷是否過(guò)濾再?zèng)Q定SQL拼接這在實(shí)際代碼里比在SQL里處理NULL更直觀。密碼處理是用戶模塊的底線。最簡(jiǎn)單可靠的方案是存入數(shù)據(jù)庫(kù)的不是明文而是MD5哈希值。LabVIEW實(shí)現(xiàn)MD5的方法我試過(guò)三種調(diào)用Windows CryptoAPI比較復(fù)雜、用VIPM里的OpenG Crypto庫(kù)簡(jiǎn)單但增加依賴、自己封裝一個(gè)MD5子VI。我最終選擇的是自己在LabVIEW里實(shí)現(xiàn)MD5算法后來(lái)發(fā)現(xiàn)工程中直接引用一個(gè)成熟的MD5.vi即可。校驗(yàn)密碼時(shí)把用戶輸入的密碼同樣做MD5再和數(shù)據(jù)庫(kù)里的哈希值比較。如果擔(dān)心MD5不夠安全可以加鹽例如MD5(MD5(password) salt)但實(shí)際工控系統(tǒng)內(nèi)網(wǎng)環(huán)境MD5已經(jīng)夠用。新增用戶的SQLINSERT INTO user (dept_id, username, password, role, enabled, email, remark) VALUES (?, ?, ?, ?, ?, ?, ?);新增前要檢查用戶名是否唯一這個(gè)約束數(shù)據(jù)庫(kù)層有但為了給用戶友好提示我會(huì)先執(zhí)行一次SELECT COUNT(*)。停用用戶不是刪除而是UPDATE user SET enabled 0 WHERE user_id ?;這樣既保留賬號(hào)歷史又禁止該用戶登錄。3.3 部門(mén)與用戶聯(lián)動(dòng)操作與界面交互用戶和部門(mén)的聯(lián)動(dòng)是這個(gè)模塊最需要花心思的地方。界面上我設(shè)計(jì)成左側(cè)部門(mén)樹(shù)、右側(cè)用戶表格。點(diǎn)擊部門(mén)樹(shù)節(jié)點(diǎn)右側(cè)表格自動(dòng)刷新為該部門(mén)下用戶。事件流程是Tree控件Value Change事件觸發(fā)后把當(dāng)前選中部門(mén)ID放到隊(duì)列后臺(tái)消費(fèi)者收到后執(zhí)行用戶查詢SQL再通過(guò)用戶事件把結(jié)果發(fā)回界面更新表格。整個(gè)過(guò)程不阻塞界面。新增用戶時(shí)部門(mén)下拉框的數(shù)據(jù)來(lái)自department表。如果用戶管理界面打開(kāi)時(shí)部門(mén)數(shù)據(jù)被修改過(guò)需要重新加載下拉框。我用一個(gè)簡(jiǎn)單的辦法每次打開(kāi)用戶管理界面的“刷新”按鈕同時(shí)刷新部門(mén)下拉框和用戶表避免不一致。部門(mén)合并或調(diào)整時(shí)最簡(jiǎn)單的是在“修改部門(mén)”功能里提供一個(gè)選擇當(dāng)前部門(mén)用戶是否隨部門(mén)遷移的選項(xiàng)。實(shí)現(xiàn)上就是在同一個(gè)事務(wù)中UPDATE user表。這里如果缺少事務(wù)一條失敗另一條成功數(shù)據(jù)就分叉了。另外操作日志我也補(bǔ)了一下。每個(gè)管理員操作完新增、修改、刪除后會(huì)往獨(dú)立的一張op_log表里寫(xiě)一條帶時(shí)間戳的記錄。這個(gè)不是硬需求但后期追責(zé)非常好用。表和用戶表沒(méi)有強(qiáng)外鍵這樣即使刪了用戶操作日志仍然保留。4. 數(shù)據(jù)操作性能優(yōu)化與問(wèn)題排查實(shí)錄4.1 十萬(wàn)條數(shù)據(jù)下SQLite的表現(xiàn)與優(yōu)化措施很多人擔(dān)心SQLite在十萬(wàn)條數(shù)據(jù)下會(huì)卡。我實(shí)測(cè)過(guò)如果是帶索引的等值查詢比如“SELECT * FROM user WHERE dept_id 1”毫秒級(jí)返回如果是模糊搜索且沒(méi)有索引比如“LIKE %abc%”可能幾十到幾百毫秒這取決于數(shù)據(jù)分布。十萬(wàn)條對(duì)SQLite真不叫事真正會(huì)出問(wèn)題的是寫(xiě)入方式。以前有個(gè)開(kāi)發(fā)同事用一條條INSERT插入測(cè)試數(shù)據(jù)幾千條花了半分鐘。問(wèn)題不在SQLite而在沒(méi)有用事務(wù)。SQLite默認(rèn)每條INSERT都是獨(dú)立事務(wù)每插一條都要做同步、寫(xiě)日志、提交當(dāng)然慢。改成“手動(dòng)事務(wù)預(yù)編譯”后效果非常明顯BEGIN TRANSACTION; INSERT INTO user (dept_id, username, password, role) VALUES (?, ?, ?, ?); COMMIT;在LabVIEW里不能在CLFN中一條Exec里寫(xiě)多行實(shí)際上多行SQL在一個(gè)sqlite3_exec里執(zhí)行沒(méi)問(wèn)題但參數(shù)綁定不行所以要用prepare/step。我封裝了一個(gè)“SQLite PrepareInsert.vi”流程是調(diào)用sqlite3_exec(dbHandle, “BEGIN TRANSACTION;”, ...)prepare_v2得到stmt句柄循環(huán)綁定每個(gè)字段執(zhí)行sqlite3_step每滿一定條數(shù)我常用5000條執(zhí)行一次COMMIT重新BEGIN最后COMMIT并release。用這個(gè)方式向user表插入一萬(wàn)條記錄我本地固態(tài)硬盤(pán)上大約在1秒以內(nèi)。十萬(wàn)條大概也就十秒左右完全可接受。批量插入時(shí)盡量別讓事務(wù)太長(zhǎng)避免數(shù)據(jù)庫(kù)文件增長(zhǎng)過(guò)大以及內(nèi)存占用。查詢優(yōu)化方面超過(guò)十萬(wàn)條數(shù)據(jù)時(shí)建議分頁(yè)加載。用戶管理界面表格控件沒(méi)必要一次顯示十萬(wàn)行。我使用SELECT ... LIMIT 50 OFFSET 0;配合總行數(shù)查詢和翻頁(yè)按鈕體驗(yàn)好很多。大量的分頁(yè)計(jì)算由SQLite完成LabVIEW側(cè)只接收當(dāng)前頁(yè)數(shù)據(jù)內(nèi)存占用穩(wěn)定。4.2 常見(jiàn)錯(cuò)誤與排查速查表這幾個(gè)月遇到不少問(wèn)題挑幾個(gè)典型的說(shuō)一說(shuō)?，F(xiàn)象可能原因解決辦法“unable to open database file”數(shù)據(jù)庫(kù)路徑不存在、權(quán)限不足、目錄不存在檢查路徑分隔符和目錄權(quán)限確保上層目錄存在不要用未創(chuàng)建的空路徑“file is not a database”打開(kāi)了一個(gè)不是SQLite的文件或文件損壞確認(rèn)文件確實(shí)由SQLite創(chuàng)建嘗試用DB Browser打開(kāi)修復(fù)“database is locked”另一個(gè)連接正在寫(xiě)庫(kù)或事務(wù)未提交檢查是否有連接沒(méi)關(guān)閉用生產(chǎn)者消費(fèi)者串行化寫(xiě)入避免多線程同時(shí)寫(xiě)中文亂碼LabVIEW字符串編碼與SQLite的UTF-8不匹配寫(xiě)入和讀取時(shí)統(tǒng)一做UTF-8轉(zhuǎn)換在CLFN中配置“UTF-8 String”或在子VI里將LabVIEW String轉(zhuǎn)UTF-8 byte array加載sqlite3.dll失敗位數(shù)不匹配或DLL缺失32位LabVIEW用32位DLL64位用64位DLL發(fā)布時(shí)把對(duì)應(yīng)DLL放在exe目錄LabVIEW直接崩潰CLFN參數(shù)類(lèi)型或指針配置錯(cuò)誤逐條核對(duì)函數(shù)原型字符串用C String Pointer輸出句柄用Pointer to Value避免返回局部指針“database is locked”這個(gè)最坑。有次我忘了關(guān)閉一個(gè)預(yù)編譯語(yǔ)句結(jié)果事務(wù)一直掛在WAL文件上后續(xù)所有寫(xiě)入都失敗。排查方法很簡(jiǎn)單用一個(gè)數(shù)據(jù)庫(kù)查看工具DB Browser for SQLite看是否有連接占用或者在代碼里任何Open之后確保有Close任何Prepare之后確保有Finalize。還有一次LabVIEW崩潰原因是sqlite3_column_text返回的指針是DLL內(nèi)部管理的我在CLFN里配置成了字符串?dāng)?shù)組返回試圖讓LabVIEW復(fù)制出來(lái)結(jié)果內(nèi)存訪問(wèn)越界。正確做法是在CLFN返回類(lèi)型中選“C String Pointer”并設(shè)置緩沖區(qū)大小為足夠長(zhǎng)或者只是讀取一次立即復(fù)制到LabVIEW字符串再做后續(xù)處理。這個(gè)細(xì)節(jié)必須謹(jǐn)記。4.3 部署與維護(hù)建議發(fā)布LabVIEW上位機(jī)時(shí)別只拷貝EXE要把數(shù)據(jù)庫(kù)文件、sqlite3.dll等相關(guān)文件按目錄結(jié)構(gòu)打包。我的習(xí)慣是程序根目錄/ 上位機(jī).exe sqlite3.dll Data/ system.db Config/ config.ini數(shù)據(jù)庫(kù)路徑不要硬編碼成絕對(duì)路徑應(yīng)該讀取程序所在目錄的相對(duì)路徑。這樣成果拷到任何一臺(tái)工控機(jī)上都能跑不會(huì)因?yàn)楸P(pán)符不一樣導(dǎo)致找不到數(shù)據(jù)庫(kù)。備份也是最容易被忽略的。SQLite支持在線備份API但LabVIEW里調(diào)用那套API太繁瑣。簡(jiǎn)單做法是在程序退出時(shí)或每天第一次啟動(dòng)時(shí)把db文件復(fù)制到備份目錄。如果數(shù)據(jù)庫(kù)開(kāi)啟WAL模式只復(fù)制.db文件不一定包含全部最新數(shù)據(jù)還要復(fù)制同目錄的.db-wal和.db-shm文件。更穩(wěn)妥的做法是程序退出前執(zhí)行一次“PRAGMA wal_checkpoint(TRUNCATE);”合并WAL文件后再?gòu)?fù)制db文件。sqlite3.dll是公共領(lǐng)域可以放心隨程序分發(fā)。唯一要注意的是版本位寬32位LabVIEW必須帶32位sqlite3.dll64位必須帶64位。我曾經(jīng)演示時(shí)拿錯(cuò)DLL現(xiàn)場(chǎng)演示數(shù)據(jù)庫(kù)打不開(kāi)尷尬得一塌糊涂。做到這里用戶管理、部門(mén)管理和數(shù)據(jù)操作的基礎(chǔ)鏈路已經(jīng)通了。我在實(shí)際工程中的習(xí)慣是每個(gè)模塊寫(xiě)一份簡(jiǎn)短的接口說(shuō)明比如“GetUserList.vi輸入條件、輸出結(jié)果集格式”這樣別人接手或自己半年后再看都不需要重新讀代碼。LabVIEW里數(shù)據(jù)庫(kù)模塊不像C#那樣有現(xiàn)成的類(lèi)庫(kù)但封裝成清晰子VI之后用起來(lái)一點(diǎn)都不差。畢竟最穩(wěn)定的系統(tǒng)往往是每個(gè)小模塊都足夠簡(jiǎn)單、職責(zé)單一。