src/cx/fbn/nevernote/sql/NotebookTable.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.text.DateFormat;\r
  24 import java.text.ParseException;\r
  25 import java.text.SimpleDateFormat;\r
  26 import java.util.ArrayList;\r
  27 import java.util.List;\r
  28 \r
  29 import com.evernote.edam.type.Notebook;\r
  30 \r
  31 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  32 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  33 import cx.fbn.nevernote.utilities.Pair;\r
  34 \r
  35 public class NotebookTable {\r
  36         \r
  37         private final ApplicationLogger                 logger;\r
  38         DatabaseConnection                                                      db;\r
  39         \r
  40         // Constructor\r
  41         public NotebookTable(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 Notebook...");\r
  49         if (!query.exec("Create table Notebook (guid varchar primary key, " +\r
  50                         "sequence integer, name varchar, defaultNotebook varchar, "+\r
  51                         "serviceCreated timestamp, serviceUpdated timestamp, published boolean, isDirty boolean, "+\r
  52                         "autoEncrypt boolean, local boolean, archived boolean)"))                               \r
  53                 logger.log(logger.HIGH, "Table Notebook creation FAILED!!!");   \r
  54         Notebook newnote = new Notebook();\r
  55         newnote.setDefaultNotebook(true);\r
  56         newnote.setName("My Notebook");\r
  57         newnote.setPublished(false);\r
  58         newnote.setGuid("1");\r
  59         addNotebook(newnote, true, false);\r
  60                 \r
  61         }\r
  62         // Drop the table\r
  63         public void dropTable() {\r
  64                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  65                 query.exec("Drop table Notebook");\r
  66         }\r
  67         // Save an individual notebook\r
  68         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
  69                 boolean check;\r
  70                 \r
  71                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
  72         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  73                 check = query.prepare("Insert Into Notebook (guid, sequence, name, defaultNotebook, "\r
  74                                 +"serviceCreated, serviceUpdated, published, "   \r
  75                                 + "isDirty, autoEncrypt," \r
  76                                 + "local, archived) Values("\r
  77                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
  78                                 +":serviceCreated, :serviceUpdated, :published, "\r
  79                                 +":isDirty, :autoEncrypt, "\r
  80                                 +":local, false)");\r
  81                 query.bindValue(":guid", tempNotebook.getGuid());\r
  82                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
  83                 query.bindValue(":name", tempNotebook.getName());\r
  84                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
  85                 \r
  86                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
  87                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
  88                 if (serviceUpdated.toString() == null)\r
  89                         serviceUpdated = serviceCreated;\r
  90                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
  91                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
  92                 query.bindValue(":published",tempNotebook.isPublished());\r
  93                 \r
  94                 if (isDirty)\r
  95                         query.bindValue(":isDirty", true);\r
  96                 else\r
  97                         query.bindValue(":isDirty", false);\r
  98                 query.bindValue(":autoEncrypt", false);\r
  99                 query.bindValue(":local", local);\r
 100 \r
 101                 check = query.exec();\r
 102                 if (!check) {\r
 103                         logger.log(logger.MEDIUM, "Notebook Table insert failed.");\r
 104                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 105                 }\r
 106         }\r
 107         // Delete the notebook based on a guid\r
 108         public void expungeNotebook(String guid, boolean needsSync) {\r
 109                 boolean check;\r
 110         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 111 \r
 112         check = query.prepare("delete from Notebook "\r
 113                                 +"where guid=:guid");\r
 114                 if (!check) {\r
 115                         logger.log(logger.EXTREME, "Notebook SQL delete prepare has failed.");\r
 116                         logger.log(logger.EXTREME, query.lastError().toString());\r
 117                 }\r
 118                 query.bindValue(":guid", guid);\r
 119                 check = query.exec();\r
 120                 if (!check) \r
 121                         logger.log(logger.MEDIUM, "Notebook delete failed.");\r
 122                 \r
 123                 // Signal the parent that work needs to be done\r
 124                 if  (needsSync) {\r
 125                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 126                         deletedTable.addDeletedItem(guid, "Notebook");\r
 127                 }\r
 128         }\r
 129         // Update a notebook\r
 130         public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
 131                 boolean check;\r
 132                 \r
 133                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 134                 \r
 135         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 136         check = query.prepare("Update Notebook set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
 137                         "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
 138                                 "published=:published, isDirty=:isDirty where guid=:guid ");\r
 139                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 140                 query.bindValue(":name", tempNotebook.getName());\r
 141                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 142 \r
 143                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 144                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
 145                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 146                 query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
 147                 \r
 148                 query.bindValue(":published", tempNotebook.isPublished());\r
 149                 query.bindValue(":isDirty", isDirty);\r
 150                 query.bindValue(":guid", tempNotebook.getGuid());\r
 151                 \r
 152                 check = query.exec();\r
 153                 if (!check) {\r
 154                         logger.log(logger.MEDIUM, "Notebook Table update failed.");\r
 155                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 156                 }\r
 157         }\r
 158         // Load notebooks from the database\r
 159         public List<Notebook> getAll() {\r
 160                 Notebook tempNotebook;\r
 161                 List<Notebook> index = new ArrayList<Notebook>();\r
 162                 boolean check;\r
 163                                         \r
 164         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 165                                         \r
 166                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 167                                 "serviceCreated, "+\r
 168                                 "serviceUpdated, "+\r
 169                                 "published, defaultNotebook from Notebook order by name");\r
 170                 if (!check)\r
 171                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 172                 while (query.next()) {\r
 173                         tempNotebook = new Notebook();\r
 174                         tempNotebook.setGuid(query.valueString(0));\r
 175                         int sequence = new Integer(query.valueString(1)).intValue();\r
 176                         tempNotebook.setUpdateSequenceNum(sequence);\r
 177                         tempNotebook.setName(query.valueString(2));\r
 178                         DateFormat indfm = null;\r
 179                         try {\r
 180                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 181 //                              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 182                         } catch (Exception e) { }\r
 183                         try {\r
 184                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 185                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 186                         } catch (ParseException e) {\r
 187                                 e.printStackTrace();\r
 188                         }\r
 189                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 190                         tempNotebook.setDefaultNotebook(new Boolean(query.valueString(7)));\r
 191                         index.add(tempNotebook); \r
 192                 }       \r
 193                 return index;\r
 194         }       \r
 195         public List<Notebook> getAllLocal() {\r
 196                 Notebook tempNotebook;\r
 197                 List<Notebook> index = new ArrayList<Notebook>();\r
 198                 boolean check;\r
 199 \r
 200         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 201                                         \r
 202                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 203                                 "serviceCreated, serviceUpdated, published from Notebook where local=true order by name");\r
 204                 if (!check)\r
 205                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 206                 while (query.next()) {\r
 207                         tempNotebook = new Notebook();\r
 208                         tempNotebook.setGuid(query.valueString(0));\r
 209                         int sequence = new Integer(query.valueString(1)).intValue();\r
 210                         tempNotebook.setUpdateSequenceNum(sequence);\r
 211                         tempNotebook.setName(query.valueString(2));\r
 212                         \r
 213                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 214 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 215                         try {\r
 216                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 217                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 218                         } catch (ParseException e) {\r
 219                                 e.printStackTrace();\r
 220                         }\r
 221                         index.add(tempNotebook); \r
 222                 }       \r
 223                 return index;\r
 224         }\r
 225         // Archive or un-archive a notebook\r
 226         public void setArchived(String guid, boolean val) {\r
 227                 boolean check;                  \r
 228         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 229                 check = query.prepare("Update notebook set archived=:archived where guid=:guid");\r
 230                 if (!check)\r
 231                         logger.log(logger.EXTREME, "Notebook SQL archive update has failed.");\r
 232                 query.bindValue(":guid", guid);\r
 233                 query.bindValue(":archived", val);\r
 234                 query.exec();\r
 235         }\r
 236         // Load non-archived notebooks from the database\r
 237         public List<Notebook> getAllArchived() {\r
 238                 Notebook tempNotebook;\r
 239                 List<Notebook> index = new ArrayList<Notebook>();\r
 240                 boolean check;\r
 241                                                 \r
 242         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 243                                         \r
 244                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 245                                 "serviceCreated, serviceUpdated, published from Notebook where archived=true order by name");\r
 246                 if (!check)\r
 247                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 248                 while (query.next()) {\r
 249                         tempNotebook = new Notebook();\r
 250                         tempNotebook.setGuid(query.valueString(0));\r
 251                         int sequence = new Integer(query.valueString(1)).intValue();\r
 252                         tempNotebook.setUpdateSequenceNum(sequence);\r
 253                         tempNotebook.setName(query.valueString(2));\r
 254                         \r
 255                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 256 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 257                         try {\r
 258                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 259                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 260                         } catch (ParseException e) {\r
 261                                 e.printStackTrace();\r
 262                         }\r
 263                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 264                         index.add(tempNotebook); \r
 265                 }       \r
 266                 return index;\r
 267         }       \r
 268         // Check for a local/remote notebook\r
 269         public boolean isNotebookLocal(String guid) {\r
 270         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 271                 \r
 272                 query.prepare("Select local from Notebook where guid=:guid");\r
 273                 query.bindValue(":guid", guid);\r
 274                 query.exec();\r
 275                 if (!query.next()) {\r
 276                         return false;\r
 277                 }\r
 278                 boolean returnValue = false;\r
 279                 String returnVal = query.valueString(0);\r
 280                 if (returnVal.equals("false"))\r
 281                         returnValue = false;\r
 282                 else\r
 283                         returnValue = true;\r
 284                 return returnValue;\r
 285         }\r
 286         // Update a notebook sequence number\r
 287         public void updateNotebookSequence(String guid, int sequence) {\r
 288                 boolean check;\r
 289         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 290                 check = query.prepare("Update Notebook set sequence=:sequence where guid=:guid");\r
 291                 query.bindValue(":guid", guid);\r
 292                 query.bindValue(":sequence", sequence);\r
 293                 query.exec();\r
 294                 if (!check) {\r
 295                         logger.log(logger.MEDIUM, "Notebook sequence update failed.");\r
 296                         logger.log(logger.MEDIUM, query.lastError());\r
 297                 } \r
 298         }\r
 299         // Update a notebook GUID number\r
 300         public void updateNotebookGuid(String oldGuid, String newGuid) {\r
 301         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 302                 query.prepare("Update Notebook set guid=:newGuid where guid=:oldGuid");\r
 303                 query.bindValue(":oldGuid", oldGuid);\r
 304                 query.bindValue(":newGuid", newGuid);\r
 305                 if (!query.exec()) {\r
 306                         logger.log(logger.MEDIUM, "Notebook guid update failed.");\r
 307                         logger.log(logger.MEDIUM, query.lastError());\r
 308                 } \r
 309                 \r
 310                 // Update any notes containing the notebook guid\r
 311                 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
 312                 query.bindValue(":oldGuid", oldGuid);\r
 313                 query.bindValue(":newGuid", newGuid);\r
 314                 if (!query.exec()) {\r
 315                         logger.log(logger.MEDIUM, "Notebook guid update for note failed.");\r
 316                         logger.log(logger.MEDIUM, query.lastError());\r
 317                 } \r
 318                 \r
 319                 // Update any watch folders with the new guid\r
 320                 query = new NSqlQuery(db.getConnection());\r
 321                 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
 322                 query.bindValue(":oldGuid", oldGuid);\r
 323                 query.bindValue(":newGuid", newGuid);\r
 324                 if (!query.exec()) {\r
 325                         logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
 326                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 327                 }               \r
 328         }\r
 329         // Get a list of notes that need to be updated\r
 330         public List <Notebook> getDirty() {\r
 331                 Notebook tempNotebook;\r
 332                 List<Notebook> index = new ArrayList<Notebook>();\r
 333                 boolean check;\r
 334                                                 \r
 335                 \r
 336         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 337                                         \r
 338                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 339                                 "serviceCreated, serviceUpdated, published from Notebook where isDirty = true and local=false");\r
 340                 if (!check) \r
 341                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 342                 while (query.next()) {\r
 343                         tempNotebook = new Notebook();\r
 344                         tempNotebook.setGuid(query.valueString(0));\r
 345                         int sequence = new Integer(query.valueString(1)).intValue();\r
 346                         tempNotebook.setUpdateSequenceNum(sequence);\r
 347                         tempNotebook.setName(query.valueString(2));\r
 348                         \r
 349                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 350 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 351                         try {\r
 352                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 353                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 354                         } catch (ParseException e) {\r
 355                                 e.printStackTrace();\r
 356                         }\r
 357                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 358                         index.add(tempNotebook); \r
 359                 }       \r
 360                 return index;   \r
 361         }\r
 362         // This is a convience method to check if a tag exists & update/create based upon it\r
 363         public void syncNotebook(Notebook notebook, boolean isDirty) {\r
 364                 if (!exists(notebook.getGuid())) {\r
 365                         addNotebook(notebook, isDirty, isDirty);\r
 366                         return;\r
 367                 }\r
 368                 updateNotebook(notebook, isDirty);\r
 369         }\r
 370         // does a record exist?\r
 371         private boolean exists(String guid) {\r
 372                 \r
 373                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 374                 \r
 375                 query.prepare("Select guid from notebook where guid=:guid");\r
 376                 query.bindValue(":guid", guid);\r
 377                 if (!query.exec())\r
 378                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
 379                 boolean retval = query.next();\r
 380                 return retval;\r
 381         }\r
 382         // Reset the dirty flag.  Typically done after a sync.\r
 383         public void  resetDirtyFlag(String guid) {\r
 384                 \r
 385                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 386                 \r
 387                 query.prepare("Update notebook set isdirty='false' where guid=:guid");\r
 388                 query.bindValue(":guid", guid);\r
 389                 if (!query.exec())\r
 390                         logger.log(logger.EXTREME, "Error resetting notebook dirty field.");\r
 391         }\r
 392         \r
 393         \r
 394         \r
 395 \r
 396         // does a record exist?\r
 397         public String findNotebookByName(String newname) {\r
 398                 \r
 399                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 400                 \r
 401                 query.prepare("Select guid from notebook where name=:newname");\r
 402                 query.bindValue(":newname", newname);\r
 403                 if (!query.exec())\r
 404                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
 405                 String val = null;\r
 406                 if (query.next())\r
 407                         val = query.valueString(0);\r
 408                 return val;\r
 409         }\r
 410         // Get a note tag counts\r
 411         public List<Pair<String,Integer>> getNotebookCounts() {\r
 412                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
 413                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 414                 if (!query.exec("select notebookGuid, count(guid) from note where active=1 group by notebookguid;")) {\r
 415                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
 416                         logger.log(logger.MEDIUM, query.lastError());\r
 417                         return null;\r
 418                 }\r
 419                 while (query.next()) {\r
 420                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
 421                         newCount.setFirst(query.valueString(0));\r
 422                         newCount.setSecond(query.valueInteger(1));\r
 423                         counts.add(newCount);\r
 424                 }       \r
 425                 return counts;\r
 426         }\r
 427 \r
 428 }\r
 429 \r