[MAUI 项目实战] 笔记App(二):数据库设计

Sqlite配置

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

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

1
2
3
4
"ConnectionStrings": {
"Default": "Data Source=file:{0};"
},
...

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

在MatoProductivityCoreModule.cs中,重写PreInitialize并设置Configuration.DefaultNameOrConnectionString:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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定义如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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文件中,引用下列包

1
2
3
4
5
<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文件

1
2
3
4
5
6
7
8
9
10
11
12
13
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)。

1
2
3
4
5
6
[AutoMapTo(typeof(NoteSegment), typeof(NoteSegmentTemplate))]

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

使用时:

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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可在程序启动时,访问数据库,并初始化种子数据。

1
2
3
4
5
6
7
8
public override void PostInitialize()
{
Helper.WithDbContextHelper.WithDbContext<MatoProductivityDbContext>(IocManager, RunMigrate);
if (!SkipDbSeed)
{
SeedHelper.SeedHostDb(IocManager);
}
}

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
public override async void Initialize()
{
IocManager.RegisterAssemblyByConvention(typeof(MatoProductivityModule).GetAssembly());
if (VersionTracking.Default.IsFirstLaunchEver)
{
MatoProductivityEntityFrameworkCoreModule.SkipDbSeed = false;
}
else
{
MatoProductivityEntityFrameworkCoreModule.SkipDbSeed = true;

}
}

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

[MAUI 项目实战] 笔记App(二):数据库设计

https://blog.matoapp.net/posts/3b234df5/

作者

林晓lx

发布于

2024-07-19

更新于

2024-09-11

许可协议

评论