C++ SqlQuery::prepare方法代码示例

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"));
}