SQLite中SELECT基本形式

每个数据库通常都包含多个表，而每个表又包含多条数据。要获取数据库中的数据，就需要SQL语言提供的查询语句SELECT。本章将讲解和SELECT语句相关的内容，其中包括SELECT基本形式、表达式、连接、分组、排序和去重、获取子集、子查询以及联合查询等。

3.1  SELECT基本形式

本节将讲解SELECT语句的基本形式，其中包括基本完整形式、SELECT子句、FROM子句以及WHERE子句等

3.1.1  基本形式

以下是SELECT语法的基本完整形式：

SELECT [DISTINCT] select_heading

FROM source_tables

WHERE filter_expression

GROUP BY grouping_expressions

HAVING filter_expression

ORDER BY ordering_expressions

LIMIT count

OFFSET count

其中，参数说明如下：

 select_heading：用来定义最终结果表的格式和内容，主要是用来定义表的头。

注意：在SQL中的数据结构就是表，表用于存储数据和数据处理。表是由一个头和体组成。头定义了每列的名称和类型（以SQLite为单位）。列名称在表格中必须是唯一的。头定义了列的顺序，这些列作为表定义的一部分被修复。体包含了所有行。每一行由每列的一个数据元素组成。表中的每一列的所有行必须具有相同数量的数据元素，每个元素可以容纳一个数据值（或一个NULL）。

• FROM source_tables：指定一个或多个源表，并将它们组合成一个大的工作表。
• WHERE filter_expression：对工作表中的特定行过滤。
• GROUP BY grouping_expressions：用来指定分组。
• HAVING filter_expression：过滤分组表中的特定行。
• ORDER BY ordering_expressions：对结果集的行进行排序。
• LIMIT count：将结果集输出限制为特定数量的行。
• OFFSET count：跳过结果集开头的行。

注意：DISTINCT是可选的，用来消除重复的行。除了DISTINCT外，在SELECT语句中，附加子句（FROM、WHERE、GROUP BY等）都是可选的。

SELECT语句中的子句并不是按照它们写入的顺序进行执行的。其执行流程如图3.1所示。

图3.1  执行流程

注意：在图3.1中R后面的数字可以认为是执行顺序。

（1）查询语句需要一个或者两个源表。

（2）通过FROM子句，获取一个R1表。

（3）WHERE子句对R1表中的行进行过滤，然后生成一个新的表R2。

（4）R2表通过GROUP BY子句然后进行分组，将R2表分组了对应的组，此时会出生成R3表。

（5）R3表再通过HAVING子句过滤表中特定行，生成R4表。

（6）R4表再通过SELECT子句执行要显示的最终结果表的格式和内容，此时会生成R5表。

（7）R5表再使用DISTINCT关键字进行去重，此时会生成R6表。

（8）R6会通过ORDER BY子句进行排序，此时会生成R7表。

（9）R7表通过OFFSET子句跳过表的开头的行，生成一个新的表R8表。

（10）R8表通过LIMIT子句限制为特定数量的行，此时会出显示出最终的结果表Result。

 

 

sqlite 排序规则

• BINARY - 使用 memcmp() 对字符串数据进行比较，无论文本编码。
• NOCASE - 与 BINARY 相同，除了在比较执行之前会将 ASCII 码的 26 个大写字母被折换为与其等值的小写字母。注意，只有 ASCII 码的字符进行大小写折换。由于所需表的大小，SQLite 不会尝试对完整 UTF 的大小写进行折换。
• RTRIM - 与 BINARY 相同，除了尾随的空格将被忽略。
•  

从 SQL 分配校对序列

每个表的每个列都有一个相关联的校对函数。如果没有校对函数被明确定义，校对规则默认为 BINARY。列定义的 COLLATE 子句被用来替代地为一个列定义校对函数。

为一个二元比较操作符（=、<、>、<=、>=、!=、IS 和 IS NOT）决定使用何种校对函数，按照下面展示的规则顺序进行：

