SQLite权威指南

SQLite权威指南(第二版）第七章 The Extension

2019-11-11  本文已影响0人  风月灯

SQLite权威指南(第二版）第七章 The Extension C API

本章介绍有关SQLite的新技术。主要讨论用户自定义函数、聚合、排序规则

一、API

在程序中注册过 函数、聚合、排序规则的回调block实现，才可以在SQL中使用他们（排序规则使用单独的注册函数）
扩展API的生命周期是短暂的，他们是基于连接注册的且不存储在数据库中，需要确保应用程序加载了扩展API并在连接中注册

1.注册函数sqlite3_create_function() 

int sqlite3_create_function(
    sqlite3 *cnx, /* connection handle */
    const char *zFunctionName, /* function/aggregate name in SQL */
    int nArg, /* number of arguments. -1 = unlimited. */
    int eTextRep, /* encoding (UTF8, 16, etc.) */
    void *pUserData, /* application data, passed to callback */
    void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
    void (*xStep)(sqlite3_context*,int,sqlite3_value**),
    void (*xFinal)(sqlite3_context*)
);

2.步骤函数

void fn(sqlite3_context* ctx, int nargs, sqlite3_value** values)

void *sqlite3_user_data(sqlite3_context*);

void *sqlite3_aggregate_context(sqlite3_context*, int nBytes);

注意： API始终以 void 指针形式使用用户数据。因为：API的很多不法会触发回调函数，在实现所谓的回调函数时，只是作为一种维护状态的便捷方法。

3.返回值

int sqlite3_value_int(sqlite3_value*);
sqlite3_int64 sqlite3_value_int64(sqlite3_value*);
double sqlite3_value_double(sqlite3_value*);

int sqlite3_value_bytes(sqlite3_value*);
const void *sqlite3_value_blob(sqlite3_value*);
const unsigned char *sqlite3_value_text(sqlite3_value*);

int len;
void* data;
len = sqlite3_value_bytes(values[0]);
data = sqlite3_malloc(len);
memcpy(data, sqlite3_value_blob(values[0]), len);

int sqlite3_value_type(sqlite3_value*);

#define SQLITE_INTEGER 1
#define SQLITE_FLOAT 2
#define SQLITE_TEXT 3
#define SQLITE_BLOB 4
#define SQLITE_NULL 5

二、函数

sqlite > select hello_newman('Jerry') as reply;
reply
------------------
Hello Jerry
sqlite > select hello_newman('Kramer') as reply;
reply
------------------
Hello Kramer
sqlite > select hello_newman('George') as reply;
reply
------------------
Hello George

int main(int argc, char **argv)
{
    int rc; sqlite3 *db;
    sqlite3_open_v2("test.db", &db);
    sqlite3_create_function( db, "hello_newman", 1, SQLITE_UTF8, NULL,
    hello_newman, NULL, NULL);
    /* Log SQL as it is executed. 执行时记录SQL日志 */
    log_sql(db,1);
    /* Call function with one text argument.传入1个文本参数调用函数 */
    fprintf(stdout, "Calling with one argument.\n");
    print_sql_result(db, "select hello_newman('Jerry')");
    /* Call function with two arguments. 传入两个参数调用函数（会失败，因为注册时只接收1个参数）
    ** It will fail as we registered the function as taking only one argument.*/
    fprintf(stdout, "\nCalling with two arguments.\n");
    print_sql_result(db, "select hello_newman ('Jerry', 'Elaine')");
    /* Call function with no arguments. This will fail too 不传参调用函数也失败 */
    fprintf(stdout, "\nCalling with no arguments.\n");
    print_sql_result(db, "select hello_newman()");
    /* Done */
    sqlite3_close(db);
    return 0;
}

void hello_newman(sqlite3_context* ctx, int nargs, sqlite3_value** values)
{
    const char *msg;
    /* Generate Newman's reply 生成 Newman的 回复 */
    msg = sqlite3_mprintf("Hello %s", sqlite3_value_text(values[0]));
    /* Set the return value. Have sqlite clean up msg w/ sqlite_free().
       设置返回值，用 sqlite3_free清理msg
     */
    sqlite3_result_text(ctx, msg, strlen(msg), sqlite3_free);
}

Calling with one argument.
    TRACE: select hello_newman('Jerry')
hello_newman('Jerry')
---------------------
Hello Jerry
Calling with two arguments.
execute() Error: wrong number of arguments to function hello_newman()
Calling with no arguments.
execute() Error: wrong number of arguments to function hello_newman()

1.返回值

void sqlite3_result_double(sqlite3_context*, double);
void sqlite3_result_int(sqlite3_context*, int);
void sqlite3_result_int64(sqlite3_context*, long long int);
void sqlite3_result_null(sqlite3_context*);

void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*));
void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*));

void sqlite3_result_xxx(
    sqlite3_context *ctx, /* function context */
    const xxx* value, /* array value */
    int len, /* array length */
    void(*free)(void*)); /* array cleanup function */

2.数组与内存清理器

#define SQLITE_STATIC ((void(*)(void *))0)
#define SQLITE_TRANSIENT ((void(*)(void *))-1)

3.错误处理

void sqlite3_result_error(
    sqlite3_context *ctx, /* the function context */
    const char *msg, /* the error message */
    int len); /* length of the error message */

4.返回输入值

void sqlite3_result_value(
    sqlite3_context *ctx, /* the function context */
    sqlite3_value* value); /* the argument value */

void echo(sqlite3_context* ctx, int nargs, sqlite3_value** values)
{
    sqlite3_result_value(ctx, values[0]);
}

三、聚合

st=>start: select sum(id) from foo;
e=>end: sum(id)
op=>operation: sqlite_prepare()
op0=>operation: sqlite_step()
op1=>operation: xStep()
op2=>operation: xFinal()
cond=>condition: SQLITE_ROW

st->op->op0->cond
cond(yes)->op1->op0
cond(no)->op2->e

1.注册函数

int sqlite3_create_function(
    sqlite3 cnx*, /* connection handle */
    const char *zFunctionName, /* function/aggregate name in SQL */
    int nArg, /* number of arguments. -1 = unlimited. */
    int eTextRep, /* encoding (UTF8, 16, etc.) */
    void *pUserData, /* application data, passed to callback */
    void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
    void (*xStep)(sqlite3_context*,int,sqlite3_value**),
    void (*xFinal)(sqlite3_context*)
);

2.实例

int main(int argc, char **argv)
{
    int rc; sqlite3 *db; char *sql;
    sqlite3_open_v2("foods.db", &db);
    /* Register aggregate. */
    fprintf(stdout, "Registering aggregate str_agg()\n");
    /* Turn SQL tracing on. */
    log_sql(db, 1);
    /* Register aggregate. */
    sqlite3_create_function( db, "str_agg", 1, SQLITE_UTF8, db,
    NULL, str_agg_step, str_agg_finalize);
    /* Test. */
    fprintf(stdout, "\nRunning query: \n");
    sql = "select season, str_agg(name, ', ') from episodes group by season";
    print_sql_result(db, sql);
    sqlite3_close(db);
    return 0;
}

3.步骤函数

void str_agg_step(sqlite3_context* ctx, int ncols, sqlite3_value** values)
{
    SAggCtx *p = (SAggCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
    static const char delim [] = ", ";
    char *txt = sqlite3_value_text(values[0]);
    int len = strlen(txt);
    if (!p->result) {
        p->result = sqlite_malloc(len + 1);
        memcpy(p->result, txt, len + 1);
        p->chrCnt = len;
    } else {
        const int delimLen = sizeof(delim);
        p->result = sqlite_realloc(p->result, p->chrCnt + len + delimLen + 1);
        memcpy(p->result + p->chrCnt, delim, delimLen);
        p->chrCnt += delimLen;
        memcpy(p->result + p->chrCnt, txt, len + 1);
        p->chrCnt += len;
    }
}


// SAggCtx是本例中的结构体
typedef struct SAggCtx SAggCtx;
struct SAggCtx {
    int chrCnt;
    char *result;
};

4.聚合上下文

5.Finalize函数

void str_agg_finalize(sqlite3_context* ctx)
{
    SAggCtx *p = (SAggCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
    if( p && p->result ) sqlite3_result_text(ctx, p->result, p->chrCnt, sqlite_free);
}

6.结果

Registering aggregate str_agg()
Running query:
TRACE: select season, str_agg(name, ', ') from episodes group by season
…
season str_agg(name, ', ')

0 Good News Bad News
1 Male Unbonding, The Stake Out, The Robbery, The Stock Tip
2 The Ex-Girlfriend, The Pony Remark, The Busboy, The Baby Shower, …
3 The Note, The Truth, The Dog, The Library, The Pen, The Parking Garage …
…

四、排序规则（Collations）

1. NULL values
2. INTEGER and REAL values
3. TEXT values
4. BLOB values

1.排序规则定义(Collation Defined)

1.1 排序规则原理(How Collation Works)
1.2 标准排序规则类型(Standard Collation Types)

2.简单例子

int sqlite3_create_collation_v2(
    sqlite3* db, /* database handle */
    const char *zName, /* collation name in SQL */
    int pref16, /* encoding */
    void* pUserData, /* application data */
    int(*xCompare)(void*,int,const void*,int,const void*)
    void(*xDestroy)(void*)
);
2.1 Compare函数

参数xCompare指向实际解析文本值的比较函数。比较函数格式如下：

int compare( void* data, /* application data */
    int len1, /* length of string 1 */
    const void* str1, /* string 1 */
    int len2, /* length of string 2 */
    const void* str2) /* string 2 */

int length_first_collation( void* data, int l1, const void* s1,
    int l2, const void* s2 )
    {
    int result, opinion;
    /* Compare lengths */
    if ( l1 == l2 ) result = 0;
    if ( l1 < l2 ) result = 1;
    if ( l1 > l2 ) result = 2;
    /* Form an opinion: is s1 really < or = to s2 ? */
    switch(result) {
        case 0: /* Equal length, collate alphabetically */
        opinion = strcmp(s1,s2);
        break;
        case 1: /* String s1 is shorter */
        opinion = -result;
        break;
        case 2: /* String s2 is shorter */
        opinion = result
        break;
        default: /* Assume equal length just in case */
        opinion = strcmp(s1,s2);
    }
    return opinion;
}
2.2 测试程序
int main(int argc, char **argv)
{
    char *sql; sqlite3 *db; int rc;
    sqlite3_open("foods.db", &db);
    /* Register Collation. */
    fprintf(stdout, "1. Register length_first Collation\n\n");
    sqlite3_create_collation_v2( db, "LENGTH_FIRST", SQLITE_UTF8, db,
    length_first_collation, length_first_collation_del );

    /* Turn SQL logging on. */
    log_sql(db, 1);
    /* Test default collation. */
    fprintf(stdout, "2. Select records using default collation.\n");
    sql = "select name from foods order by name";
    print_sql_result(db, sql);
    /* Test Length First collation. */
    fprintf(stdout, "\nSelect records using length_first collation. \n");
    sql = "select name from foods order by name collate LENGTH_FIRST";
    print_sql_result(db, sql);
    /* Done. */
    sqlite3_close(db);
    return 0;
}
2.3 结果
======================================================
1. Register length_first Collation
2. Select records using default collation.
TRACE: select name from foods order by name
name
-----------------
A1 Sauce
All Day Sucker
Almond Joy
Apple
Apple Cider
Apple Pie
Arabian Mocha Java (beans)
Arby's Roast Beef
Artichokes
Atomic Sub
...
Select records using length_first collation.
TRACE: select name from foods order by name collate LENGTH_FIRST
issue
-----------------
BLT
Gum
Kix
Pez
Pie
Tea
Bran
Duck
Dill
Life
...

======================================================

3.按需排序

总结

上一篇下一篇

猜你喜欢

热点阅读