SELECT

2011. 02. 28
SQLiteによるSQLの解釈

SELECT

select-stmt:

select-stmt

select-core:

select-core

result-column:

result-column

join-source:

join-source

single-source:

single-source

join-op:

join-op

join-constraint:

join-constraint

ordering-term:

ordering-term

compound-operator:

compound-operator

SELECTステートメントはデータベースのクエリに使用します。

SELECTの結果は、各行が持つ固定列数のゼロ個以上のデータの行です。

SELECTステートメントは、SQL言語の中で最も複雑なコマンドです。

以降の説明を簡単にするため、一連の手順として決められている、SELECTステートメントによるデータの返し方をいくつかの段落に分けて以下に示します。

これらは完全に明らかにして念頭に置いておくことが重要で、実際にはSQLiteでも他のSQLエンジンでも、これまたは他の特定の工程に従う必要があります。


単純な選択処理

単純なSELECTステートメントの構文は、select-core syntax diagramで表現されています。

単純なSELECTステートメントの生成結果は、以下に示す4つの工程で表されます。
  1. FROM clause処理:単純なSELECTの入力データを決定します。
    入力データは、(FROM句が無い場合は)暗黙的に0列の単一行か、以下のように明示的にFROM句を指定したjoin-sourceを分析することによって決定されます。

  2. WHERE clause処理:入力データは、WHERE句の式を使用してフィルタリングします。

  3. GROUP BY, HAVING and result-column expression処理:結果行の設定は、GROUP BY句に基づいたデータと、フィルタリングされた入力データセットの行の結果セットの式の計算を結合することによって計算されます。

  4. DISTINCT/ALL keyword処理:クエリが『SELECT DISTINCT』クエリの場合、重複する行は結果行のセットから削除されます。
単純なSELECTステートメントには、結合と非結合の2種類のクエリがあります。

単純なSELECTステートメントは、GROUP BY句か結果セットに1つ以上の結合関数を含む場合は、結合クエリです。

それ以外の結合関数またはGROUP BY句を含まない単純なSELECTは、非結合クエリです。


1. 入力データの決定(FROM句処理)

単純なSELECTクエリを使用した入力データは、各M列幅のN行のセットです。

FROM句が単純なSELECTステートメントから省略されている場合、入力データは暗黙的に単一行、ゼロ列幅(即ちN=1、M=0)になります。

FROM句が指定されている場合、以降のFROMキーワードで指定された、1つ以上のテーブルまたはサブクエリ(カッコ内のSELECTステートメント)で処理された、単純なSELECTクエリのデータになります。

単純なSELECTステートメント内のFROM句以降のjoin-source内で指定されたsub-selectは、sub-selectステートメントの実行によって返されたデータを含むテーブルのように処理されます。

sub-selectデータセットの各列は、collation sequencesub-selectステートメント内の式に対するaffinityを継承します。

FROM句以降のjoin-source内に単一のテーブルのみがある場合、SELECTステートメント似よって使用された入力データは名付けられたテーブルの内容になります。

FROMキーワード以降のjoin-sourceの一部として指定されたテーブルが複数ある場合、名付けられた各テーブルの内容は単純なSELECTステートメント上で動作する単一のデータセットに結合されています。

正確に言うとデータは、接続しているテーブルまたはサブクエリと共に使用されている、特定のjoin-opjoin-constraintとどのように接続されているかに依存します。

SQLite内の全ての結合は、左右のデータセットのデカルト積に基づいています。

デカルト積のデータセットの列は、右側のデータセットの全ての列の以降に、左側のデータセットの全ての列という順序になります。

デカルト積のデータセット内の行の形式は、左側と右側のデータセットからそれぞれ一意の組み合わせで結合されます。

言い換えると、Mlhs列のNlhs行から成る左側のデータセットと、Mrhs列のNrhs行の右側のデータセットの場合、デカルト積はNlhsのデータセットになります。

Nrhs行は、それぞれMlhs+Mrhs列に含まれます。

join-opが『CROSS JOIN』、『INNER JOIN』、『JOIN』、またはコンマ『,』で、ONまたはUSING句が無い場合、結合結果は単純に左右のデータセットのデカルト積になります。

結合演算子の『INNER JOIN』と『JOIN』、そして『,』間に違いはありません。

結合演算子『CROSS JOIN』は、『INNER JOIN』、『JOIN』、『,』演算子と同じデータを生成しますが、クエリ最適化によって少し処理が異なります

それ以外の場合は、以下の箇条書き項目の詳細に応じてデカルト積は変わります。
  • ON句が指定されている場合、ON式はboolean expressionとしてデカルト積の各行毎に評価されます。
    全ての行の式の評価が偽の場合、データセットから除外されます。

  • join-constraintの一部としてUSING句が指定されている場合、join-opの左右両方のデータセット内にそれぞれ列名が指定されている必要があります。
    同名な列の各ペアのため、式『lhs.X = rhs.X』はboolean expressionとしてデカルト積のそれぞれの行毎に評価されます。
    式の評価で1つ以上偽がある場合、全ての行が結果セットから除外されます。
    USING句の結果として値を比較する時、処理の親和性のため通常の規則は、照合シーケンスとNULL値の比較で適用されます。
    結合演算子の左側のデータセットからの列は、照合シーケンスと親和性を優先する目的のため、比較演算子(=)の左側にあると見なされます。
    USING句による列のペア毎の比較は、結合されたデータセットから右側データセットからの列を省略しています。
    これはUSING句と同等のON制約との唯一の違いです。

  • join-opのいずれかにNATURALキーワードが追加されている場合、暗黙的にUSING句はjoin-constraintへ追加されます。
    暗黙的なUSING句には、各左右の入力データセット両方を表す列名が含まれています。
    左右の入力データセットに共通の列名が無い場合、NATURALキーワードは結合の結果に影響を与えません。
    USINGまたはON句は、NATURALキーワードを指定することによって結合に追加することはできません。

  • join-opが『LEFT JOIN』または『LEFT OUTER JOIN』の場合、ONまたはUSINGフィルタリング句が適用された後、余った行は(もしあれば)複合データセット内にある全ての行に対応する元の左側の入力データセット内の各行の出力に追加されます。
    追加された行には、通常右側の入力データセットからコピーされた値が含まれている可能性があり、その列にはNULL値が含まれています。
2つ以上のテーブルがFROM句の一部として一緒に結合されている時、結合操作は左から右へ順番に処理されます。

言い換えると、FROM句(A join-op-1 B join-op-2 C)は((A join-op-1 B) join-op-2 C)として計算されます。


2. WHERE句のフィルタリング

Where句が指定されている場合、WHERE式はboolean expressionとして入力データ内の各行毎に評価されます。

WHERE句の全ての行の式の評価が偽の場合、続行する前にデータセットから除外されます。


3. 結果行のセットの生成

もしWHERE句の式によってフィルタリングされたFROM句からの入力データがあれば、単純なSELECTのために結果行のセットが計算されます。

正確には、どのように実行されるかは単純なSELECTが結合か非結合クエリか、GROUP BY句が指定されているか否かに依存します。

SELECTとFROMキーワード間の式のリストは、結果式リストとして知られています。

結果式が特別な式『*』の場合、入力データの全ての列はその1つの式に代入されます。

式がテーブルのエイリアスまたは『.*』に続くFROM句内のサブクエリの場合、名付けられたテーブルからの全ての列またはサブクエリは、単一の式に代入されます。

結果式リスト以外の他のコンテキスト内で『*』または『alias.*』式を使用するとエラーになります。

また、FROM句を持っていない単純なSELECTクエリ内で『*』または『alias.*』式を使用するとエラーになります。

単純なSELECTステートメントによって返された行内の列数は、『*』や『alias.*』式の代入後の結果式リスト内の式の数と同じです。

各結果行は、入力データの単一行に対して、または行のグループに対する結合クエリのため、結果式リスト内の式を評価することによって計算されます
  • SELECTステートメントがa non-aggregate queryの場合、結果式リスト内の各式はWHERE句によってフィルタリングされたデータセット内の各行に対して評価されます。

  • SELECTステートメントがan aggregate query without a GROUP BY句の場合、結果セット内の各結合式はデータセット全体に対して一度だけ評価されます。
    結果セット内の各非結合式はデータセットの任意に選択された行に対して一度評価されます。
    同じ任意選択された行は、各非結合式に使用されます。
    または、データセットがゼロ行を含む場合、各非結合式は完全にNULL値で構成される行に対して評価されます。

    結果セットのデータの単一行は、GROUP BY句を除いた結合クエリの結果で作られた、結果セット内の結合と非結合式の評価によって生成されます。
    GROUP BY句を除いた結合クエリは、入力データの行がゼロであっても、常にデータの1つの行を正確に返します。

  • SELECTステートメントがan aggregate query with a GROUP BY句の場合、GROUP BY句の一部として指定された式毎に、データセットの各行を評価します。
    各行はその結果に基づいて『group』に割り当てられます。
    (GROUP BY式を評価した結果の行は、同じグループに割り当てられている同義です)
    グループ行の目的のため、NULL値は同じとみなされます。
    GROUP BY句内の式を評価する時に、どのテキスト値を適用するかを比較する、selecting a collation sequenceのための通常の規則です。
    GROUP BY句内の式は、結果内に表示される式を持ちません。
    GROUP BY句内の式は、式を結合しない場合があります。

    HAVING句が指定されている場合、boolean expressionとして行の各グループは一度評価されます。
    HAVING句の評価結果が偽の場合、グループは破棄されます。
    HAVING句が結合式の場合、グループ内の全ての行に対して評価されます。
    HAVING句が非結合式の場合、グループから任意に選択された行に対して評価されます。
    その結果に含まれていない結合関数でも、HAVING式は値を参照することがあります。

    結果セット内の各式は、行の各グループ毎に一度評価されます。
    式が結合式の場合、グループ内の全ての行に対して評価されます。
    それ以外の場合、グループ内から単一の任意に選択された行に対して評価されます。
    結果セット内に非結合式1つ以上ある場合、全ての式は同じ行に対して評価されます。

    入力データセットの行の各グループは、結果の行のセットに単一の行を提供します。
    DISTINCTキーワードに結合したフィルタリングでは、GROUP BY句を伴う結合クエリによって返される行数は、GROUP BYとHAVING句をフィルタリングされた入力データセットに適用することによって生成された行のグループ数と同じです。

4. 重複する行の除去(DISTINCT処理)

ALLまたはDISTINCTキーワードの1つは、単純なSELECTステートメント内のSELECTキーワードに続きます。

単純なSELECTがSELECT ALLの場合、結果行のセット全体がSELECTによって返されます。

ALLまたはDISTINCTのどちらかが存在する場合、動作はALLが指定されている場合と同じです。

単純なSELECTがSELECT DISTINCTの場合、重複している行は返される前に結果行のセットから削除されます。

重複している行を検出する目的のため、2つのNULL値は同じと見なされます。

照合順序を選択するための通常の規則を適用すると、テキスト値を比較することができます。


複合選択ステートメント

2つ以上の単純なSELECTステートメントは、UNION、UNION ALL、INTERSECTまたはEXCEPT演算子を使用して、接続して合わせた複合SELECTを形成することができます。

複合SELECTでは、全てのSELECTの構成要素は結果の列と同じ数を返す必要があります。

複合SELECTの構成要素は、単純なSELECTステートメントである必要があり、ORDER BYまたはLIMIT句が含まれない可能性があります。

ORDER BYとLIMIT句は、複合SELECT全体の最後に現れる可能性があります。

UNION ALL演算子を使用して生成された複合SELECTは、UNION ALL演算子の左側にSELECTからの全ての行と、その右側にSELECTからの全ての行を返します。

UNION演算子は、重複する行が最終的な結果セットから削除されることを除いて、UNION ALL と同じように動作します。

INTERSECT演算子は、SELECTの左右の結果の積集合を返します。

EXCEPT演算子は、SELECTの右側によって返されず、SELECTの左側によって返される行のサブセットを返します。

重複する行は、結果セットを返す前に、INTERSECTとEXCEPT演算子の結果から削除されます。

複合SELECT演算子の結果から重複する行を決定する目的のため、NULL値は他のNULL値と異なる全ての非NULL値とと同じと見なされます。

2つのテキスト値の比較に使用される照合シーケンスは、優先順位で後に付加したCOLLATE演算子で指定した照合シーケンスに割り当てられないことを除けば、SELECTステートメントの左右の列がイコール演算子『=』の左右のオペランドのように決定されます。

複合SELECTの一部として行を比較する時、親和性変換は任意の値へ適用されません。

3つ以上の単純なSELECTが複合SELECTに接続されている時、左から右にまとめられます。

言い換えると、『A』、『B』と『C』が全て単純なSELECTステートメントの場合、(A op B op C)は((A op B) op C)として処理されます。


ORDER BYとLIMIT/OFFSET句

ORDER BY句を持たない複数の行を返すSELECTステートメントの場合、行が返される順序は未定義です。

また、SELECTステートメントがORDER BY句を持っている場合、行がユーザに返される順序は、ORDER BYに接続されている式のリストで決定されます。

行は最初にORDER BYリストの最も左側にある式を評価した結果に基づいて並べ替えられ、その関係は左側にある2番目以降の式の評価によって分割されます。

同じ値に評価される全てのORDER BY式の2つの行の順序は未定義です。

各ORDER BY式は、キーワードASC(小さい値が最初に返される)またはDESC(大きい値が最初に返される)のどちらかがオプションで続きます。

ASCまたはDESCのどちらかが指定されていない場合、行はデフォルトで昇順(小さい値が最初に)並べ替えられます。

各ORDER BY式は次のように処理されます。
  1. ORDER BY式が定数の整数Kの場合、式は結果セットのK番目の列のエイリアスと見なされます(列は左から右に1から始まる番号が付けられる)。

  2. ORDER BY式が出力列のいずれかのエイリアスに対応すると識別された場合、式は列のエイリアスと見なされます。

  3. それ以外のORDER BY式がその他の式の場合は、その評価と出力行の順序付けに使用された値を返します。
    SELECTステートメントが単純なSELECTの場合、ORDER BYは任意の式を含むことができます。
    ただしSELECTが複合SELECTの場合、ORDER BY式は出力列のエイリアスではなく、出力列として使用された式と正確に同じである必要があります。
並べ替える行の目的のため、値は比較式と同じ方法で比較されます。

2つのテキスト値の比較に使用される照合シーケンスは、次のように決定されます。
  1. ORDER BY式が、接尾辞COLLATE operatorを使用して照合シーケンスを割り当てた場合、指定された照合シーケンスが使用されます。

  2. それ以外のORDER BY式が接尾辞COLLATE operatorを使用して照合シーケンスを割り当てた式のエイリアスの場合、エイリアスの式に割り当てられた照合シーケンスが使用されます。

  3. それ以外のORDER BY式が列または列の式のエイリアスの場合、列のデフォルトの照合シーケンスが使用されます。

  4. それ以外の場合、BINARY照合シーケンスが使用されます。
複合SELECTステートメントでは、全てのORDER BY式は複合SELECTの結果列の1つのエイリアスとして処理されます。

ORDER BY式が整数エイリアスでない場合、SQLiteは上記の2または3のルールのどちらかに一致する結果列の複合内にある、SELECTの最も左側を検索します。

一致するものが見つかった場合、検索を停止し、式は照合されている結果列のエイリアスとして処理されます。

それ以外の場合、次の右にあるSELECTを試します。

構成しているSELECTの結果列内に一致する式が見つからなかった場合、エラーとなります。

ORDER BY句の各用語は個々に処理され、複合内の異なるSELECTステートメントからの結果列と照合することができます。

LIMIT句はSELECTステートメントによって返された行数の上限に配置するために使用されます。

任意のスカラー式は、整数または整数に可逆変換可能な値を評価するのに、LIMIT句内で使用することができます。

式がNULL値または整数に可逆変換できない他の値を評価する場合はエラーが返されます。

LIMIT式の評価が負の値の場合、返される行数に上限はありません。

それ以外の場合、LIMIT式が評価した値をNとすると、SELECTはその結果セットの最初のN行だけを返します。

また、SELECTステートメントがLIMIT句を除いたN未満の行を返す場合、結果セット全体が返されます。

式にはLIMIT句に続けてオプションでOFFSET句を接続することができ、整数または整数に可逆変換できる値に評価する必要があります。

式がOFFSET句を持つ場合、OFFSETとLIMIT句を評価した値をそれぞれMとNとすると、最初のM行はSELECTステートメントによって返された結果セットから省略され、次のN行が返されます。

またSELECTがLIMIT句を持っていない場合にM+N行未満を返す場合、最初のM行はスキップされ、(もしあれば)残った行が返されます。

OFFSET句の評価が負の値の場合、結果は評価がゼロの場合と同じになります。

OFFSET句の代わりに、LIMIT句はコンマによって区切られた2つのスカラー式を分割することができます。

この場合、最初の式はOFFSET式として使用され、2番目はLIMIT式となります。

これはOFFSET句に使用する時に、2つの式の最初がLIMIT、2番目がOFFSETとなり、直感的に反します。

これは意図的なもので、他のSQLデータベースシステムとの互換性を最大化します。



参考文献

SQL As Understood By SQLite : SELECT

0 CommentsPosted in SQLite

CREATE TABLE

2011. 02. 26
SQLiteによるSQLの解釈

CREATE TABLE

create-table-stmt:

create-table-stmt

column-def:

column-def

type-name:

type-name

column-constraint:

column-constraint

table-constraint:

table-constraint

foreign-key-clause:

foreign-key-clause

『CREATE TABLE』コマンドは、SQLiteデータベース内に新しいテーブルを生成します。

CREATE TABLEコマンドは、以下の新しいテーブルの属性を指定します。
  • 新しいテーブルの名前

  • 新しいテーブルを生成させるデータベース。
    テーブルは、メインデータベース、一時データベース、または接続されている全てのデータベース内に作成できます。

  • テーブル内の各列の名前

  • テーブル内の各列の宣言された型

  • テーブル内の各列へのデフォルト値、または式

  • 各列で使用するデフォルトの照合順序

  • オプションで、テーブルへのPRIMARY KEY。
    単一列や複合(複数列)のプライマリキーをサポートします。

  • 各テーブル用のSQLの制約の設定。
    SQLiteは、UNIQUE、NOT NULL、CHECK、FOREIGN KEY制約をサポートします。
全てのCREATE TABLEステートメントは、新しいテーブルの名前を指定する必要があります。

『sqlite_』で始まるテーブル名は、内部で使用するために予約されています。

『sqlite_』で始まる名前でテーブルを生成しようとするとエラーになります。

<database-name>が指定されている場合、attached databaseの名前を『main』または『temp』のどちらかに指定する必要があります。

この場合、新しいテーブルは指定されたデータベース内に生成されます。

『CREATE』と『TABLE』の間に『TEMP』または『TEMPORARY』キーワードがある場合、新しいテーブルは一時データベースに生成されます。

<database-name>とTEMPまたはTEMPORARYキーワードの両方を指定した場合、<database-name>が『temp』でない限りエラーとなります。

データベース名が指定されていなく、TEMPキーワードが存在しない場合、メインデータベースにテーブルが生成されます。

通常、同じ名前のインデックスやビューのテーブルが既に含まれているデータベース内に新しいテーブルを生成しようとするとエラーになります。

しかし、CREATE TABLEステートメントの一部に『IF NOT EXISTS』句が指定されていて、同じ名前のテーブルまたはビューが既に存在している場合、CREATE TABLEコマンドは単純に(エラーメッセージを返すこと無く)無効となります。

インデックスが既に存在しているために生成できなかった場合、『IF NOT EXISTS』句が指定されていてもエラーが返されます。

同じ名前のtriggerが存在するテーブルを生成してもエラーにはなりません。

テーブルはDROP TABLEステートメントを使用して削除されます。


CREATE TABLE ... AS SELECTステートメント

『CREATE TABLE ... AS SELECT』ステートメントは、SELECTステートメントの結果に基づいてデータベースにテーブルを生成します。

テーブルはSELECTステートメントによって返される行と同じ数の列を持ちます。

各列の名前は、SELECTステートメントの結果セット内の列に対応する同じ名前になります。

各列の宣言された型は、下表で示すようにSELECTステートメントの結果セット内にある対応する式の、expression affinityによって決定されます。

Expression AffinityColumn Declared Type
TEXT"TEXT"
NUMERIC"NUM"
INTEGER"INT"
REAL"REAL"
NONE""(空文字列) 

CREATE TABLE ASを使用して生成されたテーブルは、PRIMARY KEYやあらゆる種類の制約を持ちません。

各列のデフォルト値はNULLです。

新しいテーブルの各列へのデフォルトの照合順序はBINARYです。

CREATE TABLE ASを使用して生成されたテーブルは、最初にSELECTステートメントによって返されるデータの行が格納されます。

行はSELECTステートメントによって返される順序で、1から始まるrowid値を昇順で連番で割り当てられます。


列の定義

CREATE TABLE ... AS SELECTステートメントでない限り、CREATE TABLEには1つ以上のcolumn definitionsが含まれており、オプションでtable constraintsのリストが続きます。

各列の定義は、列の名前、オプションで列の宣言された型が続き、1つ以上のcolumn constraintsで校正されます。

前のステートメントの目的のため、『column constraints』の定義にはCOLLATEとDEFAULT句が含まれていますが、これらは実際には制約として解釈されず、テーブルに含まれているかもれしれないデータを制限しません。

その他の制約(NOT NULL、CHECK、UNIQUE、PRIMARY KEY、FOREIGN KEY制約)はテーブルデータに制約を課しており、下記のSQL Data Constraintsに記述されています。

ほとんどのSQLデータベースとは異なり、SQLiteは列の宣言された型に基づいた列に挿入するデータの型を制限しません。

代わりに、SQLiteはdynamic typingを使用します。

列の宣言された型は列のaffinityの決定のみに使用されます。

DEFAULT句は、INSERTを実行する時にユーザによって明示的に提供された値が無い場合、列に使用するデフォルト値を指定します。

列の定義に接続された明示的なDEFAULT句が無い場合、列のデフォルト値はNULLになります。

明示的なDEFAULT句のデフォルト値には、NULL、文字列定数、blob定数、符号付き数値、カッコで囲んだ定数式を指定します。

明示的なデフォルト値はまた、特別なcase-independentキーワードとしてCURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMPのいずれかになります。

DEFAULT句の目的のため、式はサブクエリまたは二重引用符で囲んだ文字列定数を含まない定数が提供されていると見なされます。

全てのテーブルの列の明示的な値は、INSERTステートメントによってテーブルに行が挿入される度に提供はされず、以下のように新しい行に格納されている値はデフォルト値によって決定されます。
  • 列のデフォルト値が定数NULL、テキスト、blobまたは符号付き数値の場合、値は新しい行に直接使用されます。

  • 列のデフォルト値がカッコで囲んだ式の場合、挿入された各行のために一度式を計算し、その結果を新しい行に使用します。

  • 列のデフォルト値がCURRENT_TIME、CURRENT_DATE、CURRENT_DATETIMEの場合、値は現在のUTCの日時を表現したテキストを新しい行に使用します。
    CURRENT_TIMEの値のフォーマットは『HH:MM:SS』で、CURRENT_DATEは『YYYY-MM-DD』、CURRENT_TIMESTAMPは『YYYY-MM-DD HH:MM:SS』となります。
COLLATE句は、列のデフォルトの照合順序として使用する照合順序の名前を指定します。

COLLATE句が指定されていない場合、デフォルトの照合順序はBINARYです。

テーブルの列数は、コンパイル時の引数SQLITE_MAX_COLUMNによって制限されています。

テーブルの単一行は、SQLITE_MAX_LENGTHのバイト以上のデータを格納することはできません。

これら2つの制限は、C/C++インターフェイスのsqlite3_limit()を使用して、実行時に下げることができます。


SQLデータの制約

SQLiteの各テーブルは最大で1つのPRIMARY KEYを持つことができます。

キーワードPRIMARY KEYが列定義に追加されている場合、テーブルのプライマリキーが単一列で構成されます。

または、PRIMARY KEY句がtable-constraintとして指定されている場合は、テーブルのプライマリキーはPRIMARY KEY句の一部として指定された指定された列のリストで構成されます。

単一のCREATE TABLEステートメント内に1つ以上のPRIMARY KEY句がある場合はエラーになります。

テーブルが単一列のプライマリキーを持ち、列の宣言された型が『INTEGER』の場合、その列はINTEGER PRIMARY KEYとして知られています。

INTEGER PRIMARY KEYに関連する特別なプロパティや動作についての詳細は、下記を参照してください。

プライマリキーを持つテーブルの各行は、プライマリキー列内の値の特有な組み合わせを特徴としています。

プライマリキー値の独自性を決定するものとして、NULL値は他のNULLを含む全ての他の値と異なると考えられています。

同一のプライマリキー値で2つ以上の行を特徴づけるために、INSERTまたはUPDATEステートメントでテーブル内容の変更を試みた場合は制約違反になります。

SQL標準では、PRIMARY KEYは常にNOT NULLを含める必要があります。

残念ながら、長い期間コーディングを見過ごしていたために、SQLiteには該当しません。

SQLiteでは列がINTEGER PRIMARY KEYでない限り、PRIMARY KEY列内のNULL値を許容します。

我々はSQLiteを標準に準拠するよう変更することができます(将来的に行う可能性があります)が、見出すまでの見過ごされていた期間、SQLiteは広範でしようされており、この問題を修正することで従来のコードが破損することが懸念されます。

そのため現在は、PRIMARY KEY列内でのNULLの許容を続けることを選択しました。

開発者は承知した上で、
将来的にSQLiteがSQL標準に準拠し、それに応じて新たにプログラムを設計する必要があることを、開発者は心得ておく必要があります。

UNIQUE制約は、単一のテーブルがたくさんのUNIQUE制約を持つことを除いて、PRIMARY KEY制約に似ています。

テーブルの各UNIQUE制約は、各行をUNIQUE制約によって識別された列内の値の特有の組み合わせで特徴づける必要があります。

PRIMARY KEY制約と同様に、UNIQUE制約の目的のため、NULL値は(他のNULLを含む)全ての他の値と異なると考えられています。

UNIQUE制約への条件として、列の設定内を同一の値で2つ以上の行を特徴づけるために、INSERTまたはUPDATEステートメントでテーブル内容の変更を試みた場合は制約違反になります。

INTEGER PRIMARY KEY列を別として、UNIQUEとPRIMARY KEY制約は両方とも(『CREATE UNIQUE INDEX』ステートメントを用いた同じ方法で)データベース内にインデックスを生成することで実装されています。

このようなインデックスは、クエリの最適化にデータベース内の他のインデックスと同様に使用されます。

その結果、UNIQUEまたはPRIMARY KEY制約の対象として、既にひとまとめにされている列の設定で、インデックスを生成することの優位性(ただしかなりのオーバーヘッド)は多くありません。

CHECK制約は、列の定義として付随、またはテーブルの制約として指定することができます。

実際には作成に違いはありません。

テーブルに新しい行を挿入または既存の行を更新する度に、式に関連付けされた各CHECK制約の数値を求め、CAST expressionと同様にNUMERIC値にキャストします。

結果がゼロ(整数値0または実数値0.0)の場合、制約違反が発生します。

CHECK式の数値がNULLまたはその他のゼロ以外の値の場合は、制約違反ではありません。

CHECK制約はversion 3.3.0以降からサポートされています。

version 3.3.0より前では、CHECK制約は構文解析が適用されません。

NOT NULL制約は、列の定義として付随させることができますが、テーブルの制約として指定することはできません。

当然のことながら、NOT NULL制約は関連付けされた列がNULL値を含まないことを指示します。

新しい行を挿入する、または既存の制約違反の原因の1つを更新する時に、列の値をNULLに設定しようと試みます。

正確には制約違反がconstraint conflict resolution algorithmによって決定される処理方法を指定します。

各PRIMARY KEY、UNIQUE、NOT NULLそしてCHECK制約は、デフォルトの競合解決アルゴリズムを持ちます。

PRIMARY KEY、UNIQUEそしてNOT NULL制約は、定義内にconflict-clauseを含んでいることによって、デフォルトの競合解決アルゴリズム明示的に割り当てることができます。

また、制約の定義にconflict-clauseを含まない、あるいはCHECK制約の場合、デフォルトの競合解決アルゴリズムはABORTです。

同じテーブル内でも異なる制約は、異なるデフォルトの競合解決アルゴリズムを持っている可能性があります。

追加情報についてはON CONFLICTの項を参照してください。


ROWIDとINTEGER PRIMARY KEY

全てのSQLiteテーブルの全ての行は、テーブル内の行を一意に識別する64ビットの符号付き整数を持ちます。

この整数を通常『rowid』と呼びます。

rowid値は、列名の代わりに『rowid』、『oid』、または『_rowid_』といった特殊なcase-independent名の一つを使用してアクセスすることができます。

テーブルのユーザが定義した列名に『rowid』、『oid』、または『_rowid_』が含まれている場合、その名前は常に明示的に宣言された列を参照しており、整数のrowid値の取得に使用することはできません。

SQLiteの各テーブルのデータは、rowidをキーとして使用し、各テーブルの行のエントリを含むBツリー構造体として格納されます。

これはrowidによるレコードの取得や並べ替えが高速であることを意味します。

指定したrowidのレコード、または指定した範囲内にあるrowidを持つ全てのレコードの検索は、他のPRIMARY KEYまたはインデックス値を指定した類似の検索よりも約2倍高速です。

1つの例外を除いて、テーブルが単一列で構成されているプライマリキーを持ち、その列の宣言された型が大文字と小文字の混合物中の『INTEGER』である場合、その列はrowidのエイリアスとなります。

このような列を通常『整数プライマリキー』と呼びます。

PRIMARY KEY列のみ、宣言された型名が正確に『INTEGER』の場合に整数プライマリキーになります。

その他の『INT』や『BIGINT』、『SHORT INTEGER』、『UNSIGNED INTEGER』のような整数型名は、rowidのエイリアスとしてではなく、プライマリキーの列は整数のaffinityや唯一のインデックスを持つ通常のテーブル列として動作します。

上述での例外は、『PRIMARY KEY DESC』句を含む宣言された型『INTEGER』を持つ列の宣言の場合で、rowidのエイリアスとはならず、整数プライマリキーとして分類されません。

この特異性は設計によるものではありません。

これはSQLiteの初期バージョンのバグが原因です。

バグを修正すると、その結果として非常に深刻な後方互換性の問題が発生する可能性があります。

SQLiteの開発者は、角にあるような馬鹿げたことにこだわって互換性を破壊より、元の動作を保持した方が遥かに良いと感じています。

以下の3つのテーブルの宣言では、列『x』は全てrowidのエイリアス(整数プライマリキー)になります。
  • CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
  • CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
  • CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));
しかし以下の宣言では、『x』はrowidのエイリアスにはなりません。
  • CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
rowid値は他の列値と同様に、UPDATEステートメントを使用して変更する場合があり、組み込みのエイリアス(『rowid』、『oid』または『_rowid_』)か、または整数プライマリキーによって生成されたエイリアスのどちらかを使用することができます。

同様に、INSERTステートメントで挿入された各行のrowidとして使用する値を提供することがあります。

通常のSQLite列とは異なり、整数プライマリキーまたはrowid列は整数値を含める必要があります。

整数プライマリキーやrowid列は、浮動小数点値、文字列、BLOB、またはNULLを保持することはできません。

UPDATEステートメントで、整数プライマリキーやrowid列にNULLまたはblob値、あるいは整数に可逆変換できない文字列または実数を設定しようとした場合、『datatype mismatch』エラーが発生してステートメントは終了します。

INSERTステートメントで、整数に可逆変換できないblob値や文字列、実数を整数プライマリキーまたはrowidの列に挿入しようとした場合、『datatype mismatch』エラーが発生してステートメントは終了します。

INSERTステートメントで、rowidや整数プライマリキーの列にNULL値を挿入しようとした場合、システムは自動的にrowidとして使用する整数値を選択します。

この方法の詳細についてはseparatelyで提供されています。

foreign key constraintparent keyは、rowidを使用することができません。

parent keyは列名のみ使用する必要があります。



参考文献

SQL As Understood By SQLite : CREATE TABLE

0 CommentsPosted in SQLite

REPLACE

2011. 02. 25
SQLiteによるSQLの解釈

REPLACE

REPLACEコマンドはINSERTコマンドの『INSERT OR REPLACE』の別名です。

この別名は他のSQLデータベースエンジンとの互換性のために提供されています。

追加情報についてはINSERTコマンドのドキュメントを参照してください。



参考文献

SQL As Understood By SQLite : REPLACE

0 CommentsPosted in SQLite

INSERT

2011. 02. 24
SQLiteによるSQLの解釈

INSERT

insert-stmt

insert-stmt

INSERTステートメントは3つの基本的な形式で提供されています。

  • 最初の形式は(『VALUES』キーワードを使用して)既存のテーブルに単一の新しい行を生成します。
    列リストが指定されていない場合、値の数はテーブルの列数と同じである必要があります。
    この場合、VALUESリストの一番左の式を評価した結果は、新しい行の一番左の列の中に挿入されます。
    列リストが指定されている場合、指定した列の数と値の数が一致する必要があります。
    新しい行の各列名は、対応する値の式を評価した結果が格納されます。
    列リストに表示されないテーブルの列は(CREATE TABLEステートメントの一部として指定した)デフォルトの列の値、または指定したデフォルト値が無い場合はNULLを格納します。

  • INSERTステートメントの二番目の形式は、VALUES句の代わりにSELECTステートメントが含まれています。
    新しいエントリは、SELECTステートメントを実行することによって返されるデータの各行毎に、テーブル内に挿入されます。
    列リストが指定されている場合、SELECTの結果内の列数は列リスト内の項目数と同じである必要があります。
    それ以外の列リストが指定されていない場合、SELECTの結果内の列数はテーブル内の列数と同じである必要があります。
    全てのSELECTステートメントはSELECTのコンパウンドとORDER BYやLIMIT句を伴うSELECTステートメントを含み、この形式のINSERTステートメント内で使用することができます。

  • INSERTステートメントの三番目の形式はDEFAULT VALUESを伴うものです。
    INSERT~DEFAULT VALUESステートメントは、名付けたテーブル内に単一の新しい行を挿入します。
    新しい行の各列はデフォルト値、またはCREATE TABLEステートメント内に列定義の一部として指定したデフォルト値が無い場合はNULLを格納します。
オプションのconflict句は、このINSERTコマンドの実行時に使用する、制約を選択する競合解決アルゴリズムを指定することができます。

追加情報はON CONFLICTの項目を参照してください。

MySQLとの互換性のため、構文解析プログラムは『INSERT OR REPLACE』の別名として単一のキーワードREPLACEを使用することができます。

table-nameでのオプションの『database-name.』接頭辞は、最上位レベルのINSERTステートメントのみサポートします。

テーブル名はCREATE TRIGGERステートメント内で発生するINSERTステートメントのため、非修飾である必要があります。

同様に、INSERTステートメントの『DEFAULT VALUES』形式は、最上位レベルのINSERTステートメントのみサポートし、トリガのINSERTステートメントはサポートしません。



参考文献

SQL As Understood By SQLite : INSERT

0 CommentsPosted in SQLite

SQLiteによるSQLの解釈

2011. 02. 23
SQLiteは標準SQL言語のほとんどを解釈できます。

ただし、いくつかの機能が省略されているのと同時にいくつかの機能が追加されています。

このドキュメントではSQL言語の内、SQLiteでのサポートの有無の部分を正確に記述したいと思います。

またSQLキーワードのリストも提供します。

SQL言語の構文は構文図で記述します。

以下に利用できる構文のドキュメントの題目を示します。

aggregate functions    CREATE VIRTUAL TABLE    INSERT
ALTER TABLEdate and time functionskeywords
ANALYZEDELETEON CONFLICT clause
ATTACH DATABASEDETACH DATABASEPRAGMA
BEGIN TRANSACTIONDROP INDEXREINDEX
commentDROP TABLERELEASE SAVEPOINT
COMMIT TRANSACTIONDROP TRIGGERREPLACE
core functionsDROP VIEWROLLBACK TRANSACTION
CREATE INDEXEND TRANSACTIONSAVEPOINT
CREATE TABLEEXPLAINSELECT
CREATE TRIGGERexpressionUPDATE
CREATE VIEWINDEXED BYVACUUM

sqlite3_prepare_v2()sqlite3_prepare()sqlite3_prepare16()sqlite3_prepare16_v2()sqlite3_exec()sqlite3_get_table()ルーチンは、セミコロンで区切られたステートメントのリストであるSQLステートメントリスト(sql-stmt-list)を受け入れます。

sql-stmt-list

sql-stmt-list

ステートメントリスト内の各SQLステートメントのインスタンスを以下に示します。

sql-stmt

sql-stmt



参考文献

SQL As Understood By SQLite

0 CommentsPosted in SQLite

準備ステートメントオブジェクトの破棄

2011. 02. 22
int sqlite3_finalize(sqlite3_stmt *pStmt);

sqlite3_finalize()関数はprepared statementを削除するために呼び出します。

遭遇した最新のステートメントの評価にエラーが無い、またはステートメントが評価されていない場合、sqlite3_finalize()はSQLITE_OKを返します。

ステートメントSの最新の評価が失敗の場合、sqlite3_finalize(S)は適切なerror codeまたはextended error codeを返します。

sqlite3_finalize(S)ルーチンはステートメントSを評価する前や、一回以上sqlite3_reset()を呼び出した後、またはsqlite3_step()の任意の呼び出した後など、ステートメントが実行を完了しているかどうかに関係無く、必ずSのprepared statementのライフサイクル中の任意の時点で呼び出すことができます。

NULLポインタでのsqlite3_finalize()の呼び出しは、何もせず安全です。

アプリケーションはリソースのリークを避けるため、全てのprepared statementを完了させる必要があります。

完了した後にprepared statementの使用を試みると、アプリケーションに重大なエラーとなります。

完了した後にprepared statementを使用した結果は未定義で、セグメンテーション違反やヒープ破損など望ましくない動作が発生します。

ObjectsConstantsFunctionsの一覧を参照してください。



参考文献

Destroy A Prepared Statement Object

0 CommentsPosted in SQLite

メモリ割り当てサブシステム

2011. 02. 21
void *sqlite3_malloc(int);
void *sqlite3_realloc(void*, int);
void sqlite3_free(void*);

SQLiteのコアは、必要な全ての自身の内部メモリの割り当てに、これら3つのルーチンを使用します。

前文の『コア』は、オペレーティングシステムの特定のVFSの実装を含みません。

WindowsのVFSは、いくつかの操作にネイティブのmalloc()とfree()を使用します。

sqlite3_malloc()ルーチンは引数をNとすると、少なくとも長さがNバイトのメモリのブロックへのポインタを返します。

sqlite3_malloc()が十分な空きメモリを取得できなかった場合は、NULLポインタを返します。

sqlite3_malloc()の引数Nがゼロまたは負の場合、sqlite3_malloc()はNULLポインタを返します。

事前にsqlite3_malloc()またはsqlite3_realloc()によって返されたポインタでsqlite3_free()を呼び出すと、再利用するためのメモリを解放します。

sqlite3_free()ルーチンは、NULLポインタを指定して呼び出した場合は何もしません。

sqlite3_free()にNULLポインタを渡しても無害です。

解放した後、メモリは読み込みも書き込みもできません。

仮に事前に解放されたメモリを読み込むと、セグメンテーション違反や他の重大なエラーが発生することがあります。

sqlite3_malloc()またはsqlite3_realloc()から取得していない非NULLポインタでsqlite3_free()を呼び出した場合、メモリの破損やセグメンテーション違反、または他の重大なエラーが発生する可能性があります。

sqlite3_realloc()インターフェイスは第2引数をNとすると、メモリを割り当てる前に少なくともNバイトでリサイズを試みます。

リサイズされるメモリ割り当ては第1引数です。

sqlite3_realloc()の第1引数がNULLポインタの場合、sqlite3_realloc()の第2引数Nとすると、その動作はsqlite3_malloc(N)の呼び出しと同じです。

sqlite3_realloc()の第2引数がゼロまたは負の場合、sqlite3_realloc()の第1引数をPとすると、その動作はsqlite3_free(P)の呼び出しと正確に同じです。

十分なメモリが確保できない場合、sqlite3_realloc()は少なくともNバイトを割り当てたメモリのポインタ、またはNULLを返します。

事前に割り当てたサイズをMとすると、事前に割り当てたmin(N,M)バイトはsqlite3_realloc()によって返されるバッファの先頭にコピーされ、事前に割り当ては解放されます。

sqlite3_realloc()がNULLを返した場合、事前の割り当ては解放されません。

sqlite3_malloc()とsqlite3_realloc()によって返されたメモリは、常に少なくとも8バイト区切りで、またはコンパイル時のオプションでSQLITE_4_BYTE_ALIGNED_MALLOCが使用されている場合は4バイト区切りで配置されます。

SQLiteのバージョン3.5.0と3.5.1では、組み込みで実装されているSQLITE_OMIT_MEMORY_ALLOCATIONを定義することができ、これらのルーチンを省略することができました。

その機能はもう提供されていません。

組み込みのメモリアロケータでのみ使用することができます。

Windows OSインターフェイスのレイヤーは、SQLiteで使用されるUTF-8エンコーディングとファイル名を変換する時にシステムのmalloc()とfree()を直接呼び出し、特定のWindowsインストールでファイル名の変換に使用されています。

メモリ割り当てエラーが検出された場合、SQLITE_NOMEMよりもSQLITE_CANTOPENSQLITE_IOERRとして報告されます。

sqlite3_free()とsqlite3_realloc()への引数のポインタは、NULLあるいはまだ解放されていない事前のsqlite3_malloc()やsqlite3_realloc()の呼び出しで取得したポインタのどちらかである必要があります。

アプリケーションは、sqlite3_free()またはsqlite3_realloc()を使用して解放された後のメモリブロックの任意の部分に、読み込みまたは書き込みをしてはいけません。

ObjectsConstantsFunctionsの一覧を参照してください。



参考文献

Memory Allocation Subsystem

Wikipedia/仮想ファイルシステム

0 CommentsPosted in SQLite

クエリからの結果値

2011. 02. 20
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);

これらのルーチンは『結果セット』インターフェイスから成ります。

これらのルーチンは、クエリの現在の結果行の単一列に関する情報を返します。

全ての場合において第1引数は評価されるprepared statementのポインタ(sqlite3_prepare_v2()またはその変種の一つから返されるsqlite3_stmt*)で、第2引数は情報を返す必要がある列のインデックスです。

結果セットの左端の列のインデックスは0です。

結果内の列数は、sqlite3_column_count()を使用して決定することができます。

SQLステートメントが現在有効な行を指していない、または列のインデックスが範囲外の場合、結果は未定義になります。

これらのルーチンは、最新のsqlite3_step()の呼び出しでSQLITE_ROWが返された時のみ呼び出される可能性があり、その後に呼び出されるsqlite3_reset()でもsqlite3_finalize()でもありません。

これらのルーチンのいずれかがsqlite3_reset()sqlite3_finalize()を呼び出した後、またはsqlite3_step()SQLITE_ROW以外の何かを返された後の場合、結果は未定義です。

sqlite3_step()sqlite3_reset()またはsqlite3_finalize()を異なるスレッドから呼び出した場合、これらのルーチンは保留されますが、結果は未定義です。

sqlite3_column_type()ルーチンは、結果列の初期データ型のためのdatatype codeを返します。

戻り値は、SQLITE_INTEGERSQLITE_FLOATSQLITE_TEXTSQLITE_BLOB、またはSQLITE_NULLのいずれかになります。

sqlite3_column_type()による戻り値は、後述するような型変換が生じない場合のみ意味を持ちます。

型変換後、sqlite3_column_type()による戻り値は未定義になります。

SQLiteの将来のバージョンでは型変換後のsqlite3_column_type()の動作が変更される場合があります。

結果がBLOBまたはUTF-8文字列の場合、sqlite3_column_bytes()ルーチンはBLOBまたは文字列内のバイト数を返します。

結果がUTF-16文字列の場合、sqlite3_column_bytes()は文字列をUTF-8に変換し、バイト数を返します。

結果が数値の場合、sqlite3_column_bytes()はsqlite3_snprintf()を使用して値をUTF-8文字列に変換し、文字列のバイト数を返します。

結果がNULLの場合、sqlite3_column_bytes()はゼロを返します。

結果がBLOBまたはUTF-16文字列の場合、sqlite3_column_bytes16()ルーチンはBLOBまたは文字列のバイト数を返します。

結果がUTF-8文字列の場合、sqlite3_column_bytes16()は文字列をUTF-16文字列に変換し、バイト数を返します。

結果が数値の場合、sqlite3_column_bytes16()はsqlite3_snprintf()を使用して値をUTF-16文字列に変換し、文字列のバイト数を返します。

結果がNULLの場合、sqlite3_column_bytes16()はゼロを返します。

sqlite3_column_bytes()sqlite3_column_bytes16()によって返される値は、文字列の末端にあるゼロ終端を含みません。

分かり易く言うとsqlite3_column_bytes()sqlite3_column_bytes16()は文字列内のバイト数を返すもので、文字数を返すものではありません。

sqlite3_column_text()とsqlite3_column_text16()によって返される文字列は空の文字列と同様に常にゼロ終端です。

長さがゼロのBLOBのsqlite3_column_blob()から返される値は、NULLポインタです。

sqlite3_column_value()によって返されるオブジェクトはunprotected sqlite3_valueオブジェクトです。

unprotected sqlite3_valueオブジェクトは、sqlite3_bind_value()sqlite3_result_value()でのみ使用することができます。

sqlite3_column_value()で返されたunprotected sqlite3_valueオブジェクトを他の方法で使用した場合、sqlite3_value_int()sqlite3_value_text()、またはsqlite3_value_bytes()のようなルーチンへの呼び出しを含め、その動作は未定義です。

これらのルーチンは適切な値に変換しようと試みます。

例えば、FLOATの内部表現ではテキスト結果が要求された場合、sqlite3_snprintf()は自動的に変換を実行するために内部で使用されます。

下表に実際の変換の詳細を示します。

内部の型要求された型変換
NULLINTEGER結果は0
NULLFLOAT結果は0.0
NULLTEXT結果はNULLポインタ
NULLBLOB結果はNULLポインタ
INTEGERFLOAT整数から浮動小数への変換
INTEGERTEXT整数をASCIIに翻訳
INTEGERBLOBINTEGER → TEXTと同じ
FLOATINTEGER浮動小数から整数への変換
FLOATTEXT浮動小数をASCIIに翻訳
FLOATBLOBFLOAT → TEXTと同じ
TEXTINTEGERatoi()を使用
TEXTFLOATatof()を使用
TEXTBLOB変更無し
BLOBINTEGERTEXTに変換後、atoi()を使用
BLOBFLOATTEXTに変換後、atof()を使用
BLOBTEXT必要に応じてゼロ終端を追加

上記の表にあるatoi()とatof()は標準Cライブラリの関数です。

SQLiteはこれらの関数を実際に使用するわけではなく、等価な独自の内部ルーチンを持っています。

表では簡潔に表すため、ほとんどのCプログラマに知られているatoi()とatof()という名前を使っています。

型変換が生じた場合、事前に呼び出したsqlite3_column_blob()やsqlite3_column_text()、またはsqlite3_column_text16()によって返されるポインタは無効になる場合があります。

型変換によるポインタの無効化は次の場合に発生する可能性があります。
  • 初期内容がBLOBで、sqlite3_column_text()とsqlite3_column_text16()が呼び出された場合、文字列にゼロ終端を追加する必要がある可能性があります。

  • 初期内容がUTF-8テキストで、sqlite3_column_bytes16()またはsqlite3_column_text16()が呼び出された場合、内容をUTF-16に変換する必要があります。

  • 初期内容がUTF-16テキストで、sqlite3_column_bytes()またはsqlite3_column_text()が呼び出された場合、内容をUTF-8に変換する必要があります。
UTF-16beとUTF-16le間の変換は、以前のポインタを無効にせず常にその場で行いますが、当然バッファの内容は以前のポインタの参照が変更されている前提です。

他の変換の種類は可能な時にその場所で行いますが、時々以前のポインタが無効になり不可能な場合があります。

