Introduction to ORM Query Language (oql)-concepts-Introduction to ORM Query Language (oql)-Example

Next introduction to ORM Query Language (oql)-instance

I. SQL and ORM

Relational Database Service (RDBMS) queries are structured query languages (Structured Query Language). Compared with advanced programming languages (imperative languages), SQL mainly describes what to do, SQL is also known as the fourth generation language (4gl) and is supported by most modern relational database systems. The core of SQL is "relational" operations. Database theoretical research proves that SQL is fully relational, but most advanced languages are object-oriented, to interact with relational databases, SQL becomes a necessary bridge. Because SQL is based on the "relationship" and the "object" of programming languages, to complete good interaction between them, there must be a "ing" process. The program implementing this process is ORM (Object/Relation Mapping ).

The application calls the ORM method. The ORM automatically generates the corresponding SQL statement to the database for query, and then the ORM maps the received relational data to the object. If ORM is not used, the application usually Splits a data access layer (DAL) to generate SQL statements and execute corresponding queries. Therefore, after the appearance of ORM, to a certain extent, it can replace the Dal, which reduces the workload of a layer and is very important for improving work efficiency.

It is one of the applications that use orm and traditional Dal.

 

 

(Figure 1: two data access architectures)

Ii. Problems Caused by ORM

 

 

After using Orm, you no longer need to write boring Dal code, and you do not need to splice SQL statements that may have security problems or incorrect field names. However, we found that, using ORM alone loses the flexibility of SQL, which is why many people refuse to use Orm. Let's look at how many people's ORM defines data operation interfaces. They often implement these interface methods by entity classes to create congested entity classes:

Public interface ientity <t> where T: Class
{
Void add ();
Void Update ();
Void Delete ();
List <t> getall ();
T getone (int id );
}

 

It is convenient to use the congested object class, but the insert, update, and delete operations can only be performed on one piece of data. The getall method not only extracts all the data, in addition, we may also take out unnecessary field values. The getone method may not actually be of the int type for the table's primary key, so these definitions will be problematic...

Therefore, we have seen many projects that use Orm. Whether all data is required or not, let's take it out first. Whether the primary key is of the int type or not, set a method there. It's a big deal to be an empty method, whether or not the current object requires the delete function (for example, some system user data cannot be deleted), the base class is directly implemented ......

Iii. Orm Query Language 1, separation of concerns

Can the ORM solve these problems? Orm originally completed "Object-link ing", but most of the orm here includes the "generate SQL" function, and to achieve the flexibility of SQL, then we must separate the ORM focus and extract the "generate SQL" function from the Orm, so that we can devote more energy to the invention of an object-oriented, the language used for ORM query. This is oql.

 

In fact, the ORM query language has been available for a long time. From the early hql of Hibernate to the MS's LINQ (linq2sql, EF actually uses the SQL generated by LINQ internally ), both of them can generate complex SQL statements, which are directly applied to the ORM framework. In almost the same period as LINQ, pdf. Net also invented its own ORM Query Language, known as oql. The oql mentioned below refers to the oql of PDF.

 

2. pdf. Net ORM framework

The ORM framework of PDF. Net consists of four parts:

1. Entityobject: PDF. NET Entity class;
2. Oql: Orm query language. It uses Object class objects as operation objects and generates Query expressions for Object Query objects.
3. Adohelper: the abstract class of the data access provider, which encapsulates ADO. by default, the Framework provides oledbprovider, odbcprovider, accessprovider, sqlserverprovider, and oracleprovider. To support more databases, you only need to inherit adohelper.
4. Entityquery <t>: object query object. It is an O/R Mapping object. The object type involved in operations is an object class. Inside an object, it converts oql to SQL and then calls adohelper to complete the query.

 

 

(Figure 2: PDF. Net oql Architecture)

 

If we only observe from the query call end, we find that oql is equivalent to SQL logic. One is an Object-Oriented Query and the other is a structured query:

 

 

Object Search: oql-> ORM-> objects

Equal

Structured Query: SQL> dB> Dataset

 

If the final result object = dataset, oql = SQL.

Therefore, the oql design goal is to make the SQL statement generated by oql basically achieve the same effect as the handwritten SQL statement. Since the specific implementation of SQL has many different versions, SQL statements used by SQL Server may not be used in Oracle many times. Only standard SQL statements are applicable, therefore, the oql design must also be such a standard SQL specification. Currently, the Standard Specification sql92 is implemented.

3. oql query paradigm

The following is an example of the query paradigm supported by oql. Note that the "BNF" paradigm is used in the following definition. To avoid misunderstanding, add the bfn content here. For details, refer to this link: http://baike.baidu.com/view/1137652.htm

 

Contents of the bacos paradigm

The Word ("word") in double quotes represents the characters themselves. Double_quote represents double quotation marks.

 

Words outside double quotes (which may contain underscores) represent the syntax section.

 

Required.

 

The square brackets ([]) are optional.

 

The braces ({}) contain items that can be repeated 0 to countless times.

 

Vertical bars (|) indicate either of the left and right sides, which is equivalent to "or.

 

: = Indicates "defined.

1. Data Query:

Oql q = oql. From (entityobject)

