src/cx/fbn/nevernote/sql/NotebookTable.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.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.HashMap;\r
  28 import java.util.List;\r
  29 \r
  30 import com.evernote.edam.type.NoteSortOrder;\r
  31 import com.evernote.edam.type.Notebook;\r
  32 import com.evernote.edam.type.Publishing;\r
  33 import com.trolltech.qt.core.QBuffer;\r
  34 import com.trolltech.qt.core.QByteArray;\r
  35 import com.trolltech.qt.core.QIODevice;\r
  36 import com.trolltech.qt.gui.QIcon;\r
  37 import com.trolltech.qt.gui.QImage;\r
  38 import com.trolltech.qt.gui.QPixmap;\r
  39 \r
  40 import cx.fbn.nevernote.Global;\r
  41 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  42 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  43 import cx.fbn.nevernote.utilities.Pair;\r
  44 \r
  45 public class NotebookTable {\r
  46         \r
  47         private final ApplicationLogger                 logger;\r
  48         DatabaseConnection                                              db;\r
  49         private final String                                    dbName;\r
  50         NSqlQuery                                                               notebookCountQuery;\r
  51         \r
  52         // Constructor\r
  53         public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
  54                 logger = l;\r
  55                 db = d;\r
  56                 dbName = "Notebook";\r
  57         }\r
  58         // Constructor\r
  59         public NotebookTable(ApplicationLogger l, DatabaseConnection d, String name) {\r
  60                 logger = l;\r
  61                 db = d;\r
  62                 dbName = name;\r
  63         }\r
  64 \r
  65         // Create the table\r
  66         public void createTable(boolean addDefaulte) {\r
  67                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  68         logger.log(logger.HIGH, "Creating table "+dbName+"...");\r
  69         if (!query.exec("Create table "+dbName+" (guid varchar primary key, " +\r
  70                         "sequence integer, " +\r
  71                         "name varchar, "+\r
  72                         "defaultNotebook varchar, "+\r
  73                         "serviceCreated timestamp, " +\r
  74                         "serviceUpdated timestamp, "+\r
  75                         "published boolean, "+\r
  76                         "isDirty boolean, "+\r
  77                         "autoEncrypt boolean, "+\r
  78                         "local boolean, "+\r
  79                         "archived boolean)"))                           \r
  80                 logger.log(logger.HIGH, "Table "+dbName+" creation FAILED!!!");   \r
  81         Notebook newnote = new Notebook();\r
  82         newnote.setDefaultNotebook(true);\r
  83         newnote.setName("My Notebook");\r
  84         newnote.setPublished(false);\r
  85         newnote.setGuid("1");\r
  86         \r
  87         // Setup an initial notebook\r
  88                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
  89         query = new NSqlQuery(db.getConnection());\r
  90                 query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "\r
  91                                 +"serviceCreated, serviceUpdated, published, "   \r
  92                                 + "isDirty, autoEncrypt, " \r
  93                                 + "local, archived) Values("\r
  94                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
  95                                 +":serviceCreated, :serviceUpdated, :published, "\r
  96                                 +":isDirty, :autoEncrypt, "\r
  97                                 +":local, false)");\r
  98                 query.bindValue(":guid", newnote.getGuid());\r
  99                 query.bindValue(":sequence", newnote.getUpdateSequenceNum());\r
 100                 query.bindValue(":name", newnote.getName());\r
 101                 query.bindValue(":defaultNotebook", newnote.isDefaultNotebook());\r
 102                 \r
 103                 StringBuilder serviceCreated = new StringBuilder(simple.format(newnote.getServiceCreated()));                   \r
 104                 StringBuilder serviceUpdated = new StringBuilder(simple.format(newnote.getServiceUpdated()));\r
 105                 if (serviceUpdated.toString() == null)\r
 106                         serviceUpdated = serviceCreated;\r
 107                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 108                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 109                 query.bindValue(":published",newnote.isPublished());\r
 110                 \r
 111                 query.bindValue(":isDirty", true);\r
 112                 query.bindValue(":autoEncrypt", false);\r
 113                 query.bindValue(":local", false);\r
 114 \r
 115                 boolean check = query.exec();\r
 116                 if (!check) {\r
 117                         logger.log(logger.MEDIUM, "Initial "+dbName+" Table insert failed.");\r
 118                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 119                 }\r
 120 \r
 121                 \r
 122         }\r
 123         // Drop the table\r
 124         public void dropTable() {\r
 125                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 126                 query.exec("Drop table "+dbName);\r
 127         }\r
 128         // Save an individual notebook\r
 129         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
 130                 addNotebook(tempNotebook, isDirty, local, false, false);\r
 131         }\r
 132         // Save an individual notebook\r
 133         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local, boolean linked, boolean readOnly) {\r
 134                 boolean check;\r
 135                 \r
 136                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 137         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 138                 check = query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "\r
 139                                 +"serviceCreated, serviceUpdated, published, "   \r
 140                                 + "publishingUri, publishingOrder, publishingAscending, publishingPublicDescription, "\r
 141                                 + "isDirty, autoEncrypt, stack, " \r
 142                                 + "local, archived, readOnly, linked) Values("\r
 143                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
 144                                 +":serviceCreated, :serviceUpdated, :published, "\r
 145                                 +":publishingUri, :publishingOrder, :publishingAscending, :publishingPublicDescription, "\r
 146                                 +":isDirty, :autoEncrypt, "\r
 147                                 +":stack, :local, false, :readOnly, :linked)");\r
 148                 query.bindValue(":guid", tempNotebook.getGuid());\r
 149                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 150                 query.bindValue(":name", tempNotebook.getName());\r
 151                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 152                 \r
 153                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 154                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
 155                 if (serviceUpdated.toString() == null)\r
 156                         serviceUpdated = serviceCreated;\r
 157                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 158                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 159                 query.bindValue(":published",tempNotebook.isPublished());\r
 160                 query.bindValue(":linked", linked);\r
 161                 query.bindValue(":readOnly", readOnly);\r
 162                 \r
 163                 if (tempNotebook.isPublished() && tempNotebook.getPublishing() != null) {\r
 164                         Publishing p = tempNotebook.getPublishing();\r
 165                         query.bindValue(":publishingUri", p.getUri());\r
 166                         query.bindValue(":publishingOrder", p.getOrder().getValue());\r
 167                         query.bindValue(":publishingAscending", p.isAscending());\r
 168                         query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 169                 } else {\r
 170                         query.bindValue(":publishingUri", "");\r
 171                         query.bindValue(":publishingOrder", 1);\r
 172                         query.bindValue(":publishingAscending", 1);\r
 173                         query.bindValue(":publishingPublicDescription", "");\r
 174                 }\r
 175                 \r
 176                 if (isDirty)\r
 177                         query.bindValue(":isDirty", true);\r
 178                 else\r
 179                         query.bindValue(":isDirty", false);\r
 180                 query.bindValue(":autoEncrypt", false);\r
 181                 query.bindValue(":local", local);\r
 182                 query.bindValue(":stack", tempNotebook.getStack());\r
 183 \r
 184                 check = query.exec();\r
 185                 if (!check) {\r
 186                         logger.log(logger.MEDIUM, ""+dbName+" Table insert failed.");\r
 187                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 188                 }\r
 189         }\r
 190         // Delete the notebook based on a guid\r
 191         public void expungeNotebook(String guid, boolean needsSync) {\r
 192                 boolean check;\r
 193                 Notebook n;\r
 194                 n = getNotebook(guid);\r
 195         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 196 \r
 197         check = query.prepare("delete from "+dbName+" where guid=:guid");\r
 198                 if (!check) {\r
 199                         logger.log(logger.EXTREME, dbName+" SQL delete prepare has failed.");\r
 200                         logger.log(logger.EXTREME, query.lastError().toString());\r
 201                 }\r
 202                 query.bindValue(":guid", guid);\r
 203                 check = query.exec();\r
 204                 if (!check) \r
 205                         logger.log(logger.MEDIUM, dbName+" delete failed.");\r
 206                 \r
 207                 // Signal the parent that work needs to be done\r
 208                 if  (needsSync && n!=null && n.getUpdateSequenceNum() > 0) {\r
 209                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 210                         deletedTable.addDeletedItem(guid, dbName);\r
 211                 }\r
 212         }\r
 213         // Update a notebook\r
 214         public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
 215                 boolean check;\r
 216                 \r
 217                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 218                 \r
 219         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 220         check = query.prepare("Update "+dbName+" set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
 221                         "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
 222                                 "published=:published, isDirty=:isDirty, publishinguri=:uri, "+\r
 223                                 "publishingOrder=:order, " + \r
 224                                 "publishingAscending=:ascending, " +\r
 225                                 "publishingPublicDescription=:desc, " +\r
 226                                 "stack=:stack " +\r
 227                                 "where guid=:guid ");\r
 228         \r
 229                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 230                 query.bindValue(":name", tempNotebook.getName());\r
 231                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 232 \r
 233                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 234                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
 235                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 236                 query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
 237                 \r
 238                 query.bindValue(":published", tempNotebook.isPublished());\r
 239                 query.bindValue(":isDirty", isDirty);\r
 240                 \r
 241                 if (tempNotebook.isPublished()) {\r
 242                         query.bindValue(":uri", tempNotebook.getPublishing().getUri());\r
 243                         query.bindValue(":order", tempNotebook.getPublishing().getOrder().getValue());\r
 244                         query.bindValue(":ascending", tempNotebook.getPublishing().isAscending());\r
 245                         query.bindValue(":desc", tempNotebook.getPublishing().getPublicDescription());\r
 246                 } else {\r
 247                         query.bindValue(":uri", "");\r
 248                         query.bindValue(":order", NoteSortOrder.CREATED.getValue());\r
 249                         query.bindValue(":ascending", false);\r
 250                         query.bindValue(":desc", "");\r
 251                 }\r
 252                 \r
 253                 query.bindValue(":guid", tempNotebook.getGuid());\r
 254                 query.bindValue(":stack", tempNotebook.getStack());\r
 255                 \r
 256                 check = query.exec();\r
 257                 if (!check) {\r
 258                         logger.log(logger.MEDIUM, dbName+" Table update failed.");\r
 259                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 260                 }\r
 261         }\r
 262         // Load notebooks from the database\r
 263         public List<Notebook> getAll() {\r
 264                 Notebook tempNotebook;\r
 265                 List<Notebook> index = new ArrayList<Notebook>();\r
 266                 boolean check;\r
 267                                         \r
 268         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 269                                         \r
 270                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 271                                 "serviceCreated, "+\r
 272                                 "serviceUpdated, "+\r
 273                                 "published, stack, publishinguri, publishingascending, publishingPublicDescription, "+\r
 274                                 "publishingOrder from "+dbName+" order by name");\r
 275                 if (!check)\r
 276                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 277                 while (query.next()) {\r
 278                         tempNotebook = new Notebook();\r
 279                         tempNotebook.setGuid(query.valueString(0));\r
 280                         int sequence = new Integer(query.valueString(1)).intValue();\r
 281                         tempNotebook.setUpdateSequenceNum(sequence);\r
 282                         tempNotebook.setName(query.valueString(2));\r
 283                         tempNotebook.setDefaultNotebook(query.valueBoolean(3, false));\r
 284                         DateFormat indfm = null;\r
 285                         try {\r
 286                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 287 //                              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 288                         } catch (Exception e) { }\r
 289                         try {\r
 290                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 291                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 292                         } catch (ParseException e) {\r
 293                                 e.printStackTrace();\r
 294                         }\r
 295                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 296                         tempNotebook.setStack(query.valueString(7));\r
 297                         if (tempNotebook.isPublished()) {\r
 298                                 Publishing p = new Publishing();\r
 299                                 p.setUri(query.valueString(8));\r
 300                                 p.setAscending(query.valueBoolean(9, false));\r
 301                                 p.setPublicDescription(query.valueString(10));\r
 302                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 303                                 tempNotebook.setPublishing(p);\r
 304                         }\r
 305                         index.add(tempNotebook); \r
 306                 }       \r
 307                 return index;\r
 308         }       \r
 309         public List<Notebook> getAllLocal() {\r
 310                 Notebook tempNotebook;\r
 311                 List<Notebook> index = new ArrayList<Notebook>();\r
 312                 boolean check;\r
 313 \r
 314         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 315                                         \r
 316                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 317                                 "serviceCreated, serviceUpdated, published, stack from "+dbName+" where local=true order by name");\r
 318                 if (!check)\r
 319                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 320                 while (query.next()) {\r
 321                         tempNotebook = new Notebook();\r
 322                         tempNotebook.setGuid(query.valueString(0));\r
 323                         int sequence = new Integer(query.valueString(1)).intValue();\r
 324                         tempNotebook.setUpdateSequenceNum(sequence);\r
 325                         tempNotebook.setName(query.valueString(2));\r
 326                         \r
 327                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 328 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 329                         try {\r
 330                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 331                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 332                         } catch (ParseException e) {\r
 333                                 e.printStackTrace();\r
 334                         }\r
 335                         if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
 336                                 tempNotebook.setStack(query.valueString(7));\r
 337                         index.add(tempNotebook); \r
 338                 }       \r
 339                 return index;\r
 340         }\r
 341         // Archive or un-archive a notebook\r
 342         public void setArchived(String guid, boolean val) {\r
 343                 boolean check;                  \r
 344         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 345                 check = query.prepare("Update "+dbName+" set archived=:archived where guid=:guid");\r
 346                 if (!check)\r
 347                         logger.log(logger.EXTREME, dbName+" SQL archive update has failed.");\r
 348                 query.bindValue(":guid", guid);\r
 349                 query.bindValue(":archived", val);\r
 350                 query.exec();\r
 351         }\r
 352         // Load non-archived notebooks from the database\r
 353         public List<Notebook> getAllArchived() {\r
 354                 Notebook tempNotebook;\r
 355                 List<Notebook> index = new ArrayList<Notebook>();\r
 356                 boolean check;\r
 357                                                 \r
 358         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 359                                         \r
 360                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 361                                 "serviceCreated, serviceUpdated, published, stack, "+\r
 362                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 363                                 "publishingOrder " +\r
 364                                 "from "+dbName+" where archived=true order by name");\r
 365                 if (!check)\r
 366                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 367                 while (query.next()) {\r
 368                         tempNotebook = new Notebook();\r
 369                         tempNotebook.setGuid(query.valueString(0));\r
 370                         int sequence = new Integer(query.valueString(1)).intValue();\r
 371                         tempNotebook.setUpdateSequenceNum(sequence);\r
 372                         tempNotebook.setName(query.valueString(2));\r
 373                         \r
 374                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 375 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 376                         try {\r
 377                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 378                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 379                         } catch (ParseException e) {\r
 380                                 e.printStackTrace();\r
 381                         }\r
 382                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 383                         if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
 384                                 tempNotebook.setStack(query.valueString(7));\r
 385                         \r
 386                         if (tempNotebook.isPublished()) {\r
 387                                 Publishing p = new Publishing();\r
 388                                 p.setUri(query.valueString(8));\r
 389                                 p.setAscending(query.valueBoolean(9, false));\r
 390                                 p.setPublicDescription(query.valueString(10));\r
 391                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 392                                 tempNotebook.setPublishing(p);\r
 393                         }\r
 394                         \r
 395                         index.add(tempNotebook); \r
 396                 }       \r
 397                 return index;\r
 398         }       \r
 399         // Check for a local/remote notebook\r
 400         public boolean isNotebookLocal(String guid) {\r
 401         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 402                 \r
 403                 query.prepare("Select local from "+dbName+" where guid=:guid");\r
 404                 query.bindValue(":guid", guid);\r
 405                 query.exec();\r
 406                 if (!query.next()) {\r
 407                         return false;\r
 408                 }\r
 409                 boolean returnValue = query.valueBoolean(0, false);\r
 410                 return returnValue;\r
 411         }\r
 412         // Check for a local/remote notebook\r
 413         public boolean isNotebookLinked(String guid) {\r
 414         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 415                 \r
 416                 query.prepare("Select linked from "+dbName+" where guid=:guid");\r
 417                 query.bindValue(":guid", guid);\r
 418                 query.exec();\r
 419                 if (!query.next()) {\r
 420                         return false;\r
 421                 }\r
 422                 boolean returnValue = query.valueBoolean(0, false);\r
 423                 return returnValue;\r
 424         }\r
 425         public boolean isReadOnly(String guid) {\r
 426         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 427                 \r
 428                 query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly=true");\r
 429                 query.bindValue(":guid", guid);\r
 430                 query.exec();\r
 431                 if (!query.next()) {\r
 432                         return false;\r
 433                 }\r
 434                 boolean returnValue = query.valueBoolean(0, false);\r
 435                 return returnValue;\r
 436         }\r
 437         // Update a notebook sequence number\r
 438         public void updateNotebookSequence(String guid, int sequence) {\r
 439                 boolean check;\r
 440         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 441                 check = query.prepare("Update "+dbName+" set sequence=:sequence where guid=:guid");\r
 442                 query.bindValue(":guid", guid);\r
 443                 query.bindValue(":sequence", sequence);\r
 444                 query.exec();\r
 445                 if (!check) {\r
 446                         logger.log(logger.MEDIUM, dbName+" sequence update failed.");\r
 447                         logger.log(logger.MEDIUM, query.lastError());\r
 448                 } \r
 449         }\r
 450         // Update a notebook GUID number\r
 451         public void updateNotebookGuid(String oldGuid, String newGuid) {\r
 452         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 453                 query.prepare("Update "+dbName+" set guid=:newGuid where guid=:oldGuid");\r
 454                 query.bindValue(":oldGuid", oldGuid);\r
 455                 query.bindValue(":newGuid", newGuid);\r
 456                 if (!query.exec()) {\r
 457                         logger.log(logger.MEDIUM, dbName+" guid update failed.");\r
 458                         logger.log(logger.MEDIUM, query.lastError());\r
 459                 } \r
 460                 \r
 461                 // Update any notes containing the notebook guid\r
 462                 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
 463                 query.bindValue(":oldGuid", oldGuid);\r
 464                 query.bindValue(":newGuid", newGuid);\r
 465                 if (!query.exec()) {\r
 466                         logger.log(logger.MEDIUM, dbName+" guid update for note failed.");\r
 467                         logger.log(logger.MEDIUM, query.lastError());\r
 468                 } \r
 469                 \r
 470                 // Update any watch folders with the new guid\r
 471                 query = new NSqlQuery(db.getConnection());\r
 472                 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
 473                 query.bindValue(":oldGuid", oldGuid);\r
 474                 query.bindValue(":newGuid", newGuid);\r
 475                 if (!query.exec()) {\r
 476                         logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
 477                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 478                 }\r
 479         }\r
 480         // Get a list of notes that need to be updated\r
 481         public List <Notebook> getDirty() {\r
 482                 Notebook tempNotebook;\r
 483                 List<Notebook> index = new ArrayList<Notebook>();\r
 484                 boolean check;\r
 485                                                 \r
 486         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 487                                         \r
 488                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 489                                 "serviceCreated, serviceUpdated, published, stack, "+\r
 490                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 491                                 "publishingOrder " +\r
 492                                 "from "+dbName+" where isDirty=true and local=false and linked=false");\r
 493                 if (!check) \r
 494                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 495                 while (query.next()) {\r
 496                         tempNotebook = new Notebook();\r
 497                         tempNotebook.setGuid(query.valueString(0));\r
 498                         int sequence = new Integer(query.valueString(1)).intValue();\r
 499                         tempNotebook.setUpdateSequenceNum(sequence);\r
 500                         tempNotebook.setName(query.valueString(2));\r
 501                         \r
 502                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 503                         try {\r
 504                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 505                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 506                         } catch (ParseException e) {\r
 507                                 e.printStackTrace();\r
 508                         }\r
 509                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 510                         if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
 511                                 tempNotebook.setStack(query.valueString(7));\r
 512                         \r
 513                         if (tempNotebook.isPublished()) {\r
 514                                 Publishing p = new Publishing();\r
 515                                 p.setUri(query.valueString(8));\r
 516                                 p.setAscending(query.valueBoolean(9, false));\r
 517                                 p.setPublicDescription(query.valueString(10));\r
 518                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 519                                 if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))\r
 520                                         p.setPublicDescription(null);\r
 521                                 tempNotebook.setPublishing(p);\r
 522                         }\r
 523                         \r
 524                         index.add(tempNotebook);\r
 525                 }       \r
 526                 return index;   \r
 527         }\r
 528         // Get a list of notes that need to be updated\r
 529         public Notebook getNotebook(String guid) {\r
 530                 Notebook tempNotebook;\r
 531                 boolean check;\r
 532                                                 \r
 533         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 534                                         \r
 535                 query.prepare("Select guid, sequence, name, defaultNotebook, " +\r
 536                                 "serviceCreated, serviceUpdated, published, stack, "+\r
 537                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 538                                 "publishingOrder " +\r
 539                                 "from "+dbName+" where guid=:guid");\r
 540                 query.bindValue(":guid", guid);\r
 541                 check  = query.exec();\r
 542                 if (!check) \r
 543                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 544                 while (query.next()) {\r
 545                         tempNotebook = new Notebook();\r
 546                         tempNotebook.setGuid(query.valueString(0));\r
 547                         int sequence = new Integer(query.valueString(1)).intValue();\r
 548                         tempNotebook.setUpdateSequenceNum(sequence);\r
 549                         tempNotebook.setName(query.valueString(2));\r
 550                         \r
 551                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 552                         try {\r
 553                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 554                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 555                         } catch (ParseException e) {\r
 556                                 e.printStackTrace();\r
 557                         }\r
 558                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 559                         if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
 560                                 tempNotebook.setStack(query.valueString(7));\r
 561                         \r
 562                         if (tempNotebook.isPublished()) {\r
 563                                 Publishing p = new Publishing();\r
 564                                 p.setUri(query.valueString(8));\r
 565                                 p.setAscending(query.valueBoolean(9, false));\r
 566                                 p.setPublicDescription(query.valueString(10));\r
 567                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 568                                 if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))\r
 569                                         p.setPublicDescription(null);\r
 570                                 tempNotebook.setPublishing(p);\r
 571                         }\r
 572                         \r
 573                         return tempNotebook;\r
 574                 }       \r
 575                 return null;    \r
 576         }\r
 577         // This is a convience method to check if a tag exists & update/create based upon it\r
 578         public void syncNotebook(Notebook notebook, boolean isDirty) {\r
 579                 if (!exists(notebook.getGuid())) {\r
 580                         addNotebook(notebook, isDirty, isDirty);\r
 581                         return;\r
 582                 }\r
 583                 updateNotebook(notebook, isDirty);\r
 584         }\r
 585         // This is a convience method to check if a tag exists & update/create based upon it\r
 586         public void syncLinkedNotebook(Notebook notebook, boolean isDirty, boolean readOnly) {\r
 587                 if (!exists(notebook.getGuid())) {\r
 588                         addNotebook(notebook, isDirty, false, true, readOnly);\r
 589                         return;\r
 590                 }\r
 591                 updateNotebook(notebook, isDirty);\r
 592         }\r
 593         // does a record exist?\r
 594         private boolean exists(String guid) {\r
 595                 \r
 596                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 597                 \r
 598                 query.prepare("Select guid from "+dbName+" where guid=:guid");\r
 599                 query.bindValue(":guid", guid);\r
 600                 if (!query.exec())\r
 601                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 602                 boolean retval = query.next();\r
 603                 return retval;\r
 604         }\r
 605         // Reset the dirty flag.  Typically done after a sync.\r
 606         public void  resetDirtyFlag(String guid) {\r
 607                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 608                 \r
 609                 query.prepare("Update "+dbName+" set isdirty='false' where guid=:guid");\r
 610                 query.bindValue(":guid", guid);\r
 611                 if (!query.exec())\r
 612                         logger.log(logger.EXTREME, "Error resetting "+dbName+" dirty field.");\r
 613         }\r
 614         // Set the default notebook\r
 615         public void setDefaultNotebook(String guid) {\r
 616                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 617                 \r
 618                 query.prepare("Update "+dbName+" set defaultNotebook=false, isDirty=true where linked=false and defaultNotebook=true");\r
 619                 if (!query.exec())\r
 620                         logger.log(logger.EXTREME, "Error removing default "+dbName+".");\r
 621                 query.prepare("Update "+dbName+" set defaultNotebook=true, isDirty=true where guid=:guid and linked=false");\r
 622                 query.bindValue(":guid", guid);\r
 623                 if (!query.exec())\r
 624                         logger.log(logger.EXTREME, "Error setting default "+dbName+".");\r
 625         }\r
 626         \r
 627         // Get a list of all icons\r
 628         public HashMap<String, QIcon> getAllIcons() {\r
 629                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 630                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 631         \r
 632                 if (!query.exec("SELECT guid, icon from "+dbName+" where ARCHIVED  != true"))\r
 633                         logger.log(logger.EXTREME, "Error executing "+dbName+" getAllIcons select.");\r
 634                 while (query.next()) {\r
 635                         if (query.getBlob(1) != null) {\r
 636                                 String guid = query.valueString(0);\r
 637                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 638                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 639                                 values.put(guid, icon);\r
 640                         }\r
 641                 }\r
 642                 return values;\r
 643         }\r
 644         \r
 645         // Get the notebooks custom icon\r
 646         public QIcon getIcon(String guid) {\r
 647                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 648                 \r
 649                 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))\r
 650                         logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 651                 query.bindValue(":guid", guid);\r
 652                 if (!query.exec())\r
 653                         logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");\r
 654                 if (!query.next() || query.getBlob(0) == null)\r
 655                         return null;\r
 656                 \r
 657                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 658                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 659                 return icon;\r
 660         }\r
 661         // Get the notebooks custom icon\r
 662         public QByteArray getIconAsByteArray(String guid) {\r
 663                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 664                 \r
 665                 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))\r
 666                         logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 667                 query.bindValue(":guid", guid);\r
 668                 if (!query.exec())\r
 669                         logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");\r
 670                 if (!query.next() || query.getBlob(0) == null)\r
 671                         return null;\r
 672                 \r
 673                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 674                 return blob;\r
 675         }\r
 676         // Set the notebooks custom icon\r
 677         public void setIcon(String guid, QIcon icon, String type) {\r
 678                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 679                 if (icon == null) {\r
 680                         if (!query.prepare("update "+dbName+" set icon=null where guid=:guid"))\r
 681                                 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 682                 } else {\r
 683                         if (!query.prepare("update "+dbName+" set icon=:icon where guid=:guid"))\r
 684                                 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 685                         QBuffer buffer = new QBuffer();\r
 686                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 687                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 688                         return;\r
 689                 }\r
 690                 QPixmap p = icon.pixmap(32, 32);\r
 691                 QImage i = p.toImage();\r
 692                 i.save(buffer, type.toUpperCase());\r
 693                 buffer.close();\r
 694                 QByteArray b = new QByteArray(buffer.buffer());\r
 695                 if (!b.isNull() && !b.isEmpty())\r
 696                         query.bindValue(":icon", b.toByteArray());\r
 697                 else\r
 698                         return;\r
 699                 }\r
 700                 query.bindValue(":guid", guid);\r
 701                 if (!query.exec()) \r
 702                         logger.log(logger.LOW, "Error setting "+dbName+" icon. " +query.lastError());\r
 703         }\r
 704         // Set the notebooks custom icon\r
 705         public void setReadOnly(String guid, boolean readOnly) {\r
 706                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 707                 if (!query.prepare("update "+dbName+" set readOnly=:readOnly where guid=:guid"))\r
 708                         logger.log(logger.EXTREME, "Error preparing "+dbName+" read only.");\r
 709                 query.bindValue(":guid", guid);\r
 710                 query.bindValue(":readOnly", readOnly);\r
 711                 if (!query.exec()) \r
 712                         logger.log(logger.LOW, "Error setting "+dbName+" read only. " +query.lastError());\r
 713         }\r
 714 \r
 715         // does a record exist?\r
 716         public String findNotebookByName(String newname) {\r
 717                 \r
 718                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 719                 \r
 720                 query.prepare("Select guid from "+dbName+" where name=:newname");\r
 721                 query.bindValue(":newname", newname);\r
 722                 if (!query.exec())\r
 723                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 724                 String val = null;\r
 725                 if (query.next())\r
 726                         val = query.valueString(0);\r
 727                 return val;\r
 728         }\r
 729         // Get a note tag counts\r
 730         public List<Pair<String,Integer>> getNotebookCounts() {\r
 731                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
 732                 if (notebookCountQuery == null) {\r
 733                         notebookCountQuery = new NSqlQuery(db.getConnection());\r
 734                         notebookCountQuery.prepare("select notebookGuid, count(guid) from note where active=1 group by notebookguid;");\r
 735                 }\r
 736                 if (!notebookCountQuery.exec()) {\r
 737                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
 738                         logger.log(logger.MEDIUM, notebookCountQuery.lastError());\r
 739                         return null;\r
 740                 }\r
 741                 while (notebookCountQuery.next()) {\r
 742                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
 743                         newCount.setFirst(notebookCountQuery.valueString(0));\r
 744                         newCount.setSecond(notebookCountQuery.valueInteger(1));\r
 745                         counts.add(newCount);\r
 746                 }       \r
 747                 return counts;\r
 748         }\r
 749 \r
 750         // Get/Set stacks\r
 751         public void clearStack(String guid) {\r
 752                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 753                 \r
 754                 query.prepare("Update "+dbName+" set stack='' where guid=:guid");\r
 755                 query.bindValue(":guid", guid);\r
 756                 if (!query.exec())\r
 757                         logger.log(logger.EXTREME, "Error clearing "+dbName+" stack.");\r
 758         }\r
 759         // Get/Set stacks\r
 760         public void setStack(String guid, String stack) {\r
 761                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 762                 \r
 763                 query.prepare("Update "+dbName+" set stack=:stack, isDirty=true where guid=:guid");\r
 764                 query.bindValue(":guid", guid);\r
 765                 query.bindValue(":stack", stack);\r
 766                 if (!query.exec())\r
 767                         logger.log(logger.EXTREME, "Error setting notebook stack.");\r
 768         }\r
 769         // Get all stack names\r
 770         public List<String> getAllStackNames() {\r
 771                 List<String> stacks = new ArrayList<String>();\r
 772                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 773                 \r
 774                 if (!query.exec("Select distinct stack from "+dbName)) {\r
 775                         logger.log(logger.EXTREME, "Error getting all stack names.");\r
 776                         return null;\r
 777                 }\r
 778                 \r
 779                 while (query.next()) {\r
 780                         if (query.valueString(0) != null && !query.valueString(0).trim().equals(""))\r
 781                                 stacks.add(query.valueString(0));\r
 782                 }\r
 783                 return stacks;\r
 784         }\r
 785         // Rename a stack\r
 786         public void renameStacks(String oldName, String newName) {\r
 787                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 788                 \r
 789                 if (!query.prepare("update "+dbName+" set stack=:newName where stack=:oldName")) {\r
 790                         logger.log(logger.EXTREME, "Error preparing in renameStacks.");\r
 791                         return;\r
 792                 }\r
 793                 query.bindValue(":oldName", oldName);\r
 794                 query.bindValue(":newName", newName);\r
 795                 if (!query.exec()) {\r
 796                         logger.log(logger.EXTREME, "Error updating stack names");\r
 797                         return;\r
 798                 }\r
 799                 \r
 800                 if (!query.prepare("update SystemIcon set name=:newName where name=:oldName and type='STACK'")) {\r
 801                         logger.log(logger.EXTREME, "Error preparing icon rename in renameStacks.");\r
 802                         return;\r
 803                 }\r
 804                 query.bindValue(":oldName", oldName);\r
 805                 query.bindValue(":newName", newName);\r
 806                 if (!query.exec()) {\r
 807                         logger.log(logger.EXTREME, "Error updating stack names for SystemIcon");\r
 808                         return;\r
 809                 }\r
 810 \r
 811         }\r
 812         // Get/Set stacks\r
 813         public boolean stackExists(String stack) {\r
 814                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 815                 \r
 816                 query.prepare("Select guid from "+dbName+" where stack=:stack limit 1");\r
 817                 query.bindValue(":stack", stack);\r
 818                 if (!query.exec())\r
 819                         logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
 820                 if (query.next())\r
 821                         return true;\r
 822                 else\r
 823                         return false;\r
 824         }\r
 825         // Set Publishing\r
 826         public void setPublishing(String guid, boolean published, Publishing p) {\r
 827                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 828                 \r
 829                 \r
 830                 query.prepare("Update "+dbName+" set publishingPublicDescription=:publishingPublicDescription, " +\r
 831                                 "publishingUri=:publishingUri, publishingOrder=:publishingOrder, published=:published, "+\r
 832                                 "publishingAscending=:publishingAscending, isdirty=true where "+\r
 833                                 "guid=:guid");\r
 834                 query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 835                 query.bindValue(":publishingUri", p.getUri());\r
 836                                 query.bindValue(":publishingOrder", p.getOrder().getValue());\r
 837                 query.bindValue(":publishingAscending", p.isAscending());\r
 838                 query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 839                 query.bindValue(":published", published);\r
 840                 query.bindValue(":guid", guid);\r
 841                 if (!query.exec())\r
 842                         logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
 843         }\r
 844         // Get a notebook by uri\r
 845         public String getNotebookByUri(String uri) {\r
 846                 boolean check;\r
 847                                         \r
 848         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 849                                         \r
 850                 check = query.prepare("Select guid " \r
 851                                 +"from "+dbName+" where publishingUri=:uri");\r
 852                 query.bindValue(":uri", uri);\r
 853                 check = query.exec();\r
 854                 if (!check)\r
 855                         logger.log(logger.EXTREME, "Notebook SQL retrieve guid by uri has failed.");\r
 856                 if (query.next()) {\r
 857                         return query.valueString(0);\r
 858                 }       \r
 859                 return null;\r
 860         }       \r
 861         // Get a notebook's sort order\r
 862         public int getSortColumn(String guid) {\r
 863                 boolean check;\r
 864                                         \r
 865         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 866                                         \r
 867         if (Global.getSortOrder() != Global.View_List_Wide)\r
 868                 check = query.prepare("Select wide_sort_column " \r
 869                                 +"from "+dbName+" where guid=:guid");\r
 870         else\r
 871                 check = query.prepare("Select narrow_sort_column " \r
 872                         +"from "+dbName+" where guid=:guid");\r
 873                 query.bindValue(":guid", guid);\r
 874                 check = query.exec();\r
 875                 if (!check) {\r
 876                         logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");\r
 877                         return -1;\r
 878                 }\r
 879                 if (query.next()) {\r
 880                         return query.valueInteger(0);\r
 881                 }       \r
 882                 return -1;\r
 883         }       \r
 884 \r
 885         // Get a notebook's sort order\r
 886         public int getSortOrder(String guid) {\r
 887                 boolean check;\r
 888                                         \r
 889         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 890                                         \r
 891         if (Global.getSortOrder() != Global.View_List_Wide)\r
 892                 check = query.prepare("Select wide_sort_order " \r
 893                                 +"from "+dbName+" where guid=:guid");\r
 894         else\r
 895                 check = query.prepare("Select narrow_sort_order " \r
 896                         +"from "+dbName+" where guid=:guid");\r
 897                 query.bindValue(":guid", guid);\r
 898                 check = query.exec();\r
 899                 if (!check) {\r
 900                         logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");\r
 901                         return -1;\r
 902                 }\r
 903                 if (query.next()) {\r
 904                         return query.valueInteger(0);\r
 905                 }       \r
 906                 return -1;\r
 907         }       \r
 908         // Get a notebook's sort order\r
 909         public void setSortOrder(String guid, int column, int order) {\r
 910                 boolean check;\r
 911                                         \r
 912         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 913                                         \r
 914         if (Global.getSortOrder() != Global.View_List_Wide)\r
 915                 check = query.prepare("Update "+dbName+" set wide_sort_order=:order, wide_sort_column=:column where guid=:guid");\r
 916         else\r
 917                 check = query.prepare("Update "+dbName+" set narrow_sort_order=:order, narrow_sort_column=:column where guid=:guid");\r
 918 \r
 919                 query.bindValue(":guid", guid);\r
 920                 query.bindValue(":order", order);\r
 921                 query.bindValue(":column", column);\r
 922                 check = query.exec();\r
 923                 if (!check)\r
 924                         logger.log(logger.EXTREME, "Notebook SQL set sort order has failed.");\r
 925         }       \r
 926         // Is a notebook a linked notebook?\r
 927         public boolean isLinked(String guid) {\r
 928                 boolean check;\r
 929                 \r
 930         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 931                                         \r
 932                 check = query.prepare("Select guid " \r
 933                                 +"from "+dbName+" where guid=:guid and linked=true");\r
 934                 query.bindValue(":guid", guid);\r
 935                 check = query.exec();\r
 936                 if (!check)\r
 937                         logger.log(logger.EXTREME, "Notebook SQL isLinked failed.");\r
 938                 if (query.next()) {\r
 939                         return true;\r
 940                 }       \r
 941                 return false;\r
 942         }\r
 943 \r
 944         // Given a notebook, what tags are valid for it?\r
 945         public List<String> getValidLinkedTags(String guid) {\r
 946                 boolean check;\r
 947                 List<String> tags = new ArrayList<String>();\r
 948                 \r
 949         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 950                 check = query.prepare("select distinct tagGuid from noteTags " +\r
 951                                 "where noteGuid in " +\r
 952                                 "(SELECT guid from note where notebookguid=:guid)");\r
 953                 query.bindValue(":guid", guid);\r
 954                 check = query.exec();\r
 955                 if (!check)\r
 956                         logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
 957                 while (query.next()) {\r
 958                         tags.add(query.valueString(0));\r
 959                 }       \r
 960                 return tags;\r
 961                 \r
 962                 \r
 963         }\r
 964         // Given a notebook, what tags are valid for it?\r
 965         public void deleteLinkedTags(String guid) {\r
 966                 \r
 967         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 968                 query.prepare("select distinct tagguid from noteTags " +\r
 969                                 "where noteGuid in " +\r
 970                                 "(SELECT guid from note where notebookguid=:guid)");\r
 971                 query.bindValue(":guid", guid);\r
 972                 boolean check = query.exec();\r
 973                 if (!check)\r
 974                         logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
 975                 while(query.next()) {\r
 976                         db.getTagTable().expungeTag(query.valueString(0), false);\r
 977                 }\r
 978                 \r
 979                 \r
 980                 query.prepare("delete from note " +\r
 981                                 "where notebookguid=:guid");\r
 982                 query.bindValue(":guid", guid);\r
 983                 check = query.exec();\r
 984                 if (!check)\r
 985                         logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
 986 \r
 987                 \r
 988                 return;\r
 989                 \r
 990                 \r
 991         }\r
 992         \r
 993         // Given a notebook, what tags are valid for it?\r
 994         public void convertFromSharedNotebook(String guid, boolean local) {\r
 995                 \r
 996         NSqlQuery query = new NSqlQuery(db.getConnection());  \r
 997         \r
 998         query.prepare("Update Notebook set sequence=0, published=false, isdirty=true, local=:local, publishinguri=''"\r
 999                                 +" where guid=:guid");\r
1000                 query.bindValue(":guid", guid);\r
1001                 if (local)\r
1002                         query.bindValue(":local", true);\r
1003                 else\r
1004                         query.bindValue(":local", false);\r
1005                 \r
1006                 if (!query.exec())\r
1007                         logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook error.");\r
1008                 \r
1009         query.prepare("Update Note set updatesequencenumber=0, isdirty=true"\r
1010                                 +" where notebookguid=:guid");\r
1011                 query.bindValue(":guid", guid);\r
1012                 if (!query.exec())\r
1013                         logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook #2 error.");\r
1014                         \r
1015                 return;\r
1016                 \r
1017                 \r
1018         }\r
1019 }\r
1020 \r