SQLite

Check-in [0eaf430d95]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to the FAQ. (CVS 2705)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0eaf430d9538ece1a3d1300db91f269577a5e028
User & Date: drh 2005-09-17 02:34:05.000
Context
2005-09-17
13:07
Make sure dependencies on the right-hand side of IN operators are checked correctly. Ticket #1433. (CVS 2706) (check-in: 21740794ab user: drh tags: trunk)
02:34
Updates to the FAQ. (CVS 2705) (check-in: 0eaf430d95 user: drh tags: trunk)
2005-09-16
17:16
Do not journal the locking page which doing an autovacuum. Similar to the problem of #1432 except that this one occurs on autovacuum instead of vacuum. An assert() has been added to catch any future incidents of this type. (CVS 2704) (check-in: 5b6dc12b7d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/datatype3.tcl.
1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.10 2004/11/19 11:59:24 danielk1977 Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

|







1
2
3
4
5
6
7
8
set rcsid {$Id: datatype3.tcl,v 1.11 2005/09/17 02:34:05 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

52
53
54
55
56
57
58

59
60
61
62
63
64
65
</UL>
<P>The storage class of a value that is the result of an SQL scalar
operator depends on the outermost operator of the expression.
User-defined functions may return values with any storage class. It
is not generally possible to determine the storage class of the
result of an expression at compile time.</P>


<h3>2. Column Affinity</h3>

<p>
In SQLite version 3, the type of a value is associated with the value
itself, not with the column or variable in which the value is stored.
(This is sometimes called
<a href="http://www.cliki.net/manifest%20type%20system">







>







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
</UL>
<P>The storage class of a value that is the result of an SQL scalar
operator depends on the outermost operator of the expression.
User-defined functions may return values with any storage class. It
is not generally possible to determine the storage class of the
result of an expression at compile time.</P>

<a name="affinity">
<h3>2. Column Affinity</h3>

<p>
In SQLite version 3, the type of a value is associated with the value
itself, not with the column or variable in which the value is stored.
(This is sometimes called
<a href="http://www.cliki.net/manifest%20type%20system">
Changes to www/faq.tcl.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.30 2005/08/31 02:46:21 drh Exp $}
source common.tcl
header {SQLite Frequently Asked Questions</title>}

set cnt 1
proc faq {question answer} {
  set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
  incr ::cnt
}

#############
# Enter questions and answers here.

faq {
  How do I create an AUTOINCREMENT field.
} {
  <p>Short answer: A column declared INTEGER PRIMARY KEY will
  autoincrement.</p>

  <p>Here is the long answer:
  Beginning with version SQLite 2.3.4, If you declare a column of
  a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL
  into that column of the table, the NULL is automatically converted
  into an integer which is one greater than the largest value of that
  column over all other rows in the table, or 1 if the table is empty.


  For example, suppose you have a table like this:
<blockquote><pre>
CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INTEGER
);
</pre></blockquote>
  <p>With this table, the statement</p>
<blockquote><pre>
INSERT INTO t1 VALUES(NULL,123);
</pre></blockquote>
  <p>is logically equivalent to saying:</p>
<blockquote><pre>
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
</pre></blockquote>
  <p>For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into
  an INTEGER PRIMARY KEY column, the NULL will be changed to a unique
  integer, but it will a semi-random integer.  Unique keys generated this
  way will not be sequential.  For SQLite version 2.3.4 and beyond, the
  unique keys will be sequential until the largest key reaches a value
  of 2147483647.  That is the largest 32-bit signed integer and cannot
  be incremented, so subsequent insert attempts will revert to the
  semi-random key generation algorithm of SQLite version 2.3.3 and
  earlier.</p>

  <p>Beginning with version 2.2.3, there is a new API function named

  <b>sqlite3_last_insert_rowid()</b> which will return the integer key
  for the most recent insert operation.  See the API documentation for
  details.</p>










  <p>SQLite version 3.0 expands the size of the rowid to 64 bits.</p>
}

faq {
  What datatypes does SQLite support?
} {
  <p>SQLite ignores
  the datatype information that follows the column name in CREATE TABLE.
  You can put any type of data you want
  into any column, without regard to the declared datatype of that column.
  </p>

  <p>An exception to this rule is a column of type INTEGER PRIMARY KEY.
  Such columns must hold an integer.  An attempt to put a non-integer
  value into an INTEGER PRIMARY KEY column will generate an error.</p>

  <p>There is a page on <a href="datatypes.html">datatypes in SQLite
  version 2.8</a>
  and another for <a href="datatype3.html">version 3.0</a>
  that explains this concept further.</p>
}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite does not enforce data type
  constraints.  Any data can be
  inserted into any column.  You can put arbitrary length strings into
  integer columns, floating point numbers in boolean columns, or dates
  in character columns.  The datatype you assign to a column in the
  CREATE TABLE command does not restrict what data can be put into
  that column.  Every column is able to hold
  an arbitrary length string.  (There is one exception: Columns of
  type INTEGER PRIMARY KEY may only hold an integer.  An error will result

  if you try to put anything other than an integer into an
  INTEGER PRIMARY KEY column.)</p>

  <p>The datatype does effect how values are compared, however.  For
  columns with a numeric type (such as "integer") any string that looks
  like a number is treated as a number for comparison and sorting purposes.
  Consider these two command sequences:</p>

  <blockquote><pre>
CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0');               INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0');             INSERT INTO t2 VALUES(0.0);
</pre></blockquote>

  <p>In the sequence on the left, the second insert will fail.  In this case,
  the strings '0' and '0.0' are treated as numbers since they are being 
  inserted into a numeric column and 0==0.0 which violates the uniqueness
  constraint.  But the second insert in the right-hand sequence works.  In
  this case, the constants 0 and 0.0 are treated a strings which means that
  they are distinct.</p>

  <p>There is a page on <a href="datatypes.html">datatypes in SQLite
  version 2.8</a>
  and another for <a href="datatype3.html">version 3.0</a>
  that explains this concept further.</p>
}

