SQL files generation

[jdiazgon]

Expected behavior

As a developer and user of CobiGen, I want CobiGen to generate database schema (SQL) from entities or OpenAPI so that I can save time.

Description

I think it would be very easy to create some templates in order to generate database schema. For instance, having the following entity:

public class EmployeeEntity {

	@Column(name = "EMPLOYEEID")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long employeeId;

	@Column(name = "NAME")
	private String name;

	@Column(name = "SURNAME")
	private String surname;

	@Column(name = "EMAIL")
	private String email;
}

It could generate the following SQL file:

CREATE TABLE EMPLOYEE ( 
id BIGINT auto_increment, modificationCounter INTEGER NOT NULL, 
employeeid BIGINT auto_increment, 
name VARCHAR(255), 
surname VARCHAR(255), 
email VARCHAR(255), 
PRIMARY KEY (employeeid) 
); 

This is a feature that has been requested by multiple developers.

[hohwille]

My simple guess to create a template for this:

CREATE TABLE ${variables.entityName?upper_case} (
  ID                            DECIMAL(10,0),
  VERSION                       INTEGER NOT NULL,
<#list pojo.fields as field>
<#if !field.type?starts_with("List<") && !field.type?starts_with("Set<")>
  <#if field.type?contains("Entity") || field.type=='long' || field.type=='java.lang.Long'>
    <#assign type = 'DECIMAL(10,0)'>
  <#elseif field.type=='int' || field.type=='java.lang.Integer' || field.type=='java.time.Year' || field.type=='java.time.Month'>
    <#assign type = 'INTEGER'>
  <#elseif field.type=='java.time.Instant' || field.type=='java.sql.Timestamp'>
    <#assign type = 'TIMESTAMP'>
  <#elseif field.type=='java.util.Date'>
    <#assign type = 'DATE'>
  <#elseif field.type=='boolean'>
    <#assign type = 'NUMBER(1) DEFAULT 0'>
  <#else>
    <#assign type = 'VARCHAR2(255 CHAR)'>
  </#if>
  ${field.name?upper_case?right_pad(30)}${type},
</#if>
</#list>
  CONSTRAINT PK_${variables.entityName?upper_case} PRIMARY KEY(ID)
)

[jdiazgon]

Great, that is really helpful

[github-actions]

Stale topic. Please negotiate closing or discussing the relevance of this ticket.

[quantumfate]

I suggest splitting this issue into java and openapi as I assume that two separate template implementations will be necessary

[quantumfate]

https://github.com/devonfw/cobigen/pull/1202/files#diff-0da5b52561a61bd0e26c7255b64e480dbdf4f997f939c880b4541ed469c56986

sql util

[quantumfate]

TODO

Java Code Utility

• Add utility methods to SQLUtil.java
  • public Class<?> getTypeOfField(Class<?> pojoClass, String fieldName)
  • public boolean isCollection2(Class<?> pojoClass, String fieldName)
    • there is already a function that does that
  • public String getEntityTableName(String className)
  • private static List<Field> getAllFields(List<Field> fields, Class<?> cl)
  • public String getCanonicalNameOfField(String className, String fieldName)
    • rename this to getCanonicalNameOfFieldtype
  • public String getPrimaryKey(String className)
• SQL utility method that scans for existing files to create the correct devon H2 Database prefix
  • overwrite existing entity names with sql generated code
• write methods getSqlType and getSqlTypeMapping
  • this functionality is currently in functions.ftl defined as a macro but it would make more sense to implement it as utility for future sql templates like openAPI

Freemarker xml templating

• declare macros in functions.ftl
• prepare templates.xml to be H2 database conform with V000X_ prefix

Java Testing

• test classes in ../templates/devon4j/test/utils/resources/TestFirstEntity.java

[quantumfate]

SQL Script template

• parsing field.annotations to java functions
• type checks in methods

Layout

• every function has at least the following parameter: (Annotation[] annotations, String className, String fieldName)

Not a collection

• various annotations but not collection

Primary Key

• 'getPrimaryKeyType()'

Entity

• name for referenced column
• current entity name
• created foreign key list
• getForeignKeyName()
• getForeignKeyTable()
• getForeignKey()

Primitive

• if its not an entity -> doesn't need a function

• everything is collected in a column list

Collection

• various annotations that are collections
• Following annotations are either annotated with @JoinTable [delayed/not supported yet]
• if not @(JoinTable) mappedBy Option for biderectional mapping
• no direction
• getRefTableName()

@OneToMany

@OneToOne

@ManyToMany

@ManyToOne

Not yet supported

@JoinTable

• @JoinColumns
• @JoinInverseColumns

Script

• the script itself iterates all lists to build the sql script
• it created more tables based on the number of refTables annotated with