1. 如果两个操作数都使用 COLLATE 后缀操作符分配了一个明确的校对函数，那么明确的校对函数被用在比较中，左边操作数的校对函数有较高的优先级。
2. 如果两个操作数都是一个列，左边列的校对函数有较高的优先级。前一句的目的是，带有一个或多个一元“+”操作符的列名仍被认为是一个列名。
3. 否则，比较将会使用 BINARY 校对函数。

表达式“x BETWEEN y AND z”在逻辑上等价于两个比较“x >= y AND x <= z”，并且与校对函数一起工作，就像这是有两个分开的比较一样。表达式“x IN (SELECT y ...)”与“x = y”的处理方式一样，目的是为了确定校对序列。用在“x IN (y z ...)”格式表达式上的校对序列，就是 x 的校对序列。

ORDER BY 子句是一个 SELECT 语句的一部分，其规则是，可使用 COLLATE 操作符为 SELECT 语句分配一个校对序列，在这种情况下，特定的校对函数被用于排序。否则，如果通过 ORDER BY 子句进行排序的表达式是一个列，列的校对序列将被用来确定排序顺序。如果表达式不是一个列并且没有 COLLATE 子句，则 BINARY 校对序列将被使用。

 

校对序列示例

下面的例子用来辨认校对序列，这可被用来确定由各 SQL 语句执行的文本比较的结果。注意，在 NUMERIC、BLOB 或 NULL 值的情况，一个文本的比较并不是必须的，并且不会使用校对序列。

