We developed a database driven source code generation application written using a Microsoft Access front end using extensive Visual Basic for Applications (VBA) coding which automatically generates source code in a software development language. This application is based on user created template files which contain embedded SQL statements and content. The template files are used by the source code generator engine with the embedded SQL contained within used for making database queries and selecting database content to be written to the generator engine output source code files. The software development language is based on the contents of the user created templates, and can be any text based software development language.
The generality of the user created template files provides the flexibility of the code generator to be highly customizable in terms of the content of the source files that are produced and the software development language(s). This tool has been demonstrated to increase software development productivity substantially in an environment where repositories such as spreadsheets were previously being used to store common data referenced in the application source code base. Previously, changes made to the backend data store, a spreadsheet in this particular case, and had to be manually populated in source code files when changed leading to inefficiencies and mistakes.
Code Generation Workflow
The workflow of the code generation tool is as follows.
- A user selects a template or folder where code generation template files reside.
- After selecting the code generation templates, the user begins the code generation process.
- The code generator code generation engine parses through the selected templates one by one, performing database queries based on the embedded SQL in the templates to create dynamic content, and generates the final source code files consisting of dynamic content from the databases as well a fixed content from the templates.
- The automatically generated source code is then be compiled into a binary or executable application or module.
This tool is not a compiler itself, but instead a source code generation tool using a database backend for dynamic content which produces source code files that are then used by a compiler. The content generated is based on the templates, so is development language neutral and can be based on any database schema, however data is organized in an organization. The content of the embedded SQL can be any valid SQL including table joins (inner or outer), etc.
This application is used for circumstances where there is repetitive content that is used differently in the source code files, or where important content to the source code lends itself to storage and management in either a backend database or spreadsheet.
While the application user interface is Microsoft Access forms, the database backend could be a simple split Microsoft Access database or Microsoft SQL Server if more performance is required. Collaboration by a team is based on having multiple copies of the application front end on each developer’s workstation with a common database backend so that everyone shares the data. This distributed configuration is shown in Figure 1.
Figure 1: Distributed Database Configuration
One of the principal use cases for this tool is in supporting code generation in languages other than English for products which need International language support in their user interfaces. To support Internationalization, the code generation tool allows for exporting english language strings into an XLIFF (XML Localisation Interchange File Format) formatted file. XLIFF is an XML-based format created to standardize the way localizable data are passed between tools during a localization process and a common format for CAT tool files. The exported language file is then used in any other of a number of XLIFF editors by a native language translator user who then enters in the native language corresponding to the English language strings. The language translation workflow is shown in the Figure 2.
Figure 2: Language Translation Workflow
Once the language translation has been performed by the native language specialist, a translated XLIFF file is then available. The application provides an import capability for reading back into the database the International language strings translated by the language specialist. This process abstracts the localization skills from the English speaking engineering software development skills. Based on the content of the templates, the software developer can then generate source code in a particular non-English language.
The code generator application has internal support for parsing through and creating XLIFF formatted content. The database content is typical organized with columns consisting of an attribute id, value, short English language description, long English language description, units, type, ranges, and then a number of columns consisting of the translated language strings stored in Unicode format.