## ============================================================================
## Filename : SQLCreateTableTemplate.vm
## Note(s)  : This template is used to generate SQL statement(s) to create a table.
##
## Copyright (c) 2007-2008 ThinkUI Software Inc. All rights reserved.
## ============================================================================
##
## ****************************************************************************
## The following variables are available in this template.  For more
## information on any of the following variable, please refer to the user guide.
## ****************************************************************************
## $projectName
## $authorName
## $headerText
## $tableName
## $schemaName
## $prjPkgName
## $prjClassPrefix
## $dataClass
## $date
## $targetDatabase
## $sequenceName
## $fkRelationships
## $codeGen
## $dbGen
## $stmtSeparator
## $sequenceStartValue
##
###if(${projectName})
##-- Project  : $projectName
###end
##-- History  : ${date} ${authorName} Created
###if(${targetDatabase})
##-- Database : $targetDatabase
###end
###if(${headerText})
##-- $headerText
###end
#if ($targetDatabase.equals("POSTGRESQL"))CREATE SEQUENCE $sequenceName START ${sequenceStartValue}${stmtSeparator}
#set( $tableName = $tableName.toLowerCase() )
#set( $autoIncr = " DEFAULT nextval('$sequenceName')" )
#elseif ($targetDatabase.equals("MYSQL"))
#set( $tableName = $tableName.toLowerCase() )
#set( $autoIncr = " AUTO_INCREMENT" )
#elseif ($targetDatabase.equals("CLOUDSCAPE"))
#set( $tableName = $tableName.toLowerCase() )
#set( $autoIncr = " GENERATED BY DEFAULT AS IDENTITY (START WITH ${sequenceStartValue})" )
#elseif ($targetDatabase.equals("ORACLE"))CREATE SEQUENCE $sequenceName START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 5${stmtSeparator}
#set( $tableName = $tableName.toUpperCase() )
#set( $autoIncr = "" )
#elseif ($targetDatabase.equals("DB2"))
#set( $tableName = $tableName.toLowerCase() )
#set( $autoIncr = " GENERATED ALWAYS AS IDENTITY (START WITH ${sequenceStartValue})" )
#elseif ($targetDatabase.equals("MSSQLSVR"))
#set( $tableName = $tableName.toLowerCase() )
#set( $autoIncr = " IDENTITY(${sequenceStartValue},1)" )
#else
#set( $tableName = $tableName.toUpperCase() )
#set( $autoIncr = "" )
#end
CREATE TABLE ${tableName} (
#set( $firstItem = 1 )#foreach( $attr in $dataClass.getAttributes() )
#if ($targetDatabase.equals("ORACLE"))
#set( $columnName = ${codeGen.getColumnName($attr).toUpperCase()} )
#else
#set( $columnName = ${codeGen.getColumnName($attr).toLowerCase()} )
#end
#if ($firstItem == 1)
#set( $firstItem = 0 )
  #else  ,#end$columnName ${dbGen.getDBColumnTypeClause($attr)}#if (${codeGen.isAutoIncPKAttrName($dataClass, $attr.getName())})$autoIncr#end

#end

  ,CONSTRAINT ${tableName.toLowerCase()}_pk PRIMARY KEY (${codeGen.getColumnNames($dataClass, $dataClass.getPrimaryKeyAttrNames())})
#set( $fkCount = 0 )
#foreach( $relationship in $fkRelationships )
#set( $fkCount = $fkCount + 1 )
  ,CONSTRAINT ${tableName.toLowerCase()}_fk${fkCount} FOREIGN KEY (${codeGen.getColumnNames($dataClass, $relationship.getFromAttrNames())}) REFERENCES $relationship.getToDataClassName()(${codeGen.getRelatedColumnNames($relationship)})
#end
)${stmtSeparator}
#if ($targetDatabase.equals("ORACLE"))
CREATE OR REPLACE TRIGGER IBR_${tableName}
  BEFORE INSERT ON ${tableName}
  REFERENCING OLD AS old NEW AS new
  FOR EACH ROW
BEGIN
  IF (:new.id IS NULL) THEN
##  SELECT ${sequenceName}.NEXTVAL INTO :NEW.ID FROM DUAL;
    EXECUTE IMMEDIATE 'SELECT ${sequenceName}.NEXTVAL FROM DUAL' INTO :new.id;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,100));
END${stmtSeparator}#end