JP2017120610A - Update system for database using spreadsheet interface - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide an update system of a DB using a spreadsheet I/F for extracting data from a DB, and for displaying the data on a worksheet, and for reflecting the data updated on the worksheet on the DB.SOLUTION: An update system comprises: a data request part (31) for requesting and extracting an object table from a DB; a worksheet display part (32) for providing a spreadsheet I/F, and for generating a worksheet to display the structure and record data of the object table on the sheet; a worksheet update part (33) for updating the record data on the sheet in response to the input of a user; an update definition part (34) for detecting records updated on the sheet, and for defining the structure and record data of the table of the records; a query generation part (35) for generating a query sentence for reflecting the updated records on the DB; and a DB update part (36) for transferring the query sentence to the DB to update the object table.SELECTED DRAWING: Figure 3

Description

  The present invention relates to a database using a spreadsheet interface that retrieves data from a database, displays it in a spreadsheet interface system such as Excel, and updates the database to reflect data modified by the user on the spreadsheet interface. Regarding the update system.

  Generally, in order to update a database on the web, a web program for processing the database and a query statement for indicating the web program are required. That is, the update operation is performed according to the level of the structured query language (SQL) in the database management system (see, for example, Patent Document 1 below).

  Specifically, to enter data written to a specific table in the database, modify data written to a specific table, or delete data written to a specific table from the database Using server-side web programs such as Java Server Pages (JSP) (Java is a registered trademark), Active Server Pages (ASP), Hypertext Preprocessor (PHP), etc. Update. Specifically, the server-side web program must connect to a database and create a query statement such as a structured query language (SQL) statement for processing the data in the target table. When the created query statement is transferred to the database and requested to process the query, the database processes the received query to update the table in the database.

  For this reason, in order for the web program mentioned above to process the query statement of a database, you have to comprise each table or the web screen according to the said item. In addition, there is a problem that input screens must be separately developed for the number of target tables. For this reason, there is a problem that it takes a lot of time and man-hours because each query statement must be created by the developer himself. The created screen can handle only a single data or a small amount of data, which is inconvenient for processing a large amount of data, and takes a long time to process.

  Therefore, various techniques for locally modifying a remote database based on a network have been proposed in order to modify the database more efficiently. For example, a technique has been proposed in which a part of a multidimensional database is cached and corrected on a user device, and this is reflected in the multidimensional database to correct the database (see, for example, Patent Document 2 below). Further, a technique for periodically updating using a specific file format such as a sand file has been proposed (for example, see Patent Document 3 below). Furthermore, a technique for processing the update of the subset database in the partial database has been proposed (see, for example, Patent Document 4 below).

  However, each of the conventional techniques described in the following Patent Documents 1 to 4 is a technique that modifies another database that is a part of the database and reflects this in the mother database. Therefore, there is a problem that the database must be configured and processed locally. That is, there is a problem that the local user must have knowledge of handling the database.

  In addition, a technique for taking in a large amount of data into a database has been proposed (see, for example, Patent Document 5 below). That is, it is a technique for executing a processing chain including a large number of continuous operations for converting a collection of data into converted data, and changing the database based on the converted data. However, the conventional technique described in Patent Document 5 below is a technique in which newly added data is set in a specific format, and then these data are converted into an original database and stored. For this reason, there is a problem that a general user has to configure a large amount of data in a specific format, and this cannot be processed intuitively.

