joining table sql generation error

[Will Holcomb <wi...@himinbi.org>]

I am using torque 3.0-b2 and the following schema:

<database name="directory" defaultIdMethod="native">
  ...
  <table name="first_post" description="First post in a conference">
    <column
     name="conference_id"
     required="true"
     type="INTEGER"
     description="Conference ID" />
    <column
     name="post_id"
     required="true"
     type="INTEGER"
     description="Post ID" />

    <foreign-key foreignTable="conference">
      <reference local="conference_id" foreign="id"/>
    </foreign-key>
    <foreign-key foreignTable="post">
      <reference local="post_id" foreign="id"/>
    </foreign-key>
  </table>
</database>

Generates the following sql:

CREATE TABLE first_post
(
                    conference_id integer NOT NULL,
      -- REFERENCES conference (id)
                    post_id integer NOT NULL,
      -- REFERENCES post (id
);

Note the lack of a close paren on the second reference. If I add another 
column after it then it works fine. I am really at a loss since the code 
is so straightforward: from templates/sql/base/postgresql/columns.vm

#if ($col.isForeignKey())
#set ($fk = $col.ForeignKey)
      -- REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)
#end

I see absolutely no reason that I would be missing the close paren there.

Interestingly if I replace the code with:

#if ($col.isForeignKey())
#set ($fk = $col.ForeignKey)
  -- REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames) blah blah blah
#end

Then the sql I get is:

CREATE TABLE first_post
(
                    conference_id integer NOT NULL,
      -- REFERENCES conference (id) blah blah blah
                    post_id integer NOT NULL,
      -- REFERENCES post (id) blah blah bla
);

It is always eating the last character for some reason. Why is thouroughly
beyond me. =) I can fix the error with a trailing space in the template.  
If anyone figures this out out; I'm really curious what is going on.

I am using the release version of velocity 1.3 (I got the same error with
the dev version). The velocity log shows no errors.

*-*

I did a diff against the version in cvs and I notice that the sequences
are being created by hand and set as the default in b4. That will fix the
problem I mentioned earlier about the sequence names not lining up with
postgres' default names.

I suppose it will fix the issue with a possible conflict. If there were 
two serial columns they will both just be pulling off of the same 
sequence. Can't see a real advantage to that as opposed to using postgres' 
default sequences, but it ought to work.

Will


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

RE: joining table sql generation error

[Stephen Haberman <st...@chase3000.com>]

Could you try applying this patch to the postgresql columns.vm and see
if it works?

Basically, anytime columns are generated, the last two chars are chopped
off. So columns.vm should generate:

"column1, column2, "

And then whoever called columns.vm should chop off the last space and
comma. However, this reference that is being put in interferes with the
chopping. I've moved to be above the column output.

Let me know how it goes.

Thanks,
Stephen

cvs diff src\templates\sql\base\postgresql\columns.vm Index:
src/templates/sql/base/postgresql/columns.vm
===================================================================
RCS file:
/home/cvs/jakarta-turbine-torque/src/templates/sql/base/postgresql/colum
ns.vm,v
retrieving revision 1.3
diff -r1.3 columns.vm
36,37d35
< #set ( $entry = $strings.collapseSpaces("$entry,") )
<     $strings.sub($entry," ,",",")
42a41,42
> #set ( $entry = $strings.collapseSpaces("$entry,") )
>     $strings.sub( $entry , " ," , "," )




--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>