A nhibbench BUG, NHibernateBUG

A nhibbench BUG, NHibernateBUG
I. background

In our current project, we use nhib.pdf to implement the data access layer.

When accessing data, some database tables are identified: there are clear table names and field names. In this case, you can follow the conventional method to create a ing from the database table to the class and use HQL to read and write the database.

However, some data accesses are not specific to the database tables. In the running stage, determine which database tables are accessed and which fields. Database tables and fields are not sure, naturally there is no way to suggest O-R ing, had to construct SQL statements.

Now that we have used nhib.pdf, we still use nhib.pdf when using SQL to access the database. It is mainly used to manage sessions and supports paging queries: You can specify the start row and the total number of required rows.

This is because greed is cheap and there is a problem.

Ii. Errors

When you use SQL (instead of HQL) to access the database and add the access range (the starting line or the total number of rows), sometimes there will be a strange problem: some fields clearly have values in the database, but it cannot be read.

For example, a database table is defined as MyTable (Id, Name, FromPoint ).

To query the first 10 records, use the following SQL statement:

select Id, Name, FromPoint from MyTable

After creating an SQL query, set the query range:

...var query = session.CreateSQLQuery(sql);query.SetFirstResult(0);query.SetMaxResults(10);...

For execution results, it is expected that each record has three fields. But in fact, only the values of the first two fields, the third field, and the FromPoint value are returned.

To view the logs of nhib.pdf, the generated SQL statement is:

select Id, Name from (select Id, Name, FromPoint from MyTable) where rownum<=10

This is confusing.

Iii. Cause

Search for the cause on the Internet. We had to sacrifice the final killer: Tracking the source code.

The conclusion is that nhib.pdf has a problem in parsing SQL statements, resulting in filtering out columns that should not be filtered out.

Specifically, the following code is available in the method ExtractColumnOrAliasNames of the class nhibloud. Dialect. Dialect:

if (token.StartsWithCaseInsensitive("select"))continue;if (token.StartsWithCaseInsensitive("distinct"))continue;if (token.StartsWithCaseInsensitive(","))continue;if (token.StartsWithCaseInsensitive("from"))break;

The purpose of this Code is not to use SQL reserved words such as select and distinct as columns, and once the from reserved word is encountered, it means that the column ends.

The problem is that it uses StartsWith instead of the entire word to judge, thus mistakenly hurting fields starting with these keywords. In addition, once a field starts with "from", the subsequent fields are filtered out.

Iv. Solution

The problematic method ExtractColumnOrAliasNames is static internal and there is no chance of modification. We had to bypass: add the rownum filter conditions.

Originally, we wanted to use nhib.pdf to implement cross-database operations. I also warned my friends to avoid using specific SQL syntaxes of database management systems such as Oracle and SQL Server.

Many programmers will instinctively think of a solution: Since it is open-source and the cause of the error is found, take it and modify it. compile a new version and use it. I specifically disagree with this. On the one hand, this will be out of the development of the main line version of nhib.pdf, and on the other hand, it will bring inconvenience to the reference of components: we are using Spring.. NET manages NHibernate and builds a version of its own. If you want to set up a bunch of metadata, you can start to think about it.

V. Scope

As far as I know, this BUG occurs in the following scenarios:

Zookeeper