Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Work on the "quirks.html" page. The page is still incomplete, but I need to divert attention to other issues so I'm checking in partial changes. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
508297d763221d117b94c5dfc85c4647 |
User & Date: | drh 2018-04-03 12:02:01.775 |
Context
2018-04-10
| ||
00:21 | Add the Why SQLite Does Not Use Git document. (check-in: 1b4a90faa0 user: drh tags: trunk) | |
2018-04-03
| ||
12:02 | Work on the "quirks.html" page. The page is still incomplete, but I need to divert attention to other issues so I'm checking in partial changes. (check-in: 508297d763 user: drh tags: trunk) | |
2018-04-02
| ||
15:25 | Updates to the "serverless" page to talk about resent definitions of that word. Add a skeleton for the "quirks" document. (check-in: 1e78d197a1 user: drh tags: trunk) | |
Changes
Changes to pages/quirks.in.
︙ | ︙ | |||
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | <p> This document strives to highlight the principal differences between SQLite and other SQL implementations, as an aid to developers that are porting to or from SQLite or who are trying to build a system that works across multiple database engines. <h1>SQLite Is Embedded, Not Client-Server</h1> <h1>Flexible Typing</h1> <h1>Foreign Key Enforcement Is Off By Default</h1> <h1>PRIMARY KEYs Can Sometimes Contain NULLs</h1> <h1>Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause</h1> <h1>Double-quoted String Literals Are Accepted</h1> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 | <p> This document strives to highlight the principal differences between SQLite and other SQL implementations, as an aid to developers that are porting to or from SQLite or who are trying to build a system that works across multiple database engines. <p> If you are an SQLite user whose has stumbled over some quirk of SQLite that is not mentioned here, please send us an email so that we can document the problem. <h1>SQLite Is Embedded, Not Client-Server</h1> <p> When ever comparing SQLite to other SQL database engines like SQL Server, PostgreSQL, MySQL, or Oracle, it is important first of all to realize the SQLite is not intended as a replacement or competitor to any of those systems. SQLite is [serverless]. There is no separate server process that manages the database. An application interact with the database engine using function calls, not be sending messages to a separate process or thread. <p> The fact that SQLite is embedded and [serverless] instead of being client/server is a feature, not a bug. <p> Client/server databases like MySQL, PostgreSQL, SQL Server, Oracle, and others are an important component of modern systems. These systems solve an important problem. But SQLite solves a different problem. Both SQLite and client/server databases have their role. Developers who are comparing SQLite against other SQL database engines need to clearly understand this distinction. <p> See the [Appropriate Uses For SQLite] document for additional information. <h1>Flexible Typing</h1> <p> SQLite is very flexible with regard to datatypes. <p> Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and purgative. We prefer to say that SQLite is "flexibly typed" and that other SQL databases are "rigidly typed". <p> See the [datatype|Datatypes in SQLite Version 3] document for a detailed discussion of the type system in SQLite. <p> The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of "INTEGER" and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts <b>'1234'</b> into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like <b>'wxyz'</b> into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column. <p> Similarly, SQLite allows you to store a 2000-character string into a column of type VARCHAR(50). Other SQL implementions would either throw an error or truncate the string. SQLite stores the entire 2000-character string with no loss of information and without complaint. <p> Where this ends up causing problems is when developers do some initial coding work using SQLite and get their application working, but then try to convert to another database like PostgreSQL or SQL Server for deployment. If the application is initially taking advantage of SQLite's flexible typing, then it will fail when moved to another database that uses a more rigid and unforgiving type enforcement policy. <p> Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgemental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. <h2>No Separate BOOLEAN Datatype</h2> <p> Unlike most other SQL implementations, SQLite does not have a separate BOOLEAN data type. Instead, TRUE and FALSE are (normally) represented as integers 1 and 0, respectively. This does not seem to cause many problems, as we seldom get complaints about it. But it is important to recognize. <p> Beginning with SQLite [version 3.23.0] ([dateof:3.23.0]), SQLite also recognizes TRUE and FALSE keywords as aliases for integer values 1 and 0, respectively. This provides better compatibility with other SQL implementations. But to retain backwards compatibility, if there are columns named TRUE or FALSE, then the keywords are treated as indentifiers referencing those columns, rather than BOOLEAN literals. <h2>No Separate DATETIME Datatype</h2> <p> SQLite as no DATETIME datatype. Instead, dates and times can be stored in any of these ways: <ul> <li> As a TEXT string in the ISO-8610 format. Example: '2018-04-02 12:13:46'. <li> As an INTEGER number of seconds since 1970 (also known as "unix time"). <li> As a REAL value that is the fractional [https://en.wikipedia.org/wiki/Julian_day|Julian day number]. </ul> <p> The built-in [date and time functions] of SQLite understand date/times in all of the formats above, and can freely change between them. Which format you use, is entirely up to your application. <h1>Foreign Key Enforcement Is Off By Default</h1> <p>SQLite has parsed foreign key constraints for time out of mind, but added the ability to actually enforce those constraints much later, with [version 3.6.19] ([dateof:3.6.19]). By the time foreign key constraint enforcement was added, there where already countless millions of databases in circulation that contained foreign key constraints, some of which were not correct. To avoid breaking those legacy databases, foreign key constraint enforcement is turned off by default in SQLite. <p>Applications can activate foreign key enforcement at run-time using the [PRAGMA foreign_keys] statement. Or, foreign key enforcement can be activated at compile-time using the [-DSQLITE_DEFAULT_FOREIGN_KEYS=1] compile-time option. <h1>PRIMARY KEYs Can Sometimes Contain NULLs</h1> <p> Usually (the exceptions are [INTEGER PRIMARY KEY] tables [WITHOUT ROWID] tables) a PRIMARY KEY in an SQLite table is really the same as a UNIQUE constraint. Due to an historical oversight, the column values of such a PRIMARY KEY are allowed to be NULL. This is a bug, but by the time the problem was discovered there where so many databases in circulation that depended on the bug that the decision was made to support the bugging behavior moving forward. <p> The value of an [INTEGER PRIMARY KEY] column must always be a non-NULL integer. The PRIMARY KEY columns of a [WITHOUT ROWID] table are also required to be non-NULL. <h1>Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause</h1> <p> In most SQL implementations, output columns of an aggregate query may only reference aggregate functions or columns named in the GROUP BY clause. It does not make good sense to reference an ordinary column in an aggregate query because each output row might be composed from two more more rows in the input table(s). <p> SQLite does not impose this restriction. The output columns from an aggregate query can be arbitrary expressions that include columns not found in GROUP BY clause. This feature has two uses: <ol> <li><p> With SQLite (but not any other SQL implementation that we know of) if an aggregate query contains a single min() or max() function, then the values of columns used in the output are taken from the the row where the min() or max() value was achieved. If two or more rows have the same min() or max() value, then the columns values will be chosen arbitrarily from one of those rows. <p> For example to find the highest paid employee: <codeblock> SELECT max(salary), first_name, last_name FROM employee; </codeblock> <p> In the query above, the values for the first_name and last_name columns will correspond to the row that satisfied the max(salary) condition. <li><p> If a query contains no aggregate functions at all, then a GROUP BY clause can be added as a substitute of DISTINCT ON clause. In other words, output rows are filtered so that only one row is shows for each distinct set of values in the GROUP BY clause. If two or more output rows would have otherwise had the same set of values for the GROUP BY columns, then the one of the rows is chosen arbitrarily. </ol> <h1>Does Not Do Full Unicode Case Folding By Default</h1> <p> SQLite does not know about the upper-case/lower-case distinction for all unicode characters. SQL functions like upper() and lower() only work on ASCII characters. There are two reasons for this: <ol> <li> Though stable now, when SQLite was first designed, the rules for unicode case folding where still in flux. That means that the behavior might have changed with each new unicode release, disrupting applications and corrupting indexes in the process. <li> The tables necessary to do full and proper unicode case folding are larger than the whole SQLite library. </ol> <p> Full unicode case folding is supported in SQLite if it is compiled with the [-DSQLITE_ENABLE_ICU] option and linked against the [http://site.icu-project.org/|International Components for Unicode] library. <h1>Double-quoted String Literals Are Accepted</h1> <p> The SQL standard says that one should use double-quotes around identifiers and single-quotes around string literals. <ul> <li> <tt>"this is a legal SQL column name"</tt> <li> <tt>'this is an SQL string literal'</tt> </ul> <p> SQLite accepts both of the above. But, in an effort to be compatible with MySQL 3.x (which was widely popular when SQLite was first being designed) will also use content contained in double-quotes as a string literal if the content does not match any valid identifier. <p> An unfortunate side-effect of this is that a misspelled double-quoted identifier will be interpreted as a string literal, rather than generating an error. Another problem is that this behavior allows developers who are new to the SQL language to continue using double-quoted string literals when they really need to learn to use the correct single-quoted string literal form. <p> In hindsight, we should not have tried to make SQLite accept MySQL 3.x syntax, and should have never allowed double-quoted string literals. However, we continue to support that capability to avoid breaking legacy applications. <h1>Keywords Can Often Be Used As Identifiers</h1> <p> The SQL language is rich in keywords. Most SQL implementations do not allow keywords to be used as identifiers (the names of table or columns) unless they are enclosed in double-quotes. But SQLite is more flexible. Many keywords can be used as identifiers without needing to be quoted, as long as those keywords are used in a context where it is clear that they are intended to be an identifier. <p> For example, the following statement is valid in SQLite: <codeblock> CREATE TABLE union(true INT, with BOOLEAN); </codeblock> <p> The same SQL statement will fail on every other SQL implementation that we know of due to the use of keywords "union", "true", and "with" as identifiers. <h1>Dubious SQL Is Allowed Without Any Error Or Warning</h1> <p> The original implementation of SQLite sought to follow [https://en.wikipedia.org/wiki/Robustness_principle|Postel's Law] which states in part "Be liberal in what you accept". This used to be considered good design - that a system would accept dodgy inputs and try to do the best it could without complaining too much. But lately, people have come to realize that it is sometimes better to be strict in what you accept, so as to more easily find errors in the input. <p> <h1>AUTOINCREMENT Does Not Work The Same As MySQL</h1> |
Changes to pages/whentouse.in.
1 2 3 4 5 6 7 8 | <title>Appropriate Uses For SQLite</title> <h1 align="center">Appropriate Uses For SQLite</h1> <p> SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem. | > | 1 2 3 4 5 6 7 8 9 | <title>Appropriate Uses For SQLite</title> <tcl>hd_keywords {Appropriate Uses For SQLite}</tcl> <h1 align="center">Appropriate Uses For SQLite</h1> <p> SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem. |
︙ | ︙ |