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

   1 /*\r
   2  * This file is part of NixNote/NeighborNote \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                 Tag t = getTag(guid);\r
 182                 \r
 183                 \r
 184         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 185 \r
 186         check = query.prepare("delete from Tag "\r
 187                                 +"where guid=:guid");\r
 188                 if (!check) {\r
 189                         logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");\r
 190                         logger.log(logger.EXTREME, query.lastError());\r
 191                 }\r
 192                 query.bindValue(":guid", guid);\r
 193                 check = query.exec();\r
 194                 if (!check)\r
 195                         logger.log(logger.MEDIUM, "Tag delete failed.");\r
 196                 \r
 197         check = query.prepare("delete from NoteTags "\r
 198                                 +"where tagGuid=:guid");\r
 199                 if (!check) {\r
 200                         logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");\r
 201                         logger.log(logger.EXTREME, query.lastError());\r
 202                 }\r
 203                 \r
 204                 query.bindValue(":guid", guid);\r
 205                 check = query.exec();\r
 206                 if (!check)\r
 207                         logger.log(logger.MEDIUM, "NoteTags delete failed.");\r
 208                 \r
 209                 // Add the work to the parent queue\r
 210                 if (needsSync && t!= null && t.getUpdateSequenceNum() > 0) {\r
 211                         DeletedTable del = new DeletedTable(logger, db);\r
 212                         del.addDeletedItem(guid, "Tag");\r
 213                 }\r
 214         }\r
 215         // Save a tag\r
 216         public void addTag(Tag tempTag, boolean isDirty) {\r
 217                 addTag(tempTag, isDirty, false, "", "");\r
 218         }\r
 219         // Save a tag\r
 220         public void addTag(Tag tempTag, boolean isDirty, boolean isLinked, String realName, String notebookGuid) {\r
 221                 boolean check;\r
 222                 \r
 223         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 224                 check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty, linked, realName, notebookGuid)"\r
 225                                 +" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty, :linked, :realName, :notebookGuid)");\r
 226                 if (!check) {\r
 227                         logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");\r
 228                         logger.log(logger.EXTREME, query.lastError());\r
 229                 }\r
 230                 query.bindValue(":guid", tempTag.getGuid());\r
 231                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
 232                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
 233                 query.bindValue(":hashCode", tempTag.hashCode());\r
 234                 query.bindValue(":name", tempTag.getName());\r
 235                 query.bindValue(":isDirty", isDirty);\r
 236                 query.bindValue(":linked", isLinked);\r
 237                 query.bindValue(":realName", realName);\r
 238                 query.bindValue(":notebookGuid", notebookGuid);\r
 239                 \r
 240                 check = query.exec();\r
 241                 if (!check) {\r
 242                         logger.log(logger.MEDIUM, "Tag Table insert failed.");\r
 243                         logger.log(logger.MEDIUM, query.lastError());\r
 244                 }\r
 245         }\r
 246         // Update a tag's parent\r
 247         public void updateTagParent(String guid, String parentGuid) {\r
 248                 boolean check;\r
 249                 \r
 250         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 251                 check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");\r
 252                 if (!check) {\r
 253                         logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");\r
 254                         logger.log(logger.EXTREME, query.lastError());\r
 255                 }\r
 256 \r
 257                 query.bindValue(":parentGuid", parentGuid);\r
 258                 query.bindValue(":guid", guid);\r
 259                 \r
 260                 check = query.exec();\r
 261                 if (!check) {\r
 262                         logger.log(logger.MEDIUM, "Tag parent update failed.");\r
 263                         logger.log(logger.MEDIUM, query.lastError());\r
 264                 }\r
 265         }\r
 266         //Save tags from Evernote\r
 267         public void saveTags(List<Tag> tags) {\r
 268                 Tag tempTag;\r
 269                 for (int i=0; i<tags.size(); i++) {\r
 270                         tempTag = tags.get(i);\r
 271                         addTag(tempTag, false);\r
 272                 }               \r
 273         }\r
 274         // Update a tag sequence number\r
 275         public void updateTagSequence(String guid, int sequence) {\r
 276                 boolean check;\r
 277         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 278                 check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");\r
 279                 query.bindValue(":sequence", sequence);\r
 280                 query.bindValue(":guid", guid);\r
 281                 \r
 282                 query.exec();\r
 283                 if (!check) {\r
 284                         logger.log(logger.MEDIUM, "Tag sequence update failed.");\r
 285                         logger.log(logger.MEDIUM, query.lastError());\r
 286                 }\r
 287                 \r
 288         }\r
 289         // Update a tag sequence number\r
 290         public void updateTagGuid(String oldGuid, String newGuid) {\r
 291                 boolean check;\r
 292                 \r
 293         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 294                 check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");\r
 295                 query.bindValue(":newGuid", newGuid);\r
 296                 query.bindValue(":oldGuid", oldGuid);\r
 297                 query.exec();\r
 298                 if (!check) {\r
 299                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
 300                         logger.log(logger.MEDIUM, query.lastError());\r
 301                 }\r
 302                 \r
 303                 check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");\r
 304                 query.bindValue(":newGuid", newGuid);\r
 305                 query.bindValue(":oldGuid", oldGuid);\r
 306                 query.exec();\r
 307                 if (!check) {\r
 308                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
 309                         logger.log(logger.MEDIUM, query.lastError());\r
 310                 }\r
 311                 \r
 312                 check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");\r
 313                 query.bindValue(":newGuid", newGuid);\r
 314                 query.bindValue(":oldGuid", oldGuid);\r
 315                 query.exec();\r
 316                 if (!check) {\r
 317                         logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");\r
 318                         logger.log(logger.MEDIUM, query.lastError());\r
 319                 }\r
 320                 \r
 321         }\r
 322         // Get dirty tags\r
 323         public List<Tag> getDirty() {\r
 324                 Tag tempTag;\r
 325                 List<Tag> index = new ArrayList<Tag>();\r
 326                 boolean check;\r
 327                                                 \r
 328                 \r
 329         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 330                                                 \r
 331                 check = query.exec("Select guid, parentGuid, sequence, name"\r
 332                                 +" from Tag where isDirty = true");\r
 333                 if (!check)\r
 334                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 335                 while (query.next()) {\r
 336                         tempTag = new Tag();\r
 337                         tempTag.setGuid(query.valueString(0));\r
 338                         tempTag.setParentGuid(query.valueString(1));\r
 339                         int sequence = new Integer(query.valueString(2)).intValue();\r
 340                         tempTag.setUpdateSequenceNum(sequence);\r
 341                         tempTag.setName(query.valueString(3));\r
 342                         if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))\r
 343                                 tempTag.setParentGuid(null);\r
 344                         index.add(tempTag); \r
 345                 }\r
 346                 return index;\r
 347         }\r
 348         // Find a guid based upon the name\r
 349         public String findTagByName(String name) {\r
 350                 \r
 351                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 352                 \r
 353                 query.prepare("Select guid from tag where name=:name");\r
 354                 query.bindValue(":name", name);\r
 355                 if (!query.exec())\r
 356                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 357                 String val = null;\r
 358                 if (query.next())\r
 359                         val = query.valueString(0);\r
 360                 return val;\r
 361         }\r
 362         // Get the linked notebook guid for this tag\r
 363         public String getNotebookGuid(String guid) {\r
 364                 \r
 365                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 366                 \r
 367                 query.prepare("Select notebookguid from tag where guid=:guid");\r
 368                 query.bindValue(":guid", guid);\r
 369                 if (!query.exec())\r
 370                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 371                 String val = null;\r
 372                 if (query.next())\r
 373                         val = query.valueString(0);\r
 374                 return val;\r
 375         }\r
 376         // given a guid, does the tag exist\r
 377         public boolean exists(String guid) {\r
 378                 \r
 379                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 380                 \r
 381                 query.prepare("Select guid from tag where guid=:guid");\r
 382                 query.bindValue(":guid", guid);\r
 383                 if (!query.exec())\r
 384                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 385                 boolean retval = query.next();\r
 386                 return retval;\r
 387         }\r
 388         // This is a convience method to check if a tag exists & update/create based upon it\r
 389         public void syncLinkedTag(Tag tag, String notebookGuid, boolean isDirty) {\r
 390                 if (exists(tag.getGuid())) {\r
 391                         Tag t = getTag(tag.getGuid());\r
 392                         String realName = tag.getName();\r
 393                         tag.setName(t.getName());\r
 394                         updateTag(tag, isDirty, realName);\r
 395                 }\r
 396                 else\r
 397                         addTag(tag, isDirty, true, tag.getName(), notebookGuid);\r
 398         }\r
 399 \r
 400         // This is a convience method to check if a tag exists & update/create based upon it\r
 401         public void syncTag(Tag tag, boolean isDirty) {\r
 402                 if (exists(tag.getGuid()))\r
 403                         updateTag(tag, isDirty);\r
 404                 else\r
 405                         addTag(tag, isDirty);\r
 406         }\r
 407         public void  resetDirtyFlag(String guid) {\r
 408                 \r
 409                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 410                 \r
 411                 query.prepare("Update tag set isdirty=false where guid=:guid");\r
 412                 query.bindValue(":guid", guid);\r
 413                 if (!query.exec())\r
 414                         logger.log(logger.EXTREME, "Error resetting tag dirty field.");\r
 415         }\r
 416         \r
 417         \r
 418         // Get the custom icon\r
 419         public QIcon getIcon(String guid) {\r
 420                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 421                 \r
 422                 if (!query.prepare("Select icon from tag where guid=:guid"))\r
 423                         logger.log(logger.EXTREME, "Error preparing tag icon select.");\r
 424                 query.bindValue(":guid", guid);\r
 425                 if (!query.exec())\r
 426                         logger.log(logger.EXTREME, "Error finding tag icon.");\r
 427                 if (!query.next() || query.getBlob(0) == null)\r
 428                         return null;\r
 429                 \r
 430                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 431                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 432                 return icon;\r
 433         }\r
 434         // Set the custom icon\r
 435         public void setIcon(String guid, QIcon icon, String type) {\r
 436                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 437                 if (icon == null) {\r
 438                         if (!query.prepare("update tag set icon=null where guid=:guid"))\r
 439                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
 440                 } else {\r
 441                         if (!query.prepare("update tag set icon=:icon where guid=:guid"))\r
 442                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
 443                         QBuffer buffer = new QBuffer();\r
 444                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 445                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 446                         return;\r
 447                 }\r
 448                 QPixmap p = icon.pixmap(32, 32);\r
 449                 QImage i = p.toImage();\r
 450                 i.save(buffer, type.toUpperCase());\r
 451                 buffer.close();\r
 452                 QByteArray b = new QByteArray(buffer.buffer());\r
 453                 if (!b.isNull() && !b.isEmpty())\r
 454                         query.bindValue(":icon", b.toByteArray());\r
 455                 else\r
 456                         return;\r
 457                 }\r
 458                 query.bindValue(":guid", guid);\r
 459                 if (!query.exec()) \r
 460                         logger.log(logger.LOW, "Error setting tag icon. " +query.lastError());\r
 461         }\r
 462 \r
 463         // Get a list of all icons\r
 464         public HashMap<String, QIcon> getAllIcons() {\r
 465                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 466                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 467         \r
 468                 if (!query.exec("SELECT guid, icon from tag"))\r
 469                         logger.log(logger.EXTREME, "Error executing SavedSearch getAllIcons select.");\r
 470                 while (query.next()) {\r
 471                         if (query.getBlob(1) != null) {\r
 472                                 String guid = query.valueString(0);\r
 473                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 474                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 475                                 values.put(guid, icon);\r
 476                         }\r
 477                 }\r
 478                 return values;\r
 479         }\r
 480 \r
 481         // Remove unused tags that are linked tags\r
 482         public void removeUnusedLinkedTags() {\r
 483                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 484                 \r
 485                 query.exec("Delete from tag where linked=true and guid not in (select distinct tagguid from notetags);");\r
 486         }\r
 487         \r
 488         public void cleanupTags() {\r
 489                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 490                 \r
 491                 query.exec("Update tag set parentguid=null where parentguid not in (select distinct guid from tag);");  \r
 492         }\r
 493 \r
 494         \r
 495         public List<String> findChildren(String guid, List<Tag> tagList) {\r
 496                 List<String> returnValue = new ArrayList<String>();\r
 497                 \r
 498                 for (int i=0; i<tagList.size(); i++) {\r
 499                         if (tagList.get(i).getParentGuid().equalsIgnoreCase(guid)) {\r
 500                                 returnValue.add(tagList.get(i).getName());\r
 501                                 List<String> childMatch = findChildren(tagList.get(i).getGuid(), tagList);\r
 502                                 for (int j=0; j<childMatch.size(); j++) {\r
 503                                         returnValue.add(childMatch.get(j));\r
 504                                 }\r
 505                         }\r
 506                 }\r
 507                 return returnValue;\r
 508         }\r
 509 }\r