第7章 扩充C API

本章介绍SQLite的新技巧。前一章涉及一般的数据库操作，本章将开始创新。扩充API提供3种基本方法来扩展(或说定制)SQLite，包括：创建用户自定义函数、聚合和排序序列。

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

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

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

API

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

注册函数

步进函数

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

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

返回值

函数

返回值

一个完整的例子

Listing 7-2. The main Function

  1. int main(int argc, char **argv)
  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. }

Listing 7-3. A Vanilla User-Defined Function

  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. }

一个实际的应用程序

聚合

第7章 扩充C API - 图1

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

  1. void step(sqlite3_context* ctx, int ncols, sqlite3_value** values)
  2. {
  3.     sum* s;
  4.     int x;
  5.     s = (sum*)sqlite3_aggregate_context(ctx, sizeof(sum));
  6.     if(sqlite3_aggregate_count(ctx) == 1) {
  7.         s->x = 0;
  8.     }
  9.     x = sqlite3_value_int(values[0]);;
  10.     s->x += x;
  11.     fprintf(stdout, "step()     : value=%i, total=%i\n", x, s->x);
  12. }
  13. The value sum is a struct that is specific to this example and is defined as follows:
  14. typedef struct {
  15.     int x;
  16. } sum;

聚合的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函数

Listing 7-12. The Political Collation Function

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

测试程序

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

  1. void crf( void* data, sqlite3* db,
  2.     int eTextRep, const char* cname)
  3. {
  4.     if(strcmp(collation_name, "POLITICAL") == 0) {
  5.         /* Political collation has not been registered and is now needed */
  6.         sqlite3_create_collation( db, "POLITICAL",
  7.             SQLITE_UTF8, db,
  8.             political_collation );
  9.     } else {
  10.         /* Punt: Use some default comparison function this collation. */
  11.         sqlite3_create_collation( db, collation_name,
  12.             SQLITE_UTF8, db,
  13.             default_collation );
  14.     }
  15. }

一个实际的应用程序

比较函数

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.     {
  33.         return -1;
  34.     }
  35.     else if(d1.year > d2.year)
  36.     {
  37.         return 1;
  38.     }
  39.     /* If this far, years are equal. */
  40.     if(d1.month < d2.month)
  41.     {
  42.         return -1;
  43.     }
  44.     else if(d1.month > d2.month)
  45.     {
  46.         return 1;
  47.     }
  48.     /* If this far, months are equal. */
  49.     if(d1.day < d2.day)
  50.     {
  51.         return -1;
  52.     }
  53.     else if(d1.day > d2.day)
  54.     {
  55.         return 1;
  56.     }
  57.     /* If this far, dates are equal. */
  58.     return 0;
  59. }

日期解析

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.     return result;
  44. }

测试程序

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

Listing 7-18. The Oracle Collation 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.     /* Install oracle related date functions. */
  12.     install_date_functions(db);
  13.     /* Register collating sequence. */
  14.     fprintf(stdout, "Registering collation sequence oracle_date\n");
  15.     sqlite3_create_collation( db, "oracle_date",
  16.                               SQLITE_UTF8, db,
  17.                               oracle_date_collation );
  18.     /* Create dates table, add records. */
  19.     setup(db);
  20.     /* Install date */
  21.     install_date_triggers(db);
  22.     /* Turn SQL logging on. */
  23.     log_sql(db, 1);
  24.     /* Test default collation. */
  25.     fprintf(stdout, "Select records. Use default collation.\n");
  26.     sql = "select * from dates order by date";
  27.     print_sql_result(db, sql);
  28.     /* Test Oracle collation. */
  29.     fprintf(stdout, "\nSelect records. Use Oracle data collation. \n");
  30.     sql = "select * from dates order by date collate oracle_date";
  31.     print_sql_result(db, sql);
  32.     /* Get ISO Date from Oracle date. */
  33.     fprintf(stdout, "\nConvert Oracle date to ISO format.\n");
  34.     sql = "select iso_from_oradate('01-APR-05') as 'ISO Date'";
  35.     print_sql_result(db, sql);
  36.     /* Validate Oracle date. */
  37.     fprintf(stdout, "\nValidate Oracle format. Should fail.\n");
  38.     sql = "select validate_oradate('01-NOT-2005')";
  39.     execute(db, sql);
  40.     /* Test Oracle date triggers. */
  41.     fprintf(stdout, "\nTest Oracle insert trigger -- should fail.\n");
  42.     sql = "insert into dates (date) values ('01-NOT-2005')";
  43.     execute(db, sql);
  44.     fprintf(stdout, "\nTest Oracle update trigger -- should succeed.\n");
  45.     sql = "update dates set date='01-JAN-2005'";
  46.     execute(db, sql);
  47.     print_sql_result(db, "select * from dates");
  48.     /* Done. */
  49.     sqlite3_close(db);
  50.     return 0;   
  51. }

运行结果

略。