예제 #1
0
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;
}
예제 #5
0
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;
}
예제 #6
0
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;
}
예제 #7
0
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;
}
예제 #8
0
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;
}
예제 #10
0
// 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;
}
예제 #11
0
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;
}
예제 #12
0
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;
}