Generating Database Documentation for ScrewTurn wikis

August 29, 2009

Updated XSL style sheets for dbscript documentation generators creating ScrewTurn content.

See here for sample output:

MS SQL AdventureWorks

Oracle Demo Schema

PostgreSQL OpenNMS

ScrewTurn also allows external Page Providers (instead of stored static pages)

MS SQL AdventureWorks (by Page Provider)

Leave a Comment » | Documentation Generation, Oracle, PostgreSQL, SQL Server, ScrewTurn, dbscript | Permalink
Posted by devio

Creating a Mosaic Webpage with Javascript using DOM

August 27, 2009

I tried to present my concept of a Mosaic Webpage today, and found that while it renders nicely in Firefox, Internet Explorer (6,7,8) only showed a blank page.

Time to solve the problem building the table in DOM instead of dynamically writing HTML (using document.writeln).

The HTML code of the document basically stays the same:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
 <head>
  <title>devio mosaic</title>
  <link rel="stylesheet" href="default.css" type="text/css">
 </head>
 <body onload="setup()">
  <table id="tbl" cellSpacing="1" cellPadding="1" width="100%" border="0">
  </table>
 </body>
</html>

The new Javascript code adds the various link and iframe elements to the table in two columns:

<script type="text/javascript">
function setup()
{
  var sites = [];
  sites["site1"] = "http://my.first.site/";
  sites["site2"] = "http://my.second.site/";
  // more sites

  var iMaxCol = 2;
  var iCol = 0;
  var tr;
  var tbody = document.createElement("tbody");
  document.getElementById("tbl").appendChild(tbody);

  for(site in sites)
  {
    if (iCol == 0) {
      tr  = document.createElement("tr");
      tbody.appendChild(tr);
    }

    var td = document.createElement("td");
    td.align = "center";
    var a = document.createElement("a");
    a.href = sites[site];
    a.target = "_blank";
    a.innerHTML = site;
    td.appendChild(a);
    td.appendChild(document.createElement("br"));
    var iframe = document.createElement("iframe");
    iframe.src = sites[site];
    iframe.width = "100%";
    iframe.height = "100px";
    td.appendChild(iframe);
    tr.appendChild(td);

    iCol++;

    if (iCol == iMaxCol) {
      iCol = 0;
    }
  }
}
</script>

The crucial thing for IE to render the <table> at all turned out to be the use of the <tbody> element!

Leave a Comment » | Javascript | Permalink
Posted by devio

Documenting Oracle Databases

August 26, 2009

I described the capabilities of dbscript to generate MediaWiki content and a single HTML page documenting an Oracle database in a previous post.

During development of PostgreSQL support (MediaWiki, HTML) it became clear that an XSL style sheet (among other things) needed to become specific to a database engine.

I therefore updated the XSL style sheets for Oracle support, and these are the results:

Leave a Comment » | Documentation Generation, Oracle, dbscript | Permalink
Posted by devio

Reading LONG Oracle columns in .Net

August 24, 2009

If you use ODP.Net to access an Oracle database from .Net, and select LONG text columns, the DataReader’s GetString() method will return an empty string.

To work around this behavior, you need to set the InitialLONGFetchSize to a non-zero value, and use the OracleDataReader’s GetOracleString() method, and use ToString() to convert the result into a .Net string.

Another possibility is to set InitialLONGFetchSize to -1, so that GetString() works as expected.

The linked documentation also explains how to access LONG RAW data.

Leave a Comment » | .Net, C#, Oracle | Permalink
Posted by devio

Schema-Qualified Foreign Keys in SMOscript

August 20, 2009

The MSSQL SMO library has some funny functionality and options (depending on your sense of humor).

If you tell an Smo.Table to script itself and its dependent objects (indexes, constraints, etc.) using its Script(ScriptingOptions) method, it will correctly generate the table name in Schema.Table notation (schema qualified), but the foreign key constraints to other tables will not contain the schema of the referenced table.

That is, unless you set the ScriptingOptions.SchemaQualifyForeignKeyReferences member to true. Thanks to user Oliver for pointing that out.

SMOscript has now been fixed and is available for download here.

Leave a Comment » | SMO, SMOscript | Permalink
Posted by devio

Retrieving Table and Column descriptions in SQL Server

August 19, 2009

SQL Server stores column descriptions as so-called Extended Properties, using the extended property named ‘MS_Description’.

Even though the user interface in Enterprise Manager or Management Studio does not support setting descriptions of tables and other database objects, this is possible using the sp_addextendedproperty and sp_updateextendedproperty stored procedures.

The descriptions added by the developer can be retrieved by the following SQL statements (SQL Server 2005 or higher).

To retrieve the descriptions of all tables:

SELECT sys.objects.name AS TableName, ep.name AS PropertyName,
       ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name

To retrieve the descriptions of all table columns:

SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName,
       ep.name AS PropertyName, ep.value AS Description
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
CROSS APPLY fn_listextendedproperty(default,
                  'SCHEMA', schema_name(schema_id),
                  'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep
ORDER BY sys.objects.name, sys.columns.column_id

In SQL Server 2000, you need to call the function ::fn_listextendedproperty, and the CROSS APPLY operation is not supported.

Leave a Comment » | Documentation Generation, SQL Server | Permalink
Posted by devio

Creating HTML documentation of PostgreSQL databases

August 10, 2009

dbscript ships with a couple of XSLT style sheets which transform an XML representation of a database schema into MediaWiki, HTML, or, if you create them on your own, any format you wish.

After writing the previous post on PostgreSQL support in dbscript, I fixed the XSLT for HTML generation, created a Documentation Generator in dbscript, and this is the resulting HTML documentation of the OpenNMS data model.

Leave a Comment » | Documentation Generation, PostgreSQL, dbscript | Permalink
Posted by devio

Comfort Zones

August 10, 2009

Bjarne Stroustrup says in a recent interview with devx (on page 2):

I object to the “Joe Coder” moniker. At best, it is patronizing. Realistically we are all “Joe Coder” outside our little comfort zones. Modern software is far too complex for a single person to be more than a novice in most aspects. I’m “Joe Coder” most of the time, and so are you.

How true!

Looking back, my “comfort zones” have been so far (in chronological order):

  • Basic (Spectrum, QL)
  • Turbo Pascal
  • dBase/Clipper
  • Delphi (for a looong time)
  • ASP+VBScript
  • ASP.Net+C#+TSQL (current)

(TSQL took me quite some time to feel comfortable, and it really is not as comfortable as C#)

150th post, so nothing technical today ;)

Leave a Comment » | Meta, all kinds of everything | Permalink
Posted by devio

Creating MediaWiki Documentation of PostgreSQL Databases

August 7, 2009

After hacking away the last couple of days, dbscript finally is able to perform another trick: Importing database schemas from PostgreSQL databases using the Npgsql data provider.

Internally, the database abstraction layer is now completely based on interfaces as sketched in a previous post. Rather than asking a Database Engine object, “can you do this,  and can you do this?” giving boolean answers, we ask the object: do you implement a functionality and all the required methods, which is a simple “is” operation.

The other aspect is that the newly created data access layer simplifies handling the differences between the database engines (object types, table columns, etc).

These differences also resulted in a reference from XSLT style sheets to a Database Engine to allow more specific content creation in Document Generators.

So here are the new documentation samples, based on the data model of OpenNMS:

The data diagram of the OpenNMS database schema:

PostgreSQL is the 3rd database engine supported by dbscript, after MS SQL Server and Oracle.

Please note that this version still needs some polish. The most current stable version is available for download here.

3 Comments | Documentation Generation, PostgreSQL, dbscript | Permalink
Posted by devio

Creating a light-weight Data Access Layer in C# (5)

August 4, 2009

Part 1: Retrieve information on tables and columns

Part 2: SELECT record, access record fields

Part 3: SELECT records, INSERT

Part 4: Generating C# classes

Part 5: Generated C# classes and polymorphism

The code presented in the previous articles generates a C# class definition (based on dbscript’s Object table) like this:

public partial class Object : DAL.BaseTable
{
  public Object() { }
  public Object(IDataReader dr) : base(dr) { }
  protected override string TableName { get { return "Object"; } }
  public int OID
  { get { return GetColumnValue<int>(Columns.OID); } }
  public int PV_OID
  {
    get { return GetColumnValue<int>(Columns.PV_OID); }
    set { SetColumnValue(Columns.PV_OID, value); }
  }
  ....
  protected override string IdentityColumn { get { return "OID"; } }
  public static class Columns
  {
    public static string OID = "OID";
    public static string PV_OID = "PV_OID";
    ....
  }
  public static List<Object> GetRecords(IDataReader dr)
  { return GetRecords<Object>(dr); }
  public static List<Object> GetRecords(SqlConnection conn, string sSelect)
  { return GetRecords<Object>(conn, sSelect); }
}

Note that the class is generated as a partial class, so you are free to extend it in your own code.

In the dbscript database, all information about schema objects is stored in the Object table, and of course, not all columns of this table are used by the different object types, such as tables, views, etc. If we take into account different database engines (a concept introduced in dbscript 0.96), then tables, views, etc in MSSQL have different properties than their counterparts in Oracle or PostgreSQL.

Time to create some class which are derived from DAL.BaseTable to inherit the DAL mechanisms, but reference the generated DAL.Object columns:

public class SchemaObject : dbscriptlib.DAL.BaseTable
{
  protected override string TableName { get { return "Object"; } }
  protected override string IdentityColumn { get { return "OID"; } }
  public int OID
  { get { return GetColumnValue<int>(DAL.Object.Columns.OID); } }
  public string OwnerName
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.OwnerName); }
    set { SetColumnValue(DAL.Object.Columns.OwnerName, value); }
  }
  public string ID
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.ID); }
    set { SetColumnValue(DAL.Object.Columns.ID, value); }
  }
  ....
}
public class Table : SchemaObject
{
  public string FileGroupName
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.FileGroupName); }
    set { SetColumnValue(DAL.Object.Columns.FileGroupName, value); }
  }
  ....
}

Note that the FileGroupName is defined only for tables, rather than for all DAL classes. In Oracle, there is no FileGroupName, but a TablespaceName, which we map to the original column using this definition:

public class OracleTable : SchemaObject
{
  public string TablespaceName
  {
    get { return GetColumnValue<string>(DAL.Object.Columns.FileGroupName); }
    set { SetColumnValue(DAL.Object.Columns.FileGroupName, value); }
  }
  ....
}

With this simple mechanism, a sort of polymorphic table can easily be implemented, and only the accessing classes “know” how about the mapping of the table columns.

Leave a Comment » | C#, Code Generation, Data Access Layer | Permalink
Posted by devio

« Previous Entries