[MAUI 项目实战] 笔记App（二）：数据库设计

@

Sqlite配置

应用程序里使用Sqlite作为数据库，使用EntityFramworkCore作为ORM，使用CodeFirst方式用EFCore初始化Sqlite数据库文件：
mato.db

在MatoProductivity.Core项目的
appsettings.json
中添加本地sqlite连接字符串

  "ConnectionStrings": {
    "Default": "Data Source=file:{0};"
  },
  ...

这里文件是一个占位符，通过代码hardcode到配置文件

在MatoProductivityCoreModule.cs中，重写PreInitialize并设置Configuration.DefaultNameOrConnectionString：

public override void PreInitialize()
{
    LocalizationConfigurer.Configure(Configuration.Localization);

    Configuration.Settings.Providers.Add<CommonSettingProvider>();

    string documentsPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), MatoProductivityConsts.LocalizationSourceName);

    var configuration = AppConfigurations.Get(documentsPath, development);
    var connectionString = configuration.GetConnectionString(MatoProductivityConsts.ConnectionStringName);

    var dbName = "mato.db";
    string dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), MatoProductivityConsts.LocalizationSourceName, dbName);

    Configuration.DefaultNameOrConnectionString = String.Format(connectionString, dbPath);
    base.PreInitialize();
}

创建实体

接下来定义实体类

笔记实体类

笔记用于存储实体，在笔记列表中，每个笔记都有标题和内容，创建时间等内容。

定义于
\MatoProductivity.Core\Models\Entities\Note.cs

public class Note : FullAuditedEntity<long>
{
    public Note()
    {

    }
    public Note(string name, bool isHidden, bool isRemovable)
    {
        Title = name;
        IsHidden = isHidden;
        IsRemovable = isRemovable;
    }


    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }

    public ICollection<NoteSegment> NoteSegments { get; set; }

    public string Title { get; set; }
    public string Type { get; set; }
    public string Status { get; set; }
    public string Desc { get; set; }
    public string Icon { get; set; }
    public string Color { get; set; }
    public string BackgroundColor { get; set; }
    public string BackgroundImage { get; set; }

    public string PreViewContent { get; set; }

    public bool IsEditable { get; set; }

    public bool IsHidden { get; set; }

    public bool IsRemovable { get; set; }
    public bool CanSimplified { get; set; }

}

笔记分组实体

定义于
\MatoProductivity.Core\Models\Entities\NoteGroup.cs

public class NoteGroup : FullAuditedEntity<long>
{
    public NoteGroup()
    {

    }
    public NoteGroup(string name, bool isHidden, bool isRemovable)
    {
        Title = name;
        IsHidden = isHidden;
        IsRemovable = isRemovable;
    }

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }
    public string Title { get; set; }

    public bool IsHidden { get; set; }

    public bool IsRemovable { get; set; }

    public ICollection<Note> Notes { get; set; }
}

笔记片段实体

定义于
\MatoProductivity.Core\Models\Entities\NoteSegment.cs

public class NoteSegment : FullAuditedEntity<long>, INoteSegment
{
    public NoteSegment()
    {

    }



    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }

    [ForeignKey(nameof(NoteId))]
    public Note Note { get; set; }

    public ICollection<NoteSegmentPayload> NoteSegmentPayloads { get; set; }

    public long NoteId { get; set; }

    public string Title { get; set; }
    public string Type { get; set; }
    public string Status { get; set; }
    public string Desc { get; set; }
    public string Icon { get; set; }
    public string Color { get; set; }
    public int Rank { get; set; }

    public bool IsHidden { get; set; }

    public bool IsRemovable { get; set; }


    public INoteSegmentPayload GetNoteSegmentPayload(string key)
    {
        if (NoteSegmentPayloads != null)
        {
            return NoteSegmentPayloads.FirstOrDefault(c => c.Key == key);
        }
        return default;
    }




    public void SetNoteSegmentPayload(INoteSegmentPayload noteSegmentPayload)
    {
        if (NoteSegmentPayloads != null)
        {
            var currentPayload = NoteSegmentPayloads.FirstOrDefault(c => c.Key == noteSegmentPayload.Key);
            if (currentPayload != null)
            {
                NoteSegmentPayloads.Remove(currentPayload);
            }
            if (!this.IsTransient())
            {
                (noteSegmentPayload as NoteSegmentPayload).NoteSegmentId = this.Id;
            }
            NoteSegmentPayloads.Add((noteSegmentPayload as NoteSegmentPayload));
        }
    }

    public INoteSegmentPayload GetOrSetNoteSegmentPayload(string key, INoteSegmentPayload noteSegmentPayload)
    {
        if (NoteSegmentPayloads != null)
        {
            var currentPayload = NoteSegmentPayloads.FirstOrDefault(c => c.Key == key);
            if (currentPayload != null)
            {
                return currentPayload;
            }
            if (noteSegmentPayload != null)
            {
                if (!this.IsTransient())
                {
                    (noteSegmentPayload as NoteSegmentPayload).NoteSegmentId = this.Id;
                }
                NoteSegmentPayloads.Add((noteSegmentPayload as NoteSegmentPayload));
            }
            return noteSegmentPayload;
        }
        return noteSegmentPayload;
    }

}

笔记片段负载实体

笔记片段负载与笔记片段实体为一对多的关系，用于存储笔记片段的详细内容。

定义于
\MatoProductivity.Core\Models\Entities\NoteSegmentPayload.cs

public class NoteSegmentPayload : FullAuditedEntity<long>, INoteSegmentPayload
{
    public NoteSegmentPayload()
    {

    }


    public NoteSegmentPayload(string key, object value, string valuetype = null)
    {
        if (value is string)
        {
            this.SetStringValue((value as string).ToString());
        }
        else if (value is byte[])
        {
            this.Value = value as byte[];
        }
        else if (value is DateTime)
        {
            this.SetStringValue(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"));
        }
        else
        {
            this.SetStringValue(value.ToString());
        }
        this.Key = key;
        this.ValueType = valuetype;

    }




    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }

    [ForeignKey(nameof(NoteSegmentId))]
    public NoteSegment NoteSegment { get; set; }

    public long NoteSegmentId { get; set; }

    public string Key { get; set; }

    public byte[] Value { get; set; }

    public string ValueType { get; set; }

    [NotMapped]
    public string StringValue => GetStringValue();

    public T GetConcreteValue<T>() where T : struct
    {
        var value = Encoding.UTF8.GetString(Value);
        T result = value.To<T>();
        return result;
    }

    public string GetStringValue()
    {
        var value = Encoding.UTF8.GetString(Value);
        return value;
    }

    public void SetStringValue(string value)
    {
        this.Value = Encoding.UTF8.GetBytes(value);
    }
}

笔记片段仓库实体

用于在编辑笔记页面的添加片段菜单中，加载所有可用的片段

定义于
\MatoProductivity.Core\Models\Entities\NoteSegmentStore.cs

public class NoteSegmentStore : Entity<long>
{
    
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }
    public string Title { get; set; }
    public string Type { get; set; }
    public string Category { get; set; }
    public string Status { get; set; }
    public string Desc { get; set; }
    public string Icon { get; set; }
    public string Color { get; set; }
    public bool IsHidden { get; set; }

    public bool IsRemovable { get; set; }


}

笔记模板（场景）实体

定义于
\MatoProductivity.Core\Models\Entities\NoteTemplate.cs

public class NoteTemplate : FullAuditedEntity<long>
{
    public NoteTemplate()
    {

    }
    public NoteTemplate(string name, bool isHidden, bool isRemovable)
    {
        Title = name;
        IsHidden = isHidden;
        IsRemovable = isRemovable;
    }
    
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }

    public ICollection<NoteSegmentTemplate> NoteSegmentTemplates { get; set; }

    public string Title { get; set; }
    public string Type { get; set; }
    public string Status { get; set; }
    public string Desc { get; set; }
    public string Icon { get; set; }
    public string Color { get; set; }
    public string BackgroundColor { get; set; }
    public string BackgroundImage { get; set; }

    public string PreViewContent { get; set; }

    public bool IsEditable { get; set; }

    public bool IsHidden { get; set; }

    public bool IsRemovable { get; set; }

    public bool CanSimplified { get; set; }


}

笔记片段模板实体

定义于
\MatoProductivity.Core\Models\Entities\NoteSegmentTemplate.cs

public class NoteSegmentTemplate : FullAuditedEntity<long>, INoteSegment
{
    public NoteSegmentTemplate()
    {

    }



    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }

    [ForeignKey(nameof(NoteTemplateId))]
    public NoteTemplate NoteTemplate { get; set; }

    public ICollection<NoteSegmentTemplatePayload> NoteSegmentTemplatePayloads { get; set; }

    public long NoteTemplateId { get; set; }

    public string Title { get; set; }
    public string Type { get; set; }
    public string Status { get; set; }
    public string Desc { get; set; }
    public string Icon { get; set; }
    public string Color { get; set; }
    public int Rank { get; set; }

    public bool IsHidden { get; set; }

    public bool IsRemovable { get; set; }


    public INoteSegmentPayload GetNoteSegmentPayload(string key)
    {
        if (NoteSegmentTemplatePayloads != null)
        {
            return NoteSegmentTemplatePayloads.FirstOrDefault(c => c.Key == key);
        }
        return default;
    }

    public void SetNoteSegmentPayload(INoteSegmentPayload noteSegmentPayload)
    {
        if (NoteSegmentTemplatePayloads != null)
        {
            var currentPayload = NoteSegmentTemplatePayloads.FirstOrDefault(c => c.Key == noteSegmentPayload.Key);
            if (currentPayload != null)
            {
                NoteSegmentTemplatePayloads.Remove(currentPayload);
            }
            if (!this.IsTransient())
            {
                (noteSegmentPayload as NoteSegmentTemplatePayload).NoteSegmentTemplateId = this.Id;
            }
            NoteSegmentTemplatePayloads.Add((noteSegmentPayload as NoteSegmentTemplatePayload));
        }
    }

    public INoteSegmentPayload GetOrSetNoteSegmentPayload(string key, INoteSegmentPayload noteSegmentPayload)
    {
        if (NoteSegmentTemplatePayloads != null)
        {
            var currentPayload = NoteSegmentTemplatePayloads.FirstOrDefault(c => c.Key == key);
            if (currentPayload != null)
            {
                return currentPayload;
            }
            if (noteSegmentPayload != null)
            {
                if (!this.IsTransient())
                {
                    (noteSegmentPayload as NoteSegmentTemplatePayload).NoteSegmentTemplateId = this.Id;
                }
                NoteSegmentTemplatePayloads.Add((noteSegmentPayload as NoteSegmentTemplatePayload));
            }
            return noteSegmentPayload;
        }
        return noteSegmentPayload;
    }

}

笔记片段模板负载实体

定义于
\MatoProductivity.Core\Models\Entities\NoteSegmentTemplatePayload.cs

public class NoteSegmentTemplatePayload : FullAuditedEntity<long>, INoteSegmentPayload
{
    public NoteSegmentTemplatePayload()
    {

    }



    public NoteSegmentTemplatePayload(string key, object value, string valuetype = null)
    {
        if (value is string)
        {
            this.SetStringValue((value as string).ToString());
        }
        else if (value is byte[])
        {
            this.Value = value as byte[];
        }
        else if (value is DateTime)
        {
            this.SetStringValue(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"));
        }
        else
        {
            this.SetStringValue(value.ToString());
        }
        this.Key = key;
        this.ValueType = valuetype;

    }



    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override long Id { get; set; }

    [ForeignKey(nameof(NoteSegmentTemplateId))]
    public NoteSegmentTemplate NoteSegmentTemplate { get; set; }

    public long NoteSegmentTemplateId { get; set; }

    public string Key { get; set; }

    public byte[] Value { get; set; }

    public string ValueType { get; set; }

    [NotMapped]
    public string StringValue => GetStringValue();

    public T GetConcreteValue<T>() where T : struct
    {
        var value = Encoding.UTF8.GetString(Value);
        T result = value.To<T>();
        return result;
    }

    public string GetStringValue()
    {
        var value = Encoding.UTF8.GetString(Value);
        return value;
    }

    public void SetStringValue(string value)
    {
        this.Value = Encoding.UTF8.GetBytes(value);
    }
}

配置EF

数据库上下文对象
MatoProductivityDbContext
定义如下

    public class MatoProductivityDbContext : AbpDbContext
    {
        //Add DbSet properties for your entities...

