Anwendungsbeispiel auf neuen Quellcode anpassen

Wer diese Klasse lediglich einsetzen will, der sollte mit der Referenz fortfahren. Diese Klasse setzt neben dem normalen Sprachumfang von C++ noch folgende Elemente ein:

• Funktionen der libsqlite3
• Ausnahmebehandlung in C++
• Container und Iteratoren aus der STL
• Aufbau von Klassen in C++
• Funktionsüberladung
• Typüberprüfung
• Boost::Variant
• Templates

• Tabellen erzeugen
• Spalten zu einer vorhanden Tabelle hinzuzufügen
• Index erstellen und löschen
• Daten einzufügen
• Daten ändern
• Daten löschen
• Daten im gespeicherten Datentyp auszulesen um sie direkt im Programm weiterzuverarbeiten

• BLOBs verarbeiten
• jede Möglichkeit eines SQL-Befehls als eigene Funktion implementieren
• etwas anderes als Tabellen aus einer Datenbank zurückliefern

#ifndef SQLITEWRAPPER_HPP_INCLUDED
#define SQLITEWRAPPER_HPP_INCLUDED
 
#include <sqlite3.h>
#include <string>
#include <map>
#include <vector>
#include <boost/variant.hpp>
#include <stdexcept>
#include <iostream>
 
enum result_code
{
    WRAPPER_OK,           //function ok
    WRAPPER_ERROR,         // Error found in check-function
    DATATYPE_NOT_HANDLED, // BLOBS can't be handelt with this wrapper
    DATATYPE_UNKNOWN,     // Unknown Datatype given
    INTEGER,              // Value = interger
    DOUBLE,               // Value = double
    STRING,               // Value = string
};
 
class sqlite
{
/***used datatypes***/
public:
    typedef boost::variant<double,int,std::string>Value;
    typedef std::map<std::string,Value> Row;
    typedef std::vector<Row> Result;
    typedef std::map<std::string,std::string> tablefields;
    typedef std::vector<std::string> selectfields;
 
/***iterators ***/
    typedef Row::const_iterator Row_it;
 
private:
    std::string dbs_path;
    sqlite3* db_handle;
 
/* This class can be called in two ways:
    sqlite database;
    sqlite database("path/database.db");
 
    First Option only exists in memory. When the database is closed,
    the data inside ist lost.
    Second Option connect to a database on Harddisk. If such a database
    ist not existing, it will be created.                                 */
public:
/***constructors***/
 
    sqlite(std::ofstream &log_c) : dbs_path(":memory:") // no Path with logstream
    {
                check(sqlite3_open(dbs_path.c_str(),&db_handle));
    }
 
    sqlite(std::string path,std::ofstream &log_c) : dbs_path(path)// Path with logstream
    {
        check(sqlite3_open(dbs_path.c_str(),&db_handle));
    }
/***    destructor                                                         ***/
    ~sqlite()  // Close Databaseconection
    {
        sqlite3_close(db_handle);
    }
 
/***    public memberfunctions                                             ***/
/* This function can execute every SQL command, but the result can only
   handle tables. It's also possible to execute more as one SQL-Command.
   The commands must be seperated by ";". Please note, that the result
   will be overwriten if a following command returns a result.
   database.sql_exec("SELECT * FROM table;");               */
    Result sql_exec(const std::string &sql_com);
 
 
// DDL Section
/* create_table functions can be used to create a table in database:
   database.create_table("tablename",tablefields);                           */
    void create_table(const std::string &name,
                      const sqlite::tablefields &table);
 
/* create_index function create a index to a column in a table:
   database.create_index("tablename","indexname","column_which_indexed");    */
    void create_index(const std::string &name,
                      const std::string &index_name,
                      const std::string &column_name);
 
/* alter_table function can be used to add one or more columns in table
   database.alter_table("tablename",tablefield);                             */
    void alter_table (const std::string &name,
                      const sqlite::tablefields &table);
 
 
/* these function is used to delete a table from database
   database.drop_table("tablename");                                         */
    void drop_table  (const std::string &name);
 
/* drop_index function can be used to delete a index
   database.drop_index("indexname");                                         */
    void drop_index  (const std::string &in_name);
 
//DML Section
 
/* insert function add values into the table:
    database.insert("tablename",values);                                      */
    void insert(const std::string &name,
                const sqlite::Row &insert_values);
 
 
/* update can be used to change values in table.
   database.update("tablename", values);
   database.update("tablename", values,"column=10));                              */
    void update(const std::string &name,
                const sqlite::Row &values);
 
