为工具设计架构 [英] 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(一个用于 db 中的每个 Schema)，其中包含所需的数据库表结构的副本.在 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 不支持处理来自不同工作表的整个列并构建新的工作簿或工作表?如果可以做到，那么它仍然是可以管理的.读取每一行然后每个单元格在db表结构中构建新的临时工作表以生成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.

图形界面 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(建议)

<?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屋！