OEA 2.11 supports standalone databases-Comparison Between SQLite and sqlce

In the design of the OEA platform, multiple databases must be supported, including at least sqlserver, Oracle, and a single-host file database. We have not implemented this part before, but reserved many interfaces. It took one day yesterday to allow OEA to support the local database sqlce.

 

Why should we support local databases?

1. Supports the development of simple local software.
   Occasional small applicationsProgramSuch as "personal accounting management" and "personal contact management. If the customer installs SQL Server for these simple software, the customer feels that it is all around. Choosing to use a single-host database will make the release very simple, small, and well-prepared programs, just send a package to the customer. (Of course,. Net 4.0 still needs to be installed .)
   That is to say, users can face individuals.
2. Standalone trial is supported.
   When developing and managing products, you often need to give them to some customers for trial use. You don't need to install the SQL Server stand-alone edition, so that these customers can try out our products very easily.

 

How does OEA support standalone edition?

In the process of using OEA for development, whether it is a standalone version, C/S version or B/S version,CodeThey are all the same, including the service layer, entity layer, and data layer. The single-host and C/S interfaces are completely reused. Therefore, after development, you only need to change the database connection configuration to a single-host database connection, for example:

After the configuration is complete, run the program, that is, the data folder is created under the output directory, and the database of the corresponding entity is automatically generated:

This is the corresponding library generated by the invoicing instance:

If you are interested, you can download the latest source code from the open-source address corresponding to the release of the OEA 2.9 platform and run it directly. (It takes about half a minute to generate a database at the first startup .)

 

 

Single-host database selection in OEA SQLite vs sqlce 

Since the standalone version of the database is used, which database is better? Currently, single-host databases are commonly used in the industry, such as SQLite, access, and sqlce. SQLite is widely used, and the performance is also good. This should be the first choice of OEA. In earlier versions, sqlce is mainly used for Embedded Development and has fewer applications on the desktop. Access now uses fewer and fewer people, and it is difficult to connect, which is not considered in OEA.

 

Next, I tested SQLite.

It mainly tests whether there are many conflicts between the database and the current design of OEA. It turns out that it is quite different. First, the autodb module of the OEA platform fully manages databases, so that developers can ignore the existence of databases during normal development. That is to say, this module will complete the establishment and modification of the database structure, data upgrades, and so on. However, I checked a lot of sqllite information and concluded that sqllite does not support deleting and modifying fields. The sqllite database structure information must be queried to support database differential upgrade. This is also supported in SQLite. Only the SQL statement for database creation of data tables can be queried from a sqlite_master table, you have to resolve it yourself. Secondly, in SQLite, foreign key constraints can be created, but are not checked. The integrity of the reference cannot be guaranteed, and the query speed is faster and sometimes useless. In addition, the general requirements such as cascading deletion are not supported, and all of them need to be compiled with triggers. Although autodb can be automatically written, it is too troublesome. The current design of deleting the aggregate object in OEA is to automatically cascade and delete the entire aggregation tree after the root object is deleted. It is troublesome to modify the aggregate tree, so this is still a conflict. Finally, in terms of interface manager, there are a lot of third-party software used by SQLite. After a trial, I feel that sqlitemgr is not bad, but it is not easy to use.

 

In addition, I studied sqlce again.

In fact, sqlce 3.5 has been used in OEA to implement Client Cache (For details, refer to cache module design in OEA ). Although sqlce 3.5 does not support concurrency, this problem is not found in the client cache. In addition, although autodb automatically generates and upgrades databases, it is always necessary to query data in the database during business development. However, the trouble with sqlce 3.5 lies in the absence of a good interface manager, which is inconvenient to use, whether integrated functions in Visual Studio or sqlserver management studio. In this way, it will be very troublesome to operate normally.

In view of this, I found some latest sqlce 4.0 materials. Fortunately, sqlce 4.0 now supports concurrency. When developing a web site in web matrix, sqlce 4.0 is used by default for data storage. It also provides a better interface MANAGER: sqlcetoolbox. The latest version was released two days ago, which is very easy to use. (In addition, this article describes how to use sqlce for development and returns the vs plug-in .) Second, data in sqlce 4.0 can be seamlessly migrated to sqlserver and sqlazure, which is also a great feature. Finally, it is very important. In terms of database design, sqlce 4 is basically consistent with sqlserver, and supports Primary keys, foreign keys, auto-incrementing columns, and cascading deletion. This makes it easier for us to support sqlce on OEA, whether it is autodb or Orm.

Some may say that SQLite is much faster than sqlce in terms of performance. But in fact, I personally feel that the sqlce speed is not slow. If the data volume is not very large, there should be no problem.

 

Summary:

 

 

Finally, after one day, OEA successfully supported the standalone database. Fortunately, there is sqlce 4, or it would be too hard!