2 * This file is part of NeighborNote
3 * Copyright 2013 Yuki Takahashi
5 * This file may be licensed under the terms of of the
6 * GNU General Public License Version 2 (the ``GPL'').
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.
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.
20 package cx.fbn.nevernote.sql;
22 import cx.fbn.nevernote.sql.driver.NSqlQuery;
23 import cx.fbn.nevernote.utilities.ApplicationLogger;
25 public class StaredTable {
26 private final ApplicationLogger logger;
27 private final DatabaseConnection db;
30 public StaredTable(ApplicationLogger l, DatabaseConnection d) {
36 public void createTable() {
37 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
38 logger.log(logger.HIGH, "StaredNotesテーブルを作成しています...");
39 if (!query.exec("Create table StaredNotes (id integer primary key auto_increment, masterGuid varchar, staredGuid varchar)"))
40 logger.log(logger.HIGH, "StaredNotesテーブル作成失敗!!!");
44 public void dropTable() {
45 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
46 query.exec("Drop table StaredNotes");
49 // StaredNotesテーブルにアイテムを1つ追加
50 public void addStaredItem(String masterGuid, String staredGuid) {
51 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
52 query.prepare("Insert Into StaredNotes (masterGuid, staredGuid) Values(:masterGuid, :staredGuid)");
53 query.bindValue(":masterGuid", masterGuid);
54 query.bindValue(":staredGuid", staredGuid);
56 logger.log(logger.MEDIUM, "StaredNotesテーブルへのアイテム追加に失敗");
57 logger.log(logger.MEDIUM, query.lastError());
61 // StaredNotesテーブルからアイテムを1つ削除
62 public void removeStaredItem(String masterGuid, String staredGuid) {
63 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
64 query.prepare("Delete from StaredNotes where (masterGuid=:masterGuid and staredGuid=:staredGuid)");
65 query.bindValue(":masterGuid", masterGuid);
66 query.bindValue(":staredGuid", staredGuid);
68 logger.log(logger.MEDIUM, "StaredNotesテーブルからのアイテム削除に失敗");
69 logger.log(logger.MEDIUM, query.lastError());
73 // guidを含む列をStaredNotesテーブルから削除
74 public void expungeStaredNote(String guid) {
75 NSqlQuery query = new NSqlQuery(db.getBehaviorConnection());
78 query.prepare("Delete from StaredNotes where masterGuid=:masterGuid or staredGuid=:staredGuid");
79 query.bindValue(":masterGuid", guid);
80 query.bindValue(":staredGuid", guid);
84 logger.log(logger.MEDIUM, "StaredNotesテーブルからguid=" + guid + "のデータ削除に失敗");
85 logger.log(logger.MEDIUM, query.lastError());
89 // masterGuidとchildGuidをマージ
90 public void mergeHistoryGuid(String masterGuid, String childGuid) {
91 NSqlQuery staredNotesQuery = new NSqlQuery(db.getBehaviorConnection());
92 boolean check = false;
94 // マージ後に重複してしまうデータを先に削除
95 staredNotesQuery.prepare("Delete from StaredNotes where (masterGuid=:masterGuid1 and staredGuid=:staredGuid1) or (masterGuid=:masterGuid2 and staredGuid=:staredGuid2)");
96 staredNotesQuery.bindValue(":masterGuid1", masterGuid);
97 staredNotesQuery.bindValue(":childGuid1", childGuid);
98 staredNotesQuery.bindValue(":masterGuid2", childGuid);
99 staredNotesQuery.bindValue(":staredGuid2", masterGuid);
100 check = staredNotesQuery.exec();
102 logger.log(logger.MEDIUM, "staredNotesテーブルの重複削除で失敗");
103 logger.log(logger.MEDIUM, staredNotesQuery.lastError());
106 updateStaredNoteGuid(masterGuid, childGuid);
109 // StaredNotesテーブルのGuidを更新
110 public void updateStaredNoteGuid(String newGuid, String oldGuid){
111 NSqlQuery staredNotesQuery = new NSqlQuery(db.getBehaviorConnection());
112 boolean check = false;
114 staredNotesQuery.prepare("Update StaredNotes set masterGuid=:newGuid where masterGuid=:oldGuid");
115 staredNotesQuery.bindValue(":newGuid", newGuid);
116 staredNotesQuery.bindValue(":oldGuid", oldGuid);
117 check = staredNotesQuery.exec();
119 logger.log(logger.MEDIUM, "StaredNotesテーブルのmasterGuidのところでguid更新失敗");
120 logger.log(logger.MEDIUM, staredNotesQuery.lastError());
122 staredNotesQuery.prepare("Update StaredNotes set staredGuid=:newGuid where staredGuid=:oldGuid");
123 staredNotesQuery.bindValue(":newGuid", newGuid);
124 staredNotesQuery.bindValue(":oldGuid", oldGuid);
125 check = staredNotesQuery.exec();
127 logger.log(logger.MEDIUM, "StaredNotesテーブルのstaredGuidのところでguid更新失敗");
128 logger.log(logger.MEDIUM, staredNotesQuery.lastError());
132 // StaredNotesテーブルに引数guidのノートが存在するか
133 public boolean existNote(String masterGuid, String staredGuid) {
134 NSqlQuery staredNotesQuery = new NSqlQuery(db.getBehaviorConnection());
136 // 2つの引数guidを含むアイテムの存在確認
137 staredNotesQuery.prepare("Select * from StaredNotes where Exists(Select * from StaredNotes where (masterGuid=:masterGuid1 and staredGuid=:staredGuid1) or (masterGuid=:masterGuid2 and staredGuid=:staredGuid2))");
138 staredNotesQuery.bindValue(":masterGuid1", masterGuid);
139 staredNotesQuery.bindValue(":staredGuid1", staredGuid);
140 staredNotesQuery.bindValue(":masterGuid2", masterGuid);
141 staredNotesQuery.bindValue(":staredGuid2", staredGuid);
143 if (!staredNotesQuery.exec()) {
144 logger.log(logger.MEDIUM, "StaredNotesテーブルからmasterGuid=" + masterGuid + "かつstaredGuid=" + staredGuid + "(またはその逆)のアイテムの存在確認失敗");
145 logger.log(logger.MEDIUM, staredNotesQuery.lastError());
148 if (staredNotesQuery.next()) {
155 // oldGuidのノートの除外ノートをnewGuidのノートの除外ノートとして複製
156 public void duplicateStaredNotes(String newGuid, String oldGuid) {
157 NSqlQuery staredNotesQuery = new NSqlQuery(db.getBehaviorConnection());
159 // masterGuid = oldGuidのスター付きノートを取得
160 staredNotesQuery.prepare("Select staredGuid from StaredNotes where masterGuid=:oldGuid");
161 staredNotesQuery.bindValue(":oldGuid", oldGuid);
162 if(!staredNotesQuery.exec()){
163 logger.log(logger.MEDIUM, "StaredNotesテーブルからmasterGuid=" + oldGuid + "のアイテム取得失敗");
164 logger.log(logger.MEDIUM, staredNotesQuery.lastError());
166 // masterGuid = newGuidのスター付きノートとして複製
167 while(staredNotesQuery.next()){
168 String staredGuid = staredNotesQuery.valueString(0);
170 addStaredItem(newGuid, staredGuid);
173 // staredGuid = oldGuidの除外ノートを取得
174 staredNotesQuery.prepare("Select masterGuid from StaredNotes where staredGuid=:oldGuid");
175 staredNotesQuery.bindValue(":oldGuid", oldGuid);
176 if(!staredNotesQuery.exec()){
177 logger.log(logger.MEDIUM, "StaredNotesテーブルからstaredGuid=" + oldGuid + "のアイテム取得失敗");
178 logger.log(logger.MEDIUM, staredNotesQuery.lastError());
180 // staredGuid = newGuidの除外ノートとして複製
181 while(staredNotesQuery.next()){
182 String masterGuid = staredNotesQuery.valueString(0);
184 addStaredItem(masterGuid, newGuid);