外文翻譯--sqlite與眾不同的特性

1、<p><b>　　附錄</b></p><p>　　附錄A：外文資料翻譯—原文部分</p><p>　　Distinctive Features of SQLite</p><p>　　(From SQLite.Org (http://www.sqlite.org/different.html))</p><p&

2、gt;　　This passage highlights some of the characteristics of SQLite that are unusual and which make SQLite different from many other SQL database engines. </p><p>　　Zero-Configuration</p><p>　　SQ

3、Lite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrato

4、r to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after

5、a system crash or power failure. There is nothing to troubleshoot. </p><p>　　SQLite just works. </p><p>　　Other more familiar database engines run great once you get them going. But doing the in

6、itial installation and configuration can be intimidatingly complex. </p><p>　　Serverless</p><p>　　Most SQL database engines are implemented as a separate server process. Programs that want to ac

7、cess the database communicate with the server using some kind of interprocess communication (typically TCP/IP) to send requests to the server and to receive back results. SQLite does not work this way. With SQLite, the p

8、rocess that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process. </p><p>　　There are advantages and disadvantages to being serverle

9、ss. The main advantage is that there is no separate server process to install, setup, configure, initialize, manage, and troubleshoot. This is one reason why SQLite is a "zero-configuration" database engine. Pr

10、ograms that use SQLite require no administrative support for setting up the database engine before they are run. Any program that is able to access the disk is able to use an SQLite database. </p><p>　　On th

11、e other hand, a database engine that uses a server can provide better protection from bugs in the client application - stray pointers in a client cannot corrupt memory on the server. And because a server is a single pers

12、istent process, it is able control database access with more precision, allowing for finer grain locking and better concurrency. </p><p>　　Most SQL database engines are client/server based. Of those that are

13、 serverless, SQLite is the only one that this author knows of that allows multiple applications to access the same database at the same time. </p><p>　　Single Database File</p><p>　　An SQLite da

14、tabase is a single ordinary disk file that can be located anywhere in the directory hierarchy. If SQLite can read the disk file then it can read anything in the database. If the disk file and its directory are writable,

15、then SQLite can change anything in the database. Database files can easily be copied onto a USB memory stick or emailed for sharing. </p><p>　　Other SQL database engines tend to store data as a large collect

16、ion of files. Often these files are in a standard location that only the database engine itself can access. This makes the data more secure, but also makes it harder to access. Some SQL database engines provide the optio

17、n of writing directly to disk and bypassing the filesystem all together. This provides added performance, but at the cost of considerable setup and maintenance complexity. </p><p>　　Stable Cross-Platform Dat

18、abase File</p><p>　　The SQLite file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture. Big-endian or little-endia

19、n, 32-bit or 64-bit does not matter. All machines use the same file format. Furthermore, the developers have pledged to keep the file format stable and backwards compatible, so newer versions of SQLite can read and write

20、 older database files. </p><p>　　Most other SQL database engines require you to dump and restore the database when moving from one platform to another and often when upgrading to a newer version of the softw

21、are. </p><p><b>　　Compact</b></p><p>　　When optimized for size, the whole SQLite library with everything enabled is less than 225KiB in size (as measured on an ix86 using the "s

22、ize" utility from the GNU compiler suite.) Unneeded features can be disabled at compile-time to further reduce the size of the library to under 170KiB if desired. </p><p>　　Most other SQL database engin

23、es are much larger than this. IBM boasts that its recently released CloudScape database engine is "only" a 2MiB jar file - 10 times larger than SQLite even after it is compressed! Firebird boasts that its clien

24、t-side library is only 350KiB. That's 50% larger than SQLite and does not even contain the database engine. The Berkeley DB library from Sleepycat is 450KiB and it omits SQL support, providing the programmer with onl

25、y simple key/value pairs. </p><p>　　Manifest typing</p><p>　　Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular

26、datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stor

27、ed. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some </p><p>　　As far as we can tell, the SQL language specificat

28、ion allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very st

29、rongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dy

30、namically </p><p>　　Variable-length records</p><p>　　Most other SQL database engines allocated a fixed amount of disk space for each row in most tables. They play special tricks for handling BLO

31、Bs and CLOBs which can be of wildly varying length. But for most tables, if you declare a column to be a VARCHAR(100) then the database engine will allocate 100 bytes of disk space regardless of how much information you

32、actually store in that column. </p><p>　　SQLite, in contrast, use only the amount of disk space actually needed to store the information in a row. If you store a single character in a VARCHAR(100) column, th

33、en only a single byte of disk space is consumed. (Actually two bytes - there is some overhead at the beginning of each column to record its datatype and length.) </p><p>　　The use of variable-length records

34、by SQLite has a number of advantages. It results in smaller database files, obviously. It also makes the database run faster, since there is less information to move to and from disk. And, the use of variable-length reco

35、rds makes it possible for SQLite to employ manifest typing instead of static typing. </p><p>　　Readable source code</p><p>　　The source code to SQLite is designed to be readable and accessible t

36、o the average programmer. All procedures and data structures and many automatic variables are carefully commented with useful information about what they do. Boilerplate commenting is omitted. </p><p>　　SQL

37、statements compile into virtual machine code</p><p>　　Every SQL database engine compiles each SQL statement into some kind of internal data structure which is then used to carry out the work of the statement

38、. But in most SQL engines that internal data structure is a complex web of interlinked structures and objects. In SQLite, the compiled form of statements is a short program in a machine-language like representation. User

39、s of the database can view this virtual machine language by prepending the EXPLAIN keyword to a query. </p><p>　　The use of a virtual machine in SQLite has been a great benefit to the library's developme

40、nt. The virtual machine provides a crisp, well-defined junction between the front-end of SQLite (the part that parses SQL statements and generates virtual machine code) and the back-end (the part that executes the virtua

41、l machine code and computes a result.) The virtual machine allows the developers to see clearly and in an easily readable form what SQLite is trying to do with each statement it compiles, wh</p><p>　　Public

42、domain</p><p>　　The source code for SQLite is in the public domain. No claim of copyright is made on any part of the core source code. (The documentation and test code is a different matter - some sections o

43、f documentation and test logic are governed by open-source licenses.) All contributors to the SQLite core software have signed affidavits specifically disavowing any copyright interest in the code. This means that anybod

44、y is able to legally do anything they want with the SQLite source code. </p><p>　　There are other SQL database engines with liberal licenses that allow the code to be broadly and freely used. But those other

45、 engines are still governed by copyright law. SQLite is different in that copyright law simply does not apply. </p><p>　　The source code files for other SQL database engines typically begin with a comment de

46、scribing your license rights to view and copy that file. The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers a blessing: </p><p

47、>　　May you do good and not evil</p><p>　　May you find forgiveness for yourself and forgive others</p><p>　　May you share freely, never taking more than you give. </p><p>　　SQL la

48、nguage extensions</p><p>　　SQLite provides a number of enhancements to the SQL language not normally found in other database engines. The EXPLAIN keyword and manifest typing have already been mentioned above

49、. SQLite also provides statements such as REPLACE and the ON CONFLICT clause that allow for added control over the resolution of constraint conflicts. SQLite supports ATTACH and DETACH commands that allow multiple indepe

50、ndent databases to be used together in the same query. And SQLite defines APIs that allows the user </p><p>　　附錄B：外文資料翻譯—譯文部分</p><p>　　SQLite與眾不同的特性</p><p>　?。ㄒ?SQLite官方網(wǎng)站（http://w

51、ww.sqlite.org/different.html））</p><p>　　這篇文章介紹了SQLite一些不尋常的特性，這些特性使SQLite與其它許多SQL數(shù)據(jù)庫(kù)引擎不同。</p><p><b>　　無(wú)需配置</b></p><p>　　SQLite在使用前不需要“安裝”。沒(méi)有“設(shè)置”的程序。沒(méi)有需要啟動(dòng)、停止或者配置的服務(wù)器進(jìn)程。沒(méi)

52、有必要讓一名管理員去創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)實(shí)例或者給用戶分配使用權(quán)限。SQLite使用無(wú)配置的文件。無(wú)需做任何事情來(lái)告知系統(tǒng)SQLite正在運(yùn)行。在系統(tǒng)或者電源故障后不需任何操作來(lái)恢復(fù)。沒(méi)有任何問(wèn)題需要解決。</p><p>　　SQLite就能夠這么運(yùn)行著。</p><p>　　其它許多我們熟悉的數(shù)據(jù)庫(kù)引擎一旦你讓它們運(yùn)行起來(lái)，它們將運(yùn)行得非常好。但是前期的安裝和配置可能是相當(dāng)復(fù)雜的。<

53、;/p><p><b>　　無(wú)服務(wù)器</b></p><p>　　大多數(shù)SQL數(shù)據(jù)庫(kù)引擎被應(yīng)用為一個(gè)分離的服務(wù)器處理進(jìn)程。需要訪問(wèn)數(shù)據(jù)庫(kù)的程序必須與服務(wù)器進(jìn)行通訊，它們使用一些進(jìn)程間通訊的方法（典型的如TCP/IP）將請(qǐng)求發(fā)送給服務(wù)器并接收發(fā)送回的結(jié)果。SQLite不采用這種方法工作。對(duì)于SQLite而言，需要訪問(wèn)數(shù)據(jù)庫(kù)的進(jìn)程直接讀寫(xiě)保存在磁盤(pán)上的數(shù)據(jù)庫(kù)文件。不存在中間的

54、服務(wù)器進(jìn)程。</p><p>　　無(wú)服務(wù)器有其優(yōu)點(diǎn)和缺點(diǎn)。主要優(yōu)點(diǎn)是它沒(méi)有分開(kāi)的服務(wù)器進(jìn)程需要去安裝、設(shè)置、配置、初始化、管理和排錯(cuò)。這正是SQLite被稱為“零配置”數(shù)據(jù)庫(kù)引擎的原因之一。使用SQLite的程序，在啟動(dòng)之前，不需要管理員去配置數(shù)據(jù)庫(kù)引擎。任何能夠讀取磁盤(pán)的程序都能使用SQLite數(shù)據(jù)庫(kù)。</p><p>　　另外一方面，一個(gè)使用服務(wù)器的數(shù)據(jù)庫(kù)引擎在遇到客戶端應(yīng)用程序的Bu

55、g時(shí)，可以提供更好的保護(hù)——如客戶端遺失的指針無(wú)法釋放服務(wù)器上的內(nèi)存。并且因?yàn)橐粋€(gè)服務(wù)器是一個(gè)單獨(dú)的持續(xù)運(yùn)行的進(jìn)程，它能夠控制數(shù)據(jù)庫(kù)進(jìn)行高精度的讀寫(xiě)，允許完善的原子鎖和更好的并發(fā)控制。</p><p>　　大多數(shù)SQL數(shù)據(jù)庫(kù)引擎是基于客戶端/服務(wù)器模式的。對(duì)于那些無(wú)服務(wù)器的數(shù)據(jù)庫(kù)引擎而言，據(jù)筆者所知，SQLite是唯一一個(gè)允許多個(gè)應(yīng)用程序在同一時(shí)間訪問(wèn)同一個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)引擎。</p><p&