[. [Innerjoin | leftjoin | rightjoin] (entityobject2). On (entityobject. PK, entityobject2.fk)]

[. [Innerjoin | leftjoin | rightjoin] (entityobject3). On (entityobject. PK, entityobject3.fk)]

. Select ([entityobjectx. property1] [, entityobjectx. property2] [{,…}])

. Where ([<entityobject. property1> [, entityobject. property2] [,…] | [Oql2] | [oqlcompare])

. Groupby (entityobjectx. propertyn, "<ASC >|< DESC> ")

. Havingby (entityobjectx. propertym)

. End;

 

If paging is required, you only need to perform the following operations:

Q. Limit (page size [, page number [, total number of records]);

 

Execute this method to generate paging SQL statements for a specific database platform.

 

2. Data Statistics:

 

Oql q = oql. From (entityobject)

. Select (). Count (entityobject. propertyx, <"" >|< "countasname">)

. Where ([<entityobject. property1> [, entityobject. property2] [,…] | [Oql2] | [oqlcompare])

. End;

 

3. Data Update:

 

Oql q = oql. From (entityobject)

. Update ([entityobject. property1] [, entityobject. property2] [{,…}])

. Where ([<entityobject. property1> [, entityobject. property2] [,…] | [Oql2] | [oqlcompare])

. End;

 

4. data deletion:

 

Oql q = oql. From (entityobject)

. Delete ()

. Where ([<entityobject. property1> [, entityobject. property2] [,…] | [Oql2] | [oqlcompare])

. End;

 

In the next article, we will use instances to explain how to use oql.

 

 

Note: PDF. NET is now open-source, detailed information about the framework, please see the official website Introduction: http://www.pwmis.com/sqlmap

 

Open source information:

Before the Festival: PDF. net (pwmis data development framework) v4.5 open-source version ------------------------------------------------------------- after the article was published, enthusiastic netizens gave me some comments. Since it does not involve personal privacy, he posted the original text, if he sees any objection, please contact me in time. Thank you for your comments! In addition, I suggest you make a suggestion. If you disagree, leave a reason. Thank you. --------------------- Demarcation line -----------------------------------------

Shawn (630235793) 1:10:46
After a general look, I feel pretty good.

Shawn (630235793) 1:14:32
It's just that the blog post is too technically detailed and lacks the specific problem domains and user types that the Framework solves, as well as the differences between the overall architecture and the relational data access framework, it is hard for readers to understand the intention of the ORM framework.

Reply:

These questions are indeed not clearly stated, because my writing level is limited, I did not think of these questions, nor do I know how to express them.

The main problem to be solved by oql in pdf. NET is to make ORM operations as flexible as SQL.Most of the existing ORM frameworks are based on crud method-level operations, and there are no language-level operations like SQL. Otherwise, how can it be called 4gl? Now, I think this kind of capability also exists in LINQ, and oql in my framework also has this capability. So I boldly call it an "ORM query Laguage ", just as SQL is provided to the query engine of RDBMS, oql is provided to Orm.

Therefore, oql is intended for technical personnel who prefer ORM to access databases and SQL flexibility, or provide one of them (ORM or SQL) people who do not like the other method have the opportunity to experience the advantages of the other method.

The overall idea is to operate databases in an object-oriented way and write SQL statements in an OO way!

PS: Compared with LINQ, oql is more similar to the SQL style. I am not used to the first time I come into contact with LINQ, at least I am.

 

Shawn (630235793) 1:39:15
The initial idea of the data access framework design should first meet the call layer usage requirements. In other words, the request is transactional or non-transactional. If your requests are transactional, a transactional processing mechanism should be provided at the access layer. Instead of the application layer, it can handle the transaction or not. These should be placed at the access layer's external interaction interface to provide users with a reasonable choice.
Therefore, it is better to think about the layering of the framework.

Reply:

There are some interfaces on the object layer, but this graph is not easy to place and is not the focus. It is omitted.
Whether to use things is provided to users at the external interaction interface of the access layer.

Shawn (630235793) 1:47:13
After all, the data access framework should block the differences in operations between all databases for users, and all database-related operations should be encapsulated internally. Users do not have to consider this. They only need to make specific requests. The internal mechanisms of the access layer are used to interpret user requests, translate requests into operation commands of the underlying database based on the specific database selected by the user, and so on.

 

Reply:

As you said, the Framework does this. oql shields the differences between different SQL databases and generates localized SQL statements based on the specific databases used.

 

 

Guangzhou-haiyun² ° ¹ ² What are the similarities/differences with LINQ?
Guangzhou-haiyun² ° ¹ ² The main idea in this blog post is to let people think of LINQ.

Which of the following is the main promotion of ipv.net: it is as easy as LINQ, but its performance is superior.

 

Reply:

 

As a unique feature of. net, "language integration query" is integrated into the. NET language, which is its inherent advantage. Therefore, it must be a. NET platform and the Framework version must be. net3.5 or later.

PDF.. Net oql is also the language used by the ORM framework, but the oql syntax is closer to SQL and easy to use. Moreover, oql is not used. net, so that it can be implemented as long as it is an object-oriented language and supports generics. Therefore, it is entirely possible to port it to C ++ and Java. NET platform, it also only needs. supported by net2.0.