Forked from
waLBerla / waLBerla
2002 commits behind the upstream repository.
-
Sebastian Eibl authoredSebastian Eibl authored
SQLite.cpp 22.71 KiB
//======================================================================================================================
//
// This file is part of waLBerla. waLBerla is free software: you can
// redistribute it and/or modify it under the terms of the GNU General Public
// License as published by the Free Software Foundation, either version 3 of
// the License, or (at your option) any later version.
//
// waLBerla is distributed in the hope that it will be useful, but WITHOUT
// ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
// FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
// for more details.
//
// You should have received a copy of the GNU General Public License along
// with waLBerla (see COPYING.txt). If not, see <http://www.gnu.org/licenses/>.
//
//! \file SQLite.cpp
//! \ingroup postprocessing
//! \author Martin Bauer <martin.bauer@fau.de>
//
//======================================================================================================================
#include "SQLite.h"
#include "extern/sqlite3.h"
#include <boost/uuid/uuid.hpp>
#include <boost/uuid/uuid_generators.hpp>
#include <boost/uuid/uuid_io.hpp>
#include <sstream>
namespace walberla {
namespace postprocessing {
SQLiteDB::SQLiteDB( const string & dbFile, const int busyTimeout )
: valid_(true), dbHandle_(NULL), file_( dbFile )
{
static const char * CREATE_RUN_TABLE =
"CREATE TABLE IF NOT EXISTS runs ("
" runId INTEGER PRIMARY KEY, "
" timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, "
" uuid STRING );" ;
// Create tables if it does not exist
int retVal = sqlite3_open( file_.c_str(), &dbHandle_ );
if ( retVal != SQLITE_OK ) {
WALBERLA_LOG_WARNING( "Failed to open sqlite3 file." << dbFile );
valid_ = false;
return;
}
sqlite3_busy_timeout( dbHandle_, busyTimeout*1000 );
sqlite3_exec( dbHandle_, "PRAGMA foreign_keys = ON;",0,0,0 );
sqlite3_exec( dbHandle_, CREATE_RUN_TABLE, 0,0,0);
static const char* UPDATE_RUN_TABLE_CMD = "ALTER TABLE runs ADD COLUMN uuid STRING;";
sqlite3_exec( dbHandle_, UPDATE_RUN_TABLE_CMD, 0, 0, 0 );
}
SQLiteDB::~SQLiteDB ()
{
if ( valid_ )
sqlite3_close( dbHandle_ );
}
//*******************************************************************************************************************
/*! Store information about a simulation run into a Sqlite3 Database
*
* The generated database is called "runs". The columns of the table are the keys of the given maps.
* If a column does not yet exist, it is appended to the database. The entries that have no value in this column
* are set to default value ( zero or empty string ).
* Additionally a column is created for each activated global state, and its value is set to 1
*
* \param *Properties Map of column names to value
* \returns The primary key of the inserted data set.
*/
//*******************************************************************************************************************
template<typename IntType>
uint_t storeRunImpl( sqlite3 * dbHandle, std::string & filename,
const map<string, IntType> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties )
{
WALBERLA_ASSERT_NOT_NULLPTR( dbHandle );
sqlite3_exec( dbHandle, "BEGIN;",0,0,0 );
string insertRunCommand = "INSERT INTO runs (timestamp, uuid ";
std::stringstream values;
auto uuid = boost::uuids::random_generator()();
values << " VALUES ( CURRENT_TIMESTAMP, \"" << uuid << "\" ";
// Add columns for integer properties
for ( auto i = integerProperties.begin(); i != integerProperties.end(); ++i )
{
insertRunCommand += "," + i->first;
values << ", " << i->second;
string command = "ALTER TABLE runs ADD COLUMN " + i->first + " INTEGER ";
sqlite3_exec ( dbHandle, command.c_str(), 0,0,0 ); // ignore errors (column can exist already)
}
// Add columns for string properties
for ( auto i = stringProperties.begin(); i != stringProperties.end(); ++i )
{
insertRunCommand += "," + i->first;
values << ", " << "\"" << i->second << "\"";
string command = "ALTER TABLE runs ADD COLUMN " + i->first + " TEXT ";
sqlite3_exec ( dbHandle, command.c_str(), 0,0,0 ); // ignore errors (column can exist already)
}
// Add columns for real_t properties
for ( auto i = realProperties.begin(); i != realProperties.end(); ++i )
{
if( math::finite( i->second ) )
{
insertRunCommand += "," + i->first;
values << ", " << i->second;
string command = "ALTER TABLE runs ADD COLUMN " + i->first + " DOUBLE ";
sqlite3_exec( dbHandle, command.c_str(), 0, 0, 0 ); // ignore errors (column can exist already)
}
else
{
WALBERLA_LOG_WARNING( "Skipping column \"" << i->first << "\" while inserting a row into the run table of the "
"sqlite3 database \"" << filename << "\" due to non-finite value \"" << i->second << "\"." );
}
}
// Add columns for global state selectors
for( auto i = uid::globalState().begin(); i != uid::globalState().end(); ++i )
{
insertRunCommand += "," + i->getIdentifier();
values << " ,1";
// no boolean in sqlite3, use integer instead
string command = "ALTER TABLE runs ADD COLUMN " + i->getIdentifier() + " INTEGER ";
sqlite3_exec ( dbHandle, command.c_str(), 0,0,0 ); // ignore errors (column can exist already)
}
insertRunCommand += " ) ";
values << "); ";
insertRunCommand += values.str();
int ret = sqlite3_exec ( dbHandle, insertRunCommand.c_str(), 0, 0, 0 );
if ( ret != SQLITE_OK) {
WALBERLA_LOG_WARNING( "Failed to insert a row into run table of sqlite3 database: " << sqlite3_errmsg(dbHandle) << "\n sql command: " << insertRunCommand.c_str() );
}
uint_t generatedPrimaryKey = uint_c ( sqlite3_last_insert_rowid( dbHandle ) );
sqlite3_exec( dbHandle, "END TRANSACTION;",0,0,0 );
return generatedPrimaryKey;
}
//*******************************************************************************************************************
/*! Stores information in another table, referencing the "run" table
*
* \param runId result of storeRun() member function, primary key of the run to store information for
* \param tableName name of the table where the information is stored in
* is created if it does not yet exist
* \param *Properties Map of column names to value
*/
//*******************************************************************************************************************
template<typename IntType>
void storeAdditionalRunInfoImpl( sqlite3 * dbHandle,
uint_t runId, const std::string & tableName,
const map<string, IntType> & integerProperties,
const map<string, string > & stringProperties ,
const map<string, double > & realProperties )
{
sqlite3_exec( dbHandle, "BEGIN;",0,0,0 );
std::string CREATE_TABLE =
"CREATE TABLE IF NOT EXISTS " + tableName +
" (runId INTEGER, "
" FOREIGN KEY (runId) REFERENCES runs(runId) "
" );" ;
sqlite3_exec( dbHandle, CREATE_TABLE.c_str(), 0,0,0);
string insertRunCommand = "INSERT INTO " + tableName + "( runId";
std::stringstream values;
values << " VALUES ( " << runId;
// Add columns for integer properties
for ( auto i = integerProperties.begin(); i != integerProperties.end(); ++i )
{
insertRunCommand += "," + i->first;
values << ", " << i->second;
string command = "ALTER TABLE " + tableName + " ADD COLUMN " + i->first + " INTEGER ";
sqlite3_exec ( dbHandle, command.c_str(), 0,0,0 ); // ignore errors (column can exist already)
}
// Add columns for string properties
for ( auto i = stringProperties.begin(); i != stringProperties.end(); ++i )
{
insertRunCommand += "," + i->first;
values << ", " << "\"" << i->second << "\"";
string command = "ALTER TABLE " + tableName + " ADD COLUMN " + i->first + " TEXT ";
sqlite3_exec ( dbHandle, command.c_str(), 0,0,0 ); // ignore errors (column can exist already)
}
// Add columns for real_t properties
for ( auto i = realProperties.begin(); i != realProperties.end(); ++i )
{
insertRunCommand += "," + i->first;
values << ", " << i->second ;
string command = "ALTER TABLE " + tableName + " ADD COLUMN " + i->first + " DOUBLE ";
sqlite3_exec ( dbHandle, command.c_str(), 0,0,0 ); // ignore errors (column can exist already)
}
insertRunCommand += " ) ";
values << "); ";
insertRunCommand += values.str();
int ret = sqlite3_exec ( dbHandle, insertRunCommand.c_str(), 0, 0, 0 );
if ( ret != SQLITE_OK) {
WALBERLA_LOG_WARNING( "Failed to insert a row into run table of sqlite3 database: " << sqlite3_errmsg(dbHandle) << "\n sql command: " << insertRunCommand.c_str() );
}
sqlite3_exec( dbHandle, "END TRANSACTION;",0,0,0 );
}
//*******************************************************************************************************************
/*! Store information about a simulation run into a Sqlite3 Database
*
* The generated database is called "runs". The columns of the table are the keys of the given maps.
* If a column does not yet exist, it is appended to the database. The entries that have no value in this column
* are set to default value ( zero or empty string ).
* Additionally a column is created for each activated global state, and its value is set to 1
*
* \param *Properties Map of column names to value
* \returns The primary key of the inserted data set.
*/
//*******************************************************************************************************************
uint_t SQLiteDB::storeRun( const map<string, int> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties )
{
return storeRunImpl(dbHandle_, file_, integerProperties, stringProperties, realProperties);
}
/// \see storeRun
uint_t SQLiteDB::storeRun( const map<string, int64_t> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties )
{
return storeRunImpl(dbHandle_, file_, integerProperties, stringProperties, realProperties);
}
//*******************************************************************************************************************
/*! Stores information in another table, referencing the "run" table
*
* \param runId result of storeRun() member function, primary key of the run to store information for
* \param tableName name of the table where the information is stored in
* is created if it does not yet exist
* \param *Properties Map of column names to value
*/
//*******************************************************************************************************************
void SQLiteDB::storeAdditionalRunInfo( uint_t runId, const std::string & tableName,
const map<string, int> & integerProperties,
const map<string, string > & stringProperties ,
const map<string, double > & realProperties )
{
return storeAdditionalRunInfoImpl( dbHandle_, runId, tableName, integerProperties, stringProperties, realProperties);
}
/// \see storeAdditionalRunInfo
void SQLiteDB::storeAdditionalRunInfo( uint_t runId, const std::string & tableName,
const map<string, int64_t> & integerProperties,
const map<string, string > & stringProperties ,
const map<string, double > & realProperties )
{
return storeAdditionalRunInfoImpl( dbHandle_, runId, tableName, integerProperties, stringProperties, realProperties);
}
//*******************************************************************************************************************
/*! Stores a TimingPool in a Sqlite3 Database, and links it to a run
*
* The generated table is called "timingPools"
*
* \param runId primary key of the run, as returned by storeRun()
* \param tp the TimingPool to store
* \param name name of the timing pool ( as written to database column )
*/
//*******************************************************************************************************************
void SQLiteDB::storeTimingPool ( uint_t runId,
const WcTimingPool & tp,
const std::string & timingPoolName )
{
sqlite3_exec( dbHandle_, "BEGIN;",0,0,0 );
assert ( timingPoolName.length() > 0 && timingPoolName.length() < 255 );
static const char * CREATE_TIMINGPOOL_TABLE =
"CREATE TABLE IF NOT EXISTS timingPool ("
" runId INTEGER, "
" name VARCHAR(255),"
" sweep VARCHAR(255),"
" average DOUBLE, "
" min DOUBLE, "
" max DOUBLE, "
" count INTEGER,"
" variance DOUBLE, "
" percentage DOUBLE, "
" FOREIGN KEY (runId) REFERENCES runs(runId) "
" );" ;
static const char * INSERT_STATEMENT =
" INSERT INTO timingPool (runId,name,sweep,average,min,max,count,variance,percentage) "
" VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ? )";
sqlite3_exec( dbHandle_, CREATE_TIMINGPOOL_TABLE, 0,0,0 );
sqlite3_stmt *stmt = NULL;
auto retVal = sqlite3_prepare_v2(dbHandle_, INSERT_STATEMENT, -1, &stmt, 0 );
if ( retVal != SQLITE_OK ) {
WALBERLA_LOG_WARNING( "Failed to prepare SQL Insert statement." << file_ );
return;
}
double totalTime = 0;
for ( auto i = tp.begin(); i != tp.end(); ++i )
totalTime += i->second.total();
for ( auto i = tp.begin(); i != tp.end(); ++i )
{
sqlite3_bind_int64 ( stmt, 1, int64_c(runId) );
sqlite3_bind_text ( stmt, 2, timingPoolName.c_str() , -1, SQLITE_STATIC );
sqlite3_bind_text ( stmt, 3, i->first.c_str() , -1, SQLITE_STATIC );
sqlite3_bind_double( stmt, 4, ( ( i->second.getCounter() == uint_t(0) ) ? 0.0 : double_c( i->second.average() ) ) );
sqlite3_bind_double( stmt, 5, ( ( i->second.getCounter() == uint_t(0) ) ? 0.0 : double_c( i->second.min() ) ) );
sqlite3_bind_double( stmt, 6, ( ( i->second.getCounter() == uint_t(0) ) ? 0.0 : double_c( i->second.max() ) ) );
sqlite3_bind_int64 ( stmt, 7, int64_c ( i->second.getCounter() ));
sqlite3_bind_double( stmt, 8, ( ( i->second.getCounter() == uint_t(0) ) ? 0.0 : double_c( i->second.variance() ) ) );
sqlite3_bind_double( stmt, 9, ( ( i->second.getCounter() == uint_t(0) ) ? 0.0 : double_c( i->second.total() / totalTime ) ) );
sqlite3_step ( stmt ); // execute statement
sqlite3_reset ( stmt ); // undo binding
}
sqlite3_exec( dbHandle_, "END TRANSACTION;",0,0,0 );
sqlite3_finalize( stmt ); // free prepared statement
}
//*******************************************************************************************************************
/*! Stores a TimingTree in a Sqlite3 Database, and links it to a run
*
* The generated table is called "timingTree"
*
* \param runId primary key of the run, as returned by storeRun()
* \param tt the TimingTree to store
* \param name name of the timing tree ( as written to database column )
*/
//*******************************************************************************************************************
void SQLiteDB::storeTimingTree ( uint_t runId,
const WcTimingTree & tt,
const std::string & timingTreeName )
{
sqlite3_exec( dbHandle_, "BEGIN;",0,0,0 );
assert ( timingTreeName.length() > 0 && timingTreeName.length() < 255 );
static const char * CREATE_TIMINGTREE_TABLE =
"CREATE TABLE IF NOT EXISTS timingTree ("
" id INTEGER PRIMARY KEY, "
" runId INTEGER, "
" name VARCHAR(255),"
" parentId INTEGER, "
" sweep VARCHAR(255),"
" average DOUBLE, "
" min DOUBLE, "
" max DOUBLE, "
" count INTEGER,"
" variance DOUBLE, "
" percentage DOUBLE, "
" FOREIGN KEY (runId) REFERENCES runs(runId) "
" );" ;
sqlite3_exec( dbHandle_, CREATE_TIMINGTREE_TABLE, 0,0,0 );
double totalTime = 0.0;
for (auto it = tt.getRawData().tree_.begin(); it != tt.getRawData().tree_.end(); ++it)
{
totalTime += it->second.timer_.total();
}
storeTimingNode(runId, std::numeric_limits<int>::max(), tt.getRawData(), timingTreeName, "Total", totalTime);
sqlite3_exec( dbHandle_, "END TRANSACTION;",0,0,0 );
}
//*******************************************************************************************************************
/*! Stores a TimingNode recursively in a Sqlite3 Database, and links it together
*
* \param runId primary key of the run, as returned by storeRun()
* \param parentId parent key of the node
* \param tn the TimingNode to store
* \param name name of the timing tree ( as written to database column )
*/
//*******************************************************************************************************************
void SQLiteDB::storeTimingNode ( const uint_t runId,
const int parentId,
const WcTimingNode & tn,
const std::string & timingTreeName,
const std::string & sweep,
const double totalTime )
{
static const char * INSERT_STATEMENT =
" INSERT INTO timingTree (runId,name,parentId,sweep,average,min,max,count,variance,percentage) "
" VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
sqlite3_stmt *stmt = NULL;
auto retVal = sqlite3_prepare_v2(dbHandle_, INSERT_STATEMENT, -1, &stmt, 0 );
if ( retVal != SQLITE_OK ) {
WALBERLA_LOG_WARNING( "Failed to prepare SQL Insert statement (" << retVal << ")." );
return;
}
sqlite3_bind_int64 ( stmt, 1, int64_c(runId) );
sqlite3_bind_text ( stmt, 2, timingTreeName.c_str() , -1, SQLITE_STATIC );
sqlite3_bind_int64 ( stmt, 3, parentId );
sqlite3_bind_text ( stmt, 4, sweep.c_str() , -1, SQLITE_STATIC );
sqlite3_bind_double( stmt, 5, ( ( tn.timer_.getCounter() == uint_t(0) ) ? 0.0 : double_c( tn.timer_.average() ) ) );
sqlite3_bind_double( stmt, 6, ( ( tn.timer_.getCounter() == uint_t(0) ) ? 0.0 : double_c( tn.timer_.min() ) ) );
sqlite3_bind_double( stmt, 7, ( ( tn.timer_.getCounter() == uint_t(0) ) ? 0.0 : double_c( tn.timer_.max() ) ) );
sqlite3_bind_int64 ( stmt, 8, int64_c ( tn.timer_.getCounter() ));
sqlite3_bind_double( stmt, 9, ( ( tn.timer_.getCounter() == uint_t(0) ) ? 0.0 : double_c( tn.timer_.variance() ) ) );
sqlite3_bind_double( stmt,10, ( ( tn.timer_.getCounter() == uint_t(0) ) ? 0.0 : double_c( tn.timer_.total() / totalTime ) ) );
sqlite3_step ( stmt ); // execute statement
sqlite3_reset ( stmt ); // undo binding
sqlite3_finalize( stmt ); // free prepared statement
int currentId = int_c( sqlite3_last_insert_rowid( dbHandle_ ) );
for ( auto i = tn.tree_.begin(); i != tn.tree_.end(); ++i )
{
storeTimingNode( runId, currentId, i->second, timingTreeName, i->first, totalTime);
}
}
uint_t storeRunInSqliteDB( const string & dbFile,
const map<string, int> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties,
const int busyTimeout )
{
SQLiteDB db ( dbFile, busyTimeout );
return db.storeRun( integerProperties, stringProperties, realProperties );
}
uint_t storeRunInSqliteDB( const string & dbFile,
const map<string, int64_t> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties,
const int busyTimeout )
{
SQLiteDB db ( dbFile, busyTimeout );
return db.storeRun( integerProperties, stringProperties, realProperties );
}
void storeAdditionalRunInfoInSqliteDB( const uint_t runId,
const string & dbFile,
const string & tableName,
const map<string, int> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties,
const int busyTimeout )
{
SQLiteDB db ( dbFile, busyTimeout );
return db.storeAdditionalRunInfo( runId, tableName, integerProperties, stringProperties, realProperties );
}
void storeAdditionalRunInfoInSqliteDB( const uint_t runId,
const string & dbFile,
const string & tableName,
const map<string, int64_t> & integerProperties,
const map<string, string > & stringProperties,
const map<string, double > & realProperties,
const int busyTimeout )
{
SQLiteDB db ( dbFile, busyTimeout );
return db.storeAdditionalRunInfo( runId, tableName, integerProperties, stringProperties, realProperties );
}
void storeTimingPoolInSqliteDB ( const string & dbFile, uint_t runId,
const WcTimingPool & tp,
const std::string & timingPoolName,
const int busyTimeout )
{
SQLiteDB db ( dbFile, busyTimeout );
db.storeTimingPool( runId, tp, timingPoolName );
}
void storeTimingTreeInSqliteDB ( const string & dbFile, uint_t runId,
const WcTimingTree & tt,
const std::string & timingTreeName,
const int busyTimeout )
{
SQLiteDB db ( dbFile, busyTimeout );
db.storeTimingTree( runId, tt, timingTreeName );
}
} // namespace postprocessing
} // namespace walberla