chx 学习jForum笔记十八 jForum与ms sqlserver

jForum的原生数据库是mysql，但对sqlserver也应是支持的。

我当时安装的时候是从网上下载了一个sqlserver2000的补丁包，完成了安装。在此向提供此包的高手表示致敬。

在附件中即是我当时下载的那个补丁包。

下面要说的是其实sqlserver2000与其他的SQL不同之处在于没有LIMIT ?,?语句，而应该使用TOP ?语句。

因此，最终在我的sqlserver2000.sql文件中的内容为：

# ####################################
# @author ??? (original coding)
# @author Dirk Rasmussen - [e-mail] (modifies for MS SQL Server 2005)
# @author Andowson Chang - http://www.andowson.com (fix for MS SQL Server 2000)
# @version $Id$
# ####################################

# #############
# UserModel
# #############						
UserModel.selectAllByLimit = SELECT TOP %d \
	user_email, user_id, user_posts, user_regdate, username, deleted, user_karma, user_from, user_website, user_viewemail \
	FROM jforum_users \
	ORDER BY user_id ASC

UserModel.selectAllByGroup = SELECT TOP %d user_email, u.user_id, user_posts, user_regdate, username, deleted, user_karma, user_from, user_website, user_viewemail \
    FROM jforum_users u, jforum_user_groups ug WHERE u.user_id = ug.user_id AND ug.group_id = ? \
    ORDER BY u.user_id

UserModel.lastUserRegistered = SELECT top 1 user_id, username FROM jforum_users ORDER BY user_regdate DESC

UserModel.selectById = SELECT u.*, \
    (SELECT COUNT(1) FROM jforum_privmsgs pm \
    WHERE pm.privmsgs_to_userid = u.user_id \
    AND pm.privmsgs_type = 1) AS private_messages \
    FROM jforum_users u \
    WHERE u.user_id = ?
	
# #############
# PostModel
# #############
PostModel.selectLatestByForumForRSS = SELECT TOP %d \
    p.topic_id, p.topic_id, p.post_id, p.forum_id, pt.post_subject AS subject, pt.post_text, p.post_time, p.user_id, u.username \
	FROM jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u \
	WHERE p.post_id = t.topic_last_post_id \
	AND p.topic_id = t.topic_id \
	AND p.user_id = u.user_id \
	AND p.post_id = pt.post_id \
	AND p.need_moderate = 0 \
	AND t.forum_id = ? \
	ORDER BY t.topic_last_post_id DESC

PostModel.selectLatestForRSS = SELECT TOP %d \
    t.topic_id, t.topic_title AS subject, p.post_id, t.forum_id, pt.post_text, p.post_time, p.user_id, u.username \
	FROM jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u \
	WHERE p.post_id = t.topic_last_post_id \
	AND p.topic_id = t.topic_id \
	AND p.user_id = u.user_id \
	AND p.post_id = pt.post_id \
	AND p.need_moderate = 0  \
	ORDER BY topic_last_post_id DESC
	
PostModel.selectHotForRSS = SELECT TOP %d \
    t.topic_id, t.topic_title AS subject, p.post_id, t.forum_id, pt.post_text, p.post_time, p.user_id, u.username \
	FROM jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u \
	WHERE p.post_id = t.topic_last_post_id \
	AND p.topic_id = t.topic_id \
	AND p.user_id = u.user_id \
	AND p.post_id = pt.post_id \
	AND p.need_moderate = 0  \
	ORDER BY topic_views DESC

PostModel.selectAllByTopicByLimit = SELECT TOP %d \
	p.post_id, topic_id, forum_id, p.user_id, post_time, poster_ip, enable_bbcode, p.attach, \
	enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count, status, pt.post_subject, pt.post_text, username, p.need_moderate \
	FROM jforum_posts p, jforum_posts_text pt, jforum_users u \
	WHERE p.post_id = pt.post_id \
	AND topic_id = ? \
	AND p.user_id = u.user_id \
	AND p.need_moderate = 0 
	ORDER BY post_time ASC

PostModel.selectByUserByLimit = SELECT TOP %d \
    p.post_id, topic_id, forum_id, p.user_id, post_time, poster_ip, enable_bbcode, p.attach, \
	enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count, status, pt.post_subject, pt.post_text, username, p.need_moderate \
	FROM jforum_posts p, jforum_posts_text pt, jforum_users u \
	WHERE p.post_id = pt.post_id \
	AND p.user_id = u.user_id \
	AND p.user_id = ? \
	AND p.need_moderate = 0 \
	AND forum_id IN(:fids:) \
	ORDER BY p.post_id DESC

