欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-29 19:46:15
...

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

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

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

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

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

# ####################################
# @author ??? (original coding)
# @author Dirk Rasmussen - d.rasmussen@bevis.de (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\目录中复制过来,并注意修改相应的文件名。