src/cx/fbn/nevernote/sql/runners/RSavedSearchTable.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.runners;\r
  22 \r
  23 import java.util.ArrayList;\r
  24 import java.util.List;\r
  25 \r
  26 import com.evernote.edam.type.QueryFormat;\r
  27 import com.evernote.edam.type.SavedSearch;\r
  28 \r
  29 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  30 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  31 \r
  32 public class RSavedSearchTable {\r
  33         private final ApplicationLogger                 logger;\r
  34         private final RDatabaseConnection               db;\r
  35 \r
  36         \r
  37         // Constructor\r
  38         public RSavedSearchTable(ApplicationLogger l, RDatabaseConnection d) {\r
  39                 logger = l;\r
  40                 db = d;\r
  41         }\r
  42         // Create the table\r
  43         public void createTable() {\r
  44                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  45         logger.log(logger.HIGH, "Creating table SavedSearch...");\r
  46         if (!query.exec("Create table SavedSearch (guid varchar primary key, " +\r
  47                         "name varchar, query varchar, format integer, sequence integer, isDirty boolean)"))\r
  48                 logger.log(logger.HIGH, "Table SavedSearch creation FAILED!!!"); \r
  49         }\r
  50         // Drop the table\r
  51         public void dropTable() {\r
  52                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  53                 query.exec("Drop table SavedSearch");\r
  54         }\r
  55         // get all tags\r
  56         public List<SavedSearch> getAll() {\r
  57                 SavedSearch tempSearch;\r
  58                 List<SavedSearch> index = new ArrayList<SavedSearch>();\r
  59                 boolean check;\r
  60                                                 \r
  61         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  62                                                 \r
  63                 check = query.exec("Select guid, name, query, format, sequence"\r
  64                                 +" from SavedSearch");\r
  65                 if (!check)\r
  66                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in getAll().");\r
  67                 while (query.next()) {\r
  68                         tempSearch = new SavedSearch();\r
  69                         tempSearch.setGuid(query.valueString(0));\r
  70                         tempSearch.setName(query.valueString(1));\r
  71                         tempSearch.setQuery(query.valueString(2));\r
  72                         int fmt = new Integer(query.valueString(3));\r
  73                         if (fmt == 1)\r
  74                                 tempSearch.setFormat(QueryFormat.USER);\r
  75                         else\r
  76                                 tempSearch.setFormat(QueryFormat.SEXP);\r
  77                         int sequence = new Integer(query.valueString(4)).intValue();\r
  78                         tempSearch.setUpdateSequenceNum(sequence);\r
  79                         index.add(tempSearch); \r
  80                 }       \r
  81                 return index;\r
  82         }\r
  83         public SavedSearch getSavedSearch(String guid) {\r
  84                 SavedSearch tempSearch = null;\r
  85                 boolean check;\r
  86                         \r
  87         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  88                                                 \r
  89                 check = query.prepare("Select guid, name, query, format, sequence"\r
  90                                 +" from SavedSearch where guid=:guid");\r
  91                 if (!check)\r
  92                         logger.log(logger.EXTREME, "SavedSearch SQL prepare has failed in getSavedSearch.");\r
  93                 query.bindValue(":guid", guid);\r
  94                 query.exec();\r
  95                 if (!check)\r
  96                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in getSavedSearch.");\r
  97                 if (query.next()) {\r
  98                         tempSearch = new SavedSearch();\r
  99                         tempSearch.setGuid(query.valueString(0));\r
 100                         tempSearch.setName(query.valueString(1));\r
 101                         tempSearch.setQuery(query.valueString(2));\r
 102                         int fmt = new Integer(query.valueString(3));\r
 103                         if (fmt == 1)\r
 104                                 tempSearch.setFormat(QueryFormat.USER);\r
 105                         else\r
 106                                 tempSearch.setFormat(QueryFormat.SEXP);\r
 107                         int sequence = new Integer(query.valueString(4)).intValue();\r
 108                         tempSearch.setUpdateSequenceNum(sequence);\r
 109                 }\r
 110                 return tempSearch;\r
 111         }\r
 112         // Update a tag\r
 113         public void updateSavedSearch(SavedSearch search, boolean isDirty) {\r
 114                 boolean check;\r
 115         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 116                 check = query.prepare("Update SavedSearch set sequence=:sequence, "+\r
 117                         "name=:name, isDirty=:isDirty, query=:query, format=:format "\r
 118                         +"where guid=:guid");\r
 119        \r
 120                 if (!check) {\r
 121                         logger.log(logger.EXTREME, "SavedSearch SQL update prepare has failed.");\r
 122                         logger.log(logger.EXTREME, query.lastError().toString());\r
 123                 }\r
 124                 query.bindValue(":sequence", search.getUpdateSequenceNum());\r
 125                 query.bindValue(":name", search.getName());\r
 126                 query.bindValue(":isDirty", isDirty);\r
 127                 query.bindValue(":query", search.getQuery());\r
 128                 if (search.getFormat() == QueryFormat.USER)\r
 129                         query.bindValue(":format", 1);\r
 130                 else\r
 131                         query.bindValue(":format", 2);\r
 132                 \r
 133                 query.bindValue(":guid", search.getGuid());\r
 134                 \r
 135                 check = query.exec();\r
 136                 if (!check) {\r
 137                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
 138                         logger.log(logger.EXTREME, query.lastError().toString());\r
 139                 }\r
 140         }\r
 141         // Delete a tag\r
 142         public void expungeSavedSearch(String guid, boolean needsSync) {\r
 143                 boolean check;\r
 144         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 145 \r
 146         check = query.prepare("delete from SavedSearch "\r
 147                                 +"where guid=:guid");\r
 148                 if (!check) {\r
 149                         logger.log(logger.EXTREME, "SavedSearch SQL delete prepare has failed.");\r
 150                         logger.log(logger.EXTREME, query.lastError().toString());\r
 151                 }\r
 152                 query.bindValue(":guid", guid);\r
 153                 check = query.exec();\r
 154                 if (!check) {\r
 155                         logger.log(logger.MEDIUM, "Saved Search delete failed.");\r
 156                         logger.log(logger.EXTREME, query.lastError().toString());\r
 157                 }\r
 158 \r
 159                 // Add the work to the parent queue\r
 160                 if (needsSync) {\r
 161                         RDeletedTable del = new RDeletedTable(logger, db);\r
 162                         del.addDeletedItem(guid, "SavedSearch");\r
 163                 }\r
 164         }\r
 165         // Save a tag\r
 166         public void addSavedSearch(SavedSearch search, boolean isDirty) {\r
 167                 boolean check;\r
 168         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 169                 check = query.prepare("Insert Into SavedSearch (guid, query, sequence, format, name, isDirty)"\r
 170                                 +" Values(:guid, :query, :sequence, :format, :name, :isDirty)");\r
 171                 if (!check) {\r
 172                         logger.log(logger.EXTREME, "Search SQL insert prepare has failed.");\r
 173                         logger.log(logger.EXTREME, query.lastError().toString());\r
 174                 }\r
 175                 query.bindValue(":guid", search.getGuid());\r
 176                 query.bindValue(":query", search.getQuery());\r
 177                 query.bindValue(":sequence", search.getUpdateSequenceNum());\r
 178                 if (search.getFormat() == QueryFormat.USER)\r
 179                         query.bindValue(":format", 1);\r
 180                 else\r
 181                         query.bindValue(":format", 2);\r
 182                 query.bindValue(":name", search.getName());\r
 183                 query.bindValue(":isDirty", isDirty);\r
 184         \r
 185                 check = query.exec();\r
 186                 if  (!check) {\r
 187                         logger.log(logger.MEDIUM, "Search Table insert failed.");\r
 188                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 189                 }\r
 190         }\r
 191         // Update a tag sequence number\r
 192         public void updateSavedSearchSequence(String guid, int sequence) {\r
 193                 boolean check;\r
 194                 ;\r
 195         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 196                 check = query.prepare("Update SavedSearch set sequence=:sequence where guid=:guid");\r
 197                 query.bindValue(":sequence", sequence);\r
 198                 query.bindValue(":guid", guid);\r
 199                 query.exec();\r
 200                 if (!check) {\r
 201                         logger.log(logger.MEDIUM, "SavedSearch sequence update failed.");\r
 202                         logger.log(logger.MEDIUM, query.lastError());\r
 203                 }\r
 204         }\r
 205         // Update a tag sequence number\r
 206         public void updateSavedSearchGuid(String oldGuid, String newGuid) {\r
 207                 boolean check;\r
 208         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 209                 check = query.prepare("Update SavedSearch set guid=:newGuid where guid=:oldGuid");\r
 210                 query.bindValue(":newGuid", newGuid);\r
 211                 query.bindValue(":oldGuid", oldGuid);\r
 212                 query.exec();\r
 213                 if (!check) {\r
 214                         logger.log(logger.MEDIUM, "SavedSearch guid update failed.");\r
 215                         logger.log(logger.MEDIUM, query.lastError());\r
 216                 }\r
 217         }\r
 218         // Get dirty tags\r
 219         public List<SavedSearch> getDirty() {\r
 220                 SavedSearch search;\r
 221                 List<SavedSearch> index = new ArrayList<SavedSearch>();\r
 222                 boolean check;\r
 223                                                 \r
 224         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 225                                                 \r
 226                 check = query.exec("Select guid, query, sequence, name, format"\r
 227                                 +" from SavedSearch where isDirty = true");\r
 228                 if (!check)\r
 229                         logger.log(logger.EXTREME, "SavedSearch getDirty prepare has failed.");\r
 230                 while (query.next()) {\r
 231                         search = new SavedSearch();\r
 232                         search.setGuid(query.valueString(0));\r
 233                         search.setQuery(query.valueString(1));\r
 234                         int sequence = new Integer(query.valueString(2)).intValue();\r
 235                         search.setUpdateSequenceNum(sequence);\r
 236                         search.setName(query.valueString(3));\r
 237                         int fmt = new Integer(query.valueString(4)).intValue();\r
 238                         if (fmt == 1)\r
 239                                 search.setFormat(QueryFormat.USER);\r
 240                         else\r
 241                                 search.setFormat(QueryFormat.SEXP);\r
 242                         index.add(search); \r
 243                 }       \r
 244                 return index;\r
 245         }\r
 246         // Find a guid based upon the name\r
 247         public String findSavedSearchByName(String name) {\r
 248                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 249                 \r
 250                 query.prepare("Select guid from SavedSearch where name=:name");\r
 251                 query.bindValue(":name", name);\r
 252                 if (!query.exec())\r
 253                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in findSavedSearchByName().");\r
 254                 String val = null;\r
 255                 if (query.next())\r
 256                         val = query.valueString(0);\r
 257                 return val;\r
 258         }\r
 259         // given a guid, does the tag exist\r
 260         public boolean exists(String guid) {\r
 261                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 262                 query.prepare("Select guid from SavedSearch where guid=:guid");\r
 263                 query.bindValue(":guid", guid);\r
 264                 if (!query.exec())\r
 265                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in exists().");\r
 266                 boolean retval = query.next();\r
 267                 return retval;\r
 268         }\r
 269         // This is a convience method to check if a tag exists & update/create based upon it\r
 270         public void syncSavedSearch(SavedSearch search, boolean isDirty) {\r
 271                 if (exists(search.getGuid()))\r
 272                         updateSavedSearch(search, isDirty);\r
 273                 else\r
 274                         addSavedSearch(search, isDirty);\r
 275         }\r
 276         public void  resetDirtyFlag(String guid) {\r
 277                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 278                 \r
 279                 query.prepare("Update SavedSearch set isdirty=false where guid=:guid");\r
 280                 query.bindValue(":guid", guid);\r
 281                 if (!query.exec())\r
 282                         logger.log(logger.EXTREME, "Error resetting SavedSearch dirty field in resetDirtyFlag().");\r
 283         }\r
 284 }\r