1 /**
2 * Copyright (c) 2015, Timothy Stack
3 *
4 * All rights reserved.
5 *
6 * Redistribution and use in source and binary forms, with or without
7 * modification, are permitted provided that the following conditions are met:
8 *
9 * * Redistributions of source code must retain the above copyright notice, this
10 * list of conditions and the following disclaimer.
11 * * Redistributions in binary form must reproduce the above copyright notice,
12 * this list of conditions and the following disclaimer in the documentation
13 * and/or other materials provided with the distribution.
14 * * Neither the name of Timothy Stack nor the names of its contributors
15 * may be used to endorse or promote products derived from this software
16 * without specific prior written permission.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ''AS IS'' AND ANY
19 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
20 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
21 * DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY
22 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
23 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
24 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
25 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
27 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28 *
29 * @file time-extension-functions.cc
30 */
31
32 #include "config.h"
33
34 #include <string.h>
35 #include <sys/types.h>
36 #include <stdint.h>
37
38 #include <string>
39 #include <unordered_map>
40
41 #include "base/date_time_scanner.hh"
42 #include "base/lrucache.hpp"
43 #include "sql_util.hh"
44 #include "relative_time.hh"
45
46 #include "vtab_module.hh"
47
48 using namespace std;
49
timeslice(sqlite3_value * time_in,nonstd::optional<const char * > slice_in_opt)50 static nonstd::optional<text_auto_buffer> timeslice(sqlite3_value *time_in, nonstd::optional<const char *> slice_in_opt)
51 {
52 thread_local date_time_scanner dts;
53 thread_local struct {
54 std::string c_slice_str;
55 relative_time c_rel_time;
56 } cache;
57 const auto slice_in = string_fragment(slice_in_opt.value_or("15m"));
58
59 if (slice_in.empty()) {
60 throw sqlite_func_error("no time slice value given");
61 }
62
63 if (slice_in != cache.c_slice_str.c_str()) {
64 auto parse_res = relative_time::from_str(slice_in.data());
65 if (parse_res.isErr()) {
66 throw sqlite_func_error("unable to parse time slice value: {} -- {}",
67 slice_in, parse_res.unwrapErr().pe_msg);
68 }
69
70 cache.c_rel_time = parse_res.unwrap();
71 if (cache.c_rel_time.empty()) {
72 throw sqlite_func_error("could not determine a time slice from: {}",
73 slice_in);
74 }
75
76 cache.c_slice_str = slice_in.to_string();
77 }
78
79 struct timeval tv;
80 struct exttm tm;
81
82 switch (sqlite3_value_type(time_in)) {
83 case SQLITE_BLOB:
84 case SQLITE3_TEXT: {
85 const char *time_in_str = reinterpret_cast<const char *>(sqlite3_value_text(
86 time_in));
87
88 if (dts.scan(time_in_str, strlen(time_in_str), nullptr, &tm, tv, false) == nullptr) {
89 dts.unlock();
90 if (dts.scan(time_in_str, strlen(time_in_str), nullptr, &tm, tv, false) == nullptr) {
91 throw sqlite_func_error("unable to parse time value -- {}",
92 time_in_str);
93 }
94 }
95 break;
96 }
97 case SQLITE_INTEGER: {
98 auto msecs = std::chrono::milliseconds(sqlite3_value_int64(time_in));
99
100 tv.tv_sec = std::chrono::duration_cast<std::chrono::seconds>(msecs)
101 .count();
102 tm.et_tm = *gmtime(&tv.tv_sec);
103 tm.et_nsec = std::chrono::duration_cast<std::chrono::nanoseconds>(
104 msecs % 1000).count();
105 break;
106 }
107 case SQLITE_FLOAT: {
108 auto secs = sqlite3_value_double(time_in);
109 double integ;
110 auto fract = modf(secs, &integ);
111
112 tv.tv_sec = integ;
113 tm.et_tm = *gmtime(&tv.tv_sec);
114 tm.et_nsec = floor(fract * 1000000000.0);
115 break;
116 }
117 case SQLITE_NULL: {
118 return nonstd::nullopt;
119 }
120 }
121
122 auto win_start_opt = cache.c_rel_time.window_start(tm);
123
124 if (!win_start_opt) {
125 return nonstd::nullopt;
126 }
127
128 auto win_start = *win_start_opt;
129 auto ts = auto_buffer::alloc(64);
130 auto actual_length = sql_strftime(ts.in(), ts.size(), win_start.to_timeval());
131
132 ts.shrink_to(actual_length);
133 return text_auto_buffer{ std::move(ts) };
134 }
135
136 static
sql_timediff(const char * time1,const char * time2)137 nonstd::optional<double> sql_timediff(const char *time1, const char *time2)
138 {
139 struct timeval tv1, tv2, retval;
140 date_time_scanner dts1, dts2;
141 auto parse_res1 = relative_time::from_str(time1, -1);
142
143 if (parse_res1.isOk()) {
144 tv1 = parse_res1.unwrap().adjust_now().to_timeval();
145 } else if (!dts1.convert_to_timeval(time1, -1, nullptr, tv1)) {
146 return nonstd::nullopt;
147 }
148
149 auto parse_res2 = relative_time::from_str(time2, -1);
150 if (parse_res2.isOk()) {
151 tv2 = parse_res2.unwrap().adjust_now().to_timeval();
152 } else if (!dts2.convert_to_timeval(time2, -1, nullptr, tv2)) {
153 return nonstd::nullopt;
154 }
155
156 timersub(&tv1, &tv2, &retval);
157
158 return (double) retval.tv_sec + (double) retval.tv_usec / 1000000.0;
159 }
160
time_extension_functions(struct FuncDef ** basic_funcs,struct FuncDefAgg ** agg_funcs)161 int time_extension_functions(struct FuncDef **basic_funcs,
162 struct FuncDefAgg **agg_funcs)
163 {
164 static struct FuncDef time_funcs[] = {
165 sqlite_func_adapter<decltype(×lice), timeslice>::builder(
166 help_text("timeslice",
167 "Return the start of the slice of time that the given timestamp falls in. "
168 "If the time falls outside of the slice, NULL is returned.")
169 .sql_function()
170 .with_parameter({"time", "The timestamp to get the time slice for."})
171 .with_parameter({"slice", "The size of the time slices"})
172 .with_tags({"datetime"})
173 .with_example({
174 "To get the timestamp rounded down to the start of the ten minute slice",
175 "SELECT timeslice('2017-01-01T05:05:00', '10m')"
176 })
177 .with_example({
178 "To group log messages into five minute buckets and count them",
179 "SELECT timeslice(log_time_msecs, '5m') AS slice, count(1) FROM lnav_example_log GROUP BY slice"
180 })
181 .with_example({
182 "To group log messages by those before 4:30am and after",
183 "SELECT timeslice(log_time_msecs, 'before 4:30am') AS slice, count(1) FROM lnav_example_log GROUP BY slice"
184 })
185 ),
186
187 sqlite_func_adapter<decltype(&sql_timediff), sql_timediff>::builder(
188 help_text("timediff",
189 "Compute the difference between two timestamps in seconds")
190 .sql_function()
191 .with_parameter({"time1", "The first timestamp"})
192 .with_parameter({"time2", "The timestamp to subtract from the first"})
193 .with_tags({"datetime"})
194 .with_example({
195 "To get the difference between two timestamps",
196 "SELECT timediff('2017-02-03T04:05:06', '2017-02-03T04:05:00')"
197 })
198 .with_example({
199 "To get the difference between relative timestamps",
200 "SELECT timediff('today', 'yesterday')"
201 })
202 ),
203
204 { nullptr }
205 };
206
207 *basic_funcs = time_funcs;
208 *agg_funcs = nullptr;
209
210 return SQLITE_OK;
211 }
212