<?xml version="1.0" encoding="utf-8"?>

<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->

<!-- This component and the accompanying materials are made available under the terms of the License 

"Eclipse Public License v1.0" which accompanies this distribution, 

and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->

<!-- Initial Contributors:

    Nokia Corporation - initial contribution.

Contributors: 

-->

<!DOCTYPE concept

  PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">

<concept id="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757" xml:lang="en"><title>SQL Schema

Tips</title><shortdesc>This guide gives tips for using the database schema. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

<section id="GUID-37B02CC0-B591-542D-8B02-B6E2F3FF2097"><title>Introduction</title> <p>A

schema is a way of organizing a number of related database objects such as

tables. The schema contains at least one object but can contain as many as

the total number of tables in the database. This section provides some handy

tips for working with schemas in Symbian SQL. </p> <p>The following tips or

techniques are discussed: </p> <ul>

<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>

<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>

<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>

<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>

<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>

<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>

</ul> </section>

<section id="GUID-296FB987-9E61-5925-9246-0468534D16A9"><title>Handling SQL

schema errors</title> <p>In order to translate a SQL statement into bytecode, <codeph>Prepare()</codeph> needs

to know the database schema. For any given SQL statement there are typically

many different ways of implementing the statement in bytecode. The compiler

(or a particular part of the compiler that is commonly referred to as the <i>optimizer</i>)

tries to pick the most efficient implementation based on what is known about

the content of the various tables and what indexes are available. The bytecode

depends so much on the database schema that the slightest change to the schema

can cause totally different bytecode to be generated for the same SQL statement. </p> <p>When <codeph>Prepare()</codeph> runs,

it uses the database schema as of the last time the database was accessed

when preparing the bytecode. So if the database schema has been changed by

another program, or if the schema is changed after <codeph>Prepare()</codeph> completes

but before <codeph>Exec()</codeph> or <codeph>Next()</codeph> is run, the

schema of the database will not match the schema used to prepare the bytecode. </p> <p>To

guard against any problems, the first few instructions of bytecode in a prepared

statement read the current schema cookie for the database and check to see

that it is the same as it was when <codeph>Prepare()</codeph> was run. If

the schema cookie has changed, then <codeph>Exec()</codeph> and <codeph>Next()</codeph> terminate

with the error <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref>. </p> <p>When this occurs,

a new prepared statement should be created by calling <codeph>Prepare()</codeph> again

with the same SQL and the original prepared statement should be discarded. </p> <p>It

is important to recognize that a <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref> error can

occur on any call to <codeph>Exec()</codeph> or <codeph>Next()</codeph>. A

common programming error with is to omit the necessary error handling or to

do it incorrectly. The error is, unfortunately, often overlooked during testing

because the usual cause of an <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref> error is an

external program making a schema change at just the wrong moment, and external

programs are not normally running during testing. </p> </section>

<section id="GUID-0D7324A8-8DAF-57B5-BCA1-C8BDD855773C"><title> Designing

tables for more efficient access</title> <p>Put smaller and frequently accessed

columns at the beginning of tables. SQLite stores a row of a table by gathering

the data for all columns in that row and packing the data together in column

order into as few bytes as possible. <i>Column order</i> means that the data

for the first declared column of the table – the column that appears first

in the CREATE TABLE statement – is packed into the bundle first. The data

for the second column is packed into the bundle second. And so forth. </p> <p>SQLite

goes to some trouble to use as few bytes as possible when storing data. A

text string that is 5 bytes long, for example, is stored using just 5 bytes.

The '\00' terminator on the end is omitted. No padding bytes or overhead are

added even if the column specifies a larger string such as VARCHAR(1000). </p> <p>Small

integers (between -127 and +127) are stored as a single byte. As the magnitude

of the integer increases, additional bytes of storage are added as necessary

up to 8 bytes. Floating point numbers are normally stored as 8-byte IEEE floats,

but if the floating point number can be represented as a smaller integer without

loss of information, it is converted and stored that way to save space. BLOBs

are also stored using the minimum number of bytes necessary. </p> <p>These

efforts to save space both help to keep SQLite database files small and also

improve performance since the smaller each record is the more information

will fit in the same amount of space and the less disk I/O needs to occur. </p> <p>The

downside of using compressed storage is that data in each column is an unpredictable

size. So within each row, we do not know in advance where one column ends

and the next column begins. To extract the data from the N-th column of a

row, SQLite has to decode the data from the N-1 prior columns first. Thus,

