1
/* This file is part of the KDE project
2
Copyright (C) 2006-2007 Jarosław Staniek <staniek@kde.org>
4
This library is free software; you can redistribute it and/or
5
modify it under the terms of the GNU Library General Public
6
License as published by the Free Software Foundation; either
7
version 2 of the License, or (at your option) any later version.
9
This library is distributed in the hope that it will be useful,
10
but WITHOUT ANY WARRANTY; without even the implied warranty of
11
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12
Library General Public License for more details.
14
You should have received a copy of the GNU Library General Public License
15
along with this library; see the file COPYING.LIB. If not, write to
16
the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
17
* Boston, MA 02110-1301, USA.
20
#ifndef KEXIDB_ALTER_H
21
#define KEXIDB_ALTER_H
23
#include "connection.h"
29
#include <kexiutils/utils.h>
35
//! @short A tool for handling altering database table schema.
36
/*! In relational (and other) databases, table schema altering is not an easy task.
37
It may be considered as easy if there is no data that user wants to keep while
38
the table schema is altered. Otherwise, if the table is alredy filled with data,
39
there could be no easy algorithm like:
40
1. Drop existing table
41
2. Create new one with altered schema.
43
Instead, more complex algorithm is needed. To perform the table schema alteration,
44
a list of well defined atomic operations is used as a "recipe".
46
1. Look at the current data, and:
47
1.1. analyze what values will be removed (in case of impossible conversion
48
or table field removal);
49
1.2. analyze what values can be converted (e.g. from numeric types to text), and so on.
50
2. Optimize the atomic actions knowing that sometimes a compilation of one action
51
and another that's opposite to the first means "do nothing". The optimization
52
is a simulating of actions' execution.
53
For example, when both action A="change field name from 'city' to 'town'"
54
and action B="change field name from 'town' to 'city'" is specified, the compilation
55
of the actions means "change field name from 'city' to 'city'", what is a NULL action.
56
On the other hand, we need to execute all the actions on the destination table
57
in proper order, and not just drop them. For the mentioned example, between actions
58
A and B there can be an action like C="change the type of field 'city' to LongText".
59
If A and B were simply removed, C would become invalid (there is no 'city' field).
60
3. Ask user whether she agrees with the results of analysis mentioned in 1.
61
3.2. Additionally, it may be possible to get some hints from the user, as humans usually
62
know more about logic behind the altered table schema than any machine.
63
If the user provided hints about the altering, apply them to the actions list.
64
4. Create (empty) destination table schema with temporary name, using
65
the information collected so far.
66
5. Copy the data from the source to destionation table. Convert values,
67
move them between fields, using the information collected.
68
6. Remove the source table.
69
7. Rename the destination table to the name previously assigned for the source table.
72
* The actions 4 to 7 should be performed within a database transaction.
73
* [todo] We want to take care about database relationships as well.
74
For example, is a table field is removed, relationships related to this field should
75
be also removed (similar rules as in the Query Designer).
76
* Especially, care about primary keys and uniquess (indices). Recreate them when needed.
77
The problem could be if such analysis may require to fetch the entire table data
78
to the client side. Use "SELECT INTO" statments if possible to avoid such a treat.
80
The AlterTableHandler is used in Kexi's Table Designer.
81
Already opened Connection object is needed.
85
Connection *conn = ...
87
// add some actions (in reality this is performed by tracking user's actions)
88
// Actions 1, 2 will require physical table altering PhysicalAltering
89
// Action 3 will only require changes in kexi__fields
90
// Action 4 will only require changes in extended table schema written in kexi__objectdata
91
AlterTable::ActionList list;
93
// 1. rename the "city" field to "town"
94
list << new ChangeFieldPropertyAction("city", "name", "town")
96
// 2. change type of "town" field to "LongText"
97
<< new ChangeFieldPropertyAction("town", "type", "LongText")
99
// 3. set caption of "town" field to "Town"
100
<< new ChangeFieldPropertyAction("town", "caption", "Town")
102
// 4. set visible decimal places to 4 for "cost" field
103
<< new ChangeFieldPropertyAction("cost", "visibleDecimalPlaces", 4)
105
AlterTableHandler::execute( *conn );
111
class KEXI_DB_EXPORT AlterTableHandler : public Object
114
class ChangeFieldPropertyAction;
115
class RemoveFieldAction;
116
class InsertFieldAction;
117
class MoveFieldPositionAction;
119
//! Defines flags for possible altering requirements; can be combined.
120
enum AlteringRequirements {
121
/*! Physical table altering is required; e.g. ALTER TABLE ADD COLUMN. */
122
PhysicalAlteringRequired = 1,
124
/*! Data conversion is required; e.g. converting integer
125
values to string after changing column type from integer to text. */
126
DataConversionRequired = 2,
128
/* Changes to the main table schema (in kexi__fields) required,
129
this does not require physical changes for the table;
130
e.g. changing value of the "caption" or "description" property. */
131
MainSchemaAlteringRequired = 4,
133
/* Only changes to extended table schema required,
134
this does not require physical changes for the table;
135
e.g. changing value of the "visibleDecimalPlaces" property
136
or any of the custom properties. */
137
ExtendedSchemaAlteringRequired = 8,
139
/*! Convenience flag, changes to the main or extended schema is required. */
140
SchemaAlteringRequired = ExtendedSchemaAlteringRequired | MainSchemaAlteringRequired
144
//! For collecting actions related to a single field
145
typedef KexiUtils::AutodeletedHash<QByteArray, ActionBase*> ActionDict;
146
typedef KexiUtils::AutodeletedHash<int, ActionDict*> ActionDictDict; //!< for collecting groups of actions by field UID
147
typedef QHash<QByteArray, ActionBase*>::Iterator ActionDictIterator;
148
typedef QHash<QByteArray, ActionBase*>::ConstIterator ActionDictConstIterator;
149
typedef QHash<int, ActionDict*>::Iterator ActionDictDictIterator;
150
typedef QHash<int, ActionDict*>::ConstIterator ActionDictDictConstIterator;
151
typedef QVector<ActionBase*> ActionsVector; //!< for collecting actions related to a single field
153
//! Defines a type for action list.
154
typedef QList<ActionBase*> ActionList;
156
//! Defines a type for action list's iterator.
157
typedef QList<ActionBase*>::ConstIterator ActionListIterator;
159
//! Abstract base class used for implementing all the AlterTable actions.
160
class KEXI_DB_EXPORT ActionBase
163
ActionBase(bool null = false);
164
virtual ~ActionBase();
166
ChangeFieldPropertyAction& toChangeFieldPropertyAction();
167
RemoveFieldAction& toRemoveFieldAction();
168
InsertFieldAction& toInsertFieldAction();
169
MoveFieldPositionAction& toMoveFieldPositionAction();
171
//! \return true if the action is NULL; used in the Table Designer
172
//! for temporarily collecting actions that have no effect at all.
173
bool isNull() const {
177
//! Controls debug options for actions. Used in debugString() and debug().
181
DebugOptions() : showUID(true), showFieldDebug(false) {}
183
//! true if UID should be added to the action debug string (the default)
186
//! true if the field associated with the action (if exists) should
187
//! be appended to the debug string (default is false)
191
virtual QString debugString(const DebugOptions& debugOptions = DebugOptions()) {
192
Q_UNUSED(debugOptions); return "ActionBase";
194
void debug(const DebugOptions& debugOptions = DebugOptions()) {
195
KexiDBDbg << debugString(debugOptions)
196
<< " (req = " << alteringRequirements() << ")";
200
//! Sets requirements for altering; used internally by AlterTableHandler object
201
void setAlteringRequirements(int alteringRequirements) {
202
m_alteringRequirements = alteringRequirements;
205
int alteringRequirements() const {
206
return m_alteringRequirements;
209
virtual void updateAlteringRequirements() {}
211
/*! Simplifies \a fieldActions dictionary. If this action has to be inserted
212
Into the dictionary, an ActionDict is created first and then a copy of this action
213
is inserted into it. */
214
virtual void simplifyActions(ActionDictDict &fieldActions) {
215
Q_UNUSED(fieldActions);
218
/*! After calling simplifyActions() for each action,
219
shouldBeRemoved() is called for them as an additional step.
220
This is used for ChangeFieldPropertyAction items so actions
221
that do not change property values are removed. */
222
virtual bool shouldBeRemoved(ActionDictDict &fieldActions) {
223
Q_UNUSED(fieldActions); return false;
226
virtual tristate updateTableSchema(TableSchema &table, Field* field,
227
QHash<QString, QString>& fieldHash) {
228
Q_UNUSED(table); Q_UNUSED(field); Q_UNUSED(fieldHash); return true;
232
//! Performs physical execution of this action.
233
virtual tristate execute(Connection& /*conn*/, TableSchema& /*table*/) {
237
//! requirements for altering; used internally by AlterTableHandler object
238
int m_alteringRequirements;
240
//! @internal used for "simplify" algorithm
245
friend class AlterTableHandler;
248
//! Abstract base class used for implementing table field-related actions.
249
class KEXI_DB_EXPORT FieldActionBase : public ActionBase
252
FieldActionBase(const QString& fieldName, int uid);
253
FieldActionBase(bool);
254
virtual ~FieldActionBase();
256
//! \return field name for this action
257
QString fieldName() const {
261
/*! \return field's unique identifier
262
This id is needed because in the meantime there can be more than one
263
field sharing the same name, so we need to identify them unambiguously.
264
After the (valid) altering is completed all the names will be unique.
266
Example scenario when user exchanged the field names:
267
1. At the beginning: [field A], [field B]
268
2. Rename the 1st field to B: [field B], [field B]
269
3. Rename the 2nd field to A: [field B], [field A] */
274
//! Sets field name for this action
275
void setFieldName(const QString& fieldName) {
276
m_fieldName = fieldName;
281
//! field's unique identifier, @see uid()
287
/*! Defines an action for changing a single property value of a table field.
288
Supported properties are currently:
289
"name", "type", "caption", "description", "unsigned", "length", "precision",
290
"width", "defaultValue", "primaryKey", "unique", "notNull", "allowEmpty",
291
"autoIncrement", "indexed", "visibleDecimalPlaces"
295
class KEXI_DB_EXPORT ChangeFieldPropertyAction : public FieldActionBase
298
ChangeFieldPropertyAction(const QString& fieldName,
299
const QString& propertyName, const QVariant& newValue, int uid);
300
//! @internal, used for constructing null action
301
ChangeFieldPropertyAction(bool null);
302
virtual ~ChangeFieldPropertyAction();
304
QString propertyName() const {
305
return m_propertyName;
307
QVariant newValue() const {
310
virtual QString debugString(const DebugOptions& debugOptions = DebugOptions());
312
virtual void simplifyActions(ActionDictDict &fieldActions);
314
virtual bool shouldBeRemoved(ActionDictDict &fieldActions);
316
virtual tristate updateTableSchema(TableSchema &table, Field* field,
317
QHash<QString, QString>& fieldHash);
320
virtual void updateAlteringRequirements();
322
//! Performs physical execution of this action.
323
virtual tristate execute(Connection &conn, TableSchema &table);
325
QString m_propertyName;
329
//! Defines an action for removing a single table field.
330
class KEXI_DB_EXPORT RemoveFieldAction : public FieldActionBase
333
RemoveFieldAction(const QString& fieldName, int uid);
334
RemoveFieldAction(bool);
335
virtual ~RemoveFieldAction();
337
virtual QString debugString(const DebugOptions& debugOptions = DebugOptions());
339
virtual void simplifyActions(ActionDictDict &fieldActions);
341
virtual tristate updateTableSchema(TableSchema &table, Field* field,
342
QHash<QString, QString>& fieldHash);
345
virtual void updateAlteringRequirements();
347
//! Performs physical execution of this action.
348
virtual tristate execute(Connection &conn, TableSchema &table);
351
//! Defines an action for inserting a single table field.
352
class KEXI_DB_EXPORT InsertFieldAction : public FieldActionBase
355
InsertFieldAction(int fieldIndex, KexiDB::Field *newField, int uid);
357
InsertFieldAction(const InsertFieldAction& action);
358
InsertFieldAction(bool);
359
virtual ~InsertFieldAction();
364
void setIndex(int index) {
367
KexiDB::Field& field() const {
370
void setField(KexiDB::Field* field);
371
virtual QString debugString(const DebugOptions& debugOptions = DebugOptions());
373
virtual void simplifyActions(ActionDictDict &fieldActions);
375
virtual tristate updateTableSchema(TableSchema &table, Field* field,
376
QHash<QString, QString>& fieldHash);
379
virtual void updateAlteringRequirements();
381
//! Performs physical execution of this action.
382
virtual tristate execute(Connection &conn, TableSchema &table);
387
KexiDB::Field *m_field;
390
/*! Defines an action for moving a single table field to a different
391
position within table schema. */
392
class KEXI_DB_EXPORT MoveFieldPositionAction : public FieldActionBase
395
MoveFieldPositionAction(int fieldIndex, const QString& fieldName, int uid);
396
MoveFieldPositionAction(bool);
397
virtual ~MoveFieldPositionAction();
402
virtual QString debugString(const DebugOptions& debugOptions = DebugOptions());
404
virtual void simplifyActions(ActionDictDict &fieldActions);
407
virtual void updateAlteringRequirements();
409
//! Performs physical execution of this action.
410
virtual tristate execute(Connection &conn, TableSchema &table);
415
AlterTableHandler(Connection &conn);
417
virtual ~AlterTableHandler();
419
/*! Appends \a action for the alter table tool. */
420
void addAction(ActionBase* action);
422
/*! Provided for convenience, @see addAction(const ActionBase& action). */
423
AlterTableHandler& operator<< (ActionBase* action);
425
/*! Removes an action from the alter table tool at index \a index. */
426
void removeAction(int index);
428
/*! Removes all actions from the alter table tool. */
431
/*! Sets \a actions for the alter table tool. Previous actions are cleared.
432
\a actions will be owned by the AlterTableHandler object. */
433
void setActions(const ActionList& actions);
435
/*! \return a list of actions for this AlterTable object.
436
Use ActionBase::ListIterator to iterate over the list items. */
437
const ActionList& actions() const;
439
//! Arguments for AlterTableHandler::execute().
440
class ExecutionArguments
448
, onlyComputeRequirements(false) {
450
/*! If not 0, debug is directed here. Used only in the alter table test suite. */
451
QString* debugString;
452
/*! Requrements computed, a combination of AlteringRequirements values. */
454
/*! Set to true on success, to false on failure. */
456
/*! Used only in the alter table test suite. */
458
/*! Set to true if requirements should be computed
459
and the execute() method should return afterwards. */
460
bool onlyComputeRequirements;
463
/*! Performs table alteration using predefined actions for table named \a tableName,
464
assuming it already exists. The Connection object passed to the constructor must exist,
465
must be connected and a database must be used. The connection must not be read-only.
467
If args.simulate is true, the execution is only simulated, i.e. al lactions are processed
468
like for regular execution but no changes are performed physically.
469
This mode is used only for debugging purposes.
471
@todo For some cases, table schema can completely change, so it will be needed
472
to refresh all objects depending on it.
475
Sets args.result to true on success, to false on failure or when the above requirements are not met
476
(then, you can get a detailed error message from KexiDB::Object).
477
When the action has been cancelled (stopped), args.result is set to cancelled value.
478
If args.debugString is not 0, it will be filled with debugging output.
479
\return the new table schema object created as a result of schema altering.
480
The old table is returned if recreating table schema was not necessary or args.simulate is true.
481
0 is returned if args.result is not true. */
482
TableSchema* execute(const QString& tableName, ExecutionArguments & args);
484
//! Displays debug information about all actions collected by the handler.
487
/*! Like execute() with simulate set to true, but debug is directed to debugString.
488
This function is used only in the alter table test suite. */
489
// tristate simulateExecution(const QString& tableName, QString& debugString);
491
/*! Helper. \return a combination of AlteringRequirements values decribing altering type required
492
when a given property field's \a propertyName is altered.
493
Used internally AlterTableHandler. Moreover it can be also used in the Table Designer's code
494
as a temporary replacement before AlterTableHandler is fully implemented.
495
Thus, it is possible to identify properties that have no PhysicalAlteringRequired flag set
496
(e.g. caption or extended properties like visibleDecimalPlaces. */
497
static int alteringTypeForProperty(const QByteArray& propertyName);
500
// TableSchema* executeInternal(const QString& tableName, tristate& result, bool simulate = false,
501
// QString* debugString = 0);