维护与 Mnesia 的关系完整性
我最近一直在研究 Erlang，我决定使用 Mnesia 来完成我的数据库工作，因为它可以毫无问题地存储任何类型的 Erlang 数据结构、轻松扩展、与列表理解一起使用等。
来自标准 SQL在数据库中，大多数行可以而且应该由主键标识，主键通常是自动递增的整数。 默认情况下，Mnesia 将行的第一个字段视为其键。 据我所知，它也无法提供自动递增的整数键。
鉴于我有这些代表我的表的虚构记录：
-record(user, {name, salt, pass_hash, email}).
-record(entry, {title, body, slug}).
-record(user_entry, {user_name, entry_title}).
我认为使用用户名对于某些目的来说可能已经足够了，就像条目标题一样，以便识别资源，但我该如何保持完整性呢？
假设用户更改了名称，或者条目的标题在编辑后发生了更改。 如何确保我的数据仍然正确相关？ 无论怎么说，在用户名发生变化时使用用户名更新每个表听起来都是一个糟糕的主意。
在 Mnesia 中实施某种主键系统的最佳方法是什么？
另外，如果第一个字段通常是键，像“user_entry”这样的中间表会如何做？ 否则，在 Mnesia 中表示多对多关系的更好方法是什么？
I've been diving into Erlang recently, and I decided to use Mnesia to do my database work given it can store any kind of Erlang data structure without a problem, scale with ease, be used with list comprehensions, etc.
Coming from standard SQL databases, most rows can and should be identified by a primary key, usually an auto-incrementing integer. By default Mnesia considers the first field of a row to be its key. It also gives no way to have an auto-incrementing integer key as far as I know.
Given I have these fictional records representing my tables:
-record(user, {name, salt, pass_hash, email}).
-record(entry, {title, body, slug}).
-record(user_entry, {user_name, entry_title}).
I figure using the username may be good enough for some purposes, as with the entry title, in order to identify the resource, but how do I go about maintaining integrity?
Say the user changes its name, or that the entry's title changes after an edit. How do I make sure my data is still correctly related? Updating every table using the username when it changes sounds like a terrible idea no matter how it's put.
What would be the best way to implement some kind of primary key system in Mnesia?
Also, how would an intermediary table like 'user_entry' do if the first field is usually the key? Otherwise, what would a better way be to represent a many-to-many relationship in Mnesia?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我更喜欢使用 GUID 而不是自动递增整数作为人工外键。 有一个 Erlang uuid 模块，位于
GitHub，或者您可以使用
{now(), node()}，因为now/0文档说：“还可以保证对此 BIF 的后续调用返回不断增加价值。”在我看来，使用可以更改的东西作为主键，独立于数据库系统似乎是一个坏主意。
不要忘记，您不需要将 Mnesia 中的数据标准化为第一范式； 在您的示例中，我会考虑以下结构：
其中
entries和users是 id 列表。 当然，这取决于您想要的查询。编辑：固定为多对多而不是多对一。
I prefer using GUIDs instead of auto-incrementing ints as artificial foreign keys. There is an Erlang uuid module available at
GitHub, or you can use
{now(), node()}, given thatnow/0doc says: "It is also guaranteed that subsequent calls to this BIF returns continuously increasing values."Using something that can change as the primary key seems to me to be a bad idea independent of the database system.
Don't forget that you don't need to normalise data in Mnesia even to first normal form; in your example, I would consider the following structure:
where
entriesandusersare lists of ids. Of course, this depends on the queries you want.EDIT: fixed to be many-to-many instead of many-to-one.
Mnesia 支持
mnesia:dirty_update_counter(Table, Key, Increment)形式的序列（自动递增整数）。 要使用它，您需要一个具有两个属性 Key 和 Count 的表。 尽管名称如此，但 dirty_update_counter 是原子的，即使它不在事务内运行。Ulf Wiger 在他的 rdbms 包。 他的代码提供了外键约束、参数化索引、字段值约束等。 不幸的是，这段代码已经两年没有更新了，如果没有相当多的 Erlang 经验，可能很难运行。
在设计和使用 mnesia 时，您应该记住 mnesia 不是关系数据库。 它是一个事务性键/值存储，当您不进行标准化时更容易使用。
如果您的用户名是唯一的，您可以使用以下架构：
其中
posted是文章上传的 erlang:now() 时间。 如果您经常需要检索用户的所有文章的列表，user_name可能需要二级索引。 由于此数据分为两个表，因此您必须在应用程序代码中强制执行任何完整性约束（例如，不接受没有有效用户名的条目）。mnesia 中的每个字段值都可以是任何 erlang 术语，因此如果您在任何一个特定字段上找不到唯一键，您通常可以组合一些字段来为您提供一个始终唯一的值 - 也许是 {Username,发布日期、发布时间}。 Mnesia 允许您通过
mnesia:select(Table, MatchSpec)搜索部分键。 MatchSpec 很难手动编写，因此请记住ets:fun2ms/1可以将伪 erlang 函数转换为 matchspec。在此示例中，fun2ms 为我们生成一个用于搜索博客条目表
-record(entry, {key, title, slug, body}).的匹配规范，其中 key 为{Username, {Year, Month, Day}, {Hour, Minute, Second}}- 作者的用户名以及文章发布的日期和时间。 下面的示例检索 2008 年 12 月期间TargetUsername的所有博客文章的标题。Mnesia does support sequences (auto-incrementing integers) in the form of
mnesia:dirty_update_counter(Table, Key, Increment). To use it you need a table with two attributes Key and Count. Despite the name, dirty_update_counter is atomic even though it doesn't run inside a transaction.Ulf Wiger did some work on providing typical RDBMS features on top of mnesia in his rdbms package. His code provides foreign key constraints, parametized indices, field value constraints and so on. Unfortunately this code has not been updated in two years and will probably be difficult to get running without quite a bit of Erlang experience.
When designing for and using mnesia you should remember that mnesia is not a relational database. It is a transactional Key/Value store and is much easier to use when you don't normalise.
If your usernames are unique, you could use the schema:
Where
postedis the erlang:now() time when the article is uploaded.user_namemight need a secondary index if you often need to retreive a list of all articles for a user. As this data is split over two tables, you will have to enforce any integrity constraints in your application code (for instance, not accepting entries without a valid user_name).Each field value in mnesia can be any erlang term, so if you're at a loss for a unique key on any one particular field, you can often combine some fields to give you a value that will always be unique - perhaps {Username, DatePosted, TimePosted}. Mnesia allows you to search partial keys via
mnesia:select(Table, MatchSpec). MatchSpecs are quite difficult to write by hand, so remember thatets:fun2ms/1can convert a psuedo erlang function into a matchspec for you.In this example, fun2ms generates us a matchspec for searching a blog entry table
-record(entry, {key, title, slug, body}).where key is{Username, {Year, Month, Day}, {Hour, Minute, Second}}- the username of the author and the date and time the article was posted. The example below retrieves the titles of all the blog posts byTargetUsernameduring December 2008.