Project

General

Profile

Actions

Feature #12167

open

Enable setting of a Default Value for Dbo fields

Added by Christian Meyer about 1 year ago. Updated 4 months ago.

Status:
New
Priority:
Normal
Assignee:
-
Target version:
Start date:
11/18/2023
Due date:
% Done:

0%

Estimated time:

Description

Adding this would enable automatic update for Tables that already have Data

I Build a "Wt" Way to update SQL Tables in which I scan the generated SQL against previous versions. Then I remove all colums of a table that are already present in the older ones.

The Rest can be added with strict sql and as long as the table is empty, everything works.
If new columns are set as optional, this also works.

But as soon as the first row is filled in, updating failed as no default is given.

Adding a default, that shows up in the sql would fix that immediately =)

This might also help with generating info about the Application or Module Version to then update older info with new elements if needed.

Thanks

Actions #1

Updated by Matthias Van Ceulebroeck about 1 year ago

  • Target version set to future

Hello Christian,

I see your use-case. This is, however, a big undertaking, as it touches a lot of code. I will schedule this for the future, and still under some reservation.

For now, I would advise some external system to manage your database (structure). I have used flyway before, and think that's fairly decent. I know Liquibase is an alternative to it, but have never used it. That way you can define your structure very clearly, and perform migrations on it.
Additionally, this has the advantage that it provides more control in that you can define things not possible in Wt (like indexes for example).

Actions #2

Updated by Christian Meyer 4 months ago

Tried it and for now it seems to work

Added new Dbo::field() overload that takes a "default Value" as first parameter as a string to avoid ambiguity
Added new Constructor for FieldRef and FieldInfo

updated createTable to add default Value when exists

Here is a Diff

Would love some feedback if possible

Cheers
Christian

diff --git a/src/Wt/Dbo/DbAction_impl.h b/src/Wt/Dbo/DbAction_impl.h
index 7671812e..306b7bce 100644
--- a/src/Wt/Dbo/DbAction_impl.h
+++ b/src/Wt/Dbo/DbAction_impl.h
@@ -91,9 +91,17 @@ void InitSchema::act(const FieldRef<V>& field)
                  foreignKeyTable_, foreignKeyName_,
                  flags | FieldFlags::ForeignKey, fkConstraints_));
   else
-    // Normal field
-    mapping_.fields.push_back
-      (FieldInfo(field.name(), &typeid(V), field.sqlType(session_), flags));
+  {
+    if(field.hasDefaultValue())
+    {
+      mapping_.fields.push_back
+        (FieldInfo(field.name(), &typeid(V), field.sqlType(session_), flags, field.defaultValue()));
+
+    }else
+      // Normal field
+      mapping_.fields.push_back
+        (FieldInfo(field.name(), &typeid(V), field.sqlType(session_), flags));
+  }
 }

 template<class C>
diff --git a/src/Wt/Dbo/Field.h b/src/Wt/Dbo/Field.h
index 8b1b0f94..2fe40104 100644
--- a/src/Wt/Dbo/Field.h
+++ b/src/Wt/Dbo/Field.h
@@ -182,7 +182,8 @@ public:
     AuxId = 0x1
   };

-  FieldRef(V& value, const std::string& name, int size, int flags = 0);
+  explicit FieldRef(V& value, const std::string& name, int size, int flags = 0);
+  explicit FieldRef(const std::string& defaultValue, V& value, const std::string& name, int size, int flags = 0);

   const std::string& name() const;
   int size() const;
@@ -193,6 +194,9 @@ public:
   const V& value() const { return value_; }
   void setValue(const V& value) const { value_ = value; }

+  bool hasDefaultValue() const { return hasDefaultValue_; }
+  const std::string& defaultValue() const  { return defaultValue_; }
+
   void bindValue(SqlStatement *statement, int column) const;
   void setValue(Session& session, SqlStatement *statement, int column) const;

@@ -201,6 +205,8 @@ private:
   std::string name_;
   int size_;
   int flags_;
