- 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:stylesheet>
<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="/">
</xsl:template>
<!-- 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 match="d:form">
CREATE TABLE
</xsl:template>
<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));
<!-- Here we do map the various field types. You want to revisit the form -->
<xsl:template match="d:field">
</xsl:template>
<xsl:value-of select="@name"></xsl:value-of>
<!-- Sample of a specia case consideration for number fields -->
<xsl:template match="d:field[@type='number']">
</xsl:template>
<xsl:value-of select="@name"></xsl:value-of>
<xsl:choose>
</xsl:choose>
<xsl:when test="d:numberformat/@format='fixed'">
DECIMAL(31,
</xsl:when>
<xsl:value-of select="d:numberformat/@digits"></xsl:value-of>
),
<!-- Generated with xml2html.xslt, Linkwerk.com -->
| Permalink | 
Comments
Posted by Stephan H. Wissel At 01:23:42 On 04/14/2008 | - Website - |
<a href="{ Link } Source Code</a>
Posted by Ramesh At 22:20:54 On 04/15/2008 | - Website - |
Posted by Sherwin Delfin At 12:58:12 On 04/10/2008 | - Website - |
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).
Posted by Axel At 19:31:53 On 04/13/2008 | - Website - |
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.
Posted by Axel At 22:52:29 On 04/14/2008 | - Website - |
Posted by Chen Yan At 16:35:49 On 04/17/2009 | - Website - |
Posted by Stephan H. Wissel At 00:03:39 On 04/19/2009 | - Website - |