====== SQLite Wrapperklasse (Rückgabe als String) ======

Die hier vorgestellte Klasse kann verwendet werden, um SQLite in C++ als Datenbankobjekt einzusetzen. Die Klasse stellt dabei die Funktionen zum Einfügen, Ändern, Löschen und Suchen von Datensätzen in eine Datenbank zur Verfügung. Wer es genauer wissen will, der sei hier auf die [[dbs:sqlite:libsqlite3:cppwrapper_simple:ref|Referenz]] zur Klasse verwiesen. Um das ganze kompilieren zu können, muss die libsqlite3.a oder libsqlite3.so zum Programm [[dbs:sqlite:libsqlite3:install|gelinkt]] werden. 

  * Funktionen der libsqlite3
  * Klasse String in C++
  * Ausnahmebehandlung in C++
  * Container und Iteratoren aus der STL
  * Aufbau von Klassen in C++

\\ 
Headerdatei der Klasse:

<code cpp>
#include <string>
#include <sqlite3.h>
#include <stdexcept>
#include <vector>
#include <map>
#include <iostream>

class SQLite
{
  public:

    typedef std::map<std::string,std::string> Row;
    typedef std::vector<Row> Result;

    //--------------------------------------------------------------------------
    SQLite( const std::string& dbase)
    {
      checkCall( sqlite3_open(dbase.c_str(), &_db) );
    }

    //--------------------------------------------------------------------------
    ~SQLite()
    {
      sqlite3_close(_db);
    }

    //--------------------------------------------------------------------------
    Result query( const std::string& query )
    {
      //std::cout << query << std::endl;

      sqlite3_stmt *stmt;
      checkCall( sqlite3_prepare_v2(_db, query.c_str(), query.size(), &stmt, 0) );

      int cols = sqlite3_column_count(stmt);
      Result result;

      while( sqlite3_step(stmt) == SQLITE_ROW )
      {
        Row cur_row;

        for( int i = 0; i < cols; ++i )
        {
          if( !sqlite3_column_name(stmt,i) )
            continue;

          std::string name( sqlite3_column_name(stmt,i) );
          if( name.empty() )
            continue;

          std::string value;
          if( sqlite3_column_text(stmt,i) )
            value = reinterpret_cast<const char*>(sqlite3_column_text(stmt,i));

          cur_row[ name ] = value;
        }

        result.push_back(cur_row);
      }

      sqlite3_finalize(stmt);

      return result;
    }

    //--------------------------------------------------------------------------
    void insert( const std::string& table,
                 const Row& values )
    {
      std::string col_names, col_values;

      for( Row::const_iterator col = values.begin();
          col != values.end();
          ++col )
      {
        if( !col_names.empty() )
        {
          col_names  += ",";
          col_values += ",";
        }

        col_names  += "`" + col->first  + "`";
        col_values += "'" + col->second + "'";
      }

      query(  "INSERT INTO " + table
            + " (" + col_names + ") VALUES (" + col_values + ")" );
    }

    //--------------------------------------------------------------------------
    Result select( const std::string& table,
                   const Row& values )
    {
      std::string checks;

      for( Row::const_iterator col = values.begin();
          col != values.end();
          ++col )
      {
        if( !checks.empty() )
          checks += " and ";

        checks += "`" + col->first + "`='" + col->second + "'";
      }

      return query( "SELECT * FROM " + table + " WHERE " + checks );
    }

    //--------------------------------------------------------------------------
    void update( const std::string& table,
                 const Row& values,
                 const std::string& where )
    {
      std::string sets;

      for( Row::const_iterator col = values.begin();
          col != values.end();
          ++col )
      {
        if( !sets.empty() )
          sets += ',';

        sets += "`" + col->first  + "`='" + col->second + "'";
      }

      query(  "UPDATE " + table + " SET " + sets + " WHERE " + where );
    }

    //--------------------------------------------------------------------------
    void deleteFrom( const std::string& table,
                     const std::string& where )
    {
      query(   "DELETE FROM " + table
             + " WHERE " + where );
    }

  private:

    void checkCall( int retval )
    {
      if( retval != SQLITE_OK )
        throw std::runtime_error( sqlite3_errmsg(_db) );
    }
  sqlite3* _db;
};
</code>\\

Und ein Verwendungsbeispiel:
<code cpp>
int main(int argc, char* argv[])
{
  // open the database
  SQLite db("messages.db");

  // create new entry
  SQLite::Row input;
  input["type"        ] = argv[2];
  input["host"        ] = argv[3];
  input["service"     ] = argv[4];
  input["state"       ] = argv[5];
  input["duration"    ] = argv[6];
  input["email"       ] = argv[7];

  std::cout << "Inserting new notification..." << std::endl;
  db.insert("messages", input);

  // print out the database
  SQLite::Result result = db.query("select * from messages");

  for( SQLite::Result::iterator row = result.begin();
       row != result.end();
       ++row )
  {
    std::cout << "-----------------------------------------------------------"
              << "---------------------" << std::endl;
    std::cout << (*row)["id"]      << "|"
              << (*row)["type"]    << "|"
              << (*row)["host"]    << "|"
              << (*row)["service"] << "|"
              << (*row)["state"]   << "|"
              << (*row)["duration"]<< "|"
              << (*row)["inserted"]<< "|"
              << (*row)["message_sent"]
              << std::endl;
  }

  return 0;
}
</code>\\
schema.sql:
<code sql>
DROP TABLE IF EXISTS messages;
CREATE TABLE messages(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  inserted INTEGER NOT NULL DEFAULT 0,
  message_sent INTEGER NOT NULL DEFAULT 0,
  type TEXT,
  host TEXT,
  service TEXT,
  state TEXT,
  duration TEXT,
  email TEXT
);
</code>\\
Datenbank in der Konsole erstellen:
<code>sqlite3 messages.db < schema.sql</code>