def initmysql(host, user, passwd, dbname): operationhandle = mysqlOperaction(host, user, passwd, dbname) operationhandle.connect() createuser_log = "CREATE TABLE user_log (user_ID int(11) NOT NULL, user_name varchar(64) NOT NULL DEFAULT 'newschatuser', user_passwd varchar(128) NOT NULL, user_log_ip varchar(128) DEFAULT NULL, is_delete enum('0','1') DEFAULT '0', PRIMARY KEY (user_ID),CONSTRAINT fk_user_ID FOREIGN KEY (user_ID) REFERENCES user_detail (user_ID),CONSTRAINT fk_user_ID1 FOREIGN KEY (user_ID) REFERENCES user_detail (user_ID)) ENGINE=InnoDB DEFAULT CHARSET='latin1';" createuser_detail = "CREATE TABLE user_detail (user_ID int(11) NOT NULL,user_gender enum('girl','boy','secret') DEFAULT 'secret',user_phone varchar(11) DEFAULT NULL,user_address varchar(50) DEFAULT NULL,is_delete enum('0','1') DEFAULT '0',PRIMARY KEY (user_ID)) ENGINE=InnoDB DEFAULT CHARSET='latin1';" operationhandle.create(createuser_detail) operationhandle.create(createuser_log)
def modifyIP(ck, userID): # print("ck is %s" %(ck)) usersocket = str(ck).split("raddr=('")[1].replace(")>", "").replace("', ", ":") # print(usersocket) modifyIPsql = "UPDATE user_log SET user_log_ip = " + '"' + usersocket + '"' + \ " WHERE user_ID = " + "'" + userID + "';" conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") # print(modifyIPsql) conneMysql.update(modifyIPsql)
def checkuser(userID, userpasswd): redissearch = connectRedis("westos") conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") conneMysql.connect() sqlsearch = "SELECT user_passwd FROM user_log WHERE user_ID = " + userID + ";" # print(sqlsearch) redisresult = str(redissearch.get(userID)) mysqlresult = str(conneMysql.getOne(sqlsearch)).replace("',)", "").replace("('", "") if redisresult == userpasswd or mysqlresult == userpasswd: return True else: return False
def logout(ck, recvdata): userID = recvdata.split(format)[2] sql = "UPDATE user_log SET user_log_ip = '0.0.0.0:00000' WHERE user_ID = '" + userID + "';" # print(sql) conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") conneMysql.connect() conneMysql.update(sql) sqlselect = "SELECT user_log_ip FROM user_log WHERE user_ID = '" + userID + "';" result = str(conneMysql.getOne(sqlselect)).replace("',)", "").replace("('", "") if result == "0.0.0.0:00000": senddata(ck, "&$&logout&$&" + userID + "&$&log out successful&$&") return True return False
def friendlist(ck, userID): sql = 'SELECT user_list FROM ' + "schat" + userID + ';' conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") sqlSelectName = "SELECT user_name FROM user_log WHERE user_ID = " # print(sql) friendList = conneMysql.getAll(sql) result = str(friendList).replace(",", "", ).replace(")", " ").replace("(", "", -1) # print(result) messages = "&$&friendlist&$&" + userID + "&$&" for user_ID in result.split(" "): # print(user_ID) sqlSelect = sqlSelectName + "'" + user_ID + "';" user_name = conneMysql.getOne(sqlSelect) user_name = str(user_name).split("',")[0].replace("('", "", -1) messages += str(user_ID) + "&$&" + user_name + "&$&" senddata(ck, messages)
def searchfriend(ck, recvdata): conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") conneMysql.connect() sqlDetail = str("SELECT user_name FROM user_log WHERE user_ID = " + recvdata.split("&$&")[3]) # print(sqlDetail) user_name = str(conneMysql.getOne(sqlDetail)) if user_name == "None": user_name = "No such user" user_name = "&$&searchfriend&$&" + userID + "&$&" + user_name + "&$&" senddata(ck, str(user_name)) return False else: user_name = user_name.replace(",)", "").replace("'", '').replace("(", "") user_name = "&$&searchfriend&$&" + userID + "&$&" + user_name + "&$&" senddata(ck, str(user_name)) return True
def delfriend(ck, recvdata): userID = str(recvdata).split("&$&")[2] user_table = "schat" + str(recvdata).split("&$&")[2] friend_ID = str(recvdata).split("&$&")[3] sql_exist = "SELECT user_list FROM " + user_table + " WHERE user_list = " + "'" + friend_ID + "';" sql_delete = "DELETE FROM " + user_table + " WHERE user_list = " + friend_ID + ";" # print(sql_delete) conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") conneMysql.connect() result = str(conneMysql.getOne(sql_exist)).replace("(", "").replace(")", "").replace(",", "") # print(result) if result == "None": senddata("&$&delfriend&$&" + userID + "&$&" + friend_ID + "&$&delete error&$&") else: conneMysql.delete(sql_delete) senddata(ck, "&$&delfriend&$&" + userID + "&$&" + friend_ID + "&$&successful&$&") friendlist(ck, userID)
def addfriend(ck, recvdata): user_table = "schat" + str(recvdata).split("&$&")[2] userID = str(recvdata).split("&$&")[2] # print(user_table) friend_ID = str(recvdata).split("&$&")[3] sql_exist = "SELECT user_list FROM " + user_table + " WHERE user_list = " + "'" + friend_ID + "';" sql_insert = "INSERT INTO " + user_table + "(user_list) values('" + friend_ID + "');" conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") conneMysql.connect() result = str(conneMysql.getOne(sql_exist)) result = result.replace("(", "").replace(")", "").replace(",", "") if result == friend_ID: senddata(ck, "&$&addfriend&$&" + userID + "&$&" + friend_ID + "&$&user exist in list&$&") friendlist(ck, userID) elif result == "None": conneMysql.insert(sql_insert) senddata(ck, "&$&addfriend&$&" + userID + "&$&" + friend_ID + "&$&successful&$&") friendlist(ck, userID)
def adduser(ck, userID, userName, userpass, userGender, phoneNumber, address): usersocket = str(ck).split("raddr=('")[1].replace(")>", "").replace("', ", ":") conneMysql = mysqlOperaction("localhost", "schatuser", "schat121214", "userInfo") conneMysql.connect() sqlDetail = str( 'INSERT INTO user_detail(user_ID, user_gender,user_phone,user_address) ' 'values(' + '"' + userID + '"' + ", " + '"' + userGender + '"' + ", " + '"' + phoneNumber + '"' + "," + '"' + address + '"' + ");") print(sqlDetail) sqlLog = str( 'INSERT INTO user_log(user_ID, user_name,user_passwd,user_log_ip) ' 'values(' + '"' + userID + '"' + ',' + '"' + userName + '"' + "," + '"' + userpass + '"' + "," + '"' + usersocket + '"' + ");") # print(sqlLog) createUserSql = "CREATE TABLE IF NOT EXISTS " + "schat" + userID + "(user_list INT PRIMARY KEY);" # print(createUserSql) result = conneMysql.insert(sqlDetail) # print(result) conneMysql.insert(sqlLog) conneMysql.create(createUserSql)