OSDN Git Service

There are multiple changes with this commit.
authorRandy Baumgarte <randy@fbn.cx>
Fri, 23 Jul 2010 16:49:41 +0000 (12:49 -0400)
committerRandy Baumgarte <randy@fbn.cx>
Fri, 23 Jul 2010 16:49:41 +0000 (12:49 -0400)
- Added the ability to do basic GeoTag handling.  Tags can be added, removed, or launched in a separate browser to bring up Google Earth.

- Removed the DBRunner thread and changed all existing threads to have dedicated connections to the database.  This allows for the removal of a lot of the classes underneath cx.fbn.nevernote.sql.* since they are no longer needed.

57 files changed:
src/cx/fbn/nevernote/Global.java
src/cx/fbn/nevernote/NeverNote.java
src/cx/fbn/nevernote/dialog/GeoDialog.java [new file with mode: 0644]
src/cx/fbn/nevernote/dialog/WatchFolder.java
src/cx/fbn/nevernote/dialog/WatchFolderAdd.java
src/cx/fbn/nevernote/filters/EnSearch.java
src/cx/fbn/nevernote/gui/BrowserWindow.java
src/cx/fbn/nevernote/icons/globe.png [new file with mode: 0644]
src/cx/fbn/nevernote/icons/splash_logo.png
src/cx/fbn/nevernote/sql/DatabaseConnection.java
src/cx/fbn/nevernote/sql/DeletedItemRecord.java [moved from src/cx/fbn/nevernote/sql/runners/DeletedItemRecord.java with 92% similarity]
src/cx/fbn/nevernote/sql/DeletedTable.java
src/cx/fbn/nevernote/sql/InvalidXMLTable.java
src/cx/fbn/nevernote/sql/NoteResourceTable.java
src/cx/fbn/nevernote/sql/NoteTable.java
src/cx/fbn/nevernote/sql/NoteTagsRecord.java [moved from src/cx/fbn/nevernote/sql/runners/NoteTagsRecord.java with 92% similarity]
src/cx/fbn/nevernote/sql/NoteTagsTable.java
src/cx/fbn/nevernote/sql/NotebookTable.java
src/cx/fbn/nevernote/sql/REnSearch.java [moved from src/cx/fbn/nevernote/sql/runners/REnSearch.java with 95% similarity]
src/cx/fbn/nevernote/sql/SavedSearchTable.java
src/cx/fbn/nevernote/sql/SyncTable.java
src/cx/fbn/nevernote/sql/TagTable.java
src/cx/fbn/nevernote/sql/WatchFolderRecord.java [moved from src/cx/fbn/nevernote/sql/runners/WatchFolderRecord.java with 92% similarity]
src/cx/fbn/nevernote/sql/WatchFolderTable.java
src/cx/fbn/nevernote/sql/WordsTable.java
src/cx/fbn/nevernote/sql/requests/DBRunnerRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/DatabaseRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/DeletedItemRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/EnSearchRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/InvalidXMLRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/NoteRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/NoteTagsRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/NotebookRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/ResourceRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/SavedSearchRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/SyncRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/TagRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/WatchFolderRequest.java [deleted file]
src/cx/fbn/nevernote/sql/requests/WordRequest.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RDatabaseConnection.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RDeletedTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RInvalidXMLTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RNoteResourceTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RNoteTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RNoteTagsTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RNotebookTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RSavedSearchTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RSyncTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RTagTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RWatchFolderTable.java [deleted file]
src/cx/fbn/nevernote/sql/runners/RWordsTable.java [deleted file]
src/cx/fbn/nevernote/threads/CounterRunner.java
src/cx/fbn/nevernote/threads/DBRunner.java [deleted file]
src/cx/fbn/nevernote/threads/IndexRunner.java
src/cx/fbn/nevernote/threads/SaveRunner.java
src/cx/fbn/nevernote/threads/SyncRunner.java
src/cx/fbn/nevernote/utilities/ListManager.java

index 7bb54a4..3a124b5 100644 (file)
@@ -21,6 +21,7 @@ package cx.fbn.nevernote;
 \r
 \r
 //import java.io.ByteArrayOutputStream;\r
+\r
 import java.io.PrintStream;\r
 import java.text.SimpleDateFormat;\r
 import java.util.ArrayList;\r
@@ -34,7 +35,6 @@ import com.evernote.edam.type.PrivilegeLevel;
 import com.evernote.edam.type.User;\r
 import com.evernote.edam.type.UserAttributes;\r
 import com.trolltech.qt.core.QByteArray;\r
-import com.trolltech.qt.core.QMutex;\r
 import com.trolltech.qt.core.QSettings;\r
 import com.trolltech.qt.gui.QPalette;\r
 \r
@@ -44,20 +44,13 @@ import cx.fbn.nevernote.config.StartupConfig;
 import cx.fbn.nevernote.gui.ContainsAttributeFilterTable;\r
 import cx.fbn.nevernote.gui.DateAttributeFilterTable;\r
 import cx.fbn.nevernote.gui.ShortcutKeys;\r
-import cx.fbn.nevernote.signals.DBRunnerSignal;\r
-import cx.fbn.nevernote.threads.DBRunner;\r
 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 \r
 public class Global {\r
        public static String version = "0.88";\r
     public static String username = ""; \r
-    public static String password = ""; \r
-\r
-    public static DBRunner                             dbRunner;                       // Database thread\r
-    public static DBRunnerSignal               dbRunnerSignal;         // Signals to the database runner\r
-    public static QMutex                               dbrunnerWorkLock;       // mutex lock for work queue\r
+    public static String password = "";     \r
     \r
-\r
     public static int mainThreadId=0;\r
     private static ArrayBlockingQueue<Boolean> mainThreadWaiter = new ArrayBlockingQueue<Boolean>(1);\r
     \r
@@ -86,7 +79,7 @@ public class Global {
     private static ArrayBlockingQueue<Boolean> indexThread04ThreadWaiter = new ArrayBlockingQueue<Boolean>(1);\r
     \r
     public static int dbThreadId=9;   // This should always be the highest thread ID\r
-    \r
+\r
     \r
     public static HashMap<String,String> passwordSafe = new HashMap<String, String>();\r
     public static List<String> passwordRemember = new ArrayList<String>();\r
@@ -118,6 +111,7 @@ public class Global {
     private static String wordRegex;\r
     public static boolean enableCarriageReturnFix = false;\r
     \r
+    public static String name = null;\r
     public static QSettings    settings;\r
     public static boolean isConnected;\r
     public static boolean showDeleted = false;\r
@@ -139,7 +133,7 @@ public class Global {
        PrintStream stdoutStream;\r
        public static QPalette                          originalPalette;\r
        public static ShortcutKeys                      shortcutKeys;\r
-       private static boolean                          disableViewing;\r
+       public static boolean                           disableViewing;\r
        \r
        public static List<String>                              invalidElements = new ArrayList<String>();\r
        public static HashMap<String, ArrayList<String>>        invalidAttributes = new HashMap<String, ArrayList<String>>();\r
@@ -151,7 +145,7 @@ public class Global {
        static Calendar intervalTraceTime;\r
 \r
        private static FileManager fileManager;\r
-\r
+       \r
     // Do initial setup \r
     public static void setup(StartupConfig startupConfig) throws InitializationException  {\r
         settings = new QSettings("fbn.cx", startupConfig.getName());\r
@@ -159,6 +153,7 @@ public class Global {
 \r
         fileManager = new FileManager(startupConfig.getHomeDirPath());\r
 \r
+\r
                        getServer();\r
                        settings.beginGroup("General");\r
                        String regex = (String) settings.value("regex", "[,\\s]+");\r
@@ -179,7 +174,6 @@ public class Global {
 //                     indexLock = new DBLock();\r
                        logger = new ApplicationLogger("global.log");\r
                        shortcutKeys = new ShortcutKeys();\r
-                       dbrunnerWorkLock = new QMutex();\r
                        mimicEvernoteInterface = getMimicEvernoteInterface();\r
                        resourceMap = new HashMap<String,String>();\r
                                \r
@@ -910,6 +904,7 @@ public class Global {
     public static void dbContinue() {\r
 //     Global.dbThreadWait.wakeAll();\r
     }\r
+       \r
     public static synchronized void dbClientWait(int id) {\r
        if (id == mainThreadId) {\r
                        try {mainThreadWaiter.take(); } catch (InterruptedException e) {e.printStackTrace();}\r
@@ -1116,12 +1111,14 @@ public class Global {
                startTraceTime = null;\r
        }\r
 \r
+\r
     public static FileManager getFileManager() {\r
         return fileManager;\r
     }\r
-\r
     public static boolean getDisableViewing() {\r
         return disableViewing;\r
     }\r
+    \r
+\r
 }\r
 \r
index c9a95b3..5897cf1 100644 (file)
@@ -63,6 +63,7 @@ import com.trolltech.qt.core.QFile;
 import com.trolltech.qt.core.QFileInfo;
 import com.trolltech.qt.core.QFileSystemWatcher;
 import com.trolltech.qt.core.QIODevice;
+import com.trolltech.qt.core.QIODevice.OpenModeFlag;
 import com.trolltech.qt.core.QModelIndex;
 import com.trolltech.qt.core.QSize;
 import com.trolltech.qt.core.QTemporaryFile;
@@ -71,19 +72,22 @@ import com.trolltech.qt.core.QThreadPool;
 import com.trolltech.qt.core.QTimer;
 import com.trolltech.qt.core.QUrl;
 import com.trolltech.qt.core.Qt;
-import com.trolltech.qt.core.QIODevice.OpenModeFlag;
 import com.trolltech.qt.core.Qt.SortOrder;
 import com.trolltech.qt.core.Qt.WidgetAttribute;
 import com.trolltech.qt.gui.QAbstractItemView;
+import com.trolltech.qt.gui.QAbstractItemView.ScrollHint;
 import com.trolltech.qt.gui.QAction;
 import com.trolltech.qt.gui.QApplication;
 import com.trolltech.qt.gui.QCloseEvent;
 import com.trolltech.qt.gui.QColor;
 import com.trolltech.qt.gui.QComboBox;
+import com.trolltech.qt.gui.QComboBox.InsertPolicy;
 import com.trolltech.qt.gui.QCursor;
 import com.trolltech.qt.gui.QDesktopServices;
 import com.trolltech.qt.gui.QDialog;
 import com.trolltech.qt.gui.QFileDialog;
+import com.trolltech.qt.gui.QFileDialog.AcceptMode;
+import com.trolltech.qt.gui.QFileDialog.FileMode;
 import com.trolltech.qt.gui.QGridLayout;
 import com.trolltech.qt.gui.QHBoxLayout;
 import com.trolltech.qt.gui.QIcon;
@@ -93,11 +97,13 @@ import com.trolltech.qt.gui.QListWidget;
 import com.trolltech.qt.gui.QMainWindow;
 import com.trolltech.qt.gui.QMenu;
 import com.trolltech.qt.gui.QMessageBox;
+import com.trolltech.qt.gui.QMessageBox.StandardButton;
 import com.trolltech.qt.gui.QPixmap;
 import com.trolltech.qt.gui.QPrintDialog;
 import com.trolltech.qt.gui.QPrinter;
 import com.trolltech.qt.gui.QProgressBar;
 import com.trolltech.qt.gui.QSizePolicy;
+import com.trolltech.qt.gui.QSizePolicy.Policy;
 import com.trolltech.qt.gui.QSpinBox;
 import com.trolltech.qt.gui.QSplashScreen;
 import com.trolltech.qt.gui.QSplitter;
@@ -107,14 +113,8 @@ import com.trolltech.qt.gui.QTableWidgetItem;
 import com.trolltech.qt.gui.QTextEdit;
 import com.trolltech.qt.gui.QToolBar;
 import com.trolltech.qt.gui.QTreeWidgetItem;
-import com.trolltech.qt.gui.QAbstractItemView.ScrollHint;
-import com.trolltech.qt.gui.QComboBox.InsertPolicy;
-import com.trolltech.qt.gui.QFileDialog.AcceptMode;
-import com.trolltech.qt.gui.QFileDialog.FileMode;
-import com.trolltech.qt.gui.QMessageBox.StandardButton;
-import com.trolltech.qt.gui.QSizePolicy.Policy;
-import com.trolltech.qt.webkit.QWebSettings;
 import com.trolltech.qt.webkit.QWebPage.WebAction;
+import com.trolltech.qt.webkit.QWebSettings;
 import com.trolltech.qt.xml.QDomAttr;
 import com.trolltech.qt.xml.QDomDocument;
 import com.trolltech.qt.xml.QDomElement;
@@ -148,10 +148,8 @@ import cx.fbn.nevernote.gui.TableView;
 import cx.fbn.nevernote.gui.TagTreeWidget;
 import cx.fbn.nevernote.gui.Thumbnailer;
 import cx.fbn.nevernote.gui.TrashTreeWidget;
-import cx.fbn.nevernote.signals.DBRunnerSignal;
 import cx.fbn.nevernote.sql.DatabaseConnection;
-import cx.fbn.nevernote.sql.runners.WatchFolderRecord;
-import cx.fbn.nevernote.threads.DBRunner;
+import cx.fbn.nevernote.sql.WatchFolderRecord;
 import cx.fbn.nevernote.threads.IndexRunner;
 import cx.fbn.nevernote.threads.SyncRunner;
 import cx.fbn.nevernote.utilities.AESEncrypter;
@@ -206,7 +204,6 @@ public class NeverNote extends QMainWindow{
     boolean                                    noteDirty;                                      // Has the note been changed?
     boolean                            inkNote;                    // if this is an ink note, it is read only
   
-    QThread                                    dbThread;                                       // Thread to handle DB communication
     ListManager                                listManager;                                    // DB runnable task
     
     List<QTemporaryFile>       tempFiles;                                      // Array of temporary files;
@@ -291,21 +288,14 @@ public class NeverNote extends QMainWindow{
     //***************************************************************
     //***************************************************************
     // Application Constructor 
-       private NeverNote(DatabaseConnection dbConn)  {
-               conn = dbConn;
+       public NeverNote(DatabaseConnection dbConn)  {
+               conn = dbConn;          
 
                thread().setPriority(Thread.MAX_PRIORITY);
                
                logger = new ApplicationLogger("nevernote.log");
                logger.log(logger.HIGH, tr("Starting Application"));
 
-               logger.log(logger.EXTREME, tr("Starting DB thread"));
-               dbThread = new QThread(Global.dbRunner, "Database Thread");
-               dbThread.start();
-               logger.log(logger.EXTREME, tr("DB Thread has started"));
-               Global.dbRunnerSignal.start.emit();
-               logger.log(logger.EXTREME, tr("Setting main thread DB connection"));
-               logger.log(logger.EXTREME, tr("main DB thread setup complete."));
                conn.checkDatabaseVersion();
                
                // Start building the invalid XML tables
@@ -340,7 +330,7 @@ public class NeverNote extends QMainWindow{
         listManager = new ListManager(conn, logger, Global.mainThreadId);
         
                logger.log(logger.EXTREME, tr("Building index runners & timers"));
-        indexRunner = new IndexRunner("indexRunner.log");
+        indexRunner = new IndexRunner("indexRunner.log", Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
                indexThread = new QThread(indexRunner, "Index Thread");
                indexThread.start();
                
@@ -357,7 +347,7 @@ public class NeverNote extends QMainWindow{
                                
                logger.log(logger.EXTREME, tr("Setting sync thread & timers"));
                syncThreadsReady=1;
-               syncRunner = new SyncRunner("syncRunner.log");
+               syncRunner = new SyncRunner("syncRunner.log", Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
                syncTime = new SyncTimes().timeValue(Global.getSyncInterval());
                syncTimer = new QTimer();
                syncTimer.timeout.connect(this, "syncTimer()");
@@ -600,8 +590,8 @@ public class NeverNote extends QMainWindow{
                QApplication.initialize(args);
                QPixmap pixmap = new QPixmap("classpath:cx/fbn/nevernote/icons/splash_logo.png");
                QSplashScreen splash = new QSplashScreen(pixmap);
-
                boolean showSplash;
+               
                DatabaseConnection dbConn;
 
         try {
@@ -624,6 +614,7 @@ public class NeverNote extends QMainWindow{
         }
 
         NeverNote application = new NeverNote(dbConn);
+
                application.setAttribute(WidgetAttribute.WA_DeleteOnClose, true);
                if (Global.wasWindowMaximized())
                        application.showMaximized();
@@ -642,10 +633,9 @@ public class NeverNote extends QMainWindow{
      * @throws InitializationException when opening the database fails, e.g. because another process has it locked
      */
     private static DatabaseConnection setupDatabaseConnection() throws InitializationException {
-        DatabaseConnection dbConn = new DatabaseConnection(Global.mainThreadId);
-        dbConn.dbSetup();
+       ApplicationLogger logger = new ApplicationLogger("nevernote-database.log");
+       DatabaseConnection dbConn = new DatabaseConnection(logger,Global.getDatabaseUrl(), Global.getDatabaseUserid(), Global.getDatabaseUserPassword(), Global.cipherPassword);
 
-        Global.dbRunnerSignal = new DBRunnerSignal();
         if (Global.getDatabaseUrl().toUpperCase().indexOf("CIPHER=") > -1) {
             boolean goodCheck = false;
             while (!goodCheck) {
@@ -658,8 +648,6 @@ public class NeverNote extends QMainWindow{
                         Global.getDatabaseUserPassword(), Global.cipherPassword);
             }
         }
-        Global.dbRunner = new DBRunner(Global.getDatabaseUrl(), Global.getDatabaseUserid(),
-                Global.getDatabaseUserPassword(), Global.cipherPassword);
         return dbConn;
     }
 
@@ -775,15 +763,6 @@ public class NeverNote extends QMainWindow{
                        }
                }
 
-               logger.log(logger.EXTREME, "Shutting down database");
-               conn.dbShutdown();
-               logger.log(logger.EXTREME, "Waiting for DB thread to terminate");
-               try {
-                       dbThread.join();
-               } catch (InterruptedException e) {
-                       e.printStackTrace();
-               }
-               logger.log(logger.EXTREME, "DB Thread has terminated");
                logger.log(logger.HIGH, "Leaving NeverNote.closeEvent");
        }
 
@@ -844,7 +823,9 @@ public class NeverNote extends QMainWindow{
            browserWindow.noteSignal.subjectDateChanged.connect(listManager, "updateNoteSubjectDate(String, QDateTime)");
            browserWindow.noteSignal.subjectDateChanged.connect(this, "updateListDateSubject(String, QDateTime)");
            browserWindow.noteSignal.authorChanged.connect(listManager, "updateNoteAuthor(String, String)");
+           browserWindow.noteSignal.geoChanged.connect(listManager, "updateNoteGeoTag(String, Double,Double,Double)");
            browserWindow.noteSignal.authorChanged.connect(this, "updateListAuthor(String, String)");
+           browserWindow.noteSignal.geoChanged.connect(this, "setNoteDirty()");
            browserWindow.noteSignal.sourceUrlChanged.connect(listManager, "updateNoteSourceUrl(String, String)");
            browserWindow.noteSignal.sourceUrlChanged.connect(this, "updateListSourceUrl(String, String)");
            browserWindow.focusLost.connect(this, "saveNote()");
@@ -852,6 +833,8 @@ public class NeverNote extends QMainWindow{
 //         browserWindow.resourceSignal.externalFileEdit.connect(this, "saveResourceLater(String, String)");
        }
 
+       
+
        //***************************************************************
        //***************************************************************
        //* Settings and look & feel
@@ -2626,7 +2609,7 @@ public class NeverNote extends QMainWindow{
        logger.log(logger.HIGH, "Leaving NeverNote.updateListAuthor");
     }
        private void updateListNoteNotebook(String guid, String notebook) {
-       logger.log(logger.HIGH, "Entering NeverNote.updateListAuthor");
+       logger.log(logger.HIGH, "Entering NeverNote.updateListNoteNotebook");
 
        for (int i=0; i<noteTableView.model.rowCount(); i++) {
                //QModelIndex modelIndex =  noteTableView.proxyModel.index(i, Global.noteTableGuidPosition);
@@ -2642,7 +2625,7 @@ public class NeverNote extends QMainWindow{
                        }       
                }
        }
-       logger.log(logger.HIGH, "Leaving NeverNote.updateListAuthor");
+       logger.log(logger.HIGH, "Leaving NeverNote.updateListNoteNotebook");
     }
     // Update a title for a specific note in the list
     @SuppressWarnings("unused")
@@ -2978,7 +2961,7 @@ public class NeverNote extends QMainWindow{
     // Get a note from Evernote (and put it in the browser)
        private void refreshEvernoteNote(boolean reload) {
                logger.log(logger.HIGH, "Entering NeverNote.refreshEvernoteNote");
-               if (Global.getDisableViewing()) {
+               if (Global.disableViewing) {
                        browserWindow.setEnabled(false);
                        return;
                }
@@ -3747,6 +3730,8 @@ public class NeverNote extends QMainWindow{
         if (f.exists()) {
             return relativePath;
         }
+       if (f.exists())
+               return appl+".png";
        logger.log(logger.HIGH, "Leaving NeverNote.findIcon");
        return "attachment.png";
     }
@@ -3778,7 +3763,7 @@ public class NeverNote extends QMainWindow{
                                        fileDetails = r.getAttributes().getFileName();
                                String contextFileName;
                                FileManager fileManager = Global.getFileManager();
-                                if (fileDetails != null && !fileDetails.equals("")) {
+                if (fileDetails != null && !fileDetails.equals("")) {
                                        enmedia.setAttribute("href", "nnres://" +r.getGuid() +Global.attachmentNameDelimeter +fileDetails);
                                        contextFileName = fileManager.getResDirPath(r.getGuid() + Global.attachmentNameDelimeter + fileDetails);
                                } else { 
@@ -3849,7 +3834,7 @@ public class NeverNote extends QMainWindow{
                                // NFC TODO: should this be a 'file://' URL?
                                newText.setAttribute("src", Global.getFileManager().getImageDirPath(icon));
                                if (goodPreview) {
-                                       // NFC TODO: should this be a 'file://' URL?
+                               // NFC TODO: should this be a 'file://' URL?
                                        newText.setAttribute("src", fileManager.getResDirPath(filePath));
                                        newText.setAttribute("style", "border-style:solid; border-color:green; padding:0.5mm 0.5mm 0.5mm 0.5mm;");
                                }
@@ -4124,9 +4109,6 @@ public class NeverNote extends QMainWindow{
 //             noteTableView.selectionModel().selectionChanged.connect(this, "noteTableSelection()");
 //             indexRunner.setKeepRunning(Global.keepRunning);
                
-               // Reload the unindexed table  If the dbthread is dead, we are probably shutting down.
-               if (!dbThread.isAlive())
-                       return;
                listManager.setUnsynchronizedNotes(conn.getNoteTable().getUnsynchronizedGUIDs());
                for (int i=0; i<noteTableView.model.rowCount(); i++) {
                        QModelIndex modelIndex =  noteTableView.model.index(i, Global.noteTableGuidPosition);
@@ -4332,13 +4314,6 @@ public class NeverNote extends QMainWindow{
                } else
                        saveThreadDeadCount=0;
 
-               if (!dbThread.isAlive()) {
-                       dbThreadDeadCount++;
-                       QMessageBox.information(this, "A thread his died.", "It appears as the database thread has died.  I recommend "+
-                       "checking stopping NeverNote, saving the logs for later viewing, and restarting.  Sorry.");
-               } else
-                       dbThreadDeadCount=0;
-
                if (!syncThread.isAlive()) {
                        syncThreadDeadCount++;
                        QMessageBox.information(this, "A thread his died.", "It appears as the synchronization thread has died.  I recommend "+
@@ -4706,7 +4681,6 @@ public class NeverNote extends QMainWindow{
                // Strip URL prefix and base dir path
                String dPath = FileUtils.toForwardSlashedPath(Global.getFileManager().getResDirPath());
                String name = fileName.replace(dPath, "");
-
                int pos = name.lastIndexOf('.');
                String guid = name;
                if (pos > -1) {
@@ -4813,6 +4787,9 @@ public class NeverNote extends QMainWindow{
                }
        }
 
+
+       
+       
        //*************************************************
        //* Check database userid & passwords
        //*************************************************
diff --git a/src/cx/fbn/nevernote/dialog/GeoDialog.java b/src/cx/fbn/nevernote/dialog/GeoDialog.java
new file mode 100644 (file)
index 0000000..eb03e73
--- /dev/null
@@ -0,0 +1,144 @@
+/*\r
+ * This file is part of NeverNote \r
+ * Copyright 2009 Randy Baumgarte\r
+ * \r
+ * This file may be licensed under the terms of of the\r
+ * GNU General Public License Version 2 (the ``GPL'').\r
+ *\r
+ * Software distributed under the License is distributed\r
+ * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
+ * express or implied. See the GPL for the specific language\r
+ * governing rights and limitations.\r
+ *\r
+ * You should have received a copy of the GPL along with this\r
+ * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
+ * or write to the Free Software Foundation, Inc.,\r
+ * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
+ *\r
+*/\r
+\r
+package cx.fbn.nevernote.dialog;\r
+\r
+import com.trolltech.qt.gui.QDialog;\r
+import com.trolltech.qt.gui.QDoubleValidator;\r
+import com.trolltech.qt.gui.QDoubleValidator.Notation;\r
+import com.trolltech.qt.gui.QGridLayout;\r
+import com.trolltech.qt.gui.QLabel;\r
+import com.trolltech.qt.gui.QLineEdit;\r
+import com.trolltech.qt.gui.QPushButton;\r
+\r
+public class GeoDialog extends QDialog {\r
+\r
+       private boolean         okPressed;\r
+       private final QLineEdit altitude;\r
+       private final QLineEdit latitude;\r
+       private final QLineEdit longitude;\r
+       private final QPushButton ok;\r
+       \r
+       \r
+       // Constructor\r
+       public GeoDialog() {\r
+               okPressed = false;\r
+               setWindowTitle("NeverNote Login");\r
+               QGridLayout grid = new QGridLayout();\r
+               setLayout(grid);\r
+               QGridLayout passwordGrid = new QGridLayout();\r
+               QGridLayout buttonGrid = new QGridLayout();\r
+               \r
+               \r
+               longitude = new QLineEdit();\r
+               QDoubleValidator longVal = new QDoubleValidator(-180.0,180.0,4,longitude);\r
+               longVal.setNotation(Notation.StandardNotation);\r
+               longitude.setValidator(longVal);\r
+               \r
+               latitude = new QLineEdit();\r
+               QDoubleValidator latVal = new QDoubleValidator(-90.0,90.0,4,latitude);\r
+               latVal.setNotation(Notation.StandardNotation);\r
+               latitude.setValidator(latVal);\r
+               \r
+               altitude = new QLineEdit();\r
+               QDoubleValidator altVal = new QDoubleValidator(-9999.0,9999.0,4,altitude);\r
+               altVal.setNotation(Notation.StandardNotation);\r
+               altitude.setValidator(altVal);\r
+\r
+               \r
+               passwordGrid.addWidget(new QLabel("Longitude"), 1,1);\r
+               passwordGrid.addWidget(longitude, 1, 2);\r
+               passwordGrid.addWidget(new QLabel("Latitude"), 2,1);\r
+               passwordGrid.addWidget(latitude, 2, 2);\r
+               passwordGrid.addWidget(new QLabel("Altitude"), 3,1);\r
+               passwordGrid.addWidget(altitude, 3, 2);\r
+               passwordGrid.setContentsMargins(10, 10,  -10, -10);\r
+               grid.addLayout(passwordGrid,1,1);\r
+               \r
+               ok = new QPushButton("OK");\r
+               ok.clicked.connect(this, "okButtonPressed()");\r
+               QPushButton cancel = new QPushButton("Cancel");\r
+               cancel.clicked.connect(this, "cancelButtonPressed()");\r
+               buttonGrid.addWidget(ok, 1, 1);\r
+               buttonGrid.addWidget(cancel, 1,2);\r
+               grid.addLayout(buttonGrid,2,1);\r
+       }\r
+       \r
+       // The OK button was pressed\r
+       @SuppressWarnings("unused")\r
+       private void okButtonPressed() {\r
+               okPressed = true;\r
+               close();\r
+       }\r
+       \r
+       // The CANCEL button was pressed\r
+       @SuppressWarnings("unused")\r
+       private void cancelButtonPressed() {\r
+               okPressed = false;\r
+               close();\r
+       }\r
+       \r
+       // Get the longitude\r
+       public double getLongitude() {\r
+               try {\r
+                       return new Double(longitude.text());\r
+               } catch (java.lang.NumberFormatException e) {\r
+                       return 0.0;\r
+               }\r
+       }\r
+       \r
+       // Get the latitude\r
+       public double getLatitude() {\r
+               try {\r
+                       return new Double(latitude.text());\r
+               } catch (java.lang.NumberFormatException e) {\r
+                       return 0.0;\r
+               }\r
+       }\r
+       \r
+       // Get the altitude\r
+       public double getAltitude() {\r
+               try {\r
+                       return new Double(altitude.text()); \r
+               } catch (java.lang.NumberFormatException e) {\r
+                       return 0.0;\r
+               }\r
+       }\r
+       \r
+       \r
+       public void setLongitude(double value) {\r
+               longitude.setText(new Float(value).toString());\r
+       }\r
+\r
+       public void setLatitude(double value) {\r
+               latitude.setText(new Float(value).toString());\r
+       }\r
+\r
+       public void setAltitude(double value) {\r
+               altitude.setText(new Float(value).toString());\r
+       }\r
+\r
+       \r
+       // Check if the OK button was pressed\r
+       public boolean okPressed() {\r
+               return okPressed;\r
+       }\r
+\r
+       \r
+}\r
index 3bec89b..3a86b39 100644 (file)
@@ -24,6 +24,8 @@ import java.util.List;
 \r
 import com.evernote.edam.type.Notebook;\r
 import com.trolltech.qt.core.QModelIndex;\r
+import com.trolltech.qt.gui.QAbstractItemView.SelectionBehavior;\r
+import com.trolltech.qt.gui.QAbstractItemView.SelectionMode;\r
 import com.trolltech.qt.gui.QApplication;\r
 import com.trolltech.qt.gui.QDialog;\r
 import com.trolltech.qt.gui.QFontMetrics;\r
@@ -32,10 +34,8 @@ import com.trolltech.qt.gui.QPushButton;
 import com.trolltech.qt.gui.QTableWidget;\r
 import com.trolltech.qt.gui.QTableWidgetItem;\r
 import com.trolltech.qt.gui.QVBoxLayout;\r
-import com.trolltech.qt.gui.QAbstractItemView.SelectionBehavior;\r
-import com.trolltech.qt.gui.QAbstractItemView.SelectionMode;\r
 \r
-import cx.fbn.nevernote.sql.runners.WatchFolderRecord;\r
+import cx.fbn.nevernote.sql.WatchFolderRecord;\r
 \r
 public class WatchFolder extends QDialog {\r
        private final QPushButton               okButton;\r
index 0186976..0e7fa53 100644 (file)
@@ -25,14 +25,14 @@ import com.evernote.edam.type.Notebook;
 import com.trolltech.qt.gui.QComboBox;\r
 import com.trolltech.qt.gui.QDialog;\r
 import com.trolltech.qt.gui.QFileDialog;\r
+import com.trolltech.qt.gui.QFileDialog.FileMode;\r
 import com.trolltech.qt.gui.QGridLayout;\r
 import com.trolltech.qt.gui.QHBoxLayout;\r
 import com.trolltech.qt.gui.QLabel;\r
 import com.trolltech.qt.gui.QPushButton;\r
 import com.trolltech.qt.gui.QVBoxLayout;\r
-import com.trolltech.qt.gui.QFileDialog.FileMode;\r
 \r
-import cx.fbn.nevernote.sql.runners.WatchFolderRecord;\r
+import cx.fbn.nevernote.sql.WatchFolderRecord;\r
 \r
 public class WatchFolderAdd extends QDialog {\r
        private final QPushButton               okButton;\r
index 5265b1d..0a91692 100644 (file)
 \r
 package cx.fbn.nevernote.filters;\r
 \r
-import java.util.ArrayList;\r
 import java.util.List;\r
 \r
 import com.evernote.edam.type.Note;\r
 import com.evernote.edam.type.Tag;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.EnSearchRequest;\r
+import cx.fbn.nevernote.sql.DatabaseConnection;\r
+import cx.fbn.nevernote.sql.REnSearch;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 \r
 public class EnSearch {\r
        \r
 \r
        private List<Note>                      matches;\r
        public List<String>                     hilightWords;\r
-       int id;\r
        \r
-       public EnSearch(int i, String s, List<Tag> t, int len, int weight) {\r
-               id = i;\r
+       public EnSearch(DatabaseConnection conn, ApplicationLogger logger, String s, List<Tag> t, int len, int weight) {\r
                if (s == null) \r
                        return;\r
                if (s.trim().equals(""))\r
                        return;\r
                \r
                matches = null;\r
-               EnSearchRequest request = new EnSearchRequest();\r
-               request.requestor_id = id;\r
-//             request.type = DeletedItemRequest.Get_All;\r
-               request.string1 = s;\r
-               if (t!=null) {\r
-                       request.tags = new ArrayList<Tag>();\r
-                       for (int k=0; k<t.size(); k++) {\r
-                               request.tags.add(t.get(k).deepCopy());\r
-                       }\r
-               }\r
-               request.int1 = len;\r
-               request.int2 = weight;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               EnSearchRequest req = Global.dbRunner.enSearchResponse.get(id);\r
-               hilightWords = req.responseStrings;\r
-               matches = req.responseNotes;\r
+               REnSearch request = new REnSearch(conn, logger, s, t, len, weight);\r
+               matches = request.matchWords();\r
+               hilightWords = request.getWords();\r
        }\r
                \r
        public List<Note> matchWords() {\r
index ca12e3a..50bdccf 100644 (file)
@@ -57,6 +57,8 @@ import com.trolltech.qt.gui.QComboBox;
 import com.trolltech.qt.gui.QDateEdit;\r
 import com.trolltech.qt.gui.QDesktopServices;\r
 import com.trolltech.qt.gui.QFileDialog;\r
+import com.trolltech.qt.gui.QFileDialog.AcceptMode;\r
+import com.trolltech.qt.gui.QFileDialog.FileMode;\r
 import com.trolltech.qt.gui.QFontDatabase;\r
 import com.trolltech.qt.gui.QFormLayout;\r
 import com.trolltech.qt.gui.QGridLayout;\r
@@ -74,17 +76,16 @@ import com.trolltech.qt.gui.QShortcut;
 import com.trolltech.qt.gui.QTimeEdit;\r
 import com.trolltech.qt.gui.QVBoxLayout;\r
 import com.trolltech.qt.gui.QWidget;\r
-import com.trolltech.qt.gui.QFileDialog.AcceptMode;\r
-import com.trolltech.qt.gui.QFileDialog.FileMode;\r
 import com.trolltech.qt.network.QNetworkRequest;\r
 import com.trolltech.qt.webkit.QWebPage;\r
+import com.trolltech.qt.webkit.QWebPage.WebAction;\r
 import com.trolltech.qt.webkit.QWebSettings;\r
 import com.trolltech.qt.webkit.QWebView;\r
-import com.trolltech.qt.webkit.QWebPage.WebAction;\r
 \r
 import cx.fbn.nevernote.Global;\r
 import cx.fbn.nevernote.dialog.EnCryptDialog;\r
 import cx.fbn.nevernote.dialog.EnDecryptDialog;\r
+import cx.fbn.nevernote.dialog.GeoDialog;\r
 import cx.fbn.nevernote.dialog.InsertLinkDialog;\r
 import cx.fbn.nevernote.dialog.TableDialog;\r
 import cx.fbn.nevernote.dialog.TagAssign;\r
@@ -101,6 +102,7 @@ public class BrowserWindow extends QWidget {
        private final QLineEdit urlText;\r
        private final QLabel authorLabel;\r
        private final QLineEdit authorText;\r
+       private final QComboBox geoBox;\r
        public final TagLineEdit tagEdit;\r
        public final QLabel tagLabel;\r
        private final QLabel urlLabel;\r
@@ -190,6 +192,7 @@ public class BrowserWindow extends QWidget {
                titleLabel.setMaxLength(Constants.EDAM_NOTE_TITLE_LEN_MAX);\r
                urlText = new QLineEdit();\r
                authorText = new QLineEdit();\r
+               geoBox = new QComboBox();\r
                urlLabel = new QLabel();\r
                authorLabel = new QLabel();\r
                conn = c;\r
@@ -239,6 +242,14 @@ public class BrowserWindow extends QWidget {
                urlLabel.setVisible(false);\r
                urlText.setVisible(false);\r
                authorLabel.setVisible(false);\r
+               \r
+               geoBox.setVisible(false);\r
+               geoBox.addItem(new QIcon(iconPath+"globe.png"), "");\r
+               geoBox.addItem(new String("Set"));\r
+               geoBox.addItem(new String("Clear"));\r
+               geoBox.addItem(new String("View On Map"));\r
+               geoBox.activated.connect(this, "geoBoxChanged()");\r
+               \r
                authorText.setVisible(false);\r
                createdDate.setVisible(false);\r
                alteredLabel.setVisible(false);\r
@@ -291,6 +302,7 @@ public class BrowserWindow extends QWidget {
                QHBoxLayout authorLayout = new QHBoxLayout();\r
                authorLayout.addWidget(authorLabel, 0);\r
                authorLayout.addWidget(authorText, 0);\r
+               authorLayout.addWidget(geoBox);\r
                v.addLayout(authorLayout);\r
 \r
                dateLayout.addWidget(createdLabel, 0, 0);\r
@@ -469,6 +481,7 @@ public class BrowserWindow extends QWidget {
                notebookBox.setEnabled(!v);\r
                tagEdit.setEnabled(!v);\r
                authorLabel.setEnabled(!v);\r
+               geoBox.setEnabled(!v);\r
                urlText.setEnabled(!v);\r
                createdDate.setEnabled(!v);\r
                subjectDate.setEnabled(!v);\r
@@ -599,6 +612,7 @@ public class BrowserWindow extends QWidget {
                urlLabel.setVisible(extendedOn);\r
                urlText.setVisible(extendedOn);\r
                authorText.setVisible(extendedOn);\r
+               geoBox.setVisible(extendedOn);\r
                authorLabel.setVisible(extendedOn);\r
                createdDate.setVisible(extendedOn);\r
                createdTime.setVisible(extendedOn);\r
@@ -1617,6 +1631,7 @@ public class BrowserWindow extends QWidget {
                                icon = findIcon(type[0]);\r
                        if (icon.equals("attachment.png"))\r
                                icon = findIcon(url.substring(url.lastIndexOf(".")+1));\r
+                       StringBuffer imageBuffer = new StringBuffer();\r
                        String imageURL = FileUtils.toFileURLString(Global.getFileManager().getImageDirFile(icon));\r
 \r
                        logger.log(logger.EXTREME, "Creating resource ");\r
@@ -1650,17 +1665,16 @@ public class BrowserWindow extends QWidget {
 \r
                                PDFPreview pdfPreview = new PDFPreview();\r
                                if (pdfPreview.setupPreview(Global.getFileManager().getResDirPath(fileName), "pdf",0)) {\r
-                                       // NFC TODO: should this be a 'file://' url like the ones above?\r
-                                       imageURL = file.fileName() + ".png";\r
+                               // NFC TODO: should this be a 'file://' url like the ones above?\r
+                               imageURL = file.fileName() + ".png";\r
                                }\r
                        }\r
                                                \r
                        logger.log(logger.EXTREME, "Generating link tags");\r
                        buffer.append("<a en-tag=\"en-media\" guid=\"" +newRes.getGuid()+"\" ");\r
                        buffer.append(" onContextMenu=\"window.jambi.imageContextMenu(&apos;")\r
-                             .append(Global.getFileManager().getResDirPath(fileName))\r
-                             .append("&apos;);\" ");\r
-                       buffer.append("type=\"" + mimeType + "\" href=\"nnres://" + fileName +"\" hash=\""+Global.byteArrayToHexString(newRes.getData().getBodyHash()) +"\" >");\r
+                     .append(Global.getFileManager().getResDirPath(fileName))\r
+                     .append("&apos;);\" ");                   buffer.append("type=\"" + mimeType + "\" href=\"nnres://" + fileName +"\" hash=\""+Global.byteArrayToHexString(newRes.getData().getBodyHash()) +"\" >");\r
                        buffer.append("<img src=\"" + imageURL + "\" title=\"" +newRes.getAttributes().getFileName());\r
                        buffer.append("\"></img>");\r
                        buffer.append("</a>");\r
@@ -1835,6 +1849,42 @@ public class BrowserWindow extends QWidget {
        private void authorChanged() {\r
                noteSignal.authorChanged.emit(currentNote.getGuid(), authorText.text());\r
        }\r
+       \r
+       private void geoBoxChanged() {\r
+               int index = geoBox.currentIndex();\r
+               geoBox.setCurrentIndex(0);\r
+               if (index == 1) {\r
+                       GeoDialog box = new GeoDialog();\r
+                       box.setLongitude(currentNote.getAttributes().getLongitude());\r
+                       box.setLatitude(currentNote.getAttributes().getLatitude());\r
+                       box.setAltitude(currentNote.getAttributes().getAltitude());\r
+                       box.exec();\r
+                       if (!box.okPressed())\r
+                               return;\r
+                       double alt = box.getAltitude();\r
+                       double lat = box.getLatitude();\r
+                       double lon = box.getLongitude();\r
+                       if (alt != currentNote.getAttributes().getAltitude() ||\r
+                               lon != currentNote.getAttributes().getLongitude() ||\r
+                               lat != currentNote.getAttributes().getLatitude()) {\r
+                                       noteSignal.geoChanged.emit(currentNote.getGuid(), lon, lat, alt);\r
+                                       currentNote.getAttributes().setAltitude(alt);\r
+                                       currentNote.getAttributes().setLongitude(lon);\r
+                                       currentNote.getAttributes().setLatitude(lat);\r
+                       }\r
+               }\r
+               \r
+               if (index == 2) {\r
+                       noteSignal.geoChanged.emit(currentNote.getGuid(), 0.0, 0.0, 0.0);\r
+                       currentNote.getAttributes().setAltitude(0.0);\r
+                       currentNote.getAttributes().setLongitude(0.0);\r
+                       currentNote.getAttributes().setLatitude(0.0);\r
+               }\r
+               \r
+               if (index == 3 || index == 0) {\r
+                       QDesktopServices.openUrl(new QUrl("http://maps.google.com/maps?z=6&q="+currentNote.getAttributes().getLatitude() +"," +currentNote.getAttributes().getLongitude()));\r
+               }\r
+       }\r
 \r
        // ************************************************************\r
        // * User chose to save an attachment. Pares out the request *\r
@@ -1901,12 +1951,9 @@ public class BrowserWindow extends QWidget {
                fd.setAcceptMode(AcceptMode.AcceptSave);\r
                fd.setDirectory(System.getProperty("user.home"));\r
                String name = request.url().toString();\r
-\r
-               // Strip URL prefix and base dir path\r
                name = name.replace("nnres://", "");\r
                String dPath = FileUtils.toForwardSlashedPath(Global.getFileManager().getResDirPath());\r
                name = name.replace(dPath, "");\r
-\r
                int pos = name.lastIndexOf('.');\r
                String guid = name;\r
                if (pos > -1) {\r
@@ -2244,8 +2291,8 @@ public class BrowserWindow extends QWidget {
                                String source; \r
                                if (locTag.startsWith("src")) {\r
                                         source = newSegment.substring(startSrcPos+locTag.length(),endSrcPos);\r
-                                       newSegment = newSegment.replace(source,\r
-                                               FileUtils.toForwardSlashedPath(Global.getFileManager().getResDirPath(newFile)));\r
+                                               newSegment = newSegment.replace(source,\r
+                                                       FileUtils.toForwardSlashedPath(Global.getFileManager().getResDirPath(newFile)));\r
                                } else {\r
                                        source = newSegment.substring(startSrcPos+locTag.length(),endSrcPos);\r
                                        newSegment = newSegment.replace(source, newFile);\r
diff --git a/src/cx/fbn/nevernote/icons/globe.png b/src/cx/fbn/nevernote/icons/globe.png
new file mode 100644 (file)
index 0000000..5413db7
Binary files /dev/null and b/src/cx/fbn/nevernote/icons/globe.png differ
index 6206ea7..dd130be 100644 (file)
Binary files a/src/cx/fbn/nevernote/icons/splash_logo.png and b/src/cx/fbn/nevernote/icons/splash_logo.png differ
index b754828..c046b2a 100644 (file)
 package cx.fbn.nevernote.sql;
 
 import java.io.File;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+
+import com.trolltech.qt.sql.QJdbc;
 
 import cx.fbn.nevernote.Global;
-import cx.fbn.nevernote.sql.requests.DatabaseRequest;
+import cx.fbn.nevernote.sql.driver.NSqlQuery;
+import cx.fbn.nevernote.utilities.ApplicationLogger;
 
 
 public class DatabaseConnection {
        // Table helpers
-       private final WordsTable                                wordsTable;
-       private final TagTable                                  tagTable;
-       private final NotebookTable                             notebookTable;
-       private final NoteTable                                 noteTable;
-       private final DeletedTable                              deletedTable;
-       private final SavedSearchTable                  searchTable;
-       private final WatchFolderTable                  watchFolderTable;
-       private final InvalidXMLTable                   invalidXMLTable;
-       private final SyncTable                                 syncTable;
+       private WordsTable                                      wordsTable;
+       private TagTable                                        tagTable;
+       private NotebookTable                           notebookTable;
+       private NoteTable                                       noteTable;
+       private DeletedTable                            deletedTable;
+       private SavedSearchTable                        searchTable;
+       private WatchFolderTable                        watchFolderTable;
+       private InvalidXMLTable                         invalidXMLTable;
+       private SyncTable                                       syncTable;
+       private final ApplicationLogger         logger;
+       private Connection                                      conn;
        int id;
 
        
-       public DatabaseConnection(int i) {
-               id = i;
-               tagTable = new TagTable(id);
-               notebookTable = new NotebookTable(id);
-               noteTable = new NoteTable(id);
-               deletedTable = new DeletedTable(id);
-               searchTable = new SavedSearchTable(id);
-               watchFolderTable = new WatchFolderTable(id);
-               wordsTable = new WordsTable(id);
-               invalidXMLTable = new InvalidXMLTable(id);
-               syncTable = new SyncTable(id);
+       public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
+               logger = l;
+               dbSetup(url, userid, password, cypherPassword);
+       }
+       
+       private void setupTables() {
+               tagTable = new TagTable(logger, this);
+               notebookTable = new NotebookTable(logger, this);
+               noteTable = new NoteTable(logger, this);
+               deletedTable = new DeletedTable(logger, this);
+               searchTable = new SavedSearchTable(logger, this);       
+               watchFolderTable = new WatchFolderTable(logger, this);
+               invalidXMLTable = new InvalidXMLTable(logger, this);
+               wordsTable = new WordsTable(logger, this);
+               syncTable = new SyncTable(logger, this);
        }
        
        
+       // Compact the database
+       public void compactDatabase() {
+               
+       }
+       
        // Initialize the database connection
-       public void dbSetup() {
-               // NFC FIXME: should be parameterized with databaseName like in RDatabaseConnection?
-               File f = Global.getFileManager().getDbDirFile("NeverNote.h2.db");
+       public void dbSetup(String url,String userid, String userPassword, String cypherPassword) {
+               logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
+
+               
+               try {
+                       Class.forName("org.h2.Driver");
+               } catch (ClassNotFoundException e1) {
+                       e1.printStackTrace();
+                       System.exit(16);
+               }
+               
+               QJdbc.initialize();
+               
+               File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
                boolean dbExists = f.exists(); 
                
                // If it doesn't exist and we are the main thread, then we need to create stuff.
-               if (!dbExists && id  == 0)  {
+               if (!dbExists)  {
                        createTables();
                        Global.setAutomaticLogin(false);
                }
+               setupTables();
+               
+               logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
+               
+
+               try {
+                       String passwordString = null;
+                       if (cypherPassword==null || cypherPassword.trim().equals(""))
+                               passwordString = userPassword;
+                       else
+                               passwordString = cypherPassword+" "+userPassword;
+                       conn = DriverManager.getConnection(url,userid,passwordString);
+//                     conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
+               } catch (SQLException e) {
+                       e.printStackTrace();
+                       return;
+               }
+               
+               logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbSetup");
+               
+               
+               
+               logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
        }
        
        
        public void dbShutdown() {
-               DatabaseRequest req = new DatabaseRequest();
-               req.type = DatabaseRequest.Shutdown;
-               Global.dbRunner.addWork(req);
+               logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
+               try {
+                       conn.close();
+               } catch (SQLException e) {
+                       e.printStackTrace();
+               }
+               logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
        }
        
        public void upgradeDb(String version) {
                if (version.equals("0.85")) {
-                       DatabaseRequest req = new DatabaseRequest();
-                       req.type = DatabaseRequest.Execute_Sql;
-                       req.string1 = new String("alter table note add column titleColor integer");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.type = DatabaseRequest.Execute_Sql;
-                       req.string1 = new String("update note set titlecolor=-1");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.type = DatabaseRequest.Execute_Sql;
-                       req.string1 = new String("alter table note add column thumbnail blob");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("alter table note add column thumbnailneeded boolean");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("Update note set thumbnailneeded = true;");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
+                       executeSql("alter table note add column titleColor integer");
+                       executeSql("alter table note add column thumbnail blob");
+                       executeSql("alter table note add column thumbnailneeded boolean");
+                       executeSql("Update note set thumbnailneeded = true;");
+                       executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
+                       executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
                        version = "0.86";
                        Global.setDatabaseVersion(version);
                } 
        }
        
+       public void executeSql(String sql) {
+               NSqlQuery query = new NSqlQuery(conn);
+               query.exec(sql);        
+       }
+       
        public void checkDatabaseVersion() {
-               // NFC FIXME: this needs to read the existing version number from a table in the DB
                if (!Global.getDatabaseVersion().equals("0.86")) {
                        upgradeDb(Global.getDatabaseVersion());
                }
        }
        
-       public void compactDatabase() {
-               DatabaseRequest request = new DatabaseRequest();
-               request.requestor_id = id;
-               request.type = DatabaseRequest.Compact;
-               Global.dbRunner.addWork(request);
-               Global.dbClientWait(id);
-       }
 
        public void backupDatabase(int highSequence, long date) {
-               DatabaseRequest request = new DatabaseRequest();
-               request.requestor_id = id;
-               request.int1 = highSequence;
-               request.long1 = date;
-               request.type = DatabaseRequest.Backup_Database;
-               Global.dbRunner.addWork(request);
-               Global.dbClientWait(id);
+               
        }
        
        
-       private void createTables() {
+       public void createTables() {
                Global.setDatabaseVersion("0.85");
-//             Global.setUpdateSequenceNumber(0);
                Global.setAutomaticLogin(false);
                Global.saveCurrentNoteGuid("");
                Global.saveUploadAmount(0);
                
+               getTagTable().createTable();
+               notebookTable.createTable();
+               noteTable.createTable();
+               deletedTable.createTable();             
+               searchTable.createTable();
+               watchFolderTable.createTable();
+               invalidXMLTable.createTable();
+               wordsTable.createTable();
+               syncTable.createTable();
+               
+       }
+       
+       public Connection getConnection() {
+               return conn;
        }
        
        //***************************************************************
@@ -17,7 +17,7 @@
  *\r
 */\r
 \r
-package cx.fbn.nevernote.sql.runners;\r
+package cx.fbn.nevernote.sql;\r
 \r
 public class DeletedItemRecord {\r
        public String guid;\r
index 897590e..ea6610a 100644 (file)
 \r
 package cx.fbn.nevernote.sql;\r
 \r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.DeletedItemRequest;\r
-import cx.fbn.nevernote.sql.runners.DeletedItemRecord;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 import cx.fbn.nevernote.utilities.ListManager;\r
 \r
 public class DeletedTable {\r
        ListManager parent;\r
-       int id;\r
+       private final ApplicationLogger                 logger;\r
+       private final DatabaseConnection                db;\r
+\r
        \r
        // Constructor\r
-       public DeletedTable(int i) {\r
-               id = i;\r
+       public DeletedTable(ApplicationLogger l,DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               DeletedItemRequest request = new DeletedItemRequest();\r
-               request.requestor_id = id;\r
-               request.type = DeletedItemRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               logger.log(logger.HIGH, "Creating table DeletedItems...");\r
+        if (!query.exec("Create table DeletedItems (guid varchar primary key, type varchar)"))\r
+               logger.log(logger.HIGH, "Table DeletedItems creation FAILED!!!"); \r
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               DeletedItemRequest request = new DeletedItemRequest();\r
-               request.requestor_id = id;\r
-               request.type = DeletedItemRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table DeletedItems");\r
        }\r
        // Add an item to the deleted table\r
        public void addDeletedItem(String guid, String type) {\r
-               DeletedItemRequest request = new DeletedItemRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = guid;\r
-               request.string2 = type;\r
-               request.type = DeletedItemRequest.Add_Deleted_Item;\r
-               Global.dbRunner.addWork(request);\r
-       }\r
-       public List<DeletedItemRecord> getAllDeleted() {\r
-               DeletedItemRequest request = new DeletedItemRequest();\r
-               request.requestor_id = id;\r
-               request.type = DeletedItemRequest.Get_All;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               DeletedItemRequest req = Global.dbRunner.deletedItemResponse.get(id).copy();\r
-               return req.responseDeletedRecords;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Insert Into DeletedItems (guid, type) Values(:guid, :type)");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":type", type);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Insert into deleted items failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
+       // Add an item to the deleted table\r
        public void expungeDeletedItem(String guid, String type) {\r
-               DeletedItemRequest request = new DeletedItemRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = guid;\r
-               request.string2 = type;\r
-               request.type = DeletedItemRequest.Expunge_Record;\r
-               Global.dbRunner.addWork(request);\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("delete from DeletedItems where guid=:guid and type=:type");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":type", type);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Expunge deleted items failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       }\r
+       public List<DeletedItemRecord> getAllDeleted() {\r
+               logger.log(logger.HIGH, "Entering DeletedTable.getAllDeleted");\r
+               List<DeletedItemRecord> list = new ArrayList<DeletedItemRecord>();\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Select guid, type from DeletedItems");\r
+               while (query.next()) {\r
+                       DeletedItemRecord record = new DeletedItemRecord();\r
+                       record.guid = query.valueString(0);\r
+                       record.type = query.valueString(1);\r
+                       list.add(record);\r
+               }\r
+               logger.log(logger.HIGH, "Leaving DeletedTable.getAllDeleted");\r
+               return list;\r
+\r
        }\r
        public void expungeAllDeletedRecords() {\r
-               DeletedItemRequest request = new DeletedItemRequest();\r
-               request.requestor_id = id;\r
-               request.type = DeletedItemRequest.Expunge_All;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("delete from DeletedItems");\r
        }\r
 \r
 }\r
index 9d504e8..6bc373a 100644 (file)
@@ -23,81 +23,199 @@ package cx.fbn.nevernote.sql;
 import java.util.ArrayList;\r
 import java.util.List;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.InvalidXMLRequest;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 import cx.fbn.nevernote.utilities.ListManager;\r
 \r
 public class InvalidXMLTable {\r
        ListManager parent;\r
-       int id;\r
+       private final ApplicationLogger                 logger;\r
+       private final DatabaseConnection                db;\r
+\r
        \r
        // Constructor\r
-       public InvalidXMLTable(int i) {\r
-               id = i;\r
+       public InvalidXMLTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.type = InvalidXMLRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+//             query.exec("drop table InvalidXML");\r
+               logger.log(logger.HIGH, "Creating table InvalidXML...");\r
+        if (!query.exec("Create table InvalidXML (type varchar, element varchar, attribute varchar,primary key(type, element,attribute) );"))\r
+               logger.log(logger.HIGH, "Table InvalidXML creation FAILED!!!"); \r
+//        query.clear();\r
+        \r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'button', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'embed', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'fieldset', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'form', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'input', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'label', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'legend', '');");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'o:p', '')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'option', '')");        \r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'script', '')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'select', '')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ELEMENT', 'wbr', '')");\r
+        \r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'a', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'a', 'done')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'a', 'id')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'a', 'onclick')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'a', 'onmousedown')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'div', 'id')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'dl', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'dl', 'id')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'dt', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'h1', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'h2', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'h3', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'h4', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'h5', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'img', 'gptag')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'li', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'ol', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'ol', 'id')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'p', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'p', 'id')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'p', 'span')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'accesskey')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'action')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'alt')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'bgcolor')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'checked')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'flashvars')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'for')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'height')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'id')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'maxlength')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'method')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'name')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'onblur')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'onchange')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'aclick')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'onsubmit')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'quality')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'selected')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'src')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'target')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'type')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'value')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'width')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'span', 'wmode')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'table', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'td', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'tr', 'class')");\r
+        query.exec("Insert into InvalidXML (type, element, attribute) values ('ATTRIBUTE', 'ul', 'class')");\r
+\r
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.type = InvalidXMLRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table InvalidXML");\r
        }\r
-       // Add an invalid XML element to the table\r
-       public void addInvalidElement(String element) {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = element;\r
-               request.type = InvalidXMLRequest.Add_Invalid_Element;\r
-               Global.dbRunner.addWork(request);\r
+       // Add an item to the table\r
+       public void addAttribute(String element, String attribute) {\r
+               if (attributeExists(element,attribute))\r
+                       return;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Insert Into InvalidXML (type, element, attribute) Values('ATTRIBUTE', :element, :attribute)");\r
+               query.bindValue(":element", element);\r
+               query.bindValue(":attribute", attribute);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Insert Attribute into invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
-       // Add an invalid XML attribute to the table\r
-       public void addInvalidAttribute(String element, String attribute) {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = element;\r
-               request.string2 = attribute;\r
-               request.type = InvalidXMLRequest.Add_Invalid_Attribute;\r
-               Global.dbRunner.addWork(request);\r
+       // Add an item to the table\r
+       public void addElement(String element) {\r
+               if (elementExists(element))\r
+                       return;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Insert Into InvalidXML (type, element) Values('ELEMENT', :element)");\r
+               query.bindValue(":element", element);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Insert Element into invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
-       // Get invalid attributes for a given element\r
+       // get invalid elements\r
+       public List<String> getInvalidElements() {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               if (!query.exec("Select element from InvalidXML where type = 'ELEMENT'")) {\r
+                       logger.log(logger.MEDIUM, "getInvalidElement from invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               List<String> elements = new ArrayList<String>();\r
+               while (query.next()) {\r
+                       elements.add(query.valueString(0));\r
+               }\r
+               return elements;\r
+       }\r
+       \r
+       // get invalid elements\r
        public List<String> getInvalidAttributeElements() {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.type = InvalidXMLRequest.Get_Invalid_Attribute_Elements;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               InvalidXMLRequest req = Global.dbRunner.invalidXMLResponse.get(id).copy();\r
-               return req.responseList;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               if (!query.exec("Select distinct element from InvalidXML where type = 'ATTRIBUTE'")) {\r
+                       logger.log(logger.MEDIUM, "getInvalidElement from invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               List<String> elements = new ArrayList<String>();\r
+               while (query.next()) {\r
+                       elements.add(query.valueString(0));\r
+               }\r
+               return elements;\r
        }\r
-       // Get the list of elements which we have invalid attributes for\r
+       // get invalid attributes for a given element\r
        public ArrayList<String> getInvalidAttributes(String element) {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = element;\r
-               request.type = InvalidXMLRequest.Get_Invalid_Attributes;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               InvalidXMLRequest req = Global.dbRunner.invalidXMLResponse.get(id).copy();\r
-               return req.responseArrayList;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select attribute from InvalidXML where type = 'ATTRIBUTE' and element = :element");\r
+               query.bindValue(":element", element);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "getInvalidElement from invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               ArrayList<String> elements = new ArrayList<String>();\r
+               while (query.next()) {\r
+                       elements.add(query.valueString(0));\r
+               }\r
+               return elements;\r
        }\r
-       // Add an invalid XML attribute to the table\r
-       public List<String> getInvalidElements() {\r
-               InvalidXMLRequest request = new InvalidXMLRequest();\r
-               request.requestor_id = id;\r
-               request.type = InvalidXMLRequest.Get_Invalid_Elements;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               InvalidXMLRequest req = Global.dbRunner.invalidXMLResponse.get(id).copy();\r
-               return req.responseList;\r
+\r
+       // Determine if an element already is in the table\r
+       public boolean elementExists(String element) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select element from InvalidXML where type='ELEMENT' and element=:element");\r
+               query.bindValue(":element", element);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "elementExists in invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               if (query.next())\r
+                       return true;\r
+               else\r
+                       return false;\r
        }\r
        \r
-       \r
-\r
+       // Determine if an element already is in the table\r
+       public boolean attributeExists(String element, String attribute) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select element from InvalidXML where type='ATTRIBUTE' and element=:element and attribute=:attribute");\r
+               query.bindValue(":element", element);\r
+               query.bindValue(":attribute", attribute);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "attributeExists in invalidXML failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               if (query.next())\r
+                       return true;\r
+               else\r
+                       return false;\r
+       }\r
 }\r
index a0461e7..25bd0d5 100644 (file)
 \r
 \r
 package cx.fbn.nevernote.sql;\r
+import java.text.SimpleDateFormat;\r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
+import com.evernote.edam.type.Data;\r
 import com.evernote.edam.type.Resource;\r
+import com.evernote.edam.type.ResourceAttributes;\r
+import com.trolltech.qt.core.QByteArray;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.ResourceRequest;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 \r
 \r
-public class NoteResourceTable {\r
-       private final int id;\r
+\r
+public class NoteResourceTable  {\r
+       /**\r
+        * \r
+        */\r
+       private static final long serialVersionUID = 1L;\r
+       private final ApplicationLogger                 logger;\r
+       private final DatabaseConnection                db;     \r
        \r
        // Constructor\r
-       public NoteResourceTable(int i) {\r
-               id = i;\r
+       public NoteResourceTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\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
+                       "noteGuid varchar, updateSequenceNumber integer, dataHash varchar, "+\r
+                       "dataSize integer, dataBinary blob, "+\r
+                       "mime varchar, width integer, height integer, duration integer, "+\r
+                       "active integer, recognitionHash varchar, recognitionSize integer, " +\r
+                       "recognitionBinary varchar, attributeSourceUrl varchar, attributeTimestamp timestamp, " +\r
+                       "attributeLatitude double, attributeLongitude double, "+\r
+                       "attributeAltitude double, attributeCameraMake varchar, attributeCameraModel varchar, "\r
+                       +"attributeClientWillIndex varchar, attributeRecoType varchar, attributeFileName varchar,"+\r
+                       "attributeAttachment boolean, isDirty boolean, indexNeeded boolean)"))\r
+               logger.log(logger.HIGH, "Table NoteResource creation FAILED!!!"); \r
+        if (!query.exec("CREATE INDEX unindexed_resources on noteresources (indexneeded desc, guid);"))\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
        }\r
        // Drop the table\r
        public void dropTable() {               \r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table NoteResources");\r
        }\r
        // Reset the dirty flag\r
        public void  resetDirtyFlag(String guid) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Reset_Dirty_Flag;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update noteresources set isdirty=false where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError());\r
        }\r
        // Set if the resource should be indexed\r
        public void  setIndexNeeded(String guid, Boolean indexNeeded) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Set_Index_Needed;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = indexNeeded;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());            \r
+               query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid");\r
+               query.bindValue(":needed", indexNeeded);\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError());\r
        }\r
        // get any unindexed resource\r
        public List<String> getNextUnindexed(int limit) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Next_Unindexed;\r
-               request.int1 = limit;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseStrings;\r
+               List<String> guids = new ArrayList<String>();\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\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
+\r
+               // Get a list of the notes\r
+               String guid;\r
+               while (query.next()) {\r
+                       guid = new String();\r
+                       guid = query.valueString(0);\r
+                       guids.add(guid);\r
+               }       \r
+               return guids;   \r
        }\r
+\r
+\r
+       \r
        public void saveNoteResource(Resource r, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Save_Note_Resource;\r
-               request.resource = r.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
-//             Global.dbClientWait(id);\r
+               logger.log(logger.HIGH, "Entering DBRunner.saveNoteResources");\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
+               \r
+               check = query.prepare("Insert Into NoteResources ("\r
+                               +"guid, noteGuid, dataHash, dataSize, dataBinary, updateSequenceNumber, "\r
+                               +"mime, width, height, duration, active, recognitionHash, "                             \r
+                               +"recognitionSize, recognitionBinary, attributeSourceUrl, attributeTimestamp, "\r
+                               +"attributeLatitude, attributeLongitude, attributeAltitude, attributeCameraMake, "\r
+                               +"attributeCameraModel, "\r
+                               +"attributeClientWillIndex, attributeRecoType, attributeFileName, attributeAttachment, isDirty, "\r
+                               +"indexNeeded) Values("\r
+                               +":guid, :noteGuid, :dataHash,:dataSize, :dataBody, :updateSequenceNumber, "\r
+                               +":mime, :width, :height, :duration, :active, :recognitionHash, "                               \r
+                               +":recognitionSize, :recognitionBody, :attributeSourceUrl, :attributeTimestamp, "\r
+                               +":attributeLatitude, :attributeLongitude, :attributeAltitude, :attributeCameraMake, "\r
+                               +":attributeCameraModel, "\r
+                               +":attributeClientWillIndex, :attributeRecoType, :attributeFileName, :attributeAttachment, "\r
+                               +":isDirty, true)");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteResource SQL insert prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       \r
+                       query.bindValue(":guid", r.getGuid());\r
+                       query.bindValue(":noteGuid", r.getNoteGuid());\r
+                       if (r.getData() != null) {\r
+//                             query.bindValue(":dataHash", new QByteArray(r.getData().getBodyHash()).toHex());\r
+//                             query.bindValue(":dataHash", "");\r
+                               query.bindValue(":dataHash", byteArrayToHexString(r.getData().getBodyHash()));\r
+                               query.bindValue(":dataSize", r.getData().getSize());\r
+                               query.bindBlob(":dataBody", r.getData().getBody());\r
+                       }\r
+                       query.bindValue(":updateSequenceNumber", r.getUpdateSequenceNum());\r
+                       query.bindValue(":mime", r.getMime());\r
+                       query.bindValue(":width", new Integer(r.getWidth()));\r
+                       query.bindValue(":height", new Integer(r.getHeight()));\r
+                       query.bindValue(":duration", new Integer(r.getDuration()));\r
+                       query.bindValue(":active", r.isActive());\r
+                       if (r.getRecognition() != null) {\r
+                               query.bindValue(":recognitionHash", r.getRecognition().getBodyHash());\r
+                               query.bindValue(":recognitionSize", r.getRecognition().getSize());\r
+                               if (r.getRecognition().getBody() != null)\r
+                                       query.bindValue(":recognitionBody", new String(r.getRecognition().getBody()));\r
+                               else\r
+                                       query.bindValue(":recognitionBody", "");\r
+                       } else {\r
+                               query.bindValue(":recognitionHash", "");\r
+                               query.bindValue(":recognitionSize", 0);\r
+                               query.bindValue(":recognitionBody", "");\r
+                       }\r
+                       if (r.getAttributes() != null) {\r
+                               query.bindValue(":attributeSourceUrl", r.getAttributes().getSourceURL());\r
+                               StringBuilder ts = new StringBuilder(simple.format(r.getAttributes().getTimestamp()));\r
+                               query.bindValue(":attributeTimestamp", ts.toString());\r
+                               query.bindValue(":attributeLatitude", r.getAttributes().getLatitude());\r
+                               query.bindValue(":attributeLongitude", r.getAttributes().getLongitude());\r
+                               query.bindValue(":attributeAltitude", r.getAttributes().getAltitude());\r
+                               query.bindValue(":attributeCameraMake", r.getAttributes().getCameraMake());\r
+                               query.bindValue(":attributeCameraModel", r.getAttributes().getCameraModel());\r
+                               query.bindValue(":attributeClientWillIndex", r.getAttributes().isClientWillIndex());\r
+                               query.bindValue(":attributeRecoType", r.getAttributes().getRecoType());\r
+                               query.bindValue(":attributeFileName", r.getAttributes().getFileName());\r
+                               query.bindValue(":attributeAttachment", r.getAttributes().isAttachment());                      \r
+                       }\r
+                       query.bindValue(":isDirty", isDirty);\r
+                                               \r
+                       check = query.exec();\r
+                       if (!check) {\r
+                               logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");             \r
+                               logger.log(logger.MEDIUM, query.lastError());\r
+                       }\r
+                       \r
+                                               \r
+                       logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources");\r
        }\r
        // delete an old resource\r
        public void expungeNoteResource(String guid) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Expunge_Note_Resource;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("delete from NoteResources where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
        }\r
 \r
+       \r
        // Get a note resource from the database by it's hash value\r
        public String getNoteResourceGuidByHashHex(String noteGuid, String hash) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource_Guid_By_Hash_Hex;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(hash);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseString;      \r
+               logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
+\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               check = query.prepare("Select guid from NoteResources " +\r
+                                       "where noteGuid=:noteGuid and dataHash=:hash");\r
+               if (check)\r
+                       logger.log(logger.EXTREME, "NoteResource SQL select prepare was successful.");\r
+               else {\r
+                       logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":hash", hash);\r
+       \r
+               check = query.exec();\r
+               if (!check)      {\r
+                       logger.log(logger.MEDIUM, "dbRunner.getNoteResourceGuidByHashHex Select failed." +\r
+                                       "Note Guid:" +noteGuid+\r
+                                       "Data Body Hash:" +hash);               \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               if (!query.next()) {\r
+                       logger.log(logger.MEDIUM, "Note Resource not found.");\r
+                       return null;\r
+               }\r
+               return query.valueString(0);\r
        }\r
+\r
        // Get a note resource from the database by it's hash value\r
        public Resource getNoteResourceDataBodyByHashHex(String noteGuid, String hash) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource_Data_Body_By_Hash_Hex;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(hash);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResource;\r
-       }\r
-\r
-       // Update a note resource guid\r
-       public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Update_Note_Resource_Guid;\r
-               request.string1 = new String(oldGuid);\r
-               request.string2 = new String(newGuid);\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
-//             Global.dbClientWait(id);\r
-       }\r
-       \r
+               logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
 \r
-       // Reset update sequence number to zero\r
-       public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Reset_Update_Sequence_Number;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               check = query.prepare("Select guid, mime, from NoteResources " +\r
+                                       "where noteGuid=:noteGuid and dataHash=:hash");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":hash", hash);\r
+       \r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "NoteResource Select failed." +\r
+                                       "Note Guid:" +noteGuid+\r
+                                       "Data Body Hash:" +hash);               \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               if (!query.next()) {\r
+                       logger.log(logger.MEDIUM, "Note Resource not found.");\r
+                       return null;\r
+               }\r
+               \r
+               Resource r = new Resource();\r
+               r.setGuid(query.valueString(0));\r
+               r.setMime(query.valueString(1));\r
+               \r
+               NSqlQuery binary = new NSqlQuery(db.getConnection());\r
+               if (!binary.prepare("Select databinary from NoteResources " +\r
+                                       "where guid=:guid")) {\r
+                       logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");\r
+                       logger.log(logger.MEDIUM, binary.lastError());\r
+               }\r
+               \r
+               if (!binary.exec()) {\r
+                       logger.log(logger.MEDIUM, "NoteResources Binary Select failed." +\r
+                                       "Note Guid:" +noteGuid+\r
+                                       "Data Body Hash:" +hash);               \r
+                       logger.log(logger.MEDIUM, binary.lastError());\r
+               }\r
+               if (!binary.next()) {\r
+                       logger.log(logger.MEDIUM, "Note Resource Binary not found.");\r
+                       return null;\r
+               }\r
+               \r
+               Data d = new Data();\r
+               r.setData(d);\r
+               d.setBody(binary.valueString(0).getBytes());\r
+               logger.log(logger.HIGH, "Leaving DBRunner.getNoteResourceDataBodyByHash");\r
+               return r;\r
        }\r
+\r
        \r
        // Get a note's resourcesby Guid\r
        public Resource getNoteResource(String guid, boolean withBinary) {\r
-               if (guid == null) \r
+               if (guid == null)\r
+                       return null;\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               String queryString;\r
+               queryString = new String("Select guid, noteGuid, mime, width, height, duration, "\r
+                               +"active, updateSequenceNumber, dataHash, dataSize, "\r
+                               +"recognitionHash, recognitionSize, "\r
+                               +"attributeLatitude, attributeLongitude, attributeAltitude, "\r
+                               +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, "\r
+                               +"attributeRecoType, attributeFileName, attributeAttachment, recognitionBinary "\r
+                               +" from NoteResources where guid=:guid");\r
+\r
+               \r
+               query.prepare(queryString);\r
+               \r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
                        return null;\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = withBinary;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResource;\r
+               }\r
+               Resource r = null;\r
+               if (query.next()) {\r
+                                                                       \r
+                       r = new Resource();\r
+                       r.setGuid(query.valueString(0));        // Resource Guid\r
+                       r.setNoteGuid(query.valueString(1));   // note Guid\r
+                       r.setMime(query.valueString(2));       // Mime Type\r
+                       r.setWidth(new Short(query.valueString(3)));  // Width\r
+                       r.setHeight(new Short(query.valueString(4)));  // Height\r
+                       r.setDuration(new Short(query.valueString(5)));  // Duration\r
+                       r.setActive(new Boolean(query.valueString(6)));  // active\r
+                       r.setUpdateSequenceNum(new Integer(query.valueString(7)));  // update sequence number\r
+                       \r
+                       Data d = new Data();\r
+                       byte[] h = query.valueString(8).getBytes();    // data hash\r
+                       QByteArray hData = new QByteArray(h);\r
+                       QByteArray bData = new QByteArray(QByteArray.fromHex(hData));\r
+                       d.setBodyHash(bData.toByteArray());\r
+                       d.setSize(new Integer(query.valueString(9)));\r
+                       r.setData(d);\r
+                       \r
+                       Data rec = new Data();\r
+                       if (query.valueObject(10) != null)\r
+                               rec.setBodyHash(query.valueString(10).getBytes());   // Recognition Hash\r
+                       if (query.valueObject(11) != null)\r
+                               rec.setSize(new Integer(query.valueString(11)));\r
+                       else\r
+                               rec.setSize(0);\r
+                       r.setRecognition(rec);\r
+\r
+                       ResourceAttributes a = new ResourceAttributes();\r
+                       if (!query.valueString(12).equals(""))              // Latitude\r
+                               a.setLatitude(new Float(query.valueString(12)));\r
+                       if (!query.valueString(13).equals(""))              // Longitude\r
+                               a.setLongitude(new Float(query.valueString(13)));\r
+                       if (!query.valueString(14).equals(""))              // Altitude\r
+                               a.setAltitude(new Float(query.valueString(14)));\r
+                       a.setCameraMake(stringValue(query.valueString(15)));              // Camera Make\r
+                       a.setCameraModel(stringValue(query.valueString(16)));\r
+                       a.setClientWillIndex(booleanValue(query.valueString(17).toString(),false));  // Camera Model\r
+                       a.setRecoType(stringValue(query.valueString(18)));                 // Recognition Type\r
+                       a.setFileName(stringValue(query.valueString(19)));                  // File Name\r
+                       a.setAttachment(booleanValue(query.valueString(20).toString(),false));\r
+                       r.setAttributes(a);\r
+               \r
+                       if (withBinary) {\r
+                           \r
+                               query.prepare("Select dataBinary from NoteResources where guid=:guid");\r
+                               query.bindValue(":guid", r.getGuid());\r
+                               query.exec();\r
+                               if (query.next()) {\r
+                                       byte[] b = query.getBlob(0);\r
+                                       r.getData().setBody(b);\r
+                               }\r
+                       } \r
+               }\r
+               return r;\r
        }\r
        \r
        \r
        // Get a note's resourcesby Guid\r
        public List<Resource> getNoteResources(String noteGuid, boolean withBinary) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resources;\r
-               request.string1 = new String(noteGuid);\r
-               request.bool1 = withBinary;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResources;\r
+               if (noteGuid == null)\r
+                       return null;\r
+               List<Resource> res = new ArrayList<Resource>();\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select guid"\r
+                               +" from NoteResources where noteGuid = :noteGuid");\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {\r
+                       String guid = (query.valueString(0));\r
+                       res.add(getNoteResource(guid, withBinary));\r
+               }       \r
+               return res;\r
        }\r
-       \r
-       \r
        // Get all of a note's recognition data by the note guid\r
        public List<Resource> getNoteResourcesRecognition(String noteGuid) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resources_Recognition;\r
-               request.string1 = new String(noteGuid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResources;\r
+               if (noteGuid == null)\r
+                       return null;\r
+               boolean check;\r
+               List<Resource> res = new ArrayList<Resource>();\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Select "\r
+                               +"recognitionHash, recognitionSize, recognitionBinary "\r
+                               +" from NoteResources where noteGuid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               query.bindValue(":guid", noteGuid);\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {  \r
+                       Resource r = new Resource();            \r
+                       res.add(r);\r
+                       Data rec = new Data();\r
+                       rec.setBodyHash(query.valueString(0).getBytes());\r
+                       String x = new String(query.valueString(1));\r
+                       if (!x.equals("")) {\r
+                               rec.setSize(new Integer(x));\r
+                               rec.setBody(query.valueString(2).getBytes());\r
+                       } else\r
+                               rec.setSize(0);\r
+                       r.setRecognition(rec);\r
+               }       \r
+               return res;\r
        }\r
        \r
+       \r
        // Get a note's recognition data by it's guid.\r
        public Resource getNoteResourceRecognition(String guid) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource_Recognition;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);       \r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResource;\r
+               if (guid == null)\r
+                       return null;\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Select "\r
+                               +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
+                               +" from NoteResources where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               Resource r = null;\r
+               while (query.next()) {\r
+                                                                       \r
+                       r = new Resource();             \r
+                       Data rec = new Data();\r
+                       rec.setBodyHash(query.valueString(0).getBytes());\r
+                       String x = new String(query.valueString(1));\r
+                       if (!x.equals("")) {\r
+                               rec.setSize(new Integer(x));\r
+                               rec.setBody(query.valueString(2).getBytes());\r
+                       } else\r
+                               rec.setSize(0);\r
+                       r.setRecognition(rec);\r
+                       r.setNoteGuid(query.valueString(3));\r
+               }       \r
+               return r;\r
        }\r
+\r
        // Save Note Resource\r
        public void updateNoteResource(Resource r, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Update_Note_Resource;\r
-               request.resource = r.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("delete from NoteResources where guid=:recGuid");\r
+               query.bindValue(":recGuid", r.getGuid());\r
+               query.exec();\r
+               saveNoteResource(r, isDirty);\r
+               logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource");\r
+       }\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
+               query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");\r
+               query.bindValue(":newGuid", newGuid);\r
+               query.bindValue(":isDirty", isDirty);\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.exec();\r
+               logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
+       }\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
+               query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");\r
+               query.bindValue(":isDirty", isDirty);\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
        }\r
        \r
        // Drop the table\r
        public void reindexAll() {              \r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Reindex_All;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Update NoteResources set indexneeded=true");\r
        }\r
        // Count unindexed notes\r
        public int getResourceCount() {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Resource_Count;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseInteger;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("select count(*) from noteresources");\r
+               query.next(); \r
+               int returnValue = new Integer(query.valueString(0));\r
+               return returnValue;\r
+       }\r
+\r
+       //********************************************\r
+       //** Utility Functions\r
+       //********************************************\r
+       // Convert a byte array to a hex string\r
+       private static String byteArrayToHexString(byte data[]) {\r
+               StringBuffer buf = new StringBuffer();\r
+           for (byte element : data) {\r
+               int halfbyte = (element >>> 4) & 0x0F;\r
+               int two_halfs = 0;\r
+               do {\r
+                       if ((0 <= halfbyte) && (halfbyte <= 9))\r
+                              buf.append((char) ('0' + halfbyte));\r
+                          else\r
+                               buf.append((char) ('a' + (halfbyte - 10)));\r
+                       halfbyte = element & 0x0F;\r
+               } while(two_halfs++ < 1);\r
+           }\r
+           return buf.toString();              \r
        }\r
+\r
+       \r
+       private String stringValue(Object value) {\r
+               if (value != null && value.toString() != null) \r
+                       return value.toString();\r
+               else\r
+                       return null;\r
+       }\r
+       \r
+       private  boolean booleanValue(Object value, boolean unknown) {\r
+               if (value != null && value.toString() != null) {\r
+                       try {\r
+                               if ((Integer)value > 0)\r
+                                       return true;\r
+                               else\r
+                                       return false;\r
+                       } catch (java.lang.ClassCastException e) {\r
+                               try {\r
+                                       String stringValue = (String)value;\r
+                                       if (stringValue.equalsIgnoreCase("true"))\r
+                                               return true;\r
+                                       else \r
+                                               return false;\r
+                               } catch (java.lang.ClassCastException e1) { \r
+                                       return unknown;\r
+                               }\r
+                       }\r
+               }\r
+               else\r
+                       return unknown;\r
+       }\r
+\r
 }\r
index fa89e40..945e7e3 100644 (file)
 \r
 package cx.fbn.nevernote.sql;\r
 \r
+import java.text.DateFormat;\r
+import java.text.ParseException;\r
+import java.text.SimpleDateFormat;\r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
 import com.evernote.edam.type.Note;\r
+import com.evernote.edam.type.NoteAttributes;\r
+import com.evernote.edam.type.Resource;\r
+import com.evernote.edam.type.Tag;\r
 import com.trolltech.qt.core.QByteArray;\r
 import com.trolltech.qt.core.QDateTime;\r
+import com.trolltech.qt.core.QTextCodec;\r
 \r
 import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.NoteRequest;\r
+import cx.fbn.nevernote.evernote.EnmlConverter;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 import cx.fbn.nevernote.utilities.Pair;\r
 \r
 public class NoteTable {\r
+       private final ApplicationLogger                 logger;\r
+       public final NoteTagsTable                              noteTagsTable;\r
        public NoteResourceTable                                noteResourceTable;\r
-       public NoteTagsTable                                    noteTagsTable;\r
-       \r
+       private final DatabaseConnection                db;\r
        int id;\r
+\r
+       // Prepared Queries to improve speed\r
+       private NSqlQuery                                               getQueryWithContent;\r
+       private NSqlQuery                                               getQueryWithoutContent;\r
+       private NSqlQuery                                               getAllQueryWithoutContent;\r
        \r
        // Constructor\r
-       public NoteTable(int i) {\r
-               id = i;\r
-               noteTagsTable = new NoteTagsTable(id);\r
-               noteResourceTable = new NoteResourceTable(id);\r
+       public NoteTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
+               id = 0;\r
+               noteResourceTable = new NoteResourceTable(logger, db);\r
+               noteTagsTable = new NoteTagsTable(logger, db);\r
+               getQueryWithContent = null;\r
+               getQueryWithoutContent = null;\r
+               \r
        }\r
        // Create the table\r
        public void createTable() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               getQueryWithContent = new NSqlQuery(db.getConnection());\r
+               getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        logger.log(logger.HIGH, "Creating table Note...");\r
+        if (!query.exec("Create table Note (guid varchar primary key, " +\r
+                       "updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+\r
+                       "contentLength integer, created timestamp, updated timestamp, deleted timestamp, " \r
+                       +"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+\r
+                       "attributeLatitude double, attributeLongitude double, attributeAltitude double,"+\r
+                       "attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+\r
+                       "attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +\r
+                       "isDirty boolean)"))                    \r
+               logger.log(logger.HIGH, "Table Note creation FAILED!!!");    \r
+        if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))\r
+               logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");\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
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table Note");\r
+               noteTagsTable.dropTable();\r
+               noteResourceTable.dropTable();\r
        }\r
        // Save Note List from Evernote \r
        public void addNote(Note n, boolean isDirty) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.note = n.deepCopy();\r
-               request.bool1 = isDirty;\r
-               request.type = NoteRequest.Add_Note;\r
-               Global.dbRunner.addWork(request);\r
+               logger.log(logger.EXTREME, "Inside addNote");\r
+               if (n == null)\r
+                       return;\r
+               \r
+               SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
+\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());                    \r
+               query.prepare("Insert Into Note ("\r
+                               +"guid, updateSequenceNumber, title, content, "\r
+                               +"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "\r
+                               +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
+                               +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
+                               +"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded" \r
+                               +") Values("\r
+                               +":guid, :updateSequenceNumber, :title, :content, "\r
+                               +":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "\r
+                               +":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "\r
+                               +":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "\r
+                               +":indexNeeded, :isExpunged, :isDirty, -1, true) ");\r
+\r
+               StringBuilder created = new StringBuilder(simple.format(n.getCreated()));                       \r
+               StringBuilder updated = new StringBuilder(simple.format(n.getUpdated()));                       \r
+               StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));\r
+\r
+               EnmlConverter enml = new EnmlConverter(logger);\r
+               \r
+               query.bindValue(":guid", n.getGuid());\r
+               query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());\r
+               query.bindValue(":title", n.getTitle());\r
+               query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));\r
+               query.bindValue(":contentHash", n.getContentHash());\r
+               query.bindValue(":contentLength", n.getContentLength());\r
+               query.bindValue(":created", created.toString());\r
+               query.bindValue(":updated", updated.toString());\r
+               query.bindValue(":deleted", deleted.toString());\r
+               query.bindValue(":active", n.isActive());\r
+               query.bindValue(":notebookGuid", n.getNotebookGuid());\r
+               \r
+               if (n.getAttributes() != null) {\r
+                       created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));\r
+                       query.bindValue(":attributeSubjectDate", created.toString());\r
+                       query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());\r
+                       query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());\r
+                       query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());\r
+                       query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());\r
+                       query.bindValue(":attributeSource", n.getAttributes().getSource());\r
+                       query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL());\r
+                       query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication());\r
+               }\r
+               query.bindValue(":indexNeeded", true);\r
+               query.bindValue(":isExpunged", false);\r
+               query.bindValue(":isDirty", isDirty);\r
+\r
+               \r
+               if (!query.exec())\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               \r
+               // Save the note tags\r
+               if (n.getTagGuids() != null) {\r
+                       for (int i=0; i<n.getTagGuids().size(); i++) \r
+                               noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i));\r
+               }\r
+               logger.log(logger.EXTREME, "Leaving addNote");\r
        } \r
+       // Setup queries for get to save time later\r
+       private void prepareQueries() {\r
+               getQueryWithContent = new NSqlQuery(db.getConnection());\r
+               getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
+               getAllQueryWithoutContent = new NSqlQuery(db.getConnection());\r
+               \r
+               if (!getQueryWithContent.prepare("Select "\r
+                               +"guid, updateSequenceNumber, title, "\r
+                               +"created, updated, deleted, active, notebookGuid, "\r
+                               +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
+                               +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
+                               +"content, contentHash, contentLength"\r
+                               +" from Note where guid=:guid and isExpunged=false")) {\r
+                                       logger.log(logger.EXTREME, "Note SQL select prepare with content has failed.");\r
+                                       logger.log(logger.MEDIUM, getQueryWithContent.lastError());\r
+               }\r
+               \r
+               if (!getQueryWithoutContent.prepare("Select "\r
+                               +"guid, updateSequenceNumber, title, "\r
+                               +"created, updated, deleted, active, notebookGuid, "\r
+                               +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
+                               +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
+                               +" from Note where guid=:guid and isExpunged=false")) {\r
+                                       logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
+                                       logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
+               }\r
+               if (!getAllQueryWithoutContent.prepare("Select "\r
+                               +"guid, updateSequenceNumber, title, "\r
+                               +"created, updated, deleted, active, notebookGuid, "\r
+                               +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
+                               +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
+                               +" from Note where isExpunged = false")) {\r
+                                       logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
+                                       logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
+               }\r
+       }\r
+\r
+       // Get a note's content in raw, binary format for the sync.\r
+       public String getNoteContentBinary(String guid) {\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select content from note where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();           \r
+               query.next();\r
+               return query.valueString(0);\r
+       }\r
        // Get a note by Guid\r
        public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Note;\r
