自动数据库模式生成系统？
我正在与一位客户合作，他拥有一款定制网站软件，其中有一些我以前从未见过的东西。它有一个 MySQL 数据库后端，但大多数表是由 php 代码自动生成的。这允许最终用户创建他们认为合适的表和字段。所以它是数据库中的数据库，但显然没有“最外部”数据库中可用的所有功能。有几个表基本上是自动生成的表名称和字段到用户友好的表名称和字段的映射。*这使查询感觉非常不直观：P
他们正在寻找一些附加功能，当您使用这些功能时可以立即使用这些功能直接使用数据库，例如数据类型强制、外键、唯一索引等。但由于这是数据库中的数据库，因此所有这些功能都必须添加到运行数据库的 php 代码中。我首先想到的是内部平台效应* - 但我不知道我没有找到一种方法来摆脱数据库模拟并仍然为他们提供他们需要的功能！
我想知道，我是否可以创建一个系统，让用户削弱创建“真实”表的能力，从而免费获得所有关系功能？过去，总是由开发人员/管理员制作表格，然后用户通过应用程序进行 CRUD 操作。我只是对让用户访问模式操作有一种不舒服的感觉，即使是通过应用程序也是如此。我正处于未知领域。
这种系统有名字吗？在代码内部，这称为“集合”系统。数据库中“虚拟”表和字段的名称称为“分类法”。这是否类似于 CCK 或 Drupal 中的分类模块？我正在寻找执行此类操作的软件模型，这样我就可以了解其中的陷阱和好处。基本上我正在寻找有关此类系统的更多外部信息。
- 请注意，这不是一个简单的键值映射，如有关平台内部效果参考的维基百科文章。它们的工作方式就像多个单元格的实际元组——就像简单的数据库表一样。
I'm working with a client who has a piece of custom website software that has something I haven't seen before. It has a MySQL Database backend, but most of the tables are auto-generated by the php code. This allows end-users to create tables and fields as they see fit. So it's a database within a database, but obviously without all the features available in the 'outermost' database. There are a couple tables that are basically mappings of auto-generated table names and fields to user-friendly table names and fields.* This makes queries feel very unintuitive :P
They are looking for some additional features, ones that are immediately available when you use the database directly, such as data type enforcement, foreign keys, unique indexes, etc. But since this a database within a database, all those features have to be added into the php code that runs the database. The first thing that came to my mind is Inner Platform Effect* -- but I don't see a way to get out of database emulation and still provide them with the features they need!
I'm wondering, could I create a system that gives users nerfed ability to create 'real' tables, thus gaining all the relational features for free? In the past, it's always been the developer/admin who made the tables, and then the users did CRUD operations through the application. I just have an uncomfortable feeling about giving users access to schema operations, even when it is through the application. I'm in uncharted territory.
Is there a name for this kind of system? Internally, in the code, this is called a 'collection' system. The name of 'virtual' tables and fields within the database is called a 'taxonomy'. Is this similiar to CCK or the taxonomy modules in Drupal? I'm looking for models of software that do this kind of this, so I can see what the pitfalls and benefits are. Basically I'm looking for more outside information about this kind of system.
- Note this is not a simple key-value mapping, as the wikipedia article on inner-platform effect references. These work like actual tuples of multiple cells -- like simple database tables.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我已经做到了，你可以让它变得非常简单，也可以完全疯狂。但当你把它交给客户时，你确实会遇到问题，我们是否会要求他们找出主键、唯一约束和外键？
因此，假设您想继续这样做，您需要某种类型的数据字典，也称为元数据存储库。您已经有了一个开始，但是您需要添加将列收集到表中的想法，然后指定主键和外键。
之后，生成 DDL 就相当简单了。循环表、循环列、构建 CREATE TABLE 命令。唯一的问题是您需要对表进行排序，以便在子项之前创建父项。这并不难，实现 http://en.wikipedia.org/wiki/Topological_ordering
在第二级，您首先必须检查现有数据库，然后有时只发出 ALTER TABLE ADD COLUMN... 命令。所以事情开始变得复杂。
然后，当您考虑允许默认值、指定索引等时，事情会变得更加复杂。任务是有限的，但可能比看起来要大得多。
您可能希望考虑一下您真正想要支持多少内容，然后对编码进行价值判断。
我的 triangulum 项目是这样做的：http://code.google.com/p/triangulum-db/ 但它仅适用于 Alpha 2，我还不建议在生产环境中使用它。
你也可以看看Doctrine，http://www.doctrine-project.org/，他们有某种基于文本的字典来构建数据库，但我不确定他们已经走了多远。
I've done this, you can make it pretty simple or go completely nuts with it. You do run into problems though when you put it into customers' hands, are we going to ask them to figure out primary keys, unique constraints and foreign keys?
So assuming you want to go ahead with that in mind, you need some type of data dictionary, aka meta-data repository. You have a start, but you need to add the ideas that columns are collected into tables, then specify primary and foreign keys.
After that, generating DDL is fairly trivial. Loop through tables, loop through columns, build a CREATE TABLE command. The only hitch is you need to sequence the tables so that parents are created before children. That is not hard, implement a http://en.wikipedia.org/wiki/Topological_ordering
At the second level, you first have to examine the existing database and then sometimes only issue ALTER TABLE ADD COLUMN... commands. So it starts to get complicated.
Then things continue to get more complicated as you consider allowing DEFAULTS, specifying indexes, and so on. The task is finite, but can be much larger than it seems.
You may wish to consider how much of this you really want to support, and then make a value judgment about coding it up.
My triangulum project does this: http://code.google.com/p/triangulum-db/ but it is only at Alpha 2 and I would not recommend using it in a Production situation just yet.
You may also look at Doctrine, http://www.doctrine-project.org/, they have some sort of text-based dictionary to build databases out of, but I'm not sure how far they've gone with it.