Sqlstone：每个用户拥有一个个人数据库副本：解决后端挑战（数据可用性/所有权）

• 下载 sqlstone_v001.zip - 20.4 MB

 GitHub - raddevus/sqlstone：C# .NET Core 8.x ASP.NET MVC，为每个注册用户创建新的 sqlite 数据库以存储他们自己的数据。原因？为什么不呢？[^]

引言

我想写一个 SaaS（软件即服务），它

1. 需要尽可能少的支持和维护
2. 可用性高
3. 解决真实用户面临的问题
4. 运行成本低
5. 能赚钱

随着时间的推移，我解决了许多这些挑战。例如，我通过发现如何让 .NET Core WebAPI 在 DigitalOcean（基于 Debian）的 droplet（每月仅需 6 美元）上运行，从而实现了以上列表中的第一、第二和第四项。

存储数据：主要挑战

在现代应用程序开发中，真正的挑战在于数据存储领域。

构建前端相对容易，基本上只需要一些文本文件、文本编辑器和网页浏览器。（注意：我并不是说创建美观、功能齐全的 UI 设计很容易。我说的是这些挑战更多地不在于技术领域（而在于后端）。）

相比之下，将数据保存为可从任何设备远程访问，仍然是技术挑战列表中一项艰巨的任务。

此外，本文可以看作是我之前在 CP 上发布的文章的第二部分，我曾提出问题：Sqlite 能处理多少？多个线程并发插入 Sqlite[^]

让数据从任何设备访问

如今，用户拥有多个设备（iPad、Android 手机、台式电脑、笔记本电脑），并可能希望在任何给定时间从任何一个设备访问他们的数据（使用您的应用程序）。

要使数据可从任何设备访问，您需要构建一些服务器端软件。此外，您还需要知道如何配置各种软件（Web 服务器、数据库等）。您还必须创建数据模式（用于存储用户数据的结构）。之后，您需要确保数据始终可用

1. 确保网站正常运行，
2. 确保数据库正常运行，
3. 确保您的应用程序连接正常

当您尝试使数据可从任何设备和任何位置访问时，需要做的事情太多了。

本文内容

本文旨在通过以下方式，至少在一定程度上简化这些挑战：

1. 创建一种可重复的方式，为每个用户提供他们自己数据的副本
2. 使用户能够管理他们自己数据的可访问性（提供下载他们数据库副本并将其用于关联应用程序的离线本地副本的能力）。

使用的技术

为了实现这些目标，我使用了最简单的可用技术（我认为这是存储用户数据最直接/最简单的方式）。

1. .NET Core WebAPI - 用于将数据发布到远程数据存储
2. Sqlite - 支持 SQL 查询的基于文本的数据库（因此用户可以以多种方式使用他们的数据）
3. Entity Framework Core - 过去我从不使用 EF，因为它感觉不好，我喜欢创建自己的存储过程。然而，Sqlite 本身不支持存储过程，而且此项目使用的 SQL 非常简单，使用 EF Core 是有意义的。此外，一旦你开始使用 EF，你就会有点沉迷于它，因为它非常容易。🤓

WebAPI 框架将提供什么

我使用这些技术提供了一种框架，开发人员可以快速在此基础上构建，以创建特定的目标 SaaS 并创建最终用户应用程序。

示例目标应用程序

一个示例目标应用程序（包装在我极其精简的“框架”中）是一个每日日记应用程序，它允许用户存储他们完成的活动、他们的想法等日常自由格式的笔记。

Sqlstone：项目命名的重要性

开始一个项目，你需要做的第一件事就是起一个很酷/独特项目名称。要起一个很酷和独特项目名称，你应该把一些不一定有意义的词组合在一起。

这就是我将这个项目命名为 Sqlstone 的原因。看看它是否让你感到困惑，并激发了你对这将是一个巨大的开源项目的信心？🤓😆

每日日记应用：在线试用

