Making Queries

From Open Source Software Research
Revision as of 02:31, 13 October 2014 by Gmadey (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Top Links
• Query the Archive
• Schema Browser
• Research Data
• Making Queries
• Resources
• Papers
• Contact
• FAQ
• Schemas
• All tables
• Finding data
• ER diagrams

The Data Query Form

The query form features three main fields: SELECT, FROM, and WHERE.

The Result Field Separator features three options which allow you to choose between the colon, semicolon, and pound sign as a delimiter between the columns of your results.

One difficultly is working out whether the fields you are extracting actually contain the character that you are using as the separator and characters that are then confused with the end of the row (i.e., carriage return). A possible solution: 1) impose that the character chosen as column separator is substituted by another character (for example, if the column separator is ':' replace it with ';') 2) substitute the carriage return inside the column with a space Here is an example:

SELECT a.artifact_id, REPLACE(REPLACE(REPLACE(a.details, ':', ';'), CHR(10),' '), CHR(13),' ')
FROM sf0606.artifact as a
WHERE a.group_artifact_id=497423 ORDER BY a.open_date

There is also a link to a schema browser if you would like to explore what data is available for each schema.

You may access the query form here:
SourceForge Research Archive Query

Tips on Making Queries

The query is cap-insensitive. The WHERE field may be left empty without causing errors.
Some useful clauses include: ORDER BY, COUNT, GROUP BY.

More complex queries can be made by nesting commands.

You can see a sample query and its results here.
Some useful links for newcomers to SQL:

Common Queries

Hints on Debugging

Some of the common errors tend to look like the following:

This error generally refers to a syntax error at the end of the FROM field

Couldn't execute statement: ERROR:  syntax error at end of input at character 32 at /var/www/cgi-bin/req.pl line 71.

Check the end of the SELECT statement for syntax errors

Couldn't execute statement: ERROR:  syntax error at or near "FROM" at character 9 at /var/www/cgi-bin/req.pl line 71.

Check the schema browser for the information you are looking for, as the item in quotes is not in that table.

Couldn't execute statement: ERROR:  column "hello" does not exist at /var/www/cgi-bin/req.pl line 71.


Check to make sure you don't have unnecessary commas or other punctuation.

Analysis

Preliminary analysis on the relationship between number of projects and number of developers has been plotted using R.

  • Results can be found here.
  • Information on how the plots were created can be found here.
Related Links
Creating Snapshots | Sample Snapshots | Tips on Creating Plots | Dataset Resources