Introduction
This is the third Yet Another Code Generator (YACGEN) article. This article gives a basic introduction to how schema properties can be populated using user defined code and field property layout customization.
Property Population
As indicated in my previous articles (here and here), YACGEN's core is the ability to inventory and store database schemas, which is then used to generate output through user defined templates. There are a number of standard database properties stored, such as field name, size, and data type. Otherwise, all other properties are not required or populated.
Any of the bolded field properties are populated by the database provider (currently, only SQL Server versions 2000 to 2008), as seen in the following figure:
If you have used YACGEN to populate a database, you will notice that a lot of non-bolded properties are populated. For example, the Alt Field Name property contains the field name with spaces added where appropriate (ProductID becomes Product ID). The Fixed property contains the field name with any white space or non-alphanumeric characters removed.
The table fields contain the plural and singular forms of the table name (e.g., Territory and Territories). So, how are these populated?
YACGEN can execute Python code against any table or field property during schema population. This functionality is provided by the Microsoft IronPython engine. This code is user defined, and can be modified at any time. To see what code is associated with a given property, go to the DB Layout Settings tab and select the Code tab:
Here, you will see a four column grid and a large text box with code. The first column represents the property to populate. The second contains the Python expression to evaluate. The third identifies if it's a field or table property. And, the last column identifies when the expression is evaluated. This can be on new items (first time they're populated), on existing fields (subsequent schema updates only), or both new and updates. The large text box below the grid contains the Python code.
The expression can be any valid IronPython expression that can perform operations on any YACGEN property. A list of these properties can be seen in the previous article. For example, the CharWidth2 and CharWidth3 properties get populated with the field's Width property, when first inventoried. There are more complex expressions such as PixelWidth, which is populated with the character width multiplied by 8 if less than 100 characters; otherwise, it's 400:
Width>100 and 400 or Width * 8
This is the Python equivalent to C# ternary operators, or iif in VB. However, if you look further, you will see an iif expression:
iif(tableLayout.Properties["IsView"] == "True" ,"",GetSQL())
For those who know Python, they will know iif is not a Python expression. The function is defined in the code text box, and looks like this:
def iif(expression,truecon, falsecon):
if expression == True :
return truecon
else:
return falsecon
So, you can create your own functions that can be called during schema population.
You can execute code to populate table properties as well. For example, the table property is populated with plural and singular forms of table names. This is useful when generating names for a single object instance (i.e., Product), or collection, or operation (Products or GetAllProducts). This worked well until recently I had a table ending in Address. The original function assumed if the name ended in 's', it was already in plural form. So, a change was made to the logic to reflect this. The new code looks like this:
def makePlural(fieldName):
if fieldName[-1] == "y":
fieldName = fieldName[:-1] + "ies"
elif fieldName[-1] != "s":
fieldName = fieldName + "s"
elif fieldName[-2:] == "ss":
fieldName = fieldName + "es"
return fieldName
If you enter a property that doesn't exist, it will add the property to the schema. This allows you to add custom properties. You can add as many custom properties you want. Once the property is added, it can be used in your templates by simply referencing the <#propertyname#> in your template. If you define a new field property, it will appear in the field properties second tab.
For example, you might want to create a property that stores the field name upper case. So, add a new row to the grid and enter UpperCaseFieldName as the property name. Enter the expression FieldName.upper(). Make sure to keep the property type flagged as field. Select to update all fields (new and existing). The result should look something like this:
Now, process an existing or import a new database schema. This will execute the expression against each field. Select any field and go to the second tab. You should see something similar to the following:
The property UpperCaseFieldName has been added to each field property. You can reference the property in templates by entering <#UpperCaseFieldName#>. You can also reference the property in any Python expression.
Property Layout
While you can access the new field property via the Properties box, you may wish to display it in its own text box or customize the display. You can change property labels and assign a different property. To do this, move to the DB Layout Settings tab and select the Property Settings sub-tab.
Select the existing property from the drop down. This is where the new property and label will be displayed. Enter a new label and property name to display. There is no drop down for the new property name since it might be a custom property. Using the earlier example, enter the custom property UpperCaseFieldName.
Save the configuration and restart YACGEN. You will now see your new label and property displayed at the location of the specified property.
You may simply wish to change the label but not change the property being displayed. Enter the same property under the New ID column as the Name column.
Conclusion
Hopefully, this provides a better idea on updating custom properties and layout. Future articles may include more information on how to access the YACGEN schema object model. Any feedback is appreciated, including (constructive) criticism. If you are going to rate less than 3, I'd appreciate feedback.
History
- Dec. 28th 2008 - First release.
