app/src/main/resources/sql/macros.vm

#**
Special macro to add table column with default null
**#
#macro(addColumnNull $table $column $type)
#if ($db.DBTYPE == "ORACLE" || $db.DBTYPE == "MSSQL")
    alter table $table add $column $type default null;
#else
    alter table $table add column $column $type default null;
#end
#end


#**
Special macro to add table column with not-null restriction and default value
**#
#macro(addColumnNotNull $table $column $type $default)
#if($db.DBTYPE == "MYSQL" || $db.DBTYPE=="HSQDB")
    alter table $table add column $column $type default $default not null;  
#elseif ($db.DBTYPE == "POSTGRESQL")
    alter table $table add column $column $type;
    alter table $table alter $column set default $default;
    update $table set $column=$default;
    alter table $table alter $column set not null;
#elseif ($db.DBTYPE == "HSQLDB")
    alter table $table add column $column $type default $default not null; 
#elseif ($db.DBTYPE == "DERBY" || $db.DBTYPE == "DB2")
    alter table $table add column $column $type with default $default not null;
#elseif ($db.DBTYPE == "ORACLE" || $db.DBTYPE == "MSSQL")
    alter table $table add $column $type default $default not null;  
#end
#end


#**
Special macro to drop NOT NULL requirement from an 'id' column.
**#
#macro(dropNotNullFromTableId $table)
#if($db.DBTYPE == "MYSQL")
    alter table $table drop primary key;
    alter table $table modify id varchar(48) null;
#elseif ($db.DBTYPE == "POSTGRESQL")
    alter table $table drop constraint "${table}_pkey";
    alter table $table  alter column id drop not null;
#elseif ($db.DBTYPE == "HSQLDB")
    alter table $table alter column id varchar(48) null;
#elseif ($db.DBTYPE == "DERBY" || $db.DBTYPE == "DB2")
    alter table $table drop primary key;
    alter table $table alter column id null;
#elseif ($db.DBTYPE == "ORACLE")
    alter table $table drop primary key; 
#end
#end


#**
Special macro to drop NOT NULL requirement from a column.
**#
#macro(dropNotNullFromColumn $table $colname $coltype)
#if($db.DBTYPE == "MYSQL")
    alter table $table modify $colname $coltype null;
#elseif ($db.DBTYPE == "POSTGRESQL")
    alter table $table  alter column $colname drop not null;
#elseif ($db.DBTYPE == "HSQLDB" || $db.DBTYPE == "MSSQL")
    alter table $table alter column $colname $coltype null;
#elseif ($db.DBTYPE == "DERBY" || $db.DBTYPE == "DB2")
    alter table $table alter column $colname null;
#elseif ($db.DBTYPE == "ORACLE")
    alter table $table alter column $colname null; 
#end
#end


#**
Define non-null column with default value
**#
#macro(columnNotNullWithDefault $columnName $columnType $defaultValue)
#if($db.DBTYPE == "ORACLE" || $db.DBTYPE == "HSQLDB")
   $columnName      $columnType default '$defaultValue' not null
#else 
   $columnName      $columnType not null default '$defaultValue'
#end
#end

#**
Rename a table.
For Derby, will not work if there is a view or foreign key that references the table. (DERBY-6672)
**#
#macro(renameTable $oldTableName $newTableName)
#if ($db.DBTYPE == 'POSTGRESQL' || $db.DBTYPE == 'HSQLDB')
alter table $oldTableName rename to $newTableName;
#elseif ($db.DBTYPE == 'MSSQL')
sp_rename '$oldTableName', '$newTableName';
#else
rename table $oldTableName TO $newTableName;
#end
#end

#**
Macro to account for lack of comparable long varchar in MySQL
**#
#macro(longtext $size) #if($db.DBTYPE == "MYSQL") text #else varchar($size) #end #end


#macro(dropForeignKey $tableName $indexName)
#if ($db.DBTYPE == 'POSTGRESQL')
drop index $indexName;
#elseif ($db.DBTYPE == "DERBY" || $db.DBTYPE == "MYSQL")
alter table $tableName drop foreign key $indexName;
#else
drop index $indexName on $tableName;
#end
#end

#macro(dropIndex $tableName $indexName)
#if ($db.DBTYPE == 'POSTGRESQL')
drop index $indexName;
#elseif ($db.DBTYPE == "DERBY" || $db.DBTYPE == "MYSQL")
alter table $tableName drop index $indexName;
#else
drop index $indexName on $tableName;
#end
#end


#macro(dropColumn $tableName $columnName)
alter table $tableName drop column $columnName;
#end


#macro(dropTableIfExists $tableName) 
#if ($db.DBTYPE == "DERBY")
-- You should drop table $tableName if it exists
#else
drop table if exists $tableName;
#end
#end


#macro(concat $s1 $s2) 
#if ($db.DBTYPE == "MSSQL") 
	$s1 + $s2 
#elseif ($db.DBTYPE != "MYSQL")
	$s1 || $s2 
#else 
	concat($s1, $s2) 
#end 
#end


#macro(alterColumnType $tableName $columnName $columnType)
#if($db.DBTYPE == "MYSQL" || $db.DBTYPE == "ORACLE")
alter table $tableName modify $columnName $columnType;
#elseif ($db.DBTYPE == "POSTGRESQL")
alter table $tableName alter column $columnName type $columnType;
#elseif ($db.DBTYPE == "DB2")
alter table $tableName alter column $columnName set data type $columnType;
#elseif ($db.DBTYPE == "HSQLDB" || $db.DBTYPE == "MSSQL")
alter table $tableName alter column $columnName $columnType;
#else
-- You should alter type of column $columnName of table $tableName to $columnType
#end
#end