Shorten table names in temporary tables of the insert_into_target_tables function

[Ayoub-28]

Hi guys

When I'm trying insert data, by using the insert_into_target_tables function I get the following error:

raise exc.IdentifierError(
sqlalchemy.exc.IdentifierError: Identifier 'temp_a1893bc5_AllocationSeriesNotification_Manifest_ManifestItem' exceeds maximum length of 63 characters

}

I tried printing the source and target table to verify my model.

    print(data_model.source_tree)
    print("================")
    print(data_model.target_tree)

The output of these print statements is shown at the bottom of this issue.

Conclusion: Iam able to use the elevate_wo_prefix to get rid of the top level elements. But I'm unable to get rid of the sub-level elements.

For example, the EDSNBusinessDocumentHeader DOES work. But the Manifest DOES NOT work.

conf: Final[dict] = {
    "tables": {
        #"Manifest": { "transform": "elevate_wo_prefix" },
        "AllocationSeriesNotification": {
            "fields": {
                "EDSNBusinessDocumentHeader": { "transform": "elevate_wo_prefix" },
              
            }
        }
    }
}

I have tried the following combinations already.
EDSNBusinessDocumentHeader_Manifest
Manifest_ManifestItem

and I can also not find AllocationSeriesNotification_Manifest_ManifestItem or any other logical combination in the xsd. To reduce the length of the temp_a1893bc5_AllocationSeriesNotification_Manifest_ManifestItem. I have no issue creating tables, that works fine. These temp tables are only created by inserting the data. I would like to know how I can reduce those lengths.

EDSNBusinessDocumentHeader[1, 1]:
    ContentHash[0, 1]: string
    ConversationID[0, 1]: string
    CorrelationID[0, 1]: string
    CreationTimestamp[1, 1]: dateTime
    DocumentID[0, 1]: string
    ExpiresAt[0, 1]: dateTime
    MessageID[1, 1]: string
    ProcessTypeID[0, 1]: string
    RepeatedRequest[0, 1]: string
    TestRequest[0, 1]: string
    Destination[1, 1]:
        Receiver[1, 1]:
            Authority[0, 1]: string
            ContactTypeIdentifier[0, 1]: string
            ReceiverID[1, 1]: string
        Service[0, 1]:
            ServiceMethod[0, 1]: string
            ServiceName[0, 1]: string
    Manifest[0, 1]:
        NumberofItems[1, 1]: decimal
        ManifestItem[1, None]:
            Description[0, 1]: string
            LanguageCode[0, 1]: string
            MimeTypeQualifierCode[1, 1]: string
            UniformResourceIdentifier[1, 1]: string
    Source[1, 1]:
        Authority[0, 1]: string
        ContactTypeIdentifier[0, 1]: string
        SenderID[1, 1]: string
Allocation_Series[1, 1]:
    mRID[1, 1]: string
    product[1, 1]: string
    group_businessType[1, 1]: string
    run_mRID[1, 1]: string
    referenceRevisionRequest_mRID[0, 1]: string
    MarketEvaluationPoint[1, 1]:
        mRID[1, 1]: string
    MarketParticipant[0, 1]:
        mRID[1, 1]: string
        MarketRole[1, 1]:
            type[1, 1]: string
    DateAndOrTime[1, 1]:
        startDateTime[1, 1]: dateTime
        endDateTime[1, 1]: dateTime
    Detail_Series[1, None]:
        resolution[1, 1]: string
        Product[1, 1]:
            identification[1, 1]: string
            measureUnit[1, 1]: string
        FlowDirection[1, 1]:
            direction[1, 1]: string
        Point[1, None]:
            position[1, 1]: integer
            quantity[1, 1]: decimal
            origin[1, 1]: string
            validationStatus[0, 1]: string
            repairMethod[0, 1]: string
================
ContentHash[0, 1]: string
ConversationID[0, 1]: string
CorrelationID[0, 1]: string
CreationTimestamp[1, 1]: dateTime
DocumentID[0, 1]: string
ExpiresAt[0, 1]: dateTime
MessageID[1, 1]: string
ProcessTypeID[0, 1]: string
RepeatedRequest[0, 1]: string
TestRequest[0, 1]: string
Destination_Receiver_Authority[0, 1]: string
Destination_Receiver_ContactTypeIdentifier[0, 1]: string
Destination_Receiver_ReceiverID[1, 1]: string
Destination_Service_ServiceMethod[0, 1]: string
Destination_Service_ServiceName[0, 1]: string
Manifest_NumberofItems[0, 1]: decimal
Manifest_ManifestItem[0, None]:
    Description[0, 1]: string
    LanguageCode[0, 1]: string
    MimeTypeQualifierCode[1, 1]: string
    UniformResourceIdentifier[1, 1]: string