    void update(const std::string &name,
                const sqlite::Row &values,
                const std::string &where);
 
/* delete can be used to delete a row in the table
   database.del("tablename","column=10");                                          */
    void del(const std::string &name,
             const std::string &where);
 
 /* selects can be used to get some data out of a table:
   database.select("tablename");
   database.select("tablename","column=10");
   database.select("tablename",columns);
   database.select("tablename",columns,where);                               */
 
    Result select(const std::string &name);
 
    Result select(const std::string &name,
                  const std::string &where);
 
    Result select(const std::string &name,
                  const sqlite::selectfields &column);
 
    Result select(const std::string &name,
                  const sqlite::selectfields &column,
                  const std::string &where);
 
//Get_Data Section:
/* This template can be used to get the data out of the Rows
   NOTE: you can write a Row direct into a strem by the iterator,
   but if you want to fill the data in variables you must use one of
   this functions.
   This Template can be used to convert a Value type to int, double
   and string: database.return_values<double>(Value);
*/
 
    template<class T> T return_values(const sqlite::Value &value)
    {
        return boost::get<T>(value);
    }
 
  /* this function return one of the following constraints:
    INTEGER, DOUBLE, STRING*/
    int get_type(const sqlite::Value &value);
 
private:
    int check(int ret_value);
    std::string change_value(sqlite::Value input);
};
#endif // SQLITEWRAPPER_HPP_INCLUDED

#include "sqlitewrapper.hpp"
#include <iostream>
#include <sqlite3.h>
#include <cstring>
#include <stdexcept>
#include <typeinfo>
#include <sstream>
 
/*** Function to handle Errors***/
//-----------------------------------------------------------------------------
int sqlite::check(int ret_value)
{
    switch(ret_value)
    {
        case DATATYPE_NOT_HANDLED : throw std::runtime_error(
                                      "Wrapper Error: Datatype not handled!");
                                    break;
 
        case DATATYPE_UNKNOWN     : throw std::runtime_error(
                                          "Wrapper Error: Datatype unknown!");
                                    break;
 
        case SQLITE_OK            : return WRAPPER_OK;
                                    break;
        default                   : std::cerr << "SQL Error: ";
                                    throw std::runtime_error(
                                               sqlite3_errmsg(db_handle));
    }
    return WRAPPER_ERROR;
}
std::string sqlite::change_value(sqlite::Value input)
{
    std::string return_value,temp;
    std::stringstream stream;
 
    const std::type_info &ti = input.type();
 
    if (ti == typeid(std::string))
    return_value = "'" + boost::get<std::string>(input) + "'";
 
    else if (ti == typeid(int))
    {
        stream << boost::get<int>(input);
        stream >> temp;
        return_value = temp;
        temp.clear();
        stream.clear();
    }
 
    else if (ti == typeid(double))
    {
        stream << boost::get<double>(input);
        stream >> temp;
        return_value = temp;
        temp.clear();
        stream.clear();
    }
    return return_value;
}
//-----------------------------------------------------------------------------
/*** Function to execute every SQL-Command ***/
sqlite::Result sqlite::sql_exec(const std::string &sql_com)
{
    sqlite::Result ret_table;
    sqlite3_stmt* stmt;
    const char* ptr_com = sql_com.c_str();
    int size = sql_com.size();
    const char* next_sql_com = NULL;
    sqlite::Row cur_row;
 
    while(ptr_com != NULL)
    {
 
    check(sqlite3_prepare_v2(db_handle,ptr_com,size,&stmt,&next_sql_com));
 
    if (strlen(next_sql_com) != 0)
       ptr_com = next_sql_com;
    else ptr_com = NULL;
 
    int cols = sqlite3_column_count(stmt);
 
       while (sqlite3_step(stmt) == SQLITE_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;
 
                sqlite::Value value;
 
 
                switch(sqlite3_column_type(stmt,i))
                {
                    case SQLITE_INTEGER: value = sqlite3_column_int(stmt,i);
                                          break;
                    case SQLITE_FLOAT  : value = sqlite3_column_double(stmt,i);
                                          break;
                    case SQLITE_TEXT   : value = reinterpret_cast<const char*>
                                                 (sqlite3_column_text(stmt,i));
                                          break;
                    case SQLITE_BLOB   : check(DATATYPE_NOT_HANDLED);
                                          break;
                    case SQLITE_NULL   : value = "";
                                          break;
                    default            : check(DATATYPE_UNKNOWN);
                }
            cur_row[ name ] = value;
            }
        ret_table.push_back(cur_row);
        }
    sqlite3_finalize(stmt);
    }
    return ret_table;
}
//DDL Section
//-----------------------------------------------------------------------------
/*** Function to create tables in Database ***/
void sqlite::create_table(const std::string &name,
                          const sqlite::tablefields &table)
{
    std::string fields;
 
    for(sqlite::tablefields::const_iterator col = table.begin();
        col != table.end(); ++col)
    {
        fields += col->first + " ";
        fields += col->second + ",";
    }
    fields.erase(fields.find_last_of(","),1);
    sqlite::sql_exec("CREATE TABLE " + name + "(" + fields + ");");
}
 
//-----------------------------------------------------------------------------
 
void sqlite::create_index(const std::string &name,
                          const std::string &index_name,
                          const std::string &column_name)
{
    sqlite::sql_exec("CREATE INDEX " + index_name + " ON " +
                      name + "(" + column_name + ");");
}
 
//-----------------------------------------------------------------------------
 
void sqlite::alter_table(const std::string &name,
                         const sqlite::tablefields &table)
{
    std::string tablefield;
    for (sqlite::tablefields::const_iterator col = table.begin();
         col != table.end(); col++)
    {
        tablefield = col->first + " ";
        tablefield += col->second;
 
        sqlite::sql_exec("ALTER TABLE " + name + " ADD " + tablefield + ";");
    }
 
}
 
//-----------------------------------------------------------------------------
 
void sqlite::drop_table(const std::string &name)
{
    sqlite::sql_exec("DROP TABLE " + name + ";");
}
 
//-----------------------------------------------------------------------------
 
void sqlite::drop_index  (const std::string &in_name)
{
    sqlite::sql_exec("DROP INDEX " + in_name + ";");
}
 
//-----------------------------------------------------------------------------
 
//DML Section
void sqlite::insert(const std::string &name,
                     const sqlite::Row &insert_values)
 {
     std::string tablelines,values;
 
     for(sqlite::Row_it col = insert_values.begin();
        col != insert_values.end(); ++col)
    {
        tablelines += col->first + ",";
        values += sqlite::change_value(col->second) + ",";
 
    }
    tablelines.erase(tablelines.find_last_of(","),1);
    values.erase(values.find_last_of(","),1);
 
    sqlite::sql_exec("INSERT INTO " + name + "(" + tablelines + ") VALUES ("
                 + values + ");");
}
 
//-----------------------------------------------------------------------------
 
void sqlite::update(const std::string &name,
                    const sqlite::Row &values)
{
   std::string sets,checks;
   for (sqlite::Row_it col = values.begin(); col != values.end(); col++)
   {
       sets = col->first + "=" + sqlite::change_value(col->second) + " AND ";
   }
   int pos = sets.find_last_of("AND");
   pos -= 3;
   sets.erase(pos,5);
   sqlite::sql_exec("UPDATE " + name + " SET " + sets + ";");
 
}
 
void sqlite::update(const std::string &name,
                    const sqlite::Row &values,
                    const std::string &where)
{
   std::string sets;
   for (sqlite::Row_it col = values.begin(); col != values.end();col++)
   {
       sets += col->first + "=" + sqlite::change_value(col->second) + " AND ";
   }
   int pos = sets.find_last_of("AND");
   pos -= 3;
   sets.erase(pos,5);
   sqlite::sql_exec("UPDATE " + name + " SET " + sets +
                    " WHERE " + where + ";");
 
}
 
//-----------------------------------------------------------------------------
 
void sqlite::del(const std::string &name,
                 const std::string &where)
{
    sqlite::sql_exec("DELETE FROM " + name + " WHERE " + where + ";");
}
 
//-----------------------------------------------------------------------------
 
