Changeset - c64e5768a871
Parent rev.
Child rev.
[Not reviewed]
0 2 0
HanzZ - 14 years ago 2011-08-06 18:16:09
hanzz.k@gmail.com
MySQL prepared statements + untested macros
2 files changed with 67 insertions and 23 deletions:
include/transport/mysqlbackend.h
14
14
src/mysqlbackend.cpp
53
9
0 comments (0 inline, 0 general)
include/transport/mysqlbackend.h
➞
Show inline comments
 
@@ -98,20 +98,20 @@ class MySQLBackend : public StorageBackend
 
		std::string m_prefix;
 

			




	
	
	
		
 
		// statements

			




	
	
	
		
 
// 		sqlite3_stmt *m_setUser;

			




	
	
	
		
 
// 		sqlite3_stmt *m_getUser;

			




	
	
	
		
 
// 		sqlite3_stmt *m_getUserSetting;

			




	
	
	
		
 
// 		sqlite3_stmt *m_setUserSetting;

			




	
	
	
		
 
// 		sqlite3_stmt *m_updateUserSetting;

			




	
	
	
		
 
// 		sqlite3_stmt *m_removeUser;

			




	
	
	
		
 
// 		sqlite3_stmt *m_removeUserBuddies;

			




	
	
	
		
 
// 		sqlite3_stmt *m_removeUserSettings;

			




	
	
	
		
 
// 		sqlite3_stmt *m_removeUserBuddiesSettings;

			




	
	
	
		
 
// 		sqlite3_stmt *m_addBuddy;

			




	
	
	
		
 
// 		sqlite3_stmt *m_updateBuddy;

			




	
	
	
		
 
// 		sqlite3_stmt *m_updateBuddySetting;

			




	
	
	
		
 
// 		sqlite3_stmt *m_getBuddies;

			




	
	
	
		
 
// 		sqlite3_stmt *m_getBuddiesSettings;

			




	
	
	
		
 
		MYSQL_STMT *m_setUser;

			




	
	
	
		
 
		MYSQL_STMT *m_getUser;

			




	
	
	
		
 
		MYSQL_STMT *m_getUserSetting;

			




	
	
	
		
 
		MYSQL_STMT *m_setUserSetting;

			




	
	
	
		
 
		MYSQL_STMT *m_updateUserSetting;

			




	
	
	
		
 
		MYSQL_STMT *m_removeUser;

			




	
	
	
		
 
		MYSQL_STMT *m_removeUserBuddies;

			




	
	
	
		
 
		MYSQL_STMT *m_removeUserSettings;

			




	
	
	
		
 
		MYSQL_STMT *m_removeUserBuddiesSettings;

			




	
	
	
		
 
		MYSQL_STMT *m_addBuddy;

			




	
	
	
		
 
		MYSQL_STMT *m_updateBuddy;

			




	
	
	
		
 
		MYSQL_STMT *m_updateBuddySetting;

			




	
	
	
		
 
		MYSQL_STMT *m_getBuddies;

			




	
	
	
		
 
		MYSQL_STMT *m_getBuddiesSettings;

			




	
	
	
		
 
};

			




	
	
	
		
 

			




	
	
	
		
 
}

			




        

    


    
    

    

        

                

                    src/mysqlbackend.cpp
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	
	
		
 
