Simple analysis of some design changes in SQLite4 _ database other

1.0 Summary of Content

SQLite4 is a compact, self-contained, 0-maintained Acid database engine placed in the library, like SQLite3, but with improved interfaces and file formats.

The runtime environment is encapsulated within an object.

A good key value pair was used for the storage engine:

• A single, large key space-not a separate key space and index for each table in SQLite3.
• Sorts by key in dictionary order.
• Multiple storage engines that can be interchanged at run time.
• Default storage on disk attentive use of a merged database with a log structure.

The table's primary key is really used as the key for the storage engine.

You can use decimal arithmetic.

FOREIGN KEY constraints and recursive triggers are enabled by default.

The overlay index can display the declaration.

2.0 Overview

SQLite4 is an option for SQLite3, not an alternative. SQLite3 is not obsolete yet. SQLite3 and QLite4 will be supported in parallel.   The legacy of SQLite3 will not be abandoned. SQLite3 will also be continuously maintained and improved. But if needed, the designers of the new system would now be able to choose SQLite4 instead of SQLite3.

SQLite4 efforts to maintain the best features of the SQLite3, while not destroying the compatibility of the premise to solve the SQLite3 can not repair the problem. SQLite3 and SQLite4 will continue to maintain the same characteristics:

• SQLite4 is placed in a library and linked to a large application's complete, relational, transactional, ACID, SQL database engine. No server, I/O is directly oriented to the hard drive.
• SQLite4 source code Anyone can be used for any purpose.  There are no restrictions on copyright, publishing, or exposing source code or compiling binary files. There's no need to worry about annoying licenses.
• Dynamic types are used rather than the rigid static types used by most other SQL database engines.
• (default) Mirroring on disk is a separate disk file with a good and stable file format, making the SQLITE4 library suitable for use as a file format for an application.
• SQLite4 will be fast and reliable, and it works fine without the administrator worrying about it.
• The SQLITE4 implementation has only the simplest dependencies, so it can be easily integrated into embedded systems or other unconventional runtime environments.

Implementation still uses the commonly used assembly language C. SQLite4 uses more C99 features than SQLite3, but it can still be compiled with a common compiler. SQLite4 uses such as size_t,int64_t,uint64_t and other standard data types.

The SQLITE4 programming interface is very similar to the SQLite3, except that the naming prefix is changed from sqlite3_ to Sqlite4_. The old and voided interfaces in the SQLite3 have been removed from the SQLite4. Some functions are added to the parameters, sometimes the parameters are slightly modified or the order of their parameters are reordered. Some interface names have been modified to make them more consistent with their functions. In general, SQLite4 's programming interface is very similar to SQLite3 's, so porting a SQLite3 application to SQLite4 takes only one hours or two hours to complete the search substitution.

SQLite3 and SQLite4 do not share any symbols, so it is possible to embed SQLite3 and SQLite4 simultaneously in the same process.

Major changes in 3.0 SQLite4
3.1 Run Time objects

The first parameter of some interfaces in SQLite4 receives a (new) pointer to a Sqlite4_env object that defines the Run-time environment. Sample programs that need to receive sqlite4_env pointers include:

• Sqlite4_open ()
• Sqlite4_malloc (), Sqlite4_realloc (), and Sqlite4_free ()
• sqlite4_mprintf ()
• Sqlite4_random ()
• Sqlite4_config ()

An Sqlite4_env object instance defines how the SQLITE4 interacts with other systems. A Sqlite4_env object contains methods that can:

• Assign, enter, leave, and retract the mutex
• allocating, adjusting, and freeing heap memory,
• Access and control of the underlying key/value storage engine,
• Using high quality random seeds to initialize the built in PRNG,
• Gets the current time and date with the local time zone,
• Log an error log message.

The SQLITE4 build of the standard platform (Windows and UNIX) contains a global sqlite4_env object, which is usually suitable for the platform. If there is a pointer to a Sqlite4_env object in the parameter of an interface program, and the pointer to this parameter is a null pointer, the interface program uses the default global Sqlite4_env object. In addition, some applications may require two or more SQLite4 instances to be run on the same address space, with each instance using its own different mutex primitives, different memory stacks, and different time and date functions. SQLite4 satisfies this requirement by creating different sqlite4_env objects for each instance of the database. Global and static variables are also abolished in the Sqlite4_env object, which makes it very easy to migrate SQLite4 to embedded systems that provide limited support for static or global data.

3.2 Simplified key/value storage engine

The key/value storage engine used in relation to SQLITE3,SQLITE4 has a significantly simplified interface. This storage engine is pluggable and can be changed at run time by making the appropriate changes to the Qlite4_env object before opening the new database connection.

SQLITE4 requires a storage engine that implements an ordered key/value pair, and its keys and values are binary data of any length. Keys must be unique and sorted by dictionary. That is, the key should be sorted according to a comparison function, for example:

Copy Code code as follows:

int Key_compare (const void *key1, int n1, const void *key2, int n2) {
int c = memcmp (Key1, Key2, n1<n2? n1:n2);
if (c==0) c = n1-n2;
return C;
}

