2013年（2）
分类： 数据库开发技术
2013-08-31 01:13:33
数据结构的相对稳定，是一个应用的基础保障。
一个已投入正常运行的应用程序，数据库的字段只能添加，不应删除。如此规定，反而能达到限制垃圾字段泛滥的目的。
数据库中每一个字段都有它的用途（除非明确标明废弃），所存放的数据都应追求准确有效。没有“可以忽略不管”的数据。
一套基于数据库的应用程序（依靠数据库）的成功与失败取决于应用程序如何使用数据库。
一个开发团队，需要其核心成员是对数据库精通的程序员。他们负责确保数据库逻辑是合理的，并且系统是优化的。
这些看上去好像是显而易见，但以我的经验，发现很多人使用数据库时，把数据库看做是一个“黑盒子”，即不必去理解的东西。可能是有SQL生成器，这使得他们不必费力学习SQL。还有可能他们把数据库视为展开文件，只能用来完成“按码读取数据”操作。不管这些～～（摘自互联网）
对于一个应用程序，数据库设计应包含以下描述：1表、2字段、3主外键、4索引、5视图、6存储过程（包括函数）、7初始数据、8数据初始化的校验规则、9核心业务处理原理。
而7和8是最容易被程序员忽视的，也是项目部署的难点所在。
我们追求的是在项目部署环节，花最少的时间。往远说是促进软件产品化进程，往近说是将有限的项目时间用在“与用户沟通、需求满足、培训”上，而不是花在“部署”上。
数据库设计与维护常态
A、设计阶段，设计者一般习惯用PowerDesigner建模。设计好后，用之生成文档和能在数据库中生成创建表的SQL语句，即可进入开发阶段。
B、随之经历“开发、部署、投产”，这三个令人欣喜的阶段。
C、到了运行维护阶段，问题随之而来。随着时间推移，新的需求不断产生，其中最典型的表现就是“增加字段”，届时，技术人员的表现可分为以下阶段：
懵懂期（通常工作经验0-3年）：技术人员手工在数据库中添加字段，然后再修改程序。
缺点：手工操作具有一次性执行特征，不能简单复制到其它项目，当碰到第二个项目时，慌忙中只能将第一个项目的数据库备份出来，恢复到第二个项目上，然后再删除第一个项目的业务数据，通常删除不干净。更糟糕的是：第二个项目的数据库是DB2，而第一个项目是ORACLE，不能简单恢复。 费时费力。
初级阶段（通常工作经验2-4年） ：吃一亏长一智，技术人员用SQL命令来增加字段，且将其全部保存下来，以备以后查阅和使用。
缺点：等真碰到又下一个项目时，才发现保存的SQL文件（或命令）数量极多，仍旧难以维护，且执行起来错误频出，尤其是在不同数据库上。有时甚至这些SQL命令哪些执行过了，哪些没执行，哪些与哪些有重复、有冲突，有些字段有记录遗漏的，结果到底都建全了没有，心里仍旧没底，跑起应用来，错误频出。结果与“懵懂阶段”好不了多少。
靠谱阶段（通常工作经验4-6年）：技术人员已经明白，要想让应用程序在多个项目上多种数据库上成功执行，不仅“将上个项目的数据库恢复到新项目上再删除”方法不靠谱，而且“制作模版数据库用于在新项目上进行恢复”同样是不靠谱的。必须将数据库表字段的“建表功能”封装好，并可随时执行，甚至可重复执行，也不会破坏数据。碰到新项目时，只需要执行该“建表功能”，几分钟即可正常运行项目。碰到新增字段时，也懂得必须在“建表功能”中添加代码，然后在数据库上运行该“建表功能”（以保证下次再需要执行时不会报错），才开始制作代码。项目复制的问题解决。
缺点：时间一长，“建立功能”中的代码较杂乱，从中看不出一个表的完整结构（没考虑数据字典文档）；索引名和主键名由人为定制没有规律，不好维护（删除和重建）；要想编制“建表功能”的源代码，必须懂得几种不同数据库的区别，要想做到这些并不容易。
晋升阶段（通常工作经验6-8年） ：制定一种数据库描述规则，设计一个工具来解析和建立数据库。这个工具要符合以下要求:
●要求语法规则通俗易懂，上手快速；
●规则适用于现有大型数据库，能够方便扩充规则，以适应新出现的数据库；
●能够建立包括表、字段、索引、主外键、视图、序列、存储过程、基础数据等在内的数据库对象；
●能够自动修补数据库中缺少的字段，而不是“重建（删除后重建）”，在投产环境中绝不能“重建”；
●能够重复执行而不会对数据库造成损伤；
●能够自动输出《数据字典》；
●能够方便查阅表名字段名；
●能够方便对数据进行维护（增删改查）；
●能够自定义数据库方言，以应对特殊情况。
●使用 “中文注释”并能够自动生成部分代码等，为程序开发提供便利；
●能够编制“一键重建索引”等功能，以提高应用程序运行效率；
终极目标：建表维护工具与开发工具完美结合，建表工具能为开发工具直接辅助生成数据模型源代码、页面源代码等。
能够根据CT来编制工具验证数据库是否完全符合CT描述规则。如无外键的情况下，硬枚举、软枚举数据表与主表的匹配度。
u 坚决要避免的：懵懂阶段的做法：在项目中当有需求时，程序员先到数据库里增加好字段，即开始编制应用程序，待有空了再维护《数据字典》文档。
在项目中修改了视图的创建语句，但未保留下来，或保存得散乱而不好复用。
针对不同种数据库，分别进行描述（存储过程除外）。
u 还要避免的：
表名、字段名长度超过18个字符。
出现怪异字段类型。如在Bit、Boolean，导致无法移植到别的数据库。
使用某种数据库的关键字做为表名、字段名等。如user，在oralce中是关键字；function是sql2000关键字；func是sybase关键字。
以下词语不能单独做为表名、字段名：window、no、function、func、user、name、in、at、as、dual、key等。还有多少个单词，可以从各数据库厂商的白皮书中收集。使用DT工具的CRI(多数据库兼容规则检验)功能可以全面检查。
主键、索引、外键等的命名无规律、数量无规则。
数据库设计，未包含标准初始化的数据字典。
推荐工具名称：CT（CreateTable）。自主研发的创建数据库工具。基于在十几年的PB项目中，在源代码中的数据库层代码，进行数据库迁移的经验总结和精华提炼，总结出的一套应用工具。它应该从数据库细化设计开始，贯穿软件项目从设计、开发、测试、部署、运维、售后多个环节。有以下优点：
书写简单、查阅直观。格式为文本文件，
容易打印输出或被WORD文档引用，用户（尤其是较低应用水平的用户）更容易接受；
跨数据库并完美匹配。只需维护一个描述，运行在不同数据库上；数据库对象完全使用CT进行维护，保证该描述与数据库完全匹配，不会出现数据库结构与描述（数据字典）不一致的情况；
不但支持建立表、主键、外键和索引，还支持建立序列、触发器等数据库对象，还支持初始化数据。自动创建的主外键名称规则明确，使更细致的数据库优化维护变为可能；
支持对表添加字段、扩充字符串类型长度、修改视图、修改存储过程，添加修改方法不会破坏描述的完整性；
CT描述与源代码放在一起，可制作“一键创建和升级数据库对象”，使软件的setup变得简单，使施工变得简单易行，推进软件产品化进程；在软件成熟后可以保证各项目源代码的独立性，和更具有现实意义的模块相互移植；
可重复执行，不会因误操作对数据库造成损伤。
1、表名和字段名10位长足矣，不要超过15位，超过18位是不正确的。
解释：a)、informix最大支持18位长度；oracle最大支持28位长度；db2里超过12位长度会变成短表名，从db中导出的视图语句难看得很。
b)、CT默认外键规则为FK_[本表名]_[字段名]。如果表名和字段名都超过15位，外键名就会超过30位长。过长导致各数据库不能很好支持。
2、单表字段的总长度避免超过7000。
解释：超过会导致某些数据库不兼容。比如在一个表中增加多个备用字段，都是varchar(255)类型，且含义不明确，让不同模块的程序员如何使用！正确的做法是当需要建立字段时，使用CT现加即可，含义明确，定义清晰。
3、一套软件系统中，相同含义的字段名应该完全相同。
解释：如果遇到过以下这些事，还是很难过的：
a)、一套系统中，同一个字段有多种叫法，仅是细微差别
FUNDBUSINESS_ID (资金主单ID)(出现20次，如T_FUNDBUSINESS)
FUND_BUSINESSID (资金主单ID)(出现2次，如T_YEAR_INT_CUST)
FUNDBUSINESSID (资金主单ID)(出现4次，如T_PERSONAL_UNITE)
b)、一个字段名中有两个下划线“TERM__BALANCE (定期余额)”，明显错误。软件开发后期了才改字段，导致相关模块都要调整。
答：软件的数据库设计一般分以下阶段：
A数据库主体结构设计（表设计） ->
B细化设计（字段明确） ->
C开发和施工过程中增加表和字段 ->
D售后维护时少量增加表和字段
建议：从B阶段开始启用，直到D。
答：无需拿记事本从零开始手工编制CT。正确的方法是采用“RCT逆向工程”可以随时将一个数据库变成CT格式，包括表结构、索引、主键、外键、视图、初始数据。
如果是ORACLE数据库，逆向时可以直接逆向出中文说明（含表说明和字段说明）。
无论应用程序处于设计开发阶段、还是交付用户已投产数月，只要想在数据库中增加字段，只需按如下方式修改CT，再用工具一键生成到数据库即可：
比如希望在个人信息表中增加“配偶姓名”和“配偶身份证”，将上面提到的“abc.txt”文件打开，黑色部分是已有的内容，需要书写的是以下蓝色部分：
//创建数据表A_XIA_GRXX(个人信息表)
CREATETABLE A_XIA_GRXX 个人信息表 DWDM,GRDM
DWDM 单位代码 VARCHAR(20) N A_XIA_DWXX.DWDM
GRDM 个人代码 NUMERIC(18,0) N
XM 姓名 VARCHAR(100) N
XB 性别 CHAR(4) Y
POXM 配偶姓名 VARCHAR(100) Y
POSFZ 配偶身份证 VARCHAR(18) Y
END
在开发、运维、售后时，需要增加字段，技术人员必须按如下步骤执行：
a、在CT中增加字段描述
b、运行CT工具，在数据库中执行
c、修改应用程序代码，以使用新字段
通常程序员的错误习惯（也是坚决要避免的）：
a、在数据库中手工添加新字段
b、修改应用程序代码，以使用新字段
c、补写建表SQL文件（或是直接添加在“create table xxx”里；或是另起一行写在“alter table add column xxx”里）
d、补写PDM
若不使用CT工具，在开发、运维、售后时，需要增加字段，技术人员必须按如下步骤执行：
前提：应用程序是跑在多种大型数据库上的，为了多数据库兼容，技术人员会编制多个目录来分别存放多种数据库的创建sql，至少三种：oracle、db2、ms sqlserver。
方案一：
1、在PDM中添加新字段；
2、在创建SQL中另起一行，添加“alter table add column xxx”，注意要分别编制3个目录中的三个SQL文件；
3、运行某工具（如pl_sql），在数据库中执行；
4、修改应用程序代码，以使用新字段
缺点：维护3个不同数据库的SQL文件，对于一个做具体项目（一个项目几乎都只使用一种数据库）的技术人员来说，难度过大。可行性过低。
方案二：
1、在PDM中添加新字段；
2、使用PDM同步到数据库；
3、修改应用程序，以使用新字段。
缺点：在PDM同步数据库时，会出现DROP语句，如何不生成drop，虽然有选项。作者感觉很危险。
应避免删除或修改字段
程序员有时把“删除和修改字段”当成家常便饭，太不慎重。
删除和修改字段，是使软件“不能平滑升级、整个系统垃圾字段泛滥”的罪魁祸首。
项目经验缺乏，或未经历过“在正式生产环境中成功升级软件”的程序员，较难有此体会。
确实需要改字段怎么办？
答：将旧的字段在CT中明确标明“废弃”，添加建新的字段。字段只增不减，源代码也只增不减。如果旧字段的“是否允许为空”是Y，源代码可以不再维护旧字段；如果旧字段的“是否允许为空”是N，则源代码插个默认值得了。
1. 初始数据无法创建，就是初始字典列表（如个人状态：1正常,2封存,3销户）。而CT有，比如我设计的公积金数据库初始数据txt有950行，执行只需几十秒。
2. PD功能强大，必定安全系数降低，同步数据库时容易误操作。用户正式的数据神圣不可侵犯，不可容一点损伤。而CT工具功能单一，只有增加功能，就没有删除功能，安全得以保障。
3. 使用PD与数据库同步时，一次同步成功的几率并不高；若同步不成功，再次执行困难重重。CT工具可以重复执行。
4. 使用PD，就会出现生僻字段类型（如boolean）出现。而CT工具由我研发，可以将恒泰丰约定写在程序里，就不允许程序员使用非法字段类型。
5. 使用PD，无法觉察哪些单词做为名称会导致多数据库不兼容（如字段长度太长(超出18个字符)、使用了AS、KEY、USER、WINDOW、FUNCTION、FUNC等关键字）出现。而CT工具可以将恒泰丰约定写在程序里，就不允许程序员使用非法名称。对于多数据库兼容的应程序来说，使用了这些关键单词字，等同于灾难。而CT可以积累并控制那些单词。恒泰丰软件需要至少兼容三个数据库，是事实。
6. PD对我们制作setup毫无帮助，这意味着制作setup要费力得多，也许还要分数据库分别写insert语句。CT在setup上的贡献很直接。
7. PD未考虑数据库索引优化。而CT具备此功能，项目人员需要“一键优化索引”功能。
8. 使用PD创建主键名、索引名、外键名，是由人为编制的，极易发生错乱。而CT的大部分元素是自动按规则自动创建，无需关注。
描述说明：一个应用程序，数据库通常会出现以下五个对象：PDM、数据库字段说明文件(如word格式)、数据库备份、SQL补丁语句、源代码。
假如有java项目8个，商务人员6名，项目经理和售后服务12名，每人每年重装系统2次。那么一年安装8*(6+12)*2= 288次。会发现四样东西都不匹配的机率是50%，“数据库备份”与“sql补丁语句”不匹配的机率是40%。“数据库备份+SQL补丁语句”与“源代码”不匹配的机率是90%。PDM与三者不匹配的机率是95%。这样就造成新软件的部署效率很低，给商务做演示也很困难。正常情况，部署软件应该在10小时内完成.，而实际情况是，30个小时完不成。浪费288*20=5760小时，按8小时工作换算是720天，按24小时换算是240天。
如果您对上面数字表示怀疑，可以现在抽查某些项目的现状，这四样东西不匹配的比例是多少。测试一下将某oracle项目复制一个空数据库在一台新电脑上运行，再将某项目的db2程序的创建sql语句变成oracle安装在新的电脑上运行，排除源代码中对oracle数据库的不支持，单说处理缺少的字段，就够花去30个小时修复。单说将“创建SQL语句”成功运行出结果，就需要十几个小时。
使用PD增加字段后，同步到正式数据库。误操作很容易发生，易造成用户数据丢失（PD一不留神会产生先“drop table”再“create table”的SQL语句）。而哪些字段加过，哪些字段未加过，PD的同步令人担忧，极易造成同步不成功。
创建视图时程序员一般会使用SQL语句。但使用CT会更便捷，原因如下：
1. 使用CT时，可以重复执行，以确保所有视图都创建成功；而SQL通常不行，即使使用“CREATE OR REPLACE VIEW”语句，也只能支持个别（如ORACLE）数据库，在SQLSERVER上是不行的。
2. 在CT中创建视图时，CT的语法决定了可以将部分函数进行数据库的自动转换，如“NVL、ISNULL、SUBSTR、||”；而SQL不能。
3. CT对创建视图的规范决定了视图必须要有中文说明，包括字段的说明。而程序员通过SQL来创建时，常忽略这些。
4. 在CT中写好视图后，可以通过DT工具中“5.CRI多数据库兼容规则检验”来检查其兼容性；而SQL不能。
5. 视图写成CT后，会有固定的位置存放于源代码中，程序员会保持其与数据库中的匹配度；而程序员通常不会把SQL文件保存得很完好。
6. 视图写在CT中，可以通过DT工具自动生成JAVA源代码；而SQL不能。
7. 当碰到有个别视图不能编制得多数据库兼容时，可以在一个CT文件中按指定数据库写出多份创建语句来，CT的执行语法决定了解析和执行CT时可以将某些视图在指定数据库上执行，大大增强了创建视图的舒适性；而用SQL语句来解决此问题时，只能写在多个SQL文件中，在执行时必须手工进行挑选，增加错误率。
8. 如果数据库的视图是由CT创建的，则便于在不同种数据库中迁移，也便于逆向，逆向出来格式也好看一些；如果视图是由SQL语句创建的，因为SQL语法更灵活，不太注重编写格式，导致在不同种数据库之间迁移变得困难，逆向出来也有相当难度。