我已经在 Sqlstone 的基础上构建了一个极其基础的每日日记应用程序版本，我们将使用该代码来展示每个用户获取自己数据库的这个想法是如何工作的。

您可以在我的网站上试用该应用程序：https://newlibre.com/journal^ 

我将引导您完成使用每日日记应用程序的步骤，但首先让我们谈谈简单的 Sqlstone 框架，以了解它能为您的应用程序做什么。

Sqlstone 总结

Sqlstone 项目为您做的最主要的事情是

将您的自定义 Sqlite 数据库复制到用户的文件空间（在您的 Web 服务器上）。

用户的 文件空间位于何处？

wwwroot

对于 .NET Core WebAPI 和 MVC 应用程序，项目会创建一个 wwwroot 文件夹，Web 内容将从中提供。

UUID 文件夹名称

在该文件夹下，Sqlstone 代码将创建一个基于 UUID 的唯一命名文件夹。

当用户注册由目标应用程序生成的 UUID 时，将为该用户创建该文件夹。我们将在本文稍后介绍每日日记应用程序时，通过相关的屏幕截图看到所有这些。

Sqlstone 代码做什么？

Sqlstone 的几乎所有代码都实现在 UserController.cs 类中。

当用户发布到 UserController 以注册其 UUID 时，系统将调用 RegisterUser API 方法。调用该方法时，将运行 UserController 构造函数并配置一些项。

让我们看看 UserController 构造函数，因为它有助于阐明一些问题。

public UserController(ILogger<UserController> logger, 
            IConfiguration _configuration,
            IWebHostEnvironment webHostEnvironment)
    {
        _logger = logger;
        templateDbFile = _configuration["templateDbFile"];
        Console.WriteLine($"content rootPath: {webHostEnvironment.WebRootPath}");
        webRootPath = webHostEnvironment.WebRootPath;
        contentRootPath = webHostEnvironment.ContentRootPath;
    }

使用了两个注入的接口

默认的 Controller 通常只有一个 ILogger 参数。但在我们的 UserController 中，我注入了两个接口：

1. IConfiguration
2. IWebHostEnvironment

我添加了这些，以便我们可以

1. 从我们的应用程序配置（在项目中的 appSettings.json 文件中设置）读取值。
2. 读取一些 WebHost 环境设置，我们将使用它们来将目标解决方案的 Sqlite 数据库的新副本存储在用户的文件空间中。

appSettings.json：templateDbFile

templateDbFile 是将包含目标项目（您正在构建在 Sqlstone 之上的项目）所有表的文件。在我们的例子中，这将是每日日记应用程序的数据库。

这是 Journal 应用程序源代码中定义的 appSettings.json 文件的全部内容。

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "templateDbFile": "sqlstone_journal.db"
}

Logging 部分包含 .NET Core MVC 应用程序中包含的默认值。

我们想在这里关注的值是 templateDbFile，我将其设置为：sqlstone_journal.db。

这是在以下代码行上从 UserController 构造函数读取的值：

templateDbFile = _configuration["templateDbFile"];

这允许我们使用我们想要的任何值来引用目标数据库。

在构造函数中，我们将值加载到一个成员变量中，以便稍后（在 RegisterUser() API 调用中）使用。

IWebHostEnvironment：获取路径

现在，我们需要用于

1. 复制数据库模板文件
2. 将数据库复制到用户的 UUID 文件夹

由于我们将 IWebHostEnvironment 注入了构造函数，我们现在可以获取

1. webRootPath：Web 根目录的路径（这基本上是 wwwroot 上方的文件夹——这是您解决方案的 Web 二进制文件部署到的位置）。
2. contentRootPath：wwwroot 文件夹的路径（所有内容都从 wwwroot 文件夹提供）。

现在我们对这些设置有所了解，我们可以讨论当用户注册其 UUID 时会发生什么。

用户/UUID 注册

为了让用户使用基于这个非常简单的 Sqlstone 框架构建的应用程序，他们必须注册一个 UUID。

注册会创建用户文件空间和个人数据库

