Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to src/sqlite.h.in.

  4617   4617   ** KEYWORDS: {function creation routines}
  4618   4618   ** KEYWORDS: {application-defined SQL function}
  4619   4619   ** KEYWORDS: {application-defined SQL functions}
  4620   4620   ** METHOD: sqlite3
  4621   4621   **
  4622   4622   ** ^These functions (collectively known as "function creation routines")
  4623   4623   ** are used to add SQL functions or aggregates or to redefine the behavior
  4624         -** of existing SQL functions or aggregates.  The only differences between
  4625         -** these routines are the text encoding expected for
  4626         -** the second parameter (the name of the function being created)
  4627         -** and the presence or absence of a destructor callback for
  4628         -** the application data pointer.
         4624  +** of existing SQL functions or aggregates. The only differences between
         4625  +** the three "sqlite3_create_function*" routines are the text encoding 
         4626  +** expected for the second parameter (the name of the function being 
         4627  +** created) and the presence or absence of a destructor callback for
         4628  +** the application data pointer. Function sqlite3_create_window_function()
         4629  +** is similar, but allows the user to supply the extra callback functions
         4630  +** needed by [aggregate window functions].
  4629   4631   **
  4630   4632   ** ^The first parameter is the [database connection] to which the SQL
  4631   4633   ** function is to be added.  ^If an application uses more than one database
  4632   4634   ** connection then application-defined SQL functions must be added
  4633   4635   ** to each database connection separately.
  4634   4636   **
  4635   4637   ** ^The second parameter is the name of the SQL function to be created or
................................................................................
  4667   4669   ** function that is not deterministic.  The SQLite query planner is able to
  4668   4670   ** perform additional optimizations on deterministic functions, so use
  4669   4671   ** of the [SQLITE_DETERMINISTIC] flag is recommended where possible.
  4670   4672   **
  4671   4673   ** ^(The fifth parameter is an arbitrary pointer.  The implementation of the
  4672   4674   ** function can gain access to this pointer using [sqlite3_user_data()].)^
  4673   4675   **
  4674         -** ^The sixth, seventh and eighth parameters, xFunc, xStep and xFinal, are
         4676  +** ^The sixth, seventh and eighth parameters passed to the three
         4677  +** "sqlite3_create_function*" functions, xFunc, xStep and xFinal, are
  4675   4678   ** pointers to C-language functions that implement the SQL function or
  4676   4679   ** aggregate. ^A scalar SQL function requires an implementation of the xFunc
  4677   4680   ** callback only; NULL pointers must be passed as the xStep and xFinal
  4678   4681   ** parameters. ^An aggregate SQL function requires an implementation of xStep
  4679   4682   ** and xFinal and NULL pointer must be passed for xFunc. ^To delete an existing
  4680   4683   ** SQL function or aggregate, pass NULL pointers for all three function
  4681   4684   ** callbacks.
  4682   4685   **
  4683         -** ^(If the ninth parameter to sqlite3_create_function_v2() is not NULL,
  4684         -** then it is destructor for the application data pointer. 
  4685         -** The destructor is invoked when the function is deleted, either by being
  4686         -** overloaded or when the database connection closes.)^
  4687         -** ^The destructor is also invoked if the call to
  4688         -** sqlite3_create_function_v2() fails.
  4689         -** ^When the destructor callback of the tenth parameter is invoked, it
  4690         -** is passed a single argument which is a copy of the application data 
  4691         -** pointer which was the fifth parameter to sqlite3_create_function_v2().
         4686  +** ^The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue 
         4687  +** and xInverse) passed to sqlite3_create_window_function are pointers to
         4688  +** C-lanugage callbacks that implement the new function. xStep and xFinal
         4689  +** must both be non-NULL. xValue and xInverse may either both be NULL, in
         4690  +** which case a regular aggregate function is created, or must both be 
         4691  +** non-NULL, in which case the new function may be used as either an aggregate
         4692  +** or aggregate window function. More details regarding the implementation
         4693  +** of aggregate window functions are 
         4694  +** [user-defined window functions|available here].
         4695  +**
         4696  +** ^(If the final parameter to sqlite3_create_function_v2() or
         4697  +** sqlite3_create_window_function() is not NULL, then it is destructor for
         4698  +** the application data pointer. The destructor is invoked when the function 
         4699  +** is deleted, either by being overloaded or when the database connection 
         4700  +** closes.)^ ^The destructor is also invoked if the call to 
         4701  +** sqlite3_create_function_v2() fails.  ^When the destructor callback is
         4702  +** invoked, it is passed a single argument which is a copy of the application
         4703  +** data pointer which was the fifth parameter to sqlite3_create_function_v2().
  4692   4704   **
  4693   4705   ** ^It is permitted to register multiple implementations of the same
  4694   4706   ** functions with the same name but with either differing numbers of
  4695   4707   ** arguments or differing preferred text encodings.  ^SQLite will use
  4696   4708   ** the implementation that most closely matches the way in which the
  4697   4709   ** SQL function is used.  ^A function implementation with a non-negative
  4698   4710   ** nArg parameter is a better match than a function implementation with

