h2db: disable mysql mode

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` (