src/cx/fbn/nevernote/sql/WordsTable.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 cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  24 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  25 \r
  26 public class WordsTable {\r
  27         private final ApplicationLogger                 logger;\r
  28         private final DatabaseConnection                db;\r
  29 \r
  30         \r
  31         // Constructor\r
  32         public WordsTable(ApplicationLogger l, DatabaseConnection d) {\r
  33                 logger = l;\r
  34                 db = d;\r
  35         }\r
  36         // Create the table\r
  37         public void createTable() {\r
  38                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  39         logger.log(logger.HIGH, "Creating table WORDS ...");\r
  40         if (!query.exec("create table words (word varchar, guid varchar, source varchar, weight int, primary key (word, guid, source));")) {\r
  41                 logger.log(logger.HIGH, "Table WORDS creation FAILED!!!");   \r
  42                 logger.log(logger.HIGH, query.lastError());\r
  43         }   \r
  44         }\r
  45         // Drop the table\r
  46         public void dropTable() {\r
  47                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  48                 query.exec("drop table words");\r
  49         }\r
  50         // Count unindexed notes\r
  51         public int getWordCount() {\r
  52         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  53                 query.exec("select count(*) from words");\r
  54                 query.next(); \r
  55                 int returnValue = new Integer(query.valueString(0));\r
  56                 return returnValue;\r
  57         }\r
  58 \r
  59         // Clear out the word index table\r
  60         public void clearWordIndex() {\r
  61                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  62         logger.log(logger.HIGH, "DELETE FROM WORDS");\r
  63         \r
  64         boolean check = query.exec("DELETE FROM WORDS");\r
  65         if (!check)\r
  66                 logger.log(logger.HIGH, "Table WORDS clear has FAILED!!!");  \r
  67         }       \r
  68 \r
  69         //********************************************************************************\r
  70         //********************************************************************************\r
  71         //* Support adding & deleting index words\r
  72         //********************************************************************************\r
  73         //********************************************************************************\r
  74         public void expungeFromWordIndex(String guid, String type) {\r
  75                 NSqlQuery deleteWords = new NSqlQuery(db.getConnection());\r
  76                 if (!deleteWords.prepare("delete from words where guid=:guid and source=:source")) {\r
  77                         logger.log(logger.EXTREME, "Note SQL select prepare deleteWords has failed.");\r
  78                         logger.log(logger.MEDIUM, deleteWords.lastError());\r
  79                 }\r
  80         \r
  81                 deleteWords.bindValue(":guid", guid);\r
  82                 deleteWords.bindValue(":source", type);\r
  83                 deleteWords.exec();\r
  84 \r
  85         }\r
  86         // Reindex a note\r
  87         public synchronized void addWordToNoteIndex(String guid, String word, String type, Integer weight) {\r
  88                 NSqlQuery findWords = new NSqlQuery(db.getConnection());\r
  89                 if (!findWords.prepare("Select weight from words where guid=:guid and source=:type and word=:word")) {\r
  90                         logger.log(logger.MEDIUM, "Prepare failed in addWordToNoteIndex()");\r
  91                         logger.log(logger.MEDIUM, findWords.lastError());\r
  92                 }\r
  93                 \r
  94                 findWords.bindValue(":guid", guid);\r
  95                 findWords.bindValue(":type", type);\r
  96                 findWords.bindValue(":word", word);\r
  97                 \r
  98                 boolean addNeeded = true;\r
  99                 findWords.exec();\r
 100                 // If we have a match, find out which has the heigher weight & update accordingly\r
 101                 if (findWords.next()) {\r
 102                         int recordWeight = new Integer(findWords.valueString(0));\r
 103                         addNeeded = false;\r
 104                         if (recordWeight < weight) {\r
 105                                 NSqlQuery updateWord = new NSqlQuery(db.getConnection());\r
 106                                 if (!updateWord.prepare("Update words set weight=:weight where guid=:guid and source=:type and word=:word")) {\r
 107                                         logger.log(logger.MEDIUM, "Prepare failed for find words in addWordToNoteIndex()");\r
 108                                         logger.log(logger.MEDIUM, findWords.lastError());                                       \r
 109                                 }\r
 110                                 \r
 111                                 updateWord.bindValue(":weight", weight);\r
 112                                 updateWord.bindValue(":guid", guid);\r
 113                                 updateWord.bindValue(":type", type);\r
 114                                 updateWord.bindValue(":word",word);\r
 115                                 updateWord.exec();\r
 116                         }\r
 117                 }\r
 118                 \r
 119                 \r
 120                 if (!addNeeded)\r
 121                         return;\r
 122                 \r
 123                 NSqlQuery insertWords = new NSqlQuery(db.getConnection());\r
 124                 if (!insertWords.prepare("Insert Into Words (word, guid, weight, source)"\r
 125                                 +" Values(:word, :guid, :weight, :type )")) {\r
 126                         logger.log(logger.EXTREME, "Note SQL select prepare checkWords has failed.");\r
 127                         logger.log(logger.MEDIUM, insertWords.lastError());\r
 128                 }\r
 129                 insertWords.bindValue(":word", word);\r
 130                 insertWords.bindValue(":guid", guid);\r
 131                 insertWords.bindValue(":weight", weight);\r
 132                 insertWords.bindValue(":type", type);\r
 133                 if (!insertWords.exec()) {\r
 134                         String err = insertWords.lastError();\r
 135                         logger.log(logger.MEDIUM, "Error inserting words into index: " +err);\r
 136                 }\r
 137         }\r
 138 \r
 139 \r
 140 }\r