A question regarding record integrity

[Manos Pappas]

Hello,

Please consider the following scenario: we have two tables, table A and table B.

Table B has among its fields an integer field that is a lookup to a field on table A.

It is a scenario like a master (A) - details (B).

When the user deletes a record from table B that refers to a record in table A, there is no problem with the record integrity. When the user however deletes a record from table A, the records on table B that had lookup connection to the record on table A are left in an orphaned or inconsistent state.

How can one prevent this from happening?

On MSSQL server when I design such tables (outside of Jam), one can create a foreign key relationship for the field in table B (the one that lookup in table A) and create a rule that would forbid the deletion of the record in table A unless there are no more references to it on the records in table B.

Any ideas are welcomed.

Many thanks in advance.

Best regards,

Manos Pappas

[Danijel Kaurin]

Hi Manos.

In which Jam.py version do you want to solve this?

Regards

Danijel Kaurin

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/fead2686-3c1e-4394-b94b-2c959f2f2a3en%40googlegroups.com.

[Dean D. Babic]

Hi, 

one can create FK from Jam v5. Normally, the Soft Delete would prevent orphaned records.

This should be taken into account with ie. Reports.

Regards

D.

[Manos Pappas]

Hello,

Sorry for digging up this old post but I have a question about v7.

Since v7 does not have foreign key support, how one can handle the exception when the user tries to delete a record that is referenced in another item/table?

I am trying to handle this from the client using the on_before_delete() event and although it fails to delete the record (as it should, since it is referenced elsewhere), it throws an exception that I cannot manage.

I am using the following client code:

function on_before_delete(item) {

    /* check if the selected field is referenced in the fieldjournal table */

   

    /* fieldjournal test */

    var fjtest = task.fieldjournal.copy();

    fjtest.set_where({field_gid: item.field_uuid.value});

    fjtest.open();

    if (!fjtest.rec_count) {

        item.alert_error('Cannot delete field: A journal record exists for this field!');

        fjtest.close();

        return;

    }

else {

fjtest.delete();

fjtest.apply();

}

fjtest.close();

}

The above code throws an unhandled exception from the server side which is undesirable.

What I am trying to do is to is check if the field UUID exists on the fieldjournal table and if it does not THEN proceed with the deletion of the field record.

Many thanks in advance for any pointers on how to do this properly.

Best regards,

Manos

[r rad]

Hi,

For this scenario, delete_records will be to manage return "false" from on_before_delete client_event, to cancel try to delete!

But, and for rule, this is for server code. But, above principle is the same. 

[r rad]

A few years ago, my claims that foreign keys, ie. the integrity of the primary key is an asset of civilization. 

Jam.py development has gone the other way. And me with him. :)

[Danijel Kaurin]

Hi Manos.

Bellow is example of code from my V7 that 100% works:

function on_before_delete(item) {

let selections = item.selections,

         rs = item.task.posiljke.copy({handlers: false});

         rs.set_where({id__in:selections});

         rs.open();

               

         try {

             while (!rs.eof()) {

             rs.delete();

                  rs.apply();

             }

             item.selections = [];

             item.refresh();

             item.alert_success('Successful delete!');

         }

         catch (error) {

         item.warning(error);

         }

 }

In V7, apply() method for this event handler needs to be inside of the while loop. V5 is different...

Regards

Danijel

To view this discussion visit https://groups.google.com/d/msgid/jam-py/1261d792-f670-49e8-9ed1-bb65293aa1f0n%40googlegroups.com.

[r rad]

Hi and sorry me.

I didn't understand that the database is "old" with the FK installed, 

but what to do if there is no FK in the database, 

ie the pod database built with Jam.py v7. 

How then to manage this problem? 

I hope the answer is no, checking for references around. 

That's the trigger, only it's not in the database, but in the code.

And that further means keeping the database to yourself for too long, perhaps. 

In fact, the basic question is whether your recommendation is to keep FK as before 

