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

SQLite C++ プログラム
SQLite 技術メモ　： SELECT Where句による条件実装

Select用関数を前回は実装していきましたが、条件も無しに、全部大量に検索されてしまいましたね。では次は、条件を付けてみましょう。とりあえず、北海道のデータだけを引っ張って来るように考えてみましょう。

まず、SQL文を考えないといけないですね。条件を指定してSELECTする場合用いるのは、WHERE句です。

SELECT * FROM [テーブル名] where [条件文]

まあとにかく具体例を考えていきましょう。条件文はどのようになるでしょうか？

えーと、件名を出している列名は・・・なんでしたっけ？CSVファイルを確認すると、「都道府県」ですね。なので条件文は

"都道府県" = '北海道'

というような感じになるでしょうか。日本語を扱う場合は、列名にダブルクォーテーションで囲んでいることに注意してください。

さて、WHERE句付きのSELECT文を実装します。すでに、NBSQLiteAccessor::Select()が実装されていますので、引数付きSelect文をオーバーロードしましょう。

最終的には、文字列を引数で受けて、そのWhere句を引数指定できるようにしますが、まずは本当に、これで北海道だけとれるか試してみましょう。

オーバーロードして実装したselect関数だけまずは掲載してみます。赤字の部分を見てください。

 

std::vector<std::vector<std::string>> NBSQLiteAccessor::Select(std::string& whereCondition) const
{
    std::vector<std::vector<std::string>> tableData;
    char *errorMessage = nullptr;
    auto select = "SELECT * FROM " + SQL_TABLE_NAME + " WHERE \"都道府県\" = \'北海道\';";
    std::cout << select << std::endl;
    auto ret = sqlite3_exec(m_db, select.c_str(), callback, &tableData, &errorMessage);
    if (ret != SQLITE_OK)
    {
        std::cout << "Failed to insert data : " << errorMessage << std::endl;
    }
    return tableData;
}

 

そして、以下が実行結果です。素晴らしい！北海道だけみごとに検索できました。

さて、続いて、ちょっと検索にかかる時間を見てみたいので、結果表示を一度コメントアウトして、時間を見てみましょう。

そして以下が結果です。select実行の区間を見てみると、14msecとなっています。まあ、遅いっていうほどではないですね。

 

SELECTの高速化を狙う場合は、やはりINDEXの作成が王道でしょうか？しかし、そもそもで14msecしかかかっていないので、効果はどうなるでしょう。

INDEXを作るには、対象のテーブルに対して、INDEXをはる列を指定してCREATE INDEXを実行します

CREATE INDEX [INDEX名] ON [テーブル名]([カラム名],[カラム名],...);

というような感じです。試してみましょう。

あとで完全なソースコードは掲載するとして、以下のような感じにとりあえず、CreateIndex関数を作ってみました。

ソースコード [NBSQLiteAccessor.cpp]

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

    std::string sql_create = SQL_CREATE_INDEX + SQL_INDEX_NAME + "ON " + SQL_TABLE_NAME + "(\"都道府県\");";
    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 << errorMessage << std::endl;
        sqlite3_close(m_db);
        sqlite3_free(errorMessage);
        return false;
    }
    return true;
}

 

Lap Time3 ～ Lap Time 4 が、Create Indexを実行しています。




で、Lap Time 4～ Lap Time 5がSelectですが・・・。10msecうーん。早くなったのか・・・な？何度が繰り返しましたが、ほぼ10msecで完了しますので、一応ちょっとだけ、早くなっていそうです。

しかしです。CREATE INDEX の実行時間が、46msec で結果的にはこっちの方が大きく足を引っ張ります。というわけで、CREATE INDEXは諸刃の件だということは覚えておきましょう。

ちょっと気になるのが、今回はInsertが全て終わった後にCreateIndexを呼び出しましたが、Insert する前だとどうなるでしょう？InsertごとにIndexが作成されるので、同様に遅くなるはずではあります。

 以下では、Lap Time2 ～ 3がCreate Index, Lap Time 3 ～ 4 がInsert処理, Lap Time 4 ～ 5 がSelectになる。Create Indexは、8msecと早くなりましたね。データが無いためです。そして、Insertが遅くなりました。最後のselectは11msecと同等です。今回程度の規模のテーブルではあまりINDEXをはっても、意味がなさそうです。

最後に、ソースコードを綺麗にしていきます。

本当はソースコードをそのままアップロードしたいところですが、無料版だとできないみたいなので、ソースコードをそのままテキストで掲載。

 

＜ヘッダーファイル＞

  • NBCsvData.h
  • NBSQLiteAccessor.h

＜ソースファイルファイル＞

  • NBCsvData.cpp
  • NBSQLiteAccessor.cpp
  • SQLiteTest3.cpp
ソースコード [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;
    std::string trim(const std::string& string) const;
};

 

ソースコード [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 InsertValues(std::vector<std::vector<std::string>>& tableData, const bool TransactionOpt = false) const;
    virtual bool CreateIndeex(std::vector<std::string>& titleData, bool exec = false) const;
    virtual std::vector<std::vector<std::string>> Select() const;
    virtual std::vector<std::vector<std::string>> Select(std::string& whereCondition) const;
private:
    sqlite3 *m_db;
};

 

 

 

ソースコード [NBCsvData.cpp]

#include "NBCsvData.h"