Given a probe key, SQLite4 needs to be able to find its closest key, and then iterate through the keys in ascending or descending order of the dictionary order. Inserting data into an existing key overwrites the old data. transactions, including atomic commit and rollback, are the responsibility of the storage engine.

Instead of SQLite4 all data to a single key space via tables and indexes, each table and index in SQLITE3 requires a separate key space. SQLite4 storage is also different from SQLite3 because it requires the storage engine to sort the keys in a dictionary order, and SQLite3 uses a very complex comparison function to determine the order in which records are stored.

The communication between the SQLITE4 and the storage engine is done through a well-defined and simple interface. The new storage engine can be replaced during run time: just replace some of the function pointers in the Sqlite4_env object before you specify the database connection.

If the replacement storage engine does not support rollback, this means that SQLite4 cannot run rollback. If the replacement storage engine does not support transaction nesting, this means that SQLite4 cannot run nested transactions. As a result, the less functionality of the storage engine embedded in the SQLite4, the poorer the overall system functionality.

The built-in storage engine by default is the merged database for the log structure. It is many times faster than LEVELDB and supports nested transactions, which store the entire content in a single disk file. Future versions of SQLite4 may also contain a built-in storage engine with a B-tree structure.

3.3 Now the primary key is the real primary key

Sqlite3 allows you to declare any single or multiple columns in the table as primary keys. But internally, SQLite3 only treats primary key simply as a unique constraint. The key that is actually used for storage is the rowid of each row.

Sqlite4 instead, it actually inserts the storage engine as a key using the declared table primary key (or, more specifically, the encoded value of the primary key). SQLite4 tables usually do not have rowid (unless the table has primary key, a ROWID is required as an implicit primary key.) This means that the content is stored in the order of the primary key on the disk. This also means that records can be positioned by a single query to the primary key. In SQLite3, a search on a primary key means finding rowID in an automatically created index and then doing a two search of the primary table based on that rowid.

SQLite4 all elements that require PRIMARY KEY cannot be empty.  This is an SQL standard. Due to the negligence of earlier versions, SQLite3 did not enforce this NOT NULL constraint on the primary KEY column, and when the vulnerability was discovered SQLite3 was already widely used, and activation of the NOT NULL constraint might affect too many programs.
3.4 Decimal Digits

SQLite4 uses decimal arithmetic to do all the numerical calculations.  SQLite4 never uses a C-type double or float (in addition to the use of the language interface routines when converting between double and inner decimal representations).  Instead, all numeric values are represented internally as a 18-bit decimal number with a 3-digit 10-based index. The characteristics of this presentation are:

• Even on a platform that lacks support for IEEE 754 binary64 floating-point numbers, it works reliably and is compatible.
• Currency calculations are usually accurate and do not need to be rounded.
• Any signed and unsigned 64-bit integers can be represented precisely.
• The range and precision of floating-point numbers exceed the IEEE 754 binary64 floating-point number.
• Positive infinity and negative infinity and NaN (not-a-number) all have good representations.

SQLite4 makes no difference between integer and floating-point numbers. But there is a difference between the exact and approximate numbers. In C + +, the number of digits is accurate and the floating-point numbers are approximate.  But SQLite4 is not necessarily the case.  Floating-point numbers can be accurate in SQLite4. The number of integers that can be represented by 64-bit is always accurate in SQLite4, while large integer numbers may be approximate.

The SQLITE4 number format is for internal use.  Numbers can be converted between integral and double types for input and output. The storage space on the disk requires a SQLite4 numeric value from 1 to 12 digits, depending on its size and the number of important digits.

3.5 FOREIGN KEY constraints and recursive triggers are enabled by default

FOREIGN KEY constraints are not available in earlier versions of SQLite3, and they turn off the ability to backward compatibility by default. However, foreign KEY constraints are always valid and are open by default in SQLite4. All foreign KEY constraints are default delays, although they can be created by definition. However, there is no effective mechanism to trigger a foreign key constraint, whether delayed or created immediately.

SQLite3 provides recursive triggers, but this is only a function that works in a runtime. In SQLite4, all triggers are recursive in all time periods.

3.6 Explicit Index Overlay

SQLite4 an optional clause in the CREATE TABLE statement that defines additional column information that repeats in the index.  This allows the application developer to explicitly create an overlay index without using any tricks in the SQLite4. For example:

CREATE INDEX cover1 on table1 (a,b) covering (c,d);

The Cover1 index above can be used in a single lookup operation in the storage engine as a query in the form of "SELECT c,d from table1 where a=?1 and b=?2." If there are no additional covering clauses in the index, SQLITE4 may do two operations in the storage engine; One operation finds the primary key based on the value of A and B, and the second operation finds the values of C and D based on the primary key. The covering clause makes the values of C and D available in the index, which means they can be extracted from the index without a second lookup.

There is a change on the covering statement:

CREATE INDEX cover2 on table (x,y) covering all;

The covering all statement means that all columns in the datasheet are duplicated in the index, which ensures that the original datasheet will never be accessed to complete a query that uses the index.  The disadvantage of this approach is, of course, the duplication of information and therefore the larger database file. But by explicitly specifying covering shutdown on index metrics, SQLITE4 allows application developers to weigh the space and time from the application to make it more applicable.