Re: Patch file for table drop on import - Mailing list pgadmin-hackers
| From | John McCawley |
|---|---|
| Subject | Re: Patch file for table drop on import |
| Date | |
| Msg-id | 3D0779A9.2030607@worleyco.com Whole thread Raw |
| In response to | Re: Patch file for table drop on import ("Dave Page" <dpage@vale-housing.co.uk>) |
| List | pgadmin-hackers |
>An attachment to the list would be great thanks.
>
>
Attached is a patch for frmWizard.frm in the pgMigration project. It
implements the following:
1) Adds a checkbox to the wizard for "Drop any existing destination tables"
2) If this box is checked, it uses the schema object to determine the
existance of the imported table in the destination Postgres DB, and adds
a drop table statement before the create table statement
3) Minor modification to the existing MS Access autonumber code to allow
me to reuse it for MS SQL
3) If the source DB type is MS SQL, it runs a query on the source
database to find the autonumber field, if found it sets variables to
allow the existing autonumber code to add the sequence
4) If the "drop existing" checkbox is checked, it will drop existing
sequence names that conflict with the ones that are being created
5) Added text to the status window to notify user of dropped tables, and
created and dropped sequences.
NOTE: Due to an apparent name length limitation in the schema object, I
was only able to compare up to 31 characters of the destination sequence
name. If the first 31 characters match, I assume it to have the same name
NOTE: The SQL Server autonumber query has only been tested on SQL Server
7.0. I am unable to test its validity in 6.5 or SQL Server 2000.
John
Index: plugins/migration/frmWizard.frm
===================================================================
RCS file: /disk1/cvsroot/pgadmin2/plugins/migration/frmWizard.frm,v
retrieving revision 1.12
diff -u -r1.12 frmWizard.frm
--- plugins/migration/frmWizard.frm 2002/06/05 15:28:18 1.12
+++ plugins/migration/frmWizard.frm 2002/06/12 14:35:07
@@ -1,18 +1,18 @@
VERSION 5.00
-Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
+Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "comdlg32.ocx"
Object = "{BDC217C8-ED16-11CD-956C-0000C04E4C0A}#1.1#0"; "tabctl32.ocx"
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "mscomctl.ocx"
Begin VB.Form frmWizard
BorderStyle = 1 'Fixed Single
Caption = "Database Migration Wizard"
- ClientHeight = 4320
+ ClientHeight = 4515
ClientLeft = 2325
ClientTop = 1455
ClientWidth = 6885
Icon = "frmWizard.frx":0000
LinkTopic = "Form1"
MaxButton = 0 'False
- ScaleHeight = 4320
+ ScaleHeight = 4515
ScaleWidth = 6885
Begin VB.PictureBox picStrip
BorderStyle = 0 'None
@@ -32,13 +32,13 @@
Left = 540
TabIndex = 18
ToolTipText = "Edit the data Type Map."
- Top = 3960
+ Top = 4080
Visible = 0 'False
Width = 1230
End
Begin MSComDlg.CommonDialog CommonDialog1
Left = 2205
- Top = 3915
+ Top = 4035
_ExtentX = 847
_ExtentY = 847
_Version = 393216
@@ -50,7 +50,7 @@
Left = 3300
TabIndex = 17
ToolTipText = "Move back a step."
- Top = 3960
+ Top = 4080
Width = 1140
End
Begin VB.CommandButton cmdNext
@@ -59,7 +59,7 @@
Left = 4500
TabIndex = 16
ToolTipText = "Proceed to the next step."
- Top = 3960
+ Top = 4080
Width = 1140
End
Begin VB.CommandButton cmdOK
@@ -70,19 +70,19 @@
Left = 5700
TabIndex = 21
ToolTipText = "Accept the completed migration"
- Top = 3960
+ Top = 4080
Visible = 0 'False
Width = 1140
End
Begin TabDlg.SSTab tabWizard
- Height = 3840
+ Height = 3960
Left = 540
TabIndex = 0
TabStop = 0 'False
Top = 90
Width = 6300
_ExtentX = 11113
- _ExtentY = 6773
+ _ExtentY = 6985
_Version = 393216
Tabs = 7
TabsPerRow = 7
@@ -117,69 +117,61 @@
Tab(0).Control(11).Enabled= 0 'False
Tab(0).Control(12)= "fraSQLServer"
Tab(0).Control(12).Enabled= 0 'False
- Tab(0).ControlCount= 13
+ Tab(0).Control(13)= "chkDropExistingTables"
+ Tab(0).Control(13).Enabled= 0 'False
+ Tab(0).ControlCount= 14
TabCaption(1) = " "
TabPicture(1) = "frmWizard.frx":187D
Tab(1).ControlEnabled= 0 'False
- Tab(1).Control(0)= "Label2(0)"
- Tab(1).Control(0).Enabled= 0 'False
- Tab(1).Control(1)= "lstDatabase"
- Tab(1).Control(1).Enabled= 0 'False
+ Tab(1).Control(0)= "lstDatabase"
+ Tab(1).Control(1)= "Label2(0)"
Tab(1).ControlCount= 2
TabCaption(2) = " "
TabPicture(2) = "frmWizard.frx":1899
Tab(2).ControlEnabled= 0 'False
- Tab(2).Control(0)= "Label2(1)"
- Tab(2).Control(0).Enabled= 0 'False
- Tab(2).Control(1)= "lstNamespace"
- Tab(2).Control(1).Enabled= 0 'False
+ Tab(2).Control(0)= "lstNamespace"
+ Tab(2).Control(1)= "Label2(1)"
Tab(2).ControlCount= 2
TabCaption(3) = " "
TabPicture(3) = "frmWizard.frx":18B5
Tab(3).ControlEnabled= 0 'False
- Tab(3).Control(0)= "Label1(1)"
- Tab(3).Control(0).Enabled= 0 'False
- Tab(3).Control(1)= "cmdDeselect(0)"
- Tab(3).Control(1).Enabled= 0 'False
- Tab(3).Control(2)= "cmdSelect(0)"
- Tab(3).Control(2).Enabled= 0 'False
- Tab(3).Control(3)= "lstTables"
- Tab(3).Control(3).Enabled= 0 'False
+ Tab(3).Control(0)= "lstTables"
+ Tab(3).Control(1)= "cmdSelect(0)"
+ Tab(3).Control(2)= "cmdDeselect(0)"
+ Tab(3).Control(3)= "Label1(1)"
Tab(3).ControlCount= 4
TabCaption(4) = " "
TabPicture(4) = "frmWizard.frx":18D1
Tab(4).ControlEnabled= 0 'False
- Tab(4).Control(0)= "Label1(9)"
- Tab(4).Control(0).Enabled= 0 'False
- Tab(4).Control(1)= "cmdDeselect(1)"
- Tab(4).Control(1).Enabled= 0 'False
- Tab(4).Control(2)= "cmdSelect(1)"
- Tab(4).Control(2).Enabled= 0 'False
- Tab(4).Control(3)= "lstData"
- Tab(4).Control(3).Enabled= 0 'False
+ Tab(4).Control(0)= "lstData"
+ Tab(4).Control(1)= "cmdSelect(1)"
+ Tab(4).Control(2)= "cmdDeselect(1)"
+ Tab(4).Control(3)= "Label1(9)"
Tab(4).ControlCount= 4
TabCaption(5) = " "
TabPicture(5) = "frmWizard.frx":18ED
Tab(5).ControlEnabled= 0 'False
- Tab(5).Control(0)= "Label1(8)"
- Tab(5).Control(0).Enabled= 0 'False
- Tab(5).Control(1)= "Label1(10)"
- Tab(5).Control(1).Enabled= 0 'False
+ Tab(5).Control(0)= "lstForeignKeys"
+ Tab(5).Control(1)= "cmdSelect(2)"
Tab(5).Control(2)= "cmdDeselect(2)"
- Tab(5).Control(2).Enabled= 0 'False
- Tab(5).Control(3)= "cmdSelect(2)"
- Tab(5).Control(3).Enabled= 0 'False
- Tab(5).Control(4)= "lstForeignKeys"
- Tab(5).Control(4).Enabled= 0 'False
+ Tab(5).Control(3)= "Label1(10)"
+ Tab(5).Control(4)= "Label1(8)"
Tab(5).ControlCount= 5
TabCaption(6) = " "
TabPicture(6) = "frmWizard.frx":1909
Tab(6).ControlEnabled= 0 'False
- Tab(6).Control(0)= "pbStatus"
- Tab(6).Control(0).Enabled= 0 'False
- Tab(6).Control(1)= "txtStatus"
- Tab(6).Control(1).Enabled= 0 'False
+ Tab(6).Control(0)= "txtStatus"
+ Tab(6).Control(1)= "pbStatus"
Tab(6).ControlCount= 2
+ Begin VB.CheckBox chkDropExistingTables
+ Caption = "Drop Any Existing Destination Tables "
+ Height = 240
+ Left = 660
+ TabIndex = 60
+ ToolTipText = "Select this to convert index names to lower case."
+ Top = 3660
+ Width = 4380
+ End
Begin VB.ListBox lstTables
Height = 3435
Left = -73470
@@ -724,7 +716,7 @@
Left = 5700
TabIndex = 20
ToolTipText = "Start the database migration."
- Top = 3960
+ Top = 4080
Width = 1140
End
End
@@ -1232,6 +1224,14 @@
Dim auto_increment_table As String
Dim auto_increment_query As String
Dim auto_increment_rs As New Recordset
+Dim auto_increment_sequencename As String
+
+'Johnm - for checking for the existance of a destination table to drop
+Dim bDrop As Boolean
+'Temp variables to reduce if/then code bloat on table drop
+Dim szDropNamespace As String
+Dim szDropTablename As String
+Dim szDropTableConcatenation As String
StartMsg "Migrating database..."
lVer = svr.dbVersion.VersionNum
@@ -1245,6 +1245,7 @@
If chkLCaseTables.Value = 1 Then svr.LogEvent "Table names being converted to lowercase.", etMiniDebug
If chkLCaseColumns.Value = 1 Then svr.LogEvent "Column names being converted to lowercase.", etMiniDebug
If chkLCaseIndexes.Value = 1 Then svr.LogEvent "Index names being converted to lowercase.", etMiniDebug
+ If chkDropExistingTables.Value = 1 Then svr.LogEvent "Index names being converted to lowercase.", etMiniDebug
'Begin a transaction.
svr.Databases(szDatabase).Execute "BEGIN"
@@ -1260,18 +1261,51 @@
szTemp1 = "" 'Added 1/30/2001 Rod Childers Variables not being set to ""
szTemp2 = ""
+ szQryStr = ""
+
+ '****
+ 'Johnm - If checked by the user, drop the matching destination table
+ If chkDropExistingTables = 1 Then
+ bDrop = False
+
+ If chkLCaseTables.Value = 0 Then
+ szDropTablename = lstData.List(X)
+ Else
+ szDropTablename = LCase(lstData.List(X))
+ End If
+
+ If lVer >= 7.3 Then
+ szDropNamespace = szNamespace
+ szDropTableConcatenation = szNamespace & "." & szDropTablename
+ Else
+ szDropNamespace = "public"
+ szDropTableConcatenation = szDropTablename
+ End If
+
+ 'Set drop boolean based on whether the table was found in the destination DB
+ bDrop = svr.Databases(szDatabase).Namespaces(szDropNamespace).Tables.Exists(szDropTablename)
+
+ 'Only attempt to drop the tables if they were found in the destination postgres database
+ If bDrop = True Then
+ txtStatus.Text = txtStatus.Text & "Dropping table " & szDropTablename & vbCrLf
+ szQryStr = szQryStr & "DROP TABLE " & szDropTableConcatenation & "; "
+ End If
+ End If
+ 'Johnm - table dropping code
+ '****
+
loFlag = False
If lVer >= 7.3 Then
If chkLCaseTables.Value = 0 Then
- szQryStr = "CREATE TABLE " & fmtID(szNamespace) & "." & fmtID(lstData.List(X)) & " ( "
+ szQryStr = szQryStr & "CREATE TABLE " & fmtID(szNamespace) & "." & fmtID(lstData.List(X)) & " ( "
Else
- szQryStr = "CREATE TABLE " & fmtID(szNamespace) & "." & fmtID(LCase(lstData.List(X))) & " ( "
+ szQryStr = szQryStr & "CREATE TABLE " & fmtID(szNamespace) & "." & fmtID(LCase(lstData.List(X))) & " ( "
End If
Else
If chkLCaseTables.Value = 0 Then
- szQryStr = "CREATE TABLE " & fmtID(lstData.List(X)) & " ( "
+ szQryStr = szQryStr & "CREATE TABLE " & fmtID(lstData.List(X)) & " ( "
Else
- szQryStr = "CREATE TABLE " & fmtID(LCase(lstData.List(X))) & " ( "
+ szQryStr = szQryStr & "CREATE TABLE " & fmtID(LCase(lstData.List(X))) & " ( "
End If
End If
@@ -1288,6 +1322,41 @@
auto_increment_table = LCase(lstData.List(X))
End If
auto_increment_query = ""
+
+ '****
+ 'Johnm - MSSQL Autonumber code NOTE: using some of the variables defined for the Access autonumber code
+ 'NOTE: currently only tested on MSSQL Server 7.0/NT4
+ If InStr(1, cnLocal.ConnectionString, "PROVIDER=SQLOLEDB") = 0 Then
+ 'The following query should pull a record that contains the autonumber column if one exists for the table
+ auto_increment_query = "select (syscolumns.status & 128) as isidentity ," & _
+ " sysobjects.name as tablename, syscolumns.name as columnname " & _
+ " From " & _
+ " sysobjects inner join syscolumns on sysobjects.id = syscolumns.id " & _
+ " inner join systypes on syscolumns.xtype = systypes.xtype " & _
+ " LEFT OUTER JOIN sysindexkeys on sysindexkeys.id = sysobjects.id and sysindexkeys.colid = syscolumns.colid "
&_
+ " LEFT OUTER JOIN sysindexes on sysindexkeys.indid = sysindexes.indid AND sysindexkeys.id = sysindexes.id " &
_
+ " where sysobjects.type = 'U' AND (syscolumns.status & 128) = 128 AND " & _
+ " sysobjects.name = '" & auto_increment_table & "'"
+
+ ' Perform the query
+ auto_increment_rs.Open auto_increment_query, cnLocal, 3, 1
+ 'If a record was found, then there is an autonumber for this table
+ If auto_increment_rs.EOF = False Then
+ 'Johnm - setting boolean and variables to utilize the existing MSDASQL code
+ auto_increment_on = 1
+ If chkLCaseColumns.Value = 0 Then
+ auto_increment_field_name = auto_increment_rs("columnname")
+ Else
+ auto_increment_field_name = LCase(auto_increment_rs("columnname"))
+ End If
+ End If
+ If auto_increment_rs.State <> adStateClosed Then auto_increment_rs.Close
+ Set auto_increment_rs = Nothing
+ auto_increment_query = ""
+ End If
+ 'Johnm - End of MSSQL Autonumber code
+ '****
+
' Only do this if it's an access database
If InStr(1, cnLocal.ConnectionString, "MSDASQL") = 0 Then
For Y = 0 To catLocal.Tables(lstData.List(X)).Columns.Count - 1
@@ -1305,6 +1374,7 @@
End If
End If
Next Y
+ End If 'Johnm - Moved end if here so that both blocks of autoincrement code can use this section
If auto_increment_on = 1 Then
auto_increment_query = "SELECT MAX(" & szQuoteChar & auto_increment_field_name & szQuoteChar & ") AS RECCOUNT
FROM" & szQuoteChar & auto_increment_table & szQuoteChar
@@ -1323,17 +1393,40 @@
' Destroy what I created
If auto_increment_rs.State <> adStateClosed Then auto_increment_rs.Close
Set auto_increment_rs = Nothing
+
+ 'Johnm - assuming that if we are to drop conflicting tables, we should also drop conflicting sequences
+ If chkDropExistingTables = 1 Then
+ auto_increment_sequencename = Left(auto_increment_table & "_" & auto_increment_field_name & "_key", 31)
+ If svr.Databases(szDatabase).Namespaces(szDropNamespace).Sequences.Exists(auto_increment_sequencename)
Then
+ ' Set the PostgreSQL query
+ If lVer >= 7.3 Then
+ auto_increment_query = "DROP SEQUENCE " & fmtID(szNamespace) & "." & fmtID(auto_increment_table &
"_"& auto_increment_field_name & "_key") & ";"
+ Else
+ auto_increment_query = "DROP SEQUENCE " & fmtID(auto_increment_table & "_" &
auto_increment_field_name& "_key") & ";"
+ End If
+ 'Johnm - added status update to notify of sequence creation
+ txtStatus.Text = txtStatus.Text & "Dropping sequence " & auto_increment_table & "_" &
auto_increment_field_name& "_key" & vbCrLf
+ Else
+ 'Johnm - added status update to notify of sequence creation
+ 'txtStatus.Text = txtStatus.Text & "Sequence " & auto_increment_sequencename & " not found " & vbCrLf
+ End If
+ End If
+ 'Johnm - added status update to notify of sequence creation
+ txtStatus.Text = txtStatus.Text & "Creating sequence " & auto_increment_table & "_" &
auto_increment_field_name& "_key" & vbCrLf
+
' Set the PostgreSQL query
If lVer >= 7.3 Then
- auto_increment_query = "CREATE SEQUENCE " & fmtID(szNamespace) & "." & fmtID(auto_increment_table & "_" &
auto_increment_field_name& "_key") & " START " & auto_increment_count
+ auto_increment_query = auto_increment_query & "CREATE SEQUENCE " & fmtID(szNamespace) & "." &
fmtID(auto_increment_table& "_" & auto_increment_field_name & "_key") & " START " & auto_increment_count
Else
- auto_increment_query = "CREATE SEQUENCE " & fmtID(auto_increment_table & "_" & auto_increment_field_name &
"_key")& " START " & auto_increment_count
+ auto_increment_query = auto_increment_query & "CREATE SEQUENCE " & fmtID(auto_increment_table & "_" &
auto_increment_field_name& "_key") & " START " & auto_increment_count
End If
Else
auto_increment_query = ""
End If
- End If
+
+ 'Johnm - Former location of "if access" if
+
' End AutoIncrement Fix
' 07/02/01 - Matthew MacSuga - Put columns in original order fix
pgadmin-hackers by date: