Update l.d.o syncing for d.o update of project data

I think its fine to filter those out.

Closed #2139775: Commerce economic not listed on l.d.o as duplicate of this one... Now with so much time passed, this is getting to be a problem.

@SebCorbin: I looked at the query before/after your patch (http://drupalcode.org/project/l10n_server.git/commitdiff/583b6eae7c8109f...) and looks like our db query user does not have access to the taxonomy_index table. There is no term_node table anymore (I assume due to D7 upgrade), so no way to get data from there either. Trying to get access to that table now :)

Opened #2148907: Give read access to localize_ro user to file_managed.

Also closed #2151225: The project d4os is not listed on localize.drupal.org as duplicate.

Ok, the permission is now granted on taxonomy_index. I did some more digging and improved sebcorbin's patch. The project usage sync will not work as the timestamp max query runs into a filesort and mysql resets the connection on us for that, heh :D So I commented that out. That is a sacrifice if at least the release synching would work but that does not work either :/ Looks like the release files are not in the files table anymore or some other table is not tracking them anymore... That needs more looking into.

I also provisionally put in the Oct 31 last release timestamp to the last sync so it will pick up all releases even though our last erroneous attempt knocked that number over on the live site :)

Unfortunately I am out of steam for tonight but will look into this on Monday.

Just got an IRC report from @csakiistvan that Drupal 7.24 with l10n_update will not download proper translations either since 7.24 is not on l.d.o yet :/

Also marked #2153015: Drupal core 7.24 not listed on l.d.o as a duplicate.

Did more debugging. Figured out we cannot gain results from our join on [files] since that is a D6 leftover table. D7 has file_managed. Doh. Updated my issue to ask for access to that too: https://drupal.org/comment/8293461#comment-8293461

Pinged @nnewton again about https://drupal.org/comment/8293461#comment-8293461

I share your frustrations. I spent several hours yesterday trying to untangle this only to be more depressed about the situation :( The extent to which we need changes is way bigger than I thought. I thought I fixed several queries and only needed the file_managed table to be accessed, and then figured out yesterday that the tables we have access stopped collecting data on October 31st and now data like project shortnames or dev/stable designations are at entirely different places. Eg data we used to get from the project_release_nodes and project_projects tables are now in field_data_field_project_machine_name and field_data_field_release_build_type. :/

Earlier I also found out that the data for project usage reached a treshold where our queries would resort to filesort, which would bring down the database.

So I think if we want to get this fixed sooner than later, we need to give up most syncing functionality that localize.drupal.org used to have (eg. ordering projects by usage or having real project titles). Which is pretty sad. I mean that you would find some (old) projects under their human titles and new ones under machine names only is quite odd. But the data provided publicly by drupal.org is not sufficient to get this type of information with use of any reasonable amount of resources.

The offered solution from the drupal.org upgrade team was/is a single TSV file at https://drupal.org/files/releases.tsv which provides all releases ever made. This can be used to create the release info on l.d.o, with some assumptions about where the files are put by the packager (there are no filenames in this dump). It cannot be used to collect real project names (project node titles), project usage data, and it cannot be used to tell if a project / release was unpublished (eg. security unsupported). These are things the current code does.

At least from a stop-gap perspective the https://drupal.org/files/releases.tsv data could be used to save raw project info (machine name basically) to l.d.o's db, so people could search for their projects with that and it can be used to set up minimal info about releases as well. We can keep using old project usage data for a while until people start complaining about that too.... Not having that is probably not that big of a problem ATM than not having any new projects or releases available.

Since we will not get to where we wanted to be with asking for db table access one by one, I think everybody can help move this forward who wants to dedicate some time and know PHP :) The task would be to update the logic in http://drupalcode.org/project/l10n_server.git/blob/refs/heads/6.x-3.x:/c... based on data from https://drupal.org/files/releases.tsv and gut out all the stuff that cannot be done with this data (ie. project status tracking, real project titles, tracking of project usage, tracking of file hashes).