CREATE TABLE t1(
    x INTEGER PRIMARY KEY 
    a                  /* 校对序列 BINARY */
    b COLLATE BINARY   /* 校对序列 BINARY */
    c COLLATE RTRIM    /* 校对序列 RTRIM */
    d COLLATE NOCASE   /* 校对序列 NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1 'abc' 'abc' 'abc  ' 'abc');
INSERT INTO t1 VALUES(2 'abc' 'abc' 'abc'   'ABC');
INSERT INTO t1 VALUES(3 'abc' 'abc' 'abc ' 'Abc');
INSERT INTO t1 VALUES(4 'abc' 'abc ' 'ABC'   'abc');

/* 使用 BINARY 校对序列执行文本比较 a = b。 */
SELECT x FROM t1 WHERE a = b ORDER BY x;
-- 结果 1 2 3

/* 使用 RTRIM 校对序列执行文本比较 a = b。 */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
-- 结果 1 2 3 4

/* 使用 NOCASE 校对序列执行文本比较 d = a。 */
SELECT x FROM t1 WHERE d = a ORDER BY x;
-- 结果 1 2 3 4

/* 使用 BINARY 校对序列执行文本比较 a = d。 */
SELECT x FROM t1 WHERE a = d ORDER BY x;
-- 结果 1 4

/* 使用 RTRIM 校对序列执行文本比较 'abc' = c。 */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
-- 结果 1 2 3

/* 使用 RTRIM 校对序列执行文本比较 c = 'abc'。 */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
-- 结果 1 2 3

/* 使用 NOCASE 校对序列进行分组（值“abc”、“ABC”和“Abc”被放在相同的分组中）。 */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
-- 结果 4

/* 使用 BINARY 校对序列执行分组。“abc”、“ABC”和“Abc”来自不同的分组。 */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
-- 结果 1 1 2

/* 使用 RTRIM 校对序列或列 c 执行排序。 */
SELECT x FROM t1 ORDER BY c x;
-- 结果 4 1 2 3

/* 使用 BINARY 校对序列或 (c || '') 执行排序。 */
SELECT x FROM t1 ORDER BY (c||'') x;
-- 结果 4 2 3 1

/* 使用 NOCASE 校对序列或列 c 执行排序。 */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE x;
-- 结果 2 4 3 1

 

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

1.使用自定义函数, 聚合以及排序规则的基本方法是使用回调函数.
这些注册的函数的生命周期只存在于应用程序中, 并不存储在数据库文件中, 因此需要在每个连接建立时注册才可以在 SQL 中进行使用.

2.排序规则
SQLite 对结果集中的字段进行排序时, SQLite 使用比较操作符如 < 或 >= 在字段内对值进行比较, 第一件事就是根据存储类对字段值进行排列.
然后在每种存储类中, 根据该类指定的方法进行排序. 存储类进行排序的顺序从前往后如下所示:
NULL 值 < INTEGER 和 REAL 值 < TEXT 值 < BLOB 值
(1) NULL 值具有最低的类值, 一个具有 NULL 存储类的值比所有其他的值都小(包括其他具有 NULL 存储类的值). 在 NULL 值之间, 没有具体的排序顺序.
(2) INTEGER 或 REAL 存储类值高于 NULL, 它们的类值相等. INTEGER 值和 REAL 值通过其数值进行比较.
(3) TEXT 存储类的值比 INTEGER 和 REAL 髙. 数值永远比字符串的值低. 当两个 TEXT 值进行比较时, 其值大小由该值中定义的 “排序法” 决定.
(4) BLOB 存储类具有最高的类值. 具有 BLOB 类的值大于其他所有类的值. BLOB 值之间在比较时使用 C 函数 memcmp.
对 TEXT 存储类的数据, 可以在创建表时指定字段排序规则, 也可以直接在查询中指定它们
CREATE TABLE Foo(x TEXT COLLATE NOCASE);
SELECT * FROM Foo ORDER BY x COLLATE NOCASE;

3.相关 API
(1) 注册自定义函数, 聚合函数
int sqlite3_create_function_v2(
sqlite3* db,
const char* zFunctionName,
int nArg,
int eTextRep,
void* pApp,
void (*xFunc)(sqlite3_context* ctx, int argc, sqlite3_value** argv),
void (*xStep)(sqlite3_context* ctx, int argc, sqlite3_value** argv),
void (*xFinal)(sqlite3_context* ctx),
void (*xDestroy)(void* pApp)
);
db: 数据库连接句柄, 函数和聚合的指定连接. 若要使用函数, 必须在连接上注册
zFunctionName: 在 SQL 语句中使用的函数,聚合名称, 长度限制 255 字节
nArg: 函数参数个数, 如果为-1, 表示可变长参数. SQLite 会强制检查参数个数, 确保传给自定义函数的参数格式是正确的
eTextRep: 首先文本编码格式, 如 SQLITE_UTF8, SQLITE_UTF16
pApp: 传递给回调函数的应用程序数据, 可供在 xFunc, xStep, xFinal 指定的回调函数中使用, 但必须使用特殊的 API 函数获取数据
xFunc: 回调函数
xStep: 聚合步骤函数, SQLite 处理聚合结果集中的每一行都要调用 xStep, 此函数内部处理聚合逻辑, 并保存到结果值中
xFinal: finalize 聚合函数, 处理完所有的行后 SQLite 调用该函数进行整个聚合汇总处理, 此函数内部一般设置聚合功能完成的结果
在自定义函数中,指定 xFunc, 同时 xStep 和 xFinal 设置为 nullptr. 自定义聚合函数则与前者刚好相反, 需设置 xStep 和 xFinal, 同时设置 xFunc 为 nullptr.
只要 nArg 指定参数的个数不同, 或 eTextRep 指定的编码不同, 同一个 zFunctionName 指定的函数就可以注册多个版本, SQLite 会自动选择最佳版本
xDestroy: 清理函数, 用于释放应用程序数据 pApp.

(2) int sqlite3_create_collation_v2(
sqlite3* db,
const char* zFunctionName,
int eTextRep,
void* pApp,
int (*xCompare)(void* pApp, int lLen, const void* lData, int rLen, const void* rData),
void (*xDestroy)(void* pApp)
);
各个参数的意义同 sqlite3_create_function_v2. 唯一不同的是比较函数指针 xCompare, 此参数指定用于比较的函数.

(3) void xFunc(sqlite3_context* ctx, int argc, sqlite3_value** argv);
ctx: 函数/聚合的上下文环境. 它保持特殊函数调用的实例状态, 通过它可以获得 sqlite3_create_function_v2 供的应用程序数据参数 pApp.
要获取该数据可以使用 void* sqlite3_user_data(sqlite3_context* ctx);
argc: 参数个数
argv: 参数值数组, 使用前需配合 argc 验证是否越界.

(4) int xCompare(void* pApp, int lLen, const void* lData, int rLen, const void* rData);
pApp: 在 sqlite3_create_collation_v2 设置的应用程序数据
lLen: 参数1的长度
lData: 参数1的指针
rLen: 参数2的长度
rData: 参数2的指针
PS: 参数1和参数2传入 const void*, 转换为 const char* 后, 字符串不一定是以 '\0' 结束. 因此类似以下的操作结果值不确定:
string lText{static_cast<const char*>(lData)};
应使用 string 的另一个构造函数
string lText{static_cast<const char*>(lData), 0, lLen};

(5) void *sqlite3_user_data(sqlite3_context* ctx);
取得 sqlite3_create_function_v2 调用时传入的 pApp 参数值. 对所有的回调函数, 此函数返回的参数是共享的.

(6) void* sqlite3_aggregate_context(sqlite3_context* ctx, int nBytes);
此函数为每个特定的实例分配状态, 第一次调用该函数时执行一个特殊的聚合函数, 分配 nBytes 字节内存, 并赋值为0.
注意, 分配内存后会赋值为0, 了解这一点非常重要, 这要求我们设置自定义类型保存聚合数据时, 必须是 POD 类型.
此后同一个 ctx 指定的上下文环境中的调用(同一个聚合的实例), 返回相同的数据, 即地址相同.
聚合函数必须要使用此函数, 因此聚合的目前, 就是可以在调用之间存储状态以便堆积数据.
当聚合完成 finalize()回调时, 由 SQLite 自动释放内存, 无须手动释放.

(7) Type sqlite3_value_Type(sqlite3_value* value);
返回 Type 类型的数据. 获取标量值:
double sqlite3_value_double(sqlite3_value*);
int sqlite3_value_int(sqlite3_value*);
sqlite3_int64 sqlite3_value_int64(sqlite3_value*);
获取数组值:
int sqlite3_value_bytes(sqlite3_value*); // 返回 blob 缓冲区中数据长度
const void* sqlite3_value_blob(sqlite3_value*); // 返回 blob 缓冲区中数据指针
const unsigned char* sqlite3_value_text(sqlite3_value*); // 注意返回值不是 const char*
检查数据类型的函数:
int sqlite3_value_type(sqlite3_value*); // SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT
int sqlite3_value_numeric_type(sqlite3_value*);
配对使用 sqlite3_value_bytes 和 sqlite3_value_blob 函数, 就可以从结果中复制数据. 如:
int valueLen = sqlite3_value_bytes(argv[0]);
char* buf = static_cast<char*>(sqlite3_malloc(valueLen + 1));

(8) 设置结果, SQL 查询语句中返回的值
void sqlite3_result_text(sqlite3_context* ctx, const char* data, int len, void(*free)(void* data));
void sqlite3_result_blob(sqlite3_context* ctx, const void* data, int bytes, void(*free)(void* data));
void sqlite3_result_double(sqlite3_context*, double);
void sqlite3_result_int64(sqlite3_context*, sqlite3_int64);
void sqlite3_result_null(sqlite3_context*);
其中 pCleaner 指向释放内存的函数指针
设置出错信息
void sqlite3_result_error(sqlite3_context*, const char*, int);
void sqlite3_result_error_toobig(sqlite3_context*);
void sqlite3_result_error_nomem(sqlite3_context*);
void sqlite3_result_error_code(sqlite3_context*, int);
清理内存
void free(void* p); 用户自定义函数调用完成后, 调用指定的清理函数释放内存. 即 sqlite3_result_text 和 sqlite3_result_blob 的实参 data
预定义清理函数
#define SQLITE_STATIC ((void(*)(void *))0)
#define SQLITE_TRANSIENT ((void(*)(void*))-l)
SQLITE_STATIC 意味着数组内存驻留在非托管空间, SQLite 不需要数据副本, 也不会试图清理它.
SQLITE_TRANSIENT 提示数组数据有可能改变, SQLite 使用 sqlite3_malloc 为自己复制一份数据.

(9) 内存分配与释放
void* sqlite3_malloc(int);
void* sqlite3_realloc(void*, int);
void sqlite3_free(void*);

 

//
// 测试代码
#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;
}

