C++ QSqlDriver::sqlStatement方法代码示例

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

	void deleteRecord(QString table, QSqlRecord record) {
		QSqlDriver *driver = QSqlDatabase::database().driver();
		QString delete_statement = driver->sqlStatement(QSqlDriver::DeleteStatement, table, QSqlRecord(), false);

		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, table, record, false);
		QSqlQuery query(QString("%1 %2").arg(delete_statement, where_statement));
		if (query.lastError().isValid())
			throw new SqlQueryError(query);
	}

	QSqlRecord findOne(QString table, QSqlRecord values, QSqlRecord where) {

		QSqlDriver *driver = QSqlDatabase::database().driver();

		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, table, where, false);
		QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, table, values, false);

		QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));

		qDebug() << "Query:" << query.lastQuery();

		if (query.lastError().isValid())
			throw new SqlQueryError(query);

		if (query.next())
			return query.record();
		else
			return QSqlRecord();
	}

	QList<QSqlRecord> selectQuery(QString table, QSqlRecord values, QSqlRecord where) {
		QList<QSqlRecord> records;

		QSqlDriver *driver = QSqlDatabase::database().driver();


		QString where_statement = driver->sqlStatement(QSqlDriver::WhereStatement, table, where, false);
		QString select_statement = driver->sqlStatement(QSqlDriver::SelectStatement, table, values, false);

		QSqlQuery query(QString("%1 %2").arg(select_statement, where_statement));

		if (query.lastError().isValid())
			throw new SqlQueryError(query);

		while (query.next())
			records.append(query.record());

		return records;
	}

bool SqlTableModel::postRow(int row_no, bool throw_exc)
{
	qfLogFuncFrame() << row_no;

	QF_ASSERT(m_table.isValidRowIndex(row_no),
			  QString("row: %1 is out of range of rows (%2)").arg(row_no).arg(m_table.rowCount()),
			  return false);
	qfu::TableRow &row_ref = m_table.rowRef(row_no);
	bool ret = true;
	if(row_ref.isInsert()) {
		qfDebug() << "\tINSERT";

		//QSet<QString> referenced_foreign_tables = referencedForeignTables();
		//int tbl_cnt = 0;
		qf::core::sql::Connection sql_conn = sqlConnection();
		QSqlDriver *sqldrv = sql_conn.driver();
		const QStringList table_ids = tableIdsSortedAccordingToForeignKeys();
		for(QString table_id : table_ids) {
			qfDebug() << "\ttable:" << table_id;
			QSqlRecord rec;
			int i = -1;
			int serial_ix = -1;
			bool serial_ix_explicitly_set = false;
			int primary_ix = -1;
			//QSqlIndex pri_ix = ti.primaryIndex();
			//bool has_blob_field = false;
			Q_FOREACH(const qf::core::utils::Table::Field &fld, row_ref.fields()) {
				i++;
				if(fld.tableId() != table_id)
					continue;
				//qfInfo() << table_id << "field:" << fld.name() << "is serial:" << fld.isSerial();
				bool is_field_dirty = row_ref.isDirty(i);
				if(fld.isSerial()) {
					/// always include serial fields, an empty line cannot be inserted in other case
					//is_field_dirty = true;
					serial_ix = i;
					serial_ix_explicitly_set = is_field_dirty;
					qfDebug() << "\t serial ix:" << serial_ix;
				}
				if(fld.isPriKey()) {
					/// always include prikey field, an empty line cannot be inserted in other case
					//is_field_dirty = true;
					primary_ix = i;
					qfDebug() << "\t primary ix:" << primary_ix;
				}
				if(!is_field_dirty)
					continue;
				qfDebug() << "\tdirty field:" << fld.name();
				QVariant v = row_ref.value(i);
				if(is_field_dirty) {
					/// null hodnotu nema smysl insertovat, pokud to neni nutne kvuli necemu jinemu
					/// naopak se pri insertu dokumentu z vice join tabulek muze stat, ze se vlozi to not-null fieldu null hodnota
					/// pokud je insert, join radek neexistuje, takze hodnoty jsou null a mysql v takovem pripade v resultsetu nastavi
					/// i not-null field na nullable
					QSqlField new_fld;
					//if(v.isNull() && fld.isSerial())
					//	v = 0;
					new_fld.setValue(v);
					//qfInfo() << "\t\t" << "val type:" << QVariant::typeToName(f.value().type());
					new_fld.setName(fld.shortName());
					rec.append(new_fld);
				}
			}

			qfDebug() << "updating table inserts" << table_id;
			QString qs;
			QString table = sql_conn.fullTableNameToQtDriverTableName(table_id);
			qfs::Query q(sql_conn);
			if(rec.isEmpty()) {
				if(serial_ix >= 0) {
					qs = "INSERT INTO %1 DEFAULT VALUES";
					qs = qs.arg(table);
				}
			}
			else {
				qs = sqldrv->sqlStatement(QSqlDriver::InsertStatement, table, rec, true);
				//qs = fixSerialDefaultValue(qs, serial_ix, rec);
			}
			if(qs.isEmpty())
				continue;
			qfDebug() << "\texecuting prepared query:" << qs;
			bool ok = q.prepare(qs);
			if(!ok) {
				qfError() << "Cannot prepare query:" << qs;
			}
			else {
				for(int i=0; i<rec.count(); i++) {
					QVariant::Type type = rec.field(i).value().type();
					//qfInfo() << "\t" << rec.field(i).name() << "bound type:" << QVariant::typeToName(type);
					qfDebug() << "\t\t" << rec.field(i).name() << "bound type:" << QVariant::typeToName(type) << "value:" << rec.field(i).value().toString().mid(0, 100);
					q.addBindValue(rec.field(i).value());
				}
			}
			ok = q.exec();
			if(ok) {
				qfDebug() << "\tnum rows affected:" << q.numRowsAffected();
				int num_rows_affected = q.numRowsAffected();
				//setNumRowsAffected(q.numRowsAffected());
				QF_ASSERT(num_rows_affected == 1,
						  tr("numRowsAffected() = %1, should be 1\n%2").arg(num_rows_affected).arg(qs),
//.........这里部分代码省略.........