OSDN Git Service

Rework some database logic to try and reduce the overhead with indexing & thumbnail...
authorRandy Baumgarte <randy@fbn.cx>
Thu, 27 Jan 2011 15:48:57 +0000 (10:48 -0500)
committerRandy Baumgarte <randy@fbn.cx>
Sun, 30 Jan 2011 13:37:18 +0000 (08:37 -0500)
src/cx/fbn/nevernote/NeverNote.java
src/cx/fbn/nevernote/sql/DatabaseConnection.java
src/cx/fbn/nevernote/sql/DeletedTable.java
src/cx/fbn/nevernote/sql/NoteTable.java
src/cx/fbn/nevernote/threads/IndexRunner.java
src/cx/fbn/nevernote/threads/SyncRunner.java
src/cx/fbn/nevernote/threads/ThumbnailRunner.java

index 0eb192a..8ee35d5 100644 (file)
@@ -437,7 +437,7 @@ public class NeverNote extends QMainWindow{
                thumbnailTimer = new QTimer();
                thumbnailTimer.timeout.connect(this, "thumbnailTimer()");
                thumbnailTimer();
-               thumbnailTimer.setInterval(60*1000);  // Thumbnail every minute
+               thumbnailTimer.setInterval(500*1000);  // Thumbnail every minute
                thumbnailTimer.start();
                
                logger.log(logger.EXTREME, "Starting authentication timer");
@@ -2400,6 +2400,8 @@ public class NeverNote extends QMainWindow{
        @SuppressWarnings("unused")
        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();
@@ -3950,6 +3952,10 @@ public class NeverNote extends QMainWindow{
     @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)) {
                        QTextCodec codec = QTextCodec.codecForName("UTF-8");
@@ -5083,6 +5089,7 @@ public class NeverNote extends QMainWindow{
                        
                        if (syncThreadsReady > 0) {
                                indexRunner.interrupt = true;
+                               thumbnailRunner.interrupt = true;
                                saveNoteIndexWidth();
                                saveNoteColumnPositions();
                                if (syncRunner.addWork("SYNC")) {
@@ -5187,6 +5194,7 @@ public class NeverNote extends QMainWindow{
                if (syncRunning) 
                        return;
                if (!indexDisabled && indexRunner.idle) { 
+                       thumbnailRunner.interrupt = true;
                        indexRunner.addWork("SCAN");
                }
                logger.log(logger.EXTREME, "Leaving neverNote index timer");
@@ -5269,11 +5277,8 @@ public class NeverNote extends QMainWindow{
        }
 
        private void thumbnailTimer() {
-               if (Global.enableThumbnails() && conn.getNoteTable().getThumbnailNeededCount() > 1) {
-                       thumbnailTimer.setInterval(10*1000);
+               if (Global.enableThumbnails() && !syncRunning && indexRunner.idle) {
                        thumbnailRunner.addWork("SCAN");
-               } else {
-                       thumbnailTimer.setInterval(60*1000);
                }
        }
        
index 993f743..7f3ebdc 100644 (file)
@@ -108,6 +108,7 @@ public class DatabaseConnection {
                        else
                                passwordString = cypherPassword+" "+userPassword;
 //                     conn = DriverManager.getConnection(url,userid,passwordString);
+//                     conn = DriverManager.getConnection(url,userid,passwordString);
 //                     conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
                        if (throttle == 0)
                                conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
@@ -279,4 +280,20 @@ public class DatabaseConnection {
                return inkImagesTable;
        }
 
+       //****************************************************************
+       //* Begin/End transactions
+       //****************************************************************
+       public void beginTransaction() {
+        NSqlQuery query = new NSqlQuery(getConnection());
+                                                       
+               if (!query.exec("Begin Transaction"))
+                       logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
+
+       }
+       public void commitTransaction() {
+        NSqlQuery query = new NSqlQuery(getConnection());
+                                                       
+               if (!query.exec("Commit"))
+                       logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
+       }
 }
index ea6610a..5331646 100644 (file)
@@ -52,6 +52,8 @@ public class DeletedTable {
        }\r
        // Add an item to the deleted table\r
        public void addDeletedItem(String guid, String type) {\r
+               if (exists(guid,type))\r
+                       return;\r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                query.prepare("Insert Into DeletedItems (guid, type) Values(:guid, :type)");\r
                query.bindValue(":guid", guid);\r
@@ -61,6 +63,18 @@ public class DeletedTable {
                        logger.log(logger.MEDIUM, query.lastError());\r
                }\r
        }\r
+       // Check if a record exists\r
+       public boolean exists(String guid, String type) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select guid, type from DeletedItems where guid=:guid and type=:type");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":type", type);\r
+               query.exec();\r
+               if (!query.next()) {\r
+                       return false;\r
+               }\r
+               return true;\r
+       }\r
        // Add an item to the deleted table\r
        public void expungeDeletedItem(String guid, String type) {\r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
index 1d12b07..57a0764 100644 (file)
@@ -128,7 +128,6 @@ public class NoteTable {
                if (isDirty) {\r
                        EnmlConverter enml = new EnmlConverter(logger);\r
                        query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));\r
-                       enml = null;\r
                }\r
                else\r
                        query.bindValue(":content", n.getContent());\r
@@ -164,11 +163,6 @@ public class NoteTable {
                        for (int i=0; i<n.getTagGuids().size(); i++) \r
                                noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i));\r
                }\r
-               created = null;\r
-               updated = null;\r
-               deleted = null;\r
-               query = null;\r
-               simple = null;\r
                \r
                logger.log(logger.EXTREME, "Leaving addNote");\r
        } \r
index 4ea69c5..e9b4dab 100644 (file)
@@ -82,12 +82,13 @@ public class IndexRunner extends QObject implements Runnable {
        public boolean indexAttachmentsLocally = true;\r
        public volatile IndexSignal                     signal;\r
        private final TreeSet<String>           foundWords;\r
+       int uncommittedCount = 0;\r
 \r
        \r
        public IndexRunner(String logname, String u, 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, 300);\r
+               conn = new DatabaseConnection(logger, u, uid, pswd, cpswd, 0);\r
                indexType = SCAN;\r
                guid = null;\r
                keepRunning = true;\r
@@ -110,6 +111,8 @@ public class IndexRunner extends QObject implements Runnable {
                while (keepRunning) {\r
                        idle=true;\r
                        try {\r
+                               conn.commitTransaction();\r
+                               uncommittedCount = 0;\r
                                waitSeconds(1);\r
                                String work = workQueue.take();\r
                                idle=false;\r
@@ -173,11 +176,12 @@ public class IndexRunner extends QObject implements Runnable {
                                \r
                logger.log(logger.EXTREME, "Splitting words");\r
                String[] result = text.toString().split(regex);\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; j++) {\r
+               for (int j=0; j<result.length && keepRunning && !interrupt; j++) {\r
                        if (!result[j].trim().equals("")) {\r
                                logger.log(logger.EXTREME, "Result word: " +result[j].trim());\r
                                addToIndex(guid, result[j], "CONTENT");\r
@@ -187,7 +191,9 @@ public class IndexRunner extends QObject implements Runnable {
                if (Global.keepRunning) {\r
                        logger.log(logger.EXTREME, "Resetting note guid needed");\r
                        conn.getNoteTable().setIndexNeeded(guid, false);\r
-               }\r
+               } \r
+               conn.commitTransaction();\r
+               uncommittedCount = 0;\r
                logger.log(logger.EXTREME, "Leaving indexRunner.indexNoteContent()");\r
        }\r
        \r
@@ -231,30 +237,44 @@ public class IndexRunner extends QObject implements Runnable {
                else\r
                        resourceBinary = new QByteArray(r.getRecognition().getBody());\r
                \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
                conn.getWordsTable().expungeFromWordIndex(guid, "RESOURCE");   \r
+               conn.commitTransaction();\r
+               uncommittedCount = 0;\r
+               conn.beginTransaction();\r
                        \r
                doc.setContent(resourceBinary);\r
                QDomElement docElem = doc.documentElement();\r
                        \r
                // look for text tags\r
                QDomNodeList anchors = docElem.elementsByTagName("t");\r
-               for (int i=0; i<anchors.length() && keepRunning; i++) {\r
+               for (int i=0; i<anchors.length() && keepRunning && !interrupt; i++) {\r
                        QDomElement enmedia = anchors.at(i).toElement();\r
                        String weight = new String(enmedia.attribute("w"));\r
                        String text = new String(enmedia.text()).toLowerCase();\r
                        if (!text.equals("")) {\r
                                conn.getWordsTable().addWordToNoteIndex(r.getNoteGuid(), text, "RESOURCE", new Integer(weight));\r
+                               uncommittedCount++;\r
+                               if (uncommittedCount > 100) {\r
+                                       conn.commitTransaction();\r
+                                       uncommittedCount=0;\r
+                               }\r
                        }\r
                }\r
                \r
-               if (Global.keepRunning && indexAttachmentsLocally) {\r
+               if (Global.keepRunning && indexAttachmentsLocally && !interrupt) {\r
+                       conn.commitTransaction();\r
+                       uncommittedCount = 0;\r
+                       conn.beginTransaction();\r
                        indexResourceContent(guid);\r
                }\r
                                \r
                if (Global.keepRunning)\r
                        conn.getNoteTable().noteResourceTable.setIndexNeeded(guid,false);\r
+               conn.commitTransaction();\r
+               uncommittedCount = 0;\r
        }\r
        \r
        private void indexResourceContent(String guid) {\r
@@ -296,7 +316,7 @@ public class IndexRunner extends QObject implements Runnable {
        private void indexResourceRTF(Resource r) {\r
 \r
                QTemporaryFile f = writeResource(r.getData());\r
-               if (!keepRunning) {\r
+               if (!keepRunning || interrupt) {\r
                        return;\r
                }\r
                \r
@@ -309,7 +329,7 @@ public class IndexRunner extends QObject implements Runnable {
                        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; i++) {\r
+                       for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
                                addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
                        }\r
                        input.close();\r
@@ -338,7 +358,7 @@ public class IndexRunner extends QObject implements Runnable {
        private void indexResourceODF(Resource r) {\r
 \r
                QTemporaryFile f = writeResource(r.getData());\r
-               if (!keepRunning) {\r
+               if (!keepRunning || interrupt) {\r
                        return;\r
                }\r
                \r
@@ -351,7 +371,7 @@ public class IndexRunner extends QObject implements Runnable {
                        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; i++) {\r
+                       for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
                                addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
                        }\r
                        input.close();\r
@@ -380,7 +400,7 @@ public class IndexRunner extends QObject implements Runnable {
        private void indexResourceOffice(Resource r) {\r
 \r
                QTemporaryFile f = writeResource(r.getData());\r
-               if (!keepRunning) {\r
+               if (!keepRunning || interrupt) {\r
                        return;\r
                }\r
                \r
@@ -393,7 +413,7 @@ public class IndexRunner extends QObject implements Runnable {
                        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; i++) {\r
+                       for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
                                addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
                        }\r
                        input.close();\r
@@ -423,7 +443,7 @@ public class IndexRunner extends QObject implements Runnable {
        private void indexResourcePDF(Resource r) {\r
 \r
                QTemporaryFile f = writeResource(r.getData());\r
-               if (!keepRunning) {\r
+               if (!keepRunning || interrupt) {\r
                        return;\r
                }\r
                \r
@@ -436,7 +456,7 @@ public class IndexRunner extends QObject implements Runnable {
                        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; i++) {\r
+                       for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
                                addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
                        }\r
                        input.close();\r
@@ -465,7 +485,7 @@ public class IndexRunner extends QObject implements Runnable {
        private void indexResourceOOXML(Resource r) {\r
 \r
                QTemporaryFile f = writeResource(r.getData());\r
-               if (!keepRunning) {\r
+               if (!keepRunning || interrupt) {\r
                        return;\r
                }\r
                \r
@@ -478,7 +498,7 @@ public class IndexRunner extends QObject implements Runnable {
                        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; i++) {\r
+                       for (int i=0; i<result.length && keepRunning && !interrupt; i++) {\r
                                addToIndex(r.getNoteGuid(), result[i], "RESOURCE");\r
                        }\r
                        input.close();\r
@@ -518,7 +538,7 @@ public class IndexRunner extends QObject implements Runnable {
                int index = content.indexOf("<en-crypt");\r
                int endPos;\r
                boolean tagFound = true;\r
-               while (tagFound && keepRunning) {\r
+               while (tagFound && keepRunning && !interrupt) {\r
                        endPos = content.indexOf("</en-crypt>", index)+11;\r
                        if (endPos > -1 && index > -1) {\r
                                content = content.substring(0,index)+content.substring(endPos);\r
@@ -555,6 +575,11 @@ public class IndexRunner extends QObject implements Runnable {
                                foundWords.add(buffer.toString());\r
                                foundWords.add(word);\r
                                conn.getWordsTable().addWordToNoteIndex(guid, buffer.toString(), type, 100);\r
+                               uncommittedCount++;\r
+                               if (uncommittedCount > 100) {\r
+                                       conn.commitTransaction();\r
+                                       uncommittedCount=0;\r
+                               }\r
                        }\r
                }\r
                return;\r
@@ -570,7 +595,7 @@ public class IndexRunner extends QObject implements Runnable {
                }\r
                for (int i=0; i<notes.size() && !interrupt && keepRunning; i++) {\r
                        guid = notes.get(i);\r
-                       if (guid != null && keepRunning) {\r
+                       if (guid != null && keepRunning && !interrupt) {\r
                                waitSeconds(1);\r
                                indexNoteContent();\r
                        }\r
@@ -605,6 +630,8 @@ public class IndexRunner extends QObject implements Runnable {
 \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
index 61a90bb..b565d24 100644 (file)
@@ -167,9 +167,6 @@ public class SyncRunner extends QObject implements Runnable {
                authRefreshNeeded = false;\r
                keepRunning = true;\r
                idle = true;\r
-               noteStore = null;\r
-               userStore = null;\r
-               authToken = null;\r
                disableUploads = false;\r
                ignoreTags = new TreeSet<String>();\r
                ignoreNotebooks = new TreeSet<String>();\r
@@ -207,7 +204,6 @@ public class SyncRunner extends QObject implements Runnable {
                                                status.message.emit(e.getMessage());\r
                                        }\r
                                }\r
-                               dirtyNoteGuids = null;\r
                                idle=true;\r
                                logger.log(logger.EXTREME, "Signaling refresh finished.  refreshNeeded=" +refreshNeeded);\r
                                syncSignal.finished.emit(refreshNeeded);\r
@@ -740,7 +736,6 @@ public class SyncRunner extends QObject implements Runnable {
                                error = true;\r
                        }               \r
                }\r
-               remoteList = null;\r
                logger.log(logger.HIGH, "Leaving SyncRunner.syncLocalNotebooks");\r
 \r
        }\r
@@ -837,7 +832,6 @@ public class SyncRunner extends QObject implements Runnable {
                        enTag = findNextTag();\r
                }\r
                logger.log(logger.HIGH, "Leaving SyncRunner.syncLocalTags");\r
-               remoteList = null;\r
        }\r
        private void syncLocalLinkedNotebooks() {\r
                logger.log(logger.HIGH, "Entering SyncRunner.syncLocalLinkedNotebooks");\r
@@ -962,9 +956,7 @@ public class SyncRunner extends QObject implements Runnable {
                                error = true;\r
                        }               \r
                }\r
-               \r
-               remoteList = null;\r
-               searches = null;\r
+\r
                logger.log(logger.HIGH, "Entering SyncRunner.syncLocalSavedSearches");\r
        }       \r
 \r
@@ -994,9 +986,9 @@ public class SyncRunner extends QObject implements Runnable {
                                if (!refreshConnection())\r
                                        return;\r
                        \r
-                       chunk = null;\r
                        int sequence = updateSequenceNumber;\r
                        try {\r
+                               conn.beginTransaction();\r
                                logger.log(logger.EXTREME, "Getting chunk from Evernote");\r
                                chunk = noteStore.getSyncChunk(authToken, sequence, chunkSize, fullSync);\r
                        } catch (EDAMUserException e) {\r
@@ -1040,6 +1032,7 @@ public class SyncRunner extends QObject implements Runnable {
                                updateSequenceNumber = chunk.getChunkHighUSN();\r
                                conn.getSyncTable().setLastSequenceDate(chunk.getCurrentTime());\r
                                conn.getSyncTable().setUpdateSequenceNumber(updateSequenceNumber);\r
+                               conn.commitTransaction();\r
                        }\r
                        \r
                        \r
@@ -1049,9 +1042,8 @@ public class SyncRunner extends QObject implements Runnable {
                                pct = pct/evernoteUpdateCount;\r
                                status.message.emit(tr("Downloading ") +new Long(pct).toString()+tr("% complete."));\r
                        }\r
+                       conn.commitTransaction();\r
                }\r
-\r
-               dirtyNoteGuids = null;\r
                logger.log(logger.HIGH, "Leaving SyncRunner.syncRemoteToLocal");\r
        }\r
        // Sync expunged notes\r
index 024ce6f..5e1b64c 100644 (file)
@@ -72,6 +72,7 @@ public class ThumbnailRunner extends QObject implements Runnable {
        private String                                                          guid;\r
        public  NoteSignal                                                      noteSignal;\r
        private boolean                                                         keepRunning;\r
+       public boolean                                                          interrupt;\r
        private final DatabaseConnection                        conn;\r
        private volatile LinkedBlockingQueue<String> workQueue;\r
        private static int                                                      MAX_QUEUED_WAITING = 1000;\r
@@ -97,6 +98,7 @@ public class ThumbnailRunner extends QObject implements Runnable {
                logger.log(logger.MEDIUM, "Starting thumbnail thread ");\r
                while (keepRunning) {\r
                        try {\r
+                               interrupt = false;\r
                                String work = workQueue.take();\r
                                if (work.startsWith("GENERATE")) {\r
                                        work = work.replace("GENERATE ", "");\r
@@ -104,7 +106,8 @@ public class ThumbnailRunner extends QObject implements Runnable {
                                        generateThumbnail();\r
                                }\r
                                if (work.startsWith("SCAN")) {\r
-                                       scanDatabase();\r
+                                       if (conn.getNoteTable().getThumbnailNeededCount() > 1)\r
+                                               scanDatabase();\r
                                }\r
                                if (work.startsWith("IMAGE")) {\r
                                        work = work.replace("IMAGE ", "");\r
@@ -174,7 +177,7 @@ public class ThumbnailRunner extends QObject implements Runnable {
                // Find a few records that need thumbnails\r
                List<String> guids = conn.getNoteTable().findThumbnailsNeeded();\r
                logger.log(logger.HIGH, guids.size() +" records returned");\r
-               for (int i=0; i<guids.size() && keepRunning; i++) {\r
+               for (int i=0; i<guids.size() && keepRunning && !interrupt; i++) {\r
                        guid = guids.get(i);\r
                        logger.log(logger.HIGH, "Working on:" +guids.get(i));\r
                        generateThumbnail();\r