Re: Patch file for table drop on import
От | John McCawley |
---|---|
Тема | Re: Patch file for table drop on import |
Дата | |
Msg-id | 3D0779A9.2030607@worleyco.com обсуждение исходный текст |
Ответ на | Re: Patch file for table drop on import ("Dave Page" <dpage@vale-housing.co.uk>) |
Список | 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 по дате отправления:
Следующее
От: "Dave Page"Дата:
Сообщение: Re: [pgadmin-support] "Invisible" relations after migrating Access