N:N Relations: On Update problem

Scriptcase 8 Bugs
#1

Hi
i make an N:N Relation, which shows, wich Courses (like modules) are available in different Workshops. Currently, i have 2 Courses (30,31) in Workshop 4. Whenn i press the update / save button without changing any values, scriptcase do the following:


(pdo-mysql): delete from CourseWorkshopLnk where WorkshopId = 4   
(pdo-mysql): insert into CourseWorkshopLnk (id, CourseId, WorkshopId) values (NULL, '31', 4)   
(pdo-mysql): insert into CourseWorkshopLnk (id, CourseId, WorkshopId) values (NULL, '30', 4)

It delete’s EVERY record in the “middle” table and then insert every record again.

BUT i have other relations to this “middle” table. If a Course is available in a workshop, i have a table with course details, which describes the Room Number and Time for the specific Course in the workshop. Means, i have a Detailstable which has a relation to the id of CourseWorkshopLnk. So long, so good. But when scriptcase everytime delete and reinsert the records, my relation get lost. Why? How can i prevent scriptcase delete existing relations.
YES, of course, when the user removes a Course from the workshop, the details like time and place can also deleted. I defined this in the relation (ON DELETE => CASCADE).

I think this is a bug in scriptcase. Scriptcase should only delete or update things in the middle table when things changed.

Or what other’s think about that?

Best regards
Steve

#2

Steve,

Yes, I have seen this happen, and I agree it is a bug.

As an example of this, in our database, three tables: employees, timeclocks, and employee_timeclocks
We have many columns in the employees table, and many columns in the timeclocks table.
The employee_timeclocks table is primarily to indicate which timeclocks a particular employee can use.
It has a column for employee_id, timeclock_id, as you would expect for a N-N relationship, but it also has a few other columns: allow_facial_recognition, allow_fingerprint, etc.

When editing an employee to allow him to use a new timeclock, scriptcase deletes all of that employee’s employee_timeclock records, and rewrites them (rewriting only employee_id and timeclock_id, zeroing all other columns.).

Bad bug.

When you have to redesign your relational database structure to compensate for your development tool, it is not a good thing.

Dave

#3

More like a design flaw than a bug if you ask me.

Regards

#4

Good point

Dave

#5

Easy to solve…

Okay, if bug or not, it’s nice more when only do database changes when needed. So i did the follow.
And of course: I not can use the current N:N relation, because this code belongs scriptcase. I made a new Field “DoubleSelect” and do some code around this.

  • Save the starting values from the Double Select Field in "onLoad" Event in a variable, [startValues]={myDoubleSelect}
  • On Update do following: Put Start Values and Current Values of the Double Select Field in arrays: $_startValues=array_filter(explode(";",[startValues])); $_endValues=array_filter(explode(";",{myDoubleSelect}));
  • [B]// Which Values we have to insert $_insertValues=array_values(array_diff($_endValues,$_startValues)); // Which Values we have to delete $_deleteValues=array_values(array_diff($_startValues,$_endValues));[/B]
  • Do the insert SQL Statement. I use a foreach to make the insert commands.
  • Do the delete Statement like "DELETE from myTable WHERE leftkey={leftkey} AND rightkey IN ".implode(",",$_deleteValues).")";

I think that’s it. But inputs are welcome. Has anyone idea how to tell the scriptcase team to fix, sorry, make more beautiful this behavior?

#6

Hi
can someone tell me, if scriptcase team work on that, when it will be fixed or what we have to do for fix it?

I need N:N in many relations and my workaround above is not sexy like the implementation in scriptcase N:N could be… so PLEASE, fix it.

Thank’s for the answer.

Best regards
Steve

#7

[QUOTE=stephanw;34121]Okay, if bug or not, it’s nice more when only do database changes when needed. So i did the follow.
And of course: I not can use the current N:N relation, because this code belongs scriptcase. I made a new Field “DoubleSelect” and do some code around this.

  • Save the starting values from the Double Select Field in "onLoad" Event in a variable, [startValues]={myDoubleSelect}
  • On Update do following: Put Start Values and Current Values of the Double Select Field in arrays: $_startValues=array_filter(explode(";",[startValues])); $_endValues=array_filter(explode(";",{myDoubleSelect}));
  • [B]// Which Values we have to insert $_insertValues=array_values(array_diff($_endValues,$_startValues)); // Which Values we have to delete $_deleteValues=array_values(array_diff($_startValues,$_endValues));[/B]
  • Do the insert SQL Statement. I use a foreach to make the insert commands.
  • Do the delete Statement like "DELETE from myTable WHERE leftkey={leftkey} AND rightkey IN ".implode(",",$_deleteValues).")";

I think that’s it. But inputs are welcome. Has anyone idea how to tell the scriptcase team to fix, sorry, make more beautiful this behavior?[/QUOTE]

Yes, Stephen, I can write the php to do the N:N myself, but then why did I buy ScriptCase?

Dave

#8

[QUOTE=stephanw;34501]Hi
can someone tell me, if scriptcase team work on that, when it will be fixed or what we have to do for fix it?

I need N:N in many relations and my workaround above is not sexy like the implementation in scriptcase N:N could be… so PLEASE, fix it.

Thank’s for the answer.

Best regards
Steve[/QUOTE]

Send mail to bugs@scriptcase.net pointing to this thread.

#9

[QUOTE=stephanw;34121]Okay, if bug or not, it’s nice more when only do database changes when needed. So i did the follow.
And of course: I not can use the current N:N relation, because this code belongs scriptcase. I made a new Field “DoubleSelect” and do some code around this.

  • Save the starting values from the Double Select Field in "onLoad" Event in a variable, [startValues]={myDoubleSelect}
  • On Update do following: Put Start Values and Current Values of the Double Select Field in arrays: $_startValues=array_filter(explode(";",[startValues])); $_endValues=array_filter(explode(";",{myDoubleSelect}));
  • [B]// Which Values we have to insert $_insertValues=array_values(array_diff($_endValues,$_startValues)); // Which Values we have to delete $_deleteValues=array_values(array_diff($_startValues,$_endValues));[/B]
  • Do the insert SQL Statement. I use a foreach to make the insert commands.
  • Do the delete Statement like "DELETE from myTable WHERE leftkey={leftkey} AND rightkey IN ".implode(",",$_deleteValues).")";

I think that’s it. But inputs are welcome. Has anyone idea how to tell the scriptcase team to fix, sorry, make more beautiful this behavior?[/QUOTE]

Hello,

I’ve read your problem and I saw that it is delete mode ‘cascade’ data belonging to your table N: N, right? Due to “remove and insert” the data. But the principle that it is not a bug, but we will simulate the same way you use and check that will be necessary to make changes to the table insert mode N: N or not.

Thank you and sorry for any problem occurred.

#10

[QUOTE=Thomas Soares;34933]Hello,

I’ve read your problem and I saw that it is delete mode ‘cascade’ data belonging to your table N: N, right? Due to “remove and insert” the data. But the principle that it is not a bug, but we will simulate the same way you use and check that will be necessary to make changes to the table insert mode N: N or not.

Thank you and sorry for any problem occurred.[/QUOTE]

Hi
i’am not understand what this have to do with the “Delete Mode”. It also happens, when you have additional columns in the “middle table”, then these information will be deleted every time i add a record.
I send a demo project to scriptcase yesterday and hope, this bug will be fixed soon. Because delete every record from a table to reinsert the same records plus the changes again make no sense to me.

#11

[QUOTE=stephanw;34940]Hi
i’am not understand what this have to do with the “Delete Mode”. It also happens, when you have additional columns in the “middle table”, then these information will be deleted every time i add a record.
I send a demo project to scriptcase yesterday and hope, this bug will be fixed soon. Because delete every record from a table to reinsert the same records plus the changes again make no sense to me.[/QUOTE]

This problem was already informed our development team so that it can be resolved soon, but I can not say it any specific date.

Sorry for any problems, and thanks for listening.

#12

Thank’s for this information. I got the information, that the ticket is resolved allready. And now i’am waiting for the update in scriptcase? Because i need this fixed.

I hope, the changes will be included in the next update!

Best regards
Steve

#13

Two updates later, i am waiting still for this fix…

Please give me a date for deploy this fix, the the ticket shows me, it is fixed allready. My work should go on… but can’t until this bug is deployed.

Thank’s
Steve

#14

Please give a release date…

Today, another “big” release happens in scriptcase… and i see nothing in the changelog that this bug is fixed.

Please give a date for release the allready made fix of this bug, so i can plan my work on my project, which need this bug fixed!

Best regards and thank’s
Steve

#15

May i ask again? When this fix will be deployed? The ticket is closed since weeks, but the deployment stocks? What happen’s?

Best regards
Steve

#16

Please give us a date guys

🚀 Scriptcase 9.12 is now available! Check out the new features here 👉🚀 Next Monday (02/24), Scriptcase 9.12 will be released! Check out the new features before anyone else 👉