CN107832387B

CN107832387B - SQL statement analysis method based on FMDB

Info

Publication number: CN107832387B
Application number: CN201711042272.8A
Authority: CN
Inventors: 郭艳杰
Original assignee: Beijing Kuwo Technology Co Ltd
Current assignee: Beijing Kuwo Technology Co Ltd
Priority date: 2017-10-31
Filing date: 2017-10-31
Publication date: 2021-07-13
Anticipated expiration: 2037-10-31
Also published as: CN107832387A

Abstract

The invention relates to an SQL statement analyzing method based on FMDB, which comprises the following steps: creating a UserDBmanager class on an FM interface layer for managing a user database, wherein the UserDBmanager class generates an object and calls a method: AllModel (), the method internally calls the FMDB encapsulation function: and the table function is used for appointing table related information, the table refers to a table in a user database, the operation function is used for appointing operation related information to be executed, the additional function is used for appointing additional condition information, after parameters corresponding to the packaging function are received and analyzed, the UserDBmanager class packages the analysis result through a commit instruction, and then calls SQL to execute corresponding operation. The invention is based on FMDB, has clear structure and easy realization, simplifies the code writing amount of SQL sentences, is beneficial to improving the development efficiency and reduces errors caused by repeated labor in programming.

Description

SQL statement analysis method based on FMDB

Technical Field

The invention relates to the technical field of SQLite API packaging, in particular to an SQL statement analyzing method based on FMDB.

Background

The iOS SDK supports SQLite for a long time, and only the libsqlite3.dylib dependence needs to be added and the SQLite3.h header file needs to be introduced when the sQlite SDK is used. However, the native SQLite API is not very friendly to use and is very inconvenient to use.

In addition, when the SQLite database is used, it is inevitable that a stack of complex sql statements needs to be written. As follows (by way of example only):

// create Table

（1）CREATE TABLE IF NOT EXISTS BSUser （id integer（11）PRIMARY KEY，arr text DEFAULT NULL，data text DEFAULT NULL，stu_id long DEFAULT NULL，name text DEFAULT NULL，image text DEFAULT NULL）；

（2）CREATE TABLE IF NOT EXISTS BSTeacher （id integer（11）PRIMARY KEY，name text DEFAULT NULL，depart text DEFAULT NULL）；

（3）CREATE TABLE IF NOT EXISTS BSStudent （id integer（11）PRIMARY KEY，tea_id long DEFAULT NULL，name text DEFAULT NULL）；

// fuzzy query:

(1) select from YHWorkGroup where id = 2013 and msgContent like%'

// update Table:

(1) update YHUserInfo set isRegister = 1, isSelfModel = 1, isoffical = 1, isfullowed = 1, sex = 1, fromType = 1, dynamicCount = 1, nnewfenscount = 2, fanscont =0, folllowcount = 28, like count = 4, identity = 1, fripstatus = 1, addFriStatus = 2, photont =0, updatatus =0, id = '201311110', taxcount = 'accessoken =0', mobilephone = '13570871315', userName = 'userName0', avararu 'http:/=' =/www.google.com ', avarit =' www.baidu.com ', city' userink '=' business '=', short-range mobile phone '1479110498.98624', short-range mobile phone 'operation =', short-range work = '3', short-range mobile phone = '3', short-range work = '3', short-range 'work =' 3 ', short-range =' work = ', short-range =' work = '3', short-mobile phone = ', short-range =' work = ', short-mobile phone =', short-range 'work =', short-person 'work =', short-mobile phone, email = 'qqqq0. eamil.com', visitTime = '1479110598.98624', jobTags = '(' U968f \ U673a \ U6807\ U7b e ')', workExperience = '[ { "moreDescription": MarxideIlfIfInjia { "time": usage "{" 20134"," composition ": company 18', 'workExplopid": 10', 'position ": 29', 'beginnai": 20130' { "text" { "7" }, { "26" { "text" { "7" { "text" { "25" { "text" } "," { "text" { "30" { "text" { "26" { "text" } "," { "text" { "30" { "text" { "26" } "," { "text" { "30" { "text" } "," { "text" } "," { "text" { "30" { "text" { "30" } "," { "text" {, "major," endian "for understanding the aspects of" accounting, "" beginntime ":"20130, "" editorial background ": for understanding the aspects of" major, "" endTime ":"20134, "" modedescription ": for understanding the aspects of" eduExpId ": 260" }, { "school": school 26, "" major ": random specialty," "beginnTime": 20130, "" editorial background ": for understanding the aspects of" major, "" enderTime ": 20134," "modeDedescription": for understanding the aspects of "eduExpId": 380, "" school ": school 38," "major": accounting, "" beginn ": 20130," "editorial background": 201311110 ": this section": 20134, "" modernization ": 3582";

in the iOS development, if the model attribute is added manually, the code is redundant, i.e. the amount of code is increased, readability is poor, and if the name of some attribute of the model is changed, the corresponding position of the SQL statement is changed. The addition, deletion, modification and check are all changed. If a batch of attribute names are changed, the workload is simply catastrophic, and errors are easily caused, so that the development work is not facilitated.

Disclosure of Invention

Aiming at the defects in the prior art, the invention aims to provide the SQL statement analyzing method based on the FMDB, which is clear in structure and easy to realize based on the FMDB, simplifies the code compiling amount of the SQL statement, is beneficial to improving the development efficiency and reduces errors caused by repeated labor in programming.

In order to achieve the above purposes, the technical scheme adopted by the invention is as follows:

an SQL statement parsing method based on FMDB is characterized by comprising the following steps:

at the FM interface layer, a UserDBmanager class is created for managing the user database,

the UserDBmanager class generates an object and invokes a method: AllModel (), the method internally calls the FMDB encapsulation function:

a table function for specifying table related information, the table referring to a table in a user database,

an operation function for specifying operation-related information to be executed,

an additional function for specifying additional condition information,

and after the parameters corresponding to the packaging functions are received and analyzed, the UserDBmanager class packages the analysis result through the commit instruction, and then calls the SQL to execute corresponding operation.

On the basis of the above technical solution, the additional condition information is related to an operation to be executed, including but not limited to: WHERE statement, ORDERBY statement, LIMIT quantity LIMIT statement.

On the basis of the above technical solution, the table related information includes but is not limited to: table name.

On the basis of the above technical solution, the operation related information includes: the operation key words and the operation objects,

the operation keywords include: select keywords, create keywords, insert keywords, update keywords, add keywords, remove keywords, execute keywords,

the operation object comprises: table object table, row object row, array object array.

On the basis of the technical scheme, after the parameters are transmitted through the FMDB encapsulation function, the parameters are analyzed as follows:

analyzing the table function into a table assignment statement;

parsing the operation function into a column selection statement;

parsing the additional function into additional conditional statements;

after the analysis is finished, the UserDBmanager class calls a related algorithm to assemble the analysis result into a standard SQL statement.

On the basis of the technical scheme, the assembled standard SQL statement is returned to the UserDBAnager class.

On the basis of the technical scheme, the user DBmanager takes the received assembled standard SQL sentences as character strings, and calls SQL to execute corresponding operations after the character strings are packaged by commit instructions.

On the basis of the technical scheme, the character string is packaged through the commit instruction and then used as the value of the params parameter to call the SQL to execute corresponding operation.

The SQL sentence analyzing method based on the FMDB is clear in structure and easy to realize, based on the FMDB, simplifies the code writing amount of the SQL sentence, is beneficial to improving the development efficiency, and reduces errors caused by repeated labor in programming.

Drawings

The invention has the following drawings:

FIG. 1 is a flow chart of the present invention.

Detailed Description

The present invention will be described in further detail with reference to the accompanying drawings.

As shown in fig. 1, the SQL statement parsing method based on FMDB of the present invention includes the following steps:

an additional function for specifying additional condition information,

On the basis of the technical scheme, as a preferred scheme, when the user database is managed, each table in the database is respectively created with a management class with different class names.

In the preferred scheme, the User database manager class only manages the User table in the database, and the other tables correspondingly replace the prefix part of the class name (the character before the DB is the prefix) according to the table name.

Another optional preferred scheme is that the userbmanager class, as a large class, abstracts the shared content of the management class, and then generates a plurality of subclasses based on the userbmanager class, wherein each subclass manages a certain table in the database one-to-one.

analyzing the table function into a table assignment statement; for example: table = statement;

parsing the operation function into a column selection statement; for example: defining column names in the required table;

parsing the additional function into additional conditional statements; for example: [ WHERE < CONDITIONAL EXPRESSION > ], [ GROUP BY < GROUP > ], [ HAVING < CONDITIONAL EXPRESSION > ], [ ORDER BY < ORDER > [ ASC | DESC ] ], and the like;

after parsing is completed, the UserDBmanager class calls the correlation algorithm to assemble the parsed result into a standard SQL statement, such as select (uid, diameter, …) from user ORDER BY rowed DESC. The assembly of the parsed results into standard SQL statements can be assembled by sqlType. Wherein:

the aforementioned operation keywords "select keyword, create keyword, insert keyword, update keyword, add keyword, remove keyword" will be analyzed and assembled as follows: a select one statement, a select array statement, an insert statement, an update statement, a create statement, a delete statement, or an addclose statement.

On the basis of the technical scheme, when SQL is called to execute corresponding operation, firstly, a standard SQL statement is stored in a DBqueue of an FM execution layer,

in the FM execution layer, an operation is performed to query the database by the executeQuery () method, resulting in a query result resultSet,

and finally, returning the query result resultSet to the FM interface layer through the KWSqlResult parameter.

On the basis of the technical scheme, the query result resultSet is uniformly formatted so as to facilitate subsequent analysis.

The query result resultSet includes at least the following data:

success flag bit success, used to flag the result of SQL execution,

multiple data bits rows for storing query results comprising multiple data,

a single data bit id for storing a query result including one data,

and counting the bits num, and storing the query result comprising the statistical data.

Those not described in detail in this specification are within the skill of the art.

Claims

1. An SQL statement parsing method based on FMDB is characterized by comprising the following steps:

when a user database is managed, each table in the database is respectively established with a management class, and the class names are different;

or: when a user database is managed, the UserDBmanager class is used as a large class to abstract the common content of the management class, and then a plurality of subclasses are generated based on the UserDBmanager class, wherein each subclass is one-to-one in one of the management databases;

an additional function for specifying additional condition information,

after the parameters are input through the FMDB encapsulation function, the parameters are analyzed as follows:

analyzing the table function into a table assignment statement;

parsing the operation function into a column selection statement;

parsing the additional function into additional conditional statements;

after the parameters corresponding to the encapsulation functions are received and analyzed, the UserDBmanager class encapsulates the analysis result through a commit instruction, and then calls SQL to execute corresponding operations, wherein:

and the UserDBAnager class calls a correlation algorithm to assemble the analysis result into a standard SQL statement, the assembled standard SQL statement is returned to the UserDBAnager class, and the UserDBAnager class packages the received assembled standard SQL statement as a character string through a commit instruction.

2. An FMDB-based SQL statement parsing method according to claim 1, wherein: the additional condition information is related to the operation to be performed, including but not limited to: WHERE statement, ORDERBY statement, LIMIT quantity LIMIT statement.

3. An FMDB-based SQL statement parsing method according to claim 1, wherein: the table related information includes but is not limited to: table name.

4. An FMDB-based SQL statement parsing method according to claim 1, wherein: the operation-related information includes: the operation key words and the operation objects,

5. An FMDB-based SQL statement parsing method according to claim 1, wherein: and the character string is packaged by the commit instruction and then used as the value of params parameter to call SQL to execute corresponding operation, wherein:

when SQL is called to execute corresponding operation, firstly, the standard SQL statement is stored in the DBQueue queue of the FM execution layer,

finally, returning the query result resultSet to the FM interface layer through the KWSqlResult parameter;

the query result resultSet is uniformly formatted so as to facilitate subsequent analysis, and the query result resultSet at least comprises the following data:

success flag bit success, used to flag the result of SQL execution,

multiple data bits rows for storing query results comprising multiple data,

a single data bit id for storing a query result including one data,