src/cx/fbn/nevernote/sql/NoteResourceTable.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 import java.text.SimpleDateFormat;\r
  23 import java.util.ArrayList;\r
  24 import java.util.List;\r
  25 \r
  26 import com.evernote.edam.type.Data;\r
  27 import com.evernote.edam.type.Resource;\r
  28 import com.evernote.edam.type.ResourceAttributes;\r
  29 import com.trolltech.qt.core.QByteArray;\r
  30 \r
  31 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  32 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  33 \r
  34 \r
  35 \r
  36 public class NoteResourceTable  {\r
  37         /**\r
  38          * \r
  39          */\r
  40 //      private static final long serialVersionUID = 1L;\r
  41         private final ApplicationLogger                 logger;\r
  42         private final DatabaseConnection                db;     \r
  43         \r
  44         // Constructor\r
  45         public NoteResourceTable(ApplicationLogger l, DatabaseConnection d) {\r
  46                 logger = l;\r
  47                 db = d;\r
  48         }\r
  49         // Create the table\r
  50         public void createTable() {\r
  51                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
  52         // Create the NoteResource table\r
  53         logger.log(logger.HIGH, "Creating table NoteResource...");\r
  54         if (!query.exec("Create table NoteResources (guid varchar primary key, " +\r
  55                         "noteGuid varchar, updateSequenceNumber integer, dataHash varchar, "+\r
  56                         "dataSize integer, dataBinary blob, "+\r
  57                         "mime varchar, width integer, height integer, duration integer, "+\r
  58                         "active integer, recognitionHash varchar, recognitionSize integer, " +\r
  59                         "recognitionBinary varchar, attributeSourceUrl varchar, attributeTimestamp timestamp, " +\r
  60                         "attributeLatitude double, attributeLongitude double, "+\r
  61                         "attributeAltitude double, attributeCameraMake varchar, attributeCameraModel varchar, "\r
  62                         +"attributeClientWillIndex varchar, attributeRecoType varchar, attributeFileName varchar,"+\r
  63                         "attributeAttachment boolean, isDirty boolean, indexNeeded boolean)"))\r
  64                 logger.log(logger.HIGH, "Table NoteResource creation FAILED!!!"); \r
  65         if (!query.exec("CREATE INDEX unindexed_resources on noteresources (indexneeded desc, guid);"))\r
  66                 logger.log(logger.HIGH, "Noteresources unindexed_resources index creation FAILED!!!");\r
  67         if (!query.exec("CREATE INDEX resources_dataheshhex on noteresources (datahash, guid);"))\r
  68                 logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r
  69         if (!query.exec("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);"))\r
  70                 logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r
  71         }\r
  72         // Drop the table\r
  73         public void dropTable() {               \r
  74                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
  75                 query.exec("Drop table NoteResources");\r
  76         }\r
  77         // Reset the dirty flag\r
  78         public void  resetDirtyFlag(String guid) {\r
  79                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
  80                 \r
  81                 query.prepare("Update noteresources set isdirty=false where guid=:guid");\r
  82                 query.bindValue(":guid", guid);\r
  83                 if (!query.exec())\r
  84                         logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError());\r
  85                 else\r
  86                         query.exec("commit");\r
  87         }\r
  88         // Set if the resource should be indexed\r
  89         public void  setIndexNeeded(String guid, Boolean indexNeeded) {\r
  90                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());            \r
  91                 query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid");\r
  92                 query.bindValue(":needed", indexNeeded);\r
  93                 query.bindValue(":guid", guid);\r
  94                 if (!query.exec())\r
  95                         logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError());\r
  96                 else\r
  97                         query.exec("commit");\r
  98         }\r
  99         // get any unindexed resource\r
 100         public List<String> getNextUnindexed(int limit) {\r
 101                 List<String> guids = new ArrayList<String>();\r
 102         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 103                                         \r
 104                 if (!query.exec("Select guid from NoteResources where indexNeeded = true limit " +limit))\r
 105                         logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getNextUnindexed(): " +query.lastError());\r
 106 \r
 107                 // Get a list of the notes\r
 108                 String guid;\r
 109                 while (query.next()) {\r
 110                         guid = new String();\r
 111                         guid = query.valueString(0);\r
 112                         guids.add(guid);\r
 113                 }       \r
 114                 return guids;   \r
 115         }\r
 116         // get any unindexed resource\r
 117         public List<String> getUnindexed() {\r
 118                 List<String> guids = new ArrayList<String>();\r
 119         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 120                                         \r
 121                 if (!query.exec("Select guid from NoteResources where indexNeeded = true"))\r
 122                         logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getUnindexed(): " +query.lastError());\r
 123 \r
 124                 // Get a list of the notes\r
 125                 String guid;\r
 126                 while (query.next()) {\r
 127                         guid = new String();\r
 128                         guid = query.valueString(0);\r
 129                         guids.add(guid);\r
 130                 }       \r
 131                 return guids;   \r
 132         }\r
 133 \r
 134         public List<String> getAll() {\r
 135                 List<String> guids = new ArrayList<String>();\r
 136                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 137                 \r
 138                 query.prepare("Select guid from noteresources;");\r
 139                 if (!query.exec())\r
 140                         logger.log(logger.EXTREME, "Error getting all note resource guids. " +query.lastError());\r
 141                 \r
 142                 while (query.next()) {\r
 143                         guids.add(query.valueString(0));\r
 144                 }\r
 145                 return guids;\r
 146         }\r
 147         \r
 148         public List<String> findInkNotes() {\r
 149                 List<String> guids = new ArrayList<String>();\r
 150                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 151                 \r
 152                 query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'");\r
 153                 if (!query.exec())\r
 154                         logger.log(logger.EXTREME, "Error searching for ink notes. " +query.lastError());\r
 155                 \r
 156                 while (query.next()) {\r
 157                         guids.add(query.valueString(0));\r
 158                 }\r
 159                 return guids;\r
 160         }\r
 161         \r
 162         public void saveNoteResource(Resource r, boolean isDirty) {\r
 163                 logger.log(logger.HIGH, "Entering saveNoteResources: isDirty " +isDirty);\r
 164                 boolean check;\r
 165                 logger.log(logger.HIGH, "Note: " +r.getNoteGuid());\r
 166                 logger.log(logger.HIGH, "Resource: " +r.getGuid());\r
 167                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 168                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
 169                 \r
 170                 query.prepare("Insert Into NoteResources ("\r
 171                                 +"guid, noteGuid, dataHash, dataSize, dataBinary, updateSequenceNumber, "\r
 172                                 +"mime, width, height, duration, active, recognitionHash, "                             \r
 173                                 +"recognitionSize, recognitionBinary, attributeSourceUrl, attributeTimestamp, "\r
 174                                 +"attributeLatitude, attributeLongitude, attributeAltitude, attributeCameraMake, "\r
 175                                 +"attributeCameraModel, "\r
 176                                 +"attributeClientWillIndex, attributeRecoType, attributeFileName, attributeAttachment, isDirty, "\r
 177                                 +"indexNeeded) Values("\r
 178                                 +":guid, :noteGuid, :dataHash,:dataSize, :dataBody, :updateSequenceNumber, "\r
 179                                 +":mime, :width, :height, :duration, :active, :recognitionHash, "                               \r
 180                                 +":recognitionSize, :recognitionBody, :attributeSourceUrl, :attributeTimestamp, "\r
 181                                 +":attributeLatitude, :attributeLongitude, :attributeAltitude, :attributeCameraMake, "\r
 182                                 +":attributeCameraModel, "\r
 183                                 +":attributeClientWillIndex, :attributeRecoType, :attributeFileName, :attributeAttachment, "\r
 184                                 +":isDirty, true)");\r
 185         \r
 186                         query.bindValue(":guid", r.getGuid());\r
 187                         query.bindValue(":noteGuid", r.getNoteGuid());\r
 188                         if (r.getData() != null) {\r
 189                                 query.bindValue(":dataHash", byteArrayToHexString(r.getData().getBodyHash()));\r
 190 //                              query.bindValue(":dataHash", "c0369123fe9871d675ae456fd056ba33");\r
 191                                 query.bindValue(":dataSize", r.getData().getSize());\r
 192                                 query.bindBlob(":dataBody", r.getData().getBody());\r
 193                         }\r
 194                         query.bindValue(":updateSequenceNumber", r.getUpdateSequenceNum());\r
 195                         query.bindValue(":mime", r.getMime());\r
 196                         query.bindValue(":width", new Integer(r.getWidth()));\r
 197                         query.bindValue(":height", new Integer(r.getHeight()));\r
 198                         query.bindValue(":duration", new Integer(r.getDuration()));\r
 199                         query.bindValue(":active", r.isActive());\r
 200                         if (r.getRecognition() != null) {\r
 201                                 query.bindValue(":recognitionHash", r.getRecognition().getBodyHash());\r
 202                                 query.bindValue(":recognitionSize", r.getRecognition().getSize());\r
 203                                 if (r.getRecognition().getBody() != null)\r
 204                                         query.bindValue(":recognitionBody", new String(r.getRecognition().getBody()));\r
 205                                 else\r
 206                                         query.bindValue(":recognitionBody", "");\r
 207                         } else {\r
 208                                 query.bindValue(":recognitionHash", "");\r
 209                                 query.bindValue(":recognitionSize", 0);\r
 210                                 query.bindValue(":recognitionBody", "");\r
 211                         }\r
 212                         if (r.getAttributes() != null) {\r
 213                                 query.bindValue(":attributeSourceUrl", r.getAttributes().getSourceURL());\r
 214                                 StringBuilder ts = new StringBuilder(simple.format(r.getAttributes().getTimestamp()));\r
 215                                 query.bindValue(":attributeTimestamp", ts.toString());\r
 216                                 query.bindValue(":attributeLatitude", r.getAttributes().getLatitude());\r
 217                                 query.bindValue(":attributeLongitude", r.getAttributes().getLongitude());\r
 218                                 query.bindValue(":attributeAltitude", r.getAttributes().getAltitude());\r
 219                                 query.bindValue(":attributeCameraMake", r.getAttributes().getCameraMake());\r
 220                                 query.bindValue(":attributeCameraModel", r.getAttributes().getCameraModel());\r
 221                                 query.bindValue(":attributeClientWillIndex", r.getAttributes().isClientWillIndex());\r
 222                                 query.bindValue(":attributeRecoType", r.getAttributes().getRecoType());\r
 223                                 query.bindValue(":attributeFileName", r.getAttributes().getFileName());\r
 224                                 query.bindValue(":attributeAttachment", r.getAttributes().isAttachment());                      \r
 225                         } \r
 226                         query.bindValue(":isDirty", isDirty);\r
 227                                                 \r
 228                         check = query.exec();\r
 229                         if (!check) {\r
 230                                 logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");             \r
 231                                 logger.log(logger.MEDIUM, query.lastError());\r
 232                         } else\r
 233                                 query.exec("commit");\r
 234                         \r
 235                                                 \r
 236                         logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources");\r
 237         }\r
 238         // delete an old resource\r
 239         public void expungeNoteResource(String guid) {\r
 240                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 241                 query.prepare("delete from NoteResources where guid=:guid");\r
 242                 query.bindValue(":guid", guid);\r
 243                 query.exec();\r
 244                 query.exec("commit");\r
 245                 \r
 246                 NSqlQuery query2 = new NSqlQuery(db.getConnection());\r
 247                 query2.prepare("Delete from InkImages where guid=:guid");\r
 248                 query2.bindValue(":guid", guid);\r
 249                 query2.exec();\r
 250                 query2.exec("commit");\r
 251 \r
 252         }\r
 253 \r
 254         \r
 255         // Get a note resource from the database by it's hash value\r
 256         public String getNoteResourceGuidByHashHex(String noteGuid, String hash) {\r
 257                 logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
 258 \r
 259                 boolean check;\r
 260                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 261                 \r
 262                 check = query.prepare("Select guid from NoteResources " +\r
 263                                         "where noteGuid=:noteGuid and dataHash=:hash");\r
 264                 if (check)\r
 265                         logger.log(logger.EXTREME, "NoteResource SQL select prepare was successful.");\r
 266                 else {\r
 267                         logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
 268                         logger.log(logger.MEDIUM, query.lastError());\r
 269                 }\r
 270                 query.bindValue(":noteGuid", noteGuid);\r
 271                 query.bindValue(":hash", hash);\r
 272         \r
 273                 check = query.exec();\r
 274                 if (!check)      {\r
 275                         logger.log(logger.MEDIUM, "dbRunner.getNoteResourceGuidByHashHex Select failed." +\r
 276                                         "Note Guid:" +noteGuid+\r
 277                                         "Data Body Hash:" +hash);               \r
 278                         logger.log(logger.MEDIUM, query.lastError());\r
 279                 }\r
 280                 if (!query.next()) {\r
 281                         logger.log(logger.MEDIUM, "Note Resource not found.");\r
 282                         return null;\r
 283                 }\r
 284                 return query.valueString(0);\r
 285         }\r
 286 \r
 287         // Get a note resource from the database by it's hash value\r
 288         public Resource getNoteResourceDataBodyByHashHex(String noteGuid, String hash) {\r
 289                 logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
 290 \r
 291                 boolean check;\r
 292                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 293                 \r
 294                 check = query.prepare("Select guid, mime, from NoteResources " +\r
 295                                         "where noteGuid=:noteGuid and dataHash=:hash");\r
 296                 if (!check) {\r
 297                         logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
 298                         logger.log(logger.MEDIUM, query.lastError());\r
 299                 }\r
 300                 query.bindValue(":noteGuid", noteGuid);\r
 301                 query.bindValue(":hash", hash);\r
 302         \r
 303                 if (!query.exec()) {\r
 304                         logger.log(logger.MEDIUM, "NoteResource Select failed." +\r
 305                                         "Note Guid:" +noteGuid+\r
 306                                         "Data Body Hash:" +hash);               \r
 307                         logger.log(logger.MEDIUM, query.lastError());\r
 308                 }\r
 309                 if (!query.next()) {\r
 310                         logger.log(logger.MEDIUM, "Note Resource not found.");\r
 311                         return null;\r
 312                 }\r
 313                 \r
 314                 Resource r = new Resource();\r
 315                 r.setGuid(query.valueString(0));\r
 316                 r.setMime(query.valueString(1));\r
 317                 \r
 318                 NSqlQuery binary = new NSqlQuery(db.getResourceConnection());\r
 319                 if (!binary.prepare("Select databinary from NoteResources " +\r
 320                                         "where guid=:guid")) {\r
 321                         logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");\r
 322                         logger.log(logger.MEDIUM, binary.lastError());\r
 323                 }\r
 324                 \r
 325                 if (!binary.exec()) {\r
 326                         logger.log(logger.MEDIUM, "NoteResources Binary Select failed." +\r
 327                                         "Note Guid:" +noteGuid+\r
 328                                         "Data Body Hash:" +hash);               \r
 329                         logger.log(logger.MEDIUM, binary.lastError());\r
 330                 }\r
 331                 if (!binary.next()) {\r
 332                         logger.log(logger.MEDIUM, "Note Resource Binary not found.");\r
 333                         return null;\r
 334                 }\r
 335                 \r
 336                 Data d = new Data();\r
 337                 r.setData(d);\r
 338                 d.setBody(binary.valueString(0).getBytes());\r
 339                 logger.log(logger.HIGH, "Leaving DBRunner.getNoteResourceDataBodyByHash");\r
 340                 return r;\r
 341         }\r
 342 \r
 343         \r
 344         // Get a note's resourcesby Guid\r
 345         public Resource getNoteResource(String guid, boolean withBinary) {\r
 346                 if (guid == null)\r
 347                         return null;\r
 348                 \r
 349                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 350                 String queryString;\r
 351                 queryString = new String("Select guid, noteGuid, mime, width, height, duration, "\r
 352                                 +"active, updateSequenceNumber, dataHash, dataSize, "\r
 353                                 +"recognitionHash, recognitionSize, "\r
 354                                 +"attributeLatitude, attributeLongitude, attributeAltitude, "\r
 355                                 +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, "\r
 356                                 +"attributeRecoType, attributeFileName, attributeAttachment, attributeSourceUrl "\r
 357                                 +" from NoteResources where guid=:guid");\r
 358 \r
 359                 \r
 360                 query.prepare(queryString);\r
 361                 \r
 362                 query.bindValue(":guid", guid);\r
 363                 if (!query.exec()) {\r
 364                         logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
 365                         logger.log(logger.MEDIUM, query.lastError());\r
 366                         return null;\r
 367                 }\r
 368                 Resource r = null;\r
 369                 if (query.next()) {\r
 370                                                                         \r
 371                         r = new Resource();\r
 372                         r.setGuid(query.valueString(0));        // Resource Guid\r
 373                         r.setNoteGuid(query.valueString(1));   // note Guid\r
 374                         r.setMime(query.valueString(2));       // Mime Type\r
 375                         r.setWidth(new Short(query.valueString(3)));  // Width\r
 376                         r.setHeight(new Short(query.valueString(4)));  // Height\r
 377                         r.setDuration(new Short(query.valueString(5)));  // Duration\r
 378                         r.setActive(new Boolean(query.valueString(6)));  // active\r
 379                         r.setUpdateSequenceNum(new Integer(query.valueString(7)));  // update sequence number\r
 380                         \r
 381                         Data d = new Data();\r
 382                         byte[] h = query.valueString(8).getBytes();    // data hash\r
 383                         QByteArray hData = new QByteArray(h);\r
 384                         QByteArray bData = new QByteArray(QByteArray.fromHex(hData));\r
 385                         d.setBodyHash(bData.toByteArray());\r
 386                         d.setSize(new Integer(query.valueString(9)));\r
 387                         r.setData(d);\r
 388                         \r
 389                         Data rec = new Data();\r
 390                         if (query.valueObject(10) != null)\r
 391                                 rec.setBodyHash(query.valueString(10).getBytes());   // Recognition Hash\r
 392                         if (query.valueObject(11) != null)\r
 393                                 rec.setSize(new Integer(query.valueString(11)));\r
 394                         else\r
 395                                 rec.setSize(0);\r
 396                         r.setRecognition(rec);\r
 397 \r
 398                         ResourceAttributes a = new ResourceAttributes();\r
 399                         if (!query.valueString(12).equals(""))              // Latitude\r
 400                                 a.setLatitude(new Float(query.valueString(12)));\r
 401                         if (!query.valueString(13).equals(""))              // Longitude\r
 402                                 a.setLongitude(new Float(query.valueString(13)));\r
 403                         if (!query.valueString(14).equals(""))              // Altitude\r
 404                                 a.setAltitude(new Float(query.valueString(14)));\r
 405                         a.setCameraMake(stringValue(query.valueString(15)));              // Camera Make\r
 406                         a.setCameraModel(stringValue(query.valueString(16)));\r
 407                         a.setClientWillIndex(booleanValue(query.valueString(17).toString(),false));  // Camera Model\r
 408                         a.setRecoType(stringValue(query.valueString(18)));                 // Recognition Type\r
 409                         a.setFileName(stringValue(query.valueString(19)));                  // File Name\r
 410                         a.setAttachment(booleanValue(query.valueString(20).toString(),false));\r
 411                         a.setSourceURL(query.valueString(21));\r
 412                         r.setAttributes(a);\r
 413                 \r
 414                         if (withBinary) {\r
 415                             \r
 416                                 query.prepare("Select dataBinary from NoteResources where guid=:guid");\r
 417                                 query.bindValue(":guid", r.getGuid());\r
 418                                 query.exec();\r
 419                                 if (query.next()) {\r
 420                                         byte[] b = query.getBlob(0);\r
 421                                         r.getData().setBody(b);\r
 422                                 }\r
 423                         } \r
 424                 }\r
 425                 return r;\r
 426         }\r
 427         \r
 428         \r
 429         // Get a note's resourcesby Guid\r
 430         public List<Resource> getNoteResources(String noteGuid, boolean withBinary) {\r
 431                 if (noteGuid == null)\r
 432                         return null;\r
 433                 List<Resource> res = new ArrayList<Resource>();\r
 434                 \r
 435                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 436                 query.prepare("Select guid"\r
 437                                 +" from NoteResources where noteGuid = :noteGuid");\r
 438                 query.bindValue(":noteGuid", noteGuid);\r
 439                 if (!query.exec()) {\r
 440                         logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
 441                         logger.log(logger.MEDIUM, query.lastError());\r
 442                         return null;\r
 443                 }\r
 444                 while (query.next()) {\r
 445                         String guid = (query.valueString(0));\r
 446                         res.add(getNoteResource(guid, withBinary));\r
 447                 }       \r
 448                 return res;\r
 449         }\r
 450         // Get all of a note's recognition data by the note guid\r
 451         public List<Resource> getNoteResourcesRecognition(String noteGuid) {\r
 452                 if (noteGuid == null)\r
 453                         return null;\r
 454                 boolean check;\r
 455                 List<Resource> res = new ArrayList<Resource>();\r
 456                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 457                 check = query.prepare("Select "\r
 458                                 +"recognitionHash, recognitionSize, recognitionBinary "\r
 459                                 +" from NoteResources where noteGuid=:guid");\r
 460                 if (!check) {\r
 461                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
 462                         logger.log(logger.MEDIUM, query.lastError());\r
 463                         return null;\r
 464                 }\r
 465                 query.bindValue(":guid", noteGuid);\r
 466                 if (!check) {\r
 467                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
 468                         logger.log(logger.MEDIUM, query.lastError());\r
 469                         return null;\r
 470                 }\r
 471                 while (query.next()) {  \r
 472                         Resource r = new Resource();            \r
 473                         res.add(r);\r
 474                         Data rec = new Data();\r
 475                         rec.setBodyHash(query.valueString(0).getBytes());\r
 476                         String x = new String(query.valueString(1));\r
 477                         if (!x.equals("")) {\r
 478                                 rec.setSize(new Integer(x));\r
 479                                 rec.setBody(query.valueString(2).getBytes());\r
 480                         } else\r
 481                                 rec.setSize(0);\r
 482                         r.setRecognition(rec);\r
 483                 }       \r
 484                 return res;\r
 485         }\r
 486         \r
 487         \r
 488         // Get a note's recognition data by it's guid.\r
 489         public Resource getNoteResourceRecognition(String guid) {\r
 490                 if (guid == null)\r
 491                         return null;\r
 492                 boolean check;\r
 493                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 494                 check = query.prepare("Select "\r
 495                                 +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
 496                                 +" from NoteResources where guid=:guid");\r
 497                 if (!check) {\r
 498                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
 499                         logger.log(logger.MEDIUM, query.lastError());\r
 500                         return null;\r
 501                 }\r
 502                 query.bindValue(":guid", guid);\r
 503                 query.exec();\r
 504                 if (!check) {\r
 505                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
 506                         logger.log(logger.MEDIUM, query.lastError());\r
 507                         return null;\r
 508                 }\r
 509                 Resource r = null;\r
 510                 while (query.next()) {\r
 511                                                                         \r
 512                         r = new Resource();             \r
 513                         Data rec = new Data();\r
 514                         rec.setBodyHash(query.valueString(0).getBytes());\r
 515                         String x = new String(query.valueString(1));\r
 516                         if (!x.equals("")) {\r
 517                                 rec.setSize(new Integer(x));\r
 518                                 rec.setBody(query.valueString(2).getBytes());\r
 519                         } else\r
 520                                 rec.setSize(0);\r
 521                         r.setRecognition(rec);\r
 522                         r.setNoteGuid(query.valueString(3));\r
 523                 }       \r
 524                 return r;\r
 525         }\r
 526 \r
 527         // Save Note Resource\r
 528         public void updateNoteResource(Resource r, boolean isDirty) {\r
 529                 logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r
 530                 expungeNoteResource(r.getGuid());\r
 531                 saveNoteResource(r, isDirty);\r
 532                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource");\r
 533         }\r
 534         // Update note resource GUID\r
 535         public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
 536                 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
 537                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 538                 query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");\r
 539                 query.bindValue(":newGuid", newGuid);\r
 540                 query.bindValue(":isDirty", isDirty);\r
 541                 query.bindValue(":oldGuid", oldGuid);\r
 542                 query.exec();\r
 543                 query.exec("commit");\r
 544                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
 545         }\r
 546         // Update note resource GUID\r
 547         public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
 548                 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
 549                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 550                 query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");\r
 551                 query.bindValue(":isDirty", isDirty);\r
 552                 query.bindValue(":guid", guid);\r
 553                 query.exec();\r
 554                 query.exec("commit");\r
 555                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
 556         }\r
 557         \r
 558         // Drop the table\r
 559         public void reindexAll() {              \r
 560                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 561                 query.exec("Update NoteResources set indexneeded=true");\r
 562                 query.exec("commit");\r
 563         }\r
 564         // Count attachments\r
 565         public int getResourceCount() {\r
 566         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 567                 query.exec("select count(*) from noteresources");\r
 568                 query.next(); \r
 569                 int returnValue = new Integer(query.valueString(0));\r
 570                 return returnValue;\r
 571         }\r
 572         //\r
 573         // Count unindexed notes\r
 574         public int getUnindexedCount() {\r
 575         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 576                 query.exec("select count(*) from noteresources where indexneeded=true");\r
 577                 query.next(); \r
 578                 int returnValue = new Integer(query.valueString(0));\r
 579                 return returnValue;\r
 580         }\r
 581         \r
 582         //********************************************\r
 583         //** Utility Functions\r
 584         //********************************************\r
 585         // Convert a byte array to a hex string\r
 586         private static String byteArrayToHexString(byte data[]) {\r
 587                 StringBuffer buf = new StringBuffer();\r
 588             for (byte element : data) {\r
 589                 int halfbyte = (element >>> 4) & 0x0F;\r
 590                 int two_halfs = 0;\r
 591                 do {\r
 592                         if ((0 <= halfbyte) && (halfbyte <= 9))\r
 593                                buf.append((char) ('0' + halfbyte));\r
 594                            else\r
 595                                 buf.append((char) ('a' + (halfbyte - 10)));\r
 596                         halfbyte = element & 0x0F;\r
 597                 } while(two_halfs++ < 1);\r
 598             }\r
 599             return buf.toString();              \r
 600         }\r
 601 \r
 602         \r
 603         private String stringValue(Object value) {\r
 604                 if (value != null && value.toString() != null) \r
 605                         return value.toString();\r
 606                 else\r
 607                         return null;\r
 608         }\r
 609         \r
 610         private  boolean booleanValue(Object value, boolean unknown) {\r
 611                 if (value != null && value.toString() != null) {\r
 612                         try {\r
 613                                 if ((Integer)value > 0)\r
 614                                         return true;\r
 615                                 else\r
 616                                         return false;\r
 617                         } catch (java.lang.ClassCastException e) {\r
 618                                 try {\r
 619                                         String stringValue = (String)value;\r
 620                                         if (stringValue.equalsIgnoreCase("true"))\r
 621                                                 return true;\r
 622                                         else \r
 623                                                 return false;\r
 624                                 } catch (java.lang.ClassCastException e1) { \r
 625                                         return unknown;\r
 626                                 }\r
 627                         }\r
 628                 }\r
 629                 else\r
 630                         return unknown;\r
 631         }\r
 632 \r
 633         // Update note source url. \r
 634         public void updateNoteSourceUrl(String guid, String url, boolean isDirty) {\r
 635                 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteSourceUrl()");\r
 636                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 637                 query.prepare("update NoteResources set attributesourceurl=:url, isDirty=:isDirty where guid=:guid");\r
 638                 query.bindValue(":guid", guid);\r
 639                 query.bindValue(":isDirty", isDirty);\r
 640                 query.bindValue(":url", url);\r
 641                 query.exec();\r
 642                 query.exec("commit");\r
 643                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteSourceUrl()");\r
 644         }\r
 645         \r
 646         // Get note source\r
 647         public String getNoteSourceUrl(String guid) {\r
 648                 logger.log(logger.HIGH, "Entering RNoteResourceTable.getNoteSourceUrl()");\r
 649                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 650                 query.prepare("Select attributesourceurl from noteresources where guid=:guid");\r
 651                 query.bindValue(":guid", guid);\r
 652                 query.exec();\r
 653                 if (query.next()) {\r
 654                         logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl()");\r
 655                         return query.valueString(0);\r
 656                 }\r
 657                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl() - no value found");\r
 658                 return null;\r
 659         }\r
 660         \r
 661         // Get note source\r
 662         public List<String> getDistinctNoteGuids() {\r
 663                 logger.log(logger.HIGH, "Entering NoteResourceTable.getDistinctNoteGuids()");\r
 664                 List<String> guids = new ArrayList<String>();\r
 665                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 666                 query.exec("select distinct noteguid from noteresources");\r
 667                 if (query.next()) {\r
 668                         guids.add(query.valueString(0));\r
 669                 }\r
 670                 logger.log(logger.HIGH, "Leaving NoteResourceTable.getDistinctNoteGuids()");\r
 671                 return guids;\r
 672         }\r
 673 \r
 674         public void resetAllDirty() {\r
 675                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 676                 query.exec("update noteresources set isdirty=false");\r
 677         }\r
 678         \r
 679         // リソーステキストを更新\r
 680         public void updateResourceText(String guid, String text) {\r
 681                 logger.log(logger.HIGH, "Entering NoteResourceTable.updateResourceText");\r
 682                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
 683                 boolean check = query.prepare("Update noteResources set resourceText=:resourceText where guid=:guid");\r
 684                 if (!check) {\r
 685                         logger.log(logger.EXTREME, "Update resourceText sql prepare has failed.");\r
 686                         logger.log(logger.MEDIUM, query.lastError());\r
 687                 }\r
 688                 \r
 689                 query.bindValue(":resourceText", text);\r
 690                 query.bindValue(":guid", guid);\r
 691                 \r
 692                 check = query.exec();\r
 693                 if (!check) {\r
 694                         logger.log(logger.EXTREME, "Update resourceText has failed.");\r
 695                         logger.log(logger.MEDIUM, query.lastError());\r
 696                 }\r
 697                 logger.log(logger.HIGH, "Leaving NoteResourceTable.updateResourceText");\r
 698                 query.exec();\r
 699         }\r
 700 }\r
 701 \r