SQL duplicate table for inner structures

[mstmob]

Hi, I tried to convert a LTE RRC ASN1 spec to PSQL. The ASN1 definition contains inner/nested structures such as ENUMERATED with the same name multiple times. Example ASN1 spec (https://github.com/proj3rd/3gpp-specs/blob/master/36-series/36331/36331-f01.asn1#L2152-L2166):

SystemInformationBlockType1-v1320-IEs ::=   SEQUENCE {
    freqHoppingParametersDL-r13             SEQUENCE {
        mpdcch-pdsch-HoppingNB-r13              ENUMERATED {nb2, nb4}       OPTIONAL,    -- Need OR
        interval-DLHoppingConfigCommonModeA-r13 CHOICE {
            interval-FDD-r13                    ENUMERATED {int1, int2, int4, int8},
            interval-TDD-r13                    ENUMERATED {int1, int5, int10, int20}
        }                                                                   OPTIONAL,    -- Need OR
        interval-DLHoppingConfigCommonModeB-r13 CHOICE {
            interval-FDD-r13                    ENUMERATED {int2, int4, int8, int16},
            interval-TDD-r13                    ENUMERATED { int5, int10, int20, int40}
        }                                                                   OPTIONAL,    -- Need OR
        mpdcch-pdsch-HoppingOffset-r13          INTEGER (1..maxAvailNarrowBands-r13)    OPTIONAL     -- Need OR
    }                                                               OPTIONAL,    -- Cond Hopping
    nonCriticalExtension                        SystemInformationBlockType1-v1350-IEs                   OPTIONAL
}

Resulting SQL definition via asn1rs . ./spec.asn1 --convert-to sql:

CREATE TABLE IntervalFddR13IntervalFddR13 (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO IntervalFddR13IntervalFddR13 (id, name) VALUES
    (0, 'Int1'), 
    (1, 'Int2'), 
    (2, 'Int4'), 
    (3, 'Int8');

/* ... */

CREATE TABLE IntervalFddR13IntervalFddR13 (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO IntervalFddR13IntervalFddR13 (id, name) VALUES
    (0, 'Int2'), 
    (1, 'Int4'), 
    (2, 'Int8'), 
    (3, 'Int16');

Both interval-FDD-r13 ENUMERATED for instance are converted to a IntervalFddR13IntervalFddR13 SQL table. The second CREATE TABLE statement fails because the table already exists.

I am using the latest asn1rs master build (https://github.com/kellerkindt/asn1rs/tree/68afe21e68b8138414d3dec1f706bd5a7e86a95d)

[kellerkindt]

I guess you are using a simplified version of the asn1 definition. I change the name generation - which did actually generate wrong type names by duplicating the inner name only - which should resolve this issue: e8aa191000382a1fd16a9f0a6af5d9e9c4c24fa2

Could you try the most recent master?

[mstmob]

Thanks for your quick reply and fix! The create statements look fine but unfortunately the table names now exceed the default 63 character limitation of Postgres (https://stackoverflow.com/questions/27865770/how-long-can-postgresql-table-names-be). As a consequence the table names are cut off at 63 chars and I get duplicate table names for systeminformationblocktype1v1320iesfreqhoppingparametersdlr13in which should actually be something like SystemInformationBlockType1V1320IEsFreqHoppingParametersDlR13IntervalDlHoppingConfigCommonModeAr13IntervalTddR13...

You are right, and sorry for not mentioning, that I use a custom generated subset of the LTE RRC spec which just includes the BCCH-DL-SCH-Message PDU and excludes some ExtensionAdditions as well...

[kellerkindt]

Not sure if this will catch all cases, but do you mind trying d662e56ec969156dfddbd3064fba6f90186d9d8c and c7b74013efadef7d8b4c77fa5e0f377c5d97cacd? It is on a separate branch https://github.com/kellerkindt/asn1rs/tree/test-psql-typename-limit

[mstmob]

Thanks, and sorry for my late reply.. I tested both commits; at the moment the problem remains for the generation of the REFERENCES statement.

For example

CREATE TABLE SystemInformationBlockType1V1320IEsFreqHoppingParametersDlR13 (
    id SERIAL PRIMARY KEY,
    mpdcch_pdsch_hopping_nb_r13 INTEGER REFERENCES SystemInformationBlockType1V1320IEsFreqHoppingParametersDlR13MpdcchPdschHoppingNbR13(id) ON DELETE CASCADE ON UPDATE CASCADE,
    interval_dl_hopping_config_common_mode_a_r13 INTEGER REFERENCES SystemInformationBlockType1V1320IEsFreqHoppingParametersDlR13IntervalDlHoppingConfigCommonModeAr13(id) ON DELETE CASCADE ON UPDATE CASCADE,
    interval_dl_hopping_config_common_mode_b_r13 INTEGER REFERENCES SystemInformationBlockType1V1320IEsFreqHoppingParametersDlR13IntervalDlHoppingConfigCommonModeBr13(id) ON DELETE CASCADE ON UPDATE CASCADE,
    mpdcch_pdsch_hopping_offset_r13 SMALLINT
)

Error in query (7): ERROR: relation "systeminformationblocktype1v1320iesfreqhoppingparametersdlr13mp" does not exist
Warning: PDO::query(): SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "systeminformationblocktype1v1320iesfreqhoppingparametersdlr13" does not exist in /var/www/html/adminer.php on line 185

[kellerkindt]

Hey, got another commit to that should fix the issue with the reference statements 2d69ca1ca4fa0c165d107d2ecde616eaf5e4bedd You wanna give it a try? Its also on the test-psql-typename-limit-branch

[mstmob]

Hi, thanks! I tried the latest commit and the reference statements seem to be fixed now :+1:

However I get errors because of references to yet not created tables. I see that the tables should be created later but the order of the CREATE TABLE statements is wrong (The table dependencies are not resolved correctly...). I don't know if this is out of scope and related to this issue?