SqlDateTime.hpp

// SPDX-License-Identifier: Apache-2.0
 
#pragma once
 
#include "../SqlColumnTypeDefinitions.hpp"
#include "Core.hpp"
#include "SqlDate.hpp"
#include "SqlTime.hpp"
 
#include <algorithm>
#include <chrono>
#include <cstddef>
#include <cstring>
#include <format>
#include <memory>
#include <ranges>
#include <string_view>
#include <vector>
 
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
 
namespace Lightweight
{
 
/// Represents a date and time to efficiently write to or read from a database.
///
/// @see SqlDate, SqlTime
/// @ingroup DataTypes
struct SqlDateTime
{
    /// The native C++ type representing a date-time value.
    using native_type = std::chrono::system_clock::time_point;
    /// The duration type used for arithmetic operations.
    using duration_type = std::chrono::system_clock::duration;
 
    /// Returns the current date and time.
    [[nodiscard]] static LIGHTWEIGHT_FORCE_INLINE SqlDateTime Now() noexcept
    {
        return SqlDateTime { std::chrono::system_clock::now() };
    }
 
#if !defined(LIGHTWEIGHT_CXX26_REFLECTION)
    /// Return the current date and time in UTC.
    [[nodiscard]] static LIGHTWEIGHT_FORCE_INLINE SqlDateTime NowUTC() noexcept
    {
        // `std::chrono::system_clock` already represents Unix time (UTC, no leap seconds) and is what
        // `SqlDateTime` stores, so it yields the correct UTC value directly and identically on every
        // platform. We deliberately avoid `utc_clock::now().time_since_epoch()` here: it counts leap
        // seconds, so reinterpreting that duration as a `system_clock::time_point` would shift the value
        // ~27s into the future, and `utc_clock` is unavailable on libc++ without a built-in tz database
        // (the Homebrew toolchain on macOS) — both branches would otherwise disagree.
        return SqlDateTime { std::chrono::system_clock::now() };
    }
#endif
 
    /// Default constructor.
    constexpr SqlDateTime() noexcept = default;
    /// Default move constructor.
    constexpr SqlDateTime(SqlDateTime&&) noexcept = default;
    /// Default move assignment operator.
    constexpr SqlDateTime& operator=(SqlDateTime&&) noexcept = default;
    /// Default copy constructor.
    constexpr SqlDateTime(SqlDateTime const&) noexcept = default;
    /// Default copy assignment operator.
    constexpr SqlDateTime& operator=(SqlDateTime const& other) noexcept = default;
    constexpr ~SqlDateTime() noexcept = default;
 
    /// Three-way comparison operator.
    constexpr std::weak_ordering operator<=>(SqlDateTime const& other) const noexcept
    {
        return value() <=> other.value();
    }
    /// Equality comparison operator.
    constexpr bool operator==(SqlDateTime const& other) const noexcept
    {
        return (*this <=> other) == std::weak_ordering::equivalent;
    }
 
    /// Inequality comparison operator.
    constexpr bool operator!=(SqlDateTime const& other) const noexcept
    {
        return !(*this == other);
    }
 
    /// Constructs a date and time from individual components.
    LIGHTWEIGHT_FORCE_INLINE constexpr SqlDateTime(std::chrono::year_month_day ymd,
                                                   std::chrono::hh_mm_ss<duration_type> time) noexcept:
        sqlValue {
            .year = (SQLSMALLINT) (int) ymd.year(),
            .month = (SQLUSMALLINT) (unsigned) ymd.month(),
            .day = (SQLUSMALLINT) (unsigned) ymd.day(),
            .hour = (SQLUSMALLINT) time.hours().count(),
            .minute = (SQLUSMALLINT) time.minutes().count(),
            .second = (SQLUSMALLINT) time.seconds().count(),
            .fraction =
                (SQLUINTEGER) (std::chrono::duration_cast<std::chrono::nanoseconds>(time.to_duration()).count() / 100) * 100,
        }
    {
    }
 
