Implementation suggestions for writing SQL Queries to meet the Babacus Export Tool Requirements

The Babacus Export tool uses ODBC or OleDB to perform one SQL query consisting of several columns. Optionally, a second SQL query can be perfromed to retrieve alternate site ID's, such as HSA-ID. The details of these queries are described in detail here. It is extremely unlikely that the certifying authority (CA) has a table with the exact same columns. More likely the CA database will have an arbitrary number of tables containing the different pieces of information. Therefore, the CA using the Export tool needs to write their SQL queries against the database to combine the columns needed and present them in the format expected by the Export tool.

The certificate query returns information about certifications and the sites to which the certifications have been awarded. This data contains certificate codes, types, expiration dates, and information about the sites as described here.

The site alias query returns "aliases", or alternative unique identifiers for the site ID's extracted from the certificate query. This allows CA's to transmit information such as HSA-ID site identifiers.

The following text describes some helpful tips and hints that can get you started in writing your SQL query. They are written with the assumption that the CA database is implemented on Microsoft's SQL Server. However, most other database platforms implement similar dialects of SQL.

Unique identifiers

Almost certaintly, the CA database has unique IDs for each certificate and for each site it has registered. These unique IDs may be 32-bit integers, 64-bit integers, GUIDs or some form of unique string. These IDs are unique within each CA database. It is possible that the IDs from one CA database may coincide with IDs from other CA databases. To avoid confusing IDs from different CAs, Babacus translates unique IDs received from CAs to an internal format. All CAs need to do is provide uniqueness within their own databases.

Unique IDs must be presented to Babacus as strings of length no more than 20 characters (varchar(20) in SQL parlance). If necessary, use the tip below to convert integral or GUID columns into strings. It is essential that the IDs provided to Babacus are persistent. A persistent ID is an ID that doesn't change from day to day. For example, if the certification 568706, issued to site 1532123 on June 3^rd is still valid on June 4^th, then it must be submitted again as certification 568706.

Implementing an SQL View

Some relational databases implement the concept of views. Babacus recommends that the CA implement its import query as an SQL view (or equivalent) if the CA database permits it. The SQL query entered in the Export tool will then be a simple piece of code, along the lines of:

SELECT * FROM CertificateExportView

instead of a more cumbersome:

SELECT SomeTable.cId AS certId
  SomeOtherTable.oId AS orgId
  YetAnotherTable.szAfa AS siteId
  ...

One benefit the CA gains by implementing a view is that the internal (and possibly sensitive) structure of its certificate database is not divulged to the Export tool: all it sees are the predefined columns the view defines. In addition, security is enhanced: the database administrator can restrict the login the Export tool uses so that it only can perform reads on the View in question.

Another benefit is that Views can be optimized by the database SQL engine. They will perform faster than 'raw' SELECT statements.

A view is created in SQL as follows:

Assigning column names

Suppose that the CA database has a table Certificates with a column certificateID which contains a unique identifier for a particular certificate. This is exactly the information the Export tool wants, but it expects it to be located in a column called certId (see the documentation). To change the name of a column, use the AS SQL syntax as follows:

This renames the column "certificationID" to the required name "certID".

Translating certificate types

The Export tool requires that certificate types be specified as one of a set of strings defined here. However, the CA database may store certificate types internally as different strings, or even as integers. For example, the ISO 14001:2004, SS 62 77 50, and AFS 2001:1 certificate types might be represented internally in the CA database as the strings iso_14001_2004, ss_62_77_50 and afs_2001, respectively, or even as the numbers 31, 42 and 56, respectively.

To translate these values, a simple translation table may be used. The CA creates a two-column table in its database. For this example, let's assume the translation table is called TranslateCertificateType. The first column of TranslateCertificateType is called internal and contains the certificate types as they occur internally in the CA database. This column is also the primary key for the translation table. The second column, called external, contains the certificate types as the Export tool expects them. For our example above, the translation table could look like this:

If the certificate types are stored as integers, the translation table could look like this:

Next, the translation is performed in the SELECT statement. Let's assume the CA database table Certificates we discussed in the previous section has an additional column certificateType. This column contains certificate types stored in the internal format and needs translating. This translation is done with the following SQL fragment (note that the actual SQL fragment you will be producing will be a lot more complex than this explanatory example):

SELECT

  Certificates.certificateID AS certId,
  TranslateCertificateType.external AS certType,
  ...
FROM Certificates AS C
  JOIN TranslateCertificateType AS TCT
    ON C.certificateType =
   TCT.internal
  ...

Converting datatypes

The Export tool requires the unique ID values for certificates, organizations, and sites to be provided as variable-length strings, or varchars. However, the CA database might be using integers, GUIDs, or other SQL data types to represent these unique IDs. Fortunately it is relatively easy to convert most SQL data types to varchar strings.

Assuming the CA database stores certificate IDs in the table Certificates, in an autoincremented column cId of type int -- a common scenario. The SELECT statement to convert it might look like this: