src/cx/fbn/nevernote/sql/SharedNotebookTable.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.SharedNotebook;\r
  30 \r
  31 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  32 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  33 \r
  34 public class SharedNotebookTable {\r
  35         \r
  36         private final ApplicationLogger                 logger;\r
  37         DatabaseConnection                                                      db;\r
  38         \r
  39         // Constructor\r
  40         public SharedNotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
  41                 logger = l;\r
  42                 db = d;\r
  43         }\r
  44         // Create the table\r
  45         public void createTable() {\r
  46                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  47         logger.log(logger.HIGH, "Creating table SharedNotebook...");\r
  48         if (!query.exec("Create table SharedNotebook (id long primary key, " +\r
  49                         "userid Integer, " +\r
  50                         "notebookGuid VarChar, "+\r
  51                         "email VarChar, "+\r
  52                         "notebookModifiable boolean, " +\r
  53                         "requireLogin boolean, "+\r
  54                         "serviceCreated timestamp, "+\r
  55                         "shareKey VarChar,  "+\r
  56                         "username VarChar, "+\r
  57                         "icon blob, " +\r
  58                         "isDirty boolean)"))                            \r
  59                 logger.log(logger.HIGH, "Table SharedNotebook creation FAILED!!!");   \r
  60         }\r
  61         // Drop the table\r
  62         public void dropTable() {\r
  63                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  64                 query.exec("Drop table SharedNotebook");\r
  65         }\r
  66         // Save an individual notebook\r
  67         public void addNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
  68                 boolean check;\r
  69                 \r
  70                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
  71         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  72                 check = query.prepare("Insert Into SharedNotebook (id, userid, notebookGuid, email,  "\r
  73 <<<<<<< HEAD
  74                                 +"notebookModifiable, requireLogin, serviceCreated, shareKey, username, isDirty) "   \r
  75                                 + " Values("\r
  76                                 +":id, :userid, :notebookGuid, :email, "\r
  77                                 +":notebookModifiable, :requireLogin, :serviceCreated, "\r
  78                                 +":shareKey, :username, :isDirty)");\r
  79 =======
  80                                 +"notebookModifiable, requireLogin, serviceCreated, shareKey, username) "   \r
  81                                 + " Values("\r
  82                                 +":id, :userid, :notebookGuid, :email, "\r
  83                                 +":notebookModifiable, :requireLogin, :serviceCreated, "\r
  84                                 +":shareKey, :username)");\r
  85 >>>>>>> Add linked & shared notebook tables
  86                 query.bindValue(":id", tempNotebook.getId());\r
  87                 query.bindValue(":userid", tempNotebook.getUserId());\r
  88                 query.bindValue(":notebookGuid", tempNotebook.getNotebookGuid());\r
  89                 query.bindValue(":email", tempNotebook.getEmail());\r
  90                 query.bindValue(":notebookModifiable", tempNotebook.isNotebookModifiable());\r
  91                 query.bindValue(":requireLogin", tempNotebook.isRequireLogin());\r
  92 \r
  93                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
  94                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
  95                 \r
  96                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
  97                 query.bindValue(":username", tempNotebook.getUsername());\r
  98                 \r
  99                 if (isDirty)\r
 100                         query.bindValue(":isDirty", true);\r
 101                 else\r
 102                         query.bindValue(":isDirty", false);\r
 103 \r
 104                 check = query.exec();\r
 105                 if (!check) {\r
 106                         logger.log(logger.MEDIUM, "SharedNotebook Table insert failed.");\r
 107                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 108                 }\r
 109         }\r
 110 <<<<<<< HEAD
 111         // Check if a notebook exists\r
 112         public boolean exists(long id) {\r
 113         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 114         boolean check = query.prepare("Select id from sharednotebook where id=:id");\r
 115         query.bindValue(":id", id);\r
 116                 check = query.exec();\r
 117                 if (!check) {\r
 118                         logger.log(logger.MEDIUM, "SharedNotebook Table exists check failed.");\r
 119                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 120                 }\r
 121                 if (query.next())\r
 122                         return true;\r
 123                 return false;\r
 124         }\r
 125         // Delete the notebook based on a id\r
 126 =======
 127         // Delete the notebook based on a guid\r
 128 >>>>>>> Add linked & shared notebook tables
 129         public void expungeNotebook(long id, boolean needsSync) {\r
 130                 boolean check;\r
 131         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 132 \r
 133         check = query.prepare("delete from SharedNotebook "\r
 134                                 +"where id=:id");\r
 135                 if (!check) {\r
 136                         logger.log(logger.EXTREME, "SharedNotebook SQL delete prepare has failed.");\r
 137                         logger.log(logger.EXTREME, query.lastError().toString());\r
 138                 }\r
 139                 query.bindValue(":id", id);\r
 140                 check = query.exec();\r
 141                 if (!check) \r
 142                         logger.log(logger.MEDIUM, "SharedNotebook delete failed.");\r
 143                 \r
 144                 // Signal the parent that work needs to be done\r
 145                 if  (needsSync) {\r
 146                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 147                         deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook");\r
 148                 }\r
 149         }\r
 150 <<<<<<< HEAD
 151         // Delete the notebook based on a id\r
 152         public void expungeNotebookByGuid(String id, boolean needsSync) {\r
 153                 boolean check;\r
 154         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 155 \r
 156         check = query.prepare("delete from SharedNotebook "\r
 157                                 +"where guid=:id");\r
 158                 if (!check) {\r
 159                         logger.log(logger.EXTREME, "SharedNotebook SQL delete by notebook guid prepare has failed.");\r
 160                         logger.log(logger.EXTREME, query.lastError().toString());\r
 161                 }\r
 162                 query.bindValue(":id", id);\r
 163                 check = query.exec();\r
 164                 if (!check) \r
 165                         logger.log(logger.MEDIUM, "SharedNotebook delete by notebook guid failed.");\r
 166                 \r
 167                 // Signal the parent that work needs to be done\r
 168                 if  (needsSync) {\r
 169                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 170                         deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook");\r
 171                 }\r
 172         }\r
 173 \r
 174         \r
 175         // Update a notebook\r
 176         public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
 177                 boolean check;\r
 178                 if (!exists(tempNotebook.getId())) {\r
 179                         addNotebook(tempNotebook, isDirty);\r
 180                         return;\r
 181                 }\r
 182                 \r
 183                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 184                 \r
 185                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                                              \r
 186         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 187         check = query.prepare("Update SharedNotebook set id=:id, userid=:userid, notebookGuid=:notebook, "\r
 188                         + "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:serviceCreated, "\r
 189 =======
 190         // Update a notebook\r
 191         public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
 192                 boolean check;\r
 193                 \r
 194                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 195                 \r
 196         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 197         check = query.prepare("Update SharedNotebook set id=:id, userid=:userid, notebookGuid=:notebook, "\r
 198                         + "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:created "\r
 199 >>>>>>> Add linked & shared notebook tables
 200                         + "shareKey=:shareKey, username=:username, isDirty=:isdirty");\r
 201                 query.bindValue(":id", tempNotebook.getId());\r
 202                 query.bindValue(":userid", tempNotebook.getUserId());\r
 203                 query.bindValue(":notebook", tempNotebook.getNotebookGuid());\r
 204                 query.bindValue(":email", tempNotebook.getEmail());\r
 205                 query.bindValue(":mod", tempNotebook.isNotebookModifiable());\r
 206                 query.bindValue(":rlogin", tempNotebook.isRequireLogin());\r
 207 <<<<<<< HEAD
 208                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 209                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
 210                 query.bindValue(":username", tempNotebook.getUsername());\r
 211 =======
 212                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
 213                 query.bindValue(":username", tempNotebook.getUsername());\r
 214 \r
 215                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                                              \r
 216                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 217 >>>>>>> Add linked & shared notebook tables
 218                 \r
 219                 query.bindValue(":isDirty", isDirty);\r
 220                 \r
 221                 check = query.exec();\r
 222                 if (!check) {\r
 223                         logger.log(logger.MEDIUM, "SharedNotebook Table update failed.");\r
 224                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 225                 }\r
 226         }\r
 227         // Load notebooks from the database\r
 228         public List<SharedNotebook> getAll() {\r
 229                 SharedNotebook tempNotebook;\r
 230                 List<SharedNotebook> index = new ArrayList<SharedNotebook>();\r
 231                 boolean check;\r
 232                                         \r
 233         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 234                                         \r
 235                 check = query.exec("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " +\r
 236                                 "serviceCreated, "+\r
 237                                 "shareKey, username from SharedNotebook");\r
 238                 if (!check)\r
 239                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 240                 while (query.next()) {\r
 241                         tempNotebook = new SharedNotebook();\r
 242                         tempNotebook.setId(query.valueLong(0));\r
 243                         tempNotebook.setUserId(query.valueInteger(1));\r
 244                         tempNotebook.setNotebookGuid(query.valueString(2));\r
 245                         tempNotebook.setEmail(query.valueString(3));\r
 246                         tempNotebook.setNotebookModifiable(query.valueBoolean(4,false));\r
 247                         tempNotebook.setRequireLogin(query.valueBoolean(5,true));\r
 248                         DateFormat indfm = null;\r
 249                         try {\r
 250                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 251                         } catch (Exception e) { }\r
 252                         try {\r
 253                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime());\r
 254                         } catch (ParseException e) {\r
 255                                 e.printStackTrace();\r
 256                         }\r
 257                         tempNotebook.setShareKey(query.valueString(7));\r
 258                         tempNotebook.setUsername(query.valueString(8));\r
 259 \r
 260                         index.add(tempNotebook); \r
 261                 }       \r
 262                 return index;\r
 263         }                       \r
 264 \r
 265 <<<<<<< HEAD
 266         // Load notebooks from the database\r
 267         public List<SharedNotebook> getForNotebook(String guid) {\r
 268                 SharedNotebook tempNotebook;\r
 269                 List<SharedNotebook> index = new ArrayList<SharedNotebook>();\r
 270                 boolean check;\r
 271                                         \r
 272         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 273                                         \r
 274                 check = query.prepare("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " +\r
 275                                 "serviceCreated, "+\r
 276                                 "shareKey, username from SharedNotebook where notebookGuid=:notebookGuid ");\r
 277                 if (!check)\r
 278                         logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL prepare has failed.");\r
 279                 query.bindValue(":notebookGuid", guid);\r
 280                 check = query.exec();\r
 281                 if (!check)\r
 282                         logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL exec has failed.");\r
 283                 \r
 284                 while (query.next()) {\r
 285                         tempNotebook = new SharedNotebook();\r
 286                         tempNotebook.setId(query.valueLong(0));\r
 287                         tempNotebook.setUserId(query.valueInteger(1));\r
 288                         tempNotebook.setNotebookGuid(query.valueString(2));\r
 289                         tempNotebook.setEmail(query.valueString(3));\r
 290                         tempNotebook.setNotebookModifiable(query.valueBoolean(4,false));\r
 291                         tempNotebook.setRequireLogin(query.valueBoolean(5,true));\r
 292                         DateFormat indfm = null;\r
 293                         try {\r
 294                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 295                         } catch (Exception e) { }\r
 296                         try {\r
 297                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime());\r
 298                         } catch (ParseException e) {\r
 299                                 e.printStackTrace();\r
 300                         }\r
 301                         tempNotebook.setShareKey(query.valueString(7));\r
 302                         tempNotebook.setUsername(query.valueString(8));\r
 303 \r
 304                         index.add(tempNotebook); \r
 305                 }       \r
 306                 return index;\r
 307         }                       \r
 308 \r
 309         \r
 310         // Get a list of shared notebooks that need to be updated\r
 311         public List <Long> getDirtyIds() {\r
 312                 List<Long> index = new ArrayList<Long>();\r
 313                 boolean check;  \r
 314         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 315                                         \r
 316                 check = query.exec("Select id from SharedNotebook where isDirty = true");\r
 317                 if (!check) \r
 318                         logger.log(logger.EXTREME, "SharedNotebook SQL retrieve has failed in getdirtyIds.");\r
 319                 while (query.next()) {\r
 320                         index.add(query.valueLong(0));\r
 321                 }       \r
 322                 return index;   \r
 323         }\r
 324 \r
 325 =======
 326         // does a record exist?\r
 327         public String findNotebookByName(String newname) {\r
 328                 \r
 329                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 330                 \r
 331                 query.prepare("Select guid from sharednotebook where name=:newname");\r
 332                 query.bindValue(":newname", newname);\r
 333                 if (!query.exec())\r
 334                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
 335                 String val = null;\r
 336                 if (query.next())\r
 337                         val = query.valueString(0);\r
 338                 return val;\r
 339         }\r
 340 \r
 341 \r
 342 >>>>>>> Add linked & shared notebook tables
 343 }\r
 344 \r