# #############
# TopicModel
# #############
TopicModel.selectAllByForumByLimit =  SELECT TOP %d \
    t.*, p.user_id AS last_user_id, p.post_time, (SELECT SUM(p.attach) \
        FROM jforum_posts p \
        WHERE p.topic_id = t.topic_id \
        AND p.need_moderate = 0) AS attach \
	FROM jforum_topics t, jforum_posts p \
	WHERE (t.forum_id = ? OR t.topic_moved_id = ?) \
	AND p.post_id = t.topic_last_post_id \
	AND p.need_moderate = 0 \
	ORDER BY t.topic_type DESC, t.topic_last_post_id DESC

TopicModel.selectRecentTopicsByLimit = SELECT TOP %d \
	t.*, p.user_id AS last_user_id, p.post_time, (SELECT SUM(p.attach) \
        FROM jforum_posts p \
        WHERE p.topic_id = t.topic_id \
        AND p.need_moderate = 0) AS attach \
	FROM jforum_topics t, jforum_posts p \
	WHERE p.post_id = t.topic_last_post_id \
	AND p.need_moderate = 0  \
	ORDER BY t.topic_last_post_id DESC

TopicModel.selectHottestTopicsByLimit = SELECT TOP %d \
  t.*, p.user_id AS last_user_id, p.post_time, (SELECT SUM(p.attach) \
        FROM jforum_posts p \
        WHERE p.topic_id = t.topic_id \
        AND p.need_moderate = 0) AS attach \
  FROM jforum_topics t, jforum_posts p \
  WHERE p.post_id = t.topic_last_post_id \
  AND p.need_moderate = 0 \
  ORDER BY topic_views DESC

TopicModel.selectByUserByLimit = SELECT TOP %d \
    t.*, p.user_id AS last_user_id, p.post_time, (SELECT SUM(p.attach) \
        FROM jforum_posts p \
        WHERE p.topic_id = t.topic_id \
        AND p.need_moderate = 0) AS attach \
	FROM jforum_topics t, jforum_posts p \
	WHERE p.post_id = t.topic_last_post_id \
	AND t.user_id = ? \
	AND p.need_moderate = 0 \
	AND t.forum_id IN(:fids:) \
	ORDER BY t.topic_last_post_id DESC

# ################
# AttachmentModel
# ################
AttachmentModel.selectTopDownloadsByLimit = SELECT TOP %d f.forum_id, f.forum_name, t.topic_id, t.topic_title, ad.attach_id, ad.real_filename, ad.filesize, ad.download_count \
    FROM jforum_forums f, jforum_posts p, jforum_topics t, jforum_attach a, jforum_attach_desc ad \
    WHERE p.topic_id = t.topic_id AND p.forum_id = f.forum_id and p.post_id = a.post_id \
    AND a.attach_id = ad.attach_id AND a.privmsgs_id = 0 AND ad.download_count > 0 \
    ORDER BY ad.download_count DESC 


# ############
# SearchModel
# ############
SearchModel.firstPostIdByDate = SELECT TOP 1 post_id FROM jforum_posts WHERE post_time > ?
SearchModel.lastPostIdByDate = SELECT TOP 1 post_id FROM jforum_posts WHERE post_time < ? ORDER BY post_id DESC

# ################
# ModerationLog
# ################
ModerationLog.selectAll = SELECT TOP %d l.*, u.username, u2.username AS poster_username FROM jforum_moderation_log l LEFT JOIN jforum_users u2 ON u2.user_id = l.post_user_id LEFT JOIN jforum_users u ON l.user_id = u.user_id ORDER BY log_id DESC

 

我自己添加了最后的几句。

其中ModerationLog那句影响的是“管理日志 ”功能。光在这个里面添加SQL语句还不行，程序会自动调用generModerationLogDAO();中的相应语句导致SQL报错。为此需要在SqlServer2000DataAccessDriver.java中增加以下语句：

private static ModerationLogDAO moderationLogDao = new SqlServer2000ModerationLogDAO();

public net.jforum.dao.ModerationLogDAO newModerationLogDAO()
{
   return moderationLogDao;
}

 

而对于search那两句，则不光要进行上述类似的操作，还需要在/dao/sqlserver中新增一个名为SqlServer2000LuceneDAO.java的文件。文件内容为：

package net.jforum.dao.sqlserver;