这将是所有基于 Sqlstone 构建的应用程序所必需的，因为它将

1. 创建用户的文件空间（在 wwwroot 目录下方）。目录格式为 wwwroot/<UUID>。
2. 将目标 Sqlite 数据库的新实例复制到用户的 wwwroot/<UUID> 目录中。

非常简单的标识

注册 UUID 创建了一种非常简单的方式来识别用户的内容将存储在哪个数据库中。

是的，这意味着（目前）存储数据在目标数据库中不需要密码。我们稍后会进一步讨论这一点。（提示：在未来的文章中，我将展示如何使用认证 AES256 加密来加密每个用户的数据。）

保持主要思想，主要焦点

目前，请理解这是一个原型，用于确定允许每个用户拥有自己 Sqlite 数据库副本的这种方法是否可行。

用户注册 UUID 时的步骤工作流程

这是接收代表用户 UUID 的字符串的整个 RegisterUser() 方法。

[HttpPost]
    public ActionResult RegisterUser([FromQuery] string uuid){
        Console.WriteLine($"uuid: {uuid}");
        User u = new User(uuid);
        var ipAddr = HelperTool.GetIpAddress(Request);
        
        var userDir = Path.Combine(webRootPath,uuid);
        var journalDb = Path.Combine(contentRootPath,templateDbFile);
        
        Directory.CreateDirectory(userDir);
        var userDbFile = Path.Combine(userDir,templateDbFile);
        Console.WriteLine($"userDbFile: {userDbFile}");
        if (!System.IO.File.Exists( userDbFile)){
            
            try{
                Console.WriteLine($"{journalDb} \n {userDir}");
                System.IO.File.Copy(journalDb,Path.Combine(userDir, userDbFile));
            }
            catch{
                return new JsonResult(new {result=false, error="Couldn't register user. Try again."});
            }
            UuidInfo info = new UuidInfo{Uuid=uuid,IpAddr=ipAddr};
            UuidInfoContext uuidCtx = new UuidInfoContext(contentRootPath);
            uuidCtx.Add(info);
            uuidCtx.SaveChanges();
        }
        else{
            Console.WriteLine("User is already registered.");
        }
    

        return new JsonResult(new {result=true, directory=webRootPath, ip=ipAddr});
    }

这基本上是用户注册期间发生的事情。

1. 获取用户发布的数据中的 UUID。是的，在这种情况下，我让客户端生成 UUID。（我将在本文后面向您展示执行此操作的 JavaScript。）我可以轻松地让 .NET C# 代码生成 UUID，但我只是随意决定将其放在客户端。如果您希望如此，您可以轻松地将此方法转换为在服务器端执行。
2. 获取 IP 地址，以便我们可以跟踪注册每个 UUID 的 IP 地址。我这样做只是为了控制有人生成数千个 UUID（以及相应的文件夹）的攻击。
3. 如果用户的文件空间（wwwroot 下的 UUID 目录）不存在，则创建它。
4. 检查 UserDbFile 是否已存在于用户文件空间中。如果模板文件已创建，我们显然不想覆盖它。
5. 如果用户的 db 文件尚不存在，则复制位于 webRootPath\sqlstone_journal.db 的模板文件。
6. 创建 UuidInfo 对象，这是一个简单的对象，包装了 UUID、IpAddress 和 Created 日期。
7. 将 UuidInfo 数据存储在 sqlstone.db 中。sqlstone.db 仅供 Sqlstone 框架的管理员访问。它只是一个简单的方法来确定有多少用户注册试用该项目。

就是这样。用户注册后，她将能够使用系统的其余部分。我将引导您使用 Journal App，以便您可以在我的网站上试用（或下载代码并在本地试用）。

但是，首先，请允许我多解释一下数据库模板文件。

关于数据库模板文件的更多信息

数据库模板文件 (sqlstone_journal.db) 是一个空数据库，其中只包含目标项目的表架构。

对于 Journal App，数据库中只有一个名为 JEntry（Journal Entry）的表，定义如下：

