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)