Who wants to help?

Thanks sebcorbin for jumping on this. At least this will get us a minimum level to move forward :) Some things to fix before we deploy:

1. +++ b/connectors/l10n_drupal_rest/l10n_drupal_rest.rest.inc
   @@ -0,0 +1,106 @@
   +  $local_projects = l10n_server_get_projects(array('all' => TRUE));
   ...
   +    unset($local_projects[$project]);
   ...
   +  if (count($local_projects)) {
   +    // If we still have local projects lingering, those are not anymore
   +    // available with non-dev releases on drupal.org, so we should turn off
   +    // their listing in our database.
   +    $disabled_projects = array_keys($local_projects);
   +    db_query('UPDATE {l10n_server_project} SET status = 0 WHERE uri IN (' . db_placeholders($disabled_projects, 'varchar') . ')', $disabled_projects);
   +  }
   

   I think this is the kind of stuff that we cannot do anymore, at least I would not parse this multi-megabyte file in its entirety, sounds like a recipe for problems... :/ Also if we don't remove/disable anything in this code, since we don't really know the project status, the rollout of this would be more safe :D

2. +++ b/connectors/l10n_drupal_rest/l10n_drupal_rest.rest.inc
   @@ -0,0 +1,106 @@
   +  $destination_path = file_directory_path() . '/releases.tsv';
   +  $url = variable_get('l10n_drupal_rest_refresh_url', 'https://drupal.org/files/releases.tsv');
   +  // This will take some time, so we need to increase timeout.
   +  $response = drupal_http_request($url, array(), 'GET', NULL, 3, 300);
   +  if ($response->code == 200) {
   +    // Save as temporary file
   +    file_save_data($response->data, $destination_path, FILE_EXISTS_REPLACE);
   +    _l10n_drupal_rest_read_tsv($destination_path, $before, $projects,
   +      $releases);
   +  }
   

   I think downloading the whole file and parsing the whole thing would be problematic. IMHO we should read the tsv line by line and stop after the last sync timestamp minus a day. That should mean we only read a few hundred lines at most (except the first run now that we need to pick up all our missing stuff).

3. +++ b/connectors/l10n_drupal_rest/l10n_drupal_rest.rest.inc
   @@ -0,0 +1,106 @@
   +      // New project, not recorded before.
   +      db_query("INSERT INTO {l10n_server_project} (uri, title, last_parsed, home_link, connector_module, status) VALUES ('%s', '%s', %d, '%s', '%s', %d)", $project, $project, time(), 'http://drupal.org/project/' . $project, $connector_name, 1);
   

   Add a @todo that titles need to be grabbed from somewhere *later*. Not blocking this patch at all.

4. +++ b/connectors/l10n_drupal_rest/l10n_drupal_rest.rest.inc
   @@ -0,0 +1,106 @@
   +  // @TODO Filter them so that they do not include the term "Translations" (tid 29).
   

   I don't think this is relevant if we only consider new releases, since there are no new translation releases allowed.

Looks good. If you find it works well on staging, it looks good to me to deploy. Thanks for jumping on this so fast.

Oh, well, sorry for the detour then. Let's get back to the drupal http request code BUT don't disable projects and only look at the first needed part of the file (to be quicker and use less memory). Hopefully the file will be OK size for a while.

In the meantime the tsv changed to include the project full name, so we can create it with that. See http://drupalcode.org/project/infrastructure.git/commitdiff/311211d. Also with this the size of the tsv changed from 2.9MB to 4.4MB, so a pretty huge increase :/ Hope this will not mean problems for downloads for a while...

I think we can call this fixed. @Sebcorbin amazingly rolled this out and thousands of releases are now in the queue to parse. Will take some time to catch up with parsing, but now its running. Still 751 releases in the queue but yesterday it was above 2000, so its running well seems like :)