基于MSDE账套管理程序的设计
2014-04-16巫宗宾
巫宗宾
【摘 要】 基于MSDE桌面数据库系统设计一个通用管理程序，完成对数据库实体也就是账套的新建、删除、备份、恢复、注册、反注册、压缩等功能。使非专业人士也可以专业的完成数据库维护，同时提供标准的开发接口，缩短开发周期，降低开发成本。具有良好的市场推广价值和创造经济价值。
【关键词】 MSDE 账套 备份 MIS 通用接口
【Abstract】 a general management program design of database system based on MSDE desktop， complete was the database entity account of new， delete， backup， restore， registration， the registration， compression and other functions. The non professionals can also professional complete database maintenance， while providing development interface standard， shorten the development cycle， reduce the cost of development. Has a good market popularizing value and create economic value.
【Keywords】 MSDE account backup MIS universal interface
MSDE是MS SQL Server的桌面免费版，它是一个基于 SQL Server 核心技术构建的数据引擎，没有图型管理工具，通过命令行管理程序osql来连接的。当对数据库系统的功能和性能提出新的需求时，则可以在不修改现有程序代码的情况下，完全移植到SQL Server的平台上运行。在管理信息系统的应用中，每一个程序都需要对数据库账套进行新建、删除、备份、恢复、压缩、信息注册、反注册等操作。设计一个通用的管理程序，通过简单的操作完成数据库管理并提供标准的开发接口。本设计通过利用动态SQL语句和Powerbuilder9.0作为开发工具，完成基于MSDE的数据库管理软件设计。
1 设计思路
通过查询Master数据库中的系统表sysaltfiles，检测是否包含账套信息数据库的逻辑数据库名称Kt_acct，如果存在则直接连接Kt_acct数据库，否则创建Kt_acct数据库，同时创建用户表T_user，帐套信息表T_acct和帐套文件存放信息表T_acct_info，并连接到Kt_acct数据库进入账套管理程序，对账套进行维护，同时通过访问Kt_acct数据库获取应用程序的账套数据库信息，连接到账套数据库建立新的应用。具体流程参见（图1）：
2 具体实现方法
该环节实现账套信息数据库的建立和表的创建，以及账套的新建、删除、备份、恢复、压缩等功能的实现。程序主界面如（图2）所示：
2.1 检测账套信息数据库Kt_acct
通过查询Master数据库中的sysaltfiles表，检测账套信息数据库的逻辑数据库名称t_acct_data是否存在，如果存在则连接Kt_acctk，否则创建Kt_acctk，其核心代码如下：
Int flags=0；
select count（*） into： flags from sysaltfiles where name="kt_acct_data"；
如果SQL语句执行后flags>0表示数据库已经存在，否则表示数据库不存在。
2.2 创建账套信息数据库Kt_acct
该环节通过构建动态SQL语句并执行SQL语句完成数据库Kt_acct的创建。定义字符串变量ls_sql，构建一个动态SQL语句赋予变量ls_sql，在SQL语句具中指出数据库主文件kt_acct_data.mdf和数据库日志文件kt_acct_log.ldf，通过执行变量ls_sql中的语句完成数据库创建，其核心代码如下：
string ls_sql=””；
ls_sql="create database kt_acct on（ name=kt_acct_data，filename = 'd：＼kt_acct_data.mdf'，size = 10） log on（ name =kt_acct_log，filename ='d：＼kt_acct_log.ldf'，size = 10）" ；
execute immediate ：ls_sql using sqlca；
2.3 创建用户表T_USER
该环节构建一个动态SQL语句并执行完成数据库用户信息表T_USER的创建，包含用户名称USERS字段和密码字段PASS，并向表添加一个超级用户Admin，密码为空。其核心代码如下：
string ls_createtable=””；
ls_createtable="create table t_user（users char（8） not null，pass char（16）null）"；
execute immediate：ls_createtable using sqlca；
insert into t_user （users，pass）values （"admin"，""） using sqlca ；
2.4 创建账套信息表T_acct
该环节构建一个动态SQL语句，通过动态SQL语句生成数据库表T_acct，并定义表字段kacctid为主键，其核心代码如下：endprint
ls_createtable="create table [dbo].[t_acct] （[kacctid] [int] identity （1， 1） not null ， [kacctnumber] [nvarchar] （50） collate chinese_prc_ci_as not null ，[kacctname] [nvarchar] （50） collate chinese_prc_ci_as not null ，[kdbname] [nvarchar] （100） collate chinese_prc_ci_as not null ，[kcompanyname] [nvarchar] （50） collate chinese_prc_ci_as not null ，[fdescription] [nvarchar] （50） collate chinese_prc_ci_as null，[kacctver] [char] （10） collate chinese_prc_ci_as not null ） on [primary]"；
execute immediate：ls_createtable using sqlca；
ls_createtable="alter table [dbo].[t_acct] add constraint [pk_t_acct] primary key clustered （[kacctid]） on [primary]" ；
execute immediate：ls_createtable using sqlca；
2.5 创建数据库文件存放信息表t_acct_info
该环节构建一个动态SQL语句，通过动态SQL语句生成数据库表，其中字段l_name存放数据库文件逻辑名，字段，p_name存放数据库文件完整路径及文件名，字段f_g_name存放文件属性，size和maxsize字段指定文件大小。其核心代码如下：
ls_createtable="create table [dbo].[t_acct_info]（l_name varchar（30），p_name varchar（1000），type varchar（10），f_g_name varchar（20） null，size bigint，maxsize bigint）"；
execute immediate：ls_createtable using sqlca；
2.6 创建存储过程
该环节创建两个动态SQL语句，通过动态SQL语句生成存储过程sp_ restore 和sp_dump，其中sp_ restore完成数据库恢复，sp_dump取消数据库记录日志，为压缩数据库做准备，这两个存储过程供程序调用完成相关功能。核心代码如下：
ls_createtable="create procedure sp_restore @bakfile nvarchar（1000） as insert into t_acct_info exec（'restore filelistonly from disk = n'''+@bakfile+''''）"；
execute immediate ：ls_createtable；
ls_createtable="create procedure sp_dump @datafile nvarchar（50） as dump transaction @datafile with no_log backup log @datafile with no_log dbcc shrinkfile （2） dbcc shrinkfile （1） "；
execute immediate：ls_createtable；
3 账套管理实现方法
该环节主要实现应用程序数据库的新建、删除、备份、恢复、数据压缩、信息登记、账套注册以及账套数据结转等功能，充分体现简单的操作方法和高效的管理的特点，当基于SQL SERVER2000应用的MIS开发成功以后，都会有一个存放所有数据的数据库文件，通过SQL SERVER2000的工具将该数据库文件导出生成一个SQL文件，这个文件包含数据库的所有对象、数据以及对象间关系，把文件命名为：chs.sql。通过文件chs.sql可以完成行业账套的新建和恢复等功能。
3.1 新建账套
在登录至管理界面之后选中新建功能，输入帐套号，帐套名称帐套数据库路径之后便能得到一个所需要管理的数据库，至于公司名称及帐套说明是对所需要管理的数据说明，以便用户分清所要管理数据的特征。输入信息完毕后，软件提示等待信息，当帐套创建成功后有信息提示框弹出，如（图3）所示。
其核心代码如下：
string str_acctnumber=""//恢复账套名称；
string ls_sql=""//动态sql变量；
string ls_logicdata=""//新数据库mdf逻辑名称；
string ls_logiclog=""//新数据库ldf逻辑名称；
string ls_griphicmdf=""//新数据库mdf存放路径及名称；
string ls_griphicldf=""//新数据库ldf存放路径及名称；
string ls_olddatabase=""//备份文件中数据库名称；
ls_sql="restore database "+str_acctnumber+" from "+ ls_olddatabase+" with recovery，move "+"'"+ls_logicdata+"' to '"+ls_griphicmdf+"'，move "+"'"+ls_logiclog+"' to '"+ls_griphicldf+"'"；endprint
execute immediate ： ls_sql using tr_sqlca；//恢复数据库；
3.2 删除账套
在登录至管理界面之后选中账套数据库点击删除图标，根据提示完成数据库账套删除。该操作会删除账套信息数据库中相关的记录，同时会从物理上删除行业数据库的文件。其核心代码如下：
string ls_exec//动态sql变量
string str_dbname=""//数据库名称
ls_exec="sp_detach_db @dbname='"+str_dbname+"'"；
execute immediate ：ls_exec using tr_sqlca ；
3.3 备份账套
在登录至管理界面之后选中账套数据库点击备份图标，根据选择备份路径等，完成数据库备份。其核心代码如下：
string ls_path ，ls_use，ls_dbname
ls_use = "exec sp_dropdevice 'hotel'"
execute immediate ： ls_use using tr_sqlca；
ls_use = "exec sp_addumpdevice 'disk'"+" ， 'hotel' ， "+" '"+ls_path+"'"
execute immediate ： ls_use using tr_sqlca；
ls_use ="backup database "+ls_dbname+" to hotel"
execute immediate ： ls_use using tr_sqlca；
ls_use = "exec sp_dropdevice 'hotel'"
execute immediate ： ls_use using tr_sqlca；
3.4 恢复账套
恢复功能是建立在备份数据基础上的功能，这个功能的主要实现目的在于当数据丢失或误删时，能够及时恢复数据。其核心代码如下：
string str_bakfile=""
declare sp_ restore procedure for sp_ restore @bakfile=：str_bakfile；
execute sp_ restore；
close sp_ restore；
3.5 压缩账套
这项功能用于对不是经常用的数据进行压缩处理，这样既保留了数据的完整性又避免了数据过多的占用空间资源。其核心代码如下：
string str_datafile
declare sp_dump procedure for sp_dump @datafile=：str_datafile using sqlca；
execute sp_dump；
close sp_dump；
3.6 账套注册
该功能把数据库实体登记到账套管理数据库，以方便应用程序访问，如（图4）所示：
其核心代码如下：
if dw_2.update（）=1 then
commit using sqlca；
if sqlca.sqlcode=0 then
messagebox（"提示"，"注册成功！"）
dw_2.reset（）
dw_2.insertrow（0）
dw_1.retrieve（）
else
messagebox（"提示"，"注册失败，请确定帐套名称和公司名称是否正确！"）
end if
else
messagebox（"提示"，"注册失败，请确定帐套名称和公司名称是否正确！"）
rollback using sqlca；
end if
3.7 账套反注册
该功能取消数据库实体在账套管理数据库的登记，这样用用程序将不能够访问，如（图5）所示：
其核心代码如下：
if dw_1.getrow（）>0 then
dw_1.deleterow（dw_1.getrow（））
if dw_1.update（）=1 then
commit using sqlca；
else
rollback using sqlca；
end if
end if
4 行业MIS访问账套
该功能完成账套数据库的访问，可以完成行业软件的开发。如（图6）所示：
其核心代码如下：
string ls_server//服务器名称
string ls_accout//账套名称
sqlca.dbms = "mss microsoft sql server"
sqlca.database =ls_accout
sqlca.servername = ls_server
sqlca.logid = "sa"
sqlca.autocommit = false
sqlca.dbparm = ""
connect using sqlca；
5 结语
在数据库应用管理程序的设计当中，最重要的是有以下几点：（1）软件消耗的资源不能太多；（2）设计管理软件的时候要尽量避免复杂的操作和设计，以免给用户带来不必要的误解，使用户不能充分的理解该软件的功能从而无法使用，所以设计应当尽量使其简单化；（3）开发软件时周期不能太长。所以该管理程序的开发应用，可以简化开发流程，缩短开发周期以及实现便捷的数据库管理方法，具有很高的实用价值和经济价值。
参考文献：
[1]王蓉等.PowerBuild7.0应用开发技术详解[M].电子工业出版社.2000.
[2]唐学忠等.SQL Server 2000数据库教程[M].电子工业出版社.2000.
[3]Konrad King（美）.译者：杜大鹏等.SQL编程实用大全[M].中国水利水电出版社.2000.
[4]钱雪忠..数据库原理及应用[M].北京邮电大学出版社.2007.
[5]Bain T.SQL server 2000数据仓库与Analysis Services[M].中国电力出版社.2003.
[6]王珊.数据库技术与联机分析处理[M].北京科学出版社.1998.endprint
execute immediate ： ls_sql using tr_sqlca；//恢复数据库；
3.2 删除账套
在登录至管理界面之后选中账套数据库点击删除图标，根据提示完成数据库账套删除。该操作会删除账套信息数据库中相关的记录，同时会从物理上删除行业数据库的文件。其核心代码如下：
string ls_exec//动态sql变量
string str_dbname=""//数据库名称
ls_exec="sp_detach_db @dbname='"+str_dbname+"'"；
execute immediate ：ls_exec using tr_sqlca ；
3.3 备份账套
在登录至管理界面之后选中账套数据库点击备份图标，根据选择备份路径等，完成数据库备份。其核心代码如下：
string ls_path ，ls_use，ls_dbname
ls_use = "exec sp_dropdevice 'hotel'"
execute immediate ： ls_use using tr_sqlca；
ls_use = "exec sp_addumpdevice 'disk'"+" ， 'hotel' ， "+" '"+ls_path+"'"
execute immediate ： ls_use using tr_sqlca；
ls_use ="backup database "+ls_dbname+" to hotel"
execute immediate ： ls_use using tr_sqlca；
ls_use = "exec sp_dropdevice 'hotel'"
execute immediate ： ls_use using tr_sqlca；
3.4 恢复账套
恢复功能是建立在备份数据基础上的功能，这个功能的主要实现目的在于当数据丢失或误删时，能够及时恢复数据。其核心代码如下：
string str_bakfile=""
declare sp_ restore procedure for sp_ restore @bakfile=：str_bakfile；
execute sp_ restore；
close sp_ restore；
3.5 压缩账套
这项功能用于对不是经常用的数据进行压缩处理，这样既保留了数据的完整性又避免了数据过多的占用空间资源。其核心代码如下：
string str_datafile
declare sp_dump procedure for sp_dump @datafile=：str_datafile using sqlca；
execute sp_dump；
close sp_dump；
3.6 账套注册
该功能把数据库实体登记到账套管理数据库，以方便应用程序访问，如（图4）所示：
其核心代码如下：
if dw_2.update（）=1 then
commit using sqlca；
if sqlca.sqlcode=0 then
messagebox（"提示"，"注册成功！"）
dw_2.reset（）
dw_2.insertrow（0）
dw_1.retrieve（）
else
messagebox（"提示"，"注册失败，请确定帐套名称和公司名称是否正确！"）
end if
else
messagebox（"提示"，"注册失败，请确定帐套名称和公司名称是否正确！"）
rollback using sqlca；
end if
3.7 账套反注册
该功能取消数据库实体在账套管理数据库的登记，这样用用程序将不能够访问，如（图5）所示：
其核心代码如下：
if dw_1.getrow（）>0 then
dw_1.deleterow（dw_1.getrow（））
if dw_1.update（）=1 then
commit using sqlca；
else
rollback using sqlca；
end if
end if
4 行业MIS访问账套
该功能完成账套数据库的访问，可以完成行业软件的开发。如（图6）所示：
其核心代码如下：
string ls_server//服务器名称
string ls_accout//账套名称
sqlca.dbms = "mss microsoft sql server"
sqlca.database =ls_accout
sqlca.servername = ls_server
sqlca.logid = "sa"
sqlca.autocommit = false
sqlca.dbparm = ""
connect using sqlca；
5 结语
在数据库应用管理程序的设计当中，最重要的是有以下几点：（1）软件消耗的资源不能太多；（2）设计管理软件的时候要尽量避免复杂的操作和设计，以免给用户带来不必要的误解，使用户不能充分的理解该软件的功能从而无法使用，所以设计应当尽量使其简单化；（3）开发软件时周期不能太长。所以该管理程序的开发应用，可以简化开发流程，缩短开发周期以及实现便捷的数据库管理方法，具有很高的实用价值和经济价值。
参考文献：
[1]王蓉等.PowerBuild7.0应用开发技术详解[M].电子工业出版社.2000.
[2]唐学忠等.SQL Server 2000数据库教程[M].电子工业出版社.2000.
[3]Konrad King（美）.译者：杜大鹏等.SQL编程实用大全[M].中国水利水电出版社.2000.
[4]钱雪忠..数据库原理及应用[M].北京邮电大学出版社.2007.
[5]Bain T.SQL server 2000数据仓库与Analysis Services[M].中国电力出版社.2003.
[6]王珊.数据库技术与联机分析处理[M].北京科学出版社.1998.endprint
execute immediate ： ls_sql using tr_sqlca；//恢复数据库；
3.2 删除账套
在登录至管理界面之后选中账套数据库点击删除图标，根据提示完成数据库账套删除。该操作会删除账套信息数据库中相关的记录，同时会从物理上删除行业数据库的文件。其核心代码如下：
string ls_exec//动态sql变量
string str_dbname=""//数据库名称
ls_exec="sp_detach_db @dbname='"+str_dbname+"'"；
execute immediate ：ls_exec using tr_sqlca ；
3.3 备份账套
在登录至管理界面之后选中账套数据库点击备份图标，根据选择备份路径等，完成数据库备份。其核心代码如下：
string ls_path ，ls_use，ls_dbname
ls_use = "exec sp_dropdevice 'hotel'"
execute immediate ： ls_use using tr_sqlca；
ls_use = "exec sp_addumpdevice 'disk'"+" ， 'hotel' ， "+" '"+ls_path+"'"
execute immediate ： ls_use using tr_sqlca；
ls_use ="backup database "+ls_dbname+" to hotel"
execute immediate ： ls_use using tr_sqlca；
ls_use = "exec sp_dropdevice 'hotel'"
execute immediate ： ls_use using tr_sqlca；
3.4 恢复账套
恢复功能是建立在备份数据基础上的功能，这个功能的主要实现目的在于当数据丢失或误删时，能够及时恢复数据。其核心代码如下：
string str_bakfile=""
declare sp_ restore procedure for sp_ restore @bakfile=：str_bakfile；
execute sp_ restore；
close sp_ restore；
3.5 压缩账套
这项功能用于对不是经常用的数据进行压缩处理，这样既保留了数据的完整性又避免了数据过多的占用空间资源。其核心代码如下：
string str_datafile
declare sp_dump procedure for sp_dump @datafile=：str_datafile using sqlca；
execute sp_dump；
close sp_dump；
3.6 账套注册
该功能把数据库实体登记到账套管理数据库，以方便应用程序访问，如（图4）所示：
其核心代码如下：
if dw_2.update（）=1 then
commit using sqlca；
if sqlca.sqlcode=0 then
messagebox（"提示"，"注册成功！"）
dw_2.reset（）
dw_2.insertrow（0）
dw_1.retrieve（）
else
messagebox（"提示"，"注册失败，请确定帐套名称和公司名称是否正确！"）
end if
else
messagebox（"提示"，"注册失败，请确定帐套名称和公司名称是否正确！"）
rollback using sqlca；
end if
3.7 账套反注册
该功能取消数据库实体在账套管理数据库的登记，这样用用程序将不能够访问，如（图5）所示：
其核心代码如下：
if dw_1.getrow（）>0 then
dw_1.deleterow（dw_1.getrow（））
if dw_1.update（）=1 then
commit using sqlca；
else
rollback using sqlca；
end if
end if
4 行业MIS访问账套
该功能完成账套数据库的访问，可以完成行业软件的开发。如（图6）所示：
其核心代码如下：
string ls_server//服务器名称
string ls_accout//账套名称
sqlca.dbms = "mss microsoft sql server"
sqlca.database =ls_accout
sqlca.servername = ls_server
sqlca.logid = "sa"
sqlca.autocommit = false
sqlca.dbparm = ""
connect using sqlca；
5 结语
在数据库应用管理程序的设计当中，最重要的是有以下几点：（1）软件消耗的资源不能太多；（2）设计管理软件的时候要尽量避免复杂的操作和设计，以免给用户带来不必要的误解，使用户不能充分的理解该软件的功能从而无法使用，所以设计应当尽量使其简单化；（3）开发软件时周期不能太长。所以该管理程序的开发应用，可以简化开发流程，缩短开发周期以及实现便捷的数据库管理方法，具有很高的实用价值和经济价值。
参考文献：
[1]王蓉等.PowerBuild7.0应用开发技术详解[M].电子工业出版社.2000.
[2]唐学忠等.SQL Server 2000数据库教程[M].电子工业出版社.2000.
[3]Konrad King（美）.译者：杜大鹏等.SQL编程实用大全[M].中国水利水电出版社.2000.
[4]钱雪忠..数据库原理及应用[M].北京邮电大学出版社.2007.
[5]Bain T.SQL server 2000数据仓库与Analysis Services[M].中国电力出版社.2003.
[6]王珊.数据库技术与联机分析处理[M].北京科学出版社.1998.endprint