faq {
  Why does SQLite think that the expression '0'=='00' is TRUE?
} {
  <p>As of version 2.7.0, it doesn't.</p>

  <p>But if one of the two values being compared is stored in a column that
  has a numeric type, the the other value is treated as a number, not a
  string and the result succeeds.  For example:</p>

<blockquote><pre>
CREATE TABLE t3(a INTEGER, b TEXT);
INSERT INTO t3 VALUES(0,0);
SELECT count(*) FROM t3 WHERE a=='00';
</pre></blockquote>

  <p>The SELECT in the above series of commands returns 1.  The "a" column
  is numeric so in the WHERE clause the string '00' is converted into a
  number for comparison against "a".  0==00 so the test is true.  Now
  consider a different SELECT:</p>

<blockquote><pre>
SELECT count(*) FROM t3 WHERE b=='00';
</pre></blockquote>

  <p>In this case the answer is 0.  B is a text column so a text comparison
  is done against '00'.  '0'!='00' so the WHERE clause returns FALSE and
  the count is zero.</p>

  <p>There is a page on <a href="datatypes.html">datatypes in SQLite
  version 2.8</a>
  and another for <a href="datatype3.html">version 3.0</a>
  that explains this concept further.</p>
}

faq {
  Why doesn't SQLite allow me to use '0' and '0.0' as the primary
  key on two different rows of the same table?
} {
  <p>Your primary key must have a numeric type.  Change the datatype of



|



















|
|



>
>















<
<
<
<
<
<
<
<
<

|
>
|
|
<

>
>
>
>
>
>
>
>
>
|





<
<
<
<
<
<
<
<
<
<
<
<
|
<













|
>



|
<
|
|
|
<
<
<
<
<
|
<
<
<
<
<
<
|
<
<
|
|





|
|
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.31 2005/09/17 02:34:05 drh Exp $}
source common.tcl
header {SQLite Frequently Asked Questions</title>}

set cnt 1
proc faq {question answer} {
  set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
  incr ::cnt
}

#############
# Enter questions and answers here.

faq {
  How do I create an AUTOINCREMENT field.
} {
  <p>Short answer: A column declared INTEGER PRIMARY KEY will
  autoincrement.</p>

  <p>Here is the long answer:
  If you declare a column of a table to be INTEGER PRIMARY KEY, then
  whenever you insert a NULL
  into that column of the table, the NULL is automatically converted
  into an integer which is one greater than the largest value of that
  column over all other rows in the table, or 1 if the table is empty.
  (If the largest possible integer key, 9223372036854775807, then an
  unused key value is chosen at random.)
  For example, suppose you have a table like this:
<blockquote><pre>
CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INTEGER
);
</pre></blockquote>
  <p>With this table, the statement</p>
<blockquote><pre>
INSERT INTO t1 VALUES(NULL,123);
</pre></blockquote>
  <p>is logically equivalent to saying:</p>
<blockquote><pre>
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
</pre></blockquote>










  <p>There is a new API function named
  <a href="capi3ref.html#sqlite3_last_insert_rowid">
  sqlite3_last_insert_rowid()</a> which will return the integer key
  for the most recent insert operation.</p>


  <p>Note that the integer key is one greater than the largest
  key that was in the table just prior to the insert.  The new key
  will be unique over all keys currently in the table, but it might
  overlap with keys that have been previously deleted from the
  table.  To create keys that are unique over the lifetime of the
  table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY
  declaration.  Then the key chosen will be one more than than the
  largest key that has ever existed in that table.  If the largest
  possible key has previously existed in that table, then the INSERT
  will fail with an SQLITE_FULL error code.</p>
}

faq {
  What datatypes does SQLite support?
} {












  <p>See <a href="datatype3.html">http://www.sqlite.org/datatype3.html</a>.</p>

}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite does not enforce data type
  constraints.  Any data can be
  inserted into any column.  You can put arbitrary length strings into
  integer columns, floating point numbers in boolean columns, or dates
  in character columns.  The datatype you assign to a column in the
  CREATE TABLE command does not restrict what data can be put into
  that column.  Every column is able to hold
  an arbitrary length string.  (There is one exception: Columns of
  type INTEGER PRIMARY KEY may only hold a 64-bit signed integer.
  An error will result
  if you try to put anything other than an integer into an
  INTEGER PRIMARY KEY column.)</p>

  <p>But SQLite does use the declared type of a column as a hint

  that you prefer values in that format.  So, for example, if a
  column is of type INTEGER and you try to insert a string into
  that column, SQLite will attempt to convert the string into an





  integer.  If it can, it inserts the integer instead.  If not,






  it inserts the string.  This feature is sometimes


  call <a href="datatype3.html#affinity">type or column affinity</a>.
  </p>
}

faq {
  Why does SQLite think that the expression '0'=='00' is TRUE?
} {
  <p>As of version 2.7.0, it doesn't.  See the document on
  <a href="datatype3.html">datatypes in SQLite version 3</a>



  for details.</p>























}

