/*
* Juick
* Copyright (C) 2008-2011, Ugnich Anton
*
* 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 .
*/
package com.juick.server;
import com.juick.Tag;
import org.apache.commons.lang3.StringEscapeUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
*
* @author Ugnich Anton
*/
public class TagQueries {
public static com.juick.Tag getTag(JdbcTemplate sql, int tid) {
try {
return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?",
(rs, num) -> {
Tag ret = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(2)));
ret.TID = tid;
ret.SynonymID = rs.getInt(1);
return ret;
}, tid);
} catch (EmptyResultDataAccessException e) {
return null;
}
}
public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) {
Tag ret = null;
try {
ret = sql.queryForObject("SELECT tag_id,synonym_id,name FROM tags WHERE name=?",
(rs, rowNum) -> {
Tag ret1 = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3)));
ret1.TID = rs.getInt(1);
ret1.SynonymID = rs.getInt(2);
return ret1;
}, StringEscapeUtils.escapeHtml4(tag));
} catch (EmptyResultDataAccessException e) {
// tag not found
}
if (ret == null && autoCreate) {
ret = new com.juick.Tag(tag);
ret.TID = createTag(sql, tag);
}
return ret;
}
public static List getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) {
List ret = new ArrayList<>();
for (String tag : tags) {
if (!tag.isEmpty()) {
Tag t = getTag(sql, tag, autoCreate);
if (t != null) {
ret.add(t);
}
}
}
return ret;
}
public static boolean getTagNoIndex(JdbcTemplate sql, int tag_id) {
try {
return sql.queryForObject("SELECT noindex FROM tags WHERE tag_id=?", Integer.class, tag_id) == 1;
} catch (EmptyResultDataAccessException e) {
return false;
}
}
public static int createTag(JdbcTemplate sql, String name) {
KeyHolder holder = new GeneratedKeyHolder();
sql.update(con -> {
PreparedStatement stmt = con.prepareStatement("INSERT INTO tags(name) VALUES (?)",
Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, StringEscapeUtils.escapeHtml4(name));
return stmt;
}, holder);
return holder.getKey().intValue();
}
public static List getUserTagsAll(JdbcTemplate sql, int uid) {
return sql.query("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",
(rs, rowNum) -> {
Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1)));
t.UsageCnt = rs.getInt(2);
return t;
}, uid);
}
public static List getUserBLTags(JdbcTemplate sql, int uid) {
return sql.queryForList("SELECT tags.name FROM tags INNER JOIN bl_tags " +
"ON (bl_tags.user_id=? AND bl_tags.tag_id=tags.tag_id) ORDER BY tags.name",
String.class, uid);
}
public static List getPopularTags(JdbcTemplate sql) {
return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class).stream()
.map(StringEscapeUtils::unescapeHtml4).collect(Collectors.toList());
}
public static List updateTags(JdbcTemplate sql, int mid, List newTags) {
List currentTags = MessagesQueries.getMessageTags(sql, mid);
newTags.stream().filter(currentTags::contains)
.forEach(t -> sql.update("DELETE FROM messages_tags WHERE message_id=? AND tag_id=?", mid, t.TID));
newTags.stream().filter(t -> !currentTags.contains(t))
.forEach(t -> sql.update("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", mid, t.TID));
return MessagesQueries.getMessageTags(sql, mid);
}
public static List fromString(JdbcTemplate sql, String txt, boolean tagsOnly) {
String patternString = tagsOnly ? "^(?:(?:\\*[^ \\r\\n\\t]+)|\\s)+$" : "^\\*([^ \\r\\n\\t]+)\\s+([\\s\\S]+)";
Pattern tagsPattern = Pattern.compile(patternString);
if (tagsPattern.matcher(txt).matches()) {
Pattern tagPattern = Pattern.compile("\\*([^ \\r\\n\\t]+)");
Matcher tagMatcher = tagPattern.matcher(txt);
List tags = new ArrayList<>();
// TODO: process readonly, private, friends, public
while (tagMatcher.find()) {
for (int i = 1; i <= tagMatcher.groupCount(); i++) {
tags.add(getTag(sql, tagMatcher.group(i), true));
}
}
return tags;
}
return Collections.emptyList();
}
}