src/LanSpider/Database/Manager.cs

   1 using System;
   2 using System.Collections.Generic;
   3 using System.Data;
   4 using System.Data.SQLite;
   5 using System.IO;
   6
   7 using LanSpider.Properties;
   8
   9 namespace LanSpider.Database
  10 {
  11     public class Manager : IDisposable
  12     {
  13         private const string SelectRootSql = "SELECT node_id FROM node where node_name='root' AND path='root'";
  14
  15         private const string SelectRelationshipSql =
  16             "SELECT distance FROM relationship WHERE parent_node_id = @parent_node_id AND child_node_id = @child_node_id;";
  17
  18         private const string InsertRelationshipSql =
  19             "INSERT INTO relationship (parent_node_id,child_node_id,distance, is_leaf) VALUES (@parent_node_id,@child_node_id,@distance,@is_leaf)";
  20
  21         private readonly SQLiteConnection _conn;
  22         private readonly int _rootId;
  23
  24         #region Static methods
  25
  26         private static bool SchemaExists( SQLiteConnection connection )
  27         {
  28             SQLiteCommand cmd = new SQLiteCommand( null, connection );
  29             cmd.CommandText = "SELECT name FROM sqlite_master WHERE name=@table";
  30             cmd.Parameters.Add( new SQLiteParameter( "@table", "node" ) );
  31             object tableName = cmd.ExecuteScalar();
  32
  33             return tableName != null && tableName != DBNull.Value;
  34         }
  35
  36         private static void CreateSchema( SQLiteConnection connection )
  37         {
  38             SQLiteCommand cmd = new SQLiteCommand( null, connection );
  39
  40             cmd.CommandText = Resources.create_db;
  41             cmd.ExecuteNonQuery();
  42         }
  43
  44         private static int GerRootNodeId( SQLiteConnection connection )
  45         {
  46             using ( SQLiteCommand cmd = new SQLiteCommand( SelectRootSql, connection ) )
  47             {
  48                 return (int) (long) cmd.ExecuteScalar();
  49             }
  50         }
  51
  52         #endregion Static methods
  53
  54         public Manager( string dbFileName )
  55         {
  56             _conn = new SQLiteConnection( String.Format( "Data Source={0};", dbFileName ) );
  57             _conn.Open();
  58
  59             if ( !SchemaExists( _conn ) )
  60             {
  61                 CreateSchema( _conn );
  62             }
  63
  64             _rootId = GerRootNodeId( _conn );
  65         }
  66
  67         public void SavePath( string share, params string[] parts )
  68         {
  69             using ( SQLiteCommand cmd = new SQLiteCommand( _conn ) )
  70             {
  71                 int shareId = GetNodeId( share, share );
  72                 LinkNodes( _rootId, shareId, 1 );
  73
  74                 string path = share;
  75                 List<int> parents = new List<int>( parts.Length );
  76                 parents.Add( shareId );
  77
  78                 for ( int i = 0; i < parts.Length; ++i )
  79                 {
  80                     string part = parts[ i ];
  81                     path += '\\' + part;
  82                     int partId = GetNodeId( part, path );
  83
  84                     LinkNodes( _rootId, partId, i + 2, i == parts.Length - 1 );
  85                     for ( int j = 0; j < parents.Count; ++j )
  86                     {
  87                         LinkNodes( parents[ j ], partId, i + 1 - j, i==parts.Length-1 );
  88                     }
  89
  90                     parents.Add( partId );
  91                 }
  92             }
  93         }
  94
  95         /// <summary>
  96         /// Ensure a link between nodes exists.
  97         /// </summary>
  98         /// <param name="parentId"></param>
  99         /// <param name="childId"></param>
 100         /// <param name="distance"></param>
 101         private void LinkNodes( int parentId, int childId, int distance )
 102         {
 103             LinkNodes( parentId, childId, distance, false );
 104         }
 105
 106         /// <summary>
 107         /// Ensure a link between nodes exists.
 108         /// </summary>
 109         /// <param name="parentId"></param>
 110         /// <param name="childId"></param>
 111         /// <param name="distance"></param>
 112         /// <param name="isLeaf"></param>
 113         private void LinkNodes( int parentId, int childId, int distance, bool isLeaf )
 114         {
 115             using ( SQLiteCommand cmd = new SQLiteCommand( _conn ) )
 116             {
 117                 cmd.CommandType = CommandType.Text;
 118                 cmd.CommandText = SelectRelationshipSql;
 119
 120                 cmd.Parameters.Add( new SQLiteParameter( "@parent_node_id", parentId ) );
 121                 cmd.Parameters.Add( new SQLiteParameter( "@child_node_id", childId ) );
 122
 123                 object val = cmd.ExecuteScalar();
 124
 125                 if ( val == null || val == DBNull.Value )
 126                 {
 127                     cmd.CommandText = InsertRelationshipSql;
 128
 129                     cmd.Parameters.Add( new SQLiteParameter( "@distance", distance ) );
 130                     cmd.Parameters.Add( new SQLiteParameter( "@is_leaf", isLeaf ) );
 131
 132                     cmd.ExecuteNonQuery();
 133                 }
 134             }
 135         }
 136
 137         /// <summary>
 138         /// Get node id with path precified, create one if not exists.
 139         /// </summary>
 140         /// <param name="name">node_name</param>
 141         /// <param name="path">path</param>
 142         /// <returns>Node ID</returns>
 143         public int GetNodeId( string name, string path )
 144         {
 145             int nodeId;
 146
 147             using ( SQLiteCommand cmd = new SQLiteCommand( _conn ) )
 148             {
 149                 cmd.CommandType = CommandType.Text;
 150
 151                 cmd.CommandText = "SELECT node_id FROM node WHERE path= @path";
 152                 cmd.Parameters.Add( new SQLiteParameter( "@path", path ) );
 153
 154                 object val = cmd.ExecuteScalar();
 155                 if ( val == null || val == DBNull.Value )
 156                 {
 157                     cmd.CommandText = "INSERT INTO node (node_name, path) VALUES (@node_name, @path)";
 158                     cmd.Parameters.Add( new SQLiteParameter( "@node_name", name ) );
 159                     cmd.ExecuteNonQuery();
 160                     cmd.Parameters.Clear();
 161
 162                     cmd.CommandText = "SELECT last_insert_rowid()";
 163                     nodeId = (int) (long) cmd.ExecuteScalar();
 164                 }
 165                 else
 166                 {
 167                     nodeId = (int) (long) val;
 168                 }
 169             }
 170
 171             return nodeId;
 172         }
 173
 174         private void SaveRelations( int depth, string path, params string[] parts )
 175         {
 176             if ( depth >= parts.Length - 1 )
 177             {
 178                 return;
 179             }
 180             string parentName = parts.GetValue( depth ).ToString();
 181             string childName = parts.GetValue( depth + 1 ).ToString();
 182             string parentPath = Path.Combine( path, parentName );
 183
 184             string childPath = Path.Combine( path, childName );
 185
 186             int parentId = GetNodeId( parentName, parentPath );
 187         }
 188
 189
 190         public void Dispose()
 191         {
 192             _conn.Close();
 193         }
 194     }
 195 }