RU2515565C1 - Method of updating structured data in relational database management system - Google Patents

Abstract

FIELD: physics, computer engineering.

SUBSTANCE: invention relates to computer engineering. The method of updating structured data in a relational database management system involves forming an updated database structure containing metadata and a set of altered data; comparing the structure of the initial and updated database by comparing data and metadata of the structures, and if the structures of the initial and updated databases are identical, the structure comparison result is transformed into a data format of a given structure, which depends on the structure of lines of the initial and updated databases returned by a request, thereby obtaining fragments of metadata in the data format of the given structure; comparing the obtained metadata fragments while forming comparison results; analysing the formed comparison results and selecting the essence - different data lines in the data format of the given structure of the formatted data descriptor fragment, in the updated or initial database, using the selected essence; creating a data file describing difference of data and describing all actions bringing data of the initial database into an updated state, using the formed data file; updating data in the initial database by inverse transformation of data from the data format of the given structure into a request format.

EFFECT: high accuracy and quality of comparing and updating data in relational database management system.

7 cl, 3 dwg

Description

The invention relates to computing (computer networks), in particular to a method for updating structured data in a relational database management system (RDBMS), and can be used in a DBMS for managing and exchanging updates for various types of data: text or numeric, or date, or BLOB - Binary Large Object, which is a special data type designed to store images, audio and video, as well as compiled program code.

RDBMS models are characterized by the simplicity of forming a data structure in a database management system, and a user-friendly structural (tabular) representation of data.

In the database design process, the structure of the RDBMS is determined (the composition of the data tables, their structure and logical relationships). The structure of the RDBMS is determined by the composition of the columns, the type of data and the size of the columns, table keys.

The basic concepts of the RDBMS model “entity - relationship” include: entities, relationships between them and their attributes (properties).

Entities are basic data types: numerical values, dates and times, strings, and individual characters that are stored in the RDBMS.

The RDBMS model is focused on organizing data in the form of two-dimensional relational data tables. Each relational data table is a two-dimensional data array and has the following properties:

- each element of a two-dimensional array - one data element,

- all cells in a column of a two-dimensional array (table)

homogeneous, i.e. all elements in the column are of the same type

(numeric, character, etc.),

- each column has a unique name,

- there are no identical rows in the table,

- the order of rows and columns can be arbitrary [K.J.Date. Introduction to Database Systems = Introduction to Database Systems. - 8th ed. - M.: Williams, 2006 .-- P.1328. - ISBN 0-321-19784], [http: ru .// wikipedia.org/wiki/Relational_DBMS].

An example of the location of a two-dimensional data array in the form of a relational data table is shown in figure 1.

Over the past decade, in connection with the development of computer technology and the use of computer technology, the task of not only storing a large amount of various data (various information) in databases, but also updating and replenishing it, is becoming increasingly important.

The invention is known by patent CA 2464927 "Text Joins for Data Cleansing and Integration in a Relational Database Management System", Int. Cd G06F 17/30; G06F 7/02, publication date 10/21/2004 (priority document - application US No. 200404641 P dated 04/21/2003), which for updating structured data (files on the device) contains a system for comparing strings in several respects in an RDBMS and includes creating a set of lines from the TERS ofcharac set, decomposing each line into a subset of tokens, creating at least two relations within the line, establishing the relationship threshold similarity, selecting at least two relations, correlating the similarity threshold relations and returning all tokens that meet the criteria cx dstva threshold).

This approach allows you to compare string data in RDBMS tables, but due to the complexity of both structural and technological characteristics of data storage and processing systems, this approach to data comparison can no longer be considered optimal or sufficient for efficient comparison of heterogeneous data that can be stored in modern database management systems.

The disadvantage of this invention is that this approach does not allow you to adapt to the growing needs of storage systems and data processing, in terms of storage and processing of information other than text, as it focuses solely on the processing of text information, which significantly limits the scope of this technology to compare very large volumes of diverse information (video, audio) and other digital data that are now widely used in connection with the spread of web-technology Guy, and especially web 2.0 technologies.

The closest technical solution (prototype) to the claimed invention is published on February 3, 2005, application US No.2005027717 "Text joins Text joins for data cleansing and integration in a relational database management system", Int. Cl .: G06F 17/30 (priority documents are applications US No. 2004,02828819 dated April 21, 2004; US No. 20030464101P dated April 21, 2003). This technical solution is a system for comparing strings in several respects in an RDBMS, including creating a set of strings from a character set, decomposing each string into a subset of tokens, creating at least two relationships within the strings, setting a similarity threshold for the relationships, selecting at least two relations, correlating the similarity threshold relations and the return of all tokens that meet the threshold similarity criteria.

Again, this method allows you to process (compare) text data stored in the RDBMS. But the current vector of development of data processing and storage technologies shows that it is necessary to develop more general solutions that allow the processing of heterogeneous data at a higher level and with greater efficiency.

The disadvantage of this invention is that the method describes the processing of only text information, which no longer corresponds to the current level of technology. Now more stringent and complex requirements are being placed on the processing of large volumes of heterogeneous (heterogeneous) information.

Therefore, the task of comparing and updating structured data in a relational database management system is still relevant.

The technical problem to which the claimed invention is directed is to increase the accuracy and quality of comparing and updating structured data in an RDBMS.

The stated technical problem is solved by the claimed method of updating structured data in a relational database management system in which the source database is formed from rows of the same structure, where each row is represented by a set of fields with specified values, and a combination of values of the same field in different rows forms a column data values and metadata, each of which has its own data type, are numbered line by line so that each line gets a unique number, and remember it,

according to the invention:

form the structure of the updated database containing metadata and a set of modified data in relation to the original database,

comparing the structures of the original and updated databases by comparing the data and metadata of the structures using the operating system interface that provides this data and metadata from the database server,

if the comparison results show that the structures of the original and updated databases are not identical, then complete the comparison procedure;

if the comparison results show that the structures of the source and updated databases are identical, then the result of the comparison of structures is transformed into the data format of the given structure, depending on the structure of the rows returned by the query from the original and updated databases, receiving metadata fragments in the data format of the given structure,

comparing the received fragments of metadata in the data format of a given structure through the operating system interface, forming the comparison results,

analyze the generated comparison results and distinguish entities - different data rows in the data format of the given structure of the formatted fragment-data descriptor, in the updated and original database,

using the selected entities, they form a data file describing the difference in data in the structures of the updated and the original database, the changed data and metadata, data on the data structures and a description of all the actions that must be performed on the data of the updated and the original database to bring the data of the original database data to the updated state,

using the generated data file, the data in the source database is updated by transforming the data back from the data format of the given structure to the query format, determining the type of data changes and updating the data in the source database.

The column of data and metadata values has its own data type: text or numeric, or date type, or BLOB - Binary Large Object, which is a special data type intended, for example, for storing images, audio and video, as well as compiled program code .

Data and metadata from the database server is obtained through the ODBC interface - Open Database Connectivity, which provides access to databases.

Comparison of the structures of the source and updated databases is performed using the XML language.

As a data format of a given structure, use the format

XML

An XML file is used as a generated data file for updating data in the source database.

Updating the data in the source database is performed by reverse transforming the data from the data format of the specified XML structure to the format of SQL queries, determining the type of data changes of the type INSERT, or UPDATE, or DELETE.

Thus, improving the accuracy and quality of comparing and updating structured data in an RDBMS is achieved by forming the structure of the updated database, comparing the structures of the original and updated databases, highlighting entities (structures and rows) in the original and updated databases, comparing data in tables and lines, the formation of a data file that describes the difference between the data in the structures of the updated and the original database, the changed data and metadata, data on the data structures and a description of all the actions that must be performed on the data of the updated and the original database in order to bring the data of the original database into the updated state, using the generated data file, update the data in the original database by reverse transforming the data from the data format of the given structure to the query format, determining the type of data changes .

A patent and scientific and technical search carried out by the applicant did not reveal the features of this invention in the prior art.

Further, a detailed description of the invention is illustrated by examples with reference to the drawings.

Figure 1 shows an example of the location of a two-dimensional data array in the form of a relational table.

Figure 2 in a generalized form shows an example implementation of the claimed invention according to the claimed invention.

