File indexing completed on 2024-12-08 07:18:17

0001 /* This file is part of the KDE project
0002    Copyright (C) 2006-2012 Jarosław Staniek <staniek@kde.org>
0003 
0004    This library is free software; you can redistribute it and/or
0005    modify it under the terms of the GNU Library General Public
0006    License as published by the Free Software Foundation; either
0007    version 2 of the License, or (at your option) any later version.
0008 
0009    This library is distributed in the hope that it will be useful,
0010    but WITHOUT ANY WARRANTY; without even the implied warranty of
0011    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
0012    Library General Public License for more details.
0013 
0014    You should have received a copy of the GNU Library General Public License
0015    along with this library; see the file COPYING.LIB.  If not, write to
0016    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
0017  * Boston, MA 02110-1301, USA.
0018 */
0019 
0020 #ifndef KDB_ALTER_H
0021 #define KDB_ALTER_H
0022 
0023 #include "KDbUtils.h"
0024 #include "KDbResult.h"
0025 #include "KDbTristate.h"
0026 #include "KDbTableSchema.h"
0027 
0028 #include <QList>
0029 #include <QHash>
0030 
0031 class KDbConnection;
0032 
0033 //! @short A tool for handling altering database table schema.
0034 /*! In relational (and other) databases, table schema altering is not an easy task.
0035  It may be considered as easy if there is no data that user wants to keep while
0036  the table schema is altered. Otherwise, if the table is alredy filled with data,
0037  there could be no easy algorithm like:
0038  1. Drop existing table
0039  2. Create new one with altered schema.
0040 
0041  Instead, more complex algorithm is needed. To perform the table schema alteration,
0042  a list of well defined atomic operations is used as a "recipe".
0043 
0044  1. Look at the current data, and:
0045  1.1. analyze what values will be removed (in case of impossible conversion
0046       or table field removal);
0047  1.2. analyze what values can be converted (e.g. from numeric types to text), and so on.
0048  2. Optimize the atomic actions knowing that sometimes a compilation of one action
0049     and another that's opposite to the first means "do nothing". The optimization
0050     is a simulating of actions' execution.
0051     For example, when both action A="change field name from 'city' to 'town'"
0052     and action B="change field name from 'town' to 'city'" is specified, the compilation
0053     of the actions means "change field name from 'city' to 'city'", what is a NULL action.
0054     On the other hand, we need to execute all the actions on the destination table
0055     in proper order, and not just drop them. For the mentioned example, between actions
0056     A and B there can be an action like C="change the type of field 'city' to LongText".
0057     If A and B were simply removed, C would become invalid (there is no 'city' field).
0058  3. Ask user whether she agrees with the results of analysis mentioned in 1.
0059  3.2. Additionally, it may be possible to get some hints from the user, as humans usually
0060       know more about logic behind the altered table schema than any machine.
0061       If the user provided hints about the altering, apply them to the actions list.
0062  4. Create (empty) destination table schema with temporary name, using
0063     the information collected so far.
0064  5. Copy the data from the source to destionation table. Convert values,
0065     move them between fields, using the information collected.
0066  6. Remove the source table.
0067  7. Rename the destination table to the name previously assigned for the source table.
0068 
0069  Notes:
0070  * The actions 4 to 7 should be performed within a database transaction.
0071  * [todo] We want to take care about database relationships as well.
0072     For example, is a table field is removed, relationships related to this field should
0073     be also removed (similar rules as in the Query Designer).
0074  * Especially, care about primary keys and uniquess (indices). Recreate them when needed.
0075    The problem could be if such analysis may require to fetch the entire table data
0076    to the client side. Use "SELECT INTO" statements if possible to avoid such a treat.
0077 
0078  The KDbAlterTableHandler is used in Kexi's Table Designer.
0079  Already opened KDbConnection object is needed.
0080 
0081  Use case:
0082  @code
0083   KDbConnection *conn = ...
0084 
0085   // add some actions (in reality this is performed by tracking user's actions)
0086   // Actions 1, 2 will require physical table altering PhysicalAltering
0087   // Action 3 will only require changes in kexi__fields
0088   // Action 4 will only require changes in extended table schema written in kexi__objectdata
0089   AlterTable::ActionList list;
0090 
0091   // 1. rename the "city" field to "town"
0092   list << new ChangeFieldPropertyAction("city", "name", "town")
0093 
0094   // 2. change type of "town" field to "LongText"
0095     << new ChangeFieldPropertyAction("town", "type", "LongText")
0096 
0097   // 3. set caption of "town" field to "Town"
0098     << new ChangeFieldPropertyAction("town", "caption", "Town")
0099 
0100   // 4. set visible decimal places to 4 for "cost" field
0101     << new ChangeFieldPropertyAction("cost", "visibleDecimalPlaces", 4)
0102 
0103   KDbAlterTableHandler::execute( *conn );
0104 
0105  @endcode
0106 
0107  Actions for Alter
0108 */
0109 class KDB_EXPORT KDbAlterTableHandler : public KDbResultable
0110 {
0111 public:
0112     class ChangeFieldPropertyAction;
0113     class RemoveFieldAction;
0114     class InsertFieldAction;
0115     class MoveFieldPositionAction;
0116 
0117     //! Defines flags for possible altering requirements; can be combined.
0118     enum AlteringRequirements {
0119         /*! Physical table altering is required; e.g. ALTER TABLE ADD COLUMN. */
0120         PhysicalAlteringRequired = 1,
0121 
0122         /*! Data conversion is required; e.g. converting integer
0123          values to string after changing column type from integer to text. */
0124         DataConversionRequired = 2,
0125 
0126         /*! Changes to the main table schema (in kexi__fields) required,
0127          this does not require physical changes for the table;
0128          e.g. changing value of the "caption" or "description" property. */
0129         MainSchemaAlteringRequired = 4,
0130 
0131         /*! Only changes to extended table schema required,
0132          this does not require physical changes for the table;
0133          e.g. changing value of the "visibleDecimalPlaces" property
0134          or any of the custom properties. */
0135         ExtendedSchemaAlteringRequired = 8,
0136 
0137         /*! Convenience flag, changes to the main or extended schema is required. */
0138         SchemaAlteringRequired = ExtendedSchemaAlteringRequired | MainSchemaAlteringRequired
0139     };
0140 
0141     class ActionBase;
0142     //! For collecting actions related to a single field
0143     typedef KDbUtils::AutodeletedHash<QByteArray, ActionBase*> ActionDict;
0144     typedef KDbUtils::AutodeletedHash<int, ActionDict*> ActionDictDict; //!< for collecting groups of actions by field UID
0145     typedef QHash<QByteArray, ActionBase*>::Iterator ActionDictIterator;
0146     typedef QHash<QByteArray, ActionBase*>::ConstIterator ActionDictConstIterator;
0147     typedef QHash<int, ActionDict*>::Iterator ActionDictDictIterator;
0148     typedef QHash<int, ActionDict*>::ConstIterator ActionDictDictConstIterator;
0149     typedef QVector<ActionBase*> ActionsVector; //!< for collecting actions related to a single field
0150 
0151     //! Defines a type for action list.
0152     typedef QList<ActionBase*> ActionList;
0153 
0154     //! Defines a type for action list's iterator.
0155     typedef QList<ActionBase*>::ConstIterator ActionListIterator;
0156 
0157     //! Abstract base class used for implementing all the AlterTable actions.
0158     class KDB_EXPORT ActionBase
0159     {
0160     public:
0161         virtual ~ActionBase();
0162 
0163         ChangeFieldPropertyAction& toChangeFieldPropertyAction();
0164         RemoveFieldAction& toRemoveFieldAction();
0165         InsertFieldAction& toInsertFieldAction();
0166         MoveFieldPositionAction& toMoveFieldPositionAction();
0167 
0168         //! @return true if the action is NULL; used in the Table Designer
0169         //! for temporarily collecting actions that have no effect at all.
0170         inline bool isNull() const {
0171             return m_null;
0172         }
0173 
0174         //! Controls debug options for actions. Used in debugString() and debug().
0175         class DebugOptions
0176         {
0177         public:
0178             inline DebugOptions() : showUID(true), showFieldDebug(false) {}
0179 
0180             //! true if UID should be added to the action debug string (the default)
0181             bool showUID;
0182 
0183             //! true if the field associated with the action (if exists) should
0184             //! be appended to the debug string (default is false)
0185             bool showFieldDebug;
0186         };
0187 
0188         inline virtual QString debugString(const DebugOptions& debugOptions = DebugOptions()) {
0189             Q_UNUSED(debugOptions); return QLatin1String("ActionBase");
0190         }
0191 
0192 //! @todo add QDebug operator <<
0193         void debug(const DebugOptions& debugOptions = DebugOptions());
0194 
0195     protected:
0196         //! @internal, used for constructing null action
0197         explicit ActionBase(bool null);
0198 
0199         //! Sets requirements for altering; used internally by KDbAlterTableHandler object
0200         inline void setAlteringRequirements(int alteringRequirements) {
0201             m_alteringRequirements = alteringRequirements;
0202         }
0203 
0204         inline int alteringRequirements() const {
0205             return m_alteringRequirements;
0206         }
0207 
0208         inline virtual void updateAlteringRequirements() {}
0209 
0210         /*! Simplifies @a fieldActions dictionary. If this action has to be inserted
0211          Into the dictionary, an ActionDict is created first and then a copy of this action
0212          is inserted into it. */
0213         inline virtual void simplifyActions(ActionDictDict *fieldActions) {
0214             Q_UNUSED(fieldActions);
0215         }
0216 
0217         /*! After calling simplifyActions() for each action,
0218          shouldBeRemoved() is called for them as an additional step.
0219          This is used for ChangeFieldPropertyAction items so actions
0220          that do not change property values are removed. */
0221         inline virtual bool shouldBeRemoved(ActionDictDict *fieldActions) {
0222             Q_UNUSED(fieldActions); return false;
0223         }
0224 
0225         inline virtual tristate updateTableSchema(KDbTableSchema* table, KDbField* field,
0226                                            QHash<QString, QString>* fieldHash) {
0227             Q_UNUSED(table); Q_UNUSED(field); Q_UNUSED(fieldHash); return true;
0228         }
0229 
0230     private:
0231         //! Performs physical execution of this action.
0232         inline virtual tristate execute(KDbConnection* /*conn*/, KDbTableSchema* /*table*/) {
0233             return true;
0234         }
0235 
0236         //! requirements for altering; used internally by KDbAlterTableHandler object
0237         int m_alteringRequirements;
0238 
0239         //! @internal used for "simplify" algorithm
0240         int m_order;
0241 
0242         const bool m_null;
0243 
0244         friend class KDbAlterTableHandler;
0245     };
0246 
0247     //! Abstract base class used for implementing table field-related actions.
0248     class KDB_EXPORT FieldActionBase : public ActionBase
0249     {
0250     public:
0251         FieldActionBase(const QString& fieldName, int uid);
0252         ~FieldActionBase() override;
0253 
0254         //! @return field name for this action
0255         inline QString fieldName() const {
0256             return m_fieldName;
0257         }
0258 
0259         /*! @return field's unique identifier
0260          This id is needed because in the meantime there can be more than one
0261          field sharing the same name, so we need to identify them unambiguously.
0262          After the (valid) altering is completed all the names will be unique.
0263 
0264          Example scenario when user exchanged the field names:
0265          1. At the beginning: [field A], [field B]
0266          2. Rename the 1st field to B: [field B], [field B]
0267          3. Rename the 2nd field to A: [field B], [field A] */
0268         inline int uid() const {
0269             return m_fieldUID;
0270         }
0271 
0272         //! Sets field name for this action
0273         inline void setFieldName(const QString& fieldName) {
0274             m_fieldName = fieldName;
0275         }
0276 
0277     protected:
0278         //! @internal, used for constructing null action
0279         explicit FieldActionBase(bool null);
0280 
0281         //! field's unique identifier, @see uid()
0282         int m_fieldUID;
0283     private:
0284         QString m_fieldName;
0285     };
0286 
0287     /*! Defines an action for changing a single property value of a table field.
0288      Supported properties are currently:
0289      "name", "type", "caption", "description", "unsigned", "maxLength", "precision",
0290      "defaultWidth", "defaultValue", "primaryKey", "unique", "notNull", "allowEmpty",
0291      "autoIncrement", "indexed", "visibleDecimalPlaces"
0292 
0293      More to come.
0294     */
0295     class KDB_EXPORT ChangeFieldPropertyAction : public FieldActionBase
0296     {
0297     public:
0298         ChangeFieldPropertyAction(const QString& fieldName,
0299                                   const QString& propertyName, const QVariant& newValue, int uid);
0300 
0301         //! Creates null action
0302         ChangeFieldPropertyAction();
0303 
0304         ~ChangeFieldPropertyAction() override;
0305 
0306         inline QString propertyName() const {
0307             return m_propertyName;
0308         }
0309         inline QVariant newValue() const {
0310             return m_newValue;
0311         }
0312         QString debugString(const DebugOptions& debugOptions = DebugOptions()) override;
0313 
0314         void simplifyActions(ActionDictDict *fieldActions) override;
0315 
0316         bool shouldBeRemoved(ActionDictDict *fieldActions) override;
0317 
0318         tristate updateTableSchema(KDbTableSchema *table, KDbField *field,
0319                                    QHash<QString, QString> *fieldHash) override;
0320 
0321     protected:
0322         //! @internal, used for constructing null action
0323         explicit ChangeFieldPropertyAction(bool null);
0324 
0325         void updateAlteringRequirements() override;
0326 
0327         //! Performs physical execution of this action.
0328         tristate execute(KDbConnection* conn, KDbTableSchema* table) override;
0329 
0330         QString m_propertyName;
0331         QVariant m_newValue;
0332     };
0333 
0334     //! Defines an action for removing a single table field.
0335     class KDB_EXPORT RemoveFieldAction : public FieldActionBase
0336     {
0337     public:
0338         RemoveFieldAction(const QString& fieldName, int uid);
0339 
0340         ~RemoveFieldAction() override;
0341 
0342         QString debugString(const DebugOptions& debugOptions = DebugOptions()) override;
0343 
0344         void simplifyActions(ActionDictDict *fieldActions) override;
0345 
0346         tristate updateTableSchema(KDbTableSchema *table, KDbField *field,
0347                                    QHash<QString, QString> *fieldHash) override;
0348 
0349     protected:
0350         //! @internal, used for constructing null action
0351         explicit RemoveFieldAction(bool null);
0352 
0353         void updateAlteringRequirements() override;
0354 
0355         //! Performs physical execution of this action.
0356         tristate execute(KDbConnection* conn, KDbTableSchema* table) override;
0357     };
0358 
0359     //! Defines an action for inserting a single table field.
0360     class KDB_EXPORT InsertFieldAction : public FieldActionBase
0361     {
0362     public:
0363         InsertFieldAction(int fieldIndex, KDbField *newField, int uid);
0364 
0365         //! copy ctor
0366         InsertFieldAction(const InsertFieldAction& action);
0367 
0368         //! Creates null action
0369         InsertFieldAction();
0370 
0371         ~InsertFieldAction() override;
0372 
0373         inline int index() const {
0374             return m_index;
0375         }
0376         inline void setIndex(int index) {
0377             m_index = index;
0378         }
0379         inline const KDbField* field() const {
0380             return m_field;
0381         }
0382         void setField(KDbField* field);
0383         QString debugString(const DebugOptions& debugOptions = DebugOptions()) override;
0384 
0385         void simplifyActions(ActionDictDict *fieldActions) override;
0386 
0387         tristate updateTableSchema(KDbTableSchema *table, KDbField *field,
0388                                    QHash<QString, QString> *fieldHash) override;
0389 
0390     protected:
0391         //! @internal, used for constructing null action
0392         explicit InsertFieldAction(bool null);
0393 
0394         void updateAlteringRequirements() override;
0395 
0396         //! Performs physical execution of this action.
0397         tristate execute(KDbConnection* conn, KDbTableSchema* table) override;
0398 
0399         int m_index;
0400 
0401     private:
0402         KDbField *m_field;
0403     };
0404 
0405     /*! Defines an action for moving a single table field to a different
0406      position within table schema. */
0407     class KDB_EXPORT MoveFieldPositionAction : public FieldActionBase
0408     {
0409     public:
0410         MoveFieldPositionAction(int fieldIndex, const QString& fieldName, int uid);
0411 
0412         ~MoveFieldPositionAction() override;
0413 
0414         inline int index() const {
0415             return m_index;
0416         }
0417         QString debugString(const DebugOptions& debugOptions = DebugOptions()) override;
0418 
0419         void simplifyActions(ActionDictDict *fieldActions) override;
0420 
0421     protected:
0422         //! @internal, used for constructing null action
0423         explicit MoveFieldPositionAction(bool null);
0424 
0425         void updateAlteringRequirements() override;
0426 
0427         //! Performs physical execution of this action.
0428         tristate execute(KDbConnection* conn, KDbTableSchema* table) override;
0429 
0430         int m_index;
0431     };
0432 
0433     explicit KDbAlterTableHandler(KDbConnection* conn);
0434 
0435     ~KDbAlterTableHandler() override;
0436 
0437     /*! Appends @a action for the alter table tool. */
0438     void addAction(ActionBase* action);
0439 
0440     /*! Provided for convenience, @see addAction(const ActionBase& action). */
0441     KDbAlterTableHandler& operator<< (ActionBase* action);
0442 
0443     /*! Removes an action from the alter table tool at index @a index. */
0444     void removeAction(int index);
0445 
0446     /*! Removes all actions from the alter table tool. */
0447     void clear();
0448 
0449     /*! Sets @a actions for the alter table tool. Previous actions are cleared.
0450      @a actions will be owned by the KDbAlterTableHandler object. */
0451     void setActions(const ActionList& actions);
0452 
0453     /*! @return a list of actions for this AlterTable object.
0454      Use ActionBase::ListIterator to iterate over the list items. */
0455     const ActionList& actions() const;
0456 
0457     //! Arguments for KDbAlterTableHandler::execute().
0458     class ExecutionArguments
0459     {
0460     public:
0461         inline ExecutionArguments()
0462                 : debugString(nullptr)
0463                 , requirements(0)
0464                 , result(false)
0465                 , simulate(false)
0466                 , onlyComputeRequirements(false) {
0467         }
0468         /*! If not 0, debug is directed here. Used only in the alter table test suite. */
0469         QString* debugString;
0470         /*! Requrements computed, a combination of AlteringRequirements values. */
0471         int requirements;
0472         /*! Set to true on success, to false on failure. */
0473         tristate result;
0474         /*! Used only in the alter table test suite. */
0475         bool simulate;
0476         /*! Set to true if requirements should be computed
0477          and the execute() method should return afterwards. */
0478         bool onlyComputeRequirements;
0479     private:
0480         Q_DISABLE_COPY(ExecutionArguments)
0481     };
0482 
0483     /*! Performs table alteration using predefined actions for table named @a tableName,
0484      assuming it already exists. The KDbConnection object passed to the constructor must exist,
0485      must be connected and a database must be used. The connection must not be read-only.
0486 
0487      If args.simulate is true, the execution is only simulated, i.e. al lactions are processed
0488      like for regular execution but no changes are performed physically.
0489      This mode is used only for debugging purposes.
0490 
0491     @todo For some cases, table schema can completely change, so it will be needed
0492      to refresh all objects depending on it.
0493      Implement this!
0494 
0495      Sets args.result to true on success, to false on failure or when the above requirements are not met
0496      (then, you can get a detailed error message from KDbObject).
0497      When the action has been cancelled (stopped), args.result is set to cancelled value.
0498      If args.debugString is not 0, it will be filled with debugging output.
0499      @return the new table schema object created as a result of schema altering.
0500      The old table is returned if recreating table schema was not necessary or args.simulate is true.
0501      0 is returned if args.result is not true. */
0502     KDbTableSchema* execute(const QString& tableName, ExecutionArguments* args);
0503 
0504     //! Displays debug information about all actions collected by the handler.
0505     void debug();
0506 
0507     /*! Like execute() with simulate set to true, but debug is directed to debugString.
0508      This function is used only in the alter table test suite. */
0509 //  tristate simulateExecution(const QString& tableName, QString& debugString);
0510 
0511     /*! Helper. @return a combination of AlteringRequirements values decribing altering type required
0512      when a given property field's @a propertyName is altered.
0513      Used internally KDbAlterTableHandler. Moreover it can be also used in the Table Designer's code
0514      as a temporary replacement before KDbAlterTableHandler is fully implemented.
0515      Thus, it is possible to identify properties that have no PhysicalAlteringRequired flag set
0516      (e.g. caption or extended properties like visibleDecimalPlaces. */
0517     static int alteringTypeForProperty(const QByteArray& propertyName);
0518 
0519 private:
0520     Q_DISABLE_COPY(KDbAlterTableHandler)
0521     class Private;
0522     Private * const d;
0523 };
0524 
0525 #endif