CN111309765A

CN111309765A - Method and device for directly querying structured data

Info

Publication number: CN111309765A
Application number: CN202010075999.1A
Authority: CN
Inventors: 魏永明; 耿岳; 薛淑明
Original assignee: Beijing Feynman Software Technology Co ltd
Current assignee: Beijing Feynman Software Technology Co ltd
Priority date: 2020-01-22
Filing date: 2020-01-22
Publication date: 2020-06-19
Anticipated expiration: 2040-01-22
Also published as: CN111309765B

Abstract

The invention discloses a method and a device for directly inquiring structured data, wherein the method comprises the following steps: carrying out validity check according to a data query request sent by a user agent to generate an effective data query statement; executing according to the effective data query statement to obtain a query result; and writing the query result into the socket to obtain the response output of the query result. According to the method and the device for directly querying the structured data, the new lsql, rsql and rsqls diagrams are introduced, so that a method for directly accessing a local or remote database and executing an SQL statement is provided for an HTML webpage, and the code amount for writing a front-end script program or a background server program is reduced. Therefore, by implementing the method and the device, the technical problems that additional script programs need to be written when the structured data is operated and the occupied memory is large in the prior art are solved.

Description

Method and device for directly querying structured data

Technical Field

The invention relates to the technical field of webpage front ends, in particular to a method and a device for directly querying structured data.

Background

The front end of the webpage is a webpage which runs on browsers such as a PC end and a mobile end and is displayed for a user to browse. The front-end technology is generally divided into front-end design and front-end development, the front-end design can be generally understood as visual design of a website, and the front-end development is the foreground code implementation of the website and comprises basic HTML (Hypertext markup Language) and CSS (Cascading Style Sheets) and JavaScript/ajax.

Before HTML5, the browser only had access to the database through the HTTP server, and could not directly manipulate the local database (only providing a simple local storage mechanism of cookies). In HTML5, an interface for accessing a local storage and a local database is added. The local storage includes a session storage (sessionStorage) and a local persistent storage (localStorage). The local database is a local database created by the webpage script, and CRUD operation can be executed by SQL sentences, so that the webpage can operate the structured data more conveniently.

However, the above-described functions in HTML5 can all be used only by writing an ECMAScript script program; therefore, when the prior art is adopted to operate structured data, additional script programs need to be written, and the workload of developers is increased; meanwhile, the ECMAScript adopted in the scheme is an interpreted programming language, the execution efficiency is relatively low, and the memory is large.

Disclosure of Invention

In view of this, embodiments of the present invention provide a method and an apparatus for directly querying structured data, so as to solve the problem that in the prior art, when structured data is operated, an additional script program needs to be written, and the workload of developers is increased.

The technical scheme provided by the invention is as follows:

the first aspect of the embodiments of the present invention provides a method for directly querying structured data, where the method includes the following steps: carrying out validity check according to a data query request sent by a user agent to generate an effective data query statement; executing according to the effective data query statement to obtain a query result; and writing the query result into a socket to obtain the response output of the query result.

Optionally, executing according to the valid data query statement, and before obtaining a query result, further including: judging whether the data query is a local query or a remote query according to the effective data query statement; when the data query is a local query, judging whether the queried database executes a data query request or not according to the effective data query statement; when the data query request is being executed in the queried database, the valid query statement is saved.

Optionally, the method for directly querying structured data further includes: when the data query is a remote query, connecting a remote management module according to the effective data query statement; and controlling the number of the inquired servers to be smaller than a preset threshold value according to the remote management module.

Optionally, performing validity check according to the data query request sent by the user agent, and generating an effective data query statement, including: checking whether the data query request exceeds a set value according to the data query request sent by the user agent; when the data query request does not exceed a set value, separating request parameters in the data query request; and generating an effective data query statement according to the separated request parameters.

Optionally, executing according to the valid data query statement to obtain a query result, including: when the queried database does not execute the data query request, determining the user identity according to the user identifier in the valid data query statement; opening a queried database according to the user identity; and executing the effective data query statement according to the queried database to obtain a query result.

Optionally, executing according to the valid data query statement to obtain a query result, including: when the data query is a remote query, determining a remote server where the queried database is located and a corresponding program interface according to the effective data query statement; sending the valid data query statement to the remote server according to the program interface; and receiving a query result corresponding to the effective data query statement according to the program interface.

Optionally, writing the query result into a socket according to the query result to obtain a response output of the query result, where the response output includes: setting a key value of a response body in a query result according to the query result; calculating the length of a response body according to the query result; and writing the response body key value and the response body length into a socket to obtain the response output of the query result.

A second aspect of the embodiments of the present invention provides an apparatus for directly querying structured data, where the apparatus includes: the request validity checking module is used for checking the validity according to the data query request sent by the user agent to generate an effective data query statement; the execution module is used for executing according to the effective data query statement to obtain a query result; and the response output module is used for writing the socket according to the query result to obtain the response output of the query result.

A third aspect of the embodiments of the present invention provides a computer-readable storage medium, which stores computer instructions for causing a computer to execute the method for directly querying structured data according to any one of the first aspect and the first aspect of the embodiments of the present invention.

A fourth aspect of the present invention provides a terminal for directly querying structured data, including: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the computer instructions to perform the method for directly querying structured data according to any one of the first aspect and the first aspect of the embodiments of the present invention.

