QModelIndex ListModel::_appendAfter(ListItem* item, const QString& content, App::AppendMode mode) { SqlQuery sql; ListItem* parent = item->parent(); if (!parent) return QModelIndex(); // cannot append after the root int row = mode == App::AppendAfter ? item->row() + 1 : item->row(); sql.prepare("UPDATE list_item SET weight = weight + 1 WHERE list_id = :list AND parent_id = :parent AND weight >= :weight"); sql.bindValue(":list", _listId); sql.bindValue(":parent", parent->id()); sql.bindValue(":weight", row); if (!sql.exec()) return QModelIndex(); sql.prepare("INSERT INTO list_item (list_id, parent_id, weight, content, created_at) VALUES (:list, :parent, :weight, :content, CURRENT_TIMESTAMP)"); sql.bindValue(":list", _listId); sql.bindValue(":parent", parent->id()); sql.bindValue(":weight", row); sql.bindValue(":content", content); if (!sql.exec()) return QModelIndex(); int id = sql.lastInsertId().toInt(); beginInsertRows(indexFromItem(parent), row, row); ListItem* newItem = new ListItem(_listId, id, content); parent->insertChild(row, newItem); if (isNewItemCheckable(item->parent(), row)) newItem->setCheckable(true); endInsertRows(); return indexFromItem(newItem); }
int NGSD::geneToApprovedID(const QByteArray& gene) { //init static SqlQuery q_gene = getQuery(true); static SqlQuery q_prev = getQuery(true); static SqlQuery q_syn = getQuery(true); static bool init = false; if (!init) { q_gene.prepare("SELECT id FROM gene WHERE symbol=:1"); q_prev.prepare("SELECT g.id FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='previous'"); q_syn.prepare("SELECT g.id FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='synonym'"); init = true; } //approved q_gene.bindValue(0, gene); q_gene.exec(); if (q_gene.size()==1) { q_gene.next(); return q_gene.value(0).toInt(); } //previous q_prev.bindValue(0, gene); q_prev.exec(); if (q_prev.size()==1) { q_prev.next(); return q_prev.value(0).toInt(); } else if(q_prev.size()>1) { return -1; } //synonymous q_syn.bindValue(0, gene); q_syn.exec(); if (q_syn.size()==1) { q_syn.next(); return q_syn.value(0).toInt(); } return -1; }
void ListModel::_loadItems(ListItem* parent) { int parentId = parent->id(); SqlQuery sql; sql.prepare("SELECT id, weight, content, is_expanded, is_project, is_milestone, is_highlighted, is_checkable, is_completed, is_cancelled, due_date, priority " "FROM list_item WHERE list_id = :list AND parent_id = :parent " "ORDER BY weight ASC"); sql.bindValue(":list", _listId); sql.bindValue(":parent", parentId); if (!sql.exec()) return; int currRow = 0; while (sql.next()) { int c = -1; int id = sql.value(++c).toInt(); int row = sql.value(++c).toInt(); QString content = sql.value(++c).toString(); bool isExpanded = sql.value(++c).toBool(); bool isProject = sql.value(++c).toBool(); bool isMilestone = sql.value(++c).toBool(); bool isHighlighted = sql.value(++c).toBool(); bool isCheckable = sql.value(++c).toBool(); bool isCompleted = sql.value(++c).toBool(); bool isCancelled = sql.value(++c).toBool(); QDate dueDate = sql.value(++c).toDate(); int priority = sql.value(++c).toInt(); // Fix gap between row in database if (row != currRow) { SqlQuery sql; sql.prepare("UPDATE list_item SET weight = :weight WHERE id = :id"); sql.bindValue(":weight", currRow); sql.bindValue(":id", id); sql.exec(); } ListItem* item = new ListItem(_listId, id, content, isExpanded, isProject, isMilestone, isHighlighted, isCheckable, isCompleted, isCancelled, dueDate, priority); parent->appendChild(item); _loadItems(item); ++currRow; } }
//----------------------------------------------------------------------------- bool PhaseParameterModel::setWeight(Id phaseParameterId, double weight) { SqlQuery updateQuery; updateQuery.prepare("UPDATE PhaseParameter SET Weight = :weight WHERE ID = :id"); updateQuery.bindValue(":weight", weight); updateQuery.bindValue(":id", phaseParameterId); return updateQuery.exec(); }
void NGSD::setClassification(const Variant& variant, const QString& classification, const QString& comment) { QString v_id = variantId(variant); QVariant vc_id = getValue("SELECT id FROM variant_classification WHERE variant_id='" + v_id + "'"); SqlQuery query = getQuery(); //use binding (user input) if (vc_id.isNull()) //insert { query.prepare("INSERT INTO variant_classification (variant_id, class, comment) VALUES ('" + v_id + "',:class,:comment)"); } else //update { query.prepare("UPDATE variant_classification SET class=:class, comment=:comment WHERE id='" + vc_id.toString() + "'"); } query.bindValue(":class", classification); query.bindValue(":comment", comment); query.exec(); }
void NGSD::setValidationStatus(const QString& filename, const Variant& variant, const ValidationInfo& info) { QString s_id = sampleId(filename); QString v_id = variantId(variant); QVariant vv_id = getValue("SELECT id FROM variant_validation WHERE sample_id='" + s_id + "' AND variant_id='" + v_id + "'"); SqlQuery query = getQuery(); //use binding (user input) if (vv_id.isNull()) //insert { QString geno = getValue("SELECT genotype FROM detected_variant WHERE variant_id='" + v_id + "' AND processed_sample_id='" + processedSampleId(filename) + "'", false).toString(); query.prepare("INSERT INTO variant_validation (sample_id, variant_id, genotype, status, type, comment) VALUES ('" + s_id + "','" + v_id + "','" + geno + "',:status,:type,:comment)"); } else //update { query.prepare("UPDATE variant_validation SET status=:status, type=:type, comment=:comment WHERE id='" + vv_id.toString() + "'"); } query.bindValue(":status", info.status); query.bindValue(":type", info.type); query.bindValue(":comment", info.comment); query.exec(); }
QModelIndex ListModel::appendChild(const QModelIndex& parent, int row, QString content) { if (parent.isValid() && parent.column() != 0) return QModelIndex(); ListItem* parentItem = itemFromIndex(parent); if (!parentItem) return QModelIndex(); SqlQuery sql; sql.prepare("UPDATE list_item SET weight = weight + 1 WHERE parent_id = :parent AND weight >= :row"); sql.bindValue(":list", _listId); sql.bindValue(":parent", parentItem->id()); sql.bindValue(":row", row); if (!sql.exec()) return QModelIndex(); sql.prepare("INSERT INTO list_item (list_id, parent_id, weight, content, created_at) VALUES (:list, :parent, :row, :content, CURRENT_TIMESTAMP)"); sql.bindValue(":list", _listId); sql.bindValue(":parent", parentItem->id()); sql.bindValue(":row", row); sql.bindValue(":content", content); if (!sql.exec()) return QModelIndex(); int id = sql.lastInsertId().toInt(); beginInsertRows(parent, row, row); ListItem* newItem = new ListItem(_listId, id, content); if (isNewItemCheckable(parentItem, row)) newItem->setCheckable(true); parentItem->insertChild(row, newItem); endInsertRows(); return indexFromItem(newItem); }
bool ListModel::_removeItem(ListItem* item) { for (int i = item->childCount(); i > 0; --i) _removeItem(item->child(i - 1)); ListItem* parent = item->parent(); int id = item->id(); int row = item->row(); SqlQuery sql; sql.prepare("UPDATE list_item SET weight = weight - 1 WHERE parent_id = :parent AND weight > :weight"); sql.bindValue(":parent", parent->id()); sql.bindValue(":weight", row); if (!sql.exec()) return false; sql.prepare("DELETE FROM list_item WHERE id = :id"); sql.bindValue(":id", id); if (!sql.exec()) return false; parent->removeChild(row); return true; }
//----------------------------------------------------------------------------- void PhaseParameterModel::update() { SqlQuery parameterWeightQuery; parameterWeightQuery.prepare(QString("SELECT %4.%1, %2, %3 FROM %4 INNER JOIN %5 ON %4.%6 = %5.%7 WHERE %8 = :phaseId ORDER BY %3 DESC") .arg("ID", "Name", "Weight", "PhaseParameter" , "Parameter", "ParameterID", "ID" , "PhaseID")); //%! Тут нужно все-таки %4.%1 т.е. Id другой. parameterWeightQuery.addBindValue(mPhaseId); parameterWeightQuery.exec(); setQuery(parameterWeightQuery); setHeaderData(0, Qt::Horizontal, tr("ID")); setHeaderData(1, Qt::Horizontal, tr("Name")); setHeaderData(2, Qt::Horizontal, tr("Weight")); }
QString NGSD::variantId(const Variant& variant, bool throw_if_fails) { SqlQuery query = getQuery(); //use binding user input (safety) query.prepare("SELECT id FROM variant WHERE chr=:chr AND start='"+QString::number(variant.start())+"' AND end='"+QString::number(variant.end())+"' AND ref=:ref AND obs=:obs"); query.bindValue(":chr", variant.chr().str()); query.bindValue(":ref", variant.ref()); query.bindValue(":obs", variant.obs()); query.exec(); if (query.size()==0) { if (throw_if_fails) { THROW(DatabaseException, "Variant " + variant.toString() + " not found in NGSD!"); } else { return "-1"; } } query.next(); return query.value(0).toString(); }
QString NGSD::sampleId(const QString& filename, bool throw_if_fails) { QStringList parts = QFileInfo(filename).baseName().append('_').split('_'); //get sample ID SqlQuery query = getQuery(); //use binding (user input) query.prepare("SELECT id FROM sample WHERE name=:sample"); query.bindValue(":sample", parts[0]); query.exec(); if (query.size()==0) { if(throw_if_fails) { THROW(DatabaseException, "Sample name '" + parts[0] + "' not found in NGSD!"); } else { return ""; } } query.next(); return query.value(0).toString(); }
QString NGSD::processedSampleId(const QString& filename, bool throw_if_fails) { QStringList parts = QFileInfo(filename).baseName().append('_').split('_'); //get sample ID SqlQuery query = getQuery(); //use binding (user input) query.prepare("SELECT ps.id FROM processed_sample ps, sample s WHERE s.name=:sample AND ps.sample_id=s.id AND ps.process_id=:psnum"); query.bindValue(":sample", parts[0]); query.bindValue(":psnum", QString::number(parts[1].toInt())); query.exec(); if (query.size()==0) { if(throw_if_fails) { THROW(DatabaseException, "Processed sample name '" + parts[0] + "_" + parts[1] + "' not found in NGSD!"); } else { return ""; } } query.next(); return query.value(0).toString(); }
QPair<QByteArray, QByteArray> NGSD::geneToApproved(const QByteArray& gene) { //init static SqlQuery q_gene = getQuery(true); static SqlQuery q_prev = getQuery(true); static SqlQuery q_syn = getQuery(true); static bool init = false; if (!init) { q_gene.prepare("SELECT id FROM gene WHERE symbol=:1"); q_prev.prepare("SELECT g.symbol FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='previous'"); q_syn.prepare("SELECT g.symbol FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='synonym'"); init = true; } //approved q_gene.bindValue(0, gene); q_gene.exec(); if (q_gene.size()==1) { q_gene.next(); return qMakePair(gene, QByteArray("KEPT: is approved symbol")); } //previous q_prev.bindValue(0, gene); q_prev.exec(); if (q_prev.size()==1) { q_prev.next(); return qMakePair(q_prev.value(0).toByteArray(), "REPLACED: " + gene + " is a previous symbol"); } else if(q_prev.size()>1) { QByteArray genes; while(q_prev.next()) { if (!genes.isEmpty()) genes.append(", "); genes.append(q_prev.value(0).toByteArray()); } return qMakePair(gene, "ERROR: is a previous symbol of the genes " + genes); } //synonymous q_syn.bindValue(0, gene); q_syn.exec(); if (q_syn.size()==1) { q_syn.next(); return qMakePair(q_syn.value(0).toByteArray(), "REPLACED: " + gene + " is a synonymous symbol"); } else if(q_syn.size()>1) { QByteArray genes; while(q_syn.next()) { if (!genes.isEmpty()) genes.append(", "); genes.append(q_syn.value(0).toByteArray()); } return qMakePair(gene, "ERROR: is a synonymous symbol of the genes " + genes); } return qMakePair(gene, QByteArray("ERROR: is unknown symbol")); }