databaseConnector.cpp
Go to the documentation of this file.
/******************************************************
* Presage, an extensible predictive text entry system
* ---------------------------------------------------
*
* Copyright (C) 2008 Matteo Vescovi <matteo.vescovi@yahoo.co.uk>
This program 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 2 of the License, or
(at your option) any later version.
This program 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 this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*
**********(*)*/
#include "databaseConnector.h"
#include "../../core/utility.h"
#include <list>
#include <sstream>
#include <stdlib.h>
#include <assert.h>
DatabaseConnector::DatabaseConnector(const std::string database_name,
const size_t cardinality,
const bool read_write)
: logger("DatabaseConnector", std::cerr)
{
set_database_filename (database_name);
set_cardinality (cardinality);
set_read_write_mode (read_write);
}
DatabaseConnector::DatabaseConnector(const std::string database_name,
const size_t cardinality,
const bool read_write,
const std::string& log_level)
: logger("DatabaseConnector", std::cerr, log_level)
{
set_database_filename (database_name);
set_cardinality (cardinality);
set_read_write_mode (read_write);
}
DatabaseConnector::~DatabaseConnector()
{}
void DatabaseConnector::createNgramTable(const size_t n) const
{
if (n > 0) {
std::stringstream query;
std::stringstream unique;
query << "CREATE TABLE";
// This #ifdef does not belong here, but unfortunately SQLite 2.x does
// not support the IF NOT EXISTS SQL clause.
#ifndef HAVE_SQLITE_H
query << " IF NOT EXISTS";
#endif
query << " _" << n << "_gram (";
for (int i = n - 1; i >= 0; i--) {
if (i != 0) {
unique << "word_" << i << ", ";
query << "word_" << i << " TEXT, ";
} else {
unique << "word";
query << "word TEXT, count INTEGER, UNIQUE(" << unique.str() << ") );";
}
}
executeSql(query.str());
} else {
// TODO
// throw exception
}
}
int DatabaseConnector::getUnigramCountsSum() const
{
std::string query = "SELECT SUM(count) FROM _1_gram;";
NgramTable result = executeSql(query);
logger << DEBUG << "NgramTable:";
for (size_t i = 0; i < result.size(); i++) {
for (size_t j = 0; j < result[i].size(); j++) {
logger << DEBUG << result[i][j] << '\t';
}
logger << DEBUG << endl;
}
return extractFirstInteger(result);
}
int DatabaseConnector::getNgramCount(const Ngram ngram) const
{
std::stringstream query;
query << "SELECT count "
<< "FROM _" << ngram.size() << "_gram"
<< buildWhereClause(ngram) << ";";
NgramTable result = executeSql(query.str());
logger << DEBUG << "NgramTable:";
for (size_t i = 0; i < result.size(); i++) {
for (size_t j = 0; j < result[i].size(); j++) {
logger << DEBUG << result[i][j] << '\t';
}
logger << DEBUG << endl;
}
return extractFirstInteger(result);
}
NgramTable DatabaseConnector::getNgramLikeTable(const Ngram ngram, int limit) const
{
std::stringstream query;
query << "SELECT " << buildSelectLikeClause(ngram.size()) << " "
<< "FROM _" << ngram.size() << "_gram"
<< buildWhereLikeClause(ngram)
<< " ORDER BY count DESC";
if (limit < 0) {
query << ";";
} else {
query << " LIMIT " << limit << ';';
}
return executeSql(query.str());
}
NgramTable DatabaseConnector::getNgramLikeTableFiltered(const Ngram ngram, const char** filter, int limit) const
{
std::stringstream query;
query << "SELECT " << buildSelectLikeClause(ngram.size()) << " "
<< "FROM _" << ngram.size() << "_gram"
<< buildWhereLikeClauseFiltered(ngram,filter)
<< " ORDER BY count DESC";
if (limit < 0) {
query << ";";
} else {
query << " LIMIT " << limit << ';';
}
return executeSql(query.str());
}
int DatabaseConnector::incrementNgramCount(const Ngram ngram) const
{
int count = getNgramCount(ngram);
if (count > 0) {
// the ngram was found in the database
updateNgram(ngram, ++count);
logger << DEBUG << "Updated ngram to " << count << endl;
} else {
// the ngram was not found in the database
count = 1;
insertNgram(ngram, count);
logger << DEBUG << "Inserted ngram" << endl;
}
return count;
}
void DatabaseConnector::removeNgram(const Ngram ngram) const
{}
void DatabaseConnector::insertNgram(const Ngram ngram, const int count) const
{
std::stringstream query;
query << "INSERT INTO _" << ngram.size() << "_gram "
<< buildValuesClause(ngram, count)
<< ";";
executeSql(query.str());
}
void DatabaseConnector::updateNgram(const Ngram ngram, const int count) const
{
std::stringstream query;
query << "UPDATE _" << ngram.size() << "_gram "
<< "SET count = " << count
<< buildWhereClause(ngram) << ";";
executeSql(query.str());
}
std::string DatabaseConnector::buildWhereClause(const Ngram ngram) const
{
std::stringstream where_clause;
where_clause << " WHERE";
for (size_t i = 0; i < ngram.size(); i++) {
if (i < ngram.size() - 1) {
where_clause << " word_" << ngram.size() - i - 1 << " = '"
<< sanitizeString(ngram[i]) << "' AND";
} else {
where_clause << " word = '" << sanitizeString(ngram[ngram.size() - 1]) << "'";
}
}
return where_clause.str();
}
// TODO REVISIT refactor: this is same as buildWhereClause, except for
// "word = " instead of "word LIKE "
std::string DatabaseConnector::buildWhereLikeClause(const Ngram ngram) const
{
std::stringstream where_clause;
where_clause << " WHERE";
for (size_t i = 0; i < ngram.size(); i++) {
if (i < ngram.size() - 1) {
where_clause << " word_" << ngram.size() - i - 1 << " = '"
<< sanitizeString(ngram[i]) << "' AND";
} else {
where_clause << " word LIKE '" << sanitizeString(ngram[ngram.size() - 1]) << "%'";
}
}
return where_clause.str();
}
std::string DatabaseConnector::buildWhereLikeClauseFiltered(const Ngram ngram, const char** filter) const
{
std::stringstream where_clause;
where_clause << " WHERE";
for (size_t i = 0; i < ngram.size(); i++) {
if (i < ngram.size() - 1) {
where_clause << " word_" << ngram.size() - i - 1 << " = '"
<< sanitizeString(ngram[i]) << "' AND";
} else {
if(filter == 0)
where_clause << " word LIKE '" << sanitizeString(ngram[ngram.size() - 1]) << "%'";
else {
std::string true_prefix = sanitizeString(ngram[ngram.size() - 1]);
where_clause << " (";
for (int j = 0; filter[j] != 0; j++) {
// for(size_t j=0; j < filter.size()-1; j++)
if (j) {
where_clause << " OR ";
}
where_clause << " word LIKE '" << true_prefix << filter[j] << "%'";
}
// where_clause << " word LIKE '" << true_prefix <<"%' )";
where_clause << ')';
}
}
}
return where_clause.str();
}
std::string DatabaseConnector::buildSelectLikeClause(const int cardinality) const
{
assert(cardinality > 0);
std::stringstream result;
for (int i = cardinality - 1; i >= 0; i--) {
if (i != 0) {
result << "word_" << i << ", ";
} else {
result << "word, count";
}
}
return result.str();
}
std::string DatabaseConnector::buildValuesClause(const Ngram ngram, const int count) const
{
std::stringstream values_clause;
values_clause << "VALUES(";
for (size_t i = 0; i < ngram.size(); i++) {
if (i < ngram.size() - 1) {
values_clause << "'" << sanitizeString(ngram[i]) << "', ";
} else {
values_clause << "'" << sanitizeString(ngram[i]) << "', " << count << ")";
}
}
return values_clause.str();
}
std::string DatabaseConnector::sanitizeString(const std::string str) const
{
// Escape single quotes
std::size_t searchPos = 0;
std::size_t pos;
std::string result(str);
while((pos = result.find("'", searchPos)) != std::string::npos)
{
result.replace(pos, 1, "''");
searchPos = pos + 2;
}
return result;
}
int DatabaseConnector::extractFirstInteger(const NgramTable& table) const
{
// Initialize count to zero and then check that we have at least
// an entry in the table of ngram counts returned by the
// executeSql() method. If so, convert it into an integer and
// return it.
//
// REVISIT: make conversion to integer more robust (strtol ??)
//
int count = 0;
if (table.size() > 0) {
if (table[0].size() > 0) {
count = atoi(table[0][0].c_str());
}
}
logger << DEBUG << "table: ";
for (size_t i = 0; i < table.size(); i++) {
for (size_t j = 0; j < table[i].size(); j++) {
logger << DEBUG << table[i][j] << '\t';
}
logger << DEBUG << endl;
}
return (count > 0 ? count : 0);
}
void DatabaseConnector::beginTransaction() const
{
executeSql("BEGIN TRANSACTION;");
}
void DatabaseConnector::endTransaction() const
{
executeSql("END TRANSACTION;");
}
void DatabaseConnector::rollbackTransaction() const
{
executeSql("ROLLBACK TRANSACTION;");
}
std::string DatabaseConnector::get_database_filename () const
{
return database_filename;
}
std::string DatabaseConnector::set_database_filename (const std::string& filename)
{
std::string prev_filename = database_filename;
database_filename = expand_variables (filename);
// make an attempt at determining whether directory where language
// model database is located exists and try to create it if it
// does not... only cater for one directory level to create it.
//
std::string dir = Utility::dirname (database_filename);
if (! dir.empty()) {
// check that specified directory exists and accessible
if (! Utility::is_directory_usable (dir)) {
// create it if not
Utility::create_directory (dir);
}
}
return prev_filename;
}
std::string DatabaseConnector::expand_variables (std::string filepath) const
{
// scan the filepath for variables, which follow the same pattern
// as shell variables - strings enclosed in '${' and '}'
//
const std::string start_marker = "${";
const std::string end_marker = "}";
std::list<std::string> variables;
std::string::size_type pos_start = filepath.find (start_marker);
while (pos_start != std::string::npos)
{
std::string::size_type pos_end = filepath.find (end_marker, pos_start);
if (pos_end != std::string::npos) {
variables.push_back (filepath.substr(pos_start + start_marker.size(), pos_end - end_marker.size() - pos_start - 1));
}
pos_start = filepath.find (start_marker, pos_end);
}
for (std::list<std::string>::const_iterator it = variables.begin();
it != variables.end();
it++)
{
substitute_variable_in_string(*it, filepath);
}
return filepath;
}
void DatabaseConnector::substitute_variable_in_string (const std::string& variable_name, std::string& filepath) const
{
std::string variable_token = "${" + variable_name + "}";
for (std::string::size_type pos = filepath.find (variable_token);
pos != std::string::npos;
pos = filepath.find (variable_token, pos))
{
const char* value = getenv(variable_name.c_str());
if (value)
{
filepath.replace (pos,
variable_token.size(),
value);
}
else
{
// handle "special" variables
if (variable_name == "HOME")
{
value = getenv("USERPROFILE");
if (value)
{
filepath.replace (pos,
variable_token.size(),
value);
}
}
else
{
// FIXME: maybe throw exception instead of leaving
// variable name in string?
//
filepath.replace (pos,
variable_token.size(),
variable_name);
}
}
}
}
void DatabaseConnector::set_cardinality (const size_t card)
{
cardinality = card;
}
size_t DatabaseConnector::get_cardinality () const
{
return cardinality;
}
void DatabaseConnector::set_read_write_mode (const bool read_write)
{
read_write_mode = read_write;
}
bool DatabaseConnector::get_read_write_mode () const
{
return read_write_mode;
}
DatabaseConnector::substitute_variable_in_string
void substitute_variable_in_string(const std::string &variable_name, std::string &filepath) const
Definition: databaseConnector.cpp:408
DatabaseConnector::getNgramCount
int getNgramCount(const Ngram ngram) const
Definition: databaseConnector.cpp:104
Utility::create_directory
static void create_directory(const std::string &dir)
Definition: utility.cpp:330
DatabaseConnector::insertNgram
void insertNgram(const Ngram ngram, const int count) const
Definition: databaseConnector.cpp:180
DatabaseConnector::set_database_filename
std::string set_database_filename(const std::string &filename)
Definition: databaseConnector.cpp:355
DatabaseConnector::get_read_write_mode
bool get_read_write_mode() const
Definition: databaseConnector.cpp:464
Utility::is_directory_usable
static bool is_directory_usable(const std::string &dir)
Definition: utility.cpp:307
DatabaseConnector::read_write_mode
bool read_write_mode
Definition: databaseConnector.h:166
NgramTable
std::vector< Ngram > NgramTable
Definition: databaseConnector.h:39
DatabaseConnector::get_cardinality
size_t get_cardinality() const
Definition: databaseConnector.cpp:454
Ngram
Definition: ngram.h:33
DatabaseConnector::set_read_write_mode
void set_read_write_mode(const bool read_write)
Definition: databaseConnector.cpp:459
DatabaseConnector::getUnigramCountsSum
int getUnigramCountsSum() const
Definition: databaseConnector.cpp:87
DatabaseConnector::expand_variables
std::string expand_variables(std::string filename) const
Definition: databaseConnector.cpp:377
DatabaseConnector::database_filename
std::string database_filename
Definition: databaseConnector.h:164
DatabaseConnector::removeNgram
void removeNgram(const Ngram ngram) const
Definition: databaseConnector.cpp:177
DatabaseConnector::buildWhereLikeClauseFiltered
std::string buildWhereLikeClauseFiltered(const Ngram ngram, const char **filter) const
Definition: databaseConnector.cpp:234
DatabaseConnector::buildValuesClause
std::string buildValuesClause(const Ngram ngram, const int count) const
Definition: databaseConnector.cpp:280
Utility::dirname
static std::string dirname(const std::string &)
Definition: utility.cpp:275
DatabaseConnector::getNgramLikeTable
NgramTable getNgramLikeTable(const Ngram ngram, int limit=-1) const
Definition: databaseConnector.cpp:124
DatabaseConnector::extractFirstInteger
int extractFirstInteger(const NgramTable &) const
Definition: databaseConnector.cpp:308
DatabaseConnector::set_cardinality
void set_cardinality(const size_t cardinality)
Definition: databaseConnector.cpp:449
DatabaseConnector::cardinality
size_t cardinality
Definition: databaseConnector.h:165
DatabaseConnector::incrementNgramCount
int incrementNgramCount(const Ngram ngram) const
Definition: databaseConnector.cpp:156
databaseConnector.h
DatabaseConnector::DatabaseConnector
DatabaseConnector(const std::string database_name, const size_t cardinality, const bool read_write)
Definition: databaseConnector.cpp:34
endl
const Logger< _charT, _Traits > & endl(const Logger< _charT, _Traits > &lgr)
Definition: logger.h:278
DatabaseConnector::endTransaction
virtual void endTransaction() const
Definition: databaseConnector.cpp:340
DatabaseConnector::executeSql
virtual NgramTable executeSql(const std::string query) const =0
DatabaseConnector::logger
Logger< char > logger
Definition: databaseConnector.h:127
DatabaseConnector::beginTransaction
virtual void beginTransaction() const
Definition: databaseConnector.cpp:335
DatabaseConnector::updateNgram
void updateNgram(const Ngram ngram, const int count) const
Definition: databaseConnector.cpp:191
DatabaseConnector::~DatabaseConnector
virtual ~DatabaseConnector()
Definition: databaseConnector.cpp:55
DatabaseConnector::createNgramTable
void createNgramTable(const size_t cardinality) const
Definition: databaseConnector.cpp:58
DatabaseConnector::buildSelectLikeClause
std::string buildSelectLikeClause(const int cardinality) const
Definition: databaseConnector.cpp:264
DatabaseConnector::rollbackTransaction
virtual void rollbackTransaction() const
Definition: databaseConnector.cpp:345
DatabaseConnector::buildWhereClause
std::string buildWhereClause(const Ngram ngram) const
Definition: databaseConnector.cpp:202
DatabaseConnector::buildWhereLikeClause
std::string buildWhereLikeClause(const Ngram ngram) const
Definition: databaseConnector.cpp:219
DatabaseConnector::getNgramLikeTableFiltered
NgramTable getNgramLikeTableFiltered(const Ngram ngram, const char **filter, int limit=-1) const
Definition: databaseConnector.cpp:140
DatabaseConnector::sanitizeString
std::string sanitizeString(const std::string) const
Definition: databaseConnector.cpp:294
DatabaseConnector::get_database_filename
std::string get_database_filename() const
Definition: databaseConnector.cpp:350