The technical scheme provided by the invention has the following effects:

according to the method and the device for directly querying the structured data, the new lsql, rsql and rsqls diagrams are introduced, so that a method for directly accessing a local or remote database and executing an SQL statement is provided for an HTML webpage, and the code amount for writing a front-end script program or a background server program is reduced. Therefore, by implementing the method and the device, the technical problems that additional script programs need to be written when the structured data is operated and the occupied memory is large in the prior art are solved.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and other drawings can be obtained by those skilled in the art without creative efforts.

FIG. 1 is a flow diagram of a method of directly querying structured data, in accordance with an embodiment of the present invention;

FIG. 2 is a flow diagram of a method of directly querying structured data, according to another embodiment of the invention;

FIG. 3 is a flow diagram of a method of directly querying structured data, according to another embodiment of the invention;

FIG. 4 is a flow diagram of a method of directly querying structured data, according to another embodiment of the invention;

FIG. 5 is a flow diagram of a method of directly querying structured data, according to another embodiment of the invention;

FIG. 6 is a block diagram of an apparatus for directly querying structured data according to an embodiment of the present invention;

FIG. 7 is a schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

FIG. 8 is a schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

FIG. 9 is a schematic diagram of an apparatus for querying structured data directly according to another embodiment of the present invention;

FIG. 10 is a schematic diagram of an apparatus for directly querying structured data according to another embodiment of the present invention;

fig. 11 is a schematic diagram of a terminal hardware structure for directly querying structured data according to an embodiment of the present invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, but not all, embodiments of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

An embodiment of the present invention provides a method for directly querying structured data, as shown in fig. 1, the method includes the following steps:

step S101: and carrying out validity check according to the data query request sent by the user agent to generate an effective data query statement.

In an embodiment, before the validity check is performed, a data query request sent by a user agent may be obtained according to a preset protocol, where the data query request includes a request line, a header, an empty line, and a request body. The preset protocol may be a Unix Domain Socket communication protocol between the local server and the user agent (browser or other client) process, which is similar to HTTP1.1 and may be referred to as LSQL/RSQL protocol, and in this embodiment, the name of the communication protocol may be LSQL/1.0, and 1.0 refers to a version number.

In one embodiment, a user agent (browser or other program) may initiate an asynchronous request or a synchronous request depending on the requirements of the web page (whether the request is to be executed synchronously or asynchronously may be specified via the Ajax interface) or the context (the form submission request is typically executed synchronously). Synchronous requests or asynchronous requests are processed within the user agent, in relation to the context of the web page content or script, and need not be concerned with the request processing process. Therefore, for LSQL/RSQL and other requests, the process and subsequent processing of initiating the request at the user agent side is almost the same as the schema of HTTP and the like. The differences are as follows: for the diagrams such as HTTP and the like, a URL request processing process is connected with a remote server, sends a request according to the agreement of an HTTP protocol and waits for the return of the server; for the LSQL/RSQL schema, the URL request processing process is connected to the local server process providing LSQL/RSQL service through the socket, sends the request through the socket and waits for the content returned by the server; for the diagrams such as HTTP and the like, processing local cache by the URL request processing process according to response header information of an HTTP protocol; for the LSQL/RSQL schema, the URL request processing process does not process the local cache, i.e., does not do local cache processing for any results returned by the LSQL/RSQL local server.

Assuming that there is a SQL database locally, the SQL query can be executed on it through the following URL. lsql: ///data/testdb/db? sqlQuery ═ SELECT% 20 a% 2C% 20B% 2C% 20sum (C)% 20 FROM% 20tab 1% 20 GROUP% 20 BY% 20 a% 3B

In this URL, lsql is a new URL schema (schema) introduced by the present invention to specify that this URL will execute an SQL query directly on the local SQL database; the data/testdb specifies the path of the local SQL database file; db specifies the target database name of the SQL statement to be executed thereafter; the value of the sqlQuery parameter is an SQL statement coded by a URI (special characters in the URL, such as spaces, commas, semicolons and the like, are subjected to escape processing), and the decoded content is as follows:

SELECT a，b，sum(c)FROM tab1 GROUP BY a

the URL may also be understood as initiating an LSQL request (or LSQL request) with the target resource being the db database in the local database file/data/testdb and the SQL query statements to be executed being SELECT a, b, sum (c) FROmTab1 GROUP BY a.

In addition, the SQL database may also be located on a remote server, on which SQL queries may be executed via the following URLs:

rsql：//username：password＠foo.bar.com：3306/mysql/db？sqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B

in this URL, rsql is a new URL schema (schema) introduced by the present invention to specify that this URL will perform SQL queries directly on the remote SQL database; username is the user account name; password is a password corresponding to the user account; com is the host name running the SQL database; 3306 is the port number for the SQL database to provide SQL query services; the/mysql/db specifies the type of the remote SQL database and the target database name of the SQL statement to be executed thereafter; the value of the sqlQuery parameter is an SQL statement coded by a URI (meaning processing is carried out on special characters in the URL, such as a space, a comma, a semicolon and the like), and the decoded content is as above.

Com, the target resource is database db (database type mysql) on the remote host, and the SQL query statement to be executed is SELECT a, b, sum (c) FROM tab1 GROUP BY a. The remote host processes the rsql request on port 3306, and the user account name and password used for connection are: username, password.

In addition, as with the encrypted transport protocol where https is http, rsqls may be used as the encryption schema for rsql (the lsql schema need not support encrypted transport).

In an embodiment, for the LSQL URL, the delivery may be performed by using a GET method or a POST method. When the GET method is adopted, the LSQL request sent by the user agent has the following contents:

GET

/data/testdb/db？sqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B LSQL/1.0

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)Gecko/20050225Firefox/1.0.1

User－Name：username

in addition, when the data query request is transmitted by adopting the POST method, the request content is as follows:

POST/data/testdb/db LSQL/1.0

User－Id：100

Content－Type：application/x－www－form－urlencoded

Content－Length：71

sqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B

in the two data query requests transmitted by the GET method or the POST method, the corresponding header information includes: User-Agent (representing a User Agent identification string), User-Id (representing either a User identifier of the executing User Agent or User-Name, 100 in this example), User-Name (representing either a User account Name of the executing User Agent or a User-Id), which will be used as a security check, primarily on non-Unix-Type operating systems, Content-Type (specifying the Content Type of the requesting body, typically application/x-www-form-encoded, when using the POST method), Content-Length (specifying the Content Length (number of bytes) of the requesting body, 67 in the above example, when using the POST method).

In an embodiment, for the RSQL URL, the delivery may be performed by a GET method or a POST method. When the GET method is adopted, the content of the RSQL request sent by the user agent is as follows:

GET

/mysql/db？sqlQuery＝SELECT％20a％2C％20b％2C％20sum(c)％20FROM％20tab1％20GROUP％20BY％20a％3B RSQL/1.0

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)Gecko/20050225 Firefox/1.0.1

Host：foo.bar.com

Port：3306

User：username

Password：password

POST/mysql/db RSQL/1.0

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)

Gecko/20050225Firefox/1.0.1

Host：foo.bar.com

Port：3306

User：username

Password：password

Content－Type：application/x－www－form－urlencoded

Content－Length：71

for RSQL requests, the header information it contains is partially the same as the header information of LSQL. In addition, the header information of the RSQL request further includes: host (indicating a Host computer where the remote database to be connected is specified), Port (specifying a Port number for providing services for the remote database), User (specifying a User name for connecting the remote database), and Password (specifying a Password corresponding to the User name for connecting the remote data).

In an embodiment, for the encryption mode RSQLs request of RSQL URL, the request may also be initiated by RSQL protocol, except that the RSQLs request needs to additionally provide SSL connection certificate and other information in the header information. Therefore, when the RSQLS request is delivered by the GET method, the content of the RSQLS request is as follows:

GET

User－Agent：Mozilla/5.0(Windows；U；Windows NT 5.1；en－US；rv：1.7.6)

Gecko/20050225Firefox/1.0.1

Host：foo.bar.com

Port：3306

User：username

Password：password

SSL－Ca：/home/mysql/ca－cert.pem

SSL－Cert：/home/mysql/client－cert.pem

SSL－Key：/home/mysql/client－key.pem

for the RSQLS request, the header information further includes: SSL-Ca (indicating that a Ca certificate at the time of establishing an SSL connection is designated), SSL-Cert (indicating that a client certificate at the time of establishing an SSL connection is designated), SSL-Key (indicating that a client Key at the time of establishing an SSL connection is designated).

Step S102: and executing according to the effective data query statement to obtain a query result.

In one embodiment, before executing the valid data query statement, the method further comprises: judging whether the data query is a local query or a remote query according to the effective data query statement; when the data query is a local query, judging whether the queried database executes a data query request or not according to the effective data query statement; when a data query request is being executed in the queried database, a valid query statement is saved.

Because the queried database can be located in a local database or a database of a remote server, a schema in an effective data query statement can be identified according to a predefined local query (LSQL) and a remote query (RSQL), when the query is determined to be the local query (LSQL), because the LSQL request uses a single SQL database engine (SQLite), and no small database engines such as SQLite provide concurrent access capability for the database, before the SQL statement is executed, it is further required to check whether a specific LSQL request is being executed on the queried database, and if so, a new request can be executed after the request is finished. In this case, a queue is used to store new requests, and when a request ends, the stored request is checked and executed if there are no other executing requests on the corresponding database.

In addition, other database engines such as Oracle Berkeley DB can be used to implement the lsql schema, but support for SQL statements is required, which includes relevant modules for SQL syntax parsing and execution. In the case of supporting multiple different local database engines, the lsql schema may determine the specific database engine by determining the format of the database file.

In one embodiment, when the data query is a remote query, connecting the remote management module according to an effective data query statement; and controlling the number of the inquired servers to be smaller than a preset threshold value according to the remote management module.

In particular, since the SQL execution requested by RSQL is performed on a remote server, the differences between the RSQL and different remote database types are handled by loading different sub-drivers before execution, while a remotely connected management module is required to manage these connections: the remote management module controls the number of connections to the same remote server not to exceed a limit; controlling the number of control connections to the same remote server from the same user agent does not exceed a limit; frequent closing and opening of connections pointing to the same remote database is avoided, and when users are the same, preferential multiplexing of connections should be considered.

