No apparent way to issue PRAGMA commands about sqlite_orm HOT 15 CLOSED

yes, I think this is super! Perhaps some other pramas will come along that work like this btw, might be worth to generalise this somewhat internally. If I can help or test please let me know!

from sqlite_orm.

Hey. Thank you for using the lib. You're right about getting sqlite* object - you cannot get it at random time. You can obtain it only with database opening using on_open member. on_open member is designed for SQLite extensions that require sqlite* pointer - not for raw query. Also there is no raw query interface cause sqlite_orm's goal is to make correct queries by using C++ features with no raw queries.
The best way to achieve what you want is to add a public function for synchronous pragma just like user_version function. Something like this:

    void synchronous(int value) {
            std::shared_ptr<internal::database_connection> connection;
            sqlite3 *db;
            if(!this->currentTransaction){
                connection = std::make_shared<internal::database_connection>(this->filename);
                db = connection->get_db();
                this->on_open_internal(db);
            }else{
                db = this->currentTransaction->get_db();
            }
            std::stringstream ss;
            ss << "PRAGMA synchronous = " << value;
            auto query = ss.str();
            auto rc = sqlite3_exec(db, query.c_str(), nullptr, nullptr, nullptr);
            if(rc != SQLITE_OK) {
                auto msg = sqlite3_errmsg(db);
                throw std::runtime_error(msg);
            }
        }

    int synchronous() {
            std::shared_ptr<internal::database_connection> connection;
            sqlite3 *db;
            if(!this->currentTransaction){
                connection = std::make_shared<internal::database_connection>(this->filename);
                db = connection->get_db();
                this->on_open_internal(db);
            }else{
                db = this->currentTransaction->get_db();
            }
            std::string query = "PRAGMA synchronous";
            int res = -1;
            auto rc = sqlite3_exec(db,
                                   query.c_str(),
                                   [](void *data, int argc, char **argv,char **) -> int {
                                       auto &res = *(int*)data;
                                       if(argc){
                                           res = row_extrator<int>().extract(argv[0]);
                                       }
                                       return 0;
                                   }, &res, nullptr);
            if(rc != SQLITE_OK) {
                auto msg = sqlite3_errmsg(db);
                throw std::runtime_error(msg);
            }
            return res;
        }

and use it like this:

storage.synchronous(0);

Only one thing exists I don't know - do we need to call this function every time after database is opened or once per file?

from sqlite_orm.

@ahupowerdns you there?

from sqlite_orm.

I see that PRAGMA synchronous sets value only per connection. So it is gonna be like this: you call storage. synchronous(0); (or any other value) and storage will remember this value and will set it every time once connection is opened. Getter storage.synchronous(); will return value from database. Is it ok?

from sqlite_orm.

Hey @ahupowerdns I got an idea. I'll add pragma member to a storage_t class with user_version and synchronous functions. It can be accessed like this:

storage.pragma.user_version();    //  getter
storage.pragma.user_version(int);    //  setter
storage.pragma.synchronous();    //  getter
storage.pragma.synchronous(int);    //  setter

Also other pragmas will be added to pragma member's class later.

from sqlite_orm.

@ahupowerdns please check out the latest commit. I need your confirmation that the issue is closed. Also take a look at the way pragmas implemented now. Adding new pragma is easy but please don't forget to pull request it to me. Thanks

from sqlite_orm.

I just got an idea. It will be even more comfortable for library users to use another syntax:

storage.pragma.user_version = 1;   // same as storage.pragma.user_version(1); now
int version = storage.pragma.user_version;  // same as storage.pragma.user_version(); now

and same way with synchronous. The benefits are:

1. syntax looks like sqlite even more
2. ability for adding hint functions just like this:

storage.pragma.synchronous.off();   // same as storage.pragma.synchronous = 0;
storage.pragma.synchronous.normal();  // same as storage.pragma.synchronous = 1;
//etc..

What do you think?

from sqlite_orm.

I would definitely not do the assignment syntax. It hides the fact that "things are happening". Actual functions make that very clear, and that is more "the C++ way". I'll check your commit in production now!

from sqlite_orm.

@ahupowerdns you're right. Let's leave it in current way

from sqlite_orm.

Ok, I tried this:

    storage.pragma.synchronous(0);

And it creates an infinite recursion - pragma_t::set_synchronous wants to create a connection, which in turn triggers another call to set_pragma() etc. Trying to fix it here.

from sqlite_orm.

This is weird. I've implemented usage in tests and it worked fine. Please provide your total code. Thanks

from sqlite_orm.

#include <sqlite_orm.h>
struct Query
{
  std::string src_ip;
  uint16_t src_port;
  uint16_t txn_id;
  uint32_t tv_sec;
  uint32_t tv_usec;
  std::string name;
  uint16_t type;
};

int main(int argc, char** argv)
{
  using namespace sqlite_orm;

   auto storage = make_storage("index.sqlite",
                              make_table("queries",
                                         make_column("tv_sec", &Query::tv_sec),
                                         make_column("tv_usec", &Query::tv_usec),
                                         make_column("name", &Query::name),
                                         make_column("type", &Query::type),
                                         make_column("src_ip", &Query::src_ip),
                                         make_column("src_port", &Query::src_port),
                                         make_column("txn_id", &Query::txn_id))
                              );
  storage.sync_schema();

  storage.pragma.synchronous(0);
  storage.remove_all<Query>();
}

from sqlite_orm.

@ahupowerdns nice example. I've copied this code to synchronous example if you don't mind. Also I've fixed recursive call. Please check it out

from sqlite_orm.

@ahupowerdns if everything is ok please close the issue.

from sqlite_orm.

works as intended now, thanks!

from sqlite_orm.