Korean Published Patent No. 10-1999-0038698 (published on June 05, 1999) Korean Published Patent No. 10-2008-0031202 (published April 08, 2008) Korean Published Patent No. 10-2004-0053254 (published on June 23, 2004) Korean Open Patent No. 10-2012-0052301 (released on May 23, 2012) Republic of Korea Patent No. 10-2014-0027128 (published March 06, 2014)

  The present invention has been devised to solve the above-mentioned problems, and its purpose is to extract data from a database, display it in a spreadsheet interface system such as Excel, and correct it by the user on the spreadsheet interface. It is an object of the present invention to provide a database update system using a spreadsheet interface that updates the database by reflecting the data thus obtained.

  In order to achieve the above object, the present invention relates to a database update system using a spreadsheet interface, which is connected to a database by a network, and requests data from a database by requesting a data set of the target table. And a spreadsheet interface that provides a spreadsheet interface, generates a worksheet and displays the structure and record data of the data set on the worksheet, and responds to user input using the spreadsheet interface Updating the record data on the worksheet and displaying the update status of each record data, and for the records updated on the worksheet, the structure of the updated record data set and Record data An update definition unit to be defined, a query generation unit for generating a query statement for reflecting a record updated from the data set structure and record data of the defined record, and the generated query statement to the database And a database (DB) update unit that updates the target table.

  Further, the present invention provides the database update system using a spreadsheet interface, wherein the data requesting unit uses the entire target table extracted from the database or a result extracted as a query for the target table as a data set. A query result is extracted using a query editor for querying the retrieved target table or given to the worksheet display unit, and the extracted query result is given as a data set to the worksheet display unit. It is preferable to display on the worksheet.

Furthermore, in the database update system using a spreadsheet interface according to the present invention, the worksheet display unit displays the structure of the data set in any one line on the worksheet, and the structure of the data set is displayed. It is preferable to display the data of the record from the line below the displayed line.
Furthermore, in the database update system using a spreadsheet interface according to the present invention, the structure of the target table includes a field name of each data field, and is any one of a display name, a data type, a condition, and a key field. It is preferable to further include the above.

  Furthermore, the present invention is the database update system using a spreadsheet interface, wherein the worksheet display unit is configured to provide a cell for each data field of the dataset structure with respect to a column on the worksheet of the data field. It is preferable to set the format or column format to the data type of the data field.

  Furthermore, the present invention is the database update system using a spreadsheet interface, wherein the worksheet display unit further adds a first update state field for displaying the update state of the record on the worksheet, When the cell content of one record on the worksheet is updated, the worksheet update unit displays the type of update in the first update status field of the record, and the update definition unit Preferably, with reference to the first update status field on the worksheet, the presence / absence of update and the type of update are determined for each record.

  Furthermore, in the database update system using a spreadsheet interface, when the worksheet display unit generates the worksheet, the content of the record on the worksheet is stored in an execution module linked to the worksheet. An event occurs when updated, and includes a script function that is called and executed by the event. The script function automatically sets the type of update in the first update status field when the record is updated. Is preferably displayed.

  Further, according to the present invention, in the database update system using a spreadsheet interface, the update definition unit defines a data set structure as an XML document and defines updated data as a flat file. It is preferable to define the field values of each record by dividing them with predefined column and row separators.

  Furthermore, in the database update system using a spreadsheet interface according to the present invention, the worksheet display unit further includes a record constituting a second update state field for displaying a field update state on the worksheet. When the cell content of one record on the worksheet is changed or updated to input, the worksheet update unit displays the update type in the second update status field of the record field. The update definition unit preferably generates a structure of a data set to be updated by configuring only the fields in which input or change is updated in the key field and the second update state field among the record fields. .

  As described above, according to the database update system using the spreadsheet interface according to the present invention, when data is extracted in the form of a spreadsheet such as Excel and corrected by itself, the database table is updated. Has the effect that the database can be updated without knowledge of database processing methods such as query statements.

  In addition, according to the database update system using the spreadsheet interface according to the present invention, the database is updated intuitively by updating the database using a spreadsheet such as Excel frequently used for office work. The effect that it can do is acquired.

  Furthermore, according to the database update system using the spreadsheet interface according to the present invention, a user can easily and quickly process a large amount of data, so that a data analysis report desired by the user can be generated and analyzed. Thus, there is an effect that it can be utilized in various fields such as management planning, simulation, and test processing for pilot development.

1 is a block diagram illustrating a configuration of an entire system for performing a database update system using a spreadsheet interface according to the present invention. FIG. 1 is a configuration diagram for an entire system for performing a database update system using a spreadsheet interface according to the present invention; FIG. 1 is a block diagram illustrating a configuration of a database update system using a spreadsheet interface according to an embodiment of the present invention. FIG. It is an example of the screen which selects the object table of the objective database by one Embodiment of this invention. It is an example of the screen which displayed the schema of the table on the worksheet by one Embodiment of this invention. It is an example of the screen which displayed the data of the table on the worksheet by one Embodiment of this invention. It is an example of the screen which displayed the state by which the data of the table were updated on the worksheet by the 1st Embodiment of this invention. It is an example of the table information displayed by XML by the 1st Embodiment of this invention. It is an example of the flat file which defined the data which should be updated by the 1st Embodiment of this invention. It is an example of the screen which displayed the state by which the data of the table were updated on the worksheet by the 2nd Embodiment of this invention. It is an example of the table information displayed by XML by the 2nd Embodiment of this invention. It is an example of the flat file which defined the data which should be updated by the 2nd Embodiment of this invention.

  Hereinafter, the specific content for implementation of this invention is demonstrated based on an accompanying drawing.

  In the description of the present invention, the same portions are denoted by the same reference numerals, and redundant description thereof is omitted.

  First, an overall system for performing a database update system using a spreadsheet interface according to the present invention will be described with reference to FIG.

  As shown in FIG. 1, the overall system for carrying out the present invention includes a client 20 provided in a user terminal 10, an update server 30 connected to the client 20 via a network, a database 40 for storing and managing data, Is provided. FIG. 2 is a block diagram showing the entire system. A “user” illustrated in FIG. 2 is a configuration in the user terminal 10 and displays a configuration driven by the client 20.

  The client 20 is a client program system provided in the user terminal 10 and provides a user interface via a web browser. That is, the user performs data processing work online via a web browser or a screen interface such as a web browser. At this time, the user terminal 10 receives a user command or the like, performs the command, and displays the processing result on the screen or the web browser.

  On the other hand, the user terminal 10 is a computer terminal having a computing function such as a personal computer (PC), a notebook personal computer, a tablet PC, a fablet, a PDA, and a smartphone. The user terminal 10 and the update server 30 are connected by a network, and the client 20 on the user terminal 10 performs data processing work online.

  In addition, the client 20 requests the update server 30 to perform online processing such as data request or data update, extracts the result from the server 30, and displays it on the web browser. Alternatively, the processed data is transferred to the update server 30. In particular, the client 20 displays data retrieved from the database 40 via an interface of a spreadsheet program such as Excel (which is a Microsoft spreadsheet software trademark) or receives commands / data. . Note that the client 20 transfers the update result of the data processed via the spreadsheet interface to the update server 30.

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

  On the other hand, the functions of the client 20 and the update server 30 described above are merely examples, and can be variously embodied according to the technology for realizing the server and the client. That is, the client 20 and the update server 30 are one update system, and these functions are distributed to each other according to performance.

  According to another example, the client 20 has only a web browser function, and all functions are constructed in the update server 30. That is, the update server 30 has not only an interface function of the database 40 and a query statement creation function for the corrected data, but also a function for correcting data using an interface based on a spreadsheet.

  According to yet another example, the update server 30 only has an interface function with the database 40, and the client 20 has not only modified the data via the spreadsheet-based interface, but also modified the data. It also has a function for automatically generating query statements for data.

  According to another example, the client 20 immediately connects to the database 40 without the update server 30, and includes the database connection function, the data correction function using the spreadsheet interface, the query statement automatic generation function, and the like. Do both.

  Next, the database 60 is a normal database (DB) for storing data, and includes a database management system (DBMS) for managing data, and uses queries to store, delete, and search data. Do it. In particular, the database 40 is a commercialized database and provides a data query service using a normal query function for processing a data set.

  In particular, the database 40 is a database that stores big data. Preferably, the database 40 is a relational database (RDB).

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

  As shown in FIG. 3, a database update system using a spreadsheet interface according to an embodiment of the present invention displays a data request unit 31 for requesting a data set of the database and the requested data set on the spreadsheet interface. The worksheet display unit 32, the worksheet update unit 33 that updates some data in the data set on the worksheet, the update definition unit 34 that defines the update content for the updated data, and the update A query generation unit 35 that generates a query statement for updating the received data, and a database (DB) update unit 36 that updates the database using the generated query statement.

  First, the data request unit 31 requests the database 40 to retrieve a data set from the target database. Here, the data set refers to a collection of data composed of a large number of records composed of the same field. That is, one record is composed of a large number of field values, but the fields (columns) of all the records have the same field (or column).

  Preferably, the data set is data retrieved from a single database (DB) table.

  Specifically, the data request unit 31 requests the database 40 to inquire about the table list of the target database, and displays the inquired table list on the screen. The user selects a desired table from the table list displayed on the screen. FIG. 4 shows an example of a screen for selecting a table to be extracted from the table list.

  When one of the tables displayed on the user screen is selected, schema information regarding the table is extracted. The schema of the target database or target table is detailed information on how the table data is structured. That is, the schema indicates the structure of the table data, such as the name of each data field, the display name, the data type (kind), and the conditions.

  According to another example, the data request unit 31 retrieves table data in the target database via a query statement. Preferably, the query statement at this time is a query statement for one table, and the key field of the table must always exist in the query result. At this time, if multiple fields form a key, all of the multiple fields must be included. Data retrieved via a query statement forms one data set.

  That is, the data request unit 31 retrieves a data set from the target database. When a table is selected in advance, the data set to be extracted is all the data of the table, and when a query sentence is extracted from the target table, it is a part of the table filtered by the query sentence. At this time, preferably, the data of the target database is taken out via a Java database connection (JDBC) (registered trademark) driver or the like. Prior to this, the data schema is also retrieved using a Java Database Connection (JDBC) (registered trademark) driver.

  On the other hand, according to another embodiment, the data requesting unit 31 uses a query editor that takes out the target table of the target database as a data set and queries the extracted data set (or target table). , Extract the query results. The extracted query result is used as one data set. That is, only the query result data set extracted from the worksheet display unit 32 described later is displayed as data on the worksheet. As a result, it is possible to display on the worksheet a table composed only of fields desired by the user without displaying the entire target table on the worksheet. Since unnecessary data fields are not displayed, the user can use the data easily.

  As described above, the data set of the table extracted from the target database, the data set extracted by the query statement, or the data set obtained by filtering the table by the query statement are all data sets to be updated. Hereinafter, this is referred to as an initial data set (first data set).

  Next, the worksheet display unit 32 provides a spreadsheet interface, generates a worksheet (hereinafter referred to as a worksheet) on a spreadsheet such as Excel, and displays a data set on the generated worksheet. In particular, the data set retrieved from the target database is displayed on a spreadsheet worksheet. Spreadsheets are categorized by row and column, such as Excel (Program trademark by Microsoft), Lotus (Lotus 1-2-3, Program trademark by Lotus Software), Hansel (Program trademark by Hangul and Computer), etc. It is a spreadsheet software program that is composed of a worksheet displayed in the form of a displayed table and processes 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 creates a worksheet according to detailed information regarding each field. For this purpose, a new worksheet is generated.

  As an example, as shown in FIG. 5, the detailed structure of the table data such as the name of each data field, the display name, the data type (kind), the condition, and the key field is displayed according to the column of the worksheet. Further, the type of data corresponding to each column is analyzed, and the display attributes (particularly, the display format for numbers) of each column are corrected according to the data type (format). For example, if the column name is “birthday” but the format of the field is a date format, all the columns are set to a date format. In addition, if it is a call display, it displays in the data format by this.

  In addition, when generating the worksheet of the spreadsheet, the worksheet display unit 32 generates an execution module linked to the worksheet provided from the spreadsheet. The execution module is a module (or code) created by a script-type program language such as VBA (Visual Basic for application). The execution module is a module that executes a function corresponding to each event when a specific event occurs in the worksheet. For this reason, when the user performs a specific operation such as deletion, correction, or insertion on the worksheet by the worksheet update unit 33, the function in the execution module is executed according to the operation or the operation event, and the related operation is performed. Do.

  Further, the worksheet display unit 32 displays the data set (or initial data set) to be updated on the spreadsheet worksheet. As shown in FIG. 6, the detailed structure such as the field name of each field of data is described in parallel in a column on the worksheet, and the lower row in the column corresponding to the field name. The field value (or table data) is displayed continuously. That is, as shown in FIG. 6, the data on the spreadsheet (or worksheet) is displayed in each cell by row and column. At this time, each column in the upper row has a field name (field name). , Display name, data type, etc. are described, and records below are arranged according to each field.

  That is, the initial data set is composed of a large number of records, and each record is displayed in one row on the worksheet. In the cell corresponding to each column of the record, the field value of the record field is displayed. In particular, the data format for each field column was analyzed in advance, and all the columns were set to that format, so all the columns for that record were displayed on the spreadsheet worksheet to match that data format. Is done.

  In the example of FIG. 6, in the case of the EMPLOYEES table, since EMPLOYEE_ID is a basic key, this column does not allow duplication.

  The worksheet display unit 32 presets the maximum number of records that can be displayed on one worksheet. When the maximum number is exceeded, a new worksheet is generated and the remaining records are displayed on the generated worksheet. For example, in order to process data on a spreadsheet screen, it is possible to define 1 million data in the same sheet and millions of data via a plurality of sheets and input it to the database.

  On the other hand, the worksheet display unit 32 embeds only the main functions of a commercial spreadsheet program such as Excel. 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 unit 32. The same applies to a worksheet update unit 33 described later.

  Further, the worksheet display unit 32 further displays a column for displaying the update state. Hereinafter, this column is referred to as an update state field or an update state column. Preferably, the first column on the worksheet is set as the update status field. In the example of FIG. 5, the column described as “Check” is set in the update state field.

  Further, the worksheet display unit 32 displays a command button for performing a command on the worksheet. Preferably, the command button includes an inquiry button for inquiring a table or a query result, a confirmation button for transferring the updated data to the server and updating the database, and the like.

  Next, the worksheet update unit 33 performs an update operation on the data displayed on the worksheet. In particular, in the worksheet update unit 33, the user performs a data update operation via a spreadsheet interface screen provided by the worksheet display unit 32 in advance.

  That is, the worksheet update unit 33 displays the initial data set on the spreadsheet worksheet and processes the work on the spreadsheet worksheet to be processed by the user. For example, the user corrects data of a specific cell on the spreadsheet, inserts a specific row, or deletes the specific cell. At this time, an interface such as a normal spreadsheet interface is processed.

  Each function of the worksheet update unit 33 is realized by an execution module (a program module created by a script language such as VBA) linked with the worksheet. That is, when the user performs a specific operation such as correction, insertion, or deletion on the worksheet, the operation is determined as one event, and the function of the execution module corresponding to the operation event is executed. Such functionality is provided from the core functionality of the spreadsheet. Hereinafter, for convenience of explanation, it is assumed that any data that has been changed due to correction, deletion, insertion (new addition), or the like has been “updated”.

  When an editing operation is generated on the worksheet, the worksheet update unit 33 displays the update type in the update state field cell according to the edit type or the update type. Preferably, the update type is indicated by a symbol or an update type symbol. The update type symbol is a preset symbol. The update type displays the update state or update type of the record or row, and displays the update state for each record such as correction, insertion, and deletion. For example, when a data cell is modified, “U” is displayed in the update status column of the row, and “C” is displayed when a row is added.

  The worksheet update unit 33 is arranged so that the user can display the update state or the update type symbol in the update state field. For example, “D” is input to the update status column of the row (or record) to be deleted by the user. At this time, a combo box is provided for each cell in the update state column, and the update state is set for each record (or for each row).

  Furthermore, preferably, no value or symbol is displayed in the update status field for records (or rows) in which none of the items have been updated.

  FIG. 7 shows a state in which a part of the record data in FIG. 6 has been updated. That is, the record of the key 104 is deleted, the records 105, 106, and 108 are changed, respectively, and the changed data is a salary and a phone number phone_number. For this reason, D, U, U, and U are displayed in the update field check1, respectively.

  Next, the update definition unit 34 detects the updated data on the worksheet or the worksheet, and defines the update contents for the detected data (or record). Preferably, it is defined in the form of an XML document, and the data is constituted by a flat file.

  The worksheet update unit 33 allows the user to correct the data set on the worksheet in units of cells, ranges, rows, or columns. That is, the data in one cell is corrected, or one row (or record) or the like is deleted (that is, the deletion symbol D is displayed in the update state field of the record to be deleted). Alternatively, a new data record is inserted and a value is entered in each field.

  In addition, the update definition unit 34 defines the update contents for each detected update record according to a defined definition rule. At this time, the contents to be defined are table information indicating the structure or schema of data to be updated or a data set, and data to be updated. That is, the update definition unit 34 writes (or defines) update data only to updated data or updated records. That is, data that has not been updated at all is not written.

  First, the update definition unit 34 defines a data set (or update data structure) in a predetermined format (or definition rule). Preferably, the structure of the data set (or the update data structure) is displayed in XML characters. An example of the structure of the data set displayed as an XML document is shown in FIG.

  The table information or data set information shown in FIG. 8 includes data on the following individuals and their attributes. Preferably, the table information or information on the structure of the data set is a table name (specified automatically when selecting a table from the user screen), a database (DB) connection code (connected to the database (DB) on the user screen) Automatically specified), the presence / absence of the key field, the data type, the presence / absence of null (NULL) value, and the like.

