Changeset - 5855e6608cdc
Parent rev.
Child rev.
[Not reviewed]
0 2 0
Jan Kaluza - 14 years ago 2011-04-07 14:51:34
hanzz.k@gmail.com
begin/commit transaction, update buddy statement
2 files changed with 26 insertions and 5 deletions:
include/transport/sqlite3backend.h
4
3
src/sqlite3backend.cpp
22
2
0 comments (0 inline, 0 general)
include/transport/sqlite3backend.h
➞
Show inline comments
 
@@ -60,48 +60,49 @@ class SQLite3Backend : public StorageBackend
 
		/// Gets user data from database and stores them into user reference.
 
		/// \param barejid barejid of user
 
		/// \param user UserInfo object where user data will be stored
 
		/// \return true if user has been found in database
 
		bool getUser(const std::string &barejid, UserInfo &user);
 

			




	
	
	
		
 
		/// Changes users online state variable in database.

			




	
	
	
		
 
		/// \param id id of user - UserInfo.id

			




	
	
	
		
 
		/// \param online online state

			




	
	
	
		
 
		void setUserOnline(long id, bool online);

			




	
	
	
		
 

			




	
	
	
		
 
		/// Removes user and all connected data from database.

			




	
	
	
		
 
		/// \param id id of user - UserInfo.id

			




	
	
	
		
 
		/// \return true if user has been found in database and removed

			




	
	
	
		
 
		bool removeUser(long id);

			




	
	
	
		
 

			




	
	
	
		
 
		/// Returns JIDs of all buddies in user's roster.

			




	
	
	
		
 
		/// \param id id of user - UserInfo.id

			




	
	
	
		
 
		/// \param roster string list used to store user's roster

			




	
	
	
		
 
		/// \return true if user has been found in database and roster has been fetched

			




	
	
	
		
 
		bool getBuddies(long id, std::list<std::string> &roster);

			




	
	
	
		
 

			




	
	
	
		
 
		long addBuddy(long userId, const BuddyInfo &buddyInfo);

			




	
	
	
		
 

			




	
	
	
		
 
		void updateBuddy(long userId, const BuddyInfo &buddyInfo) {}

			




	
	
	
		
 
		void updateBuddy(long userId, const BuddyInfo &buddyInfo);

			




	
	
	
		
 
		void removeBuddy(long id) {}

			




	
	
	
		
 

			




	
	
	
		
 
		void beginTransaction() {}

			




	
	
	
		
 
		void commitTransaction() {}

			




	
	
	
		
 
		void beginTransaction();

			




	
	
	
		
 
		void commitTransaction();

			




	
	
	
		
 

			




	
	
	
		
 
	private:

			




	
	
	
		
 
		bool exec(const std::string &query);

			




	
	
	
		
 

			




	
	
	
		
 
		sqlite3 *m_db;

			




	
	
	
		
 
		Config *m_config;

			




	
	
	
		
 
		std::string m_prefix;

			




	
	
	
		
 

			




	
	
	
		
 
		// statements

			




	
	
	
		
 
		sqlite3_stmt *m_setUser;

			




	
	
	
		
 
		sqlite3_stmt *m_getUser;

			




	
	
	
		
 
		sqlite3_stmt *m_removeUser;

			




	
	
	
		
 
		sqlite3_stmt *m_removeUserBuddies;

			




	
	
	
		
 
		sqlite3_stmt *m_removeUserSettings;

			




	
	
	
		
 
		sqlite3_stmt *m_removeUserBuddiesSettings;

			




	
	
	
		
 
		sqlite3_stmt *m_addBuddy;

			




	
	
	
		
 
		sqlite3_stmt *m_updateBuddy;

			




	
	
	
		
 
};

			




	
	
	
		
 

			




	
	
	
		
 
}

			




        

    


    
    

    

        

                

                    src/sqlite3backend.cpp
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	
	
		
 
