Documentation Source Text

Check-in [eb122dda42]
Login

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

Overview
Comment:Update the FAQ.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eb122dda421700e8960eed56befb81c01d5ae269
User & Date: drh 2014-04-21 13:12:24.415
Context
2014-04-21
18:20
Update the command-line shell documentation with a discussion of CSV import and export. (check-in: 070bfddab9 user: drh tags: trunk)
13:12
Update the FAQ. (check-in: eb122dda42 user: drh tags: trunk)
2014-04-05
12:04
Fix a typo on the 3.8.4.3 news page. (check-in: 7401266cf0 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/faq.in.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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 is in use 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>







|





|
|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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

  <p>Longer 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.
  Or, if the largest existing integer key 9223372036854775807 is in use 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>
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
  REAL, TEXT, BLOB, or as NULL.
}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite uses [dynamic typing]. 
  It 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>







|
|
|
|







63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
  REAL, TEXT, BLOB, or as NULL.
}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite uses [dynamic typing]. 
  It does not enforce data type  constraints.  Data of any type can
  (usually) 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>
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
  with the SQLITE_THREADSAFE preprocessor macro set to 1.  Both the Windows
  and Linux precompiled binaries in the distribution are compiled this way.
  If you are unsure if the SQLite library you are linking against is compiled
  to be threadsafe you can call the [sqlite3_threadsafe()]
  interface to find out.
  </p>

  <p>Prior to [version 3.3.1],
  an <b>sqlite3</b> structure could only be used in the same thread
  that called [sqlite3_open()] to create it.
  You could not open a
  database in one thread then pass the handle off to another thread for
  it to use.  This was due to limitations (bugs?) in many common threading
  implementations such as on RedHat9.  Specifically, an fcntl() lock
  created by one thread cannot be removed or modified by a different
  thread on the troublesome systems.  And since SQLite uses fcntl()
  locks heavily for concurrency control, serious problems arose if you 
  start moving database connections across threads.</p>

  <p>The restriction on moving database connections across threads
  was relaxed somewhat in [version 3.3.1].  With that and subsequent
  versions, it is safe to move a connection handle across threads
  as long as the connection is not holding any fcntl() locks.  You
  can safely assume that no locks are being held if no
  transaction is pending and all 
  [sqlite3_stmt|statements] have been [sqlite3_finalize|finalized].</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 {
  How do I list all tables/indices contained in an SQLite database
} {
  <p>If you are running the <b>sqlite3</b> command-line access program
  you can type "<b>.tables</b>" to get a list of all tables.  Or you







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


|
<







165
166
167
168
169
170
171
172
173






174

175
176





177

178
179
180

181
182
183
184
185
186
187
  with the SQLITE_THREADSAFE preprocessor macro set to 1.  Both the Windows
  and Linux precompiled binaries in the distribution are compiled this way.
  If you are unsure if the SQLite library you are linking against is compiled
  to be threadsafe you can call the [sqlite3_threadsafe()]
  interface to find out.
  </p>

  <p>SQLite is threadsafe because it uses mutexes to serialize
  access to common data structures.  However, the work of acquiring and






  releasing these mutexes will slow SQLite down slightly.  Hence, if you

  do not need SQLite to be threadsafe, you should disable the mutexes
  for maximum performance.  See the [threading mode] documentation for





  additional information.</p>


  <p>Under Unix, you should not carry an open SQLite database across
  a fork() system call into the child process.</p>

}

faq {
  How do I list all tables/indices contained in an SQLite database
} {
  <p>If you are running the <b>sqlite3</b> command-line access program
  you can type "<b>.tables</b>" to get a list of all tables.  Or you
259
260
261
262
263
264
265
266
267



268
269
270
271
272
273
274
275

276
277
278
279
280
281
282
  the limits of SQLite.</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 later allow you to store BLOB data in 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







|
|
>
>
>






|
|
>







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
  the limits of SQLite.</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 store a 500-million character
  string there.  And it will keep all 500-million characters intact.
  Your content is never truncated.  SQLite understands the column type
  of "VARCHAR(<i>N</i>)" to be the same as "TEXT", regardless of the value
  of <i>N</i>.
  </p>
}

