Tag Archives: DB Doc

Linux and Wine compatibility

Here at Yohz Software, we receive the occasional email asking if our PostgreSQL-centric products run on Linux.  Being Windows applications, our products will run on Linux but requires Wine (the software, not the drink).  DB Doc has been able to run on Ubuntu since 2014, and we recently installed and ran PgComment and SQL Multi Select on Ubuntu 17.04 and Fedora 25.

SQL Multi Select on Fedora 25 and PlayOnLinux:

DB Doc on Ubuntu and Wine.

There are the occasional GUI quirks, which we try to fix as soon as possible, which is why you sometimes see different text and/or images if you run the same application on Windows.  If you discover any quirks/inconsistencies we have overlooked, please do drop us a line at support@yohz.com.

PostgreSQL statistics

PostgreSQL has a neat set of pg_stat views from which you can obtain statistics for tables, indexes, etc.  From DB Doc 4.2 and PgComment 1.2 onwards, you can now view the database, table, and index statistics in the DB Doc reports and in PgComment object inspector.

In DB Doc, database statistics are displayed on the database properties page

table statistics are displayed on each tables’ properties page

and index statistics (idx_scan, idx_tup_read, idx_tup_fetch) are displayed as part of the index definitions.

If you choose not to display the statistics, just deselect the Display database, table, and index statistics option.

 

PgComment, our free tool to easily add/modify comments to your PostgreSQL objects, displays the statistics details in the object inspector window.

– database statistics

 

– table statistics

 

– index statistics

Table and index sizes in DB Doc 4

Starting from DB Doc 4, table and index objects now have size properties.

For tables, the following size properties are available:

  • total size
  • table size (excludes indexes and toast sizes)
  • indexes size
  • toast size

For indexes, there is a single size property.  In all the report templates, the size properties are displayed.

In PDF and DOC reports:

 

In HTML and CHM reports:

DB Doc also displays the largest 20 tables and indexes by size in the template reports.  As usual, you can always customize the number of tables/indexes to display, and the details shown.

In PDF and DOC reports:

 

In HTML and CHM reports:

 

Take a look at the completes samples available here: http://www.yohz.com/dbdoc_samples.htm

Documenting your PostgreSQL database

PostgreSQL has a pretty nifty feature to add comments to database objects, using the COMMENT function.  You can pretty much add comments to almost any objects, like tables, view, foreign keys, constraints, columns, etc.

You add a comment using the COMMENT ON … function, then retrieve the comment using the obj_description function for most objects.  For columns, you’ll need to use the col_description function.

All this is rather tedious if you plan to add/remove comments for a lot of objects.  To make this task easier, we have recently released a free product, PgComment, downloadable here.  With PgComment, you just connect to your database and the application will list out all the comments attached to your tables, columns, indexes, views, sequences, domains, and functions.

You can then easily modify the comments, and apply them to the database.  Changes are cached, and sent to the server in a single batch, allowing for faster editing locally.  With the schema displayed in a hierarchical tree, you get an overview of what’s been commented and what has not.  This makes it so much easier to document your PostgreSQL database.

Another feature of PgComment is that you can view the properties of your database objects.  For tables, you can see details like the owner, total size, index size, estimated rows etc.

For indexes, you can see details like the index definition, index size, and various other properties.

Displaying these properties should help you write better comments for your objects.

If you want to generate PDF or HTML reports of your PostgreSQL database schema, take a look at DB Doc.  With DB Doc, you can generate customizable PDF and HTML schema reports in just a few clicks.

How fast is DB Doc

DB Doc is our PostgreSQL database schema documentation tool, and we recently had the opportunity to work on a fairly large production database.  The database had 7 schemas, containing over 800 tables, over 17000 columns in total, 300+ sequences, and 350+ functions.  DB Doc was ran on the server with a single Intel I5 3 GHz CPU, and the disks were consumer-grade SATA disks.

To read the entire database schema, DB Doc took 15 seconds.  To generate the HTML documentation, which comprised over 1600 HTML pages, took 7 seconds.

7 seconds!

That means it took DB Doc less than 30 seconds to read the database schema and generate a 1600 page HTML report documenting that database.

To generate a PDF report that contained almost 11000 pages took 3 minutes 45 seconds.

11000 pages in 3 minutes 45 seconds!

Understandably, we are very proud of what we have achieved with DB Doc.  If you have a PostgreSQL database that DB Doc seems to be having problems documenting, please do drop us a line at support@yohz.com.  We’re pumped to improve on the above numbers even further!

Customizing DB Doc PDF and Word report templates

The PDF and Word reports in DB Doc share the same template.  Most of the elements in the PDF template are customizable.  To customize the report, click on the Edit button next to the Report template file name.

There are broadly 2 types of elements in the report templates:

  • text memos

Text memos are text elements you use to display static text or database values.  Double click on any text element in the template to bring up the memo editor.

In the memo editor, enter any static text you want to display

or enter a database schema value.  Text memos enclosed in square brackets are schema element values, and are retrieved from the schema that is being processed.

You can mix both static text and schema element values in the same text memo.  For e.g. in the Table of Contents page, there are a lot of text memos that display the schema element type, which is a static text, followed by the schema element name.

 The DB Doc help file contains a reference for all the schema elements you can use in your reports.  Once you’ve set up your text values, you can drag the element handles to resize the element, or reposition the element in the report.

  • graphic elements

