プログラミング C++ 関連の技術メモ【SQLite の 活用方法（その6）】

SQLite 技術メモ　： リファクタリング

とりあえず、本日はリファクタリングをして、今後の改造に備える。

ソースコード [SQLiteTest3.cpp]

#include <chrono>
#include "NBCsvData.h"
#include "NBSQLiteAccessor.h"

#define MY_TABLE_NAME "test_table"
#define MY_DB_NAME "./test.db"
#define CSV_DELIMITER L','

std::chrono::system_clock::time_point  _start, _end;
const std::wstring CSV_FILE_NAME_L = L"humanFlow_zenkoku.csv";

static void START_TIMER()
{
    _start = std::chrono::system_clock::now(); // 計始時間
}

static void LAP(unsigned int count)
{
    _end = std::chrono::system_clock::now();
    double elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(_end - _start).count();
    std::cout << "Lap Time " << count << " = " << elapsed << std::endl;
}

static void END_TIMER()
{
    _end = std::chrono::system_clock::now();
    double elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(_end - _start).count();
    std::cout << "Fin Time = " << elapsed << std::endl;
}

int main()
{
    START_TIMER();
    NBCsvData csvData(CSV_FILE_NAME_L);
    LAP(1);
    std::unique_ptr<NBSQLiteAccessor> accessor = std::make_unique<NBSQLiteAccessor>();
    accessor->CreateTable(csvData.titleData);
    LAP(2);
    accessor->InsertTable(csvData.tableData, true);
    END_TIMER();
}

ソースコード [NBCsvData.h]

#pragma once
#include <iostream>
#include <fstream>
#include <string>
#include <vector>
#include <Windows.h>

class NBCsvData {
public:
    std::vector<std::string> titleData;
    std::vector<std::vector<std::string>> tableData;
public:
    NBCsvData(const std::wstring& filename);
    NBCsvData(const NBCsvData& csvData) = delete;
    virtual ~NBCsvData() {}
private:
    std::vector<std::string> split(std::string &originalStr) const;
    std::string WStringToString(std::wstring &oWString) const;
};

ソースコード [NBCsvData.cpp]

#include "NBCsvData.h"

#define CSV_DELIMITER L','

NBCsvData::NBCsvData(const std::wstring& filename)
{
    setlocale(LC_CTYPE, "ja_JP.UTF-8");
    FILE* fpr = nullptr;
    wchar_t tmp[1024];
    std::wstring lineTitle;
    _wfopen_s(&fpr, filename.c_str(), L"r, ccs=UTF-8");
    fgetws(tmp, 1024, fpr);
    lineTitle = tmp;
    std::string cvtTitleData = WStringToString(lineTitle);
    std::vector<std::string> strvec = split(cvtTitleData);
    for (auto title : strvec)
    {
        printf("TITLE = %s\n", title.c_str());
        titleData.push_back(title);
    }

    while (fgetws(tmp, 1024, fpr) != NULL)
    {
        std::vector<std::string> rowData;
        lineTitle = tmp;
        std::string cvtStringData = WStringToString(lineTitle);
        std::vector<std::string> strvec = split(cvtStringData);
        for (auto title : strvec)
        {
            rowData.push_back(title);
        }
        tableData.push_back(rowData);
    }
    fclose(fpr);
}

std::vector<std::string> NBCsvData::split(std::string &originalStr) const
{
    int topOfStr = 0;
    int endOfStr = 0;
    std::vector<std::string> result;

    do {
        endOfStr = (int)originalStr.find_first_of(CSV_DELIMITER, topOfStr);
        if (endOfStr == std::string::npos)
        {
            endOfStr = (int)originalStr.size();
        }
        std::string subStr(originalStr, topOfStr, (endOfStr - topOfStr));
        result.push_back(subStr);
        topOfStr = endOfStr + 1;
    } while (topOfStr < originalStr.size());
    return result;
}

std::string NBCsvData::WStringToString(std::wstring &oWString) const
{
    setlocale(LC_ALL, "Japanese");
    int bufferSize = WideCharToMultiByte(CP_ACP, 0, oWString.c_str(),
        -1, (char *)NULL, 0, NULL, NULL);
    std::unique_ptr<CHAR> cpMultiByte = std::make_unique<CHAR>(bufferSize);
    WideCharToMultiByte(CP_ACP, 0, oWString.c_str(), -1, cpMultiByte.get(),
        bufferSize, NULL, NULL);
    std::string result(cpMultiByte.get(), cpMultiByte.get() + bufferSize - 1);
    return(result);
}

