sqladapter.java

EasyJForum 是一个基于 Java 技术的免费社区论坛软件系统

        "update ejf_topic set replies=replies+1,lastPostUser=?,lastNickname=?," +
        "lastPostTime=NOW() where topicID = ?";

    public String Topic_IncReplies = 
        "update ejf_topic set replies=replies+1 where topicID = ?";
    
    public String Topic_DecReplies = 
        "update ejf_topic set replies=replies-1 where topicID = ?";
    
    public String Topic_IncVisits = 
        "update ejf_topic set visits=visits+1 where topicID = ?";
    
    public String Topic_StatReplies = 
        "select COUNT(replyID) as replies,MAX(replyID) as lastReplyID " +
        "from ejf_reply where topicID = ? and state = 'N'";
    
    public String Topic_GetLastPost = 
        "select a.userID,b.nickname,a.createTime from ejf_reply a LEFT JOIN ejf_user b " +
        "on a.userID = b.userID where a.replyID = ?";
    
    public String Topic_ModReplies = 
        "update ejf_topic set replies=?,lastPostUser=?,lastNickname=?,lastPostTime=? " +
        "where topicID = ?";

    public String Topic_ResetReplies = 
        "update ejf_topic set replies=0,lastPostUser=userID,lastNickname=nickname,lastPostTime=createTime " +
        "where topicID = ?";
    
    public String Topic_SetIsSolved = 
        "update ejf_topic set isSolved=? where topicID = ?";
    
    public String Topic_RemoveByUser = 
        "delete from ejf_topic where userID = ?";

    // Reply
    public String Reply_Insert = 
        "insert into ejf_reply(topicID,userID,remoteIP,title,content,isHidePost," +
        "attaches,createTime) values(?,?,?,?,?,?,?,NOW())";

    public String Reply_Update = 
        "update ejf_reply set title=?,content=?,isHidePost=?,attaches=?," +
        "updateTime=NOW() where replyID = ?";
    
    public String Reply_GetCount = 
        "select COUNT(*) from ejf_reply where topicID = ? and state <> 'R'";

    public String Reply_GetList = 
        "select a.replyID,a.title,a.content,a.isHidePost,a.attaches,a.isBest," +
        "a.state,a.createTime,a.userID,b.nickname,b.avatar,b.posts,b.credits,b.groupID " + 
        "from ejf_reply a LEFT JOIN ejf_user b on a.userID = b.userID " +
        "where a.topicID = ? and a.state <> 'R' order by a.isBest DESC, a.replyID ASC";

    public String Reply_GetSeqno = 
        "select COUNT(*) from ejf_reply where topicID = ? and state <> 'R' " +
        "and (replyID < ? or isBest='T')";
    
    public String Reply_Index = 
        "select a.topicID,a.boardID,b.replyID,b.userID,b.title,b.content " + 
        "from ejf_topic a, ejf_reply b " +
        "where a.topicID = b.topicID and a.state <> 'R' and b.state <> 'R'";
    
    public String Reply_GetListByUser = 
        "select a.sectionID,a.boardID,a.topicID,a.title,b.replyID,b.userID," + 
        "b.state,b.createTime from ejf_topic a, ejf_reply b " +
        "where a.topicID = b.topicID and a.state <> 'R' and b.userID = ?";

    public String Reply_GetCountByUser = 
        "select COUNT(*) from ejf_topic a, ejf_reply b " + 
        "where a.topicID = b.topicID and a.state <> 'R' and b.userID = ?";
    
    public String Reply_Select = 
        "select * from ejf_reply where replyID = ?";

    public String Reply_GetLogInfo = 
        "select a.topicID,a.boardID,a.title,a.reward,b.attaches,b.userID,b.isBest " + 
        "from ejf_topic a, ejf_reply b where a.topicID = b.topicID and b.replyID = ?";

    public String Reply_Delete = 
        "update ejf_reply set state='R' where replyID = ?";

    public String Reply_Remove = 
        "delete from ejf_reply where replyID = ?";
    
    public String Reply_SetIsBest = 
        "update ejf_reply set isBest=? where replyID = ?";

    public String Reply_ModState = 
        "update ejf_reply set state=? where replyID = ?";
    
    // Attachment
    public String Attach_Insert = 
        "insert into ejf_attach(topicID,replyID,userID,localname,localID,filename," +
        "filesize,credits,title,state,createTime) values(?,?,?,?,?,?,?,?,?,?,NOW())";

    public String Attach_Update = 
        "update ejf_attach set credits=?,title=?,state=?,updateTime=NOW() " +
        "where attachID = ?";
    
    public String Attach_RemoveByPost = 
        "update ejf_attach set state = 'R' where topicID = ? and replyID = ?";

    public String Attach_RemoveByUser = 
        "delete from ejf_attach where userID = ?";
    
    public String Attach_IncDownloads = 
        "update ejf_attach set downloads = downloads + 1 where attachID = ?";

    public String Attach_GetList = 
        "select * from ejf_attach where topicID = ? and state = 'N'";

    public String Attach_GetRecycledList = 
        "select attachID,localname from ejf_attach where state = 'R' and createTime < ?";

    public String Attach_GetAvatarList = 
        "select avatar from ejf_user";

    public String Attach_Get2MonthList = 
        "select localname from ejf_attach where createTime >= ?";
    
    public String Attach_delete = 
        "delete from ejf_attach where state = 'R' and attachID=?";
    
    public String Attach_GetPostList = 
        "select * from ejf_attach where topicID = ? and replyID = ? and state <> 'R'";
    
    public String Attach_Select = 
        "select * from ejf_attach where attachID = ?";

    public String Attach_Query = 
        "select a.*, b.title as topicTitle, b.boardID from ejf_attach a, ejf_topic b";

    // Trash box
    public String TrashBox_Insert = 
        "insert into ejf_trash_box(topicID,replyID,boardID,boardName,topicTitle," +
        "userID,deleteUser,createTime) values(?,?,?,?,?,?,?,NOW())";

    public String TrashBox_Delete = 
        "delete from ejf_trash_box where topicID=? and replyID=?";

    public String TrashBox_CleanExpired = 
        "delete from ejf_trash_box where createTime < ?";
    
    public String TrashBox_RemoveTopic = 
        "delete from ejf_topic a where a.state = 'R' and (not exists " +
        "(select * from ejf_trash_box b where a.topicID=b.topicID))";

    public String TrashBox_RemoveReply = 
        "delete from ejf_reply a where a.state = 'R' and (not exists " +
        "(select * from ejf_trash_box b where a.topicID=b.topicID " +
        "and a.replyID=b.replyID))";
    
    // Short message
    public String ShortMsg_Insert = 
        "insert into ejf_short_msg(title,message,userID,fromUser,createTime)" + 
        " values(?,?,?,?,NOW())";
    
    public String ShortMsg_SetReadState = 
        "update ejf_short_msg set state='R',updateTime=NOW() where msgID=?";
    
    public String ShortMsg_RemoveFromInbox = 
        "update ejf_short_msg set state='D' where msgID=?";

    public String ShortMsg_RemoveFromOutbox = 
        "update ejf_short_msg set outflag='D' where msgID=?";

    public String ShortMsg_Delete = 
        "delete from ejf_short_msg where state='D' and outflag='D'";
    
    public String ShortMsg_GetMessage = 
        "select message,state from ejf_short_msg where msgID=?";

    public String ShortMsg_Select = 
        "select * from ejf_short_msg where msgID=?";
    
    public String ShortMsg_GetList = 
        "select a.msgID,a.title,a.fromUser,b.nickname,a.state,a.createTime " +
        "from ejf_short_msg a LEFT JOIN ejf_user b on a.userID=b.userID " +
        "where a.userID=? and a.state<>'D' order by a.createTime DESC"; 

    public String ShortMsg_GetCount = 
        "select COUNT(*) from ejf_short_msg where userID=? and state<>'D'"; 

    public String ShortMsg_GetOutList = 
        "select a.msgID,a.title,a.userID,b.nickname,a.state,a.createTime " +
        "from ejf_short_msg a LEFT JOIN ejf_user b on a.fromUser=b.userID " +
        "where a.fromUser=? and a.outflag<>'D' order by a.createTime DESC"; 

    public String ShortMsg_GetOutCount = 
        "select COUNT(*) from ejf_short_msg where fromUser=? and outflag<>'D'"; 
    
    public String ShortMsg_GetUnreadCount = 
        "select COUNT(*) from ejf_short_msg where state='N' and userID=?";
    
    public String ShortMsg_StatUserCount = 
        "select COUNT(*) ct,userID from ejf_short_msg where state<>'D' group by userID";    

    public String ShortMsg_getOverflow = 
        "select createTime from ejf_short_msg " +
        "where userID = ? order by createTime DESC";

    public String ShortMsg_cleanOverflow = 
        "delete from ejf_short_msg where userID = ? and createTime < ?";
    
    // Friend
    public String Friend_Insert = 
        "insert into ejf_friend(userID,friendID,remark,createTime) values(?,?,?,NOW())";
    
    public String Friend_Delete = 
        "delete from ejf_friend where userID=? and friendID=?";

    public String Friend_GetList = 
        "select a.*,b.nickname from ejf_friend a LEFT JOIN ejf_user b " +
        "on a.friendID=b.userID where a.userID=? order by a.createTime DESC";

    public String Friend_GetCount = 
        "select COUNT(*) from ejf_friend where userID=?";
    
    public String Friend_IsExistedFriend = 
        "select COUNT(*) from ejf_friend where userID=? and friendID=?";
    
    //Bookmark
    public String Bookmark_Insert = 
        "insert into ejf_bookmark(userID,url,title,boardName,createTime)"
        + " values(?,?,?,?,NOW())";
    
    public String Bookmark_Delete = 
        "delete from ejf_bookmark where markID=?";

    public String Bookmark_GetList = 
        "select * from ejf_bookmark where userID=? order by createTime DESC";

    public String Bookmark_GetCount = 
        "select COUNT(*) from ejf_bookmark where userID=?";
    
    public String Bookmark_StatUserCount = 
        "select COUNT(*) ct,userID from ejf_bookmark group by userID";    

    public String Bookmark_getOverflow = 
        "select createTime from ejf_bookmark " +
        "where userID = ? order by createTime DESC";

    public String Bookmark_cleanOverflow = 
        "delete from ejf_bookmark where userID = ? and createTime < ?";
    
    // Action Logs
    public String AdminLog_Insert = 
        "insert into ejf_admin_log(userID,groupName,remoteIP,action,remark,createTime) " +
        "values(?,?,?,?,?,NOW())";
    
    public String AdminLog_Delete = 
        "delete from ejf_admin_log where createTime < ?";

    public String ModerateLog_Insert = 
        "insert into ejf_moderator_log(userID,groupName,remoteIP,boardID,boardName," +
        "topicID,topicTitle,replyID,action,reason,createTime) " +
        "values(?,?,?,?,?,?,?,?,?,?,NOW())";
    
    public String ModerateLog_Delete = 
        "delete from ejf_moderator_log where createTime < ?";
    
    public String ErrorLog_Insert = 
        "insert into ejf_error_log(userID,remoteIP,action,errorInfo,createTime) " +
        "values(?,?,?,?,NOW())";
    
    public String ErrorLog_Delete = 
        "delete from ejf_error_log where createTime < ?";
    
    public String ReportLog_Insert = 
        "insert into ejf_report_log(userID,reportedUser,boardID,boardName," +
        "topicID,topicTitle,replyID,reason,createTime) values(?,?,?,?,?,?,?,?,NOW())";

    public String ReportLog_Delete = 
        "delete from ejf_report_log where createTime < ?";

    public String CensorLog_Insert = 
        "insert into ejf_censor_log(userID,boardID,boardName," +
        "topicID,topicTitle,replyID,reason,createTime) values(?,?,?,?,?,?,?,NOW())";

    public String CensorLog_Delete = 
        "delete from ejf_censor_log where createTime < ?";
    
    public String CreditsLog_Insert = 
        "insert into ejf_credits_log(userID,fromUser,credits,action,createTime) " +
        "values(?,?,?,?,NOW())";

    public String CreditsLog_Delete = 
        "delete from ejf_credits_log where createTime < ?";
    
    // Visits stat
    public String VisitStat_GetTopicVisits = 
        "select SUM(visits) from ejf_topic";

    public String VisitStat_GetTopics = 
        "select COUNT(*) from ejf_topic";

    public String VisitStat_GetDigestTopics = 
        "select COUNT(*) from ejf_topic where isDigest='T'";

    public String VisitStat_GetRewardTopics = 
        "select COUNT(*) from ejf_topic where reward > 0";

    public String VisitStat_GetReplies = 
        "select SUM(replies) from ejf_topic";

    public String VisitStat_GetUsers = 
        "select COUNT(*) from ejf_user";

    public String VisitStat_GetPostUsers = 
        "select COUNT(*) from ejf_user where posts > 0";

    public String VisitStat_GetAdminUsers = 
        "select COUNT(*) from ejf_user where groupID = 'A'";

    public String VisitStat_GetUserLogins = 
        "select SUM(visitCount) from ejf_user where groupID <> 'A'";
    
    public String VisitStat_GetAttaches = 
        "select COUNT(*) from ejf_attach";
    
    public String VisitStat_Insert = 
        "insert into ejf_visit_stat(statDate,topics,replies,users,visits) " +
        "values(?,?,?,?,?)";

    public String VisitStat_Delete = 
        "delete from ejf_visit_stat where statDate=?";
    
    public String VisitStat_GetList = 
        "select ym, MAX(topics) tc, MAX(replies) rc, MAX(users) uc, MAX(visits) vc from " +
        "(select LEFT(statDate,7) ym,topics,replies,users,visits from ejf_visit_stat) a " +
        "group by ym order by ym ASC";

    public String VisitStat_GetMonths = 
        "select ym from (select LEFT(statDate,7) ym from ejf_visit_stat) a group by ym";

    public String VisitStat_30DaysTopBoards = 
        "select boardID, COUNT(*) ct from ejf_topic where createTime >= ? " +
        "group by boardID order by ct DESC";

    public String VisitStat_TopVisitsTopics = 
        "select sectionID,boardID,topicID,title,visits " +
        "from ejf_topic where createTime >= ? order by visits DESC";

    public String VisitStat_TopRepliesTopics = 
        "select sectionID,boardID,topicID,title,replies " +
        "from ejf_topic where createTime >= ? order by replies DESC";

    public String VisitStat_TopCreditsUsers = 
        "select userID,nickname,credits from ejf_user order by credits DESC";

    public String VisitStat_TopPostsUsers = 
        "select userID,nickname,posts from ejf_user order by posts DESC";

    public String VisitStat_AdminUsers = 
        "select userID,nickname,credits,posts,groupID,lastVisited,visitCount," +
        "createTime from ejf_user where groupID='A' or groupID='S' or userID in";
    
    // Backup Task
    public String BackupTask_Insert = 
        "insert into ejf_backup_task(inputFile,outputFile,runAt,sendmail,remark,createTime) " +
        "values(?,?,?,?,?,NOW())";
    
    public String BackupTask_Update = 
        "update ejf_backup_task set inputFile=?,outputFile=?,runAt=?,sendmail=?,remark=? " +
        "where taskID=?";

    public String BackupTask_Delete = 
        "delete from ejf_backup_task where taskID=?";

    public String BackupTask_Select = 
        "select * from ejf_backup_task where taskID=?";
    
    public String BackupTask_GetAllList = 
        "select * from ejf_backup_task";

    public String BackupTask_GetListByRunAt = 
        "select * from ejf_backup_task where runAt=?";
}