mirror of
https://github.com/tursodatabase/libsql.git
synced 2025-03-08 22:31:40 +00:00
d3a156caf5
A common complain with libSQL is how to run extensions. The main mechanism, with a .so, has a lot of issues around how those .so are distributed. The most common extensions are the ones in the sqlean package. We can improve this experience by bundling them in our sqlite build. Not all SQLean extensions are kosher: some of them, like fileio, use the vfs. Others, are deemed too complex. The extensions included here are a subset that we deem important enough, and low risk enough, to just be a part of the main bundle.
799 lines
28 KiB
C
799 lines
28 KiB
C
// Copyright (c) 2024 Anton Zhiyanov, MIT License
|
|
// https://github.com/nalgeon/sqlean
|
|
|
|
// SQLite extension for working with time.
|
|
|
|
#include <assert.h>
|
|
#include <stdint.h>
|
|
#include <stdio.h>
|
|
#include <stdlib.h>
|
|
#include <string.h>
|
|
|
|
#include "sqlite3ext.h"
|
|
SQLITE_EXTENSION_INIT3
|
|
|
|
#include "time/timex.h"
|
|
|
|
// result_blob converts a Time value to a blob and sets it as the result.
|
|
static void result_blob(sqlite3_context* context, Time t) {
|
|
uint8_t buf[TIMEX_BLOB_SIZE];
|
|
time_to_blob(t, buf);
|
|
sqlite3_result_blob(context, buf, sizeof(buf), SQLITE_TRANSIENT);
|
|
}
|
|
|
|
// time_now()
|
|
static void fn_now(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 0);
|
|
Time t = time_now();
|
|
result_blob(context, t);
|
|
}
|
|
|
|
// time_date(year, month, day[, hour, min, sec[, nsec[, offset_sec]]])
|
|
static void fn_date(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 3 || argc == 6 || argc == 7 || argc == 8);
|
|
for (int i = 0; i < argc; i++) {
|
|
if (sqlite3_value_type(argv[i]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "all parameters should be integers", -1);
|
|
return;
|
|
}
|
|
}
|
|
int year = sqlite3_value_int(argv[0]);
|
|
int month = sqlite3_value_int(argv[1]);
|
|
int day = sqlite3_value_int(argv[2]);
|
|
|
|
int hour = 0;
|
|
int min = 0;
|
|
int sec = 0;
|
|
if (argc >= 6) {
|
|
hour = sqlite3_value_int(argv[3]);
|
|
min = sqlite3_value_int(argv[4]);
|
|
sec = sqlite3_value_int(argv[5]);
|
|
}
|
|
|
|
int nsec = 0;
|
|
if (argc >= 7) {
|
|
nsec = sqlite3_value_int(argv[6]);
|
|
}
|
|
|
|
int offset_sec = 0;
|
|
if (argc == 8) {
|
|
offset_sec = sqlite3_value_int(argv[7]);
|
|
}
|
|
|
|
Time t = time_date(year, month, day, hour, min, sec, nsec, offset_sec);
|
|
result_blob(context, t);
|
|
}
|
|
|
|
// time_get_year(t)
|
|
// time_get_month(t)
|
|
// time_get_day(t)
|
|
// time_get_hour(t)
|
|
// time_get_minute(t)
|
|
// time_get_second(t)
|
|
// time_get_nano(t)
|
|
// time_get_weekday(t)
|
|
// time_get_yearday(t)
|
|
static void fn_extract(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "parameter should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "invalid time blob size", -1);
|
|
return;
|
|
}
|
|
int (*extract)(Time t) = (int (*)(Time t))sqlite3_user_data(context);
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
sqlite3_result_int(context, extract(t));
|
|
}
|
|
|
|
// time_get_isoyear(t)
|
|
static void fn_get_isoyear(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "parameter should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
int year, week;
|
|
time_get_isoweek(t, &year, &week);
|
|
sqlite3_result_int(context, year);
|
|
}
|
|
|
|
// time_get_isoweek(t)
|
|
static void fn_get_isoweek(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "parameter should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
int year, week;
|
|
time_get_isoweek(t, &year, &week);
|
|
sqlite3_result_int(context, week);
|
|
}
|
|
|
|
// get_field returns a part of the t according to a given field
|
|
static void get_field(sqlite3_context* context, Time t, const char* field) {
|
|
// millennium, century, decade
|
|
if (strcmp(field, "millennium") == 0) {
|
|
int millennium = time_get_year(t) / 1000;
|
|
sqlite3_result_int(context, millennium);
|
|
return;
|
|
}
|
|
if (strcmp(field, "century") == 0) {
|
|
int century = time_get_year(t) / 100;
|
|
sqlite3_result_int(context, century);
|
|
return;
|
|
}
|
|
if (strncmp(field, "decade", 6) == 0) {
|
|
int decade = time_get_year(t) / 10;
|
|
sqlite3_result_int(context, decade);
|
|
return;
|
|
}
|
|
|
|
// year, quarter, month, day
|
|
if (strcmp(field, "year") == 0 || strcmp(field, "years") == 0) {
|
|
sqlite3_result_int(context, time_get_year(t));
|
|
return;
|
|
}
|
|
if (strncmp(field, "quarter", 7) == 0) {
|
|
int quarter = (time_get_month(t) - 1) / 3 + 1;
|
|
sqlite3_result_int(context, quarter);
|
|
return;
|
|
}
|
|
if (strncmp(field, "month", 5) == 0) {
|
|
sqlite3_result_int(context, time_get_month(t));
|
|
return;
|
|
}
|
|
if (strcmp(field, "day") == 0 || strcmp(field, "days") == 0) {
|
|
sqlite3_result_int(context, time_get_day(t));
|
|
return;
|
|
}
|
|
|
|
// hour, minute, second
|
|
if (strncmp(field, "hour", 4) == 0) {
|
|
sqlite3_result_int(context, time_get_hour(t));
|
|
return;
|
|
}
|
|
if (strncmp(field, "minute", 6) == 0) {
|
|
sqlite3_result_int(context, time_get_minute(t));
|
|
return;
|
|
}
|
|
if (strncmp(field, "second", 6) == 0) {
|
|
// including fractional part
|
|
double sec = time_get_second(t) + t.nsec / 1e9;
|
|
sqlite3_result_double(context, sec);
|
|
return;
|
|
}
|
|
|
|
// millisecond, microsecond, nanosecond
|
|
if (strncmp(field, "milli", 5) == 0) {
|
|
int msec = time_get_nano(t) / 1000000;
|
|
sqlite3_result_int(context, msec);
|
|
return;
|
|
}
|
|
if (strncmp(field, "micro", 5) == 0) {
|
|
int usec = time_get_nano(t) / 1000;
|
|
sqlite3_result_int(context, usec);
|
|
return;
|
|
}
|
|
if (strncmp(field, "nano", 4) == 0) {
|
|
sqlite3_result_int(context, time_get_nano(t));
|
|
return;
|
|
}
|
|
|
|
// isoyear, isoweek, isodow, yearday, weekday
|
|
if (strcmp(field, "isoyear") == 0) {
|
|
int year, week;
|
|
time_get_isoweek(t, &year, &week);
|
|
sqlite3_result_int(context, year);
|
|
return;
|
|
}
|
|
if (strcmp(field, "isoweek") == 0 || strcmp(field, "week") == 0) {
|
|
int year, week;
|
|
time_get_isoweek(t, &year, &week);
|
|
sqlite3_result_int(context, week);
|
|
return;
|
|
}
|
|
if (strcmp(field, "isodow") == 0) {
|
|
int isodow = time_get_weekday(t) == 0 ? 7 : time_get_weekday(t);
|
|
sqlite3_result_int(context, isodow);
|
|
return;
|
|
}
|
|
if (strcmp(field, "yearday") == 0 || strcmp(field, "doy") == 0 ||
|
|
strcmp(field, "dayofyear") == 0) {
|
|
sqlite3_result_int(context, time_get_yearday(t));
|
|
return;
|
|
}
|
|
if (strcmp(field, "weekday") == 0 || strcmp(field, "dow") == 0 ||
|
|
strcmp(field, "dayofweek") == 0) {
|
|
sqlite3_result_int(context, time_get_weekday(t));
|
|
return;
|
|
}
|
|
|
|
// epoch
|
|
if (strcmp(field, "epoch") == 0) {
|
|
// including fractional part
|
|
double epoch = time_to_unix(t) + t.nsec / 1e9;
|
|
sqlite3_result_double(context, epoch);
|
|
return;
|
|
}
|
|
|
|
sqlite3_result_error(context, "unknown field", -1);
|
|
}
|
|
|
|
// time_get(t, field)
|
|
static void fn_get(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_TEXT) {
|
|
sqlite3_result_error(context, "2nd parameter: should be a field name", -1);
|
|
return;
|
|
}
|
|
const char* field = (const char*)sqlite3_value_text(argv[1]);
|
|
|
|
get_field(context, t, field);
|
|
}
|
|
|
|
// date_part(field, t)
|
|
// Postgres-compatible.
|
|
static void date_part(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_TEXT) {
|
|
sqlite3_result_error(context, "1st parameter: should be a field name", -1);
|
|
return;
|
|
}
|
|
const char* field = (const char*)sqlite3_value_text(argv[0]);
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "2nd parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[1]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "2nd parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[1]));
|
|
|
|
get_field(context, t, field);
|
|
}
|
|
|
|
// time_unix(sec[, nsec])
|
|
static void fn_unix(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1 || argc == 2);
|
|
for (int i = 0; i < argc; i++) {
|
|
if (sqlite3_value_type(argv[i]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "all parameters should be integers", -1);
|
|
return;
|
|
}
|
|
}
|
|
|
|
int64_t sec = sqlite3_value_int64(argv[0]);
|
|
int64_t nsec = 0;
|
|
if (argc == 2) {
|
|
nsec = sqlite3_value_int64(argv[1]);
|
|
}
|
|
|
|
Time t = time_unix(sec, nsec);
|
|
result_blob(context, t);
|
|
}
|
|
|
|
// time_milli(msec)
|
|
// time_micro(usec)
|
|
// time_nano(nsec)
|
|
static void fn_unix_n(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "parameter should be an integer", -1);
|
|
return;
|
|
}
|
|
int64_t n = sqlite3_value_int64(argv[0]);
|
|
Time (*convert)(int64_t n) = (Time(*)(int64_t))sqlite3_user_data(context);
|
|
Time t = convert(n);
|
|
result_blob(context, t);
|
|
}
|
|
|
|
// time_to_unix(t)
|
|
// time_to_milli(t)
|
|
// time_to_micro(t)
|
|
// time_to_nano(t)
|
|
static void fn_convert(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "parameter should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "invalid time blob size", -1);
|
|
return;
|
|
}
|
|
int64_t (*convert)(Time t) = (int64_t(*)(Time t))sqlite3_user_data(context);
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
sqlite3_result_int64(context, convert(t));
|
|
}
|
|
|
|
// time_after(t, u)
|
|
// time_before(t, u)
|
|
// time_compare(t, u)
|
|
// time_equal(t, u)
|
|
static void fn_compare(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "2nd parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[1]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "2nd parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time u = time_blob(sqlite3_value_blob(argv[1]));
|
|
|
|
int (*compare)(Time t, Time u) = (int (*)(Time, Time))sqlite3_user_data(context);
|
|
sqlite3_result_int(context, compare(t, u));
|
|
}
|
|
|
|
// time_add(t, d)
|
|
static void fn_add(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "2nd parameter: should be an integer", -1);
|
|
return;
|
|
}
|
|
Duration d = sqlite3_value_int64(argv[1]);
|
|
|
|
Time r = time_add(t, d);
|
|
result_blob(context, r);
|
|
}
|
|
|
|
// time_sub(t, u)
|
|
static void fn_sub(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "2nd parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[1]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "2nd parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time u = time_blob(sqlite3_value_blob(argv[1]));
|
|
|
|
Duration d = time_sub(t, u);
|
|
sqlite3_result_int64(context, d);
|
|
}
|
|
|
|
// time_since(t)
|
|
static void fn_since(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "parameter should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
Duration d = time_since(t);
|
|
sqlite3_result_int64(context, d);
|
|
}
|
|
|
|
// time_until(t)
|
|
static void fn_until(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "parameter should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
Duration d = time_until(t);
|
|
sqlite3_result_int64(context, d);
|
|
}
|
|
|
|
// time_add_date(t, years[, months[, days]])
|
|
static void fn_add_date(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2 || argc == 3 || argc == 4);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "2nd parameter: should be an integer", -1);
|
|
return;
|
|
}
|
|
int years = sqlite3_value_int(argv[1]);
|
|
|
|
int months = 0;
|
|
if (argc >= 3) {
|
|
if (sqlite3_value_type(argv[2]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "3rd parameter: should be an integer", -1);
|
|
return;
|
|
}
|
|
months = sqlite3_value_int(argv[2]);
|
|
}
|
|
|
|
int days = 0;
|
|
if (argc == 4) {
|
|
if (sqlite3_value_type(argv[3]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "4th parameter: should be an integer", -1);
|
|
return;
|
|
}
|
|
days = sqlite3_value_int(argv[3]);
|
|
}
|
|
|
|
Time r = time_add_date(t, years, months, days);
|
|
result_blob(context, r);
|
|
}
|
|
|
|
// trunc_field truncates t according to a given field
|
|
static void trunc_field(sqlite3_context* context, Time t, const char* field) {
|
|
// millennium, century, decade
|
|
if (strcmp(field, "millennium") == 0) {
|
|
int year = time_get_year(t);
|
|
int millennium = year / 1000 * 1000;
|
|
Time r = time_date(millennium, January, 1, 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "century") == 0) {
|
|
int year = time_get_year(t);
|
|
int century = year / 100 * 100;
|
|
Time r = time_date(century, January, 1, 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "decade") == 0) {
|
|
int year = time_get_year(t);
|
|
int decade = year / 10 * 10;
|
|
Time r = time_date(decade, January, 1, 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
|
|
// year, quarter, month, week, day
|
|
if (strcmp(field, "year") == 0) {
|
|
Time r = time_date(time_get_year(t), January, 1, 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "quarter") == 0) {
|
|
int quarter = (time_get_month(t) - 1) / 3;
|
|
Time r = time_date(time_get_year(t), quarter * 3 + 1, 1, 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "month") == 0) {
|
|
Time r = time_date(time_get_year(t), time_get_month(t), 1, 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "week") == 0) {
|
|
int year, week;
|
|
time_get_isoweek(t, &year, &week);
|
|
Time r = time_date(year, January, 1, 0, 0, 0, 0, 0);
|
|
r = time_add_date(r, 0, 0, (week - 1) * 7);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "day") == 0) {
|
|
Time r =
|
|
time_date(time_get_year(t), time_get_month(t), time_get_day(t), 0, 0, 0, 0, TIMEX_UTC);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
|
|
// hour, minute, second, millisecond, microsecond
|
|
if (strcmp(field, "hour") == 0) {
|
|
Time r = time_truncate(t, Hour);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "minute") == 0) {
|
|
Time r = time_truncate(t, Minute);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strcmp(field, "second") == 0) {
|
|
Time r = time_truncate(t, Second);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strncmp(field, "milli", 5) == 0) {
|
|
int64_t nsec = (t.nsec / 1000000) * 1000000;
|
|
Time r = time_unix(time_to_unix(t), nsec);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
if (strncmp(field, "micro", 5) == 0) {
|
|
int64_t nsec = (t.nsec / 1000) * 1000;
|
|
Time r = time_unix(time_to_unix(t), nsec);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
|
|
sqlite3_result_error(context, "unknown field", -1);
|
|
}
|
|
|
|
// time_trunc(t, field)
|
|
// time_trunc(t, d)
|
|
static void fn_trunc(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
|
|
// first parameter is a time blob
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
// second parameter can be a custom duration
|
|
if (sqlite3_value_type(argv[1]) == SQLITE_INTEGER) {
|
|
// truncate to custom duration
|
|
Duration d = sqlite3_value_int64(argv[1]);
|
|
Time r = time_truncate(t, d);
|
|
result_blob(context, r);
|
|
return;
|
|
}
|
|
|
|
// or a field name
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_TEXT) {
|
|
sqlite3_result_error(context, "2nd parameter: should be a field name", -1);
|
|
return;
|
|
}
|
|
const char* field = (const char*)sqlite3_value_text(argv[1]);
|
|
|
|
// truncate to field
|
|
trunc_field(context, t, field);
|
|
}
|
|
|
|
// date_trunc(field, t)
|
|
// Postgres-compatible.
|
|
static void date_trunc(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
|
|
// first parameter is a field name
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_TEXT) {
|
|
sqlite3_result_error(context, "1st parameter: should be a field name", -1);
|
|
return;
|
|
}
|
|
const char* field = (const char*)sqlite3_value_text(argv[0]);
|
|
|
|
// second parameter is a time blob
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "2nd parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[1]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "2nd parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[1]));
|
|
|
|
trunc_field(context, t, field);
|
|
}
|
|
|
|
// time_round(t, d)
|
|
static void fn_round(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 2);
|
|
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "2nd parameter: should be an integer", -1);
|
|
return;
|
|
}
|
|
Duration d = sqlite3_value_int64(argv[1]);
|
|
|
|
Time r = time_round(t, d);
|
|
result_blob(context, r);
|
|
}
|
|
|
|
// time_fmt_iso(t[, offset_sec])
|
|
// time_fmt_datetime(t[, offset_sec])
|
|
// time_fmt_date(t[, offset_sec])
|
|
// time_fmt_time(t[, offset_sec])
|
|
static void fn_format(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1 || argc == 2);
|
|
if (sqlite3_value_type(argv[0]) != SQLITE_BLOB) {
|
|
sqlite3_result_error(context, "1st parameter: should be a time blob", -1);
|
|
return;
|
|
}
|
|
if (sqlite3_value_bytes(argv[0]) != TIMEX_BLOB_SIZE) {
|
|
sqlite3_result_error(context, "1st parameter: invalid time blob size", -1);
|
|
return;
|
|
}
|
|
Time t = time_blob(sqlite3_value_blob(argv[0]));
|
|
|
|
int offset_sec = 0;
|
|
if (argc == 2) {
|
|
if (sqlite3_value_type(argv[1]) != SQLITE_INTEGER) {
|
|
sqlite3_result_error(context, "2nd parameter: should be an integer", -1);
|
|
return;
|
|
}
|
|
offset_sec = sqlite3_value_int(argv[1]);
|
|
}
|
|
|
|
char buf[36];
|
|
size_t (*format)(char* buf, size_t size, Time t, int offset_sec) =
|
|
(size_t(*)(char*, size_t, Time, int))sqlite3_user_data(context);
|
|
format(buf, sizeof(buf), t, offset_sec);
|
|
sqlite3_result_text(context, buf, -1, SQLITE_TRANSIENT);
|
|
}
|
|
|
|
// time_parse(v)
|
|
static void fn_parse(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 1);
|
|
const char* val = (const char*)sqlite3_value_text(argv[0]);
|
|
Time t = time_parse(val);
|
|
result_blob(context, t);
|
|
}
|
|
|
|
// dur_h(), dur_m(), dur_s(), dur_ms(), dur_us(), dur_ns()
|
|
static void fn_dur_const(sqlite3_context* context, int argc, sqlite3_value** argv) {
|
|
assert(argc == 0);
|
|
int64_t d = (intptr_t)sqlite3_user_data(context);
|
|
sqlite3_result_int64(context, d);
|
|
}
|
|
|
|
int time_init(sqlite3* db) {
|
|
static const int flags = SQLITE_UTF8 | SQLITE_INNOCUOUS | SQLITE_DETERMINISTIC;
|
|
static const int flags_nd = SQLITE_UTF8 | SQLITE_INNOCUOUS;
|
|
|
|
// constructors
|
|
sqlite3_create_function(db, "time_now", 0, flags_nd, 0, fn_now, 0, 0);
|
|
sqlite3_create_function(db, "time_date", 3, flags, 0, fn_date, 0, 0);
|
|
sqlite3_create_function(db, "time_date", 6, flags, 0, fn_date, 0, 0);
|
|
sqlite3_create_function(db, "time_date", 7, flags, 0, fn_date, 0, 0);
|
|
sqlite3_create_function(db, "time_date", 8, flags, 0, fn_date, 0, 0);
|
|
|
|
// time parts
|
|
sqlite3_create_function(db, "time_get_year", 1, flags, time_get_year, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_month", 1, flags, time_get_month, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_day", 1, flags, time_get_day, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_hour", 1, flags, time_get_hour, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_minute", 1, flags, time_get_minute, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_second", 1, flags, time_get_second, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_nano", 1, flags, time_get_nano, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_weekday", 1, flags, time_get_weekday, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_yearday", 1, flags, time_get_yearday, fn_extract, 0, 0);
|
|
sqlite3_create_function(db, "time_get_isoyear", 1, flags, 0, fn_get_isoyear, 0, 0);
|
|
sqlite3_create_function(db, "time_get_isoweek", 1, flags, 0, fn_get_isoweek, 0, 0);
|
|
sqlite3_create_function(db, "time_get", 2, flags, 0, fn_get, 0, 0);
|
|
|
|
// unix time
|
|
sqlite3_create_function(db, "time_unix", 1, flags, 0, fn_unix, 0, 0);
|
|
sqlite3_create_function(db, "time_unix", 2, flags, 0, fn_unix, 0, 0);
|
|
sqlite3_create_function(db, "time_milli", 1, flags, time_milli, fn_unix_n, 0, 0);
|
|
sqlite3_create_function(db, "time_micro", 1, flags, time_micro, fn_unix_n, 0, 0);
|
|
sqlite3_create_function(db, "time_nano", 1, flags, time_nano, fn_unix_n, 0, 0);
|
|
sqlite3_create_function(db, "time_to_unix", 1, flags, time_to_unix, fn_convert, 0, 0);
|
|
sqlite3_create_function(db, "time_to_milli", 1, flags, time_to_milli, fn_convert, 0, 0);
|
|
sqlite3_create_function(db, "time_to_micro", 1, flags, time_to_micro, fn_convert, 0, 0);
|
|
sqlite3_create_function(db, "time_to_nano", 1, flags, time_to_nano, fn_convert, 0, 0);
|
|
|
|
// comparison
|
|
sqlite3_create_function(db, "time_after", 2, flags, time_after, fn_compare, 0, 0);
|
|
sqlite3_create_function(db, "time_before", 2, flags, time_before, fn_compare, 0, 0);
|
|
sqlite3_create_function(db, "time_compare", 2, flags, time_compare, fn_compare, 0, 0);
|
|
sqlite3_create_function(db, "time_equal", 2, flags, time_equal, fn_compare, 0, 0);
|
|
|
|
// arithmetic
|
|
sqlite3_create_function(db, "time_add", 2, flags, 0, fn_add, 0, 0);
|
|
sqlite3_create_function(db, "time_sub", 2, flags, 0, fn_sub, 0, 0);
|
|
sqlite3_create_function(db, "time_since", 1, flags_nd, 0, fn_since, 0, 0);
|
|
sqlite3_create_function(db, "time_until", 1, flags_nd, 0, fn_until, 0, 0);
|
|
sqlite3_create_function(db, "time_add_date", 2, flags, 0, fn_add_date, 0, 0);
|
|
sqlite3_create_function(db, "time_add_date", 3, flags, 0, fn_add_date, 0, 0);
|
|
sqlite3_create_function(db, "time_add_date", 4, flags, 0, fn_add_date, 0, 0);
|
|
|
|
// rounding
|
|
sqlite3_create_function(db, "time_trunc", 2, flags, 0, fn_trunc, 0, 0);
|
|
sqlite3_create_function(db, "time_round", 2, flags, 0, fn_round, 0, 0);
|
|
|
|
// formatting
|
|
sqlite3_create_function(db, "time_fmt_iso", 1, flags, time_fmt_iso, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_iso", 2, flags, time_fmt_iso, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_datetime", 1, flags, time_fmt_datetime, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_datetime", 2, flags, time_fmt_datetime, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_date", 1, flags, time_fmt_date, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_date", 2, flags, time_fmt_date, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_time", 1, flags, time_fmt_time, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_fmt_time", 2, flags, time_fmt_time, fn_format, 0, 0);
|
|
sqlite3_create_function(db, "time_parse", 1, flags, 0, fn_parse, 0, 0);
|
|
|
|
// duration constants
|
|
sqlite3_create_function(db, "dur_h", 0, flags, (void*)Hour, fn_dur_const, 0, 0);
|
|
sqlite3_create_function(db, "dur_m", 0, flags, (void*)Minute, fn_dur_const, 0, 0);
|
|
sqlite3_create_function(db, "dur_s", 0, flags, (void*)Second, fn_dur_const, 0, 0);
|
|
sqlite3_create_function(db, "dur_ms", 0, flags, (void*)Millisecond, fn_dur_const, 0, 0);
|
|
sqlite3_create_function(db, "dur_us", 0, flags, (void*)Microsecond, fn_dur_const, 0, 0);
|
|
sqlite3_create_function(db, "dur_ns", 0, flags, (void*)Nanosecond, fn_dur_const, 0, 0);
|
|
|
|
// postgres compatibility layer
|
|
sqlite3_create_function(db, "age", 2, flags, 0, fn_sub, 0, 0);
|
|
sqlite3_create_function(db, "date_add", 2, flags, 0, fn_add, 0, 0);
|
|
sqlite3_create_function(db, "date_part", 2, flags, 0, date_part, 0, 0);
|
|
sqlite3_create_function(db, "date_trunc", 2, flags, 0, date_trunc, 0, 0);
|
|
sqlite3_create_function(db, "make_date", 3, flags, 0, fn_date, 0, 0);
|
|
sqlite3_create_function(db, "make_timestamp", 6, flags, 0, fn_date, 0, 0);
|
|
sqlite3_create_function(db, "now", 0, flags_nd, 0, fn_now, 0, 0);
|
|
sqlite3_create_function(db, "to_timestamp", 1, flags, 0, fn_unix, 0, 0);
|
|
|
|
return SQLITE_OK;
|
|
}
|