56、gt;<b>　　單一數(shù)據(jù)庫(kù)文件</b></p><p>　　一個(gè)SQLite數(shù)據(jù)庫(kù)是一個(gè)單獨(dú)的普通磁盤(pán)文件，它可以被放置在目錄結(jié)構(gòu)中的任何位置。如果SQLite能夠讀取該磁盤(pán)文件，那么它就可以讀取數(shù)據(jù)庫(kù)中的任何內(nèi)容。如果該磁盤(pán)文件和它所處的文件夾是可寫(xiě)入的，那么SQLite能夠更改數(shù)據(jù)庫(kù)中的任何內(nèi)容。數(shù)據(jù)庫(kù)文件能夠被非常簡(jiǎn)單地拷貝到一個(gè)USB記憶棒中或者通過(guò)電子郵件進(jìn)行共享。</p&

57、gt;<p>　　其它SQL數(shù)據(jù)庫(kù)引擎傾向于將數(shù)據(jù)保存到一個(gè)大規(guī)模的文件集合中。通常這些文件位于一個(gè)標(biāo)準(zhǔn)的地方，只有數(shù)據(jù)庫(kù)引擎自己才可以訪問(wèn)。這使得數(shù)據(jù)更加安全，不過(guò)也讓它更難訪問(wèn)。一些SQL數(shù)據(jù)庫(kù)引擎提供了直接寫(xiě)入磁盤(pán)同時(shí)繞過(guò)文件系統(tǒng)的選項(xiàng)。這提供了附加的性能，不過(guò)是以相當(dāng)大的設(shè)置和維護(hù)復(fù)雜度作為代價(jià)的。</p><p>　　穩(wěn)定的跨平臺(tái)數(shù)據(jù)庫(kù)文件</p><p>　　SQ

