SwedenCpp Stockholm0x06

x = application code,  _ = time spent in the database

x = application code,  _ = time spent in the database

xxxxx_______________________________________________

x = application code,  _ = time spent in the database

xxxxx_______________________________________________

  xx________________________________________________

x = application code,  _ = time spent in the database

xxxxx_______________________________________________

  xx________________________________________________

  xx_______________

constexpr const char*
mySqlStatement()
{
R"~(
select/insert/update ...

... what ever ...

... where ever ...

;
)~"
}

CREATE TABLE artist(
  id    INTEGER PRIMARY KEY,
  name  TEXT
);
CREATE TABLE track(
  id     INTEGER,
  name   TEXT,
  artist INTEGER ,
  FOREIGN KEY(artist) REFERENCES artist(id)
);

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0)
    UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0)
    UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
     WHERE (x*x + y*y) < 4.0 AND iter<28),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
    FROM m2 GROUP BY cy)
SELECT group_concat(rtrim(t),x'0a') FROM a;

  sqlite3* db = nullptr;
  auto rc = sqlite3_open (name, &db);
  if(rc != SQLITE_OK) {
    std::cerr << "Unable to open database '" << name << "': "
              <<  sqlite3_errmsg (db);
    sqlite3_close (db);
    std::exit(EXIT_FAILURE);
  }

  // do not forgett to close !!!
  sqlite3_close (db);

using database =
  std::unique_ptr<sqlite3, decltype(&sqlite3_close)> ;

database open_database(const char* name)
{
  sqlite3* db = nullptr;
  auto rc = sqlite3_open (name, &db);
  if(rc != SQLITE_OK) {
    std::cerr << "Unable to open database '" << name << "': "
              <<  sqlite3_errmsg (db);
    sqlite3_close (db);
    std::exit(EXIT_FAILURE);
  }
  return database{db, sqlite3_close} ;
}

auto db = open_database(":memory:");

void execute (not_null<sqlite3*> db, const char* sql)
{
  char* errmsg = 0;
  int rc = sqlite3_exec (db.get (), sql, 0, 0, &errmsg);
  if (rc != SQLITE_OK) {
    std::cerr << "Unable to execute '" << sql << "': "
              <<  errmsg ;
    sqlite3_free(errmsg) ;
    std::exit(EXIT_FAILURE);
  }
}

// Note: The not_null is from the MS GSL.

constexpr const char* create_things()
{
  return R"~(BEGIN TRANSACTION ;
  CREATE TABLE things(id INTEGER PRIMARY KEY, name TEXT,value REAL);
  INSERT INTO things VALUES(1,'one', 1.1);
  INSERT INTO things VALUES(2,'two', 2.2);
  COMMIT TRANSACTION ;
  )~";
}

int main()
{
  auto db = open_database(":memory:");
  execute (db.get(), create_things());
  // run your program....
}

struct Transaction
{
  Transaction(not_null<sqlite3*> db) : _db{db}{
    execute(_db, "BEGIN TRANSACTION;") ;
  }
  ~Transaction() {
    if(_db) execute(_db, "ROLLBACK TRANSACTION;") ;
  }
  void commit() {
    if(_db) execute(_db, "COMMIT TRANSACTION;") ;
    _db = nullptr ;
  }
  Transaction (Transaction&&) =  default ;

  Transaction (Transaction&) =  delete ;
  Transaction& operator=(Transaction&) =  delete ;
  Transaction& operator=(Transaction&&) =  delete ;
private:
  sqlite3* _db ;
};

constexpr const char* create_things()
{
  return R"~(
  CREATE TABLE things(id INTEGER PRIMARY KEY, name TEXT,value REAL);
  INSERT INTO things VALUES(1,'one', 1.1);
  INSERT INTO things VALUES(2,'two', 2.2); )~";
}

int main()
{
  auto db = open_database(":memory:");{
   Transaction transaction{db};
   execute (db.get(), create_things());
   transaction.commit();
  }
  // now there is some data
}

-- sqlite syntax

INSERT INTO things VALUES(?,?,?); -- index 1, 2 , 3

-- or with names

INSERT INTO things VALUES(@id,@name,@value);

-- indexes still work

  sqlite3_stmt* stmt = nullptr;
  int rc = sqlite3_prepare_v2 (db,
                              sql.c_str (), sql.length(),
                              &stmt, nullptr);
  if (rc != SQLITE_OK) {
    std::cerr << "Unable to create statement '" << sql << "': "
              <<  sqlite3_errmsg(db.get ());
    std::exit(EXIT_FAILURE);
  }

  sqlite3_finalize(stmt) ;

