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, boolean isDirty) {\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=:isDirty where guid=:guid");\r
 163                 if (!check)\r
 164                         logger.log(logger.EXTREME, "RNoteTagsTable.saveNoteTag prepare has failed.");\r
 165                 query.bindValue(":isDirty", isDirty);\r
 166                 query.bindValue(":guid", noteGuid);\r
 167                 query.exec();\r
 168                 if (!check) {\r
 169                         logger.log(logger.MEDIUM, "RNoteTagsTable.saveNoteTag has failed to set note as dirty.");               \r
 170                         logger.log(logger.MEDIUM, query.lastError());\r
 171                 }\r
 172         }\r
 173         // Delete a note's tags\r
 174         public void deleteNoteTag(String noteGuid) {\r
 175                 boolean check;\r
 176                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 177                 check = query.prepare("Delete from NoteTags where noteGuid = :noteGuid");\r
 178                 if (!check)\r
 179                         logger.log(logger.EXTREME, "Note SQL delete prepare has failed.");\r
 180         \r
 181                 query.bindValue(":noteGuid", noteGuid);\r
 182                 check = query.exec();\r
 183                 if (!check) {\r
 184                         logger.log(logger.MEDIUM, "NoteTags Table delete failed.");             \r
 185                         logger.log(logger.MEDIUM, query.lastError());\r
 186                 }\r
 187 \r
 188         }\r
 189         // Get a note tag counts\r
 190         public List<Pair<String,Integer>> getTagCounts() {\r
 191                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
 192                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 193                 if (!query.exec("select tagguid, count(noteguid) from notetags group by tagguid;")) {\r
 194                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
 195                         logger.log(logger.MEDIUM, query.lastError());\r
 196                         return null;\r
 197                 }\r
 198                 while (query.next()) {\r
 199                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
 200                         newCount.setFirst(query.valueString(0));\r
 201                         newCount.setSecond(query.valueInteger(1));\r
 202                         counts.add(newCount);\r
 203                 }       \r
 204                 return counts;\r
 205         }\r
 206 }\r