建议组织/构建数据库解决方案的替代方法
我们使用的是 Visual Studio 2010，但它最初是用 VS2003 构思的。
我会将最好的建议转达给我的团队。目前的设置几乎让我呕吐。它是一个 C# 解决方案，大多数项目都包含 .sql 文件。因为我们支持 Microsoft、Oracle 和 Sybase，所以自制了一个预处理器，很像 C 预处理器，只不过替换是由自制的 C# 程序执行，而不使用 yacc 和类似工具那。 #ifdefs 用于条件宏定义，是的 - 宏就是完成此操作的方式。一个宏可以扩展到另一个或两个宏，但这最终应该终止。只有宏中包含 #ifdef - 其余类似 SQL 的代码仅使用这些宏。
现在，各种配置：Debug、MNDebug、MNRelease、Release、SQL_APPLY_ALL、SQL_APPLY_MSFT、SQL_APPLY_ORACLE、SQL_APPLY_SYBASE、SQL_BUILD_OUTPUT_ALL、SQL_COMPILE 以及另外 2 个配置。
另外：任何 CPU、混合平台、Win32。
让我抓狂的是必须正确配置它，从 12 x 3 = 36 配置中选择正确的配置，并且必须根据数据库类型替换数据库名称：config、main ，或网关。我认为配置应该减少到只有 Debug、Release 和 SQL_APPLY。另外，使用 0、1 和 2 看起来很 80 年代...... 最后，我认为我的意图是为 3 种类型的供应商构建或不构建 3 种类型的数据库，应该只用一个 tic tac toe 板来配置，例如：
XOX
OOX
XXX
在这种情况下，这意味着构建 MSFT+CONFIG、所有 SYBASE 和所有 GATEWAY 。
尽管如此，使用文本文件和预处理器以及许多配置的整体工作似乎非常笨重。现在是 2010 年，肯定会有人拥有非常干净和/或有创意的工具/解决方案。唯一的优点是现有的宏集合已经过良好的测试。
您是否曾经需要编写适用于多个供应商的 SQL？你是怎么做到的？
SqlVars.txt（30 个用户中的每一个都复制一份模板并修改它以满足自己的需要）：
// This is the default parameters file and should not be changed.
// You can overwrite any of these parameters by copying the appropriate
// section to override into SqlVars.txt and providing your own information.
//Build types are 0-Config, 1-Main, 2-Gateway
BUILD_TYPE=1
REMOVE_COMMENTS=1
// Login information used when applying to a Microsoft SQL server database
SQL_APPLY_MSFT_version=SQL2005
SQL_APPLY_MSFT_database=msftdb
SQL_APPLY_MSFT_server=ABC
SQL_APPLY_MSFT_user=msftusr
SQL_APPLY_MSFT_password=msftpwd
// Login information used when applying to an Oracle database
SQL_APPLY_ORACLE_version=ORACLE10g
SQL_APPLY_ORACLE_server=oradb
SQL_APPLY_ORACLE_user=orausr
SQL_APPLY_ORACLE_password=orapwd
// Login information used when applying to a Sybase database
SQL_APPLY_SYBASE_version=SYBASE125
SQL_APPLY_SYBASE_database=sybdb
SQL_APPLY_SYBASE_server=sybdb
SQL_APPLY_SYBASE_user=sybusr
SQL_APPLY_SYBASE_password=sybpwd
... (THIS GOES ON)
We are using Visual Studio 2010, but this was first conceived with VS2003.
I will forward the best suggestions to my team. The current setup almost makes me vomit. It is a C# solution with most projects containing .sql files. Because we support Microsoft, Oracle, and Sybase, and so home-brewed a pre-processor, much like C preprocessor, except that substitutions are performed by a home-brewed C# program without using yacc and tools like that. #ifdefs are used for conditional macro definitions, and yeah - macros are the way this is done. A macro can expand to another macro or two, but this should eventually terminate. Only macros have #ifdef in them - the rest of the SQL-like code just uses these macros.
Now, the various configurations: Debug, MNDebug, MNRelease, Release, SQL_APPLY_ALL, SQL_APPLY_MSFT, SQL_APPLY_ORACLE, SQL_APPLY_SYBASE, SQL_BUILD_OUTPUT_ALL, SQL_COMPILE, as well as 2 more.
Also: Any CPU, Mixed Platforms, Win32.
What drives me nuts is having to configure it correctly as well as choosing the right one out of 12 x 3 = 36 configurations as well as having to substitute database name depending on the type of database: config, main, or gateway. I am thinking that configuration should be reduced to just Debug, Release, and SQL_APPLY. Also, using 0, 1, and 2 seems so 80s ...
Finally, I think my intention to build or not to build 3 types of databases for 3 types of vendors should be configured with just a tic tac toe board like:
XOX
OOX
XXX
In this case it would mean build MSFT+CONFIG, all SYBASE, and all GATEWAY.
Still, the overall thing which uses a text file and a pre-processor and many configurations seems incredibly clunky. It is year 2010 now and someone out there is bound to have a very clean and/or creative tool/solution. The only pro would be that the existing collection of macros has been well tested.
Have you ever had to write SQL that would work for several vendors? How did you do it?
SqlVars.txt (Every one of 30 users makes a copy of a template and modifies this to suit their needs):
// This is the default parameters file and should not be changed.
// You can overwrite any of these parameters by copying the appropriate
// section to override into SqlVars.txt and providing your own information.
//Build types are 0-Config, 1-Main, 2-Gateway
BUILD_TYPE=1
REMOVE_COMMENTS=1
// Login information used when applying to a Microsoft SQL server database
SQL_APPLY_MSFT_version=SQL2005
SQL_APPLY_MSFT_database=msftdb
SQL_APPLY_MSFT_server=ABC
SQL_APPLY_MSFT_user=msftusr
SQL_APPLY_MSFT_password=msftpwd
// Login information used when applying to an Oracle database
SQL_APPLY_ORACLE_version=ORACLE10g
SQL_APPLY_ORACLE_server=oradb
SQL_APPLY_ORACLE_user=orausr
SQL_APPLY_ORACLE_password=orapwd
// Login information used when applying to a Sybase database
SQL_APPLY_SYBASE_version=SYBASE125
SQL_APPLY_SYBASE_database=sybdb
SQL_APPLY_SYBASE_server=sybdb
SQL_APPLY_SYBASE_user=sybusr
SQL_APPLY_SYBASE_password=sybpwd
... (THIS GOES ON)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我使用了ORM。检查 NHibernate。由于您使用的是 VS2010，因此您还可以检查 MS ADO。 Net实体框架。
ORM 使您能够高度抽象数据库。仍然会有一些查询需要单独为每个 RDBMS 手工制作，但根据我的经验，这个比例不到 10%。
对于其他商业和开源 ORM，请查看 Wikipedia 文章：对象关系映射软件列表 。
编辑：再交易平台评论：
我从来没有从事过这样的项目，所以我真的无法给出任何有意义的建议。作为一般建议，ORM 可以帮助您巩固数据访问层并最大限度地减少对 RDBMS 特定代码的需求。大多数ORM也可以自动生成数据库。
我无法判断这会对性能造成什么影响。您应该使用顶级 ORM（商业和开源）进行一些测试，然后做出决定（Microsoft 可能有兴趣与您所在市场中使用实体框架的公司合作）。
另外，不要忘记，在现有产品中引入 ORM 很困难，因此这是您需要考虑的另一个问题。
I used ORM's. Check NHibernate. Since you are on VS2010, you could also check MS ADO.Net Entity Framework.
ORM enables you to abstract database in high degree. There will still be queries that should be handcrafted for each RDBMS separately, but in my experience, it is less than 10%.
For other commercial and open source ORM's check Wikipedia article: List of object-relational mapping software.
Edit: Re trading platform comment:
I never worked on such a project, so I really cannot give any meaningful advice. As a general suggestion, ORM can help you consolidate data access layer and minimize need for RDBMS specific code. Most of ORM's also can automatically generate database.
What would be the performance penalty of this is impossible for me to tell. You should make some tests with top ORMs, both commercial and open source and then decide (Microsoft would probably be interested to work with company that uses Entity Framework in your market).
Also, not to forget, introducing ORM in existing product is hard, so this is another problem you need to take into account.
从根本上讲，您可以通过在数据层和任何其他层之间添加一个抽象层来实现这一点。然后您有几个选择：
听起来您的情况的根本问题是数据层没有封装到单个库中并且完全与任何其他层屏蔽。如果这是真的，那么松散的内聚力和紧密的耦合就是你的困境的一些根源。
您应该考虑重构当前的库，以便所有数据库交互都在单个库中完成，并且通过接口完全从其上方的任何层中抽象出来。这可以通过多个发布周期逐渐完成，直到最终所有调用都通过与数据库无关的接口或抽象类，其中您有工厂方法为相关数据库加载适当的具体类。
有关详细信息：
Fundamentally, you do it by adding a layer of abstraction between the data tier and any other tier. Then you have a couple of choices:
It sounds like the fundamental problem in your situation is that the data tier is not encapsulated to a single library and entirely shielded from any other tier. If that is true, the loose cohesion and tight coupling are some of the sources of your woes.
You should consider refactoring the current libraries so that all database interaction is done within single library and is entirely abstracted away from any tier above it via interfaces. This can be done gradually through multiple release cycles until eventually all calls go through an interface or abstract class that is database agnostic where you have factory methods that load the appropriate concrete class for the database in question.
For more info: