WO2020233367A1 - Blockchain data storage and query method, apparatus and device, and storage medium - Google Patents

Abstract

The present application relates to the field of blockchains, and in particular to the field of block structure improvement. Disclosed are a blockchain data storage and query method, apparatus and device, and a storage medium. The blockchain data storage and query method comprises: generating an index database; acquiring a target structured query language (SQL) statement, with the target SQL statement being a legitimate statement conforming to an SQL syntax rule; parsing the target SQL statement according to a preset algorithm to obtain an abstract syntax tree; generating a logical execution plan according to the abstract syntax tree, with the logical execution plan comprising a data insertion plan, a data modification plan and a data query plan; and performing a read-write operation on a blockchain distributed database according to a logical generation plan and the index database to obtain a read-write result, with the blockchain distributed database being used for storing and querying data. In the present application, by encapsulating a blockchain underlying interface and using an SQL statement and an index database to realize data chaining storage and query, the development threshold is lowered, and the retrieval and development efficiency is improved.

Description

Block chain data storage and query method, device, equipment and storage medium

This application claims the priority of the Chinese patent application filed with the Chinese Patent Office on May 22, 2019, the application number is 201910427137.8, and the invention title is "Blockchain data storage and query methods, devices, equipment and storage media". The content is incorporated in this application by reference.

Technical field

This application relates to the field of block structure improvement, in particular to blockchain data storage and query methods, devices, equipment and storage media.

Background technique

Blockchain (BC) is an intelligent peer-to-peer network that uses distributed databases to identify, disseminate and record information. It is also called the Internet of Value. Blockchain is essentially a decentralized database. Blockchain Contains a list called blocks, which has a continuously growing and neatly arranged record. Each block contains a timestamp and a link to the previous block. The block chain makes the data immutable. Once recorded, the data in a block will be irreversible.

Blockchain distributed storage data is of key-value type. Although the bottom layer of blockchain technology also provides interfaces for data operations, these interfaces are relatively simple. The transaction information stored in the block is basically The existence of the string form requires the application to frequently parse the data information of the string. It only supports querying the data stored on the chain by key, and does not support complex queries, which is inconvenient to use.

In the actual project development process, the inventor realized that in order to modify the data originally stored in the relational data into the blockchain storage, it is necessary to rewrite the logic code of the data storage and query part, and also need to be familiar with the blockchain Data operation interface requires a large amount of development work, time-consuming, and low development efficiency.

Summary of the invention

The main purpose of this application is to solve the problem of insufficient support of the blockchain for complex queries and low retrieval efficiency. At the same time, it solves the need to rewrite the data query and storage logic code when the data storage is changed from a relational database to a blockchain distributed database. , Leading to the problems of large development workload, long time-consuming and low development efficiency.

In order to achieve the above purpose, the first aspect of this application provides a blockchain data storage and query method, including: generating an index database; obtaining a target structured query language SQL statement, the target SQL statement being legal in compliance with SQL syntax rules Statement; parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree; generate a logical execution plan according to the abstract syntax tree, and the logical execution plan includes inserting a data plan, modifying a data plan, and querying a data plan; according to The logic generation plan and the index database perform read and write operations on the blockchain distributed database to obtain read and write results. The blockchain distributed database is used to store and query data.

The second aspect of the present application provides a block chain data storage and query device, including: a first generating unit for generating an index database; an obtaining unit for obtaining a target structured query language SQL statement, the target SQL statement It is a legal statement that conforms to the SQL grammar rules; the parsing unit is used to parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree; the second generation unit is used to generate a logical execution plan according to the abstract syntax tree, The logic execution plan includes inserting data plans, modifying data plans, and querying data plans; a reading and writing unit is used to perform read and write operations on the blockchain distributed database according to the logic generation plan and the index database to obtain read and write operations As a result, the blockchain distributed database is used to store and query data.

A third aspect of the present application provides a blockchain data storage and query method and equipment, including: a memory and at least one processor, the memory stores instructions, and the memory and the at least one processor are interconnected by wires; The at least one processor invokes the instructions in the memory, so that the blockchain data storage and query device executes the methods described in the foregoing aspects.

The fourth aspect of the present application provides a computer-readable storage medium that stores instructions in the computer-readable storage medium, which when run on a computer, causes the computer to execute the methods described in the above aspects.

The fifth aspect of the present application provides a computer program product containing instructions, which when run on a computer, causes the computer to execute the methods described in the foregoing aspects.

In the embodiment of the present invention, through the processing of SQL statements, the use of SQL statements is compatible with query and storage in the blockchain distributed database and the relational database, and the corresponding relationship between the index database and the blockchain distributed database is established to improve the area. Retrieval efficiency of blockchain business information, encapsulate the underlying interface of blockchain, use SQL statements and index databases to achieve data storage and query on the chain.

Description of the drawings

Figure 1 is a schematic diagram of an embodiment of a blockchain data storage and query method in an embodiment of the application;

FIG. 2 is a schematic diagram of another embodiment of a blockchain data storage and query method in an embodiment of the application;

Fig. 3 is a schematic diagram of an embodiment of a block chain data storage and query device in an embodiment of the application;

Figure 4 is a schematic diagram of another embodiment of a blockchain data storage and query device in an embodiment of the present invention;

Fig. 5 is a schematic diagram of another embodiment of a blockchain data storage and query device in an embodiment of the application.

Detailed ways

The embodiment of the application provides a blockchain data storage and query method, device, device, and storage medium. In the embodiment of the application, through the processing of SQL statements, the use of SQL statements is compatible with blockchain distributed databases and relationships. Query and storage of type database, improve the retrieval efficiency of blockchain business information by establishing the corresponding relationship between the index database and the blockchain distributed database, encapsulate the underlying interface of the blockchain, and use SQL statements and index databases to achieve data on the chain Storage and query, lower development threshold and improve development efficiency.

For ease of understanding, the following describes the specific process of the embodiment of the present application. Please refer to Figure 1. An embodiment of the blockchain data storage and query method in the embodiment of the present application includes:

101. Generate an index database;

The server generates an index database according to business requirements. The index database is located in the data middleware of the blockchain. The data middleware also provides a unified interface for accessing the underlying blockchain of the blockchain. The unified interface for accessing the underlying blockchain of the blockchain includes the creation of data , Query, modify and delete (create read update delete, CURD). Index data is stored in a relational database MySQL, corresponding to the data type on a blockchain distributed database, and a one-to-one index data table is created in the index database. The structure of the index data table contains all the information of the data structure in the blockchain distributed database. For example: Store user points data on the blockchain. The user points data includes user ID, user name, and user points. Then create Table 1 user points user index table in the index database.

Table 1: User score user index table

Field Name	Field Type	Remarks
user_id	bigint	User ID, primary key
user_name	varchar	username
point	int	account credits
data_id	varchar	Index identification, corresponding to the key of the blockchain data

Table 1 includes an index identification field data_id, which corresponds to the key key for storing data on the blockchain distributed database. The server must query or store data on the blockchain distributed database according to the index identification. Each index table in the index database contains a data_id field. The data_id is unique in the index database and corresponds to the key in the blockchain distributed database one by one to establish the corresponding relationship between the index data and the blockchain data .

102. Obtain a target structured query language SQL statement, where the target SQL statement is a legal statement that conforms to the SQL syntax rules;

The server obtains the target structured query language SQL statement of the business request, and the target SQL statement is a legal statement that conforms to the SQL syntax rules. The SQL statement is consistent with the SQL statement used to access the relational database, including four SQL statements inserting insert, modifying update, querying select, and deleting delete, corresponding to different interfaces. Specifically, the server receives the initial SQL statement and parameters, encapsulates the initial SQL statement and parameters, and the server calls the relevant interface to obtain the encapsulated initial SQL statement and parameters to obtain the target SQL statement. The target SQL statement is a legal statement. The legality of the SQL has been judged before the business request, and the statement that does not conform to the SQL grammar rules is eliminated.

It should be noted that structured query language (SQL) is a database query and programming language, including SQL syntax rules, used to access data and manage relational database systems, SQL statements can be used to execute various Various operations, such as inserting data into the database, modifying data in the database, and extracting data from the database.

103. Analyze the target SQL statement according to the preset algorithm to obtain an abstract syntax tree;

The server processes the target SQL statement according to the preset algorithm to obtain the abstract syntax tree. Specifically, the server parses the target SQL statement according to the SQL parser, and the parsing includes lexical analysis and syntax analysis. Among them, lexical analysis refers to splitting the target SQL statement into lexical units that cannot be subdivided. In SQL grammar, lexical units include keywords, identifiers, literals, operators, and delimiters. For example, when the server reads the UPDATE of the target SQL statement, the server determines that the first character "U" meets the rules of keywords and identifiers, and the second character "P" also meets the rules of keywords and identifiers. By analogy, until the seventh character is a space, the server judges that the rule is not met, and the server has completed the recognition of a lexical unit. UPDATE is not only a keyword defined by the SQL specification, but also satisfies the rules of identifiers. Therefore, when a lexical unit is an identifier, the SQL parser judges the priority to determine whether a lexical unit is a keyword, and the priority of the keyword is high In the identifier rules. Other rules include that characters starting with a number are read according to the literal value of the numerical rules; those starting with double quotation marks or single quotation marks are read according to the literal value of the string rules; operators or delimiters are read according to the symbol characteristics Recognition.

The grammatical analysis obtains a lexical unit from the lexical analysis each time. If the rules are met, the extraction and matching of the next lexical unit will continue until the end of the string; if the rules are not met, an error will be prompted and the analysis will end. The grammar analysis finally converts the target SQL statement into an abstract syntax tree.

Optionally, the server uses the SQL Parser module of the database connection pool druid to parse the target SQL statement, and parse the natural semantic SQL statement into a more abstract data structure, that is, an abstract syntax tree (AST). It should be noted that the AST is a tree representation of the abstract syntax structure of the source code. Each node on the tree represents a structure in the source code. The AST does not represent every detail of the actual syntax. For example, Nested parentheses are implicit in the structure of the tree and are not presented as nodes.

104. According to the abstract syntax tree logical execution plan, the logical execution plan includes inserting the data plan, modifying the data plan, and querying the data plan;

The server logically executes the plan according to the abstract syntax tree. The logical execution plan includes inserting the data plan, modifying the data plan, and querying the data plan. Specifically, the server obtains SQL elements and data according to the abstract syntax tree, and generates a logical execution plan. The logical execution plan describes what data needs to be queried or stored, and the logical execution plan includes insert data plan, modify data plan, and query data plan. For example: a query request for user points, the server parses the received SQL query select statement, decomposes the original SQL string into a logical execution plan, that is, reads the value of the point field from the user index table, and the filter condition is the user name user_name The value is Tom's record.

105. Perform read and write operations on the blockchain distributed database according to the logical generation plan and the index database to obtain the read and write results. The blockchain distributed database is used to store and query data;

The server reads and writes the blockchain distributed database according to the logic generation plan and the index database to obtain the read and write results. The blockchain distributed database is used to store and query data. The read and write operations are inserted according to SQL commands into insert, modified update and query select. After the server parses the SQL statement to generate a logical execution plan, it operates the blockchain distributed database in conjunction with the index database to complete data query and storage operations.

When detecting that the logical execution plan is an insert data plan, the server performs an insert operation on the index database and the blockchain distributed database to obtain the read and write results. Specifically, the server generates the first index identifier; the server performs an insert operation on the index database according to the inserted data plan and the first index identifier to obtain the primary key value returned by the index database; the server queries the index database according to the primary key value, and obtains the returned value from the index database Insert data; the server performs an insert operation on the blockchain distributed database according to the first index identifier and the inserted data. For example, for a request to insert user points data, the server first sets the first index identifier data_id of the user points index table to the character string USER_1, and then the server writes the data to the user of the index database according to the user points insertion data plan and the first index identifier In the score index table, get the primary key value 1 of the primary key user_id, query the user score index table according to the primary key value 1, and get the query data. Use USER_1 in the query data as the key key of the blockchain distributed database, and combine other fields with The data is encapsulated in JavaScript Object Notation (JSON) format, and the JSON data is set as the value of the blockchain distributed database, and then the underlying interface of the blockchain is called to write the key and value to the blockchain distribution Style database.

When detecting that the logical execution plan is a modified data plan, the server modifies the index database and the blockchain distributed database to obtain the read and write results. According to the second index identification, the data is queried from the blockchain distributed database to obtain the initial data, and the initial data is updated and then written into the blockchain distributed database to ensure the data integrity and consistency of the blockchain distributed database. For example, for a request to modify the user score point field of the user score index table user, the server obtains the corresponding logical execution plan according to the request, and obtains the update element, then the server first updates the index database and obtains the second index identification data_id of the updated data Value, use the second index identifier as the key key, query the blockchain distributed database to obtain the initial value, which is JSON format data, modify the user’s point value for the initial value to obtain the target value, which is also JSON format data, and finally the server calls the underlying interface of the blockchain to write the key and target value into the blockchain distributed database.

When it is detected that the logical execution plan is a query data plan, the server performs a query operation on the index database and the blockchain distributed database to obtain the read and write results. The server obtains the query results from the blockchain distributed database to ensure the accuracy of the data. For example, for a request to query user points, the server queries the user points data index table according to the query statement, and obtains the value USER_1 of the third index identifier data_id field. The server uses USER_1 as the key to query the blockchain distributed database to obtain the query result.

In the embodiment of the application, through the processing of SQL statements, the use of SQL statements is compatible with the query and storage of the blockchain distributed database and the relational database, and the corresponding relationship between the index database and the blockchain distributed database is established to improve the area. Retrieval efficiency of blockchain business information, encapsulate the underlying interface of the blockchain, use SQL statements and index databases to achieve data storage and query on the chain, reduce development thresholds, and improve development efficiency.

Referring to Fig. 2, another embodiment of the blockchain data storage and query method in the embodiment of the present application includes:

201. Generate an index database;

The server generates an index database according to business requirements. The index database is located in the data middleware of the blockchain. The data middleware also provides a unified interface for accessing the underlying blockchain of the blockchain. The unified interface for accessing the underlying blockchain of the blockchain includes the creation of data , Query, modify and delete (create read update delete CURD). Index data is stored in a relational database MySQL, corresponding to the data type on a blockchain distributed database, and a one-to-one index data table is created in the index database. The structure of the index data table contains all the information of the data structure in the blockchain distributed database. For example: Store user points data on the blockchain. The user points data includes user ID, user name, and user points. Then create Table 1 user points user index table in the index database.

Table 1 includes an index identification field data_id, which corresponds to the key key for storing data on the blockchain distributed database. The server must query or store data on the blockchain distributed database according to the index identification. Each index table in the index database contains an index identification field. The index identification is unique in the index database and corresponds to the key key in the blockchain distributed database one by one. The index data and the blockchain data are correspondingly established. relationship. For example, if the index identifier data_id stored in the user score index table is USER_1, then a piece of user score data with a key of USER_1 is also stored in the blockchain distributed database. It should be noted that the index identifier is named data_id or index_id, which does not conflict with other fields of the index table in the index database, and the specifics are not limited here.

202. Obtain a target structured query language SQL statement, where the target SQL statement is a legal statement that complies with SQL grammar rules;

The server obtains the target structured query language SQL statement requested by the database, and the target SQL statement is a legal statement that conforms to the SQL syntax rules. The SQL statement is consistent with the SQL statement used to access the relational database, including four SQL statements inserting insert, modifying update, querying select, and deleting delete, corresponding to different interfaces. The target SQL statement is a legal statement. The legality of the SQL has been judged before the business request, and the statement that does not conform to the SQL grammar rules is eliminated. Specifically, the server receives a request including a target structured query language SQL statement and parameters. The request is a data format that encapsulates the target SQL statement and parameters according to preset rules, so that the server can obtain the target SQL statement and parameters in a uniform manner. For example, after encapsulating the select statement and parameter param of the database SQL query of user points, as shown in Table 2 User Points Query Request Table.

Table 2: User points query request form

parameter name	Parameter Type	value
sql	String	select point from user where user_name=?
param	String	{0:{sqlType:12,value:’Tom’}}

The parameter sql is a structured query language SQL statement, and the parameter param is a collection of other parameters of the JSON data structure. The server calls the relevant interface to parse the request including the target SQL statement and parameters, obtains the parameter sql, and obtains the target SQL statement.

It should be noted that structured query language (SQL) is a database query and programming language, including SQL syntax rules, used to access data and manage relational database systems, SQL statements can be used to execute various Various operations, such as inserting data into the database, modifying data in the database, and extracting data from the database.

Optionally, the server sets a configuration file for connecting to the database. The configuration file includes two configuration files, one is the configuration file for connecting to the relational database, and the other is the configuration file for connecting to the blockchain. The parameter names of the two configuration files are the same. Both include the URL of the accessed connection address and the database driver, but the parameter values are different. By setting different configuration files, the server realizes fast switching between relational databases and blockchain distributed databases, and uses the same SQL statement for storage and query operations without changing the existing business logic code. For example, by setting different configuration files, the user points service can be stored and queried centrally in the MySQL database, or distributed storage and query in the distributed blockchain database.

203. parse the target SQL statement according to the preset algorithm to obtain an abstract syntax tree;

The server processes the target SQL statement according to the preset algorithm to obtain the abstract syntax tree. Specifically, the server parses the target SQL statement according to the SQL parser, and the parsing includes lexical analysis and syntax analysis. Among them, lexical analysis refers to splitting the target SQL statement into lexical units that cannot be subdivided. In SQL grammar, lexical units include keywords, identifiers, literals, operators, and delimiters. For example, when the server reads the UPDATE of the target SQL statement, the server determines that the first character "U" meets the rules of keywords and identifiers, and the second character "P" also meets the rules of keywords and identifiers. By analogy, until the seventh character is a space, the server determines that the rule is not met, and then determines the recognition of a lexical unit. It should be noted that UPDATE is not only a keyword defined by the SQL specification, but also satisfies the identifier rules. Therefore, when a lexical unit is an identifier, the SQL parser judges the priority to determine whether the lexical unit is a keyword. Other rules include that characters starting with a number are read according to the literal value of the numerical rules; those starting with double quotation marks or single quotation marks are read according to the literal value of the string rules; operators or delimiters are read according to the symbol characteristics Recognition.

The grammatical analysis obtains a lexical unit from the lexical analysis each time. If the rules are met, the extraction and matching of the next lexical unit will continue until the end of the string; if the rules are not met, an error will be prompted and the analysis will end. The grammar analysis finally converts the target SQL statement into an abstract syntax tree. For example, for the target SQL statement:

SELECT user_id,user_name,point FROM user WHERE user_id>10;

After the server uses the SQL parser for lexical analysis, the lexical unit obtained includes the keywords SELECT, FROM and WHERE; the identifiers are user_id, user_name, point and user; the literal is 10; the operator is >; the delimiter is, and; . Grammatical analysis is based on the lexical unit for regular loop processing and branch selection, and finally parsed into an abstract syntax tree including the root node.

Optionally, the server uses the SQL Parser module of the database connection pool druid to parse the target SQL statement, and parse the natural semantic SQL statement into a more abstract data structure, that is, an abstract syntax tree (AST). It should be noted that the AST is a tree representation of the abstract syntax structure of the source code. Each node on the tree represents a structure in the source code. The AST does not represent every detail of the actual syntax. For example, Nested parentheses are implicit in the structure of the tree and are not presented as nodes.

204. Generate a logical execution plan according to the abstract syntax tree, where the logical execution plan includes inserting a data plan, modifying a data plan, and querying a data plan;

The server generates a logical execution plan according to the abstract syntax tree, and the logical execution plan includes inserting the data plan, modifying the data plan, and querying the data plan. Specifically, the server obtains SQL elements and data according to the abstract syntax tree, and generates a logical execution plan. The logical execution plan includes insert data plan, modify data plan, and query data plan, that is, it describes what data needs to be queried or what data is stored. Among them, inserting data plan includes inserting insert element, and modifying data plan includes modifying update element and query The data plan includes the query select element.

First, the server uses the visitor mode to traverse the AST syntax tree to obtain each element in the target SQL statement, which includes basic SQL elements and related data. The visitor pattern, also known as the visitor pattern, separates structure and data, writes a visitor to visit the elements in the data structure, and then delegates all the processing of each element to the visitor class.

Then, the server generates a logical execution plan through the elements in the SQL statement. The logical plan describes which data needs to be queried or stored. The data middleware gives the physical realization of the logical execution plan, calls the underlying block link port, and completes the actual execution. For example: Table 2 user points query request form, the server parses the received SQL query select statement, and decomposes the original SQL string into a logical execution plan, that is, reads the point field value from the user points user index table, the filter condition is user The name user_name is a record with Tom. Logical execution plans include inserting data plans, modifying data plans, and querying data plans.

205. Determine whether the logical execution plan is to insert a data plan, modify a data plan, or query a data plan;

The server determines whether the logical execution plan is an insert data plan, a modified data plan, or a query data plan. Specifically, the server determines whether the logical execution plan includes the insert element of SQL. If the logical execution plan includes the insert element of SQL, it determines that the logical execution plan is an insert data plan; the server determines whether the logical execution plan includes the update element of SQL. If the plan includes the update element of SQL, the logical execution plan is determined to be a modified data plan; the server determines whether the logical execution plan includes the select element of SQL, and if the logical execution plan includes the select element of SQL, the logical execution plan is determined to be an insert data plan.

It should be noted that the server operates the blockchain distributed database according to the logical generation plan and the index database, and the blockchain distributed database is used to store and query data. The operation is divided into insert according to the SQL command, update and query select. After the server parses the SQL statement to generate a logical execution plan, it operates the blockchain distributed database in conjunction with the index database to complete data query and storage operations.

206. If the logical execution plan is an insert data plan, insert the index database and the blockchain distributed database to obtain the read and write results;

If the logical execution plan is an insert data plan, the server performs an insert operation on the index database and the blockchain distributed database to obtain the read and write results. Specifically, the server generates a first index identifier, where the first index identifier is dynamically generated by the server according to preset rules and is unique; the server generates a SQL insert statement according to the insert data plan and the first index identifier, and further, the server obtains the insert The insert element, index table name, multiple field names to be inserted into the data and their corresponding field values in the data plan; the server obtains the index field name corresponding to the first index identifier; the server converts the field names and first index identifiers of multiple data to be inserted The corresponding index field name is set to multiple insertion index data table fields, and the field values and first index identifiers corresponding to the multiple data to be inserted are set to multiple insertion index data in the order corresponding to the multiple insertion index data table fields. The corresponding data value of the table field; the server constructs the SQL insert statement into the index database according to the SQL syntax rules for the insert element, the index table name, the data values corresponding to the insert element, the index table name, and the multiple insert index data table fields; The server inserts the index database according to the SQL insert statement, and obtains the primary key value returned by the index database; the server queries the index database according to the primary key value to obtain the inserted data returned by the index database; the server compares the block according to the first index identifier and the inserted data The chain distributed database performs an insert operation to obtain an insert result, which includes successful data insertion and data insertion failure, and the insert result is set as the read-write result. For example, an application requests to write user points. First, the server generates the first index identifier USER_1 according to the preset rules, and its SQL insert statement is as follows:

insert into`user`(user_id,user_name,point,data_id)values(1,‘Tom’,0,‘USER_1’);

The server then writes data into the user score user index table in the index database according to the SQL insert statement, and obtains the insertion result, as shown in Table 3, insert user score index table.

Table 3: Insert user points index table

user_id	user_name	point	data_id
1	Tom	0	USER_1

Finally, the server queries the user table in the index database to obtain the query data, uses USER_1 in the query data as the key key of the blockchain distributed database, and encapsulates other fields and data into JavaScript Object Notation (JSON) format , And set the JSON data {'userId':1,'userName':'Tom',point:0} as the value of the blockchain distributed database, and then call the underlying interface of the blockchain to write the key and value In the blockchain distributed database, the data stored on the blockchain after writing is shown in Table 4 Blockchain distributed data table, and the inserted result is returned to the terminal.

Table 4: Blockchain distributed data table

key	value
USER_1	{‘UserId’:1,‘userName’:’Tom’,point:0}

207. If the logical execution plan is to modify the data plan, modify the index database and the blockchain distributed database to obtain the read and write results;

If the logical execution plan is to modify the data plan, the server modifies the index database and the blockchain distributed database to obtain the read and write results. Specifically, the SQL modification statement is generated according to the modified data plan. Further, the server obtains the update element, index table name, modification condition, field names of multiple data to be modified and respective corresponding field values in the modified data plan; the server according to the SQL syntax rules Rebuild the update element, index table name, modification condition, field names of multiple data to be modified, and respective corresponding field values to obtain the SQL modification statement for modifying the index database; update the index database according to the SQL modification statement to obtain the second index identification; Query the blockchain distributed database according to the second index identifier to obtain the initial data of the blockchain; modify the initial data of the blockchain to obtain the target data of the blockchain; write the target data of the blockchain into the area according to the second index identifier The block chain distributed database obtains the modification result, the modification result includes the successful modification of the data and the modification failure, and the modification result is set as the read-write result. For example, the application requests to add 100 points to the user ID 1. First, the server generates the SQL modification statement as follows:

update`user`set point=point+100 where user_id=1;

Then, the server executes the SQL modification statement to update the user table in the index database, and the updated user table data is shown in Table 5 to update the user score index table.

Table 5: Update user points index table

user_id	user_name	point	data_id
1	Tom	100	USER_1

Finally, the server updates the data on the blockchain. Specifically, the server executes select data_id from`user`where user_id=1 to obtain the value of the second index identifier data_id field USERT_1; the server uses USERT_1 as the key to query the blockchain database and returns JSON data {'userId':1,'userName':'Tom',point:0}; the server converts the JSON data into the point update operation, and the updated point data is {'userId':1,'userName':' Tom',point:100}, finally, call the bottom-level interface of the blockchain to write the updated points data to the blockchain distributed database. The updated blockchain data is shown in Table 6 to update the blockchain distributed database, and Return the modified result to the terminal.

Table 6: Update the blockchain distributed database

Key	Value
USER_1	{‘UserId’:1,‘userName’:‘Tom’,point:100}

It should be noted that the data is queried from the blockchain distributed database according to the second index identification to obtain the initial data, and the initial data is updated and then written to the blockchain distributed database to ensure that the data in the blockchain distributed database is complete Sex and consistency.

208. If the logical execution plan is a query data plan, perform a query operation on the index database and the blockchain distributed database to obtain read and write results.

If the logical execution plan is a query data plan, then perform a query operation on the index database and the blockchain distributed database to obtain the read and write results. Specifically, the SQL query statement is generated according to the query data plan. Further, the server obtains the select element, index table name, query condition, and multiple field names to be queried in the query data plan; the server performs the query on the select element and index according to the SQL syntax rules The table name, query conditions and multiple field names to be queried are reconstructed to obtain the SQL query statement for querying the index database; query the index database according to the SQL query statement to obtain the third index identification; read the blockchain according to the third index identification Distributed database, get the query result, and set the query result as the read-write result. For example, the application requests to query the points of user ID 1, the target SQL statement is select point from`user`where user_id=1, the SQL query statement generated by the server is select data_id from`user`where user_id=1; the server executes the SQL query statement Read the user table in the index database, and the return result is USER_1; the server uses the key value USER_1 to read the blockchain distributed database, and the query data is {'userId':1,'userName':'Tom',point:100 }, the server parses the query data, obtains point=100 and returns it to the application program. It should be noted that, when the data amount of the query result is 0, it means that the query result is empty. The server obtains the query results from the blockchain distributed database to ensure the accuracy of the data.

Further, for complex SQL statement requests such as multi-table joint query, grouping and sorting, the basic idea of processing is to first parse the target SQL statement, analyze the logical execution plan, and then rewrite the SQL statement on the logical execution plan to obtain the third The index identifies data_id, and reads the basic data from the blockchain distributed database according to the data_id, and finally aggregates and calculates the data to obtain the final query result.

Optionally, the server sends the first message to the message queue to obtain the transaction identifier; the server writes the index database according to the transaction identifier to obtain the write result; the server determines whether the write result is successful; if the write result is If the write is successful, the server writes the blockchain distributed database through the message queue; if the write result is a write failure, the server deletes the index database through the message queue. Specifically, the server first writes the data into the index database, and then writes the data into the blockchain distributed database according to the index identification. The server uses the transaction message function provided by the distributed message middleware RocketMQ to write index data and send distribution The message is sent to RocketMQ in one transaction, ensuring that the writing of data and sending of the message succeed or fail at the same time. The server writing to the blockchain distributed database is completed by detecting RocketMQ messages, adding the write log table to ensure idempotence, and achieving the ultimate consistency of the index database and the blockchain distributed database.

In the embodiment of the application, through the processing of SQL statements, the use of SQL statements is compatible with the query and storage of the blockchain distributed database and the relational database, and the corresponding relationship between the index database and the blockchain distributed database is established to improve the area. Retrieval efficiency of blockchain business information, encapsulate the underlying interface of the blockchain, use SQL statements and index databases to achieve data storage and query on the chain, reduce development thresholds, and improve development efficiency.

The block chain data storage and query method in the embodiment of the application is described above, and the block chain data storage and query device in the embodiment of the application is described below. Please refer to FIG. 3, the block chain data storage in the embodiment of the application An embodiment of the query device includes: a first generating unit 301, used to generate an index database; an obtaining unit 302, used to obtain a target structured query language SQL statement, the target SQL statement is a legal statement that conforms to SQL grammar rules; a parsing unit 303 , Used to parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree; the second generating unit 304 is used to generate a logical execution plan according to the abstract syntax tree, the logical execution plan includes inserting a data plan, modifying a data plan, and querying data Plan; The read-write unit 305 is used to perform read and write operations on the blockchain distributed database according to the logic generation plan and index database to obtain the read and write results. The blockchain distributed database is used to store and query data.

In the embodiment of the application, through the processing of SQL statements, the use of SQL statements is compatible with the query and storage of the blockchain distributed database and the relational database, and the corresponding relationship between the index database and the blockchain distributed database is established to improve the area. Retrieval efficiency of blockchain business information, encapsulate the underlying interface of the blockchain, use SQL statements and index databases to achieve data storage and query on the chain, reduce development thresholds, and improve development efficiency.

4, another embodiment of the device for storing and querying blockchain data in the embodiment of the present invention includes: a first generating unit 301, configured to generate an index database; and an acquiring unit 302, configured to acquire a target structured query language SQL Statement, the target SQL statement is a legal statement that conforms to the SQL grammar rules; the parsing unit 303 is used to parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree; the second generating unit 304 is used to generate logic according to the abstract syntax tree Execution plan, the logical execution plan includes inserting data plan, modifying data plan and querying data plan; the reading and writing unit 305 is used to read and write the blockchain distributed database according to the logic generation plan and index database to obtain the read and write results, The blockchain distributed database is used to store and query data.

Optionally, the second generating unit 304 may also be specifically used to: traverse the abstract syntax tree through the visitor mode to obtain the elements of the target SQL statement; generate a logical execution plan according to the elements of the target SQL statement, and the logical execution plan includes an insert data plan , Modify the data plan and query the data plan.

Optionally, the reading and writing unit 305 may further include: a judging subunit 3051, for judging whether the logical execution plan is an insert data plan, a modified data plan, or a query data plan; an insert subunit 3052, if the logical execution plan is an insert data plan , It is used to insert operations on the index database and the blockchain distributed database to obtain the read and write results; the modification subunit 3053, if the logical execution plan is to modify the data plan, it is used for the index database and the blockchain distributed database Perform modification operations to obtain read and write results; the query subunit 3054, if the logical execution plan is a query data plan, is used for query operations on the index database and blockchain distributed database to obtain the read and write results.

Optionally, the insert subunit 3052 can also be specifically used to: generate a first index identifier; generate a SQL insert statement according to the insert data plan and the first index identifier; perform an insert operation on the index database according to the SQL insert statement, and obtain the return value of the index database Primary key value; query the index database according to the primary key value to get the inserted data returned by the index database; insert the blockchain distributed database according to the first index identifier and the inserted data to obtain the insert result, and set the insert result to read and write result.

Optionally, the modification subunit 3053 can also be specifically used to: generate SQL modification statements according to the modified data plan; update the index database according to the SQL modification statements to obtain the second index identification; query the blockchain distributed database according to the second index identification, Obtain the initial data of the blockchain; modify the initial data of the blockchain to obtain the target data of the blockchain; write the target data of the blockchain into the distributed database of the blockchain according to the second index identification, obtain the modified result, and then modify the result Set to read and write results.

Optionally, the query subunit 3054 can also be specifically used to: generate SQL query statements according to the query data plan; query the index database according to the SQL query statements to obtain the third index identifier; read the blockchain distributed database according to the third index identifier To get the query result, and set the query result as the read-write result.

Optionally, the blockchain data storage and query device further includes: a sending unit 306, configured to send a first message to the message queue to obtain a transaction identifier; and a first writing unit 307, configured to write to the index database according to the transaction identifier In operation, the writing result is obtained; the judgment unit 308 is used to judge whether the writing result is successful; the second writing unit 309, if the writing result is successful, is used to check the blockchain through the message queue The distributed database performs a write operation; the deleting unit 310, if the write result is a write failure, is used to delete the index database through the message queue.

In the embodiment of the present invention, through the processing of SQL statements, the use of SQL statements is compatible with query and storage in the blockchain distributed database and the relational database, and the corresponding relationship between the index database and the blockchain distributed database is established to improve the area. Retrieval efficiency of blockchain business information, encapsulate the underlying interface of the blockchain, use SQL statements and index databases to achieve data storage and query on the chain, reduce development thresholds, and improve development efficiency. The above figures 3 and 4 describe in detail the block chain data storage and query device in the embodiment of the present invention from the perspective of modular functional entities. The following describes the block chain data storage and query device in the embodiment of the present invention from the perspective of hardware processing Give a detailed description.

FIG. 4 is a schematic structural diagram of a blockchain data storage and query device provided by an embodiment of the present application. The blockchain data storage and query device 500 may have relatively large differences due to different configurations or performance, and may include one or One or more processors (central processing units, CPU) 501 (for example, one or more processors) and memory 509, one or more storage media 508 for storing application programs 509 or data 509 (for example, one or one storage device with a large amount of ). Among them, the memory 509 and the storage medium 508 may be short-term storage or persistent storage. The program stored in the storage medium 508 may include one or more modules (not shown in the figure), and each module may include a series of instruction operations for storing and querying blockchain data. Furthermore, the processor 501 may be configured to communicate with the storage medium 508, and execute a series of instruction operations in the storage medium 508 on the blockchain data storage and query device 500. The instructions include: 101. Generate an index database; 102. Obtain a target structured query language SQL statement, where the target SQL statement is a legal statement that complies with the SQL grammar rules; 103. parse the target SQL statement according to a preset algorithm , Obtain an abstract syntax tree; 104, generate a logical execution plan according to the abstract syntax tree, the logical execution plan includes inserting a data plan, modifying a data plan, and querying a data plan; 105, generating a plan and the index database according to the logic Perform read and write operations on the blockchain distributed database to obtain read and write results. The blockchain distributed database is used to store and query data.

The blockchain data storage and query device 500 may also include one or more power supplies 502, one or more wired or wireless network interfaces 503, one or more input and output interfaces 504, and/or, one or more operating systems 505 , Such as Windows Serve, Mac OS X, Unix, Linux, FreeBSD and so on. Those skilled in the art can understand that the structure of the blockchain data storage and query device shown in FIG. 4 does not constitute a limitation on the blockchain data storage and query device, and may include more or fewer components than shown in the figure, or Combining certain components, or different component arrangements.

