Script Tokens - Schema-Smith/SchemaSmithEnterpriseDemos GitHub Wiki

A script token is a key/value pair where the key can be referenced surrounded with {{ }} pairs and the value will be substituted in its location. Script tokens allow you to variablize concepts and defer defining them until runtime. They are defined in product.json in the ScriptTokens key and can be used within it and any template.json definition. There are a number of tokens that are defined for you by the system:

• ProductName is set to whatever name you give your product.
• TemplateName is set to the name of the current template being quenched. Only available at the template scope.
• TableSchema is automatically added at the template level and contains a json array of all of the table json objects.
• TableSchema_<tamplatename> is added for each template in the product to make the table metadata for all templates available for use cases such as a common data dictionary.

If you were to define the following token in your product json

{
    "ScriptTokens": {
        "ReleaseVersion": "1.0.0"
    }
}

You could refer to the ReleaseVersion in your template definition to insert the current version of your software into some key value table.

{
  "VersionStampScript": "INSERT ProductVersions(Product, Version) VALUES( '{{ProductName}}', '{{ReleaseVersion}}')"
}

• Define script tokens in both the product and template json files. Tokens from the product level are available in every template but template level tokens are scoped to only that one template.
• Script tokens can be used within the sql scripting keys of the product.json and template.json files.
• Reference any script token within any sql script (including migration scripts and object scripts).

Query tokens can be defined like regular script tokens within either the product or template definition. They have a special prefix <*Query*> in the value of the token to denote this type. The sql query is executed for each database in the template, or once for a product and the first column from the resultset is collected with each row becoming a line in the string that is replaced.

For the following example:

{
    "ScriptTokens": {
        "DatabaseList": "<*Query*>SELECT [Name] FROM master.dbo.sysdatabases WHERE [Name] IN ('master', 'tempdb') ORDER BY [Name]"
    }
}

the value that would be in the key DatabaseList would be

master
tempdb

File tokens are similar to query tokens except that instead of query, a file is loaded into the value. There are two types of file tokens, file and binary. File has the prefix <*File*> and binary has <*BinaryFile*> in the value of the token to denote its type.

For the following example:

{
    "ScriptTokens": {
        "MyTableData": "<*File*>Tables/dbo.MyTable.data",
        "CLRFunction": "<*BinaryFile*>Files/CLR.dll"
    }
}

You could have json file content loaded in to the MyTableData token so that in a later script you could insert it into a table. Likewise for binary content, you can have a file loaded so you could register an assembly or insert an image into a table.

This is a hybrid of the file and query tokens. The content of the file is loaded and then executed as a query and the results are stored in the token just like a Query token would be. This allows taking more complex queries and putting them into a .sql file for easier editing with syntax highlighting. A Query File has the prefix <*QueryFile*>

For the following example:

{
    "ScriptTokens": {
        "MyGeneratedCode": "<*QueryFile*>Query Files/Generate select list for latest column definition.sql"
    }
}

The script located in the indicated file is loaded and executed like a query token to produce the output that will be added in place of the token.