00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024 #include <QDebug>
00025 #include <QApplication>
00026 #include <QString>
00027 #include <QBuffer>
00028 #include <QFile>
00029 #include <QByteArray>
00030 #include <QDataStream>
00031 #include <QMessageBox>
00032 #include <QSqlRecord>
00033 #include <QSqlDatabase>
00034 #include <QSqlQuery>
00035 #include <QSqlError>
00036
00037 #include <klfguiutil.h>
00038 #include "klflib.h"
00039 #include "klflibview.h"
00040 #include "klflibdbengine.h"
00041 #include "klflibdbengine_p.h"
00042
00043
00044
00045
00088 static QByteArray image_data(const QImage& img, const char *format)
00089 {
00090 QByteArray data;
00091 QBuffer buf(&data);
00092 buf.open(QIODevice::WriteOnly);
00093 img.save(&buf, format);
00094 buf.close();
00095 return data;
00096 }
00097
00098 template<class T>
00099 static QByteArray metatype_to_data(const T& object)
00100 {
00101 QByteArray data;
00102 {
00103 QDataStream stream(&data, QIODevice::WriteOnly);
00104 stream << object;
00105
00106 }
00107 return data;
00108 }
00109
00110 template<class T>
00111 static T metatype_from_data(const QByteArray& data)
00112 {
00113 T object;
00114 QDataStream stream(data);
00115 stream >> object;
00116 return object;
00117 }
00118
00119
00120
00121
00122
00123
00124
00125
00126 KLFLibDBConnectionClassUser::KLFLibDBConnectionClassUser()
00127 {
00128 pAutoDisconnectDB = false;
00129 pDBConnectionName = QString();
00130 }
00131 KLFLibDBConnectionClassUser::~KLFLibDBConnectionClassUser()
00132 {
00133 if (pAutoDisconnectDB)
00134 QSqlDatabase::removeDatabase(pDBConnectionName);
00135 }
00136
00137
00138
00139
00140
00141
00142
00143 KLFLibDBEngine * KLFLibDBEngine::openUrl(const QUrl& givenurl, QObject *parent)
00144 {
00145 bool accessshared = false;
00146
00147 QUrl url = givenurl;
00148
00149 if (url.hasQueryItem("klfDefaultSubResource")) {
00150 QString defaultsubres = url.queryItemValue("klfDefaultSubResource");
00151
00152 url.removeAllQueryItems("klfDefaultSubResource");
00153 url.addQueryItem("klfDefaultSubResource", defaultsubres.toLower());
00154 }
00155
00156 QSqlDatabase db;
00157 if (url.scheme() == "klf+sqlite") {
00158 QUrl dburl = url;
00159 dburl.removeAllQueryItems("klfDefaultSubResource");
00160 dburl.removeAllQueryItems("klfReadOnly");
00161 accessshared = false;
00162 QString dburlstr = dburl.toString();
00163 QString path = klfUrlLocalFilePath(dburl);
00164 if (dburlstr.isEmpty() || !QFile::exists(path)) {
00165 QMessageBox::critical(0, tr("Error"),
00166 tr("Database file <b>%1</b> does not exist.").arg(path));
00167 return NULL;
00168 }
00169 db = QSqlDatabase::database(dburlstr);
00170 if ( ! db.isValid() ) {
00171
00172 db = QSqlDatabase::addDatabase("QSQLITE", dburl.toString());
00173 db.setDatabaseName(path);
00174 if ( !db.open() || db.lastError().isValid() ) {
00175 QMessageBox::critical(0, tr("Error"),
00176 tr("Unable to open library file \"%1\" (engine: \"%2\").\nError: %3")
00177 .arg(path, db.driverName(), db.lastError().text()), QMessageBox::Ok);
00178 return NULL;
00179 }
00180 }
00181 } else {
00182 qWarning("KLFLibDBEngine::openUrl: bad url scheme in URL\n\t%s",
00183 qPrintable(url.toString()));
00184 return NULL;
00185 }
00186
00187 return new KLFLibDBEngine(db, true , url, accessshared, parent);
00188 }
00189
00190
00191 KLFLibDBEngine * KLFLibDBEngine::createSqlite(const QString& fileName, const QString& sresname,
00192 const QString& srestitle, QObject *parent)
00193 {
00194 KLF_DEBUG_BLOCK(KLF_FUNC_NAME) ;
00195 klfDbgSt("fileName="<<fileName<<", sresname="<<sresname<<", srestitle="<<srestitle) ;
00196
00197 QString subresname = sresname;
00198 QString subrestitle = srestitle;
00199
00200 bool r;
00201
00202 if (QFile::exists(fileName)) {
00203
00204
00205 return NULL;
00206 }
00207 QUrl url = QUrl::fromLocalFile(fileName);
00208 url.setScheme("klf+sqlite");
00209
00210 QString dburlstr = url.toString();
00211 QSqlDatabase db = QSqlDatabase::database(dburlstr);
00212 if (!db.isValid()) {
00213 db = QSqlDatabase::addDatabase("QSQLITE", dburlstr);
00214 QString path = klfUrlLocalFilePath(url);
00215 db.setDatabaseName(path);
00216 r = db.open();
00217 if ( !r || db.lastError().isValid() ) {
00218 QMessageBox::critical(0, tr("Error"),
00219 tr("Unable to create library file %1 (SQLITE database):\n"
00220 "%2")
00221 .arg(path, db.lastError().text()), QMessageBox::Ok);
00222 return NULL;
00223 }
00224 }
00225
00226 if (subresname.isEmpty()) {
00227 subresname = "table1";
00228 if (subrestitle.isEmpty()) {
00229 subrestitle = "Table 1";
00230 }
00231 }
00232 if (subrestitle.isEmpty())
00233 subrestitle = subresname;
00234
00235 url.addQueryItem("klfDefaultSubResource", subresname);
00236 if (subresname.contains("\"")) {
00237
00238 qWarning()<<KLF_FUNC_NAME<<"\" character is not allowed in SQLITE database tables (<-> library sub-resources).";
00239 return NULL;
00240 }
00241
00242 r = initFreshDatabase(db);
00243 if ( r ) {
00244
00245 r = createFreshDataTable(db, subresname);
00246 }
00247 if ( !r ) {
00248 QMessageBox::critical(0, tr("Error"),
00249 tr("Unable to initialize the SQLITE database file %1!").arg(url.path()));
00250 return NULL;
00251 }
00252
00253 KLFLibDBEngine *res = new KLFLibDBEngine(db, true , url, false, parent);
00254
00255 r = res->setSubResourceProperty(subresname, SubResPropTitle, subrestitle);
00256 if ( ! r ) {
00257 qWarning()<<"Failed to create table named "<<subresname<<"!";
00258 delete res;
00259 return NULL;
00260 }
00261
00262 return res;
00263 }
00264
00265
00266 KLFLibDBEngine::KLFLibDBEngine(const QSqlDatabase& db, bool autodisconnect,
00267 const QUrl& url, bool accessshared, QObject *parent)
00268 : KLFLibResourceEngine(url, FeatureReadOnly|FeatureLocked|FeatureSubResources
00269 |FeatureSubResourceProps, parent)
00270 {
00271 pAutoDisconnectDB = autodisconnect;
00272
00273
00274 KLFPropertizedObject::setProperty(PropAccessShared, accessshared);
00275
00276 setDatabase(db);
00277 readResourceProperty(-1);
00278
00279 readDbMetaInfo();
00280 QStringList subres = subResourceList();
00281 int k;
00282 for (k = 0; k < subres.size(); ++k)
00283 readAvailColumns(subres[k]);
00284
00285 KLFLibDBEnginePropertyChangeNotifier *dbNotifier = dbPropertyNotifierInstance(db.connectionName());
00286 connect(dbNotifier, SIGNAL(resourcePropertyChanged(int)),
00287 this, SLOT(resourcePropertyUpdate(int)));
00288 connect(dbNotifier, SIGNAL(subResourcePropertyChanged(const QString&, int)),
00289 this, SLOT(subResourcePropertyUpdate(const QString&, int)));
00290
00291 dbNotifier->ref();
00292 }
00293
00294 KLFLibDBEngine::~KLFLibDBEngine()
00295 {
00296 pDBConnectionName = pDB.connectionName();
00297 KLFLibDBEnginePropertyChangeNotifier *dbNotifier = dbPropertyNotifierInstance(pDBConnectionName);
00298 if (dbNotifier->deRef() && pAutoDisconnectDB) {
00299 pDB.close();
00300 pAutoDisconnectDB = true;
00301 } else {
00302 pAutoDisconnectDB = false;
00303 }
00304 }
00305
00306
00307 bool KLFLibDBEngine::tableExists(const QString& subResource) const
00308 {
00309 return pDB.tables().contains(dataTableName(subResource), Qt::CaseInsensitive);
00310 }
00311
00312
00313 QString KLFLibDBEngine::dataTableName(const QString& subResource)
00314 {
00315 return "t_"+subResource.toLower();
00316 }
00317
00318 QString KLFLibDBEngine::quotedDataTableName(const QString& subResource)
00319 {
00320 QString dtname = dataTableName(subResource);
00321 dtname.replace('"', "\"\"");
00322 return '"' + dtname + '"';
00323 }
00324
00325
00326 uint KLFLibDBEngine::compareUrlTo(const QUrl& other, uint interestFlags) const
00327 {
00328
00329 interestFlags = interestFlags & (KlfUrlCompareBaseEqual);
00330
00331 interestFlags |= klfUrlCompareFlagIgnoreQueryItemValueCase;
00332
00333 return klfUrlCompare(url(), other, interestFlags);
00334 }
00335
00336 bool KLFLibDBEngine::canModifyData(const QString& subResource, ModifyType mt) const
00337 {
00338 if ( !KLFLibResourceEngine::canModifyData(subResource, mt) )
00339 return false;
00340 if ( !validDatabase() )
00341 return false;
00342
00345 return true;
00346 }
00347
00348 bool KLFLibDBEngine::canModifyProp(int propId) const
00349 {
00350 return KLFLibResourceEngine::canModifyProp(propId);
00351 }
00352 bool KLFLibDBEngine::canRegisterProperty(const QString& ) const
00353 {
00354
00355 return canModifyProp(-1);
00356 }
00357
00358 bool KLFLibDBEngine::validDatabase() const
00359 {
00360 return pDB.isOpen();
00361 }
00362
00363 void KLFLibDBEngine::setDatabase(const QSqlDatabase& db)
00364 {
00365 pDB = db;
00366 }
00367
00368
00369 bool KLFLibDBEngine::saveResourceProperty(int propId, const QVariant& value)
00370 {
00371 KLF_DEBUG_TIME_BLOCK(KLF_FUNC_NAME) ;
00372
00373 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00374 return false; ) ;
00375
00376 QString propName = propertyNameForId(propId);
00377 if ( propName.isEmpty() )
00378 return false;
00379
00380 if ( KLFPropertizedObject::property(propId) == value )
00381 return true;
00382
00383 {
00384 QSqlQuery q = QSqlQuery(pDB);
00385 q.prepare("DELETE FROM klf_properties WHERE name = ?");
00386 q.addBindValue(propName);
00387 bool r = q.exec();
00388 if ( !r || q.lastError().isValid() ) {
00389 qWarning()<<"KLFLibDBEngine::setRes.Property("<<propId<<","<<value<<"): can't DELETE!\n\t"
00390 <<q.lastError().text()<<"\n\tSQL="<<q.lastQuery();
00391 return false;
00392 }
00393 }
00394 {
00395 QSqlQuery q = QSqlQuery(pDB);
00396 q.prepare("INSERT INTO klf_properties (name,value) VALUES (?,?)");
00397 q.bindValue(0, propName);
00398 q.bindValue(1, convertVariantToDBData(value));
00399 if ( ! q.exec() || q.lastError().isValid() ) {
00400 qWarning()<<"KLFLibDBEngine::setRes.Property("<<propId<<","<<value<<"): can't INSERT!\n\t"
00401 <<q.lastError().text()<<"\n\tSQL="<<q.lastQuery();
00402 return false;
00403 }
00404 }
00405 KLFPropertizedObject::setProperty(propId, value);
00406 dbPropertyNotifierInstance(pDB.connectionName())->notifyResourcePropertyChanged(propId);
00407
00408
00409 return true;
00410 }
00411
00412 void KLFLibDBEngine::resourcePropertyUpdate(int propId)
00413 {
00414 readResourceProperty(propId);
00415 }
00416
00417 void KLFLibDBEngine::subResourcePropertyUpdate(const QString& subResource, int propId)
00418 {
00419 emit subResourcePropertyChanged(subResource, propId);
00420 }
00421
00422 void KLFLibDBEngine::readResourceProperty(int propId)
00423 {
00424 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00425 return ) ;
00426
00427 QString sqlstr = "SELECT name,value FROM klf_properties";
00428 QString propName;
00429 if (propId >= 0) {
00430 if (!propertyIdRegistered(propId)) {
00431 qWarning()<<"Can't read un-registered resource property "<<propId<<" !";
00432 return;
00433 }
00434 sqlstr += " WHERE name = ?";
00435 propName = propertyNameForId(propId);
00436 }
00437
00438 QSqlQuery q = QSqlQuery(pDB);
00439 q.prepare("SELECT name,value FROM klf_properties");
00440 q.exec();
00441 while (q.next()) {
00442 QString propname = q.value(0).toString();
00443 int propId = propertyIdForName(propname);
00444 if (!propertyNameRegistered(propname)) {
00445 if (!canRegisterProperty(propname))
00446 continue;
00447 KLFPropertizedObject::registerProperty(propname);
00448 }
00449 QVariant propvalue = convertVariantFromDBData(q.value(1));
00450 klfDbg( "Setting property `"<<propname<<"' (id #"<<propId<<") to "<<propvalue<<"" ) ;
00451 KLFPropertizedObject::setProperty(propId, propvalue);
00452 emit resourcePropertyChanged(propId);
00453 }
00454 }
00455
00456 void KLFLibDBEngine::readDbMetaInfo()
00457 {
00458 QSqlQuery q = QSqlQuery(pDB);
00459 q.prepare("SELECT name,value FROM klf_dbmetainfo");
00460 bool r = q.exec();
00461 if ( !r || q.lastError().isValid() ) {
00462 qWarning()<<KLF_FUNC_NAME<<": unable to fetch DB meta-info: "<<q.lastError().text();
00463 return;
00464 }
00465 while (q.next()) {
00466 QString name = q.value(0).toString();
00467 QString version = q.value(1).toString();
00468 if (name == QLatin1String("klf_dbversion")) {
00469 pDBVersion = version.toInt();
00470 }
00471 }
00472 }
00473 void KLFLibDBEngine::readAvailColumns(const QString& subResource)
00474 {
00475 QSqlRecord rec = pDB.record(dataTableName(subResource));
00476 QStringList columns;
00477 int k;
00478 for (k = 0; k < rec.count(); ++k)
00479 columns << rec.fieldName(k);
00480
00481 pDBAvailColumns[subResource] = columns;
00482 }
00483
00484
00485
00486
00487 QStringList KLFLibDBEngine::columnNameList(const QString& subResource, const QList<int>& entryPropList,
00488 bool wantIdFirst)
00489 {
00490 QStringList cols;
00491 KLFLibEntry dummy;
00492 int k;
00493 for (k = 0; k < entryPropList.size(); ++k) {
00494 QString col = dummy.propertyNameForId(entryPropList[k]);
00495 if (pDBAvailColumns[subResource].contains(col))
00496 cols << col;
00497 else if (entryPropList[k] == KLFLibEntry::PreviewSize)
00498 cols << "Preview";
00499 }
00500 if (entryPropList.size() == 0) {
00501 cols << "*";
00502 }
00503 if (wantIdFirst && (!cols.size() || cols[0] != "id") )
00504 cols.prepend("id");
00505
00506 return cols;
00507 }
00508
00509 QStringList KLFLibDBEngine::detectEntryColumns(const QSqlQuery& q)
00510 {
00511 const QSqlRecord rec = q.record();
00512 QStringList cols;
00513 KLFLibEntry dummy;
00514 int k;
00515 for (k = 0; k < rec.count(); ++k) {
00516 QString propName = rec.fieldName(k);
00517 int propId = dummy.propertyIdForName(propName);
00518 if (propId < 0 && propName != "id") {
00519 klfDbg( "Registering property "<<propName ) ;
00520 dummy.setEntryProperty(propName, QVariant());
00521 }
00522 cols << propName;
00523 }
00524 return cols;
00525 }
00526
00527 KLFLibEntry KLFLibDBEngine::readEntry(const QSqlQuery& q, const QStringList& cols)
00528 {
00529
00530 KLFLibEntry entry;
00531 int k;
00532 for (k = 0; k < cols.size(); ++k) {
00533 QVariant v = q.value(k);
00534 if (cols[k] == "id")
00535 continue;
00536 int propId = entry.propertyIdForName(cols[k]);
00537 QVariant value = dbReadEntryPropertyValue(q.value(k), propId);
00538 entry.setEntryProperty(cols[k], value);
00539 }
00540
00541
00542
00543 const QImage& preview = entry.property(KLFLibEntry::Preview).value<QImage>();
00544 if (!preview.isNull()) {
00545 const QSize& s = entry.property(KLFLibEntry::PreviewSize).toSize();
00546 if (!s.isValid() || s != preview.size()) {
00547 klfDbg( ": missing or incorrect preview size set to "<<entry.preview().size() ) ;
00548 entry.setPreviewSize(entry.preview().size());
00549 }
00550 }
00551 return entry;
00552 }
00553
00554
00555 QList<KLFLib::entryId> KLFLibDBEngine::allIds(const QString& subResource)
00556 {
00557 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00558 return QList<KLFLib::entryId>() ) ;
00559
00560 QSqlQuery q = QSqlQuery(pDB);
00561 q.prepare(QString("SELECT id FROM %1").arg(quotedDataTableName(subResource)));
00562 q.setForwardOnly(true);
00563 bool r = q.exec();
00564 if ( ! r || q.lastError().isValid() ) {
00565 qWarning("KLFLibDBEngine::allIds: Error fetching IDs!\n"
00566 "SQL Error: %s", qPrintable(q.lastError().text()));
00567 return QList<KLFLib::entryId>();
00568 }
00569 QList<KLFLib::entryId> idlist;
00570 while (q.next()) {
00571 idlist << q.value(0).toInt();
00572 }
00573 return idlist;
00574 }
00575 bool KLFLibDBEngine::hasEntry(const QString& subResource, entryId id)
00576 {
00577 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00578 return false ) ;
00579
00580 QSqlQuery q = QSqlQuery(pDB);
00581 q.prepare(QString("SELECT id FROM %1 WHERE id = ?").arg(quotedDataTableName(subResource)));
00582 q.addBindValue(id);
00583 bool r = q.exec();
00584 if ( ! r || q.lastError().isValid() ) {
00585 qWarning("KLFLibDBEngine::hasEntry: Error!\n"
00586 "SQL Error: %s", qPrintable(q.lastError().text()));
00587 return false;
00588 }
00589 if (q.next())
00590 return true;
00591 return false;
00592 }
00593 QList<KLFLibResourceEngine::KLFLibEntryWithId>
00594 KLFLibDBEngine::entries(const QString& subResource, const QList<KLFLib::entryId>& idList,
00595 const QList<int>& wantedEntryProperties)
00596 {
00597 KLF_DEBUG_BLOCK(KLF_FUNC_NAME); klfDbg( "\t: subResource="<<subResource<<"; idlist="<<idList ) ;
00598 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00599 return QList<KLFLibEntryWithId>() ) ;
00600 if (idList.isEmpty())
00601 return QList<KLFLibEntryWithId>();
00602
00603 QStringList cols = columnNameList(subResource, wantedEntryProperties, true);
00604 if (cols.contains("*")) {
00605 cols = QStringList();
00606 cols << "id"
00607 << pDBAvailColumns[subResource];
00608 }
00609
00610 QSqlQuery q = QSqlQuery(pDB);
00611 q.prepare(QString("SELECT %1 FROM %2 WHERE id = ?").arg(cols.join(","),
00612 quotedDataTableName(subResource)));
00613
00614 KLFProgressReporter progr(0, idList.size(), this);
00615 if (!thisOperationProgressBlocked())
00616 emit operationStartReportingProgress(&progr, tr("Fetching items from library database ..."));
00617
00618 QList<KLFLibEntryWithId> eList;
00619
00620 int k;
00621 for (k = 0; k < idList.size(); ++k) {
00622 if (k % 10 == 0)
00623 progr.doReportProgress(k);
00624
00625 q.bindValue(0, idList[k]);
00626 bool r = q.exec();
00627 if ( !r || q.lastError().isValid() ) {
00628 klfDbg( " SQL Error, sql="<<q.lastQuery()<<"; boundvalues="<<q.boundValues() ) ;
00629 qWarning("KLFLibDBEngine::entries: Error\n"
00630 "SQL Error (?): %s", qPrintable(q.lastError().text()));
00631 continue;
00632 }
00633 if ( !q.next() ) {
00634 klfDbg( ": id="<<idList[k]<<" does not exist in DB." ) ;
00635 KLFLibEntryWithId e; e.entry = KLFLibEntry(); e.id = -1;
00636 eList << e;
00637 continue;
00638 }
00639
00640 KLFLibEntryWithId e;
00641 e.entry = readEntry(q, cols);
00642 e.id = q.value(0).toInt();
00643 eList << e;
00644 }
00645
00646 progr.doReportProgress(idList.size());
00647
00648 return eList;
00649 }
00650
00651
00652 static QString escape_sql_data_string(QString s)
00653 {
00654 s.replace("'", "''");
00655 return "'" + s + "'";
00656 }
00657
00658
00659 static QString make_like_condition(QString field, QString val, bool wildbefore, bool wildafter, bool casesensitive)
00660 {
00661 if (casesensitive) {
00662 QString globval = val;
00663 if (wildbefore)
00664 globval.prepend("*");
00665 if (wildafter)
00666 globval.append("*");
00667
00668 return field+" GLOB "+escape_sql_data_string(globval)+" AND "
00669 + make_like_condition(field, val, wildbefore, wildafter, false);
00670 } else {
00671
00672 val.replace("%", "\\%");
00673 val.replace("_", "\\_");
00674 val.replace("\\", "\\\\");
00675 val = escape_sql_data_string(val);
00676 if (wildbefore)
00677 val.prepend("%");
00678 if (wildafter)
00679 val.append("%");
00680 return field+" LIKE '"+val+"' ESCAPE '\\' ";
00681 }
00682 }
00683
00684
00685
00686 static QString make_sql_condition(const KLFLib::EntryMatchCondition m, QVariantList *placeholders,
00687 bool *haspostsqlcondition, KLFLib::EntryMatchCondition *postsqlcondition)
00688 {
00691 *haspostsqlcondition = false;
00692
00693 if (m.type() == KLFLib::EntryMatchCondition::MatchAllType) {
00694 return "1";
00695 }
00696 if (m.type() == KLFLib::EntryMatchCondition::PropertyMatchType) {
00697 QString condition;
00698 KLFLib::PropertyMatch pm = m.propertyMatch();
00699 KLFLibEntry dummyentry;
00700 QString field = dummyentry.propertyNameForId(pm.propertyId());
00701 condition += "(";
00702 uint f = pm.matchFlags();
00703 switch ( f & 0xFF ) {
00704 case Qt::MatchExactly:
00705 condition += field+" = ?";
00706 if ((f & Qt::CaseSensitive) == 0)
00707 condition += " COLLATE NOCASE";
00708 if (f & Qt::MatchFixedString)
00709 placeholders->append(pm.matchValueString());
00710 else
00711 placeholders->append(pm.matchValueString());
00712 if (pm.matchValueString().isEmpty())
00713 condition += " OR "+field+" IS NULL";
00714 break;
00715 case Qt::MatchContains:
00716 condition += make_like_condition(field, pm.matchValueString(), true, true, (f & Qt::CaseSensitive));
00717 break;
00718 case Qt::MatchStartsWith:
00719 condition += make_like_condition(field, pm.matchValueString(), true, false, (f & Qt::CaseSensitive));
00720 break;
00721 case Qt::MatchEndsWith:
00722 condition += make_like_condition(field, pm.matchValueString(), false, true, (f & Qt::CaseSensitive));
00723 break;
00724 case Qt::MatchRegExp:
00725
00726 *haspostsqlcondition = true;
00727 *postsqlcondition = m;
00728 condition += "1";
00729 break;
00730 case Qt::MatchWildcard:
00731 if (f & Qt::CaseSensitive) {
00732 condition += field+" GLOB ? ";
00733 } else {
00734 condition += " lower("+field+") GLOB lower(?) ";
00735 }
00736 placeholders->append(pm.matchValueString());
00737 break;
00738 default:
00739 qWarning()<<KLF_FUNC_NAME<<": unknown property match type flags: "<<f ;
00740 return "0";
00741 }
00742 condition += ")";
00743 return condition;
00744 }
00745 if (m.type() == KLFLib::EntryMatchCondition::NegateMatchType) {
00746 if (m.conditionList().size() == 0) {
00747 qWarning()<<KLF_FUNC_NAME<<": condition list is empty for NOT match type!";
00748 return "0";
00749 }
00750 KLFLib::EntryMatchCondition postm = KLFLib::EntryMatchCondition::mkMatchAll();
00751 QString c = "(NOT " + make_sql_condition(m.conditionList()[0], placeholders,
00752 haspostsqlcondition, &postm) ;
00753 if (*haspostsqlcondition) {
00754 *postsqlcondition = KLFLib::EntryMatchCondition::mkNegateMatch(postm);
00755 }
00756 return c;
00757 }
00758 if (m.type() == KLFLib::EntryMatchCondition::OrMatchType ||
00759 m.type() == KLFLib::EntryMatchCondition::AndMatchType) {
00760 static const char *w_and = " AND ";
00761 static const char *w_or = " OR ";
00762 const char * word = (m.type() == KLFLib::EntryMatchCondition::AndMatchType) ? w_and : w_or ;
00763 QList<KLFLib::EntryMatchCondition> clist = m.conditionList();
00764 if (clist.isEmpty())
00765 return "1";
00766 int k;
00767 QString str;
00768 QList<KLFLib::EntryMatchCondition> postconditionlist;
00769 for (k = 0; k < clist.size(); ++k) {
00770 if (k > 0)
00771 str += word;
00772
00773 KLFLib::EntryMatchCondition thispostm = KLFLib::EntryMatchCondition::mkMatchAll();
00774 bool thishaspostsql;
00775 QString c = make_sql_condition(m.conditionList()[0], placeholders,
00776 &thishaspostsql, &thispostm) ;
00777 if (thishaspostsql) {
00778 postconditionlist.append(thispostm);
00779 }
00780 str += c;
00781 }
00782 if (postconditionlist.size()) {
00783 *haspostsqlcondition = true;
00784 *postsqlcondition = (m.type() == KLFLib::EntryMatchCondition::OrMatchType)
00785 ? KLFLib::EntryMatchCondition::mkOrMatch(postconditionlist)
00786 : KLFLib::EntryMatchCondition::mkOrMatch(postconditionlist) ;
00787 }
00788 return str;
00789 }
00790 qWarning()<<KLF_FUNC_NAME<<": unknown entry match condition type: "<<m.type();
00791 return "0";
00792 }
00793
00794 int KLFLibDBEngine::query(const QString& subResource, const Query& query, QueryResult *result)
00795 {
00796 KLF_DEBUG_BLOCK(KLF_FUNC_NAME);
00797 klfDbg( "\t: subResource="<<subResource<<"; query="<<query ) ;
00798
00799 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00800 return -1 ) ;
00801
00802 QStringList cols = columnNameList(subResource, query.wantedEntryProperties, true);
00803
00804 QString sql;
00805
00806 sql = QString("SELECT %1 FROM %2 ").arg(cols.join(","), quotedDataTableName(subResource));
00807 QVariantList placeholders;
00808 bool haspostsqlcondition = false;
00809 KLFLib::EntryMatchCondition postsqlcondition = KLFLib::EntryMatchCondition::mkMatchAll();
00810 QString wherecond = make_sql_condition(query.matchCondition, &placeholders, &haspostsqlcondition,
00811 &postsqlcondition);
00812 sql += " WHERE "+wherecond;
00813
00815 if (haspostsqlcondition) {
00816
00817 klfDbg("You are using a feature that is not natively implemented in KLFLibDBEngine: falling back to "
00818 "rudimentary and slow implementation!");
00819 return KLFLibResourceSimpleEngine::queryImpl(this, subResource, query, result);
00820 }
00821
00822 if (query.orderPropId != -1) {
00823 sql += " ORDER BY "+KLFLibEntry().propertyNameForId(query.orderPropId)+" ";
00824 sql += (query.orderDirection==Qt::AscendingOrder)?"ASC ":"DESC ";
00825 }
00826
00827 if (query.limit != -1) {
00828 sql += " LIMIT "+QString::number(query.skip+query.limit);
00829 }
00830
00831 klfDbg("Built query: SQL="<<sql<<"; placeholders="<<placeholders) ;
00832
00833 QSqlQuery q = QSqlQuery(pDB);
00834 q.prepare(sql);
00835 q.setForwardOnly(true);
00836 int k;
00837 for (k = 0; k < placeholders.size(); ++k)
00838 q.bindValue(k, placeholders[k]);
00839
00840
00841 bool r = q.exec();
00842 if ( !r || q.lastError().isValid() ) {
00843 qWarning()<<KLF_FUNC_NAME<<"SQL Error: "<<qPrintable(q.lastError().text())
00844 <<"\nSql was="<<sql<<"; bound values="<<q.boundValues();
00845 return -1;
00846 }
00847
00848
00849
00850 cols = detectEntryColumns(q);
00851
00852 int N = q.size();
00853 if (N == -1)
00854 N = 100;
00855 else
00856 N -= query.skip;
00857 KLFProgressReporter progr(0, N, this);
00858 if (!thisOperationProgressBlocked())
00859 emit operationStartReportingProgress(&progr, tr("Querying items from library database ..."));
00860
00861
00862 int skipped = 0;
00863 bool ok = true;
00864 while (skipped < query.skip && (ok = q.next()))
00865 ++skipped;
00866 klfDbg("skipped "<<skipped<<" entries.") ;
00867
00868
00869
00870 int count = 0;
00871 while (ok && q.next()) {
00872 if (count % 10 == 0 && count < N) {
00873
00874 progr.doReportProgress(count);
00875 }
00876
00877 KLFLibEntryWithId e;
00878 e.id = q.value(0).toInt();
00879 e.entry = readEntry(q, cols);
00880
00881 if (result->fillFlags & QueryResult::FillEntryIdList)
00882 result->entryIdList << e.id;
00883 if (result->fillFlags & QueryResult::FillRawEntryList)
00884 result->rawEntryList << e.entry;
00885 if (result->fillFlags & QueryResult::FillEntryWithIdList)
00886 result->entryWithIdList << e;
00887 ++count;
00888 }
00889
00890 progr.doReportProgress(count);
00891 klfDbg("got "<<count<<" entries.") ;
00892 return count;
00893 }
00894 QList<QVariant> KLFLibDBEngine::queryValues(const QString& subResource, int entryPropId)
00895 {
00896 KLF_DEBUG_BLOCK(KLF_FUNC_NAME);
00897 klfDbg( "\t: subResource="<<subResource<<"; entryPropId="<<entryPropId ) ;
00898
00899 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00900 return QList<QVariant>() ) ;
00901
00902 if (!pDBAvailColumns.contains(subResource) || !hasSubResource(subResource)) {
00903 qWarning()<<KLF_FUNC_NAME<<": bad sub-resource: "<<subResource;
00904 return QVariantList();
00905 }
00906
00907 KLFLibEntry dummye;
00908 QString pname;
00909 if (!dummye.propertyIdRegistered(entryPropId)) {
00910 qWarning()<<KLF_FUNC_NAME<<": Invalid property ID "<<entryPropId;
00911 return QVariantList();
00912 }
00913 pname = dummye.propertyNameForId(entryPropId);
00914 if (!pDBAvailColumns[subResource].contains(pname)) {
00915 qWarning()<<KLF_FUNC_NAME<<": property "<<pname<<" is not available in tables for sub-res "<<subResource
00916 <<" (avail are "<<pDBAvailColumns[subResource]<<")";
00917 return QVariantList();
00918 }
00919
00920 QString sql = "SELECT DISTINCT "+pname+" FROM "+quotedDataTableName(subResource);
00921
00922 QSqlQuery q = QSqlQuery(pDB);
00923 q.prepare(sql);
00924 q.setForwardOnly(true);
00925 bool r = q.exec();
00926 if ( !r || q.lastError().isValid() ) {
00927 qWarning()<<KLF_FUNC_NAME<<"SQL Error: "<<qPrintable(q.lastError().text())
00928 <<"\nSQL was: "<<sql;
00929 return QVariantList();
00930 }
00931
00932 QVariantList list;
00933 while (q.next()) {
00934 list << dbReadEntryPropertyValue(q.value(0), entryPropId);
00935 klfDbg("adding value "<<list.last().toString()) ;
00936 }
00937
00938 return list;
00939 }
00940
00941
00942
00943 KLFLibEntry KLFLibDBEngine::entry(const QString& subResource, entryId id)
00944 {
00945 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00946 return KLFLibEntry() ) ;
00947
00948 QSqlQuery q = QSqlQuery(pDB);
00949 q.prepare(QString("SELECT * FROM %1 WHERE id = ?").arg(quotedDataTableName(subResource)));
00950 q.addBindValue(id);
00951 bool r = q.exec();
00952
00953 if ( !r || q.lastError().isValid() || q.size() == 0) {
00954 qWarning("KLFLibDBEngine::entry: id=%d cannot be found!\n"
00955 "SQL Error (?): %s", id, qPrintable(q.lastError().text()));
00956 return KLFLibEntry();
00957 }
00958
00959
00960
00961
00962 if ( ! q.next() ) {
00963 qWarning("KLFLibDBEngine::entry(): no entry available!\n"
00964 "SQL=\"%s\" (?=%d)", qPrintable(q.lastQuery()), id);
00965 return KLFLibEntry();
00966 }
00967
00968
00969 KLFLibEntry e = readEntry(q, detectEntryColumns(q));
00970 q.finish();
00971 return e;
00972 }
00973
00974
00975 QList<KLFLibResourceEngine::KLFLibEntryWithId>
00976 KLFLibDBEngine::allEntries(const QString& subResource, const QList<int>& wantedEntryProperties)
00977 {
00978 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
00979 return QList<KLFLibEntryWithId>() ) ;
00980
00981 QStringList cols = columnNameList(subResource, wantedEntryProperties, true);
00982
00983 QSqlQuery q = QSqlQuery(pDB);
00984 q.prepare(QString("SELECT %1 FROM %2 ORDER BY id ASC").arg(cols.join(","), quotedDataTableName(subResource)));
00985 q.setForwardOnly(true);
00986 bool r = q.exec();
00987 if ( ! r || q.lastError().isValid() ) {
00988 qWarning()<<KLF_FUNC_NAME<<": SQL ERROR. Sql="<<q.lastQuery()<<"\n\tError: "<<q.lastError().text()
00989 <<"\nbound values: "<<q.boundValues();
00990 return QList<KLFLibEntryWithId>();
00991 }
00992
00993 QList<KLFLibEntryWithId> entryList;
00994
00995 cols = detectEntryColumns(q);
00996
00997 int count = q.size();
00998
00999 KLFProgressReporter progr(0, count, this);
01000 if (!thisOperationProgressBlocked())
01001 emit operationStartReportingProgress(&progr, tr("Fetching items from library database ..."));
01002
01003 int n = 0;
01004 while (q.next()) {
01005 if (n % 10 == 0)
01006 progr.doReportProgress(n++);
01007 KLFLibEntryWithId e;
01008 e.id = q.value(0).toInt();
01009 e.entry = readEntry(q, cols);
01010 entryList << e;
01011 }
01012
01013 progr.doReportProgress(count);
01014
01015 return entryList;
01016 }
01017
01018
01019 bool KLFLibDBEngine::compareDefaultSubResourceEquals(const QString& subResourceName) const
01020 {
01021 return QString::compare(defaultSubResource(), subResourceName, Qt::CaseInsensitive) == 0;
01022 }
01023
01024
01025 bool KLFLibDBEngine::canCreateSubResource() const
01026 {
01027 return baseCanModifyStatus(false) == MS_CanModify;
01028 }
01029
01030 bool KLFLibDBEngine::canDeleteSubResource(const QString& subResource) const
01031 {
01032 if (baseCanModifyStatus(true, subResource) == MS_CanModify)
01033 if (tableExists(subResource) && subResourceList().size() > 1)
01034 return true;
01035
01036 return false;
01037 }
01038
01039 QVariant KLFLibDBEngine::subResourceProperty(const QString& subResource, int propId) const
01040 {
01041 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
01042 return QVariant() ) ;
01043
01044 QSqlQuery q = QSqlQuery(pDB);
01045 q.prepare("SELECT pvalue FROM klf_subresprops WHERE lower(subresource) = lower(?) AND pid = ?");
01046 q.addBindValue(QVariant::fromValue<QString>(subResource));
01047 q.addBindValue(QVariant::fromValue<int>(propId));
01048 int r = q.exec();
01049 if ( !r || q.lastError().isValid() ) {
01050 qWarning()<<"KLFLibDBEngine::subResourceProperty("<<subResource<<","<<propId<<"): SQL Error: "
01051 <<q.lastError().text() << "\n\t\tSQL: "<<q.lastQuery()<<"\n\t\tBound values: "<<q.boundValues();
01052 klfDbg("DB: "<<pDB.connectionName());
01053 return QVariant();
01054 }
01055
01056
01057 if ( !q.next() ) {
01058
01059 if (propId == SubResPropLocked)
01060 return QVariant(false);
01061 if (propId == SubResPropViewType)
01062 return QVariant("");
01063 if (propId == SubResPropTitle)
01064 return QVariant("");
01065 return QVariant();
01066 }
01067 return convertVariantFromDBData(q.value(0));
01068 }
01069
01070
01071 bool KLFLibDBEngine::hasSubResource(const QString& subRes) const
01072 {
01073 return tableExists(subRes);
01074 }
01075
01076 QStringList KLFLibDBEngine::subResourceList() const
01077 {
01078 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
01079 return QStringList() ) ;
01080
01081 QStringList allTables = pDB.tables();
01082 QStringList subreslist;
01083 int k;
01084 for (k = 0; k < allTables.size(); ++k) {
01085 if (allTables[k].startsWith("t_"))
01086 subreslist << allTables[k].mid(2);
01087 }
01088 return subreslist;
01089 }
01090
01091
01092 bool KLFLibDBEngine::setSubResourceProperty(const QString& subResource, int propId, const QVariant& value)
01093 {
01094 KLF_DEBUG_TIME_BLOCK(KLF_FUNC_NAME) ;
01095
01096 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
01097 return false ) ;
01098
01099 if ( !canModifyProp(-1) )
01100 return false;
01101 if ( subResourceProperty(subResource, SubResPropLocked).toBool() && propId != SubResPropLocked )
01102 return false;
01103
01104 klfDbg( ": setting sub-resource property "<<propId<<" to "<<value<<" in sub-res "
01105 <<subResource ) ;
01106
01107 if ( subResourceProperty(subResource, propId) == value ) {
01108 klfDbg("property already has the requested value "<<value<<".");
01109 return true;
01110 }
01111
01112 {
01113 QSqlQuery q = QSqlQuery(pDB);
01114 q.prepare("DELETE FROM klf_subresprops WHERE lower(subresource) = lower(?) and pid = ?");
01115 q.bindValue(0, subResource);
01116 q.bindValue(1, propId);
01117 bool r = q.exec();
01118 if ( !r || q.lastError().isValid() ) {
01119 qWarning()<<"KLFLibDBEngine::setSubRes.Prop.("<<subResource<<","<<propId<<","<<value<<"):"
01120 <<" can't DELETE!\n\t"<<q.lastError().text()<<"\n\tBound values="<<q.boundValues();
01121 return false;
01122 }
01123 }
01124 {
01125 QSqlQuery q = QSqlQuery(pDB);
01126 q.prepare("INSERT INTO klf_subresprops (subresource,pid,pvalue) VALUES (?,?,?)");
01127 q.bindValue(0, subResource);
01128 q.bindValue(1, propId);
01129 q.bindValue(2, convertVariantToDBData(value));
01130 if ( ! q.exec() || q.lastError().isValid() ) {
01131 qWarning()<<"KLFLibDBEngine::setSubRes.Prop.("<<subResource<<","<<propId<<","<<value<<"):"
01132 <<" can't INSERT!\n\t"<<q.lastError().text()<<"\n\tBound values="<<q.boundValues();
01133 return false;
01134 }
01135 }
01136
01137 dbPropertyNotifierInstance(pDB.connectionName())
01138 ->notifySubResourcePropertyChanged(subResource, propId);
01139
01140
01141
01142
01143 return true;
01144 }
01145
01146
01147
01148 QVariant KLFLibDBEngine::dbMakeEntryPropertyValue(const QVariant& entryval, int propertyId)
01149 {
01150 if (propertyId == KLFLibEntry::Latex)
01151 return QVariant::fromValue<QString>(entryval.toString());
01152 if (propertyId == KLFLibEntry::DateTime)
01153 return QVariant::fromValue<qulonglong>(entryval.toDateTime().toTime_t());
01154 if (propertyId == KLFLibEntry::Preview)
01155 return QVariant::fromValue<QByteArray>(image_data(entryval.value<QImage>(), "PNG"));
01156 if (propertyId == KLFLibEntry::Category)
01157 return QVariant::fromValue<QString>(entryval.toString());
01158 if (propertyId == KLFLibEntry::Tags)
01159 return QVariant::fromValue<QString>(entryval.toString());
01160 if (propertyId == KLFLibEntry::PreviewSize) {
01161 QSize s = entryval.value<QSize>();
01162 return QVariant::fromValue<qulonglong>( (((qulonglong)s.width()) << 32) |
01163 (((qulonglong)s.height()) & 0xFFFFFFFF) );
01164 }
01165
01166 return convertVariantToDBData(entryval);
01167 }
01168 QVariant KLFLibDBEngine::dbReadEntryPropertyValue(const QVariant& dbdata, int propertyId)
01169 {
01170 if (propertyId == KLFLibEntry::Latex)
01171 return dbdata.toString();
01172 if (propertyId == KLFLibEntry::DateTime)
01173 return QVariant::fromValue<QDateTime>(QDateTime::fromTime_t(dbdata.toULongLong()));
01174 if (propertyId == KLFLibEntry::Preview) {
01175 QImage img;
01176 img.loadFromData(dbdata.toByteArray(), "PNG");
01177 return QVariant::fromValue<QImage>(img);
01178 }
01179 if (propertyId == KLFLibEntry::Category)
01180 return dbdata.toString();
01181 if (propertyId == KLFLibEntry::Tags)
01182 return dbdata.toString();
01183 if (propertyId == KLFLibEntry::PreviewSize) {
01184 qulonglong val = dbdata.toULongLong();
01185 int w = (int)((val>>32) & 0xFFFFFFFF) ;
01186 int h = (int)(val & 0xFFFFFFFF) ;
01187 return QVariant::fromValue<QSize>(QSize(w, h));
01188 }
01189
01190 return convertVariantFromDBData(dbdata);
01191 }
01192
01193
01194
01195
01196
01197
01198 QVariant KLFLibDBEngine::convertVariantToDBData(const QVariant& value) const
01199 {
01200
01201
01202 if (!value.isValid())
01203 return QVariant();
01204
01205 int t = value.type();
01206 const char *ts = value.typeName();
01207 if (t == QVariant::Int || t == QVariant::UInt || t == QVariant::LongLong || t == QVariant::ULongLong ||
01208 t == QVariant::Double || t == QVariant::Bool)
01209 return value;
01210
01211
01212 if (t == QVariant::String)
01213 return encaps(ts, value.toString());
01214
01215
01216 if (t == QVariant::ByteArray)
01217 return encaps(ts, value.toByteArray());
01218 if (t == QVariant::DateTime)
01219 return encaps(ts, value.value<QDateTime>().toString(Qt::ISODate).toLatin1());
01220 if (t == QVariant::Image)
01221 return encaps(ts, image_data(value.value<QImage>(), "PNG"));
01222
01223
01224 QByteArray valuedata;
01225 { QDataStream stream(&valuedata, QIODevice::WriteOnly);
01226 stream.setVersion(QDataStream::Qt_4_4);
01227 stream << value; }
01228 return encaps(ts, valuedata);
01229 }
01230
01231 QVariant KLFLibDBEngine::encaps(const char *ts, const QString& data) const
01232 {
01233 return QVariant::fromValue<QString>(QString("[")+ts+"]"+data);
01234 }
01235 QVariant KLFLibDBEngine::encaps(const char *ts, const QByteArray& data) const
01236 {
01237 QByteArray edata;
01238 edata.append("[");
01239 edata.append(ts);
01240 edata.append("]");
01241 edata.append(data);
01242 return QVariant::fromValue<QByteArray>(edata);
01243 }
01244 QVariant KLFLibDBEngine::convertVariantFromDBData(const QVariant& dbdata) const
01245 {
01246 if ( !dbdata.isValid() )
01247 return QVariant();
01248
01249 int t = dbdata.type();
01250 if (t == QVariant::Int || t == QVariant::UInt || t == QVariant::LongLong || t == QVariant::ULongLong ||
01251 t == QVariant::Double || t == QVariant::Bool)
01252 return dbdata;
01253
01254 if (t == QVariant::String)
01255 return decaps(dbdata.toString());
01256 if (t == QVariant::ByteArray)
01257 return decaps(dbdata.toByteArray());
01258
01259 qWarning()<<"Unexpected DB data variant found: "<<dbdata;
01260 return QVariant();
01261 }
01262 QVariant KLFLibDBEngine::decaps(const QString& sdata) const
01263 {
01264 return decaps(sdata.toUtf8());
01265 }
01266 QVariant KLFLibDBEngine::decaps(const QByteArray& data) const
01267 {
01268
01269 int k;
01270 if (!data.size())
01271 return QVariant();
01272 if (data[0] != '[')
01273 return QVariant::fromValue<QString>(QString::fromUtf8(data));
01274 for (k = 1; k < data.size() && data[k] != ']'; ++k) ;
01275 if (k >= data.size()) {
01276 qWarning()<<"KLFLibDBEngine::decaps(QB.A.): bad data:"<<data;
01277 return QVariant();
01278 }
01279 const QByteArray typenam = data.mid(1, k-1);
01280 const QByteArray valuedata = data.mid(k+1);
01281
01282 if (typenam == "bool") {
01283 QString svaluedata = QString::fromUtf8(valuedata).trimmed();
01284 return QVariant::fromValue<bool>(svaluedata[0] != '0' ||
01285 (svaluedata != "1" && (svaluedata[0].toLower() == 't' ||
01286 svaluedata[0].toLower() == 'y' ||
01287 svaluedata.toInt() != 0)) );
01288 }
01289 if (typenam == "QString")
01290 return QVariant::fromValue<QString>(QString::fromUtf8(valuedata));
01291 if (typenam == "QByteArray")
01292 return QVariant::fromValue<QByteArray>(valuedata);
01293 if (typenam == "QDateTime")
01294 return QDateTime::fromString(QString::fromLatin1(valuedata), Qt::ISODate);
01295 if (typenam == "QImage") {
01296 QImage img;
01297 img.loadFromData(valuedata);
01298 return QVariant::fromValue<QImage>(img);
01299 }
01300
01301
01302
01303 QVariant value;
01304 { QDataStream stream(valuedata);
01305 stream.setVersion(QDataStream::Qt_4_4);
01306 stream >> value; }
01307 return value;
01308 }
01309
01310 bool KLFLibDBEngine::ensureDataTableColumnsExist(const QString& subResource, const QStringList& columnList)
01311 {
01312 QSqlRecord rec = pDB.record(dataTableName(subResource));
01313 int k;
01314 bool failed = false;
01315 for (k = 0; k < columnList.size(); ++k) {
01316 if (columnList[k] == "*")
01317 continue;
01318 if (rec.contains(columnList[k]))
01319 continue;
01320 QSqlQuery sql = QSqlQuery(pDB);
01321 sql.prepare("ALTER TABLE "+quotedDataTableName(subResource)+" ADD COLUMN "+columnList[k]+" BLOB");
01322 bool r = sql.exec();
01323 if (!r || sql.lastError().isValid()) {
01324 qWarning()<<"KLFLibDBEngine::ensureDataTableColumnsExist("<<subResource<<"): Can't add column "
01325 <<columnList[k]<<"!\n"<<sql.lastError().text()<<" SQL="<<sql.lastQuery();
01326 failed = true;
01327 }
01328 }
01329
01330 readAvailColumns(subResource);
01331
01332 return !failed;
01333 }
01334 bool KLFLibDBEngine::ensureDataTableColumnsExist(const QString& subResource)
01335 {
01336 KLFLibEntry dummy;
01337 QSqlRecord rec = pDB.record(dataTableName(subResource));
01338 QStringList propNameList = dummy.registeredPropertyNameList();
01339 return ensureDataTableColumnsExist(subResource, propNameList);
01340 }
01341
01342
01343
01344
01345
01346 bool KLFLibDBEngine::deleteSubResource(const QString& subResource)
01347 {
01348 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
01349 return false ) ;
01350
01351 if (!canDeleteSubResource(subResource))
01352 return false;
01353
01354 QSqlQuery q = QSqlQuery(pDB);
01355 q.prepare(QString("DROP TABLE %1").arg(quotedDataTableName(subResource)));
01356 int r = q.exec();
01357 if ( !r || q.lastError().isValid() ) {
01358 qWarning()<<KLF_FUNC_NAME<<"("<<subResource<<"): SQL Error: "
01359 <<q.lastError().text() << "\n\tSQL="<<q.lastQuery() ;
01360 return false;
01361 }
01362
01363
01364 emit subResourceDeleted(subResource);
01365
01366 if (subResource == defaultSubResource()) {
01367 QString newDefaultSubResource;
01368 if (subResourceList().size() >= 1)
01369 newDefaultSubResource = subResourceList()[0];
01370 else
01371 newDefaultSubResource = QString();
01372 setDefaultSubResource(newDefaultSubResource);
01373 emit defaultSubResourceChanged(newDefaultSubResource);
01374 }
01375 return true;
01376 }
01377
01378
01379 bool KLFLibDBEngine::createSubResource(const QString& subResource, const QString& subResourceTitle)
01380 {
01381
01382 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
01383 return false ) ;
01384
01385 if ( subResource.isEmpty() )
01386 return false;
01387
01388 if ( subResourceList().contains(subResource) ) {
01389 qWarning()<<"KLFLibDBEngine::createSubResource: Sub-Resource "<<subResource<<" already exists!";
01390 return false;
01391 }
01392
01393 bool r = createFreshDataTable(pDB, subResource);
01394 if (!r)
01395 return false;
01396 QString title = subResourceTitle;
01397 if (title.isEmpty())
01398 title = subResource;
01399 r = setSubResourceProperty(subResource, SubResPropTitle, QVariant(title));
01400 if (!r)
01401 return false;
01402
01403
01404 emit subResourceCreated(subResource);
01405
01406 return true;
01407 }
01408
01409
01410 QList<KLFLibResourceEngine::entryId> KLFLibDBEngine::insertEntries(const QString& subres,
01411 const KLFLibEntryList& entrylist)
01412 {
01413 int k, j;
01414
01415 KLF_ASSERT_CONDITION( validDatabase() , "Database connection not valid!" ,
01416 return QList<KLFLibResourceEngine::entryId>() ) ;
01417
01418 klfDbg("subres="<<subres<<"; entrylist="<<entrylist) ;
01419
01420 if ( entrylist.size() == 0 ) {
01421 return QList<entryId>();
01422 }
01423
01424 if ( !canModifyData(subres, InsertData) ) {
01425 klfDbg("can't modify data.") ;
01426 return QList<entryId>();
01427 }
01428
01429 if ( !tableExists(subres) ) {
01430 qWarning()<<KLF_FUNC_NAME<<": Sub-Resource "<<subres<<" does not exist.";
01431 return QList<entryId>();
01432 }
01433
01434 KLFLibEntry e;
01435 QList<int> propids = e.registeredPropertyIdList();
01436 QStringList props;
01437 QStringList questionmarks;
01438 for (k = 0; k < propids.size(); ++k) {
01439 props << e.propertyNameForId(propids[k]);
01440 questionmarks << "?";
01441 }
01442
01443 QList<entryId> insertedIds;
01444
01445 ensureDataTableColumnsExist(subres);
01446
01447 KLFProgressReporter progr(0, entrylist.size(), this);
01448 if (!thisOperationProgressBlocked())
01449 emit operationStartReportingProgress(&progr, tr("Inserting items into library database ..."));
01450
01451 QSqlQuery q = QSqlQuery(pDB);
01452 q.prepare("INSERT INTO " + quotedDataTableName(subres) + " (" + props.join(",") + ") "
01453 " VALUES (" + questionmarks.join(",") + ")");
01454 klfDbg( "INSERT query: "<<q.lastQuery() ) ;
01455
01456 for (j = 0; j < entrylist.size(); ++j) {
01457 if (j % 10 == 0)
01458 progr.doReportProgress(j);
01459
01460 for (k = 0; k < propids.size(); ++k) {
01461 QVariant data = dbMakeEntryPropertyValue(entrylist[j].property(propids[k]), propids[k]);
01462
01463 klfDbg( "Binding value "<<k<<": "<<data ) ;
01464 q.bindValue(k, data);
01465 }
01466
01467 bool r = q.exec();
01468 if ( ! r || q.lastError().isValid() ) {
01469 qWarning()<<"INSERT failed! SQL Error: "<<q.lastError().text()<<"\n\tSQL="<<q.lastQuery();
01470 insertedIds << -1;
01471 } else {
01472 QVariant v_id = q.lastInsertId();
01473 if ( ! v_id.isValid() )
01474 insertedIds << -2;
01475 else
01476 insertedIds << v_id.toInt();
01477 }
01478 }
01479
01480
01481
01482 progr.doReportProgress(entrylist.size());
01483
01484 emit dataChanged(subres, InsertData, insertedIds);
01485 return insertedIds;
01486 }
01487
01488
01489 bool KLFLibDBEngine::changeEntries(const QString& subResource, const QList<entryId>& idlist,
01490 const QList<int>& properties, const QList<QVariant>& values)
01491 {
01492 if ( ! validDatabase() )
01493 return false;
01494 if ( ! canModifyData(subResource, ChangeData) )
01495 return false;
01496
01497 if ( !tableExists(subResource) ) {
01498 qWarning()<<KLF_FUNC_NAME<<": Sub-Resource "<<subResource<<" does not exist.";
01499 return false;
01500 }
01501
01502 if ( properties.size() != values.size() ) {
01503 qWarning("KLFLibDBEngine::changeEntry(): properties' and values' sizes mismatch!");
01504 return false;
01505 }
01506
01507 if ( idlist.size() == 0 )
01508 return true;
01509
01510 klfDbg( "KLFLibDBEngine::changeEntries: funcional tests passed; idlist="<<idlist<<" props="
01511 <<properties<<" vals="<<values ) ;
01512
01513 ensureDataTableColumnsExist(subResource);
01514
01515 KLFLibEntry e;
01516 QStringList updatepairs;
01517 int k;
01518 for (k = 0; k < properties.size(); ++k) {
01519 updatepairs << (e.propertyNameForId(properties[k]) + " = ?");
01520 }
01521
01522 QSqlQuery q = QSqlQuery(pDB);
01523 q.prepare(QString("UPDATE %1 SET %2 WHERE id = ?")
01524 .arg(quotedDataTableName(subResource), updatepairs.join(",")));
01525 for (k = 0; k < properties.size(); ++k) {
01526 q.bindValue(k, dbMakeEntryPropertyValue(values[k], properties[k]));
01527 }
01528 const int idBindValueNum = k;
01529
01530 KLFProgressReporter progr(0, idlist.size(), this);
01531 if (!thisOperationProgressBlocked())
01532 emit operationStartReportingProgress(&progr, tr("Changing entries in database ..."));
01533
01534 bool failed = false;
01535 for (k = 0; k < idlist.size(); ++k) {
01536 if (k % 10 == 0)
01537 progr.doReportProgress(k);
01538
01539 q.bindValue(idBindValueNum, idlist[k]);
01540 bool r = q.exec();
01541 if ( !r || q.lastError().isValid() ) {
01542 qWarning() << "SQL UPDATE Error: "<<q.lastError().text()<<"\nWith SQL="<<q.lastQuery()
01543 <<";\n and bound values="<<q.boundValues();
01544 failed = true;
01545 }
01546 }
01547
01548 progr.doReportProgress(idlist.size());
01549
01550 emit dataChanged(subResource, ChangeData, idlist);
01551
01552 return !failed;
01553 }
01554
01555 bool KLFLibDBEngine::deleteEntries(const QString& subResource, const QList<entryId>& idlist)
01556 {
01557 if ( ! validDatabase() )
01558 return false;
01559 if (idlist.size() == 0)
01560 return true;
01561 if ( ! canModifyData(subResource, DeleteData) )
01562 return false;
01563
01564 if ( !tableExists(subResource) ) {
01565 qWarning()<<KLF_FUNC_NAME<<": Sub-Resource "<<subResource<<" does not exist.";
01566 return false;
01567 }
01568
01569 int k;
01570 bool failed = false;
01571
01572 QString sql = QString("DELETE FROM %1 WHERE id = ?").arg(quotedDataTableName(subResource));
01573
01574 klfDbg("sql is "<<sql<<", idlist is "<<idlist) ;
01575
01576 QSqlQuery q = QSqlQuery(pDB);
01577 q.prepare(sql);
01578
01579 KLFProgressReporter progr(0, idlist.size(), this);
01580 if (!thisOperationProgressBlocked())
01581 emit operationStartReportingProgress(&progr, tr("Removing entries from database ..."));
01582
01583 for (k = 0; k < idlist.size(); ++k) {
01584 if (k % 10 == 0)
01585 progr.doReportProgress(k);
01586
01587 q.bindValue(0, idlist[k]);
01588 bool r = q.exec();
01589 if ( !r || q.lastError().isValid() ) {
01590 qWarning()<<KLF_FUNC_NAME<<": Sql error: "<<q.lastError().text();
01591 failed = true;
01592 continue;
01593 }
01594 }
01595
01596 progr.doReportProgress(idlist.size());
01597
01598 emit dataChanged(subResource, DeleteData, idlist);
01599
01600 return !failed;
01601 }
01602
01603 bool KLFLibDBEngine::saveTo(const QUrl& newPath)
01604 {
01605 if (newPath.scheme() == QLatin1String("klf+sqlite") && url().scheme() == QLatin1String("klf+sqlite")) {
01606 if (!newPath.host().isEmpty()) {
01607 qWarning()<<"KLFLibDBEngine::saveTo("<<newPath<<"): Expected empty host!";
01608 return false;
01609 }
01610 return QFile::copy(klfUrlLocalFilePath(url()), klfUrlLocalFilePath(newPath));
01611 }
01612 qWarning()<<"KLFLibDBEngine::saveTo("<<newPath<<"): Bad scheme!";
01613 return false;
01614 }
01615
01616
01617 bool KLFLibDBEngine::initFreshDatabase(QSqlDatabase db)
01618 {
01619 if ( ! db.isOpen() )
01620 return false;
01621
01622
01623
01624 QStringList sql;
01625 sql << "CREATE TABLE klf_properties (id INTEGER PRIMARY KEY, name TEXT, value BLOB)";
01626 sql << "INSERT INTO klf_properties (name, value) VALUES ('Title', 'New Resource')";
01627 sql << "INSERT INTO klf_properties (name, value) VALUES ('Locked', 'false')";
01628 sql << "CREATE TABLE klf_dbmetainfo (id INTEGER PRIMARY KEY, name TEXT, value BLOB)";
01629 sql << "INSERT INTO klf_dbmetainfo (name, value) VALUES ('klf_version', '" KLF_VERSION_STRING "')";
01630 sql << "INSERT INTO klf_dbmetainfo (name, value) VALUES ('klf_dbversion', '"+
01631 QString::number(1)+"')";
01632 sql << "CREATE TABLE klf_subresprops (id INTEGER PRIMARY KEY, pid INTEGER, subresource TEXT, pvalue BLOB)";
01633
01634 int k;
01635 for (k = 0; k < sql.size(); ++k) {
01636 QSqlQuery query(db);
01637 query.prepare(sql[k]);
01638 bool r = query.exec();
01639 if ( !r || query.lastError().isValid() ) {
01640 qWarning()<<"KLFLibDBEngine::initFreshDatabase(): SQL Error: "<<query.lastError().text()<<"\n"
01641 <<"SQL="<<sql[k];
01642 return false;
01643 }
01644 }
01645 return true;
01646 }
01647
01648
01649 bool KLFLibDBEngine::createFreshDataTable(QSqlDatabase db, const QString& subres)
01650 {
01651 qDebug("KLFLibDBEngine::createFreshDataTable(.., '%s')", qPrintable(subres));
01652 QString datatablename = dataTableName(subres);
01653 if ( ! db.isOpen() ) {
01654 qWarning("KLFLibDBEngine::createFreshDataTable(..,%s): DB is not open!", qPrintable(subres));
01655 return false;
01656 }
01657
01658 if ( db.tables().contains(datatablename) ) {
01659 qWarning("KLFLibDBEngine::createFreshDataTable(..,%s): table %s exists!", qPrintable(subres),
01660 qPrintable(datatablename));
01661 return false;
01662 }
01663 QString qdtname = quotedDataTableName(subres);
01664
01665
01666 QSqlQuery query(db);
01667 query.prepare(QString("")+
01668 "CREATE TABLE "+qdtname+" (id INTEGER PRIMARY KEY, Latex TEXT, DateTime TEXT, "
01669 " Preview BLOB, PreviewSize TEXT, Category TEXT, Tags TEXT, Style BLOB)");
01670 bool r = query.exec();
01671 if ( !r || query.lastError().isValid() ) {
01672 qWarning()<<"createFreshDataTable(): SQL Error: "<<query.lastError().text()<<"\n"
01673 <<"SQL="<<query.lastQuery();
01674 return false;
01675 }
01676
01677 return true;
01678 }
01679
01680
01681
01682
01683
01684
01685
01686
01687
01688
01689
01690
01691
01692
01693
01694
01695
01696
01697
01698
01699 QMap<QString,KLFLibDBEnginePropertyChangeNotifier*> KLFLibDBEngine::pDBPropertyNotifiers
01700 = QMap<QString,KLFLibDBEnginePropertyChangeNotifier*>();
01701
01702
01703 KLFLibDBEnginePropertyChangeNotifier *KLFLibDBEngine::dbPropertyNotifierInstance(const QString& dbname)
01704 {
01705 if (!pDBPropertyNotifiers.contains(dbname))
01706 pDBPropertyNotifiers[dbname] = new KLFLibDBEnginePropertyChangeNotifier(dbname, qApp);
01707 return pDBPropertyNotifiers[dbname];
01708 }
01709
01710
01711
01712 #define MAGIC_SQLITE_HEADER_LEN 16
01713
01714 QString KLFLibDBLocalFileSchemeGuesser::guessScheme(const QString& fileName) const
01715 {
01716
01717 const char magic_sqlite_header[MAGIC_SQLITE_HEADER_LEN]
01718 = { 0x53, 0x51, 0x4c, 0x69, 0x74, 0x65, 0x20, 0x66,
01719 0x6f, 0x72, 0x6d, 0x61, 0x74, 0x20, 0x33, 0x00 };
01720 char header[MAGIC_SQLITE_HEADER_LEN];
01721
01722 klfDbg("guessing scheme of "<<fileName) ;
01723
01724 if (fileName.endsWith(".klf.db"))
01725 return QLatin1String("klf+sqlite");
01726
01727 QFile f(fileName);
01728 if ( ! f.open(QIODevice::ReadOnly) ) {
01729 klfDbg("Yikes, can't read file "<<fileName);
01730 return QString();
01731 }
01732
01733 int len = f.read(header, MAGIC_SQLITE_HEADER_LEN);
01734
01735 if (len < MAGIC_SQLITE_HEADER_LEN) {
01736 klfDbg("Nope-can't read header.");
01737 return QString();
01738 }
01739
01740 if (!strncmp(header, magic_sqlite_header, MAGIC_SQLITE_HEADER_LEN)) {
01741 klfDbg("Yep, it's klf-sqlite!");
01742 return QLatin1String("klf+sqlite");
01743 }
01744
01745 klfDbg("Nope-bad header.");
01746 return QString();
01747 }
01748
01749
01750
01751
01752 KLFLibDBEngineFactory::KLFLibDBEngineFactory(QObject *parent)
01753 : KLFLibEngineFactory(parent)
01754 {
01755 KLFLibBasicWidgetFactory::LocalFileType f;
01756 f.scheme = QLatin1String("klf+sqlite");
01757 f.filepattern = QLatin1String("*.klf.db");
01758 f.filter = QString("%1 (%2)").arg(schemeTitle(f.scheme), f.filepattern);
01759 KLFLibBasicWidgetFactory::addLocalFileType(f);
01760 new KLFLibDBLocalFileSchemeGuesser(this);
01761 }
01762
01763 QStringList KLFLibDBEngineFactory::supportedTypes() const
01764 {
01765 return QStringList() << QLatin1String("klf+sqlite");
01766 }
01767
01768 QString KLFLibDBEngineFactory::schemeTitle(const QString& scheme) const
01769 {
01770 if (scheme == QLatin1String("klf+sqlite"))
01771 return tr("Local Library Database File");
01772 return QString();
01773 }
01774 uint KLFLibDBEngineFactory::schemeFunctions(const QString& scheme) const
01775 {
01776 uint flags = FuncOpen;
01777 if (scheme == QLatin1String("klf+sqlite"))
01778 flags |= FuncCreate;
01779 else
01780 qWarning()<<"KLFLibDBEngineFactory::schemeFunctions: Bad scheme: "<<scheme;
01781 return flags;
01782 }
01783
01784 QString KLFLibDBEngineFactory::correspondingWidgetType(const QString& scheme) const
01785 {
01786 if (scheme == QLatin1String("klf+sqlite"))
01787 return QLatin1String("LocalFile");
01788 return QString();
01789 }
01790
01791
01792 KLFLibResourceEngine *KLFLibDBEngineFactory::openResource(const QUrl& location, QObject *parent)
01793 {
01794 return KLFLibDBEngine::openUrl(location, parent);
01795 }
01796
01797
01798 KLFLibResourceEngine *KLFLibDBEngineFactory::createResource(const QString& scheme,
01799 const Parameters& parameters, QObject *parent)
01800 {
01801 QString defsubres = parameters["klfDefaultSubResource"].toString();
01802 QString defsubrestitle = parameters["klfDefaultSubResourceTitle"].toString();
01803 if (defsubres.isEmpty())
01804 defsubres = "entries";
01805 if (defsubrestitle.isEmpty())
01806 defsubrestitle = tr("Default Table", "[[default sub-resource title]]");
01807
01808
01809 defsubres = KLFLibNewSubResDlg::makeSubResInternalName(defsubres);
01810
01811 if ( !parameters.contains("Filename") ) {
01812 qWarning()
01813 <<"KLFLibLegacyEngineFactory::createResource: bad parameters. They do not contain `Filename': "
01814 <<parameters;
01815 }
01816
01817 if (scheme == QLatin1String("klf+sqlite"))
01818 return KLFLibDBEngine::createSqlite(parameters["Filename"].toString(), defsubres,
01819 defsubrestitle, parent);
01820 qWarning()<<"KLFLibDBEngineFactory::createResource("<<scheme<<","<<parameters<<","<<parent<<"):"
01821 <<"Bad scheme!";
01822 return NULL;
01823 }