## ============================================================================
## Filename : SQLCreateTableTemplate.vm
## Note(s)  : This template is used to generate SQL statement(s) to create a table.
##
## Copyright (c) 2007-2009 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
## $includeComments
## $codeGen
## $dbGen
## $stmtSeparator
## $sequenceStartValue
##
#if ($targetDatabase.equals("POSTGRESQL"))
#set( $autoIncr = " DEFAULT nextval('$sequenceName')" )
#elseif ($targetDatabase.equals("MYSQL"))
#set( $autoIncr = " AUTO_INCREMENT" )
#elseif ($targetDatabase.equals("HSQLDB"))
#set( $autoIncr = " IDENTITY" )
#elseif ($targetDatabase.equals("CLOUDSCAPE") || $targetDatabase.equals("H2"))
#set( $autoIncr = " GENERATED BY DEFAULT AS IDENTITY (START WITH ${sequenceStartValue})" )
#elseif ($targetDatabase.equals("ORACLE"))
#set( $autoIncr = "" )
#elseif ($targetDatabase.equals("DB2"))
#set( $autoIncr = " GENERATED ALWAYS AS IDENTITY (START WITH ${sequenceStartValue})" )
#elseif ($targetDatabase.equals("MSSQLSVR"))
#set( $autoIncr = " IDENTITY(${sequenceStartValue},1)" )
#else
#set( $autoIncr = "" )
#end
#if ($targetDatabase.equals("POSTGRESQL"))CREATE SEQUENCE $sequenceName START ${sequenceStartValue}${stmtSeparator}#elseif ($targetDatabase.equals("ORACLE"))CREATE SEQUENCE $sequenceName START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 5${stmtSeparator}CREATE OR REPLACE TRIGGER IBR_${codeGen.dbName($tableName).toUpperCase()}
  BEFORE INSERT ON ${codeGen.identifier($schemaName)}.${codeGen.identifier($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
CREATE TABLE ${codeGen.identifier($schemaName)}.${codeGen.identifier($tableName)} (
#set( $firstItem = 1 )#foreach( $attr in $dataClass.getAttributes() )
#set( $columnName = ${codeGen.identifier($codeGen.getColumnName($attr))} )
#if ($firstItem == 1)
#set( $firstItem = 0 )
  #else  ,#end$columnName ${dbGen.getDBColumnTypeClause($attr)}#if (${codeGen.isAutoIncPKAttrName($dataClass, $attr.getName())})$autoIncr#end

#end

  ,CONSTRAINT ${codeGen.dbName($tableName)}_pk PRIMARY KEY (${codeGen.getColumnNames($dataClass, $dataClass.getPrimaryKeyAttrNames())})
#set( $fkCount = 0 )
#foreach( $relationship in $fkRelationships )
#set( $fkCount = $fkCount + 1 )
  ,CONSTRAINT ${codeGen.dbName($tableName)}_fk${fkCount} FOREIGN KEY (${codeGen.getColumnNames($dataClass, $relationship.getFromAttrNames())}) REFERENCES $relationship.getToDataClassName()(${codeGen.getRelatedColumnNames($relationship)})#end)${stmtSeparator}#if ($includeComments)
#if (!$codeGen.isEmpty($dataClass.getDescription()))
COMMENT ON TABLE ${codeGen.identifier($schemaName)}.${codeGen.identifier($tableName)} IS $codeGen.singleQuote($dataClass.getDescription());
#end
#foreach( $attr in $dataClass.getAttributes() )
#if (!$codeGen.isEmpty($attr.getDescription()))
COMMENT ON COLUMN ${codeGen.identifier($schemaName)}.${codeGen.identifier($tableName)}.${codeGen.identifier($codeGen.getColumnName($attr))} IS $codeGen.singleQuote($attr.getDescription());
#end
#end
#end