In this embodiment, in order to distinguish different types of remote databases (e.g., MySQL, PostgreSQL, etc.), a mechanism for negotiating the type of the database instance after establishing a connection is added to the rsql schema implementation, and the user agent loads a sub-driver corresponding to the type of the database instance according to the negotiation result to complete communication with the remote database instance. Meanwhile, different schema names can be directly defined for different database types, such as:

mysql：//foo.bar.com/db？sqlQuery＝...

postgresql：//foo.bar.com/db？sqlQuery＝...

sqlserver：//foo.bar.com/db？sqlQuery＝...

db2：//foo.bar.com/db？sqlQuery＝...

step S103: and writing the query result into the socket to obtain the response output of the query result. Specifically, when generating the response output of the query result, a JSON object representative response body may be first constructed, and then the socket is written according to the format requirement of the LSQL/RSQL response, so as to finally obtain the corresponding output of the query result.

According to the method for directly querying the structured data, the new lsql, rsql and rsqls diagrams are introduced, so that a method for directly accessing a local or remote database and executing an SQL statement is provided for an HTML webpage, and the code amount for writing a front-end script program or a background server program is reduced. Therefore, by implementing the method and the device, the technical problems that additional script programs need to be written when the structured data is operated and the occupied memory is large in the prior art are solved.

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 2, step S101 performs validity check according to a data query request sent by a user agent, and generates an effective data query statement, including the following steps:

step S111: checking whether the data query request exceeds a set value according to the data query request sent by the user agent; in particular, it is necessary to check whether the number of pending and unreturned LSQL requests or RSQL requests exceeds a set value, including the number of all pending requests from a single user agent running instance (distinguished by socket identifiers) and the number of all pending LSQL requests or RSQL requests.

Step S112: and when the set value is not exceeded, separating the request parameters in the data query request.

In one embodiment, for an LSQL request, the database file path (dbFile, e.g.,/data/testdb), database name (dbName, e.g., db), user identifier (userld) to open the database, user name (userName) to open the database, varying SQL statement (sqlQuery), and row format (sqlRowFormat) of the result set in the request row and in the request body may be separated and organized as dictionary data results. In addition, the request line and other request parameters in the request body can be separated.

Where userId is the user identifier used to open the database. Although the request header of LSQL contains User-Id information, we cannot rely on this header information to determine the User identifier of the User agent process, but should get the process on the other end of the Unix socket and the User identifier information through a system call. On Unix-like systems, this information is obtained by calling the getsockopt function to obtain the value of the SO _ PERCRED option. If the user identifier obtained by the system call does not match the user identifier in the request header by the LSQL, then illegal request processing should be done.

Specifically, ￥ can be used as a prefix to define a variable in the data query request, such as ￥ Id, which indicates that the variable is to be replaced by a URL parameter of the same name transmitted at the same time when generating the final data query request.

lsql：///data/testdb/db？sqlQuery＝DELETE％20FROM％20Books％20WHERE％20Id％3D￥Id％3B&Id＝1

The URL contains two parameters, wherein the value corresponding to sqlQuery is a variant quantization SQL statement, the decoded value is DELETEFROM Books WHERE Id ￥ Id, and the value corresponding to Id is 1, the parameters are used for replacing ￥ Id in the variant quantization SQL statement, therefore, the SQL statement finally executed by the URL is DELETE FROM Books WHERE Id 1

Through the variable quantization SQL statement (data query request), different values in the SQL statement can be set through the input items in the form, and the variable quantization SQL statement can be kept unchanged, so that the coding amount is reduced. And if the quantized SQL statement is not used, additional script code will be needed to generate the final SQL statement.

The variable names in the variable quantity SQL sentences meet the following specification that the characters which begin with ￥ characters and immediately follow the characters must be letters and only contain numbers, letters and underlines, any non-numbers, letters and underlines represent the termination of the variable names, the situation that ￥ characters are reserved and numbers are used as sequence reference parameters, the variable names are case sensitive, that is, ￥ Abc and ￥ Abc represent different variables, in order to correctly process the situation that the original SQL sentences contain ￥ characters, ￥ characters are subjected to the escape processing, the original SQL sentences are processed into the variable names according to the above rules, ￥￥ is used for the escape processing, and if the SQL sentences do not process into the variable names according to the above rules, the escape processing is not needed.

The original SQL statement as follows:

SELECT＊FROM Books WHERE Code＝＇￥name￥15￥＇

the following escaping process is required:

SELECT＊FROM Books WHERE Code＝＇￥￥name￥￥15￥＇

in addition, for the variable quantity SQL statement, variable name definition rules different from ￥ characters can also be adopted, such as%, (A) and the like.

For example, all the Books of the Fyodor dostovisky need to be searched from the Books table, and the ISBN is used to sort the results, so the corresponding SQL statement should be:

SELECT Title，Isbn FROM Books WHERE Author＝＇Fyodor Dostoyevsky＇SORTBY Isbn ASC；

but the quantization processing can be performed on the SQL statement according to the above specification of quantization processing:

SELECT Title，Isbn FROM Books WHERE Author＝＇￥author＇SORT BY￥sortBy￥sortOrder；

