OSDN Git Service

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