From ef34ce254debb2dc76d504e236980e1c7f3329a0 Mon Sep 17 00:00:00 2001 From: yuki Date: Tue, 13 Aug 2013 18:08:38 +0900 Subject: [PATCH] =?utf8?q?=E3=83=AD=E3=83=BC=E3=82=AB=E3=83=AB=E6=B7=BB?= =?utf8?q?=E4=BB=98=E3=83=95=E3=82=A1=E3=82=A4=E3=83=AB=E3=82=82Apache=20L?= =?utf8?q?ucene=E3=82=92=E4=BD=BF=E3=81=A3=E3=81=9F=E5=85=A8=E6=96=87?= =?utf8?q?=E6=A4=9C=E7=B4=A2=E3=81=AE=E5=AF=BE=E8=B1=A1=E3=81=AB=E8=BF=BD?= =?utf8?q?=E5=8A=A0=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .classpath | 7 +- src/cx/fbn/nevernote/Global.java | 78 ++++++++----- src/cx/fbn/nevernote/NeverNote.java | 4 +- src/cx/fbn/nevernote/dialog/ConfigDialog.java | 17 ++- src/cx/fbn/nevernote/dialog/ConfigIndexPage.java | 54 ++++----- src/cx/fbn/nevernote/sql/DatabaseConnection.java | 19 ++- src/cx/fbn/nevernote/sql/NoteResourceTable.java | 22 ++++ src/cx/fbn/nevernote/sql/REnSearch.java | 38 ++++-- src/cx/fbn/nevernote/threads/IndexRunner.java | 142 ++++++++++++----------- 9 files changed, 239 insertions(+), 142 deletions(-) diff --git a/.classpath b/.classpath index 77ad830..7b170d0 100644 --- a/.classpath +++ b/.classpath @@ -25,8 +25,9 @@ - - - + + + + diff --git a/src/cx/fbn/nevernote/Global.java b/src/cx/fbn/nevernote/Global.java index f73ea0a..5b09540 100644 --- a/src/cx/fbn/nevernote/Global.java +++ b/src/cx/fbn/nevernote/Global.java @@ -53,6 +53,7 @@ import cx.fbn.nevernote.config.StartupConfig; import cx.fbn.nevernote.gui.ContainsAttributeFilterTable; import cx.fbn.nevernote.gui.DateAttributeFilterTable; import cx.fbn.nevernote.gui.ShortcutKeys; +import cx.fbn.nevernote.sql.DatabaseConnection; import cx.fbn.nevernote.sql.driver.NSqlQuery; import cx.fbn.nevernote.utilities.ApplicationLogger; import cx.fbn.nevernote.utilities.Pair; @@ -140,7 +141,7 @@ public class Global { //public static int minimumWordCount = 2; // Regular expression to parse text with when indexing - private static String wordRegex; +// private static String wordRegex; // Experimental fixes. Set via Edit/Preferences/Debugging public static boolean enableCarriageReturnFix = false; @@ -222,10 +223,10 @@ public class Global { getServer(); // Setup URL to connect to // Get regular expressions used to parse out words - settings.beginGroup("General"); - String regex = (String) settings.value("regex", "[,\\s]+"); - setWordRegex(regex); - settings.endGroup(); +// settings.beginGroup("General"); +// String regex = (String) settings.value("regex", "[,\\s]+"); +// setWordRegex(regex); +// settings.endGroup(); //Setup debugging information settings.beginGroup("Debug"); @@ -252,12 +253,12 @@ public class Global { } // Get/Set word parsing regular expression - public static String getWordRegex() { - return wordRegex; - } - public static void setWordRegex(String r) { - wordRegex = r; - } +// public static String getWordRegex() { +// return wordRegex; +// } +// public static void setWordRegex(String r) { +// wordRegex = r; +// } // Set the debug message level public static void setMessageLevel(String msglevel) { @@ -1881,18 +1882,18 @@ public class Global { settings.endGroup(); } // Get/Set characters that shouldn't be removed from a word - public static String getSpecialIndexCharacters() { - settings.beginGroup("Index"); - String text = (String)settings.value("specialCharacters", ""); - settings.endGroup(); - return text; - } - public static void setSpecialIndexCharacters(String value) { - settings.beginGroup("Index"); - settings.setValue("specialCharacters", value); - settings.endGroup(); - databaseCache = value; - } +// public static String getSpecialIndexCharacters() { +// settings.beginGroup("Index"); +// String text = (String)settings.value("specialCharacters", ""); +// settings.endGroup(); +// return text; +// } +// public static void setSpecialIndexCharacters(String value) { +// settings.beginGroup("Index"); +// settings.setValue("specialCharacters", value); +// settings.endGroup(); +// databaseCache = value; +// } //***************************************************************************** // Control how tag selection behaves (should they be "and" or "or" selections @@ -2345,7 +2346,8 @@ public class Global { } // 全文検索機能の対象となるテーブルとカラムを再構築 - public static boolean rebuildFullTextTarget(NSqlQuery query) { + public static boolean rebuildFullTextNoteTarget(DatabaseConnection dbConn) { + NSqlQuery nQuery = new NSqlQuery(dbConn.getConnection()); StringBuilder noteTableTarget = new StringBuilder(); boolean success = true; @@ -2359,12 +2361,30 @@ public class Global { noteTableTarget.append("TITLE"); } - // TODO 他の項目もあとで追加 - if (noteTableTarget.length() > 0) { - query.prepare("CALL FTL_CREATE_INDEX('PUBLIC', 'NOTE', :column);"); - query.bindValue(":column", noteTableTarget.toString()); - if (!query.exec()) { + nQuery.prepare("CALL FTL_CREATE_INDEX('PUBLIC', 'NOTE', :column);"); + nQuery.bindValue(":column", noteTableTarget.toString()); + if (!nQuery.exec()) { + success = false; + } + } + + return success; + } + + public static boolean rebuildFullTextResourceTarget(DatabaseConnection dbConn) { + NSqlQuery rQuery = new NSqlQuery(dbConn.getResourceConnection()); + StringBuilder resourceTableTarget = new StringBuilder(); + boolean success = true; + + if (Global.indexAttachmentsLocally()) { + resourceTableTarget.append("RESOURCETEXT"); + } + + if (resourceTableTarget.length() > 0) { + rQuery.prepare("CALL FTL_CREATE_INDEX('PUBLIC', 'NOTERESOURCES', :column);"); + rQuery.bindValue(":column", resourceTableTarget.toString()); + if (!rQuery.exec()) { success = false; } } diff --git a/src/cx/fbn/nevernote/NeverNote.java b/src/cx/fbn/nevernote/NeverNote.java index 1607030..48bc3f5 100644 --- a/src/cx/fbn/nevernote/NeverNote.java +++ b/src/cx/fbn/nevernote/NeverNote.java @@ -455,7 +455,7 @@ public class NeverNote extends QMainWindow{ indexRunner.indexImageRecognition = Global.indexImageRecognition(); // indexRunner.indexNoteBody = Global.indexNoteBody(); // indexRunner.indexNoteTitle = Global.indexNoteTitle(); - indexRunner.specialIndexCharacters = Global.getSpecialIndexCharacters(); +// indexRunner.specialIndexCharacters = Global.getSpecialIndexCharacters(); indexThread.start(); synchronizeAnimationTimer = new QTimer(); @@ -1463,7 +1463,7 @@ public class NeverNote extends QMainWindow{ indexRunner.indexAttachmentsLocally = Global.indexAttachmentsLocally(); // indexRunner.indexNoteBody = Global.indexNoteBody(); // indexRunner.indexNoteTitle = Global.indexNoteTitle(); - indexRunner.specialIndexCharacters = Global.getSpecialIndexCharacters(); +// indexRunner.specialIndexCharacters = Global.getSpecialIndexCharacters(); indexRunner.indexImageRecognition = Global.indexImageRecognition(); if (Global.showTrayIcon() || Global.minimizeOnClose()) trayIcon.show(); diff --git a/src/cx/fbn/nevernote/dialog/ConfigDialog.java b/src/cx/fbn/nevernote/dialog/ConfigDialog.java index 2613102..fc60069 100644 --- a/src/cx/fbn/nevernote/dialog/ConfigDialog.java +++ b/src/cx/fbn/nevernote/dialog/ConfigDialog.java @@ -180,12 +180,12 @@ public class ConfigDialog extends QDialog { Global.setIndexNoteTitle(indexPage.getIndexNoteTitle()); Global.setIndexImageRecognition(indexPage.getIndexImageRecognition()); // Global.setAutomaticWildcardSearches(indexPage.getAutomaticWildcardSearches()); - Global.setSpecialIndexCharacters(indexPage.getSpecialCharacters()); +// Global.setSpecialIndexCharacters(indexPage.getSpecialCharacters()); Global.setIncludeTagChildren(appearancePage.getIncludeTagChildren()); Global.setDisplayRightToLeft(appearancePage.getDisplayRightToLeft()); Global.userStoreUrl = "https://"+debugPage.getServer()+"/edam/user"; - Global.setWordRegex(indexPage.getRegex()); +// Global.setWordRegex(indexPage.getRegex()); Global.setRecognitionWeight(indexPage.getRecognitionWeight()); Global.setIndexThreadSleepInterval(indexPage.getSleepInterval()); Global.setMessageLevel( debugPage.getDebugLevel()); @@ -243,9 +243,14 @@ public class ConfigDialog extends QDialog { Global.setRensoListItemMaximum(rensoNoteListPage.getRensoListItemMaximum()); // 全文検索の対象項目を再設定 - NSqlQuery query = new NSqlQuery(conn.getConnection()); - query.exec("CALL FTL_DROP_ALL();"); // カラム単位で削除できないので一度全部消して、再構築 - Global.rebuildFullTextTarget(query); + NSqlQuery nQuery = new NSqlQuery(conn.getConnection()); + NSqlQuery rQuery = new NSqlQuery(conn.getResourceConnection()); + // カラム単位で削除できないので一度全部消す + nQuery.exec("CALL FTL_DROP_ALL();"); + rQuery.exec("CALL FTL_DROP_ALL();"); + // 再構築 + Global.rebuildFullTextNoteTarget(conn); + Global.rebuildFullTextResourceTarget(conn); close(); } @@ -356,7 +361,7 @@ public class ConfigDialog extends QDialog { appearancePage.setDisplayRightToLeft(Global.displayRightToLeft()); appearancePage.setStartupNotebook(Global.getStartupNotebook()); - indexPage.setRegex(Global.getWordRegex()); +// indexPage.setRegex(Global.getWordRegex()); indexPage.setSleepInterval(Global.getIndexThreadSleepInterval()); connectionPage.setSyncInterval(Global.getSyncInterval()); diff --git a/src/cx/fbn/nevernote/dialog/ConfigIndexPage.java b/src/cx/fbn/nevernote/dialog/ConfigIndexPage.java index 3d1ec9b..2420a9f 100644 --- a/src/cx/fbn/nevernote/dialog/ConfigIndexPage.java +++ b/src/cx/fbn/nevernote/dialog/ConfigIndexPage.java @@ -45,9 +45,9 @@ public class ConfigIndexPage extends QWidget { private final QCheckBox indexImageRecognition; private final QCheckBox indexTitle; // private final QCheckBox automaticWildcard; - private final QLineEdit specialStrip; +// private final QLineEdit specialStrip; private final QCheckBox indexBody; - private final QLineEdit regexEdit; +// private final QLineEdit regexEdit; public ConfigIndexPage(QWidget parent) { // super(parent); @@ -79,8 +79,8 @@ public class ConfigIndexPage extends QWidget { // automaticWildcard = new QCheckBox(tr("Automatically Wildcard All Searches")); // automaticWildcard.setChecked(Global.automaticWildcardSearches()); - specialStrip = new QLineEdit(); - specialStrip.setText(Global.getSpecialIndexCharacters()); +// specialStrip = new QLineEdit(); +// specialStrip.setText(Global.getSpecialIndexCharacters()); QVBoxLayout attachmentLayout = new QVBoxLayout(); attachmentLayout.addWidget(indexBody); @@ -89,10 +89,10 @@ public class ConfigIndexPage extends QWidget { attachmentLayout.addWidget(indexImageRecognition); // attachmentLayout.addWidget(automaticWildcard); - QHBoxLayout specialCharLayout = new QHBoxLayout(); - specialCharLayout.addWidget(new QLabel(tr("Special Word Characters"))); - specialCharLayout.addWidget(specialStrip); - attachmentLayout.addLayout(specialCharLayout); +// QHBoxLayout specialCharLayout = new QHBoxLayout(); +// specialCharLayout.addWidget(new QLabel(tr("Special Word Characters"))); +// specialCharLayout.addWidget(specialStrip); +// attachmentLayout.addLayout(specialCharLayout); attachmentGroup.setLayout(attachmentLayout); // Index sleep interval @@ -109,22 +109,22 @@ public class ConfigIndexPage extends QWidget { sleepGroup.setLayout(sleepLayout); // Regular Expressions for word parsing - QGroupBox regexGroup = new QGroupBox(tr("Word Parse")); - QLabel regexLabel = new QLabel(tr("Regular Expression")); - regexEdit = new QLineEdit(); - regexEdit.setText(Global.getWordRegex()); - - QHBoxLayout regexLayout = new QHBoxLayout(); - regexLayout.addWidget(regexLabel); - regexLayout.addWidget(regexEdit); - regexGroup.setLayout(regexLayout); +// QGroupBox regexGroup = new QGroupBox(tr("Word Parse")); +// QLabel regexLabel = new QLabel(tr("Regular Expression")); +// regexEdit = new QLineEdit(); +// regexEdit.setText(Global.getWordRegex()); +// +// QHBoxLayout regexLayout = new QHBoxLayout(); +// regexLayout.addWidget(regexLabel); +// regexLayout.addWidget(regexEdit); +// regexGroup.setLayout(regexLayout); QVBoxLayout mainLayout = new QVBoxLayout(); mainLayout.addWidget(sleepGroup); mainLayout.addWidget(weightGroup); mainLayout.addWidget(attachmentGroup); - mainLayout.addWidget(regexGroup); +// mainLayout.addWidget(regexGroup); mainLayout.addStretch(1); setLayout(mainLayout); @@ -144,9 +144,9 @@ public class ConfigIndexPage extends QWidget { public boolean getIndexNoteTitle() { return indexTitle.isChecked(); } - public String getSpecialCharacters() { - return specialStrip.text(); - } +// public String getSpecialCharacters() { +// return specialStrip.text(); +// } public boolean getIndexImageRecognition() { return indexImageRecognition.isChecked(); } @@ -181,11 +181,11 @@ public class ConfigIndexPage extends QWidget { //***************************************** //* Regex get/set methods //***************************************** - public void setRegex(String s) { - regexEdit.setText(s); - } - public String getRegex() { - return regexEdit.text(); - } +// public void setRegex(String s) { +// regexEdit.setText(s); +// } +// public String getRegex() { +// return regexEdit.text(); +// } } diff --git a/src/cx/fbn/nevernote/sql/DatabaseConnection.java b/src/cx/fbn/nevernote/sql/DatabaseConnection.java index 1ea7e23..5714bdf 100644 --- a/src/cx/fbn/nevernote/sql/DatabaseConnection.java +++ b/src/cx/fbn/nevernote/sql/DatabaseConnection.java @@ -308,10 +308,25 @@ public class DatabaseConnection { query2.exec(); } - // 全文検索のための準備 + // Apache Luceneを使った全文検索のための準備 query.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLucene.init\""); query.exec("CALL FTL_INIT()"); - Global.rebuildFullTextTarget(query); + + Global.rebuildFullTextNoteTarget(this); + } + + // Apache Luceneを使った日本語検索のためのプレーンテキストノートリソースカラムを準備 + NSqlQuery rQuery = new NSqlQuery(resourceConn); + rQuery.exec("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='NOTERESOURCES' and COLUMN_NAME='RESOURCETEXT'"); + if (!rQuery.next()) { + rQuery.exec("alter table noteResources add column resourceText VarChar"); + rQuery.exec("update noteResources set resourceText = ''"); + + // Apache Luceneを使った全文検索のための準備 + rQuery.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLucene.init\""); + rQuery.exec("CALL FTL_INIT()"); + + Global.rebuildFullTextResourceTarget(this); } } diff --git a/src/cx/fbn/nevernote/sql/NoteResourceTable.java b/src/cx/fbn/nevernote/sql/NoteResourceTable.java index 99746c6..d5507aa 100644 --- a/src/cx/fbn/nevernote/sql/NoteResourceTable.java +++ b/src/cx/fbn/nevernote/sql/NoteResourceTable.java @@ -675,5 +675,27 @@ public class NoteResourceTable { NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.exec("update noteresources set isdirty=false"); } + + // リソーステキストを更新 + public void updateResourceText(String guid, String text) { + logger.log(logger.HIGH, "Entering NoteResourceTable.updateResourceText"); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); + boolean check = query.prepare("Update noteResources set resourceText=:resourceText where guid=:guid"); + if (!check) { + logger.log(logger.EXTREME, "Update resourceText sql prepare has failed."); + logger.log(logger.MEDIUM, query.lastError()); + } + + query.bindValue(":resourceText", text); + query.bindValue(":guid", guid); + + check = query.exec(); + if (!check) { + logger.log(logger.EXTREME, "Update resourceText has failed."); + logger.log(logger.MEDIUM, query.lastError()); + } + logger.log(logger.HIGH, "Leaving NoteResourceTable.updateResourceText"); + query.exec(); + } } diff --git a/src/cx/fbn/nevernote/sql/REnSearch.java b/src/cx/fbn/nevernote/sql/REnSearch.java index 9dccbd6..46f2edf 100644 --- a/src/cx/fbn/nevernote/sql/REnSearch.java +++ b/src/cx/fbn/nevernote/sql/REnSearch.java @@ -704,8 +704,10 @@ public class REnSearch { NSqlQuery indexQuery = new NSqlQuery(conn.getIndexConnection()); NSqlQuery mergeQuery = new NSqlQuery(conn.getConnection()); NSqlQuery deleteQuery = new NSqlQuery(conn.getConnection()); - NSqlQuery ftlQuery = new NSqlQuery(conn.getConnection()); - ftlQuery.prepare("SELECT N.GUID AS GUID FROM FTL_SEARCH_DATA(:text, 0, 0) FT, NOTE N WHERE FT.TABLE='NOTE' AND N.GUID=FT.KEYS[0]"); + NSqlQuery ftlNoteQuery = new NSqlQuery(conn.getConnection()); + NSqlQuery ftlResourceQuery = new NSqlQuery(conn.getResourceConnection()); + ftlNoteQuery.prepare("SELECT N.GUID AS GUID FROM FTL_SEARCH_DATA(:text, 0, 0) FT, NOTE N WHERE FT.TABLE='NOTE' AND N.GUID=FT.KEYS[0]"); + ftlResourceQuery.prepare("SELECT R.GUID AS GUID FROM FTL_SEARCH_DATA(:text, 0, 0) FT, NOTERESOURCES R WHERE FT.TABLE='NOTERESOURCES' AND R.GUID=FT.KEYS[0]"); insertQuery.prepare("Insert into SEARCH_RESULTS (guid) values (:guid)"); mergeQuery.prepare("Insert into SEARCH_RESULTS_MERGE (guid) values (:guid)"); @@ -735,11 +737,11 @@ public class REnSearch { } } - // luceneによる全文検索 - ftlQuery.bindValue(":text", getWords().get(i)); - ftlQuery.exec(); - while(ftlQuery.next()) { - guid = ftlQuery.valueString(0); + // luceneによる全文検索　ノートテーブル + ftlNoteQuery.bindValue(":text", getWords().get(i)); + ftlNoteQuery.exec(); + while(ftlNoteQuery.next()) { + guid = ftlNoteQuery.valueString(0); if (i==0 || any) { insertQuery.bindValue(":guid", guid); insertQuery.exec(); @@ -748,6 +750,28 @@ public class REnSearch { mergeQuery.exec(); } } + // luceneによる全文検索　リソーステーブル + NSqlQuery rQuery = new NSqlQuery(conn.getResourceConnection()); + ftlResourceQuery.bindValue(":text", getWords().get(i)); + ftlResourceQuery.exec(); + while(ftlResourceQuery.next()) { + guid = ftlResourceQuery.valueString(0); + + // リソースguidからノートguidを算出 + rQuery.prepare("Select noteGuid from noteResources where guid=:guid"); + rQuery.bindValue(":guid", guid); + rQuery.exec(); + while(rQuery.next()) { + guid = rQuery.valueString(0); + if (i==0 || any) { + insertQuery.bindValue(":guid", guid); + insertQuery.exec(); + } else { + mergeQuery.bindValue(":guid", guid); + mergeQuery.exec(); + } + } + } if (i>0 && !any) { deleteQuery.exec("Delete from SEARCH_RESULTS where guid not in (select guid from SEARCH_RESULTS_MERGE)"); diff --git a/src/cx/fbn/nevernote/threads/IndexRunner.java b/src/cx/fbn/nevernote/threads/IndexRunner.java index abb7a86..ad26cd6 100644 --- a/src/cx/fbn/nevernote/threads/IndexRunner.java +++ b/src/cx/fbn/nevernote/threads/IndexRunner.java @@ -72,8 +72,8 @@ public class IndexRunner extends QObject implements Runnable { public final int REINDEXNOTE=3; public boolean keepRunning; private final QDomDocument doc; - private static String regex = Global.getWordRegex(); - public String specialIndexCharacters = ""; +// private static String regex = Global.getWordRegex(); +// public String specialIndexCharacters = ""; // public boolean indexNoteBody = true; // public boolean indexNoteTitle = true; public boolean indexImageRecognition = true; @@ -366,10 +366,11 @@ public class IndexRunner extends QObject implements Runnable { RTFParser parser = new RTFParser(); ParseContext context = new ParseContext(); parser.parse(input, textHandler, metadata, context); - String[] result = textHandler.toString().split(regex); - for (int i=0; i=0; i--) { - if (!Character.isLetterOrDigit(buffer.charAt(i)) && specialIndexCharacters.indexOf(buffer.charAt(i)) == -1) - buffer.deleteCharAt(i); - else - break; - } - buffer = buffer.reverse(); - for (int i=buffer.length()-1; i>=0; i--) { - if (!Character.isLetterOrDigit(buffer.charAt(i))) - buffer.deleteCharAt(i); - else - break; - } - buffer = buffer.reverse(); - if (buffer.length() > 0) { - // We have a good word, now let's trim off junk at the beginning or end - if (!foundWords.contains(buffer.toString())) { - foundWords.add(buffer.toString()); - foundWords.add(word); - conn.getWordsTable().addWordToNoteIndex(guid, buffer.toString(), type, 100); - uncommittedCount++; - if (uncommittedCount > 100) { - conn.commitTransaction(); - uncommittedCount=0; - } - } - } - return; +// private void addToIndex(String guid, String word, String type) { +// if (foundWords.contains(word)) +// return; +// StringBuffer buffer = new StringBuffer(word.toLowerCase()); +// for (int i=buffer.length()-1; i>=0; i--) { +// if (!Character.isLetterOrDigit(buffer.charAt(i)) && specialIndexCharacters.indexOf(buffer.charAt(i)) == -1) +// buffer.deleteCharAt(i); +// else +// break; +// } +// buffer = buffer.reverse(); +// for (int i=buffer.length()-1; i>=0; i--) { +// if (!Character.isLetterOrDigit(buffer.charAt(i))) +// buffer.deleteCharAt(i); +// else +// break; +// } +// buffer = buffer.reverse(); +// if (buffer.length() > 0) { +// // We have a good word, now let's trim off junk at the beginning or end +// if (!foundWords.contains(buffer.toString())) { +// foundWords.add(buffer.toString()); +// foundWords.add(word); +// conn.getWordsTable().addWordToNoteIndex(guid, buffer.toString(), type, 100); +// uncommittedCount++; +// if (uncommittedCount > 100) { +// conn.commitTransaction(); +// uncommittedCount=0; +// } +// } +// } +// return; +// } + + // ノートリソーステーブルのリソーステキストに追加 + private void updateResourceText(String guid, String text) { + conn.getNoteTable().noteResourceTable.updateResourceText(guid, text); } private void scanUnindexed() { -- 2.11.0