then, when the data query request is transmitted, the real variable value can be transmitted through the following codes: lsql: ///data/testdb/db? sqlQuery [% SELECT 20 Title% ] 2C% ] 20Isbn [% 20FROM [% 20Books ]. 20WHERE [% 20Author ] ]. 3D ']. 24 auror' ] 20SORT [% 20BY ] ]. 24SORTBy [% ] 20 SORTOrder [% ] 3B & auror [% Fyodor ] ]. 20Dostoyevsky & sortBy [ ]. Isbn & sortOrder ]. ASC

Since the user agent performs the quantization processing when generating the data query request, when separating the request line from the request parameter in the request body, the quantization SQL statement (sqlQuery) parameter can be separated.

In an embodiment, when the LSQL request is checked for validity, it may also be checked whether the length of the requesting entity and the length of each URI defined in the request header exceed the limit values; it is checked whether the user identifier userId or userName defined in the request header matches the option value returned by the socket. (ii) a Check dbFile specifies whether a database file exists and check access rights. (ii) a The value specified by sqlRowFormat is checked to ensure one of ditt or array.

In one embodiment, for RSQL requests, Host (Host, e.g., foo. bar. com), database name (Port, e.g., 3306), user name (odbservame) used to connect to the remote database, password (dbPassword) used to connect to the remote database, remote database type (dbType, e.g., mysql), remote database name (dbName, e.g., db), varying SQL statement (sqlQuery), row format of result set (sqlRowFormat), CA certificate file (SSLCa) used to establish SSL connection, client certificate file (sslkert) used to establish SSL connection, and client key file (ssey) used to establish SSL connection may be separated and organized as dictionary data result. In addition, the request line and other request parameters in the request body can be separated.

In an embodiment, when performing validity check on the RSQL request, it may also check whether the length of the requesting entity and the length of each URI defined in the request header exceed the limit value; checking whether a user identifier userId or userName defined in the request header matches an option value returned by the socket; checking whether a given database type (dbType) is supported; the value specified by sqlRowFormat is checked to ensure one of ditt or array.

Meanwhile, when the validity check is performed on the RSQL request, a connection to a specified host and a port number can be established (if SSLCa is specified, encrypted connection is used), and parameters such as dbUsername, dbPasserid, dbName and the like are transmitted.

Step S113: and generating a valid data query statement according to the separated request parameters. Specifically, when generating the final valid data query statement, it is necessary to determine whether all the variables of the SQL statement that meet the convention include corresponding parameters, and if yes, replace the variables with the values of the parameters, that is, replace the variables with actual values, thereby generating the valid data query statement to be executed.

It should be noted that, when the validity of the request is checked, an error state may be returned in advance in the case of an illegal request. The error status may be represented by status codes such as 400Bad Request, 403Forbidden, 401Unauthorized, 501Not Implemented, or 404Not Found. Other status codes may be included, which may be a subset of the HTTP status codes. Specifically, the message state code (corresponding to HTTP state code range of 100-199) and the redirection state code (corresponding to HTTP state code range of 300-399) can be included.

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 3, the step S102 is executed according to the valid data query statement to obtain a query result, and includes the following steps:

step S201: when the data query request is not being executed in the queried database, the user identity is determined according to the user identifier in the valid data query statement.

Step S202: and opening the queried database according to the user identity.

Step S203: and executing the effective data query statement according to the queried database to obtain a query result.

Specifically, for an LSQL request, if there is no executing request on the database corresponding to the new LSQL request, the request may be executed, and the flow is as follows: creating a pipeline, and executing a fork system call creation subprocess; calling the setup in the child process to set up a valid user identifier, and simultaneously setting one end of a pipeline created by the parent process as standard output (a file descriptor is 1); so far, the sub-process will run with the user identity specified by userId, and because the local server is usually executed with the root user identity, if the setup system call is not called, a huge security hole will be created, so that a general user agent can utilize the LSQL to request to execute the high-risk SQL query statement; and the sub-process opens the database and executes SQL query, analyzes the result of the SQL query and converts the result into JSON format, and then prints the result to standard output. In the parent process, reading the other end of the pipeline can obtain the child process, namely the query result.

In one embodiment, for a Unix-like system, the parent process may call the following function to accomplish the above and return the conduit file descriptor of the read SQL query output:

it should be noted that, when the LSQL request is executed, the server as a parent process should call waitpid system call to monitor the exit status of the child process, so as to determine whether the SQL query is executed correctly, and set the status code in the LSQL response accordingly. At this stage, a status code may be generated (an error occurs in processing the request).

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 4, step S102 is executed according to the valid data query statement to obtain a query result, and includes:

step S211: and when the data query is a remote query, determining a remote server where the queried database is located and a corresponding program interface according to the effective data query statement.

Step S212: and sending the effective data query statement to a remote server according to the program interface.

Step S213: and receiving a query result corresponding to the effective data query statement according to the program interface.

Specifically, when the data query is a remote query, that is, when the data query request sent by the user agent is a request for RSQL, since the large SQL database issues an SDK, the SDK is used by other applications to connect to the remote server to execute the SQL query, and return the result. Therefore, for RSQL requests, the following procedure can be followed: submitting SQL statements to a remote server through a sub-driver interface corresponding to the type of the queried database, and then returning; checking the results of all executed SQL queries by polling, and saving the returned data; and formatting all the line data in the result set according to the requirements of sqlRowFormat. And when the SQL query is executed and all returned data are received, closing the connection. At this stage, a status code may be generated (an error occurs in processing the request).