string getString(sqlite3_value* value)
{
    const char* text = reinterpret_cast<const char*>(sqlite3_value_text(value));
    return text ? string{text} : "";
}

/**
 * 将字符串拆分成非全数字组成的前缀,和全数字组成的后缀
 */
bool splitName(const string& text, string& prefix, string& suffix)
{
    regex reg("^(.*[^\\d]+)(\\d+)$|^(\\d+)$");
    smatch match;
    if (regex_match(text, match, reg))
    {
        prefix = match.str(1);
        suffix = prefix.empty() ? match.str(3) : match.str(2);
        return true;
    }
    prefix = text;
    return false;
}

/**
 * 去掉字符串前面的0
 */
string trimPrefixZero(const string& text)
{
    size_t pos = text.find_first_not_of('0');
    if (string::npos != pos && 0 != pos)
    {
        return text.substr(pos);
    }
    return text;
}

/**
 * 比较两个数字字符串序列的大小
 */
int compareDigit(const string& lhs, const string& rhs)
{
    string lText = trimPrefixZero(lhs);
    string rText = trimPrefixZero(rhs);
    if (lText.length() != rText.length())
    {
        return lText.length() - rText.length();
    }
    for (size_t i = 0; i < lText.length(); ++i)
    {
        if (lText.at(i) != rText.at(i))
        {
            return lText.at(i) - rText.at(i);
        }
    }
    return 0;
}

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);
}