@@ -33,30 +33,42 @@ using namespace log4cxx;

			




	
	
	
		
 

			




	
	
	
		
 
// Prepare the SQL statement

			




	
	
	
		
 
#define PREP_STMT(sql, str) \

			




	
	
	
		
 
	if(sqlite3_prepare_v2(m_db, std::string(str).c_str(), -1, &sql, NULL)) { \

			




	
	
	
		
 
		LOG4CXX_ERROR(logger, str<< (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db))); \

			




	
	
	
		
 
	sql = mysql_stmt_init(&m_conn);\

			




	
	
	
		
 
	if (mysql_stmt_prepare(sql, std::string(str).c_str(), std::string(str).size())) {\

			




	
	
	
		
 
		LOG4CXX_ERROR(logger, str << " " << mysql_error(&m_conn)); \

			




	
	
	
		
 
		return false; \

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
// Finalize the prepared statement

			




	
	
	
		
 
#define FINALIZE_STMT(prep) \

			




	
	
	
		
 
	if(prep != NULL) { \

			




	
	
	
		
 
		sqlite3_finalize(prep); \

			




	
	
	
		
 
		mysql_stmt_close(prep); \

			




	
	
	
		
 
	}

			




	
	
	
		
 
	

			




	
	
	
		
 
#define BEGIN(STATEMENT) 	sqlite3_reset(STATEMENT);\

			




	
	
	
		
 
#define BEGIN(STATEMENT, SIZE) 	MYSQL_BIND STATEMENT##_bind[SIZE]; \

			




	
	
	
		
 
							memset(STATEMENT##_bind, 0, sizeof(STATEMENT##_bind)); \

			




	
	
	
		
 
							int STATEMENT##_id = 1;\

			




	
	
	
		
 
							int STATEMENT##_id_get = 0;\

			




	
	
	
		
 
							(void)STATEMENT##_id_get;

			




	
	
	
		
 

			




	
	
	
		
 
#define BIND_INT(STATEMENT, VARIABLE) sqlite3_bind_int(STATEMENT, STATEMENT##_id++, VARIABLE)

			




	
	
	
		
 
#define BIND_STR(STATEMENT, VARIABLE) sqlite3_bind_text(STATEMENT, STATEMENT##_id++, VARIABLE.c_str(), -1, SQLITE_STATIC)

			




	
	
	
		
 
#define BIND_INT(STATEMENT, VARIABLE) STATEMENT##_bind[STATEMENT##_id].buffer_type= MYSQL_TYPE_LONG;\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id].buffer= (char *)&VARIABLE;\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id].is_null= 0;\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id++].length= 0;

			




	
	
	
		
 
#define BIND_STR(STATEMENT, VARIABLE) STATEMENT##_bind[STATEMENT##_id].buffer_type= MYSQL_TYPE_STRING;\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id].buffer= VARIABLE.c_str();\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id].buffer_length= STRING_SIZE;\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id].is_null= 0;\

			




	
	
	
		
 
							STATEMENT##_bind[STATEMENT##_id++].length= VARIABLE.size();

			




	
	
	
		
 
#define RESET_GET_COUNTER(STATEMENT)	STATEMENT##_id_get = 0;

			




	
	
	
		
 
#define GET_INT(STATEMENT)	sqlite3_column_int(STATEMENT, STATEMENT##_id_get++)

			




	
	
	
		
 
#define GET_STR(STATEMENT)	(const char *) sqlite3_column_text(STATEMENT, STATEMENT##_id_get++)

			




	
	
	
		
 
#define EXECUTE_STATEMENT(STATEMENT, NAME) 	if(sqlite3_step(STATEMENT) != SQLITE_DONE) {\

			




	
	
	
		
 
		LOG4CXX_ERROR(logger, NAME<< (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));\

			




	
	
	
		
 
			}

			




	
	
	
		
 
#define EXECUTE_STATEMENT(STATEMENT, NAME) if (mysql_stmt_bind_param(STATEMENT, STATEMENT##_bind)) { \

			




	
	
	
		
 
		LOG4CXX_ERROR(logger, NAME << " " << mysql_error(&m_conn)); \

			




	
	
	
		
 
	} \

			




	
	
	
		
 
	if (mysql_stmt_execute(STATEMENT)) { \

			




	
	
	
		
 
		LOG4CXX_ERROR(logger, NAME << " " << mysql_error(&m_conn)); \

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
using namespace boost;

			




	
	
	
		
 

			




	
	
		
 
@@ -71,6 +83,20 @@ MySQLBackend::MySQLBackend(Config *config) {

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
MySQLBackend::~MySQLBackend(){

			




	
	
	
		
 
	FINALIZE_STMT(m_setUser);

			




	
	
	
		
 
	FINALIZE_STMT(m_getUser);

			




	
	
	
		
 
	FINALIZE_STMT(m_removeUser);

			




	
	
	
		
 
	FINALIZE_STMT(m_removeUserBuddies);

			




	
	
	
		
 
	FINALIZE_STMT(m_removeUserSettings);

			




	
	
	
		
 
	FINALIZE_STMT(m_removeUserBuddiesSettings);

			




	
	
	
		
 
	FINALIZE_STMT(m_addBuddy);

			




	
	
	
		
 
	FINALIZE_STMT(m_updateBuddy);

			




	
	
	
		
 
	FINALIZE_STMT(m_getBuddies);

			




	
	
	
		
 
	FINALIZE_STMT(m_getBuddiesSettings);

			




	
	
	
		
 
	FINALIZE_STMT(m_getUserSetting);

			




	
	
	
		
 
	FINALIZE_STMT(m_setUserSetting);

			




	
	
	
		
 
	FINALIZE_STMT(m_updateUserSetting);

			




	
	
	
		
 
	FINALIZE_STMT(m_updateBuddySetting);

			




	
	
	
		
 
	mysql_close(&m_conn);

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
		
 
@@ -89,6 +115,24 @@ bool MySQLBackend::connect() {

			




	
	
	
		
 
		return false;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	PREP_STMT(m_setUser, "INSERT INTO " + m_prefix + "users (jid, uin, password, language, encoding, last_login, vip) VALUES (?, ?, ?, ?, ?, DATETIME('NOW'), ?)");

			




	
	
	
		
 
	PREP_STMT(m_getUser, "SELECT id, jid, uin, password, encoding, language, vip FROM " + m_prefix + "users WHERE jid=?");

			




	
	
	
		
 

			




	
	
	
		
 
	PREP_STMT(m_removeUser, "DELETE FROM " + m_prefix + "users WHERE id=?");

			




	
	
	
		
 
	PREP_STMT(m_removeUserBuddies, "DELETE FROM " + m_prefix + "buddies WHERE user_id=?");

			




	
	
	
		
 
	PREP_STMT(m_removeUserSettings, "DELETE FROM " + m_prefix + "users_settings WHERE user_id=?");

			




	
	
	
		
 
	PREP_STMT(m_removeUserBuddiesSettings, "DELETE FROM " + m_prefix + "buddies_settings WHERE user_id=?");

			




	
	
	
		
 

			




	
	
	
		
 
	PREP_STMT(m_addBuddy, "INSERT INTO " + m_prefix + "buddies (user_id, uin, subscription, groups, nickname, flags) VALUES (?, ?, ?, ?, ?, ?)");

			




	
	
	
		
 
	PREP_STMT(m_updateBuddy, "UPDATE " + m_prefix + "buddies SET groups=?, nickname=?, flags=?, subscription=? WHERE user_id=? AND uin=?");

			




	
	
	
		
 
	PREP_STMT(m_getBuddies, "SELECT id, uin, subscription, nickname, groups, flags FROM " + m_prefix + "buddies WHERE user_id=? ORDER BY id ASC");

			




	
	
	
		
 
	PREP_STMT(m_getBuddiesSettings, "SELECT buddy_id, type, var, value FROM " + m_prefix + "buddies_settings WHERE user_id=? ORDER BY buddy_id ASC");

			




	
	
	
		
 
	PREP_STMT(m_updateBuddySetting, "INSERT OR REPLACE INTO " + m_prefix + "buddies_settings (user_id, buddy_id, var, type, value) VALUES (?, ?, ?, ?, ?)");

			




	
	
	
		
 
	

			




	
	
	
		
 
	PREP_STMT(m_getUserSetting, "SELECT type, value FROM " + m_prefix + "users_settings WHERE user_id=? AND var=?");

			




	
	
	
		
 
	PREP_STMT(m_setUserSetting, "INSERT INTO " + m_prefix + "users_settings (user_id, var, type, value) VALUES (?,?,?,?)");

			




	
	
	
		
 
	PREP_STMT(m_updateUserSetting, "UPDATE " + m_prefix + "users_settings SET value=? WHERE user_id=? AND var=?");

			




	
	
	
		
 

			




	
	
	
		
 
	return true;

			




	
	
	
		
 
}

			




	
	
	
		
 

			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







  


    




    








    
        Server instance: srv01-28608
    
    
        This site is powered by
            Kallithea,
        which is
        © 2010–2021 by various authors & licensed under GPLv3.
            – Support