Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add new API function sqlite3_create_window_function(), for creating new aggregate window functions. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
da03fb4318fd2613ec5c5b109a3974ac |
| User & Date: | dan 2018-06-18 16:55:22 |
Context
|
2018-06-18
| ||
| 17:36 | Ensure that all four callbacks are provided when registering a window function (otherwise SQLITE_MISUSE is returned). (check-in: 5720dcd8 user: dan tags: exp-window-functions) | |
| 16:55 | Add new API function sqlite3_create_window_function(), for creating new aggregate window functions. (check-in: da03fb43 user: dan tags: exp-window-functions) | |
|
2018-06-15
| ||
| 20:46 | Add extra OOM test. (check-in: ac251f72 user: dan tags: exp-window-functions) | |
Changes
Changes to Makefile.in.
415 415 $(TOP)/src/test_superlock.c \ 416 416 $(TOP)/src/test_syscall.c \ 417 417 $(TOP)/src/test_tclsh.c \ 418 418 $(TOP)/src/test_tclvar.c \ 419 419 $(TOP)/src/test_thread.c \ 420 420 $(TOP)/src/test_vfs.c \ 421 421 $(TOP)/src/test_windirent.c \ 422 + $(TOP)/src/test_window.c \ 422 423 $(TOP)/src/test_wsd.c \ 423 424 $(TOP)/ext/fts3/fts3_term.c \ 424 425 $(TOP)/ext/fts3/fts3_test.c \ 425 426 $(TOP)/ext/session/test_session.c \ 426 427 $(TOP)/ext/rbu/test_rbu.c 427 428 428 429 # Statically linked extensions
Changes to Makefile.msc.
1475 1475 $(TOP)\src\test_superlock.c \ 1476 1476 $(TOP)\src\test_syscall.c \ 1477 1477 $(TOP)\src\test_tclsh.c \ 1478 1478 $(TOP)\src\test_tclvar.c \ 1479 1479 $(TOP)\src\test_thread.c \ 1480 1480 $(TOP)\src\test_vfs.c \ 1481 1481 $(TOP)\src\test_windirent.c \ 1482 + $(TOP)\src\test_window.c \ 1482 1483 $(TOP)\src\test_wsd.c \ 1483 1484 $(TOP)\ext\fts3\fts3_term.c \ 1484 1485 $(TOP)\ext\fts3\fts3_test.c \ 1485 1486 $(TOP)\ext\rbu\test_rbu.c \ 1486 1487 $(TOP)\ext\session\test_session.c 1487 1488 1488 1489 # Statically linked extensions.
Changes to main.mk.
345 345 $(TOP)/src/test_superlock.c \ 346 346 $(TOP)/src/test_syscall.c \ 347 347 $(TOP)/src/test_tclsh.c \ 348 348 $(TOP)/src/test_tclvar.c \ 349 349 $(TOP)/src/test_thread.c \ 350 350 $(TOP)/src/test_vfs.c \ 351 351 $(TOP)/src/test_windirent.c \ 352 + $(TOP)/src/test_window.c \ 352 353 $(TOP)/src/test_wsd.c 353 354 354 355 # Extensions to be statically loaded. 355 356 # 356 357 TESTSRC += \ 357 358 $(TOP)/ext/misc/amatch.c \ 358 359 $(TOP)/ext/misc/carray.c \
Changes to src/func.c.
1767 1767 void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){ 1768 1768 struct compareInfo *pInfo; 1769 1769 if( caseSensitive ){ 1770 1770 pInfo = (struct compareInfo*)&likeInfoAlt; 1771 1771 }else{ 1772 1772 pInfo = (struct compareInfo*)&likeInfoNorm; 1773 1773 } 1774 - sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0); 1775 - sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0); 1774 + sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0, 0, 0); 1775 + sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0, 0, 0); 1776 1776 sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8, 1777 - (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0); 1777 + (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0, 0, 0); 1778 1778 setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE); 1779 1779 setLikeOptFlag(db, "like", 1780 1780 caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE); 1781 1781 } 1782 1782 1783 1783 /* 1784 1784 ** pExpr points to an expression which implements a function. If
Changes to src/main.c.
1679 1679 const char *zFunctionName, 1680 1680 int nArg, 1681 1681 int enc, 1682 1682 void *pUserData, 1683 1683 void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), 1684 1684 void (*xStep)(sqlite3_context*,int,sqlite3_value **), 1685 1685 void (*xFinal)(sqlite3_context*), 1686 + void (*xValue)(sqlite3_context*), 1687 + void (*xInverse)(sqlite3_context*,int,sqlite3_value **), 1686 1688 FuncDestructor *pDestructor 1687 1689 ){ 1688 1690 FuncDef *p; 1689 1691 int nName; 1690 1692 int extraFlags; 1691 1693 1692 1694 assert( sqlite3_mutex_held(db->mutex) ); ................................................................................ 1712 1714 ** to the hash table. 1713 1715 */ 1714 1716 if( enc==SQLITE_UTF16 ){ 1715 1717 enc = SQLITE_UTF16NATIVE; 1716 1718 }else if( enc==SQLITE_ANY ){ 1717 1719 int rc; 1718 1720 rc = sqlite3CreateFunc(db, zFunctionName, nArg, SQLITE_UTF8|extraFlags, 1719 - pUserData, xSFunc, xStep, xFinal, pDestructor); 1721 + pUserData, xSFunc, xStep, xFinal, xValue, xInverse, pDestructor); 1720 1722 if( rc==SQLITE_OK ){ 1721 1723 rc = sqlite3CreateFunc(db, zFunctionName, nArg, SQLITE_UTF16LE|extraFlags, 1722 - pUserData, xSFunc, xStep, xFinal, pDestructor); 1724 + pUserData, xSFunc, xStep, xFinal, xValue, xInverse, pDestructor); 1723 1725 } 1724 1726 if( rc!=SQLITE_OK ){ 1725 1727 return rc; 1726 1728 } 1727 1729 enc = SQLITE_UTF16BE; 1728 1730 } 1729 1731 #else ................................................................................ 1761 1763 pDestructor->nRef++; 1762 1764 } 1763 1765 p->u.pDestructor = pDestructor; 1764 1766 p->funcFlags = (p->funcFlags & SQLITE_FUNC_ENCMASK) | extraFlags; 1765 1767 testcase( p->funcFlags & SQLITE_DETERMINISTIC ); 1766 1768 p->xSFunc = xSFunc ? xSFunc : xStep; 1767 1769 p->xFinalize = xFinal; 1770 + p->xValue = xValue; 1771 + p->xInverse = xInverse; 1768 1772 p->pUserData = pUserData; 1769 1773 p->nArg = (u16)nArg; 1770 1774 return SQLITE_OK; 1771 1775 } 1772 1776 1773 1777 /* 1774 -** Create new user functions. 1778 +** Worker function used by utf-8 APIs that create new functions: 1779 +** 1780 +** sqlite3_create_function() 1781 +** sqlite3_create_function_v2() 1782 +** sqlite3_create_window_function() 1775 1783 */ 1776 -int sqlite3_create_function( 1784 +static int createFunctionApi( 1777 1785 sqlite3 *db, 1778 1786 const char *zFunc, 1779 1787 int nArg, 1780 1788 int enc, 1781 1789 void *p, 1782 - void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), 1783 - void (*xStep)(sqlite3_context*,int,sqlite3_value **), 1784 - void (*xFinal)(sqlite3_context*) 1785 -){ 1786 - return sqlite3_create_function_v2(db, zFunc, nArg, enc, p, xSFunc, xStep, 1787 - xFinal, 0); 1788 -} 1789 - 1790 -int sqlite3_create_function_v2( 1791 - sqlite3 *db, 1792 - const char *zFunc, 1793 - int nArg, 1794 - int enc, 1795 - void *p, 1796 - void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), 1797 - void (*xStep)(sqlite3_context*,int,sqlite3_value **), 1790 + void (*xSFunc)(sqlite3_context*,int,sqlite3_value**), 1791 + void (*xStep)(sqlite3_context*,int,sqlite3_value**), 1798 1792 void (*xFinal)(sqlite3_context*), 1799 - void (*xDestroy)(void *) 1793 + void (*xValue)(sqlite3_context*), 1794 + void (*xInverse)(sqlite3_context*,int,sqlite3_value**), 1795 + void(*xDestroy)(void*) 1800 1796 ){ 1801 1797 int rc = SQLITE_ERROR; 1802 1798 FuncDestructor *pArg = 0; 1803 1799 1804 1800 #ifdef SQLITE_ENABLE_API_ARMOR 1805 1801 if( !sqlite3SafetyCheckOk(db) ){ 1806 1802 return SQLITE_MISUSE_BKPT; ................................................................................ 1814 1810 xDestroy(p); 1815 1811 goto out; 1816 1812 } 1817 1813 pArg->nRef = 0; 1818 1814 pArg->xDestroy = xDestroy; 1819 1815 pArg->pUserData = p; 1820 1816 } 1821 - rc = sqlite3CreateFunc(db, zFunc, nArg, enc, p, xSFunc, xStep, xFinal, pArg); 1817 + rc = sqlite3CreateFunc(db, zFunc, nArg, enc, p, 1818 + xSFunc, xStep, xFinal, xValue, xInverse, pArg 1819 + ); 1822 1820 if( pArg && pArg->nRef==0 ){ 1823 1821 assert( rc!=SQLITE_OK ); 1824 1822 xDestroy(p); 1825 1823 sqlite3_free(pArg); 1826 1824 } 1827 1825 1828 1826 out: 1829 1827 rc = sqlite3ApiExit(db, rc); 1830 1828 sqlite3_mutex_leave(db->mutex); 1831 1829 return rc; 1832 1830 } 1831 + 1832 +/* 1833 +** Create new user functions. 1834 +*/ 1835 +int sqlite3_create_function( 1836 + sqlite3 *db, 1837 + const char *zFunc, 1838 + int nArg, 1839 + int enc, 1840 + void *p, 1841 + void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), 1842 + void (*xStep)(sqlite3_context*,int,sqlite3_value **), 1843 + void (*xFinal)(sqlite3_context*) 1844 +){ 1845 + return createFunctionApi(db, zFunc, nArg, enc, p, xSFunc, xStep, 1846 + xFinal, 0, 0, 0); 1847 +} 1848 +int sqlite3_create_function_v2( 1849 + sqlite3 *db, 1850 + const char *zFunc, 1851 + int nArg, 1852 + int enc, 1853 + void *p, 1854 + void (*xSFunc)(sqlite3_context*,int,sqlite3_value **), 1855 + void (*xStep)(sqlite3_context*,int,sqlite3_value **), 1856 + void (*xFinal)(sqlite3_context*), 1857 + void (*xDestroy)(void *) 1858 +){ 1859 + return createFunctionApi(db, zFunc, nArg, enc, p, xSFunc, xStep, 1860 + xFinal, 0, 0, xDestroy); 1861 +} 1862 +int sqlite3_create_window_function( 1863 + sqlite3 *db, 1864 + const char *zFunc, 1865 + int nArg, 1866 + int enc, 1867 + void *p, 1868 + void (*xStep)(sqlite3_context*,int,sqlite3_value **), 1869 + void (*xFinal)(sqlite3_context*), 1870 + void (*xValue)(sqlite3_context*), 1871 + void (*xInverse)(sqlite3_context*,int,sqlite3_value **), 1872 + void (*xDestroy)(void *) 1873 +){ 1874 + return createFunctionApi(db, zFunc, nArg, enc, p, 0, xStep, 1875 + xFinal, xValue, xInverse, xDestroy); 1876 +} 1833 1877 1834 1878 #ifndef SQLITE_OMIT_UTF16 1835 1879 int sqlite3_create_function16( 1836 1880 sqlite3 *db, 1837 1881 const void *zFunctionName, 1838 1882 int nArg, 1839 1883 int eTextRep, ................................................................................ 1847 1891 1848 1892 #ifdef SQLITE_ENABLE_API_ARMOR 1849 1893 if( !sqlite3SafetyCheckOk(db) || zFunctionName==0 ) return SQLITE_MISUSE_BKPT; 1850 1894 #endif 1851 1895 sqlite3_mutex_enter(db->mutex); 1852 1896 assert( !db->mallocFailed ); 1853 1897 zFunc8 = sqlite3Utf16to8(db, zFunctionName, -1, SQLITE_UTF16NATIVE); 1854 - rc = sqlite3CreateFunc(db, zFunc8, nArg, eTextRep, p, xSFunc,xStep,xFinal,0); 1898 + rc = sqlite3CreateFunc(db, zFunc8, nArg, eTextRep, p, xSFunc,xStep,xFinal,0,0,0); 1855 1899 sqlite3DbFree(db, zFunc8); 1856 1900 rc = sqlite3ApiExit(db, rc); 1857 1901 sqlite3_mutex_leave(db->mutex); 1858 1902 return rc; 1859 1903 } 1860 1904 #endif 1861 1905
Changes to src/sqlite.h.in.
4736 4736 int nArg, 4737 4737 int eTextRep, 4738 4738 void *pApp, 4739 4739 void (*xFunc)(sqlite3_context*,int,sqlite3_value**), 4740 4740 void (*xStep)(sqlite3_context*,int,sqlite3_value**), 4741 4741 void (*xFinal)(sqlite3_context*), 4742 4742 void(*xDestroy)(void*) 4743 +); 4744 +int sqlite3_create_window_function( 4745 + sqlite3 *db, 4746 + const char *zFunctionName, 4747 + int nArg, 4748 + int eTextRep, 4749 + void *pApp, 4750 + void (*xStep)(sqlite3_context*,int,sqlite3_value**), 4751 + void (*xFinal)(sqlite3_context*), 4752 + void (*xValue)(sqlite3_context*), 4753 + void (*xInverse)(sqlite3_context*,int,sqlite3_value**), 4754 + void(*xDestroy)(void*) 4743 4755 ); 4744 4756 4745 4757 /* 4746 4758 ** CAPI3REF: Text Encodings 4747 4759 ** 4748 4760 ** These constant define integer codes that represent the various 4749 4761 ** text encodings supported by SQLite.
Changes to src/sqliteInt.h.
4240 4240 KeyInfo *sqlite3KeyInfoFromExprList(Parse*, ExprList*, int, int); 4241 4241 4242 4242 #ifdef SQLITE_DEBUG 4243 4243 int sqlite3KeyInfoIsWriteable(KeyInfo*); 4244 4244 #endif 4245 4245 int sqlite3CreateFunc(sqlite3 *, const char *, int, int, void *, 4246 4246 void (*)(sqlite3_context*,int,sqlite3_value **), 4247 - void (*)(sqlite3_context*,int,sqlite3_value **), void (*)(sqlite3_context*), 4247 + void (*)(sqlite3_context*,int,sqlite3_value **), 4248 + void (*)(sqlite3_context*), 4249 + void (*)(sqlite3_context*), 4250 + void (*)(sqlite3_context*,int,sqlite3_value **), 4248 4251 FuncDestructor *pDestructor 4249 4252 ); 4250 4253 void sqlite3NoopDestructor(void*); 4251 4254 void sqlite3OomFault(sqlite3*); 4252 4255 void sqlite3OomClear(sqlite3*); 4253 4256 int sqlite3ApiExit(sqlite3 *db, int); 4254 4257 int sqlite3OpenTempDatabase(Parse *);
Changes to src/test_tclsh.c.
101 101 #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) 102 102 extern int Sqlitetestfts3_Init(Tcl_Interp *interp); 103 103 #endif 104 104 #ifdef SQLITE_ENABLE_ZIPVFS 105 105 extern int Zipvfs_Init(Tcl_Interp*); 106 106 #endif 107 107 extern int TestExpert_Init(Tcl_Interp*); 108 + extern int Sqlitetest_window_Init(Tcl_Interp *); 108 109 109 110 Tcl_CmdInfo cmdInfo; 110 111 111 112 /* Since the primary use case for this binary is testing of SQLite, 112 113 ** be sure to generate core files if we crash */ 113 114 #if defined(unix) 114 115 { struct rlimit x; ................................................................................ 165 166 SqliteRbu_Init(interp); 166 167 Sqlitetesttcl_Init(interp); 167 168 168 169 #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) 169 170 Sqlitetestfts3_Init(interp); 170 171 #endif 171 172 TestExpert_Init(interp); 173 + Sqlitetest_window_Init(interp); 172 174 173 175 Tcl_CreateObjCommand( 174 176 interp, "load_testfixture_extensions", load_testfixture_extensions,0,0 175 177 ); 176 178 return 0; 177 179 } 178 180
Added src/test_window.c.
1 +/* 2 +** 2018 June 17 3 +** 4 +** The author disclaims copyright to this source code. In place of 5 +** a legal notice, here is a blessing: 6 +** 7 +** May you do good and not evil. 8 +** May you find forgiveness for yourself and forgive others. 9 +** May you share freely, never taking more than you give. 10 +** 11 +************************************************************************* 12 +*/ 13 + 14 +#include "sqlite3.h" 15 + 16 +#ifdef SQLITE_TEST 17 + 18 +#include "sqliteInt.h" 19 +#include <tcl.h> 20 + 21 +extern int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb); 22 +extern const char *sqlite3ErrName(int); 23 + 24 +typedef struct TestWindow TestWindow; 25 +struct TestWindow { 26 + Tcl_Obj *xStep; 27 + Tcl_Obj *xFinal; 28 + Tcl_Obj *xValue; 29 + Tcl_Obj *xInverse; 30 + Tcl_Interp *interp; 31 +}; 32 + 33 +typedef struct TestWindowCtx TestWindowCtx; 34 +struct TestWindowCtx { 35 + Tcl_Obj *pVal; 36 +}; 37 + 38 +static void doTestWindowStep( 39 + int bInverse, 40 + sqlite3_context *ctx, 41 + int nArg, 42 + sqlite3_value **apArg 43 +){ 44 + int i; 45 + TestWindow *p = (TestWindow*)sqlite3_user_data(ctx); 46 + Tcl_Obj *pEval = Tcl_DuplicateObj(bInverse ? p->xInverse : p->xStep); 47 + TestWindowCtx *pCtx = sqlite3_aggregate_context(ctx, sizeof(TestWindowCtx)); 48 + 49 + Tcl_IncrRefCount(pEval); 50 + if( pCtx ){ 51 + const char *zResult; 52 + int rc; 53 + if( pCtx->pVal ){ 54 + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_DuplicateObj(pCtx->pVal)); 55 + }else{ 56 + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewStringObj("", -1)); 57 + } 58 + for(i=0; i<nArg; i++){ 59 + Tcl_Obj *pArg; 60 + pArg = Tcl_NewStringObj((const char*)sqlite3_value_text(apArg[i]), -1); 61 + Tcl_ListObjAppendElement(p->interp, pEval, pArg); 62 + } 63 + rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL); 64 + if( rc!=TCL_OK ){ 65 + zResult = Tcl_GetStringResult(p->interp); 66 + sqlite3_result_error(ctx, zResult, -1); 67 + }else{ 68 + if( pCtx->pVal ) Tcl_DecrRefCount(pCtx->pVal); 69 + pCtx->pVal = Tcl_DuplicateObj(Tcl_GetObjResult(p->interp)); 70 + Tcl_IncrRefCount(pCtx->pVal); 71 + } 72 + } 73 + Tcl_DecrRefCount(pEval); 74 +} 75 + 76 +static void doTestWindowFinalize(int bValue, sqlite3_context *ctx){ 77 + TestWindow *p = (TestWindow*)sqlite3_user_data(ctx); 78 + Tcl_Obj *pEval = Tcl_DuplicateObj(bValue ? p->xValue : p->xFinal); 79 + TestWindowCtx *pCtx = sqlite3_aggregate_context(ctx, sizeof(TestWindowCtx)); 80 + 81 + Tcl_IncrRefCount(pEval); 82 + if( pCtx ){ 83 + const char *zResult; 84 + int rc; 85 + if( pCtx->pVal ){ 86 + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_DuplicateObj(pCtx->pVal)); 87 + }else{ 88 + Tcl_ListObjAppendElement(p->interp, pEval, Tcl_NewStringObj("", -1)); 89 + } 90 + 91 + rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL); 92 + zResult = Tcl_GetStringResult(p->interp); 93 + if( rc!=TCL_OK ){ 94 + sqlite3_result_error(ctx, zResult, -1); 95 + }else{ 96 + sqlite3_result_text(ctx, zResult, -1, SQLITE_TRANSIENT); 97 + } 98 + 99 + if( bValue==0 ){ 100 + if( pCtx->pVal ) Tcl_DecrRefCount(pCtx->pVal); 101 + pCtx->pVal = 0; 102 + } 103 + } 104 + Tcl_DecrRefCount(pEval); 105 +} 106 + 107 +static void testWindowStep( 108 + sqlite3_context *ctx, 109 + int nArg, 110 + sqlite3_value **apArg 111 +){ 112 + doTestWindowStep(0, ctx, nArg, apArg); 113 +} 114 +static void testWindowInverse( 115 + sqlite3_context *ctx, 116 + int nArg, 117 + sqlite3_value **apArg 118 +){ 119 + doTestWindowStep(1, ctx, nArg, apArg); 120 +} 121 + 122 +static void testWindowFinal(sqlite3_context *ctx){ 123 + doTestWindowFinalize(0, ctx); 124 +} 125 +static void testWindowValue(sqlite3_context *ctx){ 126 + doTestWindowFinalize(1, ctx); 127 +} 128 + 129 +static void testWindowDestroy(void *pCtx){ 130 + ckfree(pCtx); 131 +} 132 + 133 +/* 134 +** Usage: sqlite3_create_window_function DB NAME XSTEP XFINAL XVALUE XINVERSE 135 +*/ 136 +static int SQLITE_TCLAPI test_create_window( 137 + void * clientData, 138 + Tcl_Interp *interp, 139 + int objc, 140 + Tcl_Obj *CONST objv[] 141 +){ 142 + TestWindow *pNew; 143 + sqlite3 *db; 144 + const char *zName; 145 + int rc; 146 + 147 + if( objc!=7 ){ 148 + Tcl_WrongNumArgs(interp, 1, objv, "DB NAME XSTEP XFINAL XVALUE XINVERSE"); 149 + return TCL_ERROR; 150 + } 151 + 152 + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; 153 + zName = Tcl_GetString(objv[2]); 154 + pNew = ckalloc(sizeof(TestWindow)); 155 + memset(pNew, 0, sizeof(TestWindow)); 156 + pNew->xStep = Tcl_DuplicateObj(objv[3]); 157 + pNew->xFinal = Tcl_DuplicateObj(objv[4]); 158 + pNew->xValue = Tcl_DuplicateObj(objv[5]); 159 + pNew->xInverse = Tcl_DuplicateObj(objv[6]); 160 + pNew->interp = interp; 161 + 162 + Tcl_IncrRefCount(pNew->xStep); 163 + Tcl_IncrRefCount(pNew->xFinal); 164 + Tcl_IncrRefCount(pNew->xValue); 165 + Tcl_IncrRefCount(pNew->xInverse); 166 + 167 + rc = sqlite3_create_window_function(db, zName, -1, SQLITE_UTF8, (void*)pNew, 168 + testWindowStep, testWindowFinal, testWindowValue, testWindowInverse, 169 + testWindowDestroy 170 + ); 171 + if( rc!=SQLITE_OK ){ 172 + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); 173 + return TCL_ERROR; 174 + } 175 + 176 + return TCL_OK; 177 +} 178 + 179 +int Sqlitetest_window_Init(Tcl_Interp *interp){ 180 + static struct { 181 + char *zName; 182 + Tcl_ObjCmdProc *xProc; 183 + int clientData; 184 + } aObjCmd[] = { 185 + { "sqlite3_create_window_function", test_create_window, 0 }, 186 + }; 187 + int i; 188 + for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){ 189 + ClientData c = (ClientData)SQLITE_INT_TO_PTR(aObjCmd[i].clientData); 190 + Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, c, 0); 191 + } 192 + return TCL_OK; 193 +} 194 +#endif
Changes to src/vdbe.c.
6410 6410 case OP_AggFinal: { 6411 6411 Mem *pMem; 6412 6412 assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) ); 6413 6413 pMem = &aMem[pOp->p1]; 6414 6414 assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 ); 6415 6415 if( pOp->p3 ){ 6416 6416 rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc); 6417 + pMem = &aMem[pOp->p3]; 6417 6418 }else{ 6418 6419 rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc); 6419 6420 } 6420 6421 if( rc ){ 6421 6422 sqlite3VdbeError(p, "%s", sqlite3_value_text(pMem)); 6422 6423 goto abort_due_to_error; 6423 6424 }
Changes to src/window.c.
296 296 int nArg, 297 297 sqlite3_value **apArg 298 298 ){ 299 299 } 300 300 static void cume_distValueFunc(sqlite3_context *pCtx){ 301 301 struct CallCount *p; 302 302 p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p)); 303 - if( p ){ 303 + if( p && p->nTotal ){ 304 304 double r = (double)(p->nStep) / (double)(p->nTotal); 305 305 sqlite3_result_double(pCtx, r); 306 306 } 307 307 } 308 308 309 309 /* 310 310 ** Context object for ntile() window function. ................................................................................ 680 680 ** are invoked in the correct order as described under "SELECT REWRITING" 681 681 ** at the top of this file. 682 682 */ 683 683 int sqlite3WindowRewrite(Parse *pParse, Select *p){ 684 684 int rc = SQLITE_OK; 685 685 if( p->pWin ){ 686 686 Vdbe *v = sqlite3GetVdbe(pParse); 687 - int i; 688 687 sqlite3 *db = pParse->db; 689 688 Select *pSub = 0; /* The subquery */ 690 689 SrcList *pSrc = p->pSrc; 691 690 Expr *pWhere = p->pWhere; 692 691 ExprList *pGroupBy = p->pGroupBy; 693 692 Expr *pHaving = p->pHaving; 694 693 ExprList *pSort = 0; ................................................................................ 739 738 740 739 pSub = sqlite3SelectNew( 741 740 pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0 742 741 ); 743 742 p->pSrc = sqlite3SrcListAppend(db, 0, 0, 0); 744 743 assert( p->pSrc || db->mallocFailed ); 745 744 if( p->pSrc ){ 746 - int iTab; 747 - ExprList *pList = 0; 748 745 p->pSrc->a[0].pSelect = pSub; 749 746 sqlite3SrcListAssignCursors(pParse, p->pSrc); 750 747 if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){ 751 748 rc = SQLITE_NOMEM; 752 749 }else{ 753 750 pSub->selFlags |= SF_Expanded; 754 751 p->selFlags &= ~SF_Aggregate; ................................................................................ 1084 1081 WhereInfo *pWInfo, /* WhereInfo to call WhereEnd() on */ 1085 1082 int regFlushPart, /* Register to use with Gosub lblFlushPart */ 1086 1083 int lblFlushPart, /* Subroutine to Gosub to */ 1087 1084 int *pRegSize /* OUT: Register containing partition size */ 1088 1085 ){ 1089 1086 Window *pMWin = p->pWin; 1090 1087 Vdbe *v = sqlite3GetVdbe(pParse); 1091 - Window *pWin; 1092 1088 int iSubCsr = p->pSrc->a[0].iCursor; 1093 1089 int nSub = p->pSrc->a[0].pTab->nCol; 1094 1090 int k; 1095 1091 1096 1092 int reg = pParse->nMem+1; 1097 1093 int regRecord = reg+nSub; 1098 1094 int regRowid = regRecord+1; ................................................................................ 1406 1402 Select *p, 1407 1403 WhereInfo *pWInfo, 1408 1404 int regGosub, 1409 1405 int addrGosub 1410 1406 ){ 1411 1407 Window *pMWin = p->pWin; 1412 1408 Vdbe *v = sqlite3GetVdbe(pParse); 1413 - Window *pWin; 1414 - int k; 1415 - int nSub = p->pSrc->a[0].pTab->nCol; 1416 1409 int regFlushPart; /* Register for "Gosub flush_partition" */ 1417 1410 int lblFlushPart; /* Label for "Gosub flush_partition" */ 1418 1411 int lblFlushDone; /* Label for "Gosub flush_partition_done" */ 1419 1412 1420 1413 int regArg; 1421 - int nArg; 1422 1414 int addr; 1423 1415 int csrStart = pParse->nTab++; 1424 1416 int csrEnd = pParse->nTab++; 1425 1417 int regStart; /* Value of <expr> PRECEDING */ 1426 1418 int regEnd; /* Value of <expr> FOLLOWING */ 1427 - int addrNext; 1428 1419 int addrGoto; 1429 1420 int addrTop; 1430 1421 int addrIfPos1; 1431 1422 int addrIfPos2; 1432 - 1433 - int regPeer = 0; /* Number of peers in current group */ 1434 - int regPeerVal = 0; /* Array of values identifying peer group */ 1435 - int iPeer = 0; /* Column offset in eph-table of peer vals */ 1436 - int nPeerVal; /* Number of peer values */ 1437 1423 int regSize = 0; 1438 1424 1439 1425 assert( pMWin->eStart==TK_PRECEDING 1440 1426 || pMWin->eStart==TK_CURRENT 1441 1427 || pMWin->eStart==TK_FOLLOWING 1442 1428 || pMWin->eStart==TK_UNBOUNDED 1443 1429 ); ................................................................................ 1675 1661 Select *p, 1676 1662 WhereInfo *pWInfo, 1677 1663 int regGosub, 1678 1664 int addrGosub 1679 1665 ){ 1680 1666 Window *pMWin = p->pWin; 1681 1667 Vdbe *v = sqlite3GetVdbe(pParse); 1682 - Window *pWin; 1683 1668 int k; 1684 1669 int addr; 1685 1670 ExprList *pPart = pMWin->pPartition; 1686 1671 ExprList *pOrderBy = pMWin->pOrderBy; 1687 1672 int nPeer = pOrderBy ? pOrderBy->nExpr : 0; 1688 1673 int regNewPeer; 1689 1674 ................................................................................ 1691 1676 int addrNext; /* Jump here for next iteration of loop */ 1692 1677 int regFlushPart; 1693 1678 int lblFlushPart; 1694 1679 int csrLead; 1695 1680 int regCtr; 1696 1681 int regArg; /* Register array to martial function args */ 1697 1682 int regSize; 1698 - int nArg; 1699 1683 int lblEmpty; 1700 1684 int bReverse = pMWin->pOrderBy && pMWin->eStart==TK_CURRENT 1701 1685 && pMWin->eEnd==TK_UNBOUNDED; 1702 1686 1703 1687 assert( (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT) 1704 1688 || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_UNBOUNDED) 1705 1689 || (pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_CURRENT) ................................................................................ 1818 1802 Select *p, 1819 1803 WhereInfo *pWInfo, 1820 1804 int regGosub, 1821 1805 int addrGosub 1822 1806 ){ 1823 1807 Window *pMWin = p->pWin; 1824 1808 Vdbe *v = sqlite3GetVdbe(pParse); 1825 - Window *pWin; 1826 1809 int k; 1827 1810 int iSubCsr = p->pSrc->a[0].iCursor; 1828 1811 int nSub = p->pSrc->a[0].pTab->nCol; 1829 1812 int reg = pParse->nMem+1; 1830 1813 int regRecord = reg+nSub; 1831 1814 int regRowid = regRecord+1; 1832 1815 int addr;
Changes to test/window1.test.
265 265 4 6 8 6 8 10 8 10 default 10 {} default {} {} default 266 266 } 267 267 268 268 do_execsql_test 7.3 { 269 269 SELECT row_number() OVER (ORDER BY x) FROM t1 270 270 } {1 2 3 4 5} 271 271 272 +do_execsql_test 7.4 { 273 + SELECT 274 + row_number() OVER win, 275 + lead(x) OVER win 276 + FROM t1 277 + WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 278 +} {1 3 2 5 3 7 4 9 5 {}} 272 279 273 280 finish_test 274 281
Added test/window5.test.
1 +# 2018 May 8 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. Specifically, 12 +# it tests the sqlite3_create_window_function() API. 13 +# 14 + 15 +set testdir [file dirname $argv0] 16 +source $testdir/tester.tcl 17 +set testprefix window1 18 + 19 +proc m_step {ctx val} { 20 + lappend ctx $val 21 + return $ctx 22 +} 23 +proc m_value {ctx} { 24 + set lSort [lsort $ctx] 25 + 26 + set nVal [llength $lSort] 27 + set n [expr $nVal/2] 28 + 29 + if {($nVal % 2)==0 && $nVal>0} { 30 + set a [lindex $lSort $n] 31 + set b [lindex $lSort $n-1] 32 + if {($a+$b) % 2} { 33 + set ret [expr ($a+$b)/2.0] 34 + } else { 35 + set ret [expr ($a+$b)/2] 36 + } 37 + } else { 38 + set ret [lindex $lSort $n] 39 + } 40 + return $ret 41 +} 42 +proc m_inverse {ctx val} { 43 + set ctx [lrange $ctx 1 end] 44 + return $ctx 45 +} 46 +proc w_value {ctx} { 47 + lsort $ctx 48 +} 49 + 50 +sqlite3_create_window_function db median m_step m_value m_value m_inverse 51 +sqlite3_create_window_function db win m_step w_value w_value m_inverse 52 + 53 +do_execsql_test 1.0 { 54 + CREATE TABLE t1(a, b); 55 + INSERT INTO t1 VALUES(4, 'a'); 56 + INSERT INTO t1 VALUES(6, 'b'); 57 + INSERT INTO t1 VALUES(1, 'c'); 58 + INSERT INTO t1 VALUES(5, 'd'); 59 + INSERT INTO t1 VALUES(2, 'e'); 60 + INSERT INTO t1 VALUES(3, 'f'); 61 +} 62 + 63 +do_execsql_test 1.1 { 64 + SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1; 65 +} {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} 66 + 67 +finish_test 68 +