sqliteで管理してみた(2) - 実装

C#のsqlite実装

前回初期処理とProgEntryの読み出しの一部について紹介しましたが、ここでは使用するSQL文を全て実装していきます。

配列用の変換処理

ProgFolderではSubFoldersやNodesは":"区切りで配列的な役割をさせています。List<int> { 1, 2, 3 } → ":1:2:3:" というフォーマット形式にしています。ということで双方向の処理です。

// ":x:y:z:"形式からList<int> = {x, y, z}に変換
private static List<int> ParseArrange(string str)
{
    List<int> entry = new List<int>();
    if (string.IsNullOrEmpty(str)) { return entry; }
    string[] items = str.TrimStart(':').TrimEnd(':').Split(':');
    if (items.Length != 0)
    {
        foreach (string item in items)
        {
            entry.Add(int.Parse(item));
        }
    }
    return entry;
}

// List<int> = {x, y, z}形式から":x:y:z:"形式に変換
private static string ParseDearrange(List<int> items)
{
    if (items.Count == 0) return string.Empty;
    StringBuilder sb = new StringBuilder(20);

    foreach (int entry in items)
    {
        sb.Append(string.Format(":{0}", entry));
    }

    sb.Append(":");
    return sb.ToString();
}

SELECT文

ProgEntry関連のSELECT文処理となります。

public static ProgEntry GetEntryInfo(int No)
{
    var sql = string.Format("SELECT * FROM ProgEntry WHERE No={0};", No);
    return GetEntrySingleInternal(sql);
}

public static ProgEntry GetEntryInfo(string Name)
{
    var sql = string.Format("SELECT * FROM ProgEntry WHERE Name=\'{0}\';", Name.Replace("'", "''"));
    return GetEntrySingleInternal(sql);
}

public static List<ProgEntry> GetEntryInfo(int[] No)
{
    if (No.Length == 0) { return null; }
    StringBuilder sb = new StringBuilder(20);
    sb.Append("SELECT * FROM ProgEntry WHERE No IN (");
    foreach (int item in No)
    {
        sb.Append(string.Format("{0},", item));
    }
    var sql = string.Format("{0});", sb.ToString().Trim(','));
    return GetEntryMultiInternal(sql);
}

ProgFolder関連のSELECT文処理となります。

public static ProgFolder GetFolderInfo(int No)
{
    var sql = string.Format("SELECT * FROM ProgFolder WHERE No={0};", No);
    return GetFolderSingleInternal(sql);
}

public static ProgFolder GetFolderInfo(string path)
{
    var sql = string.Format("SELECT * FROM ProgFolder WHERE Name=\'{0}\';", path.Replace("'", "''"));
    return GetFolderSingleInternal(sql);
}

public static List<ProgFolder> GetFolderInfo(int[] No)
{
    if (No.Length == 0) { return null; }
    StringBuilder sb = new StringBuilder(20);
    sb.Append("SELECT * FROM ProgFolder WHERE No IN (");
    foreach (int item in No)
    {
        sb.Append(string.Format("{0},", item));
    }
    var sql = string.Format("{0});", sb.ToString().Trim(','));
    return GetFolderMultiInternal(sql);
}

前回紹介した内容ではあるがProgEntryの実際の読み出し処理部です。

// 返値が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;
}

// 返値が複数行であることを前提
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;
}

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

次にProgFolderの実際の読み出し処理部です。

// 返値が1行であることを前提
private static ProgFolder GetFolderSingleInternal(string sql)
{
    ProgFolder entry = null;
    using (var cmd = new SQLiteCommand(sqlConn))
    {
        cmd.CommandText = sql;
        using (SQLiteDataReader sr = cmd.ExecuteReader())
        {
            if (sr.Read())
            {
                entry = InternalProgFolder(sr);
            }
        }
    }
    return entry;
}

// 返値が複数行であることを前提
private static List<ProgFolder> GetFolderMultiInternal(string sql)
{
    List<ProgFolder> entry = new List<ProgFolder>();
    using (var cmd = new SQLiteCommand(sqlConn))
    {
        cmd.CommandText = sql;
        using (SQLiteDataReader sr = cmd.ExecuteReader())
        {
            while (sr.Read())
            {
                entry.Add(InternalProgFolder(sr));
            }
        }
    }
    return entry;
}

private static ProgFolder InternalProgFolder(SQLiteDataReader sr)
{
    ProgFolder entry = new ProgFolder();
    entry.No = int.Parse(sr["No"].ToString());
    entry.Name = sr["Name"].ToString().Replace("\\", "/");
    entry.SubFolders = ParseArrange(sr["SubFolders"].ToString());
    entry.LastWrite = long.Parse(sr["LastWrite"].ToString());
    entry.Nodes = ParseArrange(sr["Nodes"].ToString());

    return entry;
}

また、最後にINSERTされたNoを取得するSQL文となります。この処理についてはこの関数のみで完結させてしまっています。

public static int LastInsert(string table)
{
    var sql = string.Format("SELECT * FROM {0} WHERE ROWID = LAST_INSERT_ROWID(); ", table);
    int No = -1;
    using (var cmd = new SQLiteCommand(sqlConn))
    {
        cmd.CommandText = sql;
        using (SQLiteDataReader sr = cmd.ExecuteReader())
        {
            sr.Read();
            No = int.Parse(sr["No"].ToString());
        }
    }
    return No;
}

INSERT/UPDATE文

既にデータが存在している時はUPDATEで処理させて、存在しない時はINSERTを実行する処理にしたいと思います。

ProgEntryのINSERT/UPDATE処理はUPDATE処理時にはLinks数を増やすことにします。

public static int InsertEntry(ProgEntry entry)
{
    ProgEntry buffer = GetEntryInfo(entry.Name);
    string sql = string.Empty;
    if (buffer == null)
    {
        // データが存在しないのでエントリ追加
        entry.Links = 1;
        sql = string.Format("INSERT INTO ProgEntry(Name, Links, Incomplete) VALUES(\'{0}\',{1},{2});",
            entry.Name.Replace("'", "''"),
            entry.Links,
            entry.Incompleted ? 1 : 0);
    }
    else
    {
        // データが存在しているのでLinks数を増加
        sql = string.Format("UPDATE ProgEntry SET Links={0} WHERE No={1};", buffer.Links + 1, buffer.No);
    }

    if (ExecuteCmd(sql) != 1) { return -1; }
    if (buffer != null) { return buffer.No; }
    return LastInsert("ProgEntry");
}

ProgFolderの場合は既に存在している場合に変更はLastWrite、SubFolders、NodesカラムをUPDATE処理で、存在していない時はINSERT処理を実施しています。

public static int InsertFolder(ProgFolder entry)
{
    ProgFolder buffer = GetFolderInfo(entry.Name);
    string sql = string.Empty;
    if (buffer == null)
    {
        sql = string.Format("INSERT INTO ProgFolder(Name, LastWrite, SubFolders, Nodes) Values(\'{0}\', {1}, \'{2}\', \'{3}\');",
            entry.Name.Replace("\\", "/"),
            entry.LastWrite,
            ParseDearrange(entry.SubFolders),
            ParseDearrange(entry.Nodes));
    }
    else
    {
        sql = string.Format("UPDATE ProgFolder SET LastWrite={0}, SubFolders=\'{1}\', Nodes=\'{2}\' WHERE No={3};",
            entry.LastWrite,
            ParseDearrange(entry.SubFolders),
            ParseDearrange(entry.Nodes),
            buffer.No);
    }

    if (ExecuteCmd(sql) != 1) { return -1; }
    if (buffer != null) { return buffer.No; }
    return LastInsert("ProgFolder");
}

DELETE文

ProgEntryとProgFolderの削除処理ですが、ProgEntryは削除させずにLinks数を減らしているのでUPDATEを使用しています。

public static int DeleteEntry(ProgEntry entry)
{
    var sql = string.Format("UPDATE ProgEntry SET Links={0} WHERE No={1};", entry.Links - 1, entry.No);
    return ExecuteCmd(sql);
}

public static int DeleteFolder(ProgFolder folder)
{
    var sql = string.Format("DELETE FROM ProgFolder WHERE No={0};", folder.No);
    return ExecuteCmd(sql);
}

以上の内容で外部から呼ぶ出せばデータベースに参照や書き込みがなされるはずです。

sqliteからデータの取り出し方には色々な方法がありますが、用途に合わせて試行錯誤が必要になるかと思います。

フォルダ削除処理においてSubFoldersやNodes等も合わせて処理するかを悩んだのですが、呼び出し元の方で対処することにしました。

また、結果的にはデータ構造に依存させる処理を組み込んでしまっているが、データベース処理部は最小限に抑えたかったというのも理由ではあります。