Source_Authority[0, 1]: string
Source_ContactTypeIdentifier[0, 1]: string
Source_SenderID[1, 1]: string
Allocation_Series_mRID[1, 1]: string
Allocation_Series_product[1, 1]: string
Allocation_Series_group_businessType[1, 1]: string
Allocation_Series_run_mRID[1, 1]: string
Allocation_Series_referenceRevisionRequest_mRID[0, 1]: string
Allocation_Series_MarketEvaluationPoint_mRID[1, 1]: string
Allocation_Series_MarketParticipant_mRID[0, 1]: string
Allocation_Series_MarketParticipant_MarketRole_type[0, 1]: string
Allocation_Series_DateAndOrTime_startDateTime[1, 1]: dateTime
Allocation_Series_DateAndOrTime_endDateTime[1, 1]: dateTime
Allocation_Series_Detail_Series[1, None]:
    resolution[1, 1]: string
    Product_identification[1, 1]: string
    Product_measureUnit[1, 1]: string
    FlowDirection_direction[1, 1]: string
    Point[1, None]:
        position[1, 1]: integer
        quantity[1, 1]: decimal
        origin[1, 1]: string
        validationStatus[0, 1]: string
        repairMethod[0, 1]: string

[martinv13]

Hi,

There are several things in your issue:

• In source_tree and target_tree, the top level is not shown, which is a bug that should be fixed
• following the explanation given in Generated table names are too long #24 you should be able also to remove the Manifest level, which would make it just under the 63 characters limits (your config is wrong, having a transform under a table and not a field)
• this character limit is a strong limitation (not sure it will work for other tables of your schema), so I suppose a config argument to rename tables would be useful anyways, I'll think about it
• finally, we should work on better validation/type hints/docs for the config dict

[sanski]

Thank you for your help.

I would like to address your 4 bullet points

1. Regarding the top level not shown, is there anything we can do on our end or collaborate in this github repo or do we simply need to wait and ignore it for now?

2. I see there was some commented out code in our example, sorry for the confusion. We used this config, do you see any issues with that?

{
"tables": {
"AllocationSeriesNotification": {
            "fields": {
                "EDSNBusinessDocumentHeader": { "transform": "elevate_wo_prefix" },
                "Allocation_Series": { "transform": "elevate_wo_prefix" },
                "Manifest": { "transform": "elevate_wo_prefix" },
            }
        },
}}

3. is there any update here, is there something we can help with? Or should we wait?
4. is there any update here, is there something we can help with? Or should we wait?

[martinv13]

I tried to address some of this points in #29:

1. Regarding the top level not shown, is there anything we can do on our end or collaborate in this github repo or do we simply need to wait and ignore it for now?

Easy fix, done.

2. I see there was some commented out code in our example, sorry for the confusion. We used this config, do you see any issues with that?

The issue here is that Manifest is not a child of AllocationSeriesNotification but rather EDSNBusinessDocumentHeader, so you need to add a config for table EDSNBusinessDocumentHeader under which you can put the config for the Manifest field.

#29 better checks the config and should raise an exception with the config you have provided.

3. is there any update here, is there something we can help with? Or should we wait?

This one is a bit more difficult and requires more testing as table names are used in different places.

In the short run if you are able to configure elevate_without_prefix for the long names it should alleviate the issue.

4. is there any update here, is there something we can help with? Or should we wait?

I tried to implement in #29 static typing for model config so that type checker e.g. IDEs could tell whether the config object, and more runtime checks, especially to check that tables and fields for which config is provided do exist. Would you be able to check whether #29 helps in this regards?

[sanski]

Thank you for making the effort. Our team has decided to go into another direction. So we will not be able to test this PR anymore.
Thank you for the help.

[cre-os]

#54 should now solve the initial issue of long identifiers exceeding the maximum allowed length for some databases