代码之家 › 专栏 › 技术社区 › JYelton Melchior Blausand

如何通过编程验证、创建和更新SQL表结构?

sql c#

JYelton Melchior Blausand · 技术社区 · 14 年前

脚本:

我有一个应用程序(C),它需要一个SQL数据库和一个由用户设置的登录名。一旦连接,它将检查是否存在多个表,如果找不到,则创建这些表。

如果我发布依赖于新列的程序的新版本,我希望通过让程序能够向这些表中添加列来扩展这一点。

问题:

对现有SQL表的结构进行编程检查并创建或更新以匹配预期结构的最佳方法是什么?

我计划遍历所需列的列表,并在现有表不包含新列时更改该表。我禁不住想知道是否有一种方法是不同的或更好的。

标准:

以下是我的一些期望和自我强加的规则:

程序的较新版本可能不再使用某些列,但它们将保留以用于数据日志记录。换句话说,不会删除任何列。
必须保留表中的现有数据,因此不能简单地删除并重新创建表。
在所有情况下,新添加的列都将允许空数据,因此通过使用默认的空值来处理旧记录的填充。

例子:

这是一个示例表(因为可视化示例有帮助!):

id  datetime         sensor_name  sensor_status  x1    x2    x3    x4
1   20100513T151907  na019        OK             0.01  0.21  1.41  1.22
2   20100513T152907  na019        OK             0.02  0.23  1.45  1.52

然后,在新版本中,我可能想添加列 x5 . “ x -列”是所有接受空值的数据存储列。

编辑:

我更新了上面的示例表。它更像是一个日志,而不是父表。因此,传感器将重复显示在此日志表中,并记录值。单独的父表包含地理和其他后勤信息关于传感器,使表我想修改一个子表。

4 回复 | 直到 14 年前

Paul Sasik 14 年前

这是您正在考虑实现的一个非常麻烦的特性。我建议不要这样做,而是考虑使用第三方工具(如Red Gate的SQL Compare)编写更改脚本: http://www.red-gate.com/products/SQL_Compare/index.htm

如果您有疑问,请考虑下载该软件的试用版,并在两个数据库上执行结构差异脚本,其中有一些非常重要的差异。您将从结果中看到,对此类操作的考虑远不简单。

解决这类问题的另一种方法是使用EAV模型重新设计数据库: http://en.wikipedia.org/wiki/Entity-attribute-value_model (旋转以动态添加行,因此不会更改结构。它有自己的问题,但它非常灵活。)

(要使用diff工具,您必须拥有所有DB版本的副本,并创建diff脚本,这些脚本将随新版本和升级一起发布和执行。这本身就是一个巨大的混乱。EAV就是这样一种方式。它错误地因为没有传统的DB结构那么好用而受到很多指责,但我已经多次成功地使用了它。事实上,我有一个符合HIPAA的EAV DB(SQL Server 2000),它已经投入生产六年多了,其中几个EAV表包含数千万行,并且在没有大的减速的情况下仍然很强劲。当然,我们不会对数据库做大量的报告。对于报表,我们有一个将数据扁平化为关系结构的导出。)

luke 14 年前

我看到的常见解决方案是将版本信息存储在数据库的某个地方。也许有一张很小的桌子:

CREATE TABLE DB_PROPERTIES (key varchar(100), value varchar(100));

然后可以添加一行:

    key | value
version | 12

然后您可以创建一个SQL更新脚本(或一组脚本),它从版本更新数据库。 12 版本 13 .

declare v  varchar(100)
select v=value from DB_PROPERTIES where key='version'
if v ='12'
    #do upgrade from 12 to 13
elsif v='11'
    #do upgrade from 11 to 13

...and so on

根据您希望支持的升级路径,您可以添加更多案例。很明显,您也可以将这个升级逻辑转移到C或者其他适合您的设计中。但是,将数据库版本信息存储在数据库中会使您更容易了解已经存在的内容,而不是单独查询所有的数据库结构。

HLGEM 14 年前

如果您必须以依赖应用程序进行表更改的方式构建某些内容,那么您的设计是有缺陷的。您应该有一个传感器值的相关表(x1、x2等),然后您可以只添加另一个记录,而不必创建一个新列。

建议的子表结构

读数身份识别读取类型varchar(10) 读取_值int

然后表中的数据将为:

ID读取类型读取值 1×1 2 1×23个 1×3 1 2 x1 7个

blorkfish 14 年前

尝试Microsoft.sqlserver.management.smo
这些是一组C类,为SQL Server数据库对象提供API。
microsoft.sqlserver.management.smo.table有一个columns集合,允许您查询和操作这些列。
玩得高兴。