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

#**
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  The ASF licenses this file to You
  under the Apache License, Version 2.0 (the "License"); you may not
  use this file except in compliance with the License.
  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.  For additional information regarding
  copyright in this work, please see the NOTICE file in the top level
  directory of this distribution.
*#
#**
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

#**
Rename a table column.
**#
#macro(alterColumnName $tableName $columnName $newColumnName $columnType)
#if ($db.DBTYPE == "MSSQL")
sp_rename '$tableName.$columnName', '$newColumnName', 'COLUMN';
#elseif ($db.DBTYPE == "HSQLDB")
alter table $tableName alter column $columnName rename to $newColumnName;
#elseif ($db.DBTYPE == "MYSQL")
#** MySQL 5.7 and earlier need this syntax **#
alter table $tableName change $columnName $newColumnName $columnType;
#else
alter table $tableName rename column $columnName to $newColumnName;
#end
#end