T4SQL is a SQL Code Generation implemented by
Preprocessed T4 Text Templates
(C# or Visual Basic).
Two initiative scenarios of using T4SQL:
- 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);
- 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):
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
- 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.
A built-in Generic View Library is shipped with the release, also as a sample to make your own generic SQL libraries.
T4SQL mainly consists of three modules:
T4SQL Workspaces, Template Engine and
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
table, including all necessary constraints (FK, PK…), triggers and a helper view for you.
(…) is a handy stored procedure to generate all objects which match the search criteria into a single script and prints on Message Screen.
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
connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=T4SQLDB;Integrated Security=True"
- Different config for Oracle
connectionString="Data Source=ORCL;User Id=/"
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.
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.
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
which prepares all necessary scaffolding for you.
Once the T4SQL.Base
package is installed into your class library project, the
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:
- Add a new T4SQL Template item into the class library project,
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.
- 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.
A good way to create a T4 SQL text template is to convert an existing tested example of the SQL script.
||Don't need to make any changes in this master file unless you want to support more databases other than SQL Server and Oracle.
||This code is generated by TextTemplatingFilePreprocessor, you can't do anything in this file.
||The SQL text template for Oracle.
||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.
||The SQL text template for SQL Server.
The following figure shows their internal relationships:
- 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
The following are naming conventions for T4SQL Template class name (.tt file name).
||PL/SQL Package (Oracle)
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
as T4SQL Template name in their T4SQL workspaces, so it’s necessary to keep names simple.
- 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.
- 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:
- 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.