foliantcontrib.dbdoc 0.1.10

Database documentation generator

Database Documentation Generator for Foliant

Static site on the picture was built with Slate backend together with DBDoc preprocessor

This preprocessor generates simple documentation based on the structure of the database. It uses Jinja2 templating engine for customizing the layout and PlantUML for drawing the database scheme.

Currently supported databases:

• PostgreSQL,
• Oracle,
• Microsoft SQL Server,
• MySQL.

Important Notice: We, here at Foliant, don't work with all of the databases mentioned above. That's why we cannot thoroughly test the preprocessor's work with all of them. That's where we need your help: If you encounter any errors during build; if you are not getting enough information for your document in the template; if you can't make the filters work; or if you see any other anomaly, please send us an issue in GitHub. We will try to fix it as fast as we can. Thanks!

Installation

Prerequisites

DBDoc generates documentation by querying database structure. That's why you will need client libraries and their Python connectors installed on your computer before running the preprocessor.

PostgreSQL

To install PostgreSQL simply run

$ pip3 install psycopg2-binary

Oracle

Oracle libraries are proprietary, so we cannot include them even in our Docker distribution. So if you are planning on using DBDoc to document Oracle databases, first install the Instant Client.

If you search the web, you can find ways to install Oracle Instant Client inside your Docker image, just saying.

Next install the Python connector for Oracle database

$ pip3 install cx_Oracle

Microsoft SQL Server

On Windows you will need to install MS SQL Server.

On Unix you will first need to install unixODBC, and then — the ODBC driver. Microsoft has a detailed instructions on how to install the driver on Linux and on Mac.

Install the Python connector for Microsoft SQL Server database

$ pip3 install pyodbc

MySQL

On Mac you can simply run

$ brew install mysql

On Linux you will have to install server and client packages, for example, with apt-get

sudo apt-get update
sudo apt-get install -y mysql-server libmysqlclient-dev

Finally, install the Python connector for Microsoft SQL Server database

$ pip3 install mysqlclient

Preprocessor

$ pip install foliantcontrib.dbdoc

Config

To enable the preprocessor, add dbdoc to preprocessors section in the project config:

preprocessors:
    - dbdoc

The preprocessor has a number of options:

preprocessors:
    - dbdoc:
        dbms: pgsql
        host: localhost
        port: 5432
        dbname: postgres
        user: postgres
        password: !env DBDOC_PASS
        doc: True
        scheme: True
        strict: False
        trusted_connection: False
        filters:
            ...
        doc_template: dbdoc.j2
        scheme_template: scheme.j2
        components:
          - tables
          - functions
          - triggers
        driver: '{ODBC Driver 17 for SQL Server}'

dbms : Name of the DBMS. Should be one of: pgsql, oracle, sqlserver, mysql. Only needed if you are using <dbdoc> tag. If you are using explicit tags (<oracle>, <pgsql>), this parameter is ignored.

host : Database host address. Default: localhost

port : Database port. Default: 5432 for pgsql, 1521 for Oracle, 1433 for MS SQL, 3306 for MySQL.

dbname : Database name. Default: postgres for pgsql, orcl for oracle, mssql for MS SQL, mysql for MySQL.

user : Database user name. Default: postgres for pgsql, hr for oracle, SA for MS SQL, root for MySQL.

password : Database user password. Default: postgres for pgsql, oracle for oracle, <YourStrong@Passw0rd> for MS SQL, passwd for MySQL.

It is not secure to store plain text passwords in your config files. We recommend to use environment variables to supply passwords

doc : If true — documentation will be generated. Set to false if you only want to draw a scheme of the database. Default: true

scheme : If true — the platuml code for database scheme will be generated. Default: true

strict : If true — the build will fail if connection to database cannot be established. If false — the preprocessor will skip the tag with warning. Default: false

trusted_connection : Specific option for MS SQL Server. If true - will use Windows Authentication (Trusted Connection) instead of username/password. Default: false. Requires proper ODBC driver configuration.

filters : SQL-like operators for filtering the results. More info in the Filters section.

doc_template : Path to jinja-template for documentation. Path is relative to the project directory. If not supplied — default template would be used.

scheme_template : Path to jinja-template for scheme. Path is relative to the project directory. If not supplied — default template would be used.

components : List of components to be added to documentation. If not supplied — everything will be added. Use to exclude some parts of documentation. Available components: 'tables', 'views', 'functions', 'triggers'.

driver : Specific option for MS SQL Server database. Defines the driver connection string. Default: {ODBC Driver 17 for SQL Server}.

Usage

DBDoc currently supports four database engines: Oracle, PostgreSQL, MySQL and Microsoft SQL Server. To generate Oracle database documentation, add an <oracle></oracle> tag to a desired place of your chapter.

# Introduction

This document contains the most awesome automatically generated documentation of our marvellous Oracle database.

<oracle></oracle>

To generate PostgreSQL database documentation, add a <pgsql></pgsql> tag to a desired place of your chapter.

# Introduction

This document contains the most awesome automatically generated documentation of our marvellous Oracle database.

<pgsql></pgsql>

To generate MySQL database documentation, add a <mysql></mysql> tag to a desired place of your chapter.

# Introduction

This document contains the most awesome automatically generated documentation of our marvellous SQL Server database.

<mysql></mysql>