安全で簡単に処理を忘れずに行うには、次の方法のいずれかのルーチンを呼び出してください。
  • sqlite3_column_bytes()の後にsqlite3_column_text()
  • sqlite3_column_bytes()の後にsqlite3_column_blob()
  • sqlite3_column_bytes16()の後にsqlite3_column_text16()
すなわち、sqlite3_column_text()やsqlite3_column_blob()mまたはsqlite3_column_text16()といった望む結果のフォーマットを最初に呼び出し、それからsqlite3_column_bytes()またはsqlite3_column_bytes16()を結果のサイズを確認して呼び出します。

sqlite3_column_text()またはsqlite3_column_blob()をsqlite3_column_bytes16()と、またはsqlite3_column_text16()とsqlite3_column_bytes()を混合して呼び出さないでください。

返されたポインタは上記に示した型変換の発生まで、あるいはsqlite3_step()sqlite3_reset()、またはsqlite3_finalize()の呼び出しまで有効です。

メモリ空間は文字列の保持に使用され、BLOBは自動的に解放されます。

sqlite3_column_blob()やsqlite3_column_text()などで返されたポインタをsqlite3_free()に渡さないでください。

これらのルーチンのいずれかの評価中にメモリ割り当てエラーが発生した場合は、デフォルト値を返します。

デフォルト値は、整数 0、浮動小数点 0.0、またはNULLポインタです。

続いて呼び出されるsqlite3_errcode()SQLITE_NOMEMを返します。

ObjectsConstantsFunctionsの一覧を参照してください。



参考文献

Result Values From A Query

0 CommentsPosted in SQLite

SQLステートメントの評価

2011. 02. 19
int sqlite3_step(sqlite3_stmt*);

sqlite3_prepare_v2()またはsqlite3_prepare16_v2()のどちらか、あるいは従来のsqlite3_prepare()またはsqlite3_prepare16()を使用して、prepared statementの準備ができたら、この関数を1回以上呼び出してステートメントの評価を行う必要があります。

sqlite3_step()インターフェイスの動作の詳細については、ステートメントの準備に新しい『v2』インターフェイスのsqlite3_prepare_v2()sqlite3_prepare16_v2()か、古い従来のインターフェイスsqlite3_prepare()sqlite3_prepare16()のどちらを使用したかに依存します。

新しいアプリケーションでは新しい『v2』インターフェイスの使用を推奨しますが、サポートの継続には従来のインターフェイスを使用してください。

従来のインターフェイスの戻り値はSQLITE_BUSYSQLITE_DONESQLITE_ROWSQLITE_ERROR、またはSQLITE_MISUSEのいずれかになります。

『v2』インターフェイスは、更にその他のresult codesextended result codesも返します。

SQLITE_BUSYは、データベースエンジンを取得しようとした際に、データベースがジョブを実行する必要があるためにロックされていることを表します。

ステートメントがCOMMITまたは明示的なトランザクションの外部で発生した場合は、ステートメントの再試行を行うことができます。

ステートメントがCOMMITではなく、明示的なトランザクションの内部で発生した場合は、継続する前にトランザクションをロールバックする必要があります。

SQLITE_DONEは、ステートメントの実行が正常に完了したことを表します。

sqlite3_step()は仮想マシンを初期状態に戻す最初のsqlite3_reset()の呼び出しによるリセットを除いて、この仮想マシン上で再度呼び出す必要はありません。

SQLステートメントが実行した際に任意のデータを返す場合、SQLITE_ROWはデータの新しい行が呼び出し元による処理のために準備される度に返します。

値はcolumn access functionsを使用してアクセスすることができます。

sqlite3_step()はデータの次の行を取得するために再度呼び出されます。

SQLITE_ERRORは、(制約違反などの)ランタイムエラーが発生したことを表します。

sqlite3_step()は仮想マシン上で再度呼び出す必要はありません。

詳細な情報は、sqlite3_errmsg()を呼び出すことによって見つけることができます。

従来のインターフェイスでは、より具体的なエラーコード(例えばSQLITE_INTERUPTSQLITE_SCHEMASQLITE_CORRUPTなど)を、prepared statement上でsqlite3_reset()を呼び出すことによって取得することができます。

『v2』インターフェイスでは、より具体的なエラーコードはsqlite3_step()が直接返します。

SQLITE_MISUSEは、このルーチンが不正確に呼ばれたことを意味します。

おそらくprepared statement上で呼び出された際に、既にfinalizedまたはSQLITE_ERRORSQLITE_DONEが事前に返されています。

もしくは、同じデータベース接続が同時に2つ以上のスレッドを使用した可能性があります。

SQLiteの3.6.23.1までの全てのバージョンでは、sqlite3_step()が後続を呼び出す前に、sqlite3_step()がSQLITE_ROW以外を返した後で、sqlite3_reset()を呼び出す必要がありました。

sqlite3_reset()を使用した準備ステートメントのリセットが失敗すると、sqlite3_step()からSQLITE_MISUSEが返ってくるでしょう。

しかし3.6.23.1より後のバージョンでは、この状況下の場合にSQLITE_MISUSEを返すのではなく、sqlite3_step()は自動的にsqlite3_reset()を呼び出し始めます。

SQLITE_MISUSEエラーを受信する全てのアプリケーションは常に定義によって破棄されるので、互換性の破棄とはみなされません。

SQLITE_OMIT_AUTORESETはコンパイル時のオプションで、従来の動作を復元するために使用することができます。

ばかげたインターフェイスの警告:

従来のインターフェイスでsqlite3_step() APIは、SQLITE_BUSYSQLITE_MISUSE以外の全てのエラーは、常に一般的なエラーコードSQLITE_ERRORを返していました。

より詳細な特定のerror codesを見いだすためには、sqlite3_reset()またはsqlite3_finalize()を呼び出す必要がありました。

我々はこのばかげた設計を認識しています。

この問題は『v2』インターフェイスで修正しています。

全てのSQLステートメントの準備に、従来のsqlite3_prepare()sqlite3_prepare16()インターフェイスの代わりに、sqlite3_prepare_v2()またはsqlite3_prepare16_v2()のどちらかを使用すると、sqlite3_step()によってより特定されたerror codesが直接返されます。

『v2』インターフェイスの使用を推奨します。

ObjectsConstantsFunctionsの一覧を参照してください。



参考文献

Evaluate An SQL Statement

0 CommentsPosted in SQLite

SQLステートメントのコンパイル

2011. 02. 18
int sqlite3_prepare(
    sqlite3 *db,
    const char *zSql,
    int nByte,
    sqlite3_stmt **ppStmt,
    const char **pzTail
);
int sqlite3_prepare_v2(
    sqlite3 *db,
    const char *zSql,
    int nByte,
    sqlite3_stmt **ppStmt,
    const char **pzTail
);
int sqlite3_prepare16(
    sqlite3 *db,
    const void *zSql,
    int nByte,
    sqlite3_stmt **ppStmt,
    const void **pzTail
);
int sqlite3_prepare16_v2(
    sqlite3 *db,
    const void *zSql,
    int nByte,
    sqlite3_stmt **ppStmt,
    const void **pzTail
);
    
/* データベース名 */
/* UTF-8エンコードのSQLステートメント */
/* zSqlのバイト単位の最大長 */
/* 出力:ステートメント名 */
/* 出力:zSqlの未使用部分へのポインタ */


/* データベース名 */
/* UTF-8エンコードのSQLステートメント */
/* zSqlのバイト単位の最大長 */
/* 出力:ステートメント名 */
/* 出力:zSqlの未使用部分へのポインタ */


/* データベース名 */
/* UTF-16エンコードのSQLステートメント */
/* zSqlのバイト単位の最大長 */
/* 出力:ステートメント名 */
/* 出力:zSqlの未使用部分へのポインタ */


/* データベース名 */
/* UTF-16エンコードのSQLステートメント */
/* zSqlのバイト単位の最大長 */
/* 出力:ステートメント名 */
/* 出力:zSqlの未使用部分へのポインタ */

SQLクエリを実行するには、最初にこれらルーチンの一つを使用してバイトコードにプログラムをコンパイルする必要があります。

第1引数の『db』は、事前にsqlite3_open()sqlite3_open_v2()、またはsqlite3_open16()の呼び出しが成功したことによって取得されたdatabase connectionです。

