sqliteで管理してみた(1) - 概要

ottawa123

概要

前回までで番組情報を取得しましたのでデータベースに格納することにしました。サービスとして起動しないsqliteがお手軽かな…という安易な考えです。

最近のsqliteはjson形式のデータも入れられるみたいですがここでは別の方法を採用します。録画したTSファイルをフォルダに移動したり、録画ディスクを付け替えしたりすることもあり、TSファイルは個別に管理したいと思いファイルシステムのinodeのような構成にしたいと思います。

フォルダ管理テーブル
ファイル管理テーブル

フォルダ管理テーブルでフォルダに対してのサブフォルダをSubFolder、ファイルをNodesとしています。配列区切りを":"を使用してNo値を格納しています。例えば、"F:/RecFolder"にはSubFolder値は"2"と"3"が格納されていることを意味し、"F:/RecFolder/ドラマ"にはNodesの"5"が入っているのでファイル表から"のどぐろカンタービレ.ts"を取得できます。

このファイルはLinksは参照されている数を意味しており、実際には"G:/RecFolder/映画"のNodesにも含まれています。また、ファイル管理のNo "8"はどこからも参照されていないのでLinks数は0となります。Incompleteは録画中のファイルで再取得が必要なファイルであることを明示的にしています。

ファイルはフォルダ移動とかする場合に今まで存在していたフォルダから消えますので移動先で再度番組情報等を再取得させないためです。サムネイルとかも再作成すると効率が悪いかと思います。また、ディスクの付け替えをするので付け替える毎にフルフルの検索されるのも嫌なので。

TSファイルの検索有無を判断するためにフォルダの最終更新日を使用します。データベースに格納されている更新日と実際のフォルダの更新日が異なるようであればそのフォルダは更新されていることになります。

データベース構成と操作

事前にテーブル構成や操作について確認しております。
フォルダ管理用のテーブルとしてProgFolderという名称にします。

CREATE TABLE IF NOT EXISTS ProgFolder(
    No INTEGER PRIMARY KEY,
    Name TEXT NOT NULL UNIQUE,
    LastWrite INTEGER,
    SubFolders TEXT,
    Nodes TEXT
);

ファイル管理用のテーブルとしてProgEntryという名称にします。番組情報等は現時点で除外しております。

CREATE TABLE IF NOT EXISTS ProgEntry(
      No INTEGER PRIMARY KEY,
      Name TEXT NOT NULL UNIQUE,
      Links INTEGER,
      Incomplete INTEGER
);

データ挿入(INSERT)コマンド

INSERT INTO ProgFolder(Name, LastWrite, SubFolders, Nodes) 
VALUES('F:/RecFolder/ドラマ', UNIXTIME, ':4:', ':5:');

INSERT INTO ProgEntry(Name, Links, Incomplete)
VALUES('のどぐろカンタービレ.ts', 1, 0);

データ更新(UPDATE)コマンド

UPDATE ProgFolder SET LastWrite=UNIXTIME, SubFolder=':4:', Nodes=':5:10:' WHERE No=X;
UPDATE ProgEntry SET Links=2, Incomplete=0 WHERE No=X;

UPDATEコマンドで変更されることが無い項目(ファイル名やディレクトリ名等)は更新対象として含めない。

データ削除(DELETE)コマンド

DELETE FROM ProgFolder WHERE NO=X;

ProgEntryはLinks数を減らすだけなのでUPDATEで代用する。

データ参照（SELECT）コマンド

標準的な参照
SELECT * FROM ProgFolder WHERE NAME='F:/RecFolder';
SELECT * FROM ProgFolder WHERE NO=1;
SELECT * FROM ProgFolder WHERE No in (2,3);

No=3の親フォルダ名を取得する場合
SELECT * FROM ProgFolder WHERE SubFolders LIKE ':3:';

最後にINSERTした行番号を取得
SELECT * FROM ProgXXXXX WHERE ROWID = last_insert_rowid();

以上のSQL文を実装すればデータ管理は行えそうです。ProgEntryのTSファイルエントリを削除するだけであればトリガー処理とかの方が便利かもですが本構成ではLinks数を減らすので手動で実装しようと思います。

データベース初期化

SQL文を使用してデータベースからデータを読み出ししたり、書き込みしたい、更新、削除を実施します。

事前準備

フォルダ管理データを格納するクラスとファイル管理データを格納するクラスを生成します。

フォルダ用 ProgFolder

public class ProgFolder
{
    public int No { get; set; } = -1;
    public string Name { get; set; }
    public long LastWrite { get; set; }
    public List<int> SubFolders { get; set; }
    public List<int> Nodes { get; set; }

    public ProgFolder() { }

    public ProgFolder(string Name)
    {
        this.Name = Name;
        this.SubFolders = new List<int>();
        this.Nodes = new List<int>();
    }
}

ファイル用 ProgEntry(番組情報はまだ未適用)

public class ProgEntry
{
    public int No { get; set; }
    public string Name { get; set; }
    public int Links { get; set; } = -1;
    public bool Incompleted { get; set; } = false;

    public ProgEntry() { }

    public ProgEntry(string Name)
    {
        this.Name = Name;
    }
}

sqliteをC#で使用するためNugetにてsystem.data.sqlite.coreを事前にインストールしておく必要があります。

Nugetインストール済みモジュール

データベースのオープンとクローズ処理となります。定型文のようなものなのでこんな感じでsqliteをオープンするんだな、程度で十分だと思います。Open時に引数でデータベースのファイルパスを指定します。

public static class Database
{
    private static SQLiteConnection sqlConn { get; set; } = null;
    private static SQLiteTransaction sqlTransaction { get; set; } = null;

    public static bool Open(string DatabasePath)
    {
        SQLiteConnectionStringBuilder SQLConnSb = new SQLiteConnectionStringBuilder { DataSource = DatabasePath };
        sqlConn = new SQLiteConnection(SQLConnSb.ToString());
        sqlConn.Open();
        if (sqlConn.State == ConnectionState.Open)
        {
            // トランザクション開始 
            sqlTransaction = sqlConn.BeginTransaction();
            if (InitDatabase()) { return true; }
        }
        Close();
        return false;
    }

    public static void Close()
    {
        if (sqlConn.State == ConnectionState.Open)
        {
            // トランザクションコミット
            sqlTransaction.Commit();
        }

        if (sqlConn != null)
        {
            sqlConn.Close();
            sqlConn = null;
        }
    }
}

想像していたよりもトランザクションを有効にしないと書き込みがとにかく遅いです。

テーブルが存在しない場合に作成する処理となります。

private static bool InitDatabase()
{
    string sql = "CREATE TABLE IF NOT EXISTS ProgFolder(" +
        "No INTEGER PRIMARY KEY," +
        "Name TEXT NOT NULL UNIQUE," +
        "LastWrite INTEGER," +
        "SubFolders TEXT," +
        "Nodes TEXT);";

    int result = ExecuteCmd(sql);
    if (result == -1) { return false; }

    sql = "CREATE TABLE IF NOT EXISTS ProgEntry(" +
        "No INTEGER PRIMARY KEY," +
        "Name TEXT NOT NULL UNIQUE," +
        "Links INTEGER," +
        "Incomplete);";

    result = ExecuteCmd(sql);
    if (result == -1) { return false; }

    return true;
}

private static int ExecuteCmd(string sql)
{
    int result = -1;
    using (SQLiteCommand cmd = new SQLiteCommand(sqlConn))
    {
        cmd.CommandText = sql;
        result = cmd.ExecuteNonQuery();
    }
    return result;
}

SQLiteCommand.ExecuteNonQuery(終了コード以外の結果は取得しない)にてSQL文を実行します。また、終了コードは影響を与えた行数が値となります。

読み出し例

試しにデータを読み出し処理を紹介します。本格的には次回となります。
一つの処理にしてしまっても良いのですが3つ分けています。

// 実行したいSQL文
public static ProgEntry GetEntryInfo(int No)
{
    var sql = string.Format("SELECT * FROM ProgEntry WHERE No={0};", No);
    return GetEntrySingleInternal(sql);
}

Noの値を取得するSQL文となります。SQL文を実行してデータを読み出し可能な状態にしております。また、1行しか応答がないことを前提としているので、読み出しは1回しかしていません。

// データベースから読み出し処理
private static ProgEntry GetEntrySingleInternal(string sql)
{
    ProgEntry entry = null;
    using (var cmd = new SQLiteCommand(sqlConn))
    {
        cmd.CommandText = sql;
        using (SQLiteDataReader sr = cmd.ExecuteReader())
        {
            if (sr.Read())
            {
                entry = InternalProgEntry(sr);
            }
        }
    }
    return entry;
}

読み込んだデータをProgEntryクラスに格納します。

// データをクラスに格納
private static ProgEntry InternalProgEntry(SQLiteDataReader sr)
{
    ProgEntry entry = new ProgEntry();
    entry.No = int.Parse(sr["No"].ToString());
    entry.Name = sr["Name"].ToString();
    entry.Links = int.Parse(sr["Links"].ToString());
    entry.Incompleted = (int.Parse(sr["Incomplete"].ToString()) == 1) ? true : false;

    return entry;
}

今回は読み出し処理の例を紹介しました。他のSQL文はINSERT、UPDATE、DELETEは読み出し処理は必要ないのでCREATE TABLEと同じ方法で実行できます。

また、SELECT文で複数行の応答が期待される場合には、sr.Read()がfalseになるまで繰り返し実行します。

private static List<ProgEntry> GetEntryMultiInternal(string sql)
{
    List<ProgEntry> entry = new List<ProgEntry>();
    using (var cmd = new SQLiteCommand(sqlConn))
    {
        cmd.CommandText = sql;
        using (SQLiteDataReader sr = cmd.ExecuteReader())
        {
            while (sr.Read())
            {
                entry.Add(InternalProgEntry(sr));
            }
        }
    }
    return entry;
}

1行のみの応答を期待する場合と、複数行の応答を期待する場合とを分けていますが、一緒にしてしまっても良いとは思います。

この記事が気に入ったらサポートをしてみませんか？