Tag Archives: DB Doc

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!

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.