SQLite | 第1部分

SQLite的 (SQLite)

SQLite is a library written in C that provides an engine relational database accessible through SQL. SQLite implements a large part of the standard SQL-92 and ACID properties.

SQLite是用C编写的库，提供了可通过SQL访问的引擎关系数据库。 SQLite实现了大部分标准SQL-92和ACID属性。

Unlike servers traditional databases, like MySQL or PostgreSQL, its particularity is not to reproduce the usual client-server, but to be directly integrated programs. The entire database (statements, tables, indexes and data) is stored in a platform-independent.

与服务器传统数据库(如MySQL或PostgreSQL)不同，它的特殊性不是重现通常的客户端服务器，而是直接集成程序。 整个数据库(语句，表，索引和数据)存储在独立于平台的位置。

D. Richard Hipp, creator of SQLite, chose to put this library and its source code in the public domain, allowing its use without restriction in both open source projects than in proprietary projects. The creator and part of the main developers of SQLite are employed by the American Society Hwaci.

SQLite的创建者D. Richard Hipp选择将该库及其源代码放在公共领域，从而允许在两个开源项目中(而不是在专有项目中)无限制地使用它。 SQLite的创建者和主要开发者的一部分受雇于美国华氏协会。

SQLite is the database engine as distributed in the world due to its use in many consumer software, including Firefox, Skype, Google Gears, in some products from Apple, Adobe and McAfee and the standard libraries many languages such as PHP or Python. With its extremely lightweight (under 300 kio), it is also very popular in embedded systems, including the most modern Smart phones: iPhone and the mobile operating systems Symbian and Android use as embedded database. In total, we can count between 200 and 300 million copies known and declared in the library.

由于SQLite在许多消费类软件中使用，包括Firefox，Skype，Google Gears，Apple，Adobe和McAfee的某些产品以及许多语言(例如PHP或Python)的标准库，因此SQLite是全球发行的数据库引擎。 它的重量极轻(低于300 kio)，因此在嵌入式系统中也很受欢迎，包括最现代的智能手机：iPhone以及Symbian和Android用作移动操作系统的嵌入式数据库。 总共，我们可以计算出库中已知和声明的200至3亿份副本。

D. Richard Hipp and colleague began the design of SQLite early 2000 while working at General Dynamics, then contract with the U.S. Navy. SQLite should be used in guided missiles, in order to replace IBM Informix databases running on HP-UX machines. The main objective was to free themselves of any installation or administration: installation or update of the database could take a whole day.

D. Richard Hipp和他的同事于2000年初在通用动力公司工作时就开始设计SQLite，然后与美国海军签约。 SQLite应该用于制导导弹，以便替换在HP-UX计算机上运行的IBM Informix数据库。 主要目标是使自己摆脱任何安装或管理工作：数据库的安装或更新可能需要一整天。

In August 2000, the first version of SQLite is published. It used gdbm (GNU Database Manager) for manipulating trees B.

2000年8月，发布了SQLite的第一个版本。 它使用gdbm(GNU数据库管理器)来操纵树B。

SQLite 2.0 suppressed the dependence gdbm and added transaction support.

SQLite 2.0抑制了对gdbm的依赖性，并增加了对事务的支持。

SQLite 3.0, produced with the help of AOL, was published in 2004 and adds, among others, regionalization (with support for unicode and snacks) and the declaration of types.

在AOL的帮助下生产SQLite 3.0于2004年发布，除其他外，还添加了区域化(支持unicode和点心)和类型声明。

SQLite功能–嵌入式数据库 (SQLite Features – Embedded Database)

The majority of systems management database are built as client-server paradigm, that is to say a library client software is integrated and used in one or more applications while the database engine works in a space of its own performance, or even on a different machine, as IT. SQLite, on the contrary, is directly integrated into the application that uses its software library, with its engine database. Access to an SQLite database is done by opening the file corresponding to this: each database is stored in a file of its own, with its statements, its tables, its indexes, but also its data.

大多数系统管理数据库都是作为客户机-服务器范例构建的，也就是说，库客户机软件已集成并在一个或多个应用程序中使用，而数据库引擎则在其自身性能的空间中运行，甚至在另一台机器上运行，作为IT。 相反，SQLite直接集成到使用其软件库和引擎数据库的应用程序中。 通过打开与此对应的文件来访问SQLite数据库：每个数据库都存储在其自己的文件中，包括其语句，表，索引以及数据。

This feature makes it extremely interesting SQLite as an alternative to text files, used as a storage medium integrated in many applications (settings, history, cache, etc.) because it makes data access faster, more secure, more structured , easier and totally independent of the platform, while not infringing on the ease of deployment of the application that uses it.

