Issue with creating nested sub tables with DB Manager

Wappler General Bugs
1

Wappler Version : 7.1.2
Operating System : Win 11
Server Model: node
Database Type: PostgreSQL via docker
Hosting Type: DO/ Docker

Expected behavior

What do you think should happen?

Sub tables should be placed in correct position in heirachy

Actual behavior

What actually happens?

After inserting multiple sub tables, database manager starts to place tables incorrectly

How to reproduce

This is not a one off, i have been able to reproduce this in may ways by adding subtables in different places and under different names within the heirachy.

Firstly see this video to see scenario and lead up

Having made the video i started to think more about the issue and decided to investigate the knex files created.

The initial "content" add generated the following code:

exports.up = function(knex) {
  return knex.schema
    .raw(knex.client.constructor.name.startsWith('Client_PG') ? 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"' : 'SELECT 1')
    .createTable('book_chapter_section_content', async function (table) {
      table.uuid('content_id').primary().defaultTo(knex.client.constructor.name.startsWith('Client_PG') ? knex.raw('uuid_generate_v4()')
              : (knex.client.constructor.name.includes('MSSQL') ? knex.raw('NEWID()')
                : (knex.client.constructor.name.includes('MySQL') ? knex.raw('(UUID())') : null)));
      table.uuid('section_id');
      table.foreign('section_id').references('section_id').inTable('book_chapter_section').onUpdate('CASCADE').onDelete('CASCADE');
    })

};

exports.down = function(knex) {
  return knex.schema
    .dropTable('book_chapter_section_content')
};

It can clearly be seen in the 4th and last line that the target is correctly:

book_chapter_section_content

However on examining the final (incorrect) commit the code was:

exports.up = function(knex) {
  return knex.schema
    .raw(knex.client.constructor.name.startsWith('Client_PG') ? 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"' : 'SELECT 1')
    .createTable('subsection_content', async function (table) {
      table.uuid('content_id').primary().defaultTo(knex.client.constructor.name.startsWith('Client_PG') ? knex.raw('uuid_generate_v4()')
              : (knex.client.constructor.name.includes('MSSQL') ? knex.raw('NEWID()')
                : (knex.client.constructor.name.includes('MySQL') ? knex.raw('(UUID())') : null)));
      table.uuid('subsection_id');
      table.foreign('subsection_id').references('subsection_id').inTable('book_chapter_section_subsection').onUpdate('CASCADE').onDelete('CASCADE');
    })

};

exports.down = function(knex) {
  return knex.schema
    .dropTable('subsection_content')
};

The error can clearly be seen in that the fact that the parent table was itself a subtable of "section" has been misdiagnosed.

I was able to correct that issue by rolling back the change, and editing the two lines reading

"subsection_content"

to

"book_chapter_section_subsection_content"

Ironically the foreign key section appears correct

table.foreign('subsection_id').references('subsection_id').inTable('book_chapter_section_subsection').onUpdate('CASCADE').onDelete('CASCADE');

editing and quoting the full:

"book_chapter_section_subsection"

Seems to fix the issue.

I attach a zip file of the migrations leading up the the stage before the error so the structure can be rebuilt if necessary.

initial.zip (4.8 KB)

and a zip of the erroneous final migration

20250723120547_add more content.zip (615 Bytes)

2

Just when you thought you understand what happened:

After manual correction, i went on to add a content table to "paragraphs"

the code generated was (which worked)

exports.up = function(knex) {
  return knex.schema
    .raw(knex.client.constructor.name.startsWith('Client_PG') ? 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"' : 'SELECT 1')
    .createTable('subsection_paragraph_content', async function (table) {
      table.uuid('content_id').primary().defaultTo(knex.client.constructor.name.startsWith('Client_PG') ? knex.raw('uuid_generate_v4()')
              : (knex.client.constructor.name.includes('MSSQL') ? knex.raw('NEWID()')
                : (knex.client.constructor.name.includes('MySQL') ? knex.raw('(UUID())') : null)));
      table.uuid('paragraph_id');
      table.foreign('paragraph_id').references('paragraph_id').inTable('subsection_paragraph').onUpdate('CASCADE').onDelete('CASCADE');
    })

};

exports.down = function(knex) {
  return knex.schema
    .dropTable('subsection_paragraph_content')
};

so i assume that the full structural path is not needed, just the parent table so in above post could have been shortened to:

section_subsection_content

but ironically the table appears to be correctly placed in the structure

image
image445×770 15.7 KB

Also updating production worked correctly (with the amended code as above in place)

3

i will keep pumping out info re this issue and the "ideosyncasies" of the tables generated.
Guessing it is on hold for whoever is the "knexpert" on the team.

Step forward, started to populate tables with fields and look at data via pgAdmin 4

So here is an ERD of the current data

image
image1401×1134 105 KB

and here is the table list
image

My concern is that the paragraph tables are addressed differrently to the remaining tables omitting the prefix of "book_chapter_section"

At this stage things seem to work regardless but I am uncofortable that this "ideosyncracy" my come back later to bite me.

Can you confirm that referencing the parent table in the file name is suifficent to maintain functionality (which implies the full "path" is not actually necessary although i think visually is better)

EDIT
I have renamed these "incorrect" table names in pgAdmin to include the full "path". This seems, at this stage, to work correctly so i am more comfortable with the structure now.

image

4

I will have to check more in detail but because the database table names and also key names have length limit of max 63 chars, we have limit them to 32 to be able to generate valid key names without errors.

So when exceeding 32 chars because of all upper parents, we start over again. So that might be the issue here.

I still have to check in the code if we wrap on 32 or 63 chars. Will do when back from holiday.

6

no urgency, manual fix seems to have got me in the correct direction.
Guess you didnt plan for crazy people like me to nest to that level!
Enjoy your holiday and recharge your batteries.

7

A fix woud be nice bit If this is problematic to fix, now that we know why, it will be easy to work around.
For example i could have names the tables bk, ch, se, ss and pa which would keep it within the 32 char limit and just alias the table names for clarity in use.
With a theoretical max of 16 levels based on single letter table names i dont suppose it is going to be a huge problem.
As an interim intervention, i could amend the docs to highlight this issue so people are aware.

8

@George If i could ask one small question please.
Is my "fix" likely to cause problems in the future or should i recreate while I am in early design stages.
One word answer acceptable