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