faq {
  Why doesn't SQLite allow me to use '0' and '0.0' as the primary
  key on two different rows of the same table?
} {
  <p>Your primary key must have a numeric type.  Change the datatype of
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
faq {
  Can multiple applications or multiple instances of the same
  application access a single database file at the same time?
} {
  <p>Multiple processes can have the same database open at the same
  time.  Multiple processes can be doing a SELECT
  at the same time.  But only one process can be making changes to
  the database at once.</p>


  <p>Win95/98/ME lacks support for reader/writer locks in the operating
  system.  Prior to version 2.7.0, this meant that under windows you
  could only have a single process reading the database at one time.
  This problem was resolved in version 2.7.0 by implementing a user-space
  probabilistic reader/writer locking strategy in the windows interface
  code file.  Windows
  now works like Unix in allowing multiple simultaneous readers.</p>

  <p>The locking mechanism used to control simultaneous access might
  not work correctly if the database file is kept on an NFS filesystem.
  This is because file locking is broken on some NFS implementations.
  You should avoid putting SQLite database files on NFS if multiple
  processes might try to access the file at the same time.  On Windows,
  Microsoft's documentation says that locking may not work under FAT
  filesystems if you are not running the Share.exe daemon.  People who
  have a lot of experience with Windows tell me that file locking of
  network files is very buggy and is not dependable.  If what they
  say is true, sharing an SQLite database between two or more Windows
  machines might cause unexpected problems.</p>


  <p>Locking in SQLite is very course-grained.  SQLite locks the






  entire database.  Big database servers (PostgreSQL, Oracle, etc.)
  generally have finer grained locking, such as locking on a single




  table or a single row within a table.  If you have a massively

  parallel database application, you should consider using a big database

  server instead of SQLite.</p>

  <p>When SQLite tries to access a file that is locked by another
  process, the default behavior is to return SQLITE_BUSY.  You can
  adjust this behavior from C code using the <b>sqlite3_busy_handler()</b> or

  <b>sqlite3_busy_timeout()</b> API functions.  See the API documentation
  for details.</p>

  <p>If two or more processes have the same database open and one
  process creates a new table or index, the other processes might
  not be able to see the new table right away.  You might have to
  get the other processes to close and reopen their connection to
  the database before they will be able to see the new table.</p>
}

faq {
  Is SQLite threadsafe?
} {
  <p>Yes.  Sometimes.  In order to be thread-safe, SQLite must be compiled
  with the THREADSAFE preprocessor macro set to 1.  In the default
  distribution, the windows binaries are compiled to be threadsafe but
  the linux binaries are not.  If you want to change this, you'll have to
  recompile.</p>

  <p>"Threadsafe" in the previous paragraph means that two or more threads
  can run SQLite at the same time on different "<b>sqlite3</b>" structures
  returned from separate calls to <b>sqlite3_open()</b>.  It is never safe

  to use the same <b>sqlite3</b> structure pointer simultaneously in two
  or more threads.</p>

  <p>An <b>sqlite3</b> structure can only be used in the same thread

  that called <b>sqlite3_open</b> to create it.  You cannot open a
  database in one thread then pass the handle off to another thread for
  it to use.  This is due to limitations (bugs?) in many common threading
  implementations such as on RedHat9.</p>

  <p>Note that if two or more threads have the same database open and one
  thread creates a new table or index, the other threads might
  not be able to see the new table right away.  You might have to
  get the other threads to close and reopen their connection to
  the database before they will be able to see the new table.</p>

  <p>Under UNIX, you should not carry an open SQLite database across
  a fork() system call into the child process.  Problems will result
  if you do.</p>
}

faq {







|

>
|
<
<
<
<
<
<
|
|

|









>
|
>
>
>
>
>
>
|
|
>
>
>
>
|
>
|
>
|



|
>
|
|
<
<
<
<
<
<













|
>
|



>
|


|
|
<
|
|
<
<







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
faq {
  Can multiple applications or multiple instances of the same
  application access a single database file at the same time?
} {
  <p>Multiple processes can have the same database open at the same
  time.  Multiple processes can be doing a SELECT
  at the same time.  But only one process can be making changes to
  the database at an given moement in time, however.</p>

  <p>SQLite uses reader/writer locks to control access to the database.
  (Under Win95/98/ME which lacks support for reader/writer locks, a






  probabilistic simulation is used instead.)
  But use caution: this locking mechanism might
  not work correctly if the database file is kept on an NFS filesystem.
  This is because fcntl() file locking is broken on many NFS implementations.
  You should avoid putting SQLite database files on NFS if multiple
  processes might try to access the file at the same time.  On Windows,
  Microsoft's documentation says that locking may not work under FAT
  filesystems if you are not running the Share.exe daemon.  People who
  have a lot of experience with Windows tell me that file locking of
  network files is very buggy and is not dependable.  If what they
  say is true, sharing an SQLite database between two or more Windows
  machines might cause unexpected problems.</p>

  <p>We are aware of no other <i>embedded</i> SQL database engine that
  supports as much concurrancy as SQLite.  SQLite allows multiple processes
  to have the database file open at once, and for multiple processes to
  read the database at once.  When any process wants to write, it must
  lock the entire database file for the duration of its update.  But that
  normally only takes a few milliseconds.  Other processes just wait on
  the writer to finish then continue about their business.  Other embedded
  SQL database engines typically only allow a single process to connect to
  the database at once.</p>

  <p>However, client/server database engines (such as PostgreSQL, MySQL,
  or Oracle) usually support a higher level of concurrency and allow
  multiple processes to be writing to the same database at the same time.
  This is possible in a client/server database because there is always a
  single well-controlled server process available to coordinate access.
  If your application has a need for a lot of concurrency, then you should
  consider using a client/server database.  But experience suggests that
  most applications need much less concurrency than their designers imagine.
  </p>

  <p>When SQLite tries to access a file that is locked by another
  process, the default behavior is to return SQLITE_BUSY.  You can
  adjust this behavior from C code using the 
  <a href="capi3ref#sqlite3_busy_handler">sqlite3_busy_handler()</a> or
  <a href="capi3ref#sqlite3_busy_timeout">sqlite3_busy_timeout()</a>
  API functions.</p>






}

faq {
  Is SQLite threadsafe?
} {
  <p>Yes.  Sometimes.  In order to be thread-safe, SQLite must be compiled
  with the THREADSAFE preprocessor macro set to 1.  In the default
  distribution, the windows binaries are compiled to be threadsafe but
  the linux binaries are not.  If you want to change this, you'll have to
  recompile.</p>

  <p>"Threadsafe" in the previous paragraph means that two or more threads
  can run SQLite at the same time on different "<b>sqlite3</b>" structures
  returned from separate calls to 
  <a href="capi3ref#sqlite3_open">sqlite3_open()</a>.  It is never safe
  to use the same <b>sqlite3</b> structure pointer in two
  or more threads.</p>

  <p>An <b>sqlite3</b> structure can only be used in the same thread
  that called <a href="capi3ref#sqlite3_open">sqlite3_open</a> to create it.
  You cannot open a
  database in one thread then pass the handle off to another thread for
  it to use.  This is due to limitations (bugs?) in many common threading
  implementations such as on RedHat9.  There may be ways to work around
  these limitations, but they are complex and exceedingly difficult to

  test for correctness.  For that reason, SQLite currently takes the safe
  approach and disallows the sharing of handles among threads.</p>



  <p>Under UNIX, you should not carry an open SQLite database across
  a fork() system call into the child process.  Problems will result
  if you do.</p>
}

faq {
317
318
319
320
321
322
323
324

325


326



327


328


329




330
331



332
333
334
335

336
337
338
339
340
341
342
343
344


345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368




369
370
371
372
373
374
375
376
ORDER BY name
</pre></blockquote>
}

faq {
  Are there any known size limits to SQLite databases?
} {
  <p>As of version 2.7.4, 

  SQLite can handle databases up to 2<sup>41</sup> bytes (2 terabytes)


  in size on both Windows and Unix.  Older version of SQLite



  were limited to databases of 2<sup>31</sup> bytes (2 gigabytes).</p>





  <p>SQLite version 2.8 limits the amount of data in one row to 




  1 megabyte.  SQLite version 3.0 has no limit on the amount of
  data that can be stored in a single row.



  </p>

  <p>The names of tables, indices, view, triggers, and columns can be
  as long as desired.  However, the names of SQL functions (as created

  by the <a href="c_interface.html#cfunc">sqlite3_create_function()</a> API)
  may not exceed 255 characters in length.</p>
}

faq {
  What is the maximum size of a VARCHAR in SQLite?
} {
  <p>SQLite does not enforce datatype constraints.
  A VARCHAR column can hold as much data as you care to put in it.</p>


}

faq {
  Does SQLite support a BLOB type?
} {
  <p>SQLite version 3.0 lets you puts BLOB data into any column, even
  columns that are declared to hold some other type.</p>

  <p>SQLite version 2.8 will store any text data without embedded
  '\000' characters.  If you need to store BLOB data in SQLite version
  2.8 you'll want to encode that data first.
  There is a source file named 
  "<b>src/encode.c</b>" in the SQLite version 2.8 distribution that contains
  implementations of functions named "<b>sqlite_encode_binary()</b>
  and <b>sqlite_decode_binary()</b> that can be used for converting
  binary data to ASCII and back again, if you like.</p>

 
}

faq {
  How do I add or delete columns from an existing table in SQLite.
} {
  <p>SQLite does yes not support the "ALTER TABLE" SQL command.  If you




  what to change the structure of a table, you have to recreate the
  table.  You can save existing data to a temporary table, drop the
  old table, create the new table, then copy the data back in from
  the temporary table.</p>

  <p>For example, suppose you have a table named "t1" with columns
  names "a", "b", and "c" and that you want to delete column "c" from
  this table.  The following steps illustrate how this could be done:







|
>
|
>
>
|
>
>
>
|
>
>

>
>
|
>
>
>
>
|
<
>
>
>




>
|
|





|
|
>
>





|

<
<
<
<
<
<
<
<
<
<
<





|
>
>
>
>
|







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
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322











323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
ORDER BY name
</pre></blockquote>
}

faq {
  Are there any known size limits to SQLite databases?
} {
  <p>A database is limited in size to 2 tibibytes (2<sup>41</sup> bytes).
  That is a theoretical limitation.  In practice, you should try to keep
  your SQLite databases below 100 gigabytes to avoid performance problems.
  If you need to store 100 gigabytes or more in a database, consider using
  an enterprise database engine which is designed for that purpose.</p>

  <p>The theoretical limit on the number of rows in a table is
  2<sup>64</sup>-1, though obviously you will run into the file size
  limitation prior to reaching the row limit.  A single row can hold
  up to 2<sup>30</sup> bytes of data in the current implementation.  The
  underlying file format supports row sizes up to about 2<sup>62</sup> bytes.
  </p>

  <p>There are probably limits on the number of tables or indices or
  the number of columns in a table or index, but nobody is sure what
  those limits are.  In practice, SQLite must read and parse the original
  SQL of all table and index declarations everytime a new database file
  is opened, so for the best performance of
  <a href="capi3ref.html#sqlite3_open">sqlite3_open()</a> it is best
  to keep down the number of declared tables.   Likewise, though there
  is no limit on the number of columns in a table, more than a few hundred

  seems extreme.  Only the first 31 columns of a table are candidates for
  certain optimizations.  You can put as many columns in an index as you like
  but indexes with more than 30 columns will not be used to optimize queries.
  </p>

  <p>The names of tables, indices, view, triggers, and columns can be
  as long as desired.  However, the names of SQL functions (as created
  by the 
  <a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a>
  API) may not exceed 255 characters in length.</p>
}

faq {
  What is the maximum size of a VARCHAR in SQLite?
} {
  <p>SQLite does not enforce the length of a VARCHAR.  You can declare
  a VARCHAR(10) and SQLite will be happy to let you put 500 characters
  in it.  And it will keep all 500 characters intact - it never truncates.
  </p>
}

faq {
  Does SQLite support a BLOB type?
} {
  <p>SQLite versions 3.0 and leter let you puts BLOB data into any column, even
  columns that are declared to hold some other type.</p>











}

faq {
  How do I add or delete columns from an existing table in SQLite.
} {
  <p>SQLite has limited 
  <a href="lang_altertable.html">ALTER TABLE</a> support that you can
  use to add a column to the end of a table or to change the name of
  a table.  
  If you what make more complex changes the structure of a table,
  you will have to recreate the
  table.  You can save existing data to a temporary table, drop the
  old table, create the new table, then copy the data back in from
  the temporary table.</p>

  <p>For example, suppose you have a table named "t1" with columns
  names "a", "b", and "c" and that you want to delete column "c" from
  this table.  The following steps illustrate how this could be done:
394
395
396
397
398
399
400

401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417

418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438


439
440



441

442
443
444
445
446
447
448
449

450
451
452
453
454
455
456
457
458



459
460
461
462
463
464
465
} {
  <p>No.  When you delete information from an SQLite database, the
  unused disk space is added to an internal "free-list" and is reused
  the next time you insert data.  The disk space is not lost.  But
  neither is it returned to the operating system.</p>

  <p>If you delete a lot of data and want to shrink the database file,

  run the VACUUM command (version 2.8.1 and later).  VACUUM will reconstruct
  the database from scratch.  This will leave the database with an empty
  free-list and a file that is minimal in size.  Note, however, that the
  VACUUM can take some time to run (around a half second per megabyte
  on the Linux box where SQLite is developed) and it can use up to twice
  as much temporary disk space as the original file while it is running.
  </p>

  <p>As of SQLite version 3.1, an alternative to using the VACUUM command
  is auto-vacuum mode, enabled using the 
  <a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
}

faq {
  Can I use SQLite in my commercial product without paying royalties?
} {
  <p>Yes.  SQLite is in the public domain.  No claim of ownership is made

  to any part of the code.  You can do anything you want with it.</p>
}

faq {
  How do I use a string literal that contains an embedded single-quote (')
  character?
} {
  <p>The SQL standard specifies that single-quotes in strings are escaped
  by putting two single quotes in a row.  SQL works like the Pascal programming
  language in the regard.  SQLite follows this standard.  Example:
  </p>

  <blockquote><pre>
    INSERT INTO xyz VALUES('5 O''clock');
  </pre></blockquote>
}

faq {What is an SQLITE_SCHEMA error, and why am I getting one?} {
  <p>In version 3 of SQLite, an SQLITE_SCHEMA error is returned when a 
  prepared SQL statement is no longer valid and cannot be executed.
  When this occurs, the statement must be recompiled from SQL using 


  the sqlite3_prepare() API. In SQLite 3, an SQLITE_SCHEMA error can
  only occur when using the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize()



  API to execute SQL, not when using the sqlite3_exec(). This was not

  the case in version 2.</p>

  <p>The most common reason for a prepared statement to become invalid
  is that the schema of the database was modified after the SQL was 
  prepared (possibly by another process).  The other reasons this can 
  happen are:</p> 
  <ul>
  <li>A database was DETACHed.

  <li>A user-function definition was deleted or changed.
  <li>A collation sequence definition was deleted or changed.
  <li>The authorization function was changed.
  </ul>

  <p>In all cases, the solution is to recompile the statement from SQL
  and attempt to execute it again. Because a prepared statement can be
  invalidated by another process changing the database schema, all code
  that uses the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize()



  API should be prepared to handle SQLITE_SCHEMA errors. An example
  of one approach to this follows:</p>

  <blockquote><pre>

    int rc;
    sqlite3_stmt *pStmt;







>
|















|
>


















|


>
>
|
|
>
>
>
|
>







|
>








|
>
>
>







358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
} {
  <p>No.  When you delete information from an SQLite database, the
  unused disk space is added to an internal "free-list" and is reused
  the next time you insert data.  The disk space is not lost.  But
  neither is it returned to the operating system.</p>

  <p>If you delete a lot of data and want to shrink the database file,
  run the <a href="lang_vacuum.html">VACUUM</a> command.
  VACUUM will reconstruct
  the database from scratch.  This will leave the database with an empty
  free-list and a file that is minimal in size.  Note, however, that the
  VACUUM can take some time to run (around a half second per megabyte
  on the Linux box where SQLite is developed) and it can use up to twice
  as much temporary disk space as the original file while it is running.
  </p>

  <p>As of SQLite version 3.1, an alternative to using the VACUUM command
  is auto-vacuum mode, enabled using the 
  <a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
}

faq {
  Can I use SQLite in my commercial product without paying royalties?
} {
  <p>Yes.  SQLite is in the 
  <a href="copyright.html">public domain</a>.  No claim of ownership is made
  to any part of the code.  You can do anything you want with it.</p>
}

faq {
  How do I use a string literal that contains an embedded single-quote (')
  character?
} {
  <p>The SQL standard specifies that single-quotes in strings are escaped
  by putting two single quotes in a row.  SQL works like the Pascal programming
  language in the regard.  SQLite follows this standard.  Example:
  </p>

  <blockquote><pre>
    INSERT INTO xyz VALUES('5 O''clock');
  </pre></blockquote>
}

faq {What is an SQLITE_SCHEMA error, and why am I getting one?} {
  <p>An SQLITE_SCHEMA error is returned when a 
  prepared SQL statement is no longer valid and cannot be executed.
  When this occurs, the statement must be recompiled from SQL using 
  the 
  <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a> API.
  In SQLite version 3, an SQLITE_SCHEMA error can
  only occur when using the 
  <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>/<a
  href="capi3ref.html#sqlite3_step">sqlite3_step()</a>/<a
  href="capi3ref.html#sqlite3_finalize">sqlite3_finalize()</a>
  API to execute SQL, not when using the
  <a href="capi3ref.html#sqlite3_exec">sqlite3_exec()</a>. This was not
  the case in version 2.</p>

  <p>The most common reason for a prepared statement to become invalid
  is that the schema of the database was modified after the SQL was 
  prepared (possibly by another process).  The other reasons this can 
  happen are:</p> 
  <ul>
  <li>A database was <a href="lang_detach.html">DETACH</a>ed.
  <li>The database was <a href="lang_vacuum.html">VACUUM</a>ed
  <li>A user-function definition was deleted or changed.
  <li>A collation sequence definition was deleted or changed.
  <li>The authorization function was changed.
  </ul>

  <p>In all cases, the solution is to recompile the statement from SQL
  and attempt to execute it again. Because a prepared statement can be
  invalidated by another process changing the database schema, all code
  that uses the
  <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a>/<a
  href="capi3ref.html#sqlite3_step">sqlite3_step()</a>/<a
  href="capi3ref.html#sqlite3_finalize">sqlite3_finalize()</a>
  API should be prepared to handle SQLITE_SCHEMA errors. An example
  of one approach to this follows:</p>

  <blockquote><pre>

    int rc;
    sqlite3_stmt *pStmt;
492
493
494
495
496
497
498

499

500
501
502
503
504
505
506
  SQLite uses) is 9.949999999999999289457264239899814128875732421875.
  So when you type "9.95", SQLite really understands the number to be
  the much longer value shown above.  And that value rounds down.</p>

  <p>This kind of problem comes up all the time when dealing with
  floating point binary numbers.  The general rule to remember is
  that most fractional numbers that have a finite representation in decimal

  do not have a finite representation in binary.  And so they are

  approximated using the closest binary number available.  That
  approximation is usually very close, but it will be slightly off
  and in some cases can cause your results to be a little different
  from what you might expect.</p>
}

# End of questions and answers.







>
|
>







468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
  SQLite uses) is 9.949999999999999289457264239899814128875732421875.
  So when you type "9.95", SQLite really understands the number to be
  the much longer value shown above.  And that value rounds down.</p>

  <p>This kind of problem comes up all the time when dealing with
  floating point binary numbers.  The general rule to remember is
  that most fractional numbers that have a finite representation in decimal
  (a.k.a "base-10")
  do not have a finite representation in binary (a.k.a "base-2").
  And so they are
  approximated using the closest binary number available.  That
  approximation is usually very close, but it will be slightly off
  and in some cases can cause your results to be a little different
  from what you might expect.</p>
}

# End of questions and answers.