CREATE TABLE [JEntry]
( [ID] INTEGER NOT NULL PRIMARY KEY,
  [Title] NVARCHAR(250) check(length(Title) <= 250),
  [Note] NVARCHAR(3000) NOT NULL check(length(Title) <= 3000),
  [Created] NVARCHAR(30) default (datetime('now','localtime')) 
  check(length(Created) <= 30),
  [Updated] NVARCHAR(30) check(length(Updated) <= 30)
);

您可以在项目源代码的 sql 文件夹中的文件中找到该定义。

在您的目标解决方案中，您将拥有一组不同的表，这些表将由您的目标应用程序用来存储其数据。然后，您将您的数据库模板文件（仅包含表，不包含数据）放在 webRootFolder（wwwroot 上方的文件夹）中，每次用户注册使用您的目标应用程序时，模板数据库将复制到她的 wwwroot/<UUID> 文件夹中，以便她自己的数据将保存在她自己的私有副本中。

现在我们已经讨论了这个非常小的 Sqlstone 框架的基本工作原理，让我们来看看一个构建在其之上的解决方案。

Journal App：构建在 Sqlstone 之上

您可能在想，“嗯……这是一个非常简单的想法。他所做的只是为每个用户提供他们自己的 Sqlite 数据库。”您是对的。这很简单。现在我想知道它是否可以在真正的生产环境中使用。我认为它会起作用。

这是我的 Journal App 目前（简单且基础的 UI）的截图。

这是用户已经

1. 注册了 UUID
2. 添加了一些数据

之后的状态快照。

在注册 UUID 之前

但是，当您访问我的网站并加载 Journal App（https://newlibre.com/journal^）或在本地运行时，您将看到不同的内容，因为您的 UUID 尚未注册。

如您所见，用户尚未生成或注册 UUID，因此这里没有太多内容。

生成并设置 UUID

首先，单击 [Gen / Set UUID] 按钮。

1. 这将
2. 生成一个全新的 UUID
3. 将其存储在您的 localStorage 中（现在您从这台设备和浏览器访问此页面时都会看到该 UUID）。

将其显示在 UUID 文本框中。

注册您的 UUID

您现在必须单击 [Register UUID] 按钮，将 UUID 发布到服务器，以便它可以创建您的用户文件空间并将数据库模板文件复制到该空间。

客户端 UI 会通过临时警报告诉您 UUID 已注册。

现在您可以创建您的第一个日记条目。

注意：Journal App 在您首先生成并注册 UUID 之前，不允许您创建新的 Journal Entry。

创建您的第一个日记条目

现在您已经注册了 UUID 并创建了您的远程文件系统空间，您就可以创建一个日记条目了。

单击 [Add New Entry] 按钮，您将看到一个空白条目出现。

填写 Note 字段（HTML TextArea）中的文本。您甚至可以保存表情符号，如果您愿意的话。

单击 [Save] 按钮将数据 POST 到网站并将其保存在您的 sqlstone_journal.db 副本中。

当您单击 [Save] 按钮时，将弹出一个 Prompt 对话框，以便您添加标题（如果需要）。

要保存标题，只需在 Prompt 字段中键入文本并按 <ENTER> 或单击 [OK] 按钮。

如果您不想保存标题，您可以单击 [Cancel] 按钮或按 <ESC> 按钮。

您的数据将被发布到网站并存储在您的 Sqlite 数据库中。

 

再次，您会看到一个提示，表明数据已保存。

您也可以编辑您的数据
如果您想更改或添加 Note 字段，或者只是添加一个 Title，那么只需进行更改并再次单击 [Save] 按钮。这一次，记录的数据将在数据库中更新。

 

如果您确实更新了数据，那么 Updated 字段将显示您上次更新的日期。

现在您已经看到它的实际运行情况，让我们看看当用户将条目保存到 Journal 中时会发生什么。

用户目标数据库的使用方式

