Torque
  1. Torque
  2. TORQUE-142

MYSQL - default timestamp set to current_timestamp generates classes incorrectly

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 3.3
    • Fix Version/s: 4.0-beta1
    • Component/s: Generator
    • Labels:
      None
    • Environment:
      Windows

      Description

      Recently I changed all the tables in my database to include an updated_ts column.

      I have the timestamp column in mysql defined as
      `updated_ts` TIMESTAMP NOT NULL DEFAULT now() ON UPDATE NOW(), this is the same as
      `updated_ts` TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp

      When I generate the XML from the DB, I get the below generated. Please look at the last column.
      <table name="address">
      <column name="address_id" primaryKey="true" required="true"
      size="20" type="VARCHAR"/>
      <column name="address_ln_1" required="true" size="150" type="VARCHAR"/>
      <column name="address_ln_2" size="150" type="VARCHAR"/>
      <column name="city" required="true" size="100" type="VARCHAR"/>
      <column name="state" required="true" size="2" type="CHAR"/>
      <column name="pincode" required="true" size="8" type="CHAR"/>
      <column name="email_id" size="100" type="VARCHAR"/>
      <column default="India" name="country" required="true" size="45" type="VARCHAR"/>
      <column default="CURRENT_TIMESTAMP" name="updated_ts"
      required="true" type="TIMESTAMP"/>
      </table>

      Now when I generate the OM classes I get the Address object generated with the below line:
      private Date updatedTs = new Date(CURRENT_TIMESTAMP);

      This results in a compilation error.

      My questions are:
      1. Are default timestamp values supported in Torque?
      2. How about the "on update" clause? If a column has an on update clause, will Torque ignore it and just send an update statement with the column to the database. This would result in some issues, for example if I selected a row from the table and changed some values. I then call save(), then the updated timestamp will not get updated because it already has a value in it. Is there anyway I can change Torque in a common place to set the value to be null, if the column name is updated_ts. This would solve this problem.

      Any help is highly appreciated.

        Activity

        Hide
        Thomas Fox added a comment -

        This is not intended to be working. Current_timestamp is a mysql specific value and will not work on other databases.
        The problem can be solved on the java side by overwriting the save() method on your objects in java and setting the timestamp in java before saving the object. In this solution, it is a good idea to check if the object was modified (isModified()) before setting the timestam otherwise the object will be saved even if it was not modified.

        Show
        Thomas Fox added a comment - This is not intended to be working. Current_timestamp is a mysql specific value and will not work on other databases. The problem can be solved on the java side by overwriting the save() method on your objects in java and setting the timestamp in java before saving the object. In this solution, it is a good idea to check if the object was modified (isModified()) before setting the timestam otherwise the object will be saved even if it was not modified.
        Hide
        Sethuraman Ramasubramanian added a comment -

        Thanks for the reply Thomas. But I am not sure if agree regarding it not being a problem. Its ok if Torque does not support current_timestamp as it is a mysql specific thing. But the generator should ignore it then and not create a java object with current_timestamp going into the constructor. This results in a compilation issue, which I think can be easily rectified. I solved this problem by changing the generator code to ignore the current_timestamp attribute ("hard coded" it). We can probably create a method in the corresponding DB Adapter and utilise this at the time of generating the classes. Maybe that can extended to defaulting values instead of overriding the save(). But that is secondary, we can avoid the compilation issue to being with.

        Show
        Sethuraman Ramasubramanian added a comment - Thanks for the reply Thomas. But I am not sure if agree regarding it not being a problem. Its ok if Torque does not support current_timestamp as it is a mysql specific thing. But the generator should ignore it then and not create a java object with current_timestamp going into the constructor. This results in a compilation issue, which I think can be easily rectified. I solved this problem by changing the generator code to ignore the current_timestamp attribute ("hard coded" it). We can probably create a method in the corresponding DB Adapter and utilise this at the time of generating the classes. Maybe that can extended to defaulting values instead of overriding the save(). But that is secondary, we can avoid the compilation issue to being with.
        Hide
        Thomas Fox added a comment -

        CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP all seem to be ANSI SQL defined so all of these should be honored both by the runtime and by the generator.

        Show
        Thomas Fox added a comment - CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP all seem to be ANSI SQL defined so all of these should be honored both by the runtime and by the generator.
        Hide
        Thomas Fox added a comment -

        One can now use CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP as default for Date fields.
        Setting the attribute useDatabaseDefaultValue to false or not setting it makes Torque use the current java time as initial field value when constructing the object and saving it to the database, not considering the database default.
        Setting the attribute useDatabaseDefaultValue to true makes Torque setting the initial field value to null and using the database default when the object is first saved to the database.
        Beware that the default mapping of Torque type Timestamp has changed, it is now mapped to DATETIME (which does not take CURRENT_TIMESTAMP as default in MySQL). This can be changed by writing an own PlatformImpl and making it available to the generator on generate time.

        Show
        Thomas Fox added a comment - One can now use CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP as default for Date fields. Setting the attribute useDatabaseDefaultValue to false or not setting it makes Torque use the current java time as initial field value when constructing the object and saving it to the database, not considering the database default. Setting the attribute useDatabaseDefaultValue to true makes Torque setting the initial field value to null and using the database default when the object is first saved to the database. Beware that the default mapping of Torque type Timestamp has changed, it is now mapped to DATETIME (which does not take CURRENT_TIMESTAMP as default in MySQL). This can be changed by writing an own PlatformImpl and making it available to the generator on generate time.

          People

          • Assignee:
            Thomas Fox
            Reporter:
            Sethuraman Ramasubramanian
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development