for example, if you have a query that reads just the fourth column of a table,

the bytecode that implements the query has to read and discard the data from

the first, second, and third columns in order to find where the data for the

fourth column begins in order to read it out. </p> <p>For this reason, it

is best to put smaller and more frequently accessed columns of a table early

in the CREATE TABLE statement and put large CLOBs and BLOBs and infrequently

accessed information toward the end. </p> </section>

<section id="GUID-DAF6BE02-7D38-5667-86B2-D12EB34BB6EC"><title>Storing Large

BLOBs Separately from the Database</title> <p>SQLite does not place arbitrary

constraints on the size of BLOBs and CLOBs that it will store. The only real

restriction is that the entire BLOB or CLOB must fit in memory all at once.

But just because you can do this does not mean you should. </p> <p>Although

SQLite is able to handle really big BLOBs, it is not optimized for that case.

If performance and storage efficiency is a concern, you will be better served

to store large BLOBs and CLOBs in separate disk files then store the name

of the file in the database in place of the actual content. </p> <p>When SQLite

encounters a large table row – e.g. because it contains one or more large

BLOBs – it tries to store as much of the row as it can on a single page of

the database. The tail of the row that will not fit on a single page is spilled

into overflow pages. If more than one overflow page is required, then the

overflow pages form a linked list. </p> <p>To store a 1MiB BLOB in a database

with a 1KiB page size will require the BLOB to be broken into a list of over

a thousand links, each of which gets stored separately. Whenever you want

to read the BLOB out of the database, SQLite has to walk this thousand-member

linked list in order to reassemble the BLOB again. This works and is reliable,

but it is not especially efficient. </p> <p>Another problem with large BLOBs

and CLOBs is that they can be neither read nor written incrementally. The

entire BLOB or CLOB must be read or written all at once. So, for example,

to append 10 bytes to the end of a 10MiB BLOB, one has to read the entire

BLOB, append the 10 bytes, then write the new BLOB back out again. With a

disk file, on the other hand, you can append 10 bytes quickly and efficiently

without having to access the other 10 million bytes of the original file. </p> <p>If

you absolutely need to store large BLOBs and CLOBs in your database (perhaps

so that changes to these data elements will be atomic and durable) then at

least consider storing the BLOBs and CLOBs in separate tables that contain

only an INTEGER PRIMARY KEY and the content of the BLOB or CLOB. So, instead

of writing your schema this way: </p> <codeblock id="GUID-A501A0C1-5AB2-5EBA-9DB3-2C786C1DFFFF" xml:space="preserve">

CREATE TABLE image(

    imageId INTEGER PRIMARY KEY,

    imageTitle TEXT,

    imageWidth INTEGER,

    imageHeight INTEGER,

    imageRefCnt INTEGER,

    imageBlob BLOB

);

</codeblock> <p>Factor out the large BLOB into a separate table so that your

schema looks more like this: </p> <codeblock id="GUID-F3CEBEDB-9D4A-5930-8AFE-3FE33DECE1CF" xml:space="preserve">

CREATE TABLE image(

    imageId INTEGER PRIMARY KEY,

    imageTitle TEXT,

    imageWidth INTEGER,

    imageHeight INTEGER,

    imageRefCnt INTEGER

);

CREATE TABLE imagedata(

    imageId INTEGER PRIMARY KEY,

    imageBlob BLOB

);

</codeblock> <p>Keeping all of the small columns in a separate table increases

locality of reference and allows queries that do not use the large BLOB to

run much faster. You can still write single queries that return both the smaller

fields and the BLOB by using a simple (and very efficient) join on the INTEGER

PRIMARY KEY. </p> </section>

<section id="GUID-8F18A8B8-706F-5F53-88BC-249F9EB48110"><title>Using INTEGER

PRIMARY KEY</title> <p>INTEGER PRIMARY KEY is a fast special case. Every row

of every SQLite table has a signed 64-bit integer RowID. This RowID is the

key for the b-tree that holds the table content. The RowID must be unique

over all other rows in the same table. When you go to find, insert, or remove

a row from a table in SQLite, the row is first located by search for its RowID.

Searching by RowID is very fast. Everything in SQLite tables centres around

RowIDs. </p> <p>In the CREATE TABLE statement that defines a table, if you

declare a column to be of type INTEGER PRIMARY KEY, then that column becomes

an alias for the RowID. It is generally a good idea to create such a column

whenever it is practical. </p> <p>Looking up information by RowID or INTEGER

