第7章 扩充C API

    用户自定义函数是编写用于特定应用的SQL函数。一旦注册，就可以在SQL中被调用。

    本章将涉及所有这3个用户定义的扩展工具及与之相关的API函数。你会看到，当与其它工具，如触发器和冲突解决等结合在一起时，用户定义的扩充API是强有力的，并能为SQLite创造非凡特色。

    空注：本章内容对编程还是很有用的，但我对这部分内容只是略做浏览。关心这部分内容的兄弟还是得自己看原文。

    用户自定义聚合、函数和排序法的生命同期是基于连接的。它们不存储在数据库中。有时你可能会把它们当成存储过程看待，而忘记了它们是在数据库之外的。它们存在于程序库(librarie)中，其生命周期严格地限制在你的程序之内。I

    注册函数

    步进函数

    自定义函数和聚合的步进函数是一样的，可如下定义：

    返回值

    函数

    一个完整的例子

    Listing 7-2. The main Function

    2. {
    3.     int rc;
    4.     sqlite3 *db;
    5.     const char* sql;
    6.     sqlite3_open("test.db", &db);
    7.     sqlite3_create_function( db, "function", -1, SQLITE_UTF8, NULL,
    8.                              function, NULL, NULL);
    9.     /* Turn on SQL logging */
    10.     //log_sql(db, 1);
    11.     /* Call function with one text argument. */
    12.     execute(db, "select function(1)");
    13.     /* Call function with several arguments of various types. */
    14.     execute(db, "select function(1, 2.71828)");
    15.     /* Call function with variable arguments, the first argument’s value
    16.     ** being 'fail'. This will trigger the function to call
    17.     ** sqlite3_result_error(). */
    18.     execute(db, "select function('fail', 1, 2.71828, 'three', X'0004', NULL)");
    19.     /* Done */
    20.     sqlite3_close(db);
    21.     return 0;   
    22. }
    1. void function(sqlite3_context* ctx, int nargs, sqlite3_value** values)
    2. {
    3.     int i; const char *msg;
    4.     fprintf(stdout, "function() : Called with %i arguments\n", nargs);
    5.     for(i=0; i < nargs; i++) {
    6.         fprintf( stdout, "    arg %i: value=%-7s type=%i\n", i,
    7.                  sqlite3_value_text(values[i]),
    8.                  sqlite3_value_type(values[i]));
    9.     }
    10.     if(strcmp((const char *)sqlite3_value_text(values[0]), "fail") == 0) {
    11.         msg = "function() : Failing because you told me to.";
    12.         sqlite3_result_error(ctx, msg, strlen(msg));
    13.         fprintf(stdout, "\n");
    14.         return;
    15.     }
    16.     fprintf(stdout, "\n");
    17.     sqlite3_result_int(ctx, 0);
    18. }

    一个实际的应用程序

    聚合

    Figure 7-1. Query processing with aggregates

    一个实际的例子

    Listing 7-9. The sum_int() Test Program

    1. int main(int argc, char **argv)
    2. {
    3.     int rc;
    4.     sqlite3 *db;
    5.     char *sql;
    6.     rc = sqlite3_open("test.db", &db);
    7.     if(rc) {
    8.         print_error(db, "Can't open database");
    9.         exit(1);
    10.     }
    11.     /* Create aggregate table, add records. */
    12.     setup(db);
    13.     /* Register aggregate. */
    14.     fprintf(stdout, "Registering aggregate sum_int()\n");
    15.     log_sql(db, 1);
    16.     sqlite3_create_function( db, "sum_int", 1, SQLITE_UTF8, db,
    17.                              NULL, step, finalize);
    18.     /* Test it. */
    19.     fprintf(stdout, "\nRunning query: \n");
    20.     sql = "select sum_int(id) from aggregate";
    21.     print_sql_result(db, sql);
    22.     /* Done. */
    23.     sqlite3_close(db);
    24.     return 0;   
    25. }

    The step() function is shown in Listing 7-10.

    Listing 7-10. The sum_int() Step Function

    聚合的Context

    Finalize函数

    Listing 7-11. The sum_int() Finalize Function

    1. void finalize(sqlite3_context* ctx)
    2. {
    3.     sum* s;
    4.     s = (sum*)sqlite3_aggregate_context(ctx, sizeof(sum));
    5.     sqlite3_result_int(ctx, s->x);
    6.     fprintf(stdout, "finalize() : total=%i\n\n", s->x);
    7. }

    排序法定义

    标准的排序法类型

    一个简单的例子

    Compare函数

    1. int political_collation( void* data, int l1, const void* s1,
    2. {
    3.     int value, opinion; struct tm* t; time_t rt;
    4.     /* Get the unpolitical value */
    5.        value = strcmp(s1,s2);
    6.     /* Get the date and time */
    7.     time(&rt);
    8.     t = localtime(&rt);
    9.     /* Form an opinion */
    10.        switch(t->tm_wday) {
    11.         case 0: /* Monday yes    */
    12.             opinion = value;
    13.             break;
    14.         case 1: /* Tueday no     */
    15.             opinion = -value;
    16.             break;
    17.         case 2: /* Wednesday bigger is better */
    18.             opinion = l1 >= l2 ? -1:1;
    20.         case 3: /* Thursday strongly no   */
    21.             opinion = -100;
    22.             break;
    23.         case 4: /* Friday strongly yes    */
    24.             opinion = 100;
    25.             break;
    26.         case 5: /* Saturday golf, everything's the same */
    27.             opinion = 0;
    28.             break;
    29.         default: /* Sunday - Meet the Press, opinion changes
    30.                     by the hour */
    31.             opinion = 2*(int)sin(t->tm_hour*180);
    32.     }
    33.     /* Now change it on a whim */
    34.     opinion = rand()-(RAND_MAX/2) > 0 ? -1:1;
    35.     return opinion;
    36. }

    Listing 7-13. The Political Collation Test Program

    1. int main(int argc, char **argv)
    2. {
    3.     int rc;
    4.     sqlite3 *db;
    5.     char *sql;
    6.     /* For forming more consistent political opinions. */
    7.     srand((unsigned)time(NULL));
    8.     rc = sqlite3_open("test.db", &db);
    9.     if(rc) {
    10.         print_error(db, "Can't open database");
    11.         exit(1);
    12.     }
    13.     /* Create issues table, add records. */
    14.     setup(db);
    15.     /* Register collating sequence. */
    16.     fprintf(stdout, "1. Register political collating sequence\n\n");
    17.     sqlite3_create_collation( db, "POLITICAL",
    18.                               SQLITE_UTF8, db,
    19.                               political_collation );
    20.     /* Turn SQL logging on. */
    21.     log_sql(db, 1);
    22.     /* Test default collation. */
    23.     fprintf(stdout, "2. Select records using default collation.\n");
    24.     sql = "select * from issues order by issue";
    25.     print_sql_result(db, sql);
    26.     /* Test Oracle collation. */
    27.     fprintf(stdout, "\nSelect records using political collation. \n");
    28.     sql = "select * from issues order by issue collate POLITICAL";
    29.     print_sql_result(db, sql);
    30.     /* Done. */
    31.     sqlite3_close(db);
    32.     return 0;   
    33. }

    按需排序(Collation on Demand)

    Listing 7-14. Collation Registration Function

    一个实际的应用程序

    比较函数

    Listing 7-15. Oracle Date Collation Function

    1. int oracle_date_collation( void* data,
    2.                            int len1, const void* arg1,
    3.                            int len2, const void* arg2 )
    4. {
    5.     int len;
    6.     date d1;
    7.     date d2;
    8.     char zDate1[25];
    9.     char zDate2[25];
    10.     /* Copy date 1 */
    11.     if(len1 > 24) {
    12.         len = 24;
    13.     } else {
    14.         len = len1;
    15.     }
    16.     strncpy(&zDate1[0], arg1, len);
    17.     zDate1[len] = '\0';
    18.     /* Copy date 2 */
    19.     if(len2 > 24) {
    20.         len = 24;
    21.     } else {
    22.         len = len2;
    23.     }
    24.     strncpy(&zDate2[0], arg2, len);
    25.     zDate2[len] = '\0';
    26.     /* Convert dates to date struct */
    27.     oracle_date_str_to_struct(zDate1, &d1);
    28.     oracle_date_str_to_struct(zDate2, &d2);
    29.     fprintf(stdout, "collate_fn() : date1=%s, date2=%s\n", zDate1, zDate2);
    30.     /* Compare structs */
    31.     if(d1.year < d2.year)
    32.         return -1;
    33.     }
    34.     else if(d1.year > d2.year)
    35.     {
    36.         return 1;
    37.     }
    38.     /* If this far, years are equal. */
    39.     if(d1.month < d2.month)
    41.         return -1;
    42.     }
    43.     else if(d1.month > d2.month)
    44.     {
    45.         return 1;
    46.     }
    47.     /* If this far, months are equal. */
    48.     if(d1.day < d2.day)
    49.     {
    50.         return -1;
    51.     }
    52.     else if(d1.day > d2.day)
    53.     {
    54.         return 1;
    55.     }
    56.     /* If this far, dates are equal. */
    57.     return 0;
    58. }

    日期解析

    Listing 7-16. The Oracle Date Parsing Function

    1. int oracle_date_str_to_struct(const char* value, date* d)
    2. {
    3.     const char* date, *tmp;
    4.     char *start, *end, zDay[3], zMonth[4], zYear[3];
    5.     date = get_date(value);
    6.     if(date == NULL) {
    7.         fprintf(stderr, "Invalid date\n");
    8.         return -1;
    9.     }
    10.     /* Find first '-' */
    11.     start = strchr(date,'-');
    12.     /* Find last '-' */
    13.     end   = strchr(start+1,'-');
    14.     /* Extract day part, convert to int*/
    15.     strncpy(zDay, date,2);
    16.     zDay[2] = '\0';
    17.     d->day = atoi(zDay);
    18.     /* Extract month part, convert to int*/
    19.     strncpy(zMonth, start+1,3);
    20.     zMonth[3] = 0;
    21.     tmp = uppercase(zMonth);
    22.     d->month = month_num(tmp);
    23.     free((void*)tmp);
    24.     /* Extract year part, convert to int*/
    25.     strncpy(zYear, end+1,2);
    26.     zYear[2] = '\0';
    27.     d->year = atoi(zYear);
    28.     free((void*)date);
    29.     return 0;
    30. }

    Listing 7-17. The get_date() Function

    1. #define ORACLE_DATE_REGEX "[0-9]{1,2}-[a-zA-Z]{3,3}-[0-9]{2,2}";
    2. const char* get_date(const char* value)
    3. {
    4.     pcre *re;
    5.     const char *error, *pattern;
    6.     int erroffset;
    7.     int ovector[3];
    8.     int value_length;
    9.     int rc, substring_length;
    10.     char* result, *substring_start;
    11.     pattern = ORACLE_DATE_REGEX;
    12.     re = pcre_compile(
    13.         pattern,              /* the pattern */
    14.         0,                    /* default options */
    15.         &error,               /* for error message */
    16.         &erroffset,           /* for error offset */
    17.         NULL);                /* use default character tables */
    18.     /* Compilation failed */
    19.     if (re == NULL) {
    20.         return NULL;
    21.     }
    22.     value_length = (int)strlen(value);
    23.     rc = pcre_exec(
    24.         re,           /* the compiled pattern */
    25.         NULL,         /* no extra data - we didn't study the pattern */
    26.         value,        /* the value string */
    27.         value_length, /* the length of the value */
    28.         0,            /* start at offset 0 in the value */
    29.         0,            /* default options */
    30.         ovector,      /* output vector for substring information */
    31.         3);           /* number of elements in the output vector */
    32.     if (rc < 0) {
    33.         /* Match error */
    34.         return NULL;
    35.     }
    36.     /* Match succeded */
    37.     substring_start = (char*)value + ovector[0];
    38.     substring_length = ovector[1] - ovector[0];
    39.     //printf("%.*s\n", substring_length, substring_start);
    40.     result = malloc(substring_length+1);
    41.     strncpy(result, substring_start, substring_length);
    42.     result[substring_length] = '\0';
    43. }

    All three of the above functions work together to collate Oracle dates in chronological order. Our example program is shown in Listing 7-18.

    运行结果

    略。