+  bool hasDefaultValue_ = false;
+  std::string defaultValue_ = {};
 };

 /*! \brief Type of an SQL relation.
@@ -368,6 +374,9 @@ void auxId(Action& action, ptr<C>& value, const std::string& name,
 template <class Action, typename V>
 void field(Action& action, V& value, const std::string& name, int size = -1);

+template <class Action, typename V>
+void field(const std::string& defaultValue, Action& action, V& value, const std::string& name, int size = -1);
+
 /*
  * This is synonym for belongsTo(), and used by id(). We should overload
  * this method also to allow foreign key constraints.
diff --git a/src/Wt/Dbo/Field_impl.h b/src/Wt/Dbo/Field_impl.h
index 51318323..ab02de21 100644
--- a/src/Wt/Dbo/Field_impl.h
+++ b/src/Wt/Dbo/Field_impl.h
@@ -24,6 +24,15 @@ FieldRef<V>::FieldRef(V& value, const std::string& name, int size, int flags)
     flags_(flags)
 { }

+template <typename V>
+FieldRef<V>::FieldRef(const std::string& dValue, V& value, const std::string& name, int size, int flags)
+  : value_(value),
+    name_(name),
+    size_(size),
+    flags_(flags),
+    defaultValue_(dValue)
+{ }
+
 template <typename V>
 const std::string& FieldRef<V>::name() const
 {
@@ -204,6 +213,12 @@ void field(A& action, V& value, const std::string& name, int size)
   action.act(FieldRef<V>(value, name, size));
 }

+template <class A, typename V>
+void field(const std::string& dValue, A& action, V& value, const std::string& name, int size)
+{
+  action.act(FieldRef<V>(dValue, value, name, size));
+}
+
 template <class A, class C>
 void field(A& action, ptr<C>& value, const std::string& name, int)
 {
diff --git a/src/Wt/Dbo/Session.C b/src/Wt/Dbo/Session.C
index 530a247c..70de9a7b 100644
--- a/src/Wt/Dbo/Session.C
+++ b/src/Wt/Dbo/Session.C
@@ -786,6 +786,11 @@ void Session::createTable(Impl::MappingInfo *mapping,

       sql << "  \"" << field.name() << "\" " << sqlType;

+      if(field.hasDefaultValue())
+      {
+        sql << " DEFAULT " << field.sqlDefaultValue();
+      }
+
       firstField = false;

       if (field.isNaturalIdField()) {
diff --git a/src/Wt/Dbo/SqlTraits.C b/src/Wt/Dbo/SqlTraits.C
index 8ce64afe..0aab719d 100644
--- a/src/Wt/Dbo/SqlTraits.C
+++ b/src/Wt/Dbo/SqlTraits.C
@@ -29,6 +29,19 @@ FieldInfo::FieldInfo(const std::string& name,
     flags_(flags)
 { }

+FieldInfo::FieldInfo(const std::string& name,
+                     const std::type_info *type,
+                     const std::string& sqlType,
+                     int flags,
+                     const std::string& defaultValue)
+  : name_(name),
+    sqlType_(sqlType),
+    type_(type),
+    flags_(flags),
+    sqlDefaultValue_(defaultValue),
+    hasDefaultValue_(true)
+{ }
+
 FieldInfo::FieldInfo(const std::string& name, const std::type_info *type,
                      const std::string& sqlType,
                      const std::string& foreignKeyTable,
diff --git a/src/Wt/Dbo/SqlTraits.h b/src/Wt/Dbo/SqlTraits.h
index 8803f314..354713b5 100644
--- a/src/Wt/Dbo/SqlTraits.h
+++ b/src/Wt/Dbo/SqlTraits.h
@@ -196,6 +196,11 @@ public:
   FieldInfo(const std::string& name, const std::type_info *type,
             const std::string& sqlType, int flags);

+  /*! \brief Creates a field description.
+   */
+  FieldInfo(const std::string& name, const std::type_info *type,
+            const std::string& sqlType, int flags, const std::string& defaultValue);
+
   /*! \brief Creates a field description.
    */
   FieldInfo(const std::string& name, const std::type_info *type,
@@ -216,6 +221,14 @@ public:
    */
   const std::string& sqlType() const { return sqlType_; }

+  /*! \brief Returns the default SQL Value.
+   */
+  const std::string& sqlDefaultValue() const { return sqlDefaultValue_; }
+
+  /*! \brief Returns whether there is a default SQL Value.
+   */
+  const bool hasDefaultValue() const { return hasDefaultValue_; }
+
   /*! \brief Returns the field qualifier.
    */
   const std::string& qualifier() const { return qualifier_; }
@@ -263,11 +276,12 @@ public:
   std::string sql() const;

 private:
-  std::string name_, sqlType_, qualifier_;
+  std::string name_, sqlType_, qualifier_, sqlDefaultValue_ = "";
   std::string foreignKeyName_, foreignKeyTable_;
   const std::type_info *type_;
   int flags_;
   int fkConstraints_;
+  bool hasDefaultValue_ = false;
 };

 /*! \class query_result_traits Wt/Dbo/SqlTraits.h Wt/Dbo/SqlTraits.h
Actions

Also available in: Atom PDF