Add Foreign Key awareness and much more

[tomrusteze]

Hello all,

I have forked this repo and implemented a lot of changes. With this PR, I hope to give something back to the community. I realise that some changes are rather big, but I still hope that others can benefit from them.

Most notably, I implemented some sort of foreign key awareness. In the creation procedure, you can select which columns should represent a foreign key record. These columns are then shown automatically when you are at the index or read page for records that refer to a foreign record. The complicated joins required for this information are generated automatically. Other changes include:

1. Comments from the SQL Database are shown as tooltips.
2. SQL errors are shown when updating, creating or deleting a record.
3. Make foreign key references are clickable
4. For a record that is referenced by other records, provide a link to these other records. (Visible at the bottom of a read page)
5. The update, delete and read pages now have buttons for update, deleting and reading.
6. SQL column names do not obey the same rules as PHP variable names, illegal characters are replaced with and underscore _.
7. SQL queries have the column names surround with ` to allow for all possible names (preventing weird SQL errors).
8. Optional (nullable) columns are properly supported.
9. The layout for read, update and create forms can be changed by changing a few lines (748-762) in generate.php.

There may be some minor changes that I forgot to mention.

Keep in mind that for the foreign key systems to work, you must select all tables that are participating in the relation.

[jan-vandenberg]

Wow! Thanks a lot for this PR!

Give me some time to look a bit more closely at the code before pulling it in.

[jan-vandenberg]

I have had some time to look at it. Since it is a large change I'd rather pull in everything all at once.

I have some questions (many more might follow). Lets go through them, as I see them:

• What does the "Visible in preview?" select button mean/do? (This is at the columns.php step).

It is not explained or intuitive?

• My main concern at the moment is that pagination seems to be broken. The First/Prev/Next/Last button bar seems to float OVER the table (it is completely stuck to the right) this might be a design choice, HOWEVER clicking it makes the app hang.

This is because the path is appended twice to the url and the domain is missing:

https://verbelco/cruddiy-pre-release/core/app/books-index.php/verbelco/cruddiy-pre-release/core/app/books-index.php?&pageno=2

The path should read: https://MYTESTDOMAIN.COM/cruddiy-pre-release/core/app/books-index.php?&pageno=2

I have not gotten around to testing the FK things yet.

[tomrusteze]

What does the "Visible in preview?" select button mean/do? (This is at the columns.php step).

Visible in preview refers to the new foreign key functionality, when a record refers to another record with an id. Previously we would display the id, now we display the columns that are selected with visible in preview. These columns will also be used in the update/create pages that refer to a foreign key.

So for example, in my case selecting the columns Name and Method for "visible in preview" will give the following index page:

My main concern at the moment is that pagination seems to be broken. The First/Prev/Next/Last button bar seems to float OVER the table (it is completely stuck to the right) this might be a design choice, HOWEVER clicking it makes the app hang.

I moved First/Prev/Next/Last button back to the bottom instead of floating. The reason that it hangs is because I made some changes to the way the domain is build up. My installation runs in a docker container, which means that it does not now what it's hostname is. So it may be running at example.com, but the PHP instance in the docker container has no way to discover that. Hence I added a domain variable to the config.php in which the user can configure this manually. So to fix this, you have to manually enter the address your server runs on. Locally this is typically someting like localhost.

Hope this helps!

[germain-italic]

@jan-vandenberg I plan to use Cruddiy for a new project, any news regarding this important PR?

[jan-vandenberg]

Hi! I have very little time to work on Cruddiy and this is a big commit!

I have tried it out and clicked around, and on the surface it looks OK, so I am willing to accept all these changes and release a new version.

Normally I would want to check every line of code, but alas I don't have the time.

Bugs may arise, but we shall see!

[germain-italic]

I will comment (and maybe try to fix) if I see something obviously broken, thanks

[germain-italic]

@tomrusteze could you explain more precisely and give more screenshot examples regarding "Visible in preview"?

I'd like to add some explaination text on the page for the users.

[tomrusteze]

With this update, we would show columns referenced in a different table. Because such a table can have many columns that are irrelevant for a short "preview". We use this selection procedure to let the user decide which columns should be displayed here.

For example: If you have a table users (with names and addresses) and a table orders (with order information and a reference to the user that has placed the order). When you are viewing the table orders. With this update, instead of seeing the id of the user, you will see columns of the user table. With the "visible in preview", you can select which columns are visible at this field (since showing all columns is usually too much)

I have also tried to explain it in a comment before, maybe reading both a few times will make sense. Let me know if there is something unclear. (I can't make a screenshot at the moment)

[germain-italic]

Thanks for clarifying. I may be able to create automated tests for that later on.

May i suggest to rename the columns like this:

And to add these instructions on top of the table:

Visibility in this table: Check the "as column" box to display the selected column in the list view of this table.

Visibility in related tables: Check the "as foreign key" box to display the selected column from related tables when it is referenced as foreign key in another table. Example: you want to hide the FK.id but you want to display the FK.name.

What do you guys think? @tomrusteze @jan-vandenberg

Also having all the columns ticked by default may produce unexpected results, should we pre-check only the primary key, and maybe a set of predefined keywords like "name", "reference", etc?

Finally I think it's possible (but not easy) to display the second columns only on tables which have at lease one column indeed referenced as a FK in another table. In @tomrusteze's example, there would be no second column on the Orders table.

In my screenshot example, which is poorly cropped, there wouldn't be a second column in the Products table.

[germain-italic]

This is where I am so far (unpublished yet):

[tomrusteze]

Looks good, the 'Show in FK' only being visible on tables that actually have a foreign key looks like a good addition. Naming was never my strongest skill, I think your explanation is bit clearer!

[germain-italic]

Great. It took me a while to figure things out. But as a non-native English speaker, I'm still unsure about the wording. Maybe the last one should be named "Show as FK".