def get_user_preference(self, user_id: str):
sqlCommand = f"""
SELECT region, kind, rent_price, pattern, space
FROM user_preference
WHERE id = %s
"""
try:
id = (user_id,)
result = self.__get_sql_result(sqlCommand, param=id)
if len(result) > 0:
# for element in result
pres = []
for element in result:
if len(element) == 5:
pref = Preference(
user_id=user_id,
region=element['region'],
kind=element['kind'],
rent_price=element['rent_price'],
pattern=element['pattern'],
space=element['space'])
pres.append(pref)
else:
raise TypeError
return pres
else:
return None
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def __create_user_token_table(self):
query = """CREATE TABLE IF NOT EXISTS `user_token` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(36) NOT NULL,
`fcm_token` varchar(255),
`create_date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `fcm_token` (`fcm_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
"""
try:
self.__execute_sql_command(query)
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def update_user_preference(self, preference: Preference):
sqlCommand = f"""
INSERT INTO user_preference(
{preference.sql_insert_field_string()}
)
VALUES(
{preference.sql_insert_value_string()}
)
ON DUPLICATE KEY UPDATE
{preference.on_duplicate_key_update_string()}
"""
try:
self.__execute_sql_command(sqlCommand)
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def update_user_token(self, user_token: User_token):
sqlCommand = """
INSERT INTO user_token(
user_id, fcm_token
)
VALUES(
%s, %s
)
"""
try:
data = (user_token.user_id, user_token.fcm_token)
self.__cursor.execute(sqlCommand, data)
self.__connection.commit()
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def __create_user_preference_table(self):
query = """CREATE TABLE IF NOT EXISTS `user_preference` (
`id` int(11) unsigned NOT NULL ,
`region` varchar(10) NOT NULL,
`kind` varchar(10),
`section` varchar(10),
`rent_price` int(11) unsigned,
`pattern` varchar(10),
`space` varchar(10),
`create_date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
"""
try:
self.__execute_sql_command(query)
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def register_user(self, user_name, email, password, phone, isAnonymous=False):
sqlCommand = """
INSERT INTO user_info(
name, email, password, phone, isAnonymous
)
VALUES(
%s, %s, %s, %s, %s
)
"""
try:
data = (user_name, email, password, phone, isAnonymous)
self.__cursor.execute(sqlCommand, data)
self.__connection.commit()
latest_user_id = str(self.__cursor.lastrowid)
print(f"latest_user_id: {latest_user_id}")
return latest_user_id
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def __create_user_info_table(self):
query = """CREATE TABLE IF NOT EXISTS `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`isAnonymous` boolean NOT NULL DEFAULT false,
`create_date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
"""
try:
self.__execute_sql_command(query)
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def get_user_id_with_password_hash(self, email):
sqlCommand = """
SELECT id, password
FROM user_info
WHERE email = %s
"""
try:
data = (email,)
result = self.__get_sql_result(sqlCommand, data)
if len(result) > 0:
print(f"result: {result}")
first_result = result[0]
id = first_result['id']
password_hash = first_result['password']
return (id, password_hash)
else:
return None
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)
def get_user_tokens(self, user_ids: list):
if type(user_ids) != list:
raise TypeError
if len(user_ids) == 0:
return []
sqlCommand = f"""
SELECT fcm_token
FROM user_token
WHERE user_id IN ({", ".join(user_ids)})
"""
print(f"get user fcm_token: {sqlCommand}")
try:
query_result = self.__get_sql_result(sqlCommand)
print(f"query_result: {query_result}")
result = []
for data in query_result:
result.append(str(data['fcm_token']))
return result
except connector.Error as error:
print(error.msg)
raise SqlError(error.msg)