wissel.net

Usability - Productivity - Business - The web - Singapore & Twins

Creating SQL Statements from form definitions


I had an iteresting discussion with a customer this week. They use Domino and dotNet for their web applications. Their decission criteria when to use what: if the data of the application needs to be fed into their data warehouse at a later point of time, they use dotNet since storage there typically ends up in an RDBMS. The biggest problem they face, in their own voice: " Our users are pretty spoiled from Domino. They expect days as turnaround time for applications. Using dotNet it takes at least three times longer."
So I asked why they don't use DECS to connect to the RDBMS. They could develop the application in Notes/Domino and once the app does what the user wants just add the tables in the RDBMS and link them up using DECS. They asked back if there is a way to generate the table or at least the create table statement from Domino directly. The short answer: Yes, you can, however you need to make decissions on datatypes and field length. The long answer: you need Domino Designer (for the Tools - DXL Utilities - Transformer ... menu) and a little XSLT stylesheet.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output xmlns:xml="http://www.w3.org/XML/1998/namespace" method="text" version="1.0" encoding="UTF-8" xml:space="preserve" omit-xml-declaration="yes"></xsl:output>
<xsl:template match="/">
<!-- We only look at forms for the time being, when I have enough time I add basic support for subforms, at least the fixed ones -->
<xsl:apply-templates select="//d:form"></xsl:apply-templates>
</xsl:template>
<xsl:template match="d:form">
<xsl:param name="formName">
<xsl:choose>
<xsl:when test="@alias">
<xsl:value-of select="@alias"></xsl:value-of>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@name"></xsl:value-of>
</xsl:otherwise>
</xsl:choose>
</xsl:param>
CREATE TABLE
<xsl:value-of select="$formName"></xsl:value-of>
( DocID CHAR(32) NOT NULL,
<xsl:apply-templates select="//d:field[@kind!='computedfordisplay']"></xsl:apply-templates>
PRIMARY KEY (DocID));
</xsl:template>
<!-- Here we do map the various field types. You want to revisit the form -->
<xsl:template match="d:field">
<xsl:value-of select="@name"></xsl:value-of>
<xsl:choose>
<xsl:when test="@type='text'">
VARCHAR(254),
</xsl:when>
<xsl:when test="@type='keyword'">
VARCHAR(254),
</xsl:when>
<xsl:when test="@type='datetime'">
DATE,
</xsl:when>
<xsl:when test="@type='number'">
LONG,
</xsl:when>
<xsl:when test="@type='names'">
VARCHAR(254),
</xsl:when>
<xsl:when test="@type='richtext'">
BLOB,
</xsl:when>
<!-- If we forgotten a data type -->
<xsl:otherwise>
VARCHAR(254),
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- Sample of a specia case consideration for number fields -->
<xsl:template match="d:field[@type='number']">
<xsl:value-of select="@name"></xsl:value-of>
<xsl:choose>
<xsl:when test="d:numberformat/@format='fixed'">
DECIMAL(31,
<xsl:value-of select="d:numberformat/@digits"></xsl:value-of>
),
</xsl:when>
<xsl:otherwise>
LONG,
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

Posted by on 10 April 2008 | Comments (7) | categories: Blog Show-N-Tell Thursday

Comments

  1. posted by Sherwin Delfin on Thursday 10 April 2008 AD:
    Very interesting observation from your customers regarding the comparison of turnaround times.
  2. posted by Axel on Sunday 13 April 2008 AD:
    Truely interesting.
    But you will have to manually postprocess the generated SQL-DDL.

    Whats missing for the real world are for example:
    - exclude domino fields from persistence.
    - field specific constraints (e.g. NOT NULL, NUMERIC(8,2), etc. Also currently its easier to validate those constraints on the form by using .NET.
    - Using character as the primary key is slow (bigint or integer are much faster)
    - foreign keys aka referential integrety. Again using domino-UniqueID tends to be slow when being used in sql-join statements.

    I think xPages has a potential to solve a lot of those issues, in case they come up.
    And lots of RDBMS solutions use to be transactional by nature of business requirements. Domino isn't designed for that.
    Chances are that we could get that on Expeditor with plugging-in openSource java frameworks which support it very well (especially together with xPages).
  3. posted by Stephan H. Wissel on Sunday 13 April 2008 AD:
    @Axel: yes xPages looks promising. However we needed a solution for the customers current 6.5 environment.
    Emoticon smile.gif stw
  4. posted by Axel on Monday 14 April 2008 AD:
    Stephan, I just wanted to point out that it depends on the requirements for the rdbms database. Depending on usage scenarios, they can result in demand for a much stricter ddl-sql script.
    Dxl is great and it has opened a lot of new opportunities. I use it a lot for the most astoundingly divers tasks as admin-automation or as input to translation services.
  5. posted by Ramesh on Tuesday 15 April 2008 AD:
    want to know full detail description about ADO.Net Click
    <a href="{ Link } Source Code</a>
  6. posted by Chen Yan on Friday 17 April 2009 AD:
    Hi, Stephan, why not use LEI (Lotus Enterprise Integrator) ?
  7. posted by Stephan H. Wissel on Saturday 18 April 2009 AD:
    @Chen: Buying LEI just to create a table might be a little overkill. So customers bulk at it. And as long record creation happens on the Domino side only DECS is sufficient. For more advanced use LEI is a good choice. I actually like it a lot.