C++ TeDatabasePortal::query方法代码示例

bool needUpdateDB(TeDatabase* db, string& DBversion)
{
	TeDatabasePortal* portal = db->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 false;
	}

	if(!portal->fetchRow())
	{
		DBversion = "";
		delete portal;
		return true;
	}

	DBversion = portal->getData(0);
	if(DBversion==TeDBVERSION)
	{
		delete portal;
		return false;
	}
	
	delete portal;
	return true;
}

bool TeSQLite::insertRelationInfo(const int tableId, const string& tField, const string& rTable, const string& rField, int& relId)
{
	if(tableId < 0)
	{
		return false;
	}
	// check if relation already exists
	TeDatabasePortal* portal = this->getPortal();

	if(!portal)
		return false;

	relId = -1;
	
	string sel = "SELECT relation_id FROM te_tables_relation WHERE";
	sel += " related_table_id = " + Te2String(tableId);
	sel += " AND related_attr = '" + tField + "'";
	sel += " AND external_table_name = '" + rTable + "'";
	sel += " AND external_attr = '" + rField + "'";

	if(!portal->query(sel))
	{
		delete portal;
		return false;
	}

	if(portal->fetchRow())
	{
		relId = atoi(portal->getData("relation_id"));
		delete portal;
		return true;
	}

	delete portal;

	string sql =  "INSERT INTO te_tables_relation (related_table_id, related_attr, external_table_name, external_attr) VALUES(";
	       sql += Te2String(tableId);
		   sql += ", '";
		   sql += tField;
		   sql += "', '";
		   sql += rTable;
		   sql += "', '";
		   sql += rField;
		   sql += "')";

	if(this->execute(sql))
	{
		//int newId = getMaxValue(this, "te_tables_relation", "relation_id");
		int newId = getLastInsertedSerial();
		if(newId >= 0)
		{
			relId = newId;
		}
	}
	else
		return false;

	return true;
}

bool TeSQLite::getIndexesFromTable(const string& tableName, std::vector<TeDatabaseIndex>& vecIndexes)
{
	TeDatabasePortal* portal = getPortal();

	std::string sqlIndexes = "PRAGMA index_list(" + tableName + ");";
	if(portal->query(sqlIndexes) == false)
	{
		delete portal;
		return false;
	}

	while(portal->fetchRow())
	{
		TeDatabaseIndex dbIndex;
		dbIndex.setIndexName(portal->getData("name"));

		vecIndexes.push_back(dbIndex);
	}

	portal->freeResult();

	for(unsigned int i = 0; i < vecIndexes.size(); ++i)
	{
		std::string sqlIndex = "PRAGMA index_info(" + vecIndexes[i].getIndexName() + ");";

		if(portal->query(sqlIndex) == false)
		{
			delete portal;
			return false;
		}

		std::vector<std::string> vecColumns;
		while(portal->fetchRow())
		{
			vecColumns.push_back(portal->getData("name"));
		}
		portal->freeResult();

		vecIndexes[i].setColumns(vecColumns);		
	}

	delete portal;

	return true;
}

bool TeSQLite::getAttributeList(const string& tableName,TeAttributeList& attList)
{
	if(!tableExist(tableName))
	{
		return false;
	}

	TeDatabasePortal* portal = this->getPortal();
	if (!portal)
		return false;

	string sql = "PRAGMA table_info(" + tableName + ")";
	if (!portal->query(sql))
	{
		delete portal;
		return false;
	}

	while(portal->fetchRow())
	{
		TeAttribute attr;
		attr.rep_.name_ = portal->getData("name");
		attr.rep_.isPrimaryKey_ = portal->getBool("pk");
		attr.rep_.null_ = !portal->getBool("notnull");
		attr.rep_.defaultValue_ = portal->getData("dflt_value");

		std::string type = portal->getData("type");
		if(type == "TEXT")
		{
			attr.rep_.type_ = TeSTRING;
		}
		else if(type == "INTEGER")
		{
			attr.rep_.type_ = TeINT;
		}
		else if(type == "REAL")
		{
			attr.rep_.type_ = TeREAL;
		}
		else if(type == "BLOB")
		{
			attr.rep_.type_ = TeBLOB;
		}
		else
		{
			attr.rep_.type_ = TeSTRING;
		}

		attList.push_back(attr);
	}

	delete portal;

	return true;
}

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;
}

