src/cx/fbn/nevernote/sql/LinkedNotebookTable.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.List;\r
  25 \r
  26 import com.evernote.edam.type.LinkedNotebook;\r
  27 \r
  28 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  29 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  30 \r
  31 public class LinkedNotebookTable {\r
  32         \r
  33         private final ApplicationLogger                 logger;\r
  34         DatabaseConnection                                                      db;\r
  35         \r
  36         // Constructor\r
  37         public LinkedNotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
  38                 logger = l;\r
  39                 db = d;\r
  40         }\r
  41         // Create the table\r
  42         public void createTable() {\r
  43                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  44         logger.log(logger.HIGH, "Creating table LinkedNotebook...");\r
  45         if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " +\r
  46                         "shareName VarChar, " +\r
  47                         "username VarChar, "+\r
  48                         "shardID VarChar, " +\r
  49                         "shareKey VarChar, " +\r
  50                         "uri VarChar, " +\r
  51                         "updateSequenceNumber Long," +\r
  52                         "lastSequenceNumber Integer," +\r
  53                         "lastSequenceDate Long," +\r
  54                         "notebookGuid VarChar," +\r
  55                         "isDirty boolean)"))                            \r
  56                 logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");   \r
  57         }\r
  58         // Drop the table\r
  59         public void dropTable() {\r
  60                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  61                 query.exec("Drop table LinkedNotebook");\r
  62         }\r
  63         // Save an individual notebook\r
  64         public void addNotebook(LinkedNotebook tempNotebook,  boolean isDirty) {\r
  65                 boolean check;\r
  66                 \r
  67         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  68                 check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username,  "\r
  69                                 +"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, "\r
  70                                 + "lastSequenceDate, notebookGuid) "   \r
  71                                 + " Values("\r
  72                                 +":guid, :shareName, :username, "\r
  73                                 +":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)");\r
  74                 query.bindValue(":guid", tempNotebook.getGuid());\r
  75                 query.bindValue(":shareName", tempNotebook.getShareName());\r
  76                 query.bindValue(":username", tempNotebook.getUsername());\r
  77                 query.bindValue(":shardId", tempNotebook.getShardId());\r
  78                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
  79                 query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
  80                 query.bindValue(":uri", tempNotebook.getUri());\r
  81                 query.bindValue(":notebookGuid", "");\r
  82                 \r
  83                 if (isDirty)\r
  84                         query.bindValue(":isDirty", true);\r
  85                 else\r
  86                         query.bindValue(":isDirty", false);\r
  87 \r
  88                 check = query.exec();\r
  89                 if (!check) {\r
  90                         logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");\r
  91                         logger.log(logger.MEDIUM, query.lastError().toString());\r
  92                         return;\r
  93                 }\r
  94         }\r
  95         // Delete the notebook based on a guid\r
  96         public void expungeNotebook(String id, boolean needsSync) {\r
  97                 boolean check;\r
  98                 \r
  99                 // First, delete any tags associated with this notebook\r
 100                 String notebookGuid = getNotebookGuid(id);\r
 101                 db.getNotebookTable().deleteLinkedTags(notebookGuid);\r
 102                 \r
 103                 // Now, delete any notes associated with this notebook\r
 104                 List<String> notes = db.getNoteTable().getNotesByNotebook(notebookGuid);\r
 105                 for (int i=0; i<notes.size(); i++) {\r
 106                         db.getNoteTable().expungeNote(notes.get(i), true, needsSync);\r
 107                 }\r
 108                 \r
 109                 // Delete the notebook record\r
 110                 db.getNotebookTable().expungeNotebook(notebookGuid, needsSync);\r
 111                 \r
 112                 // Finally, delete the linked notebook object itself\r
 113         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 114         check = query.prepare("delete from LinkedNotebook "\r
 115                                 +"where guid=:guid");\r
 116                 if (!check) {\r
 117                         logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");\r
 118                         logger.log(logger.EXTREME, query.lastError().toString());\r
 119                 }\r
 120                 query.bindValue(":guid", id);\r
 121                 check = query.exec();\r
 122                 if (!check) \r
 123                         logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");\r
 124                 \r
 125                 if  (needsSync) {\r
 126                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 127                         deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");\r
 128                 }\r
 129         }\r
 130         // Check if a notebook exists\r
 131         public boolean exists(String id) {\r
 132         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 133         boolean check = query.prepare("Select guid from linkednotebook where guid=:guid");\r
 134         query.bindValue(":guid", id);\r
 135                 check = query.exec();\r
 136                 if (!check) {\r
 137                         logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");\r
 138                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 139                 }\r
 140                 if (query.next())\r
 141                         return true;\r
 142                 return false;\r
 143         }\r
 144         // Update a notebook\r
 145         public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
 146                 boolean check;\r
 147                 if (!exists(tempNotebook.getGuid())) {\r
 148                         addNotebook(tempNotebook, isDirty);\r
 149                         return;\r
 150                 }\r
 151                 \r
 152         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 153         check = query.prepare("Update LinkedNotebook set guid=:guid, shareName=:shareName, " +\r
 154                         "username=:username, shardID=:shardID, uri=:uri, updateSequenceNumber=:usn, isDirty=:isDirty "+\r
 155                         "where guid=:keyGuid");\r
 156                 query.bindValue(":guid", tempNotebook.getGuid());\r
 157                 query.bindValue(":keyGuid", tempNotebook.getGuid());\r
 158                 query.bindValue(":shareName", tempNotebook.getShareName());\r
 159                 query.bindValue(":username", tempNotebook.getUsername());\r
 160                 query.bindValue(":shardID", tempNotebook.getShardId());\r
 161                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
 162                 query.bindValue(":uri", tempNotebook.getUri());\r
 163                 query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
 164 \r
 165                 query.bindValue(":isDirty", isDirty);\r
 166                 \r
 167                 check = query.exec();\r
 168                 if (!check) {\r
 169                         logger.log(logger.MEDIUM, "LinkedNotebook Table update failed.");\r
 170                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 171                 }\r
 172         }\r
 173         // Load notebooks from the database\r
 174         public List<LinkedNotebook> getAll() {\r
 175                 LinkedNotebook tempNotebook;\r
 176                 List<LinkedNotebook> index = new ArrayList<LinkedNotebook>();\r
 177                 boolean check;\r
 178                                         \r
 179         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 180                                         \r
 181                 check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " +\r
 182                                 " from LinkedNotebook");\r
 183                 if (!check)\r
 184                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 185                 while (query.next()) {\r
 186                         tempNotebook = new LinkedNotebook();\r
 187                         tempNotebook.setGuid(query.valueString(0));\r
 188                         tempNotebook.setShareName(query.valueString(1));\r
 189                         tempNotebook.setUsername(query.valueString(2));\r
 190                         tempNotebook.setShardId(query.valueString(3));\r
 191                         tempNotebook.setShareKey(query.valueString(4));\r
 192                         tempNotebook.setUri(query.valueString(5));\r
 193 \r
 194                         index.add(tempNotebook); \r
 195                 }       \r
 196                 return index;\r
 197         }                       \r
 198         // Load notebooks from the database\r
 199         public LinkedNotebook getNotebook(String guid) {\r
 200                 LinkedNotebook tempNotebook;\r
 201                 boolean check;\r
 202                                         \r
 203         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 204                                         \r
 205                 check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
 206                                 " from LinkedNotebook where guid=:guid");\r
 207                 if (!check)\r
 208                         logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
 209                 query.bindValue(":guid", guid);\r
 210                 query.exec();\r
 211                 while (query.next()) {\r
 212                         tempNotebook = new LinkedNotebook();\r
 213                         tempNotebook.setGuid(query.valueString(0));\r
 214                         tempNotebook.setShareName(query.valueString(1));\r
 215                         tempNotebook.setUsername(query.valueString(2));\r
 216                         tempNotebook.setShardId(query.valueString(3));\r
 217                         tempNotebook.setShareKey(query.valueString(4));\r
 218                         tempNotebook.setUri(query.valueString(5));\r
 219                         return tempNotebook;\r
 220                 }       \r
 221                 return null;\r
 222         }       \r
 223         // Load notebooks from the database\r
 224         public LinkedNotebook getByNotebookGuid(String guid) {\r
 225                 LinkedNotebook tempNotebook;\r
 226                 boolean check;\r
 227                                         \r
 228         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 229                                         \r
 230                 check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
 231                                 " from LinkedNotebook where notebookguid=:guid");\r
 232                 if (!check)\r
 233                         logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
 234                 query.bindValue(":guid", guid);\r
 235                 query.exec();\r
 236                 while (query.next()) {\r
 237                         tempNotebook = new LinkedNotebook();\r
 238                         tempNotebook.setGuid(query.valueString(0));\r
 239                         tempNotebook.setShareName(query.valueString(1));\r
 240                         tempNotebook.setUsername(query.valueString(2));\r
 241                         tempNotebook.setShardId(query.valueString(3));\r
 242                         tempNotebook.setShareKey(query.valueString(4));\r
 243                         tempNotebook.setUri(query.valueString(5));\r
 244                         return tempNotebook;\r
 245                 }       \r
 246                 return null;\r
 247         }\r
 248         // Get last sequence date for the notebook\r
 249         public long getLastSequenceDate(String guid) {\r
 250                 boolean check;\r
 251                                         \r
 252         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 253                                         \r
 254                 check = query.prepare("Select LastSequenceDate " \r
 255                                 +"from LinkedNotebook where guid=:guid");\r
 256                 query.bindValue(":guid", guid);\r
 257                 check = query.exec();\r
 258                 if (!check)\r
 259                         logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve last sequence date has failed.");\r
 260                 if (query.next()) {\r
 261                         return query.valueLong(0);\r
 262                 }       \r
 263                 return 0;\r
 264         }                       \r
 265         // Get a guid by uri\r
 266         public String getNotebookGuid(String guid) {\r
 267                 boolean check;\r
 268                                         \r
 269         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 270                                         \r
 271                 check = query.prepare("Select notebookGuid " \r
 272                                 +"from LinkedNotebook where guid=:guid");\r
 273                 query.bindValue(":guid", guid);\r
 274                 check = query.exec();\r
 275                 if (!check)\r
 276                         logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve of notebookguid by guidhas failed.");\r
 277                 if (query.next()) {\r
 278                         return query.valueString(0);\r
 279                 }       \r
 280                 return null;\r
 281         }       \r
 282         // get last sequence numeber\r
 283         public int getLastSequenceNumber(String guid) {\r
 284                 boolean check;\r
 285                                         \r
 286         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 287                                         \r
 288                 check = query.prepare("Select LastSequenceNumber " \r
 289                                 +"from LinkedNotebook where guid=:guid");\r
 290                 query.bindValue(":guid", guid);\r
 291                 check = query.exec();\r
 292                 if (!check)\r
 293                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 294                 while (query.next()) {\r
 295                         return query.valueInteger(0);\r
 296                 }       \r
 297                 return 0;\r
 298         }               \r
 299         \r
 300         // get the "true" notebook guid and not the shared notebook guid\r
 301         public String getLocalNotebookGuid(String guid) {\r
 302                 boolean check;\r
 303                                         \r
 304         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 305                                         \r
 306                 check = query.prepare("Select notebookGuid " \r
 307                                 +"from LinkedNotebook where guid=:guid");\r
 308                 query.bindValue(":guid", guid);\r
 309                 check = query.exec();\r
 310                 if (!check)\r
 311                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 312                 while (query.next()) {\r
 313                         return query.valueString(0);\r
 314                 }       \r
 315                 return null;\r
 316         }\r
 317 \r
 318         // does a record exist?\r
 319         public String findNotebookByShareName(String name) {\r
 320                 \r
 321                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 322                 \r
 323                 query.prepare("Select guid from Linkednotebook where shareName=:name");\r
 324                 query.bindValue(":name", name);\r
 325                 if (!query.exec())\r
 326                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
 327                 String val = null;\r
 328                 if (query.next())\r
 329                         val = query.valueString(0);\r
 330                 return val;\r
 331         }\r
 332 \r
 333         // does a record exist?\r
 334         public String setNotebookGuid(String shareKey, String notebookGuid) {\r
 335                 \r
 336                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 337                 \r
 338                 query.prepare("Update LinkedNotebook set notebookGuid=:notebookGuid where shareKey=:shareKey");\r
 339                 query.bindValue(":notebookGuid", notebookGuid);\r
 340                 query.bindValue(":shareKey", shareKey);\r
 341                 if (!query.exec())\r
 342                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
 343                 String val = null;\r
 344                 if (query.next())\r
 345                         val = query.valueString(0);\r
 346                 return val;\r
 347         }\r
 348         // set sync date\r
 349         public String setLastSequenceDate(String guid, long date) {\r
 350                 \r
 351                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 352                 \r
 353                 query.prepare("Update LinkedNotebook set lastsequencedate=:date where guid=:guid");\r
 354                 query.bindValue(":date", date);\r
 355                 query.bindValue(":guid", guid);\r
 356                 if (!query.exec())\r
 357                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
 358                 String val = null;\r
 359                 if (query.next())\r
 360                         val = query.valueString(0);\r
 361                 return val;\r
 362         }\r
 363         // set sync number\r
 364         public String setLastSequenceNumber(String guid, int number) {\r
 365                 \r
 366                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 367                 \r
 368                 query.prepare("Update LinkedNotebook set lastsequencenumber=:number where guid=:guid");\r
 369                 query.bindValue(":number", number);\r
 370                 query.bindValue(":guid", guid);\r
 371                 if (!query.exec())\r
 372                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
 373                 String val = null;\r
 374                 if (query.next())\r
 375                         val = query.valueString(0);\r
 376                 return val;\r
 377         }\r
 378         \r
 379         // Get a list of linked notebooks that need to be updated\r
 380         public List<String> getDirtyGuids() {\r
 381                 List<String> index = new ArrayList<String>();\r
 382                 boolean check;  \r
 383         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 384                                         \r
 385                 check = query.exec("Select guid from LinkedNotebook where isDirty = true");\r
 386                 if (!check) \r
 387                         logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve has failed in getdirtyIds.");\r
 388                 while (query.next()) {\r
 389                         index.add(query.valueString(0));\r
 390                 }       \r
 391                 return index;   \r
 392         }\r
 393 \r
 394 }\r
 395 \r