From 834fef2a7794d769144c7c087a8b1fa0a2a9bb21 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 14 Jan 2023 12:22:53 +0300 Subject: DB: merge ios, android, winphone tables to user_services --- .../java/com/juick/service/PushQueriesService.java | 29 ++--- .../com/juick/service/PushQueriesServiceImpl.java | 124 ++++++--------------- src/main/java/com/juick/www/api/Notifications.java | 45 +++----- .../resources/db/migration/V1.30__token_type.sql | 4 + .../db/migration/V1.31__user_services.sql | 1 + .../db/migration/V1.32__drop_token_tables.sql | 2 + .../resources/db/specific/h2/V1.22__schema.sql | 2 +- 7 files changed, 63 insertions(+), 144 deletions(-) create mode 100644 src/main/resources/db/migration/V1.30__token_type.sql create mode 100644 src/main/resources/db/migration/V1.31__user_services.sql create mode 100644 src/main/resources/db/migration/V1.32__drop_token_tables.sql (limited to 'src/main') diff --git a/src/main/java/com/juick/service/PushQueriesService.java b/src/main/java/com/juick/service/PushQueriesService.java index ef33f318..7da89dee 100644 --- a/src/main/java/com/juick/service/PushQueriesService.java +++ b/src/main/java/com/juick/service/PushQueriesService.java @@ -1,5 +1,5 @@ /* - * Copyright (C) 2008-2020, Juick + * Copyright (C) 2008-2023, Juick * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as @@ -17,34 +17,19 @@ package com.juick.service; +import com.juick.model.ExternalToken; + import java.util.Collection; -import java.util.List; /** * Created by aalexeev on 11/13/16. */ public interface PushQueriesService { - List getGCMRegID(int uid); - - List getGCMTokens(Collection uids); - - boolean addGCMToken(Integer uid, String token); - - boolean deleteGCMToken(String token); - - List getMPNSURL(int uid); - - List getMPNSTokens(Collection uids); - - boolean addMPNSToken(Integer uid, String token); - - boolean deleteMPNSToken(String token); - - List getAPNSToken(int uid); + Collection getToken(int uid, String serviceType); - List getAPNSTokens(Collection uids); + Collection getTokens(Collection uids); - boolean addAPNSToken(Integer uid, String token); + boolean addToken(Integer uid, String serviceType, String token); - boolean deleteAPNSToken(String token); + boolean deleteToken(String serviceType, String token); } diff --git a/src/main/java/com/juick/service/PushQueriesServiceImpl.java b/src/main/java/com/juick/service/PushQueriesServiceImpl.java index 0b83465a..18a14bd6 100644 --- a/src/main/java/com/juick/service/PushQueriesServiceImpl.java +++ b/src/main/java/com/juick/service/PushQueriesServiceImpl.java @@ -1,5 +1,5 @@ /* - * Copyright (C) 2008-2020, Juick + * Copyright (C) 2008-2023, Juick * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as @@ -17,15 +17,18 @@ package com.juick.service; +import com.juick.model.ExternalToken; import org.apache.commons.collections4.CollectionUtils; import org.springframework.dao.DataIntegrityViolationException; +import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; +import java.sql.ResultSet; +import java.sql.SQLException; import java.util.Collection; import java.util.Collections; -import java.util.List; /** * Created by aalexeev on 11/13/16. @@ -33,113 +36,56 @@ import java.util.List; @Repository public class PushQueriesServiceImpl extends BaseJdbcService implements PushQueriesService { - @Transactional(readOnly = true) - @Override - public List getGCMRegID(final int uid) { - return getJdbcTemplate().queryForList( - "SELECT regid FROM android WHERE user_id=?", - String.class, - uid); - } - - @Transactional(readOnly = true) - @Override - public List getGCMTokens(final Collection uids) { - if (CollectionUtils.isEmpty(uids)) - return Collections.emptyList(); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT regid FROM android INNER JOIN users ON (users.id = android.user_id) WHERE users.id IN (:ids)", - new MapSqlParameterSource("ids", uids), - String.class); - } + private class TokenMapper implements RowMapper { - @Transactional - @Override - public boolean addGCMToken(Integer uid, String token) { - try { - return getJdbcTemplate().update("INSERT INTO android(user_id,regid) VALUES (?, ?)", - uid, token) > 0; - } catch (DataIntegrityViolationException e) { - return false; + @Override + public ExternalToken mapRow(ResultSet rs, int rowNum) throws SQLException { + return new ExternalToken( + null, + rs.getString("service_type"), + rs.getString("regid"), + null + ); } } - @Transactional - @Override - public boolean deleteGCMToken(String token) { - return getJdbcTemplate().update("DELETE FROM android WHERE regid=?", token) > 0; - } - @Transactional(readOnly = true) @Override - public List getMPNSURL(final int uid) { - return getJdbcTemplate().queryForList( - "SELECT url FROM winphone WHERE user_id=?", - String.class, - uid); + public Collection getToken(final int uid, final String serviceType) { + return getJdbcTemplate().query( + "SELECT regid, service_type FROM user_services WHERE user_id=? AND service_type=?", + new TokenMapper(), + uid, serviceType); } @Transactional(readOnly = true) @Override - public List getMPNSTokens(final Collection uids) { + public Collection getTokens(final Collection uids) { if (CollectionUtils.isEmpty(uids)) return Collections.emptyList(); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT url FROM winphone INNER JOIN users ON (users.id=winphone.user_id) WHERE users.id IN (:ids)", + return getNamedParameterJdbcTemplate().query( + """ + SELECT regid, service_type FROM user_services INNER JOIN users + ON (users.id = user_services.user_id) WHERE users.id IN (:ids)""", new MapSqlParameterSource("ids", uids), - String.class); - } - - @Transactional - @Override - public boolean addMPNSToken(Integer uid, String token) { - return getJdbcTemplate().update("INSERT IGNORE INTO winphone(user_id,url) VALUES (?, ?)", - uid, token) > 0; - } - - @Transactional - @Override - public boolean deleteMPNSToken(String token) { - return getJdbcTemplate().update("DELETE FROM winphone WHERE url=?", token) > 0; - } - - @Transactional(readOnly = true) - @Override - public List getAPNSToken(final int uid) { - return getJdbcTemplate().queryForList( - "SELECT token from ios WHERE user_id=?", - String.class, - uid); + new TokenMapper()); } @Transactional @Override - public boolean deleteAPNSToken(String token) { - return getJdbcTemplate().update("DELETE FROM ios WHERE token=?", token) > 0; - } - - @Transactional(readOnly = true) - @Override - public List getAPNSTokens(final Collection uids) { - if (CollectionUtils.isEmpty(uids)) - return Collections.emptyList(); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT token FROM ios INNER JOIN users ON (users.id = ios.user_id) WHERE users.id IN (:ids)", - new MapSqlParameterSource("ids", uids), - String.class); + public boolean addToken(final Integer uid, final String serviceType, final String token) { + try { + return getJdbcTemplate().update( + "INSERT INTO user_services(user_id, regid, service_type) VALUES (?, ?, ?)", + uid, token, serviceType) > 0; + } catch (DataIntegrityViolationException e) { + return false; + } } @Transactional @Override - public boolean addAPNSToken(Integer uid, String token) { - try { - return getJdbcTemplate().update("INSERT INTO ios(user_id,token) VALUES (?, ?)", - uid, token) > 0; - } catch (DataIntegrityViolationException e) { - return true; - } + public boolean deleteToken(final String serviceType, final String token) { + return getJdbcTemplate().update("DELETE FROM user_services WHERE regid=? AND service_type=?", token, serviceType) > 0; } } diff --git a/src/main/java/com/juick/www/api/Notifications.java b/src/main/java/com/juick/www/api/Notifications.java index e5e85eb7..1ebe3c61 100644 --- a/src/main/java/com/juick/www/api/Notifications.java +++ b/src/main/java/com/juick/www/api/Notifications.java @@ -19,10 +19,10 @@ package com.juick.www.api; import com.juick.model.*; import com.juick.service.*; -import com.juick.util.HttpBadRequestException; import com.juick.util.HttpForbiddenException; import io.swagger.v3.oas.annotations.Hidden; import io.swagger.v3.oas.annotations.Parameter; + import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.MediaType; @@ -31,6 +31,7 @@ import org.springframework.web.bind.annotation.*; import javax.inject.Inject; import java.util.Collections; import java.util.List; +import java.util.stream.Stream; /** * Created by vitalyster on 24.10.2016. @@ -55,17 +56,12 @@ public class Notifications { private User collectTokens(Integer uid) { User user = userService.getUserByUID(uid).orElse(AnonymousUser.INSTANCE); user.setUnreadCount(messagesService.getUnread(user).size()); - pushQueriesService.getGCMRegID(uid).forEach(t -> user.getTokens().add(new ExternalToken(null, "gcm", t, null))); - pushQueriesService.getAPNSToken(uid).forEach(t -> user.getTokens().add(new ExternalToken(null, "apns", t, null))); - pushQueriesService.getMPNSURL(uid).forEach(t -> user.getTokens().add(new ExternalToken(null, "mpns", t, null))); - List xmppJids = userService.getJIDsbyUID(uid).stream() - .map(jid -> new ExternalToken(null, "xmpp", jid, null)) - .toList(); - user.getTokens().addAll(xmppJids); - List tgIds = telegramService.getTelegramIdentifiers(Collections.singletonList(user)).stream() - .map(tgId -> new ExternalToken(null, "durov", String.valueOf(tgId), null)) - .toList(); - user.getTokens().addAll(tgIds); + var tokens = pushQueriesService.getTokens(List.of(user.getUid())); + var xmppJids = userService.getJIDsbyUID(uid).stream() + .map(jid -> new ExternalToken(null, "xmpp", jid, null)); + var tgIds = telegramService.getTelegramIdentifiers(Collections.singletonList(user)).stream() + .map(tgId -> new ExternalToken(null, "durov", String.valueOf(tgId), null)); + user.setTokens(Stream.concat(Stream.concat(tokens.stream(), xmppJids), tgIds).toList()); return user; } @@ -117,12 +113,7 @@ public class Notifications { throw new HttpForbiddenException(); } list.forEach(t -> { - switch (t.type()) { - case "gcm" -> pushQueriesService.deleteGCMToken(t.token()); - case "apns" -> pushQueriesService.deleteAPNSToken(t.token()); - case "mpns" -> pushQueriesService.deleteMPNSToken(t.token()); - default -> throw new HttpBadRequestException(); - } + pushQueriesService.deleteToken(t.type(), t.token()); }); return Status.OK; @@ -136,12 +127,7 @@ public class Notifications { throw new HttpForbiddenException(); } list.forEach(t -> { - switch (t.type()) { - case "gcm" -> pushQueriesService.deleteGCMToken(t.token()); - case "apns" -> pushQueriesService.deleteAPNSToken(t.token()); - case "mpns" -> pushQueriesService.deleteMPNSToken(t.token()); - default -> throw new HttpBadRequestException(); - } + pushQueriesService.deleteToken(t.type(), t.token()); }); return Status.OK; @@ -153,12 +139,7 @@ public class Notifications { @Parameter(hidden = true) User visitor, @RequestBody List list) { list.forEach(t -> { - switch (t.type()) { - case "gcm" -> pushQueriesService.addGCMToken(visitor.getUid(), t.token()); - case "apns" -> pushQueriesService.addAPNSToken(visitor.getUid(), t.token()); - case "mpns" -> pushQueriesService.addMPNSToken(visitor.getUid(), t.token()); - default -> throw new HttpBadRequestException(); - } + pushQueriesService.addToken(visitor.getUid(), t.type(), t.token()); }); return Status.OK; } @@ -168,7 +149,7 @@ public class Notifications { public Status doAndroidRegister( @Parameter(hidden = true) User visitor, @RequestParam(name = "regid") String regId) { - pushQueriesService.addGCMToken(visitor.getUid(), regId); + pushQueriesService.addToken(visitor.getUid(),"fcm", regId); return Status.OK; } @@ -177,7 +158,7 @@ public class Notifications { public Status doWinphoneRegister( @Parameter(hidden = true) User visitor, @RequestParam(name = "url") String regId) { - pushQueriesService.addMPNSToken(visitor.getUid(), regId); + pushQueriesService.addToken(visitor.getUid(), "mpns", regId); return Status.OK; } } diff --git a/src/main/resources/db/migration/V1.30__token_type.sql b/src/main/resources/db/migration/V1.30__token_type.sql new file mode 100644 index 00000000..601695d8 --- /dev/null +++ b/src/main/resources/db/migration/V1.30__token_type.sql @@ -0,0 +1,4 @@ +alter table android add column service_type varchar(255) not null default 'fcm'; +insert into android (user_id, ts, regid, service_type) select user_id, ts, url regid, 'mpns' from winphone; +insert into android (user_id, ts, regid, service_type) select user_id, ts, token regid, 'apns' from ios; + diff --git a/src/main/resources/db/migration/V1.31__user_services.sql b/src/main/resources/db/migration/V1.31__user_services.sql new file mode 100644 index 00000000..cca1f9dd --- /dev/null +++ b/src/main/resources/db/migration/V1.31__user_services.sql @@ -0,0 +1 @@ +alter table android rename to user_services diff --git a/src/main/resources/db/migration/V1.32__drop_token_tables.sql b/src/main/resources/db/migration/V1.32__drop_token_tables.sql new file mode 100644 index 00000000..a1599670 --- /dev/null +++ b/src/main/resources/db/migration/V1.32__drop_token_tables.sql @@ -0,0 +1,2 @@ +DROP TABLE ios; +DROP TABLE winphone diff --git a/src/main/resources/db/specific/h2/V1.22__schema.sql b/src/main/resources/db/specific/h2/V1.22__schema.sql index de6946e8..46d39ac6 100644 --- a/src/main/resources/db/specific/h2/V1.22__schema.sql +++ b/src/main/resources/db/specific/h2/V1.22__schema.sql @@ -3,7 +3,7 @@ SET DB_CLOSE_DELAY -1; CREATE MEMORY TABLE "PUBLIC"."ANDROID"( "USER_ID" INTEGER NOT NULL, - "REGID" CHARACTER(255) NOT NULL, + "REGID" VARCHAR(255) NOT NULL, "TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ); -- 0 +/- SELECT COUNT(*) FROM PUBLIC.ANDROID; -- cgit v1.2.3