bool updateDB30To301(TeDatabase* db, string& errorMessage)
{
	TeAttribute fattr;

	TeDatabasePortal* portal = db->getPortal();
	if(!portal)
		return false;

	// ----- collection_table

	string sql = " SELECT collection_table FROM te_theme ";   
	if(!portal->query(sql))
	{
		delete portal;
		return false;
	}

	while(portal->fetchRow())
	{
		string collName = portal->getData(0);
		if(	(db->tableExist(collName)) && 
			(!db->columnExist(collName, "c_object_status", fattr)))
		{
			TeAttributeRep atRep;
			atRep.type_ = TeINT;
			atRep.name_ = "c_object_status";
			if(db->addColumn(collName, atRep) == false)
			{
				errorMessage = "The column c_object_status could not be appended!\n";
				errorMessage += db->errorMessage();
				delete portal;
				return false;
			}

			string upd = " UPDATE "+collName+" t1, "+collName+" t2 ";
			upd +=	" SET t1.c_object_status = t2.c_legend_result ";
			upd +=	" WHERE t1.c_object_id = t2.c_object_id ";

			if(!db->execute (upd))
			{
				errorMessage = db->errorMessage();
				delete portal;
				return false;
			}

			db->deleteColumn(collName, "c_legend_result");
		}
	}

	delete portal;
	return true; 
}

/// UTILIITARY FUNCTION - Deletes a table from a TerraLib geographical database.
/// \param db is a pointer to a TerraLib database
/// \param tableName is the name of the table being removed
/// \return Return true in case of sucess, otherwise it returns false. 
bool deleteLayerTableName ( TeDatabase *db, std::string &tableName )
{
    TeDatabasePortal* portal = db->getPortal();

    if( !portal )
        return false;

    string query = "SELECT attr_table, table_id FROM te_layer_table WHERE attr_table = '" + tableName + "'";

    if( !portal->query( query ) )
    {
        delete portal;
        return false;
    }

    vector<int> tableIds;
    string attrTable;
    string tableId;
    string drop;
    while ( portal->fetchRow() )
    {
        attrTable = portal->getData(0);
        tableId = portal->getData(1);
        //drop = "DROP TABLE " + attrTable;
        if( db->tableExist( attrTable ) )
        {
            if( !db->deleteTable( attrTable ) ) //if( !db->execute( drop ) )
            {
                cout << "Error: fail to delete table \"" << attrTable
                     << db->errorMessage() << endl;
                db->close();
                delete portal;
                return false;
            }
        }
        tableIds.push_back( atoi ( tableId.c_str() ) );

        string del = "DELETE FROM te_layer_table WHERE table_id = "+ tableId;
        db->execute(del);
    }

    delete portal;
    string del;
    del = "DELETE FROM te_layer_table WHERE attr_table = '" + tableName + "'";
    if ( !db->execute ( del ) )
        return false;
    return true;
} 

int getMaxValue(TeDatabase* database, const std::string& tableName, const std::string& intColumnName)
{	
	TeDatabasePortal* portal = database->getPortal();

	std::string sql = "SELECT MAX(" + intColumnName + ") FROM " + tableName;
	
	int maxVal = 0;
	if(portal->query(sql) && portal->fetchRow())
	{
		maxVal = portal->getInt(0);
	}

	delete portal;

	return maxVal;
}

bool TeSQLite::tableExist(const string& table)
{
	std::string sql = "SELECT tbl_name FROM sqlite_master";
	sql += " WHERE upper(tbl_name) = upper('" + table + "')";
	sql += " AND type = 'table'";

	bool exists = false;

	TeDatabasePortal* portal = getPortal();
	if(portal->query(sql) && portal->fetchRow())
	{
		exists = true;
	}

	delete portal;

	return exists;
}

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;
}

