// Recursive Function
void _ftpEntry::removeFolderChildren( size_t folderID ) {
Connection *sqlConnect = dbConnect();
bool isDone = false;
Query serverQuery = sqlConnect->query();
StoreQueryResult serverResults;
StoreQueryResult::iterator storeQueryIT;
size_t ID;
// Find all folders that parent us
serverQuery << "select * from ServerFolders where parentID = " << quote << folderID;
serverQuery.execute();
//
for(storeQueryIT = serverResults.begin(); storeQueryIT != serverResults.end(); storeQueryIT++ ) {
// Remove all children from this parent
ID = (size_t) (*storeQueryIT)["folderID"];
removeFolderChildren( ID );
// Now remove the parent
serverQuery.str("");
serverQuery << "delete from ServerFolders where folderID = " << quote << ID;
serverQuery.execute();
}
delete sqlConnect;
}
void GameModel::DeleteGame() {
using namespace ManaCraft::Database;
try {
this->teams->DeleteTeams();
Query query = DatabaseAPI::getQuery();
query.clear();
query << "DELETE FROM Game WHERE ID = " << mysqlpp::quote << this->id;
query.execute();
query = DatabaseAPI::getQuery();
query.clear();
query << "DELETE FROM Game_Teams WHERE ID = " << mysqlpp::quote << this->id;
query.execute();
query = DatabaseAPI::getQuery();
query.clear();
query << "DELETE FROM Game_Player_Towers WHERE ID = " << mysqlpp::quote << this->id;
query.execute();
}
catch (mysqlpp::BadConversion e) {
std::cout << e.what() << "\n";
}
catch (mysqlpp::BadIndex e) {
std::cout << e.what() << "\n";
}
catch (Exception e) {
throw e;
}
}
void TestDatabase::testDelete()
{
DatabaseAPI::connectToDatabase();
Query query = DatabaseAPI::getQuery();
query.clear();
query << "DELETE FROM Test_Table WHERE ID = 1";
query.execute();
query = DatabaseAPI::getQuery();
query.clear();
query << "DELETE FROM Test_Table WHERE ID = 2";
query.execute();
}
void SaslConnection::close()
{
Endpoint client = peer();
Connection::close();
if ( !u || logged ||
!client.valid() || client.protocol() == Endpoint::Unix )
return;
logged = true;
Query * q = new Query(
"insert into connections "
"(username,address,port,mechanism,authfailures,"
"syntaxerrors,started_at,ended_at,userid) "
"values ($1,$2,$3,$4,$5,$6,"
"$7::interval + 'epoch'::timestamptz,"
"$8::interval + 'epoch'::timestamptz,$9)", 0
);
q->bind( 1, u->login() );
q->bind( 2, client.address() );
q->bind( 3, client.port() );
q->bind( 4, m );
q->bind( 5, af );
q->bind( 6, sf );
q->bind( 7, s );
q->bind( 8, (uint)time( 0 ) );
q->bind( 9, u->id() );
q->execute();
}
/**
* Insert a contact into the database.
*
* Demonstrates:
* - use of a sequence
* - opening of a table
* - insert mode
* - assigning data to a row
* - posting data to the database
* - closing a table
* - inserting data into a BLOB field
*/
db_uint PhoneBook::insert_contact(const wchar_t *name, db_uint ring_id, const char *picture_name)
{
Query q;
Sequence id_sequence;
db_uint id;
id_sequence.open(db, "contact_id");
id_sequence.get_next_value(id);
q.prepare(db,
"insert into contact (id, name, ring_id, picture_name) "
" values ($<integer>0, $<nvarchar>1, $<integer>2, $<varchar>3) ");
q.param(0) = id;
q.param(1) = name;
q.param(2) = ring_id;
q.param(3) = picture_name;
if (DB_SUCCESS(print_error(q.execute(), q))) {
//---------------------------------------------------------------
// Insert the BLOB field
//---------------------------------------------------------------
update_contact_picture(id, picture_name);
} else {
//---------------------------------------------------------------
// Error returned from execute
//---------------------------------------------------------------
id = 0;
}
return id;
}
void GameModel::SaveGame()
{
using namespace ManaCraft::Database;
// Game Table
Query query = DatabaseAPI::getQuery();
query.clear();
query << "SELECT * FROM Game WHERE ID = " << mysqlpp::quote << id;
if (UseQueryResult result = query.use()) {
if (Row row = result.fetch_row()) {
DeleteGame();
}
}
try {
query = DatabaseAPI::getQuery();
query.clear();
query << "INSERT INTO Game VALUES(" << mysqlpp::quote << id << ", " << mysqlpp::quote << "Another Game" << ")";
query.execute();
teams->SaveTeams(id);
}
catch (mysqlpp::BadConversion e) {
std::cout << e.what() << "\n";
}
catch (mysqlpp::BadIndex e) {
std::cout << e.what() << "\n";
}
catch (Exception e) {
throw e;
}
}
Deliverator( Injectee * message,
const UString & mailbox, const EString & user )
: q( 0 ), i( 0 ), m( message ), mbn( mailbox ), un( user ),
p( 0 ), mb( 0 )
{
Allocator::addEternal( this, "deliver object" );
q = new Query( "select al.mailbox, n.name as namespace, u.login "
"from aliases al "
"join addresses a on (al.address=a.id) "
"left join users u on (al.id=u.alias) "
"left join namespaces n on (u.parentspace=n.id) "
"where (lower(a.localpart)=$1 and lower(a.domain)=$2) "
"or (lower(u.login)=$3)", this );
if ( user.contains( '@' ) ) {
int at = user.find( '@' );
q->bind( 1, user.mid( 0, at ).lower() );
q->bind( 2, user.mid( at + 1 ).lower() );
}
else {
q->bindNull( 1 );
q->bindNull( 2 );
}
q->bind( 3, user.lower() );
q->execute();
}
void ShotResult::saveRemark()
{
int index = mtw_Shotremark->currentRow();
qDebug("Index:%d",index);
if (index < 0) return;
QTableWidgetItem *pItem = mtw_Shotremark->item(index, WRITER);
string strwriter = pItem->text().toStdString();
pItem = mtw_Shotremark->item(index, REMARK);
string strremark = pItem->text().toStdString();
try {
sql_datetime wdate;
QDateTime datetime = QDateTime::currentDateTime();
wdate.year = datetime.date().year();
wdate.month = datetime.date().month();
wdate.day= datetime.date().day();
wdate.hour=datetime.time().hour();
wdate.minute=datetime.time().minute();
wdate.second=datetime.time().second();
Query query = m_con.query();
REMARKT remark(0,mshotno,strwriter,strremark,wdate);
query.insert(remark);
query.execute();
} catch (const mysqlpp::BadQuery& er) {
cerr << "Query error: " << er.what() << endl;
} catch (const mysqlpp::Exception& er) {
cerr << er.what() << endl;
};
}
/**
* Export picture file to disk
*
* Demonstrates:
* - reading the contents of a BLOB
*/
void PhoneBook::export_picture(db_uint id, const char *file_name)
{
Query q;
BlobField blob;
enum FieldOrder {
PICTURE_FIELD = 0
};
//-------------------------------------------------------------------
// Select a specific record from the contact table.
//-------------------------------------------------------------------
print_error(q.prepare(db, "select picture from contact where id = $<integer>0"), q);
q.param(0) = id;
if (DB_SUCCESS(print_error(q.execute(), q))) {
blob.attach(q, PICTURE_FIELD);
//---------------------------------------------------------------
// Position the cursor to the first record (only 1 record).
//---------------------------------------------------------------
if (q.seek_first() == DB_NOERROR) {
int offset, bytes_read;
db_len_t blob_size = blob.size();
const db_len_t data_size = 256;
char data[data_size];
//-----------------------------------------------------------
// Open the output file.
//-----------------------------------------------------------
FILE *picture_file;
if ((picture_file = fopen(file_name, "wb")) != NULL) {
//-------------------------------------------------------
// Export the BLOB to the output image file
//-------------------------------------------------------
for(offset = 0; offset < blob_size; offset += data_size) {
if (bytes_read = blob.read(offset, data, data_size)) {
fwrite(data, bytes_read, 1, picture_file);
}
}
//-------------------------------------------------------
// Close the output file.
//-------------------------------------------------------
fclose(picture_file);
} else {
cerr << "Cannot open " << file_name << endl;
}
} else {
cerr << "Could not find contact with id " << (long) id << endl;
}
}
return;
}
void Editor::update()
{
sh::Factory::getInstance().doMonitorShaderFiles();
if (!mMainWindow)
return;
{
boost::mutex::scoped_lock lock(mSync.mActionMutex);
// execute pending actions
while (mMainWindow->mActionQueue.size())
{
Action* action = mMainWindow->mActionQueue.front();
action->execute();
delete action;
mMainWindow->mActionQueue.pop();
}
}
{
boost::mutex::scoped_lock lock(mSync.mQueryMutex);
// execute pending queries
for (std::vector<Query*>::iterator it = mMainWindow->mQueries.begin(); it != mMainWindow->mQueries.end(); ++it)
{
Query* query = *it;
if (!query->mDone)
query->execute();
}
}
boost::mutex::scoped_lock lock2(mSync.mUpdateMutex);
// update the list of materials
mMainWindow->mState.mMaterialList.clear();
sh::Factory::getInstance().listMaterials(mMainWindow->mState.mMaterialList);
// update global settings
mMainWindow->mState.mGlobalSettingsMap.clear();
sh::Factory::getInstance().listGlobalSettings(mMainWindow->mState.mGlobalSettingsMap);
// update configuration list
mMainWindow->mState.mConfigurationList.clear();
sh::Factory::getInstance().listConfigurationNames(mMainWindow->mState.mConfigurationList);
// update shader list
mMainWindow->mState.mShaderSets.clear();
sh::Factory::getInstance().listShaderSets(mMainWindow->mState.mShaderSets);
mMainWindow->mState.mErrors += sh::Factory::getInstance().getErrorLog();
}
// 사용자 로그인 Error나면 Error의 이유를 리턴한다.
int CDBManager::LoginUser(char * szNickname, char * szPassword, int * pRealUserID)
{
try
{
Connection mCon(m_szDBName, m_szIP, m_szUser, m_szPassword);
Query mQuery = mCon.query();
// Nickname을 통해 유저의 정보를 얻어 온다.
mQuery << "SELECT id, nickname, password, online FROM userdata WHERE nickname = \"" << szNickname << "\"";
Result res = mQuery.store();
Result::iterator i;
Row row;
i = res.begin();
if(i != res.end())
{
row = *i;
if(!strcmp(szPassword, row["password"]))
{
*pRealUserID = row["id"];
int Online = row["online"];
if(Online == 1) // 중복 로그인을 막기위해 로그인 필드를 검사한다.
{
return MSG_SERV_LOGINBAD_LOGINED;
}
// Password가 일치하면 온라인으로 표기 한다.
mQuery << "UPDATE userdata SET online = 1, lastlogin = NULL WHERE id = " << row["id"];
mQuery.execute();
}
else // Password가 틀렸다.
{
return MSG_SERV_LOGINBAD_PASSBAD;
}
}
else // Nickname이 틀렸다.
{
return MSG_SERV_LOGINBAD_IDBAD;
}
}
catch(BadQuery er) // DB에러 본래는 쿼리에러다.
{
return MSG_SERV_LOGINBAD_DATABASEBAD;
}
return -1; // 성공하면 음수를 리턴
}
/**
* Update an existing contact's name.
*
* Demonstrates:
* - searching for existence of a record using an index
* - edit mode
*/
void PhoneBook::update_contact_name(db_uint id, const wchar_t *newname)
{
Query q;
q.prepare(db,
"update contact "
" set name = $<nvarchar>1 "
" where id = $<integer>0 ");
q.param(0) = id;
q.param(1) = newname;
print_error(q.execute(), q);
}
bool BaseDeDatos::guardar_registro_sensor(float valor_sensor, int id_sensor_, int tipo_sensor){
try {
time_t now = time(0);
Query query = conn.query();
switch (tipo_sensor) {
case 2:{
registro_temp registro_t(mysqlpp::DateTime(now), valor_sensor, id_sensor_);
query.insert(registro_t);
}
break;
case 1:{
registro_humedad registro_h(mysqlpp::DateTime(now), valor_sensor, id_sensor_);
query.insert(registro_h);
}
break;
case 0:{
registro_luz registro_l(mysqlpp::DateTime(now), valor_sensor, id_sensor_);
query.insert(registro_l);
}
break;
}
// Show the query about to be executed.
cout << "Query: " << query << endl;
query.execute();
}
catch (const mysqlpp::BadQuery& er) {
// Handle any query errors
cerr << "Query error: " << er.what() << endl;
return false;
}
catch (const mysqlpp::BadConversion& er) {
// Handle bad conversions
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return false;
}
catch (const mysqlpp::Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return false;
}
return true;
}
void _ftpEntry::checkDBFolders() {
bool isDone = false;
Query serverQuery = _sqlConnection->query();
StoreQueryResult serverResults;
StoreQueryResult::iterator storeQueryIT;
vector<_ftpEntry*>::iterator entryIT;
// Nothing to do here...
if( directorys.empty() )
return;
// Get a list of all the directorys that we parent
serverQuery << "select * from ServerFolders where serverID = " << quote << _serverID;
serverQuery << " AND parentID = " << quote << _folderID;
// Do the query
serverResults = serverQuery.store();
// None?
if( !serverResults.size() )
return;
// We now have to check each DB result, against the results in directorys vector
for(storeQueryIT = serverResults.begin(); storeQueryIT != serverResults.end(); storeQueryIT++ , isDone = false) {
for( entryIT = directorys.begin(); entryIT != directorys.end(); entryIT++) {
// Does this directory still exist?
if((*entryIT)->getName() == (string) (*storeQueryIT)["name"]) {
isDone = true; // Yes it exists
break;
} // parentID check
} // entryIT
// Deleted?
if(!isDone) {
// Yes, remove the entry from the DB
removeFolderChildren( (size_t) (*storeQueryIT)["folderID"] );
serverQuery.str("");
serverQuery << "delete from ServerFolders where folderID = " << quote << (*storeQueryIT)["folderID"];
// bye bye :)
serverQuery.execute();
} // !isDone
} // storeQueryIT
} // checkDBFolders
bool _ftpEntry::rescanUpdateParent(bool value) {
Query QueryNewFolder = _sqlConnection->query();
QueryNewFolder << "UPDATE ServerFolders SET rescanForce = " << quote << value;
QueryNewFolder << " WHERE folderID = " << quote << _parentID;
// go go go
SimpleResult res = QueryNewFolder.execute();
if(!res.rows())
return false; // No Row changed
return true;
}
bool DB::del(const std::string& table,const int& pk)
{
std::string str_q = "DELETE FROM "+escapeColumn(table)+" WHERE ("+escapeColumn(table)+"."+escapeColumn("pk")+" = "+std::to_string(pk)+");";
#if ORM_DEBUG & ORM_DEBUG_SQL
std::cerr<<COMMENTAIRE<<"[Sql:delete]"<<str_q<<BLANC<<std::endl;
#endif
Query* q = prepareQuery(str_q);
q->execute();
q->next();
delete q;
return true;
};
/**
* Insert a phone entry into the database.
*/
void PhoneBook::insert_phone_number(db_uint contact_id, const char *number, PhoneNumberType type, db_sint speed_dial)
{
Query q;
q.prepare(db,
"insert into phone_number (contact_id,number,type,speed_dial) "
" values ($<integer>0, $<varchar>1, $<integer>2, $<integer>3) ");
q.param(0) = contact_id;
q.param(1) = number;
q.param(2) = (int) type;
q.param(3) = speed_dial;
print_error(q.execute(), q);
}
/**
* Remove contact record from the database.
*
* Demonstrates:
* - deleting a record
* - parent/child removal
* - range search loop using seek_next()
* - seek_next() returns OK on end, so must explicitly check for is_eof()
*/
void PhoneBook::remove_contact(db_uint id)
{
Query q;
//---------------------------------------------------------------
// Remove the corresponding recs from the phone_number table.
//---------------------------------------------------------------
q.prepare(db,
"delete from phone_number "
" where contact_id = $<integer>0 ");
q.param(0) = id;
if (DB_SUCCESS(print_error(q.execute(), q))) {
//-------------------------------------------------------------------
// Remove record from contact table.
//-------------------------------------------------------------------
q.prepare(db,
"delete from contact "
" where id = $<integer>0 ");
q.param(0) = id;
print_error(q.execute(), q);
}
}
void DnsQuery::updateDB(string domain, int querytime)
{
Query query = conn.query();
time_t rawtime;
struct tm *timeinfo;
time(&rawtime);
timeinfo = localtime(&rawtime);
static char timebuf[sizeof("0000-00-00 00:00:00")];
strftime(timebuf, sizeof(timebuf), "%Y-%m-%d %T", timeinfo);
if(qcount == 1)
{
/* First time Replace */
query << "replace into mytable values("
<< quote << domain
<< "," << qcount
<< "," << querytime
<< "," << (querytime * querytime)
<< "," << querytime
<< "," << quote << timebuf
<< "," << quote << timebuf
<< ")";
doMean.insert(pair<string, double>(domain, querytime));
doSquare.insert(pair<string, double> (domain, querytime*querytime));
} else {
/* Now update the values */
double prevSum=doMean.find(domain)->second;
double prevsumofsq=doSquare.find(domain)->second;
query.reset();
double newMean = (prevSum + querytime)/qcount;
double newsumofsq = prevsumofsq + (querytime * querytime);
doMean.insert(pair<string, double>(domain, prevSum + querytime));
doSquare.insert(pair<string, double>(domain, newsumofsq));
query << "update mytable " << " set"
<< " count=" << qcount
<< " ,mean=" << newMean
<< " ,sumofsq=" << newsumofsq
<< " ,stddev=" << calcStddev(newsumofsq, newMean, querytime, domain)
<< " ,endTime=" << quote << timebuf
<< " where domain=" << quote << domain;
}
try {
query.execute();
} catch(const Exception &er) {
cout<<query<<"failed";
cerr<<er.what()<<endl;
}
}
int cServicioCatchAll::habilitarCatchAllDB(cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "INSERT INTO CATCH_ALL (ID_DOMINIO , ESTADO) VALUES('" << dominio.getIdDominio() << "', '1')";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al habilitar la cuenta Catch All";
dominio.loguear(error);
return(-2);
}
}
SpoolManager::SpoolManager()
: d( new SpoolManagerData )
{
setLog( new Log );
Query * q = new Query( "update deliveries "
"set expires_at=current_timestamp+interval '900 s' "
"where expires_at<current_timestamp+interval '900 s' "
"and id in "
"(select delivery from delivery_recipients"
" where action=$1 or action=$2)",
0 );
q->bind( 1, Recipient::Unknown );
q->bind( 2, Recipient::Delayed );
q->execute();
}
int cServicioRedireccionamientoExt::quitarRedireccionamientoExtDB(const std::string &redireccionamiento, cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "DELETE FROM REDIRECCIONAMIENTO_EXTERNO WHERE REDIRECCIONAMIENTO = '"
<< redireccionamiento << "' AND ID_DOMINIO = '" << dominio.getIdDominio() << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al quitar el redireccionamiento externo " + redireccionamiento;
dominio.loguear(error);
return(-2);
}
}
int cServicioFTPAdmin::cambiarClaveFTPAdminDB(const std::string &password, cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "UPDATE DOMINIO SET PASSWORD_FTP = '" << password << "' WHERE ID_DOMINIO = '"
<< dominio.getIdDominio() << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al cambiar el password FTP del administrador";
dominio.loguear(error);
return(-2);
}
}
int cServicioPOP3::quitarEmailDB(const std::string &cuenta, cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "DELETE FROM CUENTA_POP3 WHERE CUENTA = '" << cuenta << "' AND ID_DOMINIO = '"
<< dominio.getIdDominio() << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al quitar la cuenta POP3 " + cuenta;
dominio.loguear(error);
return(-2);
}
}
int cServicioBackup::agregarBackupDB(const std::string &compresion, cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "REPLACE INTO BACKUP(ID_DOMINIO, FECHA_HORA, COMPRESION, TIPO) VALUES('"
<< dominio.getIdDominio() << "', NOW(), '" << compresion << "', '" << tipo << "')";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al agregar el backup " + tipo + " con compresión " + compresion;
dominio.loguear(error);
return(-2);
}
}
int cServicioCatchAll::deshabilitarCatchAllDB(cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "UPDATE CATCH_ALL SET ESTADO = '0' "
<< "WHERE ID_DOMINIO = '" << dominio.getIdDominio() << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al habilitar la cuenta Catch All";
dominio.loguear(error);
return(-2);
}
}
/**
* Retrieve picture_name field from a contact
*/
String PhoneBook::get_picture_name(db_uint id)
{
Query q;
//-------------------------------------------------------------------
// Select a specific record from the contact table.
//-------------------------------------------------------------------
print_error(q.prepare(db, "select picture_name from contact where id = $<integer>0"), q);
q.param(0) = id;
if (DB_SUCCESS(print_error(q.execute(), q))) {
q.seek_first();
return String(q[0].as_string());
}
return String("");
}
int cServicioSubdominio::quitarSubdominioDB(const std::string &subdominio, cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "DELETE FROM SUBDOMINIO WHERE SUBDOMINIO = '" << subdominio << "' AND ID_DOMINIO = '"
<< dominio.getIdDominio() << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al quitar el subdominio " + subdominio;
dominio.loguear(error);
return(-2);
}
}
int cServicioProteccionAcceso::quitarProteccionAccesoDB(const std::string &directorio, cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "DELETE FROM PROTECCION_DIR_ACCESO WHERE DIRECTORIO = '" << directorio
<< "' AND ID_DOMINIO = '" << dominio.getIdDominio() << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al quitar la proteccion de acceso para el directorio " + directorio;
dominio.loguear(error);
return(-2);
}
}
int cServicioProteccionAcceso::quitarUsuariosAccesoDB(const std::string &idProteccion,
cDominio &dominio) {
try {
Query qry = dominio.con.query();
qry << "DELETE FROM PROTECCION_DIR_ACCESO_USR WHERE ID_PROTECCION_DIR_ACCESO = '"
<< idProteccion << "'";
qry.execute();
return(0);
} catch(BadQuery er) {
dominio.loguear(er.error);
return(-1);
} catch(...) {
string error;
error = "Error al quitar todos los usuarios para la proteccion " + idProteccion;
return(-2);
}
}
