C++ SqlQuery类代码示例

QStringList NGSD::getEnum(QString table, QString column)
{
	//check cache
	static QMap<QString, QStringList> cache;
	QString hash = table+"."+column;
	if (cache.contains(hash))
	{
		return cache.value(hash);
	}

	//DB query
	SqlQuery q = getQuery();
	q.exec("DESCRIBE "+table+" "+column);
	while (q.next())
	{
		QString type = q.value(1).toString();
		type.replace("'", "");
		type.replace("enum(", "");
		type.replace(")", "");
		cache[hash] = type.split(",");
		return cache[hash];
	}

	THROW(ProgrammingException, "Could not determine enum values of column '"+column+"' in table '"+table+"'!");
}

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

bool SyncJournalDb::sqlFail( const QString& log, const SqlQuery& query )
{
    commitTransaction();
    qWarning() << "SQL Error" << log << query.error();
    Q_ASSERT(!"SQL ERROR");
    _db.close();
    return false;
}

QVector<double> NGSD::getQCValues(const QString& accession, const QString& filename)
{
	//get processing system ID
	QString sys_id = getValue("SELECT processing_system_id FROM processed_sample WHERE id='" + processedSampleId(filename) + "'").toString();

	//get QC id
	QString qc_id = getValue("SELECT id FROM qc_terms WHERE qcml_id='" + accession + "'").toString();

	//get QC data
	SqlQuery q = getQuery();
	q.exec("SELECT nm.value FROM processed_sample_qc as nm, processed_sample as ps WHERE ps.processing_system_id='" + sys_id + "' AND nm.qc_terms_id='" + qc_id + "' AND nm.processed_sample_id=ps.id ");

	//fill output datastructure
	QVector<double> output;
	while(q.next())
	{
		bool ok = false;
		double value = q.value(0).toDouble(&ok);
		if (ok) output.append(value);
	}

	return output;
}

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

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

QCCollection NGSD::getQCData(const QString& filename)
{
	QString ps_id = processedSampleId(filename, false);

	//get QC data
	SqlQuery q = getQuery();
	q.exec("SELECT n.name, nm.value, n.description, n.qcml_id FROM processed_sample_qc as nm, qc_terms as n WHERE nm.processed_sample_id='" + ps_id + "' AND nm.qc_terms_id=n.id");
	QCCollection output;
	while(q.next())
	{
		output.insert(QCValue(q.value(0).toString(), q.value(1).toString(), q.value(2).toString(), q.value(3).toString()));
	}

	//get KASP data
	SqlQuery q2 = getQuery();
	q2.exec("SELECT random_error_prob FROM kasp_status WHERE processed_sample_id='" + ps_id + "'");
	QString value = "n/a";
	if (q2.size()>0)
	{
		q2.next();
		float numeric_value = 100.0 * q2.value(0).toFloat();
		if (numeric_value>100.0) //special case: random_error_prob>100%
		{
			value = "<font color=orange>KASP not performed (see NGSD)</font>";
		}
		else if (numeric_value>1.0) //random_error_prob>1% => warn
		{
			value = "<font color=red>"+QString::number(numeric_value)+"%</font>";
		}
		else
		{
			value = QString::number(numeric_value)+"%";
		}
	}
	output.insert(QCValue("kasp", value));

	return output;
}

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

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

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

    void get() {
        this->response->body.append(blog::layout::header("Gorilla Labs"));
        std::string content;
        
        // load the library
        bool loadableMod = true;
        void* test = dlopen("./libtest.so", RTLD_LAZY);
        if (!test) {
            std::cout << "Cannot load library: " << dlerror() << std::endl;
            loadableMod = false;
            // reset errors
            dlerror();
        }
        
        create_t* create_controller;
        destroy_t* destroy_controller;
        const char* dlsym_error;
        if (loadableMod) {
            // load the symbols
            create_controller = (create_t*) dlsym(test, "create");
            dlsym_error = dlerror();
            if (dlsym_error) {
                std::cout << "Cannot load symbol create: " << dlsym_error << std::endl;;
                loadableMod = false;
            }
        }
            
        if (loadableMod) {
            destroy_controller = (destroy_t*) dlsym(test, "destroy");
            dlsym_error = dlerror();
            if (dlsym_error) {
                std::cout << "Cannot load symbol destroy: " << dlsym_error << std::endl;
                loadableMod = false;
            }
        }
        
        // create an instance of the class
        if (loadableMod) {
            WebController* cont = create_controller();
            cont->get();
            destroy_controller(cont);
            dlclose(test);
        }
        
        sqlite3* db;
        int failed = sqlite3_open("utf.sql", &db);

        if (failed == 0) {
            SqlQuery stmt = SqlQuery(&db);
            /*
            stmt.exec("CREATE TABLE utf_table (\
                col1 INTEGER NOT NULL,\
                col2 CHAR(25) NULL,\
                col3 VARCHAR(25) NULL,\
                col4 NUMERIC NULL,\
                col5 TEXT(25) NULL,\
                PRIMARY KEY (col1),\
                UNIQUE (col2)\
            )");
            
            sqlite3_exec(db, "BEGIN", 0, 0, 0);
            sqlite3_exec(db, "INSERT INTO utf_table (col3) VALUES('[ˈruskʲɪj jɪˈzɨk]')", 0, 0, 0);
            sqlite3_exec(db, "COMMIT", 0, 0, 0);
            */
            stmt.query("SELECT col1, col3 FROM utf_table WHERE col3 = ?");
            std::string title = "[ˈruskʲɪj jɪˈzɨk]";
            stmt.bind(this->request->get("title"));
            stmt.fetchAll();
            if (!stmt.failed) {
                int row_size = stmt.rows.size();
                for (int i = 0; i < row_size; i++) {
                    SqlRow row = stmt.rows[i];
                    content.append("found id:" + row.get("col1") + ": " + row.get("col3") + "<br />");
                }
            } else {
                content.append("something bad happened");
            }
            sqlite3_close(db);
        } else {
            content.append("failed to open db<br />");
        }
        content.append("<form method='post'><input name='title' /></form><br />" + this->request->get("title"));
        this->response->body.append(content);
        this->response->body.append(blog::layout::footer());
    }

