Dear Bob and all, Thanks for the queries, it's a good start.
The first one could be improved to avoid to take into account the directories and other files that are not code files (for example the png files). By the way, do you have, in the fossology team, a documentation of your database (meaning of the tables, keys, columns, dependencies, ...) and/or the database modelisation ? It will be very helpful for me to understand better the database in order to retrieve my metrics (number of distinct licenses incorporated [LCS-1.2.4], the number of unlicensed files/module [LCS-1.2.5] ...). Thanks for your help, Best regards, Jonathan 2009/8/10 Bob Gobeille <[email protected]> > > On Aug 10, 2009, at 4:31 AM, Jonathan Parès wrote: > > Dear all, > > As Qualipso project team member (http://qualipso.org/), I want to use > Fossology to retrieve some metrics about the licenses available on a FLOSS > projects. I would like to retrieve these following metrics with Fossology : > > - the number of distinct licenses incorporated (called then LCS-1.2.4); > - the number of unlicensed files/module (LCS-1.2.5); > - the percentage of FLOSS licensed components (LCS-3.1.1); > > After having tested Fossology, I have seen that the metric LCS-1.2.4 is > provided by Fossology. But what about the metrics LCS-1.2.5 and LCS-3.1.1 ? > I think they can be retrieved undirectly by inference from other results ? > I'm right ? > > > You would need to query the database directly for LCS-1.2.5. The 1.2 > version of fossology will include this in the standard reports. > > In the mean time, here is some sql to help you do this by direct database > query. The examples use upload 125 only for illustration so make sure you > substitute your upload_pk. > > The number of files in an upload: > select count(*) from uploadtree where upload_fk=125 and > (ufile_mode&x'3C000000'::int)=0; > > List the files in upload 125: > select uploadtree_pk, pfile_fk, ufile_name from uploadtree > where upload_fk=125 and (ufile_mode&x'3C000000'::int)=0; > > List all the files with a license for upload_fk 125: > select DISTINCT UT1.uploadtree_pk, UT1.ufile_name,lic_name,licterm_name, > licterm_name_confidence > FROM uploadtree as UT1 > INNER JOIN agent_lic_meta on UT1.pfile_fk=agent_lic_meta.pfile_fk > INNER JOIN agent_lic_raw on agent_lic_meta.lic_fk=agent_lic_raw.lic_pk > LEFT OUTER JOIN licterm_name on licterm_name.pfile_fk=UT1.pfile_fk > LEFT OUTER JOIN licterm on licterm_pk=licterm_name.licterm_fk > WHERE upload_fk=125 > ORDER BY UT1.uploadtree_pk; > > > Hope that helps, > Bob Gobeille > > > > > -- ---------------------------------------------------- Jonathan Parès, Software engineer, Qualipso project, O-Engine/GMRC, Open Source Competence Center (China), Guangzhou China.
_______________________________________________ fossology mailing list [email protected] http://fossology.org/mailman/listinfo/fossology