        public DbSet<Note> Note { get; set; }
        public DbSet<NoteGroup> NoteGroup { get; set; }
        public DbSet<NoteSegment> NoteSegment { get; set; }
        public DbSet<NoteSegmentStore> NoteSegmentStore { get; set; }
        public DbSet<NoteSegmentPayload> NoteSegmentPayload { get; set; }
        public DbSet<NoteTemplate> NoteTemplate { get; set; }
        public DbSet<NoteSegmentTemplate> NoteSegmentTemplate { get; set; }
        public DbSet<NoteSegmentTemplatePayload> NoteSegmentTemplatePayload { get; set; }
        public DbSet<Theme> Theme { get; set; }
        public DbSet<Setting> Setting { get; set; }
        public MatoProductivityDbContext(DbContextOptions<MatoProductivityDbContext> options) 
            : base(options)
        {

        }
    }

MatoProductivity.EntityFrameworkCore是应用程序数据库的维护和管理项目，依赖于Abp.EntityFrameworkCore。
在MatoProductivity.EntityFrameworkCore项目中csproj文件中，引用下列包

<PackageReference Include="Abp.EntityFrameworkCore" Version="7.4.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Design" Version="1.1.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="7.0.0">

在该项目MatoProductivityEntityFrameworkCoreModule.cs 中，将注册上下文对象，并在程序初始化运行迁移，此时将在设备上生成
mato.db
文件

public override void PostInitialize()
{
    Helper.WithDbContextHelper.WithDbContext<MatoProductivityDbContext>(IocManager, RunMigrate);
    if (!SkipDbSeed)
    {
        SeedHelper.SeedHostDb(IocManager);
    }
}

public static void RunMigrate(MatoProductivityDbContext dbContext)
{
    dbContext.Database.Migrate();
}

创建映射

从场景到笔记，或者说从模板到实例，我们需要映射，例如从笔记片段菜单中选择一个片段添加，那么需要从笔记片段仓库实体（NoteSegmentStore）映射到笔记片段实体（NoteSegment）或者，在编辑场景中，映射到笔记片段模板实体（NoteSegmentTemplate）。

[AutoMapTo(typeof(NoteSegment), typeof(NoteSegmentTemplate))]

public class NoteSegmentStore : Entity<long>
{
    ...
}

使用时：

var note = ObjectMapper.Map<NoteSegment>(noteSegmentStore);

ABP框架默认使用
AutoMapper
进行映射，所以需要配置映射关系。

Configuration.Modules.AbpAutoMapper().Configurators.Add(config =>
{
    IgnoreAbpProperties(config.CreateMap<NoteTemplate, Note>()
    .ForMember(
        c => c.NoteSegments,
        options => options.MapFrom(input => input.NoteSegmentTemplates))
      .ForMember(
        c => c.Id,
        options => options.Ignore()));


    IgnoreAbpProperties(config.CreateMap<Note, NoteTemplate>()
       .ForMember(
           c => c.NoteSegmentTemplates,
           options => options.MapFrom(input => input.NoteSegments))
      .ForMember(
        c => c.Id,
        options => options.Ignore()));


    IgnoreAbpProperties(config.CreateMap<NoteSegmentTemplate, NoteSegment>()
    .ForMember(
        c => c.Note,
        options => options.MapFrom(input => input.NoteTemplate))
    .ForMember(
        c => c.NoteSegmentPayloads,
        options => options.MapFrom(input => input.NoteSegmentTemplatePayloads))
     .ForMember(
        c => c.NoteId,
        options => options.Ignore())
      .ForMember(
        c => c.Id,
        options => options.Ignore()));

    IgnoreAbpProperties(config.CreateMap<NoteSegmentStore, NoteSegment>()
     .ForMember(
       c => c.Id,
       options => options.Ignore()));

    IgnoreAbpProperties(config.CreateMap<NoteSegment, NoteSegmentTemplate>()
       .ForMember(
        c => c.NoteTemplate,
        options => options.MapFrom(input => input.Note))
       .ForMember(
        c => c.NoteTemplateId,
        options => options.Ignore())
       .ForMember(
           c => c.NoteSegmentTemplatePayloads,
           options => options.MapFrom(input => input.NoteSegmentPayloads))
      .ForMember(
        c => c.Id,
        options => options.Ignore()));

    IgnoreAbpProperties(config.CreateMap<NoteSegmentTemplatePayload, NoteSegmentPayload>()
       .ForMember(
           c => c.NoteSegment,
           options => options.MapFrom(input => input.NoteSegmentTemplate))
       .ForMember(
        c => c.NoteSegmentId,
        options => options.Ignore())

      .ForMember(
        c => c.Id,
        options => options.Ignore()));

    IgnoreAbpProperties(
    config.CreateMap<NoteSegmentPayload, NoteSegmentTemplatePayload>()
       .ForMember(
           c => c.NoteSegmentTemplate,
           options => options.MapFrom(input => input.NoteSegment))
       .ForMember(
        c => c.NoteSegmentTemplateId,
        options => options.Ignore()));



});

迁移和种子数据

MatoProductivity.EntityFrameworkCore.Seed.SeedHelper
可在程序启动时，访问数据库，并初始化种子数据。

public override void PostInitialize()
{
    Helper.WithDbContextHelper.WithDbContext<MatoProductivityDbContext>(IocManager, RunMigrate);
    if (!SkipDbSeed)
    {
        SeedHelper.SeedHostDb(IocManager);
    }
}

它通过
SkipDbSeed
来决定是否跳过执行种子数据初始化。我们需要在安装完成App后第一次运行才执行种子数据初始化。

MAUI中提供了
VersionTracking.Default.IsFirstLaunchEver
方式获取是否是第一次在此设备上启动应用，请查看
官方文档

public override async void Initialize()
{
    IocManager.RegisterAssemblyByConvention(typeof(MatoProductivityModule).GetAssembly());
    if (VersionTracking.Default.IsFirstLaunchEver)
    {
        MatoProductivityEntityFrameworkCoreModule.SkipDbSeed = false;
    }
    else
    {
        MatoProductivityEntityFrameworkCoreModule.SkipDbSeed = true;

    }
}

在InitialDbBuilder中我们定义了大多数的业务初始数据，具体的实现方式请查阅源码。

internal void Create()
{

    CreateSetting("Theme", "Light");
    CreateSetting("DetailPageMode", "PreviewPage");


    CreateNoteSegmentStore("时间戳", "时间/提醒", "DateTimeSegment", "记录一个瞬时时间", FaIcons.IconClockO, "#D8292B");
    CreateNoteSegmentStore("计时器", "时间/提醒", "TimerSegment", "创建一个计时器，当它结束时会通知您", FaIcons.IconBell, "#D8292B");
    CreateNoteSegmentStore("笔记", "文本", "TextSegment", "随时用文本记录您的想法", FaIcons.IconStickyNoteO, "#E1A08B");
    CreateNoteSegmentStore("Todo", "文本", "TodoSegment", "记录一个Todo项目", FaIcons.IconCheckSquareO, "#E1A08B");
    CreateNoteSegmentStore("数值", "文本", "KeyValueSegment", "记录数值，以便统计数据", FaIcons.IconLineChart, "#E1A08B");
    CreateNoteSegmentStore("手绘", "文件", "ScriptSegment", "创建一个手绘", FaIcons.IconPaintBrush, "#AD9CC2");
    CreateNoteSegmentStore("照片/视频", "文件", "MediaSegment", "拍照或摄像", FaIcons.IconCamera, "#AD9CC2");
    CreateNoteSegmentStore("文档", "文件", "DocumentSegment", "从您设备中选取一个文档", FaIcons.IconFile, "#AD9CC2");
    CreateNoteSegmentStore("录音", "文件", "VoiceSegment", "记录一段声音", FaIcons.IconMicrophone, "#AD9CC2");
    CreateNoteSegmentStore("地点", "其它", "LocationSegment", "获取当前地点，或者从地图上选取一个地点", FaIcons.IconMapMarker, "#6D987C");
    CreateNoteSegmentStore("天气", "其它", "WeatherSegment", "获取当前天气信息", FaIcons.IconCloud, "#6D987C");
    CreateNoteSegmentStore("联系人", "其它", "ContactSegment", "从您设备的通讯录中选择一个联系人", FaIcons.IconUser, "#6D987C");
}

项目地址

GitHub:MatoProductivity