Include based_on_id in the weekly exports

[trang]

Our users sometimes ask statistics about original sentences but it's not easy for someone who doesn't have database access to help answer these questions because the based_on_id field is not an information that we export in our CSV files.

A recent example is mramosch on the Wall:

Is there an easy way to find out how many of the 503.559 german sentences are originals and how many are translations?

And after I provided the number of original German sentences:

Actually I would really like to see the sources for the translations of the remaining 420.000 sentences.

[agrodet]

Just to clarify the scope of this issue, are we talking only about extracting in the sense of exporting a file, or adding a possibility to search for them on Tatoeba?

[trang]

I'm personally satisfied if the based_on_id information is included in the weekly exports. If someone wants to implement something in Tatoeba, they are free to suggest. I don't think it would be super necessary as a Tatoeba feature but if someone can design a good feature, then I'm fine if it gets implemented.

[jiru]

As a user of Tatoeba, I can see how useful it would be to have this as a search filter. Original sentences typically feature broader vocabulary and more traits that are specific to the language. Let’s keep the scope of this present issue to the weekly exports, and use #2159 for the equivalent search filter.

[ftumsh]

Would I be correct in thinking that 'weekly reports' means the sql dump in weekly_exports.sql?
Also, should I only add the column to queries that use the sentences table or should I investigate joining the sentences table to other queries so as to retrieve the based_on_id?

[trang]

@ftumsh

Would I be correct in thinking that 'weekly reports' means the sql dump in weekly_exports.sql?

Yes, that's correct.

should I only add the column to queries that use the sentences table

If we add a column to an existing file, then the sentence_details.csv would be the best candidate. I don't think it would make much sense to add it to the sentences.csv (because this data is supposed to be the bare minimum data on sentences) or sentences_CC0.csv (because this data is about CC0),

However, adding a column to an existing CSV could be disrupting for those who have built some automated process around the modified file. It could also be additional unnecessary data to download for them.

The safest option would be to create a new file. I'm not sure how to name it though. But the file could just contain the columns id and based_on_id of the sentences table.

[ftumsh]

I have created a new file, sentences_based_on_id.csv, along with the bzip of it.
The file just contains the columns id and based_on_id of the sentences table.
I've pushed it to my fork.