python 脚本 通过解析 DDL 更新PDM

     需求：

            上线工作涉及到表结构更新时，由开发人员提交变更的sql语句由DBA操作，为了保障表结构变更与PDM同步并且自动化，写了python脚本，解析DDL语句更新PDM文件。

    PDM是XML文件为脚本更新提供可能。

         代码如下：

         PDM解析操作脚本：

         

#!/usr/bin/python
# -*- coding: utf-8 -*- #

from xml.dom.minidom import parse
import xml.dom.minidom


class PDMHandler(object):
    TBL_ATTR_LIST = ["Code"]
    COL_ATTR_LIST = ["Code"]

    tableNodes = {}
    columnNodes = {}
    modelTablesNode = None
    ph = None

    filename = 'package.pdm'

    def __init__(self):
        self.init();
        return

    def init(self):
        self.ph = PDMHandler.parse(self.filename)
        for pkg in PDMHandler.getPkgNodes(self.ph):
            for tbl in PDMHandler.getTblNodesInPkg(pkg):
                tbl_attrs = PDMHandler.getTblAttrs(tbl)
                self.tableNodes[tbl_attrs['Code']] = tbl
                for col in PDMHandler.getColNodesInTbl(tbl):
                    col_attrs = PDMHandler.getColAttrs(col)
                    self.columnNodes[tbl_attrs['Code'] + '_' + col_attrs['Code']] = col

    @staticmethod
    def parse(pdmfilename):
        """
        @brief       PDM文件导入函数
        @param[in]   pdmfilename       pdm文件名
        @return      pdm文件的DOM结构
        """
        return xml.dom.minidom.parse(pdmfilename)

    @staticmethod
    def __get_nodes_by_path(parent, xml_path):
        """
        @brief 按路径取节点核心函数,通过传入一个节点和一个路径
               (类文件系统路径)的字符串,获得相应的子结点(或集合).
               例1: __get_nodes_by_path(node,"a/b/c|3/d")
                 即获得node下a下b下第3个c节点下的所有d节点的一个
                 list.
               例2: __get_nodes_by_path(node,"a/b/c|3/d|2")
                 即获得node下a下b下第3个c节点下的第2个d节点.
                 注意:此处返回的不是list,而是节点
        @param[in]   parent DOM中的父节点
        @return      节点list或节点句柄
        """
        curr_node = parent
        for tag in xml_path.split("/")[0:-1]:
            tag_desc = tag.split("|")
            tag_name, tag_index = tag_desc[0], (int(tag_desc[1]) if len(tag_desc) == 2 else 0)
            child_nodes = []
            for child_node in curr_node.childNodes:
                if child_node.nodeName == tag_name:
                    child_nodes.append(child_node)
            if len(child_nodes) < tag_index + 1:
                return []
            curr_node = child_nodes[tag_index]
        # -- 最后一级路径特殊处理 -- #
        tag = xml_path.split("/")[-1]
        tag_desc = tag.split("|")
        tag_name, tag_index = tag_desc[0], (int(tag_desc[1]) if len(tag_desc) == 2 else None)
        child_nodes = []
        for child_node in curr_node.childNodes:
            if child_node.nodeName == tag_name:
                child_nodes.append(child_node)
        if tag_index == None:
            return child_nodes
        elif len(child_nodes) < tag_index + 1:
            return []
        else:
            curr_node = child_nodes[tag_index]
            return curr_node

    @staticmethod
    def __get_attrs_by_list(parent, attr_list):
        """
        @brief       取PDM属性(注意:此属性非xml属性）
                     背景:PDM文件中主要节点(o:Table,o:Column...)的具体属性
                          一般以子节点形式表示，而不以通常的XML属性表示.此
                          函数传入一个attr_list,输出一个字典表示的:
                          { "attr":"value" ...}
        @param[in]   parent    需要被取属性的节点句柄
        @param[in]   attr_list 一个字符串的list["attr1","attr2"...],代表要取的属性名集合
        @return      返回一个dict:{"attr1":"value" ...}
        """
        ret_dict = {}
        for attr in attr_list:
            ret_dict[attr] = ""
            for child in parent.childNodes:
                if child.nodeName == "a:" + attr:
                    ret_dict[attr] = child.childNodes[0].data
                    break
        return ret_dict

    @staticmethod
    def __get_pkgnodes_recursively(o_pkg):
        # -- 需要传入一个o:Model/o:Package节点 --#
        if o_pkg.nodeName != "o:Model" and o_pkg.nodeName != "o:Package":
            return []
        ret_list = []
        subpkgs = PDMHandler.__get_nodes_by_path(o_pkg, "c:Packages/o:Package")
        if subpkgs != None:
            for subpkg in subpkgs:
                ret_list.append(subpkg)
                ret_list = ret_list + PDMHandler.__get_pkgnodes_recursively(subpkg)
        else:
            return []
        return ret_list

    @staticmethod
    def getPkgNodes(hpdm):
        """
        @brief       获取pdm文件中所有的o:package的句柄list
        @param[in]   hpdm    待处理的pdm文件DOM句柄(可通过PDMHandler.parse取得)
        @return      返回pacakge的节点list:[pkg1,pkg2...],元素为DOM节点类型
        """
        ret_list = []
        try:
            o_mdl = PDMHandler.__get_nodes_by_path(hpdm, "Model/o:RootObject/c:Children/o:Model")[0]
            ret_list.append(o_mdl)
        except IndexError:
            print("ERROR:不是一个合法的pdm文件!")
            return []
        ret_list = ret_list + PDMHandler.__get_pkgnodes_recursively(o_mdl)
        return ret_list

    @staticmethod
    def getModelTablesNodes(hpdm):
        """
        @brief       获取pdm文件中所有的o:package的句柄list
        @param[in]   hpdm    待处理的pdm文件DOM句柄(可通过PDMHandler.parse取得)
        @return      返回pacakge的节点list:[pkg1,pkg2...],元素为DOM节点类型
        """
        ret_list = []
        try:
            o_mdl = PDMHandler.__get_nodes_by_path(hpdm, "Model/o:RootObject/c:Children/o:Model/c:Tables")
            if len(o_mdl) == 0:
                o_mdl = PDMHandler.__get_nodes_by_path(hpdm, "Model/o:RootObject/c:Children/o:Model")[0]
                cTables = hpdm.createElement('c:Tables')
                o_mdl.appendChild(cTables)
                ret_list.append(cTables)
            else:
                ret_list.append(o_mdl[0])
        except IndexError:
            return []
        return ret_list

    @staticmethod
    def getTblNodesInPkg(pkgnode):
        """
        @brief       获取指定o:Package下的所有o:Table的list
        @param[in]   pkgnode 待处理的o:Package节点(可通过PDMHandler.getPkgNodes取得)
        @return      返回o:Table的节点list:[tbl1,tbl2...],元素为DOM节点类型
        """
        return PDMHandler.__get_nodes_by_path(pkgnode, "c:Tables/o:Table")

    @staticmethod
    def getColNodesInTbl(tblnode):
        """
        @brief       获取指定o:Table下的所有o:Column的list
        @param[in]   tblnode 待处理的o:Table节点(可通过PDMHandler.getTblInPkg取得)
        @return      返回o:Column的节点list:[col1,col2...],元素为DOM节点类型
        """
        return PDMHandler.__get_nodes_by_path(tblnode, "c:Columns/o:Column")

    @staticmethod
    def getIdxNodesInTbl(tblnode):
        """
        @brief       获取指定o:Table下的所有o:Index的list
        @param[in]   tblnode 待处理的o:Table节点(可通过PDMHandler.getTblInPkg取得)
        @return      返回o:Index的节点list:[idx1,idx2...],元素为DOM节点类型
        """
        return PDMHandler.__get_nodes_by_path(tblnode, "c:Indexes/o:Index")

    @staticmethod
    def getIdxColNodesInIdx(idxnode):
        """
        @brief       获取指定o:Index下的所有o:IndexColumn的list
        @param[in]   idxnode 待处理的o:Index节点(可通过PDMHandler.getIdxNodesInTbl取得)
        @return      返回o:IndexColumn的节点list:[idxcol1,idxcol2...],元素为DOM节点类型
        """
        return PDMHandler.__get_nodes_by_path(idxnode, "c:IndexColumns/o:IndexColumn")

    @staticmethod
    def getTblAttrs(tblnode):
        """
        @brief       获取指定o:Table的属性(可取的属性参见PDMHandler.TBL_ATTR_LIST)
        @param[in]   tblnode 待处理的o:Table节点(可通过PDMHandler.getTblNodesInPkg取得)
        @return      返回一个字典dict:{"attr1":"value",...}
        """
        return PDMHandler.__get_attrs_by_list(tblnode, PDMHandler.TBL_ATTR_LIST)

    @staticmethod
    def getColAttrs(colnode):
        """
        @brief       获取指定o:Column的属性(可取的属性参见PDMHandler.COL_ATTR_LIST)
        @param[in]   colnode 待处理的o:Column节点(可通过PDMHandler.getColNodesInTbl取得)
        @return      返回一个字典dict:{"attr1":"value",...}
        """
        return PDMHandler.__get_attrs_by_list(colnode, PDMHandler.COL_ATTR_LIST)

    @staticmethod
    def getNodePath(node):
        """
        @brief       获取指定node的路径(模拟文件系统路径表示法)
        @param[in]   node 待处理的节点,类型为DOM的节点类型
        @return      返回一个字符串表示node的路径,形如"/foo/bar/node"
        """
        curr = node
        path_nodes = []
        while (1):
            if curr != None and curr.nodeName != "#document":
                path_nodes.append(curr.tagName)
            else:
                break
            curr = curr.parentNode
        path_nodes.reverse()
        path = "".join([slash + node for slash in '/' for node in path_nodes])
        return path

    @staticmethod
    def getNode(parent, nodeName):
        """
        @brief 判断结点parent的名称是否与参数名称nodeName相同，相同返回parent,否则返则None
        :param parent: 节点
        :param nodeName: 参数名称
        :return:
        """
        for node in parent.childNodes:
            if node.nodeName == 'a:Name' and node.childNodes[0].data == nodeName:
                return parent
        return None

    @staticmethod
    def getTableColumnsNode(tableNode):
        """
        @brief 根据表结点tableNode查询列结点
        :param tableNode: 表节点
        :return: 表节点下节点c:Columns
        """
        for node in tableNode.childNodes:
            if node.nodeName == 'c:Columns':
                return node
        return None

    @staticmethod
    def addColumn(pdm, tableCode, columnObjectList=None):
        """
        @brief 增加列
        :param pdm: dom文档
        :return:
        """

        if tableCode == None:
            print('待增加列对应表编码不能为空')
            return False

        if columnObjectList == None or len(columnObjectList) == 0:
            print('待增加列不能为空且格式为[{"code":,"name":,...}]')
            return False

        for columnObject in columnObjectList:

            tableNode = pdm.tableNodes[tableCode]

            TableColumnsNode = PDMHandler.getTableColumnsNode(tableNode)

            nColumn = pdm.ph.createElement('o:Column')
            TableColumnsNode.appendChild(nColumn)

            nCode = pdm.ph.createElement('a:Code')
            cCode = pdm.ph.createTextNode(columnObject['code'])
            nCode.appendChild(cCode)
            nColumn.appendChild(nCode)

            pdm.columnNodes[tableCode + '_' + columnObject['code']] = nColumn

            nName = pdm.ph.createElement('a:Name')
            cName = pdm.ph.createTextNode(columnObject['code'] if columnObject.get('name') == None else columnObject['name'])
            nName.appendChild(cName)
            nColumn.appendChild(nName)

            if columnObject.get('comment') != None:
                nComment = pdm.ph.createElement('a:Comment')
                cComment = pdm.ph.createTextNode(columnObject['comment'])
                nComment.appendChild(cComment)
                nColumn.appendChild(nComment)

            nDataType = pdm.ph.createElement('a:DataType')
            cDataType = pdm.ph.createTextNode(columnObject['dataType'])
            nDataType.appendChild(cDataType)
            nColumn.appendChild(nDataType)

            nLength = pdm.ph.createElement('a:Length')
            cLength = pdm.ph.createTextNode(columnObject['length'])
            nLength.appendChild(cLength)
            nColumn.appendChild(nLength)

    @staticmethod
    def updateColumn(pdm, tableCode, columnObjectList=None):
        """
        @breif 更新列
        :param pdm:  dom文档
        :param tableCode: 表编码
        :param columnCode:
        :return:
        """

        if tableCode == None:
            print('待修改列对应表编码不能为空')
            return False

        if columnObjectList == None or len(columnObjectList) == 0:
            print('待增加列不能为空且格式为[{"code":,"name":},...]')
            return False

        for columnObject in columnObjectList:

            columnNode = pdm.columnNodes[tableCode + '_' + columnObject['code']]

            for key in columnObject.keys():
                if key != 'code':
                    tf = False
                    for node in columnNode.childNodes:
                        if node.nodeName == 'a:' + key.capitalize():
                            node.removeChild(node.childNodes[0])

                            cName = pdm.ph.createTextNode(columnObject[key])
                            node.appendChild(cName)
                            tf = True
                            break
                    if tf == False:
                        csNode = pdm.ph.createElement('a:' + key.capitalize())
                        columnNode.appendChild(csNode)
                        cName = pdm.ph.createTextNode(columnObject[key])
                        csNode.appendChild(cName)

    @staticmethod
    def addTable(pdm, tableObject=None):
        """
        @breif  增加表
        :param pdm: dom文档
        :return:
        """

        if tableObject == None or tableObject.get('code') == None:
            print('待增加表不能为空且格式为{"code":,...}')
            return False

        if pdm.modelTablesNode == None:
            pdm.modelTablesNode = PDMHandler.getModelTablesNodes(pdm.ph)[0]

        nTable = pdm.ph.createElement('o:Table')
        pdm.modelTablesNode.appendChild(nTable)

        pdm.tableNodes[tableObject['code']] = nTable

        nCode = pdm.ph.createElement('a:Code')
        cCode = pdm.ph.createTextNode(tableObject['code'])
        nCode.appendChild(cCode)
        nTable.appendChild(nCode)

        # tableObject['name'] = tableObject['code'] if tableObject.get('name') != None else tableObject['name']

        # if tableObject.get('name') != None:
        nName = pdm.ph.createElement('a:Name')
        cName = pdm.ph.createTextNode(tableObject['code'] if tableObject.get('name') == None else tableObject['name'])
        nName.appendChild(cName)
        nTable.appendChild(nName)

        if tableObject.get('comment') != None:
            nComment = pdm.ph.createElement('a:Comment')
            cComment = pdm.ph.createTextNode(tableObject['comment'])
            nComment.appendChild(cComment)
            nTable.appendChild(nComment)

        nColumns = pdm.ph.createElement('c:Columns')
        nTable.appendChild(nColumns)

    @staticmethod
    def updateTable(pdm, tableCode,tableObject=None):
        """
        @breif  增加表
        :param pdm: dom文档
        :return:
        """
        if tableObject == None:
            print('待修改表不能为空且格式为{"name":,....}')
            return False

        tableNode = pdm.tableNodes[tableCode]

        for key in tableObject.keys():
            if key != 'code':
                tf = False
                for node in tableNode.childNodes:
                    if node.nodeName == 'a:' + key.capitalize():
                        node.removeChild(node.childNodes[0])

                        cName = pdm.ph.createTextNode(tableObject[key])
                        node.appendChild(cName)
                        tf = True
                        break
                if tf == False:
                    tsNode = pdm.ph.createElement('a:' + key.capitalize())
                    tableNode.appendChild(tsNode)
                    cName = pdm.ph.createTextNode(tableObject[key])
                    tsNode.appendChild(cName)

    @staticmethod
    def delTable(pdm, tableCodeList=None):
        """
        @breif  删除表
        :param pdm: dom文档
        :return:
        """

        if tableCodeList == None or len(tableCodeList) == 0:
            print('待删除表code列表不能为空')
            return False

        for tableCode in tableCodeList:

            tableNode = pdm.tableNodes[tableCode]
            tableNode.parentNode.removeChild(tableNode)

            pdm.tableNodes.pop(tableCode)

            keysTmp = []

            for key in pdm.columnNodes.keys():
                keysTmp.append(key)

            for key in keysTmp:
                if key.startswith(tableCode + '_'):
                    pdm.columnNodes.pop(key)


    @staticmethod
    def delColumn(pdm,tableCode, columnCodeList=None):
        """
        @breif  删除列
        :param pdm: dom文档
        :return:
        """

        if columnCodeList == None or len(columnCodeList) == 0:
            print("待删除表code列表不能为空且格式如下['columnCode'...]")
            return False

        for columnCode in columnCodeList:
            colunmNode = pdm.columnNodes[tableCode + '_' + columnCode]
            colunmNode.parentNode.removeChild(colunmNode)

            pdm.columnNodes.pop(tableCode + '_' + columnCode)

    DDL解析操作脚本：

    
