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

How many projects of each team size are listed in Savannah? (05-2011)

Description

This chart shows the number of projects of each team size listed in Savannah.

Visualization

Projects listed as having 0 developers were disregarded (73 projects).

SQL Script

SELECT DISTINCT project_dev_count, COUNT( DISTINCT project_name ) AS count
FROM sv_projects
WHERE datasource_id= <current>
GROUP BY project_dev_count
ORDER BY count DESC , project_dev_count

How many projects of each team size are listed in Free Software Foundation? (05-2011)

Description

This chart shows the number of projects of each team size listed in Free Software Foundation.

Visualization

Projects listed as having 0 developers were disregarded (57 projects).

SQL Script

SELECT DISTINCT calc_dev_count, COUNT( DISTINCT proj_num ) AS count
FROM fsf_projects
WHERE datasource_id= <current>
GROUP BY calc_dev_count
ORDER BY count DESC , calc_dev_count;

How many projects of each team size are listed in Objectweb? (05-2011)

Description

This chart shows the number of projects of each team size listed in Objectweb.

Visualization

SQL Script

SELECT DISTINCT dev_count, COUNT( DISTINCT proj_unixname ) AS count
FROM ow_projects
WHERE datasource_id = <current>
GROUP BY dev_count
ORDER BY count DESC , dev_count

How many projects of each team size are listed in Rubyforge? (05-2011)

Description

This chart shows the number of projects of each team size listed in Rubyforge.

Visualization

Projects listed as having 0 developers were disregarded (152 projects).

SQL Script

SELECT DISTINCT dev_count, COUNT( DISTINCT proj_unixname ) AS count
FROM rf_projects
WHERE datasource_id = <current>
GROUP BY dev_count
ORDER BY count DESC , dev_count

How has the use of "Free" and "Open" in project names grown by year? (2010 data)

Description

This chart shows the number of new projects in each repository that use the words "Free" and "Open" in project names through 2010.

Visualization

SQL Script

Freshmeat:

SELECT YEAR( date_added ) , COUNT( DISTINCT project_id ) AS Count
FROM fm_projects
WHERE projectname_full LIKE "%free%"
AND datasource_id = <current>
GROUP BY YEAR( date_added )
ORDER BY YEAR( date_added );


SELECT YEAR( date_added ) , COUNT( DISTINCT project_id ) AS Count
FROM fm_projects
WHERE projectname_full LIKE "%open%"
AND datasource_id = <current>
GROUP BY YEAR( date_added )
ORDER BY YEAR( date_added );

Rubyforge:

SELECT YEAR( date_registered ) , COUNT( DISTINCT proj_unixname ) AS Count
FROM rf_projects
WHERE proj_unixname LIKE "%free%"
AND datasource_id = <current>
GROUP BY YEAR( date_registered )
ORDER BY YEAR( date_registered );


SELECT YEAR( date_registered ) , COUNT( DISTINCT proj_unixname ) AS Count
FROM rf_projects
WHERE proj_unixname LIKE "%open%"
AND datasource_id = <current>
GROUP BY YEAR( date_registered )
ORDER BY YEAR( date_registered );

Savannah:

SELECT YEAR( registration_date ) , COUNT( DISTINCT project_name ) AS Count
FROM sv_projects
WHERE project_name LIKE "%free%"
AND datasource_id = <current>
GROUP BY YEAR( registration_date )
ORDER BY YEAR( registration_date );


SELECT YEAR( registration_date ) , COUNT( DISTINCT project_name ) AS Count
FROM sv_projects
WHERE project_name LIKE "%open%"
AND datasource_id = <current>
GROUP BY YEAR( registration_date )
ORDER BY YEAR( registration_date );

How many projects at each repository share URL's? (05-2011)

Description

This chart shows the number of projects at each repository that share URL's as of May 2011.

Visualization

URL-to

URL-from

SQL Script

RF-FM

SELECT COUNT( r.proj_unixname )
FROM rf_projects r
WHERE r.real_url LIKE "%freshmeat%"
AND datasource_id= <current>;

RF-OW

SELECT COUNT( r.proj_unixname )
FROM rf_projects r
WHERE r.real_url LIKE "%objectweb%"
AND datasource_id= <current>;

RF-FSF

SELECT COUNT( r.proj_unixname )
FROM rf_projects r
WHERE r.real_url LIKE "%fsf%"
AND datasource_id= <current>;

FM-RF

SELECT COUNT( f.project_id )
FROM fm_project_homepages f
WHERE f.real_url_homepage LIKE "%rubyforge%"
AND datasource_id= <current>;

FM-OW

SELECT COUNT( f.project_id )
FROM fm_project_homepages f
WHERE f.real_url_homepage LIKE "%objectweb%"
AND datasource_id= <current>;

FM-FSF

SELECT COUNT( f.project_id )
FROM fm_project_homepages f
WHERE f.real_url_homepage LIKE "%fsf%"
AND datasource_id= <current>;

OW-RF

SELECT COUNT( o.proj_unixname )
FROM ow_projects o
WHERE o.real_url LIKE "%rubyforge%"
AND datasource_id= <current>;

OW-FM

SELECT COUNT( o.proj_unixname )
FROM ow_projects o
WHERE o.real_url LIKE "%freshmeat%"
AND datasource_id= <current>;

OW-FSF

SELECT COUNT( o.proj_unixname )
FROM ow_projects o
WHERE o.real_url LIKE "%fsf%"
AND datasource_id= <current>;

FSF-RF

SELECT COUNT( proj_unixname )
FROM fsf_projects
WHERE real_url LIKE "%rubyforge%"
AND datasource_id= <current>;

FSF-FM

SELECT COUNT( proj_unixname )
FROM fsf_projects
WHERE real_url LIKE "%freshmeat%"
AND datasource_id= <current>;

FSF-OW

SELECT COUNT( proj_unixname )
FROM fsf_projects
WHERE real_url LIKE "%objectweb%"
AND datasource_id= <current>;

How many projects at each repository share identical short project names? (05-2011)

Description

This chart shows the number of projects at each repository that share project names as of May 2011.

Visualization

project-names

SQL Script

RF-FM

SELECT COUNT( f.projectname_short_fixed )
FROM fm_projects f, rf_projects r
WHERE f.projectname_short_fixed = r.proj_unixname
AND f.datasource_id = <current>
AND r.datasource_id = <current>;

RF-OW

SELECT COUNT( r.proj_unixname )
FROM rf_projects r, ow_projects o
WHERE r.proj_unixname = o.proj_unixname
AND r.datasource_id = <current>
AND o.datasource_id = <current>;

FM-OW

SELECT COUNT( f.projectname_short_fixed )
FROM fm_projects f, ow_projects o
WHERE f.projectname_short_fixed = o.proj_unixname
AND f.datasource_id = <current>
AND o.datasource_id = <current>;

RF-FSF

SELECT COUNT( f.proj_unixname )
FROM fsf_projects f, rf_projects r
WHERE f.proj_unixname = r.proj_unixname
AND f.datasource_id = <current>
AND r.datasource_id = <current>;

FM-FSF

SELECT COUNT( f.proj_unixname )
FROM fsf_projects f, fm_projects fm
WHERE f.proj_unixname = fm.projectname_short_fixed
AND f.datasource_id = <current>
AND fm.datasource_id = <current>;

OW-FSF

SELECT COUNT( f.proj_unixname )
FROM fsf_projects f, ow_projects o
WHERE f.proj_unixname = o.proj_unixname
AND f.datasource_id = <current>
AND o.datasource_id = <current>;

How have projects in each repository grown by year? (05-2011)

Description

This chart shows the number of NEW projects added to each repository by month/year.

Visualization

Notes: RF had 697 projects without a project start date. OW had one project started in 1970.

SQL Script


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


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


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

How many projects are listed in each repository? (05-2011)

Description

This chart shows the number of projects that FLOSSmole most recently collected from each repository.

Visualization

Project Count Chart


SQL Script


SELECT COUNT( DISTINCT proj_name )
FROM gc_projects
WHERE datasource_id = <current>;


SELECT COUNT( DISTINCT project_id )
FROM fm_projects
WHERE datasource_id = <current>;


SELECT COUNT( DISTINCT project_name )
FROM lp_projects
WHERE datasource_id = <current>;


SELECT COUNT( DISTINCT proj_unixname )
FROM rf_projects
WHERE datasource_id= <current>;


SELECT COUNT( DISTINCT proj_num )
FROM fsf_projects
WHERE datasource_id= <current>;


SELECT COUNT( DISTINCT project_name )
FROM sv_projects
WHERE datasource_id= <current>;


SELECT COUNT( DISTINCT unixname )
FROM tg_projects
WHERE datasource_id = <current>;


SELECT COUNT( DISTINCT proj_unixname )
FROM ow_projects
WHERE datasource_id= <current>;

As of June 2009, what are the top programming languages used by projects listed in Rubyforge?

Description

This chart shows the top programming languages used by projects in Rubyforge.

Visualization

Rubyforge Programming Language Chart

SQL Script

SELECT rfpl.description, count(DISTINCT rfpl.proj_unixname) AS lang
FROM rf_project_programming_language rfpl
WHERE rfpl.datasource_id = <current>
GROUP BY rfpl.description
ORDER BY lang DESC;

Syndicate content