Category Archives: DB Doc

High DPI support

We recently added high DPI support to some of our applications so that they render better when user displays are scaled to 125% or more.  We may have missed 1 or 2 items, so if you encounter any GUI elements that are oversized or undersized, we would appreciate it very much if you could let us know at support@yohz.com.

The applications we’ve added high DPI support for are:

Standards and audit compliance in PostgreSQL databases

In DB Doc 9, we made a couple of additions to our existing reports that help in ensuring compliance with company standards with regards to table and column definitions.

At the database level, there is now a listing of all the tables across all schemas.  This allows you to easily look for similarly named tables, and ensure that they conform to your naming standards.

Similarly, there is now a listing of all the table columns across all schemas.  This allows you to easily ensure that the column naming conventions are consistent, and that the data types are also consistent for similar columns.

At the schema level, there is also a listing for all the columns in the schema.

What can you do with these reports?

  • you get a quick overview of all tables across all schemas in your entire database, allowing you to compare attributes like naming convention, size, estimated rows, etc
  • you get a quick overview of all columns across the entire database and across all schemas, allowing you to easily check for compliance with naming convention and data types/domains
  • your database administrators and developers get a single point of reference of your database schema, which can be updated as frequently required using DB Doc’s command line execution

Customizing the reports

By default, all the above 3 listings are included in the HTML, XML, CHM, PDF, and DOC reports.  You can remove one or more of the above listings by simply omitting the relevant user defined values, as explained in the help file here.

You can also easily customize what is displayed in each of the listings using DB Doc’s advanced scripting and report template features.  For e.g. you could include the index size in the table listing, or display the nullable and unique attributes in the column listing

Give DB Doc a try now

Download the installer here and see how DB Doc can help you generate accurate and timely database documentation in minutes.  We have a walkthrough available here to guide you for new users.

Removing the functions listing and details from DB Doc PDF and Word reports

In this post, we’ll show you how to remove the functions listing and details from the DB Doc PDF and Word reports.  The final templates without the functions can be downloaded here:

We will be modifying the A4 – portrait template in this example.  There are 2 parts to this – we want to remove the functions listing:

and also the functions details:

 

First, select the default template and click on the Edit button.

 

Save this template under a new name, so that we can still use the default template and also this template that excludes the functions details.

 

To remove the listing, click on the TableOfContents tab.

 

Scroll down the report until you see the TOCFunctionHeader band.  Click on the band, and set its Visible property to False.

 

Repeat the same process for the 2 bands below the TOCFunctionHeader band i.e. click each time on the TOCFunction and TOCFunctionFooter bands, and set the Visible property to False.

 

Click on the Preview button to preview the report.

 

The functions listing should no longer appear.  That takes care of the listing.  To remove the function details, first click on the SchemaObjects tab.

 

Locate the FunctionsListingHeader band.  Again, you will need to set this band’s visibility to false, and that of the next 5 bands (DetailData4, FunctionGroupHeader, FunctionDetails, FunctionDetailsDefinition, and FunctionDetailsComment).

 

Click Preview again and the function details should no longer be displayed.  However, there are 2 empty pages between the sequences and trigger functions pages because the function bands start a new page when they print (even though they are not visible, the underlying code still runs).

To remove the first empty page ,select the FunctionsListingHeader band, and click on the Events tab.


Double click on the FunctionsListingHeaderOnBeforePrint item.

This brings up the code editor, where we can see that the report template starts the functions listing on a new page, causing us to have a blank page when the listing is not displayed.

 

To remove the blank page, comment out the Engine.NewPage; line by adding two forward slashes at the beginning of that line i.e.

 

Repeat the same process for the FunctionsGroupHeader band.  In the OnBeforePrint event, comment out the line that starts a new page.

 

You can follow the same steps if you want to remove any other sections from the report.  Note that the PDF and Word reports share the same template, so you only need to modify one template.

If you require further assistance, do drop us a line at support@yohz.com.

 

DB Doc 6 released

New in DB Doc 6 is improved support for PostgreSQL 11.x databases and XML output.

  • PostgreSQL 11.x databases

    There were some changes to the system tables in PostgreSQL 11, which meant DB Doc had to retrieve some object properties  from a different source.

  • XML output

    You can now generate your PostgreSQL documentation as XML files, in addition to HTML, PDF, CHM, and Word documents.
    The XML files are generated using a fully-customizable script, so you can modify the XML file to fit your requirements.  A customizable XSL stylesheet is also provided to render the XML files in your browser.

More details about DB Doc can be found here, and you can download a free 14-day trial using this link.

DB Doc 6 and XML files

New in DB Doc 6 is the option to export your PostgreSQL database schema in XML format.

We provide a script to generate the XML file, and also a sample XSL stylesheet to display the XML files in your browser.  The provided XML structure is very basic, and if you need to modify the XML structure, it’s very simple to do.

For example, let’s take a look at the XML file that’s generated for a view.

This is the script that generates the XML file for the properties section of the view.

Now let’s say you have an application that will process the XML file, and needs the ‘OID’ and ‘owner’ properties to be easily accessed.  The provided script generates these nodes as part of the ‘properties’ collection, which is great for a stylesheet, but not so easily accessed by an application that needs to read those values.

So we need to be able to generate 2 ‘types’ of XML files – one to be easily displayed, and one to be easily read.  We also don’t want to have to maintain 2 different scripts.

So first, let’s add the code to generate the ‘OID’ and ‘owner’ values in their own nodes.

At this point, our XML file will contain 2 nodes with the ‘OID’ and ‘owner’ values, like this.

We could leave it as is – our application will read the values it requires from the top-level node, while our stylesheet will read the values from the ‘properties’ collection and ignore the top-level node.

Or we could make the script leave out the redundant nodes depending on which version of the XML file we need.  We can do this using the DB Doc script user-defined values.

Let’s say the default output is to have the ‘OID’ and ‘owner’ values as part of the ‘properties’ collection.  When we want the other output, we will define a value named ‘PARSER’ as the first user-defined value.

In our script, we simply look for this user-defined value to generate the correct output.

Now every time we want to generate the XML file for our application to use, just enter the value PARSER as the first user-defined value, and DB Doc will generate the XML file accordingly.  Thus, we only need to maintain a single script file.

More details about DB Doc can be found here, and you can download a free 14-day trial using this link.

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!