aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java12
-rw-r--r--juick-server-jdbc/src/main/resources/schema.sql151
2 files changed, 74 insertions, 89 deletions
diff --git a/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java
index 67d7b386..b68e0236 100644
--- a/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java
+++ b/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java
@@ -18,6 +18,7 @@
package com.juick.service;
import com.juick.User;
+import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
@@ -39,10 +40,13 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
uidFrom, uid_to, body) > 0;
if (ret) {
- getJdbcTemplate().update(
- "INSERT INTO pm_streams(user_id, user_id_to, lastmessage, unread) VALUES (?, ?, NOW(), 1) "
- + "ON DUPLICATE KEY UPDATE lastmessage = CURRENT_TIMESTAMP(), unread = unread + 1",
- uidFrom, uid_to);
+ try {
+ getJdbcTemplate().update(
+ "INSERT INTO pm_streams(user_id, user_id_to, lastmessage, unread) VALUES (?, ?, NOW(), 1)",
+ uidFrom, uid_to);
+ } catch (DuplicateKeyException e) {
+ // ignore
+ }
}
return ret;
}
diff --git a/juick-server-jdbc/src/main/resources/schema.sql b/juick-server-jdbc/src/main/resources/schema.sql
index 13fe1413..296fd486 100644
--- a/juick-server-jdbc/src/main/resources/schema.sql
+++ b/juick-server-jdbc/src/main/resources/schema.sql
@@ -1,4 +1,3 @@
-SET MODE MYSQL;
SET DB_CLOSE_ON_EXIT TRUE;
CREATE TABLE IF NOT EXISTS `android` (
@@ -22,12 +21,6 @@ CREATE TABLE IF NOT EXISTS `bl_users` (
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`,`bl_user_id`)
);
-CREATE TABLE IF NOT EXISTS `emails` (
- `user_id` int(10) unsigned NOT NULL,
- `email` char(64) NOT NULL,
- `subscr_hour` tinyint(4) DEFAULT NULL,
- KEY `email` (`email`)
-);
CREATE TABLE IF NOT EXISTS `facebook` (
`user_id` int(10) unsigned DEFAULT NULL,
`fb_id` bigint(20) unsigned NOT NULL,
@@ -36,8 +29,7 @@ CREATE TABLE IF NOT EXISTS `facebook` (
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`fb_name` char(64) NOT NULL,
`fb_link` char(255) NOT NULL,
- `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1',
- KEY `user_id` (`user_id`)
+ `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1'
);
CREATE TABLE IF NOT EXISTS `reactions` (
@@ -69,29 +61,6 @@ CREATE TABLE IF NOT EXISTS `images` (
`width` int(10) unsigned NOT NULL,
PRIMARY KEY (`mid`,`rid`)
);
-CREATE TABLE IF NOT EXISTS `ios` (
- `user_id` int(10) unsigned NOT NULL,
- `token` char(64) NOT NULL,
- `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- UNIQUE KEY `token` (`token`),
- KEY (`user_id`)
-);
-
-CREATE TABLE IF NOT EXISTS `jids` (
- `user_id` int(10) unsigned DEFAULT NULL,
- `jid` char(64) NOT NULL,
- `active` tinyint(1) NOT NULL DEFAULT '1',
- `loginhash` char(36) DEFAULT NULL,
- `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- UNIQUE KEY `jid` (`jid`),
- KEY (`user_id`)
-);
-
-CREATE TABLE IF NOT EXISTS `logins` (
- `user_id` int(10) unsigned NOT NULL,
- `hash` char(16) NOT NULL,
- UNIQUE KEY (`user_id`)
-);
CREATE TABLE IF NOT EXISTS `mail` (
`user_id` int(10) unsigned NOT NULL,
@@ -108,45 +77,17 @@ CREATE TABLE IF NOT EXISTS `meon` (
PRIMARY KEY (`id`)
);
-CREATE TABLE IF NOT EXISTS `messages` (
- `message_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `user_id` int(10) unsigned NOT NULL,
- `lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__',
- `ts` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `replies` smallint(5) unsigned NOT NULL DEFAULT '0',
- `maxreplyid` smallint(5) unsigned NOT NULL DEFAULT '0',
- `privacy` tinyint(4) NOT NULL DEFAULT '1',
- `readonly` tinyint(1) NOT NULL DEFAULT '0',
- `attach` nchar(3) check (attach in ('jpg', 'mp4', 'png')),
- `place_id` int(10) unsigned DEFAULT NULL,
- `lat` decimal(10,7) DEFAULT NULL,
- `lon` decimal(10,7) DEFAULT NULL,
- `popular` tinyint(4) NOT NULL DEFAULT '0',
- `hidden` tinyint(3) unsigned NOT NULL DEFAULT '0',
- `likes` smallint(6) NOT NULL DEFAULT '0',
- `updated` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
- PRIMARY KEY (`message_id`),
- KEY (`user_id`),
- KEY (`ts`),
- KEY (`attach`),
- KEY (`place_id`),
- KEY (`popular`),
- KEY (`hidden`),
- KEY (`updated`,`message_id`)
-);
+
CREATE TABLE IF NOT EXISTS `messages_access` (
`message_id` int(10) unsigned NOT NULL,
- `user_id` int(10) unsigned NOT NULL,
- KEY (`message_id`)
+ `user_id` int(10) unsigned NOT NULL
);
CREATE TABLE IF NOT EXISTS `messages_tags` (
`message_id` int(10) unsigned NOT NULL,
`tag_id` int(10) unsigned NOT NULL,
- UNIQUE KEY `message_id_2` (`message_id`,`tag_id`),
- KEY (`message_id`),
- KEY (`tag_id`)
+ UNIQUE KEY `message_id_2` (`message_id`,`tag_id`)
);
CREATE TABLE IF NOT EXISTS `messages_txt` (
@@ -196,13 +137,6 @@ CREATE TABLE IF NOT EXISTS `pm` (
`txt` text NOT NULL
);
-CREATE TABLE IF NOT EXISTS `pm_inroster` (
- `user_id` int(10) unsigned NOT NULL,
- `jid` char(64) NOT NULL,
- UNIQUE KEY (`user_id`,`jid`),
- KEY (`user_id`)
-);
-
CREATE TABLE IF NOT EXISTS `pm_streams` (
`user_id` int(10) unsigned NOT NULL,
`user_id_to` int(10) unsigned NOT NULL,
@@ -226,10 +160,7 @@ CREATE TABLE IF NOT EXISTS `replies` (
`replyto` smallint(5) unsigned NOT NULL DEFAULT '0',
`ts` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`attach` nchar(3) check (attach in ('jpg', 'mp4', 'png')),
- `txt` mediumtext NOT NULL,
- KEY (`message_id`),
- KEY (`user_id`),
- KEY (`ts`)
+ `txt` mediumtext NOT NULL
);
CREATE TABLE IF NOT EXISTS `subscr_messages` (
@@ -251,8 +182,7 @@ CREATE TABLE IF NOT EXISTS `subscr_users` (
`jid` char(64) DEFAULT NULL,
`active` bit(1) NOT NULL DEFAULT TRUE,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- UNIQUE KEY (`user_id`,`suser_id`),
- KEY (`suser_id`)
+ UNIQUE KEY (`user_id`,`suser_id`)
);
CREATE TABLE IF NOT EXISTS `tags` (
@@ -263,8 +193,7 @@ CREATE TABLE IF NOT EXISTS `tags` (
`noindex` tinyint(1) unsigned NOT NULL DEFAULT '0',
`stat_messages` int(10) unsigned NOT NULL DEFAULT '0',
`stat_users` smallint(5) unsigned NOT NULL DEFAULT '0',
- PRIMARY KEY (`tag_id`),
- KEY (`synonym_id`)
+ PRIMARY KEY (`tag_id`)
);
CREATE TABLE IF NOT EXISTS `tags_ignore` (
@@ -279,7 +208,7 @@ CREATE TABLE IF NOT EXISTS `tags_synonyms` (
CREATE TABLE IF NOT EXISTS `telegram` (
`user_id` int(10) unsigned DEFAULT NULL,
`tg_id` bigint(20) NOT NULL,
- `tg_name` char(64) NOT NULL,
+ `tg_name` char(64) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`loginhash` char(36) DEFAULT NULL
);
@@ -325,8 +254,7 @@ CREATE TABLE IF NOT EXISTS `useroptions` (
`privacy_reply` tinyint(1) NOT NULL DEFAULT '1',
`privacy_pm` tinyint(1) NOT NULL DEFAULT '1',
`repliesview` tinyint(1) NOT NULL DEFAULT '0',
- PRIMARY KEY (`user_id`),
- KEY `recommendations` (`recommendations`)
+ PRIMARY KEY (`user_id`)
);
CREATE TABLE IF NOT EXISTS `users` (
@@ -345,8 +273,7 @@ CREATE TABLE IF NOT EXISTS `users` (
CREATE TABLE IF NOT EXISTS `users_refs` (
`user_id` int(10) unsigned NOT NULL,
- `ref` int(10) unsigned NOT NULL,
- KEY `ref` (`ref`)
+ `ref` int(10) unsigned NOT NULL
);
CREATE TABLE IF NOT EXISTS `users_subscr` (
@@ -366,6 +293,60 @@ CREATE TABLE IF NOT EXISTS `usersinfo` (
`descr` varchar(255) DEFAULT NULL,
PRIMARY KEY (`user_id`)
);
+CREATE TABLE IF NOT EXISTS `emails` (
+ `user_id` int(10) unsigned NOT NULL,
+ `email` char(64) NOT NULL PRIMARY KEY,
+ `subscr_hour` tinyint(4) DEFAULT NULL,
+ foreign key (user_id) references users(id)
+);
+CREATE TABLE IF NOT EXISTS `ios` (
+ `user_id` int(10) unsigned NOT NULL,
+ `token` char(64) NOT NULL,
+ `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ UNIQUE KEY `token` (`token`),
+ foreign key (user_id) references users(id)
+);
+
+CREATE TABLE IF NOT EXISTS `jids` (
+ `user_id` int(10) unsigned DEFAULT NULL,
+ `jid` char(64) NOT NULL,
+ `active` tinyint(1) NOT NULL DEFAULT '1',
+ `loginhash` char(36) DEFAULT NULL,
+ `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ UNIQUE KEY `jid` (`jid`),
+ foreign key (user_id) references users(id)
+);
+
+CREATE TABLE IF NOT EXISTS `logins` (
+ `user_id` int(10) unsigned NOT NULL,
+ `hash` char(16) NOT NULL,
+ UNIQUE KEY (`user_id`)
+);
+CREATE TABLE IF NOT EXISTS `messages` (
+ `message_id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ `user_id` int(10) unsigned NOT NULL,
+ `lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__',
+ `ts` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `replies` smallint(5) unsigned NOT NULL DEFAULT '0',
+ `maxreplyid` smallint(5) unsigned NOT NULL DEFAULT '0',
+ `privacy` tinyint(4) NOT NULL DEFAULT '1',
+ `readonly` tinyint(1) NOT NULL DEFAULT '0',
+ `attach` nchar(3) check (attach in ('jpg', 'mp4', 'png')),
+ `place_id` int(10) unsigned DEFAULT NULL,
+ `lat` decimal(10,7) DEFAULT NULL,
+ `lon` decimal(10,7) DEFAULT NULL,
+ `popular` tinyint(4) NOT NULL DEFAULT '0',
+ `hidden` tinyint(3) unsigned NOT NULL DEFAULT '0',
+ `likes` smallint(6) NOT NULL DEFAULT '0',
+ `updated` timestamp(9) NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ FOREIGN KEY (`user_id`) references users(id)
+);
+CREATE TABLE IF NOT EXISTS `pm_inroster` (
+ `user_id` int(10) unsigned NOT NULL,
+ `jid` char(64) NOT NULL,
+ UNIQUE KEY (`user_id`,`jid`),
+ FOREIGN KEY (`user_id`) references users(id)
+);
CREATE TABLE IF NOT EXISTS `version` (
`version` bigint(20) NOT NULL
@@ -380,7 +361,7 @@ CREATE TABLE IF NOT EXISTS `vk` (
`vk_name` char(64) NOT NULL,
`vk_link` char(64) NOT NULL,
`crosspost` bit(1) unsigned NOT NULL DEFAULT FALSE,
- KEY (`user_id`)
+ FOREIGN KEY (`user_id`) references users(id)
);
CREATE TABLE IF NOT EXISTS `winphone` (
@@ -388,7 +369,7 @@ CREATE TABLE IF NOT EXISTS `winphone` (
`url` char(255) NOT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY (`url`),
- KEY (`user_id`)
+ FOREIGN KEY (`user_id`) references users(id)
);
CREATE TABLE IF NOT EXISTS `wl_users` (
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-29 01:51:59 +0000