定时收集存储过程函数视图信息入库(Oracle,MySQL)| dboop.com

需求

下午接到运维转来的一个权限申请流程：大数据部门研发要求开通保垒机权限。以方便在保垒机上安装SQL客户端去查看存储过程和视图的内容

保垒机直连数据库查询，这种不可控的方式，早在去年我就把这个历史问题给禁止掉了，没想到现在还有人要求开通

经过沟通得知，对方想查看Oracle数据库里的一些老的存储过程的代码。而DBA平台上只有表结构相关的数据字典，没有存储过程和视图的数据字典

所以，别慌，不就这点需求吗，马上就可以加上。

为什么DBA平台上的数据字典不包括存储过程和视图？因为存储过程/函数/视图也是被我禁掉的，研发人员上线不可以写存储过程和视图。所以就没想过要在DBA运维平台上做这块功能

但是因为

历史原因，以前的Oracle数据库上已经存在很多的视图和存储过程
第三方原因，公司采购的一些第三方服务和软件，带了存储过程和视图这些被禁止使用的数据库对象，也需要做统一维护

拆解

这些数据库对象的信息用定时任务收集线上的表结构到本地，存为两份
一份入库，做为快照信息，展示给用户。
一份落本地文件，上传到git，用git做版本管理

为什么不在用户请求查看某个数据库对象的信息时，实时查询给用户？

1.因为历史原因，我们有的库有几万个数据库对象，当用户选择一个库时，list列表加载很慢，所以一开始设计的时候，我们做了快照

2.一份快照，还可以用作数据库对象的git版本管理

建表

在dboop库中建表


