For pages based on views, allow inserts/updates to a different table

[DAF ID3000]

Dear Eugenio,
I use a lot of views I design in MariaDB as datasources to pages in Dadabik. This allows me to use SQL possibilities (dynamically calculated fields, subqueries, etc.) to enhance data I show in Dadabik grids.
But the dark side of this is that I loose updatable and insertable behaviors for these views and I must deal with two different pages in Dadabik, the first one based on my enhanced view to get all the informations I want to display in the grid, and the second one based on the source table to allow insert and update actions.
I managed with custom buttons from enhanced view page to link to the insert_form of the source table but the target page after inserting becomes the source table page and the user losts himself :-( So it's far from perfect...
My idea would be to add on the 'Pages based on view' definition a new field containing the target table (or updatable view) to update/insert to (by default it could be the same as 'view name' and we get the same behavior as now).
Upon the validation click on insert or update form, the only change in Dadabik behavior (for pages based on views) would be to replace the main view name by the new 'target table' field.
Of course, the reponsability of using the same fields names between the main view and the target table would be on the dadabik developper hands. He would have to show on insert/update form only fields which exist in both view and table to allow INSERT/UPDATE SQL orders to run successfully...

IMHO, this update would really give new exciting possibilities, even if your Dadabik framework is already one of my favourite tools ! ;-)

Thanks in advance to tell me what is your opinion.
KR
--
Gilles

 

[eugenio]

Hello Gilles,
the problem I see is that IF the view is not updatable, it's very hard (in most of the case, probably impossible) to map the view with the table automatically during insert/update (in fact, even MySQL can't do it automatically).

It's probably something you already tried, but maybe you can work on simplifying your views to make them updatable.

Best,

 

[DAF ID3000]

Hello Eugenio, and thank you for your quick answer.
I actually already got the updatable attribute back to True in many cases, but unfortunatly it's much more complicated with the insertable one: MySQL is not very flexible with that ;-)

I'm not really sure I understand your concern: just to be sure to make me well understood, if I take the example from my previous post, instead of running INSERT INTO `id3k_v_tickets`(...), Dadabik could construct a slightly different SQL INSERT INTO `id3k_tickets`(...) because it can read the new "target_table" field instead of using the "view_name" field as it does now (I suppose )
I can't see the automatic mapping you wrote about... which would be IMO actually nearly impossible as you said !

Thanks in advance for your answer, and have a nice day.

 

[eugenio]

Could you provide a (very simple, as simple as possible) example of a non-insertable VIEW that you are dealing with, also providing the schema of the related table?

 

[DAF ID3000]

Dear Eugenio, please find enclosed a simple example of the situations I often deal with: a test_cust table and a test_invoices table with a foreign key linked to test_cust. I want to show to the user a enhanced grid based on test_v_cust view but I would like to allow insertions/updates to the source table because view isn't updatable/insertable (for example the user could like to update customer name from this view), showing of course on the insert/update form only the fields that exist in the related test_cust table (in this example, that would be my reponsability to hide all fields but cust_code and cust_name).

In Dadabik, I would love to find in the settings page of my test_v_cust view based page, a new field to point to the table I want to update/insert to (table test_cust in my example).

You'll find attached snapshots to show data and tables/view definition, please feel free to ask for a SQL file if you want to rapidly reproduce my small test platform (I tried to attach it but text files are not allowed)

Thanks again for your time and your kindness.

 

[eugenio]

Hello,
I think that this approach would work in a very, very small subset of cases.
In the case you mentioned, for example, INSERT wouldn't probably make sense and it would be confusing for the user what they are inserting.

Maybe I am missing something, but I think that what you are really missing are links to the insert / edit form you want to link to your view.

For edit, you can exploit the "Show link to record?" parameter in form configurator.
For insert, you can add a button opening the insert form of the relevant table, as you already did, maybe in a popup so the user doesn't get lost.

Best,

 

[DAF ID3000]

Hi Eugenio,
I understand your position, even if my example was too simple to match reality: I deal with views which share 20-30 fields with related tables, with 2 or 3 enhanced fields... So I think users would not be confusing if I present to them a INSERT form hiding 3 fields between 30.

Never mind, I thank you for your time, I surely understand that it's not a priority for everyone even if it would have changed my life because I present flat tables in Dadabik on a very exceptional basis: I always have a few calculated fields to add to enhance the grid showed to users ! ;-)

Your idea to use popup window to show the INSERT form could be a part of solution. Am I supposed just to use an HTML "target" attribute with a classic "<a>" anchor, or do you think to somehting better ? maybe some JS code (I'm not an historical web developper, that's also why I appreciate so much dadabik which hides HTML from me ) ?

On the other hand, is there a way to catch SQL errors in Dadabik ? For instance, if I could catch SQL error on my previous example (table not insertable-into) and run some PHP code upon that event, I may be able to write a workaround to that situation.

Anyway, I thank you very much for your time.

 

[eugenio]

Hello,
if you use just the target attribute you can open the insert form in another window, if you want to open a popup you need javascript.

About doing something when an error is triggered during insert, you could look into the execute_db function in db_functions_pdo.php but it's a file that is replaced when you upgrade dadabik, not intended for being modified.

Best,