-               request.string1 = new String(noteGuid);\r
-               request.bool1 = loadContent;\r
-               request.bool2 = loadResources;\r
-               request.bool3 = loadRecognition;\r
-               request.bool4 = loadBinary;\r
-               request.bool5 = loadTags;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseNote;\r
+               if (noteGuid == null)\r
+                       return null;\r
+               if (noteGuid.trim().equals(""))\r
+                       return null;\r
+\r
+               prepareQueries();\r
+               NSqlQuery query;\r
+               if (loadContent) {\r
+                       query = getQueryWithContent;\r
+               } else {\r
+                       query = getQueryWithoutContent;\r
+               }\r
+               \r
+               query.bindValue(":guid", noteGuid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.EXTREME, "Note SQL select exec has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               if (!query.next()) {\r
+                       logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");\r
+                       logger.log(logger.EXTREME, " -> " +query.lastError().toString());\r
+                       logger.log(logger.EXTREME, " -> " +query.lastError());\r
+                       return null;\r
+               }\r
+               Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags);\r
+               n.setContent(fixCarriageReturn(n.getContent()));\r
+               return n;\r
+       }\r
+       // Get a note by Guid\r
+       public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
+               DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+//             indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
+\r
+               \r
+               Note n = new Note();\r
+               NoteAttributes na = new NoteAttributes();\r
+               n.setAttributes(na);\r
+               \r
+               n.setGuid(query.valueString(0));\r
+               n.setUpdateSequenceNum(new Integer(query.valueString(1)));\r
+               n.setTitle(query.valueString(2));\r
+\r
+               try {\r
+                       n.setCreated(indfm.parse(query.valueString(3)).getTime());\r
+                       n.setUpdated(indfm.parse(query.valueString(4)).getTime());\r
+                       n.setDeleted(indfm.parse(query.valueString(5)).getTime());\r
+               } catch (ParseException e) {\r
+                       e.printStackTrace();\r
+               }\r
+\r
+               n.setActive(query.valueBoolean(6,true));\r
+               n.setNotebookGuid(query.valueString(7));\r
+               \r
+               try {\r
+                       String attributeSubjectDate = query.valueString(8);\r
+                       if (!attributeSubjectDate.equals(""))\r
+                               na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());\r
+               } catch (ParseException e) {\r
+                       e.printStackTrace();\r
+               }\r
+               na.setLatitude(new Float(query.valueString(9)));\r
+               na.setLongitude(new Float(query.valueString(10)));\r
+               na.setAltitude(new Float(query.valueString(11)));\r
+               na.setAuthor(query.valueString(12));\r
+               na.setSource(query.valueString(13));\r
+               na.setSourceURL(query.valueString(14));\r
+               na.setSourceApplication(query.valueString(15));\r
+               \r
+               if (loadTags) {\r
+                       n.setTagGuids(noteTagsTable.getNoteTags(n.getGuid()));\r
+                       List<String> tagNames = new ArrayList<String>();\r
+                       TagTable tagTable = new TagTable(logger, db);\r
+                       for (int i=0; i<n.getTagGuids().size(); i++) {\r
+                               String currentGuid = n.getTagGuids().get(i);\r
+                               Tag tag = tagTable.getTag(currentGuid);\r
+                               tagNames.add(tag.getName());\r
+                       }\r
+                       n.setTagNames(tagNames);\r
+               }\r
+               \r
+               if (loadContent) {\r
+                                               \r
+                       QTextCodec codec = QTextCodec.codecForLocale();\r
+                       codec = QTextCodec.codecForName("UTF-8");\r
+               String unicode =  codec.fromUnicode(query.valueString(16)).toString();\r
+                       n.setContent(unicode);\r
+//                     n.setContent(query.valueString(16).toString());\r
+                       \r
+                       String contentHash = query.valueString(17);\r
+                       if (contentHash != null)\r
+                               n.setContentHash(contentHash.getBytes());\r
+                       n.setContentLength(new Integer(query.valueString(18)));\r
+               }\r
+               if (loadResources)\r
+                       n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));\r
+               if (loadRecognition) {\r
+                       if (n.getResources() == null) {\r
+                               List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());\r
+                               n.setResources(resources);\r
+                       } else {\r
+                               // We need to merge the recognition resources with the note resources retrieved earlier\r
+                               for (int i=0; i<n.getResources().size(); i++) {\r
+                                       Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());\r
+                                       n.getResources().get(i).setRecognition(r.getRecognition());\r
+                               }\r
+                       }\r
+               }\r
+               n.setContent(fixCarriageReturn(n.getContent()));\r
+               return n;\r
        }\r
        // Update a note's title\r
        public void updateNoteTitle(String guid, String title) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Title;\r
-               request.string1 = new String(guid);\r
-               request.string2 = new String(title);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note title sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":title", title);\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note title has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        // Update a note's creation date\r
        public void updateNoteCreatedDate(String guid, QDateTime date) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Creation_Date;\r
-               request.string1 = new String(guid);\r
-               request.date = new QDateTime(date);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
+               query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));\r
+               query.bindValue(":guid", guid);\r
+               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note creation date has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        // Update a note's creation date\r
        public void updateNoteAlteredDate(String guid, QDateTime date) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Altered_Date;\r
-               request.string1 = new String(guid);\r
-               request.date = new QDateTime(date);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
+               query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
+               query.bindValue(":guid", guid);\r
+               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note altered date has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        // Update a note's creation date\r
        public void updateNoteSubjectDate(String guid, QDateTime date) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Subject_Date;\r
-               request.string1 = new String(guid);\r
-               request.date = new QDateTime(date);\r
-               Global.dbRunner.addWork(request);\r
-       }\r
-       // Update a note's author\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       \r
+               query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
+               query.bindValue(":guid", guid);\r
+               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note subject date date has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       }\r
+       // Update a note's creation date\r
        public void updateNoteAuthor(String guid, String author) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Author;\r
-               request.string1 = new String(guid);\r
-               request.string2 = new String(author);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+\r
+               query.bindValue(":author", author);\r
+               query.bindValue(":guid", guid);\r
+\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note author has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
+       }\r
+       // Update a note's geo tags\r
+       public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+\r
+                               "attributeLatitude=:latitude, attributeAltitude=:altitude, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+\r
+               query.bindValue(":longitude", lon);\r
+               query.bindValue(":latitude", lat);\r
+               query.bindValue(":altitude", alt);\r
+               query.bindValue(":guid", guid);\r
+\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note geo tag has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
        }\r
        // Update a note's creation date\r
        public void updateNoteSourceUrl(String guid, String url) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Source_Url;\r
-               request.string1 = new String(guid);\r
-               request.string2 = new String(url);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note url sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
+               query.bindValue(":url", url);\r
+               query.bindValue(":guid", guid);\r
+\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note url has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
        }\r
        // Update the notebook that a note is assigned to\r
        public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Notebook;\r
-               request.string1 = new String(guid);\r
-               request.string2 = new String(notebookGuid);\r
-               request.bool1 = expungeFromRemote;\r
-               Global.dbRunner.addWork(request);\r
-       }\r
-       // Update a note's content\r
+               String currentNotebookGuid = new String("");\r
+               \r
+               \r
+               // If we are going from a synchronized notebook to a local notebook, we\r
+               // need to tell Evernote to purge the note online.  However, if this is  \r
+               // conflicting change we move it to the local notebook without deleting it \r
+               // or it would then delete the copy on the remote server.\r
+               NotebookTable notebookTable = new NotebookTable(logger, db);\r
+               DeletedTable deletedTable = new DeletedTable(logger, db);\r
+               if (expungeFromRemote) {\r
+                       if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {\r
+                               deletedTable.addDeletedItem(guid, "NOTE");\r
+                       }\r
+               }\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":notebook", notebookGuid);\r
+               query.bindValue(":guid", guid);\r
+               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note notebook has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               };\r
+       }\r
+       // Update a note's title\r
        public void updateNoteContent(String guid, String content) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Content;\r
-               request.string1 = new String(guid);\r
-               request.string2 = new String(content);\r
-               Global.dbRunner.addWork(request);\r
-       }\r
-       // Get a note's contents as a binary string (useful for unicode on sync)\r
-       public String getNoteContentBinary(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Note_Content_Binary;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseString;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set content=:content, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true " +\r
+                               " where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note content sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
+               query.bindValue(":content", content);\r
+               query.bindValue(":guid", guid);\r
+\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note content has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
+\r
+       \r
        // Check a note to see if it passes the attribute selection criteria\r
        public boolean checkAttributeSelection(Note n) {\r
                if (Global.createdSinceFilter.check(n) &&\r
                        Global.createdBeforeFilter.check(n) && \r
                        Global.changedSinceFilter.check(n) &&\r
-                       Global.changedBeforeFilter.check(n) &&\r
-                       Global.containsFilter.check(this, n))\r
+                       Global.changedBeforeFilter.check(n) )\r
                                return true;\r
                \r
                return false;\r
        }\r
        // Delete a note\r
        public void deleteNote(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Delete_Note;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Note delete failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        public void restoreNote(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Restore_Note;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Update Note set deleted='1969-12-31 19.00.00', active=true, isDirty=true where guid=:guid");\r
+//             query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Note restore failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        // Purge a note (actually delete it instead of just marking it deleted)\r
        public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Expunge_Note;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = permanentExpunge;\r
-               request.bool2 = needsSync;\r
-               Global.dbRunner.addWork(request);\r
+               \r
+               if (!permanentExpunge) {\r
+                       hideExpungedNote(guid, needsSync);\r
+                       return;\r
+               }\r
+               \r
+               \r
+        NSqlQuery note = new NSqlQuery(db.getConnection());\r
+        NSqlQuery resources = new NSqlQuery(db.getConnection());\r
+        NSqlQuery tags = new NSqlQuery(db.getConnection());\r
+        NSqlQuery words = new NSqlQuery(db.getConnection());\r
+        \r
+               note.prepare("Delete from Note where guid=:guid");\r
+               resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
+               tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
+               words.prepare("Delete from words where guid=:guid");\r
+\r
+               note.bindValue(":guid", guid);\r
+               resources.bindValue(":guid", guid);\r
+               tags.bindValue(":guid", guid);\r
+               words.bindValue(":guid", guid);\r
+       \r
+               // Start purging notes.\r
+               if (!note.exec()) {\r
+                       logger.log(logger.MEDIUM, "Purge from note failed.");\r
+                       logger.log(logger.MEDIUM, note.lastError());\r
+               }\r
+               if (!resources.exec()) {\r
+                               logger.log(logger.MEDIUM, "Purge from resources failed.");\r
+                       logger.log(logger.MEDIUM, resources.lastError());\r
+               }\r
+               if (!tags.exec()) {\r
+                       logger.log(logger.MEDIUM, "Note tags delete failed.");\r
+                       logger.log(logger.MEDIUM, tags.lastError());\r
+               }\r
+               if (!words.exec()) {\r
+                       logger.log(logger.MEDIUM, "Word delete failed.");\r
+                       logger.log(logger.MEDIUM, words.lastError());\r
+               }\r
+               if (needsSync) {\r
+                       DeletedTable deletedTable = new DeletedTable(logger, db);\r
+                       deletedTable.addDeletedItem(guid, "Note");\r
+               }\r
+\r
        }\r
+       // Purge a note (actually delete it instead of just marking it deleted)\r
+       public void hideExpungedNote(String guid, boolean needsSync) {\r
+        NSqlQuery note = new NSqlQuery(db.getConnection());\r
+        NSqlQuery resources = new NSqlQuery(db.getConnection());\r
+        NSqlQuery tags = new NSqlQuery(db.getConnection());\r
+        NSqlQuery words = new NSqlQuery(db.getConnection());\r
+        \r
+               note.prepare("Update Note set isExpunged=true where guid=:guid");\r
+               resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
+               tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
+               words.prepare("Delete from words where guid=:guid");\r
+\r
+               note.bindValue(":guid", guid);\r
+               resources.bindValue(":guid", guid);\r
+               tags.bindValue(":guid", guid);\r
+               words.bindValue(":guid", guid);\r
+\r
+               // Start purging notes.\r
+               if (!note.exec()) {\r
+                       logger.log(logger.MEDIUM, "Purge from note failed.");\r
+                       logger.log(logger.MEDIUM, note.lastError());\r
+               }\r
+               if (!resources.exec()) {\r
+                               logger.log(logger.MEDIUM, "Purge from resources failed.");\r
+                       logger.log(logger.MEDIUM, resources.lastError());\r
+               }\r
+               if (!tags.exec()) {\r
+                       logger.log(logger.MEDIUM, "Note tags delete failed.");\r
+                       logger.log(logger.MEDIUM, tags.lastError());\r
+               }\r
+               if (!words.exec()) {\r
+                       logger.log(logger.MEDIUM, "Word delete failed.");\r
+                       logger.log(logger.MEDIUM, words.lastError());\r
+               }\r
+               if (needsSync) {\r
+                       DeletedTable deletedTable = new DeletedTable(logger, db);\r
+                       deletedTable.addDeletedItem(guid, "Note");\r
+               }\r
+       }\r
+\r
+               \r
        // Purge all deleted notes;\r
        public void expungeAllDeletedNotes() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Expunge_All_Deleted_Notes;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("select guid, updateSequenceNumber from note where active = false");\r
+               while (query.next()) {\r
+                       String guid = query.valueString(0);\r
+                       Integer usn = new Integer(query.valueString(1));\r
+                       if (usn == 0)\r
+                               expungeNote(guid, true, false);\r
+                       else\r
+                               expungeNote(guid, false, true);\r
+               }\r
        }\r
        // Update the note sequence number\r
        public void updateNoteSequence(String guid, int sequence) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Sequence;\r
-               request.string1 = new String(guid);\r
-               request.int1 = sequence;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");\r
+\r
+               query.bindValue(":sequence", sequence);\r
+               query.bindValue(":guid", guid);\r
+               \r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Note sequence update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
        }\r
        // Update the note Guid\r
        public void updateNoteGuid(String oldGuid, String newGuid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note_Guid;\r
-               request.string1 = new String(oldGuid);\r
-               request.string2 = new String(newGuid);\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Update Note set guid=:newGuid where guid=:oldGuid");\r
+\r
+               query.bindValue(":newGuid", newGuid);\r
+               query.bindValue(":oldGuid", oldGuid);\r
+\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Note Guid update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
+               \r
+               query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");\r
+               query.bindValue(":newGuid", newGuid);\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               \r
+               query.prepare("Update words set guid=:newGuid where guid=:oldGuid");\r
+               query.bindValue(":newGuid", newGuid);\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Note guid update failed for Words.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");\r
+               query.bindValue(":newGuid", newGuid);\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        // Update a note\r
        public void updateNote(Note n, boolean isNew) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Note;\r
-               request.note = n.deepCopy();\r
-               request.bool1 = isNew;\r
-               Global.dbRunner.addWork(request);\r
+               boolean isExpunged = isNoteExpunged(n.getGuid());\r
+               \r
+               expungeNote(n.getGuid(), !isExpunged, false);\r
+               addNote(n, false);\r
        }\r
        // Does a note exist?\r
        public boolean exists(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Exists;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseBoolean;\r
+               if (guid == null)\r
+                       return false;\r
+               if (guid.trim().equals(""))\r
+                       return false;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select guid from note where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");\r
+               boolean retVal = query.next();\r
+               return retVal;\r
+       }\r
+       // Does a note exist?\r
+       public boolean isNoteExpunged(String guid) {\r
+               if (guid == null)\r
+                       return false;\r
+               if (guid.trim().equals(""))\r
+                       return false;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");\r
+               boolean retVal = query.next();\r
+               return retVal;\r
        }\r
        // This is a convience method to check if a tag exists & update/create based upon it\r
-       public void syncNote(Note note, boolean isDirty) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Sync_Note;\r
-               request.note = note.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+       public void syncNote(Note tag, boolean isDirty) {\r
+               if (exists(tag.getGuid()))\r
+                       updateNote(tag, isDirty);\r
+               else\r
+                       addNote(tag, isDirty);\r
        }\r
        // Get a list of notes that need to be updated\r
        public List <Note> getDirty() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Dirty;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseNotes;\r
+               String guid;\r
+               Note tempNote;\r
+               List<Note> notes = new ArrayList<Note>();\r
+               List<String> index = new ArrayList<String>();\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true)");\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
+               \r
+               // Get a list of the notes\r
+               while (query.next()) {\r
+                       guid = new String();\r
+                       guid = query.valueString(0);\r
+                       index.add(guid); \r
+               }       \r
+               \r
+               // Start getting notes\r
+               for (int i=0; i<index.size(); i++) {\r
+                       tempNote = getNote(index.get(i), true,true,false,true,true);\r
+                       notes.add(tempNote);\r
+               }\r
+               return notes;   \r
        }\r
        // Get a list of notes that need to be updated\r
-       public List <String> getUnsynchronizedGUIDs() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Unsynchronized_Guids;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseStrings;\r
-       }\r
        public boolean isNoteDirty(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Is_Note_Dirty;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseBoolean;\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
+               \r
+               boolean returnValue;\r
+               // Get a list of the notes\r
+               if (query.next()) \r
+                       returnValue = true; \r
+               else\r
+                       returnValue = false;\r
+\r
+               return returnValue;     \r
+       }\r
+       // Get a list of notes that need to be updated\r
+       public List <String> getUnsynchronizedGUIDs() {\r
+               String guid;\r
+               List<String> index = new ArrayList<String>();\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid from Note where isDirty = true");\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
+               \r
+               // Get a list of the notes\r
+               while (query.next()) {\r
+                       guid = new String();\r
+                       guid = query.valueString(0);\r
+                       index.add(guid); \r
+               }       \r
+               return index;   \r
        }\r
        // Reset the dirty bit\r
        public void  resetDirtyFlag(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Reset_Dirty_Flag;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update note set isdirty=false where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error resetting note dirty field.");\r
        }\r
        // Get all notes\r
        public List<String> getAllGuids() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_All_Guids;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseStrings;\r
+               List<String> notes = new ArrayList<String>();\r
+               \r
+               boolean check;                                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid from Note");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
+\r
+               // Get a list of the notes\r
+               while (query.next()) {\r
+                       notes.add(new String(query.valueString(0))); \r
+               }\r
+               return notes;\r
        }\r
        // Get all notes\r
        public List<Note> getAllNotes() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_All_Notes;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseNotes;\r
+               List<Note> notes = new ArrayList<Note>();\r
+               prepareQueries();\r
+               boolean check;                                  \r
+        NSqlQuery query = getAllQueryWithoutContent;\r
+               check = query.exec();\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
+               // Get a list of the notes\r
+               while (query.next()) {\r
+                       notes.add(mapNoteFromQuery(query, false, false, false, false, true));\r
+               }\r
+               return notes;\r
        }\r
        // Count unindexed notes\r
        public int getUnindexedCount() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Unindexed_Count;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseInt;\r
-       }\r
-       // Count unsynchronized count\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("select count(*) from note where indexneeded=true and isExpunged = false");\r
+               query.next(); \r
+               int returnValue = new Integer(query.valueString(0));\r
+               return returnValue;\r
+       }\r
+       // Count unsynchronized notes\r
        public int getDirtyCount() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Dirty_Count;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseInt;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("select count(*) from note where isDirty=true and isExpunged = false");\r
+               query.next(); \r
+               int returnValue = new Integer(query.valueString(0));\r
+               return returnValue;\r
        }\r
-       // Count unindexed notes\r
+       // Count notes\r
        public int getNoteCount() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Note_Count;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseInt;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("select count(*) from note where isExpunged = false");\r
+               query.next(); \r
+               int returnValue = new Integer(query.valueString(0));\r
+               return returnValue;\r
        }\r
        // Count deleted notes\r
        public int getDeletedCount() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Deleted_Count;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseInt;\r
-       }\r
-       // Reset a note's sequence count to zero.  This is useful when moving a conflicting note\r
-       public void resetSequenceNumber(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = new String(guid);\r
-               request.type = NoteRequest.Reset_Note_Sequence;\r
-               Global.dbRunner.addWork(request);\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("select count(*) from note where isExpunged = false and active = false");\r
+               if (!query.next()) \r
+                       return 0;\r
+               int returnValue = new Integer(query.valueString(0));\r
+               return returnValue;\r
+       }\r
+       // Reset a note sequence number to zero.  This is useful for moving conflicting notes\r
+       public void resetNoteSequence(String guid) {\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Update note sequence number has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        \r
+       \r
        // Update a note resource by the hash\r
        public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Resource_Guid_By_Hash;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(resGuid);\r
-               request.string3 = new String(hash);\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+/*             query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":hex", hash);\r
+               query.exec();\r
+               if (!query.next()) {\r
+                       logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash.  GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);\r
+                       return;\r
+               }\r
+               String guid = query.valueString(0);\r
+*/             \r
+               query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");\r
+               query.bindValue(":guid", resGuid);\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":hex", hash);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.EXTREME, "Note Resource Update by Hash failed");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
        }\r
-       \r
-       // Get the title color of notes\r
-       public List<Pair<String,Integer>> getNoteTitleColors() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Title_Colors;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responsePair;\r
+\r
+       // Fix CRLF problem that is on some notes\r
+       private String fixCarriageReturn(String note) {\r
+               if (note == null || !Global.enableCarriageReturnFix)\r
+                       return note;\r
+               QByteArray a0Hex = new QByteArray("a0");\r
+               String a0 = QByteArray.fromHex(a0Hex).toString();\r
+               note = note.replace("<div>"+a0+"</div>", "<div>&nbsp;</div>");\r
+               return note.replace("<div/>", "<div>&nbsp;</div>");\r
        }\r
        \r
-       // Get the title color of notes\r
-       public void setNoteTitleColor(String guid, int color) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.string1 = new String(guid);\r
-               request.int1 = color;\r
-               request.type = NoteRequest.Set_Title_Colors;\r
-               Global.dbRunner.addWork(request);       \r
-       }\r
        \r
        \r
        //********************************************************************************\r
@@ -402,96 +931,201 @@ public class NoteTable {
        //********************************************************************************\r
        // set/unset a note to be reindexed\r
        public void setIndexNeeded(String guid, Boolean flag) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Set_Index_Needed;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = flag;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Update Note set indexNeeded=:flag where guid=:guid");\r
+\r
+               if (flag)\r
+                       query.bindValue(":flag", 1);\r
+               else\r
+                       query.bindValue(":flag", 0);\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Note indexNeeded update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
        }\r
        // Set all notes to be reindexed\r
        public void reindexAllNotes() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Reindex_All_Notes;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               if (!query.exec("Update Note set indexNeeded=true")) {\r
+                       logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
        }\r
+\r
        // Get all unindexed notes\r
        public List <String> getUnindexed() {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Unindexed;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseStrings;\r
+               String guid;\r
+               List<String> index = new ArrayList<String>();\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))\r
+                       logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
+\r
+               // Get a list of the notes\r
+               while (query.next()) {\r
+                       guid = new String();\r
+                       guid = query.valueString(0);\r
+                       index.add(guid); \r
+               }       \r
+               return index;   \r
        }\r
        public List<String> getNextUnindexed(int limit) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Next_Unindexed;\r
-               request.int1 = limit;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseStrings;\r
+               List<String> guids = new ArrayList<String>();\r
+                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))\r
+                       logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
+               \r
+               // Get a list of the notes\r
+               String guid;\r
+               while (query.next()) {\r
+                       guid = new String();\r
+                       guid = query.valueString(0);\r
+                       guids.add(guid);\r
+               }       \r
+               return guids;   \r
+       }\r
+       \r
+       \r
+       //**********************************************************************************\r
+       //* Title color functions\r
+       //**********************************************************************************\r
+       // Get the title color of all notes\r
+       public List<Pair<String, Integer>> getNoteTitleColors() {\r
+               List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               if (!query.exec("Select guid,titleColor from Note where titleColor != -1"))\r
+                       logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
+\r
+               String guid;\r
+               Integer color;\r
+               \r
+               // Get a list of the notes\r
+               while (query.next()) {\r
+                       Pair<String, Integer> pair = new Pair<String,Integer>();\r
+                       guid = query.valueString(0);\r
+                       color = query.valueInteger(1);\r
+                       pair.setFirst(guid);\r
+                       pair.setSecond(color);\r
+                       returnValue.add(pair); \r
+               }       \r
+\r
+               \r
+               \r
+               return returnValue;\r
+       }\r
+       // Set a title color\r
+       // Reset the dirty bit\r
+       public void  setNoteTitleColor(String guid, int color) {\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update note set titlecolor=:color where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":color", color);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error updating title color.");\r
        }\r
+\r
        \r
        \r
-       //*********************************************************************************\r
-       //* Thumbnail Functions\r
-       //*********************************************************************************\r
+       //**********************************************************************************\r
+       //* Thumbnail functions\r
+       //**********************************************************************************\r
        // Set if a new thumbnail is needed\r
        public void setThumbnailNeeded(String guid, boolean needed) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Set_Thumbnail_Needed;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = needed;\r
-               Global.dbRunner.addWork(request);\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":needed", needed);\r
+               check = query.exec();\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());\r
+\r
        }\r
        // Is a thumbail needed for this guid?\r
        public boolean isThumbnailNeeded(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Is_Thumbail_Needed;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseBoolean;\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("select thumbnailneeded from note where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());\r
+               \r
+               boolean returnValue;\r
+               // Get a list of the notes\r
+               if (query.next()) \r
+                       returnValue = query.valueBoolean(0, false); \r
+               else\r
+                       returnValue = false;\r
+\r
+               return returnValue;     \r
        }\r
        // Set if a new thumbnail is needed\r
        public void setThumbnail(String guid, QByteArray thumbnail) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Set_Thumbnail;\r
-               request.string1 = new String(guid);\r
-               request.bytes = thumbnail;\r
-               Global.dbRunner.addWork(request);\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":thumbnail", thumbnail.toByteArray());\r
+               check = query.exec();\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());\r
+\r
        }\r
        // Set if a new thumbnail is needed\r
        public QByteArray getThumbnail(String guid) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Get_Thumbnail;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteRequest req = Global.dbRunner.noteResponse.get(id).copy();\r
-               return req.responseBytes;\r
+               \r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select thumbnail from note where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());\r
+               // Get a list of the notes\r
+               if (query.next()) \r
+                       if (query.getBlob(0) != null)\r
+                               return new QByteArray(query.getBlob(0)); \r
+               return null;\r
        }\r
        \r
-\r
        \r
        // Update a note content's hash.  This happens if a resource is edited outside of NN\r
        public void updateResourceContentHash(String guid, String oldHash, String newHash) {\r
-               NoteRequest request = new NoteRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteRequest.Update_Resource_Content_Hash;\r
-               request.string1 = new String(guid);\r
-               request.string2 = new String(oldHash);\r
-               request.string3 = new String(newHash);\r
-               Global.dbRunner.addWork(request);\r
+               Note n = getNote(guid, true, false, false, false,false);\r
+               int position = n.getContent().indexOf("<en-media");\r
+               int endPos;\r
+               for (;position>-1;) {\r
+                       endPos = n.getContent().indexOf(">", position+1);\r
+                       String oldSegment = n.getContent().substring(position,endPos);\r
+                       int hashPos = oldSegment.indexOf("hash=\"");\r
+                       int hashEnd = oldSegment.indexOf("\"", hashPos+7);\r
+                       String hash = oldSegment.substring(hashPos+6, hashEnd);\r
+                       if (hash.equalsIgnoreCase(oldHash)) {\r
+                               String newSegment = oldSegment.replace(oldHash, newHash);\r
+                               String content = n.getContent().substring(0,position) +\r
+                                                newSegment +\r
+                                                n.getContent().substring(endPos);\r
+                               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                               query.prepare("update note set isdirty=true, content=:content where guid=:guid");\r
+                               query.bindValue(":content", content);\r
+                               query.bindValue(":guid", n.getGuid());\r
+                               query.exec();\r
+                       }\r
+                       \r
+                       position = n.getContent().indexOf("<en-media", position+1);\r
+               }\r
        }\r
 }      \r
@@ -17,7 +17,7 @@
  *\r
 */\r
 \r
-package cx.fbn.nevernote.sql.runners;\r
+package cx.fbn.nevernote.sql;\r
 \r
 public class NoteTagsRecord {\r
        public String noteGuid;\r
index 8cc57a8..2b71bb4 100644 (file)
 \r
 package cx.fbn.nevernote.sql;\r
 \r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.NoteTagsRequest;\r
-import cx.fbn.nevernote.sql.runners.NoteTagsRecord;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 import cx.fbn.nevernote.utilities.Pair;\r
 \r
 public class NoteTagsTable {\r
-       private final int id;\r
+       private final ApplicationLogger                 logger;\r
+       DatabaseConnection                                              db;\r
+\r
        \r
        // Constructor\r
-       public NoteTagsTable(int i) {\r
-               id = i;\r
+       public NoteTagsTable(ApplicationLogger l,DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        // Create the NoteTag table\r
+        logger.log(logger.HIGH, "Creating table NoteTags...");\r
+        if (!query.exec("Create table NoteTags (noteGuid varchar, " +\r
+                       "tagGuid varchar, primary key(noteGuid, tagGuid))"))\r
+               logger.log(logger.HIGH, "Table NoteTags creation FAILED!!!"); \r
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);       }\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("drop table NoteTags");\r
+       }\r
        // Get a note tags by the note's Guid\r
        public List<String> getNoteTags(String noteGuid) {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Get_Note_Tags;\r
-               request.string1 = new String(noteGuid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteTagsRequest req = Global.dbRunner.noteTagsResponse.get(id).copy();\r
-               return req.responseStrings;\r
+               if (noteGuid == null)\r
+                       return null;\r
+               boolean check;\r
+               List<String> tags = new ArrayList<String>();\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.exec("Select "\r
+                               +"TagGuid from NoteTags where noteGuid = '" +noteGuid +"'");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTags SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {\r
+                       tags.add(query.valueString(0));\r
+               }       \r
+               return tags;\r
        }\r
        // Get a note tags by the note's Guid\r
        public List<NoteTagsRecord> getAllNoteTags() {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Get_All_Note_Tags;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteTagsRequest req = Global.dbRunner.noteTagsResponse.get(id).copy();\r
-               return req.responseNoteTagsRecord;\r
+               List<NoteTagsRecord> tags = new ArrayList<NoteTagsRecord>();\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               if (!query.exec("Select TagGuid, NoteGuid from NoteTags")) {\r
+                       logger.log(logger.EXTREME, "NoteTags SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {\r
+                       NoteTagsRecord record = new NoteTagsRecord();\r
+                       record.tagGuid = query.valueString(0);\r
+                       record.noteGuid = query.valueString(1);\r
+                       tags.add(record);\r
+               }       \r
+               return tags;\r
        }\r
        // Check if a note has a specific tag already\r
        public boolean checkNoteNoteTags(String noteGuid, String tagGuid) {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Check_Note_Note_Tags;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(tagGuid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteTagsRequest req = Global.dbRunner.noteTagsResponse.get(id).copy();\r
-               return req.responseBoolean;\r
+               if (noteGuid == null || tagGuid == null)\r
+                       return false;\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Select "\r
+                               +"NoteGuid, TagGuid from NoteTags where noteGuid = :noteGuid and tagGuid = :tagGuid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "checkNoteTags SQL prepare has failed.");\r
+               \r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":tagGuid", tagGuid);\r
+               query.exec();\r
+               \r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "checkNoteTags SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return false;\r
+               }\r
+               \r
+               if (query.next()) {\r
+                       return true;\r
+               }       \r
+               return false;\r
        }\r
        // Save Note Tags\r
        public void saveNoteTag(String noteGuid, String tagGuid) {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Save_Note_Tag;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(tagGuid);\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+\r
+               check = query.prepare("Insert Into NoteTags (noteGuid, tagGuid) "\r
+                               +"Values("\r
+                               +":noteGuid, :tagGuid)");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Note SQL insert prepare has failed.");\r
+       \r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":tagGuid", tagGuid);\r
+                                               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "NoteTags Table insert failed.");             \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               check = query.prepare("Update Note set isDirty=1 where guid=:guid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "RNoteTagsTable.saveNoteTag prepare has failed.");\r
+               query.bindValue(":guid", noteGuid);\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "RNoteTagsTable.saveNoteTag has failed to set note as dirty.");               \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
        // Delete a note's tags\r
        public void deleteNoteTag(String noteGuid) {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Delete_Note_Tag;\r
-               request.string1 = new String(noteGuid);\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Delete from NoteTags where noteGuid = :noteGuid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Note SQL delete prepare has failed.");\r
+       \r
+               query.bindValue(":noteGuid", noteGuid);\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "NoteTags Table delete failed.");             \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+\r
        }\r
-       // Get tag counts\r
+       // Get a note tag counts\r
        public List<Pair<String,Integer>> getTagCounts() {\r
-               NoteTagsRequest request = new NoteTagsRequest();\r
-               request.requestor_id = id;\r
-               request.type = NoteTagsRequest.Tag_Counts;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NoteTagsRequest req = Global.dbRunner.noteTagsResponse.get(id).copy();\r
-               return req.responseCounts;\r
-               \r
+               List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               if (!query.exec("select tagguid, count(noteguid) from notetags group by tagguid;")) {\r
+                       logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {\r
+                       Pair<String,Integer> newCount = new Pair<String,Integer>();\r
+                       newCount.setFirst(query.valueString(0));\r
+                       newCount.setSecond(query.valueInteger(1));\r
+                       counts.add(newCount);\r
+               }       \r
+               return counts;\r
        }\r
 }\r
index c872d53..70c884e 100644 (file)
 \r
 package cx.fbn.nevernote.sql;\r
 \r
+import java.text.DateFormat;\r
+import java.text.ParseException;\r
+import java.text.SimpleDateFormat;\r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
 import com.evernote.edam.type.Notebook;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.NotebookRequest;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 import cx.fbn.nevernote.utilities.Pair;\r
 \r
 public class NotebookTable {\r
-       int id;\r
        \r
-       public NotebookTable(int i) {\r
-               id = i;\r
-       }\r
+       private final ApplicationLogger                 logger;\r
+       DatabaseConnection                                                      db;\r
        \r
+       // Constructor\r
+       public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
+       }\r
        // Create the table\r
        public void createTable() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        logger.log(logger.HIGH, "Creating table Notebook...");\r
+        if (!query.exec("Create table Notebook (guid varchar primary key, " +\r
+                       "sequence integer, name varchar, defaultNotebook varchar, "+\r
+                       "serviceCreated timestamp, serviceUpdated timestamp, published boolean, isDirty boolean, "+\r
+                       "autoEncrypt boolean, local boolean, archived boolean)"))                               \r
+               logger.log(logger.HIGH, "Table Notebook creation FAILED!!!");   \r
+        Notebook newnote = new Notebook();\r
+        newnote.setDefaultNotebook(true);\r
+        newnote.setName("My Notebook");\r
+        newnote.setPublished(false);\r
+        newnote.setGuid("1");\r
+        addNotebook(newnote, true, false);\r
+               \r
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table Notebook");\r
        }\r
        // Save an individual notebook\r
        public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Add_Notebook;\r
-               request.notebook = tempNotebook;\r
-               request.bool1 = isDirty;\r
-               request.bool2 = local;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+               \r
+               SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Insert Into Notebook (guid, sequence, name, defaultNotebook, "\r
+                               +"serviceCreated, serviceUpdated, published, "   \r
+                               + "isDirty, autoEncrypt," \r
+                               + "local, archived) Values("\r
+                               +":guid, :sequence, :name, :defaultNotebook,  "\r
+                               +":serviceCreated, :serviceUpdated, :published, "\r
+                               +":isDirty, :autoEncrypt, "\r
+                               +":local, false)");\r
+               query.bindValue(":guid", tempNotebook.getGuid());\r
+               query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
+               query.bindValue(":name", tempNotebook.getName());\r
+               query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
+               \r
+               StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
+               StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
+               if (serviceUpdated.toString() == null)\r
+                       serviceUpdated = serviceCreated;\r
+               query.bindValue(":serviceCreated", serviceCreated.toString());\r
+               query.bindValue(":serviceUpdated", serviceCreated.toString());\r
+               query.bindValue(":published",tempNotebook.isPublished());\r
+               \r
+               if (isDirty)\r
+                       query.bindValue(":isDirty", true);\r
+               else\r
+                       query.bindValue(":isDirty", false);\r
+               query.bindValue(":autoEncrypt", false);\r
+               query.bindValue(":local", local);\r
+\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Notebook Table insert failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError().toString());\r
+               }\r
        }\r
        // Delete the notebook based on a guid\r
        public void expungeNotebook(String guid, boolean needsSync) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Expunge_Notebook;\r
-               request.string1 = guid;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+\r
+               check = query.prepare("delete from Notebook "\r
+                               +"where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Notebook SQL delete prepare has failed.");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) \r
+                       logger.log(logger.MEDIUM, "Notebook delete failed.");\r
+               \r
+               // Signal the parent that work needs to be done\r
+               if  (needsSync) {\r
+                       DeletedTable deletedTable = new DeletedTable(logger, db);\r
+                       deletedTable.addDeletedItem(guid, "Notebook");\r
+               }\r
        }\r
        // Update a notebook\r
        public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Update_Notebook;\r
-               request.notebook = tempNotebook;\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+               \r
+               SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+               \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Update Notebook set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
+                               "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
+                               "published=:published, isDirty=:isDirty where guid=:guid ");\r
+               query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
+               query.bindValue(":name", tempNotebook.getName());\r
+               query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
+\r
+               StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
+               StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
+               query.bindValue(":serviceCreated", serviceCreated.toString());\r
+               query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
+               \r
+               query.bindValue(":published", tempNotebook.isPublished());\r
+               query.bindValue(":isDirty", isDirty);\r
+               query.bindValue(":guid", tempNotebook.getGuid());\r
+               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Notebook Table update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError().toString());\r
+               }\r
        }\r
        // Load notebooks from the database\r
        public List<Notebook> getAll() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Get_All;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req = Global.dbRunner.notebookResponse.get(id).copy();\r
-               return req.responseNotebooks;\r
-               \r
+               Notebook tempNotebook;\r
+               List<Notebook> index = new ArrayList<Notebook>();\r
+               boolean check;\r
+                                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
+                               "serviceCreated, "+\r
+                               "serviceUpdated, "+\r
+                               "published, defaultNotebook from Notebook order by name");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
+               while (query.next()) {\r
+                       tempNotebook = new Notebook();\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       int sequence = new Integer(query.valueString(1)).intValue();\r
+                       tempNotebook.setUpdateSequenceNum(sequence);\r
+                       tempNotebook.setName(query.valueString(2));\r
+                       DateFormat indfm = null;\r
+                       try {\r
+                               indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+//                             indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
+                       } catch (Exception e) { }\r
+                       try {\r
+                               tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
+                               tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
+                       } catch (ParseException e) {\r
+                               e.printStackTrace();\r
+                       }\r
+                       tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
+                       tempNotebook.setDefaultNotebook(new Boolean(query.valueString(7)));\r
+                       index.add(tempNotebook); \r
+               }       \r
+               return index;\r
        }       \r
        public List<Notebook> getAllLocal() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Get_All_Local;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req = Global.dbRunner.notebookResponse.get(id).copy();\r
-               return req.responseNotebooks;\r
+               Notebook tempNotebook;\r
+               List<Notebook> index = new ArrayList<Notebook>();\r
+               boolean check;\r
+\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
+                               "serviceCreated, serviceUpdated, published from Notebook where local=true order by name");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
+               while (query.next()) {\r
+                       tempNotebook = new Notebook();\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       int sequence = new Integer(query.valueString(1)).intValue();\r
+                       tempNotebook.setUpdateSequenceNum(sequence);\r
+                       tempNotebook.setName(query.valueString(2));\r
+                       \r
+                       DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+//                     indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
+                       try {\r
+                               tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
+                               tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
+                       } catch (ParseException e) {\r
+                               e.printStackTrace();\r
+                       }\r
+                       index.add(tempNotebook); \r
+               }       \r
+               return index;\r
        }\r
        // Archive or un-archive a notebook\r
-       public void setArchived(String guid, boolean isDirty) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Set_Archived;\r
-               request.string1 = guid;\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+       public void setArchived(String guid, boolean val) {\r
+               boolean check;                  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());                                   \r
+               check = query.prepare("Update notebook set archived=:archived where guid=:guid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL archive update has failed.");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":archived", val);\r
+               query.exec();\r
        }\r
        // Load non-archived notebooks from the database\r
        public List<Notebook> getAllArchived() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Get_All_Archived;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req = Global.dbRunner.notebookResponse.get(id).copy();\r
-               return req.responseNotebooks;\r
+               Notebook tempNotebook;\r
+               List<Notebook> index = new ArrayList<Notebook>();\r
+               boolean check;\r
+                                               \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
+                               "serviceCreated, serviceUpdated, published from Notebook where archived=true order by name");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
+               while (query.next()) {\r
+                       tempNotebook = new Notebook();\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       int sequence = new Integer(query.valueString(1)).intValue();\r
+                       tempNotebook.setUpdateSequenceNum(sequence);\r
+                       tempNotebook.setName(query.valueString(2));\r
+                       \r
+                       DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+//                     indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
+                       try {\r
+                               tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
+                               tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
+                       } catch (ParseException e) {\r
+                               e.printStackTrace();\r
+                       }\r
+                       tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
+                       index.add(tempNotebook); \r
+               }       \r
+               return index;\r
        }       \r
        // Check for a local/remote notebook\r
        public boolean isNotebookLocal(String guid) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Is_Notebook_Local;\r
-               request.string1 = guid;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req =(Global.dbRunner.notebookResponse.get(id)).copy();\r
-               return req.responseBoolean;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Select local from Notebook where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               if (!query.next()) {\r
+                       return false;\r
+               }\r
+               boolean returnValue = false;\r
+               String returnVal = query.valueString(0);\r
+               if (returnVal.equals("false"))\r
+                       returnValue = false;\r
+               else\r
+                       returnValue = true;\r
+               return returnValue;\r
        }\r
        // Update a notebook sequence number\r
        public void updateNotebookSequence(String guid, int sequence) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Update_Notebook_Sequence;\r
-               request.int1 = sequence;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Update Notebook set sequence=:sequence where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":sequence", sequence);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Notebook sequence update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
        }\r
        // Update a notebook GUID number\r
        public void updateNotebookGuid(String oldGuid, String newGuid) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Update_Notebook_Guid;\r
-               request.string1 = oldGuid;\r
-               request.string2 = newGuid;\r
-               Global.dbRunner.addWork(request);\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Update Notebook set guid=:newGuid where guid=:oldGuid");\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.bindValue(":newGuid", newGuid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Notebook guid update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
+               \r
+               // Update any notes containing the notebook guid\r
+               query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.bindValue(":newGuid", newGuid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Notebook guid update for note failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               } \r
+               \r
+               // Update any watch folders with the new guid\r
+               query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.bindValue(":newGuid", newGuid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError().toString());\r
+               }               \r
        }\r
        // Get a list of notes that need to be updated\r
        public List <Notebook> getDirty() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Get_Dirty;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req = Global.dbRunner.notebookResponse.get(id).copy();\r
-               return req.responseNotebooks;\r
+               Notebook tempNotebook;\r
+               List<Notebook> index = new ArrayList<Notebook>();\r
+               boolean check;\r
+                                               \r
+               \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
+                               "serviceCreated, serviceUpdated, published from Notebook where isDirty = true and local=false");\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
+               while (query.next()) {\r
+                       tempNotebook = new Notebook();\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       int sequence = new Integer(query.valueString(1)).intValue();\r
+                       tempNotebook.setUpdateSequenceNum(sequence);\r
+                       tempNotebook.setName(query.valueString(2));\r
+                       \r
+                       DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+//                     indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
+                       try {\r
+                               tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
+                               tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
+                       } catch (ParseException e) {\r
+                               e.printStackTrace();\r
+                       }\r
+                       tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
+                       index.add(tempNotebook); \r
+               }       \r
+               return index;   \r
        }\r
-\r
        // This is a convience method to check if a tag exists & update/create based upon it\r
        public void syncNotebook(Notebook notebook, boolean isDirty) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Sync_Notebook;\r
-               request.notebook = notebook;\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               if (!exists(notebook.getGuid())) {\r
+                       addNotebook(notebook, isDirty, isDirty);\r
+                       return;\r
+               }\r
+               updateNotebook(notebook, isDirty);\r
+       }\r
+       // does a record exist?\r
+       private boolean exists(String guid) {\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Select guid from notebook where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
+               boolean retval = query.next();\r
+               return retval;\r
        }\r
        // Reset the dirty flag.  Typically done after a sync.\r
        public void  resetDirtyFlag(String guid) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Reset_Dirty;\r
-               request.string1 = guid;\r
-               Global.dbRunner.addWork(request);\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update notebook set isdirty='false' where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error resetting notebook dirty field.");\r
        }\r
+       \r
+       \r
+       \r
+\r
        // does a record exist?\r
        public String findNotebookByName(String newname) {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Find_Note_By_Name;\r
-               request.string1 = newname;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req = Global.dbRunner.notebookResponse.get(id).copy();\r
-               return req.responseString;\r
-       }\r
-       // Get Notebook counts\r
-       public List<Pair<String,Integer>> getNotebookCounts() {\r
-               NotebookRequest request = new NotebookRequest();\r
-               request.requestor_id = id;\r
-               request.type = NotebookRequest.Notebook_Counts;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               NotebookRequest req = Global.dbRunner.notebookResponse.get(id).copy();\r
-               return req.responseCounts;\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
                \r
+               query.prepare("Select guid from notebook where name=:newname");\r
+               query.bindValue(":newname", newname);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
+               String val = null;\r
+               if (query.next())\r
+                       val = query.valueString(0);\r
+               return val;\r
        }\r
+       // Get a note tag counts\r
+       public List<Pair<String,Integer>> getNotebookCounts() {\r
+               List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               if (!query.exec("select notebookGuid, count(guid) from note where active=1 group by notebookguid;")) {\r
+                       logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {\r
+                       Pair<String,Integer> newCount = new Pair<String,Integer>();\r
+                       newCount.setFirst(query.valueString(0));\r
+                       newCount.setSecond(query.valueInteger(1));\r
+                       counts.add(newCount);\r
+               }       \r
+               return counts;\r
+       }\r
+\r
 }\r
 \r
similarity index 95%
rename from src/cx/fbn/nevernote/sql/runners/REnSearch.java
rename to src/cx/fbn/nevernote/sql/REnSearch.java
index 73e2725..b45a3cb 100644 (file)
@@ -18,7 +18,7 @@
 */\r
 \r
 \r
-package cx.fbn.nevernote.sql.runners;\r
+package cx.fbn.nevernote.sql;\r
 \r
 import java.text.SimpleDateFormat;\r
 import java.util.ArrayList;\r
@@ -54,14 +54,13 @@ public class REnSearch {
        private final List<String>      todo;\r
        private final List<Tag>         tagIndex;\r
        private final ApplicationLogger logger;\r
-       private final RDatabaseConnection db;\r
+//     private final DatabaseConnection db;\r
        private boolean any;\r
        private int     minimumWordLength = 3;\r
        private int minimumRecognitionWeight = 80;\r
-       private final RDatabaseConnection conn;\r
+       private final DatabaseConnection conn;\r
        \r
-       public REnSearch(RDatabaseConnection c, ApplicationLogger l, RDatabaseConnection d, String s, List<Tag> t, int m, int r) {\r
-               db = d;\r
+       public REnSearch(DatabaseConnection c, ApplicationLogger l, String s, List<Tag> t, int m, int r) {\r
                logger = l;\r
                conn = c;\r
                tagIndex = t;\r
@@ -176,7 +175,7 @@ public class REnSearch {
        private boolean matchNotebook(String guid) {\r
                if (getNotebooks().size() == 0)\r
                        return true;\r
-               RNotebookTable bookTable = new RNotebookTable(logger, conn);\r
+               NotebookTable bookTable = new NotebookTable(logger, conn);\r
                List<Notebook> books = bookTable.getAll();\r
 \r
                String name = new String("");\r
@@ -635,7 +634,7 @@ public class REnSearch {
                        }\r
                }\r
                \r
-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(conn.getConnection());\r
                \r
                if (!query.prepare(buffer.toString()))\r
                        logger.log(logger.HIGH, "EnSearch Sql Prepare Failed:" +query.lastError());\r
@@ -653,7 +652,7 @@ public class REnSearch {
                }\r
 \r
                List<Note> guids = new ArrayList<Note>();\r
-               RNoteTable noteTable = new RNoteTable(logger, conn);  \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
index aaf5d54..fe26133 100644 (file)
 \r
 package cx.fbn.nevernote.sql;\r
 \r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
+import com.evernote.edam.type.QueryFormat;\r
 import com.evernote.edam.type.SavedSearch;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.SavedSearchRequest;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 \r
 public class SavedSearchTable {\r
-       int id;\r
+       private final ApplicationLogger                 logger;\r
+       private final DatabaseConnection                db;\r
+\r
        \r
        // Constructor\r
-       public SavedSearchTable(int i) {\r
-               id = i;\r
+       public SavedSearchTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        logger.log(logger.HIGH, "Creating table SavedSearch...");\r
+        if (!query.exec("Create table SavedSearch (guid varchar primary key, " +\r
+                       "name varchar, query varchar, format integer, sequence integer, isDirty boolean)"))\r
+               logger.log(logger.HIGH, "Table SavedSearch creation FAILED!!!"); \r
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
-       }\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table SavedSearch");\r
+       }\r
        // get all tags\r
        public List<SavedSearch> getAll() {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Get_All;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               SavedSearchRequest req = Global.dbRunner.savedSearchResponse.get(id).copy();\r
-               return req.responseSavedSearches;\r
+               SavedSearch tempSearch;\r
+               List<SavedSearch> index = new ArrayList<SavedSearch>();\r
+               boolean check;\r
+                                               \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                               \r
+               check = query.exec("Select guid, name, query, format, sequence"\r
+                               +" from SavedSearch");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in getAll().");\r
+               while (query.next()) {\r
+                       tempSearch = new SavedSearch();\r
+                       tempSearch.setGuid(query.valueString(0));\r
+                       tempSearch.setName(query.valueString(1));\r
+                       tempSearch.setQuery(query.valueString(2));\r
+                       int fmt = new Integer(query.valueString(3));\r
+                       if (fmt == 1)\r
+                               tempSearch.setFormat(QueryFormat.USER);\r
+                       else\r
+                               tempSearch.setFormat(QueryFormat.SEXP);\r
+                       int sequence = new Integer(query.valueString(4)).intValue();\r
+                       tempSearch.setUpdateSequenceNum(sequence);\r
+                       index.add(tempSearch); \r
+               }       \r
+               return index;\r
        }\r
        public SavedSearch getSavedSearch(String guid) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Get_Saved_Search;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               SavedSearchRequest req = Global.dbRunner.savedSearchResponse.get(id).copy();\r
-               return req.responseSavedSearch;\r
+               SavedSearch tempSearch = null;\r
+               boolean check;\r
+                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                               \r
+               check = query.prepare("Select guid, name, query, format, sequence"\r
+                               +" from SavedSearch where guid=:guid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL prepare has failed in getSavedSearch.");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in getSavedSearch.");\r
+               if (query.next()) {\r
+                       tempSearch = new SavedSearch();\r
+                       tempSearch.setGuid(query.valueString(0));\r
+                       tempSearch.setName(query.valueString(1));\r
+                       tempSearch.setQuery(query.valueString(2));\r
+                       int fmt = new Integer(query.valueString(3));\r
+                       if (fmt == 1)\r
+                               tempSearch.setFormat(QueryFormat.USER);\r
+                       else\r
+                               tempSearch.setFormat(QueryFormat.SEXP);\r
+                       int sequence = new Integer(query.valueString(4)).intValue();\r
+                       tempSearch.setUpdateSequenceNum(sequence);\r
+               }\r
+               return tempSearch;\r
        }\r
-       // Update a saved search\r
+       // Update a tag\r
        public void updateSavedSearch(SavedSearch search, boolean isDirty) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Update_Saved_Search;\r
-               request.savedSearch = search.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Update SavedSearch set sequence=:sequence, "+\r
+                       "name=:name, isDirty=:isDirty, query=:query, format=:format "\r
+                       +"where guid=:guid");\r
+       \r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL update prepare has failed.");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
+               query.bindValue(":sequence", search.getUpdateSequenceNum());\r
+               query.bindValue(":name", search.getName());\r
+               query.bindValue(":isDirty", isDirty);\r
+               query.bindValue(":query", search.getQuery());\r
+               if (search.getFormat() == QueryFormat.USER)\r
+                       query.bindValue(":format", 1);\r
+               else\r
+                       query.bindValue(":format", 2);\r
+               \r
+               query.bindValue(":guid", search.getGuid());\r
+               \r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Tag Table update failed.");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
        }\r
-       // Delete a saved search\r
+       // Delete a tag\r
        public void expungeSavedSearch(String guid, boolean needsSync) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Expunge_Saved_Search;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = needsSync;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+\r
+               check = query.prepare("delete from SavedSearch "\r
+                               +"where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL delete prepare has failed.");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "Saved Search delete failed.");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
+\r
+               // Add the work to the parent queue\r
+               if (needsSync) {\r
+                       DeletedTable del = new DeletedTable(logger, db);\r
+                       del.addDeletedItem(guid, "SavedSearch");\r
+               }\r
        }\r
-       // Save a saved search\r
+       // Save a tag\r
        public void addSavedSearch(SavedSearch search, boolean isDirty) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Add_Saved_Search;\r
-               request.savedSearch = search.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Insert Into SavedSearch (guid, query, sequence, format, name, isDirty)"\r
+                               +" Values(:guid, :query, :sequence, :format, :name, :isDirty)");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "Search SQL insert prepare has failed.");\r
+                       logger.log(logger.EXTREME, query.lastError().toString());\r
+               }\r
+               query.bindValue(":guid", search.getGuid());\r
+               query.bindValue(":query", search.getQuery());\r
+               query.bindValue(":sequence", search.getUpdateSequenceNum());\r
+               if (search.getFormat() == QueryFormat.USER)\r
+                       query.bindValue(":format", 1);\r
+               else\r
+                       query.bindValue(":format", 2);\r
+               query.bindValue(":name", search.getName());\r
+               query.bindValue(":isDirty", isDirty);\r
+       \r
+               check = query.exec();\r
+               if  (!check) {\r
+                       logger.log(logger.MEDIUM, "Search Table insert failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError().toString());\r
+               }\r
        }\r
-       // Update sequence number\r
+       // Update a tag sequence number\r
        public void updateSavedSearchSequence(String guid, int sequence) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Update_Saved_Search_Sequence;\r
-               request.string1 = new String(guid);\r
-               request.int1 = sequence;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+               ;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Update SavedSearch set sequence=:sequence where guid=:guid");\r
+               query.bindValue(":sequence", sequence);\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "SavedSearch sequence update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
-       // Update saved search guid\r
+       // Update a tag sequence number\r
        public void updateSavedSearchGuid(String oldGuid, String newGuid) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Get_Saved_Search;\r
-               request.string1 = new String(oldGuid);\r
-               request.string2 = new String(newGuid);\r
-               Global.dbRunner.addWork(request);\r
-       }\r
-       public void resetDirtyFlag(String guid) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Reset_Dirty_Flag;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               check = query.prepare("Update SavedSearch set guid=:newGuid where guid=:oldGuid");\r
+               query.bindValue(":newGuid", newGuid);\r
+               query.bindValue(":oldGuid", oldGuid);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "SavedSearch guid update failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
        }\r
-       // Get dirty records\r
+       // Get dirty tags\r
        public List<SavedSearch> getDirty() {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Get_Dirty;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               SavedSearchRequest req = Global.dbRunner.savedSearchResponse.get(id).copy();\r
-               return req.responseSavedSearches;\r
+               SavedSearch search;\r
+               List<SavedSearch> index = new ArrayList<SavedSearch>();\r
+               boolean check;\r
+                                               \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                               \r
+               check = query.exec("Select guid, query, sequence, name, format"\r
+                               +" from SavedSearch where isDirty = true");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "SavedSearch getDirty prepare has failed.");\r
+               while (query.next()) {\r
+                       search = new SavedSearch();\r
+                       search.setGuid(query.valueString(0));\r
+                       search.setQuery(query.valueString(1));\r
+                       int sequence = new Integer(query.valueString(2)).intValue();\r
+                       search.setUpdateSequenceNum(sequence);\r
+                       search.setName(query.valueString(3));\r
+                       int fmt = new Integer(query.valueString(4)).intValue();\r
+                       if (fmt == 1)\r
+                               search.setFormat(QueryFormat.USER);\r
+                       else\r
+                               search.setFormat(QueryFormat.SEXP);\r
+                       index.add(search); \r
+               }       \r
+               return index;\r
        }\r
        // Find a guid based upon the name\r
        public String findSavedSearchByName(String name) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Find_Saved_Search_By_Name;\r
-               request.string1 = new String(name);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               SavedSearchRequest req = Global.dbRunner.savedSearchResponse.get(id).copy();\r
-               return req.string1;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Select guid from SavedSearch where name=:name");\r
+               query.bindValue(":name", name);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in findSavedSearchByName().");\r
+               String val = null;\r
+               if (query.next())\r
+                       val = query.valueString(0);\r
+               return val;\r
        }\r
-       // given a guid, does the saved search exist\r
+       // given a guid, does the tag exist\r
        public boolean exists(String guid) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Exists;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               SavedSearchRequest req = Global.dbRunner.savedSearchResponse.get(id).copy();\r
-               return req.bool1;\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Select guid from SavedSearch where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in exists().");\r
+               boolean retval = query.next();\r
+               return retval;\r
        }\r
        // This is a convience method to check if a tag exists & update/create based upon it\r
        public void syncSavedSearch(SavedSearch search, boolean isDirty) {\r
-               SavedSearchRequest request = new SavedSearchRequest();\r
-               request.requestor_id = id;\r
-               request.type = SavedSearchRequest.Sync_Saved_Search;\r
-               request.savedSearch = search.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               if (exists(search.getGuid()))\r
+                       updateSavedSearch(search, isDirty);\r
+               else\r
+                       addSavedSearch(search, isDirty);\r
+       }\r
+       public void  resetDirtyFlag(String guid) {\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update SavedSearch set isdirty=false where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error resetting SavedSearch dirty field in resetDirtyFlag().");\r
        }\r
 }\r
index 79b469a..7812054 100644 (file)
 \r
 package cx.fbn.nevernote.sql;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.SyncRequest;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
+import cx.fbn.nevernote.utilities.ListManager;\r
 \r
 public class SyncTable {\r
-       int id;\r
+       ListManager parent;\r
+       private final ApplicationLogger                 logger;\r
+       private final DatabaseConnection                db;\r
+\r
        \r
        // Constructor\r
-       public SyncTable(int i) {\r
-               id = i;\r
+       public SyncTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               SyncRequest request = new SyncRequest();\r
-               request.requestor_id = id;\r
-               request.type = SyncRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               logger.log(logger.HIGH, "Creating table Sync...");\r
+        if (!query.exec("Create table Sync (key varchar primary key, value varchar);"))\r
+               logger.log(logger.HIGH, "Table Sync creation FAILED!!!"); \r
+        addRecord("LastSequenceDate","0");\r
+        addRecord("UpdateSequenceNumber", "0");\r
        }\r
        // Drop the table\r
        public void dropTable() {\r
-               SyncRequest request = new SyncRequest();\r
-               request.requestor_id = id;\r
-               request.type = SyncRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.exec("Drop table Sync");\r
+       }\r
+       // Add an item to the table\r
+       public void addRecord(String key, String value) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Insert Into Sync (key,  value) values (:key, :value);");\r
+               query.bindValue(":key", key);\r
+               query.bindValue(":value", value);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Add to into Sync failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       }\r
+       // Set a key field\r
+       public String getRecord(String key) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        query.prepare("Select value from Sync where key=:key");\r
+        query.bindValue(":key", key);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "getRecord from sync failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               if (query.next()) {\r
+                       return (query.valueString(0));\r
+               }\r
+               return null;\r
+       }\r
+       // Set a key field\r
+       public void setRecord(String key, String value) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        query.prepare("Update Sync set value=:value where key=:key");\r
+        query.bindValue(":key", key);\r
+        query.bindValue(":value", value);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "setRecord from sync failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               return;\r
        }\r
+\r
        // Set the last sequence date\r
        public void setLastSequenceDate(long date) {\r
-               Sy