Documentation Source Text

Check-in [4072a11193]
Login

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

Overview
Comment:Added a few more FAQ entries. Minor update and corrections.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4072a111933d6413cb34cdbb2c0567f3fc0a95a6
User & Date: shaneh 2009-03-06 04:13:08.000
Context
2009-03-13
15:33
Add a page that illustrates use of the new experimental sqlite3_unlock_notify() API. (check-in: f0a5786f01 user: dan tags: trunk)
2009-03-06
04:13
Added a few more FAQ entries. Minor update and corrections. (check-in: 4072a11193 user: shaneh tags: trunk)
2009-03-05
21:46
Fixed some typos and spelling mistakes. (check-in: 832ea9eb72 user: shaneh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/faq.in.
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
  <p><a href="http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf">
  Threads are evil</a>.  Avoid them.

  <p>SQLite is threadsafe.  We make this concession since many users choose
  to ignore the advice given in the previous paragraph.
  But in order to be thread-safe, SQLite must be compiled
  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







|







159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
  <p><a href="http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf">
  Threads are evil</a>.  Avoid them.

  <p>SQLite is threadsafe.  We make this concession since many users choose
  to ignore the advice given in the previous paragraph.
  But in order to be thread-safe, SQLite must be compiled
  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
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
466
467
468
}

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
  full unicode case-insensitive comparisons and case conversions 
  requires tables and logic that would nearly double the size of
  the SQLite library.  The
  SQLite developers reason that any application that needs full
  unicode case support probably already has the necessary tables and
  functions and so SQLite should not take up space to 
  duplicate this ability.</p>

  <p>Instead of providing full unicode case support by default, 
  SQLite provides the ability to link against external
  unicode comparison and conversion routines.
  The application can overload the built-in [NOCASE] collating
  sequence (using [sqlite3_create_collation()]) and the built-in
  [like()], [upper()], and [lower()] functions
  (using [sqlite3_create_function()]).  
  The SQLite source code includes an "ICU" extension that does 
  these overloads.  Or, developers can write their own overloads
  based on their own unicode-aware comparison routines already
  contained within their project.
}

faq {
  INSERT is really slow - I can only do few dozen INSERTs per second
} {
  Actually, SQLite will easily do 50,000 or more [INSERT] statements per second







|



|



|

|






|







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
466
467
468
}

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
  full Unicode case-insensitive comparisons and case conversions 
  requires tables and logic that would nearly double the size of
  the SQLite library.  The
  SQLite developers reason that any application that needs full
  Unicode case support probably already has the necessary tables and
  functions and so SQLite should not take up space to 
  duplicate this ability.</p>

  <p>Instead of providing full Unicode case support by default, 
  SQLite provides the ability to link against external
  Unicode comparison and conversion routines.
  The application can overload the built-in [NOCASE] collating
  sequence (using [sqlite3_create_collation()]) and the built-in
  [like()], [upper()], and [lower()] functions
  (using [sqlite3_create_function()]).  
  The SQLite source code includes an "ICU" extension that does 
  these overloads.  Or, developers can write their own overloads
  based on their own Unicode-aware comparison routines already
  contained within their project.
}

faq {
  INSERT is really slow - I can only do few dozen INSERTs per second
} {
  Actually, SQLite will easily do 50,000 or more [INSERT] statements per second
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512



























































513
514
515
516

517
518
519
520
521
522
523
524
525
526
527
528
529
530
  <p>Another option is to run [PRAGMA synchronous=OFF].  This command will
  cause SQLite to not wait on data to reach the disk surface, which will make
  write operations appear to be much faster.  But if you lose power in the
  middle of a transaction, your database file might go corrupt.
}

faq {
  I accidently deleted some important information from my SQLite database.
  How can I recover it?
} {
  If you have a backup copy of your database file, recover the information
  from your backup.

  <p>If you do not have a backup, recovery is very difficult.  You might
  be able to find partial string data in a binary dump of the raw database
  file.  Recovering numeric data might also be possible given special tools,
  though to our knowledge no such tools exist.  SQLite is sometimes compiled
  with the [SQLITE_SECURE_DELETE] option which overwrites all deleted content
  with zeros.  If that is the case then recovery is clearly impossible.
  Recovery is also impossible if you have run [VACUUM] since the data was
  deleted.  If SQLITE_SECURE_DELETE is not used and VACUUM has not been run,
  then some of the deleted content might still be in the database file, in
  areas marked for reuse.  But, again, there exist no procedures or tools
  that we know of to help you recover that data.
}




























































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

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


hd_puts {<oL>}
for {set i 1} {$i<$cnt} {incr i} {
  hd_puts "<li><a href=\"#q$i\">[lindex $faq($i) 0]</a></li>"
}
hd_puts {</ol>}

for {set i 1} {$i<$cnt} {incr i} {
  hd_fragment q$i
  hd_puts "<p><b>($i) [lindex $faq($i) 0]</b></p>\n"
  hd_resolve "<blockquote>[lindex $faq($i) 1]</blockquote></li>\n"
}
hd_puts {</ol>}
</tcl>







|


















>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




>

|












487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
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
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
  <p>Another option is to run [PRAGMA synchronous=OFF].  This command will
  cause SQLite to not wait on data to reach the disk surface, which will make
  write operations appear to be much faster.  But if you lose power in the
  middle of a transaction, your database file might go corrupt.
}

faq {
  I accidentally deleted some important information from my SQLite database.
  How can I recover it?
} {
  If you have a backup copy of your database file, recover the information
  from your backup.

  <p>If you do not have a backup, recovery is very difficult.  You might
  be able to find partial string data in a binary dump of the raw database
  file.  Recovering numeric data might also be possible given special tools,
  though to our knowledge no such tools exist.  SQLite is sometimes compiled
  with the [SQLITE_SECURE_DELETE] option which overwrites all deleted content
  with zeros.  If that is the case then recovery is clearly impossible.
  Recovery is also impossible if you have run [VACUUM] since the data was
  deleted.  If SQLITE_SECURE_DELETE is not used and VACUUM has not been run,
  then some of the deleted content might still be in the database file, in
  areas marked for reuse.  But, again, there exist no procedures or tools
  that we know of to help you recover that data.
}

faq {
  What is an SQLITE_CORRUPT error?  What does it mean for the database
  to be "malformed"? Why am I getting this error?
} {
  <p>An [SQLITE_CORRUPT] error is returned when SQLite detects an error
  in the structure, format, or other control elements of the
  database file.</p>

  <p>SQLite does not corrupt database files, except in the case of very
  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>FOREIGN KEY constraints are parsed but are not enforced.
  However, the equivalent constraint enforcement can be
  achieved using
  <a href="http://www.sqlite.org/lang_createtrigger.html">triggers</a>.
  The SQLite source tree contains
  source code and documentation for a C program (genfkey) that will
  read an SQLite database, analyze the foreign key constraints,
  and generate appropriate triggers automatically.</p>

  <p>The <a href="http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README">readme</a>
  for the genfkey utility contains more information.</p>

  <p>As of [Version 3.6.12] this feature is incorporated into the CLI.</p>
  
  <p>You can read about other possible solutions for foreign key
  support in the
  <a href="http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers">SQLite Wiki</a>.</p>
}

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

hd_puts {<h2>Frequently Asked Questions</h2>}
hd_puts {<p>See also <a href="http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq">SqliteWikiFaq</a>.</p>}

    hd_puts {<oL>}
for {set i 1} {$i<$cnt} {incr i} {
  hd_puts "<li><a href=\"#q$i\">[lindex $faq($i) 0]</a></li>"
}
hd_puts {</ol>}

for {set i 1} {$i<$cnt} {incr i} {
  hd_fragment q$i
  hd_puts "<p><b>($i) [lindex $faq($i) 0]</b></p>\n"
  hd_resolve "<blockquote>[lindex $faq($i) 1]</blockquote></li>\n"
}
hd_puts {</ol>}
</tcl>