#!/usr/bin/python
# -*- coding: utf-8 -*- #
import re


class DDLHandler(object):

    """
    @preif  DDL解析
    """
    ANNOTION = ['-','#']
    LIST_DDL = ['CREATE TABLE','DROP TABLE','ALTER TABLE','COMMENT ON TABLE','COMMENT ON COLUMN']

    list_ddl = []
    list_ddl_line_join = []
    fileName = 'ddl.sql'

    list_table_add = []
    list_column_add = {}
    list_table_del = []
    list_table_update = {}
    list_column_update = {}
    list_column_del = {}

    def __init__(self):
        self.init()
        self.joinLine()
        self.parseDdl()
        return

    def init(self):
        with open(self.fileName,'r',encoding='utf-8') as fl:
            for line in fl:
                if not re.match('^\s*('+'|'.join(self.ANNOTION)+')',line):

                    line = re.sub('\s+',' ', line)

                    self.list_ddl.append(line.upper().strip())

    def joinLine(self):
        list_join = []
        for line in self.list_ddl:
            if re.match('^\s*('+'|'.join(self.LIST_DDL)+')', line):
                if re.match('^\s*' + self.LIST_DDL[0], line):
                    if re.match('.*\);\s*$', line):
                        self.list_ddl_line_join.append(line)
                    else:
                        list_join.append(line)
                else:
                    self.list_ddl_line_join.append(line)
            elif re.match('^\s*\);\s*$', line):
                list_join.append(line)
                self.list_ddl_line_join.append(' '.join(list_join))
                list_join = []
            else:
                list_join.append(line)

    def parseDdl(self):
        for sql in self.list_ddl_line_join:
            if re.match('^\s*'+self.LIST_DDL[0], sql):
                self.parseTableCreate(sql)
            elif re.match('^\s*'+self.LIST_DDL[1], sql):
                self.parseTableDel(sql)
            elif re.match('^\s*'+self.LIST_DDL[2], sql):
                self.parseColumnUpdate(sql)
            elif re.match('^\s*'+self.LIST_DDL[3], sql):
                self.parseTableComment(sql)
            elif re.match('^\s*'+self.LIST_DDL[4], sql):
                self.parseColumnComment(sql)

    def parseTableCreate(self,sql):
        regex = re.compile('^\s*CREATE\s+TABLE\s+(?P<tableCode>\w+)\s*\( (?P<columnCodes>.+)\);')
        match = regex.match(sql)
        tableCode = match.groupdict()['tableCode']
        columnCodes = match.groupdict()['columnCodes']
        columnCodes = columnCodes.split(',')

        tableObject = {'code':tableCode}
        self.list_table_add.append(tableObject)

        list = []
        for column in columnCodes:
            regex = re.compile('^\s*(?P<columnCode>\w+)\s+(?P<dataType>\w+)(\((?P<length>\d+)\))?.*$')
            match = regex.match(column)

            length = match.groupdict()['length'] if match.groupdict().get('length') != None else ''
            dataType = match.groupdict()['dataType']+ ('('+match.groupdict()['length']+')' if match.groupdict().get('length') != None else '')

            data = {'code':match.groupdict()['columnCode'],'dataType':dataType,'length':length}
            list.append(data)

        self.list_column_add[tableCode] = list

    def parseTableDel(self, sql):
        regex = re.compile('^\s*DROP\s+TABLE\s+(?P<tableCode>\w+)\s*;')
        match = regex.match(sql)
        self.list_table_del.append(match.groupdict()['tableCode'])

    def parseColumnUpdate(self, sql):
        if re.match('^\s*ALTER\s+TABLE\s+\w+\s+ADD', sql):
            regex = re.compile('^\s*ALTER\s+TABLE\s+(?P<tableCode>\w+)\s+ADD\s*\((?P<columnCode>\w+)\s+(?P<dataType>\w+)(\((?P<length>\d+)\))?\);$')
            match = regex.match(sql)

            length = match.groupdict()['length'] if match.groupdict().get('length') != None else ''
            dataType = match.groupdict()['dataType'] + ('(' + match.groupdict()['length'] + ')' if match.groupdict().get('length') != None else '')

            data = {'code':match.groupdict()['columnCode'],'dataType':dataType,'length':length}

            if self.list_column_add.get(match.groupdict()['tableCode']) == None:
                self.list_column_add[match.groupdict()['tableCode']] = [data]
            else:
                self.list_column_add[match.groupdict()['tableCode']].append(data)

        elif re.match('^\s*ALTER\s+TABLE\s+\w+\s+DROP', sql):
            regex = re.compile('^\s*ALTER\s+TABLE\s+(?P<tableCode>\w+)\s+DROP\s+COLUMN\s+(?P<columnCode>\w+);$')
            match = regex.match(sql)

            if self.list_column_del.get(match.groupdict()['tableCode']) == None:
                self.list_column_del[match.groupdict()['tableCode']] = [match.groupdict()['columnCode']]
            else:
                self.list_column_del[match.groupdict()['tableCode']].append(match.groupdict()['columnCode'])

        elif re.match('^\s*ALTER\s+TABLE\s+\w+\s+MODIFY', sql):
            regex = re.compile('^\s*ALTER\s+TABLE\s+(?P<tableCode>\w+)\s+MODIFY\s*\((?P<columnCode>\w+)\s+(?P<dataType>\w+)(\((?P<length>\d+)\))?\);$')
            match = regex.match(sql)

            length = match.groupdict()['length'] if match.groupdict().get('length') != None else ''
            dataType = match.groupdict()['dataType'] + ('(' + match.groupdict()['length'] + ')' if match.groupdict().get('length') != None else '')

            data = {'code': match.groupdict()['columnCode'], 'dataType': dataType, 'length': length}

            if self.list_column_update.get(match.groupdict()['tableCode']) == None:
                self.list_column_update[match.groupdict()['tableCode']] = [data]
            else:
                self.list_column_update[match.groupdict()['tableCode']].append(data)

    def parseTableComment(self,sql):
        regex = re.compile("^\s*COMMENT\s+ON\s+TABLE\s+(?P<tableCode>\w+)\s+IS\s+'(?P<comment>.*)';$")
        match = regex.match(sql)

        if self.list_table_update.get(match.groupdict()['tableCode']) == None:
            self.list_table_update[match.groupdict()['tableCode']] = {'comment':match.groupdict()['comment']}
        else:
            self.list_table_update[match.groupdict()['tableCode']]['comment'] = match.groupdict()['comment']


    def parseColumnComment(self,sql):
        regex = re.compile("^\s*COMMENT\s+ON\s+COLUMN\s+(?P<tableCode>\w+)\.(?P<columnCode>\w+)\s+IS\s+'(?P<comment>.*)';$")
        match = regex.match(sql)

        data = {'code': match.groupdict()['columnCode'], 'comment': match.groupdict()['comment']}

        if self.list_column_update.get(match.groupdict()['tableCode']) == None:
            self.list_column_update[match.groupdict()['tableCode']] = [data]
        else:
            self.list_column_update[match.groupdict()['tableCode']].append(data)

if __name__ ==  '__main__':
        # ddl = DDLHandler()
        # text = 'CREATE TABLE STUDENTS( SNO CHAR(10), SNAME VARCHAR2(20), AGE NUMBER(3), SCHOOLDATE DATE, GRADE NUMBER(1) DEFAULT 1, CLAZZ NUMBER(2), EMAIL VARCHAR2(100) );'
        # ddl.parseTableCreate(text)
        # print(ddl.list_column_add)

        # text = "COMMENT ON COLUMN BANNER.NAME IS 'COMMENT';"
        # regex = re.compile("^\s*COMMENT\s+ON\s+COLUMN\s+(?P<tableCode>\w+)\.(?P<columnCode>\w+)\s+IS\s+'(?P<comment>.*)';$")
        # match = regex.match(text)
        # print(match.groupdict())
        text = 'CREATE     TABLE    NOTE'

        print(re.sub('\s+',' ',text))
        # if re.match('^\s*' + 'CREATE TABLE', text.replace('\s+',' ')):
        #     print(True)
        # else:
        #     print(False)

    DDL文件：
    
--创建表结构
CREATE      TABLE STUDENT(
    SNO CHAR(10),
    SNAME VARCHAR2(20),
    AGE NUMBER(3),
    SCHOOLDATE DATE,
    GRADE NUMBER(1) DEFAULT 1,
    CLAZZ NUMBER(2),
    EMAIL VARCHAR2(100)
);
CREATE TABLE STUDENTS( SNO CHAR(10), SNAME VARCHAR2(20), AGE NUMBER(3), SCHOOLDATE DATE, GRADE NUMBER(1) DEFAULT 1, CLAZZ NUMBER(2), EMAIL VARCHAR2(100) );
--删除表结构
DROP TABLE STUDENT;
DROP TABLE STUDENTS;
--修改表的名称
RENAME BANK TO SXTBANK;
--向表中添加注释
COMMENT ON TABLE STUDENT is 'banner';
-------------------修改表的结构
--添加列
ALTER TABLE STUDENT ADD(ADDRESS VARCHAR2(100));
ALTER TABLE STUDENT ADD(CLASS VARCHAR2(100));
--删除列
ALTER TABLE STUDENT DROP COLUMN ADDRESS;
ALTER TABLE STUDENT DROP COLUMN AGE;
ALTER TABLE STUDENTS DROP COLUMN SCHOOLDATE;
--修改列
ALTER TABLE STUDENT MODIFY(EMAIL VARCHAR2(200));
ALTER TABLE STUDENT MODIFY(CLASS VARCHAR2(200));
--向列添加注释
COMMENT ON COLUMN STUDENT.sname is 'the name of employees';
 
   测试脚本：

    