当您在 Sqlstone 的基础上进行构建时，您将确保您的 WebAPI 类方法的构造函数看起来与我之前展示的构造函数非常相似。

JournalEntry 构造函数

Journal App 的目标数据库有一个名为 JEntry 的表。当用户创建新记录时，应用程序将发布到 JournalEntryController 类，该类有一个 Save 方法。

public JournalEntryController(ILogger<JournalEntryController> logger, 
            IConfiguration _configuration,
            IWebHostEnvironment webHostEnvironment)
    {
        _logger = logger;
        templateDbFile = _configuration["templateDbFile"];
        Console.WriteLine($"content rootPath: {webHostEnvironment.WebRootPath}");
        webRootPath = webHostEnvironment.WebRootPath;
        contentRootPath = webHostEnvironment.ContentRootPath;
    }

这是该类的构造函数：

每次用户发布数据时，我们都会引用他们的数据库副本

这基本上是我们之前为 UserController 查看的构造函数的精确副本。这是因为每次用户向目标应用程序（在本例中为 Journal App）发布数据时，我们需要知道数据应该保存在哪个数据库中。由于我们使用的是用户特定的数据库，我们需要知道目标数据库的名称。

当用户发布数据时，我们需要另外两项

1. 用户的 UUID -- 这样我们就可以访问存储其数据库的文件空间目录。
2. 将要保存或更新的 JournalEntry。

以下是用户将发布到的 Save 方法的外观。

 [HttpPost]
    public ActionResult Save([FromForm] String uuid,[FromForm] JournalEntry jentry){
        Console.WriteLine(jentry.Note);
        Console.WriteLine(jentry.Title);
        
        ConvertEmptyStringToNull(jentry);
        var userDir = Path.Combine(webRootPath,uuid);
        var userDbFile = Path.Combine(userDir,templateDbFile);
        try{
            JournalEntryContext jec = new JournalEntryContext(userDbFile);
            // id = 0 indicates a new jentry
            if (jentry.Id == 0){
                jec.Add(jentry);
            }
            else{
                JournalEntry? currentEntry = jec.Find<JournalEntry>(jentry.Id);
                currentEntry.Note = jentry.Note;
                currentEntry.Title = jentry.Title;
                currentEntry.Updated = DateTime.Now.ToString("yyyy-MM-dd");
                Console.WriteLine($"updated; {currentEntry.Updated}");
                jec.Update(currentEntry);
                jentry = currentEntry;
                Console.WriteLine($"updated; {jentry.Updated}");

            }
            jec.SaveChanges();
            
        }
        catch (Exception ex){
            // It's possible that the user has attempted to save an Entry
            // but has never registered the UUID they see in their text box.
            return new JsonResult(new {success=false,error=$"{ex.Message}"});
        }

        return new JsonResult(new {success=true,jentry=jentry});
    }

这里的代码重点在于我们如何构建到用户特定数据库文件的路径。

// Create the path to the user's directory using their UUID
var userDir = Path.Combine(webRootPath,uuid);

// Use the wwwroot/<UUID> directory and append the name of the 
// db file (sqlstone_journal.db)
var userDbFile = Path.Combine(userDir,templateDbFile);

