From 556d10c400add4b69d8832a40fb704fda4333080 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 01:46:13 +0300 Subject: Improve SQL server compatibility --- .../java/com/juick/service/ChatServiceImpl.java | 6 ++-- .../com/juick/service/MessagesServiceImpl.java | 39 +++++++++++----------- .../java/com/juick/service/TagServiceImpl.java | 12 +++---- .../java/com/juick/service/UserServiceImpl.java | 4 +-- src/main/resources/data-sqlserver.sql | 4 +++ src/main/resources/schema-sqlserver.sql | 17 ++++++---- 6 files changed, 46 insertions(+), 36 deletions(-) (limited to 'src/main') diff --git a/src/main/java/com/juick/service/ChatServiceImpl.java b/src/main/java/com/juick/service/ChatServiceImpl.java index 9409d397..d1c4ce96 100644 --- a/src/main/java/com/juick/service/ChatServiceImpl.java +++ b/src/main/java/com/juick/service/ChatServiceImpl.java @@ -72,7 +72,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService { return getNamedParameterJdbcTemplate().query( "SELECT pm.user_id, pm.txt, pm.ts, users.nick FROM pm INNER JOIN users ON users.id=pm.user_id WHERE (user_id = :uid AND user_id_to = :uidTo) " - + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC LIMIT 20", + + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, (rs, rowNum) -> { Message msg = new Message(); @@ -92,7 +92,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService { public List getInbox(final int uid) { return getJdbcTemplate().query( "SELECT pm.user_id, users.nick, pm.txt, pm.ts " + - "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20", + "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", (rs, num) -> { Message msg = new Message(); msg.setUser(new User()); @@ -111,7 +111,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService { return getJdbcTemplate().query( "SELECT pm.user_id_to, users.nick, pm.txt, " + "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " + - "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20", + "WHERE pm.user_id=? ORDER BY pm.ts DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", (rs, num) -> { Message msg = new Message(); msg.setUser(new User()); diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 4247cee5..ef458979 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -279,7 +279,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ void updateRepliesBy(int mid) { List users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies " + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? " - + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class, + + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY", String.class, mid); String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(",")); getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid); @@ -519,7 +519,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + " SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid AND b.bl_user_id = m.user_id)" : ")") + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :visitorUid = bt.user_id)" - + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid) ORDER BY m.message_id DESC LIMIT 20", + + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid)" + + " ORDER BY m.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @@ -536,7 +537,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + (before > 0 ? " messages.message_id < :before AND " : StringUtils.EMPTY) + "(messages.privacy > 0 OR messages.user_id = :visitorUid) " + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid and b.bl_user_id = messages.user_id) " - + "ORDER BY messages.message_id DESC LIMIT :cnt", sqlParameterSource, Integer.class); + + "ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT :cnt ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -549,7 +550,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + " AND (messages.privacy > 0 OR messages.user_id = :visitorUid) " - + "ORDER BY messages.message_id DESC LIMIT :cnt", sqlParameterSource, Integer.class); + + "ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT :cnt ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -561,7 +562,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE place_id = :placeId " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) - + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC LIMIT 20", + + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @@ -588,7 +589,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)" : StringUtils.EMPTY) + ") " + (before > 0 ? "AND message_id < :before " : StringUtils.EMPTY) - + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -600,7 +601,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return getNamedParameterJdbcTemplate() .queryForList("SELECT message_id FROM messages WHERE user_id = :uid AND privacy < 0" + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) - + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -614,7 +615,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ "SELECT message_id FROM messages WHERE " + (to != 0 ? " updated < :to AND" : StringUtils.EMPTY) + " NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1" + " AND u.id = messages.user_id and u.id <> :uid) " - + " ORDER BY updated DESC, message_id DESC LIMIT 20", + + " ORDER BY updated DESC, message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, (rs, rowNum) -> rs.getInt(1)); } return getNamedParameterJdbcTemplate().query( @@ -623,7 +624,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "WHERE suser_id = :uid " + (to != 0 ? "AND updated < :to " : StringUtils.EMPTY) + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1" + " AND u.id = messages.user_id and u.id <> :uid) " - + "ORDER BY updated DESC, messages.message_id DESC LIMIT 20", + + "ORDER BY updated DESC, messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, (rs, rowNum) -> rs.getInt(1)); } @@ -636,7 +637,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return getNamedParameterJdbcTemplate().queryForList("SELECT f.message_id FROM favorites f WHERE " + "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)" + (before > 0 ? " AND f.message_id < :before " : StringUtils.EMPTY) - + "ORDER BY f.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + "ORDER BY f.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -652,7 +653,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :vid = bt.user_id)" + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :vid) " + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) " - + " ORDER BY m.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + " ORDER BY m.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -670,7 +671,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) - + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20", + + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @@ -687,7 +688,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + " WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid" + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + " AND messages.privacy >= :privacy AND users.banned = 0" - + " ORDER BY messages.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + " ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -706,7 +707,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ ? " AND ts >= :date" + " AND ts < :date" : StringUtils.EMPTY) - + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20", + + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @@ -728,7 +729,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + "(SELECT tag_id FROM messages_tags WHERE message_id = favorites.message_id) and :vid = bt.user_id)" + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) - + " ORDER BY messages.message_id DESC LIMIT 20) as r" + " UNION ALL " + + " ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) as r" + " UNION ALL " + "SELECT message_id FROM " + "(SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0" @@ -736,8 +737,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :vid = bt.user_id)" + (before > 0 ? " AND messages.message_id < :before" : StringUtils.EMPTY) - + " AND privacy >= :privacy ORDER BY messages.message_id DESC LIMIT 20) as m " - + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + " AND privacy >= :privacy ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) as m " + + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -749,7 +750,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return getNamedParameterJdbcTemplate().queryForList("SELECT message_id FROM favorites" + " INNER JOIN users" + " ON favorites.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0 " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) - + " ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); + + " ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @@ -762,7 +763,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ "SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid and users.banned = 0" + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) - + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", + + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class); } diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index f15fd113..bbf21bb0 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -148,7 +148,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { "SELECT tags.name,COUNT(messages.message_id) " + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + "AND messages.message_id=messages_tags.message_id)) " + - "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id, tags.name ORDER BY tags.name ASC", new TagStatsMapper(), uid); } @@ -165,8 +165,9 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Transactional(readOnly = true) @Override public List getPopularTags() { - return getJdbcTemplate().queryForList( - "select name from tags where noindex=false order by stat_messages desc limit 20", String.class); + return getJdbcTemplate().queryForList(""" + select name from tags where noindex=false + order by stat_messages desc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""", String.class); } @Transactional(readOnly = true) @@ -179,9 +180,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) - GROUP BY tags.tag_id HAVING COUNT(DISTINCT messages.user_id) > 1 - ORDER BY cnt DESC LIMIT 20 - """; + GROUP BY tags.tag_id, tags.name HAVING COUNT(DISTINCT messages.user_id) > 1 + ORDER BY cnt DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY"""; return getNamedParameterJdbcTemplate() .query(sql, new MapSqlParameterSource() .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE), diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index 5ae0b2d1..ca63c322 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -444,7 +444,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { "SELECT users.id,users.nick FROM (subscr_users " + "INNER JOIN users_subscr ON (subscr_users.suser_id=? " + "AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " + - "ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?", + "ON subscr_users.user_id=users.id ORDER BY cnt OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY", (rs, num) -> { User u = new User(); u.setUid(rs.getInt(1)); @@ -664,7 +664,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Override public ApplicationStatus getFbCrossPostStatus(final int uid) { List list = getJdbcTemplate().query( - "SELECT 1, crosspost FROM facebook WHERE user_id = ? LIMIT 1", + "SELECT 1, crosspost FROM facebook WHERE user_id = ?", (rs, num) -> { ApplicationStatus status = new ApplicationStatus(); diff --git a/src/main/resources/data-sqlserver.sql b/src/main/resources/data-sqlserver.sql index bb1203a4..f5844a52 100644 --- a/src/main/resources/data-sqlserver.sql +++ b/src/main/resources/data-sqlserver.sql @@ -1,7 +1,11 @@ +set identity_insert users ON; INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password'); INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password'); INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password'); +set identity_insert users OFF; +set identity_insert tags ON; INSERT INTO tags(tag_id, name) VALUES(2, 'juick'); +set identity_insert tags OFF; INSERT INTO reactions (like_id, description) VALUES (1, 'like'); INSERT INTO reactions (like_id, description) VALUES (2, 'love'); INSERT INTO reactions (like_id, description) VALUES (3, 'lol'); diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql index 7fb2d4e0..5fbe9569 100644 --- a/src/main/resources/schema-sqlserver.sql +++ b/src/main/resources/schema-sqlserver.sql @@ -1,5 +1,5 @@ CREATE TABLE users ( - id bigint NOT NULL, + id bigint IDENTITY(1,1) NOT NULL, nick character varying(64) NOT NULL, passw character varying(32) NOT NULL, lang VARCHAR(10) CHECK(lang IN ('ru', 'en', '__')) DEFAULT '__' NOT NULL, @@ -27,7 +27,7 @@ CREATE TABLE auth ( FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE tags ( - tag_id INTEGER PRIMARY KEY NOT NULL, + tag_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL, synonym_id integer, name character varying(70), [top] bit DEFAULT 0 NOT NULL, @@ -118,7 +118,7 @@ CREATE TABLE meon ( FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE messages ( - message_id INTEGER PRIMARY KEY NOT NULL, + message_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL, user_id bigint NOT NULL, lang TEXT DEFAULT '__' NOT NULL, ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, @@ -146,7 +146,7 @@ CREATE TABLE messages_properties ( message_id INTEGER PRIMARY KEY NOT NULL, reply_id smallint NOT NULL, property_key character varying(255) NOT NULL, - property_value text NOT NULL, + property_value character varying(255) NOT NULL, UNIQUE (message_id, reply_id, property_key), FOREIGN KEY (message_id) REFERENCES messages(message_id) ); @@ -157,8 +157,8 @@ CREATE TABLE messages_tags ( ); CREATE TABLE messages_txt ( message_id INTEGER NOT NULL, - repliesby text, - txt text NOT NULL, + repliesby varchar(max), + txt varchar(max) NOT NULL, updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id) ); @@ -220,6 +220,10 @@ CREATE TABLE subscr_users ( FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (suser_id) REFERENCES users(id) ); +CREATE TABLE users_subscr ( + user_id bigint NOT NULL, + cnt INTEGER NOT NULL DEFAULT 0 +); CREATE TABLE tags_ignore ( tag_id integer NOT NULL, FOREIGN KEY (tag_id) REFERENCES tags(tag_id) @@ -267,6 +271,7 @@ CREATE TABLE usersinfo ( gender character varying(32), bday character varying(32), descr text, + UNIQUE(user_id), FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE vk ( -- cgit v1.2.3