Figure 3 illustrates an algorithm for comparing structures, data and metadata in the source and updated databases, analysis of the generated comparison results, selection of entities and the formation of a data file that describes the difference in data in the structures of the updated and source databases and a description of all actions that should be performed on the data to bring the data of the original database in the updated state (according to the claimed invention).

Carry out the invention as follows.

In the relational database management system, the initial database is formed from rows of the same structure (Fig. 1), where each row is represented by a set of fields with specified values, and a combination of values of the same field in different rows forms a column of data values and metadata, each of which has its own data type: text or numeric, or date type, or BLOB - Binary Large Object, which is a special data type intended, for example, for storing images, audio and video, as well as compiled software about the code. They are numbered line by line so that each line gets a unique number, and remember it.

According to the invention (figure 2) form the structure of the updated database containing metadata and a set of modified data in relation to the original database.

The structures of the source and updated databases are compared by comparing the data and the metadata of the structures using, for example, the ODBC interface of the operating system, which provides this data and metadata from the database server. If the comparison revealed that the structures of the original and updated databases are not identical, then complete the comparison procedure. If the comparison results show that the structures of the source and updated databases are identical, then the result of the comparison of structures is transformed into the data format of the given structure, depending on the structure of the rows returned by the query from the original and updated databases, receiving metadata fragments in the data format of the given structure.

The result of comparing database structures is transformed into a data format of a given structure, for example XML, depending on the structure of the rows returned by the query from the source and updated databases, obtaining metadata fragments in the data format of the XML structure.

Compare the received fragments of metadata in the data format XML structure via the operating system interface, forming the comparison results.

The generated comparison results are analyzed and entities are distinguished - differing data strings in the data format of the XML structure, XML-formatted fragment-data descriptor, in the updated and original database.

Using the selected entities, they form an XML data file that describes the difference between the data in the structures of the updated and the original database, the changed data and metadata, data on the data structures and a description of all the actions that must be performed on the data of the updated and the original database to bring the data The source database is in an updated state.

Using the generated XML data file, the data in the source database is updated by transforming the data back from the data format of the given XML structure to the SQL query format, determining the type of data changes of the type INSERT or UPDATE, or DELETE.

The inventive method for updating structured data in a relational database management system is implemented on known devices used in computer networks (computer technology), and does not require additional significant hardware costs. Such well-known devices include, for example, the SQL Server 2008 R2 family of software and hardware devices developed by Microsoft and optimized for databases [Michael Ooty. Firmware SQL Server 2008 R2. Windows IT Pro No. 4, 2012]. To form the source and updated databases, SQL Server software and hardware devices (servers) are used, data and metadata from database servers are received via the ODBC interface, and the format and language XML are used as software tools.

Consider an example implementation of the claimed invention, using to illustrate figure 1, 2 and 3.

On the database server No. 1 (hereinafter referred to as the database server No. 1), the initial database is formed from rows of the same structure (as shown in FIG. 1). In this case, for example, the source database (hereinafter referred to as the source database) contains data and metadata (hereinafter referred to as data No. 1, data No. 2, data No. 4).

On the database server No. 1 (figure 2) form the structure of the updated database (hereinafter referred to as the updated database) containing metadata and a set of changed data in relation to the original database (hereinafter - data No. 1, data No. 4, data No. 3).

The structures of the original and updated databases are compared by comparing data and metadata of structures using, for example, the ODBC interface of the operating system (Open Database Connectivity), which ensures the receipt of this data and metadata from database server No. 1.

Get the result of the comparison:

Delete "2. Data No. 2"

Add "3. Data No. 3".

The result of comparing the database structures is transformed into the data format of the given XML structure, depending on the structure of the rows returned by the query of the original and updated database, obtaining metadata fragments in the data format of the XML structure.

Compare the received fragments of metadata in the data format XML structure via the operating system interface, forming the comparison results.

The generated comparison results are analyzed and entities are distinguished - differing data strings in the data format of the XML structure, XML-formatted fragment-data descriptor, in the updated and original database.

Using the selected entities, they form an XML data file that describes the difference between the data in the structures of the updated and the original database, the changed data and metadata, data on the data structures and a description of all the actions that must be performed on the data of the updated and the original database to bring the data The source database is in an updated state.

