connectivity/source/commontools/dbconversion.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19
  20 #include <connectivity/dbconversion.hxx>
  21 #include <com/sun/star/sdbc/SQLException.hpp>
  22 #include <com/sun/star/util/Date.hpp>
  23 #include <com/sun/star/util/Time.hpp>
  24 #include <com/sun/star/util/DateTime.hpp>
  25 #include <rtl/character.hxx>
  26 #include <rtl/math.hxx>
  27 #include <sal/log.hxx>
  28 #include <unotools/datetime.hxx>
  29 #include <comphelper/date.hxx>
  30 #include <o3tl/string_view.hxx>
  31 #include <sstream>
  32 #include <iomanip>
  33
  34 namespace
  35 {
  36     const sal_Int64 nanoSecInSec = 1000000000;
  37     const sal_Int16 secInMin  = 60;
  38     const sal_Int16 minInHour = 60;
  39
  40     const sal_Int64 secMask  = 1000000000;
  41     const sal_Int64 minMask  = 100000000000LL;
  42     const sal_Int64 hourMask = 10000000000000LL;
  43
  44     const double fNanoSecondsPerDay = nanoSecInSec * secInMin * minInHour * 24.0;
  45
  46     //  32767-12-31 in "(days since 0001-01-01) + 1" format
  47     const sal_Int32 maxDays =  11967896;
  48     // -32768-01-01 in "(days since 0001-01-01) + 1" format
  49     // Yes, I know it is currently unused. Will have to be used
  50     // when we implement negative years. Writing down the correct
  51     // value for future reference.
  52     // *** Please don't remove just because it is unused ***
  53     // Lionel Élie Mamane 2017-08-02
  54     // const sal_Int32 minDays = -11968270;
  55 }
  56
  57
  58 namespace dbtools
  59 {
  60
  61
  62     using namespace ::com::sun::star::uno;
  63     using namespace ::com::sun::star::util;
  64     using namespace ::com::sun::star::sdb;
  65     using namespace ::com::sun::star::sdbc;
  66     using namespace ::com::sun::star::lang;
  67     using namespace ::com::sun::star::beans;
  68
  69
  70     css::util::Date const & DBTypeConversion::getStandardDate()
  71     {
  72         static css::util::Date STANDARD_DB_DATE(1,1,1900);
  73         return STANDARD_DB_DATE;
  74     }
  75
  76     OUString DBTypeConversion::toDateString(const css::util::Date& rDate)
  77     {
  78         std::ostringstream ostr;
  79         using std::setw;
  80         ostr.fill('0');
  81         ostr << setw(4) << rDate.Year  << "-"
  82              << setw(2) << rDate.Month << "-"
  83              << setw(2) << rDate.Day;
  84         return OUString::createFromAscii(ostr.str().c_str());
  85     }
  86
  87     OUString DBTypeConversion::toTimeStringS(const css::util::Time& rTime)
  88     {
  89         std::ostringstream ostr;
  90         using std::setw;
  91         ostr.fill('0');
  92         ostr << setw(2) << rTime.Hours   << ":"
  93              << setw(2) << rTime.Minutes << ":"
  94              << setw(2) << rTime.Seconds;
  95         return OUString::createFromAscii(ostr.str().c_str());
  96     }
  97
  98     OUString DBTypeConversion::toTimeString(const css::util::Time& rTime)
  99     {
 100         std::ostringstream ostr;
 101         using std::setw;
 102         ostr.fill('0');
 103         ostr << setw(2) << rTime.Hours   << ":"
 104              << setw(2) << rTime.Minutes << ":"
 105              << setw(2) << rTime.Seconds << "."
 106              << setw(9) << rTime.NanoSeconds;
 107         return OUString::createFromAscii(ostr.str().c_str());
 108     }
 109
 110     OUString DBTypeConversion::toDateTimeString(const css::util::DateTime& _rDateTime)
 111     {
 112         css::util::Date aDate(_rDateTime.Day,_rDateTime.Month,_rDateTime.Year);
 113         css::util::Time const aTime(_rDateTime.NanoSeconds, _rDateTime.Seconds,
 114                 _rDateTime.Minutes, _rDateTime.Hours, _rDateTime.IsUTC);
 115         return toDateString(aDate) + " " + toTimeString(aTime);
 116     }
 117
 118     css::util::Date DBTypeConversion::toDate(const sal_Int32 _nVal)
 119     {
 120         css::util::Date aReturn;
 121         aReturn.Day = static_cast<sal_uInt16>(_nVal % 100);
 122         aReturn.Month = static_cast<sal_uInt16>((_nVal  / 100) % 100);
 123         aReturn.Year = static_cast<sal_uInt16>(_nVal  / 10000);
 124         return aReturn;
 125     }
 126
 127
 128     css::util::Time DBTypeConversion::toTime(const sal_Int64 _nVal)
 129     {
 130         css::util::Time aReturn;
 131         sal_uInt64 unVal = static_cast<sal_uInt64>(_nVal >= 0 ? _nVal : -_nVal);
 132         aReturn.Hours = unVal / hourMask;
 133         aReturn.Minutes = (unVal / minMask) % 100;
 134         aReturn.Seconds = (unVal / secMask) % 100;
 135         aReturn.NanoSeconds = unVal % secMask;
 136         return aReturn;
 137     }
 138
 139     sal_Int64 DBTypeConversion::getNsFromTime(const css::util::Time& rVal)
 140     {
 141         sal_Int32   nHour     = rVal.Hours;
 142         sal_Int32   nMin      = rVal.Minutes;
 143         sal_Int32   nSec      = rVal.Seconds;
 144         sal_Int32   nNanoSec  = rVal.NanoSeconds;
 145
 146         return nNanoSec +
 147                nSec  * nanoSecInSec +
 148                nMin  * (secInMin * nanoSecInSec) +
 149                nHour * (minInHour * secInMin * nanoSecInSec);
 150     }
 151
 152     static sal_Int32 implRelativeToAbsoluteNull(const css::util::Date& _rDate)
 153     {
 154         if (_rDate.Day == 0 && _rDate.Month == 0 && _rDate.Year == 0)
 155         {
 156             // 0000-00-00 is *NOT* a valid date and passing it to the date
 157             // conversion even when normalizing rightly asserts. Whatever we
 158             // return here, it will be wrong. The old before commit
 159             // 52ff16771ac160d27fd7beb78a4cfba22ad84f06 wrong implementation
 160             // calculated -365 for that, effectively that would be a date of
 161             // -0001-01-01 now but it was likely assumed that would be
 162             // 0000-00-01 or even 0000-00-00 instead. Try if we get away with 0
 163             // for -0001-12-31, the same that
 164             // comphelper::date::convertDateToDaysNormalizing()
 165             // would return if comphelper::date::normalize() wouldn't ignore
 166             // such "empty" date.
 167
 168             return 0;
 169         }
 170         return comphelper::date::convertDateToDaysNormalizing( _rDate.Day, _rDate.Month, _rDate.Year);
 171     }
 172
 173     sal_Int32 DBTypeConversion::toDays(const css::util::Date& _rVal, const css::util::Date& _rNullDate)
 174     {
 175         return implRelativeToAbsoluteNull(_rVal) - implRelativeToAbsoluteNull(_rNullDate);
 176     }
 177
 178
 179     double DBTypeConversion::toDouble(const css::util::Date& rVal, const css::util::Date& _rNullDate)
 180     {
 181         return static_cast<double>(toDays(rVal, _rNullDate));
 182     }
 183
 184
 185     double DBTypeConversion::toDouble(const css::util::Time& rVal)
 186     {
 187         return static_cast<double>(getNsFromTime(rVal)) / fNanoSecondsPerDay;
 188     }
 189
 190
 191     double DBTypeConversion::toDouble(const css::util::DateTime& _rVal, const css::util::Date& _rNullDate)
 192     {
 193         sal_Int64   nTime     = toDays(css::util::Date(_rVal.Day, _rVal.Month, _rVal.Year), _rNullDate);
 194         css::util::Time aTimePart;
 195
 196         aTimePart.Hours             = _rVal.Hours;
 197         aTimePart.Minutes           = _rVal.Minutes;
 198         aTimePart.Seconds           = _rVal.Seconds;
 199         aTimePart.NanoSeconds       = _rVal.NanoSeconds;
 200
 201         return static_cast<double>(nTime) + toDouble(aTimePart);
 202     }
 203
 204     static void addDays(const sal_Int32 nDays, css::util::Date& _rDate)
 205     {
 206         sal_Int64 nTempDays = implRelativeToAbsoluteNull(_rDate);
 207
 208         nTempDays += nDays;
 209         if ( nTempDays > maxDays )
 210         {
 211             _rDate.Day      = 31;
 212             _rDate.Month    = 12;
 213             _rDate.Year     = 9999;
 214         }
 215         // TODO: can we replace that check by minDays? Would allow dates BCE
 216         else if ( nTempDays <= 0 )
 217         {
 218             _rDate.Day      = 1;
 219             _rDate.Month    = 1;
 220             _rDate.Year     = 1;
 221         }
 222         else
 223             comphelper::date::convertDaysToDate( nTempDays, _rDate.Day, _rDate.Month, _rDate.Year );
 224     }
 225
 226     static void subDays(const sal_Int32 nDays, css::util::Date& _rDate )
 227     {
 228         sal_Int64 nTempDays = implRelativeToAbsoluteNull(_rDate);
 229
 230         nTempDays -= nDays;
 231         if ( nTempDays > maxDays )
 232         {
 233             _rDate.Day      = 31;
 234             _rDate.Month    = 12;
 235             _rDate.Year     = 9999;
 236         }
 237         // TODO: can we replace that check by minDays? Would allow dates BCE
 238         else if ( nTempDays <= 0 )
 239         {
 240             _rDate.Day      = 1;
 241             _rDate.Month    = 1;
 242             _rDate.Year     = 1;
 243         }
 244         else
 245             comphelper::date::convertDaysToDate( nTempDays, _rDate.Day, _rDate.Month, _rDate.Year );
 246     }
 247
 248     css::util::Date DBTypeConversion::toDate(const double dVal, const css::util::Date& _rNullDate)
 249     {
 250         css::util::Date aRet = _rNullDate;
 251
 252         if (dVal >= 0)
 253             addDays(static_cast<sal_Int32>(dVal),aRet);
 254         else
 255             subDays(static_cast<sal_uInt32>(-dVal),aRet);
 256             //  x -= (sal_uInt32)(-nDays);
 257
 258         return aRet;
 259     }
 260
 261     css::util::Time DBTypeConversion::toTime(const double dVal, short nDigits)
 262     {
 263         const double nDays = std::trunc(dVal);
 264         double fSeconds((dVal - nDays) * (fNanoSecondsPerDay / nanoSecInSec));
 265         fSeconds = ::rtl::math::round(fSeconds, nDigits);
 266         sal_Int64 nNS = fSeconds * nanoSecInSec;
 267
 268         sal_Int16 nSign;
 269         if ( nNS < 0 )
 270         {
 271             nNS *= -1;
 272             nSign = -1;
 273         }
 274         else
 275             nSign = 1;
 276
 277         css::util::Time aRet;
 278         // normalize time
 279         // we have to sal_Int32 here because otherwise we get an overflow
 280         sal_Int64 nNanoSeconds      = nNS;
 281         sal_Int32 nSeconds          = nNanoSeconds / nanoSecInSec;
 282         sal_Int32 nMinutes          = nSeconds / secInMin;
 283
 284         aRet.NanoSeconds            = nNanoSeconds % nanoSecInSec;
 285         aRet.Seconds                = nSeconds % secInMin;
 286         aRet.Hours                  = nMinutes / minInHour;
 287         aRet.Minutes                = nMinutes % minInHour;
 288
 289         // assemble time
 290         sal_Int64 nTime = nSign *
 291                           (aRet.NanoSeconds +
 292                            aRet.Seconds * secMask +
 293                            aRet.Minutes * minMask +
 294                            aRet.Hours   * hourMask);
 295
 296         if(nTime < 0)
 297         {
 298             aRet.NanoSeconds  = nanoSecInSec-1;
 299             aRet.Seconds      = secInMin-1;
 300             aRet.Minutes      = minInHour-1;
 301             aRet.Hours        = 23;
 302         }
 303         return aRet;
 304     }
 305
 306     css::util::DateTime DBTypeConversion::toDateTime(const double dVal, const css::util::Date& _rNullDate)
 307     {
 308         css::util::DateTime aRet;
 309
 310         if (!std::isfinite(dVal))
 311         {
 312             SAL_WARN("connectivity.commontools", "DateTime has invalid value: " << dVal);
 313             return aRet;
 314         }
 315
 316         css::util::Date aDate = toDate(dVal, _rNullDate);
 317         // there is not enough precision in a double to have both a date
 318         // and a time up to nanoseconds -> limit to microseconds to have
 319         // correct rounding, that is e.g. 13:00:00.000000000 instead of
 320         // 12:59:59.999999790
 321         css::util::Time aTime = toTime(dVal, 6);
 322
 323         aRet.Day          = aDate.Day;
 324         aRet.Month        = aDate.Month;
 325         aRet.Year         = aDate.Year;
 326
 327         aRet.NanoSeconds  = aTime.NanoSeconds;
 328         aRet.Minutes      = aTime.Minutes;
 329         aRet.Seconds      = aTime.Seconds;
 330         aRet.Hours        = aTime.Hours;
 331
 332
 333         return aRet;
 334     }
 335
 336     css::util::Date DBTypeConversion::toDate(std::u16string_view _sSQLString)
 337     {
 338         // get the token out of a string
 339         static const sal_Unicode sDateSep = '-';
 340
 341         sal_Int32 nIndex    = 0;
 342         sal_uInt16  nYear   = 0,
 343                     nMonth  = 0,
 344                     nDay    = 0;
 345         nYear   = static_cast<sal_uInt16>(o3tl::toInt32(o3tl::getToken(_sSQLString, 0,sDateSep,nIndex)));
 346         if(nIndex != -1)
 347         {
 348             nMonth = static_cast<sal_uInt16>(o3tl::toInt32(o3tl::getToken(_sSQLString, 0,sDateSep,nIndex)));
 349             if(nIndex != -1)
 350                 nDay = static_cast<sal_uInt16>(o3tl::toInt32(o3tl::getToken(_sSQLString, 0,sDateSep,nIndex)));
 351         }
 352
 353         return css::util::Date(nDay,nMonth,nYear);
 354     }
 355
 356
 357     css::util::DateTime DBTypeConversion::toDateTime(const OUString& _sSQLString)
 358     {
 359         //@see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Timestamp.html#valueOf(java.lang.String)
 360         //@see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Date.html#valueOf(java.lang.String)
 361         //@see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Time.html#valueOf(java.lang.String)
 362
 363         // the date part
 364         css::util::Date aDate = toDate(_sSQLString);
 365         css::util::Time aTime;
 366         sal_Int32 nSeparation = _sSQLString.indexOf( ' ' );
 367         if ( -1 != nSeparation )
 368         {
 369             const sal_Unicode *p = _sSQLString.getStr() + nSeparation;
 370             const sal_Unicode *const begin = p;
 371             while (rtl::isAsciiWhiteSpace(*p)) { ++p; }
 372             nSeparation += p - begin;
 373             aTime = toTime( _sSQLString.subView( nSeparation ) );
 374         }
 375
 376         return css::util::DateTime(aTime.NanoSeconds, aTime.Seconds, aTime.Minutes, aTime.Hours,
 377                         aDate.Day, aDate.Month, aDate.Year, false);
 378     }
 379
 380
 381     css::util::Time DBTypeConversion::toTime(std::u16string_view _sSQLString)
 382     {
 383         css::util::Time aTime;
 384         ::utl::ISO8601parseTime(_sSQLString, aTime);
 385         return aTime;
 386     }
 387
 388
 389 }   // namespace dbtools
 390
 391
 392 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */