1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
|
/* This file is part of the KDE project
Copyright (C) 2004-2007 Jaroslaw Staniek <[email protected]>
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Library General Public
License as published by the Free Software Foundation; either
version 2 of the License, or (at your option) any later version.
This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Library General Public License for more details.
You should have received a copy of the GNU Library General Public License
along with this library; see the file COPYING.LIB. If not, write to
the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
* Boston, MA 02110-1301, USA.
*/
#ifndef KEXIDB_UTILS_H
#define KEXIDB_UTILS_H
#include <tqvaluelist.h>
#include <tqvariant.h>
#include <kexidb/connection.h>
#include <kexidb/driver.h>
class TQDomNode;
class TQDomElement;
class TQDomDocument;
namespace KexiDB
{
//! for convenience
inline KEXI_DB_EXPORT bool deleteRow(Connection &conn, TableSchema *table,
const TQString &keyname, const TQString &keyval)
{
return table!=0 && conn.executeSQL("DELETE FROM " + table->name() + " WHERE "
+ keyname + "=" + conn.driver()->valueToSQL( Field::Text, TQVariant(keyval) ));
}
inline KEXI_DB_EXPORT bool deleteRow(Connection &conn, const TQString &tableName,
const TQString &keyname, const TQString &keyval)
{
return conn.executeSQL("DELETE FROM " + tableName + " WHERE "
+ keyname + "=" + conn.driver()->valueToSQL( Field::Text, TQVariant(keyval) ));
}
inline KEXI_DB_EXPORT bool deleteRow(Connection &conn, TableSchema *table,
const TQString &keyname, int keyval)
{
return table!=0 && conn.executeSQL("DELETE FROM " + table->name() + " WHERE "
+ keyname + "=" + conn.driver()->valueToSQL( Field::Integer, TQVariant(keyval) ));
}
inline KEXI_DB_EXPORT bool deleteRow(Connection &conn, const TQString &tableName,
const TQString &keyname, int keyval)
{
return conn.executeSQL("DELETE FROM " + tableName + " WHERE "
+ keyname + "=" + conn.driver()->valueToSQL( Field::Integer, TQVariant(keyval) ));
}
/*! Delete row with two generic criterias. */
inline KEXI_DB_EXPORT bool deleteRow(Connection &conn, const TQString &tableName,
const TQString &keyname1, Field::Type keytype1, const TQVariant& keyval1,
const TQString &keyname2, Field::Type keytype2, const TQVariant& keyval2)
{
return conn.executeSQL("DELETE FROM " + tableName + " WHERE "
+ keyname1 + "=" + conn.driver()->valueToSQL( keytype1, keyval1 )
+ " AND " + keyname2 + "=" + conn.driver()->valueToSQL( keytype2, keyval2 ));
}
inline KEXI_DB_EXPORT bool replaceRow(Connection &conn, TableSchema *table,
const TQString &keyname, const TQString &keyval, const TQString &valname, TQVariant val, int ftype)
{
if (!table || !KexiDB::deleteRow(conn, table, keyname, keyval))
return false;
return conn.executeSQL("INSERT INTO " + table->name()
+ " (" + keyname + "," + valname + ") VALUES ("
+ conn.driver()->valueToSQL( Field::Text, TQVariant(keyval) ) + ","
+ conn.driver()->valueToSQL( ftype, val) + ")");
}
typedef TQValueList<uint> TypeGroupList;
/*! \return list of types for type group \a typeGroup. */
KEXI_DB_EXPORT const TypeGroupList typesForGroup(Field::TypeGroup typeGroup);
/*! \return list of i18n'd type names for type group \a typeGroup. */
KEXI_DB_EXPORT TQStringList typeNamesForGroup(Field::TypeGroup typeGroup);
/*! \return list of (not-i18n'd) type names for type group \a typeGroup. */
KEXI_DB_EXPORT TQStringList typeStringsForGroup(Field::TypeGroup typeGroup);
/*! \return default field type for type group \a typeGroup,
for example, Field::Integer for Field::IntegerGroup.
It is used e.g. in KexiAlterTableDialog, to properly fill
'type' property when user selects type group for a field. */
KEXI_DB_EXPORT Field::Type defaultTypeForGroup(Field::TypeGroup typeGroup);
/*! \return a slightly simplified type name for \a field.
For BLOB type it returns i18n'd "Image" string or other, depending on the mime type.
For numbers (either floating-point or integer) it returns i18n'd "Number: string.
For other types it the same string as Field::typeGroupName() is returned. */
//! @todo support names of other BLOB subtypes
KEXI_DB_EXPORT TQString simplifiedTypeName(const Field& field);
/*! \return true if \a v represents an empty (but not null) value.
Values of some types (as for strings) can be both empty and not null. */
inline bool isEmptyValue(Field *f, const TQVariant &v) {
if (f->hasEmptyProperty() && v.toString().isEmpty() && !v.toString().isNull())
return true;
return v.isNull();
}
/*! Sets \a msg to an error message retrieved from object \a obj, and \a details
to details of this error (server message and result number).
Does nothing if \a obj is null or no error occurred.
\a msg and \a details strings are not overwritten.
If \a msg is not empty, \a obj's error message is appended to \a details.
*/
KEXI_DB_EXPORT void getHTMLErrorMesage(Object* obj, TQString& msg, TQString &details);
/*! This methods works like above, but appends both a message and a description
to \a msg. */
KEXI_DB_EXPORT void getHTMLErrorMesage(Object* obj, TQString& msg);
/*! This methods works like above, but works on \a result's members instead. */
KEXI_DB_EXPORT void getHTMLErrorMesage(Object* obj, ResultInfo *result);
/*! Function useful for building WHERE parts of sql statements.
Constructs an sql string like "fielname = value" for specific \a drv driver,
field type \a t, \a fieldName and \a value. If \a value is null, "fieldname is NULL"
string is returned. */
inline KEXI_DB_EXPORT TQString sqlWhere(Driver *drv, Field::Type t,
const TQString fieldName, const TQVariant value)
{
if (value.isNull())
return fieldName + " is NULL";
return fieldName + "=" + drv->valueToSQL( t, value );
}
/*! \return identifier for object \a objName of type \a objType
or 0 if such object does not exist. */
KEXI_DB_EXPORT int idForObjectName( Connection &conn, const TQString& objName, int objType );
/*! Variant class providing a pointer to table or query. */
class KEXI_DB_EXPORT TableOrQuerySchema {
public:
/*! Creates a new TableOrQuerySchema variant object, retrieving table or query schema
using \a conn connection and \a name. If both table and query exists for \a name,
table has priority over query.
You should check whether a query or table has been found by testing
(query() || table()) expression. */
TableOrQuerySchema(Connection *conn, const TQCString& name);
/*! Creates a new TableOrQuerySchema variant object, retrieving table or query schema
using \a conn connection and \a name. If \a table is true, \a name is assumed
to be a table name, otherwise \a name is assumed to be a query name.
You should check whether a query or table has been found by testing
(query() || table()) expression. */
TableOrQuerySchema(Connection *conn, const TQCString& name, bool table);
/*! Creates a new TableOrQuerySchema variant object. \a tableOrQuery must be of
class TableSchema or QuerySchema.
You should check whether a query or table has been found by testing
(query() || table()) expression. */
TableOrQuerySchema(FieldList &tableOrQuery);
/*! Creates a new TableOrQuerySchema variant object, retrieving table or query schema
using \a conn connection and \a id.
You should check whether a query or table has been found by testing
(query() || table()) expression. */
TableOrQuerySchema(Connection *conn, int id);
/*! Creates a new TableOrQuerySchema variant object, keeping a pointer so \a table
object. */
TableOrQuerySchema(TableSchema* table);
/*! Creates a new TableOrQuerySchema variant object, keeping a pointer so \a query
object. */
TableOrQuerySchema(QuerySchema* query);
//! \return a pointer to the query if it's provided
QuerySchema* query() const { return m_query; }
//! \return a pointer to the table if it's provided
TableSchema* table() const { return m_table; }
//! \return name of a query or table
TQCString name() const;
//! \return caption (if present) or name of the table/query
TQString captionOrName() const;
//! \return number of fields
uint fieldCount() const;
//! \return all columns for the table or the query
const QueryColumnInfo::Vector columns(bool unique = false);
/*! \return a field of the table or the query schema for name \a name
or 0 if there is no such field. */
Field* field(const TQString& name);
/*! Like Field* field(const TQString& name);
but returns all information associated with field/column \a name. */
QueryColumnInfo* columnInfo(const TQString& name);
/*! \return connection object, for table or query or 0 if there's no table or query defined. */
Connection* connection() const;
/*! \return String for debugging purposes. */
TQString debugString();
/*! Shows debug information about table or query. */
void debug();
protected:
TQCString m_name; //!< the name is kept here because m_table and m_table can be 0
//! and we still want name() and acptionOrName() work.
TableSchema* m_table;
QuerySchema* m_query;
};
//! @todo perhaps use TQ_ULLONG here?
/*! \return number of rows that can be retrieved after executing \a sql statement
within a connection \a conn. The statement should be of type SELECT.
For SQL data sources it does not fetch any records, only "COUNT(*)"
SQL aggregation is used at the backed.
-1 is returned if error occured. */
int rowCount(Connection &conn, const TQString& sql);
//! @todo perhaps use TQ_ULLONG here?
/*! \return number of rows that can be retrieved from \a tableSchema.
The table must be created or retrieved using a Connection object,
i.e. tableSchema.connection() must not return 0.
For SQL data sources it does not fetch any records, only "COUNT(*)"
SQL aggregation is used at the backed.
-1 is returned if error occurred. */
KEXI_DB_EXPORT int rowCount(const TableSchema& tableSchema);
//! @todo perhaps use TQ_ULLONG here?
/*! Like above but operates on a query schema. */
KEXI_DB_EXPORT int rowCount(QuerySchema& querySchema);
//! @todo perhaps use TQ_ULLONG here?
/*! Like above but operates on a table or query schema variant. */
KEXI_DB_EXPORT int rowCount(TableOrQuerySchema& tableOrQuery);
/*! \return a number of columns that can be retrieved from table or query schema.
In case of query, expanded fields are counted. Can return -1 if \a tableOrQuery
has neither table or query assigned. */
KEXI_DB_EXPORT int fieldCount(TableOrQuerySchema& tableOrQuery);
/*! shows connection test dialog with a progress bar indicating connection testing
(within a second thread).
\a data is used to perform a (temporary) test connection. \a msgHandler is used to display errors.
On successful connecting, a message is displayed. After testing, temporary connection is closed. */
KEXI_DB_EXPORT void connectionTestDialog(TQWidget* tqparent, const ConnectionData& data,
MessageHandler& msgHandler);
/*! Saves connection data \a data into \a map. */
KEXI_DB_EXPORT TQMap<TQString,TQString> toMap( const ConnectionData& data );
/*! Restores connection data \a data from \a map. */
KEXI_DB_EXPORT void fromMap( const TQMap<TQString,TQString>& map, ConnectionData& data );
//! Used in splitToTableAndFieldParts().
enum SplitToTableAndFieldPartsOptions {
FailIfNoTableOrFieldName = 0, //!< default value for splitToTableAndFieldParts()
SetFieldNameIfNoTableName = 1 //!< see splitToTableAndFieldParts()
};
/*! Splits \a string like "table.field" into "table" and "field" parts.
On success, a table name is passed to \a tableName and a field name is passed to \a fieldName.
The function fails if either:
- \a string is empty, or
- \a string does not contain '.' character and \a option is FailIfNoTableOrFieldName
(the default), or
- '.' character is the first of last character of \a string (in this case table name
or field name could become empty what is not allowed).
If \a option is SetFieldNameIfNoTableName and \a string does not contain '.',
\a string is passed to \a fieldName and \a tableName is set to TQString()
without failure.
If function fails, \a tableName and \a fieldName remain unchanged.
\return true on success. */
KEXI_DB_EXPORT bool splitToTableAndFieldParts(const TQString& string,
TQString& tableName, TQString& fieldName,
SplitToTableAndFieldPartsOptions option = FailIfNoTableOrFieldName);
/*! \return true if \a type supports "visibleDecimalPlaces" property. */
KEXI_DB_EXPORT bool supportsVisibleDecimalPlacesProperty(Field::Type type);
/*! \return string constructed by converting \a value.
* If \a decimalPlaces is < 0, all meaningful fractional digits are returned.
* If \a automatically is 0, just integer part is returned.
* If \a automatically is > 0, fractional part should take exactly
N digits: if the fractional part is shorter than N, additional zeros are appended.
For example, "12.345" becomes "12.345000" if N=6.
No rounding is actually performed.
KLocale::formatNumber() and KLocale::decimalSymbol() are used to get locale settings.
@see KexiDB::Field::visibleDecimalPlaces() */
KEXI_DB_EXPORT TQString formatNumberForVisibleDecimalPlaces(double value, int decimalPlaces);
//! \return true if \a propertyName is a builtin field property.
KEXI_DB_EXPORT bool isBuiltinTableFieldProperty( const TQCString& propertyName );
//! \return true if \a propertyName is an extended field property.
KEXI_DB_EXPORT bool isExtendedTableFieldProperty( const TQCString& propertyName );
/*! \return type of field for integer value \a type.
If \a type cannot be casted to KexiDB::Field::Type, KexiDB::Field::InvalidType is returned.
This can be used when type information is deserialized from a string or TQVariant. */
KEXI_DB_EXPORT Field::Type intToFieldType( int type );
/*! Sets property values for \a field. \return true if all the values are valid and allowed.
On failure contents of \a field is undefined.
Properties coming from extended schema are also supported.
This function is used e.g. by AlterTableHandler when property information comes in form of text.
*/
KEXI_DB_EXPORT bool setFieldProperties( Field& field, const TQMap<TQCString, TQVariant>& values );
/*! Sets property value for \a field. \return true if the property has been found and
the value is valid for this property. On failure contents of \a field is undefined.
Properties coming from extended schema are also supported as well as
TQVariant customProperty(const TQString& propertyName) const;
This function is used e.g. by AlterTableHandler when property information comes in form of text.
*/
KEXI_DB_EXPORT bool setFieldProperty(Field& field, const TQCString& propertyName,
const TQVariant& value);
/*! @return property value loaded from a DOM \a node, written in a TQtDesigner-like
notation: <number>int</number> or <bool>bool</bool>, etc. Supported types are
"string", "cstring", "bool", "number". For invalid values null TQVariant is returned.
You can check the validity of the returned value using TQVariant::type(). */
KEXI_DB_EXPORT TQVariant loadPropertyValueFromDom( const TQDomNode& node );
/*! Convenience version of loadPropertyValueFromDom(). \return int value. */
KEXI_DB_EXPORT int loadIntPropertyValueFromDom( const TQDomNode& node, bool* ok );
/*! Convenience version of loadPropertyValueFromDom(). \return TQString value. */
KEXI_DB_EXPORT TQString loadStringPropertyValueFromDom( const TQDomNode& node, bool* ok );
/*! Saves integer element for value \a value to \a doc document within tqparent element
\a tqparentEl. The value will be enclosed in "number" element and "elementName" element.
Example: saveNumberElementToDom(doc, tqparentEl, "height", 15) will create
\code
<height><number>15</number></height>
\endcode
\return the reference to element created with tag elementName. */
KEXI_DB_EXPORT TQDomElement saveNumberElementToDom(TQDomDocument& doc, TQDomElement& tqparentEl,
const TQString& elementName, int value);
/*! Saves boolean element for value \a value to \a doc document within tqparent element
\a tqparentEl. Like saveNumberElementToDom() but creates "bool" tags. True/false values will be
saved as "true"/"false" strings.
\return the reference to element created with tag elementName. */
KEXI_DB_EXPORT TQDomElement saveBooleanElementToDom(TQDomDocument& doc, TQDomElement& tqparentEl,
const TQString& elementName, bool value);
/*! \return an empty value that can be set for a database field of type \a type having
"null" property set. Empty string is returned for text type, 0 for integer
or floating-point types, false for boolean type, empty null byte array for BLOB type.
For date, time and date/time types current date, time, date+time is returned, respectively.
Returns null TQVariant for unsupported values like KexiDB::Field::InvalidType.
This function is efficient (uses a cache) and is heavily used by the AlterTableHandler
for filling new columns. */
KEXI_DB_EXPORT TQVariant emptyValueForType( Field::Type type );
/*! \return a value that can be set for a database field of type \a type having
"notEmpty" property set. It works in a similar way as
@ref TQVariant emptyValueForType( KexiDB::Field::Type type ) with the following differences:
- " " string (a single space) is returned for Text and LongText types
- a byte array with saved "filenew" PNG image (icon) for BLOB type
Returns null TQVariant for unsupported values like KexiDB::Field::InvalidType.
This function is efficient (uses a cache) and is heavily used by the AlterTableHandler
for filling new columns. */
KEXI_DB_EXPORT TQVariant notEmptyValueForType( Field::Type type );
//! Escaping types used in escapeBLOB().
enum BLOBEscapingType {
BLOBEscapeXHex = 1, //!< escaping like X'1FAD', used by sqlite (hex numbers)
BLOBEscape0xHex, //!< escaping like 0x1FAD, used by mysql (hex numbers)
BLOBEscapeHex, //!< escaping like 1FAD without quotes or prefixes
BLOBEscapeOctal //!< escaping like 'zk\\000$x', used by pgsql
//!< (only non-printable characters are escaped using octal numbers)
//!< See http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
};
//! @todo reverse function for BLOBEscapeOctal is available: processBinaryData() in pqxxcursor.cpp - move it here
/*! \return a string containing escaped, printable representation of \a array.
Escaping is controlled by \a type. For empty array TQString() is returned,
so if you want to use this function in an SQL statement, empty arrays should be
detected and "NULL" string should be put instead.
This is helper, used in Driver::escapeBLOB() and KexiDB::variantToString(). */
KEXI_DB_EXPORT TQString escapeBLOB(const TQByteArray& array, BLOBEscapingType type);
/*! \return byte array converted from \a data of length \a length.
\a data is escaped in format used by PostgreSQL's bytea datatype
described at http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
This function is used by PostgreSQL KexiDB and migration drivers. */
KEXI_DB_EXPORT TQByteArray pgsqlByteaToByteArray(const char* data, int length);
/*! \return string value serialized from a variant value \a v.
This functions works like TQVariant::toString() except the case when \a v is of type ByteArray.
In this case KexiDB::escapeBLOB(v.toByteArray(), KexiDB::BLOBEscapeHex) is used.
This function is needed for handling values of random type, for example "defaultValue"
property of table fields can contain value of any type.
Note: the returned string is an unescaped string. */
KEXI_DB_EXPORT TQString variantToString( const TQVariant& v );
/*! \return variant value of type \a type for a string \a s that was previously serialized using
\ref variantToString( const TQVariant& v ) function.
\a ok is set to the result of the operation. */
KEXI_DB_EXPORT TQVariant stringToVariant( const TQString& s, TQVariant::Type type, bool &ok );
/*! \return true if setting default value for \a field field is allowed. Fields with unique
(and thus primary key) flags set do not accept default values.
False is returned aslo if \a field is 0. */
KEXI_DB_EXPORT bool isDefaultValueAllowed( Field* field );
/*! Gets limits for values of type \a type. The result is put into \a minValue and \a maxValue.
Supported types are Byte, ShortInteger, Integer and BigInteger
Results for BigInteger or non-integer types are the same as for Integer due
to limitation of int type.
Signed integers are assumed. */
//! @todo add support for unsigned flag
KEXI_DB_EXPORT void getLimitsForType(Field::Type type, int &minValue, int &maxValue);
/*! Shows debug information about \a rowData row data. */
KEXI_DB_EXPORT void debugRowData(const RowData& rowData);
/*! \return type that's maximum of two integer types \a t1 and \a t2, e.g. Integer for (Byte, Integer).
If one of the types is not of the integer group, Field::InvalidType is returned. */
KEXI_DB_EXPORT Field::Type maximumForIntegerTypes(Field::Type t1, Field::Type t2);
/*! \return TQVariant value converted from null-terminated \a data string.
In case of BLOB type, \a data is not nul lterminated, so passing length is needed. */
inline TQVariant cstringToVariant(const char* data, KexiDB::Field* f, int length = -1)
{
if (!data)
return TQVariant();
// from mo st to least frequently used types:
if (!f || f->isTextType())
return TQString::fromUtf8(data, length);
if (f->isIntegerType()) {
if (f->type()==KexiDB::Field::BigInteger)
return TQVariant( TQString::tqfromLatin1(data, length).toLongLong() );
return TQVariant( TQString::tqfromLatin1(data, length).toInt() );
}
if (f->isFPNumericType())
return TQString::tqfromLatin1(data, length).toDouble();
if (f->type()==KexiDB::Field::BLOB) {
TQByteArray ba;
ba.duplicate(data, length);
return ba;
}
// the default
//! @todo date/time?
TQVariant result(TQString::fromUtf8(data, length));
if (!result.cast( KexiDB::Field::variantType(f->type()) ))
return TQVariant();
return result;
}
}
#endif
|