src/cx/fbn/nevernote/sql/HistoryTable.java

   1 /*
   2  * This file is part of NeighborNote
   3  * Copyright 2013 Yuki Takahashi
   4  *
   5  * This file may be licensed under the terms of of the
   6  * GNU General Public License Version 2 (the ``GPL'').
   7  *
   8  * Software distributed under the License is distributed
   9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
  10  * express or implied. See the GPL for the specific language
  11  * governing rights and limitations.
  12  *
  13  * You should have received a copy of the GPL along with this
  14  * program. If not, go to http://www.gnu.org/licenses/gpl.html
  15  * or write to the Free Software Foundation, Inc.,
  16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
  17  *
  18 */
  19
  20 package cx.fbn.nevernote.sql;
  21
  22 import java.util.ArrayList;
  23 import java.util.HashMap;
  24 import java.util.List;
  25
  26 import cx.fbn.nevernote.Global;
  27 import cx.fbn.nevernote.sql.driver.NSqlQuery;
  28 import cx.fbn.nevernote.utilities.ApplicationLogger;
  29
  30 public class HistoryTable {
  31         private final ApplicationLogger logger;
  32         private final DatabaseConnection db;
  33
  34         // コンストラクタ
  35         public HistoryTable(ApplicationLogger l, DatabaseConnection d) {
  36                 logger = l;
  37                 db = d;
  38         }
  39
  40         // テーブル作成
  41         public void createTable() {
  42                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
  43                 logger.log(logger.HIGH, "Historyテーブルを作成しています...");
  44                 if (!query
  45                                 .exec("Create table History (id integer primary key auto_increment, behaviorType varchar,"
  46                                                 + "guid1 varchar, guid2 varchar)"))
  47                         logger.log(logger.HIGH, "Historyテーブル作成失敗!!!");
  48         }
  49
  50         // テーブルをドロップ
  51         public void dropTable() {
  52                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
  53                 query.exec("Drop table History");
  54         }
  55
  56         // Historyテーブルにアイテムを1つ追加
  57         public void addHistory(String behaviorType, String guid1, String guid2) {
  58                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
  59                 boolean excludedCheck = false;
  60
  61                 // 操作ログの取得を停止中
  62                 if (Global.isHaltLogButton()) {
  63                         return;
  64                 }
  65
  66                 if (behaviorType == null) {
  67                         return;
  68                 }
  69                 if (guid1 == null || guid1.equals("")) {
  70                         return;
  71                 }
  72                 if (guid2 == null || guid2.equals("")) {
  73                         return;
  74                 }
  75                 if (guid1.equals(guid2)) {
  76                         return;
  77                 }
  78
  79                 // 除外ノートに指定されていないかチェックする
  80                 excludedCheck = db.getExcludedTable().existNote(guid1, guid2);
  81
  82                 if (!excludedCheck) {
  83                         query.prepare("Insert Into History (behaviorType, guid1, guid2) Values(:behaviorType, :guid1, :guid2)");
  84                         query.bindValue(":behaviorType", behaviorType);
  85                         query.bindValue(":guid1", guid1);
  86                         query.bindValue(":guid2", guid2);
  87                         if (!query.exec()) {
  88                                 logger.log(logger.MEDIUM, "Historyテーブルへのアイテム追加に失敗");
  89                                 logger.log(logger.MEDIUM, query.lastError());
  90                         }
  91                 }
  92         }
  93
  94         // masterGuidとchildGuidをマージ
  95         public void mergeHistoryGuid(String masterGuid, String childGuid) {
  96                 NSqlQuery histQuery = new NSqlQuery(db.getBehaviorConnection());
  97                 boolean check = false;
  98
  99                 // マージ後に重複してしまうデータを先に削除
 100                 histQuery.prepare("Delete from history where (guid1=:oldGuid1 and guid2=:newGuid1) or (guid1=:newGuid2 and guid2=:oldGuid2)");
 101                 histQuery.bindValue(":oldGuid1", masterGuid);
 102                 histQuery.bindValue(":newGuid1", childGuid);
 103                 histQuery.bindValue(":oldGuid2", masterGuid);
 104                 histQuery.bindValue(":newGuid2", childGuid);
 105                 check = histQuery.exec();
 106                 if(!check){
 107                         logger.log(logger.MEDIUM, "historyテーブルの重複削除で失敗");
 108                         logger.log(logger.MEDIUM, histQuery.lastError());
 109                 }
 110
 111                 updateHistoryGuid(masterGuid, childGuid);
 112         }
 113
 114         // HistoryテーブルのGuidを更新
 115         public void updateHistoryGuid(String newGuid, String oldGuid){
 116                 NSqlQuery histQuery = new NSqlQuery(db.getBehaviorConnection());
 117                 boolean check = false;
 118
 119                 histQuery.prepare("Update history set guid1=:newGuid where guid1=:oldGuid");
 120                 histQuery.bindValue(":newGuid", newGuid);
 121                 histQuery.bindValue(":oldGuid", oldGuid);
 122                 check = histQuery.exec();
 123                 if (!check) {
 124                         logger.log(logger.MEDIUM, "historyテーブルのguid1のところでguid更新失敗");
 125                         logger.log(logger.MEDIUM, histQuery.lastError());
 126                 }
 127                 histQuery.prepare("Update history set guid2=:newGuid where guid2=:oldGuid");
 128                 histQuery.bindValue(":newGuid", newGuid);
 129                 histQuery.bindValue(":oldGuid", oldGuid);
 130                 check = histQuery.exec();
 131                 if (!check) {
 132                         logger.log(logger.MEDIUM, "historyテーブルのguid2のところでguid更新失敗");
 133                         logger.log(logger.MEDIUM, histQuery.lastError());
 134                 }
 135         }
 136
 137         // Historyテーブルから引数ノートと関連のあるノートのguidと回数をゲット
 138         public HashMap<String, Integer> getBehaviorHistory(String behaviorType, String guid) {
 139                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
 140                 HashMap<String, Integer> behaviorHist = new HashMap<String, Integer>();
 141
 142                 // guid1=guidの履歴一覧を取得
 143                 query.prepare("Select guid2 from History where behaviorType=:behaviorType and guid1=:guid1");
 144                 query.bindValue(":behaviorType", behaviorType);
 145                 query.bindValue(":guid1", guid);
 146                 if (!query.exec()) {
 147                         logger.log(logger.MEDIUM,
 148                                         "HistoryテーブルからbehaviorType=" + behaviorType + "かつguid1=" + guid + "のアイテム取得失敗");
 149                         logger.log(logger.MEDIUM, query.lastError());
 150                 }
 151                 // HashMapに記録
 152                 while (query.next()) {
 153                         // すでにHashMapに登録されていたら、回数を+1
 154                         String key = query.valueString(0);
 155                         if (behaviorHist.containsKey(key)) {
 156                                 behaviorHist.put(key, behaviorHist.get(key) + 1);
 157                         } else { // そうでないなら新規登録
 158                                 behaviorHist.put(key, 1);
 159                         }
 160                 }
 161
 162                 // guid2=guidの履歴一覧を取得
 163                 query.prepare("Select guid1 from History where behaviorType=:behaviorType and guid2=:guid2");
 164                 query.bindValue(":behaviorType", behaviorType);
 165                 query.bindValue(":guid2", guid);
 166                 if (!query.exec()) {
 167                         logger.log(logger.MEDIUM,
 168                                         "HistoryテーブルからbehaviorType=" + behaviorType + "かつguid2=" + guid + "のアイテム取得失敗");
 169                         logger.log(logger.MEDIUM, query.lastError());
 170                 }
 171                 // HashMapに記録
 172                 while (query.next()) {
 173                         // すでにHashMapに登録されていたら、回数を+1
 174                         String key = query.valueString(0);
 175                         if (behaviorHist.containsKey(key)) {
 176                                 behaviorHist.put(key, behaviorHist.get(key) + 1);
 177                         } else { // そうでないなら新規登録
 178                                 behaviorHist.put(key, 1);
 179                         }
 180                 }
 181                 return behaviorHist;
 182         }
 183
 184         // oldGuidのノートの操作履歴をnewGuidのノートの操作履歴として複製
 185         public void duplicateHistory(String newGuid, String oldGuid) {
 186                 NSqlQuery histQuery = new NSqlQuery(db.getBehaviorConnection());
 187
 188                 // guid1 = oldGuidの履歴一覧を取得
 189                 histQuery.prepare("Select behaviorType, guid2 from History where guid1=:oldGuid");
 190                 histQuery.bindValue(":oldGuid", oldGuid);
 191                 if(!histQuery.exec()){
 192                         logger.log(logger.MEDIUM, "Historyテーブルからguid1=" + oldGuid + "のアイテム取得失敗");
 193                         logger.log(logger.MEDIUM, histQuery.lastError());
 194                 }
 195                 // guid1 = newGuidの履歴として複製
 196                 while(histQuery.next()){
 197                         String behaviorType = histQuery.valueString(0);
 198                         String guid2 = histQuery.valueString(1);
 199
 200                         addHistory(behaviorType, newGuid, guid2);
 201                 }
 202
 203                 // guid2 = oldGuidの履歴一覧を取得
 204                 histQuery.prepare("Select behaviorType, guid1 from History where guid2=:oldGuid");
 205                 histQuery.bindValue(":oldGuid", oldGuid);
 206                 if(!histQuery.exec()){
 207                         logger.log(logger.MEDIUM, "Historyテーブルからguid2=" + oldGuid + "のアイテム取得失敗");
 208                         logger.log(logger.MEDIUM,  histQuery.lastError());
 209                 }
 210                 // guid2 = newGuidの履歴として複製
 211                 while(histQuery.next()){
 212                         String behaviorType = histQuery.valueString(0);
 213                         String guid1 = histQuery.valueString(1);
 214
 215                         addHistory(behaviorType, guid1, newGuid);
 216                 }
 217         }
 218
 219         // guidを含む列をHistoryテーブルから削除
 220         public void expungeHistory(String guid) {
 221                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
 222                 boolean check;
 223
 224                 query.prepare("Delete from History where guid1=:guid1 or guid2=:guid2");
 225                 query.bindValue(":guid1", guid);
 226                 query.bindValue(":guid2", guid);
 227
 228                 check = query.exec();
 229                 if(!check){
 230                         logger.log(logger.MEDIUM, "historyテーブルからguid=" + guid + "のデータ削除に失敗");
 231                         logger.log(logger.MEDIUM, query.lastError());
 232                 }
 233         }
 234
 235         // guid1とguid2を指定してHistoryテーブルから削除
 236         public void expungeHistory(String guid1, String guid2) {
 237                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
 238                 boolean check;
 239
 240                 query.prepare("Delete from History where guid1=:guid1 and guid2=:guid2");
 241                 query.bindValue(":guid1", guid1);
 242                 query.bindValue(":guid2", guid2);
 243
 244                 check = query.exec();
 245                 if(!check){
 246                         logger.log(logger.MEDIUM, "historyテーブルからguid1=" + guid1 + "かつguid2=" + guid2 + "のデータ削除に失敗");
 247                         logger.log(logger.MEDIUM, query.lastError());
 248                 }
 249
 250                 // guid1とguid2が逆のパターンも削除
 251                 query.prepare("Delete from History where guid1=:guid1 and guid2=:guid2");
 252                 query.bindValue(":guid1", guid2);
 253                 query.bindValue(":guid2", guid1);
 254
 255                 check = query.exec();
 256                 if(!check){
 257                         logger.log(logger.MEDIUM, "historyテーブルからguid1=" + guid2 + "かつguid2=" + guid1 + "のデータ削除に失敗");
 258                         logger.log(logger.MEDIUM, query.lastError());
 259                 }
 260         }
 261
 262         // 同じタグが付けられたノート間の履歴を登録
 263         public void addSameTagHistory(String noteGuid, String tagGuid) {
 264                 if (noteGuid == null || noteGuid.equals("")) {
 265                         return;
 266                 }
 267                 if (tagGuid == null || tagGuid.equals("")) {
 268                         return;
 269                 }
 270
 271                 // そのタグが新しいタグでないなら終了
 272                 List<String> prevTags = new ArrayList<String>(db.getNoteTable().noteTagsTable.getNoteTags(noteGuid));
 273                 for (int i = 0; i < prevTags.size(); i++) {
 274                         System.out.println(prevTags.get(i));
 275                         if (tagGuid.equals(prevTags.get(i))) {
 276                                 return;
 277                         }
 278                 }
 279
 280                 // すでにそのタグが付いているノートを取得
 281                 List<String> sameTagNoteGuids = new ArrayList<String>(db.getNoteTable().noteTagsTable.getTagNotes(tagGuid));
 282
 283                 for (int i = 0; i < sameTagNoteGuids.size(); i++) {
 284                         String guid = sameTagNoteGuids.get(i);
 285                         addHistory("sameTag", noteGuid, guid);
 286                 }
 287         }
 288
 289         // 同じノートブックに入れられたノート間の履歴を登録
 290         public void addSameNotebookHistory(String noteGuid, String notebookGuid) {
 291                 if (noteGuid == null || noteGuid.equals("")) {
 292                         return;
 293                 }
 294                 if (notebookGuid == null || notebookGuid.equals("")) {
 295                         return;
 296                 }
 297
 298                 // すでにそのノートブックに属しているノートを取得
 299                 List<String> sameNotebookNoteGuids = new ArrayList<String>(db.getNoteTable().getNotesByNotebook(notebookGuid));
 300
 301                 for (int i = 0; i < sameNotebookNoteGuids.size(); i++) {
 302                         String guid = sameNotebookNoteGuids.get(i);
 303                         if (!noteGuid.equals(guid)) {
 304                                 addHistory("sameNotebook", noteGuid, guid);
 305                         }
 306                 }
 307         }
 308
 309         // 操作履歴数の取得
 310         public int getHistoryCount() {
 311                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
 312                 query.exec("Select count(*) from History");
 313                 query.next();
 314                 int returnValue = new Integer(query.valueString(0));
 315                 return returnValue;
 316         }
 317
 318         // 連想ノートクリック回数を取得
 319         public int getRensoClickCount() {
 320                 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
 321                 query.exec("Select count(*) from History where behaviorType='rensoItemClick'");
 322                 query.next();
 323                 int returnValue = new Integer(query.valueString(0));
 324                 return returnValue;
 325         }
 326 }