wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Diagnostics;
3 using System.Text;
4  
5 using time_t = System.Int64;
6 using sqlite3_int64 = System.Int64;
7 using i64 = System.Int64;
8 using u64 = System.UInt64;
9  
10 namespace Community.CsharpSqlite
11 {
12 using sqlite3_value = Sqlite3.Mem;
13  
14 public partial class Sqlite3
15 {
16 /*
17 ** 2003 October 31
18 **
19 ** The author disclaims copyright to this source code. In place of
20 ** a legal notice, here is a blessing:
21 **
22 ** May you do good and not evil.
23 ** May you find forgiveness for yourself and forgive others.
24 ** May you share freely, never taking more than you give.
25 **
26 *************************************************************************
27 ** This file contains the C functions that implement date and time
28 ** functions for SQLite.
29 **
30 ** There is only one exported symbol in this file - the function
31 ** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
32 ** All other code has file scope.
33 **
34 ** SQLite processes all times and dates as Julian Day numbers. The
35 ** dates and times are stored as the number of days since noon
36 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian
37 ** calendar system.
38 **
39 ** 1970-01-01 00:00:00 is JD 2440587.5
40 ** 2000-01-01 00:00:00 is JD 2451544.5
41 **
42 ** This implemention requires years to be expressed as a 4-digit number
43 ** which means that only dates between 0000-01-01 and 9999-12-31 can
44 ** be represented, even though julian day numbers allow a much wider
45 ** range of dates.
46 **
47 ** The Gregorian calendar system is used for all dates and times,
48 ** even those that predate the Gregorian calendar. Historians usually
49 ** use the Julian calendar for dates prior to 1582-10-15 and for some
50 ** dates afterwards, depending on locale. Beware of this difference.
51 **
52 ** The conversion algorithms are implemented based on descriptions
53 ** in the following text:
54 **
55 ** Jean Meeus
56 ** Astronomical Algorithms, 2nd Edition, 1998
57 ** ISBM 0-943396-61-1
58 ** Willmann-Bell, Inc
59 ** Richmond, Virginia (USA)
60 *************************************************************************
61 ** Included in SQLite3 port to C#-SQLite; 2008 Noah B Hart
62 ** C#-SQLite is an independent reimplementation of the SQLite software library
63 **
64 ** SQLITE_SOURCE_ID: 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f2
65 **
66 *************************************************************************
67 */
68 //#include "sqliteInt.h"
69 //#include <stdlib.h>
70 //#include <assert.h>
71 //#include <time.h>
72  
73 #if !SQLITE_OMIT_DATETIME_FUNCS
74  
75  
76 /*
77 ** A structure for holding a single date and time.
78 */
79 //typedef struct DateTime DateTime;
80 public class DateTime
81 {
82 public sqlite3_int64 iJD; /* The julian day number times 86400000 */
83 public int Y, M, D; /* Year, month, and day */
84 public int h, m; /* Hour and minutes */
85 public int tz; /* Timezone offset in minutes */
86 public double s; /* Seconds */
87 public byte validYMD; /* True (1) if Y,M,D are valid */
88 public byte validHMS; /* True (1) if h,m,s are valid */
89 public byte validJD; /* True (1) if iJD is valid */
90 public byte validTZ; /* True (1) if tz is valid */
91  
92 public void CopyTo( DateTime ct )
93 {
94 ct.iJD = iJD;
95 ct.Y = Y;
96 ct.M = M;
97 ct.D = D;
98 ct.h = h;
99 ct.m = m;
100 ct.tz = tz;
101 ct.s = s;
102 ct.validYMD = validYMD;
103 ct.validHMS = validHMS;
104 ct.validJD = validJD;
105 ct.validTZ = validJD;
106 }
107 };
108  
109  
110 // Temporary String for use in this module
111 static StringBuilder zdtTemp = new StringBuilder( 100 );
112 static StringBuilder zdtBuf = new StringBuilder( 100 );
113  
114 /*
115 ** Convert zDate into one or more integers. Additional arguments
116 ** come in groups of 5 as follows:
117 **
118 ** N number of digits in the integer
119 ** min minimum allowed value of the integer
120 ** max maximum allowed value of the integer
121 ** nextC first character after the integer
122 ** pVal where to write the integers value.
123 **
124 ** Conversions continue until one with nextC==0 is encountered.
125 ** The function returns the number of successful conversions.
126 */
127 static int getDigits( string zDate, int N0, int min0, int max0, char nextC0, ref int pVal0, int N1, int min1, int max1, char nextC1, ref int pVal1 )
128 {
129 int c0 = getDigits( zDate + '\0', N0, min0, max0, nextC0, ref pVal0 );
130 return c0 == 0 ? 0 : c0 + getDigits( zDate.Substring( zDate.IndexOf( nextC0 ) + 1 ) + '\0', N1, min1, max1, nextC1, ref pVal1 );
131 }
132 static int getDigits( string zDate, int N0, int min0, int max0, char nextC0, ref int pVal0, int N1, int min1, int max1, char nextC1, ref int pVal1, int N2, int min2, int max2, char nextC2, ref int pVal2 )
133 {
134 int c0 = getDigits( zDate + '\0', N0, min0, max0, nextC0, ref pVal0 );
135 if ( c0 == 0 )
136 return 0;
137 string zDate1 = zDate.Substring( zDate.IndexOf( nextC0 ) + 1 );
138 int c1 = getDigits( zDate1 + '\0', N1, min1, max1, nextC1, ref pVal1 );
139 if ( c1 == 0 )
140 return c0;
141 return c0 + c1 + getDigits( zDate1.Substring( zDate1.IndexOf( nextC1 ) + 1 ) + '\0', N2, min2, max2, nextC2, ref pVal2 );
142 }
143 static int getDigits( string zDate, int N, int min, int max, char nextC, ref int pVal )
144 {
145 //va_list ap;
146 int val;
147 //int N;
148 //int min;
149 //int max;
150 //char nextC;
151 //int pVal;
152 int cnt = 0;
153 //va_start( ap, zDate );
154 int zIndex = 0;
155 //do
156 //{
157 //N = va_arg( ap, ( Int32 ) 0 );
158 //min = va_arg( ap, ( Int32 ) 0 );
159 //max = va_arg( ap, ( Int32 ) 0 );
160 //nextC = (char)va_arg( ap, "char" );
161 //pVal = va_arg( ap, ( Int32 ) 0 );
162 val = 0;
163 while ( N-- != 0 )
164 {
165 if ( !sqlite3Isdigit( zDate[zIndex] ) )
166 {
167 goto end_getDigits;
168 }
169 val = val * 10 + zDate[zIndex] - '0';
170 zIndex++;
171 }
172 if ( val < min || val > max || zIndex < zDate.Length && ( nextC != 0 && nextC != zDate[zIndex] ) )
173 {
174 goto end_getDigits;
175 }
176 pVal = val;
177 zIndex++;
178 cnt++;
179 //} while ( nextC != 0 && zIndex < zDate.Length );
180 end_getDigits:
181 //va_end( ap );
182 return cnt;
183 }
184  
185 /*
186 ** Parse a timezone extension on the end of a date-time.
187 ** The extension is of the form:
188 **
189 ** (+/-)HH:MM
190 **
191 **
192 ** Or the "zulu" notation:
193 **
194 ** Z
195 **
196 ** If the parse is successful, write the number of minutes
197 ** of change in p.tz and return 0. If a parser error occurs,
198 ** return non-zero.
199 **
200 ** A missing specifier is not considered an error.
201 */
202 static int parseTimezone( string zDate, DateTime p )
203 {
204 int sgn = 0;
205 int nHr = 0;
206 int nMn = 0;
207 char c;
208 zDate = zDate.Trim();// while ( sqlite3Isspace( *(u8)zDate ) ) { zDate++; }
209 p.tz = 0;
210 c = zDate.Length == 0 ? '\0' : zDate[0];
211 if ( c == '-' )
212 {
213 sgn = -1;
214 }
215 else if ( c == '+' )
216 {
217 sgn = +1;
218 }
219 else if ( c == 'Z' || c == 'z' )
220 {
221 zDate = zDate.Substring( 1 ).Trim();//zDate++;
222 goto zulu_time;
223 }
224 else
225 {
226 return c != '\0' ? 1 : 0;
227 }
228 //zDate++;
229 if ( getDigits( zDate.Substring( 1 ), 2, 0, 14, ':', ref nHr, 2, 0, 59, '\0', ref nMn ) != 2 )
230 {
231 return 1;
232 }
233 //zDate += 5;
234 p.tz = sgn * ( nMn + nHr * 60 );
235 if ( zDate.Length == 6 )
236 zDate = string.Empty;
237 else if ( zDate.Length > 6 )
238 zDate = zDate.Substring( 6 ).Trim();// while ( sqlite3Isspace( *(u8)zDate ) ) { zDate++; }
239 zulu_time:
240 return zDate.Length > 0 ? 1 : 0;
241 }
242  
243 /*
244 ** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
245 ** The HH, MM, and SS must each be exactly 2 digits. The
246 ** fractional seconds FFFF can be one or more digits.
247 **
248 ** Return 1 if there is a parsing error and 0 on success.
249 */
250 static int parseHhMmSs( string zDate, DateTime p )
251 {
252 int h = 0;
253 int m = 0;
254 int s = 0;
255 double ms = 0.0;
256 if ( getDigits( zDate, 2, 0, 24, ':', ref h, 2, 0, 59, '\0', ref m ) != 2 )
257 {
258 return 1;
259 }
260 int zIndex = 5;// zDate += 5;
261 if ( zIndex < zDate.Length && zDate[zIndex] == ':' )
262 {
263 zIndex++;// zDate++;
264 if ( getDigits( zDate.Substring( zIndex ), 2, 0, 59, '\0', ref s ) != 1 )
265 {
266 return 1;
267 }
268 zIndex += 2;// zDate += 2;
269 if ( zIndex + 1 < zDate.Length && zDate[zIndex] == '.' && sqlite3Isdigit( zDate[zIndex + 1] ) )
270 {
271 double rScale = 1.0;
272 zIndex++;// zDate++;
273 while ( zIndex < zDate.Length && sqlite3Isdigit( zDate[zIndex] )
274 )
275 {
276 ms = ms * 10.0 + zDate[zIndex] - '0';
277 rScale *= 10.0;
278 zIndex++;//zDate++;
279 }
280 ms /= rScale;
281 }
282 }
283 else
284 {
285 s = 0;
286 }
287 p.validJD = 0;
288 p.validHMS = 1;
289 p.h = h;
290 p.m = m;
291 p.s = s + ms;
292 if ( zIndex < zDate.Length && parseTimezone( zDate.Substring( zIndex ), p ) != 0 )
293 return 1;
294 p.validTZ = (byte)( ( p.tz != 0 ) ? 1 : 0 );
295 return 0;
296 }
297  
298 /*
299 ** Convert from YYYY-MM-DD HH:MM:SS to julian day. We always assume
300 ** that the YYYY-MM-DD is according to the Gregorian calendar.
301 **
302 ** Reference: Meeus page 61
303 */
304 static void computeJD( DateTime p )
305 {
306 int Y, M, D, A, B, X1, X2;
307  
308 if ( p.validJD != 0 )
309 return;
310 if ( p.validYMD != 0 )
311 {
312 Y = p.Y;
313 M = p.M;
314 D = p.D;
315 }
316 else
317 {
318 Y = 2000; /* If no YMD specified, assume 2000-Jan-01 */
319 M = 1;
320 D = 1;
321 }
322 if ( M <= 2 )
323 {
324 Y--;
325 M += 12;
326 }
327 A = Y / 100;
328 B = 2 - A + ( A / 4 );
329 X1 = (int)( 36525 * ( Y + 4716 ) / 100 );
330 X2 = (int)( 306001 * ( M + 1 ) / 10000 );
331 p.iJD = (long)( ( X1 + X2 + D + B - 1524.5 ) * 86400000 );
332 p.validJD = 1;
333 if ( p.validHMS != 0 )
334 {
335 p.iJD += (long)( p.h * 3600000 + p.m * 60000 + p.s * 1000 );
336 if ( p.validTZ != 0 )
337 {
338 p.iJD -= p.tz * 60000;
339 p.validYMD = 0;
340 p.validHMS = 0;
341 p.validTZ = 0;
342 }
343 }
344 }
345  
346 /*
347 ** Parse dates of the form
348 **
349 ** YYYY-MM-DD HH:MM:SS.FFF
350 ** YYYY-MM-DD HH:MM:SS
351 ** YYYY-MM-DD HH:MM
352 ** YYYY-MM-DD
353 **
354 ** Write the result into the DateTime structure and return 0
355 ** on success and 1 if the input string is not a well-formed
356 ** date.
357 */
358 static int parseYyyyMmDd( string zDate, DateTime p )
359 {
360 int Y = 0;
361 int M = 0;
362 int D = 0;
363 bool neg;
364  
365 int zIndex = 0;
366 if ( zDate[zIndex] == '-' )
367 {
368 zIndex++;// zDate++;
369 neg = true;
370 }
371 else
372 {
373 neg = false;
374 }
375 if ( getDigits( zDate.Substring( zIndex ), 4, 0, 9999, '-', ref Y, 2, 1, 12, '-', ref M, 2, 1, 31, '\0', ref D ) != 3 )
376 {
377 return 1;
378 }
379 zIndex += 10;// zDate += 10;
380 while ( zIndex < zDate.Length && ( sqlite3Isspace( zDate[zIndex] ) || 'T' == zDate[zIndex] ) )
381 {
382 zIndex++;
383 }//zDate++; }
384 if ( zIndex < zDate.Length && parseHhMmSs( zDate.Substring( zIndex ), p ) == 0 )
385 {
386 /* We got the time */
387 }
388 else if ( zIndex >= zDate.Length )// zDate[zIndex] == '\0')
389 {
390 p.validHMS = 0;
391 }
392 else
393 {
394 return 1;
395 }
396 p.validJD = 0;
397 p.validYMD = 1;
398 p.Y = neg ? -Y : Y;
399 p.M = M;
400 p.D = D;
401 if ( p.validTZ != 0 )
402 {
403 computeJD( p );
404 }
405 return 0;
406 }
407  
408 /*
409 ** Set the time to the current time reported by the VFS
410 */
411 static void setDateTimeToCurrent( sqlite3_context context, DateTime p )
412 {
413 sqlite3 db = sqlite3_context_db_handle( context );
414 sqlite3OsCurrentTimeInt64( db.pVfs, ref p.iJD );
415 p.validJD = 1;
416 }
417  
418 /*
419 ** Attempt to parse the given string into a Julian Day Number. Return
420 ** the number of errors.
421 **
422 ** The following are acceptable forms for the input string:
423 **
424 ** YYYY-MM-DD HH:MM:SS.FFF +/-HH:MM
425 ** DDDD.DD
426 ** now
427 **
428 ** In the first form, the +/-HH:MM is always optional. The fractional
429 ** seconds extension (the ".FFF") is optional. The seconds portion
430 ** (":SS.FFF") is option. The year and date can be omitted as long
431 ** as there is a time string. The time string can be omitted as long
432 ** as there is a year and date.
433 */
434 static int parseDateOrTime(
435 sqlite3_context context,
436 string zDate,
437 ref DateTime p
438 )
439 {
440 double r = 0.0;
441 if ( parseYyyyMmDd( zDate, p ) == 0 )
442 {
443 return 0;
444 }
445 else if ( parseHhMmSs( zDate, p ) == 0 )
446 {
447 return 0;
448 }
449 else if ( zDate.Equals( "now", StringComparison.OrdinalIgnoreCase ) )
450 {
451 setDateTimeToCurrent( context, p );
452 return 0;
453 }
454 else if ( sqlite3AtoF( zDate, ref r, sqlite3Strlen30( zDate ), SQLITE_UTF8 ) )
455 {
456 p.iJD = (sqlite3_int64)( r * 86400000.0 + 0.5 );
457 p.validJD = 1;
458 return 0;
459 }
460 return 1;
461 }
462  
463 /*
464 ** Compute the Year, Month, and Day from the julian day number.
465 */
466 static void computeYMD( DateTime p )
467 {
468 int Z, A, B, C, D, E, X1;
469 if ( p.validYMD != 0 )
470 return;
471 if ( 0 == p.validJD )
472 {
473 p.Y = 2000;
474 p.M = 1;
475 p.D = 1;
476 }
477 else
478 {
479 Z = (int)( ( p.iJD + 43200000 ) / 86400000 );
480 A = (int)( ( Z - 1867216.25 ) / 36524.25 );
481 A = Z + 1 + A - ( A / 4 );
482 B = A + 1524;
483 C = (int)( ( B - 122.1 ) / 365.25 );
484 D = (int)( ( 36525 * C ) / 100 );
485 E = (int)( ( B - D ) / 30.6001 );
486 X1 = (int)( 30.6001 * E );
487 p.D = B - D - X1;
488 p.M = E < 14 ? E - 1 : E - 13;
489 p.Y = p.M > 2 ? C - 4716 : C - 4715;
490 }
491 p.validYMD = 1;
492 }
493  
494 /*
495 ** Compute the Hour, Minute, and Seconds from the julian day number.
496 */
497 static void computeHMS( DateTime p )
498 {
499 int s;
500 if ( p.validHMS != 0 )
501 return;
502 computeJD( p );
503 s = (int)( ( p.iJD + 43200000 ) % 86400000 );
504 p.s = s / 1000.0;
505 s = (int)p.s;
506 p.s -= s;
507 p.h = s / 3600;
508 s -= p.h * 3600;
509 p.m = s / 60;
510 p.s += s - p.m * 60;
511 p.validHMS = 1;
512 }
513  
514 /*
515 ** Compute both YMD and HMS
516 */
517 static void computeYMD_HMS( DateTime p )
518 {
519 computeYMD( p );
520 computeHMS( p );
521 }
522  
523 /*
524 ** Clear the YMD and HMS and the TZ
525 */
526 static void clearYMD_HMS_TZ( DateTime p )
527 {
528 p.validYMD = 0;
529 p.validHMS = 0;
530 p.validTZ = 0;
531 }
532  
533 /*
534 ** On recent Windows platforms, the localtime_s() function is available
535 ** as part of the "Secure CRT". It is essentially equivalent to
536 ** localtime_r() available under most POSIX platforms, except that the
537 ** order of the parameters is reversed.
538 **
539 ** See http://msdn.microsoft.com/en-us/library/a442x3ye(VS.80).aspx.
540 **
541 ** If the user has not indicated to use localtime_r() or localtime_s()
542 ** already, check for an MSVC build environment that provides
543 ** localtime_s().
544 */
545 //#if !defined(HAVE_LOCALTIME_R) && !defined(HAVE_LOCALTIME_S) && \
546 // defined(_MSC_VER) && defined(_CRT_INSECURE_DEPRECATE)
547 //#define HAVE_LOCALTIME_S 1
548 //#endif
549  
550 #if !SQLITE_OMIT_LOCALTIME
551 /*
552 ** The following routine implements the rough equivalent of localtime_r()
553 ** using whatever operating-system specific localtime facility that
554 ** is available. This routine returns 0 on success and
555 ** non-zero on any kind of error.
556 **
557 ** If the sqlite3GlobalConfig.bLocaltimeFault variable is true then this
558 ** routine will always fail.
559 */
560 static int osLocaltime( time_t t, tm pTm )
561 {
562 int rc;
563 #if (!(HAVE_LOCALTIME_R) || !HAVE_LOCALTIME_R) && (!(HAVE_LOCALTIME_S) || !HAVE_LOCALTIME_S)
564 tm pX;
565 sqlite3_mutex mutex = sqlite3MutexAlloc( SQLITE_MUTEX_STATIC_MASTER );
566 sqlite3_mutex_enter( mutex );
567 pX = localtime( t );
568 #if !SQLITE_OMIT_BUILTIN_TEST
569 if ( sqlite3GlobalConfig.bLocaltimeFault )
570 pX = null;
571 #endif
572 if ( pX != null )
573 pTm = pX;
574 sqlite3_mutex_leave( mutex );
575 rc = pX == null ? 1 : 0;
576 #else
577 #if !SQLITE_OMIT_BUILTIN_TEST
578 if( sqlite3GlobalConfig.bLocaltimeFault ) return 1;
579 #endif
580 #if (HAVE_LOCALTIME_R) && HAVE_LOCALTIME_R
581 rc = localtime_r(t, pTm)==0;
582 #else
583 rc = localtime_s(pTm, t);
584 #endif //* HAVE_LOCALTIME_R */
585 #endif //* HAVE_LOCALTIME_R || HAVE_LOCALTIME_S */
586 return rc;
587 }
588 #endif //* SQLITE_OMIT_LOCALTIME */
589  
590  
591 #if !SQLITE_OMIT_LOCALTIME
592 /*
593 ** Compute the difference (in milliseconds) between localtime and UTC
594 ** (a.k.a. GMT) for the time value p where p is in UTC. If no error occurs,
595 ** return this value and set *pRc to SQLITE_OK.
596 **
597 ** Or, if an error does occur, set *pRc to SQLITE_ERROR. The returned value
598 ** is undefined in this case.
599 */
600 static sqlite3_int64 localtimeOffset(
601 DateTime p, /* Date at which to calculate offset */
602 sqlite3_context pCtx, /* Write error here if one occurs */
603 out int pRc /* OUT: Error code. SQLITE_OK or ERROR */
604 )
605 {
606 DateTime x;
607 DateTime y = new DateTime();
608 time_t t;
609 tm sLocal = new tm();
610  
611 /* Initialize the contents of sLocal to avoid a compiler warning. */
612 //memset(&sLocal, 0, sizeof(sLocal));
613  
614 x = p;
615 computeYMD_HMS( x );
616 if ( x.Y < 1971 || x.Y >= 2038 )
617 {
618 x.Y = 2000;
619 x.M = 1;
620 x.D = 1;
621 x.h = 0;
622 x.m = 0;
623 x.s = 0.0;
624 }
625 else
626 {
627 int s = (int)( x.s + 0.5 );
628 x.s = s;
629 }
630 x.tz = 0;
631 x.validJD = 0;
632 computeJD( x );
633 t = (long)( x.iJD / 1000 - 210866760000L );// (time_t)(x.iJD/1000 - 21086676*(i64)10000);
634 if ( osLocaltime( t, sLocal ) != 0 )
635 {
636 sqlite3_result_error( pCtx, "local time unavailable", -1 );
637 pRc = SQLITE_ERROR;
638 return 0;
639 }
640 y.Y = sLocal.tm_year;// +1900;
641 y.M = sLocal.tm_mon;// +1;
642 y.D = sLocal.tm_mday;
643 y.h = sLocal.tm_hour;
644 y.m = sLocal.tm_min;
645 y.s = sLocal.tm_sec;
646 y.validYMD = 1;
647 y.validHMS = 1;
648 y.validJD = 0;
649 y.validTZ = 0;
650 computeJD( y );
651 pRc = SQLITE_OK;
652 return (int)( y.iJD - x.iJD );
653 }
654 #endif //* SQLITE_OMIT_LOCALTIME */
655  
656 /*
657 ** Process a modifier to a date-time stamp. The modifiers are
658 ** as follows:
659 **
660 ** NNN days
661 ** NNN hours
662 ** NNN minutes
663 ** NNN.NNNN seconds
664 ** NNN months
665 ** NNN years
666 ** start of month
667 ** start of year
668 ** start of week
669 ** start of day
670 ** weekday N
671 ** unixepoch
672 ** localtime
673 ** utc
674 **
675 ** Return 0 on success and 1 if there is any kind of error. If the error
676 ** is in a system call (i.e. localtime()), then an error message is written
677 ** to context pCtx. If the error is an unrecognized modifier, no error is
678 ** written to pCtx.
679 */
680 static int parseModifier( sqlite3_context pCtx, string zMod, DateTime p )
681 {
682 int rc = 1;
683 int n;
684 double r = 0;
685 StringBuilder z = new StringBuilder( zMod.ToLower() );
686 zdtBuf.Length = 0;
687 //z = zdtBuf;
688 //for(n=0; n<ArraySize(zdtBuf)-1 && zMod[n]; n++){
689 // z.Append( zMod.Substring( n ).ToLower() );
690 //}
691 //z[n] = 0;
692 switch ( z[0] )
693 #if !SQLITE_OMIT_LOCALTIME
694 {
695 case 'l':
696 {
697 /* localtime
698 **
699 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
700 ** show local time.
701 */
702 if ( z.ToString() == "localtime" )
703 {
704 computeJD( p );
705 p.iJD += localtimeOffset( p, pCtx, out rc );
706 clearYMD_HMS_TZ( p );
707 }
708 break;
709 }
710 #endif
711 case 'u':
712 {
713 /*
714 ** unixepoch
715 **
716 ** Treat the current value of p.iJD as the number of
717 ** seconds since 1970. Convert to a real julian day number.
718 */
719 if ( z.ToString() == "unixepoch" && p.validJD != 0 )
720 {
721 p.iJD = (long)( ( p.iJD + 43200 ) / 86400 + 210866760000000L );//p->iJD = p->iJD/86400 + 21086676*(i64)10000000;
722 clearYMD_HMS_TZ( p );
723 rc = 0;
724 }
725 #if !SQLITE_OMIT_LOCALTIME
726 else if ( z.ToString() == "utc" )
727 {
728 long c1;
729 computeJD( p );
730 c1 = localtimeOffset( p, pCtx, out rc );
731 if ( rc == SQLITE_OK )
732 {
733 p.iJD -= c1;
734 clearYMD_HMS_TZ( p );
735 p.iJD += c1 - localtimeOffset( p, pCtx, out rc );
736 }
737 }
738 #endif
739 break;
740 }
741 case 'w':
742 {
743 /*
744 ** weekday N
745 **
746 ** Move the date to the same time on the next occurrence of
747 ** weekday N where 0==Sunday, 1==Monday, and so forth. If the
748 ** date is already on the appropriate weekday, this is a no-op.
749 */
750 if ( z.ToString().StartsWith( "weekday " )
751 && sqlite3AtoF( z.ToString().Substring( 8 ), ref r, sqlite3Strlen30( z.ToString().Substring( 8 ) ), SQLITE_UTF8 )
752 && ( n = (int)r ) == r && n >= 0 && r < 7 )
753 {
754 sqlite3_int64 Z;
755 computeYMD_HMS( p );
756 p.validTZ = 0;
757 p.validJD = 0;
758 computeJD( p );
759 Z = ( ( p.iJD + 129600000 ) / 86400000 ) % 7;
760 if ( Z > n )
761 Z -= 7;
762 p.iJD += ( n - Z ) * 86400000;
763 clearYMD_HMS_TZ( p );
764 rc = 0;
765 }
766 break;
767 }
768 case 's':
769 {
770 /*
771 ** start of TTTTT
772 **
773 ** Move the date backwards to the beginning of the current day,
774 ** or month or year.
775 */
776 if ( z.Length <= 9 )
777 z.Length = 0;
778 else
779 z.Remove( 0, 9 );//z += 9;
780 computeYMD( p );
781 p.validHMS = 1;
782 p.h = p.m = 0;
783 p.s = 0.0;
784 p.validTZ = 0;
785 p.validJD = 0;
786 if ( z.ToString() == "month" )
787 {
788 p.D = 1;
789 rc = 0;
790 }
791 else if ( z.ToString() == "year" )
792 {
793 computeYMD( p );
794 p.M = 1;
795 p.D = 1;
796 rc = 0;
797 }
798 else if ( z.ToString() == "day" )
799 {
800 rc = 0;
801 }
802 break;
803 }
804 case '+':
805 case '-':
806 case '0':
807 case '1':
808 case '2':
809 case '3':
810 case '4':
811 case '5':
812 case '6':
813 case '7':
814 case '8':
815 case '9':
816 {
817 double rRounder;
818 for ( n = 1; n < z.Length && z[n] != ':' && !sqlite3Isspace( z[n] ); n++ )
819 {
820 }
821 if ( !sqlite3AtoF( z.ToString(), ref r, n, SQLITE_UTF8 ) )
822 {
823 rc = 1;
824 break;
825 }
826 Debug.Assert( n >= 1 );
827 if ( z[n] == ':' )
828 {
829 /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
830 ** specified number of hours, minutes, seconds, and fractional seconds
831 ** to the time. The ".FFF" may be omitted. The ":SS.FFF" may be
832 ** omitted.
833 */
834 string z2 = z.ToString();
835 DateTime tx;
836 sqlite3_int64 day;
837 int z2Index = 0;
838 if ( !sqlite3Isdigit( z2[z2Index] ) )
839 z2Index++;// z2++;
840 tx = new DateTime();// memset( &tx, 0, sizeof(tx));
841 if ( parseHhMmSs( z2.Substring( z2Index ), tx ) != 0 )
842 break;
843 computeJD( tx );
844 tx.iJD -= 43200000;
845 day = tx.iJD / 86400000;
846 tx.iJD -= day * 86400000;
847 if ( z[0] == '-' )
848 tx.iJD = -tx.iJD;
849 computeJD( p );
850 clearYMD_HMS_TZ( p );
851 p.iJD += tx.iJD;
852 rc = 0;
853 break;
854 }
855 //z += n;
856 while ( sqlite3Isspace( z[n] ) )
857 n++;// z++;
858 z = z.Remove( 0, n );
859 n = sqlite3Strlen30( z );
860 if ( n > 10 || n < 3 )
861 break;
862 if ( z[n - 1] == 's' )
863 {
864 z.Length = --n;
865 }// z[n - 1] = '\0'; n--; }
866 computeJD( p );
867 rc = 0;
868 rRounder = r < 0 ? -0.5 : +0.5;
869 if ( n == 3 && z.ToString() == "day" )
870 {
871 p.iJD += (long)( r * 86400000.0 + rRounder );
872 }
873 else if ( n == 4 && z.ToString() == "hour" )
874 {
875 p.iJD += (long)( r * ( 86400000.0 / 24.0 ) + rRounder );
876 }
877 else if ( n == 6 && z.ToString() == "minute" )
878 {
879 p.iJD += (long)( r * ( 86400000.0 / ( 24.0 * 60.0 ) ) + rRounder );
880 }
881 else if ( n == 6 && z.ToString() == "second" )
882 {
883 p.iJD += (long)( r * ( 86400000.0 / ( 24.0 * 60.0 * 60.0 ) ) + rRounder );
884 }
885 else if ( n == 5 && z.ToString() == "month" )
886 {
887 int x, y;
888 computeYMD_HMS( p );
889 p.M += (int)r;
890 x = p.M > 0 ? ( p.M - 1 ) / 12 : ( p.M - 12 ) / 12;
891 p.Y += x;
892 p.M -= x * 12;
893 p.validJD = 0;
894 computeJD( p );
895 y = (int)r;
896 if ( y != r )
897 {
898 p.iJD += (long)( ( r - y ) * 30.0 * 86400000.0 + rRounder );
899 }
900 }
901 else if ( n == 4 && z.ToString() == "year" )
902 {
903 int y = (int)r;
904 computeYMD_HMS( p );
905 p.Y += y;
906 p.validJD = 0;
907 computeJD( p );
908 if ( y != r )
909 {
910 p.iJD += (sqlite3_int64)( ( r - y ) * 365.0 * 86400000.0 + rRounder );
911 }
912 }
913 else
914 {
915 rc = 1;
916 }
917 clearYMD_HMS_TZ( p );
918 break;
919 }
920 default:
921 {
922 break;
923 }
924 }
925 return rc;
926 }
927  
928 /*
929 ** Process time function arguments. argv[0] is a date-time stamp.
930 ** argv[1] and following are modifiers. Parse them all and write
931 ** the resulting time into the DateTime structure p. Return 0
932 ** on success and 1 if there are any errors.
933 **
934 ** If there are zero parameters (if even argv[0] is undefined)
935 ** then assume a default value of "now" for argv[0].
936 */
937 static int isDate(
938 sqlite3_context context,
939 int argc,
940 sqlite3_value[] argv,
941 ref DateTime p
942 )
943 {
944 int i;
945 string z;
946 int eType;
947 p = new DateTime();//memset(p, 0, sizeof(*p));
948 if ( argc == 0 )
949 {
950 setDateTimeToCurrent( context, p );
951 }
952 else if ( ( eType = sqlite3_value_type( argv[0] ) ) == SQLITE_FLOAT
953 || eType == SQLITE_INTEGER )
954 {
955 p.iJD = (long)( sqlite3_value_double( argv[0] ) * 86400000.0 + 0.5 );
956 p.validJD = 1;
957 }
958 else
959 {
960 z = sqlite3_value_text( argv[0] );
961 if ( string.IsNullOrEmpty( z ) || parseDateOrTime( context, z, ref p ) != 0 )
962 {
963 return 1;
964 }
965 }
966 for ( i = 1; i < argc; i++ )
967 {
968 z = sqlite3_value_text( argv[i] );
969 if ( string.IsNullOrEmpty( z ) || parseModifier( context, z, p ) != 0 )
970 return 1;
971 }
972 return 0;
973 }
974  
975  
976 /*
977 ** The following routines implement the various date and time functions
978 ** of SQLite.
979 */
980  
981 /*
982 ** julianday( TIMESTRING, MOD, MOD, ...)
983 **
984 ** Return the julian day number of the date specified in the arguments
985 */
986 static void juliandayFunc(
987 sqlite3_context context,
988 int argc,
989 sqlite3_value[] argv
990 )
991 {
992 DateTime x = null;
993 if ( isDate( context, argc, argv, ref x ) == 0 )
994 {
995 computeJD( x );
996 sqlite3_result_double( context, x.iJD / 86400000.0 );
997 }
998 }
999  
1000 /*
1001 ** datetime( TIMESTRING, MOD, MOD, ...)
1002 **
1003 ** Return YYYY-MM-DD HH:MM:SS
1004 */
1005 static void datetimeFunc(
1006 sqlite3_context context,
1007 int argc,
1008 sqlite3_value[] argv
1009 )
1010 {
1011 DateTime x = null;
1012 if ( isDate( context, argc, argv, ref x ) == 0 )
1013 {
1014 zdtBuf.Length = 0;
1015 computeYMD_HMS( x );
1016 sqlite3_snprintf( 100, zdtBuf, "%04d-%02d-%02d %02d:%02d:%02d",
1017 x.Y, x.M, x.D, x.h, x.m, (int)( x.s ) );
1018 sqlite3_result_text( context, zdtBuf, -1, SQLITE_TRANSIENT );
1019 }
1020 }
1021  
1022 /*
1023 ** time( TIMESTRING, MOD, MOD, ...)
1024 **
1025 ** Return HH:MM:SS
1026 */
1027 static void timeFunc(
1028 sqlite3_context context,
1029 int argc,
1030 sqlite3_value[] argv
1031 )
1032 {
1033 DateTime x = new DateTime();
1034 if ( isDate( context, argc, argv, ref x ) == 0 )
1035 {
1036 zdtBuf.Length = 0;
1037 computeHMS( x );
1038 sqlite3_snprintf( 100, zdtBuf, "%02d:%02d:%02d", x.h, x.m, (int)x.s );
1039 sqlite3_result_text( context, zdtBuf, -1, SQLITE_TRANSIENT );
1040 }
1041 }
1042  
1043 /*
1044 ** date( TIMESTRING, MOD, MOD, ...)
1045 **
1046 ** Return YYYY-MM-DD
1047 */
1048 static void dateFunc(
1049 sqlite3_context context,
1050 int argc,
1051 sqlite3_value[] argv
1052 )
1053 {
1054 DateTime x = null;
1055 if ( isDate( context, argc, argv, ref x ) == 0 )
1056 {
1057 StringBuilder zdtBuf = new StringBuilder( 100 );
1058 computeYMD( x );
1059 sqlite3_snprintf( 100, zdtBuf, "%04d-%02d-%02d", x.Y, x.M, x.D );
1060 sqlite3_result_text( context, zdtBuf, -1, SQLITE_TRANSIENT );
1061 }
1062 }
1063  
1064 /*
1065 ** strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
1066 **
1067 ** Return a string described by FORMAT. Conversions as follows:
1068 **
1069 ** %d day of month
1070 ** %f ** fractional seconds SS.SSS
1071 ** %H hour 00-24
1072 ** %j day of year 000-366
1073 ** %J ** Julian day number
1074 ** %m month 01-12
1075 ** %M minute 00-59
1076 ** %s seconds since 1970-01-01
1077 ** %S seconds 00-59
1078 ** %w day of week 0-6 sunday==0
1079 ** %W week of year 00-53
1080 ** %Y year 0000-9999
1081 ** %% %
1082 */
1083 static void strftimeFunc(
1084 sqlite3_context context,
1085 int argc,
1086 sqlite3_value[] argv
1087 )
1088 {
1089 {
1090 DateTime x = new DateTime();
1091 u64 n;
1092 int i, j;
1093 StringBuilder z;
1094 sqlite3 db;
1095 string zFmt = sqlite3_value_text( argv[0] );
1096 StringBuilder zdtBuf = new StringBuilder( 100 );
1097 sqlite3_value[] argv1 = new sqlite3_value[argc - 1];
1098 for ( i = 0; i < argc - 1; i++ )
1099 {
1100 argv[i + 1].CopyTo( ref argv1[i] );
1101 }
1102 if ( string.IsNullOrEmpty( zFmt ) || isDate( context, argc - 1, argv1, ref x ) != 0 )
1103 return;
1104 db = sqlite3_context_db_handle( context );
1105 for ( i = 0, n = 1; i < zFmt.Length; i++, n++ )
1106 {
1107 if ( zFmt[i] == '%' )
1108 {
1109 switch ( (char)zFmt[i + 1] )
1110 {
1111 case 'd':
1112 case 'H':
1113 case 'm':
1114 case 'M':
1115 case 'S':
1116 case 'W':
1117 n++;
1118 break;
1119 /* fall thru */
1120 case 'w':
1121 case '%':
1122 break;
1123 case 'f':
1124 n += 8;
1125 break;
1126 case 'j':
1127 n += 3;
1128 break;
1129 case 'Y':
1130 n += 8;
1131 break;
1132 case 's':
1133 case 'J':
1134 n += 50;
1135 break;
1136 default:
1137 return; /* ERROR. return a NULL */
1138 }
1139 i++;
1140 }
1141 }
1142 testcase( n == (u64)( zdtBuf.Length - 1 ) );
1143 testcase( n == (u64)zdtBuf.Length );
1144 testcase( n == (u64)db.aLimit[SQLITE_LIMIT_LENGTH] + 1 );
1145 testcase( n == (u64)db.aLimit[SQLITE_LIMIT_LENGTH] );
1146 if ( n < (u64)zdtBuf.Capacity )
1147 {
1148 z = zdtBuf;
1149 }
1150 else if ( n > (u64)db.aLimit[SQLITE_LIMIT_LENGTH] )
1151 {
1152 sqlite3_result_error_toobig( context );
1153 return;
1154 }
1155 else
1156 {
1157 z = new StringBuilder( (int)n );// sqlite3DbMallocRaw( db, n );
1158 //if ( z == 0 )
1159 //{
1160 // sqlite3_result_error_nomem( context );
1161 // return;
1162 //}
1163 }
1164 computeJD( x );
1165 computeYMD_HMS( x );
1166 for ( i = j = 0; i < zFmt.Length; i++ )
1167 {
1168 if ( zFmt[i] != '%' )
1169 {
1170 z.Append( (char)zFmt[i] );
1171 }
1172 else
1173 {
1174 i++;
1175 zdtTemp.Length = 0;
1176 switch ( (char)zFmt[i] )
1177 {
1178 case 'd':
1179 sqlite3_snprintf( 3, zdtTemp, "%02d", x.D );
1180 z.Append( zdtTemp );
1181 j += 2;
1182 break;
1183 case 'f':
1184 {
1185 double s = x.s;
1186 if ( s > 59.999 )
1187 s = 59.999;
1188 sqlite3_snprintf( 7, zdtTemp, "%06.3f", s );
1189 z.Append( zdtTemp );
1190 j = sqlite3Strlen30( z );
1191 break;
1192 }
1193 case 'H':
1194 sqlite3_snprintf( 3, zdtTemp, "%02d", x.h );
1195 z.Append( zdtTemp );
1196 j += 2;
1197 break;
1198 case 'W': /* Fall thru */
1199 case 'j':
1200 {
1201 int nDay; /* Number of days since 1st day of year */
1202 DateTime y = new DateTime();
1203 x.CopyTo( y );
1204 y.validJD = 0;
1205 y.M = 1;
1206 y.D = 1;
1207 computeJD( y );
1208 nDay = (int)( ( x.iJD - y.iJD + 43200000 ) / 86400000 );
1209 if ( zFmt[i] == 'W' )
1210 {
1211 int wd; /* 0=Monday, 1=Tuesday, ... 6=Sunday */
1212 wd = (int)( ( ( x.iJD + 43200000 ) / 86400000 ) % 7 );
1213 sqlite3_snprintf( 3, zdtTemp, "%02d", ( nDay + 7 - wd ) / 7 );
1214 z.Append( zdtTemp );
1215 j += 2;
1216 }
1217 else
1218 {
1219 sqlite3_snprintf( 4, zdtTemp, "%03d", nDay + 1 );
1220 z.Append( zdtTemp );
1221 j += 3;
1222 }
1223 break;
1224 }
1225 case 'J':
1226 {
1227 sqlite3_snprintf( 20, zdtTemp, "%.16g", x.iJD / 86400000.0 );
1228 z.Append( zdtTemp );
1229 j = sqlite3Strlen30( z );
1230 break;
1231 }
1232 case 'm':
1233 sqlite3_snprintf( 3, zdtTemp, "%02d", x.M );
1234 z.Append( zdtTemp );
1235 j += 2;
1236 break;
1237 case 'M':
1238 sqlite3_snprintf( 3, zdtTemp, "%02d", x.m );
1239 z.Append( zdtTemp );
1240 j += 2;
1241 break;
1242 case 's':
1243 {
1244 sqlite3_snprintf( 30, zdtTemp, "%lld",
1245 (i64)( x.iJD / 1000 - 21086676 * (i64)10000 ) );
1246 z.Append( zdtTemp );
1247 j = sqlite3Strlen30( z );
1248 break;
1249 }
1250 case 'S':
1251 sqlite3_snprintf( 3, zdtTemp, "%02d", (int)x.s );
1252 z.Append( zdtTemp );
1253 j += 2;
1254 break;
1255 case 'w':
1256 {
1257 z.Append( ( ( ( x.iJD + 129600000 ) / 86400000 ) % 7 ) );
1258 break;
1259 }
1260 case 'Y':
1261 {
1262 sqlite3_snprintf( 5, zdtTemp, "%04d", x.Y );
1263 z.Append( zdtTemp );
1264 j = sqlite3Strlen30( z );
1265 break;
1266 }
1267 default:
1268 z.Append( '%' );
1269 break;
1270 }
1271 }
1272 }
1273 //z[j] = 0;
1274 sqlite3_result_text( context, z, -1,
1275 z == zdtBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC );
1276 }
1277 }
1278  
1279 /*
1280 ** current_time()
1281 **
1282 ** This function returns the same value as time('now').
1283 */
1284 static void ctimeFunc(
1285 sqlite3_context context,
1286 int NotUsed,
1287 sqlite3_value[] NotUsed2
1288 )
1289 {
1290 UNUSED_PARAMETER2( NotUsed, NotUsed2 );
1291 timeFunc( context, 0, null );
1292 }
1293  
1294 /*
1295 ** current_date()
1296 **
1297 ** This function returns the same value as date('now').
1298 */
1299 static void cdateFunc(
1300 sqlite3_context context,
1301 int NotUsed,
1302 sqlite3_value[] NotUsed2
1303 )
1304 {
1305 UNUSED_PARAMETER2( NotUsed, NotUsed2 );
1306 dateFunc( context, 0, null );
1307 }
1308  
1309 /*
1310 ** current_timestamp()
1311 **
1312 ** This function returns the same value as datetime('now').
1313 */
1314 static void ctimestampFunc(
1315 sqlite3_context context,
1316 int NotUsed,
1317 sqlite3_value[] NotUsed2
1318 )
1319 {
1320 UNUSED_PARAMETER2( NotUsed, NotUsed2 );
1321 datetimeFunc( context, 0, null );
1322 }
1323 #endif // * !SQLITE_OMIT_DATETIME_FUNCS) */
1324  
1325 #if SQLITE_OMIT_DATETIME_FUNCS
1326 /*
1327 ** If the library is compiled to omit the full-scale date and time
1328 ** handling (to get a smaller binary), the following minimal version
1329 ** of the functions current_time(), current_date() and current_timestamp()
1330 ** are included instead. This is to support column declarations that
1331 ** include "DEFAULT CURRENT_TIME" etc.
1332 **
1333 ** This function uses the C-library functions time(), gmtime()
1334 ** and strftime(). The format string to pass to strftime() is supplied
1335 ** as the user-data for the function.
1336 */
1337 //static void currentTimeFunc(
1338 // sqlite3_context *context,
1339 // int argc,
1340 // sqlite3_value[] argv
1341 //){
1342 time_t t;
1343 string zFormat = (char )sqlite3_user_data(context);
1344 sqlite3 db;
1345 sqlite3_int64 rT;
1346 char zdtBuf[20];
1347 UNUSED_PARAMETER(argc);
1348 UNUSED_PARAMETER(argv);
1349 db = sqlite3_context_db_handle(context);
1350 sqlite3OsCurrentTimeInt64(db->pVfs, &iT);
1351 t = iT/1000 - 10000*(sqlite3_int64)21086676;
1352 #if HAVE_GMTIME_R
1353 // {
1354 // struct tm sNow;
1355 // gmtime_r(&t, sNow);
1356 // strftime(zdtBuf, 20, zFormat, sNow);
1357 // }
1358 #else
1359 // {
1360 // struct tm pTm;
1361 // sqlite3_mutex_enter(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1362 // pTm = gmtime(&t);
1363 // strftime(zdtBuf, 20, zFormat, pTm);
1364 // sqlite3_mutex_leave(sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER));
1365 // }
1366 #endif
1367  
1368 // sqlite3_result_text(context, zdtBuf, -1, SQLITE_TRANSIENT);
1369 //}
1370 #endif
1371  
1372  
1373 /*
1374 ** This function registered all of the above C functions as SQL
1375 ** functions. This should be the only routine in this file with
1376 ** external linkage.
1377 */
1378 static void sqlite3RegisterDateTimeFunctions()
1379 {
1380 FuncDef[] aDateTimeFuncs = new FuncDef[] {
1381 #if !SQLITE_OMIT_DATETIME_FUNCS
1382 FUNCTION("julianday", -1, 0, 0, (dxFunc)juliandayFunc ),
1383 FUNCTION("date", -1, 0, 0, (dxFunc)dateFunc ),
1384 FUNCTION("time", -1, 0, 0, (dxFunc)timeFunc ),
1385 FUNCTION("datetime", -1, 0, 0, (dxFunc)datetimeFunc ),
1386 FUNCTION("strftime", -1, 0, 0, (dxFunc)strftimeFunc ),
1387 FUNCTION("current_time", 0, 0, 0, (dxFunc)ctimeFunc ),
1388 FUNCTION("current_timestamp", 0, 0, 0, (dxFunc)ctimestampFunc),
1389 FUNCTION("current_date", 0, 0, 0, (dxFunc)cdateFunc ),
1390 #else
1391 STR_FUNCTION("current_time", 0, "%H:%M:%S", 0, currentTimeFunc),
1392 STR_FUNCTION("current_date", 0, "%Y-%m-%d", 0, currentTimeFunc),
1393 STR_FUNCTION("current_timestamp", 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
1394 #endif
1395 };
1396 int i;
1397 #if SQLITE_OMIT_WSD
1398 FuncDefHash pHash = GLOBAL( FuncDefHash, sqlite3GlobalFunctions );
1399 FuncDef[] aFunc = (FuncDef)GLOBAL( FuncDef, aDateTimeFuncs );
1400 #else
1401 FuncDefHash pHash = sqlite3GlobalFunctions;
1402 FuncDef[] aFunc = aDateTimeFuncs;
1403 #endif
1404 for ( i = 0; i < ArraySize( aDateTimeFuncs ); i++ )
1405 {
1406 sqlite3FuncDefInsert( pHash, aFunc[i] );
1407 }
1408 }
1409 }
1410 }