liquibase changeset generation support

Has anyone come up with macro's or templates to support generation of liquibase changeset markup for generated code in the annotated SQL form or any other supported form for that matter?

Parents
No Data
Reply
  • I wrote a macro a while ago that would take the DDL generated from ER Studio and wrap that statements in changeset markup for Liquibase.  We wound up going with Datical so we're not using this code, so can't guarantee it works perfectly, but should give you a starting point:

    	'#Language "WWB-COM"
    	'Option Explicit
    
    	'dim ER/Studio variables
    	Dim oDiagram As Diagram
    	Dim oModel As Model
    	Dim sqlFileName As String
    	Dim sInDelim As String
    	Dim sOutDelim As String
    	Dim bakFileName As String
    	Dim bAuxTablePartCheck As Boolean
    	Dim bTablespaceDelete As Boolean
    	Dim aTList(500) As String
    	Dim iTCount As Integer
    
    	Public Const QUOTE As String = """
    
    	Sub Main
    
    		Dim filename As String
    		Dim bCopyFile As Boolean
    
    		Dim sLine As String
    		Dim sOutSQL As String
    		Dim sChgSet As String
    		Dim sSQL As String
    		Dim sPreCond As String
    		Dim iChgLog As Long
    		Dim bNewChgSet As Boolean
    		Dim iPeriod As Integer
    		Dim sTablename As String
    		Dim sSchemaname As String
    
    		'hold a static date and time for unique changeLog identification
    		Dim sChgLogTime As String
    		Debug.Clear
    
    		bAuxTablePartCheck = False
    		bTablespaceDelete = False
    		iTCount = 0
    
    		Set oDiagram = DiagramManager.ActiveDiagram 	'get the diagram object
    		Set oModel = oDiagram.ActiveModel				'get the model object
    
    		'UI so user can select the file to be converted
    		Begin Dialog UserDialog 880,273,"Modify Generated DDL for " & oModel.MajorPlatform & " Compression",.DialogFunc ' %GRID:10,7,1,1
    			TextBox 330,150,40,21,.txtOutDelim
    			Text 60,150,270,21,"Statement delimiter for database (output):",.lblOutDelim
    			Text 40,14,800,35,"This macro takes a DDL file generated from ER Studio and wraps it in the XML tags necessary to be processed by Liquibase." & vbNewLine & vbNewLine & "Select the generated DDL file below.",.Text2
    			Text 60,63,660,14,"Path to " & oModel.MajorPlatform & " DDL File",.Text1
    			TextBox 60,80,660,21,.Path
    			PushButton 740,80,60,21,"Browse",.Browse
    			Text 60,120,250,21,"DDL File Statement Delimiter (input):",.lblInDelim
    			TextBox 330,120,40,21,.txtInDelim
    			OKButton 250,190,110,21
    			CancelButton 420,190,110,21
    			Text 60,231,750,28,"*Note: Delimiters are based on the currently open model.  Please adjust if the DDL was generated from a different model.  Oracle Compare and Merge DDL contains a slash (/) delimiter.",.Text3
    		End Dialog
    		Dim dlg As UserDialog
    
    		start_dialog:
    
    		'launch the UI dialog
    		If Dialog(dlg) <> -1 Then Exit Sub 'exits if user clicks Cancel button
    
    		sqlFileName = dlg.Path	'get the DDL (.sql) file name from the UI textbox
    		sInDelim = Replace(dlg.txtInDelim, vbNewLine, "")
    		sOutDelim = Replace(dlg.txtOutDelim, vbNewLine, "")
    
    		iPeriod = InStrRev(sqlFileName, ".")	'find the period in the filename
    
    		On Error GoTo BadFile
    
    		'Assign the same filename, but with an XML extension for the Liquibase file
    		filename = Left(sqlFileName, iPeriod) & "xml"
    		bakFileName = Left(sqlFileName, iPeriod) & "BAK"
    		FileCopy sqlFileName, bakFileName
    
    		'get the current date/time, we'll use this with an incremented value for a unique changeset id
    		sChgLogTime = Format(Now(), "yyyy/mm/dd hh:mm:ss")
    
    		bNewChgSet = True	'flag to indicate whether a new changeset is needed
    		iChgLog = 1			'seed the change set ID
    
    		Open bakFileName For Input As #1	'open the backup DDL File for input
    		Open filename For Output As #2		'open the XML file for output
    		Open sqlFileName For Output As #3	'open the DDL file for output so we can correct ER Studio DDL errors
    
    
    		'header stuff for the xml file
    		sOutSQL = "<databaseChangeLog xmlns=" & QUOTE & "http://www.liquibase.org/xml/ns/dbchangelog" & QUOTE & "  xmlns:xsi=" & QUOTE
    		sOutSQL = sOutSQL & "http://www.w3.org/2001/XMLSchema-instance" & QUOTE & " xmlns:ext=" & QUOTE & "http://www.liquibase.org/xml/ns/dbchangelog-ext" & QUOTE
    		sOutSQL = sOutSQL & " xsi:schemaLocation=" & QUOTE & "http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd "
    		sOutSQL = sOutSQL & "http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd" & QUOTE & ">" & vbNewLine
    
    		While Not EOF(1)
    			'Read a line from the DDL file
    			Line Input #1, sLine
    			If Trim(sLine)<>"" Then	'only process the line if it's not empty, otherwise we'll ignore it
    
    				sLine = FixDDL(sLine) 'Fix ER Studio DDL Errors first
    
    				If iChgLog = 1 And bNewChgSet = True Then 'check if this is an Alter SQL, if so we need to look for a slash as the statement delimiter
    					If oModel.MajorPlatform = "Oracle" And InStr(sLine, "Standard Alter Table SQL")>0 Then sInDelim = "/"
    				End If
    
    				'assumes delimiter is always on its own line
    				If Right(sLine, Len(sInDelim)) = sInDelim And sInDelim <> sOutDelim Then sLine = Replace(sLine, sInDelim, sOutDelim) 'replace the delimiter
    				If bNewChgSet = True Then 'need to start a new changeSet
    					sChgSet = "	<changeSet id=" & QUOTE & sChgLogTime & " - " & CStr(iChgLog) & QUOTE & "  author=" & QUOTE & DiagramManager.CurrentUser & QUOTE & ">" & vbNewLine
    					bNewChgSet = False
    				End If 'If bNewChgSet = True
    
    				'If there's a DROP statement, we want to put a precondition on it so the DDL doesn't fail...
    				'only for DROP TABLE, DROP VIEW, or DROP SEQUENCE...
    				If Left(sLine, 5) = "DROP " And oModel.MajorPlatform<>"Microsoft SQL Server" Then
    					Select Case Left(sLine, 11)
    						Case "DROP TABLE "
    							'start the precondition XML
    							sPreCond =  "		<preConditions onFail=" & QUOTE & "MARK_RAN" & QUOTE & ">" & vbNewLine
    							sPreCond = sPreCond & "			<tableExists "
    							'remove commands around the object name
    							sTablename = Trim(Replace(Replace(sLine, "DROP TABLE", ""), "CASCADE CONSTRAINTS", ""))
    							'see if there's a schema name in what's remaining
    							iPeriod = InStr(sTablename, ".")
    							If iPeriod>0 Then
    								sSchemaname = Left(sTablename, iPeriod - 1)
    								'add schema name to precondition
    								sPreCond = sPreCond & "schemaName=" & QUOTE & sSchemaname & QUOTE & " "
    								sTablename = Replace(sTablename, sSchemaname & ".", "")
    							End If
    							'add tablename to precondition
    							sPreCond = sPreCond & "tableName=" & QUOTE & sTablename & QUOTE & "/>" & vbNewLine
    							sPreCond = sPreCond & "		</preConditions>" & vbNewLine
    
    						Case "DROP VIEW  "
    							sPreCond = "		<preConditions onFail=" & QUOTE & "MARK_RAN" & QUOTE & ">" & vbNewLine
    							sPreCond = sPreCond & "			<viewExists "
    							sTablename = Trim(Replace(sLine, "DROP VIEW", ""))
    							'see if there's a schema name
    							iPeriod = InStr(sTablename, ".")
    							If iPeriod>0 Then
    								sSchemaname = Left(sTablename, iPeriod - 1)
    								sPreCond = sPreCond & "schemaName=" & QUOTE & sSchemaname & QUOTE & " "
    								sTablename = Replace(sTablename, sSchemaname & ".", "")
    							End If
    							sPreCond = sPreCond & "viewName=" & QUOTE & sTablename & QUOTE & ">" & vbNewLine
    							sPreCond = sPreCond & "		</preConditions>" & vbNewLine
    
    						Case "DROP SEQUEN"
    							sPreCond = "		<preConditions onFail=" & QUOTE & "MARK_RAN" & QUOTE & ">" & vbNewLine
    							sPreCond = sPreCond & "			<sequenceExists "
    							'see if there's a schema name
    							sTablename = Trim(Replace(sLine, "DROP SEQUENCE", ""))
    							iPeriod = InStr(sTablename, ".")
    							If iPeriod>0 Then
    								sSchemaname = Left(sTablename, iPeriod - 1)
    								sPreCond = sPreCond & "schemaName=" & QUOTE & sSchemaname & QUOTE & " "
    								sTablename = Replace(sTablename, sSchemaname & ".", "")
    							End If
    							sPreCond = sPreCond & "sequenceName=" & QUOTE & sTablename & QUOTE & ">" & vbNewLine
    							sPreCond = sPreCond & "		</preConditions>" & vbNewLine
    
    					End Select 'Select Case Left(sLine, 10)
    
    				End If 'If Left(sLine, 5) = "DROP "
    
    				'add the line to the sSQL variable, this is the original DDL
    				sSQL = sSQL & sLine & vbNewLine
    
    				If Right(sLine, Len(sOutDelim)) = sOutDelim Then
    					'if there's a SQL delimiter, then we need to end the changeSet...
    					'add the changeset and the precondition to the output string
    					sOutSQL = sOutSQL & sChgSet & sPreCond
    					'wrap CDATA in case there are XML reserved words in the SQL
    					sOutSQL = sOutSQL & "		<sql"
    					If sOutDelim <> ";" Then sOutSQL = sOutSQL & " endDelimiter=" & QUOTE & sOutDelim & QUOTE 'semicolon is the default in Liquibase, anything else must be specified
    					sOutSQL = sOutSQL & "><![CDATA[" & vbNewLine
    					'add the original SQL statement
    					sOutSQL = sOutSQL & sSQL
    					sOutSQL = sOutSQL & "		 ]]></sql>" & vbNewLine	'closing tag
    					sOutSQL = sOutSQL & "	</changeSet>" & vbNewLine	'closing tag
    					'Need to create a new changeSet in case we have any more lines in the file
    					iChgLog = iChgLog + 1	'increment the change set id
    					bNewChgSet = True		'set the flag to create a new one
    					sSQL = ""				'clear out the SQL statement variable
    					sPreCond = ""			'clear out the precondition statement variable
    					sChgSet = ""			'clear out the change set variable
    				End If 'If Right(sLine, 1) = ";"
    			End If 'If Trim(sLine)<>"" Then
    
    			Print #3, sLine 'Write the adjusted line back out to the DDL file.
    
    		Wend 'While Not EOF(1)
    
    		'Only need to do this if we didn't end the last changeSet
    		If bNewChgSet = False Then
    			sOutSQL = sOutSQL & sChgSet & sPreCond
    			sOutSQL = sOutSQL & "		<sql"
    			If sOutDelim <> ";" Then sOutSQL = sOutSQL & " endDelimiter=" & QUOTE & sOutDelim & QUOTE 'semicolon is the default in Liquibase, anything else must be specified
    			sOutSQL = sOutSQL & "><![CDATA[" & vbNewLine
    			sOutSQL = sOutSQL & sSQL
    			sOutSQL = sOutSQL & "		 ]]></sql>" & vbNewLine
    			sOutSQL = sOutSQL & "	</changeSet>" & vbNewLine
    		End If 'If bNewChgSet = False
    
    		sOutSQL = sOutSQL & "</databaseChangeLog>" & vbNewLine	'closing tag
    
    		'output the entire SQL to the file
    		Print #2, sOutSQL
    
    		'Close both files
    		Close #1
    		Close #2
    		Close #3
    
    		'done
    		MsgBox "SQL and Liquibase changes are complete." & vbNewLine & "XML file created: " & filename, vbOkOnly
    
    		Exit Sub
    
    		BadFile:
    			MsgBox "Can't find file " & filename & ".  Please try again."
    			GoTo start_dialog
    
    	End Sub
    
    

Children
No Data