ソースコード [NBSQLiteAccessor.h]

#pragma once
#include <iostream>
#include <string>
#include <vector>
#include <sqlite3.h>

class NBSQLiteAccessor {
public:
    NBSQLiteAccessor();
    NBSQLiteAccessor(const NBSQLiteAccessor& sqlite) = delete;
    virtual ~NBSQLiteAccessor();
    virtual bool CreateTable(std::vector<std::string>& titleData) const;
    virtual bool InsertTable(std::vector<std::vector<std::string>>& tableData, const bool TransactionOpt = false) const;
private:
    sqlite3 *m_db;
};

ソースコード [NBSQLiteAccessor.cpp]

#include "NBSQLiteAccessor.h"

#define MY_TABLE_NAME "test_table"
#define MY_DB_NAME "./test.db"
const std::string SQL_CREATE_TABLE = "CREATE TABLE ";
const std::string SQL_INSERT = "INSERT INTO ";
const std::string SQL_TABLE_NAME = MY_TABLE_NAME;

NBSQLiteAccessor::NBSQLiteAccessor()
{
    // ２回目以降の実行前にファイルを削除しておく。
    std::remove(MY_DB_NAME);

    int ret = sqlite3_open(MY_DB_NAME, &m_db);

    if (ret != SQLITE_OK)
    {
        std::cout << "Failed to open Database File : " << ret << std::endl;
        return;
    }
}

NBSQLiteAccessor::~NBSQLiteAccessor()
{
    sqlite3_close(m_db); 
}

bool NBSQLiteAccessor::CreateTable(std::vector<std::string>& titleData) const
{
    char *errorMessage = nullptr;
    const std::string tableName = MY_TABLE_NAME;

    std::string sql_create = SQL_CREATE_TABLE + tableName + "(";
    for (int i = 0; i < titleData.size(); i++)
    {
        std::string tmp = titleData[i];
        sql_create += "\"" + tmp + "\"";
        sql_create += *1
        {
            sql_insert += ";";
            int ret = sqlite3_exec(m_db, sql_insert.c_str(), NULL, NULL, &errorMessage);
            if (ret != SQLITE_OK)
            {
                std::cout << "Failed to insert data (" << sql_insert.c_str() << ") : " << ret << std::endl;
                std::cout << errorMessage << std::endl;
                sqlite3_close(m_db);
                sqlite3_free(errorMessage);
                return false;
            }
            sql_insert = SQL_INSERT + SQL_TABLE_NAME + " values(";
        }
        else
        {
            sql_insert += ", (";
        }
    }
    if (TransactionOpt)
    {
        sqlite3_exec(m_db, "COMMIT;", 0, NULL, &errorMessage);
    }
    return true;
}

うーむ、無料版のはてなブログは、画像以外のファイルのアップロードは駄目なのか。

*1:i + 1) < (titleData.size())) ? ", " : ");";
    }
    std::cout << sql_create << std::endl;

    int ret = sqlite3_exec(m_db, sql_create.c_str(), NULL, NULL, &errorMessage);
    if (ret != SQLITE_OK)
    {
        std::cout << "Failed to create table (" << tableName.c_str() << ") : " << ret << std::endl;
        std::cout << errorMessage << std::endl;
        sqlite3_close(m_db);
        sqlite3_free(errorMessage);
        return false;
    }
    return true;
}

bool NBSQLiteAccessor::InsertTable(std::vector<std::vector<std::string>>& tableData, const bool TransactionOpt) const
{
    char *errorMessage = nullptr;
    if (TransactionOpt)
    {
        sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, NULL, &errorMessage);
    }
    std::string sql_insert = SQL_INSERT + SQL_TABLE_NAME + " values(";
    for (int i = 0; i < tableData.size(); i++)
    {
        std::vector<std::string> tmpVec = tableData[i];
        for (int j = 0; j < tmpVec.size(); j++)
        {
            std::string tmp = tmpVec[j];
            sql_insert += "\"" + tmp + "\"";
            sql_insert += ((j + 1) < (tmpVec.size())) ? ", " : ")";
        }

        if ((i + 1) % 1000 == 0 || (i + 1) >= tableData.size(