MOGLiCC Tutorial Part B2

B2) Using attributes and metaInfos: Refining SQL table data

One goal of this tutorial part is to handle the values of the table columns differently depending on their type of data. Another goal is to introduce multiple insert statements for a single database table.

1 - At first, we add new information about the field types of the table columns to our model data. For the remitter name do it that way:

attribute NAME
    metainfo tableValue "Peter Pepper"
    metainfo fieldType alphanumeric

For the other attributes set the fieldType 'number'.

2 - Now, we use this information in the template file:

#foreach ($attributeDescriptor in $attributeDescriptorList)
	
    #set( $columnName = $attributeDescriptor.name)
		
	#set( $tableValue = $attributeDescriptor.getMetaInfoValueFor("tableValue") )
	#set( $fieldType = $attributeDescriptor.getMetaInfoValueFor("fieldType") )
		
	#if( $fieldType.equals("alphanumeric") )
		
		#set( $tableValue = "'" + $tableValue + "'" )
			
	#elseif( $fieldType.equals("number") )
		
		# do nothing
		
	#else
		
		Unkown fieldtype ($fieldType) used for attribute $attributeDescriptor.name of class $classDescriptor.name !
			
	#end

    #set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes + $columnName + ", ")
    #set( $commaSeparatedListOfTableValues = $commaSeparatedListOfTableValues + $tableValue + ", ")
		
#end

The else-branch with its detailed information output is important to get immediate feedback in case a new fieldType is used so that a solution can be implemented in a new ifelse branch. Try to use an unknown field type and have a look whether the generated output provides sufficient information to fix this problem if it will really occur at some later point of time.

3 - Restart MOGLiCC and the file 'insert.sql' will show you the desired result: "...VALUES (123, 'Peter Pepper')...

4 - To get a second insert statement for the REMITTER table we need to add a second REMITTER class to the model. Add one, start MOGLiCC and see the error.report file telling you: "Identical class name". This means, that the meta model of the StandardModelProvider does not allow identical class names in the same model file. Ok, we have to refactore the model a little bit.

5 - We could extent the class names by a prefix, e.g. class InsertStatement.1.Remitter and parse the table name from that class name in the template file. A nicer solution is to move the table name from the attribute name to a metainfo element of the class:

class InsertStatment.1
  metainfo tableName REMITTER
    attribute ID
      metainfo tableValue 123
      metainfo fieldType number

Adapt all class elements to this scheme. This will cause the class name to contain no sql information, but this doesn't play any role.

6 - Again, we have to adapt our template to the modification in the model file. For this purpose you have to replace the INSERT line of main.tpl file by the following two lines:

#set( $tableName = $classDescriptor.getMetaInfoValueFor("tableName") )
INSERT INTO $tableName ($commaSeparatedListOfAttributes) VALUES ($commaSeparatedListOfTableValues);

7 - With this scheme implemented, add as many classes as you want to your model file. For instance:

model InsertSQL

	class InsertStatment.1
	  metainfo tableName REMITTER
	    attribute ID
		  metainfo tableValue 123
		  metainfo fieldType number
	    attribute NAME
		  metainfo tableValue "Peter Pepper"
		  metainfo fieldType alphanumeric
	class InsertStatment.2
	  metainfo tableName REMITTER
	    attribute ID
		  metainfo tableValue 2345
		  metainfo fieldType number
	    attribute NAME
		  metainfo tableValue "Peggy Peeper"
		  metainfo fieldType alphanumeric
		  
	class InsertStatment.3
	  metainfo tableName ORDER
	    attribute ORDERNUMBER
		  metainfo tableValue 55443322
		  metainfo fieldType number
	    attribute REMITTERID
		  metainfo tableValue 123
		  metainfo fieldType number
	class InsertStatment.4
	  metainfo tableName ORDER
	    attribute ORDERNUMBER
		  metainfo tableValue 99118822
		  metainfo fieldType number
	    attribute REMITTERID
		  metainfo tableValue 2345
		  metainfo fieldType number
	class InsertStatment.5
	  metainfo tableName ORDER
	    attribute ORDERNUMBER
		  metainfo tableValue 66336633
		  metainfo fieldType number
	    attribute REMITTERID
		  metainfo tableValue 2345
		  metainfo fieldType number

This reveals the following generation result:

INSERT INTO REMITTER (ID, NAME) VALUES (123, 'Peter Pepper');
INSERT INTO REMITTER (ID, NAME) VALUES (2345, 'Peggy Peeper');
INSERT INTO ORDER (ORDERNUMBER, REMITTERID) VALUES (55443322, 123);
INSERT INTO ORDER (ORDERNUMBER, REMITTERID) VALUES (99118822, 2345);
INSERT INTO ORDER (ORDERNUMBER, REMITTERID) VALUES (66336633, 2345);

8 - When changing the name of the result file, we have now to modify the template file. Later on we will have more than one main template files. Therefore it is generally a good idea to hold this information in the model file. We could take the model name to build the name of the result file. Doing so, however, will cause a problem because the template property @NameOfValidModel must be adapted for each change of the result file name. To avoid this problem we better use a specific model metainfo element for naming the result file:

model InsertSQL
  metainfo resultFileName 

Additionally, the adapt the template header:

#set( $resultFileName = $model.getMetaInfoValueFor("resultFileName") )

@TargetDir <applicationRootDir>
@TargetFileName $resultFileName
@NameOfValidModel InsertSQL
@CreateNew true

This example shows, that the model can be addresses not only from the template body but also from the template header. Fine, but there occurs an error: "Error parsing model file 'SQLModelFile.txt': Problem in line 2: Missing value ". Ah, ok, we forgot the value of the new metainfo. Add a space to the end of line 2 followed by 'insert.sql' and its done. Ok, this was it for this tutorial part!

Back to the tutorial main page

Next tutorial part