Using the generated XML data file, the data in the source database is updated by transforming the data back from the data format of the given XML structure to the SQL query format, determining the type of data changes of the INSERT and / or UPDATE and / or DELETE type.

SQL is a universal computer language used to create, modify and manage data in relational databases.

INSERT - allows you to add rows to the table, filling them with values.

UPDATE is an SQL statement that allows you to update data values in specified columns of a table.

Delete - in languages like SQL, DML - the operation of deleting data records from a table.

The generated data file is used to update structured data in other databases on other servers, for example, on the database server No. 2 (as shown in figure 2).

Figure 3 illustratively shows a comparison algorithm according to the claimed invention. Using the ODBC interface of the operating system, the control server comparison signal is simultaneously received from the database server No. 1 to the original database and the updated database. This signal compares the structures of the original and updated databases by comparing the data and metadata of the structures. Get the result of the comparison:

- if the database structure is not identical, then complete the comparison procedure;

- if the database structures are identical, then the result of comparing the database structures is transformed into the data format of the specified XML structure, depending on the structure of the rows returned by the query from the original and updated database, receiving metadata fragments in the data format of the XML structure.

Compare the received fragments of metadata in the data format XML structure via the operating system interface, forming the comparison results.

The generated comparison results are analyzed and entities are distinguished - differing data strings in the data format of the XML structure, XML-formatted fragment-data descriptor, in the updated and original database.

Using the selected entities, they form an XML data file that describes the difference between the data in the structures of the updated and the original database, the changed data and metadata, data on the data structures and a description of all the actions that must be performed on the data of the updated and the original database to bring the data The source database is in an updated state.

Using the generated XML data file, the data in the source database is updated by transforming the data back from the data format of the given XML structure to the SQL query format, determining the type of data changes of the type INSERT or UPDATE, or DELETE.

Thus, the inventive method provides an RDBMS efficient and high-quality management of updates of various types of structured data: text or numeric, or date, or BLOB, designed to store images, audio and video, as well as compiled program code.

Claims (7)

1. A method for updating structured data in a relational database management system in which the source database is formed from rows of the same structure, where each row is represented by a set of fields with specified values, and a combination of values of the same field in different rows forms a column of data values and metadata, each of which has its own data type, is numbered line by line so that each line gets a unique number, and remember it, characterized in that they form the structure of the updated database data containing metadata and a set of modified data with respect to the source database, compare the structures of the source and updated databases by comparing the data and metadata of the structures using the operating system interface, which receives this data and metadata from the database server, if the comparison results reveal that the structures of the original and updated databases are not identical, then complete the comparison procedure, if the comparison shows that the structures of the original and updated databases are identical, then the result of the comparison of structures is transformed into the data format of the specified structure, depending on the structure of the rows returned by the query of the source and updated databases, receiving metadata fragments in the data format of the given structure, the obtained metadata fragments in the data format of the given structure via the operating system interface, forming comparison results, analyze the generated comparison results and highlight the entities - differing data rows in the data format of a given structure In the updated and original databases, using the selected entities, the formatted data descriptor fragment urls form a data file that describes the difference in data in the structures of the updated and original databases, changed data and metadata, data on data structures and a description of all actions that should be performed on the updated and source database data in order to bring the source database data into the updated state, using the generated data file, update the data in the source database by means of the reverse transformation of data from the data format of a given structure to the query format, determining the type of data changes. 2. The method according to claim 1, characterized in that the column of data and metadata values has its own data type: text or numeric, or date type, or BLOB - Binary Large Object, which is a special data type intended, for example, for storing images , audio and video, as well as compiled code. 3. The method according to claim 1, characterized in that the data and metadata from the database server are obtained through the ODBC interface - Open Database Connectivity, which provides access to databases. 4. The method according to claim 1, characterized in that the comparison of the structures of the source and updated databases is performed using the XML language. 5. The method according to claim 1, characterized in that the XML format is used as the data format of the given structure. 6. The method according to claim 1, characterized in that an XML format file is used as the generated data file for updating data in the source database. 7. The method according to claim 1, characterized in that the data in the original database is updated by reverse transforming the data from the data format of the given XML structure into the SQL query format, determining the type of data changes of the type INSERT or UPDATE, or DELETE.

Images