bool updateDB331To400(TeDatabase* db, string& errorMessage)
{
	TeDatabasePortal* portal = db->getPortal();
	if(!portal)
		return false;

	string sql = " SELECT geom_table FROM te_representation WHERE geom_type = 512 ";

//The database does not have the te_database connection
	if(!portal->query(sql))
	{
		delete portal;
		return false;
	}

	while(portal->fetchRow())
	{
		std::string	rasterTableName = portal->getData(0);

		TeDatabasePortal* portalRaster = db->getPortal();

		if(!portalRaster)
		{
			delete portal;
			return false;
		}

		if(db->tableExist(rasterTableName) == false) 
			continue;

		string sql = " SELECT lut_table FROM " +  rasterTableName;

//The database does not have the te_database connection
		if(!portalRaster->query(sql))
		{
			delete portal;
			delete portalRaster;
			return false;
		}

		while(portalRaster->fetchRow())
		{
			TeAttribute attr;

// ----- raster_metadata
			std::string rasterMetadaTable = rasterTableName + "_metadata";

			if(db->columnExist(rasterMetadaTable, "band_name", attr) == false)
			{
				TeAttributeRep attRep;
				attRep.name_ = "band_name";
				attRep.type_ = TeSTRING;
				attRep.numChar_ = 255;
								
				if(db->addColumn(rasterMetadaTable, attRep) == false)
				{
					errorMessage = "The column band_name could not be appended!\n";
					errorMessage += db->errorMessage();
					delete portal;
					delete portalRaster;
					return false;
				}
			}

// ----- raster_lut
			std::string rasterLutTable = portalRaster->getData(0);

			if(db->tableExist(rasterLutTable))
			{
				if(db->columnExist(rasterLutTable, "class_name", attr) == false)
				{
					TeAttributeRep attRep;
					attRep.name_ = "class_name";
					attRep.type_ = TeSTRING;
					attRep.numChar_ = 255;
										
					if(db->addColumn(rasterLutTable, attRep) == false)
					{
						errorMessage = "The column class_name could not be appended!\n";
						errorMessage += db->errorMessage();
						delete portal;
						delete portalRaster;
						return false;
					}
				}
			}
		}

		delete portalRaster;
	}

	delete portal;

	return true;
}

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": "db"));
	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;

//.........这里部分代码省略.........

bool PostgreSQLUpdateDB302To310(TeDatabase* db, TeGeomRep rep, const string& geomTableName)
{
	string geomColumnName = "spatial_box";

	if(rep & TeRASTER)
		geomColumnName = "block_box";

	TeAttribute attr;

// verifies if table already has a box column
	if(db->columnExist(geomTableName, geomColumnName, attr))
		return true;

// add box column
	string sql  = "ALTER TABLE " + geomTableName;
			sql += " ADD COLUMN " + geomColumnName + " BOX";

	if(!db->execute(sql))
		return false;

// make sure box column exist	
	if(!db->columnExist(geomTableName, geomColumnName, attr))
		return false;

// populate column with values from older one
	sql  = "UPDATE " + geomTableName;

	if(rep & TePOINTS)
		sql += " SET " + geomColumnName + " = box(point(x, y), point(x, y))";
	else
		sql += " SET " + geomColumnName + " = box(point(upper_x, upper_y), point(lower_x, lower_y))";

	if(!db->execute(sql))
		return false;

// check if there is no null values
	sql  = "SELECT * FROM " + geomTableName;
	sql += " WHERE " + geomColumnName + " IS NULL";

	TeDatabasePortal* p = db->getPortal();

	if(!p)
		return false;

	 if(!p->query(sql))
	 {
		 delete p;

		 return false;
	 }

     if(p->fetchRow())
     {
         delete p;

         return false;
	 }

     delete p;

// add not null for box column
	sql  = "ALTER TABLE " + geomTableName;
	sql += " ALTER COLUMN " + geomColumnName + " SET NOT NULL";

	if(!db->execute(sql))
		return false;
	
// create GiST index
	if(!db->createSpatialIndex(geomTableName, geomColumnName))
		return false;

// for points is enough
	if(rep & TePOINTS)
		return true;

// drop older B-Tree index
	sql = "DROP INDEX " +  geomTableName + "_idx_box";

	if(!db->execute(sql))
		return false;

// drop older box column
	sql  = "ALTER TABLE " + geomTableName;
	sql += " DROP COLUMN lower_x";

	if(!db->execute(sql))
		return false;

	sql  = "ALTER TABLE " + geomTableName;
	sql += " DROP COLUMN lower_y";

	if(!db->execute(sql))
		return false;

	sql  = "ALTER TABLE " + geomTableName;
	sql += " DROP COLUMN upper_x";

	if(!db->execute(sql))
		return false;

//.........这里部分代码省略.........

//.........这里部分代码省略.........
// update polygon geometry table
			rep = itLayer->second->getRepresentation(TePOLYGONS);

			if(rep)
			{
				string geomTableName = rep->tableName_;

				if(!PostgreSQLUpdateDB302To310(db, TePOLYGONS, geomTableName))
				{
					db->execute("ROLLBACK TRANSACTION");

					return false;
				}
			}

// update raster geometry table
			rep = itLayer->second->getRepresentation(TeRASTER);

			if(rep)
			{
				string rasterGeomTableName = rep->tableName_;

				string sql = "SELECT raster_table FROM " + rasterGeomTableName;

				TeDatabasePortal* p = db->getPortal();

				if(!p)
				{
					db->execute("ROLLBACK TRANSACTION");

					return false;
				}

				if(!p->query(sql))
				{
					delete p;

					db->execute("ROLLBACK TRANSACTION");

					return false;
				}

				while(p->fetchRow())
				{
					string geomTableName = p->getData(0);

					if(!PostgreSQLUpdateDB302To310(db, TeRASTER, geomTableName))
					{
						delete p;

						db->execute("ROLLBACK TRANSACTION");

						return false;
					}
				}

				delete p;
			}

// update cell geometry table
			rep = itLayer->second->getRepresentation(TeCELLS);

			if(rep)
			{
				string geomTableName = rep->tableName_;

bool updateDB301To302(TeDatabase* db, string& errorMessage)
{
		
	TeDatabasePortal* portal = db->getPortal();
	if(!portal)
		return false;

	// ----- valid attribute table 
	
	TeAttrTableVector attrTableVec;
	if(db->getAttrTables(attrTableVec))
	{
		for(unsigned int i=0; i<attrTableVec.size(); ++i)
		{
			TeTable fromTable = attrTableVec[i];
			bool	flag = false;
			
			if(fromTable.tableType()==TeAttrMedia)
				continue;

			//verify if there is another table with the same name
			for(unsigned int j=0; j<i; ++j)
			{
				if(TeConvertToUpperCase(attrTableVec[j].name())==TeConvertToUpperCase(fromTable.name())) 
				{
					flag = true;
					break;
				}
			}

			if(flag)
				continue;
			
			if(db->validTable(fromTable)) //fromTable was modified
			{
				TeAttributeList newAttrList = fromTable.attributeList();
				TeAttributeList oldAttrList = attrTableVec[i].attributeList();
				TeAttributeList::iterator newAttIt = newAttrList.begin();
				TeAttributeList::iterator oldAttIt = oldAttrList.begin();

				bool change = false;
				while(newAttIt!=newAttrList.end())
				{
					if(((*oldAttIt).rep_.name_) != ((*newAttIt).rep_.name_))
					{
						TeAttributeRep rep = (*newAttIt).rep_;
						if(db->alterTable(fromTable.name(), rep, (*oldAttIt).rep_.name_))
							change = true;
					}

					++newAttIt;
					++oldAttIt;
				}

				if(change)
				{
					// update te_layer_table
					string upd = " UPDATE te_layer_table ";
					upd +=	" SET unique_id = '"+ fromTable.uniqueName() +"'";
					upd +=  ", attr_link = '"+ fromTable.linkName() +"'";
					upd +=	" WHERE attr_table = '"+ fromTable.name() +"'";

					if(!db->execute (upd))
					{
						delete portal;
						errorMessage = "Error updating te_layer_table!\n";
						errorMessage += db->errorMessage();
						return false;
					}
				}
			}//if
		}//for
		portal->freeResult();
	}

	// ----- te_grouping table

	string sel = " SELECT theme_id, grouping_attr, grouping_attr_type FROM te_grouping ";
	if(!portal->query(sel))
	{
		delete portal;
		return false;
	}

	while(portal->fetchRow())
	{
		string themeId, gAttr;
		themeId = portal->getData(0);
		gAttr = portal->getData(1);
		int gTypr = portal->getInt(2);
		
		if(((gAttr.empty()) || (gAttr=="NONE")) && (gTypr==0))
		{
			string del = " DELETE FROM te_grouping WHERE theme_id = "+ themeId; 
			db->execute(del);
		}
	}

	// ----- te_grouping table

//.........这里部分代码省略.........