csv<->binary

# 背景
使用csv格式存储tickdata至少有两个问题：
● 占用磁盘空间较大
● 无法快速提取某段时间数据
设计一种二进制文件格式td文件，来解决上面两个问题。由于将所有数据以二进制格式(int, double等）而不是文本格式(string)存储已经可以减少磁盘空间占用，主要关心的应该是如何从td文件中快速提取某段时间的数据
# 要求
● 请编写一个c++程序，可以
 ○ 将csv文件转换成td文件
 ■ e.g. ``./tdb -c -o db/example.db csv/example.csv``
 ○ 将td文件转换成csv文件
 ■ e.g. ``./tdb -d -o csv/example.csv db/example.db``
 ○ 快速从td文件里取出某一段时间的tickdata数据
 ■ e.g. ``./tdb -s -f "2022-06-01 09:45:32" -t "2022-06-01 10:05:21" db/example.db``

文件数据格式
InstrumentId, UpdateTime, LastPrice, LastSize, BidVolume1, BidPrice1, AskPrice1, AskVolume1, BidVolume2, BidPrice2, AskPrice2, AskVolume2, BidVolume3, BidPrice3, AskPrice3, AskVolume3, BidVolume4, BidPrice4, AskPrice4, AskVolume4, BidVolume5, BidPrice5, AskPrice5, AskVolume5, OpenInterest, TurnOver, TotalVolume, ServerTime
ni2207,20220601 09:00:00.946568,208880.0000000,0,3,208500.0000000,208880.0000000,6,1,208460.0000000,209200.0000000,1,2,208400.0000000,209260.0000000,1,4,208380.0000000,209330.0000000,1,6,208360.0000000,209430.0000000,1,57029.0000000,12166109950,57633,09:00:00.500000

#ifndef TICK_H
#define TICK_H

#include <cstdint>
#include <string>

// Structure to hold a single tick data record
struct Tick {
  int64_t timestamp_ms;
  double last_price;
  int volume;
  double bid_price;
  double ask_price;
};

// Converts a "YYYY-MM-DD HH:MM:SS.sss" string to milliseconds since epoch
int64_t string_to_timestamp_ms(const std::string& time_str);

// Converts milliseconds since epoch to a "YYYY-MM-DD HH:MM:SS.sss" string
std::string timestamp_ms_to_string(int64_t timestamp_ms);

#endif

#ifndef QUERY_H
#define QUERY_H

#include <string>

void query_time_range(const std::string& td_path, const std::string& start_str,
                      const std::string& end_str);

#endif

#include <algorithm>
#include <fstream>
#include <iostream>
#include <vector>

#include "query.h"
#include "tick.h"

void query_time_range(const std::string& td_path, const std::string& start_str,
                      const std::string& end_str) {
  int64_t start_time = string_to_timestamp_ms(start_str);
  int64_t end_time = string_to_timestamp_ms(end_str);

  std::ifstream td_file(td_path, std::ios::binary);
  if (!td_file.is_open()) {
    std::cerr << "Error opening file: " << td_path << std::endl;
    return;
  }

  // Read index metadata from the end
  td_file.seekg(-16, std::ios::end);
  int64_t index_pos, index_size;
  td_file.read(reinterpret_cast<char*>(&index_pos), sizeof(int64_t));
  td_file.read(reinterpret_cast<char*>(&index_size), sizeof(int64_t));

  // Read index into memory
  td_file.seekg(index_pos);
  std::vector<std::pair<int64_t, int64_t>> index(index_size);
  for (int i = 0; i < index_size; ++i) {
    td_file.read(reinterpret_cast<char*>(&index[i].first), sizeof(int64_t));
    td_file.read(reinterpret_cast<char*>(&index[i].second), sizeof(int64_t));
  }

  // Find the starting position using the index and a custom lambda for
  // comparison
  auto it = std::lower_bound(
      index.begin(), index.end(), start_time,
      [](const std::pair<int64_t, int64_t>& entry, int64_t time_value) {
        return entry.first < time_value;
      });

  int64_t seek_pos = 0;
  if (it != index.begin()) {
    // Move to the entry just before the one found by lower_bound
    // to ensure we don't miss records between the index point and our start
    // time.
    seek_pos = (it - 1)->second;
  }

  td_file.seekg(seek_pos);

  // Read records and print if they are within the time range
  Tick tick;
  std::cout << "Timestamp,LastPrice,Volume,BidPrice,AskPrice" << std::endl;
  while (td_file.tellg() < index_pos &&
         td_file.read(reinterpret_cast<char*>(&tick), sizeof(Tick))) {
    if (tick.timestamp_ms >= start_time && tick.timestamp_ms <= end_time) {
      std::cout << timestamp_ms_to_string(tick.timestamp_ms) << ","
                << tick.last_price << "," << tick.volume << ","
                << tick.bid_price << "," << tick.ask_price << std::endl;
    }
    // Optimization: stop reading if we've passed the desired end time
    if (tick.timestamp_ms > end_time) {
      break;
    }
  }
}

#ifndef CONVERTER_H
#define CONVERTER_H

#include <string>

void csv_to_td(const std::string& csv_path, const std::string& td_path);
void td_to_csv(const std::string& td_path, const std::string& csv_path);

#endif

#include <algorithm>
#include <chrono>
#include <filesystem>
#include <fstream>
#include <iomanip>
#include <iostream>
#include <sstream>
#include <stdexcept>
#include <string>
#include <vector>

#include "converter.h"
#include "tick.h"

// Trim leading/trailing whitespace
std::string trim(const std::string& str) {
  const auto strBegin = str.find_first_not_of(" \t");
  if (strBegin == std::string::npos) return "";
  const auto strEnd = str.find_last_not_of(" \t\r\n");
  const auto strRange = strEnd - strBegin + 1;
  return str.substr(strBegin, strRange);
}

// Flexible function to parse both "YYYY-MM-DD..." and "YYYYMMDD..."
int64_t string_to_timestamp_ms(const std::string& s_in) {
  std::string s_trimmed = trim(s_in);
  if (s_trimmed.empty()) return 0;

  // Create a new string and remove dashes to handle both formats
  std::string s_normalized = s_trimmed;
  s_normalized.erase(std::remove(s_normalized.begin(), s_normalized.end(), '-'),
                     s_normalized.end());

  std::tm tm = {};
  int year = 0, month = 0, day = 0, hour = 0, min = 0, sec = 0;
  double subsec = 0.0;

  // Check the normalized string format "YYYYMMDD HH:MM:SS..."
  if (s_normalized.length() < 14 ||
      s_normalized.find(' ') == std::string::npos) {
    std::cerr << "Warning: Unrecognized timestamp format for: \"" << s_in
              << "\"" << std::endl;
    return 0;
  }

  try {
    year = std::stoi(s_normalized.substr(0, 4));
    month = std::stoi(s_normalized.substr(4, 2));
    day = std::stoi(s_normalized.substr(6, 2));
  } catch (const std::exception& e) {
    std::cerr << "Warning: Could not parse date part of timestamp: \"" << s_in
              << "\"" << std::endl;
    return 0;
  }

  // Find the space separating date and time to parse the time part
  size_t space_pos = s_normalized.find(' ');
  sscanf(s_normalized.c_str() + space_pos, " %d:%d:%d%lf", &hour, &min, &sec,
         &subsec);

  tm.tm_year = year - 1900;
  tm.tm_mon = month - 1;
  tm.tm_mday = day;
  tm.tm_hour = hour;
  tm.tm_min = min;
  tm.tm_sec = sec;
  tm.tm_isdst = 0;

#ifdef _WIN32
  time_t time = _mkgmtime(&tm);
#else
  time_t time = timegm(&tm);
#endif

  if (time == -1) {
    return 0;
  }

  auto time_point = std::chrono::system_clock::from_time_t(time);
  time_point += std::chrono::milliseconds(static_cast<int64_t>(subsec * 1000));

  return std::chrono::duration_cast<std::chrono::milliseconds>(
             time_point.time_since_epoch())
      .count();
}

std::string timestamp_ms_to_string(int64_t timestamp_ms) {
  auto time_point = std::chrono::system_clock::time_point(
      std::chrono::milliseconds(timestamp_ms));
  std::time_t t = std::chrono::system_clock::to_time_t(time_point);
  std::tm tm = *gmtime(&t);

  std::stringstream ss;
  ss << std::put_time(&tm, "%Y-%m-%d %H:%M:%S");
  ss << '.' << std::setw(3) << std::setfill('0') << (timestamp_ms % 1000);
  return ss.str();
}

void csv_to_td(const std::string& csv_path, const std::string& td_path) {
  std::ifstream csv_file(csv_path);
  if (!csv_file.is_open()) {
    std::cerr << "Error opening input file: " << csv_path << std::endl;
    return;
  }

  std::filesystem::path output_path(td_path);
  if (output_path.has_parent_path()) {
    std::filesystem::create_directories(output_path.parent_path());
  }

  std::ofstream td_file(td_path, std::ios::binary);
  if (!td_file.is_open()) {
    std::cerr << "Error opening output file: " << td_path << std::endl;
    return;
  }

  std::string line;
  std::getline(csv_file, line);  // Skip the first line, which is the header

  std::vector<std::pair<int64_t, int64_t>> index;
  int64_t current_pos = 0;
  const int INDEX_INTERVAL = 100;
  int record_count = 0;
  long long line_number = 1;

  while (std::getline(csv_file, line)) {
    line_number++;

    // ** THIS IS THE FIX: Explicitly skip any line that looks like a header **
    if (line.empty() || line.rfind("#", 0) == 0) {
      continue;
    }

    std::stringstream ss(line);
    std::vector<std::string> columns;
    std::string item;
    while (std::getline(ss, item, ',')) {
      columns.push_back(item);
    }

    if (columns.size() < 7) {
      continue;
    }

    try {
      Tick tick;

      tick.timestamp_ms = string_to_timestamp_ms(columns[1]);
      tick.last_price =
          trim(columns[2]).empty() ? 0.0 : std::stod(trim(columns[2]));
      tick.volume = trim(columns[3]).empty() ? 0 : std::stoi(trim(columns[3]));
      tick.bid_price =
          trim(columns[5]).empty() ? 0.0 : std::stod(trim(columns[5]));
      tick.ask_price =
          trim(columns[6]).empty() ? 0.0 : std::stod(trim(columns[6]));

      if (tick.timestamp_ms == 0) {
        continue;
      }

      if (record_count % INDEX_INTERVAL == 0) {
        index.push_back({tick.timestamp_ms, current_pos});
      }

      td_file.write(reinterpret_cast<const char*>(&tick), sizeof(Tick));
      current_pos = td_file.tellp();
      record_count++;
    } catch (const std::exception& e) {
      std::cerr << "Warning: Skipping malformed line " << line_number
                << ". Error: " << e.what() << std::endl;
      continue;
    }
  }

  int64_t index_pos = td_file.tellp();
  int64_t index_size = index.size();

  for (const auto& entry : index) {
    td_file.write(reinterpret_cast<const char*>(&entry.first), sizeof(int64_t));
    td_file.write(reinterpret_cast<const char*>(&entry.second),
                  sizeof(int64_t));
  }

  td_file.write(reinterpret_cast<const char*>(&index_pos), sizeof(int64_t));
  td_file.write(reinterpret_cast<const char*>(&index_size), sizeof(int64_t));

  std::cout << "Successfully converted " << csv_path << " to " << td_path
            << ". Total records processed: " << record_count << std::endl;
}

void td_to_csv(const std::string& td_path, const std::string& csv_path) {
  std::ifstream td_file(td_path, std::ios::binary);
  if (!td_file.is_open()) {
    std::cerr << "Error opening input file: " << td_path << std::endl;
    return;
  }

  std::filesystem::path output_path(csv_path);
  if (output_path.has_parent_path()) {
    std::filesystem::create_directories(output_path.parent_path());
  }

  std::ofstream csv_file(csv_path);
  if (!csv_file.is_open()) {
    std::cerr << "Error opening output file: " << csv_path << std::endl;
    return;
  }

  td_file.seekg(0, std::ios::end);
  if (td_file.tellg() < 16) {
    std::cerr << "Error: " << td_path
              << " is not a valid or is an empty .td file." << std::endl;
    return;
  }

  td_file.seekg(-16, std::ios::end);
  int64_t index_pos;
  int64_t index_size;
  td_file.read(reinterpret_cast<char*>(&index_pos), sizeof(int64_t));
  td_file.read(reinterpret_cast<char*>(&index_size), sizeof(int64_t));

  td_file.seekg(0, std::ios::beg);

  csv_file << "Timestamp,LastPrice,Volume,BidPrice,AskPrice\n";

  Tick tick;
  while (td_file.tellg() < index_pos &&
         td_file.read(reinterpret_cast<char*>(&tick), sizeof(Tick))) {
    csv_file << timestamp_ms_to_string(tick.timestamp_ms) << ","
             << tick.last_price << "," << tick.volume << "," << tick.bid_price
             << "," << tick.ask_price << "\n";
  }

  std::cout << "Successfully converted " << td_path << " to " << csv_path
            << std::endl;
}

#include <iostream>
#include <string>
#include <vector>

#include "converter.h"
#include "query.h"

void print_usage() {
  std::cerr << "Usage: \n"
            << " ./tdb -c -o <output.td> <input.csv>\n"
            << " ./tdb -d -o <output.csv> <input.td>\n"
            << " ./tdb -s -f <start_time> -t <end_time> <input.td>\n";
}

int main(int argc, char* argv[]) {
  if (argc < 2) {
    print_usage();
    return 1;
  }

  std::string mode = argv[1];
  if (mode == "-c" && argc == 5 && std::string(argv[2]) == "-o") {
    csv_to_td(argv[4], argv[3]);
  } else if (mode == "-d" && argc == 5 && std::string(argv[2]) == "-o") {
    td_to_csv(argv[4], argv[3]);
  } else if (mode == "-s" && argc == 7 && std::string(argv[2]) == "-f" &&
             std::string(argv[4]) == "-t") {
    query_time_range(argv[6], argv[3], argv[5]);
  } else {
    print_usage();
    return 1;
  }

  return 0;
}

cmake_minimum_required(VERSION 3.10)
project(tdb)

set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

add_executable(tdb src/main.cpp src/converter.cpp src/query.cpp)

install(TARGETS tdb DESTINATION bin)