but only in db, and then to manage exceptions in the above way?

[Manos Pappas]

Hi,

I am not sure I understand correctly what you propose.

Did you mean that I have to change the line fjtest.delete() with fjtest.delete_record()?

Thank you,

Manos

[Manos Pappas]

Hi Danijel,

Thank you very much for your proposal, it helped me understand what I have to do.

Best regards,

Manos

[Manos Pappas]

Hi,

No need to be sorry, I was not clear enough :)

The database is indeed an "old" v5 Jam.py database project but I've followed the steps for migration to v7, so FKs are gone.

The problem is that my initial code - before doing what Danijel suggested - always raised an exception (that I could not manage) from the server side, without actually having any specific code handling it.

Danijel code helped me understand better.

I agree with your points above, I cannot understand why v7 dropped foreign keys but that's the way it is and we have to follow it :)

Thanks,

Manos

[r rad]

First, I apologize for my English, it is very bad. That's why I use Google translate :) 

In fact, there is something behind it, which is not talked about here. 

Now I'm only talking about the V7. 

When you create a table with ref. key, (without db support), you actually have to take care of it yourself. 

But it won't be that easy, because you don't have access to SQL (standard development). 

Although that wasn't the goal, deleted fields get the job done here, in a bit of a weird way! 

If both basic and ref. table (lookup) have defined deleted fields, in fact here it is important that it must have a ref. table, 

what happens when we delete a record from the ref. tables, although it has one or more ref. records in the base table? 

Well, nothing. There is no foreign key constraint to prevent this and the record will be marked as deleted via the deleted field. 

And what about the reference. Same nothing. It survives. Of course, the SQL query for lookup does not have 

(or at least I think it should) the additional condition "!deleted". So even though the records are marked with 

deleted, ref. survive. Multiple profits especially when you need to restructure the base, etc. 

I think that even in this mode, there is no marking of children's records. It is not the best solution because apart from having orphan records, 

we have to access child records through parent records - mandatory. 

This was the plan. What is left of this - I don't know. I just hate looking at V7 code, because I find the lack of foreign keys a serious handicap. 

Sorry for the long text, but I had to... 

PS. By the way, that thing with deleted fields is one of the easiest ways to remove some records, but still have them there.

This is for those who write accounting programs. :)

Regards,  Radosav.

[Drazen Babic]

I was about to skip this conversation but pls let me to try. 

I think this has nothing to do with integrity or a Foreign Keys. But a choice to use a Soft Delete or not.

In v5, the deletion of the record with a Foreign Key would be EXACTLY as in v7:

ERROR - FOREIGN KEY constraint failed
..

  File "/home/dba/Downloads/py3.12v5/lib/python3.12/site-packages/jam/execute.py", line 30, in execute
    cursor.execute(command)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

In v7:
ERROR - FOREIGN KEY constraint failed
Traceback (most recent call last):
 ...
  File "/home/dba/Downloads/py3.12v7/lib/python3.12/site-packages/jam/tree.py", line 208, in execute_query
    cursor.execute(query)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

Now, lets use a Soft Delete and delete the record. No traceback obviously on table A.

One table B, lets delete record which HAS a FK in Table A. So no deleted record!

We get this:

Exception: Can't delete the record. It's being used.

This is how the record is protected FOR referential integrity so thre are NO orphaned records!

BUT, delete now the record in table B with a deleted record A from before! The Soft delete kicks in, correct? No traceback.

So both records were Soft deleted. No orphaned records. No code to achieve this.

If we need to delete unreferenced records, then we need to look at Server module and some code.

