void Statement::prepare(sqlite3 *connection, const String &sql)
{
OS_ASSERT(m_statement == nullptr);
OS_EXCEPT_IF(connection == nullptr, "Invalid connection");
m_connection = connection;
sqlite_int res = safe_sqlite_prepare(connection, sql, &m_statement);
OS_EXCEPT_IF(res != SQLITE_OK, sqlite_last_error(connection, res));
}
void Statement::close()
{
if(m_statement != nullptr)
{
sqlite3_stmt *statement = m_statement;
m_statement = nullptr; // Resetta il puntatore allo statement
sqlite_int res = safe_sqlite_finalize(statement);
// La chiusura richiamata nel distruttore pertanto non deve lanciare eccezioni
if(res != SQLITE_OK)
{
OS_TRACE(sqlite_last_error(m_connection, res));
OS_ASSERTFALSE();
}
}
m_connection = nullptr;
}
void Connection::connect(const String &filename)
{
close();
// _tsqlite3_open crea un nuovo database se il file non esiste: dovrebbe essere il driver a monte che non crea la connessione se il file non esiste
OS_ASSERT(FileSystem::instance()->fileExists(filename));
int32 res = _tsqlite3_open(filename.c_str(), &m_connection);
OS_EXCEPT_IF(res != SQLITE_OK, sqlite_last_error(m_connection, res));
m_filename = filename;
uint32 cacheSize = OS_NAMESPACE_NAME::Options::instance()->getOption<uint32>(OS_SQLITE_OPTION_CACHE_SIZE);
String synchronous = static_cast<String>(OS_NAMESPACE_NAME::Options::instance()->getOption<String>(OS_SQLITE_OPTION_SYNCHRONOUS)).to_upper();
if((synchronous != _S("FULL")) && (synchronous != _S("NORMAL")) && (synchronous != _S("OFF")))
synchronous = _S("FULL");
uint32 tempStore = OS_NAMESPACE_NAME::Options::instance()->getOption<uint32>(OS_SQLITE_OPTION_TEMP_STORE);
if(tempStore > 2)
tempStore = 0;
doExecute(m_connection, _S("PRAGMA legacy_file_format = OFF;"));
doExecute(m_connection, String::format(_S("PRAGMA cache_size = %d;").c_str(), cacheSize)); // Default di sqlite:2000
doExecute(m_connection, String::format(_S("PRAGMA synchronous = %S;").c_str(), synchronous.c_str())); // FULL/NORMAL/OFF, Default di sqlite: FULL
doExecute(m_connection, String::format(_S("PRAGMA temp_store = %d").c_str(), tempStore)); // 0(Default)/1(File)/2(Memory), Default di sqlite: 0
}
String Connection::preAnalyze(const String &sql)
{
static boost::recursive_mutex cs;
OS_LOCK(cs);
// Per vedere le sql nel charts:
// RealtimeStatsManager::signal(sql, 1, RealtimeStatsCounter::ctAbsolute, _S("status"));
// Da notare che vengono archiviate nella os_monitor solo le query analizzate dalla "explain".
// Una "insert", non usando indici, non viene catalogata, a meno che non abbia sotto-query.
if(sql.find(_S("explain query plan ")) != String::npos)
return _S("");
if(sql.find(_S("os_monitor")) != String::npos)
return _S("");
static shared_ptr<Connection> connectionAnalyze = nullptr;
if(connectionAnalyze == nullptr)
{
shared_ptr<Driver> driver = boost::dynamic_pointer_cast<Driver>(getDriver());
String path = driver->getDatabasePath(_S("monitor.db"));
if(FileSystem::instance()->exists(path) == false)
{
// Crea il database se non esiste
File file;
String msg = _S("Cannot create file '") + path + _S("'");
OS_EXCEPT_IF(file.open(path, File::ofWrite) == false, msg.to_ascii());
}
connectionAnalyze.reset(OS_NEW Connection(path, getDriver()->createOptions(), getDriver()));
int32 res = _tsqlite3_open(path.c_str(), &connectionAnalyze->m_connection);
OS_EXCEPT_IF(res != SQLITE_OK, sqlite_last_error(connectionAnalyze->m_connection, res));
// Creazione tabella
String sqlCreate = _S("CREATE TABLE IF NOT EXISTS os_monitor (filename text,sql text,thread integer,conn integer,trans integer, qorder integer,qfrom integer,detail text,indexUsed text)");
doExecute(connectionAnalyze->m_connection, sqlCreate);
}
// I valori dei campi li setto a ?, per poter fare delle distinct sui risultati
String keySql = sql;
keySql.trim();
keySql = regex_replace(keySql, RegexManager::instance()->getRegex(_S("'(.*?)'")), _S("<s>")); // Valori dei campi
keySql = regex_replace(keySql, RegexManager::instance()->getRegex(_S("[-+]?[0-9]*\\.?[0-9]+")), _S("<n>")); // Numeri
keySql = keySql.replace_all(_S("\n"),_S(""));
keySql = keySql.replace_all(_S("\r"),_S(""));
keySql = keySql.replace_all(_S("\t"),_S(""));
keySql = keySql.replace_all(_S("'"),_S("")); // Futile, per evitare errori nella insert nella os_monitor
for(;;) // Comprimo gli spazi
{
String x = keySql;
keySql = keySql.replace_all(_S(" "),_S(" "));
if(x == keySql)
break;
}
uint32 conn = uint32(reinterpret_cast<uint64>(this));
boost::any threadID = PlatformManager::instance()->getCurrentThreadID();
// Tenta un cast a uint32
uint32 *thread = boost::any_cast<uint32>(&threadID);
int32 trans = m_transactionId;
DataTable result;
doQuery(m_connection, _S("explain query plan ") + sql,result);
for(uint32 r=0;r<result.rows();r++)
{
int32 order = *result.get(r,_S("order"));
int32 from = *result.get(r,_S("from"));
String detail = *result.get(r,_S("detail"));
String indexUsed;
String::size_type posWithIndex = detail.find(_S("WITH INDEX"));
if(posWithIndex != String::npos)
{
indexUsed = detail.substr(posWithIndex+10);
indexUsed = indexUsed.replace_all(_S("ORDER BY"),String::EMPTY);
indexUsed.trim();
}
String sqlInsert = String::format(_S("insert into os_monitor values ('%S','%S',%u,%u,%u,%d,%d,'%S','%S')").c_str(), m_filename.c_str(), keySql.c_str(), conn, thread != nullptr ? *thread : 0, trans, order, from, detail.c_str(), indexUsed.c_str());
doExecute(connectionAnalyze->m_connection, sqlInsert);
}
String trace = String::format(_S("Sqlite monitor: sql='%S',conn=%u,thread=%u,trans=%u\n").c_str(), sql.c_str(), conn, thread != nullptr ? *thread : 0, trans);
//OS_TRACE(trace);
//RealtimeStatsManager::signal(_S("Sqlite ") + keySql.mid(0,6), 1, RealtimeStatsCounter::ctRelative, _S("items"));
return keySql;
}
void Statement::run()
{
sqlite_int res = safe_sqlite_step(m_statement);
OS_EXCEPT_IF(res != SQLITE_OK && res != SQLITE_ROW && res != SQLITE_DONE, sqlite_last_error(m_connection, res));
}