PRIMARY KEY is usually about twice as fast as any other search method in SQLite.

So for example, if we have an indexed table like this: </p> <codeblock id="GUID-A168F121-43D7-52A1-B9BD-E92160473FE1" xml:space="preserve">

CREATE TABLE demo1(

    id1 INTEGER PRIMARY KEY,

    id2 INTEGER UNIQUE,

    content BLOB

);

</codeblock> <p>Then queries against the INTEGER PRIMARY KEY, e.g. of the

form: </p> <codeblock id="GUID-FE186FEB-8134-56BE-A23D-219C20A89FC3" xml:space="preserve">

SELECT content FROM demo1 WHERE id1=?;

</codeblock> <p>will be about twice as fast as queries like this: </p> <codeblock id="GUID-BD9DD075-48DB-51FA-823D-CB278E72E275" xml:space="preserve">

SELECT content FROM demo1 WHERE id2=?;

</codeblock> <p>Note that the following two queries are identical in SQLite

– not just equivalent but identical. They generate exactly the same bytecode: </p> <codeblock id="GUID-B28BE56D-F947-5B75-9602-3A1024A8CEC1" xml:space="preserve">

SELECT content FROM demo1 WHERE id1=?;

SELECT content FROM demo1 WHERE RowID=?;

</codeblock> <p>But for stylistic and portability reasons, the first form

of the query is preferred. </p> <p>Also observe that in order for the INTEGER

PRIMARY KEY to truly be an alias for the RowID, the declared type must be

exactly “INTEGER PRIMARY KEY”. </p> <p>Variations such as “INT PRIMARY KEY”

or “UNSIGNED INTEGER PRIMARY KEY” or “SHORTINT PRIMARY KEY” become independent

columns instead of aliases for the RowID. </p> <p>So be careful to always

spell INTEGER PRIMARY KEY correctly in your CREATE TABLE statements. </p> </section>

<section id="GUID-BD8FE233-2AA5-5DF7-9A84-EEC5836669E8"><title> Efficient

Primary Key and Row ID values</title> <p>Use small positive integers for INTEGER

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

64-bit integer: the RowID or INTEGER PRIMARY KEY. This same RowID also occurs

in every index entry and is used to refer the index entry back to the original

table row. </p> <p>When writing the RowID to disk, SQLite encodes the 64-bit

integer value using a Huffman code over a fixed probability distribution.

The resulting encoding requires between 1 and 9 bytes of storage space, depending

on the magnitude of the integer. Small, non-negative integers require less

space than larger or negative integers. Table 3.1 shows the storage requirements: </p> <table id="GUID-A50643C5-5D28-56C1-B1D9-6BFF1E9551B5">

<tgroup cols="2"><colspec colname="col0"/><colspec colname="col1"/>

<thead>

<row>

<entry>RowID Magnitude</entry>

<entry>Bytes Of Storage</entry>

</row>

</thead>

<tbody>

<row>

<entry><p>0 to 127 </p> </entry>

<entry><p>1</p> </entry>

</row>

<row>

<entry><p>128 to 16383 </p> </entry>

<entry><p>2</p> </entry>

</row>

<row>

<entry><p>16384 to 2097151 </p> </entry>

<entry><p>3</p> </entry>

</row>

<row>

<entry><p>2097152 to 268435455 </p> </entry>

<entry><p>4</p> </entry>

</row>

<row>

<entry><p>268435456 to 34359738367 </p> </entry>

<entry><p>5</p> </entry>

</row>

<row>

<entry><p>34359738368 to 4398046511103 </p> </entry>

<entry><p>6</p> </entry>

</row>

<row>

<entry><p>4398046511104 to 562949953421311 </p> </entry>

<entry><p>7</p> </entry>

</row>

<row>

<entry><p>562949953421312 to 72057594037927935 </p> </entry>

<entry><p>8</p> </entry>

</row>

<row>

<entry><p>Less than 0 or greater than 72057594037927935 </p> </entry>

<entry><p>9</p> </entry>

</row>

</tbody>

</tgroup>

</table> <p> <b>Table 3.1:</b> Storage requirements for RowIDs of different

magnitudes </p> <p>This chart makes it clear that the best way to keep reduce

the number of bytes of disk space devoted to storing RowIDs is to keep RowIDs

small non-negative integers. The database will continue to work fine with

large or negative RowIDs, but the database files will take up more space. </p> <p>SQLite

normally works by assigning RowIDs automatically. An automatically generated