(1) ExecuteInfo: Database information to be executed-ConnectionCode: Database code defined in the server (2) DataTable: Table information to be updated, many tables can be processed in the same transaction-Name: Table name-Caption: Display name -TargetTable: Table name 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: Number type, D: Data type-UpdateFlag: Whether to include a column when generating an update query (4) Rows: Define data to be processed-RowIndexCol: Serial number of the data row -RowStateCol: Column where the delimiter code for processing the relevant item is processed in case of processing of data-TextType: Location of data to be processed FILE: Transfer as an attached file XML: XML is included inside

  The column information or field information of the data set may indicate the entire field information of the target table or a part thereof. That is, when only a part of the target table is extracted via a query, a field structure is generated only for a part of the fields of the table.

  The update definition unit 34 writes data to be updated in a predetermined format and generates a file. Preferably, the update definition unit 34 further generates data updated on the worksheet as a flat file. A flat file refers to a normal file in which data is composed of a series of columns.

  FIG. 9 shows an example of a flat file. Preferably, the data defined on the spreadsheet screen is created as a text file in the format shown in FIG. 9 using the server, the predefined columns and row separators, and transferred to the server. Here, for example, a column delimiter is indicated by “|”, and a row delimiter is indicated by an “enter (or line feed)” symbol.

  In particular, in FIG. 9, the type of update for how the record (the row) is processed is displayed at the beginning of each row. The symbols for each update state are as follows.

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

  Next, the query generation unit 35 receives an update document in which table information and data to be updated are defined, and generates a query statement for the updated data.

  That is, the query generation unit 35 analyzes the contents of the update document that defines the table information, and extracts the database, table, and column information to be updated. In addition, using the extracted information, a query for an input query, an update query, a deletion query, and the like are generated.

  In addition, the query generation unit 35 reads data for each record and generates an actual query for each record. At this time, a query statement of the type (for example, an add query statement, an update query statement, a delete query statement, etc.) is generated according to the update type of each record.

  In particular, the query generation unit 35 receives data to be updated as a stream, and generates a query sentence in real time according to the contents of each record to be updated in the order received as a stream.

  Next, the database (DB) update unit 36 requests the generated (created) query statement from the database 40 to request that the data in the target table be updated.

  The database (DB) update unit 36 requests the update by transferring the actual query statement generated in advance to the database 40 for each record. However, if a query statement is individually requested from the database 40 for each record, the entire update time is prolonged and the cost increases. This is because the database processes one request by one transaction, but there are many operations for performing the transaction.

  For this reason, preferably, the database (DB) update unit 36 batch-processes a large number of actual queries for each record. In other words, the query statement is generated only once, and the processing of the transfer data is performed by mapping only the column value to the variable of the query and adding it to the batch. A large amount of data can be processed.

  Next, a database update system using a spreadsheet interface according to the second embodiment of the present invention will be described in detail with reference to FIG. The configuration of the second embodiment of the present invention is the same as that of the first embodiment described above, except that the update status field is added to the column (column) on the worksheet and displayed in one row (row). To do.

  As illustrated in FIG. 10, the worksheet display unit 32 further displays a row for displaying the update state. In FIG. 10, the row displayed as “check2” is a record indicating the update state. That is, one row is added on the worksheet, and the row is used as a record indicating the update state. This is referred to as a second update state field (or second update state record). Similar to the first embodiment described above, the update status field of the column displaying the update status is referred to as a first update status field.

  Next, when one data field value (or cell value) is changed (updated) on the worksheet, the worksheet update unit 33 sets the first update field and the second update field corresponding to the field. Both display the update status. At this time, the second update field is displayed when the data value (or cell value) is changed. When one record is added and inserted on the worksheet (when a new record is added), the second update field corresponding to the cell into which a new value is input is “C”. It is displayed that it is updated. For this reason, since no value is described, the update state is not displayed in the corresponding second update field for the field remaining in the empty cell state. When the record is deleted, the content of the second update field is updated to display such as “U” or “C” and is not displayed. That is, the value of the second update field is not changed.

  In the example of FIG. 10, the data value is changed in the salary salary and telephone number phone_number fields in the records of the keys 105, 106, and 108. For this reason, only the second update field corresponding to these two fields is updated to “U” and displayed.

  Next, the update definition unit 34 generates the structure of the data set or the structure of the schema by combining only the field (column) and the key field displayed as “U” among the second update fields on the worksheet.

  In the example of FIG. 10, the key field is “employer ID (EMPLOYEE_ID)”, and the changed fields are salary salary and telephone number phone_number. Therefore, a data set structure or a schema structure is generated only for these fields (or columns). An XML document having a data set structure created according to the above example is as shown in FIG.

  As shown in FIG. 11, the column fields show only fields for employer ID, phone number and annual salary for xml documents only.

  Moreover, when the update definition part 34 produces | generates the updated data, it displays only the structure of the updated data set. That is, when data to be updated is written in a predetermined format and generated as a file, only the key field and the field value (column value) corresponding to the updated field are displayed and configured as a flat file. An example is shown in FIG.

  Next, the effects of the present invention will be described more specifically.

  First, the present invention has no restriction on the number of executions.

  Since the data registered / corrected / deleted by the user is transferred to the server via the upload stream, it can be handled freely from single data to millions of data. The data information is created as a flat file divided by the defined row and column delimiters and uploaded to the server. The server reads the data from the upload stream and uses the defined row and column delimiters. After parsing, it is bound to a query generated in advance from the xml information transferred together and executed.

  In addition, a large amount of data can be processed quickly.

  The basic method for processing data in the update server is to connect to the database via the JDBC driver, parse the xml information sent from the client, generate query statements corresponding to input, correction, and deletion, together This is to execute after binding variables to a query generated in advance while reading data from the upload stream sent from the client.

  At this time, the size (fetch size) is specified using PreparedStatement among the connected objects, the bound data is added using the PreparedStatement batch means addBatchmethod, and it is executed when all the data is added. To process the data. Since it is not a method of executing every case but a method of executing all at once, the load on the database is reduced and the speed is very high.

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

  Function to process target column processing instead of data binding when generating a query on the server by providing options to process not only data processed by the user on the screen but also database function, user generated function, etc. I will provide a.

  In addition, an Oracle sequence, a time function such as sysdate, and a DB function such as rownum are included in the query.

  As mentioned above, although the invention made by the inventor has been specifically described by the embodiments, the present invention is not limited to these embodiments, and various modifications can be made without departing from the scope of the invention. Needless to say, there is.

  For reference, the Korean national research and development projects that supported the present invention are as follows.

