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 all tags\r
  64         public List<Tag> getAll() {\r
  65                 \r
  66                 Tag tempTag;\r
  67                 List<Tag> index = new ArrayList<Tag>();\r
  68                 boolean check;\r
  69                                                 \r
  70         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  71                                                 \r
  72                 check = query.exec("Select guid, parentGuid, sequence, name"\r
  73                                 +" from Tag");\r
  74                 if (!check) {\r
  75                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
  76                         logger.log(logger.EXTREME, query.lastError());\r
  77                 }\r
  78                 while (query.next()) {\r
  79                         tempTag = new Tag();\r
  80                         tempTag.setGuid(query.valueString(0));\r
  81                         if (query.valueString(1) != null)\r
  82                                 tempTag.setParentGuid(query.valueString(1));\r
  83                         else\r
  84                                 tempTag.setParentGuid(null);\r
  85                         int sequence = new Integer(query.valueString(2)).intValue();\r
  86                         tempTag.setUpdateSequenceNum(sequence);\r
  87                         tempTag.setName(query.valueString(3));\r
  88                         index.add(tempTag); \r
  89                 }       \r
  90                 \r
  91                 return index;\r
  92         }\r
  93         public Tag getTag(String guid) {\r
  94                 Tag tempTag = new Tag();                \r
  95                 \r
  96         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  97                                                         \r
  98                 if (!query.prepare("Select guid, parentGuid, sequence, name"\r
  99                                 +" from Tag where guid=:guid"))\r
 100                         logger.log(logger.EXTREME, "Tag select by guid SQL prepare has failed.");\r
 101 \r
 102                 query.bindValue(":guid", guid);\r
 103                 if (!query.exec())\r
 104                         logger.log(logger.EXTREME, "Tag select by guid SQL exec has failed.");\r
 105                 \r
 106                 if (!query.next())  {\r
 107                         return tempTag;\r
 108                 }\r
 109                 tempTag.setGuid(query.valueString(0));\r
 110                 tempTag.setParentGuid(query.valueString(1));\r
 111                 int sequence = new Integer(query.valueString(2)).intValue();\r
 112                 tempTag.setUpdateSequenceNum(sequence);\r
 113                 tempTag.setName(query.valueString(3));\r
 114                 return tempTag;\r
 115         }\r
 116         // Update a tag\r
 117         public void updateTag(Tag tempTag, boolean isDirty) {\r
 118                 boolean check;\r
 119                 \r
 120         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 121                 check = query.prepare("Update Tag set parentGuid=:parentGuid, sequence=:sequence, "+\r
 122                         "hashCode=:hashCode, name=:name, isDirty=:isDirty "\r
 123                         +"where guid=:guid");\r
 124       \r
 125                 if (!check) {\r
 126                         logger.log(logger.EXTREME, "Tag SQL update prepare has failed.");\r
 127                         logger.log(logger.EXTREME, query.lastError());\r
 128                 }\r
 129                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
 130                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
 131                 query.bindValue(":hashCode", tempTag.hashCode());\r
 132                 query.bindValue(":name", tempTag.getName());\r
 133                 query.bindValue(":isDirty", isDirty);\r
 134                 query.bindValue(":guid", tempTag.getGuid());\r
 135                 \r
 136                 check = query.exec();\r
 137                 if (!check)\r
 138                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
 139                 \r
 140         }\r
 141         // Delete a tag\r
 142         public void expungeTag(String guid, boolean needsSync) {\r
 143                 boolean check;\r
 144                 \r
 145                 \r
 146         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 147 \r
 148         check = query.prepare("delete from Tag "\r
 149                                 +"where guid=:guid");\r
 150                 if (!check) {\r
 151                         logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");\r
 152                         logger.log(logger.EXTREME, query.lastError());\r
 153                 }\r
 154                 query.bindValue(":guid", guid);\r
 155                 check = query.exec();\r
 156                 if (!check)\r
 157                         logger.log(logger.MEDIUM, "Tag delete failed.");\r
 158                 \r
 159         check = query.prepare("delete from NoteTags "\r
 160                                 +"where tagGuid=:guid");\r
 161                 if (!check) {\r
 162                         logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");\r
 163                         logger.log(logger.EXTREME, query.lastError());\r
 164                 }\r
 165                 \r
 166                 query.bindValue(":guid", guid);\r
 167                 check = query.exec();\r
 168                 if (!check)\r
 169                         logger.log(logger.MEDIUM, "NoteTags delete failed.");\r
 170                 \r
 171                 // Add the work to the parent queue\r
 172                 if (needsSync) {\r
 173                         DeletedTable del = new DeletedTable(logger, db);\r
 174                         del.addDeletedItem(guid, "Tag");\r
 175                 }\r
 176         }\r
 177         // Save a tag\r
 178         public void addTag(Tag tempTag, boolean isDirty) {\r
 179                 boolean check;\r
 180                 \r
 181         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 182                 check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty)"\r
 183                                 +" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty)");\r
 184                 if (!check) {\r
 185                         logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");\r
 186                         logger.log(logger.EXTREME, query.lastError());\r
 187                 }\r
 188                 query.bindValue(":guid", tempTag.getGuid());\r
 189                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
 190                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
 191                 query.bindValue(":hashCode", tempTag.hashCode());\r
 192                 query.bindValue(":name", tempTag.getName());\r
 193                 query.bindValue(":isDirty", isDirty);\r
 194                 \r
 195                 check = query.exec();\r
 196                 if (!check) {\r
 197                         logger.log(logger.MEDIUM, "Tag Table insert failed.");\r
 198                         logger.log(logger.MEDIUM, query.lastError());\r
 199                 }\r
 200         }\r
 201         // Update a tag's parent\r
 202         public void updateTagParent(String guid, String parentGuid) {\r
 203                 boolean check;\r
 204                 \r
 205         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 206                 check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");\r
 207                 if (!check) {\r
 208                         logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");\r
 209                         logger.log(logger.EXTREME, query.lastError());\r
 210                 }\r
 211 \r
 212                 query.bindValue(":parentGuid", parentGuid);\r
 213                 query.bindValue(":guid", guid);\r
 214                 \r
 215                 check = query.exec();\r
 216                 if (!check) {\r
 217                         logger.log(logger.MEDIUM, "Tag parent update failed.");\r
 218                         logger.log(logger.MEDIUM, query.lastError());\r
 219                 }\r
 220         }\r
 221         //Save tags from Evernote\r
 222         public void saveTags(List<Tag> tags) {\r
 223                 Tag tempTag;\r
 224                 for (int i=0; i<tags.size(); i++) {\r
 225                         tempTag = tags.get(i);\r
 226                         addTag(tempTag, false);\r
 227                 }               \r
 228         }\r
 229         // Update a tag sequence number\r
 230         public void updateTagSequence(String guid, int sequence) {\r
 231                 boolean check;\r
 232         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 233                 check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");\r
 234                 query.bindValue(":sequence", sequence);\r
 235                 query.bindValue(":guid", guid);\r
 236                 \r
 237                 query.exec();\r
 238                 if (!check) {\r
 239                         logger.log(logger.MEDIUM, "Tag sequence update failed.");\r
 240                         logger.log(logger.MEDIUM, query.lastError());\r
 241                 }\r
 242                 \r
 243         }\r
 244         // Update a tag sequence number\r
 245         public void updateTagGuid(String oldGuid, String newGuid) {\r
 246                 boolean check;\r
 247                 \r
 248         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 249                 check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");\r
 250                 query.bindValue(":newGuid", newGuid);\r
 251                 query.bindValue(":oldGuid", oldGuid);\r
 252                 query.exec();\r
 253                 if (!check) {\r
 254                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
 255                         logger.log(logger.MEDIUM, query.lastError());\r
 256                 }\r
 257                 \r
 258                 check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");\r
 259                 query.bindValue(":newGuid", newGuid);\r
 260                 query.bindValue(":oldGuid", oldGuid);\r
 261                 query.exec();\r
 262                 if (!check) {\r
 263                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
 264                         logger.log(logger.MEDIUM, query.lastError());\r
 265                 }\r
 266                 \r
 267                 check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");\r
 268                 query.bindValue(":newGuid", newGuid);\r
 269                 query.bindValue(":oldGuid", oldGuid);\r
 270                 query.exec();\r
 271                 if (!check) {\r
 272                         logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");\r
 273                         logger.log(logger.MEDIUM, query.lastError());\r
 274                 }\r
 275                 \r
 276         }\r
 277         // Get dirty tags\r
 278         public List<Tag> getDirty() {\r
 279                 Tag tempTag;\r
 280                 List<Tag> index = new ArrayList<Tag>();\r
 281                 boolean check;\r
 282                                                 \r
 283                 \r
 284         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 285                                                 \r
 286                 check = query.exec("Select guid, parentGuid, sequence, name"\r
 287                                 +" from Tag where isDirty = true");\r
 288                 if (!check)\r
 289                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 290                 while (query.next()) {\r
 291                         tempTag = new Tag();\r
 292                         tempTag.setGuid(query.valueString(0));\r
 293                         tempTag.setParentGuid(query.valueString(1));\r
 294                         int sequence = new Integer(query.valueString(2)).intValue();\r
 295                         tempTag.setUpdateSequenceNum(sequence);\r
 296                         tempTag.setName(query.valueString(3));\r
 297                         if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))\r
 298                                 tempTag.setParentGuid(null);\r
 299                         index.add(tempTag); \r
 300                 }\r
 301                 return index;\r
 302         }\r
 303         // Find a guid based upon the name\r
 304         public String findTagByName(String name) {\r
 305                 \r
 306                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 307                 \r
 308                 query.prepare("Select guid from tag where name=:name");\r
 309                 query.bindValue(":name", name);\r
 310                 if (!query.exec())\r
 311                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 312                 String val = null;\r
 313                 if (query.next())\r
 314                         val = query.valueString(0);\r
 315                 return val;\r
 316         }\r
 317         // given a guid, does the tag exist\r
 318         public boolean exists(String guid) {\r
 319                 \r
 320                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 321                 \r
 322                 query.prepare("Select guid from tag where guid=:guid");\r
 323                 query.bindValue(":guid", guid);\r
 324                 if (!query.exec())\r
 325                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
 326                 boolean retval = query.next();\r
 327                 return retval;\r
 328         }\r
 329         // This is a convience method to check if a tag exists & update/create based upon it\r
 330         public void syncTag(Tag tag, boolean isDirty) {\r
 331                 if (exists(tag.getGuid()))\r
 332                         updateTag(tag, isDirty);\r
 333                 else\r
 334                         addTag(tag, isDirty);\r
 335         }\r
 336         public void  resetDirtyFlag(String guid) {\r
 337                 \r
 338                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 339                 \r
 340                 query.prepare("Update tag set isdirty=false where guid=:guid");\r
 341                 query.bindValue(":guid", guid);\r
 342                 if (!query.exec())\r
 343                         logger.log(logger.EXTREME, "Error resetting tag dirty field.");\r
 344         }\r
 345         \r
 346         \r
 347         // Get the custom icon\r
 348         public QIcon getIcon(String guid) {\r
 349                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 350                 \r
 351                 if (!query.prepare("Select icon from tag where guid=:guid"))\r
 352                         logger.log(logger.EXTREME, "Error preparing tag icon select.");\r
 353                 query.bindValue(":guid", guid);\r
 354                 if (!query.exec())\r
 355                         logger.log(logger.EXTREME, "Error finding tag icon.");\r
 356                 if (!query.next() || query.getBlob(0) == null)\r
 357                         return null;\r
 358                 \r
 359                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 360                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 361                 return icon;\r
 362         }\r
 363         // Set the custom icon\r
 364         public void setIcon(String guid, QIcon icon, String type) {\r
 365                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 366                 if (icon == null) {\r
 367                         if (!query.prepare("update tag set icon=null where guid=:guid"))\r
 368                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
 369                 } else {\r
 370                         if (!query.prepare("update tag set icon=:icon where guid=:guid"))\r
 371                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
 372                         QBuffer buffer = new QBuffer();\r
 373                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 374                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 375                         return;\r
 376                 }\r
 377                 QPixmap p = icon.pixmap(32, 32);\r
 378                 QImage i = p.toImage();\r
 379                 i.save(buffer, type.toUpperCase());\r
 380                 buffer.close();\r
 381                 QByteArray b = new QByteArray(buffer.buffer());\r
 382                 if (!b.isNull() && !b.isEmpty())\r
 383                         query.bindValue(":icon", b.toByteArray());\r
 384                 else\r
 385                         return;\r
 386                 }\r
 387                 query.bindValue(":guid", guid);\r
 388                 if (!query.exec()) \r
 389                         logger.log(logger.LOW, "Error setting tag icon. " +query.lastError());\r
 390         }\r
 391 \r
 392         // Get a list of all icons\r
 393         public HashMap<String, QIcon> getAllIcons() {\r
 394                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 395                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 396         \r
 397                 if (!query.exec("SELECT guid, icon from tag"))\r
 398                         logger.log(logger.EXTREME, "Error executing SavedSearch getAllIcons select.");\r
 399                 while (query.next()) {\r
 400                         if (query.getBlob(1) != null) {\r
 401                                 String guid = query.valueString(0);\r
 402                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 403                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 404                                 values.put(guid, icon);\r
 405                         }\r
 406                 }\r
 407                 return values;\r
 408         }\r
 409 \r
 410 }\r