BpsSqlValues Class Reference

Convenience class to help composing select, insert and update statements. More...

#include <bpssqlvalues.h>

Public Types
enum	Condition { CondEqualAnd , CondUnequalAnd , CondEqualOr , CondUnequalOr }
	Conditon pair building modes. More...

Public Member Functions
	BpsSqlValues ()
	Default constructor for an empty record.
	BpsSqlValues (const BpsSqlValues &aOther)
	Copy constructor. More...
	BpsSqlValues (const QSqlRecord &aRec)
	Create object from a sql record. More...
	BpsSqlValues (const QVariantMap &aMap)
	Create object from a variant map. More...
void	clear ()
	Clear the internal map.
QString	columns (const QString &aPrefix=QString()) const
QString	condPairs (Condition aCondition=CondEqualAnd, const QString &aPrefix=QString()) const
	Get list of pairs as used for select conditions. More...
QString	condPairs (const QString &aSeparator, const QString &aPrefix=QString()) const
	Get list of pairs as used for select conditions (depreciated). More...
void	insert (const BpsSqlValues &aOther)
	Inserts (merges) all key/value pairs from the other BpsSqlValues object. More...
void	insert (const QString &aKey, const QVariant &aValue)
	Insert a key/value pair. More...
bool	isEmpty () const
QStringList	keys (const QString &aPrefix=QString()) const
QVariantMap	map () const
	operator QVariant () const
BpsSqlValues &	operator= (const BpsSqlValues &aOther)
	Assign other sql values to this object. More...
BpsSqlValues &	operator= (const QSqlRecord &aOther)
	Assign values of a sql record to this object. More...
BpsSqlValues &	operator= (const QVariantMap &aOther)
	Assign other variant map to this object. More...
QVariant &	operator[] (const QString &aName)
	Get the value associated with the key aName as a modifiable reference. More...
const QVariant	operator[] (const QString &aName) const
	Get the named columns value. More...
QString	pairs (const QString &aPrefix=QString()) const
	Get list of pairs as used for update statements. More...
int	remove (const QString &aKey)
	Removes all the items that have the key aKey from the map. More...
void	setMap (const QVariantMap &aMap)
	Set the internal map. More...
QString	tags () const
QVariant	value (const QString &aName) const
	Get the named columns value. More...
QVariantList	values () const

Detailed Description

Convenience class to help composing select, insert and update statements.

Empty strings are handled like NULL in context of inserts and updates. In select conditions the database column is compared to both, NULL or '', if the string value is empty. This special handling is necessary because for Oracle NULL and '' are the same, however for PostgreSQL they are not.

The special type BpsTypeLocaltimestamp may be used for localtimestamp in the query.

BpsSqlValues vals;
vals[bStr("c_x")] = 123;           // non-null example value
vals[bStr("c_y")] = QVariant();    // null example value
vals[bStr("c_z")] = bStr("");      // empty strings also handled like NULL
vals[bStr("c_w")] = BpsTypeLocaltimestamp();    // localtimestamp
 
// select condition example
q.prepare(
    bStr("select c_foo from t_table where %1").arg(vals.condPairs())
    // select c_foo from t_table where c_x=? and c_y is null and (c_z is null or c_z='') and c_w=localtimestamp
);
q.execute(vals.values());  // 123
 
// update example
q.prepare(
    bStr("update t_table set %1 where c_key=?").arg(vals.pairs())
    // update t_table set c_x=?, c_y=null, c_z=null, c_w=localtimestamp where c_key=?
);
q.execute(vals.values() << 456); // 123 456
 
// insert example
q.prepare(
    bStr("insert into t_table (%1) values (%2)")
        .arg(vals.columns())
        .arg(vals.tags())
    // insert into t_table (c_x, c_y, c_z, c_w) values (?, null, null, localtimestamp)
);
q.execute(vals.values()); // 123

Member Enumeration Documentation

Condition

enum BpsSqlValues::Condition

Conditon pair building modes.

Enumerator
CondEqualAnd	Compare if equal and combine the pairs with AND: c_aaa=? and c_bbb=? ...
CondUnequalAnd	Compare if not equal and combine the pairs with AND: c_aaa<>? and c_bbb<>? ...
CondEqualOr	Compare if equal and combine the pairs with OR: c_aaa=? or c_bbb=? ...
CondUnequalOr	Compare if not equal and combine the pairs with OR: c_aaa<>? or c_bbb<>? ...

Constructor & Destructor Documentation

BpsSqlValues() [1/3]

BpsSqlValues::BpsSqlValues

(

const BpsSqlValues &

aOther

)

Copy constructor.

Parameters

aOther

The other object to clone

BpsSqlValues() [2/3]

BpsSqlValues::BpsSqlValues

(

const QVariantMap &

aMap

)

Create object from a variant map.

Parameters

aMap	The variant map

BpsSqlValues() [3/3]

BpsSqlValues::BpsSqlValues

(

const QSqlRecord &

aRec

)

Create object from a sql record.

Parameters

aRec	The sql record

Member Function Documentation

columns()

QString BpsSqlValues::columns

(

const QString &

aPrefix = QString()

)

const

Parameters

aPrefix

A prefix to put before each column name, for example "x." or "y_".

Returns

All column names as comma separated list.

condPairs() [1/2]

QString BpsSqlValues::condPairs	(	Condition	aCondition = CondEqualAnd,
		const QString &	aPrefix = QString()
	)		const

Get list of pairs as used for select conditions.

This is an enhanced version with more flexibility replaceing the depreciated old version.

Takes care of NULL values when necessary, so two NULL values compare as "equal". Also handles the quirk that an empty string is handeled as NULL by some databases.

Parameters

aCondition	Condition for comparing and combining the pairs.
aPrefix	A prefix to put before each column name, for example "x." or "y_".

Returns

Pairs of "column=?", "column is null" or "(column is null or column='')" respectively as separated list.

condPairs() [2/2]

QString BpsSqlValues::condPairs	(	const QString &	aSeparator,
		const QString &	aPrefix = QString()
	)		const

Get list of pairs as used for select conditions (depreciated).

Parameters

aSeparator	Separator to be used between the pairs.
aPrefix	A prefix to put before each column name, for example "x." or "y_".

Returns

Pairs of "column=?", "column is null" or "(column is null or column='')" respectively as separated list.

insert() [1/2]

void BpsSqlValues::insert

(

const BpsSqlValues &

aOther

)

Inserts (merges) all key/value pairs from the other BpsSqlValues object.

Parameters

aOther

The other BpsSqlValues object to insert.

insert() [2/2]

void BpsSqlValues::insert	(	const QString &	aKey,
		const QVariant &	aValue
	)

Insert a key/value pair.

Parameters

aKey	The key to insert
aValue	The value to insert

isEmpty()

bool BpsSqlValues::isEmpty

(

)

const

Returns

True if there are no columns.

keys()

QStringList BpsSqlValues::keys

(

const QString &

aPrefix = QString()

)

const

Parameters

aPrefix

A prefix to put before each column name, for example "x." or "y_".

Returns

List of all keys (= column names).

map()

QVariantMap BpsSqlValues::map

(

)

const

Returns

The internal variant map.

operator QVariant()

BpsSqlValues::operator QVariant ( ) const

inline

Returns

The values as QVariant.

operator=() [1/3]

BpsSqlValues& BpsSqlValues::operator=

(

const BpsSqlValues &

aOther

)

Assign other sql values to this object.

Parameters

aOther

The other values to copy.

Returns

Reference to the current object.

operator=() [2/3]

BpsSqlValues& BpsSqlValues::operator=

(

const QSqlRecord &

aOther

)

Assign values of a sql record to this object.

Parameters

aOther

The other sql record to copy from.

Returns

Reference to the current object.

operator=() [3/3]

BpsSqlValues& BpsSqlValues::operator=

(

const QVariantMap &

aOther

)

Assign other variant map to this object.

Parameters

aOther

The other variant map to copy from.

Returns

Reference to the current object.

operator[]() [1/2]

QVariant& BpsSqlValues::operator[]

(

const QString &

aName

)

Get the value associated with the key aName as a modifiable reference.

If the values contain no such item, the function inserts a default-constructed value and returns a reference to it.

See also

insert(), value()

Parameters

aName

Name of the column to get the value for.

Returns

The value associated with the key key as a modifiable reference.

operator[]() [2/2]

const QVariant BpsSqlValues::operator[]

(

const QString &

aName

)

const

Get the named columns value.

Same as value().

Parameters

aName

Name of the column to get the value for.

Returns

The value, or QVariant() in case no such column name is found.

pairs()

QString BpsSqlValues::pairs

(

const QString &

aPrefix = QString()

)

const

Get list of pairs as used for update statements.

Parameters

aPrefix

A prefix to put before each column name, for example "x." or "y_".

Returns

Pairs of "column=?" or "column=null" respectively as comma separated list.

remove()

int BpsSqlValues::remove

(

const QString &

aKey

)

Removes all the items that have the key aKey from the map.

Parameters

aKey	The key of the pair to remove.

Returns

Number of items removed which is usually 1 but will be 0 if the key isn't in the map.

setMap()

void BpsSqlValues::setMap

(

const QVariantMap &

aMap

)

Set the internal map.

Parameters

aMap	The map to copy.

tags()

QString BpsSqlValues::tags

(

)

const

Returns

The value placeholders or nulls as comma separated list.

value()

QVariant BpsSqlValues::value

(

const QString &

aName

)

const

Get the named columns value.

Parameters

aName

Name of the column to get the value for.

Returns

The value, or QVariant() in case no such column name is found.

values()

QVariantList BpsSqlValues::values

(

)

const

Returns

List of all non-null values.

---

The documentation for this class was generated from the following file:

• bpssqlvalues.h