Normal contributors should be able to unlink links they created themselves

[kumakyoo42]

When contributing to Tatoeba it's normal to make mistakes from time to time. Let's take sentence 6157455 as an example. This sentence is a question - the greek question mark looks like a semicolon. But assume, you overlooked this and translated the sentence to "You don't have school today.", which would be the correct translation, if the sentence ended with a dot. There are several things, that might happen:

1. "You don't have school today." is not yet in the database. In this case a completely new sentence is created. You could correct it, if you know the correct translation, because it doesn't have any other translation but the original greek sentence. If you realise at this time, you do not know the correct translation you are at a loss. You can't delete this sentence, because it has a translation and of course you can't unlink. All you can do is to ask someone to remove the sentence or to remove the link. Or (which I did in such situations), replace the sentence with 'xxx', which indirectly is a deletion, because this sentence is typically removed quite soon.
2. "You don't have school today." is in the database and you own it yourself. This is similar to the case above, but typically you can't correct the sentence, because it has already translations which would get wrong, when changing the sentence. Once again, all you can do is to ask for help or to replace this sentence by "xxx" accepting that some more translations will be broken, when this sentence is deleted.
3. "You don't have school today." is in the database and you do not own it. This is the worst case, because you can't change anything. Just beg for help.

When this happend to me the first time, I actually thought of leaving Tatoeba altogether, because I felt quite bad; it took two days until I came up with the "xxx" workaround which is OK, but still does not feel good.

All in all, it feels like you have to type some text and you need to ask for permission, whenever you need to press backspace.

Idea
Well, all these problems would vanish, if normal contributors were able to undo the linking which they did themselves. This is not to be confused with unlinking links of others (which, as far as I understand, advanced contributors can do). This of course needs some trust in the user. But undoing what you did yourself can't do any harm, does it?

[jiru]

I agree with the problem statement and your proposed solution.

In case 1 and 2, I’d like to suggest solving this problem by allowing to remove your own translation, giving it only has links made by you. Because I assume new users are likely to think of removing their sentence as a way to undo their mistake.

In case 3, manual unlink is the only option.

[kumakyoo42]

Because I assume new users are likely to think of removing their sentence as a way to undo their mistake.

Worse: They will "correct" the sentence to make it fit the one translation, overlooking that they break other translations while doing so. A warning might be a good idea in such cases.

I meanwhile had a look into the source code on unlinking. I think it's rather complicated to implement my idea, because right management does not know about translations. Might also slow down everything - not sure about this, though.

Another idea would be to add the unlinking (for normal contributors) to "contributions/of_user". There it's obvious, that one can only unlink the links made by oneself, and it might be much easier to implement it there.

[jiru]

I meanwhile had a look into the source code on unlinking. I think it's rather complicated to implement my idea, because right management does not know about translations. Might also slow down everything - not sure about this, though.

That’s correct. Links are not owned, we can only rely on logs, so you need to join the sentences table with the contributions table. But I don’t think this will significantly slow down things, because we do perform "get all link log information from that sentence" operations on every sentence page already, to display logs.

(There are some edge cases where this information is not accurate, such sentences deduplicated by Horus bot, or just missing entirely on some very old sentences, but I guess this won’t a problem for this particular ticket.)

Another idea would be to add the unlinking (for normal contributors) to "contributions/of_user". There it's obvious, that one can only unlink the links made by oneself, and it might be much easier to implement it there.

It is certainly easier to implement there, but that page is much harder to find out for newcomers, so I think ultimately this won’t be solving the issue of "newcomers have to beg for help when they mistakenly add a new translation". I can be a good start though!

[kumakyoo42]

I tried to find out how this works. It seems like the check for unlinking permission is not at a single place in the source code but at several places. While I think I got some of them (PermissionsComponent, CurrentUser, SentencesHelper), I'm still missing at least one I can't find. I managed to reveal the unlink icon to a normal contributor and make sure it can be clicked. But when clicking, the turning icon appears in an endless loop. Which means, the underlying http access process does stop somewhere (the beforeFilter in LinksControler is executed, but delete is not) . I tried to hunt down the place, but it looks like this is somewhere in the code of CakePHP. So here I am at a loss. Maybe you've got an idea, where I have to look?

[jiru]

Feel free to share your branch.

Maybe the piece you are missing is the auth actions? There is this file config/auth_actions.php defining what actions can be called depending on the user role. For example /audio/import is only accessible to admins, and /links/* (all actions of the Links controller) are only accessible to advanced contributors.

[kumakyoo42]

I need some more help. I created the following mysql query to retrieve information about who linked which translation of a sentence:

SELECT translation_id, user_id 
  FROM contributions c1 
 WHERE c1.type='link' AND c1.action='insert' AND c1.sentence_id=44
   AND NOT EXISTS
    (SELECT 1 
       FROM contributions c2 
      WHERE c2.type='link' AND c2.action='insert' AND c2.sentence_id=44 AND
            c2.translation_id=c1.translation_id AND c2.datetime>c1.datetime);

Of course, the two 44s have to be replaced by the correct sentence number. This needs to be added to getSentenceWith in Model/Table/SentencesTable.php, probably by putting the query itself into Model/Table/ContributionsTable.php and adding a call to it. Unfortunately I couldn't find out, how to add a NOT-EXISTS-subquery in CakePHP, seems to me like CakePHP doesn't support this, but maybe I overlooked something. (The NOT-EXISTS-subquery ensures that I get only the latest entry of each kind.)

I also thought about using getContributionsRelatedToSentence in Model/Table/ContributionsTable.php. At first glance this looks like it will retrieve the information needed (and some more), but meanwhile I think that's not true: If I link a sentence and then unlink it and then someone else links it again I shouldn't be able to unlink this sentence (as a normal user). With this approach I cannot recognize this, because I only get the contributions of the user itself not of other users.

Another approach would be to remove the NOT-EXISTS-subquery at all and to do the filtering in PHP after the query has been executed.

What do you think, which approach should I take? Or do you have another idea?

[jiru]

I couldn't find out, how to add a NOT-EXISTS-subquery in CakePHP

You should be able to achieve what you want with the query builder’s notExists() (scroll down). Here is another example of using a subquery.

Personally, when I don’t know how to do something with CakePHP, I just browse the source code directly, with e.g. grep -rw EXISTS vendor/cakephp/cakephp/src/. I find their code rather easy to understand and methods have useful comments.

I also thought about using getContributionsRelatedToSentence in Model/Table/ContributionsTable.php

Yes, this method basically gives you what you can see under the Logs section of a sentence page, so you’d need to extra processing on the result to extract the information you need.

With this approach I cannot recognize this, because I only get the contributions of the user itself not of other users.

Not sure I understand fully what you mean, but I think it is possible, although at the expense of having to do the filtering in PHP.

What do you think, which approach should I take? Or do you have another idea?

I think both approaches work, so feel free to do what you can/want. The NOT-EXISTS-subquery seems more efficient and concise, but I don’t think it really matters here because the performance gain is not substantial. What is important however is that your implementation is well-covered with unit tests, so that in the future it doesn’t break and one can possibly reimplement it if the need arises.

[kumakyoo42]

Meanwhile I managed to make CakePHP to produce this query:

SELECT translation_id AS `translation_id`, user_id AS `user_id` FROM contributions Contributions WHERE (NOT EXISTS (SELECT 1 FROM contributions Contributions WHERE (type = :c0 AND action = :c1 AND sentence_id = :c2 AND translation_id = Contributions.translation_id AND datetime > Contributions.datetime)) AND type = :c3 AND action = :c4 AND sentence_id = :c5)

It's almost correct, but it names both tables "Contributions"; they should have different names, so the subquery accesses the correct value. Do you know a way to set that name; I couldn't find anything in the docs.

[jiru]

Yes, I think you can use Query::from(). If you haven’t called it when building the query, CakePHP will automatically call it with these defaults before compiling the query into SQL.