aboutsummaryrefslogtreecommitdiff
path: root/src/main
diff options
context:
space:
mode:
Diffstat (limited to 'src/main')
-rw-r--r--src/main/java/com/juick/service/ChatServiceImpl.java6
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java39
-rw-r--r--src/main/java/com/juick/service/TagServiceImpl.java12
-rw-r--r--src/main/java/com/juick/service/UserServiceImpl.java4
-rw-r--r--src/main/resources/data-sqlserver.sql4
-rw-r--r--src/main/resources/schema-sqlserver.sql17
6 files changed, 46 insertions, 36 deletions
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<Message> 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<String> 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<String> 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<ApplicationStatus> 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 (
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 17:39:03 +0000