diff options
author | 2018-03-28 11:27:29 +0300 | |
---|---|---|
committer | 2018-03-28 12:10:11 +0300 | |
commit | 94f85e2ec113efee03def8ae9539cdaee21cccf9 (patch) | |
tree | 6946277aef3931e4c5aebd060ff2b5264c8599e3 /juick-server-jdbc/src/main/java | |
parent | acd6d0180c67a62079652bd879a3e7c661fc2c90 (diff) |
drop sphinx search and use datasource autoconfiguration
Diffstat (limited to 'juick-server-jdbc/src/main/java')
-rw-r--r-- | juick-server-jdbc/src/main/java/com/juick/configuration/DataConfiguration.java | 300 | ||||
-rw-r--r-- | juick-server-jdbc/src/main/java/com/juick/configuration/SearchConfiguration.java | 58 | ||||
-rw-r--r-- | juick-server-jdbc/src/main/java/com/juick/service/FTSSearchServiceImpl.java (renamed from juick-server-jdbc/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java) | 47 | ||||
-rw-r--r-- | juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java | 1 |
4 files changed, 15 insertions, 391 deletions
diff --git a/juick-server-jdbc/src/main/java/com/juick/configuration/DataConfiguration.java b/juick-server-jdbc/src/main/java/com/juick/configuration/DataConfiguration.java deleted file mode 100644 index 9f59f965..00000000 --- a/juick-server-jdbc/src/main/java/com/juick/configuration/DataConfiguration.java +++ /dev/null @@ -1,300 +0,0 @@ -/* - * Copyright (C) 2008-2017, 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 - * published by the Free Software Foundation, either version 3 of the - * License, or (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU Affero General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - */ - -package com.juick.configuration; - -import com.juick.service.search.SearchService; -import org.apache.commons.dbcp2.BasicDataSource; -import org.springframework.beans.factory.annotation.Value; -import org.springframework.context.annotation.Bean; -import org.springframework.context.annotation.ComponentScan; -import org.springframework.context.annotation.Configuration; -import org.springframework.context.support.PropertySourcesPlaceholderConfigurer; -import org.springframework.dao.DuplicateKeyException; -import org.springframework.jdbc.core.JdbcTemplate; -import org.springframework.jdbc.datasource.DataSourceTransactionManager; -import org.springframework.transaction.PlatformTransactionManager; -import org.springframework.transaction.annotation.EnableTransactionManagement; -import org.springframework.transaction.annotation.TransactionManagementConfigurer; - -import javax.sql.DataSource; -import java.util.Collections; -import java.util.List; - -/** - * Created by aalexeev on 11/11/16. - */ -@Configuration -@EnableTransactionManagement -@ComponentScan(basePackages = {"com.juick.service"}) -public class DataConfiguration implements TransactionManagementConfigurer { - @Value("${datasource_driver:org.h2.Driver}") - private String datasourceDriver; - @Value("${datasource_url:jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;mode=MYSQL}") - private String datasourceUrl; - @Value("${datasource_user:juick}") - private String datasourceUser; - @Value("${datasource_password:secret}") - private String datasourcePassword; - - // NOTE: The close() method will be called automatically with default @Bean settings - // But Datasource interface has no close() method - @Bean(destroyMethod = "") - public DataSource dataSource() { - BasicDataSource dataSource = new BasicDataSource(); - - dataSource.setDriverClassName(datasourceDriver); - dataSource.setUrl(datasourceUrl); - dataSource.setUsername(datasourceUser); - dataSource.setPassword(datasourcePassword); - - dataSource.setValidationQuery("select 1"); - - return dataSource; - } - - @Bean - public PlatformTransactionManager transactionManager() { - return new DataSourceTransactionManager(dataSource()); - } - - @Override - public PlatformTransactionManager annotationDrivenTransactionManager() { - return transactionManager(); - } - - @Bean - public JdbcTemplate jdbcTemplate() { - JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource()); - if (datasourceDriver.equals("org.h2.Driver")) { - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS bl_users (user_id int(10) unsigned NOT NULL, " + - "bl_user_id int(10) unsigned NOT NULL, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP )"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS bl_tags (user_id int(10) unsigned NOT NULL, " + - "tag_id int(10) unsigned NOT NULL)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS wl_users (user_id int(10) unsigned NOT NULL, " + - "wl_user_id int(10) unsigned NOT NULL, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP )"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS messages (" + - "message_id int(10) unsigned NOT NULL AUTO_INCREMENT," + - "user_id int(10) unsigned NOT NULL," + - "place_id int(10) unsigned DEFAULT NULL," + - "lat decimal(10,7) DEFAULT NULL," + - "lon decimal(10,7) DEFAULT NULL," + - "ts TIMESTAMP 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'," + - "attach nchar(3) check (attach in ('jpg', 'mp4', 'png'))," + - "readonly tinyint(1) NOT NULL DEFAULT 0," + - "likes smallint(6) NOT NULL DEFAULT 0," + - "`popular` tinyint(4) NOT NULL DEFAULT '0'," + - "hidden tinyint(3) unsigned NOT NULL DEFAULT 0," + - "updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS messages_tags (" + - "message_id int(10) unsigned NOT NULL," + - "tag_id int(10) unsigned NOT NULL" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS users (" + - "id int(10) unsigned NOT NULL AUTO_INCREMENT," + - "nick char(64) NOT NULL," + - "`lang` enum('en','ru','fr','fa','__') NOT NULL DEFAULT '__'," + - "passw char(32) NOT NULL," + - "banned tinyint(3) unsigned NOT NULL DEFAULT 0, " + - "PRIMARY KEY(id), UNIQUE KEY(nick))"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS useroptions (" + - "user_id int(10) unsigned NOT NULL," + - "jnotify tinyint(1) NOT NULL DEFAULT 1," + - "`repliesview` tinyint(1) NOT NULL DEFAULT '0'," + - "`subscr_notify` tinyint(1) NOT NULL DEFAULT '1'," + - "`recommendations` tinyint(1) NOT NULL DEFAULT '1'," + - "subscr_active tinyint(1) NOT NULL DEFAULT 1)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS subscr_users (" + - "user_id int(10) unsigned NOT NULL," + - "suser_id int(10) unsigned NOT NULL," + - "jid char(64) DEFAULT NULL," + - "active tinyint(1) NOT NULL DEFAULT 1," + - "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " + - "PRIMARY KEY (suser_id, user_id))"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS subscr_messages (" + - "message_id int(10) unsigned NOT NULL," + - "suser_id int(10) unsigned NOT NULL, PRIMARY KEY (suser_id, message_id))"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS subscr_tags (" + - "tag_id int(10) unsigned NOT NULL," + - "suser_id int(10) unsigned NOT NULL, PRIMARY KEY (suser_id, tag_id))"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS messages_txt (" + - "message_id int(10) unsigned NOT NULL," + - "tags varchar(255)," + - "repliesby varchar(96)," + - "txt TEXT)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS tags(" + - "tag_id int(10) unsigned NOT NULL AUTO_INCREMENT," + - "synonym_id int(10) unsigned DEFAULT NULL," + - "name varchar_ignorecase(48) NOT NULL," + - "top 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)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS jids(" + - "user_id int(10) unsigned NOT NULL AUTO_INCREMENT," + - "jid char(64) NOT NULL," + - "active tinyint(1) unsigned NOT NULL DEFAULT '1'," + - "loginhash char(36) unsigned DEFAULT NULL," + - "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS replies (" + - "message_id int(10) unsigned NOT NULL," + - "reply_id smallint(5) unsigned NOT NULL," + - "user_id int(10) unsigned NOT NULL," + - "replyto smallint(5) unsigned NOT NULL DEFAULT 0," + - "ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP," + - "attach nchar(3) check (attach in ('jpg', 'mp4', 'png'))," + - "txt text)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS twitter(" + - "user_id int(10) unsigned NOT NULL," + - "access_token char(64) NOT NULL," + - "access_token_secret char(64) NOT NULL," + - "crosspost tinyint(1) unsigned NOT NULL DEFAULT '1'," + - "uname char(64) NOT NULL," + - "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS facebook(" + - "user_id int(10) unsigned NOT NULL," + - "fb_id int(20) unsigned NOT NULL," + - "access_token char(255) NOT NULL," + - "loginhash char(36) NOT NULL," + - "crosspost tinyint(1) unsigned NOT NULL DEFAULT '1'," + - "fb_name char(64) NOT NULL," + - "fb_link char(64) NOT NULL," + - "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS vk(" + - "user_id int(10) unsigned NOT NULL," + - "vk_id int(20) unsigned NOT NULL," + - "access_token char(128) NOT NULL," + - "loginhash char(36) NOT NULL," + - "crosspost tinyint(1) unsigned NOT NULL DEFAULT '1'," + - "vk_name char(64) NOT NULL," + - "vk_link char(64) NOT NULL," + - "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS emails (" + - " user_id int(10) unsigned NOT NULL," + - " email char(64) NOT NULL," + - " subscr_hour tinyint(4) DEFAULT NULL," + - " PRIMARY KEY (email)" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS favorites (" + - " user_id int(10) unsigned NOT NULL," + - " message_id int(10) unsigned NOT NULL," + - " ts datetime NOT NULL" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS logins (" + - " user_id int(10) unsigned NOT NULL," + - " hash char(16) NOT NULL," + - " PRIMARY KEY (user_id)" + - ")"); - jdbcTemplate.execute("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," + - " `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP," + - " `loginhash` char(36) DEFAULT NULL" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `pm` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `user_id_to` int(10) unsigned NOT NULL," + - " `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP," + - " `txt` text NOT NULL" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `pm_streams` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `user_id_to` int(10) unsigned NOT NULL," + - " `lastmessage` datetime NOT NULL," + - " `lastview` datetime DEFAULT NULL," + - " `unread` smallint(5) unsigned NOT NULL DEFAULT '0'" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `android` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `regid` char(255) NOT NULL," + - " `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP" + - ")"); - jdbcTemplate.execute("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" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `winphone` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `url` char(255) NOT NULL," + - " `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `tags_ignore` (" + - " `tag_id` int(10) unsigned NOT NULL" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `users_subscr` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `cnt` smallint(5) unsigned NOT NULL DEFAULT '0'," + - " PRIMARY KEY (`user_id`)" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `auth` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `protocol` enum('xmpp','email','sms') NOT NULL," + - " `account` char(64) NOT NULL," + - " `authcode` char(8) NOT NULL" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `mail` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `hash` char(16) NOT NULL," + - " PRIMARY KEY (`user_id`)" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `usersinfo` (" + - " `user_id` int(10) unsigned NOT NULL," + - " `jid` char(32) DEFAULT NULL," + - " `fullname` char(32) DEFAULT NULL," + - " `country` char(32) DEFAULT NULL," + - " `url` char(64) DEFAULT NULL," + - " `gender` char(32) DEFAULT NULL," + - " `bday` char(10) DEFAULT NULL," + - " `descr` varchar(255) DEFAULT NULL," + - " PRIMARY KEY (`user_id`)" + - ")"); - jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS `telegram_chats` (\n" + - " `chat_id` bigint(20) DEFAULT NULL,\n" + - " UNIQUE KEY `chat_id` (`chat_id`)\n" + - ")"); - } - return jdbcTemplate; - } - - @Bean - public SearchService emptySearchService() { - return new SearchService() { - @Override - public void setMaxResult(int maxResult) { - } - - @Override - public List<Integer> searchInAllMessages(String searchString, int messageIdBefore) { - return Collections.emptyList(); - } - - @Override - public List<Integer> searchByStringAndUser(String searchString, int userId, int messageIdBefore) { - return Collections.emptyList(); - } - }; - } - @Bean - public static PropertySourcesPlaceholderConfigurer propertyPlaceholderConfigurer() { - return new PropertySourcesPlaceholderConfigurer(); - } -} diff --git a/juick-server-jdbc/src/main/java/com/juick/configuration/SearchConfiguration.java b/juick-server-jdbc/src/main/java/com/juick/configuration/SearchConfiguration.java deleted file mode 100644 index 26b5f888..00000000 --- a/juick-server-jdbc/src/main/java/com/juick/configuration/SearchConfiguration.java +++ /dev/null @@ -1,58 +0,0 @@ -/* - * Copyright (C) 2008-2017, 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 - * published by the Free Software Foundation, either version 3 of the - * License, or (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU Affero General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - */ - -package com.juick.configuration; - -import com.juick.service.search.SearchService; -import com.juick.service.search.SphinxSearchServiceImpl; -import org.apache.commons.dbcp2.BasicDataSource; -import org.apache.commons.lang3.StringUtils; -import org.springframework.context.annotation.Bean; -import org.springframework.context.annotation.Configuration; -import org.springframework.context.annotation.DependsOn; -import org.springframework.core.env.Environment; - -import javax.annotation.Resource; - -/** - * Created by aalexeev on 11/18/16. - */ -@Configuration -public class SearchConfiguration { - @Resource - private Environment env; - - // NOTE: The close() method will be called automatically with default @Bean settings - // But Datasource interface has no close() method - @Bean(destroyMethod = "") - public BasicDataSource searchDataSource() { - BasicDataSource dataSource = new BasicDataSource(); - - dataSource.setDriverClassName(env.getProperty("sphinx_driver", "com.mysql.jdbc.Driver")); - dataSource.setUrl(env.getProperty("sphinx_url")); - dataSource.setUsername(env.getProperty("sphinx_user", StringUtils.EMPTY)); - dataSource.setPassword(env.getProperty("sphinx_password", StringUtils.EMPTY)); - - return dataSource; - } - - @Bean - @DependsOn("searchDataSource") - public SearchService searchService() { - return new SphinxSearchServiceImpl(searchDataSource()); - } -} diff --git a/juick-server-jdbc/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/FTSSearchServiceImpl.java index e4287502..8d2ffbdb 100644 --- a/juick-server-jdbc/src/main/java/com/juick/service/search/SphinxSearchServiceImpl.java +++ b/juick-server-jdbc/src/main/java/com/juick/service/FTSSearchServiceImpl.java @@ -15,15 +15,13 @@ * along with this program. If not, see <http://www.gnu.org/licenses/>. */ -package com.juick.service.search; +package com.juick.service; import org.apache.commons.lang3.StringUtils; -import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; -import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; -import org.springframework.util.Assert; +import org.springframework.stereotype.Repository; +import org.springframework.transaction.annotation.Transactional; -import javax.sql.DataSource; import java.util.Collections; import java.util.List; @@ -31,28 +29,13 @@ import java.util.List; * Created by aalexeev on 11/18/16. */ -/* Note - * Don't use any spring's component annotation (such as @Repository, @Service, @Component, etc). - * This class directly used by spring's search configuration class - */ -public class SphinxSearchServiceImpl implements SearchService { - private static final int DEFAULT_MAX_RESULT = 25; - - private final NamedParameterJdbcTemplate namedParameterSearchJdbcTemplate; +@Repository +@Transactional(readOnly = true) +public class FTSSearchServiceImpl extends BaseJdbcService implements SearchService { + private static final int DEFAULT_MAX_RESULT = 20; private int maxResult = DEFAULT_MAX_RESULT; - - public SphinxSearchServiceImpl(JdbcTemplate searchJdbcTemplate) { - Assert.notNull(searchJdbcTemplate, "JdbcTemplate must be initialized"); - this.namedParameterSearchJdbcTemplate = new NamedParameterJdbcTemplate(searchJdbcTemplate); - } - - public SphinxSearchServiceImpl(DataSource searchDataSource) { - Assert.notNull(searchDataSource, "DataSource must be initialized"); - this.namedParameterSearchJdbcTemplate = new NamedParameterJdbcTemplate(searchDataSource); - } - @Override public List<Integer> searchInAllMessages(final String searchString, final int messageIdBefore) { if (StringUtils.isBlank(searchString)) @@ -63,11 +46,11 @@ public class SphinxSearchServiceImpl implements SearchService { .addValue("before", messageIdBefore) .addValue("limit", maxResult); - return namedParameterSearchJdbcTemplate.queryForList( - "SELECT id AS message_id FROM messages WHERE MATCH(:search) " + + return getNamedParameterJdbcTemplate().queryForList( + "SELECT message_id FROM messages_txt WHERE MATCH(txt) AGAINST(:search) " + (messageIdBefore > 0 ? - " AND id < :before " : StringUtils.EMPTY) + - " ORDER BY id DESC LIMIT :limit", + " AND message_id < :before " : StringUtils.EMPTY) + + " ORDER BY message_id DESC LIMIT :limit", sqlParameterSource, Integer.class); } @@ -83,11 +66,11 @@ public class SphinxSearchServiceImpl implements SearchService { .addValue("before", messageIdBefore) .addValue("limit", maxResult); - return namedParameterSearchJdbcTemplate.queryForList( - "SELECT id AS message_id FROM messages WHERE user_id = :userId AND MATCH(:search) " + + return getNamedParameterJdbcTemplate().queryForList( + "SELECT messages.message_id AS message_id FROM messages INNER JOIN messages_txt ON messages_txt.message_id=messages.message_id WHERE messages.user_id = :userId AND MATCH(messages_txt.txt) AGAINST (:search) " + (messageIdBefore > 0 ? - " AND id < :before " : StringUtils.EMPTY) + - " ORDER BY id DESC LIMIT :limit", + " AND messages.message_id < :before " : StringUtils.EMPTY) + + " ORDER BY messages.message_id DESC LIMIT :limit", sqlParameterSource, Integer.class); } diff --git a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java index 4398259c..31731466 100644 --- a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -22,7 +22,6 @@ import com.juick.Tag; import com.juick.User; import com.juick.server.helpers.PrivacyOpts; import com.juick.server.helpers.ResponseReply; -import com.juick.service.search.SearchService; import com.juick.util.MessageUtils; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; |