faq {
  Does SQLite support a BLOB type?
} {
  <p>SQLite allows you to store BLOB data in any 
  column, even columns that are declared to hold some other type.
  BLOBs can even be used as PRIMARY KEYs.</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
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
  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?
} {







|
<



|







305
306
307
308
309
310
311
312

313
314
315
316
317
318
319
320
321
322
323
  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 and it can use up to twice

  as much temporary disk space as the original file while it is running.
  </p>

  <p>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?
} {
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
  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>
}

faq {
  I get hundreds of compiler warnings when I compile SQLite.
  Isn't this a problem?  Doesn't it indicate poor code quality?
} {
  <p>Quality assurance in SQLite is done using 
  [test coverage | full-coverage testing],
  not by compiler warnings or other static code analysis tools.
  In other words, we verify that SQLite actually gets the
  correct answer, not that it merely satisfies stylistic constraints.







|







381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
  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>
}

faq {
  I get some compiler warnings when I compile SQLite.
  Isn't this a problem?  Doesn't it indicate poor code quality?
} {
  <p>Quality assurance in SQLite is done using 
  [test coverage | full-coverage testing],
  not by compiler warnings or other static code analysis tools.
  In other words, we verify that SQLite actually gets the
  correct answer, not that it merely satisfies stylistic constraints.
427
428
429
430
431
432
433
434
435

436
437
438
439
440
441
442
443
444

  <p>We also run SQLite using [http://valgrind.org | Valgrind]
  on Linux and verify that it detects no problems.</p>

  <p>Some people say that we should eliminate all warnings because
  benign warnings mask real warnings that might arise in future changes.
  This is true enough.  But in reply, the developers observe that all
  warnings have already been fixed in the
  compilers used for SQLite development (various versions of GCC).

  Compiler warnings only arise from compilers that the developers do
  not use on a daily basis (Ex: MSVC).</p>
}

faq {
  Case-insensitive matching of Unicode characters does not work.
} {
  The default configuration of SQLite only supports case-insensitive
  comparisons of ASCII characters.  The reason for this is that doing







|
|
>
|
|







416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434

  <p>We also run SQLite using [http://valgrind.org | Valgrind]
  on Linux and verify that it detects no problems.</p>

  <p>Some people say that we should eliminate all warnings because
  benign warnings mask real warnings that might arise in future changes.
  This is true enough.  But in reply, the developers observe that all
  warnings have already been fixed in the builds
  used for SQLite development (various versions of GCC, MSVC,
  and clang).
  Compiler warnings usually only arise from compilers or compile-time 
  options that the SQLite developers to not use themselves.</p>
}

faq {
  Case-insensitive matching of Unicode characters does not work.
} {
  The default configuration of SQLite only supports case-insensitive
  comparisons of ASCII characters.  The reason for this is that doing
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537

538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558

559
560
561
562
563
564
565
566
567
568
569
570
  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by millions of users.</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  Details
  can be found in the discussions on the 
  <a href="atomiccommit.html">atomic commit</a> and 
  <a href="lockingv3.html">locking</a> support in SQLite
  as well as in the mailing list archives.</p>


  <p>Your can use <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> 
  to do a thorough but time intensive test of the database integrity.</p>

  <p>Your can use <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> to do a faster 
  but less thorough test of the database integrity.</p>

  <p>Depending how badly your database is corrupted, you may be able to 
  recover some of the data by using the CLI to dump the schema and contents
  to a file and then recreate.  Unfortunately, once humpty-dumpty falls off 
  the wall, it is generally not possible to put him back together again.</p>
}

faq {
  Does SQLite support foreign keys?
} {
  <p>
    As of version 3.6.19, SQLite supports [foreign key constraints].

  <p>
    Prior versions of SQLite parsed foreign key constraints, but did not

    enforce them. The equivalent functionality could be implemented using
    [CREATE TRIGGER|SQL triggers]. Versions 3.6.12 and later of the SQLite
    shell tool provided the ".genfkey" command to generate such triggers
    automatically. The <a href="http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README">
    readme</a> for the genfkey utility contains more information.
}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work







|


|
<
|
<
<
>

















|
|
<
|
>
|
<
<
<
<







513
514
515
516
517
518
519
520
521
522
523

524


525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544

545
546
547




548
549
550
551
552
553
554
  rare bugs (see 
  <a href="http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption">DatabaseCorruption</a>) 
  and even then the bugs are normally difficult to
  reproduce.  Even if your application crashes in the middle of an
  update, your database is safe.  The database is safe even if your OS
  crashes or takes a power loss.  The crash-resistance of SQLite has
  been extensively studied and tested and is attested by years of real-world 
  experience by billions of users.</p>

  <p>That said, there are a number of things that external programs or bugs
  in your hardware or OS can do to corrupt a database file.  See

  <a href="howtocorrupt.html">How To Corrupt An SQLite DAtabase File</a> for


  further information.

  <p>Your can use <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a> 
  to do a thorough but time intensive test of the database integrity.</p>

  <p>Your can use <a href="pragma.html#pragma_quick_check">PRAGMA quick_check</a> to do a faster 
  but less thorough test of the database integrity.</p>

  <p>Depending how badly your database is corrupted, you may be able to 
  recover some of the data by using the CLI to dump the schema and contents
  to a file and then recreate.  Unfortunately, once humpty-dumpty falls off 
  the wall, it is generally not possible to put him back together again.</p>
}

faq {
  Does SQLite support foreign keys?
} {
  <p>
  As of version 3.6.19, SQLite supports [foreign key constraints].  But enforcement
  of foreign key constraints is turned off by default (for backwards compatibility).

  To enable foreign key constraint enforcements, run 
  [PRAGMA foreign_keys|PRAGMA foreign_keys=ON] or compile with
  [SQLITE_DEFAULT_FOREIGN_KEYS | -DSQLITE_DEFAULT_FOREIGN_KEYS=1].




}

faq {
  I get a compiler error if I use the SQLITE_OMIT_... 
  compile-time options when building SQLite.
} {
  The [omitfeatures | SQLITE_OMIT_...] compile-time options only work
637
638
639
640
641
642
643










644
645
646
647
648
649
650
  that the core public-domain SQLite source code is not described by any ECCN,
  hence the ECCN should be reported as <b>EAR99</b>.

  <p>The above is true for the core public-domain SQLite.  If you extend
  SQLite by adding new code, or if you statically link SQLite with your
  application, that might change the ECCN in your particular case.
}











# End of questions and answers.
#############

hd_puts {<h2>Frequently Asked Questions</h2>}

    hd_puts {<oL>}







>
>
>
>
>
>
>
>
>
>







621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
  that the core public-domain SQLite source code is not described by any ECCN,
  hence the ECCN should be reported as <b>EAR99</b>.

  <p>The above is true for the core public-domain SQLite.  If you extend
  SQLite by adding new code, or if you statically link SQLite with your
  application, that might change the ECCN in your particular case.
}

faq {
  My query does not return the column name that I expect.  Is this a bug?
} {
  If the columns of your result set are named by AS clauses, then SQLite
  is guaranteed to use the identifer to the right of the AS keyword as the
  column name.  If the result set does not use an AS clause, then SQLite
  is free to name the column anything it wants.
  See the [sqlite3_column_name()] documentation for further information.
}

# End of questions and answers.
#############

hd_puts {<h2>Frequently Asked Questions</h2>}

    hd_puts {<oL>}