Database Documentation Project

The Database Documentation Project allows users to generate descriptions for all main schema objects. It currently supports all data sources supported by Valentina Studio (except ODBC and MongoDB).

It is built entirely upon Valentina Reports and Valentina Forms technologies and is therefore contained within the Valentina Project.

It features a simple user interface for generating options.

After which, you can generate a report.

This article describes the creation process of this project so you can learn how forms and reports can work together. Valentina Studio PRO users can easily adjust the form and reports to their needs. You do not need Valentina Studio PRO though to use it, only if you want to edit it.

The overall project structure is set up by database type. If you open it within Valentina Studio PRO, this project is editable.

After opening the Project, Script_Startup executes and displays the main dialog, Form_Config.

The project contains the reports and their supporting objects (data sources, queries, and sub-reports) for each engine.

It is necessary to have different reports for each engine because of their different capabilities and limitations. Some differences include:

• Differences in the structure of the system databases
• Lack of the necessary information in the standard INFORMATION_SCHEMA database
• SQLite uses its own way to present information via PRAGMA statements and system tables.

Form_Config dialog is used to configure the documentation generation process. It contains the following main parts:

• Datasource configuration
• Report configuration
• Report preview

Once the dialog is shown, the connection selector is filled with values:

for( const it of app.connections )
  this.cmbConnection.addItem( it.icon, it.nameForViews, it );

The connection object is stored in the ComboBox widget using addItem method, making it easy to reference the selected item.

After selecting a connection, the database list is filled:

const conn = this.cmbConnection.itemData( this.cmbConnection.currentIndex );
if( conn )
{
  for( const db of conn.databases )
    this.cmbDatabase.addItem( db.icon, db.name, db );
}

As previously, the database object is passed to the addItem method.

The report configuration part consists of a set of checkboxes that allow the user to turn on/off certain parts of the schema documentation.

Depending on the type of the selected connections some of the checkboxes should be shown or hidden. Optional checkboxes are contained within additional widgets so it is easy to control their visibility.

const optionalCheckBoxes = [ 'wViewTriggers',  'wChecks', 'wFunctions', 'wTypes' ];

The names of the necessary widgets are defined for each connection type in an array (where an index is the type):

let factoryOptions = [];
 
factoryOptions[ CONNECTION_FACTORIES.VALENTINA ]        = [ 'wFunctions', 'wTypes' ];
factoryOptions[ CONNECTION_FACTORIES.VALENTINA_CLIENT ] = [ 'wFunctions', 'wTypes' ];
 
factoryOptions[ CONNECTION_FACTORIES.SQLITE ]        = [ 'wViewTriggers' ];
factoryOptions[ CONNECTION_FACTORIES.SQLITE_CLIENT ] = [ 'wViewTriggers' ];
 
factoryOptions[ CONNECTION_FACTORIES.MSSQL ] = [ 'wFunctions', 'wChecks', 'wViewTriggers' ];
 
factoryOptions[ CONNECTION_FACTORIES.MYSQL ] = [ 'wFunctions' ];
 
factoryOptions[ CONNECTION_FACTORIES.POSTGRESQL ] = [ 'wFunctions', 'wChecks' ];

Once the database is selected, the visibility of the optional checkboxes is adjusted:

for( const o of optionalCheckBoxes )
{
	const opts = factoryOptions[ db.connection.factory.factoryType ];
 
	this[ o ].visible = opts && opts.indexOf( o ) >= 0;
}

The first step of creating a preview is to get the selected database and determine parts to omit:

let db 		= this.cmbDatabase.itemData( this.cmbDatabase.currentIndex );
let disable = [];
 
if( this.cbTables.checked )
{
	if( !this.cbColumns.checked    ) disable.push( 'Columns'     );
	if( !this.cbIndexes.checked    ) disable.push( 'Indexes'     );
	if( !this.cbTableLinks.checked ) disable.push( 'Table_Links' );
	if( !this.cbTriggers.checked   ) disable.push( 'Triggers'    );
	if( !this.cbChecks.checked     ) disable.push( 'Checks'    );
}
else
{
	disable.push( 'Tables' );
}
 
if( !this.cbLinks.checked     ) disable.push( 'Links'     );
if( !this.cbViews.checked     ) disable.push( 'Views'     );
if( !this.cbTypes.checked     ) disable.push( 'Types'     );
if( !this.cbFunctions.checked ) disable.push( 'Functions' );
 
this.showReport( db, disable );

Then we can find a target report, create a new instance of the report and set parameters:

let reportNames = [];
 
reportNames[ CONNECTION_FACTORIES.MSSQL            ] = 'MSSQL';
reportNames[ CONNECTION_FACTORIES.MYSQL            ] = 'MySQL';
reportNames[ CONNECTION_FACTORIES.POSTGRESQL       ] = 'PostgreSQL';
reportNames[ CONNECTION_FACTORIES.SQLITE 	   ] = 'SQLite';
reportNames[ CONNECTION_FACTORIES.SQLITE_CLIENT    ] = 'SQLite';
reportNames[ CONNECTION_FACTORIES.VALENTINA        ] = 'Valentina';
reportNames[ CONNECTION_FACTORIES.VALENTINA_CLIENT ] = 'Valentina';
 
this.report = project.makeNewReport( 'Report_' + reportNames[ db.connection.factory.factoryType ], db );
 
let disable = arguments[1];
if( disable )
{
	for( let it of disable )
		this.report.setParameterValue( 'Print_' + it , 'false' );
}
 
this.previewPage( 1 );

Now generate a preview image and place it on the form:

this.imgReport.pixmap = this.report.previewPage( page );

A set of buttons for navigation and export is located below the preview. For example, export to a PDF file on disk looks like this:

f = QFileDialog.getSaveFileName( 'Save Report As PDF', '', 'PDF (*.pdf)' );
if( f.length > 0 )
	this.report.printToDisk( f, REPORT_PRINT_TYPE.TO_PDF );

The layout for the main report for each engine looks like this:

It consists of multiple parts, and parts may contain subreports to show child items.

Parts and subreports may be excluded from the resulting report setting the corresponding parameter to false:

This parameter is checked in the print_if condition for subreport or Report Header and Page Body regions (in case it is necessary to hide part entirely):

For fields containing very long texts like function, trigger or view definitions split_type is set to Allow Split By Line. Starting with version 13.5 this option allows the text to be printed on multiple pages if necessary.

SQLite provides limited information about the schema objects, and it is just not enough to retrieve the necessary information for the complete report using SQL.

Starting with version 13.5 it is possible to use JavaScript to build a complete data set.

For example, PRAGMA FOREIGN_KEY_LIST (tblName) statement returns the information about foreign keys in the specified table, but it doesn't provide the names of the constraints. So the first step is to build a function which recognizes the basic information about foreign key constraints in the SQL definition:

function unquoteIdentifier(identifier) {
  // If identifier starts and ends with the same quote character,
  // remove the outermost quotes and replace any consecutive quotes within the identifier
  if ((identifier.startsWith('"') && identifier.endsWith('"')) || 
      (identifier.startsWith('`') && identifier.endsWith('`'))) {
    return identifier.slice(1, -1).replace(/""/g, '"').replace(/``/g, '`');
  }
  // If no quotes are present, return the original identifier
  return identifier;
}
 
function parseForeignKeyConstraints( sqlDefinition ) {
  const foreignKeyConstraints = [];
 
  // Regular expression to find the foreign key constraints
  const regex = /(?:CONSTRAINT\s+([^\s]+))?\s*FOREIGN KEY\s*\(([^)]+)\)\s*REFERENCES\s*([^(\s]+)\s*\(([^)]+)\)/gi;
 
  let match;
  while ((match = regex.exec(sqlDefinition)) !== null) {
    const constraintName = match[1] ? unquoteIdentifier(match[1]) : null;
    const keyColumns = match[2].split(',').map((column) => unquoteIdentifier(column.trim()));
    const referencedTable = unquoteIdentifier(match[3]);
    const referencedColumns = match[4].split(',').map((column) => unquoteIdentifier(column.trim()));
 
    const foreignKeyObject = {
		"constraintName": constraintName,
		"referencedTable": referencedTable,
		"keyColumns": keyColumns.join( ", " ),
		"referencedColumns": referencedColumns.join( ", " )
    };
 
    foreignKeyConstraints.push(foreignKeyObject);
  }
 
  return foreignKeyConstraints;
}

Later, we can match the found information with the result of the PRAGMA execution to build a complete data set:

let res = [];
 
let tables_curs = datasource.sqlSelect("select tbl_name, sql from sqlite_schema WHERE type = 'table' AND substr( name, 0, 8 ) <> 'sqlite_'");
 
if( tables_curs.firstRecord() )
{
	do
	{
		const tableName = tables_curs.columnValue(0);
		let fks_curs = datasource.sqlSelect( `PRAGMA FOREIGN_KEY_LIST('${ tableName }');` );
 
		const parsedFKs = parseForeignKeyConstraints( tables_curs.columnValue(1) );
 
		if( fks_curs.firstRecord() )
		{
			do
			{
				const keyColumns = fks_curs.columnValue( 3 ).split(',').map((column) => column.trim()).join( ", " );
				const referencedColumns = fks_curs.columnValue( 4 ).split(',').map((column) => column.trim()).join( ", " );
				const referencedTable = fks_curs.columnValue( 2 );
 
				let constraintName = null;
 
				for( let it of parsedFKs )
				{
					if( referencedTable   == it.referencedTable &&
					    keyColumns        == it.keyColumns      &&
					    referencedColumns == it.referencedColumns )
					{
						constraintName = it.constraintName;
						break;
					}
				}
 
				const foreignKeyObject = {
					  "constraintName": constraintName,
					  "tableName": tableName,
					  "referencedTable": referencedTable,
					  "keyColumns": keyColumns,
					  "referencedColumns": referencedColumns,
					  "onUpdateAction": fks_curs.columnValue( 5 ),
					  "onDeleteAction": fks_curs.columnValue( 6 ),
					  "match": fks_curs.columnValue( 7 )
					};
 
				res.push( foreignKeyObject );
			}
			while( fks_curs.nextRecord() )
		}
	}
	while( tables_curs.nextRecord() )
}
 
res

The last line specifies array that will be used as a source for report.