58、Lite文件格式是跨平臺(tái)的。在一臺(tái)機(jī)器上寫(xiě)的一個(gè)數(shù)據(jù)庫(kù)文件可以被拷貝到一臺(tái)不同架構(gòu)的機(jī)器上并使用。大端或小端存儲(chǔ)、32位或64位不是問(wèn)題。所有的機(jī)器使用相同的文件格式。此外，開(kāi)發(fā)者保證文件格式的平穩(wěn)和向下兼容，所以SQLite更新的版本能夠讀寫(xiě)老版本的數(shù)據(jù)庫(kù)文件。</p><p>　　大多數(shù)其它的SQL數(shù)據(jù)庫(kù)引擎要求你切斷并且恢復(fù)數(shù)據(jù)庫(kù)，當(dāng)你要將數(shù)據(jù)庫(kù)從一個(gè)平臺(tái)轉(zhuǎn)移到另一個(gè)平臺(tái)時(shí)，或者當(dāng)你升級(jí)到一個(gè)更新版本的軟件

59、時(shí)。</p><p><b>　　簡(jiǎn)潔</b></p><p>　　當(dāng)優(yōu)化文件大小時(shí)，一個(gè)開(kāi)啟所有功能的完整的SQLite庫(kù)小于225KB（使用ix86中GNU編譯套件的“size”工具測(cè)量）。如果需要的話，不必要的特性可以在編譯時(shí)禁用，以便于進(jìn)一步減少庫(kù)文件的大小使其低于170KB。</p><p>　　大多數(shù)其他的SQL數(shù)據(jù)庫(kù)引擎比這個(gè)大得

