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::lang;
  66     using namespace ::com::sun::star::beans;
  67
  68
  69     css::util::Date const & DBTypeConversion::getStandardDate()
  70     {
  71         static css::util::Date STANDARD_DB_DATE(1,1,1900);
  72         return STANDARD_DB_DATE;
  73     }
  74
  75     OUString DBTypeConversion::toDateString(const css::util::Date& rDate)
  76     {
  77         std::ostringstream ostr;
  78         using std::setw;
  79         ostr.fill('0');
  80         ostr << setw(4) << rDate.Year  << "-"
  81              << setw(2) << rDate.Month << "-"
  82              << setw(2) << rDate.Day;
  83         return OUString::createFromAscii(ostr.str());
  84     }
  85
  86     OUString DBTypeConversion::toTimeStringS(const css::util::Time& rTime)
  87     {
  88         std::ostringstream ostr;
  89         using std::setw;
  90         ostr.fill('0');
  91         ostr << setw(2) << rTime.Hours   << ":"
  92              << setw(2) << rTime.Minutes << ":"
  93              << setw(2) << rTime.Seconds;
  94         return OUString::createFromAscii(ostr.str());
  95     }
  96
  97     OUString DBTypeConversion::toTimeString(const css::util::Time& rTime)
  98     {
  99         std::ostringstream ostr;
 100         using std::setw;
 101         ostr.fill('0');
 102         ostr << setw(2) << rTime.Hours   << ":"
 103              << setw(2) << rTime.Minutes << ":"
 104              << setw(2) << rTime.Seconds << "."
 105              << setw(9) << rTime.NanoSeconds;
 106         return OUString::createFromAscii(ostr.str());
 107     }
 108
 109     OUString DBTypeConversion::toDateTimeString(const css::util::DateTime& _rDateTime)
 110     {
 111         css::util::Date aDate(_rDateTime.Day,_rDateTime.Month,_rDateTime.Year);
 112         css::util::Time const aTime(_rDateTime.NanoSeconds, _rDateTime.Seconds,
 113                 _rDateTime.Minutes, _rDateTime.Hours, _rDateTime.IsUTC);
 114         return toDateString(aDate) + " " + toTimeString(aTime);
 115     }
 116
 117     css::util::Date DBTypeConversion::toDate(const sal_Int32 _nVal)
 118     {
 119         css::util::Date aReturn;
 120         aReturn.Day = static_cast<sal_uInt16>(_nVal % 100);
 121         aReturn.Month = static_cast<sal_uInt16>((_nVal  / 100) % 100);
 122         aReturn.Year = static_cast<sal_uInt16>(_nVal  / 10000);
 123         return aReturn;
 124     }
 125
 126
 127     css::util::Time DBTypeConversion::toTime(const sal_Int64 _nVal)
 128     {
 129         css::util::Time aReturn;
 130         sal_uInt64 unVal = static_cast<sal_uInt64>(_nVal >= 0 ? _nVal : -_nVal);
 131         aReturn.Hours = unVal / hourMask;
 132         aReturn.Minutes = (unVal / minMask) % 100;
 133         aReturn.Seconds = (unVal / secMask) % 100;
 134         aReturn.NanoSeconds = unVal % secMask;
 135         return aReturn;
 136     }
 137
 138     sal_Int64 DBTypeConversion::getNsFromTime(const css::util::Time& rVal)
 139     {
 140         sal_Int32   nHour     = rVal.Hours;
 141         sal_Int32   nMin      = rVal.Minutes;
 142         sal_Int32   nSec      = rVal.Seconds;
 143         sal_Int32   nNanoSec  = rVal.NanoSeconds;
 144
 145         return nNanoSec +
 146                nSec  * nanoSecInSec +
 147                nMin  * (secInMin * nanoSecInSec) +
 148                nHour * (minInHour * secInMin * nanoSecInSec);
 149     }
 150
 151     static sal_Int32 implRelativeToAbsoluteNull(const css::util::Date& _rDate)
 152     {
 153         if (_rDate.Day == 0 && _rDate.Month == 0 && _rDate.Year == 0)
 154         {
 155             // 0000-00-00 is *NOT* a valid date and passing it to the date
 156             // conversion even when normalizing rightly asserts. Whatever we
 157             // return here, it will be wrong. The old before commit
 158             // 52ff16771ac160d27fd7beb78a4cfba22ad84f06 wrong implementation
 159             // calculated -365 for that, effectively that would be a date of
 160             // -0001-01-01 now but it was likely assumed that would be
 161             // 0000-00-01 or even 0000-00-00 instead. Try if we get away with 0
 162             // for -0001-12-31, the same that
 163             // comphelper::date::convertDateToDaysNormalizing()
 164             // would return if comphelper::date::normalize() wouldn't ignore
 165             // such "empty" date.
 166
 167             return 0;
 168         }
 169         return comphelper::date::convertDateToDaysNormalizing( _rDate.Day, _rDate.Month, _rDate.Year);
 170     }
 171
 172     sal_Int32 DBTypeConversion::toDays(const css::util::Date& _rVal, const css::util::Date& _rNullDate)
 173     {
 174         return implRelativeToAbsoluteNull(_rVal) - implRelativeToAbsoluteNull(_rNullDate);
 175     }
 176
 177
 178     double DBTypeConversion::toDouble(const css::util::Date& rVal, const css::util::Date& _rNullDate)
 179     {
 180         return static_cast<double>(toDays(rVal, _rNullDate));
 181     }
 182
 183
 184     double DBTypeConversion::toDouble(const css::util::Time& rVal)
 185     {
 186         return static_cast<double>(getNsFromTime(rVal)) / fNanoSecondsPerDay;
 187     }
 188
 189
 190     double DBTypeConversion::toDouble(const css::util::DateTime& _rVal, const css::util::Date& _rNullDate)
 191     {
 192         sal_Int64   nTime     = toDays(css::util::Date(_rVal.Day, _rVal.Month, _rVal.Year), _rNullDate);
 193         css::util::Time aTimePart;
 194
 195         aTimePart.Hours             = _rVal.Hours;
 196         aTimePart.Minutes           = _rVal.Minutes;
 197         aTimePart.Seconds           = _rVal.Seconds;
 198         aTimePart.NanoSeconds       = _rVal.NanoSeconds;
 199
 200         return static_cast<double>(nTime) + toDouble(aTimePart);
 201     }
 202
 203     static void addDays(const sal_Int32 nDays, css::util::Date& _rDate)
 204     {
 205         sal_Int64 nTempDays = implRelativeToAbsoluteNull(_rDate);
 206
 207         nTempDays += nDays;
 208         if ( nTempDays > maxDays )
 209         {
 210             _rDate.Day      = 31;
 211             _rDate.Month    = 12;
 212             _rDate.Year     = 9999;
 213         }
 214         // TODO: can we replace that check by minDays? Would allow dates BCE
 215         else if ( nTempDays <= 0 )
 216         {
 217             _rDate.Day      = 1;
 218             _rDate.Month    = 1;
 219             _rDate.Year     = 1;
 220         }
 221         else
 222             comphelper::date::convertDaysToDate( nTempDays, _rDate.Day, _rDate.Month, _rDate.Year );
 223     }
 224
 225     static void subDays(const sal_Int32 nDays, css::util::Date& _rDate )
 226     {
 227         sal_Int64 nTempDays = implRelativeToAbsoluteNull(_rDate);
 228
 229         nTempDays -= nDays;
 230         if ( nTempDays > maxDays )
 231         {
 232             _rDate.Day      = 31;
 233             _rDate.Month    = 12;
 234             _rDate.Year     = 9999;
 235         }
 236         // TODO: can we replace that check by minDays? Would allow dates BCE
 237         else if ( nTempDays <= 0 )
 238         {
 239             _rDate.Day      = 1;
 240             _rDate.Month    = 1;
 241             _rDate.Year     = 1;
 242         }
 243         else
 244             comphelper::date::convertDaysToDate( nTempDays, _rDate.Day, _rDate.Month, _rDate.Year );
 245     }
 246
 247     css::util::Date DBTypeConversion::toDate(const double dVal, const css::util::Date& _rNullDate)
 248     {
 249         css::util::Date aRet = _rNullDate;
 250
 251         if (dVal >= 0)
 252             addDays(static_cast<sal_Int32>(dVal),aRet);
 253         else
 254             subDays(static_cast<sal_uInt32>(-dVal),aRet);
 255             //  x -= (sal_uInt32)(-nDays);
 256
 257         return aRet;
 258     }
 259
 260     css::util::Time DBTypeConversion::toTime(const double dVal, short nDigits)
 261     {
 262         const double nDays = std::trunc(dVal);
 263         double fSeconds((dVal - nDays) * (fNanoSecondsPerDay / nanoSecInSec));
 264         fSeconds = ::rtl::math::round(fSeconds, nDigits);
 265         sal_Int64 nNS = fSeconds * nanoSecInSec;
 266
 267         sal_Int16 nSign;
 268         if ( nNS < 0 )
 269         {
 270             nNS *= -1;
 271             nSign = -1;
 272         }
 273         else
 274             nSign = 1;
 275
 276         css::util::Time aRet;
 277         // normalize time
 278         // we have to sal_Int32 here because otherwise we get an overflow
 279         sal_Int64 nNanoSeconds      = nNS;
 280         sal_Int32 nSeconds          = nNanoSeconds / nanoSecInSec;
 281         sal_Int32 nMinutes          = nSeconds / secInMin;
 282
 283         aRet.NanoSeconds            = nNanoSeconds % nanoSecInSec;
 284         aRet.Seconds                = nSeconds % secInMin;
 285         aRet.Hours                  = nMinutes / minInHour;
 286         aRet.Minutes                = nMinutes % minInHour;
 287
 288         // assemble time
 289         sal_Int64 nTime = nSign *
 290                           (aRet.NanoSeconds +
 291                            aRet.Seconds * secMask +
 292                            aRet.Minutes * minMask +
 293                            aRet.Hours   * hourMask);
 294
 295         if(nTime < 0)
 296         {
 297             aRet.NanoSeconds  = nanoSecInSec-1;
 298             aRet.Seconds      = secInMin-1;
 299             aRet.Minutes      = minInHour-1;
 300             aRet.Hours        = 23;
 301         }
 302         return aRet;
 303     }
 304
 305     css::util::DateTime DBTypeConversion::toDateTime(const double dVal, const css::util::Date& _rNullDate)
 306     {
 307         css::util::DateTime aRet;
 308
 309         if (!std::isfinite(dVal))
 310         {
 311             SAL_WARN("connectivity.commontools", "DateTime has invalid value: " << dVal);
 312             return aRet;
 313         }
 314
 315         css::util::Date aDate = toDate(dVal, _rNullDate);
 316         // there is not enough precision in a double to have both a date
 317         // and a time up to nanoseconds -> limit to microseconds to have
 318         // correct rounding, that is e.g. 13:00:00.000000000 instead of
 319         // 12:59:59.999999790
 320         css::util::Time aTime = toTime(dVal, 6);
 321
 322         aRet.Day          = aDate.Day;
 323         aRet.Month        = aDate.Month;
 324         aRet.Year         = aDate.Year;
 325
 326         aRet.NanoSeconds  = aTime.NanoSeconds;
 327         aRet.Minutes      = aTime.Minutes;
 328         aRet.Seconds      = aTime.Seconds;
 329         aRet.Hours        = aTime.Hours;
 330
 331
 332         return aRet;
 333     }
 334
 335     css::util::Date DBTypeConversion::toDate(std::u16string_view _sSQLString)
 336     {
 337         // get the token out of a string
 338         static const sal_Unicode sDateSep = '-';
 339
 340         sal_Int32 nIndex    = 0;
 341         sal_uInt16  nYear   = 0,
 342                     nMonth  = 0,
 343                     nDay    = 0;
 344         nYear   = static_cast<sal_uInt16>(o3tl::toInt32(o3tl::getToken(_sSQLString, 0,sDateSep,nIndex)));
 345         if(nIndex != -1)
 346         {
 347             nMonth = static_cast<sal_uInt16>(o3tl::toInt32(o3tl::getToken(_sSQLString, 0,sDateSep,nIndex)));
 348             if(nIndex != -1)
 349                 nDay = static_cast<sal_uInt16>(o3tl::toInt32(o3tl::getToken(_sSQLString, 0,sDateSep,nIndex)));
 350         }
 351
 352         return css::util::Date(nDay,nMonth,nYear);
 353     }
 354
 355
 356     css::util::DateTime DBTypeConversion::toDateTime(const OUString& _sSQLString)
 357     {
 358         //@see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Timestamp.html#valueOf(java.lang.String)
 359         //@see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Date.html#valueOf(java.lang.String)
 360         //@see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Time.html#valueOf(java.lang.String)
 361
 362         // the date part
 363         css::util::Date aDate = toDate(_sSQLString);
 364         css::util::Time aTime;
 365         sal_Int32 nSeparation = _sSQLString.indexOf( ' ' );
 366         if ( -1 != nSeparation )
 367         {
 368             const sal_Unicode *p = _sSQLString.getStr() + nSeparation;
 369             const sal_Unicode *const begin = p;
 370             while (rtl::isAsciiWhiteSpace(*p)) { ++p; }
 371             nSeparation += p - begin;
 372             aTime = toTime( _sSQLString.subView( nSeparation ) );
 373         }
 374
 375         return css::util::DateTime(aTime.NanoSeconds, aTime.Seconds, aTime.Minutes, aTime.Hours,
 376                         aDate.Day, aDate.Month, aDate.Year, false);
 377     }
 378
 379
 380     css::util::Time DBTypeConversion::toTime(std::u16string_view _sSQLString)
 381     {
 382         css::util::Time aTime;
 383         ::utl::ISO8601parseTime(_sSQLString, aTime);
 384         return aTime;
 385     }
 386
 387
 388 }   // namespace dbtools
 389
 390
 391 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */