void NoteTable::rebuildNoteListTags(qint32 lid) { // update the note list QStringList tagNames; TagTable tagTable; QSqlQuery query; query.prepare("select data from DataStore where lid=:lid and key=:key"); query.bindValue(":lid", lid); query.bindValue(":key", NOTE_TAG_LID); query.exec(); while (query.next()) { qint32 tagLid = query.value(0).toInt(); Tag t; tagTable.get(t, tagLid); tagNames.append(QString::fromStdString(t.name)); } qSort(tagNames.begin(), tagNames.end(), caseInsensitiveLessThan); QString tagCol; for (qint32 i=0; i<tagNames.size(); i++) { tagCol = tagCol + tagNames[i]; if (i<tagNames.size()-1) tagCol=tagCol+", "; } query.prepare("update NoteTable set tags=:tags where lid=:lid"); query.bindValue(":tags", tagCol); query.bindValue(":lid", lid); query.exec(); }
//*********************************************************** //* Load the completer with the list of valid tag names //*********************************************************** void TagEditorNewTag::loadCompleter() { if (completer != NULL) { delete completer; } QList<int> tagList; TagTable tagTable; QStringList tagNames; tagTable.getAll(tagList); for (qint32 i=0; i<tagList.size(); i++) { Tag t; QString guid; tagTable.getGuid(guid, tagList[i]); tagTable.get(t, guid); QString name = QString::fromStdString(t.name); qint32 tagAccount = tagTable.owningAccount(tagList[i]); if (!currentTags.contains(name) && tagAccount == account) tagNames << name; } qSort(tagNames.begin(), tagNames.end(), caseInsensitiveLessThan); completer = new QCompleter(tagNames, this); completer->setModelSorting(QCompleter::CaseInsensitivelySortedModel); completer->setCaseSensitivity(Qt::CaseInsensitive); //completer->setCompletionMode(QCompleter::InlineCompletion); setCompleter(completer); }
//* Process the list of tags for a note void ImportData::processNoteTagList(QStringList &guidList, QStringList &names) { bool atEnd = false; while(!atEnd) { if (reader->isStartElement()) { QString name = reader->name().toString().toLower(); if (name == "guid") guidList.append(textValue()); } reader->readNext(); QString endName = reader->name().toString().toLower(); if (endName == "notetags" && reader->isEndElement()) atEnd = true; } // Now look for any matches with existing ones. If they don't // exist we create a dummy record TagTable tagTable; for (qint32 i=0; i<guidList.size(); i++) { qint32 lid = tagTable.getLid(guidList[i]); if (lid == 0) { Tag newTag; newTag.guid = guidList[i].toStdString(); newTag.name = "newtag"; newTag.__isset.name = true; newTag.__isset.guid = true; tagTable.add(0, newTag, false, 0); names.push_back(QString::fromStdString(newTag.name)); } else { Tag tag; tagTable.get(tag, lid); names.push_back(QString::fromStdString(tag.name)); } } }
qint32 NoteTable::getNotesWithTag(QList<qint32> &retval, QString tag) { QSqlQuery query; TagTable tagTable; qint32 tagLid = tagTable.getLid(tag); query.prepare("Select lid data from DataStore where data=:tag and key=:key"); query.bindValue(":tag", tagLid); query.bindValue(":key", NOTE_TAG_LID); query.exec(); while(query.next()) { retval.append(query.value(0).toInt()); } return retval.size(); }
// Find the note LIDs for a tag qint32 NoteTable::findNotesByTag(QList<qint32> &values, QString data) { TagTable tagTable; qint32 tagLid = tagTable.getLid(data); QSqlQuery query; query.prepare("Select lid from DataStore where key=:key and data=:tagLid"); query.bindValue(":key", NOTE_TAG_LID); query.bindValue(":tagLid", tagLid); query.exec(); while (query.next()) { values.append(query.value(0).toInt()); } return values.size(); }
//*********************************************************** //* This will take a <tag> node and parse out the values. //*********************************************************** void ImportData::processTagNode() { Tag tag; bool tagIsDirty = false; bool atEnd = false; // Loop through until we are at the end of the </tag> while(!atEnd) { if (backup || importTags) { if (reader->isStartElement()) { QString name = reader->name().toString().toLower(); if (name == "guid") { tag.guid = textValue().toStdString(); tag.__isset.guid = true; } if (name == "name") { tag.name = textValue().toStdString(); tag.__isset.name = true; } if (name == "updatesequencenumber") { tag.updateSequenceNum = intValue(); tag.__isset.updateSequenceNum =true; } if (name == "parentguid") { tag.parentGuid = textValue().toStdString(); tag.__isset.parentGuid = true; } if (name == "Dirty") { if (booleanValue()) tagIsDirty = true; else tagIsDirty = false; } } } reader->readNext(); QString endName = reader->name().toString().toLower(); if (endName == "tag" && reader->isEndElement()) atEnd = true; } // We have a good tag, now let's save it to the database TagTable tagTable; QString name(tag.name.c_str()); // Check if we have a tag by this name already. If we // do then we treat this as an update. qint32 lid = tagTable.findByName(name,0); if (lid == 0) lid = tagTable.getLid(tag.guid); if (lid == 0) tagTable.add(0, tag,tagIsDirty, 0); else { tagTable.sync(lid, tag, 0); tagTable.setDirty(lid,tagIsDirty); } return; }
// Return a note structure given the LID bool NoteTable::get(Note ¬e, qint32 lid,bool loadResources, bool loadResourceRecognition) { QSqlQuery query; query.prepare("Select key, data from DataStore where lid=:lid"); query.bindValue(":lid", lid); query.exec(); while (query.next()) { qint32 key = query.value(0).toInt(); switch (key) { case (NOTE_GUID): note.guid = query.value(1).toString().toStdString(); note.__isset.guid = true; break; case (NOTE_UPDATE_SEQUENCE_NUMBER): note.updateSequenceNum = query.value(1).toInt(); note.__isset.updateSequenceNum = true; break; case (NOTE_ACTIVE): note.active = query.value(1).toBool(); note.__isset.active = true; break; case (NOTE_DELETED_DATE): note.active = query.value(1).toLongLong(); note.__isset.deleted = true; break; case (NOTE_ATTRIBUTE_SOURCE_URL): note.attributes.sourceURL = query.value(1).toString().toStdString(); note.__isset.attributes = true; note.attributes.__isset.sourceURL = true; break; case (NOTE_ATTRIBUTE_SOURCE_APPLICATION): note.attributes.sourceApplication = query.value(1).toString().toStdString(); note.__isset.attributes = true; note.attributes.__isset.sourceApplication = true; break; case (NOTE_CONTENT_LENGTH): note.contentLength = query.value(1).toLongLong(); note.__isset.contentLength = true; break; case (NOTE_ATTRIBUTE_LONGITUDE): note.attributes.longitude = query.value(1).toFloat(); note.__isset.attributes = true; note.attributes.__isset.longitude = true; break; case (NOTE_TITLE): note.title = query.value(1).toString().toStdString(); note.__isset.title = true; break; case (NOTE_ATTRIBUTE_SOURCE): note.attributes.source = query.value(1).toString().toStdString(); note.__isset.attributes = true; note.attributes.__isset.source = true; break; case (NOTE_ATTRIBUTE_ALTITUDE): note.attributes.altitude = query.value(1).toFloat(); note.__isset.attributes = true; note.attributes.__isset.altitude = true; break; case (NOTE_NOTEBOOK_LID): { qint32 notebookLid = query.value(1).toInt(); NotebookTable ntable; QString notebookGuid; ntable.getGuid(notebookGuid, notebookLid); note.notebookGuid = notebookGuid.toStdString(); note.__isset.notebookGuid = true; break; } case (NOTE_UPDATED_DATE): note.updated = query.value(1).toLongLong(); note.__isset.updated = true; break; case (NOTE_CREATED_DATE): note.created = query.value(1).toLongLong(); note.__isset.created = true; break; case (NOTE_ATTRIBUTE_SUBJECT_DATE): note.attributes.subjectDate = query.value(1).toLongLong(); note.__isset.attributes = true; note.attributes.__isset.subjectDate = true; break; case (NOTE_ATTRIBUTE_LATITUDE): note.attributes.latitude = query.value(1).toFloat(); note.__isset.attributes = true; note.attributes.__isset.latitude = true; break; case (NOTE_CONTENT): note.content = query.value(1).toByteArray().data(); note.__isset.content = true; break; case (NOTE_CONTENT_HASH): note.contentHash = query.value(1).toString().toStdString(); note.__isset.contentHash = true; break; case (NOTE_ATTRIBUTE_AUTHOR): note.attributes.author = query.value(1).toString().toStdString(); note.__isset.attributes = true; note.attributes.__isset.author = true; break; case (NOTE_ISDIRTY): break; case (NOTE_ATTRIBUTE_SHARE_DATE) : note.attributes.shareDate = query.value(1).toLongLong(); note.__isset.attributes = true; note.attributes.__isset.shareDate = true; break; case (NOTE_ATTRIBUTE_PLACE_NAME) : note.attributes.placeName = query.value(1).toString().toStdString(); note.__isset.attributes = true; note.attributes.__isset.placeName = true; break; case (NOTE_ATTRIBUTE_CONTENT_CLASS) : note.attributes.contentClass = query.value(1).toString().toStdString(); note.__isset.attributes = true; note.attributes.__isset.contentClass = true; break; case (NOTE_TAG_LID) : TagTable tagTable; qint32 tagLid = query.value(1).toInt(); Tag tag; tagTable.get(tag, tagLid); note.__isset.tagGuids = true; note.__isset.tagNames = true; note.tagGuids.push_back(tag.guid); note.tagNames.push_back(tag.name); break; } } ResourceTable resTable; QList<qint32> resList; if (resTable.getResourceList(resList, lid)) { for (int i=0; i<resList.size(); i++) { Resource resource; if (loadResources) { resTable.get(resource, resList[i]); } else { QString resGuid = resTable.getGuid(resList[i]); resource.guid = resGuid.toStdString(); resource.__isset.guid = true; } note.__isset.resources = true; note.resources.push_back(resource); } } /* TagScanner test; test.setData(QString::fromStdString(note.content)); QList<TagScannerRecord> retval; int k = test.findAll(retval, QString("en-note")); */ if (note.__isset.guid) return true; else return false; }
bool NoteTable::updateNoteList(qint32 lid, Note &t, bool isDirty) { NotebookTable notebookTable; qint32 notebookLid = notebookTable.getLid(t.notebookGuid); Notebook notebook; notebookTable.get(notebook, notebookLid); // Now let's update the user table QSqlQuery query; query.prepare("Delete from NoteTable where lid=:lid"); query.bindValue(":lid", lid); query.exec(); query.prepare(QString("Insert into NoteTable (lid, title, author, ") + QString("dateCreated, dateUpdated, dateSubject, dateDeleted, source, sourceUrl, sourceApplication, ") + QString("latitude, longitude, altitude, hasEncryption, hasTodo, isDirty, size, notebook, notebookLid, tags) ") + QString("Values (:lid, :title, :author, ") + QString(":dateCreated, :dateUpdated, :dateSubject, :dateDeleted, :source, :sourceUrl, :sourceApplication, ") + QString(":latitude, :longitude, :altitude, :hasEncryption, :hasTodo, :isDirty, :size, :notebook, :notebookLid, :tags) ")) ; query.bindValue(":lid", lid); if (t.__isset.title) query.bindValue(":title", QString::fromStdString(t.title)); else query.bindValue(":title", ""); if (t.__isset.attributes && t.attributes.__isset.author) query.bindValue(":author", QString::fromStdString(t.attributes.author)); else query.bindValue(":author", ""); if (t.__isset.created) query.bindValue(":dateCreated", QVariant::fromValue(t.created)); else query.bindValue(":dateCreated", 0); if (t.__isset.updated) query.bindValue(":dateUpdated", QVariant::fromValue(t.updated)); else query.bindValue(":dateUpdated", 0); if (t.__isset.attributes && t.attributes.__isset.subjectDate) query.bindValue(":dateSubject", QVariant::fromValue(t.attributes.subjectDate)); else query.bindValue(":dateSubject", 0); if (t.__isset.deleted) query.bindValue(":dateDeleted", QVariant::fromValue(t.deleted)); else query.bindValue(":dateDeleted", 0); if (t.__isset.attributes && t.attributes.__isset.source) query.bindValue(":source", QString::fromStdString(t.attributes.source)); else query.bindValue(":source", ""); if (t.__isset.attributes && t.attributes.__isset.sourceURL) query.bindValue(":sourceUrl", QString::fromStdString(t.attributes.sourceURL)); else query.bindValue(":sourceUrl", ""); if (t.__isset.attributes && t.attributes.__isset.sourceApplication) query.bindValue(":sourceApplication", QString::fromStdString(t.attributes.sourceApplication)); else query.bindValue(":sourceApplication", ""); if (t.__isset.attributes && t.attributes.__isset.latitude) query.bindValue(":latitude", QVariant::fromValue(t.attributes.latitude)); else query.bindValue(":latitude", 0); if (t.__isset.attributes && t.attributes.__isset.longitude) query.bindValue(":longitude", QVariant::fromValue(t.attributes.longitude)); else query.bindValue(":longitude", 0); if (t.__isset.attributes && t.attributes.__isset.altitude) query.bindValue(":altitude", QVariant::fromValue(t.attributes.altitude)); else query.bindValue(":altitude", 0); bool hasEncryption; if (t.content.find("<en-crypt") != string::npos) hasEncryption = true; else hasEncryption = false; query.bindValue(":hasEncryption", hasEncryption); bool hasTodo; if (t.content.find("<en-todo") != string::npos) hasTodo = true; else hasTodo = false; query.bindValue(":hasTodo", hasTodo); query.bindValue(":isDirty", isDirty); qlonglong size = t.content.length(); for (unsigned int i=0; i<t.resources.size(); i++) { size+=t.resources[i].data.size; } query.bindValue(":size", size); query.bindValue(":notebook", QString::fromStdString(notebook.name)); query.bindValue(":notebookLid", notebookLid); QString tagNames; QStringList sortedNames; for (unsigned int i=0; i<t.tagNames.size(); i++) { sortedNames.append(QString::fromStdString(t.tagNames.at(i)).toLower()); } sortedNames.sort(); TagTable tagTable; LinkedNotebookTable linkedTable; qint32 account = 0; notebookLid = notebookTable.getLid(t.notebookGuid); if (linkedTable.exists(notebookLid)) account = notebookLid; for (int i=0; i<sortedNames.size(); i++) { if (i>0) tagNames = tagNames+", "; Tag currentTag; qint32 tagLid = tagTable.findByName(sortedNames[i], account); tagTable.get(currentTag, tagLid); tagNames = tagNames + QString::fromStdString(currentTag.name); } query.bindValue(":tags", tagNames); if (!query.exec()) { QLOG_ERROR() << "Error inserting into NoteTable: " << query.lastError(); return false; } return true; }
// Add a new note to the database qint32 NoteTable::add(qint32 l, Note &t, bool isDirty) { QLOG_DEBUG() << "Adding note: " << QString::fromStdString(t.title); ResourceTable resTable; ConfigStore cs; QSqlQuery query; qint32 position; TagScanner scanner; qint32 lid = l; qint32 notebookLid; query.prepare("Insert into DataStore (lid, key, data) values (:lid, :key, :data)"); if (lid <= 0) lid = cs.incrementLidCounter(); query.bindValue(":lid", lid); query.bindValue(":key", NOTE_GUID); query.bindValue(":data", QString::fromStdString(t.guid)); query.exec(); query.bindValue(":lid", lid); query.bindValue(":key", NOTE_INDEX_NEEDED); query.bindValue(":data", true); query.exec(); if (t.__isset.title) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_TITLE); query.bindValue(":data", QString::fromStdString(t.title.c_str())); query.exec(); } if (t.__isset.content) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_CONTENT); QByteArray b; b.append(QString::fromStdString(t.content).toAscii()); query.bindValue(":data", b); query.exec(); } if (t.__isset.contentHash) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_CONTENT_HASH); query.bindValue(":data", QString::fromStdString(t.contentHash)); query.exec(); } if (t.__isset.contentLength) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_CONTENT_LENGTH); query.bindValue(":data", t.contentLength); query.exec(); } query.bindValue(":lid", lid); query.bindValue(":key", NOTE_UPDATE_SEQUENCE_NUMBER); query.bindValue(":data", t.updateSequenceNum); query.exec(); query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ISDIRTY); query.bindValue(":data", isDirty); query.exec(); if (t.__isset.created) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_CREATED_DATE); query.bindValue(":data", QVariant::fromValue(t.created)); query.exec(); } if (t.__isset.updated) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_UPDATED_DATE); query.bindValue(":data", QVariant::fromValue(t.updated)); query.exec(); } if (t.__isset.deleted) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_DELETED_DATE); query.bindValue(":data", QVariant::fromValue(t.deleted)); query.exec(); } if (t.__isset.active) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ACTIVE); query.bindValue(":data", QVariant::fromValue(t.active)); query.exec(); } if (t.__isset.notebookGuid) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_NOTEBOOK_LID); NotebookTable notebookTable; notebookLid = notebookTable.getLid(QString::fromStdString(t.notebookGuid)); // If not found, we insert one to avoid problems. We'll probably get the real data later if (notebookLid == 0) { notebookLid = cs.incrementLidCounter(); Notebook notebook; notebook.guid = t.notebookGuid; notebook.name = ""; notebook.__isset.guid = true; notebook.__isset.name = true; notebookTable.add(notebookLid, notebook, false, false); } query.bindValue(":data", notebookLid); query.exec(); } for (unsigned int i=0; t.__isset.tagGuids && i<t.tagGuids.size(); i++) { TagTable tagTable; qint32 tagLid = tagTable.getLid(t.tagGuids.at(i)); if (tagLid == 0) { // create a dummy tag to avoid later problems Tag newTag; newTag.guid = t.tagGuids.at(i); newTag.name = ""; newTag.__isset.guid = true; newTag.__isset.name = true; tagLid = cs.incrementLidCounter(); tagTable.add(tagLid, newTag, false, 0); } query.bindValue(":lid", lid); query.bindValue(":key", NOTE_TAG_LID); query.bindValue(":data", tagLid); query.exec(); } for (unsigned int i=0; t.__isset.resources && i<t.resources.size(); i++) { qint32 resLid; Resource *r; r = &t.resources[i]; resLid = resTable.getLid(t.guid,t.resources[i].guid); if (resLid == 0) resLid = cs.incrementLidCounter(); resTable.add(resLid, t.resources[i], isDirty); if (r->__isset.mime) { QString mime = QString::fromStdString(r->mime); if (!mime.startsWith("image/") && mime != "vnd.evernote.ink") { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_HAS_ATTACHMENT); query.bindValue(":data", true); query.exec(); } } } if (t.__isset.attributes) { if (t.attributes.__isset.subjectDate) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_SUBJECT_DATE); query.bindValue(":data", QVariant::fromValue(t.attributes.subjectDate)); query.exec(); } if (t.attributes.__isset.latitude) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_LATITUDE); query.bindValue(":data", QVariant::fromValue(t.attributes.latitude)); query.exec(); } if (t.attributes.__isset.latitude) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_LONGITUDE); query.bindValue(":data", QVariant::fromValue(t.attributes.longitude)); query.exec(); } if (t.attributes.__isset.altitude) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_ALTITUDE); query.bindValue(":data", QVariant::fromValue(t.attributes.altitude)); query.exec(); } if (t.attributes.__isset.author) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_AUTHOR); query.bindValue(":data", QString::fromStdString(t.attributes.author)); query.exec(); } if (t.attributes.__isset.source) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_SOURCE); query.bindValue(":data", QString::fromStdString(t.attributes.source)); query.exec(); } if (t.attributes.__isset.sourceURL) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_SOURCE_URL); query.bindValue(":data", QString::fromStdString(t.attributes.sourceURL)); query.exec(); } if (t.attributes.__isset.sourceApplication) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_SOURCE_APPLICATION); query.bindValue(":data", QString::fromStdString(t.attributes.sourceApplication)); query.exec(); } if (t.attributes.__isset.shareDate) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_SHARE_DATE); query.bindValue(":data",QVariant::fromValue(t.attributes.shareDate)); query.exec(); } if (t.attributes.__isset.placeName) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_PLACE_NAME); query.bindValue(":data", QString::fromStdString(t.attributes.placeName)); query.exec(); } if (t.attributes.__isset.contentClass) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_ATTRIBUTE_CONTENT_CLASS); query.bindValue(":data", QString::fromStdString(t.attributes.contentClass)); query.exec(); } } // No determine some attributes of the note based upon the content // This should probably happen every time a note changes? Or at least something simular: QString content; if (t.__isset.content) content = QString::fromStdString(t.content); else content = ""; position = content.indexOf("<en-crypt"); if (position > 0) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_HAS_ENCRYPT); query.bindValue(":data", true); query.exec(); } position = content.indexOf("<en-todo"); if (position > 0) { position = content.indexOf("<en-todo checked=\"true\""); if (position > 0) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_HAS_TODO_COMPLETED); query.bindValue(":data", true); query.exec(); } position = qMax(content.indexOf("<en-todo checked=\"false\""), content.indexOf("<en-todo>")); if (position > 0) { query.bindValue(":lid", lid); query.bindValue(":key", NOTE_HAS_TODO_UNCOMPLETED); query.bindValue(":data", true); query.exec(); } } updateNoteList(lid, t, isDirty); return lid; }