準備ステートメントへの値のバインド

2011. 03. 06
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

SQLステートメントでsqlite3_prepare_v2()やその変種へテキスト入力する場合に、以下のテンプレートのいずれかに一致するparameterによって文字を置換することができます。

・?
・?NNN
・:VVV
・@VVV
・$VVV

上記のテンプレートにおいて、NNNは整数文字を、VVVは英数字を表す識別子です。

これらのパラメータの値(『ホストパラメータ名』または『SQLパラメータ』とも呼ばれる)は、ここで定義されているsqlite3_bind_*()ルーチンを使用して設定することができます。

sqlite3_bind_*()ルーチンへの第1引数は常に、sqlite3_prepare_v2()またはその変種から返されるsqlite3_stmtオブジェクトへのポインタです。

第2引数はSQLパラメータのインデックスを設定します。

左端のSQLパラメータは1のインデックスを持っています。

同じ名前のSQLパラメータを複数回使用する場合は、2つ目以降も最初のものと同じインデックスを持ちます。

名付けられたパラメータへのインデックスは、sqlite3_bind_parameter_index() APIを必要に応じて使用して検索することができます。

『?NNN』パラメータへのインデックスは、NNNの値です。

NNNの値は1とsqlite3_limit()パラメータのSQLITE_LIMIT_VARIABLE_NUMBER(デフォルト値:999)の間である必要があります。

第3引数はパラメータへバインドする値です。

第4引数を持つこれらのルーチンにおいて、その値はパラメータのバイト数です。

正確に言うと、その値は値のバイト数であり、文字数ではありません。

第4引数が負の値の場合、文字列の長さは最初のゼロ終端までのバイト数です。

sqlite3_bind_blob()、sqlite3_bind_text()、sqlite3_bind_text16()の第5引数は、SQLiteの処理が完了した後でBLOBまたは文字列の処理に使用するデストラクタです。

デストラクタは、sqlite3_bind_blob()、sqlite3_bind_text()、sqlite3_bind_text16()の呼び出しが失敗した場合でも、BLOBまたは文字列を処理するために呼び出されます。

第5引数が特殊な値SQLITE_STATICの場合、SQLiteは情報は静的なものと前提され、空間の管理や解放をする必要はありません。

第5引数が値SQLITE_TRANSIENTの場合、SQLiteはsqlite3_bind_*()ルーチンが返す前に、すぐに自身のデータのプライベートコピーを作成します。

sqlite3_bind_zeroblob()ルーチンは、ゼロで満たされた長さNのBLOBをバインドします。

zeroblobは処理されている間(ちょうど整数を保持するサイズの)固定されたメモリ量を使用します。

zeroblobはincremental BLOB I/Oルーチンを使用して書かれた後の内容で、BLOBのプレースホルダとして機能するように意図されています。

zeroblobが負の値の場合はBLOBがゼロの長さになります。

prepared statementのためのNULLポインタを指定して、またはsqlite3_reset()より最近に呼び出されたsqlite3_step()のためのprepared statementを指定して、sqlite3_bind_*()ルーチンのいずれかが呼び出された場合、SQLITE_MISUSEが返されます。

sqlite3_bind_()ルーチンが破棄されたprepared statementを渡された場合、未定義でおそらく危険な結果となります。

バインディングはsqlite3_reset()ルーチンによってクリアされません。

バインドされていないパラメータはNULLとして解釈されます。

sqlite3_bind_*ルーチンは、成功した場合にはSQLITE_OKを、何か問題があった場合はerror codeを返します。

SQLITE_RANGEは、パラメータのインデックスが範囲外の場合に返されます。

SQLITE_NOMEMは、malloc()が失敗した場合に返されます。

sqlite3_bind_parameter_count()sqlite3_bind_parameter_name()sqlite3_bind_parameter_index()も参照してください。

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



参考文献

Binding Values To Prepared Statements

0 CommentsPosted in SQLite

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





Bose QuietComfort 25
Calendar
02 | 2017/03 | 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 29 30 31 -
Recent Articles
iTunes


Swift
Categories
Tips
WACOM


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
BOSE

Bose QuietComfort 25
ARC
Technical Q&A
情報プロパティリストキー
Start Developing iOS Apps Today
SQLite
OpenGL ES
Amazon


Monthly Archives
Recent Comments
Recent TrackBacks
RSS Link
Profile

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

QR Code
QR
Visitors