from PDMHandler import PDMHandler
from DDLHandler import DDLHandler

if __name__ == '__main__':

    ddl = DDLHandler()
    pmd = PDMHandler()

    #创建表
    if len(ddl.list_table_add) != 0:
        for sql_table_add in ddl.list_table_add:
            PDMHandler.addTable(pmd, sql_table_add)

    #修改表
    if len(ddl.list_table_update) != 0:
        for key in ddl.list_table_update.keys():
            PDMHandler.updateTable(pmd, key, ddl.list_table_update[key])

    #增加列
    if len(ddl.list_column_add) != 0:
        for key in ddl.list_column_add.keys():
            PDMHandler.addColumn(pmd, key, ddl.list_column_add[key])

    #修改列
    if len(ddl.list_column_update) != 0:
        for key in ddl.list_column_update.keys():
            PDMHandler.updateColumn(pmd, key, ddl.list_column_update[key])

    #删除列
    if len(ddl.list_column_del) != 0:
        for key in ddl.list_column_del.keys():
            PDMHandler.delColumn(pmd, key, ddl.list_column_del[key])

    #删除表
    if len(ddl.list_table_del) != 0:
        PDMHandler.delTable(pmd, ddl.list_table_del)

    #写入文件
    with open('package_tmp.pdm', 'w',encoding = 'utf-8') as xmlfile:
        pmd.ph.writexml(xmlfile, addindent=' ' * 2, newl='\n', encoding='utf-8')
    
   测试通过，待改进