import net.jforum.JForumExecutionContext;
import net.jforum.dao.generic.GenericLuceneDAO;
import net.jforum.exceptions.DatabaseException;
import net.jforum.util.DbUtils;
import net.jforum.util.preferences.SystemGlobals;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

/**
 * @author Rafael Steil
 * @version $Id: GenericLuceneDAO.java,v 1.12 2007/10/13 13:46:21 rafaelsteil Exp $
 */
public class SqlServer2000LuceneDAO extends GenericLuceneDAO {

    /**
     * @see net.jforum.dao.LuceneDAO#firstPostIdByDate(java.util.Date)
     */
    public int firstPostIdByDate(Date date) {
        return this.getPostIdByDate(date, SystemGlobals.getSql("SearchModel.firstPostIdByDate"));
    }

    /**
     * @see net.jforum.dao.LuceneDAO#lastPostIdByDate(java.util.Date)
     */
    public int lastPostIdByDate(Date date) {
        return this.getPostIdByDate(date, SystemGlobals.getSql("SearchModel.lastPostIdByDate"));
    }

    private int getPostIdByDate(Date date, String query) {
        int postId = 0;

        PreparedStatement p = null;
        ResultSet rs = null;

        try {
            p = JForumExecutionContext.getConnection().prepareStatement(query);

            p.setTimestamp(1, new Timestamp(date.getTime()));

            rs = p.executeQuery();

            if (rs.next()) {
                postId = rs.getInt(1);
            }
        }
        catch (SQLException e) {
            throw new DatabaseException(e);
        }
        finally {
            DbUtils.close(rs, p);
        }

        return postId;
    }

}

 

至于attachment那句，与search类似，需要新增SqlServer2000AttachmentDAO.java.内容为：

package net.jforum.dao.sqlserver;

import net.jforum.JForumExecutionContext;
import net.jforum.dao.generic.GenericAttachmentDAO;
import net.jforum.entities.TopDownloadInfo;
import net.jforum.exceptions.DatabaseException;
import net.jforum.util.DbUtils;
import net.jforum.util.preferences.SystemGlobals;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Rafael Steil
 * @version $Id: GenericAttachmentDAO.java,v 1.11 2006/08/23 02:13:41 rafaelsteil Exp $
 */
public class SqlServer2000AttachmentDAO extends GenericAttachmentDAO {

    public List<TopDownloadInfo> selectTopDownloads(final int limit) {
        final List<TopDownloadInfo> list = new ArrayList<TopDownloadInfo>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String sql = SystemGlobals.getSql("AttachmentModel.selectTopDownloadsByLimit");
        sql = String.format(sql, limit);
        try {
            pstmt = JForumExecutionContext.getConnection().prepareStatement(sql);

            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                TopDownloadInfo tdi = new TopDownloadInfo();
                tdi.setForumId(resultSet.getInt("forum_id"));
                tdi.setForumName(resultSet.getString("forum_name"));
                tdi.setTopicId(resultSet.getInt("topic_id"));
                tdi.setTopicTitle(resultSet.getString("topic_title"));
                tdi.setAttachId(resultSet.getInt("attach_id"));
                tdi.setRealFilename(resultSet.getString("real_filename"));
                tdi.setFilesize(resultSet.getLong("filesize"));
                tdi.setDownloadCount(resultSet.getInt("download_count"));
                list.add(tdi);
            }
        } catch (SQLException e) {
            throw new DatabaseException(e);
        } finally {
            DbUtils.close(resultSet, pstmt);
        }
        return list;
    }
}

 这两个文件，都是从相应的generic中复制过来进行一定的修改即可。主要就是下面这句：

sql = String.format(sql, limit);

 

另外，非常重要的是要注意web服务器上jforum\WEB-INF\config\jforum-custom.conf这个文件。

这个文件是在INSTALL之后自动生成的。除了有数据库相应的地址、端口、用户名、密码等内容之外下面这三句也许都起着一定的作用。

 

dao.driver=net.jforum.dao.sqlserver.SqlServer2000DataAccessDriver
database.connection.driver=net.sourceforge.jtds.jdbc.Driver
database.driver.name=sqlserver2000

  特别是database.driver.name=sqlserver2000这句，会导致jforum从服务器的jofrum\WEB-INF\config\database\sqlserver2000\中去取相应的内容。我的服务器上这个目录包含以下文件：

sqlserver2000.properties

sqlserver2000.sql

sqlserver2000_data_dump.sql

sqlserver2000_db_struct.sql

如果没有，请从jofrum\WEB-INF\config\database\sqlserver\目录中复制过来，并注意修改相应的文件名。