qt實現-給SQLITE添加自訂函數

標籤：

需要使用sqlite裡的password對某個欄位進行加密，由於使用的sqlite是由QT封裝好的QSqlDatabase,沒有發現載入擴充函數的方法，所以自己實現了一個。

在網上也沒找到相應的參考，就自己查官方文檔解決了。本篇文章主要是sqlite如何載入外部的函數，並沒有password函數的實現，我將寫好的函數產生了一個動態庫，由程式動態載入。

#include <iostream>#include <QString>#include <QtSql/QSqlQuery>#include <QtSql/QSqlDatabase>#include <QtSql/QSqlQuery>#include <QtSql/QSqlError>#include <QtSql/QSqlDriver>#include <QVariant>#include <sqlite3.h>#include <string.h>using namespace std;void insert_database(QSqlDatabase& database,QString name){    QSqlQuery query(database);    if(!query.exec("insert into data(name) values(password(‘"+name+"‘) )"))        cout<<query.lastError().text().toStdString()<<std::endl;    else        database.commit();}int main(){    QSqlDatabase database=QSqlDatabase::addDatabase("QSQLITE");    database.setDatabaseName("data.db");    if(!database.open())    {        cout<<"open database failure"<<std::endl;        return 0;    }    QVariant handle=database.driver()->handle();    if(!handle.isValid())    {        cout<<"handle not valid"<<endl;        return 0;    }    sqlite3* sqlhandle=*static_cast<sqlite3**>(handle.data());    char * error=(char*)sqlite3_malloc(1024);    sqlite3_enable_load_extension(sqlhandle,1);    if(SQLITE_OK==sqlite3_load_extension(sqlhandle,"/home/quanwei/desktop/my-documents/code/qt/loadsqlitefunction/password.so",0,&error));    else    cout<<"error: "<<error<<std::endl;    sqlite3_free(error);    insert_database(database,"hello");    database.close();    return 0;}

資料庫結構也放出來參考

CREATE TABLE data(id integer primary key,name text);

此程式用到的庫的支援：
QtCore,QtSql,sqlite3

具體介面官方文檔有說明

Loading An Extension An SQLite extension is a shared library or DLL. To load it, you need to supply SQLite with the name of the file containing the shared library or DLL and an entry point to initialize the extension. In C code, this information is supplied using the sqlite3_load_extension() API. See the documentation on that routine for additional information. Note that different operating systems use different filename suffixes for their shared libraries. Windows use ".dll", Mac uses ".dylib", and most unixes other than mac use ".so". If you want to make your code portable, you can omit the suffix from the shared library filename and the appropriate suffix will be added automatically by the sqlite3_load_extension() interface.

不過由於預設load_extension是處於關閉狀態，所以需要調用sqlite3_enable_load_extension開啟擴充功能

在sqlite3的shell裡可以通過執行

sqlite3> .load ./password

來載入一個動態庫函數，但是由於擴充函數沒有儲存在資料庫中，所以每次開啟這個資料庫中都要載入一次才能使用自訂函數。

參考：https://www.sqlite.org/loadext.html

http://www.quweiji.com/qt%E5%AE%9E%E7%8E%B0-%E7%BB%99sqlite%E6%B7%BB%E5%8A%A0%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0/

qt實現-給SQLITE添加自訂函數