KR101908556B1 - A database update system based on spreadsheet interface by finding out updated records automatically - Google Patents

Abstract

The present invention is based on a spreadsheet-based system that automatically updates extracted records and their fields in comparison with the original data set, and updates the database by creating extracted update records in a query statement, A system for automatically updating a database, comprising: a data request unit for requesting and fetching a data set of a target table from the database and storing an initial data set; A worksheet display unit that provides a spreadsheet interface, creates a worksheet and displays the structure of the data set and record data on the worksheet; A worksheet update unit for updating record data of a record set on the worksheet according to a user's input through the spreadsheet interface; An update extraction unit for comparing the initial data set with the updated data set to extract an updated record; An update definition unit for defining a data set structure and record data of the updated record for the extracted updated record; A query generating unit for generating a query template by using a data set structure of a defined record and mapping actual record data to a mapping variable of the query template for each updated record to generate an actual query statement; And a DB updating unit for transmitting the generated query statement to the database to update the target table.
With such a system, the user can update the database intuitively without updating the knowledge of the database processing method, such as the query statement, by updating the database using a spreadsheet, such as Excel, which is often used for office use.

Description

[0002] A spreadsheet-based database automatic update system that automatically extracts update records is provided.

In the present invention, when a data set of a database is imported into a worksheet of a spreadsheet such as Excel and is modified, the updated records and their fields are automatically extracted in comparison with the original data set, the extracted updated record is created as a query, To a spreadsheet-based database automatic update system.

The present invention also relates to a spreadsheet-based database automatic update system for creating a query statement for each modified record to update the database, generating a query template in advance, and replacing only the updated data value to generate an actual query statement.

Generally, in order to update a database on the Web, a web program for processing the database and a query statement for instructing it are needed. That is, the update operation is performed by the SQL level in the database management system [Patent Document 1].

Specifically, in order to input, modify, or delete data recorded in a specific table in the database, it is updated through a server-side web program such as JavaServer Pages (JSP), Active Server Pages (ASP), or Hypertext Preprocessor (PHP). Specifically, the server-side Web program must connect to a database and create a query statement such as an SQL statement for processing data of the target table. If you send the created query statement to the database and request to process the query, the database processes the received query to update the tables in the database.

Therefore, in order for the web program to process the query of the database, the web screen for each table or item should be configured. In addition, there is a problem that an input screen must be separately developed for the number of target tables. Therefore, each query must be written by the developer himself, so that it takes a lot of time and lots of work. In addition, it is possible to handle only single data or a small amount of data in the created screen, and it is inconvenient for mass data processing and it takes much time to process.

To this end, various techniques for locally modifying a remote database on a network basis have been proposed to more effectively modify the database. As an example, a technology for modifying a database by caching and modifying a part of a multidimensional database on a user device and reflecting the same on a multidimensional database has been proposed (Patent Document 2). In addition, a technique of updating a periodic update using a specific file format such as a sand file has been proposed (Patent Document 3). Further, a technology for processing update of a subset database in a partial database is proposed (Patent Document 4).

However, all of the above prior arts are techniques for modifying another database, which is a part of the database, and reflecting it in the parent database. Therefore, there is a problem in that a database must be configured and processed locally. That is, the local user has to have knowledge to deal with the database.

In addition, a technique for integrating a large amount of data into a database has been proposed [Patent Document 5]. That is, it is a technique of performing a processing sequence including a plurality of consecutive jobs for converting a data set into converted data, and changing the database based on the converted data. However, the prior art is a technique of converting new data to a specific format and then converting the data into an original database and storing the original data. Therefore, there is a problem that a general user must configure a large amount of data in a specific format and can not process the data intuitively.

[Patent Document 1] Korean Laid-open Patent No. 10-1999-0038698 (published May 5, 1999) [Patent Document 2] Korean Patent Laid-Open No. 10-2008-0031202 (published on April 4, 2008) [Patent Document 3] Korean Published Patent Application No. 10-2004-0053254 (published on June 23, 2004) [Patent Document 4] Korean Published Patent Application No. 10-2012-0052301 (Published May 23, 2012) [Patent Document 5] Korean Published Patent Application No. 10-2014-0027128 (published on Mar. 03, 2014)

An object of the present invention is to solve the above-mentioned problems, and it is an object of the present invention to extract a data set of a database as a worksheet of a spreadsheet such as an Excel, And automatically updates the database by writing the extracted update record into a query statement.

It is another object of the present invention to provide a spreadsheet-based database automatic update system for creating a query statement for each modified record to update the database, generating a query template in advance and replacing only the updated data value .