@@ -73,48 +73,49 @@ SQLite3Backend::~SQLite3Backend(){

			




	
	
	
		
 
	

			




	
	
	
		
 
		FINALIZE_STMT(m_setUser);

			




	
	
	
		
 
		sqlite3_close(m_db);

			




	
	
	
		
 
	}

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
bool SQLite3Backend::connect() {

			




	
	
	
		
 
	if (sqlite3_open(CONFIG_STRING(m_config, "database.database").c_str(), &m_db)) {

			




	
	
	
		
 
		sqlite3_close(m_db);

			




	
	
	
		
 
		return false;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	if (createDatabase() == false)

			




	
	
	
		
 
		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=?");

			




	
	
	
		
 

			




	
	
	
		
 
	return true;

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
bool SQLite3Backend::createDatabase() {

			




	
	
	
		
 
	int not_exist = exec("CREATE TABLE " + m_prefix + "buddies ("

			




	
	
	
		
 
				"  id INTEGER PRIMARY KEY NOT NULL,"

			




	
	
	
		
 
				"  user_id int(10) NOT NULL,"

			




	
	
	
		
 
				"  uin varchar(255) NOT NULL,"

			




	
	
	
		
 
				"  subscription varchar(20) NOT NULL,"

			




	
	
	
		
 
				"  nickname varchar(255) NOT NULL,"

			




	
	
	
		
 
				"  groups varchar(255) NOT NULL,"

			




	
	
	
		
 
				"  flags int(4) NOT NULL DEFAULT '0'"

			




	
	
	
		
 
				");");

			




	
	
	
		
 

			




	
	
	
		
 
	if (not_exist) {

			




	
	
	
		
 
		exec("CREATE UNIQUE INDEX IF NOT EXISTS user_id ON " + m_prefix + "buddies (user_id, uin);");

			




	
	
	
		
 

			




	
	
	
		
 
		exec("CREATE TABLE IF NOT EXISTS " + m_prefix + "buddies_settings ("

			




	
	
	
		
 
					"  user_id int(10) NOT NULL,"

			




	
	
	
		
 
					"  buddy_id int(10) NOT NULL,"

			




	
	
	
		
 
					"  var varchar(50) NOT NULL,"

			




	
	
	
		
 
					"  type int(4) NOT NULL,"

			




	
	
	
		
 
					"  value varchar(255) NOT NULL,"

			




	
	
		
 
@@ -197,70 +198,89 @@ bool SQLite3Backend::getUser(const std::string &barejid, UserInfo &user) {

			




	
	
	
		
 
		return true;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	if (ret != SQLITE_DONE) {

			




	
	
	
		
 
		onStorageError("getUser query", (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	return false;

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
void SQLite3Backend::setUserOnline(long id, bool online) {

			




	
	
	
		
 
	

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
long SQLite3Backend::addBuddy(long userId, const BuddyInfo &buddyInfo) {

			




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

			




	
	
	
		
 
	BEGIN(m_addBuddy);

			




	
	
	
		
 
	BIND_INT(m_addBuddy, userId);

			




	
	
	
		
 
	BIND_STR(m_addBuddy, buddyInfo.legacyName);

			




	
	
	
		
 
	BIND_STR(m_addBuddy, buddyInfo.subscription);

			




	
	
	
		
 
	BIND_STR(m_addBuddy, buddyInfo.groups[0]); // TODO: serialize groups

			




	
	
	
		
 
	BIND_STR(m_addBuddy, buddyInfo.alias);

			




	
	
	
		
 
	BIND_INT(m_addBuddy, buddyInfo.flags);

			




	
	
	
		
 

			




	
	
	
		
 
	EXECUTE_STATEMENT(m_addBuddy, "addBuddy query");

			




	
	
	
		
 

			




	
	
	
		
 
	if(sqlite3_step(m_addBuddy) != SQLITE_DONE) {

			




	
	
	
		
 
		onStorageError("addBuddy query", (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));

			




	
	
	
		
 
		return -1;

			




	
	
	
		
 
	}

			




	
	
	
		
 
	return (long) sqlite3_last_insert_rowid(m_db);

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
void SQLite3Backend::updateBuddy(long userId, const BuddyInfo &buddyInfo) {

			




	
	
	
		
 
// 	UPDATE " + m_prefix + "buddies SET groups=?, nickname=?, flags=?, subscription=? WHERE user_id=? AND uin=?

			




	
	
	
		
 
	BEGIN(m_updateBuddy);

			




	
	
	
		
 
	BIND_STR(m_updateBuddy, buddyInfo.groups[0]); // TODO: serialize groups

			




	
	
	
		
 
	BIND_STR(m_updateBuddy, buddyInfo.alias);

			




	
	
	
		
 
	BIND_INT(m_updateBuddy, buddyInfo.flags);

			




	
	
	
		
 
	BIND_STR(m_updateBuddy, buddyInfo.subscription);

			




	
	
	
		
 
	BIND_INT(m_updateBuddy, userId);

			




	
	
	
		
 
	BIND_STR(m_updateBuddy, buddyInfo.legacyName);

			




	
	
	
		
 

			




	
	
	
		
 
	EXECUTE_STATEMENT(m_updateBuddy, "updateBuddy query");

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
bool SQLite3Backend::getBuddies(long id, std::list<std::string> &roster) {

			




	
	
	
		
 
	return true;

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
bool SQLite3Backend::removeUser(long id) {

			




	
	
	
		
 
	sqlite3_reset(m_removeUser);

			




	
	
	
		
 
	sqlite3_bind_int(m_removeUser, 1, id);

			




	
	
	
		
 
	if(sqlite3_step(m_removeUser) != SQLITE_DONE) {

			




	
	
	
		
 
		onStorageError("removeUser query", (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));

			




	
	
	
		
 
		return false;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	sqlite3_reset(m_removeUserSettings);

			




	
	
	
		
 
	sqlite3_bind_int(m_removeUserSettings, 1, id);

			




	
	
	
		
 
	if(sqlite3_step(m_removeUserSettings) != SQLITE_DONE) {

			




	
	
	
		
 
		onStorageError("removeUserSettings query", (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));

			




	
	
	
		
 
		return false;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	sqlite3_reset(m_removeUserBuddies);

			




	
	
	
		
 
	sqlite3_bind_int(m_removeUserBuddies, 1, id);

			




	
	
	
		
 
	if(sqlite3_step(m_removeUserBuddies) != SQLITE_DONE) {

			




	
	
	
		
 
		onStorageError("removeUserBuddies query", (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));

			




	
	
	
		
 
		return false;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	sqlite3_reset(m_removeUserBuddiesSettings);

			




	
	
	
		
 
	sqlite3_bind_int(m_removeUserBuddiesSettings, 1, id);

			




	
	
	
		
 
	if(sqlite3_step(m_removeUserBuddiesSettings) != SQLITE_DONE) {

			




	
	
	
		
 
		onStorageError("removeUserBuddiesSettings query", (sqlite3_errmsg(m_db) == NULL ? "" : sqlite3_errmsg(m_db)));

			




	
	
	
		
 
		return false;

			




	
	
	
		
 
	}

			




	
	
	
		
 

			




	
	
	
		
 
	return true;

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
void SQLite3Backend::beginTransaction() {

			




	
	
	
		
 
	exec("BEGIN TRANSACTION;");

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
void SQLite3Backend::commitTransaction() {

			




	
	
	
		
 
	exec("COMMIT TRANSACTION;");

			




	
	
	
		
 
}

			




	
	
	
		
 

			




	
	
	
		
 
}

			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







  


    




    








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