Contents Diagrams Roles Schemas Tables Views Procedures Functions

Database Help Framework
for Microsoft SQL Server

Version 1.0, September 10, 2018

Contents

Introduction

Database Help Framework

Database Help Framework allows editing database documentation in Microsoft Excel and generating the documentation in HTML format.

Also, it adds the hyperlinks to the online documentation into the Actions menu.

Using the framework gives the following benefits:

- Developers edit the documentation in Microsoft Excel, as plain text.
- The documentation is stored in a database.
- The edit forms and documentation include actual metadata about roles, schemas, objects, columns, and parameters.
- The documentation is integrated with the SaveToDB add-in.

You may use built-in SaveToDB Cell Editor (Options, Show Cell Editor) to edit the help:

The supplied help generation script supports the Markdown's formartting syntax for hyperlinks and images, H3 and H4 headers, bold and code tags.

This documentation is generated using this framework.

You may install, update, and remove Database Help Framework using SaveToDB Application Installer wizard.

You may generate a workbook to edit the help using the SaveToDB Application Workbooks wizard.

We are making a lot for developers.

We love to hear your feedback. Feel free to contact us.

Quick Start

1. Open Microsoft Excel, select the Database tab of the SaveToDB add-in.
2. Run Wizards, Application Installer.
3. Connect to your Microsoft SQL Server database and install Database Help Framework.
4. Run Wizards, Application Workbooks.
5. Generate the database_help.xlsx workbook.
6. Edit and save help for any object.
7. Click Actions, Generate Database Documentation.

Generating Documentation

You may get actual database help using the Actions menu.

To get the final documentation to publish online, use a batch file and a powershell script to get the final links and formatting.

Here is the batch file that we use to generate the planning application documentation:

@echo off
sqlcmd -S .\SQLExpress -E -d PlanningApp2 -y0 -Q "EXEC doc.xl_actions_database_documentation @language = 'en', @schema = 'x'" -f 65001 -o planning-application.htm
powershell.exe -file update-planning-application.ps1 planning-application.htm

To use this feature, run the PowerShell and set the execution policy to the Unrestricted:

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted

See details here.

Here is a content of the update-planning-application.ps1 file:

if ($args.Length -eq 0)
{
   echo "Usage: update-planning-application.ps1 <filename>"
   exit
}
(Get-Content $args[0]  -encoding UTF8 -Raw) |
   Foreach-Object {$_ -replace ' (http[^ <]*\.htm)'                            , ' <a href="$1" class="external">$1</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((xls)\.[A-Za-z_1-9]+)'            , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((doc)\.[A-Za-z_1-9]+)'            , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((logs)\.[A-Za-z_1-9]+)'           , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((dbo25|xls25)\.[A-Za-z_1-9]+)'    , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace ' (xls_users|xls_developers|xls_formats)'       , ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (doc_readers|doc_writers)'                    , ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (log_administrators|log_users)'               , ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (planning_app_(administrators|analysts|developers|users))', ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (xls) '                                       , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace ' (doc) '                                       , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace ' (logs) '                                      , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace ' (dbo25|xls25) '                               , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace '<p>#### *([^ \n]*)( *)([^ \n]*)( *)([^\n]*?)</p>' , '<h4 id="$1$3"><a name="$1$3"></a>$1$2$3$4$5</h4>'} |
   Foreach-Object {$_ -replace '<p>### *([^ \n]*)( *)([^ \n]*)( *)([^\n]*?)</p>'  , '<h3 id="$1$3"><a name="$1$3"></a>$1$2$3$4$5</h3>'} |
   Foreach-Object {$_ -replace '<p>```(\r\n)<br>'                              , '<p><code>$1'} |
   Foreach-Object {$_ -replace '<br>```(\r\n<br>)*</p>'                        , '</code></p>'} |
   Foreach-Object {$_ -replace '!\[(.*)\]\(([A-Za-z_\-1-9\\/:\.]*\.(png|jpg|gif))\)', '<img src="$2" title="$1" alt="$1" />'} |
   Foreach-Object {$_ -replace '\[([^\]\r\n]*)\]\((https://[^ \)\r\n]*)\)', '<a href="$2" class="external">$1</a>'} |
   Set-Content -Encoding UTF8 $args[0]

The script do the following:

- adds the external class to the http links;
- adds hyperlinks to database objects;
- adds hyperlinks to database roles;
- adds hyperlinks to database schemas;
- replaces ###Header to <h3>Header</h3>;
- replaces ####Header to <h4>Header</h4>;
- replaces ``` to <code> and </code>;
- replaces ![title](url) to <img src=<url> title="title" alt="title" />;
- replaces [title](url) to hyperlinks.

You may add your rules yourself.
You have to replace the planning application names to yours.

Change History

Version 1.0, September 10, 2018

This is the first release.

Diagrams

DiagramDescription
Diagram D01Database Help Framework Data Tables
Diagram D02Database Help Framework Configuration Tables

Diagram D01. Database Help Framework Data Tables

Database Help Framework allows editing database documentation in Microsoft Excel and generating the documentation in HTML format.

The doc.help table contains help topics.
The doc.help_sections table contains help sections like objects, schemas, roles, diagrams, pages, and properties.
The doc.history table contains database history topics.
The doc.history_sections table contains history sections like announcements, new features, improvements, and bug fixes.

The Excel application uses editable views to edit the table data.

Add users who can write the documentation to the doc_writers role.
Add users who can read the documentation to the doc_readers role.

Diagram D02. Database Help Framework Configuration Tables

Database Help Framework includes complete copies of SaveToDB Framework tables.

This allows using Database Help Framework as is, without additional requirements.

The doc.objects table contains the configuration how to save data changes back to a database.
It specifies the target tables and stored procedures for INSERT, UPDATE, and DELETE operations.

The doc.handlers table contains the handler configuration.
It configures action menu items and validation lists for framework objects.

The doc.translations table contains translations of database objects, columns, parameters, and generated help headers.

The doc.formats table contains Excel table formats for framework objects.
The SaveToDB add-in applies these formats in the first connection to database objects.
Users can save and reload table formats using SaveToDB Table Format Wizard or Save Table Format and Load Table Format menu items.

The doc.workbooks table contains a configuration to generate a workbook to edit the database help.
Use the Application Workbooks wizard to generate it.

Roles

RoleDescription
doc_readersThe role includes permissions to read the database documentation.
doc_writersThe role includes permissions to read and write the database documentation.

doc_readers

The role includes permissions to read the database documentation.

Assign this role to users who can read the documentation.

See actual database permissions in the doc.xl_actions_set_role_permissions procedure.

doc_writers

The role includes permissions to read and write the database documentation.

Assign this role to users who can write the documentation.

See actual database permissions in the doc.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
docThe schema contains Database Help Framework tables, views, and procedures.

doc

The schema contains Database Help Framework tables, views, and procedures.

Database Help Framework has two roles, doc_readers and doc_writers, which include required permission on object levels.

Contents

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.