warning: Creating default object from empty value in /var/www/drupal/modules/taxonomy/taxonomy.pages.inc on line 33.

Examples

Queries and visualizations to help people use data

Freecode License Counts, July 2012

This chart shows the 20 most popular licenses chosen by the projects on Freecode.

The SQL to get the data from FLOSSmole to build this chart is as follows:

SELECT f.license, count( f.project_id )
FROM fm_projects f
WHERE datasource_id=[current datasource_id]
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

Freecode/Freshmeat Project Counts, 2005-2012

We have collected Freecode (formerly Freshmeat) data since 2005. This chart shows the number of projects listed in the Freecode directory between 2005 and 2012. Each line represents one collection. (Collections are referred to in our database by the term "datasource_id".)

Obviously in looking at this chart, we are curious what happened between datasource_id's 160 and 173 (between March and June 2009) when the number of projects listed appears to have been pruned by about 6,000 projects. We recall that on March 14, 2009 the Freshmeat site underwent a redesign (named Freshmeat 3.0), so this might have been related.

Here is the code used in the FLOSSmole database to generate the data list to create this graph:

SELECT f.datasource_id, year(d.date_donated), count( f.project_id )
FROM fm_projects f
INNER JOIN datasources d
ON f.datasource_id=d.datasource_id
GROUP BY 1,2
ORDER BY 1,2

Alioth New Project Registrations by month, 2003-2012

Alioth is a Debian-centric code forge. We collect the information about projects hosted on this forge. This chart shows the creation of new projects on this forge from 2003-2012. The datasource_id used is 325.

The SQL code used to generate the data to build this chart from FLOSSmole database is as follows:

SELECT MONTH( registered ) , YEAR( registered ) , COUNT( DISTINCT unixname )
FROM al_projects
WHERE datasource_id =325
GROUP BY YEAR( registered ) , MONTH( registered )
ORDER BY YEAR( registered ) , MONTH( registered );

Savannah New Project Registrations by month, 2000-2012

Savannah is a free software code forge.

The SQL used to generate the data that made this chart is as follows:

SELECT MONTH( registration_date ) , YEAR( registration_date ) , COUNT( DISTINCT project_name )
FROM sv_projects
WHERE datasource_id =[current datasource_id]
GROUP BY YEAR( registration_date ) , MONTH( registration_date )
ORDER BY YEAR( registration_date ) , MONTH( registration_date );

Rubyforge New Project Registrations, 2003-2012

Rubyforge is a software development "code forge" associated with projects written in the Ruby programming language. This chart shows the growth of new projects registered on this forge from July 2003 - July 2012. We used datasource_id=317 in this example.

The SQL to generate the data used to populate this graph is as follows (fill in the datasource_id accordingly):

SELECT MONTH( date_registered ) , YEAR( date_registered ) , COUNT( DISTINCT proj_unixname )
FROM rf_projects
WHERE datasource_id = [current datasource_id]
GROUP BY YEAR( date_registered ) , MONTH( date_registered )
ORDER BY YEAR( date_registered ) , MONTH( date_registered );

Freecode New Project Registrations by month, 1998-2012

Freecode (formerly known as Freshmeat) is a directory of free and open source software. FLOSSmole has been collecting Freecode/Freshmeat data since 2004. Periodically we provide snapshots as to the growth of the various forges and directories we collect data from. Here is a chart showing the rate of new project additions to Freecode/Freshmeat from January 1998 - June 2012. This data is based on the registration date recorded for each project and collected by FLOSSmole in mid-July 2012 (datasource_id 316).

The SQL code to gather this data from our FLOSSmole database is as follows:

SELECT MONTH( date_added ) , YEAR( date_added ) , COUNT( DISTINCT project_id )
FROM fm_projects
WHERE datasource_id = [current datasource_id]
GROUP BY YEAR( date_added ) , MONTH( date_added )
ORDER BY YEAR( date_added ) , MONTH( date_added );

What types of mailing lists are most commonly used in Alioth projects? (07-2011)

Description

This chart shows the top mailing list types used by projects in Alioth. Any mailing lists with an abbreviated type name were grouped accordingly (example: -pkg = packaging). There are 873 total mailing lists currently used in Alioth.

Visualization



SQL Script

This query grabs the text after the last "-" in the mailing list name.

SELECT substr(mailing_list, 2+char_length(mailing_list) -locate('-', reverse(mailing_list) ) ) as 'type', count(mailing_list)
FROM al_mailing_lists
WHERE datasource_id = <current>
GROUP BY 1
ORDER BY 2 DESC;

What developers are working together in Rubyforge projects? (07-2011)

Description

This graph shows a network diagram of the developers who worked together in Rubyforge projects. The purpose of this visualization was to identify developers who have a role in the most projects and interact with the most developers. The twenty developers with the most connections are represented.

Visualization

RF Network

For an interactive visual, click the image below and then click the "Recompute layout" button (connected dots).




SQL Script

Use the results from the following query to create a temp table. This query finds all projects with more than one developer and lists the developer pairings.

SELECT DISTINCT a.proj_unixname, a.dev_loginname, b.dev_loginname
FROM rf_developer_projects a
INNER JOIN rf_developer_projects b
ON a.proj_unixname = b.proj_unixname
WHERE a.datasource_id = <current>
AND a.proj_unixname = b.proj_unixname
AND a.dev_loginname <> b.dev_loginname

Use the temp table for the following query that finds out which developers are on the most multi-developer projects.

SELECT sender_count AS Sender Count, COUNT(mailing_list) AS Frequency
FROM al_temp_table
GROUP BY Sender Count
ORDER BY Frequency DESC;

Using the results from the previous query, you can find the connections for any developer to create the network diagram.

SELECT developer1, developer2
FROM al_temp_table
WHERE developer1 = 'developer_name'

What is the ratio of senders to messages in Alioth mailing lists? (07-2011)

Description

This graph shows the ratio of senders to email messages in Alioth project mailing lists.

Visualization:

The first number shown when hovering over a dot is the number of senders. The second number is the number of messages for each sender count.


SQL Script

SELECT mailing_list, COUNT(DISTINCT sender), COUNT(DISTINCT message_id)
FROM al_messages
GROUP BY mailing_list;

How many senders are there on mailing lists in Alioth projects? (07-2011)

Description

This graph shows the number of mailing lists with each count of email senders in Alioth.

Visualization:

The first number shown when hovering over a dot is the number of senders. The second number is the number of mailing lists with that sender count.



SQL Script

Use the results from the following query to create a temp table.

SELECT mailing_list AS List, COUNT(DISTINCT sender) AS Senders
FROM al_messages
GROUP BY List
ORDER BY Senders DESC;

Use the temp table for the following query.

SELECT sender_count AS Sender Count, COUNT(mailing_list) AS Frequency
FROM al_temp_table
GROUP BY Sender Count
ORDER BY Frequency DESC;

Syndicate content