chx学習jForumノート18 jForumとms sqlserver

12054 ワード
jForum
jForumのオリジナルデータベースはmysqlですが、sqlserverもサポートされているはずです.
私がインストールした時、ネット上からsqlserver 2000のパッチパッケージをダウンロードし、インストールを完了しました.このバッグを提供してくれた達人に敬意を表します.
添付ファイルには、私がダウンロードしたパッチがあります.
実はsqlserver 2000が他のSQLと違うのはLIMITがないこと?文、TOPを使うべきですか?文.
だから、最終的には私のsqlserver 2000です.sqlファイルの内容は次のとおりです.
# ####################################
# @author ??? (original coding)
# @author Dirk Rasmussen - [email protected] (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エラーが発生しました.そのためにはSqlServer 2000 DataAccessDriver.JAvaに次の文を追加します.
private static ModerationLogDAO moderationLogDao = new SqlServer2000ModerationLogDAO();

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

 
また、searchの2つについては、上記のような操作を行うだけでなく、/dao/sqlserverにSqlServer 2000 LuceneDAOという名前を追加する必要がある.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と同様に、SqlServer 2000 AttachmentDAOを追加する必要があります.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 selectTopDownloads(final int limit) {
        final List list = new ArrayList();
        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;
    }
}

この2つのファイルは、対応するgenericからコピーして一定の修正を行えばよい.主に次の文です.
sql = String.format(sql, limit);

 
また、webサーバ上のjforumWEB-INFconfigjforum-customに注意することが重要です.confというファイル.
このファイルはINSTALL以降に自動的に生成されます.データベースに対応するアドレス、ポート、ユーザー名、パスワードなどの内容があるほか、次の3つの文が一定の役割を果たしているかもしれません.
  
dao.driver=net.jforum.dao.sqlserver.SqlServer2000DataAccessDriver
database.connection.driver=net.sourceforge.jtds.jdbc.Driver
database.driver.name=sqlserver2000

特にdatabase.driver.name=sqlserver 2000という文は、jforumがサーバのjofrumWEB-INFconfigdatabasesqlserver 2000から対応するコンテンツを取得することになります.私のサーバ上のこのディレクトリには、次のファイルが含まれています.
sqlserver2000.properties
sqlserver2000.sql
sqlserver2000_data_dump.sql
sqlserver2000_db_struct.sql
ない場合は、jofrumWEB-INFconfigdatabasesqlserverディレクトリからコピーし、対応するファイル名の変更に注意してください.
ブルーブリッジカップ--切手を切る
SQL--DDL言語の識別列