To generate SQL Server database documentation, add a <sqlserver></sqlserver> tag to a desired place of your chapter.

# Introduction

This document contains the most awesome automatically generated documentation of our marvellous SQL Server database.

<sqlserver></sqlserver>

Each time the preprocessor encounters one of the mentioned tags, it inserts the whole generated documentation text instead of it. The connection parameters are taken from the config-file.

You can also specify some parameters (or all of them) in the tag options:

# Introduction

Introduction text for database documentation.

<oracle scheme="true"
        doc="false"
        host="11.51.126.8"
        port="1521"
        dbname="mydb"
        user="scott"
        password="tiger">
</oracle>

Tag parameters have the highest priority.

This way you can have documentation for several different databases in one foliant project (even in one md-file if you like it so). It also allows you to put documentation and scheme for you database separately by switching on/off doc and scheme params in tags.

Filters

You can add filters to exclude some tables from the documentation. dbdocs supports several SQL-like filtering operators and a determined list of filtering fields.

You can switch on filters either in foliant.yml file like this:

preprocessors:
  - dbdoc:
    filters:
      eq:
        schema: public
      regex:
        table_name: 'main_.+'

or in tag options using the same yaml-syntax:

<pgsql filters="
  eq:
    schema: public
  regex:
    table_name: 'main_.+'">
</pgsql>

List of currently supported operators:

operator	SQL equivalent	description	value
eq	=	equals	literal
not_eq	!=	does not equal	literal
in	IN	contains	list
not_in	NOT IN	does not contain	list
regex	~, REGEX_LIKE	matches regular expression	literal
not_regex	!~, NOT REGEX_LIKE	does not match regular expression	literal

Note: regex and not_regex are not supported with Microsoft SQL Server DBMS.

List of currently supported filtering fields:

field	description
schema	filter by database schema
table_name	filter by database table names

The syntax for using filters in configuration files is following:

filters:
  <operator>:
    <field>: value

If value should be list like for in operator, use YAML-lists instead:

filters:
  in:
    schema:
      - public
      - corp

About Templates

The structure of generated documentation is defined by jinja-templates. You can choose what elements will appear in the documentation, change their positions, add constant text, change layouts and more. Check the Jinja documentation for info on all cool things you can do with templates.

If you don't specify path to templates in the config-file and tag-options dbdoc will use default templates.

If you wish to create your own template, the default ones may be a good starting point.

• Default Oracle doc template.
• Default Oracle scheme template.
• Default PostgreSQL doc template.
• Default PostgreSQL scheme template.
• Default MySQL doc template.
• Default MySQL scheme template.
• Default SQL Server doc template.
• Default SQL Server scheme template.

Tests

For run tests, use:

./test_in_docker.sh --python-version "3.9" --db-type "mysql"

Options: --python-version <python-version> – Specifies Python version for test environment. Available_: 3.8, 3.9, 3.10 etc.

--db-type <db-type> – Chooses database type for testing. Available_: mysql, pgsql.

Usage Examples

# Basic usage with defaults
./test_in_docker.sh

# Specific Python and database
./test_in_docker.sh --python-version "3.10" --db-type "pgsql"

# Only change database type
./test_in_docker.sh --db-type "mysql"

# Only change Python version
./test_in_docker.sh --python-version "3.9"

What It Does:

1. Starts Docker container with specified Python version.
2. Initializes chosen database type with test data.
3. Runs test suite.
4. Cleans up resources after completion.
5. Returns exit code based on test results.

Notes

• Requires Docker installed;
• Test data is automatically loaded from test_data/ directory;
• Results are displayed in console with color formatting;
• Exit code 0 = success, 1 = test failures.

Troubleshooting

If you get errors during build, especially errors concerning connection to the database, you have to make sure that you are supplying the right parameters.

There may be a lot of possible causes for errors. For example, MS SQL Server may fail to connect to local database if you specify host as localhost, you have to explicitly write 0.0.0.0 or 127.0.0.1.

So your first action to root the source of your errors should be running a python console and trying to connect to your database manually.

Here are sample snippets on how to connect to different databases.

PostgreSQL

psycopg2 library is required.

import psycopg2

con = psycopg2.connect(
      "host=localhost "
      "port=5432 "
      "dbname=MyDatabase "
      "user=postgres"
      "password=postgres"
)

Oracle

cx_Oracle library is required.

import cx_Oracle

con = cx_Oracle.connect(
    "Scott/Tiger@localhost:1521/MyDatabase"
    encoding='UTF-8',
    nencoding='UTF-8'
)

MySQL

mysqlclient library is required.

from MySQLdb import _mysql

con = _mysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='password',
        db='MyDatabase'
    )

Microsoft SQL Server

pyodbc library is required.

import pyodbc

con = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=0.0.0.0,1433;"
    "DATABASE=MyDatabase;"
    "UID=Usernam;PWD=Password_0"
)

Microsoft SQL Server Authentication Issues

When using MS SQL Server, you have two authentication options:

1. SQL Server Authentication (username/password):

   trusted_connection: false
   user: your_username
   password: your_password
   

2. Windows Authentication (Trusted Connection):

   trusted_connection: true
   # no user/password needed
   

For Windows Authentication to work:

• Make sure your ODBC driver supports Trusted Connections.
• The account running Foliant must have proper database permissions.
• On Linux/Mac, you may need to configure Kerberos for cross-platform authentication.