[Database] desktop lightweight Database options: Access, SQLite, write by yourself?

Desktop lightweight Database options: Access, SQLite, write by yourself?
1. Access

When we do small projects, especially small MIS systems, we usually use databases to store data. It has been observed that most small systems use access databases. Some systems have changed the data file suffix to cover up the database type. In fact, as long as the data is changed back to MDB and opened with access, the data is clear at a glance, in fact, the ACCESS database itself has many problems: performance is not good; data is not secure, users can directly use access to open database files for data modification, even after encryption can be directly cracked; the access data volume is large, and the size of the file itself is very huge. The ACCESS database file is very vulnerable to damage. My system is always damaged due to unexpected power outages, and user data has been destroyed several times. To make up for the access defect, I used a MySQL database instead, but MySQL is a network database, which is complicated to install. Later, when I used python for a project, I found SQLite, which is an ideal alternative to access!

 

2. SQLite

SQLite is a small C library that implements an independent, embedded, and zero-configuration SQL database engine. Features include:

• Transaction operations are atomic, consistent, isolated, and persistent (acid), even after system crashes and power failures.

• Zero Configuration-no installation or management is required.

• The vast majority of sql92 standards are implemented.

• The entire database is stored in a single file.

• Database files can be freely shared between machines in different byte sequences.

• Supports databases up to 2 TB. (241 bytes)

• The size of a string or blob can be up to 2 GB (231 bytes ).

• Small code: the complete configuration is less than kb, ignoring some optional features less than kb.

• Most common operations are faster than popular Client/Server database engines.

• Simple and Easy-to-use APIs.

• Built-in TCL binding. It also provides binding for many other languages.

• Source code with good comments, 95% tested.

• Independent: no external dependency.

• The source code is located in the public domain. Can be used for any purpose.

SQLite is widely used, and Web applications are also using it. PhP5 has built-in SQLite extensions, so SQLite is the first choice for lightweight desktop databases. In addition to access and SQLite, there are also databases such as Firebird. I think SQLite is small enough for use. However, SQLite is a common database system, and the generated database file is easily opened in a binary editor. After being opened, the file header is "SQLite Format 3 ", this exposes that our data file is an SQLite file, and the database system is SQLite. You only need to open this file using the SQLite tool to see the content in the database. What can we do for privacy?

 

3. SQLite Customization

Sometimes, for privacy, we do not want software users to know that the database systems we use can adopt different encryption measures. You can set a password for the Access database to protect data. However, the access password is stored in the ACCESS database file, and the password for the database can be easily obtained using tools, in this way, our data structure is easily exposed to others. The safest way is to write our own database management system, so that our data storage format is private and cannot be known by others, in this way, even if he can see the binary data in the file, he cannot directly see the structure of the data model. However, it is not difficult to build a database management system by yourself. It is nothing more than storage management, index management, and SQL interfaces. However, it is time-consuming and labor-intensive if you do not have the relevant development experience, can we use "tailism" to transform our database management system from a ready-made Database Management System? Of course. Taking SQLite as an example, it is all open-source and can be downloaded from its website to the latest source code. Then, we can modify the source code into our own format and compile it. This is quite easy for users in Linux, but for developers who are not familiar with VC and BC in Windows, it is quite troublesome to configure the compiling environment and export DLL, let's use the prepared files to modify them.

We recommend that you use ultraedit to modify binary files. For better demonstration, we assume that we use Delphi, which we are familiar with, to use Delphi to access SQLite, we can use two methods: ODBC and SQLite to provide ODBC drivers; in addition, the asqlite component is used for access. asqlite is a dedicated component package that encapsulates SQLite DLL. It can be downloaded at http://www.sqlite.com.cn/upfiles/source/asqlite.rar. We chose asqlite, which is similar to asqlite in ODBC method.

1. Download SQLite.

2. Download asqlite and install it in Delphi.

3. Open the example program of asqlite to see the demonstration of SQLite. the following modifications take this example program as an example.

4. Asqlite is the encapsulation of sqlite3.dll. We need to modify the database file, sqlite3.dll, and asqlite.

5. Modify database files.

   The database file name is sampledb. Open this file with ultraedit. On the top of the file, you can see "SQLite Format 3", which is the identifier of the SQLite data file, we only need to modify this part. For example, if we change it to "crazycom dben 6", we 'd better change it to "00" (hexadecimal) if it is different from the original length ).

6. Modify sqlite3.dll.

   After modifying the above database file, if you run the example program, the data format will be incorrect, So modify sqlite3.dll accordingly.

   1) modify the file ID code

   Open sqlite3.dll, find "SQLite Format 3", find it, and change it to "crazycom dben 6". In this way, the example program runs normally.

   2) modify the function export table

   We can use the DLL and exe viewing tools, such as depend, to see which functions are exported by the DLL. All functions exported by sqlite3.dll start with "sqlite3, this exposes that the database we use is SQLite, so we need to change it to a name that is not easily identified, such as "crazydb. You must first find the location of the exported table and find "sqlite3 _" in ultraedit. When you see that many strings start with "sqlite3, the specific offset must be at 0x39db8. If you run the example program, the example program reports an exception because it cannot find the corresponding function entry, so we must modify the source file of asqlite.

7. Modify asqlite.

   Asqlite is a componentized encapsulation of sqlite3.dll. all calls are implemented through sqlite3.dll. Since we modified the export function interface of sqlite3.dll, We must modify the interfaces of asqlite and sqlite3.dll. The interface file is asgsqlite3.pas. Open this file and find the tasqlitedb. loadlibs function. You can see it. This function loads sqlite3.dll and finds various functions. You only need to modify the name of each function. Of course, it must correspond to the name of the export function of sqlite3.dll.

   After modification, reinstall asqlite.

   Re-compile the example program and run normally. The modification has basically been completed.

8. Modify the DLL name.

   Since the DLL name of SQLite is sqlite3.dll, it is easy to guess that it is SQLite. We can change the DLL name to any specific name. For example, we can change it to crazycomdb. dll. After modification, modify the example program, that is, set driverdll = 'crazycomdb. dll 'of tasqlitedb '.

4. Summary

This article provides recommendations for selecting a simple desktop database, compares two database management systems: access and SQLite, and provides detailed steps for customizing the SQLite database management system, enhancing its privacy and security, A specific example of SQLite application in Delphi is provided. There is no discussion about the ODBC driver, but it can be based on the method analogy in this article.