====== SQLite Wrapperklasse (Rückgabewerte im gespeicherten Datentyp) ======
FIXME Anwendungsbeispiel auf neuen Quellcode anpassen
===== Wissensvoraussetzungen =====
Wer diese Klasse lediglich einsetzen will, der sollte mit der [[dbs:sqlite:libsqlite3:cppwrapper_complex:ref|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
===== Funktionsumfang der Klasse =====
* 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
===== Was diese Klasse nicht kann =====
* BLOBs verarbeiten
* jede Möglichkeit eines SQL-Befehls als eigene Funktion implementieren
* etwas anderes als Tabellen aus einer Datenbank zurückliefern
===== Headerdatei =====
#ifndef SQLITEWRAPPER_HPP_INCLUDED
#define SQLITEWRAPPER_HPP_INCLUDED
#include
#include
#include
\\
===== Quellcodedatei =====
#include "sqlitewrapper.hpp"
#include
#include
#include
#include
#include
#include
/*** 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(input) + "'";
else if (ti == typeid(int))
{
stream << boost::get(input);
stream >> temp;
return_value = temp;
temp.clear();
stream.clear();
}
else if (ti == typeid(double))
{
stream << boost::get(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
(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;
}
\\
===== Anwendungsbeispiel =====
FIXME ACHTUNG: Dieses Anwendungsbeispiel ist derzeit mit der obigen Klasse inkompatibel!!
#include
#include
#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;
}