CREATE TABLE `info_objects` (
  `objectid` int NOT NULL AUTO_INCREMENT,
  `dbid` int NOT NULL DEFAULT '0',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `object_name` varchar(255) NOT NULL DEFAULT '',
  `object_type` varchar(64) NOT NULL DEFAULT '',
  `object_text` longtext,
  `cstatus` smallint NOT NULL DEFAULT '1',
  `dba_freshtime` datetime NOT NULL DEFAULT '1990-01-01 00:00:00',
  PRIMARY KEY (`objectid`),
  UNIQUE KEY `idx_infoobjects_id` (`dbid`,`object_name`,`object_type`),
  KEY `idx_info_objects_time` (`dba_freshtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

实现数据字典收集入库

建Oracle采集任务

 <!--抓存储过程和函数等信息 --> 
<action result="isok,okcount,errcount,errmess" type="sql_to_sql">
<from_server>linkdb:{dbid}</from_server>
  <isadmin>1</isadmin>
<to_server>link:dboop</to_server> 
  <from_formatstr>maxrow:9999999</from_formatstr>
<from_sqlstr>
  SELECT name,type,RTRIM(XMLAGG(XMLELEMENT(E,text,'^^').EXTRACT('//text()') ORDER BY line).GetClobVal(),'^^')  as textstr 
  FROM sys.dba_source where owner=:0 and type  not in ('TRIGGER')  group by name,type
  </from_sqlstr>
  <from_sqlpara>{dbname}</from_sqlpara>
    <to_sqlstr>insert into info_objects(dbid,TABLE_SCHEMA,object_name,object_type,object_text,cstatus,dba_freshtime)
      values(%s,%s,%s,%s,%s,1,%s)
         on duplicate key update  
      object_text=%s,cstatus=2,dba_freshtime=%s       
    </to_sqlstr>
<to_sqlpara>{dbid}</to_sqlpara>
  <to_sqlpara>{dbname}</to_sqlpara>
<to_sqlpara>{0}</to_sqlpara>
<to_sqlpara>{1}</to_sqlpara>
<to_sqlpara>{2}</to_sqlpara>
<to_sqlpara>{tasktime}</to_sqlpara>
<to_sqlpara>{2}</to_sqlpara>
<to_sqlpara>{tasktime}</to_sqlpara>
</action>     

  
  
 <!--抓视图等信息 --> 
<action result="isok,okcount,errcount,errmess" type="sql_to_sql">
<from_server>linkdb:{dbid}</from_server>
  <isadmin>1</isadmin>
<to_server>link:dboop</to_server> 
  <from_formatstr>maxrow:9999999</from_formatstr>
<from_sqlstr>
  SELECT view_name,'view',text as textstr 
  FROM sys.dba_views where OWNER=:0
union all 
select mview_name,'mview' ,query
from dba_mviews  where  OWNER = :0  
  </from_sqlstr>
  <from_sqlpara>{dbname}</from_sqlpara>
    <to_sqlstr>insert into info_objects(dbid,TABLE_SCHEMA,object_name,object_type,object_text,cstatus,dba_freshtime)
      values(%s,%s,%s,%s,%s,1,%s)
         on duplicate key update  
      object_text=%s,cstatus=2,dba_freshtime=%s       
    </to_sqlstr>
<to_sqlpara>{dbid}</to_sqlpara>
  <to_sqlpara>{dbname}</to_sqlpara>
<to_sqlpara>{0}</to_sqlpara>
<to_sqlpara>{1}</to_sqlpara>
<to_sqlpara>{2}</to_sqlpara>
<to_sqlpara>{tasktime}</to_sqlpara>
<to_sqlpara>{2}</to_sqlpara>
<to_sqlpara>{tasktime}</to_sqlpara>
</action>

建MySQL采集任务

MySQL因为我们线上没有历史问题，不存在视图和存储过程，所以还是没必要

如果要建的话，从下面的这两张表里读

# MySQL8.0:
 select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_DEFINITION,CREATED from information_schema.ROUTINES where ROUTINE_SCHEMA='库名';


  select TABLE_NAME as object_name,'view' as object_type,VIEW_DEFINITION from information_schema.views where TABLE_SCHEMA='';

# MySQL5.7及以前存储过程和funcation定义不是在ROUTINES 表中，记得是存在 mysql.proc表中

定时任务会将需要的信息写入info_object表中

补充报表

在原有的数据字典报表中，增加2个page，分别用来展示

存储过程/函数
视图



<page>
  <viewtype>table</viewtype>
  <connstr>link:dboop</connstr>
  <title>存储过程和函数</title>
  <tableheader>
  <![CDATA[
    <tr>
<th style="width:80px;"><h3>DbName</h3></th>
<th style="width:74px;"><h3>类型</h3></th>
<th style="width:230px;"><h3>对象名</h3></th>
<th style="width:640px;"><h3>SQLSTR</h3></th>
</tr>
                ]]>
  </tableheader>
  <sqlstr><![CDATA[
select a.TABLE_SCHEMA AS DbName,
object_type,
concat('<a href="view.html?action=v&report_action=v&report_ptname=dxlnj0t7vfwuq8oyksp3ha2em9&dbid=',dbid,'&id=',objectid,'">',object_name,'</a>')
as object_name
,concat('',replace(replace(replace(replace(left(object_text,140),'^',''),'<',''),'>','') ,object_name,''),'..') as SQLSTR

from info_objects a 
where a.dbid=%s  and object_type in ('FUNCTION','PROCEDURE')
order by a.object_name 
                ]]>
  </sqlstr>
  <sqlpara>#1</sqlpara>
  <width>1024px</width>

</page>




<page>
  <viewtype>table</viewtype>
  <connstr>link:dboop</connstr>
  <title>视图</title>
  <tableheader>
  <![CDATA[
    <tr>
<th style="width:80px;"><h3>DbName</h3></th>
<th style="width:74px;"><h3>类型</h3></th>
<th style="width:230px;"><h3>对象名</h3></th>
<th style="width:640px;"><h3>SQLSTR</h3></th>
</tr>
                ]]>
  </tableheader>
  <sqlstr><![CDATA[
select a.TABLE_SCHEMA AS DbName,
object_type,
concat('<a href="view.html?action=v&report_action=v&report_ptname=dxlnj0t7vfwuq8oyksp3ha2em9&dbid=',dbid,'&id=',objectid,'">',object_name,'</a>')
as object_name
,concat('',replace(replace(replace(left(object_text,120),'^',''),'<',''),'>',''),'..') as SQLSTR

from info_objects a
where a.dbid=%s  and object_type in ('view','mview')
order by a.object_name 
                ]]>
  </sqlstr>
  <sqlpara>#1</sqlpara>
  <width>1024px</width>

</page>

效果一：数据字典用户展示页

指定用户查询数字典时，可以获得存储过程和函数的列表和详细定义

数据字典用户展示页

效果二：数据字典git版本管理页

数据字典git版本管理页

非常快速的解决了用户查数据库对象信息的问题

只需要几步简单的xml配置，就可以完成

非常的简单

>> Home

定时收集存储过程函数视图信息入库(Oracle,MySQL)

需求

拆解

建表