Assignment number: 10053204
Department name: Industry and Trade Resources Department Research management organization: Korea Industrial Technology Evaluation and Management Institute Research business name: Industrial Technology Innovation Business (Global Fusion ATC Business)
Research Project Name: Development of Petabyte-class non-professional business intelligence service platform technology capable of “realization of data processing and user interface” within 3 seconds based on 500 million data Subjective Organization: BI Matrix Co., Ltd. Project Year: 2015 June 1 to May 31, 2020

10: User terminal 20: Client 30: Update server 31: Data request unit 32: Worksheet display unit 33: Worksheet update unit 34: Update definition unit 35: Query generation unit 36: Database (DB) update unit 37: Storage unit 40: Database

Claims (8)

In a database update system using a spreadsheet interface that is connected to the database by a network,
A data request unit that requests and retrieves a data set of the target table from the database;
Providing a spreadsheet interface, generating a worksheet and displaying the structure of the data set and record data on the worksheet;
A worksheet update unit for updating record data on the worksheet according to a user input using the spreadsheet interface, and displaying an update state of each record data;
An update definition unit that defines the structure of the data set of the updated record and the record data for the record updated on the worksheet;
A query generation unit for generating a query statement for reflecting the data set structure and record data updated from the record data to the database;
A database update system using a spreadsheet interface, comprising: a database (DB) update unit configured to transfer a generated query statement to the database and update the target table. The data requesting unit provides the worksheet display unit with the result of the entire target table extracted from the database or a query for the target table as a data set, or for the extracted target table. 2. The query result is extracted using a query editor that performs a query, and the extracted query result is provided as a data set to the worksheet display unit and displayed on the worksheet. Database update system using the spreadsheet interface described in 1. The worksheet display unit displays the structure of the data set on any one line on the worksheet, and displays the data of the record from the line below the line on which the structure of the data set is displayed. A database update system using the spreadsheet interface according to claim 1. The worksheet display unit sets, for each data field in the structure of the data set, a cell format or column format for a column on the worksheet of the data field as a data type of the data field. A database update system using the spreadsheet interface according to claim 3. The worksheet display unit generates a record field by further adding a first update state field for displaying the update state of the record on the worksheet,
When the cell content of one record on the worksheet is updated, the worksheet update unit displays the type of update in the first update state field of the record,
2. The spreadsheet interface according to claim 1, wherein the update definition unit determines whether or not each record is updated and the type of update with reference to a first update state field on the worksheet. Database update system using When generating the worksheet, the worksheet display unit generates an event when a record content on the worksheet is updated in an execution module linked to the worksheet, and is called and executed by the event. 6. The spreadsheet interface according to claim 5, wherein the script function including a script function automatically displays an update type in the first update status field when the record is updated. Database update system used. The update definition unit defines the data set structure as an XML document and defines the updated data as a flat file. The update definition unit classifies the field values of each record by column and row separators defined in the flat file. 6. The database update system using a spreadsheet interface according to claim 5, wherein the system is defined as follows. The worksheet display unit further generates a record constituting a second update state field for displaying the update state of the field on the worksheet,
The worksheet update unit displays the type of update in the second update status field of the record field when the cell content of one record on the worksheet is changed or updated to input,
The update defining unit generates only a field in which input or change is updated in a key field and a second update state field among record fields, and generates a structure of a data set to be updated. A database update system using the spreadsheet interface according to claim 5.