In order to accomplish the above object, the present invention provides a spreadsheet-based automatic database update system connected to a database, comprising: requesting and fetching a data set of a target table from the database; Request part; A worksheet display unit that provides a spreadsheet interface, creates a worksheet and displays the structure of the data set and record data on the worksheet; A worksheet update unit for updating record data of a record set on the worksheet according to a user's input through the spreadsheet interface; An update extraction unit for comparing the initial data set with the updated data set to extract an updated record; An update definition unit for defining a data set structure and record data of the updated record for the extracted updated record; A query generating unit for generating a query template by using a data set structure of a defined record and mapping actual record data to a mapping variable of the query template for each updated record to generate an actual query statement; And a DB updating unit for transmitting the created query statement to the database to update the target table.

According to another aspect of the present invention, there is provided a system for automatically updating a spreadsheet-based database, wherein the update extracting unit performs sorting of the initial data set and the updated data set using a key field, And sequentially comparing each of the records of the updated data set.

According to another aspect of the present invention, there is provided a system for automatically updating a spreadsheet-based database, wherein the update extractor compares key values between current records to be compared, and if the key values are the same, If the current record of the initial data set is earlier than the current record of the updated record set, then it is determined that the current record of the initial data set has been deleted. If the current record of the updated data set is ahead of the current record of the initial record set, And determines that the current record of the updated data set has been inserted.

According to another aspect of the present invention, there is provided a system for automatically updating a spreadsheet-based database, wherein the update extractor checks whether there is any remaining record if the current value of the current records to be compared is the same and the current record is the last record, The records of the recordset are inserted, the records of the initial data set remaining are determined to be deleted,

If the current record of the initial dataset is ahead of the current record of the updated record set and the current record of the initial dataset is the last record, it is determined that the current record and the remaining records of the updated dataset have been inserted,

If the current record of the updated dataset is ahead of the current record of the initial record set and the current record of the updated dataset is the last record, it is determined that the current record and the remaining records of the initial dataset have been deleted.

According to another aspect of the present invention, there is provided a system for automatically updating a spreadsheet-based database, wherein the query generator receives the updated record as a stream and generates contents of each record to be updated in real- .

According to another aspect of the present invention, there is provided a system for automatically updating a spreadsheet-based database, wherein the update extractor comprises fields having at least one modification in at least one of all fields of a recordset, And generating the data to be modified by displaying only the field values corresponding to the fields in the set of the modified fields with respect to the record, wherein the query generator generates a key field when generating a template of the modified query, Fields, and generates a query template.

According to another aspect of the present invention, there is provided a system for automatically updating a spreadsheet-based database, wherein the query generator generates a delete query template using only a key field and generates an input query template using all fields of a record set structure.

As described above, according to the spreadsheet-based database automatic update system according to the present invention, by updating the database using a spreadsheet such as Excel, which is often used for office work, the user can intuitively The effect of being able to update the data and update the database is obtained.

In addition, according to the spreadsheet-based database automatic update system of the present invention, after generating a query template to be updated, the actual query statement is generated by replacing only the data value of each record, thereby receiving updated data in a stream form, The query statement can be generated, and the effect of generating and processing the query statement in a short time can be obtained.

In addition, according to the spreadsheet-based database automatic update system of the present invention, a user can easily process a large amount of data, thereby creating and analyzing a data analysis report desired by him / And the like.

1 is a block diagram of an overall system configuration for implementing a spreadsheet-based database automatic update system in accordance with the present invention;
2 is a block diagram of an overall system for implementing a spreadsheet-based database automatic update system in accordance with the present invention;
3 is a block diagram of a configuration of a spreadsheet-based database automatic update system according to a first embodiment of the present invention;
4 is an example of a screen for selecting a target table of an object database according to the first embodiment of the present invention;
5 is an example of a screen displaying a schema of a table on a worksheet according to the first embodiment of the present invention;
6 is an example of a screen in which data of a table is displayed on a worksheet according to the first embodiment of the present invention;
7 is an example of a screen displaying a state in which data of a table is updated on a worksheet according to the first embodiment of the present invention;
FIG. 8 is a flowchart illustrating a method of comparing an initial data set and an updated data set to extract an updated record according to the first embodiment of the present invention; FIG.
9 is an example of table information shown in XML according to the first embodiment of the present invention.
10 is an example of a flat file that defines data to be updated according to the first embodiment of the present invention.
11 is an example of a query template according to the first embodiment of the present invention.
12 is an example of a screen displaying a state in which data of a table is updated on a worksheet according to the second embodiment of the present invention;
13 is an example of table information displayed in XML according to the second embodiment of the present invention;
14 is an example of a flat file that defines data to be updated according to the second embodiment of the present invention.
15 is an example of a query template according to the second embodiment of the present invention.
Figure 16 is an exemplary view of an actual query created in accordance with a second embodiment of the present invention;

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, the present invention will be described in detail with reference to the drawings.

