Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL Server Connector Generates Duplicate Refs and Enums #669

Open
asadcr opened this issue Dec 13, 2024 · 4 comments
Open

SQL Server Connector Generates Duplicate Refs and Enums #669

asadcr opened this issue Dec 13, 2024 · 4 comments
Labels
bug Something isn't working

Comments

@asadcr
Copy link

asadcr commented Dec 13, 2024

so i ran db2dbml mssql "Connection String" and

it has been failing with these errors

 ERROR: Error: Enum "dbo"."CC_Address_Info_AddressID_AddressFormatID" existed

Here's the full stack trace

Error: Error: Enum "dbo"."CC_Address_Info_AddressID_AddressFormatID" existed
    at generateDatabase (/Users/asadali/Downloads/dbml-master/packages/dbml-core/lib/parse/databaseGenerator.js:115:11)
    at Object.generateDbml (/Users/asadali/Downloads/dbml-master/packages/dbml-core/lib/import/index.js:17:58)
    at connectionHandler (/Users/asadali/Downloads/dbml-master/packages/dbml-cli/src/cli/connector.ts:17:26)
    at Object.<anonymous> (/Users/asadali/Downloads/dbml-master/packages/dbml-cli/src/cli/connector.ts:28:1)
    at Module._compile (node:internal/modules/cjs/loader:1376:14)
    at Module.m._compile (/Users/asadali/.nvm/versions/node/v20.11.0/lib/node_modules/ts-node/src/index.ts:1618:23)
    at Module._extensions..js (node:internal/modules/cjs/loader:1435:10)
    at Object.require.extensions.<computed> [as .ts] (/Users/asadali/.nvm/versions/node/v20.11.0/lib/node_modules/ts-node/src/index.ts:1621:12)
    at Module.load (node:internal/modules/cjs/loader:1207:32)
    at Function.Module._load (node:internal/modules/cjs/loader:1023:12)

So i debugged the whole code and turns out fetchSchemaJson generates duplicate entries here

so in enums section it generated this

{
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    },
    {
      "name": "CC_Address_Info_AddressID_AddressFormatID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000001"
        },
        {
          "name": "176029FA-D6D3-49E1-A48C-000000000002"
        }
      ]
    }

One entry was duplicated so many times.

Next Error

Error: Error: Enum value "6CC75003-9199-11D4-AF4C-000102C2C966" existed in enum "dbo"."CC_Contact_Lst_SclYearInfoID"
    at generateDatabase (/Users/asadali/Downloads/dbml-master/packages/dbml-core/lib/parse/databaseGenerator.js:115:11)
    at Object.generateDbml (/Users/asadali/Downloads/dbml-master/packages/dbml-core/lib/import/index.js:17:58)
    at connectionHandler (/Users/asadali/Downloads/dbml-master/packages/dbml-cli/src/cli/connector.ts:17:26)
    at Object.<anonymous> (/Users/asadali/Downloads/dbml-master/packages/dbml-cli/src/cli/connector.ts:28:1)
    at Module._compile (node:internal/modules/cjs/loader:1376:14)
    at Module.m._compile (/Users/asadali/.nvm/versions/node/v20.11.0/lib/node_modules/ts-node/src/index.ts:1618:23)
    at Module._extensions..js (node:internal/modules/cjs/loader:1435:10)
    at Object.require.extensions.<computed> [as .ts] (/Users/asadali/.nvm/versions/node/v20.11.0/lib/node_modules/ts-node/src/index.ts:1621:12)
    at Module.load (node:internal/modules/cjs/loader:1207:32)
    at Function.Module._load (node:internal/modules/cjs/loader:1023:12)

Same Case

{
      "name": "CC_Contact_Lst_SclYearInfoID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "6CC75003-9199-11D4-AF4C-000102C2C966"
        },
        {
          "name": "4EEAFF43-5A29-4366-93D5-E866EA8A6FB5"
        },
        {
          "name": "6CC75003-9199-11D4-AF4C-000102C2C966"
        },
        {
          "name": "4EEAFF43-5A29-4366-93D5-E866EA8A6FB5"
        }
      ]
    },
    {
      "name": "CC_Contact_Lst_SclYearInfoID",
      "schemaName": "dbo",
      "values": [
        {
          "name": "6CC75003-9199-11D4-AF4C-000102C2C966"
        },
        {
          "name": "4EEAFF43-5A29-4366-93D5-E866EA8A6FB5"
        },
        {
          "name": "6CC75003-9199-11D4-AF4C-000102C2C966"
        },
        {
          "name": "4EEAFF43-5A29-4366-93D5-E866EA8A6FB5"
        }
      ]
    },

Had to remove the duplicate entries to fix the Data.

Next Error has the same case

