CN107832387A - A kind of SQL statement analytic method based on FMDB - Google Patents
A kind of SQL statement analytic method based on FMDB Download PDFInfo
- Publication number
- CN107832387A CN107832387A CN201711042272.8A CN201711042272A CN107832387A CN 107832387 A CN107832387 A CN 107832387A CN 201711042272 A CN201711042272 A CN 201711042272A CN 107832387 A CN107832387 A CN 107832387A
- Authority
- CN
- China
- Prior art keywords
- fmdb
- sql statement
- function
- userdbmanager
- classes
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24539—Query rewriting; Transformation using cached or materialised query results
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
Abstract
The present invention relates to a kind of SQL statement analytic method based on FMDB, comprise the following steps：In FM interface layers, a UserDBManager classes are created, for being managed to customer data base, the UserDBManager classes generate an object and call method：AllModel（）, this method intrinsic call FMDB encapsulation functions：Table function, for specifying table relevant informations, the table refers to the table in customer data base, handling function, for specifying on-unit relevant information, additional function, for specifying additional condition information, after parameter corresponding to encapsulating function more than receives and parses through, UserDBManager classes are instructed by commit and encapsulate analysis result, then call SQL to perform corresponding operating.The present invention, it is clear in structure based on FMDB, it is easy to accomplish, simplify SQL statement writes size of code, is advantageous to improve development efficiency, reduces mistake caused by the duplication of labour in programming.
Description
Technical field
The present invention relates to SQLite API encapsulation technologies field, specifically a kind of SQL statement parsing side based on FMDB
Method.
Background technology
IOS SDK support SQLite very early, when in use, it is only necessary to add libsqlite3.dylib rely on and
Introduce sqlite3.h header files.But primary SQLite API are upper quite unfriendly in use, when in use, very
Inconvenience.
In addition, when using SQLite databases, just inevitably need to write the complicated sql sentences of a pile.It is as follows（Only
For example）：
// 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）My %' of and msgContent like ' the % of select * from YHWorkGroup where id=2013
// renewal table:
（1）Update YHUserInfo set isRegister=1, isSelfModel=1, isOfficial=1,
IsFollowed=1, sex=1, fromType=1, dynamicCount=1, nNewFansCount=2,
FansCount=0, followCount=28, likeCount=4, identity=1, friShipStatus=1,
AddFriStatus=2, photoCount=0, updateStatus=0, id=' 201311110', taxAccount
=' accessToken=0', mobilephone=' 13570871315', userName=' userName0',
avatarUrl = 'http://www.google.com', oriAvaterUrl=' http://www.baidu.com',
Intro=' personal brief introduction good short ', industry=' random industry ', job=' senior position ', province=' wide
Dong Sheng ', workCity=' random work city ', workLocation=' Guangzhou movement ', loginTime='
1479110498.98624' company=' saving in Guangzhou Mobile ', email=' qq0.eamil.com', visitTime
=' 1479110598.98624', jobTags='（" U968f U673a U6807 U7b7e ", " U968f U673a
U6807\U7b7e"）', workExperiences=' [{ " moreDescription ":" the good farad of Mary's black pigment used by women in ancient times to paint their eyebrows contains debit ", "
endTime":" 20134 ", " company ":" company 18 ", " workExpId ":" 10 ", " position ":" position 29 ", "
beginTime":" 20130 " }, { " moreDescription ":" the good farad of Mary's black pigment used by women in ancient times to paint their eyebrows contains debit ", " endTime ":"
20134 ", " company ":" company 33 ", " workExpId ":" 14 ", " position ":" position 22 ", " beginTime ":"
20130 " }, { " moreDescription ":" the good farad of Mary's black pigment used by women in ancient times to paint their eyebrows contains debit ", " endTime ":" 20134 ", " company ":"
Company 25 ", " workExpId ":" 27 ", " position ":" position 21 ", " beginTime ":" 20130 " }] ',
eductaionExperiences = '[{"school":" school 36 ", " major ":" accounting ", " beginTime ":"
20130 ", " educationBackground ":" junior college ", " endTime ":" 20134 ", " moreDescription ":" reason
Solution has been liked just ", " eduExpId ":" 260 " }, { " school ":" school 26 ", " major ":" random specialty ", "
beginTime":" 20130 ", " educationBackground ":" junior college ", " endTime ":" 20134 ", "
moreDescription":" understanding liked just ", " eduExpId ":" 380 " }, { " school ":" school 38 ", "
major":" accounting ", " beginTime ":" 20130 ", " educationBackground ":" undergraduate course ", " endTime ":"
20134 ", " moreDescription ":" understanding liked just ", " eduExpId ":"82"}]' where id =
201311110 ；
In iOS exploitations, in order to obtain above SQL statement, if manually adding model attributes, code with regard to redundancy,
Exactly increase size of code, it is readable poor, and if changing the title of some attribute of model, the corresponding position of SQL statement
Change.Increase, delete, change, look into, will change.If changing a collection of attribute-name, this workload is just calamitous
, easily cause the appearance of mistake, be unfavorable for the progress of development.
The content of the invention
For defect present in prior art, it is an object of the invention to provide a kind of SQL statement solution based on FMDB
Analysis method, it is clear in structure based on FMDB, it is easy to accomplish, simplify SQL statement writes size of code, is advantageous to improve exploitation effect
Rate, reduce mistake caused by the duplication of labour in programming.
To achieve the above objectives, the present invention adopts the technical scheme that：
A kind of SQL statement analytic method based on FMDB, it is characterised in that comprise the following steps：
In FM interface layers, a UserDBManager classes are created, for being managed to customer data base,
The UserDBManager classes generate an object and call method：AllModel（）, this method intrinsic call FMDB envelopes
Fill function：
Table function, for specifying table relevant informations, the table refers to the table in customer data base,
Handling function, for specifying on-unit relevant information,
Additional function, for specifying additional condition information,
After parameter corresponding to encapsulating function more than receives and parses through, UserDBManager classes are instructed by commit will
Analysis result encapsulates, and then calls SQL to perform corresponding operating.
On the basis of above-mentioned technical proposal, the additional condition information is related to on-unit, including but unlimited
In：WHERE sentences, ORDERBY sentences, LIMIT quantity limitation sentence.
On the basis of above-mentioned technical proposal, the table relevant informations, include but is not limited to：Table name.
On the basis of above-mentioned technical proposal, the operation relevant information includes：Keyword and operation object are operated,
The operation keyword includes：Select keywords, create keywords, insert keywords, update keywords,
Add keywords, remove keywords, execute keywords,
The operation object includes：Table object table, row object row, array object array.
On the basis of above-mentioned technical proposal, after FMDB encapsulation functions are passed to parameter, parameter is parsed as follows：
Table function is resolved into table assignment statements；
Handling function is resolved into column selection sentence；
Additional function is resolved into additional conditional statements；
After parsing, analysis result is assembled into the SQL statement of standard by UserDBManager classes calling related algorithm.
On the basis of above-mentioned technical proposal, the SQL statement of the standard finished is assembled, returns to UserDBManager classes.
On the basis of above-mentioned technical proposal, the SQL languages for the standard that UserDBManager classes finish the assembling received
Sentence, as character string by calling SQL to perform corresponding operating after commit instruction encapsulation.
On the basis of above-mentioned technical proposal, the character string after commit instruction encapsulation by being used as params parameters
Value calls SQL to perform corresponding operating.
SQL statement analytic method of the present invention based on FMDB, it is clear in structure based on FMDB, it is easy to accomplish, simplify
SQL statement writes size of code, is advantageous to improve development efficiency, reduces mistake caused by the duplication of labour in programming.
Brief description of the drawings
The present invention has drawings described below：
The flow chart of Fig. 1 present invention.
Embodiment
The present invention is described in further detail below in conjunction with accompanying drawing.
As shown in figure 1, the SQL statement analytic method of the present invention based on FMDB, comprises the following steps：
In FM interface layers, a UserDBManager classes are created, for being managed to customer data base,
The UserDBManager classes generate an object and call method：AllModel（）, this method intrinsic call FMDB envelopes
Fill function：
Table function, for specifying table relevant informations, the table refers to the table in customer data base,
Handling function, for specifying on-unit relevant information,
Additional function, for specifying additional condition information,
After parameter corresponding to encapsulating function more than receives and parses through, UserDBManager classes are instructed by commit will
Analysis result encapsulates, and then calls SQL to perform corresponding operating.
It is preferably, every in database when being managed to customer data base on the basis of above-mentioned technical proposal
A management class is respectively created in individual table, and class name is different.
In a preferred approach, UserDBManager classes, will be managed just for the User tables in database, remaining table
The prefix part of class name is accordingly replaced according to table name（Character before DB is prefix）.
Another optional preferred scheme is UserDBManager classes, as a major class, is abstracted the shared of management class
Content, UserDBManager classes are then based on, generate some subclasses, each subclass is man-to-man to manage some in database
Table.
On the basis of above-mentioned technical proposal, the additional condition information is related to on-unit, including but unlimited
In：WHERE sentences, ORDERBY sentences, LIMIT quantity limitation sentence.
On the basis of above-mentioned technical proposal, the table relevant informations, include but is not limited to：Table name.
On the basis of above-mentioned technical proposal, the operation relevant information includes：Keyword and operation object are operated,
The operation keyword includes：Select keywords, create keywords, insert keywords, update keywords,
Add keywords, remove keywords, execute keywords,
The operation object includes：Table object table, row object row, array object array.
On the basis of above-mentioned technical proposal, after FMDB encapsulation functions are passed to parameter, parameter is parsed as follows：
Table function is resolved into table assignment statements；Such as：Table=sentence；
Handling function is resolved into column selection sentence；Such as：The row name in table needed for limiting；
Additional function is resolved into additional conditional statements；Such as：[WHERE <Conditional expression>], [GROUP BY<Packet according to
According to row>], [HAVING<Conditional expression>], [ORDER BY<Sort by arranges>[ASC | DESC]] etc.；
After parsing, analysis result is assembled into the SQL statement of standard by UserDBManager classes calling related algorithm, such as
select（Uid, uname ...）From user ORDER BY rowid DESC etc..Analysis result is assembled into the SQL of standard
Sentence can be assembled by sqlType.Wherein：
" select keywords, create keywords, insert keywords, update keywords, add are closed aforementioned operation keyword
Keyword, remove keywords " will be resolved and be assembled into：Selectone sentences, selectarray sentences, insert languages
Sentence, update sentences, creat sentences, delete sentences or addcloum sentences.
On the basis of above-mentioned technical proposal, the SQL statement of the standard finished is assembled, returns to UserDBManager classes.
On the basis of above-mentioned technical proposal, the SQL languages for the standard that UserDBManager classes finish the assembling received
Sentence, as character string by calling SQL to perform corresponding operating after commit instruction encapsulation.
On the basis of above-mentioned technical proposal, the character string after commit instruction encapsulation by being used as params parameters
Value calls SQL to perform corresponding operating.
On the basis of above-mentioned technical proposal, when calling SQL execution corresponding operatings, the SQL statement of standard is stored first
To the DBQueue queues of FM execution levels,
In FM execution levels, pass through executeQuery（）Method is operated to inquire about database to perform, and obtains Query Result
ResultSet,
Finally, Query Result resultSet is returned into FM interface layers by KWSqlResult parameters.
On the basis of above-mentioned technical proposal, Query Result resultSet carries out unified formatting, in order to subsequently solve
Analysis.
Data below is comprised at least in Query Result resultSet：
Success Flag position success, the result performed for indicating SQL,
More data bit rows, the Query Result of multiple data is included for storing,
Forms data position id, the Query Result of a data is included for storing,
Meter digital num, the Query Result of statistics is included for storing.
The content not being described in detail in this specification belongs to prior art known to professional and technical personnel in the field.
Claims (8)
1. a kind of SQL statement analytic method based on FMDB, it is characterised in that comprise the following steps：
In FM interface layers, a UserDBManager classes are created, for being managed to customer data base,
The UserDBManager classes generate an object and call method：AllModel（）, this method intrinsic call FMDB envelopes
Fill function：
Table function, for specifying table relevant informations, the table refers to the table in customer data base,
Handling function, for specifying on-unit relevant information,
Additional function, for specifying additional condition information,
After parameter corresponding to encapsulating function more than receives and parses through, UserDBManager classes are instructed by commit will
Analysis result encapsulates, and then calls SQL to perform corresponding operating.
2. the SQL statement analytic method based on FMDB as claimed in claim 1, it is characterised in that：The additional condition information
It is related to on-unit, include but is not limited to：WHERE sentences, ORDERBY sentences, LIMIT quantity limitation sentence.
3. the SQL statement analytic method based on FMDB as claimed in claim 1, it is characterised in that：The related letters of the table
Breath, include but is not limited to：Table name.
4. the SQL statement analytic method based on FMDB as claimed in claim 1, it is characterised in that：The operation relevant information
Including：Keyword and operation object are operated,
The operation keyword includes：Select keywords, create keywords, insert keywords, update keywords,
Add keywords, remove keywords, execute keywords,
The operation object includes：Table object table, row object row, array object array.
5. the SQL statement analytic method based on FMDB as claimed in claim 1, it is characterised in that：Function is encapsulated by FMDB
After incoming parameter, parameter is parsed as follows：
Table function is resolved into table assignment statements；
Handling function is resolved into column selection sentence；
Additional function is resolved into additional conditional statements；
After parsing, analysis result is assembled into the SQL statement of standard by UserDBManager classes calling related algorithm.
6. the SQL statement analytic method based on FMDB as claimed in claim 5, it is characterised in that：The standard that assembling finishes
SQL statement, return to UserDBManager classes.
7. the SQL statement analytic method based on FMDB as claimed in claim 6, it is characterised in that：UserDBManager classes will
The SQL statement for the standard that the assembling received finishes, it is corresponding by calling SQL to perform after commit instruction encapsulation as character string
Operation.
8. the SQL statement analytic method based on FMDB as claimed in claim 7, it is characterised in that：The character string passes through
Value after commit instruction encapsulation as params parameters calls SQL to perform corresponding operating.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711042272.8A CN107832387B (en) | 2017-10-31 | 2017-10-31 | SQL statement analysis method based on FMDB |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201711042272.8A CN107832387B (en) | 2017-10-31 | 2017-10-31 | SQL statement analysis method based on FMDB |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN107832387A true CN107832387A (en) | 2018-03-23 |
| CN107832387B CN107832387B (en) | 2021-07-13 |
Family
ID=61650200
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201711042272.8A Active CN107832387B (en) | 2017-10-31 | 2017-10-31 | SQL statement analysis method based on FMDB |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107832387B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109726217A (en) * | 2019-01-10 | 2019-05-07 | 北京字节跳动网络技术有限公司 | A kind of database operation method, device, equipment and storage medium |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20020133504A1 (en) * | 2000-10-27 | 2002-09-19 | Harry Vlahos | Integrating heterogeneous data and tools |
| CN101105814A (en) * | 2007-09-11 | 2008-01-16 | 金蝶软件（中国）有限公司 | Method and device for converting Script language to SQL language |
| CN102253984A (en) * | 2011-06-28 | 2011-11-23 | 用友软件股份有限公司 | Query sentence processing device and query sentence processing method |
| CN103136263A (en) * | 2011-11-23 | 2013-06-05 | 英业达股份有限公司 | Method for automatic generation of structured query language (SQL) sentences |
| CN105630508A (en) * | 2015-12-31 | 2016-06-01 | 广州亦云信息技术有限公司 | Processing method and processing system for encapsulating SQL (Structured Query Language) in Java system |
-
2017
- 2017-10-31 CN CN201711042272.8A patent/CN107832387B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20020133504A1 (en) * | 2000-10-27 | 2002-09-19 | Harry Vlahos | Integrating heterogeneous data and tools |
| CN101105814A (en) * | 2007-09-11 | 2008-01-16 | 金蝶软件（中国）有限公司 | Method and device for converting Script language to SQL language |
| CN102253984A (en) * | 2011-06-28 | 2011-11-23 | 用友软件股份有限公司 | Query sentence processing device and query sentence processing method |
| CN103136263A (en) * | 2011-11-23 | 2013-06-05 | 英业达股份有限公司 | Method for automatic generation of structured query language (SQL) sentences |
| CN105630508A (en) * | 2015-12-31 | 2016-06-01 | 广州亦云信息技术有限公司 | Processing method and processing system for encapsulating SQL (Structured Query Language) in Java system |
Non-Patent Citations (1)
| Title |
|---|
| GABRIEL THEODOROPOULOS: "在 iOS 的 SQLite 数据库中应用 FMDB 库", 《HTTPS://WWW.JIANSHU.COM/P/3885AB699326》 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109726217A (en) * | 2019-01-10 | 2019-05-07 | 北京字节跳动网络技术有限公司 | A kind of database operation method, device, equipment and storage medium |
| CN109726217B (en) * | 2019-01-10 | 2021-06-18 | 北京字节跳动网络技术有限公司 | Database operation method, device, equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107832387B (en) | 2021-07-13 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108038222B (en) | System of entity-attribute framework for information system modeling and data access | |
| CN103440273B (en) | A kind of data cross-platform migration method and device | |
| CN110929042B (en) | Knowledge graph construction and query method based on power enterprise | |
| CN103500196B (en) | EXCEL data export method and let-off gear(stand) thereof under many concurrent big data quantity environment | |
| US7630956B2 (en) | System and method for analyzing and reporting extensible data from multiple sources in multiple formats | |
| CN105260403B (en) | General integration across database access method | |
| CN103064875B (en) | A kind of spatial service data distributed enquiring method | |
| US7310637B2 (en) | Dynamic database access via standard query language and abstraction technology | |
| CN106407302B (en) | It supports to call the special functional method of middleware database by Simple SQL | |
| US20110087708A1 (en) | Business object based operational reporting and analysis | |
| WO2023151543A1 (en) | Query method and system based on structured query language (sql) | |
| CN104021198B (en) | The relational database information search method and device indexed based on Ontology | |
| CN107391652B (en) | Template report interface definition system and method | |
| CN103020158A (en) | Report form creation method, device and system | |
| JP2006526840A (en) | Computer System and Method for Multidimensional Database Query and Visualization The present invention relates to the Ministry of Energy ASCI Level 1 Partnership LLL-B523835 with Stanford University ("Center for Integrated Turbulence Simulation"). As well as by the Defense Advanced Research Projects Agency (“Visualization of Complex Systems and Environments”), APRA order number E269. The US government may have rights in the invention. | |
| CN102789487B (en) | Data query retrieval process device and data query search processing method | |
| US20170193036A1 (en) | Framework for joining datasets | |
| CN103559025A (en) | Software refactoring method through clustering | |
| CN102819609A (en) | Modeling method for persisting data model | |
| CN105426506B (en) | A kind of massive dynamic data management method | |
| CN103020318A (en) | Method for maintenance of database tables in database | |
| CN114443656A (en) | Customizable automated data model analysis tool and use method thereof | |
| CN107832387A (en) | A kind of SQL statement analytic method based on FMDB | |
| US8775414B2 (en) | System and method for executing queries | |
| US7555786B2 (en) | Method for providing security mechanisms for data warehousing and analysis |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |