<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                企業??AI智能體構建引擎,智能編排和調試,一鍵部署,支持知識庫和私有化部署方案 廣告
                # 第7章 擴充C API 本章介紹SQLite的新技巧。前一章涉及一般的數據庫操作,本章將開始創新。擴充API提供3種基本方法來擴展(或說定制)SQLite,包括:創建用戶自定義函數、聚合和排序序列。 用戶自定義函數是編寫用于特定應用的SQL函數。一旦注冊,就可以在SQL中被調用。 本章將涉及所有這3個用戶定義的擴展工具及與之相關的API函數。你會看到,當與其它工具,如觸發器和沖突解決等結合在一起時,用戶定義的擴充API是強有力的,并能為SQLite創造非凡特色。 空注:本章內容對編程還是很有用的,但我對這部分內容只是略做瀏覽。關心這部分內容的兄弟還是得自己看原文。 ## API 用戶自定義聚合、函數和排序法的生命同期是基于連接的。它們不存儲在數據庫中。有時你可能會把它們當成存儲過程看待,而忘記了它們是在數據庫之外的。它們存在于程序庫(librarie)中,其生命周期嚴格地限制在你的程序之內。I ## 注冊函數 ## 步進函數 自定義函數和聚合的步進函數是一樣的,可如下定義: ``` void fn(sqlite3_context* ctx, int nargs, sqlite3_value** values) ``` ## 返回值 ## 函數 ## 返回值 ## 一個完整的例子 Listing 7-2\. The main Function ``` int main(int argc, char **argv) { ??? int rc; ??? sqlite3 *db; ??? const char* sql; ? ??sqlite3_open("test.db", &db); ??? sqlite3_create_function( db, "function", -1, SQLITE_UTF8, NULL, ???????????????????????????? function, NULL, NULL); ??? /* Turn on SQL logging */ ??? //log_sql(db, 1); ??? /* Call function with one text argument. */ ? ??execute(db, "select function(1)"); ??? /* Call function with several arguments of various types. */ ??? execute(db, "select function(1, 2.71828)"); ??? /* Call function with variable arguments, the first argument’s value ??? ** being 'fail'. This will trigger the function to call ??? ** sqlite3_result_error(). */ ??? execute(db, "select function('fail', 1, 2.71828, 'three', X'0004', NULL)"); ??? /* Done */ ??? sqlite3_close(db); ??? return 0;??? } ``` Listing 7-3\. A Vanilla User-Defined Function ``` void function(sqlite3_context* ctx, int nargs, sqlite3_value** values) { ??? int i; const char *msg; ??? fprintf(stdout, "function() : Called with %i arguments\n", nargs); ??? for(i=0; i < nargs; i++) { ??????? fprintf( stdout, "??? arg %i: value=%-7s type=%i\n", i, ???????????????? sqlite3_value_text(values[i]), ???????????????? sqlite3_value_type(values[i])); ??? } ??? if(strcmp((const char *)sqlite3_value_text(values[0]), "fail") == 0) { ??????? msg = "function() : Failing because you told me to."; ???? ???sqlite3_result_error(ctx, msg, strlen(msg)); ??????? fprintf(stdout, "\n"); ??????? return; ??? } ??? fprintf(stdout, "\n"); ??? sqlite3_result_int(ctx, 0); } ``` ## 一個實際的應用程序 ## 聚合 ![](https://box.kancloud.cn/2016-05-17_573b066d5636b.jpg) Figure 7-1\. Query processing with aggregates ## 一個實際的例子 Listing 7-9\. The sum_int() Test Program ``` int main(int argc, char **argv) { ??? int rc; ??? sqlite3 *db; ??? char *sql; ??? rc = sqlite3_open("test.db", &db); ??? if(rc) { ??????? print_error(db, "Can't open database"); ??????? exit(1); ??? } ??? /* Create aggregate table, add records. */ ??? setup(db); ??? /* Register aggregate. */ ??? fprintf(stdout, "Registering aggregate sum_int()\n"); ??? log_sql(db, 1); ??? sqlite3_create_function( db, "sum_int", 1, SQLITE_UTF8, db, ???????????????????????????? NULL, step, finalize); ??? /* Test it. */ ??? fprintf(stdout, "\nRunning query: \n"); ??? sql = "select sum_int(id) from aggregate"; ??? print_sql_result(db, sql); ??? /* Done. */ ??? sqlite3_close(db); ??? return 0;??? } ``` ### 步進函數 The step() function is shown in Listing 7-10\. Listing 7-10\. The sum_int() Step Function ``` void step(sqlite3_context* ctx, int ncols, sqlite3_value** values) { ??? sum* s; ??? int x; ??? s = (sum*)sqlite3_aggregate_context(ctx, sizeof(sum)); ??? if(sqlite3_aggregate_count(ctx) == 1) { ??????? s->x = 0; ??? } ??? x = sqlite3_value_int(values[0]);; ??? s->x += x; ??? fprintf(stdout, "step()???? : value=%i, total=%i\n", x, s->x); } The value sum is a struct that is specific to this example and is defined as follows: typedef struct { ??? int x; } sum; ``` ### 聚合的Context ### Finalize函數 Listing 7-11\. The sum_int() Finalize Function ``` void finalize(sqlite3_context* ctx) { ??? sum* s; ??? s = (sum*)sqlite3_aggregate_context(ctx, sizeof(sum)); ??? sqlite3_result_int(ctx, s->x); ??? fprintf(stdout, "finalize() : total=%i\n\n", s->x); } ``` ## 排序法 ## 排序法定義 ### 排序法如何工作 ### 標準的排序法類型 ## 一個簡單的例子 ### Compare函數 Listing 7-12\. The Political Collation Function ``` int political_collation( void* data, int l1, const void* s1, ???????????????????????????????????? int l2, const void* s2 ) { ??? int value, opinion; struct tm* t; time_t rt; ??? /* Get the unpolitical value */ ?????? value = strcmp(s1,s2); ??? /* Get the date and time */ ??? time(&rt); ??? t = localtime(&rt); ??? /* Form an opinion */ ?????? switch(t->tm_wday) { ??????? case 0: /* Monday yes??? */ ????? ??????opinion = value; ??????????? break; ??????? case 1: /* Tueday no???? */ ??????????? opinion = -value; ??????????? break; ??????? case 2: /* Wednesday bigger is better */ ??????????? opinion = l1 >= l2 ? -1:1; ??????????? break; ??????? case 3: /* Thursday strongly no?? */ ??????????? opinion = -100; ??????????? break; ??????? case 4: /* Friday strongly yes??? */ ??????????? opinion = 100; ??????????? break; ??????? case 5: /* Saturday golf, everything's the same */ ??????????? opinion = 0; ?????????? ?break; ??????? default: /* Sunday - Meet the Press, opinion changes ??????????????????? by the hour */ ??????????? opinion = 2*(int)sin(t->tm_hour*180); ??? } ??? /* Now change it on a whim */ ??? opinion = rand()-(RAND_MAX/2) > 0 ? -1:1; ??? return opinion; } ``` ### 測試程序 Listing 7-13\. The Political Collation Test Program ``` int main(int argc, char **argv) { ??? int rc; ??? sqlite3 *db; ??? char *sql; ??? /* For forming more consistent political opinions. */ ??? srand((unsigned)time(NULL)); ??? rc = sqlite3_open("test.db", &db); ??? if(rc) { ??????? print_error(db, "Can't open database"); ??????? exit(1); ??? } ??? /* Create issues table, add records. */ ??? setup(db); ??? /* Register collating sequence. */ ??? fprintf(stdout, "1. Register political collating sequence\n\n"); ??? sqlite3_create_collation( db, "POLITICAL", ????????????????????????????? SQLITE_UTF8, db, ????????????????????????????? political_collation ); ??? /* Turn SQL logging on. */ ??? log_sql(db, 1); ??? /* Test default collation. */ ?? ?fprintf(stdout, "2. Select records using default collation.\n"); ??? sql = "select * from issues order by issue"; ??? print_sql_result(db, sql); ??? /* Test Oracle collation. */ ??? fprintf(stdout, "\nSelect records using political collation. \n"); ??? sql = "select * from issues order by issue collate POLITICAL"; ??? print_sql_result(db, sql); ??? /* Done. */ ??? sqlite3_close(db); ??? return 0;??? } ``` ## 按需排序(Collation on Demand) Listing 7-14\. Collation Registration Function ``` void crf( void* data, sqlite3* db, ??? int eTextRep, const char* cname) { ??? if(strcmp(collation_name, "POLITICAL") == 0) { ??????? /* Political collation has not been registered and is now needed */ ??????? sqlite3_create_collation( db, "POLITICAL", ??????????? SQLITE_UTF8, db, ??????????? political_collation ); ??? } else { ??????? /* Punt: Use some default comparison function this collation. */ ??????? sqlite3_create_collation( db, collation_name, ??????????? SQLITE_UTF8, db, ??????????? default_collation ); ??? } } ``` ## 一個實際的應用程序 ### 比較函數 Listing 7-15\. Oracle Date Collation Function ``` int oracle_date_collation( void* data, ?????????????????????????? int len1, const void* arg1, ?????????????????????????? int len2, const void* arg2 ) { ??? int len; ??? date d1; ??? date d2; ??? char zDate1[25]; ??? char zDate2[25]; ??? /* Copy date 1 */ ??? if(len1 > 24) { ??????? len = 24; ??? } else { ??????? len = len1; ??? } ??? strncpy(&zDate1[0], arg1, len); ??? zDate1[len] = '\0'; ??? /* Copy date 2 */ ??? if(len2 > 24) { ??????? len = 24; ??? } else { ??????? len = len2; ??? } ??? strncpy(&zDate2[0], arg2, len); ??? zDate2[len] = '\0'; ??? /* Convert dates to date struct */ ??? oracle_date_str_to_struct(zDate1, &d1); ??? oracle_date_str_to_struct(zDate2, &d2); ??? fprintf(stdout, "collate_fn() : date1=%s, date2=%s\n", zDate1, zDate2); ??? /* Compare structs */ ??? if(d1.year < d2.year) ??? { ??????? return -1; ??? } ??? else if(d1.year > d2.year) ??? { ??????? return 1; ??? } ??? /* If this far, years are equal. */ ??? if(d1.month < d2.month) ??? { ??????? return -1; ??? } ??? else if(d1.month > d2.month) ??? { ??????? return 1; ??? } ??? /* If this far, months are equal. */ ??? if(d1.day < d2.day) ??? { ??????? return -1; ??? } ??? else if(d1.day > d2.day) ??? { ??????? return 1; ??? } ??? /* If this far, dates are equal. */ ??? return 0; } ``` ### 日期解析 Listing 7-16\. The Oracle Date Parsing Function ``` int oracle_date_str_to_struct(const char* value, date* d) { ??? const char* date, *tmp; ??? char *start, *end, zDay[3], zMonth[4], zYear[3]; ??? date = get_date(value); ??? if(date == NULL) { ??????? fprintf(stderr, "Invalid date\n"); ??????? return -1; ??? } ??? /* Find first '-' */ ??? start = strchr(date,'-'); ??? /* Find last '-' */ ??? end?? = strchr(start+1,'-'); ??? /* Extract day part, convert to int*/ ??? strncpy(zDay, date,2); ??? zDay[2] = '\0'; ??? d->day = atoi(zDay); ??? /* Extract month part, convert to int*/ ??? strncpy(zMonth, start+1,3); ??? zMonth[3] = 0; ??? tmp = uppercase(zMonth); ??? d->month = month_num(tmp); ??? free((void*)tmp); ??? /* Extract year part, convert to int*/ ??? strncpy(zYear, end+1,2); ??? zYear[2] = '\0'; ??? d->year = atoi(zYear); ??? free((void*)date); ??? return 0; } ``` Listing 7-17\. The get_date() Function ``` #define ORACLE_DATE_REGEX "[0-9]{1,2}-[a-zA-Z]{3,3}-[0-9]{2,2}"; const char* get_date(const char* value) { ??? pcre *re; ??? const char *error, *pattern; ??? int erroffset; ??? int ovector[3]; ??? int value_length; ??? int rc, substring_length; ??? char* result, *substring_start; ??? pattern = ORACLE_DATE_REGEX; ??? re = pcre_compile( ??????? pattern,????????????? /* the pattern */ ??????? 0,??????????????????? /* default options */ ??????? &error,?????????????? /* for error message */ ??????? &erroffset,?????????? /* for error offset */ ??????? NULL);??????????????? /* use default character tables */ ??? /* Compilation failed */ ??? if (re == NULL) { ??????? return NULL; ??? } ??? value_length = (int)strlen(value); ??? rc = pcre_exec( ??????? re,?????????? /* the compiled pattern */ ? ??????NULL,???????? /* no extra data - we didn't study the pattern */ ??????? value,??????? /* the value string */ ??????? value_length, /* the length of the value */ ??????? 0,??????????? /* start at offset 0 in the value */ ??????? 0,??????????? /* default options */ ??????? ovector,????? /* output vector for substring information */ ??????? 3);?????????? /* number of elements in the output vector */ ??? if (rc < 0) { ??????? /* Match error */ ??????? return NULL; ??? } ??? /* Match succeded */ ??? substring_start = (char*)value + ovector[0]; ??? substring_length = ovector[1] - ovector[0]; ??? //printf("%.*s\n", substring_length, substring_start); ??? result = malloc(substring_length+1); ??? strncpy(result, substring_start, substring_length); ??? result[substring_length] = '\0'; ??? return result; } ``` ### 測試程序 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 ``` int main(int argc, char **argv) { ??? int rc; ??? sqlite3 *db; ??? char *sql; ??? rc = sqlite3_open("test.db", &db); ??? if(rc) { ??????? print_error(db, "Can't open database"); ??????? exit(1); ??? } ??? /* Install oracle related date functions. */ ??? install_date_functions(db); ??? /* Register collating sequence. */ ??? fprintf(stdout, "Registering collation sequence oracle_date\n"); ??? sqlite3_create_collation( db, "oracle_date", ????????????????????????????? SQLITE_UTF8, db, ????????????????????????????? oracle_date_collation ); ??? /* Create dates table, add records. */ ??? setup(db); ??? /* Install date */ ??? install_date_triggers(db); ??? /* Turn SQL logging on. */ ??? log_sql(db, 1); ??? /* Test default collation. */ ??? fprintf(stdout, "Select records. Use default collation.\n"); ??? sql = "select * from dates order by date"; ??? print_sql_result(db, sql); ??? /* Test Oracle collation. */ ??? fprintf(stdout, "\nSelect records. Use Oracle data collation. \n"); ??? sql = "select * from dates order by date collate oracle_date"; ??? print_sql_result(db, sql); ??? /* Get ISO Date from Oracle date. */ ??? fprintf(stdout, "\nConvert Oracle date to ISO format.\n"); ??? sql = "select iso_from_oradate('01-APR-05') as 'ISO Date'"; ??? print_sql_result(db, sql); ??? /* Validate Oracle date. */ ??? fprintf(stdout, "\nValidate Oracle format. Should fail.\n"); ??? sql = "select validate_oradate('01-NOT-2005')"; ??? execute(db, sql); ??? /* Test Oracle date triggers. */ ??? fprintf(stdout, "\nTest Oracle insert trigger -- should fail.\n"); ??? sql = "insert into dates (date) values ('01-NOT-2005')"; ??? execute(db, sql); ??? fprintf(stdout, "\nTest Oracle update trigger -- should succeed.\n"); ??? sql = "update dates set date='01-JAN-2005'"; ??? execute(db, sql); ??? print_sql_result(db, "select * from dates"); ??? /* Done. */ ??? sqlite3_close(db); ??? return 0;??? } ``` ### 運行結果 略。
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看