SQLite Forum

DROP TABLE failure - is it intentional or a bug?
Login
Here is sample extension code that reports itself as deterministic but returns a constant unix epoch floating point timestamp (for Windows) that attempts to be statement stable by caching its result using the auxdata capability.

```
/*
** TimeStamp function returns a floating point unix epoch time.
** It calls the Windows PreciseTime API directly and therefore has a
** theoretical precision of 100 nanoseconds for the source time
** reduced and bounded by the IEEE floating point conversion.
**
** This value will attempt to preserve its value across invocations
** by attaching the value as auxdata to the single required parameter
** that should be a statement compile time constant, there is no way
** to ensure that this requirement is being met.
*/

#include <windows.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

static void _TimeStamp(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    sqlite_int64 huns = 0;
    double *timestamp = sqlite3_get_auxdata(context, 0);

    if (!timestamp)
    {
        timestamp = sqlite3_malloc(sizeof(double));
        if (!timestamp)
        {
            sqlite3_result_error_nomem(context);
            return;
        }
        GetSystemTimePreciseAsFileTime((void*)&huns);
        *timestamp =  ((double)(huns - 116444736000000000ll)) / 1.0e7;
    }
    sqlite3_result_double(context, *timestamp);
    if (huns)
        sqlite3_set_auxdata(context, 0, timestamp, sqlite3_free);
}

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_timestamp_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);
    return sqlite3_create_function(db, "TimeStamp", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _TimeStamp, 0, 0);
}
```