60、多。IBM聲稱，它近期發(fā)布的CloudScape數(shù)據(jù)庫(kù)引擎“僅僅”為一個(gè)2MB的jar文件（譯者注：一種JAVA的軟件壓縮包格式）——即使它處于壓縮狀態(tài)，其大小依舊是SQLite的10倍！Firebird聲稱它的客戶端庫(kù)文件只有350KB大小。那還是比SQLite大了50%，并且還不包括數(shù)據(jù)庫(kù)引擎。來(lái)自Sleepycat的Berkeley DB庫(kù)文件是450KB，并且它省略了對(duì)SQL的支持，為程序員提供的僅僅是簡(jiǎn)單的鍵/值對(duì)。</

61、p><p><b>　　弱類(lèi)型</b></p><p>　　大多數(shù)SQL數(shù)據(jù)庫(kù)引擎使用靜態(tài)類(lèi)型。一種數(shù)據(jù)類(lèi)型與一張表中的每一列相關(guān)聯(lián)，并且只有屬于該特定數(shù)據(jù)類(lèi)型的數(shù)據(jù)被允許存放在該列中。SQLite通過(guò)使用弱類(lèi)型（manifest typing）來(lái)減少這種限制。在弱類(lèi)型中，數(shù)據(jù)類(lèi)型是數(shù)據(jù)自己的一個(gè)屬性，而不是數(shù)據(jù)所在列的屬性。因此SQLite允許用戶存儲(chǔ)任何數(shù)據(jù)類(lèi)型的值到

62、任何列中，而不必考慮該列聲明的數(shù)據(jù)類(lèi)型。（這一規(guī)則有一些例外：一個(gè)INTEGER PRIMARY KEY類(lèi)型的列只能存儲(chǔ)整數(shù)。并且在可能的情況下，SQLite會(huì)試圖將值強(qiáng)行轉(zhuǎn)換為該列聲明的數(shù)據(jù)類(lèi)型。）</p><p>　　至于我們可以告訴你的是，SQL語(yǔ)言規(guī)范允許使用弱類(lèi)型。不過(guò)，大多數(shù)其它的SQL數(shù)據(jù)庫(kù)引擎都使用靜態(tài)類(lèi)型，所以因此一些人認(rèn)為SQLite使用弱類(lèi)型是一個(gè)Bug。不過(guò)SQLite的作者非常堅(jiān)持地認(rèn)為

63、這是一個(gè)特性。在SQLite中使用弱類(lèi)型是一個(gè)經(jīng)過(guò)深思熟慮的設(shè)計(jì)方案，它在實(shí)踐中已被證實(shí)使SQLite更可靠并易于使用，尤其在與使用動(dòng)態(tài)類(lèi)型的編程語(yǔ)言，如Tcl和Python，結(jié)合使用時(shí)。</p><p><b>　　可變長(zhǎng)度記錄</b></p><p>　　大多數(shù)其它的SQL數(shù)據(jù)庫(kù)引擎被分配一個(gè)固定數(shù)量的磁盤(pán)空間來(lái)存儲(chǔ)大多數(shù)表的每一行。它們使用特定的技巧來(lái)處理可能無(wú)

64、限大的BLOB和CLOB類(lèi)型數(shù)據(jù)。不過(guò)對(duì)于大多數(shù)的表，如果你聲明一個(gè)類(lèi)型為VARCHAR(100)的列，然后數(shù)據(jù)庫(kù)引擎將分配100個(gè)字節(jié)的磁盤(pán)空間，而不考慮你將在該列中實(shí)際存儲(chǔ)多少信息。</p><p>　　相比之下，SQLite只使用存儲(chǔ)一行數(shù)據(jù)信息所需的實(shí)際磁盤(pán)空間。如果你在一個(gè)VARCHAR(100)類(lèi)型的列中存儲(chǔ)一個(gè)單字符，那么它僅消耗一個(gè)字節(jié)的磁盤(pán)空間。（實(shí)際上是兩個(gè)字節(jié)——在每一列的開(kāi)頭有一些額外的空

65、間用來(lái)存儲(chǔ)它的數(shù)據(jù)類(lèi)型和長(zhǎng)度。）</p><p>　　SQLite通過(guò)使用可變長(zhǎng)度記錄有很多優(yōu)勢(shì)。很顯然，它使數(shù)據(jù)庫(kù)文件更小。它也讓數(shù)據(jù)庫(kù)運(yùn)行更快，因?yàn)樗枰獜拇疟P(pán)上讀寫(xiě)的數(shù)據(jù)也更少。并且，對(duì)可變長(zhǎng)度記錄的使用使SQLite用弱類(lèi)型替換靜態(tài)類(lèi)型成為可能。</p><p><b>　　易讀的源代碼</b></p><p>　　SQLite的源代碼

66、被設(shè)計(jì)為易于閱讀的，并且一般的程序員也可以理解。所有的程序、數(shù)據(jù)結(jié)構(gòu)和自動(dòng)化變量都有詳盡的注釋?zhuān)杏玫男畔⒑烷_(kāi)發(fā)者做了些什么。引用注釋被省略了。</p><p>　　SQL語(yǔ)句編譯為虛擬機(jī)器碼</p><p>　　每個(gè)SQL數(shù)據(jù)庫(kù)引擎都會(huì)將每條SQL語(yǔ)句編譯為一些內(nèi)部的數(shù)據(jù)結(jié)構(gòu)，然后被用來(lái)執(zhí)行語(yǔ)句所指定的工作。不過(guò)在大多數(shù)的SQL引擎中，內(nèi)部數(shù)據(jù)結(jié)構(gòu)是一張由互聯(lián)的結(jié)構(gòu)和對(duì)象組成的復(fù)雜網(wǎng)

