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!

Extracting files and data from OLE Object fields

OLE Object fields are commonly used by applications using Access as the back-end database to store files and common document formats.  It is convenient because Windows handles how the files or documents are opened, modified, and saved via OLE servers.  However, it is difficult to extract the data from those fields because of the additional OLE information embedded together with your data.

For example, let’s create a table in Access, and store a simple Excel workbook, both containing the same data.

We create the first record by directly embedding an Excel workbook.

For the second record, we simply attach an existing Excel workbook.

In Access, we can open both workbooks easily simply by double-clicking on them.  This is the OLE servers at work.

Now if we take a look at the size of the 2 records using SQL Blob Viewer, we can see that the size of the embedded workbook is larger than the attached file.  The size of the attached file record is also larger than the original file size, because Access needs to add additional data to the file.

If you try to export the data as is, you will not be able to open the exported files, because the format itself is not Excel-compliant.  Both records in the OLE Object fields have had additional OLE wrappers added to them.

The usual way to extract the content is to open each item individually in Excel, and save them to files.  This is a tedious process if you have a lot of records you need to extract.

We have 2 products, SQL Blob Viewer and Access OLE Export, that can remove the OLE wrappers for data stored in OLE Object fields, and export them to disk.

SQL Blob Viewer is for technical users who are comfortable writing SQL queries to retrieve the required data, and Access OLE Export is for less technically inclined users who just want to be able to select a table and export their data quickly.  Both products can identify embedded Office document content, images, PDF content, Open Document content, and other common binary types.  Both products can also extract data from OLE Object fields used in other database engines like SQL Server, MySQL, Oracle, PostgreSQL, Firebird, SQLite, and ODBC data sources.

There may be situations where both products are unable to identify embedded content correctly.  This may be because the registered OLE Server for that content type is not yet supported.  For e.g. PDF files can have different OLE servers like Adobe Acrobat, Foxit PDF Reader. Nitro, etc.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Why are both 32-bit and 64-bit versions of Access OLE Export installed

When you install Access OLE Export, you find that both the 32-bit and 64-bit versions are installed.

This is because when you access an Access database, you will need to use the 32-bit version of Access OLE Export if you have installed the 32-bit version of Microsoft Office on your machine, or if you have the 32-bit version of the Access Database Engine 2010 Redistributable installed.  If you have installed the 64-bit version of Office, or the 64-bit version of the Access Database Engine 2010 Redistributable, you will then need to use the 64-bit version of Access OLE Export.

The Access Database Engine Redistributable itself needs to match the version of Office that’s installed.  For example, if you have the 32-bit version of Office installed, and you try to install the 64-bit version of the Engine, you are prevented from doing so.

If you selected the wrong version of Access OLE Export to access your Access database, the following message is displayed.

Simply use the other version then.  Alternatively, you can use the Access OLE Export (Access mode) shortcut which will start the appropriate version of Access OLE Export depending on which version of Office is installed on your machine.

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.

SQL Multi Select use case

If you have manage multiple databases in your work, there would have been occasions where you needed to run the same set of queries against multiple databases or database servers.  The regular and tedious way to do this would have been to connect to each database/server, run your query, log the results, and move on to the next database/server.

SQL Multi Select removes the tedium of the above process.  Basically, you set up a list of all the servers you manage in your environment.  You then group these servers by function (each server can exist in multiple groups).  For example, you may have a group containing just your development servers, another group for your production servers etc.

Now, you can run a query, or a set of queries, against these server groups.  The results of these queries are consolidated in a single result set, so you can easily compare the results across different servers.  For ad-hoc queries, this makes it easy to run the same query on multiple database/servers.

You may also have queries you run repeatedly e.g. queries to check database sizes, server statuses etc.  You can use the SQL Multi Select command line interface (available only in the Standard and Professional editions) to run these queries in a scheduled job, and have the results saved in text files or in a SQLite database.

SQL Multi Select supports PostgreSQL, MySQL, and Oracle databases.  All server groups will contain servers of the same type, so for example, you cannot mix MySQL and Oracle servers in the same query group.

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.

SQL Blob Viewer and SQL Image Viewer

So we have 2 very similar products, SQL Blob Viewer and SQL Image Viewer, and here’s why.

SQL Image Viewer was released 9 years ago, and over the years, it has accumulated a lot of code that is user-specific.  When we made the decision to create a 64-bit version of SQL Image Viewer, we discovered that it would be pointless to port those user-specific functions over too.  We did not want to leave our existing customers with a new version that did not have those functions, so we renamed the new product SQL Blob Viewer.

SQL Blob Viewer has almost the same feature set as SQL Image Viewer.  The most obvious difference is how it displays images – it does this by displaying the images together with the textual data –

while SQL Image Viewer displays images separate from the textual data (we’re still gathering feedback on whether users prefer the SQL Image Viewer way of displaying images).

SQL Blob Viewer can also embed images in exported Excel spreadsheets, has a user-friendlier interface to configure incremental exports, supports 64-bit versions of Access, and supports larger data sets with the 64-bit versions.  The plan is to improve on SQL Blob Viewer, while SQL Image Viewer will be updated only with bug fixes.  As both products were developed using different tools, it would be too much work to maintain 2 code bases.

In the near future, probably when we’ve decided if we should support the same views as SQL Image Viewer, we will release SQL Blob Viewer as SQL Image Viewer (new), as the name SQL Image Viewer seems to attract more traffic (I suppose SQL Blob Viewer is just a tad too technical).  When that happens, you will still be able to install SQL Image Viewer (new) alongside SQL Image Viewer (old), so you can still use both versions concurrently.  Existing users with valid licenses can request for a SQL Image Viewer (new) license.

I would encourage you to give SQL Blob Viewer a try if you have not already done so, and let us know what you think.  If you’re a SQL Image Viewer user with a valid maintenance license, contact us at sales@yohz.com for a SQL Blob Viewer license.

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