OSDN Git Service

4abd69660dbfc20fa07504f02b5b2b0235d98ca4
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / NoteTagsTable.java
1 /*\r
2  * This file is part of NixNote \r
3  * Copyright 2009 Randy Baumgarte\r
4  * \r
5  * This file may be licensed under the terms of of the\r
6  * GNU General Public License Version 2 (the ``GPL'').\r
7  *\r
8  * Software distributed under the License is distributed\r
9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
10  * express or implied. See the GPL for the specific language\r
11  * governing rights and limitations.\r
12  *\r
13  * You should have received a copy of the GPL along with this\r
14  * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
15  * or write to the Free Software Foundation, Inc.,\r
16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
17  *\r
18 */\r
19 \r
20 \r
21 package cx.fbn.nevernote.sql;\r
22 \r
23 import java.util.ArrayList;\r
24 import java.util.List;\r
25 \r
26 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
27 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
28 import cx.fbn.nevernote.utilities.Pair;\r
29 \r
30 public class NoteTagsTable {\r
31         private final ApplicationLogger                 logger;\r
32         DatabaseConnection                                              db;\r
33         NSqlQuery                                                               getNoteTagsQuery;\r
34 \r
35         \r
36         // Constructor\r
37         public NoteTagsTable(ApplicationLogger l,DatabaseConnection d) {\r
38                 logger = l;\r
39                 db = d;\r
40         }\r
41         // Create the table\r
42         public void createTable() {\r
43                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
44         // Create the NoteTag table\r
45         logger.log(logger.HIGH, "Creating table NoteTags...");\r
46         if (!query.exec("Create table NoteTags (noteGuid varchar, " +\r
47                         "tagGuid varchar, primary key(noteGuid, tagGuid))"))\r
48                 logger.log(logger.HIGH, "Table NoteTags creation FAILED!!!"); \r
49         }\r
50         // Drop the table\r
51         public void dropTable() {\r
52                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
53                 query.exec("drop table NoteTags");\r
54         }\r
55         // Get a note tags by the note's Guid\r
56         public List<String> getNoteTags(String noteGuid) {\r
57                 if (noteGuid == null)\r
58                         return null;\r
59                 List<String> tags = new ArrayList<String>();\r
60                 \r
61                 if (getNoteTagsQuery == null)\r
62                         prepareGetNoteTagsQuery();\r
63                 \r
64                 getNoteTagsQuery.bindValue(":guid", noteGuid);\r
65                 if (!getNoteTagsQuery.exec()) {\r
66                         logger.log(logger.EXTREME, "NoteTags SQL select has failed.");\r
67                         logger.log(logger.MEDIUM, getNoteTagsQuery.lastError());\r
68                         return null;\r
69                 }\r
70                 while (getNoteTagsQuery.next()) {\r
71                         tags.add(getNoteTagsQuery.valueString(0));\r
72                 }       \r
73                 return tags;\r
74         }\r
75         // Get a list of notes by the tag guid\r
76         public List<String> getTagNotes(String tagGuid) {\r
77                 if (tagGuid == null)\r
78                         return null;\r
79                 List<String> notes = new ArrayList<String>();\r
80                 \r
81                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
82                 query.prepare("Select NoteGuid from NoteTags where tagGuid = :guid");\r
83                 \r
84                 query.bindValue(":guid", tagGuid);\r
85                 if (!query.exec()) {\r
86                         logger.log(logger.EXTREME, "getTagNotes SQL select has failed.");\r
87                         logger.log(logger.MEDIUM, query.lastError());\r
88                         return notes;\r
89                 }\r
90                 while (query.next()) {\r
91                         notes.add(query.valueString(0));\r
92                 }       \r
93                 return notes;\r
94         }\r
95         void prepareGetNoteTagsQuery() {\r
96                 getNoteTagsQuery = new NSqlQuery(db.getConnection());\r
97                 getNoteTagsQuery.prepare("Select TagGuid from NoteTags where noteGuid = :guid");\r
98         }\r
99         // Get a note tags by the note's Guid\r
100         public List<NoteTagsRecord> getAllNoteTags() {\r
101                 List<NoteTagsRecord> tags = new ArrayList<NoteTagsRecord>();\r
102                 \r
103                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
104                 if (!query.exec("Select TagGuid, NoteGuid from NoteTags")) {\r
105                         logger.log(logger.EXTREME, "NoteTags SQL select has failed.");\r
106                         logger.log(logger.MEDIUM, query.lastError());\r
107                         return null;\r
108                 }\r
109                 while (query.next()) {\r
110                         NoteTagsRecord record = new NoteTagsRecord();\r
111                         record.tagGuid = query.valueString(0);\r
112                         record.noteGuid = query.valueString(1);\r
113                         tags.add(record);\r
114                 }       \r
115                 return tags;\r
116         }\r
117         // Check if a note has a specific tag already\r
118         public boolean checkNoteNoteTags(String noteGuid, String tagGuid) {\r
119                 if (noteGuid == null || tagGuid == null)\r
120                         return false;\r
121                 boolean check;\r
122                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
123                 check = query.prepare("Select "\r
124                                 +"NoteGuid, TagGuid from NoteTags where noteGuid = :noteGuid and tagGuid = :tagGuid");\r
125                 if (!check)\r
126                         logger.log(logger.EXTREME, "checkNoteTags SQL prepare has failed.");\r
127                 \r
128                 query.bindValue(":noteGuid", noteGuid);\r
129                 query.bindValue(":tagGuid", tagGuid);\r
130                 query.exec();\r
131                 \r
132                 if (!check) {\r
133                         logger.log(logger.EXTREME, "checkNoteTags SQL select has failed.");\r
134                         logger.log(logger.MEDIUM, query.lastError());\r
135                         return false;\r
136                 }\r
137                 \r
138                 if (query.next()) {\r
139                         return true;\r
140                 }       \r
141                 return false;\r
142         }\r
143         // Save Note Tags\r
144         public void saveNoteTag(String noteGuid, String tagGuid) {\r
145                 boolean check;\r
146                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
147 \r
148                 check = query.prepare("Insert Into NoteTags (noteGuid, tagGuid) "\r
149                                 +"Values("\r
150                                 +":noteGuid, :tagGuid)");\r
151                 if (!check)\r
152                         logger.log(logger.EXTREME, "Note SQL insert prepare has failed.");\r
153         \r
154                 query.bindValue(":noteGuid", noteGuid);\r
155                 query.bindValue(":tagGuid", tagGuid);\r
156                                                 \r
157                 check = query.exec();\r
158                 if (!check) {\r
159                         logger.log(logger.MEDIUM, "NoteTags Table insert failed.");             \r
160                         logger.log(logger.MEDIUM, query.lastError());\r
161                 }\r
162                 check = query.prepare("Update Note set isDirty=1 where guid=:guid");\r
163                 if (!check)\r
164                         logger.log(logger.EXTREME, "RNoteTagsTable.saveNoteTag prepare has failed.");\r
165                 query.bindValue(":guid", noteGuid);\r
166                 if (!check) {\r
167                         logger.log(logger.MEDIUM, "RNoteTagsTable.saveNoteTag has failed to set note as dirty.");               \r
168                         logger.log(logger.MEDIUM, query.lastError());\r
169                 }\r
170         }\r
171         // Delete a note's tags\r
172         public void deleteNoteTag(String noteGuid) {\r
173                 boolean check;\r
174                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
175                 check = query.prepare("Delete from NoteTags where noteGuid = :noteGuid");\r
176                 if (!check)\r
177                         logger.log(logger.EXTREME, "Note SQL delete prepare has failed.");\r
178         \r
179                 query.bindValue(":noteGuid", noteGuid);\r
180                 check = query.exec();\r
181                 if (!check) {\r
182                         logger.log(logger.MEDIUM, "NoteTags Table delete failed.");             \r
183                         logger.log(logger.MEDIUM, query.lastError());\r
184                 }\r
185 \r
186         }\r
187         // Get a note tag counts\r
188         public List<Pair<String,Integer>> getTagCounts() {\r
189                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
190                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
191                 if (!query.exec("select tagguid, count(noteguid) from notetags group by tagguid;")) {\r
192                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
193                         logger.log(logger.MEDIUM, query.lastError());\r
194                         return null;\r
195                 }\r
196                 while (query.next()) {\r
197                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
198                         newCount.setFirst(query.valueString(0));\r
199                         newCount.setSecond(query.valueInteger(1));\r
200                         counts.add(newCount);\r
201                 }       \r
202                 return counts;\r
203         }\r
204 }\r