i just tried the upgrade script on hsql and it didn't work properly.
hsql complained about the "alter table foo alter" lines.
is there a reason why we are doing it in multiple lines rather than in a
single line? i.e.
i currently see ...
alter table roller_comment add column approved bit;
alter table roller_comment alter approved set default 1;
alter table roller_comment alter approved set not null;
and i had to manually change that to ...
alter table roller_comment add column approved bit default 1 not null;
in any case, i can see that by properly defining the addColumn() macro
it should make the process a bit easier.
-- Allen
On Mon, 2005-12-05 at 08:41, Dave Johnson wrote:
> I'm hearing only +1 votes so I'll commit this stuff later today.
>
> Here are some more details about Velocity based script generation.
>
>
> On Dec 5, 2005, at 10:29 AM, Elias Torres wrote:
> > It looks good, but so far it's just variable replacement. How does one
> > go about modifying a specific table/column definition for a database?
> > Would I have to parse the control sql myself? I only see a one liner
> > for parsing/var replacement. But even then, my worry is that a
> > developer can drift away from the control sql enough to cause some
> > damage.
>
> Adding a column with a default and a null constraint is the ugly case,
> but it's a *lot* less ugly than what we had before. With Velocity we can
> encapsulate the ALTER TABLE ugliness in a macro.
>
> Here's the current addColumn() macro in my 200-to-210 script:
>
> #macro( addColumn $table $column $type $default $notnull)
> alter table $table add column $column $type;
> #if( $DBTYPE == "MYSQL" || $DBTYPE == "ORACLE")
> alter table $table modify $column $type default $default;
> #if ($notnull == true)
> alter table $table modify $column $type not null;
> #end
> #else
> alter table $table alter $column set default $default;
> #if ($notnull == true)
> alter table $table alter $column set not null;
> #end
> #end
> #end
>
> That may not be right. I'm assuming that all databases other than MySQL
> and
> Oracle support SQL99 style ALTER TABLE syntax. I've checked this against
> MySQL and PostgreSQL. I'll need help checking against DB2, Derby, and
> Oracle.
>
> Anyhow, with that macro, the rest of the 200-to-210 script looks like
> this:
>
> -- Add to roller_comment table: approved and pending fields
> #addColumn("roller_comment" "approved" $BOOLEAN_SQL_TYPE $BOOLEAN_TRUE
> true)
> #addColumn("roller_comment" "pending" $BOOLEAN_SQL_TYPE $BOOLEAN_FALSE
> true)
> update roller_comment set approved=$BOOLEAN_TRUE,
> pending=$BOOLEAN_FALSE, posttime=posttime;
>
> -- Add to website table: commentmod, blacklist, defaultallowcomments
> and defaultcommentdays
> #addColumn("website" "commentmod" $BOOLEAN_SQL_TYPE $BOOLEAN_FALSE true)
> #addColumn("website" "defaultallowcomments" $BOOLEAN_SQL_TYPE
> $BOOLEAN_TRUE true)
> #addColumn("website" "defaultcommentdays" "integer" "7" true)
> #addColumn("website" "blacklist" $TEXT_SQL_TYPE "''" false)
>
> update website set commentmod=$BOOLEAN_FALSE,
> defaultallowcomments=$BOOLEAN_TRUE, defaultcommentdays=$BOOLEAN_FALSE,
> blacklist='', datecreated=datecreated;
>
>
> > in truth, I'm not sure that I see much of a difference between this
> > new method and the old one.
>
> True, the createdb template is almost identical to the old
> createdb-raw.sql.
>
> But, compare my commit yesterday with the above and I think you'll see
> the difference.
>
>
> > +1. Now get ready: "With great power comes great responsibility..."
>
> Of course, Elias is correct, this new power can corrupt. But we're good
> people, right?
>
> - Dave
>