Error: Error: Reference with the same endpoints already exists: "dbo"."WebPortal_Security_User_Type_Lst"("User_Type_ID") references "dbo"."WebPortal_Security_User_Types"("User_Type_ID")
    at generateDatabase (/Users/asadali/Downloads/dbml-master/packages/dbml-core/lib/parse/databaseGenerator.js:115:11)
    at Object.generateDbml (/Users/asadali/Downloads/dbml-master/packages/dbml-core/lib/import/index.js:17:58)
    at connectionHandler (/Users/asadali/Downloads/dbml-master/packages/dbml-cli/src/cli/connector.ts:17:26)
    at Object.<anonymous> (/Users/asadali/Downloads/dbml-master/packages/dbml-cli/src/cli/connector.ts:28:1)
    at Module._compile (node:internal/modules/cjs/loader:1376:14)
    at Module.m._compile (/Users/asadali/.nvm/versions/node/v20.11.0/lib/node_modules/ts-node/src/index.ts:1618:23)
    at Module._extensions..js (node:internal/modules/cjs/loader:1435:10)
    at Object.require.extensions.<computed> [as .ts] (/Users/asadali/.nvm/versions/node/v20.11.0/lib/node_modules/ts-node/src/index.ts:1621:12)
    at Module.load (node:internal/modules/cjs/loader:1207:32)
    at Function.Module._load (node:internal/modules/cjs/loader:1023:12)

This was due to fetchSchemaJson is generating duplicate Refs

{
      "name": "FK_WebPortal_Security_User_Type_Lst_WebPortal_Security_User_Types1",
      "endpoints": [
        {
          "tableName": "WebPortal_Security_User_Type_Lst",
          "schemaName": "dbo",
          "fieldNames": [
            "User_Type_ID"
          ],
          "relation": "*"
        },
        {
          "tableName": "WebPortal_Security_User_Types",
          "schemaName": "dbo",
          "fieldNames": [
            "User_Type_ID"
          ],
          "relation": "1"
        }
      ],
      "onDelete": null,
      "onUpdate": null
    },
    {
      "name": "FK_WebPortal_Security_User_Type_Lst_WebPortal_Security_User_Types",
      "endpoints": [
        {
          "tableName": "WebPortal_Security_User_Type_Lst",
          "schemaName": "dbo",
          "fieldNames": [
            "User_Type_ID"
          ],
          "relation": "*"
        },
        {
          "tableName": "WebPortal_Security_User_Types",
          "schemaName": "dbo",
          "fieldNames": [
            "User_Type_ID"
          ],
          "relation": "1"
        }
      ],
      "onDelete": "CASCADE",
      "onUpdate": "CASCADE"
    }

Upon removing. it was able to generate the dbml successfully.

I am pretty sure a group-by command for uniqueness in enums and refs can fix this issue.
@asadcr asadcr changed the title SQL Server Connector Generates Duplicate Indexes SQL Server Connector Generates Duplicate Refs and Enums Dec 13, 2024
@huyphung1602
Copy link
Collaborator

Hi there, thank you for contacting us. We are currently looking into the issue and will get back to you later.

@huyleminh01
Copy link
Contributor

Hi @asadcr ,

I am pretty sure a group-by command for uniqueness in enums and refs can fix this issue.

This solution is possible, but we have to detect the root cause of the issue.

To do that, can you help provide us with your table structure (if possible)? You can send it via david.bui@holistics.io, and we ensure that your information is protected and used for the debugging purpose only.

Thanks.

@asadcr
Copy link
Author

asadcr commented Dec 13, 2024

@huyleminh01 i think the above data gives a lot of information. if you need any more info i will be happy to provide that. I have debugged already FYI so if u want some intermediate results in code, i can also provide

@huyleminh01
Copy link
Contributor

huyleminh01 commented Dec 15, 2024 •
edited
Loading

Hi @asadcr ,

Thank you for replying.

Also, it seems I reproduce your case.

Here is my sql:

CREATE TABLE AddressInfo (
    AddressID UNIQUEIDENTIFIER NOT NULL,
    AddressFormatID UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT CK_Address_Valid CHECK (
        AddressID IN ('176029FA-D6D3-49E1-A48C-000000000001', '176029FA-D6D3-49E1-A48C-000000000002') AND
        AddressFormatID IN ('176029FA-D6D3-49E1-A48C-000000000001', '176029FA-D6D3-49E1-A48C-000000000002')
    )
);
go

So supprisingly, I got the duplicated enum:

"enums": [
        {
            "name": "CK_Address_Valid",
            "schemaName": "dbo",
            "values": [
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000002"
                },
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000001"
                },
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000002"
                },
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000001"
                }
            ]
        },
        {
            "name": "CK_Address_Valid",
            "schemaName": "dbo",
            "values": [
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000002"
                },
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000001"
                },
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000002"
                },
                {
                    "name": "176029FA-D6D3-49E1-A48C-000000000001"
                }
            ]
        }
    ],

@huyleminh01 huyleminh01 added the bug Something isn't working label Dec 15, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@asadcr @huyphung1602 @huyleminh01