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

   1 /*\r
   2  * This file is part of NeverNote \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.HashMap;\r
  25 import java.util.List;\r
  26 \r
  27 import com.evernote.edam.type.Tag;\r
  28 import com.trolltech.qt.core.QBuffer;\r
  29 import com.trolltech.qt.core.QByteArray;\r
  30 import com.trolltech.qt.core.QIODevice;\r
  31 import com.trolltech.qt.gui.QIcon;\r
  32 import com.trolltech.qt.gui.QImage;\r
  33 import com.trolltech.qt.gui.QPixmap;\r
  34 \r
  35 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  36 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  37 \r
  38 public class TagTable {\r
  39         private final ApplicationLogger logger;\r
  40         DatabaseConnection db;\r
  41 \r
  42         public TagTable (ApplicationLogger l, DatabaseConnection d) {\r
  43                 logger = l;\r
  44                 db = d;\r
  45         }\r
  46         // Create the table\r
  47         public void createTable() {\r
  48                 \r
  49                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  50         logger.log(logger.HIGH, "Creating table Tag...");\r
  51         if (!query.exec("Create table Tag (guid varchar primary key, " +\r
  52                         "parentGuid varchar, sequence integer, hashCode integer, name varchar, isDirty boolean)"))\r
  53                 logger.log(logger.HIGH, "Table TAG creation FAILED!!!");  \r
  54                 \r
  55         }\r
  56         // Drop the table\r
  57         public void dropTable() {\r
  58                 \r
  59                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  60                 query.exec("Drop table Tag");\r
  61                 \r
  62         }\r
  63         // Get tags for a specific notebook\r
  64         // get all tags\r
  65         public List<Tag> getTagsForNotebook(String notebookGuid) {\r
  66                 \r
  67                 Tag tempTag;\r
  68                 List<Tag> index = new ArrayList<Tag>();\r
  69                 boolean check;\r
  70                                                 \r
  71         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  72                                                 \r
  73                 check = query.prepare("Select guid, parentGuid, sequence, name"\r
  74                                 +" from Tag where notebookGuid=:notebookGuid");\r
  75                 if (!check) {\r
  76                         logger.log(logger.EXTREME, "Tag SQL prepare getTagsForNotebook has failed.");\r
  77                         logger.log(logger.EXTREME, query.lastError());\r
  78                 }\r
  79                 query.bindValue(":notebookGuid", notebookGuid);\r
  80                 query.exec();\r
  81                 while (query.next()) {\r
  82                         tempTag = new Tag();\r
  83                         tempTag.setGuid(query.valueString(0));\r
  84                         if (query.valueString(1) != null)\r
  85                                 tempTag.setParentGuid(query.valueString(1));\r
  86                         else\r
  87                                 tempTag.setParentGuid(null);\r
  88                         int sequence = new Integer(query.valueString(2)).intValue();\r
  89                         tempTag.setUpdateSequenceNum(sequence);\r
  90                         tempTag.setName(query.valueString(3));\r
  91                         index.add(tempTag); \r
  92                 }       \r
  93                 \r
  94                 return index;\r
  95         }\r
  96         // get all tags\r
  97         public List<Tag> getAll() {\r
  98                 \r
  99                 Tag tempTag;\r
 100                 List<Tag> index = new ArrayList<Tag>();\r
 101                 boolean check;\r
 102                                                 \r
 103         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 104                                                 \r
 105                 check = query.exec("Select guid, parentGuid, sequence, name"\r
 106                                 +" from Tag where notebookguid not in (select guid from notebook where archived=true)");\r
 107                 if (!check) {\r
 108                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 109                         logger.log(logger.EXTREME, query.lastError());\r
 110                 }\r
 111                 while (query.next()) {\r
 112                         tempTag = new Tag();\r
 113                         tempTag.setGuid(query.valueString(0));\r
 114                         if (query.valueString(1) != null)\r
 115                                 tempTag.setParentGuid(query.valueString(1));\r
 116                         else\r
 117                                 tempTag.setParentGuid(null);\r
 118                         int sequence = new Integer(query.valueString(2)).intValue();\r
 119                         tempTag.setUpdateSequenceNum(sequence);\r
 120                         tempTag.setName(query.valueString(3));\r
 121                         index.add(tempTag); \r
 122                 }       \r
 123                 \r
 124                 return index;\r
 125         }\r
 126         public Tag getTag(String guid) {\r
 127                 Tag tempTag = new Tag();                \r
 128                 \r
 129         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 130                                                         \r
 131                 if (!query.prepare("Select guid, parentGuid, sequence, name"\r
 132                                 +" from Tag where guid=:guid"))\r
 133                         logger.log(logger.EXTREME, "Tag select by guid SQL prepare has failed.");\r
 134 \r
 135                 query.bindValue(":guid", guid);\r
 136                 if (!query.exec())\r
 137                         logger.log(logger.EXTREME, "Tag select by guid SQL exec has failed.");\r
 138                 \r
 139                 if (!query.next())  {\r
 140                         return tempTag;\r
 141                 }\r
 142                 tempTag.setGuid(query.valueString(0));\r
 143                 tempTag.setParentGuid(query.valueString(1));\r
 144                 int sequence = new Integer(query.valueString(2)).intValue();\r
 145                 tempTag.setUpdateSequenceNum(sequence);\r
 146                 tempTag.setName(query.valueString(3));\r
 147                 return tempTag;\r
 148         }\r
 149         // Update a tag\r
 150         public void updateTag(Tag tempTag, boolean isDirty) {\r
 151                 updateTag(tempTag, isDirty, "");\r
 152         }\r
 153         // Update a tag\r
 154         public void updateTag(Tag tempTag, boolean isDirty, String realName) {\r
 155                 boolean check;\r
 156                 \r
 157         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 158                 check = query.prepare("Update Tag set parentGuid=:parentGuid, sequence=:sequence, "+\r
 159                         "hashCode=:hashCode, name=:name, isDirty=:isDirty "\r
 160                         +"where guid=:guid");\r
 161       \r
 162                 if (!check) {\r
 163                         logger.log(logger.EXTREME, "Tag SQL update prepare has failed.");\r
 164                         logger.log(logger.EXTREME, query.lastError());\r
 165                 }\r
 166                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
 167                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
 168                 query.bindValue(":hashCode", tempTag.hashCode());\r
 169                 query.bindValue(":name", tempTag.getName());\r
 170                 query.bindValue(":isDirty", isDirty);\r
 171                 query.bindValue(":guid", tempTag.getGuid());\r
 172                 \r
 173                 check = query.exec();\r
 174                 if (!check)\r
 175                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
 176                 \r
 177         }\r
 178         // Delete a tag\r
 179         public void expungeTag(String guid, boolean needsSync) {\r
 180                 boolean check;\r
 181                 \r
 182                 \r
 183         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 184 \r
 185         check = query.prepare("delete from Tag "\r
 186                                 +"where guid=:guid");\r
 187                 if (!check) {\r
 188                         logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");\r
 189                         logger.log(logger.EXTREME, query.lastError());\r
 190                 }\r
 191                 query.bindValue(":guid", guid);\r
 192                 check = query.exec();\r
 193                 if (!check)\r
 194                         logger.log(logger.MEDIUM, "Tag delete failed.");\r
 195                 \r
 196         check = query.prepare("delete from NoteTags "\r
 197                                 +"where tagGuid=:guid");\r
 198                 if (!check) {\r
 199                         logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");\r
 200                         logger.log(logger.EXTREME, query.lastError());\r
 201                 }\r
 202                 \r
 203                 query.bindValue(":guid", guid);\r
 204                 check = query.exec();\r
 205                 if (!check)\r
 206                         logger.log(logger.MEDIUM, "NoteTags delete failed.");\r
 207                 \r
 208                 // Add the work to the parent queue\r
 209                 if (needsSync) {\r
 210                         DeletedTable del = new DeletedTable(logger, db);\r
 211                         del.addDeletedItem(guid, "Tag");\r
 212                 }\r
 213         }\r
 214         // Save a tag\r
 215         public void addTag(Tag tempTag, boolean isDirty) {\r
 216                 addTag(tempTag, isDirty, false, "", "");\r
 217         }\r
 218         // Save a tag\r
 219         public void addTag(Tag tempTag, boolean isDirty, boolean isLinked, String realName, String notebookGuid) {\r
 220                 boolean check;\r
 221                 \r
 222         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 223                 check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty, linked, realName, notebookGuid)"\r
 224                                 +" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty, :linked, :realName, :notebookGuid)");\r
 225                 if (!check) {\r
 226                         logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");\r
 227                         logger.log(logger.EXTREME, query.lastError());\r
 228                 }\r
 229                 query.bindValue(":guid", tempTag.getGuid());\r
 230                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
 231                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
 232                 query.bindValue(":hashCode", tempTag.hashCode());\r
 233                 query.bindValue(":name", tempTag.getName());\r
 234                 query.bindValue(":isDirty", isDirty);\r
 235                 query.bindValue(":linked", isLinked);\r
 236                 query.bindValue(":realName", realName);\r
 237                 query.bindValue(":notebookGuid", notebookGuid);\r
 238                 \r
 239                 check = query.exec();\r
 240                 if (!check) {\r
 241                         logger.log(logger.MEDIUM, "Tag Table insert failed.");\r
 242                         logger.log(logger.MEDIUM, query.lastError());\r
 243                 }\r
 244         }\r
 245         // Update a tag's parent\r
 246         public void updateTagParent(String guid, String parentGuid) {\r
 247                 boolean check;\r
 248                 \r
 249         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 250                 check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");\r
 251                 if (!check) {\r
 252                         logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");\r
 253                         logger.log(logger.EXTREME, query.lastError());\r
 254                 }\r
 255 \r
 256                 query.bindValue(":parentGuid", parentGuid);\r
 257                 query.bindValue(":guid", guid);\r
 258                 \r
 259                 check = query.exec();\r
 260                 if (!check) {\r
 261                         logger.log(logger.MEDIUM, "Tag parent update failed.");\r
 262                         logger.log(logger.MEDIUM, query.lastError());\r
 263                 }\r
 264         }\r
 265         //Save tags from Evernote\r
 266         public void saveTags(List<Tag> tags) {\r
 267                 Tag tempTag;\r
 268                 for (int i=0; i<tags.size(); i++) {\r
 269                         tempTag = tags.get(i);\r
 270                         addTag(tempTag, false);\r
 271                 }               \r
 272         }\r
 273         // Update a tag sequence number\r
 274         public void updateTagSequence(String guid, int sequence) {\r
 275                 boolean check;\r
 276         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 277                 check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");\r
 278                 query.bindValue(":sequence", sequence);\r
 279                 query.bindValue(":guid", guid);\r
 280                 \r
 281                 query.exec();\r
 282                 if (!check) {\r
 283                         logger.log(logger.MEDIUM, "Tag sequence update failed.");\r
 284                         logger.log(logger.MEDIUM, query.lastError());\r
 285                 }\r
 286                 \r
 287         }\r
 288         // Update a tag sequence number\r
 289         public void updateTagGuid(String oldGuid, String newGuid) {\r
 290                 boolean check;\r
 291                 \r
 292         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 293                 check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");\r
 294                 query.bindValue(":newGuid", newGuid);\r
 295                 query.bindValue(":oldGuid", oldGuid);\r
 296                 query.exec();\r
 297                 if (!check) {\r
 298                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
 299                         logger.log(logger.MEDIUM, query.lastError());\r
 300                 }\r
 301                 \r
 302                 check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");\r
 303                 query.bindValue(":newGuid", newGuid);\r
 304                 query.bindValue(":oldGuid", oldGuid);\r
 305                 query.exec();\r
 306                 if (!check) {\r
 307                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
 308                         logger.log(logger.MEDIUM, query.lastError());\r
 309                 }\r
 310                 \r
 311                 check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");\r
 312                 query.bindValue(":newGuid", newGuid);\r
 313                 query.bindValue(":oldGuid", oldGuid);\r
 314                 query.exec();\r
 315                 if (!check) {\r
 316                         logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");\r
 317                         logger.log(logger.MEDIUM, query.lastError());\r
 318                 }\r
 319                 \r
 320         }\r
 321         // Get dirty tags\r
 322         public List<Tag> getDirty() {\r
 323                 Tag tempTag;\r
 324                 List<Tag> index = new ArrayList<Tag>();\r
 325                 boolean check;\r
 326                                                 \r
 327                 \r
 328         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 329                                                 \r
 330                 check = query.exec("Select guid, parentGuid, sequence, name"\r
 331                                 +" from Tag where isDirty = true");\r
 332                 if (!check)\r
 333                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 334                 while (query.next()) {\r
 335                         tempTag = new Tag();\r
 336                         tempTag.setGuid(query.valueString(0));\r
 337                         tempTag.setParentGuid(query.valueString(1));\r
 338                         int sequence = new Integer(query.valueString(2)).intValue();\r
 339                         tempTag.setUpdateSequenceNum(sequence);\r
 340                         tempTag.setName(query.valueString(3));\r
 341                         if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))\r
 342                                 tempTag.setParentGuid(null);\r
 343                         index.add(tempTag); \r
 344                 }\r
 345                 return index;\r
 346         }\r
 347         // Find a guid based upon the name\r
 348         public String findTagByName(String name) {\r
 349                 \r
 350                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 351                 \r
 352                 query.prepare("Select guid from tag where name=:name");\r
 353                 query.bindValue(":name", name);\r
 354                 if (!query.exec())\r
 355                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 356                 String val = null;\r
 357                 if (query.next())\r
 358                         val = query.valueString(0);\r
 359                 return val;\r
 360         }\r
 361         // Get the linked notebook guid for this tag\r
 362         public String getNotebookGuid(String guid) {\r
 363                 \r
 364                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 365                 \r
 366                 query.prepare("Select notebookguid from tag where guid=:guid");\r
 367                 query.bindValue(":guid", guid);\r
 368                 if (!query.exec())\r
 369                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 370                 String val = null;\r
 371                 if (query.next())\r
 372                         val = query.valueString(0);\r
 373                 return val;\r
 374         }\r
 375         // given a guid, does the tag exist\r
 376         public boolean exists(String guid) {\r
 377                 \r
 378                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 379                 \r
 380                 query.prepare("Select guid from tag where guid=:guid");\r
 381                 query.bindValue(":guid", guid);\r
 382                 if (!query.exec())\r
 383                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 384                 boolean retval = query.next();\r
 385                 return retval;\r
 386         }\r
 387         // This is a convience method to check if a tag exists & update/create based upon it\r
 388         public void syncLinkedTag(Tag tag, String notebookGuid, boolean isDirty) {\r
 389                 if (exists(tag.getGuid())) {\r
 390                         Tag t = getTag(tag.getGuid());\r
 391                         String realName = tag.getName();\r
 392                         tag.setName(t.getName());\r
 393                         updateTag(tag, isDirty, realName);\r
 394                 }\r
 395                 else\r
 396                         addTag(tag, isDirty, true, tag.getName(), notebookGuid);\r
 397         }\r
 398 \r
 399         // This is a convience method to check if a tag exists & update/create based upon it\r
 400         public void syncTag(Tag tag, boolean isDirty) {\r
 401                 if (exists(tag.getGuid()))\r
 402                         updateTag(tag, isDirty);\r
 403                 else\r
 404                         addTag(tag, isDirty);\r
 405         }\r
 406         public void  resetDirtyFlag(String guid) {\r
 407                 \r
 408                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 409                 \r
 410                 query.prepare("Update tag set isdirty=false where guid=:guid");\r
 411                 query.bindValue(":guid", guid);\r
 412                 if (!query.exec())\r
 413                         logger.log(logger.EXTREME, "Error resetting tag dirty field.");\r
 414         }\r
 415         \r
 416         \r
 417         // Get the custom icon\r
 418         public QIcon getIcon(String guid) {\r
 419                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 420                 \r
 421                 if (!query.prepare("Select icon from tag where guid=:guid"))\r
 422                         logger.log(logger.EXTREME, "Error preparing tag icon select.");\r
 423                 query.bindValue(":guid", guid);\r
 424                 if (!query.exec())\r
 425                         logger.log(logger.EXTREME, "Error finding tag icon.");\r
 426                 if (!query.next() || query.getBlob(0) == null)\r
 427                         return null;\r
 428                 \r
 429                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 430                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 431                 return icon;\r
 432         }\r
 433         // Set the custom icon\r
 434         public void setIcon(String guid, QIcon icon, String type) {\r
 435                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 436                 if (icon == null) {\r
 437                         if (!query.prepare("update tag set icon=null where guid=:guid"))\r
 438                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
 439                 } else {\r
 440                         if (!query.prepare("update tag set icon=:icon where guid=:guid"))\r
 441                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
 442                         QBuffer buffer = new QBuffer();\r
 443                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 444                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 445                         return;\r
 446                 }\r
 447                 QPixmap p = icon.pixmap(32, 32);\r
 448                 QImage i = p.toImage();\r
 449                 i.save(buffer, type.toUpperCase());\r
 450                 buffer.close();\r
 451                 QByteArray b = new QByteArray(buffer.buffer());\r
 452                 if (!b.isNull() && !b.isEmpty())\r
 453                         query.bindValue(":icon", b.toByteArray());\r
 454                 else\r
 455                         return;\r
 456                 }\r
 457                 query.bindValue(":guid", guid);\r
 458                 if (!query.exec()) \r
 459                         logger.log(logger.LOW, "Error setting tag icon. " +query.lastError());\r
 460         }\r
 461 \r
 462         // Get a list of all icons\r
 463         public HashMap<String, QIcon> getAllIcons() {\r
 464                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 465                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 466         \r
 467                 if (!query.exec("SELECT guid, icon from tag"))\r
 468                         logger.log(logger.EXTREME, "Error executing SavedSearch getAllIcons select.");\r
 469                 while (query.next()) {\r
 470                         if (query.getBlob(1) != null) {\r
 471                                 String guid = query.valueString(0);\r
 472                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 473                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 474                                 values.put(guid, icon);\r
 475                         }\r
 476                 }\r
 477                 return values;\r
 478         }\r
 479 \r
 480         // Remove unused tags that are linked tags\r
 481         public void removeUnusedLinkedTags() {\r
 482                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 483                 \r
 484                 query.exec("Delete from tag where linked=true and guid not in (select distinct tagguid from notetags);");\r
 485         }\r
 486         \r
 487         public void cleanupTags() {\r
 488                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 489                 \r
 490                 query.exec("Update tag set parentguid=null where parentguid not in (select distinct guid from tag);");  \r
 491         }\r
 492 }\r