Graphical elements are those images or icons you see displayed in the report template.  To modify the image, double click on the existing image.

This brings up the Picture editor.  Click on the Load icon to load your png, bmp, or jpeg image, then click on the OK button to display that image in your report.

The report is now updated to display the image you loaded.  You can resize and reposition the image by dragging the image or image handles.

If you want to fit the image into a smaller area, select the Stretched option in the object inspector.

Once you’ve made the changes. remember to save the template.  You can save the template under a new name to create a new template if you want to preserve the default template.  Remember to select the appropriate template when you generate your PDF report.

If you need any assistance customizing the template, please do drop us a line at support@yohz.com.

Customizing the logo and copyright message in the DB Doc PDF and Word reports

In DB Doc, the PDF and Word reports share the same templates.  On the Create docs page, select the PDF settings tab, and click on the Edit button.

This brings up the report editor page.

Double click on the YOUR LOGO HERE image object to display the picture editor.  Click on the Load button to load your company logo.  You can load any jpeg, png, or bmp image.

Once loaded, click on the OK button.

Your log is now displayed on the report.  Resize the logo, or reposition the logo as per your requirements.

To modify the copyright message, scroll down the report until you see the footer.  Double click on the footer to bring up the memo editor.

Modify the text, and click on the OK button.

The modified text is now displayed in your report.

To make these changes permanent, save the report template, either using the existing name, or under a new name.  If you save the template under a new name, remember to select that template when you generate the PDF report.

Customizing DB Doc HTML and CHM report templates

The DB Doc HTML and CHM reports share the same templates.  The report is generated by means of a Pascal-like scripting engine, so every single element is customizable.  The DB Doc help file contains a class reference for every schema element.

To edit the template, click on the Edit button next to the script file name value.

This brings up the script editor, which you can modify.

The script runs roughly in this order of procedures:

Main > CopyTemplateFiles > CreateHTMLPages

The Main procedure sets up some global values for the script, and also sets up the output files depending on whether it is generating a HTML or CHM file.

The CopyTemplateFiles procedure copies the icons and stylesheets from the DB Doc template folder into the selected HTML output folder.  If you want to modify the default icons, just replace the image files in the DB Doc images template folder (e.g. C:\Program Files (x86)\Yohz Software\DB Doc\template\images\).   The HTML and CHM outputs use only the 16×16 and 32×32 images.

The main work is performed in the CreateHTMLPages procedure.  This procedure creates the individual schema elements pages by calling the relevant procedures.  To generate the pages for each type of schema element, there are usually 3 steps:

  • the CreateListItemLink procedure is called to create the table of contents navigation structure for that element type
  • if that element type consists of multiple items like tables and views, a listing function is called to create a summary page e.g. CreateTableListingPage for tables, CreateViewListingPage for views
  • the details page for that element type is called e.g. CreateSchemaPage for schemas, CreateTablePage for tables

Within each of the procedures, you can modify the script to display different values depending on your needs.  Take the CreateTablePage function for example.  At the beginning, we create the HTML file for that table, and insert the standard header elements.

We want to display some details about that table, so we create a HTML table to display those details.

Next comes the columns.  We create a HTML table, set up its columns, and then iterate through all the columns, and display each columns’ properties.

The same process is repeated for indexes, check constraints, unique constraints, triggers, and foreign keys.  You can modify any element you want simply by modifying the script.  Once the page for this table has been completed, it’s then saved as a HTML file.

Every schema element that has a HTML page is named using a specific naming convention provided by the GetObjectDetailsPage function.

Using this function, you can get the name of a HTML page for a particular schema element, simply by passing the element as a parameter to this function.  For example, in the CreateTablePage function, when listing out the foreign keys, we display a link to the reference table using the GetObjectDetailsPage function by passing the reference table as the parameter.  It does not matter that the page may not have been created yet, because when it does get created, it will use the same naming convention provided by the GetObjectDetailsPage function.

So that’s generally how DB Doc uses scripting to create your HTML and CHM reports.  If you have had any experience coding or writing scripts, you should be able to modify the scripts quite easily.  As mentioned earlier, the class reference in the help file would assist you greatly in seeing what methods are available for each schema object type.

If you need any assistance customizing the template, please do drop us a line at support@yohz.com.

DB Doc 3.6 released

We’ve just released DB Doc 3.6, our PostgreSQL schema documentation tool.  Among the changes we’ve made are as follows:

  • added PostgreSQL SSL login option

If your PostgreSQL server requires that you use SSL for your connections, then select the Connect using SSL option.

If your PostgreSQL server requires that you use client certificates, then select the Connect using SSL option, and also provide the certificate file name, key file name, and optionally, the root certificate file name.

  • cancel the schema reading process

You can now cancel the schema reading process.  Simply click on the Cancel button while DB Doc is reading the database schema.

  • more informative progress messages

If your database has a large number of tables, reading the column definitions may take a while, and it might seem that DB Doc has stalled.  The most time-consuming process is when DB Doc reads the column definitions for each of your tables.  So now, every 5 seconds, DB Doc will report how many column definitions it has read.

Download the DB Doc installer from this link: http://www.yohz.com/downloads/dbdoc/DBDocSetup.zip