#define CSV_DELIMITER L','
const std::string TRIM_CHAR_SET = " \t\v\r\n";

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::string NBCsvData::trim(const std::string& strData) const
{
    std::string result;
    std::string::size_type left = strData.find_first_not_of(TRIM_CHAR_SET);
    std::string::size_type right = strData.find_last_not_of(TRIM_CHAR_SET);
    result = (left != std::string::npos) ? strData.substr(left, right - left + 1) : "";
    return result;
}

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(trim(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.cpp]

#include "NBSQLiteAccessor.h"

#define MY_TABLE_NAME "test_table "
#define MY_DB_NAME "./test.db"
#define MY_INDEX_NAME "test_index "
const std::string SQL_CREATE_TABLE = "CREATE TABLE ";
const std::string SQL_CREATE_INDEX = "CREATE INDEX ";

const std::string SQL_INSERT = "INSERT INTO ";
const std::string SQL_WHERE = "WHERE ";
const std::string SQL_TABLE_NAME = MY_TABLE_NAME;
const std::string SQL_INDEX_NAME = MY_INDEX_NAME;

static int callback(void *tableData, int argc, char **argv, char **columnName) 
{
    std::vector<std::string> row;
    for (int i = 0; i < argc; i++)
    {
        std::string tmpData = argv[i] ? argv[i] : "*****";
        row.push_back(tmpData);
    }
    *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;
}

bool NBSQLiteAccessor::CreateIndeex(std::vector<std::string>& titleData, bool exec) const
{
    char *errorMessage = nullptr;
    std::string sql_create = SQL_CREATE_INDEX + SQL_INDEX_NAME + "ON " + SQL_TABLE_NAME + "(";
    for (int i = 0; i < titleData.size(); i++)
    {
        std::string tmp = titleData[i];
        sql_create += "\"" + tmp + "\"";
        sql_create += *2;
            if *3
            {
                printf(" | ");
            }
        }
        printf("\n");
    }
}

int main()
{
    int exec_number = 1;
    START_TIMER();
    NBCsvData csvData(CSV_FILE_NAME_L);
    LAP(exec_number++, KEYWORD_CREATE_TABLE);
    std::unique_ptr<NBSQLiteAccessor> accessor = std::make_unique<NBSQLiteAccessor>();
    accessor->CreateTable(csvData.titleData);

    LAP(exec_number++, KEYWORD_INSERT_DATA);
    accessor->CreateIndeex(csvData.titleData, true);

    LAP(exec_number++, KEYWORD_CREATE_INDEX);
    accessor->InsertValues(csvData.tableData, true);

    LAP(exec_number++, KEYWORD_SELECT);
    std::string condition = "\"都道府県\" = \'北海道\';";
    std::vector<std::vector<std::string>> tableData = accessor->Select(condition);

    LAP(exec_number++, KEYWORD_FINISH);
    PRINT_RESULT(tableData, true);

    END_TIMER();
}

 

ちなみに、ソリューションは以下のような感じ。

 

 

 

 

*1:std::vector<std::vector<std::string>>*)tableData)->push_back(row);
    return SQLITE_OK;
}

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 += ((i + 1) < (titleData.size())) ? ", " : ");";
    }

    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::InsertValues(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(

*2:i + 1) < (titleData.size())) ? ", " : ");";
    }

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

std::vector<std::vector<std::string>> NBSQLiteAccessor::Select() const
{
    std::vector<std::vector<std::string>> tableData;
    char *errorMessage = nullptr;
    auto select = "SELECT * FROM " + SQL_TABLE_NAME;
    auto ret = sqlite3_exec(m_db, select.c_str(), callback, &tableData, &errorMessage);
    if (ret != SQLITE_OK)
    {
        std::cout << "Failed to insert data : " << errorMessage << std::endl;
    }
    return tableData;
}

std::vector<std::vector<std::string>> NBSQLiteAccessor::Select(std::string& whereCondition) const
{
    std::vector<std::vector<std::string>> tableData;
    char *errorMessage = nullptr;
    auto select = "SELECT * FROM " + SQL_TABLE_NAME + SQL_WHERE + whereCondition;
    auto ret = sqlite3_exec(m_db, select.c_str(), callback, &tableData, &errorMessage);
    if (ret != SQLITE_OK)
    {
        std::cout << "Failed to insert data : " << errorMessage << std::endl;
    }
    return tableData;
}

 

 

ソースコード [SQLiteTest3.cpp]

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

std::chrono::system_clock::time_point  _start, _end;
const std::wstring CSV_FILE_NAME_L        = L"humanFlow_zenkoku.csv";
const std::string KEYWORD_CREATE_TABLE    = "Create Table";
const std::string KEYWORD_INSERT_DATA    = "Insert Data";
const std::string KEYWORD_CREATE_INDEX    = "Create Index";
const std::string KEYWORD_SELECT        = "Select";
const std::string KEYWORD_FINISH        = "Finish";

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

static void LAP(unsigned int count, const std::string& keyword)
{
    _end = std::chrono::system_clock::now();
    double elapsed = (double)std::chrono::duration_cast<std::chrono::milliseconds>(_end - _start).count();
    std::cout << "Lap Time [" << keyword << "]" << 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;
}

static void PRINT_RESULT(std::vector<std::vector<std::string>>& tableData, bool exec = false)
{
    if (!exec) return;

    for (auto rowData : tableData)
    {
        printf("DATA = ");
        for (int i = 0; i < rowData.size(); i++)
        {
            auto data = rowData[i];
            printf("%s", data.c_str(

*3:i + 1) < rowData.size(