The functional units in the various embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units may be integrated into one unit. The above-mentioned integrated unit can be implemented in the form of hardware or software functional unit. If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it may be stored in a computer-readable storage medium, which may be a non-volatile readable storage The medium may also be a volatile readable storage medium. Based on this understanding, the technical solution of this application essentially or the part that contributes to the existing technology or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium , Including several instructions to make a computer device (which can be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the method described in each embodiment of the present application. The steps include: 101. Generating an index database; 102. Obtaining a target structured query language SQL statement, where the target SQL statement is a legal statement complying with SQL grammar rules; 103. Analyzing the target SQL statement according to a preset algorithm , Obtain an abstract syntax tree; 104, generate a logical execution plan according to the abstract syntax tree, the logical execution plan includes inserting a data plan, modifying a data plan, and querying a data plan; 105, generating a plan and the index database according to the logic Perform read and write operations on the blockchain distributed database to obtain read and write results. The blockchain distributed database is used to store and query data. The aforementioned storage media include: U disk, mobile hard disk, read-only memory (read-only memory, ROM), random access memory (random access memory, RAM), magnetic disk or optical disk and other media that can store program code .

Claims (20)

1. A blockchain data storage and query method, which includes:

   Generate index database;

   Acquiring a target structured query language SQL statement, where the target SQL statement is a legal statement that complies with SQL grammar rules;

   Parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree;

   Generating a logical execution plan according to the abstract syntax tree, the logical execution plan including inserting a data plan, modifying a data plan, and querying a data plan;

   Perform read and write operations on the blockchain distributed database according to the logic generation plan and the index database to obtain read and write results. The blockchain distributed database is used to store and query data.
2. The blockchain data storage and query method according to claim 1, wherein said generating a logical execution plan according to said abstract syntax tree, said logical execution plan including inserting data plan, modifying data plan and querying data plan comprises:

   Traverse the abstract syntax tree through the visitor mode to obtain the elements of the target SQL statement;

   A logical execution plan is generated according to the elements of the target SQL statement, and the logical execution plan includes an insert data plan, a modified data plan, and a query data plan.
3. The blockchain data storage and query method according to claim 2, wherein the read and write operations on the blockchain distributed database are performed according to the logic generation plan and the index database to obtain a read and write result, the Blockchain distributed database is used to store and query data including:

   Judging whether the logical execution plan is the inserted data plan, the modified data plan, or the query data plan;

   If the logical execution plan is the insert data plan, perform an insert operation on the index database and the blockchain distributed database to obtain the read and write result;

   If the logical execution plan is the modified data plan, modify the index database and the blockchain distributed database to obtain the read and write results;

   If the logical execution plan is the query data plan, a query operation is performed on the index database and the blockchain distributed database to obtain the read and write results.
4. The blockchain data storage and query method according to claim 3, wherein if the logical execution plan is the inserted data plan, insert the index database and the blockchain distributed database The operation to obtain the read and write result includes:

   Generate the first index identifier;

   Generating an SQL insert statement according to the insert data plan and the first index identifier;

   Performing an insert operation on the index database according to the SQL insert statement to obtain the primary key value returned by the index database;

   Query the index database according to the primary key value to obtain the inserted data returned by the index database;

   Perform an insert operation on the blockchain distributed database according to the first index identifier and the insert data to obtain an insert result, and set the insert result as the read and write result.
5. The blockchain data storage and query method according to claim 3, wherein if the logical execution plan is the modified data plan, then the index database and the blockchain distributed database are modified The operation to obtain the read and write result includes:

   Generate SQL modification statements according to the modification data plan;

   Update the index database according to the SQL modification statement to obtain a second index identifier;

   Query the blockchain distributed database according to the second index identifier to obtain initial blockchain data;

   Modify the initial data of the blockchain to obtain the target data of the blockchain;

   Write the block chain target data to the block chain distributed database according to the second index identifier, obtain a modification result, and set the modification result as the read and write result.
6. The blockchain data storage and query method according to claim 3, wherein, if the logical execution plan is the query data plan, query the index database and the blockchain distributed database The operation to obtain the operation result includes:

   Generating SQL query statements according to the query data plan;

   Query the index database according to the SQL query statement to obtain a third index identifier;

   Read the blockchain distributed database according to the third index identifier to obtain a query result, and set the query result as the read-write result.
7. The blockchain data storage and query method according to any one of claims 1 to 6, wherein the method further comprises:

   Send the first message to the message queue to obtain the transaction identifier;

   Perform a write operation on the index database according to the transaction identifier to obtain a write result;

   Judging whether the writing result is successful;

   If the writing result is that the writing is successful, perform a writing operation on the blockchain distributed database through the message queue;

   If the writing result is a writing failure, the index database is deleted through the message queue.
8. A block chain data storage and query device, wherein the block chain data storage and query device includes:

   The first generating unit is used to generate an index database;

   The obtaining unit is used to obtain a target structured query language SQL statement, where the target SQL statement is a legal statement that complies with SQL grammar rules;

   The parsing unit is used to parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree;

   The second generating unit is configured to generate a logical execution plan according to the abstract syntax tree, and the logical execution plan includes inserting a data plan, modifying a data plan, and querying a data plan;

   The reading and writing unit is configured to perform read and write operations on the blockchain distributed database according to the logic generation plan and the index database to obtain read and write results. The blockchain distributed database is used to store and query data.
9. A blockchain data storage and query device, wherein the blockchain data storage and query device includes a memory and at least one processor, the memory stores instructions, the memory and the logarithmic at least one processor Interconnected by wires;

   The at least one processor invokes the instructions in the memory, so that the blockchain data storage and query device executes the following steps:

   Generate index database;

   Acquiring a target structured query language SQL statement, where the target SQL statement is a legal statement that complies with SQL grammar rules;

   Parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree;

   Generating a logical execution plan according to the abstract syntax tree, the logical execution plan including inserting a data plan, modifying a data plan, and querying a data plan;

   Perform read and write operations on the blockchain distributed database according to the logic generation plan and the index database to obtain read and write results. The blockchain distributed database is used to store and query data.
10. The blockchain data storage and query device according to claim 9, wherein said generating a logical execution plan according to said abstract syntax tree, said logical execution plan including inserting data plan, modifying data plan and querying data plan comprises:

    Traverse the abstract syntax tree through the visitor mode to obtain the elements of the target SQL statement;

    A logical execution plan is generated according to the elements of the target SQL statement, and the logical execution plan includes an insert data plan, a modified data plan, and a query data plan.
11. The blockchain data storage and query device according to claim 10, wherein the read and write operations on the blockchain distributed database are performed according to the logic generation plan and the index database to obtain a read and write result, the Blockchain distributed database is used to store and query data including:

    Judging whether the logical execution plan is the inserted data plan, the modified data plan, or the query data plan;

    If the logical execution plan is the insert data plan, perform an insert operation on the index database and the blockchain distributed database to obtain the read and write result;

    If the logical execution plan is the modified data plan, modify the index database and the blockchain distributed database to obtain the read and write results;

    If the logical execution plan is the query data plan, a query operation is performed on the index database and the blockchain distributed database to obtain the read and write results.
12. The blockchain data storage and query device according to claim 11, wherein, if the logical execution plan is the inserted data plan, insert the index database and the blockchain distributed database The operation to obtain the read and write result includes:

    Generate the first index identifier;

    Generating an SQL insert statement according to the insert data plan and the first index identifier;

    Performing an insert operation on the index database according to the SQL insert statement to obtain the primary key value returned by the index database;

    Query the index database according to the primary key value to obtain the inserted data returned by the index database;

    Perform an insert operation on the blockchain distributed database according to the first index identifier and the insert data to obtain an insert result, and set the insert result as the read and write result.
13. The blockchain data storage and query device according to claim 11, wherein if the logical execution plan is the modified data plan, the index database and the blockchain distributed database are modified The operation to obtain the read and write result includes:

    Generate SQL modification statements according to the modification data plan;

    Update the index database according to the SQL modification statement to obtain a second index identifier;

    Query the blockchain distributed database according to the second index identifier to obtain initial blockchain data;

    Modify the initial data of the blockchain to obtain the target data of the blockchain;

    Write the block chain target data to the block chain distributed database according to the second index identifier, obtain a modification result, and set the modification result as the read and write result.
14. The blockchain data storage and query device according to claim 11, wherein, if the logical execution plan is the query data plan, query the index database and the blockchain distributed database The operation to obtain the operation result includes:

    Generating SQL query statements according to the query data plan;

    Query the index database according to the SQL query statement to obtain a third index identifier;

    Read the blockchain distributed database according to the third index identifier to obtain a query result, and set the query result as the read-write result.
15. The blockchain data storage and query device according to any one of claims 9-14, wherein the method further comprises:

    Send the first message to the message queue to obtain the transaction identifier;

    Perform a write operation on the index database according to the transaction identifier to obtain a write result;

    Judging whether the writing result is successful;

    If the writing result is that the writing is successful, perform a writing operation on the blockchain distributed database through the message queue;

    If the writing result is a writing failure, the index database is deleted through the message queue.
16. A computer-readable storage medium having a computer program stored thereon, wherein: the computer program is executed by a processor to implement the following steps:

    Generate index database;

    Acquiring a target structured query language SQL statement, where the target SQL statement is a legal statement that complies with SQL grammar rules;

    Parse the target SQL statement according to a preset algorithm to obtain an abstract syntax tree;

    Generating a logical execution plan according to the abstract syntax tree, the logical execution plan including inserting a data plan, modifying a data plan, and querying a data plan;

    Perform read and write operations on the blockchain distributed database according to the logic generation plan and the index database to obtain read and write results. The blockchain distributed database is used to store and query data.
17. The computer-readable storage medium according to claim 16, wherein the generating a logical execution plan according to the abstract syntax tree, the logical execution plan including inserting a data plan, modifying a data plan, and querying a data plan comprises:

    Traverse the abstract syntax tree through the visitor mode to obtain the elements of the target SQL statement;

    A logical execution plan is generated according to the elements of the target SQL statement, and the logical execution plan includes an insert data plan, a modified data plan, and a query data plan.
18. The computer-readable storage medium according to claim 17, wherein the read and write operations are performed on the blockchain distributed database according to the logic generation plan and the index database to obtain a read and write result, and the blockchain The distributed database used to store and query data includes:

    Judging whether the logical execution plan is the inserted data plan, the modified data plan, or the query data plan;

    If the logical execution plan is the insert data plan, perform an insert operation on the index database and the blockchain distributed database to obtain the read and write result;

    If the logical execution plan is the modified data plan, modify the index database and the blockchain distributed database to obtain the read and write results;

    If the logical execution plan is the query data plan, a query operation is performed on the index database and the blockchain distributed database to obtain the read and write results.
19. The computer-readable storage medium according to claim 18, wherein, if the logical execution plan is the insert data plan, insert operations are performed on the index database and the blockchain distributed database to obtain The read and write results include:

    Generate the first index identifier;

    Generating an SQL insert statement according to the insert data plan and the first index identifier;

    Performing an insert operation on the index database according to the SQL insert statement to obtain the primary key value returned by the index database;

    Query the index database according to the primary key value to obtain the inserted data returned by the index database;

    Perform an insert operation on the blockchain distributed database according to the first index identifier and the insert data to obtain an insert result, and set the insert result as the read and write result.
20. The computer-readable storage medium according to claim 18, wherein if the logical execution plan is the modified data plan, a modification operation is performed on the index database and the blockchain distributed database to obtain The read and write results include:

    Generate SQL modification statements according to the modification data plan;

    Update the index database according to the SQL modification statement to obtain a second index identifier;

    Query the blockchain distributed database according to the second index identifier to obtain initial blockchain data;

    Modify the initial data of the blockchain to obtain the target data of the blockchain;

    Write the block chain target data to the block chain distributed database according to the second index identifier, obtain a modification result, and set the modification result as the read and write result.

Images