author | Dominic Pinkman <dominic.pinkman@nokia.com> |
Fri, 13 Aug 2010 16:47:46 +0100 | |
changeset 14 | 578be2adaf3e |
parent 5 | f345bda72bc4 |
permissions | -rw-r--r-- |
14
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
1 |
<?xml version="1.0" encoding="utf-8"?> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
2 |
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
3 |
<!-- This component and the accompanying materials are made available under the terms of the License |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
4 |
"Eclipse Public License v1.0" which accompanies this distribution, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
5 |
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
6 |
<!-- Initial Contributors: |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
7 |
Nokia Corporation - initial contribution. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
8 |
Contributors: |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
9 |
--> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
10 |
<!DOCTYPE concept |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
11 |
PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
12 |
<concept id="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757" xml:lang="en"><title>SQL Schema |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
13 |
Tips</title><shortdesc>This guide gives tips for using the database schema. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
14 |
<section id="GUID-37B02CC0-B591-542D-8B02-B6E2F3FF2097"><title>Introduction</title> <p>A |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
15 |
schema is a way of organizing a number of related database objects such as |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
16 |
tables. The schema contains at least one object but can contain as many as |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
17 |
the total number of tables in the database. This section provides some handy |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
18 |
tips for working with schemas in Symbian SQL. </p> <p>The following tips or |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
19 |
techniques are discussed: </p> <ul> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
20 |
<li id="GUID-F50E880D-BE59-5915-BE5A-E752EA2D3868"><p> <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-296FB987-9E61-5925-9246-0468534D16A9">Handling SQL schema errors</xref> </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
21 |
<li id="GUID-A5C25843-9D8E-5F2F-96FD-61BA05B4FEC6"><p> <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-0D7324A8-8DAF-57B5-BCA1-C8BDD855773C">Designing tables for more efficient access</xref> </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
22 |
<li id="GUID-A300E8B2-A0F2-5EC1-AAE4-09909FA69E98"><p> <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-DAF6BE02-7D38-5667-86B2-D12EB34BB6EC">Storing Large BLOBs Separately from the Database</xref> </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
23 |
<li id="GUID-C66BE626-F551-58E5-98C9-33090DF96272"><p> <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-8F18A8B8-706F-5F53-88BC-249F9EB48110">Using INTEGER PRIMARY KEY</xref> </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
24 |
<li id="GUID-86442FBC-699C-5101-95CB-1BA5229DE63A"><p> <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-BD8FE233-2AA5-5DF7-9A84-EEC5836669E8">Efficient Primary Key and Row ID values</xref> </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
25 |
<li id="GUID-8FEA56BD-19A7-59A2-8B26-01A3594D6FC7"><p> <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-40CDE965-B261-5873-9F8B-7812EEC14AD7">Keep Schemas Small and Constant</xref> </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
26 |
</ul> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
27 |
<section id="GUID-296FB987-9E61-5925-9246-0468534D16A9"><title>Handling SQL |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
28 |
schema errors</title> <p>In order to translate a SQL statement into bytecode, <codeph>Prepare()</codeph> needs |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
29 |
to know the database schema. For any given SQL statement there are typically |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
30 |
many different ways of implementing the statement in bytecode. The compiler |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
31 |
(or a particular part of the compiler that is commonly referred to as the <i>optimizer</i>) |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
32 |
tries to pick the most efficient implementation based on what is known about |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
33 |
the content of the various tables and what indexes are available. The bytecode |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
34 |
depends so much on the database schema that the slightest change to the schema |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
35 |
can cause totally different bytecode to be generated for the same SQL statement. </p> <p>When <codeph>Prepare()</codeph> runs, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
36 |
it uses the database schema as of the last time the database was accessed |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
37 |
when preparing the bytecode. So if the database schema has been changed by |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
38 |
another program, or if the schema is changed after <codeph>Prepare()</codeph> completes |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
39 |
but before <codeph>Exec()</codeph> or <codeph>Next()</codeph> is run, the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
40 |
schema of the database will not match the schema used to prepare the bytecode. </p> <p>To |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
41 |
guard against any problems, the first few instructions of bytecode in a prepared |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
42 |
statement read the current schema cookie for the database and check to see |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
43 |
that it is the same as it was when <codeph>Prepare()</codeph> was run. If |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
44 |
the schema cookie has changed, then <codeph>Exec()</codeph> and <codeph>Next()</codeph> terminate |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
45 |
with the error <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref>. </p> <p>When this occurs, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
46 |
a new prepared statement should be created by calling <codeph>Prepare()</codeph> again |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
47 |
with the same SQL and the original prepared statement should be discarded. </p> <p>It |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
48 |
is important to recognize that a <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref> error can |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
49 |
occur on any call to <codeph>Exec()</codeph> or <codeph>Next()</codeph>. A |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
50 |
common programming error with is to omit the necessary error handling or to |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
51 |
do it incorrectly. The error is, unfortunately, often overlooked during testing |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
52 |
because the usual cause of an <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref> error is an |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
53 |
external program making a schema change at just the wrong moment, and external |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
54 |
programs are not normally running during testing. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
55 |
<section id="GUID-0D7324A8-8DAF-57B5-BCA1-C8BDD855773C"><title> Designing |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
56 |
tables for more efficient access</title> <p>Put smaller and frequently accessed |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
57 |
columns at the beginning of tables. SQLite stores a row of a table by gathering |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
58 |
the data for all columns in that row and packing the data together in column |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
59 |
order into as few bytes as possible. <i>Column order</i> means that the data |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
60 |
for the first declared column of the table – the column that appears first |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
61 |
in the CREATE TABLE statement – is packed into the bundle first. The data |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
62 |
for the second column is packed into the bundle second. And so forth. </p> <p>SQLite |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
63 |
goes to some trouble to use as few bytes as possible when storing data. A |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
64 |
text string that is 5 bytes long, for example, is stored using just 5 bytes. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
65 |
The '\00' terminator on the end is omitted. No padding bytes or overhead are |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
66 |
added even if the column specifies a larger string such as VARCHAR(1000). </p> <p>Small |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
67 |
integers (between -127 and +127) are stored as a single byte. As the magnitude |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
68 |
of the integer increases, additional bytes of storage are added as necessary |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
69 |
up to 8 bytes. Floating point numbers are normally stored as 8-byte IEEE floats, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
70 |
but if the floating point number can be represented as a smaller integer without |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
71 |
loss of information, it is converted and stored that way to save space. BLOBs |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
72 |
are also stored using the minimum number of bytes necessary. </p> <p>These |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
73 |
efforts to save space both help to keep SQLite database files small and also |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
74 |
improve performance since the smaller each record is the more information |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
75 |
will fit in the same amount of space and the less disk I/O needs to occur. </p> <p>The |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
76 |
downside of using compressed storage is that data in each column is an unpredictable |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
77 |
size. So within each row, we do not know in advance where one column ends |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
78 |
and the next column begins. To extract the data from the N-th column of a |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
79 |
row, SQLite has to decode the data from the N-1 prior columns first. Thus, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
80 |
for example, if you have a query that reads just the fourth column of a table, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
81 |
the bytecode that implements the query has to read and discard the data from |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
82 |
the first, second, and third columns in order to find where the data for the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
83 |
fourth column begins in order to read it out. </p> <p>For this reason, it |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
84 |
is best to put smaller and more frequently accessed columns of a table early |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
85 |
in the CREATE TABLE statement and put large CLOBs and BLOBs and infrequently |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
86 |
accessed information toward the end. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
87 |
<section id="GUID-DAF6BE02-7D38-5667-86B2-D12EB34BB6EC"><title>Storing Large |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
88 |
BLOBs Separately from the Database</title> <p>SQLite does not place arbitrary |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
89 |
constraints on the size of BLOBs and CLOBs that it will store. The only real |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
90 |
restriction is that the entire BLOB or CLOB must fit in memory all at once. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
91 |
But just because you can do this does not mean you should. </p> <p>Although |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
92 |
SQLite is able to handle really big BLOBs, it is not optimized for that case. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
93 |
If performance and storage efficiency is a concern, you will be better served |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
94 |
to store large BLOBs and CLOBs in separate disk files then store the name |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
95 |
of the file in the database in place of the actual content. </p> <p>When SQLite |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
96 |
encounters a large table row – e.g. because it contains one or more large |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
97 |
BLOBs – it tries to store as much of the row as it can on a single page of |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
98 |
the database. The tail of the row that will not fit on a single page is spilled |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
99 |
into overflow pages. If more than one overflow page is required, then the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
100 |
overflow pages form a linked list. </p> <p>To store a 1MiB BLOB in a database |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
101 |
with a 1KiB page size will require the BLOB to be broken into a list of over |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
102 |
a thousand links, each of which gets stored separately. Whenever you want |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
103 |
to read the BLOB out of the database, SQLite has to walk this thousand-member |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
104 |
linked list in order to reassemble the BLOB again. This works and is reliable, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
105 |
but it is not especially efficient. </p> <p>Another problem with large BLOBs |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
106 |
and CLOBs is that they can be neither read nor written incrementally. The |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
107 |
entire BLOB or CLOB must be read or written all at once. So, for example, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
108 |
to append 10 bytes to the end of a 10MiB BLOB, one has to read the entire |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
109 |
BLOB, append the 10 bytes, then write the new BLOB back out again. With a |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
110 |
disk file, on the other hand, you can append 10 bytes quickly and efficiently |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
111 |
without having to access the other 10 million bytes of the original file. </p> <p>If |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
112 |
you absolutely need to store large BLOBs and CLOBs in your database (perhaps |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
113 |
so that changes to these data elements will be atomic and durable) then at |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
114 |
least consider storing the BLOBs and CLOBs in separate tables that contain |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
115 |
only an INTEGER PRIMARY KEY and the content of the BLOB or CLOB. So, instead |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
116 |
of writing your schema this way: </p> <codeblock id="GUID-A501A0C1-5AB2-5EBA-9DB3-2C786C1DFFFF" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
117 |
CREATE TABLE image( |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
118 |
imageId INTEGER PRIMARY KEY, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
119 |
imageTitle TEXT, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
120 |
imageWidth INTEGER, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
121 |
imageHeight INTEGER, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
122 |
imageRefCnt INTEGER, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
123 |
imageBlob BLOB |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
124 |
); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
125 |
</codeblock> <p>Factor out the large BLOB into a separate table so that your |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
126 |
schema looks more like this: </p> <codeblock id="GUID-F3CEBEDB-9D4A-5930-8AFE-3FE33DECE1CF" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
127 |
CREATE TABLE image( |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
128 |
imageId INTEGER PRIMARY KEY, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
129 |
imageTitle TEXT, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
130 |
imageWidth INTEGER, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
131 |
imageHeight INTEGER, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
132 |
imageRefCnt INTEGER |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
133 |
); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
134 |
|
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
135 |
CREATE TABLE imagedata( |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
136 |
imageId INTEGER PRIMARY KEY, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
137 |
imageBlob BLOB |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
138 |
); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
139 |
</codeblock> <p>Keeping all of the small columns in a separate table increases |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
140 |
locality of reference and allows queries that do not use the large BLOB to |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
141 |
run much faster. You can still write single queries that return both the smaller |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
142 |
fields and the BLOB by using a simple (and very efficient) join on the INTEGER |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
143 |
PRIMARY KEY. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
144 |
<section id="GUID-8F18A8B8-706F-5F53-88BC-249F9EB48110"><title>Using INTEGER |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
145 |
PRIMARY KEY</title> <p>INTEGER PRIMARY KEY is a fast special case. Every row |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
146 |
of every SQLite table has a signed 64-bit integer RowID. This RowID is the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
147 |
key for the b-tree that holds the table content. The RowID must be unique |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
148 |
over all other rows in the same table. When you go to find, insert, or remove |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
149 |
a row from a table in SQLite, the row is first located by search for its RowID. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
150 |
Searching by RowID is very fast. Everything in SQLite tables centres around |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
151 |
RowIDs. </p> <p>In the CREATE TABLE statement that defines a table, if you |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
152 |
declare a column to be of type INTEGER PRIMARY KEY, then that column becomes |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
153 |
an alias for the RowID. It is generally a good idea to create such a column |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
154 |
whenever it is practical. </p> <p>Looking up information by RowID or INTEGER |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
155 |
PRIMARY KEY is usually about twice as fast as any other search method in SQLite. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
156 |
So for example, if we have an indexed table like this: </p> <codeblock id="GUID-A168F121-43D7-52A1-B9BD-E92160473FE1" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
157 |
CREATE TABLE demo1( |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
158 |
id1 INTEGER PRIMARY KEY, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
159 |
id2 INTEGER UNIQUE, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
160 |
content BLOB |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
161 |
); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
162 |
</codeblock> <p>Then queries against the INTEGER PRIMARY KEY, e.g. of the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
163 |
form: </p> <codeblock id="GUID-FE186FEB-8134-56BE-A23D-219C20A89FC3" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
164 |
SELECT content FROM demo1 WHERE id1=?; |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
165 |
</codeblock> <p>will be about twice as fast as queries like this: </p> <codeblock id="GUID-BD9DD075-48DB-51FA-823D-CB278E72E275" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
166 |
SELECT content FROM demo1 WHERE id2=?; |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
167 |
</codeblock> <p>Note that the following two queries are identical in SQLite |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
168 |
– not just equivalent but identical. They generate exactly the same bytecode: </p> <codeblock id="GUID-B28BE56D-F947-5B75-9602-3A1024A8CEC1" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
169 |
SELECT content FROM demo1 WHERE id1=?; |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
170 |
SELECT content FROM demo1 WHERE RowID=?; |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
171 |
</codeblock> <p>But for stylistic and portability reasons, the first form |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
172 |
of the query is preferred. </p> <p>Also observe that in order for the INTEGER |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
173 |
PRIMARY KEY to truly be an alias for the RowID, the declared type must be |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
174 |
exactly “INTEGER PRIMARY KEY”. </p> <p>Variations such as “INT PRIMARY KEY” |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
175 |
or “UNSIGNED INTEGER PRIMARY KEY” or “SHORTINT PRIMARY KEY” become independent |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
176 |
columns instead of aliases for the RowID. </p> <p>So be careful to always |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
177 |
spell INTEGER PRIMARY KEY correctly in your CREATE TABLE statements. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
178 |
<section id="GUID-BD8FE233-2AA5-5DF7-9A84-EEC5836669E8"><title> Efficient |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
179 |
Primary Key and Row ID values</title> <p>Use small positive integers for INTEGER |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
180 |
PRIMARY KEY or RowID. As explained in the section <xref href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita#GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757/GUID-8F18A8B8-706F-5F53-88BC-249F9EB48110">Using INTEGER PRIMARY KEY</xref>, every table row has a key which is a signed |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
181 |
64-bit integer: the RowID or INTEGER PRIMARY KEY. This same RowID also occurs |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
182 |
in every index entry and is used to refer the index entry back to the original |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
183 |
table row. </p> <p>When writing the RowID to disk, SQLite encodes the 64-bit |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
184 |
integer value using a Huffman code over a fixed probability distribution. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
185 |
The resulting encoding requires between 1 and 9 bytes of storage space, depending |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
186 |
on the magnitude of the integer. Small, non-negative integers require less |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
187 |
space than larger or negative integers. Table 3.1 shows the storage requirements: </p> <table id="GUID-A50643C5-5D28-56C1-B1D9-6BFF1E9551B5"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
188 |
<tgroup cols="2"><colspec colname="col0"/><colspec colname="col1"/> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
189 |
<thead> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
190 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
191 |
<entry>RowID Magnitude</entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
192 |
<entry>Bytes Of Storage</entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
193 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
194 |
</thead> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
195 |
<tbody> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
196 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
197 |
<entry><p>0 to 127 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
198 |
<entry><p>1</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
199 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
200 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
201 |
<entry><p>128 to 16383 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
202 |
<entry><p>2</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
203 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
204 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
205 |
<entry><p>16384 to 2097151 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
206 |
<entry><p>3</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
207 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
208 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
209 |
<entry><p>2097152 to 268435455 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
210 |
<entry><p>4</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
211 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
212 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
213 |
<entry><p>268435456 to 34359738367 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
214 |
<entry><p>5</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
215 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
216 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
217 |
<entry><p>34359738368 to 4398046511103 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
218 |
<entry><p>6</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
219 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
220 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
221 |
<entry><p>4398046511104 to 562949953421311 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
222 |
<entry><p>7</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
223 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
224 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
225 |
<entry><p>562949953421312 to 72057594037927935 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
226 |
<entry><p>8</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
227 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
228 |
<row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
229 |
<entry><p>Less than 0 or greater than 72057594037927935 </p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
230 |
<entry><p>9</p> </entry> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
231 |
</row> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
232 |
</tbody> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
233 |
</tgroup> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
234 |
</table> <p> <b>Table 3.1:</b> Storage requirements for RowIDs of different |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
235 |
magnitudes </p> <p>This chart makes it clear that the best way to keep reduce |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
236 |
the number of bytes of disk space devoted to storing RowIDs is to keep RowIDs |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
237 |
small non-negative integers. The database will continue to work fine with |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
238 |
large or negative RowIDs, but the database files will take up more space. </p> <p>SQLite |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
239 |
normally works by assigning RowIDs automatically. An automatically generated |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
240 |
RowID will normally be the smallest positive integer that is not already used |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
241 |
as a RowID in the same table. The first RowID assigned will be 1, the second |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
242 |
2, and so forth. So the automatic RowID assignment algorithm begins by using |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
243 |
1-byte RowIDs, then moves to 2-byte RowIDs as the number of rows increased, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
244 |
then 3-byte RowIDs, and so forth. In other words, the automatic RowID assignment |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
245 |
algorithm does a good job of selecting RowIDs that minimize storage requirements. </p> <p>If |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
246 |
a table contains an INTEGER PRIMARY KEY column, that column becomes an alias |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
247 |
for the RowID. If you then specify a particular RowID when doing an INSERT, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
248 |
the RowID you specify overrides the default choice made by SQLite itself. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
249 |
For example, if your table is this: </p> <codeblock id="GUID-54E6F47E-5FBC-5CB2-8A3A-B81B758C112A" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
250 |
CREATE TABLE demo311( |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
251 |
id INTEGER PRIMARY KEY, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
252 |
content VARCHAR(100) |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
253 |
); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
254 |
</codeblock> <p>Then if you insert like this: </p> <codeblock id="GUID-3D820D52-B53F-5F74-A727-81CEB3677295" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
255 |
INSERT INTO demo311(id,content) VALUES(-17,'Hello'); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
256 |
</codeblock> <p>The value -17 is used as the RowID which requires 9 bytes |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
257 |
of space in the disk file because it is negative. If instead of specifying |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
258 |
the id value you had just left it blank: </p> <codeblock id="GUID-8CBF96D3-9FB6-54C1-861C-CF85FF3F76A8" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
259 |
INSERT INTO demo311(content) VALUES('Hello'); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
260 |
</codeblock> <p>Or if you had specified a NULL as the INTEGER PRIMARY KEY |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
261 |
value: </p> <codeblock id="GUID-0DEF58F0-DC25-586A-B803-4B581FF8653C" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
262 |
INSERT INTO demo311(id,content) VALUES(NULL,'Hello'); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
263 |
</codeblock> <p>Then in either case, SQLite would have automatically selected |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
264 |
a RowID value that was the smallest positive integer not already in use, thus |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
265 |
minimizing the amount of disk space required. </p> <p>Therefore unless you |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
266 |
have specific requirements to the contrary, it generally works best to let |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
267 |
SQLite pick its own RowID and INTEGER PRIMARY KEY values. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
268 |
<section id="GUID-40CDE965-B261-5873-9F8B-7812EEC14AD7"><title>Keep Schemas |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
269 |
Small and Constant</title> <p>SQLite stores the original text of all CREATE |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
270 |
statements in a system table of the database. When a database session is opened |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
271 |
or when the database schema is changed, SQLite has to read and parse all of |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
272 |
these CREATE statements in order to reconstruct its internal symbol tables. </p> <p>The |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
273 |
parser and symbol table builder inside SQLite are very fast, but they still |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
274 |
take time which is proportional to the size of the schema. To minimize the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
275 |
computational overhead associated with parsing the schema: </p> <ul> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
276 |
<li id="GUID-36BDF092-298D-564D-9744-9C83836A2FB2"><p>Keep the number and |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
277 |
size of CREATE statements in the schema to a minimum. </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
278 |
<li id="GUID-DF3A6363-7C24-5727-AD27-58367EFFE1A4"><p>Avoid unnecessary calls |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
279 |
to <xref href="GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF.dita#GUID-4688F6B7-E1B0-37CF-BAA2-C6BD103D4FDF/GUID-195907BE-273B-3DD1-95C9-D48870030914"><apiname>RSqlDatabase::Open()</apiname></xref>. Reuse the same database session |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
280 |
where possible. </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
281 |
<li id="GUID-D1A62767-9CF7-5FBE-8F51-98EB154559C1"><p>Avoid changing the database |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
282 |
schema while other threads or processes might be using the database. </p> </li> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
283 |
</ul> <p>Whenever the schema is reparsed, all statements that were prepared |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
284 |
using the old schema are invalidated. A call <codeph>Next()</codeph> or <codeph>Exec()</codeph> on |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
285 |
a <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> object that was prepared prior to the schema |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
286 |
reparse will return <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref> to let you know that |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
287 |
the cause of the error was a schema change. Thus any prepared statements that |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
288 |
were cached will have to be discarded and re-prepared following a schema change. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
289 |
Refilling the prepared statement cache can be as computationally expensive |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
290 |
as parsing the schema in the first place. </p> <p>Prepared statements can |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
291 |
be invalidated for reasons other than schema changes including executing <codeph>Attach()</codeph> or <codeph>Detach()</codeph>. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
292 |
Avoid this if you have a large cache of prepared statements. The best strategy |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
293 |
is to attach all associated databases as soon as a new database session is |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
294 |
opened and before any statements are prepared. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
295 |
</conbody><related-links> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
296 |
<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
297 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
298 |
<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
299 |
Guide</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
300 |
<link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
301 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
302 |
<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
303 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
304 |
<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
305 |
Tables</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
306 |
<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
307 |
Corruption</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
308 |
<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
309 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
310 |
<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
311 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
312 |
<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
313 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
314 |
<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
315 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
316 |
<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
317 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
318 |
<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
319 |
Command</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
320 |
<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
321 |
CLause Tips</linktext></link> |
5
f345bda72bc4
Week 12 contribution of PDK documentation_content. See release notes for details. Fixes Bug 2054, Bug 1583, Bug 381, Bug 390, Bug 463, Bug 1897, Bug 344, Bug 1319, Bug 394, Bug 1520, Bug 1522, Bug 1892"
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
3
diff
changeset
|
322 |
</related-links></concept> |