Here is a prove in the above with Albums and Artists (del is the Deleted flag)::
CREATE TABLE "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL, "DEL" INTEGER,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE "artists"
(
    [ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(120)
, "DEL" INTEGER)

[r rad]

Tank you on answer!

What is syntax with "DEL" tag, is it deleted field?

Drazen, here is not point of view for behavior with FK in db, it is clear.

But, as good boy, if I make db with Jam.py V7, what about system behaviour?

[Drazen Babic]

I did not "tank" the answer Radosav ;) It is very difficult to understand you.
Google translate today is rubbish, and if you do not use AI to translate, well,
then no Chinese translation would exist for example.

Lookup delete is completely different than a FK, here is a proof!

Do we see the error? Lets be blunt, it took me 10 minutes to modify the Jam core to get this message:
Can't delete the record. It's being used in asdasdd, table t2

So, did anyone contributed to the Jam core in V5 for this message?  This is a simple change, a child can do it.

But, the community should invest time in it. Not me. I invested too much. 

For FK the error is SQL error, from the SQLite3 or MS SQL etc. Jam presents the similar error message.

So again, nothing has changed in v7 other than:
Foreign Key support is dropped at v7. This means if there are any Foreign Keys created at v5, there will be no option to manage it at v7.

Simple as that. 
Jam does not need to be a DB administration tool. Plenty of options for that.

But to publicly say:

"I just hate looking at V7 code, because I find the lack of foreign keys a serious handicap."

is way over the top Radosav. Hate is a strong word as well as serious.

It puts off users from Jam, don't you think?

D.

[r rad]

Drazen 

You should have been a politicians, not an IT guy. 

This is one of your posts where you write and say nothing. At least as far as I'm concerned. 

I've been dealing with this stuff since '91 and no one has ever told me that it's hard to explain to me. 

But I'm getting old... 

You know very well that I have been a fan of Jam.py since 2015. 

You also know very well that I think you helped Jam.py go in a direction it shouldn't have gone. 

You also know that I really appreciate your effort and work in all that Jam.py. 

Here I have expressed my opinions and concerns regarding the use of this excellent piece of software. 

Nothing more and nothing less. That's how it is with open source. Take it or leave it. And I wouldn't want 

to leave ... Not yet. 

What I asked doesn't seem to be answered here. Okay, that's fine. 

And as for using Jam.py as a UI for "normal" databases and that's fine, it just occurred to me now, 

I can build and maintain the database schema from V5, and the UI from V7. 

When I said I hated looking at V7, I said it because I've tried to mess things up several times and failed, 

so any attempt to work with the internals of Jam.py is more difficult than with other software of similar

purpose and status. 

Well, until now, while I just kept quiet and followed this forum, I don't see that users were eager to use and 

talk about Jam.py, now you say that I'm turning users away with my story. And that's good, I'll try not to 

post too much in the future, so that everyone is satisfied. 

And me, I will use V5, despite the opinion that it is outdated, etc ... 

Stay healthy and you and all other Jam.py users. 

Ps It's not a problem not to know and write on the forums. 

      The problem is not knowing how much you don't know. 

      Just comparing Jam.py to Access is ridiculous. 

      But my problem is that I am here and nobody else's.

Regards, 

   Radosav

[Drazen Babic]

I think some of your concerns are more about design decisions than something I directly control. 
I mainly handle releases and patches, so I may not be the best person to answer those in depth. 

I do hear your point about the Jam core code being harder to work with in V7. If you can report with what exactly you are having issues, I can try to clarify what I can or we can ask Andrew Yushev for a more detailed explanation.

Only he can answer "why". I can not.

[r rad]

It's long past time that I used to worry about anything. 

Mostly thanks. 

Good luck! 

Radosav

PS. I will try to make a demo, which should confirm or disprove 

my thoughts about V7 as well as prove the capabilities of Jam.py 

on standard problems in web database app design.

[Manos Pappas]

Hello,

Based on Danijel's suggestion, I've modified the code as following:

function on_before_delete(item) {

    /* check if the selected field is referenced in either fieldgrowth, fieldplant, or fieldjournal */

    var canbedeleted = true;

    var degrowth = '';

    var deplant = '';

    var dejournal = '';

    var result;

    /* fieldjournal test */

    var fjtest = task.fieldjournal.copy({handlers: false});

    fjtest.set_where({field_gid: item.field_uuid.value});

    fjtest.open();

    if (fjtest.rec_count) {

        canbedeleted = false; // cannot delete

        dejournal = 'Journal work';

    }

    fjtest.close();

    /* fieldplant test */

    var fptest = task.fieldplant.copy({handlers: false});

    fptest.set_where({fieldid: item.id.value});

    fptest.open();

    if (fptest.rec_count) {

        canbedeleted = false; // cannot delete

        deplant = 'Plantation';

    }

    fptest.close();

    /* fieldgrowth test */

    var fgtest = task.fieldgrowth.copy({handlers: false});

    fgtest.set_where({fieldid: item.id.value});

    fgtest.open();

    if (fgtest.rec_count) {

        canbedeleted = false;  // cannot delete

        degrowth = 'Growth';

    }

    fgtest.close();

    /* if the field is not found in any of these three tables, delete it */

    if (canbedeleted) {

        var fld = task.field.copy({handlers: false});

        fld.set_where({id: item.id.value});

        fld.open();

        fld.delete();

        fld.apply();

        fld.close();

        result = true;

    }

    else

    {

        item.alert_error('Cannot delete. The field is referenced in:\n' + dejournal + '\n' + deplant + '\n' + degrowth);

        item.warning('Cannot delete. The field is referenced in:\n' + dejournal + '\n' + deplant + '\n' + degrowth);

        item.abort('Cannot delete. The field is referenced in:\n' + dejournal + '\n' + deplant + '\n' + degrowth);

        result = false;

    }

    return result;

}

The code works but it does not abort the function when it cannot delete the record; the framework keeps displaying the dialog asking the user whether it wants to delete the record.

What I want to do is when the field record cannot be deleted to abort the on_before_delete() completely and don't try to ask the user to delete the record or not.

How can I do this?

Many thanks in advance,

Manos

[Drazen Babic]

Hi Manos,

The video is showing how Jam fixes a FK error with Soft Delete.

I attached the DB called "t", the albums has FK to artists.

In this DB there are also two tables T1 and T2. No FK. The T2 has a lookup to T1.
And looks like the Soft Delete does NOT actually

let us delete the field if it is referenced elsewhere. In v5, this was working.

However, v7 by the look is enforcing integrity. In v5, we would get exactly the opposite - orphaned record.

So simply put, we need to delete the referenced record first in v7. It does not matter if Soft Key is used for 

a simple lookup. It does matter for a FK! 

Are you sure that you have a FK issue and not

a simple lookup field? 

Thank

[r rad]

Hi,

Dude, I don't think you followed Danielo's instructions to the end. 

You must use a "try .. catch ... block" on your "delete and apply call" 

to be able to abort the delete attempt.

Radosav

[Drazen Babic]

The proper way is not a Client Module, but the Server. 
Below will "Soft delete" all Artists when Album is deleted.

def cascade_artist_children(delta=None, connection=None):

artists = task.artists.copy(handlers=False)

artists.set_where(id=delta.artist.value)

artists.open()

for i in range(artists.record_count()):

artists.rec_no = i

artists.delete()

artists.apply()

print("Delete applied successfully!")

def on_before_apply_record(item, delta, params, connection):

if delta and hasattr(delta, 'rec_deleted') and delta.rec_deleted():

cascade_artist_children(delta, connection)

delta._lookup_refs = {}

[Manos Pappas]

Hi Drazen,

I will probably go this way, I also did not think that handling it via the client was the proper way.

Even with a try/catch paradigm like Danijel posted, it is still not possible to abort the Delete dialog for the record.

Letting the user try to delete it and perform an Exception from within the server, might be more suitable.

M.

[r rad]

Hello, boys

Cascading deletion of related records should be done by FK on the database automatically if it is defined as such. 

