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