データベース接続は閉じてはいけません。

第2引数の『zSql』はコンパイルするステートメントで、UTF-8またはUTF-16でエンコードされます。

sqlite3_prepare()とsqlite3_prepare_v2()インターフェイスはUTF-8を、sqlite3_prepare16()とsqlite3_prepare16_v2()はUTF-16を使用しています。

引数nByteがゼロより小さい場合、zSqlは最初のゼロ終端まで読み込まれます。

nByteが負でない場合、zSqlからバイトの最大値まで読み込まれます。

nByteが負でない場合、zSql文字列は『\000』または『\u0000』文字、あるいはnByte番目のバイトの、いずれか最初に来る位置で終了します。

指定した文字列がヌル終端であることを呼び出し側が知っている場合、ヌル終端のバイト列を含む入力文字列内のバイト数に等しいため、nByteパラメータを渡すことによって僅かな性能の利益が得られます。

pzTailがNULLでない場合、*pzTailはzSql内にある最初のSQLステートメントの末尾を超えた最初のバイトを指します。

これらのルーチンはzSql内の最初のステートメントをコンパイルする場合のみで、*pzTailはコンパイルされずに残っている点を指します。

*ppStmtは、sqlite3_step()を使用して実行することができる、コンパイル済みのprepared statementを指します。

エラーがある場合、*ppStmtはNULLに設定されます。

入力テキストが(空の文字列またはコメントで)SQLを含まない場合、*ppStmtはNULLを指します。

呼び出し元のプロシージャは、sqlite3_finalize()を使用した処理が完了した後で、コンパイル済みのSQLステートメントを削除する責任があります。

ppStmtはNULLではない可能性があります。

成功した場合、ルーチンのsqlite3_prepare()ファミリはSQLITE_OKを返し、それ以外の場合ははerror codeを返します。

sqlite3_prepare_v2()とsqlite3_prepare16_v2()インターフェイスは、全ての新しいプログラムで推奨されます。

2つの古いインターフェイスは下位互換のために保持し、使用は推奨されません。

『v2』インターフェイスではprepared statement、即ちオリジナルのSQLテキストのコピーを含む(sqlite3_stmtオブジェクト)を返します。

sqlite3_step()インターフェイスは、以下の3つの方法で異なる振る舞いをします。

  1. データベースのスキーマが変更された場合、常に使用されるものとしてSQLITE_SCHEMAが代わりに返され、sqlite3_step()が自動的にSQLステートメントを再コンパイルし、再度実行を試みます。

  2. エラーが発生した場合、sqlite3_step()は詳細なerror codesまたはextended error codesの一つを返します。
  3. 従来の動作ではsqlite3_step()のみが一般的なSQLITE_ERROR結果コードを返し、アプリケーションは問題の根本的な原因を見つけるために、2回目のsqlite3_reset()の呼び出しを作成していました。
    『v2』の付いた準備インターフェイスは、エラーの根本的な原因はすぐに返されます。

  4. 指定した値がWHERE節内のhost parameterにバインドされている場合、ステートメントのクエリ手順の選択に影響を及ぼす可能性があるため、ステートメントは自動的に再コンパイルし、同様にスキーマの変更があった場合は、最初のsqlite3_step()で以下のparameterbindingsへの変更を呼び出します。
  5. パラメータがLIKEまたはGLOB演算子の左側の場合、またはパラメータがインデックス列と比較されている場合で、コンパイル時のオプションSQLITE_ENABLE_STAT2が有効な場合、WHEREのparameterの指定した値はクエリ手順の選択に影響を及ぼす可能性があります。

ObjectsConstantsFunctionsの一覧を参照してください。



参考文献

Compiling An SQL Statement

Wikipedia/プロシージャ

Wikipedia/スキーマ(データベース)

0 CommentsPosted in SQLite





SoundSport Pulse wireless headphones
Calendar
01 | 2011/02 | 03
Sun Mon Tue Wed Thu Fri Sat
- - 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 - - - - -
Recent Articles
iTunes


Swift
Categories
Tips
Profile

水月杏香

Author:水月杏香
永遠の初心者プログラマ。

Wish List
WACOM


ARC
Technical Q&A
情報プロパティリストキー
Start Developing iOS Apps Today
BOSE

SoundSport Pulse wireless headphones
Reference
NSApplicationDelegateプロトコル
NSArrayクラス
NSAutoreleasePoolクラス
NSBundleクラス
NSBundle UIKit追加分
NSCalendarクラス
NSCoderクラス
NSCodingプロトコル
NSCopyingプロトコル
NSDataクラス
NSDateクラス
NSDateFormatterクラス
NSDictionaryクラス
NSEntityDescriptionクラス
NSEnumeratorクラス
NSErrorクラス
NSExceptionクラス
NSFetchRequestクラス
NSFileHandleクラス
NSFileManagerクラス
NSIndexPathクラス
NSIndexPath UIKit追加分
NSKeyedArchiverクラス
NSKeyedUnarchiverクラス
NSKeyValueCodingプロトコル
NSLocaleクラス
NSManagedObjectクラス
NSManagedObjectContextクラス
NSManagedObjectModelクラス
NSMutableArrayクラス
NSMutableCopyingプロトコル
NSMutableDictionaryクラス
NSMutableSetクラス
NSNotificationクラス
NSNotificationCenterクラス
NSNullクラス
NSNumberクラス
NSObjectクラス
NSObject UIKit追加分
NSObjectプロトコル
NSPersistentStoreクラス
NSPersistentStoreCoordinatorクラス
NSPredicateクラス
NSPropertyListSerializationクラス
NSRunLoopクラス
NSSetクラス
NSStringクラス
NSString UIKit追加分
NSTimerクラス
NSTimeZoneクラス
NSURLクラス
NSURLProtectionSpaceクラス
NSURLRequestクラス
NSUserDefaultsクラス
NSValueクラス

UIActionSheetクラス
UIActionSheetDelegateプロトコル
UIActivityIndicatorViewクラス
UIAlertViewクラス
UIAlertViewDelegateプロトコル
UIApplicationクラス
UIApplicationDelegateプロトコル
UIBarButtonItemクラス
UIBarItemクラス
UIButtonクラス
UIColorクラス
UIControlクラス
UIDatePickerクラス
UIDeviceクラス
UIEventクラス
UIFontクラス
UIGestureRecognizerクラス
UIImageクラス
UIImageViewクラス
UIKit Function
UILabelクラス
UINavigationControllerクラス
UINavigationItemクラス
UIPickerViewクラス
UIPickerViewDataSourceプロトコル
UIPickerViewDelegateプロトコル
UIPinchGestureRecognizerクラス
UIResponderクラス
UIScreenクラス
UIScrollViewクラス
UISearchBarクラス
UISearchBarDelegateプロトコル
UISegmentedControlクラス
UISliderクラス
UISwipeGestureRecognizerクラス
UISwitchクラス
UITableViewクラス
UITableViewCellクラス
UITableViewControllerクラス
UITableViewDataSourceプロトコル
UITableViewDelegateプロトコル
UITapGestureRecognizerクラス
UITextFieldクラス
UITextFieldDelegateプロトコル
UITextInputTraitsプロトコル
UITextViewクラス
UITextViewDelegateプロトコル
UIToolbarクラス
UITouchクラス
UIViewクラス
UIViewControllerクラス
UIWebViewクラス
UIWebViewDelegateプロトコル
UIWindowクラス

AVAudioPlayerクラス
AVAudioPlayerDelegateプロトコル

CADisplayLinkクラス
CAEAGLLayerクラス
CALayerクラス

CGAffineTransform
CGBitmapContext
CGColor
CGColorSpace
CGContext
CGGeometry
CGImage
CGPath

EAGLContextクラス
EAGLDrawableプロトコル

Foundation Constants
Foundation Data Types
Foundation Functions

MPMediaItemクラス
MPMediaItemArtworkクラス
MPMediaPlaylistクラス
MPMediaPropertyPredicateクラス
MPMediaQueryクラス
MPMusicPlayerControllerクラス

Randomization Services

System Sound Services
Amazon


OpenGL ES
SQLite
Monthly Archives
Recent Comments
Recent TrackBacks
RSS Link
Visitors
QR Code
QR