Do not forget that cascading deletion is not the solution for every problem. 

Here, Manos seems to be trying to prevent deletion if there are references to a given record, which is another type of FK integrity.

This is about intercepting exceptions thrown by the database, due to foreign key integrity violations. 

This has to be done by the user himself because Jam.py does not deal with FK exceptions since V7. 

I sincerely hope that he does not deal with only those exceptions anymore.

That db exception must be intercepted, whether on the client or the server, it's all the same.

If Daniel showed how to do it, I don't see what the problem is.

In the end, everyone should do as they think. 

That's how I learned these things too, from my mistakes.

Of course it's not the best idea, but it's better than nothing.

Radosav

[Danijel Kaurin]

Hi Manos.

To implement delete record with client code, you need to override delete-btn function like this:

function on_view_form_created(item) {item.view_form.find("#delete-btn").off('click.task').on('click', function(e) {
        e.preventDefault();

item.question('Do you want to delete selected record?',

function() {

            on_before_delete(item);

}

);

    });

}

For me, it's easier to maintain client code than server code for delete action. Also, you can easily implement multiple row deletion (which can be a little bit slow with client execution).

Regards

--

You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/jam-py/1b58fe09-c1a9-4ff3-a7fd-06b5fc0b5c7dn%40googlegroups.com.

[Manos Pappas]

Hi Danijel,

Thank you very much for your suggestion, indeed one needs to override the delete button action.

I ended up dealing with it via server code (I do not have the need for multiple selection delete) and will post the code next, so that it can be used by others too.

M.

[r rad]

Hi Daniel, 

One questions about code above:

e.PreventDefault() will be stop events, how will be called delete() and apply() actions?

Radosav

[Manos Pappas]

Hello everyone,

First of all, let me express my sincere thanks for helping me solve this issue.

All your contributions helped me go to the solution I've implemented.

Although I am using Jam v5 since 2020, I am still learning and v7 is even bigger challenge for me but it is worth the trouble.

What I understood is that upon the user firing a delete record request, the v7 framework does indeed check for consistency if the record is referenced elsewhere.

As correctly said, how we handle these is the issue.

My scenario is the following: I have a table fields and three more tables: fieldjournal, fieldgrowth and fieldplant.

A field record can be referenced in the other three tables, so what I want to handle is the exception if the user tries to delete a field record that has references elsewhere and be able to tell her where.

I ended up doing it via server code on the fields table as follows:

def on_before_apply_record(item, delta, params, connection):

    for d in delta:

        # if the user tries to delete the selected field records and the field is referenced elsewhere, abort deletion with exception

        if d.rec_deleted():

            # check if field is referenced in fieldjournal

            fjournal = d.task.fieldjournal.copy(handlers=False)

            fjournal.set_fields('field_gid')

            fjournal.set_where(field_gid=d.field_uuid.value)

            fjournal.open()

            if fjournal.rec_count:

                raise Exception('Cannot delete field: Journal records exist for this field!')

                fjournal.close()

           

            # check if field is referenced in fieldgrowth

            fgrowth = d.task.fieldgrowth.copy(handlers=False)

            fgrowth.set_fields('fieldid')

            fgrowth.set_where(fieldid=d.id.value)

            fgrowth.open()

            if fgrowth.rec_count:

                raise Exception('Cannot delete field: Growth records exist for this field')

                fgrowth.close()

            # check if field is referenced in fieldplant

            fplant = d.task.fieldplant.copy(handlers=False)

            fplant.set_fields('fieldid')

            fplant.set_where(fieldid=d.id.value)

            fplant.open()

            if fplant.rec_count:

                raise Exception('Cannot delete field: Plantation records exist for this field')

                fplant.close()

Simply checking for the existance sequentially and raising exception for each case, properly aborts the delete operation.

If all three checks are cleared (i.e no foreign references), the default record deletion function executes.

Hope that helps.

Best regards,

Manos