Excelでビッグデータを乗りこなせ（6．SQLiteデータベースの作成）

前回はExcelとは異なるデータベースの独自要素を見てみた。

www.excellovers.com

今回からはその点を踏まえつつ実際にSQLiteデータベースの作成をしてみたいと思います。

ExcelでSQLiteデータベースを操作

上記を実現するためにExcel for SQLite使用する。

コレについては弊blogで過去に紹介しているのでこちらを参照して頂きたい。

www.excellovers.com

今回は２つのDLLとマクロコードを記載する１つのExcelファイルを同一のフォルダに保存している。

データベースの作成

コレについても過去に紹介しているのでご参考ください。

www.excellovers.com

上記内容をふまえてコードを記載してみると

Sub SQLiteDatabase_Make()
     Dim SQLiteDB_Handle As Long
     Dim ReturnValue As Long
     Dim SQLiteFullPath As String
     Dim myStmtHandle As Long
     Dim ErrCheck As Long
'DLLへ接続
     SQLite3Initialize
     
'データベース作成
     SQLiteFullPath = ThisWorkbook.Path & "\家計簿.db3"
     SQLite3Open SQLiteFullPath, SQLiteDB_Handle
     
'①テーブル作成
     ErrCheck = SQLite3PrepareV2(SQLiteDB_Handle, _
         "CREATE TABLE 仕訳(" & _
         "仕訳ID INTEGER PRIMARY KEY," & _
         "日付 TEXT," & _
         "項目 TEXT," & _
         "内訳 TEXT," & _
         "品名 TEXT," & _
         "お店 TEXT," & _
         "収入 INTEGER," & _
         "支出 INTEGER," & _
         "口座 TEXT," & _
         "メモ TEXT)", _
         myStmtHandle)
         
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
'②インデックス作成
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 日付index On 仕訳(日付)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
          
     SQLite3PrepareV2 SQLiteDB_Handle, _
          "Create Index 項目index On 仕訳(項目)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle

     
'データベース接続終了
     SQLite3Close SQLiteDB_Handle
     
     Select Case ErrCheck
          Case 0
               MsgBox "データベース作成完了"
          Case Else
               MsgBox "テーブル作成時にエラーが発生しました"
     End Select
End Sub

上記で、コードを記載したExcelと同じフォルダにSQLite3データベースが作成されます。

SQLite3Openでデータベース作成

SQLite3Open関数を使用するだけで作成できます。

Public Function SQLite3Open(ByVal fileName As String, ByRef dbHandle As Long) As Long

・引数 dbHandle は事前に宣言しておいたLong型変数を指定すれば大丈夫です。

Create Table構文でテーブル作成

テーブルはSQLite3PrepareV2の引数 sql でSQLのCreate文を指定すれば作成されます。

Public Function SQLite3PrepareV2(ByVal dbHandle As Long, ByVal sql As String, ByRef stmtHandle As Long) As Long

・引数 dbHandleはSQLite3Open関数の引数に使用したものと同じものを指定してください。

・引数 sql は実行したSQL文を指定します。

・事前に宣言しておいたLong型変数を指定します。

Create Table テーブル名（列名　列の型）

・必要な列の数だけ列名・列の型を記載します。

ここで前回紹介したときには登場しなかった記述があります。

PRIMARY KEYです。

主キーの設定

主キーとは、リレーショナルデータベース（RDB）のテーブル内でレコードを一意に識別することができるように指定される項目（列/カラム）。

データベースは通常、Excelのように目で見て任意の行をマウス等で指定して処理することが出来ません。

そこで各行を識別するための値、主キーを設定して識別できるようにします。

主キーとする列は以下の条件満たすようにする。

・他の行と値が重複しない

・必ず何らかのデータが格納される（NULL以外）

自分で注意して上記条件を満たすようにしても良いのですが、当然ながら間違いが起こる可能性もあります。

そこでテーブルを作成する際にPRIMARY KEY制約を設定すると上記条件を強制することが出来ます。

VBAの変数におけるOption Explicitみたいなものですね。

主キー用の列として家計簿データには無い「仕訳ID」列を追加しました。

もう１つ前回には登場しなかった記述があります。

インデックスです。

インデックスの設定

インデックスとは、索引、見出し、添字、指数などの意味を持つ英単語。ITの分野では、書籍の巻末の索引のように、多数のものの中から特定の対象をすばやく見つけ出すため識別情報や、整列された所在情報の一覧などを指すことが多い。

データベースに対して「すばやく見つけ出すための識別情報」としてつけるのがインデックスでしょうか。

・インデックスは列に対して指定する

・インデックスが設定されている列の検索が高速化する（条件によっては高速化しない）

・データベースの容量が増える

・更新系のSQL文の処理が遅くなる

取り敢えず今回は日付と項目の列に設定してみました。

このあたりは実際にインデックスの有無での検索スピードの対比はしてみたいですね。

まとめ

以前と違ってデータベース独自の要素を盛り込みながらデータベースを作成してみました。

これでデータベースの世界へ少し足を踏み込めたでしょうか。

次回はもう少しテーブル周りのことを勉強したいと思います。

こちらの本でSQL文、データベースのお作法のことを勉強しています。

スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)