using statement =
  std::unique_ptr<sqlite3_stmt, decltype(&sqlite3_finalize)> ;

statement create_statement(not_null<sqlite3*> db,
                           const std::string& sql)
{
  sqlite3_stmt* stmt = nullptr;
  int rc = sqlite3_prepare_v2 (db,
                              sql.c_str (), sql.length(),
                              &stmt, nullptr);
  if (rc != SQLITE_OK) {
    std::cerr << "Unable to create statement '" << sql << "': "
              <<  sqlite3_errmsg(db.get ());
    std::exit(EXIT_FAILURE);
  }
  return statement(stmt, sqlite3_finalize);
}

auto stmt = create_statement(db.get(),
  "INSERT INTO things VALUES(@id,@name,@value);");

void parameter(not_null<sqlite3_stmt*> stmt,
               int index, int64_t value){

  if (sqlite3_bind_int64 (stmt, index, value) != SQLITE_OK){
      throw "TODO" ;
  }
}
// void parameter( ... double value)  sqlite3_bind_real

void parameter(not_null<sqlite3_stmt*> stmt,
          int index, const std::string& value) {

  if(sqlite3_bind_text (stmt.get(), index,
                            value.c_str (), value.size (),
                            SQLITE_TRANSIENT) != SQLITE_OK){
      throw "TODO" ;
  }
}
// void parameter( ... blob& value)  sqlite3_bind_blob
// for temporary, SQLITE_TRANSIENT, sqlite get a copy

using stmt_callback = //callback to handle current row
    std::function<bool(not_null<sqlite3_stmt*>)> ;

void run(not_null<sqlite3_stmt*> stmt,
        stmt_callback callback = stmt_callback{})
{
  using reset_guard // JASG: Just another scope guard
      = std::unique_ptr<sqlite3_stmt, decltype (&sqlite3_reset)>;
  auto reset = reset_guard (stmt.get(), &sqlite3_reset);

  auto next_step = [&](int rc){
    if (rc == SQLITE_OK || rc == SQLITE_DONE)
      return false ;
    else if (rc == SQLITE_ROW)
      if(callback) return callback(stmt);
    // else ... some error handling
    return false ;
  };

  while(next_step(sqlite3_step(stmt))) ;
}

bool dump_current_row(not_null<sqlite3_stmt*> stmt)
{
  for (int i = 0 ; i < sqlite3_column_count(stmt); ++i) {

    // get the current field value and print it

    std::cout << "|" ;
  }
  std::cout << "\n" ;
  return true ;
}

  auto columntype = sqlite3_column_type(stmt, i) ;

  if(columntype == SQLITE_NULL) {
    std::cout << "<NULL>" ;
  }
  else if (columntype == SQLITE_INTEGER){
    std::cout << sqlite3_column_int64(stmt, i);
  }
  else if (columntype == SQLITE_FLOAT){
    std::cout << sqlite3_column_double(stmt, i) ;
  }
  else if (columntype == SQLITE_TEXT ){
    auto first = sqlite3_column_text (stmt, i);
    std::size_t s = sqlite3_column_bytes (stmt, i);
    std::cout << "'" << (s > 0 ?
        std::string((const char*)first, s)  : "") << "'";
  }
  else if (columntype == SQLITE_BLOB ){
    std::cout << "<BLO000B>" ;
  }

constexpr const char* create_things()
{
  return R"~(
  CREATE TABLE things(id INTEGER PRIMARY KEY, name TEXT,value REAL);
  INSERT INTO things VALUES(1,'one', 1.1);
  INSERT INTO things VALUES(2,'two', 2.2); )~";
}

int main()
{
  auto db = open_database(":memory:");{
   Transaction transaction{db};
   execute (db.get(), create_things());
   transaction.commit();
  }
  auto stmt = create_statement(db.get(), "SELECT * FROM things;");
  run(stmt.get(), dump_current_row);
}
//
// 1|'one'|1.1|
// 2|'two'|2.2|

auto on_row = [](not_null<sqlite3_stmt*> stmt)
{
  for (int i = 0 ; i < sqlite3_column_count(stmt.get()); ++i) {
    // get the current field value .
  }
  return true ;
}

auto on_row = [](not_null<sqlite3_stmt*> stmt)
{
  for (int i = 0 ; i < sqlite3_column_count(stmt.get()); ++i) {
    // get the current field value ..
  }
  return true ;
}

auto on_row = [](not_null<sqlite3_stmt*> stmt)
{
  for (int i = 0 ; i < sqlite3_column_count(stmt.get()); ++i) {
    // get the current field value ...
  }
  return true ;
}

statement create_things2(not_null<sqlite3*> db) {
  Transaction transaction(db) ;
  execute(db, R"~(CREATE TABLE things
  (id INTEGER PRIMARY KEY, name TEXT,value REAL); )~");

  auto insert_thing = create_statement(db,
        "INSERT INTO things VALUES(@id,@name,@value);");
  // create the additive identity thing
  parameter(insert_thing.get(), 1, int64_t{0}) ;
  parameter(insert_thing.get(), 2, "") ;
  parameter(insert_thing.get(), 3, double{0.0}) ;
  run (insert_thing.get()) ;
  transaction.commit() ;
  // return creator
  return insert_thing ;
}

void somehwere()
{
  auto db = open_database(":memory:");
  auto add_thing =  create_things2(db.get());
// add a second thing
  parameter(add_thing.get(), 1, int64_t{1}) ;
  parameter(add_thing.get(), 2, "first") ;
  parameter(add_thing.get(), 3, "second") ; // Mistake !!
  run(add_thing.get());

}

bool print_thing(not_null<sqlite3_stmt*> stmt) {
  // get the int64
  auto id = [&](){return sqlite3_column_int64(stmt, 0);} ;
  //get the text
  auto name = [&](){ auto first = sqlite3_column_text (stmt, 1);
    std::size_t s = sqlite3_column_bytes (stmt, 1);
    return  s > 0 ? std::string ((const char*)first, s)
                  : std::string{};
  };
  // get the double
  auto value = [&]() {return sqlite3_column_double(stmt, 2);};
  // and process the data
  std::cout << id() << ", " << name() << ", " << value() << std::endl;
  return true ;
}

//  parameter(insert_thing.get(), 1, int64_t{0}) ;
//  parameter(insert_thing.get(), 2, "") ;
//  parameter(insert_thing.get(), 3, double{0.0}) ;

//  parameter(add_thing.get(), 1, int64_t{1}) ;
//  parameter(add_thing.get(), 2, "first") ;
//  parameter(add_thing.get(), 3, "second") ; // Mistake !!

  auto stmt = create_statement(db.get(), "SELECT * FROM things;");

  run(stmt.get(), dump_current_row);
  run (stmt.get(), print_thing);

0|''|0|
1|'first'|'second'| # dump_current_row know the type

0, , 0
1, first, 0 # this might become expensive

0, , 0
1, first, 0 # this might become expensive

auto on_row = [](not_null<sqlite3_stmt*> stmt)
{
  for (int i = 0 ; i < sqlite3_column_count(stmt.get()); ++i) {
    // get the current field value ...
  }
  return true ;
}

#include <iostream>
#include <sl3/database.hpp>
int main()
{
  using namespace sl3;
  Database db(":memory:");
  db.execute("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
  // insert with type insurance
  auto cmd = db.prepare("INSERT INTO tbl(f1, f2, f3) VALUES(?,?,?);",
                     DbValues ({Type::Int,Type::Text, Type::Real})) ;
  //this will work,
  cmd.execute({ {1}, {"one"}, {1.1} } );

  try{
  // this will throw since "2" is a wrong type
      cmd.execute({ {"2"}, {"two"}, {2.1} } );
  } catch (const Error& e) {
      std::cout << e << std::endl;
  }
}

   // ommit types in prepare, do what evet
  //db.prepare("INSERT INTO tbl(f1, f2, f3) VALUES(?,?,?);") ;

  for(auto& row :db.select("SELECT * FROM tbl;"); {
     for (auto& field : row) {
         std::cout << typeName (field.getType()) << "/"
             << typeName (field.getStorageType())
             << ": " << field << ", ";
     }
     std::cout << std::endl;
  }

Variant/Int: 1, Variant/Text: one, Variant/Real: 1.1,
Variant/Text: 2, Variant/Text: two, Variant/Real: 2.1,

   try {
   // SELECT will throw if any types is not as wanted
      Dataset ds = db.select ("SELECT * FROM tbl;",
                               { Type::Int, Type::Text, Type::Real });
      for(auto& row : ds) {
        // here I can trust the type, (could be NULL :-)
        row.at(0).getInt();
        row.at(1).getText();
        row.at(2).getReal();
      }
    }
  catch (const Error& e)
    {
      std::cout << e << std::endl ;
    }