"Getting started with Drupal" statistics

It seems to me that the better solution to this would be to use Views. Granted, project_usage isn't Views enabled yet, but since Views 2 can support using views on non-nodes I think that Views could be used.

If this did go into project* directly I don't think we'd want it in the project module, but instead in project_usage. For sites that didn't run project_usage (that is probably all sites running project* with the exception of drupal.org) this function wouldn't make any sense.

Using Views seems overkill to me -- plus the query is non-trivial, IMO.

I'd basically implement a function in project_usage that executes the following query:

For modules:

For themes:

For translations we'd have to use a hard-coded list.

The query seems to be fast as it is always using the primary key:

Note: we already export usage data into the document we send to the solr index. See the new project browsing pages where you can sort by module usage:

http://d6.drupal.org/project/modules

(grrr, at least you could when solr was up). So, it might be easier to just ask solr for this info, instead of the main DB.

In any event, this should either live in project_usage, or in project_solr, but definitely not project itself.

If you're going to do this in project_usage, a few notes on your queries:

A) SELECT MAX(timestamp) FROM project_usage_week_project is very expensive and unnecessary. See variable_get('project_usage_active_weeks').

B) INNER JOIN project_projects pp is unnecessary until #372061: Form_alter the theme vocabularies so that they only show up for theme node editing is done, which might be a long time. You can just have WHERE n.type == 'project_project' for now.

C) If this lives in project_usage, you should strongly consider caching the results -- they only change at most once per week, so it's silly to redo this query on every page load of "Getting started" -- we should just clear the cache when we do the weekly processing.

Thanks for the feedback, dww.

1. Using the WHERE n.type == 'project_project' actually slows down the query because we can no longer use primary keys only. I did some basic testing on the d.o database and the WHERE-clause actually slows down the query. The existing query completes in 0.8 seconds when not cached by MySQL, and in 0.00 seconds when cached by MySQL.
2. SELECT MAX(timestamp) FROM project_usage_week_project completes in 0.01 seconds. I'll add the caching but the query isn't that bad as you seem to suggest it is. It remains to be seen if variable_get() is actually much faster -- I guess it could be a lot faster the day we have a lot more data in the weekly usage table.
3. Putting this functionality in project_usage.module sounds like the right thing to do. I don't think it makes sense to move this to Solr -- it seems like this is perfect use case for using a relational database, and faster too.

Now I've done some additional testing and validation, I'll start working on a patch, and then hopefully we can take it from there. We'd love to implement the "Getting started with Drupal" page as soon as we can.

Patch attached.

I don't have a real-life project module environment setup but I manually created some dummy tables for testing. It seems to be working.

Because there are two parameters, $tid and $limit, I decided not to add caching to this function. I'll add caching to the calling function instead. We'll call this function from custom code in the Drupal.org Customizations project.

The caching in the calling function should also eliminate the need to use variable_get('project_usage_active_weeks').

It would be great to get this committed so we can make progress with the "Getting started with Drupal" page. If you want me to clean certain things up, let me know (or at this point they might be easy enough to clean-up before committing).

1. Typo: +  return $projecs;

2. I think the db_query call needs to first be run through db_rewrite_sql(). Here's (yet another) case where caching the results of the function could lead to inadvertent information disclosure of project titles, so perhaps it's better that the patch you've provided doesn't implement the caching itself. Caching what this function returns for d.o shouldn't be a problem.

3. Instead of putting a "LIMIT" clause in the query itself, isn't it safer to use db_query_range()? My understanding is that pgsql doesn't support using LIMIT the same way that MySQL does. I don't recall whether project_usage even works on pgsql, but we might as well make this query db agnostic from the start.

DamZ and I will brainstorm about this some more to explore using Solr.

subscribing.

Has there been any more thought on this? We're working on the getting started and download pages here at the redesign sprint in SF and would like to move forward with implementing a solution.

I've updated the function Dries supplied slightly since we don't need the count for the display on the landing pages and added another function for returning Newly added projects and attached this as a patch.

We also need a function for returning Most Active Projects, which we are thinking are the projects with the most recent commits or issue updates?

Oh, I just realized that my concern about SELECT MAX(timestamp) FROM project_usage_week_project being very expensive and unnecessary is actually about {project_usage_daily}. {project_usage_week_project} doesn't have nearly as many rows, so it's not so terrible (as Dries's benchmarks suggest). However, I still think it's probably worth just using variable_get('project_usage_active_weeks'), or better yet, the new project_usage_get_current_active_week() function from #371968: Clean APIs to retrieve project related statistics.

Still don't know what the deal is with using Solr for this -- I'll try to track down Damien.