-
Notifications
You must be signed in to change notification settings - Fork 4
MOGLiCC Tutorial Part B2
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 - Correspondingly, we adapt the template file to use this information. the body of your Main.tpl contains two foreach loops. An outer one for the class descriptors and an inner our for the attribute descriptors. Replace the inner one by the following lines:
#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 - Ok, fine, but how can we add a second insert statement for the REMITTER table? Any idea? One approach could be to have for each statement a new class. Ok, try it and add a new REMITTER class to the model, 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. Well, we could rename the classes e.g. InsertStatement_1_Remitter and parse the table name from that class name, but this would lead to redundant information.
5 - What we do is to modify our data model that way:
model SQLInsertData
class REMITTER
attribute ID
metainfo fieldType number
metainfo tableValue1 123
metainfo tableValue2 2345
attribute NAME
metainfo fieldType alphanumeric
metainfo tableValue1 "Peter Pepper"
metainfo tableValue2 "Sally Salt"
class ORDER
attribute ORDERNUMBER
metainfo fieldType number
metainfo tableValue1 667788
metainfo tableValue2 991166
metainfo tableValue3 229911
attribute REMITTERID
metainfo fieldType number
metainfo tableValue1 123
metainfo tableValue2 2345
metainfo tableValue3 123
6 - Of course, we have to refactor our template file correspondingly. Unfortunately, the template code grows into something ugly:
#set( $classDescriptorList = $model.getClassDescriptorList() )
#foreach( $classDescriptor in $classDescriptorList )
#set( $attributeDescriptorList = $classDescriptor.getAttributeDescriptorList() )
#set( $commaSeparatedListOfAttributes = "" )
#set( $arrayOfCommaSeparatedListOfTableValues = [])
#foreach ($attributeDescriptor in $attributeDescriptorList)
#set( $columnName = $attributeDescriptor.name)
#set( $fieldType = $attributeDescriptor.getMetaInfoValueFor("fieldType") )
#set( $metaInfoList = $attributeDescriptor.getMetaInfosWithNameStartingWith("tableValue") )
#set( $indexOfInsertStatements = -1 )
# loop for each insert Statement to build for this class
#foreach ($metaInfo in $metaInfoList)
#set( $tableValue = $metaInfo.value )
#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
# the following velocity instructions find the insertStatement to which the current tableValue belongs
#set( $indexOfInsertStatements = $indexOfInsertStatements + 1 )
#if ( $arrayOfCommaSeparatedListOfTableValues.size() <= $indexOfInsertStatements)
# a new element for an additional insert statement has to be added to the array
#set( $forgetThisValue = $arrayOfCommaSeparatedListOfTableValues.add("") )
#end
#set( $commaSeparatedListOfTableValues = $arrayOfCommaSeparatedListOfTableValues.get($indexOfInsertStatements) )
# the following velocity instructions add the tableValue to the insertStatement where it belongs to
#set( $commaSeparatedListOfTableValues = $commaSeparatedListOfTableValues + $tableValue + ", ")
#set( $forgetThisValue = $arrayOfCommaSeparatedListOfTableValues.set($indexOfInsertStatements, $commaSeparatedListOfTableValues) )
#end
#set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes + $columnName + ", ")
#end
# Cut the last comma from the lists
#set( $numberOfChars = $commaSeparatedListOfAttributes.length() - 2 )
#set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes.substring(0, $numberOfChars) )
# loop that generates for each insert statement one output line
#foreach ($element in $arrayOfCommaSeparatedListOfTableValues)
#set( $numberOfChars = $element.length() - 2 )
#set( $element = $element.substring(0, $numberOfChars) )
INSERT INTO $classDescriptor.name ($commaSeparatedListOfAttributes) VALUES ($element);
#end
#end
Let's see what happens in the code: The number of insert statements is defined by the number of metainfo elements in the data model whose name start with tableValue'. We address this list with the method getMetaInfosWithNameStartingWith. We added a new inner loop which iterates on these insert statements. For each insert statement a variable commaSeparatedListOfTableValues exists that is stored in an array variable. The output is generated by iteraring on this array. Puh, Velocity code of this complexity is really hard to read. We will see in later parts, how we can increase the readability of template code.
7 - Now let's start MOGLiCC and see the result looks like that:
INSERT INTO REMITTER (ID, NAME) VALUES (123, 'Peter Pepper');
INSERT INTO REMITTER (ID, NAME) VALUES (2345, 'Sally Salt');
INSERT INTO ORDER (ORDERNUMBER, REMITTERID) VALUES (667788, 123);
INSERT INTO ORDER (ORDERNUMBER, REMITTERID) VALUES (991166, 2345);
INSERT INTO ORDER (ORDERNUMBER, REMITTERID) VALUES (229911, 123);
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 SQLInsertData
metainfo resultFileName
Additionally, the adapt the template header:
#set( $resultFileName = $model.getMetaInfoValueFor("resultFileName") )
@TargetDir <applicationRootDir>
@TargetFileName $resultFileName
@NameOfValidModel SQLInsertData
@CreateNew true
This example shows, that you can declare velocity variables not only in the body but also in the header of template file. Furthermore, metainfo elements can also be used on model level.
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 above, followed by 'insert.sql' and its done. Ok, this was it for this tutorial part!