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.
  • Workitems
 
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.
  • Working Properties
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.
  • Different config for SQL Server
    <connectionStrings>
        <add name="T4SQLDB" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=T4SQLDB;Integrated Security=True" />
    </connectionStrings>
    <appSettings>
        <add key="Engine_Package" value="T4SQL.ENGINE_" />
    </appSettings>
  • Different config for Oracle
    <connectionStrings>
        <add name="T4SQLDB" providerName="Oracle.ManagedDataAccess.Client" connectionString="Data Source=ORCL;User Id=/" />
    </connectionStrings>
    <appSettings>
        <add key="Engine_Package" value="T4SQL.ENGINE." />
    </appSettings>
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.
 
  • T4SQL.Base.dll, this class library provides:
    • ITemplate interface (for code generation) and ITemplateProperties interface (for publishing T4SQL Template properties metadata to database side workspaces).
    • Utility extension methods for string parse, collections, etc.
    • Database metadata retrieval, such as:
      Expanding or infering * to a explicit column list of a table;
      Traversing foreign key relation-chain for a root table.
  • Visual Studio Item Template:
    1. Add a new T4SQL Template item into the class library project,

      Notes:
      Please remain the file name extension .tt unchanged, and don't contain any spaces or punctuation in the main part of the file name. For example "My T4SQL Template.tt" would be incorrect, but "MyT4SQLTemplate.tt" is correct. The file name will be used as a class name in the generated code.
       
    2. When you save the .tt file, four subsidiary files .cs, .partial.cs, .SqlServer.sql and .Oracle.sql will be generated. To see these files in Solution Explorer, expand the .tt file node.
       File Description
       .tt Don't need to make any changes in this master file unless you want to support more databases other than SQL Server and Oracle.
       .cs This code is generated by TextTemplatingFilePreprocessor, you can't do anything in this file.
       .Oracle.sql The SQL text template for Oracle.
       .partial.cs This is a critical work on C# code, it's recommended to encapsulate all necessary variables and complex text preprocessor (which will be used in your T4 SQL text templates - .Oracle.sql and .SqlServer.sql) into this partial class, to achieve a better separation between the text presentation and the underlying logic. The scaffolding code will give you some sample for quick start.
       .SqlServer.sql The SQL text template for SQL Server.
      A good way to create a T4 SQL text template is to convert an existing tested example of the SQL script.

      The following figure shows their internal relationships:



       
    3. Since .SqlServer.sql file and .Oracle.sql file are included in .tt file, Visual Studio TextTemplatingFilePreprocessor can't detect any change in these .sql files. So after you make changes in .SqlServer.sql and/or .Oracle.sql file(s), please always re-generate .cs file by right-click the .tt file, and then click Run Custom Tool before Build or Rebuild the project.


       
  • Naming Conventions
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

  • T4SQL Workspaces Samples
    Enclosed in distribution files, a separate setup script test_create.sql is used to install a complete workspaces sample in the test schema, which includes workspace creation, workitem/properties settings and some example tables to test built-in T4SQL Templates.
     
  • Template Authoring Samples
    The built-in T4SQL Template Library is also a straightforward sample of template authoring. Please download the source code from [SOURCE CODE] tab.

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

  • Supported Databases:
    • SQL Server 2008 or later versions
    • Oracle (dev and test on version 11g R1 and R2)
  • Runtime Service:
    • .NET Framework 4.0 Client Profile or higher versions
  • Source Code:

Contributions

  • Welcome all feedback through the CodePlex project (through to-do templates list, comments, patches, or items in the Issue Tracker);
  • Looking for Clean-Style Developers, Testers and Editors.

Last edited Dec 9, 2013 at 10:55 PM by AbelCheng, version 83