Cascade delete

[tosfera]

Hey guys,

Not everyone will know what a cascade delete is, if you don't then please don't continue reading.

So, since I'm working with a mysql service, I was wondering if it was possible to create a cascade delete in LUA. In my eyes, it isn't possible. but I'd like to hear some reactions from you guys.

Thanks

[tosfera]

Realy, I mean... no one knows what a cascade delete is? ._. Maybe if I explain it, people might get an idea if it is possible or not. PLEASE NOTE THAT, SQL KNOWLEDGE IS REQUIRED TO UNDERSTAND THIS!

Whenever you got a database containing tables with PK it is no problem. When you're starting to use FK's for relations it will be a little more tricky. I got a database which is build out of relations. I can manualy remove a few rows if someone deletes his character, but if the character has 5 vehicle's, 10 buildings, 35 friends, and like 100 extra rows. I would have to do 4 queries ( 4 tables ) to get his information deleted. To write them all out, it's a pain. Cascade Delete has been made for problems like this, whenever you delete the table 'Characters', cascade will go and search for any tables which have a relationship with the table 'Characters'. If so, he will delete all the records which are bound to that one character. This will continue until all the information from that 1 character is gone. After the information is gone, he will delete the character row itself.

Now, is there any possible way to create this in lua? Does the mysql module support a cascade delete..?

[csiguusz]

Why don't you search it on google, if nobody wants/can help you here? I did a search and it seems to be possible with innoDB engine. one, two

[Renkon]

Realy, I mean... no one knows what a cascade delete is? ._. Maybe if I explain it, people might get an idea if it is possible or not. PLEASE NOTE THAT, SQL KNOWLEDGE IS REQUIRED TO UNDERSTAND THIS!

Whenever you got a database containing tables with PK it is no problem. When you're starting to use FK's for relations it will be a little more tricky. I got a database which is build out of relations. I can manualy remove a few rows if someone deletes his character, but if the character has 5 vehicle's, 10 buildings, 35 friends, and like 100 extra rows. I would have to do 4 queries ( 4 tables ) to get his information deleted. To write them all out, it's a pain. Cascade Delete has been made for problems like this, whenever you delete the table 'Characters', cascade will go and search for any tables which have a relationship with the table 'Characters'. If so, he will delete all the records which are bound to that one character. This will continue until all the information from that 1 character is gone. After the information is gone, he will delete the character row itself.

Now, is there any possible way to create this in lua? Does the mysql module support a cascade delete..?

As far as my SQL knowledge goes with SQL Server 2k8,what you might be able to do is to check 1:n relations and try using DELETE FROM's with their IDs.

After thats done you can proceed to remove the one that has got the main identity and the primary key... 'cause as you know, you cannot remove the character, otherwise foreign keys would throw an exception..

In other words, hope this works (but not what you looking for) you can use WHERE a.id_blabla = w/e you need, or you c an use a stored procedure that would make what you want much more smaller and easier.

[tosfera]

Why don't you search it on google, if nobody wants/can help you here? I did a search and it seems to be possible with innoDB engine. one, two

You don't say? But I don't want to do it in the SQL. I want to do it in my db platform made in lua.

@Renkon; its the sql side, I'd rather do it in lua. But I'll just leave it, seems impossible.

[Renkon]

Probably not because LUA is not 'integrated' with SQL, hence no special functions. You are just able to use functions made by developers.

[Spajk]

I guess you could make an sql trigger to delete those related rows.

[tosfera]

I guess you could make an sql trigger to delete those related rows.

Ya, it's sql sided. Didn't want to do it there, I should just see if SQL allows you to receive some more info about it. Might post it if I get it working. On a dark and drunk evening or so.