As an optional implementation manner of the embodiment of the present invention, as shown in fig. 5, step S103 writes in a socket according to the query result, and obtains a response output of the query result, where the response output includes:

step S301: setting a key value of a response body in the query result according to the query result; specifically, it may be determined whether an error occurs during the validity check and the execution process, and when an error occurs, rows in the response body is set to NULL, and key values such as statusCode and errorMsg are set accordingly, and then the result is output to the socket. When no error occurs, the formatted result set data can be used as the rows key value of the response body, and the key values of status code, error msg and the like are correspondingly set.

Step S302: and calculating the length of the response body according to the query result.

Step S303: and writing the key value of the response body and the length of the response body into the socket to obtain the response output of the query result. The LSQL/RSQL response can be obtained by writing the key value of the response body and the length of the response body into the socket, the format of the LSQL/RSQL response is similar to that of the LSQL/RSQL request, and the LSQL/RSQL response comprises the following parts: status-line, head (headers), blank-line, and response-body (response-body).

For example, for the LSQL request, the corresponding response content is:

the header information includes: date: the time of the response transmission; Content-Type: the header information specifies the content type and character set of the responder, the content type (MIME) is fixed to application/json, and the character set is typically UTF-8; Content-Length: the header information specifies the content length (number of bytes) of the response body, which is 433 in the above example.

It should be noted that the character set encoding of the LSQL response body, considering that the working mechanism of the filter is built on the ASCII control characters, must be ASCII compatible character set or encoding, such as ISO 8859-1, UTF-8, etc.

For the above RSQL request, the response content is almost the same as LSQL, except that the status line is different:

as an optional implementation manner of the embodiment of the present invention, the query result obtained according to the above steps should include the following JSON field information: statusscode: integer values, like HTTP status codes, such as 200 for successful execution and 404 for non-existent target database; errorMsg: the character string or NULL represents the execution error information of the SQL query statement, and no error is represented when the character string or NULL is NULL; rowsAfected: the number of affected recording lines; when an Update (Update), Delete (Delete) or the like operation is performed, the affected number of recording lines will be returned via this field, which value is typically 0 when a Retrieve (Retrieve) operation is performed; rows: for returning a result set; when a search is performed (Retrieve), a result set is returned via this field, NULL is taken if no result set is available, the result set uses arrays to represent all rows, each row being represented by a dictionary data structure (default) or arrays.

For example, a Books table for recording book information is created in an SQLite database file, and the title, author, and ISBN number information of three Books are inserted therein:

CREATE TABLE Books(Id integer PRIMARY KEY，Title text，Author text，Isbntext default＇not available＇)；

INSERT INTO Books VALUES(1，＇War and Peace＇，＇Leo Tolstoy＇，＇978－0345472403＇)；

INSERT INTO Books VALUES(2，＇The Brothers Karamazov＇，＇FyodorDostoyevsky＇，＇978－0486437910＇)；

INSERT INTO Books VALUES(3，＇Crime and Punishment＇，＇FyodorDostoyevsky＇，＇978－1840224306＇)；

the following URL is then used:

lsql：///data/testdb/db？sqlQuery＝SELECT％20＊％20FROM％20Books％3B

the following SQL statements will be executed on the database:

SELECT＊FROM Books；

the JSON data returned by this URL is as follows:

when executing the following SQL statement on the database deletes the first record:

DELETE FROM Books WHERE Id＝1；

the corresponding URL is:

lsql：///data/testdb/db？sqlQuery＝DELETE％20FROM％20Books％20WHERE％20Id％3D1％3B

the result is:

if multiple SQL statements are executed at once, a result array is used to represent the execution results of each statement. For example, two SQL statements are executed at a time (the DELETE statement is executed first and then the SELECT statement is executed), and the URL of the two SQL statements is:

lsql：///data/testdb/db？sqlQuery＝DELETE％20FROM％20Books％20WHERE％20Id％3D1％3B％20SELECT％20＊％20FROM％20Books％3B

the result is:

in order to facilitate the subsequent processing of the result set of the requests such as lsql, besides that the sqlQuery parameter is used for transmitting the SQL query statement, the invention introduces another parameter sqlRowFormat for specifying the line output format of the result set, and can take one of two values of ditt and array, wherein the latter represents the line output format using an array as the result set. When the sqlRowFormat is not specified or a wrong value is specified, a dictionary data structure is adopted. For example, for the above result, if the lsql URL includes an sql rowformat parameter, the returned result is:

as an optional implementation manner of the embodiment of the present invention, when the URL of the schema such as lsql is used as the action attribute of the form submission, the content returned by the schema such as lsql is in the JSON format and cannot be directly provided for the user agent to display. Therefore, a normal result page URL needs to be defined at the same time.

As follows:

＜form action＝＂lsql：///data/testdb/db＂method＝＂get＂destination＝＂/books.html＂＞

＜input type＝＂hidden＂name＝＂sqlQuery＂value＝＂DELETE FROM BooksWHEREId＝￥Id＂/＞

＜input type＝＂hidden＂name＝＂Id＂value＝＂1＂/＞

＜input type＝＂submit＂value＝＂Delete＂/＞

＜/form＞

