Torque
  1. Torque
  2. TORQUE-6

Generator does not generate foreign key constraints for Interbase/Firebird

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 3.2
    • Fix Version/s: 3.3
    • Component/s: Generator
    • Labels:
      None

      Description

      The generator does not generate foreign key constraints for Interbase/Firebird. This could probably be easily fixed by adding a file foreignkey.vm to the sql/base/interbase directory in the templates jar (torque-gen-templates-3.2.jar) containing this:

      #foreach ($fk in $table.ForeignKeys)
      FOREIGN KEY ($fk.LocalColumnNames) REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames),
      #end

        Activity

        Hide
        Thomas Fox added a comment -

        Fixed in SVN.
        I did not change the drop table statement. This behaviour is consistent thoughout Torque and should be solved by separating the drop table statements from the create table statements.

        Show
        Thomas Fox added a comment - Fixed in SVN. I did not change the drop table statement. This behaviour is consistent thoughout Torque and should be solved by separating the drop table statements from the create table statements.
        Hide
        Joerg Friedrich added a comment -

        I have made the following changes and tested with Firebird 1.5 (directory ...\templates\src\templates\sql\base\interbase:

        1. remove the only active line from drop.vm since it creates exceptions if the table does not exist
        2. modified file tablefk.vm (took the one from Oracle)
        3. modified file foreignkey.vm (took the one from Oracle as suggested above)

        Then it looks okay in my tests.

        Show
        Joerg Friedrich added a comment - I have made the following changes and tested with Firebird 1.5 (directory ...\templates\src\templates\sql\base\interbase: 1. remove the only active line from drop.vm since it creates exceptions if the table does not exist 2. modified file tablefk.vm (took the one from Oracle) 3. modified file foreignkey.vm (took the one from Oracle as suggested above) Then it looks okay in my tests.
        Hide
        Joerg Friedrich added a comment -

        In the past it looked like this:

        ALTER TABLE TACL
        ADD CONSTRAINT TACL_FK_1 FOREIGN KEY (PERSONKEY)
        REFERENCES TPERSON (PKEY);

        ALTER TABLE TACL
        ADD CONSTRAINT TACL_FK_2 FOREIGN KEY (ROLEKEY)
        REFERENCES TROLE (PKEY);

        ALTER TABLE TACL
        ADD CONSTRAINT TACL_FK_3 FOREIGN KEY (PROJKEY)
        REFERENCES TPROJECT (PKEY);

        ALTER TABLE TBASELINE
        ADD CONSTRAINT TBASELINE_FK_1 FOREIGN KEY (WORKITEMKEY)
        REFERENCES TWORKITEM (WORKITEMKEY);

        I have not used the onUpdate and onDelete feature, since it does not work for all database systems and thus we had to put this functionality into the application logic.

        The Firebird/Interbase manual states that it supports "ON DELETE CASCADE" and "ON UPDATE CASCADE" (exactly like this) in constraints.

        Show
        Joerg Friedrich added a comment - In the past it looked like this: ALTER TABLE TACL ADD CONSTRAINT TACL_FK_1 FOREIGN KEY (PERSONKEY) REFERENCES TPERSON (PKEY); ALTER TABLE TACL ADD CONSTRAINT TACL_FK_2 FOREIGN KEY (ROLEKEY) REFERENCES TROLE (PKEY); ALTER TABLE TACL ADD CONSTRAINT TACL_FK_3 FOREIGN KEY (PROJKEY) REFERENCES TPROJECT (PKEY); ALTER TABLE TBASELINE ADD CONSTRAINT TBASELINE_FK_1 FOREIGN KEY (WORKITEMKEY) REFERENCES TWORKITEM (WORKITEMKEY); I have not used the onUpdate and onDelete feature, since it does not work for all database systems and thus we had to put this functionality into the application logic. The Firebird/Interbase manual states that it supports "ON DELETE CASCADE" and "ON UPDATE CASCADE" (exactly like this) in constraints.
        Hide
        Thomas Fox added a comment -

        Because of the problems with the table creation order, I'd rather use something like the following (taken from oracle's foreignkey.vm):

        #foreach ($fk in $table.ForeignKeys)
        ALTER TABLE $table.Name
        ADD CONSTRAINT $fk.Name FOREIGN KEY ($fk.LocalColumnNames)
        REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)
        #if ($fk.hasOnUpdate() && !$fk.OnUpdate.equalsIgnoreCase("cascade"))
        ON UPDATE $fk.OnUpdate
        #end
        #if ($fk.hasOnDelete())
        ON DELETE $fk.OnDelete
        #end
        ;

        Will this work for firebird/interbase ? Especially, does the onUpdate and onDelete parts make sense?

        Show
        Thomas Fox added a comment - Because of the problems with the table creation order, I'd rather use something like the following (taken from oracle's foreignkey.vm): #foreach ($fk in $table.ForeignKeys) ALTER TABLE $table.Name ADD CONSTRAINT $fk.Name FOREIGN KEY ($fk.LocalColumnNames) REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames) #if ($fk.hasOnUpdate() && !$fk.OnUpdate.equalsIgnoreCase("cascade")) ON UPDATE $fk.OnUpdate #end #if ($fk.hasOnDelete()) ON DELETE $fk.OnDelete #end ; Will this work for firebird/interbase ? Especially, does the onUpdate and onDelete parts make sense?

          People

          • Assignee:
            Thomas Fox
            Reporter:
            Joerg Friedrich
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development