/**
 * 找出值为匹配前缀,并且后缀在指定范围内的值
 */
void peekName(sqlite3_context* ctx, int argc, sqlite3_value** argv)
{
    assert(4 == argc);

    bool isFound{false};
    string vPrefix;
    string vSuffix;
    string value = getString(argv[0]);
    if (splitName(value, vPrefix, vSuffix))
    {
        string prefix = getString(argv[1]);
        if (vPrefix == prefix)
        {
            string suffixFrom = getString(argv[2]);
            string suffixTo = getString(argv[3]);
            if (compareDigit(vSuffix, suffixFrom) >= 0 && compareDigit(vSuffix, suffixTo) <= 0)
            {
                isFound = true;
            }
        }
    }

    sqlite3_result_int(ctx, isFound);
}


// 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 lPrefix;
    string lSuffix;
    if (!splitName(lText, lPrefix, lSuffix))
    {
        return 0;
    }

    string rText(static_cast<const char*>(rhsData), 0, rhsLen);
    string rPrefix;
    string rSuffix;
    if (!splitName(rText, rPrefix, rSuffix))
    {
        return 0;
    }

    if (lPrefix != rPrefix)
    {
        return lPrefix.compare(rPrefix);
    }

    return compareDigit(lSuffix, rSuffix);
}

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 peekName");
    ret = sqlite3_create_function_v2(db, "peekName", 4, SQLITE_UTF8, 0, peekName, 0, 0, 0);
    MEMINFO("query using peekName");
    if (SQLITE_OK != ret)
    {
        ERR << "sqlite3_create_function_v2 -> peekName." << sqlite3_errmsg(db) << endl;
    }
    printSQL(db, "SELECT * FROM Foo WHERE peekName(name, '9Y58h', 293, 62804);");

    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();
}