Changes to src/test_window.c.

   214    214   
   215    215     return TCL_OK;
   216    216   
   217    217    error:
   218    218     Tcl_SetObjResult(interp, Tcl_NewStringObj("misuse test error", -1));
   219    219     return TCL_ERROR;
   220    220   }
          221  +
          222  +/*
          223  +** xStep for sumint().
          224  +*/
          225  +static void sumintStep(
          226  +  sqlite3_context *ctx, 
          227  +  int nArg, 
          228  +  sqlite3_value *apArg[]
          229  +){
          230  +  sqlite3_int64 *pInt;
          231  +
          232  +  assert( nArg==1 );
          233  +  if( sqlite3_value_type(apArg[0])!=SQLITE_INTEGER ){
          234  +    sqlite3_result_error(ctx, "invalid argument", -1);
          235  +    return;
          236  +  }
          237  +  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64));
          238  +  if( pInt ){
          239  +    *pInt += sqlite3_value_int64(apArg[0]);
          240  +  }
          241  +}
          242  +
          243  +/*
          244  +** xInverse for sumint().
          245  +*/
          246  +static void sumintInverse(
          247  +  sqlite3_context *ctx, 
          248  +  int nArg, 
          249  +  sqlite3_value *apArg[]
          250  +){
          251  +  sqlite3_int64 *pInt;
          252  +  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64));
          253  +  *pInt -= sqlite3_value_int64(apArg[0]);
          254  +}
          255  +
          256  +/*
          257  +** xFinal for sumint().
          258  +*/
          259  +static void sumintFinal(sqlite3_context *ctx){
          260  +  sqlite3_int64 res = 0;
          261  +  sqlite3_int64 *pInt;
          262  +  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0);
          263  +  if( pInt ) res = *pInt;
          264  +  sqlite3_result_int64(ctx, res);
          265  +}
          266  +
          267  +/*
          268  +** xValue for sumint().
          269  +*/
          270  +static void sumintValue(sqlite3_context *ctx){
          271  +  sqlite3_int64 res = 0;
          272  +  sqlite3_int64 *pInt;
          273  +  pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0);
          274  +  if( pInt ) res = *pInt;
          275  +  sqlite3_result_int64(ctx, res);
          276  +}
          277  +
          278  +static int SQLITE_TCLAPI test_create_sumint(
          279  +  void * clientData,
          280  +  Tcl_Interp *interp,
          281  +  int objc,
          282  +  Tcl_Obj *CONST objv[]
          283  +){
          284  +  sqlite3 *db;
          285  +  int rc;
          286  +
          287  +  if( objc!=2 ){
          288  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
          289  +    return TCL_ERROR;
          290  +  }
          291  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
          292  +
          293  +  rc = sqlite3_create_window_function(db, "sumint", 1, SQLITE_UTF8, 0,
          294  +      sumintStep, sumintFinal, sumintValue, sumintInverse,
          295  +      0
          296  +  );
          297  +
          298  +  if( rc!=SQLITE_OK ){
          299  +    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
          300  +    return TCL_ERROR;
          301  +  }
          302  +  return TCL_OK;
          303  +}
   221    304   
   222    305   int Sqlitetest_window_Init(Tcl_Interp *interp){
   223    306     static struct {
   224    307        char *zName;
   225    308        Tcl_ObjCmdProc *xProc;
   226    309        int clientData;
   227    310     } aObjCmd[] = {
   228    311        { "sqlite3_create_window_function", test_create_window, 0 },
   229    312        { "test_create_window_function_misuse", test_create_window_misuse, 0 },
          313  +     { "test_create_sumint", test_create_sumint, 0 },
   230    314     };
   231    315     int i;
   232    316     for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
   233    317       ClientData c = (ClientData)SQLITE_INT_TO_PTR(aObjCmd[i].clientData);
   234    318       Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, c, 0);
   235    319     }
   236    320     return TCL_OK;
   237    321   }
   238    322   #endif

Changes to test/window5.test.

    68     68     INSERT INTO t1 VALUES(2, 'e');
    69     69     INSERT INTO t1 VALUES(3, 'f');
    70     70   }
    71     71   
    72     72   do_execsql_test 1.1 {
    73     73     SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1;
    74     74   } {4 4  {4 6} 5  {1 4 6} 4  {1 4 5 6} 4.5  {1 2 4 5 6} 4 {1 2 3 4 5 6} 3.5}
           75  +
           76  +test_create_sumint db
           77  +do_execsql_test 2.0 {
           78  +  SELECT sumint(a) OVER (ORDER BY rowid) FROM t1 ORDER BY rowid;
           79  +} {4 10 11 16 18 21}
           80  +
           81  +do_execsql_test 2.1 {
           82  +  SELECT sumint(a) OVER (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t1 ORDER BY rowid;
           83  +} {10 11 12 8 10 5}
           84  +
    75     85   
    76     86   finish_test
    77     87