QVariant RelationalProxyModel::getRelationData(QModelIndex index) const {
int column = index.column();
if (not m_relations.contains(column))
return QVariant();
const QSqlRelation relation = m_relations[column];
QVariant related_id = sourceModel()->data(mapToSource(index));
QSqlDriver *driver = QSqlDatabase::database().driver();
QSqlRecord search_record;
search_record.append(QSqlField(relation.indexColumn(), QVariant::Int));
search_record.setValue(relation.indexColumn(), QVariant(related_id.toInt()));
QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, relation.tableName(), search_record, false);
QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, relation.tableName(), QSqlDatabase::database().record(relation.tableName()), false);
QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));
query.next();
if (not query.isValid())
return QVariant();
return query.record().value(relation.displayColumn());
}
// ## See if DELETE+INSERT is the best approach. Sqlite3 supports INSERT OR IGNORE which could aslo be used
// ## Also check other upsert methods
QList<QSqlRecord> VideoParser::updateMediaInfos(const QList<QFileInfo> &fis, const QString &searchPath, QSqlDatabase db)
{
Q_UNUSED(searchPath);
QList<QSqlRecord> records;
QSqlQuery query(db);
ScopedTransaction transaction(db);
foreach(const QFileInfo &fi, fis) {
DEBUG << "Updating " << fi.absoluteFilePath();
query.prepare("DELETE FROM video WHERE filepath=:filepath");
query.bindValue(":filepath", fi.absoluteFilePath());
if (!query.exec())
qWarning() << query.lastError().text();
if (!query.prepare("INSERT INTO video (filepath, title, thumbnail, uri, directory, mtime, ctime, filesize, show, season) "
" VALUES (:filepath, :title, :thumbnail, :uri, :directory, :mtime, :ctime, :filesize, :show, :season)")) {
qWarning() << query.lastError().text();
return records;
}
query.bindValue(":filepath", fi.absoluteFilePath());
query.bindValue(":title", determineTitle(fi));
query.bindValue(":thumbnail", generateThumbnail(m_settings, fi));
query.bindValue(":uri", QUrl::fromLocalFile(fi.absoluteFilePath()).toEncoded());
query.bindValue(":directory", fi.absolutePath() + '/');
query.bindValue(":mtime", fi.lastModified().toTime_t());
query.bindValue(":ctime", fi.created().toTime_t());
query.bindValue(":filesize", fi.size());
QPair<QString, QString> showAndSeason = determineShowAndSeason(fi, searchPath);
query.bindValue(":show", showAndSeason.first);
query.bindValue(":season", showAndSeason.second);
if (!query.exec())
qWarning() << query.lastError().text();
QSqlRecord record;
record.append(QSqlField("id", QVariant::Int));
record.setValue(0, query.lastInsertId());
QMap<QString, QVariant> boundValues = query.boundValues();
for (QMap<QString, QVariant>::const_iterator it = boundValues.constBegin(); it != boundValues.constEnd(); ++it) {
QString key = it.key().mid(1); // remove the ':'
record.append(QSqlField(key, (QVariant::Type) it.value().type()));
record.setValue(key, it.value());
}
records.append(record);
}
// called on first fetch
void QSQLite2ResultPrivate::init(const char **cnames, int numCols)
{
if (!cnames)
return;
rInf.clear();
if (numCols <= 0)
return;
q->init(numCols);
for (int i = 0; i < numCols; ++i) {
const char* lastDot = strrchr(cnames[i], '.');
const char* fieldName = lastDot ? lastDot + 1 : cnames[i];
//remove quotations around the field name if any
QString fieldStr = QString::fromAscii(fieldName);
QLatin1Char quote('\"');
if ( fieldStr.length() > 2 && fieldStr.startsWith(quote) && fieldStr.endsWith(quote)) {
fieldStr = fieldStr.mid(1);
fieldStr.chop(1);
}
rInf.append(QSqlField(fieldStr,
nameToType(QString::fromAscii(cnames[i+numCols]))));
}
}
QSqlField QSqlRecordProto::field(const QString &name) const
{
QSqlRecord *item = qscriptvalue_cast<QSqlRecord*>(thisObject());
if (item)
return item->field(name);
return QSqlField();
}
QSqlField QSqlRecordProto::field(int index) const
{
QSqlRecord *item = qscriptvalue_cast<QSqlRecord*>(thisObject());
if (item)
return item->field(index);
return QSqlField();
}
QSqlIndex SqliteDriver::primaryIndex(const QString &tablename) const
{
QSqlIndex idx(tablename);
if (!isOpen() || !dataBase_)
return idx;
QDomDocument doc(tablename);
QDomElement docElem;
QString stream = db_->managerModules()->contentCached(tablename + ".mtd");
if (!FLUtil::domDocumentSetContent(doc, stream)) {
#ifdef FL_DEBUG
qWarning("FLManager : " + QApplication::tr("Error al cargar los metadatos para la tabla %1").arg(tablename));
#endif
return primaryIndex2(tablename);
}
docElem = doc.documentElement();
FLTableMetaData *mtd = db_->manager()->metadata(&docElem, true);
if (!mtd)
return primaryIndex2(tablename);
idx.append(QSqlField(mtd->primaryKey(), FLFieldMetaData::flDecodeType(mtd->fieldType(mtd->primaryKey()))));
idx.setName(tablename.lower() + "_pkey");
delete mtd;
return idx;
}
QSqlIndex SqliteDriver::primaryIndex2(const QString &tblname) const
{
QSqlRecordInfo rec(recordInfo(tblname)); // expensive :(
if (!isOpen() || !dataBase_)
return QSqlIndex();
QSqlQuery q = createQuery();
q.setForwardOnly(TRUE);
// finrst find a UNIQUE INDEX
q.exec("PRAGMA index_list('" + tblname + "');");
QString indexname;
while (q.next()) {
if (q.value(2).toInt() == 1) {
indexname = q.value(1).toString();
break;
}
}
if (indexname.isEmpty())
return QSqlIndex();
q.exec("PRAGMA index_info('" + indexname + "');");
QSqlIndex index(tblname, indexname);
while (q.next()) {
QString name = q.value(2).toString();
QSqlVariant::Type type = QSqlVariant::Invalid;
if (rec.contains(name))
type = rec.find(name).type();
index.append(QSqlField(name, type));
}
return index;
}
void ContextModel::addNewContext(QString newContext)
{
if (newContext.isEmpty())
{
return;
}
QSqlQuery query;
query.prepare("select count(id) from context where shortdescr like :context");
query.bindValue(":context", newContext);
query.exec();
if (query.next())
{
if (query.record().value("count(id)").toInt() > 0)
return;
}
qDebug() << "save new context " << newContext << " in database.";
QSqlRecord record;
QSqlField shortDescr = QSqlField("shortdescr", QVariant::String);
shortDescr.setValue(newContext);
record.append(shortDescr);
QSqlField id = QSqlField("id", QVariant::Int);
shortDescr.setValue(20);
record.append(id);
if(!insertRecord( -1, record))
{
qDebug("could not create new context.");
}
if (!submitAll())
{
qDebug("could not create new context.");
}
select();
}
QSqlRecord SqliteDriver::record(const QString &tablename) const
{
QSqlRecord fil;
if (!isOpen() || !dataBase_)
return fil;
QDomDocument doc(tablename);
QDomElement docElem;
QString stream = db_->managerModules()->contentCached(tablename + ".mtd");
if (!FLUtil::domDocumentSetContent(doc, stream)) {
#ifdef FL_DEBUG
qWarning("FLManager : " + QApplication::tr("Error al cargar los metadatos para la tabla %1").arg(tablename));
#endif
return record2(tablename);
}
docElem = doc.documentElement();
FLTableMetaData *mtd = db_->manager()->metadata(&docElem, true);
if (!mtd)
return record2(tablename);
FLTableMetaData::FLFieldMetaDataList *fl = mtd->fieldList();
if (!fl) {
delete mtd;
return record2(tablename);
}
if (fl->isEmpty()) {
delete mtd;
return record2(tablename);
}
QStringList fieldsNames = QStringList::split(",", mtd->fieldsNames());
for (QStringList::Iterator it = fieldsNames.begin(); it != fieldsNames.end(); ++it)
fil.append(QSqlField((*it), FLFieldMetaData::flDecodeType(mtd->fieldType((*it)))));
delete mtd;
return fil;
}
QSqlIndex QSQLite2Driver::primaryIndex(const QString &tblname) const
{
QSqlRecord rec(record(tblname)); // expensive :(
if (!isOpen())
return QSqlIndex();
QSqlQuery q(createResult());
q.setForwardOnly(true);
QString table = tblname;
if (isIdentifierEscaped(table, QSqlDriver::TableName))
table = stripDelimiters(table, QSqlDriver::TableName);
// finrst find a UNIQUE INDEX
q.exec(QLatin1String("PRAGMA index_list('") + table + QLatin1String("');"));
QString indexname;
while(q.next()) {
if (q.value(2).toInt()==1) {
indexname = q.value(1).toString();
break;
}
}
if (indexname.isEmpty())
return QSqlIndex();
q.exec(QLatin1String("PRAGMA index_info('") + indexname + QLatin1String("');"));
QSqlIndex index(table, indexname);
while(q.next()) {
QString name = q.value(2).toString();
QVariant::Type type = QVariant::Invalid;
if (rec.contains(name))
type = rec.field(name).type();
index.append(QSqlField(name, type));
}
return index;
}
QScriptValue SqlFieldClass::newInstance(const QString &fieldName, QVariant::Type type)
{
return newInstance(QSqlField(fieldName, type));
}