void NGSD::annotate(VariantList& variants, QString filename)
{
	initProgress("NGSD annotation", true);

	//get sample ids
	QString s_id = sampleId(filename, false);
	QString ps_id = processedSampleId(filename, false);
	QString sys_id = getValue("SELECT processing_system_id FROM processed_sample WHERE id='" + processedSampleId(filename, false) + "'").toString();

	//check if we could determine the sample
	bool found_in_db = true;
	if (s_id=="" || ps_id=="" || sys_id=="")
	{
		Log::warn("Could not find processed sample in NGSD by name '" + filename + "'. Annotation will be incomplete because processing system could not be determined!");
		found_in_db = false;
	}

	//get sample ids that have processed samples with the same processing system (not same sample, variants imported, same processing system, good quality of sample, not tumor)
	QSet<int> sys_sample_ids;
	SqlQuery tmp = getQuery();
	tmp.exec("SELECT DISTINCT s.id FROM processed_sample as ps, sample s WHERE ps.processing_system_id='" + sys_id + "' AND ps.sample_id=s.id AND s.tumor='0' AND s.quality='good' AND s.id!='" + s_id + "' AND (SELECT count(id) FROM detected_variant as dv WHERE dv.processed_sample_id = ps.id)>0");
	while(tmp.next())
	{
		sys_sample_ids.insert(tmp.value(0).toInt());
	}

	//remove all NGSD-specific columns
	QList<VariantAnnotationHeader> headers = variants.annotations();
	foreach(const VariantAnnotationHeader& header, headers)
	{
		if (header.name().startsWith("ihdb_"))
		{
			removeColumnIfPresent(variants, header.name(), true);
		}
	}
	removeColumnIfPresent(variants, "classification", true);
	removeColumnIfPresent(variants, "classification_comment", true);
	removeColumnIfPresent(variants, "validated", true);
	removeColumnIfPresent(variants, "comment", true);

	//get required column indices
	QString num_samples = QString::number(sys_sample_ids.count());
	int ihdb_hom_idx = addColumn(variants, "ihdb_hom", "Homozygous variant counts in NGSD for the same processing system (" + num_samples + " samples).");
	int ihdb_het_idx = addColumn(variants, "ihdb_het", "Heterozyous variant counts in NGSD for the same processing system (" + num_samples + " samples).");
	int ihdb_wt_idx  = addColumn(variants, "ihdb_wt", "Wildtype variant counts in NGSD for the same processing system (" + num_samples + " samples).");
	int ihdb_all_hom_idx = addColumn(variants, "ihdb_allsys_hom", "Homozygous variant counts in NGSD independent of the processing system.");
	int ihdb_all_het_idx =  addColumn(variants, "ihdb_allsys_het", "Heterozygous variant counts in NGSD independent of the processing system.");
	int class_idx = addColumn(variants, "classification", "Classification from the NGSD.");
	int clacom_idx = addColumn(variants, "classification_comment", "Classification comment from the NGSD.");
	int valid_idx = addColumn(variants, "validated", "Validation information from the NGSD. Validation results of other samples are listed in brackets!");
	if (variants.annotationIndexByName("comment", true, false)==-1) addColumn(variants, "comment", "Comments from the NGSD. Comments of other samples are listed in brackets!");
	int comment_idx = variants.annotationIndexByName("comment", true, false);

	//(re-)annotate the variants
	SqlQuery query = getQuery();
	for (int i=0; i<variants.count(); ++i)
	{
		//QTime timer;
		//timer.start();

		//variant id
		Variant& v = variants[i];
		QByteArray v_id = variantId(v, false).toLatin1();

		//variant classification
		QVariant classification = getValue("SELECT class FROM variant_classification WHERE variant_id='" + v_id + "'", true);
		if (!classification.isNull())
		{
			v.annotations()[class_idx] = classification.toByteArray().replace("n/a", "");
			v.annotations()[clacom_idx] = getValue("SELECT comment FROM variant_classification WHERE variant_id='" + v_id + "'", true).toByteArray().replace("\n", " ").replace("\t", " ");
		}
		//int t_v = timer.elapsed();
		//timer.restart();

		//detected variant infos
		int dv_id = -1;
		QByteArray comment = "";
		if (found_in_db)
		{
			query.exec("SELECT id, comment FROM detected_variant WHERE processed_sample_id='" + ps_id + "' AND variant_id='" + v_id + "'");
			if (query.size()==1)
			{
				query.next();
				dv_id = query.value(0).toInt();
				comment = query.value(1).toByteArray();
			}
		}

		//validation info
		int vv_id = -1;
		QByteArray val_status = "";
		if (found_in_db)
		{
			query.exec("SELECT id, status FROM variant_validation WHERE sample_id='" + s_id + "' AND variant_id='" + v_id + "'");
			if (query.size()==1)
			{
				query.next();
				vv_id = query.value(0).toInt();
				val_status = query.value(1).toByteArray().replace("n/a", "");
			}
//.........这里部分代码省略.........