RowID will normally be the smallest positive integer that is not already used

as a RowID in the same table. The first RowID assigned will be 1, the second

2, and so forth. So the automatic RowID assignment algorithm begins by using

1-byte RowIDs, then moves to 2-byte RowIDs as the number of rows increased,

then 3-byte RowIDs, and so forth. In other words, the automatic RowID assignment

algorithm does a good job of selecting RowIDs that minimize storage requirements. </p> <p>If

a table contains an INTEGER PRIMARY KEY column, that column becomes an alias

for the RowID. If you then specify a particular RowID when doing an INSERT,

the RowID you specify overrides the default choice made by SQLite itself.

For example, if your table is this: </p> <codeblock id="GUID-54E6F47E-5FBC-5CB2-8A3A-B81B758C112A" xml:space="preserve">

CREATE TABLE demo311(

    id INTEGER PRIMARY KEY,

    content VARCHAR(100)

);

</codeblock> <p>Then if you insert like this: </p> <codeblock id="GUID-3D820D52-B53F-5F74-A727-81CEB3677295" xml:space="preserve">

INSERT INTO demo311(id,content) VALUES(-17,'Hello');

</codeblock> <p>The value -17 is used as the RowID which requires 9 bytes

of space in the disk file because it is negative. If instead of specifying

the id value you had just left it blank: </p> <codeblock id="GUID-8CBF96D3-9FB6-54C1-861C-CF85FF3F76A8" xml:space="preserve">

INSERT INTO demo311(content) VALUES('Hello');

</codeblock> <p>Or if you had specified a NULL as the INTEGER PRIMARY KEY

value: </p> <codeblock id="GUID-0DEF58F0-DC25-586A-B803-4B581FF8653C" xml:space="preserve">

INSERT INTO demo311(id,content) VALUES(NULL,'Hello');

</codeblock> <p>Then in either case, SQLite would have automatically selected

a RowID value that was the smallest positive integer not already in use, thus

minimizing the amount of disk space required. </p> <p>Therefore unless you

have specific requirements to the contrary, it generally works best to let

SQLite pick its own RowID and INTEGER PRIMARY KEY values. </p> </section>

<section id="GUID-40CDE965-B261-5873-9F8B-7812EEC14AD7"><title>Keep Schemas

Small and Constant</title> <p>SQLite stores the original text of all CREATE

statements in a system table of the database. When a database session is opened

or when the database schema is changed, SQLite has to read and parse all of

these CREATE statements in order to reconstruct its internal symbol tables. </p> <p>The

parser and symbol table builder inside SQLite are very fast, but they still

take time which is proportional to the size of the schema. To minimize the

computational overhead associated with parsing the schema: </p> <ul>

<li id="GUID-36BDF092-298D-564D-9744-9C83836A2FB2"><p>Keep the number and

size of CREATE statements in the schema to a minimum. </p> </li>

<li id="GUID-DF3A6363-7C24-5727-AD27-58367EFFE1A4"><p>Avoid unnecessary calls

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

where possible. </p> </li>

<li id="GUID-D1A62767-9CF7-5FBE-8F51-98EB154559C1"><p>Avoid changing the database

schema while other threads or processes might be using the database. </p> </li>

</ul> <p>Whenever the schema is reparsed, all statements that were prepared

using the old schema are invalidated. A call <codeph>Next()</codeph> or <codeph>Exec()</codeph> on

a <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> object that was prepared prior to the schema

reparse will return <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref> to let you know that

the cause of the error was a schema change. Thus any prepared statements that

were cached will have to be discarded and re-prepared following a schema change.

Refilling the prepared statement cache can be as computationally expensive

as parsing the schema in the first place. </p> <p>Prepared statements can

be invalidated for reasons other than schema changes including executing <codeph>Attach()</codeph> or <codeph>Detach()</codeph>.

Avoid this if you have a large cache of prepared statements. The best strategy

is to attach all associated databases as soon as a new database session is

opened and before any statements are prepared. </p> </section>

</conbody><related-links>

<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext>

</link>

<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server

Guide</linktext></link>

<link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext>

</link>

<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext>

</link>

<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient

Tables</linktext></link>

<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile

Corruption</linktext></link>

<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index

Tips</linktext></link>

<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion

Tips</linktext></link>

<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext>

</link>

<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement

Tips</linktext></link>

<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext>

</link>

<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE

Command</linktext></link>

<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE

CLause Tips</linktext></link>

</related-links></concept>