C++ DBManager类代码示例

//! Get Ascii file headers information
void clsRasterData::getASCHeaders(string databasePath,map<string,float>* headers)
{
	DBManager dbman;
	string sql;
	slTable* tbl;

	// open the database
	dbman.Open(databasePath + File_ParameterDB);
	// if there is not an error
	if(dbman.IsError()) throw ModelException("clsRasterData","getASCHeaders","Can't open paramter database!");

	// constrcut the SQL statement for the query
	sql = "SELECT Parameter,Value FROM Header";
	// run the query
	tbl = dbman.Load(sql);
	if (tbl->nRows == 0) throw ModelException("ModuleParamter","getParameterFromDatabase","Can't find ASC Headers in paramter database!");
	
	headers->clear();

    //headers = new map<string,float>();

	for(int i=1;i<=tbl->nRows;i++)
	{
		(*headers)[tbl->FieldValue(i, 1)] = float(atof(tbl->FieldValue(i, 2).c_str()));
	}	

	delete tbl;
	tbl = NULL;

	dbman.Close();
}

int main(int argc, char** argv)
{
    QApplication app(argc, argv);

    new UIManager;
    new DBManager;

    DBManager* db = DBManager::instance();
    UIManager* ui = UIManager::instance();

    //char* fn = "vega.udb";
    char* fn = "geometryprocess.udb";
    //char* fn = "vega.udb";
    //char* fn = "vega.udb";

    char* fileName = argc > 1 ? argv[1] : fn;
    printf(fileName);

    db->openDB(fileName);

    ui->lockAll();
    db->parse();
    ui->unlockAll();

    ui->updateScene();
    ui->addMainUI();
    //ui->addMainUI();
    ui->showAllUI();

    app.exec();
    DBManager::instance()->closeDB();

    return 0;
}

