一个工具设计架构 [英] Design architecture for a tool

问题描述

我有一个工具，这将捕获数据，然后将其保存在一个Excel文件，并生成SQL的数据库执行的要求。

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.

要解决一样，我开始设计和开发一个框架，将读取XML文件，并渲染数据采集摆动屏幕，并保存在Excel中。现在，我已经达到了，我已经在Excel中的数据有一个阶段。现在，我不能够进一步决定的做法。我心里有一种方法来获得Excel工作表中的数据，并建立SQL的，但我没有信心的办法。

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.

该方法正在考虑的是开发更多的XML的（一个对每个数据库架构），其中将包括所需要的数据库表结构的副本。在GUI XML，我会提供一个名为databaseMapping含SCHEMA_NAME新标签; TABLE_NAME，COLUMN_NAME。当用户请求生成SQL的一个特定的文件，然后，我会读与该Excel中的筛选XML，并找出该数据库的映射，然后生成SQL的。但我看到的挑战在这种方法： -

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）在Excel中，列将对应于多个表并可能分散在不同的表列，因此阅读和生成SQL将是一个资源消耗的活动。

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），阅读的Excel本身是如此缓慢（使用POI），所以应用程序的性能将下降为Excel增长。我相信，POI不支持从不同的应对张全柱，建设一个新的工作簿或表？如果能够做到，那么就仍然是可控的。读取每个行，然后每个单元建立新的临时工作表中的数据库表结构，以生成SQL，似乎不太对我的。

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（试行）

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>

谢谢！

推荐答案

我会去与嵌入式数据库，如 H2 ，并在他们返回家园同步移动用户的变化。

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

如果你追求你的建议的方法，你可以利用这种的数据库模式定义语言的（DBSDL），使用XSLT在SQL语句的形式生成DDL。

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."

这篇关于一个工具设计架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！