    /// Constructs a date and time from individual components.
    LIGHTWEIGHT_FORCE_INLINE constexpr SqlDateTime(
        std::chrono::year year,
        std::chrono::month month,
        std::chrono::day day,
        std::chrono::hours hour,
        std::chrono::minutes minute,
        std::chrono::seconds second,
        std::chrono::nanoseconds nanosecond = std::chrono::nanoseconds(0)) noexcept:
        sqlValue {
            .year = (SQLSMALLINT) (int) year,
            .month = (SQLUSMALLINT) (unsigned) month,
            .day = (SQLUSMALLINT) (unsigned) day,
            .hour = (SQLUSMALLINT) hour.count(),
            .minute = (SQLUSMALLINT) minute.count(),
            .second = (SQLUSMALLINT) second.count(),
            .fraction = (SQLUINTEGER) (nanosecond.count() / 100) * 100,
        }
    {
    }
 
    /// Constructs a date and time from a time point.
    LIGHTWEIGHT_FORCE_INLINE constexpr SqlDateTime(std::chrono::system_clock::time_point value) noexcept:
        sqlValue { ConvertToSqlValue(value) }
    {
    }
 
    // NOLINTBEGIN(readability-identifier-naming)
 
    /// Returns the year of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::year year() const noexcept
    {
        return std::chrono::year(static_cast<int>(sqlValue.year));
    }
 
    /// Returns the month of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::month month() const noexcept
    {
        return std::chrono::month(static_cast<unsigned>(sqlValue.month));
    }
 
    /// Returns the day of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::day day() const noexcept
    {
        return std::chrono::day(static_cast<unsigned>(sqlValue.day));
    }
 
    /// Returns the hour of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::hours hour() const noexcept
    {
        return std::chrono::hours(static_cast<unsigned>(sqlValue.hour));
    }
 
    /// Returns the minute of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::minutes minute() const noexcept
    {
        return std::chrono::minutes(static_cast<unsigned>(sqlValue.minute));
    }
 
    /// Returns the second of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::seconds second() const noexcept
    {
        return std::chrono::seconds(static_cast<unsigned>(sqlValue.second));
    }
 
    /// Returns the nanosecond of this date-time object.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE std::chrono::nanoseconds nanosecond() const noexcept
    {
        return std::chrono::nanoseconds(static_cast<unsigned>(sqlValue.fraction));
    }
 
    // NOLINTEND(readability-identifier-naming)
 
    /// Converts this SqlDateTime to its native time_point representation.
    LIGHTWEIGHT_FORCE_INLINE constexpr operator native_type() const noexcept
    {
        return value();
    }
 
    /// @brief Constructs only a date from a SQL date-time structure.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE SqlDate Date() const noexcept
    {
        return SqlDate { year(), month(), day() };
    }
 
    /// @brief Constructs only a time from a SQL date-time structure.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE SqlTime Time() const noexcept
    {
        return SqlTime { std::chrono::hours { hour() },
                         std::chrono::minutes { minute() },
                         std::chrono::seconds { second() },
                         std::chrono::microseconds { std::chrono::duration_cast<std::chrono::microseconds>(nanosecond()) } };
    }
 
    /// Converts a native time_point to the underlying SQL timestamp structure.
    static LIGHTWEIGHT_FORCE_INLINE SQL_TIMESTAMP_STRUCT constexpr ConvertToSqlValue(native_type value) noexcept
    {
        using namespace std::chrono;
        auto const totalDays = floor<days>(value);
        auto const ymd = year_month_day { totalDays };
        auto const hms =
            hh_mm_ss<duration_type> { std::chrono::duration_cast<duration_type>(floor<nanoseconds>(value - totalDays)) };
        return ConvertToSqlValue(ymd, hms);
    }
 
    /// Converts year_month_day and hh_mm_ss components to the underlying SQL timestamp structure.
    static LIGHTWEIGHT_FORCE_INLINE SQL_TIMESTAMP_STRUCT constexpr ConvertToSqlValue(
        std::chrono::year_month_day ymd, std::chrono::hh_mm_ss<duration_type> hms) noexcept
    {
        // clang-format off
        // NB: The fraction field is in 100ns units.
        return SQL_TIMESTAMP_STRUCT {
            .year = (SQLSMALLINT) (int) ymd.year(),
            .month = (SQLUSMALLINT) (unsigned) ymd.month(),
            .day = (SQLUSMALLINT) (unsigned) ymd.day(),
            .hour = (SQLUSMALLINT) hms.hours().count(),
            .minute = (SQLUSMALLINT) hms.minutes().count(),
            .second = (SQLUSMALLINT) hms.seconds().count(),
            .fraction = (SQLUINTEGER) (((static_cast<unsigned long long>(std::chrono::duration_cast<std::chrono::nanoseconds>(hms.to_duration()).count()) % 1'000'000'000LLU) / 100) * 100)
        };
        // clang-format on
    }
 
    /// Converts a SQL timestamp structure to the native time_point representation.
    static LIGHTWEIGHT_FORCE_INLINE native_type constexpr ConvertToNative(SQL_TIMESTAMP_STRUCT const& time) noexcept
    {
        // clang-format off
        using namespace std::chrono;
        auto const ymd = year_month_day { std::chrono::year { time.year } / std::chrono::month { time.month } / std::chrono::day { time.day } };
        auto const hms = hh_mm_ss<duration_type> {
            duration_cast<duration_type>(
                hours { time.hour }
                + minutes { time.minute }
                + seconds { time.second }
                + nanoseconds { time.fraction }
            )
        };
        return sys_days { ymd } + hms.to_duration();
        // clang-format on
    }
 
    /// Returns the current date and time.
    [[nodiscard]] constexpr LIGHTWEIGHT_FORCE_INLINE native_type value() const noexcept
    {
        return ConvertToNative(sqlValue);
    }
 
    /// Adds a duration to this date-time.
    LIGHTWEIGHT_FORCE_INLINE SqlDateTime& operator+=(duration_type duration) noexcept
    {
        *this = SqlDateTime { value() + duration };
        return *this;
    }
 
    /// Subtracts a duration from this date-time.
    LIGHTWEIGHT_FORCE_INLINE SqlDateTime& operator-=(duration_type duration) noexcept
    {
        *this = SqlDateTime { value() - duration };
        return *this;
    }
 
    friend LIGHTWEIGHT_FORCE_INLINE SqlDateTime operator+(SqlDateTime dateTime, duration_type duration) noexcept
    {
        return SqlDateTime { dateTime.value() + duration };
    }
 
    friend LIGHTWEIGHT_FORCE_INLINE SqlDateTime operator-(SqlDateTime dateTime, duration_type duration) noexcept
    {
        return SqlDateTime { dateTime.value() - duration };
    }
 
    /// Holds the underlying SQL timestamp structure.
    SQL_TIMESTAMP_STRUCT sqlValue {};
};
 
} // namespace Lightweight
 
template <>
struct std::formatter<Lightweight::SqlDateTime>: std::formatter<std::string>
{
    LIGHTWEIGHT_FORCE_INLINE auto format(Lightweight::SqlDateTime const& value, std::format_context& ctx) const
        -> std::format_context::iterator
    {
        // This can be used manually to format the date and time inside sql query builder,
        // that is why we need to use iso standard 8601, also millisecond precision is used for the formatting
        //
        // internally fraction is stored in nanoseconds, here we need to get it in milliseconds
        auto const milliseconds = value.sqlValue.fraction / 1'000'000;
        return std::formatter<std::string>::format(std::format("{:04}-{:02}-{:02}T{:02}:{:02}:{:02}.{:03}",
                                                               value.sqlValue.year,
                                                               value.sqlValue.month,
                                                               value.sqlValue.day,
                                                               value.sqlValue.hour,
                                                               value.sqlValue.minute,
                                                               value.sqlValue.second,
                                                               milliseconds),
                                                   ctx);
    }
};
 
template <>
struct std::formatter<std::optional<Lightweight::SqlDateTime>>: std::formatter<std::string>
{
    LIGHTWEIGHT_FORCE_INLINE auto format(std::optional<Lightweight::SqlDateTime> const& value,
                                         std::format_context& ctx) const -> std::format_context::iterator
    {
        if (!value.has_value())
            return std::formatter<std::string>::format("nullopt", ctx);
        return std::formatter<std::string>::format(std::format("{}", value.value()), ctx);
    }
};
 
namespace Lightweight
{
 
template <>
struct SqlDataBinder<SqlDateTime::native_type>
{
    static LIGHTWEIGHT_FORCE_INLINE SQLRETURN GetColumn(SQLHSTMT stmt,
                                                        SQLUSMALLINT column,
                                                        SqlDateTime::native_type* result,
                                                        SQLLEN* indicator,
                                                        SqlDataBinderCallback const& /*cb*/) noexcept
    {
        SQL_TIMESTAMP_STRUCT sqlValue {};
        auto const rc = SQLGetData(stmt, column, SQL_C_TYPE_TIMESTAMP, &sqlValue, sizeof(sqlValue), indicator);
        if (SQL_SUCCEEDED(rc))
            *result = SqlDateTime::ConvertToNative(sqlValue);
        return rc;
    }
};
 
namespace detail
{
    /// Whether the statement runs on the legacy in-box SQL Server ODBC driver (SQLSRV32.DLL).
    ///
    /// That driver predates `datetime2`: it rejects the 27/7 timestamp binding with HY104, and it cannot
    /// describe a parameter once another one is bound, so date-times bind there as `datetime` (23/3).
    [[nodiscard]] inline bool IsLegacySqlServerDriver(SqlDataBinderCallback const& cb) noexcept
    {
        using namespace std::string_view_literals;
        return cb.ServerType() == SqlServerType::MICROSOFT_SQL && cb.DriverName() == "SQLSRV32.DLL"sv;
    }
 
    /// The layout of the parameter array that is being bound.
    struct SqlParameterArrayLayout
    {
        /// The byte distance from one row's value to the next.
        std::size_t rowStride {};
        /// The number of rows.
        std::size_t rowCount {};
    };
 
    /// Reads the layout of the parameter array the statement is bound with.
    ///
    /// @param stmt The statement whose parameters are being bound.
    /// @param valueSize The size of one value, which is the stride of a column-wise array.
    /// @param columnWiseRowCount The row count of a column-wise (or single-row) binding.
    /// @return The row size and parameter set size of a row-wise binding, otherwise the column-wise layout.
    [[nodiscard]] inline SqlParameterArrayLayout CurrentParameterArrayLayout(SQLHSTMT stmt,
                                                                             std::size_t valueSize,
                                                                             std::size_t columnWiseRowCount) noexcept
    {
        auto bindType = SQLULEN { SQL_PARAM_BIND_BY_COLUMN };
        if (!SQL_SUCCEEDED(SQLGetStmtAttr(stmt, SQL_ATTR_PARAM_BIND_TYPE, &bindType, 0, nullptr))
            || bindType == SQL_PARAM_BIND_BY_COLUMN)
            return { .rowStride = valueSize, .rowCount = columnWiseRowCount };
 
        auto parameterSetSize = SQLULEN { 1 };
        if (!SQL_SUCCEEDED(SQLGetStmtAttr(stmt, SQL_ATTR_PARAMSET_SIZE, &parameterSetSize, 0, nullptr)))
            parameterSetSize = 1;
        return { .rowStride = static_cast<std::size_t>(bindType), .rowCount = static_cast<std::size_t>(parameterSetSize) };
    }
 
    /// Binds the timestamps at @p first as `datetime` (23/3) for the legacy in-box SQL Server ODBC driver.
    ///
    /// The driver rejects fractions finer than the bound scale, so this binds a copy truncated to whole
    /// milliseconds, laid out like the source and kept alive until the statement has executed.
    ///
    /// @param stmt The statement to bind to.
    /// @param column The 1-based parameter index.
    /// @param first The first row's timestamp.
    /// @param layout The stride and row count of the timestamps starting at @p first.
    /// @param cb The data binder callback that keeps the copy alive.
    /// @param indicators The NULL indicators, or @c nullptr when no value is NULL.
    /// @return The ODBC return code of the bind.
    [[nodiscard]] inline SQLRETURN BindLegacySqlServerDateTimes(SQLHSTMT stmt,
                                                                SQLUSMALLINT column,
                                                                SQL_TIMESTAMP_STRUCT const* first,
                                                                SqlParameterArrayLayout const& layout,
                                                                SqlDataBinderCallback& cb,
                                                                SQLLEN* indicators) noexcept
    {
        constexpr auto NanosecondsPerMillisecond = SQLUINTEGER { 1'000'000 };
        auto const rowCount = (std::max) (layout.rowCount, std::size_t { 1 });
        auto const staged =
            std::make_shared<std::vector<std::byte>>(((rowCount - 1) * layout.rowStride) + sizeof(SQL_TIMESTAMP_STRUCT));
        auto const* sourceBytes = reinterpret_cast<std::byte const*>(first);
        for (auto const row: std::views::iota(std::size_t { 0 }, rowCount))
        {
            // Copied bytewise, since the NULL row of an optional holds no value to read through.
            auto value = SQL_TIMESTAMP_STRUCT {};
            std::memcpy(&value, sourceBytes + (row * layout.rowStride), sizeof(value));
            value.fraction -= value.fraction % NanosecondsPerMillisecond;
            std::memcpy(staged->data() + (row * layout.rowStride), &value, sizeof(value));
        }
        cb.PlanPostExecuteCallback([staged]() {});
 
        return SQLBindParameter(stmt,
                                column,
                                SQL_PARAM_INPUT,
                                SQL_C_TIMESTAMP,
                                SQL_TYPE_TIMESTAMP,
                                23,
                                3,
                                static_cast<SQLPOINTER>(staged->data()),
                                sizeof(SQL_TIMESTAMP_STRUCT),
                                indicators);
    }
} // namespace detail
 
template <>
struct LIGHTWEIGHT_API SqlDataBinder<SqlDateTime>
{
    static constexpr auto ColumnType = SqlColumnTypeDefinitions::DateTime {};
 
    static LIGHTWEIGHT_FORCE_INLINE SQLRETURN InputParameter(SQLHSTMT stmt,
                                                             SQLUSMALLINT column,
                                                             SqlDateTime const& value,
                                                             SqlDataBinderCallback& cb) noexcept
    {
        // A row-wise batch binds row 0 through here, so the layout comes from the statement.
        if (detail::IsLegacySqlServerDriver(cb))
            return detail::BindLegacySqlServerDateTimes(
                stmt, column, &value.sqlValue, detail::CurrentParameterArrayLayout(stmt, sizeof(value), 1), cb, nullptr);
 
        return SQLBindParameter(stmt,
                                column,
                                SQL_PARAM_INPUT,
                                SQL_C_TIMESTAMP,
                                SQL_TYPE_TIMESTAMP,
                                27,
                                7,
                                (SQLPOINTER) &value.sqlValue,
                                sizeof(value),
                                nullptr);
    }
 
    /// Binds an array of timestamps as an input parameter for native (row-wise or column-wise) batch
    /// execution. @p values points at the first element; the driver strides by the statement's
    /// SQL_ATTR_PARAM_BIND_TYPE. @p indicators optionally supplies per-row NULL flags.
    static LIGHTWEIGHT_FORCE_INLINE SQLRETURN BatchInputParameter(SQLHSTMT stmt,
                                                                  SQLUSMALLINT column,
                                                                  SqlDateTime const* values,
                                                                  size_t rowCount,
                                                                  SqlDataBinderCallback& cb,
                                                                  SQLLEN* indicators = nullptr) noexcept
    {
        if (detail::IsLegacySqlServerDriver(cb))
        {
            auto const layout = detail::SqlParameterArrayLayout {
                .rowStride = detail::CurrentParameterArrayLayout(stmt, sizeof(*values), rowCount).rowStride,
                .rowCount = rowCount,
            };
            return detail::BindLegacySqlServerDateTimes(stmt, column, &values->sqlValue, layout, cb, indicators);
        }
 
        return SQLBindParameter(stmt,
                                column,
                                SQL_PARAM_INPUT,
                                SQL_C_TIMESTAMP,
                                SQL_TYPE_TIMESTAMP,
                                27,
                                7,
                                (SQLPOINTER) &values->sqlValue,
                                sizeof(*values),
                                indicators);
    }
 
    static LIGHTWEIGHT_FORCE_INLINE SQLRETURN OutputColumn(
        SQLHSTMT stmt, SQLUSMALLINT column, SqlDateTime* result, SQLLEN* indicator, SqlDataBinderCallback& /*cb*/) noexcept
    {
        // TODO: handle indicator to check for NULL values
        *indicator = sizeof(result->sqlValue);
        return SQLBindCol(stmt, column, SQL_C_TYPE_TIMESTAMP, &result->sqlValue, 0, indicator);
    }
 
    static LIGHTWEIGHT_FORCE_INLINE SQLRETURN GetColumn(SQLHSTMT stmt,
                                                        SQLUSMALLINT column,
                                                        SqlDateTime* result,
                                                        SQLLEN* indicator,
                                                        SqlDataBinderCallback const& /*cb*/) noexcept
    {
        return SQLGetData(stmt, column, SQL_C_TYPE_TIMESTAMP, &result->sqlValue, sizeof(result->sqlValue), indicator);
    }
 
    static LIGHTWEIGHT_FORCE_INLINE std::string Inspect(SqlDateTime const& value) noexcept
    {
        return std::format("{}", value);
    }
};
 
template <>
inline constexpr bool SqlIsNativeRowBindableValue<SqlDateTime> = true;
 
} // namespace Lightweight