In the description of the present invention, the same parts are denoted by the same reference numerals, and repetitive description thereof will be omitted.

First, an overall system for implementing a spreadsheet-based database automatic update system according to the present invention will be described with reference to FIG.

1, the overall system for implementing the present invention includes a client 20 installed in a user terminal 10, a renewal server 30 connected to the client 20 via a network, And a database 40 for storing the data. FIG. 2 is a configuration diagram showing the entire system of FIG. Quot; user "shown in Fig. 2 is driven by the client 20 in the configuration in the user terminal 10. [

The client 20 is a client program system installed in the user terminal 10 and has a user interface through a web browser. That is, the user performs processing such as updating data on-line through a screen interface such as a web browser or a web browser. At this time, the user terminal 10 receives a command of the user, executes the command, and displays the processing result on the screen or the web browser.

Meanwhile, the user terminal 10 is a computer terminal having a computing function such as a personal computer (PC), a notebook, a tablet PC, a tablet, a PDA, and a smart phone. The user terminal 10 and the update server 30 are connected via a network so that the client 20 on the user terminal 10 can perform a data processing operation on-line.

In addition, the client 20 requests the update server 30 to process data on-line, such as data request and data update, and obtains the result from the server 30 and displays the result on the web browser. Or transmits the processed data to the update server 30. Particularly, the client 20 exchanges data obtained from the database 40 with Excel (spreadsheet software trademark of Microsoft), Lotus (Lotus 1-2-3, trademark of Lotus Software Inc.), Hancell Trademark) through the interface of the spreadsheet program or input commands / data. Further, the client 20 transmits the update result of the processed data through the spreadsheet interface to the update server 30. [

Next, the update server 30 fetches the data set from the database 40 and transmits it to the client 20, or updates the database 40 by returning the updated data set from the client 20. In particular, the update server 30 detects newly added, deleted, or modified data in the client 20 and automatically creates a database query statement for processing the changed data. The update server 30 requests the created query statement to the database 40 and requests the result to be reflected in the database 40. [

Meanwhile, the functions of the client 20 and the update server 30 described above are merely examples, and may be variously implemented according to the implementation techniques of the server and the client. That is, the client 20 and the update server 30 are one update system, and their functions can be distributed to each other according to performance.

As another example, the client 20 simply has a web browser function, and all the functions can be built in the update server 30. [ That is, the update server 30 can have both the interface function of the database 40 and the query statement creation function for the modified data, as well as the function of modifying data through a spreadsheet-based interface.

As another example, the update server 30 merely has an interface function with the database 40, and the client 20 can perform not only the data modification function through the spreadsheet-based interface but also the automatic generation of the query statement for the modified data Function.

As another example, without the update server 30, the client 20 directly accesses the database 40 and accesses the above-described database connection, the data modification function through the spreadsheet interface, and the automatic query statement generation function .

Next, the database 60 is a normal database (DB) for storing data, and has a DBMS for managing data, and performs operations such as data storage, deletion, and retrieval through queries. In particular, the database 40 is a commercially available database, and performs a data query service using a general query function for processing a data set.

In particular, the database 40 is a database for storing big data. Also, preferably, the database 40 comprises a relational database (RDB).

In the following description, the operation of changing the entire data set such as modification, deletion, insertion of the data set is expressed as a task of "updating". In other words, the update operation is used as a concept including modification, deletion, and insertion. That is, modification refers to changing the values of some fields in a record, and updating means modification, deletion, insertion, etc. of a record. In the case where the meaning difference is not large, the term "correction" is used in combination with the term "update".

Next, a spreadsheet-based database automatic update system according to a first embodiment of the present invention will be described in more detail with reference to FIG. As described above, the automatic database update system is a server-client system implemented by the client 20 of the user terminal 10 and the server 30 connected to the network. Hereinafter, the system implemented in the server 30 will be described.

3, a spreadsheet-based database automatic update system 30 according to an embodiment of the present invention includes a data request unit 31 for requesting a data set of a database, a data request unit 31 for displaying a requested data set on a spreadsheet interface A worksheet updating unit 33 for updating some data in the data set on the worksheet, an update extracting unit 34 for comparing the original data set with the updated data set and automatically extracting the updated contents, An update definition unit 35 for defining update contents for the extracted update data, a query generation unit 36 for generating a query statement for updating the updated data, and a DB for updating the database with the generated query statement And an update unit 37. In addition, it further comprises a storage unit 38 for temporarily storing data to be processed.

First, the data requesting unit 31 requests the database 40 to fetch the data set from the object database. A data set refers to a set of data consisting of a plurality of records composed of the same fields. That is, one record is composed of a plurality of field values, and the fields (columns) of all records have the same field (or column).

Preferably, the dataset is data from one DB table.

Specifically, the data requesting unit 31 requests the database 40 to inquire the table list of the object database, and displays the inquired table list on the screen. The user can select a desired table from the displayed table list on the screen. Fig. 4 shows an example of a screen for selecting a table to be imported from the table list.

When one of the tables displayed on the user screen is selected, schema information for the corresponding table is retrieved. The schema of the destination database or target table is a detailed description of how the data in the table is organized. That is, the schema indicates the structure of the table data such as the name of each data field, the display name, the type (kind) of the data, and the condition. The data thus obtained forms a data set.

As another example, the data requesting unit 31 may fetch the table data of the object database through the query statement. Preferably, the query at this time is a query for one table, and a key field of the corresponding table must exist in the query result. In this case, when a plurality of fields form a key, all corresponding fields must be included. The data retrieved through the query form a single dataset.

That is, the data request unit 31 fetches the data set from the object database. If you select a table earlier, the dataset that you import is all the data in the table, and if you import the target table into the target table, it is part of the filtered table by the query statement. At this time, preferably, the data of the object database is fetched through a Java DB connection driver (JDBC) or the like. The data schema is also retrieved using the Java DB connection driver.

In another embodiment, the data request unit 31 extracts the query result using a query editor that imports the target table of the target database into the data set and performs a query on the imported data set (or target table) can do. You can then use the extracted query results as a single data set. That is, only the data set of the query result extracted from the worksheet display unit 32 to be described below can be displayed as data on the worksheet. This makes it possible to display a table composed of only fields desired by the user on the worksheet without displaying the entirety of the target table on the worksheet. In addition, by not displaying an unnecessary data field, it is possible to enhance convenience of editing data of a user.

As described above, the data set of the table obtained from the object database, the data set obtained by the query statement, or the data set filtered by the query statement are all the data sets to be updated. This will be hereinafter referred to as an initial data set (first data set or original data set).

The data requesting unit 31 stores the initial data set in the storage unit 38 or the like.

Next, the worksheet display unit 32 provides a spreadsheet interface, creates a worksheet (hereinafter, worksheet) on a spreadsheet such as Excel or Lotus, and displays the data set on the created worksheet. Specifically, a data set retrieved from the destination database is displayed on a worksheet in a spreadsheet. A spreadsheet is a work that is displayed in a tabular form in rows and columns, such as Excel (trademark of Microsoft Corporation), Lotus (Lotus 1-2-3, trademark of Lotus Software Corporation), Hansel And a spreadsheet software program configured to process data in each cell.

First, the worksheet display unit 32 analyzes the schema information (or the structure of the data set) of the target data set and writes the detailed information on each of the fields to the worksheet. To do this, create a new worksheet.

As an example, as shown in Fig. 5, a detailed structure of table data such as a name, a display name, a data type (kind), a condition, and a key field of each data field is displayed according to a column of the worksheet. Further, the type of data corresponding to each column is analyzed, and the display attributes (in particular, the display format for numbers) of each column are corrected to match the type of data (format). For example, if the name of a column (field) is "Birthday" and the format of the field is a date format, set all the columns to date format. If the call is displayed, the data format is displayed accordingly.

In addition, the worksheet display section 32 also generates an execution module linked to a worksheet provided in the spreadsheet when creating the worksheet of the spreadsheet. The execution module is a module (or code) written in a scripting program language such as VBA (Visual Basic for application). The execution module is a module that, when a specific event occurs in a worksheet, executes a function corresponding to each event. Accordingly, when the user performs a specific operation such as deletion, modification, or insertion on the worksheet by the worksheet update unit 33, the function in the execution module is executed according to the job or operation event to perform the related operation.

For example, an executable module may warn or restore that it should be canceled if it deletes columns in the worksheet, rows in the schema of the dataset, etc., or changes the values of those cells, such as field names or field attributes can do.

The worksheet display unit 32 displays the data set to be updated (or the initial data set) on the worksheet of the spreadsheet. As shown in FIG. 6, the detail structure such as the field names of the respective fields of the data is described in columns on the worksheet, and the corresponding field values (or table data ). That is, as shown in FIG. 6, in the data on the spreadsheet (or worksheet), data is displayed in each cell by rows and columns. In this case, each column in the upper row includes a field name (field name) And arranges the records to fit in each field.

That is, the initial data set consists of a number of records, each of which is displayed in a row on the worksheet. Cells corresponding to each column of the record show the field values of the corresponding record field. Particularly, since the data types of the columns of each field are analyzed in advance and all the columns are set in the corresponding format, all the columns of the corresponding records are displayed on the worksheet of the spreadsheet according to the data format.

In the example of FIG. 6, in the case of the EMPLOYEES table, this column (or column) does not allow duplication since EMPLOYEE_ID is the primary key.

In addition, the worksheet display section 32 sets in advance the maximum number of records that can be displayed on one worksheet. Then, when the maximum number is exceeded, the remaining records are created as a new worksheet and displayed on the created worksheet. As an example, millions of data can be defined and entered into the database through the spreadsheet screen, so that the same sheet can contain millions of sheets and multiple sheets.

On the other hand, the worksheet display unit 32 embeds and uses only the main functions of a commercially available spreadsheet program such as Excel and Lotus. That is, the spreadsheet processing operation is performed by the core function of the spreadsheet, and the entire interface is processed by the worksheet display section 32. [ The worksheet updating unit 33 to be described below is also the same.

In addition, the worksheet display section 32 can display a command button for executing a command on the worksheet. Preferably, the command button includes an inquiry button for inquiring a table or a query result, an OK button for updating the database by transmitting the updated data to the server, and the like.

Next, the worksheet update unit 33 performs an update operation on the data displayed on the worksheet. In particular, the worksheet updating unit 33 performs a data updating operation in accordance with a user's instruction through the spreadsheet interface screen provided by the worksheet display unit 32. [

That is, the worksheet updater 33 displays the initial data set on the worksheet of the spreadsheet and processes the works on the worksheet of the spreadsheet being processed by the user. For example, a user may modify data in a particular cell on a spreadsheet, or insert or delete a particular row. At this time, the interface is processed in the same manner as a normal spreadsheet interface.

Each function of the worksheet updating unit 33 is implemented by an execution module (a program module including a script language such as VBA) linked to a worksheet. That is, when the user performs a specific operation such as modification, insertion, deletion, etc. on the worksheet, the operation is judged as one event and the function of the execution module corresponding to the operation event is executed. This functionality is provided by the core functionality of the spreadsheet. Hereinafter, for the sake of convenience of explanation, all changes in the contents of data due to modification, deletion, insertion (new addition), etc. are described as being "updated".

Preferably, the worksheet updating unit 33 cancels the job when the column of the worksheet, the row in which the schema of the data set is displayed, or the like is changed or the value of the corresponding cell such as the field name or the field attribute is changed Warn or restore.

FIG. 7 shows a state in which a part of the record data in FIG. 6 is updated. That is, the record of the key 104 is deleted, the records 105, 106 and 108 are respectively changed, and the changed data are the salary and the phone number (phone_number). Also, a record 112 is newly added.

Next, the update extracting unit 34 compares the initial data set with the updated data set and automatically extracts the update contents. The update extraction unit 34 fetches the initial data set stored in the data request unit 31 and compares the initial data set with the data set displayed on the current worksheet. At this time, the data set displayed on the current worksheet will be referred to as an updated data set.

Preferably, the update extractor 34 performs the sorting of the initial data set and the updated data set using a key field, and sequentially updates each of the records of the initial data set and the updated data set Prepare.

On the other hand, FIG. 8 shows a method in which the update extracting unit 34 compares the initial data set with the updated data set and detects the updated contents.

As shown in FIG. 8, first, the update extracting unit 34 aligns the initial data set and the updated data set through the key field (S11). Then, the first record of the data set is set as the current record, respectively (S12).

Then, key values between the current records are prepared (S13).

If the key values are the same, the field values of the initial data set and the current records of the updated data set are compared with each other to determine whether to modify them (S21).

Then, it is determined whether any current record is the last record (S22). If it is not the last data, each current record is set as the next record, and the process goes back to step S13 to compare the key values of the records in advance (S23).

If any one of the current records is the last record, it is checked whether the remaining records exist. If both the initial dataset and the updated dataset are the last records, the process terminates without further processing. If the current record of the initial data set is the last record, and the updated records remain, the updated records are judged to be inserted. Conversely, if the current record of the updated data set is the last record and the records of the initial data set remain, the initial records are determined to have been deleted (S24).

Next, if the current record key values are compared with the current record values of the initial data set, it is determined that the current record of the initial data set has been deleted (S31). It is determined whether the current record of the initial data set is the last record (S32).

If not, the current record of the initial data set is set to the next record of the initial data set, and the step of comparing the key values of the records to the previous step (S13) is repeated (S33). If it is the last, it is determined that the current record and the remaining records of the updated record set are inserted (S34). And it ends.

Next, if the current record key value is compared with the current record value of the updated data set, it is determined that the current record of the updated data set is inserted (S41). It is determined whether the current record of the updated data set is the last record (S42).

If not, the current record of the updated dataset is set to the next record of the updated dataset, and the process of step S13 is repeated (S43). If it is the last, it is determined that the current record and the remaining records of the initial record set have been deleted (S44). And it ends.

As an example, referring to FIGS. 6 and 7, FIG. 6 is an initial data set, and FIG. 7 is an updated data set. Figures 6 and 7 are already sorted data sets.

At this time, after the records up to the key 103 are compared, the current record is set to the next record. At this time, in the initial data set, the record of 104 keys is the current record, and the updated data set 105 is the current record. In this case, the key 104 of the current record of the initial dataset precedes the key 105 of the current record of the updated recordset. This corresponds to the process of FIG. 8B. Thus, it is determined that the current record of the initial data set of the key 104 has been deleted.

Further, after the records up to the key 110 are compared, the current record is set to the next record. At this time, in the initial data set, the record of the 114 key is the current record, and the updated data set 112 is the current record. In this case, the key 112 of the current record of the updated dataset precedes the key 114 of the current record of the initial recordset. This corresponds to the procedure of FIG. 8C. Thus, it is determined that the current record of the updated data set of the key 112 is inserted.

In the example of FIG. 7, it is the records of the keys 105, 106, and 108 that the data cell value or field value is changed. These records are judged to have been modified since the field values in the fields of salary or phone number are different from the records of the initial data set. This corresponds to step S21 of FIG. 8A. That is, these records are modified records.

Next, the update definition unit 35 defines the update content for the updated data (or record) on the worksheet or the worksheet. Preferably, it is defined as an XML document, and the data is composed of a flat file.

That is, the update definition unit 35 defines update contents by the definition rules defined for the update records detected by the update extraction unit 34 in advance. At this time, the contents to be defined are the table information indicating the structure or schema of the data or the data set to be updated, and the data to be updated. The update definition unit 35 records (or defines) the update data only for the updated data or the updated record. In other words, data not updated at all is not recorded.

First, the update definition unit 35 defines a data set (or update data structure) by a predetermined format (or definition rule). Preferably, the structure (or update data structure) of the dataset is displayed as an XML document. An example of displaying the structure of a dataset as an XML document is shown in Fig.

The table information or the information of the data set as shown in FIG. 9 includes data on the following entity and its attributes. Preferably, the information on the structure of the table information or the data set includes a table name (automatically designated when a table is selected on the user screen), a DB connection code (automatically specified when a DB is connected on the user screen) Data type, whether to allow null values, and so on.

(1) ExecuteInfo: Database information to be executed

- ConnectionCode: Database code defined on the server.

(2) DataTable: table information to be updated, multiple tables can be processed in the same transaction

- Name: table name

- Caption: Display name

- TargetTable: the name of the table to be updated

(3) Column: Column information of the table (or data set)

- Name: Column name

- Caption: Column display name

- Constraint: key information P: Primary key V: not null N: nullable

- DataType: Data type C: String N: Numeric type, D: Data type

- UpdateFlag: Whether to include the column when generating the Update query

(4) Rows: Define data to process

- RowIndexCol: the column where the data row serial number will be located

- RowStateCol: Column to place the separator code to process the corresponding item in data handling

- TextType: The location of the data to be processed. FILE: Sent as an attachment. XML: Internal xml

The column information or the field information of the data set may represent the entire field information of the target table or may represent a part thereof. That is, if the target table is partially retrieved through a query, the field structure is generated for only some of the fields of the table.

In addition, the update definition unit 35 records the data to be updated in a predetermined format and generates it as a file. Preferably, the update definition unit 35 also generates updated data on the worksheet as a flat file. A flat file is a normal file that consists of a series of strings of data.

Fig. 10 shows an example of a flat file. Preferably, the data defined in the spreadsheet screen is created as a text file of the form shown in FIG. 9 by the server, the column and the row delimiter defined in advance, and transmitted to the server. Here, as an example, column delimiters are denoted by " | ", and row delimiters are denoted by an "enter (or new line)"

In particular, in FIG. 10, the type of update for how the corresponding record (corresponding row) is to be processed is displayed at the beginning of each line. Each update status symbol is as follows.

C: New input U: Data modification D: Data deletion

Next, the query generating unit 36 receives the table information and the update document in which the data to be updated is defined, and generates a query statement for the updated data. At this time, the query generator 36 generates a query template in advance by using the structure of the data set, maps the data to be updated to the query template, and generates a query to actually update the query template.

That is, the query generation unit 36 analyzes the contents of the update document defining the table information, and extracts the database, table, and column information to be updated. Then, using the extracted information, an input query, an update query (correction query), and a query template for a delete query are generated.

An example of a query template is shown in Fig. 11 is a query template created by the data set structure of FIG. 11 (a) is an input query (or insert query) template, and Fig. 11 (b) is a template of an update query (change query or update query). 11 (c) shows a query template of the delete query.

The query generating unit 36 creates the format of the query query such as input, update (modification), deletion, etc., describes the table name, the field name (column name) and the like in the query command format, The query template is created by inserting the mapping variable instead of the variable value in the portion corresponding to the query template. At this time, the mapping variables are numbered in order.

In the example of Fig. 11, "INSERT INTO <table name> (column name, ...) VALUES (column value, ...)" is a query query format, EMPLOYEES is a table name, EMPLOYEE_ID, FIRST_NAME, People. In addition, @ D1, @ D2, ..., @DN, etc., using the special character @ are mapping variables. 1, 2, ..., N represent numbered numbers. The mapping variable is described in the column or value inserted in the value of the variable in the format of the query statement.

The number numbered in the mapping variable indicates the order of the update data to be mapped to the mapping variable. That is, the n-th data (field value, column value) of the update data is the n-th numbered mapping variable.

Next, the query generator 36 reads the data for each record, and generates an actual query for each record. At this time, a query template (for example, an input query template, an update query template, a delete query template, or the like) of the corresponding type is selected according to the update type of each record, To generate the actual query statement.

In particular, the query generator 36 can receive the data to be updated as a stream, and generates query statements in real time with the contents of each record to be updated in the order received in the stream.

As shown in FIG. 10, the update data or the update data file is continuously described with respect to the operation type (input C, update U, deletion D, etc.) and data (or field values) to be mapped. The query generation unit 36 reads each data by record, determines what kind of work it is, and selects a query template. And maps subsequent update data (or field values) to mapping variables in the selected query template. Through this process, the actual query statement is generated.

Next, the DB updating unit 37 requests the database 40 to generate the created (created) query, and requests the database 40 to update the data of the target table.

The DB updating unit 37 can transmit the query statements generated for each record to the database 40 by requesting to update the actual query statements generated previously. However, if the database 40 requests a query statement for each record individually, the entire updating time and cost are considerably large. This is because the database processes a single request in one transaction, because there are many tasks to perform the transaction.

Therefore, preferably, the DB updating unit 37 batches the actual queries for each of a plurality of records in batches. That is, the query statement is created only once, and the transmission data is processed by mapping the corresponding column value to the variable of the query, adding it to the batch, and finally requesting execution to the database only once. can do.

Next, a spreadsheet-based database automatic update system according to a second embodiment of the present invention will be described in detail with reference to FIG. The second embodiment of the present invention is the same as the first embodiment, except that modified fields of modified records are retrieved from the updated records to create a modified template query differently.

First, the update extracting unit 34 records which field is the modified field when only a cell value (or a field value) of the record is modified. In particular, the update extracting unit 34 checks and collects all the fields having at least one modification in any record among all the fields. That is, we obtain the set of modified fields.

In the example of FIG. 7, the change of the data cell value or the field value is a field of a salary and a phone number (number) among the records of the keys 105, 106, and 108. Therefore, only these two fields are collected as modified.

Next, the update definition unit 35 combines only the set of modified fields and the key field to generate the structure of the modified dataset or the structure of the schema. Alternatively, since the entire schema structure is shown in Fig. 9, only the column name may be described. Hereinafter, for convenience of description, it is assumed that all the schema structures for the structure of the modified data set are generated.

In the example of Fig. 12, the key field is "EMPLOYEE_ID ", and the changed fields are salary and phone_number. Therefore, only those fields (or columns) are used to create the structure of the dataset or the structure of the schema. The XML document of the data set structure created according to the above example is shown in FIG.

As shown in Figure 13, the column field only shows fields for employee ID, phone number, and salary for xml documents.

In addition, when generating the modified data or the record, the update definition unit 35 displays only the structure of the updated data set. That is, when data to be updated is recorded in a predetermined format and is generated as a file, only a field value (column value) corresponding to the key field and the updated field is displayed for the modified record to constitute a flat file. An example thereof is shown in Fig.

As shown in FIG. 14, only the field value of the key field is described in the data to be deleted. The data or record to be modified records only the field values of the data set structure (schema structure) for the fields to be modified in advance. Also, records to be entered record all of the fields. That is, the record to be input displays all data of the entire schema structure as in the first embodiment.

On the other hand, as shown in FIG. 14, only the data field (column value) of the field EMPLOYEE_ID, the updated field PHONE_NUMBER, and the value of the SALARY field are recorded in the modified field. In addition, the modified data includes three employee IDs (EMPLOYEE_ID) of 105, 106, and 108, respectively. Therefore, it is recorded in three modified data (or update data file).

Next, the query generating unit 36 generates a query statement for the updated data using the data set structure of the data to be updated. At this time, the query generator 36 generates a query template in advance by using the structure of the data set, maps the data to be updated to the query template, and generates a query to actually update the query template.

In particular, the query generator 36 generates a query template using the structure of the data set that represents only the update field. Fig. 15 shows an example of a query template. As shown in Fig. 15A, in the input template, all the schemas are displayed as column names. Also, as shown in FIG. 15B, in the template of the query to be modified, the column name is displayed only as EMPLOYEE_ID, PHONE_NUMBER, and SALARY. That is, it is composed of only a key field and an updated field.

In addition, the query generator 36 reads the data for each record, and generates an actual query for each record. At this time, a query template (for example, an input query template, an update query template, a delete query template, or the like) of the corresponding type is selected according to the update type of each record, To generate the actual query statement.

Fig. 16 shows an actual query created for the update data (or record) in Fig.

Next, the effect of the present invention will be described in more detail.

First, the number of executions of the present invention is not limited.

Since the data registered / modified / deleted by the user is transmitted to the server through the upload stream, it is possible to freely handle the single data to millions of large data. The data information is converted into a flat file separated by a predetermined row and column delimiter and uploaded to the server. The server reads the data from the upload stream and parses the data according to the predetermined row and column delimiter And binds it to a pre-generated query from the obtained xml information.

Also, mass data can be processed quickly.

The basic format for processing data in the update server is to access the database through the jdbc dirver and then parse the xml information sent from the client to generate a query statement corresponding to input, The client reads the data from the upload stream transmitted from the client and binds the variable to the pre-generated query.

At this time, among the connection objects, PreparedStatement is used to specify the size (Fetch size), and the bound data is added using the PreparedStatement's addBatch method. When the entire data is added, do. The advantage of this method is that it does not execute every time, but executes at once, reducing the load on the database and speeding up the speed.

In addition, DB function (DB function) is provided as a function.

It provides the option to process database function, user creation function, etc. as well as data processed by the user, so that the server provides the function to process the target column processing instead of data binding function.

You can also include queries such as Oracle functions, time functions such as Oracle sequence, sysdate, and DB functions such as rownum.

The invention made by the present inventors has been described concretely with reference to the embodiments. However, it is needless to say that the present invention is not limited to the embodiments, and that various changes can be made without departing from the gist of the present invention.

10: user terminal 20: client
30: update server 31: data request unit
32: worksheet display section 33: worksheet update section
34: update extracting unit 35: update definition unit
36: Query generating unit 37: DB updating unit
38: storage unit 40: database

Claims (7)

A spreadsheet-based database auto-renewal system coupled with a database,
A data request unit for requesting and fetching a data set of the target table from the database and storing the fetched initial data set;
A worksheet display unit that provides a spreadsheet interface, creates a worksheet and displays the structure of the data set and record data on the worksheet;
A worksheet update unit for updating record data of a record set on the worksheet according to a user's input through the spreadsheet interface;
An update extraction unit for comparing the initial data set with the updated data set to extract an updated record;
An update definition unit for defining a data set structure and record data of the updated record for the extracted updated record;
A query generating unit for generating a query template by using a data set structure of a defined record and mapping actual record data to a mapping variable of the query template for each updated record to generate an actual query statement; And
And a DB updating unit for transmitting the generated query statement to the database to update the target table,
Wherein the update extractor comprises fields in which at least one modification is made to any record among the entire fields of the recordset as a set of modified fields,
The update definition unit generates data to be modified by displaying only the field values corresponding to the fields in the modified set of fields with respect to the modified record,
Wherein the query generator generates a query template by constructing a key template and a field in a set of the modified fields when generating a modified template of the query.
The method according to claim 1,
The update extractor may perform the sorting of the initial data set and the updated data set using a key field and sequentially comparing the initial data set and the updated data set, Features a spreadsheet-based database auto-renewal system.
3. The method of claim 2,
The update extractor compares the key values of the current records to be compared and determines whether or not to modify the field values of the current records if the key values are the same, If it is determined that the current record of the initial data set has been deleted before the current record, and the current record of the updated data set is ahead of the current record of the initial record set, it is determined that the current record of the updated data set is inserted A spreadsheet-based database auto-renewal system.
The method of claim 3,
The update extractor determines whether there is any remaining record, if the key value between the current records to be compared is the same, and if any current record is the last record, the records of the updated updated record set are inserted, It is determined that the records have been deleted,
If the current record of the initial dataset is ahead of the current record of the updated record set and the current record of the initial dataset is the last record, it is determined that the current record and the remaining records of the updated dataset have been inserted,
Wherein if the current record of the updated dataset is ahead of the current record of the initial recordset and the current record of the updated dataset is the last record then the current record and the remaining records of the initial dataset are determined to have been deleted. Based database automatic update system.
The method according to claim 1,
Wherein the query generator receives the updated record as a stream and generates contents of each record to be updated as a query in real time in the order received in the stream.
delete The method according to claim 1,
Wherein the query generation unit generates the delete query template using only the key field and generates the deletion query template using the entire field of the record set structure in the input query template.

Images