规避错误数据库模式的方法?
我们的团队被要求为植根于 Access 的现有 SQL Server 后端编写一个 Web 界面。
要求/限制之一是我们必须限制对 SQL 后端的更改。我们可以创建视图和存储过程,但我们被要求保留表/列不变。
SQL 后端不太理想。由于缺乏外键,大多数关系是隐式的。有些表缺少主键。表名和列名不一致,并且包含空格、斜杠和井号等字符。
除了找一份新工作或要求他们重新考虑这一要求之外,任何人都可以提供任何好的模式来解决这一缺陷吗?
注意:我们将使用 SQL Server 2005 和 ASP.NET 以及 .NET Framework 3.5。
Our team has been asked to write a Web interface to an existing SQL Server backend that has its roots in Access.
One of the requirements/constraints is that we must limit changes to the SQL backend. We can create views and stored procedures but we have been asked to leave the tables/columns as-is.
The SQL backend is less than ideal. Most of the relationships are implicit due to a lack of foreign keys. Some tables lack primary keys. Table and column names are inconsistent and include characters like spaces, slashes, and pound signs.
Other than getting a new job or asking them to reconsider this requirement, can anyone provide any good patterns for addressing this deficiency?
NOTE: We will be using SQL Server 2005 and ASP.NET with the .NET Framework 3.5.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(11)
视图和同义词可以帮助您到目前为止,但修复底层结构显然需要更多的工作。
我当然会再次尝试说服利益相关者,如果不解决根本问题,他们就会积累技术债务,这将导致代码前进的速度变慢,最终达到债务严重严重的地步。虽然你可以解决这个问题,但债务仍然存在。
即使使用数据访问层,如果您尝试扩展,数据库上的底层问题(例如您提到的键/索引)也会导致问题。
Views and Synonyms can get you so far, but fixing the underlying structure is clearly going to require more work.
I would certainly try again to convince the stakeholder's that by not fixing the underlying problems they are accruing technical debt and this will create a slower velocity of code moving forward, eventually to a point where the debt can be crippling. Whilst you can work around it, the debt will be there.
Even with a data access layer, the underlying problems on the database such as the keys / indexes you mention will cause problems if you attempt to scale.
简单:确保您拥有强大的数据访问和业务逻辑层。您必须避免从 ASPX 代码隐藏中直接对数据库进行编程的诱惑!
即使有了强大的数据库模式,我现在也养成了永远不在代码隐藏中使用 SQL 的习惯——这种做法是在经历了惨痛的教训之后才发展起来的,因为它有其缺点。
以下是一些有助于完成此过程的提示:
首先,研究 ObjectDataSource 类。它将允许您构建一个健壮的 BLL,该 BLL 仍然可以在不使用直接 SQL 的情况下提供 GridView 等控件。它们看起来像这样:
如果构建 BLL 类的实例需要传递参数,则需要 OnObjectCreating 链接。在您的代码隐藏中,按如下方式实现:
接下来,实现 BLL 还需要一些其他的东西,我将为您省去 Google 搜索的麻烦。这是与上面的调用相匹配的实现。
其次,可以轻松地将 DAL/BLL dll 添加到项目中作为附加项目,然后添加对主 Web 项目的引用。这样做不仅为您的 DAL 和 BLL 提供了自己的身份,而且使单元测试变得轻而易举。
第三,我几乎不愿意承认这一点,但这可能是微软实体框架派上用场的地方。我通常不喜欢 Linq to Entities,但它确实允许数据库中缺少的数据关系的代码端规范。
最后,我可以明白为什么更改您的数据库结构(例如移动字段)会成为问题,但添加新的约束(尤其是索引)不应该成为问题。他们是否担心外键最终会导致其他软件出现错误?如果是这样……这不是一件好事吗?你必须经历一些痛苦才能知道病在哪里,不是吗?
至少,出于性能原因,您应该推动根据需要添加索引的能力。另外,我同意其他人的观点,即视图可以在使结构更加合理方面大有帮助。 但是从长远来看这确实还不够。所以...继续构建视图(也是存储过程),但您仍然应该避免直接向数据库编码。否则,您仍然将实现锚定到数据库模式,并且将来转义它会比将数据库交互隔离到 DAL 更困难。
Easy: make sure that you have robust Data Access and Business Logic layers. You must avoid the temptation to program directly to the database from your ASPX codebehinds!
Even with a robust database schema, I now make it a practice never to work with SQL in the codebehinds - a practice that developed only after learning the hard way that it has its drawbacks.
Here are a few tips to help the process along:
First, investigate the ObjectDataSource class. It will allow you to build a robust BLL that can still feed controls like the GridView without using direct SQL. They look like this:
If constructing an instance of your BLL class requires that you pass arguments, you'll need the OnObjectCreating link. In your codebehind, implement this as so:
Next, implementing the BLL requires a few more things that I'll save you the trouble of Googling. Here's an implementation that matches the calls above.
Second, it is easy to add DAL/BLL dlls to your project as additional projects and then add a reference to the main web project. Doing this not only gives your DAL and BLL their own identities but it makes Unit testing a snap.
Third, I almost hate to admit it but this might be one place where Microsoft's Entity Framework comes in handy. I generally dislike the Linq to Entities but it does permit the kind of code-side specification of data relationships you are lacking in your database.
Finally, I can see why changes to your db structure (e.g. moving fields around) would be a problem but adding new constraints (especially indices) shouldn't be. Are they afraid that a foreign key will end up causing errors in other software? If so...isn't this sort of a good thing; you have to have some pain to know where the disease lies, no?
At the very least, you should be pushing for the ability to add indexes as needed for performance reasons. Also, I agree with others that Views can go a long way toward making the structure more sensible. However, this really isn't enough in the long run. So...go ahead and build Views (Stored Procedures too) but you should still avoid coding directly to the database. Otherwise, you are still anchoring your implementation to the database schema and escaping it in the future will be harder than if you isolate db interactions to a DAL.
我以前来过这里。管理层认为按原样使用旧模式并继续前进会更快。我试图让他们相信,新的模式将使这个以及所有未来的开发变得更快、更稳健。
如果您仍然被击落并且无法进行完整的重新设计,请采取折衷的方法,您可以重命名列和表,添加 PK、FK 和索引。这不会花费太多时间,并且会有很大帮助。
如果做不到这一点,你就必须把它磨掉。我将所有内容封装在存储过程中,您可以在其中添加各种检查以强制数据完整性。我仍然会尽可能地隐藏所有 PK、FK、索引和列重命名。
I've been here before. Management thinks it will be faster to use the old schema as it is and move forward. I'd try to convince them that a new schema would cause this and all future development to be faster and more robust.
If you are still shot down and can not do a complete redesign, approach them with a compromise, where you can rename columns and tables, add PKs, FKs, and indexes. This shouldn't take much time and will help a lot.
short of that you'll have to grind it out. I'd encapsulate everything in stored procedures where you can add all sorts of checks to enforce data integrity. I'd still sneak in all the PK, FK, indexes and column renames as possible.
由于您声明需要“限制”对数据库模式的更改,因此您似乎可以向那些没有主键字段的表添加主键字段。假设现有应用程序不执行任何“SELECT *...”语句,这不应破坏现有代码。完成此操作后,以统一的方式创建表的视图,并在视图上为 INSERT、UPDATE 和 DELETE 语句设置触发器。它将对性能造成轻微影响,但它将允许使用统一的数据库接口。然后添加的任何新表都应符合新标准。
处理它的方式非常老套,但这是我过去采取的一种方法,修改现有模式的访问权限有限。
Since you stated that you need to "limit" the changes to the database schema, it seems that you can possibly add a Primary Key field to those tables that do not have them. Assuming existing applications aren't performing any "SELECT *..." statements, this shouldn't break existing code. After that's been done, create views of the tables that are in a uniform approach, and set up triggers on the view for INSERT, UPDATE, and DELETE statements. It will have a minor performance hit, but it will allow for a uniform interface to the database. And then any new tables that are added should conform to the new standard.
Very hacky way of handling it, but it's an approach I've taken in the past with a limited access to modify the existing schema.
如果遇到这个问题,我会做一个“结束”。告诉你的老板,处理这个问题的最佳方法是创建一个“报告”数据库,它本质上是原始数据库的动态副本。您将创建脚本或单独的数据泵应用程序,以使用对原始数据库的更改来更新报告数据库,并将对数据库所做的更改传播回原始数据库,然后编写 Web 界面以仅与“报告”数据库进行通信。
一旦完成此设置,您就可以自由地合理化数据库副本,并通过添加主键、索引、规范化等方式将其恢复到理智状态。即使在短期内,这也是一个比尝试编写 Web 界面来与设计不良的数据库进行通信更好的选择,从长远来看,您的数据库版本可能最终会取代原始版本。
I would do an "end-around" if faced with this problem. Tell your bosses that the best way to handle this is to create a "reporting" database, which is essentially a dynamic copy of the original. You would create scripts or a separate data-pump application to update your reporting database with changes to the original, and propagate changes made to your database back to the original, and then write your web interface to communicate only with your "reporting" database.
Once you have this setup in place, you'd be free to rationalize your copy of the database and bring it back to the realm of sanity by adding primary keys, indexes, normalizing it etc. Even in the short-term, this is a better alternative than trying to write your web interface to communicate with a poorly-designed database, and in the long-term your version of the database would probably end up replacing the original.
似乎您被剥夺了“解决这个缺陷”的唯一可能性。
我建议将完整性检查的逻辑添加到您即将到来的存储过程中。外键、唯一键,这一切都可以手动强制执行。不是一项好工作,但可行。
Seems that you have been denied the only possibility to "address this deficiency".
I suggest adding the logic of integrity check into your coming stored procedures. Foreign keys, unique keys, it all can be enforced manually. Not a nice work but doable.
您可以做的一件事是创建大量视图,以便表名和列名更加合理,摆脱麻烦的字符。我倾向于不喜欢在表/列名称中包含空格,这样我就不必到处使用方括号。
不幸的是,由于缺少外键,您会遇到问题,但您可能需要在列上创建索引(例如名称,它必须是唯一的)来帮助解决。
至少从好的方面来看,您实际上不应该有很多连接要做,因此您的 SQL 应该很简单。
One thing you could do is create lots of views, so that the table and column names can be more reasonable, getting rid of the troublesome characters. I tend to prefer not having spaces in my table/column names so that I don't have to use square brackets everywhere.
Unfortunately you will have problems due to the lack of foreign keys, but you may want to create indexes on the column, such as a name, that must be unique, to help out.
At least on the bright side you shouldn't really have many joins to do, so your SQL should be simple.
如果您使用 LinqToSql,则可以在 DBML 中手动创建关系。此外,您可以将对象重命名为您喜欢的标准(而不是斜杠等)。
If you use LinqToSql, you can manually create relationships in the DBML. Additionally, you can rename objects to your preferred standard (instead of the slashes, etc).
我将从定义主键的每个表的索引视图开始。也许在您想要在表上建立索引的地方有更多索引视图。这将开始解决一些性能问题和所有命名问题。
我会将这些视图视为原始表。这是您的存储过程或数据访问层应该操作的内容,而不是基表。
在执行完整性规则方面,我更喜欢将其放在代码中的数据访问层中,而在数据库层仅进行最少的完整性检查。如果您更喜欢数据库中的完整性检查,那么您应该广泛使用存储过程来强制执行它们。
并且不要低估找到新工作的可能性;-)
I would start with indexed views on each of the tables defining a primary key. And perhaps more indexed views in places where you want indexes on the tables. That will start to address some of your performance issues and all of your naming issues.
I would treat these views as if they were raw tables. This is what your stored procedures or data access layer should be manipulating, not the base tables.
In terms of enforcing integrity rules, I prefer to put that in a data access layer in the code with only minimal integrity checks at the database layer. If you prefer the integrity checks in the database, then you should use sprocs extensively to enforce them.
And don't underestimate the possibility of finding a new job ;-)
这实际上取决于您拥有多少自动化测试覆盖率。如果你的东西很少或没有,你就无法在不破坏东西的情况下进行任何更改(如果它一开始就没有破坏)。
我建议您在进行其他更改时明智地重构内容,但不要对数据库进行大规模重构,这会引入错误和/或创建过多的测试工作。
有些问题(例如缺少主键)确实很难解决,应该尽早纠正。不一致的表名基本上可以永远容忍。
It really depends how much automated test coverage you have. If you have little or none, you can't make any changes without breaking stuff (If it wasn't already broken to start with).
I would recommend that you sensibly refactor things as you make other changes, but don't do a large-scale refactoring on the DB, it will introduce bugs and/or create too much testing work.
Some things like missing primary keys are really difficult to work-around, and should be rectified sooner rather than later. Inconsistent table names can basically be tolerated forever.
我对设计不佳的数据库进行了大量的数据集成,我采用的方法是使用与原始源数据库并排的交叉引用数据库;这允许我将视图、存储过程和维护代码存储在关联的数据库中,而无需触及原始架构。我对原始模式的一个例外是,如果需要,我将实现索引;但是,如果可能的话,我会尽量避免这种情况。
此方法的优点在于您可以将新代码与旧代码隔离,并且可以创建解决不良实体定义、命名错误等问题的视图,而无需破坏其他代码。
斯图
I do a lot of data integration with poorly-designed databases, and the method that I've embraced is to use a cross-referenced database that sits alongside the original source db; this allows me to store views, stored procedures, and maintenance code in an associated database without touching the original schema. The one exception I make to touching the original schema is that I will implement indexes if needed; however, I try to avoid that if at all possible.
The beauty of this method is you can isolate new code from old code, and you can create views that address poor entity definitions, naming errors, etc without breaking other code.
Stu