SEXP aRTconn::DeleteDb(SEXP data)
{
string databasename = GET_STRING_ELEMENT(data, "dbname");
// "checking ...": R code already does.
TeDatabase* database = NewTeDatabase();
if( !database -> connect(Host, User, Password, "", Port))
{
PrintSilentNo;
error("Could not connect\n");
}
PrintSilent("Deleting database \'%s\' ... ", databasename.c_str());
stringstream stream;
stream << "DROP DATABASE " << databasename << ";";
string sql = StreamToChar(stream);
if( !database->execute(sql) )
{
delete database;
PrintSilentNo;
error( database->errorMessage().c_str() );
}
delete database;
PrintSilentYes;
return RNULL;
}
void
fillCoverageMetadataTable(TeLayer* layer, std::string& metadataTableName, const std::string& coverageId, std::vector<TeCoverageDimension>& dimensions)
{
TeDatabase* db = layer->database();
if (!db->tableExist(metadataTableName))
{
createCoverageMetadataTable(layer, metadataTableName);
}
for (std::vector<TeCoverageDimension>::iterator it = dimensions.begin(); it != dimensions.end(); it++)
{
const std::string& coverageIdStr = coverageId;
const std::string dimensionIdStr = Te2String(it->dimension_id);
const std::string dataTypeStr = Te2String(static_cast<TeDataType>(it->type));
const std::string& nameStr = it->name;
std::string sqlInsert = "INSERT INTO " + metadataTableName + " (coverage_id, dimension_id, data_type, name) VALUES ('" + coverageIdStr + "', '" + dimensionIdStr + "', '" + dataTypeStr + "', '" + nameStr + "')";
if (!db->execute(sqlInsert))
{
std::string errorMsg = "Couldn't insert values to metadata table '" + metadataTableName + "'.";
throw TeException(UNKNOWN_ERROR_TYPE, errorMsg, false);
}
}
}
vector<string> getObjects(TeTheme* theme, vector<string>& itens)
{
vector<string> svec;
if(!theme)
return svec;
TeDatabase* db = 0;
if(theme->getProductId() == TeEXTERNALTHEME)
db = static_cast<TeExternalTheme*>(theme)->getSourceDatabase();
else if(theme->getProductId() == TeTHEME)
db = static_cast<TeTheme*>(theme)->layer()->database();
if(db == 0)
return svec;
TeDatabasePortal* portal = db->getPortal();
string C = theme->collectionTable();
string CA = theme->collectionAuxTable();
string query = "SELECT " + C + ".c_object_id FROM " + C + " LEFT JOIN " + CA;
query += " ON " + C + ".c_object_id = " + CA + ".object_id";
query += " WHERE " + CA + ".unique_id IN ";
set<string> idSet;
vector< string >::iterator it_begin = itens.begin();
vector< string >::iterator it_end = itens.end();
vector<string> inVec = generateInClauses(it_begin, it_end, db, false);
vector<string>::iterator it;
for(it=inVec.begin(); it!=inVec.end(); ++it)
{
if((*it).empty() == false)
{
string sel = query + *it;
if (portal->query(sel) == false)
{
delete portal;
return svec;
}
while (portal->fetchRow())
idSet.insert(portal->getData(0));
portal->freeResult();
}
}
delete portal;
set<string>::iterator sit;
for(sit=idSet.begin(); sit!=idSet.end(); ++sit)
svec.push_back(*sit);
return svec;
}
vector<string> getItems(TeTheme* theme, int sel)
{
vector<string> svec;
if(!theme)
return svec;
TeDatabase* db = 0;
if(theme->getProductId() == TeEXTERNALTHEME)
db = static_cast<TeExternalTheme*>(theme)->getSourceDatabase();
else if(theme->getProductId() == TeTHEME)
db = static_cast<TeTheme*>(theme)->layer()->database();
if(db == 0)
return svec;
string C = theme->collectionTable();
string CA = theme->collectionAuxTable();
string input;
if(sel == TeSelectedByPointing)
input += " WHERE " + CA + ".grid_status = 1 OR " + CA + ".grid_status = 3";
else if(sel == TeNotSelectedByPointing)
input += " WHERE " + CA + ".grid_status = 0 OR " + CA + ".grid_status = 2";
else if(sel == TeSelectedByQuery)
input += " WHERE " + CA + ".grid_status = 2 OR " + CA + ".grid_status = 3";
else if(sel == TeNotSelectedByQuery)
input += " WHERE " + CA + ".grid_status = 0 OR " + CA + ".grid_status = 1";
else if(sel == TeSelectedByPointingAndQuery)
input += " WHERE " + CA + ".grid_status = 3";
else if(sel == TeSelectedByPointingOrQuery)
input += " WHERE " + CA + ".grid_status <> 0";
else if(sel == TeGrouped)
input += " WHERE " + C + ".c_legend_id <> 0";
else if(sel == TeNotGrouped)
input += " WHERE " + C + ".c_legend_id = 0";
string query = "SELECT " + CA + ".unique_id FROM " + C + " LEFT JOIN " + CA;
query += " ON " + C + ".c_object_id = " + CA + ".object_id" + input;
TeDatabasePortal* portal = db->getPortal();
if (portal->query(query) == false)
{
delete portal;
return svec;
}
while (portal->fetchRow())
svec.push_back(portal->getData(0));
delete portal;
return svec;
}
SEXP aRTconn::AddPermission(SEXP data) // only works in MySQL!
{
string user = GET_STRING_ELEMENT(data, "user" );
bool remote = GET_BOOL_ELEMENT (data, "remote" );
string host = GET_STRING_ELEMENT(data, "host" );
string pass = GET_STRING_ELEMENT(data, "pass" );
string database = GET_STRING_ELEMENT(data, "database" );
string privilege = GET_STRING_ELEMENT(data, "privilege");
PrintSilent("Adding permissions to user \'%s\' ... ", user.c_str());
TeDatabase* db = NewTeDatabase();
if( !db -> connect(Host, User, Password, "", Port))
error("Could not connect\n");
stringstream stream;
stream << "grant " << privilege << " ON " << database << ".* TO " << user;
if(!remote && host == "") stream << "@localhost";
else if(host != "") stream << "@" << host;
else stream << "@\'%\'";
if(pass != "")
stream << " IDENTIFIED BY \'" << pass << "\'";
stream << ";";
string sql = StreamToChar(stream);
// cout << sql << endl;
if( !db->execute(sql) )
{
string err = db->errorMessage();
delete db;
error( err.c_str() );
}
delete db;
PrintSilentYes;
return RNULL;
}
aRTcomponent* aRTconn::CreateDb(SEXP data)
{
string dbname = GET_STRING_ELEMENT(data, "dbname");
TeDatabase* database = NewTeDatabase();
PrintSilent("Creating database \'%s\' ... ", dbname.c_str());
if (!database -> newDatabase(dbname, User, Password, Host, Port))
{
string error_ = database -> errorMessage();
PrintSilentNo;
delete database;
error(error_.c_str());
}
PrintSilentYes;
PrintSilent("Creating conceptual model of database \'%s\' ... ", dbname.c_str());
if (!database->createConceptualModel(true, false))
{
string error_ = database -> errorMessage();
PrintSilentNo;
delete database;
error(error_.c_str());
}
PrintSilentYes;
PrintSilent("Creating application theme table \'%s\' ... ", dbname.c_str());
if(!createAppThemeTable(database))
{
string error_ = database -> errorMessage();
PrintSilentNo;
delete database;
error(error_.c_str());
}
PrintSilentYes;
aRTdb* root = new aRTdb(database);
return root;
}
aRTconn::aRTconn(string user, string pass, unsigned port, string host, string dbms)
{
PrintSilent("Trying to connect ... ");
User = user;
Password = pass;
Port = port;
Host = host;
if (dbms == "mysql") { Type = aRTmySQL; if(!port) Port=3306; }
else if(dbms == "postgre") { Type = aRTpostgres; if(!port) Port=5432; }
else if(dbms == "postgis") { Type = aRTpostgis; if(!port) Port=5432; }
//else error("Invalid database type: %s\n", dbms.c_str());
TeDatabase* database = NewTeDatabase();
// vector<string> db_names;
bool Valid = database->connect(Host, User, Password, "", Port);
if (!Valid)
{
PrintSilentNo;
string error_msg = database -> errorMessage();
delete database;
error(error_msg.c_str());
}
PrintSilentYes;
string query = "SELECT VERSION()";
TeDatabasePortal* portal = database->getPortal();
portal->query(query);
portal->fetchRow();
PrintSilent("Connected to %s version %s\n", dbms.c_str(), portal->getData(0));
delete database;
}
SEXP aRTconn::DropUser(SEXP data)
{
string user = GET_STRING_ELEMENT(data, "user" );
bool remote = GET_BOOL_ELEMENT (data, "remote" );
string host = GET_STRING_ELEMENT(data, "host" );
PrintSilent("Dropping user \'%s\' ... ", user.c_str());
TeDatabase* db = NewTeDatabase();
if( !db -> connect(Host, User, Password, "", Port))
error("Could not connect\n");
stringstream stream;
stream << "drop user " << user;
if(!remote && host == "") stream << "@localhost";
else if(host != "") stream << "@" << host;
else stream << "@\'%\'";
stream << ";";
string sql = StreamToChar(stream);
// cout << sql << endl;
if( !db->execute(sql) )
{
string err = db->errorMessage();
delete db;
error( err.c_str() );
}
delete db;
PrintSilentYes;
return RNULL;
}
bool TeCreateThemeFromTheme(TeTheme* inTheme, const string& newThemeName, int selObj, TeTheme* newTheme)
{
// if no source theme or not a name for the new theme
if (!inTheme || newThemeName.empty())
return false;
// if there no pointer theme was passed create a new instance
if (!newTheme)
newTheme = new TeTheme();
// builds a valid theme name
TeDatabase* curDb = inTheme->layer()->database();
string validThemeName = newThemeName;
int i=1;
while (curDb->themeExist(validThemeName))
{
validThemeName = newThemeName + "_" + Te2String(i);
i++;
}
// create new theme with the same definitions of the input theme
newTheme->id(0);
newTheme->name(validThemeName);
newTheme->layer(inTheme->layer());
newTheme->setAttTables(inTheme->attrTables());
curDb->viewMap()[inTheme->view()]->add(newTheme);
newTheme->visibleRep(inTheme->visibleRep());
// save its definition in the database
// creates collection and collection_aux table
if (!newTheme->save())
{
delete newTheme;
newTheme = 0;
return false;
}
// if its a theme of raster data there's nothing else to be done
if (inTheme->layer()->hasGeometry(TeRASTER) || inTheme->layer()->hasGeometry(TeRASTERFILE))
{
TeBox tbox = curDb->getThemeBox(newTheme);
newTheme->setThemeBox(tbox);
curDb->updateTheme(newTheme);
return true;
}
// this SQL selects instances of objects according to input, so it uses te_collection_aux
string inputInst;
// this SQL selects objects according to input, so it uses te_collection
string inputObj;
if(selObj == TeSelectedByPointing) // queried
{
inputObj = "(" + inTheme->collectionTable() + ".c_object_status = 2";
inputObj += " OR " + inTheme->collectionTable() + ".c_object_status = 3)";
inputInst = "(" + inTheme->collectionAuxTable() + ".grid_status = 2";
inputInst += " OR " + inTheme->collectionAuxTable() + ".grid_status = 3)";
}
else if(selObj == 2) // pointed
{
inputObj = "(" + inTheme->collectionTable() + ".c_object_status = 1";
inputObj += " OR " + inTheme->collectionTable() + ".c_object_status = 3)";
inputInst = "(" + inTheme->collectionAuxTable() + ".grid_status = 1";
inputInst += " OR " + inTheme->collectionAuxTable() + ".grid_status = 3)";
}
else if(selObj == 3) // not queried
{
inputObj = "(" + inTheme->collectionTable() + ".c_object_status <> 2";
inputObj += " AND " + inTheme->collectionTable() + ".c_object_status <> 3)";
inputInst = "(" + inTheme->collectionAuxTable() + ".grid_status <> 2";
inputInst += " AND " + inTheme->collectionAuxTable() + ".grid_status <> 3)";
}
else if(selObj == 4) // not pointed
{
inputObj = "(" + inTheme->collectionTable() + ".c_object_status <> 1";
inputObj += " AND " + inTheme->collectionTable() + ".c_object_status <> 3)";
inputInst = "(" + inTheme->collectionAuxTable() + ".grid_status <> 1";
inputInst += " AND " + inTheme->collectionAuxTable() + ".grid_status <> 3)";
}
else if(selObj == 5) // pointed and queried
{
inputObj = "(" + inTheme->collectionTable() + ".c_object_status = 3)";
inputInst = "(" + inTheme->collectionAuxTable() + ".grid_status = 3)";
}
else if(selObj == 6) // pointed or queried
{
inputObj = "(" + inTheme->collectionTable() + ".c_object_status = 2";
inputObj += " OR " + inTheme->collectionTable() + ".c_object_status = 1";
inputObj += " OR " + inTheme->collectionTable() + ".c_object_status = 3)";
inputInst = "(" + inTheme->collectionAuxTable() + ".grid_status = 2";
inputInst += " OR " + inTheme->collectionAuxTable() + ".grid_status = 1";
inputInst += " OR " + inTheme->collectionAuxTable() + ".grid_status = 3)";
}
// fills collection table from the selection requested
string sql = "INSERT INTO " + newTheme->collectionTable() + " ( ";
sql += " c_object_id, c_legend_id, label_x, label_y, c_legend_own, c_object_status ) ";
sql += " SELECT * FROM " + inTheme->collectionTable();
if(inputObj.empty() == false)
sql += " WHERE " + inputObj;
if (!curDb->execute(sql))
{
curDb->deleteTheme(newTheme->id());
return false;
}
// sets theme objects settings to default
sql = "UPDATE " + newTheme->collectionTable() + " SET c_legend_id = 0";
if (!curDb->execute(sql))
{
curDb->deleteTheme(newTheme->id());
return false;
}
sql = "UPDATE " + newTheme->collectionTable() + " SET c_object_status = 0";
if (!curDb->execute(sql))
{
curDb->deleteTheme(newTheme->id());
return false;
}
//fills collectin aux
if(!newTheme->populateCollectionAux())
{
curDb->deleteTheme(newTheme->id());
return false;
}
TeBox tbox = curDb->getThemeBox(newTheme);
newTheme->setThemeBox(tbox);
curDb->updateTheme(newTheme);
return true;
}
// Try to open an existent database
aRTcomponent* aRTconn::OpenDb(SEXP data)
{
bool silent = Silent; // **
string dbname = GET_STRING_ELEMENT(data, "dbname");
bool update = GET_BOOL_ELEMENT(data, "update");
PrintSilent("Connecting to database \'%s\' ... ", dbname.c_str());
Silent = false; // **
TeDatabase* database = NewTeDatabase();
// ** things doesn't work fine if this line executes with Silent == true
// ** strange... I've already tried to change the name of this variable.
if (database -> connect(Host, User, Password, dbname, Port))
{
Silent = silent; // **
PrintSilentYes;
string DBversion;
TeDatabasePortal* portal = database->getPortal();
if(!portal)
return false;
string sql = " SELECT db_version FROM te_database ";
//The database does not have the te_database connection
if(!portal->query(sql))
{
DBversion = "";
delete portal;
return NULL;
}
if(!portal->fetchRow())
{
DBversion = "";
delete portal;
return NULL;
}
DBversion = portal->getData(0);
PrintSilent("Connected to database version %s\n", DBversion.c_str());
string version, error_msg;
if( needUpdateDB(database, version) )
{
if(update)
{
PrintSilent("Updating database \'%s\' to version %s ... ", dbname.c_str(), TeDBVERSION.c_str());
if( updateDBVersion(database, version, error_msg) )
{ PrintSilentYes; }
else
error("Could not update the database version: %s\n", error_msg.c_str());
}
else warning("Database needs to be updated to version %s. Call this function again with update=TRUE, otherwise things may not work correctly\n", TeDBVERSION.c_str());
}
}
else
{
Silent = silent; // **
PrintSilentNo;
error("Database \'%s\'does not exist.", dbname.c_str());
}
aRTdb* root = new aRTdb(database);
return root;
}
SEXP aRTconn::GetPermissions(SEXP data)
{
SEXP result;
SEXP *each_column;
SEXP colnames;
SEXP rownames;
string query;
string user = GET_STRING_ELEMENT(data, "user" );
bool global = GET_BOOL_ELEMENT (data, "global");
TeDatabase* db = NewTeDatabase();
if( !db -> connect(Host, User, Password, "", Port))
error("Could not connect\n");
TeDatabasePortal* portal = db->getPortal();
if(global)
query = "select Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv from mysql.user";
else
query = "select Host,User,Db,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv from mysql.db";
if(user != "")
{
query += " where user= \"";
query += user;
query += "\";";
}
else
{
query += ";";
}
portal->query(query);
each_column = new SEXP[8];
colnames = PROTECT(allocVector( STRSXP, 8 ));
result = PROTECT(allocVector( VECSXP, 8 ));
rownames = PROTECT(allocVector( STRSXP, portal->numRows() ));
for(int i = 0; i < 8; i++)
each_column[i] = PROTECT(allocVector( STRSXP, portal->numRows() ));
SET_STRING_ELT(colnames, 0, mkChar("host" ));
SET_STRING_ELT(colnames, 1, mkChar("user" ));
SET_STRING_ELT(colnames, 2, mkChar(global? "password": "******"));
SET_STRING_ELT(colnames, 3, mkChar("select" ));
SET_STRING_ELT(colnames, 4, mkChar("insert" ));
SET_STRING_ELT(colnames, 5, mkChar("update" ));
SET_STRING_ELT(colnames, 6, mkChar("delete" ));
SET_STRING_ELT(colnames, 7, mkChar("create" ));
for(int i = 0; i != portal->numRows(); i++)
{
portal->fetchRow();
string host = portal->getData(0);
if(host == "%") host = "<any>";
SET_STRING_ELT( each_column[0], i, mkChar(host.c_str()) );
// user
SET_STRING_ELT( each_column[1], i, mkChar(portal->getData(1)) );
if(global)
{
string password = portal->getData(2);
password = password == ""? "No" : "Yes";
SET_STRING_ELT( each_column[2], i, mkChar(password.c_str()) );
}
else
{
string db = portal->getData(2);
SET_STRING_ELT( each_column[2], i, mkChar(db.c_str()) );
}
for(unsigned j = 3; j != 8; j++)
{
string value = portal->getData(j);
if(value == "Y") value = "Yes";
if(value == "N") value = "No";
SET_STRING_ELT( each_column[j], i, mkChar(value.c_str()) );
}
stringstream str;
str << i+1; // rownames must start from 1
SET_STRING_ELT(rownames, i, mkChar(str.str().c_str()));
}
setAttrib(result, R_NamesSymbol, colnames);
for(unsigned j = 0; j != 8; j++)
SET_VECTOR_ELT(result, j, each_column[j]);
result = AsDataFrame(result, rownames);
UNPROTECT(11);
delete[] each_column;
delete db;
return result;
}
string
TeQuerierDB::sqlWhereRestrictions(TeRepresentation* rep)
{
TeKeys objs;
string whereClause= " 1 = 1 ";
TeDatabase* db = params_->theme()->layer()->database();
if(!db)
return "";
// load the first representation
if(!rep)
rep = (params_->theme()->layer()->vectRepres())[0];
// spatial restriction with other geometry representation
if (params_->hasSpatialRes() && rep)
{
if(params_->boxRest().isValid())
{
TeBox b = params_->boxRest();
TeGeomRep gRep = rep->geomRep_;
string geomTableRest = params_->theme()->layer()->tableName(params_->geomRepRest());
whereClause += " AND "+ db->getSQLBoxWhere(b, gRep, geomTableRest);
}
else if(params_->geomRest())
{
string geomTableRest = params_->theme()->layer()->tableName(params_->geomRepRest());
TePrecision::instance().setPrecision(TeGetPrecision(params_->theme()->layer()->projection()));
if((db->spatialRelation(geomTableRest, params_->geomRepRest(), params_->geomRest(),
objs, params_->spatialRelation())) && (!objs.empty()))
{
string obs;
for(unsigned int i=0; i<objs.size(); i++)
{
if(i!=0)
obs += ",";
obs += "'"+ objs[i] +"'";
}
whereClause += " AND "+ rep->tableName_ +".object_id IN ("+ obs +")";
}
else
whereClause += " AND 1 <> 1 "; // no geometry was found
}
}
//selected objects
switch (params_->selectedObjs())
{
case TeAll:
break;
case TeSelectedByPointing:
whereClause += " AND (grid_status = 1 OR grid_status = 3";
whereClause += " OR (grid_status IS NULL AND (c_object_status = 1 OR c_object_status = 3)))";
break;
case TeNotSelectedByPointing:
whereClause += " AND (grid_status = 0 OR grid_status = 2";
whereClause += " OR (grid_status is null AND (c_object_status = 0 OR c_object_status = 2)))";
break;
case TeSelectedByQuery:
whereClause += " AND (grid_status = 2 OR grid_status = 3";
whereClause += " OR (grid_status is null AND (c_object_status = 2 OR c_object_status = 3)))";
break;
case TeNotSelectedByQuery:
whereClause += " AND (grid_status = 0 OR grid_status = 1";
whereClause += " OR (grid_status is null AND (c_object_status = 0 OR c_object_status = 1)))";
break;
case TeGrouped:
whereClause += " AND c_legend_id <> 0";
break;
case TeNotGrouped:
whereClause += " AND c_legend_id = 0";
break;
case TeSelectedByPointingAndQuery:
whereClause += " AND grid_status = 3";
whereClause += " OR (grid_status is null AND c_object_status = 3)";
break;
case TeSelectedByPointingOrQuery:
whereClause += " AND (grid_status = 1 OR grid_status = 2 OR grid_status = 3)";
whereClause += " OR (grid_status is null AND (c_object_status = 3 OR c_object_status = 1 OR c_object_status = 2))";
break;
}
return whereClause;
}
