sqladapter.java

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

        "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 where state = 'R' and (not exists " +
        "(select * from ejf_trash_box where ejf_trash_box.topicID=ejf_topic.topicID))";

    public String TrashBox_RemoveReply = 
        "delete from ejf_reply where state = 'R' and (not exists " +
        "(select * from ejf_trash_box where ejf_trash_box.topicID=ejf_reply.topicID " +
        "and ejf_trash_box.replyID=ejf_reply.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.fromUser=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.userID=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_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 >= ? and state <> 'R' " +
        "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," +
        "runMode,isOnlyFile,remark,runStamp,createTime) values(?,?,?,?,?,?,?,?,NOW())";
    
    public String BackupTask_Update = 
        "update ejf_backup_task set inputFile=?,outputFile=?,runAt=?,sendmail=?," +
        "runMode=?,isOnlyFile=?,remark=?,runStamp=? where taskID=?";

    public String BackupTask_ModStamp = 
        "update ejf_backup_task set runStamp=? 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=?";
}