SQLite 自定义函数,聚合,排序规则

// // 测试代码 #include <cassert> #include <fstream> #include <iostream> #include <regex> #include <sstream> #include <string> #include "Memory.h" #include "Rand.h" #include "sqlite3.h" using namespace std; //#define OUTPUT_FILE #if defined(OUTPUT_FILE) #define ERR fout << "[" << __func__ << ":" << __LINE__ << "] Error! " #define INFO fout #define DEBUG fout #else #define ERR cerr << "[" << __func__ << ":" << __LINE__ << "] Error! " #define INFO cout #define DEBUG cerr #endif #define MEMINFO(msg) Memory::print(msg) #if defined(OUTPUT_FILE) fstream fout; #endif void initOutputStream() { #if defined(OUTPUT_FILE) fout.open("F:/Sqlite/mysql/log.txt", fstream::ios_base::out | fstream::ios_base::trunc); #endif } void testClean(void* p) { DEBUG << "clean:" << reinterpret_cast<int>(p) << endl; } sqlite3* openDB(const string& file) { sqlite3* db{}; int ret = sqlite3_open_v2(file.c_str(), &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, 0); if (SQLITE_OK != ret) { ERR << "sqlite3_open_v2 failed." << endl; return nullptr; } return db; } bool closeDB(sqlite3* db) { int ret = sqlite3_close_v2(db); if (SQLITE_OK != ret) { ERR << "sqlite3_close_v2 failed." << endl; return false; } return true; } bool execSQL(sqlite3* db, const string& sql) { char* errmsg{}; int ret = sqlite3_exec(db, sql.c_str(), 0, 0, &errmsg); if (SQLITE_OK != ret) { ERR << "sqlite3_exec failed." << errmsg << endl; } sqlite3_free(errmsg); return SQLITE_OK == ret; } bool printSQL(sqlite3* db, const string& sql) { int ret{}; sqlite3_stmt* stmt{}; ret = sqlite3_prepare_v2(db, sql.c_str(), sql.length(), &stmt, 0); if (SQLITE_OK != ret) { ERR << "sqlite3_prepare_v2 failed." << sqlite3_errmsg(db) << endl; return false; } INFO << sql << endl; int colCount = sqlite3_column_count(stmt); if (colCount > 0) { for (int col = 0; col < colCount; ) { INFO << sqlite3_column_name(stmt, col); if (++col != colCount) { INFO << ‘\t‘; } } INFO << endl; } do { ret = sqlite3_step(stmt); if (SQLITE_ROW != ret) { break; } for (int col = 0; col < colCount; ) { const char* text = reinterpret_cast<const char*>(sqlite3_column_text(stmt, col)); INFO << (text ? text : ""); if (++col != colCount) { INFO << ‘\t‘; } } INFO << endl; } while (true); if (SQLITE_DONE != ret) { ERR << "sqlite3_step failed." << sqlite3_errmsg(db) << endl; } ret = sqlite3_finalize(stmt); if (SQLITE_OK != ret) { ERR << "sqlite3_finalize failed." << sqlite3_errmsg(db) << endl; return false; } return true; } // SQLite 函数 void echo(sqlite3_context* ctx, int argc, sqlite3_value** argv) { assert(1 == argc); // DEBUG << "user data:" << reinterpret_cast<int>(sqlite3_user_data(ctx)) << endl; const char* text{reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))}; sqlite3_result_text(ctx, text, -1, 0); } void strcat_column(sqlite3_context* ctx, int argc, sqlite3_value** argv) { const char* delimiter{"\t"}; const int DELIMITER_LEN = strlen(delimiter); int len = 0; for (int i = 0; i < argc; ++i) { len += sqlite3_value_bytes(argv[i]); len += DELIMITER_LEN; } char* buf = static_cast<char*>(sqlite3_malloc(len)); int pos = 0; for (int i = 0; i < argc; ) { int bytes = sqlite3_value_bytes(argv[i]); memcpy(buf + pos, sqlite3_value_blob(argv[i]), bytes); pos += bytes; if (++i != argc) { memcpy(buf + pos, delimiter, DELIMITER_LEN); pos += DELIMITER_LEN; } } buf[pos] = 0; sqlite3_result_text(ctx, buf, -1, 0); } // SQLite 聚合函数 struct AggregateCharData { int len{}; char* buf{}; }; void strcat_step(sqlite3_context* ctx, int argc, sqlite3_value** argv) { assert(2 == argc); AggregateCharData* pData{static_cast<AggregateCharData*>(sqlite3_aggregate_context(ctx, sizeof(AggregateCharData)))}; if (!pData) { ERR << "Alloc AggregateData failed!" << endl; return; } int valueLen = sqlite3_value_bytes(argv[0]); if (!pData->buf) { pData->buf = static_cast<char*>(sqlite3_malloc(valueLen + 1)); } else { int delimiterLen = sqlite3_value_bytes(argv[1]); int len = valueLen + delimiterLen + pData->len + 1; pData->buf = static_cast<char*>(sqlite3_realloc(pData->buf, len)); memcpy(pData->buf + pData->len, sqlite3_value_blob(argv[1]), delimiterLen); pData->len += delimiterLen; } memcpy(pData->buf + pData->len, sqlite3_value_blob(argv[0]), valueLen); pData->len += valueLen; } void strcat_final(sqlite3_context* ctx) { AggregateCharData* pData{static_cast<AggregateCharData*>(sqlite3_aggregate_context(ctx, sizeof(AggregateCharData)))}; if (!pData || !pData->buf) { sqlite3_result_text(ctx, pData->buf, pData->len, sqlite3_free); } } /** * SQLite 排序示例,将值拆分成非全数字组成的前缀,和全数字组成的后缀,前缀不同时,按字典排序;前缀相同时,后缀按数字大小排序 **/ int compareName(void*, int lhsLen, const void* lhsData, int rhsLen, const void* rhsData) { string lText(static_cast<const char*>(lhsData), 0, lhsLen); string rText(static_cast<const char*>(rhsData), 0, rhsLen); regex reg("^(.*[^\\d]+)(\\d+)$|^(\\d+)$"); smatch lMatch; smatch rMatch; if (!regex_match(lText, lMatch, reg) || !regex_match(rText, rMatch, reg)) { return 0; } string lPrefix = lMatch.str(1); string lSuffix = lPrefix.empty() ? lMatch.str(3) : lMatch.str(2); string rPrefix = rMatch.str(1); string rSuffix = rPrefix.empty() ? rMatch.str(3) : rMatch.str(2); if (lPrefix != rPrefix) { return lPrefix.compare(rPrefix); } // 过滤后缀前面的字符0 static const string POSITIVE_NUMBER {"123456789"}; size_t pos = lSuffix.find_first_of(POSITIVE_NUMBER); if (string::npos != pos && 0 != pos) { lSuffix = lSuffix.substr(pos); } pos = rSuffix.find_first_of(POSITIVE_NUMBER); if (string::npos != pos && 0 != pos) { rSuffix = rSuffix.substr(pos); } // DEBUG << "suffix:" << lSuffix << "/" << lText << ", " << rSuffix << "/" << rText << endl; int lSuffixLen = lSuffix.length(); int rSuffixLen = rSuffix.length(); if (lSuffixLen != rSuffixLen) { return lSuffixLen - rSuffixLen; } for (int i = 0; i < lSuffixLen; ++i) { if (lSuffix.at(i) != rSuffix.at(i)) { return lSuffix.at(i) - rSuffix.at(i); } } return 0; } string randNumString() { static const string NUMBERS{"012345678900"}; string ret; int len = Rand::rand(3, 10); for (int i = 0; i < len; ++i) { ret.push_back(NUMBERS.at(Rand::rand(0, NUMBERS.length() - 1))); } return ret; } string randPrefix() { static const string PREFIX_STR{"01234567890123456789012345678901234567890123456789" "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz+-"}; string ret; int len = Rand::rand(3, 8); for (int i = 0; i < len; ++i) { ret.push_back(PREFIX_STR.at(Rand::rand(0, PREFIX_STR.length() - 1))); } return ret; } void insertTestData(sqlite3* db) { execSQL(db, "INSERT INTO Foo VALUES(null, 1, 2);"); execSQL(db, "INSERT INTO Foo VALUES(null, 10.5, 20.1);"); execSQL(db, "INSERT INTO Foo VALUES(null, ‘abc‘, ‘hehe‘);"); vector<string> prefixs{randPrefix(), randPrefix(), randPrefix(), randPrefix()}; ostringstream stm; for (int i = 0; i < 30; ++i) { string prefix = prefixs.at(Rand::rand(0, prefixs.size() - 1)); stm.str(""); stm << "INSERT INTO Foo VALUES(null, ‘" << prefix << randNumString() << "‘, " << Rand::rand(1, 999) << ");"; execSQL(db, stm.str()); stm.str(""); stm << "INSERT INTO Foo VALUES(null, ‘" << prefix << randNumString() << "‘, " << Rand::rand(1, 999) << ");"; execSQL(db, stm.str()); stm.str(""); stm << "INSERT INTO Foo VALUES(null, ‘" << prefix << randNumString() << "‘, " << Rand::rand(1, 999) << ");"; execSQL(db, stm.str()); } } int main(int, char**) { initOutputStream(); MEMINFO("start"); sqlite3* db = openDB("Foo.db"); int ret{}; MEMINFO("create table"); execSQL(db, "CREATE TABLE IF NOT EXISTS Foo" "(_id INTEGER PRIMARY KEY, name TEXT, info TEXT);"); // insertTestData(db); MEMINFO("query all"); printSQL(db, "SELECT * FROM Foo;"); MEMINFO("set echo"); ret = sqlite3_create_function_v2(db, "echo", 1, SQLITE_UTF8, reinterpret_cast<void*>(1), echo, 0, 0, testClean); if (SQLITE_OK != ret) { ERR << "sqlite3_create_function_v2 -> echo." << sqlite3_errmsg(db) << endl; } MEMINFO("query using echo"); printSQL(db, "SELECT echo(‘Hello SQLite!‘) AS replay;"); printSQL(db, "SELECT _id, echo(name) AS name FROM Foo;"); MEMINFO("set strcat_column"); ret = sqlite3_create_function_v2(db, "strcat_column", -1, SQLITE_UTF8, 0, strcat_column, 0, 0, 0); if (SQLITE_OK != ret) { ERR << "sqlite3_create_function_v2 -> strcat_column." << sqlite3_errmsg(db) << endl; } MEMINFO("query using strcat_column"); printSQL(db, "SELECT strcat_column(_id, name, info) AS list FROM Foo;"); MEMINFO("set strcat"); ret = sqlite3_create_function_v2(db, "strcat", 2, SQLITE_UTF8, 0, 0, strcat_step, strcat_final, 0); MEMINFO("query using strcat"); if (SQLITE_OK != ret) { ERR << "sqlite3_create_function_v2 -> strcat_column." << sqlite3_errmsg(db) << endl; } printSQL(db, "SELECT strcat(_id, ‘,‘) AS ids FROM Foo;"); printSQL(db, "SELECT strcat(name, ‘,‘) AS names FROM Foo;"); printSQL(db, "SELECT strcat(info, ‘,‘) AS infos FROM Foo;"); MEMINFO("set compareName"); ret = sqlite3_create_collation_v2(db, "compareName", SQLITE_UTF8, reinterpret_cast<void*>(2), compareName, testClean); MEMINFO("query using compareName"); if (SQLITE_OK != ret) { ERR << "sqlite3_create_collation_v2 -> compareName." << sqlite3_errmsg(db) << endl; } printSQL(db, "SELECT * FROM Foo ORDER BY name collate compareName;"); MEMINFO("close db"); closeDB(db); MEMINFO("end"); return 0; }

 

//////////////////////////////////////////////////////////////////////////
// 随机数

#ifndef RAND_H
#define RAND_H

#include <random>


class Rand
{
public:
    static int rand(int minValue, int maxValue);

private:
    static std::default_random_engine DEFAULT_ENGINE;
};

#endif // RAND_H
    
    
#include "Rand.h"
#include <ctime>


std::default_random_engine Rand::DEFAULT_ENGINE(time(0));

int Rand::rand(int minValue, int maxValue)
{
    std::uniform_int_distribution<int> d(minValue, maxValue);
    return d(DEFAULT_ENGINE);
}

 

//////////////////////////////////////////////////////////////////////////
// 内存打印工具

#ifndef MEMORY_H
#define MEMORY_H

#include <string>

using std::string;


class Memory
{
public:
    static void print(const string& tag = "");

private:
    static string format(long long);
    static long long m_prevAvaiPhys;
};

#endif // MEMORY_H


#include "Memory.h"
#include <iostream>
#include <sstream>
#include "windows.h"


long long Memory::m_prevAvaiPhys{};

void Memory::print(const string& tag)
{
    MEMORYSTATUSEX mem{};
    mem.dwLength = sizeof(MEMORYSTATUSEX);
    GlobalMemoryStatusEx(&mem);
    std::cout << "virtual:" << format(mem.ullAvailVirtual) << "/" << format(mem.ullTotalVirtual)
              << " physical:" << format(mem.ullAvailPhys) << "/" << format(mem.ullTotalPhys);

    if (0 != m_prevAvaiPhys)
    {
        std::cout << ", reduce physical:" << (long long)(m_prevAvaiPhys - mem.ullAvailPhys) << "B";
    }
    m_prevAvaiPhys = mem.ullAvailPhys;

    std::cout << " [" << tag <<  "]" << std::endl;
}

string Memory::format(long long bytes)
{
    std::ostringstream stm;
    //stm << (bytes >> 20) << "MB";
    stm << (bytes >> 10) << "KB";
    return stm.str();
}

 

SQLite 自定义函数,聚合,排序规则

标签：output   fun   完成后   mysql   pac   memcpy   reg   realloc   names