try{

     // Create DbContext so we can do SQL queries against user's db
     JournalEntryContext jec = new JournalEntryContext(userDbFile);

获取用户的所有日记条目

获取用户的日记条目非常相似。在这种情况下，我们调用 JournalEntryController 的 GetAll 方法，再次传递 UUID，以便我们引用正确的数据库。

[HttpPost]
    public ActionResult GetAll([FromForm] string uuid){
        var userDir = Path.Combine(webRootPath,uuid);
        var userDbFile = Path.Combine(userDir,templateDbFile);

        if (!System.IO.File.Exists(userDbFile)){
            return new JsonResult(new {result="No data"});
        }

        JournalEntryContext jec = new JournalEntryContext(userDbFile);

        var entries = jec.JEntry;

        List<JournalEntry> allItems = new List<JournalEntry>();

        foreach (JournalEntry je in entries.AsParallel<JournalEntry>()){
            Console.WriteLine($"{je.Id} : {je.Title} : {je.Note}");
            allItems.Add(je);
        }

        return new JsonResult(allItems);
    }

就是这样。我们只需要在客户端使用一些 ReactJS 来迭代从 GetAll 方法返回的 JSON，以便我们可以显示所有行。

使用摘要

这意味着您可以完成所有这些工作，通过

1. 创建您的 Sqlite 数据库架构 -- 创建一个包含将用作模板的表的 Sqlite 数据库。
2. 修改 appSettings.json 以引用您的 Sqlite 数据库。
3. 将构造函数代码添加到您的 Controller 中。
4. 在每个 Controller 方法中包含 UUID，以便您可以确定要引用哪个用户的数据库。

获取代码并本地运行

从本文顶部的 GitHub 仓库获取源代码（链接在此处），或从本文下载代码并试用。

您需要 .NET Core 8.x。

下载并解压缩代码后，只需从主 sqlstone 解决方案目录运行以下命令：

$ dotnet run --project sqlstone

如果您在项目目录（.csproj 所在位置）中，则只需执行以下操作：

$ dotnet run

忘记提及：从 UUID 加载数据

我忘记提到这个应用程序的一个重要功能。我认为它很酷，所以我想提一下。

如果您在一台设备上（例如台式电脑）注册了您的 UUID 并保存了许多日记条目，您可以在任何其他设备上检索这些数据。

将数据检索到任何设备

由于 JournalEntryController 的 GetAll 方法依赖于 UUID，如果您在主表单中提供 UUID，它将立即检索所有数据。

这就是我的意思。

检索数据步骤

1. 在一台设备上保存一些数据
2. 将您的 UUID 写下来或发送到另一台设备
3. 在第二台设备上加载 https://newlibre.com/journal
4. 将您的 UUID 粘贴（或键入）到 UUID 文本框中
5. 单击 [Gen/Set UUID] 按钮 -- 这将加载 UUID 并立即从远程服务器检索任何相关数据。

酷不酷！

有什么影响？

我的理论是，这将“更好”地扩展。现在，您的应用程序将连接到单个 Sqlite 数据库，而是连接到单独的用户数据库。正如我在上一篇关于 Sqlite 如何处理数据的文章中所学到的，这将缓解当其他用户正在插入单个数据库实例时可能发生的等待。

减少数据库插入等待

由于 .NET Core WebAPIs 和 WebApps 应该处理所有并发连接，我相信这将大大缓解数据库资源等待时间。

将数据库维护/数据所有权交由用户负责

使用 Sqlstone，而不是担心备份用户数据或以任何方式控制它，我们可以将所有这些都转交给用户。如果他们想要本地数据，我们将允许他们下载整个 Sqlite 数据库（将在下一篇文章中介绍）。

如果他们想完全删除服务器上的 Sqlite 数据库，我们将允许他们销毁 UUID 和所有相关数据（将在下一篇文章中介绍）。

下一篇文章/更多 Sqlstone 功能

在下一篇文章中，我们将添加三个新功能：

1. 用户删除单个日记条目
2. 用户下载整个 Sqlite 数据库（以及用户如何本地使用它）
3. 用户删除远程数据库和 UUID。
4. 添加 UUID 的 QR 码生成器，以便轻松获取 UUID 并在其他设备上加载数据

讨论：您怎么看？

我希望您能看看代码，试试看，并告诉我您的想法。

我对这种方法是否是提供“高可用性”数据和用户数据所有权的有效方式非常感兴趣。我也对这是否能更容易地确保用户数据得到维护（不丢失，因为他们可以下载）很感兴趣。

我以一个试图支持完整系统的单一开发者的身份考虑这些问题（作为个体经营者需要戴很多顶帽子）。而且，这也是一种替代亚马逊网络服务等方案的方法，因为我必须担心成本超支以及由于过度使用而突然收到巨额账单。

历史

首次发布：2024-06-20