public static String attachmentNameDelimeter = "------";\r
\r
public static String databaseName = new String("NeverNote");\r
+ public static String indexDatabaseName = new String("Index");\r
+ public static String resourceDatabaseName = new String("Resources");\r
public static DateAttributeFilterTable createdSinceFilter;\r
public static DateAttributeFilterTable createdBeforeFilter;\r
public static DateAttributeFilterTable changedSinceFilter;\r
val = "jdbc:h2:"+Global.getFileManager().getDbDirPath(Global.databaseName);\r
return val;\r
}\r
+ public static String getIndexDatabaseUrl() {\r
+ settings.beginGroup("General");\r
+ String val = (String)settings.value("DatabaseURL", "");\r
+ settings.endGroup();\r
+ if (val.equals(""))\r
+ val = "jdbc:h2:"+Global.getFileManager().getDbDirPath(Global.indexDatabaseName);\r
+ return val;\r
+ }\r
+ public static String getResourceDatabaseUrl() {\r
+ settings.beginGroup("General");\r
+ String val = (String)settings.value("DatabaseURL", "");\r
+ settings.endGroup();\r
+ if (val.equals(""))\r
+ val = "jdbc:h2:"+Global.getFileManager().getDbDirPath(Global.resourceDatabaseName);\r
+ return val;\r
+ }\r
public static void setDatabaseUrl(String value) {\r
settings.beginGroup("General");\r
settings.setValue("DatabaseURL", value);\r
listManager = new ListManager(conn, logger);
logger.log(logger.EXTREME, "Building index runners & timers");
- indexRunner = new IndexRunner("indexRunner.log", Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
+ indexRunner = new IndexRunner("indexRunner.log",
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
indexThread = new QThread(indexRunner, "Index Thread");
indexRunner.indexAttachmentsLocally = Global.indexAttachmentsLocally();
indexThread.start();
logger.log(logger.EXTREME, "Setting sync thread & timers");
syncThreadsReady=1;
- syncRunner = new SyncRunner("syncRunner.log", Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
+ syncRunner = new SyncRunner("syncRunner.log",
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
syncTime = new SyncTimes().timeValue(Global.getSyncInterval());
syncTimer = new QTimer();
syncTimer.timeout.connect(this, "syncTimer()");
logger.log(logger.EXTREME, "Starting thumnail thread");
pdfReadyQueue = new ArrayList<String>();
- thumbnailRunner = new ThumbnailRunner("thumbnailRunner.log", Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
+ thumbnailRunner = new ThumbnailRunner("thumbnailRunner.log",
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
thumbnailThread = new QThread(thumbnailRunner, "Thumbnail Thread");
thumbnailRunner.noteSignal.thumbnailPageReady.connect(this, "thumbnailHTMLReady(String,QByteArray,Integer)");
thumbnailThread.start();
Global.getDatabaseUserPassword(), Global.cipherPassword);
}
}
- DatabaseConnection dbConn = new DatabaseConnection(logger,Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword, 0);
+ DatabaseConnection dbConn = new DatabaseConnection(logger,Global.getDatabaseUrl(),
+ Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword, 0);
return dbConn;
}
private void databaseStatus() {
waitCursor(true);
indexRunner.interrupt = true;
- thumbnailRunner.interrupt = true;
int dirty = conn.getNoteTable().getDirtyCount();
int unindexed = conn.getNoteTable().getUnindexedCount();
DatabaseStatus status = new DatabaseStatus();
@SuppressWarnings("unused")
private void setNoteDirty() {
logger.log(logger.EXTREME, "Entering NeverNote.setNoteDirty()");
- // Interrupt indexing & thumbnails to improve performance
- indexRunner.interrupt = true;
- thumbnailRunner.interrupt = true;
// Find if the note is being edited externally. If it is, update it.
if (externalWindows.containsKey(currentNoteGuid)) {
syncRunner.syncDeletedContent = Global.synchronizeDeletedContent();
if (syncThreadsReady > 0) {
- indexRunner.interrupt = true;
thumbnailRunner.interrupt = true;
saveNoteIndexWidth();
saveNoteColumnPositions();
import java.sql.DriverManager;
import java.sql.SQLException;
-import com.trolltech.qt.sql.QJdbc;
-
import cx.fbn.nevernote.Global;
import cx.fbn.nevernote.sql.driver.NSqlQuery;
import cx.fbn.nevernote.utilities.ApplicationLogger;
private SystemIconTable systemIconTable;
private final ApplicationLogger logger;
private Connection conn;
+ private Connection indexConn;
+ private Connection resourceConn;
int throttle=0;
int id;
- public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword, int throttle) {
+ public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String userid, String password, String cypherPassword, int throttle) {
logger = l;
this.throttle = throttle;
- dbSetup(url, userid, password, cypherPassword);
+ dbSetup(url, iurl, rurl, userid, password, cypherPassword);
}
private void setupTables() {
}
// Initialize the database connection
- public void dbSetup(String url,String userid, String userPassword, String cypherPassword) {
+ public void dbSetup(String url,String indexUrl, String resourceUrl, String userid, String userPassword, String cypherPassword) {
logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
System.exit(16);
}
- QJdbc.initialize();
+// QJdbc.initialize();
setupTables();
File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
boolean dbExists = f.exists();
+ f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
+ boolean indexDbExists = f.exists();
+ f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
+ boolean resourceDbExists = f.exists();
logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
-
+ String passwordString = null;
try {
- String passwordString = null;
+
if (cypherPassword==null || cypherPassword.trim().equals(""))
passwordString = userPassword;
else
// conn = DriverManager.getConnection(url,userid,passwordString);
// conn = DriverManager.getConnection(url,userid,passwordString);
// conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
- if (throttle == 0)
+ if (throttle == 0) {
conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
- else
- conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
+ } else {
+ conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
+ }
+ indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
+ resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
// conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
} catch (SQLException e) {
e.printStackTrace();
createTables();
Global.setAutomaticLogin(false);
}
+ if (!resourceDbExists) {
+ createResourceTables();
+ if (dbTableExists("NoteResources")) {
+ // Begin migration of database
+ NSqlQuery query = new NSqlQuery(resourceConn);
+ String linkcmd = "create linked table oldnoteresources "+
+ "('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
+ query.exec(linkcmd);
+ query.exec("insert into noteresources (select * from oldnoteresources)");
+ query.exec("Drop table oldnoteresources;");
+ query.exec("Update noteresources set indexneeded='true'");
+
+ }
+ }
+ if (!indexDbExists) {
+ createIndexTables();
+ executeSql("Update note set indexneeded='true'");
+ }
logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
}
executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
- executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
executeSql("update note set thumbnailneeded=true, thumbnail=null;");
executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
- executeSql("Update note set indexneeded='true'");
- executeSql("Update noteresources set indexneeded='true'");
Global.setDatabaseVersion(version);
}
+ if (version.equals("0.95")) {
+ if (dbTableExists("words"))
+ executeSql("Drop table words;");
+ if (dbTableExists("NoteResources"))
+ executeSql("Drop table NoteResources;");
+ }
+
+
}
public void executeSql(String sql) {
if (!Global.getDatabaseVersion().equals("0.95")) {
upgradeDb(Global.getDatabaseVersion());
}
+ if (!Global.getDatabaseVersion().equals("0.97")) {
+ upgradeDb(Global.getDatabaseVersion());
+ }
}
searchTable.createTable();
watchFolderTable.createTable();
invalidXMLTable.createTable();
- wordsTable.createTable();
syncTable.createTable();
}
+ public void createIndexTables() {
+ wordsTable.createTable();
+ }
+
+ public void createResourceTables() {
+ noteTable.noteResourceTable.createTable();
+ }
+
public Connection getConnection() {
return conn;
}
+ public Connection getIndexConnection() {
+ return indexConn;
+ }
+ public Connection getResourceConnection() {
+ return resourceConn;
+ }
//***************************************************************
//* Table get methods
//* Begin/End transactions
//****************************************************************
public void beginTransaction() {
- NSqlQuery query = new NSqlQuery(getConnection());
-
+ commitTransaction();
+ NSqlQuery query = new NSqlQuery(getConnection());
if (!query.exec("Begin Transaction"))
logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
if (!query.exec("Commit"))
logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
}
+
+ //****************************************************************
+ //* Check if a table exists
+ //****************************************************************
+ public boolean dbTableExists(String name) {
+ NSqlQuery query = new NSqlQuery(getConnection());
+ query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
+ query.bindValue(":name", name.toUpperCase());
+ query.exec();
+ if (query.next())
+ return true;
+ else
+ return false;
+ }
}
}\r
// Create the table\r
public void createTable() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
// Create the NoteResource table\r
logger.log(logger.HIGH, "Creating table NoteResource...");\r
if (!query.exec("Create table NoteResources (guid varchar primary key, " +\r
logger.log(logger.HIGH, "Noteresources unindexed_resources index creation FAILED!!!");\r
if (!query.exec("CREATE INDEX resources_dataheshhex on noteresources (datahash, guid);"))\r
logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!"); \r
- \r
+ if (!query.exec("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);"))\r
+ logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!"); \r
}\r
// Drop the table\r
public void dropTable() { \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.exec("Drop table NoteResources");\r
}\r
// Reset the dirty flag\r
public void resetDirtyFlag(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
\r
query.prepare("Update noteresources set isdirty=false where guid=:guid");\r
query.bindValue(":guid", guid);\r
}\r
// Set if the resource should be indexed\r
public void setIndexNeeded(String guid, Boolean indexNeeded) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection()); \r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection()); \r
query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid");\r
query.bindValue(":needed", indexNeeded);\r
query.bindValue(":guid", guid);\r
// get any unindexed resource\r
public List<String> getNextUnindexed(int limit) {\r
List<String> guids = new ArrayList<String>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
\r
if (!query.exec("Select guid from NoteResources where indexNeeded = true limit " +limit))\r
logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getNextUnindexed(): " +query.lastError());\r
// get any unindexed resource\r
public List<String> getUnindexed() {\r
List<String> guids = new ArrayList<String>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
\r
if (!query.exec("Select guid from NoteResources where indexNeeded = true"))\r
logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getUnindexed(): " +query.lastError());\r
return guids; \r
}\r
\r
+ public List<String> getAll() {\r
+ List<String> guids = new ArrayList<String>();\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+ \r
+ query.prepare("Select guid from noteresources;");\r
+ if (!query.exec())\r
+ logger.log(logger.EXTREME, "Error getting all note resource guids. " +query.lastError());\r
+ \r
+ while (query.next()) {\r
+ guids.add(query.valueString(0));\r
+ }\r
+ return guids;\r
+ }\r
+ \r
public List<String> findInkNotes() {\r
List<String> guids = new ArrayList<String>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
\r
query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'");\r
if (!query.exec())\r
public void saveNoteResource(Resource r, boolean isDirty) {\r
logger.log(logger.HIGH, "Entering DBRunner.saveNoteResources");\r
boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
\r
check = query.prepare("Insert Into NoteResources ("\r
}\r
// delete an old resource\r
public void expungeNoteResource(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.prepare("delete from NoteResources where guid=:guid");\r
query.bindValue(":guid", guid);\r
query.exec();\r
logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
\r
boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
\r
check = query.prepare("Select guid from NoteResources " +\r
"where noteGuid=:noteGuid and dataHash=:hash");\r
logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
\r
boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
\r
check = query.prepare("Select guid, mime, from NoteResources " +\r
"where noteGuid=:noteGuid and dataHash=:hash");\r
r.setGuid(query.valueString(0));\r
r.setMime(query.valueString(1));\r
\r
- NSqlQuery binary = new NSqlQuery(db.getConnection());\r
+ NSqlQuery binary = new NSqlQuery(db.getResourceConnection());\r
if (!binary.prepare("Select databinary from NoteResources " +\r
"where guid=:guid")) {\r
logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");\r
if (guid == null)\r
return null;\r
\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
String queryString;\r
queryString = new String("Select guid, noteGuid, mime, width, height, duration, "\r
+"active, updateSequenceNumber, dataHash, dataSize, "\r
return null;\r
List<Resource> res = new ArrayList<Resource>();\r
\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.prepare("Select guid"\r
+" from NoteResources where noteGuid = :noteGuid");\r
query.bindValue(":noteGuid", noteGuid);\r
return null;\r
boolean check;\r
List<Resource> res = new ArrayList<Resource>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
check = query.prepare("Select "\r
+"recognitionHash, recognitionSize, recognitionBinary "\r
+" from NoteResources where noteGuid=:guid");\r
if (guid == null)\r
return null;\r
boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
check = query.prepare("Select "\r
+"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
+" from NoteResources where guid=:guid");\r
// Save Note Resource\r
public void updateNoteResource(Resource r, boolean isDirty) {\r
logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.prepare("delete from NoteResources where guid=:recGuid");\r
query.bindValue(":recGuid", r.getGuid());\r
query.exec();\r
// Update note resource GUID\r
public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");\r
query.bindValue(":newGuid", newGuid);\r
query.bindValue(":isDirty", isDirty);\r
// Update note resource GUID\r
public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");\r
query.bindValue(":isDirty", isDirty);\r
query.bindValue(":guid", guid);\r
\r
// Drop the table\r
public void reindexAll() { \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.exec("Update NoteResources set indexneeded=true");\r
}\r
// Count attachments\r
public int getResourceCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.exec("select count(*) from noteresources");\r
query.next(); \r
int returnValue = new Integer(query.valueString(0));\r
//\r
// Count unindexed notes\r
public int getUnindexedCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
query.exec("select count(*) from noteresources where indexneeded=true");\r
query.next(); \r
int returnValue = new Integer(query.valueString(0));\r
if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);"))\r
logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!"); \r
noteTagsTable.createTable();\r
- noteResourceTable.createTable(); \r
+// noteResourceTable.createTable(); \r
}\r
// Drop the table\r
public void dropTable() {\r
//****************************************\r
public List<Note> matchWords() {\r
logger.log(logger.EXTREME, "Inside EnSearch.matchWords()");\r
- \r
- StringBuffer buffer = new StringBuffer(100);\r
- Integer counter = 0;\r
boolean subSelect = false;\r
\r
- buffer.append("Select guid from Note ");\r
if (searchWords.size() > 0) \r
subSelect = true;\r
- if (subSelect) {\r
- buffer.append(" where guid in ");\r
- \r
- // Build the query words\r
- String connector;\r
- if (any)\r
- connector = new String("or");\r
- else\r
- connector = new String("and");\r
- for (int i=0; i<getWords().size(); i++) {\r
- buffer.append("(Select distinct guid from words where ");\r
- buffer.append("weight >= :weight"+counter.toString() +" and ");\r
- if (getWords().get(i).indexOf("*")==-1)\r
- buffer.append("word=:word" +counter.toString());\r
- else\r
- buffer.append("word like :word" +counter.toString());\r
- counter++;\r
- buffer.append(") ");\r
- if (i < getWords().size() -1)\r
- buffer.append(" " +connector +" guid in ");\r
- }\r
- }\r
- \r
+\r
NSqlQuery query = new NSqlQuery(conn.getConnection());\r
+ // Build a temp table for GUID results\r
+ if (!conn.dbTableExists("SEARCH_RESULTS")) {\r
+ query.exec("create temporary table SEARCH_RESULTS (guid varchar)");\r
+ query.exec("create temporary table SEARCH_RESULTS_MERGE (guid varchar)");\r
+ } else {\r
+ query. exec("Delete from SEARCH_RESULTS");\r
+ query. exec("Delete from SEARCH_RESULTS_MERGE");\r
+ }\r
+\r
+ NSqlQuery insertQuery = new NSqlQuery(conn.getConnection());\r
+ NSqlQuery indexQuery = new NSqlQuery(conn.getIndexConnection());\r
+ NSqlQuery mergeQuery = new NSqlQuery(conn.getConnection());\r
+ NSqlQuery deleteQuery = new NSqlQuery(conn.getConnection());\r
\r
- if (!query.prepare(buffer.toString()))\r
- logger.log(logger.HIGH, "EnSearch Sql Prepare Failed:" +query.lastError());\r
+ indexQuery.prepare("Select distinct guid from words where weight >= " +minimumRecognitionWeight +\r
+ " and word=:word");\r
+ insertQuery.prepare("Insert into SEARCH_RESULTS (guid) values (:guid)");\r
+ mergeQuery.prepare("Insert into SEARCH_RESULTS_MERGE (guid) values (:guid)");\r
\r
if (subSelect) {\r
- // Do the binding\r
- Integer binder = 0;\r
for (int i=0; i<getWords().size(); i++) {\r
- String val = getWords().get(i);\r
- val = val.replace('*', '%');\r
- query.bindValue(":weight"+binder.toString(), minimumRecognitionWeight);\r
- query.bindValue(":word"+binder.toString(), cleanupWord(val));\r
- binder++;\r
- } \r
+ indexQuery.bindValue(":word", getWords().get(i));\r
+ indexQuery.exec();\r
+ String guid = null;\r
+ while(indexQuery.next()) {\r
+ guid = indexQuery.valueString(0);\r
+ if (i==0 || any) {\r
+ insertQuery.bindValue(":guid", guid);\r
+ insertQuery.exec();\r
+ } else {\r
+ mergeQuery.bindValue(":guid", guid);\r
+ mergeQuery.exec();\r
+ }\r
+ }\r
+ if (i>0 && !any) {\r
+ deleteQuery.exec("Delete from SEARCH_RESULTS where guid not in (select guid from SEARCH_RESULTS_MERGE)");\r
+ deleteQuery.exec("Delete from SEARCH_RESULTS_MERGE");\r
+ }\r
+ }\r
}\r
-\r
- List<Note> guids = new ArrayList<Note>();\r
+ \r
NoteTable noteTable = new NoteTable(logger, conn); \r
- if (!query.exec()) \r
- logger.log(logger.EXTREME, "EnSearch.matchWords query failed: " +query.lastError());\r
List<String> validGuids = new ArrayList<String>();\r
+ query.prepare("Select distinct guid from Note where guid in (Select guid from SEARCH_RESULTS)");\r
+ if (!query.exec()) \r
+ logger.log(logger.LOW, "Error merging search results:" + query.lastError());\r
+ \r
while (query.next()) {\r
- String guid = query.valueString(0);\r
- validGuids.add(guid);\r
+ validGuids.add(query.valueString(0));\r
}\r
-\r
+ \r
List<Note> noteIndex = noteTable.getAllNotes();\r
+ List<Note> guids = new ArrayList<Note>();\r
for (int i=0; i<noteIndex.size(); i++) {\r
Note n = noteIndex.get(i);\r
boolean good = true;\r
}\r
// Create the table\r
public void createTable() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getIndexConnection());\r
logger.log(logger.HIGH, "Creating table WORDS ...");\r
if (!query.exec("create table words (word varchar, guid varchar, source varchar, weight int, primary key (word, guid, source));")) {\r
logger.log(logger.HIGH, "Table WORDS creation FAILED!!!"); \r
}\r
// Drop the table\r
public void dropTable() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getIndexConnection());\r
query.exec("drop table words");\r
}\r
// Count unindexed notes\r
public int getWordCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getIndexConnection());\r
query.exec("select count(*) from words");\r
query.next(); \r
int returnValue = new Integer(query.valueString(0));\r
\r
// Clear out the word index table\r
public void clearWordIndex() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ NSqlQuery query = new NSqlQuery(db.getIndexConnection());\r
logger.log(logger.HIGH, "DELETE FROM WORDS");\r
\r
boolean check = query.exec("DELETE FROM WORDS");\r
//********************************************************************************\r
//********************************************************************************\r
public void expungeFromWordIndex(String guid, String type) {\r
- NSqlQuery deleteWords = new NSqlQuery(db.getConnection());\r
+ NSqlQuery deleteWords = new NSqlQuery(db.getIndexConnection());\r
if (!deleteWords.prepare("delete from words where guid=:guid and source=:source")) {\r
logger.log(logger.EXTREME, "Note SQL select prepare deleteWords has failed.");\r
logger.log(logger.MEDIUM, deleteWords.lastError());\r
}\r
// Reindex a note\r
public synchronized void addWordToNoteIndex(String guid, String word, String type, Integer weight) {\r
- NSqlQuery findWords = new NSqlQuery(db.getConnection());\r
+ NSqlQuery findWords = new NSqlQuery(db.getIndexConnection());\r
if (!findWords.prepare("Select weight from words where guid=:guid and source=:type and word=:word")) {\r
logger.log(logger.MEDIUM, "Prepare failed in addWordToNoteIndex()");\r
logger.log(logger.MEDIUM, findWords.lastError());\r
int recordWeight = new Integer(findWords.valueString(0));\r
addNeeded = false;\r
if (recordWeight < weight) {\r
- NSqlQuery updateWord = new NSqlQuery(db.getConnection());\r
+ NSqlQuery updateWord = new NSqlQuery(db.getIndexConnection());\r
if (!updateWord.prepare("Update words set weight=:weight where guid=:guid and source=:type and word=:word")) {\r
logger.log(logger.MEDIUM, "Prepare failed for find words in addWordToNoteIndex()");\r
logger.log(logger.MEDIUM, findWords.lastError()); \r
if (!addNeeded)\r
return;\r
\r
- NSqlQuery insertWords = new NSqlQuery(db.getConnection());\r
+ NSqlQuery insertWords = new NSqlQuery(db.getIndexConnection());\r
if (!insertWords.prepare("Insert Into Words (word, guid, weight, source)"\r
+" Values(:word, :guid, :weight, :type )")) {\r
logger.log(logger.EXTREME, "Note SQL select prepare checkWords has failed.");\r
//*********************************************\r
//* Constructor *\r
//*********************************************\r
- public CounterRunner(String logname, int t, String u, String uid, String pswd, String cpswd) {\r
+ public CounterRunner(String logname, int t, String u, String i, String r, String uid, String pswd, String cpswd) {\r
type = t;\r
\r
threadLock = new QMutex();\r
logger = new ApplicationLogger(logname);\r
// setAutoDelete(false); \r
- conn = new DatabaseConnection(logger, u, uid, pswd, cpswd, 300);\r
+ conn = new DatabaseConnection(logger, u, i, r, uid, pswd, cpswd, 300);\r
keepRunning = true;\r
notebookSignal = new NotebookSignal();\r
tagSignal = new TagSignal();\r
int uncommittedCount = 0;\r
\r
\r
- public IndexRunner(String logname, String u, String uid, String pswd, String cpswd) {\r
+ public IndexRunner(String logname, String u, String i, String r, String uid, String pswd, String cpswd) {\r
foundWords = new TreeSet<String>();\r
logger = new ApplicationLogger(logname);\r
- conn = new DatabaseConnection(logger, u, uid, pswd, cpswd, 0);\r
+ conn = new DatabaseConnection(logger, u, i, r, uid, pswd, cpswd, 500);\r
indexType = SCAN;\r
guid = null;\r
keepRunning = true;\r
try {\r
conn.commitTransaction();\r
uncommittedCount = 0;\r
- waitSeconds(1);\r
String work = workQueue.take();\r
idle=false;\r
if (work.startsWith("SCAN")) {\r
\r
logger.log(logger.EXTREME, "Splitting words");\r
String[] result = text.toString().split(regex);\r
+ conn.commitTransaction();\r
conn.beginTransaction();\r
logger.log(logger.EXTREME, "Deleting existing words for note from index");\r
conn.getWordsTable().expungeFromWordIndex(guid, "CONTENT");\r
\r
logger.log(logger.EXTREME, "Number of words found: " +result.length);\r
- for (int j=0; j<result.length && keepRunning && !interrupt; j++) {\r
+ for (int j=0; j<result.length && keepRunning; j++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
if (!result[j].trim().equals("")) {\r
logger.log(logger.EXTREME, "Result word: " +result[j].trim());\r
addToIndex(guid, result[j], "CONTENT");\r
else\r
resourceBinary = new QByteArray(r.getRecognition().getBody());\r
\r
+ conn.commitTransaction();\r
conn.beginTransaction();\r
conn.getWordsTable().expungeFromWordIndex(r.getNoteGuid(), "RESOURCE");\r
// This is due to an old bug & can be removed at some point in the future 11/23/2010\r
\r
// look for text tags\r
QDomNodeList anchors = docElem.elementsByTagName("t");\r
- for (int i=0; i<anchors.length() && keepRunning && !interrupt; i++) {\r
+ for (int i=0; i<anchors.length() && keepRunning; i++) {\r
+ if (interrupt) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
+ }\r
QDomElement enmedia = anchors.at(i).toElement();\r
String weight = new String(enmedia.attribute("w"));\r
String text = new String(enmedia.text()).toLowerCase();\r
}\r
}\r
\r
- if (Global.keepRunning && indexAttachmentsLocally && !interrupt) {\r
+ if (Global.keepRunning && indexAttachmentsLocally) {\r
conn.commitTransaction();\r
uncommittedCount = 0;\r
conn.beginTransaction();\r
private void indexResourceRTF(Resource r) {\r
\r
QTemporaryFile f = writeResource(r.getData());\r
- if (!keepRunning || interrupt) {\r
+ if (!keepRunning) {\r
return;\r
}\r
\r
ParseContext context = new ParseContext();\r
parser.parse(input, textHandler, metadata, context);\r
String[] result = textHandler.toString().split(regex);\r
- for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
+ for (int i=0; i<result.length && keepRunning; i++) {\r
addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
}\r
input.close();\r
private void indexResourceODF(Resource r) {\r
\r
QTemporaryFile f = writeResource(r.getData());\r
- if (!keepRunning || interrupt) {\r
+ if (!keepRunning) {\r
return;\r
}\r
\r
ParseContext context = new ParseContext();\r
parser.parse(input, textHandler, metadata, context);\r
String[] result = textHandler.toString().split(regex);\r
- for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
+ for (int i=0; i<result.length && keepRunning; i++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
}\r
input.close();\r
private void indexResourceOffice(Resource r) {\r
\r
QTemporaryFile f = writeResource(r.getData());\r
- if (!keepRunning || interrupt) {\r
+ if (!keepRunning) {\r
return;\r
}\r
\r
ParseContext context = new ParseContext();\r
parser.parse(input, textHandler, metadata, context);\r
String[] result = textHandler.toString().split(regex);\r
- for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
+ for (int i=0; i<result.length && keepRunning; i++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
}\r
input.close();\r
private void indexResourcePDF(Resource r) {\r
\r
QTemporaryFile f = writeResource(r.getData());\r
- if (!keepRunning || interrupt) {\r
+ if (!keepRunning) {\r
return;\r
}\r
\r
ParseContext context = new ParseContext();\r
parser.parse(input, textHandler, metadata, context);\r
String[] result = textHandler.toString().split(regex);\r
- for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
+ for (int i=0; i<result.length && keepRunning; i++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
}\r
input.close();\r
private void indexResourceOOXML(Resource r) {\r
\r
QTemporaryFile f = writeResource(r.getData());\r
- if (!keepRunning || interrupt) {\r
+ if (!keepRunning) {\r
return;\r
}\r
\r
ParseContext context = new ParseContext();\r
parser.parse(input, textHandler, metadata, context);\r
String[] result = textHandler.toString().split(regex);\r
- for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
+ for (int i=0; i<result.length && keepRunning; i++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
}\r
input.close();\r
int index = content.indexOf("<en-crypt");\r
int endPos;\r
boolean tagFound = true;\r
- while (tagFound && keepRunning && !interrupt) {\r
+ while (tagFound && keepRunning) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
endPos = content.indexOf("</en-crypt>", index)+11;\r
if (endPos > -1 && index > -1) {\r
content = content.substring(0,index)+content.substring(endPos);\r
signal.indexStarted.emit();\r
started = true;\r
}\r
- for (int i=0; i<notes.size() && !interrupt && keepRunning; i++) {\r
+ for (int i=0; i<notes.size() && keepRunning; i++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
guid = notes.get(i);\r
- if (guid != null && keepRunning && !interrupt) {\r
- waitSeconds(1);\r
+ if (guid != null && keepRunning) {\r
indexNoteContent();\r
}\r
}\r
signal.indexStarted.emit();\r
started = true;\r
}\r
- for (int i=0; i<unindexedResources.size()&& !interrupt && keepRunning; i++) {\r
+ for (int i=0; i<unindexedResources.size()&& keepRunning; i++) {\r
+ if (interrupt) {\r
+ processInterrupt();\r
+ }\r
guid = unindexedResources.get(i);\r
if (keepRunning) {\r
- waitSeconds(1);\r
indexResource();\r
}\r
}\r
- if (started && keepRunning && !interrupt) \r
+ if (started && keepRunning) \r
signal.indexFinished.emit();\r
}\r
\r
\r
private void waitSeconds(int len) {\r
long starttime = 0; // variable declared\r
- if (starttime == 0)\r
- return;\r
//...\r
// for the first time, remember the timestamp\r
starttime = System.currentTimeMillis();\r
LockSupport.parkNanos((Math.max(0, \r
starttime - System.currentTimeMillis()) * 1000000));\r
}\r
+ \r
+ private void processInterrupt() {\r
+ conn.commitTransaction();\r
+ waitSeconds(1);\r
+ uncommittedCount = 0;\r
+ conn.beginTransaction();\r
+ interrupt = false;\r
+ }\r
+ \r
}\r
//*********************************************\r
//* Constructor *\r
//*********************************************\r
- public SaveRunner(String logname, String u, String uid, String pswd, String cpswd) {\r
+ public SaveRunner(String logname, String u, String i, String r, String uid, String pswd, String cpswd) {\r
logger = new ApplicationLogger(logname);\r
- conn = new DatabaseConnection(logger, u, uid, pswd, cpswd, 0);\r
+ conn = new DatabaseConnection(logger, u, i, r, uid, pswd, cpswd, 0);\r
threadLock = new QMutex();\r
keepRunning = true;\r
noteSignals = new NoteSignal();\r
private static int MAX_QUEUED_WAITING = 1000;\r
String dbuid;\r
String dburl;\r
+ String indexUrl;\r
+ String resourceUrl;\r
String dbpswd;\r
String dbcpswd;\r
private final TreeSet<String> ignoreTags;\r
\r
\r
\r
- public SyncRunner(String logname, String u, String uid, String pswd, String cpswd) {\r
+ public SyncRunner(String logname, String u, String i, String r, String uid, String pswd, String cpswd) {\r
logger = new ApplicationLogger(logname);\r
\r
noteSignal = new NoteSignal();\r
searchSignal = new SavedSearchSignal();\r
syncSignal = new SyncSignal();\r
resourceSignal = new NoteResourceSignal();\r
+ resourceUrl = r;\r
+ indexUrl = i;\r
dbuid = uid;\r
dburl = u;\r
dbpswd = pswd;\r
public void run() {\r
try {\r
logger.log(logger.EXTREME, "Starting thread");\r
- conn = new DatabaseConnection(logger, dburl, dbuid, dbpswd, dbcpswd, 200);\r
+ conn = new DatabaseConnection(logger, dburl, indexUrl, resourceUrl, dbuid, dbpswd, dbcpswd, 200);\r
while(keepRunning) {\r
String work = workQueue.take();\r
logger.log(logger.EXTREME, "Work found: " +work);\r
\r
\r
\r
- public ThumbnailRunner(String logname, String u, String uid, String pswd, String cpswd) {\r
+ public ThumbnailRunner(String logname, String u, String i, String r, String uid, String pswd, String cpswd) {\r
logger = new ApplicationLogger(logname);\r
- conn = new DatabaseConnection(logger, u, uid, pswd, cpswd, 300);\r
+ conn = new DatabaseConnection(logger, u, i, r, uid, pswd, cpswd, 300);\r
noteSignal = new NoteSignal();\r
guid = null;\r
keepRunning = true;\r
reloadIndexes();\r
\r
notebookSignal = new NotebookSignal();\r
- notebookCounterRunner = new CounterRunner("notebook_counter.log", CounterRunner.NOTEBOOK, Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
+ notebookCounterRunner = new CounterRunner("notebook_counter.log", CounterRunner.NOTEBOOK, \r
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),\r
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
notebookCounterRunner.setNoteIndex(getNoteIndex());\r
notebookCounterRunner.notebookSignal.countsChanged.connect(this, "setNotebookCounter(List)");\r
notebookThread = new QThread(notebookCounterRunner, "Notebook Counter Thread");\r
notebookThread.start();\r
\r
tagSignal = new TagSignal();\r
- tagCounterRunner = new CounterRunner("tag_counter.log", CounterRunner.TAG, Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
+ tagCounterRunner = new CounterRunner("tag_counter.log", CounterRunner.TAG, \r
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),\r
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
tagCounterRunner.setNoteIndex(getNoteIndex());\r
tagCounterRunner.tagSignal.countsChanged.connect(this, "setTagCounter(List)");\r
tagThread = new QThread(tagCounterRunner, "Tag Counter Thread");\r
tagThread.start();\r
\r
trashSignal = new TrashSignal();\r
- trashCounterRunner = new CounterRunner("trash_counter.log", CounterRunner.TRASH, Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
+ trashCounterRunner = new CounterRunner("trash_counter.log", CounterRunner.TRASH, \r
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),\r
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
trashCounterRunner.trashSignal.countChanged.connect(this, "trashSignalReceiver(Integer)");\r
trashThread = new QThread(trashCounterRunner, "Trash Counter Thread");\r
trashThread.start();\r
tagSignal = new TagSignal();\r
\r
logger.log(logger.EXTREME, "Setting save thread");\r
- saveRunner = new SaveRunner("saveRunner.log", Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
+ saveRunner = new SaveRunner("saveRunner.log", \r
+ Global.getDatabaseUrl(), Global.getIndexDatabaseUrl(), Global.getResourceDatabaseUrl(),\r
+ Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);\r
saveThread = new QThread(saveRunner, "Save Runner Thread");\r
saveThread.start();\r
\r