67、絡(luò)。在SQLite中，語(yǔ)句編譯的形式為一段短小的類(lèi)似描述的機(jī)器語(yǔ)言程序。數(shù)據(jù)庫(kù)的用戶可以通過(guò)在一個(gè)查詢前追加EXPLAIN關(guān)鍵字的方式來(lái)查看這種虛擬機(jī)器語(yǔ)言。</p><p>　　SQLite對(duì)虛擬機(jī)的使用為程序庫(kù)的開(kāi)發(fā)帶來(lái)了極大的便利。虛擬機(jī)在SQLite的前臺(tái)（從語(yǔ)法上分析SQL語(yǔ)句并生成虛擬機(jī)器碼的部分）和后臺(tái)（運(yùn)行虛擬機(jī)器碼并且計(jì)算結(jié)果的部分）之間提供了一個(gè)簡(jiǎn)單的、定義明確的結(jié)合點(diǎn)。虛擬機(jī)用易于閱讀的形式

68、讓開(kāi)發(fā)者看清SQLite對(duì)每句編譯好的語(yǔ)句嘗試做什么，這對(duì)調(diào)試有極大的幫助。根據(jù)它是如何編譯的，SQLite同樣有能力追蹤虛擬機(jī)的指令——打印每個(gè)虛擬機(jī)的指令和它運(yùn)行的結(jié)果。</p><p><b>　　公共領(lǐng)域</b></p><p>　　SQLite的源代碼位于公共領(lǐng)域。任何核心部分的源代碼都沒(méi)有聲明版權(quán)。（文檔和測(cè)試代碼是另一回事——一部分的文檔和測(cè)試邏輯是由開(kāi)

69、源許可管理的。）所有對(duì)于SQLite核心軟件的貢獻(xiàn)者都已簽署宣誓書(shū)否定任何代碼的版權(quán)利益。這意味著任何人用SQLite的源代碼能夠合法地做他們想做的任何事情。</p><p>　　還有一些簽署了自由協(xié)議的SQL數(shù)據(jù)庫(kù)引擎的源代碼允許被自由地傳播和使用。不過(guò)其它的引擎依舊被著作權(quán)法保護(hù)。SQLite的不同點(diǎn)在于著作權(quán)法根本不適用。</p><p>　　其它SQL數(shù)據(jù)庫(kù)引擎的源代碼文件典型的一

70、般以一段描述你許可查看和拷貝文件權(quán)利的注釋開(kāi)始。SQLite的源代碼不包含許可，因?yàn)樗槐话鏅?quán)管理。取代許可的是，SQLite的源代碼提供一個(gè)信念：</p><p>　　愿你多做好事少做壞事</p><p>　　愿你自我寬恕也寬恕他人</p><p>　　愿你無(wú)私分享，不要獲取的比你付出的更多</p><p><b>　　SQL語(yǔ)言

71、擴(kuò)展</b></p><p>　　SQLite提供了一些增強(qiáng)的SQL語(yǔ)言通常不會(huì)在其它數(shù)據(jù)庫(kù)引擎中出現(xiàn)。EXPLAIN關(guān)鍵字和弱類(lèi)型上面已經(jīng)提過(guò)了。SQLite還提供了諸如REPLACE和ON CONFICT子句，允許用戶控制約束沖突的情況。SQLite支持ATTACH和DETACH命令，它們?cè)试S用戶將多個(gè)相互獨(dú)立的數(shù)據(jù)庫(kù)聯(lián)合起來(lái)完成同一個(gè)查詢。并且SQLite定義了允許用戶添加新SQL功能和排序序列