Design architecture for a tool

Question

I have a requirement of a tool which would capture data and then save it in an excel file and generate sql’s for database execution.

To address the same, I started designing and developing a framework which would read xml file and render the swing screen for data capture and saving it in excel. Now, I have reached a stage where I have got data in excel. Now, I am not able to decide the approach further. I have an approach in mind to get the data from excel sheet and build sql’s, but am not confident of the approach.

The approach am thinking of is to develop more xml’s(one for each Schema in db) which would contain replica of the database table structure needed. In the GUI xml, I would provide a new tag called "databaseMapping" containing SCHEMA_NAME;TABLE_NAME ;COLUMN_NAME. When a user asks generate sql’s for a specific file then, I would read the screen xml related to that excel and find out the database mappings and then build the sql’s. But I am seeing challenges in this approach :-

1) In an excel, columns would correspond to more than one table and could have columns scattered across different sheets and so reading and building the sql would be a resource consuming activity.

2) Reading excel itself is so slow (using POI), so the performance of the app will go down as excel grows. I believe POI does not support coping whole columns from different sheets and building a new workbook or sheet? If that can be done then it would still be manageable. Reading each row and then each cell to build the new temporary work sheet in the db table structure so as to generate sql, does not seem quite right to me.

GUI XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataDetails>
    <Page pageId="1">
        <Column columnName="Branch ID">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>true</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Branch Name">
            <dataType>String</dataType>
            <maxLength>10</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Branch Type">
            <dataType>String</dataType>
            <maxLength>15</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Location">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Pincode">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>
    </Page>
    <Page pageId="2">
        <Column columnName="Business Line">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Branch Currency">
            <dataType>Date</dataType>
            <maxLength></maxLength>
            <isMandatory>false</isMandatory>
            <isUnique></isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Action">
            <dataType>String</dataType>
            <maxLength>10</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices>ComboBoxTest_Single.xlsx - ACTION;ACTION </limitedChoices>
            <headerName>CODE_SUB_ID</headerName>
            <databaseMapping></databaseMapping>
        </Column>
    </Page>
</DataDetails>

DB XML(Proposed)

<?xml version="1.0" encoding="UTF-8"?>
<table name="tablename">
    <column name="column1">
        <dataType>varchar</dataType>
        <length>20</length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
        <isPrimaryKey>Y</isPrimaryKey>
    </column>

    <column name="column2">
        <dataType>timestamp</dataType>
        <length></length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column3">
        <dataType>varchar</dataType>
        <length>20</length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column4">
        <dataType>decimal</dataType>
        <lengthIntegerPart>24</lengthIntegerPart>
        <lengthFractionalPart>6</lengthFractionalPart>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column5">
        <dataType>integer</dataType>
        <length>1</length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column6">
        <dataType>varchar2</dataType>
        <length>30</length>
        <nullAllowed>N</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column7">
        <dataType>date</dataType>
        <length></length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column8">
        <dataType>decimal</dataType>
        <lengthIntegerPart>24</lengthIntegerPart>
        <lengthFractionalPart>6</lengthFractionalPart>
        <nullAllowed>N</nullAllowed>
        <defaultValue></defaultValue>
    </column>
</table>

Thanks !!

Why do you need Excel as a middle step? Can you just go directly from the data entry application to the db? There are plenty of ORMs that make the kind of mapping you're trying to do easy so you won't have to write some SQL generation tool. — Ocelot20, Sep 22 '12 at 12:02
@Ocelot20 - The tool will be used used by people in higher management who will always be on the move, have no db on their machine, will be behind stricter higher wall policies. As a result of which, we would need to save the data in excel and then whenever they are back or they want the data to be shared with the company employees, they would send the excel and it will be uploaded in the company server. — dareurdream, Sep 22 '12 at 12:22
Could the data entry tool also be used to display the data and read it from your own proprietary format? — Ocelot20, Sep 22 '12 at 12:26
You know there are already tools out there which can do that - at least the data extraction part? DbUnit comes to mind, Liquibase or tools like SQL Workbench/J — , Sep 22 '12 at 15:42
@Ocelot20 - Yes the tool can be used to display data. The tools would capture and display data based on the GUI XML design. — dareurdream, Sep 24 '12 at 06:02
@a_horse_with_no_name - I am using SQLWorkench from long time but I dont know how it can extract data from an excel which is composed of columns from different table. Could you please share the details as to how to do that. Thanks — dareurdream, Sep 24 '12 at 06:03
You said you want to *save* the data to an Excel file which you can easily do with SQLWorkbench. But you can also connect to an Excel Sheet and query it (somewhat) like a SQL database: http://www.sql-workbench.net/manual/profiles.html#odbc — , Sep 24 '12 at 06:23
@a_horse_with_no_name - But how I use it to generate SQL in the application database format. The excel will capture minimum user data and certain data like constants, prmiray keys will automatically be generated while creating SQL. Thanks — dareurdream, Sep 24 '12 at 06:28
You lost me at "*SQL in the application database format*". I have no idea what that should mean. — , Sep 24 '12 at 06:33
@a_horse_with_no_name - This application will capture data in an excel which is not exactly in the DB format(can be combination of multiple tables). The DB could have more or less columns. Now, when I get the excel back I would like to extract data from the excel and generate sql statements to import the data into the database, while generating the primary keys and other non mandatory data. — dareurdream, Sep 24 '12 at 07:35

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

I'd go with an embedded database, such as H2, and synchronize the traveling users' changes upon their return home.

If you pursue your proposed approach, you may be able to leverage this Database Schema Definition Language (DBSDL) that uses "XSLT for generating DDL in the form of SQL statements."

edited May 23 '17 at 10:25

Community

1
1

answered Sep 22 '12 at 15:34

trashgod

203,806
29
246
1,045

I would certainly look into the ideas suggested. Thanks – dareurdream Sep 24 '12 at 06:06

Design architecture for a tool

1 Answers1

Linked