记简阅R.MaiFeiPin.Com 的数据库从Sqlite 升级到到MongoDB

简阅 r.maifeipin.com 算是匆忙跟风亲手手搓一个简单的集订阅与查看RSS的个人项目，随着Seed数的增加,现在每月基本上会产生2.5G的数据，虽然每月有自动分离的维护脚本，但性能和可扩展性对于我这个小站来说还是负担过重。所以打算给它作一次小小的升级换库,因为rss 本身的强文本化，强日期的特性，最终选择匹配度比较高的MongoDB…

旧数据迁移

迁移很顺利，就是对内容项的表按月拆表，同时兼容原sqlite数据表

   public void MigrateTables()
   {
       try
       {
           // 获取 SQLite 数据库中的所有表
           var tables = _sqliteDb.DbMaintenance.GetTableInfoList();

           foreach (var table in tables)
           {
               Console.WriteLine($"正在迁移表：{table.Name}");

               // 从 SQLite 表获取所有数据
               var data = _sqliteDb.Queryable<object>().AS(table.Name).ToList();

               if (data.Count > 0)
               { 
                   // 连接到 MongoDB 并创建集合
                   if (table.Name == "FeedItem")
                   {
                       var month = DateTime.Now.ToString("yyyyMM");
                       table.Name =   $"FeedItem_{month}";

                   }
                   var collectionNames = _mongoDb.ListCollectionNames().ToList();
                   if (collectionNames.Contains(table.Name))
                   {
                       Console.WriteLine($"集合 {table.Name} 已存在, 跳过迁移");
                       continue;  // 跳过此表的迁移操作
                   }
                   var collection = _mongoDb.GetCollection<BsonDocument>(table.Name);
                   // 将数据转换为 BsonDocument 并插入 MongoDB
                   var bsonList = new List<BsonDocument>();
                   foreach (var row in data)
                   {
                       // 假设 row 为匿名对象，将其转换为 BsonDocument
                       var bsonDoc = row.ToBsonDocument();
                       bsonList.Add(bsonDoc);
                   }

                   collection.InsertMany(bsonList);
                   Console.WriteLine($"成功迁移表：{table.Name} 到 MongoDB");
               }
               else
               {
                   Console.WriteLine($"表 {table.Name} 没有数据, 跳过迁移");
               }
           }

           Console.WriteLine("所有表迁移完成！");
       }
       catch (Exception ex)
       {
           Console.WriteLine($"迁移过程中发生错误: {ex.Message}");
       }
   }

使用Navicat 检查一下表和数据,

代码调整

dbconext 扩展

	
    using MongoDB.Driver;
    using SqlSugar;
    using System;

    namespace RssAdapter.Data
    {
        public enum DbTypeEnum
        {
            Sqlite,
            MongoDB
        }

        public class RssDbContext : IDisposable
        {
            private readonly SqlSugarClient _sqlSugarDb;
            private readonly IMongoDatabase _mongoDb;
            private readonly DbTypeEnum _dbType;

            public RssDbContext(string connectionString, DbTypeEnum dbType)
            {
                _dbType = dbType;

                if (_dbType == DbTypeEnum.Sqlite)
                {
                    // 配置 Sqlite
                    _sqlSugarDb = new SqlSugarClient(new ConnectionConfig
                    {
                        ConnectionString = connectionString,
                        DbType = DbType.Sqlite,
                        IsAutoCloseConnection = true
                    });
                }
                else if (_dbType == DbTypeEnum.MongoDB)
                {
                    // 配置 MongoDB
                    var client = new MongoClient(connectionString);
                    _mongoDb = client.GetDatabase("rsslite"); // MongoDB 的数据库名称
                }
                else
                {
                    throw new ArgumentException("Unsupported DbType");
                }
            }

            // 获取 SqlSugarClient 实例（用于 Sqlite）
            public SqlSugarClient GetSqlSugarDb() => _sqlSugarDb;

            // 获取 MongoDB 实例（用于 MongoDB）
            public IMongoDatabase GetMongoDb() => _mongoDb;

            public DbTypeEnum GetDbType() => _dbType;

            // 实现 IDisposable 释放资源
            public void Dispose()
            {
                if (_dbType == DbTypeEnum.Sqlite && _sqlSugarDb != null)
                {
                    _sqlSugarDb?.Dispose();  // 确保 SqlSugarClient 被释放
                }
                // MongoDB 不需要显示释放资源，连接是自动管理的
            }
        }
    }

  using Microsoft.Extensions.Configuration;
  using RssAdapter.Data;
  using System;

  namespace RssAdapter
  {
      public class DatabaseConfiguration
      {
          private readonly IConfiguration _configuration;
          public DbTypeEnum DbType { get; private set; }

          public DatabaseConfiguration(IConfiguration configuration)
          {
              _configuration = configuration;
          }

          public RssDbContext GetDbContext()
          {
              var dbType = _configuration.GetValue<string>("Database:Type");

              if (dbType == "MongoDB")
              {
                  DbType = DbTypeEnum.MongoDB;
                  var connectionString = _configuration.GetValue<string>("Database:ConnectionString");
                  return new RssDbContext(connectionString, DbTypeEnum.MongoDB);
              }
              else if (dbType == "Sqlite")
              {
                  DbType = DbTypeEnum.Sqlite;
                  var connectionString = _configuration.GetValue<string>("ConnectionStrings:RssLiteDb");
                  return new RssDbContext(connectionString, DbTypeEnum.Sqlite);
              }
              else
              {
                  throw new ArgumentException("Invalid database type");
              }
          }
      }
  }

写入时兼容MongoDB的_id 和 Sqlite的自增Id

var bulkUpdate = new List<WriteModel<FeedItem>>();
var insertList = new List<FeedItem>();

foreach (var item in feed.Items)
{
  var feedItem = ConvertToFeedItem(feed, item, rssNodeId, groupId);
  feedItem._id = ObjectId.GenerateNewId(); //新增字段
   
  if (existingTitles.Select(x=>x.Title).Contains(feedItem.Title))
  {
      // 仅当 Title 相同且 Link 不同的情况下才进行更新
      var filter = Builders<FeedItem>.Filter.And(
          Builders<FeedItem>.Filter.Eq(f => f.Title, feedItem.Title),
          Builders<FeedItem>.Filter.Ne(f => f.Link, feedItem.Link)  // 如果 Link 不同，才更新
      );

      // 如果该条件满足，进行更新
      var update = Builders<FeedItem>.Update
          .Set(f => f.Link, feedItem.Link)
          .Set(f => f.PubDate, feedItem.PubDate)
          .Set(f => f.Excerpt, feedItem.Excerpt)
          .Set(f => f.GroupId, feedItem.GroupId)
          .Set(f => f.IsReaded, feedItem.IsReaded)
          .Set(f => f.RssNodeId, feedItem.RssNodeId);

      bulkUpdate.Add(new UpdateOneModel<FeedItem>(filter, update));
  }
  else
  {
      feedItemsCount= feedItemsCount + 1; //兼容数据
      feedItem.Id = feedItemsCount;
      // 如果 Title 不存在于 MongoDB 中，表示新纪录，加入插入列表
      insertList.Add(feedItem);
  }
}

// 执行批量更新
if (bulkUpdate.Count > 0)
  await feedCollection.BulkWriteAsync(bulkUpdate);

// 执行批量插入
if (insertList.Count > 0)
  await feedCollection.InsertManyAsync(insertList);

在API的Cache服务里查询和缓存时先判断条件是否跨月：

   private async Task<List<FeedItem>> LoadAndCacheFeedItemForNodeAsync(int nodeId, int day)
   {
       string nodeFeedItemCacheKey = FeedItemsCacheKey + nodeId + day;

       if (_cache.TryGetValue(nodeFeedItemCacheKey, out List<FeedItem> cachedFeedItems))
       {
           return cachedFeedItems;
       }

       using (var dbContext = _databaseConfiguration.GetDbContext())
       {
           if (_databaseConfiguration.DbType == DbTypeEnum.Sqlite)
           {
               // Sqlite 数据库操作保持不变
               var db = dbContext.GetSqlSugarDb();
               DateTime sevenDaysAgo = DateTime.Now.AddDays(-day);
               cachedFeedItems = await db.Queryable<FeedItem>()
                   .Where(it => it.PubDate >= sevenDaysAgo && it.RssNodeId == nodeId)
                   .OrderByDescending(x => x.Id)
                   .ToListAsync();
           }
           else if (_databaseConfiguration.DbType == DbTypeEnum.MongoDB)
           {
               var mongoDb = dbContext.GetMongoDb();
               DateTime targetDate = DateTime.Now.AddDays(-day);

               // 获取当前月份的表名
               var currentMonth = DateTime.Now.ToString("yyyyMM");
               var currentCollectionName = $"FeedItem_{currentMonth}";
               var currentCollection = mongoDb.GetCollection<FeedItem>(currentCollectionName);

               // 构建基础过滤条件
               var baseFilter = Builders<FeedItem>.Filter.Eq(x => x.RssNodeId, nodeId);
               var dateFilter = Builders<FeedItem>.Filter.Gte(x => x.PubDate, targetDate);
               var filter = baseFilter & dateFilter;

               if (day > DateTime.Now.Day)
               {
                   // 需要查询上个月的数据
                   var previousMonth = DateTime.Now.AddMonths(-1).ToString("yyyyMM");
                   var previousCollectionName = $"FeedItem_{previousMonth}";
                   var previousCollection = mongoDb.GetCollection<FeedItem>(previousCollectionName);

                   // 查询上个月的数据
                   var previousMonthItems = await previousCollection.Find(filter)
                       .SortByDescending(x => x.Id)
                       .ToListAsync();

                   // 查询当前月份的数据
                   var currentMonthItems = await currentCollection.Find(filter)
                       .SortByDescending(x => x.Id)
                       .ToListAsync();

                   // 合并并排序结果
                   cachedFeedItems = previousMonthItems.Concat(currentMonthItems)
                       .OrderByDescending(x => x.Id)
                       .ToList();
               }
               else
               {
                   // 只查询当前月份的数据
                   cachedFeedItems = await currentCollection.Find(filter)
                       .SortByDescending(x => x.Id)
                       .ToListAsync();
               }
           }
           else
           {
               throw new ArgumentException("Unsupported DbType");
           }

           if (cachedFeedItems?.Count > 0)
           {
               // 设置缓存过期时间
               var cacheEntryOptions = new MemoryCacheEntryOptions()
                   .SetAbsoluteExpiration(TimeSpan.FromDays(day));
               _cache.Set(nodeFeedItemCacheKey, cachedFeedItems, cacheEntryOptions);
           }
       }

       return cachedFeedItems ?? new List<FeedItem>();
   }

个人资料

记简阅R.MaiFeiPin.Com 的数据库从Sqlite 升级到到MongoDB

旧数据迁移

代码调整

下次升级，就是UI了，当然也有可能先搞 Rss AI 自动总结和推送

个人资料

记简阅R.MaiFeiPin.Com 的数据库从Sqlite 升级到 到MongoDB

旧数据迁移

代码调整

下次升级，就是UI了，当然也有可能先搞 Rss AI 自动总结和推送

记简阅R.MaiFeiPin.Com 的数据库从Sqlite 升级到到MongoDB