sqlite::Result sqlite::select(const std::string &name)
{
    return sqlite::sql_exec("SELECT * FROM " + name + ";");
}
sqlite::Result sqlite::select(const std::string &name,
                              const std::string &where)
{
    return sqlite::sql_exec("SELECT * FROM " + name + " WHERE "
                               + where + ";");
}
sqlite::Result sqlite::select(const std::string &name,
                              const sqlite::selectfields &column)
{
    std::string columns;
 
    for (unsigned int i = 0; i != column.size();i++)
    {
        if(!column[i].empty())
            columns += column[i] + ",";
    }
    columns.erase(columns.find_last_of(","),1);
 
    return sqlite::sql_exec("SELECT " + columns + " FROM " + name + ";");
}
sqlite::Result sqlite::select(const std::string &name,
                              const sqlite::selectfields &column,
                              const std::string &where)
{
    std::string columns;
 
    for (unsigned int i = 0; i != column.size();i++)
    {
        if(!column[i].empty())
            columns += column[i] + ",";
    }
    columns.erase(columns.find_last_of(","),1);
 
    return sqlite::sql_exec("SELECT " + columns + " FROM " + name + " WHERE "
                               + where + ";");
}
 
//-----------------------------------------------------------------------------
 
int sqlite::get_type(const sqlite::Value &value)
{
    const std::type_info &ti = value.type();
 
    if (ti == typeid(std::string))
    {
        return STRING;
    }
    else if (ti == typeid(int))
    {
        return INTEGER;
    }
    else if (ti == typeid(double))
    {
        return DOUBLE;
    }
    else return DATATYPE_UNKNOWN;
}
 
 
 

ACHTUNG: Dieses Anwendungsbeispiel ist derzeit mit der obigen Klasse inkompatibel!!

#include <iostream>
#include <string>
#include "sqlitewrapper.hpp"
using namespace std;
 
void print_out(sqlite::Result result)
{
       sqlite::Row printer;
   for(sqlite::Result::const_iterator iter = result.begin();iter!= result.end();iter++)
   {
        printer = *iter;
        for(sqlite::Row_it row_iter=printer.begin(); row_iter != printer.end();row_iter++)
        {
            cout << row_iter->first << ": " << row_iter->second << endl;
        }
        cout << "-----------------------------------------------------------" << endl;
   }
}
 
int main()
{
   //Connect to database:
   sqlite dbs("database.db");
 
   //Delete Table if exists
   dbs.drop_table("IF EXISTS Tabelle");
 
   //Define Table:
   sqlite::tablefields fields;
 
   fields["ID"     ] = "integer PRIMARY KEY AUTOINCREMENT";
   fields["Name"   ] = "varchar(30) NOT NULL";
   fields["Vorname"] = "varchar(30) NOT NULL";
   fields["Geb_Dat"] = "date NOT NULL";
 
   dbs.create_table("Tabelle",fields);
 
   //create index on column Name:
   dbs.create_index("Tabelle","Name_index","Name");
 
   //Add column in Table:
   sqlite::tablefields add_fields;
   add_fields["Ort"    ] = "varchar(30)";
   add_fields["Strasse"] = "varchar(30)";
   add_fields["HausNr" ] = "integer";
   add_fields["Gehalt" ] = "numeric(5,2)";
 
   dbs.alter_table("Tabelle",add_fields);
 
   //Insert a Row in the table:
   sqlite::Row inserts;
   inserts["Name"   ] = "Mustermann";
   inserts["Vorname"] = "Max";
   inserts["Geb_Dat"] = "12/21/79";
   inserts["Ort"    ] = "Musterstadt";
   inserts["Strasse"] = "Musterstraße";
   inserts["HausNr" ] = 22;
   inserts["Gehalt" ] = 2560.606;
 
   dbs.insert("Tabelle",inserts);
 
   //Print out complete Table:
   print_out(dbs.select("Tabelle"));
 
   //Update some Values in Table:
   sqlite::Row updates;
 
   updates["Ort"    ] = "Neustadt";
   updates["Strasse"] = "Gasse des Musters";
   updates["HausNr" ] = 110;
 
   dbs.update("Tabelle",updates,"Name='Mustermann'");
 
   //Print out the updated Table:
   print_out(dbs.select("Tabelle"));
 
   //Here an example to get an double-value to work with it, from Column "Gehalt":
   sqlite::Result Example;
   sqlite::Row row;
   string column_name;
   double column_value;
 
   Example = dbs.select("Tabelle","Name='Mustermann'");
   row = Example.at(0);
   sqlite::Row_it iter = row.find("Gehalt");
   dbs.return_values(iter,column_name,column_value);
 
   cout << column_name << ": " << column_value << " * 2 = "
        << (column_value*2) << endl;
 
   return 0;
}