ManageClassWindow::ManageClassWindow(QMainWindow * parent ) : QMainWindow (parent) {
    this->resize(350 , 110);
    DBManager dbManager ;
    QVector<Student> students = dbManager.getStudentList();
    QStringList studentsName ;
    foreach (Student temp, students ) {
        studentsName.append(QString (temp.getName()+ " " + temp.getFamily())
                            + QString(" (class %1)").arg(QString::number(temp.getClassID())));
    }

void BMPReach::loadTimeSeriesData(string databasePath,time_t startTime, time_t endTime,int interval)
{
	if(!HasTimeSeriesData()) return;

	string tableName = TimeSeriesDataTableName();
	if(tableName.length() == 0)
	{
		ostringstream oss;
		oss << "The data source for " << m_bmpName << m_reachStructureId << "is empty.";
		throw ModelException("BMPReach","loadTimeSeriesData",oss.str());
	}
		
	string hydroClimatePath = databasePath + File_HydroClimateDB;
	if(!DBManager::IsTableExist(hydroClimatePath,tableName))
		throw ModelException("BMPReach","loadTimeSeriesData","The hydroclimate database '" + hydroClimatePath + 
		"' does not exist or the there is not a table named '" + tableName + "' in hydroclimate database.");

	DBManager db;
	db.Open(hydroClimatePath);
	try
	{
		utils util;
		string sStart = util.ConvertToString(&startTime);
		string sEnd = util.ConvertToString(&endTime);
		string strSQL = "SELECT * FROM " + tableName + " WHERE [DATE] >= DATE('"+sStart+"') AND [DATE] <= DATE('"+sEnd+"')";
	
		slTable* tbl = db.Load(strSQL);
		if(tbl->nRows > 0)
		{
			int dateColIndex = 1;
			for(int j=0;j<tbl->nCols;j++)
			{
				string col = tbl->FieldValue(0,j);
				int	colType = TimeSeriesDataName2Type(col);
				if(colType == BMP_REACH_UNKONWN_TYPE) continue;

				for(int i=1;i<=tbl->nRows;i++)
				{
					time_t t = util.ConvertToTime(tbl->FieldValue(i,dateColIndex), "%4d-%2d-%2d", false);
					float value = float(atof(tbl->FieldValue(i,j).c_str()));
					m_timeSerieseData[colType][t] = value;
				}
			}
		}

		delete tbl;
		db.Close();
	}
	catch(...)
	{
		db.Close();
		throw;
	}
}

// ==========================================================================
// METHOD ModuleDB::init
//
// TODO: detect double class registration
// ==========================================================================
bool ModuleDB::init (const value &reloadmods)
{
	value cache;
	
	DBManager db;
	if (! db.init ())
	{
		log::write (log::critical, "ModuleDB", "Could not init database");
		CORE->delayedexiterror ("Error initializing database");
		return false;
	}
	
	// Indicate to the database that we're bypassing the authorization layer
	// for this primary exercise.
	db.enableGodMode();

	// Load the module.cache file, we will use this to track module versions
	// between opencore runs.
	string cachepath = PATH_CACHES;
	cachepath.strcat ("/module.cache");
	
	// Make sure the module cache exists at all, if not, we will create
	// an empty cache.
	if (! fs.exists (cachepath))
	{
		log::write (log::info, "ModuleDB", "No module cache found, assuming "
				   "new install");
				   
		cache["modules"]; // Initialize cache's tree structure.
		
		// If we can't write, consider that a dealbreaker.
		if (! cache.saveshox (cachepath))
		{
			log::write (log::critical, "ModuleDB", "Can not write to module "
					   "cache at <%s>, bailing." %format (cachepath));
			CORE->delayedexiterror ("Error saving module.cache");
			sleep (2);
			exit (1);
		}
	}
	else
	{
		cache.loadshox (cachepath);
		if (! cache.count())
		{
			log::write (log::warning, "ModuleDB", "Loaded module cache "
					   "seems to be empty");
		}
	}
	
	foreach (mod, reloadmods)
	{
		cache.rmval (mod);
	}

bool PingFlow::erase(DBManager &dbm)
{
    char sql[500];
    sprintf(sql, "DELETE FROM %s WHERE `pingId`='%d';", this->get_table_name().c_str(), this->get_ping_id());
    dbm.sql_execute(sql);
    return true;
}

bool PingFlow::update(DBManager &dbm)
{
    char sql[500];
    sprintf(sql, "UPDATE %s SET `sendPackage`='%d', `receivePackage`='%d', `lostPackage`='%d', `minTime`='%d', `maxTime`='%d', `averageTime`='%d', `errorMessage`='%s', `pingTime`='FROM_UNIXTIME(%ld)'WHERE `pingId`='%d';",\
            this->get_table_name().c_str(),  this->send_package, this->receive_package, this->lost_package, this->min_time, this->max_time, this->average_time, this->error_message, this->ping_time, this->ping_id);
    dbm.sql_execute(sql);
    return true;
}

int main(int argc, char *argv[])
{
    int currentExitCode = 0;
    do
    {
        QApplication a(argc, argv);
        a.setWindowIcon(QIcon(":/general/icons/New-climber.png"));
        DBManager db;
        if (!db.openDB())
            QMessageBox::critical(0, "Base de dados", "Não foi possível conectar ao banco de dados\n"
                                  "Entre em contato com o desenvolvedor", QMessageBox::Cancel);
        MainWindow w;
        w.show();
        currentExitCode = a.exec();
    }while(currentExitCode == MainWindow::EXIT_CODE_REBOOT);

    return currentExitCode;
}

bool PingFlow::create(DBManager &dbm)
{
    char sql[500];
    sprintf(sql, "INSERT INTO %s (`sendPackage`, `receivePackage`, `lostPackage`, `minTime`, `maxTime`, `averageTime`, `errorMessage`, 'pingTime') VALUES \
                                    ('%d', '%d', '%d', '%d', '%d', '%d', '%s', 'FROM_UNIXTIME(%ld)')", \
            this->get_table_name().c_str(),  this->send_package, this->receive_package, this->lost_package, this->min_time, this->max_time, this->average_time, this->error_message, this->ping_time);
    dbm.sql_execute(sql);
    return true;
}

std::vector<ObjectFoundation*> PingFlow::find_all(DBManager &dbm)
{
    char sql[500];
    sprintf(sql, "SELECT * FROM %s;", this->get_table_name().c_str());
    std::cout << sql << std::endl;
    dbm.sql_execute(sql);
    m_sql_list sql_list = dbm.get_sql_res();
    m_sql_object sql_object;
    std::vector<ObjectFoundation*> pfl;
    if (sql_list.size()==0) {
        std::cout << "Object not found!" << std::endl;
    } else {
        m_sql_list::iterator iter = sql_list.begin();
        for (; iter != sql_list.end(); iter++) {
            pfl.push_back(this->to_object(*iter));
        }
    }
    return pfl;
}

int main(int argc, char* argv[]) {
	logger.init("log.out");

	// arguments are threshold1, threshold2, case_sensitive, filename
	if (!ldp.init(0.85f, 0.35f, false, "MRCONSO.RRF")) {
		logger.write("Error loading dictionary.");
		exit(1);
	}
	
	//****************....finished preloading....**************************
	logger.write("Preloading finished, waiting for new jobs.");
	
	// init database connection
	DBManager dbm = DBManager("localhost", "user_name", "password", "db_name", 1);
	while (true) {
		vector<int> job_ids = dbm.check_jobs();
		
		// if new jobs exists
		if (job_ids.size() > 0) {
			
			// update jobs as processing
			for (vector<int>::size_type i = 0; i != job_ids.size(); i++) {
				dbm.update_processing(job_ids[i]);
			}
			
			// spawn enough threads to handle jobs
			for (vector<int>::size_type i = 0; i != job_ids.size(); i++) {
				boost::thread t(&worker_thread, job_ids[i]);
			}
		}
		
		sleep(2);
		job_ids.clear();
	}
	
	// clean up and exit
	dbm.close();
	
	return 0;
}

void worker_thread(const int job_id) {
	
	// init database connection
	DBManager dbm = DBManager("localhost", "user_name", "password", "db_name", 0);
	
	DBJob job = dbm.get_job(job_id);
	
	cout << "job terms: " << job.getTerms() << ", job type: " << job.getType() << endl;
	
	vector<string> queries = split(job.getTerms(), '$');
	vector<job_data> matches = ldp.get_matches(queries, job.getType());
	
	if (job.getType() == 1) {
		// build results as $-separated string
		string results_str = build_result_str(matches);
		cout << "result string: " << results_str << endl;
		dbm.update_completed_type134(job_id, results_str, job.getType());
	}
	else if (job.getType() == 2) {  // return 10 options for start and end terms
		dbm.update_completed_type2(job_id, matches[0].result, matches[1].result);
	}
	else if (job.getType() == 3 || job.getType() == 4) {
		dbm.update_completed_type134(job_id, matches[0].result, job.getType());
	}
	
	// clean up and close
	dbm.close();
}

void ActivityWizard::setVisible(bool visible) {
    if (visible) {
        DBManager manager;
        QSqlError error;
        if (mRecord.contains("id") && mRecord.value("id").toULongLong() != 0) {
            mActivity =
                manager.getActivity(mRecord.value("id").toULongLong(), error);
            if (error.type() != QSqlError::NoError) {
                QMessageBox msgBox;
                msgBox.setIcon(QMessageBox::Warning);
                msgBox.setText(
                    tr("There was an error while loading activity from the "
                       "database."));
                msgBox.setDetailedText(error.text());
                msgBox.setStandardButtons(QMessageBox::Ok);
                msgBox.setDefaultButton(QMessageBox::Ok);
                msgBox.exec();
                reject();
                return;
            }
            if (!mActivity) {
                QMessageBox msgBox;
                msgBox.setIcon(QMessageBox::Information);
                msgBox.setText(tr("The requested activity does not exist."));
                msgBox.setInformativeText(tr("Maybe someone or system clean up "
                                             "job deleted the activity. "
                                             "Try refreshing the data."));
                msgBox.setStandardButtons(QMessageBox::Ok);
                msgBox.setDefaultButton(QMessageBox::Ok);
                msgBox.exec();
                reject();
                return;
            }
        } else {
            mActivity = make_shared<Activity>();
        }
    }
    QWizard::setVisible(visible);
}

//! if the given table exists
bool DBManager::IsTableExist(string databasePath,string tableName)
{
	utils util;
	if(!(util.FileExists(databasePath))) return false;

	DBManager dbman;
	bool exist = false;

	dbman.Open(databasePath);
	if (!dbman.IsError())
	{
		string strSQL = "pragma table_info("+tableName+")";
		//string strSQL = "SELECT * FROM sqlite_master WHERE type = 'table' and name = '"+tableName+"'";
		slTable* tbl = dbman.Load(strSQL);
		if(tbl->nRows > 0) exist = true;
		delete tbl;
		tbl = NULL;
	}		
	dbman.Close();

	return exist;
}

void BMPReach::loadParameters(string parameterTableName,int reachStructurId)
{
	string bmpDatabase = m_bmpDatabasePath + BMP_DATABASE_NAME;
	if(!DBManager::IsTableExist(bmpDatabase,parameterTableName))
		throw ModelException("BMPReach","loadParameters","The BMP database '" + bmpDatabase + 
		"' does not exist or the there is not a table named '" + parameterTableName + "' in BMP database.");
	
	//read corresponding data
	DBManager db;
	db.Open(bmpDatabase);
	try
	{
		string tableInfoSQL = "pragma table_info("+parameterTableName+")";
		slTable* table = db.Load(tableInfoSQL);
		
		//get all numeric and text columns
		map<int,string> textColumns;
		map<int,bool> numericColumns;
		int methodIndex = -1;
		int sedimentMethodIndex = -1;
		int nutrientMethodIndex = -1;
		for(int i = 0;i < table->nRows; i++)
		{
			string type = table->FieldValue(i+1,2);
			if(!StringMatch(type,"TEXT")) 
			{
				if(StringMatch(type,"DATE") || StringMatch(type,"DATETIME"))
					numericColumns[i] = true;
				else
					numericColumns[i] = false;
			}
			else
			{
				textColumns[i] = table->FieldValue(i+1,1);
				if(StringMatch(textColumns[i],RESERVOIR_FLOW_ROUTING_METHOD_COLUMN_NAME)) methodIndex = i;	//flow routing method
				if(StringMatch(textColumns[i],RESERVOIR_SEDIMENT_ROUTING_METHOD_COLUMN_NAME)) sedimentMethodIndex = i;//sediment routing method
				if(StringMatch(textColumns[i],RESERVOIR_NUTRIENT_ROUTING_METHOD_COLUMN_NAME)) nutrientMethodIndex = i;//nutrient routing method
			}
		}
		delete table;

		//get the data for the specific id
		ostringstream oss;
		oss << "select * from " << parameterTableName << " where id =" << reachStructurId;
		string selectSQL = oss.str();
		table = db.Load(selectSQL);
		if(table->nRows == 0)
		{
			oss.clear();
			oss << "There is not a " << parameterTableName << " whose id is " << reachStructurId << ".";
			throw ModelException("BMPReach","loadParameters",oss.str());
		}
		
		//get numeric values
		m_numericParameterNum = int(numericColumns.size());
		if(m_bmpId == BMP_TYPE_RESERVOIR)
		{
			if(methodIndex == -1) throw ModelException("BMPReach","loadParameters","Can't find flow routing method column in table "+m_parameterTableName+".");
			if(sedimentMethodIndex == -1) throw ModelException("BMPReach","loadParameters","Can't find sediment routing method column in table "+m_parameterTableName+".");
			if(nutrientMethodIndex == -1) throw ModelException("BMPReach","loadParameters","Can't find nutrient routing method column in table "+m_parameterTableName+".");
			
			m_numericParameterNum+=3;
		}

		
		if(m_numericParameterNum>0)
		{
			int index = 0;
			m_numericParameters = new float[m_numericParameterNum];
			map<int,bool>::iterator it;
			for(it = numericColumns.begin();it!=numericColumns.end();it++)
			{
				string value = table->FieldValue(1,it->first);
				float temp = 0.0f;
				if(it->second)	//date
				{
					utils util;
					m_operationDate = util.ConvertToTime(value,"%4d-%2d-%2d",false);
					temp = float(m_operationDate/86400);					
				}
				else
				{
					temp = float(atof(value.c_str()));
					if(it->first == BMP_REACH_X_INDEX) m_x = temp;
					if(it->first == BMP_REACH_Y_INDEX) m_y = temp;
				}

				m_numericParameters[index] = temp;
				index ++;
			}
		}

		//get text values
		map<int,string>::iterator it2;
		for(it2 = textColumns.begin();it2!=textColumns.end();it2++)
		{
			m_textParameters[it2->second] = table->FieldValue(1,it2->first);
		}

		delete table;
//.........这里部分代码省略.........