T4SQL Template Library

Project Description
T4SQL is a SQL Code Generation implemented by Preprocessed T4 Text Templates (C# or Visual Basic).
Two initiative scenarios of using T4SQL:
  1. When designing a knowledge-based data analysis tool, the T4SQL can act as a generic method base subsystem which manages SQL-level objects with programmability in database (SQL Server or Oracle);
  2. When building a database application, if you prefer to separate particular data transformation logics from the business framework logics, to have a cleaner code. The T4SQL can act as a method-factory, designed for database developers to use generic SQL script library as easy as OOP Generic Class Library.

    In Branches-Leaves (Tree) pattern database development, to make a distinction between the context flowchart (Branches) and the particular data transformation (Leaves):
    • Business context logics look like a deciduous tree in winter. A clear highlighted Branches graphic would help our mind to control complexity all the time.
    • A generic encapsulated Leaves picture would help our mind to liberate ourselves from the repeat of similar labor as much as possible. The T4SQL can be used as a Leaves Factory.
    Driven by minimized configured parameters and metadata in the practical environment, every generated object is early binding rather than dynamic SQL. This allows database engine optimizer to prepare execution plans in advance and makes unit testing clearer and easier.

A built-in Generic View Library is shipped with the release, also as a sample to make your own generic SQL libraries.

Project Organization

T4SQL mainly consists of three modules: T4SQL Workspaces, Template Engine and Template Library.

Project Organization Chart
 

T4SQL Workspaces

Each workspace is a set of workitems in database, includes a WORKITEM table and a PROPERTY table. Database developers usually work on database directly, some team doesn't want their stuff be seen by other teams. It's necessary to create a autonomous workspace for them.
 
A workitem is a single database object (e.g. a View) which needs to be created or maintained.
 
Above TEMPLATE_NAME is the fully qualified name (including the namespace) of .NET class in Template Library. It is a crucial key for Template Engine to dispatch the specified Template to generate the object code. The TEMPLATE_NAME column is a foreign key tie to a global table T4SQL.TEMPLATE_CLASS which is maintained by Template Engine automatically (see also detail in upcoming Template Engine Section).
 
The START_BUILD is the control switch which tells Template Engine to turn on the code generation process for that workitem. After a request is completed, the engine will save the generated code into OBJECT_CODE column (if successful) or save the error message into COMPILED_ERROR column (if failed), and reset the START_BUILD to be off.
For each workitem there should be a series of working properties as parameters for engine to invoke a template. When a new workitem is added into the WORKITEM table, a trigger behind it will pre-copy all designed properties into below PROPERTY table with either ExampleValue (must be customized) or DefaultValue (can leave it as it is), you only need to update them as your particular requirement environment.
 
 
Above STRING_VALUE is the column which you need to update. It is template's responsibility to parse the text.
The LINK_STATE column is reserved for some case to pass the object state.
 
In practice, it's recommended to use a writeable view (just the same name as the PROPERTY table with a "VW_" prefix) since the view can give you reference information at a glance during editing above STRING_VALUE and LINK_STATE columns, such as property description, custom is a must or not, etc.
 
 
A utility stored procedure T4SQL.META_CREATE_WORKSPACE(…) is used for creating a new workspace, it will create a pair of WORKITEM table and PROPERTY table, including all necessary constraints (FK, PK…), triggers and a helper view for you.
 
T4SQL.CMD_BUILD_SCRIPTS(…) is a handy stored procedure to generate all objects which match the search criteria into a single script and prints on Message Screen.

 

Template Engine

T4SQL Template Engine is a Windows Service which acts as the dispatch center.


Please deploy installation scripts onto database side


and run setup.exe to install T4SQL Template Engine Service in Windows server side.


Make sure to modify the connectionStrings in T4SQL.EngineService.exe.config before start up the service.
T4SQL Template Engine will load all add-ins template libraries under the "Templates" subdirectory on startup.
When you get some new Template Libraries and want to plug them into your engine, just copy their .dll files into the "Templates" subdirectory and restart the engine service. The engine will upload templates' metadata into database T4SQL.TEMPLATE_CLASS table and T4SQL.TEMPLATE_SPEC table, all new templates are ready for every workspaces to use.
 

Template Library

T4SQL Template Library is a library of T4SQL Templates, each T4SQL Template Library presents as an add-in .dll for T4SQL Template Engine to load. A built-in T4SQL Template Library is shipped with the T4SQL Template Engine, including about ten T4SQL Templates in the initial release.
 

 
You would be interested in being a Supplier of T4SQL Template Libraries. The following section introduces how to author a T4SQL Template.
 

Template Authoring

Every T4SQL Template is made by Preprocessed T4 Text Template. To author a T4SQL Template, you can start with a .NET class which is generated by a Preprocessed T4 Text Template. A T4 Text Template becomes a T4SQL Template when its partial class conforms to a standard for T4SQL Template interaction. This standard is provided through the ITemplate interface. Any class that implements the ITemplate interface is a T4SQL Template.

 
In practice, there is a NuGet package T4SQL.Base which prepares all necessary scaffolding for you.
 
 
Once the T4SQL.Base package is installed into your class library project, the T4SQL.Base.dll will be added into project references, at the same time a Visual Studio Item Template "T4SQL Template" will be installed as well if it does not yet exist, this is a "template of template" to make the template authoring as simple as possible.
 
Although the scaffolding code initialized by Visual Studio Item Template - T4SQL Template is a database view object as default, however the object type is not limited to view. Your T4SQL Template can generate any database object types.

The following are naming conventions for T4SQL Template class name (.tt file name).
Prefix   Object Type
V View
P PL/SQL Package (Oracle)  
S Stored Procedure
F Function
T Table
Considering for compatibility with multiple database platforms (i.e. SQL Server and Oracle), It is recommended to limit object names in 30 characters.

For namespaces, just be well-organized and meaningful.
Database side developers will use the fully-qualified name (Namespaces.ClassName) as T4SQL Template name in their T4SQL workspaces, so it’s necessary to keep names simple.
 

Samples

Template Library Reference

At the moment, please refer to result data of test.VW_sample_properties and source code of the built-in T4SQL Template Library.

System Requirements

Contributions