constexpr auto count=1000;

int main()
{    
    sqlite3* db;
    if(sqlite3_open("test.db", &db)) return -1;
//    sqlite3_exec(db, "create table Test(id integer primary key autoincrement, name text not null, fvalue real not null)", nullptr, nullptr, nullptr);
//    sqlite3_exec(db, "create index fvalue on Test (fvalue)", nullptr, nullptr, nullptr);

    auto start=high_resolution_clock::now();
    sqlite3_exec(db, "begin", nullptr, nullptr, nullptr);
    sqlite3_exec(db, "delete from Test", nullptr, nullptr, nullptr);
    sqlite3_stmt* stmt;
    if(sqlite3_prepare(db, "insert into Test (name, fvalue) values (?, ?)", -1, &stmt, nullptr)==SQLITE_OK){
        for(auto i=0.0; i<2*M_PI; i+=0.000001*count){
            sqlite3_bind_text(stmt, 1, ("sin("+to_string(i)+")").c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_bind_double(stmt, 2, sin(i));
            sqlite3_step(stmt);
            sqlite3_reset(stmt);
        }
        sqlite3_finalize(stmt);
    }
    sqlite3_exec(db, "commit", nullptr, nullptr, nullptr);
    cout<<"delete and insert: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()<<" mcs"<<endl;

    int64_t id;
    string name;
    double fvalue;
    
    start=high_resolution_clock::now();
    if(sqlite3_prepare(db, "select * from Test where fvalue>? order by fvalue limit 10 offset 10", -1, &stmt, nullptr)==SQLITE_OK){
        for(auto i=0.0; i<0.5; i+=0.5/count){
            sqlite3_bind_double(stmt, 1, i);
            while(sqlite3_step(stmt)==SQLITE_ROW){
                id=sqlite3_column_int64(stmt, 0);
                name=(const char*)sqlite3_column_text(stmt, 1);
                fvalue=sqlite3_column_double(stmt, 2);
            }
            sqlite3_reset(stmt);
        }
        sqlite3_finalize(stmt);
    }
    cout<<"prepared select: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()/count<<" mcs"<<endl;
    
    start=high_resolution_clock::now();
    for(auto i=0.0; i<0.5; i+=0.5/count){
        if(sqlite3_prepare(db, ("select * from Test where fvalue>"+to_string(i)+" order by fvalue limit 10 offset 10").c_str(), -1, &stmt, nullptr)==SQLITE_OK){
            while(sqlite3_step(stmt)==SQLITE_ROW){
                id=sqlite3_column_int64(stmt, 0);
                name=(const char*)sqlite3_column_text(stmt, 1);
                fvalue=sqlite3_column_double(stmt, 2);
            }
            sqlite3_finalize(stmt);
        }
    }
    cout<<"select: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()/count<<" mcs"<<endl;
    
    sqlite3_close(db);
    return 0;
}

SQLPP_DECLARE_TABLE((Test),
    (id     , int         , SQLPP_PRIMARY_KEY, SQLPP_AUTO_INCREMENT)
    (name   , varchar(255), SQLPP_NOT_NULL)
    (fvalue, double       , SQLPP_NOT_NULL))
constexpr auto test=Test::Test{};
constexpr auto count=1000;

int main()
{
    sqlite::connection db({"test.db", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE});    

    auto start=high_resolution_clock::now();
    auto tr=start_transaction(db);
    db(remove_from(test).unconditionally());
    auto qi=db.prepare(insert_into(test).set(test.name=parameter(test.name), test.fvalue=parameter(test.fvalue)));
    for(auto i=0.0; i<2*M_PI; i+=0.000001*count){
        qi.params.name="sin("+to_string(i)+")";
        qi.params.fvalue=sin(i);
        db(qi);
    }
    tr.commit();
    cout<<"delete and insert: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()<<" mcs"<<endl;
    
    int64_t id;
    string name;
    double fvalue;
    
    start=high_resolution_clock::now();
    auto q=db.prepare(select(all_of(test)).from(test).where(test.fvalue>parameter(test.fvalue)).order_by(test.fvalue.asc()).limit(10).offset(10));
    for(auto i=0.0; i<0.5; i+=0.5/count){
        q.params.fvalue=i;
        for(const auto& row: db(q)){
            id=row.id;
            name=row.name;
            fvalue=row.fvalue;
        }
    }
    cout<<"prepared select: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()/count<<" mcs"<<endl;
    
    start=high_resolution_clock::now();
    for(auto i=0.0; i<0.5; i+=0.5/count){
        for(const auto& row: db(select(all_of(test)).from(test).where(test.fvalue>i).order_by(test.fvalue.asc()).limit(10).offset(10))){
            id=row.id;
            name=row.name;
            fvalue=row.fvalue;
        }
    }
    cout<<"static select: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()/count<<" mcs"<<endl;
    
    start=high_resolution_clock::now();
    auto qd=dynamic_select(db).columns(all_of(test)).from(test).dynamic_where().order_by(test.fvalue.asc()).limit(10).offset(10);
    for(auto i=0.0; i<0.5; i+=0.5/count){
        auto q=qd;
        if(true) q.where.add(test.fvalue>i);
        for(const auto& row: db(q)){
            id=row.id;
            name=row.name;
            fvalue=row.fvalue;
        }
    }
    cout<<"dynamic select: "<<duration_cast<microseconds>(high_resolution_clock::now()-start).count()/count<<" mcs"<<endl;
}