since the destination URL is defined using the destination attribute in the form element above. When the form submission is successful (i.e., lsql:///data/testdb/db.

As an optional implementation manner of the embodiment of the present invention, when an LSQL/RSQL request includes a plurality of SQL statements, the SQL statements should be executed in sequence, and each result is packed in a result array and returned. If all the SQL statements are executed normally, the corresponding status code is 200, and at this time, the result and/or the result set corresponding to each SQL statement are contained in the result. When an execution error of a certain SQL statement occurs, only the SQL statement that has been normally executed before is returned, and the corresponding status code is 500, at this time, the result includes a result and/or a result set corresponding to the normally executed SQL statement. If all SQL statements can not be executed normally, the corresponding status code is 500, and result is NULL.

As an optional implementation manner of the embodiment of the invention, the newly defined extended URL type LSQL/RSQL of the invention can directly use the two types of URLs (such as forms) in an HTML page, thereby directly operating the database without writing extra server-side code. Since the resources acquired by the drawings such as lsql are always expressed in the JSON format, the result can be directly used by the JavaScript script program to realize a specific function, such as dynamically generating a DOM node. This embodiment illustrates how an lsql, etc. schema can be used without using JavaScript scripts.

For example, the following HTML page fragment defines a table listing the three books in the above example, and a user accessing the page may click on the "Delete" button at the end of each row to directly manipulate the database to Delete the corresponding record:

＜table＞

＜tr＞

＜th＞Title＜/th＞

＜th＞Author＜/th＞

＜th＞ISBN＜/th＞

＜th＞Delete＜/th＞

＜/tr＞

＜tr＞

＜td＞War and Peace＜/td＞

＜td＞Leo Tolstoy＜/td＞

＜td＞978－0345472403＜/td＞

＜td＞

＜formaction＝＂lsql：///data/testdb/db＂method＝＂get＂destination＝＂/books.html＂＞

＜inputtype＝＂hidden＂name＝＂sqlQuery＂value＝＂DELETEFROMBooksWHEREId＝￥Id＂/＞

＜inputtype＝＂hidden＂name＝＂Id＂value＝＂1＂/＞

＜inputtype＝＂submit＂value＝＂Delete＂/＞

＜/form＞

＜/td＞

＜/tr＞

＜tr＞

＜td＞TheBrothersKaramazov＜/td＞

＜td＞FyodorDostoyevsky＜/td＞

＜td＞978－0486437910＜/td＞

＜td＞

＜inputtype＝＂hidden＂name＝＂Id＂value＝＂2＂/＞

＜inputtype＝＂submit＂value＝＂Delete＂/＞

＜/form＞

＜/td＞

＜/tr＞

＜tr＞

＜td＞CrimeandPunishment＜/td＞

＜td＞FyodorDostoyevsky＜/td＞

＜td＞978－1840224306＜/td＞

＜td＞

＜inputtype＝＂hidden＂name＝＂Id＂value＝＂3＂/＞

＜inputtype＝＂submit＂value＝＂Delete＂/＞

＜/form＞

＜/td＞

＜/tr＞

＜/table＞

an embodiment of the present invention further provides a device for directly querying structured data, as shown in fig. 6, the device includes:

a request validity checking module 1, configured to perform validity checking according to a data query request sent by a user agent, and generate an effective data query statement; for details, refer to the related description of step S101 in the above method embodiment.

The execution module 2 is used for executing according to the effective data query statement to obtain a query result; for details, refer to the related description of step S102 in the above method embodiment.

And the response output module 3 is used for writing the socket according to the query result to obtain the response output of the query result. For details, refer to the related description of step S103 in the above method embodiment.

In an open-source Web engine such as WebKit, requests including URLs such as HTTP and HTTPs are all handled by independently running sub-processes (referred to as "URL request handling processes"), and therefore, the apparatus for directly querying structured data provided by the embodiment of the present invention may also follow this architecture.

As an alternative implementation manner of the embodiment of the present invention, the specific implementation of the introduced URL schema such as lsql may have various manners.

Specifically, as shown in fig. 7, the processing of the lsqlURL may be implemented in a locally running server process, called "LSQL server", to which a URL request processing process of the user agent connects through a socket, submits request parameters through the socket, and then waits for the server to return a corresponding processing result. This approach may also provide for the implementation of lsql requests for programs other than user agents, as may HTTP requests initiated in generic C/C + + programs or even Python script programs.

Alternatively, as shown in fig. 8, the lsqll URL request may be directly integrated into the URL request processing process of the user agent, a new sub-process is started for each lsql request, the database file is opened in the sub-process, the SQL statement is executed, and the execution result is obtained and then returned.

As an optional implementation manner of the embodiment of the present invention, the introduced URL schema such as rsql may also be implemented in various ways.

As shown in fig. 9, for the RSQL request, because the request essentially needs to be connected to the remote host to perform the corresponding operation, an implementation similar to the HTTP server may be selected to implement the support of the RSQL protocol, that is, to execute an RSQL server on the remote host, then connect to the database by the RSQL server and execute the corresponding SQL query, and finally return the result and the result set. However, this approach is not fundamentally different from an HTTP server that can process the corresponding URL.

Specifically, a method for accessing a remote database using an HTTP connection belongs to an indirect access method, and an HTTP server accesses the remote database to obtain data and then generates an HTML page or JSON data to return to a user agent. In this case, the script program at the HTTP server end is responsible for connecting to the database and submitting the SQL statements to be executed to the database, and then obtaining the query result of the database.

Alternatively, as shown in fig. 10, when accessing using RSQL connections, the RSQL server program may run on the local host, and the RSQL server running on the local host performs SQL queries using the client SDK provided by the database to directly connect to the remote database.

The functional description of the apparatus for directly querying structured data provided in the embodiments of the present invention refers to the description of the method for directly querying structured data in the above embodiments.

An embodiment of the present invention further provides a terminal for directly querying structured data, as shown in fig. 11, the terminal for directly querying structured data may include a processor 51 and a memory 52, where the processor 51 and the memory 52 may be connected by a bus or in another manner, and fig. 11 takes the example of connection by a bus as an example.

The processor 51 may be a Central Processing Unit (CPU). The Processor 51 may also be other general purpose processors, Digital Signal Processors (DSPs), Application Specific Integrated Circuits (ASICs), Field Programmable Gate Arrays (FPGAs) or other Programmable logic devices, discrete Gate or transistor logic devices, discrete hardware components, or combinations thereof.

The memory 52, which is a non-transitory computer readable storage medium, may be used to store non-transitory software programs, non-transitory computer executable programs, and modules, such as the corresponding program instructions/modules in the embodiments of the present invention. The processor 51 executes various functional applications and data processing of the processor, namely, a method for directly querying structured data in the above method embodiments, by executing non-transitory software programs, instructions and modules stored in the memory 52.

The memory 52 may include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program required for at least one function; the storage data area may store data created by the processor 51, and the like. Further, the memory 52 may include high speed random access memory, and may also include non-transitory memory, such as at least one magnetic disk storage device, flash memory device, or other non-transitory solid state storage device. In some embodiments, the memory 52 may optionally include memory located remotely from the processor 51, and these remote memories may be connected to the processor 51 via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The one or more modules are stored in the memory 52 and, when executed by the processor 51, perform a method of directly querying structured data as in the embodiments of fig. 1-5.

The specific details of the terminal for directly querying the structured data may be understood by referring to the corresponding related descriptions and effects in the embodiments shown in fig. 1 to fig. 5, and are not described herein again.

It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by a computer program, which can be stored in a computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. The storage medium may be a magnetic Disk, an optical Disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a Flash Memory (Flash Memory), a Hard Disk (Hard Disk Drive, abbreviated as HDD) or a Solid State Drive (SSD), etc.; the storage medium may also comprise a combination of memories of the kind described above.

Although the embodiments of the present invention have been described in conjunction with the accompanying drawings, those skilled in the art may make various modifications and variations without departing from the spirit and scope of the invention, and such modifications and variations fall within the scope defined by the appended claims.

Claims

1. A method for directly querying structured data, comprising the steps of:

carrying out validity check according to a data query request sent by a user agent to generate an effective data query statement;

executing according to the effective data query statement to obtain a query result;

and writing the query result into a socket to obtain the response output of the query result.

2. The method of claim 1, wherein executing according to the valid data query statement to obtain a query result further comprises:

judging whether the data query is a local query or a remote query according to the effective data query statement;

when the data query is a local query, judging whether the queried database executes a data query request or not according to the effective data query statement;

when the data query request is being executed in the queried database, the valid query statement is saved.

3. The method of directly querying structured data according to claim 2, further comprising:

when the data query is a remote query, connecting a remote management module according to the effective data query statement;

and controlling the number of the inquired servers to be smaller than a preset threshold value according to the remote management module.

4. The method of claim 1, wherein the generating valid data query statements according to the validity check of the data query request sent by the user agent comprises:

checking whether the data query request exceeds a set value according to the data query request sent by the user agent;

when the data query request does not exceed a set value, separating request parameters in the data query request;

and generating an effective data query statement according to the separated request parameters.

5. The method of claim 2, wherein the query result obtained by executing the query statement according to the valid data is obtained by:

when the queried database does not execute the data query request, determining the user identity according to the user identifier in the valid data query statement;

opening a queried database according to the user identity;

and executing the effective data query statement according to the queried database to obtain a query result.

6. The method of claim 3, wherein the query result obtained by executing the query statement according to the valid data is obtained by:

when the data query is a remote query, determining a remote server where the queried database is located and a corresponding program interface according to the effective data query statement;

sending the valid data query statement to the remote server according to the program interface;

and receiving a query result corresponding to the effective data query statement according to the program interface.

7. The method of claim 1, wherein writing a socket according to the query result to obtain a response output of the query result comprises:

setting a key value of a response body in a query result according to the query result;

calculating the length of a response body according to the query result;

and writing the response body key value and the response body length into a socket to obtain the response output of the query result.

8. An apparatus for directly querying structured data, comprising:

the request validity checking module is used for checking the validity according to the data query request sent by the user agent to generate an effective data query statement;

the execution module is used for executing according to the effective data query statement to obtain a query result;

and the response output module is used for writing the socket according to the query result to obtain the response output of the query result.

9. A computer-readable storage medium storing computer instructions for causing a computer to perform the method of directly querying structured data according to any one of claims 1 to 7.

10. A terminal for direct query of structured data, comprising: a memory and a processor, the memory and the processor being communicatively coupled to each other, the memory storing computer instructions, the processor executing the computer instructions to perform the method of directly querying structured data according to any one of claims 1 to 7.