此功能使SQLite成为文本文件的替代品时非常有趣，它用作集成在许多应用程序(设置，历史记录，缓存等)中的存储介质，因为它使数据访问更快，更安全，更结构化，更容易且完全独立。平台，同时不损害使用该平台的应用程序的易于部署。

The removal of the intermediary between the application and data can also slightly reduce the latency of data access compared to systems using the client-server paradigm.

与使用客户端-服务器范例的系统相比，消除应用程序和数据之间的中介还可以稍微减少数据访问的延迟。

但是，此体系结构带来了几个问题： (However, this architecture poses several problems: )

• When a large number of clients accessing the same database, if a customer starts playing a game, the entire base is blocked from editing for other customers, and conversely if a customer starts writing in part, the entire base is blocked for reading other customers. They are put on hold during this period, which can be cons-performance;
  当大量客户访问同一数据库时，如果一个客户开始玩游戏，则整个库都无法为其他客户编辑；相反，如果某个客户开始部分写作，则整个库都将被阻塞以读取其他客户。 他们在此期间被搁置，这可能是不利的表现；
• It is very difficult to distribute the load over several machines: the use of a file system on the network will generate considerable traffic and latency, the fact that the base must be charged to the customer at each opening. This problem is even more important with websites where the database is reopened for each page requested. In addition, there may be errors in the synchronization locks on files that could allow simultaneous writing to two different customers on one part of the base and thus lead to the corruption of the database;
  将负载分布在多台计算机上非常困难：在网络上使用文件系统会产生可观的流量和延迟，事实上，每次开门都要向客户收费。 在网站上为每个请求的页面重新打开数据库的情况下，此问题尤为重要。 另外，文件上的同步锁中可能存在错误，这些错误可能导致同时向基础的某一部分上的两个不同客户写入数据，从而导致数据库损坏。
• With large databases, it is impossible to divide the base into several parts or files in order to distribute the load across multiple machines. Some file systems like FAT 32, have a maximum size of file that could quickly limit the expansion of a database.
  对于大型数据库，不可能将基础分为几个部分或文件，以便在多台计算机上分配负载。 某些文件系统(例如FAT 32)具有最大文件大小，这可能会很快限制数据库的扩展。

There are no extensions to own files for SQLite database, but it is common to see extensions like .Sqlite or .Db, sometimes followed by the version number of the library (.Sqlite3 .Db2, etc.). It is possible to use a database only saved in memory, without creating database file on disk, via the special filename: memory

对于SQLite数据库，没有自己文件的扩展名，但通常会看到.Sqlite或.Db之类的扩展名，有时还会加上该库的版本号(.Sqlite3 .Db2等)。 通过特殊的文件名：memory，可以使用仅保存在内存中的数据库，而无需在磁盘上创建数据库文件。

In general, it is advisable to use SQLite where data are not centralized and where expansion of the database size is not likely to become critical. If the database is to centralize a large mass of data and provide a large number of customers, it is preferable to use DBMS-based client-server paradigm. SQLite is designed to replace the text files and not database servers traditional.

通常，建议在数据不集中且数据库规模的扩展不太可能变得至关重要的情况下使用SQLite。 如果数据库要集中大量数据并提供大量客户，则最好使用基于DBMS的客户端-服务器范例。 SQLite旨在替换文本文件，而不是传统的数据库服务器。

SQLite编译器和虚拟机 (SQLite Compiler and virtual machine)

When a SQL query is passed to SQLite via the programming interface, it is compiled before being executed.

当通过编程接口将SQL查询传递给SQLite时，将在执行之前对其进行编译。

The sequencer controls the compiler divides the data into manageable parts separately (a query and its sub-query by example), it is passed to the parser that deals with decomposing queries into different objects that represent different levels and clauses of SQL. These objects are passed to the code generator that creates a low-level intermediate code, or bytecode.

定序器控制编译器将数据分别分为可管理的部分(例如查询及其子查询)，然后传递给解析器，该解析器将查询分解为代表不同级别和子句的不同对象。 这些对象被传递到代码生成器，该代码生成器创建低级中间代码或字节码。

The code obtained is a set of instructions (137 different instructions) called Opcodes. It is launched in the virtual machine in SQLite, which sees them as small programs describing a search operation, reading and modifying data.

获得的代码是称为操作码的一组指令(137个不同的指令)。 它是在SQLite的虚拟机中启动的，该虚拟机将它们视为描述搜索操作，读取和修改数据的小程序。

When the virtual machine interprets these instructions, she called the manager to tree B is based on layers of which the lowest level cache disk pages and HAL.

当虚拟机解释这些指令时，她称树B的管理器基于最低层缓存磁盘页面和HAL的层。

Continued…

继续…

翻译自: https://www.eukhost.com/blog/webhosting/sqlite-part-1/